[Home] [Help]
PACKAGE BODY: APPS.QP_UI_QUERIES_PKG
Source
1 package body QP_UI_QUERIES_PKG as
2 /* $Header: QPXVUIQB.pls 120.0 2005/06/02 01:06:00 appldev noship $ */
3 procedure INSERT_ROW (
4 X_QUERY_ID in NUMBER,
5 X_PUBLIC_FLAG in VARCHAR2,
6 X_LINES_WHERE_CLAUSE in VARCHAR2,
7 X_HEADERS_WHERE_CLAUSE in VARCHAR2,
8 X_NAME in VARCHAR2,
9 X_CREATION_DATE in DATE,
10 X_CREATED_BY in NUMBER,
11 X_LAST_UPDATE_DATE in DATE,
12 X_LAST_UPDATED_BY in NUMBER,
13 X_LAST_UPDATE_LOGIN in NUMBER
14 ) is
15 X_ROWID ROWID;
16 cursor C is select ROWID from QP_UI_QUERIES_TL
17 where QUERY_ID = X_QUERY_ID
18 and LANGUAGE = userenv('LANG')
19 ;
20 begin
21 insert into QP_UI_QUERIES_TL (
22 CREATION_DATE,
23 CREATED_BY,
24 LAST_UPDATE_DATE,
25 LAST_UPDATED_BY,
26 LAST_UPDATE_LOGIN,
27 PUBLIC_FLAG,
28 LINES_WHERE,
29 QUERY_ID,
30 NAME,
31 HEADER_WHERE,
32 LANGUAGE,
33 SOURCE_LANG
34 ) select
35 X_CREATION_DATE,
36 X_CREATED_BY,
37 X_LAST_UPDATE_DATE,
38 X_LAST_UPDATED_BY,
39 X_LAST_UPDATE_LOGIN,
40 X_PUBLIC_FLAG,
41 X_LINES_WHERE_CLAUSE,
42 X_QUERY_ID,
43 X_NAME,
44 X_HEADERS_WHERE_CLAUSE,
45 L.LANGUAGE_CODE,
46 userenv('LANG')
47 from FND_LANGUAGES L
48 where L.INSTALLED_FLAG in ('I', 'B')
49 and not exists
50 (select NULL
51 from QP_UI_QUERIES_TL T
52 where T.QUERY_ID = X_QUERY_ID
53 and T.LANGUAGE = L.LANGUAGE_CODE);
54
55 open c;
56 fetch c into X_ROWID;
57 if (c%notfound) then
58 close c;
59 raise no_data_found;
60 end if;
61 close c;
62
63 end INSERT_ROW;
64
65 procedure LOCK_ROW (
66 X_QUERY_ID in NUMBER,
67 X_PUBLIC_FLAG in VARCHAR2 DEFAULT NULL,
68 X_NAME in VARCHAR2 DEFAULT NULL
69 ) is
70 cursor c1 is select
71 PUBLIC_FLAG,
72 NAME,
73 decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
74 from QP_UI_QUERIES_TL
75 where QUERY_ID = X_QUERY_ID
76 for update of QUERY_ID nowait;
77 begin
78 for tlinfo in c1 loop
79 if x_public_flag is not null and x_name is not null then
80 if (tlinfo.BASELANG = 'Y') then
81 if ( (tlinfo.NAME = X_NAME)
82 AND (tlinfo.PUBLIC_FLAG = X_PUBLIC_FLAG)
83 ) then
84 null;
85 else
86 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
87 app_exception.raise_exception;
88 end if;
89 end if;
90 end if;
91 end loop;
92 return;
93 end LOCK_ROW;
94
95 procedure UPDATE_ROW (
96 X_QUERY_ID in NUMBER,
97 X_DELETE_FLAG in VARCHAR2,
98 X_PUBLIC_FLAG in VARCHAR2,
99 X_NAME in VARCHAR2,
100 X_LAST_UPDATE_DATE in DATE,
101 X_LAST_UPDATED_BY in NUMBER,
102 X_LAST_UPDATE_LOGIN in NUMBER
103 ) is
104 begin
105 update QP_UI_QUERIES_TL set
106 DELETE_FLAG = X_DELETE_FLAG,
107 PUBLIC_FLAG = X_PUBLIC_FLAG,
108 NAME = X_NAME,
109 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
110 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
111 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
112 SOURCE_LANG = Userenv('LANG')
113 where QUERY_ID = X_QUERY_ID
114 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
115
116 if (sql%notfound) then
117 raise no_data_found;
118 end if;
119 end UPDATE_ROW;
120
121 procedure DELETE_ROW (
122 X_QUERY_ID in NUMBER
123 ) is
124 begin
125 delete from QP_UI_QUERIES_TL
126 where QUERY_ID = X_QUERY_ID;
127
128 if (sql%notfound) then
129 raise no_data_found;
130 end if;
131
132 end DELETE_ROW;
133
134 procedure Insert_Columns(
135 p_header_column_tbl IN UI_TREE_TBL,
136 p_query_id IN Number
137 ) is
138 i Number;
139 Begin
140 if p_header_column_tbl.count>0 then
141 for i in p_header_column_tbl.first .. p_header_column_tbl.last loop
142 Insert_row_columns
143 (
144 p_header_column_tbl(i).column_name,
145 p_header_column_tbl(i).column_value,
146 p_header_column_tbl(i).column_type,
147 p_header_column_tbl(i).column_index_id,
148 p_query_id
149 );
150
151 end loop;
152 end if;
153
154 end Insert_columns;
155
156 procedure INSERT_ROW_COLUMNS(
157 p_column_name IN varchar2,
158 p_column_value IN varchar2,
159 p_column_data_type IN varchar2,
160 p_column_index_id IN number,
161 p_query_id IN number
162 ) IS
163 l_column_id Number;
164 l_record_exist Number;
165 BEGIN
166 select count(*)
167 into l_record_exist
168 from qp_ui_query_columns
169 where query_id = p_query_id
170 and query_column_index_id = p_column_index_id;
171
172 if l_record_exist > 0 then
173 return;
174 end if;
175
176 Select qp_ui_query_columns_s.nextval
177 Into l_column_id
178 From dual;
179
180 INSERT INTO QP_UI_QUERY_COLUMNS
181 (
182 COLUMN_ID,
183 QUERY_ID,
184 QUERY_COLUMN_INDEX_ID,
185 COLUMN_NAME,
186 COLUMN_VALUE,
187 COLUMN_TYPE,
188 CREATION_DATE,
189 CREATED_BY,
190 LAST_UPDATE_DATE,
191 LAST_UPDATED_BY
192 )
193 VALUES
194 (l_column_id,
195 p_query_id,
196 p_column_index_id,
197 p_column_name,
198 p_column_value,
199 p_column_data_type,
200 sysdate,
201 FND_GLOBAL.USER_ID,
202 sysdate,
203 FND_GLOBAL.USER_ID
204 );
205
206 END INSERT_ROW_COLUMNS;
207
208 procedure ADD_LANGUAGE
209 is
210 begin
211
212 update QP_UI_QUERIES_TL T set (
213 NAME
214 ) = (select
215 B.NAME
216 from QP_UI_QUERIES_TL B
217 where B.QUERY_ID = T.QUERY_ID
218 and B.LANGUAGE = T.SOURCE_LANG)
219 where (
220 T.QUERY_ID,
221 T.LANGUAGE
222 ) in (select
223 SUBT.QUERY_ID,
224 SUBT.LANGUAGE
225 from QP_UI_QUERIES_TL SUBB, QP_UI_QUERIES_TL SUBT
226 where SUBB.QUERY_ID = SUBT.QUERY_ID
227 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
228 and (SUBB.NAME <> SUBT.NAME
229 ));
230
231 insert into QP_UI_QUERIES_TL (
232 CREATION_DATE,
233 CREATED_BY,
234 LAST_UPDATE_DATE,
235 LAST_UPDATED_BY,
236 LAST_UPDATE_LOGIN,
237 PUBLIC_FLAG,
238 LINES_WHERE,
239 QUERY_ID,
240 NAME,
241 HEADER_WHERE,
242 LANGUAGE,
243 SOURCE_LANG
244 ) select
245 B.CREATION_DATE,
246 B.CREATED_BY,
247 B.LAST_UPDATE_DATE,
248 B.LAST_UPDATED_BY,
249 B.LAST_UPDATE_LOGIN,
250 B.PUBLIC_FLAG,
251 B.LINES_WHERE,
252 B.QUERY_ID,
253 B.NAME,
254 B.HEADER_WHERE,
255 L.LANGUAGE_CODE,
256 B.SOURCE_LANG
257 from QP_UI_QUERIES_TL B, FND_LANGUAGES L
258 where L.INSTALLED_FLAG in ('I', 'B')
259 and B.LANGUAGE = userenv('LANG')
260 and not exists
261 (select NULL
262 from QP_UI_QUERIES_TL T
263 where T.QUERY_ID = B.QUERY_ID
264 and T.LANGUAGE = L.LANGUAGE_CODE);
265
266 end ADD_LANGUAGE;
267
268 end QP_UI_QUERIES_PKG;