[Home] [Help]
PACKAGE BODY: APPS.MSC_M2A_PUSH
Source
1 PACKAGE BODY MSC_M2A_PUSH AS -- body
2 /* $Header: MSCPUSHB.pls 120.7.12010000.2 2008/08/12 12:31:09 arusubra ship $ */
3
4
5 ----- PARAMETERS --------------------------------------------------------
6
7 v_dblink VARCHAR2(128);
8 v_cp_enabled NUMBER;
9 v_distributed_config_flag NUMBER;
10 v_sql_stmt VARCHAR2(9000);
11 v_warning_flag NUMBER := SYS_NO;
12 v_errbuf varchar2(2048);
13 v_retcode number;
14 v_buff varchar2(5000);
15
16 -- For outbound XML (instance_type = 3)
17 v_ins_type PLS_INTEGER;
18
19 --=====================Private Routines===================================
20
21 PROCEDURE LOG_MESSAGE( pBUFF IN VARCHAR2)
22 IS
23 BEGIN
24
25 FND_FILE.PUT_LINE( FND_FILE.LOG, pBUFF);
26 -- dbms_output.put_line(pBUFF);
27
28 END LOG_MESSAGE;
29
30 --================Insert Plan Information==================================
31
32
33 FUNCTION ins_mrp_plan (pINSTANCE_ID IN NUMBER,
34 pDESIGNATOR IN VARCHAR2,
35 pORGANIZATION_ID IN NUMBER,
36 pPLANNER IN VARCHAR2,
37 pCATEGORY_ID IN NUMBER,
38 pITEM_ID IN NUMBER,
39 pSUPPLIER_ID IN NUMBER,
40 pSUPPLIER_SITE_ID IN NUMBER,
41 pHORIZON_START_DATE IN VARCHAR2,
42 pHORIZON_END_DATE IN VARCHAR2)
43 RETURN BOOLEAN
44 IS
45 lv_count number :=0;
46 lv_sql_stmt varchar2(9000):=NULL;
47 lv_previous_plan_start_date DATE;
48 CURSOR c1 IS
49 select plan_id, organization_id, plan_start_date
50 from msc_plans
51 where sr_instance_id = pINSTANCE_ID
52 and compile_designator = pDESIGNATOR;
53
54 BEGIN
55
56 v_buff := 'Loading MRP Plans ..... ';
57 LOG_MESSAGE(v_buff);
58 For c_rec in c1 LOOP
59 lv_sql_stmt :=
60 ' SELECT count(*)'
61 ||' FROM MRP_PLANS'||v_dblink
62 ||' WHERE COMPILE_DESIGNATOR = :pDESIGNATOR '
63 ||' AND ORGANIZATION_ID = :organization_id ' ;
64
65 EXECUTE IMMEDIATE lv_sql_stmt
66 INTO lv_count
67 USING pDESIGNATOR,
68 c_rec.organization_id;
69 lv_sql_stmt := NULL;
70 IF lv_count = 0 then
71
72 lv_sql_stmt :=
73 ' INSERT INTO MRP_PLANS'||v_dblink
74 ||' ( ORGANIZATION_ID , '
75 ||' COMPILE_DESIGNATOR , '
76 ||' LAST_UPDATE_DATE , '
77 ||' LAST_UPDATED_BY , '
78 ||' CREATION_DATE , '
79 ||' CREATED_BY , '
80 ||' LAST_UPDATE_LOGIN , '
81 ||' CURR_SCHEDULE_DESIGNATOR , '
82 ||' CURR_OPERATION_SCHEDULE_TYPE , '
83 ||' CURR_PLAN_TYPE , '
84 ||' CURR_OVERWRITE_OPTION , '
85 ||' CURR_APPEND_PLANNED_ORDERS , '
86 ||' CURR_SCHEDULE_TYPE , '
87 ||' CURR_CUTOFF_DATE , '
88 ||' CURR_PART_INCLUDE_TYPE , '
89 ||' CURR_PLANNING_TIME_FENCE_FLAG , '
90 ||' CURR_DEMAND_TIME_FENCE_FLAG , '
91 ||' CURR_CONSIDER_RESERVATIONS , '
92 ||' CURR_PLAN_SAFETY_STOCK , '
93 ||' CURR_CONSIDER_WIP , '
94 ||' CURR_CONSIDER_PO , '
95 ||' CURR_SNAPSHOT_LOCK , '
96 ||' COMPILE_DEFINITION_DATE , '
97 ||' SCHEDULE_DESIGNATOR , '
98 ||' OPERATION_SCHEDULE_TYPE , '
99 ||' PLAN_TYPE , '
100 ||' OVERWRITE_OPTION , '
101 ||' APPEND_PLANNED_ORDERS , '
102 ||' SCHEDULE_TYPE , '
103 ||' CUTOFF_DATE , '
104 ||' PART_INCLUDE_TYPE , '
105 ||' PLANNING_TIME_FENCE_FLAG , '
106 ||' DEMAND_TIME_FENCE_FLAG , '
107 ||' CONSIDER_RESERVATIONS , '
108 ||' PLAN_SAFETY_STOCK , '
109 ||' CONSIDER_WIP , '
110 ||' CONSIDER_PO , '
111 ||' SNAPSHOT_LOCK , '
112 ||' DATA_START_DATE , '
113 ||' DATA_COMPLETION_DATE , '
114 ||' EXPLOSION_COMPLETION_DATE , '
115 ||' PLAN_START_DATE , '
116 ||' PLAN_COMPLETION_DATE , '
117 ||' DESCRIPTION , '
118 ||' REQUEST_ID , '
119 ||' PROGRAM_APPLICATION_ID , '
120 ||' PROGRAM_ID , '
121 ||' PROGRAM_UPDATE_DATE , '
122 ||' ATTRIBUTE_CATEGORY , '
123 ||' ATTRIBUTE1 , '
124 ||' ATTRIBUTE2 , '
125 ||' ATTRIBUTE3 , '
126 ||' ATTRIBUTE4 , '
127 ||' ATTRIBUTE5 , '
128 ||' ATTRIBUTE6 , '
129 ||' ATTRIBUTE7 , '
130 ||' ATTRIBUTE8 , '
131 ||' ATTRIBUTE9 , '
132 ||' ATTRIBUTE10 , '
133 ||' ATTRIBUTE11 , '
134 ||' ATTRIBUTE12 , '
135 ||' ATTRIBUTE13 , '
136 ||' ATTRIBUTE14 , '
137 ||' ATTRIBUTE15 , '
138 ||' ONLINE_PLANNER_START_DATE , '
139 ||' ONLINE_PLANNER_COMPLETION_DATE , '
140 ||' CURR_FULL_PEGGING , '
141 ||' FULL_PEGGING , '
142 ||' ASSIGNMENT_SET_ID , '
143 ||' CURR_ASSIGNMENT_SET_ID , '
144 ||' ORGANIZATION_SELECTION , '
145 ||' CURR_RESERVATION_LEVEL , '
146 ||' CURR_HARD_PEGGING_LEVEL , '
147 ||' RESERVATION_LEVEL , '
148 ||' HARD_PEGGING_LEVEL ) '
149 ||' SELECT '
150 ||' ORGANIZATION_ID,'
151 ||' COMPILE_DESIGNATOR , '
152 ||' LAST_UPDATE_DATE , '
153 ||' LAST_UPDATED_BY , '
154 ||' CREATION_DATE , '
155 ||' CREATED_BY , '
156 ||' LAST_UPDATE_LOGIN , '
157 ||' CURR_SCHEDULE_DESIGNATOR , '
158 ||' CURR_OPERATION_SCHEDULE_TYPE , '
159 ||' CURR_PLAN_TYPE , '
160 ||' CURR_OVERWRITE_OPTION , '
161 ||' CURR_APPEND_PLANNED_ORDERS , '
162 ||' CURR_SCHEDULE_TYPE , '
163 ||' CURR_CUTOFF_DATE , '
164 ||' CURR_PART_INCLUDE_TYPE , '
165 ||' CURR_PLANNING_TIME_FENCE_FLAG , '
166 ||' CURR_DEMAND_TIME_FENCE_FLAG , '
167 ||' 1,'
168 ||' 1,'
169 ||' 1, '
170 ||' 1,'
171 ||' 1,'
172 ||' SYSDATE, '
173 ||' SCHEDULE_DESIGNATOR , '
174 ||' OPERATION_SCHEDULE_TYPE , '
175 ||' PLAN_TYPE , '
176 ||' OVERWRITE_OPTION , '
177 ||' APPEND_PLANNED_ORDERS , '
178 ||' SCHEDULE_TYPE , '
179 ||' CUTOFF_DATE , '
180 ||' PART_INCLUDE_TYPE , '
181 ||' PLANNING_TIME_FENCE_FLAG , '
182 ||' DEMAND_TIME_FENCE_FLAG , '
183 ||' CONSIDER_RESERVATIONS , '
184 ||' PLAN_SAFETY_STOCK , '
185 ||' CONSIDER_WIP , '
186 ||' CONSIDER_PO , '
187 ||' SNAPSHOT_LOCK , '
188 ||' DATA_START_DATE , '
189 ||' DATA_COMPLETION_DATE , '
190 ||' DATA_COMPLETION_DATE , '
191 ||' PLAN_START_DATE , '
192 ||' PLAN_COMPLETION_DATE , '
193 ||' DESCRIPTION , '
194 ||' REQUEST_ID , '
195 ||' PROGRAM_APPLICATION_ID , '
196 ||' PROGRAM_ID , '
197 ||' PROGRAM_UPDATE_DATE , '
198 ||' ATTRIBUTE_CATEGORY , '
199 ||' ATTRIBUTE1 , '
200 ||' ATTRIBUTE2 , '
201 ||' ATTRIBUTE3 , '
202 ||' ATTRIBUTE4 , '
203 ||' ATTRIBUTE5 , '
204 ||' ATTRIBUTE6 , '
205 ||' ATTRIBUTE7 , '
206 ||' ATTRIBUTE8 , '
207 ||' ATTRIBUTE9 , '
208 ||' ATTRIBUTE10 , '
209 ||' ATTRIBUTE11 , '
210 ||' ATTRIBUTE12 , '
211 ||' ATTRIBUTE13 , '
212 ||' ATTRIBUTE14 , '
213 ||' ATTRIBUTE15 , '
214 ||' ONLINE_PLANNER_START_DATE , '
215 ||' ONLINE_PLANNER_COMPLETION_DATE , '
216 ||' CURR_FULL_PEGGING , '
217 ||' FULL_PEGGING , '
218 ||' ASSIGNMENT_SET_ID , '
219 ||' CURR_ASSIGNMENT_SET_ID , '
220 ||' ORGANIZATION_SELECTION , '
221 ||' CURR_RESERVATION_LEVEL , '
222 ||' CURR_HARD_PEGGING_LEVEL , '
223 ||' RESERVATION_LEVEL , '
224 ||' HARD_PEGGING_LEVEL '
225 ||' FROM MSC_PLANS '
226 ||' WHERE plan_id <> -1 '
227 ||' AND plan_id = :plan_id '
228 ||' AND sr_instance_id = :pINSTANCE_ID '
229 ||' AND organization_id = :organization_id ';
230
231 EXECUTE IMMEDIATE lv_sql_stmt
232 USING c_rec.plan_id, pINSTANCE_ID,
233 c_rec.organization_id ;
234
235
236 v_buff := 'Number of MRP Plans loaded : '||SQL%ROWCOUNT;
237 LOG_MESSAGE(v_buff);
238
239 /* 2208398 - If the plan exists, update the plan completion date and
240 data_completion_date */
241
242 ELSIF lv_count = 1 then
243
244 lv_sql_stmt := ' SELECT plan_start_date'
245 ||' FROM mrp_plans'|| v_dblink
246 ||' WHERE COMPILE_DESIGNATOR = :pDESIGNATOR '
247 ||' AND ORGANIZATION_ID = :organization_id1 ';
248
249 EXECUTE IMMEDIATE lv_sql_stmt INTO lv_previous_plan_start_date USING pDESIGNATOR, c_rec.organization_id;
250
251 IF c_rec.plan_start_date > lv_previous_plan_start_date THEN
252
253 IF (pORGANIZATION_ID IS NOT NULL) OR
254 (pPLANNER IS NOT NULL) OR
255 (pCATEGORY_ID IS NOT NULL) OR
256 (pITEM_ID IS NOT NULL) OR
257 (pSUPPLIER_ID IS NOT NULL) OR
258 (pSUPPLIER_SITE_ID IS NOT NULL) OR
259 (pHORIZON_START_DATE IS NOT NULL) OR
260 (pHORIZON_END_DATE IS NOT NULL) THEN
261
262 FND_MESSAGE.SET_NAME('MSC', 'MSC_DP_INCONSISTENT_DATA');
263 FND_MESSAGE.SET_TOKEN('DESIGNATOR', pDESIGNATOR);
264 FND_MESSAGE.SET_TOKEN('PREVIOUS_PLAN_RUN_DATE', to_char(lv_previous_plan_start_date));
265 FND_MESSAGE.SET_TOKEN('CURRENT_PLAN_RUN_DATE', to_char(c_rec.plan_start_date));
266
267 v_retcode := G_WARNING;
268 LOG_MESSAGE('------------------------------------------------------------------------');
269 LOG_MESSAGE(FND_MESSAGE.GET);
270 LOG_MESSAGE('------------------------------------------------------------------------');
271
272 END IF;
273
274 END IF;
275
276 lv_sql_stmt := 'update mrp_plans'|| v_dblink
277 ||' set (plan_start_date,plan_completion_date,data_completion_date) = '
278 ||' (select plan_start_date,plan_completion_date, data_completion_date '
279 ||' from msc_plans'
280 ||' where plan_id <> -1'
281 ||' and plan_id = :plan_id '
282 ||' AND sr_instance_id = :pINSTANCE_ID '
283 ||' AND organization_id = :organization_id) '
284 ||' WHERE COMPILE_DESIGNATOR = :pDESIGNATOR '
285 ||' AND ORGANIZATION_ID = :organization_id1 ';
286
287 EXECUTE IMMEDIATE lv_sql_stmt
288 USING c_rec.plan_id, pINSTANCE_ID,
289 c_rec.organization_id, pDESIGNATOR,
290 c_rec.organization_id;
291
292 v_buff := 'Number of MRP Plans updated : '||SQL%ROWCOUNT;
293 LOG_MESSAGE(v_buff);
294
295 ELSE
296 v_buff := ' More than one Plan : '||pDESIGNATOR||':'||c_rec.organization_id|| ' exists on source ';
297 LOG_MESSAGE(v_buff);
298
299 END IF;
300
301 END LOOP;
302 COMMIT;
303
304 RETURN TRUE;
305
306 EXCEPTION
307 WHEN OTHERS THEN
308
309 v_retcode := G_ERROR;
310 v_errbuf := SQLERRM;
311 LOG_MESSAGE(SQLERRM);
312 RAISE;
313 RETURN FALSE;
314
315 END; --ins_mrp_plan
316 --====================Insert Plan Organizations===============================
317
318 FUNCTION ins_mrp_plan_org(pINSTANCE_ID NUMBER,
319 pDESIGNATOR VARCHAR2,
320 pORGANIZATION_ID NUMBER)
321 RETURN BOOLEAN
322 IS
323 lv_count number :=0;
324 lv_sql_stmt varchar2(9000):=NULL;
325 lv_planned_org number;
326 lv_plan_level number ;
327 CURSOR c1 IS
328 select organization_id ,plan_id
329 from msc_plans
330 where sr_instance_id = pINSTANCE_ID
331 and compile_designator = pDESIGNATOR;
332 CURSOR c2 (c_plan_id number) IS
333 select organization_id
334 from msc_plan_organizations
335 where sr_instance_id = pINSTANCE_ID
336 and organization_id = nvl(pORGANIZATION_ID,organization_id)
337 and plan_id = c_plan_id;
338
339 BEGIN
340
341 v_buff := 'Loading MRP Plan Organizations ..... ';
342 LOG_MESSAGE(v_buff);
343
344 FOR c_rec in c1 LOOP
345 FOR c_rec1 in c2(c_rec.plan_id) LOOP
346 lv_sql_stmt :=
347 ' SELECT count(*)'
348 ||' FROM MRP_PLAN_ORGANIZATIONS'||v_dblink
349 ||' WHERE COMPILE_DESIGNATOR = :pDESIGNATOR '
350 ||' AND ORGANIZATION_ID = :organization_id'
351 ||' AND PLANNED_ORGANIZATION = :organization_id1 ' ;
352
353 EXECUTE IMMEDIATE lv_sql_stmt
354 INTO lv_count
355 USING pDESIGNATOR,
356 c_rec.organization_id,
357 c_rec1.organization_id;
358
359 lv_sql_stmt := NULL;
360 IF lv_count = 0 then
361 v_sql_stmt :=
362 ' INSERT INTO MRP_PLAN_ORGANIZATIONS'||v_dblink
363 ||' (ORGANIZATION_ID ,'
364 ||' COMPILE_DESIGNATOR ,'
365 ||' PLANNED_ORGANIZATION ,'
366 ||' PLAN_LEVEL ,'
367 ||' LAST_UPDATED_BY ,'
368 ||' LAST_UPDATE_DATE ,'
369 ||' CREATED_BY ,'
370 ||' CREATION_DATE ,'
371 ||' LAST_UPDATE_LOGIN ,'
372 ||' NET_WIP ,'
373 ||' NET_RESERVATIONS ,'
374 ||' NET_PURCHASING ,'
375 ||' PLAN_SAFETY_STOCK ,'
376 ||' REQUEST_ID ,'
377 ||' PROGRAM_APPLICATION_ID ,'
378 ||' PROGRAM_ID ,'
379 ||' PROGRAM_UPDATE_DATE ,'
380 ||' ATTRIBUTE_CATEGORY ,'
381 ||' ATTRIBUTE1 ,'
382 ||' ATTRIBUTE2 ,'
383 ||' ATTRIBUTE3 ,'
384 ||' ATTRIBUTE4 ,'
385 ||' ATTRIBUTE5 ,'
386 ||' ATTRIBUTE6 ,'
387 ||' ATTRIBUTE7 ,'
388 ||' ATTRIBUTE8 ,'
389 ||' ATTRIBUTE9 ,'
390 ||' ATTRIBUTE10 ,'
391 ||' ATTRIBUTE11 ,'
392 ||' ATTRIBUTE12 ,'
393 ||' ATTRIBUTE13 ,'
394 ||' ATTRIBUTE14 ,'
395 ||' ATTRIBUTE15 ) '
396 ||'SELECT '
397 ||' :organization_id ,'
398 ||' :pDESIGNATOR, '
399 ||' ORGANIZATION_ID,'
400 ||' PLAN_LEVEL ,'
401 ||' LAST_UPDATED_BY ,'
402 ||' LAST_UPDATE_DATE ,'
403 ||' CREATED_BY ,'
404 ||' CREATION_DATE ,'
405 ||' LAST_UPDATE_LOGIN ,'
406 ||' NET_WIP ,'
407 ||' NET_RESERVATIONS ,'
408 ||' NET_PURCHASING ,'
409 ||' PLAN_SAFETY_STOCK ,'
410 ||' REQUEST_ID ,'
411 ||' PROGRAM_APPLICATION_ID ,'
412 ||' PROGRAM_ID ,'
413 ||' PROGRAM_UPDATE_DATE ,'
414 ||' ATTRIBUTE_CATEGORY ,'
415 ||' ATTRIBUTE1 ,'
416 ||' ATTRIBUTE2 ,'
417 ||' ATTRIBUTE3 ,'
418 ||' ATTRIBUTE4 ,'
419 ||' ATTRIBUTE5 ,'
420 ||' ATTRIBUTE6 ,'
421 ||' ATTRIBUTE7 ,'
422 ||' ATTRIBUTE8 ,'
423 ||' ATTRIBUTE9 ,'
424 ||' ATTRIBUTE10 ,'
425 ||' ATTRIBUTE11 ,'
426 ||' ATTRIBUTE12 ,'
427 ||' ATTRIBUTE13 ,'
428 ||' ATTRIBUTE14 ,'
429 ||' ATTRIBUTE15 '
430 ||'FROM MSC_PLAN_ORGANIZATIONS '
431 ||'WHERE plan_id = :plan_id '
432 ||'AND sr_instance_id = :pINSTANCE_ID '
433 ||' AND organization_id = :organization_id1 ';
434
435 EXECUTE IMMEDIATE v_sql_stmt
436 USING c_rec.organization_id,
437 pDESIGNATOR,
438 c_rec.plan_id,
439 pINSTANCE_ID ,
440 c_rec1.organization_id;
441
442 v_buff := ' Plan Organizations loaded : '||c_rec1.organization_id||': '||SQL%ROWCOUNT ;
443 LOG_MESSAGE(v_buff);
444
445 ELSE
446
447 v_buff := ' Plan Organization: '||pDESIGNATOR||':'||c_rec1.organization_id|| ' already exists on source ';
448 LOG_MESSAGE(v_buff);
449
450 END IF; -- lv_count is 0
451 END LOOP; -- crec1
452 END LOOP; -- crec
453
454 COMMIT;
455 RETURN TRUE;
456
457 EXCEPTION
458 WHEN OTHERS THEN
459 v_retcode := G_ERROR;
460 v_errbuf := SQLERRM;
461 LOG_MESSAGE(SQLERRM);
462 RAISE;
463 RETURN FALSE;
464
465 END; --ins_mrp_plan_org
466
467 --===================Insert Designator Information=============================
468
469 FUNCTION ins_mrp_designators (pINSTANCE_ID NUMBER,
470 pDESIGNATOR VARCHAR2,
471 pPLAN_TYPE VARCHAR2)
472 RETURN BOOLEAN
473 IS
474 lv_count number :=0;
475 lv_sql_stmt varchar2(9000) :=NULL;
476 lv_pplan_type number :=0;
477 CURSOR c1 IS
478 select organization_id
479 from msc_plans
480 where sr_instance_id = pINSTANCE_ID
481 and compile_designator = pDESIGNATOR;
482 CURSOR c2(c_org_id in number) is
483 SELECT PRODUCTION , ORGANIZATION_ID, DESIGNATOR_TYPE,
484 LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN
485 FROM MSC_DESIGNATORS
486 WHERE designator = pDESIGNATOR
487 AND sr_instance_id = pINSTANCE_ID
488 AND organization_id = c_org_id
489 AND designator_type <> G_MPS_IND;
490 CURSOR c3(c_org_id in number) is
491 SELECT PRODUCTION , ORGANIZATION_ID, DESIGNATOR_TYPE,
492 LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN
493 FROM MSC_DESIGNATORS
494 WHERE designator = pDESIGNATOR
495 AND sr_instance_id = pINSTANCE_ID
496 AND organization_id = c_org_id
497 AND designator_type = G_MPS_IND;
498 BEGIN
499 lv_pplan_type := TO_NUMBER (pPLAN_TYPE);
500
501 IF lv_pplan_type <> G_MPS_IND THEN
502 v_buff := 'Loading Compile Designators..... ';
503 LOG_MESSAGE(v_buff);
504 FOR c_rec in c1 LOOP
505 lv_sql_stmt :=
506 ' SELECT count(*)'
507 ||' FROM MRP_DESIGNATORS'||v_dblink
508 ||' WHERE COMPILE_DESIGNATOR = :pDESIGNATOR '
509 ||' AND ORGANIZATION_ID = :organization_id ';
510
511
512 EXECUTE IMMEDIATE lv_sql_stmt
513 INTO lv_count
514 USING pDESIGNATOR,
515 c_rec.organization_id;
516 v_buff := 'Loading Compile Designators2 ..... '||lv_count;
517 LOG_MESSAGE(v_buff);
518 lv_sql_stmt := NULL;
519
520 IF lv_count = 0 then
521
522 lv_sql_stmt :=
523 ' INSERT INTO MRP_DESIGNATORS'||v_dblink
524 ||' (COMPILE_DESIGNATOR , '
525 ||' ORGANIZATION_ID , '
526 ||' LAST_UPDATE_DATE ,'
527 ||' LAST_UPDATED_BY , '
528 ||' CREATION_DATE , '
529 ||' CREATED_BY , '
530 ||' LAST_UPDATE_LOGIN , '
531 ||' DESCRIPTION , '
532 ||' DISABLE_DATE , '
533 ||' FEEDBACK_FLAG , '
534 ||' REQUEST_ID , '
535 ||' PROGRAM_APPLICATION_ID , '
536 ||' PROGRAM_ID , '
537 ||' PROGRAM_UPDATE_DATE , '
538 ||' ATTRIBUTE_CATEGORY , '
539 ||' ATTRIBUTE1 , '
540 ||' ATTRIBUTE2 , '
541 ||' ATTRIBUTE3 , '
542 ||' ATTRIBUTE4 , '
543 ||' ATTRIBUTE5 , '
544 ||' ATTRIBUTE6 , '
545 ||' ATTRIBUTE7 , '
546 ||' ATTRIBUTE8 , '
547 ||' ATTRIBUTE9 , '
548 ||' ATTRIBUTE10 , '
549 ||' ATTRIBUTE11 , '
550 ||' ATTRIBUTE12 , '
551 ||' ATTRIBUTE13 , '
552 ||' ATTRIBUTE14 , '
553 ||' ATTRIBUTE15 , '
554 ||' ORGANIZATION_SELECTION , '
555 ||' DRP_PLAN , '
556 ||' PRODUCTION )'
557 ||'SELECT '
558 ||' DESIGNATOR , '
559 ||' ORGANIZATION_ID , '
560 ||' LAST_UPDATE_DATE , '
561 ||' LAST_UPDATED_BY , '
562 ||' CREATION_DATE , '
563 ||' CREATED_BY , '
564 ||' LAST_UPDATE_LOGIN , '
565 ||' DESCRIPTION , '
566 ||' DISABLE_DATE , '
567 ||' 1, '
568 ||' REQUEST_ID , '
569 ||' PROGRAM_APPLICATION_ID , '
570 ||' PROGRAM_ID , '
571 ||' PROGRAM_UPDATE_DATE , '
572 ||' ATTRIBUTE_CATEGORY , '
573 ||' ATTRIBUTE1 , '
574 ||' ATTRIBUTE2 , '
575 ||' ATTRIBUTE3 , '
576 ||' ATTRIBUTE4 , '
577 ||' ATTRIBUTE5 , '
578 ||' ATTRIBUTE6 , '
579 ||' ATTRIBUTE7 , '
580 ||' ATTRIBUTE8 , '
581 ||' ATTRIBUTE9 , '
582 ||' ATTRIBUTE10 , '
583 ||' ATTRIBUTE11 , '
584 ||' ATTRIBUTE12 , '
585 ||' ATTRIBUTE13 , '
586 ||' ATTRIBUTE14 , '
587 ||' ATTRIBUTE15 , '
588 ||' ORGANIZATION_SELECTION , '
589 ||' DECODE(DESIGNATOR_TYPE,4,1,2) ,'
590 ||' PRODUCTION '
591 ||' FROM MSC_DESIGNATORS '
592 ||' WHERE designator = :pDESIGNATOR '
593 ||' AND sr_instance_id = :pINSTANCE_ID '
594 ||' AND organization_id = :organization_id '
595 ||' AND designator_type <> '|| G_MPS_IND;
596
597 EXECUTE IMMEDIATE lv_sql_stmt
598 USING pDESIGNATOR,
599 pINSTANCE_ID,
600 c_rec.organization_id;
601
602 v_buff := 'Number of Compile Designators loaded : '||SQL%ROWCOUNT;
603 LOG_MESSAGE(v_buff);
604
605 ELSE
606 FOR c_rec1 in c2 (c_rec.organization_id)
607 LOOP
608 lv_sql_stmt :=
609 ' UPDATE MRP_DESIGNATORS'||v_dblink
610 ||' SET PRODUCTION = :production '
611 ||' , DRP_PLAN = DECODE(:designator_type,4,1,2)'
612 ||' , LAST_UPDATE_DATE = :LAST_UPDATE_DATE'
613 ||' , LAST_UPDATED_BY = :LAST_UPDATED_BY'
614 ||' , LAST_UPDATE_LOGIN = :LAST_UPDATE_LOGIN'
615 ||' WHERE COMPILE_DESIGNATOR = :pDESIGNATOR '
616 ||' AND ORGANIZATION_ID = :organization_id ';
617
618 EXECUTE IMMEDIATE lv_sql_stmt
619 USING c_rec1.production,
620 c_rec1.designator_type,
621 c_rec1.LAST_UPDATE_DATE,
622 c_rec1.LAST_UPDATED_BY,
623 c_rec1.LAST_UPDATE_LOGIN,
624 pDESIGNATOR,
625 c_rec1.organization_id;
626
627 END LOOP;
628 v_buff := ' Compile Designator : '||pDESIGNATOR||':'||c_rec.organization_id|| ' already exists on source ';
629 LOG_MESSAGE(v_buff);
630
631 END IF;
632 END LOOP;
633
634 ELSE
635
636 v_buff := 'Loading Schedule Designators..... ';
637 LOG_MESSAGE(v_buff);
638 FOR c_rec in c1 LOOP
639 lv_sql_stmt :=
640 ' SELECT count(*)'
641 ||' FROM MRP_SCHEDULE_DESIGNATORS'||v_dblink
642 ||' WHERE SCHEDULE_DESIGNATOR = :pDESIGNATOR '
643 ||' AND ORGANIZATION_ID = :organization_id ';
644
645
646 EXECUTE IMMEDIATE lv_sql_stmt
647 INTO lv_count
648 USING pDESIGNATOR,
649 c_rec.organization_id;
650 v_buff := 'Loading Schedule Designators2 ..... '||lv_count;
651 LOG_MESSAGE(v_buff);
652
653 lv_sql_stmt := NULL;
654
655 IF lv_count = 0 then
656
657 lv_sql_stmt :=
658 ' INSERT INTO MRP_SCHEDULE_DESIGNATORS'||v_dblink
659 ||' (SCHEDULE_DESIGNATOR , '
660 ||' ORGANIZATION_ID , '
661 ||' LAST_UPDATE_DATE , '
662 ||' LAST_UPDATED_BY , '
663 ||' CREATION_DATE , '
664 ||' CREATED_BY , '
665 ||' LAST_UPDATE_LOGIN , '
666 ||' DESCRIPTION , '
667 ||' DISABLE_DATE , '
668 ||' MPS_RELIEF , '
669 ||' REQUEST_ID , '
670 ||' PROGRAM_APPLICATION_ID , '
671 ||' PROGRAM_ID , '
672 ||' PROGRAM_UPDATE_DATE , '
673 ||' ATTRIBUTE_CATEGORY , '
674 ||' ATTRIBUTE1 , '
675 ||' ATTRIBUTE2 , '
676 ||' ATTRIBUTE3 , '
677 ||' ATTRIBUTE4 , '
678 ||' ATTRIBUTE5 , '
679 ||' ATTRIBUTE6 , '
680 ||' ATTRIBUTE7 , '
681 ||' ATTRIBUTE8 , '
682 ||' ATTRIBUTE9 , '
683 ||' ATTRIBUTE10 , '
684 ||' ATTRIBUTE11 , '
685 ||' ATTRIBUTE12 , '
686 ||' ATTRIBUTE13 , '
687 ||' ATTRIBUTE14 , '
688 ||' ATTRIBUTE15 , '
689 ||' ORGANIZATION_SELECTION , '
690 ||' SCHEDULE_TYPE , '
691 ||' INVENTORY_ATP_FLAG , '
692 ||' DEMAND_CLASS , '
693 ||' PRODUCTION )'
694 ||'SELECT '
695 ||' DESIGNATOR , '
696 ||' ORGANIZATION_ID , '
697 ||' LAST_UPDATE_DATE , '
698 ||' LAST_UPDATED_BY , '
699 ||' CREATION_DATE , '
700 ||' CREATED_BY , '
701 ||' LAST_UPDATE_LOGIN , '
702 ||' DESCRIPTION , '
703 ||' DISABLE_DATE , '
704 ||' MPS_RELIEF , '
705 ||' REQUEST_ID , '
706 ||' PROGRAM_APPLICATION_ID , '
707 ||' PROGRAM_ID , '
708 ||' PROGRAM_UPDATE_DATE , '
709 ||' ATTRIBUTE_CATEGORY , '
710 ||' ATTRIBUTE1 , '
711 ||' ATTRIBUTE2 , '
712 ||' ATTRIBUTE3 , '
713 ||' ATTRIBUTE4 , '
714 ||' ATTRIBUTE5 , '
715 ||' ATTRIBUTE6 , '
716 ||' ATTRIBUTE7 , '
717 ||' ATTRIBUTE8 , '
718 ||' ATTRIBUTE9 , '
719 ||' ATTRIBUTE10 , '
720 ||' ATTRIBUTE11 , '
721 ||' ATTRIBUTE12 , '
722 ||' ATTRIBUTE13 , '
723 ||' ATTRIBUTE14 , '
724 ||' ATTRIBUTE15 , '
725 ||' ORGANIZATION_SELECTION , '
726 || G_MPS_IND ||' , '
727 ||' INVENTORY_ATP_FLAG , '
728 ||' DEMAND_CLASS , '
729 ||' PRODUCTION '
730 ||' FROM MSC_DESIGNATORS '
731 ||' WHERE designator = :pDESIGNATOR '
732 ||' AND sr_instance_id = :pINSTANCE_ID '
733 ||' AND organization_id = :organization_id '
734 ||' AND designator_type = ' || G_MPS_IND;
735
736 EXECUTE IMMEDIATE lv_sql_stmt
737 USING pDESIGNATOR,
738 pINSTANCE_ID,
739 c_rec.organization_id;
740
741 v_buff := 'Number of Schedule Designators loaded : '||SQL%ROWCOUNT;
742 LOG_MESSAGE(v_buff);
743
744 ELSE
745 FOR c_rec1 in c3(c_rec.organization_id)
746 LOOP
747
748 lv_sql_stmt :=
749 ' UPDATE MRP_SCHEDULE_DESIGNATORS'||v_dblink
750 ||' SET PRODUCTION = :production '
751 ||' , LAST_UPDATE_DATE = :LAST_UPDATE_DATE'
752 ||' , LAST_UPDATED_BY = :LAST_UPDATED_BY'
753 ||' , LAST_UPDATE_LOGIN = :LAST_UPDATE_LOGIN'
754 ||' WHERE SCHEDULE_DESIGNATOR = :pDESIGNATOR '
755 ||' AND ORGANIZATION_ID = :organization_id '
756 ||' AND SCHEDULE_TYPE = ' || G_MPS_IND;
757 EXECUTE IMMEDIATE lv_sql_stmt
758 USING c_rec1.production,
759 c_rec1.LAST_UPDATE_DATE,
760 c_rec1.LAST_UPDATED_BY,
761 c_rec1.LAST_UPDATE_LOGIN,
762 pDESIGNATOR,
763 c_rec1.organization_id;
764
765 END LOOP;
766 v_buff := ' Schdule Designator : '||pDESIGNATOR||':'||c_rec.organization_id|| ' already exists on source ';
767 LOG_MESSAGE(v_buff);
768
769 END IF;
770 END LOOP;
771 END IF;
772
773
774 COMMIT;
775 RETURN TRUE;
776
777 EXCEPTION
778 WHEN OTHERS THEN
779 v_retcode := G_ERROR;
780 v_errbuf := SQLERRM;
781 LOG_MESSAGE(SQLERRM);
782 RAISE;
783 RETURN FALSE;
784
785 END; --ins_mrp_designators
786
787 --=====================End of Private Routines=============================
788
789 --=====================Public Routine===================================
790
791 PROCEDURE PUSH_PLAN_INFO( ERRBUF OUT NOCOPY VARCHAR2,
792 RETCODE OUT NOCOPY NUMBER,
793 pINSTANCE_ID IN NUMBER,
794 pPLAN_TYPE IN VARCHAR2, -- dummy arg
795 pDESIGNATOR IN VARCHAR2,
796 pBUY_ORDERS_ONLY IN NUMBER,
797 pDEMAND IN NUMBER default 1, --for bug 3073566
798 pORGANIZATION_ID IN NUMBER,
799 pPLANNER IN VARCHAR2,
800 pCATEGORY_ID IN NUMBER,
801 pITEM_ID IN NUMBER,
802 pDUMMY2 IN NUMBER,
803 pSUPPLIER_ID IN NUMBER,
804 pDUMMY3 IN NUMBER,
805 pSUPPLIER_SITE_ID IN NUMBER,
806 pHORIZON_START_DATE IN VARCHAR2,
807 pHORIZON_END_DATE IN VARCHAR2)
808 IS
809
810 lv_apps_ver NUMBER;
811 lv_sql_stmt VARCHAR2(20000);
812 lv_sql_stmt1 VARCHAR2(20000);
813 lv_items_stmt VARCHAR2(5000);
814 lv_is_supp_null VARCHAR2(150):=NULL;
815 lv_is_supp_not_null VARCHAR2(150):=NULL;
816 lv_buy_count NUMBER := 0;
817 v_total_make_count NUMBER := 0;
818 v_total_buy_count NUMBER := 0;
819 v_total_buy_count1 NUMBER := 0;
820 v_total_count NUMBER := 0;
821 v_total_mgr_count NUMBER := 0;
822 v_item_count NUMBER := 0;
823 ignore NUMBER := 0;
824 cursor1 NUMBER := 0;
825 lv_sr_tp_id NUMBER;
826 lv_sr_tp_site_id NUMBER;
827 lv_start_date DATE;
828 lv_end_date DATE;
829 lv_LANG VARCHAR2(20) :=USERENV('LANG');
830 lv_organization_id NUMBER;
831 lv_user_id NUMBER;
832 CURSOR c1 IS
833 select plan_id
834 from msc_plans
835 where sr_instance_id = pINSTANCE_ID
836 and compile_designator = pDESIGNATOR;
837 CURSOR c2 (c_plan_id number) IS
838 select organization_id
839 from msc_plan_organizations
840 where sr_instance_id = pINSTANCE_ID
841 and organization_id = nvl(pORGANIZATION_ID,organization_id)
842 and plan_id = c_plan_id;
843 CURSOR c3 (c_plan_id number) IS
844 select count(*) from MSC_SUPPLIES ms
845 where ms.plan_id = c_plan_id
846 and ms.sr_instance_id = pINSTANCE_ID
847 and ms.order_type = 5
848 AND nvl(ms.source_supplier_id,ms.supplier_id) is NOT NULL
849 AND nvl(ms.source_supplier_site_id,ms.supplier_site_id) is NOT NULL;
850
851 A2A_EXCEPTION EXCEPTION; -- for outbound XML
852
853 BEGIN --Main
854
855 LOG_MESSAGE('pINSTANCE_ID : '||pINSTANCE_ID);
856 LOG_MESSAGE('pPLAN_TYPE : '||pPLAN_TYPE);
857 LOG_MESSAGE('pDESIGNATOR : '||pDESIGNATOR);
858 LOG_MESSAGE('pBUY_ORDERS_ONLY : '||pBUY_ORDERS_ONLY);
859 LOG_MESSAGE('pDEMAND : '||pDEMAND);
860 LOG_MESSAGE('pORGANIZATION_ID : '||pORGANIZATION_ID);
861 LOG_MESSAGE('pPLANNER : '||pPLANNER);
862 LOG_MESSAGE('pCATEGORY_ID : '||pCATEGORY_ID);
863 LOG_MESSAGE('pITEM_ID : '||pITEM_ID);
864 LOG_MESSAGE('pSUPPLIER_ID : '||pSUPPLIER_ID);
865 LOG_MESSAGE('pSUPPLIER_SITE_ID : '||pSUPPLIER_SITE_ID);
866 LOG_MESSAGE('pHORIZON_START_DATE : '||pHORIZON_START_DATE);
867 LOG_MESSAGE('pHORIZON_END_DATE : '||pHORIZON_END_DATE);
868 LOG_MESSAGE('------------------------------------------------------------------------');
869
870 lv_apps_ver :=-1;
871 RETCODE := G_SUCCESS;
872 ERRBUF := NULL;
873
874 BEGIN
875 SELECT DECODE( M2A_DBLINK,
876 NULL, NULL_DBLINK,
877 '@'||M2A_DBLINK),
878 DECODE( M2A_DBLINK,
879 NULL, SYS_NO,
880 SYS_YES),
881 APPS_VER,
882 INSTANCE_TYPE -- For outbound XML
883 INTO v_dblink,
884 v_distributed_config_flag,
885 lv_apps_ver,
886 v_ins_type -- For outbound XML
887 FROM MSC_APPS_INSTANCES
888 WHERE INSTANCE_ID= pINSTANCE_ID;
889 EXCEPTION
890
891 WHEN NO_DATA_FOUND THEN
892
893 RETCODE := G_ERROR;
894
895 FND_MESSAGE.SET_NAME('MSC', 'MSC_DP_INVALID_INSTANCE_ID');
896 FND_MESSAGE.SET_TOKEN('INSTANCE_ID', pINSTANCE_ID);
897 ERRBUF:= FND_MESSAGE.GET;
898
899 RETURN;
900
901 WHEN OTHERS THEN
902 RAISE;
903
904 END;
905
906 IF pHORIZON_START_DATE IS NULL THEN
907 lv_start_date := TRUNC(to_date(1, 'j')) ;
908 ELSE
909 lv_start_date := fnd_date.canonical_to_date(pHORIZON_START_DATE);
910 END IF;
911
912 IF pHORIZON_END_DATE IS NULL THEN
913 lv_end_date := TRUNC(to_date(3442447, 'j')) ;
914 ELSE
915 lv_end_date := fnd_date.canonical_to_date(pHORIZON_END_DATE);
916 END IF;
917
918
919 IF pSUPPLIER_ID IS NOT NULL THEN
920 select sr_tp_id into lv_sr_tp_id
921 from msc_trading_partners
922 where sr_instance_id = pINSTANCE_ID
923 and partner_id = pSUPPLIER_ID;
924 END IF;
925
926 IF pSUPPLIER_SITE_ID IS NOT NULL THEN
927 select sr_tp_site_id into lv_sr_tp_site_id
928 from msc_trading_partner_sites
929 where partner_id = pSUPPLIER_ID
930 and partner_site_id = pSUPPLIER_SITE_ID
931 and sr_instance_id = pINSTANCE_ID;
932 END IF;
933
934 -- For Outbound XML
935 IF v_ins_type in (3,5) THEN
936 /*
937 IF pBUY_ORDERS_ONLY <> SYS_YES THEN
938
939 lv_sql_stmt:=
940 ' BEGIN'
941 ||' MSC_A2A_XML_WF.PUSH_PLAN_OUTPUT(p_map_code => ''MSC_PLANSCHDO_OAG71_OUT'',
942 p_compile_designator => :pDESIGNATOR ,
943 p_instance_id => :pINSTANCE_ID ,
944 p_buy_items_only => :pBUY_ORDERS_ONLY );'
945 ||' END;';
946
947 EXECUTE IMMEDIATE lv_sql_stmt USING pDESIGNATOR,pINSTANCE_ID, pBUY_ORDERS_ONLY;
948
949 RAISE A2A_EXCEPTION;
950 */
951
952 FOR c_rec in c1 LOOP
953 lv_buy_count := 0;
954
955 IF pBUY_ORDERS_ONLY = SYS_YES THEN -- send xml only if supplier/ site exists for PO
956 OPEN C3 (c_rec.plan_id);
957 FETCH C3 into lv_buy_count;
958 CLOSE C3;
959
960 ELSE
961 lv_buy_count := 1; -- always send xml
962 END IF;
963
964 IF lv_buy_count > 0 THEN
965 lv_sql_stmt:=
966 ' BEGIN'
967 ||' MSC_A2A_XML_WF.PUSH_PLAN_OUTPUT(p_map_code => ''MSC_PLANSCHDO_OAG71_OUT'',
968 p_compile_designator => :pDESIGNATOR ,
969 p_instance_id => :pINSTANCE_ID ,
970 p_buy_items_only => :pBUY_ORDERS_ONLY );'
971 ||' END;';
972
973 EXECUTE IMMEDIATE lv_sql_stmt USING pDESIGNATOR,pINSTANCE_ID, pBUY_ORDERS_ONLY;
974
975 ELSE
976 fnd_message.set_name ('MSC', 'MSC_BUYORDER_NOT_FOUND');
977 fnd_message.set_token ('PLAN_NAME', pDESIGNATOR);
978 LOG_MESSAGE(fnd_message.get);
979 END IF;
980 END LOOP;
981
982 RAISE A2A_EXCEPTION;
983
984 END IF;
985
986
987
988 IF ins_mrp_plan(pINSTANCE_ID, pDESIGNATOR, pORGANIZATION_ID, pPLANNER, pCATEGORY_ID, pITEM_ID, pSUPPLIER_ID,
989 pSUPPLIER_SITE_ID, pHORIZON_START_DATE, pHORIZON_END_DATE) AND
990 ins_mrp_plan_org (pINSTANCE_ID, pDESIGNATOR, pORGANIZATION_ID) AND
991 --3771736 Added AND ins_mrp_designators one more parameter pPLAN_TYPE
992 ins_mrp_designators(pINSTANCE_ID, pDESIGNATOR, pPLAN_TYPE) THEN
993 --3771736
994
995 RETCODE := v_retcode;
996
997 v_buff := 'Deleting Recommendations and Items and Gross Requirements..... ';
998 LOG_MESSAGE(v_buff);
999
1000 BEGIN
1001 /* Begin Delete Recommendations */
1002
1003 lv_sql_stmt :=
1004 'DELETE FROM MRP_RECOMMENDATIONS'||v_dblink||' MRO'
1005 ||' WHERE MRO.COMPILE_DESIGNATOR = :pDESIGNATOR '
1006 ||' AND MRO.ORGANIZATION_ID IN (SELECT MPOV.PLANNED_ORGANIZATION'
1007 ||' FROM MSC_PLAN_ORGANIZATIONS_V MPOV'
1008 ||' WHERE MPOV.SR_INSTANCE_ID = :pINSTANCE_ID'
1009 ||' AND MPOV.COMPILE_DESIGNATOR = :pDESIGNATOR)';
1010
1011 IF pORGANIZATION_ID IS NOT NULL THEN
1012 lv_sql_stmt := lv_sql_stmt || ' AND MRO.ORGANIZATION_ID = :pORGANIZATION_ID';
1013 END IF;
1014
1015 IF pPLANNER IS NOT NULL THEN
1016
1017 lv_items_stmt :=
1018 ' AND EXISTS (SELECT 1 FROM MTL_SYSTEM_ITEMS'||v_dblink||' MSI'
1019 ||' WHERE MSI.INVENTORY_ITEM_ID = MRO.INVENTORY_ITEM_ID'
1020 ||' AND MSI.ORGANIZATION_ID = MRO.ORGANIZATION_ID'
1021 ||' AND MSI.PLANNER_CODE = :pPLANNER)';
1022
1023 END IF;
1024
1025
1026 IF pITEM_ID IS NOT NULL THEN
1027
1028 IF pPLANNER IS NOT NULL THEN
1029 lv_items_stmt := SUBSTR(lv_items_stmt,1,LENGTH(lv_items_stmt)-1) || ' AND MSI.INVENTORY_ITEM_ID = :pITEM_ID)';
1030 ELSE
1031 lv_items_stmt := ' AND MRO.INVENTORY_ITEM_ID = :pITEM_ID';
1032 END IF;
1033
1034 END IF;
1035
1036 lv_sql_stmt := lv_sql_stmt || lv_items_stmt;
1037
1038 IF pCATEGORY_ID IS NOT NULL THEN
1039 lv_sql_stmt := lv_sql_stmt
1040 ||' AND EXISTS (SELECT 1 FROM MRP_AP_ITEM_CATEGORIES_V'||v_dblink||' MAICV, MRP_AP_CATEGORY_SETS_V'||v_dblink||' MACSV'
1041 ||' WHERE MAICV.INVENTORY_ITEM_ID = MRO.INVENTORY_ITEM_ID AND MAICV.ORGANIZATION_ID ='
1042 ||' MRO.ORGANIZATION_ID AND MAICV.CATEGORY_ID = :pCATEGORY_ID'
1043 ||' AND MAICV.CATEGORY_SET_ID = MACSV.CATEGORY_SET_ID'
1044 ||' AND MAICV.LANGUAGE = MACSV.LANGUAGE'
1045 ||' AND MACSV.LANGUAGE = :pLANG'
1046 ||' AND MACSV.DEFAULT_FLAG = 1)';
1047 END IF;
1048
1049 IF pSUPPLIER_ID IS NOT NULL THEN
1050 lv_sql_stmt := lv_sql_stmt || ' AND MRO.SOURCE_VENDOR_ID = :pSUPPLIER_ID';
1051 END IF;
1052
1053 IF pSUPPLIER_SITE_ID IS NOT NULL THEN
1054 lv_sql_stmt := lv_sql_stmt || ' AND MRO.SOURCE_VENDOR_SITE_ID IN '||
1055 '(SELECT SR_TP_SITE_ID ' ||
1056 'FROM MSC_TP_SITE_ID_LID ' ||
1057 'WHERE SR_INSTANCE_ID = :pINSTANCE_ID '||
1058 'AND PARTNER_TYPE = 1 '||
1059 'AND TP_SITE_ID = :pSUPPLIER_SITE_ID)';
1060 END IF;
1061
1062 lv_sql_stmt := lv_sql_stmt || ' AND trunc(MRO.NEW_SCHEDULE_DATE) BETWEEN (:pHORIZON_START_DATE) and (:pHORIZON_END_DATE)';
1063
1064
1065
1066 cursor1 := dbms_sql.open_cursor;
1067 dbms_sql.parse(cursor1, lv_sql_stmt, dbms_sql.v7);
1068
1069 dbms_sql.bind_variable(cursor1, ':pDESIGNATOR', pDESIGNATOR);
1070 dbms_sql.bind_variable(cursor1, ':pINSTANCE_ID', pINSTANCE_ID);
1071 dbms_sql.bind_variable(cursor1, ':pDESIGNATOR', pDESIGNATOR);
1072
1073 IF pORGANIZATION_ID IS NOT NULL THEN
1074 dbms_sql.bind_variable(cursor1, ':pORGANIZATION_ID', pORGANIZATION_ID);
1075 END IF;
1076
1077 IF pPLANNER IS NOT NULL THEN
1078 dbms_sql.bind_variable(cursor1, ':pPLANNER', pPLANNER);
1079 END IF;
1080
1081 IF pCATEGORY_ID IS NOT NULL THEN
1082 dbms_sql.bind_variable(cursor1, ':pCATEGORY_ID', pCATEGORY_ID);
1083 dbms_sql.bind_variable(cursor1, ':pLANG', lv_LANG);
1084 END IF;
1085
1086 IF pITEM_ID IS NOT NULL THEN
1087 dbms_sql.bind_variable(cursor1, ':pITEM_ID', pITEM_ID);
1088 END IF;
1089
1090 IF pSUPPLIER_ID IS NOT NULL THEN
1091 dbms_sql.bind_variable(cursor1, ':pSUPPLIER_ID', lv_sr_tp_id);
1092 END IF;
1093
1094 IF pSUPPLIER_SITE_ID IS NOT NULL THEN
1095 dbms_sql.bind_variable(cursor1, ':pSUPPLIER_SITE_ID', pSUPPLIER_SITE_ID);
1096 dbms_sql.bind_variable(cursor1, ':pINSTANCE_ID', pINSTANCE_ID);
1097 END IF;
1098
1099 dbms_sql.bind_variable(cursor1, ':pHORIZON_START_DATE', lv_start_date);
1100 dbms_sql.bind_variable(cursor1, ':pHORIZON_END_DATE', lv_end_date);
1101
1102 ignore := dbms_sql.execute(cursor1);
1103 dbms_sql.close_cursor(cursor1);
1104
1105 /* End Delete Recommendations */
1106
1107 /* Begin Delete Items */
1108
1109 lv_sql_stmt := 'DELETE FROM MRP_SYSTEM_ITEMS'||v_dblink||' MSI'
1110 ||' WHERE MSI.COMPILE_DESIGNATOR = :pDESIGNATOR '
1111 ||' AND MSI.ORGANIZATION_ID IN (SELECT MPOV.PLANNED_ORGANIZATION'
1112 ||' FROM MSC_PLAN_ORGANIZATIONS_V MPOV'
1113 ||' WHERE MPOV.SR_INSTANCE_ID = :pINSTANCE_ID'
1114 ||' AND MPOV.COMPILE_DESIGNATOR = :pDESIGNATOR)';
1115
1116
1117 IF pORGANIZATION_ID IS NOT NULL THEN
1118 lv_sql_stmt := lv_sql_stmt || ' AND MSI.ORGANIZATION_ID = :pORGANIZATION_ID';
1119 END IF;
1120
1121 IF pPLANNER IS NOT NULL THEN
1122
1123 lv_items_stmt :=
1124 ' AND EXISTS (SELECT 1 FROM MTL_SYSTEM_ITEMS'||v_dblink||' MSI2'
1125 ||' WHERE MSI2.INVENTORY_ITEM_ID = MSI.INVENTORY_ITEM_ID'
1126 ||' AND MSI2.ORGANIZATION_ID = MSI.ORGANIZATION_ID'
1127 ||' AND MSI2.PLANNER_CODE = :pPLANNER)';
1128
1129 END IF;
1130
1131 IF pITEM_ID IS NOT NULL THEN
1132
1133 IF pPLANNER IS NOT NULL THEN
1134 lv_items_stmt := SUBSTR(lv_items_stmt,1,LENGTH(lv_items_stmt)-1) || ' AND MSI2.INVENTORY_ITEM_ID = :pITEM_ID)';
1135 ELSE
1136 lv_items_stmt := ' AND MSI.INVENTORY_ITEM_ID = :pITEM_ID';
1137 END IF;
1138
1139 END IF;
1140
1141 lv_sql_stmt := lv_sql_stmt || lv_items_stmt;
1142
1143 IF pCATEGORY_ID IS NOT NULL THEN
1144 lv_sql_stmt := lv_sql_stmt
1145 ||' AND EXISTS (SELECT 1 FROM MRP_AP_ITEM_CATEGORIES_V'||v_dblink||' MAICV, MRP_AP_CATEGORY_SETS_V'||v_dblink||' MACSV'
1146 ||' WHERE MAICV.INVENTORY_ITEM_ID = MSI.INVENTORY_ITEM_ID AND MAICV.ORGANIZATION_ID ='
1147 ||' MSI.ORGANIZATION_ID AND MAICV.CATEGORY_ID = :pCATEGORY_ID'
1148 ||' AND MAICV.CATEGORY_SET_ID = MACSV.CATEGORY_SET_ID'
1149 ||' AND MAICV.LANGUAGE = MACSV.LANGUAGE'
1150 ||' AND MACSV.LANGUAGE = :pLANG'
1151 ||' AND MACSV.DEFAULT_FLAG = 1)';
1152 END IF;
1153
1154
1155 cursor1 := dbms_sql.open_cursor;
1156 dbms_sql.parse(cursor1, lv_sql_stmt, dbms_sql.v7);
1157
1158 dbms_sql.bind_variable(cursor1, ':pDESIGNATOR', pDESIGNATOR);
1159 dbms_sql.bind_variable(cursor1, ':pINSTANCE_ID', pINSTANCE_ID);
1160 dbms_sql.bind_variable(cursor1, ':pDESIGNATOR', pDESIGNATOR);
1161
1162 IF pORGANIZATION_ID IS NOT NULL THEN
1163 dbms_sql.bind_variable(cursor1, ':pORGANIZATION_ID', pORGANIZATION_ID);
1164 END IF;
1165
1166 IF pPLANNER IS NOT NULL THEN
1167 dbms_sql.bind_variable(cursor1, ':pPLANNER', pPLANNER);
1168 END IF;
1169
1170 IF pCATEGORY_ID IS NOT NULL THEN
1171 dbms_sql.bind_variable(cursor1, ':pCATEGORY_ID', pCATEGORY_ID);
1172 dbms_sql.bind_variable(cursor1, ':pLANG', lv_LANG);
1173 END IF;
1174
1175 IF pITEM_ID IS NOT NULL THEN
1176 dbms_sql.bind_variable(cursor1, ':pITEM_ID', pITEM_ID);
1177 END IF;
1178
1179 ignore := dbms_sql.execute(cursor1);
1180 dbms_sql.close_cursor(cursor1);
1181
1182 /* End Delete Items */
1183
1184 IF pDEMAND = 1 THEN
1185
1186 /* Begin Delete Gross Requirements */
1187
1188 lv_sql_stmt := 'DELETE FROM MRP_GROSS_REQUIREMENTS'||v_dblink||' MGR'
1189 ||' WHERE MGR.COMPILE_DESIGNATOR = :pDESIGNATOR '
1190 ||' AND MGR.ORGANIZATION_ID IN (SELECT MPOV.PLANNED_ORGANIZATION'
1191 ||' FROM MSC_PLAN_ORGANIZATIONS_V MPOV'
1192 ||' WHERE MPOV.SR_INSTANCE_ID = :pINSTANCE_ID'
1193 ||' AND MPOV.COMPILE_DESIGNATOR = :pDESIGNATOR)';
1194
1195
1196 IF pORGANIZATION_ID IS NOT NULL THEN
1197 lv_sql_stmt := lv_sql_stmt || ' AND MGR.ORGANIZATION_ID = :pORGANIZATION_ID';
1198 END IF;
1199
1200 IF pPLANNER IS NOT NULL THEN
1201
1202 lv_items_stmt :=
1203 ' AND EXISTS (SELECT 1 FROM MTL_SYSTEM_ITEMS'||v_dblink||' MSI'
1204 ||' WHERE MSI.INVENTORY_ITEM_ID = MGR.INVENTORY_ITEM_ID'
1205 ||' AND MSI.ORGANIZATION_ID = MGR.ORGANIZATION_ID'
1206 ||' AND MSI.PLANNER_CODE = :pPLANNER)';
1207
1208 END IF;
1209
1210 IF pITEM_ID IS NOT NULL THEN
1211
1212 IF pPLANNER IS NOT NULL THEN
1213 lv_items_stmt := SUBSTR(lv_items_stmt,1,LENGTH(lv_items_stmt)-1) || ' AND MSI.INVENTORY_ITEM_ID = :pITEM_ID)';
1214 ELSE
1215 lv_items_stmt := ' AND MGR.INVENTORY_ITEM_ID = :pITEM_ID';
1216 END IF;
1217 END IF;
1218
1219 lv_sql_stmt := lv_sql_stmt || lv_items_stmt;
1220
1221 IF pCATEGORY_ID IS NOT NULL THEN
1222 lv_sql_stmt := lv_sql_stmt
1223 ||' AND EXISTS (SELECT 1 FROM MRP_AP_ITEM_CATEGORIES_V'||v_dblink||' MAICV, MRP_AP_CATEGORY_SETS_V'||v_dblink||' MACSV'
1224 ||' WHERE MAICV.INVENTORY_ITEM_ID = MGR.INVENTORY_ITEM_ID AND MAICV.ORGANIZATION_ID ='
1225 ||' MGR.ORGANIZATION_ID AND MAICV.CATEGORY_ID = :pCATEGORY_ID'
1226 ||' AND MAICV.CATEGORY_SET_ID = MACSV.CATEGORY_SET_ID'
1227 ||' AND MAICV.LANGUAGE = MACSV.LANGUAGE'
1228 ||' AND MACSV.LANGUAGE = :pLANG'
1229 ||' AND MACSV.DEFAULT_FLAG = 1)';
1230 END IF;
1231
1232
1233 lv_sql_stmt := lv_sql_stmt ||' AND trunc(MGR.USING_ASSEMBLY_DEMAND_DATE) BETWEEN (:pHORIZON_START_DATE) and (:pHORIZON_END_DATE)';
1234
1235
1236 cursor1 := dbms_sql.open_cursor;
1237 dbms_sql.parse(cursor1, lv_sql_stmt, dbms_sql.v7);
1238
1239 dbms_sql.bind_variable(cursor1, ':pDESIGNATOR', pDESIGNATOR);
1240 dbms_sql.bind_variable(cursor1, ':pINSTANCE_ID', pINSTANCE_ID);
1241 dbms_sql.bind_variable(cursor1, ':pDESIGNATOR', pDESIGNATOR);
1242
1243 IF pORGANIZATION_ID IS NOT NULL THEN
1244 dbms_sql.bind_variable(cursor1, ':pORGANIZATION_ID', pORGANIZATION_ID);
1245 END IF;
1246
1247 IF pPLANNER IS NOT NULL THEN
1248 dbms_sql.bind_variable(cursor1, ':pPLANNER', pPLANNER);
1249 END IF;
1250
1251 IF pCATEGORY_ID IS NOT NULL THEN
1252 dbms_sql.bind_variable(cursor1, ':pCATEGORY_ID', pCATEGORY_ID);
1253 dbms_sql.bind_variable(cursor1, ':pLANG', lv_LANG);
1254 END IF;
1255
1256 IF pITEM_ID IS NOT NULL THEN
1257 dbms_sql.bind_variable(cursor1, ':pITEM_ID', pITEM_ID);
1258 END IF;
1259
1260 dbms_sql.bind_variable(cursor1, ':pHORIZON_START_DATE', lv_start_date);
1261 dbms_sql.bind_variable(cursor1, ':pHORIZON_END_DATE', lv_end_date);
1262
1263 ignore := dbms_sql.execute(cursor1);
1264 dbms_sql.close_cursor(cursor1);
1265
1266
1267 /* End Delete Gross requirements */
1268
1269 END IF; --for bug 3073566
1270
1271 EXCEPTION
1272 WHEN OTHERS THEN
1273 ERRBUF := SQLERRM;
1274 RETCODE := G_WARNING;
1275 LOG_MESSAGE(SQLERRM);
1276 RETURN;
1277 END ;
1278
1279 FOR c_rec in c1 LOOP
1280 BEGIN
1281 -- If the input parameter is Buy Orders only then supplier/
1282 -- supplier site is not null
1283
1284 v_buff := 'Loading Recommendations..... ';
1285 LOG_MESSAGE(v_buff);
1286
1287 ----------------------------------------------------------------------------------------------
1288 --Supplier Site criteria was initially added for performance reasons and it is removed
1289 --for resolving the bug#3396519. Retaining supplier exists condition will not take care of the
1290 --scenario where a sourcing rule is not attached to the plan(supplier will not exist for this
1291 --condition). This check is retained for performace considerations and this has to be removed
1292 --if any customer complains about it.
1293 ----------------------------------------------------------------------------------------------
1294
1295 lv_is_supp_not_null := ' AND nvl(ms.source_supplier_id,ms.supplier_id) is NOT NULL ';
1296 lv_is_supp_null := ' AND nvl(ms.source_supplier_id,ms.supplier_id) is NULL ';
1297
1298
1299 /*This is for Buy Orders, Will always be executed */
1300 /* Bug # 2271832 (new bug for 2101174),
1301 replacing the view mrp_ap_organizations_v with the
1302 table msc_trading_partner_sites. */
1303
1304 lv_sql_stmt:=
1305 'INSERT INTO MRP_RECOMMENDATIONS'||v_dblink
1306 ||'( TRANSACTION_ID , '
1307 ||' LAST_UPDATE_DATE , '
1308 ||' LAST_UPDATED_BY , '
1309 ||' CREATION_DATE , '
1310 ||' CREATED_BY , '
1311 ||' LAST_UPDATE_LOGIN , '
1312 ||' INVENTORY_ITEM_ID , '
1313 ||' ORGANIZATION_ID , '
1314 ||' COMPILE_DESIGNATOR,'
1315 ||' NEW_SCHEDULE_DATE , '
1316 ||' OLD_SCHEDULE_DATE , '
1317 ||' NEW_WIP_START_DATE , '
1318 ||' OLD_WIP_START_DATE , '
1319 ||' DISPOSITION_ID , '
1320 ||' DISPOSITION_STATUS_TYPE , '
1321 ||' ORDER_TYPE , '
1322 ||' VENDOR_ID , '
1323 ||' VENDOR_SITE_ID , '
1324 ||' NEW_ORDER_QUANTITY , '
1325 ||' OLD_ORDER_QUANTITY , '
1326 ||' NEW_ORDER_PLACEMENT_DATE , '
1327 ||' OLD_ORDER_PLACEMENT_DATE , '
1328 ||' FIRM_PLANNED_TYPE , '
1329 ||' NEW_PROCESSING_DAYS , '
1330 ||' IMPLEMENTED_QUANTITY , '
1331 ||' PURCH_LINE_NUM , '
1332 ||' REVISION , '
1333 ||' LAST_UNIT_COMPLETION_DATE , '
1334 ||' FIRST_UNIT_START_DATE , '
1335 ||' LAST_UNIT_START_DATE , '
1336 ||' DAILY_RATE , '
1337 ||' OLD_DOCK_DATE , '
1338 ||' NEW_DOCK_DATE , '
1339 ||' RESCHEDULE_DAYS , '
1340 ||' REQUEST_ID , '
1341 ||' PROGRAM_APPLICATION_ID , '
1342 ||' PROGRAM_ID , '
1343 ||' PROGRAM_UPDATE_DATE , '
1344 ||' QUANTITY_IN_PROCESS , '
1345 ||' FIRM_QUANTITY , '
1346 ||' FIRM_DATE , '
1347 ||' UPDATED , '
1348 ||' STATUS , '
1349 ||' APPLIED , '
1350 ||' IMPLEMENT_DEMAND_CLASS , '
1351 ||' IMPLEMENT_DATE , '
1352 ||' IMPLEMENT_QUANTITY , '
1353 ||' IMPLEMENT_FIRM , '
1354 ||' IMPLEMENT_WIP_CLASS_CODE , '
1355 ||' IMPLEMENT_JOB_NAME , '
1356 ||' IMPLEMENT_DOCK_DATE , '
1357 ||' IMPLEMENT_STATUS_CODE , '
1358 ||' IMPLEMENT_EMPLOYEE_ID , '
1359 ||' IMPLEMENT_UOM_CODE , '
1360 ||' IMPLEMENT_LOCATION_ID , '
1361 ||' RELEASE_STATUS , '
1362 ||' LOAD_TYPE , '
1363 ||' IMPLEMENT_AS , '
1364 ||' DEMAND_CLASS , '
1365 ||' ALTERNATE_BOM_DESIGNATOR , '
1366 ||' ALTERNATE_ROUTING_DESIGNATOR , '
1367 ||' LINE_ID , '
1368 ||' BY_PRODUCT_USING_ASSY_ID , '
1369 ||' IMPLEMENT_SOURCE_ORG_ID , '
1370 ||' IMPLEMENT_VENDOR_ID , '
1371 ||' IMPLEMENT_VENDOR_SITE_ID , '
1372 ||' SOURCE_ORGANIZATION_ID , '
1373 ||' SOURCE_VENDOR_SITE_ID , '
1374 ||' SOURCE_VENDOR_ID , '
1375 ||' NEW_SHIP_DATE , '
1376 ||' PROJECT_ID , '
1377 ||' TASK_ID , '
1378 ||' PLANNING_GROUP , '
1379 ||' IMPLEMENT_PROJECT_ID , '
1380 ||' IMPLEMENT_TASK_ID , '
1381 ||' IMPLEMENT_SCHEDULE_GROUP_ID , '
1382 ||' IMPLEMENT_BUILD_SEQUENCE , '
1383 ||' RELEASE_ERRORS , '
1384 ||' SCHEDULE_COMPRESSION_DAYS , '
1385 ||' NUMBER1 '
1386 ||')'
1387 ||'SELECT /*+ index(ms msc_supplies_n8) leading(ms) */ '
1388 ||' MRP_SCHEDULE_DATES_S.nextval'||v_dblink||' , '
1389 ||' ms.LAST_UPDATE_DATE , '
1390 ||' ms.LAST_UPDATED_BY , '
1391 ||' ms.CREATION_DATE , '
1392 ||' ms.CREATED_BY , '
1393 ||' ms.LAST_UPDATE_LOGIN , '
1394 ||' msi.SR_INVENTORY_ITEM_ID , '
1395 ||' ms.ORGANIZATION_ID , '
1396 ||' :p_DESIGNATOR,'
1397 ||' NEW_SCHEDULE_DATE , '
1398 ||' OLD_SCHEDULE_DATE , '
1399 ||' NEW_WIP_START_DATE , '
1400 ||' OLD_WIP_START_DATE , '
1401 ||' MRP_SCHEDULE_DATES_S.currval'||v_dblink||' , '
1402 ||' DISPOSITION_STATUS_TYPE , '
1403 ||' ORDER_TYPE , '
1404 ||' mtil.sr_tp_id,'
1405 ||' mtsil.sr_tp_site_id,'
1406 ||' NEW_ORDER_QUANTITY , '
1407 ||' OLD_ORDER_QUANTITY , '
1408 ||' NEW_ORDER_PLACEMENT_DATE , '
1409 ||' OLD_ORDER_PLACEMENT_DATE , '
1410 ||' FIRM_PLANNED_TYPE , '
1411 ||' NEW_PROCESSING_DAYS , '
1412 ||' IMPLEMENTED_QUANTITY , '
1413 ||' PURCH_LINE_NUM , '
1414 ||' ms.REVISION , '
1415 ||' LAST_UNIT_COMPLETION_DATE , '
1416 ||' FIRST_UNIT_START_DATE , '
1417 ||' LAST_UNIT_START_DATE , '
1418 ||' DAILY_RATE , '
1419 ||' OLD_DOCK_DATE , '
1420 ||' NEW_DOCK_DATE , '
1421 ||' RESCHEDULE_DAYS , '
1422 ||' ms.REQUEST_ID , '
1423 ||' ms.PROGRAM_APPLICATION_ID , '
1424 ||' ms.PROGRAM_ID , '
1425 ||' ms.PROGRAM_UPDATE_DATE , '
1426 ||' ms.QUANTITY_IN_PROCESS , '
1427 ||' ms.FIRM_QUANTITY , '
1428 ||' ms.FIRM_DATE , '
1429 ||' UPDATED , '
1430 ||' ms.STATUS , '
1431 ||' APPLIED , '
1432 ||' IMPLEMENT_DEMAND_CLASS , '
1433 ||' IMPLEMENT_DATE , '
1434 ||' IMPLEMENT_QUANTITY , '
1435 ||' IMPLEMENT_FIRM , '
1436 ||' IMPLEMENT_WIP_CLASS_CODE , '
1437 ||' IMPLEMENT_JOB_NAME , '
1438 ||' IMPLEMENT_DOCK_DATE , '
1439 ||' IMPLEMENT_STATUS_CODE , '
1440 ||' IMPLEMENT_EMPLOYEE_ID , '
1441 ||' IMPLEMENT_UOM_CODE , '
1442 ||' IMPLEMENT_LOCATION_ID , '
1443 ||' RELEASE_STATUS , '
1444 ||' LOAD_TYPE , '
1445 ||' IMPLEMENT_AS , '
1446 ||' DEMAND_CLASS , '
1447 ||' ALTERNATE_BOM_DESIGNATOR , '
1448 ||' ALTERNATE_ROUTING_DESIGNATOR , '
1449 ||' LINE_ID , '
1450 ||' BY_PRODUCT_USING_ASSY_ID , '
1451 ||' IMPLEMENT_SOURCE_ORG_ID , '
1452 ||' mtil.sr_tp_id,'
1453 ||' mtsil.sr_tp_site_id,'
1454 ||' SOURCE_ORGANIZATION_ID , '
1455 ||' mtsil.sr_tp_site_id,'
1456 ||' mtil.sr_tp_id,'
1457 ||' NEW_SHIP_DATE , '
1458 ||' PROJECT_ID , '
1459 ||' TASK_ID , '
1460 ||' PLANNING_GROUP , '
1461 ||' IMPLEMENT_PROJECT_ID , '
1462 ||' IMPLEMENT_TASK_ID , '
1463 ||' IMPLEMENT_SCHEDULE_GROUP_ID , '
1464 ||' IMPLEMENT_BUILD_SEQUENCE , '
1465 ||' RELEASE_ERRORS , '
1466 ||' SCHEDULE_COMPRESS_DAYS , '
1467 ||' NUMBER1 '
1468 ||' FROM MSC_SUPPLIES ms,'
1469 ||' MSC_TP_ID_LID mtil,'
1470 ||' MSC_TP_SITE_ID_LID mtsil ,'
1471 ||' msc_trading_partners ORG ,'
1472 ||' MSC_SYSTEM_ITEMS msi'
1473 ||' WHERE ms.plan_id = :PLAN_ID'
1474 ||' AND ms.sr_instance_id = :pINSTANCE_ID'
1475 ||' AND msi.organization_id = ms.organization_id'
1476 ||' AND msi.inventory_item_id = ms.inventory_item_id'
1477 ||' AND msi.sr_instance_id = ms.sr_instance_id'
1478 ||' AND msi.plan_id = ms.plan_id'
1479 ||' and trunc(ms.NEW_SCHEDULE_DATE) BETWEEN (:pHORIZON_START_DATE) and (:pHORIZON_END_DATE)'
1480 ||' AND mtil.tp_id = nvl(ms.source_supplier_id,ms.supplier_id)'
1481 ||' AND mtil.partner_type = 1'
1482 ||' AND ORG.SR_TP_ID = ms.organization_id '
1483 ||' AND ORG.partner_type = 3'
1484 ||' AND ORG.sr_instance_id = ms.sr_instance_id'
1485 ||' AND nvl(mtsil.operating_unit, -1) = nvl(ORG.OPERATING_UNIT, -1) '
1486 ||' AND mtil.sr_instance_id = ms.sr_instance_id'
1487 ||' AND mtsil.tp_site_id = nvl(source_supplier_site_id,ms.supplier_site_id)'
1488 ||' AND mtsil.partner_type = 1'
1489 ||' AND mtsil.sr_instance_id = ms.sr_instance_id'
1490 ||' AND nvl(source_supplier_site_id,ms.supplier_site_id) IS NOT NULL'
1491 ||' AND NOT EXISTS (select 1 from msc_system_items msi1 , msc_trading_partners mtp'
1492 ||' where msi1.inventory_item_id = ms.inventory_item_id '
1493 ||' and msi1.organization_id = ms.organization_id '
1494 ||' and msi1.plan_id = ms.plan_id '
1495 ||' AND msi1.sr_instance_id = ms.sr_instance_id '
1496 ||' and nvl(msi1.release_time_fence_code,-1) = 7 '
1497 ||' and mtp.sr_tp_id = msi1.organization_id '
1498 ||' and mtp.sr_instance_id = msi1.sr_instance_id '
1499 ||' and mtp.partner_type=3 '
1500 ||' and (mtp.modeled_supplier_id is not null OR mtp.modeled_supplier_site_id is not null))'
1501 ||' AND ms.order_type = 5 '|| lv_is_supp_not_null;
1502
1503 IF pORGANIZATION_ID IS NOT NULL THEN
1504 lv_sql_stmt := lv_sql_stmt || ' AND ORG.SR_TP_ID = :pORGANIZATION_ID';
1505 END IF;
1506
1507 IF pPLANNER IS NOT NULL THEN
1508 lv_sql_stmt := lv_sql_stmt || ' AND msi.planner_code = :pPLANNER';
1509 END IF;
1510
1511 IF pCATEGORY_ID IS NOT NULL THEN
1512 lv_sql_stmt := lv_sql_stmt || ' and exists (select 1 from msc_item_categories mic, msc_category_sets mcs'
1513 ||' where mic.inventory_item_id = msi.inventory_item_id'
1514 ||' and mic.organization_id = msi.organization_id'
1515 ||' and mic.sr_instance_id = msi.sr_instance_id'
1516 ||' and mic.SR_CATEGORY_ID = :pCATEGORY_ID'
1517 ||' and mic.category_set_id = mcs.category_set_id'
1518 ||' and mcs.sr_instance_id = mic.sr_instance_id'
1519 ||' and mcs.DEFAULT_FLAG = 1)';
1520 END IF;
1521
1522 IF pITEM_ID IS NOT NULL THEN
1523 lv_sql_stmt := lv_sql_stmt || ' AND msi.sr_inventory_item_id = :pITEM_ID';
1524 END IF;
1525
1526 IF pSUPPLIER_ID IS NOT NULL THEN
1527 lv_sql_stmt := lv_sql_stmt || ' AND nvl(ms.source_supplier_id,ms.supplier_id) = :pSUPPLIER_ID';
1528 END IF;
1529
1530 IF pSUPPLIER_SITE_ID IS NOT NULL THEN
1531 lv_sql_stmt := lv_sql_stmt || ' AND nvl(source_supplier_site_id,ms.supplier_site_id) = :pSUPPLIER_SITE_ID';
1532 END IF;
1533
1534
1535
1536 cursor1 := dbms_sql.open_cursor;
1537 dbms_sql.parse(cursor1, lv_sql_stmt, dbms_sql.v7);
1538
1539 dbms_sql.bind_variable(cursor1, ':p_DESIGNATOR', pDESIGNATOR);
1540 dbms_sql.bind_variable(cursor1, ':PLAN_ID', c_rec.plan_id);
1541 dbms_sql.bind_variable(cursor1, ':pINSTANCE_ID', pINSTANCE_ID);
1542 dbms_sql.bind_variable(cursor1, ':pHORIZON_START_DATE', lv_start_date);
1543 dbms_sql.bind_variable(cursor1, ':pHORIZON_END_DATE', lv_end_date);
1544
1545 IF pORGANIZATION_ID IS NOT NULL THEN
1546 dbms_sql.bind_variable(cursor1, ':pORGANIZATION_ID', pORGANIZATION_ID);
1547 END IF;
1548
1549 IF pPLANNER IS NOT NULL THEN
1550 dbms_sql.bind_variable(cursor1, ':pPLANNER', pPLANNER);
1551 END IF;
1552
1553 IF pCATEGORY_ID IS NOT NULL THEN
1554 dbms_sql.bind_variable(cursor1, ':pCATEGORY_ID', pCATEGORY_ID);
1555 END IF;
1556
1557 IF pITEM_ID IS NOT NULL THEN
1558 dbms_sql.bind_variable(cursor1, ':pITEM_ID', pITEM_ID);
1559 END IF;
1560
1561 IF pSUPPLIER_ID IS NOT NULL THEN
1562 dbms_sql.bind_variable(cursor1, ':pSUPPLIER_ID', pSUPPLIER_ID);
1563 END IF;
1564
1565 IF pSUPPLIER_SITE_ID IS NOT NULL THEN
1566 dbms_sql.bind_variable(cursor1, ':pSUPPLIER_SITE_ID', pSUPPLIER_SITE_ID);
1567 END IF;
1568
1569 v_total_buy_count := dbms_sql.execute(cursor1);
1570 dbms_sql.close_cursor(cursor1);
1571
1572 v_buff := 'Number of Buy Recommendations with Supplier and Site loaded : '||v_total_buy_count;
1573 LOG_MESSAGE(v_buff);
1574
1575 lv_sql_stmt:=
1576 'INSERT INTO MRP_RECOMMENDATIONS'||v_dblink
1577 ||'( TRANSACTION_ID , '
1578 ||' LAST_UPDATE_DATE , '
1579 ||' LAST_UPDATED_BY , '
1580 ||' CREATION_DATE , '
1581 ||' CREATED_BY , '
1582 ||' LAST_UPDATE_LOGIN , '
1583 ||' INVENTORY_ITEM_ID , '
1584 ||' ORGANIZATION_ID , '
1585 ||' COMPILE_DESIGNATOR,'
1586 ||' NEW_SCHEDULE_DATE , '
1587 ||' OLD_SCHEDULE_DATE , '
1588 ||' NEW_WIP_START_DATE , '
1589 ||' OLD_WIP_START_DATE , '
1590 ||' DISPOSITION_ID , '
1591 ||' DISPOSITION_STATUS_TYPE , '
1592 ||' ORDER_TYPE , '
1593 ||' VENDOR_ID , '
1594 ||' NEW_ORDER_QUANTITY , '
1595 ||' OLD_ORDER_QUANTITY , '
1596 ||' NEW_ORDER_PLACEMENT_DATE , '
1597 ||' OLD_ORDER_PLACEMENT_DATE , '
1598 ||' FIRM_PLANNED_TYPE , '
1599 ||' NEW_PROCESSING_DAYS , '
1600 ||' IMPLEMENTED_QUANTITY , '
1601 ||' PURCH_LINE_NUM , '
1602 ||' REVISION , '
1603 ||' LAST_UNIT_COMPLETION_DATE , '
1604 ||' FIRST_UNIT_START_DATE , '
1605 ||' LAST_UNIT_START_DATE , '
1606 ||' DAILY_RATE , '
1607 ||' OLD_DOCK_DATE , '
1608 ||' NEW_DOCK_DATE , '
1609 ||' RESCHEDULE_DAYS , '
1610 ||' REQUEST_ID , '
1611 ||' PROGRAM_APPLICATION_ID , '
1612 ||' PROGRAM_ID , '
1613 ||' PROGRAM_UPDATE_DATE , '
1614 ||' QUANTITY_IN_PROCESS , '
1615 ||' FIRM_QUANTITY , '
1616 ||' FIRM_DATE , '
1617 ||' UPDATED , '
1618 ||' STATUS , '
1619 ||' APPLIED , '
1620 ||' IMPLEMENT_DEMAND_CLASS , '
1621 ||' IMPLEMENT_DATE , '
1622 ||' IMPLEMENT_QUANTITY , '
1623 ||' IMPLEMENT_FIRM , '
1624 ||' IMPLEMENT_WIP_CLASS_CODE , '
1625 ||' IMPLEMENT_JOB_NAME , '
1626 ||' IMPLEMENT_DOCK_DATE , '
1627 ||' IMPLEMENT_STATUS_CODE , '
1628 ||' IMPLEMENT_EMPLOYEE_ID , '
1629 ||' IMPLEMENT_UOM_CODE , '
1630 ||' IMPLEMENT_LOCATION_ID , '
1631 ||' RELEASE_STATUS , '
1632 ||' LOAD_TYPE , '
1633 ||' IMPLEMENT_AS , '
1634 ||' DEMAND_CLASS , '
1635 ||' ALTERNATE_BOM_DESIGNATOR , '
1636 ||' ALTERNATE_ROUTING_DESIGNATOR , '
1637 ||' LINE_ID , '
1638 ||' BY_PRODUCT_USING_ASSY_ID , '
1639 ||' IMPLEMENT_SOURCE_ORG_ID , '
1640 ||' IMPLEMENT_VENDOR_ID , '
1641 ||' IMPLEMENT_VENDOR_SITE_ID , '
1642 ||' SOURCE_ORGANIZATION_ID , '
1643 ||' SOURCE_VENDOR_SITE_ID , '
1644 ||' SOURCE_VENDOR_ID , '
1645 ||' NEW_SHIP_DATE , '
1646 ||' PROJECT_ID , '
1647 ||' TASK_ID , '
1648 ||' PLANNING_GROUP , '
1649 ||' IMPLEMENT_PROJECT_ID , '
1650 ||' IMPLEMENT_TASK_ID , '
1651 ||' IMPLEMENT_SCHEDULE_GROUP_ID , '
1652 ||' IMPLEMENT_BUILD_SEQUENCE , '
1653 ||' RELEASE_ERRORS , '
1654 ||' SCHEDULE_COMPRESSION_DAYS , '
1655 ||' NUMBER1 '
1656 ||')'
1657 ||'SELECT /*+ index(ms msc_supplies_n8) leading(ms) */'
1658 ||' MRP_SCHEDULE_DATES_S.nextval'||v_dblink||' , '
1659 ||' ms.LAST_UPDATE_DATE , '
1660 ||' ms.LAST_UPDATED_BY , '
1661 ||' ms.CREATION_DATE , '
1662 ||' ms.CREATED_BY , '
1663 ||' ms.LAST_UPDATE_LOGIN , '
1664 ||' msi.SR_INVENTORY_ITEM_ID , '
1665 ||' ms.ORGANIZATION_ID , '
1666 ||' :p_DESIGNATOR,'
1667 ||' NEW_SCHEDULE_DATE , '
1668 ||' OLD_SCHEDULE_DATE , '
1669 ||' NEW_WIP_START_DATE , '
1670 ||' OLD_WIP_START_DATE , '
1671 ||' MRP_SCHEDULE_DATES_S.currval'||v_dblink||' , '
1672 ||' DISPOSITION_STATUS_TYPE , '
1673 ||' ORDER_TYPE , '
1674 ||' mtil.sr_tp_id,'
1675 ||' NEW_ORDER_QUANTITY , '
1676 ||' OLD_ORDER_QUANTITY , '
1677 ||' NEW_ORDER_PLACEMENT_DATE , '
1678 ||' OLD_ORDER_PLACEMENT_DATE , '
1679 ||' FIRM_PLANNED_TYPE , '
1680 ||' NEW_PROCESSING_DAYS , '
1681 ||' IMPLEMENTED_QUANTITY , '
1682 ||' PURCH_LINE_NUM , '
1683 ||' ms.REVISION , '
1684 ||' LAST_UNIT_COMPLETION_DATE , '
1685 ||' FIRST_UNIT_START_DATE , '
1686 ||' LAST_UNIT_START_DATE , '
1687 ||' DAILY_RATE , '
1688 ||' OLD_DOCK_DATE , '
1689 ||' NEW_DOCK_DATE , '
1690 ||' RESCHEDULE_DAYS , '
1691 ||' ms.REQUEST_ID , '
1692 ||' ms.PROGRAM_APPLICATION_ID , '
1693 ||' ms.PROGRAM_ID , '
1694 ||' ms.PROGRAM_UPDATE_DATE , '
1695 ||' ms.QUANTITY_IN_PROCESS , '
1696 ||' ms.FIRM_QUANTITY , '
1697 ||' ms.FIRM_DATE , '
1698 ||' UPDATED , '
1699 ||' ms.STATUS , '
1700 ||' APPLIED , '
1701 ||' IMPLEMENT_DEMAND_CLASS , '
1702 ||' IMPLEMENT_DATE , '
1703 ||' IMPLEMENT_QUANTITY , '
1704 ||' IMPLEMENT_FIRM , '
1705 ||' IMPLEMENT_WIP_CLASS_CODE , '
1706 ||' IMPLEMENT_JOB_NAME , '
1707 ||' IMPLEMENT_DOCK_DATE , '
1708 ||' IMPLEMENT_STATUS_CODE , '
1709 ||' IMPLEMENT_EMPLOYEE_ID , '
1710 ||' IMPLEMENT_UOM_CODE , '
1711 ||' IMPLEMENT_LOCATION_ID , '
1712 ||' RELEASE_STATUS , '
1713 ||' LOAD_TYPE , '
1714 ||' IMPLEMENT_AS , '
1715 ||' DEMAND_CLASS , '
1716 ||' ALTERNATE_BOM_DESIGNATOR , '
1717 ||' ALTERNATE_ROUTING_DESIGNATOR , '
1718 ||' LINE_ID , '
1719 ||' BY_PRODUCT_USING_ASSY_ID , '
1720 ||' IMPLEMENT_SOURCE_ORG_ID , '
1721 ||' mtil.sr_tp_id,'
1722 ||' null,'
1723 ||' SOURCE_ORGANIZATION_ID , '
1724 ||' null,'
1725 ||' mtil.sr_tp_id,'
1726 ||' NEW_SHIP_DATE , '
1727 ||' PROJECT_ID , '
1728 ||' TASK_ID , '
1729 ||' PLANNING_GROUP , '
1730 ||' IMPLEMENT_PROJECT_ID , '
1731 ||' IMPLEMENT_TASK_ID , '
1732 ||' IMPLEMENT_SCHEDULE_GROUP_ID , '
1733 ||' IMPLEMENT_BUILD_SEQUENCE , '
1734 ||' RELEASE_ERRORS , '
1735 ||' SCHEDULE_COMPRESS_DAYS , '
1736 ||' NUMBER1 '
1737 ||' FROM MSC_SUPPLIES ms,'
1738 ||' MSC_TP_ID_LID mtil,'
1739 ||' MSC_SYSTEM_ITEMS msi'
1740 ||' WHERE ms.plan_id = :PLAN_ID'
1741 ||' AND ms.sr_instance_id = :pINSTANCE_ID'
1742 ||' AND msi.organization_id = ms.organization_id'
1743 ||' AND msi.inventory_item_id = ms.inventory_item_id'
1744 ||' AND msi.sr_instance_id = ms.sr_instance_id'
1745 ||' AND msi.plan_id = ms.plan_id'
1746 ||' and trunc(ms.NEW_SCHEDULE_DATE) BETWEEN (:pHORIZON_START_DATE) and (:pHORIZON_END_DATE)'
1747 ||' AND mtil.tp_id = nvl(ms.source_supplier_id,ms.supplier_id)'
1748 ||' AND mtil.partner_type = 1'
1749 ||' AND mtil.sr_instance_id = ms.sr_instance_id'
1750 ||' AND nvl(source_supplier_site_id,ms.supplier_site_id) IS NULL '
1751 ||' AND NOT EXISTS (select 1 from msc_system_items msi1 , msc_trading_partners mtp'
1752 ||' where msi1.inventory_item_id = ms.inventory_item_id '
1753 ||' and msi1.organization_id = ms.organization_id '
1754 ||' and msi1.plan_id = ms.plan_id '
1755 ||' AND msi1.sr_instance_id = ms.sr_instance_id '
1756 ||' and nvl(msi1.release_time_fence_code,-1) = 7 '
1757 ||' and mtp.sr_tp_id = msi1.organization_id '
1758 ||' and mtp.sr_instance_id = msi1.sr_instance_id '
1759 ||' and mtp.partner_type=3 '
1760 ||' and (mtp.modeled_supplier_id is not null OR mtp.modeled_supplier_site_id is not null))'
1761 ||' AND ms.order_type = 5 '|| lv_is_supp_not_null;
1762
1763 IF pORGANIZATION_ID IS NOT NULL THEN
1764 lv_sql_stmt := lv_sql_stmt || ' AND ms.organization_id = :pORGANIZATION_ID';
1765 END IF;
1766
1767 IF pPLANNER IS NOT NULL THEN
1768 lv_sql_stmt := lv_sql_stmt || ' AND msi.planner_code = :pPLANNER';
1769 END IF;
1770
1771 IF pCATEGORY_ID IS NOT NULL THEN
1772 lv_sql_stmt := lv_sql_stmt || ' and exists (select 1 from msc_item_categories mic, msc_category_sets mcs'
1773 ||' where mic.inventory_item_id = msi.inventory_item_id'
1774 ||' and mic.organization_id = msi.organization_id'
1775 ||' and mic.sr_instance_id = msi.sr_instance_id'
1776 ||' and mic.SR_CATEGORY_ID = :pCATEGORY_ID'
1777 ||' and mic.category_set_id = mcs.category_set_id'
1778 ||' and mcs.sr_instance_id = mic.sr_instance_id'
1779 ||' and mcs.DEFAULT_FLAG = 1)';
1780 END IF;
1781
1782 IF pITEM_ID IS NOT NULL THEN
1783 lv_sql_stmt := lv_sql_stmt || ' AND msi.sr_inventory_item_id = :pITEM_ID';
1784 END IF;
1785
1786 IF pSUPPLIER_ID IS NOT NULL THEN
1787 lv_sql_stmt := lv_sql_stmt || ' AND nvl(ms.source_supplier_id,ms.supplier_id) = :pSUPPLIER_ID';
1788 END IF;
1789
1790 cursor1 := dbms_sql.open_cursor;
1791 dbms_sql.parse(cursor1, lv_sql_stmt, dbms_sql.v7);
1792
1793 dbms_sql.bind_variable(cursor1, ':p_DESIGNATOR', pDESIGNATOR);
1794 dbms_sql.bind_variable(cursor1, ':PLAN_ID', c_rec.plan_id);
1795 dbms_sql.bind_variable(cursor1, ':pINSTANCE_ID', pINSTANCE_ID);
1796 dbms_sql.bind_variable(cursor1, ':pHORIZON_START_DATE', lv_start_date);
1797 dbms_sql.bind_variable(cursor1, ':pHORIZON_END_DATE', lv_end_date);
1798
1799 IF pORGANIZATION_ID IS NOT NULL THEN
1800 dbms_sql.bind_variable(cursor1, ':pORGANIZATION_ID', pORGANIZATION_ID);
1801 END IF;
1802
1803 IF pPLANNER IS NOT NULL THEN
1804 dbms_sql.bind_variable(cursor1, ':pPLANNER', pPLANNER);
1805 END IF;
1806
1807 IF pCATEGORY_ID IS NOT NULL THEN
1808 dbms_sql.bind_variable(cursor1, ':pCATEGORY_ID', pCATEGORY_ID);
1809 END IF;
1810
1811 IF pITEM_ID IS NOT NULL THEN
1812 dbms_sql.bind_variable(cursor1, ':pITEM_ID', pITEM_ID);
1813 END IF;
1814
1815 IF pSUPPLIER_ID IS NOT NULL THEN
1816 dbms_sql.bind_variable(cursor1, ':pSUPPLIER_ID', pSUPPLIER_ID);
1817 END IF;
1818
1819 v_total_buy_count1 := dbms_sql.execute(cursor1);
1820 dbms_sql.close_cursor(cursor1);
1821
1822 v_buff := 'Number of Buy Recommendations with No Site loaded : '||v_total_buy_count1;
1823 LOG_MESSAGE(v_buff);
1824
1825 v_total_buy_count := v_total_buy_count + v_total_buy_count1;
1826
1827 /* Make Orders */
1828
1829
1830 /*This is for Make Orders, Will be executed only if the input program parameter
1831 (Which is a question to the user , "Buy Orders Only" ? is set to NO.
1832 In this case we are not even joining to MSC_TP_SITE_ID_LID and MSC_TP_SITE_ID
1833 and MSC_TP_SITE_ID_LID anymore. We are populating the vendor and vendor_site_id's as
1834 NULL */
1835
1836
1837 IF pBUY_ORDERS_ONLY <> SYS_YES THEN
1838
1839 lv_sql_stmt1:=
1840 'INSERT INTO MRP_RECOMMENDATIONS'||v_dblink
1841 ||'( TRANSACTION_ID , '
1842 ||' LAST_UPDATE_DATE , '
1843 ||' LAST_UPDATED_BY , '
1844 ||' CREATION_DATE , '
1845 ||' CREATED_BY , '
1846 ||' LAST_UPDATE_LOGIN , '
1847 ||' INVENTORY_ITEM_ID , '
1848 ||' ORGANIZATION_ID , '
1849 ||' COMPILE_DESIGNATOR,'
1850 ||' NEW_SCHEDULE_DATE , '
1851 ||' OLD_SCHEDULE_DATE , '
1852 ||' NEW_WIP_START_DATE , '
1853 ||' OLD_WIP_START_DATE , '
1854 ||' DISPOSITION_ID , '
1855 ||' DISPOSITION_STATUS_TYPE , '
1856 ||' ORDER_TYPE , '
1857 ||' VENDOR_ID , '
1858 ||' NEW_ORDER_QUANTITY , '
1859 ||' OLD_ORDER_QUANTITY , '
1860 ||' NEW_ORDER_PLACEMENT_DATE , '
1861 ||' OLD_ORDER_PLACEMENT_DATE , '
1862 ||' FIRM_PLANNED_TYPE , '
1863 ||' NEW_PROCESSING_DAYS , '
1864 ||' IMPLEMENTED_QUANTITY , '
1865 ||' PURCH_LINE_NUM , '
1866 ||' REVISION , '
1867 ||' LAST_UNIT_COMPLETION_DATE , '
1868 ||' FIRST_UNIT_START_DATE , '
1869 ||' LAST_UNIT_START_DATE , '
1870 ||' DAILY_RATE , '
1871 ||' OLD_DOCK_DATE , '
1872 ||' NEW_DOCK_DATE , '
1873 ||' RESCHEDULE_DAYS , '
1874 ||' REQUEST_ID , '
1875 ||' PROGRAM_APPLICATION_ID , '
1876 ||' PROGRAM_ID , '
1877 ||' PROGRAM_UPDATE_DATE , '
1878 ||' QUANTITY_IN_PROCESS , '
1879 ||' FIRM_QUANTITY , '
1880 ||' FIRM_DATE , '
1881 ||' UPDATED , '
1882 ||' STATUS , '
1883 ||' APPLIED , '
1884 ||' IMPLEMENT_DEMAND_CLASS , '
1885 ||' IMPLEMENT_DATE , '
1886 ||' IMPLEMENT_QUANTITY , '
1887 ||' IMPLEMENT_FIRM , '
1888 ||' IMPLEMENT_WIP_CLASS_CODE , '
1889 ||' IMPLEMENT_JOB_NAME , '
1890 ||' IMPLEMENT_DOCK_DATE , '
1891 ||' IMPLEMENT_STATUS_CODE , '
1892 ||' IMPLEMENT_EMPLOYEE_ID , '
1893 ||' IMPLEMENT_UOM_CODE , '
1894 ||' IMPLEMENT_LOCATION_ID , '
1895 ||' RELEASE_STATUS , '
1896 ||' LOAD_TYPE , '
1897 ||' IMPLEMENT_AS , '
1898 ||' DEMAND_CLASS , '
1899 ||' ALTERNATE_BOM_DESIGNATOR , '
1900 ||' ALTERNATE_ROUTING_DESIGNATOR , '
1901 ||' LINE_ID , '
1902 ||' BY_PRODUCT_USING_ASSY_ID , '
1903 ||' IMPLEMENT_SOURCE_ORG_ID , '
1904 ||' IMPLEMENT_VENDOR_ID , '
1905 ||' IMPLEMENT_VENDOR_SITE_ID , '
1906 ||' SOURCE_ORGANIZATION_ID , '
1907 ||' SOURCE_VENDOR_SITE_ID , '
1908 ||' SOURCE_VENDOR_ID , '
1909 ||' NEW_SHIP_DATE , '
1910 ||' PROJECT_ID , '
1911 ||' TASK_ID , '
1912 ||' PLANNING_GROUP , '
1913 ||' IMPLEMENT_PROJECT_ID , '
1914 ||' IMPLEMENT_TASK_ID , '
1915 ||' IMPLEMENT_SCHEDULE_GROUP_ID , '
1916 ||' IMPLEMENT_BUILD_SEQUENCE , '
1917 ||' RELEASE_ERRORS , '
1918 ||' SCHEDULE_COMPRESSION_DAYS , '
1919 ||' NUMBER1 '
1920 ||')'
1921 ||'SELECT'
1922 ||' MRP_SCHEDULE_DATES_S.nextval'||v_dblink||' , '
1923 ||' ms.LAST_UPDATE_DATE , '
1924 ||' ms.LAST_UPDATED_BY , '
1925 ||' ms.CREATION_DATE , '
1926 ||' ms.CREATED_BY , '
1927 ||' ms.LAST_UPDATE_LOGIN , '
1928 ||' msi.SR_INVENTORY_ITEM_ID , '
1929 ||' ms.ORGANIZATION_ID , '
1930 ||' :p_DESIGNATOR,'
1931 ||' NEW_SCHEDULE_DATE , '
1932 ||' OLD_SCHEDULE_DATE , '
1933 ||' NEW_WIP_START_DATE , '
1934 ||' OLD_WIP_START_DATE , '
1935 ||' MRP_SCHEDULE_DATES_S.currval'||v_dblink||' , '
1936 ||' DISPOSITION_STATUS_TYPE , '
1937 ||' ORDER_TYPE , '
1938 ||' NULL,'
1939 ||' NEW_ORDER_QUANTITY , '
1940 ||' OLD_ORDER_QUANTITY , '
1941 ||' NEW_ORDER_PLACEMENT_DATE , '
1942 ||' OLD_ORDER_PLACEMENT_DATE , '
1943 ||' FIRM_PLANNED_TYPE , '
1944 ||' NEW_PROCESSING_DAYS , '
1945 ||' IMPLEMENTED_QUANTITY , '
1946 ||' PURCH_LINE_NUM , '
1947 ||' ms.REVISION , '
1948 ||' LAST_UNIT_COMPLETION_DATE , '
1949 ||' FIRST_UNIT_START_DATE , '
1950 ||' LAST_UNIT_START_DATE , '
1951 ||' DAILY_RATE , '
1952 ||' OLD_DOCK_DATE , '
1953 ||' NEW_DOCK_DATE , '
1954 ||' RESCHEDULE_DAYS , '
1955 ||' ms.REQUEST_ID , '
1956 ||' ms.PROGRAM_APPLICATION_ID , '
1957 ||' ms.PROGRAM_ID , '
1958 ||' ms.PROGRAM_UPDATE_DATE , '
1959 ||' ms.QUANTITY_IN_PROCESS , '
1960 ||' ms.FIRM_QUANTITY , '
1961 ||' ms.FIRM_DATE , '
1962 ||' UPDATED , '
1963 ||' STATUS , '
1964 ||' APPLIED , '
1965 ||' IMPLEMENT_DEMAND_CLASS , '
1966 ||' IMPLEMENT_DATE , '
1967 ||' IMPLEMENT_QUANTITY , '
1968 ||' IMPLEMENT_FIRM , '
1969 ||' IMPLEMENT_WIP_CLASS_CODE , '
1970 ||' IMPLEMENT_JOB_NAME , '
1971 ||' IMPLEMENT_DOCK_DATE , '
1972 ||' IMPLEMENT_STATUS_CODE , '
1973 ||' IMPLEMENT_EMPLOYEE_ID , '
1974 ||' IMPLEMENT_UOM_CODE , '
1975 ||' IMPLEMENT_LOCATION_ID , '
1976 ||' RELEASE_STATUS , '
1977 ||' LOAD_TYPE , '
1978 ||' IMPLEMENT_AS , '
1979 ||' DEMAND_CLASS , '
1980 ||' ALTERNATE_BOM_DESIGNATOR , '
1981 ||' ALTERNATE_ROUTING_DESIGNATOR , '
1982 ||' LINE_ID , '
1983 ||' BY_PRODUCT_USING_ASSY_ID , '
1984 ||' IMPLEMENT_SOURCE_ORG_ID , '
1985 ||' NULL,'
1986 ||' NULL,'
1987 ||' SOURCE_ORGANIZATION_ID , '
1988 ||' NULL,'
1989 ||' NULL,'
1990 ||' NEW_SHIP_DATE , '
1991 ||' PROJECT_ID , '
1992 ||' TASK_ID , '
1993 ||' PLANNING_GROUP , '
1994 ||' IMPLEMENT_PROJECT_ID , '
1995 ||' IMPLEMENT_TASK_ID , '
1996 ||' IMPLEMENT_SCHEDULE_GROUP_ID , '
1997 ||' IMPLEMENT_BUILD_SEQUENCE , '
1998 ||' RELEASE_ERRORS , '
1999 ||' SCHEDULE_COMPRESS_DAYS , '
2000 ||' NUMBER1 '
2001 ||' FROM MSC_SUPPLIES ms,'
2002 ||' MSC_SYSTEM_ITEMS msi'
2003 ||' WHERE ms.plan_id = :PLAN_ID'
2004 ||' AND ms.sr_instance_id = :pINSTANCE_ID'
2005 ||' AND msi.organization_id = ms.organization_id'
2006 ||' AND msi.inventory_item_id = ms.inventory_item_id'
2007 ||' AND msi.sr_instance_id = ms.sr_instance_id'
2008 ||' AND msi.plan_id = ms.plan_id'
2009 ||' and trunc(ms.NEW_SCHEDULE_DATE) BETWEEN (:pHORIZON_START_DATE) and (:pHORIZON_END_DATE)'
2010 ||' AND NOT EXISTS (select 1 from msc_system_items msi1 , msc_trading_partners mtp'
2011 ||' where msi1.inventory_item_id = ms.inventory_item_id '
2012 ||' and msi1.organization_id = ms.organization_id '
2013 ||' and msi1.plan_id = ms.plan_id '
2014 ||' AND msi1.sr_instance_id = ms.sr_instance_id '
2015 ||' and nvl(msi1.release_time_fence_code,-1) = 7 '
2016 ||' and mtp.sr_tp_id = msi1.organization_id '
2017 ||' and mtp.sr_instance_id = msi1.sr_instance_id '
2018 ||' and mtp.partner_type=3 '
2019 ||' and (mtp.modeled_supplier_id is not null OR mtp.modeled_supplier_site_id is not null))'
2020 ||' AND ms.order_type = 5 '|| lv_is_supp_null;
2021
2022 IF pORGANIZATION_ID IS NOT NULL THEN
2023 lv_sql_stmt1 := lv_sql_stmt1 || ' AND ms.organization_id = :pORGANIZATION_ID';
2024 END IF;
2025
2026 IF pPLANNER IS NOT NULL THEN
2027 lv_sql_stmt1 := lv_sql_stmt1 || ' AND msi.planner_code = :pPLANNER';
2028 END IF;
2029
2030 IF pCATEGORY_ID IS NOT NULL THEN
2031 lv_sql_stmt1 := lv_sql_stmt1 || ' and exists (select 1 from msc_item_categories mic, msc_category_sets mcs'
2032 ||' where mic.inventory_item_id = msi.inventory_item_id'
2033 ||' and mic.organization_id = msi.organization_id'
2034 ||' and mic.sr_instance_id = msi.sr_instance_id'
2035 ||' and mic.SR_CATEGORY_ID = :pCATEGORY_ID'
2036 ||' and mic.category_set_id = mcs.category_set_id'
2037 ||' and mcs.sr_instance_id = mic.sr_instance_id'
2038 ||' and mcs.DEFAULT_FLAG = 1)';
2039 END IF;
2040
2041 IF pITEM_ID IS NOT NULL THEN
2042 lv_sql_stmt1 := lv_sql_stmt1 || ' AND msi.sr_inventory_item_id = :pITEM_ID';
2043 END IF;
2044
2045
2046 cursor1 := dbms_sql.open_cursor;
2047 dbms_sql.parse(cursor1, lv_sql_stmt1, dbms_sql.v7);
2048
2049 dbms_sql.bind_variable(cursor1, ':p_DESIGNATOR', pDESIGNATOR);
2050 dbms_sql.bind_variable(cursor1, ':PLAN_ID', c_rec.plan_id);
2051 dbms_sql.bind_variable(cursor1, ':pINSTANCE_ID', pINSTANCE_ID);
2052 dbms_sql.bind_variable(cursor1, ':pHORIZON_START_DATE', lv_start_date);
2053 dbms_sql.bind_variable(cursor1, ':pHORIZON_END_DATE', lv_end_date);
2054
2055 IF pORGANIZATION_ID IS NOT NULL THEN
2056 dbms_sql.bind_variable(cursor1, ':pORGANIZATION_ID', pORGANIZATION_ID);
2057 END IF;
2058
2059 IF pPLANNER IS NOT NULL THEN
2060 dbms_sql.bind_variable(cursor1, ':pPLANNER', pPLANNER);
2061 END IF;
2062
2063 IF pCATEGORY_ID IS NOT NULL THEN
2064 dbms_sql.bind_variable(cursor1, ':pCATEGORY_ID', pCATEGORY_ID);
2065 END IF;
2066
2067 IF pITEM_ID IS NOT NULL THEN
2068 dbms_sql.bind_variable(cursor1, ':pITEM_ID', pITEM_ID);
2069 END IF;
2070
2071 v_total_make_count := dbms_sql.execute(cursor1);
2072 dbms_sql.close_cursor(cursor1);
2073
2074
2075 END IF;
2076
2077 v_total_count := v_total_buy_count + v_total_make_Count ;
2078
2079 v_buff := 'Total Number of Buy Recommendations loaded : '||v_total_buy_count;
2080 LOG_MESSAGE(v_buff);
2081 v_buff := 'Total Number of Make Recommendations loaded : '||v_total_make_Count;
2082 LOG_MESSAGE(v_buff);
2083
2084 v_buff := 'Total Number of Recommendations loaded : '||v_total_count;
2085 LOG_MESSAGE(v_buff);
2086
2087 END ;
2088
2089 END LOOP;
2090
2091
2092 FOR c_rec in c1 LOOP
2093 BEGIN
2094 -- If the input parameter is Buy Orders only then supplier/
2095 -- supplier site is not null
2096
2097 v_buff := 'Loading System Items ..... ';
2098 LOG_MESSAGE(v_buff);
2099
2100 lv_sql_stmt:=
2101
2102 ' INSERT INTO MRP_SYSTEM_ITEMS'||v_dblink
2103 ||' (INVENTORY_ITEM_ID ,'
2104 ||' ORGANIZATION_ID ,'
2105 ||' COMPILE_DESIGNATOR ,'
2106 ||' LAST_UPDATE_DATE ,'
2107 ||' LAST_UPDATED_BY ,'
2108 ||' CREATION_DATE ,'
2109 ||' CREATED_BY ,'
2110 ||' INVENTORY_TYPE ,'
2111 ||' MRP_PLANNING_CODE ,'
2112 ||' INVENTORY_PLANNING_CODE ,'
2113 ||' LOW_LEVEL_CODE ,'
2114 ||' FULL_LEAD_TIME ,'
2115 ||' UOM_CODE ,'
2116 ||' BUILD_IN_WIP_FLAG ,'
2117 ||' PURCHASING_ENABLED_FLAG ,'
2118 ||' PLANNING_MAKE_BUY_CODE ,'
2119 ||' REPETITIVE_TYPE ,'
2120 ||' LOT_CONTROL_CODE ,'
2121 ||' ROUNDING_CONTROL_TYPE ,'
2122 ||' CALCULATE_ATP ,'
2123 ||' END_ASSEMBLY_PEGGING ,'
2124 ||' NETTABLE_INVENTORY_QUANTITY ,'
2125 ||' NONNETTABLE_INVENTORY_QUANTITY ,'
2126 ||' ENGINEERING_ITEM_FLAG ,'
2127 ||' SAFETY_STOCK_CODE ,'
2128 ||' PREPROCESSING_LEAD_TIME ,'
2129 ||' POSTPROCESSING_LEAD_TIME ,'
2130 ||' CUMULATIVE_TOTAL_LEAD_TIME ,'
2131 ||' CUM_MANUFACTURING_LEAD_TIME ,'
2132 ||' LAST_UPDATE_LOGIN ,'
2133 ||' FIXED_LEAD_TIME ,'
2134 ||' VARIABLE_LEAD_TIME ,'
2135 ||' STANDARD_COST ,'
2136 ||' WIP_SUPPLY_TYPE ,'
2137 ||' OVERRUN_PERCENTAGE ,'
2138 ||' ACCEPTABLE_RATE_INCREASE ,'
2139 ||' ACCEPTABLE_RATE_DECREASE ,'
2140 ||' SAFETY_STOCK_PERCENT ,'
2141 ||' SAFETY_STOCK_BUCKET_DAYS ,'
2142 ||' SAFETY_STOCK_QUANTITY ,'
2143 ||' DESCRIPTION ,'
2144 ||' CATEGORY_ID ,'
2145 ||' BUYER_ID ,'
2146 ||' BUYER_NAME ,'
2147 ||' PLANNER_CODE ,'
2148 ||' ABC_CLASS ,'
2149 ||' REVISION ,'
2150 ||' FIXED_DAYS_SUPPLY ,'
2151 ||' FIXED_ORDER_QUANTITY ,'
2152 ||' FIXED_LOT_MULTIPLIER ,'
2153 ||' MINIMUM_ORDER_QUANTITY ,'
2154 ||' MAXIMUM_ORDER_QUANTITY ,'
2155 ||' PLANNING_TIME_FENCE_DAYS ,'
2156 -- ||' PLANNING_TIME_FENCE_DATE ,'
2157 ||' DEMAND_TIME_FENCE_DAYS ,'
2158 ||' INVENTORY_USE_UP_DATE ,'
2159 ||' ACCEPTABLE_EARLY_DELIVERY ,'
2160 ||' PLANNER_STATUS_CODE ,'
2161 ||' SHRINKAGE_RATE ,'
2162 ||' EXCEPTION_SHORTAGE_DAYS ,'
2163 ||' EXCEPTION_EXCESS_DAYS ,'
2164 ||' EXCEPTION_REP_VARIANCE_DAYS ,'
2165 ||' EXCEPTION_OVERPROMISED_DAYS ,'
2166 ||' PLANNING_EXCEPTION_SET ,'
2167 ||' EXCESS_QUANTITY ,'
2168 ||' REPETITIVE_VARIANCE ,'
2169 ||' BASE_ITEM_ID ,'
2170 ||' ATO_FORECAST_CONTROL ,'
2171 ||' EXCEPTION_CODE ,'
2172 ||' PROGRAM_UPDATE_DATE ,'
2173 ||' REQUEST_ID ,'
2174 ||' PROGRAM_APPLICATION_ID ,'
2175 ||' PROGRAM_ID ,'
2176 ||' DEMAND_TIME_FENCE_DATE ,'
2177 ||' IN_SOURCE_PLAN ,'
2178 ||' BOM_ITEM_TYPE ,'
2179 ||' FULL_PEGGING, '
2180 ||' ORGANIZATION_CODE )'
2181 -- ||' EFFECTIVITY_CONTROL ) '
2182 ||'SELECT '
2183 ||' SR_INVENTORY_ITEM_ID ,'
2184 ||' ORGANIZATION_ID ,'
2185 ||' :pdesignator,'
2186 ||' LAST_UPDATE_DATE ,'
2187 ||' LAST_UPDATED_BY ,'
2188 ||' CREATION_DATE ,'
2189 ||' CREATED_BY ,'
2190 ||' 1,'
2191 ||' MRP_PLANNING_CODE ,'
2192 ||' INVENTORY_PLANNING_CODE ,'
2193 ||' decode(LOW_LEVEL_CODE,null,1,LOW_LEVEL_CODE),'
2194 ||' FULL_LEAD_TIME ,'
2195 ||' UOM_CODE ,'
2196 ||' BUILD_IN_WIP_FLAG ,'
2197 ||' PURCHASING_ENABLED_FLAG ,'
2198 ||' PLANNING_MAKE_BUY_CODE ,'
2199 ||' REPETITIVE_TYPE ,'
2200 ||' LOT_CONTROL_CODE ,'
2201 ||' ROUNDING_CONTROL_TYPE ,'
2202 ||' CALCULATE_ATP ,'
2203 ||' decode(END_ASSEMBLY_PEGGING,null,1,END_ASSEMBLY_PEGGING) ,'
2204 ||' decode(NETTABLE_INVENTORY_QUANTITY,null,0,NETTABLE_INVENTORY_QUANTITY ) ,'
2205 ||' decode(NONNETTABLE_INVENTORY_QUANTITY,NULL,0,NONNETTABLE_INVENTORY_QUANTITY ),'
2206 ||' ENGINEERING_ITEM_FLAG ,'
2207 ||' SAFETY_STOCK_CODE ,'
2208 ||' PREPROCESSING_LEAD_TIME ,'
2209 ||' POSTPROCESSING_LEAD_TIME ,'
2210 ||' CUMULATIVE_TOTAL_LEAD_TIME ,'
2211 ||' CUM_MANUFACTURING_LEAD_TIME ,'
2212 ||' LAST_UPDATE_LOGIN ,'
2213 ||' FIXED_LEAD_TIME ,'
2214 ||' VARIABLE_LEAD_TIME ,'
2215 ||' STANDARD_COST ,'
2216 ||' WIP_SUPPLY_TYPE ,'
2217 ||' OVERRUN_PERCENTAGE ,'
2218 ||' ACCEPTABLE_RATE_INCREASE ,'
2219 ||' ACCEPTABLE_RATE_DECREASE ,'
2220 ||' SAFETY_STOCK_PERCENT ,'
2221 ||' SAFETY_STOCK_BUCKET_DAYS ,'
2222 ||' FIXED_SAFETY_STOCK_QTY ,'
2223 ||' DESCRIPTION ,'
2224 ||' SR_CATEGORY_ID ,'
2225 ||' BUYER_ID ,'
2226 ||' BUYER_NAME ,'
2227 ||' PLANNER_CODE ,'
2228 ||' ABC_CLASS ,'
2229 ||' REVISION ,'
2230 ||' FIXED_DAYS_SUPPLY ,'
2231 ||' FIXED_ORDER_QUANTITY ,'
2232 ||' FIXED_LOT_MULTIPLIER ,'
2233 ||' MINIMUM_ORDER_QUANTITY ,'
2234 ||' MAXIMUM_ORDER_QUANTITY ,'
2235 ||' PLANNING_TIME_FENCE_DAYS ,'
2236 -- ||' PLANNING_TIME_FENCE_DATE ,'
2237 ||' DEMAND_TIME_FENCE_DAYS ,'
2238 ||' INVENTORY_USE_UP_DATE ,'
2239 ||' ACCEPTABLE_EARLY_DELIVERY ,'
2240 ||' PLANNER_STATUS_CODE ,'
2241 ||' SHRINKAGE_RATE ,'
2242 ||' EXCEPTION_SHORTAGE_DAYS ,'
2243 ||' EXCEPTION_EXCESS_DAYS ,'
2244 ||' EXCEPTION_REP_VARIANCE_DAYS ,'
2245 ||' EXCEPTION_OVERPROMISED_DAYS ,'
2246 ||' PLANNING_EXCEPTION_SET ,'
2247 ||' EXCESS_QUANTITY ,'
2248 ||' REPETITIVE_VARIANCE ,'
2249 ||' BASE_ITEM_ID ,'
2250 ||' ATO_FORECAST_CONTROL ,'
2251 ||' EXCEPTION_CODE ,'
2252 ||' PROGRAM_UPDATE_DATE ,'
2253 ||' REQUEST_ID ,'
2254 ||' PROGRAM_APPLICATION_ID ,'
2255 ||' PROGRAM_ID ,'
2256 ||' DEMAND_TIME_FENCE_DATE ,'
2257 ||' IN_SOURCE_PLAN ,'
2258 ||' BOM_ITEM_TYPE ,'
2259 ||' FULL_PEGGING, '
2260 ||' substr(ORGANIZATION_CODE,5,3) '
2261 -- ||' EFFECTIVITY_CONTROL '
2262 ||' FROM MSC_SYSTEM_ITEMS msi'
2263 ||' WHERE msi.plan_id = :PLAN_ID'
2264 ||' AND msi.sr_instance_id = :pINSTANCE_ID'
2265 -- check orgs which are in the plan only bug#7016427 **hbinjola**
2266 ||' AND msi.ORGANIZATION_ID IN (SELECT MPOV.PLANNED_ORGANIZATION'
2267 ||' FROM MSC_PLAN_ORGANIZATIONS_V MPOV'
2268 ||' WHERE MPOV.SR_INSTANCE_ID = :pINSTANCE_ID'
2269 ||' AND MPOV.COMPILE_DESIGNATOR = :pDESIGNATOR)';
2270
2271
2272 IF pORGANIZATION_ID IS NOT NULL THEN
2273 lv_sql_stmt := lv_sql_stmt || ' AND msi.organization_id = :pORGANIZATION_ID';
2274 END IF;
2275
2276 IF pPLANNER IS NOT NULL THEN
2277 lv_sql_stmt := lv_sql_stmt || ' AND msi.planner_code = :pPLANNER';
2278 END IF;
2279
2280 IF pCATEGORY_ID IS NOT NULL THEN
2281 lv_sql_stmt := lv_sql_stmt || ' and exists (select 1 from msc_item_categories mic, msc_category_sets mcs'
2282 ||' where mic.inventory_item_id = msi.inventory_item_id'
2283 ||' and mic.organization_id = msi.organization_id'
2284 ||' and mic.sr_instance_id = msi.sr_instance_id'
2285 ||' and mic.SR_CATEGORY_ID = :pCATEGORY_ID'
2286 ||' and mic.category_set_id = mcs.category_set_id'
2287 ||' and mcs.sr_instance_id = mic.sr_instance_id'
2288 ||' and mcs.DEFAULT_FLAG = 1)';
2289 END IF;
2290
2291 IF pITEM_ID IS NOT NULL THEN
2292 lv_sql_stmt := lv_sql_stmt || ' AND msi.sr_inventory_item_id = :pITEM_ID';
2293 END IF;
2294
2295 cursor1 := dbms_sql.open_cursor;
2296 dbms_sql.parse(cursor1, lv_sql_stmt, dbms_sql.v7);
2297
2298 dbms_sql.bind_variable(cursor1, ':pDESIGNATOR', pDESIGNATOR);
2299 dbms_sql.bind_variable(cursor1, ':PLAN_ID', c_rec.plan_id);
2300 dbms_sql.bind_variable(cursor1, ':pINSTANCE_ID', pINSTANCE_ID);
2301 --bug#7016427 **hbinjola**
2302 dbms_sql.bind_variable(cursor1, ':pINSTANCE_ID', pINSTANCE_ID);
2303 dbms_sql.bind_variable(cursor1, ':pDESIGNATOR', pDESIGNATOR);
2304
2305
2306 IF pORGANIZATION_ID IS NOT NULL THEN
2307 dbms_sql.bind_variable(cursor1, ':pORGANIZATION_ID', pORGANIZATION_ID);
2308 END IF;
2309
2310 IF pPLANNER IS NOT NULL THEN
2311 dbms_sql.bind_variable(cursor1, ':pPLANNER', pPLANNER);
2312 END IF;
2313
2314 IF pCATEGORY_ID IS NOT NULL THEN
2315 dbms_sql.bind_variable(cursor1, ':pCATEGORY_ID', pCATEGORY_ID);
2316 END IF;
2317
2318 IF pITEM_ID IS NOT NULL THEN
2319 dbms_sql.bind_variable(cursor1, ':pITEM_ID', pITEM_ID);
2320 END IF;
2321
2322 v_item_count := dbms_sql.execute(cursor1);
2323 dbms_sql.close_cursor(cursor1);
2324
2325
2326 v_buff := 'Number of System items loaded : '||v_item_count;
2327 LOG_MESSAGE(v_buff);
2328
2329 END ;
2330
2331 END LOOP;
2332
2333 IF pDEMAND = 1 THEN --for bug 3073566
2334 FOR c_rec in c1 LOOP
2335 BEGIN
2336
2337 v_buff := 'Loading Gross Requirements ..... ';
2338 LOG_MESSAGE(v_buff);
2339
2340 lv_sql_stmt:=
2341
2342 ' INSERT INTO MRP_GROSS_REQUIREMENTS'||v_dblink
2343 ||' (DEMAND_ID ,'
2344 ||' LAST_UPDATE_DATE ,'
2345 ||' LAST_UPDATED_BY ,'
2346 ||' CREATION_DATE ,'
2347 ||' CREATED_BY ,'
2348 ||' LAST_UPDATE_LOGIN ,'
2349 ||' INVENTORY_ITEM_ID ,'
2350 ||' ORGANIZATION_ID ,'
2351 ||' COMPILE_DESIGNATOR ,'
2352 ||' USING_ASSEMBLY_ITEM_ID ,'
2353 ||' USING_ASSEMBLY_DEMAND_DATE ,'
2354 ||' USING_REQUIREMENTS_QUANTITY ,'
2355 ||' ASSEMBLY_DEMAND_COMP_DATE ,'
2356 ||' DEMAND_TYPE ,'
2357 ||' ORIGINATION_TYPE ,'
2358 ||' DISPOSITION_ID ,'
2359 ||' DAILY_DEMAND_RATE ,'
2360 ||' REQUEST_ID ,'
2361 ||' RESERVE_QUANTITY ,'
2362 ||' SOURCE_ORGANIZATION_ID ,'
2363 ||' UPDATED ,'
2364 ||' STATUS ,'
2365 ||' APPLIED ,'
2366 ||' DEMAND_CLASS ,'
2367 ||' FIRM_QUANTITY ,'
2368 ||' FIRM_DATE ,'
2369 ||' OLD_DEMAND_QUANTITY ,'
2370 ||' DEMAND_SCHEDULE_NAME ,'
2371 ||' OLD_DEMAND_DATE ,'
2372 ||' PROJECT_ID ,'
2373 ||' TASK_ID ,'
2374 ||' PLANNING_GROUP )'
2375 ||' SELECT '
2376 ||' MRP_GROSS_REQUIREMENTS_S.nextval'||v_dblink||' , '
2377 ||' md.LAST_UPDATE_DATE ,'
2378 ||' md.LAST_UPDATED_BY ,'
2379 ||' md.CREATION_DATE ,'
2380 ||' md.CREATED_BY ,'
2381 ||' md.LAST_UPDATED_BY ,'
2382 ||' msi.SR_INVENTORY_ITEM_ID ,'
2383 ||' md.ORGANIZATION_ID ,'
2384 ||' :pdesignator ,'
2385 ||' mtil.SR_INVENTORY_ITEM_ID ,'
2386 ||' trunc(md.USING_ASSEMBLY_DEMAND_DATE) ,'
2387 ||' md.USING_REQUIREMENT_QUANTITY ,'
2388 ||' trunc(md.ASSEMBLY_DEMAND_COMP_DATE) ,'
2389 ||' md.DEMAND_TYPE ,'
2390 ||' decode(md.ORIGINATION_TYPE,29,7 '
2391 ||' ,30,6, '
2392 ||' md.ORIGINATION_TYPE) ,'
2393 ||' NULL ,'
2394 ||' md.DAILY_DEMAND_RATE ,'
2395 ||' NULL ,'
2396 ||' md.RESERVED_QUANTITY ,'
2397 ||' md.SOURCE_ORGANIZATION_ID ,'
2398 ||' md.UPDATED ,'
2399 ||' md.STATUS ,'
2400 ||' md.APPLIED ,'
2401 ||' md.DEMAND_CLASS ,'
2402 ||' md.FIRM_QUANTITY ,'
2403 ||' md.FIRM_DATE ,'
2404 ||' md.OLD_DEMAND_QUANTITY ,'
2405 ||' NULL ,'
2406 ||' md.OLD_DEMAND_DATE ,'
2407 ||' md.PROJECT_ID ,'
2408 ||' md.TASK_ID ,'
2409 ||' md.PLANNING_GROUP '
2410 ||' FROM MSC_DEMANDS md ,'
2411 ||' MSC_SYSTEM_ITEMS msi ,'
2412 ||' MSC_ITEM_ID_LID mtil '
2413 ||' WHERE md.plan_id = :PLAN_ID '
2414 ||' AND md.sr_instance_id = :pINSTANCE_ID '
2415 ||' and trunc(md.USING_ASSEMBLY_DEMAND_DATE) BETWEEN (:pHORIZON_START_DATE) and (:pHORIZON_END_DATE)'
2416 ||' AND msi.organization_id = md.organization_id '
2417 ||' AND msi.inventory_item_id = md.inventory_item_id '
2418 ||' AND msi.sr_instance_id = md.sr_instance_id '
2419 ||' AND msi.plan_id = md.plan_id '
2420 ||' AND mtil.sr_instance_id = md.sr_instance_id '
2421 ||' AND mtil.inventory_item_id = md.USING_ASSEMBLY_ITEM_ID'
2422 ||' AND NOT EXISTS (select 1 from msc_system_items msi1'
2423 ||' where msi1.inventory_item_id = md.USING_ASSEMBLY_ITEM_ID '
2424 ||' and msi1.organization_id = md.organization_id '
2425 ||' and msi1.plan_id = md.plan_id '
2426 ||' AND msi1.sr_instance_id = md.sr_instance_id '
2427 ||' and nvl(msi1.release_time_fence_code,-1) = 7)';
2428
2429 IF pORGANIZATION_ID IS NOT NULL THEN
2430 lv_sql_stmt := lv_sql_stmt || ' AND md.organization_id = :pORGANIZATION_ID';
2431 END IF;
2432
2433 IF pPLANNER IS NOT NULL THEN
2434 lv_sql_stmt := lv_sql_stmt || ' AND msi.planner_code = :pPLANNER';
2435 END IF;
2436
2437 IF pCATEGORY_ID IS NOT NULL THEN
2438 lv_sql_stmt := lv_sql_stmt || ' and exists (select 1 from msc_item_categories mic, msc_category_sets mcs'
2439 ||' where mic.inventory_item_id = msi.inventory_item_id'
2440 ||' and mic.organization_id = msi.organization_id'
2441 ||' and mic.sr_instance_id = msi.sr_instance_id'
2442 ||' and mic.SR_CATEGORY_ID = :pCATEGORY_ID'
2443 ||' and mic.category_set_id = mcs.category_set_id'
2444 ||' and mcs.sr_instance_id = mic.sr_instance_id'
2445 ||' and mcs.DEFAULT_FLAG = 1)';
2446 END IF;
2447
2448 IF pITEM_ID IS NOT NULL THEN
2449 lv_sql_stmt := lv_sql_stmt || ' AND msi.sr_inventory_item_id = :pITEM_ID';
2450 END IF;
2451
2452 cursor1 := dbms_sql.open_cursor;
2453 dbms_sql.parse(cursor1, lv_sql_stmt, dbms_sql.v7);
2454
2455 dbms_sql.bind_variable(cursor1, ':pDESIGNATOR', pDESIGNATOR);
2456 dbms_sql.bind_variable(cursor1, ':PLAN_ID', c_rec.plan_id);
2457 dbms_sql.bind_variable(cursor1, ':pINSTANCE_ID', pINSTANCE_ID);
2458 dbms_sql.bind_variable(cursor1, ':pHORIZON_START_DATE', lv_start_date);
2459 dbms_sql.bind_variable(cursor1, ':pHORIZON_END_DATE', lv_end_date);
2460
2461 IF pORGANIZATION_ID IS NOT NULL THEN
2462 dbms_sql.bind_variable(cursor1, ':pORGANIZATION_ID', pORGANIZATION_ID);
2463 END IF;
2464
2465 IF pPLANNER IS NOT NULL THEN
2466 dbms_sql.bind_variable(cursor1, ':pPLANNER', pPLANNER);
2467 END IF;
2468
2469 IF pCATEGORY_ID IS NOT NULL THEN
2470 dbms_sql.bind_variable(cursor1, ':pCATEGORY_ID', pCATEGORY_ID);
2471 END IF;
2472
2473 IF pITEM_ID IS NOT NULL THEN
2474 dbms_sql.bind_variable(cursor1, ':pITEM_ID', pITEM_ID);
2475 END IF;
2476
2477 v_total_mgr_count := dbms_sql.execute(cursor1);
2478 dbms_sql.close_cursor(cursor1);
2479
2480
2481 v_buff := 'Number of Gross Requirements loaded : '||v_total_mgr_count;
2482 LOG_MESSAGE(v_buff);
2483
2484 END ;
2485
2486 END LOOP;
2487 END IF; --for bug 3073566
2488 IF to_number(pPLAN_TYPE) = 2 THEN
2489
2490 select organization_id
2491 into lv_organization_id
2492 from msc_plans
2493 where sr_instance_id = pINSTANCE_ID
2494 and compile_designator = pDESIGNATOR;
2495
2496 lv_sql_stmt :=
2497 'DELETE FROM MRP_SCHEDULE_DATES'||v_dblink||' MSD'
2498 ||' WHERE MSD.SCHEDULE_DESIGNATOR = :pDESIGNATOR '
2499 ||' AND MSD.ORGANIZATION_ID IN (SELECT MPOV.PLANNED_ORGANIZATION'
2500 ||' FROM MSC_PLAN_ORGANIZATIONS_V MPOV'
2501 ||' WHERE MPOV.SR_INSTANCE_ID = :pINSTANCE_ID'
2502 ||' AND MPOV.COMPILE_DESIGNATOR = :pDESIGNATOR)';
2503
2504 Execute immediate lv_sql_stmt using pDESIGNATOR, pINSTANCE_ID, pDESIGNATOR;
2505
2506 v_buff := 'Deleted MRP_SCHEDULE_DATES for Plan : '||pDESIGNATOR;
2507 LOG_MESSAGE(v_buff);
2508
2509 lv_sql_stmt :=
2510 'DELETE FROM MRP_SCHEDULE_ITEMS'||v_dblink||' MSI'
2511 ||' WHERE MSI.SCHEDULE_DESIGNATOR = :pDESIGNATOR '
2512 ||' AND MSI.ORGANIZATION_ID IN (SELECT MPOV.PLANNED_ORGANIZATION'
2513 ||' FROM MSC_PLAN_ORGANIZATIONS_V MPOV'
2514 ||' WHERE MPOV.SR_INSTANCE_ID = :pINSTANCE_ID'
2515 ||' AND MPOV.COMPILE_DESIGNATOR = :pDESIGNATOR)';
2516
2517 Execute immediate lv_sql_stmt using pDESIGNATOR, pINSTANCE_ID, pDESIGNATOR;
2518
2519 v_buff := 'Deleted MRP_SCHEDULE_ITEMS for Plan : '||pDESIGNATOR;
2520 LOG_MESSAGE(v_buff);
2521
2522 lv_sql_stmt:=
2523 ' BEGIN'
2524 ||' mrp_planner_pk.create_new_planner_mps_entries'||v_dblink||
2525 '(arg_compile_desig => :pDESIGNATOR ,
2526 arg_sched_desig => to_char(null),
2527 arg_org_id => :ORGANIZATION_ID );'
2528 ||' END;';
2529
2530 EXECUTE IMMEDIATE lv_sql_stmt USING pDESIGNATOR, lv_organization_id;
2531
2532 v_buff := 'Loaded MRP_SCHEDULE_DATES for Plan : '||pDESIGNATOR;
2533 LOG_MESSAGE(v_buff);
2534
2535 SELECT FND_GLOBAL.USER_ID
2536 INTO lv_user_id
2537 FROM dual;
2538
2539 lv_sql_stmt:= ' INSERT INTO MRP_SCHEDULE_ITEMS'||v_dblink
2540 ||' (INVENTORY_ITEM_ID,'
2541 ||' ORGANIZATION_ID,'
2542 ||' SCHEDULE_DESIGNATOR,'
2543 ||' LAST_UPDATE_DATE,'
2544 ||' LAST_UPDATED_BY,'
2545 ||' creation_date,'
2546 ||' created_by,'
2547 ||' last_update_login,'
2548 ||' MPS_EXPLOSION_LEVEL)'
2549 ||' SELECT DISTINCT dates.inventory_item_id,'
2550 ||' dates.organization_id,'
2551 ||' :compile_desig,'
2552 ||' SYSDATE,'
2553 ||' :user_id,'
2554 ||' SYSDATE,'
2555 ||' :user_id,'
2556 ||' -1,'
2557 ||' 100 /* this has no meaning for an MRP part */'
2558 ||' FROM mrp_schedule_dates'||v_dblink||' dates,'
2559 ||' mrp_system_items'||v_dblink||' data,'
2560 ||' mrp_plan_organizations_v'||v_dblink||' mpo'
2561 ||' WHERE NOT EXISTS'
2562 ||' (SELECT inventory_item_id'
2563 ||' FROM mrp_schedule_items'||v_dblink||' items'
2564 ||' WHERE items.organization_id ='
2565 ||' mpo.planned_organization'
2566 ||' AND items.inventory_item_id ='
2567 ||' dates.inventory_item_id'
2568 ||' AND items.schedule_designator ='
2569 ||' mpo.compile_designator)'
2570 ||' AND dates.organization_id = data.organization_id'
2571 ||' AND dates.schedule_designator = data.compile_designator'
2572 ||' AND dates.inventory_item_id = data.inventory_item_id'
2573 ||' AND data.mrp_planning_code IN'
2574 ||' (4, 8)'
2575 ||' AND data.organization_id = mpo.planned_organization'
2576 ||' AND data.compile_designator = mpo.compile_designator'
2577 ||' AND mpo.organization_id = :org_id'
2578 ||' AND mpo.compile_designator = :compile_desig';
2579
2580 EXECUTE IMMEDIATE lv_sql_stmt USING pDESIGNATOR,lv_user_id,lv_user_id, lv_organization_id, pDESIGNATOR;
2581
2582 v_buff := 'Loaded MRP_SCHEDULE_ITEMS for Plan : '||pDESIGNATOR;
2583 LOG_MESSAGE(v_buff);
2584
2585 END IF;
2586 COMMIT;
2587 END IF;
2588
2589 IF RETCODE = G_ERROR THEN
2590 ERRBUF := v_errbuf;
2591 RETCODE := v_retcode;
2592 RETURN;
2593 END IF;
2594
2595 EXCEPTION
2596
2597 WHEN A2A_EXCEPTION THEN
2598 null; -- just to terminate the process
2599
2600 WHEN OTHERS THEN
2601
2602 RAISE;
2603 ERRBUF := SQLERRM;
2604 RETCODE := G_ERROR;
2605
2606 LOG_MESSAGE(SQLERRM);
2607
2608 RETURN;
2609
2610 END PUSH_PLAN_INFO; --Main
2611
2612 --=========================================================================
2613
2614 END MSC_M2A_PUSH;