[Home] [Help]
PACKAGE BODY: APPS.ECX_DOCUMENT_STANDARDS_API
Source
1 PACKAGE BODY ECX_DOCUMENT_STANDARDS_API AS
2 -- $Header: ECXSTDAB.pls 120.2 2005/06/30 11:17:57 appldev ship $
3
4 /**
5 This Retrieve_XML_Standard API is used to retrieve an existing XML standard definition from the ECX_Standards table.
6 **/
7 procedure retrieve_standard
8 (
9 x_return_status Out nocopy pls_integer,
10 x_msg Out nocopy Varchar2,
11 x_standard_id Out nocopy pls_integer,
12 p_standard_code In Varchar2,
13 x_standard_type In Out nocopy Varchar2,
14 x_standard_desc Out nocopy Varchar2,
15 x_data_seeded Out nocopy Varchar2
16 )
17 is
18 x_last_update_date date;
19 x_last_updated_by pls_integer;
20 x_created_by pls_integer;
21 x_creation_date date;
22 l_standard_type varchar2(80);
23 begin
24 x_return_status := ECX_UTIL_API.G_NO_ERROR;
25 x_msg := null;
26 l_standard_type := x_standard_type;
27
28 -- make sure p_standard_code is not null.
29 If (p_standard_code is null)
30 then
31 x_return_status := ECX_UTIL_API.G_NULL_PARAM;
32 x_msg :=ecx_debug.getTranslatedMessage('ECX_STANDARD_CODE_NULL');
33 return;
34 elsif (l_standard_type is null)
35 then
36 l_standard_type := 'XML';
37 end if;
38
39
40 select STANDARD_ID,
41 STANDARD_TYPE,
42 STANDARD_DESC,
43 DATA_SEEDED,
44 LAST_UPDATE_DATE,
45 LAST_UPDATED_BY,
46 CREATED_BY,
47 CREATION_DATE
48 into x_standard_id,
49 x_standard_type,
50 x_standard_desc,
51 x_data_seeded,
52 x_last_update_date,
53 x_last_updated_by,
54 x_created_by,
55 x_creation_date
56 from ecx_standards_vl
57 where standard_code = p_standard_code
58 and standard_type = l_standard_type;
59 exception
60 when no_data_found then
61 x_return_status := ECX_UTIL_API.G_NO_DATA_ERROR;
62 x_msg :=ecx_debug.getTranslatedMessage('ECX_STANDARD_ROW_NOT_FOUND',
63 'p_standard_code',
64 p_standard_code,
65 'p_standard_type',
66 l_standard_type);
67 when too_many_rows then
68 x_return_status := ECX_UTIL_API.G_TOO_MANY_ROWS;
69 x_msg :=ecx_debug.getTranslatedMessage('ECX_STANDARD_TOO_MANY_ROWS',
70 'p_standard_code',
71 p_standard_code,
72 'p_standard_type',
73 l_standard_type);
74
75 when others then
76 x_return_status := ECX_UTIL_API.G_UNEXP_ERROR;
77 x_msg := substr(SQLERRM,1,200);
78 end retrieve_standard;
79
80 /**
81 This Create_XML_Standard API is used to create a new XML standard definition in the ECX_Standards table.
82 **/
83 procedure create_standard
84 (
85 x_return_status Out nocopy pls_integer,
86 x_msg Out nocopy Varchar2,
87 x_standard_id Out nocopy pls_integer,
88 p_standard_code In Varchar2,
89 p_standard_type In Varchar2,
90 p_standard_desc In Varchar2,
91 p_data_seeded In Varchar2,
92 p_owner In varchar2
93 )
94 is
95 i_last_updated_by pls_integer;
96 i_rowid varchar2(2000);
97 l_standard_type varchar2(80);
98 begin
99
100 x_return_status := ECX_UTIL_API.G_NO_ERROR;
101 x_standard_id :=-1;
102 x_msg := null;
103 l_standard_type := p_standard_type;
104
105 -- make sure p_standard_code is not null.
106 If (p_standard_code is null)
107 then
108 x_return_status := ECX_UTIL_API.G_NULL_PARAM;
109 x_msg := ecx_debug.getTranslatedMessage('ECX_STANDARD_CODE_NULL');
110 return;
111 elsIf (l_standard_type is null)
112 then
113 l_standard_type := 'XML';
114 end If;
115
116 -- validate data seeded flag
117 If NOT ecx_util_api.validate_data_seeded_flag(p_data_seeded)
118 then
119 x_return_status := ECX_UTIL_API.G_INVALID_PARAM;
120 x_msg := ecx_debug.getTranslatedMessage('ECX_INVALID_DATA_SEEDED',
121 'p_data_seeded', p_data_seeded);
122 return;
123 end If;
124
125 select ecx_standards_s.nextval
126 into x_standard_id
127 from dual;
128
129 if p_owner = 'SEED'
130 then
131 if p_data_seeded = 'Y'
132 then
133 i_last_updated_by :=1;
134 else
135 x_return_status := ECX_UTIL_API.G_INVALID_PARAM;
136 x_msg := ecx_debug.getTranslatedMessage('ECX_DATA_OWNER_INCONSISTENT',
137 'p_data_seeded', p_data_seeded,
138 'p_owner', p_owner);
139 return;
140 end if;
141 elsif p_owner = 'CUSTOM'
142 then
143 if NOT (p_data_seeded = 'Y')
144 then
145 i_last_updated_by :=0;
146 else
147 x_return_status := ECX_UTIL_API.G_INVALID_PARAM;
148 x_msg := ecx_debug.getTranslatedMessage('ECX_DATA_OWNER_INCONSISTENT',
149 'p_data_seeded', p_data_seeded,
150 'p_owner', p_owner);
151
152 return;
153 end if;
154 else
155 x_return_status := ECX_UTIL_API.G_INVALID_PARAM;
156 x_msg := ecx_debug.getTranslatedMessage('ECX_INVALID_OWNER',
157 'p_owner', p_owner);
158 return;
159
160 end if;
161
162 /* Call the table handler APIS to insert data into
163 ecx_standards_b , ecx_standards_tl table */
164
165 ECX_STANDARDS_PKG.INSERT_ROW(
166 X_ROWID => i_rowid,
167 X_STANDARD_ID => x_standard_id,
168 X_STANDARD_CODE => p_standard_code,
169 X_STANDARD_TYPE => l_standard_type,
170 X_DATA_SEEDED => p_data_seeded,
171 X_STANDARD_DESC => p_standard_desc,
172 X_CREATION_DATE => sysdate,
173 X_CREATED_BY => i_last_updated_by,
174 X_LAST_UPDATE_DATE => sysdate,
175 X_LAST_UPDATED_BY => i_last_updated_by,
176 X_LAST_UPDATE_LOGIN => 0);
177
178
179 exception
180 when dup_val_on_index then
181 x_return_status := ECX_UTIL_API.G_DUP_ERROR;
182 x_msg:=ecx_debug.getTranslatedMessage('ECX_DOCUMENT_STANDARD_EXISTS',
183 'p_standard_code',
184 p_standard_code,
185 'p_standard_type',
186 l_standard_type);
187
188 when no_data_found then
189 x_return_status := ECX_UTIL_API.G_NO_DATA_ERROR;
190 x_msg :=ecx_debug.getTranslatedMessage('ECX_STANDARD_ROW_NOT_FOUND',
191 'p_standard_code',
192 p_standard_code,
193 'p_standard_type',
194 l_standard_type);
195
196 when others then
197 x_return_status := ECX_UTIL_API.G_UNEXP_ERROR;
198 x_msg := substr(SQLERRM,1,200);
199
200 end create_standard;
201
202 /**
203 Update_Standards API is used to update an existing XML Standard definition in the ECX_Standards table.
204 This API allows users to update the description and data seeded fields by specifying standard id
205 **/
206
207 procedure update_standard
208 (
209 x_return_status Out nocopy pls_integer,
210 x_msg Out nocopy Varchar2,
211 p_standard_id In pls_integer,
212 p_standard_desc In Varchar2,
213 p_data_seeded In Varchar2,
214 p_owner In varchar2
215 )
216 is
217 i_last_updated_by pls_integer;
218
219 begin
220
221 x_return_status := ECX_UTIL_API.G_NO_ERROR;
222 x_msg := null;
223
224 -- make sure standard_id is not null.
225 If (p_standard_id is null)
226 then
227 x_return_status := ECX_UTIL_API.G_NULL_PARAM;
228 x_msg := ecx_debug.getTranslatedMessage('ECX_STANDARD_ID_NOT_NULL');
229 return;
230 end if;
231
232 -- validate data seeded flag
233 If NOT ecx_util_api.validate_data_seeded_flag(p_data_seeded)
234 then
235 x_return_status := ECX_UTIL_API.G_INVALID_PARAM;
236 x_msg := ecx_debug.getTranslatedMessage('ECX_INVALID_DATA_SEEDED',
237 'p_data_seeded', p_data_seeded);
238 return;
239 end If;
240
241
242 if p_owner = 'SEED'
243 then
244 if p_data_seeded = 'Y'
245 then
246 i_last_updated_by :=1;
247 else
248 x_return_status := ECX_UTIL_API.G_INVALID_PARAM;
249 x_msg := ecx_debug.getTranslatedMessage('ECX_DATA_OWNER_INCONSISTENT',
250 'p_data_seeded', p_data_seeded,
251 'p_owner', p_owner);
252
253 return;
254 end if;
255 elsif p_owner = 'CUSTOM'
256 then
257 if NOT (p_data_seeded = 'Y')
258 then
259 i_last_updated_by :=0;
260 else
261 x_return_status := ECX_UTIL_API.G_INVALID_PARAM;
262 x_msg := ecx_debug.getTranslatedMessage('ECX_DATA_OWNER_INCONSISTENT',
263 'p_data_seeded', p_data_seeded,
264 'p_owner', p_owner);
265
266 return;
267 end if;
268 else
269 x_return_status := ECX_UTIL_API.G_INVALID_PARAM;
270 x_msg := ecx_debug.getTranslatedMessage('ECX_INVALID_OWNER',
271 'p_owner', p_owner);
272 return;
273
274 end if;
275
276 update ECX_STANDARDS_B
277 set DATA_SEEDED = p_data_seeded,
278 LAST_UPDATED_BY = i_last_updated_by,
279 LAST_UPDATE_DATE = sysdate
280 where standard_id = p_standard_id;
281
282 if (sql%rowcount = 0)
283 then
284 x_return_status := ECX_UTIL_API.G_NO_DATA_ERROR;
285 x_msg := ecx_debug.getTranslatedMessage('ECX_NO_ROWS_UPDATED',
286 'p_table', 'ECX_STANDARDS_B', 'p_param_name', 'Document Standard ID','p_param_id',p_standard_id);
287 return;
288 end if;
289
290 update ECX_STANDARDS_TL
291 set STANDARD_DESC = p_standard_desc,
292 LAST_UPDATED_BY = i_last_updated_by,
293 LAST_UPDATE_DATE = sysdate,
294 source_lang = userenv('LANG')
295 where standard_id = p_standard_id and
296 userenv('LANG') in (language, source_lang);
297
298 if (sql%rowcount = 0)
299 then
300 x_return_status := ECX_UTIL_API.G_NO_DATA_ERROR;
301 x_msg := ecx_debug.getTranslatedMessage('ECX_NO_ROWS_UPDATED',
302 'p_table',
303 'ECX_STANDARDS_TL',
304 'p_param_name',
305 'Document Standard ID',
306 'p_param_id',
307 p_standard_id);
308 return;
309 end if;
310
311
312 exception
313 when others then
314 x_return_status := ECX_UTIL_API.G_UNEXP_ERROR;
315 x_msg := substr(SQLERRM,1,200);
316 end update_standard;
317
318 /**
319 Delete_XML_Standard API is used to delete an existing XML Standard definition in the ECX_STANDARDS
320 table and its attributes. This API allows users to delete the definitions by specifying the standard id.
321 **/
322 procedure delete_standard
323 (
324 x_return_status Out nocopy pls_integer,
325 x_msg Out nocopy Varchar2,
326 p_standard_id In pls_integer
327 )
328 is
329 begin
330
331 x_return_status := ECX_UTIL_API.G_NO_ERROR;
332 x_msg := null;
333
334 if (p_standard_id is null)
335 then
336 x_return_status := ECX_UTIL_API.G_NULL_PARAM;
337 x_msg := ecx_debug.getTranslatedMessage('ECX_STANDARD_ID_NOT_NULL');
338 return;
339 end if;
340
341 -- make sure standard is not used in any code conversion or external process.
342 -- if there is a reference to this standard id, then return G_REFER_ERROR.
343
344 delete from ecx_standard_attributes
345 where standard_id = p_standard_id;
346
347 /* Call tbale handler DELETE_ROW for the deletion */
348
349 ECX_STANDARDS_PKG.DELETE_ROW(x_standard_id => p_standard_id);
350
351 exception
352
353 when no_data_found then
354 x_return_status := ECX_UTIL_API.G_NO_DATA_ERROR;
355 x_msg := ecx_debug.getTranslatedMessage('ECX_NO_ROWS_DELETED',
356 'p_table',
357 'ECX_STANDARDS',
358 'p_param_name',
359 'Document Standard ID',
360 'p_param_id',p_standard_id);
361
362 when others then
363 x_return_status := ECX_UTIL_API.G_UNEXP_ERROR;
364 x_msg := substr(SQLERRM,1,200);
365 end delete_standard;
366
367 END ECX_DOCUMENT_STANDARDS_API;