[Home] [Help]
PACKAGE BODY: APPS.BOMPXINQ
Source
1 package body BOMPXINQ as
2 /* $Header: BOMXINQB.pls 120.0.12010000.4 2010/02/24 12:35:15 agoginen ship $ */
3 /*==========================================================================+
4 | Copyright (c) 1993 Oracle Corporation Belmont, California, USA |
5 | All rights reserved. |
6 +===========================================================================+
7 | |
8 | File Name : BOMXINQB.sql |
9 | DESCRIPTION : This file is a packaged procedure for the exploders.
10 | This package contains 3 different exploders for the
11 | modules it can be called from. The procedure exploders
12 | calls the correct exploder based on the module option.
13 | Each of the 3 exploders can be called on directly too.
14 | Parameters: org_id organization_id
15 | order_by 1 - Op seq, item seq
16 | 2 - Item seq, op seq
17 | grp_id unique value to identify current explosion
18 | use value from sequence bom_small_expl_temp_s
19 | session_id unique value to identify current session
20 | use value from bom_small_expl_temp_session_s
21 | levels_to_explode
22 | bom_or_eng 1 - BOM
23 | 2 - ENG
24 | impl_flag 1 - implemented only
25 | 2 - both impl and unimpl
26 | explode_option 1 - All
27 | 2 - Current
28 | 3 - Current and future
29 | module 1 - Costing
30 | 2 - Bom
31 | 3 - Order entry
32 | cst_type_id cost type id for costed explosion
33 | std_comp_flag 1 - explode only standard components
34 | 2 - all components
35 | expl_qty explosion quantity
36 | item_id item id of asembly to explode
37 | list_id unique id for lists in bom_lists for range
38 | report_option 1 - cost rollup with report
39 | 2 - cost rollup no report
40 | 3 - temp cost rollup with report
41 | cst_rlp_id rollup_id
42 | req_id request id
43 | prgm_appl_id program application id
44 | prg_id program id
45 | user_id user id
46 | lock_flag 1 - do not lock the table
47 | 2 - lock the table
48 | alt_rtg_desg alternate routing designator
49 | rollup_option 1 - single level rollup
50 | 2 - full rollup
51 | plan_factor_flag1 - Yes
52 | 2 - No
53 | alt_desg alternate bom designator
54 | rev_date explosion date
55 | comp_code concatenated component code lpad 16
56 | show_rev 1 - obtain current revision of component
57 | 2 - don't obtain current revision
58 | material_ctrl 1 - obtain subinventory locator
59 | 2 - don't obtain subinventory locator
60 | lead_time 1 - calculate offset percent
61 | 2 - don't calculate offset percent
62 | err_msg error message out buffer
63 | error_code error code out. returns sql error code
64 | if sql error, 9999 if loop detected.
65 | Revision
66 Shreyas Shah creation
67 02/10/94 Shreyas Shah added multi-org capability from bom_lists
68 max_bom_levels of all orgs for multi-org
69 | 08/03/95 Rob Yee added parameters for 10SG
70 | 01/12/02 Rahul Chitko Exporting of an indented BOM into multiple
71 | pl/sql tables enabled. These pl/sql tables
72 | match the parameters used by the pl/sql BO and
73 | can be used for a direct import.
74 | 01/23/03 Rahul Chitko Added validation to make sure that the organization
75 | hierarchy is optional and that the user can still
76 | export data for the current organization.
77 | |
78 +==========================================================================*/
79 TYPE Header_Record_Id_Type IS RECORD
80 (bill_sequence_id NUMBER := FND_API.G_MISS_NUM,
81 assembly_item_id NUMBER := FND_API.G_MISS_NUM);
82
83 TYPE Header_Record_Id_Tbl_Type IS TABLE OF Header_Record_Id_Type
84 INDEX BY BINARY_INTEGER;
85 G_Header_Record_id_Tbl Header_Record_Id_Tbl_Type;
86 G_bom_header_tbl BOM_BO_PUB.BOM_HEADER_TBL_TYPE;
87 G_bom_revisions_tbl BOM_BO_PUB.BOM_REVISION_TBL_TYPE;
88 G_bom_components_tbl BOM_BO_PUB.BOM_COMPS_TBL_TYPE;
89 G_bom_ref_designators_tbl BOM_BO_PUB.BOM_REF_DESIGNATOR_TBL_TYPE;
90 G_bom_sub_components_tbl BOM_BO_PUB.BOM_SUB_COMPONENT_TBL_TYPE;
91 G_bom_comp_ops_tbl BOM_BO_PUB.BOM_COMP_OPS_TBL_TYPE;
92 no_profile EXCEPTION;
93 invalid_org EXCEPTION;
94 invalid_assembly_item_name EXCEPTION;
95 invalid_comp_seq_id EXCEPTION;
96 invalid_bill_seq_id EXCEPTION;
97 invalid_locator_id EXCEPTION;
98 missing_parameters EXCEPTION;
99
100 procedure exploders(
101 verify_flag IN NUMBER DEFAULT 0,
102 online_flag IN NUMBER DEFAULT 0,
103 org_id IN NUMBER,
104 order_by IN NUMBER DEFAULT 1,
105 grp_id IN NUMBER,
106 session_id IN NUMBER DEFAULT 0,
107 l_levels_to_explode IN NUMBER DEFAULT 1,
108 bom_or_eng IN NUMBER DEFAULT 1,
109 impl_flag IN NUMBER DEFAULT 1,
110 plan_factor_flag IN NUMBER DEFAULT 2,
111 l_explode_option IN NUMBER DEFAULT 2,
112 module IN NUMBER DEFAULT 2,
113 cst_type_id IN NUMBER DEFAULT 0,
114 std_comp_flag IN NUMBER DEFAULT 0,
115 unit_number_from IN VARCHAR2 DEFAULT '',
116 unit_number_to IN VARCHAR2 DEFAULT '',
117 rev_date IN DATE DEFAULT sysdate,
118 show_rev IN NUMBER DEFAULT 2,
119 material_ctrl IN NUMBER DEFAULT 2,
120 lead_time IN NUMBER DEFAULT 2,
121 err_msg OUT NOCOPY VARCHAR2,
122 error_code OUT NOCOPY NUMBER) AS
123
124 max_level NUMBER;
125 levels_to_explode NUMBER;
126 explode_option NUMBER;
127 cost_org_id NUMBER;
128 max_levels NUMBER;
129 incl_oc_flag NUMBER;
130 counter NUMBER;
131 l_std_comp_flag NUMBER;
132 l_error_code NUMBER;
133 l_err_msg VARCHAR2(2000);
134 loop_detected EXCEPTION;
135
136 BEGIN
137
138 levels_to_explode := l_levels_to_explode;
139 explode_option := l_explode_option;
140
141 /*
142 ** fetch the max permissible levels for explosion
143 ** doing a max since if no row exist to prevent no_Data_found exception
144 ** from being raised
145 */
146
147 SELECT max(MAXIMUM_BOM_LEVEL)
148 INTO max_level
149 FROM BOM_PARAMETERS
150 WHERE (org_id = -1
151 or
152 (org_id <> -1 and ORGANIZATION_ID = org_id)
153 );
154
155 -- since sort width is increased to 4 and column width is only 240,
156 -- maximum level must be at most 59 (levels 0 through 59).
157
158 IF nvl(max_level, 60) > 59 THEN
159 max_level := 59;
160 END IF;
161
162 /*
163 ** if levels to explode > max levels or < 0, set it to max_levels
164 */
165 IF (levels_to_explode < 0) OR (levels_to_explode > max_level) THEN
166 levels_to_explode := max_level;
167 END IF;
168
169 /*
170 ** if levels_to_explode > 1, then explode_option = CURRENT is the
171 ** only valid option
172 ** 05/20/93 removed this condition to make it generic. Also the verify
173 ** needs current+future indented explosion.
174
175 IF levels_to_explode > 1 THEN
176 explode_option := 2;
177 END IF;
178 */
179
180 IF (module = 1 or module = 2) THEN /* cst or bom explosion */
181 l_std_comp_flag := 2; /* ALL */
182 ELSE
183 l_std_comp_flag := std_comp_flag;
184 END IF;
185
186 IF (module = 1) THEN /* CST */
187 incl_oc_flag := 2;
188 ELSE
189 incl_oc_flag := 1;
190 END IF;
191
192 -- dbms_output.put_line('calling bompbxin.bom_Exploder . . .');
193
194 BOMPBXIN.bom_exploder(
195 verify_flag => verify_flag,
196 online_flag => online_flag,
197 org_id => org_id,
198 order_by => order_by,
199 grp_id => grp_id,
200 levels_to_explode => levels_to_explode,
201 bom_or_eng => bom_or_eng,
202 impl_flag => impl_flag,
203 std_comp_flag => l_std_comp_flag,
204 plan_factor_flag => plan_factor_flag,
205 explode_option => explode_option,
206 incl_oc_flag => incl_oc_flag,
207 unit_number_from => unit_number_from,
208 unit_number_to => unit_number_to,
209 max_level => max_level,
210 rev_date => rev_date,
211 show_rev => show_rev,
212 material_ctrl => material_ctrl,
213 lead_time => lead_time,
214 err_msg => l_err_msg,
215 error_code => l_error_code
216 );
217
218 error_code := l_error_code;
219 err_msg := l_err_msg;
220
221 EXCEPTION
222 WHEN OTHERS THEN
223 error_code := l_error_code;
224 err_msg := l_err_msg;
225 END exploders;
226
227 PROCEDURE loopstr2msg(
228 grp_id IN NUMBER,
229 verify_msg OUT NOCOPY VARCHAR2
230 ) IS
231 top_alt VARCHAR2(10);
232 org_id NUMBER;
233 cur_msgstr VARCHAR2(240);
234 cur_item_id NUMBER;
235 cur_substr VARCHAR2(16);
236 position NUMBER;
237 tmp_msg VARCHAR2(2000);
238 err_msg VARCHAR2(80);
239
240 CURSOR get_loop_rows(c_group_id NUMBER) IS
241 SELECT
242 COMPONENT_CODE,
243 LOOP_FLAG,
244 PLAN_LEVEL
245 FROM BOM_SMALL_EXPL_TEMP
246 WHERE GROUP_ID = c_group_id
247 AND LOOP_FLAG = 1;
248 BEGIN
249
250 SELECT NVL( TOP_ALTERNATE_DESIGNATOR, 'none' ), ORGANIZATION_ID
251 INTO top_alt, org_id
252 FROM BOM_SMALL_EXPL_TEMP
253 WHERE GROUP_ID = grp_id
254 AND ROWNUM = 1
255 AND PLAN_LEVEL = 0;
256
257 FOR loop_rec IN get_loop_rows( grp_id ) LOOP
258
259 tmp_msg := '';
260
261 FOR i IN 0..loop_rec.plan_level LOOP
262 position := (i * 16) + 1;
263 cur_substr := SUBSTR( loop_rec.component_code, position, 16 );
264 cur_item_id := TO_NUMBER( cur_substr );
265
266 SELECT
267 substrb(MIF.ITEM_NUMBER || ' ' || BBM.ALTERNATE_BOM_DESIGNATOR,1,16)
268 INTO cur_msgstr
269 FROM MTL_ITEM_FLEXFIELDS MIF, BOM_BILL_OF_MATERIALS BBM
270 WHERE MIF.ORGANIZATION_ID = BBM.ORGANIZATION_ID
271 AND MIF.ITEM_ID = BBM.ASSEMBLY_ITEM_ID
272 AND BBM.ASSEMBLY_ITEM_ID = cur_item_id
273 AND BBM.ORGANIZATION_ID = org_id
274 AND (
275 ((top_alt = 'none') AND BBM.ALTERNATE_BOM_DESIGNATOR IS NULL)
276 OR
277 ((top_alt <> 'none')
278 AND (
279 ( EXISTS ( SELECT NULL FROM BOM_BILL_OF_MATERIALS BBM1
280 WHERE BBM1.ORGANIZATION_ID = org_id
281 AND BBM1.ASSEMBLY_ITEM_ID = cur_item_id
282 AND BBM1.ALTERNATE_BOM_DESIGNATOR = top_alt)
283 AND BBM.ALTERNATE_BOM_DESIGNATOR = top_alt
284 )
285 OR
286 ( NOT EXISTS (SELECT NULL FROM BOM_BILL_OF_MATERIALS BBM2
287 WHERE BBM2.ORGANIZATION_ID = org_id
288 AND BBM2.ASSEMBLY_ITEM_ID = cur_item_id
289 AND BBM2.ALTERNATE_BOM_DESIGNATOR = top_alt)
290 AND BBM.ALTERNATE_BOM_DESIGNATOR IS NULL
291 )
292 )
293 )
294 );
295
296 IF i = 0 THEN
297 tmp_msg := cur_msgstr;
298 ELSE
299 tmp_msg := tmp_msg || ' -> ' || cur_msgstr;
300 END IF;
301
302 END LOOP; /* loop through component_code */
303
304 verify_msg := tmp_msg;
305
306
307 END LOOP; /* for loop_rec cursor loop */
308
309
310 EXCEPTION
311 when others then
312 err_msg := substrb(SQLERRM, 1, 70);
313
314 END loopstr2msg;
315
316 procedure exploder_userexit (
317 verify_flag IN NUMBER DEFAULT 0,
318 org_id IN NUMBER,
319 order_by IN NUMBER DEFAULT 1,
320 grp_id IN NUMBER,
321 session_id IN NUMBER DEFAULT 0,
322 levels_to_explode IN NUMBER DEFAULT 1,
323 bom_or_eng IN NUMBER DEFAULT 1,
324 impl_flag IN NUMBER DEFAULT 1,
325 plan_factor_flag IN NUMBER DEFAULT 2,
326 explode_option IN NUMBER DEFAULT 2,
327 module IN NUMBER DEFAULT 2,
328 cst_type_id IN NUMBER DEFAULT 0,
329 std_comp_flag IN NUMBER DEFAULT 0,
330 expl_qty IN NUMBER DEFAULT 1,
331 item_id IN NUMBER,
332 unit_number_from IN VARCHAR2,
333 unit_number_to IN VARCHAR2,
334 alt_desg IN VARCHAR2 DEFAULT '',
335 comp_code IN VARCHAR2 DEFAULT '',
336 rev_date IN DATE DEFAULT sysdate,
337 show_rev IN NUMBER DEFAULT 2,
338 material_ctrl IN NUMBER DEFAULT 2,
339 lead_time IN NUMBER DEFAULT 2,
340 err_msg OUT NOCOPY VARCHAR2,
341 error_code OUT NOCOPY NUMBER) AS
342 cbsi NUMBER;
343 out_code NUMBER;
344 cost_org_id NUMBER;
345 stmt_num NUMBER := 1;
346 out_message VARCHAR2(240);
347 parameter_error EXCEPTION;
348 exploder_error EXCEPTION;
349 -- inv_uom_conv_exe EXCEPTION;
350 X_SortWidth number; -- Maximum of 9999 components per level
351 cnt NUMBER :=0; -- bug 2951874
352 -- Bug 2088686
353 is_cost_organization VARCHAR2(1);
354
355 /* Bug: 9355186 - PL/SQL Tables for bulk collect */
356 TYPE numTabType IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
357 TYPE charTabType IS TABLE OF varchar2(3) INDEX BY BINARY_INTEGER;
358 t_conversion_rate numTabType;
359 curBSI numTabType;
360 curCSI numTabType;
361 curCII numTabType;
362 curCBSI numTabType;
363 t_master_org_id numTabType;
364 t_child_uom charTabType;
365 t_master_uom charTabtype;
366 bulk_limit NUMBER := 10000;
367 -- t_comp_qty NUMBER;
368 -- t_comp_extd_qty NUMBER;
369 -- t_item_cost NUMBER;
370 Cursor cur is
371 Select BET.bill_sequence_id curBSI,
372 BET.component_sequence_id curCSI,
373 BET.component_item_id curCII,
374 BET.common_bill_sequence_id curCBSI,
375 msi1.organization_id t_master_org_id,
376 msi1.primary_uom_code t_master_uom,
377 msi2.primary_uom_code t_child_uom
378 from BOM_SMALL_EXPL_TEMP BET, bom_bill_of_materials bbm, mtl_system_items msi1, mtl_system_items msi2
379 where BET.bill_sequence_id <> BET.common_bill_sequence_id
380 and bbm.bill_sequence_id = BET.common_bill_sequence_id
381 and msi1.inventory_item_id = BET.component_item_id
382 and msi1.organization_id = bbm.organization_id
383 and msi2.inventory_item_id = BET.component_item_id
384 and msi2.organization_id = BET.organization_id
385 and BET.group_id = grp_id;
386 --Bug 2088686
387
388 cursor conv (t_master_uom varchar2,
389 t_child_uom varchar2,
390 t_inv_id number,
391 t_master_org_id number) is
392 select conversion_rate
393 from mtl_uom_conversions_view
394 where primary_uom_code = t_master_uom and
395 uom_code = t_child_uom and
396 inventory_item_id = t_inv_id and
397 organization_id = t_master_org_id;
398
399 BEGIN
400 -- Added savepoint for bug 3863319
401 SAVEPOINT exploder_userexit_pvt;
402
403 X_SortWidth := BOMPBXIN.G_SortWidth;
404
405 IF (verify_flag = 1) AND (module <> 2) THEN
406 raise parameter_error;
407 END IF;
408
409 if (grp_id is null or item_id is null) then
410 raise parameter_error;
411 end if;
412
413 stmt_num := 2;
414 insert into bom_small_expl_temp
415 (
416 group_id,
417 bill_sequence_id,
418 component_sequence_id,
419 organization_id,
420 top_item_id,
421 component_item_id,
422 plan_level,
423 extended_quantity,
424 basis_type,
425 component_quantity,
426 sort_order,
427 program_update_date,
428 top_bill_sequence_id,
429 component_code,
430 loop_flag,
431 top_alternate_designator,
432 bom_item_type,
433 parent_bom_item_type
434 )
435 select
436 grp_id,
437 bom.bill_sequence_id,
438 NULL,
439 org_id,
440 item_id,
441 item_id,
442 0,
443 expl_qty,
444 1,
445 1,
446 lpad('1', X_SortWidth, '0'),
447 sysdate,
448 bom.bill_sequence_id,
449 nvl(comp_code, lpad(item_id, 16, '0')),
450 2,
451 alt_desg,
452 msi.bom_item_type,
453 msi.bom_item_type
454 from bom_bill_of_materials bom, mtl_system_items msi
455 where bom.assembly_item_id = item_id
456 and bom.organization_id = org_id
457 and nvl(alternate_bom_designator, 'NONE') =
458 nvl(alt_desg, 'NONE')
459 and msi.organization_id = org_id
460 and inventory_item_id = item_id;
461
462 if (SQL%NOTFOUND) then
463 raise no_data_found;
464 end if;
465
466 -- dbms_output.put_line('level 0 inserted . . . ');
467 Exploders(
468 verify_flag => verify_flag,
469 online_flag => 1,
470 org_id => org_id,
471 order_by => order_by,
472 grp_id => grp_id,
473 session_id => session_id,
474 l_levels_to_explode => levels_to_explode,
475 bom_or_eng => bom_or_eng,
476 impl_flag => impl_flag,
477 plan_factor_flag => plan_factor_flag,
478 l_explode_option => explode_option,
479 module => module,
480 unit_number_from => unit_number_from,
481 unit_number_to => unit_number_to,
482 cst_type_id => cst_type_id,
483 std_comp_flag => std_comp_flag,
484 rev_date => rev_date,
485 show_rev => show_rev,
486 material_ctrl => material_ctrl,
487 lead_time => lead_time,
488 err_msg => out_message,
489 error_code => out_code
490 );
491
492 if (verify_flag <> 1 and (out_code = 9999 or out_code = 9998
493 or out_code < 0)) then
494 raise exploder_error;
495 elsif (verify_flag = 1 and (out_code = 9998 or out_code < 0)) then
496 raise exploder_error;
497 end if;
498
499 if (module = 1) then
500 BOMPCEXP.cst_exploder(
501 grp_id => grp_id,
502 org_id => org_id,
503 cst_type_id => cst_type_id,
504 inq_flag => 1,
505 err_msg => out_message,
506 error_code => out_code);
507 end if;
508
509 if (verify_flag = 1) then
510 Loopstr2msg( grp_id, out_message );
511 end if;
512 -- Bug 2157325 Begin
513 -- If the master organization is referenced as the costing organization then
514 -- is_cost_organzation flag is set to 'N' else if the child organization itself
515 -- referenced as the costing organization then the is_cost_organization flag is
516 -- set to 'Y'.
517 --bug 2951874 corrected the following sql.
518
519 select count(*) into cnt
520 from mtl_parameters
521 where organization_id = cost_organization_id
522 and organization_id = org_id;
523
524 if (cnt >0) then
525 is_cost_organization := 'Y';
526 else
527 is_cost_organization := 'N';
528 end if;
529 -- Bug 2157325 End
530
531 /* Bug 9355186 : Changed to bulk collect to improve performance */
532 OPEN cur;
533 LOOP
534 FETCH cur
535 bulk collect
536 into curBSI, curCSI, curCII, curCBSI, t_master_org_id, t_master_uom, t_child_uom
537 LIMIT bulk_limit;
538 EXIT WHEN curBSI.count = 0;
539
540 FOR i IN 1..curCSI.COUNT LOOP
541
542 /* Bug 9355186 - This information in queried in cursor cur
543 select msi.primary_uom_code, msi.organization_id into
544 t_master_uom, t_master_org_id
545 from mtl_system_items msi, bom_bill_of_materials bbm
546 where cr.curCBSI = bbm.bill_sequence_id and
547 bbm.organization_id = msi.organization_id and
548 msi.inventory_item_id = cr.curCII;
549
550 select msi.primary_uom_code into t_child_uom
551 from mtl_system_items msi
552 where msi.inventory_item_id = cr.curCII and
553 msi.organization_id = cr.curOI;
554 */
555
556 /* Bug 2663515
557 select conversion_rate into t_conversion_rate
558 from mtl_uom_conversions_view
559 where primary_uom_code = t_master_uom and
560 uom_code = t_child_uom and
561 inventory_item_id = cr.curCII and
562 organization_id = t_master_org_id;
563 */
564
565 -- Bug 2088686 Begin
566 -- If the Intended Bill is referenced some other bill of different organization
567 -- then the conversion rate, uom of the component in the child organization
568 -- should be calculated.
569 OPEN conv(t_master_uom(i), t_child_uom(i), curCII(i), t_master_org_id(i));
570 Fetch conv into t_conversion_rate(i);
571 if conv%NOTFOUND then
572 close conv; -- added for Bug #2994556
573 /* Bug 9355186 : Error is thrown right away instead of raising exception */
574 FND_MESSAGE.SET_NAME('BOM','BOM_UOMCV_INVUOMTYPE_ERR');
575 fnd_message.Set_Token('FROMUOM',t_master_uom(i));
576 fnd_message.Set_Token('TOUOM',t_child_uom(i));
577 fnd_message.raise_error;
578 End if;
579 close conv; -- added for Bug #2994556
580 END LOOP; -- curCSI.count loop
581
582 /* Bug 9355186 : Individual updates converted into bulk update
583 if is_cost_organization <> 'Y' then
584 UPDATE BOM_SMALL_EXPL_TEMP
585 SET item_cost = item_cost*t_conversion_rate
586 WHERE group_id = cr.curGI and
587 component_sequence_id = cr.curCSI and
588 bill_sequence_id = cr.curBSI and
589 common_bill_sequence_id = cr.curCBSI;
590 end if;
591 --Bug 2157325 End
592
593 UPDATE BOM_SMALL_EXPL_TEMP
594 SET component_quantity = trunc(component_quantity/t_conversion_rate, 22), --Bug 9173185 fix
595 extended_quantity = extended_quantity/t_conversion_rate,
596 -- item_cost = item_cost*t_conversion_rate,
597 primary_uom_code = cr.curPUC
598 WHERE group_id = cr.curGI and
599 component_sequence_id = cr.curCSI and
600 bill_sequence_id = cr.curBSI and
601 common_bill_sequence_id = cr.curCBSI;
602 */
603
604 FORALL i IN 1..curCSI.COUNT
605 /* Bug 9355186: Proving hint to improve performance */
606 UPDATE /*+ index(BOM_SMALL_EXPL_TEMP BOM_SMALL_EXPL_TEMP_n1) */ BOM_SMALL_EXPL_TEMP
607 SET
608 -- Bug 2157325 Begin
609 -- If cost_organization is Master organization then the item cost should be
610 -- calculated by multiplying the conversion_rate.
611 item_cost = decode(is_cost_organization,'Y',item_cost,item_cost*t_conversion_rate(i)),
612 component_quantity = trunc(component_quantity/t_conversion_rate(i), 22), --Bug 8977128 fix
613 extended_quantity = extended_quantity/t_conversion_rate(i)
614 WHERE group_id = grp_id and
615 component_sequence_id = curCSI(i) and
616 bill_sequence_id = curBSI(i) and
617 common_bill_sequence_id = curCBSI(i);
618
619 END LOOP; -- bulk collect loop
620 close cur;
621
622 -- Bug 2088686 End
623 error_code := out_code;
624 err_msg := out_message;
625
626 EXCEPTION
627 when exploder_error then
628 error_code := out_code;
629 err_msg := out_message;
630 WHEN parameter_error THEN
631 error_code := -1;
632 Fnd_Msg_Pub.Build_Exc_Msg(
633 p_pkg_name => 'BOMPXINQ',
634 p_procedure_name => 'exploder_userexit',
635 p_error_text => 'verify parameters');
636 err_msg := Fnd_Message.Get_Encoded;
637 /* Commented for bug 9355186 WHEN inv_uom_conv_exe THEN
638 FND_MESSAGE.SET_NAME('BOM','BOM_UOMCV_INVUOMTYPE_ERR');
639 fnd_message.Set_Token('FROMUOM',t_master_uom);
640 fnd_message.Set_Token('TOUOM',t_child_uom);
641 fnd_message.raise_error;*/
642
643 WHEN OTHERS THEN
644 error_code := SQLCODE;
645 Fnd_Msg_Pub.Build_Exc_Msg(
646 p_pkg_name => 'BOMPXINQ',
647 p_procedure_name => 'exploder_userexit',
648 p_error_text => SQLERRM);
649 err_msg := Fnd_Message.Get_Encoded;
650 ROLLBACK TO exploder_userexit_pvt; -- Added for bug: 3863319
651 END exploder_userexit;
652
653 --========================================================================
654 -- PROCEDURE : Export_BOM
655 -- PARAMETERS : Profile_id IN NUMBER Security Profile Id
656 -- Org_hierarchy_name IN VARCHAR2 Organization Hierarchy
657 -- Name
658 -- Assembly_item_id IN NUMBER Assembly item id
659 -- Organization_id IN NUMBER Organization id
660 -- Alternate_bm_designator IN VARCHAR2 Alternate bom designator
661 -- Costs IN NUMBER Cost flag
662 -- Cost_type_id IN NUMBER Cost type id
663 -- bom_export_tab OUT bomexporttabtype export table
664 -- Err_Msg OUT VARCHAR2 Error Message
665 -- Error_Code OUT NUMBER Error Megssage
666 --
667 -- COMMENT : API Accepts the security profile id,name of an hierarchy,
668 -- Assembly item id, Organization id, Alternate bom designator,
669 -- Costs, Cost type id and returns bom_export_tab PL/SQL table
670 -- consists of exploded BOM for all the organizations under
671 -- the hierarchy name. Error Code and corresponding Error
672 -- mesages are returned in case of an error
673 --
674 --========================================================================
675 PROCEDURE EXPORT_BOM ( Profile_id IN NUMBER,
676 Org_hierarchy_name IN VARCHAR2,
677 Assembly_item_id IN NUMBER,
678 Organization_id IN NUMBER,
679 Alternate_bm_designator IN VARCHAR2 DEFAULT '',
680 Costs IN NUMBER DEFAULT 2,
681 Cost_type_id IN NUMBER DEFAULT 0,
682 bom_export_tab OUT NOCOPY bomexporttabtype,
683 Err_Msg OUT NOCOPY VARCHAR2,
684 Error_Code OUT NOCOPY NUMBER )
685 IS
686 t_org_code_list INV_OrgHierarchy_PVT.OrgID_tbl_type;
687 l_Org_hierarchy_name VARCHAR2(30);
688 l_assembly_item_id NUMBER;
689 l_organization_id NUMBER;
690 l_cst_type_id NUMBER;
691
692 max_level NUMBER;
693 l_group_id NUMBER;
694 l_org_name VARCHAR2(60);
695 c_Cost_type_id NUMBER;
696 c_assembly_item_id NUMBER;
697 i_count NUMBER :=1;
698 l_assembly_found BOOLEAN :=TRUE;
699 l_org_count NUMBER;
700
701 no_organization EXCEPTION;
702 explode_error EXCEPTION;
703 no_hierarchy EXCEPTION;
704 cost_type EXCEPTION;
705 no_level_access EXCEPTION;
706 no_assy EXCEPTION;
707 no_list EXCEPTION;
708
709 -- cursor to obtain exploded bom from bom_small_expl_temp table
710 CURSOR export_tab (l_organization_id NUMBER, l_group_id NUMBER) IS
711 SELECT
712 TOP_BILL_SEQUENCE_ID ,
713 BILL_SEQUENCE_ID ,
714 COMMON_BILL_SEQUENCE_ID ,
715 ORGANIZATION_ID ,
716 COMPONENT_SEQUENCE_ID ,
717 COMPONENT_ITEM_ID ,
718 BASIS_TYPE ,
719 COMPONENT_QUANTITY ,
720 PLAN_LEVEL ,
721 EXTENDED_QUANTITY ,
722 SORT_ORDER ,
723 GROUP_ID ,
724 TOP_ALTERNATE_DESIGNATOR ,
725 COMPONENT_YIELD_FACTOR ,
726 TOP_ITEM_ID ,
727 COMPONENT_CODE ,
728 INCLUDE_IN_ROLLUP_FLAG ,
729 LOOP_FLAG ,
730 PLANNING_FACTOR ,
731 OPERATION_SEQ_NUM ,
732 BOM_ITEM_TYPE ,
733 PARENT_BOM_ITEM_TYPE ,
734 ASSEMBLY_ITEM_ID ,
735 WIP_SUPPLY_TYPE ,
736 ITEM_NUM ,
737 EFFECTIVITY_DATE ,
738 DISABLE_DATE ,
739 IMPLEMENTATION_DATE ,
740 OPTIONAL ,
741 SUPPLY_SUBINVENTORY ,
742 SUPPLY_LOCATOR_ID ,
743 COMPONENT_REMARKS ,
744 CHANGE_NOTICE ,
745 OPERATION_LEAD_TIME_PERCENT,
746 MUTUALLY_EXCLUSIVE_OPTIONS ,
747 CHECK_ATP ,
748 REQUIRED_TO_SHIP ,
749 REQUIRED_FOR_REVENUE ,
750 INCLUDE_ON_SHIP_DOCS ,
751 LOW_QUANTITY ,
752 HIGH_QUANTITY ,
753 SO_BASIS ,
754 OPERATION_OFFSET ,
755 CURRENT_REVISION ,
756 LOCATOR ,
757 CONTEXT ,
758 ATTRIBUTE1 ,
759 ATTRIBUTE2 ,
760 ATTRIBUTE3 ,
761 ATTRIBUTE4 ,
762 ATTRIBUTE5 ,
763 ATTRIBUTE6 ,
764 ATTRIBUTE7 ,
765 ATTRIBUTE8 ,
766 ATTRIBUTE9 ,
767 ATTRIBUTE10 ,
768 ATTRIBUTE11 ,
769 ATTRIBUTE12 ,
770 ATTRIBUTE13 ,
771 ATTRIBUTE14 ,
772 ATTRIBUTE15 ,
773 ITEM_COST ,
774 EXTEND_COST_FLAG
775 FROM bom_small_expl_temp
776 WHERE
777 Organization_id = l_organization_id
778 AND GROUP_ID = l_group_id;
779
780 BEGIN
781 l_assembly_item_id := Assembly_item_id;
782 l_organization_id := Organization_id;
783 c_Cost_type_id := Cost_type_id;
784
785 --Set the Security Profile value as passed by the user
786 FND_PROFILE.put('PER_SECURITY_PROFILE_ID',profile_id);
787
788 --dbms_output.put_line('within export_Bom . . . ');
789
790 -- Validate the Organization Hierarchy name and check,if the access allowed
791 if (Org_hierarchy_name is not null)
792 then
793 SELECT count (*) into l_org_count from
794 per_organization_structures
795 WHERE INV_ORGHIERARCHY_PVT.ORG_HIERARCHY_ACCESS(Org_hierarchy_name)='Y'
796 AND name = Org_hierarchy_name;
797
798 if (l_org_count <1 ) then
799 RAISE no_hierarchy;
800 end if;
801 --dbms_output.put_line('org count in heirarchy is more than 0 . . . ');
802
803 end if;
804
805 /* BEGIN
806 -- Get the corresponding Organization name
807 SELECT organization_name into l_org_name
808 FROM org_organization_definitions
809 WHERE organization_id = l_organization_id;
810
811 EXCEPTION
812 WHEN NO_DATA_FOUND THEN
813 RAISE no_organization;
814 END;
815 */
816
817 if (Org_hierarchy_name is null OR Org_hierarchy_name = '')
818 THEN
819 --dbms_output.put_line('Org Hierarachy is null ' );
820 t_org_code_list(1) := l_organization_id;
821 ELSE
822 if (INV_ORGHIERARCHY_PVT.ORG_HIERARCHY_LEVEL_ACCESS(Org_hierarchy_name,
823 l_organization_id)= 'Y') THEN
824 INV_ORGHIERARCHY_PVT.ORG_HIERARCHY_LIST (Org_hierarchy_name,
825 l_organization_id ,t_org_code_list);
826 else
827 RAISE no_level_access;
828 end if;
829 END IF;
830
831 -- For each Organization, if the assembly exists then call the bom exploder
832 FOR I in t_org_code_list.FIRST..t_org_code_list.LAST LOOP
833 BEGIN
834 --dbms_output.put_line('organization: ' || t_org_code_list(I));
835
836 SELECT assembly_item_id INTO c_assembly_item_id
837 FROM bom_bill_of_materials
838 WHERE assembly_item_id = l_assembly_item_id
839 AND organization_id = t_org_code_list(I)
840 AND nvl(ALTERNATE_BOM_DESIGNATOR,'NONE')=
841 nvl(Alternate_bm_designator,'NONE') ;
842 EXCEPTION
843 WHEN NO_DATA_FOUND THEN
844 l_assembly_found :=FALSE;
845 END;
846
847 if l_assembly_found then
848 BEGIN
849 if ( costs = 1) then
850 SELECT COST_TYPE_ID into l_cst_type_id
851 FROM cst_item_cost_type_v
852 WHERE inventory_item_id = Assembly_item_id
853 AND cost_type_id = c_Cost_type_id
854 AND organization_id = t_org_code_list(I);
855 end if;
856 EXCEPTION
857 WHEN NO_DATA_FOUND THEN
858 l_organization_id := t_org_code_list(I);
859 RAISE cost_type;
860 END;
861 --Get the maximum level for explosion is allowed for the Organization
862 SELECT MAXIMUM_BOM_LEVEL INTO max_level
863 FROM BOM_PARAMETERS
864 WHERE ORGANIZATION_ID = t_org_code_list(I);
865
866 SELECT bom_explosion_temp_s.nextval
867 INTO l_group_id from dual;
868
869 DELETE from bom_small_expl_temp where group_id =l_group_id;
870
871 --dbms_output.put_line('calling explosion . . . ');
872
873 exploder_userexit (
874 verify_flag => 0 ,
875 org_id => t_org_code_list(I) ,
876 order_by => 2 ,
877 grp_id => l_group_id ,
878 session_id => 0 ,
879 levels_to_explode => max_level,
880 bom_or_eng => 1,
881 impl_flag => 1,
882 plan_factor_flag => 2,
883 explode_option => 3,
884 module => costs ,
885 unit_number_from => NULL,
886 unit_number_to => NULL,
887 cst_type_id => cost_type_id ,
888 std_comp_flag => 2 ,
889 expl_qty => 1,
890 item_id => Assembly_item_id ,
891 alt_desg => alternate_bm_designator ,
892 comp_code => null ,
893 rev_date => sysdate ,
894 show_rev => 1 ,
895 material_ctrl => 1,
896 lead_time => 1,
897 err_msg => err_msg ,
898 error_code => Error_Code );
899 if error_code = 9998 then
900 l_organization_id := t_org_code_list(I);
901 RAISE explode_error;
902 end if ;
903 if (error_code = 0 or error_code is NULL) then
904 -- Copy the data into the PL/SQL table
905 FOR loop_tab IN export_tab ( t_org_code_list(I),l_group_id)
906 LOOP
907 bom_export_tab(i_count).TOP_BILL_SEQUENCE_ID :=
908 loop_tab.TOP_BILL_SEQUENCE_ID;
909 bom_export_tab(i_count).BILL_SEQUENCE_ID :=
910 loop_tab.BILL_SEQUENCE_ID;
911 bom_export_tab(i_count).COMMON_BILL_SEQUENCE_ID :=
912 loop_tab.COMMON_BILL_SEQUENCE_ID;
913 bom_export_tab(i_count).ORGANIZATION_ID :=
914 loop_tab.ORGANIZATION_ID ;
915 bom_export_tab(i_count).COMPONENT_SEQUENCE_ID :=
916 loop_tab.COMPONENT_SEQUENCE_ID ;
917 bom_export_tab(i_count).COMPONENT_ITEM_ID :=
918 loop_tab.COMPONENT_ITEM_ID ;
919 bom_export_tab(i_count).BASIS_TYPE:= loop_tab.BASIS_TYPE;
920 bom_export_tab(i_count).COMPONENT_QUANTITY :=
921 loop_tab.COMPONENT_QUANTITY ;
922 bom_export_tab(i_count).PLAN_LEVEL := loop_tab.PLAN_LEVEL;
923 bom_export_tab(i_count).EXTENDED_QUANTITY :=
924 loop_tab.EXTENDED_QUANTITY ;
925 bom_export_tab(i_count).SORT_ORDER :=
926 loop_tab.SORT_ORDER ;
927 bom_export_tab(i_count).GROUP_ID :=
928 loop_tab.GROUP_ID ;
929 bom_export_tab(i_count).TOP_ALTERNATE_DESIGNATOR :=
930 loop_tab.TOP_ALTERNATE_DESIGNATOR;
931 bom_export_tab(i_count).COMPONENT_YIELD_FACTOR :=
932 loop_tab.COMPONENT_YIELD_FACTOR ;
933 bom_export_tab(i_count).TOP_ITEM_ID :=
934 loop_tab.TOP_ITEM_ID ;
935 bom_export_tab(i_count).COMPONENT_CODE :=
936 loop_tab.COMPONENT_CODE ;
937 bom_export_tab(i_count).INCLUDE_IN_ROLLUP_FLAG :=
938 loop_tab.INCLUDE_IN_ROLLUP_FLAG ;
939 bom_export_tab(i_count).LOOP_FLAG := loop_tab.LOOP_FLAG ;
940 bom_export_tab(i_count).PLANNING_FACTOR :=
941 loop_tab. PLANNING_FACTOR ;
942 bom_export_tab(i_count).OPERATION_SEQ_NUM :=
943 loop_tab.OPERATION_SEQ_NUM ;
944 bom_export_tab(i_count).BOM_ITEM_TYPE := loop_tab.BOM_ITEM_TYPE;
945 bom_export_tab(i_count).PARENT_BOM_ITEM_TYPE :=
946 loop_tab.PARENT_BOM_ITEM_TYPE ;
947 bom_export_tab(i_count).ASSEMBLY_ITEM_ID :=
948 loop_tab.ASSEMBLY_ITEM_ID;
949 bom_export_tab(i_count).WIP_SUPPLY_TYPE :=
950 loop_tab.WIP_SUPPLY_TYPE ;
951 bom_export_tab(i_count).ITEM_NUM := loop_tab.ITEM_NUM ;
952 bom_export_tab(i_count).EFFECTIVITY_DATE :=
953 loop_tab.EFFECTIVITY_DATE;
954 bom_export_tab(i_count).DISABLE_DATE :=
955 loop_tab.DISABLE_DATE ;
956 bom_export_tab(i_count).IMPLEMENTATION_DATE :=
957 loop_tab.IMPLEMENTATION_DATE ;
958 bom_export_tab(i_count).OPTIONAL := loop_tab.OPTIONAL ;
959 bom_export_tab(i_count).SUPPLY_SUBINVENTORY :=
960 loop_tab.SUPPLY_SUBINVENTORY ;
961 bom_export_tab(i_count).SUPPLY_LOCATOR_ID :=
962 loop_tab.SUPPLY_LOCATOR_ID ;
963 bom_export_tab(i_count).COMPONENT_REMARKS :=
964 loop_tab.COMPONENT_REMARKS ;
965 bom_export_tab(i_count).CHANGE_NOTICE :=
966 loop_tab.CHANGE_NOTICE ;
967 bom_export_tab(i_count).OPERATION_LEAD_TIME_PERCENT :=
968 loop_tab.OPERATION_LEAD_TIME_PERCENT;
969 bom_export_tab(i_count).MUTUALLY_EXCLUSIVE_OPTIONS :=
970 loop_tab.MUTUALLY_EXCLUSIVE_OPTIONS;
971 bom_export_tab(i_count).CHECK_ATP := loop_tab.CHECK_ATP ;
972 bom_export_tab(i_count).REQUIRED_TO_SHIP :=
973 loop_tab.REQUIRED_TO_SHIP ;
974 bom_export_tab(i_count).REQUIRED_FOR_REVENUE :=
975 loop_tab.REQUIRED_FOR_REVENUE ;
976 bom_export_tab(i_count).INCLUDE_ON_SHIP_DOCS :=
977 loop_tab.INCLUDE_ON_SHIP_DOCS ;
978 bom_export_tab(i_count).LOW_QUANTITY := loop_tab.LOW_QUANTITY;
979 bom_export_tab(i_count).HIGH_QUANTITY := loop_tab.HIGH_QUANTITY;
980 bom_export_tab(i_count).SO_BASIS := loop_tab.SO_BASIS ;
981 bom_export_tab(i_count).OPERATION_OFFSET :=
982 loop_tab.OPERATION_OFFSET ;
983 bom_export_tab(i_count).CURRENT_REVISION :=
984 loop_tab.CURRENT_REVISION ;
985 bom_export_tab(i_count).LOCATOR := loop_tab.LOCATOR ;
986 bom_export_tab(i_count).CONTEXT := loop_tab.CONTEXT ;
987 bom_export_tab(i_count).ATTRIBUTE1 := loop_tab.ATTRIBUTE1 ;
988 bom_export_tab(i_count).ATTRIBUTE2 := loop_tab.ATTRIBUTE2 ;
989 bom_export_tab(i_count).ATTRIBUTE3 := loop_tab.ATTRIBUTE3 ;
990 bom_export_tab(i_count).ATTRIBUTE4 := loop_tab.ATTRIBUTE4 ;
991 bom_export_tab(i_count).ATTRIBUTE5 := loop_tab.ATTRIBUTE5 ;
992 bom_export_tab(i_count).ATTRIBUTE6 := loop_tab.ATTRIBUTE6 ;
993 bom_export_tab(i_count).ATTRIBUTE7 := loop_tab.ATTRIBUTE7 ;
994 bom_export_tab(i_count).ATTRIBUTE8 := loop_tab.ATTRIBUTE8 ;
995 bom_export_tab(i_count).ATTRIBUTE9 := loop_tab.ATTRIBUTE9 ;
996 bom_export_tab(i_count).ATTRIBUTE10 := loop_tab.ATTRIBUTE10;
997 bom_export_tab(i_count).ATTRIBUTE11 := loop_tab.ATTRIBUTE11;
998 bom_export_tab(i_count).ATTRIBUTE12 := loop_tab.ATTRIBUTE12;
999 bom_export_tab(i_count).ATTRIBUTE13 := loop_tab.ATTRIBUTE13;
1000 bom_export_tab(i_count).ATTRIBUTE14 := loop_tab.ATTRIBUTE14;
1001 bom_export_tab(i_count).ATTRIBUTE15 := loop_tab.ATTRIBUTE15;
1002 bom_export_tab(i_count).ITEM_COST := loop_tab.ITEM_COST ;
1003 bom_export_tab(i_count).EXTEND_COST_FLAG :=
1004 loop_tab.EXTEND_COST_FLAG ;
1005 i_count := i_count +1;
1006 end loop ;
1007 DELETE from bom_small_expl_temp where group_id =l_group_id;
1008 end if;
1009 end if;
1010 l_assembly_found := TRUE;
1011 END LOOP;
1012 error_code := 0;
1013 Err_Msg := NULL ;
1014
1015 EXCEPTION
1016 WHEN no_level_access THEN
1017 rollback ;
1018 bom_export_tab.delete;
1019 error_code := -122;
1020 Fnd_Message.Set_Name('BOM','BOM_NO_ORG_LEVEL_ACCESS');
1021 Fnd_Message.Set_Token('l_org_name',l_org_name);
1022 Fnd_Message.Set_Token('Org_hierarchy_name',Org_hierarchy_name);
1023 Err_Msg := Fnd_Message.Get;
1024 WHEN no_organization THEN
1025 rollback ;
1026 bom_export_tab.delete;
1027 error_code := -121;
1028 Fnd_Message.Set_Name('BOM','BOM_INVALID_ORGANIZATION');
1029 Fnd_Message.Set_Token('l_organization_id',l_organization_id);
1030 Err_Msg := Fnd_Message.Get;
1031 WHEN explode_error THEN
1032 rollback;
1033 bom_export_tab.delete;
1034 error_code := -120;
1035 Fnd_Message.Set_Name('BOM','BOM_ORG_LEVELS_EXCEEDED');
1036 Fnd_Message.Set_Token('Assembly',Assembly_item_id);
1037 Fnd_Message.Set_Token('Orgid',l_organization_id);
1038 Err_Msg:= Fnd_Message.Get;
1039 WHEN no_hierarchy THEN
1040 rollback ;
1041 bom_export_tab.delete;
1042 error_code := -119;
1043 Fnd_Message.Set_Name('BOM','BOM_INVALID_HIER_OR_ACCESS');
1044 Fnd_Message.Set_Token('Org_hierarchy_name',Org_hierarchy_name);
1045 Err_Msg := Fnd_Message.Get;
1046 --dbms_output.put_line('Error: no_hierarchy ' || Err_Msg);
1047 WHEN cost_type THEN
1048 rollback ;
1049 bom_export_tab.delete;
1050 error_code := -118;
1051 Fnd_Message.Set_Name('BOM','BOM_COST_TYPE_INVALID');
1052 Fnd_Message.Set_Token('Cost_type',c_Cost_type_id);
1053 Fnd_Message.Set_Token('Orgid',l_organization_id);
1054 Err_Msg := Fnd_Message.Get;
1055 WHEN OTHERS THEN
1056 rollback ;
1057 bom_export_tab.delete ;
1058 error_code := SQLCODE;
1059 Err_Msg := SQLERRM;
1060 END;
1061
1062 FUNCTION Get_Item_Name(P_item_id IN NUMBER,
1063 P_organization_id IN NUMBER)
1064 RETURN VARCHAR2 IS
1065 l_item_name MTL_SYSTEM_ITEMS_KFV.concatenated_segments%TYPE;
1066 BEGIN
1067 SELECT concatenated_segments
1068 INTO l_item_name
1069 FROM mtl_system_items_kfv
1070 WHERE inventory_item_id = P_item_id
1071 AND organization_id = P_organization_id;
1072 RETURN l_item_name;
1073 EXCEPTION
1074 WHEN NO_DATA_FOUND THEN
1075 RAISE invalid_assembly_item_name;
1076 END Get_Item_Name;
1077
1078
1079 FUNCTION Get_Org_Code(P_organization_id IN NUMBER)
1080 RETURN VARCHAR2 IS
1081 l_org_code VARCHAR2(3);
1082 BEGIN
1083 SELECT organization_code
1084 INTO l_org_code
1085 FROM mtl_parameters
1086 WHERE organization_id = P_organization_id;
1087 RETURN l_org_code;
1088 EXCEPTION
1089 WHEN NO_DATA_FOUND THEN
1090 RAISE Invalid_Org;
1091 END;
1092
1093 FUNCTION Header_Id_Exists(P_assembly_item_id IN NUMBER,
1094 P_bill_sequence_id IN NUMBER)
1095 RETURN BOOLEAN IS
1096 BEGIN
1097 IF (G_Header_Record_Id_Tbl.COUNT <= 0) THEN
1098 RETURN FALSE;
1099 END IF;
1100 FOR i IN G_Header_Record_Id_Tbl.FIRST..G_Header_Record_Id_Tbl.LAST LOOP
1101 IF (G_Header_Record_Id_Tbl(i).assembly_item_id = P_assembly_item_id AND
1102 G_Header_Record_Id_Tbl(i).bill_sequence_id = P_bill_sequence_id) THEN
1103 RETURN TRUE;
1104 END IF;
1105 END LOOP;
1106 RETURN FALSE;
1107 END Header_Id_Exists;
1108
1109 FUNCTION Get_Locator_Name(P_locator_id IN NUMBER,
1110 P_organization_id IN NUMBER)
1111 RETURN VARCHAR2 IS
1112 CURSOR locator_name_CUR IS
1113 SELECT concatenated_segments
1114 FROM mtl_item_locations_kfv
1115 WHERE inventory_location_id = P_locator_id
1116 AND organization_id = P_organization_id;
1117 l_locator_name MTL_ITEM_LOCATIONS_KFV.Concatenated_Segments%TYPE;
1118 BEGIN
1119 OPEN locator_name_CUR;
1120 FETCH locator_name_CUR INTO l_locator_name;
1121 IF (locator_name_CUR%NOTFOUND) THEN
1122 RAISE invalid_locator_id;
1123 END IF;
1124 RETURN l_locator_name;
1125 END Get_Locator_Name;
1126
1127 PROCEDURE Populate_Header(P_assembly_item_id IN NUMBER,
1128 P_bill_sequence_id IN NUMBER,
1129 P_organization_id IN NUMBER,
1130 P_alternate_bm_designator IN VARCHAR2) IS
1131 CURSOR Bill_Details_CUR IS
1132 SELECT specific_assembly_comment,
1133 assembly_type,
1134 common_assembly_item_id,
1135 common_organization_id,
1136 original_system_reference,
1137 alternate_bom_designator,
1138 attribute_category,
1139 attribute1,
1140 attribute2,
1141 attribute3,
1142 attribute4,
1143 attribute5,
1144 attribute6,
1145 attribute7,
1146 attribute8,
1147 attribute9,
1148 attribute10,
1149 attribute11,
1150 attribute12,
1151 attribute13,
1152 attribute14,
1153 attribute15
1154 FROM bom_bill_of_materials
1155 WHERE bill_sequence_id = p_bill_sequence_id;
1156 --AND NVL(alternate_bom_designator, '##$$##') = NVL(P_alternate_bm_designator, '##$$##');
1157
1158 CURSOR Revision_Details_CUR IS
1159 SELECT revision,
1160 description,
1161 effectivity_date,
1162 attribute_category,
1163 attribute1,
1164 attribute2,
1165 attribute3,
1166 attribute4,
1167 attribute5,
1168 attribute6,
1169 attribute7,
1170 attribute8,
1171 attribute9,
1172 attribute10,
1173 attribute11,
1174 attribute12,
1175 attribute13,
1176 attribute14,
1177 attribute15
1178 FROM mtl_item_revisions
1179 WHERE inventory_item_id = P_assembly_item_id
1180 AND organization_id = P_organization_id;
1181
1182 l_bill_details Bill_Details_CUR%ROWTYPE;
1183 l_common_assembly_item_name MTL_SYSTEM_ITEMS_KFV.concatenated_segments%TYPE;
1184 l_common_org_code VARCHAR2(3);
1185 l_item_name MTL_SYSTEM_ITEMS_KFV.concatenated_segments%TYPE;
1186 l_org_code VARCHAR2(3);
1187 l_cnt NUMBER;
1188 l_counter NUMBER;
1189 l_count NUMBER;
1190 BEGIN
1191
1192 l_count := G_Header_Record_Id_Tbl.LAST + 1;
1193 IF (l_count IS NULL) THEN
1194 l_count := 1;
1195 END IF;
1196 G_Header_Record_Id_Tbl(l_count).bill_sequence_id := P_bill_sequence_id;
1197 G_Header_Record_Id_Tbl(l_count).assembly_item_id := P_assembly_item_id;
1198
1199
1200 l_item_name := Get_Item_Name(P_assembly_item_id, P_organization_id);
1201 l_org_code := Get_Org_Code(P_organization_id);
1202
1203 OPEN Bill_Details_CUR;
1204 FETCH Bill_Details_CUR INTO l_bill_details;
1205 IF (Bill_Details_CUR%NOTFOUND) THEN
1206 RAISE invalid_bill_seq_id;
1207 END IF;
1208 CLOSE Bill_Details_CUR;
1209
1210 IF l_bill_details.common_assembly_item_id IS NOT NULL THEN
1211 l_common_assembly_item_name := Get_Item_Name(l_bill_details.common_assembly_item_id,
1212 l_bill_details.common_organization_id);
1213 l_common_org_code := Get_Org_Code(l_bill_details.common_organization_id);
1214 END IF;
1215
1216 l_cnt := G_Bom_Header_Tbl.LAST + 1;
1217 IF (l_cnt IS NULL) THEN
1218 l_cnt := 1;
1219 END IF;
1220
1221 G_Bom_Header_Tbl(l_cnt).assembly_item_name := l_item_name;
1222 G_Bom_Header_Tbl(l_cnt).organization_code := l_org_code;
1223 G_Bom_Header_Tbl(l_cnt).alternate_bom_code := l_bill_details.alternate_bom_designator;
1224 G_Bom_Header_Tbl(l_cnt).common_assembly_item_name := l_common_assembly_item_name;
1225 G_Bom_Header_Tbl(l_cnt).common_organization_code := l_common_org_code;
1226 G_Bom_Header_Tbl(l_cnt).assembly_comment := l_bill_details.specific_assembly_comment;
1227 G_Bom_Header_Tbl(l_cnt).assembly_type := l_bill_details.assembly_type;
1228 G_Bom_Header_Tbl(l_cnt).attribute_category := l_bill_details.attribute_category;
1229 G_Bom_Header_Tbl(l_cnt).attribute1 := l_bill_details.attribute1;
1230 G_Bom_Header_Tbl(l_cnt).attribute2 := l_bill_details.attribute2;
1231 G_Bom_Header_Tbl(l_cnt).attribute3 := l_bill_details.attribute3;
1232 G_Bom_Header_Tbl(l_cnt).attribute4 := l_bill_details.attribute4;
1233 G_Bom_Header_Tbl(l_cnt).attribute5 := l_bill_details.attribute5;
1234 G_Bom_Header_Tbl(l_cnt).attribute6 := l_bill_details.attribute6;
1235 G_Bom_Header_Tbl(l_cnt).attribute7 := l_bill_details.attribute7;
1236 G_Bom_Header_Tbl(l_cnt).attribute8 := l_bill_details.attribute8;
1237 G_Bom_Header_Tbl(l_cnt).attribute9 := l_bill_details.attribute9;
1238 G_Bom_Header_Tbl(l_cnt).attribute10 := l_bill_details.attribute10;
1239 G_Bom_Header_Tbl(l_cnt).attribute11 := l_bill_details.attribute11;
1240 G_Bom_Header_Tbl(l_cnt).attribute12 := l_bill_details.attribute12;
1241 G_Bom_Header_Tbl(l_cnt).attribute13 := l_bill_details.attribute13;
1242 G_Bom_Header_Tbl(l_cnt).attribute14 := l_bill_details.attribute14;
1243 G_Bom_Header_Tbl(l_cnt).attribute15 := l_bill_details.attribute15;
1244 G_Bom_Header_Tbl(l_cnt).original_system_reference := l_bill_details.original_system_reference;
1245
1246 l_counter := G_Bom_Revisions_Tbl.LAST + 1;
1247 IF (l_counter IS NULL) THEN
1248 l_counter := 1;
1249 END IF;
1250 FOR l_revision_details IN Revision_Details_CUR LOOP
1251 G_Bom_Revisions_Tbl(l_counter).assembly_item_name := l_item_name;
1252 G_Bom_Revisions_Tbl(l_counter).organization_code := l_org_code;
1253 G_Bom_Revisions_Tbl(l_counter).revision := l_revision_details.revision;
1254 G_Bom_Revisions_Tbl(l_counter).alternate_bom_code := l_bill_details.alternate_bom_designator;
1255 G_Bom_Revisions_Tbl(l_counter).description := l_revision_details.description;
1256 G_Bom_Revisions_Tbl(l_counter).start_effective_date := l_revision_details.effectivity_date;
1257 G_Bom_Revisions_Tbl(l_counter).attribute1 := l_revision_details.attribute1;
1258 G_Bom_Revisions_Tbl(l_counter).attribute2 := l_revision_details.attribute2;
1259 G_Bom_Revisions_Tbl(l_counter).attribute3 := l_revision_details.attribute3;
1260 G_Bom_Revisions_Tbl(l_counter).attribute4 := l_revision_details.attribute4;
1261 G_Bom_Revisions_Tbl(l_counter).attribute5 := l_revision_details.attribute5;
1262 G_Bom_Revisions_Tbl(l_counter).attribute6 := l_revision_details.attribute6;
1263 G_Bom_Revisions_Tbl(l_counter).attribute7 := l_revision_details.attribute7;
1264 G_Bom_Revisions_Tbl(l_counter).attribute8 := l_revision_details.attribute8;
1265 G_Bom_Revisions_Tbl(l_counter).attribute9 := l_revision_details.attribute9;
1266 G_Bom_Revisions_Tbl(l_counter).attribute10 := l_revision_details.attribute10;
1267 G_Bom_Revisions_Tbl(l_counter).attribute11 := l_revision_details.attribute11;
1268 G_Bom_Revisions_Tbl(l_counter).attribute12 := l_revision_details.attribute12;
1269 G_Bom_Revisions_Tbl(l_counter).attribute13 := l_revision_details.attribute13;
1270 G_Bom_Revisions_Tbl(l_counter).attribute14 := l_revision_details.attribute14;
1271 G_Bom_Revisions_Tbl(l_counter).attribute15 := l_revision_details.attribute15;
1272 l_counter := l_counter + 1;
1273 END LOOP;
1274
1275 EXCEPTION
1276 WHEN invalid_bill_seq_id THEN
1277 FND_MESSAGE.Set_Name('BOM', 'BOM_INVALID_BILL_SEQ_ID');
1278 FND_MESSAGE.Set_Token('BILL_SEQUENCE_ID', P_bill_sequence_id);
1279 FND_MESSAGE.Set_Token('ASSEMBLY_ITEM_NAME', l_item_name);
1280 FND_MESSAGE.Set_Token('ORGANIZATION_CODE', l_org_code);
1281 RAISE invalid_bill_seq_id;
1282 END Populate_Header;
1283
1284 PROCEDURE Populate_Details(P_component_item_id IN NUMBER,
1285 P_bill_sequence_id IN NUMBER,
1286 P_component_sequence_id IN NUMBER,
1287 P_organization_id IN NUMBER) IS
1288 CURSOR Component_Details_CUR IS
1289 SELECT effectivity_date,
1290 disable_date,
1291 operation_seq_num,
1292 acd_type,
1293 item_num,
1294 basis_type,
1295 component_quantity,
1296 planning_factor,
1297 component_yield_factor,
1298 include_in_cost_rollup,
1299 wip_supply_type,
1300 so_basis,
1301 optional,
1302 mutually_exclusive_options,
1303 check_atp,
1304 shipping_allowed,
1305 required_to_ship,
1306 required_for_revenue,
1307 include_on_ship_docs,
1308 quantity_related,
1309 supply_subinventory,
1310 low_quantity,
1311 high_quantity,
1312 component_remarks,
1313 from_end_item_unit_number,
1314 to_end_item_unit_number,
1315 enforce_int_requirements,
1316 supply_locator_id,
1317 attribute_category,
1318 attribute1,
1319 attribute2,
1320 attribute3,
1321 attribute4,
1322 attribute5,
1323 attribute6,
1324 attribute7,
1325 attribute8,
1326 attribute9,
1327 attribute10,
1328 attribute11,
1329 attribute12,
1330 attribute13,
1331 attribute14,
1332 attribute15
1333 FROM bom_inventory_components
1334 WHERE bill_sequence_id = P_bill_sequence_id
1335 AND component_sequence_id = P_component_sequence_id
1336 AND component_item_id = P_component_item_id;
1337
1338 CURSOR Sub_Component_Details_CUR IS
1339 SELECT implementation_date,
1340 substitute_component_id,
1341 substitute_item_quantity,
1342 enforce_int_requirements,
1343 attribute_category,
1344 attribute1,
1345 attribute2,
1346 attribute3,
1347 attribute4,
1348 attribute5,
1349 attribute6,
1350 attribute7,
1351 attribute8,
1352 attribute9,
1353 attribute10,
1354 attribute11,
1355 attribute12,
1356 attribute13,
1357 attribute14,
1358 attribute15
1359 FROM bom_substitute_components_v
1360 WHERE component_sequence_id = P_component_sequence_id;
1361
1362 CURSOR Ref_Desig_Details_CUR IS
1363 SELECT component_reference_designator,
1364 implementation_date,
1365 ref_designator_comment,
1366 attribute_category,
1367 attribute1,
1368 attribute2,
1369 attribute3,
1370 attribute4,
1371 attribute5,
1372 attribute6,
1373 attribute7,
1374 attribute8,
1375 attribute9,
1376 attribute10,
1377 attribute11,
1378 attribute12,
1379 attribute13,
1380 attribute14,
1381 attribute15
1382 FROM bom_reference_designators_v
1383 WHERE component_sequence_id = P_component_sequence_id;
1384
1385 CURSOR Comp_Oper_Details_CUR IS
1386 SELECT operation_seq_num,
1387 attribute_category,
1388 attribute1,
1389 attribute2,
1390 attribute3,
1391 attribute4,
1392 attribute5,
1393 attribute6,
1394 attribute7,
1395 attribute8,
1396 attribute9,
1397 attribute10,
1398 attribute11,
1399 attribute12,
1400 attribute13,
1401 attribute14,
1402 attribute15
1403 FROM bom_component_operations
1404 WHERE component_sequence_id = P_component_sequence_id;
1405 l_comp_details Component_Details_CUR%ROWTYPE;
1406 l_cnt NUMBER;
1407 j NUMBER;
1408 k NUMBER;
1409 l NUMBER;
1410 l_component_item_name MTL_SYSTEM_ITEMS_KFV.CONCATENATED_SEGMENTS%TYPE;
1411 l_locator_name MTL_ITEM_LOCATIONS_KFV.CONCATENATED_SEGMENTS%TYPE;
1412 BEGIN
1413 l_component_item_name := Get_Item_Name(P_component_item_id, P_organization_id);
1414
1415 OPEN Component_Details_CUR;
1416 FETCH Component_Details_CUR INTO l_comp_details;
1417 IF (Component_Details_CUR%NOTFOUND) THEN
1418 RAISE invalid_comp_seq_id;
1419 END IF;
1420 CLOSE Component_Details_CUR;
1421
1422 IF (l_comp_details.supply_locator_id IS NOT NULL) THEN
1423 l_locator_name := Get_Locator_Name(l_comp_details.supply_locator_id,
1424 P_organization_id);
1425 END IF;
1426
1427 l_cnt := G_Bom_Components_Tbl.LAST + 1;
1428 IF (l_cnt IS NULL) THEN
1429 l_cnt := 1;
1430 END IF;
1431
1432 j := G_Bom_Sub_Components_Tbl.LAST + 1;
1433 IF (j IS NULL) THEN
1434 j := 1;
1435 END IF;
1436
1437 k := G_Bom_Ref_Designators_Tbl.LAST + 1;
1438 IF (k IS NULL) THEN
1439 k := 1;
1440 END IF;
1441
1442 l := G_Bom_Comp_Ops_Tbl.LAST + 1;
1443 IF (l IS NULL) THEN
1444 l := 1;
1445 END IF;
1446
1447 FOR i IN G_Header_Record_Id_Tbl.FIRST..G_Header_Record_Id_Tbl.LAST LOOP
1448 IF (G_Header_Record_Id_Tbl(i).bill_sequence_id = P_bill_sequence_id) THEN
1449 G_Bom_Components_Tbl(l_cnt).organization_code := G_Bom_Header_Tbl(i).organization_code;
1450 G_Bom_Components_Tbl(l_cnt).assembly_item_name := G_Bom_Header_Tbl(i).assembly_item_name;
1451 G_Bom_Components_Tbl(l_cnt).start_effective_date := l_comp_details.effectivity_date;
1452 G_Bom_Components_Tbl(l_cnt).disable_date := l_comp_details.disable_date;
1453 G_Bom_Components_Tbl(l_cnt).operation_sequence_number := l_comp_details.operation_seq_num;
1454 G_Bom_Components_Tbl(l_cnt).component_item_name := l_component_item_name;
1455 G_Bom_Components_Tbl(l_cnt).alternate_bom_code := G_Bom_Header_Tbl(i).alternate_bom_code;
1456 G_Bom_Components_Tbl(l_cnt).item_sequence_number := l_comp_details.item_num;
1457 G_Bom_Components_Tbl(l_cnt).basis_type := l_comp_details.basis_type;
1458 G_Bom_Components_Tbl(l_cnt).quantity_per_assembly := l_comp_details.component_quantity;
1459 G_Bom_Components_Tbl(l_cnt).planning_percent := l_comp_details.planning_factor;
1460 G_Bom_Components_Tbl(l_cnt).projected_yield := l_comp_details.component_yield_factor;
1461 G_Bom_Components_Tbl(l_cnt).include_in_cost_rollup := l_comp_details.include_in_cost_rollup;
1462 G_Bom_Components_Tbl(l_cnt).wip_supply_type := l_comp_details.wip_supply_type;
1463 G_Bom_Components_Tbl(l_cnt).so_basis := l_comp_details.so_basis;
1464 G_Bom_Components_Tbl(l_cnt).optional := l_comp_details.optional;
1465 G_Bom_Components_Tbl(l_cnt).mutually_exclusive := l_comp_details.mutually_exclusive_options;
1466 G_Bom_Components_Tbl(l_cnt).check_atp := l_comp_details.check_atp;
1467 G_Bom_Components_Tbl(l_cnt).shipping_allowed := l_comp_details.shipping_allowed;
1468 G_Bom_Components_Tbl(l_cnt).required_to_ship := l_comp_details.required_to_ship;
1469 G_Bom_Components_Tbl(l_cnt).required_for_revenue := l_comp_details.required_for_revenue;
1470 G_Bom_Components_Tbl(l_cnt).include_on_ship_docs := l_comp_details.include_on_ship_docs;
1471 G_Bom_Components_Tbl(l_cnt).quantity_related := l_comp_details.quantity_related;
1472 G_Bom_Components_Tbl(l_cnt).supply_subinventory := l_comp_details.supply_subinventory;
1473 G_Bom_Components_Tbl(l_cnt).location_name := l_locator_name;
1474 G_Bom_Components_Tbl(l_cnt).minimum_allowed_quantity := l_comp_details.low_quantity;
1475 G_Bom_Components_Tbl(l_cnt).maximum_allowed_quantity := l_comp_details.high_quantity;
1476 G_Bom_Components_Tbl(l_cnt).comments := l_comp_details.component_remarks;
1477 G_Bom_Components_Tbl(l_cnt).from_end_item_unit_number := l_comp_details.from_end_item_unit_number;
1478 G_Bom_Components_Tbl(l_cnt).to_end_item_unit_number := l_comp_details.to_end_item_unit_number;
1479 G_Bom_Components_Tbl(l_cnt).enforce_int_requirements := l_comp_details.enforce_int_requirements;
1480 G_Bom_Components_Tbl(l_cnt).attribute_category := l_comp_details.attribute_category;
1481 G_Bom_Components_Tbl(l_cnt).attribute1 := l_comp_details.attribute1;
1482 G_Bom_Components_Tbl(l_cnt).attribute2 := l_comp_details.attribute2;
1483 G_Bom_Components_Tbl(l_cnt).attribute3 := l_comp_details.attribute3;
1484 G_Bom_Components_Tbl(l_cnt).attribute4 := l_comp_details.attribute4;
1485 G_Bom_Components_Tbl(l_cnt).attribute5 := l_comp_details.attribute5;
1486 G_Bom_Components_Tbl(l_cnt).attribute6 := l_comp_details.attribute6;
1487 G_Bom_Components_Tbl(l_cnt).attribute7 := l_comp_details.attribute7;
1488 G_Bom_Components_Tbl(l_cnt).attribute8 := l_comp_details.attribute8;
1489 G_Bom_Components_Tbl(l_cnt).attribute9 := l_comp_details.attribute9;
1490 G_Bom_Components_Tbl(l_cnt).attribute10 := l_comp_details.attribute10;
1491 G_Bom_Components_Tbl(l_cnt).attribute11 := l_comp_details.attribute11;
1492 G_Bom_Components_Tbl(l_cnt).attribute12 := l_comp_details.attribute12;
1493 G_Bom_Components_Tbl(l_cnt).attribute13 := l_comp_details.attribute13;
1494 G_Bom_Components_Tbl(l_cnt).attribute14 := l_comp_details.attribute14;
1495 G_Bom_Components_Tbl(l_cnt).attribute15 := l_comp_details.attribute15;
1496
1497 FOR Sub_Comp_Rec IN Sub_Component_Details_CUR LOOP
1498 G_Bom_Sub_Components_Tbl(j).organization_code := G_Bom_Header_Tbl(i).organization_code;
1499 G_Bom_Sub_Components_Tbl(j).assembly_item_name := G_Bom_Header_Tbl(i).assembly_item_name;
1500 G_Bom_Sub_Components_Tbl(j).start_effective_date := Sub_Comp_Rec.implementation_date;
1501 G_Bom_Sub_Components_Tbl(j).operation_sequence_number := l_comp_details.operation_seq_num;
1502 G_Bom_Sub_Components_Tbl(j).component_item_name := l_component_item_name;
1503 G_Bom_Sub_Components_Tbl(j).alternate_bom_code := G_Bom_Header_Tbl(i).alternate_bom_code;
1504 G_Bom_Sub_Components_Tbl(j).substitute_component_name := Get_Item_Name(Sub_Comp_Rec.substitute_component_id,
1505 P_organization_id);
1506 G_Bom_Sub_Components_Tbl(j).substitute_item_quantity := Sub_Comp_Rec.substitute_item_quantity;
1507 G_Bom_Sub_Components_Tbl(j).from_end_item_unit_number := l_comp_details.from_end_item_unit_number;
1508 G_Bom_Sub_Components_Tbl(j).enforce_int_requirements := Sub_Comp_Rec.enforce_int_requirements;
1509 G_Bom_Sub_Components_Tbl(j).attribute_category := Sub_Comp_Rec.attribute_category;
1510 G_Bom_Sub_Components_Tbl(j).attribute1 := Sub_Comp_Rec.attribute1;
1511 G_Bom_Sub_Components_Tbl(j).attribute2 := Sub_Comp_Rec.attribute2;
1512 G_Bom_Sub_Components_Tbl(j).attribute3 := Sub_Comp_Rec.attribute3;
1513 G_Bom_Sub_Components_Tbl(j).attribute4 := Sub_Comp_Rec.attribute4;
1514 G_Bom_Sub_Components_Tbl(j).attribute5 := Sub_Comp_Rec.attribute5;
1515 G_Bom_Sub_Components_Tbl(j).attribute6 := Sub_Comp_Rec.attribute6;
1516 G_Bom_Sub_Components_Tbl(j).attribute7 := Sub_Comp_Rec.attribute7;
1517 G_Bom_Sub_Components_Tbl(j).attribute8 := Sub_Comp_Rec.attribute8;
1518 G_Bom_Sub_Components_Tbl(j).attribute9 := Sub_Comp_Rec.attribute9;
1519 G_Bom_Sub_Components_Tbl(j).attribute10 := Sub_Comp_Rec.attribute10;
1520 G_Bom_Sub_Components_Tbl(j).attribute11 := Sub_Comp_Rec.attribute11;
1521 G_Bom_Sub_Components_Tbl(j).attribute12 := Sub_Comp_Rec.attribute12;
1522 G_Bom_Sub_Components_Tbl(j).attribute13 := Sub_Comp_Rec.attribute13;
1523 G_Bom_Sub_Components_Tbl(j).attribute14 := Sub_Comp_Rec.attribute14;
1524 G_Bom_Sub_Components_Tbl(j).attribute15 := Sub_Comp_Rec.attribute15;
1525 j := j + 1;
1526 END LOOP;
1527
1528 FOR Ref_Desg_Rec IN Ref_Desig_Details_CUR LOOP
1529 G_Bom_Ref_Designators_Tbl(k).organization_code := G_Bom_Header_Tbl(i).organization_code;
1530 G_Bom_Ref_Designators_Tbl(k).assembly_item_name := G_Bom_Header_Tbl(i).assembly_item_name;
1531 G_Bom_Ref_Designators_Tbl(k).start_effective_date := Ref_Desg_Rec.implementation_date;
1532 G_Bom_Ref_Designators_Tbl(k).operation_sequence_number := l_comp_details.operation_seq_num;
1533 G_Bom_Ref_Designators_Tbl(k).component_item_name := l_component_item_name;
1534 G_Bom_Ref_Designators_Tbl(k).alternate_bom_code := G_Bom_Header_Tbl(i).alternate_bom_code;
1535 G_Bom_Ref_Designators_Tbl(k).reference_designator_name := Ref_Desg_Rec.component_reference_designator;
1536 G_Bom_Ref_Designators_Tbl(k).ref_designator_comment := Ref_Desg_Rec.ref_designator_comment;
1537 G_Bom_Ref_Designators_Tbl(k).from_end_item_unit_number := l_comp_details.from_end_item_unit_number;
1538 G_Bom_Ref_Designators_Tbl(k).attribute_category := Ref_Desg_Rec.attribute_category;
1539 G_Bom_Ref_Designators_Tbl(k).attribute1 := Ref_Desg_Rec.attribute1;
1540 G_Bom_Ref_Designators_Tbl(k).attribute2 := Ref_Desg_Rec.attribute2;
1541 G_Bom_Ref_Designators_Tbl(k).attribute3 := Ref_Desg_Rec.attribute3;
1542 G_Bom_Ref_Designators_Tbl(k).attribute4 := Ref_Desg_Rec.attribute4;
1543 G_Bom_Ref_Designators_Tbl(k).attribute5 := Ref_Desg_Rec.attribute5;
1544 G_Bom_Ref_Designators_Tbl(k).attribute6 := Ref_Desg_Rec.attribute6;
1545 G_Bom_Ref_Designators_Tbl(k).attribute7 := Ref_Desg_Rec.attribute7;
1546 G_Bom_Ref_Designators_Tbl(k).attribute8 := Ref_Desg_Rec.attribute8;
1547 G_Bom_Ref_Designators_Tbl(k).attribute9 := Ref_Desg_Rec.attribute9;
1548 G_Bom_Ref_Designators_Tbl(k).attribute10 := Ref_Desg_Rec.attribute10;
1549 G_Bom_Ref_Designators_Tbl(k).attribute11 := Ref_Desg_Rec.attribute11;
1550 G_Bom_Ref_Designators_Tbl(k).attribute12 := Ref_Desg_Rec.attribute12;
1551 G_Bom_Ref_Designators_Tbl(k).attribute13 := Ref_Desg_Rec.attribute13;
1552 G_Bom_Ref_Designators_Tbl(k).attribute14 := Ref_Desg_Rec.attribute14;
1553 G_Bom_Ref_Designators_Tbl(k).attribute15 := Ref_Desg_Rec.attribute15;
1554 k := k + 1;
1555 END LOOP;
1556
1557 FOR Comp_Oper_Rec IN Comp_Oper_Details_CUR LOOP
1558 G_Bom_Comp_Ops_Tbl(l).organization_code := G_Bom_Header_Tbl(i).organization_code;
1559 G_Bom_Comp_Ops_Tbl(l).assembly_item_name := G_Bom_Header_Tbl(i).assembly_item_name;
1560 G_Bom_Comp_Ops_Tbl(l).start_effective_date := l_comp_details.effectivity_date;
1561 G_Bom_Comp_Ops_Tbl(l).from_end_item_unit_number := l_comp_details.from_end_item_unit_number;
1562 G_Bom_Comp_Ops_Tbl(l).to_end_item_unit_number := l_comp_details.to_end_item_unit_number;
1563 G_Bom_Comp_Ops_Tbl(l).operation_sequence_number := Comp_Oper_Rec.operation_seq_num;
1564 G_Bom_Comp_Ops_Tbl(l).component_item_name := l_component_item_name;
1565 G_Bom_Comp_Ops_Tbl(l).alternate_bom_code := G_Bom_Header_Tbl(i).alternate_bom_code;
1566 G_Bom_Comp_Ops_Tbl(l).attribute_category := Comp_Oper_Rec.attribute_category;
1567 G_Bom_Comp_Ops_Tbl(l).attribute1 := Comp_Oper_Rec.attribute1;
1568 G_Bom_Comp_Ops_Tbl(l).attribute2 := Comp_Oper_Rec.attribute2;
1569 G_Bom_Comp_Ops_Tbl(l).attribute3 := Comp_Oper_Rec.attribute3;
1570 G_Bom_Comp_Ops_Tbl(l).attribute4 := Comp_Oper_Rec.attribute4;
1571 G_Bom_Comp_Ops_Tbl(l).attribute5 := Comp_Oper_Rec.attribute5;
1572 G_Bom_Comp_Ops_Tbl(l).attribute6 := Comp_Oper_Rec.attribute6;
1573 G_Bom_Comp_Ops_Tbl(l).attribute7 := Comp_Oper_Rec.attribute7;
1574 G_Bom_Comp_Ops_Tbl(l).attribute8 := Comp_Oper_Rec.attribute8;
1575 G_Bom_Comp_Ops_Tbl(l).attribute9 := Comp_Oper_Rec.attribute9;
1576 G_Bom_Comp_Ops_Tbl(l).attribute10 := Comp_Oper_Rec.attribute10;
1577 G_Bom_Comp_Ops_Tbl(l).attribute11 := Comp_Oper_Rec.attribute11;
1578 G_Bom_Comp_Ops_Tbl(l).attribute12 := Comp_Oper_Rec.attribute12;
1579 G_Bom_Comp_Ops_Tbl(l).attribute13 := Comp_Oper_Rec.attribute13;
1580 G_Bom_Comp_Ops_Tbl(l).attribute14 := Comp_Oper_Rec.attribute14;
1581 G_Bom_Comp_Ops_Tbl(l).attribute15 := Comp_Oper_Rec.attribute15;
1582 l := l + 1;
1583 END LOOP;
1584 exit;
1585 END IF;
1586 END LOOP;
1587
1588 EXCEPTION
1589 WHEN invalid_comp_seq_id THEN
1590 FND_MESSAGE.Set_Name('BOM', 'BOM_INVALID_COMP_SEQ_ID');
1591 FND_MESSAGE.Set_Token('COMPONENT_ITEM_NAME', l_component_item_name);
1592 FND_MESSAGE.Set_Token('COMPONENT_SEQ_ID', P_component_sequence_id);
1593 RAISE invalid_comp_seq_id;
1594 WHEN invalid_locator_id THEN
1595 FND_MESSAGE.Set_Name('BOM', 'BOM_INVALID_LOCATOR_ID');
1596 FND_MESSAGE.Set_Token('LOCATOR_ID', l_comp_details.supply_locator_id);
1597 FND_MESSAGE.Set_Token('ORGANIZATION_ID', P_organization_id);
1598 FND_MESSAGE.Set_Token('COMPONENT_ITEM_NAME', l_component_item_name);
1599 RAISE invalid_locator_id;
1600 END Populate_Details;
1601
1602 --========================================================================
1603 -- PROCEDURE : Export_BOM
1604 -- PARAMETERS : Org_hierarchy_name IN VARCHAR2 Organization Hierarchy
1605 -- Name
1606 -- Assembly_item_name IN VARCHAR2 Assembly item name
1607 -- Organization_code IN VARCHAR2 Organization code
1608 -- Alternate_bm_designator IN VARCHAR2 Alternate bom designator
1609 -- Costs IN NUMBER Cost flag
1610 -- Cost_type_id IN NUMBER Cost type id
1611 -- X_bom_header_tbl OUT
1612 -- X_bom_revisions_tbl OUT
1613 -- X_bom_components_tbl OUT
1614 -- X_bom_ref_designators_tbl OUT
1615 -- X_bom_sub_components_tbl OUT
1616 -- X_bom_comp_ops_tbl OUT
1617 -- Err_Msg OUT VARCHAR2 Error Message
1618 -- Error_Code OUT NUMBER Error Megssage
1619 --
1620 -- COMMENT : API Accepts the name of an hierarchy, Assembly item name,
1621 -- Organization code, Alternate bom designator, Costs,
1622 -- Cost type id. It returns the following six pl/sql tables:
1623 -- 1. P_bom_header_tbl ,
1624 -- 2. p_bom_revisions_tbl,
1625 -- 3. p_bom_components_tbl,
1626 -- 4. p_bom_ref_designators_tbl,
1627 -- 5. p_bom_sub_components_tbl,
1628 -- 6. p_bom_comp_ops_tbl
1629 -- p_bom_header_tbl consists of all bom header records. p_bom_revisions_tbl
1630 -- consists of all revisions for an assembly item withina bom.
1631 -- p_bom_components_tbl consists of all components of a bom.
1632 -- p_bom_ref_designators_tbl consists of the reference designators for each
1633 -- of the components within a bom. p_bom_sub_components_tbl consits of
1634 -- substitute components for each of the components within a bom.
1635 -- p_bom_comp_ops_tbl consists of component operations for each of the
1636 -- components within a bom. Error Code and corresponding Error
1637 -- mesages are returned in case of an error
1638 --
1639 --
1640 --========================================================================
1641 PROCEDURE EXPORT_BOM ( P_org_hierarchy_name IN VARCHAR2 DEFAULT NULL,
1642 P_assembly_item_name IN VARCHAR2,
1643 P_organization_code IN VARCHAR2,
1644 P_alternate_bm_designator IN VARCHAR2 DEFAULT NULL,
1645 P_costs IN NUMBER DEFAULT 2,
1646 P_cost_type_id IN NUMBER DEFAULT 0,
1647 X_bom_header_tbl OUT NOCOPY BOM_BO_PUB.BOM_HEADER_TBL_TYPE,
1648 X_bom_revisions_tbl OUT NOCOPY BOM_BO_PUB.BOM_REVISION_TBL_TYPE,
1649 X_bom_components_tbl OUT NOCOPY BOM_BO_PUB.BOM_COMPS_TBL_TYPE,
1650 X_bom_ref_designators_tbl OUT NOCOPY BOM_BO_PUB.BOM_REF_DESIGNATOR_TBL_TYPE,
1651 X_bom_sub_components_tbl OUT NOCOPY BOM_BO_PUB.BOM_SUB_COMPONENT_TBL_TYPE,
1652 X_bom_comp_ops_tbl OUT NOCOPY BOM_BO_PUB.BOM_COMP_OPS_TBL_TYPE,
1653 X_Err_Msg OUT NOCOPY VARCHAR2,
1654 X_Error_Code OUT NOCOPY NUMBER
1655 ) IS
1656 l_organization_id NUMBER;
1657 l_assembly_item_id NUMBER;
1658 l_profile_id NUMBER := FND_PROFILE.value('PER_SECURITY_PROFILE_ID');
1659
1660 l_bom_export_tab BOMPXINQ.BOMEXPORTTABTYPE;
1661
1662 CURSOR organization_code_CUR IS
1663 SELECT organization_id
1664 FROM mtl_parameters
1665 WHERE organization_code = P_organization_code;
1666
1667 CURSOR assembly_item_name_CUR IS
1668 SELECT inventory_item_id
1669 FROM mtl_system_items
1670 WHERE segment1 = P_assembly_item_name
1671 AND organization_id = l_organization_id;
1672 l_err_text VARCHAR2(2000);
1673 l_err_msg varchar2(2000);
1674 l_err_Code number;
1675
1676 BEGIN
1677 G_Header_Record_id_Tbl.DELETE;
1678 G_bom_header_tbl.DELETE;
1679 G_bom_revisions_tbl.DELETE;
1680 G_bom_components_tbl.DELETE;
1681 G_bom_ref_designators_tbl.DELETE;
1682 G_bom_sub_components_tbl.DELETE;
1683 G_bom_comp_ops_tbl.DELETE;
1684
1685 IF (l_profile_id = null) THEN
1686 --dbms_output.put_line('No profile PER_SECURITY_PROFILE_ID . . . ');
1687 RAISE no_profile;
1688 END IF;
1689
1690
1691 --dbms_output.put_line('profile PER_SECURITY_PROFILE_ID . . . ' || l_profile_id);
1692
1693 IF ((P_assembly_item_name IS NULL OR
1694 P_assembly_item_name = FND_API.G_MISS_CHAR) OR
1695 (
1696 (P_organization_code IS NULL OR
1697 P_organization_code = FND_API.G_MISS_CHAR
1698 ) AND
1699 (P_org_hierarchy_name IS NULL OR
1700 P_org_hierarchy_name = FND_API.G_MISS_CHAR
1701 )
1702 )
1703 )
1704 THEN
1705 RAISE missing_parameters;
1706 END IF;
1707
1708 if (P_organization_code is not null)
1709 then
1710 l_organization_id := BOM_Val_To_Id.Organization(p_organization => P_organization_code,
1711 x_err_text => l_err_text);
1712 end if;
1713
1714 IF (l_organization_id IS NULL OR
1715 l_organization_id = FND_API.G_MISS_NUM)
1716 AND P_organization_code is not null
1717 THEN
1718 --dbms_output.put_line('Invalid Organization . . . ');
1719 RAISE invalid_org;
1720 END IF;
1721
1722 --dbms_output.put_line('Organization . . . ' || l_organization_id);
1723 /*
1724 OPEN organization_code_CUR;
1725
1726 FETCH organization_code_CUR INTO l_organization_id;
1727 IF (organization_code_CUR%NOTFOUND) THEN
1728 RAISE invalid_org;
1729 END IF;
1730
1731 CLOSE organization_code_CUR;
1732 */
1733
1734 OPEN assembly_item_name_CUR;
1735
1736 FETCH assembly_item_name_CUR INTO l_assembly_item_id;
1737 IF (assembly_item_name_CUR%NOTFOUND) THEN
1738 RAISE invalid_assembly_item_name;
1739 END IF;
1740 CLOSE assembly_item_name_CUR;
1741
1742 --dbms_output.put_line('Assembly item id: ' || l_assembly_item_id);
1743
1744
1745 /* Call the existing Export_BOM that returns a single pl/sql table containing information about
1746 all the entities of a BOM
1747 */
1748 EXPORT_BOM(Profile_Id => l_profile_id,
1749 Org_Hierarchy_Name => P_org_hierarchy_name,
1750 Assembly_Item_Id => l_assembly_item_id,
1751 Organization_Id => l_organization_id,
1752 Alternate_Bm_Designator => P_alternate_bm_designator,
1753 Costs => P_costs,
1754 Cost_Type_Id => P_cost_type_id,
1755 Bom_Export_Tab => l_bom_export_tab,
1756 Err_Msg => l_Err_Msg,
1757 Error_Code => l_Err_Code);
1758
1759 x_err_msg := l_err_msg;
1760 x_error_code := l_err_code;
1761
1762 --dbms_output.put_line('exported in a single table . . . ' || l_bom_export_tab.COUNT);
1763 --dbms_output.put_line('error msg. . . ' || l_Err_Msg);
1764 --dbms_output.put_line('error code. . . ' || l_err_code);
1765
1766 IF (X_Error_Code = 0 AND l_bom_export_tab.COUNT <> 0) THEN
1767 FOR i IN l_bom_export_tab.FIRST..l_bom_export_tab.LAST LOOP
1768 IF NOT Header_Id_Exists(P_assembly_item_id => l_bom_export_tab(i).assembly_item_id,
1769 P_bill_sequence_id => l_bom_export_tab(i).bill_sequence_id) THEN
1770 IF (l_bom_export_tab(i).assembly_item_id IS NOT NULL) THEN
1771 Populate_Header(l_bom_export_tab(i).assembly_item_id,
1772 l_bom_export_tab(i).bill_sequence_id,
1773 l_bom_export_tab(i).organization_id,
1774 p_Alternate_Bm_Designator);
1775 ELSE
1776 Populate_Header(l_bom_export_tab(i).component_item_id,
1777 l_bom_export_tab(i).bill_sequence_id,
1778 l_bom_export_tab(i).organization_id,
1779 p_Alternate_Bm_Designator);
1780 END IF;
1781 END IF;
1782 IF (l_bom_export_tab(i).assembly_item_id IS NOT NULL) THEN
1783 Populate_Details(l_bom_export_tab(i).component_item_id,
1784 l_bom_export_tab(i).bill_sequence_id,
1785 l_bom_export_tab(i).component_sequence_id,
1786 l_bom_export_tab(i).organization_id);
1787 END IF;
1788
1789 END LOOP;
1790 X_Bom_Header_Tbl := G_Bom_Header_Tbl;
1791 X_Bom_Revisions_Tbl := G_Bom_Revisions_Tbl;
1792 X_Bom_Components_Tbl := G_Bom_Components_Tbl;
1793 X_Bom_Ref_Designators_Tbl := G_Bom_Ref_Designators_Tbl;
1794 X_Bom_Sub_Components_Tbl := G_Bom_Sub_Components_Tbl;
1795 X_Bom_Comp_Ops_Tbl := G_Bom_Comp_Ops_Tbl;
1796 END IF; -- Error_Code = 0
1797
1798 EXCEPTION
1799 WHEN no_profile THEN
1800 rollback;
1801 X_error_code := -117;
1802 FND_MESSAGE.Set_Name('BOM', 'BOM_NO_PROFILE');
1803 X_Err_Msg := FND_MESSAGE.Get;
1804
1805 WHEN missing_parameters THEN
1806 X_error_code := -112;
1807 FND_MESSAGE.Set_Name('BOM', 'BOM_ASSY_OR_ORG_MISSING');
1808 X_err_Msg := FND_MESSAGE.Get;
1809
1810 WHEN invalid_org THEN
1811 rollback;
1812 X_error_code := -121;
1813 FND_MESSAGE.Set_Name('BOM', 'BOM_INVALID_ORGANIZATION');
1814 FND_MESSAGE.Set_Token('L_ORGANIZATION_ID', P_organization_code);
1815 X_Err_Msg := FND_MESSAGE.Get;
1816
1817 WHEN invalid_assembly_item_name THEN
1818 rollback;
1819 X_error_code := -116;
1820 FND_MESSAGE.Set_Name('BOM', 'BOM_INVALID_ASSEMBLY_ITEM');
1821 FND_MESSAGE.Set_Token('ASSEMBLY_ITEM', P_assembly_item_name);
1822 X_Err_Msg := FND_MESSAGE.Get;
1823
1824 WHEN invalid_comp_seq_id THEN
1825 rollback;
1826 X_bom_header_tbl.DELETE;
1827 X_bom_revisions_tbl.DELETE;
1828 X_bom_components_tbl.DELETE;
1829 X_bom_ref_designators_tbl.DELETE;
1830 X_bom_sub_components_tbl.DELETE;
1831 X_bom_comp_ops_tbl.DELETE;
1832 X_error_code := -115;
1833 X_Err_Msg := FND_MESSAGE.Get;
1834
1835 WHEN invalid_locator_id THEN
1836 rollback;
1837 X_bom_header_tbl.DELETE;
1838 X_bom_revisions_tbl.DELETE;
1839 X_bom_components_tbl.DELETE;
1840 X_bom_ref_designators_tbl.DELETE;
1841 X_bom_sub_components_tbl.DELETE;
1842 X_bom_comp_ops_tbl.DELETE;
1843 X_error_code := -114;
1844 X_Err_Msg := FND_MESSAGE.Get;
1845
1846 WHEN invalid_bill_seq_id THEN
1847 rollback;
1848 X_bom_header_tbl.DELETE;
1849 X_bom_revisions_tbl.DELETE;
1850 X_bom_components_tbl.DELETE;
1851 X_bom_ref_designators_tbl.DELETE;
1852 X_bom_sub_components_tbl.DELETE;
1853 X_bom_comp_ops_tbl.DELETE;
1854 X_error_code := -113;
1855 X_Err_Msg := FND_MESSAGE.Get;
1856 END EXPORT_BOM;
1857
1858 END BOMPXINQ;