1 package body wip_bomRouting_pvt as
2 /* $Header: wipbmrtb.pls 120.23.12020000.4 2013/01/02 10:38:22 pding ship $ */
3
4 g_pkgName constant varchar2(30) := 'wip_bomRouting_pvt';
5
6 -- This procedure is used to explode the bom and routing and schedule the job if needed.
7 -- p_schedulingMethod: if the value is routing based, then you must provide one of the p_startDate
8 -- and p_endDate, you can not provide both, however.
9 -- if it is not routing based, then you must provide both.
10 -- p_startDate: forward schedule the job if it is not null
11 -- p_endDate: backward schedule the job it it is not null
12 -- p_rtgRefID: only useful when p_jobType is nonstandard
13 -- p_bomRefID: only useful when p_jobType is nonstandard
14 -- p_unitNumber: To explode components properly based on unit number for unit effective assemblies.
15 procedure createJob(p_orgID in number,
16 p_wipEntityID in number,
17 p_jobType in number,
18 p_itemID in number,
19 p_schedulingMethod in number,
20 p_altRouting in varchar2,
21 p_routingRevDate in date,
22 p_altBOM in varchar2,
23 p_bomRevDate in date,
24 p_qty in number,
25 p_startDate in date,
26 p_endDate in date,
27 p_projectID in number,
28 p_taskID in number,
29 p_rtgRefID in number,
30 p_bomRefID in number,
31 p_unitNumber in varchar2 DEFAULT '', /* added for bug 5332615 */
32 x_serStartOp out nocopy number,
33 x_returnStatus out nocopy varchar2,
34 x_errorMsg out nocopy varchar2) is
35 l_params wip_logger.param_tbl_t;
36 l_procName varchar2(30) := 'createJob';
37 l_logLevel number := to_number(fnd_log.g_current_runtime_level);
38 l_retStatus varchar2(1);
39 l_msg varchar2(240);
40
41 l_startDate date;
42 l_endDate date;
43 l_rtgItemID number;
44 l_bomItemID number;
45 begin
46 x_returnStatus := fnd_api.g_ret_sts_success;
47 if (l_logLevel <= wip_constants.trace_logging) then
48 l_params(1).paramName := 'p_orgID';
49 l_params(1).paramValue := p_orgID;
50 l_params(2).paramName := 'p_wipEntityID';
51 l_params(2).paramValue := p_wipEntityID;
52 l_params(3).paramName := 'p_jobType';
53 l_params(3).paramValue := p_jobType;
54 l_params(4).paramName := 'p_itemID';
55 l_params(4).paramValue := p_itemID;
56 l_params(5).paramName := 'p_schedulingMethod';
57 l_params(5).paramValue := p_schedulingMethod;
58 l_params(6).paramName := 'p_altRouting';
59 l_params(6).paramValue := p_altRouting;
60 l_params(7).paramName := 'p_routingRevDate';
61 l_params(7).paramValue := p_routingRevDate;
62 l_params(8).paramName := 'p_altBOM';
63 l_params(8).paramValue := p_altBOM;
64 l_params(9).paramName := 'p_bomRevDate';
65 l_params(9).paramValue := p_bomRevDate;
66 l_params(10).paramName := 'p_qty';
67 l_params(10).paramValue := p_qty;
68 l_params(11).paramName := 'p_startDate';
69 l_params(11).paramValue := p_startDate;
70 l_params(12).paramName := 'p_endDate';
71 l_params(12).paramValue := p_endDate;
72 l_params(13).paramName := 'p_projectID';
73 l_params(13).paramValue := p_projectID;
74 l_params(14).paramName := 'p_taskID';
75 l_params(14).paramValue := p_taskID;
76 l_params(15).paramName := 'p_rtgRefID';
77 l_params(15).paramValue := p_rtgRefID;
78 l_params(16).paramName := 'p_bomRefID';
79 l_params(16).paramValue := p_bomRefID;
80 wip_logger.entryPoint(p_procName => g_pkgName || '.' || l_procName,
81 p_params => l_params,
82 x_returnStatus => x_returnStatus);
83 if(x_returnStatus <> fnd_api.g_ret_sts_success) then
84 raise fnd_api.g_exc_unexpected_error;
85 end if;
86 end if;
87
88 if ( p_schedulingMethod = wip_constants.routing ) then
89 if ( (p_startDate is null and p_endDate is null) or
90 ((p_startDate is not null and p_endDate is not null) and
91 /* Bug 4515999 Non standard jobs cannot have both dates not null */
92 p_jobType = wip_constants.standard) ) then
93 x_errorMsg := 'Must only provide one of the p_startDate and p_endDate for routing based scheduling';
94 raise fnd_api.g_exc_unexpected_error;
95 else
96 -- populate some value and then we will reschedule it later on.
97 l_startDate := nvl(p_startDate, p_endDate);
98 l_endDate := l_startDate;
99 end if;
100 else
101 if ( p_startDate is null or p_endDate is null ) then
102 x_errorMsg := 'Must provide both of the dates if it is not routing based scheduling';
103 raise fnd_api.g_exc_unexpected_error;
104 else
105 l_startDate := p_startDate;
106 l_endDate := p_endDate;
107 end if;
108 end if;
109
110 if ( p_jobType = wip_constants.standard ) then
111 l_rtgItemID := p_itemID;
112 l_bomItemID := p_itemID;
113 else
114 l_rtgItemID := p_rtgRefID;
115 l_bomItemID := p_bomRefID;
116 end if;
117
118 wip_bomRoutingUtil_pvt.explodeRouting(p_orgID => p_orgID,
119 p_wipEntityID => p_wipEntityID,
120 p_repSchedID => null,
121 p_itemID => l_rtgItemID,
122 p_altRouting => p_altRouting,
123 p_routingRevDate => p_routingRevDate,
124 p_qty => p_qty,
125 p_startDate => l_startDate,
126 p_endDate => l_endDate,
127 x_serStartOp => x_serStartOp,
128 x_returnStatus => x_returnStatus,
129 x_errorMsg => x_errorMsg);
130 if ( x_returnStatus <> fnd_api.g_ret_sts_success ) then
131 raise fnd_api.g_exc_unexpected_error;
132 end if;
133
134 /* -- Bug 11893957. Call Infinite Scheduler after BOM Explosion to consider resources from Phantom Routings.
135 if ( p_schedulingMethod = wip_constants.routing ) then
136 wip_infinite_scheduler_pvt.schedule(
137 p_orgID => p_orgID,
138 p_wipEntityID => p_wipEntityID,
139 p_startDate => p_startDate,
140 p_endDate => p_endDate,
141 x_returnStatus => x_returnStatus,
142 x_errorMsg => x_errorMsg);
143 if ( x_returnStatus <> fnd_api.g_ret_sts_success ) then
144 raise fnd_api.g_exc_unexpected_error;
145 end if;
146 end if;
147
148 -- explode the bom
149 select wdj.scheduled_start_date,
150 wdj.scheduled_completion_date
151 into l_startDate, l_endDate
152 from wip_discrete_jobs wdj
153 where wdj.organization_id = p_orgID
154 and wdj.wip_entity_id = p_wipEntityID;
155 */
156
157 wip_bomRoutingUtil_pvt.explodeBOM(p_orgID => p_orgID,
158 p_wipEntityID => p_wipEntityID,
159 p_jobType => p_jobType,
160 p_repSchedID => null,
161 p_itemID => l_bomItemID,
162 p_altBOM => p_altBOM,
163 p_bomRevDate => p_bomRevDate,
164 p_altRouting => p_altRouting,
165 p_routingRevDate => p_routingRevDate,
166 p_qty => p_qty,
167 p_jobStartDate => l_startDate,
168 p_projectID => p_projectID,
169 p_taskID => p_taskID,
170 p_unitNumber => p_unitNumber, /* added for bug 5332615 */
171 x_returnStatus => x_returnStatus,
172 x_errorMsg => x_errorMsg);
173 if ( x_returnStatus <> fnd_api.g_ret_sts_success ) then
174 raise fnd_api.g_exc_unexpected_error;
175 end if;
176
177 -- Bug 11893957. Call Infinite Scheduler after BOM Explosion to consider resources from Phantom Routings.
178 if ( p_schedulingMethod = wip_constants.routing ) then
179 wip_infinite_scheduler_pvt.schedule(
180 p_orgID => p_orgID,
181 p_wipEntityID => p_wipEntityID,
182 p_startDate => p_startDate,
183 p_endDate => p_endDate,
184 x_returnStatus => x_returnStatus,
185 x_errorMsg => x_errorMsg);
186 if ( x_returnStatus <> fnd_api.g_ret_sts_success ) then
187 raise fnd_api.g_exc_unexpected_error;
188 end if;
189
190 -- need to adjust the data required field
191 update wip_requirement_operations wro
192 set wro.date_required = (select nvl(max(wo.first_unit_start_date),l_startDate)
193 from wip_operations wo
194 where wo.organization_id = wro.organization_id
195 and wo.wip_entity_id = wro.wip_entity_id
196 and wo.operation_seq_num = abs(wro.operation_seq_num))
197 where wro.wip_entity_id = p_wipEntityID
198 and wro.organization_id = p_orgID;
199
200 end if;
201
202 -- for bug 3041013
203 delete wip_operation_resource_usage
204 where wip_entity_id = p_wipEntityID;
205
206 -- for bug 3041018
207 wip_op_resources_utilities.update_resource_instances(
208 p_wip_entity_id => p_wipEntityID,
209 p_org_id => p_orgID);
210
211 if (l_logLevel <= wip_constants.trace_logging) then
212 wip_logger.exitPoint(p_procName => g_pkgName || '.' || l_procName,
213 p_procReturnStatus => x_returnStatus,
214 p_msg => 'success',
215 x_returnStatus => l_retStatus);
216 end if;
217 exception
218 when others then
219 x_returnStatus := fnd_api.g_ret_sts_unexp_error;
220 -- Fixed for bug 5255226
221 -- Don't need to add the x_errormsg again to stack by calling fnd_msg_pub.add_exc_msg
222 -- Removed this API call. Instead adding this error message to debug log using
223 if (l_logLevel <= wip_constants.trace_logging) then
224 wip_logger.log(x_errorMsg,l_retstatus);
225 End if;
226 -- End of Fix for bug 5255226
227
228 if(l_logLevel <= wip_constants.trace_logging) then
229 wip_logger.exitPoint(p_procName => g_pkgName || '.' || l_procName,
230 p_procReturnStatus => x_returnStatus,
231 p_msg => x_errorMsg,
232 x_returnStatus => l_retStatus);
233 end if;
234 end;
235
236 -- This procedure is used to reexplode the bom/routing if applicable and reschedule the job.
237 -- p_schedulingMethod: if the value is routing based, then you must provide one of the p_startDate
238 -- and p_endDate, you can not provide both, however.
239 -- if it is not routing based, then you must provide both.
240 -- p_startDate: forward schedule the job if it is not null
241 -- p_endDate: backward schedule the job it it is not null
242 -- p_rtgRefID: only useful when p_jobType is nonstandard
243 -- p_bomRefID: only useful when p_jobType is nonstandard
244 -- p_unitNumber: To explode components properly based on unit number for unit effective assemblies.
245 -- for anything related to do not want to change, for instance, bom_reference_id, you must pass the original
246 -- value queried up from the job. If you pass null, this API will consider that you want to change the
247 -- value to null instead of not touching it at all.
248 --
249 procedure reexplodeJob(p_orgID in number,
250 p_wipEntityID in number,
251 p_schedulingMethod in number,
252 p_altRouting in varchar2,
253 p_routingRev in varchar2, /*add for bug 15963294*/
254 p_routingRevDate in date,
255 p_altBOM in varchar2,
256 p_bomRev in varchar2, /*add for bug 15963294*/
257 p_bomRevDate in date,
258 p_qty in number,
259 p_startDate in date,
260 p_endDate in date,
261 p_projectID in number,
262 p_taskID in number,
263 p_rtgRefID in number,
264 p_bomRefID in number,
265 p_allowExplosion in boolean,
266 p_unitNumber in varchar2 DEFAULT '', /* added for bug 5332615 */
267 x_returnStatus out nocopy varchar2,
268 x_errorMsg out nocopy varchar2) is
269 l_params wip_logger.param_tbl_t;
270 l_procName varchar2(30) := 'reexplodeJob';
271 l_logLevel number := to_number(fnd_log.g_current_runtime_level);
272 l_retStatus varchar2(1);
273 l_msg varchar2(240);
274 l_serStartOp number;
275
276 l_jobType number;
277 l_jobStatus number;
278 l_bomRefID number;
279 l_rtgRefID number;
280 l_bomRev varchar2(3);/*add for bug 15963294*/
281 l_rtgRev varchar2(3);/*add for bug 15963294*/
282 l_bomRevDate date;
283 l_rtgRevDate date;
284 l_jobstartDate date; /*Bug 12580949*/
285 l_jobCompDate date; /*Bug 12580949*/
286 l_altBom varchar2(10);
287 l_altRtg varchar2(10);
288 l_bill_present Number;
289 l_expRtgRequired boolean := false;
290 l_expBomRequired boolean := false;
291 l_rtgItemID number;
292 l_bomItemID number;
293 l_assemblyID number;
294 l_startDate date;
295 l_endDate date;
296 l_inf_sch_flag Number := 0;
297 l_usePhantomRouting number;
298 cursor c_phantoms is
299 select inventory_item_id,
300 -1*operation_seq_num operation_seq_num
301 from wip_requirement_operations
302 where organization_id = p_orgID
303 and wip_entity_id = p_wipEntityID
304 and operation_seq_num < 0
305 and wip_supply_type = wip_constants.phantom;
306 begin
307
308 x_returnStatus := fnd_api.g_ret_sts_success;
309 if (l_logLevel <= wip_constants.trace_logging) then
310 l_params(1).paramName := 'p_orgID';
311 l_params(1).paramValue := p_orgID;
312 l_params(2).paramName := 'p_wipEntityID';
313 l_params(2).paramValue := p_wipEntityID;
314 wip_logger.entryPoint(p_procName => g_pkgName || '.' || l_procName,
315 p_params => l_params,
316 x_returnStatus => x_returnStatus);
317 if(x_returnStatus <> fnd_api.g_ret_sts_success) then
318 raise fnd_api.g_exc_unexpected_error;
319 end if;
320 end if;
321 l_inf_sch_flag := 0;
322 select job_type,
323 status_type,
324 primary_item_id,
325 bom_reference_id,
326 routing_reference_id,
327 bom_revision,/*add for bug 15963294*/
328 routing_revision,/*add for bug 15963294*/
329 bom_revision_date,
330 routing_revision_date,
331 alternate_bom_designator,
332 alternate_routing_designator,
333 scheduled_start_date, /*Bug 12580949*/
334 scheduled_completion_date /*Bug 12580949*/
335 into l_jobType,
336 l_jobStatus,
337 l_assemblyID,
338 l_bomRefID,
339 l_rtgRefID,
340 l_bomRev, /*add for bug 15963294*/
341 l_rtgRev, /*add for bug 15963294*/
342 l_bomRevDate,
343 l_rtgRevDate,
344 l_altBom,
345 l_altRtg,
346 l_jobstartDate, /*Bug 12580949*/
347 l_jobCompDate /*Bug 12580949*/
348 from wip_discrete_jobs
349 where organization_id = p_orgID
350 and wip_entity_id = p_wipEntityID;
351
352 -- Bug 9834677. modified logic since forms will pass '@@@' for primary routings.
353 if ( nvl(p_altRouting, nvl(l_altRtg,'@@@')) <> nvl(l_altRtg, '@@@') or
354 nvl(p_routingRevDate, nvl(l_rtgRevDate, fnd_api.g_miss_date)) <> nvl(l_rtgRevDate, fnd_api.g_miss_date) OR
355 /*bug 15963294: when revision changes with no change of revision date due to bom_high_date being in the future, we still reexplode*/
356 nvl(p_routingRev, nvl(l_rtgRev, fnd_api.g_miss_char)) <> nvl(l_rtgRev, fnd_api.g_miss_char) OR
357 (p_allowExplosion) or (l_jobType = wip_constants.nonstandard and nvl(p_rtgRefID, -1) <> nvl(l_rtgRefID, -1)) ) then
358 l_expRtgRequired := true;
359 if (p_altRouting = '@@@' or p_altRouting = fnd_api.g_miss_char ) then
360 l_altRtg := null;
361 else
362 l_altRtg := p_altRouting;
363 end if;
364 else
365 l_expRtgRequired := false;
366 end if;
367
368 -- Bug 9834677. modified logic since forms will pass '@@@' for primary bills.
369 if ( nvl(p_altBom, nvl(l_altBom, '@@@')) <> nvl(l_altBom, '@@@') or
370 nvl(p_bomRevDate, nvl(l_bomRevDate, fnd_api.g_miss_date)) <> nvl(l_bomRevDate, fnd_api.g_miss_date) or
371 /*bug 15963294: when revision changes with no change of revision date due to bom_high_date being in the future, we still reexplode*/
372 nvl(p_bomRev, nvl(l_bomRev, fnd_api.g_miss_char)) <> nvl(l_bomRev, fnd_api.g_miss_char) OR
373 (p_allowExplosion) or (l_jobType = wip_constants.nonstandard and nvl(p_bomRefID, -1) <> nvl(l_bomRefID, -1)) ) then
374 l_expBomRequired := true;
375 if (p_altBom = '@@@' or p_altBom = fnd_api.g_miss_char) then
376 l_altBom := null;
377 else
378 l_altBom := p_altBom;
379 end if;
380 else
381 l_expBomRequired := false;
382 end if;
383
384 -- Bug 9834677. Consider only job status. unless the job is unreleased, you can never explode again from bom
385 -- Bug 14292968 Consider PENDING_scheduled status for Constraint Based scheduler in Simulate Discrete Jobs.
386 if ( l_jobStatus NOT IN ( wip_constants.unreleased,wip_constants.pend_sched )) then
387 l_expRtgRequired := false;
388 l_expBomRequired := false;
389 end if;
390
391 if ( p_schedulingMethod = wip_constants.routing ) then
392 if ( (p_startDate is null and p_endDate is null) or
393 (p_startDate is not null and p_endDate is not null) ) then
394 x_errorMsg := 'Must only provide one of the p_startDate and p_endDate for routing based scheduling';
395 raise fnd_api.g_exc_unexpected_error;
396 else
397 -- populate some value and then we will reschedule it later on.
398 l_startDate := nvl(p_startDate, p_endDate);
399 l_endDate := l_startDate;
400 end if;
401 else
402 if (p_schedulingMethod = wip_constants.ml_manual and (p_startDate is null or p_endDate is null) ) then
403 x_errorMsg := 'Must provide both of the dates if it is not routing based scheduling';
404 raise fnd_api.g_exc_unexpected_error;
405 end if;
406 l_startDate := p_startDate;
407 l_endDate := p_endDate;
408 end if;
409
410 if ( l_expRtgRequired ) then
411 -- remove any setup resource for this job
412 /* Moved inside wip_bomRoutingUtil_pvt.explodeRouting after checking for existence of routing
413 For Bug fix 12861546.
414 wip_update_setup_resources.delete_setup_resources_pub(
415 p_wip_entity_id => p_wipEntityID,
416 p_organization_id => p_orgID);
417
418 delete from wip_operations
419 where wip_entity_id = p_wipEntityID
420 and organization_id = p_orgID;
421
422 delete from wip_operation_resources
423 where wip_entity_id = p_wipEntityID
424 and organization_id = p_orgID;
425
426 delete from wip_sub_operation_resources
427 where wip_entity_id = p_wipEntityID
428 and organization_id = p_orgID;
429
430 fnd_attached_documents2_pkg.delete_attachments(
431 x_entity_name => 'WIP_DISCRETE_OPERATIONS',
432 x_pk1_value => to_char(p_wipEntityID),
433 x_pk3_value => to_char(p_orgID),
434 x_delete_document_flag => 'Y');
435 */
436 if ( l_jobType = wip_constants.standard ) then
437 l_rtgItemID := l_assemblyID;
438 else
439 l_rtgItemID := p_rtgRefID;
440 if p_rtgRefID is NULL then --Bug#14301607: If the Routing reference is removed, the routing should be removed for NonStdJobs
441 wip_update_setup_resources.delete_setup_resources_pub(
442 p_wip_entity_id => p_wipEntityID,
443 p_organization_id => p_orgID);
444
445 delete from wip_operations
446 where wip_entity_id = p_wipEntityID
447 and organization_id = p_orgID;
448
449 delete from wip_operation_resources
450 where wip_entity_id = p_wipEntityID
451 and organization_id = p_orgID;
452
453 delete from wip_sub_operation_resources
454 where wip_entity_id = p_wipEntityID
455 and organization_id = p_orgID;
456
457 fnd_attached_documents2_pkg.delete_attachments(
458 x_entity_name => 'WIP_DISCRETE_OPERATIONS',
459 x_pk1_value => to_char(p_wipEntityID),
460 x_pk3_value => to_char(p_orgID),
461 x_delete_document_flag => 'Y');
462 end if;
463 end if;
464
465 wip_bomRoutingUtil_pvt.explodeRouting(
466 p_orgID => p_orgID,
467 p_wipEntityID => p_wipEntityID,
468 p_repSchedID => null,
469 p_itemID => l_rtgItemID,
470 p_altRouting => l_altRtg,
471 p_routingRevDate => p_routingRevDate,
472 p_qty => p_qty,
473 p_startDate => l_startDate,
474 p_endDate => l_endDate,
475 x_serStartOp => l_serStartOp,
476 x_returnStatus => x_returnStatus,
477 x_errorMsg => x_errorMsg);
478 if ( x_returnStatus <> fnd_api.g_ret_sts_success ) then
479 raise fnd_api.g_exc_unexpected_error;
480 end if;
481 end if;
482
483 /* fix bug 5238435. Move the scheduler call outside the if condition since job can be
484 rescheduled even without exploding the routing when user just changes the dates */
485
486 -- Bug 11893957. Call Infinite Scheduler after BOM Explosion to consider resources from Phantom Routings.
487 /*
488 if ( p_schedulingMethod = wip_constants.routing ) then
489 wip_infinite_scheduler_pvt.schedule(
490 p_orgID => p_orgID,
491 p_wipEntityID => p_wipEntityID,
492 p_startDate => p_startDate,
493 p_endDate => p_endDate,
494 p_quantity => p_qty, --- Added for bug 5440007
495 x_returnStatus => x_returnStatus,
496 x_errorMsg => x_errorMsg);
497 if ( x_returnStatus <> fnd_api.g_ret_sts_success ) then
498 raise fnd_api.g_exc_unexpected_error;
499 end if;
500
501 select wdj.scheduled_start_date,
502 wdj.scheduled_completion_date
503 into l_startDate, l_endDate
504 from wip_discrete_jobs wdj
505 where wdj.organization_id = p_orgID
506 and wdj.wip_entity_id = p_wipEntityID;
507 end if;
508 */
509
510 -- after reexplode the routing, some op reference in WRO might be invalid. We need to set those
511 -- properly. We only need to do it if bom reexplosion is not required.
512 -- we also need to add the phantom resource back since it was deleted before
513
514 if ( l_expRtgRequired and not l_expBomRequired ) then
515 if ( l_logLevel <= wip_constants.trace_logging ) then
516 wip_logger.log('Resetting the op reference in WRO.....', l_retStatus);
517 end if;
518 wip_fix_req_ops_pkg.fix(x_wip_entity_id => p_wipEntityID,
519 x_organization_id => p_orgID,
520 x_repetitive_schedule_id => null,
521 x_entity_start_date => l_startDate);
522 l_usePhantomRouting := wip_globals.use_phantom_routings(p_orgID);
523 if ( l_usePhantomRouting = wip_constants.yes ) then
524 for phan in c_phantoms loop
525 wip_explode_phantom_rtgs.explode_resources(
526 p_wip_entity_id => p_wipEntityID,
527 p_sched_id => null,
528 p_org_id => p_orgID,
529 p_entity_type => wip_constants.discrete,
530 p_phantom_item_id => phan.inventory_item_id,
531 p_op_seq_num => phan.operation_seq_num,
532 p_rtg_rev_date => p_routingRevDate);
533 end loop;
534 end if;
535 end if;
536
537
538 if ( l_expBomRequired ) then
539
540 if ( l_jobType = wip_constants.standard ) then
541 l_bomItemID := l_assemblyID;
542 else
543 l_bomItemID := p_bomRefID;
544 end if;
545 -- For Bug fix 12861546. Delete from WRO only when Bill exists.
546 Begin
547 select 1 into l_bill_present
548 from dual
549 where exists (select 1 from bom_structures_b
550 where pk1_value = l_bomItemID
551 and pk2_value = p_orgID
552 and nvl(ALTERNATE_BOM_DESIGNATOR, '$$$$') = nvl(l_altBom,'$$$$')
553 and obj_name is null
554 and rownum =1);
555 Exception
556 when others then
557 l_bill_present := 0;
558 End;
559
560 if l_bill_present =1 OR l_bomItemID is null then --Bug#14172844: Deleting from WRO for Non-std jobs, when the bom_reference_id is null
561 delete from wip_requirement_operations
562 where wip_entity_id = p_wipEntityID
563 and organization_id = p_orgID;
564 end if;
565
566 -- Added for bug 8463132.
567 if l_startDate is null then
568 select wdj.scheduled_start_date
569 into l_startDate
570 from wip_discrete_jobs wdj
571 where wdj.organization_id = p_orgID
572 and wdj.wip_entity_id = p_wipEntityID;
573 end if;
574
575 -- Should delete the Phantom Resources since it will be re-exploded again during BOM explosion.
576 delete from WIP_OPERATION_RESOURCES
577 where wip_entity_id = p_wipEntityID
578 and organization_id = p_orgID
579 and phantom_flag = 1;
580
581 wip_bomRoutingUtil_pvt.explodeBOM(
582 p_orgID => p_orgID,
583 p_wipEntityID => p_wipEntityID,
584 p_jobType => l_jobType,
585 p_repSchedID => null,
586 p_itemID => l_bomItemID,
587 p_altBOM => l_altBom,
588 p_bomRevDate => p_bomRevDate,
589 p_altRouting => p_altRouting,
590 p_routingRevDate => p_routingRevDate,
591 p_qty => p_qty,
592 p_jobStartDate => l_startDate,
593 p_projectID => p_projectID,
594 p_taskID => p_taskID,
595 p_unitNumber => p_unitNumber, /* added for bug 5332615 */
596 x_returnStatus => x_returnStatus,
597 x_errorMsg => x_errorMsg);
598 if ( x_returnStatus <> fnd_api.g_ret_sts_success ) then
599 raise fnd_api.g_exc_unexpected_error;
600 end if;
601 end if;
602
603 -- if it is only date changes or qty changes, then the flow should be here
604 if ( not l_expBomRequired and not l_expRtgRequired ) then
605 --Bug 5464449: Quantity should be adjusted only when explode is yes at header level.
606 --Bug 13593413 : Fixing the regression caused by bug 5464449 , quantity in bill should be updated eventhough allow_explosion populated as 'N'
607 -- Other wise user not able to update the quantity of the components by preserving the additional components to the job that are not part of the bill.
608 -- if p_allowExplosion then
609 wip_bomRoutingUtil_pvt.adjustQtyChange(
610 p_orgID => p_orgID,
611 p_wipEntityID => p_wipEntityID,
612 p_qty => p_qty,
613 x_returnStatus => x_returnStatus,
614 x_errorMsg => x_errorMsg);
615 if ( x_returnStatus <> fnd_api.g_ret_sts_success ) then
616 raise fnd_api.g_exc_unexpected_error;
617 end if;
618 -- end if; --End of check on p_allowExplosion.
619 end if;
620 if ( p_schedulingMethod = wip_constants.routing ) then
621 wip_infinite_scheduler_pvt.schedule(
622 p_orgID => p_orgID,
623 p_wipEntityID => p_wipEntityID,
624 p_startDate => p_startDate,
625 p_endDate => p_endDate,
626 p_quantity => p_qty,
627 x_returnStatus => x_returnStatus,
628 x_errorMsg => x_errorMsg);
629 if ( x_returnStatus <> fnd_api.g_ret_sts_success ) then
630 raise fnd_api.g_exc_unexpected_error;
631 end if;
632
633 update wip_requirement_operations wro
634 set wro.date_required = (select nvl(max(wo.first_unit_start_date),l_startDate)
635 from wip_operations wo
636 where wo.organization_id = wro.organization_id
637 and wo.wip_entity_id = wro.wip_entity_id
638 and wo.operation_seq_num = abs(wro.operation_seq_num))
639 where wro.wip_entity_id = p_wipEntityID
640 and wro.organization_id = p_orgID;
641
642 /*Bug 12580949 when scheduling method is manual, and start date and end date populated in WJSI is exactly the same as job start date and completion date, we should not update any date
643 at operation and resource level*/
644 elsif(p_schedulingMethod <> wip_constants.ml_manual or l_jobstartDate <> NVL(p_startDate, l_jobstartDate + 1) or l_jobCompDate <> NVL(p_endDate, l_jobCompDate + 1)) then
645 -- Added for Bug 8463132.
646 if ( p_schedulingMethod = wip_constants.leadtime ) then
647 select wdj.scheduled_start_date
648 into l_startDate
649 from wip_discrete_jobs wdj
650 where wdj.organization_id = p_orgID
651 and wdj.wip_entity_id = p_wipEntityID;
652 end if;
653
654
655 update wip_operations
656 set first_unit_start_date = p_startDate,
657 first_unit_completion_date = p_startDate,
658 last_unit_start_date = p_startDate,
659 last_unit_completion_date = p_startDate
660 where organization_id = p_orgID
661 and wip_entity_id = p_wipEntityID;
662
663 update wip_operation_resources
664 set start_date = p_startDate,
665 completion_date = p_startDate
666 where organization_id = p_orgID
667 and wip_entity_id = p_wipEntityID;
668
669 update wip_sub_operation_resources
670 set start_date = p_startDate,
671 completion_date = p_startDate
672 where organization_id = p_orgID
673 and wip_entity_id = p_wipEntityID;
674
675 -- need to adjust the data required field
676 update wip_requirement_operations wro
677 set wro.date_required = (select nvl(max(wo.first_unit_start_date),l_startDate)
678 from wip_operations wo
679 where wo.organization_id = wro.organization_id
680 and wo.wip_entity_id = wro.wip_entity_id
681 and wo.operation_seq_num = abs(wro.operation_seq_num))
682 where wro.wip_entity_id = p_wipEntityID
683 and wro.organization_id = p_orgID;
684 end if;
685
686 -- for bug 3041013
687 delete wip_operation_resource_usage
688 where wip_entity_id = p_wipEntityID;
689
690 -- for bug 3041018
691 wip_op_resources_utilities.update_resource_instances(
692 p_wip_entity_id => p_wipEntityID,
693 p_org_id => p_orgID);
694
695
696 if (l_logLevel <= wip_constants.trace_logging) then
697 wip_logger.exitPoint(p_procName => g_pkgName || '.' || l_procName,
698 p_procReturnStatus => x_returnStatus,
699 p_msg => 'success',
700 x_returnStatus => l_retStatus);
701 end if;
702 exception
703 when others then
704 x_returnStatus := fnd_api.g_ret_sts_unexp_error;
705 fnd_msg_pub.add_exc_msg(p_pkg_name => g_pkgName,
706 p_procedure_name => l_procName,
707 p_error_text => SUBSTRB(x_errorMsg, 1,240));--bug 13608871
708 if(l_logLevel <= wip_constants.trace_logging) then
709 wip_logger.exitPoint(p_procName => g_pkgName || '.' || l_procName,
710 p_procReturnStatus => x_returnStatus,
711 p_msg => x_errorMsg,
712 x_returnStatus => l_retStatus);
713 end if;
714 end reexplodeJob;
715
716 end wip_bomRouting_pvt;
[Home] [Help]