eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.
PACKAGE BODY: APPS.QPR_AW_INTERFACE_PVT

Source


1 PACKAGE BODY QPR_AW_INTERFACE_PVT as
2 /* $Header: QPRAWINFCB.pls 120.0 2007/10/11 13:13:12 agbennet noship $ */
3 -- ATTACH_AW
4 procedure attach_aw
5 (p_aw_name IN varchar2, p_attach_mode IN varchar2, x_return_status OUT NOCOPY varchar2)
6 is
7 execution_string varchar2(100) ;
8 BEGIN
9 if( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
10   FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,'QPR_AW_INTERFACE_PVT.ATTACH_AW', 'Attach_aw procedure invoked' );
11 end if;
12 
13 execution_string := 'aw attach '||p_aw_name||' '||p_attach_mode||';';
14 -- Attach specified AW in multi mode .
15 dbms_aw.execute(execution_string);
16 
17 if( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
18     FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,'QPR_AW_INTERFACE_PVT.ATTACH_AW', 'DML Program Attach succeeded' );
19 end if;
20 
21 x_return_status := 'success';
22 
23 if( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
24     FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,'QPR_AW_INTERFACE_PVT.ATTACH_AW', 'Attach_aw was successful' );
25 end if;
26 
27 EXCEPTION
28 when others then
29 x_return_status := 'error';
30 if( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
31     FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,'QPR_AW_INTERFACE_PVT.ATTACH_AW', 'Attach_aw failed' );
32 end if;
33 
34 END attach_aw;
35 
36 -- DETACH_AW
37 procedure detach_aw
38 (p_aw_name IN varchar2,x_return_status OUT NOCOPY varchar2)
39 is
40 execution_string varchar2(100);
41 BEGIN
42 if( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
43     FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,'QPR_AW_INTERFACE_PVT.DETACH_AW', 'Detach_aw procedure invoked' );
44 end if;
45 execution_string := 'aw detach '||p_aw_name||';';
46 -- Detached specified AW
47 dbms_aw.execute(execution_string);
48 if( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
49     FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,'QPR_AW_INTERFACE_PVT.DETACH_AW', 'DML Program DETACH succeeded' );
50 end if;
51 x_return_status := 'success';
52 if( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
53     FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,'QPR_AW_INTERFACE_PVT.DETACH_AW', 'Detach_aw program succeeded' );
54 end if;
55 EXCEPTION
56 when others then
57 x_return_status := 'error';
58 if( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
59     FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,'QPR_AW_INTERFACE_PVT.DETACH_AW', 'Detach_aw program failed' );
60 end if;
61 END detach_aw;
62 -- writeback_aw
63 /*
64 procedure writeback_aw
65 (dimTable in QPR_DIM_TABLE,measTable in QPR_MEAS_TABLE,x_return_status OUT NOCOPY varchar2)
66 is
67 dimString varchar2(1000);
68 measString varchar2(1000);
69 dimCounter int := 1;
70 measCounter int := 1;
71 cell_id int := 1;
72 dimCheck boolean := false;
73 modMeasVal varchar2(100);
74 begin
75 if( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
76     FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,'QPR_AW_INTERFACE_PVT.WRITEBACK_AW', 'writeback_aw procedure invoked' );
77 end if;
78       << outer_loop >>
79       while (1=1)
80       loop
81         << dim_loop >>
82         while (dimTable(dimCounter).cell_id = cell_id)
83         loop
84         dimString := dimString || 'limit '||dimTable(dimCounter).dimName ||' to'''||dimTable(dimCounter).dimValue||''';' ;
85         dimCounter := dimCounter +1;
86         dimCheck := true;
87         if(dimCounter > dimTable.count) then
88           exit dim_loop;
89         end if;
90       end loop dim_loop;
91       << meas_loop >>
92       while(measTable(measCounter).cell_id = cell_id)
93       loop
94         modMeasVal := 'NA';
95 	if(measTable(measCounter).measValue =  oracleNull)THEN
96 	   measString := measString || measTable(measCounter).measName ||' = '|| modMeasVal || ';' ;
97         elsif  measTable(measCounter).measDataType = 'Text'  THEN
98             measString := measString || measTable(measCounter).measName ||' = '''|| measTable(measCounter).measValue || ''';' ;
99 
100 	    elsif measTable(measCounter).measDataType = 'Number' THEN
101         measString := measString || measTable(measCounter).measName ||' = '|| measTable(measCounter).measValue || ';' ;
102     end if;
103         measCounter := measCounter+1;
104       if(dimCheck <> false) then -- Check if dimension isn't null in which caseentire cube wud b set to above value.
105         dbms_aw.execute(dimString || measString);
106       end if;
107       if(measCounter > measTable.count) then
108           exit outer_loop;
109        end if;
110        measString := '';
111        end loop meas_loop;
112        cell_id :=cell_id+1;
113        dimString := '';
114        dimCheck := false;
115        end loop outer_loop;
116       dbms_aw.execute('ALLSTAT;');
117    x_return_status := 'success';
118 if( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
119     FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,'QPR_AW_INTERFACE_PVT.WRITEBACK_AW', 'WRITEBACK_AW procedure succeeded' );
120 end if;
121   EXCEPTION
122 when others then
123 x_return_status := 'error';
124 if( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
125     FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,'QPR_AW_INTERFACE_PVT.WRITEBACK_AW', 'WRITEBACK_AW procedure failed '||sqlcode||' '||sqlerrm);
126 end if;
127 
128 end writeback_aw;
129 */
130 /*
131 --Model Execution
132 procedure executeModel(modelName in varchar2, modelDimension in varchar2, modelMeasName in varchar2, modelExecScope in QPR_DIM_TABLE, writeBackMeas in QPR_MEAS_MET_TABLE,x_return_status OUT NOCOPY varchar2)
133 AS
134 cmdString varchar2(4000);
135 dimName varchar2(4000);
136 BEGIN
137 if( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
138     FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,'QPR_AW_INTERFACE_PVT.EXECUTEMODEL', 'EXECUTEMODEL procedure invoked' );
139 end if;
140 
141     dimName := '';
142 
143     for i in 1..modelExecScope.count loop
144        if dimName = modelExecScope(i).dimName then
145 	  cmdString := cmdString || 'limit '||modelExecScope(i).dimName ||' add '''||modelExecScope(i).dimValue||''';';
146        else
147 	  cmdString := cmdString || 'limit '||modelExecScope(i).dimName ||' to '''||modelExecScope(i).dimValue||''';';
148 	  dimName := modelExecScope(i).dimName;
149        end if;
150     end loop;
151     dbms_aw.execute(cmdString);
152 
153 if( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
154     FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,'QPR_AW_INTERFACE_PVT.EXECUTEMODEL', 'Scope set for the execution of the model' );
155 end if;
156 
157 
158 
159     cmdString := '';
160     cmdString := modelMeasName || '= 0.0;';
161     dbms_aw.execute(cmdString);
162 
163 
164     dbms_aw.execute('naskip2 = yes');
165     dbms_aw.execute(modelName ||' '||modelMeasName);
166     dbms_aw.execute('naskip2 = no');
167 
168 if( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
169     FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,'QPR_AW_INTERFACE_PVT.EXECUTEMODEL', 'Model '||modelName ||' Executed successfully ');
170 end if;
171 
172 
173 
174     cmdString := '';
175     for i in 1..writeBackMeas.count loop
176     cmdString := cmdString || 'limit '||modelDimension ||' to '||''''||writeBackMeas(i).measPPACode||''''||' ;';
177     cmdString := cmdString ||writeBackMeas(i).measId ||' = ' ||modelMeasName||' ; ';
178     end loop;
179     dbms_aw.execute(cmdString);
180 if( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
181     FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,'QPR_AW_INTERFACE_PVT.EXECUTEMODEL', 'Measure Values written back after the execution of model');
182 end if;
183 
184     cmdString := 'ALLSTAT;';
185     dbms_aw.execute(cmdString);
186     x_return_status := 'success';
187 if( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
188     FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,'QPR_AW_INTERFACE_PVT.EXECUTEMODEL', 'ExecuteModel Procedure successful');
189 end if;
190 
191 EXCEPTION
192     when others then
193     x_return_status := 'error';
194 if( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
195     FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,'QPR_AW_INTERFACE_PVT.EXECUTEMODEL', 'ExecuteModel Procedure failed '||sqlcode||' '||sqlerrm);
196 end if;
197 END;
198 
199 --Commit
200 procedure commitData(commitMeas in QPR_MEAS_MET_TABLE,x_return_status OUT NOCOPY varchar2)
201 AS
202 LOCK_ACQUIRE_FAILED1 EXCEPTION;
203 LOCK_ACQUIRE_FAILED2 EXCEPTION;
204 LOCK_ACQUIRE_FAILED3 EXCEPTION;
205 
206 PRAGMA EXCEPTION_INIT(LOCK_ACQUIRE_FAILED1, -37040);
207 PRAGMA EXCEPTION_INIT(LOCK_ACQUIRE_FAILED2, -37044);
208 PRAGMA EXCEPTION_INIT(LOCK_ACQUIRE_FAILED3, -37011);
209 cmdString varchar2(4000);
210 measString varchar2(4000);
211 BEGIN
212 if( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
213     FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,'QPR_AW_INTERFACE_PVT.COMMITDATA', 'Procedure CommitData Invoked');
214 end if;
215 
216 
217     cmdString := '';
218     measString := '';
219     for i in 1..commitMeas.count loop
220     if ( i = 1 ) then
221     	measString := commitMeas(i).measId;
222     else
223         measString := measString||','|| commitMeas(i).measid;
224     end if;
225     end loop;
226     cmdString := 'acquire '||measString||';';
227     dbms_aw.execute(cmdString);
228 if( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
229     FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,'QPR_AW_INTERFACE_PVT.COMMITDATA', 'Acquired the lock for the measures that are to be committed');
230 end if;
231 
232 
233 
234     cmdString := 'UPDATE;';
235     dbms_aw.execute(cmdString);
236 
237 if( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
238     FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,'QPR_AW_INTERFACE_PVT.COMMITDATA', 'Update of measures Successful');
239 end if;
240 
241 
242     cmdString := 'COMMIT;';
243     dbms_aw.execute(cmdString);
244 
245 if( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
246     FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,'QPR_AW_INTERFACE_PVT.COMMITDATA', 'Commit measure values Successful');
247 end if;
248 
249 
250     cmdString := 'Release '|| measString ||';';
251     dbms_aw.execute(cmdString);
252 
253 if( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
254     FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,'QPR_AW_INTERFACE_PVT.COMMITDATA', 'Released the lock acquired on the measures');
255 end if;
256 x_return_status := 'success';
257 if( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
258     FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,'QPR_AW_INTERFACE_PVT.COMMITDATA', 'CommitData Procedure Successful');
259 end if;
260 EXCEPTION
261     WHEN LOCK_ACQUIRE_FAILED1 THEN
262     x_return_status := 'LOCK_ACQUIRE_FAILED';
263 if( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
264     FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,'QPR_AW_INTERFACE_PVT.COMMITDATA', 'CommitData Procedure failed '||x_return_status||sqlcode||' '||sqlerrm);
265 end if;
266 
267  WHEN LOCK_ACQUIRE_FAILED2 THEN
268     x_return_status := 'LOCK_ACQUIRE_FAILED';
269 if( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
270     FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,'QPR_AW_INTERFACE_PVT.COMMITDATA', 'CommitData Procedure failed '||x_return_status||sqlcode||' '||sqlerrm);
271 end if;
272 
273  WHEN LOCK_ACQUIRE_FAILED3 THEN
274     x_return_status := 'LOCK_ACQUIRE_FAILED';
275 if( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
276     FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,'QPR_AW_INTERFACE_PVT.COMMITDATA', 'CommitData Procedure failed '||x_return_status||sqlcode||' '||sqlerrm);
277 end if;
278 
279  when others then
280     x_return_status := 'error';
281 if( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
282     FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,'QPR_AW_INTERFACE_PVT.COMMITDATA', 'CommitData Procedure failed '||x_return_status||sqlcode||' '||sqlerrm);
283 end if;
284 
285 END;
286 
287 procedure handleMeas(commitMeas in QPR_MEAS_MET_TABLE,dim in QPR_MEAS_MET_TABLE,acquire in varchar2,x_return_status OUT NOCOPY varchar2)
288 as
289 LOCK_ACQUIRE_FAILED1 EXCEPTION;
290 LOCK_ACQUIRE_FAILED2 EXCEPTION;
291 PRAGMA EXCEPTION_INIT(LOCK_ACQUIRE_FAILED1, -37040);
292 PRAGMA EXCEPTION_INIT(LOCK_ACQUIRE_FAILED2, -37011);
293 dimString varchar2(4000);
294 begin
295 if( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
296     FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,'QPR_AW_INTERFACE_PVT.handleMeas','Enter '||acquire);
297 end if;
298 
299    for i in 1..dim.count loop
300    if(i = 1) then
301       dimString := dim(i).measid;
302    else
303    dimString := dimString||','||dim(i).measid;
304    end if;
305 
306 end loop;
307 
308 
309     if (acquire = 'YES') then
310     for i in 1..commitMeas.count loop
311        dbms_aw.execute('acquire resync '||commitMeas(i).measid||' consistent with '||dimString);
312     end loop;
313     else
314        dbms_aw.execute('update');
315        dbms_aw.execute('commit');
316     for i in 1..commitMeas.count loop
317        dbms_aw.execute('release '||commitMeas(i).measid);
318     end loop;
319        end if;
320 
321 x_return_status := 'success';
322 
323 EXCEPTION
324     WHEN LOCK_ACQUIRE_FAILED1 THEN
325     x_return_status := 'LOCK_ACQUIRE_FAILED';
326 if( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
327     FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,'QPR_AW_INTERFACE_PVT.handleMeas', x_return_status||' '||sqlcode||' '||sqlerrm);
328 end if;
329 
330  WHEN LOCK_ACQUIRE_FAILED2 THEN
331     x_return_status := 'LOCK_ACQUIRE_FAILED';
332 if( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
333     FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,'QPR_AW_INTERFACE_PVT.handleMeas', x_return_status||' '||sqlcode||' '||sqlerrm);
334 end if;
335 
336  when others then
337     x_return_status := 'error';
338 if( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
339     FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,'QPR_AW_INTERFACE_PVT.handleMeas', x_return_status||' '||sqlcode||' '||sqlerrm);
340 end if;
341 end;*/
342 
343 END QPR_AW_INTERFACE_PVT;