1 package body ZPB_AW_LOADER_PVT as
2 /* $Header: zpbawloader.plb 120.0.12010.2 2005/12/23 06:15:55 appldev noship $ */
3
4 m_ascii_nl constant number := ascii(fnd_global.local_chr(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 begin
16 return dbms_lob.substr(dbms_aw.interp (p_cmd));
17 end CALL_AW;
18
19 -------------------------------------------------------------------------------
20 -- CALL_BOOL
21 --
22 -- Wrapper around the call the AW with boolean (yes/no) output expected.
23 -- Will handle conversion within the NLS_LANGUAGE setting (Bug 4058390).
24 --
25 -- IN: p_cmd (varchar2) - The AW boolean command to execute
26 -- OUT: boolean - The output of the the AW command
27 --
28 -------------------------------------------------------------------------------
29 function CALL_BOOL (p_cmd in varchar2)
30 return boolean is
31 begin
32 return (dbms_aw.interp (p_cmd) = dbms_aw.interp ('shw yes'));
33 end CALL_BOOL;
34
35 -------------------------------------------------------------------------------
36 -- ATTACH_AW: Attaches the AW rw
37 --
38 -- IN: p_app_name (varchar2) - The application short name
39 -- p_aw (varchar2) - Name of the AW
40 -------------------------------------------------------------------------------
41 procedure ATTACH_AW(p_app_name in varchar2,
42 p_aw in varchar2)
43 is
44 l_ret varchar2(16);
45 l_schema varchar2(16);
46 l_aw varchar2(32);
47 begin
48
49 select ORACLE_USERNAME
50 into l_schema
51 from FND_ORACLE_USERID a,
52 FND_APPLICATION b,
53 FND_PRODUCT_INSTALLATIONS c
54 where a.ORACLE_ID = c.ORACLE_ID
55 and c.APPLICATION_ID = b.APPLICATION_ID
56 and b.APPLICATION_SHORT_NAME = upper(p_app_name);
57
58 l_aw := l_schema||'.'||p_aw;
59
60 --
61 -- If this function is called from development, then the AW is attached
62 -- under ALIAS of p_aw. If from ADPATCH, then this AW is not attached.
63 -- Note that, in development, we are in as ZPB, whereas in ADPATCH, we
64 -- enter as APPS.
65 --
66 dbms_aw.execute ('awwaittime = 200');
67 if (CALL_BOOL('shw aw (attached '''||p_aw||''')') and
68 (not CALL_BOOL('shw aw (rw '''||p_aw||''')'))) then
69 dbms_aw.execute ('aw detach '||p_aw);
70 end if;
71 if (not CALL_BOOL('shw aw (attached '''||p_aw||''')')) then
72 dbms_aw.execute ('aw attach '||l_aw||' rw wait');
73 end if;
74 dbms_aw.execute ('commas = no');
75 end ATTACH_AW;
76
77 -------------------------------------------------------------------------------
78 -- CREATE_OBJECT - Creates the object of specified name, type and attribute
79 --
80 -- IN: p_object_name (varchar2) - Name of the object to create
81 -- p_object_type (varchar2) - Type of the object (ie. VARIABLE)
82 -- p_object_attributes (varchar2) - Attributes of object (ie. <DIM1, DIM2>)
83 -- p_object_ld (varchar2) - The LD (description) of the object
84 --
85 -------------------------------------------------------------------------------
86 procedure CREATE_OBJECT(p_object_name in varchar2,
87 p_object_type in varchar2,
88 p_object_attributes in varchar2,
89 p_object_ld in varchar2)
90 is
91 l_ret varchar2(16);
92 begin
93 if (CALL_BOOL('shw exists ('''||p_object_name||''')')) then
94 if (p_object_type = 'DIMENSION') then
95 dbms_aw.execute ('cns '||p_object_name);
96 dbms_aw.execute ('property delete all');
97 else
98 dbms_aw.execute ('dlt '||p_object_name);
99 dbms_aw.execute ('dfn '||p_object_name||' '||p_object_type||' '||
100 p_object_attributes);
101 end if;
102 else
103 dbms_aw.execute ('dfn '||p_object_name||' '||p_object_type||' '||
104 p_object_attributes);
105 end if;
106 dbms_aw.execute ('ld '||p_object_ld);
107 m_curr_obj := p_object_name;
108 end CREATE_OBJECT;
109
110 -------------------------------------------------------------------------------
111 -- DELETE_OBJECT - Deletes the specified object from the current AW
112
113 -- IN: p_object_name (varchar2) - Name of object to delete
114 --
115 -------------------------------------------------------------------------------
116 procedure DELETE_OBJECT(p_object_name in varchar2)
117 is
118 begin
119 if (CALL_BOOL('shw exists ('''||p_object_name||''')')) then
120 dbms_aw.execute('delete ' || p_object_name);
121 end if;
122 end DELETE_OBJECT;
123
124 -------------------------------------------------------------------------------
125 -- LOAD_DIMENSION_INT
126 --
127 -- IN: p_dimension_size (number) - The size of the integer dimension
128 -------------------------------------------------------------------------------
129 procedure LOAD_DIMENSION_INT(p_dimension_size in number)
130 is
131 l_size number;
132 l_diff number;
133 begin
134 l_size := to_number(CALL_AW('shw obj(dimmax '''||m_curr_obj||''')'));
135 if (l_size < p_dimension_size) then
136 dbms_aw.execute('mnt '||m_curr_obj||' add '||
137 (p_dimension_size - l_size));
138 elsif (l_size > p_dimension_size) then
139 dbms_aw.execute('mnt '||m_curr_obj||' delete last '||
140 (l_size - p_dimension_size));
141 end if;
142 end LOAD_DIMENSION_INT;
143
144 -------------------------------------------------------------------------------
145 -- LOAD_DIMENSION_VALUES - Loads values of a text dimension
146 --
147 -- IN: p_dimension_values - Hash of index/value pairs
148 --
149 -------------------------------------------------------------------------------
150 procedure LOAD_DIMENSION_VALUES(p_dimension_values in DIM_VALUES)
151 is
152 i number;
153 l_value varchar2(32);
154 l_size number;
155 begin
156 i := 0;
157 loop
158 if not p_dimension_values.exists(i) then
159 exit;
160 end if;
161 l_value := p_dimension_values(i);
162
163 --
164 -- If not a conjoint, add sorrounding parenthesis:
165 --
166 if (instr (l_value, '<') <> 1) then
167 l_value := ''''||l_value||'''';
168 end if;
169
170 if (not CALL_BOOL ('shw isvalue ('||m_curr_obj||' '||l_value||')')) then
171 dbms_aw.execute ('mnt '||m_curr_obj||' add '||l_value||'');
172 end if;
173 dbms_aw.execute('mnt '||m_curr_obj||' move '||l_value||' after '||i);
174 i := i + 1;
175 end loop;
176
177 --
178 -- Remove extra entries:
179 --
180 l_size := to_number(CALL_AW ('shw obj(dimmax '''||m_curr_obj||''')'));
181 if (l_size > i) then
182 dbms_aw.execute ('lmt '||m_curr_obj||' to last '||(l_size - i));
183 dbms_aw.execute ('mnt '||m_curr_obj||' delete values('||m_curr_obj||')');
184 end if;
185
186 end LOAD_DIMENSION_VALUES;
187
188 -------------------------------------------------------------------------------
189 -- LOAD_FORMULA - Builds a formula
190 --
191 -- IN: p_formula - The formula body
192 --
193 -------------------------------------------------------------------------------
194 procedure LOAD_FORMULA(p_formula in varchar2)
195 is
196 begin
197 dbms_aw.execute ('cns '||m_curr_obj);
198 dbms_aw.execute('eq '||p_formula);
199 end LOAD_FORMULA;
200
201 -------------------------------------------------------------------------------
202 -- LOAD_MODEL - Builds a model
203 --
204 -- IN: p_model - The model body
205 --
206 -------------------------------------------------------------------------------
207 procedure LOAD_MODEL(p_model in varchar2)
208 is
209 begin
210 dbms_aw.execute ('cns '||m_curr_obj);
211 dbms_aw.execute('model;'||p_model||';end');
212 end LOAD_MODEL;
213
214 -------------------------------------------------------------------------------
215 -- LOAD_PROGRAM - Builds a program
216 --
217 -- IN: p_program - The program body
218 --
219 -------------------------------------------------------------------------------
220 procedure LOAD_PROGRAM(p_program in CLOB)
221 is
222 templob CLOB;
223 begin
224 templob := 'program;';
225 dbms_aw.execute ('cns '||m_curr_obj);
226 dbms_lob.append(templob, p_program);
227 dbms_lob.append(templob, ';end');
228 templob := dbms_aw.interpclob(templob);
229 end LOAD_PROGRAM;
230
231 -------------------------------------------------------------------------------
232 -- LOAD_PROPERTIES - Loads the properties of an object
233 --
234 -- IN: p_properties - Hash of property index/value pairs
235 --
236 -------------------------------------------------------------------------------
237 procedure LOAD_PROPERTIES(p_properties in PROP_VALUES)
238 is
239 l_key varchar2(32);
240 begin
241 dbms_aw.execute ('cns '||m_curr_obj);
242 l_key := p_properties.FIRST;
243 while (l_key is not null)
244 loop
245 dbms_aw.execute('prp '''||l_key||''' '||p_properties(l_key));
246 l_key := p_properties.NEXT(l_key);
247 end loop;
248 end LOAD_PROPERTIES;
249
250 -------------------------------------------------------------------------------
251 -- LOAD_VALUESET - Loads the values of a valueset object
252 --
253 -- IN: p_dim_values - Hash of dimension values
254 --
255 -------------------------------------------------------------------------------
256 procedure LOAD_VALUESET(p_dimension_values in DIM_VALUES)
257 is
258 i number;
259 l_value varchar2(32);
260 l_size number;
261 begin
262 dbms_aw.execute('lmt '||m_curr_obj||' to null');
263 i := 0;
264 loop
265 if not p_dimension_values.exists(i) then
266 exit;
267 end if;
268 l_value := p_dimension_values(i);
269 dbms_aw.execute('lmt '||m_curr_obj||' add '||l_value);
270 i := i + 1;
271 end loop;
272
273 end LOAD_VALUESET;
274
275 -------------------------------------------------------------------------------
276 -- UPDATE_AW - Updates the AW
277 --
278 -------------------------------------------------------------------------------
279 procedure UPDATE_AW
280 is
281 begin
282 dbms_aw.execute ('upd');
283 end UPDATE_AW;
284
285 end ZPB_AW_LOADER_PVT;
[Home] [Help]