[Home] [Help]
PACKAGE BODY: APPS.BNE_QUERY_UTILS
Source
1 PACKAGE BODY BNE_QUERY_UTILS AS
2 /* $Header: bnequeryutilsb.pls 120.2 2005/06/29 03:40:50 dvayro noship $ */
3
4 PROCEDURE VALIDATE_KEYS(P_APPLICATION_ID IN NUMBER,
5 P_QUERY_CODE IN VARCHAR2)
6 IS
7 BEGIN
8 IF NOT BNE_INTEGRATOR_UTILS.IS_VALID_APPL_ID(P_APPLICATION_ID) THEN
9 RAISE_APPLICATION_ERROR(-20000, TO_CHAR(P_APPLICATION_ID)||' is not a valid application id.');
10 END IF;
11 IF NOT BNE_INTEGRATOR_UTILS.IS_VALID_OBJECT_CODE(P_QUERY_CODE, 30) THEN
12 RAISE_APPLICATION_ERROR(-20001, P_QUERY_CODE||' is not a valid code. Use A-Z, 0-9, _ characters only, and the code must be shorter than 30 characters in length.');
13 END IF;
14 END VALIDATE_KEYS;
15
16 --------------------------------------------------------------------------------
17 -- PROCEDURE: CREATE_SIMPLE_QUERY --
18 -- --
19 -- DESCRIPTION: Procedure creates a Web ADI simple metadata query. --
20 -- No Commit is done. --
21 -- Throws application error 20000 if P_APPLICATION_ID is invalid--
22 -- Throws application error 20001 if P_QUERY_CODE is invalid --
23 -- Throws application error 20002 if P_DESCRIPTION_COL and --
24 -- P_DESCRIPTION_COL_ALIAS are not both NULL/NON-NULL. --
25 -- --
26 -- PARAMETERS: --
27 -- --
28 -- MODIFICATION HISTORY --
29 -- Date Username Description --
30 -- 22-Apr-04 DAGROVES CREATED --
31 --------------------------------------------------------------------------------
32 PROCEDURE CREATE_SIMPLE_QUERY
33 (P_APPLICATION_ID IN NUMBER,
34 P_QUERY_CODE IN VARCHAR2,
35 P_ID_COL IN VARCHAR2,
36 P_ID_COL_ALIAS IN VARCHAR2,
37 P_MEANING_COL IN VARCHAR2,
38 P_MEANING_COL_ALIAS IN VARCHAR2,
39 P_DESCRIPTION_COL IN VARCHAR2,
40 P_DESCRIPTION_COL_ALIAS IN VARCHAR2,
41 P_ADDITIONAL_COLS IN VARCHAR2,
42 P_OBJECT_NAME IN VARCHAR2,
43 P_ADDITIONAL_WHERE_CLAUSE IN VARCHAR2,
44 P_ORDER_BY_CLAUSE IN VARCHAR2,
45 P_USER_NAME IN VARCHAR2,
46 P_USER_ID IN NUMBER
47 )
48 IS
49 X_ROWID VARCHAR2(2000);
50 X_ADDITIONAL_WHERE_CLAUSE VARCHAR2(2000);
51 X_OFFSET NUMBER;
52 X_LOOP_CNT NUMBER;
53 BEGIN
54 VALIDATE_KEYS(P_APPLICATION_ID, P_QUERY_CODE);
55 IF P_DESCRIPTION_COL IS NULL AND P_DESCRIPTION_COL_ALIAS IS NOT NULL OR
56 P_DESCRIPTION_COL IS NOT NULL AND P_DESCRIPTION_COL_ALIAS IS NULL
57 THEN
58 RAISE_APPLICATION_ERROR(-20002, 'Require both DESCRIPTION_COL AND DESCRIPTION_COL_ALIAS or neither.');
59 END IF;
60 BNE_QUERIES_PKG.INSERT_ROW (
61 X_ROWID => X_ROWID,
62 X_APPLICATION_ID => P_APPLICATION_ID,
63 X_QUERY_CODE => P_QUERY_CODE,
64 X_OBJECT_VERSION_NUMBER => 1,
65 X_QUERY_CLASS => 'oracle.apps.bne.query.BneSimpleSQLQuery',
66 X_USER_NAME => P_USER_NAME,
67 X_CREATION_DATE => SYSDATE,
68 X_CREATED_BY => P_USER_ID,
69 X_LAST_UPDATE_DATE => SYSDATE,
70 X_LAST_UPDATED_BY => P_USER_ID,
71 X_LAST_UPDATE_LOGIN => 0);
72
73 X_OFFSET := 1;
74 X_ADDITIONAL_WHERE_CLAUSE := SUBSTR(P_ADDITIONAL_WHERE_CLAUSE, X_OFFSET, 2000);
75
76 BNE_SIMPLE_QUERY_PKG.INSERT_ROW (
77 X_ROWID => X_ROWID,
78 X_APPLICATION_ID => P_APPLICATION_ID,
79 X_QUERY_CODE => P_QUERY_CODE,
80 X_OBJECT_VERSION_NUMBER => 1,
81 X_ID_COL => P_ID_COL,
82 X_ID_COL_ALIAS => P_ID_COL_ALIAS,
83 X_MEANING_COL => P_MEANING_COL,
84 X_MEANING_COL_ALIAS => P_MEANING_COL_ALIAS,
85 X_DESCRIPTION_COL => P_DESCRIPTION_COL,
86 X_DESCRIPTION_COL_ALIAS => P_DESCRIPTION_COL_ALIAS,
87 X_ADDITIONAL_COLS => P_ADDITIONAL_COLS,
88 X_OBJECT_NAME => P_OBJECT_NAME,
89 X_ADDITIONAL_WHERE_CLAUSE => X_ADDITIONAL_WHERE_CLAUSE,
90 X_ORDER_BY_CLAUSE => P_ORDER_BY_CLAUSE,
91 X_CREATION_DATE => SYSDATE,
92 X_CREATED_BY => P_USER_ID,
93 X_LAST_UPDATE_DATE => SYSDATE,
94 X_LAST_UPDATED_BY => P_USER_ID,
95 X_LAST_UPDATE_LOGIN => 0);
96
97 X_LOOP_CNT := 1;
98 X_OFFSET := X_OFFSET + 2000;
99 X_ADDITIONAL_WHERE_CLAUSE := SUBSTR(P_ADDITIONAL_WHERE_CLAUSE, X_OFFSET, 2000);
100
101 WHILE X_ADDITIONAL_WHERE_CLAUSE IS NOT NULL
102 LOOP
103 BNE_RAW_QUERY_PKG.INSERT_ROW (
104 X_ROWID => X_ROWID,
105 X_APPLICATION_ID => P_APPLICATION_ID,
106 X_QUERY_CODE => P_QUERY_CODE,
107 X_SEQUENCE_NUM => X_LOOP_CNT,
108 X_OBJECT_VERSION_NUMBER => 1,
109 X_QUERY => X_ADDITIONAL_WHERE_CLAUSE,
110 X_CREATION_DATE => SYSDATE,
111 X_CREATED_BY => P_USER_ID,
112 X_LAST_UPDATE_DATE => SYSDATE,
113 X_LAST_UPDATED_BY => P_USER_ID,
114 X_LAST_UPDATE_LOGIN => 0);
115
116 X_OFFSET := X_OFFSET + 2000;
117 X_ADDITIONAL_WHERE_CLAUSE := SUBSTR(P_ADDITIONAL_WHERE_CLAUSE, X_OFFSET, 2000);
118 X_LOOP_CNT := X_LOOP_CNT + 1;
119 END LOOP;
120
121 END CREATE_SIMPLE_QUERY;
122
123 --------------------------------------------------------------------------------
124 -- PROCEDURE: CREATE_RAW_QUERY --
125 -- --
126 -- DESCRIPTION: Procedure creates a Web ADI raw metadata query. --
127 -- No Commit is done. --
128 -- Throws application error 20000 if P_APPLICATION_ID is invalid--
129 -- Throws application error 20001 if P_QUERY_CODE is invalid --
130 -- --
131 -- PARAMETERS: --
132 -- --
133 -- MODIFICATION HISTORY --
134 -- Date Username Description --
135 -- 22-Apr-04 DAGROVES CREATED --
136 --------------------------------------------------------------------------------
137 PROCEDURE CREATE_RAW_QUERY
138 (P_APPLICATION_ID IN NUMBER,
139 P_QUERY_CODE IN VARCHAR2,
140 P_QUERY IN VARCHAR2,
141 P_USER_NAME IN VARCHAR2,
142 P_USER_ID IN NUMBER
143 )
144 IS
145 X_ROWID VARCHAR2(2000);
146 X_QUERY VARCHAR2(2000);
147 X_OFFSET NUMBER;
148 X_LOOP_CNT NUMBER;
149 BEGIN
150 VALIDATE_KEYS(P_APPLICATION_ID, P_QUERY_CODE);
151 BNE_QUERIES_PKG.INSERT_ROW (
152 X_ROWID => X_ROWID,
153 X_APPLICATION_ID => P_APPLICATION_ID,
154 X_QUERY_CODE => P_QUERY_CODE,
155 X_OBJECT_VERSION_NUMBER => 1,
156 X_QUERY_CLASS => 'oracle.apps.bne.query.BneRawSQLQuery',
157 X_USER_NAME => P_USER_NAME,
158 X_CREATION_DATE => SYSDATE,
159 X_CREATED_BY => P_USER_ID,
160 X_LAST_UPDATE_DATE => SYSDATE,
161 X_LAST_UPDATED_BY => P_USER_ID,
162 X_LAST_UPDATE_LOGIN => 0);
163
164 X_OFFSET := 1;
165 X_LOOP_CNT := 1;
166 X_QUERY := SUBSTR(P_QUERY, X_OFFSET, 2000);
167
168 WHILE X_QUERY IS NOT NULL
169 LOOP
170 BNE_RAW_QUERY_PKG.INSERT_ROW (
171 X_ROWID => X_ROWID,
172 X_APPLICATION_ID => P_APPLICATION_ID,
173 X_QUERY_CODE => P_QUERY_CODE,
174 X_SEQUENCE_NUM => X_LOOP_CNT,
175 X_OBJECT_VERSION_NUMBER => 1,
176 X_QUERY => X_QUERY,
177 X_CREATION_DATE => SYSDATE,
178 X_CREATED_BY => P_USER_ID,
179 X_LAST_UPDATE_DATE => SYSDATE,
180 X_LAST_UPDATED_BY => P_USER_ID,
181 X_LAST_UPDATE_LOGIN => 0);
182
183 X_OFFSET := X_OFFSET + 2000;
184 X_QUERY := SUBSTR(P_QUERY, X_OFFSET, 2000);
185 X_LOOP_CNT := X_LOOP_CNT + 1;
186 END LOOP;
187 END CREATE_RAW_QUERY;
188
189 --------------------------------------------------------------------------------
190 -- PROCEDURE: CREATE_JAVA_QUERY --
191 -- --
192 -- DESCRIPTION: Procedure creates a Web ADI java query. --
193 -- No Commit is done. --
194 -- Throws application error 20000 if P_APPLICATION_ID is invalid--
195 -- Throws application error 20001 if P_QUERY_CODE is invalid --
196 -- --
197 -- PARAMETERS: --
198 -- --
199 -- MODIFICATION HISTORY --
200 -- Date Username Description --
201 -- 22-Apr-04 DAGROVES CREATED --
202 --------------------------------------------------------------------------------
203 PROCEDURE CREATE_JAVA_QUERY
204 (P_APPLICATION_ID IN NUMBER,
205 P_QUERY_CODE IN VARCHAR2,
206 P_QUERY_CLASS IN VARCHAR2,
207 P_USER_NAME IN VARCHAR2,
208 P_USER_ID IN NUMBER
209 )
210 IS
211 X_ROWID VARCHAR2(2000);
212 BEGIN
213 VALIDATE_KEYS(P_APPLICATION_ID, P_QUERY_CODE);
214
215 BNE_QUERIES_PKG.INSERT_ROW (
216 X_ROWID => X_ROWID,
217 X_APPLICATION_ID => P_APPLICATION_ID,
218 X_QUERY_CODE => P_QUERY_CODE,
219 X_OBJECT_VERSION_NUMBER => 1,
220 X_QUERY_CLASS => P_QUERY_CLASS,
221 X_USER_NAME => P_USER_NAME,
222 X_CREATION_DATE => SYSDATE,
223 X_CREATED_BY => P_USER_ID,
224 X_LAST_UPDATE_DATE => SYSDATE,
225 X_LAST_UPDATED_BY => P_USER_ID,
226 X_LAST_UPDATE_LOGIN => 0);
227
228 END CREATE_JAVA_QUERY;
229
230 --------------------------------------------------------------------------------
231 -- PROCEDURE: DELETE_QUERY --
232 -- --
233 -- DESCRIPTION: Procedure deletes a Web ADI query. --
234 -- No Commit is done. --
235 -- Throws application error 20000 if P_APPLICATION_ID is invalid--
236 -- Throws application error 20001 if P_QUERY_CODE is invalid --
237 -- --
238 -- PARAMETERS: --
239 -- --
240 -- MODIFICATION HISTORY --
241 -- Date Username Description --
242 -- 22-Apr-04 DAGROVES CREATED --
243 --------------------------------------------------------------------------------
244 PROCEDURE DELETE_QUERY
245 (P_APPLICATION_ID IN NUMBER,
246 P_QUERY_CODE IN VARCHAR2
247 )
248 IS
249 BEGIN
250 VALIDATE_KEYS(P_APPLICATION_ID, P_QUERY_CODE);
251
252 DELETE FROM BNE_QUERIES_B WHERE APPLICATION_ID = P_APPLICATION_ID AND QUERY_CODE = P_QUERY_CODE;
253 DELETE FROM BNE_QUERIES_TL WHERE APPLICATION_ID = P_APPLICATION_ID AND QUERY_CODE = P_QUERY_CODE;
254 DELETE FROM BNE_SIMPLE_QUERY WHERE APPLICATION_ID = P_APPLICATION_ID AND QUERY_CODE = P_QUERY_CODE;
255 DELETE FROM BNE_RAW_QUERY WHERE APPLICATION_ID = P_APPLICATION_ID AND QUERY_CODE = P_QUERY_CODE;
256 END DELETE_QUERY;
257
258
259 END BNE_QUERY_UTILS;