[Home] [Help]
PACKAGE BODY: APPS.BOMPBEXP
Source
1 PACKAGE BODY BOMPBEXP AS
2 /* $Header: BOMBEXPB.pls 120.1 2005/06/21 02:47:45 appldev ship $ */
3 /*==========================================================================+
4 | Copyright (c) 1993 Oracle Corporation Belmont, California, USA |
5 | All rights reserved. |
6 +===========================================================================+
7 | |
8 | File Name : BOMBEXPB.pls |
9 | Description : This is the bom exploder. |
10 | Parameters: org_id organization_id |
11 | order_by 1 - Op seq, item seq |
12 | 2 - Item seq, op seq |
13 | grp_id unique value to identify current explosion |
14 | use value from sequence bom_explosion_temp_s|
15 | levels_to_explode |
16 | bom_or_eng 1 - BOM |
17 | 2 - ENG |
18 | impl_flag 1 - implemented only |
19 | 2 - both impl and unimpl |
20 | explode_option 1 - All |
21 | 2 - Current |
22 | 3 - Current and future |
23 | incl_oc_flag 1 - include OC and M under standard item |
24 | 2 - do not include |
25 | incl_lt_flag 1 - include operation lead time % |
26 | 2 - don't include operation lead time % |
27 | max_level max bom levels permissible for org |
28 | rev_date explosion date YYYY/MM/DD HH24:MI |
29 | err_msg error message out buffer |
30 | error_code error code out. returns sql error code |
31 | if sql error, 9999 if loop detected. |
32 | Revision |
33 | Shreyas Shah Creation |
34 | 02/10/94 Shreyas Shah added common_bill_Seq_id to cursor |
35 | added multi-org explosion |
36 | 10/19/95 Robert Yee select operation lead time percent from |
37 | routing |
38 | |
39 +==========================================================================*/
40
41 PROCEDURE bom_exploder(
42 verify_flag IN NUMBER DEFAULT 0,
43 online_flag IN NUMBER DEFAULT 1,
44 org_id IN NUMBER,
45 order_by IN NUMBER DEFAULT 1,
46 grp_id IN NUMBER,
47 levels_to_explode IN NUMBER DEFAULT 1,
48 bom_or_eng IN NUMBER DEFAULT 1,
49 impl_flag IN NUMBER DEFAULT 1,
50 plan_factor_flag IN NUMBER DEFAULT 2,
51 explode_option IN NUMBER DEFAULT 2,
52 std_comp_flag IN NUMBER DEFAULT 2,
53 incl_oc_flag IN NUMBER DEFAULT 1,
54 incl_lt_flag IN NUMBER DEFAULT 2,
55 max_level IN NUMBER,
56 module IN NUMBER DEFAULT 2,
57 rev_date IN VARCHAR2,
58 err_msg IN OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
59 error_code IN OUT NOCOPY /* file.sql.39 change */ NUMBER) AS
60
61 prev_sort_order VARCHAR2(4000);
62 prev_top_bill_id NUMBER;
63 cum_count NUMBER;
64 cur_level NUMBER;
65 total_rows NUMBER;
66 cat_sort VARCHAR2(7);
67 rev_date_s date;
68
69 /* verify local vars */
70 cur_component VARCHAR2(16);
71 cur_substr VARCHAR2(16);
72 cur_loopstr VARCHAR2(1000);
73 cur_loopflag VARCHAR2(1);
74 loop_found BOOLEAN := false;
75 some_loop_was_found BOOLEAN := false;
76 max_level_exceeded BOOLEAN := false;
77 start_pos NUMBER;
78
79 CURSOR exploder (
80 c_level NUMBER,
81 c_grp_id NUMBER,
82 c_bom_or_eng NUMBER,
83 c_rev_date date,
84 c_impl_flag NUMBER,
85 c_explode_option NUMBER,
86 c_order_by NUMBER,
87 c_verify_flag NUMBER,
88 c_plan_factor_flag NUMBER,
89 c_std_comp_flag NUMBER,
90 c_incl_oc NUMBER
91 ) IS
92 SELECT
93 BET.TOP_BILL_SEQUENCE_ID TBSI,
94 BOM.BILL_SEQUENCE_ID BSI,
95 BOM.COMMON_BILL_SEQUENCE_ID CBSI,
96 BIC.COMPONENT_ITEM_ID CID,
97 BIC.COMPONENT_SEQUENCE_ID CSI,
98 BIC.COMPONENT_QUANTITY CQ,
99 (BIC.COMPONENT_QUANTITY * BET.EXTENDED_QUANTITY *
100 decode(c_plan_factor_flag, 1, BIC.PLANNING_FACTOR/100, 1) /
101 decode(BIC.COMPONENT_YIELD_FACTOR, 0, 1,
102 BIC.COMPONENT_YIELD_FACTOR)) EQ,
103 BET.SORT_ORDER SO,
104 BET.TOP_ITEM_ID TID,
105 BET.TOP_ALTERNATE_DESIGNATOR TAD,
106 BOM.ALTERNATE_BOM_DESIGNATOR, -- for routing
107 BIC.COMPONENT_YIELD_FACTOR CYF,
108 BOM.ORGANIZATION_ID OI,
109 decode(verify_flag, 1, BET.COMPONENT_CODE,
110 BET.COMPONENT_CODE || '-' || BIC.COMPONENT_ITEM_ID) CC,
111 BIC.INCLUDE_IN_COST_ROLLUP IICR,
112 BET.LOOP_FLAG LF,
113 BIC.PLANNING_FACTOR PF, BIC.OPERATION_SEQ_NUM OSN,
114 BIC.BOM_ITEM_TYPE BIT, BET.BOM_ITEM_TYPE PBIT,
115 BET.COMPONENT_ITEM_ID PAID, BIC.WIP_SUPPLY_TYPE WST,
116 BIC.ITEM_NUM ITN,
117 BIC.EFFECTIVITY_DATE ED,
118 BIC.DISABLE_DATE DD,
119 BIC.IMPLEMENTATION_DATE ID,
120 BIC.OPTIONAL OPT,
121 BIC.SUPPLY_SUBINVENTORY SS,
122 BIC.SUPPLY_LOCATOR_ID SLI,
123 BIC.COMPONENT_REMARKS CR,
124 BIC.CHANGE_NOTICE CN,
125 BIC.OPERATION_LEAD_TIME_PERCENT OLTP,
126 BIC.MUTUALLY_EXCLUSIVE_OPTIONS MEO,
127 BIC.CHECK_ATP CATP,
128 BIC.REQUIRED_TO_SHIP RTS,
129 BIC.REQUIRED_FOR_REVENUE RFR,
130 BIC.INCLUDE_ON_SHIP_DOCS IOSD,
131 BIC.LOW_QUANTITY LQ,
132 BIC.HIGH_QUANTITY HQ,
133 BIC.SO_BASIS SB
134 FROM BOM_EXPLOSION_TEMP BET,
135 BOM_BILL_OF_MATERIALS BOM,
136 BOM_INVENTORY_COMPONENTS BIC,
137 MTL_SYSTEM_ITEMS MSI
138 WHERE BET.PLAN_LEVEL = c_level - 1
139 AND BET.GROUP_ID = c_grp_id
140 AND MSI.INVENTORY_ITEM_ID = BOM.ASSEMBLY_ITEM_ID
141 AND MSI.ORGANIZATION_ID = BOM.ORGANIZATION_ID
142 AND BOM.COMMON_BILL_SEQUENCE_ID = BIC.BILL_SEQUENCE_ID
143 AND BET.COMPONENT_ITEM_ID = BOM.ASSEMBLY_ITEM_ID
144 AND BOM.ORGANIZATION_ID = BET.ORGANIZATION_ID
145 AND NVL(BIC.ECO_FOR_PRODUCTION,2) = 2
146 AND ( (c_std_comp_flag = 1 /* only std components */
147 AND MSI.PICK_COMPONENTS_FLAG = 'Y'
148 AND BIC.BOM_ITEM_TYPE = 4 AND BIC.OPTIONAL = 2
149 )
150 OR
151 (c_std_comp_flag = 2)
152 OR
153 (c_std_comp_flag = 3 AND nvl(BET.BOM_ITEM_TYPE, 1) IN (1,2)
154 AND (BIC.BOM_ITEM_TYPE IN (1,2)
155 OR
156 (BIC.BOM_ITEM_TYPE = 4 AND BIC.OPTIONAL = 1)
157 )
158 )
159 )
160 AND ( (c_bom_or_eng = 1 and BOM.ASSEMBLY_TYPE = 1)
161 OR
162 (c_bom_or_eng = 2)
163 )
164 AND (
165 (BET.TOP_ALTERNATE_DESIGNATOR IS NULL
166 AND
167 BOM.ALTERNATE_BOM_DESIGNATOR IS NULL
168 )
169 OR
170 (BET.TOP_ALTERNATE_DESIGNATOR IS NOT NULL
171 AND
172 BOM.ALTERNATE_BOM_DESIGNATOR=BET.TOP_ALTERNATE_DESIGNATOR
173 )
174 OR
175 ( BET.TOP_ALTERNATE_DESIGNATOR IS NOT NULL
176 AND BOM.ALTERNATE_BOM_DESIGNATOR IS NULL
177 AND NOT EXISTS
178 (SELECT 'X'
179 FROM BOM_BILL_OF_MATERIALS BOM2
180 WHERE BOM2.ORGANIZATION_ID = BET.ORGANIZATION_ID
181 AND BOM2.ASSEMBLY_ITEM_ID = BET.COMPONENT_ITEM_ID
182 AND BOM2.ALTERNATE_BOM_DESIGNATOR =
183 BET.TOP_ALTERNATE_DESIGNATOR
184 AND ((c_bom_or_eng = 1 and BOM2.ASSEMBLY_TYPE = 1)
185 OR c_bom_or_eng = 2
186 )
187 ) /* subquery */
188 )
189 ) /* end of alt logic */
190 /* whether to include option classes and models under a standard item
191 ** special logic added at CST request */
192 AND ( (c_incl_oc = 1)
193 or
194 (c_incl_oc = 2 AND
195 ( BET.BOM_ITEM_TYPE = 4 AND BIC.BOM_ITEM_TYPE = 4)
196 OR
197 ( BET.BOM_ITEM_TYPE <> 4)
198 )
199 )
200 /* do not explode if immediate parent is standard and current
201 component is option class or model - special logic for config items */
202 AND NOT ( BET.PARENT_BOM_ITEM_TYPE = 4
203 AND
204 BET.BOM_ITEM_TYPE IN (1, 2)
205 )
206 AND ( (c_explode_option = 1 /* ALL */ )
207 OR
208 (c_explode_option = 2 /* CURRENT */ AND
209 c_rev_date >=
210 BIC.EFFECTIVITY_DATE AND
211 c_rev_date <
212 nvl(BIC.DISABLE_DATE,
213 c_rev_date+1)
214 ) /* CURRENT */
215 OR
216 (c_explode_option = 3 /* CURRENT AND FUTURE */ AND
217 nvl(BIC.DISABLE_DATE,
218 c_rev_date + 1) >
219 c_rev_date
220 ) /* CURRENT AND FUTURE */
221 )
222 AND ( (c_impl_flag = 2 AND
223 ( c_explode_option = 1
224 OR
225 (c_explode_option = 2 AND
226 BIC.EFFECTIVITY_DATE =
227 (SELECT MAX(EFFECTIVITY_DATE)
228 FROM BOM_INVENTORY_COMPONENTS CIB
229 WHERE CIB.BILL_SEQUENCE_ID = BIC.BILL_SEQUENCE_ID
230 AND CIB.COMPONENT_ITEM_ID = BIC.COMPONENT_ITEM_ID
231 AND NVL(CIB.ECO_FOR_PRODUCTION,2) = 2
232 AND ( decode(CIB.IMPLEMENTATION_DATE, NULL,
233 CIB.OLD_COMPONENT_SEQUENCE_ID,
237 BIC.COMPONENT_SEQUENCE_ID)
234 CIB.COMPONENT_SEQUENCE_ID) =
235 decode(BIC.IMPLEMENTATION_DATE, NULL,
236 BIC.OLD_COMPONENT_SEQUENCE_ID,
238 OR
239 CIB.OPERATION_SEQ_NUM = BIC.OPERATION_SEQ_NUM
240 ) /* decode */
241 AND CIB.EFFECTIVITY_DATE <=
242 c_rev_date
243 ) /* end of subquery */
244 ) /* CURRENT */
245 OR
246 (c_explode_option = 3 AND
247 BIC.EFFECTIVITY_DATE =
248 (SELECT MAX(EFFECTIVITY_DATE)
249 FROM BOM_INVENTORY_COMPONENTS CIB
250 WHERE CIB.BILL_SEQUENCE_ID = BIC.BILL_SEQUENCE_ID
251 AND CIB.COMPONENT_ITEM_ID = BIC.COMPONENT_ITEM_ID
252 AND NVL(CIB.ECO_FOR_PRODUCTION,2) = 2
253 AND ( decode(CIB.IMPLEMENTATION_DATE, NULL,
254 CIB.OLD_COMPONENT_SEQUENCE_ID,
255 CIB.COMPONENT_SEQUENCE_ID) =
256 decode(BIC.IMPLEMENTATION_DATE, NULL,
257 BIC.OLD_COMPONENT_SEQUENCE_ID,
258 BIC.COMPONENT_SEQUENCE_ID)
259 OR
260 CIB.OPERATION_SEQ_NUM = BIC.OPERATION_SEQ_NUM
261 ) /* decode */
262 AND CIB.EFFECTIVITY_DATE <=
263 c_rev_date
264 ) /* end of subquery */
265 OR BIC.EFFECTIVITY_DATE >
266 c_rev_date
267 ) /* CURRENT AND FUTURE */
268 ) /* explode_option */
269 ) /* impl_flag = 2 */
270 OR
271 (c_impl_flag = 1 AND BIC.IMPLEMENTATION_DATE IS NOT NULL)
272 ) /* explode option */
273 AND ( ( c_verify_flag = 1 AND BET.LOOP_FLAG = 2 ) OR
274 c_verify_flag <> 1 )
275 ORDER BY BET.TOP_BILL_SEQUENCE_ID, BET.SORT_ORDER,
276 decode(c_order_by, 1, BIC.OPERATION_SEQ_NUM, BIC.ITEM_NUM),
277 decode(c_order_by, 1, BIC.ITEM_NUM, BIC.OPERATION_SEQ_NUM);
278
279 Cursor Get_OLTP (P_Assembly in number,
280 P_Org_Id in number,
281 P_Alternate in varchar2,
282 P_Operation in number) is
283 Select round(bos.operation_lead_time_percent, 2) oltp
284 From Bom_Operation_Sequences bos,
285 Bom_Operational_Routings bor
286 Where bor.assembly_item_id = P_Assembly
287 And bor.organization_Id = P_Org_Id
288 And (bor.alternate_routing_designator = P_Alternate
289 or
290 (bor.alternate_routing_designator is null and not exists (
291 select null
292 from bom_operational_routings bor2
293 where bor2.assembly_item_id = P_Assembly
294 and bor2.organization_id = P_Org_Id
295 and bor2.alternate_routing_designator = P_Alternate)
296 ))
297 And bor.common_routing_sequence_id = bos.routing_sequence_id
298 And bos.operation_seq_num = P_Operation
299 And NVL(bos.eco_for_production,2) = 2
300 And trunc(bos.effectivity_date) <=
301 trunc(rev_date_s)
302 And nvl(bos.disable_date,
303 rev_date_s+1) >=
304 trunc(rev_date_s);
305
306
307 BEGIN
308
309 rev_date_s := to_date(rev_date || ':59', 'YYYY/MM/DD HH24:MI:SS');
310
311 for cur_level in 1..levels_to_explode loop
312
313 total_rows := 0;
314 cum_count := 0;
315
316 for expl_row in exploder (
317 cur_level,
318 grp_id,
319 bom_or_eng,
320 rev_date_s,
321 impl_flag,
322 explode_option,
323 order_by,
324 verify_flag,
325 plan_factor_flag,
326 std_comp_flag,
327 incl_oc_flag
328 ) loop
329
330 total_rows := total_rows + 1;
331 /*
332 ** for very first iteration of the loop, set prevbillid = bill_id
333 */
334 if (cum_count = 0) then
335 prev_top_bill_id := expl_row.TBSI;
336 prev_sort_order := expl_row.SO;
337 end if;
338 /*
339 ** whenever a diff assy at a particular level is being exploded, reset
340 ** the cum_count so that the sort code always starts from 001 for each
341 ** assembly
342 */
343 if ( prev_top_bill_id <> expl_row.TBSI or
344 (prev_top_bill_id = expl_row.TBSI and
345 prev_sort_order <> expl_row.SO)) then
346 cum_count := 0;
347 prev_top_bill_id := expl_row.TBSI;
348 prev_sort_order := expl_row.SO;
349 end if;
350
351 cum_count := cum_count + 1;
352 /*
353 ** lpad cat_sort with 0s upto 7 characters
354 */
355 cat_sort := lpad(to_char(cum_count), G_SortWidth, '0');
356
357 expl_row.SO := expl_row.SO || cat_sort;
358
359 if (verify_flag = 1) then
360
361 /* SQL has been modified to carry the loopstr and loopflag */
362 loop_found := FALSE;
363 cur_loopstr := expl_row.CC;
364 cur_component := LPAD( TO_CHAR( expl_row.CID ), 16, '0' );
365
366 /* search the current loop_string for current component */
367 FOR i IN 1..max_level LOOP
368 start_pos := 1+( (i-1) * 16 );
369 cur_substr := SUBSTR( cur_loopstr, start_pos, 16 );
370
371 if (cur_component = cur_substr) then
372 loop_found := TRUE;
373 EXIT;
377 /* deal with the search results */
374 end if;
375 END LOOP;
376
378 if loop_found then
379 expl_row.CC :=
380 expl_row.CC || cur_component;
381 expl_row.LF := 1;
382 some_loop_was_found := TRUE;
383 loop_found := FALSE;
384 else
385 expl_row.CC :=
386 expl_row.CC || cur_component;
387 expl_row.LF := 2;
388 end if;
389
390 end if;
391
392 Expl_Row.OLTP := Null;
393 If incl_lt_flag = 1 then
394 For X_Operation in Get_OLTP(
395 P_Assembly => Expl_Row.PAID,
396 P_Org_Id => Expl_Row.OI,
397 P_Alternate => Expl_Row.alternate_bom_designator,
398 P_Operation => Expl_Row.OSN) loop
399
400 Expl_Row.OLTP := X_Operation.OLTP;
401
402 End loop;
403 End if;
404
405 INSERT INTO BOM_EXPLOSION_TEMP (
406 TOP_BILL_SEQUENCE_ID,
407 BILL_SEQUENCE_ID,
408 COMMON_BILL_SEQUENCE_ID,
409 ORGANIZATION_ID,
410 COMPONENT_SEQUENCE_ID,
411 COMPONENT_ITEM_ID,
412 COMPONENT_QUANTITY,
413 PLAN_LEVEL,
414 EXTENDED_QUANTITY,
415 SORT_ORDER,
416 GROUP_ID,
417 TOP_ALTERNATE_DESIGNATOR,
418 COMPONENT_YIELD_FACTOR,
419 TOP_ITEM_ID,
420 COMPONENT_CODE,
421 INCLUDE_IN_ROLLUP_FLAG,
422 LOOP_FLAG,
423 PLANNING_FACTOR,
424 OPERATION_SEQ_NUM,
425 BOM_ITEM_TYPE,
426 PARENT_BOM_ITEM_TYPE,
427 ASSEMBLY_ITEM_ID,
428 WIP_SUPPLY_TYPE,
429 ITEM_NUM,
430 EFFECTIVITY_DATE,
431 DISABLE_DATE,
432 IMPLEMENTATION_DATE,
433 OPTIONAL,
434 SUPPLY_SUBINVENTORY,
435 SUPPLY_LOCATOR_ID,
436 COMPONENT_REMARKS,
437 CHANGE_NOTICE,
438 OPERATION_LEAD_TIME_PERCENT,
439 MUTUALLY_EXCLUSIVE_OPTIONS,
440 CHECK_ATP,
441 REQUIRED_TO_SHIP,
442 REQUIRED_FOR_REVENUE,
443 INCLUDE_ON_SHIP_DOCS,
444 LOW_QUANTITY,
445 HIGH_QUANTITY,
446 SO_BASIS
447 ) VALUES (
448 expl_row.TBSI,
449 expl_row.BSI,
450 expl_row.CBSI,
451 expl_row.OI,
452 expl_row.CSI,
453 expl_row.CID,
454 expl_row.CQ,
455 cur_level,
456 expl_row.EQ,
457 expl_row.SO,
458 grp_id,
459 expl_row.TAD,
460 expl_row.CYF,
461 expl_row.TID,
462 expl_row.CC,
463 expl_row.IICR,
464 expl_row.LF,
465 expl_row.PF,
466 expl_row.OSN,
467 expl_row.BIT,
468 expl_row.PBIT,
469 expl_row.PAID,
470 expl_row.WST,
471 expl_row.ITN,
472 expl_row.ED,
473 expl_row.DD,
474 expl_row.ID,
475 expl_row.OPT,
476 expl_row.SS,
477 expl_row.SLI,
478 expl_row.CR,
479 expl_row.CN,
480 expl_row.OLTP,
481 expl_row.MEO,
482 expl_row.CATP,
483 expl_row.RTS,
484 expl_row.RFR,
485 expl_row.IOSD,
486 expl_row.LQ,
487 expl_row.HQ,
488 expl_row.SB
489 );
490
491 if( (verify_flag=1) and some_loop_was_found and (online_flag = 1) )
492 then EXIT;
493 end if;
494
495 end loop; /* cursor fetch loop */
496
497 /*
498 ** if total rows fetched is 0, then break the loop here since nothing
499 ** more to explode
500 */
501
502 if (module = 1) then /* intermittent commits for CST */
503 commit;
504 end if;
505
506 if (total_rows <> 0) then
507 if cur_level = max_level then
508 max_level_exceeded := true;
509 end if;
510 else
511 Exit;
512 end if;
513
514 if( (verify_flag=1) and some_loop_was_found and (online_flag = 1))
515 then EXIT;
516 end if;
517
518 END LOOP; /* for each level */
519
520 -- done_exploding
521
522
523 if some_loop_was_found then
524 err_msg := 'BOM_LOOP_EXISTS';
525 error_code := 9999;
526 elsif max_level_exceeded then
527 err_msg := 'BOM_MAX_LEVELS';
528 error_code := 9998;
529 else
530 err_msg := null;
531 error_code := 0;
532 end if;
533
534 /*
535 ** exception handlers
536 */
537 EXCEPTION
538 WHEN OTHERS THEN
539 error_code := SQLCODE;
540 err_msg := 'BOMPBEXP:' || substrb(SQLERRM,1,60);
541 ROLLBACK;
542 END bom_exploder;
543
544 END BOMPBEXP;