[Home] [Help]
PACKAGE BODY: APPS.AMS_IMPORTCLIENT_PVT
Source
1 PACKAGE BODY AMS_ImportClient_PVT AS
2 /* $Header: amsvmicb.pls 115.47 2004/03/17 18:53:46 huili ship $ */
3 --------------------------------------------------------------------------------
4 --
5 -- NAME
6 -- AMS_ImportClient_PVT
7 --
8 -- HISTORY
9 -- 12-Apr-2001 huili Created
10 -- 13-May-2001 huili Added the "Insert_Lead_Data" module
11 -- 04-June-2001 huili Added writing to log file
12 -- 16-June-2001 huili Added checking the "DEDUPE_FLAG" of the
13 -- "ams_imp_list_headers_all" table in the
14 -- "Insert_List_Data" procedure
15 -- 18-June-2001 huili Added Updating status_id in the
16 -- "ams_imp_list_headers_all" table
17 -- 18-June-2001 huili Took out the checking for "p_row_count"
18 -- in both lead import and list import
19 -- stored procedure.
20 -- 23-July-2001 huili Changed the "SOURCE_SYSTEM" to "NEW".
21 -- 01-Oct-2001 huili Changed the alter sequence statement to
22 -- use a loop to pick up sequences.
23 -- 19-Oct-2001 huili Added code to handle more that 40 columns.
24 -- 19-Oct-2001 huili Replace single quote with two single quotes in constructing
25 -- the dynamic insertion command.
26 -- 24-JAN-2002 huili Comment out the "SOURCE_SYSTEM" default data for lead import.
27 -- 01-MAR-2002 huili Remove populating error for trancated columns.
28 -- 07-MAR-2002 huili Remove the update for number of rows for both list
29 -- and lead import.
30 -- 07-MAR-2002 huili Always insert one row into the "AMS_LIST_IMPORT_ERRORS" table
31 -- for all kinds of errors in the "as_import_interface".
32 -- 10-April-2002 huili Added one more column "marketing_score" to the "c_lead_recs"
33 -- cursor.
34 -- 26-April-2002 huili Change all tables to varchar2(2000) table for bug #2345334.
35 -- 26-April-2002 huili Remove the "batch_id" checking for the cursor c_lead_error_txt.
36 -- 30-April-2002 huili Parse the cursor once to improve efficiency.
37 -- 17-May-2002 huili Add trim to the "Load_Lead_Data_To_Interface" stored procedure.
38 -- 08-July-2002 huili Populate "batch_id" into the source line table.
39 ------------------------------------------------------------------------------
40
41 --
42 -- Global variables and constants.
43 G_PKG_NAME CONSTANT VARCHAR2(30) := 'AMS_IMPORTCLIENT_PVT'; -- Name of the current package.
44 G_DEBUG_FLAG CONSTANT VARCHAR2(1) := 'N';
45 G_COL_NUM CONSTANT NUMBER := 40;
46 G_ARC_IMPORT_HEADER CONSTANT VARCHAR2(30) := 'IMPH';
47 G_ROW_PARSE_NO_ERROR CONSTANT NUMBER := 0;
48 G_ROW_PARSE_TOO_LARGE CONSTANT NUMBER := 1;
49
50 G_ROW_PARSE_TOO_LARGE_MSG CONSTANT VARCHAR2(2000) := 'This field is too large!';
51 G_ROW_PARSE_OTHER_MSG CONSTANT VARCHAR2(2000) := 'Other error';
52
53 AMS_DEBUG_HIGH_ON boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_HIGH);
54 AMS_DEBUG_LOW_ON boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW);
55 AMS_DEBUG_MEDIUM_ON boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_MEDIUM);
56
57 PROCEDURE Append_More_Data (
58 p_str_col_names IN char_data_set_type_w,
59 p_str_data IN char_data_set_type_w,
60 p_num_col_names IN char_data_set_type_w,
61 p_num_data IN num_data_set_type_w,
62 p_col_clause IN OUT NOCOPY VARCHAR2,
63 p_val_clause IN OUT NOCOPY VARCHAR2
64 );
65
66 PROCEDURE Insert_Data (
67
68 p_api_version IN NUMBER,
69 p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE,
70 p_commit IN VARCHAR2 := FND_API.G_FALSE,
71
72 p_table_name IN VARCHAR2,
73 p_prim_key_name IN VARCHAR2,
74 p_seq_name IN VARCHAR2,
75
76 p_str_col_names IN char_data_set_type_w,
77 p_num_col_names IN char_data_set_type_w,
78 p_str_data IN char_data_set_type_w,
79 p_num_data IN num_data_set_type_w,
80 p_obj_version_num IN NUMBER := NULL, -- if NULL, do not insert this column
81 p_last_update_date_flag IN VARCHAR2 := FND_API.G_TRUE,-- need this column?
82 p_last_update_by_flag IN VARCHAR2 := FND_API.G_TRUE,-- need this column?
83 p_creation_date_flag IN VARCHAR2 := FND_API.G_TRUE,-- need this column?
84 p_created_by_flag IN VARCHAR2 := FND_API.G_TRUE,-- need this column?
85 p_ls_update_log_flag IN VARCHAR2 := FND_API.G_TRUE,-- need this column?
86
87 p_col_names IN char_data_set_type_w,
88 p_data IN char_data_set_type_w,
89 p_error_rows IN num_data_set_type_w,
90 p_row_count IN NUMBER,
91
92 x_return_status OUT NOCOPY VARCHAR2,
93 x_msg_count OUT NOCOPY NUMBER,
94 x_msg_data OUT NOCOPY VARCHAR2
95 );
96
97 PROCEDURE Create_List_import_Error (
98 p_api_version IN NUMBER,
99 p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE,
100 p_commit IN VARCHAR2 := FND_API.G_TRUE,
101 p_list_imp_error_rec IN AMS_LIST_IMPORT_ERRORS%ROWTYPE,
102
103 x_return_status OUT NOCOPY VARCHAR2,
104 x_imp_err_id OUT NOCOPY NUMBER
105 );
106 --- End forward modules
107
108 -- Start of comments
109 -- API Name Mark_Insert_Lead_Errors
110 -- Type Private
111 -- Pre-reqs None.
112 -- Function Mark lead errors to the "ams_imp_source_lines"
113 -- table and insert error records into the
114 -- "ams_list_import_errors" table.
115 -- Parameters
116 -- IN
117 -- p_import_list_header_id NUMBER Required
118 -- OUT x_return_status VARCHAR2
119 -- Version Current version: 1.0
120 -- Previous version: 1.0
121 -- Initial version: 1.0
122 -- End of comments
123 --
124 --
125 --
126 --
127 PROCEDURE Mark_Insert_Lead_Errors (
128 p_import_list_header_id IN NUMBER,
129 x_return_status OUT NOCOPY VARCHAR2
130 )
131 IS
132 L_API_NAME CONSTANT VARCHAR2(30) := 'Mark_Insert_Lead_Errors';
133 L_FULL_NAME CONSTANT VARCHAR2(60) := G_PKG_NAME ||'.'|| L_API_NAME;
134 L_MAX_ERROR_TXT_LENGTH CONSTANT NUMBER := 250;
135
136 CURSOR c_batch_id (p_import_list_header_id NUMBER) IS
137 SELECT batch_id
138 FROM ams_imp_list_headers_all
139 WHERE import_list_header_id = p_import_list_header_id;
140
141 CURSOR c_import_interface_data (p_batch_id NUMBER) IS
142 SELECT imp_list_header_number, import_interface_id,
143 DECODE (load_status,
144 'SUCCESS', 'SUCCESS',
145 'DUPLICATE', 'DUPLICATE',
146 'ERROR') load_status
147 FROM as_import_interface
148 WHERE batch_id = p_batch_id
149 AND load_status IN ('ERROR', 'SUCCESS', 'T-ERROR', 'UNEXP_ERROR', 'DUPLICATE');
150
151 CURSOR c_lead_error_txt (p_batch_id NUMBER, p_imp_interface_id NUMBER) IS
152 SELECT SUBSTR (error_text, 0, L_MAX_ERROR_TXT_LENGTH) error_text
153 FROM as_lead_import_errors
154 WHERE -- batch_id = p_batch_id AND
155 import_interface_id = p_imp_interface_id
156 ORDER BY LEAD_IMPORT_ERROR_ID DESC;
157
158 l_lead_error_rec c_lead_error_txt%ROWTYPE;
159 l_batch_id NUMBER;
160 l_list_imp_error_rec ams_list_import_errors%ROWTYPE;
161 l_imp_err_id NUMBER;
162 l_error_msg VARCHAR2(2000);
163
164 BEGIN
165 --
166 -- Initialize API return status to success.
167 --
168 x_return_status := FND_API.G_RET_STS_SUCCESS;
169
170 IF p_import_list_header_id IS NULL THEN
171 l_error_msg := 'Expected error in ' || L_FULL_NAME
172 || ' list import header is null';
173 RAISE FND_API.G_EXC_ERROR;
174 END IF;
175
176 OPEN c_batch_id (p_import_list_header_id);
177 FETCH c_batch_id INTO l_batch_id;
178
179 IF c_batch_id%FOUND THEN
180 FOR import_interface_data_rec IN c_import_interface_data (l_batch_id)
181 LOOP
182 BEGIN
183 UPDATE ams_imp_source_lines
184 SET load_status = import_interface_data_rec.load_status
185 WHERE import_source_line_id = import_interface_data_rec.imp_list_header_number;
186
187 --need to insert one row to the error table
188 IF import_interface_data_rec.load_status <> 'SUCCESS' THEN
189 OPEN c_lead_error_txt (l_batch_id,
190 import_interface_data_rec.import_interface_id);
191 FETCH c_lead_error_txt INTO l_lead_error_rec;
192
193 IF c_lead_error_txt%FOUND THEN
194 l_list_imp_error_rec.col1 := l_lead_error_rec.error_text;
195 ELSE
196 IF import_interface_data_rec.load_status = 'ERROR' then
197 l_list_imp_error_rec.col1 := 'Unexpected error reported from lead import.';
198 End IF;
199 IF import_interface_data_rec.load_status = 'DUPLICATE' then
200 l_list_imp_error_rec.col1 := 'This is a duplicate lead.';
201 End IF;
202 END IF;
203 CLOSE c_lead_error_txt;
204 l_list_imp_error_rec.import_list_header_id := p_import_list_header_id;
205 l_list_imp_error_rec.import_source_line_id
206 := import_interface_data_rec.imp_list_header_number;
207 l_list_imp_error_rec.import_type := 'LEAD';
208
209 l_list_imp_error_rec.error_type := 'E';
210 l_list_imp_error_rec.batch_id := l_batch_id;
211
212 Create_List_import_Error (
213 p_api_version => 1.0,
214 x_return_status => x_return_status,
215 p_list_imp_error_rec => l_list_imp_error_rec,
216 x_imp_err_id => l_imp_err_id);
217 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
218 l_error_msg := 'Expected error in ' || L_FULL_NAME
219 || ' can not create error record';
220 RAISE FND_API.G_EXC_ERROR;
221 END IF;
222
223 END IF;
224
225 EXCEPTION
226 WHEN FND_API.G_EXC_ERROR THEN
227 IF c_import_interface_data%ISOPEN THEN
228 CLOSE c_import_interface_data;
229 END IF;
230 IF c_batch_id%ISOPEN THEN
231 CLOSE c_batch_id;
232 END IF;
233 IF c_lead_error_txt%ISOPEN THEN
234 CLOSE c_lead_error_txt;
235 END IF;
236 RAISE FND_API.G_EXC_ERROR;
237 WHEN OTHERS THEN
238 IF c_import_interface_data%ISOPEN THEN
239 CLOSE c_import_interface_data;
240 END IF;
241 IF c_batch_id%ISOPEN THEN
242 CLOSE c_batch_id;
243 END IF;
244 IF c_lead_error_txt%ISOPEN THEN
245 CLOSE c_lead_error_txt;
246 END IF;
247 l_error_msg := 'Expected error in ' || L_FULL_NAME
248 || ' ' || SQLERRM;
249 RAISE FND_API.G_EXC_ERROR;
250 END;
251 END LOOP;
252 END IF;
253 CLOSE c_batch_id;
254
255 EXCEPTION
256 WHEN FND_API.G_EXC_ERROR THEN
257 AMS_Utility_PVT.Create_Log (
258 x_return_status => x_return_status,
259 p_arc_log_used_by => G_ARC_IMPORT_HEADER,
260 p_log_used_by_id => p_import_list_header_id,
261 p_msg_data => 'Expected error in ' || L_FULL_NAME,
262 p_msg_type => 'DEBUG'
263 );
264 x_return_status := FND_API.G_RET_STS_ERROR;
265 WHEN OTHERS THEN
266 l_error_msg := 'Unexpected error in '
267 || L_FULL_NAME || ': '|| SQLERRM;
268 AMS_Utility_PVT.Create_Log (
269 x_return_status => x_return_status,
270 p_arc_log_used_by => G_ARC_IMPORT_HEADER,
271 p_log_used_by_id => p_import_list_header_id,
272 p_msg_data => l_error_msg,
273 p_msg_type => 'DEBUG'
274 );
275 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
276 END Mark_Insert_Lead_Errors;
277
278 -- Start of comments
279 -- API Name Load_Lead_Data_To_Interface
280 -- Type Private
281 -- Pre-reqs None.
282 -- Function Transfer data from the "ams_lead_mapping_v" to
283 -- the "as_import_interface" table.
284 -- Parameters
285 -- IN
286 -- p_import_list_header_id NUMBER Required
287 -- OUT x_return_status VARCHAR2
288 -- Version Current version: 1.0
289 -- Previous version: 1.0
290 -- Initial version: 1.0
291 -- End of comments
292 PROCEDURE Load_Lead_Data_To_Interface (
293 p_import_list_header_id IN NUMBER,
294 x_return_status OUT NOCOPY VARCHAR2
295 )
296 IS
297 L_MAX_ROW_COUNT CONSTANT NUMBER := 10;
298
299 L_API_NAME CONSTANT VARCHAR2(30) := 'Load_Lead_Data_To_Interface';
300 L_FULL_NAME CONSTANT VARCHAR2(60) := G_PKG_NAME ||'.'|| L_API_NAME;
301 L_LOAD_TYPE VARCHAR2(20) := 'LEAD_LOAD';
302 l_current_date DATE := SYSDATE;
303 L_LOAD_DATE DATE := l_current_date;
304 L_LOAD_STATUS VARCHAR2(20) := 'NEW';
305 L_STATUS_CODE VARCHAR2(3) := 'NEW';
306
307 l_user_id NUMBER := FND_GLOBAL.User_ID;
308 l_login_id NUMBER := FND_GLOBAL.Conc_Login_ID;
309 l_interface_id num_data_set_type_w;
310
311 l_import_source_line_id num_data_set_type_w;
312 l_import_list_header_id num_data_set_type_w;
313 l_source_system varchar2_4000_set_type;
314 l_lead_note varchar2_4000_set_type;
315 l_promotion_code varchar2_4000_set_type;
316 l_customer_name varchar2_4000_set_type;
317 l_sic_code varchar2_4000_set_type;
318 l_analysis_fy varchar2_4000_set_type;
319 l_customer_category_code varchar2_4000_set_type;
320 l_fiscal_yearend_month varchar2_4000_set_type;
321 l_num_of_employees varchar2_4000_set_type;
322 l_potential_revenue_curr_fy varchar2_4000_set_type;
323 l_potential_revenue_next_fy varchar2_4000_set_type;
324 l_customer_rank varchar2_4000_set_type;
325 l_tax_reference varchar2_4000_set_type;
326 l_year_established varchar2_4000_set_type;
327 l_addr_do_not_mail_flag varchar2_4000_set_type;
328 l_url varchar2_4000_set_type;
329 l_cont_do_not_mail_flag varchar2_4000_set_type;
330 l_country varchar2_4000_set_type;
331 l_address1 varchar2_4000_set_type;
332 l_address2 varchar2_4000_set_type;
333 l_address3 varchar2_4000_set_type;
334 l_address4 varchar2_4000_set_type;
335 l_city varchar2_4000_set_type;
336 l_postal_code varchar2_4000_set_type;
337 l_state varchar2_4000_set_type;
338 l_province varchar2_4000_set_type;
339 l_county varchar2_4000_set_type;
340 l_email_address varchar2_4000_set_type;
341 l_sex_code varchar2_4000_set_type;
342 l_salutation varchar2_4000_set_type;
343 l_last_name varchar2_4000_set_type;
344 l_title varchar2_4000_set_type;
345 l_first_name varchar2_4000_set_type;
346 l_job_title varchar2_4000_set_type;
347 l_phone_number varchar2_4000_set_type;
348 l_phone_status varchar2_4000_set_type;
349 l_phone_type varchar2_4000_set_type;
350 l_area_code varchar2_4000_set_type;
351 l_extension varchar2_4000_set_type;
352 l_middle_initial varchar2_4000_set_type;
353 l_job_title_code varchar2_4000_set_type;
354 l_mail_stop varchar2_4000_set_type;
355 l_fax_number varchar2_4000_set_type;
356 l_fax_area_code varchar2_4000_set_type;
357 l_do_not_email_flag varchar2_4000_set_type;
361 l_channel_code varchar2_4000_set_type;
358 l_do_not_fax_flag varchar2_4000_set_type;
359 l_do_not_phone_flag varchar2_4000_set_type;
360 l_contact_role_code varchar2_4000_set_type;
362 l_budget_amount varchar2_4000_set_type;
363 l_budget_status_code varchar2_4000_set_type;
364 l_currency_code varchar2_4000_set_type;
365 l_decision_timeframe_code varchar2_4000_set_type;
366 l_close_reason varchar2_4000_set_type;
367 l_parent_project varchar2_4000_set_type;
368 l_description varchar2_4000_set_type;
369 l_vehicle_response_code varchar2_4000_set_type;
370 l_interest_type_id_1 varchar2_4000_set_type;
371 l_primary_interest_code_id_1 varchar2_4000_set_type;
372 l_secondary_interest_code_id_1 varchar2_4000_set_type;
373 l_inventory_item_id_1 varchar2_4000_set_type;
374 l_organization_id_1 varchar2_4000_set_type;
375 l_uom_code_1 varchar2_4000_set_type;
376 l_quantity_1 varchar2_4000_set_type;
377 l_budget_amount_1 varchar2_4000_set_type;
378 l_source_promotion_id_1 varchar2_4000_set_type;
379 l_offer_id_1 varchar2_4000_set_type;
380 l_interest_type_id_2 varchar2_4000_set_type;
381 l_primary_interest_code_id_2 varchar2_4000_set_type;
382 l_secondary_interest_code_id_2 varchar2_4000_set_type;
383 l_inventory_item_id_2 varchar2_4000_set_type;
384 l_organization_id_2 varchar2_4000_set_type;
385 l_uom_code_2 varchar2_4000_set_type;
386 l_quantity_2 varchar2_4000_set_type;
387 l_budget_amount_2 varchar2_4000_set_type;
388 l_source_promotion_id_2 varchar2_4000_set_type;
389 l_offer_id_2 varchar2_4000_set_type;
390 l_interest_type_id_3 varchar2_4000_set_type;
391 l_primary_interest_code_id_3 varchar2_4000_set_type;
392 l_secondary_interest_code_id_3 varchar2_4000_set_type;
393 l_inventory_item_id_3 varchar2_4000_set_type;
394 l_organization_id_3 varchar2_4000_set_type;
395 l_uom_code_3 varchar2_4000_set_type;
396 l_quantity_3 varchar2_4000_set_type;
397 l_budget_amount_3 varchar2_4000_set_type;
398 l_source_promotion_id_3 varchar2_4000_set_type;
399 l_offer_id_3 varchar2_4000_set_type;
400 l_interest_type_id_4 varchar2_4000_set_type;
401 l_primary_interest_code_id_4 varchar2_4000_set_type;
402 l_secondary_interest_code_id_4 varchar2_4000_set_type;
403 l_inventory_item_id_4 varchar2_4000_set_type;
404 l_organization_id_4 varchar2_4000_set_type;
405 l_uom_code_4 varchar2_4000_set_type;
406 l_quantity_4 varchar2_4000_set_type;
407 l_budget_amount_4 varchar2_4000_set_type;
408 l_source_promotion_id_4 varchar2_4000_set_type;
409 l_offer_id_4 varchar2_4000_set_type;
410 l_interest_type_id_5 varchar2_4000_set_type;
411 l_primary_interest_code_id_5 varchar2_4000_set_type;
412 l_secondary_interest_code_id_5 varchar2_4000_set_type;
413 l_inventory_item_id_5 varchar2_4000_set_type;
414 l_organization_id_5 varchar2_4000_set_type;
415 l_uom_code_5 varchar2_4000_set_type;
416 l_quantity_5 varchar2_4000_set_type;
417 l_budget_amount_5 varchar2_4000_set_type;
418 l_source_promotion_id_5 varchar2_4000_set_type;
419 l_offer_id_5 varchar2_4000_set_type;
420 l_orig_system_reference varchar2_4000_set_type;
421 l_orig_system_code varchar2_4000_set_type;
422 l_urgent_flag varchar2_4000_set_type;
423 l_accept_flag varchar2_4000_set_type;
424 l_assign_date varchar2_4000_set_type;
425 l_assign_sales_group_id varchar2_4000_set_type;
426 l_assign_to_person_id varchar2_4000_set_type;
427 l_assign_to_salesforce_id varchar2_4000_set_type;
428 l_auto_assignment_type varchar2_4000_set_type;
429 l_deleted_flag varchar2_4000_set_type;
430 l_import_flag varchar2_4000_set_type;
431 l_keep_flag varchar2_4000_set_type;
432 l_prm_assignment_type varchar2_4000_set_type;
433 l_qualified_flag varchar2_4000_set_type;
434 l_reject_reason_code varchar2_4000_set_type;
435 l_scorecard_id varchar2_4000_set_type;
436 l_primary_contact_flag varchar2_4000_set_type;
437 l_address_effective_date varchar2_4000_set_type;
438 l_address_lines_phonetic varchar2_4000_set_type;
439 l_address_style varchar2_4000_set_type;
440 l_content_source_type varchar2_4000_set_type;
441 l_loc_description varchar2_4000_set_type;
442 l_loc_hierarchy_id varchar2_4000_set_type;
443 l_fa_location_id varchar2_4000_set_type;
444 l_floor varchar2_4000_set_type;
445 l_house_number varchar2_4000_set_type;
446 l_language varchar2_4000_set_type;
447 l_location_directions varchar2_4000_set_type;
448 l_po_box_number varchar2_4000_set_type;
449 l_location_position varchar2_4000_set_type;
450 l_postal_plus4_code varchar2_4000_set_type;
451 l_sales_tax_geocode varchar2_4000_set_type;
452 l_sales_tax_inside_city_limits varchar2_4000_set_type;
453 l_short_description varchar2_4000_set_type;
454 l_street varchar2_4000_set_type;
455 l_street_number varchar2_4000_set_type;
456 l_street_suffix varchar2_4000_set_type;
457 l_suite varchar2_4000_set_type;
458 l_time_zone varchar2_4000_set_type;
459 l_loc_validated_flag varchar2_4000_set_type;
460 l_duns_number varchar2_4000_set_type;
461 l_group_type varchar2_4000_set_type;
462 l_gsa_indicator_flag varchar2_4000_set_type;
463 l_hq_branch_ind varchar2_4000_set_type;
464 l_jgzz_fiscal_code varchar2_4000_set_type;
465 l_known_as varchar2_4000_set_type;
469 l_known_as5 varchar2_4000_set_type;
466 l_known_as2 varchar2_4000_set_type;
467 l_known_as3 varchar2_4000_set_type;
468 l_known_as4 varchar2_4000_set_type;
470 l_language_name varchar2_4000_set_type;
471 l_last_ordered_date varchar2_4000_set_type;
472 l_mission_statement varchar2_4000_set_type;
473 l_organization_name_phonetic varchar2_4000_set_type;
474 l_party_number varchar2_4000_set_type;
475 l_person_first_name_phonetic varchar2_4000_set_type;
476 l_person_iden_type varchar2_4000_set_type;
477 l_person_identifier varchar2_4000_set_type;
478 l_person_last_name_phonetic varchar2_4000_set_type;
479 l_person_name_suffix varchar2_4000_set_type;
480 l_person_previous_last_name varchar2_4000_set_type;
481 l_party_reference_use_flag varchar2_4000_set_type;
482 l_sic_code_type varchar2_4000_set_type;
483 l_tax_name varchar2_4000_set_type;
484 l_total_num_of_orders varchar2_4000_set_type;
485 l_total_ordered_amount varchar2_4000_set_type;
486 l_parties_validated_flag varchar2_4000_set_type;
487 l_ps_uses_comments varchar2_4000_set_type;
488 l_primary_per_type varchar2_4000_set_type;
489 l_site_use_type varchar2_4000_set_type;
490 l_addressee varchar2_4000_set_type;
491 l_mailstop varchar2_4000_set_type;
492 l_party_site_name varchar2_4000_set_type;
493 l_party_site_number varchar2_4000_set_type;
494 l_org_cnt_comments varchar2_4000_set_type;
495 l_contact_number varchar2_4000_set_type;
496 l_decision_maker_flag varchar2_4000_set_type;
497 l_department varchar2_4000_set_type;
498 l_department_code varchar2_4000_set_type;
499 l_rank varchar2_4000_set_type;
500 l_promotion_id varchar2_4000_set_type;
501 l_role_level varchar2_4000_set_type;
502 primary_contact_per_role_type varchar2_4000_set_type;
503 l_cnt_pnt_content_source_type varchar2_4000_set_type;
504 l_phone_country_code varchar2_4000_set_type;
505 l_fax_country_code varchar2_4000_set_type;
506 l_phone_calling_calendar varchar2_4000_set_type;
507 l_cnt_pnt_time_zone varchar2_4000_set_type;
508 l_raw_phone_number varchar2_4000_set_type;
509 l_email_format varchar2_4000_set_type;
510 l_fax_extension varchar2_4000_set_type;
511 l_org_cnt_title varchar2_4000_set_type;
512 l_offer_id varchar2_4000_set_type;
513 l_request_id varchar2_4000_set_type;
514 l_program_application_id varchar2_4000_set_type;
515 l_program_id varchar2_4000_set_type;
516 l_program_update_date varchar2_4000_set_type;
517 l_load_error_message varchar2_4000_set_type;
518 l_phone_id varchar2_4000_set_type;
519 l_contact_party_id varchar2_4000_set_type;
520 l_security_group_id varchar2_4000_set_type;
521 l_net_worth varchar2_4000_set_type;
522 l_lead_number varchar2_4000_set_type;
523 l_prm_sales_lead_type varchar2_4000_set_type;
524 l_prm_exec_sponsor_flag varchar2_4000_set_type;
525 l_prm_prj_lead_in_place_flag varchar2_4000_set_type;
526 l_incumbent_partner_party_id varchar2_4000_set_type;
527 lincumbent_partner_resource_id varchar2_4000_set_type;
528 l_prm_ind_classification_code varchar2_4000_set_type;
529 l_party_type varchar2_4000_set_type;
530 l_party_id varchar2_4000_set_type;
531 l_party_site_id varchar2_4000_set_type;
532 l_location_id varchar2_4000_set_type;
533 l_rel_party_id varchar2_4000_set_type;
534 l_sales_lead_id varchar2_4000_set_type;
535 l_customer_key varchar2_4000_set_type;
536 l_address_key varchar2_4000_set_type;
537 l_contact_key varchar2_4000_set_type;
538 l_new_party_flag varchar2_4000_set_type;
539 l_new_loc_flag varchar2_4000_set_type;
540 l_new_ps_flag varchar2_4000_set_type;
541 l_new_rel_flag varchar2_4000_set_type;
542 l_new_con_flag varchar2_4000_set_type;
543 l_lead_rank_id varchar2_4000_set_type;
544 l_marketing_score varchar2_4000_set_type;
545 l_PERSON_INITIALS varchar2_4000_set_type;
546 l_LEAD_DATE varchar2_4000_set_type;
547
548 l_CATEGORY_ID_1 varchar2_4000_set_type; --NUMBER
549 l_CATEGORY_ID_2 varchar2_4000_set_type; --NUMBER
550 l_CATEGORY_ID_3 varchar2_4000_set_type; --NUMBER
551 l_CATEGORY_ID_4 varchar2_4000_set_type; --NUMBER
552 l_CATEGORY_ID_5 varchar2_4000_set_type; --NUMBER
553 l_SALES_METHODOLOGY_ID varchar2_4000_set_type; --NUMBER
554 l_DUNS_NUMBER_C varchar2_4000_set_type; --VARCHAR2(30)
555 --l_SOURCE_PRIMARY_REFERENCE varchar2_4000_set_type; --VARCHAR2(30)
556 l_SOURCE_SECONDARY_REFERENCE varchar2_4000_set_type; --VARCHAR2(30)
557 l_NOTE_TYPE varchar2_4000_set_type; --VARCHAR2(30)
558
559 l_batch_id NUMBER;
560
561 CURSOR c_get_batch_id (p_list_header_id NUMBER)
562 IS SELECT batch_id
563 FROM ams_imp_list_headers_all
564 WHERE import_list_header_id = p_list_header_id;
565
566 CURSOR c_lead_recs (p_list_header_id NUMBER)
567 IS SELECT import_source_line_id, import_list_header_id,source_system,
568 lead_note, promotion_code,customer_name, sic_code, analysis_fy, customer_category_code,
569 fiscal_yearend_month, num_of_employees,potential_revenue_curr_fy,
570 potential_revenue_next_fy,customer_rank,tax_reference,
571 year_established,addr_do_not_mail_flag,url,cont_do_not_mail_flag,
575 area_code,extension,middle_initial,job_title_code,mail_stop,
572 country,address1,address2,address3,address4,city,postal_code,
573 state,province,county,email_address,sex_code,salutation,last_name,
574 title,first_name,job_title,phone_number,phone_status,phone_type,
576 fax_number,fax_area_code,do_not_email_flag,do_not_fax_flag,do_not_phone_flag,
577 contact_role_code,channel_code,budget_amount,budget_status_code,currency_code,
578 decision_timeframe_code,close_reason,parent_project,description,vehicle_response_code,
579 interest_type_id_1,primary_interest_code_id_1,secondary_interest_code_id_1,
580 inventory_item_id_1,organization_id_1,uom_code_1,quantity_1,budget_amount_1,
581 source_promotion_id_1,offer_id_1,interest_type_id_2,primary_interest_code_id_2,
582 secondary_interest_code_id_2,inventory_item_id_2,organization_id_2,
583 uom_code_2,quantity_2,budget_amount_2,source_promotion_id_2,offer_id_2,interest_type_id_3,
584 primary_interest_code_id_3,secondary_interest_code_id_3,inventory_item_id_3,
585 organization_id_3,uom_code_3,quantity_3,budget_amount_3,source_promotion_id_3,
586 offer_id_3,interest_type_id_4,primary_interest_code_id_4,secondary_interest_code_id_4,
587 inventory_item_id_4,organization_id_4,uom_code_4,quantity_4,budget_amount_4,
588 source_promotion_id_4,offer_id_4,interest_type_id_5,primary_interest_code_id_5,
589 secondary_interest_code_id_5,inventory_item_id_5,organization_id_5,uom_code_5,
590 quantity_5,budget_amount_5,source_promotion_id_5,offer_id_5,orig_system_reference,
591 orig_system_code,urgent_flag,accept_flag,assign_date,assign_sales_group_id,
592 assign_to_person_id,assign_to_salesforce_id,auto_assignment_type,deleted_flag,
593 import_flag,keep_flag,prm_assignment_type,qualified_flag,reject_reason_code,
594 scorecard_id,primary_contact_flag,address_effective_date,address_lines_phonetic,
595 address_style,content_source_type,loc_description,loc_hierarchy_id,fa_location_id,
596 FLOOR,house_number,LANGUAGE,location_directions,po_box_number,
597 --location_position,
598 postal_plus4_code,sales_tax_geocode,sales_tax_inside_city_limits,short_description,
599 street,street_number,street_suffix,suite,time_zone,loc_validated_flag,duns_number,
600 group_type,gsa_indicator_flag,hq_branch_ind,jgzz_fiscal_code,known_as,known_as2,
601 known_as3,known_as4,known_as5,language_name,last_ordered_date,mission_statement,
602 organization_name_phonetic,party_number,person_first_name_phonetic,person_iden_type,
603 person_identifier,person_last_name_phonetic,person_name_suffix,person_previous_last_name,
604 party_reference_use_flag,sic_code_type,tax_name,total_num_of_orders,total_ordered_amount,
605 parties_validated_flag,ps_uses_comments,primary_per_type,site_use_type,addressee,
606 mailstop,party_site_name,party_site_number,org_cnt_comments,contact_number,
607 decision_maker_flag,department,department_code,rank,promotion_id,role_level,
608 primary_contact_per_role_type,cnt_pnt_content_source_type,phone_country_code,
609 fax_country_code,phone_calling_calendar,cnt_pnt_time_zone,
610 raw_phone_number,email_format,fax_extension,org_cnt_title,offer_id,request_id,
611 program_application_id,program_id,program_update_date,load_error_message,
612 phone_id,contact_party_id,security_group_id,net_worth,lead_number,prm_sales_lead_type,
613 prm_exec_sponsor_flag,prm_prj_lead_in_place_flag,incumbent_partner_party_id,
614 incumbent_partner_resource_id,prm_ind_classification_code,party_type,
615 party_id,party_site_id,location_id,rel_party_id,sales_lead_id,customer_key,
616 address_key,contact_key,new_party_flag,new_loc_flag,new_ps_flag,new_rel_flag,
617 new_con_flag,lead_rank_id, marketing_score, PERSON_INITIALS, LEAD_DATE,
618 CATEGORY_ID_1, CATEGORY_ID_2, CATEGORY_ID_3, CATEGORY_ID_4,
619 CATEGORY_ID_5, SALES_METHODOLOGY_ID, DUNS_NUMBER_C,
620 SOURCE_SECONDARY_REFERENCE,
621 NOTE_TYPE
622 FROM ams_lead_mapping_v
623 WHERE import_list_header_id = p_list_header_id
624 AND load_status IN ('ACTIVE', 'RELOAD');
625 BEGIN
626 --
627 -- Initialize API return status to success.
628 --
629 x_return_status := FND_API.G_RET_STS_SUCCESS;
630
631 IF p_import_list_header_id IS NULL THEN
632 RAISE FND_API.G_EXC_ERROR;
633 END IF;
634
635 OPEN c_get_batch_id (p_import_list_header_id);
636 FETCH c_get_batch_id INTO l_batch_id;
637 CLOSE c_get_batch_id;
638
639 OPEN c_lead_recs (p_import_list_header_id);
640 LOOP
641 FETCH c_lead_recs BULK COLLECT INTO
642 l_import_source_line_id,
643 l_import_list_header_id,
644 l_source_system,
645 l_lead_note,
646 l_promotion_code,
647 l_customer_name,
648 l_sic_code,
649 l_analysis_fy,
650 l_customer_category_code,
651 l_fiscal_yearend_month,
652 l_num_of_employees,
653 l_potential_revenue_curr_fy,
654 l_potential_revenue_next_fy,
655 l_customer_rank,
656 l_tax_reference,
657 l_year_established,
658 l_addr_do_not_mail_flag,
659 l_url,
660 l_cont_do_not_mail_flag,
661 l_country,
662 l_address1,
663 l_address2,
664 l_address3,
665 l_address4,
666 l_city,
667 l_postal_code,
668 l_state,
669 l_province,
670 l_county,
671 l_email_address,
672 l_sex_code,
673 l_salutation,
674 l_last_name,
675 l_title,
676 l_first_name,
680 l_phone_type,
677 l_job_title,
678 l_phone_number,
679 l_phone_status,
681 l_area_code,
682 l_extension,
683 l_middle_initial,
684 l_job_title_code,
685 l_mail_stop,
686 l_fax_number,
687 l_fax_area_code,
688 l_do_not_email_flag,
689 l_do_not_fax_flag,
690 l_do_not_phone_flag,
691 l_contact_role_code,
692 l_channel_code,
693 l_budget_amount,
694 l_budget_status_code,
695 l_currency_code,
696 l_decision_timeframe_code,
697 l_close_reason,
698 l_parent_project,
699 l_description,
700 l_vehicle_response_code,
701 l_interest_type_id_1,
702 l_primary_interest_code_id_1,
703 l_secondary_interest_code_id_1,
704 l_inventory_item_id_1,
705 l_organization_id_1,
706 l_uom_code_1,
707 l_quantity_1,
708 l_budget_amount_1,
709 l_source_promotion_id_1,
710 l_offer_id_1,
711 l_interest_type_id_2,
712 l_primary_interest_code_id_2,
713 l_secondary_interest_code_id_2 ,
714 l_inventory_item_id_2,
715 l_organization_id_2,
716 l_uom_code_2,
717 l_quantity_2,
718 l_budget_amount_2,
719 l_source_promotion_id_2,
720 l_offer_id_2,
721 l_interest_type_id_3,
722 l_primary_interest_code_id_3,
723 l_secondary_interest_code_id_3 ,
724 l_inventory_item_id_3,
725 l_organization_id_3,
726 l_uom_code_3,
727 l_quantity_3,
728 l_budget_amount_3,
729 l_source_promotion_id_3,
730 l_offer_id_3,
731 l_interest_type_id_4,
732 l_primary_interest_code_id_4,
733 l_secondary_interest_code_id_4 ,
734 l_inventory_item_id_4,
735 l_organization_id_4,
736 l_uom_code_4,
737 l_quantity_4,
738 l_budget_amount_4,
739 l_source_promotion_id_4,
740 l_offer_id_4,
741 l_interest_type_id_5,
742 l_primary_interest_code_id_5,
743 l_secondary_interest_code_id_5 ,
744 l_inventory_item_id_5,
745 l_organization_id_5,
746 l_uom_code_5,
747 l_quantity_5,
748 l_budget_amount_5,
749 l_source_promotion_id_5,
750 l_offer_id_5,
751 l_orig_system_reference,
752 l_orig_system_code,
753 l_urgent_flag,
754 l_accept_flag,
755 l_assign_date,
756 l_assign_sales_group_id,
757 l_assign_to_person_id,
758 l_assign_to_salesforce_id,
759 l_auto_assignment_type,
760 l_deleted_flag,
761 l_import_flag,
762 l_keep_flag,
763 l_prm_assignment_type,
764 l_qualified_flag,
765 l_reject_reason_code,
766 l_scorecard_id,
767 l_primary_contact_flag,
768 l_address_effective_date,
769 l_address_lines_phonetic,
770 l_address_style,
771 l_content_source_type,
772 l_loc_description,
773 l_loc_hierarchy_id,
774 l_fa_location_id,
775 l_floor,
776 l_house_number,
777 l_language,
778 l_location_directions,
779 l_po_box_number,
780 --l_location_position,
781 l_postal_plus4_code,
782 l_sales_tax_geocode,
783 l_sales_tax_inside_city_limits ,
784 l_short_description,
785 l_street,
786 l_street_number,
787 l_street_suffix,
788 l_suite,
789 l_time_zone,
790 l_loc_validated_flag,
791 l_duns_number,
792 l_group_type,
793 l_gsa_indicator_flag,
794 l_hq_branch_ind,
795 l_jgzz_fiscal_code,
796 l_known_as,
797 l_known_as2,
798 l_known_as3,
799 l_known_as4,
800 l_known_as5,
801 l_language_name,
802 l_last_ordered_date,
803 l_mission_statement,
804 l_organization_name_phonetic,
805 l_party_number,
806 l_person_first_name_phonetic,
807 l_person_iden_type,
808 l_person_identifier,
809 l_person_last_name_phonetic,
810 l_person_name_suffix,
811 l_person_previous_last_name,
812 l_party_reference_use_flag,
813 l_sic_code_type,
814 l_tax_name,
815 l_total_num_of_orders,
816 l_total_ordered_amount,
817 l_parties_validated_flag,
818 l_ps_uses_comments,
819 l_primary_per_type,
820 l_site_use_type,
821 l_addressee,
822 l_mailstop,
823 l_party_site_name,
824 l_party_site_number,
825 l_org_cnt_comments,
826 l_contact_number,
827 l_decision_maker_flag,
828 l_department,
829 l_department_code,
830 l_rank,
831 l_promotion_id,
832 l_role_level,
833 primary_contact_per_role_type ,
834 l_cnt_pnt_content_source_type,
835 l_phone_country_code,
836 l_fax_country_code,
837 l_phone_calling_calendar,
838 l_cnt_pnt_time_zone,
839 l_raw_phone_number,
840 l_email_format,
841 l_fax_extension,
842 l_org_cnt_title,
843 l_offer_id,
844 l_request_id,
845 l_program_application_id,
846 l_program_id,
847 l_program_update_date,
848 l_load_error_message,
849 l_phone_id,
850 l_contact_party_id,
854 l_prm_sales_lead_type,
851 l_security_group_id,
852 l_net_worth,
853 l_lead_number,
855 l_prm_exec_sponsor_flag,
856 l_prm_prj_lead_in_place_flag,
857 l_incumbent_partner_party_id,
858 lincumbent_partner_resource_id ,
859 l_prm_ind_classification_code,
860 l_party_type,
861 l_party_id,
862 l_party_site_id,
863 l_location_id,
864 l_rel_party_id,
865 l_sales_lead_id,
866 l_customer_key,
867 l_address_key,
868 l_contact_key,
869 l_new_party_flag,
870 l_new_loc_flag,
871 l_new_ps_flag,
872 l_new_rel_flag,
873 l_new_con_flag,
874 l_lead_rank_id,
875 l_marketing_score,
876 l_PERSON_INITIALS,
877 l_LEAD_DATE,
878 l_CATEGORY_ID_1,
879 l_CATEGORY_ID_2,
880 l_CATEGORY_ID_3,
881 l_CATEGORY_ID_4,
882 l_CATEGORY_ID_5,
883 l_SALES_METHODOLOGY_ID,
884 l_DUNS_NUMBER_C,
885 l_SOURCE_SECONDARY_REFERENCE,
886 l_NOTE_TYPE
887 LIMIT L_MAX_ROW_COUNT;
888
889 FOR i IN 1 .. l_import_source_line_id.COUNT
890 LOOP
891 SELECT as_import_interface_s.NEXTVAL INTO l_interface_id(i)
892 FROM dual;
893 END LOOP;
894
895 FORALL i IN 1 .. l_import_source_line_id.COUNT
896 INSERT INTO as_import_interface
897 (import_interface_id,
898 last_update_date,
899 last_updated_by,
900 creation_date,
901 created_by,
902 last_update_login,
903 imp_list_header_number,
904 source_system,
905 lead_note,
906 promotion_code,
907 customer_name,
908 sic_code,
909 analysis_fy,
910 customer_category_code,
911 fiscal_yearend_month,
912 num_of_employees,
913 potential_revenue_curr_fy,
914 potential_revenue_next_fy,
915 customer_rank,
916 tax_reference,
917 year_established,
918 addr_do_not_mail_flag,
919 url,
920 cont_do_not_mail_flag,
921 country,
922 address1,
923 address2,
924 address3,
925 address4,
926 city,
927 postal_code,
928 state,
929 province,
930 county,
931 email_address,
932 sex_code,
933 salutation,
934 last_name,
935 title,
936 first_name,
937 job_title,
938 phone_number,
939 phone_status,
940 phone_type,
941 area_code,
942 extension,
943 middle_initial,
944 job_title_code,
945 mail_stop,
946 fax_number,
947 fax_area_code,
948 do_not_email_flag,
949 do_not_fax_flag,
950 do_not_phone_flag,
951 contact_role_code,
952 channel_code,
953 budget_amount,
954 budget_status_code,
955 currency_code,
956 decision_timeframe_code,
957 close_reason,
958 parent_project,
959 description,
960 vehicle_response_code,
961 interest_type_id_1,
962 primary_interest_code_id_1,
963 secondary_interest_code_id_1,
964 inventory_item_id_1,
965 organization_id_1,
966 uom_code_1,
967 quantity_1,
968 budget_amount_1,
969 source_promotion_id_1,
970 offer_id_1,
971 interest_type_id_2,
972 primary_interest_code_id_2,
973 secondary_interest_code_id_2,
974 inventory_item_id_2,
975 organization_id_2,
976 uom_code_2,
977 quantity_2,
978 budget_amount_2,
979 source_promotion_id_2,
980 offer_id_2,
981 interest_type_id_3,
982 primary_interest_code_id_3,
983 secondary_interest_code_id_3,
984 inventory_item_id_3,
985 organization_id_3,
986 uom_code_3,
987 quantity_3,
988 budget_amount_3,
989 source_promotion_id_3,
990 offer_id_3,
991 interest_type_id_4,
992 primary_interest_code_id_4,
993 secondary_interest_code_id_4,
994 inventory_item_id_4,
995 organization_id_4,
996 uom_code_4,
997 quantity_4,
998 budget_amount_4,
999 source_promotion_id_4,
1000 offer_id_4,
1001 interest_type_id_5,
1002 primary_interest_code_id_5,
1003 secondary_interest_code_id_5,
1004 inventory_item_id_5,
1005 organization_id_5,uom_code_5,
1006 quantity_5,
1007 budget_amount_5,
1008 source_promotion_id_5,
1009 offer_id_5,
1010 orig_system_reference,
1011 orig_system_code,
1012 urgent_flag,
1013 accept_flag,
1014 assign_date, -- date
1015 assign_sales_group_id,
1016 assign_to_person_id,
1017 assign_to_salesforce_id,
1018 auto_assignment_type,
1019 deleted_flag,
1020 import_flag,
1021 keep_flag,
1022 prm_assignment_type,
1023 qualified_flag,
1027 address_effective_date, -- date
1024 reject_reason_code,
1025 scorecard_id,
1026 primary_contact_flag,
1028 address_lines_phonetic,
1029 address_style,
1030 content_source_type,
1031 loc_description,
1032 loc_hierarchy_id,
1033 fa_location_id,
1034 FLOOR,
1035 house_number,
1036 LANGUAGE,
1037 location_directions,
1038 po_box_number,
1039 --location_position,
1040 postal_plus4_code,
1041 sales_tax_geocode,
1042 sales_tax_inside_city_limits,
1043 short_description,
1044 street,
1045 street_number,
1046 street_suffix,
1047 suite,
1048 time_zone,
1049 loc_validated_flag,
1050 duns_number,
1051 group_type,
1052 gsa_indicator_flag,
1053 hq_branch_ind,
1054 jgzz_fiscal_code,
1055 known_as,
1056 known_as2,
1057 known_as3,
1058 known_as4,
1059 known_as5,
1060 language_name,
1061 last_ordered_date, -- date
1062 mission_statement,
1063 organization_name_phonetic,
1064 party_number,
1065 person_first_name_phonetic,
1066 person_iden_type,
1067 person_identifier,
1068 person_last_name_phonetic,
1069 person_name_suffix,
1070 person_previous_last_name,
1071 party_reference_use_flag,
1072 sic_code_type,
1073 tax_name,
1074 total_num_of_orders,
1075 total_ordered_amount,
1076 parties_validated_flag,
1077 ps_uses_comments,
1078 primary_per_type,
1079 site_use_type,
1080 addressee,
1081 mailstop,
1082 party_site_name,
1083 party_site_number,
1084 org_cnt_comments,
1085 contact_number,
1086 decision_maker_flag,
1087 department,
1088 department_code,
1089 rank,
1090 promotion_id,
1091 role_level,
1092 primary_contact_per_role_type,
1093 cnt_pnt_content_source_type,
1094 phone_country_code,
1095 fax_country_code,
1096 phone_calling_calendar,
1097 cnt_pnt_time_zone,
1098 raw_phone_number,
1099 email_format,
1100 fax_extension,
1101 org_cnt_title,
1102 offer_id,
1103 request_id,
1104 program_application_id,
1105 program_id,
1106 program_update_date, -- date
1107 load_error_message,
1108 phone_id,
1109 contact_party_id,
1110 security_group_id,
1111 net_worth,
1112 lead_number,
1113 prm_sales_lead_type,
1114 prm_exec_sponsor_flag,
1115 prm_prj_lead_in_place_flag,
1116 incumbent_partner_party_id,
1117 incumbent_partner_resource_id,
1118 prm_ind_classification_code,
1119 party_type,
1120 party_id,
1121 party_site_id,
1122 location_id,
1123 rel_party_id,
1124 sales_lead_id,
1125 customer_key,
1126 address_key,
1127 contact_key,
1128 new_party_flag,
1129 new_loc_flag,
1130 new_ps_flag,
1131 new_rel_flag,
1132 new_con_flag,
1133 lead_rank_id,
1134 marketing_score,
1135 PERSON_INITIALS,
1136 LEAD_DATE,
1137 load_type,
1138 load_date,
1139 load_status,
1140 status_code,
1141 batch_id,
1142 SOURCE_PRIMARY_REFERENCE,
1143 CATEGORY_ID_1,
1144 CATEGORY_ID_2,
1145 CATEGORY_ID_3,
1146 CATEGORY_ID_4,
1147 CATEGORY_ID_5,
1148 SALES_METHODOLOGY_ID,
1149 DUNS_NUMBER_C,
1150 SOURCE_SECONDARY_REFERENCE,
1151 NOTE_TYPE)
1152
1153 VALUES
1154 (l_interface_id(i),
1155 l_current_date,
1156 l_user_id,
1157 l_current_date,
1158 l_user_id,
1159 l_login_id,
1160 l_import_source_line_id(i),
1161 LTRIM(RTRIM(l_source_system(i))),
1162 LTRIM(RTRIM(l_lead_note(i))),
1163 LTRIM(RTRIM(l_promotion_code(i))),
1164 LTRIM(RTRIM(l_customer_name(i))),
1165 LTRIM(RTRIM(l_sic_code(i))),
1166 LTRIM(RTRIM(l_analysis_fy(i))),
1167 LTRIM(RTRIM(l_customer_category_code(i))),
1168 LTRIM(RTRIM(l_fiscal_yearend_month(i))),
1169 LTRIM(RTRIM(l_num_of_employees(i))),
1170 LTRIM(RTRIM(l_potential_revenue_curr_fy(i))),
1171 LTRIM(RTRIM(l_potential_revenue_next_fy(i))),
1172 LTRIM(RTRIM(l_customer_rank(i))),
1173 LTRIM(RTRIM(l_tax_reference(i))),
1174 LTRIM(RTRIM(l_year_established(i))),
1175 LTRIM(RTRIM(l_addr_do_not_mail_flag(i))),
1176 LTRIM(RTRIM(l_url(i))),
1177 LTRIM(RTRIM(l_cont_do_not_mail_flag(i))),
1178 LTRIM(RTRIM(l_country(i))),
1179 LTRIM(RTRIM(l_address1(i))),
1180 LTRIM(RTRIM(l_address2(i))),
1181 LTRIM(RTRIM(l_address3(i))),
1182 LTRIM(RTRIM(l_address4(i))),
1183 LTRIM(RTRIM(l_city(i))),
1184 LTRIM(RTRIM(l_postal_code(i))),
1185 LTRIM(RTRIM(l_state(i))),
1186 LTRIM(RTRIM(l_province(i))),
1187 LTRIM(RTRIM(l_county(i))),
1191 LTRIM(RTRIM(l_last_name(i))),
1188 LTRIM(RTRIM(l_email_address(i))),
1189 LTRIM(RTRIM(l_sex_code(i))),
1190 LTRIM(RTRIM(l_salutation(i))),
1192 LTRIM(RTRIM(l_title(i))),
1193 LTRIM(RTRIM(l_first_name(i))),
1194 LTRIM(RTRIM(l_job_title(i))),
1195 LTRIM(RTRIM(l_phone_number(i))),
1196 LTRIM(RTRIM(l_phone_status(i))),
1197 LTRIM(RTRIM(l_phone_type(i))),
1198 LTRIM(RTRIM(l_area_code(i))),
1199 LTRIM(RTRIM(l_extension(i))),
1200 LTRIM(RTRIM(l_middle_initial(i))),
1201 LTRIM(RTRIM(l_job_title_code(i))),
1202 LTRIM(RTRIM(l_mail_stop(i))),
1203 LTRIM(RTRIM(l_fax_number(i))),
1204 LTRIM(RTRIM(l_fax_area_code(i))),
1205 LTRIM(RTRIM(l_do_not_email_flag(i))),
1206 LTRIM(RTRIM(l_do_not_fax_flag(i))),
1207 LTRIM(RTRIM(l_do_not_phone_flag(i))),
1208 LTRIM(RTRIM(l_contact_role_code(i))),
1209 LTRIM(RTRIM(l_channel_code(i))),
1210 LTRIM(RTRIM(l_budget_amount(i))),
1211 LTRIM(RTRIM(l_budget_status_code(i))),
1212 LTRIM(RTRIM(l_currency_code(i))),
1213 LTRIM(RTRIM(l_decision_timeframe_code(i))),
1214 LTRIM(RTRIM(l_close_reason(i))),
1215 LTRIM(RTRIM(l_parent_project(i))),
1216 LTRIM(RTRIM(l_description(i))),
1217 LTRIM(RTRIM(l_vehicle_response_code(i))),
1218 LTRIM(RTRIM(l_interest_type_id_1(i))),
1219 LTRIM(RTRIM(l_primary_interest_code_id_1(i))),
1220 LTRIM(RTRIM(l_secondary_interest_code_id_1(i))),
1221 LTRIM(RTRIM(l_inventory_item_id_1(i))),
1222 LTRIM(RTRIM(l_organization_id_1(i))),
1223 LTRIM(RTRIM(l_uom_code_1(i))),
1224 LTRIM(RTRIM(l_quantity_1(i))),
1225 LTRIM(RTRIM(l_budget_amount_1(i))),
1226 LTRIM(RTRIM(l_source_promotion_id_1(i))),
1227 LTRIM(RTRIM(l_offer_id_1(i))),
1228 LTRIM(RTRIM(l_interest_type_id_2(i))),
1229 LTRIM(RTRIM(l_primary_interest_code_id_2(i))),
1230 LTRIM(RTRIM(l_secondary_interest_code_id_2(i))),
1231 LTRIM(RTRIM(l_inventory_item_id_2(i))),
1232 LTRIM(RTRIM(l_organization_id_2(i))),
1233 LTRIM(RTRIM(l_uom_code_2(i))),
1234 LTRIM(RTRIM(l_quantity_2(i))),
1235 LTRIM(RTRIM(l_budget_amount_2(i))),
1236 LTRIM(RTRIM(l_source_promotion_id_2(i))),
1237 LTRIM(RTRIM(l_offer_id_2(i))),
1238 LTRIM(RTRIM(l_interest_type_id_3(i))),
1239 LTRIM(RTRIM(l_primary_interest_code_id_3(i))),
1240 LTRIM(RTRIM(l_secondary_interest_code_id_3(i))),
1241 LTRIM(RTRIM(l_inventory_item_id_3(i))),
1242 LTRIM(RTRIM(l_organization_id_3(i))),
1243 LTRIM(RTRIM(l_uom_code_3(i))),
1244 LTRIM(RTRIM(l_quantity_3(i))),
1245 LTRIM(RTRIM(l_budget_amount_3(i))),
1246 LTRIM(RTRIM(l_source_promotion_id_3(i))),
1247 LTRIM(RTRIM(l_offer_id_3(i))),
1248 LTRIM(RTRIM(l_interest_type_id_4(i))),
1249 LTRIM(RTRIM(l_primary_interest_code_id_4(i))),
1250 LTRIM(RTRIM(l_secondary_interest_code_id_4(i))),
1251 LTRIM(RTRIM(l_inventory_item_id_4(i))),
1252 LTRIM(RTRIM(l_organization_id_4(i))),
1253 LTRIM(RTRIM(l_uom_code_4(i))),
1254 LTRIM(RTRIM(l_quantity_4(i))),
1255 LTRIM(RTRIM(l_budget_amount_4(i))),
1256 LTRIM(RTRIM(l_source_promotion_id_4(i))),
1257 LTRIM(RTRIM(l_offer_id_4(i))),
1258 LTRIM(RTRIM(l_interest_type_id_5(i))),
1259 LTRIM(RTRIM(l_primary_interest_code_id_5(i))),
1260 LTRIM(RTRIM(l_secondary_interest_code_id_5(i))),
1261 LTRIM(RTRIM(l_inventory_item_id_5(i))),
1262 LTRIM(RTRIM(l_organization_id_5(i))),
1263 LTRIM(RTRIM(l_uom_code_5(i))),
1264 LTRIM(RTRIM(l_quantity_5(i))),
1265 LTRIM(RTRIM(l_budget_amount_5(i))),
1266 LTRIM(RTRIM(l_source_promotion_id_5(i))),
1267 LTRIM(RTRIM(l_offer_id_5(i))),
1268 LTRIM(RTRIM(l_orig_system_reference(i))),
1269 LTRIM(RTRIM(l_orig_system_code(i))),
1270 LTRIM(RTRIM(l_urgent_flag(i))),
1271 LTRIM(RTRIM(l_accept_flag(i))),
1272 TO_DATE(l_assign_date(i), 'MM/DD/YYYY'),
1273 LTRIM(RTRIM(l_assign_sales_group_id(i))),
1274 LTRIM(RTRIM(l_assign_to_person_id(i))),
1275 LTRIM(RTRIM(l_assign_to_salesforce_id(i))),
1276 LTRIM(RTRIM(l_auto_assignment_type(i))),
1277 LTRIM(RTRIM(l_deleted_flag(i))),
1278 LTRIM(RTRIM(l_import_flag(i))),
1279 LTRIM(RTRIM(l_keep_flag(i))),
1280 LTRIM(RTRIM(l_prm_assignment_type(i))),
1281 LTRIM(RTRIM(l_qualified_flag(i))),
1282 LTRIM(RTRIM(l_reject_reason_code(i))),
1283 LTRIM(RTRIM(l_scorecard_id(i))),
1284 LTRIM(RTRIM(l_primary_contact_flag(i))),
1285 TO_DATE(l_address_effective_date(i), 'MM/DD/YYYY'),
1286 LTRIM(RTRIM(l_address_lines_phonetic(i))),
1287 LTRIM(RTRIM(l_address_style(i))),
1288 LTRIM(RTRIM(l_content_source_type(i))),
1289 LTRIM(RTRIM(l_loc_description(i))),
1290 LTRIM(RTRIM(l_loc_hierarchy_id(i))),
1291 LTRIM(RTRIM(l_fa_location_id(i))),
1292 LTRIM(RTRIM(l_floor(i))),
1293 LTRIM(RTRIM(l_house_number(i))),
1294 LTRIM(RTRIM(l_language(i))),
1295 LTRIM(RTRIM(l_location_directions(i))),
1296 LTRIM(RTRIM(l_po_box_number(i))),
1297 --location_position(i))),
1298 LTRIM(RTRIM(l_postal_plus4_code(i))),
1299 LTRIM(RTRIM(l_sales_tax_geocode(i))),
1300 LTRIM(RTRIM(l_sales_tax_inside_city_limits(i))),
1301 LTRIM(RTRIM(l_short_description(i))),
1302 LTRIM(RTRIM(l_street(i))),
1303 LTRIM(RTRIM(l_street_number(i))),
1307 LTRIM(RTRIM(l_loc_validated_flag(i))),
1304 LTRIM(RTRIM(l_street_suffix(i))),
1305 LTRIM(RTRIM(l_suite(i))),
1306 LTRIM(RTRIM(l_time_zone(i))),
1308 LTRIM(RTRIM(l_duns_number(i))),
1309 LTRIM(RTRIM(l_group_type(i))),
1310 LTRIM(RTRIM(l_gsa_indicator_flag(i))),
1311 LTRIM(RTRIM(l_hq_branch_ind(i))),
1312 LTRIM(RTRIM(l_jgzz_fiscal_code(i))),
1313 LTRIM(RTRIM(l_known_as(i))),
1314 LTRIM(RTRIM(l_known_as2(i))),
1315 LTRIM(RTRIM(l_known_as3(i))),
1316 LTRIM(RTRIM(l_known_as4(i))),
1317 LTRIM(RTRIM(l_known_as5(i))),
1318 LTRIM(RTRIM(l_language_name(i))),
1319 TO_DATE(l_last_ordered_date(i), 'MM/DD/YYYY'),
1320 LTRIM(RTRIM(l_mission_statement(i))),
1321 LTRIM(RTRIM(l_organization_name_phonetic(i))),
1322 LTRIM(RTRIM(l_party_number(i))),
1323 LTRIM(RTRIM(l_person_first_name_phonetic(i))),
1324 LTRIM(RTRIM(l_person_iden_type(i))),
1325 LTRIM(RTRIM(l_person_identifier(i))),
1326 LTRIM(RTRIM(l_person_last_name_phonetic(i))),
1327 LTRIM(RTRIM(l_person_name_suffix(i))),
1328 LTRIM(RTRIM(l_person_previous_last_name(i))),
1329 LTRIM(RTRIM(l_party_reference_use_flag(i))),
1330 LTRIM(RTRIM(l_sic_code_type(i))),
1331 LTRIM(RTRIM(l_tax_name(i))),
1332 LTRIM(RTRIM(l_total_num_of_orders(i))),
1333 LTRIM(RTRIM(l_total_ordered_amount(i))),
1334 LTRIM(RTRIM(l_parties_validated_flag(i))),
1335 LTRIM(RTRIM(l_ps_uses_comments(i))),
1336 LTRIM(RTRIM(l_primary_per_type(i))),
1337 LTRIM(RTRIM(l_site_use_type(i))),
1338 LTRIM(RTRIM(l_addressee(i))),
1339 LTRIM(RTRIM(l_mailstop(i))),
1340 LTRIM(RTRIM(l_party_site_name(i))),
1341 LTRIM(RTRIM(l_party_site_number(i))),
1342 LTRIM(RTRIM(l_org_cnt_comments(i))),
1343 LTRIM(RTRIM(l_contact_number(i))),
1344 LTRIM(RTRIM(l_decision_maker_flag(i))),
1345 LTRIM(RTRIM(l_department(i))),
1346 LTRIM(RTRIM(l_department_code(i))),
1347 LTRIM(RTRIM(l_rank(i))),
1348 LTRIM(RTRIM(l_promotion_id(i))),
1349 LTRIM(RTRIM(l_role_level(i))),
1350 LTRIM(RTRIM(primary_contact_per_role_type(i))),
1351 LTRIM(RTRIM(l_cnt_pnt_content_source_type(i))),
1352 LTRIM(RTRIM(l_phone_country_code(i))),
1353 LTRIM(RTRIM(l_fax_country_code(i))),
1354 LTRIM(RTRIM(l_phone_calling_calendar(i))),
1355 LTRIM(RTRIM(l_cnt_pnt_time_zone(i))),
1356 LTRIM(RTRIM(l_raw_phone_number(i))),
1357 LTRIM(RTRIM(l_email_format(i))),
1358 LTRIM(RTRIM(l_fax_extension(i))),
1359 LTRIM(RTRIM(l_org_cnt_title(i))),
1360 LTRIM(RTRIM(l_offer_id(i))),
1361 LTRIM(RTRIM(l_request_id(i))),
1362 LTRIM(RTRIM(l_program_application_id(i))),
1363 LTRIM(RTRIM(l_program_id(i))),
1364 TO_DATE(l_program_update_date(i), 'MM/DD/YYYY'),
1365 LTRIM(RTRIM(l_load_error_message(i))),
1366 LTRIM(RTRIM(l_phone_id(i))),
1367 LTRIM(RTRIM(l_contact_party_id(i))),
1368 LTRIM(RTRIM(l_security_group_id(i))),
1369 LTRIM(RTRIM(l_net_worth(i))),
1370 LTRIM(RTRIM(l_lead_number(i))),
1371 LTRIM(RTRIM(l_prm_sales_lead_type(i))),
1372 LTRIM(RTRIM(l_prm_exec_sponsor_flag(i))),
1373 LTRIM(RTRIM(l_prm_prj_lead_in_place_flag(i))),
1374 LTRIM(RTRIM(l_incumbent_partner_party_id(i))),
1375 LTRIM(RTRIM(lincumbent_partner_resource_id(i))),
1376 LTRIM(RTRIM(l_prm_ind_classification_code(i))),
1377 LTRIM(RTRIM(l_party_type(i))),
1378 LTRIM(RTRIM(l_party_id(i))),
1379 LTRIM(RTRIM(l_party_site_id(i))),
1380 LTRIM(RTRIM(l_location_id(i))),
1381 LTRIM(RTRIM(l_rel_party_id(i))),
1382 LTRIM(RTRIM(l_sales_lead_id(i))),
1383 LTRIM(RTRIM(l_customer_key(i))),
1384 LTRIM(RTRIM(l_address_key(i))),
1385 LTRIM(RTRIM(l_contact_key(i))),
1386 LTRIM(RTRIM(l_new_party_flag(i))),
1387 LTRIM(RTRIM(l_new_loc_flag(i))),
1388 LTRIM(RTRIM(l_new_ps_flag(i))),
1389 LTRIM(RTRIM(l_new_rel_flag(i))),
1390 LTRIM(RTRIM(l_new_con_flag(i))),
1391 LTRIM(RTRIM(l_lead_rank_id(i))),
1392 LTRIM(RTRIM(l_marketing_score(i))),
1393 LTRIM(RTRIM(l_PERSON_INITIALS(i))),
1394 LTRIM(RTRIM(l_LEAD_DATE(i))),
1395 l_LOAD_TYPE,
1396 L_LOAD_DATE,
1397 L_LOAD_STATUS,
1398 L_STATUS_CODE,
1399 l_batch_id,
1400 l_import_source_line_id(i),
1401 LTRIM(RTRIM(l_CATEGORY_ID_1(i))),
1402 LTRIM(RTRIM(l_CATEGORY_ID_2(i))),
1403 LTRIM(RTRIM(l_CATEGORY_ID_3(i))),
1404 LTRIM(RTRIM(l_CATEGORY_ID_4(i))),
1405 LTRIM(RTRIM(l_CATEGORY_ID_5(i))),
1406 LTRIM(RTRIM(l_SALES_METHODOLOGY_ID(i))),
1407 LTRIM(RTRIM(l_DUNS_NUMBER_C(i))),
1408 LTRIM(RTRIM(l_SOURCE_SECONDARY_REFERENCE(i))),
1409 LTRIM(RTRIM(l_NOTE_TYPE(i))));
1410 EXIT WHEN c_lead_recs%NOTFOUND;
1411 END LOOP;
1412 CLOSE c_lead_recs;
1413
1414 COMMIT;
1415
1416 EXCEPTION
1417 WHEN FND_API.G_EXC_ERROR THEN
1418 AMS_Utility_PVT.Create_Log (
1419 x_return_status => x_return_status,
1420 p_arc_log_used_by => G_ARC_IMPORT_HEADER,
1421 p_log_used_by_id => p_import_list_header_id,
1422 p_msg_data => 'Expected error in ' || L_FULL_NAME,
1423 p_msg_type => 'DEBUG'
1424 );
1428 CLOSE c_lead_recs;
1425 x_return_status := FND_API.G_RET_STS_ERROR;
1426 WHEN OTHERS THEN
1427 IF c_lead_recs%ISOPEN THEN
1429 END IF;
1430 AMS_Utility_PVT.Create_Log (
1431 x_return_status => x_return_status,
1432 p_arc_log_used_by => G_ARC_IMPORT_HEADER,
1433 p_log_used_by_id => p_import_list_header_id,
1434 p_msg_data => 'Error in ' || L_FULL_NAME || ': '|| SQLERRM||' '||SQLCODE,
1435 p_msg_type => 'DEBUG'
1436 );
1437 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1438 END Load_Lead_Data_To_Interface;
1439
1440 -- NAME
1441 -- Insert_Lead_Data
1442 --
1443 -- PURPOSE
1444 -- The procedure insert a collection of data into the "ams_imp_source_lines"
1445 -- The "p_data" contains all data needed to be inserted, assuming all data
1446 -- types are "VARCHAR2". For example, if the data to be inserted are the
1447 -- followings:
1448 --
1449 -- Row Number 1 2 3 4
1450 -- Column1 Frank Smith Scott Marry
1451 -- Column2 Amos Anderson Baber Beier
1452 -- Column3 75039 77002 23060 03062
1453 --
1454 -- The data is stored in the "p_data" as: "Frank", "Smith", "Scott", "Marry",
1455 -- "Amos", "Anderson", "Baber", "Beier", "75039", "77002", "23060", "03062".
1456 --
1457 -- NOTES
1458 --
1459 -- HISTORY
1460 -- 05/13/2001 huili Created.
1461 -- 06/04/2001 huili Populated the "loaded_no_of_rows" and "loaded_date"
1462 -- columns in the "ams_imp_list_headers_all" table.
1463 --
1464 --06/08/2001 huili Changed logic for setting up the "status_code" of
1465 -- the "ams_imp_list_headers_all" table.
1466 PROCEDURE Insert_Lead_Data (
1467 p_api_version IN NUMBER,
1468 p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE,
1469 p_commit IN VARCHAR2 := FND_API.G_TRUE,
1470 p_import_list_header_id IN NUMBER,
1471 p_data IN char_data_set_type_w,
1472 p_error_rows IN num_data_set_type_w,
1473 p_row_count IN NUMBER,
1474 x_return_status OUT NOCOPY VARCHAR2,
1475 x_msg_count OUT NOCOPY NUMBER,
1476 x_msg_data OUT NOCOPY VARCHAR2
1477 )
1478 IS
1479 --
1480 -- Standard API information constants.
1481 --
1482 L_API_VERSION CONSTANT NUMBER := 1.0;
1483 L_API_NAME CONSTANT VARCHAR2(30) := 'Insert_Lead_Data';
1484 L_FULL_NAME CONSTANT VARCHAR2(60) := G_PKG_NAME ||'.'|| L_API_NAME;
1485 l_return_status VARCHAR(1);
1486
1487 l_columns char_data_set_type_w;
1488 l_index NUMBER := 1;
1489 l_temp_vol_name VARCHAR2(30);
1490
1491 l_str_col_names char_data_set_type_w;
1492 l_num_col_names char_data_set_type_w;
1493 l_str_data char_data_set_type_w;
1494 l_num_data num_data_set_type_w;
1495 l_row_count NUMBER;
1496 l_field_table_name VARCHAR2(30) := 'AMS_LEAD_MAPPING_V';
1497 l_batch_id NUMBER;
1498
1499 --06/16/2001 huili
1500 l_dup_flag VARCHAR2(1);
1501
1502 --06/18/2001 huili
1503 l_status_id NUMBER;
1504
1505 l_msg_data VARCHAR2(2000);
1506
1507 --06/18/2001 huili
1508 CURSOR c_get_status_id IS
1509 SELECT user_status_id
1510 FROM ams_user_statuses_vl
1511 WHERE UPPER(system_status_type) = 'AMS_IMPORT_STATUS'
1512 AND UPPER(system_status_code) = 'STAGED'
1513 AND default_flag = 'Y';
1514
1515 CURSOR c_list_fields (l_import_list_header_id NUMBER) IS
1516 SELECT A.field_column_name, B.batch_id
1517 FROM ams_list_src_fields A, ams_imp_list_headers_all b
1518 WHERE b.IMPORT_LIST_HEADER_ID = l_import_list_header_id
1519 AND b.LIST_SOURCE_TYPE_ID = A.LIST_SOURCE_TYPE_ID
1520 ORDER BY LIST_SOURCE_FIELD_ID;
1521
1522 --06/16/2001 huili added
1523 CURSOR c_dup_checking (l_import_list_header_id NUMBER) IS
1524 SELECT dedupe_flag
1525 FROM ams_imp_list_headers_all
1526 WHERE import_list_header_id = l_import_list_header_id;
1527
1528 BEGIN
1529
1530 --
1531 -- Initialize message list if p_init_msg_list is set to TRUE.
1532 --
1533 IF FND_API.To_Boolean (p_init_msg_list) THEN
1534 FND_MSG_PUB.Initialize;
1535 END IF;
1536
1537 --AMS_Utility_PVT.Create_Log (
1538 -- x_return_status => l_return_status,
1539 -- p_arc_log_used_by => G_ARC_IMPORT_HEADER,
1540 -- p_log_used_by_id => p_import_list_header_id,
1541 -- p_msg_data => 'Start client lead importing.',
1542 -- p_msg_type => 'DEBUG'
1543 --);
1544
1545 --
1546 -- Standard check for API version compatibility.
1547 --
1548 IF NOT FND_API.Compatible_API_Call (L_API_VERSION,
1549 p_api_version,
1550 L_API_NAME,
1551 G_PKG_NAME)
1552 THEN
1553 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1554 END IF;
1555
1556 --
1557 -- Initialize API return status to success.
1558 --
1562 LOOP
1559 x_return_status := FND_API.G_RET_STS_SUCCESS;
1560
1561 OPEN c_list_fields(p_import_list_header_id);
1563 FETCH c_list_fields INTO l_temp_vol_name, l_batch_id;
1564 EXIT WHEN c_list_fields%NOTFOUND;
1565
1566 l_columns(l_index) := l_temp_vol_name;
1567 l_index := l_index + 1;
1568 END LOOP;
1569 CLOSE c_list_fields;
1570
1571 l_row_count := p_data.COUNT / l_columns.COUNT;
1572
1573 l_str_col_names(1) := 'IMPORT_SUCCESSFUL_FLAG';
1574
1575 l_str_data(1) := 'N';
1576
1577 l_num_col_names(1) := 'IMPORT_LIST_HEADER_ID';
1578 l_num_data(1) := p_import_list_header_id;
1579 l_num_col_names(2) := 'OBJECT_VERSION_NUMBER';
1580 l_num_data(2) := 1;
1581
1582 l_num_col_names(3) := 'BATCH_ID';
1583 l_num_data(3) := l_batch_id;
1584
1585 Insert_Data (
1586 p_api_version => 1.0,
1587 p_table_name => l_field_table_name,
1588 p_prim_key_name => 'IMPORT_SOURCE_LINE_ID',
1589 p_seq_name => 'AMS_IMP_SOURCE_LINES_S',
1590 p_str_col_names => l_str_col_names,
1591 p_num_col_names => l_num_col_names,
1592 p_str_data => l_str_data,
1593 p_num_data => l_num_data,
1594 p_col_names => l_columns,
1595 p_data => p_data,
1596 p_error_rows => p_error_rows,
1597 p_row_count => l_row_count,
1598 x_return_status => x_return_status,
1599 x_msg_count => x_msg_count,
1600 x_msg_data => l_msg_data);
1601
1602 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
1603 AMS_Utility_PVT.Create_Log (
1604 x_return_status => l_return_status,
1605 p_arc_log_used_by => G_ARC_IMPORT_HEADER,
1606 p_log_used_by_id => p_import_list_header_id,
1607 p_msg_data => l_msg_data,
1608 p_msg_type => 'DEBUG'
1609 );
1610 RAISE FND_API.G_EXC_ERROR;
1611 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1612 AMS_Utility_PVT.Create_Log (
1613 x_return_status => l_return_status,
1614 p_arc_log_used_by => G_ARC_IMPORT_HEADER,
1615 p_log_used_by_id => p_import_list_header_id,
1616 p_msg_data => l_msg_data,
1617 p_msg_type => 'DEBUG'
1618 );
1619 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1620 END IF;
1621
1622 l_status_id := NULL;
1623 OPEN c_get_status_id;
1624 FETCH c_get_status_id INTO l_status_id;
1625 CLOSE c_get_status_id;
1626
1627 --huili 06/19/2001 added NULL checking for l_status_id
1628 IF l_status_id IS NOT NULL THEN
1629 UPDATE ams_imp_list_headers_all
1630 SET status_code = 'STAGED',
1631 --loaded_no_of_rows = p_row_count,
1632 loaded_date = SYSDATE, user_status_id = l_status_id
1633 WHERE import_list_header_id = p_import_list_header_id;
1634 ELSE
1635 UPDATE ams_imp_list_headers_all
1636 SET status_code = 'STAGED',
1637 --loaded_no_of_rows = p_row_count,
1638 loaded_date = SYSDATE
1639 WHERE import_list_header_id = p_import_list_header_id;
1640 END IF;
1641 -- end change
1642 /*
1643 -- 06/16/2001 huili added
1644 l_dup_flag := NULL;
1645 OPEN c_dup_checking (p_import_list_header_id);
1646 FETCH c_dup_checking INTO l_dup_flag;
1647 CLOSE c_dup_checking;
1648
1649 IF l_dup_flag IS NOT NULL AND UPPER(l_dup_flag) = 'Y' THEN
1650 AMS_ListImport_PVT.dedup_check(
1651 p_import_list_header_id => p_import_list_header_id);
1652 END IF;
1653 -- 06/16/2001
1654 */
1655 --
1656 -- Standard check for commit request.
1657 --
1658 IF FND_API.To_Boolean(p_commit) THEN
1659 COMMIT WORK;
1660 END IF;
1661
1662 --AMS_Utility_PVT.Create_Log (
1663 -- x_return_status => l_return_status,
1664 -- p_arc_log_used_by => G_ARC_IMPORT_HEADER,
1665 -- p_log_used_by_id => p_import_list_header_id,
1666 -- p_msg_data => 'Client lead:' || l_time,
1667 -- p_msg_type => 'DEBUG'
1668 --);
1669
1670 --
1671 -- Standard API to get message count, and if 1,
1672 -- set the message data OUT variable.
1673 --
1674 FND_MSG_PUB.Count_And_Get (
1675 p_count => x_msg_count,
1676 p_data => x_msg_data,
1677 p_encoded => FND_API.G_FALSE
1678 );
1679 IF (AMS_DEBUG_HIGH_ON) THEN
1680
1681 AMS_Utility_PVT.debug_message(l_full_name ||': end');
1682 END IF;
1683 EXCEPTION
1684 WHEN FND_API.G_EXC_ERROR THEN
1685 x_return_status := FND_API.G_RET_STS_ERROR;
1686 FND_MSG_PUB.Count_And_Get (
1687 p_count => x_msg_count,
1688 p_data => x_msg_data
1689 );
1690 RAISE FND_API.G_EXC_ERROR;
1691
1692 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1693 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1694 FND_MSG_PUB.Count_And_Get (
1695 p_count => x_msg_count,
1696 p_data => x_msg_data
1697 );
1698 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
1702 AMS_Utility_PVT.Create_Log (
1699 WHEN OTHERS THEN
1700 FND_MESSAGE.set_name('AMS', 'API_DEBUG_MESSAGE');
1701 FND_MESSAGE.Set_Token('ROW','Error in client lead import program ' || SQLERRM||' '||SQLCODE);
1703 x_return_status => l_return_status,
1704 p_arc_log_used_by => G_ARC_IMPORT_HEADER,
1705 p_log_used_by_id => p_import_list_header_id,
1706 p_msg_data => FND_MESSAGE.get,
1707 p_msg_type => 'DEBUG'
1708 );
1709 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1710 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1711 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME, L_API_NAME);
1712 END IF;
1713 FND_MSG_PUB.Count_And_Get (
1714 p_count => x_msg_count,
1715 p_data => x_msg_data
1716 );
1717 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1718 END Insert_Lead_Data;
1719
1720
1721 -- NAME
1722 -- Insert_List_Data
1723 --
1724 -- PURPOSE
1725 -- The procedure insert a collection of data into the "ams_imp_source_lines"
1726 -- The "p_data" contains all data needed to be inserted, assuming all data
1727 -- types are "VARCHAR2". For example, if the data to be inserted are the
1728 -- followings:
1729 --
1730 -- Row Number 1 2 3 4
1731 -- Column1 Frank Smith Scott Marry
1732 -- Column2 Amos Anderson Baber Beier
1733 -- Column3 75039 77002 23060 03062
1734 --
1735 -- The data is stored in the "p_data" as: "Frank", "Smith", "Scott", "Marry",
1736 -- "Amos", "Anderson", "Baber", "Beier", "75039", "77002", "23060", "03062".
1737 --
1738 -- NOTES
1739 --
1740 -- HISTORY
1741 -- 04/16/2001 huili Created.
1742 -- 04/26/2001 huili Added table name to the cursor.
1743 -- 05/10/2001 huili Update "ams_imp_list_headers_all" table.
1744 -- 06/18/2001 huili Also update status_code for the
1745 -- "ams_imp_list_headers_all" table
1746 --
1747 PROCEDURE Insert_List_Data (
1748 p_api_version IN NUMBER,
1749 p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE,
1750 p_commit IN VARCHAR2 := FND_API.G_TRUE,
1751 p_import_list_header_id IN NUMBER,
1752 p_data IN char_data_set_type_w,
1753 p_row_count IN NUMBER,
1754 p_error_rows IN num_data_set_type_w,
1755 x_return_status OUT NOCOPY VARCHAR2,
1756 x_msg_count OUT NOCOPY NUMBER,
1757 x_msg_data OUT NOCOPY VARCHAR2
1758 )
1759 IS
1760 --
1761 -- Standard API information constants.
1762 --
1763 L_API_VERSION CONSTANT NUMBER := 1.0;
1764 L_API_NAME CONSTANT VARCHAR2(30) := 'Insert_List_Data';
1765 L_FULL_NAME CONSTANT VARCHAR2(60) := G_PKG_NAME ||'.'|| L_API_NAME;
1766 l_return_status VARCHAR(1);
1767
1768 l_columns char_data_set_type_w;
1769 l_index NUMBER := 1;
1770 l_temp_vol_name VARCHAR2(30);
1771
1772 l_str_col_names char_data_set_type_w;
1773 l_num_col_names char_data_set_type_w;
1774 l_str_data char_data_set_type_w;
1775 l_num_data num_data_set_type_w;
1776 l_row_count NUMBER;
1777 l_field_table_name VARCHAR2(30);
1778 l_dup_flag VARCHAR2(1);
1779 l_batch_id NUMBER;
1780
1781 --06/18/2001 huili
1782 l_status_id NUMBER;
1783
1784 l_msg_data VARCHAR2 (2000);
1785
1786 --06/18/2001 huili
1787 CURSOR c_get_status_id IS
1788 SELECT user_status_id
1789 FROM ams_user_statuses_vl
1790 WHERE UPPER(system_status_type) = 'AMS_IMPORT_STATUS'
1791 AND UPPER(system_status_code) = 'STAGED'
1792 AND default_flag = 'Y';
1793
1794 CURSOR c_list_fields (l_import_list_header_id NUMBER) IS
1795 SELECT A.field_column_name, A.FIELD_TABLE_NAME, B.BATCH_ID
1796 FROM ams_list_src_fields A, ams_imp_list_headers_all b
1797 WHERE b.IMPORT_LIST_HEADER_ID = l_import_list_header_id
1798 AND b.LIST_SOURCE_TYPE_ID = A.LIST_SOURCE_TYPE_ID
1799 ORDER BY LIST_SOURCE_FIELD_ID;
1800
1801 --06/16/2001 huili added
1802 CURSOR c_dup_checking (l_import_list_header_id NUMBER) IS
1803 SELECT dedupe_flag
1804 FROM ams_imp_list_headers_all
1805 WHERE import_list_header_id = l_import_list_header_id;
1806
1807 l_data_count NUMBER := p_data.COUNT;
1808 BEGIN
1809
1810
1811 --AMS_Utility_PVT.Create_Log (
1812 -- x_return_status => l_return_status,
1813 -- p_arc_log_used_by => G_ARC_IMPORT_HEADER,
1814 -- p_log_used_by_id => p_import_list_header_id,
1815 -- p_msg_data => 'Starting client list importing...',
1816 -- p_msg_type => 'DEBUG'
1817 --);
1818
1819 --
1820 -- Initialize message list if p_init_msg_list is set to TRUE.
1821 --
1822 IF FND_API.To_Boolean (p_init_msg_list) THEN
1823 FND_MSG_PUB.Initialize;
1824 END IF;
1825
1826 --
1827 -- Standard check for API version compatibility.
1828 --
1829 --IF NOT FND_API.Compatible_API_Call (L_API_VERSION,
1830 -- p_api_version,
1831 -- L_API_NAME,
1832 -- G_PKG_NAME)
1833 --THEN
1837 -- x_return_status => l_return_status,
1834 -- FND_MESSAGE.set_name('AMS', 'API_DEBUG_MESSAGE');
1835 -- FND_MESSAGE.Set_Token('ROW','API is not compatible for ' || G_PKG_NAME || '.'|| L_API_NAME);
1836 -- AMS_Utility_PVT.Create_Log (
1838 -- p_arc_log_used_by => G_ARC_IMPORT_HEADER,
1839 -- p_log_used_by_id => p_import_list_header_id,
1840 -- p_msg_data => FND_MESSAGE.get,
1841 -- p_msg_type => 'DEBUG'
1842 -- );
1843 -- RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1844 -- END IF;
1845
1846 --
1847 -- Initialize API return status to success.
1848 --
1849 x_return_status := FND_API.G_RET_STS_SUCCESS;
1850
1851 OPEN c_list_fields(p_import_list_header_id);
1852 LOOP
1853 FETCH c_list_fields INTO l_temp_vol_name, l_field_table_name, l_batch_id;
1854 EXIT WHEN c_list_fields%NOTFOUND;
1855
1856 l_columns(l_index) := l_temp_vol_name;
1857 l_index := l_index + 1;
1858 END LOOP;
1859 CLOSE c_list_fields;
1860
1861 l_row_count := p_data.COUNT / l_columns.COUNT;
1862
1863 l_str_col_names(1) := 'IMPORT_SUCCESSFUL_FLAG';
1864 l_num_col_names(1) := 'IMPORT_LIST_HEADER_ID';
1865 l_num_col_names(2) := 'OBJECT_VERSION_NUMBER';
1866 l_num_col_names(3) := 'BATCH_ID';
1867 l_str_data(1) := 'N';
1868 l_num_data(1) := p_import_list_header_id;
1869 l_num_data(2) := 1;
1870 l_num_data(3) := l_batch_id;
1871
1872 Insert_Data (
1873 p_api_version => 1.0,
1874 p_table_name => l_field_table_name,
1875 p_prim_key_name => 'IMPORT_SOURCE_LINE_ID',
1876 p_seq_name => 'AMS_IMP_SOURCE_LINES_S',
1877 p_str_col_names => l_str_col_names,
1878 p_num_col_names => l_num_col_names,
1879 p_str_data => l_str_data,
1880 p_num_data => l_num_data,
1881 p_col_names => l_columns,
1882 p_data => p_data,
1883 p_error_rows => p_error_rows,
1884 p_row_count => l_row_count,
1885 x_return_status => x_return_status,
1886 x_msg_count => x_msg_count,
1887 x_msg_data => l_msg_data);
1888
1889 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
1890 AMS_Utility_PVT.Create_Log (
1891 x_return_status => l_return_status,
1892 p_arc_log_used_by => G_ARC_IMPORT_HEADER,
1893 p_log_used_by_id => p_import_list_header_id,
1894 p_msg_data => l_msg_data,
1895 p_msg_type => 'DEBUG'
1896 );
1897 RAISE FND_API.G_EXC_ERROR;
1898 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1899 AMS_Utility_PVT.Create_Log (
1900 x_return_status => l_return_status,
1901 p_arc_log_used_by => G_ARC_IMPORT_HEADER,
1902 p_log_used_by_id => p_import_list_header_id,
1903 p_msg_data => l_msg_data,
1904 p_msg_type => 'DEBUG'
1905 );
1906 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1907 END IF;
1908
1909 l_status_id := NULL;
1910 OPEN c_get_status_id;
1911 FETCH c_get_status_id INTO l_status_id;
1912 CLOSE c_get_status_id;
1913
1914 --huili added NULL checking for l_status_id
1915 IF l_status_id IS NOT NULL THEN
1916 UPDATE ams_imp_list_headers_all
1917 SET status_code = 'STAGED',
1918 --loaded_no_of_rows = p_row_count,
1919 loaded_date = SYSDATE, user_status_id = l_status_id
1920 WHERE import_list_header_id = p_import_list_header_id;
1921 ELSE
1922 UPDATE ams_imp_list_headers_all
1923 SET status_code = 'STAGED',
1924 --loaded_no_of_rows = p_row_count,
1925 loaded_date = SYSDATE
1926 WHERE import_list_header_id = p_import_list_header_id;
1927 END IF;
1928 -- end change
1929 /*
1930 -- 06/16/2001 huili added
1931 l_dup_flag := NULL;
1932 OPEN c_dup_checking (p_import_list_header_id);
1933 FETCH c_dup_checking INTO l_dup_flag;
1934 CLOSE c_dup_checking;
1935
1936 IF l_dup_flag IS NOT NULL AND UPPER(l_dup_flag) = 'Y' THEN
1937 AMS_ListImport_PVT.dedup_check(
1938 p_import_list_header_id => p_import_list_header_id);
1939 END IF;
1940 -- 06/16/2001
1941 */
1942 --
1943 -- Standard check for commit request.
1944 --
1945 IF FND_API.To_Boolean(p_commit) THEN
1946 COMMIT WORK;
1947 END IF;
1948
1949 --AMS_Utility_PVT.Create_Log (
1950 -- x_return_status => l_return_status,
1951 -- p_arc_log_used_by => G_ARC_IMPORT_HEADER,
1952 -- p_log_used_by_id => p_import_list_header_id,
1953 -- p_msg_data => 'Client list importing is finished without error...',
1954 -- p_msg_type => 'DEBUG'
1955 --);
1956 --
1957 -- Standard API to get message count, and if 1,
1958 -- set the message data OUT variable.
1959 --
1960 FND_MSG_PUB.Count_And_Get (
1961 p_count => x_msg_count,
1962 p_data => x_msg_data,
1963 p_encoded => FND_API.G_FALSE
1964 );
1965 IF (AMS_DEBUG_HIGH_ON) THEN
1966
1967 AMS_Utility_PVT.debug_message(l_full_name ||': end');
1968 END IF;
1969 EXCEPTION
1970 WHEN FND_API.G_EXC_ERROR THEN
1971 x_return_status := FND_API.G_RET_STS_ERROR;
1972 FND_MSG_PUB.Count_And_Get (
1973 p_count => x_msg_count,
1974 p_data => x_msg_data
1978 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1975 );
1976 RAISE FND_API.G_EXC_ERROR;
1977
1979 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1980 FND_MSG_PUB.Count_And_Get (
1981 p_count => x_msg_count,
1982 p_data => x_msg_data
1983 );
1984 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
1985 WHEN OTHERS THEN
1986 FND_MESSAGE.set_name('AMS', 'API_DEBUG_MESSAGE');
1987 FND_MESSAGE.Set_Token('ROW','Error in client list import program ' || SQLERRM||' '||SQLCODE);
1988 AMS_Utility_PVT.Create_Log (
1989 x_return_status => l_return_status,
1990 p_arc_log_used_by => G_ARC_IMPORT_HEADER,
1991 p_log_used_by_id => p_import_list_header_id,
1992 p_msg_data => FND_MESSAGE.get,
1993 p_msg_type => 'DEBUG'
1994 );
1995 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1996 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1997 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME, L_API_NAME);
1998 END IF;
1999 FND_MSG_PUB.Count_And_Get (
2000 p_count => x_msg_count,
2001 p_data => x_msg_data
2002 );
2003 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2004 END Insert_List_Data;
2005
2006 PROCEDURE Write_Message (
2007 x_return_status OUT NOCOPY VARCHAR2,
2008 p_log_used_by_id IN VARCHAR2,
2009 p_msg_data IN VARCHAR2,
2010 p_msg_type IN VARCHAR2
2011 )
2012 IS
2013
2014 BEGIN
2015
2016 AMS_Utility_PVT.Create_Log (
2017 x_return_status => x_return_status,
2018 p_arc_log_used_by => G_ARC_IMPORT_HEADER,
2019 p_log_used_by_id => p_log_used_by_id,
2020 p_msg_data => p_msg_data,
2021 p_msg_type => p_msg_type
2022 );
2023 EXCEPTION WHEN OTHERS THEN
2024 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2025 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2026 END;
2027
2028
2029 -- NAME
2030 -- Insert_Data
2031 --
2032 -- PURPOSE
2033 -- The procedure insert a collection of data into a table whose name is
2034 -- specified by the "p_table_name" field. It uses native dynamic SQL to
2035 -- bulk insert records using binding tables.
2036 --
2037 -- The "p_col_names" field contains a collection of the column names.
2038 -- The "p_data" contains all data needed to be inserted, assuming all data
2039 -- types are "VARCHAR2". For example, if the data to be inserted are the
2040 -- followings:
2041 --
2042 -- Row Number 1 2 3 4
2043 -- Column1 Frank Smith Scott Marry
2044 -- Column2 Amos Anderson Baber Beier
2045 -- Column3 75039 77002 23060 03062
2046 --
2047 -- The data is stored in the "p_data" as: "Frank", "Smith", "Scott", "Marry",
2048 -- "Amos", "Anderson", "Baber", "Beier", "75039", "77002", "23060", "03062".
2049 -- Both "p_col_names" and "p_data" are consecutive.
2050 -- The data stored in the "p_prim_key_name" field is the column name of the
2051 -- primary key column. The "p_seq_name" is the name of sequence used to
2052 -- generate the primary keys for all the rows. For example, it must be
2053 -- "AMS.AMS_IMP_SOURCE_LINES_S" instead of "AMS_IMP_SOURCE_LINES_S".
2054 -- The "p_str_col_names" and
2055 -- the "p_num_col_names" contain other "VARCHAR2" field and "NUMBER" field
2056 -- names respectively, while the "p_str_data" and "p_num_data" are
2057 -- corresponding data. The other flag fields are used to indicate whether
2058 -- we need these fields or not. The "p_row_count" field is redundant since
2059 -- we do not want to invoke the "COUNT" on "p_data" since this table is
2060 -- supposed to be huge.
2061 --
2062 -- NOTES
2063 --
2064 -- HISTORY
2065 -- 04/12/2001 huili Created.
2066 -- 05/13/2001 huili Modified to handle different owners for sequence
2067 --
2068 PROCEDURE Insert_Data (
2069
2070 p_api_version IN NUMBER,
2071 p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE,
2072 p_commit IN VARCHAR2 := FND_API.G_FALSE,
2073
2074 p_table_name IN VARCHAR2,
2075 p_prim_key_name IN VARCHAR2,
2076 p_seq_name IN VARCHAR2,
2077
2078 p_str_col_names IN char_data_set_type_w,
2079 p_num_col_names IN char_data_set_type_w,
2080 p_str_data IN char_data_set_type_w,
2081 p_num_data IN num_data_set_type_w,
2082 p_obj_version_num IN NUMBER := NULL, -- if NULL, do not insert this column
2083 p_last_update_date_flag IN VARCHAR2 := FND_API.G_TRUE,-- need this column?
2084 p_last_update_by_flag IN VARCHAR2 := FND_API.G_TRUE,-- need this column?
2085 p_creation_date_flag IN VARCHAR2 := FND_API.G_TRUE,-- need this column?
2086 p_created_by_flag IN VARCHAR2 := FND_API.G_TRUE,-- need this column?
2087 p_ls_update_log_flag IN VARCHAR2 := FND_API.G_TRUE,-- need this column?
2088
2089 p_col_names IN char_data_set_type_w,
2090 p_data IN char_data_set_type_w,
2091 p_error_rows IN num_data_set_type_w,
2092 p_row_count IN NUMBER,
2093
2094 x_return_status OUT NOCOPY VARCHAR2,
2095 x_msg_count OUT NOCOPY NUMBER,
2096 x_msg_data OUT NOCOPY VARCHAR2
2100 --
2097 )
2098 IS
2099
2101 -- Standard API information constants.
2102 --
2103 L_API_VERSION CONSTANT NUMBER := 1.0;
2104 L_API_NAME CONSTANT VARCHAR2(30) := 'Insert_Data';
2105 L_FULL_NAME CONSTANT VARCHAR2(60) := G_PKG_NAME ||'.'|| L_API_NAME;
2106
2107 L_PLACE_HOLDER_UNIT CONSTANT NUMBER := 8;
2108
2109 L_ACTIVE CONSTANT VARCHAR2(30) := 'ACTIVE';
2110 L_ERROR CONSTANT VARCHAR2(30) := 'ERROR';
2111
2112 l_value_clause_unit VARCHAR2(8000) := '';
2113 l_num_value_units NUMBER := 0;
2114 TYPE char_table IS TABLE OF VARCHAR2(8000);
2115 place_holder char_table := char_table('');
2116
2117 l_data_seq NUMBER := 1;
2118
2119 l_tab_name_clause1 VARCHAR2(8000) := 'INSERT INTO ';
2120 l_col_clause1 VARCHAR2(8000) := ' (';
2121 l_col_clause2 VARCHAR2(8000) := '';
2122 l_col_clause3 VARCHAR2(8000) := '';
2123
2124 l_value_clause VARCHAR2(8000) := ' VALUES (';
2125 --l_value_clause1 VARCHAR2(8000) := ',';
2126
2127 --l_value_data VARCHAR2(8000) := '';
2128
2129 l_date DATE;
2130 l_col_count NUMBER;
2131 l_orig_col_count NUMBER;
2132 l_start_index NUMBER := 1;
2133 l_act_col_count NUMBER; --actual col count for each initialization
2134
2135 l_start_seq_num NUMBER;
2136
2137 l_user_id NUMBER;
2138 l_login_id NUMBER;
2139
2140 l_seq_owner VARCHAR2 (30);
2141 l_seq_name VARCHAR2 (2000);
2142 l_dot_pos NUMBER;
2143 l_index NUMBER := 1;
2144 l_one_item VARCHAR2(2000);
2145
2146 l_row_seq NUMBER := 0;
2147 l_source_line_keys num_data_set_type_w;
2148
2149 l_load_status VARCHAR2(30) := L_ACTIVE;
2150
2151 l_list_imp_error_rec AMS_LIST_IMPORT_ERRORS%ROWTYPE;
2152 l_imp_err_id NUMBER;
2153
2154 l_varchar_col_num NUMBER := 0;
2155 l_num_col_num NUMBER := 0;
2156 l_other_col_num NUMBER := 0;
2157 l_total_col_count NUMBER := 0;
2158 l_comma VARCHAR2(1) := '';
2159 g_cursor INT DEFAULT dbms_sql.open_cursor;
2160 l_status integer;
2161
2162 BEGIN
2163
2164 --
2165 -- Initialize API return status to success.
2166 --
2167 x_return_status := FND_API.G_RET_STS_SUCCESS;
2168
2169 l_orig_col_count := p_col_names.COUNT;
2170
2171 IF l_orig_col_count < 1 THEN
2172 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2173 END IF;
2174
2175 l_date := SYSDATE;
2176 l_user_id := FND_GLOBAL.User_ID;
2177 l_login_id := FND_GLOBAL.Conc_Login_ID;
2178
2179 l_tab_name_clause1 := l_tab_name_clause1 || p_table_name;
2180
2181 l_dot_pos := INSTR (p_seq_name, '.');
2182 IF l_dot_pos > 0 THEN
2183 l_seq_name := SUBSTR(p_seq_name, l_dot_pos + 1);
2184 ELSE
2185 l_seq_name := p_seq_name;
2186 END IF;
2187
2188 l_seq_name := UPPER (l_seq_name);
2189
2190 SELECT SEQUENCE_OWNER INTO l_seq_owner
2191 FROM sys.ALL_SEQUENCES WHERE SEQUENCE_NAME = l_seq_name;
2192
2193 IF l_seq_owner IS NOT NULL
2194 AND LENGTH(l_seq_owner) > 0 THEN
2195 l_seq_name := l_seq_owner || '.' || l_seq_name;
2196 END IF;
2197
2198 --
2199 -- Generate the primary key table
2200 --
2201 FOR seq_index IN 1 .. p_row_count
2202 LOOP
2203 EXECUTE IMMEDIATE
2204 'SELECT ' || l_seq_name || '.NEXTVAL FROM DUAL' INTO l_start_seq_num;
2205 l_source_line_keys(seq_index) := l_start_seq_num;
2206 END LOOP;
2207
2208 l_col_clause1 := l_col_clause1 || p_prim_key_name || ',';
2209 l_col_clause1 := l_col_clause1
2210 || 'LAST_UPDATE_DATE,LAST_UPDATED_BY,CREATION_DATE,CREATED_BY,LAST_UPDATE_LOGIN,';
2211
2212 FOR i IN p_str_col_names.FIRST .. p_str_col_names.LAST
2213 LOOP
2214 l_col_clause1 := l_col_clause1 || p_str_col_names(i) || ',';
2215 END LOOP;
2216
2217 FOR i IN p_num_col_names.FIRST .. p_num_col_names.LAST
2218 LOOP
2219 l_col_clause1 := l_col_clause1 || p_num_col_names(i) || ',';
2220 END LOOP;
2221
2222 l_comma := '';
2223 WHILE l_index <= l_orig_col_count
2224 LOOP
2225 l_col_clause1 := l_col_clause1 || l_comma || p_col_names(l_index);
2226 l_index := l_index + 1;
2227 l_other_col_num := l_other_col_num + 1;
2228 l_comma := ',';
2229 END LOOP;
2230
2231 l_other_col_num := l_other_col_num + 1;
2232 l_col_clause1 := l_col_clause1 || ',' || 'LOAD_STATUS) ';
2233
2234 l_varchar_col_num := p_str_col_names.COUNT;
2235 l_num_col_num := p_num_col_names.COUNT;
2236
2237 l_comma := '';
2238 FOR i IN 1 .. 6
2239 LOOP
2240 l_value_clause := l_value_clause || l_comma || ':r' || i ;
2241 l_comma := ',';
2242 END LOOP;
2243
2244 FOR i IN 1 .. l_varchar_col_num
2245 LOOP
2246 l_value_clause := l_value_clause || l_comma || ':v' || i ;
2247 l_comma := ',';
2248 END LOOP;
2249
2250 FOR i IN 1 .. l_num_col_num
2251 LOOP
2252 l_value_clause := l_value_clause || l_comma || ':n' || i ;
2253 l_comma := ',';
2254 END LOOP;
2255
2256 FOR i IN 1 .. l_other_col_num
2257 LOOP
2258 l_value_clause := l_value_clause || l_comma || ':o' || i ;
2259 l_comma := ',';
2260 END LOOP;
2261
2262 l_value_clause := l_value_clause || ')';
2263
2267 FOR i IN 1 .. p_row_count
2264 dbms_sql.parse (g_cursor, l_tab_name_clause1 || l_col_clause1
2265 || l_value_clause, dbms_sql.native);
2266
2268
2269 LOOP
2270 BEGIN
2271 --IF p_error_rows(i) = G_ROW_PARSE_NO_ERROR THEN
2272 l_load_status := L_ACTIVE;
2273 --ELSE
2274 -- l_load_status := L_ERROR;
2275 --END IF;
2276
2277 --dbms_sql.parse (g_cursor, l_tab_name_clause1 || l_col_clause1
2278 -- || l_value_clause, dbms_sql.native);
2279
2280 --primary_key
2281 dbms_sql.bind_variable (g_cursor, ':r1', l_source_line_keys(i));
2282 --LAST_UPDATE_DATE
2283 dbms_sql.bind_variable (g_cursor, ':r2', l_date);
2284 --LAST_UPDATED_BY
2285 dbms_sql.bind_variable (g_cursor, ':r3', l_user_id);
2286 --CREATION_DATE
2287 dbms_sql.bind_variable (g_cursor, ':r4', l_date);
2288 --CREATED_BY
2289 dbms_sql.bind_variable (g_cursor, ':r5', l_user_id);
2290 --LAST_UPDATE_LOGIN
2291 dbms_sql.bind_variable (g_cursor, ':r6', l_login_id);
2292
2293 --string data
2294 FOR j IN 1 .. l_varchar_col_num
2295 LOOP
2296 dbms_sql.bind_variable (g_cursor, ':v' || j, p_str_data(j));
2297 END LOOP;
2298
2299 --num data
2300 FOR k IN 1 .. l_num_col_num
2301 LOOP
2302 dbms_sql.bind_variable (g_cursor, ':n' || k, p_num_data(k));
2303 END LOOP;
2304
2305 --other data
2306 FOR l IN 1 .. l_other_col_num -1
2307 LOOP
2308 dbms_sql.bind_variable (g_cursor, ':o' || l,
2309 RTRIM(LTRIM(p_data (i + (l - 1) * p_row_count))));
2310 END LOOP;
2311
2312 --status_code
2313 dbms_sql.bind_variable (g_cursor, ':o' || l_other_col_num,
2314 l_load_status);
2315
2316 l_status := dbms_sql.EXECUTE(g_cursor);
2317
2318 EXCEPTION
2319 WHEN OTHERS THEN
2320 dbms_sql.close_cursor (g_cursor);
2321 x_msg_data := 'Error at row ' || i || ' ' || SQLERRM;
2322 x_msg_data := x_msg_data || ' If you mapped the "party_id" , it should be a number.';
2323 RAISE FND_API.G_EXC_ERROR;
2324 END;
2325 END LOOP;
2326 dbms_sql.close_cursor (g_cursor);
2327
2328
2329 --FOR j IN p_error_rows.FIRST .. p_error_rows.LAST
2330 --LOOP
2331
2332 -- IF p_error_rows(j) <> G_ROW_PARSE_NO_ERROR THEN
2333
2334 -- l_list_imp_error_rec.IMPORT_LIST_HEADER_ID := p_num_data(1);
2335 -- l_list_imp_error_rec.IMPORT_SOURCE_LINE_ID := l_source_line_keys(j);
2336 -- l_list_imp_error_rec.IMPORT_TYPE := 'LEAD';
2337
2338 -- IF p_error_rows(j) = G_ROW_PARSE_TOO_LARGE THEN
2339 -- l_list_imp_error_rec.COL1 := G_ROW_PARSE_TOO_LARGE_MSG;
2340 -- ELSE
2341 -- l_list_imp_error_rec.COL1 := G_ROW_PARSE_OTHER_MSG;
2342 -- END IF;
2343
2344 -- l_list_imp_error_rec.ERROR_TYPE := 'E';
2345 -- Create_List_import_Error (
2346 -- p_api_version => 1.0,
2347 -- x_return_status => x_return_status,
2348 -- p_list_imp_error_rec => l_list_imp_error_rec,
2349 -- x_imp_err_id => l_imp_err_id);
2350 --
2351 -- END IF;
2352 --
2353 --END LOOP;
2354
2355 --
2356 -- Standard check for commit request.
2357 --
2358 IF FND_API.To_Boolean (p_commit) THEN
2359 COMMIT WORK;
2360 END IF;
2361
2362 --
2363 -- Standard API to get message count, and if 1,
2364 -- set the message data OUT variable.
2365 --
2366 FND_MSG_PUB.Count_And_Get (
2367 p_count => x_msg_count,
2368 p_data => x_msg_data,
2369 p_encoded => FND_API.G_FALSE
2370 );
2371
2372 EXCEPTION
2373 WHEN FND_API.G_EXC_ERROR THEN
2374 x_return_status := FND_API.G_RET_STS_ERROR;
2375 --x_msg_data := 'Error happens AT ROW ' || l_row_seq
2376 -- || '. The error message IS ' || SQLERRM;
2377 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2378 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2379 x_msg_data := 'Error happens AT ROW ' || l_row_seq
2380 || '. The error message IS ' || SQLERRM;
2381
2382 WHEN OTHERS THEN
2383 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2384 x_msg_data := 'Unexpected error ' || SQLERRM;
2385 END Insert_Data;
2386
2387
2388 -- NAME
2389 -- Append_More_Data
2390 --
2391 -- PURPOSE
2392 -- NOTES
2393 --
2394 -- HISTORY
2395 -- 04/12/2001 huili Created.
2396 --
2397 PROCEDURE Append_More_Data (
2398 p_str_col_names IN char_data_set_type_w,
2399 p_str_data IN char_data_set_type_w,
2400 p_num_col_names IN char_data_set_type_w,
2401 p_num_data IN num_data_set_type_w,
2402 p_col_clause IN OUT NOCOPY VARCHAR2,
2403 p_val_clause IN OUT NOCOPY VARCHAR2
2404 )
2405 IS
2406 BEGIN
2407 IF p_str_col_names.COUNT > 0 THEN
2408 FOR i IN p_str_col_names.FIRST .. p_str_col_names.LAST
2409 LOOP
2410 p_col_clause := p_col_clause || p_str_col_names(i) || ', ';
2411 p_val_clause := p_val_clause || '''' || p_str_data(i) || ''', ';
2412 END LOOP;
2413 END IF;
2414
2415 IF p_num_col_names.COUNT > 0 THEN
2416 FOR j IN p_num_col_names.FIRST .. p_num_col_names.LAST
2417 LOOP
2418 p_col_clause := p_col_clause || p_num_col_names(j) || ', ';
2422 END Append_More_Data;
2419 p_val_clause := p_val_clause || '''' || p_num_data(j) || ''', ';
2420 END LOOP;
2421 END IF;
2423
2424
2425 -- Start of comments
2426 -- NAME
2427 -- Create_Metric
2428 --
2429 -- PURPOSE
2430 -- Creates a metric in AMS_METRICS_ALL_B given the
2431 -- record for the metrics.
2432 --
2433 -- NOTES
2434 --
2435 -- HISTORY
2436 -- 05/26/1999 choang Created.
2437 -- 10/9/1999 ptendulk Modified According to new Standards
2438 -- 17-Apr-2000 tdonohoe Added columns to support 11.5.2 release.
2439 --
2440 -- End of comments
2441
2442 PROCEDURE Create_List_import_Error (
2443 p_api_version IN NUMBER,
2444 p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE,
2445 p_commit IN VARCHAR2 := FND_API.G_TRUE,
2446 p_list_imp_error_rec IN AMS_LIST_IMPORT_ERRORS%ROWTYPE,
2447
2448 x_return_status OUT NOCOPY VARCHAR2,
2449 x_imp_err_id OUT NOCOPY NUMBER
2450 )
2451 IS
2452 --
2453 -- Standard API information constants.
2454 --
2455 L_API_VERSION CONSTANT NUMBER := 1.0;
2456 L_API_NAME CONSTANT VARCHAR2(30) := 'CREATE_LIST_IMPORT_ERROR';
2457 L_FULL_NAME CONSTANT VARCHAR2(60) := G_PKG_NAME ||'.'|| L_API_NAME;
2458
2459 l_list_imp_id NUMBER;
2460 l_flag NUMBER;
2461
2462 l_date DATE := SYSDATE;
2463
2464 CURSOR c_list_imp_error (l_list_imp_id IN NUMBER) IS
2465 SELECT 1
2466 FROM AMS_LIST_IMPORT_ERRORS
2467 WHERE LIST_IMPORT_ERROR_ID = l_list_imp_id;
2468
2469 BEGIN
2470 --
2471 -- Initialize savepoint.
2472 --
2473 SAVEPOINT Create_List_import_Error_pvt;
2474
2475 IF (AMS_DEBUG_HIGH_ON) THEN
2476
2477
2478
2479 Ams_Utility_Pvt.Debug_Message(l_full_name||': START');
2480
2481 END IF;
2482
2483 --
2484 -- Initialize message list if p_init_msg_list is set to TRUE.
2485 --
2486 IF FND_API.To_Boolean (p_init_msg_list) THEN
2487 FND_MSG_PUB.Initialize;
2488 END IF;
2489
2490 --
2491 -- Standard check for API version compatibility.
2492 --
2493 IF NOT FND_API.Compatible_API_Call (L_API_VERSION,
2494 p_api_version,
2495 L_API_NAME,
2496 G_PKG_NAME)
2497 THEN
2498 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2499 END IF;
2500
2501 IF p_list_imp_error_rec.import_list_header_id IS NULL
2502 OR p_list_imp_error_rec.import_source_line_id IS NULL THEN
2503 RAISE FND_API.G_EXC_ERROR;
2504 END IF;
2505
2506 --
2507 -- Initialize API return status to success.
2508 --
2509 x_return_status := FND_API.G_RET_STS_SUCCESS;
2510
2511 LOOP
2512 SELECT AMS_LIST_IMPORT_ERRORS_S.NEXTVAL INTO l_list_imp_id
2513 FROM DUAL;
2514
2515 l_flag := NULL;
2516
2517 OPEN c_list_imp_error (l_list_imp_id);
2518 FETCH c_list_imp_error INTO l_flag;
2519 CLOSE c_list_imp_error;
2520
2521 EXIT WHEN l_flag IS NULL;
2522 END LOOP;
2523
2524 INSERT INTO AMS_LIST_IMPORT_ERRORS (
2525 LIST_IMPORT_ERROR_ID,
2526 LAST_UPDATED_BY,
2527 LAST_UPDATE_DATE,
2528 CREATION_DATE,
2529 CREATED_BY,
2530 LAST_UPDATE_LOGIN,
2531 IMPORT_LIST_HEADER_ID,
2532 IMPORT_SOURCE_LINE_ID,
2533 IMPORT_TYPE,
2534 COL1,
2535 ERROR_TYPE,
2536 SECURITY_GROUP_ID,
2537 REQUEST_ID,
2538 PROGRAM_APPLICATION_ID,
2539 PROGRAM_ID,
2540 PROGRAM_UPDATE_DATE,
2541 BATCH_ID
2542 )
2543 VALUES (
2544 l_list_imp_id,
2545 FND_GLOBAL.User_ID,
2546 l_date,
2547 l_date,
2548 FND_GLOBAL.User_ID,
2549 FND_GLOBAL.Conc_Login_ID,
2550 p_list_imp_error_rec.IMPORT_LIST_HEADER_ID,
2551 p_list_imp_error_rec.IMPORT_SOURCE_LINE_ID,
2552 p_list_imp_error_rec.IMPORT_TYPE,
2553 p_list_imp_error_rec.COL1,
2554 p_list_imp_error_rec.ERROR_TYPE,
2555 p_list_imp_error_rec.SECURITY_GROUP_ID,
2556 p_list_imp_error_rec.REQUEST_ID,
2557 p_list_imp_error_rec.PROGRAM_APPLICATION_ID,
2558 p_list_imp_error_rec.PROGRAM_ID,
2559 p_list_imp_error_rec.PROGRAM_UPDATE_DATE,
2560 p_list_imp_error_rec.BATCH_ID
2561 );
2562
2563 x_imp_err_id := l_list_imp_id;
2564
2565 --
2566 -- Standard check for commit request.
2567 --
2568 IF FND_API.To_Boolean (p_commit) THEN
2569 COMMIT WORK;
2570 END IF;
2571
2572 EXCEPTION
2573 WHEN FND_API.G_EXC_ERROR THEN
2574 ROLLBACK TO Create_List_import_Error_pvt;
2575 x_return_status := FND_API.G_RET_STS_ERROR;
2576
2577 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2578 ROLLBACK TO Create_List_import_Error_pvt;
2579 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2580
2581 WHEN OTHERS THEN
2582 ROLLBACK TO Create_List_import_Error_pvt;
2583 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2584
2585 END Create_List_import_Error;
2586
2587 END AMS_ImportClient_PVT;