[Home] [Help]
PACKAGE BODY: APPS.AMS_IMP_LIST_HEADERS_PKG
Source
1 PACKAGE BODY AMS_IMP_LIST_HEADERS_PKG as
2 /* $Header: amstimpb.pls 115.16 2002/11/14 21:59:35 jieli noship $ */
3 -- ===============================================================
4 -- Start of Comments
5 -- Package name
6 -- AMS_IMP_LIST_HEADERS_PKG
7 -- Purpose
8 --
9 -- History
10 --
11 -- NOTE
12 --
13 -- End of Comments
14 -- ===============================================================
15
16
17 G_PKG_NAME CONSTANT VARCHAR2(30):= 'AMS_IMP_LIST_HEADERS_PKG';
18 G_FILE_NAME CONSTANT VARCHAR2(12) := 'amstimpb.pls';
19
20
21 ----------------------------------------------------------
22 ---- MEDIA ----
23 ----------------------------------------------------------
24
25 -- ========================================================
26 --
27 -- NAME
28 -- createInsertBody
29 --
30 -- PURPOSE
31 --
32 -- NOTES
33 --
34 -- HISTORY
35 --
36 -- ========================================================
37 AMS_DEBUG_HIGH_ON boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_HIGH);
38 AMS_DEBUG_LOW_ON boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW);
39 AMS_DEBUG_MEDIUM_ON boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_MEDIUM);
40
41 PROCEDURE Insert_Row(
42 px_import_list_header_id IN OUT NOCOPY NUMBER,
43 p_last_update_date DATE,
44 p_last_updated_by NUMBER,
45 p_creation_date DATE,
46 p_created_by NUMBER,
47 p_last_update_login NUMBER,
48 px_object_version_number IN OUT NOCOPY NUMBER,
49 p_view_application_id NUMBER,
50 p_name VARCHAR2,
51 p_version VARCHAR2,
52 p_import_type VARCHAR2,
53 p_owner_user_id NUMBER,
54 p_list_source_type_id NUMBER,
55 p_status_code VARCHAR2,
56 p_status_date DATE,
57 p_user_status_id NUMBER,
58 p_source_system VARCHAR2,
59 p_vendor_id NUMBER,
60 p_pin_id NUMBER,
61 px_org_id IN OUT NOCOPY NUMBER,
62 p_scheduled_time DATE,
63 p_loaded_no_of_rows NUMBER,
64 p_loaded_date DATE,
65 p_rows_to_skip NUMBER,
66 p_processed_rows NUMBER,
67 p_headings_flag VARCHAR2,
68 p_expiry_date DATE,
69 p_purge_date DATE,
70 p_description VARCHAR2,
71 p_keywords VARCHAR2,
72 p_transactional_cost NUMBER,
73 p_transactional_currency_code VARCHAR2,
74 p_functional_cost NUMBER,
75 p_functional_currency_code VARCHAR2,
76 p_terminated_by VARCHAR2,
77 p_enclosed_by VARCHAR2,
78 p_data_filename VARCHAR2,
79 p_process_immed_flag VARCHAR2,
80 p_dedupe_flag VARCHAR2,
81 p_attribute_category VARCHAR2,
82 p_attribute1 VARCHAR2,
83 p_attribute2 VARCHAR2,
84 p_attribute3 VARCHAR2,
85 p_attribute4 VARCHAR2,
86 p_attribute5 VARCHAR2,
87 p_attribute6 VARCHAR2,
88 p_attribute7 VARCHAR2,
89 p_attribute8 VARCHAR2,
90 p_attribute9 VARCHAR2,
91 p_attribute10 VARCHAR2,
92 p_attribute11 VARCHAR2,
93 p_attribute12 VARCHAR2,
94 p_attribute13 VARCHAR2,
95 p_attribute14 VARCHAR2,
96 p_attribute15 VARCHAR2,
97 p_custom_setup_id NUMBER,
98 p_country NUMBER,
99 p_usage NUMBER,
100 p_number_of_records NUMBER,
101 p_data_file_name VARCHAR2,
102 p_b2b_flag VARCHAR2,
103 p_rented_list_flag VARCHAR2,
104 p_server_flag VARCHAR2,
105 p_log_file_name NUMBER,
106 p_number_of_failed_records NUMBER,
107 p_number_of_duplicate_records NUMBER,
108 p_enable_word_replacement_flag VARCHAR2,
109 p_batch_id NUMBER,
110 p_server_name VARCHAR2,
111 p_user_name VARCHAR2,
112 p_password VARCHAR2,
113 p_upload_flag VARCHAR2,
114 p_parent_imp_header_id NUMBER,
115 p_record_update_flag VARCHAR2,
116 p_error_threshold NUMBER,
117 p_charset VARCHAR2)
118
119 IS
120 l_rowid VARCHAR2(20);
121 cursor C is select ROWID from AMS_IMP_LIST_HEADERS_ALL
122 where IMPORT_LIST_HEADER_ID = px_import_list_header_id;
123
124 BEGIN
125
126
127
128 INSERT INTO AMS_IMP_LIST_HEADERS_ALL(
129 import_list_header_id,
130 last_update_date,
131 last_updated_by,
132 creation_date,
133 created_by,
134 last_update_login,
135 object_version_number,
136 view_application_id,
137 name,
138 version,
139 import_type,
140 owner_user_id,
141 list_source_type_id,
142 status_code,
143 status_date,
144 user_status_id,
145 source_system,
146 vendor_id,
147 pin_id,
148 org_id,
149 scheduled_time,
150 loaded_no_of_rows,
151 loaded_date,
152 rows_to_skip,
153 processed_rows,
154 headings_flag,
155 expiry_date,
156 purge_date,
157 description,
158 keywords,
159 transactional_cost,
160 transactional_currency_code,
161 functional_cost,
162 functional_currency_code,
163 terminated_by,
164 enclosed_by,
165 data_filename,
166 process_immed_flag,
167 dedupe_flag,
168 attribute_category,
169 attribute1,
170 attribute2,
171 attribute3,
172 attribute4,
173 attribute5,
174 attribute6,
175 attribute7,
176 attribute8,
177 attribute9,
178 attribute10,
179 attribute11,
180 attribute12,
181 attribute13,
182 attribute14,
183 attribute15,
184 custom_setup_id,
185 country,
186 usage,
187 number_of_records,
188 data_file_name,
189 b2b_flag,
190 rented_list_flag,
191 server_flag,
192 log_file_name,
193 number_of_failed_records,
194 number_of_duplicate_records,
195 enable_word_replacement_flag,
196 batch_id,
197 execute_mode,
198 validate_file,
199 server_name,
200 user_name,
201 password,
202 upload_flag,
203 parent_imp_header_id,
204 record_update_flag,
205 error_threshold,
206 charset
207 ) VALUES (
208 DECODE( px_import_list_header_id, FND_API.g_miss_num, NULL, px_import_list_header_id),
209 DECODE( p_last_update_date, FND_API.g_miss_date, NULL, p_last_update_date),
210 DECODE( p_last_updated_by, FND_API.g_miss_num, NULL, p_last_updated_by),
211 DECODE( p_creation_date, FND_API.g_miss_date, NULL, p_creation_date),
212 DECODE( p_created_by, FND_API.g_miss_num, NULL, p_created_by),
213 DECODE( p_last_update_login, FND_API.g_miss_num, NULL, p_last_update_login),
214 DECODE( px_object_version_number, FND_API.g_miss_num, NULL, px_object_version_number),
215 DECODE( p_view_application_id, FND_API.g_miss_num, NULL, p_view_application_id),
216 DECODE( p_name, FND_API.g_miss_char, NULL, p_name),
217 DECODE( p_version, FND_API.g_miss_char, NULL, p_version),
218 DECODE( p_import_type, FND_API.g_miss_char, NULL, p_import_type),
219 DECODE( p_owner_user_id, FND_API.g_miss_num, NULL, p_owner_user_id),
220 DECODE( p_list_source_type_id, FND_API.g_miss_num, NULL, p_list_source_type_id),
221 DECODE( p_status_code, FND_API.g_miss_char, NULL, p_status_code),
222 DECODE( p_status_date, FND_API.g_miss_date, NULL, p_status_date),
223 DECODE( p_user_status_id, FND_API.g_miss_num, NULL, p_user_status_id),
224 DECODE( p_source_system, FND_API.g_miss_char, NULL, p_source_system),
225 DECODE( p_vendor_id, FND_API.g_miss_num, NULL, p_vendor_id),
226 DECODE( p_pin_id, FND_API.g_miss_num, NULL, p_pin_id),
227 DECODE( px_org_id, FND_API.g_miss_num, NULL, px_org_id),
228 DECODE( p_scheduled_time, FND_API.g_miss_date, NULL, p_scheduled_time),
229 DECODE( p_loaded_no_of_rows, FND_API.g_miss_num, NULL, p_loaded_no_of_rows),
230 DECODE( p_loaded_date, FND_API.g_miss_date, NULL, p_loaded_date),
231 DECODE( p_rows_to_skip, FND_API.g_miss_num, NULL, p_rows_to_skip),
232 DECODE( p_processed_rows, FND_API.g_miss_num, NULL, p_processed_rows),
233 DECODE( p_headings_flag, FND_API.g_miss_char, NULL, p_headings_flag),
234 DECODE( p_expiry_date, FND_API.g_miss_date, NULL, p_expiry_date),
235 DECODE( p_purge_date, FND_API.g_miss_date, NULL, p_purge_date),
236 DECODE( p_description, FND_API.g_miss_char, NULL, p_description),
237 DECODE( p_keywords, FND_API.g_miss_char, NULL, p_keywords),
238 DECODE( p_transactional_cost, FND_API.g_miss_num, NULL, p_transactional_cost),
239 DECODE( p_transactional_currency_code, FND_API.g_miss_char, NULL, p_transactional_currency_code),
240 DECODE( p_functional_cost, FND_API.g_miss_num, NULL, p_functional_cost),
241 DECODE( p_functional_currency_code, FND_API.g_miss_char, NULL, p_functional_currency_code),
242 DECODE( p_terminated_by, FND_API.g_miss_char, NULL, p_terminated_by),
243 DECODE( p_enclosed_by, FND_API.g_miss_char, NULL, p_enclosed_by),
244 DECODE( p_data_filename, FND_API.g_miss_char, NULL, p_data_filename),
245 DECODE( p_process_immed_flag, FND_API.g_miss_char, NULL, p_process_immed_flag),
246 DECODE( p_dedupe_flag, FND_API.g_miss_char, NULL, p_dedupe_flag),
247 DECODE( p_attribute_category, FND_API.g_miss_char, NULL, p_attribute_category),
248 DECODE( p_attribute1, FND_API.g_miss_char, NULL, p_attribute1),
249 DECODE( p_attribute2, FND_API.g_miss_char, NULL, p_attribute2),
250 DECODE( p_attribute3, FND_API.g_miss_char, NULL, p_attribute3),
251 DECODE( p_attribute4, FND_API.g_miss_char, NULL, p_attribute4),
252 DECODE( p_attribute5, FND_API.g_miss_char, NULL, p_attribute5),
253 DECODE( p_attribute6, FND_API.g_miss_char, NULL, p_attribute6),
254 DECODE( p_attribute7, FND_API.g_miss_char, NULL, p_attribute7),
255 DECODE( p_attribute8, FND_API.g_miss_char, NULL, p_attribute8),
256 DECODE( p_attribute9, FND_API.g_miss_char, NULL, p_attribute9),
257 DECODE( p_attribute10, FND_API.g_miss_char, NULL, p_attribute10),
258 DECODE( p_attribute11, FND_API.g_miss_char, NULL, p_attribute11),
259 DECODE( p_attribute12, FND_API.g_miss_char, NULL, p_attribute12),
260 DECODE( p_attribute13, FND_API.g_miss_char, NULL, p_attribute13),
261 DECODE( p_attribute14, FND_API.g_miss_char, NULL, p_attribute14),
262 DECODE( p_attribute15, FND_API.g_miss_char, NULL, p_attribute15),
263 DECODE( p_custom_setup_id, FND_API.g_miss_num, NULL, p_custom_setup_id),
264 DECODE( p_country, FND_API.g_miss_num, NULL, p_country),
265 DECODE( p_usage, FND_API.g_miss_num, NULL, p_usage),
266 DECODE( p_number_of_records, FND_API.g_miss_num, NULL, p_number_of_records),
267 DECODE( p_data_file_name, FND_API.g_miss_char, NULL, p_data_file_name),
268 DECODE( p_b2b_flag, FND_API.g_miss_char, NULL, p_b2b_flag),
269 DECODE( p_rented_list_flag, FND_API.g_miss_char, NULL, p_rented_list_flag),
270 DECODE( p_server_flag, FND_API.g_miss_char, NULL, p_server_flag),
271 DECODE( p_log_file_name, FND_API.g_miss_num, NULL, p_log_file_name),
272 DECODE( p_number_of_failed_records, FND_API.g_miss_num, NULL, p_number_of_failed_records),
273 DECODE( p_number_of_duplicate_records, FND_API.g_miss_num, NULL, p_number_of_duplicate_records),
274 DECODE( p_enable_word_replacement_flag, FND_API.g_miss_char, NULL, p_enable_word_replacement_flag),
275 DECODE( p_batch_id, FND_API.g_miss_num, NULL, p_batch_id),
276 'N',
277 'Y',
278 DECODE( p_server_name, FND_API.g_miss_char, NULL, p_server_name),
279 DECODE( p_user_name, FND_API.g_miss_char, NULL, p_user_name),
280 DECODE( p_password, FND_API.g_miss_char, NULL, p_password),
281 DECODE( p_upload_flag, FND_API.g_miss_char, NULL, p_upload_flag),
282 DECODE( p_parent_imp_header_id, FND_API.g_miss_num, NULL, p_parent_imp_header_id),
283 DECODE( p_record_update_flag, FND_API.g_miss_char, NULL, p_record_update_flag),
284 DECODE( p_error_threshold, FND_API.g_miss_num, NULL, p_error_threshold),
285 DECODE( p_charset, FND_API.g_miss_char, NULL, p_charset)
286 );
287
288 INSERT INTO AMS_IMP_LIST_HEADERS_ALL_TL(
289 import_list_header_id,
290 last_update_date,
291 last_update_by,
292 creation_date,
293 created_by,
294 last_update_login,
295 language,
296 source_lang,
297 name,
298 description
299 ) select
300 px_import_list_header_id,
301 p_last_update_date,
302 p_last_updated_by,
303 p_creation_date,
304 p_created_by,
305 p_last_update_login,
306 L.LANGUAGE_CODE,
307 userenv('LANG'),
308 p_name,
309 p_description
310 from FND_LANGUAGES L
311 where L.INSTALLED_FLAG in ('I', 'B')
312 and not exists
313 (select NULL
314 from AMS_IMP_LIST_HEADERS_ALL_TL T
315 where T.IMPORT_LIST_HEADER_ID = px_import_list_header_id
316 and T.LANGUAGE = L.LANGUAGE_CODE);
317
318 open c;
319 fetch c into l_ROWID;
320 if (c%notfound) then
321 close c;
322 raise no_data_found;
323 end if;
324 close c;
325
326
327 END Insert_Row;
328
329
333
330 ----------------------------------------------------------
331 ---- MEDIA ----
332 ----------------------------------------------------------
334 -- ========================================================
335 --
336 -- NAME
337 -- createUpdateBody
338 --
339 -- PURPOSE
340 --
341 -- NOTES
342 --
343 -- HISTORY
344 --
345 -- ========================================================
346 PROCEDURE Update_Row(
347 p_import_list_header_id NUMBER,
348 p_last_update_date DATE,
349 p_last_updated_by NUMBER,
350 p_last_update_login NUMBER,
351 p_object_version_number NUMBER,
352 p_view_application_id NUMBER,
353 p_name VARCHAR2,
354 p_version VARCHAR2,
355 p_import_type VARCHAR2,
356 p_owner_user_id NUMBER,
357 p_list_source_type_id NUMBER,
358 p_status_code VARCHAR2,
359 p_status_date DATE,
360 p_user_status_id NUMBER,
361 p_source_system VARCHAR2,
362 p_vendor_id NUMBER,
363 p_pin_id NUMBER,
364 p_org_id NUMBER,
365 p_scheduled_time DATE,
366 p_loaded_no_of_rows NUMBER,
367 p_loaded_date DATE,
368 p_rows_to_skip NUMBER,
369 p_processed_rows NUMBER,
370 p_headings_flag VARCHAR2,
371 p_expiry_date DATE,
372 p_purge_date DATE,
373 p_description VARCHAR2,
374 p_keywords VARCHAR2,
375 p_transactional_cost NUMBER,
376 p_transactional_currency_code VARCHAR2,
377 p_functional_cost NUMBER,
378 p_functional_currency_code VARCHAR2,
379 p_terminated_by VARCHAR2,
380 p_enclosed_by VARCHAR2,
381 p_data_filename VARCHAR2,
382 p_process_immed_flag VARCHAR2,
383 p_dedupe_flag VARCHAR2,
384 p_attribute_category VARCHAR2,
385 p_attribute1 VARCHAR2,
386 p_attribute2 VARCHAR2,
387 p_attribute3 VARCHAR2,
388 p_attribute4 VARCHAR2,
389 p_attribute5 VARCHAR2,
390 p_attribute6 VARCHAR2,
391 p_attribute7 VARCHAR2,
392 p_attribute8 VARCHAR2,
393 p_attribute9 VARCHAR2,
394 p_attribute10 VARCHAR2,
395 p_attribute11 VARCHAR2,
396 p_attribute12 VARCHAR2,
397 p_attribute13 VARCHAR2,
398 p_attribute14 VARCHAR2,
399 p_attribute15 VARCHAR2,
400 p_custom_setup_id NUMBER,
401 p_country NUMBER,
402 p_usage NUMBER,
403 p_number_of_records NUMBER,
404 p_data_file_name VARCHAR2,
405 p_b2b_flag VARCHAR2,
406 p_rented_list_flag VARCHAR2,
407 p_server_flag VARCHAR2,
408 p_log_file_name NUMBER,
409 p_number_of_failed_records NUMBER,
410 p_number_of_duplicate_records NUMBER,
411 p_enable_word_replacement_flag VARCHAR2,
412 p_validate_file VARCHAR2,
413 p_record_update_flag VARCHAR2,
414 p_error_threshold NUMBER)
415
416 IS
417 BEGIN
418 Update AMS_IMP_LIST_HEADERS_ALL
419 SET
420 import_list_header_id = DECODE( p_import_list_header_id, FND_API.g_miss_num, import_list_header_id, p_import_list_header_id),
421 last_update_date = DECODE( p_last_update_date, FND_API.g_miss_date, last_update_date, p_last_update_date),
422 last_updated_by = DECODE( p_last_updated_by, FND_API.g_miss_num, last_updated_by, p_last_updated_by),
423 last_update_login = DECODE( p_last_update_login, FND_API.g_miss_num, last_update_login, p_last_update_login),
424 object_version_number = DECODE( p_object_version_number, FND_API.g_miss_num, object_version_number, p_object_version_number),
425 view_application_id = DECODE( p_view_application_id, FND_API.g_miss_num, view_application_id, p_view_application_id),
426 name = DECODE( p_name, FND_API.g_miss_char, name, p_name),
427 version = DECODE( p_version, FND_API.g_miss_char, version, p_version),
428 import_type = DECODE( p_import_type, FND_API.g_miss_char, import_type, p_import_type),
429 owner_user_id = DECODE( p_owner_user_id, FND_API.g_miss_num, owner_user_id, p_owner_user_id),
430 list_source_type_id = DECODE( p_list_source_type_id, FND_API.g_miss_num, list_source_type_id, p_list_source_type_id),
431 status_code = DECODE( p_status_code, FND_API.g_miss_char, status_code, p_status_code),
432 status_date = DECODE( p_status_date, FND_API.g_miss_date, status_date, p_status_date),
433 user_status_id = DECODE( p_user_status_id, FND_API.g_miss_num, user_status_id, p_user_status_id),
434 source_system = DECODE( p_source_system, FND_API.g_miss_char, source_system, p_source_system),
435 vendor_id = DECODE( p_vendor_id, FND_API.g_miss_num, vendor_id, p_vendor_id),
436 pin_id = DECODE( p_pin_id, FND_API.g_miss_num, pin_id, p_pin_id),
437 org_id = DECODE( p_org_id, FND_API.g_miss_num, org_id, p_org_id),
438 scheduled_time = DECODE( p_scheduled_time, FND_API.g_miss_date, scheduled_time, p_scheduled_time),
442 processed_rows = DECODE( p_processed_rows, FND_API.g_miss_num, processed_rows, p_processed_rows),
439 loaded_no_of_rows = DECODE( p_loaded_no_of_rows, FND_API.g_miss_num, loaded_no_of_rows, p_loaded_no_of_rows),
440 loaded_date = DECODE( p_loaded_date, FND_API.g_miss_date, loaded_date, p_loaded_date),
441 rows_to_skip = DECODE( p_rows_to_skip, FND_API.g_miss_num, rows_to_skip, p_rows_to_skip),
443 headings_flag = DECODE( p_headings_flag, FND_API.g_miss_char, headings_flag, p_headings_flag),
444 expiry_date = DECODE( p_expiry_date, FND_API.g_miss_date, expiry_date, p_expiry_date),
445 purge_date = DECODE( p_purge_date, FND_API.g_miss_date, purge_date, p_purge_date),
446 description = DECODE( p_description, FND_API.g_miss_char, description, p_description),
447 keywords = DECODE( p_keywords, FND_API.g_miss_char, keywords, p_keywords),
448 transactional_cost = DECODE( p_transactional_cost, FND_API.g_miss_num, transactional_cost, p_transactional_cost),
449 transactional_currency_code = DECODE( p_transactional_currency_code, FND_API.g_miss_char, transactional_currency_code, p_transactional_currency_code),
450 functional_cost = DECODE( p_functional_cost, FND_API.g_miss_num, functional_cost, p_functional_cost),
451 functional_currency_code = DECODE( p_functional_currency_code, FND_API.g_miss_char, functional_currency_code, p_functional_currency_code),
452 terminated_by = DECODE( p_terminated_by, FND_API.g_miss_char, terminated_by, p_terminated_by),
453 enclosed_by = DECODE( p_enclosed_by, FND_API.g_miss_char, enclosed_by, p_enclosed_by),
454 data_filename = DECODE( p_data_filename, FND_API.g_miss_char, data_filename, p_data_filename),
455 process_immed_flag = DECODE( p_process_immed_flag, FND_API.g_miss_char, process_immed_flag, p_process_immed_flag),
456 dedupe_flag = DECODE( p_dedupe_flag, FND_API.g_miss_char, dedupe_flag, p_dedupe_flag),
457 attribute_category = DECODE( p_attribute_category, FND_API.g_miss_char, attribute_category, p_attribute_category),
458 attribute1 = DECODE( p_attribute1, FND_API.g_miss_char, attribute1, p_attribute1),
459 attribute2 = DECODE( p_attribute2, FND_API.g_miss_char, attribute2, p_attribute2),
460 attribute3 = DECODE( p_attribute3, FND_API.g_miss_char, attribute3, p_attribute3),
461 attribute4 = DECODE( p_attribute4, FND_API.g_miss_char, attribute4, p_attribute4),
462 attribute5 = DECODE( p_attribute5, FND_API.g_miss_char, attribute5, p_attribute5),
463 attribute6 = DECODE( p_attribute6, FND_API.g_miss_char, attribute6, p_attribute6),
464 attribute7 = DECODE( p_attribute7, FND_API.g_miss_char, attribute7, p_attribute7),
465 attribute8 = DECODE( p_attribute8, FND_API.g_miss_char, attribute8, p_attribute8),
466 attribute9 = DECODE( p_attribute9, FND_API.g_miss_char, attribute9, p_attribute9),
467 attribute10 = DECODE( p_attribute10, FND_API.g_miss_char, attribute10, p_attribute10),
468 attribute11 = DECODE( p_attribute11, FND_API.g_miss_char, attribute11, p_attribute11),
469 attribute12 = DECODE( p_attribute12, FND_API.g_miss_char, attribute12, p_attribute12),
470 attribute13 = DECODE( p_attribute13, FND_API.g_miss_char, attribute13, p_attribute13),
471 attribute14 = DECODE( p_attribute14, FND_API.g_miss_char, attribute14, p_attribute14),
472 attribute15 = DECODE( p_attribute15, FND_API.g_miss_char, attribute15, p_attribute15),
473 custom_setup_id = DECODE( p_custom_setup_id, FND_API.g_miss_num, custom_setup_id, p_custom_setup_id),
474 country = DECODE( p_country, FND_API.g_miss_num, country, p_country),
475 usage = DECODE( p_usage, FND_API.g_miss_num, usage, p_usage),
476 number_of_records = DECODE( p_number_of_records, FND_API.g_miss_num, number_of_records, p_number_of_records),
477 data_file_name = DECODE( p_data_file_name, FND_API.g_miss_char, data_file_name, p_data_file_name),
478 b2b_flag = DECODE( p_b2b_flag, FND_API.g_miss_char, b2b_flag, p_b2b_flag),
479 rented_list_flag = DECODE( p_rented_list_flag, FND_API.g_miss_char, rented_list_flag, p_rented_list_flag),
480 server_flag = DECODE( p_server_flag, FND_API.g_miss_char, server_flag, p_server_flag),
481 log_file_name = DECODE( p_log_file_name, FND_API.g_miss_num, log_file_name, p_log_file_name),
482 number_of_failed_records = DECODE( p_number_of_failed_records, FND_API.g_miss_num, number_of_failed_records, p_number_of_failed_records),
483 number_of_duplicate_records = DECODE( p_number_of_duplicate_records, FND_API.g_miss_num, number_of_duplicate_records, p_number_of_duplicate_records),
484 enable_word_replacement_flag = DECODE( p_enable_word_replacement_flag, FND_API.g_miss_char, enable_word_replacement_flag, p_enable_word_replacement_flag),
485 validate_file = DECODE( p_validate_file, FND_API.g_miss_char, validate_file, p_validate_file),
486 record_update_flag = DECODE( p_record_update_flag, FND_API.g_miss_char, record_update_flag, p_record_update_flag),
487 error_threshold = DECODE( p_error_threshold, FND_API.g_miss_num, error_threshold, p_error_threshold)
488 WHERE IMPORT_LIST_HEADER_ID = p_IMPORT_LIST_HEADER_ID
489 AND object_version_number = p_object_version_number;
490
491 update AMS_IMP_LIST_HEADERS_ALL_TL
492 set
493 last_update_date=DECODE( p_last_update_date, FND_API.g_miss_date, last_update_date, p_last_update_date),
494 last_update_by=DECODE( p_last_updated_by, FND_API.g_miss_num, last_update_by, p_last_updated_by),
495 last_update_login=DECODE( p_last_update_login, FND_API.g_miss_num, last_update_login, p_last_update_login),
496 source_lang=userenv('LANG'),
497 name=DECODE( p_name, FND_API.g_miss_char, name, p_name),
501
498 description=DECODE( p_description, FND_API.g_miss_char, description, p_description)
499 where IMPORT_LIST_HEADER_ID = p_IMPORT_LIST_HEADER_ID
500 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
502
503 IF (SQL%NOTFOUND) THEN
504 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
505 END IF;
506 END Update_Row;
507
508
509 ----------------------------------------------------------
510 ---- MEDIA ----
511 ----------------------------------------------------------
512
513 -- ========================================================
514 --
515 -- NAME
516 -- createDeleteBody
517 --
518 -- PURPOSE
519 --
520 -- NOTES
521 --
522 -- HISTORY
523 --
524 -- ========================================================
525 PROCEDURE Delete_Row(
526 p_IMPORT_LIST_HEADER_ID NUMBER)
527 IS
528 BEGIN
529 DELETE FROM AMS_IMP_LIST_HEADERS_ALL
530 WHERE IMPORT_LIST_HEADER_ID = p_IMPORT_LIST_HEADER_ID;
531 If (SQL%NOTFOUND) then
532 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
533 End If;
534 END Delete_Row ;
535
536
537
538 ----------------------------------------------------------
539 ---- MEDIA ----
540 ----------------------------------------------------------
541
542 -- ========================================================
543 --
544 -- NAME
545 -- createLockBody
546 --
547 -- PURPOSE
548 --
549 -- NOTES
550 --
551 -- HISTORY
552 --
553 -- ========================================================
554 PROCEDURE Lock_Row(
555 p_import_list_header_id NUMBER,
556 p_last_update_date DATE,
557 p_last_updated_by NUMBER,
558 p_creation_date DATE,
559 p_created_by NUMBER,
560 p_last_update_login NUMBER,
561 p_object_version_number NUMBER,
562 p_view_application_id NUMBER,
563 p_name VARCHAR2,
564 p_version VARCHAR2,
565 p_import_type VARCHAR2,
566 p_owner_user_id NUMBER,
567 p_list_source_type_id NUMBER,
568 p_status_code VARCHAR2,
569 p_status_date DATE,
570 p_user_status_id NUMBER,
571 p_source_system VARCHAR2,
572 p_vendor_id NUMBER,
573 p_pin_id NUMBER,
574 p_org_id NUMBER,
575 p_scheduled_time DATE,
576 p_loaded_no_of_rows NUMBER,
577 p_loaded_date DATE,
578 p_rows_to_skip NUMBER,
579 p_processed_rows NUMBER,
580 p_headings_flag VARCHAR2,
581 p_expiry_date DATE,
582 p_purge_date DATE,
583 p_description VARCHAR2,
584 p_keywords VARCHAR2,
585 p_transactional_cost NUMBER,
586 p_transactional_currency_code VARCHAR2,
587 p_functional_cost NUMBER,
588 p_functional_currency_code VARCHAR2,
589 p_terminated_by VARCHAR2,
590 p_enclosed_by VARCHAR2,
591 p_data_filename VARCHAR2,
592 p_process_immed_flag VARCHAR2,
593 p_dedupe_flag VARCHAR2,
594 p_attribute_category VARCHAR2,
595 p_attribute1 VARCHAR2,
596 p_attribute2 VARCHAR2,
597 p_attribute3 VARCHAR2,
598 p_attribute4 VARCHAR2,
599 p_attribute5 VARCHAR2,
600 p_attribute6 VARCHAR2,
601 p_attribute7 VARCHAR2,
602 p_attribute8 VARCHAR2,
603 p_attribute9 VARCHAR2,
604 p_attribute10 VARCHAR2,
605 p_attribute11 VARCHAR2,
606 p_attribute12 VARCHAR2,
607 p_attribute13 VARCHAR2,
608 p_attribute14 VARCHAR2,
609 p_attribute15 VARCHAR2,
610 p_custom_setup_id NUMBER,
611 p_country NUMBER,
612 p_usage NUMBER,
613 p_number_of_records NUMBER,
614 p_data_file_name VARCHAR2,
615 p_b2b_flag VARCHAR2,
616 p_rented_list_flag VARCHAR2,
617 p_server_flag VARCHAR2,
618 p_log_file_name NUMBER,
619 p_number_of_failed_records NUMBER,
620 p_number_of_duplicate_records NUMBER,
621 p_enable_word_replacement_flag VARCHAR2)
622
623 IS
624 CURSOR C IS
625 SELECT *
626 FROM AMS_IMP_LIST_HEADERS_ALL
627 WHERE IMPORT_LIST_HEADER_ID = p_IMPORT_LIST_HEADER_ID
628 FOR UPDATE of IMPORT_LIST_HEADER_ID NOWAIT;
629 Recinfo C%ROWTYPE;
630 BEGIN
631 OPEN c;
632 FETCH c INTO Recinfo;
633 If (c%NOTFOUND) then
634 CLOSE c;
635 FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_DELETED');
636 APP_EXCEPTION.RAISE_EXCEPTION;
637 END IF;
638 CLOSE C;
639 IF (
640 ( Recinfo.import_list_header_id = p_import_list_header_id)
641 AND ( ( Recinfo.last_update_date = p_last_update_date)
642 OR ( ( Recinfo.last_update_date IS NULL )
643 AND ( p_last_update_date IS NULL )))
647 AND ( ( Recinfo.creation_date = p_creation_date)
644 AND ( ( Recinfo.last_updated_by = p_last_updated_by)
645 OR ( ( Recinfo.last_updated_by IS NULL )
646 AND ( p_last_updated_by IS NULL )))
648 OR ( ( Recinfo.creation_date IS NULL )
649 AND ( p_creation_date IS NULL )))
650 AND ( ( Recinfo.created_by = p_created_by)
651 OR ( ( Recinfo.created_by IS NULL )
652 AND ( p_created_by IS NULL )))
653 AND ( ( Recinfo.last_update_login = p_last_update_login)
654 OR ( ( Recinfo.last_update_login IS NULL )
655 AND ( p_last_update_login IS NULL )))
656 AND ( ( Recinfo.object_version_number = p_object_version_number)
657 OR ( ( Recinfo.object_version_number IS NULL )
658 AND ( p_object_version_number IS NULL )))
659 AND ( ( Recinfo.view_application_id = p_view_application_id)
660 OR ( ( Recinfo.view_application_id IS NULL )
661 AND ( p_view_application_id IS NULL )))
662 AND ( ( Recinfo.name = p_name)
663 OR ( ( Recinfo.name IS NULL )
664 AND ( p_name IS NULL )))
665 AND ( ( Recinfo.version = p_version)
666 OR ( ( Recinfo.version IS NULL )
667 AND ( p_version IS NULL )))
668 AND ( ( Recinfo.import_type = p_import_type)
669 OR ( ( Recinfo.import_type IS NULL )
670 AND ( p_import_type IS NULL )))
671 AND ( ( Recinfo.owner_user_id = p_owner_user_id)
672 OR ( ( Recinfo.owner_user_id IS NULL )
673 AND ( p_owner_user_id IS NULL )))
674 AND ( ( Recinfo.list_source_type_id = p_list_source_type_id)
675 OR ( ( Recinfo.list_source_type_id IS NULL )
676 AND ( p_list_source_type_id IS NULL )))
677 AND ( ( Recinfo.status_code = p_status_code)
678 OR ( ( Recinfo.status_code IS NULL )
679 AND ( p_status_code IS NULL )))
680 AND ( ( Recinfo.status_date = p_status_date)
681 OR ( ( Recinfo.status_date IS NULL )
682 AND ( p_status_date IS NULL )))
683 AND ( ( Recinfo.user_status_id = p_user_status_id)
684 OR ( ( Recinfo.user_status_id IS NULL )
685 AND ( p_user_status_id IS NULL )))
686 AND ( ( Recinfo.source_system = p_source_system)
687 OR ( ( Recinfo.source_system IS NULL )
688 AND ( p_source_system IS NULL )))
689 AND ( ( Recinfo.vendor_id = p_vendor_id)
690 OR ( ( Recinfo.vendor_id IS NULL )
691 AND ( p_vendor_id IS NULL )))
692 AND ( ( Recinfo.pin_id = p_pin_id)
693 OR ( ( Recinfo.pin_id IS NULL )
694 AND ( p_pin_id IS NULL )))
695 AND ( ( Recinfo.org_id = p_org_id)
696 OR ( ( Recinfo.org_id IS NULL )
697 AND ( p_org_id IS NULL )))
698 AND ( ( Recinfo.scheduled_time = p_scheduled_time)
699 OR ( ( Recinfo.scheduled_time IS NULL )
700 AND ( p_scheduled_time IS NULL )))
701 AND ( ( Recinfo.loaded_no_of_rows = p_loaded_no_of_rows)
702 OR ( ( Recinfo.loaded_no_of_rows IS NULL )
703 AND ( p_loaded_no_of_rows IS NULL )))
704 AND ( ( Recinfo.loaded_date = p_loaded_date)
705 OR ( ( Recinfo.loaded_date IS NULL )
706 AND ( p_loaded_date IS NULL )))
707 AND ( ( Recinfo.rows_to_skip = p_rows_to_skip)
708 OR ( ( Recinfo.rows_to_skip IS NULL )
709 AND ( p_rows_to_skip IS NULL )))
710 AND ( ( Recinfo.processed_rows = p_processed_rows)
711 OR ( ( Recinfo.processed_rows IS NULL )
712 AND ( p_processed_rows IS NULL )))
713 AND ( ( Recinfo.headings_flag = p_headings_flag)
714 OR ( ( Recinfo.headings_flag IS NULL )
715 AND ( p_headings_flag IS NULL )))
716 AND ( ( Recinfo.expiry_date = p_expiry_date)
717 OR ( ( Recinfo.expiry_date IS NULL )
718 AND ( p_expiry_date IS NULL )))
719 AND ( ( Recinfo.purge_date = p_purge_date)
720 OR ( ( Recinfo.purge_date IS NULL )
721 AND ( p_purge_date IS NULL )))
722 AND ( ( Recinfo.description = p_description)
723 OR ( ( Recinfo.description IS NULL )
724 AND ( p_description IS NULL )))
725 AND ( ( Recinfo.keywords = p_keywords)
726 OR ( ( Recinfo.keywords IS NULL )
727 AND ( p_keywords IS NULL )))
728 AND ( ( Recinfo.transactional_cost = p_transactional_cost)
729 OR ( ( Recinfo.transactional_cost IS NULL )
730 AND ( p_transactional_cost IS NULL )))
731 AND ( ( Recinfo.transactional_currency_code = p_transactional_currency_code)
732 OR ( ( Recinfo.transactional_currency_code IS NULL )
733 AND ( p_transactional_currency_code IS NULL )))
734 AND ( ( Recinfo.functional_cost = p_functional_cost)
735 OR ( ( Recinfo.functional_cost IS NULL )
736 AND ( p_functional_cost IS NULL )))
737 AND ( ( Recinfo.functional_currency_code = p_functional_currency_code)
738 OR ( ( Recinfo.functional_currency_code IS NULL )
739 AND ( p_functional_currency_code IS NULL )))
740 AND ( ( Recinfo.terminated_by = p_terminated_by)
741 OR ( ( Recinfo.terminated_by IS NULL )
745 AND ( p_enclosed_by IS NULL )))
742 AND ( p_terminated_by IS NULL )))
743 AND ( ( Recinfo.enclosed_by = p_enclosed_by)
744 OR ( ( Recinfo.enclosed_by IS NULL )
746 AND ( ( Recinfo.data_filename = p_data_filename)
747 OR ( ( Recinfo.data_filename IS NULL )
748 AND ( p_data_filename IS NULL )))
749 AND ( ( Recinfo.process_immed_flag = p_process_immed_flag)
750 OR ( ( Recinfo.process_immed_flag IS NULL )
751 AND ( p_process_immed_flag IS NULL )))
752 AND ( ( Recinfo.dedupe_flag = p_dedupe_flag)
753 OR ( ( Recinfo.dedupe_flag IS NULL )
754 AND ( p_dedupe_flag IS NULL )))
755 AND ( ( Recinfo.attribute_category = p_attribute_category)
756 OR ( ( Recinfo.attribute_category IS NULL )
757 AND ( p_attribute_category IS NULL )))
758 AND ( ( Recinfo.attribute1 = p_attribute1)
759 OR ( ( Recinfo.attribute1 IS NULL )
760 AND ( p_attribute1 IS NULL )))
761 AND ( ( Recinfo.attribute2 = p_attribute2)
762 OR ( ( Recinfo.attribute2 IS NULL )
763 AND ( p_attribute2 IS NULL )))
764 AND ( ( Recinfo.attribute3 = p_attribute3)
765 OR ( ( Recinfo.attribute3 IS NULL )
766 AND ( p_attribute3 IS NULL )))
767 AND ( ( Recinfo.attribute4 = p_attribute4)
768 OR ( ( Recinfo.attribute4 IS NULL )
769 AND ( p_attribute4 IS NULL )))
770 AND ( ( Recinfo.attribute5 = p_attribute5)
771 OR ( ( Recinfo.attribute5 IS NULL )
772 AND ( p_attribute5 IS NULL )))
773 AND ( ( Recinfo.attribute6 = p_attribute6)
774 OR ( ( Recinfo.attribute6 IS NULL )
775 AND ( p_attribute6 IS NULL )))
776 AND ( ( Recinfo.attribute7 = p_attribute7)
777 OR ( ( Recinfo.attribute7 IS NULL )
778 AND ( p_attribute7 IS NULL )))
779 AND ( ( Recinfo.attribute8 = p_attribute8)
780 OR ( ( Recinfo.attribute8 IS NULL )
781 AND ( p_attribute8 IS NULL )))
782 AND ( ( Recinfo.attribute9 = p_attribute9)
783 OR ( ( Recinfo.attribute9 IS NULL )
784 AND ( p_attribute9 IS NULL )))
785 AND ( ( Recinfo.attribute10 = p_attribute10)
786 OR ( ( Recinfo.attribute10 IS NULL )
787 AND ( p_attribute10 IS NULL )))
788 AND ( ( Recinfo.attribute11 = p_attribute11)
789 OR ( ( Recinfo.attribute11 IS NULL )
790 AND ( p_attribute11 IS NULL )))
791 AND ( ( Recinfo.attribute12 = p_attribute12)
792 OR ( ( Recinfo.attribute12 IS NULL )
793 AND ( p_attribute12 IS NULL )))
794 AND ( ( Recinfo.attribute13 = p_attribute13)
795 OR ( ( Recinfo.attribute13 IS NULL )
796 AND ( p_attribute13 IS NULL )))
797 AND ( ( Recinfo.attribute14 = p_attribute14)
798 OR ( ( Recinfo.attribute14 IS NULL )
799 AND ( p_attribute14 IS NULL )))
800 AND ( ( Recinfo.attribute15 = p_attribute15)
801 OR ( ( Recinfo.attribute15 IS NULL )
802 AND ( p_attribute15 IS NULL )))
803 AND ( ( Recinfo.custom_setup_id = p_custom_setup_id)
804 OR ( ( Recinfo.custom_setup_id IS NULL )
805 AND ( p_custom_setup_id IS NULL )))
806 AND ( ( Recinfo.country = p_country)
807 OR ( ( Recinfo.country IS NULL )
808 AND ( p_country IS NULL )))
809 AND ( ( Recinfo.usage = p_usage)
810 OR ( ( Recinfo.usage IS NULL )
811 AND ( p_usage IS NULL )))
812 AND ( ( Recinfo.number_of_records = p_number_of_records)
813 OR ( ( Recinfo.number_of_records IS NULL )
814 AND ( p_number_of_records IS NULL )))
815 AND ( ( Recinfo.data_file_name = p_data_file_name)
816 OR ( ( Recinfo.data_file_name IS NULL )
817 AND ( p_data_file_name IS NULL )))
818 AND ( ( Recinfo.b2b_flag = p_b2b_flag)
819 OR ( ( Recinfo.b2b_flag IS NULL )
820 AND ( p_b2b_flag IS NULL )))
821 AND ( ( Recinfo.rented_list_flag = p_rented_list_flag)
822 OR ( ( Recinfo.rented_list_flag IS NULL )
823 AND ( p_rented_list_flag IS NULL )))
824 AND ( ( Recinfo.server_flag = p_server_flag)
825 OR ( ( Recinfo.server_flag IS NULL )
826 AND ( p_server_flag IS NULL )))
827 AND ( ( Recinfo.log_file_name = p_log_file_name)
828 OR ( ( Recinfo.log_file_name IS NULL )
829 AND ( p_log_file_name IS NULL )))
830 AND ( ( Recinfo.number_of_failed_records = p_number_of_failed_records)
831 OR ( ( Recinfo.number_of_failed_records IS NULL )
832 AND ( p_number_of_failed_records IS NULL )))
833 AND ( ( Recinfo.number_of_duplicate_records = p_number_of_duplicate_records)
834 OR ( ( Recinfo.number_of_duplicate_records IS NULL )
835 AND ( p_number_of_duplicate_records IS NULL )))
836 AND ( ( Recinfo.enable_word_replacement_flag = p_enable_word_replacement_flag)
837 OR ( ( Recinfo.enable_word_replacement_flag IS NULL )
838 AND ( p_enable_word_replacement_flag IS NULL )))
839 ) THEN
840 RETURN;
841 ELSE
842 FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_CHANGED');
843 APP_EXCEPTION.RAISE_EXCEPTION;
844 END IF;
845 END Lock_Row;
846
847 -- --------------------------------------------------------
848 procedure ADD_LANGUAGE
849 is
850 begin
851 delete from AMS_IMP_LIST_HEADERS_ALL_TL T
852 where not exists
853 (select NULL
854 from AMS_IMP_LIST_HEADERS_ALL B
855 where B.IMPORT_LIST_HEADER_ID = T.IMPORT_LIST_HEADER_ID
856 );
857
858 update AMS_IMP_LIST_HEADERS_ALL_TL T set (
859 NAME,
860 DESCRIPTION
861 ) = (select
862 B.NAME,
863 B.DESCRIPTION
864 from AMS_IMP_LIST_HEADERS_ALL_TL B
865 where B.IMPORT_LIST_HEADER_ID = T.IMPORT_LIST_HEADER_ID
866 and B.LANGUAGE = T.SOURCE_LANG)
867 where (
868 T.IMPORT_LIST_HEADER_ID,
869 T.LANGUAGE
870 ) in (select
871 SUBT.IMPORT_LIST_HEADER_ID,
872 SUBT.LANGUAGE
873 from AMS_IMP_LIST_HEADERS_ALL_TL SUBB, AMS_IMP_LIST_HEADERS_ALL_TL SUBT
874 where SUBB.IMPORT_LIST_HEADER_ID = SUBT.IMPORT_LIST_HEADER_ID
875 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
876 and (SUBB.NAME <> SUBT.NAME
877 or (SUBB.NAME is null and SUBT.NAME is not null)
878 or (SUBB.NAME is not null and SUBT.NAME is null)
879 or SUBB.DESCRIPTION <> SUBT.DESCRIPTION
880 or (SUBB.DESCRIPTION is null and SUBT.DESCRIPTION is not null)
881 or (SUBB.DESCRIPTION is not null and SUBT.DESCRIPTION is null)
882 ));
883
884 insert into AMS_IMP_LIST_HEADERS_ALL_TL (
885 NAME,
886 DESCRIPTION,
887 LAST_UPDATE_LOGIN,
888 IMPORT_LIST_HEADER_ID,
889 LAST_UPDATE_DATE,
890 LAST_UPDATE_BY,
891 CREATION_DATE,
892 CREATED_BY,
893 LANGUAGE,
894 SOURCE_LANG
895 ) select /*+ ORDERED */
896 B.NAME,
897 B.DESCRIPTION,
898 B.LAST_UPDATE_LOGIN,
899 B.IMPORT_LIST_HEADER_ID,
900 B.LAST_UPDATE_DATE,
901 B.LAST_UPDATE_BY,
902 B.CREATION_DATE,
903 B.CREATED_BY,
904 L.LANGUAGE_CODE,
905 B.SOURCE_LANG
906 from AMS_IMP_LIST_HEADERS_ALL_TL B, FND_LANGUAGES L
907 where L.INSTALLED_FLAG in ('I', 'B')
908 and B.LANGUAGE = userenv('LANG')
909 and not exists
910 (select NULL
911 from AMS_IMP_LIST_HEADERS_ALL_TL T
912 where T.IMPORT_LIST_HEADER_ID = B.IMPORT_LIST_HEADER_ID
913 and T.LANGUAGE = L.LANGUAGE_CODE);
914
915 end ADD_LANGUAGE;
916
917 END AMS_IMP_LIST_HEADERS_PKG;