[Home] [Help]
PACKAGE BODY: APPS.MSC_CL_AHL_PULL
Source
1 PACKAGE BODY MSC_CL_AHL_PULL AS
2 /* $Header: MSCPAHLB.pls 120.20.12020000.2 2012/11/16 08:18:36 swundapa ship $*/
3 v_sql_stmt VARCHAR2(32767);
4 v_union_sql VARCHAR2(32767);
5 PROCEDURE LOAD_VISITS IS
6 BEGIN
7
8 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_STATUS, 'In Procedure LOAD_VISITS ');
9
10 IF MSC_CL_PULL.v_apps_ver >= MSC_UTIL.G_EAM_CMRO_SUP_VER THEN
11 MSC_CL_PULL.v_table_name:= 'MSC_ST_VISITS';
12 MSC_CL_PULL.v_view_name := 'MRP_AD_AHL_VISITS_V';
13 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_STATUS, 'In Procedure LOAD_VISITS in IF1');
14
15 -- insert into MSC_ST_VISITS from MRP_AD_AHL_VISITS_V
16 IF MSC_CL_PULL.v_lrnn<> -1 THEN -- incremental refresh
17 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_STATUS, 'In Procedure LOAD_VISITS in v_lrnn'||MSC_CL_PULL.v_lrnn);
18
19 v_sql_stmt:= ' INSERT INTO MSC_ST_VISITS'
20 ||'(VISIT_ID,'
21 ||'ORGANIZATION_ID,'
22 ||'DELETED_FLAG,'
23 ||'REFRESH_ID,'
24 ||'SR_INSTANCE_ID)'
25 ||' SELECT '
26 ||'x.VISIT_ID,'
27 ||'x.ORGANIZATION_ID,'
28 ||'1,'
29 ||':v_refresh_id,'
30 ||':v_instance_id'
31 ||' FROM MRP_AD_AHL_VISITS_V'
32 ||MSC_CL_PULL.V_DBLINK||' x'
33 ||' WHERE x.RN> '||MSC_CL_PULL.v_lrn
34 ||' AND x.ORGANIZATION_ID '||MSC_UTIL.v_in_org_str;
35
36 EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id,
37 MSC_CL_PULL.v_instance_id;
38 COMMIT;
39 END IF;
40
41 MSC_CL_PULL.v_table_name:= 'MSC_ST_VISITS';
42 MSC_CL_PULL.V_VIEW_NAME := 'MRP_AP_VISITS_V';
43
44 v_sql_stmt:= ' INSERT INTO MSC_ST_VISITS'
45 ||'(VISIT_ID,'
46 ||'VISIT_NAME,'
47 ||'VISIT_DESC,'
48 ||'VISIT_START_DATE,'
49 ||'VISIT_END_DATE,'
50 ||'ORGANIZATION_ID,'
51 ||' DELETED_FLAG,'
52 ||'VISIT_TYPE,'
53 ||'VISIT_PRIORITY,'
54 ||'VISIT_STATUS,'
55 ||'VISIT_NUMBER,'
56 ||'REFRESH_ID,'
57 ||'SR_INSTANCE_ID)'
58 ||' SELECT '
59 ||'x.VISIT_ID,'
60 ||'x.VISIT_NAME,'
61 ||'x.DESCRIPTION,'
62 ||'x.START_DATE_TIME,'
63 ||'x.CLOSE_DATE_TIME,'
64 ||'x.ORGANIZATION_ID,'
65 ||'2,'
66 ||'x.VISIT_TYPE_CODE,'
67 ||'x.PRIORITY_CODE,'
68 ||'x.STATUS_CODE,'
69 ||'x.VISIT_NUMBER,'
70 ||' :v_refresh_id,'
71 ||' :v_instance_id'
72 ||' FROM MRP_AP_VISITS_V'||MSC_CL_PULL.v_dblink||' x'
73 ||' WHERE x.RN> '||MSC_CL_PULL.v_lrn
74 ||' AND x.ORGANIZATION_ID '
75 ||MSC_UTIL.v_in_org_str;
76
77 EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id,
78 MSC_CL_PULL.v_instance_id ;
79 END IF; /* MSC_UTIL.G_EAM_CMRO_SUP_VER */
80 END LOAD_VISITS;
81
82 PROCEDURE LOAD_WO_ATTRIBUTES IS
83 BEGIN
84
85 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_STATUS, 'In Procedure LOAD_WO_ATTRIBUTES ');
86
87 IF MSC_CL_PULL.v_apps_ver >= MSC_UTIL.G_EAM_CMRO_SUP_VER THEN
88 MSC_CL_PULL.v_table_name:= 'MSC_ST_WO_ATTRIBUTES';
89 MSC_CL_PULL.v_view_name := 'MRP_AD_WO_ATTRIBUTES_V';
90
91 -- insert into MSC_ST_WO_ATTRIBUTES from MRP_AD_WO_ATTRIBUTES_V
92 IF MSC_CL_PULL.v_lrnn<> -1 THEN -- incremental refresh
93 v_sql_stmt:= ' INSERT INTO MSC_ST_WO_ATTRIBUTES'
94 ||'(WIP_ENTITY_ID,'
95 ||'ORGANIZATION_ID,'
96 ||'DELETED_FLAG,'
97 ||'REFRESH_ID,'
98 ||'SR_INSTANCE_ID)'
99 ||' SELECT '
100 ||'x.WIP_ENTITY_ID,'
101 ||'x.ORGANIZATION_ID,'
102 ||'1,'
103 ||':v_refresh_id,'
104 ||':v_instance_id'
105 ||' FROM MRP_AD_WO_ATTRIBUTES_V'
106 ||MSC_CL_PULL.V_DBLINK||' x'
107 ||' WHERE x.RN> '||MSC_CL_PULL.v_lrn
108 ||' AND x.ORGANIZATION_ID '||MSC_UTIL.v_in_org_str;
109
110 EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id,
111 MSC_CL_PULL.v_instance_id;
112 COMMIT;
113 END IF;
114
115 MSC_CL_PULL.v_table_name:= 'MSC_ST_WO_ATTRIBUTES';
116 MSC_CL_PULL.v_view_name := 'MRP_AP_WO_ATTRIBUTES_V';
117
118 IF MSC_CL_PULL.v_lrnn<> -1 THEN -- incremental refresh
119 v_union_sql := ' AND x.RN1> '||MSC_CL_PULL.v_lrn||' UNION '
120 ||' SELECT '
121 ||'x.SERIAL_NUMBER,'
122 ||'x.WIP_ENTITY_ID,'
123 ||'x.FLEET_HEADER_ID,'
124 ||'x.PRODUCES_TO_STOCK,'
125 ||'x.ORGANIZATION_ID,'
126 ||'2,'
127 ||'x.VISIT_ID,'
128 ||'x.VISIT_NAME,'
129 ||'x.PARAMETER1,'
130 ||'x.PARAMETER2,'
131 ||'x.PARAMETER3,'
132 ||'x.PARAMETER4,'
133 ||'x.PARAMETER5,'
134 ||'x.PARAMETER6,'
135 ||'x.PARAMETER7,'
136 ||'x.PARAMETER8,'
137 ||'x.PARAMETER9,'
138 ||'x.MASTER_WORKORDER_FLAG,'
139 ||'x.PREV_MILESTONE,'
140 ||'x.NEXT_MILESTONE,'
141 ||'x.PRODUCT_CLASSIFICATION,'
142 -- ||'x.Operating_Fleet,'/* remove the above line and uncomment this line for bug fix# 12428753 */
143 ||'x.MAINTENANCE_REQT,'
144 ||'x.ITEM_ALTERNATES_EXIST,'
145 ||':v_refresh_id,'
146 ||':v_instance_id'
147 ||' FROM MRP_AP_WO_ATTRIBUTES_V'
148 ||MSC_CL_PULL.V_DBLINK||' x'
149 ||' WHERE x.RN2> '||MSC_CL_PULL.v_lrn
150 ||' AND x.ORGANIZATION_ID '||MSC_UTIL.v_in_org_str;
151 ELSE
152 v_union_sql :=' AND (x.RN1>'||MSC_CL_PULL.v_lrn
153 ||' OR x.RN2>'||MSC_CL_PULL.v_lrn||')';
154 END IF;
155
156 v_sql_stmt:= ' INSERT INTO MSC_ST_WO_ATTRIBUTES'
157 ||'(SERIAL_NUM,'
158 ||'WIP_ENTITY_ID,'
159 ||'FLEET_HEADER_ID,'
160 ||'PRODUCES_TO_STOCK,'
161 ||'ORGANIZATION_ID,'
162 ||'DELETED_FLAG,'
163 ||'VISIT_ID,'
164 ||'VISIT_NAME,'
165 ||'PARAMETER1,'
166 ||'PARAMETER2,'
167 ||'PARAMETER3,'
168 ||'PARAMETER4,'
169 ||'PARAMETER5,'
170 ||'PARAMETER6,'
171 ||'PARAMETER7,'
172 ||'PARAMETER8,'
173 ||'PARAMETER9,'
174 ||'MASTER_WO,'
175 ||'PREV_MILESTONE,'
176 ||'NEXT_MILESTONE,'
177 ||'PRODUCT_CLASSIFICATION,'
178 -- ||'Operating_Fleet,'/* remove the above line and uncomment this line for bug fix# 12428753 */
179 ||'MAINTENANCE_REQT,'
180 ||'ITEM_ALTERNATES_EXIST,'
181 ||'REFRESH_ID,'
182 ||'SR_INSTANCE_ID)'
183 ||' SELECT '
184 ||'x.SERIAL_NUMBER,'
185 ||'x.WIP_ENTITY_ID,'
186 ||'x.FLEET_HEADER_ID,'
187 ||'x.PRODUCES_TO_STOCK,'
188 ||'x.ORGANIZATION_ID,'
189 ||'2,'
190 ||'x.VISIT_ID,'
191 ||'x.VISIT_NAME,'
192 ||'x.PARAMETER1,'
193 ||'x.PARAMETER2,'
194 ||'x.PARAMETER3,'
195 ||'x.PARAMETER4,'
196 ||'x.PARAMETER5,'
197 ||'x.PARAMETER6,'
198 ||'x.PARAMETER7,'
199 ||'x.PARAMETER8,'
200 ||'x.PARAMETER9,'
201 ||'x.MASTER_WORKORDER_FLAG,'
202 ||'x.PREV_MILESTONE,'
203 ||'x.NEXT_MILESTONE,'
204 ||'x.PRODUCT_CLASSIFICATION,'
205 -- ||'x.Operating_Fleet,'/* remove the above line and uncomment this line for bug fix# 12428753 */
206 ||'x.MAINTENANCE_REQT,'
207 ||'x.ITEM_ALTERNATES_EXIST,'
208 ||':v_refresh_id,'
209 ||':v_instance_id'
210 ||' FROM MRP_AP_WO_ATTRIBUTES_V'
211 ||MSC_CL_PULL.V_DBLINK||' x'
212 ||' WHERE x.ORGANIZATION_ID '||MSC_UTIL.v_in_org_str
213 || v_union_sql ;
214
215 IF MSC_CL_PULL.v_lrnn<> -1 THEN -- incremental refresh
216 EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id,
217 MSC_CL_PULL.v_instance_id,
218 MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
219 ELSE
220 EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id,
221 MSC_CL_PULL.v_instance_id;
222 END IF;
223 END IF; /* MSC_UTIL.G_EAM_CMRO_SUP_VER */
224
225 END LOAD_WO_ATTRIBUTES;
226
227 PROCEDURE LOAD_WO_TASK_HRY IS
228 BEGIN
229
230 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_STATUS, 'In Procedure LOAD_WO_TASK_HRY ');
231
232 IF MSC_CL_PULL.v_apps_ver >= MSC_UTIL.G_EAM_CMRO_SUP_VER THEN
233 MSC_CL_PULL.v_table_name:= 'MSC_ST_WO_TASK_HIERARCHY';
234 MSC_CL_PULL.v_view_name := 'MRP_AD_WO_TASK_HIERARCHY_V';
235
236 -- insert into MSC_ST_WO_TASK_HIERARCHY from MRP_AD_WO_TASK_HRY_V
237 IF MSC_CL_PULL.v_lrnn<> -1 THEN -- incremental refresh
238 v_sql_stmt:= ' INSERT INTO MSC_ST_WO_TASK_HIERARCHY'
239 ||'(TASK_LINK_ID,'
240 ||'ORGANIZATION_ID,'
241 ||'DELETED_FLAG,'
242 ||'REFRESH_ID,'
243 ||'SR_INSTANCE_ID)'
244 ||' SELECT '
245 ||'x.TASK_LINK_ID,'
246 ||'x.ORGANIZATION_ID,'
247 ||'1,'
248 ||':v_refresh_id,'
249 ||':v_instance_id'
250 ||' FROM MRP_AD_WO_TASK_HIERARCHY_V'
251 ||MSC_CL_PULL.v_dblink||' x'
252 ||' WHERE x.RN>'||MSC_CL_PULL.v_lrn
253 ||' AND x.ORGANIZATION_ID '||MSC_UTIL.v_in_org_str;
254
255 EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id,
256 MSC_CL_PULL.v_instance_id;
257 COMMIT;
258 END IF;
259
260 MSC_CL_PULL.v_table_name:= 'MSC_ST_WO_TASK_HIERARCHY';
261 MSC_CL_PULL.v_view_name := 'MRP_AP_WO_TASK_HRY_V';
262
263 IF MSC_CL_PULL.v_lrnn<> -1 THEN -- incremental refresh
264 v_union_sql := ' AND x.RN1> '||MSC_CL_PULL.v_lrn||' UNION '
265 ||' SELECT '
266 ||'x.TASK_LINK_ID,'
267 ||'x.CURRENT_WIP_ENTITY_ID,'
268 ||'x.NEXT_WIP_ENTITY_ID,'
269 ||'x.PRECEDENCE_CONSTRAINT,'
270 ||'x.MIN_SEPARATION,'
271 ||'x.MIN_SEP_TIME_UNIT,'
272 ||'x.MAX_SEPARATION,'
273 ||'x.MAX_SEP_TIME_UNIT,'
274 ||'x.ORGANIZATION_ID, '
275 ||'2,'
276 ||':v_refresh_id,'
277 ||':v_instance_id'
278 ||' from MRP_AP_WO_TASK_HRY_V'||MSC_CL_PULL.V_DBLINK||' x'
279 ||' WHERE x.RN2> '||MSC_CL_PULL.v_lrn
280 ||' AND x.ORGANIZATION_ID '||MSC_UTIL.v_in_org_str
281 ||' UNION '
282 ||' SELECT '
283 ||'x.TASK_LINK_ID,'
284 ||'x.CURRENT_WIP_ENTITY_ID,'
285 ||'x.NEXT_WIP_ENTITY_ID,'
286 ||'x.PRECEDENCE_CONSTRAINT,'
287 ||'x.MIN_SEPARATION,'
288 ||'x.MIN_SEP_TIME_UNIT,'
289 ||'x.MAX_SEPARATION,'
290 ||'x.MAX_SEP_TIME_UNIT,'
291 ||'x.ORGANIZATION_ID, '
292 ||'2,'
293 ||':v_refresh_id,'
294 ||':v_instance_id'
295 ||' from MRP_AP_WO_TASK_HRY_V'||MSC_CL_PULL.V_DBLINK||' x'
296 ||' WHERE x.RN3> '||MSC_CL_PULL.v_lrn
297 ||' AND x.ORGANIZATION_ID '||MSC_UTIL.v_in_org_str;
298 ELSE
299 v_union_sql :=' AND (x.RN1>'||MSC_CL_PULL.v_lrn
300 ||' OR x.RN2>'||MSC_CL_PULL.v_lrn
301 ||' OR x.RN3>'||MSC_CL_PULL.v_lrn||')';
302 END IF;
303
304 v_sql_stmt:= ' INSERT INTO MSC_ST_WO_TASK_HIERARCHY'
305 ||'(TASK_LINK_ID,'
306 ||'CURRENT_WIP_ENTITY_ID,'
307 ||'NEXT_WIP_ENTITY_ID,'
308 ||'PRECEDENCE_CONSTRAINT,'
309 ||'MIN_SEPARATION,'
310 ||'MIN_SEP_TIME_UNIT,'
311 ||'MAX_SEPARATION,'
312 ||'MAX_SEP_TIME_UNIT,'
313 ||'ORGANIZATION_ID,'
314 ||'DELETED_FLAG,'
315 ||'REFRESH_ID,'
316 ||'SR_INSTANCE_ID)'
317 ||' select '
318 ||'x.TASK_LINK_ID,'
319 ||'x.CURRENT_WIP_ENTITY_ID,'
320 ||'x.NEXT_WIP_ENTITY_ID,'
321 ||'x.PRECEDENCE_CONSTRAINT,'
322 ||'x.MIN_SEPARATION,'
323 ||'x.MIN_SEP_TIME_UNIT,'
324 ||'x.MAX_SEPARATION,'
325 ||'x.MAX_SEP_TIME_UNIT,'
326 ||'x.ORGANIZATION_ID, '
327 ||'2,'
328 ||':v_refresh_id,'
329 ||':v_instance_id'
330 ||' from MRP_AP_WO_TASK_HRY_V'||MSC_CL_PULL.V_DBLINK||' x'
331 ||' WHERE x.ORGANIZATION_ID '||MSC_UTIL.v_in_org_str
332 || v_union_sql ;
333
334 IF MSC_CL_PULL.v_lrnn<> -1 THEN -- incremental refresh
335 EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id,
336 MSC_CL_PULL.v_instance_id,
337 MSC_CL_PULL.v_refresh_id,
338 MSC_CL_PULL.v_instance_id,
339 MSC_CL_PULL.v_refresh_id,
340 MSC_CL_PULL.v_instance_id;
341 ELSE
342 EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id,
343 MSC_CL_PULL.v_instance_id;
344 END IF;
345 END IF; /* MSC_UTIL.G_EAM_CMRO_SUP_VER */
346
347 END LOAD_WO_TASK_HRY;
348
349 PROCEDURE LOAD_MILESTONES IS
350 BEGIN
351
352 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_STATUS, 'In Procedure LOAD_MILESTONES ');
353
354 IF MSC_CL_PULL.v_apps_ver >= MSC_UTIL.G_EAM_CMRO_SUP_VER THEN
355 MSC_CL_PULL.v_table_name:= 'MSC_ST_WO_MILESTONES';
356 MSC_CL_PULL.v_view_name := 'MRP_AD_MILESTONES_V';
357
358 -- insert into MSC_ST_WO_MILESTONES from MRP_AD_MILESTONES_V
359 IF MSC_CL_PULL.v_lrnn<> -1 THEN -- incremental refresh
360 v_sql_stmt:= ' INSERT INTO MSC_ST_WO_MILESTONES'
361 ||'(VISIT_ID,'
362 ||'MILESTONE,'
363 ||'ORGANIZATION_ID,'
364 ||'DELETED_FLAG,'
365 ||'REFRESH_ID,'
366 ||'SR_INSTANCE_ID)'
367 ||' SELECT '
368 ||'x.VISIT_ID,'
369 ||'x.MILESTONE,'
370 ||'x.ORGANIZATION_ID,'
371 ||'1,'
372 ||':v_refresh_id,'
373 ||':v_instance_id'
374 ||' FROM MRP_AD_MILESTONES_V'
375 ||MSC_CL_PULL.v_dblink||' x'
376 ||' WHERE x.RN>'||MSC_CL_PULL.v_lrn
377 ||' AND x.ORGANIZATION_ID '||MSC_UTIL.v_in_org_str;
378
379 EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id,
380 MSC_CL_PULL.v_instance_id;
381 COMMIT;
382 END IF;
383
384 MSC_CL_PULL.v_table_name:= 'MSC_ST_WO_MILESTONES';
385 MSC_CL_PULL.v_view_name := 'MRP_AP_MILESTONES_V';
386
387 v_sql_stmt:= ' INSERT INTO MSC_ST_WO_MILESTONES'
388 ||'(MILESTONE,'
389 ||'MILESTONE_DESC,'
390 ||'VISIT_ID,'
391 ||'ORGANIZATION_ID,'
392 ||'DELETED_FLAG,'
393 ||'EARLIEST_START_DATE,'
394 ||'TARGET_COMPLETION_DATE,'
395 ||'REFRESH_ID,'
396 ||'SR_INSTANCE_ID)'
397 ||' SELECT '
398 ||'x.MILESTONE,'
399 ||'x.MILESTONE_DESCRIPTION,'
400 ||'x.VISIT_ID,'
401 ||'x.ORGANIZATION_ID,'
402 ||'2,'
403 ||'x.EARLIEST_START_DATE,'
404 ||'x.PLANNED_END_DATE,'
405 ||':v_refresh_id,'
406 ||':v_instance_id'
407 ||' from MRP_AP_MILESTONES_V'||MSC_CL_PULL.V_DBLINK||' x'
408 ||' WHERE x.RN> '||MSC_CL_PULL.v_lrn
409 ||' AND x.ORGANIZATION_ID '||MSC_UTIL.v_in_org_str;
410
411 EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id,
412 MSC_CL_PULL.v_instance_id;
413 END IF; /* MSC_UTIL.G_EAM_CMRO_SUP_VER */
414
415 END LOAD_MILESTONES;
416
417 PROCEDURE LOAD_WO_OPER_RELN IS
418 BEGIN
419
420 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_STATUS, 'In Procedure LOAD_WO_OPER_RELN');
421
422 IF MSC_CL_PULL.v_apps_ver >= MSC_UTIL.G_EAM_CMRO_SUP_VER THEN
423 MSC_CL_PULL.v_table_name:= 'MSC_ST_WO_OPERATION_REL';
424 MSC_CL_PULL.v_view_name := 'MRP_AD_WO_OPERATIONS_REL_V';
425
426 -- insert into MSC_ST_WO_OPERATION_REL from MRP_AD_WO_OPERATIONS_REL_V
427 IF MSC_CL_PULL.V_LRNN<> -1 THEN -- incremental refresh
428 v_sql_stmt:= ' INSERT INTO MSC_ST_WO_OPERATION_REL'
429 ||'(WORKORDER_ID,'
430 ||'FROM_OP_SEQ_NUM,'
431 ||'TO_OP_SEQ_NUM,'
432 ||'ORGANIZATION_ID,'
433 ||'DELETED_FLAG,'
434 ||'REFRESH_ID,'
435 ||'SR_INSTANCE_ID)'
436 ||' SELECT '
437 ||'x.WORKORDER_ID,'
438 ||'x.FROM_OP_SEQ_NUM,'
439 ||'x.TO_OP_SEQ_NUM,'
440 ||'x.ORGANIZATION_ID,'
441 ||'1,'
442 ||':v_refresh_id,'
443 ||':v_instance_id'
444 ||' FROM MRP_AD_WO_OPERATIONS_REL_V'
445 ||MSC_CL_PULL.v_dblink||' x'
446 ||' WHERE x.RN>'||MSC_CL_PULL.v_lrn
447 ||' AND x.ORGANIZATION_ID '||MSC_UTIL.v_in_org_str;
448
449 EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id,
450 MSC_CL_PULL.v_instance_id;
451 COMMIT;
452 END IF;
453
454 MSC_CL_PULL.v_table_name:= 'MSC_ST_WO_OPERATION_REL';
455 MSC_CL_PULL.V_VIEW_NAME := 'MRP_AP_WO_OPER_RELN_V';
456
457 IF MSC_CL_PULL.v_lrnn<> -1 THEN -- incremental refresh
458 v_union_sql := ' AND x.RN1> '||MSC_CL_PULL.v_lrn||' UNION '
459 ||' SELECT '
460 ||'x.WORKORDER_ID,'
461 ||'x.WIP_ENTITY_ID,'
462 ||'x.FROM_OPERATION_SEQ,'
463 ||'x.TO_OPERATION_SEQ,'
464 ||'x.PRECEDENCE_CONSTRAINT,'
465 ||'x.MIN_SEPARATION,'
466 ||'x.MIN_SEPARATION_TIME_UNIT,'
467 ||'x.MAX_SEPARATION,'
468 ||'x.MAX_SEPARATION_TIME_UNIT,'
469 ||'x.ORGANIZATION_ID,'
470 ||'2,'
471 ||':v_refresh_id,'
472 ||':v_instance_id'
473 ||' from MRP_AP_WO_OPER_RELN_V'
474 ||MSC_CL_PULL.V_DBLINK||' x'
475 ||' WHERE x.RN2> '||MSC_CL_PULL.v_lrn
476 ||' AND x.ORGANIZATION_ID '||MSC_UTIL.v_in_org_str;
477 ELSE
478 v_union_sql :=' AND (x.RN1>'||MSC_CL_PULL.v_lrn
479 ||' OR x.RN2>'||MSC_CL_PULL.v_lrn||')';
480 END IF;
481
482 v_sql_stmt:= ' INSERT INTO MSC_ST_WO_OPERATION_REL'
483 ||'(WORKORDER_ID,'
484 ||'WIP_ENTITY_ID,'
485 ||'FROM_OP_SEQ_NUM,'
486 ||'TO_OP_SEQ_NUM,'
487 ||'PRECEDENCE_CONSTRAINT,'
488 ||'MIN_SEPARATION,'
489 ||'MIN_SEP_TIME_UNIT,'
490 ||'MAX_SEPARATION,'
491 ||'MAX_SEP_TIME_UNIT,'
492 ||'ORGANIZATION_ID,'
493 ||'DELETED_FLAG,'
494 ||'REFRESH_ID,'
495 ||'SR_INSTANCE_ID)'
496 ||' SELECT '
497 ||'x.WORKORDER_ID,'
498 ||'x.WIP_ENTITY_ID,'
499 ||'x.FROM_OPERATION_SEQ,'
500 ||'x.TO_OPERATION_SEQ,'
501 ||'x.PRECEDENCE_CONSTRAINT,'
502 ||'x.MIN_SEPARATION,'
503 ||'x.MIN_SEPARATION_TIME_UNIT,'
504 ||'x.MAX_SEPARATION,'
505 ||'x.MAX_SEPARATION_TIME_UNIT,'
506 ||'x.ORGANIZATION_ID,'
507 ||'2,'
508 ||':v_refresh_id,'
509 ||':v_instance_id'
510 ||' from MRP_AP_WO_OPER_RELN_V'
511 ||MSC_CL_PULL.V_DBLINK||' x'
515 IF MSC_CL_PULL.v_lrnn<> -1 THEN -- incremental refresh
512 ||' WHERE x.ORGANIZATION_ID '||MSC_UTIL.v_in_org_str
513 || v_union_sql ;
514
516 EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id,
517 MSC_CL_PULL.v_instance_id,
518 MSC_CL_PULL.v_refresh_id,
519 MSC_CL_PULL.v_instance_id;
520 ELSE
521 EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id,
522 MSC_CL_PULL.v_instance_id;
523 END IF;
524
525 END IF; /* MSC_UTIL.G_EAM_CMRO_SUP_VER */
526
527 END LOAD_WO_OPER_RELN;
528
529 PROCEDURE LOAD_WORK_BREAKDOWN IS
530 BEGIN
531
532 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_STATUS,
533 'In Procedure LOAD_WORK_BREAKDOWN ');
534
535 IF MSC_CL_PULL.V_APPS_VER >= MSC_UTIL.G_EAM_CMRO_SUP_VER THEN
536
537 MSC_CL_PULL.v_table_name:= 'MSC_ST_WORK_BREAKDOWN_STRUCT';
538 MSC_CL_PULL.v_view_name := 'MRP_AP_WORK_BREAKDOWN_V';
539
540 v_sql_stmt:= ' INSERT INTO MSC_ST_WORK_BREAKDOWN_STRUCT'
541 ||'(ORGANIZATION_ID,'
542 ||'PARAMETER_NAME,'
543 ||'DISPLAY_NAME,'
544 ||'DELETED_FLAG,'
545 ||'REFRESH_ID,'
546 ||'SR_INSTANCE_ID)'
547 ||' select '
548 ||'x.ORGANIZATION_ID,'
549 ||'x.LOOKUP_CODE,'
550 ||'x.MEANING,'
551 ||'2,'
552 ||':v_refresh_id,'
553 ||':v_instance_id'
554 ||' from MRP_AP_WORK_BREAKDOWN_V'
555 ||MSC_CL_PULL.v_dblink||' x'
556 ||' WHERE x.ORGANIZATION_ID '||MSC_UTIL.v_in_org_str;
557
558 EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id,
559 MSC_CL_PULL.v_instance_id;
560
561 END IF; /* MSC_UTIL.G_EAM_CMRO_SUP_VER */
562
563 END LOAD_WORK_BREAKDOWN;
564
565 PROCEDURE LOAD_WO_SUB_COMP IS
566 BEGIN
567 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_STATUS, 'In Procedure LOAD_WO_SUB_COMP ');
568
569 IF MSC_CL_PULL.v_apps_ver >= MSC_UTIL.G_EAM_CMRO_SUP_VER THEN
570 MSC_CL_PULL.v_table_name:= 'MSC_ST_WO_SUB_COMP';
571 MSC_CL_PULL.V_VIEW_NAME := 'MRP_AP_WO_SUB_COMP_V';
572
573 v_sql_stmt:= ' INSERT INTO MSC_ST_WO_SUB_COMP'
574 ||'(ALTERNATE_COMPONENT_ID,'
575 ||'WIP_ENTITY_ID,'
576 ||'OP_SEQ_NUM,'
577 ||'ORGANIZATION_ID,'
578 ||'PLAN_ID,'
579 ||'PRIMARY_COMPONENT_ID,'
580 ||'RANK,'
581 ||'RATIO,'
582 ||'REQUEST_ID,'
583 ||'REFRESH_ID,'
584 ||'SR_INSTANCE_ID)'
585 ||'SELECT '
586 ||' x.ALTERNATE_COMPONENT_ID,'
587 ||'x.WIP_ENTITY_ID,'
588 ||'x.OP_SEQ_NUM,'
589 ||'x.ORGANIZATION_ID,'
590 ||'x.PLAN_ID,'
591 ||'x.PRIMARY_COMPONENT_ID,'
592 ||'x.RANK,'
593 ||'x.RATIO,'
594 ||'x.REQUEST_ID,'
595 ||':v_refresh_id,'
596 ||':v_instance_id'
597 ||' FROM MRP_AP_WO_SUB_COMP_V'||MSC_CL_PULL.v_dblink||' x'
598 ||' WHERE x.ORGANIZATION_ID '||MSC_UTIL.v_in_org_str;
599
600 EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id,
601 MSC_CL_PULL.v_instance_id ;
602 END IF; /* MSC_UTIL.G_EAM_CMRO_SUP_VER */
603
604 END LOAD_WO_SUB_COMP;
605
606 PROCEDURE LOAD_CMRO_FORECASTS (p_eam_cmro_st_date IN DATE,
607 p_eam_cmro_end_date IN DATE ) IS
608 lv_inflate_wip NUMBER;
609 v_temp_sql VARCHAR2(400);
610 v_temp_whr VARCHAR2(300);
611
612 BEGIN
613
614 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_STATUS,
615 'In Procedure LOAD_CMRO_FORECASTS ');
616 IF MSC_CL_PULL.v_apps_ver >= MSC_UTIL.G_EAM_CMRO_SUP_VER THEN
617
618 /*--incremental refresh is not to be supported for CMRO forecasts
619
620 IF MSC_CL_PULL.v_lrnn<> -1 THEN -- incremental refresh
621 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_STATUS, 'In Procedure LOAD_CMRO_FORECASTS and v_lrnn= '||MSC_CL_PULL.v_lrnn);
622
623 v_sql_stmt:= ' INSERT INTO MSC_ST_SUPPLIES'
624 ||'(DISPOSITION_ID,'
625 ||'OBJECT_TYPE,'
626 ||'SIMULATION_PLAN_ID,'
627 ||'ORDER_TYPE,'
628 ||'MAINTENANCE_OBJECT_SOURCE,'
629 ||'DELETED_FLAG,'
630 ||'REFRESH_ID,'
631 ||'SR_INSTANCE_ID)'
632 ||' SELECT '
633 ||'x.MAINTENANCE_REQMNT_ID,'
634 ||'x.OBJECT_TYPE,'
635 ||'x.SIMULATION_PLAN_ID,'
636 ||' x.ORDER_TYPE,'
637 ||' x.MAINTENANCE_OBJECT_SOURCE,'
638 ||' 1,'
639 ||':v_refresh_id,'
640 ||':v_instance_id'
641 ||' FROM MRP_AD_UMP_MAINT_REQMNTS_V'
642 || MSC_CL_PULL.V_DBLINK||' x'
643 ||' WHERE x.RN> '||MSC_CL_PULL.v_lrn;
644
645 EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id,
646 MSC_CL_PULL.v_instance_id;
647
648
649 --incremental for demands
650
651 v_sql_stmt:= ' INSERT INTO MSC_ST_DEMANDS'
652 ||'(DISPOSITION_ID,'
653 ||'OBJECT_TYPE,'
654 ||'SCHEDULE_DESIGNATOR_ID,'
655 ||'ORGANIZATION_ID,'
656 ||'INVENTORY_ITEM_ID,'
657 ||'DELETED_FLAG,'
658 ||'REFRESH_ID,'
659 ||'SR_INSTANCE_ID)'
660 ||' SELECT '
661 ||'x.MAINTENANCE_REQMNT_ID,'
662 ||'x.OBJECT_TYPE,'
663 ||'x.SIMULATION_PLAN_ID,'
664 ||'x.OPERATING_ORG_ID,'
665 ||'x.INVENTORY_ITEM_ID,'
666 ||' 1,'
667 ||':v_refresh_id,'
668 ||':v_instance_id'
669 ||' FROM MRP_AD_UMP_MATERIAL_REQMNTS_V'||MSC_CL_PULL.v_dblink||' x'
670 ||' WHERE x.RN> '||MSC_CL_PULL.v_lrn;
671
672 EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id,
673 MSC_CL_PULL.v_instance_id ;
674
675
676 --inc for resource requirements
677
678 v_sql_stmt:= ' INSERT INTO MSC_ST_RESOURCE_REQUIREMENTS'
679 ||'(DISPOSITION_ID,'
680 ||'OBJECT_TYPE,'
681 ||'SCHEDULE_DESIGNATOR_ID,'
682 ||'DEPARTMENT_ID,'
683 ||'ORGANIZATION_ID,'
684 ||'DELETED_FLAG,'
685 ||'REFRESH_ID,'
686 ||'SR_INSTANCE_ID)'
687 ||' SELECT '
688 ||'x.MAINTENANCE_REQMNT_ID,'
689 ||'x.OBJECT_TYPE,'
690 ||'x.SIMULATION_PLAN_ID,'
691 ||'x.OPERATING_ORG_ID,'
692 ||'x.INVENTORY_ITEM_ID,'
693 ||' 1,'
694 ||':v_refresh_id,'
695 ||':v_instance_id'
696 ||' MRP_AD_UMP_RESOURCE_REQMNTS_V'||MSC_CL_PULL.v_dblink||' x'
697 ||' WHERE x.RN> '||MSC_CL_PULL.v_lrn;
698
699 EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id,
700 MSC_CL_PULL.v_instance_id ;
701 --inc for designators
702
703
704 v_sql_stmt:= ' INSERT INTO MSC_ST_DESIGNATORS'
705 ||'(DESIGNATOR,'
706 ||'ORGANIZATION_ID,'
707 ||'SRC_DESIGNATOR,'
708 ||'DELETED_FLAG,'
709 ||'REFRESH_ID,'
710 ||'SR_INSTANCE_ID)'
711 ||' SELECT '
712 ||'-23453,'
713 ||'-23453,'
714 ||'x.SIMULATION_PLAN_NAME,'
715 ||' 1,'
716 ||':v_refresh_id,'
717 ||':v_instance_id'
718 ||' MRP_AD_AHL_DESIGNATORS_V'||MSC_CL_PULL.v_dblink||' x'
719 ||' WHERE x.RN> '||MSC_CL_PULL.v_lrn;
720
721 EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id,
722 MSC_CL_PULL.v_instance_id ;
723
724 COMMIT;
725 END IF;
726 --end of incremental
727 */
728
729 MSC_CL_PULL.v_table_name:= 'MSC_ST_SUPPLIES';
730 MSC_CL_PULL.v_view_name := 'MRP_AP_UMP_MAINT_REQMNTS_V';
731
735 ||'NEW_WIP_START_DATE,'
732 v_sql_stmt:= ' INSERT INTO MSC_ST_SUPPLIES '
733 ||'(SCHEDULE_DESIGNATOR_ID,'
734 ||'ORGANIZATION_ID,'
736 ||'NEW_SCHEDULE_DATE,'
737 -- ||'MAINTENANCE_TYPE_CODE,'
738 ||'to_be_exploded,'
739 ||'ORDER_NUMBER,'
740 ||'INVENTORY_ITEM_ID,'
741 ||'ORDER_TYPE,'
742 ||'COLL_ORDER_TYPE,'
743 ||'MAINTENANCE_OBJECT_SOURCE,'
744 ||'NEW_ORDER_QUANTITY,'
745 ||'FIRM_PLANNED_TYPE,'
746 ||'DELETED_FLAG,'
747 ||'PRODUCT_CLASSIFICATION,'
748 ||'OPERATING_FLEET ,'
749 ||'MAINTENANCE_REQUIREMENT,'
750 ||'SOURCE_ITEM_ID,'
751 ||'REFRESH_ID,'
752 ||'SR_INSTANCE_ID)'
753 ||' SELECT '
754 ||' x.SIMULATION_PLAN_ID,'
755 ||' x.OPERATING_ORG_ID,'
756 ||' x.DUE_DATE,'
757 ||' LAST_DAY(x.DUE_DATE),'
758 -- ||' x.MAINTENANCE_TYPE_CODE,'
759 ||' 2,'
760 ||' x.TITLE,'
761 ||' x.INVENTORY_ITEM_ID,'
762 ||' x.ORDER_TYPE,'
763 ||' 92,'
764 ||' x.MAINTENANCE_OBJECT_SOURCE,'
765 ||' x.QUANTITY,'
766 ||' 1,'
767 ||' 2,'
768 ||'x.PRODUCT_CLASSIFICATION,'
769 ||'x.FLEET_HEADER_ID ,'
770 ||'x.MAINTENANCE_REQUIREMENT,'
771 ||' x.INVENTORY_ITEM_ID,'
772 ||':v_refresh_id,'
773 ||':v_instance_id'
774 ||' FROM MRP_AP_UMP_MAINT_REQMNTS_V'||MSC_CL_PULL.v_dblink||' x'
775 ||' WHERE x.OPERATING_ORG_ID '|| MSC_UTIL.v_in_org_str
776 ||' AND x.DUE_DATE >= :p_eam_cmro_st_date '
777 ||' AND x.DUE_DATE <= :p_eam_cmro_end_date '
778 -- ||' AND x.RN > '||MSC_CL_PULL.v_lrn
779 ;
780
781
782 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS,'lv_sql_stmt for cmro forecast :' ||v_sql_stmt);
783 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_STATUS, 'SQL statement is '||v_sql_stmt||'-'||p_eam_cmro_st_date||'-'||p_eam_cmro_end_date);
784
785 EXECUTE IMMEDIATE v_sql_stmt USING
786 MSC_CL_PULL.v_refresh_id,
787 MSC_CL_PULL.v_instance_id
788 ,p_eam_cmro_st_date
789 ,p_eam_cmro_end_date
790 ;
791
792 COMMIT;
793 MSC_CL_PULL.v_table_name:= 'MSC_ST_DEMANDS';
794 MSC_CL_PULL.v_view_name := 'MRP_AP_UMP_MATERIAL_REQMNTS_V';
795
796 v_sql_stmt:= ' INSERT INTO MSC_ST_DEMANDS '
797 ||'(SCHEDULE_DESIGNATOR_ID,'
798 ||'ORGANIZATION_ID,'
799 ||'INVENTORY_ITEM_ID,'
800 ||'USING_REQUIREMENT_QUANTITY,'
801 ||'USING_ASSEMBLY_DEMAND_DATE, '
802 ||'DEMAND_TYPE,'
803 ||'ORIGINATION_TYPE,'
804 ||'DELETED_FLAG,'
805 ||'OPERATING_FLEET,'
806 ||'MAINTENANCE_REQUIREMENT,'
807 ||'MAINTENANCE_OBJECT_SOURCE,'
808 ||'USING_ASSEMBLY_ITEM_ID,'
809 ||'REFRESH_ID,'
810 ||'SR_INSTANCE_ID)'
811 ||' SELECT '
812 ||'x.SIMULATION_PLAN_ID,'
813 ||'x.OPERATING_ORG_ID,'
814 ||'x.COMPONENT,'
815 ||'x.QUANTITY,'
816 ||'x.USING_ASSEMBLY_DEMAND_DATE,'
817 ||'x.DEMAND_TYPE,'
818 ||'x.ORIGINATION_TYPE,'
819 ||' 2,'
820 ||'x.FLEET_HEADER_ID,'
821 ||'x.TITLE,'--mnagilla
822 ||'x.MAINTENANCE_OBJECT_SOURCE,'
823 ||'x.USING_ASSEMBLY_ITEM_ID,'
824 ||':v_refresh_id,'
825 ||':v_instance_id'
826 ||' FROM MRP_AP_UMP_MATERIAL_REQMNTS_V'||MSC_CL_PULL.v_dblink||' x'
827 ||' WHERE x.OPERATING_ORG_ID '||MSC_UTIL.v_in_org_str
828
829 ||' AND x.USING_ASSEMBLY_DEMAND_DATE >= :p_eam_cmro_st_date '
830 ||' AND x.USING_ASSEMBLY_DEMAND_DATE <= :p_eam_cmro_end_date '
831 -- ||' AND (x.RN1 >'||MSC_CL_PULL.v_lrn
832 -- ||' AND x.RN2 >'||MSC_CL_PULL.v_lrn||')'
833 ;
834 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS,'lv_sql_stmt for cmro forecast demands :' ||v_sql_stmt);
835
836 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_STATUS, 'SQL statement is '||v_sql_stmt||'-'||p_eam_cmro_st_date||'-'||p_eam_cmro_end_date);
837 EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id,
838 MSC_CL_PULL.v_instance_id
839 ,p_eam_cmro_st_date
840 ,p_eam_cmro_end_date;
841 COMMIT;
842
843 MSC_CL_PULL.v_table_name:= 'MSC_ST_RESOURCE_REQUIREMENTS';
844 MSC_CL_PULL.v_view_name := 'MRP_AP_UMP_RESOURCE_REQMNTS_V';
845
846 IF (nvl(fnd_profile.value('MSC_INFLATE_WIP') ,'N')= 'N') THEN
847 lv_inflate_wip := 2 ;
848 ELSE
849 lv_inflate_wip := 1 ;
850 END IF;
851
852 -- if the profile MSC_INFLATE_WIP is set to YES then inflating the operation resource hours
853 -- and touch time by efficiency and utilization.
854 IF lv_inflate_wip = 1 AND MSC_CL_PULL.v_apps_ver>= MSC_UTIL.G_APPS115 THEN
855 v_temp_sql := ' (x.TOTAL_QUANTITY *(1/x.utilization)*(1/x.efficiency)) OPERATION_HOURS_REQUIRED,'||
856 ' (x.TOTAL_QUANTITY * (1/x.efficiency)) TOUCH_TIME,';
857 v_temp_whr := ' AND (x.ENTITY_TYPE <>5 or ((x.TOTAL_QUANTITY * (1/x.utilization)* (1/x.efficiency)) - x.HOURS_EXPENDED) >0) ';
858 ELSE
859 v_temp_sql := ' x.TOTAL_QUANTITY OPERATION_HOURS_REQUIRED, '||
860 ' x.TOTAL_QUANTITY TOUCH_TIME,';
861 v_temp_whr := ' AND (x.ENTITY_TYPE <>5 or (x.TOTAL_QUANTITY - x.HOURS_EXPENDED) > 0 ) ';
862 END IF;
863
864 v_sql_stmt:= ' INSERT INTO MSC_ST_RESOURCE_REQUIREMENTS '
865 ||'(SCHEDULE_DESIGNATOR_ID,'
866 ||'DEPARTMENT_ID,'
867 ||'ORGANIZATION_ID,'
868 ||'RESOURCE_ID,'
869 ||'ASSIGNED_UNITS,'
870 ||'OPERATION_HOURS_REQUIRED,'
871 ||'TOUCH_TIME,'
872 ||'UNADJUSTED_RESOURCE_HOURS,'
873 ||'DELETED_FLAG,'
874 ||'INVENTORY_ITEM_ID,'
875 ||'OPERATING_FLEET,'
876 ||'MAINTENANCE_REQUIREMENT,'--mnagilla
877 ||'MAINTENANCE_OBJECT_SOURCE,'
878 ||'SUPPLY_TYPE,'
879 ||'START_DATE,'
880 ||'REFRESH_ID,'
881 ||'SR_INSTANCE_ID)'
882 ||' SELECT '
883 ||' x.SIMULATION_PLAN_ID,'
884 ||' x.DEPARTMENT_ID,'
885 ||' x.OPERATING_ORG_ID,'
886 ||' x.BOM_RESOURCE_ID,'
887 ||' x.ASSIGNED_UNITS ,'
888 || v_temp_sql
889 ||' x.TOTAL_QUANTITY ,'
890 ||' 2,'
891 ||'x.INVENTORY_ITEM_ID,'
892 ||'x.FLEET_HEADER_ID,'
893 ||'x.TITLE,'--mnagilla
894 ||'x.MAINTENANCE_OBJECT_SOURCE,'
895 ||'x.SUPPLY_TYPE,'
896 ||'x.due_date,'
897 ||':v_refresh_id,'
898 ||':v_instance_id'
899 ||' FROM MRP_AP_UMP_RESOURCE_REQMNTS_V'||MSC_CL_PULL.v_dblink||' x'
900 ||' WHERE x.OPERATING_ORG_ID '||MSC_UTIL.v_in_org_str
901 ||' AND x.DUE_DATE >= :p_eam_cmro_st_date '
902 ||' AND x.DUE_DATE <= :p_eam_cmro_end_date '
903 || v_temp_whr
904 ;
905
906 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS,'lv_sql_stmt for eam forecast resource :' ||v_sql_stmt);
907
908 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_STATUS, 'SQL statement is '||v_sql_stmt||'-'||p_eam_cmro_st_date||'-'||p_eam_cmro_end_date);
909 EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id,
910 MSC_CL_PULL.v_instance_id
911 ,p_eam_cmro_st_date
912 ,p_eam_cmro_end_date;
913
914 COMMIT;
915
916 MSC_CL_PULL.v_table_name:= 'MSC_ST_DESIGNATORS';
917 MSC_CL_PULL.v_view_name := 'MRP_AP_DESIGNATORS_V';
918
919
920 v_sql_stmt:= ' INSERT INTO MSC_ST_DESIGNATORS( '
921 -- ||'(DESIGNATOR_ID, '
922 ||' DESIGNATOR, '
923 ||'DESIGNATOR_TYPE, '
924 ||'MPS_RELIEF,'
925 ||'INVENTORY_ATP_FLAG, '
926 ||'SRC_SIM_FCST_ID, '
927 ||'SRC_DESCRIPTION, '
928 ||'SRC_DESIGNATOR, '
929 ||'ORGANIZATION_ID, '
930 ||'DELETED_FLAG,'
931 ||'REFRESH_ID,'
932 ||'SR_INSTANCE_ID) '
933 ||'SELECT '
934 -- ||'SIMULATION_PLAN_ID, '
935 ||'-23453, '
936 ||'DESIGNATOR_TYPE, '
937 ||'MPS_RELIEF, '
938 ||'INVENTORY_ATP_FLAG, '
939 ||'SRC_SIM_FCST_ID, '
940 ||'DESCRIPTION, '
941 ||'SIMULATION_PLAN_NAME,'
942 ||'-23453, '
943 ||' 2,'
944 ||':v_refresh_id,'
948 --||' WHERE x.ORGANIZATION_ID '||MSC_UTIL.v_in_org_str;
945 ||':v_instance_id '
946 ||' FROM '
947 ||' MRP_AP_AHL_DESIGNATORS_V'||MSC_CL_PULL.v_dblink||' x';
949
950
951 EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id ;
952
953 COMMIT;
954
955
956 END IF;
957
958 END LOAD_CMRO_FORECASTS;
959
960 PROCEDURE LOAD_SUPPLY_RESERVATIONS IS
961 BEGIN
962
963 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_STATUS, 'In Procedure LOAD_SUPPLY_RESERVATIONS ');
964
965 IF MSC_CL_PULL.v_apps_ver >= MSC_UTIL.G_EAM_CMRO_SUP_VER THEN
966 MSC_CL_PULL.v_table_name:= 'MSC_ST_RESERVATIONS';
967 MSC_CL_PULL.v_view_name := 'MRP_AD_HARD_RESERVATIONS_V';
968
969 IF MSC_CL_PULL.v_lrnn<> -1 THEN -- incremental refresh
970 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_STATUS, 'In Procedure LOAD_SUPPLY_RESERVATIONS in v_lrnn'||MSC_CL_PULL.v_lrnn);
971
972 v_sql_stmt:= ' INSERT INTO MSC_ST_RESERVATIONS'
973 ||' ( TRANSACTION_ID,'
974 ||' ORGANIZATION_ID,'
975 ||' DELETED_FLAG,'
976 ||' REFRESH_ID,'
977 ||' SR_INSTANCE_ID)'
978 ||' select'
979 ||' x.DEMAND_ID,'
980 ||' x.ORGANIZATION_ID,'
981 ||' 1,'
982 ||' :v_refresh_id,'
983 ||' :v_instance_id'
984 ||' FROM MRP_AD_HARD_RESERVATIONS_V'
985 ||MSC_CL_PULL.V_DBLINK||' x'
986 ||' WHERE x.RN> '||MSC_CL_PULL.v_lrn
987 ||' AND x.ORGANIZATION_ID '||MSC_UTIL.v_in_org_str;
988
989 EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id,
990 MSC_CL_PULL.v_instance_id;
991 COMMIT;
992 END IF;
993
994 MSC_CL_PULL.v_table_name:= 'MSC_ST_RESERVATIONS';
995 MSC_CL_PULL.V_VIEW_NAME := 'MRP_AP_CMRO_RESERVATIONS_V';
996
997 IF MSC_CL_PULL.v_lrnn<> -1 THEN -- incremental refresh
998 v_union_sql := ' AND x.RN1> '||MSC_CL_PULL.v_lrn||' UNION '
999 ||' SELECT '
1000 ||'x.RESERVATION_ID * 2,'
1001 ||'x.DEMAND_SOURCE_HEADER_ID,'
1002 ||'x.DEMAND_SOURCE_LINE_ID,'
1003 ||'x.DISPOSITION_TYPE,'
1004 ||'x.ORGANIZATION_ID,'
1005 ||'x.INVENTORY_ITEM_ID,'
1006 ||'x.RESERVATION_TYPE,'
1007 ||'x.REQUIREMENT_DATE,'
1008 ||'x.RESERVATION_QUANTITY,'
1009 ||'x.SUBINVENTORY_ID,'
1010 ||'x.TASK_ID,'
1011 ||'x.PROJECT_ID,'
1012 ||'x.SUPPLY_SOURCE_TYPE_ID,'
1013 ||'x.SUPPLY_SOURCE_HEADER_ID,'
1014 ||'2,'
1015 ||' :v_refresh_id,'
1016 ||' :v_instance_id'
1017 ||' FROM MRP_AP_CMRO_RESERVATIONS_V'
1018 ||MSC_CL_PULL.v_dblink||' x'
1019 ||' WHERE x.ORGANIZATION_ID '|| MSC_UTIL.v_in_org_str
1020 ||' AND x.RN2> '||MSC_CL_PULL.v_lrn;
1021 ELSE
1022 v_union_sql :=' AND (x.RN1>'||MSC_CL_PULL.v_lrn
1023 ||' OR x.RN2>'||MSC_CL_PULL.v_lrn||')';
1024 END IF;
1025
1026 v_sql_stmt:= ' INSERT INTO MSC_ST_RESERVATIONS'
1027 ||'(TRANSACTION_ID,'
1028 ||'DISPOSITION_ID,'
1029 ||'DEMAND_SOURCE_LINE_ID,'
1030 ||'DISPOSITION_TYPE,'
1031 ||'ORGANIZATION_ID,'
1032 ||'INVENTORY_ITEM_ID,'
1033 ||'RESERVATION_TYPE,'
1034 ||'REQUIREMENT_DATE,'
1035 ||'RESERVED_QUANTITY,'
1036 ||'SUBINVENTORY,'
1037 ||'TASK_ID,'
1038 ||'PROJECT_ID,'
1039 ||'SUPPLY_SOURCE_TYPE_ID,'
1040 ||'SUPPLY_SOURCE_HEADER_ID,'
1041 ||'DELETED_FLAG,'
1042 ||'REFRESH_ID,'
1043 ||'SR_INSTANCE_ID)'
1044 ||' SELECT '
1045 ||'x.RESERVATION_ID * 2,'
1046 ||'x.DEMAND_SOURCE_HEADER_ID,'
1047 ||'x.DEMAND_SOURCE_LINE_ID,'
1048 ||'x.DISPOSITION_TYPE,'
1049 ||'x.ORGANIZATION_ID,'
1050 ||'x.INVENTORY_ITEM_ID,'
1051 ||'x.RESERVATION_TYPE,'
1052 ||'x.REQUIREMENT_DATE,'
1053 ||'x.RESERVATION_QUANTITY,'
1054 ||'x.SUBINVENTORY_ID,'
1055 ||'x.TASK_ID,'
1056 ||'x.PROJECT_ID,'
1057 ||'x.SUPPLY_SOURCE_TYPE_ID,'
1058 ||'x.SUPPLY_SOURCE_HEADER_ID,'
1059 ||'2,'
1060 ||' :v_refresh_id,'
1061 ||' :v_instance_id'
1062 ||' FROM MRP_AP_CMRO_RESERVATIONS_V'
1063 ||MSC_CL_PULL.v_dblink||' x'
1064 ||' WHERE x.ORGANIZATION_ID '|| MSC_UTIL.v_in_org_str
1065 ||v_union_sql;
1066
1067 IF MSC_CL_PULL.v_lrnn<> -1 THEN -- incremental refresh
1068 EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id,
1069 MSC_CL_PULL.v_instance_id,
1070 MSC_CL_PULL.v_refresh_id,
1071 MSC_CL_PULL.v_instance_id;
1072 ELSE
1073 EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id,
1074 MSC_CL_PULL.v_instance_id;
1075 END IF;
1076 END IF; /* MSC_UTIL.G_EAM_CMRO_SUP_VER */
1077
1078 END LOAD_SUPPLY_RESERVATIONS;
1079
1080 PROCEDURE LOAD_CLOSED_CMRO_WOS IS
1081 BEGIN
1082 -- ===================start Closed CMRO jobs =====================
1083
1084 IF MSC_CL_PULL.v_lrnn<> -1 THEN
1085 MSC_CL_PULL.v_table_name:= 'MSC_ST_SUPPLIES';
1086 MSC_CL_PULL.v_view_name := 'MRP_AD_CMRO_CLOSED_JOBS_V';
1087
1088 v_sql_stmt:=
1089 ' insert into MSC_ST_SUPPLIES'
1090 ||' ( DISPOSITION_ID,'
1091 ||' ORDER_TYPE,'
1092 ||' ORGANIZATION_ID,'
1093 ||' MAINTENANCE_OBJECT_SOURCE,'
1094 ||' DELETED_FLAG,'
1095 ||' REFRESH_ID,'
1096 ||' SR_INSTANCE_ID)'
1097 ||' select'
1098 ||' x.WIP_ENTITY_ID,'
1099 ||' x.ORDER_TYPE,'
1100 ||' x.ORGANIZATION_ID,'
1101 ||' x.MAINTENANCE_OBJECT_SOURCE,'
1102 ||' 1,'
1103 ||' :v_refresh_id,'
1104 ||' :v_instance_id'
1105 ||' from MRP_AD_CMRO_CLOSED_JOBS_V'||MSC_CL_PULL.v_dblink||' x'
1106 ||' WHERE x.RN> :v_lrn '
1107 ||' AND x.ORGANIZATION_ID '||MSC_UTIL.v_in_org_str;
1108
1109 EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id, MSC_CL_PULL.v_lrn;
1110
1111 COMMIT;
1112 END IF;
1113
1114 IF MSC_CL_PULL.v_lrnn<> -1 THEN -- incremental refresh
1115
1116 v_union_sql :=
1117 ' AND x.RN1 > :v_lrn '
1118 ||' UNION '
1119 ||' select'
1120 ||' x.Inventory_Item_ID,'
1121 ||' x.asset_item_id,'
1122 ||' x.visit_id,'
1123 ||' x.ORGANIZATION_ID,'
1124 ||' x.WIP_ENTITY_ID,'
1125 ||' x.WIP_ENTITY_NAME, '
1126 ||' x.ACTUAL_END_DATE,'
1127 ||' x.quantity_scrapped,'
1128 ||' x.quantity_completed,'
1129 ||' x.FIRM_PLANNED_STATUS_TYPE,'
1130 ||' x.START_DATE- :v_dgmt,'
1131 ||' x.ORDER_TYPE,'
1132 ||' x.PROJECT_ID,'
1133 ||' x.TASK_ID,'
1134 ||' x.SCHEDULE_GROUP_ID,'
1135 ||' x.BUILD_SEQUENCE,'
1136 ||' x.LINE_ID,'
1137 ||' x.ALTERNATE_BOM_DESIGNATOR,'
1138 ||' x.ALTERNATE_ROUTING_DESIGNATOR,'
1139 ||' x.END_ITEM_UNIT_NUMBER,'
1140 ||' x.STATUS_CODE,'
1141 ||' x.DEMAND_CLASS,'
1142 ||' 2,'
1143 ||' x.coproducts_supply,'
1144 ||' x.requested_start_date,'
1145 ||' x.requested_completion_date,'
1146 ||' x.schedule_priority,'
1147 ||' x.ACTUAL_START_DATE,'
1148 ||' x.cfm_routing_flag, '
1149 ||' x.wip_start_quantity,'
1150 ||' x.maintenance_object_source,'
1151 ||' x.description,'
1152 ||' x.maintenance_object_type,'
1153 ||' :v_refresh_id,'
1154 ||' :v_instance_id'
1155 ||' from MRP_AP_CMRO_CLOSED_JOBS_V'||MSC_CL_PULL.v_dblink||' x'
1156 ||' where x.ORGANIZATION_ID'||MSC_UTIL.v_in_org_str
1157 ||' AND x.RN2 > :v_lrn ';
1158
1159 ELSE
1160
1161 v_union_sql := ' ';
1162
1163 END IF;
1164
1165 MSC_CL_PULL.v_table_name:= 'MSC_ST_SUPPLIES';
1166 MSC_CL_PULL.v_view_name := 'MRP_AP_CMRO_CLOSED_JOBS_V';
1167
1168 v_sql_stmt:=
1169 'insert into MSC_ST_SUPPLIES'
1170 ||' ( INVENTORY_ITEM_ID,'
1171 ||' asset_item_id,'
1172 ||' visit_id,'
1173 ||' ORGANIZATION_ID,'
1174 ||' DISPOSITION_ID,'
1175 ||' ORDER_NUMBER,'
1176 ||' NEW_SCHEDULE_DATE,'
1177 ||' QTY_SCRAPPED,'
1178 ||' QTY_COMPLETED,'
1179 ||' FIRM_PLANNED_TYPE,'
1180 ||' NEW_WIP_START_DATE,'
1181 ||' ORDER_TYPE,'
1182 ||' PROJECT_ID,'
1183 ||' TASK_ID,'
1184 ||' SCHEDULE_GROUP_ID,'
1185 ||' BUILD_SEQUENCE,'
1186 ||' LINE_ID,'
1187 ||' ALTERNATE_BOM_DESIGNATOR,'
1188 ||' ALTERNATE_ROUTING_DESIGNATOR,'
1189 ||' UNIT_NUMBER,'
1190 ||' WIP_STATUS_CODE,'
1191 ||' DEMAND_CLASS,'
1192 ||' DELETED_FLAG,'
1193 ||' COPRODUCTS_SUPPLY,'
1194 ||' REQUESTED_START_DATE,'
1195 ||' REQUESTED_COMPLETION_DATE,'
1196 ||' SCHEDULE_PRIORITY,'
1197 ||' ACTUAL_START_DATE,'
1198 ||' CFM_ROUTING_FLAG,'
1199 ||' WIP_START_QUANTITY,'
1200 ||' MAINTENANCE_OBJECT_SOURCE,'
1201 ||' DESCRIPTION,'
1202 ||' MAINTENANCE_OBJECT_TYPE,'
1203 ||' REFRESH_ID,'
1204 ||' SR_INSTANCE_ID)'
1205 ||' select'
1206 ||' x.Inventory_Item_ID,'
1207 ||' x.asset_item_id,'
1208 ||' x.visit_id,'
1209 ||' x.ORGANIZATION_ID,'
1210 ||' x.WIP_ENTITY_ID,'
1211 ||' x.WIP_ENTITY_NAME, '
1212 ||' x.ACTUAL_END_DATE,'
1213 ||' x.quantity_scrapped,'
1214 ||' x.quantity_completed,'
1215 ||' x.FIRM_PLANNED_STATUS_TYPE,'
1216 ||' x.START_DATE- :v_dgmt,'
1217 ||' x.ORDER_TYPE,'
1218 ||' x.PROJECT_ID,'
1219 ||' x.TASK_ID,'
1220 ||' x.SCHEDULE_GROUP_ID,'
1221 ||' x.BUILD_SEQUENCE,'
1222 ||' x.LINE_ID,'
1223 ||' x.ALTERNATE_BOM_DESIGNATOR,'
1224 ||' x.ALTERNATE_ROUTING_DESIGNATOR,'
1225 ||' x.END_ITEM_UNIT_NUMBER,'
1226 ||' x.STATUS_CODE,'
1227 ||' x.DEMAND_CLASS,'
1228 ||' 2,'
1229 ||' x.coproducts_supply,'
1230 ||' x.requested_start_date,'
1231 ||' x.requested_completion_date,'
1232 ||' x.schedule_priority,'
1233 ||' x.ACTUAL_START_DATE,'
1234 ||' x.cfm_routing_flag, '
1235 ||' x.wip_start_quantity,'
1236 ||' x.maintenance_object_source,'
1237 ||' x.description,'
1238 ||' x.maintenance_object_type,'
1239 ||' :v_refresh_id,'
1240 ||' :v_instance_id'
1241 ||' from MRP_AP_CMRO_CLOSED_JOBS_V '||MSC_CL_PULL.v_dblink||' x'
1242 ||' where x.ORGANIZATION_ID'||MSC_UTIL.v_in_org_str;
1243
1244 v_sql_stmt:= v_sql_stmt || v_union_sql;
1245
1246 IF MSC_CL_PULL.v_lrnn<> -1 THEN -- incremental refresh
1247 --MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'to be removed: Ds debug: wip supply incr = '||v_sql_stmt);
1248 EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_dgmt,
1249 MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id,
1250 MSC_CL_PULL.v_lrn,
1251 MSC_CL_PULL.v_dgmt,
1252 MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id,
1253 MSC_CL_PULL.v_lrn;
1254
1255 ELSE -- For COmplete Refresh
1256 EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_dgmt,
1257 MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
1258 END IF;
1259 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'Number of rows for MRP_AP_CMRO_CLOSED_JOBS_V = '|| SQL%ROWCOUNT);
1260 COMMIT;
1261 -- ===================end Closed CMRO jobs =====================
1262 END LOAD_CLOSED_CMRO_WOS;
1263
1264 PROCEDURE LOAD_OSP_SUPPLY IS
1265
1266 v_item_type_id NUMBER := MSC_UTIL.G_PARTCONDN_ITEMTYPEID;
1267 v_item_type_good NUMBER := MSC_UTIL.G_PARTCONDN_GOOD;
1268 --v_item_type_bad NUMBER := MSC_UTIL.G_PARTCONDN_BAD;
1269
1270 BEGIN
1271
1272 MSC_CL_PULL.v_table_name:= 'MSC_ST_SUPPLIES';
1273 MSC_CL_PULL.v_view_name := 'MRP_AP_OSP_SUP_V';
1274
1275 IF (MSC_CL_PULL.v_apps_ver >= MSC_UTIL.G_APPS121 AND
1276 MSC_UTIL.G_CMRO_EAM_INT_ENABLED = 'Y' ) THEN
1277
1278 v_sql_stmt:=
1279 'insert into MSC_ST_SUPPLIES'
1280 ||' ( SR_MTL_SUPPLY_ID,'
1281 ||' INVENTORY_ITEM_ID,'
1282 ||' ORGANIZATION_ID,'
1283 ||' ORDER_TYPE,'
1284 ||' COLL_ORDER_TYPE,'
1285 ||' NEW_SCHEDULE_DATE,'
1286 ||' NEW_ORDER_QUANTITY,'
1287 ||' FIRM_PLANNED_TYPE,'
1288 ||' ORDER_NUMBER,'
1289 ||' SUPPLIER_ID,'
1290 ||' SUPPLIER_SITE_ID,'
1291 ||' PRODUCES_TO_STOCK,'
1292 ||' ITEM_TYPE_ID,'
1293 ||' ITEM_TYPE_VALUE,'
1294 ||' DELETED_FLAG,'
1295 ||' REFRESH_ID,'
1296 ||' SR_INSTANCE_ID)'
1297 ||' SELECT '
1298 ||' x.LINE_ID,'
1299 ||' x.INVENTORY_ITEM_ID,'
1300 ||' x.ORGANIZATION_ID,'
1301 ||' x.ORDER_TYPE,'
1302 ||' x.COLL_ORDER_TYPE,'
1303 ||' x.NEED_BY_DATE,'
1304 ||' x.QUANTITY,'
1305 ||' 1,'
1306 ||' x.ORDER_NUMBER,'
1307 ||' x.VENDOR_ID,'
1308 ||' x.VENDOR_SITE_ID,'
1309 ||' x.PRODUCES_TO_STOCK,'
1310 ||' :v_item_type_id,'
1311 ||' :v_item_type_good,'
1312 ||' 2,'
1313 ||' :v_refresh_id,'
1314 ||' :v_instance_id'
1315 ||' FROM MRP_AP_OSP_SUP_V '||MSC_CL_PULL.v_dblink||' x'
1316 ||' WHERE x.ORGANIZATION_ID '||MSC_UTIL.v_in_org_str ;
1317
1318 EXECUTE IMMEDIATE v_sql_stmt USING v_item_type_id,v_item_type_good,
1319 MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
1320
1321 COMMIT;
1322
1323 END IF;
1324 END LOAD_OSP_SUPPLY;
1325
1326 END MSC_CL_AHL_PULL;