[Home] [Help]
PACKAGE BODY: APPS.BOM_IMPLODER_PUB
Source
1 package body bom_imploder_pub as
2 /* $Header: BOMPIMPB.pls 120.13.12010000.2 2008/09/26 12:48:36 rmpartha ship $ */
3
4 /*==========================================================================+
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,
108 organization_option IN NUMBER default 1,
109 organization_hierarchy IN VARCHAR2 default NULL,
110 serial_number_from IN VARCHAR2 default NULL,
111 serial_number_to 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,
252 BIC.BASIS_TYPE BT,
253 BIC.COMPONENT_QUANTITY CQ,
254 BIC.REVISED_ITEM_SEQUENCE_ID RISD,
255 BIC.CHANGE_NOTICE CN,
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)
319 and bbm2.alternate_bom_designator = bbm.alternate_bom_designator))
320 and ( bitt.current_assembly_type = 2
321 or bbm2.assembly_type = 1
322 and bitt.current_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 )
369 (
366 )
367 and
368 ( -- start revision filter logic
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
386 (
387 ( BBM.EFFECTIVITY_CONTROL IS NULL
388 OR BBM.EFFECTIVITY_CONTROL <> 4 -- Date Effective structure
389 )
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 and
458 (
459 bic.disable_date is null
460 or bic.disable_date > to_date (c_implosion_date, 'YYYY/MM/DD HH24:MI:SS')
461 )
462 )
463 or
464 (
465 -- fixed revision of parent
466 (bic.component_item_revision_id is not null and c_current_level <> 0)
470 ( bitt.EFFECTIVITY_CONTROL IS NULL
467 and
468 (
469 (
471 OR bitt.EFFECTIVITY_CONTROL <> 4 -- Date Effective structure
472 )
473 and
474 bitt.effectivity_date <=
475 (
476 select
477 decode( sign( min(frm.effectivity_date) - sysdate ),
478 0,sysdate,
479 1,min(frm.effectivity_date),
480 decode( min(tom.effectivity_date),
481 null,sysdate,
482 decode( sign( min(tom.effectivity_date) - sysdate ),
483 0,sysdate,
484 1,sysdate,
485 min(tom.effectivity_date)
486 )
487 )
488 ) disable_date
489 from
490 mtl_item_revisions_b frm,
491 mtl_item_revisions_b tom
492 where
493 frm.revision_id = bic.component_item_revision_id
494 and tom.revision_id(+) <> frm.revision_id
495 and frm.effectivity_date < tom.effectivity_date(+)
496 and tom.inventory_item_id(+) = frm.inventory_item_id
497 and tom.organization_id(+) = frm.organization_id
498 )
499 and
500 (
501 bitt.disable_date is null
502 or bitt.disable_date >
503 (
504 select
505 decode( sign( min(frm.effectivity_date) - sysdate ),
506 0,sysdate,
507 1,min(frm.effectivity_date),
508 decode( min(tom.effectivity_date),
509 null,sysdate,
510 decode( sign( min(tom.effectivity_date) - sysdate ),
511 0,sysdate,
512 1,sysdate,
513 min(tom.effectivity_date)
514 )
515 )
516 ) disable_date
517 from
518 mtl_item_revisions_b frm,
519 mtl_item_revisions_b tom
520 where
521 frm.revision_id = bic.component_item_revision_id
522 and tom.revision_id(+) <> frm.revision_id
523 and frm.effectivity_date < tom.effectivity_date(+)
524 and tom.inventory_item_id(+) = frm.inventory_item_id
525 and tom.organization_id(+) = frm.organization_id
526 )
527 ) -- end of and for disable date
528 ) -- end of date effective fixed rev parent
529 or
530 (
531 bitt.EFFECTIVITY_CONTROL = 4 -- Revision Effective structure
532 and
533 ( -- check for from end item revision
534 bitt.FROM_END_ITEM_REVISION IS NOT NULL
535 and bitt.FROM_END_ITEM_REVISION <=
536 (
537 SELECT mirb.REVISION
538 FROM MTL_ITEM_REVISIONS_B mirb
539 WHERE mirb.REVISION_ID = bic.COMPONENT_ITEM_REVISION_ID
540 )
541 )
542 and
543 ( -- check for to end item revision
544 bitt.TO_END_ITEM_REVISION IS NULL
545 or bitt.TO_END_ITEM_REVISION >=
546 (
547 SELECT mirb.REVISION
551 )
548 FROM MTL_ITEM_REVISIONS_B mirb
549 WHERE mirb.REVISION_ID = bic.COMPONENT_ITEM_REVISION_ID
550 )
552 ) -- end of rev effective fixed rev parent
553 ) -- end of and fixed rev parent
554 ) -- end of fixed rev parent
555 ) -- end of and fixed/floating rev parent
556 and (( c_implemented_only_option = 1
557 and bic.implementation_date is not null)
558 or
559 ( c_implemented_only_option = 2
560 and bic.effectivity_date =
561 (select max(effectivity_date)
562 from BOM_COMPONENTS_B bic2
563 where bic.bill_sequence_id = bic2.bill_sequence_id
564 and (
565 (nvl(bic.obj_name,G_EGO_OBJ_NAME) = G_EGO_OBJ_NAME
566 and (bic.component_item_id = bic2.component_item_id
567 and nvl(bic.obj_name,G_EGO_OBJ_NAME) = nvl(bic2.obj_name,G_EGO_OBJ_NAME)))
568 or
569 (bic.obj_name = G_CAD_OBJ_NAME
570 and (bic.pk1_value = bic2.pk1_value
571 and bic.obj_name = bic2.obj_name))
572 )
573 and NVL(BIC2.ECO_FOR_PRODUCTION,2) = 2
574 and decode(bic.implementation_date, NULL,
575 decode(bic.old_component_sequence_id,null,
576 bic.component_sequence_id,
577 bic.old_component_sequence_id)
578 ,bic.component_sequence_id) =
579 decode(bic2.implementation_date,NULL,
580 decode(bic2.old_component_sequence_id,null,
581 bic2.component_sequence_id,bic.old_component_sequence_id)
582 , bic2.component_sequence_id)
583 and bic2.effectivity_date <= to_date(c_implosion_date,'YYYY/MM/DD HH24:MI:SS')
584 and NOT EXISTS (SELECT null
585 FROM BOM_COMPONENTS_B bic3
586 WHERE bic3.bill_sequence_id = bic.bill_sequence_id
587 AND bic3.old_component_sequence_id = bic.component_sequence_id
588 and NVL(BIC3.ECO_FOR_PRODUCTION,2)= 2
589 AND bic3.acd_type in (2,3)
590 AND bic3.disable_date <=
591 to_date(c_implosion_date,'YYYY/MM/DD HH24:MI:SS'))
592 and (bic2.disable_date is null or bic2.disable_date
593 > to_date(c_implosion_date, 'YYYY/MM/DD HH24:MI:SS'))
594 )
595 )
596 )
597 )--end date/revision effectivity
598 OR
599 ( msi.effectivity_control = 2
600 and ( bic.DISABLE_DATE IS NULL )
601 and
602 BIC.FROM_END_ITEM_UNIT_NUMBER <= NVL(BITT.TO_END_ITEM_UNIT_NUMBER, BIC.FROM_END_ITEM_UNIT_NUMBER)
603 and
604 NVL(BIC.TO_END_ITEM_UNIT_NUMBER, NVL(BITT.FROM_END_ITEM_UNIT_NUMBER,
605 BIC.FROM_END_ITEM_UNIT_NUMBER)) >=
606 NVL(BITT.FROM_END_ITEM_UNIT_NUMBER, BIC.FROM_END_ITEM_UNIT_NUMBER)
607 and ((c_implemented_only_option=1 and bic.implementation_date is not null)
611 -- exclude serial eff EAM items
608 or c_implemented_only_option = 2)
609 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) )
610 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
612 and (bic.to_end_item_unit_number is null
613 or bic.to_end_item_unit_number >=
614 decode(msi.eam_item_type,1,c_serial_number_from,nvl(c_unit_number_from,bic.from_end_item_unit_number)))
615 )
616 ) -- end Effectivity Logic
617 ) -- end OR
618 ) -- end revision filter logic
619 ) -- end select
620 ) -- end exists
621 ) -- end effectivity for EGO_ITEM
622 OR
623 (bbm.obj_name = G_CAD_OBJ_NAME)
624 ) -- end main query AND
625 order by bitt.parent_pk1_value, bitt.parent_pk2_value,bitt.parent_pk3_value,bitt.parent_pk4_value,bitt.parent_pk5_value,
626 bbm.assembly_item_id, bic.operation_seq_num;
627
628 TYPE number_tab_tp IS TABLE OF NUMBER
629 INDEX BY BINARY_INTEGER;
630
631 TYPE date_tab_tp IS TABLE OF DATE
632 INDEX BY BINARY_INTEGER;
633
634 TYPE varchar_tab_30 IS TABLE OF VARCHAR2(30)
635 INDEX BY BINARY_INTEGER;
636
637 TYPE varchar_tab_150 IS TABLE OF VARCHAR2(150)
638 INDEX BY BINARY_INTEGER;
639
640 TYPE varchar_tab_10 IS TABLE OF VARCHAR2(10)
641 INDEX BY BINARY_INTEGER;
642
643 TYPE varchar_tab_240 IS TABLE OF VARCHAR2(240)
644 INDEX BY BINARY_INTEGER;
645
646 TYPE varchar_tab_1 IS TABLE OF VARCHAR2(1)
647 INDEX BY BINARY_INTEGER;
648
649 TYPE varchar_tab_4000 IS TABLE OF VARCHAR2(4000)
650 INDEX BY BINARY_INTEGER;
651
652 TYPE varchar_tab_25 IS TABLE OF VARCHAR2(25)
653 INDEX BY BINARY_INTEGER;
654
655 TYPE varchar_tab_35 IS TABLE OF VARCHAR2(35)
656 INDEX BY BINARY_INTEGER;
657
658 TYPE varchar_tab_50 IS TABLE OF VARCHAR2(50)
659 INDEX BY BINARY_INTEGER;
660
661 TYPE varchar_tab_3 IS TABLE OF VARCHAR2(3)
662 INDEX BY BINARY_INTEGER;
663
664 l_lpk1 varchar_tab_150;
665 l_lpk2 varchar_tab_150;
666 l_lpk3 varchar_tab_150;
667 l_lpk4 varchar_tab_150;
668 l_lpk5 varchar_tab_150;
669 l_lobj varchar_tab_30;
670 l_ppk1 varchar_tab_150;
671 l_ppk2 varchar_tab_150;
672 l_ppk3 varchar_tab_150;
673 l_ppk4 varchar_tab_150;
674 l_ppk5 varchar_tab_150;
675 l_pobj varchar_tab_30;
676 l_apk1 varchar_tab_150;
677 l_apk2 varchar_tab_150;
678 l_apk3 varchar_tab_150;
679 l_apk4 varchar_tab_150;
680 l_apk5 varchar_tab_150;
681 l_aobj varchar_tab_30;
682
683 l_abd varchar_tab_10;
684 l_sc varchar_tab_4000;
685 l_lad varchar_tab_10;
686 l_cat number_tab_tp;
687 l_csi number_tab_tp;
688 l_oi number_tab_tp;
689 l_osn number_tab_tp;
690 l_ed date_tab_tp;
691 l_dd date_tab_tp;
692 l_fun varchar_tab_30;
693 l_tun varchar_tab_30;
694 l_bt number_tab_tp;
695 l_cq number_tab_tp;
696 l_risd number_tab_tp;
697 l_cn varchar_tab_10;
698 l_impf number_tab_tp;
699 l_str_type number_tab_tp;
700
701 --bug:4204847 Store the component path with each parent
702 -- if the loop is found then throw loop_found exception
703 l_component_path varchar_tab_4000;
704 l_cur_component_path VARCHAR2(4000);
705 l_cur_component VARCHAR2(20);
709 PRAGMA EXCEPTION_INIT(loop_found, -9999);
706 l_cur_substr VARCHAR2(20);
707 l_start_pos NUMBER;
708 loop_found EXCEPTION;
710
711 --bug:4218468 Component Item Revision Id tables
712 l_component_item_revision_id number_tab_tp;
713 l_from_end_item_revision varchar_tab_3;
714 l_to_end_item_revision varchar_tab_3;
715 l_effectivity_control number_tab_tp;
716
717 Loop_Count_Val Number := 0;
718
719 BEGIN
720
721 SELECT max(MAXIMUM_BOM_LEVEL)
722 INTO max_level
723 FROM BOM_PARAMETERS
724 WHERE ORGANIZATION_ID = org_id;
725
726 IF SQL%NOTFOUND or max_level is null THEN
727 max_level := 60;
728 END IF;
729
730 levels_to_implode := a_levels_to_implode;
731
732 IF (levels_to_implode < 0 OR levels_to_implode > max_level) THEN
733 levels_to_implode := max_level;
734 END IF;
735
736 cur_level := 0; /* initialize level */
737
738 WHILE (cur_level < levels_to_implode) LOOP
739 Loop_Count_Val := 0;
740 total_rows := 0;
741 cum_count := 0;
742
743 -- Delete pl/sql tables.
744
745 l_lpk1.delete;
746 l_lpk2.delete;
747 l_lpk3.delete;
748 l_lpk4.delete;
749 l_lpk5.delete;
750 l_lobj.delete;
751 l_ppk1.delete;
752 l_ppk2.delete;
753 l_ppk3.delete;
754 l_ppk4.delete;
755 l_ppk5.delete;
756 l_pobj.delete;
757 l_apk1.delete;
758 l_apk2.delete;
759 l_apk3.delete;
760 l_apk4.delete;
761 l_apk5.delete;
762 l_aobj.delete;
763 l_abd.delete;
764 l_sc.delete;
765 l_lad.delete;
766 l_cat.delete;
767 l_csi.delete;
768 l_oi.delete;
769 l_osn.delete;
770 l_ed.delete;
771 l_dd.delete;
772 l_fun.delete;
773 l_tun.delete;
774 l_bt.delete;
775 l_cq.delete;
776 l_risd.delete;
777 l_cn.delete;
778 l_impf.delete;
779 l_str_type.delete;
780 --bug:4204847 Clear the component path tables
781 l_component_path.delete;
782 --bug:4218468 Clear the component item revision id tables
783 l_component_item_revision_id.delete;
784
785 l_from_end_item_revision.delete;
786 l_to_end_item_revision.delete;
787 l_effectivity_control.delete;
788
789
790 -- Open the Cursor, Fetch and Close for each level
791
792 IF not imploder%isopen then
793 open imploder(cur_level,sequence_id,
794 eng_mfg_flag, org_id, IMpl_date,
795 unit_number_from, unit_number_to,
796 serial_number_from, serial_number_to,impl_flag, levels_to_implode);
797 end if;
798 --LOOP
799 FETCH imploder bulk collect into
800 l_lpk1,
801 l_lpk2,
802 l_lpk3,
803 l_lpk4,
804 l_lpk5,
805 l_lobj,
806 l_ppk1,
807 l_ppk2,
808 l_ppk3,
809 l_ppk4,
810 l_ppk5,
811 l_pobj,
812 l_apk1,
813 l_apk2,
814 l_apk3,
815 l_apk4,
816 l_apk5,
817 l_aobj,
818 l_abd,
819 l_sc,
820 l_lad,
821 l_cat,
822 l_csi,
823 l_osn,
824 l_ed,
825 l_dd,
826 l_bt,
827 l_cq,
828 l_risd,
829 l_cn,
830 l_impf,
831 l_oi,
832 l_fun,
833 l_tun,
834 l_str_type,
835 l_component_path,
836 l_component_item_revision_id ,
837 l_from_end_item_revision,
838 l_to_end_item_revision,
839 l_effectivity_control;
840 loop_Count_Val := imploder%rowcount ;
841
842 CLOSE imploder;
843 -- Loop through the values and check for cursors Check_Configured_Parent
844 -- and Check_Disabled_Parent. If Record is found then delete that
845 -- row from the pl/sql table
846
847
848 -- Need to do checkconfigure parent only if obj_nam is EGO.
849
850 For i in 1..loop_Count_Val Loop -- Check Loop
851 Begin
852 total_rows := total_rows + 1;
853 IF (cur_level = 0) THEN
854 l_LAD(i) := l_ABD(i);
855 END IF;
856 IF (cum_count = 0) THEN
857 prev_parent_item_id := l_ppk1(i);
861 cum_count := 0;
858 END IF;
859
860 IF (prev_parent_item_id <> l_ppk1(i)) THEN
862 prev_parent_item_id := l_ppk1(i);
863 END IF;
864
865 cum_count := cum_count + 1;
866
867 cat_sort := lpad(cum_count, 7, '0');
868 l_SC(i) := l_SC(i) || cat_sort;
869 --bug:4204847 Check for loops using component path stored
870 --check for loops by checking if the current parent exist in component path
871 l_cur_component_path := l_component_path(i);
872
873 IF l_lobj(i) = G_CAD_OBJ_NAME THEN
874 l_cur_component := LPAD('C'||l_apk1(i), 20, '0');
875 ELSE
876 l_cur_component := LPAD('I'||l_apk1(i), 20, '0');
877 END IF;
878
879 FOR j IN 1..(cur_level+1) LOOP
880 l_start_pos := 1+( (j-1) * 20 );
881 l_cur_substr := SUBSTR( l_cur_component_path, l_start_pos, 20 );
882 IF (l_cur_component = l_cur_substr) THEN
883 --loop found, raise exception
884 RAISE loop_found;
885 EXIT;
886 END IF;
887 END LOOP;
888
889 --update the component path for current parent
890 l_component_path(i) := l_cur_component || l_component_path(i);
891 End;
892 End Loop; -- End of Check Loop
893
894
895 --Loop to check if the record exist. If It exist then copy the record into
896 --an other table and insert the other table.
897 --This has to be done to avoid "ELEMENT DOES NOT EXIST exception"
898
899 -- Insert the Second table values using FORALL.
900
901 FORALL i IN 1..loop_Count_Val
902 INSERT INTO BOM_SMALL_IMPL_TEMP
903 (LOWEST_ITEM_ID,
904 CURRENT_ITEM_ID,
905 PARENT_ITEM_ID,
906 LOWEST_PK1_VALUE,
907 LOWEST_PK2_VALUE,
908 LOWEST_PK3_VALUE,
909 LOWEST_PK4_VALUE,
910 LOWEST_PK5_VALUE,
911 LOWEST_OBJ_NAME,
912 CURRENT_PK1_VALUE,
913 CURRENT_PK2_VALUE,
914 CURRENT_PK3_VALUE,
915 CURRENT_PK4_VALUE,
916 CURRENT_PK5_VALUE,
917 CURRENT_OBJ_NAME,
918 PARENT_PK1_VALUE,
919 PARENT_PK2_VALUE,
920 PARENT_PK3_VALUE,
921 PARENT_PK4_VALUE,
922 PARENT_PK5_VALUE,
923 PARENT_OBJ_NAME,
924 ALTERNATE_DESIGNATOR,
925 CURRENT_LEVEL,
926 SORT_CODE,
927 LOWEST_ALTERNATE_DESIGNATOR,
928 CURRENT_ASSEMBLY_TYPE,
929 SEQUENCE_ID,
930 COMPONENT_SEQUENCE_ID,
931 ORGANIZATION_ID,
932 REVISED_ITEM_SEQUENCE_ID,
933 CHANGE_NOTICE,
934 OPERATION_SEQ_NUM,
935 EFFECTIVITY_DATE,
936 DISABLE_DATE,
937 FROM_END_ITEM_UNIT_NUMBER,
938 TO_END_ITEM_UNIT_NUMBER,
939 BASIS_TYPE,
940 COMPONENT_QUANTITY,
941 IMPLEMENTED_FLAG,
942 LAST_UPDATE_DATE,
943 LAST_UPDATED_BY,
944 CREATION_DATE,
945 CREATED_BY,
946 PARENT_SORT_CODE,
947 IMPLOSION_DATE,
948 STRUCTURE_TYPE_ID,
949 ACCESS_FLAG,
950 COMPONENT_PATH ,
951 COMPONENT_ITEM_REVISION_ID,
952 FROM_END_ITEM_REVISION,
953 TO_END_ITEM_REVISION,
954 EFFECTIVITY_CONTROL
955 ) VALUES (
956 l_lpk1(i),
957 l_ppk1(i),
958 l_apk1(i),
959 l_lpk1(i),
960 l_lpk2(i),
961 l_lpk3(i),
962 l_lpk4(i),
963 l_lpk5(i),
964 l_lobj(i),
965 l_ppk1(i),
966 l_ppk2(i),
967 l_ppk3(i),
968 l_ppk4(i),
969 l_ppk5(i),
970 l_pobj(i),
971 l_apk1(i),
972 l_apk2(i),
973 l_apk3(i),
974 l_apk4(i),
975 l_apk5(i),
976 l_aobj(i),
977 l_abd(i),
978 cur_level + 1,
979 l_sc(i),
980 l_lad(i),
981 l_cat(i),
982 sequence_id,
983 l_csi(i),
984 l_oi(i),
985 l_risd(i),
986 l_cn(i),
987 l_osn(i),
988 l_ed(i),
989 l_dd(i),
990 l_fun(i),
991 l_tun(i),
992 l_bt(i),
993 l_cq(i),
994 l_impf(i),
995 sysdate,
996 -1,
1000 to_date(impl_date, 'YYYY/MM/DD HH24:MI:SS'),
997 sysdate,
998 -1,
999 decode(length(l_sc(i)), 7,null,substrb(l_sc(i),1,length(l_sc(i))-7)),
1001 l_str_type(i),
1002 --Check_User_View_priv(l_apk1(i),l_apk2(i)),
1003 'T',
1004 l_component_path(i) ,
1005 l_component_item_revision_id(i),
1006 l_from_end_item_revision(i),
1007 l_to_end_item_revision(i),
1008 l_effectivity_control(i)
1009 );
1010
1011 IF (total_rows <> 0) THEN
1012 cur_level := cur_level + 1;
1013 ELSE
1014 goto done_imploding;
1015 END IF;
1016
1017 END LOOP; /* while levels */
1018
1019
1020 <<done_imploding>>
1021
1022 /* Set the top item flag for which parent item row is not present in bom_small_impl_temp */
1023 UPDATE BOM_SMALL_IMPL_TEMP bsit_child
1024 SET
1025 bsit_child.TOP_ITEM_FLAG = 'Y'
1026 WHERE
1027 (
1028 ( levels_to_implode - 1 = bsit_child.CURRENT_LEVEL )
1029 OR
1030 (
1031 NOT EXISTS
1032 (
1033 SELECT 1
1034 FROM BOM_SMALL_IMPL_TEMP bsit_parent
1035 WHERE
1036 bsit_parent.CURRENT_ITEM_ID = bsit_child.PARENT_ITEM_ID
1037 AND bsit_parent.ORGANIZATION_ID = bsit_child.ORGANIZATION_ID
1038 AND bsit_parent.CURRENT_LEVEL = (bsit_child.CURRENT_LEVEL + 1)
1039 AND bsit_parent.SEQUENCE_ID = sequence_id
1040 )
1041 )
1042 )
1043 AND bsit_child.CURRENT_LEVEL > 0 -- top item page shows parents only
1044 AND bsit_child.SEQUENCE_ID = sequence_id;
1045
1046 /* Set the is_excluded_by_rule to 'Y' for parent item from which implosion item is excluded */
1047 UPDATE BOM_SMALL_IMPL_TEMP bsit_source
1048 SET IS_EXCLUDED_BY_RULE = 'Y'
1049 WHERE
1050 EXISTS
1051 (
1052 SELECT
1053 *
1054 FROM
1055 BOM_SMALL_IMPL_TEMP bsit,
1056 BOM_EXCLUSION_RULE_DEF berd,
1057 BOM_RULES_B brb
1058 WHERE
1059 (
1060 (
1061 berd.FROM_REVISION_ID IS NULL
1062 OR
1063 (
1064 (
1065 SELECT REVISION
1066 FROM MTL_ITEM_REVISIONS_B
1067 WHERE REVISION_ID = berd.FROM_REVISION_ID
1068 ) <=
1069 (
1070 SELECT
1071 mir.REVISION
1072 FROM
1073 MTL_ITEM_REVISIONS_B mir
1074 WHERE
1075 mir.EFFECTIVITY_DATE =
1076 (
1077 SELECT
1078 MAX(mir1.EFFECTIVITY_DATE)
1079 FROM
1080 MTL_ITEM_REVISIONS_B mir1
1081 WHERE
1082 mir1.EFFECTIVITY_DATE <= TO_DATE(impl_date, 'YYYY/MM/DD HH24:MI:SS')
1083 AND mir1.INVENTORY_ITEM_ID = bsit.PARENT_ITEM_ID
1084 AND mir1.ORGANIZATION_ID = bsit.ORGANIZATION_ID
1085 )
1086 AND mir.INVENTORY_ITEM_ID = bsit.PARENT_ITEM_ID
1087 AND mir.ORGANIZATION_ID = bsit.ORGANIZATION_ID
1088 )
1089 )
1090 )
1091 AND
1092 (
1093 berd.TO_REVISION_ID IS NULL
1094 OR
1095 (
1096 (
1097 SELECT
1098 mir.REVISION
1099 FROM
1100 MTL_ITEM_REVISIONS_B mir
1101 WHERE
1102 mir.EFFECTIVITY_DATE =
1103 (
1104 SELECT
1105 MAX(mir1.EFFECTIVITY_DATE)
1106 FROM
1107 MTL_ITEM_REVISIONS_B mir1
1108 WHERE
1109 mir1.EFFECTIVITY_DATE <= TO_DATE(impl_date, 'YYYY/MM/DD HH24:MI:SS')
1110 AND mir1.INVENTORY_ITEM_ID = bsit.PARENT_ITEM_ID
1111 AND mir1.ORGANIZATION_ID = bsit.ORGANIZATION_ID
1112 )
1113 AND mir.INVENTORY_ITEM_ID = bsit.PARENT_ITEM_ID
1114 AND mir.ORGANIZATION_ID = bsit.ORGANIZATION_ID
1115 ) <=
1116 (
1117 SELECT REVISION
1118 FROM MTL_ITEM_REVISIONS_B
1119 WHERE REVISION_ID = berd.TO_REVISION_ID
1120 )
1121 )
1122 )
1123 )
1124 AND
1125 (
1126 berd.IMPLEMENTATION_DATE IS NOT NULL
1127 AND berd.DISABLE_DATE IS NULL
1131 (
1128 /* Exclusion rule does not have effectivity associated, either it is applied or not applied
1129 AND berd.IMPLEMENTATION_DATE <= to_date(impl_date, 'YYYY/MM/DD HH24:MI:SS')
1130 AND
1132 berd.DISABLE_DATE IS NULL
1133 OR TO_DATE(impl_date, 'YYYY/MM/DD HH24:MI:SS') <= berd.DISABLE_DATE
1134 )
1135 */
1136 )
1137 AND berd.ACD_TYPE = 1
1138 AND bsit.COMPONENT_PATH LIKE berd.EXCLUSION_PATH || '%'
1139 AND brb.RULE_ID = berd.RULE_ID
1140 AND brb.BILL_SEQUENCE_ID = (SELECT BILL_SEQUENCE_ID FROM BOM_COMPONENTS_B WHERE COMPONENT_SEQUENCE_ID = bsit.COMPONENT_SEQUENCE_ID)
1141 AND bsit.COMPONENT_SEQUENCE_ID IS NOT NULL
1142 AND bsit.ROWID = bsit_source.ROWID
1143 )
1144 AND bsit_source.CURRENT_LEVEL > 0
1145 AND bsit_source.SEQUENCE_ID = sequence_id;
1146
1147 error_code := 0;
1148 err_msg := '';
1149 /*
1150 ** exception handlers
1151 */
1152 EXCEPTION
1153 WHEN max_extents THEN
1154 error_code := SQLCODE;
1155 err_msg := substrb(SQLERRM, 1, 80);
1156 --bug:4204847 If loop found then pass the current component path to get component loop string
1157 WHEN loop_found THEN
1158 error_code := SQLCODE;
1159 err_msg := l_cur_component || l_cur_component_path;
1160 WHEN OTHERS THEN
1161 error_code := SQLCODE;
1162 err_msg := substrb(SQLERRM, 1, 80);
1163 END ml_imploder_cad;
1164
1165
1166 /* This is an overloaded procedure that will narrow down the where used to the
1167 * provided structure type. It will simply call the existing imploder_userexit
1168 * without regard to structure type and then delete the rows from bom_mall_impl_temp
1169 * which do not conform to the user entered structure type.
1170 * One of the out parameters will indicate whether any structures of the reqd.
1171 * structure_type were found containing this item.
1172 * Extra parameters:
1173 * struct_type : structure type name
1174 * preferred_only : flag to check indicate only whether
1175 * implosion should be caried out only
1176 * for preferred structures.
1177 * 1 for true/ 2 for false
1178 * used_in_structure : Out parameter to indicate if any structures
1179 * of given structure type were found containing
1180 * this item.
1181 */
1182 PROCEDURE imploder_userexit(
1183 sequence_id IN NUMBER ,
1184 eng_mfg_flag IN NUMBER,
1185 org_id IN NUMBER,
1186 impl_flag IN NUMBER,
1187 display_option IN NUMBER,
1188 levels_to_implode IN NUMBER,
1189 obj_name IN VARCHAR2 DEFAULT 'EGO_ITEM',
1190 pk1_value IN VARCHAR2,
1191 pk2_value IN VARCHAR2,
1192 pk3_value IN VARCHAR2,
1193 pk4_value IN VARCHAR2,
1194 pk5_value IN VARCHAR2,
1195 impl_date IN VARCHAR2,
1196 unit_number_from IN VARCHAR2,
1197 unit_number_to IN VARCHAR2,
1198 err_msg OUT NOCOPY VARCHAR2,
1199 err_code OUT NOCOPY NUMBER,
1200 organization_option IN NUMBER default 1,
1201 organization_hierarchy IN VARCHAR2 default NULL,
1202 serial_number_from IN VARCHAR2 default NULL,
1203 serial_number_to IN VARCHAR2 default NULL,
1204 struct_name IN VARCHAR2 DEFAULT FND_LOAD_UTIL.NULL_VALUE,
1205 struct_type IN VARCHAR2,
1206 preferred_only IN NUMBER DEFAULT 2,
1207 used_in_structure OUT NOCOPY VARCHAR2
1208 )
1209 IS
1210 BEGIN
1211 imploder_userexit(
1212 sequence_id => sequence_id,
1213 eng_mfg_flag => eng_mfg_flag,
1214 org_id => org_id,
1215 impl_flag => impl_flag,
1216 display_option => display_option,
1217 levels_to_implode => levels_to_implode,
1218 obj_name => obj_name,
1219 pk1_value => pk1_value,
1220 pk2_value => pk2_value,
1221 pk3_value => pk3_value,
1222 pk4_value => pk4_value,
1223 pk5_value => pk5_value,
1224 impl_date => impl_date,
1225 unit_number_from => unit_number_from,
1226 unit_number_to => unit_number_to,
1227 err_msg => err_msg,
1228 err_code => err_code,
1229 organization_option => organization_option,
1230 organization_hierarchy => organization_hierarchy,
1231 serial_number_from => serial_number_from,
1232 serial_number_to => serial_number_to,
1233 struct_name => struct_name,
1234 struct_type => struct_type,
1235 preferred_only => preferred_only,
1236 used_in_structure => used_in_structure,
1237 revision => NULL
1238 );
1239 END imploder_userexit;
1240
1241
1242 /*
1243 * Overloaded procedure to take revision of component to search in first level
1244 * parent
1245 */
1246 PROCEDURE imploder_userexit(
1247 sequence_id IN NUMBER,
1248 eng_mfg_flag IN NUMBER,
1249 org_id IN NUMBER,
1250 impl_flag IN NUMBER,
1251 display_option IN NUMBER,
1252 levels_to_implode IN NUMBER,
1253 obj_name IN VARCHAR2 DEFAULT 'EGO_ITEM',
1254 pk1_value IN VARCHAR2,
1258 pk5_value IN VARCHAR2,
1255 pk2_value IN VARCHAR2,
1256 pk3_value IN VARCHAR2,
1257 pk4_value IN VARCHAR2,
1259 impl_date IN VARCHAR2,
1260 unit_number_from IN VARCHAR2,
1261 unit_number_to IN VARCHAR2,
1262 err_msg OUT NOCOPY VARCHAR2,
1263 err_code OUT NOCOPY NUMBER,
1264 organization_option IN NUMBER default 1,
1265 organization_hierarchy IN VARCHAR2 default NULL,
1266 serial_number_from IN VARCHAR2 default NULL,
1267 serial_number_to IN VARCHAR2 default NULL,
1268 struct_name IN VARCHAR2 DEFAULT FND_LOAD_UTIL.NULL_VALUE,
1269 revision IN VARCHAR2
1270 )
1271 AS
1272 a_err_msg VARCHAR2(2000); --bug:4204847 Increasing the length so that component path can be
1273 --returned in case of loop
1274 a_err_code NUMBER;
1275 t_org_code_list INV_OrgHierarchy_PVT.OrgID_tbl_type;
1276 N NUMBER:=0;
1277 dummy NUMBER;
1278 l_org_name VARCHAR2(60);
1279 item_found BOOLEAN:=TRUE;
1280
1281 l_parents_for_pk1 NUMBER := 0;
1282 l_seq_id NUMBER;
1283 l_preferred_structure_name VARCHAR2(10);
1284
1285 l_person VARCHAR2(30);
1286 l_predicate VARCHAR2(32767);
1287 l_predicate_api_status VARCHAR2(1);
1288
1289 BEGIN
1290 --DBMS_PROFILER.START_PROFILER(sequence_id);
1291 l_seq_id := sequence_id;
1292 /* If the parameter :
1293 Organization_Option = 1 then
1294 Take the current Organization
1295 else If Organization_Option = 2 is passed then
1296 Call the Inventory API to get the list of Organizations
1297 under the current Organization Hierarchy
1298 else if Organization Option = 3 is passed then
1299 Find the list of all the Organizations to which
1300 access is allowed */
1301
1302
1303 if ( organization_option =2 ) then
1304 INV_ORGHIERARCHY_PVT.ORG_HIERARCHY_LIST(organization_hierarchy ,
1305 org_id ,t_org_code_list );
1306
1307 elsif ( organization_option = 3 ) then
1308
1309 If (OBJ_NAME = 'EGO_ITEM' OR OBJ_NAME IS NULL) then
1310 -- bug:4931463 Re-written following query to reduce shared memory.
1311 for C1 in (
1312 SELECT
1313 orgs.ORGANIZATION_ID
1314 FROM
1315 ORG_ACCESS_VIEW oav,
1316 MTL_SYSTEM_ITEMS_B msi,
1317 MTL_PARAMETERS orgs,
1318 MTL_PARAMETERS child_org
1319 WHERE
1320 orgs.ORGANIZATION_ID = oav.ORGANIZATION_ID
1321 AND msi.ORGANIZATION_ID = orgs.ORGANIZATION_ID
1322 AND orgs.MASTER_ORGANIZATION_ID = child_org.MASTER_ORGANIZATION_ID
1323 AND oav.RESPONSIBILITY_ID = FND_PROFILE.Value('RESP_ID')
1324 AND oav.RESP_APPLICATION_ID = FND_PROFILE.value('RESP_APPL_ID')
1325 AND msi.INVENTORY_ITEM_ID = pk1_value
1326 AND child_org.ORGANIZATION_ID = org_id
1327 )
1328 LOOP
1329 N:=N+1;
1330 t_org_code_list(N) := C1.organization_id;
1331 END LOOP;
1332 end if;
1333 elsif
1334 ( organization_option = 1 ) then
1335 t_org_code_list(1) := org_id;
1336 end if;
1337
1338 FOR I in t_org_code_list.FIRST..t_org_code_list.LAST LOOP
1339
1340 --if ( organization_option = 2 or organization_option = 3 ) THEN
1341 -- We do not need to check if Item exists for org option 3 as
1342 -- this is already being done in the above if cond's.
1343
1344 if ( organization_option = 2 ) THEN
1345
1346 /*Check the existence of the Item in the curent Organization,
1347 if found then call the Imploder API for the Organization,otherwise
1348 check the existence of the Item in the next Organization of the
1349 Organization List*/
1350
1351 select count(*)
1352 into dummy
1353 from mtl_system_items
1354 where organization_id = t_org_code_list(I)
1355 and inventory_item_id = pk1_value;
1356
1357 if dummy <1 then
1358 item_found := FALSE;
1359 end if;
1360 end if;
1361
1362
1363 --dbms_output.put_line('trying to insert ');
1364 --bug:4204847 Store the component path with each parent with respect to item to be imploded.
1365 --bug:4218468 Store the component revision id to get the revision label in View
1366 if item_found then
1367 INSERT INTO BOM_SMALL_IMPL_TEMP
1368 (SEQUENCE_ID,
1369 LOWEST_ITEM_ID,
1370 CURRENT_ITEM_ID,
1371 PARENT_ITEM_ID,
1372 ALTERNATE_DESIGNATOR,
1373 CURRENT_LEVEL,
1374 SORT_CODE,
1375 CURRENT_ASSEMBLY_TYPE,
1376 COMPONENT_SEQUENCE_ID,
1377 ORGANIZATION_ID,
1378 LAST_UPDATE_DATE,
1379 LAST_UPDATED_BY,
1380 CREATION_DATE,
1381 CREATED_BY,
1382 current_pk1_value,
1383 current_pk2_value,
1384 current_pk3_value,
1385 current_pk4_value,
1386 current_pk5_value,
1387 current_obj_name,
1388 parent_pk1_value,
1389 parent_pk2_value,
1390 parent_pk3_value,
1391 parent_pk4_value,
1392 parent_pk5_value,
1393 parent_obj_name,
1394 lowest_pk1_value,
1395 lowest_pk2_value,
1396 lowest_pk3_value,
1397 lowest_pk4_value,
1398 lowest_pk5_value,
1399 lowest_obj_name,
1403 lowest_trade_item_descriptor,
1400 implosion_date ,
1401 lowest_gtin_number,
1402 lowest_gtin_description,
1404 current_gtin_number,
1405 current_gtin_description,
1406 current_trade_item_descriptor,
1407 parent_gtin_number,
1408 parent_gtin_description,
1409 parent_trade_item_descriptor,
1410 primary_uom_descriptor,
1411 GTIN_PUBLICATION_STATUS,
1412 access_flag,
1413 COMPONENT_PATH,
1414 COMPONENT_ITEM_REVISION_ID,
1415 FROM_END_ITEM_REVISION,
1416 TO_END_ITEM_REVISION,
1417 EFFECTIVITY_CONTROL,
1418 BASIS_TYPE
1419 ) VALUES (
1420 sequence_id,
1421 decode(obj_name,'EGO_ITEM',pk1_value,NULL),
1422 decode(obj_name,'EGO_ITEM',pk1_value,NULL),
1423 decode(obj_name,'EGO_ITEM',pk1_value,NULL),
1424 NULL,
1425 0,
1426 '0000001',
1427 NULL,
1428 NULL,
1429 t_org_code_list(I),
1430 sysdate,
1431 -1,
1432 sysdate,
1433 -1,
1434 pk1_value,
1435 decode(obj_name,'EGO_ITEM',t_org_code_list(I),NULL,t_org_code_list(I),pk2_value), --pk2_value,
1436 pk3_value,
1437 pk4_value,
1438 pk5_value,
1439 nvl(obj_name,G_EGO_OBJ_NAME),
1440 pk1_value,
1441 decode(obj_name,'EGO_ITEM',t_org_code_list(I),NULL,t_org_code_list(I),pk2_value), --pk2_value,
1442 pk3_value,
1443 pk4_value,
1444 pk5_value,
1445 nvl(obj_name,G_EGO_OBJ_NAME),
1446 pk1_value,
1447 decode(obj_name,'EGO_ITEM',t_org_code_list(I),NULL,t_org_code_list(I),pk2_value), --pk2_value,
1448 pk3_value,
1449 pk4_value,
1450 pk5_value,
1451 nvl(obj_name,G_EGO_OBJ_NAME),
1452 to_date(impl_date, 'YYYY/MM/DD HH24:MI:SS'),
1453 NULL,--l_gtin,
1454 NULL,--l_gtin_description,
1455 NULL,--l_gtin_trade_item_descriptor,
1456 NULL,--l_gtin,
1457 NULL,--l_gtin_description,
1458 NULL,--l_gtin_trade_item_descriptor,
1459 NULL,--l_gtin,
1460 NULL,--l_gtin_description,
1461 NULL,--l_gtin_trade_item_descriptor,
1462 NULL,--l_primary_uom_desc,
1463 NULL,--l_gtin_publication_status
1464 Check_User_View_priv(pk1_value,pk2_value)--'T'
1465 ,lpad( decode(obj_name,G_CAD_OBJ_NAME,'C','I') || pk1_value, 20, '0')
1466 ,NULL --bug:4218468 For header row, insert NULL
1467 ,NULL
1468 ,NULL
1469 ,NULL
1470 ,-1 -- set the basis type to -1 for displaying blank value for header row
1471 );
1472 bom_imploder_pub.implosion_cad(sequence_id,eng_mfg_flag,t_org_code_list(I),
1473 impl_flag, display_option, levels_to_implode, impl_date,
1474 unit_number_from, unit_number_to,
1475 a_err_msg, a_err_code, serial_number_from, serial_number_to,
1476 struct_name, revision);
1477
1478 err_msg := a_err_msg;
1479 err_code := a_err_code;
1480 end if;
1481 item_found := TRUE;
1482
1483 /*BEGIN
1484 IF (structure_type_id IS NOT NULL)
1485 THEN
1486
1487 SELECT COUNT(COMPONENT_SEQUENCE_ID) INTO l_parents_for_pk1
1488 from BOM_SMALL_IMPL_TEMP
1489 WHERE
1490 LOWEST_ITEM_ID = pk1_value AND ORGANIZATION_ID = t_org_code_list(I)
1491 AND CURRENT_LEVEL = 1 AND SEQUENCE_ID = l_seq_id;
1492
1493 BEGIN
1494 IF (l_parents_for_pk1 = 0)
1495 THEN
1496 SELECT ALTERNATE_BOM_DESIGNATOR INTO l_preferred_structure_name
1497 FROM BOM_STRUCTURES_B
1498 WHERE ASSEMBLY_ITEM_ID = pk1_value
1499 AND ORGANIZATION_ID = pk2_value
1500 AND STRUCTURE_TYPE_ID = structure_type_id
1501 AND IS_PREFERRED = 'Y';
1502
1503 UPDATE BOM_SMALL_IMPL_TEMP
1504 SET TOP_ITEM_FLAG ='Y',
1505 ALTERNATE_DESIGNATOR = l_preferred_structure_name,
1506 STRUCTURE_TYPE_ID = structure_type_id
1507 WHERE CURRENT_LEVEL = 0 AND SEQUENCE_ID = l_seq_id
1508 AND LOWEST_ITEM_ID = pk1_value AND ORGANIZATION_ID = t_org_code_list(I) ;
1509
1510 END IF;
1511 EXCEPTION
1512 WHEN OTHERS THEN
1513 l_preferred_structure_name := NULL;
1514 ROLLBACK;
1515 END;
1516 END IF;
1517 END; */
1518
1519 end loop;
1520 if (a_err_code <> 0) then
1521 ROLLBACK;
1522 end if;
1523
1524 /* Get the security predicate */
1525
1526 SELECT 'HZ_PARTY'||':'||person_party_id INTO l_person
1527 FROM fnd_user WHERE user_name = FND_Global.User_Name;
1528
1529 EGO_DATA_SECURITY.get_security_predicate(
1530 p_api_version =>1.0,
1531 p_function =>'EGO_VIEW_ITEM',
1532 p_object_name =>'EGO_ITEM',
1533 p_user_name => l_person,
1534 p_statement_type =>'EXISTS',
1535 p_pk1_alias =>'BI.PARENT_PK1_VALUE',
1536 p_pk2_alias =>'BI.ORGANIZATION_ID',
1537 p_pk3_alias =>NULL,
1538 p_pk4_alias =>NULL,
1539 p_pk5_alias =>NULL,
1540 x_predicate => l_predicate,
1541 x_return_status => l_predicate_api_status);
1542
1546 END IF;
1543 IF l_predicate_api_status <> 'T'
1544 THEN
1545 Raise NO_DATA_FOUND;
1547
1548 IF l_predicate IS NOT NULL
1549 THEN
1550
1551 EXECUTE IMMEDIATE 'UPDATE bom_small_impl_temp BI SET BI.access_flag = ''F'' WHERE NOT '|| l_predicate;
1552
1553 END IF;
1554
1555 --DBMS_PROFILER.STOP_PROFILER;
1556
1557 -- R12C: Remove the rows for normal implosion results when the structure name <> 'PIM_PBOM_S'
1558 -- For packs, below procedure is called. So it will delete the rows.
1559
1560 IF ( struct_name <> 'PIM_PBOM_S' )
1561 THEN
1562 -- Normal implosion without packs
1563 DELETE
1564 FROM BOM_SMALL_IMPL_TEMP
1565 WHERE
1566 ALTERNATE_DESIGNATOR = 'PIM_PBOM_S'
1567 AND SEQUENCE_ID = sequence_id;
1568
1569 END IF;
1570
1571
1572 EXCEPTION
1573 WHEN OTHERS THEN
1574 err_msg := substrb(SQLERRM, 1, 80);
1575 err_code := SQLCODE;
1576 ROLLBACK;
1577 END imploder_userexit;
1578
1579 /*
1580 * Overloaded procedure to take revision of component to search in first level
1581 * parent. This is an overloaded procedure that will narrow down the where used to the
1582 * provided structure type.
1583 */
1584 PROCEDURE imploder_userexit(
1585 sequence_id IN NUMBER ,
1586 eng_mfg_flag IN NUMBER,
1587 org_id IN NUMBER,
1588 impl_flag IN NUMBER,
1589 display_option IN NUMBER,
1590 levels_to_implode IN NUMBER,
1591 obj_name IN VARCHAR2 DEFAULT 'EGO_ITEM',
1592 pk1_value IN VARCHAR2,
1593 pk2_value IN VARCHAR2,
1594 pk3_value IN VARCHAR2,
1595 pk4_value IN VARCHAR2,
1596 pk5_value IN VARCHAR2,
1597 impl_date IN VARCHAR2,
1598 unit_number_from IN VARCHAR2,
1599 unit_number_to IN VARCHAR2,
1600 err_msg OUT NOCOPY VARCHAR2,
1601 err_code OUT NOCOPY NUMBER,
1602 organization_option IN NUMBER default 1,
1603 organization_hierarchy IN VARCHAR2 default NULL,
1604 serial_number_from IN VARCHAR2 default NULL,
1605 serial_number_to IN VARCHAR2 default NULL,
1606 struct_name IN VARCHAR2 DEFAULT FND_LOAD_UTIL.NULL_VALUE,
1607 struct_type IN VARCHAR2,
1608 preferred_only IN NUMBER DEFAULT 2,
1609 used_in_structure OUT NOCOPY VARCHAR2,
1610 revision IN VARCHAR2
1611 )
1612 IS
1613 l_str_type_id NUMBER;
1614 l_row_count NUMBER;
1615 l_sequence_id NUMBER;
1616 l_structure_count NUMBER := 0;
1617 l_pk1_value VARCHAR2(100);
1618 BEGIN
1619 IF sequence_id is null then
1620 SELECT BOM_IMPLOSION_TEMP_S.nextval
1621 INTO l_sequence_id
1622 FROM SYS.DUAL;
1623 else
1624 l_sequence_id := sequence_id;
1625 end if;
1626 --first populate the temp table.
1627 imploder_userexit(
1628 sequence_id => l_sequence_id ,
1629 eng_mfg_flag => eng_mfg_flag ,
1630 org_id => org_id ,
1631 impl_flag => impl_flag ,
1632 display_option => display_option,
1633 levels_to_implode => levels_to_implode ,
1634 obj_name => obj_name ,
1635 pk1_value => pk1_value ,
1636 pk2_value => pk2_value ,
1637 pk3_value => pk3_value ,
1638 pk4_value => pk4_value ,
1639 pk5_value => pk5_value ,
1640 impl_date => impl_date ,
1641 unit_number_from => unit_number_from ,
1642 unit_number_to => unit_number_to,
1643 err_msg => err_msg ,
1644 err_code => err_code,
1645 organization_option => organization_option ,
1646 organization_hierarchy => organization_hierarchy ,
1647 serial_number_from => serial_number_from ,
1648 serial_number_to => serial_number_to ,
1649 struct_name => struct_name,
1650 revision => revision);
1651
1652 l_pk1_value := pk1_value;
1653 --Find the structure type id for the struct_type
1654 select structure_type_id into l_str_type_id
1655 from bom_structure_types_b
1656 where structure_type_name = struct_type;
1657
1658 --Delete those records from bom_small_impl_temp
1659 --which do not have the structure type id
1660 --user wants
1661
1662 IF ( preferred_only = 2) THEN
1663 DELETE FROM BOM_SMALL_IMPL_TEMP
1664 WHERE STRUCTURE_TYPE_ID <> l_str_type_id;
1665 ELSE
1666 /* Look only for preferred structures */
1667 DELETE FROM BOM_SMALL_IMPL_TEMP
1668 WHERE STRUCTURE_TYPE_ID <> l_str_type_id
1669 OR
1670 (STRUCTURE_TYPE_ID = l_str_type_id
1671 AND not exists
1672 (
1673 SELECT 1 from bom_structures_b
1674 where assembly_item_id = l_pk1_value
1675 and organization_id = org_id
1676 and structure_type_id = l_str_type_id
1677 and is_preferred = 'Y'
1678 )
1679 );
1680 END IF;
1681
1682 --Check if any records apart from the item record exist.
1683 SELECT count(*) into l_row_count
1684 from bom_small_impl_temp
1685 where SEQUENCE_ID = l_sequence_id;
1686 --Check if the item itself has a structure header of given str type defined
1687 --Fix for Bug 5943195
1688 --If an item has structure header only without components , then it won't
1689 --be canddidate as part of packaging heirrarchy.
1690
1691 l_structure_count := 0;
1692 begin
1693 SELECT 1 into l_structure_count
1694 FROM BOM_STRUCTURES_B BST1
1698 AND exists
1695 WHERE BST1.ASSEMBLY_ITEM_ID = l_pk1_value
1696 AND BST1.ORGANIZATION_ID = org_id
1697 AND BST1.STRUCTURE_TYPE_ID = l_str_type_id
1699 (
1700 SELECT 1 FROM BOM_COMPONENTS_B CPT1 WHERE CPT1.bill_sequence_id = BST1.bill_sequence_id
1701 AND CPT1.Disable_date IS NULL
1702 )
1703 AND rownum = 1;
1704 exception when no_data_found then
1705 l_structure_count := 0;
1706 end;
1707 IF l_row_count < 2 AND l_structure_count = 0 THEN
1708 used_in_structure := 'F';
1709 ELSE
1710 used_in_structure := 'T';
1711 END IF;
1712 END imploder_userexit;
1713
1714 END bom_imploder_pub;