[Home] [Help]
PACKAGE BODY: APPS.BOM_IMPLODER_PUB
Source
4 /*==========================================================================+
1 package body bom_imploder_pub as
2 /* $Header: BOMPIMPB.pls 120.13.12010000.5 2010/02/04 07:34:11 maychen ship $ */
3
5 | Copyright (c) 1993 Oracle Corporation Belmont, California, USA |
6 | All rights reserved. |
7 +===========================================================================+
8 | |
9 | File Name : bom_imploder_pub.sql |
10 | DESCRIPTION : This file is a packaged procedure for the imploders.
11 | This package contains 2 different imploders for the
12 | single level and multi level implosion. The package
13 | imploders calls the correct imploder based on the
14 | # of levels to implode.
15 | Parameters: org_id organization_id
16 | sequence_id unique value to identify current implosion
17 | use value from sequence bom_small_impl_temp_s
18 | levels_to_implode
19 | eng_mfg_flag 1 - BOM
20 | 2 - ENG
21 | impl_flag 1 - implemented only
22 | 2 - both impl and unimpl
23 | display_option 1 - All
24 | 2 - Current
25 | 3 - Current and future
26 | item_id item id of asembly to explode
27 | impl_date explosion date dd-mon-rr hh24:mi
28 | err_msg error message out buffer
29 | error_code error code out. returns sql error code
30 | if sql error, -9999 if loop detected.
31 | organization_option
32 | 1 - Current Organization
33 | 2 - Organization Hierarchy
34 | 3 - All Organizations to which access is allowed
35 | organization_hierarchy
36 | Organization Hierarchy Name
37 |
38 | HISTORY
39 | 06-JUL-05 Bhavnesh Patel Changes for Revision Effective Structure
40 | 27-SEP-05 Bhavnesh Patel Changes for Exclusion Rules
41 | 09-NOV-05 Bhavnesh Patel Added Revision Filter
42 | 07-FEB-06 Bhavnesh Patel Changes for improving performance
43 +==========================================================================*/
44 --Constants
45 G_CAD_OBJ_NAME VARCHAR2(30) := 'DDD_CADVIEW';
46 G_EGO_OBJ_NAME VARCHAR2(30) := 'EGO_ITEM';
47
48 /*Function added for BUG fix 3377394 */
49 FUNCTION CALCULATE_COMP_COUNT
50 ( PK_VALUE1 IN VARCHAR2,
51 PK_VALUE2 IN VARCHAR2,
52 IMPL_DATE IN VARCHAR2)
53
54 RETURN NUMBER
55 IS
56 COUNTER NUMBER;
57 BEGIN
58 SELECT COUNT(COMPONENT_SEQUENCE_ID) INTO COUNTER
59 FROM bom_components_b
60 WHERE pk1_value = PK_VALUE1
61 AND pk2_value = PK_VALUE2
62 AND to_date(impl_date, 'YYYY/MM/DD HH24:MI:SS')
63 between effectivity_date
64 and nvl(disable_date, sysdate) ;
65 /*bug: 3807198, taking care of disabled components*/
66 RETURN COUNTER;
67 END CALCULATE_COMP_COUNT;
68
69 FUNCTION Check_User_View_priv(Itemid VARCHAR2, OrgId VARCHAR2)
70 RETURN Varchar2 IS
71 l_access_flag varchar2(10);
72 BEGIN
73 Select BOM_SECURITY_PUB.CHECK_USER_PRIVILEGE( 1,
74 'EGO_VIEW_ITEM',
75 'EGO_ITEM',
76 ItemId,
77 OrgId,
78 null,
79 null,
80 null,
81 BOM_SECURITY_PUB.Get_EGO_User
82 ) into l_access_flag
83 from dual;
84 Return l_access_flag;
85 End Check_User_View_priv;
86
87 /* Procedure imploder_userexit for PLM support
88 This procedure will call overloaded procedure with null revision
89 */
90 PROCEDURE imploder_userexit(
91 sequence_id IN NUMBER,
92 eng_mfg_flag IN NUMBER,
93 org_id IN NUMBER,
94 impl_flag IN NUMBER,
95 display_option IN NUMBER,
96 levels_to_implode IN NUMBER,
97 obj_name IN VARCHAR2 DEFAULT 'EGO_ITEM',
98 pk1_value IN VARCHAR2,
99 pk2_value IN VARCHAR2,
100 pk3_value IN VARCHAR2,
101 pk4_value IN VARCHAR2,
102 pk5_value IN VARCHAR2,
103 impl_date IN VARCHAR2,
104 unit_number_from IN VARCHAR2,
105 unit_number_to IN VARCHAR2,
106 err_msg OUT NOCOPY VARCHAR2,
107 err_code OUT NOCOPY NUMBER,
111 serial_number_to IN VARCHAR2 default NULL,
108 organization_option IN NUMBER default 1,
109 organization_hierarchy IN VARCHAR2 default NULL,
110 serial_number_from IN VARCHAR2 default NULL,
112 struct_name IN VARCHAR2 DEFAULT FND_LOAD_UTIL.NULL_VALUE
113 )
114 AS
115 BEGIN
116 imploder_userexit(
117 sequence_id => sequence_id,
118 eng_mfg_flag => eng_mfg_flag,
119 org_id => org_id,
120 impl_flag => impl_flag,
121 display_option => display_option,
122 levels_to_implode => levels_to_implode,
123 obj_name => obj_name,
124 pk1_value => pk1_value,
125 pk2_value => pk2_value,
126 pk3_value => pk3_value,
127 pk4_value => pk4_value,
128 pk5_value => pk5_value,
129 impl_date => impl_date,
130 unit_number_from => unit_number_from,
131 unit_number_to => unit_number_to,
132 err_msg => err_msg,
133 err_code => err_code,
134 organization_option => organization_option,
135 organization_hierarchy => organization_hierarchy,
136 serial_number_from => serial_number_from,
137 serial_number_to => serial_number_to,
138 struct_name => struct_name,
139 revision => NULL
140 );
141 END imploder_userexit;
142
143 PROCEDURE implosion_cad(
144 sequence_id IN NUMBER,
145 eng_mfg_flag IN NUMBER,
146 org_id IN NUMBER,
147 impl_flag IN NUMBER,
148 display_option IN NUMBER,
149 levels_to_implode IN NUMBER,
150 impl_date IN VARCHAR2,
151 unit_number_from IN VARCHAR2,
152 unit_number_to IN VARCHAR2,
153 err_msg OUT NOCOPY VARCHAR2,
154 err_code OUT NOCOPY NUMBER,
155 serial_number_from IN VARCHAR2 default NULL,
156 serial_number_to IN VARCHAR2 default NULL ,
157 struct_name IN VARCHAR2 DEFAULT FND_LOAD_UTIL.NULL_VALUE,
158 revision IN VARCHAR2) AS
159
160 implosion_date VARCHAR2(25);
161 error_msg VARCHAR(2000);--bug:4204847 Increasing the length so that component path can be
162 --returned in case of loop
163 error_code NUMBER;
164
165 BEGIN
166 implosion_date := substr(impl_date, 1, 19);
167
168 ml_imploder_cad(sequence_id, eng_mfg_flag, org_id, impl_flag,
169 levels_to_implode, implosion_date, unit_number_from,
170 unit_number_to, error_msg, error_code,
171 serial_number_from, serial_number_to,struct_name, revision);
172
173 err_msg := error_msg;
174 err_code := error_code;
175
176 if (error_code <> 0) then
177 ROLLBACK;
178 end if;
179
180 EXCEPTION
181 WHEN OTHERS THEN
182 err_msg := error_msg;
183 err_code := error_code;
184 ROLLBACK;
185 END implosion_cad;
186
187 PROCEDURE ml_imploder_cad(
188 sequence_id IN NUMBER,
189 eng_mfg_flag IN NUMBER,
190 org_id IN NUMBER,
191 impl_flag IN NUMBER,
192 a_levels_to_implode IN NUMBER,
193 impl_date IN VARCHAR2,
194 unit_number_from IN VARCHAR2,
195 unit_number_to IN VARCHAR2,
196 err_msg OUT NOCOPY VARCHAR2,
197 error_code OUT NOCOPY NUMBER,
198 serial_number_from IN VARCHAR2,
199 serial_number_to IN VARCHAR2,
200 struct_name IN VARCHAR2 DEFAULT FND_LOAD_UTIL.NULL_VALUE,
201 revision IN VARCHAR2) AS
202
203 prev_parent_item_id NUMBER;
204 cum_count NUMBER;
205 cur_level NUMBER;
206 total_rows NUMBER;
207 levels_to_implode NUMBER;
208 max_level NUMBER;
209 cat_sort VARCHAR2(7);
210 max_extents EXCEPTION;
211
212
213 /*
214 ** max extents exceeded exception
215 */
216 PRAGMA EXCEPTION_INIT(max_extents, -1631);
217
218 CURSOR imploder (c_current_level NUMBER, c_sequence_id NUMBER,
219 c_eng_mfg_flag NUMBER, c_org_id NUMBER,
220 c_implosion_date VARCHAR2, c_unit_number_from VARCHAR2,
221 c_unit_number_to VARCHAR2,c_serial_number_from VARCHAR2,
222 c_serial_number_to VARCHAR2, c_implemented_only_option NUMBER,
223 c_levels_to_implode NUMBER
224 ) IS
225 SELECT --/*+ ordered first_rows */
226 BITT.LOWEST_pk1_value LID1,
227 BITT.LOWEST_pk2_value LID2,
228 BITT.LOWEST_pk3_value LID3,
229 BITT.LOWEST_pk4_value LID4,
230 BITT.LOWEST_pk5_value LID5,
231 BITT.LOWEST_obj_name LON,
232 BITT.PARENT_pk1_value PID1,
233 BITT.PARENT_pk2_value PID2,
234 BITT.PARENT_pk3_value PID3,
235 BITT.PARENT_pk4_value PID4,
236 BITT.PARENT_pk5_value PID5,
237 BITT.PARENT_obj_name PON,
238 BBM.PK1_VALUE AID1,
239 BBM.PK2_VALUE AID2,
240 BBM.PK3_VALUE AID3,
241 BBM.PK4_VALUE AID4,
242 BBM.PK5_VALUE AID5,
243 nvl(BBM.OBJ_NAME,G_EGO_OBJ_NAME) AON,
244 BBM.ALTERNATE_BOM_DESIGNATOR ABD,
245 BITT.SORT_CODE SC,
246 BITT.LOWEST_ALTERNATE_DESIGNATOR LAD,
247 BBM.ASSEMBLY_TYPE CAT,
248 BIC.COMPONENT_SEQUENCE_ID CSI,
249 BIC.OPERATION_SEQ_NUM OSN,
250 BIC.EFFECTIVITY_DATE ED,
251 BIC.DISABLE_DATE DD,
255 BIC.CHANGE_NOTICE CN,
252 BIC.BASIS_TYPE BT,
253 BIC.COMPONENT_QUANTITY CQ,
254 BIC.REVISED_ITEM_SEQUENCE_ID RISD,
256 DECODE(BIC.IMPLEMENTATION_DATE, NULL, 2, 1) IMPF,
257 BBM.ORGANIZATION_ID OI,
258 BIC.FROM_END_ITEM_UNIT_NUMBER FUN,
259 BIC.TO_END_ITEM_UNIT_NUMBER TUN,
260 BBM.STRUCTURE_TYPE_ID,
261 BITT.COMPONENT_PATH COMPONENT_PATH,
262 BIC.COMPONENT_ITEM_REVISION_ID COMPONENT_ITEM_REVISION_ID ,
263 DECODE( BIC.FROM_END_ITEM_REV_ID
264 ,NULL ,NULL
265 ,(
266 SELECT mirb.REVISION
267 FROM MTL_ITEM_REVISIONS_B mirb
268 WHERE mirb.REVISION_ID = BIC.FROM_END_ITEM_REV_ID
269 ) ) FROM_END_ITEM_REVISION,
270 DECODE( BIC.TO_END_ITEM_REV_ID
271 ,NULL ,NULL
272 ,(
273 SELECT mirb.REVISION
274 FROM MTL_ITEM_REVISIONS_B mirb
275 WHERE mirb.REVISION_ID = BIC.TO_END_ITEM_REV_ID
276 ) ) TO_END_ITEM_REVISION ,
277 BBM.EFFECTIVITY_CONTROL EFFECTIVITY_CONTROL
278 FROM
279 BOM_SMALL_IMPL_TEMP BITT,
280 BOM_COMPONENTS_B BIC,
281 BOM_STRUCTURES_B BBM
282 where
283 bitt.current_level = c_current_level
284 and bitt.organization_id = c_org_id
285 and bitt.sequence_id = c_sequence_id
286 /* Bug#7389906 Starts here. Took out the common condition */
287 and ((bitt.parent_obj_name = G_EGO_OBJ_NAME
288 and (bitt.parent_obj_name = nvl(bic.obj_name,G_EGO_OBJ_NAME)))
289 or
290 (bitt.parent_obj_name = G_CAD_OBJ_NAME
291 and (bitt.parent_obj_name = to_char(bic.obj_name))))
292 and bitt.parent_pk1_value = bic.pk1_value
293 /* Bug#7389906 Ends here*/
294 and bic.bill_sequence_id = bbm.common_bill_sequence_id
295 and bbm.organization_id = c_org_id
296 and ( (struct_name = FND_LOAD_UTIL.NULL_VALUE)
297 or
298 ( struct_name is null AND bbm.alternate_bom_designator is null )
299 or ( bbm.alternate_bom_designator = struct_name ) )
300 and NVL(BIC.ECO_FOR_PRODUCTION,2) = 2
301 and ( c_eng_mfg_flag = 2 or c_eng_mfg_flag = 1 and
302 ( c_current_level = 0
303 and bbm.assembly_type = 1
304 or c_current_level <> 0 and bitt.current_assembly_type = 1
305 and bbm.assembly_type = 1))
306 and ( c_current_level = 0
307 or /* start of all alternate logic */
308 bbm.alternate_bom_designator is null and
309 bitt.lowest_alternate_designator is null
310 or bbm.alternate_bom_designator = bitt.lowest_alternate_designator
311 or ( bitt.lowest_alternate_designator is null
312 and bbm.alternate_bom_designator is not null
313 and not exists (select NULL /*for current item */
314 from BOM_STRUCTURES_B bbm2
315 where bbm2.organization_id = c_org_id
316 and (bitt.parent_obj_name = G_EGO_OBJ_NAME
317 and (bbm2.assembly_item_id = bitt.parent_pk1_value
318 and bitt.parent_obj_name = nvl(bbm2.obj_name,G_EGO_OBJ_NAME)
322 and bitt.current_assembly_type = 1)
319 and bbm2.alternate_bom_designator = bbm.alternate_bom_designator))
320 and ( bitt.current_assembly_type = 2
321 or bbm2.assembly_type = 1
323 )
324 )
325 or /* Pickup prim par only if starting alt is not
326 null and bill for .. */
327 (bitt.lowest_alternate_designator is not null
328 and bbm.alternate_bom_designator is null
329 and not exists (select NULL
330 from BOM_STRUCTURES_B bbm2
331 where bbm2.organization_id = c_org_id
332 and (bitt.parent_obj_name = G_EGO_OBJ_NAME
333 and (bbm2.assembly_item_id = bbm.assembly_item_id
334 and nvl(bbm2.obj_name,G_EGO_OBJ_NAME) = nvl(bbm.obj_name,G_EGO_OBJ_NAME)
335 and bbm2.alternate_bom_designator = bitt.lowest_alternate_designator))
336 and ( bitt.current_assembly_type = 1
337 and bbm2.assembly_type = 1
338 or bitt.current_assembly_type = 2)
339 )
340 )
341 )
342 and (
343 (
344 bbm.obj_name = 'EGO_ITEM' or bbm.obj_name is NULL
345 and (exists (select 'X'
346 from MTL_SYSTEM_ITEMS_B MSI,
347 MTL_SYSTEM_ITEMS_B MSI_CHILD
348 where MSI.ORGANIZATION_ID = BBM.ORGANIZATION_ID
349 and MSI.INVENTORY_ITEM_ID = BBM.ASSEMBLY_ITEM_ID
350 AND MSI_CHILD.ORGANIZATION_ID = BBM.ORGANIZATION_ID
351 AND MSI_CHILD.INVENTORY_ITEM_ID = bic.COMPONENT_ITEM_ID
352 AND
353 ( ( c_current_level = 0 )
354 OR
355 (
356 ( c_current_level >= 1 )
357 AND
358 NOT ( -- start for checking configured parent
359 msi.BOM_ITEM_TYPE = 4 -- Standard
360 AND msi.REPLENISH_TO_ORDER_FLAG = 'Y'
361 AND msi.BASE_ITEM_ID IS NOT NULL -- configured item
362 AND msi_child.BOM_ITEM_TYPE IN (1, 2) -- model or option class
363 ) -- end for checking configured parent
364 AND msi.BOM_ENABLED_FLAG = 'Y' -- parent should be enabled
365 )
366 )
367 and
368 ( -- start revision filter logic
369 (
370 /* For non-null revision, select first level parents having same comp fixed revision, irrespective of
371 any effectivity criteria. */
372 revision IS NOT NULL
373 AND c_current_level = 0
374 AND revision = bic.COMPONENT_ITEM_REVISION_ID
375 AND ( ( NVL(BBM.EFFECTIVITY_CONTROL,1) = 1 ) /* bug:5227395 Filter out disabled components of non-date eff bill */
376 OR ( ( NVL(BBM.EFFECTIVITY_CONTROL,1) <> 1 ) AND ( bic.DISABLE_DATE IS NULL ) )
377 )
378 )
379 OR
380 (
381 ( ( revision IS NULL ) OR ( c_current_level <> 0 ) )
382 AND
383 ( /* Effectivity Control */
384 ( msi.effectivity_control=1 -- Date Effectivity Control
385 AND
389 )
386 (
387 ( BBM.EFFECTIVITY_CONTROL IS NULL
388 OR BBM.EFFECTIVITY_CONTROL <> 4 -- Date Effective structure
390 OR
391 (
392 BBM.EFFECTIVITY_CONTROL = 4 --Revision Effectivity
393 AND (
394 ( c_implemented_only_option = 1 AND bic.IMPLEMENTATION_DATE IS NOT NULL )
395 OR ( c_implemented_only_option = 2 )
396 )
397 AND ( bic.DISABLE_DATE IS NULL )
398 AND
399 ( --From end item revision for component <= parent current revision
400 BIC.FROM_END_ITEM_REV_ID IS NOT NULL
401 AND ( SELECT
402 MIRB.REVISION
403 FROM
404 MTL_ITEM_REVISIONS_B MIRB
405 WHERE
406 MIRB.INVENTORY_ITEM_ID = MSI.INVENTORY_ITEM_ID
407 AND MIRB.ORGANIZATION_ID = MSI.ORGANIZATION_ID
408 AND MIRB.REVISION_ID = BIC.FROM_END_ITEM_REV_ID
409 ) <=
410 (SELECT
411 MAX(MIRB.REVISION)
412 FROM
413 MTL_ITEM_REVISIONS_B MIRB
414 WHERE
415 MIRB.INVENTORY_ITEM_ID = MSI.INVENTORY_ITEM_ID
416 AND MIRB.ORGANIZATION_ID = MSI.ORGANIZATION_ID
417 AND MIRB.EFFECTIVITY_DATE <= to_date(c_implosion_date, 'YYYY/MM/DD HH24:MI:SS') )
418 )
419 AND
420 ( --To end item revision for component >= parent current revision
421 BIC.TO_END_ITEM_REV_ID IS NULL
422 OR
423 ( SELECT
424 MIRB.REVISION
425 FROM
426 MTL_ITEM_REVISIONS_B MIRB
427 WHERE
428 MIRB.INVENTORY_ITEM_ID = MSI.INVENTORY_ITEM_ID
429 AND MIRB.ORGANIZATION_ID = MSI.ORGANIZATION_ID
430 AND MIRB.REVISION_ID = BIC.TO_END_ITEM_REV_ID
431 ) >=
432 (SELECT
433 MAX(MIRB.REVISION)
434 FROM
435 MTL_ITEM_REVISIONS_B MIRB
436 WHERE
437 MIRB.INVENTORY_ITEM_ID = MSI.INVENTORY_ITEM_ID
438 AND MIRB.ORGANIZATION_ID = MSI.ORGANIZATION_ID
439 AND MIRB.EFFECTIVITY_DATE <= to_date(c_implosion_date, 'YYYY/MM/DD HH24:MI:SS') )
440 )
441 )
442 ) --end revision effectivity
443 --and bic.effectivity_date <= to_date(c_implosion_date, 'YYYY/MM/DD HH24:MI:SS')
444 --and ( bic.disable_date is null or
445 -- bic.disable_date > to_date(c_implosion_date, 'YYYY/MM/DD HH24:MI:SS'))
446 /* bug:4215514 For component of fixed revision parent, take implosion date
447 as high date of fixed revision
448 Hight Date = fixed rev effectivity date if (sysdate < fixed rev effectivity date)
449 Hight Date = fixed rev disable date if (sysdate > fixed rev disable date)
450 Hight Date = sysdate if (fixed rev effectivity date < sysdate < fixed rev disable date)
451 */
452 and
453 (
454 ( --floating revision of parent
455 (bic.component_item_revision_id is null OR c_current_level = 0)
456 and (bic.effectivity_date <= to_date (c_implosion_date, 'YYYY/MM/DD HH24:MI:SS')
457 or BBM.EFFECTIVITY_CONTROL = 4) /*Bug 8225025, remove bic.effectivity_date,implosion_date comparsion for revision effectivity control BOM*/
458 and
459 (
460 bic.disable_date is null
461 or bic.disable_date > to_date (c_implosion_date, 'YYYY/MM/DD HH24:MI:SS')
462 or (bic.disable_date is not null and bic.implementation_date is null) /* bug 8304937 */
463 )
464 )
465 or
466 (
467 -- fixed revision of parent
468 (bic.component_item_revision_id is not null and c_current_level <> 0)
469 and
470 (
471 (
472 ( bitt.EFFECTIVITY_CONTROL IS NULL
473 OR bitt.EFFECTIVITY_CONTROL <> 4 -- Date Effective structure
474 )
475 and
476 bitt.effectivity_date <=
477 (
478 select
479 decode( sign( min(frm.effectivity_date) - sysdate ),
480 0,sysdate,
481 1,min(frm.effectivity_date),
482 decode( min(tom.effectivity_date),
483 null,sysdate,
487 min(tom.effectivity_date)
484 decode( sign( min(tom.effectivity_date) - sysdate ),
485 0,sysdate,
486 1,sysdate,
488 )
489 )
490 ) disable_date
491 from
492 mtl_item_revisions_b frm,
493 mtl_item_revisions_b tom
494 where
495 frm.revision_id = bic.component_item_revision_id
496 and tom.revision_id(+) <> frm.revision_id
497 and frm.effectivity_date < tom.effectivity_date(+)
498 and tom.inventory_item_id(+) = frm.inventory_item_id
499 and tom.organization_id(+) = frm.organization_id
500 )
501 and
502 (
503 bitt.disable_date is null
504 or bitt.disable_date >
505 (
506 select
507 decode( sign( min(frm.effectivity_date) - sysdate ),
508 0,sysdate,
509 1,min(frm.effectivity_date),
510 decode( min(tom.effectivity_date),
511 null,sysdate,
512 decode( sign( min(tom.effectivity_date) - sysdate ),
513 0,sysdate,
514 1,sysdate,
515 min(tom.effectivity_date)
516 )
517 )
518 ) disable_date
519 from
520 mtl_item_revisions_b frm,
521 mtl_item_revisions_b tom
522 where
523 frm.revision_id = bic.component_item_revision_id
524 and tom.revision_id(+) <> frm.revision_id
525 and frm.effectivity_date < tom.effectivity_date(+)
526 and tom.inventory_item_id(+) = frm.inventory_item_id
527 and tom.organization_id(+) = frm.organization_id
528 )
529 ) -- end of and for disable date
530 ) -- end of date effective fixed rev parent
531 or
532 (
533 bitt.EFFECTIVITY_CONTROL = 4 -- Revision Effective structure
534 and
535 ( -- check for from end item revision
536 bitt.FROM_END_ITEM_REVISION IS NOT NULL
537 and bitt.FROM_END_ITEM_REVISION <=
538 (
539 SELECT mirb.REVISION
540 FROM MTL_ITEM_REVISIONS_B mirb
541 WHERE mirb.REVISION_ID = bic.COMPONENT_ITEM_REVISION_ID
542 )
543 )
544 and
545 ( -- check for to end item revision
546 bitt.TO_END_ITEM_REVISION IS NULL
547 or bitt.TO_END_ITEM_REVISION >=
548 (
549 SELECT mirb.REVISION
550 FROM MTL_ITEM_REVISIONS_B mirb
551 WHERE mirb.REVISION_ID = bic.COMPONENT_ITEM_REVISION_ID
552 )
553 )
554 ) -- end of rev effective fixed rev parent
555 ) -- end of and fixed rev parent
556 ) -- end of fixed rev parent
557 ) -- end of and fixed/floating rev parent
558 and (( c_implemented_only_option = 1
559 and bic.implementation_date is not null)
560 or
561 (( c_implemented_only_option = 2
562 and bic.effectivity_date in /*bug 8304937,display both impl and unimpl BOM details */
563 (select effectivity_date /*bug 8304937,display both impl and unimpl BOM details */
564 from BOM_COMPONENTS_B bic2
565 where bic.bill_sequence_id = bic2.bill_sequence_id
566 and (
570 or
567 (nvl(bic.obj_name,G_EGO_OBJ_NAME) = G_EGO_OBJ_NAME
568 and (bic.component_item_id = bic2.component_item_id
569 and nvl(bic.obj_name,G_EGO_OBJ_NAME) = nvl(bic2.obj_name,G_EGO_OBJ_NAME)))
571 (bic.obj_name = G_CAD_OBJ_NAME
572 and (bic.pk1_value = bic2.pk1_value
573 and bic.obj_name = bic2.obj_name))
574 )
575 and NVL(BIC2.ECO_FOR_PRODUCTION,2) = 2
576 and decode(bic.implementation_date, NULL,
577 decode(bic.old_component_sequence_id,null,
578 bic.component_sequence_id,
579 bic.old_component_sequence_id)
580 ,bic.component_sequence_id) =
581 decode(bic2.implementation_date,NULL,
582 decode(bic2.old_component_sequence_id,null,
583 /* corrected typo for bug 7321827
584 bic2.component_sequence_id,bic.old_component_sequence_id) */
585 bic2.component_sequence_id,bic2.old_component_sequence_id)
586 , bic2.component_sequence_id)
587 and bic2.effectivity_date <= to_date(c_implosion_date,'YYYY/MM/DD HH24:MI:SS')
588 and NOT EXISTS (SELECT null
589 FROM BOM_COMPONENTS_B bic3
590 WHERE bic3.bill_sequence_id = bic.bill_sequence_id
591 AND bic3.old_component_sequence_id = bic.component_sequence_id
592 and NVL(BIC3.ECO_FOR_PRODUCTION,2)= 2
593 AND bic3.acd_type in (2,3)
594 AND bic3.disable_date <=
595 to_date(c_implosion_date,'YYYY/MM/DD HH24:MI:SS')
596 and bic3.implementation_date is not null /* For bug8304937,display both impl and unimpl BOM details */
597 )
598 and (bic2.disable_date is null or bic2.disable_date
599 > to_date(c_implosion_date, 'YYYY/MM/DD HH24:MI:SS')
600 or (bic2.disable_date is not null and bic2.implementation_date is null) /* For bug8304937,display both impl and unimpl BOM details */
601 )
602 )
603 ) or BBM.EFFECTIVITY_CONTROL = 4 ) /*Bug 8225025, remove bic.effectivity_date/disable_date,implosion_date comparsion for revision effectivity control BOM*/
604 )
605 )--end date/revision effectivity
606 OR
607 ( msi.effectivity_control = 2
608 and ( bic.DISABLE_DATE IS NULL )
609 and
610 BIC.FROM_END_ITEM_UNIT_NUMBER <= NVL(BITT.TO_END_ITEM_UNIT_NUMBER, BIC.FROM_END_ITEM_UNIT_NUMBER)
611 and
612 NVL(BIC.TO_END_ITEM_UNIT_NUMBER, NVL(BITT.FROM_END_ITEM_UNIT_NUMBER,
613 BIC.FROM_END_ITEM_UNIT_NUMBER)) >=
614 NVL(BITT.FROM_END_ITEM_UNIT_NUMBER, BIC.FROM_END_ITEM_UNIT_NUMBER)
615 and ((c_implemented_only_option=1 and bic.implementation_date is not null)
616 or c_implemented_only_option = 2)
617 and bic.from_end_item_unit_number <= decode(msi.eam_item_type,1,c_serial_number_to, NVL(c_unit_number_to,bic.from_end_item_unit_number) )
618 and decode(msi.eam_item_type,1,c_serial_number_from,nvl(c_unit_number_from,bic.from_end_item_unit_number)) is not null
619 -- exclude serial eff EAM items
620 and (bic.to_end_item_unit_number is null
621 or bic.to_end_item_unit_number >=
622 decode(msi.eam_item_type,1,c_serial_number_from,nvl(c_unit_number_from,bic.from_end_item_unit_number)))
623 )
624 ) -- end Effectivity Logic
625 ) -- end OR
626 ) -- end revision filter logic
627 ) -- end select
628 ) -- end exists
629 ) -- end effectivity for EGO_ITEM
630 OR
631 (bbm.obj_name = G_CAD_OBJ_NAME)
632 ) -- end main query AND
633 order by bitt.parent_pk1_value, bitt.parent_pk2_value,bitt.parent_pk3_value,bitt.parent_pk4_value,bitt.parent_pk5_value,
634 bbm.assembly_item_id, bic.operation_seq_num;
635
636 TYPE number_tab_tp IS TABLE OF NUMBER
637 INDEX BY BINARY_INTEGER;
638
639 TYPE date_tab_tp IS TABLE OF DATE
640 INDEX BY BINARY_INTEGER;
641
642 TYPE varchar_tab_30 IS TABLE OF VARCHAR2(30)
643 INDEX BY BINARY_INTEGER;
644
645 TYPE varchar_tab_150 IS TABLE OF VARCHAR2(150)
646 INDEX BY BINARY_INTEGER;
647
648 TYPE varchar_tab_10 IS TABLE OF VARCHAR2(10)
649 INDEX BY BINARY_INTEGER;
650
651 TYPE varchar_tab_240 IS TABLE OF VARCHAR2(240)
652 INDEX BY BINARY_INTEGER;
653
654 TYPE varchar_tab_1 IS TABLE OF VARCHAR2(1)
655 INDEX BY BINARY_INTEGER;
656
657 TYPE varchar_tab_4000 IS TABLE OF VARCHAR2(4000)
658 INDEX BY BINARY_INTEGER;
659
660 TYPE varchar_tab_25 IS TABLE OF VARCHAR2(25)
661 INDEX BY BINARY_INTEGER;
662
663 TYPE varchar_tab_35 IS TABLE OF VARCHAR2(35)
664 INDEX BY BINARY_INTEGER;
665
666 TYPE varchar_tab_50 IS TABLE OF VARCHAR2(50)
667 INDEX BY BINARY_INTEGER;
668
669 TYPE varchar_tab_3 IS TABLE OF VARCHAR2(3)
670 INDEX BY BINARY_INTEGER;
671
672 l_lpk1 varchar_tab_150;
673 l_lpk2 varchar_tab_150;
674 l_lpk3 varchar_tab_150;
675 l_lpk4 varchar_tab_150;
676 l_lpk5 varchar_tab_150;
677 l_lobj varchar_tab_30;
681 l_ppk4 varchar_tab_150;
678 l_ppk1 varchar_tab_150;
679 l_ppk2 varchar_tab_150;
680 l_ppk3 varchar_tab_150;
682 l_ppk5 varchar_tab_150;
683 l_pobj varchar_tab_30;
684 l_apk1 varchar_tab_150;
685 l_apk2 varchar_tab_150;
686 l_apk3 varchar_tab_150;
687 l_apk4 varchar_tab_150;
688 l_apk5 varchar_tab_150;
689 l_aobj varchar_tab_30;
690
691 l_abd varchar_tab_10;
692 l_sc varchar_tab_4000;
693 l_lad varchar_tab_10;
694 l_cat number_tab_tp;
695 l_csi number_tab_tp;
696 l_oi number_tab_tp;
697 l_osn number_tab_tp;
698 l_ed date_tab_tp;
699 l_dd date_tab_tp;
700 l_fun varchar_tab_30;
701 l_tun varchar_tab_30;
702 l_bt number_tab_tp;
703 l_cq number_tab_tp;
704 l_risd number_tab_tp;
705 l_cn varchar_tab_10;
706 l_impf number_tab_tp;
707 l_str_type number_tab_tp;
708
709 --bug:4204847 Store the component path with each parent
710 -- if the loop is found then throw loop_found exception
711 l_component_path varchar_tab_4000;
712 l_cur_component_path VARCHAR2(4000);
713 l_cur_component VARCHAR2(20);
714 l_cur_substr VARCHAR2(20);
715 l_start_pos NUMBER;
716 loop_found EXCEPTION;
717 PRAGMA EXCEPTION_INIT(loop_found, -9999);
718
719 --bug:4218468 Component Item Revision Id tables
720 l_component_item_revision_id number_tab_tp;
721 l_from_end_item_revision varchar_tab_3;
722 l_to_end_item_revision varchar_tab_3;
723 l_effectivity_control number_tab_tp;
724
725 Loop_Count_Val Number := 0;
726
727 BEGIN
728
729 SELECT max(MAXIMUM_BOM_LEVEL)
730 INTO max_level
731 FROM BOM_PARAMETERS
732 WHERE ORGANIZATION_ID = org_id;
733
734 IF SQL%NOTFOUND or max_level is null THEN
735 max_level := 60;
736 END IF;
737
738 levels_to_implode := a_levels_to_implode;
739
740 IF (levels_to_implode < 0 OR levels_to_implode > max_level) THEN
741 levels_to_implode := max_level;
742 END IF;
743
744 cur_level := 0; /* initialize level */
745
746 WHILE (cur_level < levels_to_implode) LOOP
747 Loop_Count_Val := 0;
748 total_rows := 0;
749 cum_count := 0;
750
751 -- Delete pl/sql tables.
752
753 l_lpk1.delete;
754 l_lpk2.delete;
755 l_lpk3.delete;
756 l_lpk4.delete;
757 l_lpk5.delete;
758 l_lobj.delete;
759 l_ppk1.delete;
760 l_ppk2.delete;
761 l_ppk3.delete;
762 l_ppk4.delete;
763 l_ppk5.delete;
764 l_pobj.delete;
765 l_apk1.delete;
766 l_apk2.delete;
767 l_apk3.delete;
768 l_apk4.delete;
769 l_apk5.delete;
770 l_aobj.delete;
771 l_abd.delete;
772 l_sc.delete;
773 l_lad.delete;
774 l_cat.delete;
775 l_csi.delete;
776 l_oi.delete;
777 l_osn.delete;
778 l_ed.delete;
779 l_dd.delete;
780 l_fun.delete;
781 l_tun.delete;
782 l_bt.delete;
783 l_cq.delete;
784 l_risd.delete;
785 l_cn.delete;
786 l_impf.delete;
787 l_str_type.delete;
788 --bug:4204847 Clear the component path tables
789 l_component_path.delete;
790 --bug:4218468 Clear the component item revision id tables
791 l_component_item_revision_id.delete;
792
793 l_from_end_item_revision.delete;
794 l_to_end_item_revision.delete;
795 l_effectivity_control.delete;
796
797
798 -- Open the Cursor, Fetch and Close for each level
799
800 IF not imploder%isopen then
801 open imploder(cur_level,sequence_id,
802 eng_mfg_flag, org_id, IMpl_date,
803 unit_number_from, unit_number_to,
804 serial_number_from, serial_number_to,impl_flag, levels_to_implode);
805 end if;
806 --LOOP
807 FETCH imploder bulk collect into
808 l_lpk1,
809 l_lpk2,
810 l_lpk3,
811 l_lpk4,
812 l_lpk5,
813 l_lobj,
814 l_ppk1,
815 l_ppk2,
816 l_ppk3,
817 l_ppk4,
818 l_ppk5,
819 l_pobj,
820 l_apk1,
821 l_apk2,
822 l_apk3,
823 l_apk4,
824 l_apk5,
825 l_aobj,
826 l_abd,
827 l_sc,
828 l_lad,
829 l_cat,
830 l_csi,
831 l_osn,
832 l_ed,
833 l_dd,
834 l_bt,
835 l_cq,
836 l_risd,
837 l_cn,
838 l_impf,
839 l_oi,
840 l_fun,
841 l_tun,
842 l_str_type,
843 l_component_path,
844 l_component_item_revision_id ,
845 l_from_end_item_revision,
846 l_to_end_item_revision,
847 l_effectivity_control;
848 loop_Count_Val := imploder%rowcount ;
849
850 CLOSE imploder;
851 -- Loop through the values and check for cursors Check_Configured_Parent
852 -- and Check_Disabled_Parent. If Record is found then delete that
853 -- row from the pl/sql table
854
855
856 -- Need to do checkconfigure parent only if obj_nam is EGO.
857
858 For i in 1..loop_Count_Val Loop -- Check Loop
859 Begin
860 total_rows := total_rows + 1;
861 IF (cur_level = 0) THEN
862 l_LAD(i) := l_ABD(i);
863 END IF;
864 IF (cum_count = 0) THEN
865 prev_parent_item_id := l_ppk1(i);
866 END IF;
867
868 IF (prev_parent_item_id <> l_ppk1(i)) THEN
869 cum_count := 0;
870 prev_parent_item_id := l_ppk1(i);
871 END IF;
872
873 cum_count := cum_count + 1;
874
875 cat_sort := lpad(cum_count, 7, '0');
876 l_SC(i) := l_SC(i) || cat_sort;
877 --bug:4204847 Check for loops using component path stored
878 --check for loops by checking if the current parent exist in component path
879 l_cur_component_path := l_component_path(i);
880
881 IF l_lobj(i) = G_CAD_OBJ_NAME THEN
882 l_cur_component := LPAD('C'||l_apk1(i), 20, '0');
883 ELSE
884 l_cur_component := LPAD('I'||l_apk1(i), 20, '0');
885 END IF;
886
887 FOR j IN 1..(cur_level+1) LOOP
888 l_start_pos := 1+( (j-1) * 20 );
889 l_cur_substr := SUBSTR( l_cur_component_path, l_start_pos, 20 );
890 IF (l_cur_component = l_cur_substr) THEN
891 --loop found, raise exception
892 RAISE loop_found;
893 EXIT;
894 END IF;
895 END LOOP;
896
897 --update the component path for current parent
898 l_component_path(i) := l_cur_component || l_component_path(i);
899 End;
900 End Loop; -- End of Check Loop
901
902
903 --Loop to check if the record exist. If It exist then copy the record into
904 --an other table and insert the other table.
905 --This has to be done to avoid "ELEMENT DOES NOT EXIST exception"
906
907 -- Insert the Second table values using FORALL.
908
909 FORALL i IN 1..loop_Count_Val
910 INSERT INTO BOM_SMALL_IMPL_TEMP
911 (LOWEST_ITEM_ID,
912 CURRENT_ITEM_ID,
913 PARENT_ITEM_ID,
914 LOWEST_PK1_VALUE,
915 LOWEST_PK2_VALUE,
916 LOWEST_PK3_VALUE,
917 LOWEST_PK4_VALUE,
918 LOWEST_PK5_VALUE,
919 LOWEST_OBJ_NAME,
920 CURRENT_PK1_VALUE,
921 CURRENT_PK2_VALUE,
922 CURRENT_PK3_VALUE,
923 CURRENT_PK4_VALUE,
924 CURRENT_PK5_VALUE,
925 CURRENT_OBJ_NAME,
926 PARENT_PK1_VALUE,
927 PARENT_PK2_VALUE,
928 PARENT_PK3_VALUE,
929 PARENT_PK4_VALUE,
930 PARENT_PK5_VALUE,
931 PARENT_OBJ_NAME,
932 ALTERNATE_DESIGNATOR,
933 CURRENT_LEVEL,
934 SORT_CODE,
935 LOWEST_ALTERNATE_DESIGNATOR,
936 CURRENT_ASSEMBLY_TYPE,
937 SEQUENCE_ID,
938 COMPONENT_SEQUENCE_ID,
939 ORGANIZATION_ID,
940 REVISED_ITEM_SEQUENCE_ID,
941 CHANGE_NOTICE,
942 OPERATION_SEQ_NUM,
943 EFFECTIVITY_DATE,
944 DISABLE_DATE,
945 FROM_END_ITEM_UNIT_NUMBER,
946 TO_END_ITEM_UNIT_NUMBER,
947 BASIS_TYPE,
948 COMPONENT_QUANTITY,
949 IMPLEMENTED_FLAG,
950 LAST_UPDATE_DATE,
951 LAST_UPDATED_BY,
952 CREATION_DATE,
953 CREATED_BY,
954 PARENT_SORT_CODE,
955 IMPLOSION_DATE,
956 STRUCTURE_TYPE_ID,
957 ACCESS_FLAG,
958 COMPONENT_PATH ,
959 COMPONENT_ITEM_REVISION_ID,
960 FROM_END_ITEM_REVISION,
961 TO_END_ITEM_REVISION,
962 EFFECTIVITY_CONTROL
963 ) VALUES (
964 l_lpk1(i),
965 l_ppk1(i),
966 l_apk1(i),
967 l_lpk1(i),
968 l_lpk2(i),
969 l_lpk3(i),
970 l_lpk4(i),
971 l_lpk5(i),
972 l_lobj(i),
973 l_ppk1(i),
974 l_ppk2(i),
975 l_ppk3(i),
976 l_ppk4(i),
977 l_ppk5(i),
978 l_pobj(i),
979 l_apk1(i),
980 l_apk2(i),
981 l_apk3(i),
982 l_apk4(i),
983 l_apk5(i),
984 l_aobj(i),
985 l_abd(i),
986 cur_level + 1,
987 l_sc(i),
988 l_lad(i),
989 l_cat(i),
990 sequence_id,
991 l_csi(i),
992 l_oi(i),
993 l_risd(i),
994 l_cn(i),
995 l_osn(i),
996 l_ed(i),
997 l_dd(i),
998 l_fun(i),
999 l_tun(i),
1000 l_bt(i),
1001 l_cq(i),
1002 l_impf(i),
1003 sysdate,
1004 -1,
1005 sysdate,
1006 -1,
1007 decode(length(l_sc(i)), 7,null,substrb(l_sc(i),1,length(l_sc(i))-7)),
1008 to_date(impl_date, 'YYYY/MM/DD HH24:MI:SS'),
1009 l_str_type(i),
1010 --Check_User_View_priv(l_apk1(i),l_apk2(i)),
1011 'T',
1012 l_component_path(i) ,
1013 l_component_item_revision_id(i),
1014 l_from_end_item_revision(i),
1015 l_to_end_item_revision(i),
1016 l_effectivity_control(i)
1017 );
1018
1019 IF (total_rows <> 0) THEN
1020 cur_level := cur_level + 1;
1021 ELSE
1022 goto done_imploding;
1023 END IF;
1024
1025 END LOOP; /* while levels */
1026
1027
1028 <<done_imploding>>
1029
1030 /* Set the top item flag for which parent item row is not present in bom_small_impl_temp */
1031 UPDATE BOM_SMALL_IMPL_TEMP bsit_child
1032 SET
1033 bsit_child.TOP_ITEM_FLAG = 'Y'
1034 WHERE
1035 (
1036 ( levels_to_implode - 1 = bsit_child.CURRENT_LEVEL )
1037 OR
1038 (
1039 NOT EXISTS
1040 (
1041 SELECT 1
1042 FROM BOM_SMALL_IMPL_TEMP bsit_parent
1043 WHERE
1044 bsit_parent.CURRENT_ITEM_ID = bsit_child.PARENT_ITEM_ID
1045 AND bsit_parent.ORGANIZATION_ID = bsit_child.ORGANIZATION_ID
1046 AND bsit_parent.CURRENT_LEVEL = (bsit_child.CURRENT_LEVEL + 1)
1047 AND bsit_parent.SEQUENCE_ID = sequence_id
1048 )
1049 )
1050 )
1051 AND bsit_child.CURRENT_LEVEL > 0 -- top item page shows parents only
1052 AND bsit_child.SEQUENCE_ID = sequence_id;
1053
1054 /* Set the is_excluded_by_rule to 'Y' for parent item from which implosion item is excluded */
1055 UPDATE BOM_SMALL_IMPL_TEMP bsit_source
1056 SET IS_EXCLUDED_BY_RULE = 'Y'
1057 WHERE
1058 EXISTS
1059 (
1060 SELECT
1061 *
1062 FROM
1063 BOM_SMALL_IMPL_TEMP bsit,
1064 BOM_EXCLUSION_RULE_DEF berd,
1065 BOM_RULES_B brb
1066 WHERE
1067 (
1068 (
1069 berd.FROM_REVISION_ID IS NULL
1070 OR
1071 (
1072 (
1073 SELECT REVISION
1074 FROM MTL_ITEM_REVISIONS_B
1075 WHERE REVISION_ID = berd.FROM_REVISION_ID
1076 ) <=
1077 (
1078 SELECT
1079 mir.REVISION
1080 FROM
1081 MTL_ITEM_REVISIONS_B mir
1082 WHERE
1083 mir.EFFECTIVITY_DATE =
1084 (
1085 SELECT
1089 WHERE
1086 MAX(mir1.EFFECTIVITY_DATE)
1087 FROM
1088 MTL_ITEM_REVISIONS_B mir1
1090 mir1.EFFECTIVITY_DATE <= TO_DATE(impl_date, 'YYYY/MM/DD HH24:MI:SS')
1091 AND mir1.INVENTORY_ITEM_ID = bsit.PARENT_ITEM_ID
1092 AND mir1.ORGANIZATION_ID = bsit.ORGANIZATION_ID
1093 )
1094 AND mir.INVENTORY_ITEM_ID = bsit.PARENT_ITEM_ID
1095 AND mir.ORGANIZATION_ID = bsit.ORGANIZATION_ID
1096 )
1097 )
1098 )
1099 AND
1100 (
1101 berd.TO_REVISION_ID IS NULL
1102 OR
1103 (
1104 (
1105 SELECT
1106 mir.REVISION
1107 FROM
1108 MTL_ITEM_REVISIONS_B mir
1109 WHERE
1110 mir.EFFECTIVITY_DATE =
1111 (
1112 SELECT
1113 MAX(mir1.EFFECTIVITY_DATE)
1114 FROM
1115 MTL_ITEM_REVISIONS_B mir1
1116 WHERE
1117 mir1.EFFECTIVITY_DATE <= TO_DATE(impl_date, 'YYYY/MM/DD HH24:MI:SS')
1118 AND mir1.INVENTORY_ITEM_ID = bsit.PARENT_ITEM_ID
1119 AND mir1.ORGANIZATION_ID = bsit.ORGANIZATION_ID
1120 )
1121 AND mir.INVENTORY_ITEM_ID = bsit.PARENT_ITEM_ID
1122 AND mir.ORGANIZATION_ID = bsit.ORGANIZATION_ID
1123 ) <=
1124 (
1125 SELECT REVISION
1126 FROM MTL_ITEM_REVISIONS_B
1127 WHERE REVISION_ID = berd.TO_REVISION_ID
1128 )
1129 )
1130 )
1131 )
1132 AND
1133 (
1134 berd.IMPLEMENTATION_DATE IS NOT NULL
1135 AND berd.DISABLE_DATE IS NULL
1136 /* Exclusion rule does not have effectivity associated, either it is applied or not applied
1137 AND berd.IMPLEMENTATION_DATE <= to_date(impl_date, 'YYYY/MM/DD HH24:MI:SS')
1138 AND
1139 (
1140 berd.DISABLE_DATE IS NULL
1141 OR TO_DATE(impl_date, 'YYYY/MM/DD HH24:MI:SS') <= berd.DISABLE_DATE
1142 )
1143 */
1144 )
1145 AND berd.ACD_TYPE = 1
1146 AND bsit.COMPONENT_PATH LIKE berd.EXCLUSION_PATH || '%'
1147 AND brb.RULE_ID = berd.RULE_ID
1148 AND brb.BILL_SEQUENCE_ID = (SELECT BILL_SEQUENCE_ID FROM BOM_COMPONENTS_B WHERE COMPONENT_SEQUENCE_ID = bsit.COMPONENT_SEQUENCE_ID)
1149 AND bsit.COMPONENT_SEQUENCE_ID IS NOT NULL
1150 AND bsit.ROWID = bsit_source.ROWID
1151 )
1152 AND bsit_source.CURRENT_LEVEL > 0
1153 AND bsit_source.SEQUENCE_ID = sequence_id;
1154
1155 error_code := 0;
1156 err_msg := '';
1157 /*
1158 ** exception handlers
1159 */
1160 EXCEPTION
1161 WHEN max_extents THEN
1162 error_code := SQLCODE;
1163 err_msg := substrb(SQLERRM, 1, 80);
1164 --bug:4204847 If loop found then pass the current component path to get component loop string
1165 WHEN loop_found THEN
1166 error_code := SQLCODE;
1167 err_msg := l_cur_component || l_cur_component_path;
1168 WHEN OTHERS THEN
1169 error_code := SQLCODE;
1170 err_msg := substrb(SQLERRM, 1, 80);
1171 END ml_imploder_cad;
1172
1173
1174 /* This is an overloaded procedure that will narrow down the where used to the
1175 * provided structure type. It will simply call the existing imploder_userexit
1176 * without regard to structure type and then delete the rows from bom_mall_impl_temp
1177 * which do not conform to the user entered structure type.
1178 * One of the out parameters will indicate whether any structures of the reqd.
1179 * structure_type were found containing this item.
1180 * Extra parameters:
1181 * struct_type : structure type name
1182 * preferred_only : flag to check indicate only whether
1183 * implosion should be caried out only
1184 * for preferred structures.
1185 * 1 for true/ 2 for false
1186 * used_in_structure : Out parameter to indicate if any structures
1187 * of given structure type were found containing
1188 * this item.
1189 */
1190 PROCEDURE imploder_userexit(
1191 sequence_id IN NUMBER ,
1192 eng_mfg_flag IN NUMBER,
1193 org_id IN NUMBER,
1194 impl_flag IN NUMBER,
1195 display_option IN NUMBER,
1196 levels_to_implode IN NUMBER,
1197 obj_name IN VARCHAR2 DEFAULT 'EGO_ITEM',
1198 pk1_value IN VARCHAR2,
1199 pk2_value IN VARCHAR2,
1200 pk3_value IN VARCHAR2,
1201 pk4_value IN VARCHAR2,
1202 pk5_value IN VARCHAR2,
1203 impl_date IN VARCHAR2,
1204 unit_number_from IN VARCHAR2,
1205 unit_number_to IN VARCHAR2,
1206 err_msg OUT NOCOPY VARCHAR2,
1207 err_code OUT NOCOPY NUMBER,
1211 serial_number_to IN VARCHAR2 default NULL,
1208 organization_option IN NUMBER default 1,
1209 organization_hierarchy IN VARCHAR2 default NULL,
1210 serial_number_from IN VARCHAR2 default NULL,
1212 struct_name IN VARCHAR2 DEFAULT FND_LOAD_UTIL.NULL_VALUE,
1213 struct_type IN VARCHAR2,
1214 preferred_only IN NUMBER DEFAULT 2,
1215 used_in_structure OUT NOCOPY VARCHAR2
1216 )
1217 IS
1218 BEGIN
1219 imploder_userexit(
1220 sequence_id => sequence_id,
1221 eng_mfg_flag => eng_mfg_flag,
1222 org_id => org_id,
1223 impl_flag => impl_flag,
1224 display_option => display_option,
1225 levels_to_implode => levels_to_implode,
1226 obj_name => obj_name,
1227 pk1_value => pk1_value,
1228 pk2_value => pk2_value,
1229 pk3_value => pk3_value,
1230 pk4_value => pk4_value,
1231 pk5_value => pk5_value,
1232 impl_date => impl_date,
1233 unit_number_from => unit_number_from,
1234 unit_number_to => unit_number_to,
1235 err_msg => err_msg,
1236 err_code => err_code,
1237 organization_option => organization_option,
1238 organization_hierarchy => organization_hierarchy,
1239 serial_number_from => serial_number_from,
1240 serial_number_to => serial_number_to,
1241 struct_name => struct_name,
1242 struct_type => struct_type,
1243 preferred_only => preferred_only,
1244 used_in_structure => used_in_structure,
1245 revision => NULL
1246 );
1247 END imploder_userexit;
1248
1249
1250 /*
1251 * Overloaded procedure to take revision of component to search in first level
1252 * parent
1253 */
1254 PROCEDURE imploder_userexit(
1255 sequence_id IN NUMBER,
1256 eng_mfg_flag IN NUMBER,
1257 org_id IN NUMBER,
1258 impl_flag IN NUMBER,
1259 display_option IN NUMBER,
1260 levels_to_implode IN NUMBER,
1261 obj_name IN VARCHAR2 DEFAULT 'EGO_ITEM',
1262 pk1_value IN VARCHAR2,
1263 pk2_value IN VARCHAR2,
1264 pk3_value IN VARCHAR2,
1265 pk4_value IN VARCHAR2,
1266 pk5_value IN VARCHAR2,
1267 impl_date IN VARCHAR2,
1268 unit_number_from IN VARCHAR2,
1269 unit_number_to IN VARCHAR2,
1270 err_msg OUT NOCOPY VARCHAR2,
1271 err_code OUT NOCOPY NUMBER,
1272 organization_option IN NUMBER default 1,
1273 organization_hierarchy IN VARCHAR2 default NULL,
1274 serial_number_from IN VARCHAR2 default NULL,
1275 serial_number_to IN VARCHAR2 default NULL,
1276 struct_name IN VARCHAR2 DEFAULT FND_LOAD_UTIL.NULL_VALUE,
1277 revision IN VARCHAR2
1278 )
1279 AS
1280 a_err_msg VARCHAR2(2000); --bug:4204847 Increasing the length so that component path can be
1281 --returned in case of loop
1282 a_err_code NUMBER;
1283 t_org_code_list INV_OrgHierarchy_PVT.OrgID_tbl_type;
1284 N NUMBER:=0;
1285 dummy NUMBER;
1286 l_org_name VARCHAR2(60);
1287 item_found BOOLEAN:=TRUE;
1288
1289 l_parents_for_pk1 NUMBER := 0;
1290 l_seq_id NUMBER;
1291 l_preferred_structure_name VARCHAR2(10);
1292
1293 l_person VARCHAR2(30);
1294 l_predicate VARCHAR2(32767);
1295 l_predicate_api_status VARCHAR2(1);
1296
1297 BEGIN
1298 --DBMS_PROFILER.START_PROFILER(sequence_id);
1299 l_seq_id := sequence_id;
1300 /* If the parameter :
1301 Organization_Option = 1 then
1302 Take the current Organization
1303 else If Organization_Option = 2 is passed then
1304 Call the Inventory API to get the list of Organizations
1305 under the current Organization Hierarchy
1306 else if Organization Option = 3 is passed then
1307 Find the list of all the Organizations to which
1308 access is allowed */
1309
1310
1311 if ( organization_option =2 ) then
1312 INV_ORGHIERARCHY_PVT.ORG_HIERARCHY_LIST(organization_hierarchy ,
1313 org_id ,t_org_code_list );
1314
1315 elsif ( organization_option = 3 ) then
1316
1317 If (OBJ_NAME = 'EGO_ITEM' OR OBJ_NAME IS NULL) then
1318 -- bug:4931463 Re-written following query to reduce shared memory.
1319 for C1 in (
1320 SELECT
1321 orgs.ORGANIZATION_ID
1322 FROM
1323 ORG_ACCESS_VIEW oav,
1324 MTL_SYSTEM_ITEMS_B msi,
1325 MTL_PARAMETERS orgs,
1326 MTL_PARAMETERS child_org
1327 WHERE
1328 orgs.ORGANIZATION_ID = oav.ORGANIZATION_ID
1329 AND msi.ORGANIZATION_ID = orgs.ORGANIZATION_ID
1330 AND orgs.MASTER_ORGANIZATION_ID = child_org.MASTER_ORGANIZATION_ID
1331 AND oav.RESPONSIBILITY_ID = FND_PROFILE.Value('RESP_ID')
1332 AND oav.RESP_APPLICATION_ID = FND_PROFILE.value('RESP_APPL_ID')
1333 AND msi.INVENTORY_ITEM_ID = pk1_value
1334 AND child_org.ORGANIZATION_ID = org_id
1335 )
1336 LOOP
1337 N:=N+1;
1338 t_org_code_list(N) := C1.organization_id;
1339 END LOOP;
1340 end if;
1341 elsif
1342 ( organization_option = 1 ) then
1343 t_org_code_list(1) := org_id;
1347
1344 end if;
1345
1346 FOR I in t_org_code_list.FIRST..t_org_code_list.LAST LOOP
1348 --if ( organization_option = 2 or organization_option = 3 ) THEN
1349 -- We do not need to check if Item exists for org option 3 as
1350 -- this is already being done in the above if cond's.
1351
1352 if ( organization_option = 2 ) THEN
1353
1354 /*Check the existence of the Item in the curent Organization,
1355 if found then call the Imploder API for the Organization,otherwise
1356 check the existence of the Item in the next Organization of the
1357 Organization List*/
1358
1359 select count(*)
1360 into dummy
1361 from mtl_system_items
1362 where organization_id = t_org_code_list(I)
1363 and inventory_item_id = pk1_value;
1364
1365 if dummy <1 then
1366 item_found := FALSE;
1367 end if;
1368 end if;
1369
1370
1371 --dbms_output.put_line('trying to insert ');
1372 --bug:4204847 Store the component path with each parent with respect to item to be imploded.
1373 --bug:4218468 Store the component revision id to get the revision label in View
1374 if item_found then
1375 INSERT INTO BOM_SMALL_IMPL_TEMP
1376 (SEQUENCE_ID,
1377 LOWEST_ITEM_ID,
1378 CURRENT_ITEM_ID,
1379 PARENT_ITEM_ID,
1380 ALTERNATE_DESIGNATOR,
1381 CURRENT_LEVEL,
1382 SORT_CODE,
1383 CURRENT_ASSEMBLY_TYPE,
1384 COMPONENT_SEQUENCE_ID,
1385 ORGANIZATION_ID,
1386 LAST_UPDATE_DATE,
1387 LAST_UPDATED_BY,
1388 CREATION_DATE,
1389 CREATED_BY,
1390 current_pk1_value,
1391 current_pk2_value,
1392 current_pk3_value,
1393 current_pk4_value,
1394 current_pk5_value,
1395 current_obj_name,
1396 parent_pk1_value,
1397 parent_pk2_value,
1398 parent_pk3_value,
1399 parent_pk4_value,
1400 parent_pk5_value,
1401 parent_obj_name,
1402 lowest_pk1_value,
1403 lowest_pk2_value,
1404 lowest_pk3_value,
1405 lowest_pk4_value,
1406 lowest_pk5_value,
1407 lowest_obj_name,
1408 implosion_date ,
1409 lowest_gtin_number,
1410 lowest_gtin_description,
1411 lowest_trade_item_descriptor,
1412 current_gtin_number,
1413 current_gtin_description,
1414 current_trade_item_descriptor,
1415 parent_gtin_number,
1416 parent_gtin_description,
1417 parent_trade_item_descriptor,
1418 primary_uom_descriptor,
1419 GTIN_PUBLICATION_STATUS,
1420 access_flag,
1421 COMPONENT_PATH,
1422 COMPONENT_ITEM_REVISION_ID,
1423 FROM_END_ITEM_REVISION,
1424 TO_END_ITEM_REVISION,
1425 EFFECTIVITY_CONTROL,
1426 BASIS_TYPE
1427 ) VALUES (
1428 sequence_id,
1429 decode(obj_name,'EGO_ITEM',pk1_value,NULL),
1430 decode(obj_name,'EGO_ITEM',pk1_value,NULL),
1431 decode(obj_name,'EGO_ITEM',pk1_value,NULL),
1432 NULL,
1433 0,
1434 '0000001',
1435 NULL,
1436 NULL,
1437 t_org_code_list(I),
1438 sysdate,
1439 -1,
1440 sysdate,
1441 -1,
1442 pk1_value,
1443 decode(obj_name,'EGO_ITEM',t_org_code_list(I),NULL,t_org_code_list(I),pk2_value), --pk2_value,
1444 pk3_value,
1445 pk4_value,
1446 pk5_value,
1447 nvl(obj_name,G_EGO_OBJ_NAME),
1448 pk1_value,
1449 decode(obj_name,'EGO_ITEM',t_org_code_list(I),NULL,t_org_code_list(I),pk2_value), --pk2_value,
1450 pk3_value,
1451 pk4_value,
1452 pk5_value,
1453 nvl(obj_name,G_EGO_OBJ_NAME),
1454 pk1_value,
1455 decode(obj_name,'EGO_ITEM',t_org_code_list(I),NULL,t_org_code_list(I),pk2_value), --pk2_value,
1456 pk3_value,
1457 pk4_value,
1458 pk5_value,
1459 nvl(obj_name,G_EGO_OBJ_NAME),
1460 to_date(impl_date, 'YYYY/MM/DD HH24:MI:SS'),
1461 NULL,--l_gtin,
1462 NULL,--l_gtin_description,
1463 NULL,--l_gtin_trade_item_descriptor,
1464 NULL,--l_gtin,
1465 NULL,--l_gtin_description,
1466 NULL,--l_gtin_trade_item_descriptor,
1467 NULL,--l_gtin,
1468 NULL,--l_gtin_description,
1469 NULL,--l_gtin_trade_item_descriptor,
1470 NULL,--l_primary_uom_desc,
1471 NULL,--l_gtin_publication_status
1472 Check_User_View_priv(pk1_value,pk2_value)--'T'
1473 ,lpad( decode(obj_name,G_CAD_OBJ_NAME,'C','I') || pk1_value, 20, '0')
1474 ,NULL --bug:4218468 For header row, insert NULL
1475 ,NULL
1476 ,NULL
1477 ,NULL
1478 ,-1 -- set the basis type to -1 for displaying blank value for header row
1479 );
1480 bom_imploder_pub.implosion_cad(sequence_id,eng_mfg_flag,t_org_code_list(I),
1481 impl_flag, display_option, levels_to_implode, impl_date,
1482 unit_number_from, unit_number_to,
1483 a_err_msg, a_err_code, serial_number_from, serial_number_to,
1484 struct_name, revision);
1485
1486 err_msg := a_err_msg;
1487 err_code := a_err_code;
1488 end if;
1489 item_found := TRUE;
1490
1491 /*BEGIN
1492 IF (structure_type_id IS NOT NULL)
1493 THEN
1494
1495 SELECT COUNT(COMPONENT_SEQUENCE_ID) INTO l_parents_for_pk1
1496 from BOM_SMALL_IMPL_TEMP
1497 WHERE
1498 LOWEST_ITEM_ID = pk1_value AND ORGANIZATION_ID = t_org_code_list(I)
1499 AND CURRENT_LEVEL = 1 AND SEQUENCE_ID = l_seq_id;
1500
1501 BEGIN
1502 IF (l_parents_for_pk1 = 0)
1503 THEN
1504 SELECT ALTERNATE_BOM_DESIGNATOR INTO l_preferred_structure_name
1508 AND STRUCTURE_TYPE_ID = structure_type_id
1505 FROM BOM_STRUCTURES_B
1506 WHERE ASSEMBLY_ITEM_ID = pk1_value
1507 AND ORGANIZATION_ID = pk2_value
1509 AND IS_PREFERRED = 'Y';
1510
1511 UPDATE BOM_SMALL_IMPL_TEMP
1512 SET TOP_ITEM_FLAG ='Y',
1513 ALTERNATE_DESIGNATOR = l_preferred_structure_name,
1514 STRUCTURE_TYPE_ID = structure_type_id
1515 WHERE CURRENT_LEVEL = 0 AND SEQUENCE_ID = l_seq_id
1516 AND LOWEST_ITEM_ID = pk1_value AND ORGANIZATION_ID = t_org_code_list(I) ;
1517
1518 END IF;
1519 EXCEPTION
1520 WHEN OTHERS THEN
1521 l_preferred_structure_name := NULL;
1522 ROLLBACK;
1523 END;
1524 END IF;
1525 END; */
1526
1527 end loop;
1528 if (a_err_code <> 0) then
1529 ROLLBACK;
1530 end if;
1531
1532 /* Get the security predicate */
1533
1534 SELECT 'HZ_PARTY'||':'||person_party_id INTO l_person
1535 FROM fnd_user WHERE user_name = FND_Global.User_Name;
1536
1537 EGO_DATA_SECURITY.get_security_predicate(
1538 p_api_version =>1.0,
1539 p_function =>'EGO_VIEW_ITEM',
1540 p_object_name =>'EGO_ITEM',
1541 p_user_name => l_person,
1542 p_statement_type =>'EXISTS',
1543 p_pk1_alias =>'BI.PARENT_PK1_VALUE',
1544 p_pk2_alias =>'BI.ORGANIZATION_ID',
1545 p_pk3_alias =>NULL,
1546 p_pk4_alias =>NULL,
1547 p_pk5_alias =>NULL,
1548 x_predicate => l_predicate,
1549 x_return_status => l_predicate_api_status);
1550
1551 IF l_predicate_api_status <> 'T'
1552 THEN
1553 Raise NO_DATA_FOUND;
1554 END IF;
1555
1556 IF l_predicate IS NOT NULL
1557 THEN
1558
1559 EXECUTE IMMEDIATE 'UPDATE bom_small_impl_temp BI SET BI.access_flag = ''F'' WHERE NOT '|| l_predicate;
1560
1561 END IF;
1562
1563 --DBMS_PROFILER.STOP_PROFILER;
1564
1565 -- R12C: Remove the rows for normal implosion results when the structure name <> 'PIM_PBOM_S'
1566 -- For packs, below procedure is called. So it will delete the rows.
1567
1568 IF ( struct_name <> 'PIM_PBOM_S' )
1569 THEN
1570 -- Normal implosion without packs
1571 DELETE
1572 FROM BOM_SMALL_IMPL_TEMP
1573 WHERE
1574 ALTERNATE_DESIGNATOR = 'PIM_PBOM_S'
1575 AND SEQUENCE_ID = sequence_id;
1576
1577 END IF;
1578
1579
1580 EXCEPTION
1581 WHEN OTHERS THEN
1582 err_msg := substrb(SQLERRM, 1, 80);
1583 err_code := SQLCODE;
1584 ROLLBACK;
1585 END imploder_userexit;
1586
1587 /*
1588 * Overloaded procedure to take revision of component to search in first level
1589 * parent. This is an overloaded procedure that will narrow down the where used to the
1590 * provided structure type.
1591 */
1592 PROCEDURE imploder_userexit(
1593 sequence_id IN NUMBER ,
1594 eng_mfg_flag IN NUMBER,
1595 org_id IN NUMBER,
1596 impl_flag IN NUMBER,
1597 display_option IN NUMBER,
1598 levels_to_implode IN NUMBER,
1599 obj_name IN VARCHAR2 DEFAULT 'EGO_ITEM',
1600 pk1_value IN VARCHAR2,
1601 pk2_value IN VARCHAR2,
1602 pk3_value IN VARCHAR2,
1603 pk4_value IN VARCHAR2,
1604 pk5_value IN VARCHAR2,
1605 impl_date IN VARCHAR2,
1606 unit_number_from IN VARCHAR2,
1607 unit_number_to IN VARCHAR2,
1608 err_msg OUT NOCOPY VARCHAR2,
1609 err_code OUT NOCOPY NUMBER,
1610 organization_option IN NUMBER default 1,
1611 organization_hierarchy IN VARCHAR2 default NULL,
1612 serial_number_from IN VARCHAR2 default NULL,
1613 serial_number_to IN VARCHAR2 default NULL,
1614 struct_name IN VARCHAR2 DEFAULT FND_LOAD_UTIL.NULL_VALUE,
1615 struct_type IN VARCHAR2,
1616 preferred_only IN NUMBER DEFAULT 2,
1617 used_in_structure OUT NOCOPY VARCHAR2,
1618 revision IN VARCHAR2
1619 )
1620 IS
1621 l_str_type_id NUMBER;
1622 l_row_count NUMBER;
1623 l_sequence_id NUMBER;
1624 l_structure_count NUMBER := 0;
1625 l_pk1_value VARCHAR2(100);
1626 BEGIN
1627 IF sequence_id is null then
1628 SELECT BOM_IMPLOSION_TEMP_S.nextval
1629 INTO l_sequence_id
1630 FROM SYS.DUAL;
1631 else
1632 l_sequence_id := sequence_id;
1633 end if;
1634 --first populate the temp table.
1635 imploder_userexit(
1636 sequence_id => l_sequence_id ,
1637 eng_mfg_flag => eng_mfg_flag ,
1638 org_id => org_id ,
1639 impl_flag => impl_flag ,
1640 display_option => display_option,
1641 levels_to_implode => levels_to_implode ,
1642 obj_name => obj_name ,
1643 pk1_value => pk1_value ,
1644 pk2_value => pk2_value ,
1645 pk3_value => pk3_value ,
1646 pk4_value => pk4_value ,
1647 pk5_value => pk5_value ,
1648 impl_date => impl_date ,
1649 unit_number_from => unit_number_from ,
1650 unit_number_to => unit_number_to,
1651 err_msg => err_msg ,
1652 err_code => err_code,
1653 organization_option => organization_option ,
1654 organization_hierarchy => organization_hierarchy ,
1655 serial_number_from => serial_number_from ,
1656 serial_number_to => serial_number_to ,
1657 struct_name => struct_name,
1658 revision => revision);
1659
1660 l_pk1_value := pk1_value;
1661 --Find the structure type id for the struct_type
1662 select structure_type_id into l_str_type_id
1663 from bom_structure_types_b
1664 where structure_type_name = struct_type;
1665
1666 --Delete those records from bom_small_impl_temp
1667 --which do not have the structure type id
1668 --user wants
1669
1670 IF ( preferred_only = 2) THEN
1671 DELETE FROM BOM_SMALL_IMPL_TEMP
1672 WHERE STRUCTURE_TYPE_ID <> l_str_type_id;
1673 ELSE
1674 /* Look only for preferred structures */
1675 DELETE FROM BOM_SMALL_IMPL_TEMP
1676 WHERE STRUCTURE_TYPE_ID <> l_str_type_id
1677 OR
1678 (STRUCTURE_TYPE_ID = l_str_type_id
1679 AND not exists
1680 (
1681 SELECT 1 from bom_structures_b
1682 where assembly_item_id = l_pk1_value
1683 and organization_id = org_id
1684 and structure_type_id = l_str_type_id
1685 and is_preferred = 'Y'
1686 )
1687 );
1688 END IF;
1689
1690 --Check if any records apart from the item record exist.
1691 SELECT count(*) into l_row_count
1692 from bom_small_impl_temp
1693 where SEQUENCE_ID = l_sequence_id;
1694 --Check if the item itself has a structure header of given str type defined
1695 --Fix for Bug 5943195
1696 --If an item has structure header only without components , then it won't
1697 --be canddidate as part of packaging heirrarchy.
1698
1699 l_structure_count := 0;
1700 begin
1701 SELECT 1 into l_structure_count
1702 FROM BOM_STRUCTURES_B BST1
1703 WHERE BST1.ASSEMBLY_ITEM_ID = l_pk1_value
1704 AND BST1.ORGANIZATION_ID = org_id
1705 AND BST1.STRUCTURE_TYPE_ID = l_str_type_id
1706 AND exists
1707 (
1708 SELECT 1 FROM BOM_COMPONENTS_B CPT1 WHERE CPT1.bill_sequence_id = BST1.bill_sequence_id
1709 AND CPT1.Disable_date IS NULL
1710 )
1711 AND rownum = 1;
1712 exception when no_data_found then
1713 l_structure_count := 0;
1714 end;
1715 IF l_row_count < 2 AND l_structure_count = 0 THEN
1716 used_in_structure := 'F';
1717 ELSE
1718 used_in_structure := 'T';
1719 END IF;
1720 END imploder_userexit;
1721
1722 END bom_imploder_pub;