[Home] [Help]
PACKAGE BODY: APPS.WIP_MASSLOAD_PVT
Source
1 package body wip_massload_pvt as
2 /* $Header: wipmlpvb.pls 120.15.12000000.2 2007/02/21 04:50:31 parjain ship $ */
3
4 g_pkgName constant varchar2(30) := 'wip_massload_pvt';
5
6
7 procedure createWIPEntity(p_rowid in rowid);
8 procedure updateWIPEntity(p_rowid in rowid);
9
10 procedure processWJSI(p_rowid in rowid,
11 x_returnStatus out nocopy varchar2,
12 x_errorMsg out nocopy varchar2) is
13 l_params wip_logger.param_tbl_t;
14 l_procName varchar2(30) := 'processWJSI';
15 l_logLevel number := to_number(fnd_log.g_current_runtime_level);
16 l_retStatus varchar2(1);
17 l_msg varchar2(240);
18
19 wjsi_row wip_job_schedule_interface%ROWTYPE;
20 l_serStartOp number := null;
21 l_defaultSer number;
22 l_startDate date;
23 l_endDate date;
24 l_jobType number;
25 l_statusType number;
26 l_bomRefID number;
27 l_rtgRefID number;
28 l_bomRevDate date;
29 l_rtgRevDate date;
30 l_allowExplosion boolean;
31 l_qty number;
32 l_success number;
33 l_projectID number;
34 l_taskID number;
35
36 begin
37 x_returnStatus := fnd_api.g_ret_sts_success;
38 savepoint begin_process_wjsi;
39
40 if (l_logLevel <= wip_constants.trace_logging) then
41 l_params(1).paramName := 'p_rowid';
42 l_params(1).paramValue := p_rowid;
43 wip_logger.entryPoint(p_procName => g_pkgName || '.' || l_procName,
44 p_params => l_params,
45 x_returnStatus => x_returnStatus);
46 if(x_returnStatus <> fnd_api.g_ret_sts_success) then
47 raise fnd_api.g_exc_unexpected_error;
48 end if;
49 end if;
50
51 select *
52 into wjsi_row
53 from wip_job_schedule_interface
54 where rowid = p_rowid;
55
56 if ( l_logLevel <= wip_constants.trace_logging ) then
57 wip_logger.log('Interface id: ' || wjsi_row.interface_id || ' load type is: ' || wjsi_row.load_type,
58 l_retStatus);
59 end if;
60
61
62 if ( wjsi_row.load_type in (wip_constants.create_job, wip_constants.create_ns_job) ) then
63 -- create job header record
64 createWIPEntity(p_rowid);
65
66 if ( wjsi_row.load_type = wip_constants.create_job ) then
67 l_jobType := wip_constants.standard;
68 else
69 l_jobType := wip_constants.nonstandard;
70 end if;
71
72 if ( nvl(wjsi_row.allow_explosion, 'Y') not in ('n', 'N') ) then
73 wip_bomRouting_pvt.createJob(p_orgID => wjsi_row.organization_id,
74 p_wipEntityID => wjsi_row.wip_entity_id,
75 p_jobType => l_jobType,
76 p_itemID => wjsi_row.primary_item_id,
77 p_schedulingMethod =>wjsi_row.scheduling_method,
78 p_altRouting => wjsi_row.alternate_routing_designator,
79 p_routingRevDate => wjsi_row.routing_revision_date,
80 p_altBOM => wjsi_row.alternate_bom_designator,
81 p_bomRevDate => wjsi_row.bom_revision_date,
82 p_qty => wjsi_row.start_quantity,
83 p_startDate => wjsi_row.first_unit_start_date,
84 p_endDate => wjsi_row.last_unit_completion_date,
85 p_projectID => wjsi_row.project_id,
86 p_taskID => wjsi_row.task_id,
87 p_rtgRefID => wjsi_row.routing_reference_id,
88 p_bomRefID => wjsi_row.bom_reference_id,
89 p_unitNumber => wjsi_row.end_item_unit_number, /* added for bug 5332615 */
90 x_serStartOp => l_serStartOp,
91 x_returnStatus => x_returnStatus,
92 x_errorMsg => x_errorMsg);
93 if ( x_returnStatus <> fnd_api.g_ret_sts_success ) then
94 raise fnd_api.g_exc_unexpected_error;
95 end if;
96 end if;
97
98 -- default the serialization start op
99 if ( wjsi_row.serialization_start_op is null and
100 wjsi_row.primary_item_id is not null) then
101 select default_serialization_start_op
102 into l_defaultSer
103 from wip_parameters
104 where organization_id = wjsi_row.organization_id;
105
106 if ( l_serStartOp is not null ) then
107 update wip_discrete_jobs
108 set serialization_start_op = l_serStartOp
109 where wip_entity_id = wjsi_row.wip_entity_id
110 and exists (select 1
111 from mtl_system_items
112 where inventory_item_id = wjsi_row.primary_item_id
113 and organization_id = wjsi_row.organization_id
114 and serial_number_control_code = wip_constants.full_sn);
115 elsif ( l_defaultSer = wip_constants.yes ) then
116 update wip_discrete_jobs
117 set serialization_start_op = (select nvl(min(operation_seq_num), 1)
118 from wip_operations
119 where wip_entity_id = wjsi_row.wip_entity_id)
120 where wip_entity_id = wjsi_row.wip_entity_id
121 and exists (select 1
122 from mtl_system_items
123 where inventory_item_id = wjsi_row.primary_item_id
124 and organization_id = wjsi_row.organization_id
125 and serial_number_control_code = wip_constants.full_sn);
126 end if;
127 end if;
128
129 -- release job if necessary
130 if ( wjsi_row.status_type in (wip_constants.released, wip_constants.hold) ) then
131 wip_mass_load_processor.ml_release(wjsi_row.wip_entity_id,
132 wjsi_row.organization_id,
133 wjsi_row.class_code,
134 wjsi_row.status_type,
135 l_success,
136 x_errorMsg,
137 nvl(wjsi_row.date_released, sysdate));
138 if ( l_success = 0 ) then
139 raise fnd_api.g_exc_unexpected_error;
140 end if;
141 end if;
142
143 elsif ( wjsi_row.load_type = wip_constants.resched_job ) then
144 select bom_reference_id,
145 routing_reference_id,
146 bom_revision_date,
147 routing_revision_date,
148 start_quantity,
149 status_type,
150 scheduled_start_date,
151 scheduled_completion_date,
152 project_id,
153 task_id
154 into l_bomRefID,
155 l_rtgRefID,
156 l_bomRevDate,
157 l_rtgRevDate,
158 l_qty,
159 l_statusType,
160 l_startDate,
161 l_endDate,
162 l_projectID,
163 l_taskID
164 from wip_discrete_jobs
165 where wip_entity_id = wjsi_row.wip_entity_id
166 and organization_id = wjsi_row.organization_id;
167
168 if ( wjsi_row.scheduling_method = wip_constants.routing ) then
169 if ( wjsi_row.last_unit_completion_date is not null ) then
170 l_startDate := null;
171 l_endDate := wjsi_row.last_unit_completion_date;
172 elsif ( wjsi_row.first_unit_start_date is not null ) then
173 l_startDate := wjsi_row.first_unit_start_date;
174 l_endDate := null;
175 else
176 -- if not date is provided, then we forward schedule
177 l_endDate := null;
178 end if;
179 else
180 l_startDate := wjsi_row.first_unit_start_date;
181 l_endDate := wjsi_row.last_unit_completion_date;
182 end if;
183
184 if ( nvl(wjsi_row.allow_explosion, 'Y') in ('n', 'N') ) then
185 l_allowExplosion := false;
186 else
187 l_allowExplosion := true;
188 end if;
189
190 wip_bomRouting_pvt.reexplodeJob(
191 p_orgID => wjsi_row.organization_id,
192 p_wipEntityID => wjsi_row.wip_entity_id,
193 p_schedulingMethod => wjsi_row.scheduling_method,
194 p_altRouting => wjsi_row.alternate_routing_designator,
195 --Bug 5230849:If routing revision date is null in wjsi,
196 --use the date present in wdj.
197 p_routingRevDate => nvl(wjsi_row.routing_revision_date,l_rtgRevDate),
198 p_altBOM => wjsi_row.alternate_bom_designator,
199 --Bug 5230849:If bom revision date is null in wjsi,
200 --use the date present in wdj.
201 p_bomRevDate => nvl(wjsi_row.bom_revision_date,l_bomRevDate),
202 p_qty => nvl(wjsi_row.start_quantity, l_qty),
203 p_startDate => l_startDate,
204 p_endDate => l_endDate,
205 p_projectID => nvl(wjsi_row.project_id, l_projectID),
206 p_taskID => nvl(wjsi_row.task_id, l_taskID),
207 p_allowExplosion => l_allowExplosion,
208 p_rtgRefID => nvl(wjsi_row.routing_reference_id, l_rtgRefID),
209 p_bomRefID => nvl(wjsi_row.bom_reference_id, l_bomRefID),
210 p_unitNumber => wjsi_row.end_item_unit_number, /* added for bug 5332615 */
211 x_returnStatus => x_returnStatus,
212 x_errorMsg => x_errorMsg);
213 if ( x_returnStatus <> fnd_api.g_ret_sts_success ) then
214 raise fnd_api.g_exc_unexpected_error;
215 end if;
216
217 -- handle status change
218 if ( l_statusType <> wjsi_row.status_type and
219 l_statusType in (wip_constants.released, wip_constants.unreleased) ) then
220 wip_mass_load_processor.ml_status_change(
221 wjsi_row.wip_entity_id,
222 wjsi_row.organization_id,
223 wjsi_row.class_code,
224 wjsi_row.status_type,
225 l_statusType,
226 l_success,
227 x_errorMsg,
228 nvl(wjsi_row.date_released, sysdate));
229 if ( l_success = 0 ) then
230 raise fnd_api.g_exc_unexpected_error;
231 end if;
232 end if;
233
234 -- handles pick release related issues
235 if ( wip_picking_pub.is_job_pick_released(wjsi_row.wip_entity_id,
236 wjsi_row.repetitive_schedule_id,
237 wjsi_row.organization_id) and
238 l_qty <> wjsi_row.start_quantity ) then
239 wip_picking_pub.update_job_backordqty(
240 p_wip_entity_id => wjsi_row.wip_entity_id,
241 p_repetitive_schedule_id => wjsi_row.repetitive_schedule_id,
242 p_new_job_qty => wjsi_row.start_quantity,
243 x_return_status => l_retStatus,
244 x_msg_data => x_errorMsg);
245 if ( l_retStatus <> fnd_api.g_ret_sts_success ) then
246 raise fnd_api.g_exc_unexpected_error;
247 end if;
248 end if;
249
250 if ( wjsi_row.status_type in (wip_constants.comp_nochrg,
251 wip_constants.hold,
252 wip_constants.cancelled) ) then
253 wip_picking_pvt.cancel_allocations(p_wip_entity_id => wjsi_row.wip_entity_id,
254 p_wip_entity_type => wip_constants.discrete,
255 x_return_status => l_retStatus,
256 x_msg_data => x_errorMsg);
257 if ( l_retStatus <> fnd_api.g_ret_sts_success ) then
258 raise fnd_api.g_exc_unexpected_error;
259 end if;
260 end if;
261
262 -- Call the stored procedure to create a po req only if both old and new statuses
263 -- are released and the new quantity is > old quantity. When the status is
264 -- changed to released, already existing stored procedures will create a po req.
265 if ( wjsi_row.start_quantity > l_qty and
266 wjsi_row.status_type = wip_constants.released and
267 l_statusType = wip_constants.unreleased ) then
268 wip_osp.create_additional_req(wjsi_row.wip_entity_id,
269 wjsi_row.organization_id,
270 null,
271 wjsi_row.start_quantity - l_qty);
272 end if;
273
274 -- update job header info
275 updateWipEntity(p_rowid);
276
277 end if;
278 -- Added for bug 5439929
279 -- We should update the wip_supply type in wip_requirement_operations table
280 -- wip_supply_type is specified by user in interface table. If user specified the value
281 -- as 'Based on bill'(value 7), then we don't need to update as that is the default behavior
282 If nvl(wjsi_row.wip_supply_type,7) <> 7 then
283 update wip_requirement_operations
284 set wip_supply_type = wjsi_row.wip_supply_type
285 where wip_entity_id = wjsi_row.wip_entity_id;
286 End if;
287
288 if (l_logLevel <= wip_constants.trace_logging) then
289 wip_logger.exitPoint(p_procName => g_pkgName || '.' || l_procName,
290 p_procReturnStatus => x_returnStatus,
291 p_msg => 'success',
292 x_returnStatus => l_retStatus);
293 end if;
294
295 exception
296 when fnd_api.g_exc_unexpected_error then
297 rollback to savepoint begin_process_wjsi;
298 x_returnStatus := fnd_api.g_ret_sts_unexp_error;
299 if(l_logLevel <= wip_constants.trace_logging) then
300 wip_logger.exitPoint(p_procName => g_pkgName || '.' || l_procName,
301 p_procReturnStatus => x_returnStatus,
302 p_msg => x_errorMsg,
303 x_returnStatus => l_retStatus);
304 end if;
305 when others then
306 rollback to savepoint begin_process_wjsi;
307 if(l_logLevel <= wip_constants.trace_logging) then
308 wip_logger.exitPoint(p_procName => g_pkgName || '.' || l_procName,
309 p_procReturnStatus => x_returnStatus,
310 p_msg => 'unexp error:' || SQLERRM,
311 x_returnStatus => l_retStatus);
312 end if;
313 x_returnStatus := fnd_api.g_ret_sts_unexp_error;
314 fnd_msg_pub.add_exc_msg(p_pkg_name => g_pkgName,
315 p_procedure_name => l_procName,
316 p_error_text => SQLERRM);
317 wip_utilities.get_message_stack(p_msg => l_msg);
318 x_errorMsg := substrb(l_msg, 1, 240);
319 end processWJSI;
320
321
322 procedure createWIPEntity(p_rowid in rowid) is
323 begin
324 insert into wip_discrete_jobs(
325 wip_entity_id,
326 organization_id,
327 last_update_date,
328 last_updated_by,
329 creation_date,
330 created_by,
331 last_update_login,
332 request_id,
333 program_application_id,
334 program_id,
335 program_update_date,
336 source_line_id,
337 source_code,
338 description,
339 status_type,
340 date_released,
341 primary_item_id,
342 bom_reference_id,
343 routing_reference_id,
344 firm_planned_flag,
345 job_type,
346 wip_supply_type,
347 class_code,
348 material_account,
349 material_overhead_account,
350 resource_account,
351 outside_processing_account,
352 material_variance_account,
353 resource_variance_account,
354 outside_proc_variance_account,
355 std_cost_adjustment_account,
356 overhead_account,
357 overhead_variance_account,
358 scheduled_start_date,
359 scheduled_completion_date,
360 start_quantity,
361 quantity_completed,
362 quantity_scrapped,
363 net_quantity,
364 common_bom_sequence_id,
365 common_routing_sequence_id,
366 bom_revision,
367 routing_revision,
368 bom_revision_date,
369 routing_revision_date,
370 lot_number,
371 alternate_bom_designator,
372 alternate_routing_designator,
373 completion_subinventory,
374 completion_locator_id,
375 demand_class,
376 project_id,
377 task_id,
378 schedule_group_id,
379 build_sequence,
380 line_id,
381 kanban_card_id,
382 overcompletion_tolerance_type,
383 overcompletion_tolerance_value,
384 end_item_unit_number,
385 po_creation_time,
386 priority,
387 due_date,
388 requested_start_date,
389 attribute_category,
390 attribute1,
391 attribute2,
392 attribute3,
393 attribute4,
394 attribute5,
395 attribute6,
396 attribute7,
397 attribute8,
398 attribute9,
399 attribute10,
400 attribute11,
401 attribute12,
402 attribute13,
403 attribute14,
404 attribute15,
405 serialization_start_op)
406 select
407 wjsi.wip_entity_id,
408 wjsi.organization_id,
409 sysdate,
410 wjsi.last_updated_by,
411 sysdate,
412 wjsi.created_by,
413 wjsi.last_update_login,
414 wjsi.request_id,
415 wjsi.program_application_id,
416 wjsi.program_id,
417 sysdate,
418 wjsi.source_line_id,
419 wjsi.source_code,
420 wjsi.description,
421 wjsi.status_type,
422 decode(wjsi.status_type,
423 wip_constants.released,
424 decode(wjsi.date_released, null, sysdate,
425 decode(sign(wjsi.date_released-sysdate), 1, sysdate, wjsi.date_released)),
426 wip_constants.hold,
427 decode(wjsi.date_released, null, sysdate,
428 decode(sign(wjsi.date_released-sysdate), 1, sysdate, wjsi.date_released)),
429 null),
430 wjsi.primary_item_id,
431 decode(wjsi.load_type, wip_constants.create_job, null, wjsi.bom_reference_id),
432 decode(wjsi.load_type, wip_constants.create_job, null, wjsi.routing_reference_id),
433 wjsi.firm_planned_flag,
434 decode(wjsi.load_type, wip_constants.create_job, wip_constants.standard, wip_constants.nonstandard),
435 wjsi.wip_supply_type,
436 wjsi.class_code,
437 wac.material_account,
438 wac.material_overhead_account,
439 wac.resource_account,
440 wac.outside_processing_account,
441 wac.material_variance_account,
442 wac.resource_variance_account,
443 wac.outside_proc_variance_account,
444 wac.std_cost_adjustment_account,
445 wac.overhead_account,
446 wac.overhead_variance_account,
447 nvl(wjsi.first_unit_start_date, wjsi.last_unit_completion_date),
448 nvl(wjsi.last_unit_completion_date, wjsi.first_unit_start_date),
449 round(wjsi.start_quantity, wip_constants.max_displayed_precision),
450 0, -- quantity_completed
451 0, -- quantity_scrapped
452 round(wjsi.net_quantity, wip_constants.max_displayed_precision),
453 bom.common_bill_sequence_id,
454 rtg.common_routing_sequence_id,
455 wjsi.bom_revision,
456 wjsi.routing_revision,
457 wjsi.bom_revision_date,
458 wjsi.routing_revision_date,
459 wjsi.lot_number,
460 wjsi.alternate_bom_designator,
461 wjsi.alternate_routing_designator,
462 wjsi.completion_subinventory,
463 wjsi.completion_locator_id,
464 wjsi.demand_class,
465 wjsi.project_id,
466 wjsi.task_id,
467 wjsi.schedule_group_id,
468 wjsi.build_sequence,
469 wjsi.line_id,
470 wjsi.kanban_card_id,
471 wjsi.overcompletion_tolerance_type,
472 wjsi.overcompletion_tolerance_value,
473 wjsi.end_item_unit_number,
474 wp.po_creation_time,
475 nvl(wjsi.priority, wip_constants.default_priority),
476 wjsi.due_date,
477 /* Bug 5745772: Requested start date and due date can only co exist if routing does not exist.*/
478 decode(wjsi.due_date, NULL,TO_DATE(
479 TO_CHAR(
480 wjsi.requested_start_date,WIP_CONSTANTS.DT_NOSEC_FMT
481 ),WIP_CONSTANTS.DT_NOSEC_FMT
482 ),
483 DECODE(
484 wjsi.routing_revision,NULL, TO_DATE(
485 TO_CHAR(
486 wjsi.requested_start_date,WIP_CONSTANTS.DT_NOSEC_FMT
487 ),WIP_CONSTANTS.DT_NOSEC_FMT
488 ),NULL
489 )
490 ),
491 --wjsi.requested_start_date,
492 wjsi.attribute_category,
493 wjsi.attribute1,
494 wjsi.attribute2,
495 wjsi.attribute3,
496 wjsi.attribute4,
497 wjsi.attribute5,
498 wjsi.attribute6,
499 wjsi.attribute7,
500 wjsi.attribute8,
501 wjsi.attribute9,
502 wjsi.attribute10,
503 wjsi.attribute11,
504 wjsi.attribute12,
505 wjsi.attribute13,
506 wjsi.attribute14,
507 wjsi.attribute15,
508 wjsi.serialization_start_op
509 from wip_accounting_classes wac,
510 bom_operational_routings rtg,
511 bom_bill_of_materials bom,
512 wip_parameters wp,
513 wip_job_schedule_interface wjsi
514 where wjsi.rowid = p_rowid
515 and nvl(rtg.cfm_routing_flag,2) = 2
516 and wac.class_code(+) = wjsi.class_code
517 and wac.organization_id(+) = wjsi.organization_id
518 and rtg.organization_id(+) = wjsi.organization_id
519 and nvl(rtg.alternate_routing_designator(+), 'NONEXISTENT') =
520 nvl(wjsi.alternate_routing_designator, 'NONEXISTENT')
521 and rtg.assembly_item_id(+) = decode(wjsi.load_type,
522 wip_constants.create_job, wjsi.primary_item_id,
523 wjsi.routing_reference_id)
524 and bom.assembly_item_id(+) = decode(wjsi.load_type,
525 wip_constants.create_job, wjsi.primary_item_id,
526 wjsi.bom_reference_id)
527 and bom.organization_id(+) = wjsi.organization_id
528 and nvl(bom.alternate_bom_designator(+), 'NON_EXISTENT') =
529 nvl(wjsi.alternate_bom_designator, 'NON_EXISTENT')
530 and wp.organization_id = wjsi.organization_id;
531
532 insert into wip_entities(
533 wip_entity_id,
534 organization_id,
535 last_update_date,
536 last_updated_by,
537 creation_date,
538 created_by,
539 last_update_login,
540 request_id,
541 program_application_id,
542 program_id,
543 program_update_date,
544 wip_entity_name,
545 entity_type,
546 description,
547 primary_item_id,
548 gen_object_id)
549 select
550 wjsi.wip_entity_id,
551 wjsi.organization_id,
552 sysdate,
553 wjsi.last_updated_by,
554 sysdate,
555 wjsi.created_by,
556 wjsi.last_update_login,
557 wjsi.request_id,
558 wjsi.program_application_id,
559 wjsi.program_id,
560 sysdate,
561 wjsi.job_name,
562 1,
563 wjsi.description,
564 wjsi.primary_item_id,
565 mtl_gen_object_id_s.nextval
566 from wip_job_schedule_interface wjsi
567 where wjsi.rowid = p_rowid;
568 end createWIPEntity;
569
570
571 procedure updateWIPEntity(p_rowid in rowid) is
572 l_wipEntityID number;
573 begin
574 select wip_entity_id
575 into l_wipEntityID
576 from wip_job_schedule_interface
577 where rowid = p_rowid;
578
579 update wip_discrete_jobs wdj
580 set (last_updated_by,
581 last_update_login,
582 request_id,
583 program_application_id,
584 program_id,
585 program_update_date,
586 last_update_date,
587 firm_planned_flag,
588 lot_number,
589 start_quantity,
590 net_quantity,
591 status_type,
592 DATE_RELEASED,
593 DATE_COMPLETED, /* fix bug 4760788 */
594 SCHEDULED_START_DATE,
595 SCHEDULED_COMPLETION_DATE,
596 SCHEDULE_GROUP_ID,
597 BUILD_SEQUENCE,
598 LINE_ID,
599 PROJECT_ID,
600 TASK_ID,
601 completion_subinventory,
602 COMPLETION_LOCATOR_ID,
603 DESCRIPTION,
604 SOURCE_CODE,
605 SOURCE_LINE_ID,
606 OVERCOMPLETION_TOLERANCE_TYPE,
607 OVERCOMPLETION_TOLERANCE_VALUE,
608 END_ITEM_UNIT_NUMBER,
609 PRIORITY,
610 DUE_DATE,
611 ATTRIBUTE_CATEGORY,
612 ATTRIBUTE1,
613 ATTRIBUTE2,
614 ATTRIBUTE3,
615 ATTRIBUTE4,
616 ATTRIBUTE5,
617 ATTRIBUTE6,
618 ATTRIBUTE7,
619 ATTRIBUTE8,
620 ATTRIBUTE9,
621 ATTRIBUTE10,
622 ATTRIBUTE11,
623 ATTRIBUTE12,
624 ATTRIBUTE13,
625 ATTRIBUTE14,
626 ATTRIBUTE15,
627 ROUTING_REVISION_DATE,
628 ROUTING_REVISION,
629 BOM_REVISION_DATE,
630 BOM_REVISION,
631 SERIALIZATION_START_OP,
632 BOM_REFERENCE_ID,
633 ROUTING_REFERENCE_ID,
634 ALTERNATE_BOM_DESIGNATOR,
635 ALTERNATE_ROUTING_DESIGNATOR,
636 WIP_SUPPLY_TYPE,-- Fix for bug 5440109
637 DEMAND_CLASS) =
638 (SELECT LAST_UPDATED_BY,
639 LAST_UPDATE_LOGIN,
640 request_id,
641 program_application_id,
642 program_id,
643 SYSDATE,
644 SYSDATE,
645 NVL(WJ.FIRM_PLANNED_FLAG,WDJ.FIRM_PLANNED_FLAG),
646 NVL(WJ.LOT_NUMBER,WDJ.LOT_NUMBER),
647 NVL(ROUND(WJ.START_QUANTITY, WIP_CONSTANTS.MAX_DISPLAYED_PRECISION),
648 WDJ.START_QUANTITY),
649 NVL(ROUND(WJ.NET_QUANTITY, WIP_CONSTANTS.MAX_DISPLAYED_PRECISION),
650 WDJ.NET_QUANTITY),
651 NVL(WJ.STATUS_TYPE,WDJ.STATUS_TYPE),
652 DECODE(WJ.STATUS_TYPE,
653 WIP_CONSTANTS.RELEASED,NVL(WDJ.DATE_RELEASED, NVL(WJ.DATE_RELEASED,SYSDATE)),
654 WIP_CONSTANTS.HOLD,NVL(WDJ.DATE_RELEASED,NVL(WJ.DATE_RELEASED,SYSDATE)),
655 WIP_CONSTANTS.UNRELEASED, NULL, /*bug 3061143*/
656 WDJ.DATE_RELEASED),
657 DECODE(WJ.STATUS_TYPE,
658 WIP_CONSTANTS.COMP_CHRG , NVL(WDJ.DATE_COMPLETED, SYSDATE),
659 WIP_CONSTANTS.RELEASED,NULL,
660 WIP_CONSTANTS.HOLD, NULL,
661 WIP_CONSTANTS.UNRELEASED, NULL,
662 WIP_CONSTANTS.CANCELLED, NULL,
663 WDJ.DATE_COMPLETED), /*Bug Number 4760788: Update date_completed*/
664 /* Fix bug 5238435. WJSI dates are replacing the scheduler calculated dates of WDJ */
665 DECODE(WJ.SCHEDULING_METHOD,
666 WIP_CONSTANTS.ROUTING, WDJ.SCHEDULED_START_DATE,
667 NVL(WJ.FIRST_UNIT_START_DATE,WDJ.SCHEDULED_START_DATE)),
668 DECODE(WJ.SCHEDULING_METHOD,
669 WIP_CONSTANTS.ROUTING, WDJ.SCHEDULED_COMPLETION_DATE,
670 NVL(WJ.LAST_UNIT_COMPLETION_DATE, WDJ.SCHEDULED_COMPLETION_DATE)),
671 NVL(WJ.SCHEDULE_GROUP_ID,WDJ.SCHEDULE_GROUP_ID),
672 NVL(WJ.BUILD_SEQUENCE,WDJ.BUILD_SEQUENCE),
673 NVL(WJ.LINE_ID,WDJ.LINE_ID),
674 -- If PROJECT_ID is null in the interface table,
675 -- leave PROJECT_ID, TASK_ID, and
676 -- COMPLETION_LOCATOR_ID set to their old values.
677 -- Otherwise, update them to have the interface
678 -- table values, even if some of those values are null.
679 decode (
680 WJ.PROJECT_ID,
681 null, WDJ.PROJECT_ID,
682 WJ.PROJECT_ID
683 ),
684 decode (
685 WJ.PROJECT_ID,
686 null, WDJ.TASK_ID,
687 WJ.TASK_ID
688 ),
689 /*nvl(wj.completion_subinventory, wdj.completion_subinventory),
690 decode (
691 wj.completion_subinventory,
692 null, wdj.completion_locator_id,
693 wj.completion_locator_id),*/
694 /* Bug 5446216 (FP Bug 5504790) : Completion subinventory and/or locator will be nulled out
695 when fnd_api.g_miss_char and fnd_api.g_miss_num is passed for respective fields */
696 decode(wj.completion_subinventory,
697 NULL, wdj.completion_subinventory,
698 fnd_api.g_miss_char, NULL,
699 wj.completion_subinventory),
700 decode(wj.completion_subinventory,
701 NULL, decode(wj.completion_locator_id,
702 NULL, wdj.completion_locator_id,
703 wj.completion_locator_id),
704 fnd_api.g_miss_char, NULL,
705 decode(wj.completion_locator_id,
706 fnd_api.g_miss_num, NULL,
707 wj.completion_locator_id)),
708 NVL(WJ.DESCRIPTION,WDJ.DESCRIPTION),
709 NVL(WJ.SOURCE_CODE,WDJ.SOURCE_CODE),
710 NVL(WJ.SOURCE_LINE_ID,WDJ.SOURCE_LINE_ID),
711 NVL(WJ.OVERCOMPLETION_TOLERANCE_TYPE,
712 WDJ.OVERCOMPLETION_TOLERANCE_TYPE),
713 NVL(WJ.OVERCOMPLETION_TOLERANCE_VALUE,
714 WDJ.OVERCOMPLETION_TOLERANCE_VALUE),
715 NVL(WJ.END_ITEM_UNIT_NUMBER,
716 WDJ.END_ITEM_UNIT_NUMBER),
717 NVL(WJ.PRIORITY,WDJ.PRIORITY),
718 NVL(WJ.DUE_DATE,WDJ.DUE_DATE),
719 NVL(WJ.ATTRIBUTE_CATEGORY,WDJ.ATTRIBUTE_CATEGORY),
720 NVL(WJ.ATTRIBUTE1,WDJ.ATTRIBUTE1),
721 NVL(WJ.ATTRIBUTE2,WDJ.ATTRIBUTE2),
722 NVL(WJ.ATTRIBUTE3,WDJ.ATTRIBUTE3),
723 NVL(WJ.ATTRIBUTE4,WDJ.ATTRIBUTE4),
724 NVL(WJ.ATTRIBUTE5,WDJ.ATTRIBUTE5),
725 NVL(WJ.ATTRIBUTE6,WDJ.ATTRIBUTE6),
726 NVL(WJ.ATTRIBUTE7,WDJ.ATTRIBUTE7),
727 NVL(WJ.ATTRIBUTE8,WDJ.ATTRIBUTE8),
728 NVL(WJ.ATTRIBUTE9,WDJ.ATTRIBUTE9),
729 NVL(WJ.ATTRIBUTE10,WDJ.ATTRIBUTE10),
730 NVL(WJ.ATTRIBUTE11,WDJ.ATTRIBUTE11),
731 NVL(WJ.ATTRIBUTE12,WDJ.ATTRIBUTE12),
732 NVL(WJ.ATTRIBUTE13,WDJ.ATTRIBUTE13),
733 NVL(WJ.ATTRIBUTE14,WDJ.ATTRIBUTE14),
734 NVL(WJ.ATTRIBUTE15,WDJ.ATTRIBUTE15),
735 --Bug 5230849:Start of changes
736 --Routing and bom details should be updated only when the job is in unreleased status
737 --TO achieve this,decode on status type is added.
738 DECODE(NVL(WJ.STATUS_TYPE,WDJ.STATUS_TYPE),WIP_CONSTANTS.UNRELEASED,
739 NVL(TO_DATE(TO_CHAR(wj.routing_revision_date, WIP_CONSTANTS.DT_NOSEC_FMT),
740 WIP_CONSTANTS.DT_NOSEC_FMT), WDJ.ROUTING_REVISION_DATE),
741 WDJ.ROUTING_REVISION_DATE),
742 DECODE(NVL(WJ.STATUS_TYPE,WDJ.STATUS_TYPE),WIP_CONSTANTS.UNRELEASED,
743 NVL(WJ.ROUTING_REVISION,WDJ.ROUTING_REVISION),WDJ.ROUTING_REVISION),
744 DECODE(NVL(WJ.STATUS_TYPE,WDJ.STATUS_TYPE),WIP_CONSTANTS.UNRELEASED,
745 NVL(TO_DATE(TO_CHAR(wj.bom_revision_date, WIP_CONSTANTS.DT_NOSEC_FMT),
746 WIP_CONSTANTS.DT_NOSEC_FMT),WDJ.BOM_REVISION_DATE),
747 WDJ.BOM_REVISION_DATE),
748 DECODE(NVL(WJ.STATUS_TYPE,WDJ.STATUS_TYPE),WIP_CONSTANTS.UNRELEASED,
749 NVL(WJ.BOM_REVISION,WDJ.BOM_REVISION),WDJ.BOM_REVISION),
750 NVL(WJ.SERIALIZATION_START_OP, WDJ.SERIALIZATION_START_OP),
751 --DECODE(WDJ.JOB_TYPE, wip_constants.nonstandard, nvl(WJ.BOM_REFERENCE_ID, WDJ.BOM_REFERENCE_ID), null),
752 --DECODE(WDJ.JOB_TYPE, wip_constants.nonstandard, nvl(WJ.ROUTING_REFERENCE_ID, WDJ.ROUTING_REFERENCE_ID), null),
753 /* Modified for bug 5479283. Now bom/routing reference fields will be maintained with old value when null is passed. */
754 DECODE(WDJ.JOB_TYPE, wip_constants.nonstandard,
755 DECODE(nvl(WJ.STATUS_TYPE,WDJ.STATUS_TYPE),WIP_CONSTANTS.UNRELEASED,
756 WJ.BOM_REFERENCE_ID,WDJ.BOM_REFERENCE_ID),
757 null),
758 DECODE(WDJ.JOB_TYPE, wip_constants.nonstandard,
759 DECODE(nvl(WJ.STATUS_TYPE,WDJ.STATUS_TYPE),WIP_CONSTANTS.UNRELEASED,
760 WJ.ROUTING_REFERENCE_ID,WDJ.ROUTING_REFERENCE_ID),
761 null),
762 --Bug 5230849:End of changes for checking job status.
763 --Bug 5230849:Start of changes:
764 --Bom/Routing designator should be updatable for non-standard jobs also.
765 --If bom/rou designator is g_miss_char,existing value should be retained.
766 --No check on status type is needed becaue this is already considered during validation phase.
767 --DECODE(WDJ.JOB_TYPE, wip_constants.standard, nvl(WJ.ALTERNATE_BOM_DESIGNATOR, WDJ.ALTERNATE_BOM_DESIGNATOR), null),
768 --DECODE(WDJ.JOB_TYPE, wip_constants.standard, nvl(WJ.ALTERNATE_ROUTING_DESIGNATOR, WDJ.ALTERNATE_ROUTING_DESIGNATOR), null)
769 /*DECODE(WDJ.JOB_TYPE, wip_constants.standard,
770 DECODE(WJ.ALTERNATE_BOM_DESIGNATOR,fnd_api.g_miss_char,WDJ.ALTERNATE_BOM_DESIGNATOR,WJ.ALTERNATE_BOM_DESIGNATOR),
771 DECODE(
772 DECODE(NVL(WJ.STATUS_TYPE,WDJ.STATUS_TYPE),WIP_CONSTANTS.UNRELEASED,WJ.BOM_REFERENCE_ID,WDJ.BOM_REFERENCE_ID),
773 NULL,NULL,
774 DECODE(WJ.ALTERNATE_BOM_DESIGNATOR,fnd_api.g_miss_char,WDJ.ALTERNATE_BOM_DESIGNATOR,WJ.ALTERNATE_BOM_DESIGNATOR))),
775 DECODE(WDJ.JOB_TYPE, wip_constants.standard,
776 DECODE(WJ.ALTERNATE_ROUTING_DESIGNATOR,fnd_api.g_miss_char,WDJ.ALTERNATE_ROUTING_DESIGNATOR,WJ.ALTERNATE_ROUTING_DESIGNATOR),
777 DECODE(
778 DECODE(NVL(WJ.STATUS_TYPE,WDJ.STATUS_TYPE),WIP_CONSTANTS.UNRELEASED,WJ.ROUTING_REFERENCE_ID,WDJ.ROUTING_REFERENCE_ID),
779 NULL,NULL,
780 DECODE(WJ.ALTERNATE_ROUTING_DESIGNATOR,fnd_api.g_miss_char,WDJ.ALTERNATE_ROUTING_DESIGNATOR,WJ.ALTERNATE_ROUTING_DESIGNATOR))),
781 Modified update on ALTERNATE_BOM_DESIGNATOR,ALTERNATE_ROUTING_DESIGNATOR for bug 5479283.
782 This maintains old value when null is passed */
783 DECODE(WDJ.JOB_TYPE, wip_constants.standard,WJ.ALTERNATE_BOM_DESIGNATOR,
784 DECODE(DECODE(NVL(WJ.STATUS_TYPE,WDJ.STATUS_TYPE),WIP_CONSTANTS.UNRELEASED,WJ.BOM_REFERENCE_ID,WDJ.BOM_REFERENCE_ID),
785 NULL,NULL,WJ.ALTERNATE_BOM_DESIGNATOR)),
786 DECODE(WDJ.JOB_TYPE, wip_constants.standard,WJ.ALTERNATE_ROUTING_DESIGNATOR,
787 DECODE(DECODE(NVL(WJ.STATUS_TYPE,WDJ.STATUS_TYPE),WIP_CONSTANTS.UNRELEASED,WJ.ROUTING_REFERENCE_ID,WDJ.ROUTING_REFERENCE_ID),
788 NULL,NULL,WJ.ALTERNATE_ROUTING_DESIGNATOR)),
789 nvl(wj.wip_supply_type,wdj.wip_supply_type), -- Fix for bug 5440109
790 nvl(wj.demand_class,wdj.demand_class) -- Fix for bug 5440109
791 --Bug 5230849:End of changes for bom/alternate designator updation.
792 FROM WIP_JOB_SCHEDULE_INTERFACE WJ
793 WHERE WJ.ROWID = p_rowid)
794 WHERE WDJ.WIP_ENTITY_ID = l_wipEntityID;
795
796 UPDATE WIP_ENTITIES WE
797 SET (DESCRIPTION,
798 LAST_UPDATED_BY,
799 last_update_login,
800 request_id,
801 program_application_id,
802 program_id,
803 program_update_date,
804 last_update_date)
805 = (SELECT NVL(WJ.DESCRIPTION, WE.DESCRIPTION),
806 LAST_UPDATED_BY,
807 LAST_UPDATE_LOGIN,
808 REQUEST_ID,
809 PROGRAM_APPLICATION_ID,
810 PROGRAM_ID,
811 SYSDATE,
812 SYSDATE
813 FROM WIP_JOB_SCHEDULE_INTERFACE WJ
814 WHERE WJ.ROWID = p_rowid)
815 WHERE WE.WIP_ENTITY_ID = l_wipEntityID;
816 end updateWIPEntity;
817
818 end wip_massload_pvt;