[Home] [Help]
2: /*$Header: mthutilb.pls 120.36.12020000.5 2012/11/29 12:53:44 sasuren ship $ */
3:
4:
5: /* *****************************************************************************
6: * Procedure :MTH_RUN_LOG_PRE_LOAD *
7: * Description :This procedure is used for the population of the *
8: * mth_run_log table for the initial and incremental load. The procedure is *
9: * called at the begenning of the mapping execution sequence to set the *
10: * boundary conditions for the ebs collection for the corresponding fact *
4:
5: /* *****************************************************************************
6: * Procedure :MTH_RUN_LOG_PRE_LOAD *
7: * Description :This procedure is used for the population of the *
8: * mth_run_log table for the initial and incremental load. The procedure is *
9: * called at the begenning of the mapping execution sequence to set the *
10: * boundary conditions for the ebs collection for the corresponding fact *
11: * or dimension. *
12: * File Name :MTHUTILB.PLS *
22: * Ankit Goyal 31-May-2007 Initial Creation *
23: * Ankit Goyal 03-Jul-2007 Incorporated pushkala comments *
24: * Amrit Kaur 14-mar-2008 Commented apps_initilalize due to Bug 6737820 *
25: ***************************************************************************** */
26: PROCEDURE mth_run_log_pre_load( p_fact_table IN VARCHAR2,
27: p_db_global_name IN VARCHAR2,
28: p_run_mode IN VARCHAR2,
29: p_run_start_date IN DATE,
30: p_is_fact IN NUMBER,
31: p_to_date IN DATE)
32: IS
33: --local variable declation
34:
35: l_fact_table mth_run_log.fact_table%TYPE;--fact table name
36: l_ebs_organization_id mth_run_log.ebs_organization_id%TYPE;
37: l_ebs_organization_code mth_run_log.ebs_organization_code%TYPE;
38: l_from_date mth_run_log.from_date%TYPE;--from date of the run
39: l_to_date mth_run_log.to_date%TYPE;--to date of the run
32: IS
33: --local variable declation
34:
35: l_fact_table mth_run_log.fact_table%TYPE;--fact table name
36: l_ebs_organization_id mth_run_log.ebs_organization_id%TYPE;
37: l_ebs_organization_code mth_run_log.ebs_organization_code%TYPE;
38: l_from_date mth_run_log.from_date%TYPE;--from date of the run
39: l_to_date mth_run_log.to_date%TYPE;--to date of the run
40: l_source mth_run_log.source%TYPE;--process or discrete. Not used currently
33: --local variable declation
34:
35: l_fact_table mth_run_log.fact_table%TYPE;--fact table name
36: l_ebs_organization_id mth_run_log.ebs_organization_id%TYPE;
37: l_ebs_organization_code mth_run_log.ebs_organization_code%TYPE;
38: l_from_date mth_run_log.from_date%TYPE;--from date of the run
39: l_to_date mth_run_log.to_date%TYPE;--to date of the run
40: l_source mth_run_log.source%TYPE;--process or discrete. Not used currently
41: l_system_fk_key mth_systems_setup.system_pk_key%TYPE;
34:
35: l_fact_table mth_run_log.fact_table%TYPE;--fact table name
36: l_ebs_organization_id mth_run_log.ebs_organization_id%TYPE;
37: l_ebs_organization_code mth_run_log.ebs_organization_code%TYPE;
38: l_from_date mth_run_log.from_date%TYPE;--from date of the run
39: l_to_date mth_run_log.to_date%TYPE;--to date of the run
40: l_source mth_run_log.source%TYPE;--process or discrete. Not used currently
41: l_system_fk_key mth_systems_setup.system_pk_key%TYPE;
42: l_creation_date mth_run_log.creation_date%TYPE;--who column
35: l_fact_table mth_run_log.fact_table%TYPE;--fact table name
36: l_ebs_organization_id mth_run_log.ebs_organization_id%TYPE;
37: l_ebs_organization_code mth_run_log.ebs_organization_code%TYPE;
38: l_from_date mth_run_log.from_date%TYPE;--from date of the run
39: l_to_date mth_run_log.to_date%TYPE;--to date of the run
40: l_source mth_run_log.source%TYPE;--process or discrete. Not used currently
41: l_system_fk_key mth_systems_setup.system_pk_key%TYPE;
42: l_creation_date mth_run_log.creation_date%TYPE;--who column
43: l_last_update_date mth_run_log.last_update_date%TYPE;--who column
36: l_ebs_organization_id mth_run_log.ebs_organization_id%TYPE;
37: l_ebs_organization_code mth_run_log.ebs_organization_code%TYPE;
38: l_from_date mth_run_log.from_date%TYPE;--from date of the run
39: l_to_date mth_run_log.to_date%TYPE;--to date of the run
40: l_source mth_run_log.source%TYPE;--process or discrete. Not used currently
41: l_system_fk_key mth_systems_setup.system_pk_key%TYPE;
42: l_creation_date mth_run_log.creation_date%TYPE;--who column
43: l_last_update_date mth_run_log.last_update_date%TYPE;--who column
44: l_creation_system_id mth_run_log.creation_system_id%TYPE;--who column
38: l_from_date mth_run_log.from_date%TYPE;--from date of the run
39: l_to_date mth_run_log.to_date%TYPE;--to date of the run
40: l_source mth_run_log.source%TYPE;--process or discrete. Not used currently
41: l_system_fk_key mth_systems_setup.system_pk_key%TYPE;
42: l_creation_date mth_run_log.creation_date%TYPE;--who column
43: l_last_update_date mth_run_log.last_update_date%TYPE;--who column
44: l_creation_system_id mth_run_log.creation_system_id%TYPE;--who column
45: l_last_update_system_id mth_run_log.last_update_system_id%TYPE;--who column
46: --l_sysdate to holding the current date
39: l_to_date mth_run_log.to_date%TYPE;--to date of the run
40: l_source mth_run_log.source%TYPE;--process or discrete. Not used currently
41: l_system_fk_key mth_systems_setup.system_pk_key%TYPE;
42: l_creation_date mth_run_log.creation_date%TYPE;--who column
43: l_last_update_date mth_run_log.last_update_date%TYPE;--who column
44: l_creation_system_id mth_run_log.creation_system_id%TYPE;--who column
45: l_last_update_system_id mth_run_log.last_update_system_id%TYPE;--who column
46: --l_sysdate to holding the current date
47: l_sysdate DATE := sysdate;--target system sysdate
40: l_source mth_run_log.source%TYPE;--process or discrete. Not used currently
41: l_system_fk_key mth_systems_setup.system_pk_key%TYPE;
42: l_creation_date mth_run_log.creation_date%TYPE;--who column
43: l_last_update_date mth_run_log.last_update_date%TYPE;--who column
44: l_creation_system_id mth_run_log.creation_system_id%TYPE;--who column
45: l_last_update_system_id mth_run_log.last_update_system_id%TYPE;--who column
46: --l_sysdate to holding the current date
47: l_sysdate DATE := sysdate;--target system sysdate
48: --l_mode is used to determine the run type , initial 0 or incremental 1
41: l_system_fk_key mth_systems_setup.system_pk_key%TYPE;
42: l_creation_date mth_run_log.creation_date%TYPE;--who column
43: l_last_update_date mth_run_log.last_update_date%TYPE;--who column
44: l_creation_system_id mth_run_log.creation_system_id%TYPE;--who column
45: l_last_update_system_id mth_run_log.last_update_system_id%TYPE;--who column
46: --l_sysdate to holding the current date
47: l_sysdate DATE := sysdate;--target system sysdate
48: --l_mode is used to determine the run type , initial 0 or incremental 1
49: l_mode NUMBER := 0;--initial default
48: --l_mode is used to determine the run type , initial 0 or incremental 1
49: l_mode NUMBER := 0;--initial default
50: l_plant_start_date DATE; --Plant end date
51: --Hub organization code
52: l_hub_organization_code mth_run_log.hub_organization_code%TYPE;
53:
54: --cursor for iterating through the ebs organizations in mth_plants_d
55: --the rows in the mth_run_log will be at organization granularity
56: CURSOR c_ebs_orgs IS
51: --Hub organization code
52: l_hub_organization_code mth_run_log.hub_organization_code%TYPE;
53:
54: --cursor for iterating through the ebs organizations in mth_plants_d
55: --the rows in the mth_run_log will be at organization granularity
56: CURSOR c_ebs_orgs IS
57: SELECT ebs_organization_id,organization_code,
58: source,plant_pk,system_fk_key,from_date
59: FROM mth_plants_d, mth_systems_setup,mth_organizations_l
64:
65: BEGIN
66: IF p_run_mode = 'INITIAL' THEN--Initial load
67: --delete the rows from run log
68: DELETE FROM mth_run_log WHERE fact_table = p_fact_table;
69:
70: END IF;
71: --initialize the local variables and who columns
72: l_fact_table := p_fact_table;
102: /* We are dealing with non-ebs org configured to the passed system*/
103: /* Check if there are records for non-ebs organizations from same system and same fact and same plant */
104: SELECT COUNT(*)
105: INTO l_mode
106: FROM mth_run_log
107: WHERE fact_table = l_fact_table
108: AND db_global_name = p_db_global_name
109: AND hub_organization_code = l_orgs.plant_pk;
110:
111: IF l_mode = 0 OR UPPER(p_run_mode) = 'INITIAL' THEN /* Initial Load */
112:
113: --statement for insert
114:
115: INSERT INTO mth_run_log (fact_table, ebs_organization_id,
116: ebs_organization_code,from_date,to_date, source, db_global_name,
117: creation_date,last_update_date,creation_system_id,
118: last_update_system_id,plant_start_date,hub_organization_code)
119: VALUES(l_fact_table,l_ebs_organization_id,l_ebs_organization_code,
125: /* update all non_ebs organizations from same system and plant with to_date as the passed date */
126: --Custom Logic for the time dimension
127: IF p_fact_table = 'MTH_WORKDAY_SHIFTS_D'
128: THEN
129: UPDATE mth_run_log
130: SET from_date = p_run_start_date
131: WHERE
132: fact_table = p_fact_table and db_global_name=p_db_global_name;
133: END IF ;
132: fact_table = p_fact_table and db_global_name=p_db_global_name;
133: END IF ;
134:
135: --statment for update
136: UPDATE mth_run_log
137: SET TO_DATE = l_to_date,
138: LAST_UPDATE_DATE = l_last_update_date,
139: LAST_UPDATE_SYSTEM_ID =l_last_update_system_id
140: WHERE
147:
148: ELSE
149: /* We are dealing with EBS Organizations */
150:
151: --determine if there are any rows in the mth_run_log for
152: --the fact_table corresponding to the org
153: SELECT COUNT(*)
154: INTO l_mode
155: FROM mth_run_log
151: --determine if there are any rows in the mth_run_log for
152: --the fact_table corresponding to the org
153: SELECT COUNT(*)
154: INTO l_mode
155: FROM mth_run_log
156: WHERE fact_table = l_fact_table
157: AND ebs_organization_id = l_orgs.ebs_organization_id
158: AND db_global_name = p_db_global_name
159: AND hub_organization_code = l_orgs.plant_pk;
168: l_creation_system_id := -1;
169:
170: --statement for insert
171:
172: INSERT INTO mth_run_log (fact_table, ebs_organization_id,
173: ebs_organization_code,from_date,to_date, source, db_global_name,
174: creation_date,last_update_date,creation_system_id,
175: last_update_system_id,plant_start_date,hub_organization_code)
176: VALUES(l_fact_table,l_ebs_organization_id,l_ebs_organization_code,
185:
186: --Custom Logic for the time dimension
187: IF p_fact_table = 'MTH_WORKDAY_SHIFTS_D'
188: THEN
189: UPDATE mth_run_log
190: SET from_date = p_run_start_date
191: WHERE
192: fact_table = p_fact_table;
193: END IF ;
191: WHERE
192: fact_table = p_fact_table;
193: END IF ;
194: --statment for update
195: UPDATE mth_run_log
196: SET TO_DATE = l_to_date,
197: LAST_UPDATE_DATE = l_last_update_date,
198: LAST_UPDATE_SYSTEM_ID =l_last_update_system_id
199: WHERE
215: WHEN NO_DATA_FOUND THEN
216: RAISE_APPLICATION_ERROR (-20001,
217: 'Exception has occured');
218:
219: END mth_run_log_pre_load;
220:
221:
222: /* *****************************************************************************
223: * Procedure :MTH_RUN_LOG_POST_LOAD *
219: END mth_run_log_pre_load;
220:
221:
222: /* *****************************************************************************
223: * Procedure :MTH_RUN_LOG_POST_LOAD *
224: * Description :This procedure is used for the population of the *
225: * mth_run_log table for the initial and incremental load. The procedure is *
226: * called at the end of the mapping execution sequence to set the *
227: * boundary conditions for the ebs collection for the corresponding fact *
221:
222: /* *****************************************************************************
223: * Procedure :MTH_RUN_LOG_POST_LOAD *
224: * Description :This procedure is used for the population of the *
225: * mth_run_log table for the initial and incremental load. The procedure is *
226: * called at the end of the mapping execution sequence to set the *
227: * boundary conditions for the ebs collection for the corresponding fact *
228: * or dimension. *
229: * File Name : MTHUTILB.PLS *
235: * Ankit Goyal 31-May-2007 Initial Creation *
236: * Ankit Goyal 03-Jul-2007 Incorporated pushkala's comments *
237: * viveksha 30-Jan-2009 Updated the procedure to update txn ids *
238: ***************************************************************************** */
239: PROCEDURE mth_run_log_post_load(p_fact_table IN VARCHAR2,
240: p_db_global_name IN VARCHAR2)
241:
242: IS
243:
241:
242: IS
243:
244: --local variables initialization
245: l_fact_table mth_run_log.fact_table%TYPE;--fact table
246: l_sysdate DATE := sysdate;--variable for sysdate
247: l_last_update_system_id mth_run_log.last_update_system_id%TYPE;
248: l_last_update_date mth_run_log.last_update_date%TYPE;
249: l_system_fk_key mth_systems_setup.system_pk_key%TYPE;
243:
244: --local variables initialization
245: l_fact_table mth_run_log.fact_table%TYPE;--fact table
246: l_sysdate DATE := sysdate;--variable for sysdate
247: l_last_update_system_id mth_run_log.last_update_system_id%TYPE;
248: l_last_update_date mth_run_log.last_update_date%TYPE;
249: l_system_fk_key mth_systems_setup.system_pk_key%TYPE;
250: l_to_date mth_run_log.to_date%TYPE;--variable for storing to_date
251: l_from_date mth_run_log.from_date%TYPE;--variable for storing from_date
244: --local variables initialization
245: l_fact_table mth_run_log.fact_table%TYPE;--fact table
246: l_sysdate DATE := sysdate;--variable for sysdate
247: l_last_update_system_id mth_run_log.last_update_system_id%TYPE;
248: l_last_update_date mth_run_log.last_update_date%TYPE;
249: l_system_fk_key mth_systems_setup.system_pk_key%TYPE;
250: l_to_date mth_run_log.to_date%TYPE;--variable for storing to_date
251: l_from_date mth_run_log.from_date%TYPE;--variable for storing from_date
252: l_ebs_organization_id mth_run_log.ebs_organization_id%TYPE;
246: l_sysdate DATE := sysdate;--variable for sysdate
247: l_last_update_system_id mth_run_log.last_update_system_id%TYPE;
248: l_last_update_date mth_run_log.last_update_date%TYPE;
249: l_system_fk_key mth_systems_setup.system_pk_key%TYPE;
250: l_to_date mth_run_log.to_date%TYPE;--variable for storing to_date
251: l_from_date mth_run_log.from_date%TYPE;--variable for storing from_date
252: l_ebs_organization_id mth_run_log.ebs_organization_id%TYPE;
253: --Hub organization code
254: l_hub_organization_code mth_run_log.hub_organization_code%TYPE;
247: l_last_update_system_id mth_run_log.last_update_system_id%TYPE;
248: l_last_update_date mth_run_log.last_update_date%TYPE;
249: l_system_fk_key mth_systems_setup.system_pk_key%TYPE;
250: l_to_date mth_run_log.to_date%TYPE;--variable for storing to_date
251: l_from_date mth_run_log.from_date%TYPE;--variable for storing from_date
252: l_ebs_organization_id mth_run_log.ebs_organization_id%TYPE;
253: --Hub organization code
254: l_hub_organization_code mth_run_log.hub_organization_code%TYPE;
255: --TXN IDS
248: l_last_update_date mth_run_log.last_update_date%TYPE;
249: l_system_fk_key mth_systems_setup.system_pk_key%TYPE;
250: l_to_date mth_run_log.to_date%TYPE;--variable for storing to_date
251: l_from_date mth_run_log.from_date%TYPE;--variable for storing from_date
252: l_ebs_organization_id mth_run_log.ebs_organization_id%TYPE;
253: --Hub organization code
254: l_hub_organization_code mth_run_log.hub_organization_code%TYPE;
255: --TXN IDS
256: l_to_txn_id mth_run_log.to_txn_id%TYPE;
250: l_to_date mth_run_log.to_date%TYPE;--variable for storing to_date
251: l_from_date mth_run_log.from_date%TYPE;--variable for storing from_date
252: l_ebs_organization_id mth_run_log.ebs_organization_id%TYPE;
253: --Hub organization code
254: l_hub_organization_code mth_run_log.hub_organization_code%TYPE;
255: --TXN IDS
256: l_to_txn_id mth_run_log.to_txn_id%TYPE;
257: l_from_txn_id mth_run_log.from_txn_id%TYPE;
258:
252: l_ebs_organization_id mth_run_log.ebs_organization_id%TYPE;
253: --Hub organization code
254: l_hub_organization_code mth_run_log.hub_organization_code%TYPE;
255: --TXN IDS
256: l_to_txn_id mth_run_log.to_txn_id%TYPE;
257: l_from_txn_id mth_run_log.from_txn_id%TYPE;
258:
259: --cursor for iterating through the ebs organizations in mth_plants_d
260: --the rows in the mth_run_log will be at organization granularity
253: --Hub organization code
254: l_hub_organization_code mth_run_log.hub_organization_code%TYPE;
255: --TXN IDS
256: l_to_txn_id mth_run_log.to_txn_id%TYPE;
257: l_from_txn_id mth_run_log.from_txn_id%TYPE;
258:
259: --cursor for iterating through the ebs organizations in mth_plants_d
260: --the rows in the mth_run_log will be at organization granularity
261:
256: l_to_txn_id mth_run_log.to_txn_id%TYPE;
257: l_from_txn_id mth_run_log.from_txn_id%TYPE;
258:
259: --cursor for iterating through the ebs organizations in mth_plants_d
260: --the rows in the mth_run_log will be at organization granularity
261:
262: CURSOR c_ebs_orgs
263: IS
264: SELECT ebs_organization_id,system_fk_key
283: all organizations are populated with sysdate(source or target) as the to_date. So giving a generic update command here would work */
284:
285: SELECT min(to_date) --min is to avoid getting duplicate rows
286: INTO l_from_date--from date set to previous to_date
287: FROM mth_run_log
288: WHERE fact_table = l_fact_table
289: AND db_global_name = p_db_global_name;
290:
291: --select the next starting txn id into l_from_txn_id
290:
291: --select the next starting txn id into l_from_txn_id
292: SELECT MIN(to_txn_id)
293: INTO l_from_txn_id--from txn id to be set to previous to txn id
294: FROM mth_run_log
295: WHERE fact_table = l_fact_table
296: AND db_global_name = p_db_global_name;
297:
298:
297:
298:
299: --if statement to restrict the accidental re run of the block
300: IF l_from_date IS NOT NULL THEN
301: --update the mth run log for next incremental run
302:
303: UPDATE mth_run_log
304: SET from_date = l_from_date,--from date set to previous to_date
305: to_date = NULL,--to_date set to null for next run
299: --if statement to restrict the accidental re run of the block
300: IF l_from_date IS NOT NULL THEN
301: --update the mth run log for next incremental run
302:
303: UPDATE mth_run_log
304: SET from_date = l_from_date,--from date set to previous to_date
305: to_date = NULL,--to_date set to null for next run
306: last_update_date = l_last_update_date,
307: from_txn_id = l_from_txn_id, -- from txn id set to previous to txn id
322:
323: --select the next starting date into l_from_date
324: SELECT to_date
325: INTO l_from_date--from date set to previous to_date
326: FROM mth_run_log
327: WHERE fact_table = l_fact_table
328: AND db_global_name = p_db_global_name
329: AND ebs_organization_id = l_ebs_organization_id;
330:
332:
333:
334: --if statement to restrict the accidental re run of the block
335: IF l_from_date IS NOT NULL THEN
336: --update the mth run log for next incremental run
337:
338: UPDATE mth_run_log
339: SET from_date = l_from_date,--from date set to previous to_date
340: to_date = NULL,--to_date set to null for next run
334: --if statement to restrict the accidental re run of the block
335: IF l_from_date IS NOT NULL THEN
336: --update the mth run log for next incremental run
337:
338: UPDATE mth_run_log
339: SET from_date = l_from_date,--from date set to previous to_date
340: to_date = NULL,--to_date set to null for next run
341: from_txn_id = l_from_txn_id, -- from txn id set to previous to txn id
342: to_txn_id = NULL,
354: EXCEPTION
355: WHEN NO_DATA_FOUND THEN
356: RAISE_APPLICATION_ERROR (-20001,
357: 'Exception has occured');
358: END mth_run_log_post_load;
359:
360:
361: /* *****************************************************************************
362: * Procedure :MTH_HRCHY_BALANCE_LOAD *
891: /* ****************************************************************************
892: * Procedure :generate_new_time_range *
893: * Description :This procedure is used to generate a time range *
894: * starting from the last end date up to current time, sysdate, using *
895: * the p_key_table name as the key to look up the entry in mth_run_log *
896: * table. If the entry does not exist, create one and set the time range *
897: * to a hard-coded past time to current time. *
898: * File Name :MTHUTILB.PLS *
899: * Visibility :Public *
898: * File Name :MTHUTILB.PLS *
899: * Visibility :Public *
900: * Parameters *
901: * p_key_table : Name to uniquely identify one entry in the *
902: * mth_run_log table. *
903: * p_start_date : An output value that specifies the start time *
904: * of the new time period. *
905: * p_end_date : An output value that specifies the end time *
906: * of the new time period. *
918: p_start_date OUT NOCOPY DATE,
919: p_end_date OUT NOCOPY DATE,
920: p_exclusive_lock IN NUMBER DEFAULT 1)
921: IS
922: v_from_date mth_run_log.from_date%TYPE;
923: v_to_date mth_run_log.to_date%TYPE;
924: v_is_new_entry BOOLEAN := FALSE;
925: v_default_start_date DATE := to_date('1950', 'YYYY');
926: v_sysdate DATE := sysdate;
919: p_end_date OUT NOCOPY DATE,
920: p_exclusive_lock IN NUMBER DEFAULT 1)
921: IS
922: v_from_date mth_run_log.from_date%TYPE;
923: v_to_date mth_run_log.to_date%TYPE;
924: v_is_new_entry BOOLEAN := FALSE;
925: v_default_start_date DATE := to_date('1950', 'YYYY');
926: v_sysdate DATE := sysdate;
927: v_retval number := 0;
927: v_retval number := 0;
928:
929: CURSOR c_lookup IS
930: SELECT to_date
931: FROM mth_run_log
932: WHERE fact_table = p_key_table and rownum=1;
933: BEGIN
934: -- 1. Validate the p_fact_table input value.
935: IF (p_key_table is not null) THEN
957:
958: -- 4. Create a new entry if not exist. Otherwise, update the entry
959: IF v_is_new_entry THEN
960: v_from_date := v_default_start_date;
961: INSERT INTO mth_run_log
962: (fact_table, ebs_organization_id, ebs_organization_code, from_date,
963: to_date, source, db_global_name, creation_date, last_update_date,
964: creation_system_id, last_update_system_id, plant_start_date)
965: VALUES
966: (p_key_table, -1, '-1', v_from_date,
967: v_to_date, -1, '-99999', v_sysdate, v_sysdate,
968: -1, -1, v_default_start_date);
969: ELSE
970: UPDATE mth_run_log
971: SET TO_DATE = v_to_date,
972: FROM_DATE = v_from_date,
973: LAST_UPDATE_DATE = v_sysdate
974: WHERE
1299: * - C , c or NULL indicates complete refresh. *
1300: * - R or r indicates resume refresh that has been *
1301: * started earlier. The MV will be refreshed if the *
1302: * refresh date is earlier than the date stored in *
1303: * to_date column in MTH_RUN_LOG for MTH_ALL_MVS entry. *
1304: * p_push_deferred_rpc : Used by updatable materialized views only. *
1305: * Modification log : *
1306: * Author Date Change *
1307: * Yong Feng 19-Aug-2008 Initial Creation *
1334: -- Need to refresh in mode C
1335: v_bneed_refresh := TRUE;
1336: ELSE
1337: -- It is a resume operation
1338: -- First, find the refresh date in MTH_RUN_LOG in resume case
1339: SELECT max(to_date) into v_refresh_date_required
1340: FROM mth_run_log
1341: WHERE fact_table = 'MTH_ALL_MVS' AND db_global_name = v_unassigned_string;
1342: IF (v_refresh_date_required IS NULL) THEN
1336: ELSE
1337: -- It is a resume operation
1338: -- First, find the refresh date in MTH_RUN_LOG in resume case
1339: SELECT max(to_date) into v_refresh_date_required
1340: FROM mth_run_log
1341: WHERE fact_table = 'MTH_ALL_MVS' AND db_global_name = v_unassigned_string;
1342: IF (v_refresh_date_required IS NULL) THEN
1343: -- Refresh the MV if there is no entry found in MTH_RUN_LOG
1344: v_bneed_refresh := TRUE;
1339: SELECT max(to_date) into v_refresh_date_required
1340: FROM mth_run_log
1341: WHERE fact_table = 'MTH_ALL_MVS' AND db_global_name = v_unassigned_string;
1342: IF (v_refresh_date_required IS NULL) THEN
1343: -- Refresh the MV if there is no entry found in MTH_RUN_LOG
1344: v_bneed_refresh := TRUE;
1345: ELSE
1346: -- First get the last refresh date of the MV
1347: -- Then, compare the last refresh date of the MV with the date from the MTH_RUN_LOG
1343: -- Refresh the MV if there is no entry found in MTH_RUN_LOG
1344: v_bneed_refresh := TRUE;
1345: ELSE
1346: -- First get the last refresh date of the MV
1347: -- Then, compare the last refresh date of the MV with the date from the MTH_RUN_LOG
1348: -- to decide whether the MV needs to be refreshed
1349: OPEN getRefreshDate ( v_mv_name);
1350: FETCH getRefreshDate INTO v_last_refresh_date;
1351: IF (getRefreshDate%FOUND) THEN
1738: EXECUTE IMMEDIATE v_stmt;
1739: END truncate_table_partition;
1740:
1741: /* *****************************************************************************
1742: * Procedure :mth_run_log_pre_load *
1743: * Description :This procedure will log entries when a map is run taking *
1744: * transaction id and populating from_txn_id and to_txn_id *
1745: * File Name :MTHUTILS.PLS *
1746: * Visibility :Public *
1747: * Modification log : *
1748: * Author Date Change: Vivek Sharma 21-Jan-2009 Initial creation *
1749: ***************************************************************************** */
1750:
1751: PROCEDURE mth_run_log_pre_load( p_fact_table IN VARCHAR2,
1752: p_db_global_name IN VARCHAR2,
1753: p_run_mode IN VARCHAR2,
1754: p_run_start_date IN DATE,
1755: p_is_fact IN NUMBER,
1757: p_to_txn_id IN NUMBER)
1758: IS
1759: --local variable declation
1760:
1761: l_fact_table mth_run_log.fact_table%TYPE;--fact table name
1762: l_ebs_organization_id mth_run_log.ebs_organization_id%TYPE;
1763: l_ebs_organization_code mth_run_log.ebs_organization_code%TYPE;
1764: l_from_date mth_run_log.from_date%TYPE;--from date of the run
1765: l_to_date mth_run_log.to_date%TYPE;--to date of the run
1758: IS
1759: --local variable declation
1760:
1761: l_fact_table mth_run_log.fact_table%TYPE;--fact table name
1762: l_ebs_organization_id mth_run_log.ebs_organization_id%TYPE;
1763: l_ebs_organization_code mth_run_log.ebs_organization_code%TYPE;
1764: l_from_date mth_run_log.from_date%TYPE;--from date of the run
1765: l_to_date mth_run_log.to_date%TYPE;--to date of the run
1766: l_source mth_run_log.source%TYPE;--process or discrete. Not used currently
1759: --local variable declation
1760:
1761: l_fact_table mth_run_log.fact_table%TYPE;--fact table name
1762: l_ebs_organization_id mth_run_log.ebs_organization_id%TYPE;
1763: l_ebs_organization_code mth_run_log.ebs_organization_code%TYPE;
1764: l_from_date mth_run_log.from_date%TYPE;--from date of the run
1765: l_to_date mth_run_log.to_date%TYPE;--to date of the run
1766: l_source mth_run_log.source%TYPE;--process or discrete. Not used currently
1767: l_system_fk_key mth_systems_setup.system_pk_key%TYPE;
1760:
1761: l_fact_table mth_run_log.fact_table%TYPE;--fact table name
1762: l_ebs_organization_id mth_run_log.ebs_organization_id%TYPE;
1763: l_ebs_organization_code mth_run_log.ebs_organization_code%TYPE;
1764: l_from_date mth_run_log.from_date%TYPE;--from date of the run
1765: l_to_date mth_run_log.to_date%TYPE;--to date of the run
1766: l_source mth_run_log.source%TYPE;--process or discrete. Not used currently
1767: l_system_fk_key mth_systems_setup.system_pk_key%TYPE;
1768: l_creation_date mth_run_log.creation_date%TYPE;--who column
1761: l_fact_table mth_run_log.fact_table%TYPE;--fact table name
1762: l_ebs_organization_id mth_run_log.ebs_organization_id%TYPE;
1763: l_ebs_organization_code mth_run_log.ebs_organization_code%TYPE;
1764: l_from_date mth_run_log.from_date%TYPE;--from date of the run
1765: l_to_date mth_run_log.to_date%TYPE;--to date of the run
1766: l_source mth_run_log.source%TYPE;--process or discrete. Not used currently
1767: l_system_fk_key mth_systems_setup.system_pk_key%TYPE;
1768: l_creation_date mth_run_log.creation_date%TYPE;--who column
1769: l_last_update_date mth_run_log.last_update_date%TYPE;--who column
1762: l_ebs_organization_id mth_run_log.ebs_organization_id%TYPE;
1763: l_ebs_organization_code mth_run_log.ebs_organization_code%TYPE;
1764: l_from_date mth_run_log.from_date%TYPE;--from date of the run
1765: l_to_date mth_run_log.to_date%TYPE;--to date of the run
1766: l_source mth_run_log.source%TYPE;--process or discrete. Not used currently
1767: l_system_fk_key mth_systems_setup.system_pk_key%TYPE;
1768: l_creation_date mth_run_log.creation_date%TYPE;--who column
1769: l_last_update_date mth_run_log.last_update_date%TYPE;--who column
1770: l_creation_system_id mth_run_log.creation_system_id%TYPE;--who column
1764: l_from_date mth_run_log.from_date%TYPE;--from date of the run
1765: l_to_date mth_run_log.to_date%TYPE;--to date of the run
1766: l_source mth_run_log.source%TYPE;--process or discrete. Not used currently
1767: l_system_fk_key mth_systems_setup.system_pk_key%TYPE;
1768: l_creation_date mth_run_log.creation_date%TYPE;--who column
1769: l_last_update_date mth_run_log.last_update_date%TYPE;--who column
1770: l_creation_system_id mth_run_log.creation_system_id%TYPE;--who column
1771: l_last_update_system_id mth_run_log.last_update_system_id%TYPE;--who column
1772: --l_sysdate to holding the current date
1765: l_to_date mth_run_log.to_date%TYPE;--to date of the run
1766: l_source mth_run_log.source%TYPE;--process or discrete. Not used currently
1767: l_system_fk_key mth_systems_setup.system_pk_key%TYPE;
1768: l_creation_date mth_run_log.creation_date%TYPE;--who column
1769: l_last_update_date mth_run_log.last_update_date%TYPE;--who column
1770: l_creation_system_id mth_run_log.creation_system_id%TYPE;--who column
1771: l_last_update_system_id mth_run_log.last_update_system_id%TYPE;--who column
1772: --l_sysdate to holding the current date
1773: l_sysdate DATE := sysdate;--target system sysdate
1766: l_source mth_run_log.source%TYPE;--process or discrete. Not used currently
1767: l_system_fk_key mth_systems_setup.system_pk_key%TYPE;
1768: l_creation_date mth_run_log.creation_date%TYPE;--who column
1769: l_last_update_date mth_run_log.last_update_date%TYPE;--who column
1770: l_creation_system_id mth_run_log.creation_system_id%TYPE;--who column
1771: l_last_update_system_id mth_run_log.last_update_system_id%TYPE;--who column
1772: --l_sysdate to holding the current date
1773: l_sysdate DATE := sysdate;--target system sysdate
1774: --l_mode is used to determine the run type , initial 0 or incremental 1
1767: l_system_fk_key mth_systems_setup.system_pk_key%TYPE;
1768: l_creation_date mth_run_log.creation_date%TYPE;--who column
1769: l_last_update_date mth_run_log.last_update_date%TYPE;--who column
1770: l_creation_system_id mth_run_log.creation_system_id%TYPE;--who column
1771: l_last_update_system_id mth_run_log.last_update_system_id%TYPE;--who column
1772: --l_sysdate to holding the current date
1773: l_sysdate DATE := sysdate;--target system sysdate
1774: --l_mode is used to determine the run type , initial 0 or incremental 1
1775: l_mode NUMBER := 0;--initial default
1774: --l_mode is used to determine the run type , initial 0 or incremental 1
1775: l_mode NUMBER := 0;--initial default
1776: l_plant_start_date DATE; --Plant end date
1777: --Hub organization code
1778: l_hub_organization_code mth_run_log.hub_organization_code%TYPE;
1779: --TXN IDS
1780: l_to_txn_id mth_run_log.to_txn_id%TYPE;
1781: l_from_txn_id mth_run_log.from_txn_id%TYPE;
1782:
1776: l_plant_start_date DATE; --Plant end date
1777: --Hub organization code
1778: l_hub_organization_code mth_run_log.hub_organization_code%TYPE;
1779: --TXN IDS
1780: l_to_txn_id mth_run_log.to_txn_id%TYPE;
1781: l_from_txn_id mth_run_log.from_txn_id%TYPE;
1782:
1783: --cursor for iterating through the ebs organizations in mth_plants_d
1784: --the rows in the mth_run_log will be at organization granularity
1777: --Hub organization code
1778: l_hub_organization_code mth_run_log.hub_organization_code%TYPE;
1779: --TXN IDS
1780: l_to_txn_id mth_run_log.to_txn_id%TYPE;
1781: l_from_txn_id mth_run_log.from_txn_id%TYPE;
1782:
1783: --cursor for iterating through the ebs organizations in mth_plants_d
1784: --the rows in the mth_run_log will be at organization granularity
1785: CURSOR c_ebs_orgs IS
1780: l_to_txn_id mth_run_log.to_txn_id%TYPE;
1781: l_from_txn_id mth_run_log.from_txn_id%TYPE;
1782:
1783: --cursor for iterating through the ebs organizations in mth_plants_d
1784: --the rows in the mth_run_log will be at organization granularity
1785: CURSOR c_ebs_orgs IS
1786: SELECT ebs_organization_id,organization_code,
1787: source,plant_pk,system_fk_key,from_date
1788: FROM mth_plants_d, mth_systems_setup,mth_organizations_l
1794: BEGIN
1795: IF p_run_mode = 'INITIAL' THEN--Initial load
1796: l_from_txn_id := 0;
1797: --delete the rows from run log
1798: DELETE FROM mth_run_log WHERE fact_table = p_fact_table;
1799:
1800: END IF;
1801: --initialize the local variables and who columns
1802: l_fact_table := p_fact_table;
1833: /* We are dealing with non-ebs org configured to the passed system*/
1834: /* Check if there are records for non-ebs organizations from same system and same fact and same plant */
1835: SELECT COUNT(*)
1836: INTO l_mode
1837: FROM mth_run_log
1838: WHERE fact_table = l_fact_table
1839: AND db_global_name = p_db_global_name
1840: AND hub_organization_code = l_orgs.plant_pk;
1841:
1842: IF l_mode = 0 OR UPPER(p_run_mode) = 'INITIAL' THEN /* Initial Load */
1843:
1844: --statement for insert
1845:
1846: INSERT INTO mth_run_log (fact_table, ebs_organization_id,
1847: ebs_organization_code,from_date,to_date, source, db_global_name,
1848: creation_date,last_update_date,creation_system_id,
1849: last_update_system_id,plant_start_date,hub_organization_code,from_txn_id,to_txn_id)
1850: VALUES(l_fact_table,l_ebs_organization_id,l_ebs_organization_code,
1856: /* update all non_ebs organizations from same system and plant with to_date as the passed date */
1857: --Custom Logic for the time dimension
1858: IF p_fact_table = 'MTH_WORKDAY_SHIFTS_D'
1859: THEN
1860: UPDATE mth_run_log
1861: SET from_date = p_run_start_date
1862: WHERE
1863: fact_table = p_fact_table and db_global_name=p_db_global_name;
1864: END IF ;
1863: fact_table = p_fact_table and db_global_name=p_db_global_name;
1864: END IF ;
1865:
1866: --statment for update
1867: UPDATE mth_run_log
1868: SET TO_DATE = l_to_date,
1869: TO_TXN_ID = l_to_txn_id,
1870: LAST_UPDATE_DATE = l_last_update_date,
1871: LAST_UPDATE_SYSTEM_ID =l_last_update_system_id
1879:
1880: ELSE
1881: /* We are dealing with EBS Organizations */
1882:
1883: --determine if there are any rows in the mth_run_log for
1884: --the fact_table corresponding to the org
1885: SELECT COUNT(*)
1886: INTO l_mode
1887: FROM mth_run_log
1883: --determine if there are any rows in the mth_run_log for
1884: --the fact_table corresponding to the org
1885: SELECT COUNT(*)
1886: INTO l_mode
1887: FROM mth_run_log
1888: WHERE fact_table = l_fact_table
1889: AND ebs_organization_id = l_orgs.ebs_organization_id
1890: AND db_global_name = p_db_global_name
1891: AND hub_organization_code = l_orgs.plant_pk;
1900: l_creation_system_id := -1;
1901:
1902: --statement for insert
1903:
1904: INSERT INTO mth_run_log (fact_table, ebs_organization_id,
1905: ebs_organization_code,from_date,to_date, source, db_global_name,
1906: creation_date,last_update_date,creation_system_id,
1907: last_update_system_id,plant_start_date,hub_organization_code,from_txn_id,to_txn_id)
1908: VALUES(l_fact_table,l_ebs_organization_id,l_ebs_organization_code,
1917:
1918: --Custom Logic for the time dimension
1919: IF p_fact_table = 'MTH_WORKDAY_SHIFTS_D'
1920: THEN
1921: UPDATE mth_run_log
1922: SET from_date = p_run_start_date
1923: WHERE
1924: fact_table = p_fact_table;
1925: END IF ;
1923: WHERE
1924: fact_table = p_fact_table;
1925: END IF ;
1926: --statment for update
1927: UPDATE mth_run_log
1928: SET TO_DATE = l_to_date,
1929: TO_TXN_ID = l_to_txn_id,
1930: LAST_UPDATE_DATE = l_last_update_date,
1931: LAST_UPDATE_SYSTEM_ID =l_last_update_system_id
1948: WHEN NO_DATA_FOUND THEN
1949: RAISE_APPLICATION_ERROR (-20001,
1950: 'Exception has occured');
1951:
1952: END mth_run_log_pre_load;
1953:
1954:
1955:
1956: /* ****************************************************************************
2664: v_start_time DATE;
2665: v_end_time DATE;
2666: v_run_date DATE;
2667: BEGIN
2668: SELECT DISTINCT from_date INTO v_run_date FROM mth_run_log WHERE fact_table = 'MTH_EQUIP_DOWN_STS_UPTIME';
2669:
2670: FOR i IN (SELECT equipment_fk_key,shift_workday_fk_key,from_date,To_Date,system_fk_key,creation_date,last_update_date,creation_system_id,
2671: last_update_system_id FROM mth_equip_statuses WHERE status=3 AND To_Date IS NOT NULL AND last_update_date>=v_run_date)
2672: LOOP
3868: mth_util_pkg.log_msg('p_ebs_organization_id = ' || p_ebs_organization_id , mth_util_pkg.G_DBG_PARAM_VAL);
3869:
3870: SELECT MAX(FROM_DATE), MAX(TO_DATE)
3871: INTO p_from_date, p_to_date
3872: FROM MTH_RUN_LOG
3873: WHERE FACT_TABLE = p_fact_table
3874: AND (db_global_name = nvl(p_db_global_name,db_global_name)
3875: OR db_global_name IS NULL)
3876: AND (ebs_organization_id = nvl(p_ebs_organization_id,ebs_organization_id)
4010: v_log_to_date := sysdate;
4011: v_unassigned_val := MTH_UTIL_PKG.MTH_UA_GET_VAL;
4012:
4013:
4014: -- Call mth_run_log_pre_load
4015: mth_util_pkg.mth_run_log_pre_load('MTH_EQUIPMENT_SHIFTS_D',v_unassigned_val,'INCR',NULL,0,v_log_to_date);
4016:
4017: -- Call GET_RUN_LOG_DATES
4018: mth_util_pkg.GET_RUN_LOG_DATES('MTH_EQUIPMENT_SHIFTS_D',NULL,NULL,NULL,v_log_from_date,v_log_to_date);
4011: v_unassigned_val := MTH_UTIL_PKG.MTH_UA_GET_VAL;
4012:
4013:
4014: -- Call mth_run_log_pre_load
4015: mth_util_pkg.mth_run_log_pre_load('MTH_EQUIPMENT_SHIFTS_D',v_unassigned_val,'INCR',NULL,0,v_log_to_date);
4016:
4017: -- Call GET_RUN_LOG_DATES
4018: mth_util_pkg.GET_RUN_LOG_DATES('MTH_EQUIPMENT_SHIFTS_D',NULL,NULL,NULL,v_log_from_date,v_log_to_date);
4019:
4092: ) )
4093: ;
4094: mth_util_pkg.log_msg('Number of rows inserted in MTH_EQUIPMENT_SHIFTS_D - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
4095:
4096: ----Call mth_run_log_post_load
4097: mth_util_pkg.mth_run_log_post_load('MTH_EQUIPMENT_SHIFTS_D',v_unassigned_val);
4098:
4099:
4100: mth_util_pkg.log_msg('INCR_EQUIPMENT_SHIFTS end', mth_util_pkg.G_DBG_PROC_FUN_END);
4093: ;
4094: mth_util_pkg.log_msg('Number of rows inserted in MTH_EQUIPMENT_SHIFTS_D - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
4095:
4096: ----Call mth_run_log_post_load
4097: mth_util_pkg.mth_run_log_post_load('MTH_EQUIPMENT_SHIFTS_D',v_unassigned_val);
4098:
4099:
4100: mth_util_pkg.log_msg('INCR_EQUIPMENT_SHIFTS end', mth_util_pkg.G_DBG_PROC_FUN_END);
4101: COMMIT;
4749: Dbms_Mview.REFRESH(r_mv.LOOKUP_CODE, 'C', '', FALSE, FALSE, 0, 0, 0, TRUE);
4750:
4751: END LOOP;
4752:
4753: DELETE FROM MTH_RUN_LOG rl
4754: WHERE exists (
4755: SELECT 1
4756: FROM FND_LOOKUP_VALUES lv
4757: WHERE lv.LOOKUP_TYPE=v_lookup_type