[Home] [Help]
PACKAGE BODY: APPS.BNE_STORED_SQL_PKG
Source
1 package body BNE_STORED_SQL_PKG as
2 /* $Header: bnestsqlb.pls 120.2 2005/06/29 03:41:06 dvayro noship $ */
3
4 procedure INSERT_ROW (
5 X_ROWID in out NOCOPY VARCHAR2,
6 X_APPLICATION_ID in NUMBER,
7 X_CONTENT_CODE in VARCHAR2,
8 X_QUERY in VARCHAR2,
9 X_OBJECT_VERSION_NUMBER in NUMBER,
10 X_CREATION_DATE in DATE,
11 X_CREATED_BY in NUMBER,
12 X_LAST_UPDATE_DATE in DATE,
13 X_LAST_UPDATED_BY in NUMBER,
14 X_LAST_UPDATE_LOGIN in NUMBER,
15 X_QUERY_APP_ID in NUMBER,
16 X_QUERY_CODE in VARCHAR2
17 ) is
18 cursor C is select ROWID from BNE_STORED_SQL
19 where APPLICATION_ID = X_APPLICATION_ID
20 and CONTENT_CODE = X_CONTENT_CODE
21 ;
22 begin
23 insert into BNE_STORED_SQL (
24 LAST_UPDATED_BY,
25 LAST_UPDATE_LOGIN,
26 LAST_UPDATE_DATE,
27 QUERY,
28 CREATED_BY,
29 CREATION_DATE,
30 OBJECT_VERSION_NUMBER,
31 APPLICATION_ID,
32 CONTENT_CODE,
33 QUERY_APP_ID,
34 QUERY_CODE
35 ) values (
36 X_LAST_UPDATED_BY,
37 X_LAST_UPDATE_LOGIN,
38 X_LAST_UPDATE_DATE,
39 X_QUERY,
40 X_CREATED_BY,
41 X_CREATION_DATE,
42 X_OBJECT_VERSION_NUMBER,
43 X_APPLICATION_ID,
44 X_CONTENT_CODE,
45 X_QUERY_APP_ID,
46 X_QUERY_CODE
47 );
48
49 open c;
50 fetch c into X_ROWID;
51 if (c%notfound) then
52 close c;
53 raise no_data_found;
54 end if;
55 close c;
56
57 end INSERT_ROW;
58
59 procedure LOCK_ROW (
60 X_APPLICATION_ID in NUMBER,
61 X_CONTENT_CODE in VARCHAR2,
62 X_QUERY in VARCHAR2,
63 X_OBJECT_VERSION_NUMBER in NUMBER,
64 X_QUERY_APP_ID in NUMBER,
65 X_QUERY_CODE in VARCHAR2
66 ) is
67 cursor c1 is select
68 QUERY,
69 OBJECT_VERSION_NUMBER,
70 QUERY_APP_ID,
71 QUERY_CODE
72 from BNE_STORED_SQL
73 where APPLICATION_ID = X_APPLICATION_ID
74 and CONTENT_CODE = X_CONTENT_CODE
75 for update of APPLICATION_ID nowait;
76 begin
77 for tlinfo in c1 loop
78 if ( (tlinfo.QUERY = X_QUERY)
79 AND (tlinfo.OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER)
80 AND ((tlinfo.QUERY_APP_ID = X_QUERY_APP_ID)
81 OR ((tlinfo.QUERY_APP_ID is null) AND (X_QUERY_APP_ID is null)))
82 AND ((tlinfo.QUERY_CODE = X_QUERY_CODE)
83 OR ((tlinfo.QUERY_CODE is null) AND (X_QUERY_CODE is null)))
84 ) then
85 null;
86 else
87 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
88 app_exception.raise_exception;
89 end if;
90 end loop;
91 return;
92 end LOCK_ROW;
93
94 procedure UPDATE_ROW (
95 X_APPLICATION_ID in NUMBER,
96 X_CONTENT_CODE in VARCHAR2,
97 X_QUERY in VARCHAR2,
98 X_OBJECT_VERSION_NUMBER in NUMBER,
99 X_LAST_UPDATE_DATE in DATE,
100 X_LAST_UPDATED_BY in NUMBER,
101 X_LAST_UPDATE_LOGIN in NUMBER,
102 X_QUERY_APP_ID in NUMBER,
103 X_QUERY_CODE in VARCHAR2
104 ) is
105 begin
106 update BNE_STORED_SQL set
107 QUERY = X_QUERY,
108 OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER,
109 CONTENT_CODE = X_CONTENT_CODE,
110 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
111 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
112 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
113 QUERY_APP_ID = X_QUERY_APP_ID,
114 QUERY_CODE = X_QUERY_CODE
115 where APPLICATION_ID = X_APPLICATION_ID
116 and CONTENT_CODE = X_CONTENT_CODE;
117
118 if (sql%notfound) then
119 raise no_data_found;
120 end if;
121 end UPDATE_ROW;
122
123 procedure DELETE_ROW (
124 X_APPLICATION_ID in NUMBER,
125 X_CONTENT_CODE in VARCHAR2
126 ) is
127 begin
128 delete from BNE_STORED_SQL
129 where APPLICATION_ID = X_APPLICATION_ID
130 and CONTENT_CODE = X_CONTENT_CODE;
131
132 if (sql%notfound) then
133 raise no_data_found;
134 end if;
135
136 end DELETE_ROW;
137
138 procedure ADD_LANGUAGE
139 is
140 begin
141 null;
142 end ADD_LANGUAGE;
143
144 --------------------------------------------------------------------------------
145 -- PROCEDURE: LOAD_ROW --
146 -- --
147 -- DESCRIPTION: Load a row into the BNE_STORED_SQL entity. --
148 -- This proc is called from the apps loader. --
149 -- --
150 -- SEE: http://www-apps.us.oracle.com/atg/plans/r115/fndloadqr.txt --
151 -- --
152 -- --
153 -- MODIFICATION HISTORY --
154 -- Date Username Description --
155 -- 1-Oct-02 DGROVES CREATED --
156 --------------------------------------------------------------------------------
157 procedure LOAD_ROW(
158 x_content_asn in VARCHAR2,
159 x_content_code in VARCHAR2,
160 x_object_version_number in VARCHAR2,
161 x_query in VARCHAR2,
162 x_owner in VARCHAR2,
163 x_last_update_date in VARCHAR2,
164 x_custom_mode in VARCHAR2,
165 x_query_app_asn in VARCHAR2,
166 x_query_code in VARCHAR2
167 )
168 is
169 l_app_id number;
170 l_query_app_id number;
171 l_row_id varchar2(64);
172 f_luby number; -- entity owner in file
173 f_ludate date; -- entity update date in file
174 db_luby number; -- entity owner in db
175 db_ludate date; -- entity update date in db
176 begin
177 -- translate values to IDs
178 l_app_id := BNE_LCT_TOOLS_PKG.ASN_TO_APP_ID(x_content_asn);
179 l_query_app_id := BNE_LCT_TOOLS_PKG.ASN_TO_APP_ID(x_query_app_asn);
180
181 -- Translate owner to file_last_updated_by
182 f_luby := fnd_load_util.owner_id(x_owner);
183
184 -- Translate char last_update_date to date
185 f_ludate := nvl(to_date(x_last_update_date, 'YYYY/MM/DD'), sysdate);
186 begin
187 select LAST_UPDATED_BY, LAST_UPDATE_DATE
188 into db_luby, db_ludate
189 from BNE_STORED_SQL
190 where APPLICATION_ID = l_app_id
191 and CONTENT_CODE = x_content_code;
192
193 -- Test for customization and version
194 if (fnd_load_util.upload_test(f_luby, f_ludate, db_luby,
195 db_ludate, x_custom_mode)) then
196 -- Update existing row
197 BNE_STORED_SQL_PKG.Update_Row(
198 X_APPLICATION_ID => l_app_id,
199 X_CONTENT_CODE => x_content_code,
200 X_QUERY => x_query,
201 X_OBJECT_VERSION_NUMBER => x_object_version_number,
202 X_LAST_UPDATE_DATE => f_ludate,
203 X_LAST_UPDATED_BY => f_luby,
204 X_LAST_UPDATE_LOGIN => 0,
205 X_QUERY_APP_ID => l_query_app_id,
206 X_QUERY_CODE => x_query_code
207 );
208 end if;
209 exception
210 when no_data_found then
211 -- Record doesn't exist - insert in all cases
212 BNE_STORED_SQL_PKG.Insert_Row(
213 X_ROWID => l_row_id,
214 X_APPLICATION_ID => l_app_id,
215 X_CONTENT_CODE => x_content_code,
216 X_QUERY => x_query,
217 X_OBJECT_VERSION_NUMBER => x_object_version_number,
218 X_CREATION_DATE => f_ludate,
219 X_CREATED_BY => f_luby,
220 X_LAST_UPDATE_DATE => f_ludate,
221 X_LAST_UPDATED_BY => f_luby,
222 X_LAST_UPDATE_LOGIN => 0,
223 X_QUERY_APP_ID => l_query_app_id,
224 X_QUERY_CODE => x_query_code
225 );
226 end;
227 end LOAD_ROW;
228
229 end BNE_STORED_SQL_PKG;