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