[Home] [Help]
PACKAGE BODY: APPS.BOM_ROLLUP_PUB
Source
1 PACKAGE BODY Bom_Rollup_Pub AS
2 /* $Header: BOMRLUPB.pls 120.7 2007/07/02 05:31:42 dikrishn 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 SELECT impl.parent_item_id
1294 , impl.organization_id
1295 , impl.parent_alternate_designator
1296 , structure_type_id
1297 FROM bom_implosions_v impl
1298 where
1299 impl.sequence_id = p_sequence_id
1300 and impl.top_item_flag='Y'
1301 and ( current_level = 0
1302 OR EXISTS(SELECT 1
1303 FROM bom_structures_b bom
1304 WHERE bom.assembly_item_id = impl.parent_item_id
1305 AND bom.organization_id = impl.organization_id
1306 AND nvl(bom.alternate_bom_designator,'xxxxxxxxxxx') =
1307 nvl(impl.parent_alternate_designator,'xxxxxxxxxxx')
1308 AND bom.is_preferred = 'Y'
1309 AND bom.structure_type_id = p_structure_type_id
1310 AND impl.structure_type_id = p_structure_type_id
1311 )
1312 );
1313
1314 CURSOR c_Preferred_Structure(p_assembly_item_id in varchar2,
1315 p_org_id in varchar2,
1316 p_struct_type_id in varchar2)
1317 IS
1318 SELECT
1319 alternate_bom_designator
1320 FROM
1321 bom_structures_b
1322 WHERE
1323 assembly_item_id = p_assembly_item_id
1324 AND organization_id = p_org_id
1325 AND structure_type_id = p_struct_type_id
1326 AND is_Preferred = 'Y';
1327
1328 l_rollup_id NUMBER;
1329 l_Sequence NUMBER;
1330 l_alternate_bom_code varchar2(30) := FND_LOAD_UTIL.NULL_VALUE;
1331 l_parents_for_pk1 number;
1332 l_check_no_bill boolean := true;
1333 l_check_preferred boolean := true;
1334 l_rollup_item_id number;
1335 l_return_status VARCHAR2(1);
1336 l_msg_count NUMBER;
1337 BEGIN
1338 if (Bom_Rollup_Pub.g_attr_diffs is null) then
1339 --Initialize Error Hanlder
1340 Initialize_ErrorDebug_Handler;
1341 end if;
1342
1343 WRITE_DEBUG_LOG(G_BO_IDENTIFIER, 'Perform_Rollup_Private called item '||p_item_id||'-'||p_organization_id||' parent item '||p_parent_item_id);
1344 -- Rollup optimization:
1345 -- changed to parent so that comp. addition does not trigger additional rollups
1346 l_rollup_item_id := NVL(p_parent_item_id, p_item_id);
1347
1348 -- Ensure that this item is UCCNet enabled
1349 IF (Is_Pack_Item(p_item_id, p_organization_id) <> 'Y')
1350 THEN
1351 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)||']');
1352 RETURN;
1353 END IF;
1354
1355 WRITE_DEBUG_LOG(G_BO_IDENTIFIER, '*****************TESTING****************');
1356 IF G_Attribute_Map.COUNT = 0
1357 THEN
1358 Load_Attribute_Map;
1359 END IF;
1360
1361 IF G_Rollup_Action_Map.COUNT = 0
1362 THEN
1363 Load_Rollup_Action_Map;
1364 END IF;
1365
1366 --For disabling we need to get the preferred alternate structure
1367 for pralt in c_Preferred_Structure(p_assembly_item_id => p_item_id
1368 ,p_org_id => p_organization_id
1369 ,p_struct_type_id => p_structure_type_id)
1370
1371 LOOP
1372 l_alternate_bom_code := pralt.alternate_bom_designator;
1373 END LOOP;
1374
1375
1376 --
1377 -- Load the top items for rollup
1378 --
1379 Load_Top_Items( p_item_id => l_rollup_item_id
1380 , p_organization_id => p_organization_id
1381 , p_alternate_bom_code => l_alternate_bom_code
1382 , x_Sequence => l_Sequence
1383 );
1384
1385 WRITE_DEBUG_LOG(G_BO_IDENTIFIER, 'Structure Type Id is ' || p_structure_type_id);
1386
1387 BEGIN
1388 IF (p_structure_type_id IS NOT NULL)
1389 THEN
1390 SELECT
1391 COUNT(COMPONENT_SEQUENCE_ID) INTO l_parents_for_pk1
1392 from
1393 BOM_SMALL_IMPL_TEMP
1394 WHERE
1395 LOWEST_ITEM_ID = l_rollup_item_id
1396 AND ORGANIZATION_ID = p_organization_id
1397 AND CURRENT_LEVEL > 0
1398 AND SEQUENCE_ID = l_Sequence; --changed to 0 as the sql won't work
1399
1400 BEGIN
1401 IF (l_parents_for_pk1 = 0)
1402 THEN
1403 SELECT
1404 ALTERNATE_BOM_DESIGNATOR INTO l_alternate_bom_code
1405 FROM
1406 BOM_STRUCTURES_B
1407 WHERE
1408 ASSEMBLY_ITEM_ID = l_rollup_item_id
1409 AND ORGANIZATION_ID = p_organization_id
1410 AND STRUCTURE_TYPE_ID = p_structure_type_id
1411 AND IS_PREFERRED = 'Y';
1412
1413 UPDATE
1414 BOM_SMALL_IMPL_TEMP
1415 SET
1416 TOP_ITEM_FLAG ='Y',
1417 ALTERNATE_DESIGNATOR = l_alternate_bom_code,
1418 STRUCTURE_TYPE_ID = p_structure_type_id
1419 WHERE
1420 CURRENT_LEVEL = 0
1421 AND SEQUENCE_ID = l_Sequence
1422 AND LOWEST_ITEM_ID = l_rollup_item_id
1423 AND ORGANIZATION_ID = p_organization_id ;
1424 END IF;
1425 EXCEPTION
1426 WHEN OTHERS THEN
1427 l_alternate_bom_code := NULL;
1428 BEGIN
1429 UPDATE
1430 BOM_SMALL_IMPL_TEMP
1431 SET
1432 TOP_ITEM_FLAG ='Y'
1433 WHERE
1434 CURRENT_LEVEL = 0
1435 AND SEQUENCE_ID = l_Sequence
1436 AND LOWEST_ITEM_ID = l_rollup_item_id
1437 AND ORGANIZATION_ID = p_organization_id ;
1438 END;
1439 END;
1440 END IF;
1441 END;
1442
1443 FOR top_items in c_Top_Items(p_sequence_id => l_Sequence)
1444 LOOP
1445 WRITE_DEBUG_LOG(G_BO_IDENTIFIER, 'INSIDE C_TOP_ITEMS' || top_items.structure_type_id
1446 || ' sequence '||l_Sequence||' parent alt '||top_items.parent_alternate_designator
1447 );
1448 --if (top_items.structure_type_id is not null) then
1449 l_check_no_bill := false;
1450 --Removed for 11.5.10-E
1451 --delete from bom_explosions_all;
1452 l_rollup_id := to_number(to_char(sysdate,'SSSS'));
1453
1454
1455 l_Top_Item_Attrs_Map.DELETE;
1456 l_Header_Attrs_Map.DELETE;
1457 -- This map must be clean for every new top item.
1458
1459 WRITE_DEBUG_LOG (
1460 p_bo_identifier => G_BO_IDENTIFIER
1461 , p_message => 'Calling Explosion');
1462
1463 BOMPCCLT.Process_Items(
1464 p_org_id => p_organization_id
1465 , p_item_id => top_items.parent_item_id
1466 , p_roll_id => l_rollup_id
1467 , p_unit_number => null
1468 , p_eff_date => sysdate
1469 , p_alternate_bom_code => top_items.parent_alternate_designator
1470 , p_prgm_id => -1
1471 , p_prgm_app_id => -1
1472 , p_req_id => -1
1473 , x_err_msg => x_error_message
1474 );
1475
1476 WRITE_DEBUG_LOG(G_BO_IDENTIFIER, 'B4 Validate Hierarchy Attrs');
1477
1478 /* Added validation for UCCNET 3.0 Certification */
1479 BOM_GTIN_RULES.Validate_Hierarchy_ATTRS (
1480 p_group_id => l_rollup_id
1481 , x_return_status => l_return_status
1482 , x_error_message => x_error_message);
1483
1484 WRITE_DEBUG_LOG(G_BO_IDENTIFIER, 'After Validate Hierarchy Attrs Status' || l_return_status);
1485
1486 IF l_return_status IS NOT NULL AND
1487 l_return_status <> 'S'
1488 THEN
1489 WRITE_DEBUG_LOG(G_BO_IDENTIFIER, 'ERROR in Validate Hierarchy Attrs! ['||l_return_status||']'||x_error_message);
1490 Close_ErrorDebug_Handler;
1491 RETURN;
1492 END IF;
1493
1494 WRITE_DEBUG_LOG (
1495 p_bo_identifier => G_BO_IDENTIFIER
1496 , p_message => 'Finished Explosion with error message ' || x_error_message);
1497
1498 --Run Rollup for only preferred Structures
1499 --Check isPrefered
1500 WRITE_DEBUG_LOG(G_BO_IDENTIFIER, 'INSIDE is_preferred_check_cr assitem' || top_items.parent_item_id );
1501 WRITE_DEBUG_LOG(G_BO_IDENTIFIER, 'INSIDE is_preferred_check_cr orgid' || p_organization_id );
1502 WRITE_DEBUG_LOG(G_BO_IDENTIFIER, 'INSIDE is_preferred_check_cr structure' || p_structure_type_id );
1503
1504 For is_preferred_check_cr in c_Preferred_Structure(p_assembly_item_id => top_items.parent_item_id
1505 ,p_org_id => p_organization_id
1506 ,p_struct_type_id => p_structure_type_id)
1507
1508 LOOP
1509 WRITE_DEBUG_LOG(G_BO_IDENTIFIER, 'INSIDE is_preferred_check_cr top_items. parent alt desig' || top_items.parent_alternate_designator );
1510 WRITE_DEBUG_LOG(G_BO_IDENTIFIER, 'INSIDE is_preferred_check_cr pref alt desig ' || is_preferred_check_cr.alternate_bom_designator );
1511
1512 if (nvl(top_items.parent_alternate_designator,'XXXXXXXX') = nvl(is_preferred_check_cr.alternate_bom_designator,'XXXXXXXX')) then
1513 l_check_preferred := true;
1514 else
1515 l_check_preferred := false;
1516 end if;
1517 END LOOP;
1518
1519 if (l_check_preferred) then
1520 WRITE_DEBUG_LOG(G_BO_IDENTIFIER, 'Before Handle Rollup Actions');
1521 Handle_Rollup_Actions (
1522 p_rollup_id => l_rollup_id
1523 , p_action_map => p_action_map
1524 , p_item_id => top_items.parent_item_id
1525 , p_organization_id => p_organization_id
1526 , p_parent_item_id => p_parent_item_id
1527 , p_component_item_id => p_item_id
1528 , p_alternate_bom_code => top_items.parent_alternate_designator
1529 , p_validate => p_validate
1530 , p_halt_on_error => p_halt_on_error
1531 , x_return_status => l_return_status
1532 , x_msg_count => l_msg_count
1533 , x_msg_data => x_error_message
1534 );
1535 WRITE_DEBUG_LOG(G_BO_IDENTIFIER, 'After Handle Rollup Actions');
1536
1537 -- do not proceed with any other rollups if error is found
1538 IF p_halt_on_error = 'Y' AND
1539 l_return_status IS NOT NULL AND
1540 l_return_status <> 'S'
1541 THEN
1542
1543 WRITE_DEBUG_LOG(G_BO_IDENTIFIER, 'ERROR in Handle Rollup Actions! ['||l_return_status||']'||x_error_message);
1544 Close_ErrorDebug_Handler;
1545 RETURN;
1546
1547 END IF;
1548 end if;
1549 --end if;
1550 END LOOP;
1551
1552 --Close Error Hanlder
1553 Close_ErrorDebug_Handler;
1554
1555
1556
1557 END Perform_Rollup_Private;
1558
1559 PROCEDURE Perform_Rollup_Private
1560 ( p_item_id IN NUMBER
1561 , p_organization_id IN NUMBER
1562 , p_alternate_bom_code IN VARCHAR2
1563 , p_action_map IN Bom_Rollup_Pub.Rollup_Action_Map := G_EMPTY_ACTION_MAP
1564 , p_validate IN VARCHAR2
1565 , p_halt_on_error IN VARCHAR2
1566 , x_error_message OUT NOCOPY VARCHAR2
1567 )
1568 IS
1569 CURSOR c_Top_Items(p_sequence_id NUMBER)
1570 IS
1571 SELECT parent_item_id
1572 , organization_id
1573 FROM bom_implosions_v
1574 WHERE sequence_id = p_sequence_id
1575 AND top_item_flag = 'Y';
1576
1577 l_rollup_id NUMBER;
1578 l_Sequence NUMBER;
1579 l_return_status VARCHAR2(1);
1580 l_msg_count NUMBER;
1581 BEGIN
1582 if (Bom_Rollup_Pub.g_attr_diffs is null) then
1583 --Initialize Error Hanlder
1584 Initialize_ErrorDebug_Handler;
1585 end if;
1586
1587 IF G_Attribute_Map.COUNT = 0
1588 THEN
1589 Load_Attribute_Map;
1590 END IF;
1591
1592 IF G_Rollup_Action_Map.COUNT = 0
1593 THEN
1594 Load_Rollup_Action_Map;
1595 END IF;
1596 --
1597 -- Load the top items for rollup
1598 --
1599
1600 Load_Top_Items( p_item_id => p_item_id
1601 , p_organization_id => p_organization_id
1602 , p_alternate_bom_code => p_alternate_bom_code
1603 , x_Sequence => l_Sequence
1604 );
1605
1606 FOR top_items in c_Top_Items(p_sequence_id => l_Sequence)
1607 LOOP
1608 --Removed for 11.5.10-E
1609 --delete from bom_explosions_all;
1610 l_rollup_id := to_number(to_char(sysdate,'SSSS'));
1611
1612
1613 l_Top_Item_Attrs_Map.DELETE;
1614 l_Header_Attrs_Map.DELETE;
1615 -- This map must be clean for every new top item.
1616
1617 WRITE_DEBUG_LOG (
1618 p_bo_identifier => G_BO_IDENTIFIER
1619 , p_message => 'Processing Items for Explosion' );
1620
1621 BOMPCCLT.Process_Items(
1622 p_org_id => p_organization_id
1623 , p_item_id => top_items.parent_item_id
1624 , p_roll_id => l_rollup_id
1625 , p_unit_number => null
1626 , p_eff_date => sysdate
1627 , p_alternate_bom_code => p_alternate_bom_code
1628 , p_prgm_id => -1
1629 , p_prgm_app_id => -1
1630 , p_req_id => -1
1631 , x_err_msg => x_error_message
1632 );
1633 WRITE_DEBUG_LOG(G_BO_IDENTIFIER, 'B4 - 1 Validate Hierarchy Attrs');
1634
1635 BOM_GTIN_RULES.Validate_Hierarchy_ATTRS (
1636 p_group_id => l_rollup_id
1637 , x_return_status => l_return_status
1638 , x_error_message => x_error_message);
1639
1640 WRITE_DEBUG_LOG(G_BO_IDENTIFIER, 'After - 1 Validate Hierarchy Attrs Status' || l_return_status);
1641
1642 IF l_return_status IS NOT NULL AND
1643 l_return_status <> 'S'
1644 THEN
1645 WRITE_DEBUG_LOG(G_BO_IDENTIFIER, 'ERROR in Validate Hierarchy Attrs! ['||l_return_status||']'||x_error_message);
1646 Close_ErrorDebug_Handler;
1647 RETURN;
1648 END IF;
1649
1650 WRITE_DEBUG_LOG (
1651 p_bo_identifier => G_BO_IDENTIFIER
1652 , p_message => 'Handling ROllup Actions' );
1653
1654 Handle_Rollup_Actions
1655 ( p_rollup_id => l_rollup_id
1656 , p_action_map => p_action_map
1657 , p_item_id => top_items.parent_item_id
1658 , p_organization_id => p_organization_id
1659 , p_alternate_bom_code => p_alternate_bom_code
1660 , p_validate => p_validate
1661 , p_halt_on_error => p_halt_on_error
1662 , x_return_status => l_return_status
1663 , x_msg_count => l_msg_count
1664 , x_msg_data => x_error_message
1665 );
1666
1667 -- do not proceed with any other rollups if error is found
1668 IF p_halt_on_error = 'Y' AND
1669 l_return_status IS NOT NULL AND
1670 l_return_status <> 'S'
1671 THEN
1672
1673 WRITE_DEBUG_LOG(G_BO_IDENTIFIER, 'ERROR in Handle Rollup Actions! ['||l_return_status||']'||x_error_message);
1674 Close_ErrorDebug_Handler;
1675 RETURN;
1676
1677 END IF;
1678
1679 --
1680 -- Delete the processed rows and clean slate for the next
1681 -- rollup
1682 --
1683 --BOMPCCLT.Delete_Processed_Rows(l_rollup_id);
1684
1685 END LOOP;
1686
1687 --Close Error Hanlder
1688 Close_ErrorDebug_Handler;
1689
1690 END Perform_Rollup_Private;
1691
1692 /****************** Local Procedures Section Ends ******************/
1693
1694 /************************************************************************
1695 * Procedure: Perform_Rollup
1696 * Purpose : This method will perform rollup or propogation of attributes
1697 * The attribute value propogated or computed up the bom is based
1698 * on the value returned by the compute_function.
1699 * Compute function will be passed Attribute Map and the list of
1700 * child components.
1701 *
1702 **************************************************************************/
1703
1704 PROCEDURE Perform_Rollup
1705 ( p_item_id IN NUMBER
1706 , p_organization_id IN NUMBER
1707 , p_alternate_bom_code IN VARCHAR2
1708 , p_action_map IN Bom_Rollup_Pub.Rollup_Action_Map DEFAULT G_EMPTY_ACTION_MAP
1709 , p_validate IN VARCHAR2 /*DEFAULT 'N'*/
1710 , p_halt_on_error IN VARCHAR2 /*DEFAULT 'N'*/
1711 , x_error_message OUT NOCOPY VARCHAR2
1712 )
1713 IS
1714 BEGIN
1715
1716 Perform_Rollup_Private
1717 ( p_item_id => p_item_id
1718 , p_organization_id => p_organization_id
1719 , p_alternate_bom_code => p_alternate_bom_code
1720 , p_action_map => p_action_map
1721 , p_validate => p_validate
1722 , p_halt_on_error => p_halt_on_error
1723 , x_error_message => x_error_message
1724 );
1725 END Perform_Rollup;
1726
1727 /************************************************************************
1728 * Procedure: Perform_Rollup
1729 * Purpose : Method accepts a Structure Type. The Rollup will happen for the
1730 * Preferred Structure within the given Structure.
1731 * This method will perform rollup or propogation of attributes
1732 * The attribute value propogated or computed up the bom is based
1733 * on the value returned by the compute_function.
1734 * Compute function will be passed Attribute Map and the list of
1735 * child components.
1736 *
1737 **************************************************************************/
1738
1739 PROCEDURE Perform_Rollup
1740 ( p_item_id IN NUMBER
1741 , p_organization_id IN NUMBER
1742 , p_parent_item_id IN NUMBER := NULL
1743 , p_structure_type_id IN NUMBER
1744 , p_action_map IN Bom_Rollup_Pub.Rollup_Action_Map := G_EMPTY_ACTION_MAP
1745 , p_validate IN VARCHAR2 /*DEFAULT 'N'*/
1746 , p_halt_on_error IN VARCHAR2 /*DEFAULT 'N'*/
1747 , x_error_message OUT NOCOPY VARCHAR2
1748 )
1749 IS
1750 BEGIN
1751 Perform_Rollup_Private
1752 ( p_item_id => p_item_id
1753 , p_organization_id => p_organization_id
1754 , p_parent_item_id => p_parent_item_id
1755 , p_structure_type_id => p_structure_type_id
1756 , p_action_map => p_action_map
1757 , p_validate => p_validate
1758 , p_halt_on_error => p_halt_on_error
1759 , x_error_message => x_error_message
1760 );
1761
1762 END Perform_Rollup;
1763
1764 /************************************************************************
1765 * Procedure: Perform_Rollup
1766 * Purpose : Method accepts a Structure Type identifier. The Rollup will happen
1767 * for the Preferred Structure within the given Structure.
1768 * This method will perform rollup or propogation of attributes
1769 * The attribute value propogated or computed up the bom is based
1770 * on the value returned by the compute_function.
1771 * Compute function will be passed Attribute Map and the list of
1772 * child components.
1773 *
1774 **************************************************************************/
1775 PROCEDURE Perform_Rollup
1776 ( p_item_id IN NUMBER
1777 , p_organization_id IN NUMBER
1778 , p_parent_item_id IN NUMBER := NULL
1779 , p_structure_type_name IN VARCHAR2
1780 , p_action_map IN Bom_Rollup_Pub.Rollup_Action_Map := G_EMPTY_ACTION_MAP
1781 , p_validate IN VARCHAR2 /*DEFAULT 'N'*/
1782 , p_halt_on_error IN VARCHAR2 /*DEFAULT 'N'*/
1783 , x_error_message OUT NOCOPY VARCHAR2
1784 )
1785 IS
1786 CURSOR c_structure_type_id IS
1787 SELECT structure_type_id
1788 FROM bom_structure_types_B
1789 WHERE structure_type_name = p_structure_type_name;
1790 BEGIN
1791
1792
1793 FOR struct_type IN c_structure_type_id
1794 LOOP
1795 Perform_Rollup_Private
1796 ( p_item_id => p_item_id
1797 , p_organization_id => p_organization_id
1798 , p_parent_item_id => p_parent_item_id
1799 , p_structure_type_id => struct_type.structure_type_id
1800 , p_action_map => p_action_map
1801 , p_validate => p_validate
1802 , p_halt_on_error => p_halt_on_error
1803 , x_error_message => x_error_message
1804 );
1805 END LOOP;
1806
1807 END Perform_Rollup;
1808
1809
1810 /*********************************************************************
1811 * Function: Get_Rollup_Function
1812 * Purpose : Given an Object and the Rollup Action to be performed, this
1813 * function will return the Roll function that will be executed.
1814 *
1815 ************************************************************************/
1816 Function Get_Rollup_Function
1817 ( p_object_name IN VARCHAR2
1818 , p_rollup_action IN VARCHAR2
1819 ) RETURN VARCHAR2
1820 IS
1821 BEGIN
1822 NULL;
1823 -- not implemented yet.
1824 END Get_Rollup_Function;
1825
1826 /*********************************************************************
1827 * Procedure : Add_Rollup_Function
1828 * Purpose : This procedure helps build the Action Map. Once the action
1829 * map is ready, calling application can call perfom_rollup
1830 * with the Rollup Action Map.
1831 * The object/action is checked against the supported set of
1832 * Actions, hence, an incorrect combination will throw a
1833 * 'BOM_OBJECT_ACTION_INVALID' exception.
1834 ************************************************************************/
1835 Procedure Add_Rollup_Function
1836 ( p_Object_Name IN VARCHAR2
1837 , p_Rollup_Action IN VARCHAR2
1838 , p_DML_Function IN VARCHAR2
1839 , p_DML_Delayed_Write IN VARCHAR2
1840 , x_Rollup_Action_Map IN OUT NOCOPY Bom_Rollup_Pub.Rollup_Action_Map
1841 )
1842 IS
1843 l_map_index NUMBER := x_Rollup_Action_Map.COUNT + 1;
1844 BEGIN
1845 FOR cindex IN 1..x_Rollup_Action_Map.COUNT
1846 LOOP
1847 IF(x_Rollup_Action_Map(cindex).Object_Name = p_Object_Name AND
1848 x_Rollup_Action_Map(cindex).Rollup_Action = p_Rollup_Action AND
1849 x_Rollup_Action_Map(cindex).DML_Function = p_DML_Function AND
1850 x_Rollup_Action_Map(cindex).DML_Delayed_Write = p_DML_Delayed_Write
1851 )
1852 THEN
1853 -- do nothing since the action was already registered for the
1854 -- current session.
1855 return;
1856 END IF;
1857
1858 END LOOP;
1859
1860 x_Rollup_Action_Map(l_map_index).Object_Name := p_Object_Name;
1861 x_Rollup_Action_Map(l_map_index).Rollup_Action := p_Rollup_Action;
1862 x_Rollup_Action_Map(l_map_index).DML_Function := p_DML_Function;
1863 x_Rollup_Action_Map(l_map_index).DML_Delayed_Write := p_DML_Delayed_Write;
1864
1865 END Add_Rollup_Function;
1866
1867 /*********************************************************************
1868 * Procedure : Get_Item_Rollup_Map
1869 * Purpose : Returns the supported list of Actions for an Object
1870 *
1871 ************************************************************************/
1872 Function Get_Item_Rollup_Map
1873 ( p_Object_Name IN VARCHAR2 )
1874 RETURN Bom_Rollup_Pub.Rollup_Action_Map
1875 IS
1876 BEGIN
1877 return G_EMPTY_ACTION_MAP;
1878 -- not implemented yet.
1879
1880 END Get_Item_Rollup_Map;
1881
1882 /************************************************************************
1883 * Procedure: Set_Parent_Attribute
1884 * Purpose : Sets the parent attribute. When a computation function
1885 * completes, if it is an attribute that is updated for the immediate
1886 * parent, then the computation function needs to set the attribute value
1887 * for the parent so that the rollup process can issue an call for
1888 * affected parent.
1889 **************************************************************************/
1890 PROCEDURE Set_Parent_Attribute
1891 ( p_attribute_name IN VARCHAR2
1892 , p_attribute_value IN VARCHAR2
1893 )
1894 IS
1895 l_header_attr_cnt NUMBER := l_Header_Attrs_Map.COUNT;
1896 BEGIN
1897 --
1898 -- This is a helper method and is intended simply to take the attributed and append
1899 -- it to the map. this will not offer any intelligence of making sure if the attribute
1900 -- was already set. So, the calling process should ensure of this.
1901 -- we would later add this.
1902 --
1903 l_Header_Attrs_Map(l_Header_Attr_cnt).attribute_name :=
1904 p_attribute_name;
1905
1906 l_Header_Attrs_Map(l_Header_Attr_cnt).attribute_value :=
1907 p_attribute_value;
1908
1909 END Set_Parent_Attribute;
1910
1911 /************************************************************************
1912 * Procedure: Set_Parent_Attribute
1913 * Purpose : Sets the top item's attribute. When a computation function
1914 * completes, for an attribute that is only computed for the top
1915 * most parent, the computation function will call this methid to
1916 * set the Top Item's attribute. The Rollup process will at the
1917 * end issue an appropriate call to update the affected Item.
1918 **************************************************************************/
1919 PROCEDURE Set_Top_Item_Attribute
1920 ( p_attribute_name IN VARCHAR2
1921 , p_attribute_value IN VARCHAR2
1922 )
1923 IS
1924 l_header_attr_cnt NUMBER := l_Top_Item_Attrs_Map.COUNT + 1;
1925 BEGIN
1926 --
1927 -- This is a helper method and is intended simply to take the attributed and append
1928 -- it to the map. this will not offer any intelligence of making sure if the attribute
1929 -- was already set. So, the calling process should ensure of this.
1930 -- we would later add this.
1931 --
1932 FOR map_index IN 1..l_Top_Item_Attrs_Map.COUNT
1933 LOOP
1934 IF(l_Top_Item_Attrs_Map.exists(map_index) AND
1935 l_Top_Item_Attrs_Map(map_index).attribute_name = p_attribute_name)
1936 THEN
1937 l_header_attr_cnt := map_index;
1938 END IF;
1939 END LOOP;
1940 l_Top_Item_Attrs_Map(l_Header_Attr_cnt).attribute_name :=
1941 p_attribute_name;
1942 l_Top_Item_Attrs_Map(l_Header_Attr_cnt).attribute_value :=
1943 p_attribute_value;
1944
1945 END Set_Top_Item_Attribute;
1946
1947
1948 /************************************************************************
1949 * Procedure: Get_Attribute_Value
1950 * Purpose : Returns the attribute value for an attribute. This method
1951 * works only off of the reverse tology tree and has access to
1952 * to immediate children for a parent being processed.
1953 * When a computation function needs attribute value for a
1954 * component, it will request so by passing the component identifier
1955 * and the attribute name.
1956 **************************************************************************/
1957 FUNCTION Get_Attribute_Value
1958 ( p_component_sequence_id IN NUMBER
1959 , p_attribute_name IN VARCHAR2
1960 )
1961 RETURN VARCHAR2
1962 IS
1963 BEGIN
1964 FOR cmp_index IN Bom_Rollup_Pub.l_component_seq_attrs_tbl.FIRST..Bom_Rollup_Pub.l_component_seq_attrs_tbl.LAST
1965 LOOP
1966 IF Bom_Rollup_Pub.l_component_seq_attrs_tbl.EXISTS(cmp_index) AND
1967 Bom_Rollup_Pub.l_component_seq_attrs_tbl(cmp_index).component_sequence_id = p_component_sequence_id AND
1968 Bom_Rollup_Pub.l_component_seq_attrs_tbl(cmp_index).attribute_name = p_attribute_name
1969 THEN
1970 return Bom_Rollup_Pub.l_component_seq_attrs_tbl(cmp_index).attribute_value;
1971 END IF;
1972 END LOOP;
1973
1974 END Get_Attribute_Value;
1975
1976
1977 /************************************************************************
1978 * Procedure: Get_Top_Item_Attribute_Value
1979 * Purpose : Returns the attribute value for an attribute. This method
1980 * works only off of the reverse tology tree and has access to
1981 * to immediate children for a parent being processed.
1982 * When a computation function needs attribute value for a
1983 * component, it will request so by passing the component identifier
1984 * and the attribute name.
1985 **************************************************************************/
1986 FUNCTION Get_Top_Item_Attribute_Value
1987 ( p_attribute_name IN VARCHAR2
1988 )
1989 RETURN VARCHAR2
1990 IS
1991 BEGIN
1992 IF Bom_Rollup_Pub.l_Top_Item_Attrs_Map.COUNT = 0
1993 THEN
1994 RETURN NULL;
1995 END IF;
1996
1997 FOR cmp_index IN Bom_Rollup_Pub.l_Top_Item_Attrs_Map.FIRST..Bom_Rollup_Pub.l_Top_Item_Attrs_Map.LAST
1998 LOOP
1999 IF Bom_Rollup_Pub.l_Top_Item_Attrs_Map.EXISTS(cmp_index) AND
2000 Bom_Rollup_Pub.l_Top_Item_Attrs_Map(cmp_index).attribute_name = p_attribute_name
2001 THEN
2002 return Bom_Rollup_Pub.l_Top_Item_Attrs_Map(cmp_index).attribute_value;
2003 END IF;
2004 END LOOP;
2005
2006 -- else return NULL
2007 RETURN null;
2008
2009 END Get_Top_Item_Attribute_Value;
2010
2011 /************************************************************************
2012 * Procedure: Perform_Rollup
2013 * Purpose : This method will perform rollup for multi-row attributes or
2014 * propogation of multi-row attributes
2015 * The attribute value propogated or computed up the bom is based
2016 * on the value returned by the compute_function.
2017 * The propogation happens for only the attributes being passed
2018 * in EGO_USER_ATTR_DIFF_TABLE
2019 **************************************************************************/
2020 PROCEDURE Perform_Rollup
2021 ( p_item_id IN NUMBER
2022 , p_organization_id IN NUMBER
2023 , p_structure_type_name IN VARCHAR2
2024 , p_pk_column_name_value_pairs IN EGO_COL_NAME_VALUE_PAIR_ARRAY
2025 , p_class_code_name_value_pairs IN EGO_COL_NAME_VALUE_PAIR_ARRAY
2026 , p_data_level_name_value_pairs IN EGO_COL_NAME_VALUE_PAIR_ARRAY
2027 , p_attr_diffs IN EGO_USER_ATTR_DIFF_TABLE
2028 , p_transaction_type IN VARCHAR2
2029 , p_attr_group_id IN NUMBER
2030 , p_action_map IN Bom_Rollup_Pub.Rollup_Action_Map DEFAULT G_EMPTY_ACTION_MAP
2031 , x_error_message OUT NOCOPY VARCHAR2
2032 ) is
2033 begin
2034
2035 Initialize_ErrorDebug_Handler;
2036
2037 WRITE_DEBUG_LOG (
2038 p_bo_identifier => G_BO_IDENTIFIER
2039 , p_message => 'INSIDE Multi-Row Item Call');
2040
2041 IF (p_attr_diffs IS NOT NULL AND p_attr_diffs.COUNT > 0) THEN -- is the object null
2042 Bom_Rollup_Pub.g_pk_column_name_value_pairs := p_pk_column_name_value_pairs;
2043 Bom_Rollup_Pub.g_class_code_name_value_pairs := p_class_code_name_value_pairs;
2044 Bom_Rollup_Pub.g_data_level_name_value_pairs := p_data_level_name_value_pairs;
2045 Bom_Rollup_Pub.g_attr_diffs := p_attr_diffs;
2046 Bom_Rollup_Pub.g_transaction_type := p_transaction_type;
2047 Bom_Rollup_Pub.g_attr_group_id := p_attr_group_id;
2048 WRITE_DEBUG_LOG (
2049 p_bo_identifier => G_BO_IDENTIFIER
2050 , p_message => 'INSIDE Multi-Row Item Call with txn type' || p_transaction_type);
2051 END IF;
2052
2053 Perform_Rollup
2054 ( p_item_id => p_item_id
2055 , p_organization_id => p_organization_id
2056 , p_structure_type_name => p_structure_type_name
2057 , p_action_map => p_action_map
2058 , x_error_message => x_error_message
2059 );
2060
2061 end Perform_Rollup;
2062
2063 FUNCTION Is_UCCNet_Enabled(p_inventory_item_id IN NUMBER
2064 ,p_organization_id IN NUMBER
2065 )
2066 RETURN VARCHAR2
2067 IS
2068 CURSOR c_check_if_uccnet(p_inventory_item_id NUMBER
2069 ,p_organization_id NUMBER) IS
2070 SELECT 'X' assignment_present
2071 FROM mtl_default_category_sets a
2072 , mtl_item_categories b
2073 WHERE a.functional_area_id = 12
2074 AND a.category_set_id = b.category_set_id
2075 AND rownum = 1
2076 AND inventory_item_id = p_inventory_item_id
2077 AND organization_id = p_organization_id;
2078
2079 l_assignment_present VARCHAR2(1);
2080
2081 BEGIN
2082 -- note: do not embed any DML calls (even in debug statements in this function
2083 -- or else we'll see ORA-14551: cannot perform a DML operation inside a query
2084 OPEN c_check_if_uccnet(p_inventory_item_id, p_organization_id);
2085
2086 FETCH c_check_if_uccnet INTO l_assignment_present;
2087 IF c_check_if_uccnet%FOUND THEN
2088
2089 CLOSE c_check_if_uccnet;
2090 RETURN 'Y';
2091
2092 ELSE
2093
2094 CLOSE c_check_if_uccnet;
2095 RETURN 'N';
2096
2097 END IF;
2098
2099 END Is_UCCNet_Enabled;
2100
2101 FUNCTION Get_Trade_Item_Unit_Descriptor
2102 ( p_inventory_item_id IN NUMBER
2103 , p_organization_id IN NUMBER
2104 ) RETURN VARCHAR2
2105 IS
2106 CURSOR c_get_tiud(p_inventory_item_id NUMBER
2107 ,p_organization_id NUMBER) IS
2108 SELECT trade_item_descriptor
2109 FROM ego_items_v
2110 WHERE inventory_item_id = p_inventory_item_id
2111 AND organization_id = p_organization_id;
2112
2113 l_tiud VARCHAR2(35);
2114
2115 BEGIN
2116
2117 OPEN c_get_tiud(p_inventory_item_id, p_organization_id);
2118
2119 FETCH c_get_tiud INTO l_tiud;
2120
2121 IF c_get_tiud%FOUND THEN
2122
2123 CLOSE c_get_tiud;
2124 RETURN l_tiud;
2125
2126 ELSE
2127
2128 CLOSE c_get_tiud;
2129 RETURN null;
2130
2131 END IF;
2132
2133 END Get_Trade_Item_Unit_Descriptor;
2134
2135 FUNCTION Is_Pack_Item(p_inventory_item_id IN NUMBER
2136 ,p_organization_id IN NUMBER
2137 )
2138 RETURN VARCHAR2
2139 IS
2140
2141 l_pack_type VARCHAR2(80);
2142
2143 BEGIN
2144 -- Get the pack type attribute from MSI.
2145 -- IF the pack type is not null then return 'Y' otherwise return 'N'
2146 --SELECT Pack_Type INTO l_pack_type
2147 SELECT TRADE_ITEM_DESCRIPTOR INTO l_pack_type
2148 FROM MTL_SYSTEM_ITEMS_B
2149 WHERE inventory_item_id = p_inventory_item_id
2150 AND organization_id = p_organization_id;
2151
2152 IF l_pack_type IS NOT NULL THEN
2153 RETURN 'Y';
2154 ELSE
2155 RETURN 'N';
2156 END IF;
2157 EXCEPTION
2158 WHEN OTHERS THEN
2159 RETURN 'N';
2160
2161 END Is_Pack_Item;
2162
2163 /************************************************************************
2164 * Procedure: WRITE_DEBUG_LOG
2165 * Purpose : This method will write debug information to the
2166 * to the log file based on MRP_DEBUG Flag
2167 **************************************************************************/
2168 PROCEDURE WRITE_DEBUG_LOG
2169 ( p_bo_identifier IN varchar2
2170 , p_message IN varchar2
2171 )
2172 IS
2173 l_err_text varchar2(3000);
2174 BEGIN
2175 IF (G_DEBUG_FLAG = 'Y') THEN
2176 Error_HandLer.Write_Debug
2177 ( p_debug_message => p_message );
2178 END IF;
2179 exception
2180 WHEN OTHERS THEN
2181 l_err_text := SQLERRM;
2182 l_err_text := 'Error : '||TO_CHAR(SQLCODE)||'---'||l_err_text;
2183 WRITE_ERROR_LOG (
2184 p_bo_identifier => G_BO_IDENTIFIER
2185 , p_message => l_err_text);
2186 END;
2187
2188
2189 /************************************************************************
2190 * Procedure: WRITE_ERROR_LOG
2191 * Purpose : This method will write Errors to
2192 * error handler
2193 **************************************************************************/
2194 PROCEDURE WRITE_ERROR_LOG
2195 ( p_bo_identifier IN varchar2
2196 , p_message IN varchar2
2197 )
2198 IS
2199 BEGIN
2200 Error_Handler.Add_Error_Message
2201 ( p_message_text => p_message
2202 , p_message_type => 'E'
2203 );
2204 END;
2205
2206
2207 END Bom_Rollup_Pub;