[Home] [Help]
PACKAGE BODY: APPS.MSC_CL_EAM_PULL
Source
1 PACKAGE BODY MSC_CL_EAM_PULL AS
2 /* $Header:*/
3 v_sql_stmt VARCHAR2(32767);
4 v_temp_whr VARCHAR2(32767);
5 v_temp_sql VARCHAR2(32767);
6
7 PROCEDURE LOAD_EAM_INFO IS
8 BEGIN
9
10 IF MSC_CL_PULL.v_apps_ver >= MSC_UTIL.G_EAM_CMRO_SUP_VER THEN
11 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_STATUS, 'In Procedure LOAD_EAM_INFO ');
12
13 MSC_CL_PULL.v_table_name:= 'MSC_ST_EAM_ACT_ASSOCIATIONS';
14 MSC_CL_PULL.v_view_name := 'MRP_AD_EAM_ACT_ASSOC_V';
15
16 IF MSC_CL_PULL.v_lrnn<> -1 THEN -- incremental refresh
17 v_sql_stmt:= ' INSERT INTO MSC_ST_EAM_ACT_ASSOCIATIONS'
18 ||'(ASSET_ACTIVITY_ID,'
19 ||'ORGANIZATION_ID,'
20 ||'ASSET_REBUILD_ITEM_ID,'
21 ||'DELETED_FLAG,'
22 ||'REFRESH_NUMBER,'
23 ||'SR_INSTANCE_ID)'
24 ||' SELECT '
25 ||'x.ASSET_ACTIVITY_ID,'
26 ||'x.ORGANIZATION_ID,'
27 ||'x.ASSET_REBUILD_ITEM_ID,'
28 ||'1,'
29 ||':v_refresh_id,'
30 ||':v_instance_id'
31 ||' FROM MRP_AD_EAM_ACT_ASSOC_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 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS,'EAM info. --lv_sql_stmt' ||v_sql_stmt);
37 EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id,
38 MSC_CL_PULL.v_instance_id;
39 COMMIT;
40 END IF;
41
42
43 MSC_CL_PULL.v_table_name:= 'MSC_ST_EAM_ACT_ASSOCIATIONS';
44 MSC_CL_PULL.v_view_name := 'MRP_AP_EAM_ACT_ASSOC_V';
45
46 v_sql_stmt:= ' INSERT INTO MSC_ST_EAM_ACT_ASSOCIATIONS'
47 ||'(ASSET_ACTIVITY_ID,'
48 ||'ORGANIZATION_ID,'
49 ||'ACTIVITY,'
50 ||'ASSET_REBUILD_ITEM_ID,'
51 ||'ACTIVITY_TYPE,'
52 ||'ASSET_REBUILD_GROUP,'
53 ||'EAM_ITEM_TYPE,'
54 ||'DELETED_FLAG,'
55 ||'REFRESH_NUMBER,'
56 ||'SR_INSTANCE_ID)'
57 ||' SELECT '
58 ||'x.ASSET_ACTIVITY_ID,'
59 ||'x.ORGANIZATION_ID,'
60 ||'x.ACTIVITY,'
61 ||'x.INVENTORY_ITEM_ID,'
62 ||'6,'
63 ||'x.ASSET_REBUILD_GROUP,'
64 ||'x.EAM_ITEM_TYPE,'
65 ||'2,'
66 ||':v_refresh_id,'
67 ||':v_instance_id'
68 ||' FROM MRP_AP_EAM_ACT_ASSOC_V'
69 ||MSC_CL_PULL.V_DBLINK||' x'
70 ||' WHERE x.RN> '||MSC_CL_PULL.v_lrn
71 ||' AND x.ORGANIZATION_ID '||MSC_UTIL.v_in_org_str;
72 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS,'EAM info. --lv_sql_stmt' ||v_sql_stmt);
73
74 EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id,
75 MSC_CL_PULL.v_instance_id;
76
77 /*--------- PS requirements - New table to collect asset number details ---------*/
78
79 MSC_CL_PULL.v_table_name:= 'MSC_ST_EAM_ASSET_EQUIP_DTLS';
80 MSC_CL_PULL.v_view_name := 'MRP_AD_EAM_ASSET_EQUIP_V';
81
82 IF MSC_CL_PULL.v_lrnn<> -1 THEN -- incremental refresh
83 v_sql_stmt:= ' INSERT INTO MSC_ST_EAM_ASSET_EQUIP_DTLS'
84 ||'(ASSET_GROUP_ITEM_ID,'
85 ||'ORGANIZATION_ID,'
86 ||'ASSET_ACTIVITY_ID,'
87 ||'ASSET_NUMBER_ID,'
88 ||'EQUIPMENT_ITEM_ID,'
89 ||'DELETED_FLAG,'
90 ||'REFRESH_NUMBER,'
91 ||'SR_INSTANCE_ID)'
92 ||' SELECT '
93 ||'ASSET_GROUP_ITEM_ID,'
94 ||'ORGANIZATION_ID,'
95 ||'ASSET_ACTIVITY_ID,'
96 ||'ASSET_NUMBER_ID,'
97 ||'EQUIPMENT_ITEM_ID,'
98 ||'1,'
99 ||':v_refresh_id,'
100 ||':v_instance_id'
101 ||' FROM MRP_AD_EAM_ASSET_EQUIP_V'
102 ||MSC_CL_PULL.V_DBLINK||' x'
103 ||' WHERE x.RN> '||MSC_CL_PULL.v_lrn
104 ||' AND x.ORGANIZATION_ID '||MSC_UTIL.v_in_org_str;
105
106 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS,'EAM asset info. --lv_sql_stmt' ||v_sql_stmt);
107 EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id,
108 MSC_CL_PULL.v_instance_id;
109 COMMIT;
110 END IF;
111
112
113 MSC_CL_PULL.v_table_name:= 'MSC_ST_EAM_ASSET_EQUIP_DTLS';
114 MSC_CL_PULL.v_view_name := 'MRP_AP_EAM_ASSET_EQUIP_V';
115
116 v_sql_stmt:= ' INSERT INTO MSC_ST_EAM_ASSET_EQUIP_DTLS'
117 ||'(ASSET_GROUP_ITEM_ID,'
118 ||'ORGANIZATION_ID,'
119 ||'ASSET_ACTIVITY_ID,'
120 ||'ASSET_NUMBER_ID,'
121 ||'ASSET_NUMBER,'
122 ||'EQUIPMENT_ITEM_ID,'
123 ||'EQUIPMENT_SERIAL_NUM,'
124 ||'RESOURCE_ID,'
125 ||'DEPARTMENT_ID,'
126 ||'RESOURCE_CODE,'
127 ||'SCHEDULE_TO_INSTANCE,'
128 ||'DELETED_FLAG,'
129 ||'REFRESH_NUMBER,'
130 ||'SR_INSTANCE_ID)'
131 ||' SELECT '
132 ||'x.ASSET_GROUP_ID,'
133 ||'x.ORGANIZATION_ID,'
134 ||'x.ASSET_ACTIVITY_ID,'
135 ||'x.INSTANCE_ID,'
136 ||'x.ASSET_SERIAL_NUMBER,'
137 ||'x.EQUIPMENT_ITEM_ID,'
138 ||'x.EQUIPMENT_SERIAL_NUMBER,'
139 ||'y.RESOURCE_ID *2 ,'
140 ||'y.DEPARTMENT_ID *2 ,'
141 ||'y.RESOURCE_CODE,'
142 ||'y.SCHEDULE_TO_INSTANCE,'
143 ||'2,'
144 ||':v_refresh_id,'
145 ||':v_instance_id'
146 ||' FROM MRP_AP_EAM_ASSET_EQUIP_V'
147 ||MSC_CL_PULL.V_DBLINK||' x,'
148 ||' MTL_EAM_EQUIP_RESOURCES_V'
149 ||MSC_CL_PULL.V_DBLINK||' y'
150 ||' WHERE x.RN> '||MSC_CL_PULL.v_lrn
151 ||' AND x.ORGANIZATION_ID '||MSC_UTIL.v_in_org_str
152 ||' AND x.ORGANIZATION_ID = y.ORGANIZATION_ID (+)'
153 ||' AND x.EQUIPMENT_SERIAL_NUMBER = y.SERIAL_NUMBER (+)'
154 ||' AND x.EQUIPMENT_ITEM_ID = y.INVENTORY_ITEM_ID (+)';
155
156 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS,'EAM asset info. --lv_sql_stmt' ||v_sql_stmt);
157
158 EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id,
159 MSC_CL_PULL.v_instance_id;
160 END IF;
161 COMMIT;
162 END LOAD_EAM_INFO;
163
164 /* Load EAM forecasts into MSC_ST_DESISNATORS and MSC_ST_SUPPLIES table*/
165
166 PROCEDURE LOAD_EAM_FORECASTS (eam_fc_st_date IN DATE,
167 eam_fc_end_date IN DATE ) IS
168 lv_eam_fc_st_date DATE;
169 lv_eam_fc_end_date DATE;
170 lv_inflate_wip NUMBER;
171
172
173 BEGIN
174
175 lv_eam_fc_st_date := eam_fc_st_date;
176 lv_eam_fc_end_date := eam_fc_end_date ;
177
178 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_STATUS, 'In Procedure LOAD_EAM_FORECASTS ');
179 IF MSC_CL_PULL.v_apps_ver >= MSC_UTIL.G_EAM_CMRO_SUP_VER THEN
180
181
182 MSC_CL_PULL.v_table_name:= 'MSC_ST_DESIGNATORS';
183 MSC_CL_PULL.V_VIEW_NAME := 'MRP_AP_EAM_FORECAST_DESIGS_V';
184
185 v_sql_stmt:= ' INSERT INTO MSC_ST_DESIGNATORS'
186 ||'(SRC_SIM_FCST_ID,'
187 ||'ORGANIZATION_ID,'
188 ||'DESIGNATOR,'
189 ||'SRC_DESIGNATOR,'
190 ||'SRC_DESCRIPTION,'
191 ||'DESIGNATOR_TYPE,'
192 ||'MPS_RELIEF,'
193 ||'INVENTORY_ATP_FLAG,'
194 ||'DELETED_FLAG,'
195 ||'REFRESH_ID,'
196 ||'SR_INSTANCE_ID)'
197 ||' SELECT '
198 ||'x.FORECAST_ID,'
199 ||'x.ORGANIZATION_ID,'
200 ||'-23453,'
201 ||'x.FORECAST_NAME,'
202 ||'x.DESCRIPTION,'
203 ||'x.DESIGNATOR_TYPE,'
204 ||'-999,'
205 ||'2,'
206 ||'2,'
207 ||':v_refresh_id,'
208 ||':v_instance_id'
209 ||' FROM MRP_AP_EAM_FORECAST_DESIGS_V'
210 ||MSC_CL_PULL.V_DBLINK||' x'
211 ||' WHERE x.RN> '||MSC_CL_PULL.v_lrn
212 ||' AND x.ORGANIZATION_ID '||MSC_UTIL.v_in_org_str;
213
214 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS,'lv_sql_stmt' ||v_sql_stmt);
215
216 EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id,
217 MSC_CL_PULL.v_instance_id ;
218
219 COMMIT;
220
221
222 MSC_CL_PULL.v_table_name:= 'MSC_ST_SUPPLIES';
223 MSC_CL_PULL.v_view_name := 'MRP_AP_EAM_FORECASTS_V';
224
225 v_sql_stmt:= 'Insert into MSC_ST_SUPPLIES'
226 ||'( INVENTORY_ITEM_ID,'
227 ||'ORGANIZATION_ID,'
228 ||'ORDER_NUMBER,'
229 ||'NEW_ORDER_QUANTITY,'
230 ||'NEW_SCHEDULE_DATE,'
231 ||'FIRM_PLANNED_TYPE,'
232 || 'NEW_WIP_START_DATE,'
233 ||'ORDER_TYPE,'
234 ||'COLL_ORDER_TYPE,'
235 ||'DELETED_FLAG,'
236 ||'ASSET_ITEM_ID,'
237 ||'MAINTENANCE_OBJECT_SOURCE,'
238 ||'CLASS_CODE,' --mnagilla
239 ||'TO_BE_EXPLODED,'
240 ||'SCHEDULE_DESIGNATOR_ID,'
241 ||'SOURCE_ITEM_ID,'
242 ||'REFRESH_ID,'
243 ||'SR_INSTANCE_ID)'
244 ||' SELECT '
245 ||'x.ASSET_ITEM_ID,'
246 ||'x.ORGANIZATION_ID,'
247 ||'x.ORDER_NUMBER,'
248 ||'x.NEW_ORDER_QUANTITY,'
249 ||'LAST_DAY(x.SCHEDULED_COMPLETION_DATE),'
250 ||'1,'
251 ||'x.SCHEDULED_COMPLETION_DATE,'
252 ||'x.ORDER_TYPE,'
253 ||' 992, '
254 ||'2,'
255 ||'x.ASSET_ITEM_ID,'
256 ||'x.MAINTENANCE_OBJECT_SOURCE,'
257 ||'x.CLASS_CODE,'
258 ||'1,'
259 ||'x.FORECAST_ID,'
260 ||'x.ASSET_ITEM_ID,'
261 ||' :v_refresh_id,'
262 ||' :v_instance_id'
263 ||' from MRP_AP_EAM_FORECASTS_V '
264 ||MSC_CL_PULL.v_dblink||' x'
265 ||' where x.ORGANIZATION_ID'||MSC_UTIL.v_in_org_str
266 ||' and x.SCHEDULED_COMPLETION_DATE >= :lv_eam_fc_st_date'
267 ||' and x.SCHEDULED_COMPLETION_DATE <= :lv_eam_fc_end_date';
268 -- ||' AND x.RN1 > '||MSC_CL_PULL.v_lrn ;
269 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS,'lv_sql_stmt ' ||v_sql_stmt);
270 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS,'lv_sql_stmt ' ||v_sql_stmt);
271
272 EXECUTE IMMEDIATE v_sql_stmt
273 USING MSC_CL_PULL.v_refresh_id,
274 MSC_CL_PULL.v_instance_id,
275 lv_eam_fc_st_date,
276 lv_eam_fc_end_date;
277
278 COMMIT;
279
280 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS,'lv_sql_stmt for eam forecast :' ||v_sql_stmt);
281 MSC_CL_PULL.v_table_name:= 'MSC_ST_DEMANDS';
282 MSC_CL_PULL.v_view_name := 'MRP_AP_EAM_FC_MATREQ_V';
283
284 v_sql_stmt:= ' INSERT INTO MSC_ST_DEMANDS ( '
285 ||'SCHEDULE_DESIGNATOR_ID,'
286 ||'ORGANIZATION_ID,'
287 ||'INVENTORY_ITEM_ID,'
288 ||'USING_ASSEMBLY_ITEM_ID,'
289 ||'USING_REQUIREMENT_QUANTITY,'
290 ||'USING_ASSEMBLY_DEMAND_DATE, '
291 ||'DEMAND_TYPE,'
292 ||'ORIGINATION_TYPE,'
293 ||'DELETED_FLAG,'
294 ||'ASSET_ITEM_ID,'
295 ||'CLASS_CODE,'
296 ||'MAINTENANCE_OBJECT_SOURCE,'
297 ||'REFRESH_ID,'
298 ||'SR_INSTANCE_ID)'
299 ||'SELECT '
300 ||'x.FORECAST_ID,'
301 ||'x.ORGANIZATION_ID,'
302 ||'x.COMPONENT,'
303 ||'x.ASSET_ITEM_ID,'
304 ||'x.QUANTITY,'
305 ||'x.USING_ASSEMBLY_DEMAND_DATE,'
306 ||'x.DEMAND_TYPE,'
307 ||'x.ORIGINATION_TYPE,'
308 ||' 2,'
309 ||'x.ASSET_ITEM_ID,'
310 ||'x.CLASS_CODE,'
311 ||'x. MAINTENANCE_OBJECT_SOURCE,'
312 ||':v_refresh_id,'
313 ||':v_instance_id'
314 ||' FROM MRP_AP_EAM_FC_MATREQ_V'||MSC_CL_PULL.v_dblink||' x'
315 ||' WHERE x.ORGANIZATION_ID '||MSC_UTIL.v_in_org_str
316 ||' AND x.USING_ASSEMBLY_DEMAND_DATE >= :lv_eam_fc_st_date '
317 ||' AND x.USING_ASSEMBLY_DEMAND_DATE <= :lv_eam_fc_end_date '
318 -- ||' AND (x.RN1 >'||MSC_CL_PULL.v_lrn
319 -- ||' AND x.RN2 >'||MSC_CL_PULL.v_lrn||')'
320 ;
321
322 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS,'lv_sql_stmt for eam forecast demands :' ||v_sql_stmt);
323
324 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_STATUS, 'SQL statement is '||v_sql_stmt||'-'||lv_eam_fc_st_date||'-'||lv_eam_fc_end_date);
325 EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id,
326 MSC_CL_PULL.v_instance_id
327 ,lv_eam_fc_st_date
328 ,lv_eam_fc_end_date;
329 COMMIT;
330
331
332 MSC_CL_PULL.v_table_name:= 'MSC_ST_RESOURCE_REQUIREMENTS';
333 MSC_CL_PULL.v_view_name := 'MRP_AP_EAM_FC_RESREQ_V';
334
335 /* check for inflation and add code here if it is in pull code*/
336 IF MSC_CL_PULL.WIP_ENABLED= MSC_UTIL.SYS_YES THEN
337
338 IF (nvl(fnd_profile.value('MSC_INFLATE_WIP') ,'N')= 'N') THEN
339 lv_inflate_wip := 2 ;
340 ELSE
341 lv_inflate_wip := 1 ;
342 END IF;
343 /* check for inflation and add code here if it is in pull code*/
344 -- if the profile MSC_INFLATE_WIP is set to YES then inflating the operation resource hours
345 -- and touch time by efficiency and utilization.
346 IF lv_inflate_wip = 1 AND MSC_CL_PULL.v_apps_ver>= MSC_UTIL.G_APPS115 THEN
347 v_temp_sql := ' (x.TOTAL_QUANTITY *(1/x.utilization)*(1/x.efficiency)) OPERATION_HOURS_REQUIRED,'||
348 ' (x.TOTAL_QUANTITY * (1/x.efficiency)) TOUCH_TIME,';
349 v_temp_whr := ' AND (x.ENTITY_TYPE <>5 or ((x.TOTAL_QUANTITY * (1/x.utilization)* (1/x.efficiency)) - x.HOURS_EXPENDED) >0) ';
350 ELSE
351 v_temp_sql := ' x.TOTAL_QUANTITY OPERATION_HOURS_REQUIRED, '||
352 ' x.TOTAL_QUANTITY TOUCH_TIME,';
353 v_temp_whr := ' AND (x.ENTITY_TYPE <>5 or (x.TOTAL_QUANTITY - x.HOURS_EXPENDED) > 0 ) ';
354 END IF;
355 ELSE
356 v_temp_sql := ' x.TOTAL_QUANTITY OPERATION_HOURS_REQUIRED, '||
357 ' x.TOTAL_QUANTITY TOUCH_TIME,';
358 v_temp_whr := '';
359 END IF;
360 v_sql_stmt:= ' INSERT INTO MSC_ST_RESOURCE_REQUIREMENTS '
361 ||'(SCHEDULE_DESIGNATOR_ID,'
362 ||'DEPARTMENT_ID,'
363 ||'ORGANIZATION_ID,'
364 ||'RESOURCE_ID,'
365 ||'ASSIGNED_UNITS,'
366 ||'START_DATE,'
367 ||'OPERATION_HOURS_REQUIRED,'
368 ||'TOUCH_TIME,'
369 ||'UNADJUSTED_RESOURCE_HOURS,'
370 ||'DELETED_FLAG,'
371 ||'INVENTORY_ITEM_ID,'
372 ||'CLASS_CODE,'--mnagilla
373 ||'MAINTENANCE_OBJECT_SOURCE,'
374 ||'supply_type,'
375 ||'REFRESH_ID,'
376 ||'SR_INSTANCE_ID)'
377 ||' SELECT'
378 ||' x.FORECAST_ID,'
379 ||' x.DEPARTMENT_ID,'
380 ||' x.ORGANIZATION_ID,'
381 ||' x.RESOURCE_ID,'
382 ||' x.ASSIGNED_UNITS ,'
383 ||'x.START_DATE ,'
384 || v_temp_sql
385 ||' x.TOTAL_QUANTITY ,'
386 ||' 2,'
387 ||'x.ASSET_ITEM_ID,'
388 ||'x.CLASS_CODE,'--mnagilla
389 ||'x. MAINTENANCE_OBJECT_SOURCE,'
390 ||'x.supply_type,'
391 ||':v_refresh_id,'
392 ||':v_instance_id'
393 ||' FROM MRP_AP_EAM_FC_RESREQ_V'||MSC_CL_PULL.v_dblink||' x'
394 ||' WHERE x.ORGANIZATION_ID '||MSC_UTIL.v_in_org_str
395 ||' AND x.START_DATE >= :lv_eam_fc_st_date '
396 ||' AND x.START_DATE <= :lv_eam_fc_end_date '
397 || v_temp_whr
398 ;
399
400 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS,'lv_sql_stmt for eam forecast resource :' ||v_sql_stmt);
401
402 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_STATUS, 'SQL statement is '||v_sql_stmt||'-'||lv_eam_fc_st_date||'-'||lv_eam_fc_end_date);
403 EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id,
404 MSC_CL_PULL.v_instance_id
405 ,lv_eam_fc_st_date
406 ,lv_eam_fc_end_date;
407
408 COMMIT;
409
410
411 END IF; /* MSC_UTIL.G_EAM_CMRO_SUP_VER */
412
413 END LOAD_EAM_FORECASTS;
414
415
416 END MSC_CL_EAM_PULL;