[Home] [Help]
PACKAGE BODY: APPS.MSC_REL_PS_PLAN_PUB
Source
1 PACKAGE BODY MSC_REL_PS_PLAN_PUB AS
2 /* $Header: MSCPSRELB.pls 120.32.12020000.4 2013/02/26 06:47:33 akpradh ship $ */
3
4 -- Start of Comments
5 -- API name MSC_Release_Plan_SC
6 -- Type Public
7 -- Procedure
8 --
9 -- Pre-reqs
10 --
11 -- Parameters
12 --
13 -- Version Current version = 1.0
14 -- Initial version = 1.0
15 --
16 -- Notes
17 --
18 -- OVERVIEW:
19 -- This procedure populates the WIP and purchasing interface tables with
20 -- rows for creating and rescheduling jobs, purchase orders, and repetitive
21 -- schedules
22 --
23 -- ARGUMENTS:
24 -- arg_plan_id: The plan identifier
25 -- arg_org_id: The current organization id
26 -- arg_sr_instance The source instance id of the org
27 -- arg_compile_desig: The current plan name
28 -- arg_user_id: The user
29 -- arg_po_group_by: How to group attributes together for po mass load
30 -- arg_wip_group_id: How to group records in wip
31 -- lv_launch_process: Which process to launch
32 -- lv_calendar_code: Calendar code for current organization
33 -- lv_exception_set_id: Exception set id for current organization
34 --
35 -- RETURNS: Nothing
36 --
37
38
39 G_INS_DISCRETE CONSTANT NUMBER := 1;
40 G_INS_PROCESS CONSTANT NUMBER := 2;
41 G_INS_OTHER CONSTANT NUMBER := 3;
42 G_INS_MIXED CONSTANT NUMBER := 4;
43 G_INS_EXCHANGE CONSTANT NUMBER := 5;
44
45 v_curr_instance_type NUMBER;
46 v_user_id NUMBER;
47 lv_resched_cmro_jobs NUMBER;
48 lv_resched_cmro_alt_jobs NUMBER;
49 lv_resched_cmro_req_id NUMBER;
50 JOB_CANCELLED CONSTANT INTEGER := 7;
51
52 NULL_DBLINK CONSTANT VARCHAR2(1):= ' ';
53
54 LT_RESOURCE_INSTANCE CONSTANT NUMBER := 8; -- dsr
55 -- SUBST_CHANGE CONSTANT NUMBER := 3; -- dsr
56 LT_RESOURCE_INST_USAGE CONSTANT NUMBER := 9; -- dsr
57 LT_CHARGE_TYPE CONSTANT NUMBER := 10; -- dsr
58
59
60
61 /*This prcodure is called from PS integration java code, in Publish, Release Stage */
62
63 PROCEDURE MSC_PS_RELEASE( p_plan_id IN NUMBER, p_organization_id IN NUMBER, p_instance_id IN NUMBER,
64 p_plan_name IN VARCHAR2, p_user_id IN VARCHAR2, p_loaded_jobs IN OUT NOCOPY NUMBER,
65 p_resched_jobs IN OUT NOCOPY NUMBER, p_req_id IN OUT NOCOPY NUMBER ) IS
66
67 user_id VARCHAR2(100);
68
69
70
71 loaded_jobs NumTblTyp := NumTblTyp(0);
72 loaded_reqs NumTblTyp := NumTblTyp(0);
73 loaded_scheds NumTblTyp := NumTblTyp(0);
74 resched_jobs NumTblTyp := NumTblTyp(0);
75 resched_reqs NumTblTyp := NumTblTyp(0);
76 wip_group_id NUMBER;
77 wip_req_id NumTblTyp := NumTblTyp(0);
78 po_req_load_id NumTblTyp := NumTblTyp(0);
79 po_req_resched_id NumTblTyp := NumTblTyp(0);
80 release_instance NumTblTyp := NumTblTyp(0);
81 loaded_lot_jobs NumTblTyp := NumTblTyp(0);
82 resched_lot_jobs NumTblTyp := NumTblTyp(0);
83 osfm_req_id NumTblTyp := NumTblTyp(0);
84 loaded_repair_orders NumTblTyp := NumTblTyp(0);-- bug 6038957
85 repair_orders_id NumTblTyp := NumTblTyp(0);-- bug 6038957
86
87
88 BEGIN
89
90
91
92 user_id:=FND_PROFILE.VALUE('USER_ID');
93 /*dbms_output.put_line('USER_ID '|| p_user_id);*/
94
95
96
97 MSC_RELEASE_PLAN_SC( p_plan_id,
98 p_organization_id,
99 p_instance_id,
100 p_organization_id,
101 p_instance_id,
102 p_plan_name,
103 user_id,
104 null,
105 null,
106 null,
107 loaded_jobs,
108 loaded_reqs,
109 loaded_scheds,
110 resched_jobs,
111 resched_reqs,
112 wip_req_id,
113 po_req_load_id,
114 po_req_resched_id,
115 release_instance,
116 'PS',
117 null,
118 loaded_lot_jobs,
119 resched_lot_jobs,
120 osfm_req_id,
121 loaded_repair_orders,
122 repair_orders_id);
123
124 FOR i in 1..loaded_jobs.COUNT LOOP
125 p_loaded_jobs:=loaded_jobs(i) + loaded_lot_jobs(i);
126 p_resched_jobs:=resched_jobs(i) + resched_lot_jobs(i);
127 if( wip_req_id(i) > 0 ) then
128 p_req_id:=wip_req_id(i);
129 else
130 p_req_id:=osfm_req_id(i);
131 end if;
132 /*dbms_output.put_line('Released planned orders number '|| p_loaded_jobs);
133 dbms_output.put_line('request_id='||p_req_id);*/
134 END LOOP;
135
136 END;
137
138
139
140 PROCEDURE MSC_RELEASE_PLAN_SC
141 ( arg_plan_id IN NUMBER
142 , arg_log_org_id IN NUMBER
143 , arg_log_sr_instance IN NUMBER
144 , arg_org_id IN NUMBER
145 , arg_sr_instance IN NUMBER
146 , arg_compile_desig IN VARCHAR2
147 , arg_user_id IN NUMBER
148 , arg_po_group_by IN NUMBER
149 , arg_po_batch_number IN NUMBER
150 , arg_wip_group_id IN NUMBER
151 , arg_loaded_jobs IN OUT NOCOPY NumTblTyp
152 , arg_loaded_reqs IN OUT NOCOPY NumTblTyp
153 , arg_loaded_scheds IN OUT NOCOPY NumTblTyp
154 , arg_resched_jobs IN OUT NOCOPY NumTblTyp
155 , arg_resched_reqs IN OUT NOCOPY NumTblTyp
156 , arg_wip_req_id IN OUT NOCOPY NumTblTyp
157 , arg_req_load_id IN OUT NOCOPY NumTblTyp
158 , arg_req_resched_id IN OUT NOCOPY NumTblTyp
159 , arg_released_instance IN OUT NOCOPY NumTblTyp
160 , arg_mode IN VARCHAR2
161 , arg_transaction_id IN NUMBER
162 , arg_loaded_lot_jobs IN OUT NOCOPY NumTblTyp
163 , arg_resched_lot_jobs IN OUT NOCOPY NumTblTyp
164 , arg_osfm_req_id IN OUT NOCOPY NumTblTyp
165 , arg_loaded_int_repair_orders IN OUT NOCOPY NumTblTyp -- Sasi
166 , arg_int_repair_orders_id IN OUT NOCOPY NumTblTyp -- Sasi
167 ) IS
168
169 CURSOR c_Instance IS
170 SELECT apps.instance_id,
171 apps.instance_code,
172 apps.apps_ver,
173 apps.instance_type,
174 DECODE(apps.m2a_dblink,NULL,' ', '@' || m2a_dblink),
175 DECODE(apps.a2m_dblink,NULL,NULL_DBLINK,a2m_dblink),
176 LENGTH( apps.instance_code)+2
177 FROM msc_apps_instances apps,
178 ( SELECT distinct
179 sr_instance_id
180 FROM msc_plan_organizations_v plan_org
181 WHERE plan_org.plan_id = arg_plan_id
182 AND plan_org.organization_id = arg_org_id
183 AND plan_org.owning_sr_instance = arg_sr_instance
184 AND plan_org.sr_instance_id =
185 decode(arg_log_sr_instance,
186 arg_sr_instance, plan_org.sr_instance_id,
187 arg_log_sr_instance)) ins
188 WHERE apps.instance_id = ins.sr_instance_id;
189
190 l_sr_instance_id NUMBER;
191 l_instance_code VARCHAR2(3);
192 l_apps_ver VARCHAR2(10);
193 l_dblink VARCHAR2(128);
194 l_a2m_dblink VARCHAR2(128);
195
196 l_user_name VARCHAR2(100):= NULL;
197 l_resp_name VARCHAR2(100):= NULL;
198 l_application_name VARCHAR2(240):= NULL;
199
200 l_user_id NUMBER;
201 l_resp_id NUMBER;
202 l_application_id NUMBER;
203
204 l_sql_stmt VARCHAR2(4000);
205
206 l_loaded_jobs NUMBER;
207 l_loaded_reqs NUMBER;
208 l_loaded_scheds NUMBER;
209 l_resched_jobs NUMBER;
210
211 l_resched_reqs NUMBER;
212 l_wip_req_id NUMBER;
213 l_req_load_id NUMBER;
214 l_req_resched_id NUMBER;
215
216 lv_count NUMBER:= 0;
217
218
219 l_loaded_lot_jobs NUMBER;
220 l_resched_lot_jobs NUMBER;
221 l_osfm_req_id NUMBER;
222
223 lv_error_buf VARCHAR2(2000);
224 lv_ret_code NUMBER;
225
226 l_wip_group_id NUMBER;
227 l_po_batch_number NUMBER;
228
229 l_loaded_int_repair_orders number ;-- bug 6038957
230 l_int_repair_orders_id number ;-- bug 6038957
231
232
233 BEGIN
234
235
236 SELECT
237 FND_GLOBAL.USER_ID,
238 FND_GLOBAL.USER_NAME,
239 FND_GLOBAL.RESP_NAME,
240 FND_GLOBAL.APPLICATION_NAME,
241 FND_GLOBAL.RESP_APPL_ID
242 INTO v_user_id,
243 l_user_name,
244 l_resp_name,
245 l_application_name,
246 l_application_id
247 FROM dual;
248
249 -------- Release the planned order one instance by one instance.
250
251 OPEN c_Instance;
252 LOOP
253 FETCH c_Instance
254 INTO l_sr_instance_id,
255 l_instance_code,
256 l_apps_ver,
257 v_curr_instance_type,
258 l_dblink,
259 l_a2m_dblink,
260 v_instance_code_length;
261
262 EXIT WHEN c_Instance%NOTFOUND;
263
264 ---------- Initialize the remote process
265 ---------- If the instance is discrete or mixed type
266 ---------- AND the instance is at a remote database
267
268 arg_loaded_jobs.extend(1);
269 arg_loaded_reqs.extend(1);
270 arg_loaded_scheds.extend(1);
271 arg_resched_jobs.extend(1);
272 arg_resched_reqs.extend(1);
273 arg_wip_req_id.extend(1);
274 arg_req_load_id.extend(1);
275 arg_req_resched_id.extend(1);
276 arg_released_instance.extend(1);
277 arg_loaded_lot_jobs.extend(1);
278 arg_resched_lot_jobs.extend(1);
279 arg_osfm_req_id.extend(1);
280
281 arg_loaded_int_repair_orders.extend(1); -- Sasi
282 arg_int_repair_orders_id.extend(1); -- Sasi
283
284 lv_count:= lv_count+1;
285
286 arg_released_instance(lv_count):= l_sr_instance_id;
287
288 -- initialize the Applications Environment --
289 IF v_curr_instance_type IN ( G_INS_DISCRETE, G_INS_PROCESS, G_INS_MIXED) THEN
290
291 l_sql_stmt:=
292 'BEGIN'
293 ||' MRP_AP_REL_PLAN_PUB.INITIALIZE'||l_dblink
294 ||'( :l_user_name,'
295 ||' :l_resp_name,'
296 ||' :l_application_name,'
297 ||' :l_sr_instance_id,'
298 ||' :l_instance_code,'
299 ||' :l_a2m_dblink,'
300 ||' :l_wip_group_id,'
301 ||' :l_po_batch_number,'
302 ||' :l_application_id);'
303 ||'END;';
304
305 EXECUTE IMMEDIATE l_sql_stmt
306 USING IN l_user_name,
307 IN l_resp_name,
308 IN l_application_name,
309 IN l_sr_instance_id,
310 IN l_instance_code,
311 IN l_a2m_dblink,
312 OUT l_wip_group_id,
313 OUT l_po_batch_number,
314 IN l_application_id;
315
316 END IF;
317
318 l_loaded_jobs := 0;
319 l_loaded_reqs := 0;
320 l_loaded_scheds := 0;
321 l_resched_jobs := 0;
322 l_loaded_lot_jobs := 0;
323 l_resched_lot_jobs := 0;
324
325
326 -- load the msc interface tables, submit the request --
327 LOAD_MSC_INTERFACE
328 (arg_dblink => l_dblink,
329 arg_plan_id => arg_plan_id,
330 arg_log_org_id => arg_log_org_id,
331 arg_org_instance => l_sr_instance_id,
332 arg_owning_org_id => arg_org_id,
333 arg_owning_instance => arg_sr_instance,
334 arg_compile_desig => arg_compile_desig,
335 arg_user_id => arg_user_id,
336 arg_po_group_by => arg_po_group_by,
337 arg_po_batch_number => l_po_batch_number,
338 arg_wip_group_id => l_wip_group_id,
339 ----------------------------------------------- Number of Loaded Orders
340 arg_loaded_jobs => l_loaded_jobs,
341 arg_loaded_lot_jobs => l_loaded_lot_jobs,
342 arg_resched_lot_jobs => l_resched_lot_jobs,
343 arg_loaded_reqs => l_loaded_reqs,
344 arg_loaded_scheds => l_loaded_scheds,
345 arg_resched_jobs => l_resched_jobs,
346 ----------------------------------------------- Request IDs
347 arg_resched_reqs => l_resched_reqs,
348 arg_wip_req_id => l_wip_req_id,
349 arg_osfm_req_id => l_osfm_req_id,
350 arg_req_load_id => l_req_load_id,
351 arg_req_resched_id => l_req_resched_id,
352 -------------------------------------------------------------
353 arg_mode => arg_mode,
354 arg_transaction_id => arg_transaction_id,
355 l_apps_ver => l_apps_ver,
356 arg_loaded_int_repair_orders=> l_loaded_int_repair_orders, -- Sasi
357 arg_int_repair_orders_id=> l_int_repair_orders_id -- Sasi
358 );
359
360
361
362 COMMIT WORK;
363
364 arg_loaded_jobs(lv_count) := l_loaded_jobs;
365 arg_loaded_reqs(lv_count) := l_loaded_reqs;
366 arg_loaded_scheds(lv_count) := l_loaded_scheds;
367 arg_resched_jobs(lv_count) := l_resched_jobs;
368
369 arg_loaded_lot_jobs(lv_count) := l_loaded_lot_jobs;
370 arg_resched_lot_jobs(lv_count) := l_resched_lot_jobs;
371
372 arg_resched_reqs(lv_count) := l_resched_reqs;
373 arg_wip_req_id(lv_count) := l_wip_req_id;
374
375 arg_osfm_req_id(lv_count) := l_osfm_req_id;
376 arg_req_load_id(lv_count) := l_req_load_id;
377 arg_req_resched_id(lv_count):= l_req_resched_id;
378
379 --IRO release
380 arg_loaded_int_repair_orders(lv_count):= l_loaded_int_repair_orders; -- Sasi
381 arg_int_repair_orders_id(lv_count) := l_int_repair_orders_id; -- Sasi
382
383 END LOOP;
384
385 CLOSE c_Instance;
386
387 arg_loaded_jobs.trim(1);
388 arg_loaded_lot_jobs.trim(1);
389 arg_loaded_reqs.trim(1);
390 arg_loaded_scheds.trim(1);
391 arg_resched_jobs.trim(1);
392 arg_resched_lot_jobs.trim(1);
393 arg_resched_reqs.trim(1);
394 arg_wip_req_id.trim(1);
395 arg_osfm_req_id.trim(1);
396 arg_req_load_id.trim(1);
397 arg_req_resched_id.trim(1);
398 arg_released_instance.trim(1);
399 arg_loaded_int_repair_orders.trim(1);-- Sasi
400 arg_int_repair_orders_id.trim(1); -- Sasi
401
402 EXCEPTION
403
404 WHEN OTHERS THEN
405 IF c_Instance%ISOPEN THEN CLOSE c_Instance; END IF;
406
407 RAISE;
408
409 END MSC_Release_Plan_Sc;
410
411
412 PROCEDURE LOAD_MSC_INTERFACE
413 ( arg_dblink IN VARCHAR2
414 , arg_plan_id IN NUMBER
415 , arg_log_org_id IN NUMBER
416 , arg_org_instance IN NUMBER
417 , arg_owning_org_id IN NUMBER
418 , arg_owning_instance IN NUMBER
419 , arg_compile_desig IN VARCHAR2
420 , arg_user_id IN NUMBER
421 , arg_po_group_by IN NUMBER
422 , arg_po_batch_number IN NUMBER
423 , arg_wip_group_id IN NUMBER
424 , arg_loaded_jobs IN OUT NOCOPY NUMBER
425 , arg_loaded_lot_jobs IN OUT NOCOPY NUMBER
426 , arg_resched_lot_jobs IN OUT NOCOPY NUMBER
427 , arg_loaded_reqs IN OUT NOCOPY NUMBER
428 , arg_loaded_scheds IN OUT NOCOPY NUMBER
429 , arg_resched_jobs IN OUT NOCOPY NUMBER
430 , arg_resched_reqs IN OUT NOCOPY NUMBER
431 , arg_wip_req_id IN OUT NOCOPY NUMBER
432 , arg_osfm_req_id IN OUT NOCOPY NUMBER
433 , arg_req_load_id IN OUT NOCOPY NUMBER
434 , arg_req_resched_id IN OUT NOCOPY NUMBER
435 , arg_mode IN VARCHAR2
436 , arg_transaction_id IN NUMBER
437 , l_apps_ver IN VARCHAR2
438 , arg_loaded_int_repair_orders IN OUT NOCOPY Number -- Sasi
439 , arg_int_repair_orders_id IN OUT NOCOPY Number -- Sasi
440 ) IS
441
442 VERSION CONSTANT CHAR(800) :=
443 '$Header: MSCPSRELB.pls 120.32.12020000.4 2013/02/26 06:47:33 akpradh ship $';
444
445 lv_launch_process INTEGER;
446 lv_handle VARCHAR2(200);
447 lv_output NUMBER;
448 lv_error_stmt VARCHAR2(2000) := NULL;
449
450 lv_sql_stmt VARCHAR2(4000);
451
452 lv_wf NUMBER;
453 lv_wf_load_type NUMBER;
454
455 lv_error_buf VARCHAR2(2000);
456 lv_ret_code NUMBER;
457 BEGIN
458
459
460
461 -- if mode is NULL then it means that this procedure is called from PWB
462 -- where we need to do batch processing
463 -- If mode is WF, then we need to do this work only for the
464 -- transaction_id that is passed in
465 -- If the mode is WF_BATCH, this will only process the planned orders
466 -- which need to be released, it won't process the supplies which needs
467 -- to be rescheduled
468
469 dbms_lock.allocate_unique(arg_compile_desig||
470 to_char(arg_owning_org_id),lv_handle);
471
472 lv_output := dbms_lock.request(lv_handle, 6, 32767, TRUE);
473
474 if(lv_output <> 0) then
475 FND_MESSAGE.SET_NAME('MRP', 'GEN-LOCK-WARNING');
476 FND_MESSAGE.SET_TOKEN('EVENT', 'RELEASE PLANNED ORDERS');
477 lv_error_stmt := FND_MESSAGE.GET;
478 raise_application_error(-20000, lv_error_stmt);
479 end if;
480
481 -- Get the hour uom code
482 -- Get the profile MRP_PURCHASING_BY_REVISION is set
483
484 lv_sql_stmt:=
485 'BEGIN'
486 ||' :v_hour_uom := FND_PROFILE.VALUE'||arg_dblink
487 ||'(''BOM:HOUR_UOM_CODE'');'
488 ||' :v_purchasing_by_rev := FND_PROFILE.VALUE'||arg_dblink
489 ||'(''MRP_PURCHASING_BY_REVISION'');'
490 ||'END;';
491
492 EXECUTE IMMEDIATE lv_sql_stmt
493 USING OUT v_hour_uom,
494 OUT v_purchasing_by_rev;
495
496 lv_wf:= SYS_NO;
497 -- Get the Load Type if it's 'WF' mode.
498 IF arg_mode = 'WF' THEN
499 lv_wf:= SYS_YES;
500 BEGIN
501 SELECT load_type
502 INTO lv_wf_load_type
503 FROM MSC_SUPPLIES s
504 WHERE s.plan_id = arg_plan_id
505 AND s.transaction_id = arg_transaction_id
506 and s.release_status = 1;
507 EXCEPTION
508 WHEN NO_DATA_FOUND THEN NULL;
509 WHEN OTHERS THEN RAISE;
510 END;
511 END IF;
512
513 IF arg_mode = 'WF_BATCH' then
514 lv_wf := 3;
515 END IF;
516
517
518
519 --- WIP_DIS_MASS_LOAD ---
520
521 arg_loaded_jobs:= load_wip_discr_jobs_ps
522 ( arg_plan_id,
523 arg_log_org_id,
524 arg_org_instance,
525 arg_owning_org_id,
526 arg_owning_instance,
527 arg_user_id,
528 arg_wip_group_id,
529 l_apps_ver );
530
531
532 arg_loaded_lot_jobs:= load_osfm_lot_jobs_ps
533 ( arg_plan_id,
534 arg_log_org_id,
535 arg_org_instance,
536 arg_owning_org_id,
537 arg_owning_instance,
538 arg_user_id,
539 arg_wip_group_id,
540 arg_transaction_id,
541 l_apps_ver );
542
543
544
545 --- WIP_DIS_MASS_RESCHEDULE ---
546 arg_resched_jobs:= reschedule_wip_discr_jobs_ps
547 ( arg_plan_id,
548 arg_log_org_id,
549 arg_org_instance,
550 arg_owning_org_id,
551 arg_owning_instance,
552 arg_user_id,
553 arg_wip_group_id,
554 arg_transaction_id,
555 l_apps_ver );
556
557
558 arg_resched_lot_jobs := reschedule_osfm_lot_jobs_ps
559 ( arg_plan_id,
560 arg_log_org_id,
561 arg_org_instance,
562 arg_owning_org_id,
563 arg_owning_instance,
564 arg_user_id,
565 arg_wip_group_id,
566 arg_transaction_id );
567
568
569
570 /* MSC_RELEASE_HOOK.EXTEND_RELEASE
571 (lv_error_buf,
572 lv_ret_code,
573 arg_dblink => arg_dblink,
574 arg_plan_id => arg_plan_id,
575 arg_log_org_id => arg_log_org_id,
576 arg_org_instance => arg_org_instance,
577 arg_owning_org_id => arg_owning_org_id,
578 arg_owning_instance => arg_owning_instance,
579 arg_compile_desig => arg_compile_desig,
580 arg_user_id => arg_user_id,
581 arg_po_group_by => arg_po_group_by,
582 arg_po_batch_number => arg_po_batch_number,
583 arg_wip_group_id => arg_wip_group_id,
584 ----------------------------------------------- Number of Loaded Orders
585 arg_loaded_jobs => arg_loaded_jobs,
586 arg_loaded_lot_jobs => arg_loaded_lot_jobs,
587 arg_resched_lot_jobs => arg_loaded_lot_jobs,
588 arg_loaded_reqs => arg_loaded_reqs,
589 arg_loaded_scheds => arg_loaded_scheds,
590 arg_resched_jobs => arg_resched_jobs,
591 arg_int_repair_orders=>arg_loaded_int_repair_orders,
592 ----------------------------------------------- Request IDs
593 arg_resched_reqs => arg_resched_reqs,
594 arg_wip_req_id => arg_wip_req_id,
595 arg_osfm_req_id => arg_osfm_req_id,
596 arg_req_load_id => arg_req_load_id,
597 arg_req_resched_id => arg_req_resched_id,
598 arg_int_repair_Order_id=>arg_int_repair_orders_id,
599 -------------------------------------------------------------
600 arg_mode => arg_mode,
601 arg_transaction_id => arg_transaction_id,
602 l_apps_ver => l_apps_ver);
603
604
605
606 IF lv_ret_code=-1 THEN --custom hook returned error
607 FND_MESSAGE.SET_NAME('MSC','MSC_ERROR_REL_CUSTOM_HOOK');
608 RAISE_APPLICATION_ERROR(-20000,FND_MESSAGE.GET||lv_error_buf,TRUE);
609 END IF;
610 */
611
612
613
614 commit;
615
616 -- call (remote) procedures to submit the concuncurrent request --
617
618 IF v_curr_instance_type IN ( G_INS_DISCRETE, G_INS_PROCESS, G_INS_MIXED) THEN
619
620 IF arg_loaded_jobs+arg_resched_jobs+arg_loaded_scheds > 0 OR lv_resched_cmro_alt_jobs > 0 THEN
621
622 lv_sql_stmt:=
623 'BEGIN'
624 ||' MRP_AP_REL_PLAN_PUB.LD_WIP_JOB_SCHEDULE_INTERFACE'||arg_dblink
625 ||'( :arg_wip_req_id );'
626 ||' END;';
627
628 EXECUTE IMMEDIATE lv_sql_stmt
629 USING OUT arg_wip_req_id;
630
631 END IF;
632
633
634 IF arg_loaded_lot_jobs + nvl(arg_resched_lot_jobs,0) > 0 then
635
636 lv_sql_stmt:=
637 'BEGIN'
638 ||' MRP_AP_REL_PLAN_PUB.LD_LOT_JOB_SCHEDULE_INTERFACE'||arg_dblink
639 ||'( :arg_osfm_req_id );'
640 ||' END;';
641
642 EXECUTE IMMEDIATE lv_sql_stmt
643 USING OUT arg_osfm_req_id;
644
645
646 END IF;
647
648 -- Substitute component Release call for CMRO
649
650 /* IF lv_resched_cmro_jobs > 0 then
651
652 lv_sql_stmt:=
653 'BEGIN'
654 ||' MRP_AP_REL_PLAN_PUB.LD_CMRO_RESCHEDULE_INTERFACE'||arg_dblink
655 ||'(:lv_resched_cmro_req_id);'
656
657
658 ||' END;';
659
660 EXECUTE IMMEDIATE lv_sql_stmt
661 USING OUT lv_resched_cmro_req_id;
662 END IF;*/
663
664 /* DELETE msc_wip_job_schedule_interface
665 WHERE sr_instance_id= arg_org_instance;
666
667 DELETE MSC_WIP_JOB_DTLS_INTERFACE
668 WHERE sr_instance_id= arg_org_instance;
669 */
670 IF arg_loaded_reqs > 0 THEN
671 DECLARE po_group_by_name VARCHAR2(10);
672 BEGIN
673 IF arg_po_group_by = 1 THEN
674 po_group_by_name := 'ALL';
675 ELSIF arg_po_group_by = 2 THEN
676 po_group_by_name := 'ITEM';
677 ELSIF arg_po_group_by = 3 THEN
678 po_group_by_name := 'BUYER';
679 ELSIF arg_po_group_by = 4 THEN
680 po_group_by_name := 'PLANNER';
681 ELSIF arg_po_group_by = 5 THEN
682 po_group_by_name := 'VENDOR';
683 ELSIF arg_po_group_by = 6 THEN
684 po_group_by_name := 'ONE-EACH';
685 ELSIF arg_po_group_by = 7 THEN
686 po_group_by_name := 'CATEGORY';
687 ELSIF arg_po_group_by = 8 THEN
688 po_group_by_name := 'LOCATION';
689 END IF;
690
691 lv_sql_stmt:=
692 'BEGIN'
693 ||' MRP_AP_REL_PLAN_PUB.LD_PO_REQUISITIONS_INTERFACE'||arg_dblink
694 ||'( :po_group_by_name,'
695 ||' :arg_req_load_id );'
696 ||' END;';
697 EXECUTE IMMEDIATE lv_sql_stmt
698 USING IN po_group_by_name,
699 OUT arg_req_load_id;
700
701 /* DELETE MSC_PO_REQUISITIONS_INTERFACE
702 WHERE sr_instance_id= arg_org_instance;
703 */
704 END;
705 END IF;
706
707 IF arg_resched_reqs > 0 THEN
708
709 lv_sql_stmt:=
710 'BEGIN'
711 ||' MRP_AP_REL_PLAN_PUB.LD_PO_RESCHEDULE_INTERFACE'||arg_dblink
712 ||'( :arg_req_resched_id);'
713 ||' END;';
714
715 EXECUTE IMMEDIATE lv_sql_stmt
716 USING OUT arg_req_resched_id;
717
718 /* DELETE MSC_PO_RESCHEDULE_INTERFACE
719 WHERE sr_instance_id= arg_org_instance;
720 */
721 END IF;
722
723 ELSIF v_curr_instance_type in (G_INS_OTHER, G_INS_EXCHANGE) THEN
724
725 lv_sql_stmt :=
726 ' BEGIN'
727 ||' MSC_A2A_XML_WF.LEGACY_RELEASE (:p_arg_org_instance);'
728 ||' END;';
729
730 EXECUTE IMMEDIATE lv_sql_stmt USING arg_org_instance;
731
732 END IF; -- v_curr_instance_type
733
734 --- Update the released orders.
735
736 IF arg_loaded_jobs > 0 OR
737 arg_resched_jobs > 0 OR
738 arg_loaded_lot_jobs > 0 OR
739 arg_resched_lot_jobs > 0 OR
740 arg_loaded_scheds > 0 OR
741 arg_loaded_reqs > 0 OR
742 arg_resched_reqs > 0 THEN
743
744 UPDATE MSC_SUPPLIES
745 SET implement_demand_class = NULL,
746 implement_date = NULL,
747 implement_quantity = NULL,
748 implement_firm = NULL,
749 implement_wip_class_code = NULL,
750 -- implement_job_name = NULL, #Bug 10244329 - E1-VCP
751 implement_status_code = NULL,
752 implement_location_id = NULL,
753 implement_source_org_id = NULL,
754 implement_supplier_id = NULL,
755 implement_supplier_site_id = NULL,
756 implement_project_id = NULL,
757 implement_task_id = NULL,
758 release_status = NULL,
759 load_type = NULL,
760 implement_as = NULL,
761 implement_unit_number = NULL,
762 implement_schedule_group_id = NULL,
763 implement_build_sequence = NULL,
764 implement_line_id = NULL,
765 implement_alternate_bom = NULL,
766 implement_dock_date = NULL,
767 implement_ship_date = NULL,
768 implement_employee_id = NULL,
769 implement_alternate_routing = NULL,
770 implemented_quantity = nvl(implemented_quantity, 0) + nvl(quantity_in_process,0),
771 quantity_in_process = 0,
772 implement_ship_method = NULL
773 WHERE organization_id IN
774 (select planned_organization
775 from msc_plan_organizations_v
776 where organization_id = arg_owning_org_id
777 and owning_sr_instance = arg_owning_instance
778 and plan_id = arg_plan_id
779 AND planned_organization = decode(arg_log_org_id,
780 arg_owning_org_id, planned_organization,
781 arg_log_org_id)
782 AND sr_instance_id = arg_org_instance )
783 AND sr_instance_id= arg_org_instance
784 AND plan_id = arg_plan_id
785 AND release_status = 1
786 AND release_errors IS NULL
787 AND transaction_id in
788 (select header_id from msc_wip_job_schedule_interface
789 where sr_instance_id = arg_org_instance
790 UNION ALL
791 select source_line_id from msc_po_requisitions_interface
792 where sr_instance_id = arg_org_instance
793 UNION ALL
794 select source_line_id from msc_po_reschedule_interface
795 where sr_instance_id = arg_org_instance
796 )
797 AND load_type BETWEEN WIP_DIS_MASS_LOAD AND PO_MASS_RESCHEDULE;
798
799 /*
800 * Retain data in Interface tables for E1-VCP when
801 * the profile option MSC_RETAIN_RELEASED_DATA is
802 * set to YES
803 */
804
805 IF fnd_profile.value('MSC_RETAIN_RELEASED_DATA') ='N' THEN
806 DELETE msc_wip_job_schedule_interface
807 WHERE sr_instance_id= arg_org_instance
808 AND NVL(GROUP_ID,-1) = NVL(arg_wip_group_id, -1);
809
810 DELETE MSC_WIP_JOB_DTLS_INTERFACE
811 WHERE sr_instance_id= arg_org_instance
812 AND NVL(GROUP_ID,-1) = NVL(arg_wip_group_id, -1);
813
814 DELETE MSC_PO_REQUISITIONS_INTERFACE
815 WHERE sr_instance_id= arg_org_instance
816 AND NVL(BATCH_ID, -1) = NVL(arg_po_batch_number, -1);
817
818 DELETE MSC_PO_RESCHEDULE_INTERFACE
819 WHERE sr_instance_id= arg_org_instance;
820 END IF;
821
822
823
824 END IF;
825
826 EXCEPTION WHEN OTHERS THEN
827 rollback; /* Rollback whatever was released */
828
829 DELETE msc_wip_job_schedule_interface
830 WHERE sr_instance_id= arg_org_instance
831 AND NVL(GROUP_ID,-1) = NVL(arg_wip_group_id, -1);
832
833 DELETE MSC_WIP_JOB_DTLS_INTERFACE
834 WHERE sr_instance_id= arg_org_instance
835 AND NVL(GROUP_ID,-1) = NVL(arg_wip_group_id, -1);
836
837 DELETE MSC_PO_REQUISITIONS_INTERFACE
838 WHERE sr_instance_id= arg_org_instance
839 AND NVL(BATCH_ID, -1) = NVL(arg_po_batch_number, -1);
840
841
842 DELETE MSC_PO_RESCHEDULE_INTERFACE
843 WHERE sr_instance_id= arg_org_instance;
844 commit;
845 raise;
846
847 END LOAD_MSC_INTERFACE;
848
849
850 FUNCTION load_osfm_lot_jobs_ps
851 ( arg_plan_id IN NUMBER
852 , arg_log_org_id IN NUMBER
853 , arg_org_instance IN NUMBER
854 , arg_owning_org_id IN NUMBER
855 , arg_owning_instance IN NUMBER
856 , arg_user_id IN NUMBER
857 , arg_wip_group_id IN NUMBER
858 , arg_transaction_id IN NUMBER,
859 l_apps_ver IN VARCHAR2
860 )RETURN NUMBER
861 IS
862 lv_loaded_jobs NUMBER := 0;
863
864 TYPE NumTab IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
865
866
867 lv_transaction_id NumTab;
868 lv_instance_id NumTab;
869 lv_org_id NumTab;
870 lv_plan_id NumTab;
871 lv_agg_details NumTab;
872 lv_job_count NUMBER;
873 lv_release_details NUMBER;
874 lv_inflate_wip NUMBER;
875
876 BEGIN
877
878 SELECT decode(nvl(FND_PROFILE.value('MSC_RELEASE_DTLS_REVDATE'),'Y'),'N',2,1),
879 DECODE(NVL(fnd_profile.value('MSC_INFLATE_WIP') ,'N'), 'N',2 ,1)
880 INTO lv_release_details,lv_inflate_wip
881 FROM dual;
882
883
884 /* we release the lot based job details, only if it doesn't use aggregate resources */
885
886 /* Details will NOT be released for
887 b. if the new_wip_start_date is null
888 c. if the implement quantity or date is different then the planned quantity date
889 d. if the revision date is different then the new_wip_start_date
890 and the profile option setting : MSC_RELEASE_DTLS_REVDATE = 'N'
891
892 */
893
894 SELECT s.transaction_id,
895 s.sr_instance_id,
896 s.organization_id,
897 s.plan_id
898 BULK COLLECT
899 INTO lv_transaction_id,
900 lv_instance_id,
901 lv_org_id,
902 lv_plan_id
903 FROM msc_supplies s,
904 msc_plan_organizations_v orgs,
905 msc_system_items msi,
906 msc_plans mp
907 WHERE mp.plan_id = arg_plan_id
908 AND s.release_errors is NULL
909 AND nvl(s.cfm_routing_flag,0) = 3
910 AND s.implement_quantity > 0
911 AND s.organization_id = orgs.planned_organization
912 AND s.sr_instance_id = orgs.sr_instance_id
913 AND s.plan_id = arg_plan_id
914 AND orgs.plan_id = arg_plan_id
915 AND orgs.organization_id = arg_owning_org_id
916 AND orgs.owning_sr_instance = arg_owning_instance
917 AND ( orgs.planned_organization= arg_log_org_id
918 OR arg_log_org_id = arg_owning_org_id )
919 AND orgs.sr_instance_id = arg_org_instance
920 AND s.load_type = 5
921 AND s.new_wip_start_date IS NOT NULL
922 AND msi.plan_id = -1
923 AND msi.organization_id = s.organization_id
924 AND msi.sr_instance_id = s.sr_instance_id
925 AND s.release_status = 1
926 UNION
927 SELECT s.transaction_id,
928 s.sr_instance_id,
929
930 s.organization_id,
931 s.plan_id
932 FROM msc_supplies s,
933 msc_plan_organizations_v orgs
934 WHERE s.release_errors is NULL
935 AND nvl(s.cfm_routing_flag,0) = 3
936 AND s.implement_quantity > 0
937 AND s.organization_id = orgs.planned_organization
938 AND s.sr_instance_id = orgs.sr_instance_id
939 AND s.plan_id = arg_plan_id
940 AND orgs.plan_id = arg_plan_id
941 AND orgs.organization_id = arg_owning_org_id
942 AND orgs.owning_sr_instance = arg_owning_instance
943 AND ( orgs.planned_organization= arg_log_org_id
944 OR arg_log_org_id = arg_owning_org_id )
945 AND orgs.sr_instance_id = arg_org_instance
946 AND s.load_type = 5
947 and s.new_wip_start_date IS NULL
948 AND s.release_status = 1;
949
950
951 lv_job_count:= SQL%ROWCOUNT;
952
953
954
955 -- -----------------------------------------------------------------------
956 -- Perform the wip discrete job mass load
957 -- -----------------------------------------------------------------------
958 /* Due to we only give PLANNED components, BILL_RTG_EXPLOSION_FLAG
959 is set to 'Y'. */
960
961 FOR k in 1..lv_job_count
962 Loop
963 Begin
964 SELECT 2
965 Into lv_agg_details(k)
966 FROM msc_department_resources deptres,
967 msc_resource_requirements resreq
968 WHERE resreq.sr_instance_id= lv_instance_id(k)
969 AND resreq.supply_id = lv_transaction_id(k)
970 AND resreq.organization_id= lv_org_id(k)
971 AND resreq.plan_id = lv_plan_id(k)
972 AND resreq.parent_id = 2
973 AND deptres.plan_id = -1
974 AND deptres.sr_instance_id= resreq.sr_instance_id
975 AND deptres.resource_id= resreq.resource_id
976 AND deptres.department_id= resreq.department_id
977 AND deptres.organization_id= resreq.organization_id
978 AND deptres.aggregate_resource_flag= 1
979 AND rownum=1;
980 Exception
981 When no_data_found
982 then
983 lv_agg_details(k) := 1;
984 End;
985
986 End Loop;
987
988
989
990 FORALL j IN 1..lv_job_count
991 INSERT INTO msc_wip_job_schedule_interface
992 (last_update_date,
993 cfm_routing_flag,
994 last_updated_by,
995 last_update_login,
996 creation_date,
997 created_by,
998 group_id,
999 source_code,
1000 source_line_id,
1001 organization_id,
1002 organization_type,
1003 load_type,
1004 status_type,
1005 first_unit_start_date,
1006 last_unit_completion_date,
1007 bom_revision_date,
1008 routing_revision_date,
1009 primary_item_id,
1010 class_code,
1011 job_name,
1012 firm_planned_flag,
1013 start_quantity,
1014 net_quantity,
1015 demand_class,
1016 project_id,
1017 task_id,
1018 schedule_group_id,
1019 build_sequence,
1020 line_id,
1021 alternate_bom_designator,
1022 alternate_routing_designator,
1023 end_item_unit_number,
1024 process_phase,
1025 process_status,
1026 bom_reference_id,
1027 routing_reference_id,
1028 BILL_RTG_EXPLOSION_FLAG,
1029 HEADER_ID,
1030 uom_code, --Outbound Changes for XML
1031 SR_INSTANCE_ID,
1032 schedule_priority,
1033 requested_completion_date)
1034 SELECT SYSDATE,
1035 nvl(s.cfm_routing_flag,0),
1036 arg_user_id,
1037 s.last_update_login,
1038 SYSDATE,
1039 arg_user_id,
1040 arg_wip_group_id,
1041 'MSC',
1042 s.transaction_id,
1043 s.organization_id,
1044 tp.organization_type,
1045 5,
1046 s.implement_status_code,
1047 new_wip_start_date,
1048 s.implement_date + 59/86400,
1049 s.new_wip_start_date,
1050 s.new_wip_start_date,
1051 item_lid.sr_inventory_item_id,
1052 s.implement_wip_class_code,
1053 s.implement_job_name,
1054 s.firm_planned_type,
1055 decode(s.implement_quantity,s.new_order_quantity,
1056 nvl(s.wip_start_quantity,s.implement_quantity),
1057 s.implement_quantity),
1058 s.implement_quantity,
1059 s.implement_demand_class,
1060 s.implement_project_id,
1061 s.implement_task_id,
1062 s.implement_schedule_group_id,
1063 s.implement_build_sequence,
1064 s.implement_line_id,
1065 s.implement_alternate_bom,
1066 s.implement_alternate_routing,
1067 s.implement_unit_number,
1068 2,
1069 1,
1070 DECODE( tp.organization_type,
1071 2, s.bill_sequence_id,
1072 NULL),
1073 DECODE( tp.organization_type,
1074 2, s.routing_sequence_id,
1075 NULL),
1076 'Y',
1077 s.transaction_id,
1078 NULL, -- bugbug r12 has nvl(s.implement_uom_code,msi.uom_code).
1079 -- Should we get this from msi with plan_id = :refPlanId?
1080 -- Is it important? Will null be defaulted to the right thing?
1081 -- Run a test.
1082 s.sr_instance_id,
1083 s.schedule_priority,
1084 nvl(s.requested_completion_date, s.need_by_date)
1085 FROM msc_trading_partners tp,
1086 msc_parameters param,
1087 msc_item_id_lid item_lid,
1088 msc_supplies s
1089 WHERE tp.sr_tp_id= s.organization_id
1090 AND tp.sr_instance_id= s.sr_instance_id
1091 AND tp.partner_type=3
1092 AND param.organization_id = s.organization_id
1093 AND param.sr_instance_id = s.sr_instance_id
1094 AND item_lid.inventory_item_id = s.inventory_item_id
1095 AND item_lid.sr_instance_id = s.sr_instance_id
1096 AND s.transaction_id= lv_transaction_id(j)
1097 AND s.sr_instance_id= lv_instance_id(j)
1098 AND s.plan_id= arg_plan_id
1099 AND nvl(s.cfm_routing_flag,0) = 3
1100 AND s.release_status = 1;
1101
1102
1103 IF SQL%ROWCOUNT > 0
1104 THEN
1105 lv_loaded_jobs := SQL%ROWCOUNT;
1106
1107 ELSE
1108 lv_loaded_jobs := 0;
1109
1110 END IF;
1111
1112 -- ------------------------------------------------------------------------
1113 -- Perform the lot-based job mass load for the details
1114 -- -----------------------------------------------------------------------
1115
1116 /* lot-based job details are released only when the source profile WSM: Create Lot Based Job Routing is Yes
1117 and org planning parameter is primary */
1118
1119
1120 /* OPERATION NETWORKS */
1121
1122
1123 FORALL j IN 1..lv_job_count
1124 INSERT INTO MSC_WIP_JOB_DTLS_INTERFACE
1125 (last_update_date,
1126 last_updated_by,
1127 last_update_login,
1128 creation_date,
1129 created_by,
1130 group_id,
1131 parent_header_id,
1132 SUBSTITUTION_TYPE,
1133 LOAD_TYPE,
1134 process_phase,
1135 process_status,
1136 OPERATION_SEQ_NUM,
1137 NEXT_ROUTING_OP_SEQ_NUM,
1138 cfm_routing_flag,
1139 SR_INSTANCE_ID)
1140 (SELECT SYSDATE,
1141 arg_user_id,
1142 s.last_update_login,
1143 SYSDATE,
1144 arg_user_id,
1145 arg_wip_group_id,
1146 s.transaction_id,
1147 4,
1148 5,
1149 1,
1150 1,
1151 nwk.from_op_seq_num,
1152 nwk.to_op_seq_num,
1153 3,
1154 s.sr_instance_id
1155 From msc_supplies s,
1156 msc_operation_networks nwk,
1157 msc_apps_instances ins,
1158 msc_parameters param
1159 Where nwk.plan_id = -1
1160 AND nwk.sr_instance_id = s.sr_instance_id
1161 AND nwk.routing_sequence_id = s.routing_sequence_id
1162 AND nwk.transition_type = 1
1163 AND s.transaction_id= lv_transaction_id(j)
1164 AND s.sr_instance_id= lv_instance_id(j)
1165 AND s.plan_id= arg_plan_id
1166 AND lv_agg_details(j) = 1
1167 AND ins.instance_id = lv_instance_id(j)
1168 AND nvl(ins.lbj_details,2) = 1
1169 AND param.organization_id = s.organization_id
1170 AND param.sr_instance_id = s.sr_instance_id
1171 AND param.network_scheduling_method = 1
1172 AND s.release_status = 1);
1173
1174
1175 /* Operations */
1176
1177 FORALL j IN 1..lv_job_count
1178 INSERT INTO MSC_WIP_JOB_DTLS_INTERFACE
1179 (last_update_date,
1180 last_updated_by,
1181 last_update_login,
1182 creation_date,
1183 created_by,
1184 group_id,
1185 parent_header_id,
1186 SUBSTITUTION_TYPE,
1187 LOAD_TYPE,
1188 process_phase,
1189 process_status,
1190 OPERATION_SEQ_NUM,
1191 first_unit_start_date,
1192 last_unit_completion_date,
1193 cfm_routing_flag,
1194 SR_INSTANCE_ID,
1195 scheduled_quantity)
1196 (SELECT SYSDATE,
1197 arg_user_id,
1198 s.last_update_login,
1199 SYSDATE,
1200 arg_user_id,
1201 arg_wip_group_id,
1202 s.transaction_id,
1203 4,
1204 3,
1205 1,
1206 1,
1207 res.operation_seq_num,
1208 min(res.START_DATE),
1209 max(res.END_DATE),
1210 3,
1211 s.sr_instance_id,
1212 max(res.CUMMULATIVE_QUANTITY)
1213 From msc_supplies s,
1214 msc_resource_requirements res,
1215 msc_apps_instances ins,
1216 msc_parameters param
1217 Where res.plan_id = s.plan_id
1218 AND res.sr_instance_id = s.sr_instance_id
1219 AND s.transaction_id = res.supply_id
1220 AND res.parent_id = 2
1221 -- AND res.resource_id <> -1 Bug#3432607
1222 -- AND res.department_id <> -1
1223 AND s.transaction_id= lv_transaction_id(j)
1224 AND s.sr_instance_id= lv_instance_id(j)
1225 AND s.plan_id= arg_plan_id
1226 AND lv_agg_details(j) = 1
1227 AND ins.instance_id = lv_instance_id(j)
1228 AND nvl(ins.lbj_details,2) = 1
1229 AND param.organization_id = s.organization_id
1230 AND param.sr_instance_id = s.sr_instance_id
1231 AND param.network_scheduling_method = 1
1232 AND s.release_status = 1
1233 GROUP BY
1234 s.last_update_login,
1235 s.transaction_id,
1236 res.OPERATION_SEQ_NUM,
1237 s.sr_instance_id);
1238
1239 /* Resources */
1240 FORALL j IN 1..lv_job_count
1241 INSERT INTO MSC_WIP_JOB_DTLS_INTERFACE
1242 (last_update_date,
1243 last_updated_by,
1244 last_update_login,
1245 creation_date,
1246 created_by,
1247 group_id,
1248 parent_header_id,
1249 SUBSTITUTION_TYPE,
1250 LOAD_TYPE,
1251 process_phase,
1252 process_status,
1253 operation_seq_num,
1254 resource_id_new,
1255 start_date,
1256 completion_date,
1257 alternate_num,
1258 cfm_routing_flag,
1259 SR_INSTANCE_ID,
1260 firm_flag,
1261 setup_id,
1262 group_sequence_id,
1263 group_sequence_number,
1264 batch_id,
1265 maximum_assigned_units,
1266 parent_seq_num,
1267 resource_seq_num,
1268 schedule_seq_num,
1269 assigned_units,
1270 usage_rate_or_amount,
1271 scheduled_flag)
1272 (SELECT SYSDATE,
1273 arg_user_id,
1274 s.last_update_login,
1275 SYSDATE,
1276 arg_user_id,
1277 arg_wip_group_id,
1278 s.transaction_id,
1279 decode(res.parent_seq_num, null,4,2),
1280 1,
1281 1,
1282 1,
1283 res.operation_seq_num,
1284 res.resource_id,
1285 nvl(res.firm_start_date,res.START_DATE),
1286 nvl(res.firm_end_date,res.END_DATE),
1287 nvl(res.alternate_num,0),
1288 3,
1289 s.sr_instance_id,
1290 res.firm_flag,
1291 res.setup_id,
1292 res.group_sequence_id,
1293 res.group_sequence_number,
1294 res.batch_number,
1295 res.maximum_assigned_units,
1296 res.parent_seq_num,
1297 res.orig_resource_seq_num,
1298 res.resource_seq_num,
1299 res.assigned_units,
1300
1301 -- For OSFM we re-compute the rate constant. :-(
1302 -- Should really be an OSFM side calculation.
1303 -- We populate the reverse cumulative yield in resource requirements.
1304 decode(res.parent_seq_num,
1305 null,
1306 decode(res.basis_type,
1307 2, res.RESOURCE_HOURS,
1308 res.RESOURCE_HOURS /
1309 decode( msi.rounding_control_type,
1310 1, ROUND( s.new_order_quantity /
1311 nvl(res.REVERSE_CUMULATIVE_YIELD,1) ,6),
1312 s.new_order_quantity /
1313 nvl(res.REVERSE_CUMULATIVE_YIELD,1)
1314 )
1315 ) *
1316 decode(mdr.efficiency,
1317 NULL,1,
1318 0,1,
1319 mdr.efficiency / 100
1320 ) *
1321 decode( mdr.utilization,
1322 NULL,1,
1323 0,1,
1324 mdr.utilization / 100
1325 ),
1326 res.RESOURCE_HOURS
1327 ),
1328
1329 decode(nvl(res.schedule_flag,1),-23453,1,1,1,res.schedule_flag)
1330 From msc_supplies s,
1331 msc_resource_requirements res,
1332 msc_apps_instances ins,
1333 msc_parameters param,
1334 msc_department_resources mdr,
1335 msc_system_items msi
1336 Where res.plan_id = s.plan_id
1337 AND res.sr_instance_id = s.sr_instance_id
1338 AND s.transaction_id = res.supply_id
1339 AND res.parent_id = 2
1340 AND res.resource_id <> -1
1341 AND res.department_id <> -1
1342 AND s.transaction_id= lv_transaction_id(j)
1343 AND s.sr_instance_id= lv_instance_id(j)
1344 AND s.plan_id= arg_plan_id
1345 AND lv_agg_details(j) = 1
1346 AND ins.instance_id = lv_instance_id(j)
1347 AND nvl(ins.lbj_details,2) = 1
1348 AND param.organization_id = s.organization_id
1349 AND param.sr_instance_id = s.sr_instance_id
1350 AND -1 = mdr.plan_id
1351 AND res.organization_id =mdr.organization_id
1352 AND res.sr_instance_id = mdr.sr_instance_id
1353 AND res.resource_id = mdr.resource_id
1354 AND res.department_id=mdr.department_id
1355 AND msi.inventory_item_id = s.inventory_item_id
1356 AND msi.plan_id = -1
1357 AND msi.organization_id = s.organization_id
1358 AND msi.sr_instance_id = s.sr_instance_id
1359 AND param.network_scheduling_method = 1
1360 AND s.release_status = 1);
1361
1362 /*Components*/
1363
1364 FORALL j IN 1..lv_job_count
1365 INSERT INTO MSC_WIP_JOB_DTLS_INTERFACE
1366 (last_update_date,
1367 last_updated_by,
1368 last_update_login,
1369 creation_date,
1370 created_by,
1371 group_id,
1372 parent_header_id,
1373 SUBSTITUTION_TYPE,
1374 LOAD_TYPE,
1375 process_phase,
1376 process_status,
1377 operation_seq_num,
1378 inventory_item_id_new,
1379 primary_component_id,
1380 source_phantom_id,
1381 component_seq_id,
1382 mrp_net_flag,
1383 date_required,
1384 mps_date_required,
1385 basis_type,
1386 quantity_per_assembly,
1387 required_quantity,
1388 mps_required_quantity,
1389 cfm_routing_flag,
1390 SR_INSTANCE_ID)
1391 (SELECT SYSDATE,
1392 arg_user_id,
1393 s.last_update_login,
1394 SYSDATE,
1395 arg_user_id,
1396 arg_wip_group_id,
1397 s.transaction_id,
1398 4,
1399 2,
1400 1,
1401 1,
1402 nvl(md.op_seq_num,1),
1403 icomp.sr_inventory_item_id,
1404 icomp1.sr_inventory_item_id,
1405 icomp2.sr_inventory_item_id,
1406 md.COMP_SEQ_ID,
1407 1,
1408 md.USING_ASSEMBLY_DEMAND_DATE,
1409 md.USING_ASSEMBLY_DEMAND_DATE,
1410 -- bugbug Is this the correct way to compute basis_type?
1411 decode(md.component_scaling_type,1,NULL,md.component_scaling_type),
1412 md.quantity_per_assembly,
1413 md.USING_REQUIREMENT_QUANTITY,
1414 md.USING_REQUIREMENT_QUANTITY,
1415 3,
1416 s.sr_instance_id
1417 From msc_supplies s,
1418 msc_demands md,
1419 msc_system_items icomp,
1420 msc_system_items icomp1,
1421 msc_system_items icomp2,
1422 msc_apps_instances ins,
1423 msc_parameters param
1424 Where /* not exists (select 'x'
1425 from msc_exception_details excp
1426 where excp.plan_id = s.plan_id
1427 and excp.number1 = s.transaction_id
1428 and excp.inventory_item_id = s.inventory_item_id
1429 and excp.organization_id = s.organization_id
1430 and excp.sr_instance_id = s.sr_instance_id
1431 and excp.exception_type = 33
1432 and excp.number2 = md.inventory_item_id)*/ /* not needed as inv_old need not be populated*/
1433 icomp.inventory_item_id= md.inventory_item_id
1434 AND icomp.organization_id= md.organization_id
1435 AND icomp.sr_instance_id= md.sr_instance_id
1436 AND icomp.plan_id= -1
1437 AND nvl(icomp.wip_supply_type,0) <> 6
1438 AND icomp1.inventory_item_id= md.primary_component_id
1439 AND icomp1.organization_id= md.organization_id
1440 AND icomp1.sr_instance_id= md.sr_instance_id
1441 AND icomp1.plan_id= -1
1442 AND icomp2.inventory_item_id(+)= md.source_phantom_id
1443 AND icomp2.organization_id(+)= md.organization_id
1444 AND icomp2.sr_instance_id(+)= md.sr_instance_id
1445 AND icomp2.plan_id(+)= -1
1446 AND md.plan_id = s.plan_id
1447 AND md.sr_instance_id = s.sr_instance_id
1448 AND md.disposition_id= s.transaction_id
1449 AND md.origination_type = 1
1450 AND s.transaction_id= lv_transaction_id(j)
1451 AND s.sr_instance_id= lv_instance_id(j)
1452 AND s.plan_id= arg_plan_id
1453 AND lv_agg_details(j) = 1
1454 AND ins.instance_id = lv_instance_id(j)
1455 AND nvl(ins.lbj_details,2) = 1
1456 AND param.organization_id = s.organization_id
1457 AND param.sr_instance_id = s.sr_instance_id
1458 AND param.network_scheduling_method = 1
1459 AND s.release_status = 1);
1460
1461
1462 /* Resource Usage */
1463 FORALL j IN 1..lv_job_count
1464 INSERT INTO MSC_WIP_JOB_DTLS_INTERFACE
1465 (last_update_date,
1466 last_updated_by,
1467 last_update_login,
1468 creation_date,
1469 created_by,
1470 group_id,
1471 parent_header_id,
1472 SUBSTITUTION_TYPE,
1473 LOAD_TYPE,
1474 process_phase,
1475 process_status,
1476 operation_seq_num,
1477 resource_id_new,
1478 assigned_units,
1479 alternate_num,
1480 start_date,
1481 completion_date,
1482 cfm_routing_flag,
1483 SR_INSTANCE_ID,
1484 resource_seq_num,
1485 schedule_seq_num,
1486 parent_seq_num)
1487 (SELECT SYSDATE,
1488 arg_user_id,
1489 s.last_update_login,
1490 SYSDATE,
1491 arg_user_id,
1492 arg_wip_group_id,
1493 s.transaction_id,
1494 4,
1495 4,
1496 1,
1497 1,
1498 res.operation_seq_num,
1499 res.resource_id,
1500 res.assigned_units,
1501 nvl(res.alternate_num,0),
1502 nvl(res.firm_start_date,res.START_DATE),
1503 nvl(res.firm_end_date,res.END_DATE),
1504 3,
1505 s.sr_instance_id,
1506 res.orig_resource_seq_num,
1507 res.resource_seq_num,
1508 res.parent_seq_num
1509 From msc_supplies s,
1510 msc_resource_requirements res,
1511 msc_apps_instances ins,
1512 msc_parameters param
1513 Where res.plan_id = s.plan_id
1514 AND res.sr_instance_id = s.sr_instance_id
1515 AND s.transaction_id = res.supply_id
1516 AND res.parent_id = 1
1517 AND res.resource_id <> -1
1518 AND res.department_id <> -1
1519 AND s.transaction_id= lv_transaction_id(j)
1520 AND s.sr_instance_id= lv_instance_id(j)
1521 AND s.plan_id= arg_plan_id
1522 AND lv_agg_details(j) = 1
1523 AND ins.instance_id = lv_instance_id(j)
1524 AND nvl(ins.lbj_details,2) = 1
1525 AND param.organization_id = s.organization_id
1526 AND param.sr_instance_id = s.sr_instance_id
1527 AND param.network_scheduling_method = 1
1528 AND s.release_status = 1);
1529
1530 -- dsr begin: Operation Resource Instances
1531 FORALL j IN 1..lv_job_count
1532 INSERT INTO MSC_WIP_JOB_DTLS_INTERFACE
1533 ( last_update_date,
1534 last_updated_by,
1535 last_update_login,
1536 creation_date,
1537 created_by,
1538 organization_type,
1539 organization_id,
1540 group_id,
1541 parent_header_id,
1542 operation_seq_num,
1543 RESOURCE_INSTANCE_ID,
1544 start_date,
1545 completion_date,
1546 SUBSTITUTION_TYPE,
1547 LOAD_TYPE,
1548 process_phase,
1549 process_status,
1550 SR_INSTANCE_ID,
1551 operation_seq_id,
1552 FIRM_FLAG,
1553 resource_hours,
1554 department_id,
1555 SERIAL_NUMBER,
1556 group_sequence_id,
1557 group_sequence_number,
1558 batch_id
1559 , resource_seq_num
1560 , schedule_seq_num
1561 , parent_seq_num
1562 , cfm_routing_flag
1563 , resource_id_new
1564 , assigned_units
1565 )
1566 SELECT
1567 SYSDATE,
1568 arg_user_id,
1569 s.last_update_login,
1570 SYSDATE,
1571 arg_user_id,
1572 tp.organization_type,
1573 s.organization_id,
1574 arg_wip_group_id,
1575 s.transaction_id,
1576 resreq.OPERATION_SEQ_NUM,
1577 res_instreq.RES_INSTANCE_ID,
1578 nvl(resreq.firm_start_date,res_instreq.START_DATE),
1579 nvl(resreq.firm_end_date,res_instreq.END_DATE),
1580 SUBST_ADD,
1581 LT_RESOURCE_INSTANCE,
1582 2,
1583 1,
1584 s.sr_instance_id,
1585 resreq.operation_sequence_id,
1586 resreq.firm_flag,
1587 res_instreq.resource_instance_hours,
1588 resreq.department_id,
1589 res_instreq.serial_number,
1590 resreq.group_sequence_id,
1591 resreq.group_sequence_number,
1592 res_instreq.batch_number,
1593 resreq.orig_resource_seq_num
1594 , resreq.resource_seq_num
1595 , resreq.parent_seq_num
1596 , 3
1597 , resreq.resource_id
1598 , 1
1599 FROM
1600 msc_trading_partners tp,
1601 msc_resource_requirements resreq,
1602 msc_resource_instance_reqs res_instreq,
1603 msc_supplies s,
1604 msc_apps_instances ins,
1605 msc_parameters param
1606 WHERE
1607 tp.sr_tp_id=s.organization_id
1608 AND tp.sr_instance_id= s.sr_instance_id
1609 AND tp.partner_type=3
1610 AND resreq.sr_instance_id= s.sr_instance_id
1611 AND resreq.organization_id= s.organization_id
1612 AND resreq.supply_id = s.transaction_id
1613 AND resreq.plan_id = s.plan_id
1614 AND resreq.sr_instance_id = res_instreq.sr_instance_id
1615 AND resreq.plan_id = res_instreq.plan_id
1616 AND resreq.resource_seq_num = res_instreq.resource_seq_num
1617 AND resreq.operation_seq_num = res_instreq.operation_seq_num
1618 AND resreq.resource_id = res_instreq.resource_id
1619 AND resreq.supply_id = res_instreq.supply_id
1620 AND resreq.parent_id = res_instreq.parent_id
1621 AND resreq.start_date = res_instreq.start_date
1622 AND resreq.parent_id = 2
1623 AND resreq.resource_id <> -1
1624 AND resreq.department_id <> -1
1625 AND res_instreq.plan_id = s.plan_id
1626 AND s.transaction_id= lv_transaction_id(j)
1627 AND s.sr_instance_id= lv_instance_id(j)
1628 AND s.plan_id= arg_plan_id
1629 AND lv_agg_details(j) = 1
1630 AND ins.instance_id = lv_instance_id(j)
1631 AND nvl(ins.lbj_details,2) = 1
1632 AND param.organization_id = s.organization_id
1633 AND param.sr_instance_id = s.sr_instance_id
1634 AND param.network_scheduling_method = 1
1635 AND s.release_status = 1;
1636
1637
1638 -- print_debug_info( 'Operation Resource Instances: rows inserted into MSC_WIP_JOB_DTLS_INTERFACE = '
1639 -- || SQL%ROWCOUNT
1640 -- );
1641
1642 -- dsr: RESOURCE INSTANCE USAGES
1643
1644 FORALL j IN 1..lv_job_count
1645 INSERT INTO MSC_WIP_JOB_DTLS_INTERFACE
1646 ( last_update_date,
1647 last_updated_by,
1648 last_update_login,
1649 creation_date,
1650 created_by,
1651 organization_type,
1652 organization_id,
1653 group_id,
1654 parent_header_id,
1655 operation_seq_num,
1656 RESOURCE_INSTANCE_ID,
1657 start_date,
1658 completion_date,
1659 SUBSTITUTION_TYPE,
1660 LOAD_TYPE,
1661 process_phase,
1662 process_status,
1663 SR_INSTANCE_ID,
1664 operation_seq_id,
1665 FIRM_FLAG,
1666 resource_hours,
1667 department_id,
1668 serial_number,
1669 resource_seq_num,
1670 schedule_seq_num,
1671 parent_seq_num,
1672 cfm_routing_flag,
1673 resource_id_new,
1674 assigned_units
1675 )
1676 SELECT
1677 SYSDATE,
1678 arg_user_id,
1679 s.last_update_login,
1680 SYSDATE,
1681 arg_user_id,
1682 tp.organization_type,
1683 s.organization_id,
1684 arg_wip_group_id,
1685 s.transaction_id,
1686 resreq.OPERATION_SEQ_NUM,
1687 res_instreq.RES_INSTANCE_ID,
1688 nvl(resreq.firm_start_date,res_instreq.START_DATE),
1689 nvl(resreq.firm_end_date,res_instreq.END_DATE),
1690 SUBST_ADD,
1691 LT_RESOURCE_INST_USAGE,
1692 2,
1693 1,
1694 s.sr_instance_id,
1695 resreq.operation_sequence_id,
1696 resreq.firm_flag,
1697 res_instreq.resource_instance_hours,
1698 resreq.department_id,
1699 res_instreq.serial_number,
1700 resreq.orig_resource_seq_num
1701 , resreq.resource_seq_num
1702 , resreq.parent_seq_num
1703 , 3
1704 , resreq.resource_id
1705 , 1
1706 FROM
1707 msc_trading_partners tp,
1708 msc_resource_requirements resreq,
1709 msc_resource_instance_reqs res_instreq,
1710 msc_supplies s,
1711 msc_apps_instances ins,
1712 msc_parameters param
1713 WHERE
1714 tp.sr_tp_id=s.organization_id
1715 AND tp.sr_instance_id= s.sr_instance_id
1716 AND tp.partner_type=3
1717 AND resreq.sr_instance_id= s.sr_instance_id
1718 AND resreq.organization_id= s.organization_id
1719 AND resreq.supply_id = s.transaction_id
1720 AND resreq.plan_id = s.plan_id
1721 AND resreq.sr_instance_id = res_instreq.sr_instance_id
1722 AND resreq.plan_id = res_instreq.plan_id
1723 AND resreq.resource_seq_num = res_instreq.resource_seq_num
1724 AND resreq.operation_seq_num = res_instreq.operation_seq_num
1725 AND resreq.resource_id = res_instreq.resource_id
1726 AND resreq.supply_id = res_instreq.supply_id
1727 AND resreq.parent_id = res_instreq.parent_id --rawasthi
1728 AND resreq.start_date = res_instreq.start_date
1729 AND resreq.parent_id = 1
1730 AND resreq.resource_id <> -1
1731 AND resreq.department_id <> -1
1732 AND res_instreq.plan_id = s.plan_id
1733 AND s.transaction_id= lv_transaction_id(j)
1734 AND s.sr_instance_id= lv_instance_id(j)
1735 AND s.plan_id= arg_plan_id
1736 AND lv_agg_details(j) = 1
1737 AND ins.instance_id = lv_instance_id(j)
1738 AND nvl(ins.lbj_details,2) = 1
1739 AND param.organization_id = s.organization_id
1740 AND param.sr_instance_id = s.sr_instance_id
1741 AND param.network_scheduling_method = 1
1742 AND s.release_status = 1
1743 ;
1744
1745 -- print_debug_info( 'Resource Instance Usage: rows inserted into MSC_WIP_JOB_DTLS_INTERFACE = '
1746 -- || SQL%ROWCOUNT
1747 -- );
1748
1749 -- dsr end
1750
1751 return lv_loaded_jobs;
1752
1753 END load_osfm_lot_jobs_ps;
1754
1755
1756
1757 FUNCTION reschedule_osfm_lot_jobs_ps
1758 ( arg_plan_id IN NUMBER
1759 , arg_log_org_id IN NUMBER
1760 , arg_org_instance IN NUMBER
1761 , arg_owning_org_id IN NUMBER
1762 , arg_owning_instance IN NUMBER
1763 , arg_user_id IN NUMBER
1764 , arg_wip_group_id IN NUMBER
1765 , arg_transaction_id IN NUMBER
1766 )RETURN NUMBER
1767 IS
1768
1769 TYPE NumTab IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
1770
1771 lv_resched_jobs NUMBER;
1772 lv_transaction_id NumTab;
1773 lv_instance_id NumTab;
1774 lv_org_id NumTab;
1775 lv_plan_id NumTab;
1776 lv_agg_details NumTab;
1777
1778 BEGIN
1779
1780 /* Details will not be released
1781 for Non - Daily Bucketed Plans
1782 if the implement quantity or date is different then the planned quantity date
1783 if the Lot-based job uses aggregate resources
1784 if the job has faulty network*/
1785
1786 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'In reschedule_osfm_lot_jobs_ps');
1787 SELECT s.transaction_id,
1788 s.sr_instance_id,
1789 s.organization_id,
1790 s.plan_id
1791 BULK COLLECT
1792 INTO lv_transaction_id,
1793 lv_instance_id,
1794 lv_org_id,
1795 lv_plan_id
1796 FROM msc_plans mp,
1797 msc_supplies s,
1798 msc_plan_organizations_v orgs
1799 WHERE mp.plan_id = arg_plan_id
1800 AND s.release_errors is NULL
1801 AND s.implement_quantity > 0
1802 AND s.organization_id = orgs.planned_organization
1803 AND s.sr_instance_id = orgs.sr_instance_id
1804 AND s.plan_id = arg_plan_id
1805 AND orgs.plan_id = arg_plan_id
1806 AND orgs.organization_id = arg_owning_org_id
1807 AND orgs.owning_sr_instance = arg_owning_instance
1808 AND ( orgs.planned_organization= arg_log_org_id
1809 OR arg_log_org_id = arg_owning_org_id )
1810 AND orgs.sr_instance_id = arg_org_instance
1811 AND s.load_type = 6
1812 AND s.release_status = 1;
1813
1814 lv_resched_jobs:= SQL%ROWCOUNT;
1815
1816
1817 FOR k in 1..lv_resched_jobs
1818 Loop
1819 Begin
1820 SELECT 2
1821 Into lv_agg_details(k)
1822 FROM msc_department_resources deptres,
1823 msc_resource_requirements resreq
1824 WHERE resreq.sr_instance_id= lv_instance_id(k)
1825 AND resreq.supply_id = lv_transaction_id(k)
1826 AND resreq.organization_id= lv_org_id(k)
1827 AND resreq.plan_id = lv_plan_id(k)
1828 AND resreq.parent_id = 2
1829 AND deptres.plan_id = -1
1830 AND deptres.sr_instance_id= resreq.sr_instance_id
1831 AND deptres.resource_id= resreq.resource_id
1832 AND deptres.department_id= resreq.department_id
1833 AND deptres.organization_id= resreq.organization_id
1834 AND deptres.aggregate_resource_flag= 1
1835 AND rownum=1;
1836 Exception
1837 When no_data_found
1838 then
1839 lv_agg_details(k) := 1;
1840 End;
1841
1842 End Loop;
1843
1844 -- ------------------------------------------------------------------------
1845 -- Perform the lot based job reschedule
1846 -- ------------------------------------------------------------------------
1847 FORALL j in 1..lv_resched_jobs
1848 INSERT INTO msc_wip_job_schedule_interface
1849 (last_update_date,
1850 last_updated_by,
1851 cfm_routing_flag,
1852 last_update_login,
1853 creation_date,
1854 created_by,
1855 group_id,
1856 source_code,
1857 organization_id,
1858 organization_type,
1859 status_type,
1860 load_type,
1861 first_unit_start_date,
1862 last_unit_completion_date,
1863 bom_revision_date,
1864 routing_revision_date,
1865 job_name,
1866 firm_planned_flag,
1867 start_quantity, /* bug 1229891: net_quantity */
1868 net_quantity,
1869 wip_entity_id,
1870 demand_class,
1871 project_id,
1872 task_id,
1873 schedule_group_id,
1874 build_sequence,
1875 line_id,
1876 alternate_bom_designator,
1877 alternate_routing_designator,
1878 end_item_unit_number,
1879 process_phase,
1880 process_status,
1881 BILL_RTG_EXPLOSION_FLAG,
1882 HEADER_ID,
1883 uom_code, --Outbound Changes for XML
1884 SR_INSTANCE_ID,
1885 PRIMARY_ITEM_ID,
1886 source_line_id, --Outbound Changes for XML
1887 schedule_priority,
1888 requested_completion_date)
1889 SELECT SYSDATE,
1890 arg_user_id,
1891 s.cfm_routing_flag,
1892 s.last_update_login,
1893 SYSDATE,
1894 arg_user_id,
1895 arg_wip_group_id,
1896 'MSC',
1897 s.organization_id,
1898 tp.organization_type,
1899 NULL,
1900 6,
1901 new_wip_start_date,
1902 s.implement_date + 59/86400,
1903 NULL,
1904 NULL,
1905 s.implement_job_name,
1906 s.firm_planned_type,
1907 DECODE( tp.organization_type,
1908 1, DECODE(s.new_order_quantity,
1909 s.implement_quantity, TO_NUMBER(NULL),
1910 ((s.new_order_quantity + NVL(s.qty_completed, 0) +
1911 NVL(s.qty_scrapped, 0)) -
1912 (s.new_order_quantity - s.implement_quantity))),
1913 NULL),
1914 DECODE( tp.organization_type,
1915 2, DECODE(s.new_order_quantity,
1916 s.implement_quantity, TO_NUMBER(NULL),
1917 ((s.new_order_quantity + NVL(s.qty_completed, 0) +
1918 NVL(s.qty_scrapped, 0)) -
1919 (s.new_order_quantity - s.implement_quantity))),
1920 s.implement_quantity),
1921 s.disposition_id,
1922 s.implement_demand_class,
1923 s.implement_project_id,
1924 s.implement_task_id,
1925 s.implement_schedule_group_id,
1926 s.implement_build_sequence,
1927 s.implement_line_id,
1928 s.implement_alternate_bom,
1929 s.implement_alternate_routing,
1930 s.implement_unit_number,
1931 2,
1932 1,
1933 'Y',
1934 s.transaction_id,
1935 -- bugbug Is this right for Uom. R12 looks in msc_system_items if not here.
1936 s.implement_uom_code,
1937 s.sr_instance_id,
1938 item_lid.sr_inventory_item_id, -- msi.sr_inventory_item_id, -- ey, if you don't flush msc_system_items, you need to somehow pass the source_inventory_item_id to here MN: use msc_iten_id_lid
1939
1940 s.transaction_id, --Outbound Changes for XML
1941 s.schedule_priority,
1942 s.requested_completion_date
1943 FROM msc_trading_partners tp,
1944 msc_parameters param,
1945 msc_item_id_lid item_lid,
1946
1947 msc_supplies s,
1948 msc_plan_organizations_v orgs
1949 WHERE tp.sr_tp_id= s.organization_id
1950 AND tp.sr_instance_id= s.sr_instance_id
1951 AND tp.partner_type=3
1952 AND param.organization_id = s.organization_id
1953 AND param.sr_instance_id = s.sr_instance_id
1954 AND item_lid.sr_instance_id = s.sr_instance_id --MN:: added
1955 AND item_lid.inventory_item_id = s.inventory_item_id
1956 AND item_lid.sr_instance_id = s.sr_instance_id
1957 AND s.release_errors is NULL
1958 AND s.organization_id = orgs.planned_organization
1959 AND s.sr_instance_id = orgs.sr_instance_id
1960 AND s.plan_id = orgs.plan_id
1961 AND s.new_wip_start_date > SYSDATE
1962 AND orgs.organization_id = arg_owning_org_id
1963 AND orgs.owning_sr_instance = arg_owning_instance
1964 AND orgs.plan_id = arg_plan_id
1965 AND orgs.planned_organization = decode(arg_log_org_id,
1966 arg_owning_org_id, orgs.planned_organization,
1967 arg_log_org_id)
1968 AND orgs.sr_instance_id = arg_org_instance
1969 AND s.load_type = 6
1970 AND nvl(s.cfm_routing_flag,0) = 3
1971 AND s.transaction_id = lv_transaction_id(j)
1972 AND s.sr_instance_id = lv_instance_id(j)
1973 AND s.plan_id = lv_plan_id(j)
1974 AND s.release_status = 1;
1975
1976
1977
1978 -- ------------------------------------------------------------------------
1979 -- Perform the lot-based job mass load for the details
1980 -- -----------------------------------------------------------------------
1981
1982 /* lot-based job details are released only when the source profile WSM: Create Lot Based Job Routing is Yes
1983 and org planning parameter is primary */
1984
1985
1986 /* OPERATION NETWORKS */
1987
1988
1989 FORALL j IN 1..lv_resched_jobs
1990 INSERT INTO MSC_WIP_JOB_DTLS_INTERFACE
1991 (last_update_date,
1992 last_updated_by,
1993 last_update_login,
1994 creation_date,
1995 created_by,
1996 group_id,
1997 parent_header_id,
1998 SUBSTITUTION_TYPE,
1999 LOAD_TYPE,
2000 process_phase,
2001 process_status,
2002 job_op_seq_num,
2003 operation_seq_num,
2004 next_routing_op_seq_num,
2005 cfm_routing_flag,
2006 SR_INSTANCE_ID)
2007 (SELECT SYSDATE,
2008 arg_user_id,
2009 s.last_update_login,
2010 SYSDATE,
2011 arg_user_id,
2012 arg_wip_group_id,
2013 s.transaction_id,
2014 4,
2015 5,
2016 1,
2017 1,
2018 decode(s.JUMP_OP_SEQ_NUM, null, decode(nwk.from_op_seq_num, s.OPERATION_SEQ_NUM, s.JOB_OP_SEQ_NUM, null), nwk.from_op_seq_num, s.JOB_OP_SEQ_NUM,null),
2019 decode(nwk.from_op_seq_num, 50000 ,null,nwk.from_op_seq_num),
2020 nwk.to_op_seq_num,
2021 3,
2022 s.sr_instance_id
2023 From msc_supplies s,
2024 msc_job_operation_networks nwk,
2025 msc_apps_instances ins,
2026 msc_parameters param
2027 Where nwk.plan_id = -1
2028 AND nwk.sr_instance_id = s.sr_instance_id
2029 AND nwk.transaction_id = s.transaction_id
2030 AND nwk.recommended = 'Y'
2031 AND s.transaction_id= lv_transaction_id(j)
2032 AND s.sr_instance_id= lv_instance_id(j)
2033 AND s.plan_id= arg_plan_id
2034 AND s.new_wip_start_date > SYSDATE
2035 AND lv_agg_details(j) = 1
2036 AND ins.instance_id = lv_instance_id(j)
2037 AND nvl(ins.lbj_details,2) = 1
2038 AND param.organization_id = s.organization_id
2039 AND param.sr_instance_id = s.sr_instance_id
2040 AND param.network_scheduling_method = 1
2041 AND s.release_status = 1);
2042
2043
2044 /* Operations */
2045
2046 FORALL j IN 1..lv_resched_jobs
2047 INSERT INTO MSC_WIP_JOB_DTLS_INTERFACE
2048 (last_update_date,
2049 last_updated_by,
2050 last_update_login,
2051 creation_date,
2052 created_by,
2053 group_id,
2054 parent_header_id,
2055 SUBSTITUTION_TYPE,
2056 LOAD_TYPE,
2057 process_phase,
2058 process_status,
2059 job_op_seq_num,
2060 OPERATION_SEQ_NUM,
2061 first_unit_start_date,
2062 last_unit_completion_date,
2063 cfm_routing_flag,
2064 SR_INSTANCE_ID,
2065 scheduled_quantity)
2066 (SELECT SYSDATE,
2067 arg_user_id,
2068 s.last_update_login,
2069 SYSDATE,
2070 arg_user_id,
2071 arg_wip_group_id,
2072 s.transaction_id,
2073 4,
2074 3,
2075 1,
2076 1,
2077 decode(s.JUMP_OP_SEQ_NUM, null, decode(res.OPERATION_SEQ_NUM, s.OPERATION_SEQ_NUM, s.JOB_OP_SEQ_NUM, null), res.OPERATION_SEQ_NUM, s.JOB_OP_SEQ_NUM,null),
2078 decode(res.operation_seq_num, 50000,null,res.operation_seq_num),
2079 min(res.START_DATE),
2080 max(res.END_DATE),
2081 3,
2082 s.sr_instance_id,
2083 max(res.CUMMULATIVE_QUANTITY)
2084 From msc_supplies s,
2085 msc_resource_requirements res,
2086 msc_apps_instances ins,
2087 msc_parameters param
2088 Where res.plan_id = s.plan_id
2089 AND res.sr_instance_id = s.sr_instance_id
2090 AND s.transaction_id = res.supply_id
2091 AND res.parent_id = 2
2092 -- AND res.resource_id <> -1 --Bug#3432607
2093 -- AND res.department_id <> -1
2094 AND s.transaction_id= lv_transaction_id(j)
2095 AND s.sr_instance_id= lv_instance_id(j)
2096 AND s.plan_id= arg_plan_id
2097 AND s.new_wip_start_date > SYSDATE
2098 AND lv_agg_details(j) = 1
2099 AND ins.instance_id = lv_instance_id(j)
2100 AND nvl(ins.lbj_details,2) = 1
2101 AND param.organization_id = s.organization_id
2102 AND param.sr_instance_id = s.sr_instance_id
2103 AND param.network_scheduling_method = 1
2104 AND s.release_status = 1
2105 GROUP BY
2106 s.last_update_login,
2107 s.transaction_id,
2108 res.OPERATION_SEQ_NUM,
2109 s.sr_instance_id,
2110 s.OPERATION_SEQ_NUM,
2111 s.JUMP_OP_SEQ_NUM,
2112 s.JOB_OP_SEQ_NUM);
2113
2114 /* Resources */
2115 FORALL j IN 1..lv_resched_jobs
2116 INSERT INTO MSC_WIP_JOB_DTLS_INTERFACE
2117 (last_update_date,
2118 last_updated_by,
2119 last_update_login,
2120 creation_date,
2121 created_by,
2122 group_id,
2123 parent_header_id,
2124 SUBSTITUTION_TYPE,
2125 LOAD_TYPE,
2126 process_phase,
2127 process_status,
2128 job_op_seq_num,
2129 operation_seq_num,
2130 resource_id_new,
2131 start_date,
2132 completion_date,
2133 alternate_num,
2134 cfm_routing_flag,
2135 SR_INSTANCE_ID,
2136 firm_flag,
2137 setup_id,
2138 group_sequence_id,
2139 group_sequence_number,
2140 batch_id,
2141 maximum_assigned_units,
2142 parent_seq_num,
2143 resource_seq_num,
2144 schedule_seq_num,
2145 assigned_units,
2146 usage_rate_or_amount,
2147 scheduled_flag)
2148 (SELECT SYSDATE,
2149 arg_user_id,
2150 s.last_update_login,
2151 SYSDATE,
2152 arg_user_id,
2153 arg_wip_group_id,
2154 s.transaction_id,
2155 decode(res.parent_seq_num, null,4,2),
2156 1,
2157 1,
2158 1,
2159 decode(s.JUMP_OP_SEQ_NUM, null, decode(res.OPERATION_SEQ_NUM, s.OPERATION_SEQ_NUM, s.JOB_OP_SEQ_NUM, null), res.OPERATION_SEQ_NUM, s.JOB_OP_SEQ_NUM,null),
2160 decode(res.operation_seq_num, 50000,null,res.operation_seq_num),
2161 res.resource_id,
2162 nvl(res.firm_start_date,res.START_DATE),
2163 nvl(res.firm_end_date,res.END_DATE),
2164 nvl(res.alternate_num,0),
2165 3,
2166 s.sr_instance_id,
2167 res.firm_flag,
2168 res.setup_id,
2169 res.group_sequence_id,
2170 res.group_sequence_number,
2171 res.batch_number,
2172 res.maximum_assigned_units,
2173 res.parent_seq_num,
2174 res.orig_resource_seq_num,
2175 res.resource_seq_num,
2176 res.assigned_units,
2177
2178
2179 -- For OSFM we re-compute the rate constant. :-(
2180 -- Should really be an OSFM side calculation.
2181 -- We populate the reverse cumulative yield in resource requirements.
2182 decode(res.parent_seq_num,
2183 null,
2184 decode(res.basis_type,
2185 2,res.RESOURCE_HOURS,
2186 res.RESOURCE_HOURS /
2187 decode(msi.rounding_control_type,
2188 1, ROUND(s.new_order_quantity /
2189 nvl(res.REVERSE_CUMULATIVE_YIELD,1) ,6),
2190 s.new_order_quantity /
2191 nvl(res.REVERSE_CUMULATIVE_YIELD,1)
2192 )
2193 ) *
2194 decode( mdr.efficiency,
2195 NULL,1,
2196 0,1,
2197 mdr.efficiency / 100
2198 ) *
2199 decode(mdr.utilization,
2200 NULL,1,
2201 0,1,
2202 mdr.utilization / 100
2203 ),
2204 res.RESOURCE_HOURS
2205 ),
2206 decode(nvl(res.schedule_flag,1),-23453,1,1,1,res.schedule_flag)
2207 From msc_supplies s,
2208 msc_resource_requirements res,
2209 msc_apps_instances ins,
2210 msc_parameters param,
2211 msc_department_resources mdr,
2212 msc_system_items msi
2213 Where res.plan_id = s.plan_id
2214 AND res.sr_instance_id = s.sr_instance_id
2215 AND s.transaction_id = res.supply_id
2216 AND res.parent_id = 2
2217 AND res.resource_id <> -1
2218 AND res.department_id <> -1
2219 AND s.transaction_id= lv_transaction_id(j)
2220 AND s.sr_instance_id= lv_instance_id(j)
2221 AND s.plan_id= arg_plan_id
2222 AND s.new_wip_start_date > SYSDATE
2223 AND lv_agg_details(j) = 1
2224 AND ins.instance_id = lv_instance_id(j)
2225 AND nvl(ins.lbj_details,2) = 1
2226 AND param.organization_id = s.organization_id
2227 AND param.sr_instance_id = s.sr_instance_id
2228 AND -1 = mdr.plan_id
2229 AND res.organization_id =mdr.organization_id
2230 AND res.sr_instance_id = mdr.sr_instance_id
2231 AND res.resource_id = mdr.resource_id
2232 AND res.department_id=mdr.department_id
2233 AND msi.inventory_item_id = s.inventory_item_id
2234 AND msi.plan_id = -1
2235 AND msi.organization_id = s.organization_id
2236 AND msi.sr_instance_id = s.sr_instance_id
2237 AND param.network_scheduling_method = 1
2238 AND s.release_status = 1);
2239
2240 /*Components*/
2241
2242 FORALL j IN 1..lv_resched_jobs
2243 INSERT INTO MSC_WIP_JOB_DTLS_INTERFACE
2244 (last_update_date,
2245 last_updated_by,
2246 last_update_login,
2247 creation_date,
2248 created_by,
2249 group_id,
2250 parent_header_id,
2251 SUBSTITUTION_TYPE,
2252 LOAD_TYPE,
2253 process_phase,
2254 process_status,
2255 job_op_seq_num,
2256 operation_seq_num,
2257 inventory_item_id_new,
2258 primary_component_id,
2259 source_phantom_id,
2260 component_seq_id,
2261 mrp_net_flag,
2262 date_required,
2263 mps_date_required,
2264 basis_type,
2265 quantity_per_assembly,
2266 required_quantity,
2267 mps_required_quantity,
2268 cfm_routing_flag,
2269 SR_INSTANCE_ID)
2270 (SELECT SYSDATE,
2271 arg_user_id,
2272 s.last_update_login,
2273 SYSDATE,
2274 arg_user_id,
2275 arg_wip_group_id,
2276 s.transaction_id,
2277 4,
2278 2,
2279 1,
2280 1,
2281 decode(s.JUMP_OP_SEQ_NUM, null, decode(md.op_seq_num, s.OPERATION_SEQ_NUM, s.JOB_OP_SEQ_NUM, null), md.op_seq_num, s.JOB_OP_SEQ_NUM,null),
2282 decode(md.op_seq_num, 50000,null,md.op_seq_num),
2283 icomp.sr_inventory_item_id,
2284 icomp1.sr_inventory_item_id,
2285 icomp2.sr_inventory_item_id,
2286 md.COMP_SEQ_ID,
2287 1,
2288 md.USING_ASSEMBLY_DEMAND_DATE,
2289 md.USING_ASSEMBLY_DEMAND_DATE,
2290 -- bugbug Is this the correct way to compute lot basis?
2291 decode(md.component_scaling_type,1,NULL,md.component_scaling_type),
2292 md.quantity_per_assembly,
2293 md.USING_REQUIREMENT_QUANTITY,
2294 md.USING_REQUIREMENT_QUANTITY,
2295 3,
2296 s.sr_instance_id
2297 From msc_supplies s,
2298 msc_demands md,
2299 msc_system_items icomp,
2300 msc_system_items icomp1,
2301 msc_system_items icomp2,
2302 msc_apps_instances ins,
2303 msc_parameters param
2304 Where /* not exists (select 'x'
2305 from msc_exception_details excp
2306 where excp.plan_id = s.plan_id
2307 and excp.number1 = s.transaction_id
2308 and excp.inventory_item_id = s.inventory_item_id
2309 and excp.organization_id = s.organization_id
2310 and excp.sr_instance_id = s.sr_instance_id
2311 and excp.exception_type = 33
2312 and excp.number2 = md.inventory_item_id)*/ /* not needed as inv_old need not be populated*/
2313 icomp.inventory_item_id= md.inventory_item_id
2314 AND icomp.organization_id= md.organization_id
2315 AND icomp.sr_instance_id= md.sr_instance_id
2316 AND icomp.plan_id= -1
2317 AND nvl(icomp.wip_supply_type,0) <> 6
2318 AND icomp1.inventory_item_id= md.primary_component_id
2319 AND icomp1.organization_id= md.organization_id
2320 AND icomp1.sr_instance_id= md.sr_instance_id
2321 AND icomp1.plan_id= -1
2322 AND icomp2.inventory_item_id(+)= md.source_phantom_id
2323 AND icomp2.organization_id(+)= md.organization_id
2324 AND icomp2.sr_instance_id(+)= md.sr_instance_id
2325 AND icomp2.plan_id(+)= -1
2326 AND md.plan_id = s.plan_id
2327 AND md.sr_instance_id = s.sr_instance_id
2328 AND md.disposition_id= s.transaction_id
2329 AND md.origination_type = 3
2330 AND s.transaction_id= lv_transaction_id(j)
2331 AND s.sr_instance_id= lv_instance_id(j)
2332 AND s.plan_id= arg_plan_id
2333 AND s.new_wip_start_date > SYSDATE
2334 AND lv_agg_details(j) = 1
2335 AND ins.instance_id = lv_instance_id(j)
2336 AND nvl(ins.lbj_details,2) = 1
2337 AND param.organization_id = s.organization_id
2338 AND param.sr_instance_id = s.sr_instance_id
2339 AND param.network_scheduling_method = 1
2340 AND s.release_status = 1);
2341
2342
2343 /* Resource Usage */
2344 FORALL j IN 1..lv_resched_jobs
2345 INSERT INTO MSC_WIP_JOB_DTLS_INTERFACE
2346 (last_update_date,
2347 last_updated_by,
2348 last_update_login,
2349 creation_date,
2350 created_by,
2351 group_id,
2352 parent_header_id,
2353 SUBSTITUTION_TYPE,
2354 LOAD_TYPE,
2355 process_phase,
2356 process_status,
2357 job_op_seq_num,
2358 operation_seq_num,
2359 resource_id_new,
2360 assigned_units,
2361 alternate_num,
2362 start_date,
2363 completion_date,
2364 cfm_routing_flag,
2365 SR_INSTANCE_ID,
2366 resource_seq_num,
2367 schedule_seq_num,
2368 parent_seq_num)
2369 (SELECT SYSDATE,
2370 arg_user_id,
2371 s.last_update_login,
2372 SYSDATE,
2373 arg_user_id,
2374 arg_wip_group_id,
2375 s.transaction_id,
2376 4,
2377 4,
2378 1,
2379 1,
2380 decode(s.JUMP_OP_SEQ_NUM, null, decode(res.operation_seq_num, s.OPERATION_SEQ_NUM, s.JOB_OP_SEQ_NUM, null), res.operation_seq_num, s.JOB_OP_SEQ_NUM,null),
2381 decode(res.operation_seq_num, 50000,null,res.operation_seq_num),
2382 res.resource_id,
2383 res.assigned_units,
2384 nvl(res.alternate_num,0),
2385 nvl(res.firm_start_date,res.START_DATE),
2386 nvl(res.firm_end_date,res.END_DATE),
2387 3,
2388 s.sr_instance_id,
2389 res.orig_resource_seq_num,
2390 res.resource_seq_num,
2391 res.parent_seq_num
2392 From msc_supplies s,
2393 msc_resource_requirements res,
2394 msc_apps_instances ins,
2395 msc_parameters param
2396 Where res.plan_id = s.plan_id
2397 AND res.sr_instance_id = s.sr_instance_id
2398 AND s.transaction_id = res.supply_id
2399 AND res.parent_id = 1
2400 AND res.resource_id <> -1
2401 AND res.department_id <> -1
2402 AND s.transaction_id= lv_transaction_id(j)
2403 AND s.sr_instance_id= lv_instance_id(j)
2404 AND s.plan_id= arg_plan_id
2405 AND s.new_wip_start_date > SYSDATE
2406 AND lv_agg_details(j) = 1
2407 AND ins.instance_id = lv_instance_id(j)
2408 AND nvl(ins.lbj_details,2) = 1
2409 AND param.organization_id = s.organization_id
2410 AND param.sr_instance_id = s.sr_instance_id
2411 AND param.network_scheduling_method = 1
2412 AND s.release_status = 1);
2413 -- dsr begin: Operation Resource Instances
2414
2415 FORALL j IN 1..lv_resched_jobs
2416 INSERT INTO MSC_WIP_JOB_DTLS_INTERFACE
2417 ( last_update_date,
2418 last_updated_by,
2419 last_update_login,
2420 creation_date,
2421 created_by,
2422 organization_type,
2423 organization_id,
2424 group_id,
2425 parent_header_id,
2426 job_op_seq_num,
2427 operation_seq_num,
2428 RESOURCE_INSTANCE_ID,
2429 start_date,
2430 completion_date,
2431 SUBSTITUTION_TYPE,
2432 LOAD_TYPE,
2433 process_phase,
2434 process_status,
2435 SR_INSTANCE_ID,
2436 operation_seq_id,
2437 FIRM_FLAG,
2438 resource_hours,
2439 department_id,
2440 SERIAL_NUMBER,
2441 group_sequence_id,
2442 group_sequence_number,
2443 batch_id
2444 , resource_seq_num -- dsr
2445 , schedule_seq_num
2446 , parent_seq_num
2447 , cfm_routing_flag
2448 , resource_id_new
2449 , assigned_units
2450 )
2451 SELECT
2452 SYSDATE,
2453 arg_user_id,
2454 s.last_update_login,
2455 SYSDATE,
2456 arg_user_id,
2457 tp.organization_type,
2458 s.organization_id,
2459 arg_wip_group_id,
2460 s.transaction_id,
2461 decode(s.JUMP_OP_SEQ_NUM, null, decode(resreq.operation_seq_num, s.OPERATION_SEQ_NUM, s.JOB_OP_SEQ_NUM, null), resreq.operation_seq_num, s.JOB_OP_SEQ_NUM,null),
2462 decode(resreq.operation_seq_num, 50000,null,resreq.operation_seq_num),
2463 res_instreq.RES_INSTANCE_ID,
2464 nvl(resreq.firm_start_date,res_instreq.START_DATE),
2465 nvl(resreq.firm_end_date,res_instreq.END_DATE),
2466 SUBST_ADD,
2467 LT_RESOURCE_INSTANCE,
2468 2,
2469 1,
2470 s.sr_instance_id,
2471 resreq.operation_sequence_id,
2472 resreq.firm_flag,
2473 res_instreq.resource_instance_hours,
2474 resreq.department_id,
2475 res_instreq.serial_number,
2476 resreq.group_sequence_id,
2477 resreq.group_sequence_number,
2478 res_instreq.batch_number,
2479 resreq.orig_resource_seq_num,
2480 resreq.resource_seq_num,
2481 resreq.parent_seq_num,
2482 3,
2483 resreq.resource_id,
2484 1
2485 FROM msc_trading_partners tp,
2486 msc_resource_requirements resreq,
2487 msc_resource_instance_reqs res_instreq,
2488 msc_supplies s,
2489 msc_apps_instances ins,
2490 msc_parameters param
2491 WHERE
2492 tp.sr_tp_id=s.organization_id
2493 AND tp.sr_instance_id= s.sr_instance_id
2494 AND tp.partner_type=3
2495 AND resreq.sr_instance_id= s.sr_instance_id
2496 AND resreq.organization_id= s.organization_id
2497 AND resreq.supply_id = s.transaction_id
2498 AND resreq.plan_id = s.plan_id
2499 AND resreq.sr_instance_id = res_instreq.sr_instance_id
2500 AND resreq.plan_id = res_instreq.plan_id
2501 AND resreq.resource_seq_num = res_instreq.resource_seq_num
2502 AND resreq.operation_seq_num = res_instreq.operation_seq_num
2503 AND resreq.resource_id = res_instreq.resource_id
2504 AND resreq.supply_id = res_instreq.supply_id
2505 AND resreq.parent_id = res_instreq.parent_id
2506 AND resreq.start_date = res_instreq.start_date
2507 AND resreq.parent_id = 2
2508 AND resreq.resource_id <> -1
2509 AND resreq.department_id <> -1
2510 AND res_instreq.plan_id = s.plan_id
2511 AND s.transaction_id= lv_transaction_id(j)
2512 AND s.sr_instance_id= lv_instance_id(j)
2513 AND s.plan_id= arg_plan_id
2514 AND s.new_wip_start_date > SYSDATE
2515 AND lv_agg_details(j) = 1
2516 AND ins.instance_id = lv_instance_id(j)
2517 AND nvl(ins.lbj_details,2) = 1
2518 AND param.organization_id = s.organization_id
2519 AND param.sr_instance_id = s.sr_instance_id
2520 AND param.network_scheduling_method = 1
2521 AND s.release_status = 1
2522 ;
2523
2524 -- print_debug_info('Resource Instance: rows inserted into msc_wip_job_dtls_interface = '
2525 -- || sql%rowcount);
2526
2527 FORALL j IN 1..lv_resched_jobs
2528 INSERT INTO MSC_WIP_JOB_DTLS_INTERFACE
2529 ( last_update_date,
2530 last_updated_by,
2531 last_update_login,
2532 creation_date,
2533 created_by,
2534 organization_type,
2535 organization_id,
2536 group_id,
2537 parent_header_id,
2538 job_op_seq_num,
2539 operation_seq_num,
2540 RESOURCE_INSTANCE_ID,
2541 start_date,
2542 completion_date,
2543 SUBSTITUTION_TYPE,
2544 LOAD_TYPE,
2545 process_phase,
2546 process_status,
2547 SR_INSTANCE_ID,
2548 operation_seq_id,
2549 FIRM_FLAG,
2550 resource_hours,
2551 department_id,
2552 serial_number
2553 , resource_seq_num -- dsr
2554 , schedule_seq_num
2555 , parent_seq_num
2556 , cfm_routing_flag
2557 , resource_id_new
2558 , assigned_units
2559 )
2560 SELECT
2561 SYSDATE,
2562 arg_user_id,
2563 s.last_update_login,
2564 SYSDATE,
2565 arg_user_id,
2566 tp.organization_type,
2567 s.organization_id,
2568 arg_wip_group_id,
2569 s.transaction_id,
2570 decode(s.JUMP_OP_SEQ_NUM, null, decode(resreq.operation_seq_num, s.OPERATION_SEQ_NUM, s.JOB_OP_SEQ_NUM, null), resreq.operation_seq_num, s.JOB_OP_SEQ_NUM,null),
2571 decode(resreq.operation_seq_num, 50000,null,resreq.operation_seq_num),
2572 res_instreq.RES_INSTANCE_ID,
2573 nvl(resreq.firm_start_date,res_instreq.START_DATE),
2574 nvl(resreq.firm_end_date,res_instreq.END_DATE),
2575 SUBST_ADD,
2576 LT_RESOURCE_INST_USAGE,
2577 2,
2578 1,
2579 s.sr_instance_id,
2580 resreq.operation_sequence_id,
2581 resreq.firm_flag,
2582 res_instreq.resource_instance_hours,
2583 resreq.department_id,
2584 res_instreq.serial_number,
2585 resreq.orig_resource_seq_num
2586 , resreq.resource_seq_num
2587 , resreq.parent_seq_num
2588 , 3
2589 , resreq.resource_id
2590 , 1
2591 FROM
2592 msc_trading_partners tp,
2593 msc_resource_requirements resreq,
2594 msc_resource_instance_reqs res_instreq,
2595 msc_supplies s,
2596 msc_apps_instances ins,
2597 msc_parameters param
2598 WHERE
2599 tp.sr_tp_id=s.organization_id
2600 AND tp.sr_instance_id= s.sr_instance_id
2601 AND tp.partner_type=3
2602 AND resreq.sr_instance_id= s.sr_instance_id
2603 AND resreq.organization_id= s.organization_id
2604 AND resreq.supply_id = s.transaction_id
2605 AND resreq.plan_id = s.plan_id
2606 AND resreq.sr_instance_id = res_instreq.sr_instance_id
2607 AND resreq.plan_id = res_instreq.plan_id
2608 AND resreq.resource_seq_num = res_instreq.resource_seq_num
2609 AND resreq.operation_seq_num = res_instreq.operation_seq_num
2610 AND resreq.resource_id = res_instreq.resource_id
2611 AND resreq.supply_id = res_instreq.supply_id
2612 AND resreq.parent_id = res_instreq.parent_id --rawasthi
2613 AND resreq.start_date = res_instreq.start_date
2614 AND resreq.parent_id = 1
2615 AND resreq.resource_id <> -1
2616 AND resreq.department_id <> -1
2617 AND s.transaction_id= lv_transaction_id(j)
2618 AND s.sr_instance_id= lv_instance_id(j)
2619 AND s.plan_id= arg_plan_id
2620 AND s.new_wip_start_date > SYSDATE
2621 AND lv_agg_details(j) = 1
2622 AND ins.instance_id = lv_instance_id(j)
2623 AND nvl(ins.lbj_details,2) = 1
2624 AND param.organization_id = s.organization_id
2625 AND param.sr_instance_id = s.sr_instance_id
2626 AND param.network_scheduling_method = 1
2627 AND s.release_status = 1
2628 ;
2629
2630 -- print_debug_info('Resource Instance Usage: rows inserted into msc_wip_job_dtls_interface = '
2631 -- || sql%rowcount);
2632
2633 -- dsr end
2634 RETURN lv_resched_jobs;
2635
2636 END reschedule_osfm_lot_jobs_ps;
2637
2638
2639
2640 FUNCTION load_wip_discr_jobs_ps
2641 ( arg_plan_id IN NUMBER
2642 , arg_log_org_id IN NUMBER
2643 , arg_org_instance IN NUMBER
2644 , arg_owning_org_id IN NUMBER
2645 , arg_owning_instance IN NUMBER
2646 , arg_user_id IN NUMBER
2647 , arg_wip_group_id IN NUMBER
2648 , l_apps_ver IN VARCHAR2
2649 )RETURN NUMBER
2650 IS
2651 lv_loaded_jobs NUMBER;
2652
2653 TYPE NumTab IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
2654 --TYPE RIDTab IS TABLE OF ROWID INDEX BY BINARY_INTEGER;
2655
2656 lv_transaction_id NumTab;
2657 lv_instance_id NumTab;
2658 lv_Agg_details NumTab;
2659 Lv_org_id Numtab;
2660 lv_plan_id NumTab;
2661 lv_job_count NUMBER;
2662 lv_release_details NUMBER;
2663 lv_inflate_wip NUMBER;
2664 lv_round_primary_item NumTab;
2665
2666 BEGIN
2667
2668 SELECT decode(nvl(FND_PROFILE.value('MSC_RELEASE_DTLS_REVDATE'),'Y'),'N',2,1),
2669 DECODE(NVL(fnd_profile.value('MSC_INFLATE_WIP') ,'N'), 'N',2 ,1)
2670 INTO lv_release_details,lv_inflate_wip
2671 FROM dual;
2672
2673 /* we release the discrete job, only if it doesn't use aggregate resources */
2674 /* bug 1252659 fix, replace rowid by
2675 (transaction_id,sr_instance_id,plan_id) */
2676
2677 /* Details will NOT be released for
2678 a. Unconstrained Plan for 11i Source
2679 a.1 bug 4613532 - not only for 11i source, any source.
2680 b. if the new_wip_start_date is null
2681 c. if the implement quantity or date is different then the planned quantity date
2682 d. if the revision date is different then the new_wip_start_date
2683 and the profile option setting : MSC_RELEASE_DTLS_REVDATE = 'N'
2684 e. Non - Daily Bucketed Plans
2685 f. BUG 4383804
2686 Alternate BOM/Routing is changed during release.
2687 */
2688
2689
2690 SELECT s.transaction_id,
2691 s.sr_instance_id,
2692 s.organization_id,
2693 s.plan_id,
2694 2
2695 BULK COLLECT
2696 INTO lv_transaction_id,
2697 lv_instance_id,
2698 lv_org_id,
2699 lv_plan_id,
2700 lv_round_primary_item
2701 FROM msc_supplies s,
2702 msc_plan_organizations_v orgs,
2703 msc_system_items msi, -- REMOVE
2704 msc_plans mp
2705 WHERE mp.plan_id = arg_plan_id
2706 AND s.release_errors is NULL
2707 AND s.implement_quantity > 0
2708 AND s.organization_id = orgs.planned_organization
2709 AND s.sr_instance_id = orgs.sr_instance_id
2710 AND s.plan_id = orgs.plan_id
2711 AND msi.inventory_item_id = s.inventory_item_id
2712 AND msi.plan_id = -1
2713 AND msi.organization_id = s.organization_id
2714 AND msi.sr_instance_id = s.sr_instance_id
2715 AND orgs.plan_id = mp.plan_id
2716 AND orgs.organization_id = arg_owning_org_id
2717 AND orgs.owning_sr_instance = arg_owning_instance
2718 AND ( orgs.planned_organization= arg_log_org_id
2719 OR arg_log_org_id = arg_owning_org_id )
2720 AND orgs.sr_instance_id = arg_org_instance
2721 AND s.load_type = WIP_DIS_MASS_LOAD
2722 and s.new_wip_start_date IS NOT NULL
2723 AND s.release_status = 1
2724 UNION
2725 SELECT s.transaction_id,
2726 s.sr_instance_id,
2727 s.organization_id,
2728 s.plan_id,
2729 2 /* setting rounding control to 2 ,since details are not released and this flag is used in details*/
2730 FROM msc_supplies s,
2731 msc_plan_organizations_v orgs
2732 WHERE s.release_errors is NULL
2733 AND s.implement_quantity > 0
2734 AND s.organization_id = orgs.planned_organization
2735 AND s.sr_instance_id = orgs.sr_instance_id
2736 AND s.plan_id = arg_plan_id
2737 AND orgs.plan_id = arg_plan_id
2738 AND orgs.organization_id = arg_owning_org_id
2739 AND orgs.owning_sr_instance = arg_owning_instance
2740 AND ( orgs.planned_organization= arg_log_org_id
2741 OR arg_log_org_id = arg_owning_org_id )
2742 AND orgs.sr_instance_id = arg_org_instance
2743 AND s.load_type = WIP_DIS_MASS_LOAD
2744 and s.new_wip_start_date IS NULL
2745 AND s.release_status = 1;
2746
2747 lv_job_count:= SQL%ROWCOUNT;
2748
2749 -- -----------------------------------------------------------------------
2750 -- Perform the wip discrete job mass load
2751 -- -----------------------------------------------------------------------
2752 /* Due to we only give PLANNED components, BILL_RTG_EXPLOSION_FLAG
2753 is set to 'Y'. */
2754
2755 --DBMS_OUTPUT.PUT_LINE('LOAD_JOB');
2756
2757 FOR k in 1..lv_job_count
2758 Loop
2759 Begin
2760 SELECT 2
2761 Into lv_agg_details(k)
2762 FROM msc_department_resources deptres,
2763 msc_resource_requirements resreq
2764 WHERE resreq.sr_instance_id= lv_instance_id(k)
2765 AND resreq.supply_id = lv_transaction_id(k)
2766 AND resreq.organization_id= lv_org_id(k)
2767 AND resreq.plan_id = lv_plan_id(k)
2768 AND resreq.parent_id = 2
2769 AND deptres.plan_id = -1
2770 AND deptres.sr_instance_id= resreq.sr_instance_id
2771 AND deptres.resource_id= resreq.resource_id
2772 AND deptres.department_id= resreq.department_id
2773 AND deptres.organization_id= resreq.organization_id
2774 AND deptres.aggregate_resource_flag= 1
2775 AND rownum=1;
2776 Exception
2777 When no_data_found
2778 then
2779 lv_agg_details(k) := 1;
2780 End;
2781
2782 End Loop;
2783
2784
2785
2786 FORALL j IN 1..lv_job_count
2787 INSERT INTO msc_wip_job_schedule_interface
2788 (last_update_date,
2789 last_updated_by,
2790 last_update_login,
2791 creation_date,
2792 created_by,
2793 group_id,
2794 source_code,
2795 source_line_id,
2796 organization_id,
2797 organization_type,
2798 load_type,
2799 status_type,
2800 first_unit_start_date,
2801 last_unit_completion_date,
2802 bom_revision_date,
2803 routing_revision_date,
2804 primary_item_id,
2805 class_code,
2806 job_name,
2807 firm_planned_flag,
2808 start_quantity,
2809 net_quantity,
2810 demand_class,
2811 project_id,
2812 task_id,
2813 schedule_group_id,
2814 build_sequence,
2815 line_id,
2816 alternate_bom_designator,
2817 alternate_routing_designator,
2818 end_item_unit_number,
2819 process_phase,
2820 process_status,
2821 bom_reference_id,
2822 routing_reference_id,
2823 BILL_RTG_EXPLOSION_FLAG,
2824 HEADER_ID,
2825 uom_code, --Outbound Changes for XML
2826 SR_INSTANCE_ID,
2827 schedule_priority,
2828 requested_completion_date)
2829 SELECT SYSDATE,
2830 arg_user_id,
2831 s.last_update_login,
2832 decode(tp.organization_type,2,s.creation_date,SYSDATE),
2833 arg_user_id,
2834 arg_wip_group_id,
2835 'MSC',
2836 s.transaction_id,
2837 s.organization_id,
2838 tp.organization_type,
2839 1,
2840 decode(tp.organization_type,2,1,s.implement_status_code),
2841 s.new_wip_start_date,
2842 s.implement_date,
2843 /* Added to code to release the greatest of sysdate OR the BOM/Routing revision date */
2844 SYSDATE + (1439/1440),
2845 SYSDATE + (1439/1440), --bug 5388465
2846 item_lid.sr_inventory_item_id, --MN: is this correct? --ey yes
2847 s.implement_wip_class_code,
2848 s.implement_job_name,
2849 s.firm_planned_type,
2850
2851 /* Bug 4540170 - PLANNED ORDERS RELEASED FROM ASCP DO NOT CREATE BATCH WITH CORRECT QTYS */
2852 decode(tp.organization_type,2,s.implement_quantity, -- 4540170
2853 decode(s.implement_quantity,s.new_order_quantity,
2854 nvl(s.wip_start_quantity,s.implement_quantity),
2855 s.implement_quantity)
2856 ),
2857 s.implement_quantity,
2858 s.implement_demand_class,
2859 s.implement_project_id,
2860 s.implement_task_id,
2861 s.implement_schedule_group_id,
2862 s.implement_build_sequence,
2863 s.implement_line_id,
2864 s.implement_alternate_bom,
2865 s.implement_alternate_routing,
2866 s.implement_unit_number,
2867 2,
2868 1,
2869 DECODE( tp.organization_type, --RS: publish into supplies table
2870 2, s.bill_sequence_id, --RS: it was taking from msc_process_efficiency before
2871 NULL),
2872 DECODE( tp.organization_type, --RS: publish into supplies table
2873 2, s.routing_sequence_id, --RS: it was taking from msc_process_efficiency before
2874 NULL),
2875 'Y',
2876 s.transaction_id,
2877 -- bugbug Is null ok for uom?
2878 NULL,
2879 s.sr_instance_id,
2880 s.schedule_priority,
2881 nvl(s.requested_completion_date, s.need_by_date)
2882 FROM msc_trading_partners tp,
2883 msc_parameters param,
2884 msc_item_id_lid item_lid,
2885 msc_supplies s
2886 WHERE tp.sr_tp_id= s.organization_id
2887 AND tp.sr_instance_id= s.sr_instance_id
2888 AND tp.partner_type=3
2889 AND param.organization_id = s.organization_id
2890 AND param.sr_instance_id = s.sr_instance_id
2891 AND item_lid.inventory_item_id = s.inventory_item_id
2892 AND item_lid.sr_instance_id = s.sr_instance_id
2893 AND s.transaction_id= lv_transaction_id(j)
2894 AND s.sr_instance_id= lv_instance_id(j)
2895 AND s.plan_id= arg_plan_id
2896 AND s.release_status = 1;
2897
2898 IF SQL%ROWCOUNT > 0
2899 THEN
2900 lv_loaded_jobs := SQL%ROWCOUNT;
2901
2902 ELSE
2903 lv_loaded_jobs := 0;
2904
2905 END IF;
2906
2907
2908
2909
2910
2911 -- ------------------------------------------------------------------------
2912 -- Perform the wip discrete job mass load for the details
2913 -- ------------------------------------------------------------------------
2914
2915 /* the details are populated, only if the implement date and quantity
2916 are the same as the planned date and quantity */
2917
2918 /* OPERATIONS */
2919 FORALL j IN 1..lv_job_count
2920 INSERT INTO MSC_WIP_JOB_DTLS_INTERFACE
2921 (last_update_date,
2922 last_updated_by,
2923 last_update_login,
2924 creation_date,
2925 created_by,
2926 organization_type,
2927 organization_id,
2928 group_id,
2929 parent_header_id,
2930 operation_seq_num,
2931 department_id,
2932 description,
2933 standard_operation_id,
2934 first_unit_start_date,
2935 first_unit_completion_date,
2936 last_unit_start_date,
2937 last_unit_completion_date,
2938 minimum_transfer_quantity,
2939 count_point_type,
2940 backflush_flag,
2941 SUBSTITUTION_TYPE,
2942 LOAD_TYPE,
2943 process_phase,
2944 process_status,
2945 operation_seq_id, --Outbound changes for XML
2946 SR_INSTANCE_ID)
2947 SELECT SYSDATE,
2948 arg_user_id,
2949 s.last_update_login,
2950 SYSDATE,
2951 arg_user_id,
2952 tp.organization_type,
2953 s.organization_id,
2954 arg_wip_group_id,
2955 s.transaction_id,
2956 resreq.OPERATION_SEQ_NUM,
2957 NULL, --department_id,
2958 NULL, --description,
2959 NULL, --standard_operation_id,
2960 min(resreq.START_DATE), --first_unit_start_date,
2961 min(resreq.START_DATE), --first_unit_completion_date,
2962 max(resreq.END_DATE), --last_unit_start_date,
2963 max(resreq.END_DATE), --last_unit_completion_date,
2964 NULL, --minimum_transfer_quantity,
2965 NULL, --count_point_type,
2966 NULL, --backflush_flag,
2967 SUBST_CHANGE,
2968 LT_OPERATION,
2969 2,
2970 1,
2971 resreq.operation_sequence_id, --Outbound changes for XML
2972 s.sr_instance_id
2973 FROM msc_trading_partners tp,
2974 msc_resource_requirements resreq,
2975 msc_parameters param,
2976 msc_supplies s
2977 WHERE tp.sr_tp_id= s.organization_id
2978 AND tp.sr_instance_id= s.sr_instance_id
2979 AND tp.partner_type=3
2980 AND resreq.sr_instance_id= s.sr_instance_id
2981 AND resreq.organization_id= s.organization_id
2982 AND resreq.supply_id = s.transaction_id
2983 AND resreq.plan_id = s.plan_id
2984 AND resreq.parent_id = 2
2985 AND resreq.resource_id <> -1
2986 AND resreq.department_id <> -1
2987 AND param.organization_id = s.organization_id
2988 AND param.sr_instance_id = s.sr_instance_id
2989 AND s.transaction_id= lv_transaction_id(j)
2990 AND s.sr_instance_id= lv_instance_id(j)
2991 AND s.plan_id= arg_plan_id
2992 AND lv_agg_details(j) = 1
2993 AND s.release_status = 1
2994 GROUP BY
2995 SYSDATE,
2996 arg_user_id,
2997 tp.organization_type,
2998 s.organization_id,
2999 s.last_update_login,
3000 SYSDATE,
3001 arg_user_id,
3002 arg_wip_group_id,
3003 s.transaction_id,
3004 resreq.OPERATION_SEQ_NUM,
3005 NULL, --department_id,
3006 LT_OPERATION, --load_type,
3007 NULL, --description,
3008 NULL, --standard_operation_id,
3009 NULL, --minimum_transfer_quantity,
3010 NULL, --count_point_type,
3011 NULL, --backflush_flag,
3012 resreq.operation_sequence_id,
3013 s.sr_instance_id;
3014
3015
3016 --DBMS_OUTPUT.PUT_LINE('OPERATION_RESOURCE');
3017 /* for bug: 2479630, modified the expression that is passed in the column: usage_rate_or_amount,
3018 to divide the resource_hours by cum. qty (which is equal to new_order_qty/cum. yield) and if that is null
3019 than apply the CY on new_order_qty. This logic to use cummulative_qty is added to remove the calc. errors */
3020
3021 /* OPERATION RESOURCES */
3022 FORALL j IN 1..lv_job_count
3023 INSERT INTO MSC_WIP_JOB_DTLS_INTERFACE
3024 ( last_update_date,
3025 last_updated_by,
3026 last_update_login,
3027 creation_date,
3028 created_by,
3029 organization_type,
3030 organization_id,
3031 group_id,
3032 parent_header_id,
3033 operation_seq_num,
3034 alternate_num,
3035 resource_id_old,
3036 resource_id_new,
3037 usage_rate_or_amount,
3038 scheduled_flag,
3039 applied_resource_units, --
3040 applied_resource_value, --
3041 uom_code,
3042 basis_type, --
3043 activity_id, --
3044 autocharge_type, --
3045 standard_rate_flag, --
3046 start_date,
3047 completion_date,
3048 assigned_units,
3049 SUBSTITUTION_TYPE,
3050 LOAD_TYPE,
3051 process_phase,
3052 process_status,
3053 description,
3054 SR_INSTANCE_ID,
3055 operation_seq_id, --Outbound changes for XML
3056 FIRM_FLAG,
3057 resource_hours,
3058 department_id,
3059 -- added the following for dsr
3060 setup_id,
3061 group_sequence_id,
3062 group_sequence_number,
3063 batch_id,
3064 maximum_assigned_units,
3065 parent_seq_num,
3066 resource_seq_num,
3067 schedule_seq_num)
3068 SELECT SYSDATE,
3069 arg_user_id,
3070 s.last_update_login,
3071 SYSDATE,
3072 arg_user_id,
3073 tp.organization_type,
3074 s.organization_id,
3075 arg_wip_group_id,
3076 s.transaction_id,
3077 resreq.OPERATION_SEQ_NUM,
3078 resreq.ALTERNATE_NUM,
3079 resreq.RESOURCE_ID,
3080 resreq.RESOURCE_ID,
3081 /* for OPM orgs (tp.organization_type =2) we don't consider lv_inflate_wip */
3082 decode(resreq.parent_seq_num, null,
3083 (resreq.RESOURCE_HOURS/decode(resreq.basis_type,2,1,
3084 nvl(resreq.cummulative_quantity,
3085 (s.new_order_quantity/nvl(resreq.REVERSE_CUMULATIVE_YIELD,1) )
3086 ) )), resreq.usage_rate), -- RS
3087 decode(nvl(resreq.schedule_flag,1),-23453,1,1,1,resreq.schedule_flag),
3088 NULL,
3089 NULL,
3090 NULL,
3091 resreq.basis_type,
3092 NULL,
3093 NULL,
3094 NULL,
3095 nvl(resreq.firm_start_date,resreq.START_DATE),
3096 nvl(resreq.firm_end_date,resreq.END_DATE),
3097 resreq.ASSIGNED_UNITS,
3098 decode(resreq.parent_seq_num,null,SUBST_CHANGE,SUBST_ADD),
3099 -- SUBST_CHANGE,
3100 LT_RESOURCE,
3101 2,
3102 1,
3103 NULL,
3104 s.sr_instance_id,
3105 resreq.operation_sequence_id, --Outbound changes for XML
3106 NVL(resreq.firm_flag, 0), -- if null, then default to not firm (0)
3107 resreq.resource_hours,
3108 resreq.department_id,
3109 resreq.setup_id,
3110 resreq.group_sequence_id,
3111 resreq.group_sequence_number,
3112 resreq.batch_number,
3113 resreq.maximum_assigned_units,
3114 resreq.parent_seq_num,
3115 resreq.orig_resource_seq_num,
3116 resreq.resource_seq_num
3117 FROM msc_trading_partners tp,
3118 msc_resource_requirements resreq,
3119 msc_parameters param,
3120 msc_supplies s,
3121 msc_department_resources mdr --MN: "C" type , can we just say mdr.plan_id = -1 ??
3122 WHERE tp.sr_tp_id= s.organization_id
3123 AND tp.sr_instance_id= s.sr_instance_id
3124 AND tp.partner_type=3
3125 AND resreq.sr_instance_id= s.sr_instance_id
3126 AND resreq.organization_id= s.organization_id
3127 AND resreq.supply_id = s.transaction_id
3128 AND resreq.plan_id = s.plan_id
3129 AND resreq.parent_id = 2
3130 AND resreq.resource_id <> -1
3131 AND resreq.department_id <> -1
3132 AND param.organization_id = s.organization_id
3133 AND param.sr_instance_id = s.sr_instance_id
3134 AND s.transaction_id= lv_transaction_id(j)
3135 AND s.sr_instance_id= lv_instance_id(j)
3136 AND s.plan_id= arg_plan_id
3137 AND lv_agg_details(j) = 1
3138 AND mdr.plan_id = -1
3139 AND resreq.organization_id =mdr.organization_id
3140 AND resreq.sr_instance_id = mdr.sr_instance_id
3141 AND resreq.resource_id = mdr.resource_id
3142 AND resreq.department_id=mdr.department_id
3143 AND s.release_status = 1;
3144
3145
3146 --DBMS_OUTPUT.PUT_LINE('LOAD COMPONENTS');
3147
3148 /* for bug: 2378484, added code to consider the cum yield in the calc of qty_per_assembly */
3149 /* UPDATE EXISTING COMPONENTS *
3150 | We should set inventory_item_id_new to NULL |
3151 * */
3152 FORALL j IN 1..lv_job_count
3153 INSERT INTO MSC_WIP_JOB_DTLS_INTERFACE
3154 (last_update_date,
3155 last_updated_by,
3156 last_update_login,
3157 creation_date,
3158 created_by,
3159 organization_type,
3160 organization_id,
3161 group_id,
3162 parent_header_id,
3163 operation_seq_num,
3164 inventory_item_id_old,
3165 inventory_item_id_new,
3166 basis_type,
3167 quantity_per_assembly,
3168 component_yield_factor,
3169 department_id,
3170 wip_supply_type,
3171 date_required,
3172 required_quantity,
3173 quantity_issued,
3174 supply_subinventory,
3175 supply_locator_id,
3176 mrp_net_flag,
3177 mps_required_quantity,
3178 mps_date_required,
3179 SUBSTITUTION_TYPE,
3180 LOAD_TYPE,
3181 process_phase,
3182 process_status,
3183 description,
3184 -- operation_seq_id, --Outbound changes for XML
3185 uom_code, --Outbound Changes for XML
3186 SR_INSTANCE_ID)
3187 SELECT SYSDATE,
3188 arg_user_id,
3189 s.last_update_login,
3190 SYSDATE,
3191 arg_user_id,
3192 tp.organization_type,
3193 s.organization_id,
3194 arg_wip_group_id,
3195 s.transaction_id,
3196 nvl(md.op_seq_num,1),
3197 icomp.sr_inventory_item_id,
3198 decode(l_apps_ver,'4', null, '3',null,icomp.sr_inventory_item_id),
3199 -- bugbug Is this the right way to compute basis_type?
3200 decode(md.component_scaling_type,1,NULL,md.component_scaling_type),
3201 TO_NUMBER(NULL), --Quantity_per
3202 -- bugbug Is this the right way to compute component_yield_factor?
3203 md.component_yield_factor,
3204 TO_NUMBER(NULL), --Department_ID
3205 md.wip_supply_type,
3206 md.USING_ASSEMBLY_DEMAND_DATE,
3207 md.USING_REQUIREMENT_QUANTITY,
3208 0,
3209 TO_CHAR(NULL), -- Sub Inventory
3210 TO_NUMBER(NULL), -- Locator ID
3211 1, -- MRP_NET_FLAG
3212 md.USING_REQUIREMENT_QUANTITY,
3213 md.USING_ASSEMBLY_DEMAND_DATE,
3214 SUBST_CHANGE,
3215 LT_COMPONENT,
3216 2,
3217 1,
3218 TO_CHAR(NULL),
3219 -- bugbug Should we check sys_items for uom_code?
3220 s.implement_uom_code,
3221 s.sr_instance_id
3222 FROM msc_trading_partners tp,
3223 msc_system_items icomp, -- bugbug ey, should this be msc_item_id_lid or msi?
3224 msc_demands md,
3225 msc_parameters param,
3226 msc_supplies s
3227 WHERE
3228 tp.sr_tp_id= icomp.organization_id
3229 AND tp.sr_instance_id= icomp.sr_instance_id
3230 AND tp.partner_type=3
3231 AND icomp.inventory_item_id = md.inventory_item_id -- added by ey
3232 AND icomp.sr_instance_id = md.sr_instance_id -- added by ey
3233 AND icomp.organization_id = md.organization_id
3234 AND icomp.plan_id = -1
3235 AND nvl(icomp.wip_supply_type,0) <> 6 -- PHANTOM , in the future, extraction should make sure to filter it out
3236 AND (md.PRIMARY_COMPONENT_ID is null OR md.PRIMARY_COMPONENT_ID = md.inventory_item_id)
3237 AND md.disposition_id= s.transaction_id
3238 AND md.origination_type = 1
3239 AND md.plan_id = arg_plan_id
3240 AND param.organization_id = s.organization_id
3241 AND param.sr_instance_id = s.sr_instance_id
3242 AND s.transaction_id= lv_transaction_id(j)
3243 AND s.sr_instance_id= lv_instance_id(j)
3244 AND s.plan_id= arg_plan_id
3245 AND lv_agg_details(j) = 1
3246 AND s.release_status = 1;
3247
3248 --Loading Co-products/by-products for OPM orgs
3249 FORALL j IN 1..lv_job_count
3250 INSERT INTO MSC_WIP_JOB_DTLS_INTERFACE
3251 (last_update_date,
3252 last_updated_by,
3253 last_update_login,
3254 creation_date,
3255 created_by,
3256 organization_type,
3257 organization_id,
3258 group_id,
3259 parent_header_id,
3260 operation_seq_num,
3261 inventory_item_id_old,
3262 inventory_item_id_new,
3263 quantity_per_assembly,
3264 department_id,
3265 wip_supply_type,
3266 date_required,
3267 required_quantity,
3268 quantity_issued,
3269 supply_subinventory,
3270 supply_locator_id,
3271 mrp_net_flag,
3272 mps_required_quantity,
3273 mps_date_required,
3274 SUBSTITUTION_TYPE,
3275 LOAD_TYPE,
3276 process_phase,
3277 process_status,
3278 description,
3279 uom_code, --Outbound Changes for XML
3280 SR_INSTANCE_ID)
3281 SELECT SYSDATE,
3282 arg_user_id,
3283 s.last_update_login,
3284 SYSDATE,
3285 arg_user_id,
3286 tp.organization_type,
3287 s.organization_id,
3288 arg_wip_group_id,
3289 s.transaction_id,
3290 nvl(co.operation_seq_num,1),
3291 icomp.sr_inventory_item_id,
3292 decode(l_apps_ver,'4',null,'3',null,icomp.sr_inventory_item_id),
3293 TO_NUMBER(NULL), --Quantity_per
3294 TO_NUMBER(NULL), --Department_ID
3295 co.wip_supply_type,
3296 co.NEW_SCHEDULE_DATE,
3297 co.NEW_ORDER_QUANTITY,
3298 0,
3299 TO_CHAR(NULL), -- Sub Inventory
3300 TO_NUMBER(NULL), -- Locator ID
3301 1, -- MRP_NET_FLAG
3302 co.NEW_ORDER_QUANTITY,
3303 co.NEW_SCHEDULE_DATE,
3304 SUBST_CHANGE,
3305 LT_COMPONENT,
3306 2,
3307 1,
3308 TO_CHAR(NULL),
3309 s.implement_uom_code, -- bugbug MN: again is this correct?
3310 s.sr_instance_id
3311 FROM msc_trading_partners tp,
3312 msc_item_id_lid icomp,
3313 msc_supplies co,
3314 msc_parameters param,
3315 msc_supplies s
3316 WHERE tp.sr_tp_id = s.organization_id
3317 AND tp.sr_instance_id = s.sr_instance_id
3318 AND tp.partner_type = 3
3319 AND icomp.inventory_item_id = co.inventory_item_id
3320 AND icomp.sr_instance_id = co.sr_instance_id
3321 AND co.sr_instance_Id = s.sr_instance_Id
3322 AND co.disposition_id = s.transaction_id
3323 AND co.plan_id = s.plan_id
3324 AND co.order_type = 17 --Co-product /by-product
3325 AND param.organization_id = s.organization_id
3326 AND param.sr_instance_id = s.sr_instance_id
3327 AND icomp.inventory_item_id = s.inventory_item_id
3328 AND s.transaction_id = lv_transaction_id(j)
3329 AND s.sr_instance_id = lv_instance_id(j)
3330 AND s.plan_id = arg_plan_id
3331 AND tp.organization_type = 2
3332 AND s.release_status = 1;
3333
3334 --DBMS_OUTPUT.PUT_LINE('LOAD SUBSTITUTE COMPONENTS');
3335 /* SUBSTITUTE EXISTING COMPONENTS */
3336 FORALL j IN 1..lv_job_count
3337 INSERT INTO MSC_WIP_JOB_DTLS_INTERFACE
3338 (last_update_date,
3339 last_updated_by,
3340 last_update_login,
3341 creation_date,
3342 created_by,
3343 organization_type,
3344 organization_id,
3345 group_id,
3346 parent_header_id,
3347 operation_seq_num,
3348 inventory_item_id_old,
3349 inventory_item_id_new,
3350 quantity_per_assembly,
3351 department_id,
3352 wip_supply_type,
3353 date_required,
3354 required_quantity,
3355 quantity_issued,
3356 supply_subinventory,
3357 supply_locator_id,
3358 mrp_net_flag,
3359 mps_required_quantity,
3360 mps_date_required,
3361 SUBSTITUTION_TYPE,
3362 LOAD_TYPE,
3363 process_phase,
3364 process_status,
3365 description,
3366 -- operation_seq_id, --Outbound changes for XML
3367 uom_code, --Outbound Changes for XML
3368 SR_INSTANCE_ID)
3369 SELECT SYSDATE,
3370 arg_user_id,
3371 s.last_update_login,
3372 SYSDATE,
3373 arg_user_id,
3374 tp.organization_type,
3375 s.organization_id,
3376 arg_wip_group_id,
3377 s.transaction_id,
3378 nvl(md.op_seq_num,1),
3379 icomp.sr_inventory_item_id,
3380 sr_item.sr_inventory_item_id,
3381 bsub.usage_quantity,
3382 TO_NUMBER(NULL), --Department_ID
3383 md.wip_supply_type, -- bugbug if null get from msi?
3384 md.USING_ASSEMBLY_DEMAND_DATE,
3385 md.USING_REQUIREMENT_QUANTITY,
3386 0,
3387 TO_CHAR(NULL), -- Sub Inventory
3388 TO_NUMBER(NULL), -- Locator ID
3389 1,
3390 md.USING_REQUIREMENT_QUANTITY,
3391 md.USING_ASSEMBLY_DEMAND_DATE,
3392 SUBST_CHANGE,
3393 LT_COMPONENT,
3394 2,
3395 1,
3396 TO_CHAR(NULL),
3397 -- md.operation_seq_id,
3398 s.implement_uom_code, -- bugbug again should fall back to sys_items?
3399 s.sr_instance_id
3400 FROM msc_trading_partners tp,
3401 msc_item_id_lid sr_item,
3402 msc_bom_components subcomp,
3403 msc_component_substitutes bsub,
3404 /* msc_bom_components bcomp, */
3405 msc_item_id_lid icomp,
3406 msc_demands md,
3407 msc_supplies s
3408
3409 WHERE tp.sr_tp_id= s.organization_id
3410 AND tp.sr_instance_id= s.sr_instance_id
3411 AND tp.partner_type=3
3412 AND sr_item.inventory_item_id= md.inventory_item_id
3413 AND sr_item.sr_instance_id= md.sr_instance_id
3414 AND subcomp.plan_id = -1
3415 AND subcomp.bill_sequence_id = bsub.bill_sequence_id
3416 AND subcomp.COMPONENT_SEQUENCE_ID = bsub.COMPONENT_SEQUENCE_ID
3417 AND bsub.substitute_item_id = md.inventory_item_id
3418 AND bsub.organization_id = md.organization_id
3419 AND bsub.plan_id = -1
3420 AND subcomp.inventory_item_id = md.PRIMARY_COMPONENT_ID
3421 AND subcomp.using_assembly_id=md.using_assembly_item_id
3422 AND subcomp.sr_instance_id=s.sr_instance_Id
3423 AND subcomp.sr_instance_id=bsub.sr_instance_Id
3424 AND s.organization_id= md.organization_id
3425 AND md.sr_instance_Id= s.sr_instance_Id
3426 AND md.disposition_id= s.transaction_id
3427 AND md.plan_id= s.plan_id
3428 AND md.origination_type = 1
3429 AND s.transaction_id= lv_transaction_id(j)
3430 AND s.sr_instance_id= lv_instance_id(j)
3431 AND s.plan_id= arg_plan_id
3432 AND lv_agg_details(j) = 1
3433 AND md.PRIMARY_COMPONENT_ID is not null
3434 AND icomp.inventory_item_id= md.PRIMARY_COMPONENT_ID
3435 AND icomp.sr_instance_id= md.sr_instance_id
3436 AND rownum=1
3437 AND s.release_status = 1;
3438
3439
3440
3441 -- RESOURCE USAGE
3442 FORALL j IN 1..lv_job_count
3443 INSERT INTO MSC_WIP_JOB_DTLS_INTERFACE
3444 ( last_update_date,
3445 last_updated_by,
3446 last_update_login,
3447 creation_date,
3448 created_by,
3449 organization_type,
3450 organization_id,
3451 group_id,
3452 parent_header_id,
3453 operation_seq_num,
3454 alternate_num,
3455 resource_id_old,
3456 resource_id_new,
3457 usage_rate_or_amount,
3458 scheduled_flag,
3459 applied_resource_units, --
3460 applied_resource_value, --
3461 uom_code,
3462 basis_type, --
3463 activity_id, --
3464 autocharge_type, --
3465 standard_rate_flag, --
3466 start_date,
3467 completion_date,
3468 assigned_units,
3469 SUBSTITUTION_TYPE,
3470 LOAD_TYPE,
3471 process_phase,
3472 process_status,
3473 description,
3474 SR_INSTANCE_ID,
3475 operation_seq_id, --Outbound changes for XML
3476 resource_seq_num,
3477 schedule_seq_num,
3478 FIRM_FLAG,
3479 department_id,
3480 resource_hours,
3481 parent_seq_num)
3482 SELECT SYSDATE,
3483 arg_user_id,
3484 s.last_update_login,
3485 SYSDATE,
3486 arg_user_id,
3487 tp.organization_type,
3488 s.organization_id,
3489 arg_wip_group_id,
3490 s.transaction_id,
3491 resreq.OPERATION_SEQ_NUM,
3492 resreq.ALTERNATE_NUM,
3493 resreq.RESOURCE_ID,
3494 resreq.RESOURCE_ID,
3495 resreq.usage_rate,
3496 decode(nvl(resreq.schedule_flag,1),-23453,1,1,1,resreq.schedule_flag),
3497 NULL,
3498 NULL,
3499 v_hour_uom,
3500 resreq.basis_type,
3501 NULL,
3502 NULL,
3503 NULL,
3504 nvl(resreq.firm_start_date,resreq.START_DATE),
3505 nvl(resreq.firm_end_date,resreq.END_DATE),
3506 resreq.ASSIGNED_UNITS,
3507 SUBST_CHANGE,
3508 LT_RESOURCE_USAGE,
3509 2,
3510 1,
3511 NULL,
3512 s.sr_instance_id,
3513 resreq.operation_sequence_id, --Outbound changes for XML
3514 resreq.orig_resource_seq_num,
3515 resreq.resource_seq_num,
3516 decode(nvl(resreq.firm_flag,0),0,2,1),
3517 resreq.department_id,
3518 resreq.resource_hours,
3519 resreq.parent_seq_num
3520 FROM msc_trading_partners tp,
3521 msc_resource_requirements resreq,
3522 msc_parameters param,
3523 msc_supplies s,
3524 msc_department_resources mdr
3525 WHERE tp.sr_tp_id= s.organization_id
3526 AND tp.sr_instance_id= s.sr_instance_id
3527 AND tp.partner_type=3
3528 AND resreq.sr_instance_id= s.sr_instance_id
3529 AND resreq.organization_id= s.organization_id
3530 AND resreq.supply_id = s.transaction_id
3531 AND resreq.plan_id = s.plan_id
3532 AND resreq.parent_id = 1
3533 AND resreq.resource_id <> -1
3534 AND resreq.department_id <> -1
3535 AND param.organization_id = s.organization_id
3536 AND param.sr_instance_id = s.sr_instance_id
3537 AND s.transaction_id= lv_transaction_id(j)
3538 AND s.sr_instance_id= lv_instance_id(j)
3539 AND s.plan_id= arg_plan_id
3540 AND lv_agg_details(j) = 1
3541 AND mdr.plan_id = -1
3542 AND resreq.organization_id =mdr.organization_id
3543 AND resreq.sr_instance_id = mdr.sr_instance_id
3544 AND resreq.resource_id = mdr.resource_id
3545 AND resreq.department_id=mdr.department_id
3546 AND s.release_status = 1;
3547 --AND tp.organization_type = 2;
3548
3549 -- dsr starts here
3550 -- print_debug_info('OPERATION RESOURCE_INSTANCES');
3551 -- OPERATION RESOURCE_INSTANCES
3552
3553 FORALL j IN 1..lv_job_count
3554 INSERT INTO MSC_WIP_JOB_DTLS_INTERFACE
3555 ( last_update_date,
3556 last_updated_by,
3557 last_update_login,
3558 creation_date,
3559 created_by,
3560 organization_type,
3561 organization_id,
3562 group_id,
3563 parent_header_id,
3564 operation_seq_num,
3565 resource_id_old,
3566 resource_id_new,
3567 RESOURCE_INSTANCE_ID,
3568 start_date,
3569 completion_date,
3570 SUBSTITUTION_TYPE,
3571 LOAD_TYPE,
3572 process_phase,
3573 process_status,
3574 SR_INSTANCE_ID,
3575 operation_seq_id,
3576 resource_hours,
3577 department_id,
3578 SERIAL_NUMBER,
3579 group_sequence_id,
3580 group_sequence_number,
3581 batch_id,
3582 resource_seq_num,
3583 schedule_seq_num,
3584 assigned_units,
3585 parent_seq_num
3586 )
3587 SELECT
3588 SYSDATE,
3589 arg_user_id,
3590 s.last_update_login,
3591 SYSDATE,
3592 arg_user_id,
3593 tp.organization_type,
3594 s.organization_id,
3595 arg_wip_group_id,
3596 s.transaction_id,
3597 resreq.OPERATION_SEQ_NUM,
3598 resreq.resource_id,
3599 resreq.resource_id,
3600 dep_res_inst.RES_INSTANCE_ID,
3601 nvl(resreq.firm_start_date,res_instreq.START_DATE),
3602 nvl(resreq.firm_end_date,res_instreq.END_DATE),
3603 SUBST_ADD,
3604 LT_RESOURCE_INSTANCE,
3605 2,
3606 1,
3607 s.sr_instance_id,
3608 resreq.operation_sequence_id,
3609 resreq.resource_hours,
3610 resreq.department_id,
3611 dep_res_inst.serial_number,
3612 resreq.group_sequence_id,
3613 resreq.group_sequence_number,
3614 res_instreq.batch_number,
3615 nvl(resreq.orig_resource_seq_num,resreq.resource_seq_num),
3616 resreq.resource_seq_num,
3617 1 ,
3618 resreq.parent_seq_num
3619 FROM
3620 msc_trading_partners tp,
3621 msc_resource_requirements resreq,
3622 msc_resource_instance_reqs res_instreq,
3623 msc_dept_res_instances dep_res_inst,
3624 msc_supplies s
3625 WHERE
3626 tp.sr_tp_id=s.organization_id
3627 AND tp.sr_instance_id= s.sr_instance_id
3628 AND tp.partner_type=3
3629 AND resreq.sr_instance_id= s.sr_instance_id
3630 AND resreq.organization_id= s.organization_id
3631 AND resreq.supply_id = s.transaction_id
3632 AND resreq.plan_id = s.plan_id
3633 AND resreq.resource_seq_num = res_instreq.resource_seq_num
3634 AND resreq.operation_seq_num = res_instreq.operation_seq_num
3635 AND resreq.resource_id = res_instreq.resource_id
3636 AND resreq.supply_id = res_instreq.supply_id
3637 AND resreq.sr_instance_id = res_instreq.sr_instance_id
3638 AND resreq.plan_id = res_instreq.plan_id
3639 AND resreq.parent_id = res_instreq.parent_id --rawasthi
3640 AND resreq.start_date = res_instreq.start_date
3641 AND resreq.parent_id = 2
3642 AND resreq.resource_id <> -1
3643 AND resreq.department_id <> -1
3644 AND dep_res_inst.plan_id = -1 --MN: Again, Can we do this?
3645 AND res_instreq.sr_instance_id = dep_res_inst.sr_instance_id
3646 AND res_instreq.department_id = dep_res_inst.department_id
3647 AND res_instreq.resource_id = dep_res_inst.resource_id
3648 AND res_instreq.serial_number = dep_res_inst.serial_number
3649 AND res_instreq.res_instance_id = dep_res_inst.res_instance_id
3650 AND s.transaction_id= lv_transaction_id(j)
3651 AND s.sr_instance_id= lv_instance_id(j)
3652 AND s.plan_id= arg_plan_id
3653 AND lv_agg_details(j) = 1
3654 AND s.release_status = 1;
3655
3656
3657
3658 --print_debug_info('load_wip_discrete_jobs# rows inserted into MSC_WIP_JOB_DTLS_INTERFACE = ' || SQL%ROWCOUNT);
3659 -- RESOURCE INSTANCE USAGES
3660 --print_debug_info('RESOURCE INSTANCE USAGES ');
3661
3662 FORALL j IN 1..lv_job_count
3663 INSERT INTO MSC_WIP_JOB_DTLS_INTERFACE
3664 ( last_update_date,
3665 last_updated_by,
3666 last_update_login,
3667 creation_date,
3668 created_by,
3669 organization_type,
3670 organization_id,
3671 group_id,
3672 parent_header_id,
3673 operation_seq_num,
3674 resource_id_old,
3675 resource_id_new,
3676 RESOURCE_INSTANCE_ID,
3677 start_date,
3678 completion_date,
3679 SUBSTITUTION_TYPE,
3680 LOAD_TYPE,
3681 process_phase,
3682 process_status,
3683 SR_INSTANCE_ID,
3684 operation_seq_id,
3685 FIRM_FLAG,
3686 resource_hours,
3687 department_id,
3688 serial_number,
3689 resource_seq_num,
3690 schedule_seq_num,
3691 assigned_units,
3692 parent_seq_num
3693 )
3694 SELECT
3695 SYSDATE,
3696 arg_user_id,
3697 s.last_update_login,
3698 SYSDATE,
3699 arg_user_id,
3700 tp.organization_type,
3701 s.organization_id,
3702 arg_wip_group_id,
3703 s.transaction_id,
3704 resreq.OPERATION_SEQ_NUM,
3705 resreq.RESOURCE_ID,
3706 resreq.RESOURCE_ID,
3707 res_instreq.RES_INSTANCE_ID,
3708 nvl(resreq.firm_start_date,res_instreq.START_DATE),
3709 nvl(resreq.firm_end_date,res_instreq.END_DATE),
3710 SUBST_ADD,
3711 LT_RESOURCE_INST_USAGE,
3712 2,
3713 1,
3714 s.sr_instance_id,
3715 resreq.operation_sequence_id,
3716 resreq.firm_flag,
3717 res_instreq.resource_instance_hours,
3718 resreq.department_id,
3719 res_instreq.serial_number,
3720 resreq.orig_resource_seq_num,
3721 resreq.resource_seq_num,
3722 1 ,
3723 resreq.parent_seq_num
3724 FROM
3725 msc_trading_partners tp,
3726 msc_resource_requirements resreq,
3727 msc_resource_instance_reqs res_instreq,
3728 msc_supplies s
3729 WHERE
3730 tp.sr_tp_id=s.organization_id
3731 AND tp.sr_instance_id= s.sr_instance_id
3732 AND tp.partner_type=3
3733 AND resreq.sr_instance_id= s.sr_instance_id
3734 AND resreq.organization_id= s.organization_id
3735 AND resreq.supply_id = s.transaction_id
3736 AND resreq.plan_id = s.plan_id
3737 AND resreq.resource_seq_num = res_instreq.resource_seq_num
3738 AND resreq.operation_seq_num = res_instreq.operation_seq_num
3739 AND resreq.resource_id = res_instreq.resource_id
3740 AND resreq.supply_id = res_instreq.supply_id
3741 AND resreq.sr_instance_id = res_instreq.sr_instance_id
3742 AND resreq.plan_id = res_instreq.plan_id
3743 AND resreq.parent_id = res_instreq.parent_id --rawasthi
3744 AND resreq.start_date = res_instreq.start_date
3745 AND resreq.parent_id = 1
3746 AND resreq.resource_id <> -1
3747 AND resreq.department_id <> -1
3748 AND res_instreq.plan_id = s.plan_id
3749 AND s.transaction_id= lv_transaction_id(j)
3750 AND s.sr_instance_id= lv_instance_id(j)
3751 AND s.plan_id= arg_plan_id
3752 AND lv_agg_details(j) = 1
3753 AND s.release_status = 1;
3754 -- AND tp.organization_type = 2
3755
3756 -- bugbug Do we need to add the charges handling code?
3757 RETURN lv_loaded_jobs;
3758
3759 END load_wip_discr_jobs_ps;
3760
3761
3762
3763 FUNCTION reschedule_wip_discr_jobs_ps
3764 ( arg_plan_id IN NUMBER
3765 , arg_log_org_id IN NUMBER
3766 , arg_org_instance IN NUMBER
3767 , arg_owning_org_id IN NUMBER
3768 , arg_owning_instance IN NUMBER
3769 , arg_user_id IN NUMBER
3770 , arg_wip_group_id IN NUMBER
3771 , arg_transaction_id IN NUMBER
3772 , l_apps_ver IN VARCHAR2
3773 )RETURN NUMBER
3774 IS
3775
3776 lv_resched_jobs NUMBER;
3777
3778
3779 TYPE NumTab IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
3780 --TYPE RIDTab IS TABLE OF ROWID INDEX BY BINARY_INTEGER;
3781
3782 lv_transaction_id NumTab;
3783 lv_instance_id NumTab;
3784 lv_job_count NUMBER;
3785 lv_plan_id NumTab;
3786 lv_org_id Numtab;
3787 lv_agg_details NumTab;
3788
3789 BEGIN
3790 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'In reschedule_wip_discr_jobs_ps');
3791 -- we release the discrete job, only if it doesn't use aggregate resources
3792 -- bug 1252659 fix, replace rowid by
3793 -- (transaction_id,sr_instance_id,plan_id)
3794
3795
3796 SELECT s.transaction_id,
3797 s.sr_instance_id,
3798 s.organization_id,
3799 s.plan_id
3800 BULK COLLECT
3801 INTO lv_transaction_id,
3802 lv_instance_id,
3803 lv_org_id,
3804 lv_plan_id
3805 FROM msc_supplies s,
3806 msc_plan_organizations_v orgs,
3807 msc_plans mp
3808 WHERE s.release_errors is NULL
3809 AND s.implement_quantity > 0
3810 AND s.organization_id = orgs.planned_organization
3811 AND s.sr_instance_id = orgs.sr_instance_id
3812 AND s.plan_id = arg_plan_id
3813 AND orgs.plan_id = arg_plan_id
3814 AND orgs.organization_id = arg_owning_org_id
3815 AND orgs.owning_sr_instance = arg_owning_instance
3816 AND ( orgs.planned_organization= arg_log_org_id
3817 OR arg_log_org_id = arg_owning_org_id )
3818 -- orgs.planned_organization =
3819 -- decode( arg_log_org_id,
3820 -- arg_owning_org_id, orgs.planned_organization,
3821 -- arg_log_org_id)
3822 AND orgs.sr_instance_id = arg_org_instance
3823 AND s.load_type = WIP_DIS_MASS_RESCHEDULE
3824 AND mp.plan_id = s.plan_id
3825 AND s.release_status = 1;
3826
3827 -- ey, no need to pass 'arg_mode is null', this condition and this parameter can be removed. - MN: done
3828
3829
3830
3831
3832
3833
3834 lv_job_count:= SQL%ROWCOUNT;
3835
3836 -- -----------------------------------------------------------------------
3837 -- Perform the wip discrete job mass load
3838 -- -----------------------------------------------------------------------
3839 -- Due to we only give PLANNED components, BILL_RTG_EXPLOSION_FLAG
3840 -- is set to 'Y'.
3841
3842 --DBMS_OUTPUT.PUT_LINE('LOAD_JOB');
3843
3844
3845
3846
3847 FOR k in 1..lv_job_count
3848 Loop
3849 Begin
3850 SELECT 2
3851 Into lv_agg_details(k)
3852 FROM msc_department_resources deptres,
3853 msc_resource_requirements resreq
3854 WHERE resreq.sr_instance_id= lv_instance_id(k)
3855 AND resreq.supply_id = lv_transaction_id(k)
3856 AND resreq.organization_id= lv_org_id(k)
3857 AND resreq.plan_id = lv_plan_id(k)
3858 AND resreq.parent_id = 2
3859 AND deptres.plan_id = -1 --MN: Is this correct?????
3860 AND deptres.sr_instance_id= resreq.sr_instance_id
3861 AND deptres.resource_id= resreq.resource_id
3862 AND deptres.department_id= resreq.department_id
3863 AND deptres.organization_id= resreq.organization_id
3864 AND deptres.aggregate_resource_flag= 1
3865 AND rownum=1;
3866 Exception
3867 When no_data_found
3868 then
3869 lv_agg_details(k) := 1;
3870 End;
3871
3872 End Loop;
3873
3874 -- -----------------------------------------------------------------------
3875 -- Substitute component for reschedule order at the header level at WJSI
3876 -- -----------------------------------------------------------------------
3877 FORALL j IN 1..lv_job_count
3878 INSERT INTO msc_wip_job_schedule_interface
3879 (last_update_date,
3880 last_updated_by,
3881 last_update_login,
3882 creation_date,
3883 created_by,
3884 group_id,
3885 source_code,
3886 organization_id,
3887 organization_type,
3888 status_type,
3889 load_type,
3890 maintenance_object_source,
3891 last_unit_completion_date,
3892 first_unit_start_date,
3893 bom_revision_date,
3894 routing_revision_date,
3895 job_name,
3896 firm_planned_flag,
3897 start_quantity, -- bug 1229891: net_quantity
3898 net_quantity,
3899 wip_entity_id,
3900 demand_class,
3901 project_id,
3902 task_id,
3903 schedule_group_id,
3904 build_sequence,
3905 line_id,
3906 alternate_bom_designator,
3907 alternate_routing_designator,
3908 end_item_unit_number,
3909 process_phase,
3910 process_status,
3911 BILL_RTG_EXPLOSION_FLAG,
3912 HEADER_ID,
3913 SR_INSTANCE_ID,
3914 uom_code, --Outbound Changes for XML
3915 PRIMARY_ITEM_ID,
3916 source_line_id, --Outbound Changes for XML
3917 schedule_priority, --dsr
3918 requested_completion_date --dsr
3919 )
3920 SELECT distinct SYSDATE,
3921 arg_user_id,
3922 s.last_update_login,
3923 decode(tp.organization_type,2,s.creation_date,SYSDATE),
3924 arg_user_id,
3925 arg_wip_group_id*-1,
3926 'MSC',
3927 s.organization_id,
3928 tp.organization_type,
3929 null, --MN: Rongming's pseudo code
3930 258,
3931 s.maintenance_object_source,
3932 s.implement_date,
3933 s.new_wip_Start_Date,
3934 NULL,
3935 NULL,
3936 s.implement_job_name,
3937 s.firm_planned_type,
3938 DECODE( tp.organization_type,
3939 1, DECODE(s.new_order_quantity,
3940 s.implement_quantity, TO_NUMBER(NULL),
3941 ((s.new_order_quantity + NVL(s.qty_completed, 0) +
3942 NVL(s.qty_scrapped, 0)) -
3943 (s.new_order_quantity - s.implement_quantity))),
3944 NULL),
3945 DECODE( tp.organization_type,
3946 2, DECODE(s.new_order_quantity,
3947 s.implement_quantity, TO_NUMBER(NULL),
3948 ((s.new_order_quantity + NVL(s.qty_completed, 0) +
3949 NVL(s.qty_scrapped, 0)) -
3950 (s.new_order_quantity - s.implement_quantity))),
3951 (s.implement_quantity + NVL(s.qty_completed, 0) + NVL(s.qty_scrapped, 0))),
3952 s.disposition_id,
3953 s.implement_demand_class,
3954 s.implement_project_id,
3955 s.implement_task_id,
3956 s.implement_schedule_group_id,
3957 s.implement_build_sequence,
3958 s.implement_line_id,
3959 s.implement_alternate_bom,
3960 s.implement_alternate_routing,
3961 s.implement_unit_number,
3962 2,
3963 1,
3964 'Y',
3965 s.transaction_id,
3966 s.sr_instance_id,
3967 s.implement_uom_code, -- bugbug MN: NOTE: this can not be null
3968 item_lid.sr_inventory_item_id, -- msi.sr_inventory_item_id, -- ey, if you don't flush msc_system_items, you need to somehow pass the source_inventory_item_id to here MN: use msc_iten_id_lid
3969 s.transaction_id, --Outbound Changes for XML
3970 s.schedule_priority, --dsr
3971 s.requested_completion_date -- dsr
3972 FROM msc_trading_partners tp,
3973 msc_parameters param,
3974 msc_supplies s,
3975 msc_item_id_lid item_lid,
3976 msc_plan_organizations_v orgs,
3977 msc_demands md
3978 WHERE tp.sr_tp_id= s.organization_id --MN: again, is this correct ? -- ey, should be s.organization_id -- MN: done
3979 AND item_lid.sr_instance_id = s.sr_instance_id --MN:: added
3980 AND item_lid.inventory_item_id = s.inventory_item_id
3981 AND tp.sr_instance_id= s.sr_instance_id --MN: again, is this correct ? -- ey, should be s.sr_instance_id -- MN:done
3982 AND tp.partner_type=3
3983 AND param.organization_id = s.organization_id --MN: again, is this correct ? -- ey, s.organization_id -- MN: done
3984 AND param.sr_instance_id = s.sr_instance_id --MN: again, is this correct ? -- ey, s.sr_instance_id --MN: done
3985 AND s.organization_id = orgs.planned_organization
3986 AND s.sr_instance_id = orgs.sr_instance_id
3987 AND s.plan_id = orgs.plan_id
3988 AND orgs.organization_id = arg_owning_org_id
3989 AND orgs.owning_sr_instance = arg_owning_instance
3990 AND orgs.plan_id = arg_plan_id
3991 AND orgs.planned_organization = decode(arg_log_org_id,
3992 arg_owning_org_id, orgs.planned_organization,
3993 arg_log_org_id)
3994 AND orgs.sr_instance_id = arg_org_instance
3995 AND (s.load_type = WIP_DIS_MASS_RESCHEDULE)
3996 -- ey, remove arg_mode is null -- MN: done
3997 AND s.transaction_id= lv_transaction_id(j)
3998 AND s.sr_instance_id= lv_instance_id(j)
3999 AND s.release_status = 1
4000 AND s.order_type=70
4001 AND s.maintenance_object_source = 2
4002 AND md.sr_instance_Id= s.sr_instance_Id
4003 AND md.disposition_id= s.transaction_id
4004 AND md.organization_id= s.organization_id
4005 AND md.plan_id= s.plan_id
4006 AND md.primary_component_id <> md.inventory_item_id ;
4007
4008 IF SQL%ROWCOUNT > 0
4009 THEN
4010 lv_resched_cmro_alt_jobs := SQL%ROWCOUNT;
4011
4012 ELSE
4013 lv_resched_cmro_alt_jobs := 0;
4014
4015 END IF;
4016 -- -----------------------------------------------------------------------
4017 -- Reschedule orders for WJSI CMRO Orders without altenate component at Header level
4018 -- -----------------------------------------------------------------------
4019 FORALL j IN 1..lv_job_count
4020 INSERT INTO msc_wip_job_schedule_interface
4021 (last_update_date,
4022 last_updated_by,
4023 last_update_login,
4024 creation_date,
4025 created_by,
4026 group_id,
4027 source_code,
4028 organization_id,
4029 organization_type,
4030 status_type,
4031 load_type,
4032 maintenance_object_source,
4033 last_unit_completion_date,
4034 first_unit_start_date,
4035 bom_revision_date,
4036 routing_revision_date,
4037 job_name,
4038 firm_planned_flag,
4039 start_quantity, -- bug 1229891: net_quantity
4040 net_quantity,
4041 wip_entity_id,
4042 demand_class,
4043 project_id,
4044 task_id,
4045 schedule_group_id,
4046 build_sequence,
4047 line_id,
4048 alternate_bom_designator,
4049 alternate_routing_designator,
4050 end_item_unit_number,
4051 process_phase,
4052 process_status,
4053 BILL_RTG_EXPLOSION_FLAG,
4054 HEADER_ID,
4055 SR_INSTANCE_ID,
4056 uom_code, --Outbound Changes for XML
4057 PRIMARY_ITEM_ID,
4058 source_line_id, --Outbound Changes for XML
4059 schedule_priority, --dsr
4060 requested_completion_date --dsr
4061 )
4062 SELECT SYSDATE,
4063 arg_user_id,
4064 s.last_update_login,
4065 decode(tp.organization_type,2,s.creation_date,SYSDATE),
4066 arg_user_id,
4067 decode(s.order_type,70,arg_wip_group_id*-1,arg_wip_group_id), --for cmro/eam --arg_wip_group_id,
4068 'MSC',
4069 s.organization_id, --MN: is this correct? what is owning_org_id? -- ey, I think this one should be 's.organization_id' - done
4070 tp.organization_type,
4071 null, --MN: Rongming's pseudo code
4072 3,
4073 s.maintenance_object_source,
4074 s.implement_date,
4075 s.new_wip_Start_Date,
4076 NULL,
4077 NULL,
4078 s.implement_job_name,
4079 s.firm_planned_type,
4080 DECODE( tp.organization_type,
4081 1, DECODE(s.new_order_quantity,
4082 s.implement_quantity, TO_NUMBER(NULL),
4083 ((s.new_order_quantity + NVL(s.qty_completed, 0) +
4084 NVL(s.qty_scrapped, 0)) -
4085 (s.new_order_quantity - s.implement_quantity))),
4086 NULL),
4087 DECODE(s.new_order_quantity,
4088 s.implement_quantity, TO_NUMBER(NULL),
4089 ((s.new_order_quantity + NVL(s.qty_completed, 0) +
4090 NVL(s.qty_scrapped, 0)) -
4091 (s.new_order_quantity - s.implement_quantity))), --MN: NOTE:Bug 11834045 - MRP NET QTY GETS UPDATED WITH PARTIAL WO QTY
4092 s.disposition_id,
4093 s.implement_demand_class,
4094 s.implement_project_id,
4095 s.implement_task_id,
4096 s.implement_schedule_group_id,
4097 s.implement_build_sequence,
4098 s.implement_line_id,
4099 s.implement_alternate_bom,
4100 s.implement_alternate_routing,
4101 s.implement_unit_number,
4102 2,
4103 1,
4104 'N',
4105 s.transaction_id,
4106 s.sr_instance_id,
4107 s.implement_uom_code, -- bugbug MN: NOTE: this can not be null
4108 item_lid.sr_inventory_item_id, -- msi.sr_inventory_item_id, -- ey, if you don't flush msc_system_items, you need to somehow pass the source_inventory_item_id to here MN: use msc_iten_id_lid
4109 s.transaction_id, --Outbound Changes for XML
4110 s.schedule_priority, --dsr
4111 s.requested_completion_date -- dsr
4112 FROM msc_trading_partners tp,
4113 msc_parameters param,
4114 msc_supplies s,
4115 msc_item_id_lid item_lid,
4116 msc_plan_organizations_v orgs
4117 WHERE tp.sr_tp_id= s.organization_id --MN: again, is this correct ? -- ey, should be s.organization_id -- MN: done
4118 AND item_lid.sr_instance_id = s.sr_instance_id --MN:: added
4119 AND item_lid.inventory_item_id = s.inventory_item_id
4120 AND tp.sr_instance_id= s.sr_instance_id --MN: again, is this correct ? -- ey, should be s.sr_instance_id -- MN:done
4121 AND tp.partner_type=3
4122 AND param.organization_id = s.organization_id --MN: again, is this correct ? -- ey, s.organization_id -- MN: done
4123 AND param.sr_instance_id = s.sr_instance_id --MN: again, is this correct ? -- ey, s.sr_instance_id --MN: done
4124 AND s.organization_id = orgs.planned_organization
4125 AND s.sr_instance_id = orgs.sr_instance_id
4126 AND s.plan_id = orgs.plan_id
4127 AND orgs.organization_id = arg_owning_org_id
4128 AND orgs.owning_sr_instance = arg_owning_instance
4129 AND orgs.plan_id = arg_plan_id
4130 AND orgs.planned_organization = decode(arg_log_org_id,
4131 arg_owning_org_id, orgs.planned_organization,
4132 arg_log_org_id)
4133 AND orgs.sr_instance_id = arg_org_instance
4134 AND (s.load_type = WIP_DIS_MASS_RESCHEDULE)
4135 -- ey, remove arg_mode is null -- MN: done
4136 AND s.transaction_id= lv_transaction_id(j)
4137 AND s.sr_instance_id= lv_instance_id(j)
4138 AND s.release_status = 1
4139 AND s.order_type = 70
4140 AND s.maintenance_object_source = 2
4141 AND not exists (select * from msc_demands md where md.plan_id= s.plan_id AND md.sr_instance_Id= s.sr_instance_Id AND md.disposition_id= s.transaction_id
4142 AND md.organization_id= s.organization_id
4143 AND md.primary_component_id <> md.inventory_item_id) ;
4144
4145 IF SQL%ROWCOUNT > 0
4146 THEN
4147 lv_resched_cmro_jobs := SQL%ROWCOUNT;
4148 ELSE
4149 lv_resched_cmro_jobs := 0;
4150
4151 END IF;
4152
4153 -- -----------------------------------------------------------------------
4154 -- Reschedule orders for WJSI EAM/Discrete
4155 -- -----------------------------------------------------------------------
4156 FORALL j IN 1..lv_job_count
4157 INSERT INTO msc_wip_job_schedule_interface
4158 (last_update_date,
4159 last_updated_by,
4160 last_update_login,
4161 creation_date,
4162 created_by,
4163 group_id,
4164 source_code,
4165 organization_id,
4166 organization_type,
4167 status_type,
4168 load_type,
4169 maintenance_object_source,
4170 last_unit_completion_date,
4171 first_unit_start_date,
4172 bom_revision_date,
4173 routing_revision_date,
4174 job_name,
4175 firm_planned_flag,
4176 start_quantity, -- bug 1229891: net_quantity
4177 net_quantity,
4178 wip_entity_id,
4179 demand_class,
4180 project_id,
4181 task_id,
4182 schedule_group_id,
4183 build_sequence,
4184 line_id,
4185 alternate_bom_designator,
4186 alternate_routing_designator,
4187 end_item_unit_number,
4188 process_phase,
4189 process_status,
4190 BILL_RTG_EXPLOSION_FLAG,
4191 HEADER_ID,
4192 SR_INSTANCE_ID,
4193 uom_code, --Outbound Changes for XML
4194 PRIMARY_ITEM_ID,
4195 source_line_id, --Outbound Changes for XML
4196 schedule_priority, --dsr
4197 requested_completion_date --dsr
4198 )
4199 SELECT SYSDATE,
4200 arg_user_id,
4201 s.last_update_login,
4202 decode(tp.organization_type,2,s.creation_date,SYSDATE),
4203 arg_user_id,
4204 decode(s.order_type,70,arg_wip_group_id*-1,arg_wip_group_id), --for cmro/eam --arg_wip_group_id,
4205 'MSC',
4206 s.organization_id, --MN: is this correct? what is owning_org_id? -- ey, I think this one should be 's.organization_id' - done
4207 tp.organization_type,
4208 null, --MN: Rongming's pseudo code
4209 3,
4210 s.maintenance_object_source,
4211 s.implement_date,
4212 s.new_wip_Start_Date,
4213 NULL,
4214 NULL,
4215 s.implement_job_name,
4216 s.firm_planned_type,
4217 DECODE( tp.organization_type,
4218 1, DECODE(s.new_order_quantity,
4219 s.implement_quantity, TO_NUMBER(NULL),
4220 ((s.new_order_quantity + NVL(s.qty_completed, 0) +
4221 NVL(s.qty_scrapped, 0)) -
4222 (s.new_order_quantity - s.implement_quantity))),
4223 NULL),
4224 DECODE(s.new_order_quantity,
4225 s.implement_quantity, TO_NUMBER(NULL),
4226 ((s.new_order_quantity + NVL(s.qty_completed, 0) +
4227 NVL(s.qty_scrapped, 0)) -
4228 (s.new_order_quantity - s.implement_quantity))), --MN: NOTE:Bug 11834045 - MRP NET QTY GETS UPDATED WITH PARTIAL WO QTY
4229 s.disposition_id,
4230 s.implement_demand_class,
4231 s.implement_project_id,
4232 s.implement_task_id,
4233 s.implement_schedule_group_id,
4234 s.implement_build_sequence,
4235 s.implement_line_id,
4236 s.implement_alternate_bom,
4237 s.implement_alternate_routing,
4238 s.implement_unit_number,
4239 2,
4240 1,
4241 'Y',
4242 s.transaction_id,
4243 s.sr_instance_id,
4244 s.implement_uom_code, -- bugbug MN: NOTE: this can not be null
4245 item_lid.sr_inventory_item_id, -- msi.sr_inventory_item_id, -- ey, if you don't flush msc_system_items, you need to somehow pass the source_inventory_item_id to here MN: use msc_iten_id_lid
4246 s.transaction_id, --Outbound Changes for XML
4247 s.schedule_priority, --dsr
4248 s.requested_completion_date -- dsr
4249 FROM msc_trading_partners tp,
4250 msc_parameters param,
4251 msc_supplies s,
4252 msc_item_id_lid item_lid,
4253 msc_plan_organizations_v orgs
4254 WHERE tp.sr_tp_id= s.organization_id --MN: again, is this correct ? -- ey, should be s.organization_id -- MN: done
4255 AND item_lid.sr_instance_id = s.sr_instance_id --MN:: added
4256 AND item_lid.inventory_item_id = s.inventory_item_id
4257 AND tp.sr_instance_id= s.sr_instance_id --MN: again, is this correct ? -- ey, should be s.sr_instance_id -- MN:done
4258 AND tp.partner_type=3
4259 AND param.organization_id = s.organization_id --MN: again, is this correct ? -- ey, s.organization_id -- MN: done
4260 AND param.sr_instance_id = s.sr_instance_id --MN: again, is this correct ? -- ey, s.sr_instance_id --MN: done
4261 AND s.organization_id = orgs.planned_organization
4262 AND s.sr_instance_id = orgs.sr_instance_id
4263 AND s.plan_id = orgs.plan_id
4264 AND orgs.organization_id = arg_owning_org_id
4265 AND orgs.owning_sr_instance = arg_owning_instance
4266 AND orgs.plan_id = arg_plan_id
4267 AND orgs.planned_organization = decode(arg_log_org_id,
4268 arg_owning_org_id, orgs.planned_organization,
4269 arg_log_org_id)
4270 AND orgs.sr_instance_id = arg_org_instance
4271 AND (s.load_type = WIP_DIS_MASS_RESCHEDULE)
4272 -- ey, remove arg_mode is null -- MN: done
4273 AND s.transaction_id= lv_transaction_id(j)
4274 AND s.sr_instance_id= lv_instance_id(j)
4275 AND s.release_status = 1
4276 AND (s.maintenance_object_source <> 2 OR s.maintenance_object_source is NULL);
4277
4278 IF SQL%ROWCOUNT > 0
4279 THEN
4280 lv_resched_jobs := SQL%ROWCOUNT;
4281 lv_resched_jobs := lv_resched_jobs + lv_resched_cmro_jobs + lv_resched_cmro_alt_jobs;
4282 ELSE
4283 lv_resched_jobs := 0 +lv_resched_cmro_jobs +lv_resched_cmro_alt_jobs;
4284
4285 END IF;
4286
4287 -- ------------------------------------------------------------------------
4288 -- Perform the wip discrete job mass resched for the details
4289 -- ------------------------------------------------------------------------
4290
4291 -- the details are populated, only if the implement date and quantity
4292 -- are the same as the planned date and quantity
4293
4294 -- OPERATIONS
4295 FORALL j IN 1..lv_job_count
4296 INSERT INTO MSC_WIP_JOB_DTLS_INTERFACE
4297 (last_update_date,
4298 last_updated_by,
4299 last_update_login,
4300 creation_date,
4301 created_by,
4302 organization_type,
4303 organization_id,
4304 group_id,
4305 parent_header_id,
4306 operation_seq_num,
4307 department_id,
4308 description,
4309 standard_operation_id,
4310 first_unit_start_date,
4311 first_unit_completion_date,
4312 last_unit_start_date,
4313 last_unit_completion_date,
4314 minimum_transfer_quantity,
4315 count_point_type,
4316 backflush_flag,
4317 SUBSTITUTION_TYPE,
4318 LOAD_TYPE,
4319 process_phase,
4320 process_status,
4321 SR_INSTANCE_ID,
4322 operation_seq_id, --Outbound Changes for XML
4323 WIP_ENTITY_ID)
4324 SELECT SYSDATE,
4325 arg_user_id,
4326 s.last_update_login,
4327 SYSDATE,
4328 arg_user_id,
4329 tp.organization_type,
4330 s.organization_id,
4331 decode(s.order_type,70,arg_wip_group_id*-1,arg_wip_group_id),--for cmro --arg_wip_group_id,
4332 s.transaction_id,
4333 resreq.OPERATION_SEQ_NUM,
4334 NULL, --department_id,
4335 DECODE(s.order_type,70,resreq.operation_name,NULL), --description,
4336 NULL, --standard_operation_id,
4337 min(resreq.START_DATE), --first_unit_start_date,
4338 min(resreq.START_DATE), --first_unit_completion_date,
4339 max(resreq.END_DATE), --last_unit_start_date,
4340 max(resreq.END_DATE), --last_unit_completion_date,
4341 NULL, --minimum_transfer_quantity,
4342 NULL, --count_point_type,
4343 NULL, --backflush_flag,
4344 SUBST_CHANGE,
4345 LT_OPERATION,
4346 2,
4347 1,
4348 s.sr_instance_id,
4349 resreq.operation_sequence_id, --Outbound Changes for XML
4350 s.disposition_id
4351 FROM msc_trading_partners tp,
4352 msc_resource_requirements resreq,
4353 msc_parameters param,
4354 msc_supplies s
4355 WHERE tp.sr_tp_id= s.organization_id --MN: again, is this correct? -- ey, correct
4356 AND tp.sr_instance_id= s.sr_instance_id --MN: again, is this correct? -- ey, correct
4357 AND tp.partner_type=3
4358 AND resreq.sr_instance_id= s.sr_instance_id
4359 AND resreq.organization_id= s.organization_id
4360 AND resreq.supply_id = s.transaction_id
4361 AND resreq.plan_id = s.plan_id
4362 AND resreq.parent_id = 2
4363 AND param.organization_id = s.organization_id --MN: again, is this correct? -- ey, correct
4364 AND param.sr_instance_id = s.sr_instance_id --MN: again, is this correct? -- ey, correct
4365 AND s.transaction_id= lv_transaction_id(j)
4366 AND s.sr_instance_id= lv_instance_id(j)
4367 AND s.plan_id= arg_plan_id
4368 AND lv_agg_details(j) = 1
4369 AND s.release_status = 1
4370 GROUP BY
4371 SYSDATE,
4372 arg_user_id,
4373 tp.organization_type,
4374 s.organization_id,
4375 s.last_update_login,
4376 SYSDATE,
4377 arg_user_id,
4378 decode(s.order_type,70,arg_wip_group_id*-1,arg_wip_group_id),
4379 s.transaction_id,
4380 resreq.OPERATION_SEQ_NUM,
4381 NULL, --department_id,
4382 LT_OPERATION, --load_type,
4383 DECODE(s.order_type,70,resreq.operation_name,NULL), --description,
4384 NULL, --standard_operation_id,
4385 NULL, --minimum_transfer_quantity,
4386 NULL, --count_point_type,
4387 NULL, --backflush_flag,
4388 s.sr_instance_id,
4389 resreq.operation_sequence_id,
4390 s.disposition_id;
4391
4392
4393 --DBMS_OUTPUT.PUT_LINE('OPERATION_RESOURCE');
4394
4395 -- OPERATION RESOURCES
4396 FORALL j IN 1..lv_job_count
4397 INSERT INTO MSC_WIP_JOB_DTLS_INTERFACE
4398 ( last_update_date,
4399 last_updated_by,
4400 last_update_login,
4401 creation_date,
4402 created_by,
4403 organization_type,
4404 organization_id,
4405 group_id,
4406 parent_header_id,
4407 operation_seq_num,
4408 alternate_num,
4409 resource_id_old,
4410 resource_id_new,
4411 usage_rate_or_amount,
4412 scheduled_flag,
4413 applied_resource_units, --
4414 applied_resource_value, --
4415 uom_code,
4416 basis_type, --
4417 activity_id, --
4418 autocharge_type, --
4419 standard_rate_flag, --
4420 start_date,
4421 completion_date,
4422 assigned_units,
4423 SUBSTITUTION_TYPE,
4424 LOAD_TYPE,
4425 process_phase,
4426 process_status,
4427 description,
4428 SR_INSTANCE_ID,
4429 operation_seq_id, -- Outbound Changes for XML
4430 wip_entity_id,
4431 resource_hours,
4432 department_id,
4433 firm_flag, --dsr
4434 setup_id,
4435 group_sequence_id,
4436 group_sequence_number,
4437 batch_id,
4438 maximum_assigned_units,
4439 parent_seq_num,
4440 resource_seq_num,
4441 schedule_seq_num)
4442 SELECT SYSDATE,
4443 arg_user_id,
4444 s.last_update_login,
4445 SYSDATE,
4446 arg_user_id,
4447 tp.organization_type,
4448 s.organization_id,
4449 decode(s.order_type,70,arg_wip_group_id*-1,arg_wip_group_id),--for cmro --arg_wip_group_id,
4450 s.transaction_id,
4451 resreq.OPERATION_SEQ_NUM,
4452 resreq.ALTERNATE_NUM,
4453 resreq.RESOURCE_ID,
4454 resreq.RESOURCE_ID,
4455 decode(resreq.parent_seq_num, null, decode(l_apps_ver,'4',to_number(null),'3',TO_NUMBER(NULL)
4456 ,resreq.RESOURCE_HOURS/decode(resreq.basis_type,2,1,decode(s.new_order_quantity,0,1,s.new_order_quantity + s.qty_completed - resreq.quantity_completed))), resreq.usage_rate),
4457 decode(nvl(resreq.schedule_flag,1),-23453,1,1,1,resreq.schedule_flag),
4458 NULL,
4459 NULL,
4460 NULL,
4461 resreq.basis_type,
4462 NULL,
4463 NULL,
4464 NULL,
4465 nvl(resreq.firm_start_date,resreq.START_DATE),
4466 nvl(resreq.firm_end_date,resreq.END_DATE),
4467 resreq.ASSIGNED_UNITS,
4468 decode(resreq.parent_seq_num,null,SUBST_CHANGE,SUBST_ADD),
4469 -- SUBST_CHANGE,
4470 LT_RESOURCE,
4471 2,
4472 1,
4473 NULL,
4474 s.sr_instance_id,
4475 resreq.operation_sequence_id, -- Outbound Changes for XML
4476 s.disposition_id,
4477 resreq.resource_hours,
4478 resreq.department_id,
4479 resreq.firm_flag,
4480 resreq.setup_id,
4481 resreq.group_sequence_id,
4482 resreq.group_sequence_number,
4483 resreq.batch_number,
4484 resreq.maximum_assigned_units,
4485 resreq.parent_seq_num,
4486 resreq.orig_resource_seq_num,
4487 resreq.resource_seq_num
4488 FROM msc_trading_partners tp,
4489 msc_resource_requirements resreq,
4490 msc_parameters param,
4491 msc_supplies s
4492 WHERE tp.sr_tp_id= s.organization_id
4493 AND tp.sr_instance_id= s.sr_instance_id
4494 AND tp.partner_type=3
4495 AND resreq.sr_instance_id= s.sr_instance_id
4496 AND resreq.organization_id= s.organization_id
4497 AND resreq.supply_id = s.transaction_id
4498 AND resreq.plan_id = s.plan_id
4499 AND resreq.parent_id = 2
4500 AND param.organization_id = s.organization_id
4501 --MN: again, is this correct? -- ey, correct
4502 AND param.sr_instance_id = s.sr_instance_id --MN: again, is this correct? -- ey, correct
4503 AND s.transaction_id= lv_transaction_id(j)
4504 AND s.sr_instance_id= lv_instance_id(j)
4505 AND s.plan_id= arg_plan_id
4506 AND lv_agg_details(j) = 1
4507 AND s.release_status = 1;
4508
4509
4510 --DBMS_OUTPUT.PUT_LINE('LOAD COMPONENTS');
4511
4512 /* UPDATE EXISTING COMPONENTS *
4513 | We should set inventory_item_id_new to NULL |
4514 * */
4515 FORALL j IN 1..lv_job_count
4516 INSERT INTO MSC_WIP_JOB_DTLS_INTERFACE
4517 (last_update_date,
4518 last_updated_by,
4519 last_update_login,
4520 creation_date,
4521 created_by,
4522 organization_type,
4523 organization_id,
4524 group_id,
4525 parent_header_id,
4526 operation_seq_num,
4527 inventory_item_id_old,
4528 inventory_item_id_new,
4529 basis_type,
4530 quantity_per_assembly,
4531 component_yield_factor,
4532 department_id,
4533 wip_supply_type,
4534 date_required,
4535 required_quantity,
4536 quantity_issued,
4537 supply_subinventory,
4538 supply_locator_id,
4539 mrp_net_flag,
4540 mps_required_quantity,
4541 mps_date_required,
4542 SUBSTITUTION_TYPE,
4543 LOAD_TYPE,
4544 process_phase,
4545 process_status,
4546 description,
4547 SR_INSTANCE_ID,
4548 -- operation_seq_id, -- Outbound Changes for XML
4549 uom_code, --Outbound Changes for XML
4550 wip_entity_id)
4551 SELECT SYSDATE,
4552 arg_user_id,
4553 s.last_update_login,
4554 SYSDATE,
4555 arg_user_id,
4556 tp.organization_type,
4557 s.organization_id,
4558 decode(s.order_type,70,arg_wip_group_id*-1,arg_wip_group_id),--for cmro --arg_wip_group_id,
4559 s.transaction_id,
4560 nvl(md.op_seq_num,1),
4561 msi.sr_inventory_item_id, -- bugbug MN: again, is this correct? -- ey, no, this should be the source item id for the item in msc_demands
4562 decode(l_apps_ver,'3',null,msi.sr_inventory_item_id),
4563 -- bugbug MN: again, is this correct? -- ey, no, this should be the source item id for the item in msc_demands
4564 decode(md.component_scaling_type,1,NULL,md.component_scaling_type),
4565 TO_NUMBER(NULL), --decode(l_apps_ver,'4',TO_NUMBER(NULL),'3',TO_NUMBER(NULL),(md.USING_REQUIREMENT_QUANTITY/s.implement_quantity)),
4566 TO_NUMBER(NULL), --md.component_yield_factor, -- bugbug is this correct?
4567 TO_NUMBER(NULL), --Department_ID
4568 NVL(md.wip_supply_type, msi.wip_supply_type), -- rthyagar, we should take from msc_bom_components as second priority later
4569 md.USING_ASSEMBLY_DEMAND_DATE,
4570 TO_NUMBER(NULL), --decode(l_apps_ver,'4',TO_NUMBER(NULL),'3',TO_NUMBER(NULL),md.USING_REQUIREMENT_QUANTITY),
4571 TO_NUMBER(NULL), --quantity_issued
4572 TO_CHAR(NULL), -- Sub Inventory
4573 TO_NUMBER(NULL), -- Locator ID
4574 1, -- MRP_NET_FLAG
4575 decode(l_apps_ver,'4',to_number(null),'3',TO_NUMBER(NULL),md.USING_REQUIREMENT_QUANTITY),
4576 md.USING_ASSEMBLY_DEMAND_DATE,
4577 SUBST_CHANGE,
4578 LT_COMPONENT,
4579 2,
4580 1,
4581 TO_CHAR(NULL),
4582 s.sr_instance_id,
4583 -- md.operation_seq_id,
4584 s.implement_uom_code, -- bugbug MN: again, is this correct?
4585 s.disposition_id
4586 FROM msc_trading_partners tp,
4587 msc_demands md,
4588 msc_system_items msi,
4589 msc_supplies s
4590 -- bugbug Is this join correct?
4591 WHERE tp.sr_tp_id= msi.organization_id -- ey, should be s.organization_id
4592 AND tp.sr_instance_id= s.sr_instance_id -- ey, s.sr_instance_id
4593 AND tp.partner_type=3
4594 AND s.inventory_item_id= md.using_assembly_item_id
4595 --MN: again, is this correct? -- /* ey, no, this link should be removed, the original link is to find the source item_id for the item in msc_demands */
4596 AND s.organization_id= md.organization_id --MN: again, is this correct? -- ey, no, this link should be
4597 AND s.sr_instance_id= md.sr_instance_id --MN: again, is this correct? -- ey, no, this link should be
4598 AND nvl(md.wip_supply_type,0) <> 6 -- ey, you need to find the wip_supply_type for the item_id in msc_demands
4599 AND (md.PRIMARY_COMPONENT_ID is null or md.PRIMARY_COMPONENT_ID = md.inventory_item_id)
4600 AND md.origination_type = 3
4601 AND md.sr_instance_id= s.sr_instance_id
4602 AND md.disposition_id= s.transaction_id
4603 AND md.plan_id= s.plan_id
4604 AND msi.inventory_item_id= md.inventory_item_id
4605 AND msi.plan_id = -1
4606 AND msi.sr_instance_id = s.sr_instance_id
4607 AND msi.organization_id = s.organization_id
4608 AND s.transaction_id= lv_transaction_id(j)
4609 AND s.sr_instance_id= lv_instance_id(j)
4610 AND s.plan_id= arg_plan_id
4611 AND lv_agg_details(j) = 1
4612 AND s.release_status = 1;
4613
4614
4615 --Load Co-product/by-product component details
4616
4617 FORALL j IN 1..lv_job_count
4618 INSERT INTO MSC_WIP_JOB_DTLS_INTERFACE
4619 (last_update_date,
4620 last_updated_by,
4621 last_update_login,
4622 creation_date,
4623 created_by,
4624 organization_type,
4625 organization_id,
4626 group_id,
4627 parent_header_id,
4628 operation_seq_num,
4629 inventory_item_id_old,
4630 inventory_item_id_new,
4631 quantity_per_assembly,
4632 department_id,
4633 wip_supply_type,
4634 date_required,
4635 required_quantity,
4636 quantity_issued,
4637 supply_subinventory,
4638 supply_locator_id,
4639 mrp_net_flag,
4640 mps_required_quantity,
4641 mps_date_required,
4642 SUBSTITUTION_TYPE,
4643 LOAD_TYPE,
4644 process_phase,
4645 process_status,
4646 description,
4647 SR_INSTANCE_ID,
4648 -- operation_seq_id, -- Outbound Changes for XML
4649 uom_code, --Outbound Changes for XML
4650 wip_entity_id)
4651 SELECT SYSDATE,
4652 arg_user_id,
4653 s.last_update_login,
4654 SYSDATE,
4655 arg_user_id,
4656 tp.organization_type,
4657 s.organization_id,
4658 decode(s.order_type,70,arg_wip_group_id*-1,arg_wip_group_id),--for cmro --arg_wip_group_id,
4659 s.transaction_id,
4660 nvl(co.operation_seq_num,1),
4661 icomp.sr_inventory_item_id, -- ey, source_item_id for item in co
4662 decode(l_apps_ver,'4',to_number(null),'3',null,icomp.sr_inventory_item_id),
4663 decode(l_apps_ver,'4',to_number(null),'3',TO_NUMBER(NULL),(co.new_order_quantity/s.implement_quantity)),
4664 TO_NUMBER(NULL), --Department_ID
4665 co.wip_supply_type,
4666 /*NVL(MSC_REL_PLAN_PUB.GET_WIP_SUPPLY_TYPE(s.plan_id, s.sr_instance_id,s.process_seq_id,
4667 s.inventory_item_id,co.inventory_item_id,s.organization_id,
4668 md.wip_supply_type), -- ey, wip_supply_type for */
4669 co.new_schedule_date,
4670 decode(l_apps_ver,'4',to_number(null),'3',TO_NUMBER(NULL),co.new_order_quantity),
4671 TO_NUMBER(NULL), --quantity_issued
4672 TO_CHAR(NULL), -- Sub Inventory
4673 TO_NUMBER(NULL), -- Locator ID
4674 1, -- MRP_NET_FLAG
4675 decode(l_apps_ver,'4',to_number(null),'3',TO_NUMBER(NULL),co.new_order_quantity),
4676 co.new_schedule_date,
4677 SUBST_CHANGE,
4678 LT_COMPONENT,
4679 2,
4680 1,
4681 TO_CHAR(NULL),
4682 s.sr_instance_id,
4683 s.implement_uom_code, -- bugbug MN: again, is this correct?
4684 -- ey, need to default it from uom_code for the item from msc_supplies s
4685 s.disposition_id
4686 FROM msc_trading_partners tp,
4687 msc_supplies co,
4688 msc_parameters param,
4689 msc_item_id_lid icomp,
4690 msc_supplies s
4691 WHERE tp.sr_tp_id = s.organization_id
4692 --MN: again, is this correct? -- ey, correct
4693 AND tp.sr_instance_id = s.sr_instance_id --MN: again, is this correct? -- ey, correct
4694 AND tp.partner_type = 3
4695 AND co.sr_instance_id = s.sr_instance_id
4696 AND co.disposition_id = s.transaction_id
4697 AND co.plan_id = s.plan_id
4698 AND co.order_type = 14 -- Discrete Job Co-products/by-products.
4699 AND icomp.inventory_item_id = s.inventory_item_id
4700 AND icomp.sr_instance_id = s.sr_instance_id
4701 AND param.organization_id = s.organization_id -- ey, s.organization_id
4702 AND param.sr_instance_id = s.sr_instance_id -- ey, s.sr_instance_id
4703 AND s.transaction_id = lv_transaction_id(j)
4704 AND s.sr_instance_id = lv_instance_id(j)
4705 AND s.plan_id = arg_plan_id
4706 AND lv_agg_details(j) = 1
4707 AND tp.organization_type = 2
4708 AND s.release_status = 1;
4709
4710
4711
4712 --DBMS_OUTPUT.PUT_LINE('LOAD SUBSTITUTE COMPONENTS');
4713 /* SUBSTITUTE EXISTING COMPONENTS ---------AKSHYA */
4714 FORALL j IN 1..lv_job_count
4715 INSERT INTO MSC_WIP_JOB_DTLS_INTERFACE
4716 (last_update_date,
4717 last_updated_by,
4718 last_update_login,
4719 creation_date,
4720 created_by,
4721 organization_type,
4722 organization_id,
4723 group_id,
4724 parent_header_id,
4725 operation_seq_num,
4726 inventory_item_id_old,
4727 inventory_item_id_new,
4728 quantity_per_assembly,
4729 department_id,
4730 wip_supply_type,
4731 date_required,
4732 required_quantity,
4733 quantity_issued,
4734 supply_subinventory,
4735 supply_locator_id,
4736 mrp_net_flag,
4737 mps_required_quantity,
4738 mps_date_required,
4739 SUBSTITUTION_TYPE,
4740 LOAD_TYPE,
4741 process_phase,
4742 process_status,
4743 description,
4744 -- operation_seq_id, --Outbound changes for XML
4745 uom_code, --Outbound Changes for XML
4746 SR_INSTANCE_ID)
4747 SELECT SYSDATE,
4748 arg_user_id,
4749 s.last_update_login,
4750 SYSDATE,
4751 arg_user_id,
4752 tp.organization_type,
4753 s.organization_id,
4754 decode(s.order_type,70,arg_wip_group_id*-1,arg_wip_group_id),--for cmro --arg_wip_group_id,
4755 s.transaction_id,
4756 nvl(md.op_seq_num,1),
4757 icomp.sr_inventory_item_id,
4758 sr_item.sr_inventory_item_id,
4759 bsub.usage_quantity,
4760 TO_NUMBER(NULL), --Department_ID
4761 md.wip_supply_type, -- bugbug if null get from msi?
4762 md.USING_ASSEMBLY_DEMAND_DATE,
4763 md.USING_REQUIREMENT_QUANTITY,
4764 0,
4765 TO_CHAR(NULL), -- Sub Inventory
4766 TO_NUMBER(NULL), -- Locator ID
4767 1,
4768 md.USING_REQUIREMENT_QUANTITY,
4769 md.USING_ASSEMBLY_DEMAND_DATE,
4770 SUBST_CHANGE,
4771 LT_COMPONENT,
4772 2,
4773 1,
4774 TO_CHAR(NULL),
4775 -- md.operation_seq_id,
4776 s.implement_uom_code, -- bugbug again should fall back to sys_items?
4777 s.sr_instance_id
4778 FROM msc_trading_partners tp,
4779 msc_item_id_lid sr_item,
4780 msc_bom_components subcomp,
4781 msc_component_substitutes bsub,
4782 /* msc_bom_components bcomp, */
4783 msc_item_id_lid icomp,
4784 msc_demands md,
4785 msc_supplies s
4786
4787 WHERE tp.sr_tp_id= s.organization_id
4788 AND tp.sr_instance_id= s.sr_instance_id
4789 AND tp.partner_type=3
4790 AND sr_item.inventory_item_id= md.inventory_item_id
4791 AND sr_item.sr_instance_id= md.sr_instance_id
4792 AND subcomp.plan_id = -1
4793 AND subcomp.bill_sequence_id = bsub.bill_sequence_id
4794 AND subcomp.COMPONENT_SEQUENCE_ID = bsub.COMPONENT_SEQUENCE_ID
4795 AND bsub.substitute_item_id = md.inventory_item_id
4796 AND bsub.organization_id = md.organization_id
4797 AND bsub.plan_id = -1
4798 AND subcomp.inventory_item_id = md.PRIMARY_COMPONENT_ID
4799 AND subcomp.using_assembly_id=md.using_assembly_item_id
4800 AND subcomp.sr_instance_id=s.sr_instance_Id
4801 AND subcomp.sr_instance_id=bsub.sr_instance_Id
4802 AND s.organization_id= md.organization_id
4803 AND md.sr_instance_Id= s.sr_instance_Id
4804 AND md.disposition_id= s.transaction_id
4805 AND md.plan_id= s.plan_id
4806 AND md.origination_type = 3
4807 AND s.transaction_id= lv_transaction_id(j)
4808 AND s.sr_instance_id= lv_instance_id(j)
4809 AND s.plan_id= arg_plan_id
4810 AND s.order_type <>70
4811 AND lv_agg_details(j) = 1
4812 AND md.PRIMARY_COMPONENT_ID is not null
4813 AND icomp.inventory_item_id= md.PRIMARY_COMPONENT_ID
4814 AND icomp.sr_instance_id= md.sr_instance_id
4815 AND rownum=1
4816 AND s.release_status = 1;
4817 /*Code to insert the substitute component--new for USAF/CMRO */
4818
4819 FORALL j IN 1..lv_job_count
4820 INSERT INTO MSC_WIP_JOB_DTLS_INTERFACE
4821 (last_update_date,
4822 last_updated_by,
4823 last_update_login,
4824 creation_date,
4825 created_by,
4826 organization_type,
4827 organization_id,
4828 group_id,
4829 parent_header_id,
4830 wip_entity_id,
4831 operation_seq_num,
4832 inventory_item_id_old,
4833 inventory_item_id_new,
4834 quantity_per_assembly,
4835 wip_supply_type,
4836 date_required,
4837 required_quantity,
4838 SUBSTITUTION_TYPE,
4839 LOAD_TYPE,
4840 uom_code, --Outbound Changes for XML
4841 SR_INSTANCE_ID)
4842 SELECT SYSDATE,
4843 arg_user_id,
4844 s.last_update_login,
4845 SYSDATE,
4846 arg_user_id,
4847 tp.organization_type,
4848 s.organization_id,
4849 arg_wip_group_id*-1,
4850 s.transaction_id,
4851 s.disposition_id,
4852 nvl(md.op_seq_num,1),
4853 icompold.sr_inventory_item_id,
4854 icompnew.sr_inventory_item_id,
4855 md.quantity_per_assembly,
4856 md.wip_supply_type,
4857 md.USING_ASSEMBLY_DEMAND_DATE,
4858 md.USING_REQUIREMENT_QUANTITY,
4859 SUBST_CHANGE,
4860 5,
4861 s.implement_uom_code,
4862 s.sr_instance_id
4863 FROM msc_trading_partners tp,
4864 msc_demands md,
4865 msc_supplies s,
4866 msc_system_items icompold,
4867 msc_system_items icompnew
4868 WHERE tp.sr_tp_id= s.organization_id
4869 AND tp.sr_instance_id= s.sr_instance_id
4870 AND tp.partner_type=3
4871 AND s.transaction_id= lv_transaction_id(j)
4872 AND s.sr_instance_id= lv_instance_id(j)
4873 AND s.plan_id= arg_plan_id
4874 AND s.order_type = 70
4875 AND s.maintenance_object_source = 2
4876 AND s.release_status = 1
4877 AND md.sr_instance_Id= s.sr_instance_Id
4878 AND md.disposition_id= s.transaction_id
4879 AND md.plan_id= s.plan_id
4880 AND md.primary_component_id is not null
4881 AND s.inventory_item_id= md.using_assembly_item_id
4882 AND s.organization_id= md.organization_id
4883 AND icompold.inventory_item_id = md.primary_component_id
4884 AND icompold.plan_id = -1
4885 AND icompold.organization_id = md.organization_id
4886 AND icompold.sr_instance_id = md.sr_instance_id
4887 AND icompnew.inventory_item_id = md.inventory_item_id
4888 AND icompnew.plan_id = -1
4889 AND icompnew.organization_id = md.organization_id
4890 AND icompnew.sr_instance_id = md.sr_instance_id
4891 AND md.primary_component_id <> md.inventory_item_id;
4892
4893 --DBMS_OUTPUT.PUT_LINE('RESOURCE_USAGE');
4894
4895 -- RESOURCE USAGE
4896 FORALL j IN 1..lv_job_count
4897 INSERT INTO MSC_WIP_JOB_DTLS_INTERFACE
4898 ( last_update_date,
4899 last_updated_by,
4900 last_update_login,
4901 creation_date,
4902 created_by,
4903 organization_type,
4904 organization_id,
4905 group_id,
4906 parent_header_id,
4907 operation_seq_num,
4908 alternate_num,
4909 resource_id_old,
4910 resource_id_new,
4911 usage_rate_or_amount,
4912 scheduled_flag,
4913 applied_resource_units, --
4914 applied_resource_value, --
4915 uom_code,
4916 basis_type, --
4917 activity_id, --
4918 autocharge_type, --
4919 standard_rate_flag, --
4920 start_date,
4921 completion_date,
4922 assigned_units,
4923 SUBSTITUTION_TYPE,
4924 LOAD_TYPE,
4925 process_phase,
4926 process_status,
4927 description,
4928 SR_INSTANCE_ID,
4929 operation_seq_id, -- Outbound Changes for XML
4930 wip_entity_id,
4931 department_id,
4932 resource_hours,
4933 resource_seq_num,
4934 schedule_seq_num,
4935 parent_seq_num)
4936 SELECT SYSDATE,
4937 arg_user_id,
4938 s.last_update_login,
4939 SYSDATE,
4940 arg_user_id,
4941 tp.organization_type,
4942 s.organization_id,
4943 decode(s.order_type,70,arg_wip_group_id*-1,arg_wip_group_id),--for cmro --arg_wip_group_id,
4944 s.transaction_id,
4945 resreq.OPERATION_SEQ_NUM,
4946 resreq.ALTERNATE_NUM,
4947 resreq.RESOURCE_ID,
4948 resreq.RESOURCE_ID,
4949 decode(resreq.parent_seq_num, null, decode(l_apps_ver,'4',to_number(null),'3',TO_NUMBER(NULL)
4950 ,resreq.RESOURCE_HOURS/decode(resreq.basis_type,2,1,decode(s.new_order_quantity,0,1,s.new_order_quantity + s.qty_completed - resreq.quantity_completed))), resreq.usage_rate),
4951 decode(nvl(resreq.schedule_flag,1),-23453,1,1,1,resreq.schedule_flag),
4952 NULL,
4953 NULL,
4954 v_hour_uom,
4955 resreq.basis_type,
4956 NULL,
4957 NULL,
4958 NULL,
4959 nvl(resreq.firm_start_date,resreq.START_DATE),
4960 nvl(resreq.firm_end_date,resreq.END_DATE),
4961 resreq.ASSIGNED_UNITS,
4962 SUBST_CHANGE,
4963 LT_RESOURCE_USAGE,
4964 2,
4965 1,
4966 NULL,
4967 s.sr_instance_id,
4968 resreq.operation_sequence_id, -- Outbound Changes for XML
4969 s.disposition_id,
4970 resreq.department_id,
4971 resreq.resource_hours,
4972 resreq.orig_resource_seq_num,
4973 resreq.resource_seq_num,
4974 resreq.parent_seq_num
4975 FROM msc_trading_partners tp,
4976 msc_resource_requirements resreq,
4977 msc_parameters param,
4978 msc_supplies s
4979 WHERE tp.sr_tp_id= s.organization_id
4980 AND tp.sr_instance_id=s.sr_instance_id
4981 AND tp.partner_type=3
4982 AND resreq.sr_instance_id= s.sr_instance_id
4983 AND resreq.organization_id= s.organization_id
4984 AND resreq.supply_id = s.transaction_id
4985 AND resreq.plan_id = s.plan_id
4986 AND resreq.parent_id = 1
4987 AND param.organization_id = s.organization_id
4988 AND param.sr_instance_id = s.sr_instance_id
4989 AND s.transaction_id= lv_transaction_id(j)
4990 AND s.sr_instance_id= lv_instance_id(j)
4991 AND s.plan_id= arg_plan_id
4992 AND s.release_status = 1
4993
4994 AND lv_agg_details(j) = 1
4995 AND tp.organization_type IN (1, 2); -- 1 - discrete wip org; 2 - opm org
4996
4997
4998 -- ey, do we need this in 11.5.10, do we have resource instance in 11.5.10?
4999 -- dsr starts here
5000 -- print_debug_info('OPERATION RESOURCE_INSTANCES');
5001 -- OPERATION RESOURCE_INSTANCES
5002
5003 FORALL j IN 1..lv_job_count
5004 INSERT INTO MSC_WIP_JOB_DTLS_INTERFACE
5005 ( last_update_date,
5006 last_updated_by,
5007 last_update_login,
5008 creation_date,
5009 created_by,
5010 organization_type,
5011 organization_id,
5012 group_id,
5013 parent_header_id,
5014 operation_seq_num,
5015 resource_id_old, -- rawasthi
5016 resource_id_new,
5017 RESOURCE_INSTANCE_ID,
5018 start_date,
5019 completion_date,
5020 SUBSTITUTION_TYPE,
5021 LOAD_TYPE,
5022 process_phase,
5023 process_status,
5024 SR_INSTANCE_ID,
5025 operation_seq_id,
5026 resource_hours,
5027 department_id,
5028 SERIAL_NUMBER,
5029 group_sequence_id,
5030 group_sequence_number,
5031 batch_id,
5032 resource_seq_num
5033 , schedule_seq_num
5034 , wip_entity_id -- for reschedule
5035 , parent_seq_num
5036 )
5037 SELECT
5038 SYSDATE,
5039 arg_user_id,
5040 s.last_update_login,
5041 SYSDATE,
5042 arg_user_id,
5043 tp.organization_type,
5044 s.organization_id,
5045 decode(s.order_type,70,arg_wip_group_id*-1,arg_wip_group_id),--for cmro --arg_wip_group_id,
5046 s.transaction_id,
5047 resreq.OPERATION_SEQ_NUM,
5048 resreq.resource_id,
5049 resreq.resource_id,
5050 res_instreq.RES_INSTANCE_ID , -- RS
5051 nvl(resreq.firm_start_date,res_instreq.START_DATE),
5052 nvl(resreq.firm_end_date,res_instreq.END_DATE),
5053 SUBST_ADD,
5054 LT_RESOURCE_INSTANCE,
5055 2,
5056 1,
5057 s.sr_instance_id,
5058 resreq.operation_sequence_id,
5059 resreq.resource_hours,
5060 resreq.department_id,
5061 res_instreq.serial_number, -- RS
5062 resreq.group_sequence_id,
5063 resreq.group_sequence_number,
5064 res_instreq.batch_number,
5065 resreq.orig_resource_seq_num,
5066 resreq.resource_seq_num
5067 , s.disposition_id -- for reschedule
5068 , resreq.parent_seq_num
5069 FROM
5070 msc_trading_partners tp,
5071 msc_resource_requirements resreq,
5072 msc_resource_instance_reqs res_instreq,
5073 msc_supplies s
5074 WHERE
5075 tp.sr_tp_id=s.organization_id
5076 AND tp.sr_instance_id= s.sr_instance_id
5077 AND tp.partner_type=3
5078 AND resreq.sr_instance_id= s.sr_instance_id
5079 AND resreq.organization_id= s.organization_id
5080 AND resreq.supply_id = s.transaction_id
5081 AND resreq.plan_id = s.plan_id
5082 AND resreq.resource_seq_num = res_instreq.resource_seq_num
5083 AND resreq.operation_seq_num = res_instreq.operation_seq_num
5084 AND resreq.resource_id = res_instreq.resource_id
5085 AND resreq.supply_id = res_instreq.supply_id
5086 AND resreq.sr_instance_id = res_instreq.sr_instance_id
5087 AND resreq.plan_id = res_instreq.plan_id
5088 AND resreq.parent_id = res_instreq.parent_id --rawasthi
5089 AND resreq.start_date = res_instreq.start_date
5090 AND resreq.parent_id = 2
5091 AND resreq.resource_id <> -1
5092 AND resreq.department_id <> -1
5093 AND res_instreq.plan_id = s.plan_id
5094 AND s.transaction_id= lv_transaction_id(j)
5095 AND s.sr_instance_id= lv_instance_id(j)
5096 AND s.plan_id= arg_plan_id
5097 AND lv_agg_details(j) = 1
5098 AND s.release_status = 1
5099 ;
5100
5101 -- print_debug_info('reschedule_wip_discrete_jobs: 888 sql%rowcount = '|| SQL%ROWCOUNT);
5102 -- RESOURCE INSTANCE USAGES
5103 -- print_debug_info('RESOURCE INSTANCE USAGES');
5104 FORALL j IN 1..lv_job_count
5105 INSERT INTO MSC_WIP_JOB_DTLS_INTERFACE
5106 ( last_update_date,
5107 last_updated_by,
5108 last_update_login,
5109 creation_date,
5110 created_by,
5111 organization_type,
5112 organization_id,
5113 group_id,
5114 parent_header_id,
5115 operation_seq_num,
5116 resource_id_old, -- rawasthi
5117 resource_id_new,
5118 RESOURCE_INSTANCE_ID,
5119 start_date,
5120 completion_date,
5121 SUBSTITUTION_TYPE,
5122 LOAD_TYPE,
5123 process_phase,
5124 process_status,
5125 SR_INSTANCE_ID,
5126 operation_seq_id,
5127 FIRM_FLAG,
5128 resource_hours,
5129 department_id,
5130 serial_number,
5131 resource_seq_num,
5132 schedule_seq_num
5133 , wip_entity_id -- for reschedule
5134 , assigned_units
5135 ,parent_seq_num
5136 )
5137 SELECT
5138 SYSDATE,
5139 arg_user_id,
5140 s.last_update_login,
5141 SYSDATE,
5142 arg_user_id,
5143 tp.organization_type,
5144 s.organization_id,
5145 decode(s.order_type,70,arg_wip_group_id*-1,arg_wip_group_id),--for cmro --arg_wip_group_id,
5146 s.transaction_id,
5147 resreq.OPERATION_SEQ_NUM,
5148 resreq.RESOURCE_ID,
5149 resreq.RESOURCE_ID,
5150 res_instreq.RES_INSTANCE_ID ,
5151 nvl(resreq.firm_start_date,res_instreq.START_DATE),
5152 nvl(resreq.firm_end_date,res_instreq.END_DATE),
5153 SUBST_ADD,
5154 LT_RESOURCE_INST_USAGE,
5155 2,
5156 1,
5157 s.sr_instance_id,
5158 resreq.operation_sequence_id,
5159 resreq.firm_flag,
5160 res_instreq.resource_instance_hours,
5161 resreq.department_id,
5162 res_instreq.serial_number,
5163 resreq.orig_resource_seq_num,
5164 resreq.resource_seq_num
5165 , s.disposition_id -- for reschedule
5166 , 1 -- jguo
5167 ,resreq.parent_seq_num
5168 FROM
5169 msc_trading_partners tp,
5170 msc_resource_requirements resreq,
5171 msc_resource_instance_reqs res_instreq,
5172 msc_supplies s
5173 WHERE
5174 tp.sr_tp_id=s.organization_id
5175 AND tp.sr_instance_id= s.sr_instance_id
5176 AND tp.partner_type=3
5177 AND resreq.sr_instance_id= s.sr_instance_id
5178 AND resreq.organization_id= s.organization_id
5179 AND resreq.supply_id = s.transaction_id
5180 AND resreq.plan_id = s.plan_id
5181 AND resreq.resource_seq_num = res_instreq.resource_seq_num
5182 AND resreq.operation_seq_num = res_instreq.operation_seq_num
5183 AND resreq.resource_id = res_instreq.resource_id
5184 AND resreq.supply_id = res_instreq.supply_id
5185 AND resreq.sr_instance_id = res_instreq.sr_instance_id
5186 AND resreq.plan_id = res_instreq.plan_id
5187 AND resreq.parent_id = res_instreq.parent_id --rawasthi
5188 AND resreq.start_date = res_instreq.start_date
5189 AND resreq.parent_id = 1
5190 AND resreq.resource_id <> -1
5191 AND resreq.department_id <> -1
5192 AND res_instreq.plan_id = s.plan_id
5193 AND s.transaction_id= lv_transaction_id(j)
5194 AND s.sr_instance_id= lv_instance_id(j)
5195 AND s.plan_id= arg_plan_id
5196 AND lv_agg_details(j) = 1
5197 AND s.release_status = 1;
5198
5199 -- bugbug should we add resource charges handling?
5200
5201
5202
5203
5204 RETURN lv_resched_jobs;
5205
5206 END reschedule_wip_discr_jobs_ps;
5207
5208
5209 END MSC_REL_PS_PLAN_PUB;