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