[Home] [Help]
PACKAGE BODY: APPS.MSC_CL_DEMAND_PULL
Source
1 PACKAGE BODY MSC_CL_DEMAND_PULL AS -- body
2 /* $Header:*/
3
4
5
6 v_union_sql varchar2(32767);
7 v_temp_tp_sql VARCHAR2(100);
8 v_sql_stmt VARCHAR2(32767);
9 v_temp_sql VARCHAR2(15000);
10 v_temp_sql1 VARCHAR2(1000);
11 v_temp_sql2 VARCHAR2(1000);
12 v_temp_sql3 VARCHAR2(1000);
13 v_temp_sql4 VARCHAR2(1000);
14
15 -- NULL_DBLINK CONSTANT VARCHAR2(1):= ' ';
16 -- NULL_DBLINK CONSTANT VARCHAR2(1) :=MSC_UTIL.NULL_DBLINK;
17
18 v_msc_so_offset_days NUMBER := NVL(FND_PROFILE.VALUE('MSC_SO_OFFSET_DAYS'),99999);
19 v_msc_x_vmi_om_order_type varchar2(50) := FND_PROFILE.VALUE('MSC_X_VMI_OM_ORDER_TYPE');
20
21
22
23 PROCEDURE LOAD_FORECASTS IS
24
25 BEGIN
26
27 IF MSC_CL_PULL.FORECAST_ENABLED= MSC_UTIL.SYS_YES THEN
28
29 MSC_CL_PULL.v_table_name:= 'MSC_ST_DESIGNATORS';
30 MSC_CL_PULL.v_view_name := 'MRP_AP_FORECAST_DSGN_V';
31
32 v_sql_stmt :=
33 'insert into MSC_ST_DESIGNATORS'
34 ||' ( DESIGNATOR,'
35 ||' FORECAST_SET,'
36 ||' ORGANIZATION_ID,'
37 ||' MPS_RELIEF,'
38 ||' INVENTORY_ATP_FLAG,'
39 ||' DESCRIPTION,'
40 ||' DISABLE_DATE,'
41 ||' DEMAND_CLASS,'
42 ||' CONSUME_FORECAST,'
43 ||' UPDATE_TYPE,'
44 ||' FORWARD_UPDATE_TIME_FENCE,'
45 ||' BACKWARD_UPDATE_TIME_FENCE,'
46 ||' OUTLIER_UPDATE_PERCENTAGE,'
47 ||' CUSTOMER_ID,'
48 ||' SHIP_ID,'
49 ||' BILL_ID,'
50 ||' BUCKET_TYPE,'
51 ||' DESIGNATOR_TYPE,'
52 ||' DELETED_FLAG,'
53 ||' REFRESH_ID,'
54 ||' SR_INSTANCE_ID)'
55 ||' select '
56 ||' x.FORECAST_DESIGNATOR,'
57 ||' x.FORECAST_SET,'
58 ||' x.ORGANIZATION_ID,'
59 ||' x.MPS_RELIEF,'
60 ||' x.INVENTORY_ATP_FLAG,'
61 ||' x.DESCRIPTION,'
62 ||' x.DISABLE_DATE,'
63 ||' x.DEMAND_CLASS,'
64 ||' x.CONSUME_FORECAST,'
65 ||' x.UPDATE_TYPE,'
66 ||' x.FOREWARD_UPDATE_TIME_FENCE,'
67 ||' x.BACKWARD_UPDATE_TIME_FENCE,'
68 ||' x.OUTLIER_UPDATE_PERCENTAGE,'
69 ||' x.CUSTOMER_ID,'
70 ||' x.SHIP_ID,'
71 ||' x.BILL_ID,'
72 ||' x.BUCKET_TYPE,'
73 ||' x.DESIGNATOR_TYPE,'
74 ||' 2,'
75 ||' :v_refresh_id,'
76 ||' :v_instance_id'
77 ||' from MRP_AP_FORECAST_DSGN_V'||MSC_CL_PULL.v_dblink||' x'
78 ||' WHERE x.ORGANIZATION_ID'||MSC_UTIL.v_in_org_str
79 ||' AND x.RN1>'||MSC_CL_PULL.v_lrn;
80
81 EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
82
83 COMMIT;
84
85 -- IF MSC_CL_PULL.v_lrnn<> -1 THEN -- incremental refresh
86
87 MSC_CL_PULL.v_table_name:= 'MSC_ST_DESIGNATORS';
88 MSC_CL_PULL.v_view_name := 'MRP_AD_FORECAST_DSGN_V';
89
90 v_sql_stmt :=
91 'insert into MSC_ST_DESIGNATORS'
92 ||' ( DESIGNATOR,'
93 ||' FORECAST_SET,'
94 ||' ORGANIZATION_ID,'
95 ||' DELETED_FLAG,'
96 ||' REFRESH_ID,'
97 ||' SR_INSTANCE_ID)'
98 ||' select '
99 ||' x.FORECAST_DESIGNATOR,'
100 ||' x.FORECAST_SET,'
101 ||' x.ORGANIZATION_ID,'
102 ||' 1,'
103 ||' :v_refresh_id,'
104 ||' :v_instance_id'
105 ||' from MRP_AD_FORECAST_DSGN_V'||MSC_CL_PULL.v_dblink||' x'
106 ||' WHERE x.ORGANIZATION_ID'||MSC_UTIL.v_in_org_str
107 ||' AND x.RN> '||MSC_CL_PULL.v_lrn
108 ||' AND x.ORGANIZATION_ID'||MSC_UTIL.v_in_org_str;
109
110
111 EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
112
113 COMMIT;
114 -- END IF ; -- Incremental Refresh
115
116 END IF;
117
118 END LOAD_FORECASTS;
119
120
121 PROCEDURE LOAD_ITEM_FORECASTS IS
122 BEGIN
123
124 IF MSC_CL_PULL.FORECAST_ENABLED = MSC_UTIL.SYS_YES THEN
125
126 IF MSC_CL_PULL.v_lrnn<> -1 THEN -- incremental refresh
127
128 MSC_CL_PULL.v_table_name:= 'MSC_ST_DEMANDS';
129 MSC_CL_PULL.v_view_name := 'MRP_AD_FORECAST_DEMAND_V';
130
131 v_sql_stmt :=
132 'insert into MSC_ST_DEMANDS'
133 ||' ( INVENTORY_ITEM_ID,'
134 ||' SALES_ORDER_LINE_ID,'
135 ||' ORIGINATION_TYPE,'
136 ||' ORGANIZATION_ID,'
137 ||' FORECAST_DESIGNATOR,'
138 ||' DELETED_FLAG,'
139 ||' REFRESH_ID,'
140 ||' SR_INSTANCE_ID)'
141 ||' select '
142 ||' x.INVENTORY_ITEM_ID,'
143 ||' x.TRANSACTION_ID,'
144 ||' 29,'
145 ||' x.ORGANIZATION_ID,'
146 ||' x.forecast_designator,'
147 ||' 1,'
148 ||' :v_refresh_id,'
149 ||' :v_instance_id'
150 ||' from MRP_AD_FORECAST_DEMAND_V'||MSC_CL_PULL.v_dblink||' x'
151 ||' WHERE x.ORGANIZATION_ID '||MSC_UTIL.v_in_org_str
152 ||' AND x.RN>'||MSC_CL_PULL.v_lrn;
153
154 EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
155
156 COMMIT;
157 END IF; -- Incremental Refresh.
158
159 MSC_CL_PULL.v_table_name:= 'MSC_ST_DEMANDS';
160 MSC_CL_PULL.v_view_name := 'MRP_AP_FORECAST_DEMAND_V';
161
162 v_sql_stmt:=
163 'insert into MSC_ST_DEMANDS'
164 ||' ( INVENTORY_ITEM_ID,'
165 ||' SALES_ORDER_LINE_ID,'
166 ||' ORGANIZATION_ID,'
167 ||' USING_ASSEMBLY_ITEM_ID,'
168 ||' USING_ASSEMBLY_DEMAND_DATE,'
169 ||' ASSEMBLY_DEMAND_COMP_DATE,'
170 ||' USING_REQUIREMENT_QUANTITY,'
171 ||' DEMAND_CLASS,'
172 ||' ORDER_PRIORITY,'
173 ||' FORECAST_MAD,'
174 ||' CONFIDENCE_PERCENTAGE,'
175 ||' BUCKET_TYPE,'
176 ||' SOURCE_ORGANIZATION_ID,'
177 ||' PROJECT_ID,'
178 ||' TASK_ID,'
179 ||' CUSTOMER_ID,'
180 ||' FORECAST_DESIGNATOR,'
181 ||' DELETED_FLAG,'
182 ||' ORIGINATION_TYPE,'
183 ||' DEMAND_TYPE,'
184 ||' REFRESH_ID,'
185 ||' PLANNING_GROUP,'
186 ||' SR_INSTANCE_ID,'
187 ||' SOURCE_SALES_ORDER_LINE_ID)'
188 ||' select '
189 ||' x.INVENTORY_ITEM_ID,'
190 ||' x.TRANSACTION_ID,'
191 ||' x.ORGANIZATION_ID,'
192 ||' x.USING_ASSEMBLY_ID,'
193 ||' x.FORECAST_DATE,'
194 ||' x.RATE_END_DATE,'
195 ||' x.ORIGINAL_FORECAST_QUANTITY,'
196 ||' x.DEMAND_CLASS,'
197 ||' TO_NUMBER(DECODE( :v_msc_fcst_priority_flex_num,'
198 ||' 1, x.Attribute1,'
199 ||' 2, x.Attribute2,'
200 ||' 3, x.Attribute3,'
201 ||' 4, x.Attribute4,'
202 ||' 5, x.Attribute5,'
203 ||' 6, x.Attribute6,'
204 ||' 7, x.Attribute7,'
205 ||' 8, x.Attribute8,'
206 ||' 9, x.Attribute9,'
207 ||' 10, x.Attribute10,'
208 ||' 11, x.Attribute11,'
209 ||' 12, x.Attribute12,'
210 ||' 13, x.Attribute13,'
211 ||' 14, x.Attribute14,'
212 ||' 15, x.Attribute15,NULL)),'
213 ||' x.FORECAST_MAD,'
214 ||' x.CONFIDENCE_PERCENTAGE,'
215 ||' x.BUCKET_TYPE,'
216 ||' x.SOURCE_ORGANIZATION_ID,'
217 ||' x.PROJECT_ID,'
218 ||' x.TASK_ID,'
219 ||' x.CUSTOMER_ID,'
220 ||' x.FORECAST_DESIGNATOR,'
221 ||' 2,'
222 ||' x.origination_type,'
223 ||' x.demand_type,'
224 ||' :v_refresh_id,'
225 ||' x.PLANNING_GROUP,'
226 ||' :v_instance_id,'
227 ||' x.TRANSACTION_ID '
228 ||' from MRP_AP_FORECAST_DEMAND_V'||MSC_CL_PULL.v_dblink||' x'
229 ||' WHERE x.ORGANIZATION_ID'||MSC_UTIL.v_in_org_str
230 ||' AND x.RN1>'||MSC_CL_PULL.v_lrn;
231
232
233 EXECUTE IMMEDIATE v_sql_stmt
234 USING MSC_CL_PULL.v_msc_fcst_priority_flex_num,
235 MSC_CL_PULL.v_refresh_id,
236 MSC_CL_PULL.v_instance_id;
237
238 COMMIT;
239
240 END IF;
241
242 END LOAD_ITEM_FORECASTS;
243
244 --==================================================================
245
246 PROCEDURE LOAD_MDS_DEMAND IS
247 BEGIN
248
249 IF MSC_CL_PULL.MDS_ENABLED= MSC_UTIL.SYS_YES THEN
250
251 --=================== Net Change Mode: Delete ==================
252
253 IF MSC_CL_PULL.v_lrnn<> -1 THEN -- incremental refresh
254
255 MSC_CL_PULL.v_table_name:= 'MSC_ST_DEMANDS';
256 MSC_CL_PULL.v_view_name := 'MRP_AD_MDS_DEMANDS_V';
257
258 v_sql_stmt:=
259 'insert into MSC_ST_DEMANDS'
260 ||' ( DISPOSITION_ID,'
261 ||' INVENTORY_ITEM_ID,'
262 ||' ORGANIZATION_ID,'
263 ||' USING_ASSEMBLY_ITEM_ID,'
264 ||' ORIGINATION_TYPE,'
265 ||' DELETED_FLAG,'
266 ||' REFRESH_ID,'
267 ||' SR_INSTANCE_ID)'
268 ||' select'
269 ||' x.DISPOSITION_ID,'
270 ||' x.INVENTORY_ITEM_ID,'
271 ||' x.ORGANIZATION_ID,'
272 ||' x.USING_ASSEMBLY_ID,'
273 ||' x.ORIGINATION_TYPE,'
274 ||' 1,'
275 ||' :v_refresh_id,'
276 ||' :v_instance_id'
277 ||' from MRP_AD_MDS_DEMANDS_V'||MSC_CL_PULL.v_dblink||' x'
278 ||' WHERE x.RN>'||MSC_CL_PULL.v_lrn
279 ||' AND x.ORGANIZATION_ID'||MSC_UTIL.v_in_org_str;
280
281 EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
282
283 COMMIT;
284
285 END IF;
286
287 IF (MSC_CL_PULL.v_apps_ver >= MSC_UTIL.G_APPS115) THEN
288
289 v_temp_sql := 'x.original_system_line_reference,x.original_system_reference,x.demand_source_type,x.demand_class,x.PROMISE_DATE,x.LINK_TO_LINE_ID,x.ORDER_DATE_TYPE_CODE,x.SCHEDULE_ARRIVAL_DATE,x.LATEST_ACCEPTABLE_DATE,x.SHIPPING_METHOD_CODE, ';
290
291
292 v_temp_sql1 := ' TO_NUMBER(DECODE( x.Schedule_Origination_Type, '
293 ||' 2,DECODE(:v_mso_fcst_penalty,'
294 ||' 1, x.Attribute1,'
295 ||' 2, x.Attribute2,'
296 ||' 3, x.Attribute3,'
297 ||' 4, x.Attribute4,'
298 ||' 5, x.Attribute5,'
299 ||' 6, x.Attribute6,'
300 ||' 7, x.Attribute7,'
301 ||' 8, x.Attribute8,'
302 ||' 9, x.Attribute9,'
303 ||' 10, x.Attribute10,'
304 ||' 11, x.Attribute11,'
305 ||' 12, x.Attribute12,'
306 ||' 13, x.Attribute13,'
307 ||' 14, x.Attribute14,'
308 ||' 15, x.Attribute15,NULL),'
309 ||' 3,x.LATE_DEMAND_PENALTY_FACTOR) ), ';
310 ELSE
311
312 IF (MSC_CL_PULL.v_apps_ver = MSC_UTIL.G_APPS110) THEN
313 v_temp_sql :='x.original_system_line_reference ,x.original_system_reference,x.demand_source_type,NULL,x.PROMISE_DATE,NULL,';
314 ELSE
315 v_temp_sql :=' NULL, NULL,NULL,NULL,NULL,NULL,NULL, NULL,NULL,NULL, ';
316 END IF;
317
318 v_temp_sql1 := ' TO_NUMBER(DECODE('
319 ||' DECODE(x.Schedule_Origination_Type,'
320 ||' 2,:v_mso_fcst_penalty,'
321 ||' 3,:v_mso_so_penalty),'
322 ||' 1, x.Attribute1,'
323 ||' 2, x.Attribute2,'
324 ||' 3, x.Attribute3,'
325 ||' 4, x.Attribute4,'
326 ||' 5, x.Attribute5,'
327 ||' 6, x.Attribute6,'
328 ||' 7, x.Attribute7,'
329 ||' 8, x.Attribute8,'
330 ||' 9, x.Attribute9,'
331 ||' 10, x.Attribute10,'
332 ||' 11, x.Attribute11,'
333 ||' 12, x.Attribute12,'
334 ||' 13, x.Attribute13,'
335 ||' 14, x.Attribute14,'
336 ||' 15, x.Attribute15)),';
337 END IF;
338
339 MSC_CL_PULL.v_table_name:= 'MSC_ST_DEMANDS';
340 MSC_CL_PULL.v_view_name := 'MRP_AP_MDS_DEMANDS_V';
341 IF MSC_CL_PULL.v_lrnn<> -1 THEN -- incremental refresh
342 v_union_sql :=
343 ' AND ( x.RN2>'||MSC_CL_PULL.v_lrn||')' --NCP: changed to RN2
344 ||' UNION '
345 ||' select'
346 ||' x.INVENTORY_ITEM_ID,'
347 ||' x.ORGANIZATION_ID,'
348 ||' x.USING_ASSEMBLY_ID,'
349 ||' x.SCHEDULE_WORKDATE- :v_dgmt,'
350 ||' x.USING_REQUIREMENTS_QUANTITY,'
351 ||' x.ASSEMBLY_DEMAND_COMP_DATE -:v_dgmt,'
352 ||' x.DEMAND_TYPE,'
353 ||' x.DAILY_DEMAND_RATE,'
354 ||' x.ORIGINATION_TYPE,'
355 ||' x.SOURCE_ORGANIZATION_ID,'
356 ||' x.DISPOSITION_ID,'
357 ||' x.DISPOSITION_ID,'
358 ||' x.RESERVATION_ID,'
359 ||' x.DEMAND_SCHEDULE_NAME,'
360 ||' x.SALES_ORDER_NUMBER,'
361 ||' x.PROJECT_ID,'
362 ||' x.TASK_ID,'
363 ||' x.PLANNING_GROUP,'
364 ||' x.END_ITEM_UNIT_NUMBER,'
365 ||' x.SCHEDULE_DATE- :v_dgmt,'
366 ||' x.LIST_PRICE,'
367 || v_temp_sql1
368 ||' x.REQUEST_DATE,'
369 ||' TO_NUMBER(NVL(DECODE(:v_msc_dmd_priority_flex_num,'
370 ||' 1, x.Attribute21,'
371 ||' 2, x.Attribute22,'
372 ||' 3, x.Attribute23,'
376 ||' 7, x.Attribute27,'
373 ||' 4, x.Attribute24,'
374 ||' 5, x.Attribute25,'
375 ||' 6, x.Attribute26,'
377 ||' 8, x.Attribute28,'
378 ||' 9, x.Attribute29,'
379 ||' 10, x.Attribute30,'
380 ||' 11, x.Attribute31,'
381 ||' 12, x.Attribute32,'
382 ||' 13, x.Attribute33,'
383 ||' 14, x.Attribute34,'
384 ||' 15, x.Attribute35),'
385 ||' DECODE(x.Schedule_Origination_Type,'
386 ||' 2, DECODE(:v_msc_fcst_priority_flex_num,'
387 ||' 1, x.Attribute1,'
388 ||' 2, x.Attribute2,'
389 ||' 3, x.Attribute3,'
390 ||' 4, x.Attribute4,'
391 ||' 5, x.Attribute5,'
392 ||' 6, x.Attribute6,'
393 ||' 7, x.Attribute7,'
394 ||' 8, x.Attribute8,'
395 ||' 9, x.Attribute9,'
396 ||' 10, x.Attribute10,'
397 ||' 11, x.Attribute11,'
398 ||' 12, x.Attribute12,'
399 ||' 13, x.Attribute13,'
400 ||' 14, x.Attribute14,'
401 ||' 15, x.Attribute15),'
402 ||' 3, x.SALES_ORDER_PRIORITY))),'
403 ||' x.SALES_ORDER_LINE_ID,'
404 ||' x.CUSTOMER_ID,'
405 ||' x.SHIP_TO_SITE_ID,'
406 ||' 2,'
407 || v_temp_sql
408 ||' :v_refresh_id,'
409 ||' :v_instance_id'
410 ||' from MRP_AP_MDS_DEMANDS_V'||MSC_CL_PULL.v_dblink||' x'
411 ||' WHERE x.ORGANIZATION_ID'||MSC_UTIL.v_in_org_str
412 ||' AND ( x.RN3>'||MSC_CL_PULL.v_lrn||')'; --NCP: changed to RN3
413
414 ELSE
415 v_union_sql :=
416 ' AND (x.RN1>'||MSC_CL_PULL.v_lrn
417 ||' OR x.RN2>'||MSC_CL_PULL.v_lrn
418 ||' OR x.RN3>'||MSC_CL_PULL.v_lrn
419 ||' OR x.RN4>'||MSC_CL_PULL.v_lrn||')';
420
421 END IF;
422
423 v_sql_stmt:=
424 'insert into MSC_ST_DEMANDS'
425 ||' ( INVENTORY_ITEM_ID,'
426 ||' ORGANIZATION_ID,'
427 ||' USING_ASSEMBLY_ITEM_ID,'
428 ||' USING_ASSEMBLY_DEMAND_DATE,'
429 ||' USING_REQUIREMENT_QUANTITY,'
430 ||' ASSEMBLY_DEMAND_COMP_DATE,'
431 ||' DEMAND_TYPE,'
432 ||' DAILY_DEMAND_RATE,'
433 ||' ORIGINATION_TYPE,'
434 ||' SOURCE_ORGANIZATION_ID,'
435 ||' DISPOSITION_ID,'
436 ||' SOURCE_DISPOSITION_ID,'
437 ||' RESERVATION_ID,'
438 ||' DEMAND_SCHEDULE_NAME,'
439 ||' ORDER_NUMBER,'
440 ||' PROJECT_ID,'
441 ||' TASK_ID,'
442 ||' PLANNING_GROUP,'
443 ||' END_ITEM_UNIT_NUMBER,'
444 ||' SCHEDULE_DATE,'
445 ||' SELLING_PRICE,'
446 ||' DMD_LATENESS_COST,'
447 ||' REQUEST_DATE,'
448 ||' ORDER_PRIORITY,'
449 ||' SALES_ORDER_LINE_ID,'
450 ||' CUSTOMER_ID,'
451 ||' SHIP_TO_SITE_ID,'
452 ||' DELETED_FLAG,'
453 ||' ORIGINAL_SYSTEM_LINE_REFERENCE,'
454 ||' ORIGINAL_SYSTEM_REFERENCE,'
455 ||' DEMAND_SOURCE_TYPE,'
456 ||' DEMAND_CLASS,'
457 ||' PROMISE_DATE,'
458 ||' LINK_TO_LINE_ID,'
459 ||' ORDER_DATE_TYPE_CODE,'
460 ||' SCHEDULE_ARRIVAL_DATE,'
461 ||' LATEST_ACCEPTABLE_DATE,'
462 ||' SHIPPING_METHOD_CODE,'
463 ||' REFRESH_ID,'
464 ||' SR_INSTANCE_ID)'
465 ||' select'
466 ||' x.INVENTORY_ITEM_ID,'
467 ||' x.ORGANIZATION_ID,'
468 ||' x.USING_ASSEMBLY_ID,'
469 ||' x.SCHEDULE_WORKDATE- :v_dgmt,'
470 ||' x.USING_REQUIREMENTS_QUANTITY,'
471 ||' x.ASSEMBLY_DEMAND_COMP_DATE -:v_dgmt,'
472 ||' x.DEMAND_TYPE,'
473 ||' x.DAILY_DEMAND_RATE,'
474 ||' x.ORIGINATION_TYPE,'
475 ||' x.SOURCE_ORGANIZATION_ID,'
476 ||' x.DISPOSITION_ID,'
477 ||' x.DISPOSITION_ID,'
478 ||' x.RESERVATION_ID,'
479 ||' x.DEMAND_SCHEDULE_NAME,'
480 ||' x.SALES_ORDER_NUMBER,'
481 ||' x.PROJECT_ID,'
482 ||' x.TASK_ID,'
483 ||' x.PLANNING_GROUP,'
484 ||' x.END_ITEM_UNIT_NUMBER,'
485 ||' x.SCHEDULE_DATE- :v_dgmt,'
486 ||' x.LIST_PRICE,'
487 || v_temp_sql1
488 ||' x.REQUEST_DATE,'
489 ||' TO_NUMBER(NVL(DECODE(:v_msc_dmd_priority_flex_num,'
490 ||' 1, x.Attribute21,'
491 ||' 2, x.Attribute22,'
492 ||' 3, x.Attribute23,'
493 ||' 4, x.Attribute24,'
494 ||' 5, x.Attribute25,'
495 ||' 6, x.Attribute26,'
496 ||' 7, x.Attribute27,'
497 ||' 8, x.Attribute28,'
498 ||' 9, x.Attribute29,'
499 ||' 10, x.Attribute30,'
500 ||' 11, x.Attribute31,'
501 ||' 12, x.Attribute32,'
502 ||' 13, x.Attribute33,'
503 ||' 14, x.Attribute34,'
504 ||' 15, x.Attribute35),'
505 ||' DECODE(x.Schedule_Origination_Type,'
506 ||' 2, DECODE(:v_msc_fcst_priority_flex_num,'
507 ||' 1, x.Attribute1,'
508 ||' 2, x.Attribute2,'
509 ||' 3, x.Attribute3,'
510 ||' 4, x.Attribute4,'
511 ||' 5, x.Attribute5,'
512 ||' 6, x.Attribute6,'
513 ||' 7, x.Attribute7,'
517 ||' 11, x.Attribute11,'
514 ||' 8, x.Attribute8,'
515 ||' 9, x.Attribute9,'
516 ||' 10, x.Attribute10,'
518 ||' 12, x.Attribute12,'
519 ||' 13, x.Attribute13,'
520 ||' 14, x.Attribute14,'
521 ||' 15, x.Attribute15),'
522 ||' 3, x.SALES_ORDER_PRIORITY))),'
523 ||' x.SALES_ORDER_LINE_ID,'
524 ||' x.CUSTOMER_ID,'
525 ||' x.SHIP_TO_SITE_ID,'
526 ||' 2,'
527 || v_temp_sql
528 ||' :v_refresh_id,'
529 ||' :v_instance_id'
530 ||' from MRP_AP_MDS_DEMANDS_V'||MSC_CL_PULL.v_dblink||' x'
531 ||' WHERE x.ORGANIZATION_ID'||MSC_UTIL.v_in_org_str
532 || v_union_sql ;
533
534 IF MSC_CL_PULL.v_lrnn<> -1 THEN -- incremental refresh
535 IF (MSC_CL_PULL.v_apps_ver >= MSC_UTIL.G_APPS115) THEN
536 EXECUTE IMMEDIATE v_sql_stmt
537 USING MSC_CL_PULL.v_dgmt,
538 MSC_CL_PULL.v_dgmt,
539 MSC_CL_PULL.v_dgmt,
540 MSC_CL_PULL.v_mso_fcst_penalty,
541 MSC_CL_PULL.v_msc_dmd_priority_flex_num,
542 MSC_CL_PULL.v_msc_fcst_priority_flex_num,
543 MSC_CL_PULL.v_refresh_id,
544 MSC_CL_PULL.v_instance_id,
545 MSC_CL_PULL.v_dgmt,
546 MSC_CL_PULL.v_dgmt,
547 MSC_CL_PULL.v_dgmt,
548 MSC_CL_PULL.v_mso_fcst_penalty,
549 MSC_CL_PULL.v_msc_dmd_priority_flex_num,
550 MSC_CL_PULL.v_msc_fcst_priority_flex_num,
551 MSC_CL_PULL.v_refresh_id,
552 MSC_CL_PULL.v_instance_id;
553 ELSE
554 EXECUTE IMMEDIATE v_sql_stmt
555 USING MSC_CL_PULL.v_dgmt,
556 MSC_CL_PULL.v_dgmt,
557 MSC_CL_PULL.v_dgmt,
558 MSC_CL_PULL.v_mso_fcst_penalty,
559 MSC_CL_PULL.v_mso_so_penalty,
560 MSC_CL_PULL.v_msc_dmd_priority_flex_num,
561 MSC_CL_PULL.v_msc_fcst_priority_flex_num,
562 MSC_CL_PULL.v_refresh_id,
563 MSC_CL_PULL.v_instance_id,
564 MSC_CL_PULL.v_dgmt,
565 MSC_CL_PULL.v_dgmt,
566 MSC_CL_PULL.v_dgmt,
567 MSC_CL_PULL.v_mso_fcst_penalty,
568 MSC_CL_PULL.v_mso_so_penalty,
569 MSC_CL_PULL.v_msc_dmd_priority_flex_num,
570 MSC_CL_PULL.v_msc_fcst_priority_flex_num,
571 MSC_CL_PULL.v_refresh_id,
572 MSC_CL_PULL.v_instance_id;
573 END IF;
574
575 ELSE -- Targeted - Complete Refresh collections
576 IF (MSC_CL_PULL.v_apps_ver >= MSC_UTIL.G_APPS115) THEN
577 EXECUTE IMMEDIATE v_sql_stmt
578 USING MSC_CL_PULL.v_dgmt,
579 MSC_CL_PULL.v_dgmt,
580 MSC_CL_PULL.v_dgmt,
581 MSC_CL_PULL.v_mso_fcst_penalty,
582 MSC_CL_PULL.v_msc_dmd_priority_flex_num,
583 MSC_CL_PULL.v_msc_fcst_priority_flex_num,
584 MSC_CL_PULL.v_refresh_id,
585 MSC_CL_PULL.v_instance_id;
586 ELSE
587 EXECUTE IMMEDIATE v_sql_stmt
588 USING MSC_CL_PULL.v_dgmt,
589 MSC_CL_PULL.v_dgmt,
590 MSC_CL_PULL.v_dgmt,
591 MSC_CL_PULL.v_mso_fcst_penalty,
592 MSC_CL_PULL.v_mso_so_penalty,
593 MSC_CL_PULL.v_msc_dmd_priority_flex_num,
594 MSC_CL_PULL.v_msc_fcst_priority_flex_num,
595 MSC_CL_PULL.v_refresh_id,
596 MSC_CL_PULL.v_instance_id;
597 END IF;
598
599 END IF;
600
601 COMMIT;
602
603 END IF; -- MSC_CL_PULL.MDS_ENABLED
604
605 END LOAD_MDS_DEMAND;
606
607
608 PROCEDURE LOAD_SALES_ORDER ( p_worker_num IN NUMBER ) IS
609 lv_temp_sql VARCHAR2(1024);
610 v_select_sql varchar2(100);
611 BEGIN
612
613 v_union_sql := ' ';
614 v_temp_sql4 := NULL;
615
616 if(MSC_CL_PULL.v_apps_ver>= MSC_UTIL.G_APPS115) then
617 v_temp_sql1:='x.SCHEDULE_ARRIVAL_DATE,x.LATEST_ACCEPTABLE_DATE,x.SHIPPING_METHOD_CODE,x.ATO_LINE_ID,x.ORDER_DATE_TYPE_CODE,x.DELIVERY_LEAD_TIME ';
618 else
619 v_temp_sql1:='NULL,NULL,NULL,NULL,NULL,NULL ';
620 end if;
621
622
623 IF ( p_worker_num = 3) THEN
624
625 IF MSC_CL_PULL.v_lrnn<> -1 THEN -- incremental refresh
626
627 MSC_CL_PULL.v_table_name:= 'MSC_ST_SALES_ORDERS';
628 MSC_CL_PULL.v_view_name := 'MRP_AD_SALES_ORDERS_V';
629
630 Begin
631
632 IF MSC_CL_PULL.v_apps_ver = MSC_UTIL.G_APPS107 THEN
633 v_temp_sql:= ' 2 ,';
634 ELSE
635 v_temp_sql:= ' x.CTO_FLAG,';
636 END IF ;
637
638
639 End;
640 /*Added By raraghav */
641
642 IF MSC_CL_PULL.v_apps_ver >= MSC_UTIL.G_APPS115 THEN
643 lv_temp_sql := ' AND x.ORGANIZATION_ID '||MSC_UTIL.v_in_org_str;
644 ELSE
645 lv_temp_sql := NULL;
646 END IF;
647
648 v_sql_stmt:=
649 'insert into MSC_ST_SALES_ORDERS'
650 ||' ( DEMAND_ID,'
651 ||' ROW_TYPE,'
655 ||' CTO_FLAG,'
652 ||' PARENT_DEMAND_ID,'
653 ||' DELETED_FLAG,'
654 ||' RESERVATION_TYPE,'
656 ||' REFRESH_ID,'
657 ||' SR_INSTANCE_ID)'
658 ||' select'
659 ||' x.DEMAND_ID,'
660 ||' x.ROW_TYPE,' --row type
661 ||' x.PARENT_DEMAND_ID,'
662 ||' 1,'
663 ||' 2,'
664 ||' 2,'
665 ||' :v_refresh_id,'
666 ||' :v_instance_id'
667 ||' from MRP_AD_HARD_RESERVATIONS_V'||MSC_CL_PULL.v_dblink||' x'
668 ||' where x.RN> :v_lrn '
669 || lv_temp_sql;
670
671 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS,v_sql_stmt);
672 EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id, MSC_CL_PULL.v_lrn;
673
674 COMMIT;
675
676 v_sql_stmt:=
677 'insert into MSC_ST_SALES_ORDERS'
678 ||' ( DEMAND_ID,'
679 ||' ROW_TYPE,'
680 ||' PARENT_DEMAND_ID,'
681 ||' DELETED_FLAG,'
682 ||' RESERVATION_TYPE,'
683 ||' CTO_FLAG,'
684 ||' REFRESH_ID,'
685 ||' SR_INSTANCE_ID)'
686 ||' select'
687 ||' x.DEMAND_ID,'
688 ||' x.ROW_TYPE,'
689 ||' x.PARENT_DEMAND_ID,'
690 ||' 1,'
691 ||' 1,'
692 || v_temp_sql
693 ||' :v_refresh_id,'
694 ||' :v_instance_id'
695 ||' from MRP_AD_SALES_ORDERS_V'||MSC_CL_PULL.v_dblink||' x'
696 ||' WHERE x.RN> :v_lrn '
697 || lv_temp_sql;
698 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS,v_sql_stmt);
699 EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id, MSC_CL_PULL.v_lrn;
700
701 COMMIT;
702
703 END IF; --- MSC_CL_PULL.v_lrnn<> -1
704
705 MSC_CL_PULL.v_table_name:= 'MSC_ST_SALES_ORDERS';
706
707 IF MSC_CL_PULL.v_apps_ver < MSC_UTIL.G_APPS115 THEN -- 107 or 110 source instance
708 MSC_CL_PULL.v_view_name := 'MRP_AP_SALES_ORDERS_V';
709 v_temp_sql3 := ' AND (x.RN1 > :v_lrn OR x.RN2> :v_lrn OR x.RN3> :v_lrn )';
710
711 /* Changed for the fix 2521038, */
712 v_temp_sql2 := ' AND (x.PRIMARY_UOM_QUANTITY > x.COMPLETED_QUANTITY '
713 ||' OR (x.PRIMARY_UOM_QUANTITY = x.COMPLETED_QUANTITY '
714 ||' AND x.requirement_date >= trunc(sysdate - (' || v_msc_so_offset_days ||' )))) ';
715
716 v_temp_sql := ' NULL, NULL, NULL,NULL, NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL, ';
717
718 ELSE -- 11i source instance
719
720
721
722 v_temp_sql := ' x.END_ITEM_UNIT_NUMBER , x.ordered_item_id,x.ORIGINAL_INVENTORY_ITEM_ID, '
723 || ' x.LINK_TO_LINE_ID, x.cust_po_number,x.customer_line_number, x.MFG_LEAD_TIME, x.FIRM_DEMAND_FLAG, x.SHIP_SET_ID, x.ARRIVAL_SET_ID, x.SHIP_SET_NAME, x.ARRIVAL_SET_NAME, ';
724
725
726 IF (MSC_CL_PULL.v_lrnn <> -1) THEN -- incremental collections
727 v_temp_sql2 := ' AND (x.ORIGINAL_ORDERED_QUANTITY >= x.ORIGINAL_COMPLETED_QUANTITY) ';
728 MSC_CL_PULL.v_view_name := 'MRP_AN3_SALES_ORDERS_V';
729 v_temp_sql3 := ' AND (x.RN1 > :v_lrn OR x.RN2> :v_lrn )';
730
731 ELSE --- complete/targeted collections
732 v_temp_sql2 := ' AND (x.ORIGINAL_ORDERED_QUANTITY > x.ORIGINAL_COMPLETED_QUANTITY '
733 ||' OR (x.ORIGINAL_ORDERED_QUANTITY = x.ORIGINAL_COMPLETED_QUANTITY '
734 ||' AND x.requirement_date >= trunc(sysdate - (' || v_msc_so_offset_days ||' )))) ';
735 MSC_CL_PULL.v_view_name := 'MRP_AP3_SALES_ORDERS_V';
736 v_temp_sql3 := ' ';
737 END IF;
738 END IF;
739
740
741 ELSIF ( p_worker_num in (1,2) ) THEN
742
743 MSC_CL_PULL.v_table_name:= 'MSC_ST_SALES_ORDERS';
744
745 IF MSC_CL_PULL.v_lrnn<> -1 THEN -- incremental refresh
746 MSC_CL_PULL.v_view_name := 'MRP_AN'||p_worker_num||'_SALES_ORDERS_V';
747
748 if p_worker_num <> 2 then
749 if p_worker_num = 1 then
750 v_temp_sql3 := ' AND (x.RN1 > :v_lrn OR x.RN2> :v_lrn OR x.RN3> :v_lrn )';
751 /* elsif p_worker_num = 3 then
752 v_temp_sql3 := ' AND (x.RN1 > :v_lrn OR x.RN2> :v_lrn )'; */
753 end if;
754
755
756 elsif (p_worker_num = 2) then
757
758
759 v_temp_sql2 := ' ';
760
761 v_union_sql :=
762 ' UNION '
763 ||' SELECT /*+ first_rows leading(x.msik) use_nl(x.msik x.ool) */ '
764 ||' x.INVENTORY_ITEM_ID,'
765 ||' x.INVENTORY_ITEM_ID,'
766 ||' x.ORGANIZATION_ID,'
767 ||' x.PRIMARY_UOM_QUANTITY,'
768 ||' x.RESERVATION_TYPE,'
769 ||' x.RESERVATION_QUANTITY,'
770 ||' x.DEMAND_SOURCE_TYPE,'
771 ||' x.DEMAND_SOURCE_HEADER_ID,'
772 ||' x.COMPLETED_QUANTITY,'
773 ||' x.SUBINVENTORY,'
774 ||' x.DEMAND_CLASS,'
775 ||' x.REQUIREMENT_DATE,'
776 ||' x.DEMAND_SOURCE_LINE,'
777 ||' x.DEMAND_SOURCE_LINE,'
778 ||' x.DEMAND_SOURCE_DELIVERY,'
779 ||' x.DEMAND_SOURCE_NAME,'
780 ||' x.DEMAND_ID,'
781 ||' x.ROW_TYPE,'
782 ||' x.DEMAND_ID,'
783 ||' x.PARENT_DEMAND_ID,'
784 ||' x.SALES_ORDER_NUMBER,'
785 ||' x.FORECAST_VISIBLE,'
786 ||' x.DEMAND_VISIBLE,'
787 ||' x.SALESREP_CONTACT,';
788
792 v_union_sql := v_union_sql ||' NULL,';
789 if MSC_CL_PULL.v_apps_ver >= MSC_UTIL.G_APPS115 then
790 v_union_sql := v_union_sql ||' x.SALESREP_ID,';
791 else
793 end if;
794
795 v_union_sql := v_union_sql
796 ||' x.CUSTOMER_ID,'
797 ||' x.SHIP_TO_SITE_ID,'
798 ||' x.BILL_TO_SITE_ID,'
799 ||' x.REQUEST_DATE,'
800 ||' x.PROJECT_ID,'
801 ||' x.TASK_ID,'
802 ||' x.PLANNING_GROUP,'
803 ||' x.LIST_PRICE,'
804 ||' x.END_ITEM_UNIT_NUMBER , '
805 ||' x.ordered_item_id, '
806 ||' x.ORIGINAL_INVENTORY_ITEM_ID , '
807 ||' x.LINK_TO_LINE_ID ,'
808 ||' x.CUST_PO_NUMBER,'
809 ||' x.CUSTOMER_LINE_NUMBER,'
810 ||' x.MFG_LEAD_TIME,'
811 ||' x.FIRM_DEMAND_FLAG,'
812 ||' x.SHIP_SET_ID,'
813 ||' x.ARRIVAL_SET_ID,'
814 ||' x.SHIP_SET_NAME,'
815 ||' x.ARRIVAL_SET_NAME,'
816 ||' x.RN2,'
817 ||' 2,'
818 ||' x.original_system_line_reference , '
819 ||' x.original_system_reference ,'
820 ||' x.CTO_FLAG,'
821 ||' x.AVAILABLE_TO_MRP,'
822 ||' x.DEMAND_PRIORITY,'
823 ||' x.PROMISE_DATE,'
824 ||' :v_refresh_id,'
825 ||' :v_instance_id, '
826 || v_temp_sql1
827 ||' FROM '||MSC_CL_PULL.v_view_name||MSC_CL_PULL.v_dblink||' x'
828 ||' WHERE x.ORGANIZATION_ID '||MSC_UTIL.v_in_org_str
829 || v_temp_sql2
830 ||' and ( x.rn2 > :v_lrn ) ' ;
831
832 v_temp_sql3 := ' AND (x.RN1 > :v_lrn ) ';
833
834 end if;
835
836 ELSE
837 MSC_CL_PULL.v_view_name := 'MRP_AP'||p_worker_num||'_SALES_ORDERS_V';
838 v_temp_sql3 := ' ';
839
840 END IF;
841
842 v_temp_sql := ' x.END_ITEM_UNIT_NUMBER , x.ordered_item_id,x.ORIGINAL_INVENTORY_ITEM_ID , '
843 ||' x.LINK_TO_LINE_ID, x.cust_po_number,x.customer_line_number,x.MFG_LEAD_TIME,x.FIRM_DEMAND_FLAG,x.SHIP_SET_ID,x.ARRIVAL_SET_ID,x.SHIP_SET_NAME,x.ARRIVAL_SET_NAME, ';
844
845 IF (p_worker_num = 2 AND -- Bug 4245915
846 MSC_CL_PULL.v_apps_ver>= MSC_UTIL.G_APPS120) THEN --bug#5684183 (bcaru)
847 v_temp_sql4 := ' AND ( x.visible_demand_flag = ''Y'' OR (x.visible_demand_flag = ''N'' AND x.order_type='''||v_msc_x_vmi_om_order_type||''')) ';
848 END IF;
849
850 END IF; --- (p_worker_num = 4 ) condition
851
852
853 IF (MSC_CL_PULL.v_apps_ver= MSC_UTIL.G_APPS110 OR
854 MSC_CL_PULL.v_apps_ver>= MSC_UTIL.G_APPS115) THEN
855
856 /* Changed for the fix 2521038, note the views MRP_AP_SALES_ORDERS_V and MRP_AN_SALES_ORDERS_V
857 have also been changed for this. Only if the source is 115 the new columns ORIGINAL_ORDERED_QUANTITY,
858 ORIGINAL_COMPLETED_QUANTITY are applicable.
859 */
860
861 IF MSC_CL_PULL.v_apps_ver = MSC_UTIL.G_APPS110 THEN
862
863 v_temp_sql2 := ' AND (x.PRIMARY_UOM_QUANTITY > x.COMPLETED_QUANTITY '
864 ||' OR (x.PRIMARY_UOM_QUANTITY = x.COMPLETED_QUANTITY ';
865
866 v_temp_sql2 := v_temp_sql2 ||' AND x.requirement_date >= trunc( sysdate - (' || v_msc_so_offset_days ||' )))) ';
867
868 END IF; /* MSC_UTIL.G_APPS110 */
869
870 IF MSC_CL_PULL.v_apps_ver>= MSC_UTIL.G_APPS115 AND (p_worker_num <> 2) THEN
871 IF (MSC_CL_PULL.v_lrnn <> -1) THEN -- incremental collections
872 v_temp_sql2 := ' AND (x.ORIGINAL_ORDERED_QUANTITY >= x.ORIGINAL_COMPLETED_QUANTITY) ';
873
874 ELSE
875 v_temp_sql2 := 'AND (x.ORIGINAL_ORDERED_QUANTITY > x.ORIGINAL_COMPLETED_QUANTITY '
876 ||' OR (x.ORIGINAL_ORDERED_QUANTITY = x.ORIGINAL_COMPLETED_QUANTITY ';
877
878 v_temp_sql2 := v_temp_sql2 ||' AND x.requirement_date >= trunc(sysdate - (' || v_msc_so_offset_days ||' )))) ';
879
880 END IF ;
881
882 END IF;
883
884 IF (p_worker_num = 2) AND (v_msc_so_offset_days = 99999 ) THEN
885 /* This will handle AP2 and AN2 - first union */
886 v_temp_sql2 := ' ';
887 END IF;
888
889 IF MSC_CL_PULL.v_view_name = 'MRP_AP2_SALES_ORDERS_V' THEN /* Bug 3019053 */
890 v_temp_sql2 := ' ';
891 END IF;
892
893 IF MSC_CL_PULL.v_view_name = 'MRP_AN2_SALES_ORDERS_V' THEN
894 v_select_sql := ' SELECT /*+ index( x.ool oe_odr_lines_sn_n1) */ ';
895 ELSE
896 v_select_sql := ' SELECT ';
897 END IF;
898
899 v_sql_stmt:=
900 'INSERT INTO MSC_ST_SALES_ORDERS'
901 ||' ( INVENTORY_ITEM_ID,'
902 ||' SOURCE_INVENTORY_ITEM_ID,'
903 ||' ORGANIZATION_ID,'
904 ||' PRIMARY_UOM_QUANTITY,'
905 ||' RESERVATION_TYPE,'
906 ||' RESERVATION_QUANTITY,'
907 ||' DEMAND_SOURCE_TYPE,'
908 ||' DEMAND_SOURCE_HEADER_ID,'
909 ||' COMPLETED_QUANTITY,'
910 ||' SUBINVENTORY,'
911 ||' DEMAND_CLASS,'
912 ||' REQUIREMENT_DATE,'
913 ||' DEMAND_SOURCE_LINE,'
914 ||' SOURCE_DEMAND_SOURCE_LINE,'
915 ||' DEMAND_SOURCE_DELIVERY,'
916 ||' DEMAND_SOURCE_NAME,'
917 ||' DEMAND_ID,'
918 ||' ROW_TYPE,'
919 ||' SOURCE_DEMAND_ID,'
920 ||' PARENT_DEMAND_ID,'
921 ||' SALES_ORDER_NUMBER,'
922 ||' FORECAST_VISIBLE,'
923 ||' DEMAND_VISIBLE,'
924 ||' SALESREP_CONTACT,'
925 ||' SALESREP_ID,'
926 ||' CUSTOMER_ID,'
927 ||' SHIP_TO_SITE_USE_ID,'
928 ||' BILL_TO_SITE_USE_ID,'
929 ||' REQUEST_DATE,'
930 ||' PROJECT_ID,'
931 ||' TASK_ID,'
932 ||' PLANNING_GROUP,'
933 ||' SELLING_PRICE,'
934 ||' END_ITEM_UNIT_NUMBER,'
938 ||' CUST_PO_NUMBER,'
935 ||' ORDERED_ITEM_ID,'
936 ||' ORIGINAL_ITEM_ID,'
937 ||' LINK_TO_LINE_ID ,'
939 ||' CUSTOMER_LINE_NUMBER,'
940 ||' MFG_LEAD_TIME,'
941 ||' ORG_FIRM_FLAG,'
942 ||' SHIP_SET_ID,'
943 ||' ARRIVAL_SET_ID,'
944 ||' SHIP_SET_NAME,'
945 ||' ARRIVAL_SET_NAME,'
946 ||' ATP_REFRESH_NUMBER,'
947 ||' DELETED_FLAG,'
948 ||' ORIGINAL_SYSTEM_LINE_REFERENCE,'
949 ||' ORIGINAL_SYSTEM_REFERENCE,'
950 ||' CTO_FLAG,'
951 ||' AVAILABLE_TO_MRP,'
952 ||' DEMAND_PRIORITY,'
953 ||' PROMISE_DATE,'
954 ||' REFRESH_ID,'
955 ||' SR_INSTANCE_ID,'
956 ||' SCHEDULE_ARRIVAL_DATE,'
957 ||' LATEST_ACCEPTABLE_DATE,'
958 ||' SHIPPING_METHOD_CODE,'
959 ||' ATO_LINE_ID,'
960 ||' ORDER_DATE_TYPE_CODE,'
961 ||' INTRANSIT_LEAD_TIME)'
962 ||v_select_sql
963 ||' x.INVENTORY_ITEM_ID,'
964 ||' x.INVENTORY_ITEM_ID,'
965 ||' x.ORGANIZATION_ID,'
966 ||' x.PRIMARY_UOM_QUANTITY,'
967 ||' x.RESERVATION_TYPE,'
968 ||' x.RESERVATION_QUANTITY,'
969 ||' x.DEMAND_SOURCE_TYPE,'
970 ||' x.DEMAND_SOURCE_HEADER_ID,'
971 ||' x.COMPLETED_QUANTITY,'
972 ||' x.SUBINVENTORY,'
973 ||' x.DEMAND_CLASS,'
974 ||' x.REQUIREMENT_DATE,'
975 ||' x.DEMAND_SOURCE_LINE,'
976 ||' x.DEMAND_SOURCE_LINE,'
977 ||' x.DEMAND_SOURCE_DELIVERY,'
978 ||' x.DEMAND_SOURCE_NAME,'
979 ||' x.DEMAND_ID,'
980 ||' x.ROW_TYPE,'
981 ||' x.DEMAND_ID,'
982 ||' x.PARENT_DEMAND_ID,'
983 ||' x.SALES_ORDER_NUMBER,'
984 ||' x.FORECAST_VISIBLE,'
985 ||' x.DEMAND_VISIBLE,'
986 ||' x.SALESREP_CONTACT,';
987
988 if MSC_CL_PULL.v_apps_ver >= MSC_UTIL.G_APPS115 then
989 v_sql_stmt := v_sql_stmt ||' x.SALESREP_ID,';
990 else
991 v_sql_stmt := v_sql_stmt ||' NULL,';
992 end if;
993
994 v_sql_stmt := v_sql_stmt
995 ||' x.CUSTOMER_ID,'
996 ||' x.SHIP_TO_SITE_ID,'
997 ||' x.BILL_TO_SITE_ID,'
998 ||' x.REQUEST_DATE,'
999 ||' x.PROJECT_ID,'
1000 ||' x.TASK_ID,'
1001 ||' x.PLANNING_GROUP,'
1002 ||' x.LIST_PRICE,'
1003 || v_temp_sql
1004 ||' x.RN1,'
1005 ||' 2,'
1006 ||' x.ORIGINAL_SYSTEM_LINE_REFERENCE,'
1007 ||' x.ORIGINAL_SYSTEM_REFERENCE,'
1008 ||' x.CTO_FLAG,'
1009 ||' x.AVAILABLE_TO_MRP,'
1010 ||' x.DEMAND_PRIORITY,'
1011 ||' x.PROMISE_DATE,'
1012 ||' :v_refresh_id,'
1013 ||' :v_instance_id,'
1014 || v_temp_sql1
1015 ||' FROM '||MSC_CL_PULL.v_view_name||MSC_CL_PULL.v_dblink||' x'
1016 ||' WHERE x.ORGANIZATION_ID'||MSC_UTIL.v_in_org_str
1017 || v_temp_sql2
1018 || v_temp_sql3
1019 || v_temp_sql4
1020 || v_union_sql ;
1021
1022 IF MSC_CL_PULL.v_apps_ver < MSC_UTIL.G_APPS115 THEN
1023 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS,v_sql_stmt);
1024 EXECUTE IMMEDIATE v_sql_stmt
1025 USING MSC_CL_PULL.v_refresh_id,
1026 MSC_CL_PULL.v_instance_id,
1027 MSC_CL_PULL.v_lrn,
1028 MSC_CL_PULL.v_lrn,
1029 MSC_CL_PULL.v_lrn;
1030
1031 ELSE -- 11i source instance
1032
1033 IF (MSC_CL_PULL.v_lrnn <> -1) THEN -- incremental collections
1034 if (p_worker_num = 2) then
1035 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_2,v_sql_stmt);
1036 EXECUTE IMMEDIATE v_sql_stmt
1037 USING MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id, MSC_CL_PULL.v_lrn,
1038 MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id, MSC_CL_PULL.v_lrn;
1039 elsif (p_worker_num in (3)) then
1040 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_2,v_sql_stmt);
1041 EXECUTE IMMEDIATE v_sql_stmt USING
1042 MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id
1043 ,MSC_CL_PULL.v_lrn,MSC_CL_PULL.v_lrn;
1044
1045 else
1046 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_2,v_sql_stmt);
1047 EXECUTE IMMEDIATE v_sql_stmt
1048 USING MSC_CL_PULL.v_refresh_id,
1049 MSC_CL_PULL.v_instance_id,
1050 MSC_CL_PULL.v_lrn,
1051 MSC_CL_PULL.v_lrn,
1052 MSC_CL_PULL.v_lrn;
1053 end if;
1054
1055 ELSE --- complete/targeted collections
1056 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_2,v_sql_stmt);
1057 EXECUTE IMMEDIATE v_sql_stmt
1058 USING MSC_CL_PULL.v_refresh_id,
1059 MSC_CL_PULL.v_instance_id;
1060 END IF;
1061 END IF;
1062
1063 ELSE -- 107 source instance
1064
1065 v_sql_stmt:=
1066 'INSERT INTO MSC_ST_SALES_ORDERS'
1067 ||' ( INVENTORY_ITEM_ID,'
1068 ||' ORGANIZATION_ID,'
1069 ||' PRIMARY_UOM_QUANTITY,'
1070 ||' RESERVATION_TYPE,'
1071 ||' RESERVATION_QUANTITY,'
1072 ||' DEMAND_SOURCE_TYPE,'
1073 ||' DEMAND_SOURCE_HEADER_ID,'
1074 ||' COMPLETED_QUANTITY,'
1075 ||' SUBINVENTORY,'
1076 ||' DEMAND_CLASS,'
1077 ||' REQUIREMENT_DATE,'
1078 ||' DEMAND_SOURCE_LINE,'
1079 ||' DEMAND_SOURCE_DELIVERY,'
1080 ||' DEMAND_SOURCE_NAME,'
1081 ||' DEMAND_ID,'
1082 ||' ROW_TYPE,'
1083 ||' PARENT_DEMAND_ID,'
1084 ||' SALES_ORDER_NUMBER,'
1085 ||' SALESREP_CONTACT,'
1086 ||' CUSTOMER_ID,'
1087 ||' SHIP_TO_SITE_USE_ID,'
1088 ||' BILL_TO_SITE_USE_ID,'
1089 ||' ATP_REFRESH_NUMBER,'
1090 ||' DELETED_FLAG,'
1091 ||' REFRESH_ID,'
1092 ||' PROJECT_ID,'
1093 ||' TASK_ID,'
1094 ||' PLANNING_GROUP,'
1095 ||' SR_INSTANCE_ID)'
1096 ||' SELECT'
1100 ||' x.RESERVATION_TYPE,'
1097 ||' x.INVENTORY_ITEM_ID,'
1098 ||' x.ORGANIZATION_ID,'
1099 ||' x.PRIMARY_UOM_QUANTITY,'
1101 ||' x.RESERVATION_QUANTITY,'
1102 ||' x.DEMAND_SOURCE_TYPE,'
1103 ||' x.DEMAND_SOURCE_HEADER_ID,'
1104 ||' x.COMPLETED_QUANTITY,'
1105 ||' x.SUBINVENTORY,'
1106 ||' x.DEMAND_CLASS,'
1107 ||' x.REQUIREMENT_DATE,'
1108 ||' x.DEMAND_SOURCE_LINE,'
1109 ||' x.DEMAND_SOURCE_DELIVERY,'
1110 ||' x.DEMAND_SOURCE_NAME,'
1111 ||' x.DEMAND_ID,'
1112 ||' x.ROW_TYPE,'
1113 ||' x.PARENT_DEMAND_ID,'
1114 ||' x.SALES_ORDER_NUMBER,'
1115 ||' x.SALESREP_CONTACT,'
1116 ||' x.CUSTOMER_ID,'
1117 ||' x.SHIP_TO_SITE_ID,'
1118 ||' x.BILL_TO_SITE_ID,'
1119 ||' x.RN1,'
1120 ||' 2,'
1121 ||' :v_refresh_id,'
1122 ||' x.PROJECT_ID,'
1123 ||' x.TASK_ID,'
1124 ||' x.PLANNING_GROUP,'
1125 ||' :v_instance_id'
1126 ||' FROM MRP_AP_SALES_ORDERS_V'||MSC_CL_PULL.v_dblink||' x'
1127 ||' WHERE x.ORGANIZATION_ID'||MSC_UTIL.v_in_org_str
1128 ||' AND (x.RN1>'||MSC_CL_PULL.v_lrn
1129 ||' OR x.RN2>'||MSC_CL_PULL.v_lrn
1130 ||' OR x.RN3>'||MSC_CL_PULL.v_lrn||')';
1131
1132 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_2,v_sql_stmt);
1133 EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
1134
1135 END IF;
1136 COMMIT;
1137 END LOAD_SALES_ORDER;
1138
1139
1140 --==================================================================
1141
1142 PROCEDURE LOAD_HARD_RESERVATION IS
1143 BEGIN
1144
1145 IF MSC_CL_PULL.HARD_RESRVS_ENABLED= MSC_UTIL.SYS_YES THEN
1146
1147 IF MSC_CL_PULL.v_lrnn<> -1 THEN -- incremental refresh
1148
1149 MSC_CL_PULL.v_table_name:= 'MSC_ST_RESERVATIONS';
1150 MSC_CL_PULL.v_view_name := 'MRP_AD_HARD_RESERVATIONS_V';
1151
1152 IF MSC_CL_PULL.v_apps_ver >= MSC_UTIL.G_APPS115 THEN
1153 v_temp_sql := ' AND x.ORGANIZATION_ID '||MSC_UTIL.v_in_org_str;
1154 ELSE
1155 v_temp_sql := NULL;
1156 END IF;
1157
1158 v_sql_stmt:=
1159 'insert into MSC_ST_RESERVATIONS'
1160 ||' ( TRANSACTION_ID,'
1161 ||' PARENT_DEMAND_ID,'
1162 ||' DELETED_FLAG,'
1163 ||' REFRESH_ID,'
1164 ||' SR_INSTANCE_ID)'
1165 ||' select'
1166 ||' x.DEMAND_ID,'
1167 ||' x.PARENT_DEMAND_ID,'
1168 ||' 1,'
1169 ||' :v_refresh_id,'
1170 ||' :v_instance_id'
1171 ||' from MRP_AD_HARD_RESERVATIONS_V'||MSC_CL_PULL.v_dblink||' x'
1172 ||' WHERE x.RN>'||MSC_CL_PULL.v_lrn
1173 || v_temp_sql;
1174
1175 EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
1176
1177 COMMIT;
1178 /* Changes For Bug 6147734 */
1179 if (MSC_UTIL.G_COLLECT_SRP_DATA='Y' AND MSC_CL_PULL.v_apps_ver >= MSC_UTIL.G_APPS120) Then
1180 v_sql_stmt:=
1181 'insert into MSC_ST_RESERVATIONS'
1182 ||' ( TRANSACTION_ID,'
1183 ||' SUPPLY_SOURCE_TYPE_ID,'
1184 ||' ORGANIZATION_ID,'
1185 ||' INVENTORY_ITEM_ID,'
1186 ||' DISPOSITION_TYPE,'
1187 ||' DISPOSITION_ID,'
1188 ||' ORDER2_ORGANIZATION_ID,'
1189 ||' ORDER2_INVENTORY_ITEM_ID,'
1190 ||' DELETED_FLAG,'
1191 ||' REFRESH_ID,'
1192 ||' SR_INSTANCE_ID)'
1193 ||' select'
1194 ||' x.TRANSACTION_ID,'
1195 ||' x.SUPPLY_SOURCE_TYPE_ID,'
1196 ||' x.ORGANIZATION_ID,'
1197 ||' x.INVENTORY_ITEM_ID,'
1198 ||' x.DISPOSITION_TYPE,'
1199 ||' x.DISPOSITION_ID,'
1200 ||' x.ORDER2_ORGANIZATION_ID,'
1201 ||' x.ORDER2_INVENTORY_ITEM_ID,'
1202 ||' 1,'
1203 ||' :v_refresh_id,'
1204 ||' :v_instance_id'
1205 ||' from MRP_AD_REPAIR_RESERVATIONS_V'||MSC_CL_PULL.v_dblink||' x'
1206 ||' WHERE x.RN>'||MSC_CL_PULL.v_lrn
1207 ;
1208
1209
1210 EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
1211 COMMIT;
1212 End If; -- Srp Profile Check
1213 END IF; -- Incremental Refresh
1214
1215 IF MSC_CL_PULL.v_apps_ver >= MSC_UTIL.G_APPS115 THEN
1216 v_temp_sql := ' AND x.GET_ORDERED_QUANTITY > x.GET_SHIPPED_QUANTITY ';
1217 ELSE
1218 v_temp_sql := ' ';
1219 END IF;
1220
1221 IF MSC_CL_PULL.v_apps_ver >= MSC_UTIL.G_APPS115 THEN
1222 v_temp_sql1 := ' AND x.RN1 > :v_lrn';
1223 /* ds change */
1224 v_temp_sql2 := ' SUPPLY_SOURCE_HEADER_ID,SUPPLY_SOURCE_TYPE_ID, ';
1225 v_temp_sql3 := ' x.SUPPLY_SOURCE_HEADER_ID,x.SUPPLY_SOURCE_TYPE_ID, ';
1226 ELSE
1227 v_temp_sql1 := ' AND (x.RN1 > :v_lrn or x.RN2 > :v_lrn or x.RN3 > :v_lrn or x.RN4 > :v_lrn or x.RN5 > :v_lrn or x.RN6 > :v_lrn)';
1228 v_temp_sql2 := ' NULL,NULL, ';
1229 v_temp_sql3 := ' NULL.NULL, ';
1230 END IF;
1231
1232 MSC_CL_PULL.v_table_name:= 'MSC_ST_RESERVATIONS';
1233 MSC_CL_PULL.v_view_name := 'MRP_AP_HARD_RESERVATIONS_V';
1234
1235 v_sql_stmt:=
1236 'insert into MSC_ST_RESERVATIONS'
1237 ||' ( INVENTORY_ITEM_ID,'
1238 ||' ORGANIZATION_ID,'
1239 ||' TRANSACTION_ID,'
1240 ||' PARENT_DEMAND_ID,'
1241 ||' DISPOSITION_ID,'
1242 ||' REQUIREMENT_DATE,'
1246 ||' SUBINVENTORY,'
1243 ||' REVISION,'
1244 ||' RESERVED_QUANTITY,'
1245 ||' DISPOSITION_TYPE,'
1247 ||' RESERVATION_TYPE,'
1248 ||' DEMAND_CLASS,'
1249 ||' AVAILABLE_TO_MRP,'
1250 ||' RESERVATION_FLAG,'
1251 ||' PROJECT_ID,'
1252 ||' TASK_ID,'
1253 ||' PLANNING_GROUP,'
1254 || v_temp_sql2
1255 ||' DELETED_FLAG,'
1256 ||' REFRESH_ID,'
1257 ||' SR_INSTANCE_ID)'
1258 ||' select'
1259 ||' x.INVENTORY_ITEM_ID,'
1260 ||' x.ORGANIZATION_ID,'
1261 ||' x.DEMAND_ID,'
1262 ||' x.PARENT_DEMAND_ID,'
1263 ||' x.DISPOSITION_ID,'
1264 ||' x.REQUIREMENT_DATE- :v_dgmt,'
1265 ||' x.REVISION,'
1266 ||' x.RESERVED_QUANTITY,'
1267 ||' x.DISPOSITION_TYPE,'
1268 ||' x.SUBINVENTORY,'
1269 ||' x.RESERVATION_TYPE,'
1270 --||' DECODE( x.DEMAND_CLASS, NULL, NULL, :V_ICODE||x.DEMAND_CLASS),'
1271 ||' x.DEMAND_CLASS,'
1272 ||' x.AVAILABLE_TO_MRP,'
1273 ||' 2,'
1274 ||' x.PROJECT_ID,'
1275 ||' x.TASK_ID,'
1276 ||' x.PLANNING_GROUP,'
1277 || v_temp_sql3
1278 ||' 2,'
1279 ||' :v_refresh_id,'
1280 ||' :v_instance_id'
1281 ||' from MRP_AP_HARD_RESERVATIONS_V'||MSC_CL_PULL.v_dblink||' x'
1282 ||' WHERE x.ORGANIZATION_ID'||MSC_UTIL.v_in_org_str|| v_temp_sql || v_temp_sql1;
1283
1284 /*||' AND (' -- NCP: x.RN1>'||MSC_CL_PULL.v_lrn
1285 ||' x.RN2>'||MSC_CL_PULL.v_lrn||')';
1286
1287 ||' OR x.RN3>'||MSC_CL_PULL.v_lrn
1288 ||' OR x.RN4>'||MSC_CL_PULL.v_lrn
1289 ||' OR x.RN5>'||MSC_CL_PULL.v_lrn
1290 ||' OR x.RN6>'||MSC_CL_PULL.v_lrn||')'; */
1291
1292 --EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_dgmt, MSC_CL_PULL.V_ICODE, MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
1293 -- EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
1294
1295 IF MSC_CL_PULL.v_apps_ver >= MSC_UTIL.G_APPS115 THEN
1296 EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id, MSC_CL_PULL.v_lrn;
1297 ELSE
1298 EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id, MSC_CL_PULL.v_lrn, MSC_CL_PULL.v_lrn, MSC_CL_PULL.v_lrn, MSC_CL_PULL.v_lrn, MSC_CL_PULL.v_lrn, MSC_CL_PULL.v_lrn;
1299 END IF;
1300
1301 COMMIT;
1302
1303
1304 if (MSC_UTIL.G_COLLECT_SRP_DATA='Y' AND MSC_CL_PULL.v_apps_ver >= MSC_UTIL.G_APPS120) Then -- SRP Changes For Bug 5988024
1305
1306 IF MSC_CL_PULL.v_lrnn<> -1 THEN -- incremental refresh
1307 v_temp_sql1 := ' OR x.date1 > :date1 OR x.date2 > :date2 ';
1308 ELSE
1309 v_temp_sql1 := NULL;
1310 END IF;
1311
1312 v_sql_stmt:=
1313 'insert into MSC_ST_RESERVATIONS'
1314 ||' ( INVENTORY_ITEM_ID,'
1315 ||' ORGANIZATION_ID,'
1316 ||' TRANSACTION_ID,'
1317 ||' PARENT_DEMAND_ID,'
1318 ||' DISPOSITION_ID,'
1319 ||' REVISION,'
1320 ||' RESERVED_QUANTITY,'
1321 ||' DISPOSITION_TYPE,'
1322 ||' RESERVATION_TYPE,'
1323 ||' SUPPLY_SOURCE_TYPE_ID,'
1324 ||' PROJECT_ID,'
1325 ||' TASK_ID,'
1326 ||' DELETED_FLAG,'
1327 ||' REFRESH_ID,'
1328 ||' SR_INSTANCE_ID)'
1329 ||' select'
1330 ||' x.INVENTORY_ITEM_ID,'
1331 ||' x.ORGANIZATION_ID,'
1332 ||' x.DEMAND_ID,'
1333 ||' x.DISPOSITION_ID ,'
1334 ||' x.DISPOSITION_ID,'
1335 ||' x.REVISION,'
1336 ||' x.RESERVED_QUANTITY,'
1337 ||' x.DISPOSITION_TYPE,'
1338 ||' x.RESERVATION_TYPE,'
1339 ||' x.supply_source_type_id,'
1340 ||' x.PROJECT_ID,'
1341 ||' x.TASK_ID,'
1342 ||' 2,'
1343 ||' :v_refresh_id,'
1344 ||' :v_instance_id'
1345 ||' from MRP_AP_REPAIR_TRANSFERS_RESV_V'||MSC_CL_PULL.v_dblink||' x'
1346 ||' WHERE x.ORGANIZATION_ID '||MSC_UTIL.v_depot_org_str
1347 ||' AND x.RN1 > :v_lrn'
1348 || v_temp_sql1;
1349
1350
1351
1352 IF MSC_CL_PULL.v_lrnn<> -1 THEN -- incremental refresh
1353 EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id,
1354 MSC_CL_PULL.v_instance_id, MSC_CL_PULL.v_lrn,
1355 MSC_CL_PULL.g_LAST_SUCC_RES_REF_TIME,MSC_CL_PULL.g_LAST_SUCC_RES_REF_TIME;
1356 /* For Bug 6144734 */
1357 v_sql_stmt:=
1358 'Insert into MSC_ST_RESERVATIONS'
1359 ||' ( TRANSACTION_ID,'
1360 ||' SUPPLY_SOURCE_TYPE_ID,'
1361 ||' ORGANIZATION_ID,'
1362 ||' INVENTORY_ITEM_ID,'
1363 ||' DISPOSITION_TYPE,'
1364 ||' DISPOSITION_ID,'
1365 ||' ORDER2_ORGANIZATION_ID,'
1366 ||' ORDER2_INVENTORY_ITEM_ID,'
1367 ||' DELETED_FLAG,'
1368 ||' REFRESH_ID,'
1369 ||' SR_INSTANCE_ID)'
1370 ||' Select'
1371 ||' x.repair_line_id,'
1372 ||' 200,'
1373 ||' ORGANIZATION_ID,'
1374 ||' INVENTORY_ITEM_ID,'
1375 ||' null,'
1376 ||' null,'
1377 ||' null,'
1378 ||' null,'
1382 ||' from MRP_AP_REPAIR_ORDERS_V'||MSC_CL_PULL.v_dblink ||' x'
1379 ||' 1,'
1380 ||' :v_refresh_id,'
1381 ||' :v_instance_id'
1383 ||' where x.organization_id '||MSC_UTIL.v_depot_org_str
1384 || ' AND x.RO_STATUS_CODE = '||'''C'''
1385 ||' AND x. LAST_UPDATE_DATE > :date1' ;
1386
1387
1388
1389 EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id,
1390 MSC_CL_PULL.v_instance_id,
1391 MSC_CL_PULL.g_LAST_SUCC_RES_REF_TIME;
1392
1393 ELSE
1394 EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id,
1395 MSC_CL_PULL.v_instance_id, MSC_CL_PULL.v_lrn;
1396 END IF;
1397
1398
1399 commit;
1400 v_sql_stmt:=
1401 'insert into MSC_ST_RESERVATIONS'
1402 ||' ( INVENTORY_ITEM_ID,'
1403 ||' ORGANIZATION_ID,'
1404 ||' TRANSACTION_ID,'
1405 ||' PARENT_DEMAND_ID,'
1406 ||' DISPOSITION_ID,'
1407 ||' REVISION,'
1408 ||' RESERVED_QUANTITY,'
1409 ||' DISPOSITION_TYPE,'
1410 ||' RESERVATION_TYPE,'
1411 ||' REPAIR_PO_HEADER_ID,'
1412 ||' SUPPLY_SOURCE_TYPE_ID,'
1413 ||' PROJECT_ID,'
1414 ||' TASK_ID,'
1415 ||' DELETED_FLAG,'
1416 ||' REFRESH_ID,'
1417 ||' SR_INSTANCE_ID)'
1418 ||' select'
1419 ||' x.INVENTORY_ITEM_ID,'
1420 ||' x.ORGANIZATION_ID,'
1421 ||' x.DEMAND_ID,'
1422 ||' x.DISPOSITION_ID ,'
1423 ||' x.DISPOSITION_ID,'
1424 ||' x.REVISION,'
1425 ||' x.RESERVED_QUANTITY,'
1426 ||' x.DISPOSITION_TYPE,'
1427 ||' x.RESERVATION_TYPE,'
1428 ||' X.REPAIR_PO_HEADER_ID,'
1429 ||' x.SUPPLY_SOURCE_TYPE_ID,'
1430 ||' x.PROJECT_ID,'
1431 ||' x.TASK_ID,'
1432 ||' 2,'
1433 ||' :v_refresh_id,'
1434 ||' :v_instance_id'
1435 ||' from MRP_AP_EXT_REP_RESERVATIONS_V'||MSC_CL_PULL.v_dblink||' x'
1436 ||' WHERE x.ORGANIZATION_ID '||MSC_UTIL.v_in_org_str
1437 ||' AND ((x.RN > :v_lrn) OR (x.RN1 > :v_lrn) OR (x.RN2 > :v_lrn) OR (x.RN3 > :v_lrn)
1438 OR (x.RN4 > :v_lrn) OR (x.RN5 > :v_lrn) OR (x.RN6 > :v_lrn))'
1439 ;
1440
1441 /* Changed For bug 6144734 */
1442 EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id,
1443 MSC_CL_PULL.v_instance_id,
1444 MSC_CL_PULL.v_lrn,
1445 MSC_CL_PULL.v_lrn,
1446 MSC_CL_PULL.v_lrn,
1447 MSC_CL_PULL.v_lrn,
1448 MSC_CL_PULL.v_lrn,
1449 MSC_CL_PULL.v_lrn,
1450 MSC_CL_PULL.v_lrn;
1451
1452 commit;
1453
1454 END IF; -- SRP Changes For Bug 5988024
1455
1456 END IF;
1457
1458 END LOAD_HARD_RESERVATION;
1459
1460
1461 PROCEDURE LOAD_USER_DEMAND IS
1462 BEGIN
1463
1464 IF MSC_CL_PULL.v_apps_ver<> MSC_UTIL.G_APPS107 AND
1465 MSC_CL_PULL.v_apps_ver<> MSC_UTIL.G_APPS110 THEN
1466
1467 IF MSC_CL_PULL.v_lrnn<> -1 THEN -- incremental refresh
1468
1469 MSC_CL_PULL.v_table_name:= 'MSC_ST_DEMANDS';
1470 MSC_CL_PULL.v_view_name := 'MRP_AD_USER_DEMANDS_V';
1471
1472 IF MSC_CL_PULL.v_apps_ver >= MSC_UTIL.G_APPS115 THEN
1473 v_temp_sql := ' AND x.ORGANIZATION_ID '||MSC_UTIL.v_in_org_str;
1474 ELSE
1475 v_temp_sql := NULL;
1476 END IF;
1477
1478 v_sql_stmt:=
1479 ' INSERT INTO MSC_ST_DEMANDS'
1480 ||'( DISPOSITION_ID,'
1481 ||' ORIGINATION_TYPE,'
1482 ||' DELETED_FLAG,'
1483 ||' ORGANIZATION_ID,'
1484 ||' REFRESH_ID,'
1485 ||' SR_INSTANCE_ID)'
1486 ||' SELECT'
1487 ||' x.TRANSACTION_ID,'
1488 ||' x.ORIGINATION_TYPE,'
1489 ||' 1,'
1490 ||' x.ORGANIZATION_ID,'
1491 ||' :v_refresh_id,'
1492 ||' :v_instance_id'
1493 ||' FROM MRP_AD_USER_DEMANDS_V'||MSC_CL_PULL.v_dblink||' x'
1494 ||' WHERE x.RN>'||MSC_CL_PULL.v_lrn
1495 || v_temp_sql;
1496
1497 EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
1498
1499 COMMIT;
1500
1501 END IF;
1502
1503 MSC_CL_PULL.v_table_name:= 'MSC_ST_DEMANDS';
1504 MSC_CL_PULL.v_view_name := 'MRP_AP_USER_DEMANDS_V';
1505
1506 v_sql_stmt:=
1507 ' INSERT INTO MSC_ST_DEMANDS'
1508 ||'( DISPOSITION_ID,'
1509 ||' ORIGINATION_TYPE,'
1510 ||' INVENTORY_ITEM_ID,'
1511 ||' ORGANIZATION_ID,'
1512 ||' USING_ASSEMBLY_ITEM_ID,'
1513 ||' ORDER_NUMBER,'
1514 ||' USING_REQUIREMENT_QUANTITY,'
1515 ||' USING_ASSEMBLY_DEMAND_DATE,'
1516 ||' DEMAND_TYPE,'
1517 ||' DEMAND_CLASS,'
1518 ||' DELETED_FLAG,'
1519 ||' REFRESH_ID,'
1520 ||' SR_INSTANCE_ID)'
1521 ||' SELECT'
1522 ||' x.TRANSACTION_ID,'
1523 ||' x.ORIGINATION_TYPE,'
1524 ||' x.INVENTORY_ITEM_ID,'
1525 ||' x.ORGANIZATION_ID,'
1526 ||' x.INVENTORY_ITEM_ID,'
1527 ||' x.SOURCE_NAME,'
1528 ||' x.PRIMARY_UOM_QUANTITY,'
1529 ||' x.REQUIREMENT_DATE,'
1530 ||' 1,' -- demand type
1531 --||' DECODE( x.DEMAND_CLASS,NULL,NULL,:V_ICODE||x.DEMAND_CLASS),'
1532 ||' x.DEMAND_CLASS,'
1533 ||' 2,'
1534 ||' :v_refresh_id,'
1535 ||' :v_instance_id'
1539 ||' OR x.RN2>'||MSC_CL_PULL.v_lrn||')';
1536 ||' FROM MRP_AP_USER_DEMANDS_V'||MSC_CL_PULL.v_dblink||' x'
1537 ||' WHERE x.ORGANIZATION_ID'||MSC_UTIL.v_in_org_str
1538 ||' AND (x.RN1>'||MSC_CL_PULL.v_lrn
1540
1541 --EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.V_ICODE, MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
1542 EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
1543
1544 COMMIT;
1545
1546 END IF;
1547
1548 END LOAD_USER_DEMAND;
1549
1550
1551 --==================================================================
1552 /**************************************************************
1553 * LOAD AHL as Sales Orders for 11i.10 CMRO Integration
1554 ****************************************************************/
1555
1556 PROCEDURE LOAD_AHL IS
1557 lv_temp_sql VARCHAR2(1024);
1558 BEGIN
1559
1560
1561
1562 -- Not needed, Clean it up once it is coded
1563 -- MSC_CL_PULL.v_table_name:= 'MSC_ST_SALES_ORDERS';
1564
1565 IF MSC_CL_PULL.v_lrnn<> -1 THEN -- incremental refresh
1566 MSC_CL_PULL.v_view_name := 'MRP_AN_AHL_MTL_REQS_V';
1567 v_temp_sql3 := ' AND (x.RN1 > :v_lrn OR x.RN2> :v_lrn OR x.RN3> :v_lrn )';
1568
1569 ELSE
1570 MSC_CL_PULL.v_view_name := 'MRP_AP_AHL_MTL_REQS_V';
1571 v_temp_sql3 := ' ';
1572
1573 END IF;
1574
1575
1576
1577 /* In AHL You do not have to worry about planning for the past due visits as they
1578 do not exist any further */
1579 /******************************************************************************************
1580 v_temp_sql2 := ' AND (x.ORIGINAL_ORDERED_QUANTITY > x.ORIGINAL_COMPLETED_QUANTITY '
1581 ||' OR (x.ORIGINAL_ORDERED_QUANTITY = x.ORIGINAL_COMPLETED_QUANTITY ';
1582 v_temp_sql2 := v_temp_sql2 ||' AND x.requirement_date > sysdate - (' || v_msc_so_offset_days ||' ))) ';
1583 ********************************************************************************************/
1584 -- Hence we directly subsitute v_temp_sql2 in the insert statement
1585
1586 v_sql_stmt:=
1587 'INSERT INTO MSC_ST_SALES_ORDERS'
1588 ||' ( INVENTORY_ITEM_ID,'
1589 ||' ORGANIZATION_ID,'
1590 ||' PRIMARY_UOM_QUANTITY,'
1591 ||' RESERVATION_TYPE,'
1592 ||' RESERVATION_QUANTITY,'
1593 ||' DEMAND_SOURCE_TYPE,'
1594 ||' DEMAND_SOURCE_HEADER_ID,'
1595 ||' COMPLETED_QUANTITY,'
1596 ||' SUBINVENTORY,'
1597 ||' DEMAND_CLASS,'
1598 ||' REQUIREMENT_DATE,'
1599 ||' DEMAND_SOURCE_LINE,'
1600 ||' DEMAND_SOURCE_DELIVERY,'
1601 ||' DEMAND_SOURCE_NAME,'
1602 ||' DEMAND_ID,'
1603 ||' ROW_TYPE,'
1604 ||' PARENT_DEMAND_ID,'
1605 ||' SALES_ORDER_NUMBER,'
1606 ||' FORECAST_VISIBLE,'
1607 ||' DEMAND_VISIBLE,'
1608 ||' SALESREP_CONTACT,'
1609 ||' CUSTOMER_ID,'
1610 ||' SHIP_TO_SITE_USE_ID,'
1611 ||' BILL_TO_SITE_USE_ID,'
1612 ||' REQUEST_DATE,'
1613 ||' PROJECT_ID,'
1614 ||' TASK_ID,'
1615 ||' PLANNING_GROUP,'
1616 ||' SELLING_PRICE,'
1617 ||' END_ITEM_UNIT_NUMBER,'
1618 ||' ORDERED_ITEM_ID,'
1619 ||' ORIGINAL_ITEM_ID,'
1620 ||' LINK_TO_LINE_ID ,'
1621 ||' CUST_PO_NUMBER,'
1622 ||' CUSTOMER_LINE_NUMBER,'
1623 ||' MFG_LEAD_TIME,'
1624 ||' ORG_FIRM_FLAG,'
1625 -- ||' SHIP_SET_ID,'
1626 -- ||' ARRIVAL_SET_ID,'
1627 ||' ATP_REFRESH_NUMBER,'
1628 ||' DELETED_FLAG,'
1629 ||' ORIGINAL_SYSTEM_LINE_REFERENCE,'
1630 ||' ORIGINAL_SYSTEM_REFERENCE,'
1631 ||' CTO_FLAG,'
1632 ||' AVAILABLE_TO_MRP,'
1633 ||' DEMAND_PRIORITY,'
1634 ||' PROMISE_DATE,'
1635 ||' REFRESH_ID,'
1636 ||' SR_INSTANCE_ID) '
1637 -- ||' SCHEDULE_ARRIVAL_DATE,'
1638 -- ||' LATEST_ACCEPTABLE_DATE,'
1639 -- ||' SHIPPING_METHOD_CODE,'
1640 -- ||' ATO_LINE_ID,'
1641 -- ||' ORDER_DATE_TYPE_CODE)'
1642 ||' SELECT '
1643 ||' x.INVENTORY_ITEM_ID,'
1644 ||' x.ORGANIZATION_ID,'
1645 ||' x.PRIMARY_UOM_QUANTITY,'
1646 ||' x.RESERVATION_TYPE,'
1647 ||' x.RESERVATION_QUANTITY,'
1648 ||' x.DEMAND_SOURCE_TYPE,'
1649 ||' x.DEMAND_SOURCE_HEADER_ID,'
1650 ||' x.COMPLETED_QUANTITY,'
1651 ||' x.SUBINVENTORY,'
1652 ||' x.DEMAND_CLASS,'
1653 ||' x.REQUIREMENT_DATE,'
1654 ||' x.DEMAND_SOURCE_LINE,'
1655 ||' x.DEMAND_SOURCE_DELIVERY,'
1656 ||' x.DEMAND_SOURCE_NAME,'
1657 ||' x.DEMAND_ID,'
1658 ||' x.ROW_TYPE,'
1659 ||' x.PARENT_DEMAND_ID,'
1660 ||' x.SALES_ORDER_NUMBER,'
1661 ||' x.FORECAST_VISIBLE,'
1662 ||' x.DEMAND_VISIBLE,'
1663 ||' x.SALESREP_CONTACT,'
1664 ||' x.CUSTOMER_ID,'
1665 ||' x.SHIP_TO_SITE_ID,'
1666 ||' x.BILL_TO_SITE_ID,'
1667 ||' x.REQUEST_DATE,'
1668 ||' x.PROJECT_ID,'
1669 ||' x.TASK_ID,'
1670 ||' x.PLANNING_GROUP,'
1671 ||' x.LIST_PRICE,'
1672 ||' x.END_ITEM_UNIT_NUMBER ,'
1673 ||' x.ordered_item_id,'
1674 ||' x.ORIGINAL_INVENTORY_ITEM_ID , '
1675 ||' x.LINK_TO_LINE_ID,'
1676 ||' x.cust_po_number,'
1680 -- ||' x.SHIP_SET_ID,'
1677 ||' x.customer_line_number,'
1678 ||' x.MFG_LEAD_TIME,'
1679 ||' 1,'
1681 -- ||' x.ARRIVAL_SET_ID,'
1682 ||' x.RN1,'
1683 ||' 2,'
1684 ||' x.ORIGINAL_SYSTEM_LINE_REFERENCE,'
1685 ||' x.ORIGINAL_SYSTEM_REFERENCE,'
1686 ||' x.CTO_FLAG,'
1687 ||' x.AVAILABLE_TO_MRP,'
1688 ||' x.DEMAND_PRIORITY,'
1689 ||' x.PROMISE_DATE,'
1690 ||' :v_refresh_id,'
1691 ||' :v_instance_id '
1692 -- ||' x.SCHEDULE_ARRIVAL_DATE,'
1693 -- ||' x.LATEST_ACCEPTABLE_DATE,'
1694 -- ||' x.SHIPPING_METHOD_CODE,'
1695 -- ||' x.ATO_LINE_ID,'
1696 -- ||' x.ORDER_DATE_TYPE_CODE '
1697 ||' FROM '||MSC_CL_PULL.v_view_name||MSC_CL_PULL.v_dblink||' x'
1698 ||' WHERE x.ORGANIZATION_ID'||MSC_UTIL.v_in_org_str
1699 ||' AND x.ORIGINAL_ORDERED_QUANTITY > x.ORIGINAL_COMPLETED_QUANTITY '
1700 || v_temp_sql3 ;
1701
1702
1703 IF (MSC_CL_PULL.v_lrnn <> -1) THEN -- incremental collections
1704
1705 EXECUTE IMMEDIATE v_sql_stmt
1706 USING MSC_CL_PULL.v_refresh_id,
1707 MSC_CL_PULL.v_instance_id,
1708 MSC_CL_PULL.v_lrn,
1709 MSC_CL_PULL.v_lrn,
1710 MSC_CL_PULL.v_lrn;
1711
1712 ELSE --- complete/targeted collections
1713 EXECUTE IMMEDIATE v_sql_stmt
1714 USING MSC_CL_PULL.v_refresh_id,
1715 MSC_CL_PULL.v_instance_id;
1716 END IF;
1717
1718
1719 COMMIT;
1720 END LOAD_AHL;
1721
1722 PROCEDURE LOAD_OPEN_PAYBACKS IS
1723 BEGIN
1724
1725 IF MSC_CL_PULL.v_lrn = -1 AND MSC_CL_PULL.v_apps_ver >= MSC_UTIL.G_APPS115 THEN
1726
1727 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS,'PROCEDURE LOAD_OPEN_PAYBACKS');
1728 MSC_CL_PULL.v_table_name:= 'MSC_ST_OPEN_PAYBACKS';
1729 MSC_CL_PULL.v_view_name := 'MRP_AP_OPEN_PAYBACK_QTY_V';
1730
1731 v_sql_stmt:=
1732 ' INSERT INTO MSC_ST_OPEN_PAYBACKS(
1733 SR_INSTANCE_ID,
1734 INVENTORY_ITEM_ID,
1735 ORGANIZATION_ID,
1736 SCHEDULED_PAYBACK_DATE,
1737 QUANTITY,
1738 LENDING_PROJECT_ID,
1739 LENDING_TASK_ID,
1740 BORROW_PROJECT_ID,
1741 BORROW_TASK_ID,
1742 PLANNING_GROUP,
1743 LENDING_PROJ_PLANNING_GROUP,
1744 END_ITEM_UNIT_NUMBER)
1745 SELECT
1746 :v_instance_id,
1747 INVENTORY_ITEM_ID,
1748 ORGANIZATION_ID,
1749 SCHEDULED_PAYBACK_DATE,
1750 QUANTITY,
1751 LENDING_PROJECT_ID,
1752 LENDING_TASK_ID,
1753 BORROW_PROJECT_ID,
1754 BORROW_TASK_ID,
1755 PLANNING_GROUP,
1756 LENDING_PROJ_PLANNING_GROUP,
1757 END_ITEM_UNIT_NUMBER
1758 FROM MRP_AP_OPEN_PAYBACK_QTY_V'||MSC_CL_PULL.v_dblink||
1759 ' WHERE ORGANIZATION_ID '|| MSC_UTIL.v_in_org_str;
1760
1761 EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_instance_id;
1762 COMMIT;
1763
1764 END IF;
1765
1766 END LOAD_OPEN_PAYBACKS;
1767
1768 END MSC_CL_DEMAND_PULL;