[Home] [Help]
PACKAGE BODY: APPS.WSH_ITM_QUERY_CUSTOM
Source
1 PACKAGE BODY WSH_ITM_QUERY_CUSTOM AS
2 /*$Header: WSHITQCB.pls 115.1 2003/12/04 11:27:06 shravisa noship $ */
3
4 --Private method for searching with Condn String
5 --
6 G_PKG_NAME CONSTANT VARCHAR2(50) := 'WSH_ITM_QUERY_CUSTOM';
7 --
8 PROCEDURE FIND_INDEX(p_Table IN g_CondnValTableType,
9 p_FilerCond IN VARCHAR2,
10 p_index OUT NOCOPY NUMBER) IS
11 --
12 l_debug_on BOOLEAN;
13 --
14 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'FIND_INDEX';
15 BEGIN
16 --
17 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
18 --
19 IF l_debug_on IS NULL
20 THEN
21 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
22 END IF;
23 --
24 --
25 -- Debug Statements
26 --
27 IF l_debug_on THEN
28 WSH_DEBUG_SV.push(l_module_name);
29 --
30 WSH_DEBUG_SV.log(l_module_name,'P_FILERCOND',P_FILERCOND);
31 END IF;
32 --
33 FOR i in 1..p_Table.count
34 LOOP
35 IF p_Table(i).g_Condn_Qry = p_FilerCond THEN
36 p_index := i;
37 EXIT;
38 END IF;
39 END LOOP;
40 --
41 -- Debug Statements
42 --
43 IF l_debug_on THEN
44 WSH_DEBUG_SV.pop(l_module_name);
45 END IF;
46 --
47 END;
48
49 --Private method for edit/add element in Table with Value Object, Value Type
50 PROCEDURE SET_DATA_INDEX(p_Table IN OUT NOCOPY g_CondnValTableType,
51 p_index IN NUMBER,
52 p_NewFilerCond IN VARCHAR2,
53 p_NewValue IN g_ValueTableType,
54 p_NewValueType IN VARCHAR2) IS
55 --
56 l_debug_on BOOLEAN;
57 --
58 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'SET_DATA_INDEX';
59 --
60 BEGIN
61 --
62 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
63 --
64 IF l_debug_on IS NULL
65 THEN
66 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
67 END IF;
68 --
69 --
70 -- Debug Statements
71 --
72 IF l_debug_on THEN
73 WSH_DEBUG_SV.push(l_module_name);
74 --
75 WSH_DEBUG_SV.log(l_module_name,'P_INDEX',P_INDEX);
76 WSH_DEBUG_SV.log(l_module_name,'P_NEWFILERCOND',P_NEWFILERCOND);
77 WSH_DEBUG_SV.log(l_module_name,'P_NEWVALUETYPE',P_NEWVALUETYPE);
78 END IF;
79 --
80 p_Table(p_index).g_Condn_Qry := p_NewFilerCond;
81 --Modified by AJPRABHA for 8.1 Compatibility
82 --p_Table(p_index).g_Val_Table := p_NewValue;
83 p_Table(p_index).g_number_val := p_NewValue(1).g_number_val;
84 p_Table(p_index).g_varchar_val := p_NewValue(1).g_varchar_val;
85 p_Table(p_index).g_date_val := p_NewValue(1).g_date_val;
86 p_Table(p_index).g_Bind_Literal := p_NewValue(1).g_Bind_Literal;
87
88 p_Table(p_index).g_Value_Type := p_NewValueType;
89 --
90 -- Debug Statements
91 --
92 IF l_debug_on THEN
93 WSH_DEBUG_SV.pop(l_module_name);
94 END IF;
95 --
96 END;
97
98 --Add Condition, with values
99 /*===========================================================================+
100 | PROCEDURE |
101 | ADD_CONDITION |
102 | |
103 | DESCRIPTION |
104 | This procedure adds the filter condition p_FilterCond |
105 | to the PL/SQL table p_Table. p_Vale is the list of bind |
106 | variables present in the condition p_FilterCond |
107 | p_ValueType indicates the datatype of the bind variables |
108 +===========================================================================*/
109
110
111 PROCEDURE ADD_CONDITION(p_Table IN OUT NOCOPY g_CondnValTableType,
112 p_FilerCond IN VARCHAR2,
113 p_Value IN g_ValueTableType,
114 p_Value_Type IN VARCHAR2) IS
115 l_index NUMBER;
116 --
117 l_debug_on BOOLEAN;
118 --
119 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'ADD_CONDITION';
120 --
121 BEGIN
122 --Get length of the table and add in the end.
123 --
124 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
125 --
126 IF l_debug_on IS NULL
127 THEN
128 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
129 END IF;
130 --
131 --
132 -- Debug Statements
133 --
134 IF l_debug_on THEN
135 WSH_DEBUG_SV.push(l_module_name);
136 --
137 WSH_DEBUG_SV.log(l_module_name,'P_FILERCOND',P_FILERCOND);
138 WSH_DEBUG_SV.log(l_module_name,'P_VALUE_TYPE',P_VALUE_TYPE);
139 END IF;
140 --
141 l_index := p_Table.count + 1;
142 --Add Data to index
143 SET_DATA_INDEX(p_Table, l_index, p_FilerCond, p_Value, p_Value_Type);
144
145 --
146 -- Debug Statements
147 --
148 IF l_debug_on THEN
149 WSH_DEBUG_SV.pop(l_module_name);
150 END IF;
151 --
152 END ADD_CONDITION;
153
154 --Add Condition, with values
155 /*===========================================================================+
156 | PROCEDURE |
157 | ADD_CONDITION |
158 | |
159 | DESCRIPTION |
160 | This procedure adds the filter condition p_FilterCond |
161 | to the PL/SQL table p_Table. This procedure is to be |
162 | called if the filter condition does not have any bind |
163 | bind variables. |
164 +===========================================================================*/
165
166
167 --Add condition without values
168 PROCEDURE ADD_CONDITION(p_Table IN OUT NOCOPY g_CondnValTableType,
169 p_FilerCond IN VARCHAR2) IS
170 l_index NUMBER;
171 --
172 l_debug_on BOOLEAN;
173 --
174 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'ADD_CONDITION';
175 --
176 BEGIN
177 --Get length of the table and add in the end.
178 --
179 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
180 --
181 IF l_debug_on IS NULL
182 THEN
183 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
184 END IF;
185 --
186 --
187 -- Debug Statements
188 --
189 IF l_debug_on THEN
190 WSH_DEBUG_SV.push(l_module_name);
191 --
192 WSH_DEBUG_SV.log(l_module_name,'P_FILERCOND',P_FILERCOND);
193 END IF;
194 --
195 l_index := p_Table.count + 1;
196 --Add Data to index
197 p_Table(l_index).g_Condn_Qry := p_FilerCond;
198 --
199 -- Debug Statements
200 --
201 IF l_debug_on THEN
202 WSH_DEBUG_SV.pop(l_module_name);
203 END IF;
204 --
205 END ADD_CONDITION;
206
207 --
208
209
210
211 PROCEDURE DEL_CONDITION(p_Table IN OUT NOCOPY g_CondnValTableType,
212 p_FilerCond IN VARCHAR2) IS
213 l_FoundIndex NUMBER;
214 p_NewTable g_CondnValTableType;
215 --
216 l_debug_on BOOLEAN;
217 --
218 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'DEL_CONDITION';
219 --
220 BEGIN
221 --Looping thru the Table to get the index of search result
222 --
223 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
224 --
225 IF l_debug_on IS NULL
226 THEN
227 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
228 END IF;
229 --
230 --
231 -- Debug Statements
232 --
233 IF l_debug_on THEN
234 WSH_DEBUG_SV.push(l_module_name);
235 --
236 WSH_DEBUG_SV.log(l_module_name,'P_FILERCOND',P_FILERCOND);
237 END IF;
238 --
239 FIND_INDEX(p_Table, p_FilerCond, l_FoundIndex);
240
241 --Delete the Rec
242 FOR i in 1..p_Table.count-1
243 LOOP
244 IF i<l_FoundIndex THEN
245 p_NewTable(i) := p_Table(i);
246 ELSE
247 p_NewTable(i) := p_Table(i+1);
248 END IF;
249 END LOOP;
250 p_Table := p_NewTable;
251 --
252 -- Debug Statements
253 --
254 IF l_debug_on THEN
255 WSH_DEBUG_SV.pop(l_module_name);
256 END IF;
257 --
258 END;
259
260
261
262
263 PROCEDURE EDIT_CONDITION(p_Table IN OUT NOCOPY g_CondnValTableType,
264 p_OldFilerCond IN VARCHAR2,
265 p_NewFilerCond IN VARCHAR2,
266 p_NewValue IN g_ValueTableType,
267 p_NewValueType IN VARCHAR2) IS
268 l_FoundIndex NUMBER;
269 --
270 l_debug_on BOOLEAN;
271 --
272 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'EDIT_CONDITION';
273 --
274 BEGIN
275 --Looping thru the Table to get the index of search result
276 --
277 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
278 --
279 IF l_debug_on IS NULL
280 THEN
281 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
282 END IF;
283 --
284 --
285 -- Debug Statements
286 --
287 IF l_debug_on THEN
288 WSH_DEBUG_SV.push(l_module_name);
289 --
290 WSH_DEBUG_SV.log(l_module_name,'P_OLDFILERCOND',P_OLDFILERCOND);
291 WSH_DEBUG_SV.log(l_module_name,'P_NEWFILERCOND',P_NEWFILERCOND);
292 WSH_DEBUG_SV.log(l_module_name,'P_NEWVALUETYPE',P_NEWVALUETYPE);
293 END IF;
294 --
295 FIND_INDEX(p_Table, p_OldFilerCond, l_FoundIndex);
296
297 --Modifying the Found Record
298 SET_DATA_INDEX(p_Table, l_FoundIndex, p_NewFilerCond, p_NewValue, p_NewValueType);
299 --
300 -- Debug Statements
301 --
302 IF l_debug_on THEN
303 WSH_DEBUG_SV.pop(l_module_name);
304 END IF;
305 --
306 END;
307
308
309 /*===========================================================================+
310 | PROCEDURE |
311 | BIND_VALUES |
312 | |
313 | DESCRIPTION |
314 | This procedure does the binding of the values to the bind |
315 | variables of the cursor p_CursorID using the data |
316 | in the Cond table p_Table |
317 | |
318 +===========================================================================*/
319
320
321 PROCEDURE BIND_VALUES (p_Table IN g_CondnValTableType,
322 p_CursorID IN NUMBER) IS
323 --
324 l_debug_on BOOLEAN;
325 --
326 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'BIND_VALUES';
327 --
328 BEGIN
329 --Looping thru the Table to get the index of search result
330 --
331 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
332 --
333 IF l_debug_on IS NULL
334 THEN
335 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
336 END IF;
337 --
338 --
339 -- Debug Statements
340 --
341 IF l_debug_on THEN
342 WSH_DEBUG_SV.push(l_module_name);
343 --
344 WSH_DEBUG_SV.log(l_module_name,'p_cursorId',p_cursorId);
345 END IF;
346 --
347
348
349 FOR i IN 1..p_Table.COUNT
350 LOOP
351 IF p_Table(i).g_Value_Type = 'NUMBER' THEN
352 IF l_debug_on THEN
353 WSH_DEBUG_SV.LOG (l_module_name, 'Binding literal ', p_Table(i).g_Bind_Literal, WSH_DEBUG_SV.C_STMT_LEVEL);
354 WSH_DEBUG_SV.LOG (l_module_name, 'Binding Value ', p_Table(i).g_number_val, WSH_DEBUG_SV.C_STMT_LEVEL);
355 END IF;
356 DBMS_SQL.BIND_VARIABLE(p_CursorID,p_Table(i).g_Bind_Literal, p_Table(i).g_number_val);
357 ELSIF p_Table(i).g_Value_Type = 'VARCHAR' THEN
358 IF l_debug_on THEN
359 WSH_DEBUG_SV.LOG (l_module_name, 'Binding literal ', p_Table(i).g_Bind_Literal, WSH_DEBUG_SV.C_STMT_LEVEL);
360 WSH_DEBUG_SV.LOG (l_module_name, 'Binding Value ', p_Table(i).g_varchar_val, WSH_DEBUG_SV.C_STMT_LEVEL);
361 END IF;
362 DBMS_SQL.BIND_VARIABLE(p_CursorID,p_Table(i).g_Bind_Literal, p_Table(i).g_varchar_val);
363 ELSIF p_Table(i).g_Value_Type = 'DATE' THEN
364 IF l_debug_on THEN
365 WSH_DEBUG_SV.LOG (l_module_name, 'Binding literal ', p_Table(i).g_Bind_Literal, WSH_DEBUG_SV.C_STMT_LEVEL);
366 WSH_DEBUG_SV.LOG (l_module_name, 'Binding Value ', p_Table(i).g_date_val, WSH_DEBUG_SV.C_STMT_LEVEL);
367 END IF;
368 DBMS_SQL.BIND_VARIABLE(p_CursorID,p_Table(i).g_Bind_Literal, p_Table(i).g_date_val);
369 END IF;
370 END LOOP;
371 END;
372 END;