[Home] [Help]
PACKAGE BODY: APPS.MTH_MV_TO_FACT_PKG
Source
1 PACKAGE BODY MTH_MV_TO_FACT_PKG AS
2 /*$Header: mthmvfb.pls 120.4.12020000.1 2012/07/24 16:11:26 sasuren noship $*/
3
4 /*******************************************************************************
5 * Procedure :MTH_POPULATE_EQUIP_HRCHY_MV *
6 * Description :This procedure calculates the resource cost *
7 * File Name :MTHMVFB.PLS *
8 * Visibility :Public *
9 * Parameters : *
10 * Modification log : *
11 * Author Date Change *
12 * Sanjeev Ramani 02-Mar-2012 Initial Creation *
13 *******************************************************************************/
14
15 PROCEDURE MTH_POPULATE_EQUIP_HRCHY_MV IS
16 v_log_from_date DATE; -- 14152929 (sasuren)
17 v_log_to_date DATE;
18 v_unassigned_val VARCHAR2(30);
19
20 BEGIN
21 mth_util_pkg.log_msg('MTH_POPULATE_EQUIP_HRCHY_MV start', mth_util_pkg.G_DBG_PROC_FUN_START);
22
23 -- Initialize default parameters
24 v_log_to_date := sysdate;
25 v_unassigned_val := MTH_UTIL_PKG.MTH_UA_GET_VAL;
26
27 -- Call mth_run_log_pre_load
28 mth_util_pkg.mth_run_log_pre_load('MTH_EQUIPMENT_DENORM_D_MV',v_unassigned_val,NULL,NULL,0,v_log_to_date);
29 mth_util_pkg.GET_RUN_LOG_DATES('MTH_EQUIPMENT_DENORM_D_MV',NULL,NULL,NULL,v_log_from_date,v_log_to_date);
30 mth_util_pkg.log_msg('v_log_from_date - ' || to_char(v_log_from_date,'DD-MON-YYYY HH24:MI:SS'), mth_util_pkg.G_DBG_VAR_VAL);
31 mth_util_pkg.log_msg('v_log_to_date - ' || to_char(v_log_to_date,'DD-MON-YYYY HH24:MI:SS'), mth_util_pkg.G_DBG_VAR_VAL);
32
33 -- Update the Resource cost and other columns on the equipment shifts table
34
35 MERGE
36 INTO
37 MTH_EQUIPMENT_SHIFTS_D
38 USING
39 (SELECT
40 MTH_EQUIPMENT_DENORM_D_SQ.EQUIPMENT_FK_KEY "EQUIPMENT_FK_KEY",
41 MTH_EQUIPMENT_SHIFTS_D_SQ.SHIFT_WORKDAY_FK_KEY "SHIFT_WORKDAY_FK_KEY",
42 v_log_to_date "LAST_UPDATE_DATE",
43 v_unassigned_val "LAST_UPDATE_SYSTEM_ID",
44 v_unassigned_val "LAST_UPDATED_BY",
45 v_unassigned_val "LAST_UPDATE_LOGIN",
46 MTH_RESOURCE_COST_MV_SQ.RESOURCE_FK_KEY,
47 MTH_RESOURCE_COST_MV_SQ.COST
48 FROM
49 /*(SELECT
50 DISTINCT
51 MTH_RUN_LOG.FROM_DATE,
52 MTH_RUN_LOG.TO_DATE
53 FROM
54 MTH_RUN_LOG
55 WHERE
56 (MTH_RUN_LOG.FACT_TABLE = 'MTH_EQUIPMENT_DENORM_D_MV' )) MTH_RUN_LOG_SQ , */
57 (SELECT
58 MTH_EQUIPMENT_DENORM_D.EQUIPMENT_FK_KEY,
59 MTH_EQUIPMENT_DENORM_D.LEVEL9_LEVEL_KEY ,
60 MTH_EQUIPMENT_DENORM_D.EQUIPMENT_EFFECTIVE_DATE,
61 MTH_EQUIPMENT_DENORM_D.EQUIPMENT_EXPIRATION_DATE,
62 MTH_EQUIPMENT_DENORM_D.EQUIPMENT_HIERARCHY_KEY
63 FROM MTH_EQUIPMENT_DENORM_D ) MTH_EQUIPMENT_DENORM_D_SQ,
64 (SELECT
65 MTH_EQUIP_HIERARCHY.HIERARCHY_ID,
66 MTH_EQUIP_HIERARCHY.LEVEL_NUM,
67 MTH_EQUIP_HIERARCHY.LEVEL_FK_KEY,
68 MTH_EQUIP_HIERARCHY.PARENT_FK_KEY,
69 MTH_EQUIP_HIERARCHY.EFFECTIVE_DATE,
70 MTH_EQUIP_HIERARCHY.LAST_UPDATE_DATE
71 FROM MTH_EQUIP_HIERARCHY) MTH_EQUIP_HIERARCHY_SQ ,
72 (SELECT
73 MTH_RESOURCE_COST_MV.RESOURCE_FK_KEY,
74 MTH_RESOURCE_COST_MV.COST
75 FROM MTH_RESOURCE_COST_MV ) MTH_RESOURCE_COST_MV_SQ,
76 (SELECT
77 MTH_SHIFT_GREGORIAN_DENORM_MV.SHIFT_WORKDAY_PK_KEY,
78 MTH_SHIFT_GREGORIAN_DENORM_MV.SHIFT_DATE,
79 MTH_SHIFT_GREGORIAN_DENORM_MV.FROM_DATE
80 FROM MTH_SHIFT_GREGORIAN_DENORM_MV) MTH_SHIFT_GREGORIAN_DENORM_SQ ,
81 (SELECT
82 MTH_EQUIPMENT_SHIFTS_D.EQUIPMENT_FK_KEY,
83 MTH_EQUIPMENT_SHIFTS_D.SHIFT_WORKDAY_FK_KEY
84 FROM MTH_EQUIPMENT_SHIFTS_D
85 WHERE ENTITY_TYPE = 'EQUIPMENT'
86 GROUP BY EQUIPMENT_FK_KEY,SHIFT_WORKDAY_FK_KEY ) MTH_EQUIPMENT_SHIFTS_D_SQ
87 WHERE
88 ( MTH_EQUIP_HIERARCHY_SQ.LAST_UPDATE_DATE > v_log_from_date AND
89 MTH_EQUIP_HIERARCHY_SQ.LAST_UPDATE_DATE <= v_log_to_date AND
90 MTH_EQUIP_HIERARCHY_SQ.HIERARCHY_ID = -2 and
91 MTH_EQUIP_HIERARCHY_SQ.HIERARCHY_ID = MTH_EQUIPMENT_DENORM_D_SQ.EQUIPMENT_HIERARCHY_KEY and
92 MTH_EQUIP_HIERARCHY_SQ.LEVEL_NUM = 10 and
93 MTH_EQUIP_HIERARCHY_SQ.LEVEL_FK_KEY = MTH_EQUIPMENT_DENORM_D_SQ.EQUIPMENT_FK_KEY And
94 MTH_EQUIPMENT_DENORM_D_SQ.LEVEL9_LEVEL_KEY = MTH_RESOURCE_COST_MV_SQ.RESOURCE_FK_KEY (+) And
95 MTH_EQUIPMENT_DENORM_D_SQ.EQUIPMENT_EFFECTIVE_DATE = MTH_EQUIP_HIERARCHY_SQ.EFFECTIVE_DATE AND
96 MTH_EQUIPMENT_DENORM_D_SQ.EQUIPMENT_FK_KEY = MTH_EQUIPMENT_SHIFTS_D_SQ.EQUIPMENT_FK_KEY AND
97 MTH_EQUIPMENT_SHIFTS_D_SQ.SHIFT_WORKDAY_FK_KEY = MTH_SHIFT_GREGORIAN_DENORM_SQ.SHIFT_WORKDAY_PK_KEY AND
98 MTH_SHIFT_GREGORIAN_DENORM_SQ.FROM_DATE between MTH_EQUIPMENT_DENORM_D_SQ.EQUIPMENT_EFFECTIVE_DATE AND
99 nvl( MTH_EQUIPMENT_DENORM_D_SQ.EQUIPMENT_EXPIRATION_DATE,
100 TO_DATE('4000-01-01', 'YYYY-MM-DD')))
101 )
102 MERGE_EQUIP_SHIFTS_SQ
103 ON
104 ( MTH_EQUIPMENT_SHIFTS_D.EQUIPMENT_FK_KEY = MERGE_EQUIP_SHIFTS_SQ.EQUIPMENT_FK_KEY AND
105 MTH_EQUIPMENT_SHIFTS_D.SHIFT_WORKDAY_FK_KEY = MERGE_EQUIP_SHIFTS_SQ.SHIFT_WORKDAY_FK_KEY)
106
107 WHEN MATCHED THEN
108 UPDATE
109 SET
110 LAST_UPDATE_DATE = MERGE_EQUIP_SHIFTS_SQ.LAST_UPDATE_DATE,
111 LAST_UPDATE_SYSTEM_ID = MERGE_EQUIP_SHIFTS_SQ.LAST_UPDATE_SYSTEM_ID,
112 LAST_UPDATED_BY = MERGE_EQUIP_SHIFTS_SQ.LAST_UPDATED_BY,
113 LAST_UPDATE_LOGIN = MERGE_EQUIP_SHIFTS_SQ.LAST_UPDATE_LOGIN,
114 RESOURCE_FK_KEY = MERGE_EQUIP_SHIFTS_SQ.RESOURCE_FK_KEY,
115 RESOURCE_COST = MERGE_EQUIP_SHIFTS_SQ.COST ;
116
117 mth_util_pkg.log_msg('Number of rows updated in MTH_EQUIPMENT_SHIFTS_D - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
118
119 -- Update the Resource cost and other columns on the output summary table
120
121 MERGE
122 INTO
123 MTH_EQUIP_OUTPUT_SUMMARY
124 USING
125 (SELECT
126 MTH_EQUIPMENT_DENORM_D_SQ.EQUIPMENT_FK_KEY "EQUIPMENT_FK_KEY",
127 MTH_EQUIP_OUTPUT_SUMMARY_SQ.SHIFT_WORKDAY_FK_KEY "SHIFT_WORKDAY_FK_KEY",
128 v_log_to_date "LAST_UPDATE_DATE",
129 v_unassigned_val "LAST_UPDATE_SYSTEM_ID",
130 v_unassigned_val "LAST_UPDATED_BY",
131 v_unassigned_val "LAST_UPDATE_LOGIN",
132 MTH_RESOURCE_COST_MV_SQ.RESOURCE_FK_KEY,
133 MTH_RESOURCE_COST_MV_SQ.COST
134 FROM
135 /*(SELECT
136 DISTINCT
137 MTH_RUN_LOG.FROM_DATE,
138 MTH_RUN_LOG.TO_DATE
139 FROM
140 MTH_RUN_LOG
141 WHERE
142 (MTH_RUN_LOG.FACT_TABLE = 'MTH_EQUIPMENT_DENORM_D_MV' )) MTH_RUN_LOG_SQ ,*/
143 (SELECT
144 MTH_EQUIPMENT_DENORM_D.EQUIPMENT_FK_KEY,
145 MTH_EQUIPMENT_DENORM_D.LEVEL9_LEVEL_KEY ,
146 MTH_EQUIPMENT_DENORM_D.EQUIPMENT_EFFECTIVE_DATE,
147 MTH_EQUIPMENT_DENORM_D.EQUIPMENT_EXPIRATION_DATE,
148 MTH_EQUIPMENT_DENORM_D.EQUIPMENT_HIERARCHY_KEY
149 FROM MTH_EQUIPMENT_DENORM_D ) MTH_EQUIPMENT_DENORM_D_SQ,
150 (SELECT
151 MTH_EQUIP_HIERARCHY.HIERARCHY_ID,
152 MTH_EQUIP_HIERARCHY.LEVEL_NUM,
153 MTH_EQUIP_HIERARCHY.LEVEL_FK_KEY,
154 MTH_EQUIP_HIERARCHY.PARENT_FK_KEY,
155 MTH_EQUIP_HIERARCHY.EFFECTIVE_DATE,
156 MTH_EQUIP_HIERARCHY.LAST_UPDATE_DATE
157 FROM MTH_EQUIP_HIERARCHY) MTH_EQUIP_HIERARCHY_SQ ,
158 (SELECT
159 MTH_RESOURCE_COST_MV.RESOURCE_FK_KEY,
160 MTH_RESOURCE_COST_MV.COST
161 FROM MTH_RESOURCE_COST_MV ) MTH_RESOURCE_COST_MV_SQ,
162 (SELECT
163 MTH_SHIFT_GREGORIAN_DENORM_MV.SHIFT_WORKDAY_PK_KEY,
164 MTH_SHIFT_GREGORIAN_DENORM_MV.SHIFT_DATE,
165 MTH_SHIFT_GREGORIAN_DENORM_MV.FROM_DATE
166 FROM MTH_SHIFT_GREGORIAN_DENORM_MV) MTH_SHIFT_GREGORIAN_DENORM_SQ ,
167 (SELECT
168 MTH_EQUIP_OUTPUT_SUMMARY.EQUIPMENT_FK_KEY,
169 MTH_EQUIP_OUTPUT_SUMMARY.SHIFT_WORKDAY_FK_KEY
170 FROM MTH_EQUIP_OUTPUT_SUMMARY
171 GROUP BY EQUIPMENT_FK_KEY,SHIFT_WORKDAY_FK_KEY) MTH_EQUIP_OUTPUT_SUMMARY_SQ
172 WHERE
173 ( MTH_EQUIP_HIERARCHY_SQ.LAST_UPDATE_DATE > v_log_from_date AND
174 MTH_EQUIP_HIERARCHY_SQ.LAST_UPDATE_DATE <= v_log_to_date AND
175 MTH_EQUIP_HIERARCHY_SQ.HIERARCHY_ID = -2 and
176 MTH_EQUIP_HIERARCHY_SQ.HIERARCHY_ID = MTH_EQUIPMENT_DENORM_D_SQ.EQUIPMENT_HIERARCHY_KEY and
177 MTH_EQUIP_HIERARCHY_SQ.LEVEL_NUM = 10 and
178 MTH_EQUIP_HIERARCHY_SQ.LEVEL_FK_KEY = MTH_EQUIPMENT_DENORM_D_SQ.EQUIPMENT_FK_KEY And
179 MTH_EQUIPMENT_DENORM_D_SQ.LEVEL9_LEVEL_KEY = MTH_RESOURCE_COST_MV_SQ.RESOURCE_FK_KEY (+) And
180 MTH_EQUIPMENT_DENORM_D_SQ.EQUIPMENT_EFFECTIVE_DATE = MTH_EQUIP_HIERARCHY_SQ.EFFECTIVE_DATE AND
181 MTH_EQUIPMENT_DENORM_D_SQ.EQUIPMENT_FK_KEY = MTH_EQUIP_OUTPUT_SUMMARY_SQ.EQUIPMENT_FK_KEY AND
182 MTH_EQUIP_OUTPUT_SUMMARY_SQ.SHIFT_WORKDAY_FK_KEY = MTH_SHIFT_GREGORIAN_DENORM_SQ.SHIFT_WORKDAY_PK_KEY AND
183 MTH_SHIFT_GREGORIAN_DENORM_SQ.FROM_DATE between MTH_EQUIPMENT_DENORM_D_SQ.EQUIPMENT_EFFECTIVE_DATE AND
184 nvl( MTH_EQUIPMENT_DENORM_D_SQ.EQUIPMENT_EXPIRATION_DATE,
185 TO_DATE('4000-01-01', 'YYYY-MM-DD')))
186 )
187 MERGE_EQUIP_OUTPUT_SQ
188 ON
189 ( MTH_EQUIP_OUTPUT_SUMMARY.EQUIPMENT_FK_KEY = MERGE_EQUIP_OUTPUT_SQ.EQUIPMENT_FK_KEY AND
190 MTH_EQUIP_OUTPUT_SUMMARY.SHIFT_WORKDAY_FK_KEY = MERGE_EQUIP_OUTPUT_SQ.SHIFT_WORKDAY_FK_KEY)
191 WHEN MATCHED THEN
192 UPDATE
193 SET
194 LAST_UPDATE_DATE = MERGE_EQUIP_OUTPUT_SQ.LAST_UPDATE_DATE,
195 LAST_UPDATE_SYSTEM_ID = MERGE_EQUIP_OUTPUT_SQ.LAST_UPDATE_SYSTEM_ID,
196 LAST_UPDATED_BY = MERGE_EQUIP_OUTPUT_SQ.LAST_UPDATED_BY,
197 LAST_UPDATE_LOGIN = MERGE_EQUIP_OUTPUT_SQ.LAST_UPDATE_LOGIN,
198 RESOURCE_FK_KEY = MERGE_EQUIP_OUTPUT_SQ.RESOURCE_FK_KEY,
199 RESOURCE_COST = MERGE_EQUIP_OUTPUT_SQ.COST ;
200
201
202 mth_util_pkg.log_msg('Number of rows updated in MTH_EQUIP_OUTPUT_SUMMARY - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
203
204 -- Update the Resource cost and other columns on the status summary table
205
206 MERGE
207 INTO
208 MTH_EQUIP_STATUS_SUMMARY
209 USING
210 (SELECT
211 MTH_EQUIPMENT_DENORM_D_SQ.EQUIPMENT_FK_KEY "EQUIPMENT_FK_KEY",
212 MTH_EQUIP_STATUS_SUMMARY_SQ.SHIFT_WORKDAY_FK_KEY "SHIFT_WORKDAY_FK_KEY",
213 v_log_to_date "LAST_UPDATE_DATE",
214 v_unassigned_val "LAST_UPDATE_SYSTEM_ID",
215 v_unassigned_val "LAST_UPDATED_BY",
216 v_unassigned_val "LAST_UPDATE_LOGIN",
217 MTH_RESOURCE_COST_MV_SQ.RESOURCE_FK_KEY,
218 MTH_RESOURCE_COST_MV_SQ.COST
219 FROM
220 /*(SELECT
221 DISTINCT
222 MTH_RUN_LOG.FROM_DATE,
223 MTH_RUN_LOG.TO_DATE
224 FROM
225 MTH_RUN_LOG
226 WHERE
227 (MTH_RUN_LOG.FACT_TABLE = 'MTH_EQUIPMENT_DENORM_D_MV' )) MTH_RUN_LOG_SQ ,*/
228 (SELECT
229 MTH_EQUIPMENT_DENORM_D.EQUIPMENT_FK_KEY,
230 MTH_EQUIPMENT_DENORM_D.LEVEL9_LEVEL_KEY ,
231 MTH_EQUIPMENT_DENORM_D.EQUIPMENT_EFFECTIVE_DATE,
232 MTH_EQUIPMENT_DENORM_D.EQUIPMENT_EXPIRATION_DATE,
233 MTH_EQUIPMENT_DENORM_D.EQUIPMENT_HIERARCHY_KEY
234 FROM MTH_EQUIPMENT_DENORM_D ) MTH_EQUIPMENT_DENORM_D_SQ,
235 (SELECT
236 MTH_EQUIP_HIERARCHY.HIERARCHY_ID,
237 MTH_EQUIP_HIERARCHY.LEVEL_NUM,
238 MTH_EQUIP_HIERARCHY.LEVEL_FK_KEY,
239 MTH_EQUIP_HIERARCHY.PARENT_FK_KEY,
240 MTH_EQUIP_HIERARCHY.EFFECTIVE_DATE,
241 MTH_EQUIP_HIERARCHY.LAST_UPDATE_DATE
242 FROM MTH_EQUIP_HIERARCHY) MTH_EQUIP_HIERARCHY_SQ ,
243 (SELECT
244 MTH_RESOURCE_COST_MV.RESOURCE_FK_KEY,
245 MTH_RESOURCE_COST_MV.COST
246 FROM MTH_RESOURCE_COST_MV ) MTH_RESOURCE_COST_MV_SQ,
247 (SELECT
248 MTH_SHIFT_GREGORIAN_DENORM_MV.SHIFT_WORKDAY_PK_KEY,
249 MTH_SHIFT_GREGORIAN_DENORM_MV.SHIFT_DATE,
250 MTH_SHIFT_GREGORIAN_DENORM_MV.FROM_DATE
251 FROM MTH_SHIFT_GREGORIAN_DENORM_MV) MTH_SHIFT_GREGORIAN_DENORM_SQ ,
252 (SELECT
253 MTH_EQUIP_STATUS_SUMMARY.EQUIPMENT_FK_KEY,
254 MTH_EQUIP_STATUS_SUMMARY.SHIFT_WORKDAY_FK_KEY
255 FROM MTH_EQUIP_STATUS_SUMMARY
256 GROUP BY EQUIPMENT_FK_KEY,SHIFT_WORKDAY_FK_KEY) MTH_EQUIP_STATUS_SUMMARY_SQ
257 WHERE
258 ( MTH_EQUIP_HIERARCHY_SQ.LAST_UPDATE_DATE > v_log_from_date AND
259 MTH_EQUIP_HIERARCHY_SQ.LAST_UPDATE_DATE <= v_log_to_date AND
260 MTH_EQUIP_HIERARCHY_SQ.HIERARCHY_ID = -2 and
261 MTH_EQUIP_HIERARCHY_SQ.HIERARCHY_ID = MTH_EQUIPMENT_DENORM_D_SQ.EQUIPMENT_HIERARCHY_KEY and
262 MTH_EQUIP_HIERARCHY_SQ.LEVEL_NUM = 10 and
263 MTH_EQUIP_HIERARCHY_SQ.LEVEL_FK_KEY = MTH_EQUIPMENT_DENORM_D_SQ.EQUIPMENT_FK_KEY And
264 MTH_EQUIPMENT_DENORM_D_SQ.LEVEL9_LEVEL_KEY = MTH_RESOURCE_COST_MV_SQ.RESOURCE_FK_KEY (+) And
265 MTH_EQUIPMENT_DENORM_D_SQ.EQUIPMENT_EFFECTIVE_DATE = MTH_EQUIP_HIERARCHY_SQ.EFFECTIVE_DATE AND
266 MTH_EQUIPMENT_DENORM_D_SQ.EQUIPMENT_FK_KEY = MTH_EQUIP_STATUS_SUMMARY_SQ.EQUIPMENT_FK_KEY AND
267 MTH_EQUIP_STATUS_SUMMARY_SQ.SHIFT_WORKDAY_FK_KEY = MTH_SHIFT_GREGORIAN_DENORM_SQ.SHIFT_WORKDAY_PK_KEY AND
268 MTH_SHIFT_GREGORIAN_DENORM_SQ.FROM_DATE between MTH_EQUIPMENT_DENORM_D_SQ.EQUIPMENT_EFFECTIVE_DATE AND
269 nvl( MTH_EQUIPMENT_DENORM_D_SQ.EQUIPMENT_EXPIRATION_DATE,
270 TO_DATE('4000-01-01', 'YYYY-MM-DD')))
271 )
272 MERGE_EQUIP_STATUS_SQ
273 ON
274 ( MTH_EQUIP_STATUS_SUMMARY.EQUIPMENT_FK_KEY = MERGE_EQUIP_STATUS_SQ.EQUIPMENT_FK_KEY AND
275 MTH_EQUIP_STATUS_SUMMARY.SHIFT_WORKDAY_FK_KEY = MERGE_EQUIP_STATUS_SQ.SHIFT_WORKDAY_FK_KEY)
276 WHEN MATCHED THEN
277 UPDATE
278 SET
279 LAST_UPDATE_DATE = MERGE_EQUIP_STATUS_SQ.LAST_UPDATE_DATE,
280 LAST_UPDATE_SYSTEM_ID = MERGE_EQUIP_STATUS_SQ.LAST_UPDATE_SYSTEM_ID,
281 LAST_UPDATED_BY = MERGE_EQUIP_STATUS_SQ.LAST_UPDATED_BY,
282 LAST_UPDATE_LOGIN = MERGE_EQUIP_STATUS_SQ.LAST_UPDATE_LOGIN,
283 RESOURCE_FK_KEY = MERGE_EQUIP_STATUS_SQ.RESOURCE_FK_KEY,
284 RESOURCE_COST = MERGE_EQUIP_STATUS_SQ.COST ;
285
286
287 mth_util_pkg.log_msg('Number of rows updated in MTH_EQUIP_STATUS_SUMMARY - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
288
289
290
291
292 ----Call mth_run_log_post_load
293 mth_util_pkg.mth_run_log_post_load('MTH_EQUIPMENT_DENORM_D_MV',v_unassigned_val);
294
295 COMMIT;
296 mth_util_pkg.log_msg('MTH_POPULATE_EQUIP_HRCHY_MV end', mth_util_pkg.G_DBG_PROC_FUN_END);
297 EXCEPTION
298 WHEN OTHERS THEN
299 mth_util_pkg.log_msg('Exception OTHERS in MTH_EQUIP_HRCHY_MV_ALL_MAP', mth_util_pkg.G_DBG_EXCEPTION);
303 RAISE;
300 mth_util_pkg.log_msg(substr(sqlerrm,1,300), mth_util_pkg.G_DBG_EXCEPTION);
301 mth_util_pkg.log_msg(sqlcode, mth_util_pkg.G_DBG_EXCEPTION);
302 ROLLBACK;
304 END MTH_POPULATE_EQUIP_HRCHY_MV;
305
306 /*******************************************************************************
307 * Procedure :MTH_POPULATE_PROD_SCHD_MV *
308 * Description :This procedure calculates the product schedule *
309 * File Name :MTHMVFB.PLS *
310 * Visibility :Public *
311 * Parameters : *
312 * Modification log : *
313 * Author Date Change *
314 * Phanikanth 29-Mar-2012 Initial Creation *
315 *******************************************************************************/
316
317 PROCEDURE MTH_POPULATE_PROD_SCHD_MV IS
318 v_log_from_date DATE; -- 14152929 (sasuren)
319 v_log_to_date DATE;
320 v_unassigned_val VARCHAR2(30);
321
322 BEGIN
323 mth_util_pkg.log_msg('MTH_POPULATE_PROD_SCHD_MV start', mth_util_pkg.G_DBG_PROC_FUN_START);
324
325 -- Initialize default parameters
326 v_log_to_date := sysdate;
327 v_unassigned_val := MTH_UTIL_PKG.MTH_UA_GET_VAL;
328
329 -- Call mth_run_log_pre_load
330 mth_util_pkg.mth_run_log_pre_load('MTH_PRODUCTION_SCHEDULES_F_MV',v_unassigned_val,NULL,NULL,0,v_log_to_date);
331 mth_util_pkg.GET_RUN_LOG_DATES('MTH_PRODUCTION_SCHEDULES_F_MV',NULL,NULL,NULL,v_log_from_date,v_log_to_date);
332 mth_util_pkg.log_msg('v_log_from_date - ' || to_char(v_log_from_date,'DD-MON-YYYY HH24:MI:SS'), mth_util_pkg.G_DBG_VAR_VAL);
333 mth_util_pkg.log_msg('v_log_to_date - ' || to_char(v_log_to_date,'DD-MON-YYYY HH24:MI:SS'), mth_util_pkg.G_DBG_VAR_VAL);
334
335
336 -- Update the Prod Schedule and other columns on the Equip Prod Schedule table
337
338 MERGE
339 INTO
340 MTH_EQUIP_PROD_SCHEDULE_F MEPSF
341 USING
342 (SELECT
343 MPSF.WORKORDER_PK_KEY "WORKORDER_PK_KEY",
344 v_log_to_date "LAST_UPDATE_DATE",
345 v_unassigned_val "LAST_UPDATE_SYSTEM_ID",
346 v_unassigned_val "LAST_UPDATED_BY",
347 v_unassigned_val "LAST_UPDATE_LOGIN",
348 MPSF.STATUS_CODE "STATUS_CODE"
349 FROM
350 /* (SELECT
351 DISTINCT
352 MTH_RUN_LOG.FROM_DATE,
353 MTH_RUN_LOG.TO_DATE
354 FROM
355 MTH_RUN_LOG
356 WHERE
357 (MTH_RUN_LOG.FACT_TABLE = 'MTH_PRODUCTION_SCHEDULES_F_MV' )) MTH_RUN_LOG_SQ , */
358 MTH_PRODUCTION_SCHEDULES_F MPSF
359 WHERE
360 (MPSF.LAST_UPDATE_DATE > v_log_from_date AND MPSF.LAST_UPDATE_DATE <= v_log_to_date)
361 )
362 MERGE_EQUIP_PROD_SCHEDULE_SQ
363 ON (
364 MEPSF.WORKORDER_FK_KEY = MERGE_EQUIP_PROD_SCHEDULE_SQ.WORKORDER_PK_KEY
365 )
366 WHEN MATCHED THEN
367 UPDATE
368 SET
369 MEPSF.LAST_UPDATE_DATE = MERGE_EQUIP_PROD_SCHEDULE_SQ.LAST_UPDATE_DATE,
370 MEPSF.LAST_UPDATE_SYSTEM_ID = MERGE_EQUIP_PROD_SCHEDULE_SQ.LAST_UPDATE_SYSTEM_ID,
371 MEPSF.LAST_UPDATED_BY = MERGE_EQUIP_PROD_SCHEDULE_SQ.LAST_UPDATED_BY,
372 MEPSF.LAST_UPDATE_LOGIN = MERGE_EQUIP_PROD_SCHEDULE_SQ.LAST_UPDATE_LOGIN,
373 MEPSF.STATUS_CODE = MERGE_EQUIP_PROD_SCHEDULE_SQ.STATUS_CODE;
374
375 mth_util_pkg.log_msg('Number of rows updated in MTH_EQUIP_PROD_SCHEDULE0_F - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
376
377 -- Update the Prod Schedule and other columns on the Resource Transaction table
378
379 MERGE
380 INTO
381 MTH_RESOURCE_TXN_F MRTF
382 USING
383 (SELECT
384 MPSF.WORKORDER_PK_KEY "WORKORDER_PK_KEY",
385 v_log_to_date "LAST_UPDATE_DATE",
386 v_unassigned_val "LAST_UPDATE_SYSTEM_ID",
387 v_unassigned_val "LAST_UPDATED_BY",
388 v_unassigned_val "LAST_UPDATE_LOGIN",
389 MPSF.PLANNED_START_DATE "PLANNED_START_DATE",
390 MPSF.PLANNED_COMPLETION_DATE "PLANNED_COMPLETION_DATE",
391 MPSF.ACTUAL_START_DATE "ACTUAL_START_DATE",
392 MPSF.ACTUAL_COMPLETION_DATE "ACTUAL_COMPLETION_DATE"
393 FROM
394 /*(SELECT
395 DISTINCT
396 MTH_RUN_LOG.FROM_DATE,
397 MTH_RUN_LOG.TO_DATE
398 FROM
399 MTH_RUN_LOG
400 WHERE
401 (MTH_RUN_LOG.FACT_TABLE = 'MTH_PRODUCTION_SCHEDULES_F_MV' )) MTH_RUN_LOG_SQ ,*/
402 MTH_PRODUCTION_SCHEDULES_F MPSF
403 WHERE
404 (MPSF.LAST_UPDATE_DATE > v_log_from_date AND MPSF.LAST_UPDATE_DATE <= v_log_to_date)
405 )
406 MERGE_RESOURCE_TXN_SQ
407 ON (
408 MRTF.WORKORDER_FK_KEY = MERGE_RESOURCE_TXN_SQ.WORKORDER_PK_KEY
409 )
410 WHEN MATCHED THEN
411 UPDATE
412 SET
413 MRTF.LAST_UPDATE_DATE = MERGE_RESOURCE_TXN_SQ.LAST_UPDATE_DATE,
414 MRTF.LAST_UPDATE_SYSTEM_ID = MERGE_RESOURCE_TXN_SQ.LAST_UPDATE_SYSTEM_ID,
415 MRTF.LAST_UPDATED_BY = MERGE_RESOURCE_TXN_SQ.LAST_UPDATED_BY,
416 MRTF.LAST_UPDATE_LOGIN = MERGE_RESOURCE_TXN_SQ.LAST_UPDATE_LOGIN,
417 MRTF.PLANNED_START_DATE = MERGE_RESOURCE_TXN_SQ.PLANNED_START_DATE,
418 MRTF.PLANNED_COMPLETION_DATE = MERGE_RESOURCE_TXN_SQ.PLANNED_COMPLETION_DATE,
419 MRTF.ACTUAL_START_DATE = MERGE_RESOURCE_TXN_SQ.ACTUAL_START_DATE,
420 MRTF.ACTUAL_COMPLETION_DATE = MERGE_RESOURCE_TXN_SQ.ACTUAL_COMPLETION_DATE;
421
422 mth_util_pkg.log_msg('Number of rows updated in MTH_RESOURCE_TXN_F - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
423
424
425 -- Update the Prod Schedule and other columns on the WO Sales Orders table
426
427 MERGE
428 INTO
429 MTH_WO_SALES_ORDERS_F MWSOF
430 USING
431 (SELECT
432 MPSF.WORKORDER_PK_KEY "WORKORDER_PK_KEY",
433 v_log_to_date "LAST_UPDATE_DATE",
434 v_unassigned_val "LAST_UPDATE_SYSTEM_ID",
438 MPSF.PLANNED_COMPLETION_DATE "PLANNED_COMPLETION_DATE",
435 v_unassigned_val "LAST_UPDATE_LOGIN",
436 v_unassigned_val "LAST_UPDATED_BY",
437 MPSF.PLANNED_START_DATE "PLANNED_START_DATE",
439 MPSF.ACTUAL_START_DATE "ACTUAL_START_DATE",
440 MPSF.ACTUAL_COMPLETION_DATE "ACTUAL_COMPLETION_DATE"
441 FROM
442 /*(SELECT
443 DISTINCT
444 MTH_RUN_LOG.FROM_DATE,
445 MTH_RUN_LOG.TO_DATE
446 FROM
447 MTH_RUN_LOG
448 WHERE
449 (MTH_RUN_LOG.FACT_TABLE = 'MTH_PRODUCTION_SCHEDULES_F_MV' )) MTH_RUN_LOG_SQ ,*/
450 MTH_PRODUCTION_SCHEDULES_F MPSF
451 WHERE
452 (MPSF.LAST_UPDATE_DATE > v_log_from_date AND MPSF.LAST_UPDATE_DATE <= v_log_to_date)
453 )
454 MERGE_WO_SALES_ORDERS_SQ
455 ON (
456 MWSOF.WORKORDER_FK_KEY = MERGE_WO_SALES_ORDERS_SQ.WORKORDER_PK_KEY
457 )
458 WHEN MATCHED THEN
459 UPDATE
460 SET
461 MWSOF.LAST_UPDATE_DATE = MERGE_WO_SALES_ORDERS_SQ.LAST_UPDATE_DATE,
462 MWSOF.LAST_UPDATE_SYSTEM_ID = MERGE_WO_SALES_ORDERS_SQ.LAST_UPDATE_SYSTEM_ID,
463 MWSOF.LAST_UPDATE_LOGIN = MERGE_WO_SALES_ORDERS_SQ.LAST_UPDATE_LOGIN,
464 MWSOF.LAST_UPDATED_BY = MERGE_WO_SALES_ORDERS_SQ.LAST_UPDATED_BY,
465 MWSOF.PLANNED_START_DATE = MERGE_WO_SALES_ORDERS_SQ.PLANNED_START_DATE,
466 MWSOF.PLANNED_COMPLETION_DATE = MERGE_WO_SALES_ORDERS_SQ.PLANNED_COMPLETION_DATE,
467 MWSOF.ACTUAL_START_DATE = MERGE_WO_SALES_ORDERS_SQ.ACTUAL_START_DATE,
468 MWSOF.ACTUAL_COMPLETION_DATE = MERGE_WO_SALES_ORDERS_SQ.ACTUAL_COMPLETION_DATE;
469
470 mth_util_pkg.log_msg('Number of rows updated in MTH_WO_SALES_ORDERS_F - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
471
472 -- Update the Prod Schedule and other columns on the Resource Requirement table
473
474 MERGE
475 INTO
476 MTH_RESOURCE_REQUIREMENTS_F MRRF
477 USING
478 (SELECT
479 MPSF.WORKORDER_PK_KEY "WORKORDER_PK_KEY",
480 v_log_to_date "LAST_UPDATE_DATE",
481 v_unassigned_val "LAST_UPDATE_SYSTEM_ID",
482 v_unassigned_val "LAST_UPDATED_BY",
483 v_unassigned_val "LAST_UPDATE_LOGIN",
484 MPSF.PLANNED_START_DATE "PLANNED_START_DATE",
485 MPSF.PLANNED_COMPLETION_DATE "PLANNED_COMPLETION_DATE",
486 MPSF.ACTUAL_START_DATE "ACTUAL_START_DATE",
487 MPSF.ACTUAL_COMPLETION_DATE "ACTUAL_COMPLETION_DATE"
488 FROM
489 /*(SELECT
490 DISTINCT
491 MTH_RUN_LOG.FROM_DATE,
492 MTH_RUN_LOG.TO_DATE
493 FROM
494 MTH_RUN_LOG
495 WHERE
496 (MTH_RUN_LOG.FACT_TABLE = 'MTH_PRODUCTION_SCHEDULES_F_MV' )) MTH_RUN_LOG_SQ ,*/
497 MTH_PRODUCTION_SCHEDULES_F MPSF
498 WHERE
499 (MPSF.LAST_UPDATE_DATE > v_log_from_date AND MPSF.LAST_UPDATE_DATE <= v_log_to_date)
500 )
501 MERGE_RESOURCE_REQUIREMENTS_SQ
502 ON (
503 MRRF.WORKORDER_FK_KEY = MERGE_RESOURCE_REQUIREMENTS_SQ.WORKORDER_PK_KEY
504 )
505 WHEN MATCHED THEN
506 UPDATE
507 SET
508 MRRF.LAST_UPDATE_DATE = MERGE_RESOURCE_REQUIREMENTS_SQ.LAST_UPDATE_DATE,
509 MRRF.LAST_UPDATE_SYSTEM_ID = MERGE_RESOURCE_REQUIREMENTS_SQ.LAST_UPDATE_SYSTEM_ID,
510 MRRF.LAST_UPDATED_BY = MERGE_RESOURCE_REQUIREMENTS_SQ.LAST_UPDATED_BY,
511 MRRF.LAST_UPDATE_LOGIN = MERGE_RESOURCE_REQUIREMENTS_SQ.LAST_UPDATE_LOGIN,
512 MRRF.PLANNED_START_DATE = MERGE_RESOURCE_REQUIREMENTS_SQ.PLANNED_START_DATE,
513 MRRF.PLANNED_COMPLETION_DATE = MERGE_RESOURCE_REQUIREMENTS_SQ.PLANNED_COMPLETION_DATE,
514 MRRF.ACTUAL_START_DATE = MERGE_RESOURCE_REQUIREMENTS_SQ.ACTUAL_START_DATE,
515 MRRF.ACTUAL_COMPLETION_DATE = MERGE_RESOURCE_REQUIREMENTS_SQ.ACTUAL_COMPLETION_DATE;
516
517 mth_util_pkg.log_msg('Number of rows updated in MTH_RESOURCE_REQUIREMENTS_F - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
518
519
520 -- Update the Prod Schedule and other columns on the Prod MTL Consumed table
521
522 MERGE
523 INTO
524 MTH_PROD_MTL_CONSUMED_F MPMCF
525 USING
526 (SELECT
527 MPSF.WORKORDER_PK_KEY "WORKORDER_PK_KEY",
528 v_log_to_date "LAST_UPDATE_DATE",
529 v_unassigned_val "LAST_UPDATE_SYSTEM_ID",
530 v_unassigned_val "LAST_UPDATED_BY",
531 v_unassigned_val "LAST_UPDATE_LOGIN",
532 MPSF.PLANNED_START_DATE "PLANNED_START_DATE",
533 MPSF.PLANNED_COMPLETION_DATE "PLANNED_COMPLETION_DATE",
534 MPSF.ACTUAL_START_DATE "ACTUAL_START_DATE",
535 MPSF.ACTUAL_COMPLETION_DATE "ACTUAL_COMPLETION_DATE"
536 FROM
537 /*(SELECT
538 DISTINCT
539 MTH_RUN_LOG.FROM_DATE,
540 MTH_RUN_LOG.TO_DATE
541 FROM
542 MTH_RUN_LOG
543 WHERE
544 (MTH_RUN_LOG.FACT_TABLE = 'MTH_PRODUCTION_SCHEDULES_F_MV' )) MTH_RUN_LOG_SQ , */
545 MTH_PRODUCTION_SCHEDULES_F MPSF
546 WHERE
547 (MPSF.LAST_UPDATE_DATE > v_log_from_date AND MPSF.LAST_UPDATE_DATE <= v_log_to_date)
548 )
549 MERGE_PROD_MTL_CONSUMED_SQ
550 ON (
551 MPMCF.WORKORDER_FK_KEY = MERGE_PROD_MTL_CONSUMED_SQ.WORKORDER_PK_KEY
552 )
553 WHEN MATCHED THEN
554 UPDATE
555 SET
556 MPMCF.LAST_UPDATE_DATE = MERGE_PROD_MTL_CONSUMED_SQ.LAST_UPDATE_DATE,
557 MPMCF.LAST_UPDATE_SYSTEM_ID = MERGE_PROD_MTL_CONSUMED_SQ.LAST_UPDATE_SYSTEM_ID,
558 MPMCF.LAST_UPDATED_BY = MERGE_PROD_MTL_CONSUMED_SQ.LAST_UPDATED_BY,
559 MPMCF.LAST_UPDATE_LOGIN = MERGE_PROD_MTL_CONSUMED_SQ.LAST_UPDATE_LOGIN,
560 MPMCF.PLANNED_START_DATE = MERGE_PROD_MTL_CONSUMED_SQ.PLANNED_START_DATE,
561 MPMCF.PLANNED_COMPLETION_DATE = MERGE_PROD_MTL_CONSUMED_SQ.PLANNED_COMPLETION_DATE,
562 MPMCF.ACTUAL_START_DATE = MERGE_PROD_MTL_CONSUMED_SQ.ACTUAL_START_DATE,
563 MPMCF.ACTUAL_COMPLETION_DATE = MERGE_PROD_MTL_CONSUMED_SQ.ACTUAL_COMPLETION_DATE;
564
565 mth_util_pkg.log_msg('Number of rows updated in MTH_PROD_MTL_CONSUMED_F - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
566
567
568 -- Update the Prod Schedule and other columns on the Prod MTL Produced table
569
570 MERGE
571 INTO
572 MTH_PROD_MTL_PRODUCED_F MPMPF
573 USING
574 (SELECT
578 v_unassigned_val "LAST_UPDATE_LOGIN",
575 MPSF.WORKORDER_PK_KEY "WORKORDER_PK_KEY",
576 v_log_to_date "LAST_UPDATE_DATE",
577 v_unassigned_val "LAST_UPDATE_SYSTEM_ID",
579 v_unassigned_val "LAST_UPDATED_BY",
580 MPSF.PLANNED_START_DATE "PLANNED_START_DATE",
581 MPSF.PLANNED_COMPLETION_DATE "PLANNED_COMPLETION_DATE",
582 MPSF.ACTUAL_START_DATE "ACTUAL_START_DATE",
583 MPSF.ACTUAL_COMPLETION_DATE "ACTUAL_COMPLETION_DATE",
584 MPSF.STATUS_CODE "STATUS_CODE"
585 FROM
586 /*(SELECT
587 DISTINCT
588 MTH_RUN_LOG.FROM_DATE,
589 MTH_RUN_LOG.TO_DATE
590 FROM
591 MTH_RUN_LOG
592 WHERE
593 (MTH_RUN_LOG.FACT_TABLE = 'MTH_PRODUCTION_SCHEDULES_F_MV' )) MTH_RUN_LOG_SQ ,*/
594 MTH_PRODUCTION_SCHEDULES_F MPSF
595 WHERE
596 (MPSF.LAST_UPDATE_DATE > v_log_from_date AND MPSF.LAST_UPDATE_DATE <= v_log_to_date)
597 )
598 MERGE_PROD_MTL_PRODUCED_SQ
599 ON (
600 MPMPF.WORKORDER_FK_KEY = MERGE_PROD_MTL_PRODUCED_SQ.WORKORDER_PK_KEY
601 )
602 WHEN MATCHED THEN
603 UPDATE
604 SET
605 MPMPF.LAST_UPDATE_DATE = MERGE_PROD_MTL_PRODUCED_SQ.LAST_UPDATE_DATE,
606 MPMPF.LAST_UPDATE_SYSTEM_ID = MERGE_PROD_MTL_PRODUCED_SQ.LAST_UPDATE_SYSTEM_ID,
607 MPMPF.LAST_UPDATE_LOGIN = MERGE_PROD_MTL_PRODUCED_SQ.LAST_UPDATE_LOGIN,
608 MPMPF.LAST_UPDATED_BY = MERGE_PROD_MTL_PRODUCED_SQ.LAST_UPDATED_BY,
609 MPMPF.PLANNED_START_DATE = MERGE_PROD_MTL_PRODUCED_SQ.PLANNED_START_DATE,
610 MPMPF.PLANNED_COMPLETION_DATE = MERGE_PROD_MTL_PRODUCED_SQ.PLANNED_COMPLETION_DATE,
611 MPMPF.ACTUAL_START_DATE = MERGE_PROD_MTL_PRODUCED_SQ.ACTUAL_START_DATE,
612 MPMPF.ACTUAL_COMPLETION_DATE = MERGE_PROD_MTL_PRODUCED_SQ.ACTUAL_COMPLETION_DATE,
613 MPMPF.STATUS_CODE = MERGE_PROD_MTL_PRODUCED_SQ.STATUS_CODE;
614
615 mth_util_pkg.log_msg('Number of rows updated in MTH_PROD_MTL_PRODUCED_F - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
616
617 ----Call mth_run_log_post_load
618 mth_util_pkg.mth_run_log_post_load('MTH_PRODUCTION_SCHEDULES_F_MV',v_unassigned_val);
619
620 COMMIT;
621 mth_util_pkg.log_msg('MTH_POPULATE_PROD_SCHD_MV end', mth_util_pkg.G_DBG_PROC_FUN_END);
622 EXCEPTION
623 WHEN OTHERS THEN
624 mth_util_pkg.log_msg('Exception OTHERS in MTH_PROD_SCHD_MV_ALL_MAP', mth_util_pkg.G_DBG_EXCEPTION);
625 mth_util_pkg.log_msg(substr(sqlerrm,1,300), mth_util_pkg.G_DBG_EXCEPTION);
626 mth_util_pkg.log_msg(sqlcode, mth_util_pkg.G_DBG_EXCEPTION);
627 ROLLBACK;
628 RAISE;
629 END MTH_POPULATE_PROD_SCHD_MV;
630
631
632 /*********************************************************************************
633 * Procedure :MTH_POPULATE_EQP_STD_RATES_MV *
634 * Description :This procedure updates the Equip Standard Rates *
635 * File Name :MTHMVFB.PLS *
636 * Visibility :Public *
637 * Parameters : *
638 * Modification log : *
639 * Author Date Change *
640 * Srividya Naguluri 29-Mar-2012 Initial Creation *
641 *******************************************************************************/
642
643 PROCEDURE MTH_POPULATE_EQP_STD_RATES_MV IS
644 v_log_from_date DATE; -- 14152929 (sasuren)
645 v_log_to_date DATE;
646 v_unassigned_val VARCHAR2(30);
647
648 BEGIN
649 mth_util_pkg.log_msg('MTH_POPULATE_EQP_STD_RATES_MV start', mth_util_pkg.G_DBG_PROC_FUN_START);
650
651 -- Initialize default parameters
652 v_log_to_date := sysdate;
653 v_unassigned_val := MTH_UTIL_PKG.MTH_UA_GET_VAL;
654
655 -- Call mth_run_log_pre_load
656 mth_util_pkg.mth_run_log_pre_load('MTH_EQUIP_STD_RATES_MV',v_unassigned_val,NULL,NULL,0,v_log_to_date);
657 mth_util_pkg.GET_RUN_LOG_DATES('MTH_EQUIP_STD_RATES_MV',NULL,NULL,NULL,v_log_from_date,v_log_to_date);
658 mth_util_pkg.log_msg('v_log_from_date - ' || to_char(v_log_from_date,'DD-MON-YYYY HH24:MI:SS'), mth_util_pkg.G_DBG_VAR_VAL);
659 mth_util_pkg.log_msg('v_log_to_date - ' || to_char(v_log_to_date,'DD-MON-YYYY HH24:MI:SS'), mth_util_pkg.G_DBG_VAR_VAL);
660
661 -- Update the standard rates and other columns on the euip production schedule table
662
663 MERGE
664 INTO
665 MTH_EQUIP_PROD_SCHEDULE_F MEPRSF
666 USING
667 (SELECT
668 MESRF.EQUIPMENT_FK_KEY "EQUIPMENT_FK_KEY",
669 MESRF.SHIFT_WORKDAY_FK_KEY "SHIFT_WORKDAY_FK_KEY",
670 MESRF.ITEM_FK_KEY "ITEM_FK_KEY",
671 v_log_to_date "LAST_UPDATE_DATE",
672 MESRF.STANDARD_RATE_1 "STANDARD_RATE_1"
673 FROM
674 /*(SELECT
675 DISTINCT
676 MTH_RUN_LOG.FROM_DATE,
677 MTH_RUN_LOG.TO_DATE
678 FROM
679 MTH_RUN_LOG
680 WHERE
681 (MTH_RUN_LOG.FACT_TABLE= 'MTH_EQUIP_STD_RATES_MV')) MTH_RUN_LOG_SQ, */
682 MTH_EQUIP_STANDARD_RATES_F MESRF
683 WHERE
684 (MESRF.LAST_UPDATE_DATE > v_log_from_date AND MESRF.LAST_UPDATE_DATE <= v_log_to_date)
685 )
686 MERGE_EQUIP_PROD_SQ
687 ON (
688 MEPRSF.EQUIPMENT_FK_KEY = MERGE_EQUIP_PROD_SQ.EQUIPMENT_FK_KEY AND
689 MEPRSF.SHIFT_WORKDAY_FK_KEY=MERGE_EQUIP_PROD_SQ.SHIFT_WORKDAY_FK_KEY AND
690 MEPRSF.ITEM_FK_KEY = MERGE_EQUIP_PROD_SQ.ITEM_FK_KEY
691 )
692 WHEN MATCHED THEN
693 UPDATE
694 SET
695 MEPRSF.LAST_UPDATE_DATE = MERGE_EQUIP_PROD_SQ.LAST_UPDATE_DATE,
696 MEPRSF.STANDARD_RATE_1= MERGE_EQUIP_PROD_SQ.STANDARD_RATE_1;
697
698 mth_util_pkg.log_msg('Number of rows updated in MTH_EQUIP_PROD_SCHEDULE_F - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
699
700 -- Update the standard rates and other columns on the output summary table
701
702 MERGE
703 INTO
707 MESRF.EQUIPMENT_FK_KEY "EQUIPMENT_FK_KEY",
704 MTH_EQUIP_OUTPUT_SUMMARY MEOS
705 USING
706 (SELECT
708 MESRF.ITEM_FK_KEY "ITEM_FK_KEY",
709 MESRF.SHIFT_WORKDAY_FK_KEY "SHIFT_WORKDAY_FK_KEY",
710 v_log_to_date "LAST_UPDATE_DATE",
711 MESRF.STANDARD_RATE_1 "STANDARD_RATE_1"
712 FROM
713 /*(SELECT
714 DISTINCT
715 MTH_RUN_LOG.FROM_DATE,
716 MTH_RUN_LOG.TO_DATE
717 FROM
718 MTH_RUN_LOG
719 WHERE
720 (MTH_RUN_LOG.FACT_TABLE= 'MTH_EQUIP_STD_RATES_MV' )) MTH_RUN_LOG_SQ, */
721 MTH_EQUIP_STANDARD_RATES_F MESRF
722 WHERE
723 (MESRF.LAST_UPDATE_DATE > v_log_from_date AND MESRF.LAST_UPDATE_DATE <= v_log_to_date)
724 )
725 MERGE_EQUIP_OUTPUT_SQ
726 ON (
727 MEOS.EQUIPMENT_FK_KEY= MERGE_EQUIP_OUTPUT_SQ.EQUIPMENT_FK_KEY AND
728 MEOS.ITEM_FK_KEY= MERGE_EQUIP_OUTPUT_SQ.ITEM_FK_KEY AND
729 MEOS.SHIFT_WORKDAY_FK_KEY = MERGE_EQUIP_OUTPUT_SQ.SHIFT_WORKDAY_FK_KEY
730 )
731 WHEN MATCHED THEN
732 UPDATE
733 SET
734 MEOS.LAST_UPDATE_DATE = MERGE_EQUIP_OUTPUT_SQ.LAST_UPDATE_DATE,
735 MEOS.STANDARD_RATE_1= MERGE_EQUIP_OUTPUT_SQ.STANDARD_RATE_1;
736
737 mth_util_pkg.log_msg('Number of rows updated in MTH_EQUIP_OUTPUT_SUMMARY - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
738
739 ----Call mth_run_log_post_load
740 mth_util_pkg.mth_run_log_post_load('MTH_EQUIP_STD_RATES_MV',v_unassigned_val);
741
742 COMMIT;
743 mth_util_pkg.log_msg('MTH_POPULATE_EQP_STD_RATES_MV end', mth_util_pkg.G_DBG_PROC_FUN_END);
744 EXCEPTION
745 WHEN OTHERS THEN
746 mth_util_pkg.log_msg('Exception OTHERS in MTH_EQP_STD_RATES_MV_ALL_MAP', mth_util_pkg.G_DBG_EXCEPTION);
747 mth_util_pkg.log_msg(substr(sqlerrm,1,300), mth_util_pkg.G_DBG_EXCEPTION);
748 mth_util_pkg.log_msg(sqlcode, mth_util_pkg.G_DBG_EXCEPTION);
749 ROLLBACK;
750 RAISE;
751 END MTH_POPULATE_EQP_STD_RATES_MV;
752
753 /***************************************************************************************
754 * Procedure :MTH_POPULATE_ITEM_COST_MV *
755 * Description :This procedure calculates the Item cost *
756 * File Name :MTHMVFB.PLS *
757 * Visibility :Public *
758 * Parameters : *
759 * Modification log : *
760 * Author Date Change *
761 * Sailaja Yarram, 29-Mar-2012 Initial Creation *
762 * Chaitanya Rani *
763 ****************************************************************************************/
764
765 PROCEDURE MTH_POPULATE_ITEM_COST_MV IS
766 v_log_from_date DATE; -- 14152929 (sasuren)
767 v_log_to_date DATE;
768 v_unassigned_val VARCHAR2(30);
769
770 BEGIN
771 mth_util_pkg.log_msg('MTH_POPULATE_ITEM_COST_MV start', mth_util_pkg.G_DBG_PROC_FUN_START);
772
773 -- Initialize default parameters
774 v_log_to_date := sysdate;
775 v_unassigned_val := MTH_UTIL_PKG.MTH_UA_GET_VAL;
776
777 -- Call mth_run_log_pre_load
778 mth_util_pkg.mth_run_log_pre_load('MTH_ITEM_COST_MV',v_unassigned_val,NULL,NULL,0,v_log_to_date);
779 mth_util_pkg.GET_RUN_LOG_DATES('MTH_ITEM_COST_MV',NULL,NULL,NULL,v_log_from_date,v_log_to_date);
780 mth_util_pkg.log_msg('v_log_from_date - ' || to_char(v_log_from_date,'DD-MON-YYYY HH24:MI:SS'), mth_util_pkg.G_DBG_VAR_VAL);
781 mth_util_pkg.log_msg('v_log_to_date - ' || to_char(v_log_to_date,'DD-MON-YYYY HH24:MI:SS'), mth_util_pkg.G_DBG_VAR_VAL);
782
783 -- Update the Item cost and other columns on the Production Schedules table
784
785 MERGE
786 INTO
787 MTH_EQUIP_PROD_SCHEDULE_F "PROD_SCHED_F"
788 USING
789 (SELECT
790 ITEM_COST_SQ.ITEM_FK_KEY "ITEM_FK_KEY",
791 v_log_to_date "LAST_UPDATE_DATE",
792 ITEM_COST_SQ.COST "COST"
793 FROM
794 /*(SELECT
795 DISTINCT
796 MTH_RUN_LOG.FROM_DATE,
797 MTH_RUN_LOG.TO_DATE
798 FROM
799 MTH_RUN_LOG
800 WHERE
801 ( MTH_RUN_LOG.FACT_TABLE = 'MTH_ITEM_COST_MV' )) MTH_RUN_LOG_SQ, */
802 (SELECT
803 SUM(MTH_ITEM_COST.COST) "COST",
804 MAX(MTH_ITEM_COST.LAST_UPDATE_DATE)"LAST_UPDATE_DATE",
805 MTH_ITEM_COST.ITEM_FK_KEY "ITEM_FK_KEY"
806 FROM
807 MTH_ITEM_COST
808 WHERE
809 ( MTH_ITEM_COST.ISCURRENT= 1)
810 GROUP BY
811 MTH_ITEM_COST.ITEM_FK_KEY)"ITEM_COST_SQ"
812 WHERE
813 (ITEM_COST_SQ.LAST_UPDATE_DATE > v_log_from_date and ITEM_COST_SQ.LAST_UPDATE_DATE <= v_log_to_date)
814 )
815 MERGE_EQUIP_PROD_SCHED_SQ
816 ON (
817 PROD_SCHED_F.ITEM_FK_KEY = MERGE_EQUIP_PROD_SCHED_SQ.ITEM_FK_KEY
818 )
819 WHEN MATCHED THEN
820 UPDATE
821 SET
822 PROD_SCHED_F.LAST_UPDATE_DATE = MERGE_EQUIP_PROD_SCHED_SQ.LAST_UPDATE_DATE,
823 PROD_SCHED_F.ITEM_COST = MERGE_EQUIP_PROD_SCHED_SQ.COST;
824
825 mth_util_pkg.log_msg('Number of rows updated in MTH_EQUIP_PROD_SCHEDULE_F - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
826
827 -- Update the item cost and other columns on the output summary table
828
829 MERGE
830 INTO
831 MTH_EQUIP_OUTPUT_SUMMARY "OUTPUT_SUMMARY"
832 USING
833 (SELECT
834 ITEM_COST_SQ.ITEM_FK_KEY "ITEM_FK_KEY",
835 v_log_to_date "LAST_UPDATE_DATE",
836 ITEM_COST_SQ.COST "COST"
837 FROM
838 /*(SELECT
839 DISTINCT
840 MTH_RUN_LOG.FROM_DATE,
841 MTH_RUN_LOG.TO_DATE
842 FROM
843 MTH_RUN_LOG
844 WHERE
845 ( MTH_RUN_LOG.FACT_TABLE = 'MTH_ITEM_COST_MV')) MTH_RUN_LOG_SQ, */
849 MTH_ITEM_COST.ITEM_FK_KEY "ITEM_FK_KEY"
846 (SELECT
847 SUM(MTH_ITEM_COST.COST) "COST",
848 MAX(MTH_ITEM_COST.LAST_UPDATE_DATE) "LAST_UPDATE_DATE",
850 FROM
851 MTH_ITEM_COST
852 GROUP BY
853 MTH_ITEM_COST.ITEM_FK_KEY) "ITEM_COST_SQ"
854 WHERE
855 (ITEM_COST_SQ.LAST_UPDATE_DATE > v_log_from_date and ITEM_COST_SQ.LAST_UPDATE_DATE <= v_log_to_date)
856 )
857 MERGE_EQUIP_OUTPUT_SUM_SQ
858 ON (
859 OUTPUT_SUMMARY.ITEM_FK_KEY = MERGE_EQUIP_OUTPUT_SUM_SQ.ITEM_FK_KEY
860 )
861 WHEN MATCHED THEN
862 UPDATE
863 SET
864 OUTPUT_SUMMARY.LAST_UPDATE_DATE = MERGE_EQUIP_OUTPUT_SUM_SQ.LAST_UPDATE_DATE,
865 OUTPUT_SUMMARY.ITEM_COST = MERGE_EQUIP_OUTPUT_SUM_SQ.COST ;
866
867 mth_util_pkg.log_msg('Number of rows updated in MTH_EQUIP_OUTPUT_SUMMARY - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
868
869 -- Update the item cost and other columns on the Production Consumed table
870
871 MERGE
872 INTO
873 MTH_PROD_MTL_CONSUMED_F "PROD_CONSUMED_F"
874 USING
875 (SELECT
876 ITEM_COST_SQ.ITEM_FK_KEY "ITEM_FK_KEY",
877 v_log_to_date "LAST_UPDATE_DATE",
878 ITEM_COST_SQ.COST "COST"
879 FROM
880 /*(SELECT
881 DISTINCT
882 MTH_RUN_LOG.FROM_DATE,
883 MTH_RUN_LOG.TO_DATE
884 FROM
885 MTH_RUN_LOG
886 WHERE
887 ( MTH_RUN_LOG.FACT_TABLE = 'MTH_ITEM_COST_MV' ))MTH_RUN_LOG_SQ , */
888 (SELECT
889 SUM(MTH_ITEM_COST.COST) "COST",
890 MAX(MTH_ITEM_COST.LAST_UPDATE_DATE)"LAST_UPDATE_DATE",
891 MTH_ITEM_COST.ITEM_FK_KEY "ITEM_FK_KEY"
892 FROM
893 MTH_ITEM_COST
894 WHERE
895 ( MTH_ITEM_COST.ISCURRENT = 1 )
896 GROUP BY
897 MTH_ITEM_COST.ITEM_FK_KEY)"ITEM_COST_SQ"
898 WHERE
899 (ITEM_COST_SQ.LAST_UPDATE_DATE > v_log_from_date and ITEM_COST_SQ.LAST_UPDATE_DATE <= v_log_to_date)
900 )
901 MERGE_PR0D_CONSUMED_SQ
902 ON (
903 PROD_CONSUMED_F.MATERIAL_FK_KEY = MERGE_PR0D_CONSUMED_SQ.ITEM_FK_KEY
904 )
905 WHEN MATCHED THEN
906 UPDATE
907 SET
908 PROD_CONSUMED_F.LAST_UPDATE_DATE = MERGE_PR0D_CONSUMED_SQ.LAST_UPDATE_DATE,
909 PROD_CONSUMED_F.ITEM_COST = MERGE_PR0D_CONSUMED_SQ.COST;
910
911 mth_util_pkg.log_msg('Number of rows updated in MTH_PROD_MTL_CONSUMED_F - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
912
913 ----Call mth_run_log_post_load
914 mth_util_pkg.mth_run_log_post_load('MTH_ITEM_COST_MV',v_unassigned_val);
915 COMMIT;
916 mth_util_pkg.log_msg('MTH_POPULATE_ITEM_COST_MV end', mth_util_pkg.G_DBG_PROC_FUN_END);
917 EXCEPTION
918 WHEN OTHERS THEN
919 mth_util_pkg.log_msg('Exception OTHERS in MTH_ITEM_COST_MV_ALL_MAP', mth_util_pkg.G_DBG_EXCEPTION);
920 mth_util_pkg.log_msg(substr(sqlerrm,1,300), mth_util_pkg.G_DBG_EXCEPTION);
921 mth_util_pkg.log_msg(sqlcode, mth_util_pkg.G_DBG_EXCEPTION);
922 ROLLBACK;
923 RAISE;
924 END MTH_POPULATE_ITEM_COST_MV;
925
926 /*******************************************************************************
927 * Procedure :MTH_POPULATE_RESOURCE_COST_MV *
928 * Description :This procedure calculates the resource cost *
929 * File Name :MTHMVFB.PLS *
930 * Visibility :Public *
931 * Parameters : *
932 * Modification log : *
933 * Author Date Change *
934 * Sanjeev Ramani 02-Mar-2012 Initial Creation *
935 *******************************************************************************/
936
937 PROCEDURE MTH_POPULATE_RESOURCE_COST_MV IS
938 v_log_from_date DATE; -- 14152929 (sasuren)
939 v_log_to_date DATE;
940 v_unassigned_val VARCHAR2(30);
941
942 BEGIN
943 mth_util_pkg.log_msg('MTH_POPULATE_RESOURCE_COST_MV start', mth_util_pkg.G_DBG_PROC_FUN_START);
944
945 -- Initialize default parameters
946 v_log_to_date := sysdate;
947 v_unassigned_val := MTH_UTIL_PKG.MTH_UA_GET_VAL;
948
949 -- Call mth_run_log_pre_load
950 --mth_util_pkg.mth_run_log_pre_load('MTH_RESOURCE_COST_MV',v_unassigned_val,'INITIAL',NULL,0,v_log_date);
951 mth_util_pkg.mth_run_log_pre_load('MTH_RESOURCE_COST_MV',v_unassigned_val,NULL,NULL,0,v_log_to_date); -- changed mode (sasuren)
952 mth_util_pkg.GET_RUN_LOG_DATES('MTH_RESOURCE_COST_MV',NULL,NULL,NULL,v_log_from_date,v_log_to_date);
953 mth_util_pkg.log_msg('v_log_from_date - ' || to_char(v_log_from_date,'DD-MON-YYYY HH24:MI:SS'), mth_util_pkg.G_DBG_VAR_VAL);
954 mth_util_pkg.log_msg('v_log_to_date - ' || to_char(v_log_to_date,'DD-MON-YYYY HH24:MI:SS'), mth_util_pkg.G_DBG_VAR_VAL);
955
956 -- Update the Resource cost and other columns on the equipment shifts table
957
958 MERGE
959 INTO
960 MTH_EQUIPMENT_SHIFTS_D
961 USING
962 (SELECT
963 v_log_to_date "LAST_UPDATE_DATE",
964 v_unassigned_val "LAST_UPDATE_SYSTEM_ID",
965 v_unassigned_val "LAST_UPDATED_BY",
966 v_unassigned_val "LAST_UPDATE_LOGIN",
967 RESOURCE_COST_SQ.RESOURCE_FK_KEY,
968 RESOURCE_COST_SQ.RESOURCE_COST
969 FROM
970 /*(SELECT
971 DISTINCT
972 MTH_RUN_LOG.FROM_DATE,
973 MTH_RUN_LOG.TO_DATE
974 FROM
975 MTH_RUN_LOG
976 WHERE
977 (MTH_RUN_LOG.FACT_TABLE = 'MTH_RESOURCE_COST_MV' )) MTH_RUN_LOG_SQ , */
978 (SELECT
979 SUM( CASE MTH_RESOURCE_COST.ISCURRENT WHEN 1 THEN MTH_RESOURCE_COST.COST ELSE NULL END) "RESOURCE_COST",
980 MAX(MTH_RESOURCE_COST.LAST_UPDATE_DATE) "LAST_UPDATE_DATE",
981 MTH_RESOURCE_COST.RESOURCE_FK_KEY "RESOURCE_FK_KEY"
982 FROM
983 MTH_RESOURCE_COST
984 GROUP BY
985 MTH_RESOURCE_COST.RESOURCE_FK_KEY) "RESOURCE_COST_SQ"
989 MERGE_EQUIP_SHIFTS_SQ
986 WHERE
987 ( RESOURCE_COST_SQ.LAST_UPDATE_DATE > v_log_from_date AND RESOURCE_COST_SQ.LAST_UPDATE_DATE <= v_log_to_date)
988 )
990 ON (
991 MTH_EQUIPMENT_SHIFTS_D.RESOURCE_FK_KEY = MERGE_EQUIP_SHIFTS_SQ.RESOURCE_FK_KEY
992 )
993 WHEN MATCHED THEN
994 UPDATE
995 SET
996 LAST_UPDATE_DATE = MERGE_EQUIP_SHIFTS_SQ.LAST_UPDATE_DATE,
997 LAST_UPDATE_SYSTEM_ID = MERGE_EQUIP_SHIFTS_SQ.LAST_UPDATE_SYSTEM_ID,
998 LAST_UPDATED_BY = MERGE_EQUIP_SHIFTS_SQ.LAST_UPDATED_BY,
999 LAST_UPDATE_LOGIN = MERGE_EQUIP_SHIFTS_SQ.LAST_UPDATE_LOGIN,
1000 RESOURCE_COST = MERGE_EQUIP_SHIFTS_SQ.RESOURCE_COST ;
1001
1002 mth_util_pkg.log_msg('Number of rows updated in MTH_EQUIPMENT_SHIFTS_D - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
1003
1004 -- Update the Resource cost and other columns on the resource requirements table
1005
1006 MERGE
1007 INTO
1008 MTH_RESOURCE_REQUIREMENTS_F
1009 USING
1010 (SELECT
1011 v_log_to_date "LAST_UPDATE_DATE",
1012 v_unassigned_val "LAST_UPDATE_SYSTEM_ID",
1013 v_unassigned_val "LAST_UPDATED_BY",
1014 v_unassigned_val "LAST_UPDATE_LOGIN",
1015 RESOURCE_COST_SQ.RESOURCE_FK_KEY,
1016 RESOURCE_COST_SQ.RESOURCE_COST
1017 FROM
1018 /* (SELECT
1019 DISTINCT
1020 MTH_RUN_LOG.FROM_DATE,
1021 MTH_RUN_LOG.TO_DATE
1022 FROM
1023 MTH_RUN_LOG
1024 WHERE
1025 (MTH_RUN_LOG.FACT_TABLE = 'MTH_RESOURCE_COST_MV' )) MTH_RUN_LOG_SQ , */
1026 (SELECT
1027 SUM( CASE MTH_RESOURCE_COST.ISCURRENT WHEN 1 THEN MTH_RESOURCE_COST.COST ELSE NULL END) "RESOURCE_COST",
1028 MAX(MTH_RESOURCE_COST.LAST_UPDATE_DATE) "LAST_UPDATE_DATE",
1029 MTH_RESOURCE_COST.RESOURCE_FK_KEY "RESOURCE_FK_KEY"
1030 FROM
1031 MTH_RESOURCE_COST
1032 GROUP BY
1033 MTH_RESOURCE_COST.RESOURCE_FK_KEY) "RESOURCE_COST_SQ"
1034 WHERE
1035 ( RESOURCE_COST_SQ.LAST_UPDATE_DATE > v_log_from_date AND RESOURCE_COST_SQ.LAST_UPDATE_DATE <= v_log_to_date)
1036 )
1037 MERGE_RESOURCE_REQ_SQ
1038 ON (
1039 MTH_RESOURCE_REQUIREMENTS_F.RESOURCE_FK_KEY = MERGE_RESOURCE_REQ_SQ.RESOURCE_FK_KEY
1040 )
1041 WHEN MATCHED THEN
1042 UPDATE
1043 SET
1044 LAST_UPDATE_DATE = MERGE_RESOURCE_REQ_SQ.LAST_UPDATE_DATE,
1045 LAST_UPDATE_SYSTEM_ID = MERGE_RESOURCE_REQ_SQ.LAST_UPDATE_SYSTEM_ID,
1046 LAST_UPDATED_BY = MERGE_RESOURCE_REQ_SQ.LAST_UPDATED_BY,
1047 LAST_UPDATE_LOGIN = MERGE_RESOURCE_REQ_SQ.LAST_UPDATE_LOGIN,
1048 RESOURCE_COST = MERGE_RESOURCE_REQ_SQ.RESOURCE_COST ;
1049
1050 mth_util_pkg.log_msg('Number of rows updated in MTH_RESOURCE_REQUIREMENTS_F - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
1051
1052 -- Update the Resource cost and other columns on the status summary table
1053
1054 MERGE
1055 INTO
1056 MTH_EQUIP_STATUS_SUMMARY
1057 USING
1058 (SELECT
1059 v_log_to_date "LAST_UPDATE_DATE",
1060 v_unassigned_val "LAST_UPDATE_SYSTEM_ID",
1061 v_unassigned_val "LAST_UPDATED_BY",
1062 v_unassigned_val "LAST_UPDATE_LOGIN",
1063 RESOURCE_COST_SQ.RESOURCE_FK_KEY,
1064 RESOURCE_COST_SQ.RESOURCE_COST
1065 FROM
1066 /*(SELECT
1067 DISTINCT
1068 MTH_RUN_LOG.FROM_DATE,
1069 MTH_RUN_LOG.TO_DATE
1070 FROM
1071 MTH_RUN_LOG
1072 WHERE
1073 (MTH_RUN_LOG.FACT_TABLE = 'MTH_RESOURCE_COST_MV' )) MTH_RUN_LOG_SQ , */
1074 (SELECT
1075 SUM( CASE MTH_RESOURCE_COST.ISCURRENT WHEN 1 THEN MTH_RESOURCE_COST.COST ELSE NULL END) "RESOURCE_COST",
1076 MAX(MTH_RESOURCE_COST.LAST_UPDATE_DATE) "LAST_UPDATE_DATE",
1077 MTH_RESOURCE_COST.RESOURCE_FK_KEY "RESOURCE_FK_KEY"
1078 FROM
1079 MTH_RESOURCE_COST
1080 GROUP BY
1081 MTH_RESOURCE_COST.RESOURCE_FK_KEY) "RESOURCE_COST_SQ"
1082 WHERE
1083 ( RESOURCE_COST_SQ.LAST_UPDATE_DATE > v_log_from_date AND RESOURCE_COST_SQ.LAST_UPDATE_DATE <= v_log_to_date)
1084 )
1085 MERGE_EQUIP_STATUS_SUM_SQ
1086 ON (
1087 MTH_EQUIP_STATUS_SUMMARY.RESOURCE_FK_KEY = MERGE_EQUIP_STATUS_SUM_SQ.RESOURCE_FK_KEY
1088 )
1089 WHEN MATCHED THEN
1090 UPDATE
1091 SET
1092 LAST_UPDATE_DATE = MERGE_EQUIP_STATUS_SUM_SQ.LAST_UPDATE_DATE,
1093 LAST_UPDATE_SYSTEM_ID = MERGE_EQUIP_STATUS_SUM_SQ.LAST_UPDATE_SYSTEM_ID,
1094 LAST_UPDATED_BY = MERGE_EQUIP_STATUS_SUM_SQ.LAST_UPDATED_BY,
1095 LAST_UPDATE_LOGIN = MERGE_EQUIP_STATUS_SUM_SQ.LAST_UPDATE_LOGIN,
1096 RESOURCE_COST = MERGE_EQUIP_STATUS_SUM_SQ.RESOURCE_COST ;
1097
1098 mth_util_pkg.log_msg('Number of rows updated in MTH_EQUIP_STATUS_SUMMARY - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
1099
1100 -- Update the Resource cost and other columns on the output summary table
1101
1102 MERGE
1103 INTO
1104 MTH_EQUIP_OUTPUT_SUMMARY
1105 USING
1106 (SELECT
1107 v_log_to_date "LAST_UPDATE_DATE",
1108 v_unassigned_val "LAST_UPDATE_SYSTEM_ID",
1109 v_unassigned_val "LAST_UPDATED_BY",
1110 v_unassigned_val "LAST_UPDATE_LOGIN",
1111 RESOURCE_COST_SQ.RESOURCE_FK_KEY,
1112 RESOURCE_COST_SQ.RESOURCE_COST
1113 FROM
1114 /*(SELECT
1115 DISTINCT
1116 MTH_RUN_LOG.FROM_DATE,
1117 MTH_RUN_LOG.TO_DATE
1118 FROM
1119 MTH_RUN_LOG
1120 WHERE
1121 (MTH_RUN_LOG.FACT_TABLE = 'MTH_RESOURCE_COST_MV' )) MTH_RUN_LOG_SQ ,*/
1122 (SELECT
1123 SUM( CASE MTH_RESOURCE_COST.ISCURRENT WHEN 1 THEN MTH_RESOURCE_COST.COST ELSE NULL END) "RESOURCE_COST",
1124 MAX(MTH_RESOURCE_COST.LAST_UPDATE_DATE) "LAST_UPDATE_DATE",
1125 MTH_RESOURCE_COST.RESOURCE_FK_KEY "RESOURCE_FK_KEY"
1126 FROM
1127 MTH_RESOURCE_COST
1128 GROUP BY
1129 MTH_RESOURCE_COST.RESOURCE_FK_KEY) "RESOURCE_COST_SQ"
1130 WHERE
1131 ( RESOURCE_COST_SQ.LAST_UPDATE_DATE > v_log_from_date AND RESOURCE_COST_SQ.LAST_UPDATE_DATE <= v_log_to_date)
1132 )
1133 MERGE_EQUIP_OUTPUT_SUM_SQ
1134 ON (
1135 MTH_EQUIP_OUTPUT_SUMMARY.RESOURCE_FK_KEY = MERGE_EQUIP_OUTPUT_SUM_SQ.RESOURCE_FK_KEY
1136 )
1137 WHEN MATCHED THEN
1138 UPDATE
1139 SET
1143 LAST_UPDATE_LOGIN = MERGE_EQUIP_OUTPUT_SUM_SQ.LAST_UPDATE_LOGIN,
1140 LAST_UPDATE_DATE = MERGE_EQUIP_OUTPUT_SUM_SQ.LAST_UPDATE_DATE,
1141 LAST_UPDATE_SYSTEM_ID = MERGE_EQUIP_OUTPUT_SUM_SQ.LAST_UPDATE_SYSTEM_ID,
1142 LAST_UPDATED_BY = MERGE_EQUIP_OUTPUT_SUM_SQ.LAST_UPDATED_BY,
1144 RESOURCE_COST = MERGE_EQUIP_OUTPUT_SUM_SQ.RESOURCE_COST ;
1145
1146 mth_util_pkg.log_msg('Number of rows updated in MTH_EQUIP_OUTPUT_SUMMARY - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
1147
1148 -- Update the Resource cost and other columns on the resource transactions table
1149
1150 MERGE
1151 INTO
1152 MTH_RESOURCE_TXN_F
1153 USING
1154 (SELECT
1155 v_log_to_date "LAST_UPDATE_DATE",
1156 v_unassigned_val "LAST_UPDATE_SYSTEM_ID",
1157 v_unassigned_val "LAST_UPDATED_BY",
1158 v_unassigned_val "LAST_UPDATE_LOGIN",
1159 RESOURCE_COST_SQ.RESOURCE_FK_KEY,
1160 RESOURCE_COST_SQ.RESOURCE_COST
1161 FROM
1162 /*(SELECT
1163 DISTINCT
1164 MTH_RUN_LOG.FROM_DATE,
1165 MTH_RUN_LOG.TO_DATE
1166 FROM
1167 MTH_RUN_LOG
1168 WHERE
1169 (MTH_RUN_LOG.FACT_TABLE = 'MTH_RESOURCE_COST_MV' )) MTH_RUN_LOG_SQ ,*/
1170 (SELECT
1171 SUM( CASE MTH_RESOURCE_COST.ISCURRENT WHEN 1 THEN MTH_RESOURCE_COST.COST ELSE NULL END) "RESOURCE_COST",
1172 MAX(MTH_RESOURCE_COST.LAST_UPDATE_DATE) "LAST_UPDATE_DATE",
1173 MTH_RESOURCE_COST.RESOURCE_FK_KEY "RESOURCE_FK_KEY"
1174 FROM
1175 MTH_RESOURCE_COST
1176 GROUP BY
1177 MTH_RESOURCE_COST.RESOURCE_FK_KEY) "RESOURCE_COST_SQ"
1178 WHERE
1179 ( RESOURCE_COST_SQ.LAST_UPDATE_DATE > v_log_from_date AND RESOURCE_COST_SQ.LAST_UPDATE_DATE <= v_log_to_date)
1180 )
1181 MERGE_RESOURCE_TXN_SQ
1182 ON (
1183 MTH_RESOURCE_TXN_F.RESOURCE_FK_KEY = MERGE_RESOURCE_TXN_SQ.RESOURCE_FK_KEY
1184 )
1185 WHEN MATCHED THEN
1186 UPDATE
1187 SET
1188 LAST_UPDATE_DATE = MERGE_RESOURCE_TXN_SQ.LAST_UPDATE_DATE,
1189 LAST_UPDATE_SYSTEM_ID = MERGE_RESOURCE_TXN_SQ.LAST_UPDATE_SYSTEM_ID,
1190 LAST_UPDATED_BY = MERGE_RESOURCE_TXN_SQ.LAST_UPDATED_BY,
1191 LAST_UPDATE_LOGIN = MERGE_RESOURCE_TXN_SQ.LAST_UPDATE_LOGIN,
1192 RESOURCE_COST = MERGE_RESOURCE_TXN_SQ.RESOURCE_COST ;
1193
1194 mth_util_pkg.log_msg('Number of rows updated in MTH_RESOURCE_TXN_F - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
1195
1196 ----Call mth_run_log_post_load
1197 mth_util_pkg.mth_run_log_post_load('MTH_RESOURCE_COST_MV',v_unassigned_val);
1198
1199 COMMIT;
1200 mth_util_pkg.log_msg('MTH_POPULATE_RESOURCE_COST_MV end', mth_util_pkg.G_DBG_PROC_FUN_END);
1201 EXCEPTION
1202 WHEN OTHERS THEN
1203 mth_util_pkg.log_msg('Exception OTHERS in MTH_RESOURCE_COST_MV_ALL_MAP', mth_util_pkg.G_DBG_EXCEPTION);
1204 mth_util_pkg.log_msg(substr(sqlerrm,1,300), mth_util_pkg.G_DBG_EXCEPTION);
1205 mth_util_pkg.log_msg(sqlcode, mth_util_pkg.G_DBG_EXCEPTION);
1206 ROLLBACK;
1207 RAISE;
1208 END MTH_POPULATE_RESOURCE_COST_MV;
1209
1210 END MTH_MV_TO_FACT_PKG;