[Home] [Help]
PACKAGE BODY: APPS.WIP_BOMROUTINGUTIL_PVT
Source
1 package body wip_bomRoutingUtil_pvt as
2 /* $Header: wipbmrub.pls 120.14.12010000.5 2008/11/19 09:14:15 sisankar ship $ */
3
4 g_pkgName constant varchar2(30) := 'wip_bomRoutingUtil_pvt';
5
6 procedure explodeRouting(p_orgID in number,
7 p_wipEntityID in number,
8 p_repSchedID in number,
9 p_itemID in number,
10 p_altRouting in varchar2,
11 p_routingRevDate in date,
12 p_qty in number,
13 p_startDate in date,
14 p_endDate in date,
15 x_serStartOp out nocopy number,
16 x_returnStatus out nocopy varchar2,
17 x_errorMsg out nocopy varchar2) is
18 l_params wip_logger.param_tbl_t;
19 l_procName varchar2(30) := 'explodeRouting';
20 l_logLevel number := to_number(fnd_log.g_current_runtime_level);
21 l_retStatus varchar2(1);
22 l_msg varchar2(240);
23
24 l_routingSeqID number;
25 l_excludeECO number;
26 l_maxSeq number;
27
28 cursor op_attachments(p_org_id number, p_wip_entity_id number) is
29 select wo.operation_seq_num,
30 wo.operation_sequence_id
31 from wip_operations wo
32 where wo.organization_id = p_org_id
33 and wo.wip_entity_id = p_wip_entity_id
34 and exists (select fad.pk1_value
35 from fnd_attached_documents fad
36 where fad.pk1_value = to_char(wo.operation_sequence_id)
37 and fad.entity_name = 'BOM_OPERATION_SEQUENCES');
38 begin
39 x_returnStatus := fnd_api.g_ret_sts_success;
40 if (l_logLevel <= wip_constants.trace_logging) then
41 l_params(1).paramName := 'p_orgID';
42 l_params(1).paramValue := p_orgID;
43 l_params(2).paramName := 'p_wipEntityID';
44 l_params(2).paramValue := p_wipEntityID;
45 l_params(3).paramName := 'p_repSchedID';
46 l_params(3).paramValue := p_repSchedID;
47 l_params(4).paramName := 'p_itemID';
48 l_params(4).paramValue := p_itemID;
49 l_params(5).paramName := 'p_altRouting';
50 l_params(5).paramValue := p_altRouting;
51 l_params(6).paramName := 'p_routingRevDate';
52 l_params(6).paramValue := p_routingRevDate;
53 l_params(7).paramName := 'p_qty';
54 l_params(7).paramValue := p_qty;
55 l_params(8).paramName := 'p_startDate';
56 l_params(8).paramValue := p_startDate;
57 l_params(9).paramName := 'p_endDate';
58 l_params(9).paramValue := p_endDate;
59 wip_logger.entryPoint(p_procName => g_pkgName || '.' || l_procName,
60 p_params => l_params,
61 x_returnStatus => x_returnStatus);
62 if(x_returnStatus <> fnd_api.g_ret_sts_success) then
63 raise fnd_api.g_exc_unexpected_error;
64 end if;
65 end if;
66
67 begin
68 select common_routing_sequence_id,
69 serialization_start_op
70 into l_routingSeqID,
71 x_serStartOp
72 from bom_operational_routings
73 where organization_id = p_orgID
74 and assembly_item_id = p_itemID
75 and nvl(alternate_routing_designator, '@@@^@@@') = nvl(p_altRouting, '@@@^@@@')
76 and nvl(cfm_routing_flag, 2) = 2;
77 exception
78 when NO_DATA_FOUND then
79 if(l_logLevel <= wip_constants.trace_logging) then
80 wip_logger.exitPoint(p_procName => g_pkgName || '.' || l_procName,
81 p_procReturnStatus => 'true',
82 p_msg => 'this item does not have a routing',
83 x_returnStatus => l_retStatus);
84 wip_logger.cleanup(l_retStatus);
85 end if;
86 return;
87 end;
88
89 l_excludeECO := fnd_profile.value('WIP_RELEASED_REVS');
90
91 if ( l_logLevel <= wip_constants.full_logging ) then
92 wip_logger.log('exclude ECO is: ' || l_excludeECO, l_retStatus);
93 wip_logger.log('RoutingSeqID is: ' || l_routingSeqID, l_retStatus);
94 end if;
95
96 insert into wip_operations
97 (wip_entity_id,
98 operation_seq_num,
99 organization_id,
100 repetitive_schedule_id,
101 last_update_date,
102 last_updated_by,
103 creation_date,
104 created_by,
105 last_update_login,
106 request_id,
107 program_application_id,
108 program_id,
109 program_update_date,
110 operation_sequence_id,
111 department_id,
112 scheduled_quantity,
113 quantity_in_queue,
114 quantity_running,
115 quantity_waiting_to_move,
116 quantity_rejected,
117 quantity_scrapped,
118 quantity_completed,
119 cumulative_scrap_quantity,
120 count_point_type,
121 backflush_flag,
122 minimum_transfer_quantity,
123 first_unit_start_date,
124 first_unit_completion_date,
125 last_unit_start_date,
126 last_unit_completion_date,
127 standard_operation_id,
128 description,
129 long_description,
130 attribute_category,
131 attribute1,
132 attribute2,
133 attribute3,
134 attribute4,
135 attribute5,
136 attribute6,
137 attribute7,
138 attribute8,
139 attribute9,
140 attribute10,
141 attribute11,
142 attribute12,
143 attribute13,
144 attribute14,
145 attribute15,
146 check_skill)
147 select p_wipEntityID,
148 bos.operation_seq_num,
149 p_orgID,
150 p_repSchedID,
151 sysdate,
152 fnd_global.user_id,
153 sysdate,
154 fnd_global.user_id,
155 fnd_global.login_id,
156 fnd_global.conc_request_id,
157 fnd_global.prog_appl_id,
158 fnd_global.conc_program_id,
159 sysdate,
160 min(bos.operation_sequence_id),
161 bos.department_id,
162 round(p_qty, 6),
163 0, 0, 0, 0, 0, 0, 0,
164 bos.count_point_type,
165 bos.backflush_flag,
166 nvl(bos.minimum_transfer_quantity, 0),
167 p_startDate, p_endDate,
168 p_startDate, p_endDate,
169 bos.standard_operation_id,
170 bos.operation_description,
171 bos.long_description,
172 bos.attribute_category,
173 bos.attribute1,
174 bos.attribute2,
175 bos.attribute3,
176 bos.attribute4,
177 bos.attribute5,
178 bos.attribute6,
179 bos.attribute7,
180 bos.attribute8,
181 bos.attribute9,
182 bos.attribute10,
183 bos.attribute11,
184 bos.attribute12,
185 bos.attribute13,
186 bos.attribute14,
187 bos.attribute15,
188 nvl(bos.check_skill,2)
189 from bom_operation_sequences bos
190 where bos.routing_sequence_id = l_routingSeqID
191 and nvl(bos.operation_type, 1) = 1
192 and bos.effectivity_date <= p_routingRevDate
193 and nvl(bos.disable_date, p_routingRevDate+1) >= p_routingRevDate
194 and ( bos.implementation_date is not null
195 or exists (select 1
196 from eng_revised_items eng
197 where eng.change_notice = bos.change_notice
198 and eng.organization_id = p_orgID
199 and eng.routing_sequence_id = l_routingSeqID
200 and ((eng.status_type in (4, 7) and l_excludeECO = 1) or
201 (eng.status_type in (1, 4, 7) and l_excludeECO = 2))))
202 and not exists (select 1
203 from bom_operation_sequences bos2
204 where bos2.routing_sequence_id = bos.routing_sequence_id
205 and bos2.effectivity_date <= p_routingRevDate
206 and bos2.operation_seq_num = bos.operation_seq_num
207 and exists
208 (select 1
209 from eng_revised_items eng
210 where eng.change_notice = bos2.change_notice
211 and eng.organization_id = p_orgID
212 and eng.routing_sequence_id = l_routingSeqID
213 and ((eng.status_type in (4, 7) and l_excludeECO = 1) or
214 (eng.status_type in (1, 4, 7) and l_excludeECO = 2)))
215 and bos2.acd_type = 3)
216 group by bos.operation_seq_num,
217 bos.department_id, bos.count_point_type, bos.backflush_flag, bos.minimum_transfer_quantity,
218 p_orgID, p_wipEntityID, p_repSchedID, p_qty, p_startDate, p_endDate, sysdate, fnd_global.user_id,
219 fnd_global.login_id, fnd_global.conc_request_id, fnd_global.prog_appl_id,
220 fnd_global.conc_program_id, bos.standard_operation_id, bos.operation_description,
221 bos.long_description, bos.attribute_category, bos.attribute1, bos.attribute2,
222 bos.attribute3, bos.attribute4, bos.attribute5, bos.attribute6, bos.attribute7, bos.attribute8,
223 bos.attribute9, bos.attribute10, bos.attribute11, bos.attribute12, bos.attribute13,
224 bos.attribute14, bos.attribute15, bos.check_skill;
225
226
227 update wip_operations wo
228 set previous_operation_seq_num = (select max(operation_seq_num)
229 from wip_operations
230 where wip_entity_id = p_wipEntityID
231 and organization_id = p_orgID
232 and operation_seq_num < wo.operation_seq_num),
233 next_operation_seq_num = (select min(operation_seq_num)
234 from wip_operations
235 where wip_entity_id = p_wipEntityID
236 and organization_id = p_orgID
237 and operation_seq_num > wo.operation_seq_num)
238 where wo.wip_entity_id = p_wipEntityID
239 and wo.organization_id = p_orgID;
240
241 if ( l_logLevel <= wip_constants.full_logging ) then
242 wip_logger.log('begin to load resources', l_retStatus);
243 end if;
244
245
246 insert into wip_operation_resources
247 (wip_entity_id,
248 operation_seq_num,
249 resource_seq_num,
250 organization_id,
251 repetitive_schedule_id,
252 last_update_date,
253 last_updated_by,
254 creation_date,
255 created_by,
256 last_update_login,
257 request_id,
258 program_application_id,
259 program_id,
260 program_update_date,
261 resource_id,
262 uom_code,
263 basis_type,
264 usage_rate_or_amount,
265 activity_id,
266 scheduled_flag,
267 assigned_units,
268 autocharge_type,
269 standard_rate_flag,
270 applied_resource_units,
271 applied_resource_value,
272 start_date,
273 completion_date,
274 schedule_seq_num,
275 substitute_group_num,
276 replacement_group_num,
277 principle_flag,
278 setup_id,
279 attribute_category,
280 attribute1,
281 attribute2,
282 attribute3,
283 attribute4,
284 attribute5,
285 attribute6,
286 attribute7,
287 attribute8,
288 attribute9,
289 attribute10,
290 attribute11,
291 attribute12,
292 attribute13,
293 attribute14,
294 attribute15)
295 select p_wipEntityID,
296 bos.operation_seq_num,
297 bor.resource_seq_num,
298 p_orgID,
299 p_repSchedID,
300 sysdate,
301 fnd_global.user_id,
302 sysdate,
303 fnd_global.user_id,
304 fnd_global.login_id,
305 fnd_global.conc_request_id,
306 fnd_global.prog_appl_id,
307 fnd_global.conc_program_id,
308 sysdate,
309 bor.resource_id,
310 br.unit_of_measure,
311 bor.basis_type,
312 bor.usage_rate_or_amount,
313 bor.activity_id,
314 bor.schedule_flag,
315 bor.assigned_units,
316 bor.autocharge_type,
317 bor.standard_rate_flag,
318 0, 0,
319 p_startDate,
320 p_endDate,
321 bor.schedule_seq_num,
322 bor.substitute_group_num,
323 0,
324 bor.principle_flag,
325 bor.setup_id,
326 bor.attribute_category,
327 bor.attribute1,
328 bor.attribute2,
329 bor.attribute3,
330 bor.attribute4,
331 bor.attribute5,
332 bor.attribute6,
333 bor.attribute7,
334 bor.attribute8,
335 bor.attribute9,
336 bor.attribute10,
337 bor.attribute11,
338 bor.attribute12,
339 bor.attribute13,
340 bor.attribute14,
341 bor.attribute15
342 from bom_operation_sequences bos,
343 bom_operation_resources bor,
344 bom_resources br
345 where bos.routing_sequence_id = l_routingSeqID
349 and bor.resource_id = br.resource_id
346 and bos.effectivity_date <= p_routingRevDate
347 and nvl(bos.disable_date, p_routingRevDate+1) >= p_routingRevDate
348 and bos.operation_sequence_id = bor.operation_sequence_id
350 and nvl(bor.acd_type, 0) <> 3
351 and bos.effectivity_date =
352 (select max(effectivity_date)
353 from bom_operation_sequences bos2,
354 bom_operation_resources bor2
355 where bos2.routing_sequence_id = l_routingSeqID
356 and bos2.operation_sequence_id = bor2.operation_sequence_id
357 and bos2.operation_seq_num = bos.operation_seq_num
358 and bor2.resource_seq_num = bor.resource_seq_num
359 and nvl(bos2.operation_type, 1) = 1
360 and bos2.effectivity_date <= p_routingRevDate
361 and ( bos2.implementation_date is not null
362 or exists (select 1
363 from eng_revised_items eng
364 where eng.change_notice = bos2.change_notice
365 and eng.organization_id = p_orgID
366 and eng.routing_sequence_id = l_routingSeqID
367 and ((eng.status_type in (4, 7) and l_excludeECO = 1) or
368 (eng.status_type in (1, 4, 7) and l_excludeECO = 2)))));
369
370 if ( l_logLevel <= wip_constants.full_logging ) then
371 wip_logger.log('begin to load substitute resources', l_retStatus);
372 end if;
373
374 select max(resource_seq_num)
375 into l_maxSeq
376 from wip_operation_resources
377 where organization_id = p_orgID
378 and wip_entity_id = p_wipEntityID;
379
380
381 insert into wip_sub_operation_resources
382 (wip_entity_id,
383 operation_seq_num,
384 resource_seq_num,
385 organization_id,
386 repetitive_schedule_id,
387 last_update_date,
388 last_updated_by,
389 creation_date,
390 created_by,
391 last_update_login,
392 request_id,
393 program_application_id,
394 program_id,
395 program_update_date,
396 resource_id,
397 uom_code,
398 basis_type,
399 usage_rate_or_amount,
400 activity_id,
401 scheduled_flag,
402 assigned_units,
403 autocharge_type,
404 standard_rate_flag,
405 applied_resource_units,
406 applied_resource_value,
407 start_date,
408 completion_date,
409 schedule_seq_num,
410 substitute_group_num,
411 replacement_group_num,
412 principle_flag,
413 setup_id,
414 attribute_category,
415 attribute1,
416 attribute2,
417 attribute3,
418 attribute4,
419 attribute5,
420 attribute6,
421 attribute7,
422 attribute8,
423 attribute9,
424 attribute10,
425 attribute11,
426 attribute12,
427 attribute13,
428 attribute14,
429 attribute15)
430 select wo.wip_entity_id,
431 wo.operation_seq_num,
432 l_maxSeq + ROWNUM,
433 wo.organization_id,
434 wo.repetitive_schedule_id,
435 wo.last_update_date,
436 wo.last_updated_by,
437 wo.creation_date,
438 wo.created_by,
439 wo.last_update_login,
440 wo.request_id,
441 wo.program_application_id,
442 wo.program_id,
443 wo.program_update_date,
444 bsor.resource_id,
445 br.unit_of_measure,
446 bsor.basis_type,
447 bsor.usage_rate_or_amount,
448 bsor.activity_id,
449 bsor.schedule_flag,
450 bsor.assigned_units,
451 bsor.autocharge_type,
452 bsor.standard_rate_flag,
453 0, 0,
454 wo.first_unit_start_date,
455 wo.last_unit_completion_date,
456 bsor.schedule_seq_num,
457 bsor.substitute_group_num,
458 bsor.replacement_group_num,
459 bsor.principle_flag,
460 bsor.setup_id,
461 bsor.attribute_category,
462 bsor.attribute1,
463 bsor.attribute2,
464 bsor.attribute3,
465 bsor.attribute4,
466 bsor.attribute5,
467 bsor.attribute6,
468 bsor.attribute7,
469 bsor.attribute8,
470 bsor.attribute9,
471 bsor.attribute10,
472 bsor.attribute11,
473 bsor.attribute12,
474 bsor.attribute13,
475 bsor.attribute14,
476 bsor.attribute15
477 from bom_resources br,
478 bom_sub_operation_resources bsor,
479 wip_operations wo
480 where wo.organization_id = p_orgID
481 and wo.wip_entity_id = p_wipEntityID
482 and wo.operation_sequence_id = bsor.operation_sequence_id
486 if ( l_logLevel <= wip_constants.full_logging ) then
483 and bsor.resource_id = br.resource_id
484 and nvl(bsor.acd_type, 0) <> 3;
485
487 wip_logger.log('begin to load attachment', l_retStatus);
488 end if;
489
490
491 FOR op_attach IN op_attachments(p_orgID, p_wipEntityID) LOOP
492 fnd_attached_documents2_pkg.copy_attachments(
493 x_from_entity_name => 'BOM_OPERATION_SEQUENCES',
494 x_from_pk1_value => to_char(op_attach.operation_sequence_id),
495 x_to_entity_name => 'WIP_DISCRETE_OPERATIONS',
496 x_to_pk1_value => to_char(p_wipEntityID),
497 x_to_pk2_value => to_char(op_attach.operation_seq_num),
498 x_to_pk3_value => to_char(p_orgID),
499 x_created_by => fnd_global.user_id,
500 x_last_update_login => fnd_global.login_id,
501 x_program_application_id => fnd_global.prog_appl_id,
502 x_program_id => fnd_global.conc_program_id,
503 x_request_id => fnd_global.conc_request_id);
504 END LOOP;
505
506 /* Added for 12.1.1 Skills Validation project.*/
507 if ( l_logLevel <= wip_constants.full_logging ) then
508 wip_logger.log('begin to load competence', l_retStatus);
509 end if;
510
511 DELETE FROM WIP_OPERATION_COMPETENCIES
512 WHERE WIP_ENTITY_ID = p_wipEntityID
513 AND ORGANIZATION_ID = p_orgID;
514
515
516 INSERT INTO WIP_OPERATION_COMPETENCIES
517 (LEVEL_ID, ORGANIZATION_ID,
518 WIP_ENTITY_ID, OPERATION_SEQ_NUM, OPERATION_SEQUENCE_ID,
519 STANDARD_OPERATION_ID, COMPETENCE_ID, RATING_LEVEL_ID,
520 QUALIFICATION_TYPE_ID, LAST_UPDATE_DATE, LAST_UPDATED_BY,
521 LAST_UPDATE_LOGIN, CREATED_BY, CREATION_DATE)
522 SELECT
523 3, WO.ORGANIZATION_ID,
524 WO.WIP_ENTITY_ID, WO.OPERATION_SEQ_NUM, BOS.OPERATION_SEQUENCE_ID,
525 BOS.STANDARD_OPERATION_ID, BOS.COMPETENCE_ID, BOS.RATING_LEVEL_ID,
526 BOS.QUALIFICATION_TYPE_ID, WO.LAST_UPDATE_DATE, WO.LAST_UPDATED_BY,
527 WO.LAST_UPDATE_LOGIN, WO.CREATED_BY, WO.CREATION_DATE
528 FROM BOM_OPERATION_SKILLS BOS,
529 WIP_OPERATIONS WO,
530 WIP_ENTITIES WE
531 WHERE
532 WE.WIP_ENTITY_ID = WO.WIP_ENTITY_ID
533 AND WO.ORGANIZATION_ID = WO.ORGANIZATION_ID
534 AND WE.ENTITY_TYPE = 1
535 AND WO.ORGANIZATION_ID = p_orgID
536 AND WO.WIP_ENTITY_ID = p_wipEntityID
537 AND WO.ORGANIZATION_ID = BOS.ORGANIZATION_ID
538 AND BOS.OPERATION_SEQUENCE_ID = WO.OPERATION_SEQUENCE_ID
539 AND BOS.LEVEL_ID = 2;
540
541 if (l_logLevel <= wip_constants.trace_logging) then
542 wip_logger.exitPoint(p_procName => g_pkgName || '.' || l_procName,
543 p_procReturnStatus => x_returnStatus,
544 p_msg => 'success',
545 x_returnStatus => l_retStatus);
546 end if;
547 exception
548 when others then
549 if(l_logLevel <= wip_constants.trace_logging) then
550 wip_logger.exitPoint(p_procName => g_pkgName || '.' || l_procName,
551 p_procReturnStatus => x_returnStatus,
552 p_msg => 'unexp error:' || SQLERRM,
553 x_returnStatus => l_retStatus);
554 end if;
555 x_returnStatus := fnd_api.g_ret_sts_unexp_error;
556 fnd_msg_pub.add_exc_msg(p_pkg_name => g_pkgName,
557 p_procedure_name => l_procName,
558 p_error_text => SQLERRM);
559 wip_utilities.get_message_stack(p_msg => l_msg);
560 x_errorMsg := substrb(l_msg, 1, 240);
561 end explodeRouting;
562
563
564 procedure explodeBOM(p_orgID in number,
565 p_wipEntityID in number,
566 p_jobType in number,
567 p_repSchedID in number,
568 p_itemID in number,
569 p_altBOM in varchar2,
570 p_bomRevDate in date,
571 p_altRouting in varchar2,
572 p_routingRevDate in date,
573 p_qty in number,
574 p_jobStartDate in date,
575 p_projectID in number,
576 p_taskID in number,
577 p_unitNumber in varchar2 DEFAULT '', /* added for bug 5332615 */
578 x_returnStatus out nocopy varchar2,
579 x_errorMsg out nocopy varchar2) is
580 l_procName varchar2(30) := 'explodeBOM';
581 l_params wip_logger.param_tbl_t;
582 l_logLevel number := to_number(fnd_log.g_current_runtime_level);
583 l_retStatus varchar2(1);
584 l_msg varchar2(240);
585
586 l_compTbl system.wip_component_tbl_t;
587 l_mrpFlag number;
588 l_count number;
589
590 l_entityType number;
591 l_usePhantomRouting number;
592 l_minOp number;
593 l_exists number;
594 l_opSeq number;
595 l_multipleFactor number;
596
597 l_diff_basis number;
598 l_basis number;
599 l_wro_op number;
600
601 cursor c_phantoms is
602 select inventory_item_id,
606 and wip_entity_id = p_wipEntityID
603 -1*operation_seq_num operation_seq_num
604 from wip_requirement_operations
605 where organization_id = p_orgID
607 and nvl(repetitive_schedule_id, -1) = nvl(p_repSchedID, -1)
608 and operation_seq_num < 0
609 and wip_supply_type = wip_constants.phantom;
610
611 begin
612 x_returnStatus := fnd_api.g_ret_sts_success;
613 if (l_logLevel <= wip_constants.trace_logging) then
614 l_params(1).paramName := 'p_orgID';
615 l_params(1).paramValue := p_orgID;
616 l_params(2).paramName := 'p_wipEntityID';
617 l_params(2).paramValue := p_wipEntityID;
618 l_params(3).paramName := 'p_jobType';
619 l_params(3).paramValue := p_jobType;
620 l_params(4).paramName := 'p_repSchedID';
621 l_params(4).paramValue := p_repSchedID;
622 l_params(5).paramName := 'p_itemID';
623 l_params(5).paramValue := p_itemID;
624 l_params(6).paramName := 'p_altBOM';
625 l_params(6).paramValue := p_altBOM;
626 l_params(7).paramName := 'p_bomRevDate';
627 l_params(7).paramValue := p_bomRevDate;
628 l_params(8).paramName := 'p_altRouting';
629 l_params(8).paramValue := p_altRouting;
630 l_params(9).paramName := 'p_routingRevDate';
631 l_params(9).paramValue := p_routingRevDate;
632 l_params(10).paramName := 'p_qty';
633 l_params(10).paramValue := p_qty;
634 l_params(11).paramName := 'p_jobStartDate';
635 l_params(11).paramValue := p_jobStartDate;
636 l_params(12).paramName := 'p_projectID';
637 l_params(12).paramValue := p_projectID;
638 l_params(13).paramName := 'p_taskID';
639 l_params(13).paramValue := p_taskID;
640 wip_logger.entryPoint(p_procName => g_pkgName || '.' || l_procName,
641 p_params => l_params,
642 x_returnStatus => x_returnStatus);
643 if(x_returnStatus <> fnd_api.g_ret_sts_success) then
644 raise fnd_api.g_exc_unexpected_error;
645 end if;
646 end if;
647
648 l_entityType := wip_constants.discrete;
649 if ( p_repSchedID is not null ) then
650 l_entityType := wip_constants.repetitive;
651 end if;
652
653 l_mrpFlag := wip_constants.yes;
654 if ( p_jobType = wip_constants.nonstandard ) then
655 l_mrpFlag := wip_constants.no;
656 end if;
657
658 /*
659 wip_bflProc_priv.explodeRequirements(
660 p_itemID => p_itemID,
661 p_orgID => p_orgID,
662 p_qty => 1,
663 p_altBomDesig => p_altBOM,
664 p_altOption => 2,
665 p_bomRevDate => p_bomRevDate,
666 p_txnDate => null,
667 p_projectID => p_projectID,
668 p_taskID => p_taskID,
669 p_initMsgList => fnd_api.g_false,
670 p_endDebug => fnd_api.g_false,
671 x_compTbl => l_compTbl,
672 x_returnStatus => x_returnStatus);
673 */
674
675 wip_flowUtil_priv.explodeRequirementsAndDefault(
676 p_assyID => p_itemID,
677 p_orgID => p_orgID,
678 p_qty => 1,
679 p_altBomDesig => p_altBOM,
680 p_altOption => 2,
681 p_bomRevDate => p_bomRevDate,
682 p_txnDate => p_jobStartDate,
683 p_implFlag => 2, /* for bug 5383135 */
684 p_projectID => p_projectID,
685 p_taskID => p_taskID,
686 p_toOpSeqNum => null,
687 p_altRoutDesig => p_altRouting,
688 p_txnFlag => false, /* for bug4538135 */ /* ER 4369064 */
689 p_unitNumber => p_unitNumber, /* added for bug 5332615 */
690 x_compTbl => l_compTbl,
691 x_returnStatus => x_returnStatus);
692 if ( x_returnStatus <> fnd_api.g_ret_sts_success ) then
693 raise fnd_api.g_exc_unexpected_error;
694 end if;
695
696 begin
697 select nvl(min(operation_seq_num), 1)
698 into l_minOp
699 from wip_operations
700 where wip_entity_id = p_wipEntityID;
701 exception
702 when others then
703 l_minOp := 1;
704 end;
705
706 l_count := l_compTbl.first;
707 while ( l_count is not null ) loop
708 -- here, we need to make sure that the op exists. This might happen if the user use an alternate
709 -- routing to create the job so the op exploded might not exists. In that case, we will assign this component
710 -- to the first operation
711 begin
712 select 1 into l_exists
713 from wip_operations
714 where wip_entity_id = p_wipEntityID
715 and operation_seq_num = l_compTbl(l_count).operation_seq_num;
716 l_opSeq := l_compTbl(l_count).operation_seq_num;
717 exception
718 when others then
719 l_opSeq := l_minOp;
720 end;
721
722 if( l_compTbl(l_count).basis_type = WIP_CONSTANTS.LOT_BASED_MTL) then
723 l_multipleFactor := 1 ;
724 else
728 /* Fix for bug 4703486. If no rtg exists, Op pull components should become Assy Pull */
725 l_multipleFactor := p_qty ;
726 end if;
727
729 if (l_opSeq = 1 AND l_compTbl(l_count).wip_supply_type = WIP_CONSTANTS.OP_PULL) then
730 l_compTbl(l_count).wip_supply_type := WIP_CONSTANTS.ASSY_PULL;
731 end if;
732
733
734 /* bug 4688276 - if the same component in op 1 and op 10, for example, we'll try
735 to merge the qties into op 10, as long as their baisis type is the same */
736 if( l_compTbl(l_count).wip_supply_type = wip_constants.phantom) then
737 l_wro_op := -1*l_opSeq;
738 else
739 l_wro_op := l_opSeq;
740 end if;
741
742 select count(distinct nvl(basis_type, 1)), min(distinct nvl(basis_type, 1) )
743 into l_diff_basis, l_basis
744 from wip_requirement_operations wro
745 where wro.inventory_item_id = l_compTbl(l_count).inventory_item_id
746 and wro.organization_id = p_orgID
747 and wro.wip_entity_id = p_wipEntityID
748 and wro.operation_seq_num = l_wro_op;
749
750 if( l_diff_basis > 1 ) then
751 raise fnd_api.g_exc_unexpected_error;
752 elsif( l_diff_basis = 1 ) then
753 if( (l_compTbl(l_count).basis_type is null and l_basis = 1 )
754 or l_compTbl(l_count).basis_type = l_basis ) then
755 update wip_requirement_operations wro
756 set wro.quantity_per_assembly = round( l_compTbl(l_count).primary_quantity + wro.quantity_per_assembly,
757 wip_constants.max_displayed_precision),
758 wro.required_quantity = round( round(l_compTbl(l_count).primary_quantity,
759 wip_constants.max_displayed_precision)
760 *l_multipleFactor/l_compTbl(l_count).component_yield_factor,
761 wip_constants.max_displayed_precision) + wro.required_quantity
762 where wro.inventory_item_id = l_compTbl(l_count).inventory_item_id
763 and wro.organization_id = p_orgID
764 and wro.wip_entity_id = p_wipEntityID
765 and wro.operation_seq_num = l_wro_op;
766
767 update wip_requirement_operations wro
768 /*Fix for bug 7486594*/
769 set wro.component_yield_factor = decode(wro.quantity_per_assembly,0,1,round( wro.quantity_per_assembly * l_multipleFactor / wro.required_quantity,
770 wip_constants.max_displayed_precision))
771 where wro.inventory_item_id = l_compTbl(l_count).inventory_item_id
772 and wro.organization_id = p_orgID
773 and wro.wip_entity_id = p_wipEntityID
774 and wro.operation_seq_num = l_wro_op;
775 else
776 x_returnStatus := fnd_api.g_ret_sts_error;
777 fnd_message.set_name('WIP', 'WIP_COMP_DUP_OP_ONE');
778 fnd_msg_pub.add;
779 if (l_logLevel <= wip_constants.full_logging) then
780 wip_logger.log(p_msg => 'Item ' || l_compTbl(l_count).inventory_item_id ||
781 ' has duplicates in op 1, failed explosion!',
782 x_returnStatus => x_returnStatus);
783
784
785 end if;
786 return;
787 end if;
788 else /* --> end of bug fix 4688276 */
789
790
791 insert into wip_requirement_operations
792 (inventory_item_id,
793 organization_id,
794 wip_entity_id,
795 operation_seq_num,
796 repetitive_schedule_id,
797 last_update_date,
798 last_updated_by,
799 creation_date,
800 created_by,
801 last_update_login,
802 request_id,
803 program_application_id,
804 program_id,
805 program_update_date,
806 component_sequence_id,
807 wip_supply_type,
808 date_required,
809 required_quantity,
810 quantity_issued,
811 quantity_per_assembly,
812 component_yield_factor, /*For Component Yield Enhancement(Bug 4369064) */
813 basis_type,
814 supply_subinventory,
815 supply_locator_id,
816 mrp_net_flag)
817 values(
818 l_compTbl(l_count).inventory_item_id,
819 p_orgID,
820 p_wipEntityID,
821 decode(l_compTbl(l_count).wip_supply_type,
822 wip_constants.phantom, -1*l_opSeq, l_opSeq),
823 null,
824 sysdate,
825 fnd_global.user_id,
826 sysdate,
827 fnd_global.user_id,
828 fnd_global.login_id,
829 fnd_global.conc_request_id,
830 fnd_global.prog_appl_id,
831 fnd_global.conc_program_id,
832 sysdate,
833 l_compTbl(l_count).component_sequence_id,
834 l_compTbl(l_count).wip_supply_type,
835 p_jobStartDate,
836 round(round(l_compTbl(l_count).primary_quantity, wip_constants.max_displayed_precision)*l_multipleFactor/
837 l_compTbl(l_count).component_yield_factor, wip_constants.max_displayed_precision),
838 /*For Component Yield Enhancement(Bug 4369064)->Always need to consider yield factor*/
839 0,
843 l_compTbl(l_count).supply_subinventory,
840 round(l_compTbl(l_count).primary_quantity, wip_constants.max_displayed_precision),
841 l_compTbl(l_count).component_yield_factor, /*For Component Yield Enhancement(Bug 4369064) */
842 decode(l_compTbl(l_count).basis_type,WIP_CONSTANTS.LOT_BASED_MTL,2,NULL),
844 l_compTbl(l_count).supply_locator_id,
845 decode(l_compTbl(l_count).wip_supply_type, 5, 2,
846 decode(sign(l_compTbl(l_count).primary_quantity), -1, 2, l_mrpFlag)));
847
848 end if; /* end insert */
849
850 l_count := l_compTbl.next(l_count);
851 end loop;
852
853 l_usePhantomRouting := wip_globals.use_phantom_routings(p_orgID);
854 if ( l_usePhantomRouting = wip_constants.yes ) then
855 for phan in c_phantoms loop
856 wip_explode_phantom_rtgs.explode_resources(
857 p_wip_entity_id => p_wipEntityID,
858 p_sched_id => p_repSchedID,
859 p_org_id => p_orgID,
860 p_entity_type => l_entityType,
861 p_phantom_item_id => phan.inventory_item_id,
862 p_op_seq_num => phan.operation_seq_num,
863 p_rtg_rev_date => p_routingRevDate);
864 end loop;
865 end if;
866
867 -- bug 5527438 added call to the following API to enable defaulting of supply subinventory
868 -- and locator from the resource definition.
869
870 wip_picking_pvt.Post_Explosion_CleanUp( p_wip_entity_id => p_wipEntityID,
871 p_repetitive_schedule_id => null,
872 p_org_id => p_orgID,
873 x_return_status => x_returnStatus,
874 x_msg_data => x_errorMsg );
875
876 if (x_returnStatus <> fnd_api.g_ret_sts_success) then
877 if (l_logLevel <= wip_constants.full_logging) then
878 wip_logger.log(p_msg => 'Post_Explosion_Cleanup failed for wip_entity_id '||p_wipEntityID,
879 x_returnStatus => x_returnStatus);
880 end if;
881 return;
882 end if;
883
884 -- bug 5527438 end of changes for this fix
885
886 update wip_requirement_operations wro
887 set (date_required,
888 department_id,
889 wip_supply_type) =
890 (select nvl(max(wo.first_unit_start_date), wro.date_required),
891 max(department_id),
892 decode(wro.wip_supply_type, wip_constants.assy_pull,
893 decode(nvl(max(wo.count_point_type), 0),
894 wip_constants.no_manual, wip_constants.op_pull,
895 wro.wip_supply_type),
896 wro.wip_supply_type)
897 from wip_operations wo
898 where wo.organization_id = wro.organization_id
899 and wo.wip_entity_id = wro.wip_entity_id
900 and nvl(wo.repetitive_schedule_id, -1) = nvl(wro.repetitive_schedule_id, -1)
901 and wo.operation_seq_num = abs(wro.operation_seq_num)),
902 (comments,
903 attribute_category,
904 attribute1,
905 attribute2,
906 attribute3,
907 attribute4,
908 attribute5,
909 attribute6,
910 attribute7,
911 attribute8,
912 attribute9,
913 attribute10,
914 attribute11,
915 attribute12,
916 attribute13,
917 attribute14,
918 attribute15) =
919 (select bic.component_remarks,
920 bic.attribute_category,
921 bic.attribute1,
922 bic.attribute2,
923 bic.attribute3,
924 bic.attribute4,
925 bic.attribute5,
926 bic.attribute6,
927 bic.attribute7,
928 bic.attribute8,
929 bic.attribute9,
930 bic.attribute10,
931 bic.attribute11,
932 bic.attribute12,
933 bic.attribute13,
934 bic.attribute14,
935 bic.attribute15
936 from bom_inventory_components bic
937 where bic.component_sequence_id = wro.component_sequence_id),
938 (segment1,
939 segment2,
940 segment3,
941 segment4,
942 segment5,
943 segment6,
944 segment7,
945 segment8,
946 segment9,
947 segment10,
948 segment11,
949 segment12,
950 segment13,
951 segment14,
952 segment15,
953 segment16,
954 segment17,
955 segment18,
956 segment19,
957 segment20) =
958 (select msi.segment1,
959 msi.segment2,
960 msi.segment3,
961 msi.segment4,
962 msi.segment5,
963 msi.segment6,
964 msi.segment7,
965 msi.segment8,
966 msi.segment9,
967 msi.segment10,
968 msi.segment11,
969 msi.segment12,
970 msi.segment13,
971 msi.segment14,
972 msi.segment15,
973 msi.segment16,
974 msi.segment17,
975 msi.segment18,
976 msi.segment19,
977 msi.segment20
978 from mtl_system_items msi
979 where msi.inventory_item_id = wro.inventory_item_id
980 and msi.organization_id = wro.organization_id)
981 where wro.wip_entity_id = p_wipEntityID
982 and nvl(wro.repetitive_schedule_id, -1) = nvl(p_repSchedID, -1)
983 and wro.organization_id = p_orgID;
984
985 if (l_logLevel <= wip_constants.trace_logging) then
986 wip_logger.exitPoint(p_procName => g_pkgName || '.' || l_procName,
987 p_procReturnStatus => x_returnStatus,
988 p_msg => 'success',
989 x_returnStatus => l_retStatus);
990 end if;
991 exception
992 when fnd_api.g_exc_unexpected_error then
993 if (l_logLevel <= wip_constants.trace_logging) then
994 wip_logger.exitPoint(p_procName => g_pkgName || '.' || l_procName,
995 p_procReturnStatus => x_returnStatus,
996 p_msg => 'failed at exploding requirements',
997 x_returnStatus => l_retStatus); --discard logging return status
998 end if;
999 when others then
1000 if(l_logLevel <= wip_constants.trace_logging) then
1001 wip_logger.exitPoint(p_procName => g_pkgName || '.' || l_procName,
1002 p_procReturnStatus => x_returnStatus,
1003 p_msg => 'unexp error:' || SQLERRM,
1004 x_returnStatus => l_retStatus);
1005 end if;
1006 x_returnStatus := fnd_api.g_ret_sts_unexp_error;
1007 fnd_msg_pub.add_exc_msg(p_pkg_name => g_pkgName,
1008 p_procedure_name => l_procName,
1009 p_error_text => SQLERRM);
1010 wip_utilities.get_message_stack(p_msg => l_msg);
1011 x_errorMsg := substrb(l_msg, 1, 240);
1012 end explodeBOM;
1013
1014
1015 procedure adjustQtyChange(p_orgID in number,
1016 p_wipEntityID in number,
1017 p_qty in number,
1018 x_returnStatus out nocopy varchar2,
1019 x_errorMsg out nocopy varchar2) is
1020 l_params wip_logger.param_tbl_t;
1021 l_procName varchar2(30) := 'adjustQtyChange';
1022 l_logLevel number := to_number(fnd_log.g_current_runtime_level);
1023 l_msg varchar2(240);
1024 l_retStatus varchar2(1);
1025
1026 l_jobQty number;
1027 l_jobStatus number;
1028 l_minOp number;
1029 begin
1030
1031 x_returnStatus := fnd_api.g_ret_sts_success;
1032 if (l_logLevel <= wip_constants.trace_logging) then
1033 l_params(1).paramName := 'p_orgID';
1034 l_params(1).paramValue := p_orgID;
1035 l_params(2).paramName := 'p_wipEntityID';
1036 l_params(2).paramValue := p_wipEntityID;
1037 l_params(3).paramName := 'p_qty';
1038 l_params(3).paramValue := p_qty;
1039 wip_logger.entryPoint(p_procName => g_pkgName || '.' || l_procName,
1040 p_params => l_params,
1041 x_returnStatus => x_returnStatus);
1042 if(x_returnStatus <> fnd_api.g_ret_sts_success) then
1043 raise fnd_api.g_exc_unexpected_error;
1044 end if;
1045 end if;
1046
1047
1048 select start_quantity,
1049 status_type
1050 into l_jobQty,
1051 l_jobStatus
1052 from wip_discrete_jobs
1053 where organization_id = p_orgID
1054 and wip_entity_id = p_wipEntityID;
1055
1056 if ( p_qty is null or
1057 l_jobStatus not in (wip_constants.unreleased,
1058 wip_constants.released,
1059 wip_constants.comp_chrg,
1060 wip_constants.hold) ) then
1061 if(l_logLevel <= wip_constants.trace_logging) then
1062 wip_logger.exitPoint(p_procName => g_pkgName || '.' || l_procName,
1063 p_procReturnStatus => x_returnStatus,
1064 p_msg => 'no qty change or job status is not right',
1065 x_returnStatus => l_retStatus);
1066 end if;
1067 return;
1068 end if;
1069
1070
1071 if ( l_jobStatus <> wip_constants.unreleased ) then
1072 select nvl(min(operation_seq_num), fnd_api.g_miss_num)
1073 into l_minOp
1074 from wip_operations
1075 where organization_id = p_orgID
1076 and wip_entity_id = p_wipEntityID;
1077
1078 if ( l_minOp = fnd_api.g_miss_num ) then
1079 if(l_logLevel <= wip_constants.trace_logging) then
1080 wip_logger.exitPoint(p_procName => g_pkgName || '.' || l_procName,
1081 p_procReturnStatus => x_returnStatus,
1082 p_msg => 'no operation exist',
1086 end if;
1083 x_returnStatus => l_retStatus);
1084 end if;
1085 return;
1087
1088 update wip_operations
1089 set quantity_in_queue = quantity_in_queue - (scheduled_quantity - p_qty)
1090 where organization_id = p_orgID
1091 and wip_entity_id = p_wipEntityID
1092 and operation_seq_num = l_minOp
1093 and quantity_in_queue <> 0 ;/* Fix for Bug 6639146 */
1094
1095 /* Fix for bug 6954115 */
1096 update wip_operations
1097 set quantity_in_queue = p_qty -(quantity_completed+quantity_running+quantity_in_queue)
1098 where organization_id = p_orgID
1099 and wip_entity_id = p_wipEntityID
1100 and operation_seq_num = l_minOp
1101 and quantity_in_queue = 0
1102 and (quantity_completed+quantity_running+quantity_in_queue) < p_qty;
1103
1104 end if;
1105
1106 update wip_operations
1107 set scheduled_quantity = p_qty
1108 where organization_id = p_orgID
1109 and wip_entity_id = p_wipEntityID;
1110
1111 update wip_requirement_operations
1112 set required_quantity = decode(basis_type,
1113 2, /* basis is lot */
1114 round(quantity_per_assembly/nvl(component_yield_factor,1), 6),/*For Component Yield Enhancement(Bug 4369064) */
1115 round(quantity_per_assembly/nvl(component_yield_factor,1) * p_qty, 6))
1116 where organization_id = p_orgID
1117 and wip_entity_id = p_wipEntityID;
1118
1119 if (l_logLevel <= wip_constants.trace_logging) then
1120 wip_logger.exitPoint(p_procName => g_pkgName || '.' || l_procName,
1121 p_procReturnStatus => x_returnStatus,
1122 p_msg => 'success',
1123 x_returnStatus => l_retStatus);
1124 end if;
1125
1126 exception
1127 when others then
1128 if(l_logLevel <= wip_constants.trace_logging) then
1129 wip_logger.exitPoint(p_procName => g_pkgName || '.' || l_procName,
1130 p_procReturnStatus => x_returnStatus,
1131 p_msg => 'unexp error:' || SQLERRM,
1132 x_returnStatus => l_retStatus);
1133 end if;
1134 x_returnStatus := fnd_api.g_ret_sts_unexp_error;
1135 fnd_msg_pub.add_exc_msg(p_pkg_name => g_pkgName,
1136 p_procedure_name => l_procName,
1137 p_error_text => SQLERRM);
1138 wip_utilities.get_message_stack(p_msg => l_msg);
1139 x_errorMsg := substrb(l_msg, 1, 240);
1140 end adjustQtyChange;
1141
1142 end wip_bomRoutingUtil_pvt;