[Home] [Help]
PACKAGE BODY: APPS.WIP_JSI_PROCESSOR
Source
1 package body WIP_JSI_Processor as
2 /* $Header: wipjsipb.pls 120.1.12010000.2 2010/02/05 01:49:53 pding ship $ */
3
4
5
6 PROCEDURE update_parent_tables is
7
8 x_load_type number ;
9 x_status_type number ;
10 x_entity_id number ;
11 x_org_id number ;
12 x_item_id number;
13 x_cmn_bom_seq_id number;
14 x_cmn_routing_seq_id number;
15 x_rep_sched_id number ;
16 x_mo_req_number VARCHAR2(20);
17 x_conc_req_id number;
18 x_old_start_quantity number;
19 x_new_start_quantity number;
20 l_previous_job_status number;
21 l_create_requisition boolean := TRUE;
22 l_pm_sched_id NUMBER;
23 l_plan_maint varchar2(1);
24 l_status VARCHAR2(1);
25 l_msg_data VARCHAR2(2000);
26 l_move_order_required VARCHAR2(1);
27 l_allocTbl wip_picking_pub.allocate_tbl_t;
28 l_task_id NUMBER;
29 l_project_id NUMBER;
30
31 BEGIN
32 select
33 load_type,
34 status_type,
35 wip_entity_id,
36 organization_id,
37 repetitive_schedule_id,
38 start_quantity,
39 pm_schedule_id,
40 project_id,
41 task_id,
42 material_issue_by_mo
43 into
44 x_load_type,
45 x_status_type,
46 x_entity_id,
47 x_org_id,
48 x_rep_sched_id,
49 x_new_start_quantity,
50 l_pm_sched_id,
51 l_project_id,
52 l_task_id,
53 l_move_order_required
54 from wip_job_schedule_interface
55 where rowid = WIP_JSI_Utils.current_rowid ;
56
57 if (x_load_type in (WIP_CONSTANTS.RESCHED_JOB, WIP_CONSTANTS.RESCHED_LOT_JOB)) then
58 begin
59 select wdj.start_quantity
60 into x_old_start_quantity
61 FROM wip_discrete_jobs wdj,
62 wip_job_schedule_interface wi
63 WHERE
64 wi.rowid = WIP_JSI_Utils.current_rowid and
65 wi.wip_entity_id = wdj.wip_entity_id;
66 exception
67 when no_data_found then
68 x_old_start_quantity := null;
69 end;
70 end if;
71
72 if (UPPER(l_move_order_required) = 'Y' and x_status_type = WIP_CONSTANTS.RELEASED) then
73 l_allocTbl(1).wip_entity_id := x_entity_id;
74 l_allocTbl(1).repetitive_schedule_id := x_rep_sched_id;
75 l_allocTbl(1).project_id := l_project_id;
76 l_allocTbl(1).task_id := l_task_id;
77 l_allocTbl(1).use_pickset_flag := 'N';
78
79 wip_picking_pvt.allocate(p_alloc_tbl => l_allocTbl,
80 p_cutoff_date => null, -- no cutoff day
81 p_wip_entity_type => wip_constants.eam,
82 p_organization_id => x_org_id,
83 x_mo_req_number => x_mo_req_number,
84 x_conc_req_id => x_conc_req_id,
85 x_return_status => l_status,
86 x_msg_data => l_msg_data
87 );
88
89 if(l_status in ('S', 'P')) then
90 wip_jsi_utils.record_error_text( l_status || ':' || x_mo_req_number || ': ' || l_msg_data, true) ;
91 elsif(l_status in ('N', 'E')) then
92 wip_jsi_utils.record_error_text( l_status || ': ' || l_msg_data, true) ;
93 else
94 wip_jsi_utils.record_error_text( l_status || ': ' || l_msg_data, true);
95 end if;
96 end if;
97
98 if(l_pm_sched_id is not null) then
99 l_plan_maint := 'Y';
100 end if;
101
102 if (x_load_type IN (WIP_CONSTANTS.CREATE_JOB,
103 WIP_CONSTANTS.CREATE_NS_JOB,
104 WIP_CONSTANTS.CREATE_EAM_JOB))
105 then
106
107 INSERT INTO WIP_DISCRETE_JOBS
108 (wip_entity_id,
109 organization_id,
110 last_update_date,
111 last_updated_by,
112 creation_date,
113 created_by,
114 last_update_login,
115 request_id,
116 program_application_id,
117 program_id,
118 program_update_date,
119 source_line_id,
120 source_code,
121 description,
122 status_type,
123 date_released,
124 primary_item_id,
125 bom_reference_id,
126 routing_reference_id,
127 firm_planned_flag,
128 job_type,
129 wip_supply_type,
130 class_code,
131 material_account,
132 material_overhead_account,
133 resource_account,
134 outside_processing_account,
135 material_variance_account,
136 resource_variance_account,
137 outside_proc_variance_account,
138 std_cost_adjustment_account,
139 overhead_account,
140 overhead_variance_account,
141 scheduled_start_date,
142 scheduled_completion_date,
143 start_quantity,
144 quantity_completed,
145 quantity_scrapped,
146 net_quantity,
147 common_bom_sequence_id,
148 common_routing_sequence_id,
149 bom_revision,
150 routing_revision,
151 bom_revision_date,
152 routing_revision_date,
153 lot_number,
154 alternate_bom_designator,
155 alternate_routing_designator,
156 completion_subinventory,
157 completion_locator_id,
158 demand_class,
159 project_id,
160 task_id,
161 schedule_group_id,
162 build_sequence,
163 line_id,
164 kanban_card_id,
165 overcompletion_tolerance_type,
166 overcompletion_tolerance_value,
167 end_item_unit_number,
168 po_creation_time,
169 priority,
170 due_date,
171 requested_start_date,
172 parent_wip_entity_id,
173 asset_group_id,
174 asset_number,
175 owning_department,
176 rebuild_item_id,
177 rebuild_serial_number,
178 manual_rebuild_flag,
179 activity_type,
180 activity_cause,
181 activity_source,
182 work_order_type,
183 notification_required,
184 shutdown_type,
185 pm_schedule_id,
186 plan_maintenance,
187 tagout_required,
188 attribute_category,
189 attribute1,
190 attribute2,
191 attribute3,
192 attribute4,
193 attribute5,
194 attribute6,
195 attribute7,
196 attribute8,
197 attribute9,
198 attribute10,
199 attribute11,
200 attribute12,
201 attribute13,
202 attribute14,
203 attribute15,
204 maintenance_object_id,
205 maintenance_object_type,
206 maintenance_object_source,
207 material_issue_by_mo,
208 serialization_start_op)
209 SELECT
210 wi.wip_entity_id,
211 wi.organization_id,
212 SYSDATE,
213 wi.last_updated_by,
214 SYSDATE,
215 wi.created_by,
216 wi.last_update_login,
217 wi.request_id,
218 wi.program_application_id,
219 wi.program_id,
220 SYSDATE,
221 wi.source_line_id,
222 wi.source_code,
223 wi.description,
224 wi.status_type,
225 decode(wi.status_type,
226 WIP_CONSTANTS.RELEASED,
227 decode(wi.date_released,NULL,SYSDATE,decode( sign(wi.date_released - sysdate),1,sysdate,wi.date_released)),
228 WIP_CONSTANTS.HOLD,
229 decode(wi.date_released,NULL,SYSDATE,decode( sign(wi.date_released - sysdate),1,sysdate,wi.date_released)),
230 WIP_CONSTANTS.UNRELEASED,
231 NULL,
232 NULL
233 ),
234 wi.primary_item_id,
235 decode(wi.load_type, WIP_CONSTANTS.CREATE_JOB, NULL,
236 wi.bom_reference_id),
237 decode(wi.load_type, WIP_CONSTANTS.CREATE_JOB, NULL,
238 wi.routing_reference_id),
239 wi.firm_planned_flag,
240 decode(wi.load_type, WIP_CONSTANTS.CREATE_JOB, WIP_CONSTANTS.STANDARD,
241 WIP_CONSTANTS.NONSTANDARD),
242 wi.wip_supply_type,
243 wi.class_code,
244 wac.material_account,
245 wac.material_overhead_account,
246 wac.resource_account,
247 wac.outside_processing_account,
248 wac.material_variance_account,
249 wac.resource_variance_account,
250 wac.outside_proc_variance_account,
251 wac.std_cost_adjustment_account,
252 wac.overhead_account,
253 wac.overhead_variance_account,
254 TO_DATE(TO_CHAR(wi.first_unit_start_date,
255 WIP_CONSTANTS.DT_NOSEC_FMT),
256 WIP_CONSTANTS.DT_NOSEC_FMT),
257 TO_DATE(TO_CHAR(wi.last_unit_completion_date,
258 WIP_CONSTANTS.DT_NOSEC_FMT),
259 WIP_CONSTANTS.DT_NOSEC_FMT),
260 ROUND(wi.start_quantity, WIP_CONSTANTS.MAX_DISPLAYED_PRECISION),
261 0,
262 0,
263 ROUND(wi.net_quantity, WIP_CONSTANTS.MAX_DISPLAYED_PRECISION),
264 bom.common_bill_sequence_id,
265 rtg.common_routing_sequence_id,
266 wi.bom_revision,
267 wi.routing_revision,
268 TO_DATE(TO_CHAR(wi.bom_revision_date,
269 WIP_CONSTANTS.DT_NOSEC_FMT),
270 WIP_CONSTANTS.DT_NOSEC_FMT),
271 TO_DATE(TO_CHAR(wi.routing_revision_date,
272 WIP_CONSTANTS.DT_NOSEC_FMT),
273 WIP_CONSTANTS.DT_NOSEC_FMT),
274 wi.lot_number,
275 wi.alternate_bom_designator,
276 wi.alternate_routing_designator,
277 wi.completion_subinventory,
278 wi.completion_locator_id,
279 wi.demand_class,
280 wi.project_id,
281 wi.task_id,
282 decode(wi.load_type, WIP_CONSTANTS.CREATE_EAM_JOB, NULL, wi.schedule_group_id),
283 decode(wi.load_type, WIP_CONSTANTS.CREATE_EAM_JOB, NULL, wi.build_sequence),
284 wi.line_id,
285 wi.kanban_card_id,
286 wi.overcompletion_tolerance_type,
287 wi.overcompletion_tolerance_value,
288 wi.end_item_unit_number,
289 params.po_creation_time,
290 nvl(wi.priority,WIP_CONSTANTS.DEFAULT_PRIORITY),
291 TO_DATE(TO_CHAR(wi.due_date,
292 WIP_CONSTANTS.DT_NOSEC_FMT),
293 WIP_CONSTANTS.DT_NOSEC_FMT),
294 TO_DATE(TO_CHAR(wi.requested_start_date,
295 WIP_CONSTANTS.DT_NOSEC_FMT),
296 WIP_CONSTANTS.DT_NOSEC_FMT),
297 decode(wi.load_type, WIP_CONSTANTS.CREATE_EAM_JOB, wi.parent_wip_entity_id, NULL),
298 decode(wi.load_type, WIP_CONSTANTS.CREATE_EAM_JOB, wi.asset_group_id, NULL),
299 decode(wi.load_type, WIP_CONSTANTS.CREATE_EAM_JOB, wi.asset_number, NULL),
300 decode(wi.load_type, WIP_CONSTANTS.CREATE_EAM_JOB, wi.owning_department, NULL),
301 decode(wi.load_type, WIP_CONSTANTS.CREATE_EAM_JOB, wi.rebuild_item_id, NULL),
302 decode(wi.load_type, WIP_CONSTANTS.CREATE_EAM_JOB, wi.rebuild_serial_number, NULL),
303 decode(wi.load_type, WIP_CONSTANTS.CREATE_EAM_JOB, wi.manual_rebuild_flag, NULL),
304 decode(wi.load_type, WIP_CONSTANTS.CREATE_EAM_JOB, wi.activity_type, NULL),
305 decode(wi.load_type, WIP_CONSTANTS.CREATE_EAM_JOB, wi.activity_cause, NULL),
306 decode(wi.load_type, WIP_CONSTANTS.CREATE_EAM_JOB, wi.activity_source, NULL),
307 decode(wi.load_type, WIP_CONSTANTS.CREATE_EAM_JOB, wi.work_order_type, NULL),
308 decode(wi.load_type, WIP_CONSTANTS.CREATE_EAM_JOB, wi.notification_required, NULL),
309 decode(wi.load_type, WIP_CONSTANTS.CREATE_EAM_JOB, wi.shutdown_type, NULL),
310 decode(wi.load_type, WIP_CONSTANTS.CREATE_EAM_JOB, wi.pm_schedule_id, NULL),
311 decode(wi.load_type, WIP_CONSTANTS.CREATE_EAM_JOB, l_plan_maint, NULL),
312 decode(wi.load_type, WIP_CONSTANTS.CREATE_EAM_JOB, wi.tagout_required, NULL),
313 wi.attribute_category,
314 wi.attribute1,
315 wi.attribute2,
316 wi.attribute3,
317 wi.attribute4,
318 wi.attribute5,
319 wi.attribute6,
320 wi.attribute7,
321 wi.attribute8,
322 wi.attribute9,
323 wi.attribute10,
324 wi.attribute11,
325 wi.attribute12,
326 wi.attribute13,
327 wi.attribute14,
328 wi.attribute15,
329 wi.maintenance_object_id,
330 wi.maintenance_object_type,
331 wi.maintenance_object_source,
332 wi.material_issue_by_mo,
333 decode(wi.load_type, WIP_CONSTANTS.CREATE_JOB, wi.serialization_start_op,
334 WIP_CONSTANTS.CREATE_NS_JOB, wi.serialization_start_op, NULL)
335 FROM wip_accounting_classes wac,
336 bom_operational_routings rtg,
337 bom_bill_of_materials bom,
338 wip_parameters params,
339 wip_job_schedule_interface wi
340 WHERE
341 wi.rowid = WIP_JSI_Utils.current_rowid
342 AND nvl(rtg.cfm_routing_flag,2) = 2
343 AND wac.class_code(+)=wi.class_code
344 AND wac.organization_id(+)=wi.organization_id
345 AND rtg.organization_id(+)=wi.organization_id
346 AND NVL(rtg.alternate_routing_designator(+),'NONEXISTENT') =
347 NVL(wi.alternate_routing_designator,'NONEXISTENT')
348 AND rtg.assembly_item_id(+) =
349 decode(wi.load_type,1,wi.primary_item_id, wi.routing_reference_id)
350 AND bom.assembly_item_id (+) =
351 decode(wi.load_type,1,wi.primary_item_id, wi.bom_reference_id)
352 AND bom.organization_id (+) = wi.organization_id
353 AND NVL(bom.alternate_bom_designator (+),'NON_EXISTENT') =
354 NVL(wi.alternate_bom_designator,'NON_EXISTENT')
355 AND params.organization_id = wi.organization_id ;
356
357 INSERT INTO WIP_ENTITIES
358 (wip_entity_id,
359 organization_id,
360 last_update_date,
361 last_updated_by,
362 creation_date,
363 created_by,
364 last_update_login,
365 request_id,
366 program_application_id,
367 program_id,
368 program_update_date,
369 wip_entity_name,
370 entity_type,
371 description,
372 primary_item_id,
373 gen_object_id)
374 SELECT
375 wi.wip_entity_id,
376 wi.organization_id,
377 SYSDATE,
378 wi.last_updated_by,
379 SYSDATE,
380 wi.created_by,
381 wi.last_update_login,
382 wi.request_id,
383 wi.program_application_id,
384 wi.program_id,
385 SYSDATE,
386 wi.job_name,
387 decode(wi.load_type, WIP_CONSTANTS.CREATE_EAM_JOB, 6, 1),
388 wi.description,
389 wi.primary_item_id,
390 MTL_GEN_OBJECT_ID_S.nextval
391 FROM WIP_JOB_SCHEDULE_INTERFACE wi
392 WHERE wi.rowid = WIP_JSI_Utils.current_rowid ;
393
394 /* Fix for Bug#3201290 */
395 if ((x_load_type = WIP_CONSTANTS.CREATE_EAM_JOB)
396 and (x_status_type = WIP_CONSTANTS.RELEASED)) then
397 WIP_EAM_UTILS.create_default_operation ( x_org_id,
398 x_entity_id
399 ) ;
400 end if ;
401
402 -- bug#2798688, per eam team, copy attachments is only needed when
403 -- creating jobs through massload(or PM scheduler which uses ML)
404 select wdj.organization_id, wdj.wip_entity_id, wdj.primary_item_id,
405 wdj.common_bom_sequence_id, wdj.common_routing_sequence_id
406 into
407 x_org_id, x_entity_id, x_item_id, x_cmn_bom_seq_id, x_cmn_routing_seq_id
408 FROM wip_discrete_jobs wdj,
409 wip_job_schedule_interface wi
410 WHERE
411 wi.rowid = WIP_JSI_Utils.current_rowid and
412 wi.wip_entity_id = wdj.wip_entity_id;
413
414 WIP_EAM_UTILS.copy_attachments(
415 'Y', /* copy_asset_attachments IN VARCHAR2, */
416 'Y', /* copy_activity_attachments IN VARCHAR2, */
417 'Y', /* copy_activity_bom_attachments IN VARCHAR2, */
418 'Y', /* copy_activity_rtng_attachments IN VARCHAR2, */
419 x_org_id, /* p_organization_id IN NUMBER, */
420 x_entity_id, /* p_wip_entity_id IN NUMBER, */
421 x_item_id, /* p_primary_item_id IN NUMBER, */
422 x_cmn_bom_seq_id, /* p_common_bom_sequence_id IN NUMBER, */
423 x_cmn_routing_seq_id /* p_common_routing_sequence_id IN NUMBER */
424 );
425
426
427 elsif (x_load_type IN (WIP_CONSTANTS.RESCHED_JOB, WIP_CONSTANTS.RESCHED_EAM_JOB)) then
428
429 UPDATE WIP_DISCRETE_JOBS WDJ
430 SET ( LAST_UPDATED_BY,
431 last_update_login,
432 request_id,
433 program_application_id,
434 program_id,
435 program_update_date,
436 LAST_UPDATE_DATE,
437 FIRM_PLANNED_FLAG,
438 LOT_NUMBER,
439 START_QUANTITY,
440 NET_QUANTITY,
441 STATUS_TYPE,
442 DATE_RELEASED,
443 DATE_COMPLETED, /*Bug Number 4760788*/
444 SCHEDULED_START_DATE,
445 SCHEDULED_COMPLETION_DATE,
446 SCHEDULE_GROUP_ID,
447 BUILD_SEQUENCE,
448 LINE_ID,
449 PROJECT_ID,
450 TASK_ID,
451 completion_subinventory,
452 COMPLETION_LOCATOR_ID,
453 DESCRIPTION,
454 SOURCE_CODE,
455 SOURCE_LINE_ID,
456 OVERCOMPLETION_TOLERANCE_TYPE,
457 OVERCOMPLETION_TOLERANCE_VALUE,
458 END_ITEM_UNIT_NUMBER,
459 PRIORITY,
460 DUE_DATE,
461 WORK_ORDER_TYPE,
462 OWNING_DEPARTMENT,
463 ACTIVITY_TYPE,
464 ACTIVITY_CAUSE,
465 ACTIVITY_SOURCE,
466 NOTIFICATION_REQUIRED,
467 SHUTDOWN_TYPE,
468 ATTRIBUTE_CATEGORY,
469 ATTRIBUTE1,
470 ATTRIBUTE2,
471 ATTRIBUTE3,
472 ATTRIBUTE4,
473 ATTRIBUTE5,
474 ATTRIBUTE6,
475 ATTRIBUTE7,
476 ATTRIBUTE8,
477 ATTRIBUTE9,
478 ATTRIBUTE10,
479 ATTRIBUTE11,
480 ATTRIBUTE12,
481 ATTRIBUTE13,
482 ATTRIBUTE14,
483 ATTRIBUTE15,
484 MAINTENANCE_OBJECT_ID,
485 MAINTENANCE_OBJECT_TYPE,
486 MAINTENANCE_OBJECT_SOURCE,
487 material_issue_by_mo,
488 BOM_REVISION_DATE,
489 BOM_REVISION,
490 SERIALIZATION_START_OP) =
491 (SELECT LAST_UPDATED_BY,
492 LAST_UPDATE_LOGIN,
493 request_id,
494 program_application_id,
495 program_id,
496 SYSDATE,
497 SYSDATE,
498 NVL(WJ.FIRM_PLANNED_FLAG,WDJ.FIRM_PLANNED_FLAG),
499 NVL(WJ.LOT_NUMBER,WDJ.LOT_NUMBER),
500 decode(wj.load_type, 7, 1, 8, WDJ.START_QUANTITY,
501 NVL(ROUND(WJ.START_QUANTITY,
502 WIP_CONSTANTS.MAX_DISPLAYED_PRECISION),
503 WDJ.START_QUANTITY)),
504 decode(wj.load_type, 7, 1, 8, WDJ.START_QUANTITY,
505 NVL(ROUND(WJ.NET_QUANTITY,
506 WIP_CONSTANTS.MAX_DISPLAYED_PRECISION),
507 WDJ.NET_QUANTITY)),
508 NVL(WJ.STATUS_TYPE,WDJ.STATUS_TYPE),
509 DECODE(WJ.STATUS_TYPE,
510 WIP_CONSTANTS.RELEASED,NVL(WDJ.DATE_RELEASED, NVL(WJ.DATE_RELEASED,SYSDATE)),
511 WIP_CONSTANTS.HOLD,NVL(WDJ.DATE_RELEASED,NVL(WJ.DATE_RELEASED,SYSDATE)),
512 WIP_CONSTANTS.UNRELEASED, NULL, /*bug 3061143*/
513 WDJ.DATE_RELEASED),
514 DECODE(WJ.STATUS_TYPE,
515 WIP_CONSTANTS.COMP_CHRG , NVL(WDJ.DATE_COMPLETED, SYSDATE),
516 WIP_CONSTANTS.RELEASED,NULL,
517 WIP_CONSTANTS.HOLD, NULL,
518 WIP_CONSTANTS.UNRELEASED, NULL,
519 WIP_CONSTANTS.CANCELLED, NULL,
520 WDJ.DATE_COMPLETED), /*Bug Number 4760788: Update date_completed*/
521 NVL(TO_DATE(TO_CHAR(WJ.FIRST_UNIT_START_DATE,WIP_CONSTANTS.DT_NOSEC_FMT), WIP_CONSTANTS.DT_NOSEC_FMT),
522 WDJ.SCHEDULED_START_DATE),/*Fix for Bug 8784056 (FP of 8704687), format date before upate WDJ*/
523 NVL(TO_DATE(TO_CHAR(WJ.LAST_UNIT_COMPLETION_DATE,WIP_CONSTANTS.DT_NOSEC_FMT), WIP_CONSTANTS.DT_NOSEC_FMT),
524 WDJ.SCHEDULED_COMPLETION_DATE),/*Fix for Bug 8784056 (FP of 8704687), format date before upate WDJ*/
525 NVL(WJ.SCHEDULE_GROUP_ID,WDJ.SCHEDULE_GROUP_ID),
526 NVL(WJ.BUILD_SEQUENCE,WDJ.BUILD_SEQUENCE),
527 NVL(WJ.LINE_ID,WDJ.LINE_ID),
528
529 -- If PROJECT_ID is null in the interface table,
530 -- leave PROJECT_ID, TASK_ID, and
531 -- COMPLETION_LOCATOR_ID set to their old values.
532 -- Otherwise, update them to have the interface
533 -- table values, even if some of those values are null.
534 decode (
535 WJ.PROJECT_ID,
536 null, WDJ.PROJECT_ID,
537 WJ.PROJECT_ID
538 ) ,
539 decode (
540 WJ.PROJECT_ID,
541 null, WDJ.TASK_ID,
542 WJ.TASK_ID
543 ) ,
544 -- bug#4099186
545 -- if rescheduling, sub/loc are now modifiable
546 -- will update from both wjsi.completion_subinventory and
547 -- and wjsi.locator only if wjsi.completion_subinventory is not null
548 nvl(wj.completion_subinventory, wdj.completion_subinventory),
549 decode (
550 wj.completion_subinventory,
551 null, wdj.completion_locator_id,
552 wj.completion_locator_id),
553 /*decode (
554 WJ.PROJECT_ID,
555 null, WDJ.COMPLETION_LOCATOR_ID,
556 WJ.COMPLETION_LOCATOR_ID
557 ) ,*/
558
559 NVL(WJ.DESCRIPTION,WDJ.DESCRIPTION),
560 NVL(WJ.SOURCE_CODE,WDJ.SOURCE_CODE),
561 NVL(WJ.SOURCE_LINE_ID,WDJ.SOURCE_LINE_ID),
562 NVL(WJ.OVERCOMPLETION_TOLERANCE_TYPE,
563 WDJ.OVERCOMPLETION_TOLERANCE_TYPE),
564 NVL(WJ.OVERCOMPLETION_TOLERANCE_VALUE,
565 WDJ.OVERCOMPLETION_TOLERANCE_VALUE),
566 NVL(WJ.END_ITEM_UNIT_NUMBER,
567 WDJ.END_ITEM_UNIT_NUMBER),
568 NVL(WJ.PRIORITY,WDJ.PRIORITY),
569 NVL(WJ.DUE_DATE,WDJ.DUE_DATE),
570 decode(wj.load_type, WIP_CONSTANTS.RESCHED_EAM_JOB, NVL(wj.work_order_type, WDJ.WORK_ORDER_TYPE),
571 WDJ.WORK_ORDER_TYPE),
572 decode(wj.load_type, WIP_CONSTANTS.RESCHED_EAM_JOB, NVL(WJ.OWNING_DEPARTMENT, WDJ.OWNING_DEPARTMENT),
573 WDJ.OWNING_DEPARTMENT),
574 decode(wj.load_type, WIP_CONSTANTS.RESCHED_EAM_JOB, NVL(wj.activity_type, WDJ.ACTIVITY_TYPE),
575 WDJ.ACTIVITY_TYPE),
576 decode(wj.load_type, WIP_CONSTANTS.RESCHED_EAM_JOB, NVL(wj.activity_cause, WDJ.ACTIVITY_CAUSE),
577 WDJ.ACTIVITY_CAUSE),
578 decode(wj.load_type, WIP_CONSTANTS.RESCHED_EAM_JOB, NVL(wj.activity_source, WDJ.ACTIVITY_SOURCE),
579 WDJ.ACTIVITY_SOURCE),
580 decode(wj.load_type, WIP_CONSTANTS.RESCHED_EAM_JOB, NVL(WJ.NOTIFICATION_REQUIRED, WDJ.NOTIFICATION_REQUIRED),
581 WDJ.NOTIFICATION_REQUIRED),
582 decode(wj.load_type, WIP_CONSTANTS.RESCHED_EAM_JOB, NVL(wj.shutdown_type, WDJ.SHUTDOWN_TYPE),
583 WDJ.SHUTDOWN_TYPE),
584 NVL(WJ.ATTRIBUTE_CATEGORY,WDJ.ATTRIBUTE_CATEGORY),
585 NVL(WJ.ATTRIBUTE1,WDJ.ATTRIBUTE1),
586 NVL(WJ.ATTRIBUTE2,WDJ.ATTRIBUTE2),
587 NVL(WJ.ATTRIBUTE3,WDJ.ATTRIBUTE3),
588 NVL(WJ.ATTRIBUTE4,WDJ.ATTRIBUTE4),
589 NVL(WJ.ATTRIBUTE5,WDJ.ATTRIBUTE5),
590 NVL(WJ.ATTRIBUTE6,WDJ.ATTRIBUTE6),
591 NVL(WJ.ATTRIBUTE7,WDJ.ATTRIBUTE7),
592 NVL(WJ.ATTRIBUTE8,WDJ.ATTRIBUTE8),
593 NVL(WJ.ATTRIBUTE9,WDJ.ATTRIBUTE9),
594 NVL(WJ.ATTRIBUTE10,WDJ.ATTRIBUTE10),
595 NVL(WJ.ATTRIBUTE11,WDJ.ATTRIBUTE11),
596 NVL(WJ.ATTRIBUTE12,WDJ.ATTRIBUTE12),
597 NVL(WJ.ATTRIBUTE13,WDJ.ATTRIBUTE13),
598 NVL(WJ.ATTRIBUTE14,WDJ.ATTRIBUTE14),
599 NVL(WJ.ATTRIBUTE15,WDJ.ATTRIBUTE15),
600 NVL(WJ.MAINTENANCE_OBJECT_ID,WDJ.MAINTENANCE_OBJECT_ID),
601 NVL(WJ.MAINTENANCE_OBJECT_TYPE,WDJ.MAINTENANCE_OBJECT_TYPE),
602 NVL(WJ.MAINTENANCE_OBJECT_SOURCE,WDJ.MAINTENANCE_OBJECT_SOURCE),
603 NVL(WJ.material_issue_by_mo,WDJ.material_issue_by_mo),
604 NVL(TO_DATE(TO_CHAR(wj.bom_revision_date,
605 WIP_CONSTANTS.DT_NOSEC_FMT),
606 WIP_CONSTANTS.DT_NOSEC_FMT),
607 WDJ.BOM_REVISION_DATE),
608 NVL(WJ.BOM_REVISION,WDJ.BOM_REVISION),
609 DECODE(WJ.LOAD_TYPE, WIP_CONSTANTS.RESCHED_JOB,
610 NVL(WJ.SERIALIZATION_START_OP, WDJ.SERIALIZATION_START_OP), WDJ.SERIALIZATION_START_OP)
611 FROM WIP_JOB_SCHEDULE_INTERFACE WJ
612 WHERE WJ.ROWID = WIP_JSI_Utils.current_rowid)
613 WHERE WDJ.WIP_ENTITY_ID = x_entity_id ;
614
615 UPDATE WIP_ENTITIES WE
616 SET (DESCRIPTION,
617 LAST_UPDATED_BY,
618 last_update_login,
619 request_id,
620 program_application_id,
621 program_id,
622 program_update_date,
623 last_update_date)
624 = (SELECT NVL(WJ.DESCRIPTION, WE.DESCRIPTION),
625 LAST_UPDATED_BY,
626 LAST_UPDATE_LOGIN,
627 REQUEST_ID,
628 PROGRAM_APPLICATION_ID,
629 PROGRAM_ID,
630 SYSDATE,
631 SYSDATE
632 FROM WIP_JOB_SCHEDULE_INTERFACE WJ
633 WHERE WJ.ROWID = WIP_JSI_Utils.current_rowid)
634 WHERE WE.WIP_ENTITY_ID = x_entity_id ;
635
636 /* Fix for Bug#3201290 */
637 if ((x_load_type = WIP_CONSTANTS.RESCHED_EAM_JOB)
638 and (x_status_type = WIP_CONSTANTS.RELEASED)) then
639 WIP_EAM_UTILS.create_default_operation ( x_org_id,
640 x_entity_id
641 ) ;
642 end if ;
643
644 if (x_load_type in (WIP_CONSTANTS.RESCHED_JOB, WIP_CONSTANTS.RESCHED_LOT_JOB) and
645 wip_picking_pub.is_job_pick_released(x_entity_id, x_rep_sched_id, x_org_id) and
646 x_old_start_quantity <> x_new_start_quantity) then
647 FND_MESSAGE.set_name('WIP','WIP_QTY_REQ_CHANGE_WARNING');
648 wip_jsi_utils.record_current_error(TRUE);
649
650 wip_picking_pub.Update_Job_BackOrdQty (p_wip_entity_id => x_entity_id,
651 p_repetitive_schedule_id => x_rep_sched_id,
652 p_new_job_qty => x_new_start_quantity,
653 x_return_status => l_status,
654 x_msg_data => l_msg_data);
655
656 if(l_status <> FND_API.G_RET_STS_SUCCESS) then
657 fnd_message.set_name('WIP', 'WIP_UNEXPECTED_ERROR');
658 fnd_message.set_token('ERROR_TEXT', 'WIP_JSI_Processor.update_parent_table calling => ' ||
659 'wip_picking_pub.Update_Job_BackOrdQty: ' || SQLERRM);
660 wip_jsi_utils.record_current_error;
661 end if;
662 end if;
663
664
665 if(x_load_type = WIP_CONSTANTS.RESCHED_JOB AND x_status_type in
666 (WIP_CONSTANTS.COMP_NOCHRG, WIP_CONSTANTS.HOLD, wip_constants.cancelled)) then
667 wip_picking_pvt.cancel_allocations(p_wip_entity_id => x_entity_id,
668 p_wip_entity_type => wip_constants.discrete,
669 x_return_status => l_status,
670 x_msg_data => l_msg_data);
671 if(l_status <> FND_API.G_RET_STS_SUCCESS) then
672 fnd_message.set_name('WIP', 'WIP_UNEXPECTED_ERROR');
673 fnd_message.set_token('ERROR_TEXT', 'WIP_JSI_Processor.update_parent_table calling => ' ||
674 'wip_picking_pub.Update_Job_BackOrdQty: ' || SQLERRM);
675 wip_jsi_utils.record_current_error;
676 end if;
677 end if;
678
679 elsif (x_load_type = WIP_CONSTANTS.CREATE_SCHED) then
680
681 INSERT INTO WIP_REPETITIVE_SCHEDULES(
682 repetitive_schedule_id,
683 organization_id,
684 last_update_date,
685 last_updated_by,
686 creation_date,
687 created_by,
688 last_update_login,
689 request_id,
690 program_application_id,
691 program_id,
692 program_update_date,
693 wip_entity_id,
694 line_id,
695 daily_production_rate,
696 processing_work_days,
697 status_type,
698 firm_planned_flag,
699 alternate_bom_designator,
700 common_bom_sequence_id,
701 bom_revision,
702 bom_revision_date,
703 alternate_routing_designator,
704 common_routing_sequence_id,
705 routing_revision,
706 routing_revision_date,
707 first_unit_start_date,
708 first_unit_completion_date,
709 last_unit_start_date,
710 last_unit_completion_date,
711 quantity_completed,
712 description,
713 demand_class,
714 material_account,
715 material_overhead_account,
716 material_variance_account,
717 outside_processing_account,
718 outside_proc_variance_account,
719 overhead_account,
720 overhead_variance_account,
721 resource_account,
722 resource_variance_account,
723 po_creation_time,
724 attribute_category,
725 attribute1,
726 attribute2,
727 attribute3,
728 attribute4,
729 attribute5,
730 attribute6,
731 attribute7,
732 attribute8,
733 attribute9,
734 attribute10,
735 attribute11,
736 attribute12,
737 attribute13,
738 attribute14,
739 attribute15)
740 SELECT
741 wi.repetitive_schedule_id,
742 wi.organization_id,
743 SYSDATE,
744 wi.last_updated_by,
745 SYSDATE,
746 wi.created_by,
747 wi.last_update_login,
748 wi.request_id,
749 wi.program_application_id,
750 wi.program_id,
751 SYSDATE,
752 ri.wip_entity_id,
753 wi.line_id,
754 wi.daily_production_rate,
755 wi.processing_work_days,
756 WIP_CONSTANTS.PEND_REPML,
757 wi.firm_planned_flag,
758 ri.alternate_bom_designator,
759 bom.common_bill_sequence_id,
760 wi.bom_revision,
761 TO_DATE(TO_CHAR(wi.bom_revision_date,
762 WIP_CONSTANTS.DT_NOSEC_FMT),
763 WIP_CONSTANTS.DT_NOSEC_FMT),
764 ri.alternate_routing_designator,
765 bor.common_routing_sequence_id,
766 wi.routing_revision,
767 TO_DATE(TO_CHAR(wi.routing_revision_date,
768 WIP_CONSTANTS.DT_NOSEC_FMT),
769 WIP_CONSTANTS.DT_NOSEC_FMT),
770 TO_DATE(TO_CHAR(wi.first_unit_start_date,
771 WIP_CONSTANTS.DT_NOSEC_FMT),
772 WIP_CONSTANTS.DT_NOSEC_FMT),
773 TO_DATE(TO_CHAR(wi.first_unit_completion_date,
774 WIP_CONSTANTS.DT_NOSEC_FMT),
775 WIP_CONSTANTS.DT_NOSEC_FMT),
776 TO_DATE(TO_CHAR(wi.last_unit_start_date,
777 WIP_CONSTANTS.DT_NOSEC_FMT),
778 WIP_CONSTANTS.DT_NOSEC_FMT),
779 TO_DATE(TO_CHAR(wi.last_unit_completion_date,
780 WIP_CONSTANTS.DT_NOSEC_FMT),
781 WIP_CONSTANTS.DT_NOSEC_FMT),
782 0, /* quantity completed */
783 wi.description,
784 wi.demand_class,
785 ac.material_account,
786 ac.material_overhead_account,
787 ac.material_variance_account,
788 ac.outside_processing_account,
789 ac.outside_proc_variance_account,
790 ac.overhead_account,
791 ac.overhead_variance_account,
792 ac.resource_account,
793 ac.resource_variance_account,
794 params.po_creation_time,
795 wi.attribute_category,
796 wi.attribute1,
797 wi.attribute2,
798 wi.attribute3,
799 wi.attribute4,
800 wi.attribute5,
801 wi.attribute6,
802 wi.attribute7,
803 wi.attribute8,
804 wi.attribute9,
805 wi.attribute10,
806 wi.attribute11,
807 wi.attribute12,
808 wi.attribute13,
809 wi.attribute14,
810 wi.attribute15
811 FROM
812 bom_operational_routings bor,
813 bom_bill_of_materials bom,
814 wip_repetitive_items ri,
815 wip_accounting_classes ac,
816 wip_parameters params,
817 wip_job_schedule_interface wi
818 WHERE wi.rowid = WIP_JSI_Utils.current_rowid
819 AND nvl(bor.cfm_routing_flag,2) = 2
820 AND ri.organization_id = wi.organization_id
821 AND ri.primary_item_id = wi.primary_item_id
822 AND ri.line_id = wi.line_id
823 AND bor.assembly_item_id (+) = ri.primary_item_id
824 AND bor.organization_id (+) = ri.organization_id
825 AND NVL(bor.alternate_routing_designator (+),'NON_EXISTENT') =
826 NVL(ri.alternate_routing_designator,'NON_EXISTENT')
827 AND bom.assembly_item_id (+) = ri.primary_item_id
828 AND bom.organization_id (+) = ri.organization_id
829 AND NVL(bom.alternate_bom_designator (+),'NON_EXISTENT') =
830 NVL(ri.alternate_bom_designator,'NON_EXISTENT')
831 AND ac.class_code = ri.class_code
832 AND ac.organization_id = wi.organization_id
833 AND params.organization_id = wi.organization_id ;
834
835 end if ;
836 /*
837 Fix for Bug#2034660
838
839 if (x_load_type in (WIP_CONSTANTS.CREATE_JOB,
840 WIP_CONSTANTS.CREATE_SCHED
841 ) and
842 x_status_type = WIP_CONSTANTS.released)
843 then
844 WIP_OSP.release_validation(x_entity_id, x_org_id, x_rep_sched_id) ;
845 end if;
846
847 */
848 END update_parent_tables ;
849
850
851
852 PROCEDURE Run_Report(P_Group_Id IN NUMBER) IS
853 req_id NUMBER;
854 wait BOOLEAN;
855 phase VARCHAR2(2000);
856 status VARCHAR2(2000);
857 devphase VARCHAR2(2000);
858 devstatus VARCHAR2(2000);
859 message VARCHAR2(2000);
860 BEGIN
861
862 req_id := FND_REQUEST.SUBMIT_REQUEST
863 ('WIP','WIPMLINT',NULL,NULL,FALSE,
864 to_char(P_Group_Id), NULL, to_char(WIP_CONSTANTS.NO),
865 chr(0), NULL, NULL, NULL, NULL, NULL, NULL,
866 NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
867 NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
868 NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
869 NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
870 NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
871 NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
872 NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
873 NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
874 NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL);
875
876 commit;
877
878 if req_id = 0 then
879 NULL;
880 else
881
882 -- We wait 100 minutes for the report.
883
884 wait := FND_CONCURRENT.WAIT_FOR_REQUEST
885 (req_id, 10, 36000, phase, status, devphase,
886 devstatus, message);
887
888 end if;
889
890 END Run_Report;
891
892 PROCEDURE Delete_Completed_Records(P_Group_Id IN NUMBER) IS
893 BEGIN
894
895 DELETE FROM WIP_INTERFACE_ERRORS
896 WHERE INTERFACE_ID IN
897 (SELECT INTERFACE_ID
898 FROM WIP_JOB_SCHEDULE_INTERFACE
899 WHERE PROCESS_PHASE = WIP_CONSTANTS.ML_COMPLETE
900 AND GROUP_ID = P_Group_Id
901 AND PROCESS_STATUS = WIP_CONSTANTS.ML_COMPLETE);
902
903 DELETE FROM WIP_JOB_SCHEDULE_INTERFACE
904 WHERE GROUP_ID = P_Group_Id
905 AND PROCESS_PHASE = WIP_CONSTANTS.ML_COMPLETE
906 AND PROCESS_STATUS = WIP_CONSTANTS.ML_COMPLETE;
907
908 END Delete_Completed_Records;
909
910 PROCEDURE ML_Release(P_Wip_Entity_Id IN NUMBER,
911 P_Organization_Id IN NUMBER,
912 P_Class_Code IN VARCHAR2,
913 P_New_Status_Type IN NUMBER,
914 P_Success_Flag OUT NOCOPY NUMBER,
915 P_Error_Msg OUT NOCOPY VARCHAR2) IS
916 x_dummy NUMBER;
917 BEGIN
918 WIP_CHANGE_STATUS.Release
919 (P_Wip_Entity_Id,
920 P_Organization_Id,
921 NULL, NULL,
922 P_Class_Code,
923 WIP_CONSTANTS.UNRELEASED,
924 P_New_Status_Type,
925 x_dummy);
926
927 P_Success_Flag := 1;
928 EXCEPTION
929 WHEN OTHERS THEN
930 P_Success_Flag := 0;
931 P_Error_Msg := SUBSTR(FND_MESSAGE.get,1,500);
932 END ML_Release;
933
934 PROCEDURE ML_Status_Change(P_Wip_Entity_Id IN NUMBER,
935 P_Organization_Id IN NUMBER,
936 P_Class_Code IN VARCHAR2,
937 P_New_Status_Type IN NUMBER,
938 P_Old_Status_Type IN NUMBER,
939 P_Success_Flag OUT NOCOPY NUMBER,
940 P_Error_Msg OUT NOCOPY VARCHAR2) IS
941 BEGIN
942 IF P_Old_Status_Type = 1 THEN
943 ML_Release(P_Wip_Entity_Id,
944 P_Organization_Id,
945 P_Class_Code,
946 P_New_Status_Type,
947 P_Success_Flag,
948 P_Error_Msg);
949 ELSE
950 WIP_UNRELEASE.Unrelease(P_Organization_Id,
951 P_Wip_Entity_Id,
952 NULL,
953 NULL,
954 1);
955
956 END IF;
957
958 P_Success_Flag := 1;
959
960 EXCEPTION
961 WHEN OTHERS THEN
962 P_Success_Flag := 0;
963 P_Error_Msg := SUBSTR(FND_MESSAGE.get,1,500);
964 END;
965
966
967 END WIP_JSI_Processor ;