[Home] [Help]
PACKAGE BODY: APPS.EDW_APPS_INT
Source
1 Package Body EDW_APPS_INT AS
2 /* $Header: EDWAPPSB.pls 115.7 2002/12/05 01:12:22 arsantha ship $ */
3
4
5 PROCEDURE registerSourceViews
6 (p_flex_view_name IN VARCHAR2,
7 p_generated_view_name IN VARCHAR2,
8 p_collection_view_name IN VARCHAR2,
9 p_Interface_table_name IN VARCHAR2,
10 p_object_name IN VARCHAR2,
11 p_level_name IN VARCHAR2,
12 p_version IN VARCHAR2) IS
13
14 v_cursor_id NUMBER;
15 v_ret_code NUMBER;
16 v_sql_stmt VARCHAR2(1000);
17 v_source_lang VARCHAR2(40);
18 cid NUMBER;
19 l_count NUMBER;
20 BEGIN
21
22 IF (p_version is null) then
23 return;
24 END IF;
25
26 IF (upper(p_version) <> 'ALL') THEN
27 select count(*) into l_count from edw_source_views
28 where upper(object_name) = upper(p_object_name) and
29 nvl(upper(level_name), '000') = nvl(upper(p_level_name), '000') and
30 upper(version) = upper(p_version) ;
31
32 IF (l_count <> 0 ) THEN
33 RETURN;
34 END IF;
35 ELSIF (upper(p_version) = 'ALL') THEN /* insert all versions */
36 registerSourceViews(p_flex_view_name, p_generated_view_name, p_collection_view_name,
37 p_Interface_table_name, p_object_name, p_level_name, '10.7');
38 registerSourceViews(p_flex_view_name, p_generated_view_name, p_collection_view_name,
39 p_Interface_table_name, p_object_name, p_level_name, '11.0');
40 registerSourceViews(p_flex_view_name, p_generated_view_name, p_collection_view_name,
41 p_Interface_table_name, p_object_name, p_level_name, '11i');
42 END IF;
43
44
45 v_sql_stmt := 'INSERT INTO EDW_SOURCE_VIEWS ' ||
46 '(flex_view_name, generated_view_name, collection_view_name,
47 Interface_table_name, object_name, level_name, version,
48 last_update_date, last_updated_by, last_update_login,
49 created_by, creation_date)
50 values (:xflex, :xgen, :xcoll, :xit, :xobj, :xlvl, :xver, sysdate,
51 0, 1, 1, sysdate) ';
52 v_cursor_id := dbms_sql.open_cursor;
53 dbms_sql.parse(v_cursor_id, v_sql_stmt, DBMS_SQL.V7);
54 dbms_sql.bind_variable(v_cursor_id, ':xflex', upper(p_flex_view_name));
55 dbms_sql.bind_variable(v_cursor_id, ':xgen', upper(p_generated_view_name));
56 dbms_sql.bind_variable(v_cursor_id, ':xcoll', upper(p_collection_view_name));
57 dbms_sql.bind_variable(v_cursor_id, ':xit', upper(p_Interface_table_name));
58 dbms_sql.bind_variable(v_cursor_id, ':xobj', upper(p_object_name));
59 dbms_sql.bind_variable(v_cursor_id, ':xlvl', upper(p_level_name));
60 dbms_sql.bind_variable(v_cursor_id, ':xver', p_version);
61
62 v_ret_code := dbms_sql.execute(v_cursor_id);
63 dbms_sql.close_cursor(v_cursor_id);
64
65
66 EXCEPTION
67 WHEN others THEN
68 raise;
69 END registerSourceViews;
70
71 Procedure removeSourceViews(p_object_name IN VARCHAR2, p_version IN VARCHAR2) IS
72 BEGIN
73
74 IF (p_version <> 'ALL') THEN
75 DELETE FROM edw_source_views
76 WHERE upper(object_name) = upper(p_object_name)
77 AND upper(version) = upper(p_version);
78 ELSE
79 DELETE FROM edw_source_views
80 WHERE upper(object_name) = upper(p_object_name);
81 END IF;
82
83 EXCEPTION
84 WHEN no_data_found THEN
85 null;
86 WHEN others then
87 raise;
88 END removeSourceViews;
89
90 Procedure removeFlexAssignments(p_object_name IN VARCHAR2, p_version IN VARCHAR2) IS
91 BEGIN
92
93 IF (p_version <> 'ALL') THEN
94 delete from edw_sv_flex_assignments
95 where upper(object_name) = upper(p_object_name)
96 and upper(version) = upper(p_version);
97 ELSE
98 delete from edw_sv_flex_assignments
99 where upper(object_name) = upper(p_object_name);
100 END IF;
101 EXCEPTION
102 WHEN no_data_found THEN
103 null;
104
105 WHEN others THEN
106 raise;
107 END removeFlexAssignments;
108
109
110 PROCEDURE registerFlexAssignments
111 (
112 p_object_name IN VARCHAR2,
113 p_flex_view_name IN VARCHAR2,
114 p_flex_field_code IN VARCHAR2,
115 p_flex_field_prefix IN VARCHAR2,
116 p_application_id IN NUMBER,
117 p_application_short_name IN VARCHAR2,
118 p_flex_field_type IN VARCHAR2,
119 p_flex_field_name IN VARCHAR2,
120 p_version IN VARCHAR2)
121 IS
122 v_cursor_id NUMBER;
123 v_ret_code NUMBER;
124 v_sql_stmt VARCHAR2(1000);
125 v_source_lang VARCHAR2(40);
126 cid NUMBER;
127 l_count NUMBER;
128 BEGIN
129
130 IF (p_version is null) then
131 return;
132 END IF;
133
134 IF (upper(p_version) <> 'ALL') THEN
135 select count(*) into l_count from edw_sv_flex_assignments
136 where upper(object_name) = upper(p_object_name) and
137 version = p_version and upper(flex_field_code)=upper(p_flex_field_code)
138 and upper(flex_field_prefix) = upper(p_flex_field_prefix)
139 and upper(flex_view_name) = upper(p_flex_view_name);
140
141 IF (l_count <> 0 ) THEN
142 RETURN;
143 END IF;
144 ELSIF (upper(p_version) = 'ALL') THEN /* insert all versions */
145 registerFlexAssignments ( p_object_name, p_flex_view_name, p_flex_field_code,
146 p_flex_field_prefix, p_application_id, p_application_short_name ,
147 p_flex_field_type, p_flex_field_name, '10.7' );
148 registerFlexAssignments ( p_object_name, p_flex_view_name, p_flex_field_code,
149 p_flex_field_prefix, p_application_id, p_application_short_name ,
150 p_flex_field_type, p_flex_field_name, '11.0' );
151 registerFlexAssignments ( p_object_name, p_flex_view_name, p_flex_field_code,
152 p_flex_field_prefix, p_application_id, p_application_short_name ,
153 p_flex_field_type, p_flex_field_name, '11i' );
154 END IF;
155
156 v_sql_stmt := 'INSERT INTO EDW_SV_FLEX_ASSIGNMENTS ' ||
157 '(object_name, flex_view_name, flex_field_code, flex_field_prefix,
158 application_id, application_short_name, flex_field_type, flex_field_name,version,
159 last_update_date, last_updated_by, last_update_login, created_by, creation_date)
160 values (:xobject, :xflexview, :xflexcode, :xprefix,
161 :xappid, :xappname, :xflextype, :xflexname, :xversion,
162 sysdate, 1, 1, 1, sysdate) ';
163 v_cursor_id := dbms_sql.open_cursor;
164 dbms_sql.parse(v_cursor_id, v_sql_stmt, DBMS_SQL.V7);
165 dbms_sql.bind_variable(v_cursor_id, ':xobject', upper(p_object_name));
166 dbms_sql.bind_variable(v_cursor_id, ':xflexview', upper(p_flex_view_name));
167 dbms_sql.bind_variable(v_cursor_id, ':xflexcode', p_flex_field_code);
168 dbms_sql.bind_variable(v_cursor_id, ':xprefix', p_flex_field_prefix);
169 dbms_sql.bind_variable(v_cursor_id, ':xappid', p_application_id);
170 dbms_sql.bind_variable(v_cursor_id, ':xappname', p_application_short_name);
171 dbms_sql.bind_variable(v_cursor_id, ':xflextype', p_flex_field_type);
172 dbms_sql.bind_variable(v_cursor_id, ':xflexname', p_flex_field_name);
173 dbms_sql.bind_variable(v_cursor_id, ':xversion', p_version);
174
175
176 v_ret_code := dbms_sql.execute(v_cursor_id);
177 dbms_sql.close_cursor(v_cursor_id);
178
179 EXCEPTION
180 WHEN others THEN
181 raise;
182 END registerFlexAssignments;
183
184 END EDW_APPS_INT;