[Home] [Help]
PACKAGE BODY: APPS.WSMPWROT
Source
1 PACKAGE BODY WSMPWROT as
2 /* $Header: WSMWROTB.pls 120.4.12010000.2 2008/11/27 12:32:34 lmente ship $ */
3
4
5 /* ===========================================================================
6
7 PROCEDURE NAME: populate_wro
8
9 =========================================================================== */
10 PROCEDURE POPULATE_WRO (
11 p_first_flag IN NUMBER,
12 p_wip_entity_id IN NUMBER,
13 p_organization_id IN NUMBER,
14 p_assembly_item_id IN NUMBER,
15 p_bom_revision_date IN DATE,
16 p_alt_bom IN VARCHAR2,
17 p_quantity IN NUMBER,
18 p_operation_sequence_id IN NUMBER, -- BA: NSO-WLT
19 p_charges_exist IN NUMBER DEFAULT NULL, -- DEF ENH 0,-- EA: NSO-WLT
20 x_err_code OUT NOCOPY NUMBER,
21 x_err_msg OUT NOCOPY VARCHAR2,
22 p_routing_revision_date IN DATE DEFAULT NULL, -- DEF ENH SYSDATE,
23 p_wip_supply_type IN NUMBER DEFAULT NULL ) -- DEF ENH 7)
24 IS
25 BEGIN
26 POPULATE_WRO (
27 p_first_flag,
28 p_wip_entity_id,
29 p_organization_id,
30 p_assembly_item_id,
31 p_bom_revision_date,
32 p_alt_bom,
33 p_quantity,
34 p_operation_sequence_id, -- BA: NSO-WLT
35 p_charges_exist, -- EA: NSO-WLT
36 x_err_code,
37 x_err_msg,
38 p_routing_revision_date,
39 p_wip_supply_type,
40 NULL);
41
42 -- DEF ENH added exception block
43
44 EXCEPTION
45
46 WHEN OTHERS THEN
47 x_err_code := SQLCODE;
48 x_err_msg := substr('WSMPWROT ('||'Overloaded Procedure'||')' || SQLERRM,1,2000);
49
50 END;
51
52 PROCEDURE POPULATE_WRO (
53 p_first_flag IN NUMBER,
54 p_wip_entity_id IN NUMBER,
55 p_organization_id IN NUMBER,
56 p_assembly_item_id IN NUMBER,
57 p_bom_revision_date IN DATE,
58 p_alt_bom IN VARCHAR2,
59 p_quantity IN NUMBER,
60 p_operation_sequence_id IN NUMBER, -- BA: NSO-WLT
61 p_charges_exist IN NUMBER DEFAULT NULL, -- DEF ENH 0,-- EA: NSO-WLT
62 x_err_code OUT NOCOPY NUMBER,
63 x_err_msg OUT NOCOPY VARCHAR2,
64 p_routing_revision_date IN DATE DEFAULT NULL, -- DEF ENH SYSDATE,
65 p_wip_supply_type IN NUMBER DEFAULT NULL, -- DEF ENH 7,
66 p_routing_sequence_id IN NUMBER )
67 IS
68 x_routing_revision_date DATE ; -- DEF ENH := p_routing_revision_date;
69
70 x_default_sub VARCHAR2(10);
71 x_default_loc NUMBER;
72 x_bill_sequence_id NUMBER;
73 x_wdj_supply_type NUMBER;
74 x_org_id NUMBER;
75 x_user_id NUMBER := FND_GLOBAL.USER_ID;
76 x_login_id NUMBER := FND_GLOBAL.login_id;
77 x_operation_seq_num NUMBER;
78 x_max_resc_seq_num NUMBER;
79
80 l_error_msg VARCHAR2(2000);
81 l_error_code NUMBER := 0;
82 l NUMBER := 0;
83 m NUMBER := 0;
84 x_last_update_date DATE;
85 x_last_updated_by NUMBER;
86 x_creation_date DATE;
87 x_created_by NUMBER;
88 x_last_update_login NUMBER;
89 x_request_id NUMBER;
90 x_program_application_id NUMBER;
91 x_program_id NUMBER;
92 x_program_update_date DATE;
93
94 l_stmt_num NUMBER := 0; -- BA: NSO-WLT
95 l_op_seq_num_incr NUMBER; -- EA: NSO-WLT
96
97 be_exploder_exception EXCEPTION;
98 my_exception EXCEPTION;
99 loop_in_bom_exception EXCEPTION;
100 x_max_bill_levels NUMBER := 60;
101 x_start_date DATE;
102 x_completion_date DATE;
103 x_uom_code VARCHAR2(3);
104 x_applied_resource_units NUMBER := 0;
105 x_applied_resource_value NUMBER := 0;
106 x_no_of_first_level_comp NUMBER := 0;
107 x_comp_code VARCHAR2(2000):= to_char(p_assembly_item_id);
108 x_phan_qty NUMBER := 1;
109 NO_OF_COMP NUMBER := 0;
110 i NUMBER:=0;
111 x_explode_phantoms BOOLEAN:=FALSE;
112 x_routing_sequence_id NUMBER;
113 x_use_phantom_routings NUMBER := 0;
114 x_department_id NUMBER;
115 x_job_wip_supply_type NUMBER ; -- DEF ENH := p_wip_supply_type;
116 x_first_flag NUMBER := p_first_flag;
117 l_dummy NUMBER;
118 DATETIME_FMT CONSTANT VARCHAR2(22) := 'YYYY/MM/DD HH24:MI:SS';
119 l_ato_phantom VARCHAR2(1) := 'N'; -- bug 7598223
120
121 cursor reqs is
122 SELECT
123 A.COMPONENT_SEQUENCE_ID,
124 A.COMPONENT_ITEM_ID,
125 O.OPERATION_SEQ_NUM,
126
127 decode (p_organization_id, -- Supply locator id begin
128 x_org_id,
129
130 decode (A.SUPPLY_SUBINVENTORY, NULL,
131 decode ( C.WIP_SUPPLY_SUBINVENTORY, NULL,
132 decode(nvl(A.WIP_SUPPLY_TYPE, nvl(C.WIP_SUPPLY_TYPE, 1)),2,
133 nvl(x_default_loc,-1),3, nvl(x_default_loc,-1), NULL),
134 C.WIP_SUPPLY_LOCATOR_ID),
135 A.SUPPLY_LOCATOR_ID ),
136
137 decode ( C.WIP_SUPPLY_SUBINVENTORY, NULL,
138 decode(nvl(A.WIP_SUPPLY_TYPE, nvl(C.WIP_SUPPLY_TYPE, 1)),2,
139 nvl(x_default_loc,-1),3, nvl(x_default_loc,-1), NULL),
140 C.WIP_SUPPLY_LOCATOR_ID)
141
142 ) supply_locator_id, -- Supply locator id end
143
144 decode(x_job_wip_supply_type, 7, nvl(A.WIP_SUPPLY_TYPE, nvl(C.WIP_SUPPLY_TYPE, 1)),
145 x_job_wip_supply_type) wip_supply_type,
146 A.basis_type, --LBM enh
147
148 --R12-Component Shrinkage changes
149 A.COMPONENT_QUANTITY QPA,
150 A.COMPONENT_YIELD_FACTOR,
151 --R12-Component Shrinkage changes
152
153 decode (p_organization_id,
154 x_org_id,
155 nvl(A.SUPPLY_SUBINVENTORY, nvl(C.WIP_SUPPLY_SUBINVENTORY,
156 decode(nvl(A.WIP_SUPPLY_TYPE, nvl(C.WIP_SUPPLY_TYPE, 1)),2,x_default_sub,
157 3,x_default_sub, NULL))),
158 nvl(C.WIP_SUPPLY_SUBINVENTORY,
159 decode(nvl(A.WIP_SUPPLY_TYPE, nvl(C.WIP_SUPPLY_TYPE, 1)),2,x_default_sub,
160 3,x_default_sub, NULL)) ) supply_subinventory,
161
162 a.component_remarks comments,
163 a.attribute_category,
164 a.attribute1,
165 a.attribute2,
166 a.attribute3,
167 a.attribute4,
168 a.attribute5,
169 a.attribute6,
170 a.attribute7,
171 a.attribute8,
172 a.attribute9,
173 a.attribute10,
174 a.attribute11,
175 a.attribute12,
176 a.attribute13,
177 a.attribute14,
178 a.attribute15,
179 c.segment1,
180 c.segment2,
181 c.segment3,
182 c.segment4,
183 c.segment5,
184 c.segment6,
185 c.segment7,
186 c.segment8,
187 c.segment9,
188 c.segment10,
189 c.segment11,
190 c.segment12,
191 c.segment13,
192 c.segment14,
193 c.segment15,
194 c.segment16,
195 c.segment17,
196 c.segment18,
197 c.segment19,
198 c.segment20,
199 o.first_unit_start_date,
200 o.department_id
201 FROM
202 WIP_OPERATIONS O,
203 BOM_INVENTORY_COMPONENTS A,
204 MTL_SYSTEM_ITEMS C,
205 BOM_EXPLOSION_TEMP BE
206 WHERE
207 BE.GROUP_ID=WSMPWROT.EXPLOSION_GROUP_ID
208 AND BE.TOP_BILL_SEQUENCE_ID=x_bill_sequence_id
209 AND A.COMPONENT_SEQUENCE_ID=BE.COMPONENT_SEQUENCE_ID
210 AND BE.COMPONENT_ITEM_ID = C.INVENTORY_ITEM_ID
211 AND O.wip_entity_id = p_wip_entity_id
212 AND O.organization_id = p_organization_id
213 AND O.operation_sequence_id = p_operation_sequence_id
214 -- bugfix 1661987: Replaced the earlier operation_seq_num condition with the following
215 -- so that records are inserted in WRO for the last operation also.
216 AND O.operation_seq_num IN
217 (
218 SELECT max (o1.operation_seq_num)
219 from wip_operations o1
220 where
221 o1.wip_entity_id = p_wip_entity_id
222 and o1.operation_sequence_id = o.operation_sequence_id
223 and not exists (
224 select 'x' from wip_requirement_operations r
225 where
226 r.wip_entity_id = p_wip_entity_id
227 and r.organization_id = p_organization_id
228 and ((r.OPERATION_SEQ_NUM = o1.operation_seq_num) or
229 (r.OPERATION_SEQ_NUM = -o1.operation_seq_num))
230 )
231 )
232 AND C.ORGANIZATION_ID = p_organization_id
233 AND ((A.operation_seq_num =
234 (SELECT BOS.operation_seq_num
235 FROM BOM_OPERATION_SEQUENCES BOS
236 WHERE operation_sequence_id = p_operation_sequence_id))
237 OR (x_first_flag = 1 AND A.OPERATION_SEQ_NUM = 1))
238 AND A.EFFECTIVITY_DATE <=p_bom_revision_date
239 AND nvl(A.DISABLE_DATE, p_bom_revision_date + 1) >= p_bom_revision_date -- CHG: BUG2380517 from > to >=
240 AND A.EFFECTIVITY_DATE =
241 (
242 SELECT MAX(EFFECTIVITY_DATE)
243 FROM BOM_INVENTORY_COMPONENTS BIC,
244 ENG_REVISED_ITEMS ERI
245 WHERE BIC.BILL_SEQUENCE_ID = A.BILL_SEQUENCE_ID
246 AND BIC.COMPONENT_ITEM_ID = A.COMPONENT_ITEM_ID
247 AND (decode(BIC.IMPLEMENTATION_DATE, NULL,
248 BIC.OLD_COMPONENT_SEQUENCE_ID,
249 BIC.COMPONENT_SEQUENCE_ID) =
250 decode(A.IMPLEMENTATION_DATE, NULL,
251 A.OLD_COMPONENT_SEQUENCE_ID,
252 A.COMPONENT_SEQUENCE_ID)
253 OR
254 BIC.OPERATION_SEQ_NUM = A.OPERATION_SEQ_NUM)
255 AND BIC.EFFECTIVITY_DATE <= p_bom_revision_date
256 AND BIC.REVISED_ITEM_SEQUENCE_ID =
257 ERI.REVISED_ITEM_SEQUENCE_ID(+)
258 AND (nvl(ERI.STATUS_TYPE,6) IN (4,6,7))
259 AND NOT EXISTS
260 (SELECT 'X'
261 FROM BOM_INVENTORY_COMPONENTS BICN, ENG_REVISED_ITEMS ERI1
262 WHERE BICN.BILL_SEQUENCE_ID = A.BILL_SEQUENCE_ID
263 AND BICN.OLD_COMPONENT_SEQUENCE_ID =
264 A.COMPONENT_SEQUENCE_ID
265 AND BICN.ACD_TYPE in (2,3)
266 AND BICN.DISABLE_DATE <= p_bom_revision_date
267 AND ERI1.REVISED_ITEM_SEQUENCE_ID = BICN.REVISED_ITEM_SEQUENCE_ID
268 AND ( nvl(ERI1.STATUS_TYPE,6) IN (4,6,7) )
269 )
270 )
271 ORDER BY A.COMPONENT_ITEM_ID,
272 nvl(A.WIP_SUPPLY_TYPE, C.WIP_SUPPLY_TYPE),
273 TO_NUMBER(TO_CHAR(A.EFFECTIVITY_DATE,'SSSS'));
274
275 TYPE table_comp_details is TABLE OF reqs%ROWTYPE
276 INDEX by BINARY_INTEGER;
277 t_comp_details table_comp_details;
278
279 BEGIN
280
281 l_stmt_num := 5;
282
283 if (WSMPWROT.USE_PHANTOM_ROUTINGS IS NULL) then
284 begin
285 SELECT nvl(use_phantom_routings, 0)
286 INTO WSMPWROT.USE_PHANTOM_ROUTINGS
287 FROM BOM_PARAMETERS
288 WHERE ORGANIZATION_ID = p_organization_id;
289 exception
290 when no_data_found then
291 null;
292 end;
293 end if;
294
295 x_use_phantom_routings := WSMPWROT.USE_PHANTOM_ROUTINGS;
296
297 l_stmt_num := 6;
298 begin
299 SELECT nvl(maximum_bom_level, 60)
300 INTO x_max_bill_levels
301 FROM BOM_PARAMETERS
302 WHERE ORGANIZATION_ID = p_organization_id;
303 exception
304 when no_data_found then
305 null;
306 end;
307
308 if ((p_routing_revision_date is NULL) and (p_wip_supply_type is null)) then
309 SELECT nvl(routing_revision_date, SYSDATE), nvl(wip_supply_type, 7)
310 INTO x_routing_revision_date, x_job_wip_supply_type
311 FROM wip_discrete_jobs
312 WHERE wip_entity_id=p_wip_entity_id;
313 elsif (p_routing_revision_date is NULL) then
314 SELECT nvl(routing_revision_date, SYSDATE)
315 INTO x_routing_revision_date
316 FROM wip_discrete_jobs
317 WHERE wip_entity_id=p_wip_entity_id;
318
319 x_job_wip_supply_type := p_wip_supply_type ;
320 elsif (p_wip_supply_type is null) then
321 SELECT nvl(wip_supply_type, 7)
322 INTO x_job_wip_supply_type
323 FROM wip_discrete_jobs
324 WHERE wip_entity_id=p_wip_entity_id;
325
326 x_routing_revision_date := p_routing_revision_date;
327 else
328 x_job_wip_supply_type := p_wip_supply_type ;
329 x_routing_revision_date := p_routing_revision_date;
330 end if;
331
332 l_stmt_num := 10;
333
334 SELECT max(operation_seq_num)
335 INTO x_operation_seq_num
336 FROM WIP_OPERATIONS
337 WHERE operation_sequence_id=p_operation_sequence_id
338 and wip_entity_id = p_wip_entity_id;
339
340 l_stmt_num := 15;
341
342 SELECT first_unit_start_date, last_unit_completion_date, department_id
343 INTO x_start_date, x_completion_date, x_department_id
344 FROM WIP_OPERATIONS
345 WHERE wip_entity_id = p_wip_entity_id
346 AND organization_id = p_organization_id
347 AND operation_seq_num = x_operation_seq_num;
348
349 l_stmt_num := 20;
350
351 SELECT default_pull_supply_subinv,
352 default_pull_supply_locator_id
353 INTO x_default_sub,
354 x_default_loc
355 FROM wip_parameters
356 WHERE organization_id = p_organization_id;
357
358 l_stmt_num := 30;
359
360 if (p_charges_exist is NULL or p_charges_exist = 0 ) then
361 l_op_seq_num_incr := 0;
362 else
363 SELECT nvl(op_seq_num_increment, 10)
364 INTO l_op_seq_num_incr
365 FROM wsm_parameters
366 WHERE organization_id = p_organization_id;
367 end if;
368
369 BEGIN
370 l_stmt_num := 40;
371
372 SELECT B.BILL_SEQUENCE_ID, B.ORGANIZATION_ID
373 INTO x_bill_sequence_id, x_org_id
374 FROM BOM_BILL_OF_MATERIALS B
375 WHERE B.ASSEMBLY_ITEM_ID = p_assembly_item_id
376 AND B.ORGANIZATION_ID = p_organization_id
377 AND (
378 (
379 (
380 nvl(B.ALTERNATE_BOM_DESIGNATOR, 'NONE') = nvl(p_alt_bom, 'NONE')
381 OR
382 (
383 B.ALTERNATE_BOM_DESIGNATOR IS NULL AND
384 NOT EXISTS
385 (
386 SELECT 'X' FROM BOM_BILL_OF_MATERIALS C
387 WHERE C.ASSEMBLY_ITEM_ID = p_assembly_item_id
388 AND C.ORGANIZATION_ID = p_organization_id
389 AND C.ALTERNATE_BOM_DESIGNATOR = p_alt_bom
390 )
391 )
392 )
393 )
394 );
395
396 EXCEPTION
397 WHEN NO_DATA_FOUND THEN
398 return;
399 END;
400
401 l_stmt_num := 45;
402
403 if (WSMPWROT.explosion_group_id is null) then
404 x_explode_phantoms := TRUE;
405
406 l_stmt_num := 46;
407
408 SELECT BOM_EXPLOSION_TEMP_S.nextval INTO WSMPWROT.EXPLOSION_GROUP_ID
409 FROM DUAL;
410 end if;
411
412 x_explode_phantoms:=TRUE;
413
414 if (x_explode_phantoms=TRUE) then
415
416 l_stmt_num := 48;
417
418 /* bompexpl.exploder_userexit(0, -- verify_flag
419 p_organization_id,
420 1, --order_by
421 WSMPWROT.EXPLOSION_GROUP_ID, -- grp_id
422 0, -- session_id
423 1, -- levels_to_explode
424 1, -- bom_or_eng
425 1, -- impl_flag
426 1, -- plan_factor_flag
427 2, --changed by skaradib for bug 2362939 -- explode_option
428 5, -- module
429 0, -- cst_type_id
430 0, -- std_comp_flag
431 1, -- expl_qty
432 p_assembly_item_id,
433 p_alt_bom,
434 '', -- comp_code
435 to_char(p_bom_revision_date, datetime_fmt), --datetime_fmt for bug 2393005 by skaradib
436 l_error_msg,
437 l_error_code); */
438 -- Bug fix: 5377818 Used named convention for arguments
439
440 bompexpl.exploder_userexit(
441 verify_flag =>0, -- verify_flag
442 org_id =>p_organization_id,
443 order_by =>1, --order_by
444 grp_id =>WSMPWROT.EXPLOSION_GROUP_ID, -- grp_id
445 session_id =>0, -- session_id
446 levels_to_explode=>1, -- levels_to_explode
447 bom_or_eng =>1, -- bom_or_eng
448 impl_flag =>1, -- impl_flag
449 plan_factor_flag =>1, -- plan_factor_flag
450 explode_option =>2, --changed by skaradib for bug 2362939 -- explode_option
451 module =>5, -- module
452 cst_type_id =>0, -- cst_type_id
453 std_comp_flag =>0, -- std_comp_flag
454 expl_qty =>1, -- expl_qty
455 item_id =>p_assembly_item_id,
456 alt_desg =>p_alt_bom,
457 comp_code =>'', -- comp_code
458 rev_date =>to_char(p_bom_revision_date, datetime_fmt), --datetime_fmt for bug 2393005 by skaradib
459 err_msg =>l_error_msg,
460 error_code =>l_error_code);
461
462
463
464 if (l_error_code <> 0) then
465 raise be_exploder_exception;
466 end if;
467 end if;
468
469 l_stmt_num := 49;
470
471 if (x_first_flag=1) then
472 begin
473 select 0 into x_first_flag
474 from BOM_OPERATION_NETWORKS_V BONV
475 where BONV.routing_sequence_id=p_routing_sequence_id
476 and ((BONV.from_seq_num = 1) or (BONV.to_seq_num=1));
477 exception
478 when no_data_found then
479 null;
480 when too_many_rows then
481 x_first_flag := 0;
482 end;
483 end if;
484
485 begin
486 OPEN reqs;
487
488 LOOP
489
490 FETCH reqs INTO t_comp_details(NO_OF_COMP);
491
492 EXIT WHEN reqs%NOTFOUND;
493 NO_OF_COMP := NO_OF_COMP+1;
494 END LOOP;
495 CLOSE reqs;
496 end;
497
498 x_no_of_first_level_comp:=NO_OF_COMP;
499 i:=0;
500
501 LOOP
502 x_explode_phantoms := TRUE;
503 if (t_comp_details.exists(i)) then
504 if (t_comp_details(i).wip_supply_type=6) then
505 BEGIN
506
507 l_stmt_num := 50;
508 t_comp_details(i).operation_seq_num :=-x_operation_seq_num;
509 x_phan_qty := t_comp_details(i).qpa;
510
511 SELECT B.BILL_SEQUENCE_ID, B.ORGANIZATION_ID
512 INTO x_bill_sequence_id, x_org_id
513 FROM BOM_BILL_OF_MATERIALS B
514 WHERE B.ASSEMBLY_ITEM_ID = t_comp_details(i).component_item_id
515 AND B.ORGANIZATION_ID = p_organization_id
516 AND B.ALTERNATE_BOM_DESIGNATOR IS NULL;
517
518 EXCEPTION
519 WHEN NO_DATA_FOUND THEN
520 x_bill_sequence_id := NULL;
521 END;
522 -- bug 7598223: begin
523 l_stmt_num := 50.5;
524 begin
525 select 'Y'
526 into l_ato_phantom
527 from mtl_system_items msi
528 where msi.inventory_item_id = t_comp_details(i).component_item_id
529 and msi.organization_id = p_organization_id
530 and msi.replenish_to_order_flag = 'Y'
531 and msi.bom_item_type in (1,2);
532 exception
533 when no_data_found then
534 l_ato_phantom := 'N';
535 end;
536 -- bug 7598223: end
537 l_stmt_num := 51;
538
539 BEGIN
540 SELECT common_routing_sequence_id into x_routing_sequence_id
541 FROM BOM_OPERATIONAL_ROUTINGS
542 WHERE assembly_item_id = t_comp_details(i).component_item_id
543 AND organization_id = p_organization_id
544 AND alternate_routing_designator is null
545 AND cfm_routing_flag = 3;
546 EXCEPTION
547 WHEN NO_DATA_FOUND THEN
548 null;
549 END;
550 -- bug 7598223: added the condition on l_ato_phantom
551 if ((x_explode_phantoms=TRUE) and (x_bill_sequence_id is not null) and l_ato_phantom = 'N') then
552
553 l_stmt_num := 60;
554
555 /* bompexpl.exploder_userexit(0, -- verify_flag
556 p_organization_id,
557 1, --order_by
558 WSMPWROT.EXPLOSION_GROUP_ID, -- grp_id
559 0, -- session_id
560 x_max_bill_levels-1, -- levels_to_explode
561 1, -- bom_or_eng
562 1, -- impl_flag
563 1, -- plan_factor_flag
564 2, --changed by skaradib for bug 2362939 1, -- explode_option
565 5, -- module
566 0, -- cst_type_id
567 0, -- std_comp_flag
568 1, -- expl_qty
569 t_comp_details(i).component_item_id,
570 NULL,
571 x_comp_code, -- comp_code
572 to_char(p_bom_revision_date, datetime_fmt), --datetime_fmt for bug 2393005 by skaradib
573 l_error_msg,
574 l_error_code); */
575
576 -- Bug fix: 5377818 Used named convention for arguments
577
578 bompexpl.exploder_userexit(
579 verify_flag =>0, -- verify_flag
580 org_id =>p_organization_id,
581 order_by =>1, --order_by
582 grp_id =>WSMPWROT.EXPLOSION_GROUP_ID, -- grp_id
583 session_id =>0, -- session_id
584 levels_to_explode=>x_max_bill_levels-1, -- levels_to_explode
585 bom_or_eng =>1, -- bom_or_eng
586 impl_flag =>1, -- impl_flag
587 plan_factor_flag =>1, -- plan_factor_flag
588 explode_option =>2, --changed by skaradib for bug 2362939 -- explode_option
589 module =>5, -- module
590 cst_type_id =>0, -- cst_type_id
591 std_comp_flag =>0, -- std_comp_flag
592 expl_qty =>1, -- expl_qty
593 item_id =>t_comp_details(i).component_item_id,
594 alt_desg =>NULL,
595 comp_code =>x_comp_code, -- comp_code
596 rev_date =>to_char(p_bom_revision_date, datetime_fmt), --datetime_fmt for bug 2393005 by skaradib
597 err_msg =>l_error_msg,
598 error_code =>l_error_code);
599
600
601 if (l_error_code <> 0) then
602 raise be_exploder_exception;
603 end if;
604 end if;
605
606 if ((x_routing_sequence_id is not null) and (x_use_phantom_routings=1) and (x_bill_sequence_id is not null)) then
607 DECLARE
608 no_of_levels NUMBER:=0;
609 l_level NUMBER := 1;
610 BEGIN
611
612 l_stmt_num := 70;
613 select max(plan_level) into no_of_levels from bom_explosion_temp
614 where top_bill_sequence_id=x_bill_sequence_id;
615
616 FOR l_level in 1..no_of_levels LOOP
617 l_stmt_num := 80;
618 update bom_explosion_temp be set be.primary_path_flag=1
619 where
620 be.top_bill_sequence_id=x_bill_sequence_id
621 and be.group_id=WSMPWROT.EXPLOSION_GROUP_ID
622 and ((be.operation_seq_num=1)
623 or (be.operation_seq_num in (
624 select bonv.from_seq_num
625 from bom_operational_routings bor,
626 bom_operation_networks_v bonv,
627 bom_operation_sequences bos
628 where bor.assembly_item_id=be.assembly_item_id
629 and bor.alternate_routing_designator is null
630 and bonv.routing_sequence_id=bor.common_routing_sequence_id
631 and be.operation_seq_num=bos.operation_seq_num
632 and bos.routing_sequence_id=bor.common_routing_sequence_id
633 AND bos.effectivity_date <= x_routing_revision_date
634 AND nvl(bos.disable_date, x_routing_revision_date+2) >= x_routing_revision_date
635 -- CHG: BUG2380517 from > to >=
636 and NVL(BOS.operation_type, 1) = 1
637 and bonv.transition_type=1))
638 or (be.operation_seq_num in (
639 select bonv.to_seq_num
640 from bom_operational_routings bor,
641 bom_operation_networks_v bonv,
642 bom_operation_sequences bos
643 where bor.assembly_item_id=be.assembly_item_id
644 and bor.alternate_routing_designator is null
645 and bonv.routing_sequence_id=bor.common_routing_sequence_id
646 and be.operation_seq_num=bos.operation_seq_num
647 and bos.routing_sequence_id=bor.common_routing_sequence_id
648 AND bos.effectivity_date <= x_routing_revision_date
649 AND nvl(bos.disable_date, x_routing_revision_date+2) > x_routing_revision_date
650 -- CHG: BUG2380517 from > to >=
651 and NVL(BOS.operation_type, 1) = 1
652 and bonv.transition_type=1)))
653 and plan_level=l_level
654 and ((plan_level=1)
655 OR (exists (
656 select 'x'
657 from bom_explosion_temp be1
658 where be1.top_bill_sequence_id=x_bill_sequence_id
659 and be1.group_id=WSMPWROT.EXPLOSION_GROUP_ID
660 and be1.component_item_id=be.assembly_item_id
661 and be1.sort_order=SUBSTR(BE.SORT_ORDER, 1, l_level*(WSMPWROT.X_SortWidth))
662 and be1.primary_path_flag=1)));
663 END LOOP;
664 END;
665 else --if ((x_routing_sequence_id is not null) and (x_use_phantom_routings=1) and (x_bill_sequence_id is not null)) then
666
667 l_stmt_num := 90;
668 update bom_explosion_temp be set be.primary_path_flag=1
669 where be.top_bill_sequence_id=x_bill_sequence_id;
670
671 t_comp_details.delete(i);
672 end if; --if ((x_routing_sequence_id is not null) and (x_use_phantom_routings=1) and (x_bill_sequence_id is not null)) then
673
674
675 DECLARE
676 cursor phan_comp is
677 SELECT
678 BE.COMPONENT_SEQUENCE_ID,
679 BE.COMPONENT_ITEM_ID,
680 BE.OPERATION_SEQ_NUM,
681
682 decode (p_organization_id, -- Supply locator id begin
683 x_org_id,
684
685 decode (A.SUPPLY_SUBINVENTORY, NULL,
686 decode (C.WIP_SUPPLY_SUBINVENTORY, NULL,
687 decode(nvl(A.WIP_SUPPLY_TYPE, nvl(C.WIP_SUPPLY_TYPE, 1)),2,
688 nvl(x_default_loc,-1),3, nvl(x_default_loc,-1), NULL),
689 C.WIP_SUPPLY_LOCATOR_ID),
690 A.SUPPLY_LOCATOR_ID ),
691
692 decode (C.WIP_SUPPLY_SUBINVENTORY, NULL,
693 decode (nvl(A.WIP_SUPPLY_TYPE, nvl(C.WIP_SUPPLY_TYPE, 1)),2,
694 nvl(x_default_loc,-1),3, nvl(x_default_loc,-1), NULL),
695 C.WIP_SUPPLY_LOCATOR_ID)
696
697 ) supply_locator_id, -- Supply locator id end
698
699 decode(x_job_wip_supply_type, 7, nvl(A.WIP_SUPPLY_TYPE, nvl(C.WIP_SUPPLY_TYPE, 1)),
700 x_job_wip_supply_type) wip_supply_type,
701 A.basis_type,
702
703 BE.extended_quantity*x_phan_qty qpa,
704 A.component_yield_factor,
705
706 decode (p_organization_id,
707 x_org_id,
708 nvl(A.SUPPLY_SUBINVENTORY, nvl(C.WIP_SUPPLY_SUBINVENTORY,
709 decode(nvl(A.WIP_SUPPLY_TYPE, nvl(C.WIP_SUPPLY_TYPE, 1)),2,x_default_sub,
710 3,x_default_sub, NULL))),
711 nvl(C.WIP_SUPPLY_SUBINVENTORY,
712 decode(nvl(A.WIP_SUPPLY_TYPE, nvl(C.WIP_SUPPLY_TYPE, 1)),2,x_default_sub,
713 3,x_default_sub, NULL)) ) supply_subinventory,
714
715 a.component_remarks comments,
716 a.attribute_category,
717 a.attribute1,
718 a.attribute2,
719 a.attribute3,
720 a.attribute4,
721 a.attribute5,
722 a.attribute6,
723 a.attribute7,
724 a.attribute8,
725 a.attribute9,
726 a.attribute10,
727 a.attribute11,
728 a.attribute12,
729 a.attribute13,
730 a.attribute14,
731 a.attribute15,
732 c.segment1,
733 c.segment2,
734 c.segment3,
735 c.segment4,
736 c.segment5,
737 c.segment6,
738 c.segment7,
739 c.segment8,
740 c.segment9,
741 c.segment10,
742 c.segment11,
743 c.segment12,
744 c.segment13,
745 c.segment14,
746 c.segment15,
747 c.segment16,
748 c.segment17,
749 c.segment18,
750 c.segment19,
751 c.segment20,
752 x_start_date first_unit_start_date,
753 x_department_id department_id
754 FROM
755 BOM_INVENTORY_COMPONENTS A,
756 MTL_SYSTEM_ITEMS C,
757 BOM_EXPLOSION_TEMP BE
758 WHERE BE.GROUP_ID = WSMPWROT.EXPLOSION_GROUP_ID
759 AND A.COMPONENT_SEQUENCE_ID = BE.COMPONENT_SEQUENCE_ID
760 AND C.INVENTORY_ITEM_ID = BE.COMPONENT_ITEM_ID
761 AND C.ORGANIZATION_ID = P_ORGANIZATION_ID
762 AND BE.TOP_BILL_SEQUENCE_ID=x_bill_sequence_id
763 AND BE.PRIMARY_PATH_FLAG = 1
764 AND A.EFFECTIVITY_DATE <=p_bom_revision_date
765 AND nvl(A.DISABLE_DATE, p_bom_revision_date + 1) >= p_bom_revision_date -- CHG: BUG2380517 from > to >=
766 AND A.EFFECTIVITY_DATE =
767 (
768 SELECT MAX(EFFECTIVITY_DATE)
769 FROM BOM_INVENTORY_COMPONENTS BIC,
770 ENG_REVISED_ITEMS ERI
771 WHERE BIC.BILL_SEQUENCE_ID = A.BILL_SEQUENCE_ID
772 AND BIC.COMPONENT_ITEM_ID = A.COMPONENT_ITEM_ID
773 AND (
774 decode(BIC.IMPLEMENTATION_DATE, NULL,
775 BIC.OLD_COMPONENT_SEQUENCE_ID,
776 BIC.COMPONENT_SEQUENCE_ID) =
777 decode(A.IMPLEMENTATION_DATE, NULL,
778 A.OLD_COMPONENT_SEQUENCE_ID,
779 A.COMPONENT_SEQUENCE_ID)
780 OR
781 BIC.OPERATION_SEQ_NUM = A.OPERATION_SEQ_NUM)
782 AND BIC.EFFECTIVITY_DATE <= p_bom_revision_date
783 AND BIC.REVISED_ITEM_SEQUENCE_ID =
784 ERI.REVISED_ITEM_SEQUENCE_ID(+)
785 AND ( nvl(ERI.STATUS_TYPE,6) IN (4,6,7))
786 AND NOT EXISTS
787 (SELECT 'X'
788 FROM BOM_INVENTORY_COMPONENTS BICN, ENG_REVISED_ITEMS ERI1
789 WHERE BICN.BILL_SEQUENCE_ID = A.BILL_SEQUENCE_ID
790 AND BICN.OLD_COMPONENT_SEQUENCE_ID =
791 A.COMPONENT_SEQUENCE_ID
792 AND BICN.ACD_TYPE in (2,3)
793 AND BICN.DISABLE_DATE <= p_bom_revision_date
794 AND ERI1.REVISED_ITEM_SEQUENCE_ID = BICN.REVISED_ITEM_SEQUENCE_ID
795 AND ( nvl(ERI1.STATUS_TYPE,6) IN (4,6,7) )
796 ))
797
798 ORDER BY A.COMPONENT_ITEM_ID,
799 nvl(A.WIP_SUPPLY_TYPE, C.WIP_SUPPLY_TYPE),
800 TO_NUMBER(TO_CHAR(A.EFFECTIVITY_DATE,'SSSS'));
801
802 BEGIN
803 l_stmt_num := 100;
804 OPEN phan_comp;
805 LOOP
806 l_stmt_num := 110;
807
808 FETCH phan_comp into t_comp_details(NO_OF_COMP);
809 EXIT WHEN phan_comp%NOTFOUND;
810
811 if ((t_comp_details(NO_OF_COMP).component_item_id=p_assembly_item_id) and (t_comp_details(NO_OF_COMP).wip_supply_type=6)) then
812 raise loop_in_bom_exception;
813 end if;
814 IF (t_comp_details(NO_OF_COMP).wip_supply_type=6) THEN
815 t_comp_details(NO_OF_COMP).operation_seq_num :=-x_operation_seq_num;
816 ELSE
817 t_comp_details(NO_OF_COMP).operation_seq_num :=x_operation_seq_num;
818 END IF;
819 NO_OF_COMP:=NO_OF_COMP+1;
820 END LOOP;
821 CLOSE phan_comp;
822 END;
823 END IF; --if (t_comp_details(i).wip_supply_type=6) then
824
825 IF (i=(x_no_of_first_level_comp - 1)) THEN
826 EXIT;
827 ELSE
828 i := i+1;
829 END IF;
830 ELSE --if (t_comp_details.exists(i)) then
831 exit;
832 END IF;
833
834 END LOOP; --LOOP
835
836
837 WHILE (l<(t_comp_details.last+1)) LOOP
838 m := l+1;
839 if (t_comp_details.exists(l)) then
840 WHILE (m<(t_comp_details.last+1)) LOOP
841 if (t_comp_details.exists(m)) then
842 if (t_comp_details(l).component_item_id= t_comp_details(m).component_item_id) then
843 t_comp_details(l).qpa := t_comp_details(l).qpa + t_comp_details(m).qpa;
844 if (t_comp_details(l).wip_supply_type > t_comp_details(m).wip_supply_type) then
845 t_comp_details(l).wip_supply_type := t_comp_details(m).wip_supply_type;
846 t_comp_details(l).supply_subinventory := t_comp_details(m).supply_subinventory;
847 t_comp_details(l).supply_locator_id := t_comp_details(m).supply_locator_id;
848 end if;
849 t_comp_details.delete(m);
850 end if;
851 end if;
852 m:=m+1;
853 END LOOP;
854
855 l_stmt_num := 120;
856
857 INSERT INTO WIP_REQUIREMENT_OPERATIONS
858 (
859 inventory_item_id,
860 organization_id,
861 wip_entity_id,
862 operation_seq_num,
863 repetitive_schedule_id,
864 last_update_date,
865 last_updated_by,
866 creation_date,
867 created_by,
868 last_update_login,
869 component_sequence_id,
870 wip_supply_type,
871 basis_type, --LBM enh
872 date_required,
873 required_quantity,
874 quantity_issued,
875 quantity_per_assembly,
876 component_yield_factor, --R12-COMPONENT SHRINKAGE
877 supply_subinventory,
878 supply_locator_id,
879 mrp_net_flag,
880 comments,
881 attribute_category,
882 attribute1,
883 attribute2,
884 attribute3,
885 attribute4,
886 attribute5,
887 attribute6,
888 attribute7,
889 attribute8,
890 attribute9,
891 attribute10,
892 attribute11,
893 attribute12,
894 attribute13,
895 attribute14,
896 attribute15,
897 segment1,
898 segment2,
899 segment3,
900 segment4,
901 segment5,
902 segment6,
903 segment7,
904 segment8,
905 segment9,
906 segment10,
907 segment11,
908 segment12,
909 segment13,
910 segment14,
911 segment15,
912 segment16,
913 segment17,
914 segment18,
915 segment19,
916 segment20,
917 department_id,
918 costed_quantity_issued,
919 costed_quantity_relieved
920 )
921 values
922 (
923 t_comp_details(l).component_item_id,
924 p_organization_id,
925 p_wip_entity_id,
926 t_comp_details(l).operation_seq_num + l_op_seq_num_incr,
927 NULL,
928 SYSDATE,
929 x_user_id,
930 SYSDATE,
931 x_user_id,
932 x_login_id,
933 t_comp_details(l).component_sequence_id,
934 t_comp_details(l).wip_supply_type,
935 t_comp_details(l).basis_type, --LBM enh
936 t_comp_details(l).first_unit_start_date,
937 ROUND(t_comp_details(l).qpa / t_comp_details(l).component_yield_factor
938 * decode(nvl(t_comp_details(l).basis_type, 1), 2, 1, p_quantity),6), --LBM enh
939 0,
940 t_comp_details(l).qpa,
941 t_comp_details(l).component_yield_factor, --R12-Component Shrinkage
942 t_comp_details(l).supply_subinventory,
943 decode(t_comp_details(l).supply_locator_id, -1, NULL,t_comp_details(l).supply_locator_id),
944 decode(t_comp_details(l).wip_supply_type, 5, 2,
945 decode(sign(t_comp_details(l).qpa), -1, 2, 1)),
946 t_comp_details(l).comments,
947 t_comp_details(l).attribute_category,
948 t_comp_details(l).attribute1,
949 t_comp_details(l).attribute2,
950 t_comp_details(l).attribute3,
951 t_comp_details(l).attribute4,
952 t_comp_details(l).attribute5,
953 t_comp_details(l).attribute6,
954 t_comp_details(l).attribute7,
955 t_comp_details(l).attribute8,
956 t_comp_details(l).attribute9,
957 t_comp_details(l).attribute10,
958 t_comp_details(l).attribute11,
959 t_comp_details(l).attribute12,
960 t_comp_details(l).attribute13,
961 t_comp_details(l).attribute14,
962 t_comp_details(l).attribute15,
963 t_comp_details(l).segment1,
964 t_comp_details(l).segment2,
965 t_comp_details(l).segment3,
966 t_comp_details(l).segment4,
967 t_comp_details(l).segment5,
968 t_comp_details(l).segment6,
969 t_comp_details(l).segment7,
970 t_comp_details(l).segment8,
971 t_comp_details(l).segment9,
972 t_comp_details(l).segment10,
973 t_comp_details(l).segment11,
974 t_comp_details(l).segment12,
975 t_comp_details(l).segment13,
976 t_comp_details(l).segment14,
977 t_comp_details(l).segment15,
978 t_comp_details(l).segment16,
979 t_comp_details(l).segment17,
980 t_comp_details(l).segment18,
981 t_comp_details(l).segment19,
982 t_comp_details(l).segment20,
983 t_comp_details(l).department_id,
984 0,
985 0
986 );
987
988 end if;
989 l:=l+1;
990 END LOOP;
991
992 if (x_use_phantom_routings = 1) then
993
994 DECLARE
995 CURSOR phan_resc_cursor IS
996 SELECT BOR.resource_id ,
997 BOR.activity_id ,
998 BOR.standard_rate_flag ,
999 BOR.assigned_units ,
1000 decode(nvl(BOR.basis_type, 1), wip_constants.PER_LOT , BOR.usage_rate_or_amount,
1001 BOR.usage_rate_or_amount * nvl(WRO.QUANTITY_PER_ASSEMBLY, 1) ) usage_rate_or_amount,
1002 BOR.basis_type ,
1003 BOR.autocharge_type ,
1004 BOS.department_id,
1005 -(WRO.OPERATION_SEQ_NUM) phantom_op_seq_num,
1006 WRO.INVENTORY_ITEM_ID phantom_item_id,
1007 BOR.SCHEDULE_SEQ_NUM, --bugfix 2493065
1008 BOR.SUBSTITUTE_GROUP_NUM, BOR.PRINCIPLE_FLAG, BOR.SETUP_ID
1009
1010 FROM
1011 MTL_UOM_CONVERSIONS CON,
1012 BOM_RESOURCES BR,
1013 BOM_OPERATION_RESOURCES BOR,
1014 BOM_DEPARTMENT_RESOURCES BDR1,
1015 BOM_OPERATION_SEQUENCES BOS,
1016 BOM_OPERATIONAL_ROUTINGS BRTG,
1017 WIP_REQUIREMENT_OPERATIONS WRO,
1018 MTL_SYSTEM_ITEMS MSI --BUG 7598223
1019 WHERE
1020 BRTG.organization_id = p_organization_id
1021 and BRTG.assembly_item_id = wro.inventory_item_id
1022 and MSI.inventory_item_id = wro.inventory_item_id --BUG 7598223
1023 and MSI.organization_id = p_organization_id --BUG 7598223
1024 and (MSI.bom_item_type not in (1, 2) or MSI.replenish_to_order_flag <> 'Y') --BUG 7598223
1025 and NVL(BRTG.cfm_routing_flag, 3) = 3 /* not a flow routing */
1026 and BRTG.alternate_routing_designator IS NULL /* primary routing */
1027 and BOS.department_id = BDR1.department_id
1028 and BOR.resource_id = BDR1.resource_id
1029 and BOR.operation_sequence_id = BOS.operation_sequence_id
1030 AND BOR.resource_id = BR.resource_id
1031 AND CON.UOM_CODE (+) = BR.UNIT_OF_MEASURE
1032 AND CON.INVENTORY_ITEM_ID (+) = 0
1033 AND WRO.Operation_seq_num = -x_operation_seq_num
1034 AND WRO.wip_entity_id=p_wip_entity_id
1035 and wro.organization_id=p_organization_id
1036 and BRTG.common_routing_sequence_id = BOS.routing_sequence_id
1037 and BOS.effectivity_date <= x_routing_revision_date
1038 and NVL(BOS.operation_type, 1) = 1
1039 and NVL(BOS.disable_date, x_routing_revision_date+ 2) >= x_routing_revision_date
1040 and (bos.operation_sequence_id in ((select bon.from_op_seq_id from bom_operation_sequences bos1, BOM_OPERATION_NETWORKS_V BON
1041 where bon.transition_type=1
1042 and bon.routing_sequence_id=BRTG.common_routing_sequence_id )
1043 UNION ALL
1044 (select bon.to_op_seq_id from BOM_OPERATION_NETWORKS_V BON
1045 where bon.transition_type=1
1046 and bon.routing_sequence_id=BRTG.common_routing_sequence_id )))
1047 ORDER BY BOS.operation_seq_num;
1048
1049 BEGIN
1050
1051 l_stmt_num := 130;
1052
1053 SELECT max(resource_seq_num)
1054 INTO x_max_resc_seq_num
1055 FROM WIP_OPERATION_RESOURCES
1056 WHERE wip_entity_id = p_wip_entity_id
1057 and organization_id = p_organization_id
1058 and operation_seq_num = x_operation_seq_num;
1059
1060 if x_max_resc_seq_num is null then
1061 x_max_resc_seq_num := 0;
1062 end if;
1063
1064 begin
1065
1066 l_stmt_num := 140;
1067
1068 SELECT last_update_date, last_updated_by, creation_date,
1069 created_by, last_update_login, request_id,
1070 program_application_id, program_id, program_update_date
1071 INTO x_last_update_date, x_last_updated_by, x_creation_date,
1072 x_created_by, x_last_update_login, x_request_id,
1073 x_program_application_id, x_program_id, x_program_update_date
1074 FROM WIP_OPERATION_RESOURCES
1075 WHERE wip_entity_id = p_wip_entity_id
1076 and organization_id = p_organization_id
1077 and resource_seq_num = x_max_resc_seq_num
1078 and operation_seq_num = x_operation_seq_num;
1079
1080 exception
1081 when no_data_found then
1082 x_last_update_date := SYSDATE;
1083 x_last_updated_by := FND_GLOBAL.USER_ID ;
1084 x_creation_date := SYSDATE;
1085 x_created_by := FND_GLOBAL.USER_ID;
1086 x_last_update_login := FND_GLOBAL.LOGIN_ID;
1087 x_request_id := FND_GLOBAL.CONC_REQUEST_ID;
1088 x_program_application_id := FND_GLOBAL.PROG_APPL_ID;
1089 x_program_id := FND_GLOBAL.CONC_PROGRAM_ID;
1090 x_program_update_date := SYSDATE;
1091
1092 end;
1093
1094 /* --------------------------------------------------------- *
1095 * GO through the cursor. Populate phantom resources *
1096 * information to WIP_OPERATION_RESOURCES *
1097 * ----------------------------------------------------------*/
1098 l_stmt_num := 160;
1099 FOR cur_resc IN phan_resc_cursor LOOP
1100 /* set resource_seq_num to be unique */
1101 x_max_resc_seq_num := x_max_resc_seq_num + 10;
1102
1103 /* get UOM_code */
1104
1105 l_stmt_num := 170;
1106
1107 select unit_of_measure
1108 into x_uom_code
1109 from BOM_RESOURCES
1110 where resource_id = cur_resc.resource_id;
1111
1112 l_stmt_num := 180;
1113
1114 /* insert phantom resources */
1115 INSERT INTO WIP_OPERATION_RESOURCES(
1116 wip_entity_id,
1117 operation_seq_num,
1118 resource_seq_num,
1119 organization_id,
1120 repetitive_schedule_id,
1121 last_update_date,
1122 last_updated_by,
1123 creation_date,
1124 created_by,
1125 last_update_login,
1126 request_id,
1127 program_application_id,
1128 program_id,
1129 program_update_date,
1130 resource_id,
1131 uom_code,
1132 basis_type,
1133 usage_rate_or_amount,
1134 activity_id,
1135 scheduled_flag,
1136 assigned_units,
1137 autocharge_type,
1138 standard_rate_flag,
1139 applied_resource_units,
1140 applied_resource_value,
1141 start_date,
1142 completion_date,
1143 department_id,
1144 phantom_flag,
1145 phantom_op_seq_num,
1146 phantom_item_id,
1147 schedule_seq_num, --bugfix 2493065
1148 substitute_group_num,
1149 principle_flag,
1150 setup_id )
1151 VALUES(
1152 p_wip_entity_id,
1153 x_operation_seq_num,
1154 x_max_resc_seq_num,
1155 p_organization_id,
1156 NULL, --DECODE(p_sched_id, 0, null, p_sched_id),
1157 x_last_update_date,
1158 x_last_updated_by,
1159 x_creation_date,
1160 x_created_by,
1161 x_last_update_login,
1162 x_request_id,
1163 x_program_application_id,
1164 x_program_id,
1165 x_program_update_date,
1166 cur_resc.resource_id,
1167 x_uom_code,
1168 cur_resc.basis_type,
1169 cur_resc.usage_rate_or_amount,
1170 cur_resc.activity_id,
1171 2, /* non-scheduled */
1172 cur_resc.assigned_units,
1173 cur_resc.autocharge_type,
1174 cur_resc.standard_rate_flag,
1175 x_applied_resource_units,
1176 x_applied_resource_value,
1177 x_start_date,
1178 x_completion_date,
1179 cur_resc.department_id,
1180 1, /* phantom_flag = YES */
1181 cur_resc.phantom_op_seq_num,
1182 cur_resc.phantom_item_id,
1183 cur_resc.schedule_seq_num, --bugfix 2493065
1184 cur_resc.substitute_group_num,
1185 cur_resc.principle_flag,
1186 cur_resc.setup_id);
1187
1188 END LOOP;
1189 END;
1190 END IF;
1191
1192
1193 --Start NL BugFix 2786476
1194
1195 WSMPWROT.EXPLOSION_GROUP_ID := NULL;
1196
1197 --End NL BugFix 2786476
1198
1199 --delete from wip_requirement_operations where wip_entity_id=p_wip_entity_id and operation_seq_num<0;
1200
1201 EXCEPTION
1202
1203 WHEN be_exploder_exception THEN
1204 x_err_code := l_error_code;
1205 x_err_msg := substr('WSMPWROT ('||to_char(l_stmt_num)||')' || l_error_msg,1,2000);
1206 x_err_msg := 'WSMWROT stop';
1207
1208
1209 WHEN MY_EXCEPTION THEN
1210 x_err_code := -199;
1211 x_err_msg := 'WSMPWROT ('||to_char(l_stmt_num)||') '||x_operation_seq_num; /*NO_OF_COMP||' '
1212 ||t_comp_details(0).segment1||' '
1213 ||t_comp_details(1).segment1||' '||t_comp_details(2).segment1||' '||t_comp_details(3).segment1||' '
1214 ||t_comp_details(4).segment1||' '||t_comp_details(5).segment1||' '||t_comp_details(6).segment1||' '
1215 ||t_comp_details(7).segment1; */
1216
1217 WHEN loop_in_bom_exception THEN
1218 x_err_code := -1;
1219 FND_MESSAGE.SET_NAME('BOM', 'BOM_LOOP_EXISTS');
1220 x_err_msg := FND_MESSAGE.GET;
1221
1222 WHEN OTHERS THEN
1223 x_err_code := SQLCODE;
1224 x_err_msg := substr('WSMPWROT ('||to_char(l_stmt_num)||')' || SQLERRM,1,2000);
1225
1226
1227 END populate_wro;
1228
1229 FUNCTION GET_EXPLOSION_GROUP_ID
1230 RETURN NUMBER IS
1231 BEGIN
1232 return WSMPWROT.EXPLOSION_GROUP_ID;
1233 END;
1234
1235 PROCEDURE SET_EXPLOSION_GROUP_ID_NULL
1236 IS
1237 BEGIN
1238 WSMPWROT.EXPLOSION_GROUP_ID := NULL;
1239 WSMPWROT.USE_PHANTOM_ROUTINGS := NULL;
1240 END;
1241
1242 END WSMPWROT;