[Home] [Help]
PACKAGE BODY: APPS.AMS_IMP_REG_DETAIL_PVT
Source
1 PACKAGE BODY ams_imp_reg_detail_pvt as
2 /* $Header: amsvimrb.pls 115.26 2004/01/07 18:59:18 soagrawa ship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30) := 'ams_imp_reg_detail_pvt';
5 G_ARC_IMPORT_HEADER CONSTANT VARCHAR2(30) := 'IMPH';
6
7 AMS_DEBUG_HIGH_ON CONSTANT boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_HIGH);
8 AMS_DEBUG_LOW_ON CONSTANT boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW);
9 AMS_DEBUG_MEDIUM_ON CONSTANT boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_MEDIUM);
10
11 PROCEDURE LoadProcess( errbuf OUT NOCOPY VARCHAR2
12 , retcode OUT NOCOPY NUMBER
13 , p_list_header_id IN NUMBER := NULL
14 )
15 IS
16 CURSOR c_get_header_id IS
17 -- soagrawa 05-feb-2003 fixed bug# 2773827
18 SELECT IMPORT_LIST_HEADER_ID, PROCESSED_ROWS, NUMBER_OF_FAILED_RECORDS, record_update_flag
19 from AMS_IMP_LIST_HEADERS_ALL
20 where IMPORT_TYPE = 'EVENT'
21 and nvl(PROCESSED_ROWS, -1) < nvl(LOADED_NO_OF_ROWS, 0);
22
23 CURSOR c_get_row_metadata(p_list_header_id in NUMBER) IS
24 -- soagrawa 05-feb-2003 fixed bug# 2773827
25 SELECT PROCESSED_ROWS, NUMBER_OF_FAILED_RECORDS, record_update_flag
26 from AMS_IMP_LIST_HEADERS_ALL
27 where IMPORT_TYPE = 'EVENT'
28 and import_list_header_id = p_list_header_id;
29 --and nvl(PROCESSED_ROWS, -1) < nvl(LOADED_NO_OF_ROWS, 0);
30
31 cursor c_get_lines (id_in IN NUMBER) is
32 select import_source_line_id,
33 last_update_date,
34 last_updated_by,
35 creation_date,
36 created_by,
37 last_update_login,
38 -- Event Details
39 event_source_code,
40 registration_source_type,
41 attendance_flag,
42 waitlisted_flag,
43 cancellation_flag,
44 cancellation_reason_code,
45 confirmation_code,
46 original_system_reference,
47 --Registrants Details,
48 reg_party_id,
49 reg_party_type,
50 reg_contact_id,
51 reg_party_name,
52 reg_title,
53 reg_first_name,
54 reg_middle_name,
55 reg_last_name,
56 reg_address1,
57 reg_address2,
58 reg_address3,
59 reg_address4,
60 reg_gender,
61 reg_address_line_phoenetic,
62 reg_analysis_fy,
63 reg_apt_flag,
64 reg_best_time_contact_begin,
65 reg_best_time_contact_end,
66 reg_category_code,
67 reg_ceo_name,
68 reg_city,
69 reg_country,
70 reg_county,
71 reg_current_fy_potential_rev,
72 reg_next_fy_potential_rev,
73 reg_household_income,
74 reg_decision_maker_flag,
75 reg_department,
76 reg_dun_no_c,
77 reg_email_address,
78 reg_employee_total,
79 reg_fy_end_month,
80 reg_floor,
81 reg_gsa_indicator_flag,
82 reg_house_number,
83 reg_identifying_address_flag,
84 reg_jgzz_fiscal_code,
85 reg_job_title,
86 reg_last_order_date,
87 reg_org_legal_status,
88 reg_line_of_business,
89 reg_mission_statement,
90 reg_org_name_phoenetic,
91 reg_overseas_address_flag,
92 reg_name_suffix,
93 reg_phone_area_code,
94 reg_phone_country_code,
95 reg_phone_extension,
96 reg_phone_number,
97 reg_postal_code,
98 reg_postal_plus4_code,
99 reg_po_box_no,
100 reg_province,
101 reg_rural_route_no,
102 reg_rural_route_type,
103 reg_secondary_suffix_element,
104 reg_sic_code,
105 reg_sic_code_type,
106 reg_site_use_code,
107 reg_state,
108 reg_street,
109 reg_street_number,
110 reg_street_suffix,
111 reg_suite,
112 reg_tax_name,
113 reg_tax_reference,
114 reg_timezone,
115 reg_total_no_of_orders,
116 reg_total_order_amount,
117 reg_year_establised,
118 reg_url,
119 reg_servey_notes,
120 reg_contact_me_flag,
121 reg_email_ok_flag,
122 -- Attendent Details,
123 att_party_id,
124 att_party_type,
125 att_contact_id,
126 att_party_name,
127 att_title,
128 att_first_name,
129 att_middle_name,
130 att_last_name,
131 att_address1,
132 att_address2,
133 att_address3,
134 att_address4,
135 att_gender,
136 att_address_line_phoenetic,
137 att_analysis_fy,
138 att_apt_flag,
139 att_best_time_contact_begin,
140 att_best_time_contact_end,
141 att_category_code,
142 att_ceo_name,
143 att_city,
144 att_country,
145 att_county,
146 att_current_fy_potential_rev,
147 att_next_fy_potential_rev,
148 att_household_income,
149 att_decision_maker_flag,
150 att_department,
151 att_dun_no,
152 att_email_address,
153 att_employee_total,
154 att_fy_end_month,
155 att_floor,
156 att_gsa_indicator_flag,
157 att_house_number,
158 att_identifying_address_flag,
159 att_jgzz_fiscal_code,
160 att_job_title,
161 att_last_order_date,
162 att_legal_status,
163 att_line_of_business,
164 att_mission_statement,
165 att_org_name_phoenetic,
166 att_overseas_address_flag,
167 att_name_suffix,
168 att_phone_area_code,
169 att_phone_country_code,
170 att_phone_extension,
171 att_phone_number,
172 att_postal_code,
173 att_postal_plus4_code,
174 att_po_box_no,
175 att_province,
176 att_rural_route_no,
177 att_rural_route_type,
178 att_secondary_suffix_element,
179 att_sic_code,
180 att_sic_code_type,
181 att_site_use_code,
182 att_state,
183 att_street,
184 att_street_number,
185 att_street_suffix,
186 att_suite,
187 att_tax_name,
188 att_tax_reference,
189 att_timezone,
190 att_total_no_of_orders,
191 att_total_order_amount,
192 att_year_establised,
193 att_url,
194 att_servey_notes,
195 att_contact_me_flag,
196 att_email_ok_flag
197 from AMS_EVENT_MAPPING_V
198 where IMPORT_LIST_HEADER_ID = id_in
199 and IMPORT_SUCCESSFUL_FLAG = 'N'
200 and load_status in ('ACTIVE', 'RELOAD');
201 -- end of c_get_lines
202
203 -- soagrawa 05-feb-2003 fixed bug# 2773827
204 l_upd_flag VARCHAR2(1);
205 l_imp_rec c_get_lines%ROWTYPE;
206 l_reg_rec AMS_Registrants_PUB.RegistrationDet;
207 l_hdr_id NUMBER := p_list_header_id;
208 l_processed_rows NUMBER ;
209 l_failed_rows NUMBER;
210 l_reg_id NUMBER;
211 l_return_status VARCHAR2(1);
212 l_out_status VARCHAR2(1);
213 l_msg_count NUMBER;
214 l_msg_data VARCHAR2(2000);
215 --l_system_status_code VARCHAR2(30);
216 l_conf_code ams_event_registrations.confirmation_code%type;
217 l_cnt1 NUMBER;
218 l_cnt2 NUMBER;
219 l_request_id NUMBER;
220 l_used_by varchar2(30);
221 l_return_status_log varchar2(30);
222 l_notfound NUMBER := 0;
223 -- error_threshold_exc exception;
224 BEGIN
225 l_cnt1 := 0;
226 l_cnt2 := 0;
227 l_used_by := 'IMPH';
228 l_request_id := FND_GLOBAL.CONC_REQUEST_ID;
229 --FND_FILE.PUT_LINE(FND_FILE.LOG,'Start of import Event registrations Record');
230 --FND_FILE.PUT_LINE(FND_FILE.LOG,'requestid :'||l_request_id);
231 IF (p_list_header_id is null)
232 THEN
233 OPEN c_get_header_id;
234 FETCH c_get_header_id
235 -- soagrawa 05-feb-2003 fixed bug# 2773827
236 INTO l_hdr_id,l_processed_rows,l_failed_rows, l_upd_flag;
237 IF c_get_header_id%NOTFOUND
238 THEN
239 CLOSE c_get_header_id;
240 l_notfound := 1;
241 END IF;
242 ELSE
243 OPEN c_get_row_metadata(l_hdr_id);
244 FETCH c_get_row_metadata
245 -- soagrawa 05-feb-2003 fixed bug# 2773827
246 INTO l_processed_rows,l_failed_rows, l_upd_flag;
247 IF c_get_row_metadata%NOTFOUND
248 THEN
249 CLOSE c_get_row_metadata;
250 l_notfound := 1;
251 END IF;
252 END IF;
253 IF (l_notfound = 0)
254 THEN
255 Ams_Utility_PVT.Create_Log (
256 x_return_status => l_return_status_log,
257 p_arc_log_used_by => l_used_by,
258 p_log_used_by_id => l_hdr_id,
259 p_msg_data => 'Start of import Event registrations Record'
260 );
261 Ams_Utility_PVT.Create_Log (
262 x_return_status => l_return_status_log,
263 p_arc_log_used_by => l_used_by,
264 p_log_used_by_id => l_hdr_id,
265 p_msg_data => 'Concurrent Program started the Id is ' || to_char(l_request_id)
266 );
267 IF p_list_header_id IS NULL THEN
268 LOOP
269 --DBMS_OUTPUT.put_line('In side first loop'||l_cnt1||' '||l_cnt2);
270 exit WHEN c_get_header_id%NOTFOUND;
271 -- dbiswas replaced nvl values for l_processed_rows and l_failed_rows with 0 initial val on
272 -- 14-apr-2003 to fix bug 2834282
273 -- l_processed_rows := nvl(l_processed_rows, 0);
274 -- l_failed_rows := nvl(l_failed_rows, 0);
275 l_processed_rows := 0;
276 l_failed_rows := 0 ;
277
278
279 open c_get_lines(l_hdr_id);
280 fetch c_get_lines into l_imp_rec;
281 IF c_get_lines%NOTFOUND THEN
282 CLOSE c_get_lines;
283 --FND_FILE.PUT_LINE(FND_FILE.LOG,'No Line RECORD TO Process at this time');
284 Ams_Utility_PVT.Create_Log (
285 x_return_status => l_return_status_log,
286 p_arc_log_used_by => l_used_by,
287 p_log_used_by_id => l_hdr_id,
288 p_msg_data => 'No Line RECORD TO Process at this time'
289 );
290 --DBMS_OUTPUT.put_line('No line RECORD available Process');
291 ELSE
292 LOOP
293 exit WHEN c_get_lines%NOTFOUND;
294 --DBMS_OUTPUT.put_line('In side second loop'||l_cnt1||' ' ||l_cnt2);
295 Ams_Utility_PVT.Create_Log( x_return_status => l_return_status_log
296 , p_arc_log_used_by => l_used_by
297 , p_log_used_by_id => l_hdr_id
298 , p_msg_data => '----Processing the record whose source Line Id is '
299 || to_char(l_imp_rec.import_source_line_id) || '----'
300 );
301
302 /*
303 --l_reg_rec.EVENT_REGISTRATION_ID := to_number(l_imp_rec.EVENT_REGISTRATION_ID);
304 l_reg_rec.LAST_UPDATE_DATE:= l_imp_rec.LAST_UPDATE_DATE;
305 l_reg_rec.LAST_UPDATED_BY := l_imp_rec.LAST_UPDATED_BY;
306 l_reg_rec.CREATION_DATE := l_imp_rec.CREATION_DATE;
307 l_reg_rec.CREATED_BY := l_imp_rec.CREATED_BY ;
308 l_reg_rec.LAST_UPDATE_LOGIN := l_imp_rec.LAST_UPDATE_LOGIN ;
309 l_reg_rec.OBJECT_VERSION_NUMBER := 1;-- l_imp_rec.OBJECT_VERSION_NUMBER ,
310 l_reg_rec.EVENT_OFFER_ID := to_number(l_imp_rec.EVENT_OFFER_ID);
311 l_reg_rec.APPLICATION_ID := 530; --l_imp_rec.G_APPLICATION_ID;
312 l_reg_rec.ACTIVE_FLAG := l_imp_rec.ACTIVE_FLAG;
313 l_reg_rec.OWNER_USER_ID := l_imp_rec.OWNER_USER_ID;
314 l_reg_rec.SYSTEM_STATUS_CODE:= l_imp_rec.SYSTEM_STATUS_CODE;
315 l_reg_rec.DATE_REGISTRATION_PLACED:= to_date(l_imp_rec.DATE_REGISTRATION_PLACED);
316 l_reg_rec. USER_STATUS_ID:= to_number(l_imp_rec.USER_STATUS_ID) ;
317 l_reg_rec.LAST_REG_STATUS_DATE := to_date (l_imp_rec.LAST_REG_STATUS_DATE) ;
318 l_reg_rec.REG_SOURCE_TYPE_CODE := l_imp_rec.REG_SOURCE_TYPE_CODE;
319 l_reg_rec.REGISTRATION_SOURCE_ID := to_number(l_imp_rec.REGISTRATION_SOURCE_ID);
320 --l_reg_rec.CONFIRMATION_CODE := l_imp_rec.CONFIRMATION_CODE;
321 l_reg_rec.SOURCE_CODE := l_imp_rec.SOURCE_CODE ;
322 l_reg_rec.REGISTRATION_GROUP_ID :=to_number(l_imp_rec.REGISTRATION_GROUP_ID);
323 l_reg_rec.REGISTRANT_PARTY_ID := to_number(l_imp_rec.REGISTRANT_PARTY_ID);
324 l_reg_rec.REGISTRANT_CONTACT_ID := to_number(l_imp_rec.REGISTRANT_CONTACT_ID) ;
325 l_reg_rec.REGISTRANT_ACCOUNT_ID:= to_number(l_imp_rec.REGISTRANT_ACCOUNT_ID);
326 l_reg_rec.ATTENDANT_PARTY_ID := to_number(l_imp_rec.ATTENDANT_PARTY_ID);
327 l_reg_rec.ATTENDANT_CONTACT_ID := to_number(l_imp_rec.ATTENDANT_CONTACT_ID);
328 l_reg_rec.ATTENDANT_ACCOUNT_ID := to_number(l_imp_rec.ATTENDANT_ACCOUNT_ID);
329 l_reg_rec.ORIGINAL_REGISTRANT_CONTACT_ID := to_number(l_imp_rec.ORIGINAL_REGISTRANT_CONTACT_ID);
330 l_reg_rec.PROSPECT_FLAG := l_imp_rec.PROSPECT_FLAG ;
331 l_reg_rec.ATTENDED_FLAG := l_imp_rec.ATTENDED_FLAG ;
332 l_reg_rec.CONFIRMED_FLAG := l_imp_rec.CONFIRMED_FLAG ;
333 l_reg_rec.EVALUATED_FLAG := l_imp_rec.EVALUATED_FLAG ;
334 l_reg_rec.ATTENDANCE_RESULT_CODE := l_imp_rec.ATTENDANCE_RESULT_CODE ;
335 l_reg_rec.WAITLISTED_PRIORITY := to_number(l_imp_rec.WAITLISTED_PRIORITY);
336 l_reg_rec.TARGET_LIST_ID := to_number(l_imp_rec.TARGET_LIST_ID) ;
337 l_reg_rec.INBOUND_MEDIA_ID := to_number(l_imp_rec.INBOUND_MEDIA_ID);
338 l_reg_rec.INBOUND_CHANNEL_ID := to_number(l_imp_rec.INBOUND_CHANNEL_ID) ;
339 l_reg_rec.CANCELLATION_CODE := l_imp_rec.CANCELLATION_CODE ;
340 l_reg_rec.CANCELLATION_REASON_CODE := l_imp_rec.CANCELLATION_REASON_CODE ;
341 l_reg_rec.ATTENDANCE_FAILURE_REASON := l_imp_rec.ATTENDANCE_FAILURE_REASON ;
342 l_reg_rec.ATTENDANT_LANGUAGE := l_imp_rec.ATTENDANT_LANGUAGE ;
343 l_reg_rec.SALESREP_ID := to_number(l_imp_rec.SALESREP_ID) ;
344 l_reg_rec.ORDER_HEADER_ID := to_number(l_imp_rec.ORDER_HEADER_ID) ;
345 l_reg_rec.ORDER_LINE_ID := to_number(l_imp_rec.ORDER_LINE_ID);
346 l_reg_rec.DESCRIPTION := l_imp_rec.DESCRIPTION;
347 l_reg_rec.MAX_ATTENDEE_OVERRIDE_FLAG := l_imp_rec.MAX_ATTENDEE_OVERRIDE_FLAG ;
348 l_reg_rec.INVITE_ONLY_OVERRIDE_FLAG := l_imp_rec.INVITE_ONLY_OVERRIDE_FLAG;
349 l_reg_rec.PAYMENT_STATUS_CODE:= l_imp_rec.PAYMENT_STATUS_CODE;
350 l_reg_rec.AUTO_REGISTER_FLAG:= l_imp_rec.AUTO_REGISTER_FLAG;
351 l_reg_rec.ATTRIBUTE_CATEGORY := l_imp_rec.ATTRIBUTE_CATEGORY ;
352 l_reg_rec.ATTRIBUTE1 := l_imp_rec.ATTRIBUTE1;
353 l_reg_rec.ATTRIBUTE2 := l_imp_rec.ATTRIBUTE2 ;
354 l_reg_rec.ATTRIBUTE3 := l_imp_rec.ATTRIBUTE3;
355 l_reg_rec.ATTRIBUTE4 := l_imp_rec.ATTRIBUTE4;
356 l_reg_rec.ATTRIBUTE5 := l_imp_rec.ATTRIBUTE5;
357 l_reg_rec.ATTRIBUTE6 := l_imp_rec.ATTRIBUTE6;
358 l_reg_rec.ATTRIBUTE7 := l_imp_rec.ATTRIBUTE7;
359 l_reg_rec.ATTRIBUTE8 := l_imp_rec.ATTRIBUTE8;
360 l_reg_rec.ATTRIBUTE9 := l_imp_rec.ATTRIBUTE9;
361 l_reg_rec.ATTRIBUTE10 := l_imp_rec.ATTRIBUTE10;
362 l_reg_rec.ATTRIBUTE11 := l_imp_rec.ATTRIBUTE11;
363 l_reg_rec.ATTRIBUTE12 := l_imp_rec.ATTRIBUTE12;
364 l_reg_rec.ATTRIBUTE13 := l_imp_rec.ATTRIBUTE13;
365 l_reg_rec.ATTRIBUTE14 := l_imp_rec.ATTRIBUTE14;
366 l_reg_rec.ATTRIBUTE15 := l_imp_rec.ATTRIBUTE15;
367 */
368 l_reg_rec.last_update_date := l_imp_rec.last_update_date;
369 l_reg_rec.last_updated_by := l_imp_rec.last_updated_by;
370 l_reg_rec.creation_date := l_imp_rec.creation_date;
371 l_reg_rec.created_by := l_imp_rec.created_by;
372 l_reg_rec.last_update_login := l_imp_rec.last_update_login;
373 -- Event Details
374 l_reg_rec.event_source_code := l_imp_rec.event_source_code;
375 l_reg_rec.registration_source_type := l_imp_rec.registration_source_type;
376 l_reg_rec.attendance_flag := l_imp_rec.attendance_flag;
377 l_reg_rec.waitlisted_flag := l_imp_rec.waitlisted_flag;
378 l_reg_rec.cancellation_flag := l_imp_rec.cancellation_flag;
379 l_reg_rec.cancellation_reason_code := l_imp_rec.cancellation_reason_code;
380 l_reg_rec.confirmation_code := l_imp_rec.confirmation_code;
381 l_reg_rec.original_system_reference := l_imp_rec.original_system_reference;
382 --Registrant Details
383 l_reg_rec.reg_party_id := l_imp_rec.reg_party_id;
384 l_reg_rec.reg_party_type := l_imp_rec.reg_party_type;
385 l_reg_rec.reg_contact_id := l_imp_rec.reg_contact_id;
386 l_reg_rec.reg_party_name := l_imp_rec.reg_party_name;
387 l_reg_rec.reg_title := l_imp_rec.reg_title;
388 l_reg_rec.reg_first_name := l_imp_rec.reg_first_name;
389 l_reg_rec.reg_middle_name := l_imp_rec.reg_middle_name;
390 l_reg_rec.reg_last_name := l_imp_rec.reg_last_name;
391 l_reg_rec.reg_address1 := l_imp_rec.reg_address1;
392 l_reg_rec.reg_address2 := l_imp_rec.reg_address2;
393 l_reg_rec.reg_address3 := l_imp_rec.reg_address3;
394 l_reg_rec.reg_address4 := l_imp_rec.reg_address4;
395 l_reg_rec.reg_gender := l_imp_rec.reg_gender;
396 l_reg_rec.reg_address_line_phonetic := l_imp_rec.reg_address_line_phoenetic;
397 l_reg_rec.reg_analysis_fy := l_imp_rec.reg_analysis_fy;
398 l_reg_rec.reg_apt_flag := l_imp_rec.reg_apt_flag;
399 l_reg_rec.reg_best_time_contact_begin := l_imp_rec.reg_best_time_contact_begin;
400 l_reg_rec.reg_best_time_contact_end := l_imp_rec.reg_best_time_contact_end;
401 l_reg_rec.reg_category_code := l_imp_rec.reg_category_code;
402 l_reg_rec.reg_ceo_name := l_imp_rec.reg_ceo_name;
403 l_reg_rec.reg_city := l_imp_rec.reg_city;
404 l_reg_rec.reg_country := l_imp_rec.reg_country;
405 l_reg_rec.reg_county := l_imp_rec.reg_county;
409 l_reg_rec.reg_decision_maker_flag := l_imp_rec.reg_decision_maker_flag;
406 l_reg_rec.reg_current_fy_potential_rev := l_imp_rec.reg_current_fy_potential_rev;
407 l_reg_rec.reg_next_fy_potential_rev := l_imp_rec.reg_next_fy_potential_rev;
408 l_reg_rec.reg_household_income := l_imp_rec.reg_household_income;
410 l_reg_rec.reg_department := l_imp_rec.reg_department;
411 l_reg_rec.reg_dun_no_c := l_imp_rec.reg_dun_no_c;
412 l_reg_rec.reg_email_address := l_imp_rec.reg_email_address;
413 l_reg_rec.reg_employee_total := l_imp_rec.reg_employee_total;
414 l_reg_rec.reg_fy_end_month := l_imp_rec.reg_fy_end_month;
415 l_reg_rec.reg_floor := l_imp_rec.reg_floor;
416 l_reg_rec.reg_gsa_indicator_flag := l_imp_rec.reg_gsa_indicator_flag;
417 l_reg_rec.reg_house_number := l_imp_rec.reg_house_number;
418 l_reg_rec.reg_identifying_address_flag := l_imp_rec.reg_identifying_address_flag;
419 l_reg_rec.reg_jgzz_fiscal_code := l_imp_rec.reg_jgzz_fiscal_code;
420 l_reg_rec.reg_job_title := l_imp_rec.reg_job_title;
421 l_reg_rec.reg_last_order_date := l_imp_rec.reg_last_order_date;
422 l_reg_rec.reg_org_legal_status := l_imp_rec.reg_org_legal_status;
423 l_reg_rec.reg_line_of_business := l_imp_rec.reg_line_of_business;
424 l_reg_rec.reg_mission_statement := l_imp_rec.reg_mission_statement;
425 l_reg_rec.reg_org_name_phonetic := l_imp_rec.reg_org_name_phoenetic;
426 l_reg_rec.reg_overseas_address_flag := l_imp_rec.reg_overseas_address_flag;
427 l_reg_rec.reg_name_suffix := l_imp_rec.reg_name_suffix;
428 l_reg_rec.reg_phone_area_code := l_imp_rec.reg_phone_area_code;
429 l_reg_rec.reg_phone_country_code := l_imp_rec.reg_phone_country_code;
430 l_reg_rec.reg_phone_extension := l_imp_rec.reg_phone_extension;
431 l_reg_rec.reg_phone_number := l_imp_rec.reg_phone_number;
432 l_reg_rec.reg_postal_code := l_imp_rec.reg_postal_code;
433 l_reg_rec.reg_postal_plus4_code := l_imp_rec.reg_postal_plus4_code;
434 l_reg_rec.reg_po_box_no := l_imp_rec.reg_po_box_no;
435 l_reg_rec.reg_province := l_imp_rec.reg_province;
436 l_reg_rec.reg_rural_route_no := l_imp_rec.reg_rural_route_no;
437 l_reg_rec.reg_rural_route_type := l_imp_rec.reg_rural_route_type;
438 l_reg_rec.reg_secondary_suffix_element := l_imp_rec.reg_secondary_suffix_element;
439 l_reg_rec.reg_sic_code := l_imp_rec.reg_sic_code;
440 l_reg_rec.reg_sic_code_type := l_imp_rec.reg_sic_code_type;
441 l_reg_rec.reg_site_use_code := l_imp_rec.reg_site_use_code;
442 l_reg_rec.reg_state := l_imp_rec.reg_state;
443 l_reg_rec.reg_street := l_imp_rec.reg_street;
444 l_reg_rec.reg_street_number := l_imp_rec.reg_street_number;
445 l_reg_rec.reg_street_suffix := l_imp_rec.reg_street_suffix;
446 l_reg_rec.reg_suite := l_imp_rec.reg_suite;
447 l_reg_rec.reg_tax_name := l_imp_rec.reg_tax_name;
448 l_reg_rec.reg_tax_reference := l_imp_rec.reg_tax_reference;
449 l_reg_rec.reg_timezone := l_imp_rec.reg_timezone;
450 l_reg_rec.reg_total_no_of_orders := l_imp_rec.reg_total_no_of_orders;
451 l_reg_rec.reg_total_order_amount := l_imp_rec.reg_total_order_amount;
452 l_reg_rec.reg_year_established := l_imp_rec.reg_year_establised;
453 l_reg_rec.reg_url := l_imp_rec.reg_url;
454 l_reg_rec.reg_survey_notes := l_imp_rec.reg_servey_notes;
455 l_reg_rec.reg_contact_me_flag := l_imp_rec.reg_contact_me_flag;
456 l_reg_rec.reg_email_ok_flag := l_imp_rec.reg_email_ok_flag;
457 -- Attendent Details
458 l_reg_rec.att_party_id := l_imp_rec.att_party_id;
459 l_reg_rec.att_party_type := l_imp_rec.att_party_type;
460 l_reg_rec.att_contact_id := l_imp_rec.att_contact_id;
461 l_reg_rec.att_party_name := l_imp_rec.att_party_name;
462 l_reg_rec.att_title := l_imp_rec.att_title;
463 l_reg_rec.att_first_name := l_imp_rec.att_first_name;
464 l_reg_rec.att_middle_name := l_imp_rec.att_middle_name;
465 l_reg_rec.att_last_name := l_imp_rec.att_last_name;
466 l_reg_rec.att_address1 := l_imp_rec.att_address1;
467 l_reg_rec.att_address2 := l_imp_rec.att_address2;
468 l_reg_rec.att_address3 := l_imp_rec.att_address3;
469 l_reg_rec.att_address4 := l_imp_rec.att_address4;
470 l_reg_rec.att_gender := l_imp_rec.att_gender;
471 l_reg_rec.att_address_line_phonetic := l_imp_rec.att_address_line_phoenetic;
472 l_reg_rec.att_analysis_fy := l_imp_rec.att_analysis_fy;
473 l_reg_rec.att_apt_flag := l_imp_rec.att_apt_flag;
474 l_reg_rec.att_best_time_contact_begin := l_imp_rec.att_best_time_contact_begin;
475 l_reg_rec.att_best_time_contact_end := l_imp_rec.att_best_time_contact_end;
476 l_reg_rec.att_category_code := l_imp_rec.att_category_code;
477 l_reg_rec.att_ceo_name := l_imp_rec.att_ceo_name;
478 l_reg_rec.att_city := l_imp_rec.att_city;
482 l_reg_rec.att_next_fy_potential_rev := l_imp_rec.att_next_fy_potential_rev;
479 l_reg_rec.att_country := l_imp_rec.att_country;
480 l_reg_rec.att_county := l_imp_rec.att_county;
481 l_reg_rec.att_current_fy_potential_rev := l_imp_rec.att_current_fy_potential_rev;
483 l_reg_rec.att_household_income := l_imp_rec.att_household_income;
484 l_reg_rec.att_decision_maker_flag := l_imp_rec.att_decision_maker_flag;
485 l_reg_rec.att_department := l_imp_rec.att_department;
486 l_reg_rec.att_dun_no_c := l_imp_rec.att_dun_no;
487 l_reg_rec.att_email_address := l_imp_rec.att_email_address;
488 l_reg_rec.att_employee_total := l_imp_rec.att_employee_total;
489 l_reg_rec.att_fy_end_month := l_imp_rec.att_fy_end_month;
490 l_reg_rec.att_floor := l_imp_rec.att_floor;
491 l_reg_rec.att_gsa_indicator_flag := l_imp_rec.att_gsa_indicator_flag;
492 l_reg_rec.att_house_number := l_imp_rec.att_house_number;
493 l_reg_rec.att_identifying_address_flag := l_imp_rec.att_identifying_address_flag;
494 l_reg_rec.att_jgzz_fiscal_code := l_imp_rec.att_jgzz_fiscal_code;
495 l_reg_rec.att_job_title := l_imp_rec.att_job_title;
496 l_reg_rec.att_last_order_date := l_imp_rec.att_last_order_date;
497 l_reg_rec.att_org_legal_status := l_imp_rec.att_legal_status;
498 l_reg_rec.att_line_of_business := l_imp_rec.att_line_of_business;
499 l_reg_rec.att_mission_statement := l_imp_rec.att_mission_statement;
500 l_reg_rec.att_org_name_phonetic := l_imp_rec.att_org_name_phoenetic;
501 l_reg_rec.att_overseas_address_flag := l_imp_rec.att_overseas_address_flag;
502 l_reg_rec.att_name_suffix := l_imp_rec.att_name_suffix;
503 l_reg_rec.att_phone_area_code := l_imp_rec.att_phone_area_code;
504 l_reg_rec.att_phone_country_code := l_imp_rec.att_phone_country_code;
505 l_reg_rec.att_phone_extension := l_imp_rec.att_phone_extension;
506 l_reg_rec.att_phone_number := l_imp_rec.att_phone_number;
507 l_reg_rec.att_postal_code := l_imp_rec.att_postal_code;
508 l_reg_rec.att_postal_plus4_code := l_imp_rec.att_postal_plus4_code;
509 l_reg_rec.att_po_box_no := l_imp_rec.att_po_box_no;
510 l_reg_rec.att_province := l_imp_rec.att_province;
511 l_reg_rec.att_rural_route_no := l_imp_rec.att_rural_route_no;
512 l_reg_rec.att_rural_route_type := l_imp_rec.att_rural_route_type;
513 l_reg_rec.att_secondary_suffix_element := l_imp_rec.att_secondary_suffix_element;
514 l_reg_rec.att_sic_code := l_imp_rec.att_sic_code;
515 l_reg_rec.att_sic_code_type := l_imp_rec.att_sic_code_type;
516 l_reg_rec.att_site_use_code := l_imp_rec.att_site_use_code;
517 l_reg_rec.att_state := l_imp_rec.att_state;
518 l_reg_rec.att_street := l_imp_rec.att_street;
519 l_reg_rec.att_street_number := l_imp_rec.att_street_number;
520 l_reg_rec.att_street_suffix := l_imp_rec.att_street_suffix;
521 l_reg_rec.att_suite := l_imp_rec.att_suite;
522 l_reg_rec.att_tax_name := l_imp_rec.att_tax_name;
523 l_reg_rec.att_tax_reference := l_imp_rec.att_tax_reference;
524 l_reg_rec.att_timezone := l_imp_rec.att_timezone;
525 l_reg_rec.att_total_no_of_orders := l_imp_rec.att_total_no_of_orders;
526 l_reg_rec.att_total_order_amount := l_imp_rec.att_total_order_amount;
527 l_reg_rec.att_year_established := l_imp_rec.att_year_establised;
528 l_reg_rec.att_url := l_imp_rec.att_url;
529 l_reg_rec.att_survey_notes := l_imp_rec.att_servey_notes;
530 l_reg_rec.att_contact_me_flag := l_imp_rec.att_contact_me_flag;
531 l_reg_rec.att_email_ok_flag := l_imp_rec.att_email_ok_flag;
532 -- soagrawa 27-feb-2003 bug# 2824593
533 IF l_upd_flag IS NOT null
534 THEN
535 IF l_upd_flag = 'Y'
536 THEN
537 l_reg_rec.update_reg_rec := 'Y';
538 ELSIF l_upd_flag = 'N'
539 THEN
540 l_reg_rec.update_reg_rec := 'C';
541 END IF;
542 END IF;
543
544 AMS_Registrants_PUB.Register( p_api_version_number => 1.0
545 , p_init_msg_list => FND_API.G_FALSE
546 , p_commit => FND_API.G_FALSE
547 , x_return_status => l_return_status
548 , x_msg_count => l_msg_count
549 , x_msg_data => l_msg_data
550 , p_reg_det_rec => l_reg_rec
551 -- soagrawa 30-jan-2003 bug# 2769511
552 -- , p_owner_user_id => -1 -- what do we do with this?
553 , p_owner_user_id => ams_utility_pvt.get_resource_id(FND_GLOBAL.user_id)
557 l_processed_rows := l_processed_rows+1;
554 , p_application_id => 530
555 , x_confirm_code => l_conf_code
556 );
558 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
559 l_failed_rows := l_failed_rows+1;
560 END IF;
561
562 -- BEGIN
563 update_imp_src_line_rec( p_imp_src_id => l_imp_rec.import_source_line_id
564 , p_imp_hdr_id => l_hdr_id
565 , p_return_status => l_return_status
566 , p_msg_data => l_msg_data
567 , p_msg_count => l_msg_count -- Added by ptendulk on 21-Dec-2002
568 , p_out_status => l_out_status
569 );
570 -- soagrawa added out status on 03-feb-2003 for error threshold
571 IF (l_out_status = FND_API.G_RET_STS_ERROR) THEN
572 RETURN;
573 END IF;
574 -- EXCEPTION
575 -- WHEN error_threshold_exc THEN
576 -- RAISE error_threshold_exc;
577 -- END;
578
579 fetch c_get_lines
580 into l_imp_rec;
581 l_cnt2 := l_cnt2 + 1;
582 END LOOP; -- Inner LOOP for line rec
583 CLOSE c_get_lines;
584 END IF;
585 update_imp_hdr_rec( p_imp_hdr_id => l_hdr_id
586 , p_processed_rows => l_processed_rows
587 , p_failed_rows => l_failed_rows
588 );
589 IF (p_list_header_id is null)
590 THEN
591 FETCH c_get_header_id
592 into l_hdr_id,
593 l_processed_rows,
594 l_failed_rows,
595 -- soagrawa 05-feb-2003 fixed bug# 2773827
596 l_upd_flag;
597 ELSE
598 FETCH c_get_row_metadata
599 into l_processed_rows,
600 l_failed_rows,
601 -- soagrawa 05-feb-2003 fixed bug# 2773827
602 l_upd_flag;
603 END IF;
604 l_cnt1 := l_cnt1 + 1;
605 END LOOP;
606 ELSE
607 -- dbiswas replaced nvl values for l_processed_rows and l_failed_rows with 0 initial val on
608 -- 14-apr-2003 to fix bug 2834282
609 -- l_processed_rows := nvl(l_processed_rows, 0);
610 -- l_failed_rows := nvl(l_failed_rows, 0);
611 l_processed_rows := 0;
612 l_failed_rows := 0;
613
614 open c_get_lines(p_list_header_id);
615 fetch c_get_lines into l_imp_rec;
616 IF c_get_lines%NOTFOUND THEN
617 CLOSE c_get_lines;
618 --FND_FILE.PUT_LINE(FND_FILE.LOG,'No Line RECORD TO Process at this time');
619 Ams_Utility_PVT.Create_Log (
620 x_return_status => l_return_status_log,
621 p_arc_log_used_by => l_used_by,
622 p_log_used_by_id => p_list_header_id,
623 p_msg_data => 'No Line RECORD TO Process at this time'
624 );
625 --DBMS_OUTPUT.put_line('No line RECORD available Process');
626 ELSE
627 LOOP
628 exit WHEN c_get_lines%NOTFOUND;
629 --DBMS_OUTPUT.put_line('In side second loop'||l_cnt1||' ' ||l_cnt2);
630 Ams_Utility_PVT.Create_Log( x_return_status => l_return_status_log
631 , p_arc_log_used_by => l_used_by
632 , p_log_used_by_id => p_list_header_id
633 , p_msg_data => '----Processing the record whose source Line Id is '
634 || to_char(l_imp_rec.import_source_line_id) || '----'
635 );
636 /*
637 --l_reg_rec.EVENT_REGISTRATION_ID := to_number(l_imp_rec.EVENT_REGISTRATION_ID);
638 l_reg_rec.LAST_UPDATE_DATE:= l_imp_rec.LAST_UPDATE_DATE;
639 l_reg_rec.LAST_UPDATED_BY := l_imp_rec.LAST_UPDATED_BY;
640 l_reg_rec.CREATION_DATE := l_imp_rec.CREATION_DATE;
641 l_reg_rec.CREATED_BY := l_imp_rec.CREATED_BY ;
642 l_reg_rec.LAST_UPDATE_LOGIN := l_imp_rec.LAST_UPDATE_LOGIN ;
643 l_reg_rec.OBJECT_VERSION_NUMBER := 1;-- l_imp_rec.OBJECT_VERSION_NUMBER ,
644 l_reg_rec.EVENT_OFFER_ID := to_number(l_imp_rec.EVENT_OFFER_ID);
645 l_reg_rec.APPLICATION_ID := 530; --l_imp_rec.G_APPLICATION_ID;
646 l_reg_rec.ACTIVE_FLAG := l_imp_rec.ACTIVE_FLAG;
647 l_reg_rec.OWNER_USER_ID := l_imp_rec.OWNER_USER_ID;
648 l_reg_rec.SYSTEM_STATUS_CODE:= l_imp_rec.SYSTEM_STATUS_CODE;
649 l_reg_rec.DATE_REGISTRATION_PLACED:= to_date(l_imp_rec.DATE_REGISTRATION_PLACED);
650 l_reg_rec. USER_STATUS_ID:= to_number(l_imp_rec.USER_STATUS_ID) ;
654 --l_reg_rec.CONFIRMATION_CODE := l_imp_rec.CONFIRMATION_CODE;
651 l_reg_rec.LAST_REG_STATUS_DATE := to_date (l_imp_rec.LAST_REG_STATUS_DATE) ;
652 l_reg_rec.REG_SOURCE_TYPE_CODE := l_imp_rec.REG_SOURCE_TYPE_CODE;
653 l_reg_rec.REGISTRATION_SOURCE_ID := to_number(l_imp_rec.REGISTRATION_SOURCE_ID);
655 l_reg_rec.SOURCE_CODE := l_imp_rec.SOURCE_CODE ;
656 l_reg_rec.REGISTRATION_GROUP_ID :=to_number(l_imp_rec.REGISTRATION_GROUP_ID);
657 l_reg_rec.REGISTRANT_PARTY_ID := to_number(l_imp_rec.REGISTRANT_PARTY_ID);
658 l_reg_rec.REGISTRANT_CONTACT_ID := to_number(l_imp_rec.REGISTRANT_CONTACT_ID) ;
659 l_reg_rec.REGISTRANT_ACCOUNT_ID:= to_number(l_imp_rec.REGISTRANT_ACCOUNT_ID);
660 l_reg_rec.ATTENDANT_PARTY_ID := to_number(l_imp_rec.ATTENDANT_PARTY_ID);
661 l_reg_rec.ATTENDANT_CONTACT_ID := to_number(l_imp_rec.ATTENDANT_CONTACT_ID);
662 l_reg_rec.ATTENDANT_ACCOUNT_ID := to_number(l_imp_rec.ATTENDANT_ACCOUNT_ID);
663 l_reg_rec.ORIGINAL_REGISTRANT_CONTACT_ID := to_number(l_imp_rec.ORIGINAL_REGISTRANT_CONTACT_ID);
664 l_reg_rec.PROSPECT_FLAG := l_imp_rec.PROSPECT_FLAG ;
665 l_reg_rec.ATTENDED_FLAG := l_imp_rec.ATTENDED_FLAG ;
666 l_reg_rec.CONFIRMED_FLAG := l_imp_rec.CONFIRMED_FLAG ;
667 l_reg_rec.EVALUATED_FLAG := l_imp_rec.EVALUATED_FLAG ;
668 l_reg_rec.ATTENDANCE_RESULT_CODE := l_imp_rec.ATTENDANCE_RESULT_CODE ;
669 l_reg_rec.WAITLISTED_PRIORITY := to_number(l_imp_rec.WAITLISTED_PRIORITY);
670 l_reg_rec.TARGET_LIST_ID := to_number(l_imp_rec.TARGET_LIST_ID) ;
671 l_reg_rec.INBOUND_MEDIA_ID := to_number(l_imp_rec.INBOUND_MEDIA_ID);
672 l_reg_rec.INBOUND_CHANNEL_ID := to_number(l_imp_rec.INBOUND_CHANNEL_ID) ;
673 l_reg_rec.CANCELLATION_CODE := l_imp_rec.CANCELLATION_CODE ;
674 l_reg_rec.CANCELLATION_REASON_CODE := l_imp_rec.CANCELLATION_REASON_CODE ;
675 l_reg_rec.ATTENDANCE_FAILURE_REASON := l_imp_rec.ATTENDANCE_FAILURE_REASON ;
676 l_reg_rec.ATTENDANT_LANGUAGE := l_imp_rec.ATTENDANT_LANGUAGE ;
677 l_reg_rec.SALESREP_ID := to_number(l_imp_rec.SALESREP_ID) ;
678 l_reg_rec.ORDER_HEADER_ID := to_number(l_imp_rec.ORDER_HEADER_ID) ;
679 l_reg_rec.ORDER_LINE_ID := to_number(l_imp_rec.ORDER_LINE_ID);
680 l_reg_rec.DESCRIPTION := l_imp_rec.DESCRIPTION;
681 l_reg_rec.MAX_ATTENDEE_OVERRIDE_FLAG := l_imp_rec.MAX_ATTENDEE_OVERRIDE_FLAG ;
682 l_reg_rec.INVITE_ONLY_OVERRIDE_FLAG := l_imp_rec.INVITE_ONLY_OVERRIDE_FLAG;
683 l_reg_rec.PAYMENT_STATUS_CODE:= l_imp_rec.PAYMENT_STATUS_CODE;
684 l_reg_rec.AUTO_REGISTER_FLAG:= l_imp_rec.AUTO_REGISTER_FLAG;
685 l_reg_rec.ATTRIBUTE_CATEGORY := l_imp_rec.ATTRIBUTE_CATEGORY ;
686 l_reg_rec.ATTRIBUTE1 := l_imp_rec.ATTRIBUTE1;
687 l_reg_rec.ATTRIBUTE2 := l_imp_rec.ATTRIBUTE2 ;
688 l_reg_rec.ATTRIBUTE3 := l_imp_rec.ATTRIBUTE3;
689 l_reg_rec.ATTRIBUTE4 := l_imp_rec.ATTRIBUTE4;
690 l_reg_rec.ATTRIBUTE5 := l_imp_rec.ATTRIBUTE5;
691 l_reg_rec.ATTRIBUTE6 := l_imp_rec.ATTRIBUTE6;
692 l_reg_rec.ATTRIBUTE7 := l_imp_rec.ATTRIBUTE7;
693 l_reg_rec.ATTRIBUTE8 := l_imp_rec.ATTRIBUTE8;
694 l_reg_rec.ATTRIBUTE9 := l_imp_rec.ATTRIBUTE9;
695 l_reg_rec.ATTRIBUTE10 := l_imp_rec.ATTRIBUTE10;
696 l_reg_rec.ATTRIBUTE11 := l_imp_rec.ATTRIBUTE11;
697 l_reg_rec.ATTRIBUTE12 := l_imp_rec.ATTRIBUTE12;
698 l_reg_rec.ATTRIBUTE13 := l_imp_rec.ATTRIBUTE13;
699 l_reg_rec.ATTRIBUTE14 := l_imp_rec.ATTRIBUTE14;
700 l_reg_rec.ATTRIBUTE15 := l_imp_rec.ATTRIBUTE15;
701 */
702 l_reg_rec.last_update_date := l_imp_rec.last_update_date;
703 l_reg_rec.last_updated_by := l_imp_rec.last_updated_by;
704 l_reg_rec.creation_date := l_imp_rec.creation_date;
705 l_reg_rec.created_by := l_imp_rec.created_by;
706 l_reg_rec.last_update_login := l_imp_rec.last_update_login;
707 -- Event Details
708 l_reg_rec.event_source_code := l_imp_rec.event_source_code;
709 l_reg_rec.registration_source_type := l_imp_rec.registration_source_type;
710 l_reg_rec.attendance_flag := l_imp_rec.attendance_flag;
711 l_reg_rec.waitlisted_flag := l_imp_rec.waitlisted_flag;
712 l_reg_rec.cancellation_flag := l_imp_rec.cancellation_flag;
713 l_reg_rec.cancellation_reason_code := l_imp_rec.cancellation_reason_code;
714 l_reg_rec.confirmation_code := l_imp_rec.confirmation_code;
715 l_reg_rec.original_system_reference := l_imp_rec.original_system_reference;
716 --Registrant Details
717 l_reg_rec.reg_party_id := l_imp_rec.reg_party_id;
718 l_reg_rec.reg_party_type := l_imp_rec.reg_party_type;
719 l_reg_rec.reg_contact_id := l_imp_rec.reg_contact_id;
720 l_reg_rec.reg_party_name := l_imp_rec.reg_party_name;
721 l_reg_rec.reg_title := l_imp_rec.reg_title;
722 l_reg_rec.reg_first_name := l_imp_rec.reg_first_name;
723 l_reg_rec.reg_middle_name := l_imp_rec.reg_middle_name;
727 l_reg_rec.reg_address3 := l_imp_rec.reg_address3;
724 l_reg_rec.reg_last_name := l_imp_rec.reg_last_name;
725 l_reg_rec.reg_address1 := l_imp_rec.reg_address1;
726 l_reg_rec.reg_address2 := l_imp_rec.reg_address2;
728 l_reg_rec.reg_address4 := l_imp_rec.reg_address4;
729 l_reg_rec.reg_gender := l_imp_rec.reg_gender;
730 l_reg_rec.reg_address_line_phonetic := l_imp_rec.reg_address_line_phoenetic;
731 l_reg_rec.reg_analysis_fy := l_imp_rec.reg_analysis_fy;
732 l_reg_rec.reg_apt_flag := l_imp_rec.reg_apt_flag;
733 l_reg_rec.reg_best_time_contact_begin := l_imp_rec.reg_best_time_contact_begin;
734 l_reg_rec.reg_best_time_contact_end := l_imp_rec.reg_best_time_contact_end;
735 l_reg_rec.reg_category_code := l_imp_rec.reg_category_code;
736 l_reg_rec.reg_ceo_name := l_imp_rec.reg_ceo_name;
737 l_reg_rec.reg_city := l_imp_rec.reg_city;
738 l_reg_rec.reg_country := l_imp_rec.reg_country;
739 l_reg_rec.reg_county := l_imp_rec.reg_county;
740 l_reg_rec.reg_current_fy_potential_rev := l_imp_rec.reg_current_fy_potential_rev;
741 l_reg_rec.reg_next_fy_potential_rev := l_imp_rec.reg_next_fy_potential_rev;
742 l_reg_rec.reg_household_income := l_imp_rec.reg_household_income;
743 l_reg_rec.reg_decision_maker_flag := l_imp_rec.reg_decision_maker_flag;
744 l_reg_rec.reg_department := l_imp_rec.reg_department;
745 l_reg_rec.reg_dun_no_c := l_imp_rec.reg_dun_no_c;
746 l_reg_rec.reg_email_address := l_imp_rec.reg_email_address;
747 l_reg_rec.reg_employee_total := l_imp_rec.reg_employee_total;
748 l_reg_rec.reg_fy_end_month := l_imp_rec.reg_fy_end_month;
749 l_reg_rec.reg_floor := l_imp_rec.reg_floor;
750 l_reg_rec.reg_gsa_indicator_flag := l_imp_rec.reg_gsa_indicator_flag;
751 l_reg_rec.reg_house_number := l_imp_rec.reg_house_number;
752 l_reg_rec.reg_identifying_address_flag := l_imp_rec.reg_identifying_address_flag;
753 l_reg_rec.reg_jgzz_fiscal_code := l_imp_rec.reg_jgzz_fiscal_code;
754 l_reg_rec.reg_job_title := l_imp_rec.reg_job_title;
755 l_reg_rec.reg_last_order_date := l_imp_rec.reg_last_order_date;
756 l_reg_rec.reg_org_legal_status := l_imp_rec.reg_org_legal_status;
757 l_reg_rec.reg_line_of_business := l_imp_rec.reg_line_of_business;
758 l_reg_rec.reg_mission_statement := l_imp_rec.reg_mission_statement;
759 l_reg_rec.reg_org_name_phonetic := l_imp_rec.reg_org_name_phoenetic;
760 l_reg_rec.reg_overseas_address_flag := l_imp_rec.reg_overseas_address_flag;
761 l_reg_rec.reg_name_suffix := l_imp_rec.reg_name_suffix;
762 l_reg_rec.reg_phone_area_code := l_imp_rec.reg_phone_area_code;
763 l_reg_rec.reg_phone_country_code := l_imp_rec.reg_phone_country_code;
764 l_reg_rec.reg_phone_extension := l_imp_rec.reg_phone_extension;
765 l_reg_rec.reg_phone_number := l_imp_rec.reg_phone_number;
766 l_reg_rec.reg_postal_code := l_imp_rec.reg_postal_code;
767 l_reg_rec.reg_postal_plus4_code := l_imp_rec.reg_postal_plus4_code;
768 l_reg_rec.reg_po_box_no := l_imp_rec.reg_po_box_no;
769 l_reg_rec.reg_province := l_imp_rec.reg_province;
770 l_reg_rec.reg_rural_route_no := l_imp_rec.reg_rural_route_no;
771 l_reg_rec.reg_rural_route_type := l_imp_rec.reg_rural_route_type;
772 l_reg_rec.reg_secondary_suffix_element := l_imp_rec.reg_secondary_suffix_element;
773 l_reg_rec.reg_sic_code := l_imp_rec.reg_sic_code;
774 l_reg_rec.reg_sic_code_type := l_imp_rec.reg_sic_code_type;
775 l_reg_rec.reg_site_use_code := l_imp_rec.reg_site_use_code;
776 l_reg_rec.reg_state := l_imp_rec.reg_state;
777 l_reg_rec.reg_street := l_imp_rec.reg_street;
778 l_reg_rec.reg_street_number := l_imp_rec.reg_street_number;
779 l_reg_rec.reg_street_suffix := l_imp_rec.reg_street_suffix;
780 l_reg_rec.reg_suite := l_imp_rec.reg_suite;
781 l_reg_rec.reg_tax_name := l_imp_rec.reg_tax_name;
782 l_reg_rec.reg_tax_reference := l_imp_rec.reg_tax_reference;
783 l_reg_rec.reg_timezone := l_imp_rec.reg_timezone;
784 l_reg_rec.reg_total_no_of_orders := l_imp_rec.reg_total_no_of_orders;
785 l_reg_rec.reg_total_order_amount := l_imp_rec.reg_total_order_amount;
786 l_reg_rec.reg_year_established := l_imp_rec.reg_year_establised;
787 l_reg_rec.reg_url := l_imp_rec.reg_url;
788 l_reg_rec.reg_survey_notes := l_imp_rec.reg_servey_notes;
789 l_reg_rec.reg_contact_me_flag := l_imp_rec.reg_contact_me_flag;
790 l_reg_rec.reg_email_ok_flag := l_imp_rec.reg_email_ok_flag;
791 -- Attendent Details
792 l_reg_rec.att_party_id := l_imp_rec.att_party_id;
793 l_reg_rec.att_party_type := l_imp_rec.att_party_type;
794 l_reg_rec.att_contact_id := l_imp_rec.att_contact_id;
795 l_reg_rec.att_party_name := l_imp_rec.att_party_name;
796 l_reg_rec.att_title := l_imp_rec.att_title;
800 l_reg_rec.att_address1 := l_imp_rec.att_address1;
797 l_reg_rec.att_first_name := l_imp_rec.att_first_name;
798 l_reg_rec.att_middle_name := l_imp_rec.att_middle_name;
799 l_reg_rec.att_last_name := l_imp_rec.att_last_name;
801 l_reg_rec.att_address2 := l_imp_rec.att_address2;
802 l_reg_rec.att_address3 := l_imp_rec.att_address3;
803 l_reg_rec.att_address4 := l_imp_rec.att_address4;
804 l_reg_rec.att_gender := l_imp_rec.att_gender;
805 l_reg_rec.att_address_line_phonetic := l_imp_rec.att_address_line_phoenetic;
806 l_reg_rec.att_analysis_fy := l_imp_rec.att_analysis_fy;
807 l_reg_rec.att_apt_flag := l_imp_rec.att_apt_flag;
808 l_reg_rec.att_best_time_contact_begin := l_imp_rec.att_best_time_contact_begin;
809 l_reg_rec.att_best_time_contact_end := l_imp_rec.att_best_time_contact_end;
810 l_reg_rec.att_category_code := l_imp_rec.att_category_code;
811 l_reg_rec.att_ceo_name := l_imp_rec.att_ceo_name;
812 l_reg_rec.att_city := l_imp_rec.att_city;
813 l_reg_rec.att_country := l_imp_rec.att_country;
814 l_reg_rec.att_county := l_imp_rec.att_county;
815 l_reg_rec.att_current_fy_potential_rev := l_imp_rec.att_current_fy_potential_rev;
816 l_reg_rec.att_next_fy_potential_rev := l_imp_rec.att_next_fy_potential_rev;
817 l_reg_rec.att_household_income := l_imp_rec.att_household_income;
818 l_reg_rec.att_decision_maker_flag := l_imp_rec.att_decision_maker_flag;
819 l_reg_rec.att_department := l_imp_rec.att_department;
820 l_reg_rec.att_dun_no_c := l_imp_rec.att_dun_no;
821 l_reg_rec.att_email_address := l_imp_rec.att_email_address;
822 l_reg_rec.att_employee_total := l_imp_rec.att_employee_total;
823 l_reg_rec.att_fy_end_month := l_imp_rec.att_fy_end_month;
824 l_reg_rec.att_floor := l_imp_rec.att_floor;
825 l_reg_rec.att_gsa_indicator_flag := l_imp_rec.att_gsa_indicator_flag;
826 l_reg_rec.att_house_number := l_imp_rec.att_house_number;
827 l_reg_rec.att_identifying_address_flag := l_imp_rec.att_identifying_address_flag;
828 l_reg_rec.att_jgzz_fiscal_code := l_imp_rec.att_jgzz_fiscal_code;
829 l_reg_rec.att_job_title := l_imp_rec.att_job_title;
830 l_reg_rec.att_last_order_date := l_imp_rec.att_last_order_date;
831 l_reg_rec.att_org_legal_status := l_imp_rec.att_legal_status;
832 l_reg_rec.att_line_of_business := l_imp_rec.att_line_of_business;
833 l_reg_rec.att_mission_statement := l_imp_rec.att_mission_statement;
834 l_reg_rec.att_org_name_phonetic := l_imp_rec.att_org_name_phoenetic;
835 l_reg_rec.att_overseas_address_flag := l_imp_rec.att_overseas_address_flag;
836 l_reg_rec.att_name_suffix := l_imp_rec.att_name_suffix;
837 l_reg_rec.att_phone_area_code := l_imp_rec.att_phone_area_code;
838 l_reg_rec.att_phone_country_code := l_imp_rec.att_phone_country_code;
839 l_reg_rec.att_phone_extension := l_imp_rec.att_phone_extension;
840 l_reg_rec.att_phone_number := l_imp_rec.att_phone_number;
841 l_reg_rec.att_postal_code := l_imp_rec.att_postal_code;
842 l_reg_rec.att_postal_plus4_code := l_imp_rec.att_postal_plus4_code;
843 l_reg_rec.att_po_box_no := l_imp_rec.att_po_box_no;
844 l_reg_rec.att_province := l_imp_rec.att_province;
845 l_reg_rec.att_rural_route_no := l_imp_rec.att_rural_route_no;
846 l_reg_rec.att_rural_route_type := l_imp_rec.att_rural_route_type;
847 l_reg_rec.att_secondary_suffix_element := l_imp_rec.att_secondary_suffix_element;
848 l_reg_rec.att_sic_code := l_imp_rec.att_sic_code;
849 l_reg_rec.att_sic_code_type := l_imp_rec.att_sic_code_type;
850 l_reg_rec.att_site_use_code := l_imp_rec.att_site_use_code;
851 l_reg_rec.att_state := l_imp_rec.att_state;
852 l_reg_rec.att_street := l_imp_rec.att_street;
853 l_reg_rec.att_street_number := l_imp_rec.att_street_number;
854 l_reg_rec.att_street_suffix := l_imp_rec.att_street_suffix;
855 l_reg_rec.att_suite := l_imp_rec.att_suite;
856 l_reg_rec.att_tax_name := l_imp_rec.att_tax_name;
857 l_reg_rec.att_tax_reference := l_imp_rec.att_tax_reference;
858 l_reg_rec.att_timezone := l_imp_rec.att_timezone;
859 l_reg_rec.att_total_no_of_orders := l_imp_rec.att_total_no_of_orders;
860 l_reg_rec.att_total_order_amount := l_imp_rec.att_total_order_amount;
861 l_reg_rec.att_year_established := l_imp_rec.att_year_establised;
862 l_reg_rec.att_url := l_imp_rec.att_url;
863 l_reg_rec.att_survey_notes := l_imp_rec.att_servey_notes;
864 l_reg_rec.att_contact_me_flag := l_imp_rec.att_contact_me_flag;
865 l_reg_rec.att_email_ok_flag := l_imp_rec.att_email_ok_flag;
866 -- soagrawa 27-feb-2003 bug# 2824593
867 IF l_upd_flag IS NOT null
868 THEN
872 ELSIF l_upd_flag = 'N'
869 IF l_upd_flag = 'Y'
870 THEN
871 l_reg_rec.update_reg_rec := 'Y';
873 THEN
874 l_reg_rec.update_reg_rec := 'C';
875 END IF;
876 END IF;
877
878 AMS_Registrants_PUB.Register( p_api_version_number => 1.0
879 , p_init_msg_list => FND_API.G_FALSE
880 , p_commit => FND_API.G_FALSE
881 , x_return_status => l_return_status
882 , x_msg_count => l_msg_count
883 , x_msg_data => l_msg_data
884 , p_reg_det_rec => l_reg_rec
885 -- soagrawa 30-jan-2003 bug# 2769511
886 -- , p_owner_user_id => -1 -- what do we do with this?
887 , p_owner_user_id => ams_utility_pvt.get_resource_id(FND_GLOBAL.user_id)
888 , p_application_id => 530
889 , x_confirm_code => l_conf_code
890 );
891 l_processed_rows := l_processed_rows+1;
892 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
893 l_failed_rows := l_failed_rows+1;
894 END IF;
895
896
897 -- BEGIN
898 update_imp_src_line_rec( p_imp_src_id => l_imp_rec.import_source_line_id
899 , p_imp_hdr_id => p_list_header_id
900 , p_return_status => l_return_status
901 , p_msg_data => l_msg_data
902 , p_msg_count => l_msg_count -- Added by ptendulk on 21-Dec-2002
903 , p_out_status => l_out_status
904 );
905 -- soagrawa added out status on 03-feb-2003 for error threshold
906 IF (l_out_status = FND_API.G_RET_STS_ERROR) THEN
907 RETURN;
908 END IF;
909
910 -- EXCEPTION
911 -- WHEN error_threshold_exc THEN
912 -- RAISE error_threshold_exc;
913 -- END;
914
915 fetch c_get_lines
916 into l_imp_rec;
917 l_cnt2 := l_cnt2 + 1;
918 END LOOP; -- Inner LOOP for line rec
919 CLOSE c_get_lines;
920 END IF;
921 update_imp_hdr_rec( p_imp_hdr_id => p_list_header_id
922 , p_processed_rows => l_processed_rows
923 , p_failed_rows => l_failed_rows
924 );
925
926 END IF;
927 IF (p_list_header_id is null)
928 THEN
929 CLOSE c_get_header_id;
930 ELSE
931 CLOSE c_get_row_metadata;
932 END IF;
933 END IF;
934 errbuf := SUBSTR(FND_MESSAGE.GET,1,240);
935 retcode := 0;
936 RETURN;
937
938
939 --EXCEPTION
940
941 -- WHEN error_threshold_exc THEN
942 -- RETURN;
943
944 END LoadProcess;
945
946 -- soagrawa added out status on 03-feb-2003 for error threshold
947 PROCEDURE update_imp_src_line_rec(p_imp_src_id IN NUMBER
948 , p_imp_hdr_id IN NUMBER
949 , p_return_status IN VARCHAR2
950 , p_msg_data IN VARCHAR2
951 , p_msg_count IN NUMBER
952 , p_out_status OUT NOCOPY VARCHAR2 )
953 IS
954 l_msg_count NUMBER;
955 l_return_status_log varchar2(30);
956 l_used_by varchar2(30);
957 l_msg_data varchar2(2000);
958 l_return_status_ec varchar2(1);
959 l_msg_count_ec number;
960 l_msg_data_ec varchar2(2000);
961 l_tmp_var VARCHAR2(2000);
962 -- error_threshold_exc exception;
963 BEGIN
964 p_out_status := FND_API.g_ret_sts_success;
965 l_used_by := 'IMPH'; --'EVENT_REG_IMPORT';
966 --DBMS_OUTPUT.put_line('Inside update_imp_src_line_rec' || p_return_status || ' ' || p_imp_src_id );
967 IF (p_return_status = FND_API.G_RET_STS_SUCCESS) THEN
968 --FND_FILE.PUT_LINE(FND_FILE.LOG,'Record proceesed sucessfully = ' || p_imp_src_id );
969 l_msg_data := 'Record proceesed sucessfully ';
970 Ams_Utility_PVT.Create_Log (
971 x_return_status => l_return_status_log,
972 p_arc_log_used_by => l_used_by,
973 p_log_used_by_id => p_imp_hdr_id,
974 p_msg_data => l_msg_data
975 );
976 UPDATE ams_imp_source_lines
977 SET import_successful_flag = 'Y',
978 load_status = 'SUCCESS' -- This line is added by ptendulk on 13-Jun-02
979 WHERE import_source_line_id = p_imp_src_id;
980 ELSE
984 /*
981 -- The following code is modified by ptendulk on 21-Dec-2002 to capture error in one variable
982 -- instead of stack and then send it to error_capture process.
983 --FND_FILE.PUT_LINE(FND_FILE.LOG,'Record Not proceesed = ' || p_imp_src_id );
985 l_msg_data := 'Processing failed for the record whose sourceline Id is ' || p_imp_src_id || '. Check the log table for detail error message';
986 Ams_Utility_PVT.Create_Log (
987 x_return_status => l_return_status_log,
988 p_arc_log_used_by => l_used_by,
989 p_log_used_by_id => p_imp_hdr_id,
990 p_msg_data => l_msg_data
991 );
992
993 AMS_List_Import_PUB.error_capture( p_api_version => 1.0
994 , x_return_status => l_return_status_ec
995 , x_msg_count => l_msg_count_ec
996 , x_msg_data => l_msg_data_ec
997 , p_import_list_header_id => p_imp_hdr_id
998 , p_import_source_line_id => p_imp_src_id
999 , p_imp_xml_element_id => null
1000 , p_imp_xml_attribute_id => null
1001 , p_field_name => null
1002 , p_error_text => l_msg_data
1003 );
1004 --FND_FILE.PUT_LINE(FND_FILE.LOG,'check the log table for detail error message ');
1005 l_msg_count := FND_MSG_PUB.count_msg;
1006 FOR i IN 1..FND_MSG_PUB.count_msg LOOP
1007 l_msg_data := FND_MSG_PUB.get(i, FND_API.G_FALSE);
1008 AMS_List_Import_PUB.error_capture( p_api_version => 1.0
1009 , x_return_status => l_return_status_ec
1010 , x_msg_count => l_msg_count_ec
1011 , x_msg_data => l_msg_data_ec
1012 , p_import_list_header_id => p_imp_hdr_id
1013 , p_import_source_line_id => p_imp_src_id
1014 , p_imp_xml_element_id => null
1015 , p_imp_xml_attribute_id => null
1016 , p_field_name => null
1017 , p_error_text => l_msg_data
1018 ); */
1019 /*
1020 Ams_Utility_PVT.Create_Log (
1021 x_return_status => l_return_status_log,
1022 p_arc_log_used_by => l_used_by,
1023 p_log_used_by_id => p_imp_hdr_id,
1024 p_msg_data => l_msg_data
1025 );
1026 END LOOP;
1027 FND_MSG_PUB.initialize;
1028 */
1029
1030
1031 FOR i IN 1..p_msg_count LOOP
1032 -- soagrawa 17-feb-2003 now passing index to fnd msg pub get for bug# 2769511
1033 l_tmp_var := fnd_msg_pub.get(i, p_encoded => fnd_api.g_false);
1034 l_msg_data := SUBSTR((l_msg_data || (i ||'. ')|| l_tmp_var),1,2000);
1035 END LOOP;
1036 AMS_List_Import_PUB.error_capture( p_api_version => 1.0
1037 , x_return_status => l_return_status_ec
1038 , x_msg_count => l_msg_count_ec
1039 , x_msg_data => l_msg_data_ec
1040 , p_import_list_header_id => p_imp_hdr_id
1041 , p_import_source_line_id => p_imp_src_id
1042 , p_imp_xml_element_id => null
1043 , p_imp_xml_attribute_id => null
1044 , p_field_name => null
1045 , p_error_text => l_msg_data
1046 );
1047
1048 -- soagrawa added out status on 03-feb-2003 for error threshold
1049 IF l_return_status_ec = FND_API.G_RET_STS_ERROR THEN
1050 p_out_status := FND_API.G_RET_STS_ERROR;
1051 END IF;
1052
1053 UPDATE ams_imp_source_lines
1054 SET import_successful_flag = 'N',
1055 load_status = 'ERROR', -- This line is added by ptendulk on 13-Jun-2002
1056 import_failure_reason = p_msg_data
1057 where import_source_line_id = p_imp_src_id;
1058 FND_MSG_PUB.initialize;
1059 END IF;
1060 END;
1061
1062 PROCEDURE update_imp_hdr_rec(p_imp_hdr_id IN NUMBER
1063 , p_processed_rows IN NUMBER
1064 , p_failed_rows IN NUMBER)
1065 IS
1066 l_return_status VARCHAR2(1);
1067 BEGIN
1068 --FND_FILE.PUT_LINE(FND_FILE.LOG,'No RECORD TO Processed = ' || p_processed_rows );
1069 Ams_Utility_PVT.Create_Log (
1070 x_return_status => l_return_status,
1071 p_arc_log_used_by => G_ARC_IMPORT_HEADER,
1072 p_log_used_by_id => p_imp_hdr_id,
1073 p_msg_data => '----No RECORD TO Processed = ' || p_processed_rows
1074 );
1075 IF (p_failed_rows > 0) THEN
1076 --FND_FILE.PUT_LINE(FND_FILE.LOG,'No of Failed Records' || p_failed_rows || '----');
1077 Ams_Utility_PVT.Create_Log (
1078 x_return_status => l_return_status,
1079 p_arc_log_used_by => G_ARC_IMPORT_HEADER,
1080 p_log_used_by_id => p_imp_hdr_id,
1081 p_msg_data => 'No of Failed Records' || p_failed_rows
1082 );
1083 --FND_FILE.PUT_LINE(FND_FILE.LOG,'check the log table for detail error message');
1084 END IF;
1085 --DBMS_OUTPUT.put_line('Inside update_imp_hdr_line_rec');
1086 UPDATE ams_imp_list_headers_all
1087 SET processed_rows = p_processed_rows,
1088 number_of_failed_records = p_failed_rows
1089 WHERE import_list_header_id = p_imp_hdr_id;
1090 END;
1091
1092
1093 END ams_imp_reg_detail_pvt;