[Home] [Help]
PACKAGE BODY: APPS.BOMPCCLT
Source
1 PACKAGE BODY BOMPCCLT AS
2 /* $Header: BOMCCLTB.pls 120.2 2006/02/16 04:24:22 abbhardw ship $ */
3 /*-----------------------------------------------------------------------------
4 -------------------------------------------------------------------------------
5 Copyright (c) 1993 Oracle Corporation Belmont, California, USA
6 All rights reserved.
7 -------------------------------------------------------------------------------
8
9 File Name : BOMCCLTB.pls
10 DESCRIPTION : This file is a packaged procedure for lead time rollup.
11 This package contains 4 procedures. Procedure explode_assy
12 explodes a given assembly into its components. Procedure
13 set_comp_lt computes cumulative lead times for all
14 purchased items. Procedure process_items computes cum lead
15 times for a given bom level. Procedure update_lt updates
16 mtl_system_items with cumulative lead time values.
17 Required Tables :
18 BOM_PARAMETERS
19 BOM_INVENTORY_COMPONENTS
20 BOM_BILL_OF_MATERIALS
21 BOM_LOW_LEVEL_CODES
22 MTL_SYSTEM_ITEMS
23 History
24 20-Feb-1996 Manu Chadha
25 -Added the MTL.ORGANIZATION_ID = org_id; line to to
26 update_lc as a fix for bug#343531
27 02-Oct-1997 Rob Yee
28 Streamline for performance by using
29 recursion for explosion and updating leadtimes
30 in mtl_system_items directly
31 21-Aug-1998 Mani
32 Added unit number and changed SQL statements
33 to implement Serial Effectivity.
34 13-May-2004 Rahul Chitko
35 -Added alternate_bom_code parameter to be able to
36 perform rollup for specified alternate.
37 -----------------------------------------------------------------------------*/
38 Type StackTabType is table of number index by binary_integer;
39 G_CommitRows constant number := 1000; -- frequency of commits
40
41 Procedure explode_next_level(
42 p_item_id IN NUMBER,
43 p_org_id IN NUMBER,
44 p_prgm_id IN NUMBER,
45 p_prgm_app_id IN NUMBER,
46 p_req_id IN NUMBER,
47 p_roll_id IN NUMBER,
48 p_unit_number IN VARCHAR2,
49 p_eff_date IN DATE,
50 p_max_level IN NUMBER DEFAULT 60,
51 p_alternate_bom_code IN VARCHAR2 DEFAULT NULL,
52 p_Path IN OUT NOCOPY StackTabType,
53 p_Level IN OUT NOCOPY binary_integer,
54 x_LoopFound IN OUT NOCOPY /* file.sql.39 change */ BOOLEAN,
55 x_err_msg IN OUT NOCOPY /* file.sql.39 change */ VARCHAR2) is
56
57 l_LoopFound boolean := false;
58 l_err_msg varchar2(2000) := null;
59 l_FatalError exception;
60 Cursor l_comps_csr (p_org_id number, p_item_id number, p_unit_number varchar2,
61 p_eff_date date) is
62 SELECT COM.COMPONENT_ITEM_ID
63 FROM MTL_SYSTEM_ITEMS MTL2,
64 BOM_INVENTORY_COMPONENTS COM,
65 MTL_SYSTEM_ITEMS MTL1,
66 BOM_BILL_OF_MATERIALS BOM
67 WHERE NVL(BOM.ALTERNATE_BOM_DESIGNATOR,'XXXXXXXXXXX') =
68 NVL(p_alternate_bom_code,'XXXXXXXXXXX')
69 AND COM.BILL_SEQUENCE_ID = BOM.COMMON_BILL_SEQUENCE_ID
70 AND BOM.ORGANIZATION_ID = p_org_id
71 AND BOM.ASSEMBLY_ITEM_ID = p_item_id
72 AND MTL1.INVENTORY_ITEM_ID = BOM.ASSEMBLY_ITEM_ID
73 AND MTL1.ORGANIZATION_ID = BOM.ORGANIZATION_ID
74 AND MTL2.INVENTORY_ITEM_ID = COM.COMPONENT_ITEM_ID
75 AND MTL2.ORGANIZATION_ID = BOM.ORGANIZATION_ID
76 AND COM.IMPLEMENTATION_DATE IS NOT NULL
77 AND NVL(COM.ECO_FOR_PRODUCTION,2) = 2
78 AND NOT (mtl1.replenish_to_order_flag = 'Y'
79 AND mtl1.bom_item_type = 4
80 AND mtl1.base_item_id IS NOT NULL
81 AND MTL2.BOM_ITEM_TYPE IN (1,2))
82 AND (
83 COM.DISABLE_DATE IS NULL
84 OR
85 COM.DISABLE_DATE > p_eff_date
86 )
87 AND ((MTL1.EFFECTIVITY_CONTROL <> 1
88 AND p_unit_number is NOT NULL
89 AND COM.DISABLE_DATE IS NULL
90 AND p_unit_number BETWEEN COM.FROM_END_ITEM_UNIT_NUMBER AND
91 NVL(COM.TO_END_ITEM_UNIT_NUMBER, p_unit_number))
92 OR (MTL1.EFFECTIVITY_CONTROL = 1
93 AND COM.EFFECTIVITY_DATE <= p_eff_date));
94
95 cursor l_item_csr(P_ItemId number, P_OrgId number) is
96 Select item_number
97 From mtl_item_flexfields
98 Where item_id = P_ItemId
99 And organization_id = P_OrgId;
100 l_stmt varchar2(3);
101 Begin
102 l_LoopFound := false;
103 l_err_msg := null;
104 l_stmt := '1';
105 For l_comps_rec in l_comps_csr(
106 p_org_id => p_org_id,
107 p_item_id => p_item_id,
108 p_unit_number => p_unit_number,
109 p_eff_date => p_eff_date) loop
110 l_stmt := '2';
111 For i in 0..p_level loop -- loop check
112 If p_path(i) = l_comps_rec.component_item_id then
113 l_LoopFound := true;
114 End if;
115 End loop;
116 p_level := p_level + 1;
117 p_path(p_level) := l_comps_rec.component_item_id;
118 If (not l_LoopFound) and (p_level < p_max_level) then
119 explode_next_level(
120 p_item_id => l_comps_rec.component_item_id,
121 p_org_id => p_org_id,
122 p_prgm_id => p_prgm_id,
123 p_prgm_app_id => p_prgm_app_id,
124 p_req_id => p_req_id,
125 p_roll_id => p_roll_id,
126 p_unit_number => p_unit_number,
127 p_eff_date => p_eff_date,
128 p_max_level => p_max_level,
129 p_path => p_path,
130 p_level => p_level,
131 p_alternate_bom_code => p_alternate_bom_code,
132 x_LoopFound => l_LoopFound,
133 x_err_msg => l_err_msg);
134 End if; -- recursion
135 If l_LoopFound then
136 l_stmt := '3';
137 For l_item_rec in l_item_csr(
138 P_ItemId => l_comps_rec.component_item_id,
139 P_OrgId => p_org_id) loop
140 l_err_msg := '-->'||l_item_rec.item_number||l_err_msg;
141 End loop; -- loop string
142 Exit;
143 Elsif l_err_msg is not null then
144 Raise l_FatalError;
145 Else
146 l_stmt := '4';
147 Update bom_low_level_codes
148 Set low_level_code = p_level,
149 program_update_date = sysdate
150 Where rollup_id = p_roll_id
151 And inventory_item_id = l_comps_rec.component_item_id
152 And low_level_code < p_level;
153 l_stmt := '5';
154 Insert into bom_low_level_codes(
155 rollup_id,
156 inventory_item_id,
157 low_level_code,
158 request_id,
159 program_application_id,
160 program_id,
161 program_update_date)
162 Select
163 p_roll_id,
164 l_comps_rec.component_item_id,
165 p_level,
166 p_req_id,
167 p_prgm_app_id,
168 p_prgm_id,
169 sysdate
170 From dual
171 Where not exists(
172 Select null
173 From bom_low_level_codes
174 Where rollup_id = p_roll_id
175 And inventory_item_id = l_comps_rec.component_item_id
176 And low_level_code >= p_level);
177 End if;
178 If mod(l_comps_csr%rowcount, G_CommitRows) = 0 then
179 --Commit; -- conserve rollback segments
180 null;
181 End if;
182 p_level := p_level - 1;
183 End loop; -- components
184 --Commit;
185 x_LoopFound := l_LoopFound;
186 x_err_msg := l_err_msg;
187 Exception
188 When l_FatalError then
189 x_LoopFound := false;
190 x_err_msg := l_err_msg;
191 When others then
192 x_LoopFound := false;
193 FND_MSG_PUB.Build_Exc_Msg(
194 p_pkg_name => 'BOMPCCLT',
195 p_procedure_name => 'explode_next_level('||l_stmt||')');
196 x_err_msg := Fnd_Message.Get_Encoded;
197 End explode_next_level;
198
199 Procedure explode_assy(
200 org_id IN NUMBER,
201 prgm_id IN NUMBER,
202 prgm_app_id IN NUMBER,
203 req_id IN NUMBER,
204 roll_id IN NUMBER,
205 unit_number IN VARCHAR2,
206 eff_date IN DATE,
207 alternate_bom_code IN VARCHAR2,
208 loop_found IN OUT NOCOPY /* file.sql.39 change */ BOOLEAN,
209 err_msg IN OUT NOCOPY /* file.sql.39 change */ VARCHAR2) IS
210
211 l_AbsoluteMaxLevel constant number := 60;
212 cursor l_parameter_csr is
213 SELECT MAXIMUM_BOM_LEVEL
214 FROM BOM_PARAMETERS
215 WHERE ORGANIZATION_ID = org_id;
216 max_bom_level NUMBER;
217 l_stmt_num varchar2(3);
218 cursor l_list_csr is
219 Select bl.assembly_item_id,
220 bl.conc_flex_string
221 From bom_lists bl, mtl_system_items msi
222 Where bl.sequence_id = roll_id
223 and msi.organization_id = org_id
224 and msi.inventory_item_id = bl.assembly_item_id
225 and (unit_number is NOT NULL
226 or (unit_number is NULL and msi.effectivity_control = 1));
227 l_LoopFound boolean := false;
228 l_err_msg varchar2(2000) := null;
229 l_FatalError exception;
230 l_path StackTabType;
231 l_level binary_integer := 0;
232 BEGIN
233 -- maximum levels that a BOM can be exploded
234 max_bom_level := l_AbsoluteMaxLevel;
235 l_stmt_num := '1';
236 For l_parameter_rec in l_parameter_csr loop
237 max_bom_level := l_parameter_rec.MAXIMUM_BOM_LEVEL;
238 End loop;
239 If max_bom_level is NULL then
240 max_bom_level := l_AbsoluteMaxLevel;
241 End if;
242
243 -- explode the assemblies in list and insert into BOM_LOW_LEVEL_CODES
244
245 --bom_debug('starting walk down of ' || alternate_bom_code);
246
247 l_LoopFound := false;
248 l_err_msg := null;
249 For l_bill_rec in l_list_csr loop
250 l_level := 0;
251 l_path(l_level) := l_bill_rec.assembly_item_id;
252 explode_next_level(
253 p_item_id => l_bill_rec.assembly_item_id,
254 p_org_id => org_id,
255 p_prgm_id => prgm_id,
256 p_prgm_app_id => prgm_app_id,
257 p_req_id => req_id,
258 p_roll_id => roll_id,
259 p_unit_number => unit_number,
260 p_eff_date => eff_date,
261 p_max_level => max_bom_level,
262 p_path => l_path,
263 p_level => l_level,
264 p_alternate_bom_code => alternate_bom_code,
265 x_LoopFound => l_LoopFound,
266 x_err_msg => l_err_msg);
267 If l_LoopFound then
268 l_stmt_num := '2';
269 l_err_msg := l_bill_rec.conc_flex_string||l_err_msg;
270 Exit;
271 Elsif l_err_msg is not null then
272 Raise l_FatalError;
273 Else
274 l_stmt_num := '3';
275 Insert into bom_low_level_codes(
276 rollup_id,
277 inventory_item_id,
278 low_level_code,
279 request_id,
280 program_application_id,
281 program_id,
282 program_update_date)
283 Select
284 roll_id,
285 l_bill_rec.assembly_item_id,
286 0,
287 req_id,
288 prgm_app_id,
289 prgm_id,
290 sysdate
291 From dual
292 Where not exists(
293 Select null
294 From bom_low_level_codes
295 Where rollup_id = roll_id
296 And inventory_item_id = l_bill_rec.assembly_item_id
297 And low_level_code >= 0);
298 End if;
299 If mod(l_list_csr%rowcount, G_CommitRows) = 0 then
300 --Commit; -- conserve rollback segments
301 null;
302 End if;
303 End loop; -- components
304 --Commit;
305 loop_found := l_LoopFound;
306 err_msg := l_err_msg;
307 EXCEPTION
308 When l_FatalError then
309 loop_found := false;
310 err_msg := l_err_msg;
311 WHEN OTHERS THEN
312 loop_found := false;
313 FND_MSG_PUB.Build_Exc_Msg(
314 p_pkg_name => 'BOMPCCLT',
315 p_procedure_name => 'explode_assy('||l_stmt_num||')');
316 err_msg := Fnd_Message.Get_Encoded;
317 END explode_assy;
318
319 PROCEDURE update_lt(
320 org_id IN NUMBER,
321 roll_id IN NUMBER,
322 prgm_id IN NUMBER,
323 prgm_app_id IN NUMBER,
324 req_id IN NUMBER,
325 unit_number IN VARCHAR2,
326 rev_date IN DATE,
327 err_msg IN OUT NOCOPY /* file.sql.39 change */ VARCHAR2) IS
328
329 X_include_in_rollup varchar2(1) := 'N';
330 X_include_models varchar2(1) := 'N';
331
332 cursor l_LowLevelCode_csr is
333 Select nvl(max(low_level_code), -1) depth
334 From bom_low_level_codes
335 Where rollup_id = roll_id;
336 l_depth number;
337
338 CURSOR l_assy_csr(p_level number) is
339 select MTL.ROWID row_id,
340 MTL.INVENTORY_ITEM_ID,
341 NVL(MTL.PREPROCESSING_LEAD_TIME, 0) +
342 NVL(MTL.POSTPROCESSING_LEAD_TIME, 0) +
343 NVL(MTL.FULL_LEAD_TIME, 0) TOTAL_LEAD_TIME,
344 DECODE(MTL.PLANNING_MAKE_BUY_CODE,
345 2, 0,
346 NVL(MTL.FULL_LEAD_TIME, 0)) FULL_LEAD_TIME,
347 MTL.PLANNING_MAKE_BUY_CODE,
348 MTL.bom_item_type bom_item_type
349 from mtl_system_items MTL,
350 bom_low_level_codes LLC
351 where LLC.ROLLUP_ID = roll_id
352 AND LLC.LOW_LEVEL_CODE = p_level
353 AND MTL.INVENTORY_ITEM_ID = LLC.INVENTORY_ITEM_ID
354 AND MTL.ORGANIZATION_ID = org_id
355 For update of mtl.CUMULATIVE_TOTAL_LEAD_TIME,
356 mtl.CUM_MANUFACTURING_LEAD_TIME NOWAIT;
357
358 Cursor l_comps_csr (p_org_id number, p_item_id number, p_unit_number varchar2,
359 p_eff_date date) is
360 SELECT NVL(MTL2.CUMULATIVE_TOTAL_LEAD_TIME, 0) CUMULATIVE_TOTAL_LEAD_TIME,
361 NVL(MTL2.CUM_MANUFACTURING_LEAD_TIME, 0) CUM_MANUFACTURING_LEAD_TIME,
362 COM.OPERATION_SEQ_NUM
363 FROM MTL_SYSTEM_ITEMS MTL2,
364 BOM_INVENTORY_COMPONENTS COM,
365 MTL_SYSTEM_ITEMS MTL1,
366 BOM_BILL_OF_MATERIALS BOM
367 WHERE BOM.ALTERNATE_BOM_DESIGNATOR IS NULL
368 AND COM.BILL_SEQUENCE_ID = BOM.COMMON_BILL_SEQUENCE_ID
369 AND BOM.ORGANIZATION_ID = p_org_id
370 AND BOM.ASSEMBLY_ITEM_ID = p_item_id
371 AND MTL1.INVENTORY_ITEM_ID = BOM.ASSEMBLY_ITEM_ID
372 AND MTL1.ORGANIZATION_ID = BOM.ORGANIZATION_ID
373 AND MTL2.INVENTORY_ITEM_ID = COM.COMPONENT_ITEM_ID
374 AND MTL2.ORGANIZATION_ID = BOM.ORGANIZATION_ID
375 AND COM.IMPLEMENTATION_DATE IS NOT NULL
376 AND NVL(COM.ECO_FOR_PRODUCTION,2) = 2
377 AND COM.COMPONENT_QUANTITY > 0
378 AND NOT (mtl1.replenish_to_order_flag = 'Y'
379 AND mtl1.bom_item_type = 4
380 AND mtl1.base_item_id IS NOT NULL
381 AND MTL2.BOM_ITEM_TYPE IN (1,2))
382 AND (
383 COM.DISABLE_DATE IS NULL
384 OR
385 COM.DISABLE_DATE > p_eff_date
386 )
387 AND ((MTL1.EFFECTIVITY_CONTROL <> 1
388 AND p_unit_number is NOT NULL
389 AND COM.DISABLE_DATE IS NULL
390 AND p_unit_number BETWEEN COM.FROM_END_ITEM_UNIT_NUMBER AND
391 NVL(COM.TO_END_ITEM_UNIT_NUMBER, p_unit_number))
392 OR (MTL1.EFFECTIVITY_CONTROL = 1
393 AND COM.EFFECTIVITY_DATE <= p_eff_date));
394 Cursor Get_OLTP (P_Assembly number, P_Org_Id number, P_Operation number) is
395 Select nvl(bos.operation_lead_time_percent, 0) operation_lead_time_percent
396 From Bom_Operation_Sequences bos,
397 Bom_Operational_Routings bor
398 Where bor.assembly_item_id = P_Assembly
399 And bor.organization_Id = P_Org_Id
400 And bor.alternate_routing_designator is null
401 And bor.common_routing_sequence_id = bos.routing_sequence_id
402 And bos.operation_seq_num = P_Operation
403 And NVL(bos.eco_for_production,2) = 2
404 -- Changed for bug 2647027
405 /** And bos.effectivity_date <= trunc(rev_date)
406 And nvl(bos.disable_date, rev_date + 1) >= trunc(rev_date); **/
407 And bos.effectivity_date <= rev_date
408 And nvl(bos.disable_date, rev_date + 1) >= rev_date;
409 l_oltp number := 0; -- operation lead time percent
410 l_cmlt number := 0; -- cumulative mfg lead time
411 l_ctlt number := 0; -- cumulative total lead time
412 l_stmt varchar2(5);
413 l_last_updated_by number; -- BUG 4990802
414 l_last_update_login number; -- BUG 4990802
415 BEGIN
416 l_stmt := '1';
417 For l_LevelCode_rec in l_LowLevelCode_csr loop
418 l_depth := l_LevelCode_rec.depth;
419 End loop;
420
421 For l_level in reverse 0..l_depth loop
422 l_stmt := '2';
423 For l_assy_rec in l_assy_csr(p_level => l_level) loop
424 l_cmlt := 0; -- cumulative mfg lead time
425 l_ctlt := 0; -- cumulative total lead time
426
427 -- added for Lead time rollup enh to exclude models
428
429
430 SELECT INCLUDE_MODELS_IN_ROLLUP
431 INTO X_include_models
432 FROM bom_parameters
433 WHERE organization_id = org_id;
434 if(X_include_models ='Y' OR
435 (l_assy_rec.bom_item_type <>1 AND l_assy_rec.bom_item_type <> 2)) THEN
436 X_include_in_rollup :='Y';
437 else
438 X_include_in_rollup := 'N';
439 end if;
440
441
442 If (l_assy_rec.planning_make_buy_code = 1 AND X_include_in_rollup='Y') then
443 l_stmt := '3';
444 For l_comps_rec in l_comps_csr (
445 p_org_id => org_id,
446 p_item_id => l_assy_rec.inventory_item_id,
447 p_unit_number => unit_number,
448 p_eff_date => rev_date) loop
449 l_oltp := 0; -- operation lead time percent
450 l_stmt := '4';
451 For l_operaton_rec in Get_OLTP (
452 P_Assembly => l_assy_rec.inventory_item_id,
453 P_Org_Id => org_id,
454 P_Operation => l_comps_rec.operation_seq_num) loop
455 l_oltp := l_operaton_rec.operation_lead_time_percent;
456 End loop;
457 l_ctlt := greatest(l_ctlt, l_comps_rec.cumulative_total_lead_time -
458 l_oltp/100 * l_assy_rec.full_lead_time);
459 l_cmlt := greatest(l_cmlt, l_comps_rec.cum_manufacturing_lead_time -
460 l_oltp/100 * l_assy_rec.full_lead_time);
461 End loop; -- components
462 End if; -- make
463 l_stmt := '5';
464
465 l_last_updated_by := NVL(fnd_global.user_id, -1); -- BUG 4990802
466 l_last_update_login := NVL(fnd_global.login_id, -1); -- BUG 4990802
467 /* Modified update statement to include the attributes last_update_date, last_updated_by, last_update_login for BUG 4990802 */
468 if (X_include_in_rollup ='Y') then
469 Update mtl_system_items set
470 CUMULATIVE_TOTAL_LEAD_TIME = l_assy_rec.total_lead_time + l_ctlt ,
471 CUM_MANUFACTURING_LEAD_TIME = l_assy_rec.full_lead_time + l_cmlt ,
472 REQUEST_ID = req_id,
473 PROGRAM_APPLICATION_ID = prgm_app_id,
474 PROGRAM_ID = prgm_id,
475 PROGRAM_UPDATE_DATE = SYSDATE,
476 LAST_UPDATE_DATE = SYSDATE,
477 LAST_UPDATED_BY = l_last_updated_by,
478 LAST_UPDATE_LOGIN = l_last_update_login
479 where ROWID = l_assy_rec.row_id;
480 end if; -- include in roll up is 'Y'
481 end loop; -- items
482 --COMMIT WORK;
483 end loop; -- level
484 err_msg := null;
485 EXCEPTION
486 WHEN OTHERS THEN
487 FND_MSG_PUB.Build_Exc_Msg(
488 p_pkg_name => 'BOMPCCLT',
489 p_procedure_name => 'update_lt('||l_stmt||')');
490 err_msg := Fnd_Message.Get_Encoded;
491 END update_lt;
492
493 /******************************************************************/
494 /*#
495 * Delete Processed Rows will delete the processed rows within the session
496 * under a given rollup id
497 * @param p_rollup_id current rollup identifier
498 * @rep:scope private
499 * @rep:lifecycle active
500 * @rep:displayname Delete Processed Rows within a rollup session.
501 ********************************************************************/
502 PROCEDURE Delete_Processed_Rows
503 (p_rollup_id IN NUMBER)
504 IS
505 l_RowsFound BOOLEAN;
506 BEGIN
507 l_RowsFound := true;
508 While l_RowsFound
509 LOOP
513 l_RowsFound := sql%found;
510 DELETE FROM BOM_LOW_LEVEL_CODES
511 WHERE ROLLUP_ID = p_rollup_id
512 and rownum <= G_CommitRows;
514 End loop;
515 --Commit;
516 END Delete_Processed_Rows;
517
518 PROCEDURE process_items(
519 org_id IN NUMBER,
520 roll_id IN NUMBER,
521 unit_number IN VARCHAR2,
522 eff_date IN DATE,
523 prgm_id IN NUMBER,
524 prgm_app_id IN NUMBER,
525 req_id IN NUMBER,
526 err_msg IN OUT NOCOPY /* file.sql.39 change */ VARCHAR2) IS
527
528 l_RowsFound boolean := true;
529 l_LoopFound boolean := false;
530 l_yes constant number := 1;
531 l_no constant number := 2;
532 l_err_msg varchar2(2000) := null;
533 l_stmt varchar2(5);
534 BEGIN
535 l_RowsFound := true;
536 While l_RowsFound loop
537 l_stmt := '1';
538 DELETE FROM BOM_LOW_LEVEL_CODES
539 WHERE ROLLUP_ID = roll_id
540 and rownum <= G_CommitRows;
541 l_RowsFound := sql%found;
542 End loop;
543 Commit;
544
545 l_err_msg := null;
546 explode_assy(
547 org_id => org_id,
548 prgm_id => prgm_id,
549 prgm_app_id => prgm_app_id,
550 req_id => req_id,
551 roll_id => roll_id,
552 unit_number => unit_number,
553 eff_date => eff_date,
554 alternate_bom_code => null,
555 loop_found => l_LoopFound,
556 err_msg => l_err_msg);
557
558 If l_err_msg is null then
559 update_lt(
560 org_id => org_id,
561 roll_id => roll_id,
562 prgm_id => prgm_id,
563 prgm_app_id => prgm_app_id,
564 req_id => req_id,
565 unit_number => unit_number,
566 rev_date => eff_date,
567 err_msg => l_err_msg);
568 End if;
569
570 l_RowsFound := true;
571 While l_RowsFound loop
572 l_stmt := '2';
573 DELETE FROM BOM_LOW_LEVEL_CODES
574 WHERE ROLLUP_ID = roll_id
575 and rownum <= G_CommitRows;
576 l_RowsFound := sql%found;
577 End loop;
578 --Commit;
579
580 If l_LoopFound then
581 Fnd_Message.Set_Name('BOM', 'BOM_ONLINE_LOOP');
582 Fnd_Message.Set_Token('ENTITY1', l_err_msg);
583 err_msg := Fnd_Message.Get_Encoded;
584 Else
585 err_msg := l_err_msg;
586 End if;
587
588 EXCEPTION
589 WHEN OTHERS THEN
590 FND_MSG_PUB.Build_Exc_Msg(
591 p_pkg_name => 'BOMPCCLT',
592 p_procedure_name => 'process_items('||l_stmt||')');
593 err_msg := Fnd_Message.Get_Encoded;
594 END process_items;
595
596
597 PROCEDURE process_items(
598 p_org_id IN NUMBER,
599 p_item_id IN NUMBER,
600 p_roll_id IN OUT NOCOPY /* file.sql.39 change */ NUMBER,
601 p_unit_number IN VARCHAR2,
602 p_eff_date IN DATE,
603 p_alternate_bom_code IN VARCHAR2,
604 p_prgm_id IN NUMBER,
605 p_prgm_app_id IN NUMBER,
606 p_req_id IN NUMBER,
610 l_err_code number;
607 x_err_msg IN OUT NOCOPY VARCHAR2)
608 IS
609 l_loopFound boolean := false;
611 BEGIN
612 --bom_debug('in process item . . . ' || p_item_id );
613 /*
614 explode_assy( org_id => org_id
615 ,prgm_id => prgm_id
616 ,prgm_app_id => prgm_app_id
617 ,req_id => req_id
618 ,roll_id => roll_id
619 ,unit_number => unit_number
620 ,eff_date => eff_date
621 ,alternate_bom_code=> alternate_bom_code
622 ,loop_found => l_LoopFound
623 ,err_msg => err_msg);
624
625 */
626 bom_exploder_pub.exploder_userexit(
627 org_id => p_org_id,
628 alt_desg => p_alternate_bom_code,
629 pk_value1 => p_item_id,
630 pk_value2 => p_org_id,
631 order_by => 1,
632 grp_id => p_roll_id,
633 levels_to_explode => 60,
634 impl_flag => 2,
635 expl_qty => 1,
636 p_autonomous_transaction => 2,
637 err_msg => x_err_msg,
638 error_code => l_err_code);
639 --bom_debug('out of process item . . . ');
640
641 END process_items;
642
643 END BOMPCCLT;