[Home] [Help]
PACKAGE BODY: APPS.BOMPXINQ
Source
1 package body BOMPXINQ as
2 /* $Header: BOMXINQB.pls 120.0 2005/05/25 05:49:28 appldev noship $ */
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 t_conversion_rate NUMBER;
355 t_master_org_id NUMBER;
356 t_child_uom varchar(3);
357 t_comp_qty NUMBER;
358 t_comp_extd_qty NUMBER;
359 t_master_uom varchar(3);
360 t_item_cost NUMBER;
361 Cursor cur is
362 Select BET.organization_id curOI,
363 BET.bill_sequence_id curBSI,
364 BET.component_sequence_id curCSI,
365 BET.component_item_id curCII,
366 BET.common_bill_sequence_id curCBSI,
367 BET.group_id curGI,
368 BET.primary_uom_code curPUC
369 from BOM_SMALL_EXPL_TEMP BET
370 where BET.bill_sequence_id <> BET.common_bill_sequence_id
371 and BET.group_id = grp_id;
372 --Bug 2088686
373
374 cursor conv (t_master_uom varchar2,
375 t_child_uom varchar2,
376 t_inv_id number,
377 t_master_org_id number) is
378 select conversion_rate
379 from mtl_uom_conversions_view
380 where primary_uom_code = t_master_uom and
381 uom_code = t_child_uom and
382 inventory_item_id = t_inv_id and
383 organization_id = t_master_org_id;
384
385 BEGIN
386 -- Added savepoint for bug 3863319
387 SAVEPOINT exploder_userexit_pvt;
388
389 X_SortWidth := BOMPBXIN.G_SortWidth;
390
391 IF (verify_flag = 1) AND (module <> 2) THEN
392 raise parameter_error;
393 END IF;
394
395 if (grp_id is null or item_id is null) then
396 raise parameter_error;
397 end if;
398
399 stmt_num := 2;
400 insert into bom_small_expl_temp
401 (
402 group_id,
403 bill_sequence_id,
404 component_sequence_id,
405 organization_id,
406 top_item_id,
407 component_item_id,
408 plan_level,
409 extended_quantity,
410 basis_type,
411 component_quantity,
412 sort_order,
413 program_update_date,
414 top_bill_sequence_id,
415 component_code,
416 loop_flag,
417 top_alternate_designator,
418 bom_item_type,
419 parent_bom_item_type
420 )
421 select
422 grp_id,
423 bom.bill_sequence_id,
424 NULL,
425 org_id,
426 item_id,
427 item_id,
428 0,
429 expl_qty,
430 1,
431 1,
432 lpad('1', X_SortWidth, '0'),
433 sysdate,
434 bom.bill_sequence_id,
435 nvl(comp_code, lpad(item_id, 16, '0')),
436 2,
437 alt_desg,
438 msi.bom_item_type,
439 msi.bom_item_type
440 from bom_bill_of_materials bom, mtl_system_items msi
441 where bom.assembly_item_id = item_id
442 and bom.organization_id = org_id
443 and nvl(alternate_bom_designator, 'NONE') =
444 nvl(alt_desg, 'NONE')
445 and msi.organization_id = org_id
446 and inventory_item_id = item_id;
447
448 if (SQL%NOTFOUND) then
449 raise no_data_found;
450 end if;
451
452 -- dbms_output.put_line('level 0 inserted . . . ');
453 Exploders(
454 verify_flag => verify_flag,
455 online_flag => 1,
456 org_id => org_id,
457 order_by => order_by,
458 grp_id => grp_id,
459 session_id => session_id,
460 l_levels_to_explode => levels_to_explode,
461 bom_or_eng => bom_or_eng,
462 impl_flag => impl_flag,
463 plan_factor_flag => plan_factor_flag,
464 l_explode_option => explode_option,
465 module => module,
466 unit_number_from => unit_number_from,
467 unit_number_to => unit_number_to,
468 cst_type_id => cst_type_id,
469 std_comp_flag => std_comp_flag,
470 rev_date => rev_date,
471 show_rev => show_rev,
472 material_ctrl => material_ctrl,
473 lead_time => lead_time,
474 err_msg => out_message,
475 error_code => out_code
476 );
477
478 if (verify_flag <> 1 and (out_code = 9999 or out_code = 9998
479 or out_code < 0)) then
480 raise exploder_error;
481 elsif (verify_flag = 1 and (out_code = 9998 or out_code < 0)) then
482 raise exploder_error;
483 end if;
484
485 if (module = 1) then
486 BOMPCEXP.cst_exploder(
487 grp_id => grp_id,
488 org_id => org_id,
489 cst_type_id => cst_type_id,
490 inq_flag => 1,
491 err_msg => out_message,
492 error_code => out_code);
493 end if;
494
495 if (verify_flag = 1) then
496 Loopstr2msg( grp_id, out_message );
497 end if;
498 -- Bug 2157325 Begin
499 -- If the master organization is referenced as the costing organization then
500 -- is_cost_organzation flag is set to 'N' else if the child organization itself
501 -- referenced as the costing organization then the is_cost_organization flag is
502 -- set to 'Y'.
503 --bug 2951874 corrected the following sql.
504
505 select count(*) into cnt
506 from mtl_parameters
507 where organization_id = cost_organization_id
508 and organization_id = org_id;
509
510 if (cnt >0) then
511 is_cost_organization := 'Y';
512 else
513 is_cost_organization := 'N';
514 end if;
515 -- Bug 2157325 End
516
517 -- Bug 2088686 Begin
518 -- If the Intended Bill is referenced some other bill of different organization
519 -- then the conversion rate, uom of the component in the child organization
520 -- should be calculated.
521
522 FOR cr IN cur LOOP
523 select msi.primary_uom_code, msi.organization_id into
524 t_master_uom, t_master_org_id
525 from mtl_system_items msi, bom_bill_of_materials bbm
526 where cr.curCBSI = bbm.bill_sequence_id and
527 bbm.organization_id = msi.organization_id and
528 msi.inventory_item_id = cr.curCII;
529
530 select msi.primary_uom_code into t_child_uom
531 from mtl_system_items msi
532 where msi.inventory_item_id = cr.curCII and
533 msi.organization_id = cr.curOI;
534
535 /* Bug 2663515
536 select conversion_rate into t_conversion_rate
537 from mtl_uom_conversions_view
538 where primary_uom_code = t_master_uom and
539 uom_code = t_child_uom and
540 inventory_item_id = cr.curCII and
541 organization_id = t_master_org_id;
542 */
543
544 OPEN conv(t_master_uom, t_child_uom, cr.curCII, t_master_org_id);
545 Fetch conv into t_conversion_rate;
546 if conv%NOTFOUND then
547 close conv; -- added for Bug #2994556
548 raise inv_uom_conv_exe;
549 End if;
550 close conv; -- added for Bug #2994556
551
552
553 -- Bug 2157325 Begin
554 -- If cost_organization is Master organization then the item cost should be
555 -- calculated by multiplying the conversion_rate.
556
557 if is_cost_organization <> 'Y' then
558 UPDATE BOM_SMALL_EXPL_TEMP
559 SET item_cost = item_cost*t_conversion_rate
560 WHERE group_id = cr.curGI and
561 component_sequence_id = cr.curCSI and
562 bill_sequence_id = cr.curBSI and
563 common_bill_sequence_id = cr.curCBSI;
564 end if;
565 --Bug 2157325 End
566
567 UPDATE BOM_SMALL_EXPL_TEMP
568 SET component_quantity = component_quantity/t_conversion_rate,
569 extended_quantity = extended_quantity/t_conversion_rate,
570 -- item_cost = item_cost*t_conversion_rate,
571 primary_uom_code = cr.curPUC
572 WHERE group_id = cr.curGI and
573 component_sequence_id = cr.curCSI and
574 bill_sequence_id = cr.curBSI and
575 common_bill_sequence_id = cr.curCBSI;
576
577 END LOOP;
578 -- Bug 2088686 End
579 error_code := out_code;
580 err_msg := out_message;
581
582 EXCEPTION
583 when exploder_error then
584 error_code := out_code;
585 err_msg := out_message;
586 WHEN parameter_error THEN
587 error_code := -1;
588 Fnd_Msg_Pub.Build_Exc_Msg(
589 p_pkg_name => 'BOMPXINQ',
590 p_procedure_name => 'exploder_userexit',
591 p_error_text => 'verify parameters');
592 err_msg := Fnd_Message.Get_Encoded;
593 WHEN inv_uom_conv_exe THEN
594 FND_MESSAGE.SET_NAME('BOM','BOM_UOMCV_INVUOMTYPE_ERR');
595 fnd_message.Set_Token('FROMUOM',t_master_uom);
596 fnd_message.Set_Token('TOUOM',t_child_uom);
597 fnd_message.raise_error;
598
599 WHEN OTHERS THEN
600 error_code := SQLCODE;
601 Fnd_Msg_Pub.Build_Exc_Msg(
602 p_pkg_name => 'BOMPXINQ',
603 p_procedure_name => 'exploder_userexit',
604 p_error_text => SQLERRM);
605 err_msg := Fnd_Message.Get_Encoded;
606 ROLLBACK TO exploder_userexit_pvt; -- Added for bug: 3863319
607 END exploder_userexit;
608
609 --========================================================================
610 -- PROCEDURE : Export_BOM
611 -- PARAMETERS : Profile_id IN NUMBER Security Profile Id
612 -- Org_hierarchy_name IN VARCHAR2 Organization Hierarchy
613 -- Name
614 -- Assembly_item_id IN NUMBER Assembly item id
615 -- Organization_id IN NUMBER Organization id
616 -- Alternate_bm_designator IN VARCHAR2 Alternate bom designator
617 -- Costs IN NUMBER Cost flag
618 -- Cost_type_id IN NUMBER Cost type id
619 -- bom_export_tab OUT bomexporttabtype export table
620 -- Err_Msg OUT VARCHAR2 Error Message
621 -- Error_Code OUT NUMBER Error Megssage
622 --
623 -- COMMENT : API Accepts the security profile id,name of an hierarchy,
624 -- Assembly item id, Organization id, Alternate bom designator,
625 -- Costs, Cost type id and returns bom_export_tab PL/SQL table
626 -- consists of exploded BOM for all the organizations under
627 -- the hierarchy name. Error Code and corresponding Error
628 -- mesages are returned in case of an error
629 --
630 --========================================================================
631 PROCEDURE EXPORT_BOM ( Profile_id IN NUMBER,
632 Org_hierarchy_name IN VARCHAR2,
633 Assembly_item_id IN NUMBER,
634 Organization_id IN NUMBER,
635 Alternate_bm_designator IN VARCHAR2 DEFAULT '',
636 Costs IN NUMBER DEFAULT 2,
637 Cost_type_id IN NUMBER DEFAULT 0,
638 bom_export_tab OUT NOCOPY bomexporttabtype,
639 Err_Msg OUT NOCOPY VARCHAR2,
640 Error_Code OUT NOCOPY NUMBER )
641 IS
642 t_org_code_list INV_OrgHierarchy_PVT.OrgID_tbl_type;
643 l_Org_hierarchy_name VARCHAR2(30);
644 l_assembly_item_id NUMBER;
645 l_organization_id NUMBER;
646 l_cst_type_id NUMBER;
647
648 max_level NUMBER;
649 l_group_id NUMBER;
650 l_org_name VARCHAR2(60);
651 c_Cost_type_id NUMBER;
652 c_assembly_item_id NUMBER;
653 i_count NUMBER :=1;
654 l_assembly_found BOOLEAN :=TRUE;
655 l_org_count NUMBER;
656
657 no_organization EXCEPTION;
658 explode_error EXCEPTION;
659 no_hierarchy EXCEPTION;
660 cost_type EXCEPTION;
661 no_level_access EXCEPTION;
662 no_assy EXCEPTION;
663 no_list EXCEPTION;
664
665 -- cursor to obtain exploded bom from bom_small_expl_temp table
666 CURSOR export_tab (l_organization_id NUMBER, l_group_id NUMBER) IS
667 SELECT
668 TOP_BILL_SEQUENCE_ID ,
669 BILL_SEQUENCE_ID ,
670 COMMON_BILL_SEQUENCE_ID ,
671 ORGANIZATION_ID ,
672 COMPONENT_SEQUENCE_ID ,
673 COMPONENT_ITEM_ID ,
674 BASIS_TYPE ,
675 COMPONENT_QUANTITY ,
676 PLAN_LEVEL ,
677 EXTENDED_QUANTITY ,
678 SORT_ORDER ,
679 GROUP_ID ,
680 TOP_ALTERNATE_DESIGNATOR ,
681 COMPONENT_YIELD_FACTOR ,
682 TOP_ITEM_ID ,
683 COMPONENT_CODE ,
684 INCLUDE_IN_ROLLUP_FLAG ,
685 LOOP_FLAG ,
686 PLANNING_FACTOR ,
687 OPERATION_SEQ_NUM ,
688 BOM_ITEM_TYPE ,
689 PARENT_BOM_ITEM_TYPE ,
690 ASSEMBLY_ITEM_ID ,
691 WIP_SUPPLY_TYPE ,
692 ITEM_NUM ,
693 EFFECTIVITY_DATE ,
694 DISABLE_DATE ,
695 IMPLEMENTATION_DATE ,
696 OPTIONAL ,
697 SUPPLY_SUBINVENTORY ,
698 SUPPLY_LOCATOR_ID ,
699 COMPONENT_REMARKS ,
700 CHANGE_NOTICE ,
701 OPERATION_LEAD_TIME_PERCENT,
702 MUTUALLY_EXCLUSIVE_OPTIONS ,
703 CHECK_ATP ,
704 REQUIRED_TO_SHIP ,
705 REQUIRED_FOR_REVENUE ,
706 INCLUDE_ON_SHIP_DOCS ,
707 LOW_QUANTITY ,
708 HIGH_QUANTITY ,
709 SO_BASIS ,
710 OPERATION_OFFSET ,
711 CURRENT_REVISION ,
712 LOCATOR ,
713 CONTEXT ,
714 ATTRIBUTE1 ,
715 ATTRIBUTE2 ,
716 ATTRIBUTE3 ,
717 ATTRIBUTE4 ,
718 ATTRIBUTE5 ,
719 ATTRIBUTE6 ,
720 ATTRIBUTE7 ,
721 ATTRIBUTE8 ,
722 ATTRIBUTE9 ,
723 ATTRIBUTE10 ,
724 ATTRIBUTE11 ,
725 ATTRIBUTE12 ,
726 ATTRIBUTE13 ,
727 ATTRIBUTE14 ,
728 ATTRIBUTE15 ,
729 ITEM_COST ,
730 EXTEND_COST_FLAG
731 FROM bom_small_expl_temp
732 WHERE
733 Organization_id = l_organization_id
734 AND GROUP_ID = l_group_id;
735
736 BEGIN
737 l_assembly_item_id := Assembly_item_id;
738 l_organization_id := Organization_id;
739 c_Cost_type_id := Cost_type_id;
740
741 --Set the Security Profile value as passed by the user
742 FND_PROFILE.put('PER_SECURITY_PROFILE_ID',profile_id);
743
744 --dbms_output.put_line('within export_Bom . . . ');
745
746 -- Validate the Organization Hierarchy name and check,if the access allowed
747 if (Org_hierarchy_name is not null)
748 then
749 SELECT count (*) into l_org_count from
750 per_organization_structures
751 WHERE INV_ORGHIERARCHY_PVT.ORG_HIERARCHY_ACCESS(Org_hierarchy_name)='Y'
752 AND name = Org_hierarchy_name;
753
754 if (l_org_count <1 ) then
755 RAISE no_hierarchy;
756 end if;
757 --dbms_output.put_line('org count in heirarchy is more than 0 . . . ');
758
759 end if;
760
761 /* BEGIN
762 -- Get the corresponding Organization name
763 SELECT organization_name into l_org_name
764 FROM org_organization_definitions
765 WHERE organization_id = l_organization_id;
766
767 EXCEPTION
768 WHEN NO_DATA_FOUND THEN
769 RAISE no_organization;
770 END;
771 */
772
773 if (Org_hierarchy_name is null OR Org_hierarchy_name = '')
774 THEN
775 --dbms_output.put_line('Org Hierarachy is null ' );
776 t_org_code_list(1) := l_organization_id;
777 ELSE
778 if (INV_ORGHIERARCHY_PVT.ORG_HIERARCHY_LEVEL_ACCESS(Org_hierarchy_name,
779 l_organization_id)= 'Y') THEN
780 INV_ORGHIERARCHY_PVT.ORG_HIERARCHY_LIST (Org_hierarchy_name,
781 l_organization_id ,t_org_code_list);
782 else
783 RAISE no_level_access;
784 end if;
785 END IF;
786
787 -- For each Organization, if the assembly exists then call the bom exploder
788 FOR I in t_org_code_list.FIRST..t_org_code_list.LAST LOOP
789 BEGIN
790 --dbms_output.put_line('organization: ' || t_org_code_list(I));
791
792 SELECT assembly_item_id INTO c_assembly_item_id
793 FROM bom_bill_of_materials
794 WHERE assembly_item_id = l_assembly_item_id
795 AND organization_id = t_org_code_list(I)
796 AND nvl(ALTERNATE_BOM_DESIGNATOR,'NONE')=
797 nvl(Alternate_bm_designator,'NONE') ;
798 EXCEPTION
799 WHEN NO_DATA_FOUND THEN
800 l_assembly_found :=FALSE;
801 END;
802
803 if l_assembly_found then
804 BEGIN
805 if ( costs = 1) then
806 SELECT COST_TYPE_ID into l_cst_type_id
807 FROM cst_item_cost_type_v
808 WHERE inventory_item_id = Assembly_item_id
809 AND cost_type_id = c_Cost_type_id
810 AND organization_id = t_org_code_list(I);
811 end if;
812 EXCEPTION
813 WHEN NO_DATA_FOUND THEN
814 l_organization_id := t_org_code_list(I);
815 RAISE cost_type;
816 END;
817 --Get the maximum level for explosion is allowed for the Organization
818 SELECT MAXIMUM_BOM_LEVEL INTO max_level
819 FROM BOM_PARAMETERS
820 WHERE ORGANIZATION_ID = t_org_code_list(I);
821
822 SELECT bom_explosion_temp_s.nextval
823 INTO l_group_id from dual;
824
825 DELETE from bom_small_expl_temp where group_id =l_group_id;
826
827 --dbms_output.put_line('calling explosion . . . ');
828
829 exploder_userexit (
830 verify_flag => 0 ,
831 org_id => t_org_code_list(I) ,
832 order_by => 2 ,
833 grp_id => l_group_id ,
834 session_id => 0 ,
835 levels_to_explode => max_level,
836 bom_or_eng => 1,
837 impl_flag => 1,
838 plan_factor_flag => 2,
839 explode_option => 3,
840 module => costs ,
841 unit_number_from => NULL,
842 unit_number_to => NULL,
843 cst_type_id => cost_type_id ,
844 std_comp_flag => 2 ,
845 expl_qty => 1,
846 item_id => Assembly_item_id ,
847 alt_desg => alternate_bm_designator ,
848 comp_code => null ,
849 rev_date => sysdate ,
850 show_rev => 1 ,
851 material_ctrl => 1,
852 lead_time => 1,
853 err_msg => err_msg ,
854 error_code => Error_Code );
855 if error_code = 9998 then
856 l_organization_id := t_org_code_list(I);
857 RAISE explode_error;
858 end if ;
859 if (error_code = 0 or error_code is NULL) then
860 -- Copy the data into the PL/SQL table
861 FOR loop_tab IN export_tab ( t_org_code_list(I),l_group_id)
862 LOOP
863 bom_export_tab(i_count).TOP_BILL_SEQUENCE_ID :=
864 loop_tab.TOP_BILL_SEQUENCE_ID;
865 bom_export_tab(i_count).BILL_SEQUENCE_ID :=
866 loop_tab.BILL_SEQUENCE_ID;
867 bom_export_tab(i_count).COMMON_BILL_SEQUENCE_ID :=
868 loop_tab.COMMON_BILL_SEQUENCE_ID;
869 bom_export_tab(i_count).ORGANIZATION_ID :=
870 loop_tab.ORGANIZATION_ID ;
871 bom_export_tab(i_count).COMPONENT_SEQUENCE_ID :=
872 loop_tab.COMPONENT_SEQUENCE_ID ;
873 bom_export_tab(i_count).COMPONENT_ITEM_ID :=
874 loop_tab.COMPONENT_ITEM_ID ;
875 bom_export_tab(i_count).BASIS_TYPE:= loop_tab.BASIS_TYPE;
876 bom_export_tab(i_count).COMPONENT_QUANTITY :=
877 loop_tab.COMPONENT_QUANTITY ;
878 bom_export_tab(i_count).PLAN_LEVEL := loop_tab.PLAN_LEVEL;
879 bom_export_tab(i_count).EXTENDED_QUANTITY :=
880 loop_tab.EXTENDED_QUANTITY ;
881 bom_export_tab(i_count).SORT_ORDER :=
882 loop_tab.SORT_ORDER ;
883 bom_export_tab(i_count).GROUP_ID :=
884 loop_tab.GROUP_ID ;
885 bom_export_tab(i_count).TOP_ALTERNATE_DESIGNATOR :=
886 loop_tab.TOP_ALTERNATE_DESIGNATOR;
887 bom_export_tab(i_count).COMPONENT_YIELD_FACTOR :=
888 loop_tab.COMPONENT_YIELD_FACTOR ;
889 bom_export_tab(i_count).TOP_ITEM_ID :=
890 loop_tab.TOP_ITEM_ID ;
891 bom_export_tab(i_count).COMPONENT_CODE :=
892 loop_tab.COMPONENT_CODE ;
893 bom_export_tab(i_count).INCLUDE_IN_ROLLUP_FLAG :=
894 loop_tab.INCLUDE_IN_ROLLUP_FLAG ;
895 bom_export_tab(i_count).LOOP_FLAG := loop_tab.LOOP_FLAG ;
896 bom_export_tab(i_count).PLANNING_FACTOR :=
897 loop_tab. PLANNING_FACTOR ;
898 bom_export_tab(i_count).OPERATION_SEQ_NUM :=
899 loop_tab.OPERATION_SEQ_NUM ;
900 bom_export_tab(i_count).BOM_ITEM_TYPE := loop_tab.BOM_ITEM_TYPE;
901 bom_export_tab(i_count).PARENT_BOM_ITEM_TYPE :=
902 loop_tab.PARENT_BOM_ITEM_TYPE ;
903 bom_export_tab(i_count).ASSEMBLY_ITEM_ID :=
904 loop_tab.ASSEMBLY_ITEM_ID;
905 bom_export_tab(i_count).WIP_SUPPLY_TYPE :=
906 loop_tab.WIP_SUPPLY_TYPE ;
907 bom_export_tab(i_count).ITEM_NUM := loop_tab.ITEM_NUM ;
908 bom_export_tab(i_count).EFFECTIVITY_DATE :=
909 loop_tab.EFFECTIVITY_DATE;
910 bom_export_tab(i_count).DISABLE_DATE :=
911 loop_tab.DISABLE_DATE ;
912 bom_export_tab(i_count).IMPLEMENTATION_DATE :=
913 loop_tab.IMPLEMENTATION_DATE ;
914 bom_export_tab(i_count).OPTIONAL := loop_tab.OPTIONAL ;
915 bom_export_tab(i_count).SUPPLY_SUBINVENTORY :=
916 loop_tab.SUPPLY_SUBINVENTORY ;
917 bom_export_tab(i_count).SUPPLY_LOCATOR_ID :=
918 loop_tab.SUPPLY_LOCATOR_ID ;
919 bom_export_tab(i_count).COMPONENT_REMARKS :=
920 loop_tab.COMPONENT_REMARKS ;
921 bom_export_tab(i_count).CHANGE_NOTICE :=
922 loop_tab.CHANGE_NOTICE ;
923 bom_export_tab(i_count).OPERATION_LEAD_TIME_PERCENT :=
924 loop_tab.OPERATION_LEAD_TIME_PERCENT;
925 bom_export_tab(i_count).MUTUALLY_EXCLUSIVE_OPTIONS :=
926 loop_tab.MUTUALLY_EXCLUSIVE_OPTIONS;
927 bom_export_tab(i_count).CHECK_ATP := loop_tab.CHECK_ATP ;
928 bom_export_tab(i_count).REQUIRED_TO_SHIP :=
929 loop_tab.REQUIRED_TO_SHIP ;
930 bom_export_tab(i_count).REQUIRED_FOR_REVENUE :=
931 loop_tab.REQUIRED_FOR_REVENUE ;
932 bom_export_tab(i_count).INCLUDE_ON_SHIP_DOCS :=
933 loop_tab.INCLUDE_ON_SHIP_DOCS ;
934 bom_export_tab(i_count).LOW_QUANTITY := loop_tab.LOW_QUANTITY;
935 bom_export_tab(i_count).HIGH_QUANTITY := loop_tab.HIGH_QUANTITY;
936 bom_export_tab(i_count).SO_BASIS := loop_tab.SO_BASIS ;
937 bom_export_tab(i_count).OPERATION_OFFSET :=
938 loop_tab.OPERATION_OFFSET ;
939 bom_export_tab(i_count).CURRENT_REVISION :=
940 loop_tab.CURRENT_REVISION ;
941 bom_export_tab(i_count).LOCATOR := loop_tab.LOCATOR ;
942 bom_export_tab(i_count).CONTEXT := loop_tab.CONTEXT ;
943 bom_export_tab(i_count).ATTRIBUTE1 := loop_tab.ATTRIBUTE1 ;
944 bom_export_tab(i_count).ATTRIBUTE2 := loop_tab.ATTRIBUTE2 ;
945 bom_export_tab(i_count).ATTRIBUTE3 := loop_tab.ATTRIBUTE3 ;
946 bom_export_tab(i_count).ATTRIBUTE4 := loop_tab.ATTRIBUTE4 ;
947 bom_export_tab(i_count).ATTRIBUTE5 := loop_tab.ATTRIBUTE5 ;
948 bom_export_tab(i_count).ATTRIBUTE6 := loop_tab.ATTRIBUTE6 ;
949 bom_export_tab(i_count).ATTRIBUTE7 := loop_tab.ATTRIBUTE7 ;
950 bom_export_tab(i_count).ATTRIBUTE8 := loop_tab.ATTRIBUTE8 ;
951 bom_export_tab(i_count).ATTRIBUTE9 := loop_tab.ATTRIBUTE9 ;
952 bom_export_tab(i_count).ATTRIBUTE10 := loop_tab.ATTRIBUTE10;
953 bom_export_tab(i_count).ATTRIBUTE11 := loop_tab.ATTRIBUTE11;
954 bom_export_tab(i_count).ATTRIBUTE12 := loop_tab.ATTRIBUTE12;
955 bom_export_tab(i_count).ATTRIBUTE13 := loop_tab.ATTRIBUTE13;
956 bom_export_tab(i_count).ATTRIBUTE14 := loop_tab.ATTRIBUTE14;
957 bom_export_tab(i_count).ATTRIBUTE15 := loop_tab.ATTRIBUTE15;
958 bom_export_tab(i_count).ITEM_COST := loop_tab.ITEM_COST ;
959 bom_export_tab(i_count).EXTEND_COST_FLAG :=
960 loop_tab.EXTEND_COST_FLAG ;
961 i_count := i_count +1;
962 end loop ;
963 DELETE from bom_small_expl_temp where group_id =l_group_id;
964 end if;
965 end if;
966 l_assembly_found := TRUE;
967 END LOOP;
968 error_code := 0;
969 Err_Msg := NULL ;
970
971 EXCEPTION
972 WHEN no_level_access THEN
973 rollback ;
974 bom_export_tab.delete;
975 error_code := -122;
976 Fnd_Message.Set_Name('BOM','BOM_NO_ORG_LEVEL_ACCESS');
977 Fnd_Message.Set_Token('l_org_name',l_org_name);
978 Fnd_Message.Set_Token('Org_hierarchy_name',Org_hierarchy_name);
979 Err_Msg := Fnd_Message.Get;
980 WHEN no_organization THEN
981 rollback ;
982 bom_export_tab.delete;
983 error_code := -121;
984 Fnd_Message.Set_Name('BOM','BOM_INVALID_ORGANIZATION');
985 Fnd_Message.Set_Token('l_organization_id',l_organization_id);
986 Err_Msg := Fnd_Message.Get;
987 WHEN explode_error THEN
988 rollback;
989 bom_export_tab.delete;
990 error_code := -120;
991 Fnd_Message.Set_Name('BOM','BOM_ORG_LEVELS_EXCEEDED');
992 Fnd_Message.Set_Token('Assembly',Assembly_item_id);
993 Fnd_Message.Set_Token('Orgid',l_organization_id);
994 Err_Msg:= Fnd_Message.Get;
995 WHEN no_hierarchy THEN
996 rollback ;
997 bom_export_tab.delete;
998 error_code := -119;
999 Fnd_Message.Set_Name('BOM','BOM_INVALID_HIER_OR_ACCESS');
1000 Fnd_Message.Set_Token('Org_hierarchy_name',Org_hierarchy_name);
1001 Err_Msg := Fnd_Message.Get;
1002 --dbms_output.put_line('Error: no_hierarchy ' || Err_Msg);
1003 WHEN cost_type THEN
1004 rollback ;
1005 bom_export_tab.delete;
1006 error_code := -118;
1007 Fnd_Message.Set_Name('BOM','BOM_COST_TYPE_INVALID');
1008 Fnd_Message.Set_Token('Cost_type',c_Cost_type_id);
1009 Fnd_Message.Set_Token('Orgid',l_organization_id);
1010 Err_Msg := Fnd_Message.Get;
1011 WHEN OTHERS THEN
1012 rollback ;
1013 bom_export_tab.delete ;
1014 error_code := SQLCODE;
1015 Err_Msg := SQLERRM;
1016 END;
1017
1018 FUNCTION Get_Item_Name(P_item_id IN NUMBER,
1019 P_organization_id IN NUMBER)
1020 RETURN VARCHAR2 IS
1021 l_item_name MTL_SYSTEM_ITEMS_KFV.concatenated_segments%TYPE;
1022 BEGIN
1023 SELECT concatenated_segments
1024 INTO l_item_name
1025 FROM mtl_system_items_kfv
1026 WHERE inventory_item_id = P_item_id
1027 AND organization_id = P_organization_id;
1028 RETURN l_item_name;
1029 EXCEPTION
1030 WHEN NO_DATA_FOUND THEN
1031 RAISE invalid_assembly_item_name;
1032 END Get_Item_Name;
1033
1034
1035 FUNCTION Get_Org_Code(P_organization_id IN NUMBER)
1036 RETURN VARCHAR2 IS
1037 l_org_code VARCHAR2(3);
1038 BEGIN
1039 SELECT organization_code
1040 INTO l_org_code
1041 FROM mtl_parameters
1042 WHERE organization_id = P_organization_id;
1043 RETURN l_org_code;
1044 EXCEPTION
1045 WHEN NO_DATA_FOUND THEN
1046 RAISE Invalid_Org;
1047 END;
1048
1049 FUNCTION Header_Id_Exists(P_assembly_item_id IN NUMBER,
1050 P_bill_sequence_id IN NUMBER)
1051 RETURN BOOLEAN IS
1052 BEGIN
1053 IF (G_Header_Record_Id_Tbl.COUNT <= 0) THEN
1054 RETURN FALSE;
1055 END IF;
1056 FOR i IN G_Header_Record_Id_Tbl.FIRST..G_Header_Record_Id_Tbl.LAST LOOP
1057 IF (G_Header_Record_Id_Tbl(i).assembly_item_id = P_assembly_item_id AND
1058 G_Header_Record_Id_Tbl(i).bill_sequence_id = P_bill_sequence_id) THEN
1059 RETURN TRUE;
1060 END IF;
1061 END LOOP;
1062 RETURN FALSE;
1063 END Header_Id_Exists;
1064
1065 FUNCTION Get_Locator_Name(P_locator_id IN NUMBER,
1066 P_organization_id IN NUMBER)
1067 RETURN VARCHAR2 IS
1068 CURSOR locator_name_CUR IS
1069 SELECT concatenated_segments
1070 FROM mtl_item_locations_kfv
1071 WHERE inventory_location_id = P_locator_id
1072 AND organization_id = P_organization_id;
1073 l_locator_name MTL_ITEM_LOCATIONS_KFV.Concatenated_Segments%TYPE;
1074 BEGIN
1075 OPEN locator_name_CUR;
1076 FETCH locator_name_CUR INTO l_locator_name;
1077 IF (locator_name_CUR%NOTFOUND) THEN
1078 RAISE invalid_locator_id;
1079 END IF;
1080 RETURN l_locator_name;
1081 END Get_Locator_Name;
1082
1083 PROCEDURE Populate_Header(P_assembly_item_id IN NUMBER,
1084 P_bill_sequence_id IN NUMBER,
1085 P_organization_id IN NUMBER,
1086 P_alternate_bm_designator IN VARCHAR2) IS
1087 CURSOR Bill_Details_CUR IS
1088 SELECT specific_assembly_comment,
1089 assembly_type,
1090 common_assembly_item_id,
1091 common_organization_id,
1092 original_system_reference,
1093 alternate_bom_designator,
1094 attribute_category,
1095 attribute1,
1096 attribute2,
1097 attribute3,
1098 attribute4,
1099 attribute5,
1100 attribute6,
1101 attribute7,
1102 attribute8,
1103 attribute9,
1104 attribute10,
1105 attribute11,
1106 attribute12,
1107 attribute13,
1108 attribute14,
1109 attribute15
1110 FROM bom_bill_of_materials
1111 WHERE bill_sequence_id = p_bill_sequence_id;
1112 --AND NVL(alternate_bom_designator, '##$$##') = NVL(P_alternate_bm_designator, '##$$##');
1113
1114 CURSOR Revision_Details_CUR IS
1115 SELECT revision,
1116 description,
1117 effectivity_date,
1118 attribute_category,
1119 attribute1,
1120 attribute2,
1121 attribute3,
1122 attribute4,
1123 attribute5,
1124 attribute6,
1125 attribute7,
1126 attribute8,
1127 attribute9,
1128 attribute10,
1129 attribute11,
1130 attribute12,
1131 attribute13,
1132 attribute14,
1133 attribute15
1134 FROM mtl_item_revisions
1135 WHERE inventory_item_id = P_assembly_item_id
1136 AND organization_id = P_organization_id;
1137
1138 l_bill_details Bill_Details_CUR%ROWTYPE;
1139 l_common_assembly_item_name MTL_SYSTEM_ITEMS_KFV.concatenated_segments%TYPE;
1140 l_common_org_code VARCHAR2(3);
1141 l_item_name MTL_SYSTEM_ITEMS_KFV.concatenated_segments%TYPE;
1142 l_org_code VARCHAR2(3);
1143 l_cnt NUMBER;
1144 l_counter NUMBER;
1145 l_count NUMBER;
1146 BEGIN
1147
1148 l_count := G_Header_Record_Id_Tbl.LAST + 1;
1149 IF (l_count IS NULL) THEN
1150 l_count := 1;
1151 END IF;
1152 G_Header_Record_Id_Tbl(l_count).bill_sequence_id := P_bill_sequence_id;
1153 G_Header_Record_Id_Tbl(l_count).assembly_item_id := P_assembly_item_id;
1154
1155
1156 l_item_name := Get_Item_Name(P_assembly_item_id, P_organization_id);
1157 l_org_code := Get_Org_Code(P_organization_id);
1158
1159 OPEN Bill_Details_CUR;
1160 FETCH Bill_Details_CUR INTO l_bill_details;
1161 IF (Bill_Details_CUR%NOTFOUND) THEN
1162 RAISE invalid_bill_seq_id;
1163 END IF;
1164 CLOSE Bill_Details_CUR;
1165
1166 IF l_bill_details.common_assembly_item_id IS NOT NULL THEN
1167 l_common_assembly_item_name := Get_Item_Name(l_bill_details.common_assembly_item_id,
1168 l_bill_details.common_organization_id);
1169 l_common_org_code := Get_Org_Code(l_bill_details.common_organization_id);
1170 END IF;
1171
1172 l_cnt := G_Bom_Header_Tbl.LAST + 1;
1173 IF (l_cnt IS NULL) THEN
1174 l_cnt := 1;
1175 END IF;
1176
1177 G_Bom_Header_Tbl(l_cnt).assembly_item_name := l_item_name;
1178 G_Bom_Header_Tbl(l_cnt).organization_code := l_org_code;
1179 G_Bom_Header_Tbl(l_cnt).alternate_bom_code := l_bill_details.alternate_bom_designator;
1180 G_Bom_Header_Tbl(l_cnt).common_assembly_item_name := l_common_assembly_item_name;
1181 G_Bom_Header_Tbl(l_cnt).common_organization_code := l_common_org_code;
1182 G_Bom_Header_Tbl(l_cnt).assembly_comment := l_bill_details.specific_assembly_comment;
1183 G_Bom_Header_Tbl(l_cnt).assembly_type := l_bill_details.assembly_type;
1184 G_Bom_Header_Tbl(l_cnt).attribute_category := l_bill_details.attribute_category;
1185 G_Bom_Header_Tbl(l_cnt).attribute1 := l_bill_details.attribute1;
1186 G_Bom_Header_Tbl(l_cnt).attribute2 := l_bill_details.attribute2;
1187 G_Bom_Header_Tbl(l_cnt).attribute3 := l_bill_details.attribute3;
1188 G_Bom_Header_Tbl(l_cnt).attribute4 := l_bill_details.attribute4;
1189 G_Bom_Header_Tbl(l_cnt).attribute5 := l_bill_details.attribute5;
1190 G_Bom_Header_Tbl(l_cnt).attribute6 := l_bill_details.attribute6;
1191 G_Bom_Header_Tbl(l_cnt).attribute7 := l_bill_details.attribute7;
1192 G_Bom_Header_Tbl(l_cnt).attribute8 := l_bill_details.attribute8;
1193 G_Bom_Header_Tbl(l_cnt).attribute9 := l_bill_details.attribute9;
1194 G_Bom_Header_Tbl(l_cnt).attribute10 := l_bill_details.attribute10;
1195 G_Bom_Header_Tbl(l_cnt).attribute11 := l_bill_details.attribute11;
1196 G_Bom_Header_Tbl(l_cnt).attribute12 := l_bill_details.attribute12;
1197 G_Bom_Header_Tbl(l_cnt).attribute13 := l_bill_details.attribute13;
1198 G_Bom_Header_Tbl(l_cnt).attribute14 := l_bill_details.attribute14;
1199 G_Bom_Header_Tbl(l_cnt).attribute15 := l_bill_details.attribute15;
1200 G_Bom_Header_Tbl(l_cnt).original_system_reference := l_bill_details.original_system_reference;
1201
1202 l_counter := G_Bom_Revisions_Tbl.LAST + 1;
1203 IF (l_counter IS NULL) THEN
1204 l_counter := 1;
1205 END IF;
1206 FOR l_revision_details IN Revision_Details_CUR LOOP
1207 G_Bom_Revisions_Tbl(l_counter).assembly_item_name := l_item_name;
1208 G_Bom_Revisions_Tbl(l_counter).organization_code := l_org_code;
1209 G_Bom_Revisions_Tbl(l_counter).revision := l_revision_details.revision;
1210 G_Bom_Revisions_Tbl(l_counter).alternate_bom_code := l_bill_details.alternate_bom_designator;
1211 G_Bom_Revisions_Tbl(l_counter).description := l_revision_details.description;
1212 G_Bom_Revisions_Tbl(l_counter).start_effective_date := l_revision_details.effectivity_date;
1213 G_Bom_Revisions_Tbl(l_counter).attribute1 := l_revision_details.attribute1;
1214 G_Bom_Revisions_Tbl(l_counter).attribute2 := l_revision_details.attribute2;
1215 G_Bom_Revisions_Tbl(l_counter).attribute3 := l_revision_details.attribute3;
1216 G_Bom_Revisions_Tbl(l_counter).attribute4 := l_revision_details.attribute4;
1217 G_Bom_Revisions_Tbl(l_counter).attribute5 := l_revision_details.attribute5;
1218 G_Bom_Revisions_Tbl(l_counter).attribute6 := l_revision_details.attribute6;
1219 G_Bom_Revisions_Tbl(l_counter).attribute7 := l_revision_details.attribute7;
1220 G_Bom_Revisions_Tbl(l_counter).attribute8 := l_revision_details.attribute8;
1221 G_Bom_Revisions_Tbl(l_counter).attribute9 := l_revision_details.attribute9;
1222 G_Bom_Revisions_Tbl(l_counter).attribute10 := l_revision_details.attribute10;
1223 G_Bom_Revisions_Tbl(l_counter).attribute11 := l_revision_details.attribute11;
1224 G_Bom_Revisions_Tbl(l_counter).attribute12 := l_revision_details.attribute12;
1225 G_Bom_Revisions_Tbl(l_counter).attribute13 := l_revision_details.attribute13;
1226 G_Bom_Revisions_Tbl(l_counter).attribute14 := l_revision_details.attribute14;
1227 G_Bom_Revisions_Tbl(l_counter).attribute15 := l_revision_details.attribute15;
1228 l_counter := l_counter + 1;
1229 END LOOP;
1230
1231 EXCEPTION
1232 WHEN invalid_bill_seq_id THEN
1233 FND_MESSAGE.Set_Name('BOM', 'BOM_INVALID_BILL_SEQ_ID');
1234 FND_MESSAGE.Set_Token('BILL_SEQUENCE_ID', P_bill_sequence_id);
1235 FND_MESSAGE.Set_Token('ASSEMBLY_ITEM_NAME', l_item_name);
1236 FND_MESSAGE.Set_Token('ORGANIZATION_CODE', l_org_code);
1237 RAISE invalid_bill_seq_id;
1238 END Populate_Header;
1239
1240 PROCEDURE Populate_Details(P_component_item_id IN NUMBER,
1241 P_bill_sequence_id IN NUMBER,
1242 P_component_sequence_id IN NUMBER,
1243 P_organization_id IN NUMBER) IS
1244 CURSOR Component_Details_CUR IS
1245 SELECT effectivity_date,
1246 disable_date,
1247 operation_seq_num,
1248 acd_type,
1249 item_num,
1250 basis_type,
1251 component_quantity,
1252 planning_factor,
1253 component_yield_factor,
1254 include_in_cost_rollup,
1255 wip_supply_type,
1256 so_basis,
1257 optional,
1258 mutually_exclusive_options,
1259 check_atp,
1260 shipping_allowed,
1261 required_to_ship,
1262 required_for_revenue,
1263 include_on_ship_docs,
1264 quantity_related,
1265 supply_subinventory,
1266 low_quantity,
1267 high_quantity,
1268 component_remarks,
1269 from_end_item_unit_number,
1270 to_end_item_unit_number,
1271 enforce_int_requirements,
1272 supply_locator_id,
1273 attribute_category,
1274 attribute1,
1275 attribute2,
1276 attribute3,
1277 attribute4,
1278 attribute5,
1279 attribute6,
1280 attribute7,
1281 attribute8,
1282 attribute9,
1283 attribute10,
1284 attribute11,
1285 attribute12,
1286 attribute13,
1287 attribute14,
1288 attribute15
1289 FROM bom_inventory_components
1290 WHERE bill_sequence_id = P_bill_sequence_id
1291 AND component_sequence_id = P_component_sequence_id
1292 AND component_item_id = P_component_item_id;
1293
1294 CURSOR Sub_Component_Details_CUR IS
1295 SELECT implementation_date,
1296 substitute_component_id,
1297 substitute_item_quantity,
1298 enforce_int_requirements,
1299 attribute_category,
1300 attribute1,
1301 attribute2,
1302 attribute3,
1303 attribute4,
1304 attribute5,
1305 attribute6,
1306 attribute7,
1307 attribute8,
1308 attribute9,
1309 attribute10,
1310 attribute11,
1311 attribute12,
1312 attribute13,
1313 attribute14,
1314 attribute15
1315 FROM bom_substitute_components_v
1316 WHERE component_sequence_id = P_component_sequence_id;
1317
1318 CURSOR Ref_Desig_Details_CUR IS
1319 SELECT component_reference_designator,
1320 implementation_date,
1321 ref_designator_comment,
1322 attribute_category,
1323 attribute1,
1324 attribute2,
1325 attribute3,
1326 attribute4,
1327 attribute5,
1328 attribute6,
1329 attribute7,
1330 attribute8,
1331 attribute9,
1332 attribute10,
1333 attribute11,
1334 attribute12,
1335 attribute13,
1336 attribute14,
1337 attribute15
1338 FROM bom_reference_designators_v
1339 WHERE component_sequence_id = P_component_sequence_id;
1340
1341 CURSOR Comp_Oper_Details_CUR IS
1342 SELECT operation_seq_num,
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_component_operations
1360 WHERE component_sequence_id = P_component_sequence_id;
1361 l_comp_details Component_Details_CUR%ROWTYPE;
1362 l_cnt NUMBER;
1363 j NUMBER;
1364 k NUMBER;
1365 l NUMBER;
1366 l_component_item_name MTL_SYSTEM_ITEMS_KFV.CONCATENATED_SEGMENTS%TYPE;
1367 l_locator_name MTL_ITEM_LOCATIONS_KFV.CONCATENATED_SEGMENTS%TYPE;
1368 BEGIN
1369 l_component_item_name := Get_Item_Name(P_component_item_id, P_organization_id);
1370
1371 OPEN Component_Details_CUR;
1372 FETCH Component_Details_CUR INTO l_comp_details;
1373 IF (Component_Details_CUR%NOTFOUND) THEN
1374 RAISE invalid_comp_seq_id;
1375 END IF;
1376 CLOSE Component_Details_CUR;
1377
1378 IF (l_comp_details.supply_locator_id IS NOT NULL) THEN
1379 l_locator_name := Get_Locator_Name(l_comp_details.supply_locator_id,
1380 P_organization_id);
1381 END IF;
1382
1383 l_cnt := G_Bom_Components_Tbl.LAST + 1;
1384 IF (l_cnt IS NULL) THEN
1385 l_cnt := 1;
1386 END IF;
1387
1388 j := G_Bom_Sub_Components_Tbl.LAST + 1;
1389 IF (j IS NULL) THEN
1390 j := 1;
1391 END IF;
1392
1393 k := G_Bom_Ref_Designators_Tbl.LAST + 1;
1394 IF (k IS NULL) THEN
1395 k := 1;
1396 END IF;
1397
1398 l := G_Bom_Comp_Ops_Tbl.LAST + 1;
1399 IF (l IS NULL) THEN
1400 l := 1;
1401 END IF;
1402
1403 FOR i IN G_Header_Record_Id_Tbl.FIRST..G_Header_Record_Id_Tbl.LAST LOOP
1404 IF (G_Header_Record_Id_Tbl(i).bill_sequence_id = P_bill_sequence_id) THEN
1405 G_Bom_Components_Tbl(l_cnt).organization_code := G_Bom_Header_Tbl(i).organization_code;
1406 G_Bom_Components_Tbl(l_cnt).assembly_item_name := G_Bom_Header_Tbl(i).assembly_item_name;
1407 G_Bom_Components_Tbl(l_cnt).start_effective_date := l_comp_details.effectivity_date;
1408 G_Bom_Components_Tbl(l_cnt).disable_date := l_comp_details.disable_date;
1409 G_Bom_Components_Tbl(l_cnt).operation_sequence_number := l_comp_details.operation_seq_num;
1410 G_Bom_Components_Tbl(l_cnt).component_item_name := l_component_item_name;
1411 G_Bom_Components_Tbl(l_cnt).alternate_bom_code := G_Bom_Header_Tbl(i).alternate_bom_code;
1412 G_Bom_Components_Tbl(l_cnt).item_sequence_number := l_comp_details.item_num;
1413 G_Bom_Components_Tbl(l_cnt).basis_type := l_comp_details.basis_type;
1414 G_Bom_Components_Tbl(l_cnt).quantity_per_assembly := l_comp_details.component_quantity;
1415 G_Bom_Components_Tbl(l_cnt).planning_percent := l_comp_details.planning_factor;
1416 G_Bom_Components_Tbl(l_cnt).projected_yield := l_comp_details.component_yield_factor;
1417 G_Bom_Components_Tbl(l_cnt).include_in_cost_rollup := l_comp_details.include_in_cost_rollup;
1418 G_Bom_Components_Tbl(l_cnt).wip_supply_type := l_comp_details.wip_supply_type;
1419 G_Bom_Components_Tbl(l_cnt).so_basis := l_comp_details.so_basis;
1420 G_Bom_Components_Tbl(l_cnt).optional := l_comp_details.optional;
1421 G_Bom_Components_Tbl(l_cnt).mutually_exclusive := l_comp_details.mutually_exclusive_options;
1422 G_Bom_Components_Tbl(l_cnt).check_atp := l_comp_details.check_atp;
1423 G_Bom_Components_Tbl(l_cnt).shipping_allowed := l_comp_details.shipping_allowed;
1424 G_Bom_Components_Tbl(l_cnt).required_to_ship := l_comp_details.required_to_ship;
1425 G_Bom_Components_Tbl(l_cnt).required_for_revenue := l_comp_details.required_for_revenue;
1426 G_Bom_Components_Tbl(l_cnt).include_on_ship_docs := l_comp_details.include_on_ship_docs;
1427 G_Bom_Components_Tbl(l_cnt).quantity_related := l_comp_details.quantity_related;
1428 G_Bom_Components_Tbl(l_cnt).supply_subinventory := l_comp_details.supply_subinventory;
1429 G_Bom_Components_Tbl(l_cnt).location_name := l_locator_name;
1430 G_Bom_Components_Tbl(l_cnt).minimum_allowed_quantity := l_comp_details.low_quantity;
1431 G_Bom_Components_Tbl(l_cnt).maximum_allowed_quantity := l_comp_details.high_quantity;
1432 G_Bom_Components_Tbl(l_cnt).comments := l_comp_details.component_remarks;
1433 G_Bom_Components_Tbl(l_cnt).from_end_item_unit_number := l_comp_details.from_end_item_unit_number;
1434 G_Bom_Components_Tbl(l_cnt).to_end_item_unit_number := l_comp_details.to_end_item_unit_number;
1435 G_Bom_Components_Tbl(l_cnt).enforce_int_requirements := l_comp_details.enforce_int_requirements;
1436 G_Bom_Components_Tbl(l_cnt).attribute_category := l_comp_details.attribute_category;
1437 G_Bom_Components_Tbl(l_cnt).attribute1 := l_comp_details.attribute1;
1438 G_Bom_Components_Tbl(l_cnt).attribute2 := l_comp_details.attribute2;
1439 G_Bom_Components_Tbl(l_cnt).attribute3 := l_comp_details.attribute3;
1440 G_Bom_Components_Tbl(l_cnt).attribute4 := l_comp_details.attribute4;
1441 G_Bom_Components_Tbl(l_cnt).attribute5 := l_comp_details.attribute5;
1442 G_Bom_Components_Tbl(l_cnt).attribute6 := l_comp_details.attribute6;
1443 G_Bom_Components_Tbl(l_cnt).attribute7 := l_comp_details.attribute7;
1444 G_Bom_Components_Tbl(l_cnt).attribute8 := l_comp_details.attribute8;
1445 G_Bom_Components_Tbl(l_cnt).attribute9 := l_comp_details.attribute9;
1446 G_Bom_Components_Tbl(l_cnt).attribute10 := l_comp_details.attribute10;
1447 G_Bom_Components_Tbl(l_cnt).attribute11 := l_comp_details.attribute11;
1448 G_Bom_Components_Tbl(l_cnt).attribute12 := l_comp_details.attribute12;
1449 G_Bom_Components_Tbl(l_cnt).attribute13 := l_comp_details.attribute13;
1450 G_Bom_Components_Tbl(l_cnt).attribute14 := l_comp_details.attribute14;
1451 G_Bom_Components_Tbl(l_cnt).attribute15 := l_comp_details.attribute15;
1452
1453 FOR Sub_Comp_Rec IN Sub_Component_Details_CUR LOOP
1454 G_Bom_Sub_Components_Tbl(j).organization_code := G_Bom_Header_Tbl(i).organization_code;
1455 G_Bom_Sub_Components_Tbl(j).assembly_item_name := G_Bom_Header_Tbl(i).assembly_item_name;
1456 G_Bom_Sub_Components_Tbl(j).start_effective_date := Sub_Comp_Rec.implementation_date;
1457 G_Bom_Sub_Components_Tbl(j).operation_sequence_number := l_comp_details.operation_seq_num;
1458 G_Bom_Sub_Components_Tbl(j).component_item_name := l_component_item_name;
1459 G_Bom_Sub_Components_Tbl(j).alternate_bom_code := G_Bom_Header_Tbl(i).alternate_bom_code;
1460 G_Bom_Sub_Components_Tbl(j).substitute_component_name := Get_Item_Name(Sub_Comp_Rec.substitute_component_id,
1461 P_organization_id);
1462 G_Bom_Sub_Components_Tbl(j).substitute_item_quantity := Sub_Comp_Rec.substitute_item_quantity;
1463 G_Bom_Sub_Components_Tbl(j).from_end_item_unit_number := l_comp_details.from_end_item_unit_number;
1464 G_Bom_Sub_Components_Tbl(j).enforce_int_requirements := Sub_Comp_Rec.enforce_int_requirements;
1465 G_Bom_Sub_Components_Tbl(j).attribute_category := Sub_Comp_Rec.attribute_category;
1466 G_Bom_Sub_Components_Tbl(j).attribute1 := Sub_Comp_Rec.attribute1;
1467 G_Bom_Sub_Components_Tbl(j).attribute2 := Sub_Comp_Rec.attribute2;
1468 G_Bom_Sub_Components_Tbl(j).attribute3 := Sub_Comp_Rec.attribute3;
1469 G_Bom_Sub_Components_Tbl(j).attribute4 := Sub_Comp_Rec.attribute4;
1470 G_Bom_Sub_Components_Tbl(j).attribute5 := Sub_Comp_Rec.attribute5;
1471 G_Bom_Sub_Components_Tbl(j).attribute6 := Sub_Comp_Rec.attribute6;
1472 G_Bom_Sub_Components_Tbl(j).attribute7 := Sub_Comp_Rec.attribute7;
1473 G_Bom_Sub_Components_Tbl(j).attribute8 := Sub_Comp_Rec.attribute8;
1474 G_Bom_Sub_Components_Tbl(j).attribute9 := Sub_Comp_Rec.attribute9;
1475 G_Bom_Sub_Components_Tbl(j).attribute10 := Sub_Comp_Rec.attribute10;
1476 G_Bom_Sub_Components_Tbl(j).attribute11 := Sub_Comp_Rec.attribute11;
1477 G_Bom_Sub_Components_Tbl(j).attribute12 := Sub_Comp_Rec.attribute12;
1478 G_Bom_Sub_Components_Tbl(j).attribute13 := Sub_Comp_Rec.attribute13;
1479 G_Bom_Sub_Components_Tbl(j).attribute14 := Sub_Comp_Rec.attribute14;
1480 G_Bom_Sub_Components_Tbl(j).attribute15 := Sub_Comp_Rec.attribute15;
1481 j := j + 1;
1482 END LOOP;
1483
1484 FOR Ref_Desg_Rec IN Ref_Desig_Details_CUR LOOP
1485 G_Bom_Ref_Designators_Tbl(k).organization_code := G_Bom_Header_Tbl(i).organization_code;
1486 G_Bom_Ref_Designators_Tbl(k).assembly_item_name := G_Bom_Header_Tbl(i).assembly_item_name;
1487 G_Bom_Ref_Designators_Tbl(k).start_effective_date := Ref_Desg_Rec.implementation_date;
1488 G_Bom_Ref_Designators_Tbl(k).operation_sequence_number := l_comp_details.operation_seq_num;
1489 G_Bom_Ref_Designators_Tbl(k).component_item_name := l_component_item_name;
1490 G_Bom_Ref_Designators_Tbl(k).alternate_bom_code := G_Bom_Header_Tbl(i).alternate_bom_code;
1491 G_Bom_Ref_Designators_Tbl(k).reference_designator_name := Ref_Desg_Rec.component_reference_designator;
1492 G_Bom_Ref_Designators_Tbl(k).ref_designator_comment := Ref_Desg_Rec.ref_designator_comment;
1493 G_Bom_Ref_Designators_Tbl(k).from_end_item_unit_number := l_comp_details.from_end_item_unit_number;
1494 G_Bom_Ref_Designators_Tbl(k).attribute_category := Ref_Desg_Rec.attribute_category;
1495 G_Bom_Ref_Designators_Tbl(k).attribute1 := Ref_Desg_Rec.attribute1;
1496 G_Bom_Ref_Designators_Tbl(k).attribute2 := Ref_Desg_Rec.attribute2;
1497 G_Bom_Ref_Designators_Tbl(k).attribute3 := Ref_Desg_Rec.attribute3;
1498 G_Bom_Ref_Designators_Tbl(k).attribute4 := Ref_Desg_Rec.attribute4;
1499 G_Bom_Ref_Designators_Tbl(k).attribute5 := Ref_Desg_Rec.attribute5;
1500 G_Bom_Ref_Designators_Tbl(k).attribute6 := Ref_Desg_Rec.attribute6;
1501 G_Bom_Ref_Designators_Tbl(k).attribute7 := Ref_Desg_Rec.attribute7;
1502 G_Bom_Ref_Designators_Tbl(k).attribute8 := Ref_Desg_Rec.attribute8;
1503 G_Bom_Ref_Designators_Tbl(k).attribute9 := Ref_Desg_Rec.attribute9;
1504 G_Bom_Ref_Designators_Tbl(k).attribute10 := Ref_Desg_Rec.attribute10;
1505 G_Bom_Ref_Designators_Tbl(k).attribute11 := Ref_Desg_Rec.attribute11;
1506 G_Bom_Ref_Designators_Tbl(k).attribute12 := Ref_Desg_Rec.attribute12;
1507 G_Bom_Ref_Designators_Tbl(k).attribute13 := Ref_Desg_Rec.attribute13;
1508 G_Bom_Ref_Designators_Tbl(k).attribute14 := Ref_Desg_Rec.attribute14;
1509 G_Bom_Ref_Designators_Tbl(k).attribute15 := Ref_Desg_Rec.attribute15;
1510 k := k + 1;
1511 END LOOP;
1512
1513 FOR Comp_Oper_Rec IN Comp_Oper_Details_CUR LOOP
1514 G_Bom_Comp_Ops_Tbl(l).organization_code := G_Bom_Header_Tbl(i).organization_code;
1515 G_Bom_Comp_Ops_Tbl(l).assembly_item_name := G_Bom_Header_Tbl(i).assembly_item_name;
1516 G_Bom_Comp_Ops_Tbl(l).start_effective_date := l_comp_details.effectivity_date;
1517 G_Bom_Comp_Ops_Tbl(l).from_end_item_unit_number := l_comp_details.from_end_item_unit_number;
1518 G_Bom_Comp_Ops_Tbl(l).to_end_item_unit_number := l_comp_details.to_end_item_unit_number;
1519 G_Bom_Comp_Ops_Tbl(l).operation_sequence_number := Comp_Oper_Rec.operation_seq_num;
1520 G_Bom_Comp_Ops_Tbl(l).component_item_name := l_component_item_name;
1521 G_Bom_Comp_Ops_Tbl(l).alternate_bom_code := G_Bom_Header_Tbl(i).alternate_bom_code;
1522 G_Bom_Comp_Ops_Tbl(l).attribute_category := Comp_Oper_Rec.attribute_category;
1523 G_Bom_Comp_Ops_Tbl(l).attribute1 := Comp_Oper_Rec.attribute1;
1524 G_Bom_Comp_Ops_Tbl(l).attribute2 := Comp_Oper_Rec.attribute2;
1525 G_Bom_Comp_Ops_Tbl(l).attribute3 := Comp_Oper_Rec.attribute3;
1526 G_Bom_Comp_Ops_Tbl(l).attribute4 := Comp_Oper_Rec.attribute4;
1527 G_Bom_Comp_Ops_Tbl(l).attribute5 := Comp_Oper_Rec.attribute5;
1528 G_Bom_Comp_Ops_Tbl(l).attribute6 := Comp_Oper_Rec.attribute6;
1529 G_Bom_Comp_Ops_Tbl(l).attribute7 := Comp_Oper_Rec.attribute7;
1530 G_Bom_Comp_Ops_Tbl(l).attribute8 := Comp_Oper_Rec.attribute8;
1531 G_Bom_Comp_Ops_Tbl(l).attribute9 := Comp_Oper_Rec.attribute9;
1532 G_Bom_Comp_Ops_Tbl(l).attribute10 := Comp_Oper_Rec.attribute10;
1533 G_Bom_Comp_Ops_Tbl(l).attribute11 := Comp_Oper_Rec.attribute11;
1534 G_Bom_Comp_Ops_Tbl(l).attribute12 := Comp_Oper_Rec.attribute12;
1535 G_Bom_Comp_Ops_Tbl(l).attribute13 := Comp_Oper_Rec.attribute13;
1536 G_Bom_Comp_Ops_Tbl(l).attribute14 := Comp_Oper_Rec.attribute14;
1537 G_Bom_Comp_Ops_Tbl(l).attribute15 := Comp_Oper_Rec.attribute15;
1538 l := l + 1;
1539 END LOOP;
1540 exit;
1541 END IF;
1542 END LOOP;
1543
1544 EXCEPTION
1545 WHEN invalid_comp_seq_id THEN
1546 FND_MESSAGE.Set_Name('BOM', 'BOM_INVALID_COMP_SEQ_ID');
1547 FND_MESSAGE.Set_Token('COMPONENT_ITEM_NAME', l_component_item_name);
1548 FND_MESSAGE.Set_Token('COMPONENT_SEQ_ID', P_component_sequence_id);
1549 RAISE invalid_comp_seq_id;
1550 WHEN invalid_locator_id THEN
1551 FND_MESSAGE.Set_Name('BOM', 'BOM_INVALID_LOCATOR_ID');
1552 FND_MESSAGE.Set_Token('LOCATOR_ID', l_comp_details.supply_locator_id);
1553 FND_MESSAGE.Set_Token('ORGANIZATION_ID', P_organization_id);
1554 FND_MESSAGE.Set_Token('COMPONENT_ITEM_NAME', l_component_item_name);
1555 RAISE invalid_locator_id;
1556 END Populate_Details;
1557
1558 --========================================================================
1559 -- PROCEDURE : Export_BOM
1560 -- PARAMETERS : Org_hierarchy_name IN VARCHAR2 Organization Hierarchy
1561 -- Name
1562 -- Assembly_item_name IN VARCHAR2 Assembly item name
1563 -- Organization_code IN VARCHAR2 Organization code
1564 -- Alternate_bm_designator IN VARCHAR2 Alternate bom designator
1565 -- Costs IN NUMBER Cost flag
1566 -- Cost_type_id IN NUMBER Cost type id
1567 -- X_bom_header_tbl OUT
1568 -- X_bom_revisions_tbl OUT
1569 -- X_bom_components_tbl OUT
1570 -- X_bom_ref_designators_tbl OUT
1571 -- X_bom_sub_components_tbl OUT
1572 -- X_bom_comp_ops_tbl OUT
1573 -- Err_Msg OUT VARCHAR2 Error Message
1574 -- Error_Code OUT NUMBER Error Megssage
1575 --
1576 -- COMMENT : API Accepts the name of an hierarchy, Assembly item name,
1577 -- Organization code, Alternate bom designator, Costs,
1578 -- Cost type id. It returns the following six pl/sql tables:
1579 -- 1. P_bom_header_tbl ,
1580 -- 2. p_bom_revisions_tbl,
1581 -- 3. p_bom_components_tbl,
1582 -- 4. p_bom_ref_designators_tbl,
1583 -- 5. p_bom_sub_components_tbl,
1584 -- 6. p_bom_comp_ops_tbl
1585 -- p_bom_header_tbl consists of all bom header records. p_bom_revisions_tbl
1586 -- consists of all revisions for an assembly item withina bom.
1587 -- p_bom_components_tbl consists of all components of a bom.
1588 -- p_bom_ref_designators_tbl consists of the reference designators for each
1589 -- of the components within a bom. p_bom_sub_components_tbl consits of
1590 -- substitute components for each of the components within a bom.
1591 -- p_bom_comp_ops_tbl consists of component operations for each of the
1592 -- components within a bom. Error Code and corresponding Error
1593 -- mesages are returned in case of an error
1594 --
1595 --
1596 --========================================================================
1597 PROCEDURE EXPORT_BOM ( P_org_hierarchy_name IN VARCHAR2 DEFAULT NULL,
1598 P_assembly_item_name IN VARCHAR2,
1599 P_organization_code IN VARCHAR2,
1600 P_alternate_bm_designator IN VARCHAR2 DEFAULT NULL,
1601 P_costs IN NUMBER DEFAULT 2,
1602 P_cost_type_id IN NUMBER DEFAULT 0,
1603 X_bom_header_tbl OUT NOCOPY BOM_BO_PUB.BOM_HEADER_TBL_TYPE,
1604 X_bom_revisions_tbl OUT NOCOPY BOM_BO_PUB.BOM_REVISION_TBL_TYPE,
1605 X_bom_components_tbl OUT NOCOPY BOM_BO_PUB.BOM_COMPS_TBL_TYPE,
1606 X_bom_ref_designators_tbl OUT NOCOPY BOM_BO_PUB.BOM_REF_DESIGNATOR_TBL_TYPE,
1607 X_bom_sub_components_tbl OUT NOCOPY BOM_BO_PUB.BOM_SUB_COMPONENT_TBL_TYPE,
1608 X_bom_comp_ops_tbl OUT NOCOPY BOM_BO_PUB.BOM_COMP_OPS_TBL_TYPE,
1609 X_Err_Msg OUT NOCOPY VARCHAR2,
1610 X_Error_Code OUT NOCOPY NUMBER
1611 ) IS
1612 l_organization_id NUMBER;
1613 l_assembly_item_id NUMBER;
1614 l_profile_id NUMBER := FND_PROFILE.value('PER_SECURITY_PROFILE_ID');
1615
1616 l_bom_export_tab BOMPXINQ.BOMEXPORTTABTYPE;
1617
1618 CURSOR organization_code_CUR IS
1619 SELECT organization_id
1620 FROM mtl_parameters
1621 WHERE organization_code = P_organization_code;
1622
1623 CURSOR assembly_item_name_CUR IS
1624 SELECT inventory_item_id
1625 FROM mtl_system_items
1626 WHERE segment1 = P_assembly_item_name
1627 AND organization_id = l_organization_id;
1628 l_err_text VARCHAR2(2000);
1629 l_err_msg varchar2(2000);
1630 l_err_Code number;
1631
1632 BEGIN
1633 G_Header_Record_id_Tbl.DELETE;
1634 G_bom_header_tbl.DELETE;
1635 G_bom_revisions_tbl.DELETE;
1636 G_bom_components_tbl.DELETE;
1637 G_bom_ref_designators_tbl.DELETE;
1638 G_bom_sub_components_tbl.DELETE;
1639 G_bom_comp_ops_tbl.DELETE;
1640
1641 IF (l_profile_id = null) THEN
1642 --dbms_output.put_line('No profile PER_SECURITY_PROFILE_ID . . . ');
1643 RAISE no_profile;
1644 END IF;
1645
1646
1647 --dbms_output.put_line('profile PER_SECURITY_PROFILE_ID . . . ' || l_profile_id);
1648
1649 IF ((P_assembly_item_name IS NULL OR
1650 P_assembly_item_name = FND_API.G_MISS_CHAR) OR
1651 (
1652 (P_organization_code IS NULL OR
1653 P_organization_code = FND_API.G_MISS_CHAR
1654 ) AND
1655 (P_org_hierarchy_name IS NULL OR
1656 P_org_hierarchy_name = FND_API.G_MISS_CHAR
1657 )
1658 )
1659 )
1660 THEN
1661 RAISE missing_parameters;
1662 END IF;
1663
1664 if (P_organization_code is not null)
1665 then
1666 l_organization_id := BOM_Val_To_Id.Organization(p_organization => P_organization_code,
1667 x_err_text => l_err_text);
1668 end if;
1669
1670 IF (l_organization_id IS NULL OR
1671 l_organization_id = FND_API.G_MISS_NUM)
1672 AND P_organization_code is not null
1673 THEN
1674 --dbms_output.put_line('Invalid Organization . . . ');
1675 RAISE invalid_org;
1676 END IF;
1677
1678 --dbms_output.put_line('Organization . . . ' || l_organization_id);
1679 /*
1680 OPEN organization_code_CUR;
1681
1682 FETCH organization_code_CUR INTO l_organization_id;
1683 IF (organization_code_CUR%NOTFOUND) THEN
1684 RAISE invalid_org;
1685 END IF;
1686
1687 CLOSE organization_code_CUR;
1688 */
1689
1690 OPEN assembly_item_name_CUR;
1691
1692 FETCH assembly_item_name_CUR INTO l_assembly_item_id;
1693 IF (assembly_item_name_CUR%NOTFOUND) THEN
1694 RAISE invalid_assembly_item_name;
1695 END IF;
1696 CLOSE assembly_item_name_CUR;
1697
1698 --dbms_output.put_line('Assembly item id: ' || l_assembly_item_id);
1699
1700
1701 /* Call the existing Export_BOM that returns a single pl/sql table containing information about
1702 all the entities of a BOM
1703 */
1704 EXPORT_BOM(Profile_Id => l_profile_id,
1705 Org_Hierarchy_Name => P_org_hierarchy_name,
1706 Assembly_Item_Id => l_assembly_item_id,
1707 Organization_Id => l_organization_id,
1708 Alternate_Bm_Designator => P_alternate_bm_designator,
1709 Costs => P_costs,
1710 Cost_Type_Id => P_cost_type_id,
1711 Bom_Export_Tab => l_bom_export_tab,
1712 Err_Msg => l_Err_Msg,
1713 Error_Code => l_Err_Code);
1714
1715 x_err_msg := l_err_msg;
1716 x_error_code := l_err_code;
1717
1718 --dbms_output.put_line('exported in a single table . . . ' || l_bom_export_tab.COUNT);
1719 --dbms_output.put_line('error msg. . . ' || l_Err_Msg);
1720 --dbms_output.put_line('error code. . . ' || l_err_code);
1721
1722 IF (X_Error_Code = 0 AND l_bom_export_tab.COUNT <> 0) THEN
1723 FOR i IN l_bom_export_tab.FIRST..l_bom_export_tab.LAST LOOP
1724 IF NOT Header_Id_Exists(P_assembly_item_id => l_bom_export_tab(i).assembly_item_id,
1725 P_bill_sequence_id => l_bom_export_tab(i).bill_sequence_id) THEN
1726 IF (l_bom_export_tab(i).assembly_item_id IS NOT NULL) THEN
1727 Populate_Header(l_bom_export_tab(i).assembly_item_id,
1728 l_bom_export_tab(i).bill_sequence_id,
1729 l_bom_export_tab(i).organization_id,
1730 p_Alternate_Bm_Designator);
1731 ELSE
1732 Populate_Header(l_bom_export_tab(i).component_item_id,
1733 l_bom_export_tab(i).bill_sequence_id,
1734 l_bom_export_tab(i).organization_id,
1735 p_Alternate_Bm_Designator);
1736 END IF;
1737 END IF;
1738 IF (l_bom_export_tab(i).assembly_item_id IS NOT NULL) THEN
1739 Populate_Details(l_bom_export_tab(i).component_item_id,
1740 l_bom_export_tab(i).bill_sequence_id,
1741 l_bom_export_tab(i).component_sequence_id,
1742 l_bom_export_tab(i).organization_id);
1743 END IF;
1744
1745 END LOOP;
1746 X_Bom_Header_Tbl := G_Bom_Header_Tbl;
1747 X_Bom_Revisions_Tbl := G_Bom_Revisions_Tbl;
1748 X_Bom_Components_Tbl := G_Bom_Components_Tbl;
1749 X_Bom_Ref_Designators_Tbl := G_Bom_Ref_Designators_Tbl;
1750 X_Bom_Sub_Components_Tbl := G_Bom_Sub_Components_Tbl;
1751 X_Bom_Comp_Ops_Tbl := G_Bom_Comp_Ops_Tbl;
1752 END IF; -- Error_Code = 0
1753
1754 EXCEPTION
1755 WHEN no_profile THEN
1756 rollback;
1757 X_error_code := -117;
1758 FND_MESSAGE.Set_Name('BOM', 'BOM_NO_PROFILE');
1759 X_Err_Msg := FND_MESSAGE.Get;
1760
1761 WHEN missing_parameters THEN
1762 X_error_code := -112;
1763 FND_MESSAGE.Set_Name('BOM', 'BOM_ASSY_OR_ORG_MISSING');
1764 X_err_Msg := FND_MESSAGE.Get;
1765
1766 WHEN invalid_org THEN
1767 rollback;
1768 X_error_code := -121;
1769 FND_MESSAGE.Set_Name('BOM', 'BOM_INVALID_ORGANIZATION');
1770 FND_MESSAGE.Set_Token('L_ORGANIZATION_ID', P_organization_code);
1771 X_Err_Msg := FND_MESSAGE.Get;
1772
1773 WHEN invalid_assembly_item_name THEN
1774 rollback;
1775 X_error_code := -116;
1776 FND_MESSAGE.Set_Name('BOM', 'BOM_INVALID_ASSEMBLY_ITEM');
1777 FND_MESSAGE.Set_Token('ASSEMBLY_ITEM', P_assembly_item_name);
1778 X_Err_Msg := FND_MESSAGE.Get;
1779
1780 WHEN invalid_comp_seq_id THEN
1781 rollback;
1782 X_bom_header_tbl.DELETE;
1783 X_bom_revisions_tbl.DELETE;
1784 X_bom_components_tbl.DELETE;
1785 X_bom_ref_designators_tbl.DELETE;
1786 X_bom_sub_components_tbl.DELETE;
1787 X_bom_comp_ops_tbl.DELETE;
1788 X_error_code := -115;
1789 X_Err_Msg := FND_MESSAGE.Get;
1790
1791 WHEN invalid_locator_id THEN
1792 rollback;
1793 X_bom_header_tbl.DELETE;
1794 X_bom_revisions_tbl.DELETE;
1795 X_bom_components_tbl.DELETE;
1796 X_bom_ref_designators_tbl.DELETE;
1797 X_bom_sub_components_tbl.DELETE;
1798 X_bom_comp_ops_tbl.DELETE;
1799 X_error_code := -114;
1800 X_Err_Msg := FND_MESSAGE.Get;
1801
1802 WHEN invalid_bill_seq_id THEN
1803 rollback;
1804 X_bom_header_tbl.DELETE;
1805 X_bom_revisions_tbl.DELETE;
1806 X_bom_components_tbl.DELETE;
1807 X_bom_ref_designators_tbl.DELETE;
1808 X_bom_sub_components_tbl.DELETE;
1809 X_bom_comp_ops_tbl.DELETE;
1810 X_error_code := -113;
1811 X_Err_Msg := FND_MESSAGE.Get;
1812 END EXPORT_BOM;
1813
1814 END BOMPXINQ;