[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.21 2012/06/12 11:38:47 swundapa 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_sqlstmt1 VARCHAR2(4000);
2123 lv_instance_id NUMBER;
2124 lv_dblink VARCHAR2(128);
2125
2126 lv_result BOOLEAN;
2127
2128 lv_dummy INTEGER;
2129
2130 BEGIN
2131
2132 select DECODE( A2M_DBLINK,
2133 NULL, ' ',
2134 '@'||A2M_DBLINK),
2135 INSTANCE_ID
2136 into lv_dblink,
2137 lv_instance_id
2138 from MRP_AP_APPS_INSTANCES_ALL
2139 where instance_id = v_instance_id
2140 and instance_code = v_instance_code
2141 and nvl(a2m_dblink,NULL_DBLINK) = nvl(v_dblink,NULL_DBLINK)
2142 and ALLOW_RELEASE_FLAG=1;
2143
2144 lv_sqlstmt:=
2145 'INSERT INTO WIP_JOB_SCHEDULE_INTERFACE'
2146 ||' ( LAST_UPDATE_DATE,'
2147 ||' LAST_UPDATED_BY,'
2148 ||' CREATION_DATE,'
2149 ||' CREATED_BY,'
2150 ||' LAST_UPDATE_LOGIN,'
2151 ||' GROUP_ID,'
2152 ||' SOURCE_CODE,'
2153 ||' SOURCE_LINE_ID,'
2154 ||' ORGANIZATION_ID,'
2155 ||' LOAD_TYPE,'
2156 ||' STATUS_TYPE,'
2157 ||' FIRST_UNIT_START_DATE,'
2158 ||' LAST_UNIT_COMPLETION_DATE,'
2159 ||' PROCESSING_WORK_DAYS,'
2160 ||' DAILY_PRODUCTION_RATE,'
2161 ||' LINE_ID,'
2162 ||' PRIMARY_ITEM_ID,'
2163 ||' BOM_REVISION_DATE,'
2164 ||' ROUTING_REVISION_DATE,'
2165 ||' CLASS_CODE,'
2166 ||' JOB_NAME,'
2167 ||' FIRM_PLANNED_FLAG,'
2168 ||' ALTERNATE_ROUTING_DESIGNATOR,'
2169 ||' ALTERNATE_BOM_DESIGNATOR,'
2170 ||' DEMAND_CLASS,'
2171 ||' START_QUANTITY,'
2172 ||' WIP_ENTITY_ID,'
2173 ||' PROCESS_PHASE,'
2174 ||' PROCESS_STATUS,'
2175 ||' SCHEDULE_GROUP_ID,'
2176 ||' BUILD_SEQUENCE,'
2177 ||' PROJECT_ID,'
2178 ||' TASK_ID,'
2179 ||' NET_QUANTITY,'
2180 ||' END_ITEM_UNIT_NUMBER,'
2181 ||' BOM_REFERENCE_ID,'
2182 ||' ROUTING_REFERENCE_ID,'
2183 ||' ALLOW_EXPLOSION,'
2184 ||' HEADER_ID,'
2185 ||' priority, ' -- dsr
2186 ||' DUE_DATE)' ---- Need to check this was already there
2187 ||' SELECT'
2188 ||' SYSDATE,'
2189 ||' FND_GLOBAL.USER_ID,'
2190 ||' DECODE( ORGANIZATION_TYPE,1,SYSDATE,creation_date), '
2191 ||' FND_GLOBAL.USER_ID,'
2192 ||' LAST_UPDATE_LOGIN,'
2193 ||' DECODE( ORGANIZATION_TYPE,'
2194 ||' 1,'||TO_CHAR(G_WIP_GROUP_ID)
2195 ||',2,'||TO_CHAR(G_OPM_WIP_GROUP_ID)||'),'
2196 ||' SOURCE_CODE,'
2197 ||' SOURCE_LINE_ID,'
2198 ||' ORGANIZATION_ID,'
2199 ||' LOAD_TYPE,'
2200 ||' STATUS_TYPE,'
2201 ||' FIRST_UNIT_START_DATE,'
2202 ||' LAST_UNIT_COMPLETION_DATE,'
2203 ||' PROCESSING_WORK_DAYS,'
2204 ||' DAILY_PRODUCTION_RATE,'
2205 ||' LINE_ID,'
2206 -- dsr ||' PRIMARY_ITEM_ID,'
2207 ||' DECODE(PRIMARY_ITEM_ID, -1001, NULL, PRIMARY_ITEM_ID), '
2208 ||' BOM_REVISION_DATE,'
2209 ||' ROUTING_REVISION_DATE,'
2210 ||' CLASS_CODE,'
2211 ||' JOB_NAME,'
2212 ||' FIRM_PLANNED_FLAG,'
2213 ||' ALTERNATE_ROUTING_DESIGNATOR,'
2214 ||' ALTERNATE_BOM_DESIGNATOR,'
2215 ||' DEMAND_CLASS,'
2216 ||' START_QUANTITY,'
2217 ||' TRUNC(WIP_ENTITY_ID/2),' /* decode wip_entity_id */
2218 ||' PROCESS_PHASE,'
2219 ||' PROCESS_STATUS,'
2220 ||' SCHEDULE_GROUP_ID,'
2221 ||' BUILD_SEQUENCE,'
2222 ||' PROJECT_ID,'
2223 ||' TASK_ID,'
2224 ||' NET_QUANTITY,'
2225 ||' END_ITEM_UNIT_NUMBER,'
2226 ||' DECODE( ORGANIZATION_TYPE,'
2227 ||' 2,TRUNC(BOM_REFERENCE_ID/2),' /* decode bill_sequence_id */
2228 ||' BOM_REFERENCE_ID),' /* for OPM only */
2229 ||' DECODE( ORGANIZATION_TYPE,'
2230 ||' 2,TRUNC(ROUTING_REFERENCE_ID/2),' /* decode routing_sequence_id */
2231 ||' ROUTING_REFERENCE_ID),' /* for OPM only */
2232 ||' BILL_RTG_EXPLOSION_FLAG,'
2233 ||' HEADER_ID,'
2234 ||' schedule_priority, ' -- dsr
2235 ||' requested_completion_date' -- need to check in ds code it is requested_completion_date
2236 ||' FROM MSC_WIP_JOB_SCHEDULE_INTERFACE'||lv_dblink
2237 ||' WHERE SR_INSTANCE_ID= :lv_instance_id'
2238 ||' AND GROUP_ID = :G_WIP_GROUP_ID'
2239 ||' AND nvl(CFM_ROUTING_FLAG,0) <> 3 '
2240 ||' AND load_type <> 21 ' -- dsr: exclude oem: EAM_RESCHEDULE_WORK_RODER
2241 ;
2242
2243 EXECUTE IMMEDIATE lv_sqlstmt USING lv_instance_id,G_WIP_GROUP_ID;
2244
2245 lv_sqlstmt:=
2246 'INSERT INTO WIP_JOB_DTLS_INTERFACE'
2247 ||' ( INTERFACE_ID,'
2248 ||' GROUP_ID,'
2249 ||' OPERATION_SEQ_NUM,'
2250 ||' RESOURCE_SEQ_NUM,'
2251 ||' REPLACEMENT_GROUP_NUM,'
2252 ||' RESOURCE_ID_OLD,'
2253 ||' RESOURCE_ID_NEW,'
2254 ||' USAGE_RATE_OR_AMOUNT,'
2255 ||' SCHEDULED_FLAG,'
2256 ||' ASSIGNED_UNITS,'
2257 ||' APPLIED_RESOURCE_UNITS,'
2258 ||' APPLIED_RESOURCE_VALUE,'
2259 ||' UOM_CODE,'
2260 ||' BASIS_TYPE,'
2261 ||' ACTIVITY_ID,'
2262 ||' AUTOCHARGE_TYPE,'
2263 ||' STANDARD_RATE_FLAG,'
2264 ||' START_DATE,'
2265 ||' COMPLETION_DATE,'
2266 ||' INVENTORY_ITEM_ID_OLD,'
2267 ||' INVENTORY_ITEM_ID_NEW,'
2268 ||' QUANTITY_PER_ASSEMBLY,'
2269 ||' COMPONENT_YIELD_FACTOR,'
2270 ||' DEPARTMENT_ID,'
2271 ||' WIP_SUPPLY_TYPE,'
2272 ||' DATE_REQUIRED,'
2273 ||' REQUIRED_QUANTITY,'
2274 ||' QUANTITY_ISSUED,'
2275 ||' SUPPLY_SUBINVENTORY,'
2276 ||' SUPPLY_LOCATOR_ID,'
2277 ||' MRP_NET_FLAG,'
2278 ||' MPS_REQUIRED_QUANTITY,'
2279 ||' MPS_DATE_REQUIRED,'
2280 ||' LOAD_TYPE,'
2281 ||' SUBSTITUTION_TYPE,'
2282 ||' PROCESS_PHASE,'
2283 ||' PROCESS_STATUS,'
2284 ||' REQUEST_ID,'
2285 ||' PROGRAM_APPLICATION_ID,'
2286 ||' PROGRAM_ID,'
2287 ||' PROGRAM_UPDATE_DATE,'
2288 ||' PARENT_HEADER_ID,'
2289 ||' DESCRIPTION,'
2290 ||' STANDARD_OPERATION_ID,'
2291 ||' FIRST_UNIT_START_DATE,'
2292 ||' FIRST_UNIT_COMPLETION_DATE,'
2293 ||' LAST_UNIT_START_DATE,'
2294 ||' LAST_UNIT_COMPLETION_DATE,'
2295 ||' COUNT_POINT_TYPE,'
2296 ||' BACKFLUSH_FLAG,'
2297 ||' MINIMUM_TRANSFER_QUANTITY,'
2298 ||' WIP_ENTITY_ID,'
2299 ||' ORGANIZATION_ID,'
2300 ||' ATTRIBUTE1,'
2301 ||' LAST_UPDATE_DATE,'
2302 ||' LAST_UPDATED_BY,'
2303 ||' CREATION_DATE,'
2304 ||' CREATED_BY,'
2305 ||' LAST_UPDATE_LOGIN '
2306 -- dsr: added following 10 new columns
2307 ||' , Serial_number_new ' -- rawasthi changed the column from serial_number to Serial_number_new
2308 ||' , resource_serial_number ' -- jguo
2309 ||' , setup_id '
2310 ||' , group_sequence_id '
2311 ||' , group_sequence_number '
2312 ||' , batch_id '
2313 ||' , resource_instance_id '
2314 ||' , charge_number '
2315 ||' , maximum_assigned_units '
2316 ||' , parent_seq_num '
2317 ||' , firm_flag '
2318 -- jguo opm ||' , orig_resource_seq_num ) '
2319 ||' , schedule_seq_num ) '
2320 ||' SELECT'
2321 ||' INTERFACE_ID,'
2322 ||' DECODE( ORGANIZATION_TYPE,'
2323 ||' 1,'||TO_CHAR(G_WIP_GROUP_ID)
2324 ||',2,'||TO_CHAR(G_OPM_WIP_GROUP_ID)||'),'
2325 ||' OPERATION_SEQ_NUM,'
2326 ||' RESOURCE_SEQ_NUM,'
2327 ||' ALTERNATE_NUM,'
2328 ||' TRUNC(RESOURCE_ID_OLD/2),' /* decode resource_id */
2329 ||' TRUNC(RESOURCE_ID_NEW/2),' /* decode resource_id */
2330 ||' USAGE_RATE_OR_AMOUNT,'
2331 ||' SCHEDULED_FLAG,'
2332 ||' ASSIGNED_UNITS,'
2333 ||' APPLIED_RESOURCE_UNITS,'
2334 ||' APPLIED_RESOURCE_VALUE,'
2335 ||' UOM_CODE,'
2336 ||' BASIS_TYPE,'
2337 ||' ACTIVITY_ID,'
2338 ||' AUTOCHARGE_TYPE,'
2339 ||' STANDARD_RATE_FLAG,'
2340 ||' START_DATE,'
2341 ||' COMPLETION_DATE,'
2342 ||' INVENTORY_ITEM_ID_OLD,'
2343 ||' INVENTORY_ITEM_ID_NEW,'
2344 ||' QUANTITY_PER_ASSEMBLY,'
2345 ||' COMPONENT_YIELD_FACTOR,'
2346 ||' TRUNC(DEPARTMENT_ID/2),' /* decode department_id */
2347 ||' WIP_SUPPLY_TYPE,'
2348 ||' DATE_REQUIRED,'
2349 ||' REQUIRED_QUANTITY,'
2350 ||' QUANTITY_ISSUED,'
2351 ||' SUPPLY_SUBINVENTORY,'
2352 ||' SUPPLY_LOCATOR_ID,'
2353 ||' MRP_NET_FLAG,'
2354 ||' MPS_REQUIRED_QUANTITY,'
2355 ||' MPS_DATE_REQUIRED,'
2356 ||' LOAD_TYPE,'
2357 ||' SUBSTITUTION_TYPE,'
2358 ||' PROCESS_PHASE,'
2359 ||' PROCESS_STATUS,'
2360 ||' REQUEST_ID,'
2361 ||' PROGRAM_APPLICATION_ID,'
2362 ||' PROGRAM_ID,'
2363 ||' PROGRAM_UPDATE_DATE,'
2364 ||' PARENT_HEADER_ID,'
2365 ||' DESCRIPTION,'
2366 ||' STANDARD_OPERATION_ID,'
2367 ||' FIRST_UNIT_START_DATE,'
2368 ||' FIRST_UNIT_COMPLETION_DATE,'
2369 ||' LAST_UNIT_START_DATE,'
2370 ||' LAST_UNIT_COMPLETION_DATE,'
2371 ||' COUNT_POINT_TYPE,'
2372 ||' BACKFLUSH_FLAG,'
2373 ||' MINIMUM_TRANSFER_QUANTITY,'
2374 ||' TRUNC(WIP_ENTITY_ID/2),' /* decode wip_entity_id */
2375 ||' ORGANIZATION_ID,'
2376 ||' decode(organization_type,2,fnd_number.number_to_canonical(resource_hours),resource_hours),'
2377 ||' SYSDATE,'
2378 ||' FND_GLOBAL.USER_ID,'
2379 ||' SYSDATE,'
2380 ||' FND_GLOBAL.USER_ID,'
2381 ||' LAST_UPDATE_LOGIN '
2382 -- dsr: added following 10 new columns
2383 ||' , serial_number '
2384 ||' , serial_number ' -- jguo
2385 ||' , setup_id '
2386 ||' , group_sequence_id '
2387 ||' , group_sequence_number '
2388 ||' , batch_id '
2389 ||' ,TRUNC(resource_instance_id/2) '-- changed by abhikuma
2390 ||' , charge_number '
2391 ||' , maximum_assigned_units '
2392 ||' , parent_seq_num '
2393 ||' , firm_flag '
2394 ||' , schedule_seq_num '
2395 ||' FROM MSC_WIP_JOB_DTLS_INTERFACE'||lv_dblink
2396 ||' WHERE SR_INSTANCE_ID= :lv_instance_id'
2397 ||' AND nvl(CFM_ROUTING_FLAG,0) <> 3 '
2398 ||' AND GROUP_ID = :G_WIP_GROUP_ID'
2399 ||' AND nvl(operation_seq_num,-1) <> -1'
2400 ||' AND nvl(eam_flag, -1) <> 1 '
2401 ;
2402
2403 --Commented out to support OPM integration
2404 --||' AND ORGANIZATION_TYPE = 1 ';
2405
2406 EXECUTE IMMEDIATE lv_sqlstmt USING lv_instance_id,G_WIP_GROUP_ID;
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 -- Submit 'EAM req for CMRO ' Request --
2469 BEGIN
2470
2471 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '1 dblink is '|| lv_dblink);
2472 lv_sqlstmt := 'SELECT 1 '
2473 ||' FROM MSC_WIP_JOB_SCHEDULE_INTERFACE'||lv_dblink
2474 ||' WHERE SR_INSTANCE_ID= :lv_instance_id'
2475 ||' AND GROUP_ID = (:G_WIP_GROUP_ID *-1)'
2476 ||' AND LOAD_TYPE in (3,258)'
2477 ||' AND ROWNUM = 1 ';
2478
2479 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, ' lv_sqlstmt is '|| lv_sqlstmt);
2480
2481 EXECUTE IMMEDIATE lv_sqlstmt into lv_dummy USING lv_instance_id,G_WIP_GROUP_ID;
2482
2483
2484 -- set to trigger mode to bypass the 'SAVEPOINT' and 'ROLLBACK' command.
2485 lv_result := FND_REQUEST.SET_MODE(TRUE);
2486 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'dblink is '|| lv_dblink);
2487 o_request_id := FND_REQUEST.SUBMIT_REQUEST(
2488 'MSC', -- application
2489 'MRPPSRELB', -- program
2490 NULL, -- description
2491 NULL, -- start_time
2492 FALSE, -- sub_request
2493 lv_dblink, --dblink
2494 G_WIP_GROUP_ID * -1, --group_id
2495 lv_instance_id );
2496
2497
2498 EXCEPTION
2499 WHEN NO_DATA_FOUND THEN NULL;
2500 WHEN OTHERS THEN RAISE;
2501 END;
2502
2503 END LD_WIP_JOB_SCHEDULE_INTERFACE;
2504
2505 PROCEDURE LD_EAM_CMRO_SCHEDULE_INTERFACE
2506 ( o_request_id_eam OUT NOCOPY NUMBER,
2507 o_request_id_cmro OUT NOCOPY NUMBER,
2508 plan_completion_date IN DATE
2509 )
2510 IS
2511 lv_sqlstmt VARCHAR2(4000);
2512 lv_sqlstmt1 VARCHAR2(4000);
2513 lv_instance_id NUMBER;
2514 lv_dblink VARCHAR2(128);
2515
2516 lv_result BOOLEAN;
2517
2518 lv_dummy INTEGER;
2519
2520 BEGIN
2521
2522 select DECODE( A2M_DBLINK,
2523 NULL, ' ',
2524 '@'||A2M_DBLINK),
2525 INSTANCE_ID
2526 into lv_dblink,
2527 lv_instance_id
2528 from MRP_AP_APPS_INSTANCES_ALL
2529 where instance_id = v_instance_id
2530 and instance_code = v_instance_code
2531 and nvl(a2m_dblink,NULL_DBLINK) = nvl(v_dblink,NULL_DBLINK)
2532 and ALLOW_RELEASE_FLAG=1;
2533
2534
2535 o_request_id_eam := NULL;
2536 o_request_id_cmro := NULL;
2537
2538 BEGIN
2539
2540 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '1 dblink is '|| lv_dblink);
2541 lv_sqlstmt := 'SELECT 1 '
2542 ||' FROM MSC_WIP_JOB_SCHEDULE_INTERFACE'||lv_dblink
2543 ||' WHERE SR_INSTANCE_ID= :lv_instance_id'
2544 ||' AND GROUP_ID = (:G_WIP_GROUP_ID *-1)'
2545 ||' AND MAINTENANCE_OBJECT_SOURCE =1'
2546 ||' AND ROWNUM = 1 ';
2547
2548 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, ' lv_sqlstmt is '|| lv_sqlstmt);
2549
2550 EXECUTE IMMEDIATE lv_sqlstmt into lv_dummy USING lv_instance_id,G_WIP_GROUP_ID;
2551 -- set to trigger mode to bypass the 'SAVEPOINT' and 'ROLLBACK' command.
2552 lv_result := FND_REQUEST.SET_MODE(TRUE);
2553
2554 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'dblink is '|| lv_dblink);
2555
2556 o_request_id_eam := FND_REQUEST.SUBMIT_REQUEST(
2557 'MSC', -- application
2558 'MRPEAMRELB', -- program
2559 NULL, -- description
2560 NULL, -- start_time
2561 FALSE, -- sub_request
2562 lv_dblink, --dblink
2563 G_WIP_GROUP_ID * -1, --group_id
2564 lv_instance_id );
2565
2566 EXCEPTION
2567 WHEN NO_DATA_FOUND THEN NULL;
2568 WHEN OTHERS THEN RAISE;
2569 END;
2570 --handle CMRO jobs now
2571
2572 BEGIN
2573
2574 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '1 dblink is '|| lv_dblink);
2575 lv_sqlstmt := 'SELECT 1 '
2576 ||' FROM MSC_WIP_JOB_SCHEDULE_INTERFACE'||lv_dblink
2577 ||' WHERE SR_INSTANCE_ID= :lv_instance_id'
2578 ||' AND GROUP_ID = (:G_WIP_GROUP_ID *-1)'
2579 ||' AND MAINTENANCE_OBJECT_SOURCE =2'
2580 ---maintenance_object_source will be 2 for CMRO jobs
2581 ||' AND ROWNUM = 1 ';
2582
2583 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, ' lv_sqlstmt is '|| lv_sqlstmt);
2584
2585 EXECUTE IMMEDIATE lv_sqlstmt into lv_dummy USING
2586 lv_instance_id,G_WIP_GROUP_ID;
2587 -- set to trigger mode to bypass the 'SAVEPOINT' and 'ROLLBACK' command.
2588 lv_result := FND_REQUEST.SET_MODE(TRUE);
2589
2590 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'dblink is '|| lv_dblink);
2591
2592 o_request_id_cmro := FND_REQUEST.SUBMIT_REQUEST(
2593 'MSC', -- application
2594 'MRPCMRELB', -- new exe name
2595 --The exe needs to be associated with procedure RELEASE_CMRO_WO in file MRPPSRELB.pls
2596 NULL, -- description
2597 NULL, -- start_time
2598 FALSE, -- sub_request
2599 lv_dblink, --dblink
2600 G_WIP_GROUP_ID * -1, --group_id
2601 lv_instance_id,
2602 plan_completion_date );
2603
2604 EXCEPTION
2605 WHEN NO_DATA_FOUND THEN NULL;
2606 WHEN OTHERS THEN RAISE;
2607 END;
2608
2609 /* EXCEPTION
2610 WHEN NO_DATA_FOUND THEN NULL;
2611 WHEN OTHERS THEN RAISE;
2612 */
2613
2614 END LD_EAM_CMRO_SCHEDULE_INTERFACE;
2615
2616 PROCEDURE LD_EAM_RESCHEDULE_INTERFACE
2617 ( o_request_id OUT NOCOPY NUMBER)
2618 IS
2619 lv_sqlstmt VARCHAR2(4000);
2620 lv_sqlstmt1 VARCHAR2(4000);
2621 lv_instance_id NUMBER;
2622 lv_dblink VARCHAR2(128);
2623
2624 lv_result BOOLEAN;
2625
2626 lv_dummy INTEGER;
2627
2628 BEGIN
2629
2630 select DECODE( A2M_DBLINK,
2631 NULL, ' ',
2632 '@'||A2M_DBLINK),
2633 INSTANCE_ID
2634 into lv_dblink,
2635 lv_instance_id
2636 from MRP_AP_APPS_INSTANCES_ALL
2637 where instance_id = v_instance_id
2638 and instance_code = v_instance_code
2639 and nvl(a2m_dblink,NULL_DBLINK) = nvl(v_dblink,NULL_DBLINK)
2640 and ALLOW_RELEASE_FLAG=1;
2641
2642
2643 o_request_id := NULL;
2644
2645 BEGIN
2646
2647 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '1 dblink is '|| lv_dblink);
2648 lv_sqlstmt := 'SELECT 1 '
2649 ||' FROM MSC_WIP_JOB_SCHEDULE_INTERFACE'||lv_dblink
2650 ||' WHERE SR_INSTANCE_ID= :lv_instance_id'
2651 ||' AND GROUP_ID = (:G_WIP_GROUP_ID *-1)'
2652 ||' AND MAINTENANCE_OBJECT_SOURCE =1'
2653 ||' AND ROWNUM = 1 ';
2654
2655 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, ' lv_sqlstmt is '|| lv_sqlstmt);
2656
2657 EXECUTE IMMEDIATE lv_sqlstmt into lv_dummy USING lv_instance_id,G_WIP_GROUP_ID;
2658 -- set to trigger mode to bypass the 'SAVEPOINT' and 'ROLLBACK' command.
2659 lv_result := FND_REQUEST.SET_MODE(TRUE);
2660
2661 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'dblink is '|| lv_dblink);
2662
2663 o_request_id := FND_REQUEST.SUBMIT_REQUEST(
2664 'MSC', -- application
2665 'MRPPSRELB', -- program
2666 NULL, -- description
2667 NULL, -- start_time
2668 FALSE, -- sub_request
2669 lv_dblink, --dblink
2670 G_WIP_GROUP_ID * -1, --group_id
2671 lv_instance_id );
2672
2673 EXCEPTION
2674 WHEN NO_DATA_FOUND THEN NULL;
2675 WHEN OTHERS THEN RAISE;
2676 END;
2677
2678
2679 EXCEPTION
2680 WHEN NO_DATA_FOUND THEN NULL;
2681 WHEN OTHERS THEN RAISE;
2682
2683
2684 END LD_EAM_RESCHEDULE_INTERFACE;
2685
2686 PROCEDURE LD_CMRO_RESCHEDULE_INTERFACE
2687 ( o_request_id OUT NOCOPY NUMBER)
2688 IS
2689 lv_sqlstmt VARCHAR2(4000);
2690 lv_sqlstmt1 VARCHAR2(4000);
2691 lv_instance_id NUMBER;
2692 lv_dblink VARCHAR2(128);
2693
2694 lv_result BOOLEAN;
2695
2696 lv_dummy INTEGER;
2697
2698 BEGIN
2699
2700 select DECODE( A2M_DBLINK,
2701 NULL, ' ',
2702 '@'||A2M_DBLINK),
2703 INSTANCE_ID
2704 into lv_dblink,
2705 lv_instance_id
2706 from MRP_AP_APPS_INSTANCES_ALL
2707 where instance_id = v_instance_id
2708 and instance_code = v_instance_code
2709 and nvl(a2m_dblink,NULL_DBLINK) = nvl(v_dblink,NULL_DBLINK)
2710 and ALLOW_RELEASE_FLAG=1;
2711
2712
2713 o_request_id := NULL;
2714
2715 BEGIN
2716
2717 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '1 dblink is '|| lv_dblink);
2718 lv_sqlstmt := 'SELECT 1 '
2719 ||' FROM MSC_WIP_JOB_SCHEDULE_INTERFACE'||lv_dblink
2720 ||' WHERE SR_INSTANCE_ID= :lv_instance_id'
2721 ||' AND GROUP_ID = (:G_WIP_GROUP_ID *-1)'
2722 ||' AND LOAD_TYPE=258'
2723 ||' AND MAINTENANCE_OBJECT_SOURCE =2'
2724 ||' AND ROWNUM = 1 ';
2725
2726 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, ' lv_sqlstmt is '|| lv_sqlstmt);
2727
2728 EXECUTE IMMEDIATE lv_sqlstmt into lv_dummy USING lv_instance_id,G_WIP_GROUP_ID;
2729 -- set to trigger mode to bypass the 'SAVEPOINT' and 'ROLLBACK' command.
2730 lv_result := FND_REQUEST.SET_MODE(TRUE);
2731
2732 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'dblink is '|| lv_dblink);
2733
2734 o_request_id := FND_REQUEST.SUBMIT_REQUEST(
2735 'MSC', -- application
2736 'MRPPSRELB', -- program
2737 NULL, -- description
2738 NULL, -- start_time
2739 FALSE, -- sub_request
2740 lv_dblink, --dblink
2741 G_WIP_GROUP_ID * -1, --group_id
2742 lv_instance_id );
2743
2744 EXCEPTION
2745 WHEN NO_DATA_FOUND THEN NULL;
2746 WHEN OTHERS THEN RAISE;
2747 END;
2748
2749
2750 EXCEPTION
2751 WHEN NO_DATA_FOUND THEN NULL;
2752 WHEN OTHERS THEN RAISE;
2753
2754
2755 END LD_CMRO_RESCHEDULE_INTERFACE;
2756
2757 PROCEDURE LD_PO_REQUISITIONS_INTERFACE
2758 ( p_po_group_by_name IN VARCHAR2,
2759 o_request_id OUT NOCOPY NUMBER)
2760 IS
2761
2762 -- added for 2541517
2763 TYPE CharTab IS TABLE OF VARCHAR2(3) INDEX BY BINARY_INTEGER;
2764 TYPE RIDTab IS TABLE OF ROWID INDEX BY BINARY_INTEGER;
2765 TYPE NumTab IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
2766
2767
2768 lv_req_count NUMBER;
2769 lv_pri_rowid RIDTab;
2770 lv_sec_uom_code CharTab;
2771 lv_sec_uom_qty NumTab;
2772
2773 lv_sqlstmt VARCHAR2(4000);
2774 lv_instance_id NUMBER;
2775 lv_dblink VARCHAR2(128);
2776
2777 lv_result BOOLEAN;
2778
2779 lv_accrual_acct_id NUMBER;
2780 lv_charge_acct_id NUMBER;
2781
2782 var_revision VARCHAR2(3);
2783 var_revision_ctrl NUMBER;
2784
2785 Cursor c1 is select pvsa.vendor_site_id,pri.rowid,pri.item_id, pri.destination_organization_id,pri.charge_account_id,pri.project_id
2786 from po_vendor_sites_all pvsa, po_Requisitions_interface_All pri
2787 where pri.suggested_vendor_id = pvsa.vendor_id(+)
2788 and pri.suggested_vendor_site = pvsa.vendor_site_code(+)
2789 and nvl(pri.org_id,-99) = nvl(pvsa.org_id(+),-99)
2790 and pri.interface_source_code = 'MSC'
2791 and pri.batch_id = G_PO_BATCH_NUMBER;
2792
2793 CURSOR c1_rec is
2794 SELECT item_id,
2795 destination_organization_id,
2796 rowid
2797 from PO_REQUISITIONS_INTERFACE_ALL
2798 where batch_id = G_PO_BATCH_NUMBER;
2799
2800
2801 BEGIN
2802
2803 select DECODE( A2M_DBLINK,
2804 NULL, ' ',
2805 '@'||A2M_DBLINK),
2806 INSTANCE_ID
2807 into lv_dblink,
2808 lv_instance_id
2809 from MRP_AP_APPS_INSTANCES_ALL
2810 where instance_id = v_instance_id
2811 and instance_code = v_instance_code
2812 and nvl(a2m_dblink,NULL_DBLINK) = nvl(v_dblink,NULL_DBLINK)
2813 and ALLOW_RELEASE_FLAG=1;
2814
2815 lv_sqlstmt:=
2816 'INSERT INTO PO_REQUISITIONS_INTERFACE_ALL'
2817 ||' ( PROJECT_ACCOUNTING_CONTEXT,'
2818 ||' PROJECT_ID,'
2819 ||' TASK_ID,'
2820 ||' NEED_BY_DATE,'
2821 ||' ITEM_ID,'
2822 ||' ITEM_REVISION,'
2823 ||' CHARGE_ACCOUNT_ID,'
2824 ||' AUTHORIZATION_STATUS,'
2825 ||' BATCH_ID,'
2826 ||' GROUP_CODE,'
2827 ||' PREPARER_ID,'
2828 ||' AUTOSOURCE_FLAG,'
2829 ||' SOURCE_ORGANIZATION_ID,'
2830 ||' DESTINATION_ORGANIZATION_ID,'
2831 ||' DELIVER_TO_LOCATION_ID,'
2832 ||' DELIVER_TO_REQUESTOR_ID,'
2833 ||' SUGGESTED_VENDOR_ID,'
2834 ||' SUGGESTED_VENDOR_SITE,'
2835 ||' LAST_UPDATED_BY,'
2836 ||' LAST_UPDATE_DATE,'
2837 ||' LAST_UPDATE_LOGIN,'
2838 ||' CREATION_DATE,'
2839 ||' CREATED_BY,'
2840 ||' INTERFACE_SOURCE_CODE,'
2841 ||' SOURCE_TYPE_CODE,'
2842 ||' DESTINATION_TYPE_CODE,'
2843 ||' QUANTITY,'
2844 ||' UOM_CODE,'
2845 ||' LINE_TYPE_ID,'
2846 ||' ORG_ID,'
2847 ||' VMI_FLAG,'
2848 ||' END_ITEM_UNIT_NUMBER )'
2849 ||' SELECT'
2850 ||' PROJECT_ACCOUNTING_CONTEXT,'
2851 ||' PROJECT_ID,'
2852 ||' TASK_ID,'
2853 ||' NEED_BY_DATE,'
2854 ||' ITEM_ID,'
2855 ||' ITEM_REVISION,'
2856 ||' CHARGE_ACCOUNT_ID,'
2857 ||' AUTHORIZATION_STATUS,'
2858 || TO_CHAR(G_PO_BATCH_NUMBER)||','
2859 ||' GROUP_CODE,'
2860 ||' PREPARER_ID,'
2861 ||' AUTOSOURCE_FLAG,'
2862 ||' SOURCE_ORGANIZATION_ID,'
2863 ||' DESTINATION_ORGANIZATION_ID,'
2864 ||' DELIVER_TO_LOCATION_ID,'
2865 ||' DELIVER_TO_REQUESTOR_ID,'
2866 ||' SUGGESTED_VENDOR_ID,'
2867 ||' SUGGESTED_VENDOR_SITE,'
2868 ||' FND_GLOBAL.USER_ID,'
2869 ||' SYSDATE,'
2870 ||' LAST_UPDATE_LOGIN,'
2871 ||' SYSDATE,'
2872 ||' FND_GLOBAL.USER_ID,'
2873 ||' INTERFACE_SOURCE_CODE,'
2874 ||' SOURCE_TYPE_CODE,'
2875 ||' DESTINATION_TYPE_CODE,'
2876 ||' QUANTITY,'
2877 ||' UOM_CODE,'
2878 ||' LINE_TYPE_ID,'
2879 ||' ORG_ID,'
2880 ||' DECODE(VMI_FLAG,1,''Y'',''N''), '
2881 ||' END_ITEM_UNIT_NUMBER'
2882 ||' FROM MSC_PO_REQUISITIONS_INTERFACE'||lv_dblink
2883 ||' WHERE SR_INSTANCE_ID= :lv_instance_id'
2884 ||' AND BATCH_ID = :G_PO_BATCH_NUMBER';
2885
2886 EXECUTE IMMEDIATE lv_sqlstmt
2887 USING lv_instance_id,G_PO_BATCH_NUMBER;
2888
2889
2890
2891 For i in c1
2892 Loop
2893 BEGIN
2894 /* --Added for the bug#3319306
2895 IF i.vendor_site_id IS NOT NULL THEN
2896 update po_requisitions_interface_all
2897 set suggested_vendor_site_id = i.vendor_site_id
2898 where rowid = i.rowid;
2899 END IF;
2900 Removed --- bug 8256097*/
2901
2902 If nvl(i.charge_account_id,-1) = -1 Then
2903
2904 GMP_UTILITY.generate_opm_acct('INVENTORY',
2905 'ASSET',
2906 'ASSET',
2907 i.destination_organization_id,
2908 i.item_id,
2909 i.vendor_site_id,
2910 lv_charge_acct_id
2911 );
2912 If NVL(lv_charge_acct_id,0) > 0 Then
2913 Update po_requisitions_interface_all
2914 set charge_account_id = lv_charge_acct_id
2915 where rowid = i.rowid;
2916 Else
2917 Update po_requisitions_interface_all
2918 set charge_account_id =
2919 (Select nvl(mapv.material_account,
2920 decode( msi.inventory_asset_flag,
2921 'Y', mp.material_account,
2922 nvl(msi.expense_account, mp.expense_account)))
2923 from mtl_system_items msi,
2924 mtl_parameters mp,
2925 /* Bug 3341083 Note: Any changes to from clause or where clause of MRP_AP_PROJECTS_V, needs a corresponding change to this inline view*/
2926 (SELECT ppp.Project_ID,
2927 ppp.Organization_ID,
2928 ccga.Material_Account
2929 FROM CST_COST_GROUP_ACCOUNTS ccga,
2930 PA_PROJECT_PLAYERS ppl,
2931 PA_PROJECTS_ALL ppa,
2932 PJM_PROJECT_PARAMETERS ppp
2933 WHERE ppa.Project_ID= ppp.Project_ID
2934 AND ccga.Cost_Group_ID(+)= ppp.Costing_Group_ID
2935 AND ccga.Organization_ID(+)= ppp.Organization_ID
2936 AND ppl.project_role_type(+)= 'PROJECT MANAGER'
2937 AND ppl.project_id(+)= ppa.project_id
2938 UNION ALL
2939 SELECT ppp.Project_ID,
2940 ppp.Organization_ID,
2941 ccga.Material_Account
2942 FROM CST_COST_GROUP_ACCOUNTS ccga,
2943 PJM_SEIBAN_NUMBERS psn,
2944 PJM_PROJECT_PARAMETERS ppp
2945 WHERE psn.Project_ID= ppp.Project_ID
2946 AND ccga.Cost_Group_ID(+)= ppp.Costing_Group_ID
2947 AND ccga.Organization_ID(+)= ppp.Organization_ID) mapv
2948 where msi.inventory_item_id = i.item_id
2949 and msi.organization_id = i.destination_organization_id
2950 and mp.organization_id = msi.organization_id
2951 and mapv.organization_id(+) = msi.organization_id
2952 and mapv.project_id(+) = nvl(i.project_id,-23453))
2953 where rowid = i.rowid; --9192631
2954 End if;
2955
2956 GMP_UTILITY.generate_opm_acct('ACCRUAL',
2957 'ASSET',
2958 'ASSET',
2959 i.destination_organization_id,
2960 i.item_id,
2961 i.vendor_site_id,
2962 lv_accrual_acct_id
2963 );
2964 If NVL(lv_accrual_acct_id,0) > 0 Then
2965 Update po_requisitions_interface_all
2966 set accrual_account_id = lv_accrual_acct_id
2967 where rowid = i.rowid;
2968 Else
2969 Null;
2970 End if;
2971
2972 End if; /* Charge acct id = -1 */
2973
2974 EXCEPTION
2975 WHEN OTHERS THEN RAISE;
2976 END;
2977
2978 End loop;
2979
2980 -- fix for 2541517
2981 -- Populating SECONDARY_UOM_CODE and SECONDARY_QUANTITY in PO_REQUISITIONS_INTERFACE_ALL from MTL_SYSTEM_ITEMS
2982 BEGIN
2983 SELECT pri.rowid,
2984 msi.SECONDARY_UOM_CODE,
2985 inv_convert.inv_um_convert(pri.ITEM_ID,9,pri.QUANTITY,pri.UOM_CODE,msi.SECONDARY_UOM_CODE,null,null)
2986 BULK COLLECT
2987 INTO lv_pri_rowid,
2988 lv_sec_uom_code,
2989 lv_sec_uom_qty
2990 FROM PO_REQUISITIONS_INTERFACE_ALL pri,
2991 MTL_SYSTEM_ITEMS msi
2992 WHERE pri.ITEM_ID = msi.INVENTORY_ITEM_ID
2993 AND pri.DESTINATION_ORGANIZATION_ID = msi.ORGANIZATION_ID
2994 AND msi.SECONDARY_UOM_CODE is not NULL
2995 AND pri.batch_id = G_PO_BATCH_NUMBER;
2996
2997 lv_req_count:= SQL%ROWCOUNT;
2998
2999 EXCEPTION
3000 WHEN OTHERS THEN RAISE;
3001 END;
3002
3003 IF lv_req_count <> 0 THEN
3004
3005 FOR j IN 1..lv_req_count LOOP
3006
3007 UPDATE PO_REQUISITIONS_INTERFACE_ALL pri
3008 SET pri.SECONDARY_UOM_CODE = lv_sec_uom_code(j),
3009 pri.SECONDARY_QUANTITY = lv_sec_uom_qty(j)
3010 WHERE ROWID= lv_pri_rowid(j);
3011
3012 END LOOP;
3013 END IF;
3014
3015 FOR ctemp in c1_rec LOOP
3016
3017 BEGIN
3018 SELECT max(rev.revision),
3019 max(msi.revision_qty_control_code)
3020 INTO var_revision,var_revision_ctrl
3021 FROM mtl_system_items_b msi,
3022 mtl_item_revisions rev
3023 WHERE msi.inventory_item_id = ctemp.item_id
3024 AND msi.organization_id = ctemp.destination_organization_id
3025 AND rev.inventory_item_id = msi.inventory_item_id
3026 AND rev.organization_id = msi.organization_id
3027 AND TRUNC(rev.effectivity_date) =
3028 (SELECT TRUNC(max(rev2.effectivity_date))
3029 FROM mtl_item_revisions rev2
3030 WHERE rev2.implementation_date IS NOT NULL
3031 AND rev2.effectivity_date <= TRUNC(SYSDATE)+.99999
3032 AND rev2.organization_id = rev.organization_id
3033 AND rev2.inventory_item_id = rev.inventory_item_id);
3034
3035 EXCEPTION
3036 WHEN NO_DATA_FOUND THEN
3037 var_revision_ctrl := NOT_UNDER_REV_CONTROL;
3038 WHEN OTHERS THEN
3039 RAISE;
3040 END;
3041
3042 BEGIN
3043 UPDATE PO_REQUISITIONS_INTERFACE_ALL
3044 set item_revision = DECODE(var_purchasing_by_rev, NULL,
3045 DECODE(var_revision_ctrl, NOT_UNDER_REV_CONTROL, NULL, var_revision),
3046 PURCHASING_BY_REV, var_revision,
3047 NOT_PURCHASING_BY_REV, NULL)
3048 WHERE ROWID = ctemp.rowid;
3049
3050 EXCEPTION
3051 WHEN OTHERS THEN
3052 RAISE;
3053 END;
3054
3055 END LOOP;
3056
3057 -- Launching the REQIMPORT in loop for each OU, change for MOAC
3058 DECLARE
3059 CURSOR c1 IS
3060 SELECT DISTINCT org_id
3061 FROM PO_REQUISITIONS_INTERFACE_ALL
3062 WHERE batch_id = G_PO_BATCH_NUMBER;
3063
3064 BEGIN
3065 FOR C2 IN C1
3066 LOOP
3067
3068 /*MO_GLOBAL.INIT ('PO'); Bug 8397994 */
3069 FND_REQUEST.SET_ORG_ID (c2.org_id);
3070 -- set to trigger mode to bypass the 'SAVEPOINT' and 'ROLLBACK' command.
3071 lv_result := FND_REQUEST.SET_MODE(TRUE);
3072
3073 o_request_id := NULL;
3074 o_request_id := FND_REQUEST.SUBMIT_REQUEST(
3075 'PO', -- application
3076 'REQIMPORT',-- program
3077 NULL, -- description
3078 NULL, -- start_time
3079 FALSE, -- sub_request
3080 'MSC',
3081 G_PO_BATCH_NUMBER,
3082 p_po_group_by_name,
3083 0);
3084 END LOOP;
3085 END;
3086
3087 END LD_PO_REQUISITIONS_INTERFACE;
3088
3089 PROCEDURE LD_PO_RESCHEDULE_INTERFACE
3090 ( o_request_id OUT NOCOPY NUMBER)
3091 IS
3092 lv_sqlstmt VARCHAR2(4000);
3093 lv_instance_id NUMBER;
3094 lv_dblink VARCHAR2(128);
3095
3096 lv_result BOOLEAN;
3097
3098 BEGIN
3099
3100 select DECODE( A2M_DBLINK,
3101 NULL, ' ',
3102 '@'||A2M_DBLINK),
3103 INSTANCE_ID
3104 into lv_dblink,
3105 lv_instance_id
3106 from MRP_AP_APPS_INSTANCES_ALL
3107 where instance_id = v_instance_id
3108 and instance_code = v_instance_code
3109 and nvl(a2m_dblink,NULL_DBLINK) = nvl(v_dblink,NULL_DBLINK)
3110 and ALLOW_RELEASE_FLAG=1;
3111
3112 lv_sqlstmt:=
3113 'INSERT INTO PO_RESCHEDULE_INTERFACE'
3114 ||' ( LINE_ID,'
3115 ||' QUANTITY,'
3116 ||' NEED_BY_DATE,'
3117 ||' PROCESS_ID,'
3118 ||' LAST_UPDATE_DATE,'
3119 ||' LAST_UPDATED_BY,'
3120 ||' CREATION_DATE,'
3121 ||' CREATED_BY,'
3122 ||' LAST_UPDATE_LOGIN,'
3123 ||' REQUEST_ID,'
3124 ||' PROGRAM_APPLICATION_ID,'
3125 ||' PROGRAM_ID,'
3126 ||' PROGRAM_UPDATE_DATE )'
3127 ||' SELECT'
3128 ||' LINE_ID,'
3129 ||' QUANTITY,'
3130 ||' NEED_BY_DATE,'
3131 ||' NULL,'
3132 ||' SYSDATE,'
3133 ||' FND_GLOBAL.USER_ID,'
3134 ||' SYSDATE,'
3135 ||' FND_GLOBAL.USER_ID,'
3136 ||' LAST_UPDATE_LOGIN,'
3137 ||' REQUEST_ID,'
3138 ||' PROGRAM_APPLICATION_ID,'
3139 ||' PROGRAM_ID,'
3140 ||' PROGRAM_UPDATE_DATE'
3141 ||' FROM MSC_PO_RESCHEDULE_INTERFACE'||lv_dblink
3142 ||' WHERE SR_INSTANCE_ID= :lv_instance_id';
3143
3144 EXECUTE IMMEDIATE lv_sqlstmt USING lv_instance_id;
3145
3146 -- Launching the POXRSR in loop for each OU, change for MOAC
3147 DECLARE
3148 CURSOR c1 IS
3149 SELECT DISTINCT prla.org_id
3150 FROM PO_RESCHEDULE_INTERFACE PRI, PO_REQUISITION_LINES_ALL PRLA
3151 WHERE pri.line_id = prla.requisition_line_id;
3152
3153 BEGIN
3154 FOR C2 IN C1
3155 LOOP
3156
3157 /*MO_GLOBAL.INIT ('PO'); Bug 8397994 */
3158 FND_REQUEST.SET_ORG_ID (c2.org_id);
3159 -- set to trigger mode to bypass the 'SAVEPOINT' and 'ROLLBACK' command.
3160 lv_result := FND_REQUEST.SET_MODE(TRUE);
3161
3162 o_request_id := NULL;
3163 o_request_id := FND_REQUEST.SUBMIT_REQUEST(
3164 'PO', -- application
3165 'POXRSR', -- program
3166 NULL, -- description
3167 NULL, -- start_time
3168 FALSE); -- sub_request
3169 END LOOP;
3170 END;
3171
3172 END LD_PO_RESCHEDULE_INTERFACE;
3173
3174
3175 PROCEDURE MODIFY_COMPONENT_REQUIREMENT
3176 IS
3177
3178
3179 Cursor C1 is
3180 Select a.header_id,min(bos.operation_seq_num) new_op_seq
3181 from wip_job_schedule_interface a,
3182 wip_job_dtls_interface b,
3183 bom_operation_Sequences bos,
3184 bom_operational_routings bor
3185 where a.group_id = b.group_id
3186 and a.header_id = b.parent_header_id --added for the bug#3538800
3187 and a.group_id = G_WIP_GROUP_ID
3188 and a.primary_item_id = bor.assembly_item_id
3189 and a.organization_id = bor.organization_id
3190 and nvl(bor.alternate_routing_Designator,0) = nvl(a.alternate_routing_designator,0)
3191 and bor.common_routing_Sequence_id = bos.routing_Sequence_id
3192 and b.load_type = 2
3193 and b.substitution_type = 3
3194 and a.source_code = 'MSC'
3195 and b.operation_seq_num = 1
3196 and ( bos.disable_date IS NULL
3197 OR trunc(bos.disable_date) >= trunc(nvl(a.bom_revision_date,a.first_unit_start_date))
3198 )
3199 group by a.header_id;
3200
3201 Cursor C2 is
3202 select sum(round(b.QUANTITY_PER_ASSEMBLY*NVL(b.COMPONENT_YIELD_FACTOR,1),6)) qty_per_assy,
3203 sum(b.REQUIRED_QUANTITY) reqd_qty,
3204 b.group_id,
3205 b.parent_header_id,
3206 b.INVENTORY_ITEM_ID_OLD,
3207 b.ORGANIZATION_ID,
3208 b.OPERATION_SEQ_NUM
3209 from wip_job_schedule_interface a,
3210 wip_job_dtls_interface b
3211 where a.source_code = 'MSC'
3212 and a.group_id = G_WIP_GROUP_ID
3213 and a.group_id = b.group_id
3214 and a.header_id = b.parent_header_id
3215 and b.load_type = 2
3216 and b.substitution_type = 3
3217 and b.process_phase = 2
3218 and b.process_status = 1
3219 group by b.group_id,
3220 b.parent_header_id,
3221 b.ORGANIZATION_ID,
3222 b.INVENTORY_ITEM_ID_OLD,
3223 b.OPERATION_SEQ_NUM;
3224
3225 Cursor C3 is
3226 select b.rowid
3227 from wip_job_schedule_interface a,
3228 wip_job_dtls_interface b
3229 where a.source_code = 'MSC'
3230 and a.group_id = G_WIP_GROUP_ID
3231 and a.group_id = b.group_id
3232 and a.header_id = b.parent_header_id
3233 and b.load_type = 2
3234 and b.substitution_type = 3
3235 and b.process_phase = 2
3236 and b.process_status = 1
3237 and b.rowid not in (select min(c.rowid)
3238 from wip_job_dtls_interface c
3239 where b.group_id = c.group_id
3240 and b.parent_header_id = c.parent_header_id
3241 and b.ORGANIZATION_ID = c.ORGANIZATION_ID
3242 and b.INVENTORY_ITEM_ID_OLD = c.INVENTORY_ITEM_ID_OLD
3243 and b.OPERATION_SEQ_NUM = c.OPERATION_SEQ_NUM
3244 and b.load_type = c.load_type
3245 and b.substitution_type = c.substitution_type
3246 and b.process_phase = c.process_phase
3247 and b.process_status = c.process_status );
3248
3249 Begin
3250
3251 For I in C1
3252
3253 loop
3254 update wip_job_dtls_interface
3255 set operation_Seq_num = I.new_op_seq
3256 where parent_header_id = I.header_id
3257 and operation_seq_num = 1
3258 and load_type = 2
3259 and substitution_type = 3;
3260
3261 End loop;
3262
3263 For J in C2
3264
3265 loop
3266 update wip_job_dtls_interface
3267 set QUANTITY_PER_ASSEMBLY = J.qty_per_assy,
3268 REQUIRED_QUANTITY = J.reqd_qty
3269 where group_id = J.group_id
3270 and parent_header_id = J.parent_header_id
3271 and ORGANIZATION_ID = J.ORGANIZATION_ID
3272 and INVENTORY_ITEM_ID_OLD = J.INVENTORY_ITEM_ID_OLD
3273 and OPERATION_SEQ_NUM = J.OPERATION_SEQ_NUM
3274 and load_type = 2
3275 and substitution_type = 3
3276 and process_phase = 2
3277 and process_status = 1;
3278
3279 End loop;
3280
3281 For K in C3
3282
3283 loop
3284
3285 delete wip_job_dtls_interface
3286 where rowid = K.rowid;
3287
3288 End loop;
3289
3290 End MODIFY_COMPONENT_REQUIREMENT;
3291
3292
3293 PROCEDURE MODIFY_RESOURCE_REQUIREMENT
3294 IS
3295
3296 /* dsr jsi.primary_item_id and jsi.organization_id can be null */
3297 /* dsr: added outer join in the following cursor*/
3298 cursor cres_upd is select jdi.rowid,jdi.operation_seq_num,
3299 jdi.parent_header_id,rtng.common_routing_Sequence_id
3300 FROM BOM_OPERATIONAL_ROUTINGS rtng,
3301 wip_job_dtls_interface jdi,
3302 wip_job_schedule_interface jsi
3303 where rtng.assembly_item_id (+) = jsi.primary_item_id -- dsr
3304 and jsi.group_id = G_WIP_GROUP_ID
3305 AND rtng.organization_id (+) = jsi.organization_id -- dsr
3306 AND NVL(rtng.alternate_routing_designator,' ')=
3307 NVL( jsi.alternate_routing_designator,' ')
3308 and jsi.header_id = jdi.parent_header_id
3309 and nvl(jdi.resource_seq_num,-1) = -1000
3310 and jdi.load_type = 1
3311 order by jdi.parent_header_id,jdi.operation_Seq_num;
3312
3313 v_old_op number;
3314 v_old_res number;
3315 v_old_header number;
3316
3317 TYPE NumTab IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
3318 TYPE RIDTab IS TABLE OF ROWID INDEX BY BINARY_INTEGER;
3319 TYPE DateTab IS TABLE OF DATE INDEX BY BINARY_INTEGER;
3320
3321 v_max_resource_Seq number;
3322
3323 lv_std_job_count NUMBER;
3324 lv_jsi_rowid RIDTab;
3325 lv_header_id NumTab;
3326 lv_routing_seq_id NumTab;
3327 lv_organization_id NumTab;
3328 lv_start_date DateTab;
3329 lv_end_Date DateTab;
3330
3331 lv_res_req_count NUMBER;
3332 lv_jdi_rowid RIDTab;
3333 lv_op_seq_num NumTab;
3334 lv_sim_res_seq NumTab;
3335 lv_res_priority NumTab;
3336 lv_resource_id NumTab;
3337 lv_resource_id_old NumTab;
3338 lv_res_seq NumTab;
3339 lv_sub_type NumTab;
3340 lv_res_id_old NumTab;
3341 lv_ld_type NumTab;
3342
3343 lu_res_req_count NUMBER;
3344 lu_op_seq_num NumTab;
3345 lu_sim_res_seq NumTab;
3346 lu_res_priority NumTab;
3347 lu_res_seq NumTab;
3348 lu_resource_id NumTab;
3349 lu_res_required NumTab;
3350 lu_schedule_seq_num NumTab;
3351
3352
3353 lun_res_req_count NUMBER;
3354 lun_usage NumTab;
3355 lun_op_seq_num NumTab;
3356 lun_sim_res_seq NumTab;
3357 lun_res_priority NumTab;
3358 lun_res_seq NumTab;
3359 lun_resource_id NumTab;
3360 lun_basis_type NumTab;
3361 lun_res_required NumTab;
3362
3363
3364 elsud_op_seq_num NumTab;
3365 elsud_sim_res_seq NumTab;
3366 elsud_res_seq NumTab;
3367 elsud_jdi_rowid RIDTab;
3368 elsud_sub_grp NumTab;
3369 elsud_rep_grp NumTab;
3370
3371 lv_elsud_cnt NUMBER;
3372
3373 v_previous_op number;
3374 v_previous_res number;
3375 v_previous_res1 number;
3376 v_next_op number;
3377 v_next_res number;
3378 v_next_res1 number;
3379 v_last_end date;
3380 v_frst_end date;
3381 v_last_start date;
3382 v_frst_start date;
3383
3384 luno_op_count number;
3385 luno_op_seq_num NumTab;
3386 luno_std_op_seq_id NumTab;
3387
3388
3389
3390 lv_res_alt_op_seq_num NumTab;
3391 lv_res_alt_schd_seq_num NumTab;
3392 lv_res_alt_prin_flag NumTab;
3393 lv_res_alt_res_seq_num NumTab;
3394 lv_res_alt_res_id NumTab;
3395 lv_res_alt_hdr_id NumTab;
3396 lv_res_alt_org_id NumTab;
3397 lv_res_alt_rowid RIDTab;
3398 lv_res_alt_sub_grp_num NumTab;
3399 lv_res_alt_rep_grp_num NumTab;
3400 lv_res_alt_start_date DateTab;
3401 lv_res_alt_completion_date DateTab;
3402 lv_res_alt_usage_rate NumTab;
3403 lv_res_alt_basis_type NumTab;
3404 lv_res_alt_req_count Number;
3405
3406
3407
3408 lsud_header_id NumTab;
3409 lsud_sub_grp NumTab;
3410 lsud_new_usage NumTab;
3411 lsud_rep_grp NumTab;
3412 lsud_organization_id Numtab;
3413 lsud_op_seq_num NumTab;
3414 lsud_sim_res_seq NumTab;
3415 lsud_res_priority NumTab;
3416 lsud_res_seq NumTab;
3417 lsud_resource_id NumTab;
3418 lsud_jdi_rowid RIDTab;
3419 lsud_resource_id_del NumTab;
3420 lsud_basis_type NumTab;
3421 lsud_res_req_count Number;
3422 lsud_row NumTab;
3423 lsud_start_date DateTab;
3424 lsud_completion_Date DateTab;
3425
3426 -- dsr added following 7 lines
3427 lsud_firm_flag NumTab;
3428 lsud_setup_id NumTab;
3429 lsud_group_sequence_id NumTab;
3430 lsud_group_sequence_number NumTab;
3431 lsud_batch_id NumTab;
3432 lsud_maximum_assigned_units NumTab;
3433 lsud_parent_seq_num NumTab;
3434
3435
3436 lv_init_k NUMBER;
3437 temp_res_id NUMBER;
3438 lv_scheduled_flag NumTab;
3439 lv_parent_id NumTab;
3440 lv_job_schedule_type NumTab;
3441 lv_wip_entity_id NumTab;
3442
3443 BEGIN
3444
3445
3446 SELECT jsi.rowid,
3447 jsi.header_id,
3448 jsi.organization_id,
3449 jsi.first_unit_start_date,
3450 jsi.last_unit_completion_date,
3451 rtng.common_routing_sequence_id,
3452 jsi.load_type,
3453 jsi.wip_entity_id
3454 BULK COLLECT
3455 INTO lv_jsi_rowid,
3456 lv_header_id,
3457 lv_organization_id,
3458 lv_start_date,
3459 lv_end_date,
3460 lv_routing_seq_id,
3461 lv_job_schedule_type,
3462 lv_wip_entity_id
3463 FROM BOM_OPERATIONAL_ROUTINGS rtng,
3464 wip_job_schedule_interface jsi
3465 WHERE jsi.group_id = G_WIP_GROUP_ID
3466 AND jsi.load_type in(1,3) /* create standard job */
3467 AND rtng.assembly_item_id(+)= jsi.primary_item_id
3468 AND rtng.organization_id(+)= jsi.organization_id
3469 AND NVL(rtng.alternate_routing_designator(+),' ')=
3470 NVL( jsi.alternate_routing_designator,' ');
3471
3472 lv_std_job_count:= SQL%ROWCOUNT;
3473
3474 IF lv_std_job_count= 0 THEN RETURN; END IF;
3475
3476 FOR n IN 1..lv_std_job_count LOOP
3477
3478 BEGIN
3479 SELECT jdi.ROWID,
3480 jdi.operation_seq_num,
3481 -- jdi.resource_seq_num,
3482 nvl(jdi.schedule_seq_num, jdi.resource_seq_num),
3483 TO_NUMBER(nvl(jdi.REPLACEMENT_GROUP_NUM,0)),
3484 jdi.resource_id_old,
3485 jdi.resource_seq_num,
3486 jdi.substitution_type,
3487 jdi.resource_id_old,
3488 jdi.load_type,
3489 jdi.scheduled_flag,
3490 jdi.parent_header_id
3491 BULK COLLECT
3492 INTO lv_jdi_rowid,
3493 lv_op_seq_num,
3494 lv_sim_res_seq,
3495 lv_res_priority,
3496 lv_resource_id,
3497 lv_res_seq,
3498 lv_sub_type,
3499 lv_resource_id_old,
3500 lv_ld_type,
3501 lv_scheduled_flag,
3502 lv_parent_id
3503 FROM WIP_JOB_DTLS_INTERFACE jdi
3504 WHERE jdi.group_id= G_WIP_GROUP_ID
3505 AND jdi.parent_header_id= lv_header_id(n)
3506 -- dsr AND jdi.load_type=LT_RESOURCE
3507 AND jdi.load_type IN (LT_RESOURCE, LT_RESOURCE_USAGE
3508 , RESOURCE_INSTANCES, RESOURCE_INSTANCE_USAGE
3509 )
3510 ORDER BY
3511 2,3,5;
3512
3513 lv_res_req_count:= SQL%ROWCOUNT;
3514
3515 EXCEPTION
3516 WHEN OTHERS THEN RAISE;
3517 END;
3518
3519 IF nvl(lv_res_req_count,0) = 0 THEN
3520
3521 UPDATE wip_job_schedule_interface
3522 SET first_unit_start_date=NULL,
3523 scheduling_method= WIP_CONSTANTS.ROUTING
3524 WHERE ROWID= lv_jsi_rowid(n);
3525
3526 GOTO next_c_std_job;
3527 ELSE
3528
3529
3530 UPDATE wip_job_schedule_interface
3531 set scheduling_method= WIP_CONSTANTS.ML_MANUAL
3532 WHERE ROWID= lv_jsi_rowid(n);
3533
3534 END IF;
3535
3536 If nvl(lv_job_schedule_type(n),1) = 1 Then
3537
3538 SELECT os.operation_seq_num,
3539 to_number(decode(nvl(bor.schedule_seq_num,-1),-1,bor.resource_seq_num,bor.schedule_seq_num)),
3540 0,
3541 bor.resource_seq_num,
3542 bor.schedule_seq_num,
3543 bor.resource_id,
3544 2
3545 BULK COLLECT
3546 INTO lu_op_seq_num,
3547 lu_sim_res_seq,
3548 lu_res_priority,
3549 lu_res_seq,
3550 lu_schedule_seq_num,
3551 lu_resource_id,
3552 lu_res_required
3553 FROM BOM_OPERATION_RESOURCES bor,
3554 BOM_OPERATION_SEQUENCES os
3555 WHERE os.routing_sequence_id= lv_routing_seq_id(n)
3556 AND bor.operation_sequence_id= os.operation_sequence_id
3557 AND os.effectivity_date <= lv_start_date(n)
3558 AND NVL(os.disable_date, lv_start_date(n)) >= lv_start_date(n)
3559 ORDER BY
3560 1,2,3 ASC,5;
3561
3562
3563
3564 lu_res_req_count:= SQL%ROWCOUNT;
3565
3566
3567 BEGIN
3568
3569 SELECT os.operation_seq_num,os.standard_operation_id
3570 BULK COLLECT
3571 INTO luno_op_seq_num,luno_std_op_Seq_id
3572 FROM BOM_OPERATION_SEQUENCES os
3573 WHERE os.routing_sequence_id= lv_routing_seq_id(n)
3574 AND os.effectivity_date <= lv_start_date(n)
3575 AND NVL(os.disable_date, lv_start_date(n)) >= lv_start_date(n)
3576 AND not exists(select jdi.operation_seq_num
3577 FROM BOM_OPERATIONAL_ROUTINGS rtng,
3578 BOM_OPERATION_SEQUEnCES seqs,
3579 wip_job_dtls_interface jdi,
3580 wip_job_schedule_interface jsi
3581 where rtng.assembly_item_id= jsi.primary_item_id
3582 AND rtng.organization_id= jsi.organization_id
3583 and nvl(rtng.common_routing_sequence_id,rtng.routing_sequence_id) = os.routing_Sequence_id
3584 AND nvl(rtng.common_routing_sequence_id,rtng.routing_Sequence_id) = seqs.routing_sequence_id
3585 AND NVL(rtng.alternate_routing_designator,' ')=
3586 NVL( jsi.alternate_routing_designator,' ')
3587 and jsi.header_id = jdi.parent_header_id
3588 and jdi.load_type = 3
3589 and seqs.operation_seq_num = os.operation_seq_num
3590 and seqs.operation_seq_num = jdi.operation_seq_nuM
3591 and jdi.parent_header_id = lv_header_id(n)
3592 and jdi.group_id = jsi.group_id
3593 and jsi.group_id = G_WIP_GROUP_ID);
3594
3595 luno_op_count:= SQL%ROWCOUNT;
3596
3597
3598 EXCEPTION WHEN OTHERS THEN
3599 RAISE;
3600 END;
3601
3602
3603 SELECT os.operation_seq_num,
3604 to_number(decode(nvl(bor.schedule_seq_num,-1),-1,bor.resource_seq_num,bor.schedule_seq_num)),
3605 0,
3606 bor.resource_seq_num,
3607 bor.resource_id,
3608 bor.basis_type,
3609 bor.usage_rate_or_Amount,
3610 2
3611 BULK COLLECT
3612 INTO lun_op_seq_num,
3613 lun_sim_res_seq,
3614 lun_res_priority,
3615 lun_res_seq,
3616 lun_resource_id,
3617 lun_basis_type,
3618 lun_usage,
3619 lun_res_required
3620 FROM BOM_OPERATION_RESOURCES bor,
3621 BOM_OPERATION_SEQUENCES os,
3622 BOM_RESOURCES br
3623 WHERE os.routing_sequence_id= lv_routing_seq_id(n)
3624 AND bor.operation_sequence_id= os.operation_sequence_id
3625 AND os.effectivity_date <= lv_start_date(n)
3626 -- AND NVL(os.disable_date, lv_start_date(n)) >= lv_start_date(n)
3627 AND NVL(os.disable_date, nvl(br.disable_date, lv_start_date(n))) >= lv_start_date(n) -- bug# 4290120
3628 AND br.resource_id = bor.resource_id
3629 and schedule_flag = 2
3630 AND not exists(select jdi.resource_id_new
3631 FROM BOM_OPERATIONAL_ROUTINGS rtng,
3632 BOM_OPERATION_SEQUENCES seqs,
3633 wip_job_dtls_interface jdi,
3634 wip_job_schedule_interface jsi
3635 where rtng.assembly_item_id= jsi.primary_item_id
3636 AND rtng.organization_id= jsi.organization_id
3637 AND nvl(rtng.common_routing_sequence_id,rtng.routing_sequence_id) = seqs.routing_sequence_id
3638 AND NVL(rtng.alternate_routing_designator,' ')=
3639 NVL( jsi.alternate_routing_designator,' ')
3640 and jsi.header_id = jdi.parent_header_id
3641 and jdi.load_type = 1
3642 and jdi.resource_id_new = bor.resource_id
3643 and nvl(rtng.common_routing_sequence_id,rtng.routing_sequence_id) = os.routing_Sequence_id
3644 and seqs.operation_seq_num = os.operation_seq_num
3645 and seqs.operation_seq_num = jdi.operation_seq_nuM
3646 and jdi.parent_header_id = lv_header_id(n)
3647 and jdi.group_id = jsi.group_id
3648 and jsi.group_id = G_WIP_GROUP_ID);
3649
3650 lun_res_req_count:= SQL%ROWCOUNT;
3651
3652 SELECT distinct os.operation_seq_num,
3653 to_number(bor.schedule_seq_num),
3654 bor.principle_flag,
3655 to_number(bor1.resource_seq_num),
3656 bor.resource_id,
3657 bor1.resource_id,
3658 jdi.parent_header_id,
3659 jdi.organization_id,
3660 jdi.rowid,
3661 bor1.substitute_group_num,
3662 jdi.REPLACEMENT_GROUP_NUM,
3663 jdi.start_date,
3664 jdi.completion_date,
3665 jdi.usage_rate_or_amount,
3666 bor.basis_type
3667 -- dsr: added the following 7 columns
3668 , jdi.firm_flag
3669 , jdi.setup_id
3670 , jdi.group_sequence_id
3671 , jdi.group_sequence_number
3672 , jdi.batch_id
3673 , jdi.maximum_assigned_units
3674 , jdi.parent_seq_num
3675 -- , resource_seq_num
3676 -- , schedule_seq_num
3677 BULK COLLECT
3678 INTO lsud_op_seq_num,
3679 lsud_sim_res_seq,
3680 lsud_res_priority,
3681 lsud_res_seq,
3682 lsud_resource_id,
3683 lsud_resource_id_del,
3684 lsud_header_id,
3685 lsud_organization_id,
3686 lsud_jdi_rowid,
3687 lsud_sub_grp,
3688 lsud_rep_grp,
3689 lsud_start_date,
3690 lsud_completion_date,
3691 lsud_new_usage,
3692 lsud_basis_type
3693 -- dsr added following 7 lines
3694 , lsud_firm_flag
3695 , lsud_setup_id
3696 , lsud_group_sequence_id
3697 , lsud_group_sequence_number
3698 , lsud_batch_id
3699 , lsud_maximum_assigned_units
3700 , lsud_parent_seq_num
3701 -- , lsud_schedule_seq_num
3702 FROM BOM_SUB_OPERATION_RESOURCES bor,
3703 bom_operation_resources bor1,
3704 BOM_OPERATION_SEQUENCES os,
3705 BOM_RESOURCES br,
3706 WIP_JOB_DTLS_INTERFACE JDI
3707 WHERE os.routing_sequence_id= lv_routing_seq_id(n)
3708 AND bor.operation_sequence_id= os.operation_sequence_id
3709 AND os.effectivity_date <= lv_start_date(n)
3710 -- AND NVL(os.disable_date, lv_start_date(n)) >= lv_start_date(n)
3711 AND NVL(os.disable_date, nvl(br.disable_date,lv_start_date(n))) >= lv_start_date(n)
3712 AND br.resource_id = bor.resource_id
3713 AND bor.operation_sequence_id= bor1.operation_sequence_id
3714 and bor.substitute_group_num = bor1.substitute_group_num
3715 and bor.schedule_seq_num = bor1.schedule_seq_num
3716 AND TO_NUMBER(bor.schedule_seq_num) IS NOT NULL
3717 AND JDI.RESOURCE_ID_NEW = BOR.RESOURCE_ID
3718 -- and jdi.resource_seq_num = bor1.resource_seq_num
3719 and jdi.schedule_seq_num = bor1.schedule_seq_num
3720 /*The resource_seq in jdi is actually the schedule_seq*/
3721 AND JDI.GROUP_ID =G_WIP_GROUP_ID
3722 and jdi.parent_header_id = lv_header_id(n) /* Bug # 2671426 - Forward Port for Bug 2657820 */
3723 and nvl(jdi.REPLACEMENT_GROUP_NUM,-1) <> 0
3724 and bor.replacement_group_num = nvl(jdi.REPLACEMENT_GROUP_NUM,-100)
3725 -- dsr and jdi.load_type = 1
3726 and jdi.load_type IN (1, LT_RESOURCE_USAGE, RESOURCE_INSTANCES, RESOURCE_INSTANCE_USAGE)
3727 ORDER BY
3728 1,2,3 ASC,5;
3729
3730 lsud_res_req_count:= SQL%ROWCOUNT;
3731
3732 lv_init_k:= 1;
3733
3734
3735 /* 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 */
3736
3737 FOR j IN 1..lv_res_req_count LOOP
3738
3739
3740 -- FOR k in lv_init_k..lu_res_req_count LOOP
3741 /* Bug : 1967136 , in order to fix resource id does not exists issue*/
3742 FOR k in 1..lu_res_req_count LOOP
3743
3744 IF lv_op_seq_num(j) = lu_op_seq_num(k) AND
3745 lv_sim_res_seq(j) = lu_sim_res_seq(k) AND
3746 lv_res_priority(j)= lu_res_priority(k) AND
3747 lv_resource_id (j)= lu_resource_id(k) THEN
3748
3749 -- lv_res_seq(j):= lu_res_seq(k);
3750 lu_res_required(k):= 1;
3751
3752 lv_init_k:= k+1;
3753 EXIT;
3754 END IF;
3755
3756 END LOOP; -- k
3757
3758 END LOOP;
3759
3760
3761
3762 lv_init_k:= 1;
3763
3764
3765 /* Based on the Resource_Seq_num we got above, update the records with that resource_seq_num
3766 for the resource records in WIP_JOB_DETAILS_INTERFACE, (the one's we are created) */
3767
3768 FOR j IN 1..lv_res_req_count
3769
3770 Loop
3771
3772
3773
3774 UPDATE WIP_JOB_DTLS_INTERFACE
3775 SET resource_seq_num= lv_res_seq(j),
3776 resource_id_old = lv_resource_id(j),
3777 schedule_seq_num = lv_sim_res_seq(j),
3778 scheduled_flag = lv_scheduled_flag(j)
3779 WHERE ROWID= lv_jdi_rowid(j)
3780 and lv_res_priority(j) = 0; --Update only the primary resources
3781
3782 End loop;
3783
3784
3785 /* HANDLE ALTERNATE RESOURCES */
3786 /* If we are enforcing use of a alternate resource, then we first need to communicate to WIP to
3787 delete the primary resource which wip will get by exploding the routing. This loop Below will
3788 loop through all Resource records where we are implementing alternate and delete the primaries
3789 for that alternate -- this was old logic*/
3790 /* Now if we communicating to WIP that we are passing alternate resource to WIP,
3791 for a resource
3792 substitution, the record in wip_job_dtls_interface should have the
3793 following columns filled in:
3794 load_type = 1
3795 substitution_type = 3
3796 opearation_seq_num, resource_seq_num, resource_id_old, resource_id_new,
3797 and substitute_group_num should be set to the current(or primary) resource in
3798 wip_operation_resources
3799 replacement_group_num = valid value in wip_sub_operation_resources.Finally the
3800 alternate resource record will be deleted from wip_job_dtls_interface*/
3801
3802 FOR j IN 1..lsud_res_req_count
3803
3804 Loop
3805 Begin
3806
3807 INSERT INTO WIP_JOB_DTLS_INTERFACE
3808 ( last_update_date,
3809 last_updated_by,
3810 last_update_login,
3811 creation_date,
3812 created_by,
3813 group_id,
3814 parent_header_id,
3815 operation_seq_num,
3816 resource_seq_num,
3817 resource_id_old,
3818 resource_id_new,
3819 replacement_group_num,
3820 substitute_group_num,
3821 start_date,
3822 completion_date,
3823 organization_id,
3824 substitution_type,
3825 load_type,
3826 process_phase,
3827 process_status,
3828 scheduled_flag
3829 -- dsr: added the following 7 columns
3830 , firm_flag
3831 , setup_id
3832 , group_sequence_id
3833 , group_sequence_number
3834 , batch_id
3835 , maximum_assigned_units
3836 , parent_seq_num
3837 -- , resource_seq_num
3838 -- , schedule_seq_num
3839 )
3840 SELECT SYSDATE,
3841 FND_GLOBAL.USER_ID,
3842 FND_GLOBAL.USER_ID,
3843 SYSDATE,
3844 FND_GLOBAL.USER_ID,
3845 G_WIP_GROUP_ID,
3846 lsud_header_id(j),
3847 lsud_op_seq_num(j),
3848 lsud_res_seq(j),
3849 lsud_resource_id_del(j),
3850 lsud_resource_id_del(j),
3851 lsud_rep_grp(j),
3852 lsud_sub_grp(j),
3853 lsud_start_date(j),
3854 lsud_completion_date(j),
3855 lsud_organization_id(j),
3856 3,
3857 1,
3858 2,
3859 1,
3860 1 --lv_scheduled_flag(j)
3861 -- dsr: added the following 7 columns
3862 , lsud_firm_flag(j)
3863 , lsud_setup_id(j)
3864 , lsud_group_sequence_id(j)
3865 , lsud_group_sequence_number(j)
3866 , lsud_batch_id(j)
3867 , lsud_maximum_assigned_units(j)
3868 , lsud_parent_seq_num(j)
3869 -- , lsud_schedule_seq_num(j)
3870 FROM DUAL;
3871
3872
3873 /*Delete the Alternate that we provided */
3874 delete from WIP_JOB_DTLS_INTERFACE
3875 WHERE ROWID= lsud_jdi_rowid(j);
3876 Exception
3877 when others then raise;
3878 End;
3879
3880 End loop;
3881
3882 /* HANDLE UNSCHEDULES OPERATIONS AND RESOURCES */
3883 /* Since WIP Will Pull into the Job , all operations that APS didn't even collect
3884 (scheduled=no), we will set the start and end time of these operations to the
3885 fall in between the previous op from this op and the next op from this op */
3886
3887
3888 FOR M in 1..luno_op_count
3889
3890 Loop
3891
3892
3893 BEGIN
3894 select nvl(max(operation_seq_num),-1)
3895 into v_previous_op
3896 from wip_job_dtls_interface
3897 where parent_header_id = lv_header_id(n)
3898 and group_id = G_WIP_GROUP_ID
3899 and load_type = 3
3900 and substitution_type <> 1
3901 and operation_seq_num < luno_op_seq_num(m);
3902
3903 select nvl(min(operation_seq_num),-1)
3904 into v_next_op
3905 from wip_job_dtls_interface
3906 where parent_header_id = lv_header_id(n)
3907 and group_id = G_WIP_GROUP_ID
3908 and substitution_type <> 1
3909 and load_type = 3
3910 and operation_seq_num > luno_op_seq_num(m);
3911
3912
3913 /* If v_previous_op = -1 and v_next_op = -1 , Do nothing */
3914 /* as we will not plan for just 1 un-scheduled opeartion, if it exists */
3915
3916 if (v_previous_op = -1 and v_next_op <> -1) then
3917
3918 Select first_unit_start_date
3919 into v_last_end
3920 from wip_job_dtls_interface
3921 where parent_header_id = lv_header_id(n)
3922 and group_id = G_WIP_GROUP_ID
3923 and load_type = 3
3924 and operation_seq_num = v_next_op;
3925
3926 v_last_start := v_last_end;
3927 v_frst_start := v_last_end;
3928 v_frst_end := v_last_end;
3929
3930 elsif (v_next_op = -1 and v_previous_op <> -1) then
3931
3932 Select last_unit_completion_date
3933 into v_frst_start
3934 from wip_job_dtls_interface
3935 where parent_header_id = lv_header_id(n)
3936 and group_id = G_WIP_GROUP_ID
3937 and load_type = 3
3938 and operation_seq_num = v_previous_op;
3939
3940 v_frst_end := v_frst_start;
3941 v_last_start := v_frst_start;
3942 v_last_end := v_frst_start;
3943
3944 else
3945
3946 Select first_unit_start_date
3947 into v_last_end
3948 from wip_job_dtls_interface
3949 where parent_header_id = lv_header_id(n)
3950 and group_id = G_WIP_GROUP_ID
3951 and load_type = 3
3952 and operation_seq_num = v_next_op;
3953
3954 v_last_start := v_last_end;
3955
3956 Select last_unit_completion_date
3957 into v_frst_start
3958 from wip_job_dtls_interface
3959 where parent_header_id = lv_header_id(n)
3960 and group_id = G_WIP_GROUP_ID
3961 and load_type = 3
3962 and operation_seq_num = v_previous_op;
3963
3964 v_frst_end := v_frst_start;
3965
3966 end if;
3967
3968 EXCEPTION WHEN OTHERS THEN
3969 RAISE;
3970 END;
3971
3972 INSERT INTO WIP_JOB_DTLS_INTERFACE
3973 ( last_update_date,
3974 last_updated_by,
3975 last_update_login,
3976 creation_date,
3977 created_by,
3978 group_id,
3979 parent_header_id,
3980 operation_seq_num,
3981 standard_operation_id,
3982 organization_id,
3983 substitution_type,
3984 load_type,
3985 first_unit_start_date,
3986 first_unit_completion_date,
3987 last_unit_start_date,
3988 last_unit_completion_date,
3989 process_phase,
3990 process_status,
3991 scheduled_flag)
3992 SELECT SYSDATE,
3993 FND_GLOBAL.USER_ID,
3994 FND_GLOBAL.USER_ID,
3995 SYSDATE,
3996 FND_GLOBAL.USER_ID,
3997 G_WIP_GROUP_ID,
3998 lv_header_id(n),
3999 luno_op_seq_num(m),
4000 luno_std_op_seq_id(m),
4001 lv_organization_id(n),
4002 3,
4003 3,
4004 v_frst_start,
4005 v_frst_end,
4006 v_last_start,
4007 v_last_end,
4008 2,
4009 1,
4010 2 --lv_scheduled_flag(j)
4011 FROM DUAL;
4012
4013 End loop;
4014
4015 FOR j IN 1..lun_res_req_count
4016
4017 Loop
4018 Begin
4019
4020 BEGIN
4021 select max(operation_seq_num)
4022 into v_previous_op
4023 from wip_job_dtls_interface
4024 where parent_header_id = lv_header_id(n)
4025 and group_id = G_WIP_GROUP_ID
4026 and load_type = 3
4027 and substitution_type <> 1
4028 and operation_seq_num <= lun_op_seq_num(j);
4029
4030 select min(operation_seq_num)
4031 into v_next_op
4032 from wip_job_dtls_interface
4033 where parent_header_id = lv_header_id(n)
4034 and group_id = G_WIP_GROUP_ID
4035 and substitution_type <> 1
4036 and load_type = 3
4037 and operation_seq_num >= lun_op_seq_num(j);
4038
4039 select nvl(max(resource_seq_num),-1)
4040 into v_previous_res
4041 from wip_job_dtls_interface s
4042 where s.parent_header_id = lv_header_id(n)
4043 and s.group_id = G_WIP_GROUP_ID
4044 and s.load_type = 1
4045 and s.substitution_type <> 1
4046 and s.operation_seq_num = lun_op_seq_num(j)
4047 and nvl(s.schedule_seq_num,s.resource_seq_num) < lun_res_seq(j);
4048
4049
4050 select nvl(max(schedule_seq_num),-1)
4051 into v_previous_res1
4052 from wip_job_dtls_interface s
4053 where s.parent_header_id = lv_header_id(n)
4054 and s.group_id = G_WIP_GROUP_ID
4055 and s.load_type = 1
4056 and s.substitution_type <> 1
4057 and s.operation_seq_num = lun_op_seq_num(j)
4058 and nvl(s.schedule_seq_num,s.resource_seq_num) < lun_res_seq(j);
4059
4060 if (v_previous_res1 > v_previous_res) then
4061 select nvl(max(resource_seq_num),-1)
4062 into v_previous_res
4063 from wip_job_dtls_interface s
4064 where s.parent_header_id = lv_header_id(n)
4065 and s.group_id = G_WIP_GROUP_ID
4066 and s.load_type = 1
4067 and s.substitution_type <> 1
4068 and s.operation_seq_num = lun_op_seq_num(j)
4069 and nvl(s.schedule_seq_num,s.resource_seq_num) < lun_res_seq(j)
4070 and s.schedule_seq_num = v_previous_res1
4071 and rownum=1;
4072 end if ;
4073
4074 select nvl(min(resource_seq_num),-1)
4075 into v_next_res
4076 from wip_job_dtls_interface s
4077 where s.parent_header_id = lv_header_id(n)
4078 and s.group_id = G_WIP_GROUP_ID
4079 and s.substitution_type <> 1
4080 and s.load_type = 1
4081 and s.operation_seq_num = lun_op_seq_num(j)
4082 and nvl(s.schedule_seq_num,s.resource_seq_num) > lun_res_seq(j);
4083
4084 select nvl(min(schedule_seq_num),-1)
4085 into v_next_res1
4086 from wip_job_dtls_interface s
4087 where s.parent_header_id = lv_header_id(n)
4088 and s.group_id = G_WIP_GROUP_ID
4089 and s.substitution_type <> 1
4090 and s.load_type = 1
4091 and s.operation_seq_num = lun_op_seq_num(j)
4092 and nvl(s.schedule_seq_num,s.resource_seq_num) > lun_res_seq(j);
4093
4094 if ((v_next_res1 < v_next_res) and (v_next_res1 <> -1)) then -- Bug 14051795
4095 select nvl(min(resource_seq_num),-1)
4096 into v_next_res
4097 from wip_job_dtls_interface s
4098 where s.parent_header_id = lv_header_id(n)
4099 and s.group_id = G_WIP_GROUP_ID
4100 and s.substitution_type <> 1
4101 and s.load_type = 1
4102 and s.operation_seq_num = lun_op_seq_num(j)
4103 and nvl(s.schedule_seq_num,s.resource_seq_num) > lun_res_seq(j)
4104 and s.schedule_seq_num = v_next_res1;
4105 --and rownum=1;
4106 end if ;
4107
4108
4109 if (v_previous_res = -1 and v_next_res = -1)
4110 then
4111
4112 -- BUG 14074820, uncommenting the computation of v_last_end
4113
4114 Select first_unit_start_date
4115 into v_last_end
4116 from wip_job_dtls_interface
4117 where parent_header_id = lv_header_id(n)
4118 and group_id = G_WIP_GROUP_ID
4119 and load_type = 3
4120 and operation_seq_num = v_next_op;
4121
4122 /*
4123 v_last_start := v_last_end;
4124
4125 Select last_unit_completion_date
4126 into v_frst_start
4127 from wip_job_dtls_interface
4128 where parent_header_id = lv_header_id(n)
4129 and group_id = G_WIP_GROUP_ID
4130 and load_type = 3
4131 and operation_seq_num = v_previous_op;
4132
4133 v_frst_end := v_frst_start;
4134 */ -- Removing the unncessary calculation here 9886513
4135 /* There is no need to calculate first_unit_start_date
4136 and last_unit_completion_date for unscheduled resources
4137 as they are supposed to be started and ended at the same time.
4138 */
4139
4140 v_frst_start := v_last_end; /*Start and end time are same for the rsrc*/
4141 elsif (v_previous_res = -1 and v_next_res <> -1)
4142
4143 then
4144 -- BUG 14074820, uncommenting the computation of v_last_end
4145 Select start_date
4146 into v_last_end
4147 from wip_job_dtls_interface
4148 where parent_header_id = lv_header_id(n)
4149 and group_id = G_WIP_GROUP_ID
4150 and load_type = 1
4151 and operation_seq_num = v_next_op
4152 and resource_Seq_num = v_next_res
4153 and rownum = 1;
4154
4155 /*
4156 v_last_start := v_last_end;
4157
4158 Select start_date
4159 into v_frst_start
4160 from wip_job_dtls_interface
4161 where parent_header_id = lv_header_id(n)
4162 and group_id = G_WIP_GROUP_ID
4163 and load_type = 1
4164 and operation_seq_num = v_previous_op
4165 and resource_Seq_num = v_next_res
4166 and rownum = 1;
4167
4168 v_frst_end := v_frst_start;
4169 */ -- Removing the unncessary calculation here 9886513
4170
4171 v_frst_start := v_last_end; /*Start and end time are same for the rsrc*/
4172 elsif (v_previous_res <> -1 and v_next_res = -1)
4173
4174 then
4175 -- BUG 14074820, uncommenting the computation of v_last_end
4176 Select completion_date
4177 into v_last_end
4178 from wip_job_dtls_interface
4179 where parent_header_id = lv_header_id(n)
4180 and group_id = G_WIP_GROUP_ID
4181 and load_type = 1
4182 and operation_seq_num = v_next_op
4183 and resource_Seq_num = v_previous_res
4184 and rownum = 1;
4185
4186 /*
4187 v_last_start := v_last_end;
4188
4189 Select completion_date
4190 into v_frst_start
4191 from wip_job_dtls_interface
4192 where parent_header_id = lv_header_id(n)
4193 and group_id = G_WIP_GROUP_ID
4194 and load_type = 1
4195 and operation_seq_num = v_previous_op
4196 and resource_Seq_num = v_previous_res
4197 and rownum = 1;
4198
4199 v_frst_end := v_frst_start;
4200 */ -- Removing the unncessary calculation here 9886513
4201
4202 v_frst_start := v_last_end; /*Start and end time are same for the rsrc*/
4203 else
4204 -- BUG 14074820, uncommenting the computation of v_last_end
4205 Select start_date
4206 into v_last_end
4207 from wip_job_dtls_interface
4208 where parent_header_id = lv_header_id(n)
4209 and group_id = G_WIP_GROUP_ID
4210 and load_type = 1
4211 and operation_seq_num = v_next_op
4212 and resource_Seq_num = v_next_res
4213 and rownum = 1;
4214
4215 /*
4216 v_last_start := v_last_end;
4217
4218 Select completion_date
4219 into v_frst_start
4220 from wip_job_dtls_interface
4221 where parent_header_id = lv_header_id(n)
4222 and group_id = G_WIP_GROUP_ID
4223 and load_type = 1
4224 and operation_seq_num = v_previous_op
4225 and resource_Seq_num = v_previous_res
4226 and rownum = 1;
4227
4228 v_frst_end := v_frst_start;
4229 */ -- Removing the unncessary calculation here 9886513
4230
4231 v_frst_start := v_last_end; /*Start and end time are same for the rsrc*/
4232
4233 End if;
4234
4235 EXCEPTION WHEN OTHERS THEN
4236 RAISE;
4237 END;
4238
4239 /*insert record for the resource with schedule = no with start and end time
4240 as that of the opertaion*/
4241 INSERT INTO WIP_JOB_DTLS_INTERFACE
4242 ( last_update_date,
4243 last_updated_by,
4244 last_update_login,
4245 creation_date,
4246 created_by,
4247 group_id,
4248 parent_header_id,
4249 operation_seq_num,
4250 resource_seq_num,
4251 resource_id_old,
4252 resource_id_new,
4253 basis_type,
4254 usage_rate_or_amount,
4255 organization_id,
4256 substitution_type,
4257 load_type,
4258 start_date,
4259 completion_date,
4260 process_phase,
4261 process_status,
4262 scheduled_flag)
4263 SELECT SYSDATE,
4264 FND_GLOBAL.USER_ID,
4265 FND_GLOBAL.USER_ID,
4266 SYSDATE,
4267 FND_GLOBAL.USER_ID,
4268 G_WIP_GROUP_ID,
4269 lv_header_id(n),
4270 lun_op_seq_num(j),
4271 lun_res_seq(j),
4272 lun_resource_id(j),
4273 lun_resource_id(j),
4274 lun_basis_type(j),
4275 lun_usage(j),
4276 lv_organization_id(n),
4277 3,
4278 1,
4279 v_frst_start,
4280 v_last_end,
4281 2,
4282 1,
4283 2 --lv_scheduled_flag(j)
4284 FROM DUAL;
4285
4286 End;
4287
4288
4289 End loop;
4290
4291 ELSE
4292
4293 BEGIN
4294
4295 SELECT wor.operation_seq_num,
4296 wor.schedule_seq_num,
4297 wor.resource_seq_num,
4298 jdi.rowid,
4299 wor.substitute_group_num,
4300 jdi.REPLACEMENT_GROUP_NUM
4301 BULK COLLECT
4302 INTO elsud_op_seq_num,
4303 elsud_sim_res_seq,
4304 elsud_res_seq,
4305 elsud_jdi_rowid,
4306 elsud_sub_grp,
4307 elsud_rep_grp
4308 FROM WIP_OPERATION_RESOURCES wor,
4309 WIP_JOB_DTLS_INTERFACE JDI
4310 WHERE wor.operation_seq_num = jdi.operation_seq_num
4311 and nvl(wor.schedule_seq_num,wor.resource_seq_num) = jdi.schedule_seq_num
4312 -- and wor.resource_seq_num = jdi.resource_seq_num
4313 and nvl(wor.replacement_group_num,0) = nvl(jdi.replacement_group_num,0)
4314 and jdi.resource_id_new = wor.resource_id
4315 and jdi.resource_seq_num = wor.resource_seq_num
4316 -- dsr and jdi.load_type = 1
4317 and jdi.load_type IN (1, LT_RESOURCE_USAGE, RESOURCE_INSTANCES, RESOURCE_INSTANCE_USAGE)
4318 and jdi.parent_header_id = lv_header_id(n)
4319 and JDI.GROUP_ID =G_WIP_GROUP_ID
4320 and wor.wip_entity_id = lv_wip_entity_id(n)
4321 and nvl(wor.repetitive_schedule_id ,-1)= -1
4322 and jdi.parent_seq_num is null
4323 ;
4324
4325 lv_elsud_cnt := SQL%ROWCOUNT;
4326
4327 FOR x IN 1..lv_elsud_cnt
4328
4329 Loop
4330
4331 UPDATE WIP_JOB_DTLS_INTERFACE
4332 SET
4333 -- resource_seq_num= elsud_res_seq(x),
4334 schedule_seq_num = elsud_sim_res_seq(x),
4335 substitute_group_num = elsud_sub_grp(x)
4336 WHERE ROWID= elsud_jdi_rowid(x);
4337
4338
4339 End loop;
4340
4341
4342 Exception
4343 when NO_DATA_FOUND THEN
4344 Null;
4345 When others THEN raise;
4346
4347 END;
4348
4349 BEGIN
4350
4351 select wor.operation_seq_num,
4352 wsor.schedule_seq_num,
4353 wsor.principle_flag,
4354 wor.resource_seq_num,
4355 wor.resource_id,
4356 jdi.parent_header_id,
4357 jdi.organization_id,
4358 jdi.rowid,
4359 wor.substitute_group_num,
4360 jdi.REPLACEMENT_GROUP_NUM,
4361 jdi.start_date,
4362 jdi.completion_date,
4363 jdi.usage_rate_or_amount,
4364 wsor.basis_type
4365 BULK COLLECT INTO lv_res_alt_op_seq_num,
4366 lv_res_alt_schd_seq_num,
4367 lv_res_alt_prin_flag,
4368 lv_res_alt_res_seq_num,
4369 lv_res_alt_res_id,
4370 lv_res_alt_hdr_id,
4371 lv_res_alt_org_id,
4372 lv_res_alt_rowid,
4373 lv_res_alt_sub_grp_num,
4374 lv_res_alt_rep_grp_num,
4375 lv_res_alt_start_date,
4376 lv_res_alt_completion_date,
4377 lv_res_alt_usage_rate,
4378 lv_res_alt_basis_type
4379 from wip_job_dtls_interface jdi,
4380 wip_operation_resources wor,
4381 wip_sub_operation_resources wsor
4382 where not exists(select 1 from wip_operation_resources wor1
4383 where wor1.operation_seq_num = jdi.operation_seq_num
4384 and nvl(wor1.schedule_seq_num,wor1.resource_seq_num) = jdi.schedule_seq_num
4385 -- and wor1.resource_seq_num = jdi.resource_seq_num
4386 and nvl(wor1.replacement_group_num,0) = nvl(jdi.replacement_group_num,0)
4387 and wor1.wip_entity_id = lv_wip_entity_id(n)
4388 and nvl(wor1.repetitive_schedule_id ,-1) = -1 )
4389 and wsor.operation_seq_num = wor.operation_seq_num
4390 and wsor.wip_entity_id = wor.wip_entity_id
4391 and nvl(wsor.repetitive_schedule_id,-1) = nvl(wor.repetitive_schedule_id,-1)
4392 and wor.substitute_group_num = wsor.substitute_group_num
4393 and wsor.wip_entity_id = lv_wip_entity_id(n)
4394 and wsor.resource_id = jdi.resource_id_old
4395 and wsor.operation_seq_num = jdi.operation_seq_num
4396 and nvl(wsor.schedule_seq_num,wsor.resource_seq_num) = jdi.schedule_seq_num
4397 -- and wsor.resource_seq_num = jdi.resource_seq_num
4398 and nvl(wsor.replacement_group_num,0) = nvl(jdi.replacement_group_num,0)
4399 and nvl(wsor.repetitive_schedule_id ,-1)= -1
4400 and jdi.load_type = 1
4401 and jdi.parent_header_id = lv_header_id(n)
4402 and JDI.GROUP_ID =G_WIP_GROUP_ID
4403 and wsor.scheduled_flag <> 2;
4404
4405 lv_res_alt_req_count:= SQL%ROWCOUNT;
4406
4407 FOR j IN 1..lv_res_alt_req_count
4408
4409 Loop
4410 Begin
4411
4412 INSERT INTO WIP_JOB_DTLS_INTERFACE
4413 ( last_update_date,
4414 last_updated_by,
4415 last_update_login,
4416 creation_date,
4417 created_by,
4418 group_id,
4419 parent_header_id,
4420 operation_seq_num,
4421 resource_seq_num,
4422 resource_id_old,
4423 resource_id_new,
4424 replacement_group_num,
4425 substitute_group_num,
4426 start_date,
4427 completion_date,
4428 organization_id,
4429 substitution_type,
4430 load_type,
4431 process_phase,
4432 process_status,
4433 scheduled_flag)
4434 SELECT SYSDATE,
4435 FND_GLOBAL.USER_ID,
4436 FND_GLOBAL.USER_ID,
4437 SYSDATE,
4438 FND_GLOBAL.USER_ID,
4439 G_WIP_GROUP_ID,
4440 lv_res_alt_hdr_id(j),
4441 lv_res_alt_op_seq_num(j),
4442 lv_res_alt_res_seq_num(j),
4443 lv_res_alt_res_id(j),
4444 lv_res_alt_res_id(j),
4445 lv_res_alt_rep_grp_num(j),
4446 lv_res_alt_sub_grp_num(j),
4447 lv_res_alt_start_date(j),
4448 lv_res_alt_completion_date(j),
4449 lv_res_alt_org_id(j),
4450 3,
4451 1,
4452 2,
4453 1,
4454 1 --lv_scheduled_flag(j)
4455 FROM DUAL;
4456
4457 delete from WIP_JOB_DTLS_INTERFACE
4458 WHERE ROWID= lv_res_alt_rowid(j);
4459
4460 Exception
4461 when others then raise;
4462
4463 END;
4464 END LOOP;
4465
4466 Exception
4467 when NO_DATA_FOUND THEN
4468 Null;
4469 When others THEN raise;
4470 END;
4471
4472 end if;
4473
4474 --Bug 3333343
4475 UPDATE WIP_JOB_DTLS_INTERFACE set REPLACEMENT_GROUP_NUM = null
4476 where REPLACEMENT_GROUP_NUM = 0
4477 AND GROUP_ID= G_WIP_GROUP_ID
4478 AND PARENT_HEADER_ID= lv_header_id(n)
4479 AND LOAD_TYPE = LT_RESOURCE;
4480
4481
4482 <<next_c_std_job>> NULL;
4483 END LOOP;
4484
4485 /* Now that we are done processing all Jobs and details, lets go back to the alternate
4486 resource records for all the jobs we processed and generate a resource_Seq_num by getting
4487 the max of existing resource_seq_num from the routing for the assembly and adding one to it.
4488 --old logic this piece of code is now commented out*/
4489 /*
4490 for i in cres_upd
4491 loop
4492 if (nvl(v_old_op,0) <> i.operation_seq_num and nvl(v_old_header,0) <> i.parent_header_id)
4493
4494 then
4495
4496 Select max(bor1.resource_seq_num)
4497 into v_max_resource_Seq
4498 FROM bom_operation_resources bor1,
4499 BOM_OPERATION_SEQUENCES os
4500 WHERE bor1.operation_sequence_id= os.operation_sequence_id
4501 and os.routing_sequence_id= i.common_routing_Sequence_id
4502 and os.operation_seq_num = i.operation_seq_num;
4503
4504 v_old_header := i.parent_header_id;
4505 v_old_op := i.operation_seq_num;
4506 End if;
4507
4508 v_max_resource_seq := v_max_resource_Seq + 1;
4509
4510
4511 update wip_job_dtls_interface
4512 set resource_Seq_num = v_max_resource_Seq
4513 where rowid = i.rowid;
4514 End loop;
4515 */
4516
4517 EXCEPTION
4518 WHEN OTHERS THEN RAISE;
4519
4520 END MODIFY_RESOURCE_REQUIREMENT;
4521
4522 -- dsr: begin
4523 -- commenting the eam code, refer bug# 4524589
4524
4525 /* PROCEDURE LD_EAM_RESCHEDULE_JOBS
4526 ( o_request_id OUT NOCOPY NUMBER)
4527 IS
4528 lv_sqlstmt VARCHAR2(4000);
4529 lv_instance_id NUMBER;
4530 lv_dblink VARCHAR2(128);
4531
4532 lv_result BOOLEAN;
4533
4534 lv_dummy INTEGER;
4535
4536 BEGIN
4537
4538 MSC_UTIL.LOG_MSG( 'LD_EAM_RESCHEDULE_JOBS: 000 ');
4539
4540 select DECODE( A2M_DBLINK,
4541 NULL, ' ',
4542 '@'||A2M_DBLINK),
4543 INSTANCE_ID
4544 into lv_dblink,
4545 lv_instance_id
4546 from MRP_AP_APPS_INSTANCES_ALL
4547 where instance_id = v_instance_id
4548 and instance_code = v_instance_code
4549 and nvl(a2m_dblink,NULL_DBLINK) = nvl(v_dblink,NULL_DBLINK)
4550 and ALLOW_RELEASE_FLAG=1;
4551
4552 MSC_UTIL.LOG_MSG( 'LD_EAM_RESCHEDULE_JOBS: 111 lv_dblink/lv_instance_id '
4553 || lv_dblink
4554 || '/' || lv_instance_id
4555 );
4556
4557 lv_sqlstmt:=
4558 'INSERT INTO EAM_WORK_ORDER_IMPORT'
4559 ||' ( LAST_UPDATE_DATE,'
4560 ||' LAST_UPDATED_BY,'
4561 ||' CREATION_DATE,'
4562 ||' CREATED_BY,'
4563 ||' LAST_UPDATE_LOGIN,'
4564 ||' header_ID,'
4565 ||' GROUP_ID,'
4566 ||' wip_entity_id,'
4567 ||' ORGANIZATION_ID,'
4568 ||' asset_activity_id,'
4569 ||' REQUESTED_START_DATE,'
4570 ||' DUE_DATE,'
4571 ||' FIRM_PLANNED_FLAG,'
4572 ||' SCHEDULED_START_DATE,'
4573 ||' SCHEDULED_COMPLETION_DATE,'
4574 ||' priority,'
4575 ||' STATUS_TYPE,'
4576 ||' WIP_ENTITY_NAME,'
4577 ||' Job_quantity,'
4578 ||' TRANSACTION_TYPE,'
4579 ||' PROCESS_STATUS,'
4580 ||' project_id,'
4581 ||' task_id,'
4582 --||' bom_reference_id,'
4583 --||' routing_reference_id,'
4584 ||' alternate_bom_designator,'
4585 ||' alternate_routing_designator, '
4586 ||' end_item_unit_number,'
4587 ||' schedule_group_id,'
4588 ||' REBUILD_SERIAL_NUMBER )' -- build_sequence )'
4589 ||' SELECT'
4590 ||' SYSDATE,'
4591 ||' FND_GLOBAL.USER_ID,'
4592 ||' DECODE( ORGANIZATION_TYPE,1,SYSDATE,creation_date), '
4593 ||' FND_GLOBAL.USER_ID,'
4594 ||' LAST_UPDATE_LOGIN,'
4595 ||' WIP_ENTITY_ID,'
4596 ||' EAM_WORK_ORDER_IMPORT_S.nextval,'
4597 ||' wip_entity_id,'
4598 ||' ORGANIZATION_ID,'
4599 -- dsr ||' PRIMARY_ITEM_ID,'
4600 ||' DECODE(PRIMARY_ITEM_ID, -1000, NULL, PRIMARY_ITEM_ID),'
4601 ||' FIRST_UNIT_START_DATE,' -- REQUESTED_START_DATE,'
4602 ||' REQUESTED_COMPLETION_DATE,'
4603 ||' firm_planned_flag,'
4604 ||' first_unit_start_date,'
4605 ||' last_unit_completion_date,'
4606 ||' schedule_priority,'
4607 ||' status_type,'
4608 ||' job_name,'
4609 ||' start_quantity,'
4610 ||' 2,' -- G_OPR_UPDATE,'
4611 ||' 1,' -- process_status
4612 ||' PROJECT_ID,'
4613 ||' TASK_ID,'
4614 --||' bom_reference_id,'
4615 --||' routing_reference_id,'
4616 ||' alternate_bom_designator, '
4617 ||' alternate_routing_designator, '
4618 ||' end_item_unit_number, '
4619 ||' schedule_group_id, '
4620 ||' build_sequence '
4621 ||' FROM MSC_WIP_JOB_SCHEDULE_INTERFACE'||lv_dblink
4622 ||' WHERE SR_INSTANCE_ID= :lv_instance_id'
4623 ||' AND GROUP_ID = :G_WIP_GROUP_ID'
4624 ||' AND nvl(CFM_ROUTING_FLAG,0) <> 3 '
4625 ||' AND load_type = 21 ' -- EAM_RESCHEDULE_WORK_RODER
4626 ;
4627
4628 EXECUTE IMMEDIATE lv_sqlstmt USING lv_instance_id,G_WIP_GROUP_ID;
4629
4630 MSC_UTIL.LOG_MSG( 'rows inserted into EAM_WORK_ORDER_IMPORT = '
4631 || SQL%ROWCOUNT);
4632
4633 -- operations
4634
4635 MSC_UTIL.LOG_MSG('operations');
4636 lv_sqlstmt:=
4637 'INSERT INTO EAM_OPERATION_IMPORT'
4638 ||' ( LAST_UPDATE_DATE,'
4639 ||' LAST_UPDATED_BY,'
4640 ||' CREATION_DATE,'
4641 ||' CREATED_BY,'
4642 ||' LAST_UPDATE_LOGIN,'
4643 ||' header_ID,'
4644 ||' GROUP_ID,'
4645 ||' wip_entity_id,'
4646 ||' ORGANIZATION_ID,'
4647 ||' TRANSACTION_TYPE,'
4648 --||' PROCESS_STATUS,'
4649 ||' operation_seq_num,'
4650 ||' OPERATION_SEQUENCE_ID,'
4651 ||' department_id,'
4652 ||' START_DATE,'
4653 ||' COMPLETION_DATE )'
4654 ||' SELECT'
4655 ||' SYSDATE,'
4656 ||' FND_GLOBAL.USER_ID,'
4657 ||' DECODE( ORGANIZATION_TYPE,1,SYSDATE,creation_date), '
4658 ||' FND_GLOBAL.USER_ID,'
4659 ||' LAST_UPDATE_LOGIN,'
4660 ||' WIP_ENTITY_ID,'
4661 ||' EAM_WORK_ORDER_IMPORT_S.nextval,'
4662 ||' wip_entity_id,'
4663 ||' ORGANIZATION_ID,'
4664 ||' 2,' -- G_OPR_UPDATE,','
4665 --||' 1,' -- process_status
4666 ||' operation_seq_num,'
4667 ||' operation_seq_id,'
4668 ||' department_id,'
4669 ||' first_unit_start_date,'
4670 ||' last_unit_completion_date '
4671 ||' FROM MSC_WIP_JOB_DTLS_INTERFACE'||lv_dblink
4672 ||' WHERE SR_INSTANCE_ID= :lv_instance_id'
4673 ||' AND nvl(CFM_ROUTING_FLAG,0) <> 3 '
4674 ||' AND GROUP_ID = :G_WIP_GROUP_ID'
4675 ||' AND nvl(operation_seq_num,-1) <> -1'
4676 ||' AND eam_flag = 1 ' -- SYS_YES
4677 ||' AND load_type = 3 '
4678 ;
4679
4680 MSC_UTIL.LOG_MSG('lv_dblink/lv_instance_id/G_WIP_GROUP_ID = '
4681 || lv_dblink
4682 || '/' || lv_instance_id
4683 || '/' || G_WIP_GROUP_ID
4684 );
4685
4686 EXECUTE IMMEDIATE lv_sqlstmt USING lv_instance_id,G_WIP_GROUP_ID;
4687
4688 MSC_UTIL.LOG_MSG( 'rows inserted into EAM_OPERATION_IMPORT = '
4689 || SQL%ROWCOUNT);
4690
4691 -- operation resource
4692
4693 lv_sqlstmt:=
4694 'INSERT INTO EAM_RESOURCE_IMPORT'
4695 ||' ( header_ID,'
4696 ||' GROUP_ID,'
4697 ||' wip_entity_id,'
4698 ||' ORGANIZATION_ID,'
4699 ||' TRANSACTION_TYPE,'
4700 --||' PROCESS_STATUS,'
4701 ||' operation_seq_num,'
4702 ||' resource_seq_num,'
4703 ||' replacement_group_num,'
4704 ||' resource_id,'
4705 ||' START_DATE,'
4706 ||' completion_date, '
4707 ||' Schedule_Seq_num, '
4708 ||' scheduled_flag, '
4709 ||' basis_type, '
4710 ||' department_id, '
4711 ||' Assigned_Units, '
4712 ||' Firm_flag ) '
4713 ||' SELECT'
4714 ||' WIP_ENTITY_ID,'
4715 ||' EAM_WORK_ORDER_IMPORT_S.nextval,'
4716 ||' wip_entity_id,'
4717 ||' ORGANIZATION_ID,'
4718 ||' 2,' -- G_OPR_UPDATE,','
4719 --||' 1,' -- process_status
4720 ||' operation_seq_num,'
4721 ||' orig_resource_seq_num,'
4722 ||' alternate_num,'
4723 ||' resource_id_new,'
4724 ||' start_date,'
4725 ||' completion_date, '
4726 ||' 999, ' -- calculated for Schedule_Seq_num
4727 ||' 999, ' -- calculated for SUBSTITUTE_GROUP_NUM
4728 ||' scheduled_flag, '
4729 ||' basis_type, '
4730 ||' department_id, '
4731 ||' FIRM_FLAG '
4732 ||' FROM MSC_WIP_JOB_DTLS_INTERFACE'||lv_dblink
4733 ||' WHERE SR_INSTANCE_ID= :lv_instance_id'
4734 ||' AND nvl(CFM_ROUTING_FLAG,0) <> 3 '
4735 ||' AND GROUP_ID = :G_WIP_GROUP_ID'
4736 ||' AND nvl(operation_seq_num,-1) <> -1'
4737 ||' AND eam_flag = 1 ' -- SYS_YES
4738 ||' AND load_type = 1 '
4739 ;
4740
4741 EXECUTE IMMEDIATE lv_sqlstmt USING lv_instance_id,G_WIP_GROUP_ID;
4742
4743 MSC_UTIL.LOG_MSG( 'rows inserted into EAM_RESOURCE_IMPORT = '
4744 || SQL%ROWCOUNT);
4745
4746 -- operation components
4747
4748 lv_sqlstmt:=
4749 'INSERT INTO EAM_MATERIAL_IMPORT'
4750 ||' ( LAST_UPDATE_DATE,'
4751 ||' LAST_UPDATED_BY,'
4752 ||' CREATION_DATE,'
4753 ||' CREATED_BY,'
4754 ||' LAST_UPDATE_LOGIN,'
4755 ||' header_ID,'
4756 ||' GROUP_ID,'
4757 ||' wip_entity_id,'
4758 ||' ORGANIZATION_ID,'
4759 ||' TRANSACTION_TYPE,'
4760 --||' PROCESS_STATUS,'
4761 ||' operation_seq_num,'
4762 ||' inventory_item_id,'
4763 ||' date_required,'
4764 ||' Required_quantity )'
4765 ||' SELECT'
4766 ||' SYSDATE,'
4767 ||' FND_GLOBAL.USER_ID,'
4768 ||' DECODE( ORGANIZATION_TYPE,1,SYSDATE,creation_date), '
4769 ||' FND_GLOBAL.USER_ID,'
4770 ||' LAST_UPDATE_LOGIN,'
4771 ||' WIP_ENTITY_ID,'
4772 ||' EAM_WORK_ORDER_IMPORT_S.nextval,'
4773 ||' wip_entity_id,'
4774 ||' ORGANIZATION_ID,'
4775 ||' 2,' -- G_OPR_UPDATE,','
4776 --||' 1,' -- process_status
4777 ||' operation_seq_num,'
4778 ||' inventory_item_id_old,'
4779 ||' date_required,'
4780 ||' Required_quantity '
4781 ||' FROM MSC_WIP_JOB_DTLS_INTERFACE'||lv_dblink
4782 ||' WHERE SR_INSTANCE_ID= :lv_instance_id'
4783 ||' AND nvl(CFM_ROUTING_FLAG,0) <> 3 '
4784 ||' AND GROUP_ID = :G_WIP_GROUP_ID'
4785 ||' AND nvl(operation_seq_num,-1) <> -1'
4786 ||' AND eam_flag = 1 ' -- SYS_YES
4787 ||' AND load_type = 2 ' -- components
4788 ;
4789
4790 EXECUTE IMMEDIATE lv_sqlstmt USING lv_instance_id,G_WIP_GROUP_ID;
4791
4792 MSC_UTIL.LOG_MSG( 'rows inserted into EAM_MATERIAL_IMPORT = '
4793 || SQL%ROWCOUNT);
4794
4795 -- reschedule resource instance
4796
4797 lv_sqlstmt:=
4798 'INSERT INTO EAM_RESOURCE_INSTANCE_IMPORT'
4799 ||' ( LAST_UPDATE_DATE,'
4800 ||' LAST_UPDATED_BY,'
4801 ||' CREATION_DATE,'
4802 ||' CREATED_BY,'
4803 ||' LAST_UPDATE_LOGIN,'
4804 ||' header_ID,'
4805 ||' GROUP_ID,'
4806 ||' wip_entity_id,'
4807 ||' ORGANIZATION_ID,'
4808 ||' TRANSACTION_TYPE,'
4809 --||' PROCESS_STATUS,'
4810 ||' operation_seq_num,'
4811 ||' resource_seq_num,'
4812 ||' INSTANCE_ID,'
4813 ||' START_DATE,'
4814 ||' completion_date, '
4815 ||' SERIAL_NUMBER ) '
4816 ||' SELECT'
4817 ||' SYSDATE,'
4818 ||' FND_GLOBAL.USER_ID,'
4819 ||' DECODE( ORGANIZATION_TYPE,1,SYSDATE,creation_date), '
4820 ||' FND_GLOBAL.USER_ID,'
4821 ||' LAST_UPDATE_LOGIN,'
4822 ||' WIP_ENTITY_ID,'
4823 ||' EAM_WORK_ORDER_IMPORT_S.nextval,'
4824 ||' wip_entity_id,'
4825 ||' ORGANIZATION_ID,'
4826 ||' 2,' -- G_OPR_UPDATE,','
4827 --||' 1,' -- process_status
4828 ||' operation_seq_num,'
4829 ||' orig_resource_seq_num,'
4830 ||' RESOURCE_INSTANCE_ID,'
4831 ||' start_date,'
4832 ||' completion_date, '
4833 ||' SERIAL_NUMBER '
4834 ||' FROM MSC_WIP_JOB_DTLS_INTERFACE'||lv_dblink
4835 ||' WHERE SR_INSTANCE_ID= :lv_instance_id'
4836 ||' AND nvl(CFM_ROUTING_FLAG,0) <> 3 '
4837 ||' AND GROUP_ID = :G_WIP_GROUP_ID'
4838 ||' AND nvl(operation_seq_num,-1) <> -1'
4839 ||' AND eam_flag = 1 ' -- SYS_YES
4840 ||' AND load_type = 6 ' -- resource instance
4841 ;
4842
4843 EXECUTE IMMEDIATE lv_sqlstmt USING lv_instance_id,G_WIP_GROUP_ID;
4844
4845 MSC_UTIL.LOG_MSG( 'rows inserted into EAM_RESOURCE_INSTANCE_IMPORT = '
4846 || SQL%ROWCOUNT);
4847
4848 -- reschedule resource/instance usage
4849
4850 lv_sqlstmt:=
4851 'INSERT INTO EAM_RESOURCE_USAGE_IMPORT'
4852 ||' ( LAST_UPDATE_DATE,'
4853 ||' LAST_UPDATED_BY,'
4854 ||' CREATION_DATE,'
4855 ||' CREATED_BY,'
4856 ||' LAST_UPDATE_LOGIN,'
4857 ||' header_ID,'
4858 ||' GROUP_ID,'
4859 ||' wip_entity_id,'
4860 ||' ORGANIZATION_ID,'
4861 ||' TRANSACTION_TYPE,'
4862 --||' PROCESS_STATUS,'
4863 ||' operation_seq_num,'
4864 ||' resource_seq_num,'
4865 ||' INSTANCE_ID,'
4866 ||' START_DATE,'
4867 ||' completion_date, '
4868 ||' SERIAL_NUMBER ) '
4869 ||' SELECT'
4870 ||' SYSDATE,'
4871 ||' FND_GLOBAL.USER_ID,'
4872 ||' DECODE( ORGANIZATION_TYPE,1,SYSDATE,creation_date), '
4873 ||' FND_GLOBAL.USER_ID,'
4874 ||' LAST_UPDATE_LOGIN,'
4875 ||' WIP_ENTITY_ID,'
4876 ||' EAM_WORK_ORDER_IMPORT_S.nextval,'
4877 ||' wip_entity_id,'
4878 ||' ORGANIZATION_ID,'
4879 ||' 2,' -- G_OPR_UPDATE,','
4880 --||' 1,' -- process_status
4881 ||' operation_seq_num,'
4882 ||' orig_resource_seq_num,'
4883 ||' RESOURCE_INSTANCE_ID,'
4884 ||' start_date,'
4885 ||' completion_date, '
4886 ||' SERIAL_NUMBER '
4887 ||' FROM MSC_WIP_JOB_DTLS_INTERFACE'||lv_dblink
4888 ||' WHERE SR_INSTANCE_ID= :lv_instance_id'
4889 ||' AND nvl(CFM_ROUTING_FLAG,0) <> 3 '
4890 ||' AND GROUP_ID = :G_WIP_GROUP_ID'
4891 ||' AND nvl(operation_seq_num,-1) <> -1'
4892 ||' AND eam_flag = 1 ' -- SYS_YES
4893 ||' AND load_type IN (4, 7) ' -- resource and instance usage
4894 ;
4895
4896
4897 --Commented out to support OPM integration
4898 --||' AND ORGANIZATION_TYPE = 1 ';
4899
4900 EXECUTE IMMEDIATE lv_sqlstmt USING lv_instance_id,G_WIP_GROUP_ID;
4901
4902 MSC_UTIL.LOG_MSG( 'rows inserted into EAM_RESOURCE_USAGE_IMPORT = '
4903 || SQL%ROWCOUNT);
4904
4905 o_request_id := NULL;
4906
4907 -- Submit EAM RESCHEDULE Request --
4908 BEGIN
4909 SELECT 1
4910 INTO lv_dummy
4911 FROM WIP_JOB_SCHEDULE_INTERFACE
4912 WHERE GROUP_ID= G_WIP_GROUP_ID
4913 AND ROWNUM=1;
4914
4915 MODIFY_EAM_COMP_REQUIREMENT;
4916
4917 MODIFY_EAM_RES_REQUIREMENT;
4918
4919 --set to trigger mode to bypass the 'SAVEPOINT' and 'ROLLBACK' command.
4920 lv_result := FND_REQUEST.SET_MODE(TRUE);
4921
4922
4923 o_request_id := FND_REQUEST.SUBMIT_REQUEST(
4924 'EAM', -- application
4925 'EAMIMPWO', -- program
4926 NULL, -- description
4927 NULL, -- start_time
4928 FALSE, -- sub_request
4929 g_eam_group_id, -- group_id
4930 1, -- validation_level
4931 1); -- print report
4932
4933
4934 EXCEPTION
4935 WHEN NO_DATA_FOUND THEN NULL;
4936 WHEN OTHERS THEN RAISE;
4937 END;
4938
4939 MSC_UTIL.LOG_MSG( 'EAM RESCHEDULE Request submitted = ' || o_request_id);
4940
4941 END LD_EAM_RESCHEDULE_JOBS;
4942 */
4943
4944 /*
4945 PROCEDURE MODIFY_EAM_COMP_REQUIREMENT
4946 IS
4947
4948 Cursor C1 is
4949 Select a.header_id,min(bos.operation_seq_num) new_op_seq
4950 from eam_work_order_import a, -- wip_job_schedule_interface a,
4951 eam_material_import b, -- wip_job_dtls_interface b,
4952 bom_operation_Sequences bos,
4953 bom_operational_routings bor
4954 where a.group_id = b.group_id
4955 and a.group_id = G_WIP_GROUP_ID
4956 -- and a.primary_item_id = bor.assembly_item_id
4957 and a.REBUILD_ITEM_ID = bor.assembly_item_id
4958 and nvl(bor.alternate_routing_Designator,0) = nvl(a.alternate_routing_designator,0)
4959 and bor.common_routing_Sequence_id = bos.routing_Sequence_id
4960 --and b.load_type = 2
4961 --and b.substitution_type = 3
4962 and a.source_code = 'MSC'
4963 and b.operation_seq_num = 1
4964 and ( bos.disable_date IS NULL
4965 OR trunc(bos.disable_date) >= trunc(nvl(a.bom_revision_date
4966 ,a.scheduled_start_date))
4967 )
4968 group by a.header_id;
4969
4970 Cursor C2 is
4971 select sum(b.QUANTITY_PER_ASSEMBLY) qty_per_assy,
4972 sum(b.REQUIRED_QUANTITY) reqd_qty,
4973 b.group_id,
4974 b.wip_entity_id, -- b.parent_header_id,
4975 b.INVENTORY_ITEM_ID, -- b.INVENTORY_ITEM_ID_OLD,
4976 b.ORGANIZATION_ID,
4977 b.OPERATION_SEQ_NUM
4978 from eam_work_order_import a, -- wip_job_schedule_interface a,
4979 eam_material_import b -- wip_job_dtls_interface b
4980 where a.source_code = 'MSC'
4981 and a.group_id = G_WIP_GROUP_ID
4982 and a.group_id = b.group_id
4983 -- and a.header_id = b.parent_header_id
4984 and a.wip_entity_id = b.wip_entity_id
4985 -- and b.load_type = 2
4986 -- and b.substitution_type = 3
4987 -- and b.process_phase = 2
4988 -- and b.process_status = 1
4989 group by b.group_id,
4990 b.wip_entity_id, -- b.parent_header_id,
4991 b.ORGANIZATION_ID,
4992 b.INVENTORY_ITEM_ID, -- b.INVENTORY_ITEM_ID_OLD,
4993 b.OPERATION_SEQ_NUM;
4994
4995 Cursor C3 is
4996 select b.rowid
4997 from eam_work_order_import a, -- wip_job_schedule_interface a,
4998 eam_material_import b -- wip_job_dtls_interface b
4999 where a.source_code = 'MSC'
5000 and a.group_id = G_WIP_GROUP_ID
5001 and a.group_id = b.group_id
5002 -- and a.header_id = b.parent_header_id
5003 and a.wip_entity_id = b.wip_entity_id
5004 -- and b.load_type = 2
5005 -- and b.substitution_type = 3
5006 -- and b.process_phase = 2
5007 -- and b.process_status = 1
5008 and b.rowid not in (select min(c.rowid)
5009 from eam_material_import c -- wip_job_dtls_interface c
5010 where b.group_id = c.group_id
5011 -- and b.parent_header_id = c.parent_header_id
5012 and b.wip_entity_id = c.wip_entity_id
5013 and b.ORGANIZATION_ID = c.ORGANIZATION_ID
5014 -- and b.INVENTORY_ITEM_ID_OLD = c.INVENTORY_ITEM_ID_OLD
5015 and b.INVENTORY_ITEM_ID = c.INVENTORY_ITEM_ID
5016 and b.OPERATION_SEQ_NUM = c.OPERATION_SEQ_NUM
5017 -- and b.load_type = c.load_type
5018 -- and b.substitution_type = c.substitution_type
5019 -- and b.process_phase = c.process_phase
5020 -- and b.process_status = c.process_status
5021 );
5022
5023
5024
5025 Begin
5026
5027
5028 For I in C1
5029
5030 loop
5031 update eam_material_import -- wip_job_dtls_interface
5032 set operation_Seq_num = I.new_op_seq
5033 -- where parent_header_id = I.header_id
5034 where wip_entity_id = I.header_id
5035 and operation_seq_num = 1
5036 -- and load_type = 2
5037 -- and substitution_type = 3
5038 ;
5039
5040 End loop;
5041
5042
5043 For J in C2
5044
5045 loop
5046 update eam_material_import -- wip_job_dtls_interface
5047 set QUANTITY_PER_ASSEMBLY = J.qty_per_assy,
5048 REQUIRED_QUANTITY = J.reqd_qty
5049 where group_id = J.group_id
5050 -- and parent_header_id = J.parent_header_id
5051 and wip_entity_id = J.wip_entity_id
5052 and ORGANIZATION_ID = J.ORGANIZATION_ID
5053 -- and INVENTORY_ITEM_ID_OLD = J.INVENTORY_ITEM_ID_OLD
5054 and INVENTORY_ITEM_ID = J.INVENTORY_ITEM_ID
5055 and OPERATION_SEQ_NUM = J.OPERATION_SEQ_NUM
5056 -- and load_type = 2
5057 -- and substitution_type = 3
5058 -- and process_phase = 2
5059 -- and process_status = 1
5060 ;
5061
5062 End loop;
5063
5064 For K in C3
5065
5066 loop
5067
5068 --jguo
5069 -- delete wip_job_dtls_interface
5070 -- where rowid = K.rowid;
5071
5072 null;
5073
5074 End loop;
5075
5076 End MODIFY_EAM_COMP_REQUIREMENT;
5077 */
5078
5079 /*
5080 PROCEDURE MODIFY_EAM_RES_REQUIREMENT
5081 IS
5082
5083
5084 cursor cres_upd is
5085 select wor.schedule_seq_num
5086 , wor.substitute_group_num
5087 FROM WIP_OPERATION_RESOURCES wor
5088 , EAM_RESOURCE_IMPORT eir
5089 WHERE wor.operation_seq_num = eir.operation_seq_num
5090 and wor.resource_seq_num = eir.resource_seq_num
5091 and nvl(wor.replacement_group_num,0) = nvl(eir.replacement_group_num,0)
5092 and eir.resource_id = wor.resource_id
5093 and eir.GROUP_ID =G_EAM_GROUP_ID
5094 and wor.wip_entity_id =eir.wip_entity_id
5095 and nvl(wor.repetitive_schedule_id ,-1)= -1
5096 ;
5097
5098 l_schedule_seq_num NUMBER;
5099 l_substitute_group_num NUMBER;
5100
5101 BEGIN
5102
5103 OPEN cres_upd;
5104 FETCH cres_upd INTO l_schedule_seq_num, l_substitute_group_num;
5105 CLOSE cres_upd;
5106
5107 Update EAM_RESOURCE_IMPORT eir
5108 SET eir.schedule_seq_num = l_schedule_seq_num
5109 , eir.substitute_group_num = l_substitute_group_num
5110 ;
5111
5112 EXCEPTION
5113 WHEN OTHERS THEN RAISE;
5114
5115 END MODIFY_EAM_RES_REQUIREMENT; */
5116
5117 --dsr: end
5118
5119 END MRP_AP_REL_PLAN_PUB;