1 PACKAGE BODY BOM_UTIL as
2 /* $Header: BOMUTILB.pls 120.1 2006/03/17 12:53:15 seradhak noship $ */
3 -- -----------------------------------------------------------------------
4 FUNCTION get_first_level_components
5 (p_cbill_sequence_id IN BOM_BILL_OF_MATERIALS.bill_sequence_id%TYPE)
6 RETURN NUMBER IS
7 l_first_level_count NUMBER := 0;
8 BEGIN
9
10 if p_cbill_sequence_id is null
11 then
12 return 0;
13 end if;
14
15 SELECT count(bill_sequence_id) INTO l_first_level_count
16 FROM bom_inventory_components
17 WHERE bill_sequence_id = p_cbill_sequence_id
18 AND implementation_date is NOT NULL;
19
20 RETURN l_first_level_count;
21
22 END get_first_level_components;
23
24 -- -----------------------------------------------------------------------
25 FUNCTION get_change_order_count
26 (p_bill_sequence_id IN BOM_BILL_OF_MATERIALS.bill_sequence_id%TYPE)
27 RETURN NUMBER IS
28 l_change_order_count NUMBER := 0;
29 BEGIN
30
31 if p_bill_sequence_id is null
32 then
33 return 0;
34 end if;
35
36 SELECT count(distinct change_id) INTO l_change_order_count
37 FROM ENG_REVISED_ITEMS
38 WHERE bill_sequence_id = p_bill_sequence_id;
39
40 RETURN l_change_order_count;
41 END get_change_order_count;
42 -- -----------------------------------------------------------------------
43 FUNCTION get_second_level_components
44 (p_component_item_id IN
45 BOM_INVENTORY_COMPONENTS.component_item_id%TYPE,
46 p_organization_id IN NUMBER,
47 p_alternate_bom_designator IN VARCHAR2)
48 RETURN NUMBER is
49 l_component_count NUMBER := 0;
50 BEGIN
51
52 if (p_component_item_id is null)
53 then
54 return 0;
55 end if;
56
57 SELECT count(*) into l_component_count
58 FROM
59 BOM_INVENTORY_COMPONENTS bomc
60 WHERE
61 implementation_date is NOT NULL
62 AND bomc.bill_sequence_id IN -- For Bug Fix . 2832017
63 (SELECT common_bill_sequence_id
64 FROM
65 BOM_BILL_OF_MATERIALS bom
66 WHERE
67 bom.assembly_item_id = p_component_item_id
68 AND bom.organization_id = p_organization_id
69 -- Commented for Bug Fix 2832017
70 -- AND bom.alternate_bom_designator = p_alternate_bom_designator
71 );
72
73 RETURN l_component_count;
74
75 END get_second_level_components;
76 -- --------------------------------------------------------------------------
77 FUNCTION getFirstLevelComponents(p_component_item_id IN NUMBER,
78 p_bill_sequence_id IN NUMBER,
79 p_top_bill_sequence_id IN NUMBER,
80 p_plan_level IN NUMBER,
81 p_organization_id IN NUMBER)
82 RETURN NUMBER IS
83 l_component_count NUMBER := 0;
84 BEGIN
85 /*
86 AND organization_id = p_organization_id */
87
88 if p_bill_sequence_id is null or p_component_item_id is null
89 then
90 return 0;
91 end if;
92 SELECT count(*) into l_component_count
93 FROM bom_explosions_V
94 WHERE assembly_item_id = p_component_item_id
95 and top_bill_sequence_id = p_top_bill_sequence_id
96 --AND bill_sequence_id = p_bill_sequence_id
97 AND plan_level = p_plan_level + 1;
98
99 RETURN l_component_count;
100 END getFirstLevelComponents;
101 -- --------------------------------------------------------------------------
102 FUNCTION get_effective_date(p_structure_type_id IN NUMBER)
103 RETURN DATE IS
104 l_effective_date DATE;
105 BEGIN
106 SELECT effective_date INTO l_effective_date
107 FROM bom_structure_types_b
108 WHERE structure_type_id = p_structure_type_id;
109
110 RETURN l_effective_date;
111
112 END get_effective_date;
113 -- --------------------------------------------------------------------------
114 FUNCTION get_disable_date(p_structure_type_id IN NUMBER)
115 RETURN DATE Is
116 l_disable_date DATE;
117 BEGIN
118 SELECT disable_date INTO l_disable_date
119 FROM bom_structure_types_b
120 WHERE structure_type_id = p_structure_type_id;
121
122 RETURN l_disable_date;
123
124 END get_disable_date;
125 -- --------------------------------------------------------------------------
126 FUNCTION check_structures_exist(p_structure_type_id IN NUMBER)
127 RETURN VARCHAR2 IS
128 l_structures_count NUMBER := 0;
129
130 BEGIN
131 SELECT count(*) INTO l_structures_count
132 FROM bom_bill_of_materials
133 WHERE structure_type_id = p_structure_type_id;
134
135 IF l_structures_count = 0 THEN
136 RETURN 'N';
137 ELSE
138 RETURN 'Y';
139 END IF;
140
141 END check_structures_exist;
142 -- --------------------------------
143 FUNCTION check_id_exist(p_structure_type_id IN NUMBER)
144 RETURN VARCHAR2 IS
145 l_count NUMBER := 0;
146
147 BEGIN
148 SELECT count(*) INTO l_count
149 FROM bom_structure_types_b
150 WHERE structure_type_id = p_structure_type_id;
151 IF l_count = 0 THEN
152 RETURN 'N';
153 ELSE
154 RETURN 'Y';
155 END IF;
156
157 END check_id_exist;
158 -- --------------------------------------------------------------------
159 /*************************************************************************
160 * Local Procedure: Calculate_both_totals
161 * Parameter IN : old_component_sequenc_id
162 * Parameters OUT : Total Quantity of Designators
163 * Purpose : Procedure calculate_both_totals will take the component
164 * sequence_id and calculate the number of designators that
165 * already exist for it and the how many exist on the same
166 * component on the ECO with an acd_type of add or disable
167 * Then by making use of the set operater it will eliminate
168 * the disable one's from the list. This is the quantity
169 * of designator that will remain on the component after
170 * implementation and is returned by the procedure as
171 * Total Quantity.
172 **************************************************************************/
173 PROCEDURE Calculate_Both_Totals( p_old_component_sequence_id IN NUMBER,
174 x_TotalQuantity IN OUT NOCOPY NUMBER
175 )
176 IS
177
178 X_OldComp NUMBER;
179 X_Add CONSTANT NUMBER := 1;
180 X_Delete CONSTANT NUMBER := 3;
181 l_Implemented_Count NUMBER;
182 l_dummy VARCHAR2(80);
183
184 CURSOR GetTotalQty IS
185 SELECT brd.component_reference_designator
186 FROM bom_reference_designators brd
187 WHERE brd.component_sequence_id = p_old_component_sequence_id
188 AND NVL(brd.acd_type, X_Add) = X_Add
189 UNION
190 SELECT brd.component_reference_designator
191 FROM bom_reference_designators brd,
192 bom_inventory_components bic
193 WHERE DECODE(bic.old_component_sequence_id, NULL,
194 bic.component_sequence_id,
195 bic.old_component_sequence_id) = p_old_component_sequence_id
196 AND bic.component_sequence_id = brd.component_sequence_id
197 AND bic.implementation_date IS NULL
198 AND brd.acd_type = X_Add
199 MINUS
200 SELECT brd.component_reference_designator
201 FROM bom_reference_designators brd,
202 bom_inventory_components bic
203 WHERE DECODE(bic.old_component_sequence_id, NULL,
204 bic.component_sequence_id,
205 bic.old_component_sequence_id) = p_old_component_sequence_id
206 AND bic.component_sequence_id = brd.component_sequence_id
207 AND bic.implementation_date IS NULL
208 AND brd.acd_type = X_Delete;
209
210 BEGIN
211 FOR X_Designators IN GetTotalQty LOOP
212 X_TotalQuantity := GetTotalQty%rowcount;
213 RETURN;
214 END LOOP;
215
216 -- Else return 0
217 X_TotalQuantity := 0;
218
219 END Calculate_Both_Totals;
220 -- -------------------------------------------------------------------------------
221 PROCEDURE validate_RefDesig_Entity
222 ( p_organization_id IN NUMBER
223 , p_component_seq_id IN NUMBER
224 , p_ref_desig_name IN VARCHAR2
225 , p_acd_type IN NUMBER
226 , x_return_status IN OUT NOCOPY VARCHAR2
227 ) IS
228 l_return_status VARCHAR2(1) := 'S';
229 l_dummy VARCHAR(80);
230
231 CURSOR c_acdtype IS
232 SELECT acd_type, old_component_sequence_id
233 FROM bom_inventory_components bic
234 WHERE bic.component_sequence_id = p_component_seq_id;
235
236 CURSOR c_QuantityRelated IS
237 SELECT component_quantity
238 FROM bom_inventory_components
239 WHERE component_sequence_id = p_component_seq_id
240 AND quantity_related = 1;
241
242 BEGIN
243 x_return_status := 'SUCCESS';
244 /**********************************************************************
245 * If the Transaction Type is CREATE and the ACD_Type = Disable, then
246 * the reference designator should already exist for the revised
247 * component.
248 ***********************************************************************/
249 IF p_acd_type = 3 THEN
250
251 BEGIN
252 SELECT component_reference_designator INTO l_dummy
253 FROM bom_reference_designators brd,bom_inventory_components bic
254 WHERE bic.component_sequence_id = p_component_seq_id
255 AND brd.component_sequence_id = bic.old_component_sequence_id
256 AND brd.component_reference_designator = p_ref_desig_name;
257
258 EXCEPTION
259 WHEN NO_DATA_FOUND THEN
260 -- It means that the reference designator does not exist on the
261 -- revised component or it is probably not implemented yet.
262
263 x_return_status := 'BOM_DISABLE_DESG_NOT_FOUND';
264 l_return_status := Fnd_Api.G_RET_STS_ERROR;
265 RETURN;
266 END;
267
268 END IF;
269
270 /************************************************************************
271 * Check if ACD_Type of component is ADD then ref. desg is also add.
272 *************************************************************************/
273
274 FOR acd IN c_acdtype LOOP
275 --
276 -- If the component has an ACD_Type of ADD then ref. Desg must also be ADD
277 --
278 IF acd.acd_type = 1 /* ADD */ AND
279 p_acd_type <> 1
280 THEN
281 l_return_status := Fnd_Api.G_RET_STS_ERROR;
282 x_return_status := 'BOM_RFD_ACD_NOT_COMPATIBLE';
283 RETURN;
284 END IF;
285 END LOOP;
286
287
288 /************************************************************************
289 * If the Transaction Type is CREATE and the ACD_type is ADD then check the
290 * type of item to which a ref. designator is being added. Planning bills
291 * cannot have ref. desgs and also components which are not Standard cannot
292 * have ref. desgs. This OR so even if either exists Ref. Designators cannot
293 * be added.
294 *************************************************************************/
295
296 BEGIN
297 SELECT 'Non-Standard Comp'
298 INTO l_dummy
299 FROM bom_inventory_Components bic
300 WHERE bic.component_sequence_id = p_component_seq_id
301 AND bic.bom_item_type IN (1, 2, 3); /*MODEL,OPTION CLASS,PLANNING*/
302
303 -- If no exception is raised then
304 -- Generate an error saying that the component is non-standard.
305
306 l_return_status := Fnd_Api.G_RET_STS_ERROR;
307 x_return_status := 'BOM_RFD_NON_STD_PARENT';
308 RETURN;
309
310 EXCEPTION
311 WHEN NO_DATA_FOUND THEN
312 -- do nothing
313 NULL;
314 WHEN OTHERS THEN
315 -- do nothing
316 NULL;
317
318 END; /* End Checking for non-standard component */
319
320 BEGIN
321 SELECT 'Planning Bill'
322 INTO l_dummy
323 FROM sys.dual
324 WHERE EXISTS (SELECT 'Planning Item'
325 FROM bom_bill_of_materials bom,
326 mtl_system_items msi,
327 bom_inventory_components bic
328 WHERE msi.bom_item_type = 3 /* PLANNING */
329 AND msi.inventory_item_id = bom.assembly_item_id
330 AND msi.organization_id = bom.organization_id
331 AND bom.bill_sequence_id = bic.bill_sequence_id
332 AND bic.component_sequence_id = p_component_seq_id
333 );
334
335 -- If a record is found, then log an error because of the above
336 -- mentioned comment.
337 l_return_status := Fnd_Api.G_RET_STS_ERROR;
338 x_return_status := 'BOM_RFD_PLANNING_BILL';
339 RETURN;
340
341 EXCEPTION
342 WHEN NO_DATA_FOUND THEN
343 NULL; -- Do nothing
344 WHEN OTHERS THEN
345 NULL; -- Do nothing
346 END; /* End Checking for Planning Parent */
347
348 END validate_RefDesig_Entity;
349 -- -------------------------------------------------------------------------------
350 PROCEDURE check_RefDesig_Access
351 ( p_organization_id IN NUMBER
352 , p_assembly_item_id IN NUMBER
353 , p_alternate_bom_code IN VARCHAR2
354 , p_ref_desig_name IN VARCHAR2
355 , p_component_item_id IN NUMBER
356 , p_component_item_name IN VARCHAR2
357 , p_component_seq_id IN NUMBER
358 , x_return_status IN OUT NOCOPY VARCHAR2
359 )IS
360 l_return_status VARCHAR2(1) := 'S';
361 l_dummy VARCHAR(80);
362 l_bom_ref_designator_rec Bom_Bo_Pub.Bom_Ref_Designator_Rec_Type;
363 l_Mesg_Token_Tbl Error_Handler.Mesg_Token_Tbl_Type;
364 BEGIN
365 x_return_status := 'SUCCESS';
366 Bom_Validate_Bom_Header.Check_Access
367 ( p_organization_id=>p_organization_id
368 , p_assembly_item_id=>p_assembly_item_id
369 , p_alternate_bom_code=>p_alternate_bom_code
370 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
371 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
372 , x_return_status => l_Return_Status
373 );
374 IF l_Mesg_Token_Tbl.COUNT > 0
375 THEN
376 x_return_status := l_Mesg_Token_Tbl(1).message_text;
377 RETURN;
378 END IF;
379
380 IF l_return_status = Error_Handler.G_STATUS_ERROR
381 THEN
382 x_return_status := 'BOM_RFD_RITACC_FAT_FATAL';
383 RETURN;
384 ELSIF l_return_status = Error_Handler.G_STATUS_UNEXPECTED
385 THEN
386 x_return_status := 'BOM_RFD_RITACC_UNEXP_SKIP';
387 RETURN;
388 END IF;
389 -- Check that user has access to Bom component
390 Bom_Validate_Bom_Component.Check_Access
391 ( p_organization_id => p_organization_id
392 , p_component_item_id => p_component_item_id
393 , p_component_name => p_component_item_name
394 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
395 , x_return_status => l_Return_Status
396 );
397
398 IF l_Mesg_Token_Tbl.COUNT > 0
399 THEN
400 x_return_status := l_Mesg_Token_Tbl(1).message_text;
401 RETURN;
402 END IF;
403 IF l_return_status = Error_Handler.G_STATUS_ERROR
404 THEN
405 x_return_status := 'BOM_RFD_CMPACC_FAT_FATAL';
406 RETURN;
407 ELSIF l_return_status = Error_Handler.G_STATUS_UNEXPECTED
408 THEN
409 x_return_status := 'BOM_RFD_CMPACC_UNEXP_SKIP';
410 RETURN;
411 END IF;
412 EXCEPTION
413 WHEN OTHERS THEN
414 NULL;
415
416 -- Check Reference Designator Access
417 BEGIN
418 SELECT 'parent not disabled'
419 INTO l_dummy
420 FROM bom_inventory_components bic
421 WHERE bic.component_sequence_id = p_component_seq_id
422 AND NVL(bic.acd_type, 0) <> 3;
423 EXCEPTION
424 WHEN NO_DATA_FOUND THEN
425 -- This means that the parent is disabled as the record search
426 -- was fired to get a parent which is not disabled
427 x_return_status := 'BOM_RFD_COMP_ACD_TYPE_DISABLE';
428 RETURN;
429 WHEN OTHERS THEN
430 --This means that an unexpected error has occured
431 x_return_status := 'ERROR in Entity validation '
432 || SUBSTR(SQLERRM, 1, 240) || ' ' || TO_CHAR(SQLCODE);
433 RETURN;
434 END;
435 END check_RefDesig_Access;
436 -- -------------------------------------------------------------------------------
437 PROCEDURE get_RefDesig_Quantity
438 ( p_component_seq_id IN NUMBER
439 , p_acd_type IN NUMBER
440 , x_refdesig_qty IN OUT NOCOPY NUMBER
441 , x_qty_related IN OUT NOCOPY NUMBER
442 , x_comp_qty IN OUT NOCOPY NUMBER
443 ) IS
444 l_ref_qty NUMBER := 0;
445 l_quantity NUMBER;
446
447 CURSOR c_acdtype IS
448 SELECT acd_type, old_component_sequence_id
449 FROM bom_inventory_components bic
450 WHERE bic.component_sequence_id = p_component_seq_id;
451
452 CURSOR c_QuantityRelated IS
453 SELECT component_quantity
454 FROM bom_inventory_components
455 WHERE component_sequence_id = p_component_seq_id
456 AND quantity_related = 1;
457
458
459 BEGIN
460 x_qty_related := 0;
461 x_comp_qty := 0;
462
463 OPEN c_QuantityRelated;
464 FETCH c_QuantityRelated INTO l_Quantity;
465 IF c_QuantityRelated%FOUND THEN
466
467 x_qty_related := 1;
468 x_comp_qty := l_Quantity;
469
470 FOR acd IN c_acdtype LOOP
471 IF acd.acd_type = 2 /* CHANGE */
472 THEN
473 Calculate_Both_Totals
474 ( p_old_component_sequence_id => acd.old_component_sequence_id
475 , x_TotalQuantity => l_ref_qty
476 );
477 ELSE
478 Calculate_Both_Totals
479 ( p_old_component_sequence_id => p_component_seq_id
480 , x_TotalQuantity => l_ref_qty);
481 END IF;
482 END LOOP;
483
484 END IF;
485
486 CLOSE c_QuantityRelated;
487
488 x_refdesig_qty := l_ref_qty;
489
490 END get_RefDesig_Quantity;
491 -- ---------------------------------------------------------------------------------
492 FUNCTION get_change_notice(p_change_line_id IN NUMBER)
493 RETURN VARCHAR2 IS
494 CURSOR c_get_change_notice(c_p_change_line_id NUMBER)
495 IS
496 SELECT eec.change_notice
497 FROM
498 eng_engineering_changes eec,
499 eng_revised_items eri--eng_change_lines ecl
500 WHERE eri.change_id/*cl.change_id*/ = eec.change_id
501 -- AND ecl.change_line_id = c_p_change_line_id;
502 AND eri.revised_item_sequence_id = c_p_change_line_id;
503 l_change_notice eng_engineering_changes.change_notice%type;
504 BEGIN
505 OPEN c_get_change_notice(p_change_line_id);
506 FETCH c_get_change_notice INTO l_change_notice;
507 IF c_get_change_notice%FOUND THEN
508 CLOSE c_get_change_notice;
509 RETURN l_change_notice;
510 ELSE
511 CLOSE c_get_change_notice;
512 RETURN '';
513 END IF;
514 END get_change_notice;
515 -- --------------------------------------------------
516
517 FUNCTION get_person_name(p_user_id IN NUMBER)
518 RETURN VARCHAR2 IS
519 CURSOR c_get_person_name(c_p_user_id NUMBER)
520 IS
521 SELECT person_first_name ||' '||person_last_name
522 FROM hz_parties
523 WHERE party_id = (SELECT customer_id FROM FND_USER
524 WHERE user_id = c_p_user_id);
525 l_user_name varchar2(400);
526 BEGIN
527 OPEN c_get_person_name(p_user_id);
528 FETCH c_get_person_name INTO l_user_name;
529 IF c_get_person_name%FOUND THEN
530 CLOSE c_get_person_name;
531 RETURN l_user_name;
532 ELSE
533 CLOSE c_get_person_name;
534 RETURN '';
535 END IF;
536 END get_person_name;
537 -- -----------------------------------------------------
538 FUNCTION get_implemen_date(p_bill_sequence_id IN NUMBER)
539 RETURN DATE is
540 BEGIN
541 RETURN null;
542 END get_implemen_date;
543 END BOM_UTIL;
[Home] [Help]