1 PACKAGE BODY Bom_Compute_Functions AS
2 /* $Header: BOMCMPFB.pls 120.5 2007/02/26 12:29:34 vhymavat ship $ */
3 /*# This package defines the atttribute Compute functions and Rollup compute functions
4 * Whenever an item attribute needs to have computation based on related attributes
5 * a computation function can be added and registered in the Attribute Map. Only
6 * function per attribute is permitted.
7 * Similarly, Rollup functions help in computing the value of a Parent in a Parent
8 * Child relationship.
9 * For eg. Container's wt = Wt. of Container item + 1..n[Sum(qty of child * unit wt of child)]
10 * A rollup function is expected to impact atmost 1 attribute of the parent.
11 * When rollup functions are registered, it is not required for them to always belong to this
12 * package, but are expected to conform to the input/output parameter restrictions.
13 * All rollup functions and compute functions have access to the attributes of the object at that level
14 * @rep:scope private
15 * @rep:product BOM
16 * @rep:displayname Rollup Functions
17 * @rep:compatibility S
18 * @rep:category BUSINESS_ENTITY BOM_BILL_OF_MATERIAL
19 */
20
21 /**********************************************************************
22 ** The attribute compute functions affect attributes at the component level and
23 ** will compute the data for a single component sequence_id
24 **
25 ** The Rollup functions will affect attributes of the header and will
26 ** affect a single header row. When the rollup function is done, it is
27 ** expected to write the attribute_name, computed_value to the header attribute
28 ** map. This is required for the further process to correctly perform the
29 ** rollup and updates of the header item.
30 ** Some Rollup functions like Top GTIN affect only the Top Item
31 ***********************************************************************/
32
33 /*#
34 * This method will be used for computing the net_weight attributes value
35 * The method does not have any parameters, but it will have access to the
36 * attribute map or the current item in process.
37 * This should not be confused with the actual rollup function. This function
38 * helps the derivation of the net_weight attribute for that particulat item
39 * whereas rollup function of net_weight would take into consideration all the
40 * child components of the current item.
41 *
42 * @rep:scope private
43 * @rep:lifecycle active
44 * @rep:displayname Compute Net weight
45 */
46 PROCEDURE Compute_Net_Weight(x_attribute_value IN OUT NOCOPY VARCHAR2
47 ,p_component_sequence_id IN OUT NOCOPY NUMBER)
48 IS
49 BEGIN
50 --
51 -- for an item the net wt is whatever is there in the table entry
52 -- until it is over-written by a rollup function
53 -- at the parent level, but there is no special rule of
54 -- deriving the unit weight of a component
55 --
56 null;
57 END Compute_Net_Weight;
58
59 /*#
60 * This method will be used for computing the net_weight attributes value
61 * The method does not have any parameters, but it will have access to the
62 * attribute map or the current item in process.
63 * This should not be confused with the actual rollup function. This function
64 * helps the derivation of the net_weight attribute for that particulat item
65 * whereas rollup function of net_weight would take into consideration all the
66 * child components of the current item.
67 *
68 * @rep:scope private
69 * @rep:lifecycle active
70 * @rep:displayname Compute Net weight
71 */
72 PROCEDURE Compute_Gross_Weight(x_attribute_value IN OUT NOCOPY VARCHAR2
73 ,p_component_sequence_id IN OUT NOCOPY NUMBER)
74 IS
75 CURSOR c_parent_or_leaf IS
76 SELECT 'Parent'
77 FROM bom_components_b comp
78 ,bom_structures_b parent
79 WHERE comp.component_sequence_id = p_component_sequence_id
80 AND comp.bill_sequence_id = parent.bill_sequence_id
81 AND EXISTS (SELECT bill_sequence_id
82 FROM bom_structures_b comp_struct
83 WHERE comp_struct.pk1_value = comp.pk1_value
84 AND nvl(comp_struct.pk2_value, 'xxxxxxxxxxxxxxx') = nvl(comp.pk2_value,'xxxxxxxxxxxxxxx')
85 AND comp_struct.obj_name = comp.obj_name
86 AND comp_struct.alternate_bom_designator = parent.alternate_bom_designator
87 );
88 BEGIN
89 --
90 -- Gross Weight will include packaging material.
91 -- but for the gross weight itself there is not special computation
92 --
93 null;
94
95 END Compute_Gross_Weight;
96
97 /*#
98 * This method will be used for computing the net_weight attributes value
99 * The method does not have any parameters, but it will have access to the
100 * attribute map or the current item in process.
101 * This should not be confused with the actual rollup function. This function
102 * helps the derivation of the net_weight attribute for that particulat item
103 * whereas rollup function of net_weight would take into consideration all the
104 * child components of the current item.
105 *
106 * @rep:scope private
107 * @rep:lifecycle active
108 * @rep:displayname Rollup Net weight
109 */
110 PROCEDURE Rollup_Net_Weight
111 (p_header_item_id IN NUMBER DEFAULT NULL
112 ,p_organization_id IN NUMBER DEFAULT NULL
113 ,p_validate IN VARCHAR2
114 ,p_halt_on_error IN VARCHAR2
115 ,x_return_status OUT NOCOPY VARCHAR2
116 ,x_error_message OUT NOCOPY VARCHAR2
117 )
118 IS
119 l_net_weight NUMBER := null;
120 l_unit_weight NUMBER := null;
121 l_qty NUMBER := 0;
122 l_net_weight_uom varchar2(30) := null;
123 l_net_wt_uom varchar2(30);
124 l_Comp_Attrs Bom_Rollup_Pub.Attribute_Map;
125 l_CAttrs_Map Bom_Rollup_Pub.Component_Seq_Attrs_Tbl;
126 BEGIN
127 --
128 -- Net Weight rollup happens as the following equation:
129 -- assembly item wt = 1..n[sum(all gtin components)]
130 --
131 IF (Bom_Rollup_Pub.l_Component_Seq_Tbl.COUNT = 0)
132 THEN
133 -- If it is not an each, reset weight attributes
134 IF (Bom_Rollup_Pub.Get_Trade_Item_Unit_Descriptor(p_header_item_id, p_organization_id) <> 'BASE_UNIT_OR_EACH') THEN
135
136 Bom_Rollup_Pub.Set_Parent_Attribute('UNIT_WEIGHT',null);
137 Bom_Rollup_Pub.Set_Parent_Attribute('NET_WEIGHT_UOM',null);
138
139 END IF;
140
141 RETURN;
142 END IF;
143
144 -- Ensure that this item is UCCNet enabled
145 IF (Bom_Rollup_Pub.Is_Pack_Item(p_header_item_id, p_organization_id) <> 'Y')
146 THEN
147 RETURN;
148 END IF;
149
150 FOR cmp_index IN Bom_Rollup_Pub.l_Component_Seq_Tbl.FIRST..Bom_Rollup_Pub.l_Component_Seq_Tbl.LAST
151 LOOP
152 IF Bom_Rollup_Pub.l_Component_Seq_Tbl.EXISTS(cmp_index)
153 THEN
154 Bom_Rollup_Pub.Write_Debug_Log(Bom_Rollup_Pub.G_BO_IDENTIFIER, 'Entering GDSN Check');
155 -- Only GDSN Items should be accounted bug # 4359090
156 IF (Bom_Rollup_Pub.Is_UCCNet_Enabled(
157 Bom_Rollup_Pub.l_Component_Seq_Tbl(cmp_index).component_item_id,
158 p_organization_id)
159 = 'Y')
160 THEN
161 IF p_validate = 'Y' THEN
162 Bom_Gtin_Rules.Check_GTIN_Attributes
163 ( p_assembly_item_id => p_header_item_id
164 , p_organization_id => p_organization_id
165 , p_component_item_id => Bom_Rollup_Pub.l_Component_Seq_Tbl(cmp_index).component_item_id
166 , p_ignore_published => 'Y'
167 , x_return_status => x_return_status
168 , x_error_message => x_error_message
169 );
170 Bom_Rollup_Pub.WRITE_DEBUG_LOG(Bom_Rollup_Pub.G_BO_IDENTIFIER,
171 'Check gtin attribs called for parent '||p_header_item_id||
172 ' child '||Bom_Rollup_Pub.l_Component_Seq_Tbl(cmp_index).component_item_id||', returned '||
173 x_return_status||':'||x_error_message);
174
175 IF p_halt_on_error = 'Y' AND
176 x_return_status IS NOT NULL AND
177 x_return_status <> 'S'
178 THEN
179
180 -- error is passed up call stack in x_error_message
181 RETURN;
182
183 END IF;
184 END IF;
185
186
187 l_unit_weight := to_number(Bom_Rollup_Pub.Get_Attribute_Value
188 ( p_component_sequence_id=> Bom_Rollup_Pub.l_Component_Seq_Tbl(cmp_index).component_sequence_id
189 , p_attribute_name => 'UNIT_WEIGHT'
190 )
191 );
192
193 l_qty := to_number(Bom_Rollup_Pub.Get_Attribute_Value
194 ( p_component_sequence_id=> Bom_Rollup_Pub.l_Component_Seq_Tbl(cmp_index).component_sequence_id
195 , p_attribute_name => 'COMPONENT_QUANTITY'
196 )
197 );
198
199 l_net_wt_uom := Bom_Rollup_Pub.Get_Attribute_Value
200 ( p_component_sequence_id=> Bom_Rollup_Pub.l_Component_Seq_Tbl(cmp_index).component_sequence_id
201 , p_attribute_name => 'NET_WEIGHT_UOM'
202 );
203
204 --l_net_weight := l_net_weight + (l_qty * l_unit_weight);
205
206 IF l_unit_weight IS NOT NULL THEN
207 IF l_net_weight IS NULL THEN
208 l_net_weight := l_qty * l_unit_weight;
209 ELSE
210 l_net_weight := l_net_weight + (l_qty * l_unit_weight);
211 END IF;
212 END IF;
213 IF l_net_wt_uom IS NOT NULL THEN
214 l_net_weight_uom := l_net_wt_uom;
215 END IF;
216
217 END IF; --GDSN CHECK
218 END IF; -- make sure to retrieve the collection only if exists a row.
219 END LOOP;
220
221 Bom_Rollup_Pub.Set_Parent_Attribute('UNIT_WEIGHT',l_net_weight);
222 Bom_Rollup_Pub.Set_Parent_Attribute('NET_WEIGHT_UOM',l_net_weight_uom);
223
224 END Rollup_Net_Weight;
225
226
227 /*#
228 * This method will be used for computing the gross-weight attributes value
229 * The method does not have any parameters, but it will have access to the
230 * attribute map or the current item in process.
231 * This should not be confused with the actual rollup function. This function
232 * helps the derivation of the net_weight attribute for that particulat item
233 * whereas rollup function of net_weight would take into consideration all the
234 * child components of the current item.
235 *
236 * @rep:scope private
237 * @rep:lifecycle active
238 * @rep:displayname Rollup Gross weight
239 */
240 PROCEDURE Rollup_Gross_Weight
241 (p_header_item_id IN NUMBER DEFAULT NULL
242 ,p_organization_id IN NUMBER DEFAULT NULL
243 ,p_validate IN VARCHAR2
244 ,p_halt_on_error IN VARCHAR2
245 ,x_return_status OUT NOCOPY VARCHAR2
246 ,x_error_message OUT NOCOPY VARCHAR2
247 )
248 IS
249 l_unit_weight NUMBER := null;
250 l_qty NUMBER :=0;
251 l_gross_weight NUMBER := null;
252 l_gross_wt NUMBER := null;
253 l_gross_nwt_uom varchar2(30);
254 l_gross_gwt_uom varchar2(30);
255 BEGIN
256 --
257 -- Gross weight computations does not exclude the packaging material
258 -- When registered as a function of a packaging structure, this can be modified
259 -- to care for any specific requirement. but for now this does not seem to be the case.
260 --
261
262 --
263 -- once we migrate some of the rollup computation to the middle tier
264 -- we should be able to efficiently use the attribute name itself
265 -- as the key. Currently the kep for the map is a sequence, which will
266 -- require us to iterate through the map to find an attribute.
267 --
268
269 --
270 -- assembly item wt = 1..n[sum(of all components)]
271 --
272 IF (Bom_Rollup_Pub.l_Component_Seq_Tbl.COUNT = 0)
273 THEN
274 -- If it is not an each, reset weight attributes
275 IF (Bom_Rollup_Pub.Get_Trade_Item_Unit_Descriptor(p_header_item_id, p_organization_id) <> 'BASE_UNIT_OR_EACH') THEN
276
277 Bom_Rollup_Pub.Set_Parent_Attribute('GROSS_WEIGHT',null);
278 Bom_Rollup_Pub.Set_Parent_Attribute('GROSS_WEIGHT_UOM',null);
279
280 END IF;
281
282 RETURN;
283 END IF;
284
285 -- Ensure that this item is UCCNet enabled
286 IF (Bom_Rollup_Pub.Is_UCCNet_Enabled(p_header_item_id, p_organization_id) <> 'Y')
287 THEN
288 RETURN;
289 END IF;
290
291 FOR cmp_index IN Bom_Rollup_Pub.l_Component_Seq_Tbl.FIRST..Bom_Rollup_Pub.l_Component_Seq_Tbl.LAST
292 LOOP
293 IF Bom_Rollup_Pub.l_Component_Seq_Tbl.EXISTS(cmp_index)
294 THEN
295
296 IF p_validate = 'Y' THEN
297
298 Bom_Gtin_Rules.Check_GTIN_Attributes
299 ( p_assembly_item_id => p_header_item_id
300 , p_organization_id => p_organization_id
301 , p_component_item_id => Bom_Rollup_Pub.l_Component_Seq_Tbl(cmp_index).component_item_id
302 , p_ignore_published => 'Y'
303 , x_return_status => x_return_status
304 , x_error_message => x_error_message
305 );
306 Bom_Rollup_Pub.WRITE_DEBUG_LOG(Bom_Rollup_Pub.G_BO_IDENTIFIER,
307 'Check gtin attribs called for parent '||p_header_item_id||
308 ' child '||Bom_Rollup_Pub.l_Component_Seq_Tbl(cmp_index).component_item_id||', returned '||
309 x_return_status||':'||x_error_message);
310
311 IF p_halt_on_error = 'Y' AND
312 x_return_status IS NOT NULL AND
313 x_return_status <> 'S'
314 THEN
315
316 -- error is passed up call stack in x_error_message
317 RETURN;
318
319 END IF;
320
321 END IF;
322
323
324 l_unit_weight :=
325 to_number(Bom_Rollup_Pub.Get_Attribute_Value
326 ( p_component_sequence_id=> Bom_Rollup_Pub.l_Component_Seq_Tbl(cmp_index).component_sequence_id
327 , p_attribute_name => 'UNIT_WEIGHT'
328 )
329 );
330 l_gross_wt :=
331 to_number(Bom_Rollup_Pub.Get_Attribute_Value
332 ( p_component_sequence_id=> Bom_Rollup_Pub.l_Component_Seq_Tbl(cmp_index).component_sequence_id
333 , p_attribute_name => 'GROSS_WEIGHT'
334 )
335 );
336 l_qty :=
337 to_number(Bom_Rollup_Pub.Get_Attribute_Value
338 ( p_component_sequence_id=> Bom_Rollup_Pub.l_Component_Seq_Tbl(cmp_index).component_sequence_id
339 , p_attribute_name => 'COMPONENT_QUANTITY'
340 )
341 );
342 l_gross_nwt_uom := Bom_Rollup_Pub.Get_Attribute_Value
343 ( p_component_sequence_id=> Bom_Rollup_Pub.l_Component_Seq_Tbl(cmp_index).component_sequence_id
344 , p_attribute_name => 'NET_WEIGHT_UOM'
345 );
346 l_gross_gwt_uom := Bom_Rollup_Pub.Get_Attribute_Value
347 ( p_component_sequence_id=> Bom_Rollup_Pub.l_Component_Seq_Tbl(cmp_index).component_sequence_id
348 , p_attribute_name => 'GROSS_WEIGHT_UOM'
349 );
350 /*
351 if ( (l_gross_wt is not null) and (l_gross_wt > 0) ) then
352 l_gross_weight := l_gross_weight + (l_qty * l_gross_wt);
353 else
354 l_gross_weight := l_gross_weight + (l_qty * l_unit_weight);
355 l_gross_gwt_uom := l_gross_nwt_uom;
356 end if;
357 */
358 IF (l_gross_wt is not null) and (l_gross_wt > 0) THEN
359 IF l_gross_weight IS NULL THEN
360 l_gross_weight := l_qty * l_gross_wt;
361 ELSE
362 l_gross_weight := l_gross_weight + (l_qty * l_gross_wt);
363 END IF;
364 ELSE
365 IF l_unit_weight IS NOT NULL THEN
366 IF l_gross_weight IS NULL THEN
367 l_gross_weight := l_qty * l_unit_weight;
368 ELSE
369 l_gross_weight := l_gross_weight + (l_qty * l_unit_weight);
370 END IF;
371 END IF;
372 l_gross_gwt_uom := l_gross_nwt_uom;
373 END IF;
374 END IF; -- fetch a collection row only if one exists.
375 END LOOP;
376
377
378
379 Bom_Rollup_Pub.Set_Parent_Attribute('GROSS_WEIGHT',l_gross_weight);
380 Bom_Rollup_Pub.Set_Parent_Attribute('GROSS_WEIGHT_UOM',l_gross_gwt_uom);
381
382 END Rollup_Gross_Weight;
383
384 /*#
385 * This method will help propogate the Private Flag.
386 * Private flag propogation has one rule. If a component is Private = Y, then the parent
387 * has to be Private.
388 * A Private child cannot have a non-private Parent.
389 *
390 * @rep:scope private
391 * @rep:lifecycle active
392 * @rep:displayname Rollup Private Flag
393 */
394
395 PROCEDURE Propogate_Private_Flag
396 (p_header_item_id IN NUMBER DEFAULT NULL
397 ,p_organization_id IN NUMBER DEFAULT NULL
398 ,p_validate IN VARCHAR2
399 ,p_halt_on_error IN VARCHAR2
400 ,x_return_status OUT NOCOPY VARCHAR2
401 ,x_error_message OUT NOCOPY VARCHAR2
402 )
403 IS
404 l_private_flag VARCHAR2(1) := 'N';
405 BEGIN
406
407 IF (Bom_Rollup_Pub.l_Component_Seq_Tbl.COUNT = 0)
408 THEN
409 -- If it is not an each, reset weight attributes
410 IF (Bom_Rollup_Pub.Get_Trade_Item_Unit_Descriptor(p_header_item_id, p_organization_id) <> 'BASE_UNIT_OR_EACH') THEN
411
412 Bom_Rollup_Pub.Set_Parent_Attribute('IS_TRADE_ITEM_INFO_PRIVATE',null);
413
414 END IF;
415
416 RETURN;
417 END IF;
418
419 -- Ensure that this item is UCCNet enabled
420 IF (Bom_Rollup_Pub.Is_UCCNet_Enabled(p_header_item_id, p_organization_id) <> 'Y')
421 THEN
422 RETURN;
423 END IF;
424
425 FOR cmp_index IN Bom_Rollup_Pub.l_Component_Seq_Tbl.FIRST..Bom_Rollup_Pub.l_Component_Seq_Tbl.LAST
426 LOOP
427 IF Bom_Rollup_Pub.l_Component_Seq_Tbl.EXISTS(cmp_index) AND
428 l_private_flag = 'N'
429 THEN
430 IF p_validate = 'Y' THEN
431
432 Bom_Gtin_Rules.Check_GTIN_Attributes
433 ( p_assembly_item_id => p_header_item_id
434 , p_organization_id => p_organization_id
435 , p_component_item_id => Bom_Rollup_Pub.l_Component_Seq_Tbl(cmp_index).component_item_id
436 , p_ignore_published => 'Y'
437 , x_return_status => x_return_status
438 , x_error_message => x_error_message
439 );
440 Bom_Rollup_Pub.WRITE_DEBUG_LOG(Bom_Rollup_Pub.G_BO_IDENTIFIER,
441 'Check gtin attribs called for parent '||p_header_item_id||
442 ' child '||Bom_Rollup_Pub.l_Component_Seq_Tbl(cmp_index).component_item_id||', returned '||
443 x_return_status||':'||x_error_message);
444
445 IF p_halt_on_error = 'Y' AND
446 x_return_status IS NOT NULL AND
447 x_return_status <> 'S'
448 THEN
449
450 -- error is passed up call stack in x_error_message
451 RETURN;
452
453 END IF;
454
455 END IF;
456
457 IF Bom_Rollup_Pub.Get_Attribute_Value
458 ( p_attribute_name => 'IS_TRADE_ITEM_INFO_PRIVATE'
459 , p_component_sequence_id => Bom_Rollup_Pub.l_Component_Seq_Tbl(cmp_index).component_sequence_id
460 ) = 'Y'
461 THEN
462 l_private_flag := 'Y';
463
464 END IF;
465 END IF; -- fetch a collection row only if one exists.
466 END LOOP;
467
468
469 --
470 -- set the private flag only if atleast 1 component is Private.
471 --
472 IF l_private_flag = 'Y'
473 THEN
474 Bom_Rollup_Pub.Set_Parent_Attribute('IS_TRADE_ITEM_INFO_PRIVATE',l_private_flag);
475 END IF;
476
477 END Propogate_Private_Flag;
478
479 PROCEDURE Propogate_Brand_Info
480 (p_header_item_id IN NUMBER DEFAULT NULL
481 ,p_organization_id IN NUMBER DEFAULT NULL
482 ,p_validate IN VARCHAR2
483 ,p_halt_on_error IN VARCHAR2
484 ,x_return_status OUT NOCOPY VARCHAR2
485 ,x_error_message OUT NOCOPY VARCHAR2
486 )
487 IS
488 l_brand_owner_name VARCHAR2(35) := '';
489 l_brand_owner_gln VARCHAR2(35) := '';
490 l_functional_name VARCHAR2(35) := '';
491 l_sub_brand VARCHAR2(35) := '';
492
493 BEGIN
494
495 IF (Bom_Rollup_Pub.l_Component_Seq_Attrs_Tbl.COUNT = 0)
496 THEN
497 -- If it is not an each, reset weight attributes
498 IF (Bom_Rollup_Pub.Get_Trade_Item_Unit_Descriptor(p_header_item_id, p_organization_id) <> 'BASE_UNIT_OR_EACH') THEN
499
500 Bom_Rollup_Pub.Set_Parent_Attribute('BRAND_OWNER_NAME',null);
501 Bom_Rollup_Pub.Set_Parent_Attribute('BRAND_OWNER_GLN',null);
502 Bom_Rollup_Pub.Set_Parent_Attribute('FUNCTIONAL_NAME',null);
503 Bom_Rollup_Pub.Set_Parent_Attribute('SUB_BRAND',null);
504
505 END IF;
506
507 RETURN;
508 END IF;
509
510 -- Ensure that this item is UCCNet enabled
511 IF (Bom_Rollup_Pub.Is_UCCNet_Enabled(p_header_item_id, p_organization_id) <> 'Y')
512 THEN
513 RETURN;
514 END IF;
515
516 IF p_validate = 'Y' THEN
517
518 FOR cmp_index IN Bom_Rollup_Pub.l_Component_Seq_Tbl.FIRST..Bom_Rollup_Pub.l_Component_Seq_Tbl.LAST
519 LOOP
520
521 IF Bom_Rollup_Pub.l_Component_Seq_Tbl.EXISTS(cmp_index)
522 THEN
523
524 Bom_Gtin_Rules.Check_GTIN_Attributes
525 ( p_assembly_item_id => p_header_item_id
526 , p_organization_id => p_organization_id
527 , p_component_item_id => Bom_Rollup_Pub.l_Component_Seq_Tbl(cmp_index).component_item_id
528 , p_ignore_published => 'Y'
529 , x_return_status => x_return_status
530 , x_error_message => x_error_message
531 );
532 Bom_Rollup_Pub.WRITE_DEBUG_LOG(Bom_Rollup_Pub.G_BO_IDENTIFIER,
533 'Check gtin attribs called for parent '||p_header_item_id||
534 ' child '||Bom_Rollup_Pub.l_Component_Seq_Tbl(cmp_index).component_item_id||', returned '||
535 x_return_status||':'||x_error_message);
536
537 IF p_halt_on_error = 'Y' AND
538 x_return_status IS NOT NULL AND
539 x_return_status <> 'S'
540 THEN
541
542 -- error is passed up call stack in x_error_message
543 RETURN;
544
545 END IF;
546
547 END IF;
548
549 END LOOP;
550
551 END IF;
552
553
554 FOR cmp_index IN Bom_Rollup_Pub.l_Component_Seq_Attrs_Tbl.FIRST..Bom_Rollup_Pub.l_Component_Seq_Attrs_Tbl.LAST
555 LOOP
556 IF Bom_Rollup_Pub.l_Component_Seq_Attrs_Tbl.EXISTS(cmp_index)
557 THEN
558 IF Bom_Rollup_Pub.l_Component_Seq_Attrs_Tbl(cmp_index).attribute_name =
559 'BRAND_OWNER_NAME'
560 THEN
561 l_brand_owner_name :=
562 Bom_Rollup_Pub.l_Component_Seq_Attrs_Tbl(cmp_index).attribute_value;
563 Bom_Rollup_Pub.Set_Parent_Attribute('BRAND_OWNER_NAME',l_brand_owner_name);
564 ELSIF Bom_Rollup_Pub.l_Component_Seq_Attrs_Tbl(cmp_index).attribute_name =
565 'BRAND_OWNER_GLN'
566 THEN
567 l_brand_owner_gln :=
568 Bom_Rollup_Pub.l_Component_Seq_Attrs_Tbl(cmp_index).attribute_value;
569 Bom_Rollup_Pub.Set_Parent_Attribute('BRAND_OWNER_GLN',l_brand_owner_gln);
570 ELSIF Bom_Rollup_Pub.l_Component_Seq_Attrs_Tbl(cmp_index).attribute_name =
571 'FUNCTIONAL_NAME'
572 THEN
573 l_functional_name :=
574 Bom_Rollup_Pub.l_Component_Seq_Attrs_Tbl(cmp_index).attribute_value;
575 Bom_Rollup_Pub.Set_Parent_Attribute('FUNCTIONAL_NAME',l_functional_name);
576 ELSIF Bom_Rollup_Pub.l_Component_Seq_Attrs_Tbl(cmp_index).attribute_name =
577 'SUB_BRAND'
578 THEN
579 l_sub_brand :=
580 Bom_Rollup_Pub.l_Component_Seq_Attrs_Tbl(cmp_index).attribute_value;
581 Bom_Rollup_Pub.Set_Parent_Attribute('SUB_BRAND',l_sub_brand);
582 END IF;
583 END IF; -- fetch a collection row only if one exists.
584 END LOOP;
585
586 --
587 -- Set the attributes
588 -- Open Design Issue: What happens in the case of a Hetrogenous Pack ?
589 -- possible solution is that the user assigns the attribute value for the parent
590 -- which hold the hierarchy. Propogation can start from that level above.
591 --
592
593 END Propogate_Brand_Info;
594
595
596 /*#
597 * This method will compute the TOP GTIN flag.
598 * Computation of TOP GTIN is based on 2 flags, Consumable and Orderable other than the fact
599 * that both the Parent Item and Component Item have to be GTINs.
600 * At any time there are atmost 2 rows that will be affected, one is the current row
601 * and second is the top item.
602 * Following matrix explains which combination evaluates to a Top GTIN flag of Yes:
603 * All other conditions evaluate to a Top GTIN flag = No.
604 * ---------------------------------------------------
605 * | Top Item Flag | Consumable | Orderable |
606 * ---------------------------------------------------
607 * | Yes | Component Item | Component Item |
608 * | -------------------------------------------------
609 * | Yes | Component Item | Top Item |
610 * | -------------------------------------------------
611 * | Yes | Top Item | Component Item |
612 * | -------------------------------------------------
613 * | Yes | Top Item | Top Item |
614 * | -------------------------------------------------
615 *
616 * @rep:scope public
617 * @rep:lifecycle active
618 * @rep:displayname Compute Top GTIN Flag
619 */
620 PROCEDURE Propogate_Top_GTIN_Flag
621 (p_header_item_id IN NUMBER DEFAULT NULL
622 ,p_organization_id IN NUMBER DEFAULT NULL
623 ,p_validate IN VARCHAR2
624 ,p_halt_on_error IN VARCHAR2
625 ,x_return_status OUT NOCOPY VARCHAR2
626 ,x_error_message OUT NOCOPY VARCHAR2
627 )
628 IS
629 CURSOR c_cons_ord_flag(p_item_id NUMBER) IS
630 SELECT is_trade_item_a_consumer_unit
631 , customer_order_enabled_flag
632 , Bom_Rollup_Pub.Is_UCCNet_Enabled(p_item_id, p_organization_id) is_uccnet
633 FROM ego_items_v
634 WHERE inventory_item_id = p_item_id
635 AND organization_id = p_organization_id;
636
637 CURSOR c_comp_cons_ord_flag(p_component_sequence_id NUMBER) IS
638 SELECT is_trade_item_a_consumer_unit
639 , customer_order_enabled_flag
640 , Bom_Rollup_Pub.Is_UCCNet_Enabled(bic.component_item_id, p_organization_id) is_uccnet
641 FROM ego_items_v
642 , bom_components_b bic
643 WHERE inventory_item_id = bic.component_item_id
644 AND organization_id = p_organization_id
645 AND bic.component_sequence_id = p_component_sequence_id;
646
647 l_top_gtin_flag VARCHAR2(1) := null;
648 l_top_gtin_in_map VARCHAR2(1);
649 l_top_consumable VARCHAR2(1);
650 l_top_orderable VARCHAR2(1);
651 is_top_uccnet VARCHAR2(1);
652 l_comp_consumable VARCHAR2(1);
653 l_comp_orderable VARCHAR2(1);
654 is_comp_uccnet VARCHAR2(1);
655 l_found_consumable VARCHAR2(1);
656 l_found_orderable VARCHAR2(1);
657 BEGIN
658
659 Bom_Rollup_Pub.WRITE_DEBUG_LOG ( p_bo_identifier => Bom_Rollup_Pub.G_BO_IDENTIFIER
660 , p_message => 'Inside TOP GTIN');
661
662 -- Ensure that this item is UCCNet enabled
663 IF (Bom_Rollup_Pub.Is_UCCNet_Enabled(p_header_item_id, p_organization_id) <> 'Y')
664 THEN
665 RETURN;
666 END IF;
667
668 l_top_gtin_in_map := Bom_Rollup_Pub.Get_Top_Item_Attribute_Value('TOP_GTIN');
669 IF l_top_gtin_in_map IS NOT NULL AND
670 l_top_gtin_in_map = 'Y'
671 THEN
672 RETURN;
673 -- if a prior level set the Top_Gtin to Y, then
674 -- simply return.
675 END IF;
676
677 --
678 -- Check if top item is a GTIN
679 --
680
681 FOR cons_or_ord_flag IN c_cons_ord_flag(p_item_id => Bom_Rollup_Pub.Get_Top_Item_Id)
682 LOOP
683 l_top_consumable := cons_or_ord_flag.is_trade_item_a_consumer_unit;
684 l_top_orderable := cons_or_ord_flag.customer_order_enabled_flag;
685 is_top_uccnet := cons_or_ord_flag.is_uccnet;
686 END LOOP;
687
688
689 Bom_Rollup_Pub.WRITE_DEBUG_LOG ( p_bo_identifier => Bom_Rollup_Pub.G_BO_IDENTIFIER
690 , p_message => 'l_top_consumable ' || l_top_consumable
691 || ' and l_top_orderable ' || l_top_orderable
692 || ' is_top_uccnet ' || is_top_uccnet);
693
694 -- bug 4043371: need to check if consumable and orderable are set anywhere in the hierarchy
695 -- use FOUND_CONSUMABLE and FOUND_ORDERABLE dummy flags to store these
696 l_found_consumable := Bom_Rollup_Pub.Get_Top_Item_Attribute_Value('FOUND_CONSUMABLE');
697 l_found_orderable := Bom_Rollup_Pub.Get_Top_Item_Attribute_Value('FOUND_ORDERABLE');
698 IF l_found_consumable IS NOT NULL THEN
699 l_top_consumable := l_found_consumable;
700 END IF;
701 IF l_found_orderable IS NOT NULL THEN
702 l_top_orderable := l_found_orderable;
703 END IF;
704
705 IF l_top_consumable = 'Y' AND
706 l_top_orderable = 'Y'
707 THEN
708 l_top_gtin_flag := 'Y';
709 --
710 -- This check and assignment will prevent any unnecessary
711 -- processing of the components
712 -- since the above satisfies the requirement for a TOP GTIN criteria
713 --
714 END IF;
715 --
716 -- Check if one of the above conditions in the
717 -- TOP GTIN matrix is true.
718 --
719
720 /* IF (Bom_Rollup_Pub.l_Component_Seq_Tbl.COUNT = 0)
721 THEN
722 return;
723 END IF;*/
724
725 Bom_Rollup_Pub.WRITE_DEBUG_LOG (Bom_Rollup_Pub.G_BO_IDENTIFIER, 'header item: '||p_header_item_id||
726 ' components: '||Bom_Rollup_Pub.l_Component_Seq_Tbl.COUNT || ' TOP_GTIN_FLAG ' || l_top_gtin_flag );
727
728 IF (Bom_Rollup_Pub.l_Component_Seq_Tbl.COUNT > 0) THEN
729 FOR cmp_index IN Bom_Rollup_Pub.l_Component_Seq_Tbl.FIRST..Bom_Rollup_Pub.l_Component_Seq_Tbl.LAST
730 LOOP
731 IF l_top_gtin_flag = 'Y'
732 THEN
733 exit;
734 END IF;
735
736 IF Bom_Rollup_Pub.l_Component_Seq_Tbl.EXISTS(cmp_index)
737 THEN
738
739 IF p_validate = 'Y' THEN
740
741 Bom_Gtin_Rules.Check_GTIN_Attributes
742 ( p_assembly_item_id => p_header_item_id
743 , p_organization_id => p_organization_id
744 , p_component_item_id => Bom_Rollup_Pub.l_Component_Seq_Tbl(cmp_index).component_item_id
745 , p_ignore_published => 'Y'
746 , x_return_status => x_return_status
747 , x_error_message => x_error_message
748 );
749 Bom_Rollup_Pub.WRITE_DEBUG_LOG(Bom_Rollup_Pub.G_BO_IDENTIFIER,
750 'Check gtin attribs called for parent '||p_header_item_id||
751 ' child '||Bom_Rollup_Pub.l_Component_Seq_Tbl(cmp_index).component_item_id||', returned '||
752 x_return_status||':'||x_error_message);
753
754 IF p_halt_on_error = 'Y' AND
755 x_return_status IS NOT NULL AND
756 x_return_status <> 'S'
757 THEN
758
759 -- error is passed up call stack in x_error_message
760 RETURN;
761
762 END IF;
763
764 END IF;
765
766 FOR comp_cons_or_ord_flag IN c_comp_cons_ord_flag(p_component_sequence_id =>
767 Bom_Rollup_Pub.l_Component_Seq_Tbl(cmp_index).component_sequence_id
768 )
769 LOOP
770 -- if consumable or orderable are already 'Y', don't look any further
771 is_comp_uccnet := comp_cons_or_ord_flag.is_uccnet;
772 IF is_comp_uccnet = 'Y' AND
773 (l_comp_consumable IS NULL OR l_comp_consumable <> 'Y') THEN
774 l_comp_consumable := comp_cons_or_ord_flag.is_trade_item_a_consumer_unit;
775 END IF;
776 IF is_comp_uccnet = 'Y' AND
777 (l_comp_orderable IS NULL OR l_comp_orderable <> 'Y') THEN
778 l_comp_orderable := comp_cons_or_ord_flag.customer_order_enabled_flag;
779 END IF;
780 Bom_Rollup_Pub.WRITE_DEBUG_LOG (Bom_Rollup_Pub.G_BO_IDENTIFIER,
781 'Within loop for compseq('||Bom_Rollup_Pub.l_Component_Seq_Tbl(cmp_index).component_sequence_id||
782 ') top: cons='||l_top_consumable||'/ord='||l_top_orderable||' comp: cons='||l_comp_consumable||'/ord='||
783 l_comp_orderable||'/ucc='||is_comp_uccnet);
784 END LOOP;
785 END IF; -- fetch a collection row only if one exists.
786
787 -- bug 4043371: if consumable and orderable are found anywhere in the hierarchy
788 -- store this in the top item's map
789 IF (l_comp_consumable = 'Y') THEN
790 Bom_Rollup_Pub.Set_Top_Item_Attribute('FOUND_CONSUMABLE',l_comp_consumable);
791 END IF;
792 IF (l_comp_orderable = 'Y') THEN
793 Bom_Rollup_Pub.Set_Top_Item_Attribute('FOUND_ORDERABLE',l_comp_orderable);
794 END IF;
795
796 --
797 -- We have the Component flags and the Top Item flags required for the matrix check
798 --
799 IF is_comp_uccnet = 'Y'
800 THEN
801 IF ( (l_comp_consumable = 'Y' AND
802 l_comp_orderable = 'Y'
803 ) OR
804 (l_comp_consumable = 'Y' AND
805 l_top_orderable = 'Y'
806 ) OR
807 (l_top_consumable = 'Y' AND
808 l_comp_orderable = 'Y'
809 ) OR
810 (l_top_consumable = 'Y' AND
811 l_top_orderable = 'Y'
812 )
813 )
814 THEN
815 l_top_gtin_flag := 'Y';
816 exit;
817 -- if the top level gtin is set, then the rest of the components can be
818 -- ignored.
819 END IF;
820 END IF;
821 END LOOP;
822 END IF;
823
824 --
825 -- Set the TOP GTIN attribute. Now, this function does not choose to set the attribute
826 -- in the central store, since the updates here do not affect the component or the parent
827 -- but only impacts the top level. So the value will be stored in the Top Attribute Map
828 --
829 --
830 -- If the Top_Item_Attribute Map is empty then store the value, otherwise write only if N since
831 -- a value of Y would mean that some level already evaluated the top item to be top_gtin Y.
832 -- This process will continue till the top and if none of the levels are able to evaluate the
833 -- condition to True, then the current top_item is not a GTIN.
834 --
835
836 IF l_top_gtin_in_map IS NULL
837 THEN
838 --
839 -- which means that this is the first subtree of the last level in the Tree
840 -- so create the row either Y or NULL, whatever is the value of l_top_gtin_flag
841 --
842 Bom_Rollup_Pub.Set_Top_Item_Attribute('TOP_GTIN',l_top_gtin_flag);
843
844 ELSIF l_top_gtin_in_map IS NOT NULL AND
845 l_top_gtin_in_map = 'N' AND
846 l_top_gtin_flag = 'Y'
847 THEN
848 Bom_Rollup_Pub.Set_Top_Item_Attribute('TOP_GTIN',l_top_gtin_flag);
849 END IF;
850
851 Bom_Rollup_Pub.WRITE_DEBUG_LOG (
852 p_bo_identifier => Bom_Rollup_Pub.G_BO_IDENTIFIER
853 , p_message => 'End TOP GTIN');
854 EXCEPTION
855 WHEN OTHERS THEN
856 Bom_Rollup_Pub.WRITE_DEBUG_LOG (Bom_Rollup_Pub.G_BO_IDENTIFIER, 'Exception in Top GTIN: '||sqlerrm);
857
858 END Propogate_Top_GTIN_Flag;
859
860
861 /*#
862 * This method will be used for computing the multirow attributes value
863 * The method does not have any parameters, but it will have access to the
864 * attr_diff_object or the current item in process.
865 * This at present will do the update of the multirow attrs
866 * Fixme once this is working
867 *
868 * @rep:scope private
869 * @rep:lifecycle active
870 * @rep:displayname Rollup Net weight
871 */
872 PROCEDURE Compute_Multi_Row_Attrs
873 (p_header_item_id IN NUMBER DEFAULT NULL
874 ,p_organization_id IN NUMBER DEFAULT NULL
875 ,p_validate IN VARCHAR2
876 ,p_halt_on_error IN VARCHAR2
877 ,x_return_status OUT NOCOPY VARCHAR2
878 ,x_error_message OUT NOCOPY VARCHAR2
879 )
880 IS
881 l_pk_column_values EGO_COL_NAME_VALUE_PAIR_ARRAY;
882 l_pk_column_value EGO_COL_NAME_VALUE_PAIR_OBJ;
883 l_error_message VARCHAR2(2000);
884 l_item_catalog_group_id NUMBER;
885 l_extension_ids EGO_NUMBER_TBL_TYPE;
886 l_cur_attr_diff_tbl EGO_USER_ATTR_DIFF_TABLE;
887 l_transaction_type VARCHAR2(10) := 'SYNC';
888 l_found_ext_id BOOLEAN := FALSE;
889
890 --Cursor for creating the classification code
891 Cursor get_classification_code
892 is
893 SELECT
894 item_catalog_group_id
895 FROM
896 mtl_system_items_b
897 WHERE
898 inventory_item_id = p_header_item_id
899 AND organization_id = p_organization_id;
900
901 CURSOR updatable_gtin_mulrow is
902 SELECT DISTINCT ATTR_GROUP_TYPE
903 , ATTR_GROUP_NAME
904 FROM
905 EGO_ATTRS_V A
906 WHERE
907 A.APPLICATION_ID = 431 --PERF BUG 4932131
908 AND A.ATTR_GROUP_TYPE = 'EGO_ITEM_GTIN_MULTI_ATTRS'
909 AND A.EDIT_IN_HIERARCHY_CODE IN ('LP');
910
911 CURSOR get_attr_group_id(p_attr_id NUMBER) IS
912 SELECT DISTINCT AG.ATTR_GROUP_ID
913 FROM
914 EGO_ATTRS_V A, EGO_ATTR_GROUPS_V AG
915 WHERE
916 A.APPLICATION_ID = AG.APPLICATION_ID
917 AND A.ATTR_GROUP_TYPE = AG.ATTR_GROUP_TYPE
918 AND A.ATTR_GROUP_NAME = AG.ATTR_GROUP_NAME
919 AND A.ATTR_GROUP_TYPE = 'EGO_ITEM_GTIN_MULTI_ATTRS'
920 AND A.EDIT_IN_HIERARCHY_CODE IN ('LP')
921 AND A.ATTR_ID = p_attr_id;
922 BEGIN
923
924 -- Ensure that this item is UCCNet enabled
925 IF (Bom_Rollup_Pub.Is_UCCNet_Enabled(p_header_item_id, p_organization_id) <> 'Y')
926 THEN
927 RETURN;
928 END IF;
929
930 IF (Bom_Rollup_Pub.l_Component_Seq_Tbl.COUNT = 0)
931 THEN
932 -- If it is not an each, reset weight attributes
933 IF (Bom_Rollup_Pub.Get_Trade_Item_Unit_Descriptor(p_header_item_id, p_organization_id) <> 'BASE_UNIT_OR_EACH') THEN
934
935 -- Handle resetting of multirow attributes in update_attributes
936 l_transaction_type := 'DELETE';
937
938 END IF;
939
940 END IF;
941
942 l_pk_column_values := EGO_COL_NAME_VALUE_PAIR_ARRAY(
943 EGO_COL_NAME_VALUE_PAIR_OBJ('INVENTORY_ITEM_ID', p_header_item_id)
944 ,EGO_COL_NAME_VALUE_PAIR_OBJ('ORGANIZATION_ID', p_organization_id)
945 );
946
947 --
948 -- When there is a change to the packaging hierarchy
949 -- the attr_diff object will not be passed
950 -- Hence if the count is zero we have to query the attr_diff object
951 --
952 IF (Bom_Rollup_Pub.g_attr_diffs IS NULL OR Bom_Rollup_Pub.g_attr_diffs.count <= 0)
953 THEN
954
955 Bom_Rollup_Pub.WRITE_DEBUG_LOG
956 ( p_bo_identifier => Bom_Rollup_Pub.G_BO_IDENTIFIER
957 , p_message => 'INSIDE Multi-Row Component Call tt='||l_transaction_type);
958 --Query the ATTR_DIFF object
959 --This will also give the l_class_code_name_value_pairs
960 -- and l_data_level_name_value_pairs
961
962 /*********FIX ME **************/
963 -- This has to be per attr_group basis, as the Update
964 -- can only handle one attr_group at a time
965 /*********FIX ME **************/
966 FOR c1 IN updatable_gtin_mulrow
967 LOOP
968 EGO_GTIN_PVT.Get_Attr_Diffs
969 (
970 p_inventory_item_id => p_header_item_id
971 , p_org_id => p_organization_id
972 , p_application_id => 431
973 , p_attr_group_type => c1.ATTR_GROUP_TYPE
974 , p_attr_group_name => c1.ATTR_GROUP_NAME
975 , px_attr_diffs => Bom_Rollup_Pub.g_attr_diffs
976 , px_pk_column_name_value_pairs => Bom_Rollup_Pub.g_pk_column_name_value_pairs
977 , px_class_code_name_value_pairs => Bom_Rollup_Pub.g_class_code_name_value_pairs
978 , px_data_level_name_value_pairs => Bom_Rollup_Pub.g_data_level_name_value_pairs
979 , x_error_message => l_error_message
980 );
981 -- for now just copy attr_diff.old to attr_diff.new, so calling update_attrs has no effect
982 --Make sure we call the update api only when multirow attrs are updated
983 -- bug: 4037735
984 IF (l_transaction_type <> 'DELETE'
985 AND Bom_Rollup_Pub.g_attr_diffs IS NOT NULL
986 AND Bom_Rollup_Pub.g_attr_diffs.count > 0) THEN
987 FOR i IN Bom_Rollup_Pub.g_attr_diffs.FIRST .. Bom_Rollup_Pub.g_attr_diffs.LAST
988 LOOP
989
990 Bom_Rollup_Pub.g_attr_diffs(i).NEW_ATTR_VALUE_STR :=
991 Bom_Rollup_Pub.g_attr_diffs(i).OLD_ATTR_VALUE_STR;
992 Bom_Rollup_Pub.g_attr_diffs(i).NEW_ATTR_VALUE_NUM :=
993 Bom_Rollup_Pub.g_attr_diffs(i).OLD_ATTR_VALUE_NUM;
994 Bom_Rollup_Pub.g_attr_diffs(i).NEW_ATTR_VALUE_DATE :=
995 Bom_Rollup_Pub.g_attr_diffs(i).OLD_ATTR_VALUE_DATE;
996 Bom_Rollup_Pub.g_attr_diffs(i).NEW_ATTR_UOM :=
997 Bom_Rollup_Pub.g_attr_diffs(i).OLD_ATTR_UOM;
998
999 END LOOP;
1000 END IF;--if Bom_Rollup_Pub.g_attr_diffs is not null
1001 END LOOP;
1002 ELSE
1003 --
1004 -- Get the classification code, this could be different as components
1005 -- in one hierarchy can be of different classification
1006 --
1007 FOR c1 IN get_classification_code
1008 LOOP
1009 l_item_catalog_group_id := c1.item_catalog_group_id;
1010 END LOOP;
1011
1012 Bom_Rollup_Pub.g_class_code_name_value_pairs :=
1013 EGO_COL_NAME_VALUE_PAIR_ARRAY
1014 (EGO_COL_NAME_VALUE_PAIR_OBJ('ITEM_CATALOG_GROUP_ID', to_char(l_item_catalog_group_id)));
1015
1016 END IF;
1017 --Call the update api only if Bom_Rollup_Pub.g_attr_diffs is not null.
1018 --bug: 4037735.
1019
1020 IF (Bom_Rollup_Pub.g_attr_diffs IS NOT NULL
1021 AND Bom_Rollup_Pub.g_attr_diffs.count > 0) THEN
1022
1023 -- Group diff objects by extension_id
1024 FOR i IN Bom_Rollup_Pub.g_attr_diffs.FIRST .. Bom_Rollup_Pub.g_attr_diffs.LAST
1025 LOOP
1026
1027 l_found_ext_id := FALSE;
1028
1029 IF l_extension_ids IS NOT NULL THEN
1030
1031 FOR j IN l_extension_ids.FIRST .. l_extension_ids.LAST
1032 LOOP
1033
1034 IF l_found_ext_id = FALSE AND
1035 l_extension_ids(j) = Bom_Rollup_Pub.g_attr_diffs(i).EXTENSION_ID
1036 THEN
1037
1038 l_found_ext_id := TRUE;
1039
1040 END IF;
1041
1042 END LOOP;
1043
1044 ELSE
1045
1046 l_extension_ids := EGO_NUMBER_TBL_TYPE();
1047
1048 END IF;
1049
1050 IF l_found_ext_id = FALSE
1051 THEN
1052
1053 l_extension_ids.EXTEND();
1054 l_extension_ids(l_extension_ids.LAST) := Bom_Rollup_Pub.g_attr_diffs(i).EXTENSION_ID;
1055
1056 END IF;
1057
1058 END LOOP;
1059
1060 -- Now iterate through the distinct ext ids, construct diff tables, and issue update_attrs call
1061 FOR i IN l_extension_ids.FIRST .. l_extension_ids.LAST
1062 LOOP
1063
1064 l_cur_attr_diff_tbl := EGO_USER_ATTR_DIFF_TABLE();
1065
1066 FOR j IN Bom_Rollup_Pub.g_attr_diffs.FIRST .. Bom_Rollup_Pub.g_attr_diffs.LAST
1067 LOOP
1068
1069 IF (l_extension_ids(i) IS NULL AND Bom_Rollup_Pub.g_attr_diffs(j).EXTENSION_ID IS NULL)
1070 OR l_extension_ids(i) = Bom_Rollup_Pub.g_attr_diffs(j).EXTENSION_ID THEN
1071
1072 l_cur_attr_diff_tbl.EXTEND();
1073 l_cur_attr_diff_tbl(l_cur_attr_diff_tbl.LAST) := Bom_Rollup_Pub.g_attr_diffs(j);
1074
1075 END IF;
1076
1077 END LOOP;
1078
1079 IF l_cur_attr_diff_tbl.COUNT > 0 THEN
1080
1081 FOR c1 IN get_attr_group_id(l_cur_attr_diff_tbl(l_cur_attr_diff_tbl.FIRST).ATTR_ID)
1082 LOOP
1083
1084 Bom_Rollup_Pub.WRITE_DEBUG_LOG (
1085 p_bo_identifier => Bom_Rollup_Pub.G_BO_IDENTIFIER
1086 , p_message => 'Calling Update Attributes');
1087 EGO_GTIN_PVT.Update_Attributes
1088 ( p_pk_column_name_value_pairs => l_pk_column_values
1089 , p_class_code_name_value_pairs => Bom_Rollup_Pub.g_class_code_name_value_pairs
1090 , p_data_level_name_value_pairs => Bom_Rollup_Pub.g_data_level_name_value_pairs
1091 , p_attr_diffs => l_cur_attr_diff_tbl
1092 , p_transaction_type => l_transaction_type
1093 , p_attr_group_id => c1.attr_group_id
1094 , x_error_message => l_error_message);
1095
1096 Bom_Rollup_Pub.WRITE_DEBUG_LOG (
1097 p_bo_identifier => Bom_Rollup_Pub.G_BO_IDENTIFIER
1098 , p_message => 'After Calling Update Attributes' || l_error_message);
1099
1100 END LOOP;
1101
1102 END IF;
1103
1104 END LOOP;
1105
1106 END IF;
1107 END Compute_Multi_Row_Attrs;
1108
1109 PROCEDURE Propagate_SH_Temps
1110 (p_header_item_id IN NUMBER DEFAULT NULL
1111 ,p_organization_id IN NUMBER DEFAULT NULL
1112 ,p_validate IN VARCHAR2
1113 ,p_halt_on_error IN VARCHAR2
1114 ,x_return_status OUT NOCOPY VARCHAR2
1115 ,x_error_message OUT NOCOPY VARCHAR2
1116 )
1117 IS
1118 l_sh_temp_min NUMBER;
1119 l_sh_temp_max NUMBER;
1120 l_uom_sh_temp_min VARCHAR2(30);
1121 l_uom_sh_temp_max VARCHAR2(30);
1122
1123 BEGIN
1124 Bom_Rollup_Pub.WRITE_DEBUG_LOG (
1125 p_bo_identifier => Bom_Rollup_Pub.G_BO_IDENTIFIER
1126 , p_message => 'Propagate_SH_Temps called for Item '||p_header_item_id||'-'||p_organization_id||
1127 ' with l_comp_attrs_map.count = '||Bom_Rollup_Pub.l_Component_Seq_Attrs_Tbl.COUNT);
1128
1129 IF (Bom_Rollup_Pub.l_Component_Seq_Attrs_Tbl.COUNT = 0)
1130 THEN
1131 -- If it is not an each, reset weight attributes
1132 IF (Bom_Rollup_Pub.Get_Trade_Item_Unit_Descriptor(p_header_item_id, p_organization_id) <> 'BASE_UNIT_OR_EACH') THEN
1133
1134 Bom_Rollup_Pub.Set_Parent_Attribute('STORAGE_HANDLING_TEMP_MIN',null);
1135 Bom_Rollup_Pub.Set_Parent_Attribute('STORAGE_HANDLING_TEMP_MAX',null);
1136 Bom_Rollup_Pub.Set_Parent_Attribute('UOM_STORAGE_HANDLING_TEMP_MIN',null);
1137 Bom_Rollup_Pub.Set_Parent_Attribute('UOM_STORAGE_HANDLING_TEMP_MAX',null);
1138
1139 END IF;
1140
1141 RETURN;
1142 END IF;
1143
1144 -- Ensure that this item is UCCNet enabled
1145 IF (Bom_Rollup_Pub.Is_UCCNet_Enabled(p_header_item_id, p_organization_id) <> 'Y')
1146 THEN
1147 RETURN;
1148 END IF;
1149
1150 IF p_validate = 'Y' THEN
1151
1152 FOR cmp_index IN Bom_Rollup_Pub.l_Component_Seq_Tbl.FIRST..Bom_Rollup_Pub.l_Component_Seq_Tbl.LAST
1153 LOOP
1154
1155 IF Bom_Rollup_Pub.l_Component_Seq_Tbl.EXISTS(cmp_index)
1156 THEN
1157
1158 Bom_Gtin_Rules.Check_GTIN_Attributes
1159 ( p_assembly_item_id => p_header_item_id
1160 , p_organization_id => p_organization_id
1161 , p_component_item_id => Bom_Rollup_Pub.l_Component_Seq_Tbl(cmp_index).component_item_id
1162 , p_ignore_published => 'Y'
1163 , x_return_status => x_return_status
1164 , x_error_message => x_error_message
1165 );
1166 Bom_Rollup_Pub.WRITE_DEBUG_LOG(Bom_Rollup_Pub.G_BO_IDENTIFIER,
1167 'Check gtin attribs called for parent '||p_header_item_id||
1168 ' child '||Bom_Rollup_Pub.l_Component_Seq_Tbl(cmp_index).component_item_id||', returned '||
1169 x_return_status||':'||x_error_message);
1170
1171 IF p_halt_on_error = 'Y' AND
1172 x_return_status IS NOT NULL AND
1173 x_return_status <> 'S'
1174 THEN
1175
1176 -- error is passed up call stack in x_error_message
1177 RETURN;
1178
1179 END IF;
1180
1181 END IF;
1182
1183 END LOOP;
1184
1185 END IF;
1186
1187 FOR cmp_index IN Bom_Rollup_Pub.l_Component_Seq_Attrs_Tbl.FIRST..Bom_Rollup_Pub.l_Component_Seq_Attrs_Tbl.LAST
1188 LOOP
1189 IF Bom_Rollup_Pub.l_Component_Seq_Attrs_Tbl.EXISTS(cmp_index)
1190 THEN
1191 IF Bom_Rollup_Pub.l_Component_Seq_Attrs_Tbl(cmp_index).attribute_name =
1192 'STORAGE_HANDLING_TEMP_MIN'
1193 THEN
1194 l_sh_temp_min :=
1195 Bom_Rollup_Pub.l_Component_Seq_Attrs_Tbl(cmp_index).attribute_value;
1196 Bom_Rollup_Pub.Set_Parent_Attribute('STORAGE_HANDLING_TEMP_MIN',l_sh_temp_min);
1197 ELSIF Bom_Rollup_Pub.l_Component_Seq_Attrs_Tbl(cmp_index).attribute_name =
1198 'STORAGE_HANDLING_TEMP_MAX'
1199 THEN
1200 l_sh_temp_max :=
1201 Bom_Rollup_Pub.l_Component_Seq_Attrs_Tbl(cmp_index).attribute_value;
1202 Bom_Rollup_Pub.Set_Parent_Attribute('STORAGE_HANDLING_TEMP_MAX',l_sh_temp_max);
1203 ELSIF Bom_Rollup_Pub.l_Component_Seq_Attrs_Tbl(cmp_index).attribute_name =
1204 'UOM_STORAGE_HANDLING_TEMP_MIN'
1205 THEN
1206 l_uom_sh_temp_min :=
1207 Bom_Rollup_Pub.l_Component_Seq_Attrs_Tbl(cmp_index).attribute_value;
1208 Bom_Rollup_Pub.Set_Parent_Attribute('UOM_STORAGE_HANDLING_TEMP_MIN',l_uom_sh_temp_min);
1209 ELSIF Bom_Rollup_Pub.l_Component_Seq_Attrs_Tbl(cmp_index).attribute_name =
1210 'UOM_STORAGE_HANDLING_TEMP_MAX'
1211 THEN
1212 l_uom_sh_temp_max :=
1213 Bom_Rollup_Pub.l_Component_Seq_Attrs_Tbl(cmp_index).attribute_value;
1214 Bom_Rollup_Pub.Set_Parent_Attribute('UOM_STORAGE_HANDLING_TEMP_MAX',l_uom_sh_temp_max);
1215 END IF;
1216 END IF; -- fetch a collection row only if one exists.
1217 END LOOP;
1218
1219 --
1220 -- Set the attributes
1221 --
1222 Bom_Rollup_Pub.WRITE_DEBUG_LOG (
1223 p_bo_identifier => Bom_Rollup_Pub.G_BO_IDENTIFIER
1224 , p_message => 'Propagate_SH_Temps: setting temps to '||l_sh_temp_min||'('||l_uom_sh_temp_min||'), '||l_sh_temp_max||'('||l_uom_sh_temp_max||')');
1225
1226 /* Moved the setting of attributes for Heterogenous pack for null check
1227 the null check is being done while getting the attribute */
1228
1229 EXCEPTION
1230 WHEN OTHERS THEN
1231 Bom_Rollup_Pub.WRITE_DEBUG_LOG (
1232 p_bo_identifier => Bom_Rollup_Pub.G_BO_IDENTIFIER
1233 , p_message => 'Propagate_SH_Temps: exception: '||sqlerrm);
1234
1235 END Propagate_SH_Temps;
1236
1237 PROCEDURE Set_User_Attributes
1238 ( p_item_id IN NUMBER
1239 , p_organization_id IN NUMBER
1240 , p_object_name IN VARCHAR2
1241 , p_application_id IN NUMBER
1242 , p_attr_group_type IN VARCHAR2
1243 , p_attr_group_name IN VARCHAR2
1244 , p_attr_name_value_pairs IN EGO_USER_ATTR_DATA_TABLE
1245 , x_return_status OUT NOCOPY VARCHAR2
1246 , x_msg_count OUT NOCOPY VARCHAR2
1247 , x_msg_data OUT NOCOPY VARCHAR2
1248 )
1249 IS
1250 l_pk_columns EGO_COL_NAME_VALUE_PAIR_ARRAY;
1251 l_class_code EGO_COL_NAME_VALUE_PAIR_ARRAY;
1252 l_data_level EGO_COL_NAME_VALUE_PAIR_ARRAY;
1253 l_item_catalog_group_id NUMBER;
1254 l_error_code NUMBER;
1255
1256 --Cursor for creating the classification code
1257 CURSOR get_classification_code
1258 IS
1259 SELECT
1260 item_catalog_group_id
1261 FROM
1262 mtl_system_items_b
1263 WHERE
1264 inventory_item_id = p_item_id
1265 AND organization_id = p_organization_id;
1266
1267 BEGIN
1268
1269 l_pk_columns := EGO_COL_NAME_VALUE_PAIR_ARRAY
1270 ( EGO_COL_NAME_VALUE_PAIR_OBJ('INVENTORY_ITEM_ID', p_item_id)
1271 , EGO_COL_NAME_VALUE_PAIR_OBJ('ORGANIZATION_ID', p_organization_id)
1272 );
1273
1274 FOR c1 IN get_classification_code
1275 LOOP
1276 l_item_catalog_group_id := c1.item_catalog_group_id;
1277 END LOOP;
1278
1279 l_class_code := EGO_COL_NAME_VALUE_PAIR_ARRAY
1280 (EGO_COL_NAME_VALUE_PAIR_OBJ('ITEM_CATALOG_GROUP_ID', to_char(l_item_catalog_group_id)));
1281
1282 l_data_level := EGO_COL_NAME_VALUE_PAIR_ARRAY
1283 (EGO_COL_NAME_VALUE_PAIR_OBJ('DATA_LEVEL', 'EGO_ITEM'));
1284
1285 EGO_USER_ATTRS_DATA_PVT.Perform_DML_On_Row(
1286 p_api_version => 1.0
1287 , p_object_name => 'EGO_ITEM'
1288 , p_application_id => p_application_id
1289 , p_attr_group_type => p_attr_group_type
1290 , p_attr_group_name => p_attr_group_name
1291 , p_pk_column_name_value_pairs => l_pk_columns
1292 , p_class_code_name_value_pairs => l_class_code
1293 -- , p_data_level_name_value_pairs => l_data_level
1294 , p_data_level_name_value_pairs => Bom_Rollup_Pub.g_data_level_name_value_pairs
1295 , p_attr_name_value_pairs => p_attr_name_value_pairs
1296 -- this is very important, because otherwise, updates would trigger rollups, creating an infinite loop
1297 , p_bulkload_flag => FND_API.G_TRUE
1298 , x_return_status => x_return_status
1299 , x_errorcode => l_error_code
1300 , x_msg_count => x_msg_count
1301 , x_msg_data => x_msg_data
1302 );
1303
1304 Bom_Rollup_Pub.WRITE_DEBUG_LOG (
1305 p_bo_identifier => Bom_Rollup_Pub.G_BO_IDENTIFIER
1306 , p_message => 'Set_User_Attribute: returned with '||x_return_status||' code '||l_error_code||' cnt '||x_msg_count||' data '||x_msg_data);
1307
1308 EXCEPTION
1309 WHEN OTHERS THEN
1310
1311 Bom_Rollup_Pub.WRITE_DEBUG_LOG (
1312 p_bo_identifier => Bom_Rollup_Pub.G_BO_IDENTIFIER
1313 , p_message => 'Set_User_Attribute: exception: '||sqlerrm);
1314
1315 END Set_User_Attributes;
1316
1317 PROCEDURE Get_Attribute_Value
1318 ( p_attr_name IN VARCHAR2
1319 , x_attr_value OUT NOCOPY VARCHAR2
1320 , x_found OUT NOCOPY BOOLEAN
1321 , p_header_item_map IN BOOLEAN := TRUE
1322 )
1323 IS
1324 l_attrs_map Bom_Rollup_Pub.Attribute_Map;
1325 l_attr_value varchar2(1000) := null;
1326 BEGIN
1327 x_found := FALSE;
1328
1329 IF p_header_item_map THEN
1330 l_attrs_map := Bom_Rollup_Pub.l_Header_Attrs_Map;
1331 ELSE
1332 l_attrs_map := Bom_Rollup_Pub.l_Top_Item_Attrs_Map;
1333 END IF;
1334
1335 IF l_attrs_map IS NULL OR
1336 NOT l_attrs_map.COUNT > 0
1337 THEN
1338 Bom_Rollup_Pub.WRITE_DEBUG_LOG(Bom_Rollup_Pub.G_BO_IDENTIFIER,'Get_Attribute_Value: map is null/empty, returning...');
1339 RETURN;
1340 END IF;
1341
1342 FOR i IN l_attrs_map.FIRST .. l_attrs_map.LAST
1343 LOOP
1344 IF l_attrs_map.EXISTS(i) THEN
1345 IF (l_attrs_map(i).attribute_name = p_attr_name)
1346 THEN
1347 if (l_attrs_map(i).attribute_value is not null) then
1348 l_attr_value := l_attrs_map(i).attribute_value;
1349 end if;
1350 x_found := TRUE;
1351 /* Storing the attrbute value in a local variable so that
1352 heterogenous packs are supported for null value
1353 If we have to do an average between values this is where we should be doing
1354 as it is a single change for all attributes
1355 */
1356
1357 --x_attr_value := l_attrs_map(i).attribute_value;
1358 --RETURN;
1359 Bom_Rollup_Pub.WRITE_DEBUG_LOG(Bom_Rollup_Pub.G_BO_IDENTIFIER,
1360 'The attribute value for ' || p_attr_name || ' is ' || l_attr_value );
1361 END IF;
1362 END IF;
1363 END LOOP;
1364
1365 /*if the attribute was found we will set it here */
1366 if (x_found) then
1367 x_attr_value := l_attr_value;
1368 end if;
1369
1370 END Get_Attribute_Value;
1371
1372 PROCEDURE Get_Top_Item_Attribute_Value
1373 ( p_attr_name IN VARCHAR2
1374 , x_attr_value OUT NOCOPY VARCHAR2
1375 , x_found OUT NOCOPY BOOLEAN
1376 )
1377 IS
1378 BEGIN
1379 Get_Attribute_Value(p_attr_name, x_attr_value, x_found, TRUE);
1380 END Get_Top_Item_Attribute_Value;
1381
1382 PROCEDURE Set_Net_Weight
1383 ( p_Header_Item_Id IN NUMBER
1384 , p_Organization_Id IN NUMBER
1385 , p_Header_Attrs_Flag IN VARCHAR2
1386 , x_return_status OUT NOCOPY VARCHAR2
1387 , x_msg_count OUT NOCOPY NUMBER
1388 , x_msg_data OUT NOCOPY VARCHAR2
1389 )
1390 IS
1391 l_unit_weight VARCHAR2(100) := NULL;
1392 l_net_weight_uom VARCHAR2(30) := NULL;
1393 l_unit_weight_found BOOLEAN;
1394 l_net_weight_uom_found BOOLEAN;
1395 l_use_header_attrs_map BOOLEAN := TRUE;
1396
1397 l_attr_name VARCHAR2(30);
1398 l_errorcode NUMBER;
1399 BEGIN
1400 Bom_Rollup_Pub.WRITE_DEBUG_LOG(Bom_Rollup_Pub.G_BO_IDENTIFIER,'Set_Net_Weight: starting '||p_header_attrs_flag);
1401
1402 IF p_header_attrs_flag <> 'Y' THEN
1403 l_use_header_attrs_map := FALSE;
1404 END IF;
1405
1406 Get_Attribute_Value('UNIT_WEIGHT', l_unit_weight, l_unit_weight_found, l_use_header_attrs_map);
1407 Get_Attribute_Value('NET_WEIGHT_UOM', l_net_weight_uom, l_net_weight_uom_found, l_use_header_attrs_map);
1408
1409 IF l_unit_weight_found THEN
1410
1411 l_attr_name := 'Unit_Weight';
1412
1413 ELSIF l_net_weight_uom_found THEN
1414
1415 l_attr_name := 'Weight_Uom_Code';
1416
1417 END IF;
1418
1419 IF l_unit_weight_found OR l_net_weight_uom_found THEN
1420
1421 EGO_GTIN_PVT.Update_Attribute
1422 ( p_inventory_item_id => p_header_item_id
1423 , p_organization_id => p_organization_id
1424 , p_attr_name => l_attr_name
1425 , p_attr_new_value_num => to_number(l_unit_weight)
1426 , p_attr_new_value_uom => l_net_weight_uom
1427 , x_return_status => x_return_status
1428 , x_errorcode => l_errorcode
1429 , x_msg_count => x_msg_count
1430 , x_msg_data => x_msg_data
1431 );
1432
1433 END IF;
1434
1435 EXCEPTION
1436 WHEN OTHERS THEN
1437
1438 Bom_Rollup_Pub.WRITE_DEBUG_LOG (
1439 p_bo_identifier => Bom_Rollup_Pub.G_BO_IDENTIFIER
1440 , p_message => 'Set_Net_Weight: exception: '||sqlerrm);
1441
1442 END Set_Net_Weight;
1443
1444 PROCEDURE Set_Private_Flag
1445 ( p_Header_Item_Id IN NUMBER
1446 , p_Organization_Id IN NUMBER
1447 , p_Header_Attrs_Flag IN VARCHAR2
1448 , x_return_status OUT NOCOPY VARCHAR2
1449 , x_msg_count OUT NOCOPY NUMBER
1450 , x_msg_data OUT NOCOPY VARCHAR2
1451 )
1452 IS
1453 l_private_flag VARCHAR2(1);
1454 l_private_flag_found BOOLEAN;
1455 l_attr_values EGO_USER_ATTR_DATA_TABLE := NULL;
1456 l_use_header_attrs_map BOOLEAN := TRUE;
1457 l_errorcode NUMBER;
1458 BEGIN
1459
1460 Bom_Rollup_Pub.WRITE_DEBUG_LOG(Bom_Rollup_Pub.G_BO_IDENTIFIER,'Set_Private_Flag: starting '||p_header_attrs_flag);
1461
1462 IF p_header_attrs_flag <> 'Y' THEN
1463 l_use_header_attrs_map := FALSE;
1464 END IF;
1465
1466 Get_Attribute_Value('IS_TRADE_ITEM_INFO_PRIVATE', l_private_flag, l_private_flag_found, l_use_header_attrs_map);
1467
1468 IF l_private_flag_found THEN
1469
1470 EGO_GTIN_PVT.Update_Attribute
1471 ( p_inventory_item_id => p_header_item_id
1472 , p_organization_id => p_organization_id
1473 , p_attr_name => 'Is_Trade_Item_Info_Private'
1474 , p_attr_group_type => 'EGO_ITEM_GTIN_ATTRS'
1475 , p_attr_group_name => 'Gtin_Unit_Indicator'
1476 , p_attr_new_value_str => l_private_flag
1477 , x_return_status => x_return_status
1478 , x_errorcode => l_errorcode
1479 , x_msg_count => x_msg_count
1480 , x_msg_data => x_msg_data
1481 );
1482
1483 END IF;
1484
1485 Bom_Rollup_Pub.WRITE_DEBUG_LOG(Bom_Rollup_Pub.G_BO_IDENTIFIER,'Set_Private_Flag: done with ret '||x_return_status);
1486
1487 EXCEPTION
1488 WHEN OTHERS THEN
1489
1490 Bom_Rollup_Pub.WRITE_DEBUG_LOG (
1491 p_bo_identifier => Bom_Rollup_Pub.G_BO_IDENTIFIER
1492 , p_message => 'Set_Net_Weight: exception: '||sqlerrm);
1493
1494 END Set_Private_Flag;
1495
1496 PROCEDURE Set_Brand_Info
1497 ( p_Header_Item_Id IN NUMBER
1498 , p_Organization_Id IN NUMBER
1499 , p_Header_Attrs_Flag IN VARCHAR2
1500 , x_return_status OUT NOCOPY VARCHAR2
1501 , x_msg_count OUT NOCOPY NUMBER
1502 , x_msg_data OUT NOCOPY VARCHAR2
1503 )
1504 IS
1505 l_brand_owner_name VARCHAR2(35);
1506 l_brand_owner_gln VARCHAR2(35);
1507 l_functional_name VARCHAR2(35);
1508 l_sub_brand VARCHAR2(35);
1509 l_found_bon BOOLEAN := FALSE;
1510 l_found_bog BOOLEAN := FALSE;
1511 l_found_fn BOOLEAN := FALSE;
1512 l_found_sb BOOLEAN := FALSE;
1513 l_use_header_attrs_map BOOLEAN := TRUE;
1514 l_errorcode NUMBER;
1515 BEGIN
1516
1517 Bom_Rollup_Pub.WRITE_DEBUG_LOG(Bom_Rollup_Pub.G_BO_IDENTIFIER,'Set_Brand_Info: starting '||p_header_attrs_flag);
1518
1519 IF p_header_attrs_flag <> 'Y' THEN
1520 l_use_header_attrs_map := FALSE;
1521 END IF;
1522
1523 -- for performance, we might want to expand this loop,
1524 -- especially if the map is long
1525 Get_Attribute_Value('BRAND_OWNER_NAME', l_brand_owner_name, l_found_bon, l_use_header_attrs_map);
1526 Get_Attribute_Value('BRAND_OWNER_GLN', l_brand_owner_gln, l_found_bog, l_use_header_attrs_map);
1527 Get_Attribute_Value('FUNCTIONAL_NAME', l_functional_name, l_found_fn, l_use_header_attrs_map);
1528 Get_Attribute_Value('SUB_BRAND', l_sub_brand, l_found_sb, l_use_header_attrs_map);
1529
1530 IF l_found_bon OR l_found_bog OR l_found_fn OR l_found_sb THEN
1531
1532 IF l_found_bon THEN
1533
1534 EGO_GTIN_PVT.Update_Attribute
1535 ( p_inventory_item_id => p_header_item_id
1536 , p_organization_id => p_organization_id
1537 , p_attr_name => 'Retail_Brand_Owner_Name'
1538 , p_attr_group_type => 'EGO_ITEM_GTIN_ATTRS'
1539 , p_attr_group_name => 'Trade_Item_Description'
1540 , p_attr_new_value_str => l_brand_owner_name
1541 , x_return_status => x_return_status
1542 , x_errorcode => l_errorcode
1543 , x_msg_count => x_msg_count
1544 , x_msg_data => x_msg_data
1545 );
1546
1547 END IF;
1548
1549 IF l_found_bog THEN
1550
1551 EGO_GTIN_PVT.Update_Attribute
1552 ( p_inventory_item_id => p_header_item_id
1553 , p_organization_id => p_organization_id
1554 , p_attr_name => 'Retail_Brand_Owner_Gln'
1555 , p_attr_group_type => 'EGO_ITEM_GTIN_ATTRS'
1556 , p_attr_group_name => 'Trade_Item_Description'
1557 , p_attr_new_value_str => l_brand_owner_gln
1558 , x_return_status => x_return_status
1559 , x_errorcode => l_errorcode
1560 , x_msg_count => x_msg_count
1561 , x_msg_data => x_msg_data
1562 );
1563
1564 END IF;
1565
1566 IF l_found_fn THEN
1567
1568 EGO_GTIN_PVT.Update_Attribute
1569 ( p_inventory_item_id => p_header_item_id
1570 , p_organization_id => p_organization_id
1571 , p_attr_name => 'Functional_Name'
1572 , p_attr_group_type => 'EGO_ITEM_GTIN_ATTRS'
1573 , p_attr_group_name => 'Trade_Item_Description'
1574 , p_attr_new_value_str => l_functional_name
1575 , x_return_status => x_return_status
1576 , x_errorcode => l_errorcode
1577 , x_msg_count => x_msg_count
1578 , x_msg_data => x_msg_data
1579 );
1580
1581 END IF;
1582
1583 IF l_found_sb THEN
1584
1585 EGO_GTIN_PVT.Update_Attribute
1586 ( p_inventory_item_id => p_header_item_id
1587 , p_organization_id => p_organization_id
1588 , p_attr_name => 'Sub_Brand'
1589 , p_attr_group_type => 'EGO_ITEM_GTIN_ATTRS'
1590 , p_attr_group_name => 'Trade_Item_Description'
1591 , p_attr_new_value_str => l_sub_brand
1592 , x_return_status => x_return_status
1593 , x_errorcode => l_errorcode
1594 , x_msg_count => x_msg_count
1595 , x_msg_data => x_msg_data
1596 );
1597
1598 END IF;
1599
1600 END IF;
1601
1602 Bom_Rollup_Pub.WRITE_DEBUG_LOG(Bom_Rollup_Pub.G_BO_IDENTIFIER,'Set_Brand_Info: done with ret '||x_return_status);
1603
1604 EXCEPTION
1605 WHEN OTHERS THEN
1606
1607 Bom_Rollup_Pub.WRITE_DEBUG_LOG (
1608 p_bo_identifier => Bom_Rollup_Pub.G_BO_IDENTIFIER
1609 , p_message => 'Set_Brand_Info: exception: '||sqlerrm);
1610
1611 END Set_Brand_Info;
1612
1613 PROCEDURE Set_Top_GTIN_Flag
1614 ( p_Header_Item_Id IN NUMBER
1615 , p_Organization_Id IN NUMBER
1616 , p_Header_Attrs_Flag IN VARCHAR2
1617 , x_return_status OUT NOCOPY VARCHAR2
1618 , x_msg_count OUT NOCOPY NUMBER
1619 , x_msg_data OUT NOCOPY VARCHAR2
1620 )
1621 IS
1622 l_top_gtin_flag VARCHAR2(1) := '';
1623 l_top_gtin_flag_found BOOLEAN;
1624 l_use_header_attrs_map BOOLEAN := TRUE;
1625 l_errorcode NUMBER;
1626 BEGIN
1627 -- TODO: check with deena
1628
1629 Bom_Rollup_Pub.WRITE_DEBUG_LOG(Bom_Rollup_Pub.G_BO_IDENTIFIER,'Set_Top_gtin_Flag: starting '||p_header_attrs_flag);
1630
1631 IF p_header_attrs_flag <> 'Y' THEN
1632 l_use_header_attrs_map := FALSE;
1633 END IF;
1634
1635 IF Bom_Rollup_Pub.Get_Top_Item_Id = p_header_item_id AND
1636 Bom_Rollup_Pub.Get_Top_Organization_Id = p_organization_id
1637 THEN
1638
1639 Get_Attribute_Value(
1640 'TOP_GTIN'
1641 , l_top_gtin_flag
1642 , l_top_gtin_flag_found
1643 , l_use_header_attrs_map);
1644
1645 IF l_top_gtin_flag_found THEN
1646
1647 Bom_Rollup_Pub.WRITE_DEBUG_LOG(Bom_Rollup_Pub.G_BO_IDENTIFIER,'Set_Top_gtin_Flag: calling EGO');
1648
1649 EGO_GTIN_PVT.Update_Attribute
1650 ( p_inventory_item_id => p_header_item_id
1651 , p_organization_id => p_organization_id
1652 , p_attr_name => 'Top_Gtin'
1653 , p_attr_new_value_str => l_top_gtin_flag
1654 , x_return_status => x_return_status
1655 , x_errorcode => l_errorcode
1656 , x_msg_count => x_msg_count
1657 , x_msg_data => x_msg_data
1658 );
1659
1660 END IF;
1661
1662 END IF;
1663
1664 Bom_Rollup_Pub.WRITE_DEBUG_LOG(Bom_Rollup_Pub.G_BO_IDENTIFIER,'Set_Top_Gtin_Flag: done with ret '||x_return_status);
1665
1666 EXCEPTION
1667 WHEN OTHERS THEN
1668
1669 Bom_Rollup_Pub.WRITE_DEBUG_LOG (
1670 p_bo_identifier => Bom_Rollup_Pub.G_BO_IDENTIFIER
1671 , p_message => 'Set_Top_Gtin_Flag: exception: '||sqlerrm);
1672
1673 END Set_Top_GTIN_Flag;
1674
1675 PROCEDURE Set_Multirow_Attributes
1676 ( p_Header_Item_Id IN NUMBER
1677 , p_Organization_Id IN NUMBER
1678 , p_Header_Attrs_Flag IN VARCHAR2
1679 , x_return_status OUT NOCOPY VARCHAR2
1680 , x_msg_count OUT NOCOPY NUMBER
1681 , x_msg_data OUT NOCOPY VARCHAR2
1682 )
1683 IS
1684 BEGIN
1685 NULL;
1686 END Set_Multirow_Attributes;
1687
1688 PROCEDURE Set_SH_Temps
1689 ( p_Header_Item_Id IN NUMBER
1690 , p_Organization_Id IN NUMBER
1691 , p_Header_Attrs_Flag IN VARCHAR2
1692 , x_return_status OUT NOCOPY VARCHAR2
1693 , x_msg_count OUT NOCOPY NUMBER
1694 , x_msg_data OUT NOCOPY VARCHAR2
1695 )
1696 IS
1697 l_sh_temp_min NUMBER(3);
1698 l_sh_temp_max NUMBER(3);
1699 l_sh_temp_min_str VARCHAR2(100);
1700 l_sh_temp_max_str VARCHAR2(100);
1701 l_sh_temp_min_uom VARCHAR2(3);
1702 l_sh_temp_max_uom VARCHAR2(3);
1703 l_found_sh_temp_min BOOLEAN := FALSE;
1704 l_found_sh_temp_max BOOLEAN := FALSE;
1705 l_found_sh_temp_min_uom BOOLEAN := FALSE;
1706 l_found_sh_temp_max_uom BOOLEAN := FALSE;
1707 l_use_header_attrs_map BOOLEAN := TRUE;
1708 l_errorcode NUMBER;
1709 BEGIN
1710
1711 Bom_Rollup_Pub.WRITE_DEBUG_LOG(Bom_Rollup_Pub.G_BO_IDENTIFIER,'Set_SH_Temps: starting '||p_header_attrs_flag);
1712
1713 IF p_header_attrs_flag <> 'Y' THEN
1714 l_use_header_attrs_map := FALSE;
1715 END IF;
1716
1717 -- for performance, we might want to expand this loop,
1718 -- especially if the map is long
1719 Get_Attribute_Value('STORAGE_HANDLING_TEMP_MIN', l_sh_temp_min_str, l_found_sh_temp_min, l_use_header_attrs_map);
1720 Get_Attribute_Value('STORAGE_HANDLING_TEMP_MAX', l_sh_temp_max_str, l_found_sh_temp_max, l_use_header_attrs_map);
1721 Get_Attribute_Value('UOM_STORAGE_HANDLING_TEMP_MIN', l_sh_temp_min_uom, l_found_sh_temp_min_uom, l_use_header_attrs_map);
1722 Get_Attribute_Value('UOM_STORAGE_HANDLING_TEMP_MAX', l_sh_temp_max_uom, l_found_sh_temp_max_uom, l_use_header_attrs_map);
1723
1724 IF l_found_sh_temp_min
1725 OR l_found_sh_temp_max
1726 OR l_found_sh_temp_min_uom
1727 OR l_found_sh_temp_max_uom
1728 THEN
1729
1730 IF l_found_sh_temp_min THEN
1731
1732 l_sh_temp_min := to_number(l_sh_temp_min_str);
1733
1734 EGO_GTIN_PVT.Update_Attribute
1735 ( p_inventory_item_id => p_header_item_id
1736 , p_organization_id => p_organization_id
1737 , p_attr_name => 'Uccnet_Storage_Temp_Min'
1738 , p_attr_group_type => 'EGO_ITEM_GTIN_ATTRS'
1739 , p_attr_group_name => 'Temperature_Information'
1740 , p_attr_new_value_num => l_sh_temp_min
1741 , x_return_status => x_return_status
1742 , x_errorcode => l_errorcode
1743 , x_msg_count => x_msg_count
1744 , x_msg_data => x_msg_data
1745 );
1746
1747 END IF;
1748
1749 IF l_found_sh_temp_max THEN
1750
1751 l_sh_temp_max := to_number(l_sh_temp_max_str);
1752
1753 EGO_GTIN_PVT.Update_Attribute
1754 ( p_inventory_item_id => p_header_item_id
1755 , p_organization_id => p_organization_id
1756 , p_attr_name => 'Uccnet_Storage_Temp_Max'
1757 , p_attr_group_type => 'EGO_ITEM_GTIN_ATTRS'
1758 , p_attr_group_name => 'Temperature_Information'
1759 , p_attr_new_value_num => l_sh_temp_max
1760 , x_return_status => x_return_status
1761 , x_errorcode => l_errorcode
1762 , x_msg_count => x_msg_count
1763 , x_msg_data => x_msg_data
1764 );
1765
1766 END IF;
1767
1768 IF l_found_sh_temp_min_uom THEN
1769
1770 EGO_GTIN_PVT.Update_Attribute
1771 ( p_inventory_item_id => p_header_item_id
1772 , p_organization_id => p_organization_id
1773 , p_attr_name => 'Uom_Storage_Handling_Temp_Min'
1774 , p_attr_group_type => 'EGO_ITEM_GTIN_ATTRS'
1775 , p_attr_group_name => 'Temperature_Information'
1776 , p_attr_new_value_str => l_sh_temp_min_uom
1777 , x_return_status => x_return_status
1778 , x_errorcode => l_errorcode
1779 , x_msg_count => x_msg_count
1780 , x_msg_data => x_msg_data
1781 );
1782
1783 END IF;
1784
1785 IF l_found_sh_temp_max_uom THEN
1786
1787 EGO_GTIN_PVT.Update_Attribute
1788 ( p_inventory_item_id => p_header_item_id
1789 , p_organization_id => p_organization_id
1790 , p_attr_name => 'Uom_Storage_Handling_Temp_Max'
1791 , p_attr_group_type => 'EGO_ITEM_GTIN_ATTRS'
1792 , p_attr_group_name => 'Temperature_Information'
1793 , p_attr_new_value_str => l_sh_temp_max_uom
1794 , x_return_status => x_return_status
1795 , x_errorcode => l_errorcode
1796 , x_msg_count => x_msg_count
1797 , x_msg_data => x_msg_data
1798 );
1799
1800 END IF;
1801
1802 END IF;
1803
1804 Bom_Rollup_Pub.WRITE_DEBUG_LOG(Bom_Rollup_Pub.G_BO_IDENTIFIER,'Set_SH_Temps: done with ret '||x_return_status);
1805
1806 END Set_SH_Temps;
1807
1808 END Bom_Compute_Functions;
[Home] [Help]