[Home] [Help]
PACKAGE BODY: APPS.OPI_UOM_WH_PUSH_PKG
Source
1 PACKAGE BODY OPI_UOM_WH_PUSH_PKG as
2 /* $Header: OPIUOMPB.pls 115.9 2002/04/29 15:24:15 pkm ship $ */
3 l_db_link VARCHAR2(240);
4
5 CURSOR sources IS
6 SELECT instance_code, warehouse_to_instance_link
7 FROM edw_source_instances_vl
8 WHERE enabled_flag = 'Y';
9
10 Function isInstanceRunning(p_mode IN NUMBER, p_db_link IN VARCHAR2, p_instance IN VARCHAR2) RETURN BOOLEAN IS
11 cid NUMBER := 0;
12 bRunning BOOLEAN := TRUE;
13 BEGIN
14 edw_misc_util.globalNamesOff;
15 cid := DBMS_SQL.open_cursor;
16 BEGIN
17 dbms_sql.parse(cid, 'SELECT 1 FROM dual@'||p_db_link, dbms_sql.native);
18 Exception
19 WHEN OTHERS THEN
20
21 bRunning := FALSE;
22 edw_owb_collection_util.write_to_log_file('Instance not running: '|| p_db_link || ' : '||SQLERRM);
23 END;
24
25 dbms_sql.close_cursor(cid);
26
27 IF (p_mode = 1) THEN
28 IF (bRunning = FALSE) THEN
29 fnd_message.set_name('BIS', 'EDW_BAD_DBLINK');
30 fnd_message.set_token('DBLINK', p_instance, FALSE);
31 app_exception.raise_exception;
32 END IF;
33 END IF;
34 RETURN bRunning;
35 END;
36
37 PROCEDURE pushToSource(p_object_name IN varchar2) IS
38
39 l_temp VARCHAR2(200):=NULL;
40 l_inst_down VARCHAR2(2000):=NULL;
41 l_stmt VARCHAR2(5000):=NULL;
42 cid NUMBER:=0;
43 l_dummy NUMBER:=0;
44 l_progress varchar2(10);
45 l_count NUMBER := 0;
46 BEGIN
47 edw_misc_util.globalNamesOff;
48 SAVEPOINT start_push_to_source;
49
50 l_progress := '010';
51 cid := DBMS_SQL.open_cursor;
52 --open instances;
53 open sources;
54 LOOP
55 l_progress := '020';
56 --FETCH instances INTO l_db_link;
57 fetch sources into l_temp, l_db_link;
58 EXIT WHEN sources%NOTFOUND;
59
60 IF isInstanceRunning(0, l_db_link, l_temp) THEN
61 null;
62 ELSE
63 l_inst_down := l_inst_down ||l_temp||' ';
64 END IF;
65 -- Check to see if the link points to itself, is so skip for this instance
66 /* ***** 1922031 Bug Fix : Removed conditions to check if the
67 db link points to itself. The ODF file containing the Misc
68 Block for target will contain two local tables for
69 UOM Dimension and UOM Conversion Fact.
70 This will ensure that the post-load goes through successfully
71 even if the target's source flag is turned on for POA DUNS
72 dimension.****** */
73 IF ( isInstanceRunning(0, l_db_link, l_temp)) THEN
74
75 IF p_object_name = 'EDW_MTL_UOM_M' then
76
77 l_progress := '030';
78 DBMS_SQL.PARSE(cid, 'DELETE EDW_MTL_LOCAL_UOM_M@'||l_db_link, dbms_sql.native);
79 l_dummy := dbms_sql.execute(cid);
80 l_progress := '040';
81 l_stmt := 'INSERT INTO EDW_MTL_LOCAL_UOM_M@'||l_db_link||' (UOM_EDW_UOM_PK, UOM_GLOBAL_FLAG,UOM_EDW_BASE_UOM, UOM_CONVERSION_RATE, LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN)';
82 l_stmt := l_stmt || ' SELECT UOM_EDW_UOM_PK, UOM_GLOBAL_FLAG, UOM_EDW_BASE_UOM, UOM_CONVERSION_RATE, SYSDATE, -1, SYSDATE, -1, -1 FROM EDW_MTL_UOM_M';
83 l_progress := '050';
84 DBMS_SQL.PARSE(cid, l_stmt, dbms_sql.native);
85 l_dummy := dbms_sql.execute(cid);
86 l_progress := '060';
87
88 ELSIF p_object_name = 'OPI_EDW_UOM_CONV_F' then
89
90 l_progress := '070';
91 DBMS_SQL.PARSE(cid, 'DELETE OPI_EDW_LOCAL_UOM_CONV_F@'||l_db_link, dbms_sql.native);
92 l_dummy := dbms_sql.execute(cid);
93
94 l_progress := '080';
95
96 l_stmt := 'INSERT INTO OPI_EDW_LOCAL_UOM_CONV_F@'||l_db_link||' (UOM_CONV_PK, EDW_BASE_UOM_FK, EDW_UOM_FK, EDW_CONVERSION_RATE,CLASS_CONVERSION_FLAG, LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN)';
97 l_stmt := l_stmt || ' SELECT UOM_CONV_PK, EDW_BASE_UOM_FK, EDW_UOM_FK, EDW_CONVERSION_RATE, CLASS_CONVERSION_FLAG, SYSDATE, -1, SYSDATE, -1, -1 FROM OPI_EDW_UOM_CONV_F';
98 l_progress := '090';
99
100 DBMS_SQL.PARSE(cid, l_stmt, dbms_sql.native);
101 l_dummy := dbms_sql.execute(cid);
102 l_progress := '100';
103 END IF;
104 END IF;
105
106 END LOOP;
107
108 DBMS_SQL.close_cursor(cid);
109
110 close sources;
111 COMMIT;
112
113 EXCEPTION
114 when others then
115
116 ROLLBACK TO start_push_to_source;
117 close sources;
118 DBMS_SQL.close_cursor(cid);
119 edw_owb_collection_util.write_to_log_file('push_to_source: '||l_progress|| ' : '||SQLERRM);
120 raise;
121
122 END pushToSource;
123
124 End OPI_UOM_WH_PUSH_PKG;