[Home] [Help]
PACKAGE BODY: APPS.MRP_AP_REL_PLAN_PUB
Source
1 PACKAGE BODY MRP_AP_REL_PLAN_PUB AS
2 /* $Header: MRPRELPB.pls 120.11 2008/01/10 10:32:28 sbyerram ship $ */
3
4 G_WIP_GROUP_ID NUMBER;
5 G_EAM_GROUP_ID NUMBER; -- dsr
6 G_OPM_WIP_GROUP_ID NUMBER;
7 G_PO_BATCH_NUMBER NUMBER;
8
9 -- PV_RESOURCE_TYPE NUMBER; /* profile value */
10 PV_RES_PRIORITY NUMBER;
11 PV_SIM_RES_SEQ NUMBER;
12 PV_REL_REQUEST_DATE VARCHAR2(1);
13
14 LT_RESOURCE CONSTANT INTEGER := 1; -- wip details load type
15 LT_COMPONENT CONSTANT INTEGER := 2;
16 LT_OPERATION CONSTANT INTEGER := 3;
17 LT_RESOURCE_USAGE CONSTANT INTEGER := 4;
18
19 SUBST_DELETE CONSTANT INTEGER := 1; -- wip details substitution
20 SUBST_ADD CONSTANT INTEGER := 2; -- type
21 SUBST_CHANGE CONSTANT INTEGER := 3;
22
23 PURCHASING_BY_REV CONSTANT INTEGER := 1;
24 NOT_PURCHASING_BY_REV CONSTANT INTEGER := 2;
25 UNDER_REV_CONTROL CONSTANT INTEGER := 2;
26 NOT_UNDER_REV_CONTROL CONSTANT INTEGER := 1;
27
28 RESOURCE_INSTANCES CONSTANT INTEGER := 8; -- dsr
29 RESOURCE_INSTANCE_USAGE CONSTANT INTEGER := 9; -- dsr
30 RESOURCE_INSTANCES_OSFM CONSTANT INTEGER := 7; -- dsr
31 RESOURCE_INSTANCE_USAGE_OSFM CONSTANT INTEGER := 4; -- dsr
32 G_OPR_UPDATE CONSTANT INTEGER := 2; -- dsr
33
34 NULL_DBLINK CONSTANT VARCHAR2(1):= ' ';
35
36 var_purchasing_by_rev NUMBER := to_number(FND_PROFILE.VALUE('MRP_PURCHASING_BY_REVISION'));
37
38 var_lot_job_copy_rout NUMBER := to_number(FND_PROFILE.VALUE('WSM_CREATE_LBJ_COPY_ROUTING'));
39
40 v_instance_id NUMBER;
41 v_dblink VARCHAR2(128);
42 v_instance_code VARCHAR2(3);
43 v_application_id NUMBER;
44
45 --This Procedure checks if there is any detail exists for OPM Jobs
46 --If detail exists, the scheduling method is set to 1(Manual
47 --scheduling) and if doesn't exist then the scheduling method is
48 --set to 2(Others).
49 PROCEDURE SET_OPM_SCHEDULING_METHOD
50 IS
51 BEGIN
52
53 UPDATE wip_job_schedule_interface i
54 SET i.scheduling_method=2
55 WHERE
56 i.group_id = G_OPM_WIP_GROUP_ID
57 AND i.load_type in(1,3);
58
59 UPDATE wip_job_schedule_interface i
60 SET i.scheduling_method=1
61 WHERE
62 EXISTS(SELECT 1
63 FROM WIP_JOB_DTLS_INTERFACE jdi
64 WHERE jdi.group_id=G_OPM_WIP_GROUP_ID
65 AND jdi.parent_header_id = i.header_id
66 AND ROWNUM=1)
67 AND i.group_id = G_OPM_WIP_GROUP_ID
68 AND i.load_type in(1,3);
69
70 END SET_OPM_SCHEDULING_METHOD;
71
72
73
74
75 --Overloaded this function so that UI code MSCRLWFB.pls can still call other
76 --Initialize Function
77 PROCEDURE INITIALIZE
78 ( p_user_name IN VARCHAR2,
79 p_resp_name IN VARCHAR2,
80 p_application_name IN VARCHAR2,
81 p_instance_id IN NUMBER,
82 p_instance_code IN VARCHAR2,
83 p_aps_dblink IN VARCHAR2,
84 p_wip_group_id OUT NOCOPY NUMBER,
85 p_po_batch_number OUT NOCOPY NUMBER,
86 p_application_id IN NUMBER)
87 IS
88 BEGIN
89
90 v_instance_id := p_instance_id;
91 v_instance_code := p_instance_code;
92 v_dblink := p_aps_dblink;
93 v_application_id := p_application_id;
94
95 INITIALIZE( p_user_name ,
96 p_resp_name ,
97 p_application_name,
98 p_wip_group_id,
99 p_po_batch_number );
100
101
102 END INITIALIZE;
103
104 PROCEDURE INITIALIZE( p_user_name IN VARCHAR2,
105 p_resp_name IN VARCHAR2,
106 p_application_name IN VARCHAR2,
107 p_wip_group_id OUT NOCOPY NUMBER,
108 p_po_batch_number OUT NOCOPY NUMBER )
109 IS
110
111 l_user_id NUMBER;
112 l_application_id NUMBER;
113 l_resp_id NUMBER;
114 lv_log_msg varchar2(500);
115 BEGIN
116
117 SELECT wip_job_schedule_interface_s.nextval,
118 mrp_workbench_query_s.nextval,
119 -- TO_NUMBER( FND_PROFILE.VALUE('MSC_RESOURCE_TYPE')),
120 TO_NUMBER( FND_PROFILE.VALUE('MSC_ALT_RES_PRIORITY')),
121 TO_NUMBER( FND_PROFILE.VALUE('MSC_SIMUL_RES_SEQ')),
122 NVL(FND_PROFILE.VALUE('MSC_UPD_REQ_DATE_REL'),'N')
123 INTO G_WIP_GROUP_ID,
124 G_PO_BATCH_NUMBER,
125 -- PV_RESOURCE_TYPE,
126 PV_RES_PRIORITY,
127 PV_SIM_RES_SEQ,
128 PV_REL_REQUEST_DATE
129 FROM DUAL;
130
131 SELECT wip_job_schedule_interface_s.nextval
132 INTO G_OPM_WIP_GROUP_ID
133 FROM DUAL;
134
135 p_wip_group_id := G_WIP_GROUP_ID;
136 p_po_batch_number := G_PO_BATCH_NUMBER;
137
138 /* if user_id = -1, it means this procedure is called from a
139 remote database */
140 IF FND_GLOBAL.USER_ID = -1 THEN
141
142 BEGIN
143
144 SELECT USER_ID
145 INTO l_user_id
146 FROM FND_USER
147 WHERE USER_NAME = p_user_name;
148
149 EXCEPTION
150 WHEN NO_DATA_FOUND THEN
151 raise_application_error (-20001, 'NO_USER_DEFINED');
152 END;
153
154 IF MRP_CL_FUNCTION.validateUser(l_user_id,MSC_UTIL.TASK_RELEASE,lv_log_msg) THEN
155 MRP_CL_FUNCTION.MSC_Initialize(MSC_UTIL.TASK_RELEASE,
156 l_user_id,
157 -1, --l_resp_id,
158 -1 --l_application_id
159 );
160 ELSE
161 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_log_msg);
162 raise_application_error (-20001, lv_log_msg);
163 END IF;
164
165 END IF;
166
167 END INITIALIZE;
168
169
170 PROCEDURE MODIFY_LJ_RES_REQ
171 IS
172
173 TYPE NumTab IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
174 TYPE RIDTab IS TABLE OF ROWID INDEX BY BINARY_INTEGER;
175 TYPE DateTab IS TABLE OF DATE INDEX BY BINARY_INTEGER;
176
177 lv_std_job_count NUMBER;
178 lv_jsi_rowid RIDTab;
179 lv_header_id NumTab;
180 lv_job_schedule_type NumTab;
181 lv_routing_seq_id NumTab;
182 lv_start_date DateTab;
183 lv_wip_entity_id NumTab;
184
185 lv_details NUMBER;
186
187 lv_nwk_job_op_seq_num NumTab;
188 lv_nwk_op_seq_num NumTab;
189 lv_un_op_cnt NUMBER;
190
191 lv_pri_res_rowid RIDTab;
192 lv_pri_res_seq_num NumTab;
193 lv_pri_sub_grp_num NumTab;
194 lv_pri_schd_seq_num NumTab;
195
196 lv_pri_res_cnt Number;
197
198 lv_alt_res_rowid RIDTab;
199 lv_alt_sub_grp_num NumTab;
200 lv_alt_schd_seq_num NumTab;
201
202 lv_alt_res_cnt Number;
203
204 lv_unschd_op_seq_num NumTab;
205 lv_unschd_rtng_op_seq_num NumTab;
206 lv_unschd_res_id NumTab;
207 lv_unschd_res_seq_num NumTab;
208 lv_unschd_schd_seq_num NumTab;
209 lv_unschd_rep_grp_num NumTab;
210 lv_unschd_subs_grp_num NumTab;
211 lv_unschd_load_type NumTab;
212 -- dsr added following 7 lines
213 lv_unschd_firm_flag NumTab;
214 lv_unschd_setup_id NumTab;
215 lv_unschd_group_sequence_id NumTab;
216 LV_UNSCHD_GROUP_SEQ_NUM NumTab;
217 lv_unschd_batch_id NumTab;
218 LV_UNSCHD_MAX_ASSIGNED_UNITS NumTab;
219 lv_unschd_parent_seq_num NumTab;
220
221 lv_unschd_res_cnt Number;
222
223
224 lv_unres_res_seq_num NumTab;
225 lv_unres_schd_seq_num NumTab;
226 lv_unres_res_id NumTab;
227 lv_unres_sub_grp_num NumTab;
228 lv_unres_rep_grp_num NumTab;
229
230
231 lv_unres_cnt Number;
232
233 lv_res_rowid RidTab;
234 lv_res_seq_num NumTab;
235 lv_sub_grp_num NumTab;
236 lv_schd_seq_num NumTab;
237
238
239 lv_res_cnt Number;
240
241 lv_cur_op_res_rowid RIDTab;
242 lv_cur_op_res_seq_num NumTab;
243 lv_cur_op_sub_grp_num NumTab;
244 lv_cur_op_schd_seq_num NumTab;
245
246 lv_cur_op_res_cnt Number;
247
248 lv_unschd_job_op_seq_num NumTab;
249
250
251
252
253
254
255 Begin
256
257 /* get header info for jobs being loaded */
258
259 SELECT jsi.rowid,
260 jsi.header_id,
261 rtng.common_routing_sequence_id,
262 jsi.load_type,
263 jsi.first_unit_start_date,
264 jsi.wip_entity_id
265 BULK COLLECT
266 INTO lv_jsi_rowid,
267 lv_header_id,
268 lv_routing_seq_id,
269 lv_job_schedule_type,
270 lv_start_date,
271 lv_wip_entity_id
272 FROM BOM_OPERATIONAL_ROUTINGS rtng,
273 WSM_LOT_JOB_INTERFACE jsi
274 WHERE jsi.group_id = G_WIP_GROUP_ID
275 AND jsi.load_type in (5,6) /* standard job */
276 AND rtng.assembly_item_id(+)= jsi.primary_item_id
277 AND rtng.organization_id(+)= jsi.organization_id
278 AND NVL(rtng.alternate_routing_designator(+),' ')=
279 NVL( jsi.alternate_routing_designator,' ');
280
281 lv_std_job_count:= SQL%ROWCOUNT;
282
283 IF lv_std_job_count= 0 THEN RETURN; END IF;
284
285 FOR n IN 1..lv_std_job_count LOOP
286
287 BEGIN
288
289 /* check whether the details has been released for the job */
290
291 select count(*)
292 into lv_details
293 From WSM_LOT_JOB_DTL_INTERFACE jdi
294 Where jdi.group_id = G_WIP_GROUP_ID
295 and jdi.parent_header_id = lv_header_id(n);
296
297 If lv_details = 0 Then
298
299 If lv_job_schedule_type(n) in (5,6) Then --bug#3459145
300
301 update WSM_LOT_JOB_INTERFACE
302 set FIRST_UNIT_START_DATE = null
303 where GROUP_ID=G_WIP_GROUP_ID
304 and HEADER_ID=lv_header_id(n);
305
306 END IF;
307
308 ELSE
309
310 BEGIN
311
312 /* when we are releasing the details, scheduling_method is 3 Bug 3401524*/
313
314 IF var_lot_job_copy_rout = 1 THEN
315 update WSM_LOT_JOB_INTERFACE
316 set SCHEDULING_METHOD=3
317 where GROUP_ID=G_WIP_GROUP_ID
318 and HEADER_ID=lv_header_id(n);
319 END IF;
320
321 /* Determine the unscheduled operations that are part of the recommended path */
322
323 Select jdi.JOB_OP_SEQ_NUM,
324 jdi.ROUTING_OP_SEQ_NUM
325 BULK COLLECT INTO
326 lv_nwk_job_op_seq_num,
327 lv_nwk_op_seq_num
328 From WSM_LOT_JOB_DTL_INTERFACE jdi
329 Where jdi.group_id = G_WIP_GROUP_ID
330 and jdi.parent_header_id = lv_header_id(n)
331 and jdi.load_type = 3
332 and not exists ( SELECT 1 from WSM_LOT_JOB_DTL_INTERFACE jdi1
333 Where jdi1.group_id = G_WIP_GROUP_ID
334 and jdi1.parent_header_id = lv_header_id(n)
335 and jdi1.load_type = 1
336 and nvl(jdi1.JOB_OP_SEQ_NUM,-1) = nvl(jdi.JOB_OP_SEQ_NUM,-1)
337 and nvl(jdi1.ROUTING_OP_SEQ_NUM,-1) = nvl(jdi.ROUTING_OP_SEQ_NUM,-1));
338 /*union
339 Select to_number(NULL), --Bug#3432607
340 jdi.NEXT_ROUTING_OP_SEQ_NUM
341 From WSM_LOT_JOB_DTL_INTERFACE jdi
342 Where jdi.group_id = G_WIP_GROUP_ID
343 and jdi.parent_header_id = lv_header_id(n)
344 and jdi.load_type = 5
345 and not exists ( SELECT 1 from WSM_LOT_JOB_DTL_INTERFACE jdi1
346 Where jdi1.group_id = G_WIP_GROUP_ID
347 and jdi1.parent_header_id = lv_header_id(n)
348 and jdi1.load_type = 3
349 and jdi1.ROUTING_OP_SEQ_NUM = jdi.NEXT_ROUTING_OP_SEQ_NUM)
350 and not exists (select 1
351 from WSM_LOT_JOB_DTL_INTERFACE jdi1
352 where jdi1.group_id = G_WIP_GROUP_ID
353 and jdi1.parent_header_id = lv_header_id(n)
354 and jdi1.load_type = 5
355 and jdi1.ROUTING_OP_SEQ_NUM = jdi.NEXT_ROUTING_OP_SEQ_NUM);*/
356
357 lv_un_op_cnt := SQL%ROWCOUNT;
358
359 /* For Bug 3608361 -- we need not set start_date and completion date as null for unscheduled operations */
360 -- IF lv_un_op_cnt > 0 Then
361
362 -- FORALL j in 1..lv_un_op_cnt
363
364 /* insert unscheduled operations with start_date and completion date as null
365
366 INSERT INTO WSM_LOT_JOB_DTL_INTERFACE --Bug#3432607
367 ( last_update_date,
368 last_updated_by,
369 last_update_login,
370 creation_date,
371 created_by,
372 RECORD_ID,
373 group_id,
374 parent_header_id,
375 job_op_seq_num,
376 routing_op_seq_num,
377 substitution_type,
378 load_type,
379 operation_start_date,
380 operation_completion_date,
381 process_phase,
382 process_status,
383 transaction_date)
384 SELECT SYSDATE,
385 FND_GLOBAL.USER_ID,
386 FND_GLOBAL.USER_ID,
387 SYSDATE,
388 FND_GLOBAL.USER_ID,
389 WSM_LOT_SM_IFC_HEADER_S.NEXTVAL,
390 G_WIP_GROUP_ID,
391 lv_header_id(n),
392 lv_nwk_job_op_seq_num(j),
393 lv_nwk_op_seq_num(j),
394 4,
395 3,
396 null,
397 null,
398 1,
399 1,
400 sysdate
401 FROM DUAL;*/
402
403
404 /* For Bug 3608361 -- we need not set start_date and completion date as null for unscheduled operations */
405 /*
406 UPDATE WSM_LOT_JOB_DTL_INTERFACE jdi
407 SET operation_start_date = null, operation_completion_date = null
408 WHERE jdi.group_id = G_WIP_GROUP_ID
409 AND jdi.parent_header_id = lv_header_id(n)
410 AND jdi.load_type = 3
411 AND nvl(jdi.job_op_seq_num,-1) = nvl(lv_nwk_job_op_seq_num(j),-1)
412 AND routing_op_seq_num = lv_nwk_op_seq_num(j);
413 */
414
415 -- END IF;
416
417 EXCEPTION WHEN NO_DATA_FOUND THEN NULL;
418 WHEN OTHERS THEN RAISE;
419 END;
420
421
422 If lv_job_schedule_type(n) = 5 Then /*New jobs*/
423
424 /* Get resource_seq_num for primary resource records */
425
426 BEGIN
427
428 Select jdi.rowid,
429 bor.resource_seq_num,
430 bor.substitute_group_num,
431 bor.schedule_seq_num
432 BULK COLLECT INTO lv_pri_res_rowid,
433 lv_pri_res_seq_num,
434 lv_pri_sub_grp_num,
435 lv_pri_schd_seq_num
436 From WSM_LOT_JOB_DTL_INTERFACE jdi,
437 bom_operation_resources bor,
438 bom_operation_sequences bos
439 Where jdi.group_id = G_WIP_GROUP_ID
440 and jdi.parent_header_id = lv_header_id(n)
441 -- ds and jdi.load_type in (1,4)
442 and jdi.load_type in (1,4, RESOURCE_INSTANCES_OSFM, RESOURCE_INSTANCE_USAGE_OSFM)
443 and jdi.replacement_group_num = 0
444 and bos.operation_seq_num = jdi.routing_op_seq_num
445 -- and nvl(bor.schedule_seq_num,bor.resource_seq_num) = jdi.resource_seq_num
446 and bor.resource_seq_num = jdi.resource_seq_num
447 and bor.resource_id = jdi.resource_id_new
448 and bos.routing_sequence_id = lv_routing_seq_id(n)
449 and bos.operation_sequence_id = bor.operation_sequence_id
450 and bos.effectivity_date <= lv_start_date(n)
451 and NVL(bos.disable_date, lv_start_date(n)) >= lv_start_date(n);
452
453
454 lv_pri_res_cnt := SQL%ROWCOUNT;
455
456 IF lv_pri_res_cnt > 0 THEN
457
458 FORALL k IN 1..lv_pri_res_cnt
459 UPDATE WSM_LOT_JOB_DTL_INTERFACE set resource_seq_num = lv_pri_res_seq_num(k),
460 substitute_group_num = lv_pri_sub_grp_num(k),
461 schedule_seq_num = lv_pri_schd_seq_num(k)
462 WHERE rowid = lv_pri_res_rowid(k);
463
464 END IF;
465
466 EXCEPTION WHEN NO_DATA_FOUND THEN NULL;
467 WHEN OTHERS THEN RAISE;
468
469 END;
470
471 /* get resource_seq_num for alternate resources */
472
473 BEGIN
474
475 Select distinct
476 jdi.rowid,
477 bsor.substitute_group_num,
478 bsor.schedule_seq_num
479 BULK COLLECT INTO lv_alt_res_rowid,
480 lv_alt_sub_grp_num,
481 lv_alt_schd_seq_num
482 From WSM_LOT_JOB_DTL_INTERFACE jdi,
483 bom_operation_resources bor,
484 bom_operation_sequences bos,
485 bom_sub_operation_resources bsor
486 Where jdi.group_id = G_WIP_GROUP_ID
487 and jdi.parent_header_id = lv_header_id(n)
488 -- and jdi.load_type in (1,4)
489 and jdi.load_type in (1,4, RESOURCE_INSTANCES_OSFM, RESOURCE_INSTANCE_USAGE_OSFM)
490 and jdi.replacement_group_num <> 0
491 and bos.routing_sequence_id = lv_routing_seq_id(n)
492 and bos.operation_sequence_id = bor.operation_sequence_id
493 and bos.effectivity_date <= lv_start_date(n)
494 and NVL(bos.disable_date, lv_start_date(n)) >= lv_start_date(n)
495 and bsor.substitute_group_num = bor.substitute_group_num
496 and bsor.operation_sequence_id = bor.operation_sequence_id
497 and nvl(jdi.schedule_seq_num,jdi.resource_seq_num )= nvl(bor.schedule_seq_num,bor.resource_seq_num)
498 and jdi.resource_id_new = bsor.resource_id
499 and jdi.replacement_group_num = bsor.replacement_group_num
500 and jdi.routing_op_seq_num = bos.operation_seq_num;
501
502 lv_alt_res_cnt := SQL%ROWCOUNT;
503
504 IF lv_alt_res_cnt > 0 THEN
505
506 FORALL k IN 1..lv_alt_res_cnt
507 UPDATE WSM_LOT_JOB_DTL_INTERFACE set substitute_group_num = lv_alt_sub_grp_num(k),
508 schedule_seq_num = lv_alt_schd_seq_num(k),
509 resource_seq_num = null
510 WHERE rowid = lv_alt_res_rowid(k);
511
512 END IF;
513
514 EXCEPTION WHEN NO_DATA_FOUND THEN NULL;
515 WHEN OTHERS THEN RAISE;
516
517 END;
518
519 /* get details for unscheduled resources and insert them with start_date and completion_date as null */
520
521 BEGIN
522 Select bos.operation_seq_num,
523 bor.resource_id,
524 bor.resource_seq_num,
525 bor.schedule_seq_num,
526 0,
527 bor.substitute_group_num,
528 jdi.load_type
529 , jdi.firm_type -- dsr: added the following 7 columns
530 , jdi.setup_id
531 , jdi.group_sequence_id
532 , jdi.group_sequence_num -- sbala
533 , jdi.batch_id
534 , jdi.max_assigned_units --- sbala
535 , jdi.parent_resource_seq_num -- sbala
536 -- , resource_seq_num
537 -- , schedule_seq_num
538 BULK COLLECT INTO
539 lv_unschd_op_seq_num,
540 lv_unschd_res_id,
541 lv_unschd_res_seq_num,
542 lv_unschd_schd_seq_num,
543 lv_unschd_rep_grp_num,
544 lv_unschd_subs_grp_num,
545 lv_unschd_load_type
546 -- dsr added following 7 lines
547 , lv_unschd_firm_flag
548 , lv_unschd_setup_id
549 , lv_unschd_group_sequence_id
550 , LV_UNSCHD_GROUP_SEQ_NUM
551 , lv_unschd_batch_id
552 , LV_UNSCHD_MAX_ASSIGNED_UNITS
553 , lv_unschd_parent_seq_num
554 -- , lv_unschd_schedule_seq_num
555 from bom_operation_resources bor,
556 bom_operation_sequences bos,
557 wsm_lot_job_dtl_interface jdi
558 Where bor.schedule_flag = 2
559 and bor.schedule_seq_num = jdi.schedule_seq_num
560 and nvl(jdi.replacement_group_num,0) = 0
561 and bos.operation_seq_num = jdi.routing_op_seq_num
562 and bos.effectivity_date <= lv_start_date(n)
563 and NVL(bos.disable_date, lv_start_date(n)) >= lv_start_date(n)
564 and bor.operation_sequence_id = bos.operation_sequence_id
565 and bos.routing_sequence_id = lv_routing_seq_id(n)
566 and jdi.group_id = G_WIP_GROUP_ID
567 and jdi.parent_header_id = lv_header_id(n)
568 -- and jdi.load_type=1
569 and jdi.load_type IN (1, RESOURCE_INSTANCES_OSFM)
570 union all
571 Select bos.operation_seq_num,
572 bsor.resource_id,
573 to_number(null),
574 bsor.schedule_seq_num,
575 bsor.replacement_group_num,
576 bsor.substitute_group_num,
577 jdi.load_type
578 -- dsr: added the following 7 columns
579 , jdi.firm_type
580 , jdi.setup_id
581 , jdi.group_sequence_id
582 , jdi.group_sequence_num --- sbala
583 , jdi.batch_id
584 , jdi.max_assigned_units --- sbala
585 , jdi.parent_resource_seq_num
586 from bom_sub_operation_resources bsor,
587 bom_operation_sequences bos,
588 wsm_lot_job_dtl_interface jdi
589 Where bsor.schedule_flag = 2
590 and bsor.schedule_seq_num = jdi.schedule_seq_num
591 and jdi.replacement_group_num = bsor.replacement_group_num
592 and bos.operation_seq_num = jdi.routing_op_seq_num
593 and bos.effectivity_date <= lv_start_date(n)
594 and NVL(bos.disable_date, lv_start_date(n)) >= lv_start_date(n)
595 and bsor.operation_sequence_id = bos.operation_sequence_id
596 and bos.routing_sequence_id = lv_routing_seq_id(n)
597 and jdi.group_id = G_WIP_GROUP_ID
598 and jdi.parent_header_id = lv_header_id(n)
599 -- and jdi.load_type=1
600 and jdi.load_type IN (1, RESOURCE_INSTANCES_OSFM)
601 union all
602 Select distinct
603 bos.operation_seq_num,
604 bor.resource_id,
605 bor.resource_seq_num,
606 bor.schedule_seq_num,
607 0,
608 bor.substitute_group_num,
609 jdi.load_type
610 -- dsr: added the following 7 columns
611 , jdi.firm_type
612 , jdi.setup_id
613 , jdi.group_sequence_id
614 , jdi.group_sequence_num --- sbala
615 , jdi.batch_id
616 , jdi.max_assigned_units --- sbala
617 , jdi.parent_resource_seq_num --- sbala
618 from bom_operation_resources bor,
619 bom_operation_sequences bos,
620 wsm_lot_job_dtl_interface jdi
621 Where bor.schedule_flag = 2
622 and not exists (select schedule_seq_num from wsm_lot_job_dtl_interface jdi1
623 where jdi1.group_id = G_WIP_GROUP_ID
624 and jdi1.parent_header_id = lv_header_id(n)
625 and jdi1.routing_op_seq_num = bos.operation_seq_num
626 and jdi1.schedule_seq_num = bor.schedule_seq_num)
627 and bos.operation_seq_num = jdi.routing_op_seq_num
628 and bos.effectivity_date <= lv_start_date(n)
629 and NVL(bos.disable_date, lv_start_date(n)) >= lv_start_date(n)
630 and bor.operation_sequence_id = bos.operation_sequence_id
631 and bos.routing_sequence_id = lv_routing_seq_id(n)
632 and jdi.group_id = G_WIP_GROUP_ID
633 and jdi.parent_header_id = lv_header_id(n)
634 -- and jdi.load_type=1 ;
635 and jdi.load_type IN (1, RESOURCE_INSTANCES_OSFM);
636
637 lv_unschd_res_cnt := SQL%ROWCOUNT;
638
639 IF lv_unschd_res_cnt > 0 Then
640
641 For l in 1..lv_unschd_res_cnt LOOP
642
643 Insert INTO WSM_LOT_JOB_DTL_INTERFACE
644 ( last_update_date,
645 last_updated_by,
646 last_update_login,
647 creation_date,
648 created_by,
649 RECORD_ID,
650 group_id,
651 parent_header_id,
652 routing_op_seq_num,
653 resource_seq_num,
654 resource_id_new,
655 schedule_seq_num,
656 substitute_group_num,
657 replacement_group_num,
658 substitution_type,
659 load_type,
660 start_date,
661 completion_date,
662 process_phase,
663 process_status,
664 transaction_date
665 -- dsr: added the following 7 columns
666 , firm_type --- sbala
667 , setup_id
668 , group_sequence_id
669 , group_sequence_num --- sbala
670 , batch_id
671 , max_assigned_units --- sbala
672 , parent_resource_seq_num
673 -- , resource_seq_num
674 -- , schedule_seq_num
675 )
676 SELECT SYSDATE,
677 FND_GLOBAL.USER_ID,
678 FND_GLOBAL.USER_ID,
679 SYSDATE,
680 FND_GLOBAL.USER_ID,
681 WSM_LOT_SM_IFC_HEADER_S.NEXTVAL,
682 G_WIP_GROUP_ID,
683 lv_header_id(n),
684 lv_unschd_op_seq_num(l),
685 lv_unschd_res_seq_num(l),
686 lv_unschd_res_id(l),
687 lv_unschd_schd_seq_num(l),
688 lv_unschd_subs_grp_num(l),
689 lv_unschd_rep_grp_num(l),
690 4,
691 lv_unschd_load_type(l),
692 Null,
693 Null,
694 1,
695 1,
696 sysdate
697 -- dsr: added the following 7 columns
698 , lv_unschd_firm_flag(l)
699 , lv_unschd_setup_id(l)
700 , lv_unschd_group_sequence_id(l)
701 , LV_UNSCHD_GROUP_SEQ_NUM(l)
702 , lv_unschd_batch_id(l)
703 , LV_UNSCHD_MAX_ASSIGNED_UNITS(l)
704 , lv_unschd_parent_seq_num(l)
705 -- , lv_unschd_schedule_seq_num(l)
706 From Dual;
707 END LOOP;
708 END IF;
709
710 EXCEPTION WHEN NO_DATA_FOUND THEN NULL;
711 WHEN OTHERS THEN RAISE;
712
713 END ;
714
715 /* Primary resources for unscheduled operation */
716
717
718 For k in 1..lv_un_op_cnt LOOP
719
720 BEGIN
721
722 select bor.resource_seq_num,
723 bor.schedule_seq_num,
724 bor.resource_id,
725 bor.substitute_group_num
726 Bulk Collect into
727 lv_unres_res_seq_num,
728 lv_unres_schd_seq_num,
729 lv_unres_res_id,
730 lv_unres_sub_grp_num
731 From BOM_OPERATION_SEQUENCES bos,
732 BOM_OPERATION_RESOURCES bor
733 Where bos.routing_sequence_id = lv_routing_seq_id(n)
734 and bos.operation_seq_num = lv_nwk_op_seq_num(k)
735 and bos.effectivity_date <= lv_start_date(n)
736 and NVL(bos.disable_date, lv_start_date(n)) >= lv_start_date(n)
737 and bor.operation_sequence_id = bos.operation_sequence_id;
738
739 lv_unres_cnt := SQL%ROWCOUNT;
740
741 IF lv_unres_cnt > 0 Then
742
743 For l in 1..lv_unres_cnt LOOP
744
745 Insert INTO WSM_LOT_JOB_DTL_INTERFACE
746 ( last_update_date,
747 last_updated_by,
748 last_update_login,
749 creation_date,
750 created_by,
751 RECORD_ID,
752 group_id,
753 parent_header_id,
754 job_op_seq_num,
755 routing_op_seq_num,
756 resource_seq_num,
757 resource_id_new,
758 schedule_seq_num,
759 substitute_group_num,
760 replacement_group_num,
761 substitution_type,
762 load_type,
763 start_date,
764 completion_date,
765 process_phase,
766 process_status,
767 transaction_date)
768 SELECT SYSDATE,
769 FND_GLOBAL.USER_ID,
770 FND_GLOBAL.USER_ID,
771 SYSDATE,
772 FND_GLOBAL.USER_ID,
773 WSM_LOT_SM_IFC_HEADER_S.NEXTVAL,
774 G_WIP_GROUP_ID,
775 lv_header_id(n),
776 null,
777 lv_nwk_op_seq_num(k),
778 lv_unres_res_seq_num(l),
779 lv_unres_res_id(l),
780 lv_unres_schd_seq_num(l),
781 lv_unres_sub_grp_num(l),
782 0,
783 4,
784 1,
785 null,
786 null,
787 1,
788 1,
789 sysdate
790 From Dual;
791
792 /* Bug 3344136
793 Insert INTO WSM_LOT_JOB_DTL_INTERFACE
794 ( last_update_date,
795 last_updated_by,
796 last_update_login,
797 creation_date,
798 created_by,
799 RECORD_ID,
800 group_id,
801 parent_header_id,
802 job_op_seq_num,
803 routing_op_seq_num,
804 resource_seq_num,
805 resource_id_new,
806 substitute_group_num,
807 replacement_group_num,
808 substitution_type,
809 load_type,
810 start_date,
811 completion_date,
812 process_phase,
813 process_status,
814 transaction_date)
815 SELECT SYSDATE,
816 FND_GLOBAL.USER_ID,
817 FND_GLOBAL.USER_ID,
818 SYSDATE,
819 FND_GLOBAL.USER_ID,
820 WSM_LOT_SM_IFC_HEADER_S.NEXTVAL,
821 G_WIP_GROUP_ID,
822 lv_header_id(n),
823 null,
824 lv_nwk_op_seq_num(k),
825 lv_unres_res_seq_num(l),
826 lv_unres_res_id(l),
827 lv_unres_schd_seq_num(l),
828 0,
829 4,
830 4,
831 null,
832 null,
833 2,
834 1,
835 sysdate
836 From Dual;
837 */
838
839 END LOOP;
840 END IF;
841
842 EXCEPTION WHEN NO_DATA_FOUND THEN NULL;
843 WHEN OTHERS THEN RAISE;
844
845 END;
846
847 END LOOP;
848
849
850 END If; /* new jobs */
851
852
853 If lv_job_schedule_type(n) = 6 Then /* existing jobs */
854
855 BEGIN
856
857 Select jdi.rowid,
858 wcor.resource_seq_num,
859 wcor.substitute_group_num,
860 wcor.schedule_seq_num
861 BULK COLLECT INTO
862 lv_res_rowid,
863 lv_res_seq_num,
864 lv_sub_grp_num,
865 lv_schd_seq_num
866 From WSM_LOT_JOB_DTL_INTERFACE jdi,
867 WSM_COPY_OP_RESOURCES wcor
868 Where jdi.group_id = G_WIP_GROUP_ID
869 and jdi.parent_header_id = lv_header_id(n)
870 -- and jdi.load_type in (1,4)
871 and jdi.load_type in (1,4, RESOURCE_INSTANCES_OSFM, RESOURCE_INSTANCE_USAGE_OSFM)
872 and jdi.replacement_group_num = wcor.replacement_group_num
873 -- and nvl(wcor.schedule_seq_num,wcor.resource_seq_num) = jdi.resource_seq_num
874 and wcor.resource_seq_num = jdi.resource_seq_num
875 and wcor.resource_id = jdi.resource_id_new
876 and wcor.operation_seq_num = jdi.ROUTING_OP_SEQ_NUM
877 and jdi.job_op_seq_num is null
878 and wcor.wip_entity_id = lv_wip_entity_id(n);
879
880
881 lv_res_cnt := SQL%ROWCOUNT;
882
883 IF lv_res_cnt > 0 THEN
884
885 FORALL k IN 1..lv_res_cnt
886 UPDATE WSM_LOT_JOB_DTL_INTERFACE set resource_seq_num = lv_res_seq_num(k),
887 substitute_group_num = lv_sub_grp_num(k),
888 schedule_seq_num = lv_schd_seq_num(k)
889 WHERE rowid = lv_res_rowid(k);
890
891 END IF;
892
893 EXCEPTION WHEN NO_DATA_FOUND THEN NULL;
894 WHEN OTHERS THEN RAISE;
895
896 END;
897
898 BEGIN
899
900 Select jdi.rowid,
901 wor.resource_seq_num,
902 wor.substitute_group_num,
903 wor.schedule_seq_num
904 BULK COLLECT INTO
905 lv_cur_op_res_rowid,
906 lv_cur_op_res_seq_num,
907 lv_cur_op_sub_grp_num,
908 lv_cur_op_schd_seq_num
909 From WSM_LOT_JOB_DTL_INTERFACE jdi,
910 wip_operation_resources wor
911 Where jdi.group_id = G_WIP_GROUP_ID
912 and jdi.parent_header_id = lv_header_id(n)
913 -- and jdi.load_type in (1,4)
914 and jdi.load_type in (1,4, RESOURCE_INSTANCES_OSFM, RESOURCE_INSTANCE_USAGE_OSFM)
915 and jdi.replacement_group_num = nvl(wor.replacement_group_num,0)
916 and nvl(wor.schedule_seq_num,wor.resource_seq_num) = jdi.resource_seq_num
917 and wor.resource_id = jdi.resource_id_new
918 and wor.operation_seq_num = jdi.JOB_OP_SEQ_NUM
919 and wor.wip_entity_id = lv_wip_entity_id(n)
920 union all
921 Select jdi.rowid,
922 wsor.resource_seq_num,
923 wsor.substitute_group_num,
924 wsor.schedule_seq_num
925 From WSM_LOT_JOB_DTL_INTERFACE jdi,
926 wip_sub_operation_resources wsor
927 Where jdi.group_id = G_WIP_GROUP_ID
928 and jdi.parent_header_id = lv_header_id(n)
929 -- and jdi.load_type in (1,4)
930 and jdi.load_type in (1,4, RESOURCE_INSTANCES_OSFM, RESOURCE_INSTANCE_USAGE_OSFM)
931 and jdi.replacement_group_num = nvl(wsor.replacement_group_num,0)
932 and nvl(wsor.schedule_seq_num,wsor.resource_seq_num) = jdi.resource_seq_num
933 and wsor.resource_id = jdi.resource_id_new
934 and wsor.operation_seq_num = jdi.JOB_OP_SEQ_NUM
935 and wsor.wip_entity_id = lv_wip_entity_id(n);
936
937 lv_cur_op_res_cnt := SQL%ROWCOUNT;
938
939 IF lv_cur_op_res_cnt > 0 THEN
940
941 FORALL k IN 1..lv_cur_op_res_cnt
942 UPDATE WSM_LOT_JOB_DTL_INTERFACE set substitute_group_num = lv_cur_op_sub_grp_num(k),
943 schedule_seq_num = lv_cur_op_schd_seq_num(k),
944 resource_seq_num = lv_cur_op_res_seq_num(k)
945 WHERE rowid = lv_cur_op_res_rowid(k);
946
947 END IF;
948
949 EXCEPTION WHEN NO_DATA_FOUND THEN NULL;
950 WHEN OTHERS THEN RAISE;
951
952 END;
953
954 BEGIN
955
956 Select wor.operation_seq_num,
957 wor.resource_id,
958 wor.resource_seq_num,
959 wor.schedule_seq_num,
960 wor.replacement_group_num,
961 wor.substitute_group_num,
962 jdi.routing_op_seq_num,
963 jdi.load_type
964 BULK COLLECT INTO
965 lv_unschd_job_op_seq_num,
966 lv_unschd_res_id,
967 lv_unschd_res_seq_num,
968 lv_unschd_schd_seq_num,
969 lv_unschd_rep_grp_num,
970 lv_unschd_subs_grp_num,
971 lv_unschd_rtng_op_seq_num,
972 lv_unschd_load_type
973 from wip_operation_resources wor,
974 wsm_lot_job_dtl_interface jdi
975 where wor.scheduled_flag = 2
976 and wor.schedule_seq_num = jdi.schedule_seq_num
977 and jdi.replacement_group_num = nvl(wor.replacement_group_num,0)
978 and wor.operation_seq_num = jdi.job_op_seq_num
979 and wor.wip_entity_id = lv_wip_entity_id(n)
980 and jdi.group_id = G_WIP_GROUP_ID
981 and jdi.parent_header_id = lv_header_id(n)
982 -- and jdi.load_type=1
983 and jdi.load_type IN (1, RESOURCE_INSTANCES_OSFM)
984 union all
985 Select wsor.operation_seq_num,
986 wsor.resource_id,
987 wsor.resource_seq_num,
988 wsor.schedule_seq_num,
989 wsor.replacement_group_num,
990 wsor.substitute_group_num,
991 jdi.routing_op_seq_num,
992 jdi.load_type
993 from wip_sub_operation_resources wsor,
994 wsm_lot_job_dtl_interface jdi
995 where wsor.scheduled_flag = 2
996 and wsor.schedule_seq_num = jdi.schedule_seq_num
997 and jdi.replacement_group_num = nvl(wsor.replacement_group_num,0)
998 and wsor.operation_seq_num = jdi.job_op_seq_num
999 and wsor.wip_entity_id = lv_wip_entity_id(n)
1000 and jdi.group_id = G_WIP_GROUP_ID
1001 and jdi.parent_header_id = lv_header_id(n)
1002 -- and jdi.load_type=1
1003 and jdi.load_type IN (1, RESOURCE_INSTANCES_OSFM)
1004 union all
1005 Select distinct
1006 wor.operation_seq_num,
1007 wor.resource_id,
1008 wor.resource_seq_num,
1009 wor.schedule_seq_num,
1010 nvl(wor.replacement_group_num,0),
1011 wor.substitute_group_num,
1012 jdi.routing_op_seq_num,
1013 jdi.load_type
1014 from wip_operation_resources wor,
1015 wsm_lot_job_dtl_interface jdi
1016 Where wor.scheduled_flag = 2
1017 and not exists (select schedule_seq_num from wsm_lot_job_dtl_interface jdi1
1018 where jdi1.group_id = G_WIP_GROUP_ID
1019 and jdi1.parent_header_id = lv_header_id(n)
1020 and jdi1.job_op_seq_num = wor.operation_seq_num
1021 and jdi1.schedule_seq_num = wor.schedule_seq_num)
1022 and wor.operation_seq_num = jdi.job_op_seq_num
1023 and wor.wip_entity_id = lv_wip_entity_id(n)
1024 and jdi.group_id = G_WIP_GROUP_ID
1025 and jdi.parent_header_id = lv_header_id(n)
1026 --and jdi.load_type=1;
1027 and jdi.load_type IN (1, RESOURCE_INSTANCES_OSFM);
1028
1029 lv_unschd_res_cnt := SQL%ROWCOUNT;
1030
1031 IF lv_unschd_res_cnt > 0 Then
1032
1033 For l in 1..lv_unschd_res_cnt LOOP
1034
1035 Insert INTO WSM_LOT_JOB_DTL_INTERFACE
1036 ( last_update_date,
1037 last_updated_by,
1038 last_update_login,
1039 creation_date,
1040 created_by,
1041 RECORD_ID,
1042 group_id,
1043 parent_header_id,
1044 JOB_OP_SEQ_NUM,
1045 ROUTING_OP_SEQ_NUM,
1046 resource_seq_num,
1047 resource_id_new,
1048 schedule_seq_num,
1049 substitute_group_num,
1050 replacement_group_num,
1051 substitution_type,
1052 load_type,
1053 start_date,
1054 completion_date,
1055 process_phase,
1056 process_status,
1057 transaction_date
1058 -- dsr: added the following 8 columns
1059 , firm_type --- sbala
1060 , setup_id
1061 , group_sequence_id
1062 , group_sequence_num -- sbala
1063 , batch_id
1064 , max_assigned_units --- sbala
1065 , parent_resource_seq_num
1066 -- , resource_seq_num
1067 -- , schedule_seq_num
1068 )
1069 SELECT SYSDATE,
1070 FND_GLOBAL.USER_ID,
1071 FND_GLOBAL.USER_ID,
1072 SYSDATE,
1073 FND_GLOBAL.USER_ID,
1074 WSM_LOT_SM_IFC_HEADER_S.NEXTVAL,
1075 G_WIP_GROUP_ID,
1076 lv_header_id(n),
1077 lv_unschd_job_op_seq_num(l),
1078 lv_unschd_rtng_op_seq_num(l),
1079 lv_unschd_res_seq_num(l),
1080 lv_unschd_res_id(l),
1081 lv_unschd_schd_seq_num(l),
1082 lv_unschd_subs_grp_num(l),
1083 lv_unschd_rep_grp_num(l),
1084 4,
1085 lv_unschd_load_type(l),
1086 Null,
1087 Null,
1088 1,
1089 1,
1090 sysdate
1091 -- dsr: added the following 8 columns
1092 , lv_unschd_firm_flag(l)
1093 , lv_unschd_setup_id(l)
1094 , lv_unschd_group_sequence_id(l)
1095 , LV_UNSCHD_GROUP_SEQ_NUM(l)
1096 , lv_unschd_batch_id(l)
1097 , LV_UNSCHD_MAX_ASSIGNED_UNITS(l)
1098 , lv_unschd_parent_seq_num(l)
1099 -- , lv_unschd_schedule_seq_num(l)
1100 From Dual;
1101 END LOOP;
1102 END IF;
1103
1104 EXCEPTION WHEN NO_DATA_FOUND THEN NULL;
1105 WHEN OTHERS THEN RAISE;
1106
1107 END;
1108
1109 BEGIN
1110
1111 Select wcor.operation_seq_num,
1112 wcor.resource_id,
1113 wcor.resource_seq_num,
1114 wcor.schedule_seq_num,
1115 wcor.replacement_group_num,
1116 wcor.substitute_group_num,
1117 jdi.load_type
1118 BULK COLLECT INTO lv_unschd_op_seq_num,
1119 lv_unschd_res_id,
1120 lv_unschd_res_seq_num,
1121 lv_unschd_schd_seq_num,
1122 lv_unschd_rep_grp_num,
1123 lv_unschd_subs_grp_num,
1124 lv_unschd_load_type
1125 from wsm_copy_op_resources wcor,
1126 wsm_lot_job_dtl_interface jdi
1127 Where wcor.schedule_flag = 2
1128 and wcor.schedule_seq_num = jdi.schedule_seq_num
1129 and jdi.replacement_group_num = wcor.replacement_group_num
1130 and wcor.operation_seq_num = jdi.ROUTING_op_seq_num
1131 and jdi.JOB_OP_SEQ_NUM is null
1132 and wcor.wip_entity_id = lv_wip_entity_id(n)
1133 and jdi.group_id = G_WIP_GROUP_ID
1134 and jdi.parent_header_id = lv_header_id(n)
1135 -- and jdi.load_type=1
1136 and jdi.load_type IN (1, RESOURCE_INSTANCES_OSFM)
1137 union all
1138 Select distinct
1139 wcor.operation_seq_num,
1140 wcor.resource_id,
1141 wcor.resource_seq_num,
1142 wcor.schedule_seq_num,
1143 nvl(wcor.replacement_group_num,0),
1144 wcor.substitute_group_num,
1145 jdi.load_type
1146 from wsm_copy_op_resources wcor,
1147 wsm_lot_job_dtl_interface jdi
1148 Where wcor.schedule_flag = 2
1149 and not exists (select schedule_seq_num from wsm_lot_job_dtl_interface jdi1
1150 where jdi1.group_id = G_WIP_GROUP_ID
1151 and jdi1.parent_header_id = lv_header_id(n)
1152 and jdi1.ROUTING_op_seq_num = wcor.operation_seq_num
1153 and jdi1.schedule_seq_num = wcor.schedule_seq_num)
1154 and wcor.operation_seq_num = jdi.routing_op_seq_num
1155 and wcor.wip_entity_id = lv_wip_entity_id(n)
1156 and jdi.group_id = G_WIP_GROUP_ID
1157 and jdi.parent_header_id = lv_header_id(n)
1158 and jdi.JOB_OP_SEQ_NUM is null
1159 -- and jdi.load_type =1;
1160 and jdi.load_type IN (1, RESOURCE_INSTANCES_OSFM);
1161
1162
1163 lv_unschd_res_cnt := SQL%ROWCOUNT;
1164
1165 IF lv_unschd_res_cnt > 0 Then
1166
1167 For l in 1..lv_unschd_res_cnt LOOP
1168
1169 Insert INTO WSM_LOT_JOB_DTL_INTERFACE
1170 ( last_update_date,
1171 last_updated_by,
1172 last_update_login,
1173 creation_date,
1174 created_by,
1175 RECORD_ID,
1176 group_id,
1177 parent_header_id,
1178 JOB_OP_SEQ_NUM,
1179 ROUTING_OP_SEQ_NUM,
1180 resource_seq_num,
1181 resource_id_new,
1182 schedule_seq_num,
1183 substitute_group_num,
1184 replacement_group_num,
1185 substitution_type,
1186 load_type,
1187 start_date,
1188 completion_date,
1189 process_phase,
1190 process_status,
1191 transaction_date)
1192 SELECT SYSDATE,
1193 FND_GLOBAL.USER_ID,
1194 FND_GLOBAL.USER_ID,
1195 SYSDATE,
1196 FND_GLOBAL.USER_ID,
1197 WSM_LOT_SM_IFC_HEADER_S.NEXTVAL,
1198 G_WIP_GROUP_ID,
1199 lv_header_id(n),
1200 null,
1201 lv_unschd_op_seq_num(l),
1202 lv_unschd_res_seq_num(l),
1203 lv_unschd_res_id(l),
1204 lv_unschd_schd_seq_num(l),
1205 lv_unschd_subs_grp_num(l),
1206 lv_unschd_rep_grp_num(l),
1207 4,
1208 lv_unschd_load_type(l),
1209 Null,
1210 Null,
1211 1,
1212 1,
1213 sysdate
1214 From Dual;
1215
1216 END LOOP;
1217 END IF;
1218
1219
1220 EXCEPTION WHEN NO_DATA_FOUND THEN NULL;
1221 WHEN OTHERS THEN RAISE;
1222
1223 END;
1224
1225
1226 For k in 1..lv_un_op_cnt LOOP
1227
1228 BEGIN
1229
1230 select wor.resource_seq_num,
1231 wor.schedule_seq_num,
1232 wor.substitute_group_num,
1233 wor.replacement_group_num,
1234 wor.resource_id
1235 Bulk Collect into
1236 lv_unres_res_seq_num,
1237 lv_unres_schd_seq_num,
1238 lv_unres_sub_grp_num,
1239 lv_unres_rep_grp_num,
1240 lv_unres_res_id
1241 From WIP_OPERATION_RESOURCES wor
1242 Where wor.operation_seq_num = lv_nwk_job_op_seq_num(k)
1243 and wor.wip_entity_id = lv_wip_entity_id(n);
1244
1245 lv_unres_cnt := SQL%ROWCOUNT;
1246
1247 IF lv_unres_cnt > 0 Then
1248
1249 For l in 1..lv_unres_cnt LOOP
1250
1251 Insert INTO WSM_LOT_JOB_DTL_INTERFACE
1252 ( last_update_date,
1253 last_updated_by,
1254 last_update_login,
1255 creation_date,
1256 created_by,
1257 RECORD_ID,
1258 group_id,
1259 parent_header_id,
1260 job_op_seq_num,
1261 routing_op_seq_num,
1262 resource_seq_num,
1263 resource_id_new,
1264 schedule_seq_num,
1265 substitute_group_num,
1266 replacement_group_num,
1267 substitution_type,
1268 load_type,
1269 start_date,
1270 completion_date,
1271 process_phase,
1272 process_status,
1273 transaction_date)
1274 SELECT SYSDATE,
1275 FND_GLOBAL.USER_ID,
1276 FND_GLOBAL.USER_ID,
1277 SYSDATE,
1278 FND_GLOBAL.USER_ID,
1279 WSM_LOT_SM_IFC_HEADER_S.NEXTVAL,
1280 G_WIP_GROUP_ID,
1281 lv_header_id(n),
1282 lv_nwk_job_op_seq_num(k),
1283 lv_nwk_op_seq_num(k),
1284 lv_unres_res_seq_num(l),
1285 lv_unres_res_id(l),
1286 lv_unres_schd_seq_num(l),
1287 lv_unres_sub_grp_num(l),
1288 0,
1289 4,
1290 1,
1291 null,
1292 null,
1293 1,
1294 1,
1295 sysdate
1296 From Dual;
1297
1298 /* Bug 3344136
1299 Insert INTO WSM_LOT_JOB_DTL_INTERFACE
1300 ( last_update_date,
1301 last_updated_by,
1302 last_update_login,
1303 creation_date,
1304 created_by,
1305 RECORD_ID,
1306 group_id,
1307 parent_header_id,
1308 job_op_seq_num,
1309 routing_op_seq_num,
1310 resource_seq_num,
1311 resource_id_new,
1312 substitute_group_num,
1313 replacement_group_num,
1314 substitution_type,
1315 load_type,
1316 start_date,
1317 completion_date,
1318 process_phase,
1319 process_status,
1320 transaction_date)
1321 SELECT SYSDATE,
1322 FND_GLOBAL.USER_ID,
1323 FND_GLOBAL.USER_ID,
1324 SYSDATE,
1325 FND_GLOBAL.USER_ID,
1326 WSM_LOT_SM_IFC_HEADER_S.NEXTVAL,
1327 G_WIP_GROUP_ID,
1328 lv_header_id(n),
1329 lv_nwk_job_op_seq_num(k),
1330 lv_nwk_op_seq_num(k),
1331 lv_unres_res_seq_num(l),
1332 lv_unres_res_id(l),
1333 lv_unres_schd_seq_num(l),
1334 0,
1335 4,
1336 4,
1337 null,
1338 null,
1339 2,
1340 1,
1341 sysdate
1342 From Dual;
1343 */
1344
1345 END LOOP;
1346 END IF;
1347
1348 EXCEPTION WHEN NO_DATA_FOUND THEN NULL;
1349 WHEN OTHERS THEN RAISE;
1350
1351 END;
1352
1353 -- dsr start
1354 For k in 1..lv_un_op_cnt LOOP
1355
1356 BEGIN
1357
1358 /* select wori.resource_seq_num,
1359 wori.schedule_seq_num,
1360 wori.substitute_group_num,
1361 wori.replacement_group_num,
1362 wori.resource_id
1363 Bulk Collect into
1364 lv_unres_res_seq_num,
1365 lv_unres_schd_seq_num,
1366 lv_unres_sub_grp_num,
1367 lv_unres_rep_grp_num,
1368 lv_unres_res_id
1369 From WIP_OP_RESOURCE_INSTANCES wori
1370 Where wori.operation_seq_num = lv_nwk_job_op_seq_num(k)
1371 and wori.wip_entity_id = lv_wip_entity_id(n);*/
1372
1373 select wor.resource_seq_num,
1374 wor.schedule_seq_num,
1375 wor.substitute_group_num,
1376 wor.replacement_group_num,
1377 wor.resource_id
1378 Bulk Collect into
1379 lv_unres_res_seq_num,
1380 lv_unres_schd_seq_num,
1381 lv_unres_sub_grp_num,
1382 lv_unres_rep_grp_num,
1383 lv_unres_res_id
1384 From WIP_OPERATION_RESOURCES wor
1385 Where wor.operation_seq_num = lv_nwk_job_op_seq_num(k)
1386 and wor.wip_entity_id = lv_wip_entity_id(n);
1387
1388 lv_unres_cnt := SQL%ROWCOUNT;
1389
1390 IF lv_unres_cnt > 0 Then
1391
1392 For l in 1..lv_unres_cnt LOOP
1393
1394 Insert INTO WSM_LOT_JOB_DTL_INTERFACE
1395 ( last_update_date,
1396 last_updated_by,
1397 last_update_login,
1398 creation_date,
1399 created_by,
1400 RECORD_ID,
1401 group_id,
1402 parent_header_id,
1403 job_op_seq_num,
1404 routing_op_seq_num,
1405 resource_seq_num,
1406 resource_id_new,
1407 substitute_group_num,
1408 replacement_group_num,
1409 substitution_type,
1410 load_type,
1411 start_date,
1412 completion_date,
1413 process_phase,
1414 process_status,
1415 transaction_date)
1416 SELECT SYSDATE,
1417 FND_GLOBAL.USER_ID,
1418 FND_GLOBAL.USER_ID,
1419 SYSDATE,
1420 FND_GLOBAL.USER_ID,
1421 WSM_LOT_SM_IFC_HEADER_S.NEXTVAL,
1422 G_WIP_GROUP_ID,
1423 lv_header_id(n),
1424 lv_nwk_job_op_seq_num(k),
1425 lv_nwk_op_seq_num(k),
1426 lv_unres_res_seq_num(l),
1427 lv_unres_res_id(l),
1428 lv_unres_schd_seq_num(l),
1429 0,
1430 4,
1431 RESOURCE_INSTANCES_OSFM,
1432 null,
1433 null,
1434 1,
1435 1,
1436 sysdate
1437 From Dual;
1438
1439 END LOOP;
1440 END IF;
1441
1442 EXCEPTION WHEN NO_DATA_FOUND THEN NULL;
1443 WHEN OTHERS THEN RAISE;
1444
1445 END;
1446 END LOOP;
1447 -- dsr end
1448
1449
1450 BEGIN
1451
1452 select wcor.resource_seq_num,
1453 wcor.schedule_seq_num,
1454 wcor.substitute_group_num,
1455 wcor.replacement_group_num,
1456 wcor.resource_id
1457 Bulk Collect into
1458 lv_unres_res_seq_num,
1459 lv_unres_schd_seq_num,
1460 lv_unres_sub_grp_num,
1461 lv_unres_rep_grp_num,
1462 lv_unres_res_id
1463 From WSM_COPY_OP_RESOURCES wcor
1464 Where wcor.operation_seq_num = lv_nwk_op_seq_num(k)
1465 and wcor.wip_entity_id = lv_wip_entity_id(n)
1466 and lv_nwk_job_op_seq_num(k) is null;
1467
1468 lv_unres_cnt := SQL%ROWCOUNT;
1469
1470 IF lv_unres_cnt > 0 Then
1471
1472 For l in 1..lv_unres_cnt LOOP
1473
1474 Insert INTO WSM_LOT_JOB_DTL_INTERFACE
1475 ( last_update_date,
1476 last_updated_by,
1477 last_update_login,
1478 creation_date,
1479 created_by,
1480 RECORD_ID,
1481 group_id,
1482 parent_header_id,
1483 job_op_seq_num,
1484 routing_op_seq_num,
1485 resource_seq_num,
1486 resource_id_new,
1487 schedule_seq_num,
1488 substitute_group_num,
1489 replacement_group_num,
1490 substitution_type,
1491 load_type,
1492 start_date,
1493 completion_date,
1494 process_phase,
1495 process_status,
1496 transaction_date)
1497 SELECT SYSDATE,
1498 FND_GLOBAL.USER_ID,
1499 FND_GLOBAL.USER_ID,
1500 SYSDATE,
1501 FND_GLOBAL.USER_ID,
1502 WSM_LOT_SM_IFC_HEADER_S.NEXTVAL,
1503 G_WIP_GROUP_ID,
1504 lv_header_id(n),
1505 null,
1506 lv_nwk_op_seq_num(k),
1507 lv_unres_res_seq_num(l),
1508 lv_unres_res_id(l),
1509 lv_unres_schd_seq_num(l),
1510 lv_unres_sub_grp_num(l),
1511 0,
1512 4,
1513 1,
1514 null,
1515 null,
1516 1,
1517 1,
1518 sysdate
1519 From Dual;
1520 /*Bug 3344136
1521 Insert INTO WSM_LOT_JOB_DTL_INTERFACE
1522 ( last_update_date,
1523 last_updated_by,
1524 last_update_login,
1525 creation_date,
1526 created_by,
1527 RECORD_ID,
1528 group_id,
1529 parent_header_id,
1530 job_op_seq_num,
1531 routing_op_seq_num,
1532 resource_seq_num,
1533 resource_id_new,
1534 substitute_group_num,
1535 replacement_group_num,
1536 substitution_type,
1537 load_type,
1538 start_date,
1539 completion_date,
1540 process_phase,
1541 process_status,
1542 transaction_date)
1543 SELECT SYSDATE,
1544 FND_GLOBAL.USER_ID,
1545 FND_GLOBAL.USER_ID,
1546 SYSDATE,
1547 FND_GLOBAL.USER_ID,
1548 WSM_LOT_SM_IFC_HEADER_S.NEXTVAL,
1549 G_WIP_GROUP_ID,
1550 lv_header_id(n),
1551 null,
1552 lv_nwk_op_seq_num(k),
1553 lv_unres_res_seq_num(l),
1554 lv_unres_res_id(l),
1555 lv_unres_schd_seq_num(l),
1556 0,
1557 4,
1558 4,
1559 null,
1560 null,
1561 2,
1562 1,
1563 sysdate
1564 From Dual;
1565 */
1566 END LOOP;
1567 END IF;
1568
1569 EXCEPTION WHEN NO_DATA_FOUND THEN NULL;
1570 WHEN OTHERS THEN RAISE;
1571
1572 END;
1573
1574 -- dsr start
1575
1576 BEGIN
1577
1578 /* select wcori.resource_seq_num,
1579 wcori.schedule_seq_num,
1580 wcori.substitute_group_num,
1581 wcori.replacement_group_num,
1582 wcori.resource_id
1583 Bulk Collect into
1584 lv_unres_res_seq_num,
1585 lv_unres_schd_seq_num,
1586 lv_unres_sub_grp_num,
1587 lv_unres_rep_grp_num,
1588 lv_unres_res_id
1589 From WSM_COPY_OP_RESOURCE_INSTANCES wcori
1590 Where wcori.operation_seq_num = lv_nwk_op_seq_num(k)
1591 and wcori.wip_entity_id = lv_wip_entity_id(n)
1592 and lv_nwk_job_op_seq_num(k) is null;*/
1593
1594 select wcor.resource_seq_num,
1595 wcor.schedule_seq_num,
1596 wcor.substitute_group_num,
1597 wcor.replacement_group_num,
1598 wcor.resource_id
1599 Bulk Collect into
1600 lv_unres_res_seq_num,
1601 lv_unres_schd_seq_num,
1602 lv_unres_sub_grp_num,
1603 lv_unres_rep_grp_num,
1604 lv_unres_res_id
1605 From WSM_COPY_OP_RESOURCES wcor
1606 Where wcor.operation_seq_num = lv_nwk_op_seq_num(k)
1607 and wcor.wip_entity_id = lv_wip_entity_id(n)
1608 and lv_nwk_job_op_seq_num(k) is null;
1609
1610 lv_unres_cnt := SQL%ROWCOUNT;
1611
1612 IF lv_unres_cnt > 0 Then
1613
1614 For l in 1..lv_unres_cnt LOOP
1615
1616 Insert INTO WSM_LOT_JOB_DTL_INTERFACE
1617 ( last_update_date,
1618 last_updated_by,
1619 last_update_login,
1620 creation_date,
1621 created_by,
1622 RECORD_ID,
1623 group_id,
1624 parent_header_id,
1625 job_op_seq_num,
1626 routing_op_seq_num,
1627 resource_seq_num,
1628 resource_id_new,
1629 substitute_group_num,
1630 replacement_group_num,
1631 substitution_type,
1632 load_type,
1633 start_date,
1634 completion_date,
1635 process_phase,
1636 process_status,
1637 transaction_date)
1638 SELECT SYSDATE,
1639 FND_GLOBAL.USER_ID,
1640 FND_GLOBAL.USER_ID,
1641 SYSDATE,
1642 FND_GLOBAL.USER_ID,
1643 WSM_LOT_SM_IFC_HEADER_S.NEXTVAL,
1644 G_WIP_GROUP_ID,
1645 lv_header_id(n),
1646 null,
1647 lv_nwk_op_seq_num(k),
1648 lv_unres_res_seq_num(l),
1649 lv_unres_res_id(l),
1650 lv_unres_schd_seq_num(l),
1651 0,
1652 4,
1653 RESOURCE_INSTANCES_OSFM,
1654 null,
1655 null,
1656 1,
1657 1,
1658 sysdate
1659 From Dual;
1660 END LOOP;
1661 END IF;
1662
1663 EXCEPTION WHEN NO_DATA_FOUND THEN NULL;
1664 WHEN OTHERS THEN RAISE;
1665
1666 END;
1667
1668 -- dsr end
1669
1670 END LOOP;
1671
1672 END If;
1673
1674 END IF;
1675
1676 END;
1677
1678 END LOOP;
1679
1680 EXCEPTION WHEN NO_DATA_FOUND THEN NULL;
1681 WHEN OTHERS THEN RAISE;
1682
1683 END MODIFY_LJ_RES_REQ;
1684
1685
1686 PROCEDURE MODIFY_LJ_COMP_REQ
1687 IS
1688
1689
1690 Cursor C1 is
1691 Select a.header_id,min(bos.operation_seq_num) new_op_seq
1692 from WSM_LOT_JOB_INTERFACE a,
1693 WSM_LOT_JOB_DTL_INTERFACE b,
1694 bom_operation_Sequences bos,
1695 bom_operational_routings bor
1696 where a.group_id = b.group_id
1697 and a.group_id = G_WIP_GROUP_ID
1698 and a.header_id = b.parent_header_id
1699 and a.load_type = 5
1700 and a.primary_item_id = bor.assembly_item_id
1701 and nvl(bor.alternate_routing_Designator,0) = nvl(a.alternate_routing_designator,0)
1702 and bor.common_routing_Sequence_id = bos.routing_Sequence_id
1703 and b.load_type = 2
1704 and b.substitution_type = 4
1705 and a.source_code = 'MSC'
1706 and b.routing_op_seq_num = 1
1707 and ( bos.disable_date IS NULL
1708 OR trunc(bos.disable_date) >= trunc(nvl(a.bom_revision_date,a.first_unit_start_date))
1709 )
1710 group by a.header_id;
1711
1712
1713 Cursor C2 is
1714 select sum(b.QUANTITY_PER_ASSEMBLY) qty_per_assy,
1715 sum(b.REQUIRED_QUANTITY) reqd_qty,
1716 sum(b.MPS_REQUIRED_QUANTITY) mps_reqd_qty,
1717 b.group_id,
1718 b.parent_header_id,
1719 b.INVENTORY_ITEM_ID_NEW,
1720 b.ROUTING_OP_SEQ_NUM
1721 from WSM_LOT_JOB_INTERFACE a,
1722 WSM_LOT_JOB_DTL_INTERFACE b
1723 where a.source_code = 'MSC'
1724 and a.group_id = G_WIP_GROUP_ID
1725 and a.group_id = b.group_id
1726 and a.header_id = b.parent_header_id
1727 and b.load_type = 2
1728 and b.substitution_type = 4
1729 and b.process_phase = 1
1730 and b.process_status = 1
1731 group by b.group_id,
1732 b.parent_header_id,
1733 b.INVENTORY_ITEM_ID_NEW,
1734 b.ROUTING_OP_SEQ_NUM,
1735 b.COMPONENT_SEQUENCE_ID,
1736 b.PRIMARY_ITEM_ID,
1737 b.SRC_PHANTOM_ITEM_ID;
1738
1739 Cursor C3 is
1740 select b.rowid
1741 from WSM_LOT_JOB_INTERFACE a,
1742 WSM_LOT_JOB_DTL_INTERFACE b
1743 where a.source_code = 'MSC'
1744 and a.group_id = G_WIP_GROUP_ID
1745 and a.group_id = b.group_id
1746 and a.header_id = b.parent_header_id
1747 and b.load_type = 2
1748 and b.substitution_type = 4
1749 and b.process_phase = 1
1750 and b.process_status = 1
1751 and b.rowid not in (select min(c.rowid)
1752 from WSM_LOT_JOB_DTL_INTERFACE c
1753 where b.group_id = c.group_id
1754 and b.parent_header_id = c.parent_header_id
1755 and b.INVENTORY_ITEM_ID_NEW = c.INVENTORY_ITEM_ID_NEW
1756 and b.ROUTING_OP_SEQ_NUM = c.ROUTING_OP_SEQ_NUM
1757 and nvl(b.COMPONENT_SEQUENCE_ID,0) = nvl(c.COMPONENT_SEQUENCE_ID,0)
1758 and nvl(b.PRIMARY_ITEM_ID ,0) = nvl(c.PRIMARY_ITEM_ID ,0)
1759 and nvl(b.SRC_PHANTOM_ITEM_ID,0)= nvl(c.SRC_PHANTOM_ITEM_ID,0)
1760 and b.load_type = c.load_type
1761 and b.substitution_type = c.substitution_type
1762 and b.process_phase = c.process_phase
1763 and b.process_status = c.process_status );
1764
1765
1766 Begin
1767
1768 For I in C1
1769
1770 loop
1771 update WSM_LOT_JOB_DTL_INTERFACE
1772 set routing_op_Seq_num = I.new_op_seq
1773 where parent_header_id = I.header_id
1774 and routing_op_seq_num = 1
1775 and group_id = G_WIP_GROUP_ID
1776 and load_type = 2
1777 and substitution_type = 4;
1778
1779 End loop;
1780
1781 For J in C2
1782
1783 loop
1784 update WSM_LOT_JOB_DTL_INTERFACE
1785 set QUANTITY_PER_ASSEMBLY = J.qty_per_assy,
1786 REQUIRED_QUANTITY = J.reqd_qty,
1787 MPS_REQUIRED_QUANTITY = J.mps_reqd_qty
1788 where group_id = J.group_id
1789 and parent_header_id = J.parent_header_id
1790 and INVENTORY_ITEM_ID_NEW = J.INVENTORY_ITEM_ID_NEW
1791 and ROUTING_OP_SEQ_NUM = J.ROUTING_OP_SEQ_NUM
1792 and load_type = 2
1793 and substitution_type = 4
1794 and process_phase = 1
1795 and process_status = 1;
1796
1797 End loop;
1798
1799 For K in C3
1800
1801 loop
1802
1803 delete WSM_LOT_JOB_DTL_INTERFACE
1804 where rowid = K.rowid;
1805
1806 End loop;
1807
1808
1809 End MODIFY_LJ_COMP_REQ;
1810
1811
1812 PROCEDURE LD_LOT_JOB_SCHEDULE_INTERFACE
1813 ( o_request_id OUT NOCOPY NUMBER)
1814 IS
1815 lv_sqlstmt VARCHAR2(4000);
1816 lv_instance_id NUMBER;
1817 lv_dblink VARCHAR2(128);
1818 lv_result BOOLEAN;
1819
1820 lv_dummy INTEGER;
1821 BEGIN
1822
1823 select DECODE( A2M_DBLINK,
1824 NULL, ' ',
1825 '@'||A2M_DBLINK),
1826 INSTANCE_ID
1827 into lv_dblink,
1828 lv_instance_id
1829 from MRP_AP_APPS_INSTANCES_ALL
1830 where instance_id = v_instance_id
1831 and instance_code = v_instance_code
1832 and nvl(a2m_dblink,NULL_DBLINK) = nvl(v_dblink,NULL_DBLINK)
1833 and ALLOW_RELEASE_FLAG=1;
1834
1835
1836 lv_sqlstmt:=
1837 'INSERT INTO WSM_LOT_JOB_INTERFACE'
1838 ||' ( LAST_UPDATE_DATE,'
1839 ||' MODE_FLAG,'
1840 ||' LAST_UPDATED_BY,'
1841 ||' CREATION_DATE,'
1842 ||' CREATED_BY,'
1843 ||' LAST_UPDATE_LOGIN,'
1844 ||' GROUP_ID,'
1845 ||' SOURCE_CODE,'
1846 ||' SOURCE_LINE_ID,'
1847 ||' ORGANIZATION_ID,'
1848 ||' LOAD_TYPE,'
1849 ||' STATUS_TYPE,'
1850 ||' FIRST_UNIT_START_DATE,'
1851 ||' LAST_UNIT_COMPLETION_DATE,'
1852 ||' PROCESSING_WORK_DAYS,'
1853 ||' DAILY_PRODUCTION_RATE,'
1854 ||' LINE_ID,'
1855 ||' PRIMARY_ITEM_ID,'
1856 ||' BOM_REVISION_DATE,'
1857 ||' ROUTING_REVISION_DATE,'
1858 ||' CLASS_CODE,'
1859 ||' JOB_NAME,'
1860 ||' FIRM_PLANNED_FLAG,'
1861 ||' ALTERNATE_ROUTING_DESIGNATOR,'
1862 ||' ALTERNATE_BOM_DESIGNATOR,'
1863 ||' DEMAND_CLASS,'
1864 ||' START_QUANTITY,'
1865 ||' WIP_ENTITY_ID,'
1866 ||' PROCESS_PHASE,'
1867 ||' PROCESS_STATUS,'
1868 ||' SCHEDULE_GROUP_ID,'
1869 ||' BUILD_SEQUENCE,'
1870 ||' PROJECT_ID,'
1871 ||' TASK_ID,'
1872 ||' NET_QUANTITY,'
1873 ||' END_ITEM_UNIT_NUMBER,'
1874 ||' BOM_REFERENCE_ID,'
1875 ||' ROUTING_REFERENCE_ID,'
1876 ||' ALLOW_EXPLOSION,'
1877 ||' SCHEDULING_METHOD,'
1878 ||' HEADER_ID,'
1879 ||' INTERFACE_ID, '
1880 -- dsr added 2 new columns
1881 ||' priority,'
1882 ||' due_date)'
1883 ||' SELECT'
1884 ||' SYSDATE,'
1885 ||' 1,'
1886 ||' FND_GLOBAL.USER_ID,'
1887 ||' SYSDATE,'
1888 ||' FND_GLOBAL.USER_ID,'
1889 ||' LAST_UPDATE_LOGIN,'
1890 ||' DECODE( ORGANIZATION_TYPE,'
1891 ||' 1,'||TO_CHAR(G_WIP_GROUP_ID)
1892 ||',2,'||TO_CHAR(G_OPM_WIP_GROUP_ID)||'),'
1893 ||' SOURCE_CODE,'
1894 ||' SOURCE_LINE_ID,'
1895 ||' ORGANIZATION_ID,'
1896 ||' LOAD_TYPE,'
1897 ||' STATUS_TYPE,'
1898 -- dsr ||' FIRST_UNIT_START_DATE,'
1899 ||' FIRST_UNIT_START_DATE,'
1900 ||' LAST_UNIT_COMPLETION_DATE,'
1901 ||' PROCESSING_WORK_DAYS,'
1902 ||' DAILY_PRODUCTION_RATE,'
1903 ||' LINE_ID,'
1904 -- dsr ||' PRIMARY_ITEM_ID,'
1905 ||' DECODE(PRIMARY_ITEM_ID, -1001, NULL, PRIMARY_ITEM_ID),'
1906 ||' BOM_REVISION_DATE,'
1907 ||' ROUTING_REVISION_DATE,'
1908 ||' CLASS_CODE,'
1909 ||' JOB_NAME,'
1910 ||' FIRM_PLANNED_FLAG,'
1911 ||' ALTERNATE_ROUTING_DESIGNATOR,'
1912 ||' ALTERNATE_BOM_DESIGNATOR,'
1913 ||' DEMAND_CLASS,'
1914 ||' decode(status_type,7,0,START_QUANTITY),'
1915 ||' TRUNC(WIP_ENTITY_ID/2),' /* decode wip_entity_id */
1916 ||' PROCESS_PHASE,'
1917 ||' PROCESS_STATUS,'
1918 ||' SCHEDULE_GROUP_ID,'
1919 ||' BUILD_SEQUENCE,'
1920 ||' PROJECT_ID,'
1921 ||' TASK_ID,'
1922 ||' decode(status_type,7,0,NET_QUANTITY),'
1923 ||' END_ITEM_UNIT_NUMBER,'
1924 ||' DECODE( ORGANIZATION_TYPE,'
1925 ||' 2,TRUNC(BOM_REFERENCE_ID/2),' /* decode bill_sequence_id */
1926 ||' BOM_REFERENCE_ID),' /* for OPM only */
1927 ||' DECODE( ORGANIZATION_TYPE,'
1928 ||' 2,TRUNC(ROUTING_REFERENCE_ID/2),' /* decode routing_sequence_id */
1929 ||' ROUTING_REFERENCE_ID),' /* for OPM only */
1930 ||' BILL_RTG_EXPLOSION_FLAG,'
1931 ||' decode(:var_lot_job_copy_rout,2,decode(LOAD_TYPE,6,2,3),1),'
1932 ||' WSM_LOT_SM_IFC_HEADER_S.NEXTVAL,'
1933 ||' HEADER_ID, '
1934 -- dsr added 2 new columns
1935 ||' Schedule_priority,'
1936 ||' Requested_completion_date '
1937 ||' FROM MSC_WIP_JOB_SCHEDULE_INTERFACE'||lv_dblink
1938 ||' WHERE SR_INSTANCE_ID= :lv_instance_id'
1939 ||' AND GROUP_ID = :G_WIP_GROUP_ID'
1940 ||' AND nvl(CFM_ROUTING_FLAG,0) = 3' ;
1941
1942 EXECUTE IMMEDIATE lv_sqlstmt USING var_lot_job_copy_rout,lv_instance_id,G_WIP_GROUP_ID;
1943
1944 BEGIN
1945
1946 lv_sqlstmt:=
1947 'INSERT INTO WSM_LOT_JOB_DTL_INTERFACE'
1948 || '(RECORD_ID, '
1949 || 'GROUP_ID, '
1950 || 'PARENT_HEADER_ID, '
1951 || 'LOAD_TYPE, '
1952 || 'SUBSTITUTION_TYPE, '
1953 || 'PROCESS_PHASE, '
1954 || 'PROCESS_STATUS, '
1955 || 'JOB_OP_SEQ_NUM, '
1956 || 'ROUTING_OP_SEQ_NUM, '
1957 || 'NEXT_ROUTING_OP_SEQ_NUM, '
1958 || 'RESOURCE_SEQ_NUM, '
1959 || 'SCHEDULE_SEQ_NUM, '
1960 || 'RESOURCE_ID_NEW, '
1961 || 'ASSIGNED_UNITS, '
1962 || 'START_DATE, '
1963 || 'COMPLETION_DATE, '
1964 || 'REPLACEMENT_GROUP_NUM, '
1965 || 'INVENTORY_ITEM_ID_NEW, '
1966 || 'PRIMARY_ITEM_ID, '
1967 || 'SRC_PHANTOM_ITEM_ID, '
1968 || 'COMPONENT_SEQUENCE_ID, '
1969 || 'DATE_REQUIRED, '
1970 || 'REQUIRED_QUANTITY, '
1971 || 'BASIS_TYPE, '
1972 || 'QUANTITY_PER_ASSEMBLY, '
1973 || 'MPS_REQUIRED_QUANTITY, '
1974 || 'MPS_DATE_REQUIRED, '
1975 || 'SCHEDULED_QUANTITY,'
1976 || 'OPERATION_START_DATE, '
1977 || 'OPERATION_COMPLETION_DATE, '
1978 || 'TRANSACTION_DATE, '
1979 || 'LAST_UPDATE_DATE, '
1980 || 'LAST_UPDATED_BY, '
1981 || 'CREATION_DATE, '
1982 || 'CREATED_BY, '
1983 || 'LAST_UPDATE_LOGIN, '
1984 || 'REQUEST_ID, '
1985 || 'PROGRAM_APPLICATION_ID, '
1986 || 'PROGRAM_ID, '
1987 || 'PROGRAM_UPDATE_DATE, '
1988 -- dsr added 9 new columns
1989 ||' Serial_number_new,' -- rawasthi changed the column from serial_number to Serial_number_new
1990 ||' Setup_id,'
1991 ||' firm_type,'
1992 ||' Group_Sequence_id,'
1993 ||' Group_Sequence_num,'
1994 ||' Batch_Id,'
1995 ||' instance_id_new,'
1996 -- ||' Charge_number,'
1997 ||' Max_Assigned_Units,' -- dsr
1998 ||' USAGE_RATE_OR_AMOUNT,'
1999 ||' SCHEDULED_FLAG ,'
2000 ||' PARENT_RESOURCE_SEQ_NUM) ' -- dsr
2001 || ' SELECT'
2002 ||' WSM_LOT_SM_IFC_HEADER_S.NEXTVAL,'
2003 || G_WIP_GROUP_ID || ','
2004 ||' PARENT_HEADER_ID,'
2005 ||' LOAD_TYPE,'
2006 ||' SUBSTITUTION_TYPE,'
2007 ||' PROCESS_PHASE,'
2008 ||' PROCESS_STATUS,'
2009 ||' JOB_OP_SEQ_NUM,'
2010 ||' OPERATION_SEQ_NUM,'
2011 ||' NEXT_ROUTING_OP_SEQ_NUM,'
2012 ||' RESOURCE_SEQ_NUM,'
2013 || 'SCHEDULE_SEQ_NUM, '
2014 ||' TRUNC(RESOURCE_ID_NEW/2),'
2015 ||' ASSIGNED_UNITS,'
2016 ||' START_DATE, '
2017 ||' COMPLETION_DATE,'
2018 ||' ALTERNATE_NUM,'
2019 ||' INVENTORY_ITEM_ID_NEW,'
2020 ||' PRIMARY_COMPONENT_ID,'
2021 ||' SOURCE_PHANTOM_ID,'
2022 ||' COMPONENT_SEQ_ID/2,'
2023 ||' DATE_REQUIRED,'
2024 ||' REQUIRED_QUANTITY,'
2025 ||' BASIS_TYPE,'
2026 ||' QUANTITY_PER_ASSEMBLY,'
2027 ||' MPS_REQUIRED_QUANTITY,'
2028 ||' MPS_DATE_REQUIRED,'
2029 ||' SCHEDULED_QUANTITY,'
2030 ||' FIRST_UNIT_START_DATE,'
2031 ||' LAST_UNIT_COMPLETION_DATE,'
2032 ||' SYSDATE,'
2033 ||' LAST_UPDATE_DATE,'
2034 ||' LAST_UPDATED_BY,'
2035 ||' CREATION_DATE,'
2036 ||' CREATED_BY,'
2037 ||' LAST_UPDATE_LOGIN,'
2038 ||' REQUEST_ID,'
2039 ||' PROGRAM_APPLICATION_ID,'
2040 ||' PROGRAM_ID,'
2041 ||' PROGRAM_UPDATE_DATE, '
2042 -- dsr added new columns
2043 ||' Serial_number,'
2044 ||' Setup_id,'
2045 ||' firm_flag,'
2046 ||' Group_Sequence_id,'
2047 ||' Group_Sequence_number,'
2048 ||' Batch_Id,'
2049 ||' trunc(Resource_instance_id/2),'
2050 -- ||' Charge_number,'
2051 ||' Maximum_Assigned_Units,'
2052 ||' USAGE_RATE_OR_AMOUNT,'
2053 ||' SCHEDULED_FLAG ,'
2054 ||' PARENT_SEQ_NUM '
2055 ||' FROM MSC_WIP_JOB_DTLS_INTERFACE'||lv_dblink
2056 ||' WHERE SR_INSTANCE_ID= :lv_instance_id'
2057 ||' AND GROUP_ID = :G_WIP_GROUP_ID'
2058 ||' AND nvl(CFM_ROUTING_FLAG,0) = 3';
2059
2060
2061 EXECUTE IMMEDIATE lv_sqlstmt USING lv_instance_id,G_WIP_GROUP_ID;
2062
2063 update WSM_LOT_JOB_DTL_INTERFACE wdi
2064 set wdi.parent_header_id = (select header_id from wsm_lot_job_interface whi
2065 where wdi.parent_header_id = whi.interface_id
2066 and wdi.GROUP_ID = whi.GROUP_ID
2067 and whi.GROUP_ID = G_WIP_GROUP_ID) ;
2068
2069 EXCEPTION WHEN NO_DATA_FOUND THEN NULL;
2070 WHEN OTHERS THEN RAISE;
2071
2072 END;
2073
2074
2075 MODIFY_LJ_COMP_REQ;
2076 MODIFY_LJ_RES_REQ;
2077
2078
2079 o_request_id := NULL;
2080
2081 -- Submit 'OSFM Load' Request --
2082 BEGIN
2083 SELECT 1
2084 INTO lv_dummy
2085 FROM WSM_LOT_JOB_INTERFACE
2086 WHERE GROUP_ID= G_WIP_GROUP_ID
2087 AND ROWNUM=1;
2088
2089 --set to trigger mode to bypass the 'SAVEPOINT' and 'ROLLBACK' command.
2090 lv_result := FND_REQUEST.SET_MODE(TRUE);
2091
2092 IF WSMPVERS.get_osfm_release_version >= '110509' then
2093 o_request_id := FND_REQUEST.SUBMIT_REQUEST(
2094 'WSM', -- application
2095 'WSMPLBJI', -- program
2096 NULL, -- description
2097 NULL, -- start_time
2098 FALSE, -- sub_request
2099 g_wip_group_id); -- group_id
2100 ELSE
2101 o_request_id := FND_REQUEST.SUBMIT_REQUEST(
2102 'WSM', -- application
2103 'WSMPLBJI', -- program
2104 NULL, -- description
2105 NULL, -- start_time
2106 FALSE); -- sub_request
2107 END IF;
2108
2109 EXCEPTION
2110 WHEN NO_DATA_FOUND THEN NULL;
2111 WHEN OTHERS THEN RAISE;
2112 END;
2113
2114 END LD_LOT_JOB_SCHEDULE_INTERFACE;
2115
2116
2117
2118 PROCEDURE LD_WIP_JOB_SCHEDULE_INTERFACE
2119 ( o_request_id OUT NOCOPY NUMBER)
2120 IS
2121 lv_sqlstmt VARCHAR2(4000);
2122 lv_instance_id NUMBER;
2123 lv_dblink VARCHAR2(128);
2124
2125 lv_result BOOLEAN;
2126
2127 lv_dummy INTEGER;
2128
2129 BEGIN
2130
2131 select DECODE( A2M_DBLINK,
2132 NULL, ' ',
2133 '@'||A2M_DBLINK),
2134 INSTANCE_ID
2135 into lv_dblink,
2136 lv_instance_id
2137 from MRP_AP_APPS_INSTANCES_ALL
2138 where instance_id = v_instance_id
2139 and instance_code = v_instance_code
2140 and nvl(a2m_dblink,NULL_DBLINK) = nvl(v_dblink,NULL_DBLINK)
2141 and ALLOW_RELEASE_FLAG=1;
2142
2143 lv_sqlstmt:=
2144 'INSERT INTO WIP_JOB_SCHEDULE_INTERFACE'
2145 ||' ( LAST_UPDATE_DATE,'
2146 ||' LAST_UPDATED_BY,'
2147 ||' CREATION_DATE,'
2148 ||' CREATED_BY,'
2149 ||' LAST_UPDATE_LOGIN,'
2150 ||' GROUP_ID,'
2151 ||' SOURCE_CODE,'
2152 ||' SOURCE_LINE_ID,'
2153 ||' ORGANIZATION_ID,'
2154 ||' LOAD_TYPE,'
2155 ||' STATUS_TYPE,'
2156 ||' FIRST_UNIT_START_DATE,'
2157 ||' LAST_UNIT_COMPLETION_DATE,'
2158 ||' PROCESSING_WORK_DAYS,'
2159 ||' DAILY_PRODUCTION_RATE,'
2160 ||' LINE_ID,'
2161 ||' PRIMARY_ITEM_ID,'
2162 ||' BOM_REVISION_DATE,'
2163 ||' ROUTING_REVISION_DATE,'
2164 ||' CLASS_CODE,'
2165 ||' JOB_NAME,'
2166 ||' FIRM_PLANNED_FLAG,'
2167 ||' ALTERNATE_ROUTING_DESIGNATOR,'
2168 ||' ALTERNATE_BOM_DESIGNATOR,'
2169 ||' DEMAND_CLASS,'
2170 ||' START_QUANTITY,'
2171 ||' WIP_ENTITY_ID,'
2172 ||' PROCESS_PHASE,'
2173 ||' PROCESS_STATUS,'
2174 ||' SCHEDULE_GROUP_ID,'
2175 ||' BUILD_SEQUENCE,'
2176 ||' PROJECT_ID,'
2177 ||' TASK_ID,'
2178 ||' NET_QUANTITY,'
2179 ||' END_ITEM_UNIT_NUMBER,'
2180 ||' BOM_REFERENCE_ID,'
2181 ||' ROUTING_REFERENCE_ID,'
2182 ||' ALLOW_EXPLOSION,'
2183 ||' HEADER_ID,'
2184 ||' priority, ' -- dsr
2185 ||' DUE_DATE)' ---- Need to check this was already there
2186 ||' SELECT'
2187 ||' SYSDATE,'
2188 ||' FND_GLOBAL.USER_ID,'
2189 ||' DECODE( ORGANIZATION_TYPE,1,SYSDATE,creation_date), '
2190 ||' FND_GLOBAL.USER_ID,'
2191 ||' LAST_UPDATE_LOGIN,'
2192 ||' DECODE( ORGANIZATION_TYPE,'
2193 ||' 1,'||TO_CHAR(G_WIP_GROUP_ID)
2194 ||',2,'||TO_CHAR(G_OPM_WIP_GROUP_ID)||'),'
2195 ||' SOURCE_CODE,'
2196 ||' SOURCE_LINE_ID,'
2197 ||' ORGANIZATION_ID,'
2198 ||' LOAD_TYPE,'
2199 ||' STATUS_TYPE,'
2200 ||' FIRST_UNIT_START_DATE,'
2201 ||' LAST_UNIT_COMPLETION_DATE,'
2202 ||' PROCESSING_WORK_DAYS,'
2203 ||' DAILY_PRODUCTION_RATE,'
2204 ||' LINE_ID,'
2205 -- dsr ||' PRIMARY_ITEM_ID,'
2206 ||' DECODE(PRIMARY_ITEM_ID, -1001, NULL, PRIMARY_ITEM_ID), '
2207 ||' BOM_REVISION_DATE,'
2208 ||' ROUTING_REVISION_DATE,'
2209 ||' CLASS_CODE,'
2210 ||' JOB_NAME,'
2211 ||' FIRM_PLANNED_FLAG,'
2212 ||' ALTERNATE_ROUTING_DESIGNATOR,'
2213 ||' ALTERNATE_BOM_DESIGNATOR,'
2214 ||' DEMAND_CLASS,'
2215 ||' START_QUANTITY,'
2216 ||' TRUNC(WIP_ENTITY_ID/2),' /* decode wip_entity_id */
2217 ||' PROCESS_PHASE,'
2218 ||' PROCESS_STATUS,'
2219 ||' SCHEDULE_GROUP_ID,'
2220 ||' BUILD_SEQUENCE,'
2221 ||' PROJECT_ID,'
2222 ||' TASK_ID,'
2223 ||' NET_QUANTITY,'
2224 ||' END_ITEM_UNIT_NUMBER,'
2225 ||' DECODE( ORGANIZATION_TYPE,'
2226 ||' 2,TRUNC(BOM_REFERENCE_ID/2),' /* decode bill_sequence_id */
2227 ||' BOM_REFERENCE_ID),' /* for OPM only */
2228 ||' DECODE( ORGANIZATION_TYPE,'
2229 ||' 2,TRUNC(ROUTING_REFERENCE_ID/2),' /* decode routing_sequence_id */
2230 ||' ROUTING_REFERENCE_ID),' /* for OPM only */
2231 ||' BILL_RTG_EXPLOSION_FLAG,'
2232 ||' HEADER_ID,'
2233 ||' schedule_priority, ' -- dsr
2234 ||' requested_completion_date' -- need to check in ds code it is requested_completion_date
2235 ||' FROM MSC_WIP_JOB_SCHEDULE_INTERFACE'||lv_dblink
2236 ||' WHERE SR_INSTANCE_ID= :lv_instance_id'
2237 ||' AND GROUP_ID = :G_WIP_GROUP_ID'
2238 ||' AND nvl(CFM_ROUTING_FLAG,0) <> 3 '
2239 ||' AND load_type <> 21 ' -- dsr: exclude oem: EAM_RESCHEDULE_WORK_RODER
2240 ;
2241
2242 EXECUTE IMMEDIATE lv_sqlstmt USING lv_instance_id,G_WIP_GROUP_ID;
2243
2244 lv_sqlstmt:=
2245 'INSERT INTO WIP_JOB_DTLS_INTERFACE'
2246 ||' ( INTERFACE_ID,'
2247 ||' GROUP_ID,'
2248 ||' OPERATION_SEQ_NUM,'
2249 ||' RESOURCE_SEQ_NUM,'
2250 ||' REPLACEMENT_GROUP_NUM,'
2251 ||' RESOURCE_ID_OLD,'
2252 ||' RESOURCE_ID_NEW,'
2253 ||' USAGE_RATE_OR_AMOUNT,'
2254 ||' SCHEDULED_FLAG,'
2255 ||' ASSIGNED_UNITS,'
2256 ||' APPLIED_RESOURCE_UNITS,'
2257 ||' APPLIED_RESOURCE_VALUE,'
2258 ||' UOM_CODE,'
2259 ||' BASIS_TYPE,'
2260 ||' ACTIVITY_ID,'
2261 ||' AUTOCHARGE_TYPE,'
2262 ||' STANDARD_RATE_FLAG,'
2263 ||' START_DATE,'
2264 ||' COMPLETION_DATE,'
2265 ||' INVENTORY_ITEM_ID_OLD,'
2266 ||' INVENTORY_ITEM_ID_NEW,'
2267 ||' QUANTITY_PER_ASSEMBLY,'
2268 ||' COMPONENT_YIELD_FACTOR,'
2269 ||' DEPARTMENT_ID,'
2270 ||' WIP_SUPPLY_TYPE,'
2271 ||' DATE_REQUIRED,'
2272 ||' REQUIRED_QUANTITY,'
2273 ||' QUANTITY_ISSUED,'
2274 ||' SUPPLY_SUBINVENTORY,'
2275 ||' SUPPLY_LOCATOR_ID,'
2276 ||' MRP_NET_FLAG,'
2277 ||' MPS_REQUIRED_QUANTITY,'
2278 ||' MPS_DATE_REQUIRED,'
2279 ||' LOAD_TYPE,'
2280 ||' SUBSTITUTION_TYPE,'
2281 ||' PROCESS_PHASE,'
2282 ||' PROCESS_STATUS,'
2283 ||' REQUEST_ID,'
2284 ||' PROGRAM_APPLICATION_ID,'
2285 ||' PROGRAM_ID,'
2286 ||' PROGRAM_UPDATE_DATE,'
2287 ||' PARENT_HEADER_ID,'
2288 ||' DESCRIPTION,'
2289 ||' STANDARD_OPERATION_ID,'
2290 ||' FIRST_UNIT_START_DATE,'
2291 ||' FIRST_UNIT_COMPLETION_DATE,'
2292 ||' LAST_UNIT_START_DATE,'
2293 ||' LAST_UNIT_COMPLETION_DATE,'
2294 ||' COUNT_POINT_TYPE,'
2295 ||' BACKFLUSH_FLAG,'
2296 ||' MINIMUM_TRANSFER_QUANTITY,'
2297 ||' WIP_ENTITY_ID,'
2298 ||' ORGANIZATION_ID,'
2299 ||' ATTRIBUTE1,'
2300 ||' LAST_UPDATE_DATE,'
2301 ||' LAST_UPDATED_BY,'
2302 ||' CREATION_DATE,'
2303 ||' CREATED_BY,'
2304 ||' LAST_UPDATE_LOGIN '
2305 -- dsr: added following 10 new columns
2306 ||' , Serial_number_new ' -- rawasthi changed the column from serial_number to Serial_number_new
2307 ||' , resource_serial_number ' -- jguo
2308 ||' , setup_id '
2309 ||' , group_sequence_id '
2310 ||' , group_sequence_number '
2311 ||' , batch_id '
2312 ||' , resource_instance_id '
2313 ||' , charge_number '
2314 ||' , maximum_assigned_units '
2315 ||' , parent_seq_num '
2316 ||' , firm_flag '
2317 -- jguo opm ||' , orig_resource_seq_num ) '
2318 ||' , schedule_seq_num ) '
2319 ||' SELECT'
2320 ||' INTERFACE_ID,'
2321 ||' DECODE( ORGANIZATION_TYPE,'
2322 ||' 1,'||TO_CHAR(G_WIP_GROUP_ID)
2323 ||',2,'||TO_CHAR(G_OPM_WIP_GROUP_ID)||'),'
2324 ||' OPERATION_SEQ_NUM,'
2325 ||' RESOURCE_SEQ_NUM,'
2326 ||' ALTERNATE_NUM,'
2327 ||' TRUNC(RESOURCE_ID_OLD/2),' /* decode resource_id */
2328 ||' TRUNC(RESOURCE_ID_NEW/2),' /* decode resource_id */
2329 ||' USAGE_RATE_OR_AMOUNT,'
2330 ||' SCHEDULED_FLAG,'
2331 ||' ASSIGNED_UNITS,'
2332 ||' APPLIED_RESOURCE_UNITS,'
2333 ||' APPLIED_RESOURCE_VALUE,'
2334 ||' UOM_CODE,'
2335 ||' BASIS_TYPE,'
2336 ||' ACTIVITY_ID,'
2337 ||' AUTOCHARGE_TYPE,'
2338 ||' STANDARD_RATE_FLAG,'
2339 ||' START_DATE,'
2340 ||' COMPLETION_DATE,'
2341 ||' INVENTORY_ITEM_ID_OLD,'
2342 ||' INVENTORY_ITEM_ID_NEW,'
2343 ||' QUANTITY_PER_ASSEMBLY,'
2344 ||' COMPONENT_YIELD_FACTOR,'
2345 ||' TRUNC(DEPARTMENT_ID/2),' /* decode department_id */
2346 ||' WIP_SUPPLY_TYPE,'
2347 ||' DATE_REQUIRED,'
2348 ||' REQUIRED_QUANTITY,'
2349 ||' QUANTITY_ISSUED,'
2350 ||' SUPPLY_SUBINVENTORY,'
2351 ||' SUPPLY_LOCATOR_ID,'
2352 ||' MRP_NET_FLAG,'
2353 ||' MPS_REQUIRED_QUANTITY,'
2354 ||' MPS_DATE_REQUIRED,'
2355 ||' LOAD_TYPE,'
2356 ||' SUBSTITUTION_TYPE,'
2357 ||' PROCESS_PHASE,'
2358 ||' PROCESS_STATUS,'
2359 ||' REQUEST_ID,'
2360 ||' PROGRAM_APPLICATION_ID,'
2361 ||' PROGRAM_ID,'
2362 ||' PROGRAM_UPDATE_DATE,'
2363 ||' PARENT_HEADER_ID,'
2364 ||' DESCRIPTION,'
2365 ||' STANDARD_OPERATION_ID,'
2366 ||' FIRST_UNIT_START_DATE,'
2367 ||' FIRST_UNIT_COMPLETION_DATE,'
2368 ||' LAST_UNIT_START_DATE,'
2369 ||' LAST_UNIT_COMPLETION_DATE,'
2370 ||' COUNT_POINT_TYPE,'
2371 ||' BACKFLUSH_FLAG,'
2372 ||' MINIMUM_TRANSFER_QUANTITY,'
2373 ||' TRUNC(WIP_ENTITY_ID/2),' /* decode wip_entity_id */
2374 ||' ORGANIZATION_ID,'
2375 ||' decode(organization_type,2,fnd_number.number_to_canonical(resource_hours),resource_hours),'
2376 ||' SYSDATE,'
2377 ||' FND_GLOBAL.USER_ID,'
2378 ||' SYSDATE,'
2379 ||' FND_GLOBAL.USER_ID,'
2380 ||' LAST_UPDATE_LOGIN '
2381 -- dsr: added following 10 new columns
2382 ||' , serial_number '
2383 ||' , serial_number ' -- jguo
2384 ||' , setup_id '
2385 ||' , group_sequence_id '
2386 ||' , group_sequence_number '
2387 ||' , batch_id '
2388 ||' ,TRUNC(resource_instance_id/2) '-- changed by abhikuma
2389 ||' , charge_number '
2390 ||' , maximum_assigned_units '
2391 ||' , parent_seq_num '
2392 ||' , firm_flag '
2393 ||' , schedule_seq_num '
2394 ||' FROM MSC_WIP_JOB_DTLS_INTERFACE'||lv_dblink
2395 ||' WHERE SR_INSTANCE_ID= :lv_instance_id'
2396 ||' AND nvl(CFM_ROUTING_FLAG,0) <> 3 '
2397 ||' AND GROUP_ID = :G_WIP_GROUP_ID'
2398 ||' AND nvl(operation_seq_num,-1) <> -1'
2399 ||' AND nvl(eam_flag, -1) <> 1 '
2400 ;
2401
2402 --Commented out to support OPM integration
2403 --||' AND ORGANIZATION_TYPE = 1 ';
2404
2405 EXECUTE IMMEDIATE lv_sqlstmt USING lv_instance_id,G_WIP_GROUP_ID;
2406
2407 o_request_id := NULL;
2408
2409 -- Submit 'WIP Mass Load' Request --
2410 BEGIN
2411 SELECT 1
2412 INTO lv_dummy
2413 FROM WIP_JOB_SCHEDULE_INTERFACE
2414 WHERE GROUP_ID= G_WIP_GROUP_ID
2415 AND ROWNUM=1;
2416
2417 MODIFY_COMPONENT_REQUIREMENT;
2418
2419 MODIFY_RESOURCE_REQUIREMENT;
2420
2421 --set to trigger mode to bypass the 'SAVEPOINT' and 'ROLLBACK' command.
2422 lv_result := FND_REQUEST.SET_MODE(TRUE);
2423
2424
2425 o_request_id := FND_REQUEST.SUBMIT_REQUEST(
2426 'WIP', -- application
2427 'WICMLP', -- program
2428 NULL, -- description
2429 NULL, -- start_time
2430 FALSE, -- sub_request
2431 g_wip_group_id, -- group_id
2432 1, -- validation_level
2433 1); -- print report
2434
2435
2436 EXCEPTION
2437 WHEN NO_DATA_FOUND THEN NULL;
2438 WHEN OTHERS THEN RAISE;
2439 END;
2440
2441 -- Submit 'OPM's WIP Mass Load' Request --
2442 BEGIN
2443 SELECT 1
2444 INTO lv_dummy
2445 FROM WIP_JOB_SCHEDULE_INTERFACE
2446 WHERE GROUP_ID= G_OPM_WIP_GROUP_ID
2447 AND ROWNUM=1;
2448
2449 SET_OPM_SCHEDULING_METHOD;
2450
2451 -- set to trigger mode to bypass the 'SAVEPOINT' and 'ROLLBACK' command.
2452 lv_result := FND_REQUEST.SET_MODE(TRUE);
2453
2454 o_request_id := FND_REQUEST.SUBMIT_REQUEST(
2455 'GMP', -- application
2456 'GMPAPSFD', -- program
2457 NULL, -- description
2458 NULL, -- start_time
2459 FALSE, -- sub_request
2460 G_OPM_WIP_GROUP_ID); -- group_id
2461
2462
2463 EXCEPTION
2464 WHEN NO_DATA_FOUND THEN NULL;
2465 WHEN OTHERS THEN RAISE;
2466 END;
2467
2468
2469 END LD_WIP_JOB_SCHEDULE_INTERFACE;
2470
2471
2472 PROCEDURE LD_PO_REQUISITIONS_INTERFACE
2473 ( p_po_group_by_name IN VARCHAR2,
2474 o_request_id OUT NOCOPY NUMBER)
2475 IS
2476
2477 -- added for 2541517
2478 TYPE CharTab IS TABLE OF VARCHAR2(3) INDEX BY BINARY_INTEGER;
2479 TYPE RIDTab IS TABLE OF ROWID INDEX BY BINARY_INTEGER;
2480 TYPE NumTab IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
2481
2482
2483 lv_req_count NUMBER;
2484 lv_pri_rowid RIDTab;
2485 lv_sec_uom_code CharTab;
2486 lv_sec_uom_qty NumTab;
2487
2488 lv_sqlstmt VARCHAR2(4000);
2489 lv_instance_id NUMBER;
2490 lv_dblink VARCHAR2(128);
2491
2492 lv_result BOOLEAN;
2493
2494 lv_accrual_acct_id NUMBER;
2495 lv_charge_acct_id NUMBER;
2496
2497 var_revision VARCHAR2(3);
2498 var_revision_ctrl NUMBER;
2499
2500 Cursor c1 is select pvsa.vendor_site_id,pri.rowid,pri.item_id, pri.destination_organization_id,pri.charge_account_id,pri.project_id
2501 from po_vendor_sites_all pvsa, po_Requisitions_interface_All pri
2502 where pri.suggested_vendor_id = pvsa.vendor_id(+)
2503 and pri.suggested_vendor_site = pvsa.vendor_site_code(+)
2504 and nvl(pri.org_id,-99) = nvl(pvsa.org_id(+),-99)
2505 and pri.interface_source_code = 'MSC'
2506 and pri.batch_id = G_PO_BATCH_NUMBER;
2507
2508 CURSOR c1_rec is
2509 SELECT item_id,
2510 destination_organization_id,
2511 rowid
2512 from PO_REQUISITIONS_INTERFACE_ALL
2513 where batch_id = G_PO_BATCH_NUMBER;
2514
2515
2516 BEGIN
2517
2518 select DECODE( A2M_DBLINK,
2519 NULL, ' ',
2520 '@'||A2M_DBLINK),
2521 INSTANCE_ID
2522 into lv_dblink,
2523 lv_instance_id
2524 from MRP_AP_APPS_INSTANCES_ALL
2525 where instance_id = v_instance_id
2526 and instance_code = v_instance_code
2527 and nvl(a2m_dblink,NULL_DBLINK) = nvl(v_dblink,NULL_DBLINK)
2528 and ALLOW_RELEASE_FLAG=1;
2529
2530 lv_sqlstmt:=
2531 'INSERT INTO PO_REQUISITIONS_INTERFACE_ALL'
2532 ||' ( PROJECT_ACCOUNTING_CONTEXT,'
2533 ||' PROJECT_ID,'
2534 ||' TASK_ID,'
2535 ||' NEED_BY_DATE,'
2536 ||' ITEM_ID,'
2537 ||' ITEM_REVISION,'
2538 ||' CHARGE_ACCOUNT_ID,'
2539 ||' AUTHORIZATION_STATUS,'
2540 ||' BATCH_ID,'
2541 ||' GROUP_CODE,'
2542 ||' PREPARER_ID,'
2543 ||' AUTOSOURCE_FLAG,'
2544 ||' SOURCE_ORGANIZATION_ID,'
2545 ||' DESTINATION_ORGANIZATION_ID,'
2546 ||' DELIVER_TO_LOCATION_ID,'
2547 ||' DELIVER_TO_REQUESTOR_ID,'
2548 ||' SUGGESTED_VENDOR_ID,'
2549 ||' SUGGESTED_VENDOR_SITE,'
2550 ||' LAST_UPDATED_BY,'
2551 ||' LAST_UPDATE_DATE,'
2552 ||' LAST_UPDATE_LOGIN,'
2553 ||' CREATION_DATE,'
2554 ||' CREATED_BY,'
2555 ||' INTERFACE_SOURCE_CODE,'
2556 ||' SOURCE_TYPE_CODE,'
2557 ||' DESTINATION_TYPE_CODE,'
2558 ||' QUANTITY,'
2559 ||' UOM_CODE,'
2560 ||' LINE_TYPE_ID,'
2561 ||' ORG_ID,'
2562 ||' VMI_FLAG,'
2563 ||' END_ITEM_UNIT_NUMBER )'
2564 ||' SELECT'
2565 ||' PROJECT_ACCOUNTING_CONTEXT,'
2566 ||' PROJECT_ID,'
2567 ||' TASK_ID,'
2568 ||' NEED_BY_DATE,'
2569 ||' ITEM_ID,'
2570 ||' ITEM_REVISION,'
2571 ||' CHARGE_ACCOUNT_ID,'
2572 ||' AUTHORIZATION_STATUS,'
2573 || TO_CHAR(G_PO_BATCH_NUMBER)||','
2574 ||' GROUP_CODE,'
2575 ||' PREPARER_ID,'
2576 ||' AUTOSOURCE_FLAG,'
2577 ||' SOURCE_ORGANIZATION_ID,'
2578 ||' DESTINATION_ORGANIZATION_ID,'
2579 ||' DELIVER_TO_LOCATION_ID,'
2580 ||' DELIVER_TO_REQUESTOR_ID,'
2581 ||' SUGGESTED_VENDOR_ID,'
2582 ||' SUGGESTED_VENDOR_SITE,'
2583 ||' FND_GLOBAL.USER_ID,'
2584 ||' SYSDATE,'
2585 ||' LAST_UPDATE_LOGIN,'
2586 ||' SYSDATE,'
2587 ||' FND_GLOBAL.USER_ID,'
2588 ||' INTERFACE_SOURCE_CODE,'
2589 ||' SOURCE_TYPE_CODE,'
2590 ||' DESTINATION_TYPE_CODE,'
2591 ||' QUANTITY,'
2592 ||' UOM_CODE,'
2593 ||' LINE_TYPE_ID,'
2594 ||' ORG_ID,'
2595 ||' DECODE(VMI_FLAG,1,''Y'',''N''), '
2596 ||' END_ITEM_UNIT_NUMBER'
2597 ||' FROM MSC_PO_REQUISITIONS_INTERFACE'||lv_dblink
2598 ||' WHERE SR_INSTANCE_ID= :lv_instance_id'
2599 ||' AND BATCH_ID = :G_PO_BATCH_NUMBER';
2600
2601 EXECUTE IMMEDIATE lv_sqlstmt
2602 USING lv_instance_id,G_PO_BATCH_NUMBER;
2603
2604
2605
2606 For i in c1
2607 Loop
2608 BEGIN
2609 --Added for the bug#3319306
2610 IF i.vendor_site_id IS NOT NULL THEN
2611 update po_requisitions_interface_all
2612 set suggested_vendor_site_id = i.vendor_site_id
2613 where rowid = i.rowid;
2614 END IF;
2615
2616 If nvl(i.charge_account_id,-1) = -1 Then
2617
2618 GMP_UTILITY.generate_opm_acct('INVENTORY',
2619 'ASSET',
2620 'ASSET',
2621 i.destination_organization_id,
2622 i.item_id,
2623 i.vendor_site_id,
2624 lv_charge_acct_id
2625 );
2626 If NVL(lv_charge_acct_id,0) > 0 Then
2627 Update po_requisitions_interface_all
2628 set charge_account_id = lv_charge_acct_id
2629 where rowid = i.rowid;
2630 Else
2631 Update po_requisitions_interface_all
2632 set charge_account_id =
2633 (Select nvl(mapv.material_account,
2634 decode( msi.inventory_asset_flag,
2635 'Y', mp.material_account,
2636 nvl(msi.expense_account, mp.expense_account)))
2637 from mtl_system_items msi,
2638 mtl_parameters mp,
2639 /* Bug 3341083 Note: Any changes to from clause or where clause of MRP_AP_PROJECTS_V, needs a corresponding change to this inline view*/
2640 (SELECT ppp.Project_ID,
2641 ppp.Organization_ID,
2642 ccga.Material_Account
2643 FROM CST_COST_GROUP_ACCOUNTS ccga,
2644 PA_PROJECT_PLAYERS ppl,
2645 PA_PROJECTS_ALL ppa,
2646 PJM_PROJECT_PARAMETERS ppp
2647 WHERE ppa.Project_ID= ppp.Project_ID
2648 AND ccga.Cost_Group_ID(+)= ppp.Costing_Group_ID
2649 AND ccga.Organization_ID(+)= ppp.Organization_ID
2650 AND ppl.project_role_type(+)= 'PROJECT MANAGER'
2651 AND ppl.project_id(+)= ppa.project_id
2652 UNION ALL
2653 SELECT ppp.Project_ID,
2654 ppp.Organization_ID,
2655 ccga.Material_Account
2656 FROM CST_COST_GROUP_ACCOUNTS ccga,
2657 PJM_SEIBAN_NUMBERS psn,
2658 PJM_PROJECT_PARAMETERS ppp
2659 WHERE psn.Project_ID= ppp.Project_ID
2660 AND ccga.Cost_Group_ID(+)= ppp.Costing_Group_ID
2661 AND ccga.Organization_ID(+)= ppp.Organization_ID) mapv
2662 where msi.inventory_item_id = i.item_id
2663 and msi.organization_id = i.destination_organization_id
2664 and mp.organization_id = msi.organization_id
2665 and mapv.organization_id(+) = msi.organization_id
2666 and mapv.project_id(+) = nvl(i.project_id,-23453));
2667 End if;
2668
2669 GMP_UTILITY.generate_opm_acct('ACCRUAL',
2670 'ASSET',
2671 'ASSET',
2672 i.destination_organization_id,
2673 i.item_id,
2674 i.vendor_site_id,
2675 lv_accrual_acct_id
2676 );
2677 If NVL(lv_accrual_acct_id,0) > 0 Then
2678 Update po_requisitions_interface_all
2679 set accrual_account_id = lv_accrual_acct_id
2680 where rowid = i.rowid;
2681 Else
2682 Null;
2683 End if;
2684
2685 End if; /* Charge acct id = -1 */
2686
2687 EXCEPTION
2688 WHEN OTHERS THEN RAISE;
2689 END;
2690
2691 End loop;
2692
2693 -- fix for 2541517
2694 -- Populating SECONDARY_UOM_CODE and SECONDARY_QUANTITY in PO_REQUISITIONS_INTERFACE_ALL from MTL_SYSTEM_ITEMS
2695 BEGIN
2696 SELECT pri.rowid,
2697 msi.SECONDARY_UOM_CODE,
2698 inv_convert.inv_um_convert(pri.ITEM_ID,9,pri.QUANTITY,pri.UOM_CODE,msi.SECONDARY_UOM_CODE,null,null)
2699 BULK COLLECT
2700 INTO lv_pri_rowid,
2701 lv_sec_uom_code,
2702 lv_sec_uom_qty
2703 FROM PO_REQUISITIONS_INTERFACE_ALL pri,
2704 MTL_SYSTEM_ITEMS msi
2705 WHERE pri.ITEM_ID = msi.INVENTORY_ITEM_ID
2706 AND pri.DESTINATION_ORGANIZATION_ID = msi.ORGANIZATION_ID
2707 AND msi.SECONDARY_UOM_CODE is not NULL
2708 AND pri.batch_id = G_PO_BATCH_NUMBER;
2709
2710 lv_req_count:= SQL%ROWCOUNT;
2711
2712 EXCEPTION
2713 WHEN OTHERS THEN RAISE;
2714 END;
2715
2716 IF lv_req_count <> 0 THEN
2717
2718 FOR j IN 1..lv_req_count LOOP
2719
2720 UPDATE PO_REQUISITIONS_INTERFACE_ALL pri
2721 SET pri.SECONDARY_UOM_CODE = lv_sec_uom_code(j),
2722 pri.SECONDARY_QUANTITY = lv_sec_uom_qty(j)
2723 WHERE ROWID= lv_pri_rowid(j);
2724
2725 END LOOP;
2726 END IF;
2727
2728 FOR ctemp in c1_rec LOOP
2729
2730 BEGIN
2731 SELECT max(rev.revision),
2732 max(msi.revision_qty_control_code)
2733 INTO var_revision,var_revision_ctrl
2734 FROM mtl_system_items_b msi,
2735 mtl_item_revisions rev
2736 WHERE msi.inventory_item_id = ctemp.item_id
2737 AND msi.organization_id = ctemp.destination_organization_id
2738 AND rev.inventory_item_id = msi.inventory_item_id
2739 AND rev.organization_id = msi.organization_id
2740 AND TRUNC(rev.effectivity_date) =
2741 (SELECT TRUNC(max(rev2.effectivity_date))
2742 FROM mtl_item_revisions rev2
2743 WHERE rev2.implementation_date IS NOT NULL
2744 AND rev2.effectivity_date <= TRUNC(SYSDATE)+.99999
2745 AND rev2.organization_id = rev.organization_id
2746 AND rev2.inventory_item_id = rev.inventory_item_id);
2747
2748 EXCEPTION
2749 WHEN NO_DATA_FOUND THEN
2750 var_revision_ctrl := NOT_UNDER_REV_CONTROL;
2751 WHEN OTHERS THEN
2752 RAISE;
2753 END;
2754
2755 BEGIN
2756 UPDATE PO_REQUISITIONS_INTERFACE_ALL
2757 set item_revision = DECODE(var_purchasing_by_rev, NULL,
2758 DECODE(var_revision_ctrl, NOT_UNDER_REV_CONTROL, NULL, var_revision),
2759 PURCHASING_BY_REV, var_revision,
2760 NOT_PURCHASING_BY_REV, NULL)
2761 WHERE ROWID = ctemp.rowid;
2762
2763 EXCEPTION
2764 WHEN OTHERS THEN
2765 RAISE;
2766 END;
2767
2768 END LOOP;
2769
2770 -- Launching the REQIMPORT in loop for each OU, change for MOAC
2771 DECLARE
2772 CURSOR c1 IS
2773 SELECT DISTINCT org_id
2774 FROM PO_REQUISITIONS_INTERFACE_ALL
2775 WHERE batch_id = G_PO_BATCH_NUMBER;
2776
2777 BEGIN
2778 FOR C2 IN C1
2779 LOOP
2780
2781 MO_GLOBAL.INIT ('PO');
2782 FND_REQUEST.SET_ORG_ID (c2.org_id);
2783 -- set to trigger mode to bypass the 'SAVEPOINT' and 'ROLLBACK' command.
2784 lv_result := FND_REQUEST.SET_MODE(TRUE);
2785
2786 o_request_id := NULL;
2787 o_request_id := FND_REQUEST.SUBMIT_REQUEST(
2788 'PO', -- application
2789 'REQIMPORT',-- program
2790 NULL, -- description
2791 NULL, -- start_time
2792 FALSE, -- sub_request
2793 'MSC',
2794 G_PO_BATCH_NUMBER,
2795 p_po_group_by_name,
2796 0);
2797 END LOOP;
2798 END;
2799
2800 END LD_PO_REQUISITIONS_INTERFACE;
2801
2802 PROCEDURE LD_PO_RESCHEDULE_INTERFACE
2803 ( o_request_id OUT NOCOPY NUMBER)
2804 IS
2805 lv_sqlstmt VARCHAR2(4000);
2806 lv_instance_id NUMBER;
2807 lv_dblink VARCHAR2(128);
2808
2809 lv_result BOOLEAN;
2810
2811 BEGIN
2812
2813 select DECODE( A2M_DBLINK,
2814 NULL, ' ',
2815 '@'||A2M_DBLINK),
2816 INSTANCE_ID
2817 into lv_dblink,
2818 lv_instance_id
2819 from MRP_AP_APPS_INSTANCES_ALL
2820 where instance_id = v_instance_id
2821 and instance_code = v_instance_code
2822 and nvl(a2m_dblink,NULL_DBLINK) = nvl(v_dblink,NULL_DBLINK)
2823 and ALLOW_RELEASE_FLAG=1;
2824
2825 lv_sqlstmt:=
2826 'INSERT INTO PO_RESCHEDULE_INTERFACE'
2827 ||' ( LINE_ID,'
2828 ||' QUANTITY,'
2829 ||' NEED_BY_DATE,'
2830 ||' PROCESS_ID,'
2831 ||' LAST_UPDATE_DATE,'
2832 ||' LAST_UPDATED_BY,'
2833 ||' CREATION_DATE,'
2834 ||' CREATED_BY,'
2835 ||' LAST_UPDATE_LOGIN,'
2836 ||' REQUEST_ID,'
2837 ||' PROGRAM_APPLICATION_ID,'
2838 ||' PROGRAM_ID,'
2839 ||' PROGRAM_UPDATE_DATE )'
2840 ||' SELECT'
2841 ||' LINE_ID,'
2842 ||' QUANTITY,'
2843 ||' NEED_BY_DATE,'
2844 ||' NULL,'
2845 ||' SYSDATE,'
2846 ||' FND_GLOBAL.USER_ID,'
2847 ||' SYSDATE,'
2848 ||' FND_GLOBAL.USER_ID,'
2849 ||' LAST_UPDATE_LOGIN,'
2850 ||' REQUEST_ID,'
2851 ||' PROGRAM_APPLICATION_ID,'
2852 ||' PROGRAM_ID,'
2853 ||' PROGRAM_UPDATE_DATE'
2854 ||' FROM MSC_PO_RESCHEDULE_INTERFACE'||lv_dblink
2855 ||' WHERE SR_INSTANCE_ID= :lv_instance_id';
2856
2857 EXECUTE IMMEDIATE lv_sqlstmt USING lv_instance_id;
2858
2859 -- Launching the POXRSR in loop for each OU, change for MOAC
2860 DECLARE
2861 CURSOR c1 IS
2862 SELECT DISTINCT prla.org_id
2863 FROM PO_RESCHEDULE_INTERFACE PRI, PO_REQUISITION_LINES_ALL PRLA
2864 WHERE pri.line_id = prla.requisition_line_id;
2865
2866 BEGIN
2867 FOR C2 IN C1
2868 LOOP
2869
2870 MO_GLOBAL.INIT ('PO');
2871 FND_REQUEST.SET_ORG_ID (c2.org_id);
2872 -- set to trigger mode to bypass the 'SAVEPOINT' and 'ROLLBACK' command.
2873 lv_result := FND_REQUEST.SET_MODE(TRUE);
2874
2875 o_request_id := NULL;
2876 o_request_id := FND_REQUEST.SUBMIT_REQUEST(
2877 'PO', -- application
2878 'POXRSR', -- program
2879 NULL, -- description
2880 NULL, -- start_time
2881 FALSE); -- sub_request
2882 END LOOP;
2883 END;
2884
2885 END LD_PO_RESCHEDULE_INTERFACE;
2886
2887
2888 PROCEDURE MODIFY_COMPONENT_REQUIREMENT
2889 IS
2890
2891
2892 Cursor C1 is
2893 Select a.header_id,min(bos.operation_seq_num) new_op_seq
2894 from wip_job_schedule_interface a,
2895 wip_job_dtls_interface b,
2896 bom_operation_Sequences bos,
2897 bom_operational_routings bor
2898 where a.group_id = b.group_id
2899 and a.header_id = b.parent_header_id --added for the bug#3538800
2900 and a.group_id = G_WIP_GROUP_ID
2901 and a.primary_item_id = bor.assembly_item_id
2902 and a.organization_id = bor.organization_id
2903 and nvl(bor.alternate_routing_Designator,0) = nvl(a.alternate_routing_designator,0)
2904 and bor.common_routing_Sequence_id = bos.routing_Sequence_id
2905 and b.load_type = 2
2906 and b.substitution_type = 3
2907 and a.source_code = 'MSC'
2908 and b.operation_seq_num = 1
2909 and ( bos.disable_date IS NULL
2910 OR trunc(bos.disable_date) >= trunc(nvl(a.bom_revision_date,a.first_unit_start_date))
2911 )
2912 group by a.header_id;
2913
2914 Cursor C2 is
2915 select sum(round(b.QUANTITY_PER_ASSEMBLY*NVL(b.COMPONENT_YIELD_FACTOR,1),6)) qty_per_assy,
2916 sum(b.REQUIRED_QUANTITY) reqd_qty,
2917 b.group_id,
2918 b.parent_header_id,
2919 b.INVENTORY_ITEM_ID_OLD,
2920 b.ORGANIZATION_ID,
2921 b.OPERATION_SEQ_NUM
2922 from wip_job_schedule_interface a,
2923 wip_job_dtls_interface b
2924 where a.source_code = 'MSC'
2925 and a.group_id = G_WIP_GROUP_ID
2926 and a.group_id = b.group_id
2927 and a.header_id = b.parent_header_id
2928 and b.load_type = 2
2929 and b.substitution_type = 3
2930 and b.process_phase = 2
2931 and b.process_status = 1
2932 group by b.group_id,
2933 b.parent_header_id,
2934 b.ORGANIZATION_ID,
2935 b.INVENTORY_ITEM_ID_OLD,
2936 b.OPERATION_SEQ_NUM;
2937
2938 Cursor C3 is
2939 select b.rowid
2940 from wip_job_schedule_interface a,
2941 wip_job_dtls_interface b
2942 where a.source_code = 'MSC'
2943 and a.group_id = G_WIP_GROUP_ID
2944 and a.group_id = b.group_id
2945 and a.header_id = b.parent_header_id
2946 and b.load_type = 2
2947 and b.substitution_type = 3
2948 and b.process_phase = 2
2949 and b.process_status = 1
2950 and b.rowid not in (select min(c.rowid)
2951 from wip_job_dtls_interface c
2952 where b.group_id = c.group_id
2953 and b.parent_header_id = c.parent_header_id
2954 and b.ORGANIZATION_ID = c.ORGANIZATION_ID
2955 and b.INVENTORY_ITEM_ID_OLD = c.INVENTORY_ITEM_ID_OLD
2956 and b.OPERATION_SEQ_NUM = c.OPERATION_SEQ_NUM
2957 and b.load_type = c.load_type
2958 and b.substitution_type = c.substitution_type
2959 and b.process_phase = c.process_phase
2960 and b.process_status = c.process_status );
2961
2962 Begin
2963
2964 For I in C1
2965
2966 loop
2967 update wip_job_dtls_interface
2968 set operation_Seq_num = I.new_op_seq
2969 where parent_header_id = I.header_id
2970 and operation_seq_num = 1
2971 and load_type = 2
2972 and substitution_type = 3;
2973
2974 End loop;
2975
2976 For J in C2
2977
2978 loop
2979 update wip_job_dtls_interface
2980 set QUANTITY_PER_ASSEMBLY = J.qty_per_assy,
2981 REQUIRED_QUANTITY = J.reqd_qty
2982 where group_id = J.group_id
2983 and parent_header_id = J.parent_header_id
2984 and ORGANIZATION_ID = J.ORGANIZATION_ID
2985 and INVENTORY_ITEM_ID_OLD = J.INVENTORY_ITEM_ID_OLD
2986 and OPERATION_SEQ_NUM = J.OPERATION_SEQ_NUM
2987 and load_type = 2
2988 and substitution_type = 3
2989 and process_phase = 2
2990 and process_status = 1;
2991
2992 End loop;
2993
2994 For K in C3
2995
2996 loop
2997
2998 delete wip_job_dtls_interface
2999 where rowid = K.rowid;
3000
3001 End loop;
3002
3003 End MODIFY_COMPONENT_REQUIREMENT;
3004
3005
3006 PROCEDURE MODIFY_RESOURCE_REQUIREMENT
3007 IS
3008
3009 /* dsr jsi.primary_item_id and jsi.organization_id can be null */
3010 /* dsr: added outer join in the following cursor*/
3011 cursor cres_upd is select jdi.rowid,jdi.operation_seq_num,
3012 jdi.parent_header_id,rtng.common_routing_Sequence_id
3013 FROM BOM_OPERATIONAL_ROUTINGS rtng,
3014 wip_job_dtls_interface jdi,
3015 wip_job_schedule_interface jsi
3016 where rtng.assembly_item_id (+) = jsi.primary_item_id -- dsr
3017 and jsi.group_id = G_WIP_GROUP_ID
3018 AND rtng.organization_id (+) = jsi.organization_id -- dsr
3019 AND NVL(rtng.alternate_routing_designator,' ')=
3020 NVL( jsi.alternate_routing_designator,' ')
3021 and jsi.header_id = jdi.parent_header_id
3022 and nvl(jdi.resource_seq_num,-1) = -1000
3023 and jdi.load_type = 1
3024 order by jdi.parent_header_id,jdi.operation_Seq_num;
3025
3026 v_old_op number;
3027 v_old_res number;
3028 v_old_header number;
3029
3030 TYPE NumTab IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
3031 TYPE RIDTab IS TABLE OF ROWID INDEX BY BINARY_INTEGER;
3032 TYPE DateTab IS TABLE OF DATE INDEX BY BINARY_INTEGER;
3033
3034 v_max_resource_Seq number;
3035
3036 lv_std_job_count NUMBER;
3037 lv_jsi_rowid RIDTab;
3038 lv_header_id NumTab;
3039 lv_routing_seq_id NumTab;
3040 lv_organization_id NumTab;
3041 lv_start_date DateTab;
3042 lv_end_Date DateTab;
3043
3044 lv_res_req_count NUMBER;
3045 lv_jdi_rowid RIDTab;
3046 lv_op_seq_num NumTab;
3047 lv_sim_res_seq NumTab;
3048 lv_res_priority NumTab;
3049 lv_resource_id NumTab;
3050 lv_resource_id_old NumTab;
3051 lv_res_seq NumTab;
3052 lv_sub_type NumTab;
3053 lv_res_id_old NumTab;
3054 lv_ld_type NumTab;
3055
3056 lu_res_req_count NUMBER;
3057 lu_op_seq_num NumTab;
3058 lu_sim_res_seq NumTab;
3059 lu_res_priority NumTab;
3060 lu_res_seq NumTab;
3061 lu_resource_id NumTab;
3062 lu_res_required NumTab;
3063 lu_schedule_seq_num NumTab;
3064
3065
3066 lun_res_req_count NUMBER;
3067 lun_usage NumTab;
3068 lun_op_seq_num NumTab;
3069 lun_sim_res_seq NumTab;
3070 lun_res_priority NumTab;
3071 lun_res_seq NumTab;
3072 lun_resource_id NumTab;
3073 lun_basis_type NumTab;
3074 lun_res_required NumTab;
3075
3076
3077 elsud_op_seq_num NumTab;
3078 elsud_sim_res_seq NumTab;
3079 elsud_res_seq NumTab;
3080 elsud_jdi_rowid RIDTab;
3081 elsud_sub_grp NumTab;
3082 elsud_rep_grp NumTab;
3083
3084 lv_elsud_cnt NUMBER;
3085
3086 v_previous_op number;
3087 v_previous_res number;
3088 v_previous_res1 number;
3089 v_next_op number;
3090 v_next_res number;
3091 v_next_res1 number;
3092 v_last_end date;
3093 v_frst_end date;
3094 v_last_start date;
3095 v_frst_start date;
3096
3097 luno_op_count number;
3098 luno_op_seq_num NumTab;
3099 luno_std_op_seq_id NumTab;
3100
3101
3102
3103 lv_res_alt_op_seq_num NumTab;
3104 lv_res_alt_schd_seq_num NumTab;
3105 lv_res_alt_prin_flag NumTab;
3106 lv_res_alt_res_seq_num NumTab;
3107 lv_res_alt_res_id NumTab;
3108 lv_res_alt_hdr_id NumTab;
3109 lv_res_alt_org_id NumTab;
3110 lv_res_alt_rowid RIDTab;
3111 lv_res_alt_sub_grp_num NumTab;
3112 lv_res_alt_rep_grp_num NumTab;
3113 lv_res_alt_start_date DateTab;
3114 lv_res_alt_completion_date DateTab;
3115 lv_res_alt_usage_rate NumTab;
3116 lv_res_alt_basis_type NumTab;
3117 lv_res_alt_req_count Number;
3118
3119
3120
3121 lsud_header_id NumTab;
3122 lsud_sub_grp NumTab;
3123 lsud_new_usage NumTab;
3124 lsud_rep_grp NumTab;
3125 lsud_organization_id Numtab;
3126 lsud_op_seq_num NumTab;
3127 lsud_sim_res_seq NumTab;
3128 lsud_res_priority NumTab;
3129 lsud_res_seq NumTab;
3130 lsud_resource_id NumTab;
3131 lsud_jdi_rowid RIDTab;
3132 lsud_resource_id_del NumTab;
3133 lsud_basis_type NumTab;
3134 lsud_res_req_count Number;
3135 lsud_row NumTab;
3136 lsud_start_date DateTab;
3137 lsud_completion_Date DateTab;
3138
3139 -- dsr added following 7 lines
3140 lsud_firm_flag NumTab;
3141 lsud_setup_id NumTab;
3142 lsud_group_sequence_id NumTab;
3143 lsud_group_sequence_number NumTab;
3144 lsud_batch_id NumTab;
3145 lsud_maximum_assigned_units NumTab;
3146 lsud_parent_seq_num NumTab;
3147
3148
3149 lv_init_k NUMBER;
3150 temp_res_id NUMBER;
3151 lv_scheduled_flag NumTab;
3152 lv_parent_id NumTab;
3153 lv_job_schedule_type NumTab;
3154 lv_wip_entity_id NumTab;
3155
3156 BEGIN
3157
3158
3159 SELECT jsi.rowid,
3160 jsi.header_id,
3161 jsi.organization_id,
3162 jsi.first_unit_start_date,
3163 jsi.last_unit_completion_date,
3164 rtng.common_routing_sequence_id,
3165 jsi.load_type,
3166 jsi.wip_entity_id
3167 BULK COLLECT
3168 INTO lv_jsi_rowid,
3169 lv_header_id,
3170 lv_organization_id,
3171 lv_start_date,
3172 lv_end_date,
3173 lv_routing_seq_id,
3174 lv_job_schedule_type,
3175 lv_wip_entity_id
3176 FROM BOM_OPERATIONAL_ROUTINGS rtng,
3177 wip_job_schedule_interface jsi
3178 WHERE jsi.group_id = G_WIP_GROUP_ID
3179 AND jsi.load_type in(1,3) /* create standard job */
3180 AND rtng.assembly_item_id(+)= jsi.primary_item_id
3181 AND rtng.organization_id(+)= jsi.organization_id
3182 AND NVL(rtng.alternate_routing_designator(+),' ')=
3183 NVL( jsi.alternate_routing_designator,' ');
3184
3185 lv_std_job_count:= SQL%ROWCOUNT;
3186
3187 IF lv_std_job_count= 0 THEN RETURN; END IF;
3188
3189 FOR n IN 1..lv_std_job_count LOOP
3190
3191 BEGIN
3192 SELECT jdi.ROWID,
3193 jdi.operation_seq_num,
3194 -- jdi.resource_seq_num,
3195 nvl(jdi.schedule_seq_num, jdi.resource_seq_num),
3196 TO_NUMBER(nvl(jdi.REPLACEMENT_GROUP_NUM,0)),
3197 jdi.resource_id_old,
3198 jdi.resource_seq_num,
3199 jdi.substitution_type,
3200 jdi.resource_id_old,
3201 jdi.load_type,
3202 jdi.scheduled_flag,
3203 jdi.parent_header_id
3204 BULK COLLECT
3205 INTO lv_jdi_rowid,
3206 lv_op_seq_num,
3207 lv_sim_res_seq,
3208 lv_res_priority,
3209 lv_resource_id,
3210 lv_res_seq,
3211 lv_sub_type,
3212 lv_resource_id_old,
3213 lv_ld_type,
3214 lv_scheduled_flag,
3215 lv_parent_id
3216 FROM WIP_JOB_DTLS_INTERFACE jdi
3217 WHERE jdi.group_id= G_WIP_GROUP_ID
3218 AND jdi.parent_header_id= lv_header_id(n)
3219 -- dsr AND jdi.load_type=LT_RESOURCE
3220 AND jdi.load_type IN (LT_RESOURCE, LT_RESOURCE_USAGE
3221 , RESOURCE_INSTANCES, RESOURCE_INSTANCE_USAGE
3222 )
3223 ORDER BY
3224 2,3,5;
3225
3226 lv_res_req_count:= SQL%ROWCOUNT;
3227
3228 EXCEPTION
3229 WHEN OTHERS THEN RAISE;
3230 END;
3231
3232 IF nvl(lv_res_req_count,0) = 0 THEN
3233
3234 UPDATE wip_job_schedule_interface
3235 SET first_unit_start_date=NULL,
3236 scheduling_method= WIP_CONSTANTS.ROUTING
3237 WHERE ROWID= lv_jsi_rowid(n);
3238
3239 GOTO next_c_std_job;
3240 ELSE
3241
3242
3243 UPDATE wip_job_schedule_interface
3244 set scheduling_method= WIP_CONSTANTS.ML_MANUAL
3245 WHERE ROWID= lv_jsi_rowid(n);
3246
3247 END IF;
3248
3249 If nvl(lv_job_schedule_type(n),1) = 1 Then
3250
3251 SELECT os.operation_seq_num,
3252 to_number(decode(nvl(bor.schedule_seq_num,-1),-1,bor.resource_seq_num,bor.schedule_seq_num)),
3253 0,
3254 bor.resource_seq_num,
3255 bor.schedule_seq_num,
3256 bor.resource_id,
3257 2
3258 BULK COLLECT
3259 INTO lu_op_seq_num,
3260 lu_sim_res_seq,
3261 lu_res_priority,
3262 lu_res_seq,
3263 lu_schedule_seq_num,
3264 lu_resource_id,
3265 lu_res_required
3266 FROM BOM_OPERATION_RESOURCES bor,
3267 BOM_OPERATION_SEQUENCES os
3268 WHERE os.routing_sequence_id= lv_routing_seq_id(n)
3269 AND bor.operation_sequence_id= os.operation_sequence_id
3270 AND os.effectivity_date <= lv_start_date(n)
3271 AND NVL(os.disable_date, lv_start_date(n)) >= lv_start_date(n)
3272 ORDER BY
3273 1,2,3 ASC,5;
3274
3275
3276
3277 lu_res_req_count:= SQL%ROWCOUNT;
3278
3279
3280 BEGIN
3281
3282 SELECT os.operation_seq_num,os.standard_operation_id
3283 BULK COLLECT
3284 INTO luno_op_seq_num,luno_std_op_Seq_id
3285 FROM BOM_OPERATION_SEQUENCES os
3286 WHERE os.routing_sequence_id= lv_routing_seq_id(n)
3287 AND os.effectivity_date <= lv_start_date(n)
3288 AND NVL(os.disable_date, lv_start_date(n)) >= lv_start_date(n)
3289 AND not exists(select jdi.operation_seq_num
3290 FROM BOM_OPERATIONAL_ROUTINGS rtng,
3291 BOM_OPERATION_SEQUEnCES seqs,
3292 wip_job_dtls_interface jdi,
3293 wip_job_schedule_interface jsi
3294 where rtng.assembly_item_id= jsi.primary_item_id
3295 AND rtng.organization_id= jsi.organization_id
3296 and nvl(rtng.common_routing_sequence_id,rtng.routing_sequence_id) = os.routing_Sequence_id
3297 AND nvl(rtng.common_routing_sequence_id,rtng.routing_Sequence_id) = seqs.routing_sequence_id
3298 AND NVL(rtng.alternate_routing_designator,' ')=
3299 NVL( jsi.alternate_routing_designator,' ')
3300 and jsi.header_id = jdi.parent_header_id
3301 and jdi.load_type = 3
3302 and seqs.operation_seq_num = os.operation_seq_num
3303 and seqs.operation_seq_num = jdi.operation_seq_nuM
3304 and jdi.parent_header_id = lv_header_id(n));
3305
3306
3307 luno_op_count:= SQL%ROWCOUNT;
3308
3309
3310 EXCEPTION WHEN OTHERS THEN
3311 RAISE;
3312 END;
3313
3314
3315 SELECT os.operation_seq_num,
3316 to_number(decode(nvl(bor.schedule_seq_num,-1),-1,bor.resource_seq_num,bor.schedule_seq_num)),
3317 0,
3318 bor.resource_seq_num,
3319 bor.resource_id,
3320 bor.basis_type,
3321 bor.usage_rate_or_Amount,
3322 2
3323 BULK COLLECT
3324 INTO lun_op_seq_num,
3325 lun_sim_res_seq,
3326 lun_res_priority,
3327 lun_res_seq,
3328 lun_resource_id,
3329 lun_basis_type,
3330 lun_usage,
3331 lun_res_required
3332 FROM BOM_OPERATION_RESOURCES bor,
3333 BOM_OPERATION_SEQUENCES os,
3334 BOM_RESOURCES br
3335 WHERE os.routing_sequence_id= lv_routing_seq_id(n)
3336 AND bor.operation_sequence_id= os.operation_sequence_id
3337 AND os.effectivity_date <= lv_start_date(n)
3338 -- AND NVL(os.disable_date, lv_start_date(n)) >= lv_start_date(n)
3339 AND NVL(os.disable_date, nvl(br.disable_date, lv_start_date(n))) >= lv_start_date(n) -- bug# 4290120
3340 AND br.resource_id = bor.resource_id
3341 and schedule_flag = 2
3342 AND not exists(select jdi.resource_id_new
3343 FROM BOM_OPERATIONAL_ROUTINGS rtng,
3344 BOM_OPERATION_SEQUENCES seqs,
3345 wip_job_dtls_interface jdi,
3346 wip_job_schedule_interface jsi
3347 where rtng.assembly_item_id= jsi.primary_item_id
3348 AND rtng.organization_id= jsi.organization_id
3349 AND nvl(rtng.common_routing_sequence_id,rtng.routing_sequence_id) = seqs.routing_sequence_id
3350 AND NVL(rtng.alternate_routing_designator,' ')=
3351 NVL( jsi.alternate_routing_designator,' ')
3352 and jsi.header_id = jdi.parent_header_id
3353 and jdi.load_type = 1
3354 and jdi.resource_id_new = bor.resource_id
3355 and nvl(rtng.common_routing_sequence_id,rtng.routing_sequence_id) = os.routing_Sequence_id
3356 and seqs.operation_seq_num = os.operation_seq_num
3357 and seqs.operation_seq_num = jdi.operation_seq_nuM
3358 and jdi.parent_header_id = lv_header_id(n));
3359
3360
3361 lun_res_req_count:= SQL%ROWCOUNT;
3362
3363 SELECT distinct os.operation_seq_num,
3364 to_number(bor.schedule_seq_num),
3365 bor.principle_flag,
3366 to_number(bor1.resource_seq_num),
3367 bor.resource_id,
3368 bor1.resource_id,
3369 jdi.parent_header_id,
3370 jdi.organization_id,
3371 jdi.rowid,
3372 bor1.substitute_group_num,
3373 jdi.REPLACEMENT_GROUP_NUM,
3374 jdi.start_date,
3375 jdi.completion_date,
3376 jdi.usage_rate_or_amount,
3377 bor.basis_type
3378 -- dsr: added the following 7 columns
3379 , jdi.firm_flag
3380 , jdi.setup_id
3381 , jdi.group_sequence_id
3382 , jdi.group_sequence_number
3383 , jdi.batch_id
3384 , jdi.maximum_assigned_units
3385 , jdi.parent_seq_num
3386 -- , resource_seq_num
3387 -- , schedule_seq_num
3388 BULK COLLECT
3389 INTO lsud_op_seq_num,
3390 lsud_sim_res_seq,
3391 lsud_res_priority,
3392 lsud_res_seq,
3393 lsud_resource_id,
3394 lsud_resource_id_del,
3395 lsud_header_id,
3396 lsud_organization_id,
3397 lsud_jdi_rowid,
3398 lsud_sub_grp,
3399 lsud_rep_grp,
3400 lsud_start_date,
3401 lsud_completion_date,
3402 lsud_new_usage,
3403 lsud_basis_type
3404 -- dsr added following 7 lines
3405 , lsud_firm_flag
3406 , lsud_setup_id
3407 , lsud_group_sequence_id
3408 , lsud_group_sequence_number
3409 , lsud_batch_id
3410 , lsud_maximum_assigned_units
3411 , lsud_parent_seq_num
3412 -- , lsud_schedule_seq_num
3413 FROM BOM_SUB_OPERATION_RESOURCES bor,
3414 bom_operation_resources bor1,
3415 BOM_OPERATION_SEQUENCES os,
3416 BOM_RESOURCES br,
3417 WIP_JOB_DTLS_INTERFACE JDI
3418 WHERE os.routing_sequence_id= lv_routing_seq_id(n)
3419 AND bor.operation_sequence_id= os.operation_sequence_id
3420 AND os.effectivity_date <= lv_start_date(n)
3421 -- AND NVL(os.disable_date, lv_start_date(n)) >= lv_start_date(n)
3422 AND NVL(os.disable_date, nvl(br.disable_date,lv_start_date(n))) >= lv_start_date(n)
3423 AND br.resource_id = bor.resource_id
3424 AND bor.operation_sequence_id= bor1.operation_sequence_id
3425 and bor.substitute_group_num = bor1.substitute_group_num
3426 and bor.schedule_seq_num = bor1.schedule_seq_num
3427 AND TO_NUMBER(bor.schedule_seq_num) IS NOT NULL
3428 AND JDI.RESOURCE_ID_NEW = BOR.RESOURCE_ID
3429 -- and jdi.resource_seq_num = bor1.resource_seq_num
3430 and jdi.schedule_seq_num = bor1.schedule_seq_num
3431 /*The resource_seq in jdi is actually the schedule_seq*/
3432 AND JDI.GROUP_ID =G_WIP_GROUP_ID
3433 and jdi.parent_header_id = lv_header_id(n) /* Bug # 2671426 - Forward Port for Bug 2657820 */
3434 and nvl(jdi.REPLACEMENT_GROUP_NUM,-1) <> 0
3435 and bor.replacement_group_num = nvl(jdi.REPLACEMENT_GROUP_NUM,-100)
3436 -- dsr and jdi.load_type = 1
3437 and jdi.load_type IN (1, LT_RESOURCE_USAGE, RESOURCE_INSTANCES, RESOURCE_INSTANCE_USAGE)
3438 ORDER BY
3439 1,2,3 ASC,5;
3440
3441 lsud_res_req_count:= SQL%ROWCOUNT;
3442
3443 lv_init_k:= 1;
3444
3445
3446 /* If we are recommending any Resource changes, then Get the Resource_seq_num for the Resource from BOM Tables. (Since we did not collect this - We only collected schedule_seq_num */
3447
3448 FOR j IN 1..lv_res_req_count LOOP
3449
3450
3451 -- FOR k in lv_init_k..lu_res_req_count LOOP
3452 /* Bug : 1967136 , in order to fix resource id does not exists issue*/
3453 FOR k in 1..lu_res_req_count LOOP
3454
3455 IF lv_op_seq_num(j) = lu_op_seq_num(k) AND
3456 lv_sim_res_seq(j) = lu_sim_res_seq(k) AND
3457 lv_res_priority(j)= lu_res_priority(k) AND
3458 lv_resource_id (j)= lu_resource_id(k) THEN
3459
3460 -- lv_res_seq(j):= lu_res_seq(k);
3461 lu_res_required(k):= 1;
3462
3463 lv_init_k:= k+1;
3464 EXIT;
3465 END IF;
3466
3467 END LOOP; -- k
3468
3469 END LOOP;
3470
3471
3472
3473 lv_init_k:= 1;
3474
3475
3476 /* Based on the Resource_Seq_num we got above, update the records with that resource_seq_num
3477 for the resource records in WIP_JOB_DETAILS_INTERFACE, (the one's we are created) */
3478
3479 FOR j IN 1..lv_res_req_count
3480
3481 Loop
3482
3483
3484
3485 UPDATE WIP_JOB_DTLS_INTERFACE
3486 SET resource_seq_num= lv_res_seq(j),
3487 resource_id_old = lv_resource_id(j),
3488 schedule_seq_num = lv_sim_res_seq(j),
3489 scheduled_flag = lv_scheduled_flag(j)
3490 WHERE ROWID= lv_jdi_rowid(j)
3491 and lv_res_priority(j) = 0; --Update only the primary resources
3492
3493 End loop;
3494
3495
3496 /* HANDLE ALTERNATE RESOURCES */
3497 /* If we are enforcing use of a alternate resource, then we first need to communicate to WIP to
3498 delete the primary resource which wip will get by exploding the routing. This loop Below will
3499 loop through all Resource records where we are implementing alternate and delete the primaries
3500 for that alternate -- this was old logic*/
3501 /* Now if we communicating to WIP that we are passing alternate resource to WIP,
3502 for a resource
3503 substitution, the record in wip_job_dtls_interface should have the
3504 following columns filled in:
3505 load_type = 1
3506 substitution_type = 3
3507 opearation_seq_num, resource_seq_num, resource_id_old, resource_id_new,
3508 and substitute_group_num should be set to the current(or primary) resource in
3509 wip_operation_resources
3510 replacement_group_num = valid value in wip_sub_operation_resources.Finally the
3511 alternate resource record will be deleted from wip_job_dtls_interface*/
3512
3513 FOR j IN 1..lsud_res_req_count
3514
3515 Loop
3516 Begin
3517
3518 INSERT INTO WIP_JOB_DTLS_INTERFACE
3519 ( last_update_date,
3520 last_updated_by,
3521 last_update_login,
3522 creation_date,
3523 created_by,
3524 group_id,
3525 parent_header_id,
3526 operation_seq_num,
3527 resource_seq_num,
3528 resource_id_old,
3529 resource_id_new,
3530 replacement_group_num,
3531 substitute_group_num,
3532 start_date,
3533 completion_date,
3534 organization_id,
3535 substitution_type,
3536 load_type,
3537 process_phase,
3538 process_status,
3539 scheduled_flag
3540 -- dsr: added the following 7 columns
3541 , firm_flag
3542 , setup_id
3543 , group_sequence_id
3544 , group_sequence_number
3545 , batch_id
3546 , maximum_assigned_units
3547 , parent_seq_num
3548 -- , resource_seq_num
3549 -- , schedule_seq_num
3550 )
3551 SELECT SYSDATE,
3552 FND_GLOBAL.USER_ID,
3553 FND_GLOBAL.USER_ID,
3554 SYSDATE,
3555 FND_GLOBAL.USER_ID,
3556 G_WIP_GROUP_ID,
3557 lsud_header_id(j),
3558 lsud_op_seq_num(j),
3559 lsud_res_seq(j),
3560 lsud_resource_id_del(j),
3561 lsud_resource_id_del(j),
3562 lsud_rep_grp(j),
3563 lsud_sub_grp(j),
3564 lsud_start_date(j),
3565 lsud_completion_date(j),
3566 lsud_organization_id(j),
3567 3,
3568 1,
3569 2,
3570 1,
3571 1 --lv_scheduled_flag(j)
3572 -- dsr: added the following 7 columns
3573 , lsud_firm_flag(j)
3574 , lsud_setup_id(j)
3575 , lsud_group_sequence_id(j)
3576 , lsud_group_sequence_number(j)
3577 , lsud_batch_id(j)
3578 , lsud_maximum_assigned_units(j)
3579 , lsud_parent_seq_num(j)
3580 -- , lsud_schedule_seq_num(j)
3581 FROM DUAL;
3582
3583
3584 /*Delete the Alternate that we provided */
3585 delete from WIP_JOB_DTLS_INTERFACE
3586 WHERE ROWID= lsud_jdi_rowid(j);
3587 Exception
3588 when others then raise;
3589 End;
3590
3591 End loop;
3592
3593 /* HANDLE UNSCHEDULES OPERATIONS AND RESOURCES */
3594 /* Since WIP Will Pull into the Job , all operations that APS didn't even collect
3595 (scheduled=no), we will set the start and end time of these operations to the
3596 fall in between the previous op from this op and the next op from this op */
3597
3598
3599 FOR M in 1..luno_op_count
3600
3601 Loop
3602
3603
3604 BEGIN
3605 select nvl(max(operation_seq_num),-1)
3606 into v_previous_op
3607 from wip_job_dtls_interface
3608 where parent_header_id = lv_header_id(n)
3609 and group_id = G_WIP_GROUP_ID
3610 and load_type = 3
3611 and substitution_type <> 1
3612 and operation_seq_num < luno_op_seq_num(m);
3613
3614 select nvl(min(operation_seq_num),-1)
3615 into v_next_op
3616 from wip_job_dtls_interface
3617 where parent_header_id = lv_header_id(n)
3618 and group_id = G_WIP_GROUP_ID
3619 and substitution_type <> 1
3620 and load_type = 3
3621 and operation_seq_num > luno_op_seq_num(m);
3622
3623
3624 /* If v_previous_op = -1 and v_next_op = -1 , Do nothing */
3625 /* as we will not plan for just 1 un-scheduled opeartion, if it exists */
3626
3627 if (v_previous_op = -1 and v_next_op <> -1) then
3628
3629 Select first_unit_start_date
3630 into v_last_end
3631 from wip_job_dtls_interface
3632 where parent_header_id = lv_header_id(n)
3633 and group_id = G_WIP_GROUP_ID
3634 and load_type = 3
3635 and operation_seq_num = v_next_op;
3636
3637 v_last_start := v_last_end;
3638 v_frst_start := v_last_end;
3639 v_frst_end := v_last_end;
3640
3641 elsif (v_next_op = -1 and v_previous_op <> -1) then
3642
3643 Select last_unit_completion_date
3644 into v_frst_start
3645 from wip_job_dtls_interface
3646 where parent_header_id = lv_header_id(n)
3647 and group_id = G_WIP_GROUP_ID
3648 and load_type = 3
3649 and operation_seq_num = v_previous_op;
3650
3651 v_frst_end := v_frst_start;
3652 v_last_start := v_frst_start;
3653 v_last_end := v_frst_start;
3654
3655 else
3656
3657 Select first_unit_start_date
3658 into v_last_end
3659 from wip_job_dtls_interface
3660 where parent_header_id = lv_header_id(n)
3661 and group_id = G_WIP_GROUP_ID
3662 and load_type = 3
3663 and operation_seq_num = v_next_op;
3664
3665 v_last_start := v_last_end;
3666
3667 Select last_unit_completion_date
3668 into v_frst_start
3669 from wip_job_dtls_interface
3670 where parent_header_id = lv_header_id(n)
3671 and group_id = G_WIP_GROUP_ID
3672 and load_type = 3
3673 and operation_seq_num = v_previous_op;
3674
3675 v_frst_end := v_frst_start;
3676
3677 end if;
3678
3679 EXCEPTION WHEN OTHERS THEN
3680 RAISE;
3681 END;
3682
3683 INSERT INTO WIP_JOB_DTLS_INTERFACE
3684 ( last_update_date,
3685 last_updated_by,
3686 last_update_login,
3687 creation_date,
3688 created_by,
3689 group_id,
3690 parent_header_id,
3691 operation_seq_num,
3692 standard_operation_id,
3693 organization_id,
3694 substitution_type,
3695 load_type,
3696 first_unit_start_date,
3697 first_unit_completion_date,
3698 last_unit_start_date,
3699 last_unit_completion_date,
3700 process_phase,
3701 process_status,
3702 scheduled_flag)
3703 SELECT SYSDATE,
3704 FND_GLOBAL.USER_ID,
3705 FND_GLOBAL.USER_ID,
3706 SYSDATE,
3707 FND_GLOBAL.USER_ID,
3708 G_WIP_GROUP_ID,
3709 lv_header_id(n),
3710 luno_op_seq_num(m),
3711 luno_std_op_seq_id(m),
3712 lv_organization_id(n),
3713 3,
3714 3,
3715 v_frst_start,
3716 v_frst_end,
3717 v_last_start,
3718 v_last_end,
3719 2,
3720 1,
3721 2 --lv_scheduled_flag(j)
3722 FROM DUAL;
3723
3724 End loop;
3725
3726 FOR j IN 1..lun_res_req_count
3727
3728 Loop
3729 Begin
3730
3731 BEGIN
3732 select max(operation_seq_num)
3733 into v_previous_op
3734 from wip_job_dtls_interface
3735 where parent_header_id = lv_header_id(n)
3736 and group_id = G_WIP_GROUP_ID
3737 and load_type = 3
3738 and substitution_type <> 1
3739 and operation_seq_num <= lun_op_seq_num(j);
3740
3741 select min(operation_seq_num)
3742 into v_next_op
3743 from wip_job_dtls_interface
3744 where parent_header_id = lv_header_id(n)
3745 and group_id = G_WIP_GROUP_ID
3746 and substitution_type <> 1
3747 and load_type = 3
3748 and operation_seq_num >= lun_op_seq_num(j);
3749
3750 select nvl(max(resource_seq_num),-1)
3751 into v_previous_res
3752 from wip_job_dtls_interface s
3753 where s.parent_header_id = lv_header_id(n)
3754 and s.group_id = G_WIP_GROUP_ID
3755 and s.load_type = 1
3756 and s.substitution_type <> 1
3757 and s.operation_seq_num = lun_op_seq_num(j)
3758 and nvl(s.schedule_seq_num,s.resource_seq_num) < lun_res_seq(j);
3759
3760
3761 select nvl(max(schedule_seq_num),-1)
3762 into v_previous_res1
3763 from wip_job_dtls_interface s
3764 where s.parent_header_id = lv_header_id(n)
3765 and s.group_id = G_WIP_GROUP_ID
3766 and s.load_type = 1
3767 and s.substitution_type <> 1
3768 and s.operation_seq_num = lun_op_seq_num(j)
3769 and nvl(s.schedule_seq_num,s.resource_seq_num) < lun_res_seq(j);
3770
3771 if (v_previous_res1 > v_previous_res) then
3772 select nvl(max(resource_seq_num),-1)
3773 into v_previous_res
3774 from wip_job_dtls_interface s
3775 where s.parent_header_id = lv_header_id(n)
3776 and s.group_id = G_WIP_GROUP_ID
3777 and s.load_type = 1
3778 and s.substitution_type <> 1
3779 and s.operation_seq_num = lun_op_seq_num(j)
3780 and nvl(s.schedule_seq_num,s.resource_seq_num) < lun_res_seq(j)
3781 and s.schedule_seq_num = v_previous_res1
3782 and rownum=1;
3783 end if ;
3784
3785 select nvl(min(resource_seq_num),-1)
3786 into v_next_res
3787 from wip_job_dtls_interface s
3788 where s.parent_header_id = lv_header_id(n)
3789 and s.group_id = G_WIP_GROUP_ID
3790 and s.substitution_type <> 1
3791 and s.load_type = 1
3792 and s.operation_seq_num = lun_op_seq_num(j)
3793 and nvl(s.schedule_seq_num,s.resource_seq_num) > lun_res_seq(j);
3794
3795 select nvl(min(schedule_seq_num),-1)
3796 into v_next_res1
3797 from wip_job_dtls_interface s
3798 where s.parent_header_id = lv_header_id(n)
3799 and s.group_id = G_WIP_GROUP_ID
3800 and s.substitution_type <> 1
3801 and s.load_type = 1
3802 and s.operation_seq_num = lun_op_seq_num(j)
3803 and nvl(s.schedule_seq_num,s.resource_seq_num) > lun_res_seq(j);
3804
3805 if (v_next_res1 < v_next_res) then
3806 select nvl(resource_seq_num,-1)
3807 into v_next_res
3808 from wip_job_dtls_interface s
3809 where s.parent_header_id = lv_header_id(n)
3810 and s.group_id = G_WIP_GROUP_ID
3811 and s.substitution_type <> 1
3812 and s.load_type = 1
3813 and s.operation_seq_num = lun_op_seq_num(j)
3814 and nvl(s.schedule_seq_num,s.resource_seq_num) > lun_res_seq(j)
3815 and s.schedule_seq_num = v_next_res1
3816 and rownum=1;
3817 end if ;
3818
3819
3820 if (v_previous_res = -1 and v_next_res = -1)
3821
3822
3823 then
3824
3825
3826
3827 Select first_unit_start_date
3828 into v_last_end
3829 from wip_job_dtls_interface
3830 where parent_header_id = lv_header_id(n)
3831 and group_id = G_WIP_GROUP_ID
3832 and load_type = 3
3833 and operation_seq_num = v_next_op;
3834
3835 v_last_start := v_last_end;
3836
3837 Select last_unit_completion_date
3838 into v_frst_start
3839 from wip_job_dtls_interface
3840 where parent_header_id = lv_header_id(n)
3841 and group_id = G_WIP_GROUP_ID
3842 and load_type = 3
3843 and operation_seq_num = v_previous_op;
3844
3845 v_frst_end := v_frst_start;
3846
3847
3848 elsif (v_previous_res = -1 and v_next_res <> -1)
3849
3850
3851 then
3852
3853 Select start_date
3854 into v_last_end
3855 from wip_job_dtls_interface
3856 where parent_header_id = lv_header_id(n)
3857 and group_id = G_WIP_GROUP_ID
3858 and load_type = 1
3859 and operation_seq_num = v_next_op
3860 and resource_Seq_num = v_next_res
3861 and rownum = 1;
3862
3863 v_last_start := v_last_end;
3864
3865 Select start_date
3866 into v_frst_start
3867 from wip_job_dtls_interface
3868 where parent_header_id = lv_header_id(n)
3869 and group_id = G_WIP_GROUP_ID
3870 and load_type = 1
3871 and operation_seq_num = v_previous_op
3872 and resource_Seq_num = v_next_res
3873 and rownum = 1;
3874
3875 v_frst_end := v_frst_start;
3876
3877 elsif (v_previous_res <> -1 and v_next_res = -1)
3878
3879 then
3880
3881 Select completion_date
3882 into v_last_end
3883 from wip_job_dtls_interface
3884 where parent_header_id = lv_header_id(n)
3885 and group_id = G_WIP_GROUP_ID
3886 and load_type = 1
3887 and operation_seq_num = v_next_op
3888 and resource_Seq_num = v_previous_res
3889 and rownum = 1;
3890
3891 v_last_start := v_last_end;
3892
3893 Select completion_date
3894 into v_frst_start
3895 from wip_job_dtls_interface
3896 where parent_header_id = lv_header_id(n)
3897 and group_id = G_WIP_GROUP_ID
3898 and load_type = 1
3899 and operation_seq_num = v_previous_op
3900 and resource_Seq_num = v_previous_res
3901 and rownum = 1;
3902
3903 v_frst_end := v_frst_start;
3904
3905 else
3906
3907 Select start_date
3908 into v_last_end
3909 from wip_job_dtls_interface
3910 where parent_header_id = lv_header_id(n)
3911 and group_id = G_WIP_GROUP_ID
3912 and load_type = 1
3913 and operation_seq_num = v_next_op
3914 and resource_Seq_num = v_next_res
3915 and rownum = 1;
3916
3917 v_last_start := v_last_end;
3918
3919 Select completion_date
3920 into v_frst_start
3921 from wip_job_dtls_interface
3922 where parent_header_id = lv_header_id(n)
3923 and group_id = G_WIP_GROUP_ID
3924 and load_type = 1
3925 and operation_seq_num = v_previous_op
3926 and resource_Seq_num = v_previous_res
3927 and rownum = 1;
3928
3929 v_frst_end := v_frst_start;
3930
3931 End if;
3932
3933 EXCEPTION WHEN OTHERS THEN
3934 RAISE;
3935 END;
3936
3937 /*insert record for the resource with schedule = no with start and end time
3938 as that of the opertaion*/
3939 INSERT INTO WIP_JOB_DTLS_INTERFACE
3940 ( last_update_date,
3941 last_updated_by,
3942 last_update_login,
3943 creation_date,
3944 created_by,
3945 group_id,
3946 parent_header_id,
3947 operation_seq_num,
3948 resource_seq_num,
3949 resource_id_old,
3950 resource_id_new,
3951 basis_type,
3952 usage_rate_or_amount,
3953 organization_id,
3954 substitution_type,
3955 load_type,
3956 start_date,
3957 completion_date,
3958 process_phase,
3959 process_status,
3960 scheduled_flag)
3961 SELECT SYSDATE,
3962 FND_GLOBAL.USER_ID,
3963 FND_GLOBAL.USER_ID,
3964 SYSDATE,
3965 FND_GLOBAL.USER_ID,
3966 G_WIP_GROUP_ID,
3967 lv_header_id(n),
3968 lun_op_seq_num(j),
3969 lun_res_seq(j),
3970 lun_resource_id(j),
3971 lun_resource_id(j),
3972 lun_basis_type(j),
3973 lun_usage(j),
3974 lv_organization_id(n),
3975 3,
3976 1,
3977 v_frst_start,
3978 v_last_end,
3979 2,
3980 1,
3981 2 --lv_scheduled_flag(j)
3982 FROM DUAL;
3983
3984 End;
3985
3986
3987 End loop;
3988
3989 ELSE
3990
3991 BEGIN
3992
3993 SELECT wor.operation_seq_num,
3994 wor.schedule_seq_num,
3995 wor.resource_seq_num,
3996 jdi.rowid,
3997 wor.substitute_group_num,
3998 jdi.REPLACEMENT_GROUP_NUM
3999 BULK COLLECT
4000 INTO elsud_op_seq_num,
4001 elsud_sim_res_seq,
4002 elsud_res_seq,
4003 elsud_jdi_rowid,
4004 elsud_sub_grp,
4005 elsud_rep_grp
4006 FROM WIP_OPERATION_RESOURCES wor,
4007 WIP_JOB_DTLS_INTERFACE JDI
4008 WHERE wor.operation_seq_num = jdi.operation_seq_num
4009 and nvl(wor.schedule_seq_num,wor.resource_seq_num) = jdi.schedule_seq_num
4010 -- and wor.resource_seq_num = jdi.resource_seq_num
4011 and nvl(wor.replacement_group_num,0) = nvl(jdi.replacement_group_num,0)
4012 and jdi.resource_id_new = wor.resource_id
4013 and jdi.resource_seq_num = wor.resource_seq_num
4014 -- dsr and jdi.load_type = 1
4015 and jdi.load_type IN (1, LT_RESOURCE_USAGE, RESOURCE_INSTANCES, RESOURCE_INSTANCE_USAGE)
4016 and jdi.parent_header_id = lv_header_id(n)
4017 and JDI.GROUP_ID =G_WIP_GROUP_ID
4018 and wor.wip_entity_id = lv_wip_entity_id(n)
4019 and nvl(wor.repetitive_schedule_id ,-1)= -1
4020 and jdi.parent_seq_num is null
4021 ;
4022
4023 lv_elsud_cnt := SQL%ROWCOUNT;
4024
4025 FOR x IN 1..lv_elsud_cnt
4026
4027 Loop
4028
4029 UPDATE WIP_JOB_DTLS_INTERFACE
4030 SET
4031 -- resource_seq_num= elsud_res_seq(x),
4032 schedule_seq_num = elsud_sim_res_seq(x),
4033 substitute_group_num = elsud_sub_grp(x)
4034 WHERE ROWID= elsud_jdi_rowid(x);
4035
4036
4037 End loop;
4038
4039
4040 Exception
4041 when NO_DATA_FOUND THEN
4042 Null;
4043 When others THEN raise;
4044
4045 END;
4046
4047 BEGIN
4048
4049 select wor.operation_seq_num,
4050 wsor.schedule_seq_num,
4051 wsor.principle_flag,
4052 wor.resource_seq_num,
4053 wor.resource_id,
4054 jdi.parent_header_id,
4055 jdi.organization_id,
4056 jdi.rowid,
4057 wor.substitute_group_num,
4058 jdi.REPLACEMENT_GROUP_NUM,
4059 jdi.start_date,
4060 jdi.completion_date,
4061 jdi.usage_rate_or_amount,
4062 wsor.basis_type
4063 BULK COLLECT INTO lv_res_alt_op_seq_num,
4064 lv_res_alt_schd_seq_num,
4065 lv_res_alt_prin_flag,
4066 lv_res_alt_res_seq_num,
4067 lv_res_alt_res_id,
4068 lv_res_alt_hdr_id,
4069 lv_res_alt_org_id,
4070 lv_res_alt_rowid,
4071 lv_res_alt_sub_grp_num,
4072 lv_res_alt_rep_grp_num,
4073 lv_res_alt_start_date,
4074 lv_res_alt_completion_date,
4075 lv_res_alt_usage_rate,
4076 lv_res_alt_basis_type
4077 from wip_job_dtls_interface jdi,
4078 wip_operation_resources wor,
4079 wip_sub_operation_resources wsor
4080 where not exists(select 1 from wip_operation_resources wor1
4081 where wor1.operation_seq_num = jdi.operation_seq_num
4082 and nvl(wor1.schedule_seq_num,wor1.resource_seq_num) = jdi.schedule_seq_num
4083 -- and wor1.resource_seq_num = jdi.resource_seq_num
4084 and nvl(wor1.replacement_group_num,0) = nvl(jdi.replacement_group_num,0)
4085 and wor1.wip_entity_id = lv_wip_entity_id(n)
4086 and nvl(wor1.repetitive_schedule_id ,-1) = -1 )
4087 and wsor.operation_seq_num = wor.operation_seq_num
4088 and wsor.wip_entity_id = wor.wip_entity_id
4089 and nvl(wsor.repetitive_schedule_id,-1) = nvl(wor.repetitive_schedule_id,-1)
4090 and wor.substitute_group_num = wsor.substitute_group_num
4091 and wsor.wip_entity_id = lv_wip_entity_id(n)
4092 and wsor.resource_id = jdi.resource_id_old
4093 and wsor.operation_seq_num = jdi.operation_seq_num
4094 and nvl(wsor.schedule_seq_num,wsor.resource_seq_num) = jdi.schedule_seq_num
4095 -- and wsor.resource_seq_num = jdi.resource_seq_num
4096 and nvl(wsor.replacement_group_num,0) = nvl(jdi.replacement_group_num,0)
4097 and nvl(wsor.repetitive_schedule_id ,-1)= -1
4098 and jdi.load_type = 1
4099 and jdi.parent_header_id = lv_header_id(n)
4100 and JDI.GROUP_ID =G_WIP_GROUP_ID
4101 and wsor.scheduled_flag <> 2;
4102
4103 lv_res_alt_req_count:= SQL%ROWCOUNT;
4104
4105 FOR j IN 1..lv_res_alt_req_count
4106
4107 Loop
4108 Begin
4109
4110 INSERT INTO WIP_JOB_DTLS_INTERFACE
4111 ( last_update_date,
4112 last_updated_by,
4113 last_update_login,
4114 creation_date,
4115 created_by,
4116 group_id,
4117 parent_header_id,
4118 operation_seq_num,
4119 resource_seq_num,
4120 resource_id_old,
4121 resource_id_new,
4122 replacement_group_num,
4123 substitute_group_num,
4124 start_date,
4125 completion_date,
4126 organization_id,
4127 substitution_type,
4128 load_type,
4129 process_phase,
4130 process_status,
4131 scheduled_flag)
4132 SELECT SYSDATE,
4133 FND_GLOBAL.USER_ID,
4134 FND_GLOBAL.USER_ID,
4135 SYSDATE,
4136 FND_GLOBAL.USER_ID,
4137 G_WIP_GROUP_ID,
4138 lv_res_alt_hdr_id(j),
4139 lv_res_alt_op_seq_num(j),
4140 lv_res_alt_res_seq_num(j),
4141 lv_res_alt_res_id(j),
4142 lv_res_alt_res_id(j),
4143 lv_res_alt_rep_grp_num(j),
4144 lv_res_alt_sub_grp_num(j),
4145 lv_res_alt_start_date(j),
4146 lv_res_alt_completion_date(j),
4147 lv_res_alt_org_id(j),
4148 3,
4149 1,
4150 2,
4151 1,
4152 1 --lv_scheduled_flag(j)
4153 FROM DUAL;
4154
4155 delete from WIP_JOB_DTLS_INTERFACE
4156 WHERE ROWID= lv_res_alt_rowid(j);
4157
4158 Exception
4159 when others then raise;
4160
4161 END;
4162 END LOOP;
4163
4164 Exception
4165 when NO_DATA_FOUND THEN
4166 Null;
4167 When others THEN raise;
4168 END;
4169
4170 end if;
4171
4172 --Bug 3333343
4173 UPDATE WIP_JOB_DTLS_INTERFACE set REPLACEMENT_GROUP_NUM = null
4174 where REPLACEMENT_GROUP_NUM = 0
4175 AND GROUP_ID= G_WIP_GROUP_ID
4176 AND PARENT_HEADER_ID= lv_header_id(n)
4177 AND LOAD_TYPE = LT_RESOURCE;
4178
4179
4180 <<next_c_std_job>> NULL;
4181 END LOOP;
4182
4183 /* Now that we are done processing all Jobs and details, lets go back to the alternate
4184 resource records for all the jobs we processed and generate a resource_Seq_num by getting
4185 the max of existing resource_seq_num from the routing for the assembly and adding one to it.
4186 --old logic this piece of code is now commented out*/
4187 /*
4188 for i in cres_upd
4189 loop
4190 if (nvl(v_old_op,0) <> i.operation_seq_num and nvl(v_old_header,0) <> i.parent_header_id)
4191
4192 then
4193
4194 Select max(bor1.resource_seq_num)
4195 into v_max_resource_Seq
4196 FROM bom_operation_resources bor1,
4197 BOM_OPERATION_SEQUENCES os
4198 WHERE bor1.operation_sequence_id= os.operation_sequence_id
4199 and os.routing_sequence_id= i.common_routing_Sequence_id
4200 and os.operation_seq_num = i.operation_seq_num;
4201
4202 v_old_header := i.parent_header_id;
4203 v_old_op := i.operation_seq_num;
4204 End if;
4205
4206 v_max_resource_seq := v_max_resource_Seq + 1;
4207
4208
4209 update wip_job_dtls_interface
4210 set resource_Seq_num = v_max_resource_Seq
4211 where rowid = i.rowid;
4212 End loop;
4213 */
4214
4215 EXCEPTION
4216 WHEN OTHERS THEN RAISE;
4217
4218 END MODIFY_RESOURCE_REQUIREMENT;
4219
4220 -- dsr: begin
4221 -- commenting the eam code, refer bug# 4524589
4222
4223 /* PROCEDURE LD_EAM_RESCHEDULE_JOBS
4224 ( o_request_id OUT NOCOPY NUMBER)
4225 IS
4226 lv_sqlstmt VARCHAR2(4000);
4227 lv_instance_id NUMBER;
4228 lv_dblink VARCHAR2(128);
4229
4230 lv_result BOOLEAN;
4231
4232 lv_dummy INTEGER;
4233
4234 BEGIN
4235
4236 dbms_output.put_line( 'LD_EAM_RESCHEDULE_JOBS: 000 ');
4237
4238 select DECODE( A2M_DBLINK,
4239 NULL, ' ',
4240 '@'||A2M_DBLINK),
4241 INSTANCE_ID
4242 into lv_dblink,
4243 lv_instance_id
4244 from MRP_AP_APPS_INSTANCES_ALL
4245 where instance_id = v_instance_id
4246 and instance_code = v_instance_code
4247 and nvl(a2m_dblink,NULL_DBLINK) = nvl(v_dblink,NULL_DBLINK)
4248 and ALLOW_RELEASE_FLAG=1;
4249
4250 dbms_output.put_line( 'LD_EAM_RESCHEDULE_JOBS: 111 lv_dblink/lv_instance_id '
4251 || lv_dblink
4252 || '/' || lv_instance_id
4253 );
4254
4255 lv_sqlstmt:=
4256 'INSERT INTO EAM_WORK_ORDER_IMPORT'
4257 ||' ( LAST_UPDATE_DATE,'
4258 ||' LAST_UPDATED_BY,'
4259 ||' CREATION_DATE,'
4260 ||' CREATED_BY,'
4261 ||' LAST_UPDATE_LOGIN,'
4262 ||' header_ID,'
4263 ||' GROUP_ID,'
4264 ||' wip_entity_id,'
4265 ||' ORGANIZATION_ID,'
4266 ||' asset_activity_id,'
4267 ||' REQUESTED_START_DATE,'
4268 ||' DUE_DATE,'
4269 ||' FIRM_PLANNED_FLAG,'
4270 ||' SCHEDULED_START_DATE,'
4271 ||' SCHEDULED_COMPLETION_DATE,'
4272 ||' priority,'
4273 ||' STATUS_TYPE,'
4274 ||' WIP_ENTITY_NAME,'
4275 ||' Job_quantity,'
4276 ||' TRANSACTION_TYPE,'
4277 ||' PROCESS_STATUS,'
4278 ||' project_id,'
4279 ||' task_id,'
4280 --||' bom_reference_id,'
4281 --||' routing_reference_id,'
4282 ||' alternate_bom_designator,'
4283 ||' alternate_routing_designator, '
4284 ||' end_item_unit_number,'
4285 ||' schedule_group_id,'
4286 ||' REBUILD_SERIAL_NUMBER )' -- build_sequence )'
4287 ||' SELECT'
4288 ||' SYSDATE,'
4289 ||' FND_GLOBAL.USER_ID,'
4290 ||' DECODE( ORGANIZATION_TYPE,1,SYSDATE,creation_date), '
4291 ||' FND_GLOBAL.USER_ID,'
4292 ||' LAST_UPDATE_LOGIN,'
4293 ||' WIP_ENTITY_ID,'
4294 ||' EAM_WORK_ORDER_IMPORT_S.nextval,'
4295 ||' wip_entity_id,'
4296 ||' ORGANIZATION_ID,'
4297 -- dsr ||' PRIMARY_ITEM_ID,'
4298 ||' DECODE(PRIMARY_ITEM_ID, -1000, NULL, PRIMARY_ITEM_ID),'
4299 ||' FIRST_UNIT_START_DATE,' -- REQUESTED_START_DATE,'
4300 ||' REQUESTED_COMPLETION_DATE,'
4301 ||' firm_planned_flag,'
4302 ||' first_unit_start_date,'
4303 ||' last_unit_completion_date,'
4304 ||' schedule_priority,'
4305 ||' status_type,'
4306 ||' job_name,'
4307 ||' start_quantity,'
4308 ||' 2,' -- G_OPR_UPDATE,'
4309 ||' 1,' -- process_status
4310 ||' PROJECT_ID,'
4311 ||' TASK_ID,'
4312 --||' bom_reference_id,'
4313 --||' routing_reference_id,'
4314 ||' alternate_bom_designator, '
4315 ||' alternate_routing_designator, '
4316 ||' end_item_unit_number, '
4317 ||' schedule_group_id, '
4318 ||' build_sequence '
4319 ||' FROM MSC_WIP_JOB_SCHEDULE_INTERFACE'||lv_dblink
4320 ||' WHERE SR_INSTANCE_ID= :lv_instance_id'
4321 ||' AND GROUP_ID = :G_WIP_GROUP_ID'
4322 ||' AND nvl(CFM_ROUTING_FLAG,0) <> 3 '
4323 ||' AND load_type = 21 ' -- EAM_RESCHEDULE_WORK_RODER
4324 ;
4325
4326 EXECUTE IMMEDIATE lv_sqlstmt USING lv_instance_id,G_WIP_GROUP_ID;
4327
4328 dbms_output.put_line( 'rows inserted into EAM_WORK_ORDER_IMPORT = '
4329 || SQL%ROWCOUNT);
4330
4331 -- operations
4332
4333 dbms_output.put_line('operations');
4334 lv_sqlstmt:=
4335 'INSERT INTO EAM_OPERATION_IMPORT'
4336 ||' ( LAST_UPDATE_DATE,'
4337 ||' LAST_UPDATED_BY,'
4338 ||' CREATION_DATE,'
4339 ||' CREATED_BY,'
4340 ||' LAST_UPDATE_LOGIN,'
4341 ||' header_ID,'
4342 ||' GROUP_ID,'
4343 ||' wip_entity_id,'
4344 ||' ORGANIZATION_ID,'
4345 ||' TRANSACTION_TYPE,'
4346 --||' PROCESS_STATUS,'
4347 ||' operation_seq_num,'
4348 ||' OPERATION_SEQUENCE_ID,'
4349 ||' department_id,'
4350 ||' START_DATE,'
4351 ||' COMPLETION_DATE )'
4352 ||' SELECT'
4353 ||' SYSDATE,'
4354 ||' FND_GLOBAL.USER_ID,'
4355 ||' DECODE( ORGANIZATION_TYPE,1,SYSDATE,creation_date), '
4356 ||' FND_GLOBAL.USER_ID,'
4357 ||' LAST_UPDATE_LOGIN,'
4358 ||' WIP_ENTITY_ID,'
4359 ||' EAM_WORK_ORDER_IMPORT_S.nextval,'
4360 ||' wip_entity_id,'
4361 ||' ORGANIZATION_ID,'
4362 ||' 2,' -- G_OPR_UPDATE,','
4363 --||' 1,' -- process_status
4364 ||' operation_seq_num,'
4365 ||' operation_seq_id,'
4366 ||' department_id,'
4367 ||' first_unit_start_date,'
4368 ||' last_unit_completion_date '
4369 ||' FROM MSC_WIP_JOB_DTLS_INTERFACE'||lv_dblink
4370 ||' WHERE SR_INSTANCE_ID= :lv_instance_id'
4371 ||' AND nvl(CFM_ROUTING_FLAG,0) <> 3 '
4372 ||' AND GROUP_ID = :G_WIP_GROUP_ID'
4373 ||' AND nvl(operation_seq_num,-1) <> -1'
4374 ||' AND eam_flag = 1 ' -- SYS_YES
4375 ||' AND load_type = 3 '
4376 ;
4377
4378 dbms_output.put_line('lv_dblink/lv_instance_id/G_WIP_GROUP_ID = '
4379 || lv_dblink
4380 || '/' || lv_instance_id
4381 || '/' || G_WIP_GROUP_ID
4382 );
4383
4384 EXECUTE IMMEDIATE lv_sqlstmt USING lv_instance_id,G_WIP_GROUP_ID;
4385
4386 dbms_output.put_line( 'rows inserted into EAM_OPERATION_IMPORT = '
4387 || SQL%ROWCOUNT);
4388
4389 -- operation resource
4390
4391 lv_sqlstmt:=
4392 'INSERT INTO EAM_RESOURCE_IMPORT'
4393 ||' ( header_ID,'
4394 ||' GROUP_ID,'
4395 ||' wip_entity_id,'
4396 ||' ORGANIZATION_ID,'
4397 ||' TRANSACTION_TYPE,'
4398 --||' PROCESS_STATUS,'
4399 ||' operation_seq_num,'
4400 ||' resource_seq_num,'
4401 ||' replacement_group_num,'
4402 ||' resource_id,'
4403 ||' START_DATE,'
4404 ||' completion_date, '
4405 ||' Schedule_Seq_num, '
4406 ||' scheduled_flag, '
4407 ||' basis_type, '
4408 ||' department_id, '
4409 ||' Assigned_Units, '
4410 ||' Firm_flag ) '
4411 ||' SELECT'
4412 ||' WIP_ENTITY_ID,'
4413 ||' EAM_WORK_ORDER_IMPORT_S.nextval,'
4414 ||' wip_entity_id,'
4415 ||' ORGANIZATION_ID,'
4416 ||' 2,' -- G_OPR_UPDATE,','
4417 --||' 1,' -- process_status
4418 ||' operation_seq_num,'
4419 ||' orig_resource_seq_num,'
4420 ||' alternate_num,'
4421 ||' resource_id_new,'
4422 ||' start_date,'
4423 ||' completion_date, '
4424 ||' 999, ' -- calculated for Schedule_Seq_num
4425 ||' 999, ' -- calculated for SUBSTITUTE_GROUP_NUM
4426 ||' scheduled_flag, '
4427 ||' basis_type, '
4428 ||' department_id, '
4429 ||' FIRM_FLAG '
4430 ||' FROM MSC_WIP_JOB_DTLS_INTERFACE'||lv_dblink
4431 ||' WHERE SR_INSTANCE_ID= :lv_instance_id'
4432 ||' AND nvl(CFM_ROUTING_FLAG,0) <> 3 '
4433 ||' AND GROUP_ID = :G_WIP_GROUP_ID'
4434 ||' AND nvl(operation_seq_num,-1) <> -1'
4435 ||' AND eam_flag = 1 ' -- SYS_YES
4436 ||' AND load_type = 1 '
4437 ;
4438
4439 EXECUTE IMMEDIATE lv_sqlstmt USING lv_instance_id,G_WIP_GROUP_ID;
4440
4441 dbms_output.put_line( 'rows inserted into EAM_RESOURCE_IMPORT = '
4442 || SQL%ROWCOUNT);
4443
4444 -- operation components
4445
4446 lv_sqlstmt:=
4447 'INSERT INTO EAM_MATERIAL_IMPORT'
4448 ||' ( LAST_UPDATE_DATE,'
4449 ||' LAST_UPDATED_BY,'
4450 ||' CREATION_DATE,'
4451 ||' CREATED_BY,'
4452 ||' LAST_UPDATE_LOGIN,'
4453 ||' header_ID,'
4454 ||' GROUP_ID,'
4455 ||' wip_entity_id,'
4456 ||' ORGANIZATION_ID,'
4457 ||' TRANSACTION_TYPE,'
4458 --||' PROCESS_STATUS,'
4459 ||' operation_seq_num,'
4460 ||' inventory_item_id,'
4461 ||' date_required,'
4462 ||' Required_quantity )'
4463 ||' SELECT'
4464 ||' SYSDATE,'
4465 ||' FND_GLOBAL.USER_ID,'
4466 ||' DECODE( ORGANIZATION_TYPE,1,SYSDATE,creation_date), '
4467 ||' FND_GLOBAL.USER_ID,'
4468 ||' LAST_UPDATE_LOGIN,'
4469 ||' WIP_ENTITY_ID,'
4470 ||' EAM_WORK_ORDER_IMPORT_S.nextval,'
4471 ||' wip_entity_id,'
4472 ||' ORGANIZATION_ID,'
4473 ||' 2,' -- G_OPR_UPDATE,','
4474 --||' 1,' -- process_status
4475 ||' operation_seq_num,'
4476 ||' inventory_item_id_old,'
4477 ||' date_required,'
4478 ||' Required_quantity '
4479 ||' FROM MSC_WIP_JOB_DTLS_INTERFACE'||lv_dblink
4480 ||' WHERE SR_INSTANCE_ID= :lv_instance_id'
4481 ||' AND nvl(CFM_ROUTING_FLAG,0) <> 3 '
4482 ||' AND GROUP_ID = :G_WIP_GROUP_ID'
4483 ||' AND nvl(operation_seq_num,-1) <> -1'
4484 ||' AND eam_flag = 1 ' -- SYS_YES
4485 ||' AND load_type = 2 ' -- components
4486 ;
4487
4488 EXECUTE IMMEDIATE lv_sqlstmt USING lv_instance_id,G_WIP_GROUP_ID;
4489
4490 dbms_output.put_line( 'rows inserted into EAM_MATERIAL_IMPORT = '
4491 || SQL%ROWCOUNT);
4492
4493 -- reschedule resource instance
4494
4495 lv_sqlstmt:=
4496 'INSERT INTO EAM_RESOURCE_INSTANCE_IMPORT'
4497 ||' ( LAST_UPDATE_DATE,'
4498 ||' LAST_UPDATED_BY,'
4499 ||' CREATION_DATE,'
4500 ||' CREATED_BY,'
4501 ||' LAST_UPDATE_LOGIN,'
4502 ||' header_ID,'
4503 ||' GROUP_ID,'
4504 ||' wip_entity_id,'
4505 ||' ORGANIZATION_ID,'
4506 ||' TRANSACTION_TYPE,'
4507 --||' PROCESS_STATUS,'
4508 ||' operation_seq_num,'
4509 ||' resource_seq_num,'
4510 ||' INSTANCE_ID,'
4511 ||' START_DATE,'
4512 ||' completion_date, '
4513 ||' SERIAL_NUMBER ) '
4514 ||' SELECT'
4515 ||' SYSDATE,'
4516 ||' FND_GLOBAL.USER_ID,'
4517 ||' DECODE( ORGANIZATION_TYPE,1,SYSDATE,creation_date), '
4518 ||' FND_GLOBAL.USER_ID,'
4519 ||' LAST_UPDATE_LOGIN,'
4520 ||' WIP_ENTITY_ID,'
4521 ||' EAM_WORK_ORDER_IMPORT_S.nextval,'
4522 ||' wip_entity_id,'
4523 ||' ORGANIZATION_ID,'
4524 ||' 2,' -- G_OPR_UPDATE,','
4525 --||' 1,' -- process_status
4526 ||' operation_seq_num,'
4527 ||' orig_resource_seq_num,'
4528 ||' RESOURCE_INSTANCE_ID,'
4529 ||' start_date,'
4530 ||' completion_date, '
4531 ||' SERIAL_NUMBER '
4532 ||' FROM MSC_WIP_JOB_DTLS_INTERFACE'||lv_dblink
4533 ||' WHERE SR_INSTANCE_ID= :lv_instance_id'
4534 ||' AND nvl(CFM_ROUTING_FLAG,0) <> 3 '
4535 ||' AND GROUP_ID = :G_WIP_GROUP_ID'
4536 ||' AND nvl(operation_seq_num,-1) <> -1'
4537 ||' AND eam_flag = 1 ' -- SYS_YES
4538 ||' AND load_type = 6 ' -- resource instance
4539 ;
4540
4541 EXECUTE IMMEDIATE lv_sqlstmt USING lv_instance_id,G_WIP_GROUP_ID;
4542
4543 dbms_output.put_line( 'rows inserted into EAM_RESOURCE_INSTANCE_IMPORT = '
4544 || SQL%ROWCOUNT);
4545
4546 -- reschedule resource/instance usage
4547
4548 lv_sqlstmt:=
4549 'INSERT INTO EAM_RESOURCE_USAGE_IMPORT'
4550 ||' ( LAST_UPDATE_DATE,'
4551 ||' LAST_UPDATED_BY,'
4552 ||' CREATION_DATE,'
4553 ||' CREATED_BY,'
4554 ||' LAST_UPDATE_LOGIN,'
4555 ||' header_ID,'
4556 ||' GROUP_ID,'
4557 ||' wip_entity_id,'
4558 ||' ORGANIZATION_ID,'
4559 ||' TRANSACTION_TYPE,'
4560 --||' PROCESS_STATUS,'
4561 ||' operation_seq_num,'
4562 ||' resource_seq_num,'
4563 ||' INSTANCE_ID,'
4564 ||' START_DATE,'
4565 ||' completion_date, '
4566 ||' SERIAL_NUMBER ) '
4567 ||' SELECT'
4568 ||' SYSDATE,'
4569 ||' FND_GLOBAL.USER_ID,'
4570 ||' DECODE( ORGANIZATION_TYPE,1,SYSDATE,creation_date), '
4571 ||' FND_GLOBAL.USER_ID,'
4572 ||' LAST_UPDATE_LOGIN,'
4573 ||' WIP_ENTITY_ID,'
4574 ||' EAM_WORK_ORDER_IMPORT_S.nextval,'
4575 ||' wip_entity_id,'
4576 ||' ORGANIZATION_ID,'
4577 ||' 2,' -- G_OPR_UPDATE,','
4578 --||' 1,' -- process_status
4579 ||' operation_seq_num,'
4580 ||' orig_resource_seq_num,'
4581 ||' RESOURCE_INSTANCE_ID,'
4582 ||' start_date,'
4583 ||' completion_date, '
4584 ||' SERIAL_NUMBER '
4585 ||' FROM MSC_WIP_JOB_DTLS_INTERFACE'||lv_dblink
4586 ||' WHERE SR_INSTANCE_ID= :lv_instance_id'
4587 ||' AND nvl(CFM_ROUTING_FLAG,0) <> 3 '
4588 ||' AND GROUP_ID = :G_WIP_GROUP_ID'
4589 ||' AND nvl(operation_seq_num,-1) <> -1'
4590 ||' AND eam_flag = 1 ' -- SYS_YES
4591 ||' AND load_type IN (4, 7) ' -- resource and instance usage
4592 ;
4593
4594
4595 --Commented out to support OPM integration
4596 --||' AND ORGANIZATION_TYPE = 1 ';
4597
4598 EXECUTE IMMEDIATE lv_sqlstmt USING lv_instance_id,G_WIP_GROUP_ID;
4599
4600 dbms_output.put_line( 'rows inserted into EAM_RESOURCE_USAGE_IMPORT = '
4601 || SQL%ROWCOUNT);
4602
4603 o_request_id := NULL;
4604
4605 -- Submit EAM RESCHEDULE Request --
4606 BEGIN
4607 SELECT 1
4608 INTO lv_dummy
4609 FROM WIP_JOB_SCHEDULE_INTERFACE
4610 WHERE GROUP_ID= G_WIP_GROUP_ID
4611 AND ROWNUM=1;
4612
4613 MODIFY_EAM_COMP_REQUIREMENT;
4614
4615 MODIFY_EAM_RES_REQUIREMENT;
4616
4617 --set to trigger mode to bypass the 'SAVEPOINT' and 'ROLLBACK' command.
4618 lv_result := FND_REQUEST.SET_MODE(TRUE);
4619
4620
4621 o_request_id := FND_REQUEST.SUBMIT_REQUEST(
4622 'EAM', -- application
4623 'EAMIMPWO', -- program
4624 NULL, -- description
4625 NULL, -- start_time
4626 FALSE, -- sub_request
4627 g_eam_group_id, -- group_id
4628 1, -- validation_level
4629 1); -- print report
4630
4631
4632 EXCEPTION
4633 WHEN NO_DATA_FOUND THEN NULL;
4634 WHEN OTHERS THEN RAISE;
4635 END;
4636
4637 dbms_output.put_line( 'EAM RESCHEDULE Request submitted = ' || o_request_id);
4638
4639 END LD_EAM_RESCHEDULE_JOBS;
4640 */
4641
4642 /*
4643 PROCEDURE MODIFY_EAM_COMP_REQUIREMENT
4644 IS
4645
4646 Cursor C1 is
4647 Select a.header_id,min(bos.operation_seq_num) new_op_seq
4648 from eam_work_order_import a, -- wip_job_schedule_interface a,
4649 eam_material_import b, -- wip_job_dtls_interface b,
4650 bom_operation_Sequences bos,
4651 bom_operational_routings bor
4652 where a.group_id = b.group_id
4653 and a.group_id = G_WIP_GROUP_ID
4654 -- and a.primary_item_id = bor.assembly_item_id
4655 and a.REBUILD_ITEM_ID = bor.assembly_item_id
4656 and nvl(bor.alternate_routing_Designator,0) = nvl(a.alternate_routing_designator,0)
4657 and bor.common_routing_Sequence_id = bos.routing_Sequence_id
4658 --and b.load_type = 2
4659 --and b.substitution_type = 3
4660 and a.source_code = 'MSC'
4661 and b.operation_seq_num = 1
4662 and ( bos.disable_date IS NULL
4663 OR trunc(bos.disable_date) >= trunc(nvl(a.bom_revision_date
4664 ,a.scheduled_start_date))
4665 )
4666 group by a.header_id;
4667
4668 Cursor C2 is
4669 select sum(b.QUANTITY_PER_ASSEMBLY) qty_per_assy,
4670 sum(b.REQUIRED_QUANTITY) reqd_qty,
4671 b.group_id,
4672 b.wip_entity_id, -- b.parent_header_id,
4673 b.INVENTORY_ITEM_ID, -- b.INVENTORY_ITEM_ID_OLD,
4674 b.ORGANIZATION_ID,
4675 b.OPERATION_SEQ_NUM
4676 from eam_work_order_import a, -- wip_job_schedule_interface a,
4677 eam_material_import b -- wip_job_dtls_interface b
4678 where a.source_code = 'MSC'
4679 and a.group_id = G_WIP_GROUP_ID
4680 and a.group_id = b.group_id
4681 -- and a.header_id = b.parent_header_id
4682 and a.wip_entity_id = b.wip_entity_id
4683 -- and b.load_type = 2
4684 -- and b.substitution_type = 3
4685 -- and b.process_phase = 2
4686 -- and b.process_status = 1
4687 group by b.group_id,
4688 b.wip_entity_id, -- b.parent_header_id,
4689 b.ORGANIZATION_ID,
4690 b.INVENTORY_ITEM_ID, -- b.INVENTORY_ITEM_ID_OLD,
4691 b.OPERATION_SEQ_NUM;
4692
4693 Cursor C3 is
4694 select b.rowid
4695 from eam_work_order_import a, -- wip_job_schedule_interface a,
4696 eam_material_import b -- wip_job_dtls_interface b
4697 where a.source_code = 'MSC'
4698 and a.group_id = G_WIP_GROUP_ID
4699 and a.group_id = b.group_id
4700 -- and a.header_id = b.parent_header_id
4701 and a.wip_entity_id = b.wip_entity_id
4702 -- and b.load_type = 2
4703 -- and b.substitution_type = 3
4704 -- and b.process_phase = 2
4705 -- and b.process_status = 1
4706 and b.rowid not in (select min(c.rowid)
4707 from eam_material_import c -- wip_job_dtls_interface c
4708 where b.group_id = c.group_id
4709 -- and b.parent_header_id = c.parent_header_id
4710 and b.wip_entity_id = c.wip_entity_id
4711 and b.ORGANIZATION_ID = c.ORGANIZATION_ID
4712 -- and b.INVENTORY_ITEM_ID_OLD = c.INVENTORY_ITEM_ID_OLD
4713 and b.INVENTORY_ITEM_ID = c.INVENTORY_ITEM_ID
4714 and b.OPERATION_SEQ_NUM = c.OPERATION_SEQ_NUM
4715 -- and b.load_type = c.load_type
4716 -- and b.substitution_type = c.substitution_type
4717 -- and b.process_phase = c.process_phase
4718 -- and b.process_status = c.process_status
4719 );
4720
4721
4722
4723 Begin
4724
4725
4726 For I in C1
4727
4728 loop
4729 update eam_material_import -- wip_job_dtls_interface
4730 set operation_Seq_num = I.new_op_seq
4731 -- where parent_header_id = I.header_id
4732 where wip_entity_id = I.header_id
4733 and operation_seq_num = 1
4734 -- and load_type = 2
4735 -- and substitution_type = 3
4736 ;
4737
4738 End loop;
4739
4740
4741 For J in C2
4742
4743 loop
4744 update eam_material_import -- wip_job_dtls_interface
4745 set QUANTITY_PER_ASSEMBLY = J.qty_per_assy,
4746 REQUIRED_QUANTITY = J.reqd_qty
4747 where group_id = J.group_id
4748 -- and parent_header_id = J.parent_header_id
4749 and wip_entity_id = J.wip_entity_id
4750 and ORGANIZATION_ID = J.ORGANIZATION_ID
4751 -- and INVENTORY_ITEM_ID_OLD = J.INVENTORY_ITEM_ID_OLD
4752 and INVENTORY_ITEM_ID = J.INVENTORY_ITEM_ID
4753 and OPERATION_SEQ_NUM = J.OPERATION_SEQ_NUM
4754 -- and load_type = 2
4755 -- and substitution_type = 3
4756 -- and process_phase = 2
4757 -- and process_status = 1
4758 ;
4759
4760 End loop;
4761
4762 For K in C3
4763
4764 loop
4765
4766 --jguo
4767 -- delete wip_job_dtls_interface
4768 -- where rowid = K.rowid;
4769
4770 null;
4771
4772 End loop;
4773
4774 End MODIFY_EAM_COMP_REQUIREMENT;
4775 */
4776
4777 /*
4778 PROCEDURE MODIFY_EAM_RES_REQUIREMENT
4779 IS
4780
4781
4782 cursor cres_upd is
4783 select wor.schedule_seq_num
4784 , wor.substitute_group_num
4785 FROM WIP_OPERATION_RESOURCES wor
4786 , EAM_RESOURCE_IMPORT eir
4787 WHERE wor.operation_seq_num = eir.operation_seq_num
4788 and wor.resource_seq_num = eir.resource_seq_num
4789 and nvl(wor.replacement_group_num,0) = nvl(eir.replacement_group_num,0)
4790 and eir.resource_id = wor.resource_id
4791 and eir.GROUP_ID =G_EAM_GROUP_ID
4792 and wor.wip_entity_id =eir.wip_entity_id
4793 and nvl(wor.repetitive_schedule_id ,-1)= -1
4794 ;
4795
4796 l_schedule_seq_num NUMBER;
4797 l_substitute_group_num NUMBER;
4798
4799 BEGIN
4800
4801 OPEN cres_upd;
4802 FETCH cres_upd INTO l_schedule_seq_num, l_substitute_group_num;
4803 CLOSE cres_upd;
4804
4805 Update EAM_RESOURCE_IMPORT eir
4806 SET eir.schedule_seq_num = l_schedule_seq_num
4807 , eir.substitute_group_num = l_substitute_group_num
4808 ;
4809
4810 EXCEPTION
4811 WHEN OTHERS THEN RAISE;
4812
4813 END MODIFY_EAM_RES_REQUIREMENT; */
4814
4815 --dsr: end
4816
4817 END MRP_AP_REL_PLAN_PUB;