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