[Home] [Help]
PACKAGE BODY: APPS.AMS_XML_ELEMENT_PVT
Source
1 PACKAGE BODY AMS_Xml_Element_PVT as
2 /* $Header: amsvxelb.pls 115.10 2003/03/06 17:25:38 huili noship $ */
3 -- ===============================================================
4 -- Start of Comments
5 -- Package name
6 -- AMS_Xml_Element_PVT
7 -- Purpose
8 -- Manage XML Elements.
9 --
10 -- History
11 -- 05/13/2002 DMVINCEN Created.
12 -- 05/21/2002 DMVINCEN BUG 2380113: Removed G_USER_ID and G_LOGIN_ID.
13 -- 05/21/2002 DMVINCEN Removed created_by and creation_date from update.
14 --
15 -- NOTE
16 --
17 -- End of Comments
18 -- ===============================================================
19
20
21 G_PKG_NAME CONSTANT VARCHAR2(30):= 'AMS_Xml_Element_PVT';
22 G_FILE_NAME CONSTANT VARCHAR2(12) := 'amsvxelb.pls';
23 G_B2B_VIEW_NAME CONSTANT VARCHAR2(100) := 'AMS_HZ_B2B_MAPPING_V';
24 G_B2C_VIEW_NAME CONSTANT VARCHAR2(100) := 'AMS_HZ_B2C_MAPPING_V';
25
26 AMS_DEBUG_HIGH_ON boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_HIGH);
27 AMS_DEBUG_LOW_ON boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW);
28 AMS_DEBUG_MEDIUM_ON boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_MEDIUM);
29
30 PROCEDURE write_msg(p_message IN VARCHAR2)
31 IS
32 BEGIN
33 --insert into imp_xml_test values (TO_CHAR(DBMS_UTILITY.get_time)
34 -- ||'::::' || p_message);
35 --commit;
36 null;
37 END;
38
39
40 PROCEDURE Update_Xml_Source_Lines_Util (
41 p_view_name IN VARCHAR2,
42 p_commit IN VARCHAR2,
43 p_column_name IN VARCHAR2,
44 p_prim_key IN NUMBER,
45 p_xml_elements_data IN varchar2_2000_set_type,
46 p_xml_elements_col_name IN varchar2_2000_set_type,
47 x_return_status OUT NOCOPY VARCHAR2,
48 x_msg_count OUT NOCOPY NUMBER,
49 x_msg_data OUT NOCOPY VARCHAR2
50 )
51 IS
52
53 l_update_statement VARCHAR2(2000) := ' UPDATE ' || p_view_name
54 || ' SET LOAD_STATUS = ''RELOAD'' ';
55 l_status integer;
56 g_cursor INT DEFAULT dbms_sql.open_cursor;
57
58 l_error VARCHAR2(2000);
59 BEGIN
60 FOR i IN p_xml_elements_col_name.FIRST .. p_xml_elements_col_name.LAST
61 LOOP
62 l_update_statement := l_update_statement || ',' || p_xml_elements_col_name(i)
63 || '=:v' || i;
64 END LOOP;
65
66 l_update_statement := l_update_statement || ' WHERE '
67 || p_column_name || '=:p1';
68
69 write_msg(' l_update_statement::' || l_update_statement);
70
71 BEGIN
72 write_msg(' test1::');
73
74 dbms_sql.parse (g_cursor, l_update_statement, dbms_sql.native);
75
76 write_msg(' test2::');
77 FOR j IN p_xml_elements_data.FIRST .. p_xml_elements_data.LAST
78 LOOP
79 write_msg(' test3::');
80 dbms_sql.bind_variable (g_cursor, ':v' || j, p_xml_elements_data(j));
81 END LOOP;
82
83 write_msg(' test4::');
84 dbms_sql.bind_variable (g_cursor, ':p1', p_prim_key);
85
86 l_status := dbms_sql.EXECUTE(g_cursor);
87 dbms_sql.close_cursor (g_cursor);
88
89 EXCEPTION
90 WHEN OTHERS THEN
91 dbms_sql.close_cursor (g_cursor);
92 --IF (AMS_DEBUG_HIGH_ON) THENAMS_UTILITY_PVT.debug_message('Error Update_Xml_Source_Lines_Util in cursor execution');END IF;
93 l_error := SQLERRM;
94 IF (AMS_DEBUG_HIGH_ON) THEN
95
96 AMS_UTILITY_PVT.debug_message(l_error);
97 END IF;
98 --write_msg(' test5::' || l_error);
99 RAISE FND_API.G_EXC_ERROR;
100 END;
101
102 -- Standard check for p_commit
103 IF FND_API.to_Boolean( p_commit) THEN
104 COMMIT WORK;
105 END IF;
106
107 EXCEPTION
108 WHEN FND_API.G_EXC_ERROR THEN
109 x_return_status := FND_API.G_RET_STS_ERROR;
110 --IF (AMS_DEBUG_HIGH_ON) THENAMS_UTILITY_PVT.debug_message('Private API: Update_Xml_Source_Lines_Util expected');END IF;
111 -- Standard call to get message count and if count=1, get the message
112 FND_MSG_PUB.Count_And_Get (
113 p_encoded => FND_API.G_FALSE,
114 p_count => x_msg_count,
115 p_data => x_msg_data
116 );
117 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
118 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
119 --IF (AMS_DEBUG_HIGH_ON) THENAMS_UTILITY_PVT.debug_message('Private API: Update_Xml_Source_Lines_Util unexpected');END IF;
120 -- Standard call to get message count and if count=1, get the message
121 FND_MSG_PUB.Count_And_Get (
122 p_encoded => FND_API.G_FALSE,
123 p_count => x_msg_count,
124 p_data => x_msg_data
125 );
126 WHEN OTHERS THEN
127 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
128 --IF (AMS_DEBUG_HIGH_ON) THENAMS_UTILITY_PVT.debug_message('Private API: Update_Xml_Source_Lines_Util OTHERS');END IF;
129 -- Standard call to get message count and if count=1, get the message
130 FND_MSG_PUB.Count_And_Get (
131 p_encoded => FND_API.G_FALSE,
132 p_count => x_msg_count,
133 p_data => x_msg_data
134 );
135 END Update_Xml_Source_Lines_Util;
136
137 PROCEDURE Update_B2C_Xml_Source_lines (
138 x_return_status OUT NOCOPY VARCHAR2,
139 x_msg_count OUT NOCOPY NUMBER,
140 x_msg_data OUT NOCOPY VARCHAR2,
141 p_xml_element_rec IN AMS_IMP_XML_ELEMENTS%ROWTYPE,
142 p_xml_element_ids IN num_data_set_type_w,
143 p_xml_elements_data IN varchar2_2000_set_type,
144 p_xml_elements_col_name IN varchar2_2000_set_type,
145 p_commit IN VARCHAR2
146 )
147 IS
148 l_column_name AMS_IMP_XML_ELEMENTS.COLUMN_NAME%TYPE;
149 l_source_system_column_name VARCHAR2(2000);
150 l_update_element_id NUMBER;
151 l_element_rec AMS_IMP_XML_ELEMENTS%ROWTYPE;
152
153 BEGIN
154 l_column_name := UPPER (p_xml_element_rec.COLUMN_NAME);
155 write_msg(' l_column_name::'||l_column_name );
156 IF l_column_name IS NOT NULL AND l_column_name = 'PERSON' THEN -- PERSON
157 --('PERSON', 'PERSON_FIRST_NAME', 'PERSON_MIDDLE_NAME',
158 -- 'PERSON_LAST_NAME', 'PERSON_NAME_SUFFIX', 'PERSON_TITLE', 'SALUTATION', 'PARTY_ID') THEN --PERSON
159 l_source_system_column_name := 'PER_IMP_XML_ELEMENT_ID';
160 ELSIF l_column_name IS NOT NULL AND l_column_name = 'ADDRESS' THEN -- ADDRESS
161 -- ('ADDRESS', 'ADDRESS1', 'ADDRESS2', 'ADDRESS3', 'ADDRESS4',
162 -- 'CITY', 'COUNTY', 'PROVINCE', 'STATE', 'POSTAL_CODE',
163 -- 'COUNTRY', 'ADDRESS_LINES_PHONETIC', 'PO_BOX_NUMBER',
164 -- 'HOUSE_NUMBER', 'STREET_SUFFIX', 'STREET', 'STREET_NUMBER',
165 -- 'FLOOR', 'SUITE', 'POSTAL_PLUS4_CODE', 'IDENTIFYING_ADDRESS_FLAG') THEN
166 l_source_system_column_name := 'ADD_IMP_XML_ELEMENT_ID';
167 ELSIF l_column_name IS NOT NULL AND l_column_name = 'PHONE_INFO' THEN --PHONE_INFO
168 -- ('PHONE_INFO', 'PHONE_COUNTRY_CODE', 'PHONE_AREA_CODE', 'PHONE_NUMBER', 'PHONE_EXTENSION') THEN
169 l_source_system_column_name := 'CP_IMP_XML_ELEMENT_ID';
170 ELSIF l_column_name IS NOT NULL AND l_column_name = 'EMAIL_INFO' THEN --EMAIL_INFO
171 l_source_system_column_name := 'EM_IMP_XML_ELEMENT_ID';
172 END IF;
173
174 Update_Xml_Source_Lines_Util (
175 p_view_name => G_B2C_VIEW_NAME,
176 p_commit => p_commit,
177 p_column_name => l_source_system_column_name,
178 p_prim_key => p_xml_element_rec.IMP_XML_ELEMENT_ID,
179 p_xml_elements_data => p_xml_elements_data,
180 p_xml_elements_col_name => p_xml_elements_col_name,
181 x_return_status => x_return_status,
182 x_msg_count => x_msg_count,
183 x_msg_data => x_msg_data
184 );
185
186 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
187 RAISE FND_API.G_EXC_ERROR;
188 END IF;
189
190 EXCEPTION
191 WHEN FND_API.G_EXC_ERROR THEN
192 x_return_status := FND_API.G_RET_STS_ERROR;
193 --IF (AMS_DEBUG_HIGH_ON) THENAMS_UTILITY_PVT.debug_message('Private API: Update_B2B_Xml_Source_lines expected');END IF;
194 -- Standard call to get message count and if count=1, get the message
195 FND_MSG_PUB.Count_And_Get (
196 p_encoded => FND_API.G_FALSE,
197 p_count => x_msg_count,
198 p_data => x_msg_data
199 );
200 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
201 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
202 --IF (AMS_DEBUG_HIGH_ON) THENAMS_UTILITY_PVT.debug_message('Private API: Update_B2B_Xml_Source_lines unexpected');END IF;
203 -- Standard call to get message count and if count=1, get the message
204 FND_MSG_PUB.Count_And_Get (
205 p_encoded => FND_API.G_FALSE,
206 p_count => x_msg_count,
207 p_data => x_msg_data
208 );
209 WHEN OTHERS THEN
210 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
211 --IF (AMS_DEBUG_HIGH_ON) THENAMS_UTILITY_PVT.debug_message('Private API: Update_B2B_Xml_Source_lines OTHERS');END IF;
212 -- Standard call to get message count and if count=1, get the message
213 FND_MSG_PUB.Count_And_Get (
214 p_encoded => FND_API.G_FALSE,
215 p_count => x_msg_count,
216 p_data => x_msg_data
217 );
218 END;
219
220 PROCEDURE Update_B2B_Xml_Source_lines (
221 x_return_status OUT NOCOPY VARCHAR2,
222 x_msg_count OUT NOCOPY NUMBER,
223 x_msg_data OUT NOCOPY VARCHAR2,
224 p_xml_element_rec IN AMS_IMP_XML_ELEMENTS%ROWTYPE,
225 p_xml_element_ids IN num_data_set_type_w,
226 p_xml_elements_data IN varchar2_2000_set_type,
227 p_xml_elements_col_name IN varchar2_2000_set_type,
228 p_commit IN VARCHAR2
229 )
230 IS
231 l_column_name AMS_IMP_XML_ELEMENTS.COLUMN_NAME%TYPE;
232 l_source_system_column_name VARCHAR2(2000);
233 l_update_element_id NUMBER;
234 l_element_rec AMS_IMP_XML_ELEMENTS%ROWTYPE;
235
236 l_update_statement VARCHAR2(2000) := ' UPDATE ' || G_B2B_VIEW_NAME
237 || ' SET LOAD_STATUS = ''RELOAD'', ';
238
239 BEGIN
240 l_column_name := UPPER (p_xml_element_rec.COLUMN_NAME);
241 IF l_column_name IS NOT NULL AND l_column_name = 'ORGANIZATION' THEN --ORGANIZATION
242 --('PARTY_NAME', 'FISCAL_YEAREND_MONTH',
243 -- 'DUNS_NUMBER', 'EMPLOYEES_TOTAL', 'LINE_OF_BUSINESS', 'YEAR_ESTABLISHED',
244 -- 'TAX_REFERENCE', 'CEO_NAME', 'SIC_CODE', 'SIC_CODE_TYPE', 'ANALYSIS_FY',
245 -- 'CURR_FY_POTENTIAL_REVENUE', 'NEXT_FY_POTENTIAL_REVENUE', 'GSA_INDICATOR_FLAG',
246 -- 'MISSION_STATEMENT', 'ORGANIZATION_NAME_PHONETIC', 'CATEGORY_CODE',
247 -- 'JGZZ_FISCAL_CODE', 'PARTY_ID') THEN --organization
248 l_source_system_column_name := 'ORG_IMP_XML_ELEMENT_ID';
249 ELSIF l_column_name IS NOT NULL AND l_column_name = 'ADDRESS' THEN --ADDRESS
250 -- ('ADDRESS1', 'ADDRESS2', 'ADDRESS3', 'ADDRESS4',
251 -- 'CITY', 'COUNTY', 'PROVINCE', 'STATE', 'POSTAL_CODE',
252 -- 'COUNTRY', 'ADDRESS_LINES_PHONETIC', 'PO_BOX_NUMBER',
253 -- 'HOUSE_NUMBER', 'STREET_SUFFIX', 'STREET', 'STREET_NUMBER',
254 -- 'FLOOR', 'SUITE', 'POSTAL_PLUS4_CODE','IDENTIFYING_ADDRESS_FLAG') THEN
255 l_source_system_column_name := 'ADD_IMP_XML_ELEMENT_ID';
256 ELSIF l_column_name IS NOT NULL AND l_column_name = 'CONTACT' THEN
257 -- ('PERSON_FIRST_NAME', 'PERSON_MIDDLE_NAME', 'PERSON_NAME_SUFFIX',
258 -- 'PERSON_TITLE', 'PERSON_LAST_NAME', 'DEPARTMENT',
259 -- 'JOB_TITLE', 'DECISION_MAKER_FLAG') THEN
260 l_source_system_column_name := 'OCONT_IMP_XML_ELEMENT_ID';
261 ELSIF l_column_name IS NOT NULL AND l_column_name = 'PHONE_INFO' THEN -- PHONE_INFO
262 -- ('PHONE_COUNTRY_CODE', 'PHONE_AREA_CODE',
263 -- 'PHONE_NUMBER', 'PHONE_EXTENSION') THEN
264 l_source_system_column_name := 'CP_IMP_XML_ELEMENT_ID';
265 ELSIF l_column_name IS NOT NULL AND l_column_name = 'EMAIL_INFO' THEN --EMAIL_INFO
266 l_source_system_column_name := 'EM_IMP_XML_ELEMENT_ID';
267 END IF;
268
269 Update_Xml_Source_Lines_Util (
270 p_view_name => G_B2B_VIEW_NAME,
271 p_commit => p_commit,
272 p_column_name => l_source_system_column_name,
273 p_prim_key => p_xml_element_rec.IMP_XML_ELEMENT_ID,
274 p_xml_elements_data => p_xml_elements_data,
275 p_xml_elements_col_name => p_xml_elements_col_name,
276 x_return_status => x_return_status,
277 x_msg_count => x_msg_count,
278 x_msg_data => x_msg_data
279 );
280
281 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
282 RAISE FND_API.G_EXC_ERROR;
283 END IF;
284
285 /****
286 AMS_Import_XML_PVT.Get_Parent_Node (
287 p_imp_xml_element_id => p_xml_element_rec.IMP_XML_ELEMENT_ID,
288 x_node_rec => l_element_rec,
289 x_return_status => x_return_status,
290 x_msg_data => x_msg_data);
291 l_update_element_id := l_element_rec.imp_xml_element_id;
292 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
293 RAISE FND_API.G_EXC_ERROR;
294 END IF;
295
296 l_update_statement := l_update_statement || l_column_name
297 || ' = :v1 WHERE ' || l_source_system_column_name || ' = :p1';
298
299 EXECUTE IMMEDIATE
300 l_update_statement USING p_xml_element_rec.data, l_update_element_id;
301 ****/
302
303 EXCEPTION
304 WHEN FND_API.G_EXC_ERROR THEN
305 x_return_status := FND_API.G_RET_STS_ERROR;
306 --IF (AMS_DEBUG_HIGH_ON) THENAMS_UTILITY_PVT.debug_message('Private API: Update_B2B_Xml_Source_lines expected');END IF;
307 -- Standard call to get message count and if count=1, get the message
308 FND_MSG_PUB.Count_And_Get (
309 p_encoded => FND_API.G_FALSE,
310 p_count => x_msg_count,
311 p_data => x_msg_data
312 );
313 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
314 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
315 --IF (AMS_DEBUG_HIGH_ON) THENAMS_UTILITY_PVT.debug_message('Private API: Update_B2B_Xml_Source_lines unexpected');END IF;
316 -- Standard call to get message count and if count=1, get the message
317 FND_MSG_PUB.Count_And_Get (
318 p_encoded => FND_API.G_FALSE,
319 p_count => x_msg_count,
320 p_data => x_msg_data
321 );
322 WHEN OTHERS THEN
323 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
324 --IF (AMS_DEBUG_HIGH_ON) THENAMS_UTILITY_PVT.debug_message('Private API: Update_B2B_Xml_Source_lines OTHERS');END IF;
325 -- Standard call to get message count and if count=1, get the message
326 FND_MSG_PUB.Count_And_Get (
327 p_encoded => FND_API.G_FALSE,
328 p_count => x_msg_count,
329 p_data => x_msg_data
330 );
331 END;
332
333 -- Set the load_status for the current element as "RELOAD", update all the child elements
334 -- with new values and update source lines correspondingly
335 PROCEDURE Update_Error_Xml_Element (
336 p_api_version_number IN NUMBER,
337 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
338 p_commit IN VARCHAR2 := FND_API.G_FALSE,
339 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
340 p_xml_element_rec IN xml_element_rec_type,
341 p_xml_element_ids IN num_data_set_type_w,
342 p_xml_elements_data IN varchar2_2000_set_type,
343 p_xml_elements_col_name IN varchar2_2000_set_type,
344 x_return_status OUT NOCOPY VARCHAR2,
345 x_msg_count OUT NOCOPY NUMBER,
346 x_msg_data OUT NOCOPY VARCHAR2,
347 x_object_version_number OUT NOCOPY NUMBER
348 )
349 IS
350
351 CURSOR c_get_xml_element(l_imp_xml_element_id NUMBER) IS
352 SELECT *
353 FROM AMS_IMP_XML_ELEMENTS
354 WHERE imp_xml_element_id = l_imp_xml_element_id;
355 -- Hint: Developer need to provide Where clause
356
357 CURSOR c_get_import_type (p_header_id NUMBER) IS
361
358 SELECT IMPORT_TYPE
359 FROM AMS_IMP_LIST_HEADERS_ALL
360 WHERE IMPORT_LIST_HEADER_ID = p_header_id;
362 CURSOR c_get_header_id (p_imp_xml_doc_id NUMBER ) IS
363 SELECT IMPORT_LIST_HEADER_ID
364 FROM AMS_IMP_DOCUMENTS
365 WHERE imp_document_id = p_imp_xml_doc_id;
366
367 l_header_id c_get_header_id%ROWTYPE;
368
369 l_import_type c_get_import_type%rowtype;
370
371 L_API_NAME CONSTANT VARCHAR2(30) := 'Update_Error_Xml_Element';
372 L_API_VERSION_NUMBER CONSTANT NUMBER := 1.0;
373
374 -- Local Variables
375 l_object_version_number NUMBER;
376 l_IMP_XML_ELEMENT_ID NUMBER;
377 --l_ref_xml_element_rec c_get_Xml_Element%ROWTYPE ;
378 --l_tar_xml_element_rec AMS_Xml_Element_PVT.xml_element_rec_type := P_xml_element_rec;
379
380 l_return_status VARCHAR2(1);
381 l_msg_count NUMBER;
382 l_msg_data VARCHAR2 (2000);
383
384 l_element_rec AMS_IMP_XML_ELEMENTS%ROWTYPE;
385
386 BEGIN
387
388 -- Standard Start of API savepoint
389 SAVEPOINT Update_Error_Xml_Element;
390
391 -- Standard call to check for call compatibility.
392 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
393 p_api_version_number,
394 l_api_name,
395 G_PKG_NAME) THEN
396 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
397 END IF;
398
399 -- Initialize message list if p_init_msg_list is set to TRUE.
400 IF FND_API.to_Boolean( p_init_msg_list )
401 THEN
402 FND_MSG_PUB.initialize;
403 END IF;
404
405 -- Debug Message
406 IF (AMS_DEBUG_HIGH_ON) THEN
407
408 AMS_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'start');
409 END IF;
410
411 -- Initialize API return status to SUCCESS
412 x_return_status := FND_API.G_RET_STS_SUCCESS;
413
414 -- Debug Message
415 IF (AMS_DEBUG_HIGH_ON) THEN
416
417 AMS_UTILITY_PVT.debug_message('Private API: - Open Cursor to Select');
418 END IF;
419
420 /****
421 If (l_tar_xml_element_rec.object_version_number is NULL or
422 l_tar_xml_element_rec.object_version_number = FND_API.G_MISS_NUM ) Then
423 AMS_Utility_PVT.Error_Message(p_message_name => 'API_VERSION_MISSING',
424 p_token_name => 'COLUMN',
425 p_token_value => 'Last_Update_Date') ;
426 raise FND_API.G_EXC_ERROR;
427 End if;
428
429 IF ( P_validation_level >= FND_API.G_VALID_LEVEL_FULL) THEN
430 -- Debug message
431 IF (AMS_DEBUG_HIGH_ON) THEN
432
433 AMS_UTILITY_PVT.debug_message('Private API: Validate_Xml_Element');
434 END IF;
435
436 -- Invoke validation procedures
437 Validate_xml_element(
438 p_api_version_number => 1.0,
439 p_init_msg_list => FND_API.G_FALSE,
440 p_validation_level => p_validation_level,
441 p_validation_mode => JTF_PLSQL_API.g_update,
442 p_xml_element_rec => p_xml_element_rec,
443 x_return_status => x_return_status,
444 x_msg_count => x_msg_count,
445 x_msg_data => x_msg_data);
446 END IF;
447
448 IF x_return_status<>FND_API.G_RET_STS_SUCCESS THEN
449 RAISE FND_API.G_EXC_ERROR;
450 END IF;
451
455 AMS_UTILITY_PVT.debug_message('Private API: Calling update table handler');
452 -- Debug Message
453 IF (AMS_DEBUG_HIGH_ON) THEN
454
456 END IF;
457
458 -- Invoke table handler(AMS_IMP_XML_ELEMENTS_PKG.Update_Row)
459 AMS_IMP_XML_ELEMENTS_PKG.Update_Row (
460 p_imp_xml_element_id => p_xml_element_rec.imp_xml_element_id,
461 p_last_updated_by => FND_GLOBAL.user_id,
462 p_object_version_number => p_xml_element_rec.object_version_number,
463 -- p_created_by => p_xml_element_rec.created_by,
464 p_last_update_login => FND_GLOBAL.conc_login_id,
465 p_last_update_date => SYSDATE,
466 -- p_creation_date => p_xml_element_rec.creation_date,
467 p_imp_xml_document_id => p_xml_element_rec.imp_xml_document_id,
468 p_order_initial => p_xml_element_rec.order_initial,
469 p_order_final => p_xml_element_rec.order_final,
470 p_column_name => p_xml_element_rec.column_name,
471 p_data => p_xml_element_rec.data,
472 p_num_attr => p_xml_element_rec.num_attr,
473 p_data_type => p_xml_element_rec.data_type,
474 p_load_status => 'RELOAD', --p_xml_element_rec.load_status,
475 p_error_text => NULL); --p_xml_element_rec.error_text);
476
477 IF (AMS_DEBUG_HIGH_ON) THEN
478
479
480
481 AMS_UTILITY_PVT.debug_message('Private API: Returned update table handler');
482
483 END IF;
484 ****/
485
486 AMS_Import_XML_PVT.Get_Parent_Node (
487 p_imp_xml_element_id => p_xml_element_ids(1),
488 x_node_rec => l_element_rec,
489 x_return_status => x_return_status,
490 x_msg_data => x_msg_data);
491
492 --x_object_version_number := p_xml_element_rec.object_version_number + 1;
493
494 --IF l_ref_xml_element_rec.data IS NULL THEN
495 -- l_ref_xml_element_rec.data := FND_API.g_miss_char;
496 --END IF;
497
498 UPDATE ams_imp_xml_elements
499 SET LOAD_STATUS = 'RELOAD'
500 WHERE IMP_XML_ELEMENT_ID = l_element_rec.imp_xml_element_id;
501
502 OPEN c_get_header_id (l_element_rec.IMP_XML_DOCUMENT_ID);
503 FETCH c_get_header_id INTO l_header_id;
504 CLOSE c_get_header_id;
505
506 write_msg(' a test 01::l_element_rec.imp_xml_element_id' || l_element_rec.imp_xml_element_id
507 || ' and the l_header_id.IMPORT_LIST_HEADER_ID::'
508 || l_header_id.IMPORT_LIST_HEADER_ID );
509 IF l_header_id.IMPORT_LIST_HEADER_ID IS NOT NULL THEN
510 UPDATE ams_imp_list_headers_all
511 SET execute_mode = 'R'
512 WHERE import_list_header_id = l_header_id.IMPORT_LIST_HEADER_ID;
513
514 UPDATE ams_imp_source_lines
515 SET LOAD_STATUS = 'RELOAD'
516 WHERE IMPORT_LIST_HEADER_ID = l_header_id.IMPORT_LIST_HEADER_ID;
517 END IF;
518
519 write_msg(' a test 02::' );
520 FORALL l_count IN p_xml_element_ids.FIRST .. p_xml_element_ids.LAST
521 UPDATE ams_imp_xml_elements
522 SET DATA = p_xml_elements_data (l_count)
523 WHERE IMP_XML_ELEMENT_ID = p_xml_element_ids (l_count);
524
525 OPEN c_get_import_type (l_header_id.IMPORT_LIST_HEADER_ID);
526 FETCH c_get_import_type INTO l_import_type;
527 CLOSE c_get_import_type;
528
529 write_msg(' a test 03:: l_import_type.IMPORT_TYPE::' || l_import_type.IMPORT_TYPE);
530 IF l_import_type.IMPORT_TYPE IS NOT NULL AND UPPER (l_import_type.IMPORT_TYPE) = 'B2B' THEN
531 Update_B2B_Xml_Source_lines (
532 x_return_status => l_return_status,
533 x_msg_count => l_msg_count,
534 x_msg_data => l_msg_data,
535 p_xml_element_rec => l_element_rec,
536 p_xml_element_ids => p_xml_element_ids,
537 p_xml_elements_data => p_xml_elements_data,
538 p_xml_elements_col_name => p_xml_elements_col_name,
539 p_commit => FND_API.G_FALSE);
540 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
541 RAISE FND_API.G_EXC_ERROR;
542 END IF;
543 ELSIF l_import_type.IMPORT_TYPE IS NOT NULL AND UPPER (l_import_type.IMPORT_TYPE) = 'B2C' THEN
544 Update_B2C_Xml_Source_lines (
545 x_return_status => l_return_status,
546 x_msg_count => l_msg_count,
547 x_msg_data => l_msg_data,
548 p_xml_element_rec => l_element_rec,
549 p_xml_element_ids => p_xml_element_ids,
550 p_xml_elements_data => p_xml_elements_data ,
551 p_xml_elements_col_name => p_xml_elements_col_name,
552 p_commit => FND_API.G_FALSE);
553 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
554 RAISE FND_API.G_EXC_ERROR;
555 END IF;
556 END IF;
557 --END IF; --IF l_ref_xml_element_rec.imp_xml_element_id IS NOT NULL
558
559 -- Standard check for p_commit
560 IF FND_API.to_Boolean( p_commit) THEN
561 COMMIT WORK;
562 END IF;
563
564 -- Debug Message
565 IF (AMS_DEBUG_HIGH_ON) THEN
566
567 AMS_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'end');
568 END IF;
569
570 -- Standard call to get message count and if count is 1, get message info.
571 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
572 p_data => x_msg_data);
573
574 EXCEPTION
575 WHEN AMS_Utility_PVT.resource_locked THEN
576 x_return_status := FND_API.g_ret_sts_error;
577 AMS_Utility_PVT.Error_Message(p_message_name => 'AMS_API_RESOURCE_LOCKED');
578
579 WHEN FND_API.G_EXC_ERROR THEN
580 ROLLBACK TO Update_Error_Xml_Element;
581 x_return_status := FND_API.G_RET_STS_ERROR;
582 IF (AMS_DEBUG_HIGH_ON) THEN
583
584 AMS_UTILITY_PVT.debug_message('Private API: Exception G_EXC_ERROR');
585 END IF;
586 -- Standard call to get message count and if count=1, get the message
587 FND_MSG_PUB.Count_And_Get (
588 p_encoded => FND_API.G_FALSE,
592 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
589 p_count => x_msg_count,
590 p_data => x_msg_data );
591
593 ROLLBACK TO Update_Error_Xml_Element;
594 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
595 IF (AMS_DEBUG_HIGH_ON) THEN
596
597 AMS_UTILITY_PVT.debug_message('Private API: Exception G_EXC_UNEXPECTED_ERROR');
598 END IF;
599 -- Standard call to get message count and if count=1, get the message
600 FND_MSG_PUB.Count_And_Get (
601 p_encoded => FND_API.G_FALSE,
602 p_count => x_msg_count,
603 p_data => x_msg_data
604 );
605
606 WHEN OTHERS THEN
607 ROLLBACK TO Update_Error_Xml_Element;
608 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
609 IF (AMS_DEBUG_HIGH_ON) THEN
610
611 AMS_UTILITY_PVT.debug_message('Private API: Exception OTHERS');
612 END IF;
613 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
614 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
615 END IF;
616 -- Standard call to get message count and if count=1, get the message
617 FND_MSG_PUB.Count_And_Get (
618 p_encoded => FND_API.G_FALSE,
619 p_count => x_msg_count,
620 p_data => x_msg_data);
621 END Update_Error_Xml_Element;
622
623 -- Hint: Primary key needs to be returned.
624 PROCEDURE Create_Xml_Element(
625 p_api_version_number IN NUMBER,
626 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
627 p_commit IN VARCHAR2 := FND_API.G_FALSE,
628 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
629
630 x_return_status OUT NOCOPY VARCHAR2,
631 x_msg_count OUT NOCOPY NUMBER,
632 x_msg_data OUT NOCOPY VARCHAR2,
633
634 p_xml_element_rec IN xml_element_rec_type := g_miss_xml_element_rec,
635 x_imp_xml_element_id OUT NOCOPY NUMBER
636 )
637
638 IS
639 L_API_NAME CONSTANT VARCHAR2(30) := 'Create_Xml_Element';
640 L_API_VERSION_NUMBER CONSTANT NUMBER := 1.0;
641 l_return_status_full VARCHAR2(1);
642 l_object_version_number NUMBER := 1;
643 l_org_id NUMBER := FND_API.G_MISS_NUM;
644 l_IMP_XML_ELEMENT_ID NUMBER;
645 l_dummy NUMBER;
646
647 CURSOR c_id IS
648 SELECT AMS_IMP_XML_ELEMENTS_s.NEXTVAL
649 FROM dual;
650
651 CURSOR c_id_exists (l_id IN NUMBER) IS
652 SELECT 1
653 FROM AMS_IMP_XML_ELEMENTS
654 WHERE IMP_XML_ELEMENT_ID = l_id;
655
656 BEGIN
657 -- Standard Start of API savepoint
658 SAVEPOINT CREATE_Xml_Element_PVT;
659
660 -- Standard call to check for call compatibility.
661 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
662 p_api_version_number,
663 l_api_name,
664 G_PKG_NAME)
665 THEN
666 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
667 END IF;
668
669 -- Initialize message list if p_init_msg_list is set to TRUE.
670 IF FND_API.to_Boolean( p_init_msg_list )
671 THEN
672 FND_MSG_PUB.initialize;
673 END IF;
674
675 -- Debug Message
676 IF (AMS_DEBUG_HIGH_ON) THEN
677
678 AMS_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'start');
679 END IF;
680
681
682 -- Initialize API return status to SUCCESS
683 x_return_status := FND_API.G_RET_STS_SUCCESS;
684
685 -- Local variable initialization
686
687 IF p_xml_element_rec.IMP_XML_ELEMENT_ID IS NULL OR
688 p_xml_element_rec.IMP_XML_ELEMENT_ID = FND_API.g_miss_num THEN
689 LOOP
690 l_dummy := NULL;
691 OPEN c_id;
692 FETCH c_id INTO l_IMP_XML_ELEMENT_ID;
693 CLOSE c_id;
694
695 OPEN c_id_exists(l_IMP_XML_ELEMENT_ID);
696 FETCH c_id_exists INTO l_dummy;
697 CLOSE c_id_exists;
698 EXIT WHEN l_dummy IS NULL;
699 END LOOP;
700 ELSE
701 l_imp_xml_element_id := p_xml_element_rec.imp_xml_element_id;
702 END IF;
703
704 -- =========================================================================
705 -- Validate Environment
706 -- =========================================================================
707
708 IF FND_GLOBAL.User_Id IS NULL
709 THEN
710 AMS_Utility_PVT.Error_Message(p_message_name => 'USER_PROFILE_MISSING');
711 RAISE FND_API.G_EXC_ERROR;
712 END IF;
713
714 IF ( P_validation_level >= FND_API.G_VALID_LEVEL_FULL)
715 THEN
716 -- Debug message
717 IF (AMS_DEBUG_HIGH_ON) THEN
718
719 AMS_UTILITY_PVT.debug_message('Private API: Validate_Xml_Element');
720 END IF;
721
722 -- Invoke validation procedures
723 Validate_xml_element(
724 p_api_version_number => 1.0,
725 p_init_msg_list => FND_API.G_FALSE,
726 p_validation_level => p_validation_level,
727 p_validation_mode => JTF_PLSQL_API.g_create,
728 p_xml_element_rec => p_xml_element_rec,
729 x_return_status => x_return_status,
730 x_msg_count => x_msg_count,
731 x_msg_data => x_msg_data);
732 END IF;
733
734 IF x_return_status<>FND_API.G_RET_STS_SUCCESS THEN
735 RAISE FND_API.G_EXC_ERROR;
736 END IF;
737
738
739 -- Debug Message
743 END IF;
740 IF (AMS_DEBUG_HIGH_ON) THEN
741
742 AMS_UTILITY_PVT.debug_message( 'Private API: Calling create table handler');
744
745 -- Invoke table handler(AMS_IMP_XML_ELEMENTS_PKG.Insert_Row)
746 AMS_IMP_XML_ELEMENTS_PKG.Insert_Row(
747 px_imp_xml_element_id => l_imp_xml_element_id,
748 p_last_updated_by => FND_GLOBAL.user_id,
749 px_object_version_number => l_object_version_number,
750 p_created_by => FND_GLOBAL.user_id,
751 p_last_update_login => FND_GLOBAL.conc_login_id,
752 p_last_update_date => SYSDATE,
753 p_creation_date => SYSDATE,
754 p_imp_xml_document_id => p_xml_element_rec.imp_xml_document_id,
755 p_order_initial => p_xml_element_rec.order_initial,
756 p_order_final => p_xml_element_rec.order_final,
757 p_column_name => p_xml_element_rec.column_name,
758 p_data => p_xml_element_rec.data,
759 p_num_attr => p_xml_element_rec.num_attr,
760 p_data_type => p_xml_element_rec.data_type,
761 p_load_status => p_xml_element_rec.load_status,
762 p_error_text => p_xml_element_rec.error_text);
763
764 x_imp_xml_element_id := l_imp_xml_element_id;
765 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
766 RAISE FND_API.G_EXC_ERROR;
767 END IF;
768 --
769 -- End of API body
770 --
771
772 -- Standard check for p_commit
773 IF FND_API.to_Boolean( p_commit )
774 THEN
775 COMMIT WORK;
776 END IF;
777
778 -- Debug Message
779 IF (AMS_DEBUG_HIGH_ON) THEN
780
781 AMS_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'end');
782 END IF;
783
784 -- Standard call to get message count and if count is 1, get message info.
785 FND_MSG_PUB.Count_And_Get
786 (p_count => x_msg_count,
787 p_data => x_msg_data
788 );
789 EXCEPTION
790
791 WHEN AMS_Utility_PVT.resource_locked THEN
792 x_return_status := FND_API.g_ret_sts_error;
793 AMS_Utility_PVT.Error_Message(p_message_name => 'AMS_API_RESOURCE_LOCKED');
794
795 WHEN FND_API.G_EXC_ERROR THEN
796 ROLLBACK TO CREATE_Xml_Element_PVT;
797 x_return_status := FND_API.G_RET_STS_ERROR;
798 -- Standard call to get message count and if count=1, get the message
799 FND_MSG_PUB.Count_And_Get (
800 p_encoded => FND_API.G_FALSE,
801 p_count => x_msg_count,
802 p_data => x_msg_data
803 );
804
805 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
806 ROLLBACK TO CREATE_Xml_Element_PVT;
807 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
808 -- Standard call to get message count and if count=1, get the message
809 FND_MSG_PUB.Count_And_Get (
810 p_encoded => FND_API.G_FALSE,
811 p_count => x_msg_count,
812 p_data => x_msg_data
813 );
814
815 WHEN OTHERS THEN
816 ROLLBACK TO CREATE_Xml_Element_PVT;
817 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
818 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
819 THEN
820 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
821 END IF;
822 -- Standard call to get message count and if count=1, get the message
823 FND_MSG_PUB.Count_And_Get (
824 p_encoded => FND_API.G_FALSE,
825 p_count => x_msg_count,
826 p_data => x_msg_data
827 );
828 End Create_Xml_Element;
829
830
831 PROCEDURE Update_Xml_Element(
832 p_api_version_number IN NUMBER,
833 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
834 p_commit IN VARCHAR2 := FND_API.G_FALSE,
835 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
836
837 x_return_status OUT NOCOPY VARCHAR2,
838 x_msg_count OUT NOCOPY NUMBER,
839 x_msg_data OUT NOCOPY VARCHAR2,
840
841 p_xml_element_rec IN xml_element_rec_type,
842 x_object_version_number OUT NOCOPY NUMBER
843 )
844
845 IS
846
847 CURSOR c_get_xml_element(l_imp_xml_element_id NUMBER) IS
848 SELECT *
849 FROM AMS_IMP_XML_ELEMENTS
850 WHERE imp_xml_element_id = l_imp_xml_element_id;
851 -- Hint: Developer need to provide Where clause
852
853 CURSOR c_get_import_type (p_header_id NUMBER) IS
854 SELECT IMPORT_TYPE
855 FROM AMS_IMP_LIST_HEADERS_ALL
856 WHERE IMPORT_LIST_HEADER_ID = p_header_id;
857
858 CURSOR c_get_header_id (p_imp_xml_doc_id NUMBER ) IS
859 SELECT IMPORT_LIST_HEADER_ID
860 FROM AMS_IMP_DOCUMENTS
861 WHERE imp_document_id = p_imp_xml_doc_id;
862
863 l_header_id c_get_header_id%ROWTYPE;
864
865 l_import_type c_get_import_type%rowtype;
866
867
868 L_API_NAME CONSTANT VARCHAR2(30) := 'Update_Xml_Element';
869 L_API_VERSION_NUMBER CONSTANT NUMBER := 1.0;
870 -- Local Variables
871 l_object_version_number NUMBER;
872 l_IMP_XML_ELEMENT_ID NUMBER;
873 l_ref_xml_element_rec c_get_Xml_Element%ROWTYPE ;
874 l_tar_xml_element_rec AMS_Xml_Element_PVT.xml_element_rec_type := P_xml_element_rec;
875
876 l_return_status VARCHAR2(1);
877 l_msg_count NUMBER;
878 l_msg_data VARCHAR2 (2000);
879
880 BEGIN
881 -- Standard Start of API savepoint
882 SAVEPOINT UPDATE_Xml_Element_PVT;
883
884 -- Standard call to check for call compatibility.
885 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
889 THEN
886 p_api_version_number,
887 l_api_name,
888 G_PKG_NAME)
890 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
891 END IF;
892
893 -- Initialize message list if p_init_msg_list is set to TRUE.
894 IF FND_API.to_Boolean( p_init_msg_list )
895 THEN
896 FND_MSG_PUB.initialize;
897 END IF;
898
899 -- Debug Message
900 IF (AMS_DEBUG_HIGH_ON) THEN
901
902 AMS_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'start');
903 END IF;
904
905
906 -- Initialize API return status to SUCCESS
907 x_return_status := FND_API.G_RET_STS_SUCCESS;
908
909 -- Debug Message
910 IF (AMS_DEBUG_HIGH_ON) THEN
911
912 AMS_UTILITY_PVT.debug_message('Private API: - Open Cursor to Select');
913 END IF;
914
915 /*
916 OPEN c_get_Xml_Element( l_tar_xml_element_rec.imp_xml_element_id);
917
918 FETCH c_get_Xml_Element INTO l_ref_xml_element_rec ;
919
920 If ( c_get_Xml_Element%NOTFOUND) THEN
921 AMS_Utility_PVT.Error_Message(p_message_name => 'API_MISSING_UPDATE_TARGET',
922 p_token_name => 'INFO',
923 p_token_value => 'Xml_Element') ;
924 RAISE FND_API.G_EXC_ERROR;
925 END IF;
926 -- Debug Message
927 IF (AMS_DEBUG_HIGH_ON) THEN
928
929 AMS_UTILITY_PVT.debug_message('Private API: - Close Cursor');
930 END IF;
931 CLOSE c_get_Xml_Element;
932 */
933
934
935 If (l_tar_xml_element_rec.object_version_number is NULL or
936 l_tar_xml_element_rec.object_version_number = FND_API.G_MISS_NUM ) Then
937 AMS_Utility_PVT.Error_Message(p_message_name => 'API_VERSION_MISSING',
938 p_token_name => 'COLUMN',
939 p_token_value => 'Last_Update_Date') ;
940 raise FND_API.G_EXC_ERROR;
941 End if;
942 -- Check Whether record has been changed by someone else
943 If (l_tar_xml_element_rec.object_version_number <> l_ref_xml_element_rec.object_version_number) Then
944 AMS_Utility_PVT.Error_Message(p_message_name => 'API_RECORD_CHANGED',
945 p_token_name => 'INFO',
946 p_token_value => 'Xml_Element') ;
947 raise FND_API.G_EXC_ERROR;
948 End if;
949 IF ( P_validation_level >= FND_API.G_VALID_LEVEL_FULL)
950 THEN
951 -- Debug message
952 IF (AMS_DEBUG_HIGH_ON) THEN
953
954 AMS_UTILITY_PVT.debug_message('Private API: Validate_Xml_Element');
955 END IF;
956
957 -- Invoke validation procedures
958 Validate_xml_element(
959 p_api_version_number => 1.0,
960 p_init_msg_list => FND_API.G_FALSE,
961 p_validation_level => p_validation_level,
962 p_validation_mode => JTF_PLSQL_API.g_update,
963 p_xml_element_rec => p_xml_element_rec,
964 x_return_status => x_return_status,
965 x_msg_count => x_msg_count,
966 x_msg_data => x_msg_data);
967 END IF;
968
969 IF x_return_status<>FND_API.G_RET_STS_SUCCESS THEN
970 RAISE FND_API.G_EXC_ERROR;
971 END IF;
972
973
974 -- Debug Message
975 IF (AMS_DEBUG_HIGH_ON) THEN
976
977 AMS_UTILITY_PVT.debug_message('Private API: Calling update table handler');
978 END IF;
979
980 -- Invoke table handler(AMS_IMP_XML_ELEMENTS_PKG.Update_Row)
981 AMS_IMP_XML_ELEMENTS_PKG.Update_Row (
982 p_imp_xml_element_id => p_xml_element_rec.imp_xml_element_id,
983 p_last_updated_by => FND_GLOBAL.user_id,
984 p_object_version_number => p_xml_element_rec.object_version_number,
985 -- p_created_by => p_xml_element_rec.created_by,
986 p_last_update_login => FND_GLOBAL.conc_login_id,
987 p_last_update_date => SYSDATE,
988 -- p_creation_date => p_xml_element_rec.creation_date,
989 p_imp_xml_document_id => p_xml_element_rec.imp_xml_document_id,
990 p_order_initial => p_xml_element_rec.order_initial,
991 p_order_final => p_xml_element_rec.order_final,
992 p_column_name => p_xml_element_rec.column_name,
993 p_data => p_xml_element_rec.data,
994 p_num_attr => p_xml_element_rec.num_attr,
995 p_data_type => p_xml_element_rec.data_type,
996 p_load_status => 'RELOAD', --p_xml_element_rec.load_status,
997 p_error_text => NULL); --p_xml_element_rec.error_text);
998
999 IF (AMS_DEBUG_HIGH_ON) THEN
1000
1001
1002
1003 AMS_UTILITY_PVT.debug_message('Private API: Returned update table handler');
1004
1005 END IF;
1006
1007 x_object_version_number := p_xml_element_rec.object_version_number + 1;
1008
1009 OPEN c_get_Xml_Element(p_xml_element_rec.imp_xml_element_id);
1010 FETCH c_get_Xml_Element INTO l_ref_xml_element_rec;
1011 CLOSE c_get_Xml_Element;
1012
1013 IF l_ref_xml_element_rec.data IS NULL THEN
1014 l_ref_xml_element_rec.data := FND_API.g_miss_char;
1015 END IF;
1016
1017 --write_msg ('test1::id::' || l_ref_xml_element_rec.imp_xml_element_id
1018 -- || ' p data::' || p_xml_element_rec.data || ' ref data::' || l_ref_xml_element_rec.data);
1019 OPEN c_get_header_id (l_ref_xml_element_rec.imp_xml_document_id);
1020 FETCH c_get_header_id INTO l_header_id;
1021 CLOSE c_get_header_id;
1022
1023 IF l_header_id.IMPORT_LIST_HEADER_ID IS NOT NULL THEN
1024 UPDATE ams_imp_list_headers_all
1025 SET execute_mode = 'R'
1026 WHERE import_list_header_id = l_header_id.IMPORT_LIST_HEADER_ID;
1027 END IF;
1031 --l_ref_xml_element_rec.data <> p_xml_element_rec.data THEN
1028
1029 IF l_ref_xml_element_rec.imp_xml_element_id IS NOT NULL AND
1030 p_xml_element_rec.data IS NOT NULL THEN
1032 --write_msg ('test2');
1033 OPEN c_get_import_type (l_header_id.IMPORT_LIST_HEADER_ID);
1034 FETCH c_get_import_type INTO l_import_type;
1035 CLOSE c_get_import_type;
1036 --write_msg ('test3::' || l_import_type.IMPORT_TYPE);
1037 IF l_import_type.IMPORT_TYPE IS NOT NULL AND
1038 UPPER (l_import_type.IMPORT_TYPE) = 'B2B' THEN
1039 --Update_B2B_Xml_Source_lines (
1040 -- x_return_status => l_return_status,
1041 -- x_msg_count => l_msg_count,
1042 -- x_msg_data => l_msg_data,
1043 -- p_xml_element_rec => l_ref_xml_element_rec);
1044 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1045 RAISE FND_API.G_EXC_ERROR;
1046 END IF;
1047 ELSIF l_import_type.IMPORT_TYPE IS NOT NULL AND
1048 UPPER (l_import_type.IMPORT_TYPE) = 'B2C' THEN
1049 --Update_B2C_Xml_Source_lines (
1050 -- x_return_status => l_return_status,
1051 -- x_msg_count => l_msg_count,
1052 -- x_msg_data => l_msg_data,
1053 -- p_xml_element_rec => l_ref_xml_element_rec);
1054 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1055 RAISE FND_API.G_EXC_ERROR;
1056 END IF;
1057 END IF;
1058 END IF;
1059
1060 --
1061 -- End of API body.
1062 --
1063
1064 -- Standard check for p_commit
1065 IF FND_API.to_Boolean( p_commit )
1066 THEN
1067 COMMIT WORK;
1068 END IF;
1069
1070 -- Debug Message
1071 IF (AMS_DEBUG_HIGH_ON) THEN
1072
1073 AMS_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'end');
1074 END IF;
1075
1076 -- Standard call to get message count and if count is 1, get message info.
1077 FND_MSG_PUB.Count_And_Get
1078 (p_count => x_msg_count,
1079 p_data => x_msg_data
1080 );
1081 EXCEPTION
1082
1083 WHEN AMS_Utility_PVT.resource_locked THEN
1084 x_return_status := FND_API.g_ret_sts_error;
1085 AMS_Utility_PVT.Error_Message(p_message_name => 'AMS_API_RESOURCE_LOCKED');
1086
1087 WHEN FND_API.G_EXC_ERROR THEN
1088 ROLLBACK TO UPDATE_Xml_Element_PVT;
1089 x_return_status := FND_API.G_RET_STS_ERROR;
1090 IF (AMS_DEBUG_HIGH_ON) THEN
1091
1092 AMS_UTILITY_PVT.debug_message('Private API: Exception G_EXC_ERROR');
1093 END IF;
1094 -- Standard call to get message count and if count=1, get the message
1095 FND_MSG_PUB.Count_And_Get (
1096 p_encoded => FND_API.G_FALSE,
1097 p_count => x_msg_count,
1098 p_data => x_msg_data
1099 );
1100
1101 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1102 ROLLBACK TO UPDATE_Xml_Element_PVT;
1103 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1104 IF (AMS_DEBUG_HIGH_ON) THEN
1105
1106 AMS_UTILITY_PVT.debug_message('Private API: Exception G_EXC_UNEXPECTED_ERROR');
1107 END IF;
1108 -- Standard call to get message count and if count=1, get the message
1109 FND_MSG_PUB.Count_And_Get (
1110 p_encoded => FND_API.G_FALSE,
1111 p_count => x_msg_count,
1112 p_data => x_msg_data
1113 );
1114
1115 WHEN OTHERS THEN
1116 ROLLBACK TO UPDATE_Xml_Element_PVT;
1117 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1118 IF (AMS_DEBUG_HIGH_ON) THEN
1119
1120 AMS_UTILITY_PVT.debug_message('Private API: Exception OTHERS');
1121 END IF;
1122 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1123 THEN
1124 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
1125 END IF;
1126 -- Standard call to get message count and if count=1, get the message
1127 FND_MSG_PUB.Count_And_Get (
1128 p_encoded => FND_API.G_FALSE,
1129 p_count => x_msg_count,
1130 p_data => x_msg_data
1131 );
1132 End Update_Xml_Element;
1133
1134
1135 PROCEDURE Delete_Xml_Element(
1136 p_api_version_number IN NUMBER,
1137 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1138 p_commit IN VARCHAR2 := FND_API.G_FALSE,
1139 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1140 x_return_status OUT NOCOPY VARCHAR2,
1141 x_msg_count OUT NOCOPY NUMBER,
1142 x_msg_data OUT NOCOPY VARCHAR2,
1143 p_imp_xml_element_id IN NUMBER,
1144 p_object_version_number IN NUMBER
1145 )
1146
1147 IS
1148 L_API_NAME CONSTANT VARCHAR2(30) := 'Delete_Xml_Element';
1149 L_API_VERSION_NUMBER CONSTANT NUMBER := 1.0;
1150 l_object_version_number NUMBER;
1151
1152 BEGIN
1153 -- Standard Start of API savepoint
1154 SAVEPOINT DELETE_Xml_Element_PVT;
1155
1156 -- Standard call to check for call compatibility.
1157 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
1158 p_api_version_number,
1159 l_api_name,
1160 G_PKG_NAME)
1161 THEN
1162 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1163 END IF;
1164
1165 -- Initialize message list if p_init_msg_list is set to TRUE.
1166 IF FND_API.to_Boolean( p_init_msg_list )
1167 THEN
1168 FND_MSG_PUB.initialize;
1169 END IF;
1170
1171 -- Debug Message
1172 IF (AMS_DEBUG_HIGH_ON) THEN
1173
1177
1174 AMS_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'start');
1175 END IF;
1176
1178 -- Initialize API return status to SUCCESS
1179 x_return_status := FND_API.G_RET_STS_SUCCESS;
1180
1181 --
1182 -- Api body
1183 --
1184 -- Debug Message
1185 IF (AMS_DEBUG_HIGH_ON) THEN
1186
1187 AMS_UTILITY_PVT.debug_message( 'Private API: Calling delete table handler');
1188 END IF;
1189
1190 -- Invoke table handler(AMS_IMP_XML_ELEMENTS_PKG.Delete_Row)
1191 AMS_IMP_XML_ELEMENTS_PKG.Delete_Row(
1192 p_IMP_XML_ELEMENT_ID => p_IMP_XML_ELEMENT_ID);
1193 --
1194 -- End of API body
1195 --
1196
1197 -- Standard check for p_commit
1198 IF FND_API.to_Boolean( p_commit )
1199 THEN
1200 COMMIT WORK;
1201 END IF;
1202
1203
1204 -- Debug Message
1205 IF (AMS_DEBUG_HIGH_ON) THEN
1206
1207 AMS_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'end');
1208 END IF;
1209
1210 -- Standard call to get message count and if count is 1, get message info.
1211 FND_MSG_PUB.Count_And_Get
1212 (p_count => x_msg_count,
1213 p_data => x_msg_data
1214 );
1215 EXCEPTION
1216
1217 WHEN AMS_Utility_PVT.resource_locked THEN
1218 x_return_status := FND_API.g_ret_sts_error;
1219 AMS_Utility_PVT.Error_Message(p_message_name => 'AMS_API_RESOURCE_LOCKED');
1220
1221 WHEN FND_API.G_EXC_ERROR THEN
1222 ROLLBACK TO DELETE_Xml_Element_PVT;
1223 x_return_status := FND_API.G_RET_STS_ERROR;
1224 -- Standard call to get message count and if count=1, get the message
1225 FND_MSG_PUB.Count_And_Get (
1226 p_encoded => FND_API.G_FALSE,
1227 p_count => x_msg_count,
1228 p_data => x_msg_data
1229 );
1230
1231 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1232 ROLLBACK TO DELETE_Xml_Element_PVT;
1233 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1234 -- Standard call to get message count and if count=1, get the message
1235 FND_MSG_PUB.Count_And_Get (
1236 p_encoded => FND_API.G_FALSE,
1237 p_count => x_msg_count,
1238 p_data => x_msg_data
1239 );
1240
1241 WHEN OTHERS THEN
1242 ROLLBACK TO DELETE_Xml_Element_PVT;
1243 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1244 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1245 THEN
1246 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
1247 END IF;
1248 -- Standard call to get message count and if count=1, get the message
1249 FND_MSG_PUB.Count_And_Get (
1250 p_encoded => FND_API.G_FALSE,
1251 p_count => x_msg_count,
1252 p_data => x_msg_data
1253 );
1254 End Delete_Xml_Element;
1255
1256
1257
1258 -- Hint: Primary key needs to be returned.
1259 PROCEDURE Lock_Xml_Element(
1260 p_api_version_number IN NUMBER,
1261 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1262
1263 x_return_status OUT NOCOPY VARCHAR2,
1264 x_msg_count OUT NOCOPY NUMBER,
1265 x_msg_data OUT NOCOPY VARCHAR2,
1266
1267 p_imp_xml_element_id IN NUMBER,
1268 p_object_version IN NUMBER
1269 )
1270
1271 IS
1272 L_API_NAME CONSTANT VARCHAR2(30) := 'Lock_Xml_Element';
1273 L_API_VERSION_NUMBER CONSTANT NUMBER := 1.0;
1274 L_FULL_NAME CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
1275 l_IMP_XML_ELEMENT_ID NUMBER;
1276
1277 CURSOR c_Xml_Element IS
1278 SELECT IMP_XML_ELEMENT_ID
1279 FROM AMS_IMP_XML_ELEMENTS
1280 WHERE IMP_XML_ELEMENT_ID = p_IMP_XML_ELEMENT_ID
1281 AND object_version_number = p_object_version
1282 FOR UPDATE NOWAIT;
1283
1284 BEGIN
1285
1286 -- Debug Message
1287 IF (AMS_DEBUG_HIGH_ON) THEN
1288
1289 AMS_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'start');
1290 END IF;
1291
1292 -- Initialize message list if p_init_msg_list is set to TRUE.
1293 IF FND_API.to_Boolean( p_init_msg_list )
1294 THEN
1295 FND_MSG_PUB.initialize;
1296 END IF;
1297
1298 -- Standard call to check for call compatibility.
1299 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
1300 p_api_version_number,
1301 l_api_name,
1302 G_PKG_NAME)
1303 THEN
1304 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1305 END IF;
1306
1307
1308 -- Initialize API return status to SUCCESS
1309 x_return_status := FND_API.G_RET_STS_SUCCESS;
1310
1311
1312 ------------------------ lock -------------------------
1313
1314 IF (AMS_DEBUG_HIGH_ON) THEN
1315
1316
1317
1318 AMS_Utility_PVT.debug_message(l_full_name||': start');
1319
1320 END IF;
1321 OPEN c_Xml_Element;
1322
1323 FETCH c_Xml_Element INTO l_IMP_XML_ELEMENT_ID;
1324
1325 IF (c_Xml_Element%NOTFOUND) THEN
1326 CLOSE c_Xml_Element;
1327 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1328 FND_MESSAGE.set_name('AMS', 'AMS_API_RECORD_NOT_FOUND');
1329 FND_MSG_PUB.add;
1330 END IF;
1331 RAISE FND_API.g_exc_error;
1332 END IF;
1333
1334 CLOSE c_Xml_Element;
1335
1336 -------------------- finish --------------------------
1340 p_data => x_msg_data);
1337 FND_MSG_PUB.count_and_get(
1338 p_encoded => FND_API.g_false,
1339 p_count => x_msg_count,
1341 IF (AMS_DEBUG_HIGH_ON) THEN
1342
1343 AMS_Utility_PVT.debug_message(l_full_name ||': end');
1344 END IF;
1345 EXCEPTION
1346
1347 WHEN AMS_Utility_PVT.resource_locked THEN
1348 x_return_status := FND_API.g_ret_sts_error;
1349 AMS_Utility_PVT.Error_Message(p_message_name => 'AMS_API_RESOURCE_LOCKED');
1350
1351 WHEN FND_API.G_EXC_ERROR THEN
1352 ROLLBACK TO LOCK_Xml_Element_PVT;
1353 x_return_status := FND_API.G_RET_STS_ERROR;
1354 -- Standard call to get message count and if count=1, get the message
1355 FND_MSG_PUB.Count_And_Get (
1356 p_encoded => FND_API.G_FALSE,
1357 p_count => x_msg_count,
1358 p_data => x_msg_data
1359 );
1360
1361 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1362 ROLLBACK TO LOCK_Xml_Element_PVT;
1363 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1364 -- Standard call to get message count and if count=1, get the message
1365 FND_MSG_PUB.Count_And_Get (
1366 p_encoded => FND_API.G_FALSE,
1367 p_count => x_msg_count,
1368 p_data => x_msg_data
1369 );
1370
1371 WHEN OTHERS THEN
1372 ROLLBACK TO LOCK_Xml_Element_PVT;
1373 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1374 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1375 THEN
1376 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
1377 END IF;
1378 -- Standard call to get message count and if count=1, get the message
1379 FND_MSG_PUB.Count_And_Get (
1380 p_encoded => FND_API.G_FALSE,
1381 p_count => x_msg_count,
1382 p_data => x_msg_data
1383 );
1384 End Lock_Xml_Element;
1385
1386
1387 PROCEDURE check_xml_element_uk_items(
1388 p_xml_element_rec IN xml_element_rec_type,
1389 p_validation_mode IN VARCHAR2 := JTF_PLSQL_API.g_create,
1390 x_return_status OUT NOCOPY VARCHAR2)
1391 IS
1392 l_valid_flag VARCHAR2(1) := FND_API.g_true;
1393
1394 BEGIN
1395 x_return_status := FND_API.g_ret_sts_success;
1396 IF p_validation_mode = JTF_PLSQL_API.g_create THEN
1397 l_valid_flag := AMS_Utility_PVT.check_uniqueness(
1398 'AMS_IMP_XML_ELEMENTS',
1399 'IMP_XML_ELEMENT_ID = ''' || p_xml_element_rec.IMP_XML_ELEMENT_ID ||''''
1400 );
1401 -- ELSE
1402 -- l_valid_flag := AMS_Utility_PVT.check_uniqueness(
1403 -- 'AMS_IMP_XML_ELEMENTS',
1404 -- 'IMP_XML_ELEMENT_ID = ''' || p_xml_element_rec.IMP_XML_ELEMENT_ID ||
1405 -- ''' AND IMP_XML_ELEMENT_ID <> ' || p_xml_element_rec.IMP_XML_ELEMENT_ID
1406 -- );
1407 END IF;
1408
1409 IF l_valid_flag = FND_API.g_false THEN
1410 AMS_Utility_PVT.Error_Message(p_message_name => 'AMS_IMP_XML_ELEMENT_ID_DUPLICATE');
1411 x_return_status := FND_API.g_ret_sts_error;
1412 RETURN;
1413 END IF;
1414
1415 END check_xml_element_uk_items;
1416
1417 PROCEDURE check_xml_element_req_items(
1418 p_xml_element_rec IN xml_element_rec_type,
1419 p_validation_mode IN VARCHAR2 := JTF_PLSQL_API.g_create,
1420 x_return_status OUT NOCOPY VARCHAR2
1421 )
1422 IS
1423 BEGIN
1424 x_return_status := FND_API.g_ret_sts_success;
1425
1426 IF p_validation_mode = JTF_PLSQL_API.g_create THEN
1427
1428
1429 IF p_xml_element_rec.imp_xml_element_id = FND_API.g_miss_num OR p_xml_element_rec.imp_xml_element_id IS NULL THEN
1430 FND_MESSAGE.set_name('AMS', 'AMS_API_MISSING_FIELD');
1431 FND_MESSAGE.set_token('MISS_FIELD','IMP_XML_ELEMENT_ID');
1432 x_return_status := FND_API.g_ret_sts_error;
1433 RETURN;
1434 END IF;
1435
1436
1437 IF p_xml_element_rec.last_updated_by = FND_API.g_miss_num OR p_xml_element_rec.last_updated_by IS NULL THEN
1438 FND_MESSAGE.set_name('AMS', 'AMS_API_MISSING_FIELD');
1439 FND_MESSAGE.set_token('MISS_FIELD','LAST_UPDATED_BY');
1440 x_return_status := FND_API.g_ret_sts_error;
1441 RETURN;
1442 END IF;
1443
1444
1445 IF p_xml_element_rec.created_by = FND_API.g_miss_num OR p_xml_element_rec.created_by IS NULL THEN
1446 FND_MESSAGE.set_name('AMS', 'AMS_API_MISSING_FIELD');
1447 FND_MESSAGE.set_token('MISS_FIELD','CREATED_BY');
1448 x_return_status := FND_API.g_ret_sts_error;
1449 RETURN;
1450 END IF;
1451
1452
1453 IF p_xml_element_rec.last_update_login = FND_API.g_miss_num OR p_xml_element_rec.last_update_login IS NULL THEN
1454 FND_MESSAGE.set_name('AMS', 'AMS_API_MISSING_FIELD');
1455 FND_MESSAGE.set_token('MISS_FIELD','LAST_UPDATE_LOGIN');
1456 x_return_status := FND_API.g_ret_sts_error;
1457 RETURN;
1458 END IF;
1459
1460
1461 IF p_xml_element_rec.last_update_date = FND_API.g_miss_date OR p_xml_element_rec.last_update_date IS NULL THEN
1462 FND_MESSAGE.set_name('AMS', 'AMS_API_MISSING_FIELD');
1463 FND_MESSAGE.set_token('MISS_FIELD','LAST_UPDATE_DATE');
1464 x_return_status := FND_API.g_ret_sts_error;
1465 RETURN;
1466 END IF;
1467
1468
1469 IF p_xml_element_rec.creation_date = FND_API.g_miss_date OR p_xml_element_rec.creation_date IS NULL THEN
1470 FND_MESSAGE.set_name('AMS', 'AMS_API_MISSING_FIELD');
1471 FND_MESSAGE.set_token('MISS_FIELD','CREATION_DATE');
1472 x_return_status := FND_API.g_ret_sts_error;
1473 RETURN;
1474 END IF;
1475
1476
1480 x_return_status := FND_API.g_ret_sts_error;
1477 IF p_xml_element_rec.imp_xml_document_id = FND_API.g_miss_num OR p_xml_element_rec.imp_xml_document_id IS NULL THEN
1478 FND_MESSAGE.set_name('AMS', 'AMS_API_MISSING_FIELD');
1479 FND_MESSAGE.set_token('MISS_FIELD','IMP_XML_DOCUMENT_ID');
1481 RETURN;
1482 END IF;
1483 ELSE
1484
1485
1486 IF p_xml_element_rec.imp_xml_element_id IS NULL THEN
1487 AMS_Utility_PVT.Error_Message(p_message_name => 'AMS_xml_element_NO_imp_xml_element_id');
1488 x_return_status := FND_API.g_ret_sts_error;
1489 RETURN;
1490 END IF;
1491
1492
1493 IF p_xml_element_rec.last_updated_by IS NULL THEN
1494 AMS_Utility_PVT.Error_Message(p_message_name => 'AMS_xml_element_NO_last_updated_by');
1495 x_return_status := FND_API.g_ret_sts_error;
1496 RETURN;
1497 END IF;
1498
1499
1500 IF p_xml_element_rec.created_by IS NULL THEN
1501 AMS_Utility_PVT.Error_Message(p_message_name => 'AMS_xml_element_NO_created_by');
1502 x_return_status := FND_API.g_ret_sts_error;
1503 RETURN;
1504 END IF;
1505
1506
1507 IF p_xml_element_rec.last_update_login IS NULL THEN
1508 AMS_Utility_PVT.Error_Message(p_message_name => 'AMS_xml_element_NO_last_update_login');
1509 x_return_status := FND_API.g_ret_sts_error;
1510 RETURN;
1511 END IF;
1512
1513
1514 IF p_xml_element_rec.last_update_date IS NULL THEN
1515 AMS_Utility_PVT.Error_Message(p_message_name => 'AMS_xml_element_NO_last_update_date');
1516 x_return_status := FND_API.g_ret_sts_error;
1517 RETURN;
1518 END IF;
1519
1520
1521 IF p_xml_element_rec.creation_date IS NULL THEN
1522 AMS_Utility_PVT.Error_Message(p_message_name => 'AMS_xml_element_NO_creation_date');
1523 x_return_status := FND_API.g_ret_sts_error;
1524 RETURN;
1525 END IF;
1526
1527
1528 IF p_xml_element_rec.imp_xml_document_id IS NULL THEN
1529 AMS_Utility_PVT.Error_Message(p_message_name => 'AMS_xml_element_NO_imp_xml_document_id');
1530 x_return_status := FND_API.g_ret_sts_error;
1531 RETURN;
1532 END IF;
1533 END IF;
1534
1535 END check_xml_element_req_items;
1536
1537 PROCEDURE check_xml_element_FK_items(
1538 p_xml_element_rec IN xml_element_rec_type,
1539 x_return_status OUT NOCOPY VARCHAR2
1540 )
1541 IS
1542 BEGIN
1543 x_return_status := FND_API.g_ret_sts_success;
1544
1545 -- Enter custom code here
1546
1547 END check_xml_element_FK_items;
1548
1549 PROCEDURE check_xml_element_Lookup_items(
1550 p_xml_element_rec IN xml_element_rec_type,
1551 x_return_status OUT NOCOPY VARCHAR2
1552 )
1553 IS
1554 BEGIN
1555 x_return_status := FND_API.g_ret_sts_success;
1556
1557 -- Enter custom code here
1558
1559 END check_xml_element_Lookup_items;
1560
1561 PROCEDURE Check_xml_element_Items (
1562 P_xml_element_rec IN xml_element_rec_type,
1563 p_validation_mode IN VARCHAR2,
1564 x_return_status OUT NOCOPY VARCHAR2
1565 )
1566 IS
1567 BEGIN
1568
1569 -- Check Items Uniqueness API calls
1570
1571 check_xml_element_uk_items(
1572 p_xml_element_rec => p_xml_element_rec,
1573 p_validation_mode => p_validation_mode,
1574 x_return_status => x_return_status);
1575 IF x_return_status <> FND_API.g_ret_sts_success THEN
1576 RETURN;
1577 END IF;
1578
1579 -- Check Items Required/NOT NULL API calls
1580
1581 check_xml_element_req_items(
1582 p_xml_element_rec => p_xml_element_rec,
1583 p_validation_mode => p_validation_mode,
1584 x_return_status => x_return_status);
1585 IF x_return_status <> FND_API.g_ret_sts_success THEN
1586 RETURN;
1587 END IF;
1588 -- Check Items Foreign Keys API calls
1589
1590 check_xml_element_FK_items(
1591 p_xml_element_rec => p_xml_element_rec,
1592 x_return_status => x_return_status);
1593 IF x_return_status <> FND_API.g_ret_sts_success THEN
1594 RETURN;
1595 END IF;
1596 -- Check Items Lookups
1597
1598 check_xml_element_Lookup_items(
1599 p_xml_element_rec => p_xml_element_rec,
1600 x_return_status => x_return_status);
1601 IF x_return_status <> FND_API.g_ret_sts_success THEN
1602 RETURN;
1603 END IF;
1604
1605 END Check_xml_element_Items;
1606
1607
1608
1609 PROCEDURE Complete_xml_element_Rec (
1610 p_xml_element_rec IN xml_element_rec_type,
1611 x_complete_rec OUT NOCOPY xml_element_rec_type)
1612 IS
1613 l_return_status VARCHAR2(1);
1614
1615 CURSOR c_complete IS
1616 SELECT *
1617 FROM ams_imp_xml_elements
1618 WHERE imp_xml_element_id = p_xml_element_rec.imp_xml_element_id;
1619 l_xml_element_rec c_complete%ROWTYPE;
1620 BEGIN
1621 x_complete_rec := p_xml_element_rec;
1622
1623
1624 OPEN c_complete;
1625 FETCH c_complete INTO l_xml_element_rec;
1626 CLOSE c_complete;
1627
1628 -- imp_xml_element_id
1629 IF p_xml_element_rec.imp_xml_element_id = FND_API.g_miss_num THEN
1630 x_complete_rec.imp_xml_element_id := l_xml_element_rec.imp_xml_element_id;
1631 END IF;
1632
1633 -- last_updated_by
1634 IF p_xml_element_rec.last_updated_by = FND_API.g_miss_num THEN
1635 x_complete_rec.last_updated_by := l_xml_element_rec.last_updated_by;
1636 END IF;
1637
1638 -- object_version_number
1639 IF p_xml_element_rec.object_version_number = FND_API.g_miss_num THEN
1640 x_complete_rec.object_version_number := l_xml_element_rec.object_version_number;
1644 IF p_xml_element_rec.created_by = FND_API.g_miss_num THEN
1641 END IF;
1642
1643 -- created_by
1645 x_complete_rec.created_by := l_xml_element_rec.created_by;
1646 END IF;
1647
1648 -- last_update_login
1649 IF p_xml_element_rec.last_update_login = FND_API.g_miss_num THEN
1650 x_complete_rec.last_update_login := l_xml_element_rec.last_update_login;
1651 END IF;
1652
1653 -- last_update_date
1654 IF p_xml_element_rec.last_update_date = FND_API.g_miss_date THEN
1655 x_complete_rec.last_update_date := l_xml_element_rec.last_update_date;
1656 END IF;
1657
1658 -- creation_date
1659 IF p_xml_element_rec.creation_date = FND_API.g_miss_date THEN
1660 x_complete_rec.creation_date := l_xml_element_rec.creation_date;
1661 END IF;
1662
1663 -- imp_xml_document_id
1664 IF p_xml_element_rec.imp_xml_document_id = FND_API.g_miss_num THEN
1665 x_complete_rec.imp_xml_document_id := l_xml_element_rec.imp_xml_document_id;
1666 END IF;
1667
1668 -- order_initial
1669 IF p_xml_element_rec.order_initial = FND_API.g_miss_num THEN
1670 x_complete_rec.order_initial := l_xml_element_rec.order_initial;
1671 END IF;
1672
1673 -- order_final
1674 IF p_xml_element_rec.order_final = FND_API.g_miss_num THEN
1675 x_complete_rec.order_final := l_xml_element_rec.order_final;
1676 END IF;
1677
1678 -- column_name
1679 IF p_xml_element_rec.column_name = FND_API.g_miss_char THEN
1680 x_complete_rec.column_name := l_xml_element_rec.column_name;
1681 END IF;
1682
1683 -- data
1684 IF p_xml_element_rec.data = FND_API.g_miss_char THEN
1685 x_complete_rec.data := l_xml_element_rec.data;
1686 END IF;
1687
1688 -- num_attr
1689 IF p_xml_element_rec.num_attr = FND_API.g_miss_num THEN
1690 x_complete_rec.num_attr := l_xml_element_rec.num_attr;
1691 END IF;
1692
1693 -- data_type
1694 IF p_xml_element_rec.data_type = FND_API.g_miss_char THEN
1695 x_complete_rec.data_type := l_xml_element_rec.data_type;
1696 END IF;
1697
1698 -- load_status
1699 IF p_xml_element_rec.load_status = FND_API.g_miss_char THEN
1700 x_complete_rec.load_status := l_xml_element_rec.load_status;
1701 END IF;
1702
1703 -- error_text
1704 IF p_xml_element_rec.error_text = FND_API.g_miss_char THEN
1705 x_complete_rec.error_text := l_xml_element_rec.error_text;
1706 END IF;
1707 -- Note: Developers need to modify the procedure
1708 -- to handle any business specific requirements.
1709 END Complete_xml_element_Rec;
1710
1711 PROCEDURE Validate_xml_element(
1712 p_api_version_number IN NUMBER,
1713 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1714 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1715 p_xml_element_rec IN xml_element_rec_type,
1716 p_validation_mode IN VARCHAR2,
1717 x_return_status OUT NOCOPY VARCHAR2,
1718 x_msg_count OUT NOCOPY NUMBER,
1719 x_msg_data OUT NOCOPY VARCHAR2
1720 )
1721 IS
1722 L_API_NAME CONSTANT VARCHAR2(30) := 'Validate_Xml_Element';
1723 L_API_VERSION_NUMBER CONSTANT NUMBER := 1.0;
1724 l_object_version_number NUMBER;
1725 l_xml_element_rec AMS_Xml_Element_PVT.xml_element_rec_type;
1726
1727 BEGIN
1728 -- Standard Start of API savepoint
1729 SAVEPOINT VALIDATE_Xml_Element_;
1730
1731 -- Standard call to check for call compatibility.
1732 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
1733 p_api_version_number,
1734 l_api_name,
1735 G_PKG_NAME)
1736 THEN
1737 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1738 END IF;
1739
1740 -- Initialize message list if p_init_msg_list is set to TRUE.
1741 IF FND_API.to_Boolean( p_init_msg_list )
1742 THEN
1743 FND_MSG_PUB.initialize;
1744 END IF;
1745 IF p_validation_level >= JTF_PLSQL_API.g_valid_level_item THEN
1746 Check_xml_element_Items(
1747 p_xml_element_rec => p_xml_element_rec,
1748 p_validation_mode => p_validation_mode,
1749 x_return_status => x_return_status
1750 );
1751
1752 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
1753 RAISE FND_API.G_EXC_ERROR;
1754 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1755 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1756 END IF;
1757 END IF;
1758
1759 Complete_xml_element_Rec(
1760 p_xml_element_rec => p_xml_element_rec,
1761 x_complete_rec => l_xml_element_rec
1762 );
1763
1764 /****
1765 IF p_validation_level >= JTF_PLSQL_API.g_valid_level_item THEN
1766 Validate_xml_element_Rec(
1767 p_api_version_number => 1.0,
1768 p_init_msg_list => FND_API.G_FALSE,
1769 x_return_status => x_return_status,
1770 x_msg_count => x_msg_count,
1771 x_msg_data => x_msg_data,
1772 p_xml_element_rec => l_xml_element_rec);
1773
1774 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
1775 RAISE FND_API.G_EXC_ERROR;
1776 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1777 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1778 END IF;
1779 END IF;
1780 ****/
1781
1782 -- Debug Message
1783 IF (AMS_DEBUG_HIGH_ON) THEN
1784
1785 AMS_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'start');
1786 END IF;
1787
1788 -- Initialize API return status to SUCCESS
1789 x_return_status := FND_API.G_RET_STS_SUCCESS;
1790
1791
1792 -- Debug Message
1793 IF (AMS_DEBUG_HIGH_ON) THEN
1794
1795 AMS_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'end');
1796 END IF;
1797
1798 -- Standard call to get message count and if count is 1, get message info.
1799 FND_MSG_PUB.Count_And_Get
1800 (p_count => x_msg_count,
1801 p_data => x_msg_data
1802 );
1803 EXCEPTION
1804
1805 WHEN AMS_Utility_PVT.resource_locked THEN
1806 x_return_status := FND_API.g_ret_sts_error;
1807 AMS_Utility_PVT.Error_Message(p_message_name => 'AMS_API_RESOURCE_LOCKED');
1808
1809 WHEN FND_API.G_EXC_ERROR THEN
1810 ROLLBACK TO VALIDATE_Xml_Element_;
1811 x_return_status := FND_API.G_RET_STS_ERROR;
1812 -- Standard call to get message count and if count=1, get the message
1813 FND_MSG_PUB.Count_And_Get (
1814 p_encoded => FND_API.G_FALSE,
1815 p_count => x_msg_count,
1816 p_data => x_msg_data
1817 );
1818
1819 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1820 ROLLBACK TO VALIDATE_Xml_Element_;
1821 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1822 -- Standard call to get message count and if count=1, get the message
1823 FND_MSG_PUB.Count_And_Get (
1824 p_encoded => FND_API.G_FALSE,
1825 p_count => x_msg_count,
1826 p_data => x_msg_data
1827 );
1828
1829 WHEN OTHERS THEN
1830 ROLLBACK TO VALIDATE_Xml_Element_;
1831 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1832 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1833 THEN
1834 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
1835 END IF;
1836 -- Standard call to get message count and if count=1, get the message
1837 FND_MSG_PUB.Count_And_Get (
1838 p_encoded => FND_API.G_FALSE,
1839 p_count => x_msg_count,
1840 p_data => x_msg_data
1841 );
1842 End Validate_Xml_Element;
1843
1844
1845 PROCEDURE Validate_xml_element_rec(
1846 p_api_version_number IN NUMBER,
1847 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1848 x_return_status OUT NOCOPY VARCHAR2,
1849 x_msg_count OUT NOCOPY NUMBER,
1850 x_msg_data OUT NOCOPY VARCHAR2,
1851 p_xml_element_rec IN xml_element_rec_type
1852 )
1853 IS
1854 BEGIN
1855 -- Initialize message list if p_init_msg_list is set to TRUE.
1856 IF FND_API.to_Boolean( p_init_msg_list )
1857 THEN
1858 FND_MSG_PUB.initialize;
1859 END IF;
1860
1861 -- Initialize API return status to SUCCESS
1862 x_return_status := FND_API.G_RET_STS_SUCCESS;
1863
1864 -- Hint: Validate data
1865 -- If data not valid
1866 -- THEN
1867 -- x_return_status := FND_API.G_RET_STS_ERROR;
1868
1869 -- Debug Message
1870 IF (AMS_DEBUG_HIGH_ON) THEN
1871
1872 AMS_UTILITY_PVT.debug_message('Private API: Validate_dm_model_rec');
1873 END IF;
1874 -- Standard call to get message count and if count is 1, get message info.
1875 FND_MSG_PUB.Count_And_Get
1876 (p_count => x_msg_count,
1877 p_data => x_msg_data
1878 );
1879 END Validate_xml_element_Rec;
1880
1881 END AMS_Xml_Element_PVT;