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