1 PACKAGE AMS_Import_List_PVT AUTHID CURRENT_USER AS
2 /* $Header: amsvimps.pls 120.1 2005/12/29 20:10:27 ryedator noship $ */
3 -- ===============================================================
4 -- Start of Comments
5 -- Package name
6 -- AMS_Import_List_PVT
7 -- Purpose
8 --
9 -- History
10 --
11 -- 06-JUNE-2002 huili added three columns "SERVER_NAME", "USER_NAME"
12 -- and "PASSWORD" to the ams_import_rec_type def.
13 -- 10-JUNE-2002 huili added the "file_type" to the "Duplicate_Import_List" module.
14 -- 18-JUNE-2002 huili added the "RECORD_UPDATE_FLAG" and "ERROR_THRESHOLD" to
15 -- the record definition.
16 -- NOTE
17 --
18 -- End of Comments
19 -- ===============================================================
20
21 -- Default number of records fetch per call
22 G_DEFAULT_NUM_REC_FETCH NUMBER := 30;
23 --===================================================================
24 -- Start of Comments
25 -- -------------------------------------------------------
26 -- Record name
27 -- ams_import_rec_type
28 -- -------------------------------------------------------
29 -- Parameters:
30 -- Required
31 --
32 -- Defaults
33 --
34 -- Note: This is automatic generated record definition, it includes all columns
35 -- defined in the table, developer must manually add or delete some of the attributes.
36 --
37 -- End of Comments
38
39 --===================================================================
40 TYPE ams_import_rec_type IS RECORD
41 (
42 import_list_header_id NUMBER := FND_API.G_MISS_NUM,
43 last_update_date DATE := FND_API.G_MISS_DATE,
44 last_updated_by NUMBER := FND_API.G_MISS_NUM,
45 creation_date DATE := FND_API.G_MISS_DATE,
46 created_by NUMBER := FND_API.G_MISS_NUM,
47 last_update_login NUMBER := FND_API.G_MISS_NUM,
48 object_version_number NUMBER := FND_API.G_MISS_NUM,
49 view_application_id NUMBER := FND_API.G_MISS_NUM,
50 name VARCHAR2(120) := FND_API.G_MISS_CHAR,
51 version VARCHAR2(10) := FND_API.G_MISS_CHAR,
52 import_type VARCHAR2(30) := FND_API.G_MISS_CHAR,
53 owner_user_id NUMBER := FND_API.G_MISS_NUM,
54 list_source_type_id NUMBER := FND_API.G_MISS_NUM,
55 status_code VARCHAR2(30) := FND_API.G_MISS_CHAR,
56 status_date DATE := FND_API.G_MISS_DATE,
57 user_status_id NUMBER := FND_API.G_MISS_NUM,
58 source_system VARCHAR2(4000) := FND_API.G_MISS_CHAR,
59 vendor_id NUMBER := FND_API.G_MISS_NUM,
60 pin_id NUMBER := FND_API.G_MISS_NUM,
61 org_id NUMBER := FND_API.G_MISS_NUM,
62 scheduled_time DATE := FND_API.G_MISS_DATE,
63 loaded_no_of_rows NUMBER := FND_API.G_MISS_NUM,
64 loaded_date DATE := FND_API.G_MISS_DATE,
65 rows_to_skip NUMBER := FND_API.G_MISS_NUM,
66 processed_rows NUMBER := FND_API.G_MISS_NUM,
67 headings_flag VARCHAR2(1) := FND_API.G_MISS_CHAR,
68 expiry_date DATE := FND_API.G_MISS_DATE,
69 purge_date DATE := FND_API.G_MISS_DATE,
70 description VARCHAR2(4000) := FND_API.G_MISS_CHAR,
71 keywords VARCHAR2(4000) := FND_API.G_MISS_CHAR,
72 transactional_cost NUMBER := FND_API.G_MISS_NUM,
73 transactional_currency_code VARCHAR2(15) := FND_API.G_MISS_CHAR,
74 functional_cost NUMBER := FND_API.G_MISS_NUM,
75 functional_currency_code VARCHAR2(15) := FND_API.G_MISS_CHAR,
76 terminated_by VARCHAR2(30) := FND_API.G_MISS_CHAR,
77 enclosed_by VARCHAR2(30) := FND_API.G_MISS_CHAR,
78 data_filename VARCHAR2(1000) := FND_API.G_MISS_CHAR,
79 process_immed_flag VARCHAR2(1) := FND_API.G_MISS_CHAR,
80 dedupe_flag VARCHAR2(1) := FND_API.G_MISS_CHAR,
81 attribute_category VARCHAR2(30) := FND_API.G_MISS_CHAR,
82 attribute1 VARCHAR2(150) := FND_API.G_MISS_CHAR,
83 attribute2 VARCHAR2(150) := FND_API.G_MISS_CHAR,
84 attribute3 VARCHAR2(150) := FND_API.G_MISS_CHAR,
85 attribute4 VARCHAR2(150) := FND_API.G_MISS_CHAR,
86 attribute5 VARCHAR2(150) := FND_API.G_MISS_CHAR,
87 attribute6 VARCHAR2(150) := FND_API.G_MISS_CHAR,
88 attribute7 VARCHAR2(150) := FND_API.G_MISS_CHAR,
89 attribute8 VARCHAR2(150) := FND_API.G_MISS_CHAR,
90 attribute9 VARCHAR2(150) := FND_API.G_MISS_CHAR,
91 attribute10 VARCHAR2(150) := FND_API.G_MISS_CHAR,
92 attribute11 VARCHAR2(150) := FND_API.G_MISS_CHAR,
93 attribute12 VARCHAR2(150) := FND_API.G_MISS_CHAR,
94 attribute13 VARCHAR2(150) := FND_API.G_MISS_CHAR,
95 attribute14 VARCHAR2(150) := FND_API.G_MISS_CHAR,
96 attribute15 VARCHAR2(150) := FND_API.G_MISS_CHAR,
97 custom_setup_id NUMBER := FND_API.G_MISS_NUM,
98 country NUMBER := FND_API.G_MISS_NUM,
99 usage NUMBER := FND_API.G_MISS_NUM,
100 number_of_records NUMBER := FND_API.G_MISS_NUM,
101 data_file_name VARCHAR2(1000) := FND_API.G_MISS_CHAR,
102 b2b_flag VARCHAR2(1) := FND_API.G_MISS_CHAR,
103 rented_list_flag VARCHAR2(1) := FND_API.G_MISS_CHAR,
104 server_flag VARCHAR2(1) := FND_API.G_MISS_CHAR,
105 log_file_name NUMBER := FND_API.G_MISS_NUM,
106 number_of_failed_records NUMBER := FND_API.G_MISS_NUM,
107 number_of_duplicate_records NUMBER := FND_API.G_MISS_NUM,
108 enable_word_replacement_flag VARCHAR2(1) := FND_API.G_MISS_CHAR,
109 validate_file VARCHAR2(1) := FND_API.G_MISS_CHAR,
110 server_name AMS_IMP_LIST_HEADERS_ALL.SERVER_NAME%TYPE := FND_API.G_MISS_CHAR,
111 user_name AMS_IMP_LIST_HEADERS_ALL.USER_NAME%TYPE := FND_API.G_MISS_CHAR,
112 password AMS_IMP_LIST_HEADERS_ALL.PASSWORD%TYPE := FND_API.G_MISS_CHAR,
113 upload_flag AMS_IMP_LIST_HEADERS_ALL.UPLOAD_FLAG%TYPE := FND_API.G_MISS_CHAR,
114 parent_imp_header_id AMS_IMP_LIST_HEADERS_ALL.PARENT_IMP_HEADER_ID%TYPE := FND_API.G_MISS_NUM,
115 record_update_flag AMS_IMP_LIST_HEADERS_ALL.RECORD_UPDATE_FLAG%TYPE := FND_API.G_MISS_CHAR,
116 error_threshold AMS_IMP_LIST_HEADERS_ALL.ERROR_THRESHOLD%TYPE := FND_API.G_MISS_NUM,
117 charset AMS_IMP_LIST_HEADERS_ALL.CHARSET%TYPE := FND_API.G_MISS_CHAR);
118
119 g_miss_ams_import_rec ams_import_rec_type;
120 TYPE ams_import_tbl_type IS TABLE OF ams_import_rec_type INDEX BY BINARY_INTEGER;
121 g_miss_ams_import_tbl ams_import_tbl_type;
122
123
124 -- ==============================================================================
125 -- Start of Comments
126 -- ==============================================================================
127 -- API Name
128 -- Duplicate_Import_List
129 -- Type
130 -- public
131 -- Pre-Req
132 --
133 -- Parameters
134 --
135 -- Version : Current version 1.0
136 -- Note: Copy the record with the import_list_header_id passed in and create a new row in the header
137 -- table.
138 --
139 -- End of Comments
140 -- ==============================================================================
141 --
142 /*
143 PROCEDURE Do_Recurring (
144 p_api_version_number IN NUMBER,
145 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
146 p_commit IN VARCHAR2 := FND_API.G_FALSE,
147 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
148
149 x_return_status OUT NOCOPY VARCHAR2,
150 x_msg_count OUT NOCOPY NUMBER,
151 x_msg_data OUT NOCOPY VARCHAR2,
152
153 p_obj_id IN NUMBER,
154 p_repeat_mode IN VARCHAR2,
155 p_repeate_time IN VARCHAR2,
156 p_repeate_end_time IN VARCHAR2,
157 p_repeate_unit IN VARCHAR2,
158 p_repeate_interval IN NUMBER,
159 p_recur_type IN VARCHAR2);
160 */
161
162 -- ==============================================================================
163 -- Start of Comments
164 -- ==============================================================================
165 -- API Name
166 -- Duplicate_Import_List
167 -- Type
168 -- public
169 -- Pre-Req
170 --
171 -- Parameters
172 --
173 -- IN
174 -- p_api_version_number IN NUMBER Required
175 -- p_init_msg_list IN VARCHAR2 Optional Default = FND_API_G_FALSE
176 -- p_commit IN VARCHAR2 Optional Default = FND_API.G_FALSE
177 -- p_validation_level IN NUMBER Optional Default = FND_API.G_VALID_LEVEL_FULL
178 -- p_import_list_header_id IN NUMBER Required
179 --
180 -- OUT
181 -- x_return_status OUT VARCHAR2
182 -- x_msg_count OUT NUMBER
183 -- x_msg_data OUT VARCHAR2
184 -- x_ams_import_rec OUT ams_import_rec_type
185 -- Version : Current version 1.0
186 -- Note: Copy the record with the import_list_header_id passed in and create a new row in the header
187 -- table.
188 --
189 -- End of Comments
190 -- ==============================================================================
191 --
192
193 PROCEDURE Duplicate_Import_List (
194 p_api_version_number IN NUMBER,
195 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
196 p_commit IN VARCHAR2 := FND_API.G_FALSE,
197 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
198
199 x_return_status OUT NOCOPY VARCHAR2,
200 x_msg_count OUT NOCOPY NUMBER,
201 x_msg_data OUT NOCOPY VARCHAR2,
202
203 p_import_list_header_id IN NUMBER,
204 x_ams_import_rec OUT NOCOPY ams_import_rec_type,
205 x_file_type OUT NOCOPY VARCHAR2);
206
207 -- ==============================================================================
208 -- Start of Comments
209 -- ==============================================================================
210 -- API Name
211 -- Create_Import_List
212 -- Type
213 -- Private
214 -- Pre-Req
215 --
216 -- Parameters
217 --
218 -- IN
219 -- p_api_version_number IN NUMBER Required
220 -- p_init_msg_list IN VARCHAR2 Optional Default = FND_API_G_FALSE
221 -- p_commit IN VARCHAR2 Optional Default = FND_API.G_FALSE
222 -- p_validation_level IN NUMBER Optional Default = FND_API.G_VALID_LEVEL_FULL
223 -- p_ams_import_rec IN ams_import_rec_type Required
224 --
225 -- OUT
226 -- x_return_status OUT VARCHAR2
227 -- x_msg_count OUT NUMBER
228 -- x_msg_data OUT VARCHAR2
229 -- Version : Current version 1.0
230 -- Note: This automatic generated procedure definition, it includes standard IN/OUT parameters
231 -- and basic operation, developer must manually add parameters and business logic as necessary.
232 --
233 -- End of Comments
234 -- ==============================================================================
235 --
236
237 PROCEDURE Create_Import_List(
238 p_api_version_number IN NUMBER,
239 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
240 p_commit IN VARCHAR2 := FND_API.G_FALSE,
241 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
242
243 x_return_status OUT NOCOPY VARCHAR2,
244 x_msg_count OUT NOCOPY NUMBER,
245 x_msg_data OUT NOCOPY VARCHAR2,
246
247 p_ams_import_rec IN ams_import_rec_type := g_miss_ams_import_rec,
248 x_import_list_header_id OUT NOCOPY NUMBER
249 );
250
251 -- ==============================================================================
252 -- Start of Comments
253 -- ==============================================================================
254 -- API Name
255 -- Update_Import_List
256 -- Type
257 -- Private
258 -- Pre-Req
259 --
260 -- Parameters
261 --
262 -- IN
263 -- p_api_version_number IN NUMBER Required
264 -- p_init_msg_list IN VARCHAR2 Optional Default = FND_API_G_FALSE
265 -- p_commit IN VARCHAR2 Optional Default = FND_API.G_FALSE
266 -- p_validation_level IN NUMBER Optional Default = FND_API.G_VALID_LEVEL_FULL
267 -- p_ams_import_rec IN ams_import_rec_type Required
268 --
269 -- OUT
270 -- x_return_status OUT VARCHAR2
271 -- x_msg_count OUT NUMBER
272 -- x_msg_data OUT VARCHAR2
273 -- Version : Current version 1.0
274 -- Note: This automatic generated procedure definition, it includes standard IN/OUT parameters
275 -- and basic operation, developer must manually add parameters and business logic as necessary.
276 --
277 -- End of Comments
278 -- ==============================================================================
279 --
280
281 PROCEDURE Update_Import_List(
282 p_api_version_number IN NUMBER,
283 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
284 p_commit IN VARCHAR2 := FND_API.G_FALSE,
285 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
286
287 x_return_status OUT NOCOPY VARCHAR2,
288 x_msg_count OUT NOCOPY NUMBER,
289 x_msg_data OUT NOCOPY VARCHAR2,
290
291 p_ams_import_rec IN ams_import_rec_type,
292 x_object_version_number OUT NOCOPY NUMBER
293 );
294
295 -- ==============================================================================
296 -- Start of Comments
297 -- ==============================================================================
298 -- API Name
299 -- Delete_Import_List
300 -- Type
301 -- Private
302 -- Pre-Req
303 --
304 -- Parameters
305 --
306 -- IN
307 -- p_api_version_number IN NUMBER Required
308 -- p_init_msg_list IN VARCHAR2 Optional Default = FND_API_G_FALSE
309 -- p_commit IN VARCHAR2 Optional Default = FND_API.G_FALSE
310 -- p_validation_level IN NUMBER Optional Default = FND_API.G_VALID_LEVEL_FULL
311 -- p_IMPORT_LIST_HEADER_ID IN NUMBER
312 -- p_object_version_number IN NUMBER Optional Default = NULL
313 --
314 -- OUT
318 -- Version : Current version 1.0
315 -- x_return_status OUT VARCHAR2
316 -- x_msg_count OUT NUMBER
317 -- x_msg_data OUT VARCHAR2
319 -- Note: This automatic generated procedure definition, it includes standard IN/OUT parameters
320 -- and basic operation, developer must manually add parameters and business logic as necessary.
321 --
322 -- End of Comments
323 -- ==============================================================================
324 --
325
326 PROCEDURE Delete_Import_List(
327 p_api_version_number IN NUMBER,
328 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
329 p_commit IN VARCHAR2 := FND_API.G_FALSE,
330 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
331 x_return_status OUT NOCOPY VARCHAR2,
332 x_msg_count OUT NOCOPY NUMBER,
333 x_msg_data OUT NOCOPY VARCHAR2,
334 p_import_list_header_id IN NUMBER,
335 p_object_version_number IN NUMBER
336 );
337
338 -- ==============================================================================
339 -- Start of Comments
340 -- ==============================================================================
341 -- API Name
342 -- Lock_Import_List
343 -- Type
344 -- Private
345 -- Pre-Req
346 --
347 -- Parameters
348 --
349 -- IN
350 -- p_api_version_number IN NUMBER Required
351 -- p_init_msg_list IN VARCHAR2 Optional Default = FND_API_G_FALSE
352 -- p_commit IN VARCHAR2 Optional Default = FND_API.G_FALSE
353 -- p_validation_level IN NUMBER Optional Default = FND_API.G_VALID_LEVEL_FULL
354 -- p_ams_import_rec IN ams_import_rec_type Required
355 --
356 -- OUT
357 -- x_return_status OUT VARCHAR2
358 -- x_msg_count OUT NUMBER
359 -- x_msg_data OUT VARCHAR2
360 -- Version : Current version 1.0
361 -- Note: This automatic generated procedure definition, it includes standard IN/OUT parameters
362 -- and basic operation, developer must manually add parameters and business logic as necessary.
363 --
364 -- End of Comments
365 -- ==============================================================================
366 --
367
368 PROCEDURE Lock_Import_List(
369 p_api_version_number IN NUMBER,
370 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
371
372 x_return_status OUT NOCOPY VARCHAR2,
373 x_msg_count OUT NOCOPY NUMBER,
374 x_msg_data OUT NOCOPY VARCHAR2,
375
376 p_import_list_header_id IN NUMBER,
377 p_object_version IN NUMBER
378 );
379
380
381 -- Start of Comments
382 --
383 -- validation procedures
384 --
385 -- p_validation_mode is a constant defined in AMS_UTILITY_PVT package
386 -- For create: G_CREATE, for update: G_UPDATE
387 -- Note: 1. This is automated generated item level validation procedure.
388 -- The actual validation detail is needed to be added.
389 -- 2. We can also validate table instead of record. There will be an option for user to choose.
390 -- End of Comments
391
392 PROCEDURE Validate_import_list(
393 p_api_version_number IN NUMBER,
394 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
395 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
396 p_ams_import_rec IN ams_import_rec_type,
397 x_return_status OUT NOCOPY VARCHAR2,
398 x_msg_count OUT NOCOPY NUMBER,
399 x_msg_data OUT NOCOPY VARCHAR2
400 );
401
402 -- Start of Comments
403 --
404 -- validation procedures
405 --
406 -- p_validation_mode is a constant defined in AMS_UTILITY_PVT package
407 -- For create: G_CREATE, for update: G_UPDATE
408 -- Note: 1. This is automated generated item level validation procedure.
409 -- The actual validation detail is needed to be added.
410 -- 2. Validate the unique keys, lookups here
411 -- End of Comments
412
413 PROCEDURE Check_ams_import_Items (
414 P_ams_import_rec IN ams_import_rec_type,
415 p_validation_mode IN VARCHAR2,
416 x_return_status OUT NOCOPY VARCHAR2
417 );
418
419 -- Start of Comments
420 --
421 -- Record level validation procedures
422 --
423 -- p_validation_mode is a constant defined in AMS_UTILITY_PVT package
424 -- For create: G_CREATE, for update: G_UPDATE
425 -- Note: 1. This is automated generated item level validation procedure.
426 -- The actual validation detail is needed to be added.
427 -- 2. Developer can manually added inter-field level validation.
428 -- End of Comments
429
430 PROCEDURE Validate_ams_import_rec(
431 p_api_version_number IN NUMBER,
432 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
433 x_return_status OUT NOCOPY VARCHAR2,
434 x_msg_count OUT NOCOPY NUMBER,
435 x_msg_data OUT NOCOPY VARCHAR2,
436 p_ams_import_rec IN ams_import_rec_type
437 );
438 END AMS_Import_List_PVT;