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