[Home] [Help]
PACKAGE BODY: APPS.BOM_GTIN_RULES
Source
1 PACKAGE BODY BOM_GTIN_Rules AS
2 /* $Header: BOMLGTNB.pls 120.11 2007/08/01 08:43:37 dikrishn ship $ */
3 /****************************************************************************
4 --
5 -- Copyright (c) 1996 Oracle Corporation, Redwood Shores, CA, USA
6 -- All rights reserved.
7 --
8 -- FILENAME
9 --
10 -- BOMLGTNB.pls
11 --
12 -- DESCRIPTION
13 --
14 -- Package body: BOM Validations for GTIN
15 --
16 -- NOTES
17 --
18 -- HISTORY
19 --
20 -- 18-MAY-04 Refai Farook Initial Creation
21 --
22 --
23 ****************************************************************************/
24
25 FUNCTION Get_Message
26 ( p_application_short_name IN VARCHAR2 := NULL
27 , p_message_name IN VARCHAR2 := NULL
28 , p_message_text IN VARCHAR2 := NULL
29 , p_api_name IN VARCHAR2 := NULL
30 ) RETURN VARCHAR2 IS
31
32 BEGIN
33
34 IF p_message_text IS NULL THEN
35 FND_MESSAGE.Set_Name (p_application_short_name, p_message_name);
36 --FND_MSG_PUB.Add;
37 ELSE
38 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR )
39 THEN
40 FND_MSG_PUB.Add_Exc_Msg
41 ( p_pkg_name => 'BOM_GTIN_Rules'
42 , p_procedure_name => p_api_name
43 , p_error_text => p_message_text
44 );
45 END IF;
46 END IF;
47
48 Return FND_MESSAGE.Get;
49
50 END;
51
52 FUNCTION Pack_Check(p_item_id IN NUMBER,p_org_id IN NUMBER)
53 RETURN VARCHAR2 IS
54
55 CURSOR Pack_Exist IS
56 SELECT bill_sequence_id
57 FROM bom_structures_b
58 WHERE assembly_item_id = p_item_id
59 AND organization_id = p_org_id
60 AND alternate_bom_designator = 'PIM_PBOM_S';
61
62 CURSOR Pack_Comp_Exist(l_bill_seq_id IN NUMBER) IS
63 SELECT 'Exist'
64 FROM bom_components_b
65 WHERE bill_sequence_id = l_bill_seq_id
66 AND (disable_date IS NULL OR disable_date > sysdate);
67
68 TYPE var_type IS TABLE OF VARCHAR2(20);
69 l_temp NUMBER;
70 l_exist VARCHAR2(5) := 'Y';
71 l_comp_exist var_type;
72
73 BEGIN
74
75 OPEN Pack_Exist;
76 FETCH Pack_Exist INTO l_temp;
77 IF l_temp IS NULL THEN
78 l_exist := 'N';
79 ELSE
80 OPEN Pack_Comp_Exist(l_temp);
81 FETCH Pack_Comp_Exist BULK COLLECT INTO l_comp_exist;
82 IF l_comp_exist.COUNT = 0 THEN
83 l_exist := 'N';
84 END IF;
85 CLOSE Pack_Comp_Exist;
86 END IF;
87 CLOSE Pack_Exist;
88
89 RETURN l_exist;
90
91 END;
92
93 /* Overloaded method with out ignore published status flag
94 * This will be the procedure called by all routines except for the rollup
95 * and will invoke the overloaded method with p_ignore_published as 'N'
96 */
97
98 PROCEDURE Check_GTIN_Attributes ( p_bill_sequence_id IN NUMBER := NULL,
99 p_assembly_item_id NUMBER,
100 p_organization_id IN NUMBER,
101 p_alternate_bom_code IN VARCHAR2 := NULL,
102 p_component_item_id IN NUMBER,
103 x_return_status OUT NOCOPY VARCHAR2,
104 x_error_message OUT NOCOPY VARCHAR2) IS
105 BEGIN
106 Check_GTIN_Attributes ( p_bill_sequence_id => p_bill_sequence_id
107 , p_assembly_item_id => p_assembly_item_id
108 , p_organization_id => p_organization_id
109 , p_alternate_bom_code => p_alternate_bom_code
110 , p_component_item_id => p_component_item_id
111 , p_ignore_published => 'N'
112 , x_return_status => x_return_status
113 , x_error_message => x_error_message );
114 END Check_GTIN_Attributes;
115
116
117 /* Overloaded method with ignore published status flag
118 * this will be called by the rollup with 'Y' as the p_ignore_published flag
119 * other calls will be routed through the overloaded procedure w.o.
120 * this flag, and will be passed as 'N'
121 */
122
123 PROCEDURE Check_GTIN_Attributes ( p_bill_sequence_id IN NUMBER := NULL,
124 p_assembly_item_id NUMBER,
125 p_organization_id IN NUMBER,
126 p_alternate_bom_code IN VARCHAR2 := NULL,
127 p_component_item_id IN NUMBER,
128 p_ignore_published IN VARCHAR2,
129 x_return_status OUT NOCOPY VARCHAR2,
130 x_error_message OUT NOCOPY VARCHAR2) IS
131
132 l_parent_tid VARCHAR2(35);
133 l_publication_status VARCHAR2(1);
134 l_top_gtin VARCHAR2(15);
135 l_parent_gtin VARCHAR2(25);
136 l_parent_uom_code VARCHAR2(3);
137 l_child_uom_code VARCHAR2(3);
138
139 l_component_tid VARCHAR2(35);
140 l_component_gtin VARCHAR2(25);
141
142 l_bill_sequence_id NUMBER;
143 l_compatible VARCHAR2(1);
144
145 CURSOR c_gtin_count(p_bill_sequence_id IN NUMBER) IS
146 SELECT count(DISTINCT egi.trade_item_descriptor) distinct_trade_count, count(DISTINCT egi.gtin) gtin_count,
147 count(egi.trade_item_descriptor) total_trade_count
148 FROM bom_components_b bic, ego_items_v egi
149 WHERE bic.bill_sequence_id = p_bill_sequence_id AND
150 bic.pk1_value = egi.inventory_item_id AND
151 bic.pk2_value = egi.organization_id AND
152 bic.effectivity_date <= SYSDATE AND
153 nvl(bic.disable_date, SYSDATE+1) > SYSDATE AND
154 egi.trade_item_descriptor IS NOT NULL;
155 --egi.gtin IS NOT NULL AND egi.cross_reference_type = 'GTIN';
156
157 BEGIN
158
159 x_return_status := 'S';
160
161 IF p_assembly_item_id IS NULL OR p_organization_id IS NULL OR p_component_item_id IS NULL
162 THEN
163 x_return_status := 'E';
164 x_error_message := 'Parameter error';
165 Return;
166 END IF;
167
168
169 /* Get the parent gtin, parent tid, published status, top_gtin flag value for the parent */
170
171 SELECT gtin,trade_item_descriptor,publication_status, top_gtin, primary_uom_code
172 INTO l_parent_gtin, l_parent_tid, l_publication_status, l_top_gtin, l_parent_uom_code
173 FROM ego_items_v
174 WHERE inventory_item_id = p_assembly_item_id AND
175 organization_id = p_organization_id;
176
177 /* Get the componet gtin, component tid value for the component */
178
179 SELECT gtin, trade_item_descriptor, primary_uom_code
180 INTO l_component_gtin, l_component_tid, l_child_uom_code
181 FROM ego_items_v
182 WHERE inventory_item_id = p_component_item_id AND
183 organization_id = p_organization_id;
184
185
186 /* Published GTINs cannot allow any changes on the GTIN
187 Once published the publication status will have some value other than null */
188
189 IF l_publication_status IS NOT NULL
190 THEN
191 IF l_component_gtin IS NOT NULL
192 THEN
193 IF (p_ignore_published is null OR p_ignore_published = 'N') THEN
194 x_return_status := 'E';
195 x_error_message := Get_Message('BOM','BOM_GTIN_BOM_PUBLISHED');
196 Return;
197 END IF;
198 END IF;
199 END IF;
200
201 /* Apply the validation rules */
202
203 /* Parent and child must belong to the same UOM class */
204
205 /* commented our bug 5639158 */
206 /* GET_UOM_CLASS_COMPATIBILITY(p_src_uom_code => l_parent_uom_code,
207 p_dest_uom_code => l_child_uom_code,
208 x_compatibility_status => l_compatible);
209 IF l_compatible = 'N'
210 THEN
211 x_return_status := 'E';
212 x_error_message := Get_Message('BOM','BOM_UOM_CLASS_DIFF');
213 END IF;
214 */
215
216 /* Get the parent bill seq */
217 IF p_bill_sequence_id IS NULL
218 THEN
219 IF (p_alternate_bom_code is NULL)
220 THEN
221 SELECT
222 bill_sequence_id INTO l_bill_sequence_id
223 FROM
224 bom_structures_b bsb, bom_structure_types_b bstb
225 WHERE
226 assembly_item_id = p_assembly_item_id
227 AND organization_id = p_organization_id
228 AND bsb.structure_type_id = bstb.structure_type_id
229 AND bstb.structure_type_name = 'Packaging Hierarchy'
230 AND bsb.is_preferred = 'Y';
231 ELSE
232 SELECT bill_sequence_id INTO l_bill_sequence_id FROM bom_structures_b WHERE
233 assembly_item_id = p_assembly_item_id AND
234 organization_id = p_organization_id AND
235 alternate_bom_designator = p_alternate_bom_code ;
236 END IF;
237 ELSE
238 l_bill_sequence_id := p_bill_sequence_id;
239 END IF;
240
241
242 /* Check for trade item unit descriptor compatibility between parent and child*/
243
244 /* Feb 03, 2006: Implemented the validation rules as per UCCNET 3.0*/
245
246 IF (l_parent_tid IS NULL OR l_component_tid IS NULL)
247 THEN
248 null;
249 ELSIF l_parent_tid IN ('MIXED_MODULE','PALLET')
250 THEN
251 IF l_component_tid NOT IN ('DISPLAY_SHIPPER','CASE','PACK_OR_INNER_PACK','BASE_UNIT_OR_EACH','SETPACK','MULTIPACK')
252 THEN
253 x_return_status := 'E';
254 x_error_message := Get_Message('BOM','BOM_GTIN_PL_CONFIG_INVALID');
255 END IF;
256 ELSIF l_parent_tid IN ('DISPLAY_SHIPPER')
257 THEN
258 IF l_component_tid NOT IN ('CASE','BASE_UNIT_OR_EACH','SETPACK','MULTIPACK')
259 THEN
260 x_return_status := 'E';
261 x_error_message := Get_Message('BOM','BOM_GTIN_DS_CONFIG_INVALID');
262 END IF;
263 ELSIF l_parent_tid IN ('CASE')
264 THEN
265 IF l_component_tid NOT IN ('BASE_UNIT_OR_EACH','PACK_OR_INNER_PACK','SETPACK','MULTIPACK')
266 THEN
267 x_return_status := 'E';
268 x_error_message := Get_Message('BOM','BOM_GTIN_CA_CONFIG_INVALID');
269 END IF;
270 ELSIF l_parent_tid IN ('PACK_OR_INNER_PACK')
271 THEN
272 IF l_component_tid NOT IN ('BASE_UNIT_OR_EACH','SETPACK')
273 THEN
274 x_return_status := 'E';
275 x_error_message := Get_Message('BOM','BOM_GTIN_PK_CONFIG_INVALID');
276 END IF;
277
278 ELSIF l_parent_tid IN ('PREPACK_ASSORTMENT')
279 THEN
280 IF l_component_tid NOT IN ('PREPACK','SETPACK','MULTIPACK')
281 THEN
282 x_return_status := 'E';
283 x_error_message := Get_Message('BOM','BOM_GTIN_PPKA_CONFIG_INVALID');
284 END IF;
285 ELSIF l_parent_tid IN ('PREPACK')
286 THEN
287 IF l_component_tid NOT IN ('BASE_UNIT_OR_EACH')
288 THEN
289 x_return_status := 'E';
290 x_error_message := Get_Message('BOM','BOM_GTIN_PPK_CONFIG_INVALID');
291 END IF;
292 ELSIF l_parent_tid IN ('SETPACK')
293 THEN
294 IF l_component_tid NOT IN ('BASE_UNIT_OR_EACH')
295 THEN
296 x_return_status := 'E';
297 x_error_message := Get_Message('BOM','BOM_GTIN_SPK_CONFIG_INVALID');
298 END IF;
299 ELSIF l_parent_tid IN ('MULTIPACK')
300 THEN
301 IF l_component_tid NOT IN ('PACK_OR_INNER_PACK','BASE_UNIT_OR_EACH')
302 THEN
303 x_return_status := 'E';
304 x_error_message := Get_Message('BOM','BOM_GTIN_MPK_CONFIG_INVALID');
305 END IF;
306 END IF;
307
308 IF x_return_status = 'E'
309 THEN
310 Return;
311 END IF;
312
313 /* Check for validity of multiple childrens */
314
315 IF (l_parent_tid IS NULL OR l_component_tid IS NULL)
316 THEN
317 null;
318 ELSIF l_parent_tid IN ('PALLET','CASE', 'PACK_OR_INNER_PACK')
319 THEN
320 /* PALLET: A pallet can have only one type of children instance.
321 It all could be DS or CA or PK or EA but not a mixture of these.
322 CASE: A case cannot have multiple gtins.
323 PACK_OR_INNER_PACK: Cannot have multiple gtins
324 */
325
326 FOR r1 IN c_gtin_count (l_bill_sequence_id)
327 LOOP
328 IF r1.total_trade_count > 1
329 THEN
330 x_return_status := 'E';
331 IF l_parent_tid = 'CASE'
332 THEN
333 x_error_message := Get_Message('BOM','BOM_GTIN_CA_MULTI_GTINS');
334 ELSIF l_parent_tid = 'PALLET'
335 THEN
336 x_error_message := Get_Message('BOM','BOM_GTIN_PL_MULTI_GTINS');
337 --bug: 4516894
338 ELSIF l_parent_tid = 'PACK_OR_INNER_PACK'
339 THEN
340 x_error_message := Get_Message('BOM','BOM_GTIN_PK_MULTI_GTINS');
341 END IF;
342 END IF;
343 /*
344 IF r1.distinct_trade_count > 1 AND l_parent_tid = 'PALLET'
345 THEN
346 x_return_status := 'E';
347 x_error_message := Get_Message('BOM','BOM_GTIN_PL_MULTI_TRADES');
348 ELSIF r1.total_trade_count > 1 AND l_parent_tid = 'CASE'
349 --ELSIF r1.gtin_count > 1 AND l_parent_tid = 'CASE'
350 THEN
351 x_return_status := 'E';
352 x_error_message := Get_Message('BOM','BOM_GTIN_CA_MULTI_GTINS');
353 END IF;
354 */
355 END LOOP;
356 ELSIF nvl(l_parent_tid,'-1') = 'BASE_UNIT_OR_EACH'
357 THEN
358 /* Each cannot have a BOM */
359 x_return_status := 'E';
360 x_error_message := Get_Message('BOM','BOM_GTIN_EACH_NO_BOM');
361 END IF;
362
363 END;
364
365 PROCEDURE Update_Top_GTIN( p_organization_id IN NUMBER,
366 p_component_item_id IN NUMBER,
367 p_parent_item_id in NUMBER := NULL,
368 p_structure_name in VARCHAR2 := NULL) IS
369 is_preferred_flag BOOLEAN := FALSE;
370 CURSOR c_Preferred_Structure(p_assembly_item_id in varchar2,
371 p_organization_id in varchar2,
372 p_structure_name in varchar2)
373 IS
374 SELECT
375 alternate_bom_designator
376 FROM
377 bom_structures_b
378 WHERE
379 assembly_item_id = p_assembly_item_id
380 AND organization_id = p_organization_id
381 AND alternate_bom_designator = p_structure_name
382 AND is_Preferred = 'Y';
383
384 BEGIN
385
386 IF ( p_structure_name IS NOT NULL) THEN
387 for c1 in c_Preferred_Structure(p_assembly_item_id => p_parent_item_id,
388 p_organization_id => p_organization_id,
389 p_structure_name => p_structure_name)
390 LOOP
391 is_preferred_flag := TRUE;
392 END LOOP;
393 END IF;
394 IF (is_preferred_flag) THEN
395 UPDATE EGO_ITEM_GTN_ATTRS_B
396 SET top_gtin = null
397 WHERE inventory_item_id = p_component_item_id AND
398 organization_id = p_organization_id;
399 END IF;
400 END;
401
402 /* Returns the uom conversion rate
403 Returns -99999 when any error occurs */
404
405 FUNCTION Get_Suggested_Quantity ( p_component_item_id IN NUMBER,
406 p_component_uom IN VARCHAR2,
407 p_assembly_uom IN VARCHAR2) RETURN NUMBER IS
408
409 l_uom_rate NUMBER;
410
411 BEGIN
412
413 l_uom_rate := INV_CONVERT.Inv_Um_Convert (p_component_item_id,
414 null, null,
415 p_assembly_uom,
416 p_component_uom,
417 null, null);
418 IF l_uom_rate = -99999
419 THEN
420 Return 0;
421 ELSE
422 Return l_uom_rate;
423 END IF;
424
425 END;
426
427 /*
428 FUNCTION Get_Suggested_Quantity ( p_component_item_id IN NUMBER,
429 p_component_uom_name IN VARCHAR2,
430 p_assembly_uom_name IN VARCHAR2) RETURN NUMBER IS
431 BEGIN
432
433 Return INV_CONVERT.Inv_Um_Convert (p_component_item_id,
434 null, null,
435 null, null,
436 p_component_uom_name,
437 p_assembly_uom_name);
438 END;
442 p_assembly_item_id NUMBER,
439 */
440
441 FUNCTION Get_Suggested_Quantity ( p_organization_id IN NUMBER,
443 p_component_item_id IN NUMBER ) RETURN NUMBER IS
444 l_component_uom VARCHAR2(3);
445 l_assembly_uom VARCHAR2(3);
446 l_uom_rate NUMBER;
447
448 BEGIN
449
450 SELECT primary_uom_code INTO l_component_uom FROM mtl_system_items_b WHERE
451 inventory_item_id = p_component_item_id AND
452 organization_id = p_organization_id;
453
454 SELECT primary_uom_code INTO l_assembly_uom FROM mtl_system_items_b WHERE
455 inventory_item_id = p_assembly_item_id AND
456 organization_id = p_organization_id;
457
458 l_uom_rate := INV_CONVERT.Inv_Um_Convert (p_component_item_id,
459 null, null,
460 l_assembly_uom,
461 l_component_uom,
462 null, null);
463 IF l_uom_rate = -99999
464 THEN
465 Return 0;
466 ELSE
467 Return l_uom_rate;
468 END IF;
469
470 EXCEPTION WHEN OTHERS THEN
471 Return 0;
472
473 END;
474
475 PROCEDURE Perform_Rollup
476 ( p_item_id IN NUMBER
477 , p_organization_id IN NUMBER
478 , p_parent_item_id IN NUMBER
479 , p_structure_type_name IN VARCHAR2
480 , p_transaction_type IN VARCHAR2
481 , p_validate IN VARCHAR2 /*DEFAULT 'N'*/
482 , p_halt_on_error IN VARCHAR2 /*DEFAULT 'N'*/
483 , p_structure_name IN VARCHAR2 := NULL
484 , x_error_message OUT NOCOPY VARCHAR2
485 ) IS
486
487 l_rollup_map Bom_Rollup_Pub.Rollup_Action_Map := Bom_Rollup_Pub.G_EMPTY_ACTION_MAP;
488 l_return_status varchar2(1) := 'S';
489 l_msg_count number := 0;
490 l_msg_data varchar2(3000) := null;
491
492 BEGIN
493 -- We are not considering the p_structure_name anymore to calculate the preferred structure
494 -- as items are also calling the same api and the callee doesn't have a structure name
495 IF p_transaction_type IN ('CREATE','DELETE')
496 THEN
497 IF (Bom_Rollup_Pub.Is_UCCNet_Enabled(p_item_id, p_organization_id) = 'Y')
498 THEN
499 Bom_Rollup_Pub.Add_Rollup_Function
500 ( p_Object_Name => 'EGO_ITEM'
501 , p_Rollup_Action => Bom_Rollup_Pub.G_COMPUTE_NET_WEIGHT
502 , p_DML_Function => 'Bom_Compute_Functions.Set_Net_Weight'
503 , p_DML_Delayed_Write => 'N'
504 , x_Rollup_Action_Map => l_rollup_map
505 );
506
507 Bom_Rollup_Pub.Add_Rollup_Function
508 ( p_Object_Name => 'EGO_ITEM'
509 , p_Rollup_Action => Bom_Rollup_Pub.G_PROPOGATE_BRAND_INFO
510 , p_DML_Function => 'Bom_Compute_Functions.Set_Brand_Info'
511 , p_DML_Delayed_Write => 'N'
512 , x_Rollup_Action_Map => l_rollup_map
513 );
514 Bom_Rollup_Pub.Add_Rollup_Function
515 ( p_Object_Name => 'EGO_ITEM'
516 , p_Rollup_Action => Bom_Rollup_Pub.G_COMPUTE_TOP_GTIN_FLAG
517 , p_DML_Function => 'Bom_Compute_Functions.Set_Top_GTIN_Flag'
518 , p_DML_Delayed_Write => 'N'
519 , x_Rollup_Action_Map => l_rollup_map
520 );
521
522 Bom_Rollup_Pub.Add_Rollup_Function
523 ( p_Object_Name => 'EGO_ITEM'
524 , p_Rollup_Action => Bom_Rollup_Pub.G_COMPUTE_MULTI_ROW_ATTRS
525 , p_DML_Function => 'Bom_Compute_Functions.Set_Multirow_Attributes'
526 , p_DML_Delayed_Write => 'N'
527 , x_Rollup_Action_Map => l_rollup_map
528 );
529
530 Bom_Rollup_Pub.Add_Rollup_Function
531 ( p_Object_Name => 'EGO_ITEM'
532 , p_Rollup_Action => Bom_Rollup_Pub.G_PROPAGATE_SH_TEMPS
533 , p_DML_Function => 'Bom_Compute_Functions.Set_SH_Temps'
534 , p_DML_Delayed_Write => 'N'
535 , x_Rollup_Action_Map => l_rollup_map
536 );
537 ELSIF (Bom_Rollup_Pub.Is_Pack_Item(p_item_id, p_organization_id) = 'Y')
538 THEN
539 Bom_Rollup_Pub.Add_Rollup_Function
540 ( p_Object_Name => 'EGO_ITEM'
541 , p_Rollup_Action => Bom_Rollup_Pub.G_COMPUTE_NET_WEIGHT
542 , p_DML_Function => 'Bom_Compute_Functions.Set_Net_Weight'
543 , p_DML_Delayed_Write => 'N'
544 , x_Rollup_Action_Map => l_rollup_map
545 );
546 ELSE
547 RETURN;
548 END IF;
549 END IF;
550
551 IF p_transaction_type IN ('UPDATE')
552 THEN
553 Bom_Rollup_Pub.Add_Rollup_Function
554 ( p_Object_Name => 'EGO_ITEM'
555 , p_Rollup_Action => Bom_Rollup_Pub.G_COMPUTE_NET_WEIGHT
556 , p_DML_Function => 'Bom_Compute_Functions.Set_Net_Weight'
557 , p_DML_Delayed_Write => 'N'
558 , x_Rollup_Action_Map => l_rollup_map
559 );
560 END IF;
561
562 IF p_transaction_type = 'DELETE' AND
563 p_parent_item_id IS NOT NULL
564 THEN
568 , p_structure_type_name => p_structure_type_name
565 Bom_Rollup_Pub.g_attr_diffs := null;
566 Bom_Rollup_Pub.Perform_Rollup( p_item_id => p_parent_item_id
567 , p_organization_id => p_organization_id
569 , p_action_map => l_rollup_map
570 , p_validate => p_validate
571 , p_halt_on_error => p_halt_on_error
572 , x_error_message => x_error_message );
573 -- note: because of the Items flow, we want to store attr_diffs
574 -- between calls to Perform_Rollup; however, in this case we want to clear them
575 Bom_Rollup_Pub.g_attr_diffs := null;
576 Bom_Rollup_Pub.Perform_Rollup( p_item_id => p_item_id
577 , p_organization_id => p_organization_id
578 , p_structure_type_name => p_structure_type_name
579 , p_action_map => l_rollup_map
580 , p_validate => p_validate
581 , p_halt_on_error => p_halt_on_error
582 , x_error_message => x_error_message );
583 Bom_Rollup_Pub.g_attr_diffs := null;
584
585 ELSE
586 Bom_Rollup_Pub.g_attr_diffs := null;
587 Bom_Rollup_Pub.Perform_Rollup( p_item_id => p_item_id
588 , p_organization_id => p_organization_id
589 , p_parent_item_id => p_parent_item_id
590 , p_structure_type_name => p_structure_type_name
591 , p_action_map => l_rollup_map
592 , p_validate => p_validate
593 , p_halt_on_error => p_halt_on_error
594 , x_error_message => x_error_message );
595 Bom_Rollup_Pub.g_attr_diffs := null;
596 END IF;
597 END;
598
599 --added by dikrishn for bug3938873
600 PROCEDURE UPDATE_REG_PUB_UPDATE_DATES (p_inventory_item_id IN NUMBER,
601 p_organization_id IN NUMBER,
602 p_update_reg IN VARCHAR2 := 'N',
603 p_commit IN VARCHAR2 := FND_API.G_FALSE,
604 x_return_status OUT NOCOPY VARCHAR2,
605 x_msg_count OUT NOCOPY NUMBER,
606 x_msg_data OUT NOCOPY VARCHAR2
607 )
608 IS
609 BEGIN
610
611 EGO_GTIN_PVT.UPDATE_REG_PUB_UPDATE_DATES (p_inventory_item_id => p_inventory_item_id,
612 p_organization_id => p_organization_id,
613 p_update_reg => p_update_reg,
614 p_commit => p_commit,
615 x_return_status => x_return_status,
616 x_msg_count => x_msg_count,
617 x_msg_data => x_msg_data
618 );
619 END;
620
621 PROCEDURE GET_UOM_CLASS_COMPATIBILITY(p_source_item_id IN NUMBER,
622 p_destn_item_id IN NUMBER,
623 p_src_org_id IN NUMBER,
624 p_dest_org_id IN NUMBER,
625 x_compatibility_status OUT NOCOPY VARCHAR2
626 )
627 IS
628 l_src_uom_code VARCHAR2(3);
629 l_dest_uom_code VARCHAR2(3);
630 BEGIN
631
632 SELECT primary_uom_code
633 INTO l_src_uom_code
634 FROM MTL_SYSTEM_ITEMS_B
635 WHERE inventory_item_id = p_source_item_id
636 and organization_id = p_src_org_id;
637
638 SELECT primary_uom_code
639 INTO l_dest_uom_code
640 FROM MTL_SYSTEM_ITEMS_B
641 WHERE inventory_item_id = p_destn_item_id
642 and organization_id = p_dest_org_id;
643
644 GET_UOM_CLASS_COMPATIBILITY(p_src_uom_code => l_src_uom_code,
645 p_dest_uom_code => l_dest_uom_code,
646 x_compatibility_status => x_compatibility_status);
647
648 END;
649
650
651 PROCEDURE GET_UOM_CLASS_COMPATIBILITY(p_src_uom_code IN VARCHAR2,
652 p_dest_uom_code IN VARCHAR2,
653 x_compatibility_status OUT NOCOPY VARCHAR2)
654 IS
655 l_total NUMBER;
656
657 BEGIN
658
659 SELECT count(DISTINCT uom_class)
660 into l_total
661 FROM mtl_units_of_measure_vl
662 WHERE uom_code = p_src_uom_code OR uom_code = p_dest_uom_code;
663
664 IF l_total > 1
665 THEN
666 x_compatibility_status := 'N';
667 ELSE
668 x_compatibility_status := 'Y';
669 END IF;
670
671 END GET_UOM_CLASS_COMPATIBILITY;
672
673
674 /* Validate the rollup atributes within the hierarchy. They all should have the same values
675 within a hierarchy.We need to validate this, in order to extend the support for heterogeneous
679 PROCEDURE Validate_Hierarchy_Attrs ( p_group_id IN NUMBER,
676 hierarchies as part of UCCNET3.0 compliance
677 */
678
680 x_return_status OUT NOCOPY VARCHAR2,
681 x_error_message OUT NOCOPY VARCHAR2) IS
682
683 CURSOR c_unique_count IS
684 SELECT count(DISTINCT ega.storage_handling_temp_max) storage_temp_max_cnt,
685 count(DISTINCT ega.storage_handling_temp_min) storage_temp_min_cnt,
686 count(DISTINCT ega.uom_storage_handling_temp_max) uom_storage_temp_max_cnt,
687 count(DISTINCT ega.uom_storage_handling_temp_min) uom_storage_temp_min_cnt,
688 count(DISTINCT ega.brand_owner_gln) brand_owner_gln_cnt,
689 count(DISTINCT ega.brand_owner_name) brand_owner_name_cnt,
690 count(DISTINCT ega.sub_brand) sub_brand_cnt,
691 count(DISTINCT egal.functional_name) functional_name_cnt,
692 count(DISTINCT msi.weight_uom_code) weight_uom_code_cnt
693 FROM bom_explosions_all be,
694 ego_item_gtn_attrs_b ega,
695 ego_item_gtn_attrs_tl egal,
696 mtl_system_items_b msi
697 WHERE be.group_id = p_group_id
698 AND be.trade_item_descriptor = 'BASE_UNIT_OR_EACH'
699 AND be.component_item_id = msi.inventory_item_id
700 AND be.common_organization_id = msi.organization_id
701 AND msi.inventory_item_id = ega.inventory_item_id
702 AND msi.organization_id = ega.organization_id
703 AND ega.extension_id = egal.extension_id
704 AND egal.language = userenv('LANG');
705
706 TYPE MFR_TBL_TYPE IS TABLE OF VARCHAR2(1000)
707 INDEX BY BINARY_INTEGER;
708
709 l_mfg_table MFR_TBL_TYPE;
710
711 l_where_clause VARCHAR2(32000);
712 l_total_mfrs NUMBER := 0;
713 l_result NUMBER;
714
715 BEGIN
716
717 x_return_status := 'S';
718
719 /* Validate attributes */
720
721 FOR r1 IN c_unique_count
722 LOOP
723 IF r1.storage_temp_max_cnt > 1 OR r1.uom_storage_temp_max_cnt > 1
724 THEN
725 x_return_status := 'E';
726 x_error_message := Get_Message('BOM','BOM_GTIN_MULTI_STORAGE_MAX');
727 ELSIF r1.storage_temp_min_cnt > 1 OR r1.uom_storage_temp_min_cnt > 1
728 THEN
729 x_return_status := 'E';
730 x_error_message := Get_Message('BOM','BOM_GTIN_MULTI_STORAGE_MIN');
731 ELSIF r1.brand_owner_gln_cnt > 1
732 THEN
733 x_return_status := 'E';
734 x_error_message := Get_Message('BOM','BOM_GTIN_MULTI_BRAND_GLN');
735 ELSIF r1.brand_owner_name_cnt > 1
736 THEN
737 x_return_status := 'E';
738 x_error_message := Get_Message('BOM','BOM_GTIN_MULTI_BRAND_NAME');
739 ELSIF r1.sub_brand_cnt > 1
740 THEN
741 x_return_status := 'E';
742 x_error_message := Get_Message('BOM','BOM_GTIN_MULTI_SUB_BRAND');
743 ELSIF r1.functional_name_cnt > 1
744 THEN
745 x_return_status := 'E';
746 x_error_message := Get_Message('BOM','BOM_GTIN_MULTI_FUNC_NAME');
747 ELSIF r1.weight_uom_code_cnt > 1
748 THEN
749 x_return_status := 'E';
750 x_error_message := Get_Message('BOM','BOM_GTIN_MULTI_WEIGHT_UOM');
751 END IF;
752 END LOOP;
753
754 IF x_return_status <> 'S'
755 THEN
756 Return;
757 END IF;
758
759 /* Validation for the multi row attributes Manufacturer name and
760 Manufacturer GLN
761 */
762
763 SELECT DISTINCT concat(ega.manufacturer,ega.name_of_manufacturer)
764 BULK COLLECT INTO l_mfg_table
765 FROM bom_explosions_all be,
766 ego_gtin_mfg_attrs_v ega
767 WHERE be.group_id = p_group_id
768 AND be.trade_item_descriptor = 'BASE_UNIT_OR_EACH'
769 AND be.component_item_id = ega.inventory_item_id
770 AND be.common_organization_id = ega.organization_id;
771
772 l_total_mfrs := l_mfg_table.COUNT;
773
774 IF l_total_mfrs = 0
775 THEN
776 Return;
777 END IF;
778
779 FOR i in 1..l_total_mfrs
780 LOOP
781 l_where_clause := l_where_clause||','||''''||l_mfg_table(i)||'''';
782 END LOOP;
783
784 l_where_clause := substr(l_where_clause,2);
785
786 /*
787 dbms_output.put_line(substr('SELECT 1 INTO l_result FROM dual WHERE EXISTS
788 (SELECT null FROM bom_explosions_all be, ego_gtin_mfg_attrs_v ega
789 WHERE be.group_id = '||p_group_id||' AND be.trade_item_descriptor = '||''''||'BASE_UNIT_OR_EACH'||''''||
790 ' AND be.component_item_id = ega.inventory_item_id AND be.common_organization_id = ega.organization_id
791 AND concat(ega.manufacturer,ega.name_of_manufacturer) IN ('||l_where_clause||')'||
792 ' GROUP BY be.component_item_id HAVING count(*) <>'||l_total_mfrs||')',1,250));
793 */
794
795 BEGIN
796
797 EXECUTE IMMEDIATE 'SELECT 1 FROM dual WHERE EXISTS
798 (SELECT null FROM bom_explosions_all be, ego_gtin_mfg_attrs_v ega
799 WHERE be.group_id = '||p_group_id||' AND be.trade_item_descriptor = '||''''||'BASE_UNIT_OR_EACH'||''''||
800 ' AND be.component_item_id = ega.inventory_item_id AND be.common_organization_id = ega.organization_id
801 AND concat(ega.manufacturer,ega.name_of_manufacturer) IN ('||l_where_clause||')'||
802 ' GROUP BY be.component_item_id HAVING count(*) <> '||l_total_mfrs||' )' INTO l_result;
803
804 EXCEPTION WHEN NO_DATA_FOUND
805 THEN
806 l_result := 0;
807 END;
808
809 --dbms_output.put_line('l_result is '||l_result);
810
811 IF l_result <> 0
812 THEN
813 x_return_status := 'E';
814 x_error_message := Get_Message('BOM','BOM_GTIN_MFG_DATA_NOT_SYNC');
815 END IF;
816
817 END;
818
819 END BOM_GTIN_Rules;
820
821
822