1 package body msd_AW_LOADER_PVT as
2 /* $Header: msdawloaderb.pls 120.0 2005/05/25 20:00:46 appldev noship $ */
3
4 m_ascii_nl number := 10;
5 m_curr_obj varchar2(32);
6
7 -------------------------------------------------------------------------------
8 -- CALL_AW - Calls an AW command and returns the result
9 --
10 -- IN: p_cmd (varchar2) - The command to execute
11 -- OUT: The result of the command
12 -------------------------------------------------------------------------------
13 function CALL_AW(p_cmd in varchar2) return varchar2
14 is
15 l_return varchar2(4000);
16 l_pos number := 1;
17 begin
18 l_return := dbms_aw.interp (p_cmd);
19
20 if (l_return is null or length (l_return) = 0) then
21 return l_return;
22 end if;
23 loop
24 if (ascii (substr (l_return, l_pos)) = 10) then
25 l_pos := l_pos + 1;
26 else
27 exit;
28 end if;
29 end loop;
30 return substr (l_return, l_pos);
31 end CALL_AW;
32
33 -------------------------------------------------------------------------------
34 -- ATTACH_AW: Attaches the AW rw
35 --
36 -- IN: p_schema (varchar2) - Schema of the AW to attach
37 -- p_aw (varchar2) - Name of the AW
38 -------------------------------------------------------------------------------
39 procedure ATTACH_AW(p_schema in varchar2,
40 p_aw in varchar2)
41 is
42 l_ret varchar2(16);
43 l_aw varchar2(32) := p_schema||'.'||p_aw;
44 begin
45 --
46 -- If this function is called from development, then the AW is attached
47 -- under ALIAS of p_aw. If from ADPATCH, then this AW is not attached.
48 -- Note that, in development, we are in as msd, whereas in ADPATCH, we
49 -- enter as APPS.
50 --
51 if (upper (CALL_AW('shw aw (attached '''||p_aw||''')')) = 'YES' and
52 upper(CALL_AW('shw aw (rw '''||p_aw||''')')) = 'NO') then
53 dbms_aw.execute ('aw detach '||p_aw);
54 end if;
55 if (upper (CALL_AW('shw aw (attached '''||p_aw||''')')) <> 'YES') then
56 dbms_aw.execute ('aw attach '||l_aw||' rw wait');
57 end if;
58 end ATTACH_AW;
59
60 -------------------------------------------------------------------------------
61 -- CREATE_OBJECT - Creates the object of specified name, type and attribute
62 --
63 -- IN: p_object_name (varchar2) - Name of the object to create
64 -- p_object_type (varchar2) - Type of the object (ie. VARIABLE)
65 -- p_object_attributes (varchar2) - Attributes of object (ie. <DIM1, DIM2>)
66 -- p_object_ld (varchar2) - The LD (description) of the object
67 --
68 -------------------------------------------------------------------------------
69 procedure CREATE_OBJECT(p_object_name in varchar2,
70 p_object_type in varchar2,
71 p_object_attributes in varchar2,
72 p_object_ld in varchar2)
73 is
74 l_ret varchar2(16);
75 begin
76 if (upper (CALL_AW('shw exists ('''||p_object_name||''')')) = 'YES') then
77 if (p_object_type = 'DIMENSION') then
78 dbms_aw.execute ('cns '||p_object_name);
79 dbms_aw.execute ('property delete all');
80 else
81 dbms_aw.execute ('dlt '||p_object_name);
82 dbms_aw.execute ('dfn '||p_object_name||' '||p_object_type||' '||
83 p_object_attributes);
84 end if;
85 else
86 dbms_aw.execute ('dfn '||p_object_name||' '||p_object_type||' '||
87 p_object_attributes);
88 end if;
89 dbms_aw.execute ('ld '||p_object_ld);
90 m_curr_obj := p_object_name;
91 end CREATE_OBJECT;
92
93 -------------------------------------------------------------------------------
94 -- LOAD_DIMENSION_INT
95 --
96 -- IN: p_dimension_size (number) - The size of the integer dimension
97 -------------------------------------------------------------------------------
98 procedure LOAD_DIMENSION_INT(p_dimension_size in number)
99 is
100 l_size number;
101 l_diff number;
102 begin
103 l_size := to_number(CALL_AW('shw obj(dimmax '''||m_curr_obj||''')'));
104 if (l_size < p_dimension_size) then
105 dbms_aw.execute('mnt '||m_curr_obj||' add '||
106 (p_dimension_size - l_size));
107 elsif (l_size > p_dimension_size) then
108 dbms_aw.execute('mnt '||m_curr_obj||' delete last '||
109 (l_size - p_dimension_size));
110 end if;
111 end LOAD_DIMENSION_INT;
112
113 -------------------------------------------------------------------------------
114 -- LOAD_DIMENSION_VALUES - Loads values of a text dimension
115 --
116 -- IN: p_dimension_values - Hash of index/value pairs
117 --
118 -------------------------------------------------------------------------------
119 procedure LOAD_DIMENSION_VALUES(p_dimension_values in DIM_VALUES)
120 is
121 i number := 0;
122 l_value varchar2(32);
123 l_size number;
124 begin
125 loop
126 if not p_dimension_values.exists(i) then
127 exit;
128 end if;
129 l_value := p_dimension_values(i);
130
131 --
132 -- If not a conjoint, add sorrounding parenthesis:
133 --
134 if (instr (l_value, '<') <> 1) then
135 l_value := ''''||l_value||'''';
136 end if;
137
138 if (upper (CALL_AW ('shw isvalue ('||m_curr_obj||' '||l_value||')'))
139 = 'NO') then
140 dbms_aw.execute ('mnt '||m_curr_obj||' add '||l_value||'');
141 end if;
142 dbms_aw.execute('mnt '||m_curr_obj||' move '||l_value||' after '||i);
143 i := i + 1;
144 end loop;
145
146 --
147 -- Remove extra entries:
148 --
149 l_size := to_number(CALL_AW ('shw obj(dimmax '''||m_curr_obj||''')'));
150 if (l_size > i) then
151 dbms_aw.execute ('lmt '||m_curr_obj||' to last '||(l_size - i));
152 dbms_aw.execute ('mnt '||m_curr_obj||' delete values('||m_curr_obj||')');
153 end if;
154
155 end LOAD_DIMENSION_VALUES;
156
157 -------------------------------------------------------------------------------
158 -- LOAD_FORMULA - Builds a formula
159 --
160 -- IN: p_formula - The formula body
161 --
162 -------------------------------------------------------------------------------
163 procedure LOAD_FORMULA(p_formula in varchar2)
164 is
165 begin
166 dbms_aw.execute ('cns '||m_curr_obj);
167 dbms_aw.execute('eq '||p_formula);
168 end LOAD_FORMULA;
169
170 -------------------------------------------------------------------------------
171 -- LOAD_MODEL - Builds a model
172 --
173 -- IN: p_model - The model body
174 --
175 -------------------------------------------------------------------------------
176 procedure LOAD_MODEL(p_model in varchar2)
177 is
178 begin
179 dbms_aw.execute ('cns '||m_curr_obj);
180 dbms_aw.execute('model;'||p_model||';end');
181 end LOAD_MODEL;
182
183 -------------------------------------------------------------------------------
184 -- LOAD_PROGRAM - Builds a program
185 --
186 -- IN: p_program - The program body
187 --
188 -------------------------------------------------------------------------------
189 procedure LOAD_PROGRAM(p_program in CLOB)
190 is
191 templob CLOB := 'program;';
192 begin
193 dbms_aw.execute ('cns '||m_curr_obj);
194 dbms_lob.append(templob, p_program);
195 dbms_lob.append(templob, ';end');
196 templob := dbms_aw.interpclob(templob);
197 end LOAD_PROGRAM;
198
199 -------------------------------------------------------------------------------
200 -- LOAD_PROPERTIES - Loads the properties of an object
201 --
202 -- IN: p_properties - Hash of property index/value pairs
203 --
204 -------------------------------------------------------------------------------
205 procedure LOAD_PROPERTIES(p_properties in PROP_VALUES)
206 is
207 l_key varchar2(32);
208 begin
209 dbms_aw.execute ('cns '||m_curr_obj);
210 l_key := p_properties.FIRST;
211 while (l_key is not null)
212 loop
213 dbms_aw.execute('prp '''||l_key||''' '||p_properties(l_key));
214 l_key := p_properties.NEXT(l_key);
215 end loop;
216 end LOAD_PROPERTIES;
217
218 -------------------------------------------------------------------------------
219 -- LOAD_VALUESET - Loads the values of a valueset object
220 --
221 -- IN: p_dim_values - Hash of dimension values
222 --
223 -------------------------------------------------------------------------------
224 procedure LOAD_VALUESET(p_dimension_values in DIM_VALUES)
225 is
226 i number := 0;
227 l_value varchar2(32);
228 l_size number;
229 begin
230 dbms_aw.execute('lmt '||m_curr_obj||' to null');
231 loop
232 if not p_dimension_values.exists(i) then
233 exit;
234 end if;
235 l_value := p_dimension_values(i);
236 dbms_aw.execute('lmt '||m_curr_obj||' add '||l_value);
237 i := i + 1;
238 end loop;
239
240 end LOAD_VALUESET;
241
242 -------------------------------------------------------------------------------
243 -- UPDATE_AW - Updates the AW
244 --
245 -------------------------------------------------------------------------------
246 procedure UPDATE_AW
247 is
248 begin
249 dbms_aw.execute ('upd');
250 end UPDATE_AW;
251
252 end msd_AW_LOADER_PVT;