[Home] [Help]
PACKAGE BODY: APPS.HZ_IMP_LOAD_CPT_PKG
Source
1 PACKAGE BODY HZ_IMP_LOAD_CPT_PKG AS
2 /* $Header: ARHLCPTB.pls 120.43 2011/09/22 08:22:58 vsegu ship $ */
3
4
5 g_debug_count NUMBER := 0;
6 --g_debug BOOLEAN := FALSE;
7
8 l_batch_id BATCH_ID;
9 l_cp_orig_system ORIG_SYSTEM;
10 l_cp_orig_system_reference ORIG_SYSTEM_REFERENCE;
11 l_party_orig_system ORIG_SYSTEM;
12 l_party_orig_system_reference ORIG_SYSTEM_REFERENCE;
13 l_site_orig_system ORIG_SYSTEM;
14 l_site_orig_system_reference ORIG_SYSTEM_REFERENCE;
15 l_insert_update_flag INSERT_UPDATE_FLAG;
16 l_contact_point_type CONTACT_POINT_TYPE;
17 l_contact_point_purpose CONTACT_POINT_PURPOSE;
18 l_edi_ece_tp_location_code EDI_ECE_TP_LOCATION_CODE;
19 l_edi_id_number EDI_ID_NUMBER;
20 l_edi_payment_format EDI_PAYMENT_FORMAT;
21 l_edi_payment_method EDI_PAYMENT_METHOD;
22 l_edi_remittance_instruction EDI_REMITTANCE_INSTRUCTION;
23 l_edi_remittance_method EDI_REMITTANCE_METHOD;
24 l_edi_tp_header_id EDI_TP_HEADER_ID;
25 l_edi_transaction_handling EDI_TRANSACTION_HANDLING;
26 l_eft_printing_program_id EFT_PRINTING_PROGRAM_ID;
27 l_eft_swift_code EFT_SWIFT_CODE;
28 l_eft_transmission_program_id EFT_TRANSMISSION_PROGRAM_ID;
29 l_eft_user_number EFT_USER_NUMBER;
30 l_email_address EMAIL_ADDRESS;
31 l_email_format EMAIL_FORMAT;
32 l_phone_area_code PHONE_AREA_CODE;
33 l_phone_country_code PHONE_COUNTRY_CODE;
37 l_raw_phone_number RAW_PHONE_NUMBER;
34 l_phone_extension PHONE_EXTENSION;
35 l_phone_line_type PHONE_LINE_TYPE;
36 l_phone_number PHONE_NUMBER;
38 l_phone_calling_calendar PHONE_CALLING_CALENDAR;
39 l_telex_number TELEX_NUMBER;
40 l_timezone_id TIMEZONE_ID;
41 l_timezone_code TIMEZONE_CODE;
42 l_url URL;
43 l_web_type WEB_TYPE;
44 l_attribute_category ATTRIBUTE_CATEGORY;
45 l_attribute1 ATTRIBUTE;
46 l_attribute2 ATTRIBUTE;
47 l_attribute3 ATTRIBUTE;
48 l_attribute4 ATTRIBUTE;
49 l_attribute5 ATTRIBUTE;
50 l_attribute6 ATTRIBUTE;
51 l_attribute7 ATTRIBUTE;
52 l_attribute8 ATTRIBUTE;
53 l_attribute9 ATTRIBUTE;
54 l_attribute10 ATTRIBUTE;
55 l_attribute11 ATTRIBUTE;
56 l_attribute12 ATTRIBUTE;
57 l_attribute13 ATTRIBUTE;
58 l_attribute14 ATTRIBUTE;
59 l_attribute15 ATTRIBUTE;
60 l_attribute16 ATTRIBUTE;
61 l_attribute17 ATTRIBUTE;
62 l_attribute18 ATTRIBUTE;
63 l_attribute19 ATTRIBUTE;
64 l_attribute20 ATTRIBUTE;
65 l_interface_status INTERFACE_STATUS;
66 l_action_flag ACTION_FLAG;
67 l_error_id ERROR_ID;
68 l_dqm_action_flag DQM_ACTION_FLAG;
69 l_dup_within_int_flag DUP_WITHIN_INT_FLAG;
70 l_party_id PARTY_ID;
71 l_party_site_id PARTY_SITE_ID;
72 -- l_stage_cp_id STAGE_CP_ID;
73 l_created_by_module CREATED_BY_MODULE;
74 l_owner_table_name OWNER_TABLE_NAME;
75 l_owner_table_id OWNER_TABLE_ID;
76 l_contact_point_id CONTACT_POINT_ID;
77 l_owner_table_error FLAG_ERROR;
78 l_action_mismatch_error FLAG_ERROR;
79 l_contact_point_type_error LOOKUP_ERROR;
80 l_cpt_type_updatable_error LOOKUP_ERROR;
81 l_cp_purpose_web_err LOOKUP_ERROR;
82 l_cp_purpose_error LOOKUP_ERROR;
83 l_edi_id_number_error FLAG_ERROR;
84 l_email_address_error FLAG_ERROR;
85 l_email_format_error LOOKUP_ERROR;
86 l_phone_country_code_error LOOKUP_ERROR;
87 l_phone_line_type_error LOOKUP_ERROR;
88 l_phone_number_error FLAG_ERROR;
89 l_raw_phone_number_error FLAG_ERROR;
90 l_telex_number_error FLAG_ERROR;
91 l_timezone_error LOOKUP_ERROR;
92 l_url_error FLAG_ERROR;
93 l_web_type_error FLAG_ERROR;
94 l_error_flag FLAG_ERROR;
95 l_primary_flag FLAG_ERROR;
96
97 l_error_party_id PARTY_ID;
98 l_error_cpt_id CONTACT_POINT_ID;
99 l_error_cpt_type CONTACT_POINT_TYPE;
100 l_update_party_id PARTY_ID;
101 l_update_cpt_id CONTACT_POINT_ID;
102 l_update_cpt_type CONTACT_POINT_TYPE;
103
104 l_contact_point_type_errors LOOKUP_ERROR;
105 l_status_error LOOKUP_ERROR;
106 l_primary_flag_error LOOKUP_ERROR;
107 l_phone_lone_type_error LOOKUP_ERROR;
108 l_contact_point_purpose_error LOOKUP_ERROR;
109 l_orig_system_ref_upd_error LOOKUP_ERROR;
110
111 l_createdby_errors LOOKUP_ERROR;
112
113
114 l_flex_val_errors NUMBER_COLUMN;
115 l_dss_security_errors FLAG_COLUMN;
116 l_new_osr_exists_flag FLAG_ERROR;
117 l_old_cp_orig_system_ref ORIG_SYSTEM_REFERENCE;
118
119 l_third_party_update_error FLAG_ERROR; /* bug 4079902 */
120
121 l_creation_date DATE;
122 l_user_id NUMBER;
123 l_last_update_date DATE;
124 l_last_updated_by NUMBER;
125 l_last_update_login NUMBER;
126 l_program_id NUMBER;
127 l_program_application_id NUMBER;
128 l_request_id NUMBER;
129 l_program_update_date DATE;
130 l_sysdate DATE;
131 l_rerun_flag varchar2(1);
132 l_content_source_type varchar2(100);
133 l_actual_content_source varchar2(100);
134
135 /* For updating error_id in interface table in bulk */
136 l_int_error_row_id ROWID := ROWID();
137 l_int_error_id ERROR_ID := ERROR_ID();
138
139 /* For inserting into hz_imp_errors in bulk */
140 l_err_error_id ERROR_ID := ERROR_ID();
141 l_err_message_name ERROR_MESSAGE_NAME := ERROR_MESSAGE_NAME();
142 l_token1 ERROR_MESSAGE_TOKEN := ERROR_MESSAGE_TOKEN();
143 l_errm varchar2(100);
144 l_row_id ROWID;
145 l_osr_error_flag FLAG_COLUMN;
146 --l_primary_flag FLAG_ERROR;
147 -- l_status FLAG_COLUMN;
148
149
150 /* Keep track of rows that do not get inserted or updated successfully.
151 Those are the rows that have some validation or DML errors.
152 Use this when inserting into or updating other tables so that we
153 do not need to check all the validation arrays. */
154 l_num_row_processed NUMBER_COLUMN;
155
156 PROCEDURE validate_desc_flexfield(p_validation_date IN DATE) IS
157
158 -- l_validation_date DATE := P_DML_RECORD.SYSDATE;
159 l_flex_exists VARCHAR2(1);
160 CURSOR desc_flex_exists IS
161 SELECT 'Y'
162 FROM fnd_descriptive_flexs
163 WHERE application_id = 222
164 AND descriptive_flexfield_name = 'RA_PHONES_HZ';
165
166 BEGIN
167 /*
168 OPEN desc_flex_exists;
169 FETCH desc_flex_exists INTO l_flex_exists;
170 IF desc_flex_exists%NOTFOUND THEN
171 CLOSE desc_flex_exists;
172 -- Error out all flexfield validation entries as flexfield doesn't exist
173 FOR i IN 1..l_cp_orig_system_reference.count LOOP
174 l_flex_val_errors(i) := 1;
175 END LOOP;
176 return;
177 END IF;
178 CLOSE desc_flex_exists;
179 */
180
181 FOR i IN 1..l_contact_point_id.count LOOP
182
183 FND_FLEX_DESCVAL.set_context_value(l_attribute_category(i));
184
188 FND_FLEX_DESCVAL.set_column_value('ATTRIBUTE4', l_attribute4(i));
185 FND_FLEX_DESCVAL.set_column_value('ATTRIBUTE1', l_attribute1(i));
186 FND_FLEX_DESCVAL.set_column_value('ATTRIBUTE2', l_attribute2(i));
187 FND_FLEX_DESCVAL.set_column_value('ATTRIBUTE3', l_attribute3(i));
189 FND_FLEX_DESCVAL.set_column_value('ATTRIBUTE5', l_attribute5(i));
190 FND_FLEX_DESCVAL.set_column_value('ATTRIBUTE6', l_attribute6(i));
191 FND_FLEX_DESCVAL.set_column_value('ATTRIBUTE7', l_attribute7(i));
192 FND_FLEX_DESCVAL.set_column_value('ATTRIBUTE8', l_attribute8(i));
193 FND_FLEX_DESCVAL.set_column_value('ATTRIBUTE9', l_attribute9(i));
194 FND_FLEX_DESCVAL.set_column_value('ATTRIBUTE10', l_attribute10(i));
195 FND_FLEX_DESCVAL.set_column_value('ATTRIBUTE11', l_attribute11(i));
196 FND_FLEX_DESCVAL.set_column_value('ATTRIBUTE12', l_attribute12(i));
197 FND_FLEX_DESCVAL.set_column_value('ATTRIBUTE13', l_attribute13(i));
198 FND_FLEX_DESCVAL.set_column_value('ATTRIBUTE14', l_attribute14(i));
199 FND_FLEX_DESCVAL.set_column_value('ATTRIBUTE15', l_attribute15(i));
200 FND_FLEX_DESCVAL.set_column_value('ATTRIBUTE16', l_attribute16(i));
201 FND_FLEX_DESCVAL.set_column_value('ATTRIBUTE17', l_attribute17(i));
202 FND_FLEX_DESCVAL.set_column_value('ATTRIBUTE18', l_attribute18(i));
203 FND_FLEX_DESCVAL.set_column_value('ATTRIBUTE19', l_attribute19(i));
204 FND_FLEX_DESCVAL.set_column_value('ATTRIBUTE20', l_attribute20(i));
205
206 IF (NOT FND_FLEX_DESCVAL.validate_desccols(
207 'AR',
208 'RA_PHONES_HZ',
209 'V',
210 p_validation_date)) THEN
211 l_flex_val_errors(i) := 1;
212 END IF;
213
214 END LOOP;
215
216 END validate_desc_flexfield;
217
218 FUNCTION validate_desc_flexfield_f(
219 p_attr_category IN VARCHAR2,
220 p_attr1 IN VARCHAR2,
221 p_attr2 IN VARCHAR2,
222 p_attr3 IN VARCHAR2,
223 p_attr4 IN VARCHAR2,
224 p_attr5 IN VARCHAR2,
225 p_attr6 IN VARCHAR2,
226 p_attr7 IN VARCHAR2,
227 p_attr8 IN VARCHAR2,
228 p_attr9 IN VARCHAR2,
229 p_attr10 IN VARCHAR2,
230 p_attr11 IN VARCHAR2,
231 p_attr12 IN VARCHAR2,
232 p_attr13 IN VARCHAR2,
233 p_attr14 IN VARCHAR2,
234 p_attr15 IN VARCHAR2,
235 p_attr16 IN VARCHAR2,
236 p_attr17 IN VARCHAR2,
237 p_attr18 IN VARCHAR2,
238 p_attr19 IN VARCHAR2,
239 p_attr20 IN VARCHAR2,
240 p_validation_date IN DATE
241 ) RETURN VARCHAR2 IS
242 BEGIN
243
244 FND_FLEX_DESCVAL.set_context_value(p_attr_category);
245
246 FND_FLEX_DESCVAL.set_column_value('ATTRIBUTE1', p_attr1);
247 FND_FLEX_DESCVAL.set_column_value('ATTRIBUTE2', p_attr2);
248 FND_FLEX_DESCVAL.set_column_value('ATTRIBUTE3', p_attr3);
249 FND_FLEX_DESCVAL.set_column_value('ATTRIBUTE4', p_attr4);
250 FND_FLEX_DESCVAL.set_column_value('ATTRIBUTE5', p_attr5);
251 FND_FLEX_DESCVAL.set_column_value('ATTRIBUTE6', p_attr6);
252 FND_FLEX_DESCVAL.set_column_value('ATTRIBUTE7', p_attr7);
253 FND_FLEX_DESCVAL.set_column_value('ATTRIBUTE8', p_attr8);
254 FND_FLEX_DESCVAL.set_column_value('ATTRIBUTE9', p_attr9);
255 FND_FLEX_DESCVAL.set_column_value('ATTRIBUTE10', p_attr10);
256 FND_FLEX_DESCVAL.set_column_value('ATTRIBUTE11', p_attr11);
257 FND_FLEX_DESCVAL.set_column_value('ATTRIBUTE12', p_attr12);
258 FND_FLEX_DESCVAL.set_column_value('ATTRIBUTE13', p_attr13);
259 FND_FLEX_DESCVAL.set_column_value('ATTRIBUTE14', p_attr14);
260 FND_FLEX_DESCVAL.set_column_value('ATTRIBUTE15', p_attr15);
261 FND_FLEX_DESCVAL.set_column_value('ATTRIBUTE16', p_attr16);
262 FND_FLEX_DESCVAL.set_column_value('ATTRIBUTE17', p_attr17);
263 FND_FLEX_DESCVAL.set_column_value('ATTRIBUTE18', p_attr18);
264 FND_FLEX_DESCVAL.set_column_value('ATTRIBUTE19', p_attr19);
265 FND_FLEX_DESCVAL.set_column_value('ATTRIBUTE20', p_attr20);
266
267 IF (FND_FLEX_DESCVAL.validate_desccols(
268 'AR',
269 'RA_PHONES_HZ',
270 'V',
271 p_validation_date)) THEN
272 return 'Y';
273 ELSE
274 return null;
275 END IF;
276
277 END validate_desc_flexfield_f;
278
279 PROCEDURE validate_DSS_security IS
280 dss_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
281 dss_msg_count NUMBER := 0;
282 dss_msg_data VARCHAR2(2000):= null;
283 BEGIN
284 /* Check if the DSS security is granted to the user.
285 Only check for update. */
286 FOR i IN 1..l_cp_orig_system_reference.count LOOP
287 l_dss_security_errors(i) :=
288 hz_dss_util_pub.test_instance(
289 p_operation_code => 'UPDATE',
290 p_db_object_name => 'HZ_CONTACT_POINTS',
291 p_instance_pk1_value => l_contact_point_id(i),
292 p_instance_pk2_value => null,
293 p_instance_pk3_value => null,
294 p_instance_pk4_value => null,
295 p_instance_pk5_value => null,
296 p_user_name => fnd_global.user_name,
297 x_return_status => dss_return_status,
298 x_msg_count => dss_msg_count,
299 x_msg_data => dss_msg_data);
300 END LOOP;
301
302 END validate_DSS_security;
303
304 procedure report_errors (
305 P_DML_RECORD IN HZ_IMP_LOAD_WRAPPER.DML_RECORD_TYPE
306 ,P_ACTION IN VARCHAR2
307 ,P_DML_EXCEPTION IN VARCHAR2 ) IS
308
309 -- local variables
310 n NUMBER := 1; -- Counter if # of exceptions and validation errors
311 -- across all errored interface records.
312 num_exp NUMBER; -- variable to store # of DML exceptions occured
313 exp_ind NUMBER := 1; -- temp variable to store expection index.
317 l_debug_prefix VARCHAR2(30) := '';
314
315 -- For updating error_id in interface table in bulk
316 l_exception_exists FLAG_ERROR;
318 BEGIN
319 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
320 hz_utility_v2pub.debug(p_message=>'CPT:report_errors()+',
321 p_prefix=>l_debug_prefix,
322 p_msg_level=>fnd_log.level_procedure);
323 END IF;
324 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
325 hz_utility_v2pub.debug(p_message=>'CPT:no of recs processed:'||l_contact_point_id.count,
326 p_prefix =>l_debug_prefix,
327 p_msg_level=>fnd_log.level_statement);
328 END IF;
329 --
330 -- Flow
331 -- what is the flow?
332 --
333 -- 1. if there are no rows processed then return gracefully
334 -- 2.
335
336 -- 1. if there are no rows processed return.
337 l_num_row_processed := NUMBER_COLUMN(); -- initalizing
338 num_exp := SQL%BULK_EXCEPTIONS.COUNT;
339 l_num_row_processed.extend(l_contact_point_id.count);
340 l_exception_exists := FLAG_ERROR();
341 l_exception_exists.extend(l_contact_point_id.count);
342
343 IF l_contact_point_id.count = 0 THEN
344 IF fnd_log.level_exception>=fnd_log.g_current_runtime_level THEN
345 hz_utility_v2pub.debug(p_message=>'CPT:# no rows to process - exiting',
346 p_prefix=>'WARNING',
347 p_msg_level=>fnd_log.level_exception);
348 END IF;
349 RETURN ;
350 END IF;
351
352 /* Note: For Credit Ratings update would not cause following errors:
353 1. dup val exception
354 2. missing_parent exception.
355 other entities copying the code may need to take care of that.
356
357 IF g_debug THEN
358 hz_utility_v2pub.debug('CPT:report_errors:initializing collections');
359 END IF;
360 */
361
362 -- l_num_row_processed := null; -- is this needed ?
363 -- for all the rows that must be processed
364 -- check the BULK_ROWCOUNT exception to see
365 -- if there are any error while doing DML.
366 -- If so identify the row.
367
368 FOR k IN 1.. l_contact_point_id.count LOOP
369 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
370 hz_utility_v2pub.debug(p_message=>'CPT:bfr bulk row excep check',
371 p_prefix =>l_debug_prefix,
372 p_msg_level=>fnd_log.level_statement);
373 END IF;
374 -- check the bulk row exception for each row
375
376 IF (SQL%BULK_ROWCOUNT(k) = 0) THEN
377 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
378 hz_utility_v2pub.debug(p_message=>'CPT:DML fails at:'||k,
379 p_prefix =>'ERROR',
380 p_msg_level=>fnd_log.level_error);
381 END IF;
382 -- Check for any exceptions during DML
383 l_num_row_processed(k) := 0;
384 IF P_DML_EXCEPTION = 'Y' THEN
385 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
386 hz_utility_v2pub.debug(p_message=>'CPT:DML exception occured',
387 p_prefix =>'ERROR',
388 p_msg_level=>fnd_log.level_error);
389 END IF;
390
391 -- determine if exception is at this index
392 FOR i IN exp_ind..num_exp LOOP
393
394 IF SQL%BULK_EXCEPTIONS(i).ERROR_INDEX = k THEN
395 -- if the error index is same as the interface rec, process
396 -- the exception.
397 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
398 hz_utility_v2pub.debug(p_message=>'CPT:excep code:'||SQL%BULK_EXCEPTIONS(i).ERROR_CODE,
399 p_prefix=>'ERROR',
400 p_msg_level=>fnd_log.level_error);
401 END IF;
402 IF SQL%BULK_EXCEPTIONS(i).ERROR_CODE <> 1 THEN
403 -- In case of any other exceptions, raise apps exception
404 -- to be caught in load_creditrtaings()
405 l_exception_exists(k) := 'Y';
406 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
407 l_errm := SQLERRM(-SQL%BULK_EXCEPTIONS(i).ERROR_CODE);
408 hz_utility_v2pub.debug(p_message=>'CPT:exception is:'||l_errm,
409 p_prefix=>'ERROR',
410 p_msg_level=>fnd_log.level_error);
411 END IF;
412 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
413 END IF; -- error code 1 check ends
414
415 -- increment the total errors count and go to next exception
416 n := n+1;
417 exp_ind := n+1;
418
419 ELSE
420 -- if the error index is not the current interface row, exit
421 IF fnd_log.level_exception>=fnd_log.g_current_runtime_level THEN
422 hz_utility_v2pub.debug(p_message=>'CPT:error index <> current int row',
423 p_prefix=>'WARNING',
424 p_msg_level=>fnd_log.level_exception);
425 END IF;
426 EXIT;
427 END IF; -- end of error index check
428 END LOOP; -- end of exceptions loop.
429 ELSE
430 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
431 hz_utility_v2pub.debug(p_message=>'CPT:No DML exception',
432 p_prefix =>l_debug_prefix,
433 p_msg_level=>fnd_log.level_statement);
434 END IF;
435 END IF; -- end of DML exception check
436 ELSE
437 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
438 hz_utility_v2pub.debug(p_message=>'CPT:record#'||k||' processed successfully ',
439 p_prefix =>l_debug_prefix,
440 p_msg_level=>fnd_log.level_statement);
441 hz_utility_v2pub.debug(p_message=>'CPT:SQL%BULK_ROWCOUNT(k):'||SQL%BULK_ROWCOUNT(k),
442 p_prefix =>l_debug_prefix,
443 p_msg_level=>fnd_log.level_statement);
444 END IF;
445 l_num_row_processed(k) := 1;
446 END IF; -- end of SQL%BULK_ROWCOUNT(k) = 0 check
447 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
451 END IF;
448 hz_utility_v2pub.debug(p_message=>'CPT:----------------------',
449 p_prefix =>l_debug_prefix,
450 p_msg_level=>fnd_log.level_statement);
452 END LOOP; -- end of loop for l_contact_point_id.count
453
454 BEGIN -- anonymous block to insert into hz_imp_errors
455 forall j in 1..l_contact_point_id.count
456 insert into hz_imp_tmp_errors
457 ( request_id, batch_id, int_row_id,
458 interface_table_name, error_id,
459 creation_date, created_by, last_update_date,
460 last_updated_by, last_update_login,
461 program_application_id, program_id,
462 program_update_date,/*MISSING_PARENT_FLAG, */
463 MISSING_PARENT_FLAG, --Bug No: 3443866
464 ACTION_MISMATCH_FLAG, OTHER_EXCEP_FLAG,
465 e1_flag, e2_flag,
466 e3_flag, e4_flag,
467 e5_flag, e6_flag,
468 e7_flag, e8_flag,
469 e9_flag, e10_flag,
470 e11_flag, e12_flag,
471 e13_flag, e14_flag,
472 e15_flag, --only for insert
473 e16_flag,
474 e17_flag, e18_flag,
475 e19_flag, e20_flag,
476 e21_flag
477 )(
478 select
479 P_DML_RECORD.REQUEST_ID,
480 P_DML_RECORD.BATCH_ID,
481 l_row_id(j),
482 'HZ_IMP_CONTACTPTS_INT',
483 HZ_IMP_ERRORS_S.NextVal,
484 P_DML_RECORD.SYSDATE,
485 P_DML_RECORD.USER_ID,
486 P_DML_RECORD.SYSDATE,
487 P_DML_RECORD.USER_ID,
488 P_DML_RECORD.LAST_UPDATE_LOGIN,
489 P_DML_RECORD.PROGRAM_APPLICATION_ID,
490 P_DML_RECORD.PROGRAM_ID,
491 P_DML_RECORD.SYSDATE,/*l_owner_table_error(j), */
492 'Y',--Bug No: 3443866
493 l_action_mismatch_error(j),
494 l_exception_exists(j),
495 'Y',
496 l_cp_purpose_error(j),
497 l_edi_id_number_error(j),
498 l_email_address_error(j),
499 l_email_format_error(j),
500 l_phone_country_code_error(j),
501 l_phone_line_type_error(j),
502 l_phone_number_error(j),
503 l_telex_number_error(j),
504 l_timezone_error(j),
505 l_url_error(j),
506 l_web_type_error(j),
507 l_flex_val_errors(j),
508 'Y',
509 'Y',
510 'Y',
511 l_cpt_type_updatable_error(j),
512 decode(l_dss_security_errors(j),FND_API.G_FALSE,
513 nvl2(l_party_orig_system_reference(j),'P',nvl2(l_site_orig_system_reference(j),'S','P')),'Y'),
514 l_cp_purpose_web_err(j),
515 l_third_party_update_error(j),
516 l_createdby_errors(j)
517 from dual
518 where l_num_row_processed(j) = 0
519 );
520
521 EXCEPTION
522 WHEN OTHERS THEN
523 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
524 hz_utility_v2pub.debug(p_message=>'CPT:while inserting into errors tbl got others excep',
525 p_prefix=>'SQL ERROR',
526 p_msg_level=>fnd_log.level_error);
527 hz_utility_v2pub.debug(p_message=>sqlerrm,
528 p_prefix=>'SQL ERROR',
529 p_msg_level=>fnd_log.level_error);
530 END IF;
531 END; -- anonymous block end
532
533 --Start Bug No:3387220
534 /*
535 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
536 hz_utility_v2pub.debug(p_message=>'CPT:Update errored records in interface table',
537 p_prefix =>l_debug_prefix,
538 p_msg_level=>fnd_log.level_statement);
539 END IF;
540 -- Update for success cases, on in the case of reruns
541 if (P_DML_RECORD.RERUN = 'Y') THEN
542 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
543 hz_utility_v2pub.debug(p_message=>'CPT:In case of rerun, update sucessful interface records',
544 p_prefix =>l_debug_prefix,
545 p_msg_level=>fnd_log.level_statement);
546 END IF;
547 ForAll j in 1..l_num_row_processed.count
548 update HZ_IMP_CONTACTPTS_INT
549 set error_id = null,
550 interface_status = null,
551 insert_update_flag = P_ACTION
552 where
553 l_num_row_processed(j) = 1
554 and rowid = l_row_id(j);
555 end if;
556 --------------
557 */--End of Bug No:3387220
558
559 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
560 hz_utility_v2pub.debug(p_message=>'CPT:report_errors()-',
561 p_prefix=>l_debug_prefix,
562 p_msg_level=>fnd_log.level_procedure);
563 END IF;
564
565 EXCEPTION
566 WHEN OTHERS THEN
567 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
568 hz_utility_v2pub.debug(p_message=>'CPT:in report_errors() expection block',
569 p_prefix=>'ERROR',
570 p_msg_level=>fnd_log.level_error);
571 hz_utility_v2pub.debug(p_message=>sqlerrm,
572 p_prefix=>'ERROR',
573 p_msg_level=>fnd_log.level_error);
574 END IF;
575 END report_errors;
576
577 PROCEDURE populate_error_table(
578 P_DML_RECORD IN HZ_IMP_LOAD_WRAPPER.DML_RECORD_TYPE,
579 P_DUP_VAL_EXP IN VARCHAR2,
580 P_SQL_ERRM IN VARCHAR2 ) IS
581
582 dup_val_exp_val VARCHAR2(1) := null;
583 other_exp_val VARCHAR2(1) := 'Y';
584 l_debug_prefix VARCHAR2(30) := '';
585 BEGIN
586
587 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
588 hz_utility_v2pub.debug(p_message=>'CPT: populate_error_table()+',
589 p_prefix=>l_debug_prefix,
590 p_msg_level=>fnd_log.level_procedure);
591 END IF;
592 IF(P_DUP_VAL_EXP = 'Y') then
593 other_exp_val := null;
597 p_prefix=>'ERROR',
594 IF(instr(P_SQL_ERRM, '_U1')<>0) THEN
595 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
596 hz_utility_v2pub.debug(p_message=>'CPT: HZ_CONTACT_POINTS_U1 violated',
598 p_msg_level=>fnd_log.level_error);
599 END IF;
600 dup_val_exp_val := 'A';
601 END IF;
602 END IF;
603
604 insert into hz_imp_tmp_errors
605 (
606 request_id,
607 batch_id,
608 int_row_id,
609 interface_table_name,
610 error_id,
611 creation_date,
612 created_by,
613 last_update_date,
614 last_updated_by,
615 last_update_login,
616 program_application_id,
617 program_id,
618 program_update_date,
619 e1_flag,
620 e2_flag,e3_flag, e4_flag,
621 e5_flag, e6_flag, e7_flag,
622 e8_flag, e9_flag, e10_flag,
623 e11_flag, e12_flag, e13_flag,
624 e14_flag, e15_flag, e16_flag,
625 e17_flag,e18_flag,e19_flag,e20_flag,
626 e21_flag,
627 ACTION_MISMATCH_FLAG,
628 DUP_VAL_IDX_EXCEP_FLAG,
629 OTHER_EXCEP_FLAG
630 )
631 (
632 select P_DML_RECORD.REQUEST_ID,
633 P_DML_RECORD.BATCH_ID,
634 p_sg.int_row_id,
635 'HZ_IMP_CONTACTPTS_INT',
636 HZ_IMP_ERRORS_S.NextVal,
637 P_DML_RECORD.SYSDATE,
638 P_DML_RECORD.USER_ID,
639 P_DML_RECORD.SYSDATE,
640 P_DML_RECORD.USER_ID,
641 P_DML_RECORD.LAST_UPDATE_LOGIN,
642 P_DML_RECORD.PROGRAM_APPLICATION_ID,
643 P_DML_RECORD.PROGRAM_ID,
644 P_DML_RECORD.SYSDATE,
645 'Y','Y','Y','Y','Y',
646 'Y','Y','Y','Y','Y',
647 'Y','Y','Y','Y','Y',
648 'Y','Y','Y',
649 'Y','Y','Y','Y',
650 dup_val_exp_val,
651 other_exp_val
652 from hz_imp_contactpts_int int,hz_imp_contactpts_sg p_sg
653 where int.rowid = p_sg.int_row_id
654 and p_sg.action_flag = 'I'
655 and p_sg.batch_id = P_DML_RECORD.BATCH_ID
656 and int.party_orig_system = P_DML_RECORD.OS
657 and int.party_orig_system_reference
658 between P_DML_RECORD.FROM_OSR and P_DML_RECORD.TO_OSR
659 );
660
661 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
662 hz_utility_v2pub.debug(p_message=>'CPT:populate_error_table()-',
663 p_prefix=>l_debug_prefix,
664 p_msg_level=>fnd_log.level_procedure);
665 END IF;
666 END populate_error_table;
667
668
669 --------------------------------------
670 -- private procedures and functions
671 --------------------------------------
672 --------------------------------------
673 /*PROCEDURE enable_debug IS
674 BEGIN
675 g_debug_count := g_debug_count + 1;
676
677 IF g_debug_count = 1 THEN
678 IF fnd_profile.value('HZ_API_FILE_DEBUG_ON') = 'Y' OR
679 fnd_profile.value('HZ_API_DBMS_DEBUG_ON') = 'Y'
680 THEN
681 hz_utility_v2pub.enable_debug;
682 g_debug := TRUE;
683 END IF;
684 END IF;
685 END enable_debug; -- end procedure
686 */
687 --------------------------------------
688 --------------------------------------
689 /*PROCEDURE disable_debug IS
690 BEGIN
691
692 IF g_debug THEN
693 g_debug_count := g_debug_count - 1;
694 IF g_debug_count = 0 THEN
695 hz_utility_v2pub.disable_debug;
696 g_debug := FALSE;
697 END IF;
698 END IF;
699
700 END disable_debug;
701 */
702 /********************************************************************************
703 *
704 * process_insert_contactpoints
705 *
706 ********************************************************************************/
707
708 PROCEDURE process_insert_contactpoints
709 (
710 P_DML_RECORD IN HZ_IMP_LOAD_WRAPPER.DML_RECORD_TYPE
711 ,x_return_status OUT NOCOPY VARCHAR2
712 ,x_msg_count OUT NOCOPY NUMBER
713 ,x_msg_data OUT NOCOPY VARCHAR2
714 )
715 as
716 c_handle_insert RefCurType;
717 primary_flag_err_cursor RefCurType;
718 de_norm_cursor RefCurType;
719
720 l_insert_sql varchar2(25000) :=
721 'BEGIN insert all
722 when (action_mismatch_error is not null
723 and flex_val_errors is not null
724 and cpt_null_error is not null
725 and contact_point_type_error is not null
726 and cp_purpose_web_error is not null
727 and cp_purpose_error is not null
728 and edi_id_number_error is not null
729 and email_address_error is not null
730 and email_format_error is not null
731 and phone_country_code_error is not null
732 and phone_line_type_error is not null
733 and phone_number_error is not null
734 and telex_number_error is not null
735 and timezone_id_error is not null
736 and url_error is not null
737 and web_type_error is not null
738 --and owner_table_error is not null --Bug No:3443866
739 and owner_table_error =''Y'' --Bug No:3443866
740 and error_flag is null
741 and cpt_addr_osr_mismatch_err is not null
742 and createdby_error is not null
743 ) then
744 into hz_contact_points (
745 actual_content_source, application_id, content_source_type,
746 created_by, creation_date, last_updated_by,
747 last_update_date, last_update_login, program_application_id,
748 program_id, program_update_date, request_id,
749 contact_point_id, contact_point_type, status,
750 owner_table_name, owner_table_id, primary_flag,
751 orig_system_reference, attribute_category, attribute1,
752 attribute2, attribute3, attribute4,
756 attribute14, attribute15, attribute16,
753 attribute5, attribute6, attribute7,
754 attribute8, attribute9, attribute10,
755 attribute11, attribute12, attribute13,
757 attribute17, attribute18, attribute19,
758 attribute20, edi_transaction_handling, edi_id_number,
759 edi_payment_method, edi_payment_format, edi_remittance_method,
760 edi_remittance_instruction, edi_tp_header_id, edi_ece_tp_location_code,
761 email_format, email_address, phone_calling_calendar,
762 timezone_id, phone_area_code, phone_country_code,
763 phone_number, phone_extension, phone_line_type,
764 telex_number, web_type, url,
765 raw_phone_number, object_version_number, created_by_module,
766 contact_point_purpose, eft_transmission_program_id, eft_printing_program_id,
767 eft_user_number, eft_swift_code)
768 values (
769 :l_actual_content_src, :l_application_id, :l_content_source,
770 :l_user_id, :l_sysdate, :l_user_id, :l_sysdate, -- l_created_by, l_creation_date, l_last_updated_by,l_last_update_date
771 :l_last_update_login, :l_program_application_id,
772 :l_program_id, :l_sysdate, -- l_program_update_date,
773 :l_request_id,
774 contact_point_id, contact_point_type, ''A'',
775 owner_table_name, owner_table_id, primary_flag,
776 cp_orig_system_reference, attribute_category, attribute1,
777 attribute2, attribute3, attribute4,
778 attribute5, attribute6, attribute7,
779 attribute8, attribute9, attribute10,
780 attribute11, attribute12, attribute13,
781 attribute14, attribute15, attribute16,
782 attribute17, attribute18, attribute19,
783 attribute20, edi_transaction_handling, edi_id_number,
784 edi_payment_method, edi_payment_format, edi_remittance_method,
785 edi_remittance_instruction, edi_tp_header_id, edi_ece_tp_location_code,
786 email_format, email_address, phone_calling_calendar,
787 timezone_id, phone_area_code, phone_country_code,
788 phone_number, phone_extension, phone_line_type,
789 telex_number, web_type, url,
790 raw_phone_number,1,nvl(nullif(created_by_module,:G_MISS_CHAR),''HZ_IMPORT''),
791 contact_point_purpose, eft_transmission_program_id, eft_printing_program_id,
792 eft_user_number, eft_swift_code)
793 into hz_orig_sys_references (
794 application_id, created_by, creation_date,
795 last_updated_by, last_update_date, last_update_login,
796 orig_system_ref_id, orig_system, orig_system_reference,
797 owner_table_name, owner_table_id, status,
798 start_date_active, created_by_module, object_version_number,party_id,
799 request_id, program_application_id, program_id, program_update_date)
800 values (
801 :l_application_id,
802 :l_user_id, :l_sysdate,:l_user_id, :l_sysdate, -- l_created_by, l_creation_date, l_last_updated_by,l_last_update_date
803 :l_last_update_login,
804 hz_orig_system_ref_s.nextval, cp_orig_system, cp_orig_system_reference,
805 ''HZ_CONTACT_POINTS'', contact_point_id, ''A'',
806 :l_sysdate, created_by_module, 1,party_id,
807 :l_request_id, :l_program_application_id, :l_program_id, :l_sysdate)
808 else
809 into hz_imp_tmp_errors (
810 created_by, creation_date, last_updated_by,
811 last_update_date, last_update_login,
812 program_application_id,
813 program_id, program_update_date,
814 error_id, batch_id, request_id,
815 int_row_id, interface_table_name, e1_flag,
816 e2_flag, e3_flag, e4_flag,
817 e5_flag, e6_flag, e7_flag,
818 e8_flag, e9_flag, e10_flag,
819 e11_flag, e12_flag, e13_flag,
820 /* e14_flag ,*/ e15_flag, e16_flag,
821 e19_flag,
822 MISSING_PARENT_FLAG,ACTION_MISMATCH_FLAG,
823 e17_flag,e18_flag,e20_flag,
824 e21_flag)
825 values (
826 :l_user_id, :l_sysdate, :l_user_id,
827 :l_sysdate, :l_last_update_login,
828 :l_program_application_id,
829 :l_program_id, :l_sysdate,
830 HZ_IMP_ERRORS_S.nextval, :l_batch_id, :l_request_id,
831 row_id, ''HZ_IMP_CONTACTPTS_INT'',contact_point_type_error,
832 cp_purpose_error, edi_id_number_error,email_address_error,
833 email_format_error, phone_country_code_error,phone_line_type_error,
834 phone_number_error, telex_number_error,timezone_id_error,
835 url_error, web_type_error,flex_val_errors,
836 /* cpt_party_osr_mismatch_err ,*/ cpt_null_error,cpt_addr_osr_mismatch_err,
837 cp_purpose_web_error,
838 owner_table_error,action_mismatch_error,
839 ''Y'',''Y'',''Y'',
840 createdby_error)
841 select row_id, cp_orig_system, cp_orig_system_reference,
842 party_orig_system, party_orig_system_reference, site_orig_system,
843 site_orig_system_reference, insert_update_flag, contact_point_type,
844 contact_point_purpose, edi_ece_tp_location_code, edi_id_number,
845 edi_payment_format, edi_payment_method, edi_remittance_instruction,
846 edi_remittance_method, edi_tp_header_id, edi_transaction_handling,
847 eft_printing_program_id, eft_swift_code, eft_transmission_program_id,
848 eft_user_number, email_address,
849 decode(contact_point_type,''EMAIL'',nvl(email_format,''MAILTEXT''),email_format) email_format,
850 phone_area_code, phone_country_code, phone_extension,
851 phone_line_type, phone_number, nvl(raw_phone_number,phone_area_code||''-''|| phone_number) raw_phone_number,
852 phone_calling_calendar, telex_number, timezone_id,
853 url, web_type, attribute_category,
854 attribute1, attribute2, attribute3,
855 attribute4, attribute5, attribute6,
856 attribute7, attribute8, attribute9,
857 attribute10, attribute11, attribute12,
858 attribute13, attribute14, attribute15,
859 attribute16, attribute17, attribute18,
860 attribute19, attribute20, interface_status,
861 action_flag, error_id, dqm_action_flag,
865 --party_id,
862 dup_within_int_flag, party_id, party_site_id,
863 nvl(created_by_module,''HZ_IMPORT'') created_by_module, owner_table_name, owner_table_id,
864 primary_flag, contact_point_id,
866 nvl2(nullif(insert_update_flag, action_flag), null, ''Y'') action_mismatch_error,
867 decode(:l_val_flex, ''Y'',
868 HZ_IMP_LOAD_CPT_PKG.validate_desc_flexfield_f(
869 attribute_category, attribute1, attribute2, attribute3, attribute4,
870 attribute5, attribute6, attribute7, attribute8, attribute9,
871 attribute10, attribute11, attribute12, attribute13, attribute14,
872 attribute15, attribute16, attribute17, attribute18, attribute19,
873 attribute20, :l_sysdate), ''T'') flex_val_errors,
874 ''T'' dss_security_errors,
875 nvl2(contact_point_type, ''Y'',null) cpt_null_error,
876 nvl2(contact_point_type_l, ''Y'',null) contact_point_type_error,
877 --decode(contact_point_type, null, ''Y'', ''WEB'', nvl2(contact_point_purpose, cp_purpose_web_l, ''Y''), nvl2(contact_point_purpose, cp_purpose_l, ''Y'')) cp_purpose_error,
878 decode(contact_point_type, ''WEB'', nvl2(contact_point_purpose, cp_purpose_web_l, ''Y''),''Y'') cp_purpose_web_error,
879 decode(contact_point_type, ''WEB'',''Y'',null,''Y'', nvl2(contact_point_purpose, cp_purpose_l, ''Y'')) cp_purpose_error,
880 decode(contact_point_type, ''EDI'', nvl2(edi_id_number, ''Y'', null), ''Y'') edi_id_number_error,
881 decode(contact_point_type, ''EMAIL'', nvl2(email_address, ''Y'', null), ''Y'') email_address_error,
882 decode(contact_point_type, ''EMAIL'', nvl2(email_format, email_format_l, ''Y''), ''Y'') email_format_error,
883 --decode(contact_point_type, ''PHONE'', nvl2(nullif(phone_country_code, pccl), null, ''Y''), ''Y'') phone_country_code_error,--3401319
884 decode(contact_point_type, ''PHONE'', pccl, ''Y'') phone_country_code_error,--3401319
885 decode(contact_point_type, ''PHONE'', nvl2(phone_line_type, phone_line_type_l, ''Y''), ''Y'') phone_line_type_error,
886 decode(contact_point_type, ''PHONE'', decode(phone_number,null, nvl2(raw_phone_number,''Y'', null),nvl2(raw_phone_number,null,''Y'')), ''Y'') phone_number_error,
887 /*decode(contact_point_type, ''PHONE'',
888 decode(phone_number, null, decode(raw_phone_number, null,null,:G_MISS_CHAR, null,''Y''),
889 :G_MISS_CHAR,decode(raw_phone_number,null,null,:G_MISS_CHAR, null,''Y''),
890 decode(raw_phone_number,null,''Y'',:G_MISS_CHAR,''Y'',null)), ''Y'') phone_number_error,*/
891 decode(contact_point_type, ''TLX'', nvl2(telex_number,''Y'',null), ''Y'') telex_number_error,
892 decode(contact_point_type, ''PHONE'', decode(timezone_code, null,''Y'',decode(timezone_id,null,null,''Y'')), ''Y'') timezone_id_error,
893 decode(contact_point_type, ''WEB'', nvl2(url,''Y'',null), ''Y'') url_error,
894 decode(contact_point_type, ''WEB'', nvl2(web_type,''Y'',null), ''Y'') web_type_error,
895 --nvl2(mosr_owner_table_id,''Y'',null) owner_table_error, --Bug No:3443866
896 nvl2(mosr_owner_table_id,nvl2(owner_table_id,''Y'',nvl2(site_orig_system_reference,''A'',''P'')),
897 nvl2(site_orig_system_reference,''A'',''P'')) owner_table_error, --Bug No:3443866
898 --owner_table_error should check for null owner_table_id as it will throw exception
899 decode(party_site_id,null,''Y'',nvl2(nullif(party_site_id,site_owner_table_id),null,''Y'')) cpt_addr_osr_mismatch_err,
900 --decode(party_id,null,''Y'',nvl2(nullif(party_id,party_owner_table_id),null,''Y'')) cpt_party_osr_mismatch_err,
901 --owner_table_error,
902 error_flag,
903 nvl2(created_by_module, createdby_l, ''Y'') createdby_error
904 from (
905 select /*+ leading(ps) use_nl(contact_point_type_l, email_format_l,
906 cp_purpose_l,cp_purpose_web_l, phone_line_type_l) */ pi.rowid row_id,
907 pi.cp_orig_system,
908 pi.cp_orig_system_reference,
909 pi.party_orig_system,
910 pi.party_orig_system_reference,
911 pi.site_orig_system,
912 pi.site_orig_system_reference,
913 nullif(pi.insert_update_flag, :G_MISS_CHAR) insert_update_flag,
914 nullif(pi.contact_point_type, :G_MISS_CHAR) contact_point_type,
915 nullif(pi.contact_point_purpose, :G_MISS_CHAR) contact_point_purpose,
916 nullif(pi.edi_ece_tp_location_code, :G_MISS_CHAR) edi_ece_tp_location_code,
917 nullif(pi.edi_id_number, :G_MISS_CHAR) edi_id_number,
918 nullif(pi.edi_payment_format, :G_MISS_CHAR) edi_payment_format,
919 nullif(pi.edi_payment_method, :G_MISS_CHAR) edi_payment_method,
920 nullif(pi.edi_remittance_instruction, :G_MISS_CHAR) edi_remittance_instruction,
921 nullif(pi.edi_remittance_method, :G_MISS_CHAR) edi_remittance_method,
922 nullif(pi.edi_tp_header_id, to_number(:G_MISS_NUM)) edi_tp_header_id,
923 nullif(pi.edi_transaction_handling, :G_MISS_CHAR) edi_transaction_handling,
924 nullif(pi.eft_printing_program_id, to_number(:G_MISS_NUM)) eft_printing_program_id,
925 nullif(pi.eft_swift_code, :G_MISS_CHAR) eft_swift_code,
926 nullif(pi.eft_transmission_program_id, to_number(:G_MISS_NUM)) eft_transmission_program_id,
927 nullif(pi.eft_user_number, :G_MISS_CHAR) eft_user_number,
928 substrb(nullif(pi.email_address, :G_MISS_CHAR),1,320) email_address,
929 nullif(pi.email_format, :G_MISS_CHAR) email_format,
930 nullif(pi.phone_area_code, :G_MISS_CHAR) phone_area_code,
931 nullif(pi.phone_country_code, :G_MISS_CHAR) phone_country_code,
932 nullif(pi.phone_extension, :G_MISS_CHAR) phone_extension,
933 nullif(pi.phone_line_type, :G_MISS_CHAR) phone_line_type,
934 nullif(pi.phone_number, :G_MISS_CHAR) phone_number,
935 nullif(pi.raw_phone_number, :G_MISS_CHAR) raw_phone_number,
936 nullif(pi.phone_calling_calendar, :G_MISS_CHAR) phone_calling_calendar,
937 nullif(pi.telex_number, :G_MISS_CHAR) telex_number,
938 nullif(pi.timezone_code, :G_MISS_CHAR) timezone_code,
939 ht.upgrade_tz_id timezone_id,
940 nullif(pi.url, :G_MISS_CHAR) url,
941 nullif(pi.web_type, :G_MISS_CHAR) web_type,
945 nullif(pi.attribute3, :G_MISS_CHAR) attribute3,
942 nullif(pi.attribute_category, :G_MISS_CHAR) attribute_category,
943 nullif(pi.attribute1, :G_MISS_CHAR) attribute1,
944 nullif(pi.attribute2, :G_MISS_CHAR) attribute2,
946 nullif(pi.attribute4, :G_MISS_CHAR) attribute4,
947 nullif(pi.attribute5, :G_MISS_CHAR) attribute5,
948 nullif(pi.attribute6, :G_MISS_CHAR) attribute6,
949 nullif(pi.attribute7, :G_MISS_CHAR) attribute7,
950 nullif(pi.attribute8, :G_MISS_CHAR) attribute8,
951 nullif(pi.attribute9, :G_MISS_CHAR) attribute9,
952 nullif(pi.attribute10, :G_MISS_CHAR) attribute10,
953 nullif(pi.attribute11, :G_MISS_CHAR) attribute11,
954 nullif(pi.attribute12, :G_MISS_CHAR) attribute12,
955 nullif(pi.attribute13, :G_MISS_CHAR) attribute13,
956 nullif(pi.attribute14, :G_MISS_CHAR) attribute14,
957 nullif(pi.attribute15, :G_MISS_CHAR) attribute15,
958 nullif(pi.attribute16, :G_MISS_CHAR) attribute16,
959 nullif(pi.attribute17, :G_MISS_CHAR) attribute17,
960 nullif(pi.attribute18, :G_MISS_CHAR) attribute18,
961 nullif(pi.attribute19, :G_MISS_CHAR) attribute19,
962 nullif(pi.attribute20, :G_MISS_CHAR) attribute20,
963 pi.interface_status,
964 ps.action_flag,
965 pi.error_id,
966 pi.dqm_action_flag,
967 pi.dup_within_int_flag,
968 ps.party_id,
969 ps.party_site_id,
970 ps.contact_point_id,
971 nvl(ps.primary_flag,''N'') primary_flag,
972 nullif(pi.created_by_module, :G_MISS_CHAR) created_by_module,
973 nvl2(ps.party_site_id, ''HZ_PARTY_SITES'', ''HZ_PARTIES'') owner_table_name,
974 nvl(ps.party_site_id, ps.party_id) owner_table_id,
975 nvl2(contact_point_type_l.lookup_code, ''Y'', null) contact_point_type_l,
976 nvl2(email_format_l.lookup_code, ''Y'', null) email_format_l,
977 nvl2(cp_purpose_l.lookup_code, ''Y'', null) cp_purpose_l,
978 nvl2(cp_purpose_web_l.lookup_code, ''Y'', null) cp_purpose_web_l,
979 nvl2(phone_line_type_l.lookup_code, ''Y'', null) phone_line_type_l,
980 --nvl2(hpc.phone_country_code, ''Y'', null) pccl,
981 --hpc.phone_country_code pccl,
982 nvl2(pi.phone_country_code, decode(tc.a, 1, ''Y''), ''Y'') pccl,
983 nvl2(createdby_l.lookup_code, ''Y'', null) createdby_l,
984 --nvl(mosr_site.owner_table_id,mosr_party.owner_table_id) mosr_owner_table_id, --Bug No:3443866
985 nvl2(pi.site_orig_system_reference,mosr_site.owner_table_id,hp.party_id) mosr_owner_table_id, --Bug No:3443866
986 /*(select ''Y''
987 from hz_orig_sys_references
988 where status = ''A''
989 and rownum = 1
990 and (orig_system, orig_system_reference, owner_table_name) in (
991 (pi.site_orig_system, pi.site_orig_system_reference,
992 ''HZ_PARTY_SITES''),
993 (pi.party_orig_system, pi.party_orig_system_reference,
994 ''HZ_PARTIES''))) owner_table_error, */
995 ps.error_flag,
996 hp.party_id party_owner_table_id,
997 mosr_site.owner_table_id site_owner_table_id
998 from hz_imp_contactpts_int pi,
999 hz_imp_contactpts_sg ps,
1000 --hz_orig_sys_references mosr_party,
1001 hz_parties hp,
1002 hz_orig_sys_references mosr_site,
1003 --(select distinct phone_country_code from hz_phone_country_codes) hpc,--3401319
1004 (select 0 a from dual union all select 1 a from dual) tc,--3401319
1005 fnd_timezones_b ht,
1006 fnd_lookup_values contact_point_type_l,
1007 fnd_lookup_values email_format_l,
1008 fnd_lookup_values cp_purpose_l,
1009 fnd_lookup_values cp_purpose_web_l,
1010 fnd_lookup_values phone_line_type_l,
1011 fnd_lookup_values createdby_l
1012
1013 where pi.rowid = ps.int_row_id
1014 and mosr_site.orig_system (+) = pi.site_orig_system
1015 and mosr_site.orig_system_reference (+) = pi.site_orig_system_reference
1016 and mosr_site.status (+) = ''A''
1017 and mosr_site.owner_table_name (+) = ''HZ_PARTY_SITES''
1018 and hp.party_id (+) = ps.party_id
1019 and hp.status (+) = ''A''
1020 and contact_point_type_l.lookup_code (+) = pi.contact_point_type
1021 and contact_point_type_l.lookup_type (+) = ''COMMUNICATION_TYPE''
1022 and contact_point_type_l.language (+) = userenv(''LANG'')
1023 and contact_point_type_l.view_application_id (+) = 222
1024 and contact_point_type_l.security_group_id (+) =
1025 fnd_global.lookup_security_group(''COMMUNICATION_TYPE'', 222)
1026 and cp_purpose_l.lookup_code (+) = pi.contact_point_purpose
1027 and cp_purpose_l.lookup_type (+) = ''CONTACT_POINT_PURPOSE''
1028 and cp_purpose_l.language (+) = userenv(''LANG'')
1029 and cp_purpose_l.view_application_id (+) = 222
1030 and cp_purpose_l.security_group_id (+) =
1031 fnd_global.lookup_security_group(''CONTACT_POINT_PURPOSE'', 222)
1032 and cp_purpose_web_l.lookup_code (+) = pi.contact_point_purpose
1033 and cp_purpose_web_l.lookup_type (+) = ''CONTACT_POINT_PURPOSE_WEB''
1034 and cp_purpose_web_l.language (+) = userenv(''LANG'')
1035 and cp_purpose_web_l.view_application_id (+) = 222
1036 and cp_purpose_web_l.security_group_id (+) =
1037 fnd_global.lookup_security_group(''CONTACT_POINT_PURPOSE_WEB'', 222)
1038 and email_format_l.lookup_code (+) = pi.email_format
1039 and email_format_l.lookup_type (+) = ''EMAIL_FORMAT''
1040 and email_format_l.language (+) = userenv(''LANG'')
1041 and email_format_l.view_application_id (+) = 222
1042 and email_format_l.security_group_id (+) =
1043 fnd_global.lookup_security_group(''EMAIL_FORMAT'', 222)
1044 and phone_line_type_l.lookup_code (+) = pi.phone_line_type
1045 and phone_line_type_l.lookup_type (+) = ''PHONE_LINE_TYPE''
1046 and phone_line_type_l.language (+) = userenv(''LANG'')
1047 and phone_line_type_l.view_application_id (+) = 222
1051 --and pi.phone_country_code = hpc.phone_country_code (+) --3401319
1048 and phone_line_type_l.security_group_id (+) =
1049 fnd_global.lookup_security_group(''PHONE_LINE_TYPE'', 222)
1050 and pi.timezone_code = ht.timezone_code (+)
1052 and tc.a = (select count(*) from hz_phone_country_codes hpc --3401319
1053 where pi.phone_country_code = hpc.phone_country_code
1054 and rownum < 2)
1055 and createdby_l.lookup_code (+) = pi.created_by_module
1056 and createdby_l.lookup_type (+) = ''HZ_CREATED_BY_MODULES''
1057 and createdby_l.language (+) = userenv(''LANG'')
1058 and createdby_l.view_application_id (+) = 222
1059 and createdby_l.security_group_id (+) =
1060 fnd_global.lookup_security_group(''HZ_CREATED_BY_MODULES'', 222)
1061 and ps.batch_id = :l_batch_id
1062 and ps.party_orig_system = :l_os
1063 and ps.party_orig_system_reference between :l_from_osr and :l_to_osr
1064 and ps.batch_mode_flag = :l_batch_mode_flag
1065 and ps.action_flag = ''I''';
1066
1067 l_where_first_run_sql varchar2(35) := ' AND pi.interface_status is null';
1068 l_where_rerun_sql varchar2(35) := ' AND pi.interface_status = ''C''';
1069
1070 l_where_enabled_lookup_sql varchar2(1500) :=
1071 ' AND ( contact_point_type_l.ENABLED_FLAG(+) = ''Y'' AND
1072 TRUNC(SYSDATE) BETWEEN
1073 TRUNC(NVL( contact_point_type_l.START_DATE_ACTIVE,SYSDATE ) ) AND
1074 TRUNC(NVL( contact_point_type_l.END_DATE_ACTIVE,SYSDATE ) ) )
1075 AND ( cp_purpose_l.ENABLED_FLAG(+) = ''Y'' AND
1076 TRUNC(SYSDATE) BETWEEN
1077 TRUNC(NVL( cp_purpose_l.START_DATE_ACTIVE,SYSDATE ) ) AND
1078 TRUNC(NVL( cp_purpose_l.END_DATE_ACTIVE,SYSDATE ) ) )
1079 AND ( cp_purpose_web_l.ENABLED_FLAG(+) = ''Y'' AND
1080 TRUNC(SYSDATE) BETWEEN
1081 TRUNC(NVL( cp_purpose_web_l.START_DATE_ACTIVE,SYSDATE ) ) AND
1082 TRUNC(NVL( cp_purpose_web_l.END_DATE_ACTIVE,SYSDATE ) ) )
1083 AND ( email_format_l.ENABLED_FLAG(+) = ''Y'' AND
1084 TRUNC(SYSDATE) BETWEEN
1085 TRUNC(NVL( email_format_l.START_DATE_ACTIVE,SYSDATE ) ) AND
1086 TRUNC(NVL( email_format_l.END_DATE_ACTIVE,SYSDATE ) ) )
1087 --AND ( ht.ENABLED_FLAG(+) = ''Y'') --Bug No:3398342
1088 AND ( phone_line_type_l.ENABLED_FLAG(+) = ''Y'' AND
1089 TRUNC(SYSDATE) BETWEEN
1090 TRUNC(NVL( phone_line_type_l.START_DATE_ACTIVE,SYSDATE ) ) AND
1091 TRUNC(NVL( phone_line_type_l.END_DATE_ACTIVE,SYSDATE ) ) )
1092 AND ( createdby_l.ENABLED_FLAG(+) = ''Y'' AND
1093 TRUNC(SYSDATE) BETWEEN
1094 TRUNC(NVL( createdby_l.START_DATE_ACTIVE,SYSDATE ) ) AND
1095 TRUNC(NVL( createdby_l.END_DATE_ACTIVE,SYSDATE ) ) )';
1096 l_end_sql VARCHAR2(10) := ' ); END;';
1097 l_final_sql VARCHAR2(32000);
1098 l_entity_attr_id NUMBER := null;
1099 l_dml_exception VARCHAR2(1) := 'N';
1100 l_debug_prefix VARCHAR2(30) := '';
1101
1102 BEGIN
1103 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
1104 hz_utility_v2pub.debug(p_message=>'CPT: process_insert_contactpoints (+)',
1105 p_prefix=>l_debug_prefix,
1106 p_msg_level=>fnd_log.level_procedure);
1107 END IF;
1108 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1109 hz_utility_v2pub.debug(p_message=>'CPT: RERUN:' || P_DML_RECORD.RERUN,
1110 p_prefix =>l_debug_prefix,
1111 p_msg_level=>fnd_log.level_statement);
1112 hz_utility_v2pub.debug(p_message=>'CPT: ALLOW_DISABLED_LOOKUP:' || P_DML_RECORD.ALLOW_DISABLED_LOOKUP,
1113 p_prefix =>l_debug_prefix,
1114 p_msg_level=>fnd_log.level_statement);
1115 END IF;
1116
1117 savepoint process_insert_cpt_pvt;
1118
1119 FND_MSG_PUB.initialize;
1120
1121 --Initialize API return status to success.
1122 x_return_status := FND_API.G_RET_STS_SUCCESS;
1123
1124
1125 IF P_DML_RECORD.ALLOW_DISABLED_LOOKUP = 'Y' THEN
1126 IF P_DML_RECORD.RERUN = 'N' THEN
1127 -- First run with disabled lookup
1128 l_final_sql := l_insert_sql || l_where_first_run_sql || l_end_sql;
1129 ELSE
1130 -- Re-run with disabled lookup
1131 l_final_sql := l_insert_sql || l_where_rerun_sql || l_end_sql;
1132 END IF;
1133 ELSE
1134 IF P_DML_RECORD.RERUN = 'N' THEN
1135 -- First run with enabled lookup
1136 l_final_sql := l_insert_sql || l_where_first_run_sql || l_where_enabled_lookup_sql || l_end_sql;
1137 ELSE
1138 -- Re-run with enabled lookup
1139 l_final_sql := l_insert_sql || l_where_rerun_sql || l_where_enabled_lookup_sql || l_end_sql;
1140 END IF;
1141 END IF;
1142
1143 EXECUTE IMMEDIATE l_final_sql using
1144 P_DML_RECORD.ACTUAL_CONTENT_SRC,
1145 P_DML_RECORD.APPLICATION_ID,
1146 'USER_ENTERED',--Bug No:3413574
1147 P_DML_RECORD.USER_ID,
1148 P_DML_RECORD.SYSDATE,
1149 P_DML_RECORD.LAST_UPDATE_LOGIN,
1150 P_DML_RECORD.PROGRAM_APPLICATION_ID,
1151 P_DML_RECORD.PROGRAM_ID,
1152 P_DML_RECORD.REQUEST_ID,
1153 P_DML_RECORD.GMISS_CHAR,
1154 P_DML_RECORD.BATCH_ID,
1155 P_DML_RECORD.FLEX_VALIDATION,
1156 P_DML_RECORD.GMISS_NUM,
1157 P_DML_RECORD.OS,
1158 P_DML_RECORD.FROM_OSR,
1159 P_DML_RECORD.TO_OSR,
1160 P_DML_RECORD.BATCH_MODE_FLAG;
1161
1162 FND_FILE.put_line(fnd_file.log, 'CPT:Rows inserted in MTI = ' || SQL%ROWCOUNT);
1163
1164 FND_FILE.PUT_LINE(FND_FILE.LOG, 'BATCH_ID = ' || P_DML_RECORD.BATCH_ID);
1165 FND_FILE.PUT_LINE(FND_FILE.LOG, 'OS = ' || P_DML_RECORD.OS);
1166 FND_FILE.PUT_LINE(FND_FILE.LOG, 'FROM_OSR = ' || P_DML_RECORD.FROM_OSR);
1167 FND_FILE.PUT_LINE(FND_FILE.LOG, 'TO_OSR = ' || P_DML_RECORD.TO_OSR);
1168 FND_FILE.PUT_LINE(FND_FILE.LOG, 'ACTUAL_CONTENT_SRC = ' || P_DML_RECORD.ACTUAL_CONTENT_SRC);
1169 FND_FILE.PUT_LINE(FND_FILE.LOG, 'RERUN = ' || P_DML_RECORD.RERUN);
1170 FND_FILE.PUT_LINE(FND_FILE.LOG, 'ERROR_LIMIT = ' || P_DML_RECORD.ERROR_LIMIT);
1171 FND_FILE.PUT_LINE(FND_FILE.LOG, 'BATCH_MODE_FLAG = ' || P_DML_RECORD.BATCH_MODE_FLAG);
1175 FND_FILE.PUT_LINE(FND_FILE.LOG, 'REQUEST_ID = ' || P_DML_RECORD.REQUEST_ID);
1172 FND_FILE.PUT_LINE(FND_FILE.LOG, 'USER_ID = ' || P_DML_RECORD.USER_ID);
1173
1174 FND_FILE.PUT_LINE(FND_FILE.LOG, 'SYSDATE = ' || to_char(P_DML_RECORD.SYSDATE, 'DD-MON-YYYY HH24:MI:SS'));
1176 FND_FILE.PUT_LINE(FND_FILE.LOG, 'GMISS_CHAR = ' || P_DML_RECORD.GMISS_CHAR);
1177 FND_FILE.PUT_LINE(FND_FILE.LOG, 'GMISS_NUM = ' || P_DML_RECORD.GMISS_NUM);
1178 FND_FILE.PUT_LINE(FND_FILE.LOG, 'FLEX_VALIDATION = ' || P_DML_RECORD.FLEX_VALIDATION);
1179 FND_FILE.PUT_LINE(FND_FILE.LOG, 'DSS_SECURITY = ' || P_DML_RECORD.DSS_SECURITY);
1180
1181 /* DE-NORM */
1182 /* for all the failed record of primary_flag = 'Y', update the party with */
1183 /* the next available contact point */
1184 --Bug 3978485: changed where condition to use request_id in HZ_IMP_TMP_ERRORS
1185 OPEN primary_flag_err_cursor FOR
1186 'select cpt_sg.party_id, int.contact_point_type,
1187 ( select hz_cpt.contact_point_id
1188 from hz_contact_points hz_cpt
1189 where hz_cpt.owner_table_id = cpt_sg.party_id
1190 and hz_cpt.owner_table_name= ''HZ_PARTIES''
1191 and hz_cpt.CONTACT_POINT_TYPE = int.contact_point_type
1192 and rownum = 1
1193 ) contact_point_id
1194 from HZ_IMP_TMP_ERRORS err_table,
1195 hz_imp_contactpts_int int,
1196 hz_imp_contactpts_sg cpt_sg
1197 where err_table.request_id = :request_id
1198 and cpt_sg.batch_id = :batch_id
1199 and cpt_sg.batch_mode_flag = :batch_mode_flag
1200 and err_table.interface_table_name = ''HZ_IMP_CONTACTPTS_INT''
1201 and cpt_sg.party_orig_system = :orig_system
1202 and cpt_sg.party_orig_system_reference between :from_osr and :to_osr
1203 and cpt_sg.primary_flag = ''Y''
1204 and cpt_sg.int_row_id = err_table.int_row_id
1205 and int.rowid = cpt_sg.int_row_id
1206 and int.contact_point_type in(''WEB'',''EMAIL'')
1207 and cpt_sg.action_flag = ''I'''
1208 using P_DML_RECORD.REQUEST_ID,P_DML_RECORD.BATCH_ID,
1209 P_DML_RECORD.BATCH_MODE_FLAG, P_DML_RECORD.OS,
1210 P_DML_RECORD.FROM_OSR, P_DML_RECORD.TO_OSR;
1211
1212 fetch primary_flag_err_cursor BULK COLLECT INTO
1213 l_error_party_id,l_error_cpt_type, l_error_cpt_id;
1214 close primary_flag_err_cursor;
1215 /* Start of bug 7383480
1216 forall i in 1..l_error_party_id.count
1217 update hz_parties hz_pty
1218 set (email_address,url ) =
1219 ( select email_address,url
1220 from hz_contact_points
1221 where contact_point_id = l_error_cpt_id(i)
1222 )
1223 where hz_pty.party_id = l_error_party_id(i);
1224 */
1225 forall i in 1..l_error_party_id.count
1226 update hz_parties hz_pty
1227 set email_address =
1228 ( select email_address
1229 from hz_contact_points
1230 where contact_point_id = l_error_cpt_id(i)
1231 and contact_point_type='EMAIL'
1232 )
1233 where hz_pty.party_id = l_error_party_id(i)
1234 and l_error_cpt_type(i)= 'EMAIL';
1235
1236 forall i in 1..l_error_party_id.count
1237 update hz_parties hz_pty
1238 set url =
1239 ( select url
1240 from hz_contact_points
1241 where contact_point_id = l_error_cpt_id(i)
1242 and contact_point_type='WEB'
1243 )
1244 where hz_pty.party_id = l_error_party_id(i)
1245 and l_error_cpt_type(i)= 'WEB';
1246 --end of bug 7383480
1247 forall i in 1..l_error_party_id.count
1248 update hz_contact_points
1249 set primary_flag = 'Y'
1250 where contact_point_id = l_error_cpt_id(i);
1251
1252 FND_FILE.put_line(fnd_file.log, 'CPT:Rows updated with primary flag = ' || l_error_party_id.count);
1253
1254 /* de-norm the primary contact point to parties */
1255 /* Note: for error case, the party with the id will just be not found */
1256 /* in update. Not necessary to filter out here. */
1257 OPEN de_norm_cursor FOR
1258 'select cpt_sg.party_id, cpt_sg.contact_point_id, int.contact_point_type
1259 from hz_imp_contactpts_int int,hz_imp_contactpts_sg cpt_sg
1260 where int.rowid = cpt_sg.int_row_id
1261 and int.contact_point_type in(''WEB'',''EMAIL'')
1262 and cpt_sg.batch_id = :batch_id
1263 and cpt_sg.batch_mode_flag = :batch_mode_flag
1264 and cpt_sg.party_orig_system = :orig_system
1265 and cpt_sg.party_orig_system_reference between :from_osr and :to_osr
1266 and cpt_sg.primary_flag = ''Y''
1267 and cpt_sg.action_flag = ''I''
1268 and cpt_sg.party_action_flag = ''U''
1269 and not exists (select tmp_err.INT_ROW_ID
1270 from hz_imp_tmp_errors tmp_err
1271 where tmp_err.INT_ROW_ID = int.rowid
1272 and tmp_err.INTERFACE_TABLE_NAME = ''HZ_IMP_CONTACTPTS_INT'')
1273 '
1274 using P_DML_RECORD.BATCH_ID,
1275 P_DML_RECORD.BATCH_MODE_FLAG, P_DML_RECORD.OS,
1276 P_DML_RECORD.FROM_OSR, P_DML_RECORD.TO_OSR;
1277
1278 fetch de_norm_cursor BULK COLLECT INTO
1279 l_update_party_id, l_update_cpt_id,l_update_cpt_type;
1280 close de_norm_cursor;
1281 /* Start of bug 7383480
1282 forall i in 1..l_update_party_id.count
1283 update hz_parties hz_pty
1284 set (email_address,url ) =
1285 ( select email_address,url
1286 from hz_contact_points
1287 where contact_point_id = l_update_cpt_id(i)
1288 )
1289 where hz_pty.party_id = l_update_party_id(i);
1290 */
1291 forall i in 1..l_update_party_id.count
1292 update hz_parties hz_pty
1293 set email_address =( select email_address
1294 from hz_contact_points
1295 where contact_point_id = l_update_cpt_id(i)
1296 and contact_point_type='EMAIL')
1297 where hz_pty.party_id = l_update_party_id(i)
1298 and l_update_cpt_type(i) = 'EMAIL' ;
1299
1300
1301 forall i in 1..l_update_party_id.count
1302 update hz_parties hz_pty
1306 and contact_point_type='WEB')
1303 set url =( select url
1304 from hz_contact_points
1305 where contact_point_id = l_update_cpt_id(i)
1307 where hz_pty.party_id = l_update_party_id(i)
1308 and l_update_cpt_type(i) = 'WEB' ;
1309 --End of bug 7383480
1310 FND_FILE.put_line(fnd_file.log, 'CPT:Denormalised contact point counts = ' || l_update_party_id.count);
1311
1312 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
1313 hz_utility_v2pub.debug(p_message=>'CPT: process_insert_contactpoints-',
1314 p_prefix=>l_debug_prefix,
1315 p_msg_level=>fnd_log.level_procedure);
1316 END IF;
1317
1318 EXCEPTION
1319
1320 WHEN FND_API.G_EXC_ERROR THEN
1321
1322 ROLLBACK TO process_insert_cpt_pvt;
1323 populate_error_table(P_DML_RECORD, 'Y', SQLERRM);
1324 x_return_status := FND_API.G_RET_STS_ERROR;
1325 FND_MSG_PUB.Count_And_Get(
1326 p_encoded => FND_API.G_FALSE,
1327 p_count => x_msg_count,
1328 p_data => x_msg_data);
1329
1330 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1331
1332 ROLLBACK TO process_insert_cpt_pvt;
1333 populate_error_table(P_DML_RECORD, 'N', SQLERRM);
1334 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1335 FND_MSG_PUB.Count_And_Get(
1336 p_encoded => FND_API.G_FALSE,
1337 p_count => x_msg_count,
1338 p_data => x_msg_data);
1339
1340 WHEN OTHERS THEN
1341
1342 ROLLBACK TO process_insert_cpt_pvt;
1343 FND_FILE.put_line(fnd_file.log,'Unexpected error occurs while loading contactpoints in process_insert_contactpoints');
1344 FND_FILE.put_line(fnd_file.log, SQLERRM);
1345 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1346 FND_MESSAGE.SET_NAME('AR', 'HZ_API_OTHERS_EXCEP');
1347 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
1348 FND_MSG_PUB.ADD;
1349 FND_MSG_PUB.Count_And_Get(
1350 p_encoded => FND_API.G_FALSE,
1351 p_count => x_msg_count,
1352 p_data => x_msg_data);
1353
1354 END process_insert_contactpoints;
1355
1356
1357 /********************************************************************************
1358 *
1359 * process_update_contactpoints
1360 *
1361 ********************************************************************************/
1362
1363 PROCEDURE process_update_contactpoints
1364 (
1365 P_DML_RECORD IN HZ_IMP_LOAD_WRAPPER.DML_RECORD_TYPE
1366 ,x_return_status OUT NOCOPY VARCHAR2
1367 ,x_msg_count OUT NOCOPY NUMBER
1368 ,x_msg_data OUT NOCOPY VARCHAR2
1369 )
1370 IS
1371 c_handle_update RefCurType;
1372 l_update_sql varchar2(12000) :=
1373 'SELECT /*+ leading(ps) use_nl(pi) rowid(pi) */
1374 pi.rowid row_id,
1375 pi.cp_orig_system,
1376 pi.cp_orig_system_reference,
1377 pi.party_orig_system,
1378 pi.party_orig_system_reference,
1379 pi.site_orig_system,
1380 pi.site_orig_system_reference,
1381 nullif(pi.insert_update_flag, :G_MISS_CHAR) insert_update_flag,
1382 nullif(pi.contact_point_type, :G_MISS_CHAR) contact_point_type,
1383 nullif(pi.contact_point_purpose, :G_MISS_CHAR) contact_point_purpose,
1384 nullif(pi.edi_ece_tp_location_code, :G_MISS_CHAR) edi_ece_tp_location_code,
1385 nullif(pi.edi_id_number, :G_MISS_CHAR) edi_id_number,
1386 nullif(pi.edi_payment_format, :G_MISS_CHAR) edi_payment_format,
1387 nullif(pi.edi_payment_method, :G_MISS_CHAR) edi_payment_method,
1388 nullif(pi.edi_remittance_instruction, :G_MISS_CHAR) edi_remittance_instruction,
1389 nullif(pi.edi_remittance_method, :G_MISS_CHAR) edi_remittance_method,
1390 nullif(pi.edi_tp_header_id, :G_MISS_NUM) edi_tp_header_id,
1391 nullif(pi.edi_transaction_handling, :G_MISS_CHAR) edi_transaction_handling,
1392 nullif(pi.eft_printing_program_id, :G_MISS_NUM) eft_printing_program_id,
1393 nullif(pi.eft_swift_code, :G_MISS_CHAR) eft_swift_code,
1394 nullif(pi.eft_transmission_program_id, :G_MISS_NUM) eft_transmission_program_id,
1395 nullif(pi.eft_user_number, :G_MISS_CHAR) eft_user_number,
1396 substrb(nullif(pi.email_address, :G_MISS_CHAR),1,320) email_address,
1397 nullif(pi.email_format, :G_MISS_CHAR) email_format,
1398 nullif(pi.phone_area_code, :G_MISS_CHAR) phone_area_code,
1399 nullif(pi.phone_country_code, :G_MISS_CHAR) phone_country_code,
1400 nullif(pi.phone_extension, :G_MISS_CHAR) phone_extension,
1401 nullif(pi.phone_line_type, :G_MISS_CHAR) phone_line_type,
1402 nullif(pi.phone_number, :G_MISS_CHAR) phone_number,
1403 nullif(pi.raw_phone_number, :G_MISS_CHAR) raw_phone_number,
1404 nullif(pi.phone_calling_calendar, :G_MISS_CHAR) phone_calling_calendar,
1405 nullif(pi.telex_number, :G_MISS_CHAR) telex_number,
1406 nullif(pi.timezone_code, :G_MISS_CHAR) timezone_code,
1407 ht.upgrade_tz_id timezone_id,
1408 nullif(pi.url, :G_MISS_CHAR) url,
1409 nullif(pi.web_type, :G_MISS_CHAR) web_type,
1410 nullif(pi.attribute_category, :G_MISS_CHAR) attribute_category,
1411 nullif(pi.attribute1, :G_MISS_CHAR) attribute1,
1412 nullif(pi.attribute2, :G_MISS_CHAR) attribute2,
1413 nullif(pi.attribute3, :G_MISS_CHAR) attribute3,
1414 nullif(pi.attribute4, :G_MISS_CHAR) attribute4,
1415 nullif(pi.attribute5, :G_MISS_CHAR) attribute5,
1416 nullif(pi.attribute6, :G_MISS_CHAR) attribute6,
1417 nullif(pi.attribute7, :G_MISS_CHAR) attribute7,
1418 nullif(pi.attribute8, :G_MISS_CHAR) attribute8,
1419 nullif(pi.attribute9, :G_MISS_CHAR) attribute9,
1420 nullif(pi.attribute10, :G_MISS_CHAR) attribute10,
1421 nullif(pi.attribute11, :G_MISS_CHAR) attribute11,
1422 nullif(pi.attribute12, :G_MISS_CHAR) attribute12,
1423 nullif(pi.attribute13, :G_MISS_CHAR) attribute13,
1427 nullif(pi.attribute17, :G_MISS_CHAR) attribute17,
1424 nullif(pi.attribute14, :G_MISS_CHAR) attribute14,
1425 nullif(pi.attribute15, :G_MISS_CHAR) attribute15,
1426 nullif(pi.attribute16, :G_MISS_CHAR) attribute16,
1428 nullif(pi.attribute18, :G_MISS_CHAR) attribute18,
1429 nullif(pi.attribute19, :G_MISS_CHAR) attribute19,
1430 nullif(pi.attribute20, :G_MISS_CHAR) attribute20,
1431 pi.interface_status,
1432 ps.action_flag,
1433 pi.error_id,
1434 pi.dqm_action_flag,
1435 pi.dup_within_int_flag,
1436 ps.party_id,
1437 ps.party_site_id,
1438 ps.contact_point_id,
1439 nvl(hp.primary_flag,''N'') primary_flag, /* Bug No: 3917168 */
1440 nullif(pi.created_by_module,:G_MISS_CHAR) created_by_module,
1441 nvl2(ps.party_site_id, ''HZ_PARTY_SITES'', ''HZ_PARTIES'') owner_table_name,
1442 nvl(ps.party_site_id, ps.party_id) owner_table_id,
1443 nvl2(nullif(pi.insert_update_flag, ps.action_flag),null, ''Y'') action_mismatch_error,
1444 decode(nullif(pi.contact_point_type,:G_MISS_CHAR),null,null,hp.contact_point_type,''Y'',null) cpt_type_updatable_error,
1445 /*decode(hp.CONTACT_POINT_TYPE,null,''Y'',''WEB'',nvl2(nullif(pi.CONTACT_POINT_PURPOSE,:G_MISS_CHAR),nvl2(cp_purpose_web_l.lookup_code,''Y'',null),''Y'' ),
1446 nvl2(nullif(pi.CONTACT_POINT_PURPOSE,:G_MISS_CHAR),nvl2(cp_purpose_l.lookup_code,''Y'',null),''Y'' ))cp_purpose_error,*/
1447 decode(hp.CONTACT_POINT_TYPE,null,''Y'',''WEB'',nvl2(nullif(pi.CONTACT_POINT_PURPOSE,:G_MISS_CHAR),nvl2(cp_purpose_web_l.lookup_code,''Y'',null),''Y'' ),''Y'' ) cp_purpose_web_err,
1448 decode(hp.CONTACT_POINT_TYPE,null,''Y'',''WEB'',''Y'',
1449 nvl2(nullif(pi.CONTACT_POINT_PURPOSE,:G_MISS_CHAR),nvl2(cp_purpose_l.lookup_code,''Y'',null),''Y'' ))cp_purpose_error,
1450 decode(hp.CONTACT_POINT_TYPE,''EDI'',nvl2(nullif(pi.EDI_ID_NUMBER,:G_MISS_CHAR),''Y'',null),''Y'') edi_id_number_error,
1451 decode(hp.CONTACT_POINT_TYPE,''EMAIL'',nvl2(nullif(pi.EMAIL_ADDRESS,:G_MISS_CHAR),''Y'',null),''Y'') email_address_error,
1452 decode(hp.CONTACT_POINT_TYPE,''EMAIL'',nvl2(nullif(pi.EMAIL_FORMAT,:G_MISS_CHAR),
1453 nvl2(email_format_l.lookup_code,''Y'',null),nvl2(hp.email_format,null,''Y'')),''Y'') email_format_error,
1454 --decode(hp.CONTACT_POINT_TYPE,''PHONE'',nvl2(nullif(pi.PHONE_COUNTRY_CODE,:G_MISS_CHAR),nvl2(hpc.phone_country_code,''Y'',null),''Y''),''Y'') phone_country_code_error,--3401319
1455 decode(hp.CONTACT_POINT_TYPE,''PHONE'',nvl2(nullif(pi.PHONE_COUNTRY_CODE,:G_MISS_CHAR),decode(tc.a, 1, ''Y''),''Y''),''Y'') phone_country_code_error,--3401319
1456 decode(hp.CONTACT_POINT_TYPE,''PHONE'',nvl2(nullif(pi.PHONE_LINE_TYPE,:G_MISS_CHAR),nvl2(phone_line_type_l.lookup_code,''Y'',null),null),''Y'') phone_line_type_error,
1457 decode(hp.contact_point_type, ''PHONE'',decode(nullif(pi.phone_number,:G_MISS_CHAR),null,nvl2(nullif(pi.raw_phone_number,:G_MISS_CHAR),''Y'',null),
1458 nvl2(nullif(pi.raw_phone_number,:G_MISS_CHAR),null,''Y'')), ''Y'') phone_number_error,
1459 decode(hp.CONTACT_POINT_TYPE,''TLX'',nvl2(nullif(pi.TELEX_NUMBER,:G_MISS_CHAR),''Y'',null),''Y'') telex_number_error,
1460 decode(hp.CONTACT_POINT_TYPE,''PHONE'',nvl2(nullif(pi.TIMEZONE_CODE,:G_MISS_CHAR),nvl2(ht.UPGRADE_TZ_ID,''Y'',null),''Y''),''Y'') timezone_error,
1461 decode(hp.CONTACT_POINT_TYPE,''WEB'',nvl2(nullif(pi.URL,:G_MISS_CHAR),''Y'', null),''Y'') url_error,
1462 decode(hp.CONTACT_POINT_TYPE,''WEB'',nvl2(nullif(pi.WEB_TYPE,:G_MISS_CHAR),''Y'', null),''Y'') web_type_error,
1463 --nvl2(nullif(pi.cp_orig_system_reference,hp.orig_system_reference),null,''Y'') orig_system_ref_upd_error,
1464 ps.error_flag,
1465 --mosr.owner_table_id owner_table_error,
1466 ''T'' dss_security_errors,
1467 0 flex_val_errors,
1468 ps.old_cp_orig_system_ref,
1469 ps.NEW_OSR_EXISTS_FLAG,
1470 /* Bug 4079902 */
1471 nvl2(nullif(hp.actual_content_source,:l_actual_content_source),
1472 nvl2(nullif(hos.orig_system_type,''PURCHASED''),''Y'',null),
1473 ''Y'') third_party_update_error,
1474 nvl2(nullif(pi.created_by_module,:GMISS_CHAR),
1475 nvl2(ps.new_osr_exists_flag,
1476 nvl2(nullif(pi.cp_orig_system_reference,ps.old_cp_orig_system_ref),
1477 createdby_l.lookup_code,
1478 ''Y''
1479 ),
1480 ''Y''
1481 ),
1482 ''Y'') createdby_error
1483
1484 FROM HZ_IMP_CONTACTPTS_INT pi, HZ_IMP_CONTACTPTS_SG ps,hz_contact_points hp,
1485 hz_orig_systems_b hos, /* Bug 4079902 */
1486 --(select distinct phone_country_code from hz_phone_country_codes) hpc,--3401319
1487 (select 0 a from dual union all select 1 a from dual) tc,--3401319
1488 fnd_timezones_b ht,fnd_lookup_values contact_point_type_l,
1489 fnd_lookup_values email_format_l,fnd_lookup_values cp_purpose_l,
1490 fnd_lookup_values cp_purpose_web_l,fnd_lookup_values phone_line_type_l,
1491 fnd_lookup_values createdby_l
1492 WHERE pi.rowid = ps.int_row_id
1493 and ps.contact_point_id = hp.contact_point_id
1494 and pi.CONTACT_POINT_TYPE = contact_point_type_l.lookup_code(+)
1495 and contact_point_type_l.lookup_type(+) = ''COMMUNICATION_TYPE''
1496 and contact_point_type_l.language (+) = userenv(''LANG'')
1497 and contact_point_type_l.view_application_id (+) = 222
1498 and contact_point_type_l.security_group_id (+) =
1499 fnd_global.lookup_security_group(''COMMUNICATION_TYPE'', 222)
1500 and pi.CONTACT_POINT_PURPOSE = cp_purpose_l.lookup_code(+)
1501 and cp_purpose_l.lookup_type(+) = ''CONTACT_POINT_PURPOSE''
1502 and cp_purpose_l.language (+) = userenv(''LANG'')
1503 and cp_purpose_l.view_application_id (+) = 222
1504 and cp_purpose_l.security_group_id (+) =
1505 fnd_global.lookup_security_group(''CONTACT_POINT_PURPOSE'', 222)
1506 and pi.CONTACT_POINT_PURPOSE = cp_purpose_web_l.lookup_code(+)
1510 and cp_purpose_web_l.security_group_id (+) =
1507 and cp_purpose_web_l.lookup_type(+) = ''CONTACT_POINT_PURPOSE_WEB''
1508 and cp_purpose_web_l.language (+) = userenv(''LANG'')
1509 and cp_purpose_web_l.view_application_id (+) = 222
1511 fnd_global.lookup_security_group(''CONTACT_POINT_PURPOSE_WEB'', 222)
1512 and pi.EMAIL_FORMAT = email_format_l.lookup_code(+)
1513 and email_format_l.lookup_type(+) = ''EMAIL_FORMAT''
1514 and email_format_l.language (+) = userenv(''LANG'')
1515 and email_format_l.view_application_id (+) = 222
1516 and email_format_l.security_group_id (+) =
1517 fnd_global.lookup_security_group(''EMAIL_FORMAT'', 222)
1518 and pi.PHONE_LINE_TYPE = phone_line_type_l.lookup_code(+)
1519 and phone_line_type_l.lookup_type(+) = ''PHONE_LINE_TYPE''
1520 and phone_line_type_l.language (+) = userenv(''LANG'')
1521 and phone_line_type_l.view_application_id (+) = 222
1522 and phone_line_type_l.security_group_id (+) =
1523 fnd_global.lookup_security_group(''PHONE_LINE_TYPE'', 222)
1524 and pi.TIMEZONE_CODE = ht.timezone_code(+)
1525 --and pi.PHONE_COUNTRY_CODE = hpc.phone_country_code(+)--3401319
1526 and createdby_l.lookup_code (+) = pi.created_by_module
1527 and createdby_l.lookup_type (+) = ''HZ_CREATED_BY_MODULES''
1528 and createdby_l.language (+) = userenv(''LANG'')
1529 and createdby_l.view_application_id (+) = 222
1530 and createdby_l.security_group_id (+) =
1531 fnd_global.lookup_security_group(''HZ_CREATED_BY_MODULES'', 222)
1532 and tc.a = (select count(*) from hz_phone_country_codes hpc --3401319
1533 where pi.phone_country_code = hpc.phone_country_code
1534 and rownum < 2)
1535 and ps.batch_id = :l_batch_id
1536 and ps.party_orig_system = :l_os
1537 and ps.party_orig_system_reference between :l_from_osr and :l_to_osr
1538 and ps.batch_mode_flag = :l_batch_mode_flag
1539 and ps.ACTION_FLAG = ''U''
1540 /* bug 4079902 */
1541 and hos.orig_system=hp.actual_content_source';
1542
1543 l_where_first_run_sql varchar2(35) := ' AND pi.interface_status is null';
1544 l_where_rerun_sql varchar2(35) := ' AND pi.interface_status = ''C''';
1545
1546 l_where_enabled_lookup_sql varchar2(3000) :=
1547 ' AND ( contact_point_type_l.ENABLED_FLAG(+) = ''Y'' AND
1548 TRUNC(SYSDATE) BETWEEN
1549 TRUNC(NVL( contact_point_type_l.START_DATE_ACTIVE,SYSDATE ) ) AND
1550 TRUNC(NVL( contact_point_type_l.END_DATE_ACTIVE,SYSDATE ) ) )
1551 AND ( cp_purpose_l.ENABLED_FLAG(+) = ''Y'' AND
1552 TRUNC(SYSDATE) BETWEEN
1553 TRUNC(NVL( cp_purpose_l.START_DATE_ACTIVE,SYSDATE ) ) AND
1554 TRUNC(NVL( cp_purpose_l.END_DATE_ACTIVE,SYSDATE ) ) )
1555 AND ( cp_purpose_web_l.ENABLED_FLAG(+) = ''Y'' AND
1556 TRUNC(SYSDATE) BETWEEN
1557 TRUNC(NVL( cp_purpose_web_l.START_DATE_ACTIVE,SYSDATE ) ) AND
1558 TRUNC(NVL( cp_purpose_web_l.END_DATE_ACTIVE,SYSDATE ) ) )
1559 AND ( email_format_l.ENABLED_FLAG(+) = ''Y'' AND
1560 TRUNC(SYSDATE) BETWEEN
1561 TRUNC(NVL( email_format_l.START_DATE_ACTIVE,SYSDATE ) ) AND
1562 TRUNC(NVL( email_format_l.END_DATE_ACTIVE,SYSDATE ) ) )
1563 AND ( phone_line_type_l.ENABLED_FLAG(+) = ''Y'' AND
1564 TRUNC(SYSDATE) BETWEEN
1565 TRUNC(NVL( phone_line_type_l.START_DATE_ACTIVE,SYSDATE ) ) AND
1566 TRUNC(NVL( phone_line_type_l.END_DATE_ACTIVE,SYSDATE ) ) )
1567 AND ( createdby_l.ENABLED_FLAG(+) = ''Y'' AND
1568 TRUNC(SYSDATE) BETWEEN
1569 TRUNC(NVL( createdby_l.START_DATE_ACTIVE,SYSDATE ) ) AND
1570 TRUNC(NVL( createdby_l.END_DATE_ACTIVE,SYSDATE ) ) )';
1571
1572 l_dml_exception varchar2(1) := 'N';
1573 l_final_sql VARCHAR2(32000);
1574 l_debug_prefix VARCHAR2(30) := '';
1575 BEGIN
1576 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
1577 hz_utility_v2pub.debug(p_message=>'CPT: process_update_contactpoints (+)',
1578 p_prefix=>l_debug_prefix,
1579 p_msg_level=>fnd_log.level_procedure);
1580 END IF;
1581 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1582 hz_utility_v2pub.debug(p_message=>'CPT: RERUN:' || P_DML_RECORD.RERUN,
1583 p_prefix =>l_debug_prefix,
1584 p_msg_level=>fnd_log.level_statement);
1585 hz_utility_v2pub.debug(p_message=>'CPT: ALLOW_DISABLED_LOOKUP:' || P_DML_RECORD.ALLOW_DISABLED_LOOKUP,
1586 p_prefix =>l_debug_prefix,
1587 p_msg_level=>fnd_log.level_statement);
1588 END IF;
1589
1590 savepoint process_update_cpts_pvt;
1591
1592 FND_MSG_PUB.initialize;
1593
1594 --Initialize API return status to success.
1595 x_return_status := FND_API.G_RET_STS_SUCCESS;
1596
1597 IF P_DML_RECORD.ALLOW_DISABLED_LOOKUP = 'Y' THEN
1598 IF P_DML_RECORD.RERUN = 'N' THEN
1599 -- First run with disabled lookup
1600 l_final_sql := l_update_sql || l_where_first_run_sql;
1601 ELSE
1602 -- Re-run with disabled lookup
1603 l_final_sql := l_update_sql || l_where_rerun_sql;
1604 END IF;
1605 ELSE
1606 IF P_DML_RECORD.RERUN = 'N' THEN
1607 -- First run with enabled lookup
1608 l_final_sql := l_update_sql || l_where_first_run_sql || l_where_enabled_lookup_sql;
1609 ELSE
1610 -- Re-run with enabled lookup
1611 l_final_sql := l_update_sql || l_where_rerun_sql || l_where_enabled_lookup_sql;
1612 END IF;
1613 END IF;
1614
1615 OPEN c_handle_update FOR l_final_sql using
1616 P_DML_RECORD.GMISS_CHAR,P_DML_RECORD.GMISS_CHAR,P_DML_RECORD.GMISS_CHAR,P_DML_RECORD.GMISS_CHAR,
1617 P_DML_RECORD.GMISS_CHAR,P_DML_RECORD.GMISS_CHAR,P_DML_RECORD.GMISS_CHAR,P_DML_RECORD.GMISS_CHAR,
1618 P_DML_RECORD.GMISS_CHAR,P_DML_RECORD.GMISS_NUM,P_DML_RECORD.GMISS_CHAR,P_DML_RECORD.GMISS_NUM,
1622 P_DML_RECORD.GMISS_CHAR,P_DML_RECORD.GMISS_CHAR,/*P_DML_RECORD.GMISS_NUM,*/ P_DML_RECORD.GMISS_CHAR,
1619 P_DML_RECORD.GMISS_CHAR,P_DML_RECORD.GMISS_NUM,P_DML_RECORD.GMISS_CHAR,P_DML_RECORD.GMISS_CHAR,
1620 P_DML_RECORD.GMISS_CHAR,P_DML_RECORD.GMISS_CHAR,P_DML_RECORD.GMISS_CHAR,P_DML_RECORD.GMISS_CHAR,
1621 P_DML_RECORD.GMISS_CHAR,P_DML_RECORD.GMISS_CHAR,P_DML_RECORD.GMISS_CHAR,P_DML_RECORD.GMISS_CHAR,
1623 P_DML_RECORD.GMISS_CHAR,P_DML_RECORD.GMISS_CHAR,P_DML_RECORD.GMISS_CHAR,P_DML_RECORD.GMISS_CHAR,
1624 P_DML_RECORD.GMISS_CHAR,P_DML_RECORD.GMISS_CHAR,P_DML_RECORD.GMISS_CHAR,P_DML_RECORD.GMISS_CHAR,
1625 P_DML_RECORD.GMISS_CHAR,P_DML_RECORD.GMISS_CHAR,P_DML_RECORD.GMISS_CHAR,P_DML_RECORD.GMISS_CHAR,
1626 P_DML_RECORD.GMISS_CHAR,P_DML_RECORD.GMISS_CHAR,P_DML_RECORD.GMISS_CHAR,P_DML_RECORD.GMISS_CHAR,
1627 P_DML_RECORD.GMISS_CHAR,P_DML_RECORD.GMISS_CHAR,P_DML_RECORD.GMISS_CHAR,P_DML_RECORD.GMISS_CHAR,
1628 P_DML_RECORD.GMISS_CHAR,P_DML_RECORD.GMISS_CHAR,P_DML_RECORD.GMISS_CHAR,P_DML_RECORD.GMISS_CHAR,
1629 P_DML_RECORD.GMISS_CHAR,P_DML_RECORD.GMISS_CHAR,P_DML_RECORD.GMISS_CHAR,P_DML_RECORD.GMISS_CHAR,
1630 P_DML_RECORD.GMISS_CHAR,P_DML_RECORD.GMISS_CHAR,P_DML_RECORD.GMISS_CHAR,P_DML_RECORD.GMISS_CHAR,
1631 P_DML_RECORD.GMISS_CHAR,P_DML_RECORD.GMISS_CHAR,P_DML_RECORD.GMISS_CHAR,P_DML_RECORD.GMISS_CHAR,
1632 P_DML_RECORD.GMISS_CHAR,P_DML_RECORD.GMISS_CHAR,P_DML_RECORD.ACTUAL_CONTENT_SRC,
1633 P_DML_RECORD.GMISS_CHAR,
1634 P_DML_RECORD.BATCH_ID,P_DML_RECORD.OS,P_DML_RECORD.FROM_OSR,P_DML_RECORD.TO_OSR,
1635 P_DML_RECORD.BATCH_MODE_FLAG;
1636
1637 FETCH c_handle_update BULK COLLECT INTO
1638 l_row_id,
1639 l_cp_orig_system,
1640 l_cp_orig_system_reference,
1641 l_party_orig_system,
1642 l_party_orig_system_reference,
1643 l_site_orig_system,
1644 l_site_orig_system_reference,
1645 l_insert_update_flag,
1646 l_contact_point_type,
1647 l_contact_point_purpose,
1648 l_edi_ece_tp_location_code,
1649 l_edi_id_number,
1650 l_edi_payment_format,
1651 l_edi_payment_method,
1652 l_edi_remittance_instruction,
1653 l_edi_remittance_method,
1654 l_edi_tp_header_id,
1655 l_edi_transaction_handling,
1656 l_eft_printing_program_id,
1657 l_eft_swift_code,
1658 l_eft_transmission_program_id,
1659 l_eft_user_number,
1660 l_email_address,
1661 l_email_format,
1662 l_phone_area_code,
1663 l_phone_country_code,
1664 l_phone_extension,
1665 l_phone_line_type,
1666 l_phone_number,
1667 l_raw_phone_number,
1668 l_phone_calling_calendar,
1669 l_telex_number,
1670 l_timezone_code,
1671 l_timezone_id,
1672 l_url,
1673 l_web_type,
1674 l_attribute_category,
1675 l_attribute1,
1676 l_attribute2,
1677 l_attribute3,
1678 l_attribute4,
1679 l_attribute5,
1680 l_attribute6,
1681 l_attribute7,
1682 l_attribute8,
1683 l_attribute9,
1684 l_attribute10,
1685 l_attribute11,
1686 l_attribute12,
1687 l_attribute13,
1688 l_attribute14,
1689 l_attribute15,
1690 l_attribute16,
1691 l_attribute17,
1692 l_attribute18,
1693 l_attribute19,
1694 l_attribute20,
1695 l_interface_status,
1696 l_action_flag,
1697 l_error_id,
1698 l_dqm_action_flag,
1699 l_dup_within_int_flag,
1700 l_party_id,
1701 l_party_site_id,
1702 l_contact_point_id,
1703 l_primary_flag,
1704 l_created_by_module,
1705 l_owner_table_name,
1706 l_owner_table_id,
1707 l_action_mismatch_error,
1708 l_cpt_type_updatable_error,
1709 l_cp_purpose_web_err,
1710 l_cp_purpose_error,
1711 l_edi_id_number_error,
1712 l_email_address_error,
1713 l_email_format_error,
1714 l_phone_country_code_error,
1715 l_phone_line_type_error,
1716 l_phone_number_error,
1717 l_telex_number_error,
1718 l_timezone_error,
1719 l_url_error,
1720 l_web_type_error,
1721 --l_orig_system_ref_upd_error,
1722 l_error_flag,
1723 --l_owner_table_error,
1724 l_dss_security_errors,
1725 l_flex_val_errors,
1726 l_old_cp_orig_system_ref,
1727 l_new_osr_exists_flag,
1728 l_third_party_update_error, /* Bug 4079902 */
1729 l_createdby_errors;
1730 -- Do FND desc flex validation based on profile
1731 IF P_DML_RECORD.FLEX_VALIDATION = 'Y' THEN
1732 validate_desc_flexfield(P_DML_RECORD.SYSDATE);
1733 END IF;
1734
1735 -- Do DSS security validation based on profile
1736 IF P_DML_RECORD.DSS_SECURITY = 'Y' THEN
1737 validate_DSS_security;
1738 END IF;
1739
1740 BEGIN
1741 ForAll j in 1..l_contact_point_id.count SAVE EXCEPTIONS
1742 update hz_contact_points set
1743 --CONTACT_POINT_ID = DECODE(l_contact_point_id(j),NULL,CONTACT_POINT_ID,P_DML_RECORD.GMISS_CHAR, NULL,l_contact_point_id(j)),
1744 --CONTACT_POINT_TYPE = DECODE(l_contact_point_type(j),NULL,CONTACT_POINT_TYPE,P_DML_RECORD.GMISS_CHAR, NULL,l_contact_point_type(j)),
1745 --STATUS = DECODE(l_status(j),NULL,STATUS,P_DML_RECORD.GMISS_CHAR, NULL,l_status(j)),
1746 --OWNER_TABLE_NAME = DECODE(l_owner_table_name(j),NULL,OWNER_TABLE_NAME,P_DML_RECORD.GMISS_CHAR, NULL,l_owner_table_name(j)),
1747 --OWNER_TABLE_ID = DECODE(l_owner_table_id(j),NULL,OWNER_TABLE_ID,P_DML_RECORD.GMISS_CHAR, NULL,l_owner_table_id(j)),
1748 --PRIMARY_FLAG = DECODE(l_primary_flag(j),NULL,PRIMARY_FLAG,P_DML_RECORD.GMISS_CHAR, NULL,l_primary_flag(j)), /* Bug No: 3917168 */
1749 LAST_UPDATE_DATE = P_DML_RECORD.SYSDATE,
1750 LAST_UPDATED_BY = P_DML_RECORD.USER_ID,
1751 LAST_UPDATE_LOGIN = P_DML_RECORD.LAST_UPDATE_LOGIN,
1752 REQUEST_ID = P_DML_RECORD.REQUEST_ID,
1753 PROGRAM_APPLICATION_ID = P_DML_RECORD.PROGRAM_APPLICATION_ID,
1757 P_DML_RECORD.GMISS_CHAR, NULL,l_attribute_category(j)),
1754 PROGRAM_ID = P_DML_RECORD.PROGRAM_ID,
1755 PROGRAM_UPDATE_DATE = P_DML_RECORD.SYSDATE,
1756 ATTRIBUTE_CATEGORY = DECODE(l_attribute_category(j),NULL,ATTRIBUTE_CATEGORY,
1758 ATTRIBUTE1 = DECODE(l_attribute1(j),NULL,ATTRIBUTE1,P_DML_RECORD.GMISS_CHAR, NULL,l_attribute1(j)),
1759 ATTRIBUTE2 = DECODE(l_attribute2(j),NULL,ATTRIBUTE2,P_DML_RECORD.GMISS_CHAR, NULL,l_attribute2(j)),
1760 ATTRIBUTE3 = DECODE(l_attribute3(j),NULL,ATTRIBUTE3,P_DML_RECORD.GMISS_CHAR, NULL,l_attribute3(j)),
1761 ATTRIBUTE4 = DECODE(l_attribute4(j),NULL,ATTRIBUTE4,P_DML_RECORD.GMISS_CHAR, NULL,l_attribute4(j)),
1762 ATTRIBUTE5 = DECODE(l_attribute5(j),NULL,ATTRIBUTE5,P_DML_RECORD.GMISS_CHAR, NULL,l_attribute5(j)),
1763 ATTRIBUTE6 = DECODE(l_attribute6(j),NULL,ATTRIBUTE6,P_DML_RECORD.GMISS_CHAR, NULL,l_attribute6(j)),
1764 ATTRIBUTE7 = DECODE(l_attribute7(j),NULL,ATTRIBUTE7,P_DML_RECORD.GMISS_CHAR, NULL,l_attribute7(j)),
1765 ATTRIBUTE8 = DECODE(l_attribute8(j),NULL,ATTRIBUTE8,P_DML_RECORD.GMISS_CHAR, NULL,l_attribute8(j)),
1766 ATTRIBUTE9 = DECODE(l_attribute9(j),NULL,ATTRIBUTE9,P_DML_RECORD.GMISS_CHAR, NULL,l_attribute9(j)),
1767 ATTRIBUTE10 = DECODE(l_attribute10(j),NULL,ATTRIBUTE10,P_DML_RECORD.GMISS_CHAR, NULL,l_attribute10(j)),
1768 ATTRIBUTE11 = DECODE(l_attribute11(j),NULL,ATTRIBUTE11,P_DML_RECORD.GMISS_CHAR, NULL,l_attribute11(j)),
1769 ATTRIBUTE12 = DECODE(l_attribute12(j),NULL,ATTRIBUTE12,P_DML_RECORD.GMISS_CHAR, NULL,l_attribute12(j)),
1770 ATTRIBUTE13 = DECODE(l_attribute13(j),NULL,ATTRIBUTE13,P_DML_RECORD.GMISS_CHAR, NULL,l_attribute13(j)),
1771 ATTRIBUTE14 = DECODE(l_attribute14(j),NULL,ATTRIBUTE14,P_DML_RECORD.GMISS_CHAR, NULL,l_attribute14(j)),
1772 ATTRIBUTE15 = DECODE(l_attribute15(j),NULL,ATTRIBUTE15,P_DML_RECORD.GMISS_CHAR, NULL,l_attribute15(j)),
1773 ATTRIBUTE16 = DECODE(l_attribute16(j),NULL,ATTRIBUTE16,P_DML_RECORD.GMISS_CHAR, NULL,l_attribute16(j)),
1774 ATTRIBUTE17 = DECODE(l_attribute17(j),NULL,ATTRIBUTE17,P_DML_RECORD.GMISS_CHAR, NULL,l_attribute17(j)),
1775 ATTRIBUTE18 = DECODE(l_attribute18(j),NULL,ATTRIBUTE18,P_DML_RECORD.GMISS_CHAR, NULL,l_attribute18(j)),
1776 ATTRIBUTE19 = DECODE(l_attribute19(j),NULL,ATTRIBUTE19,P_DML_RECORD.GMISS_CHAR, NULL,l_attribute19(j)),
1777 ATTRIBUTE20 = DECODE(l_attribute20(j),NULL,ATTRIBUTE20,P_DML_RECORD.GMISS_CHAR, NULL,l_attribute20(j)),
1778 EDI_TRANSACTION_HANDLING = DECODE(l_edi_transaction_handling(j),NULL,EDI_TRANSACTION_HANDLING,P_DML_RECORD.GMISS_CHAR, NULL,l_edi_transaction_handling(j)),
1779 EDI_ID_NUMBER = DECODE(l_edi_id_number(j),NULL,EDI_ID_NUMBER,P_DML_RECORD.GMISS_NUM, NULL,l_edi_id_number(j)),
1780 EDI_PAYMENT_METHOD = DECODE(l_edi_payment_method(j),NULL,EDI_PAYMENT_METHOD,P_DML_RECORD.GMISS_CHAR, NULL,l_edi_payment_method(j)),
1781 EDI_PAYMENT_FORMAT = DECODE(l_edi_payment_format(j),NULL,EDI_PAYMENT_FORMAT,P_DML_RECORD.GMISS_CHAR, NULL,l_edi_payment_format(j)),
1782 EDI_REMITTANCE_METHOD = DECODE(l_edi_remittance_method(j),NULL,EDI_REMITTANCE_METHOD,P_DML_RECORD.GMISS_CHAR, NULL,l_edi_remittance_method(j)),
1783 EDI_REMITTANCE_INSTRUCTION = DECODE(l_edi_remittance_instruction(j),NULL,EDI_REMITTANCE_INSTRUCTION,P_DML_RECORD.GMISS_CHAR, NULL,l_edi_remittance_instruction(j)),
1784 EDI_TP_HEADER_ID = DECODE(l_edi_tp_header_id(j),NULL,EDI_TP_HEADER_ID,P_DML_RECORD.GMISS_NUM, NULL,l_edi_tp_header_id(j)),
1785 EDI_ECE_TP_LOCATION_CODE = DECODE(l_edi_ece_tp_location_code(j),NULL,EDI_ECE_TP_LOCATION_CODE,P_DML_RECORD.GMISS_CHAR, NULL,l_edi_ece_tp_location_code(j)),
1786 EMAIL_FORMAT = DECODE(l_email_format(j),NULL,EMAIL_FORMAT,P_DML_RECORD.GMISS_CHAR, NULL,l_email_format(j)),
1787 EMAIL_ADDRESS = DECODE(l_email_address(j),NULL,EMAIL_ADDRESS,P_DML_RECORD.GMISS_CHAR, NULL,l_email_address(j)),
1788 --BEST_TIME_TO_CONTACT_START = DECODE(l_best_time_to_contact_start(j),NULL,BEST_TIME_TO_CONTACT_START,P_DML_RECORD.GMISS_CHAR, NULL,l_best_time_to_contact_start(j)),
1789 --BEST_TIME_TO_CONTACT_END = DECODE(l_best_time_to_contact_end(j),NULL,BEST_TIME_TO_CONTACT_END,P_DML_RECORD.GMISS_CHAR, NULL,l_best_time_to_contact_end(j)),
1790 PHONE_CALLING_CALENDAR = DECODE(l_phone_calling_calendar(j),NULL,PHONE_CALLING_CALENDAR,P_DML_RECORD.GMISS_CHAR, NULL,l_phone_calling_calendar(j)),
1791 --CONTACT_ATTEMPTS = DECODE(l_contact_attempts(j),NULL,CONTACT_ATTEMPTS,P_DML_RECORD.GMISS_CHAR, NULL,l_contact_attempts(j)),
1792 --CONTACTS = DECODE(l_contacts(j),NULL,CONTACTS,P_DML_RECORD.GMISS_CHAR, NULL,l_contacts(j)),
1793 --LAST_CONTACT_DT_TIME = DECODE(l_last_contact_dt_time(j),NULL,LAST_CONTACT_DT_TIME,P_DML_RECORD.GMISS_CHAR, NULL,l_last_contact_dt_time(j)),
1794 PHONE_AREA_CODE = DECODE(l_phone_area_code(j),NULL,PHONE_AREA_CODE,P_DML_RECORD.GMISS_CHAR, NULL,l_phone_area_code(j)),
1795 PHONE_COUNTRY_CODE = DECODE(l_phone_country_code(j),NULL,PHONE_COUNTRY_CODE,P_DML_RECORD.GMISS_CHAR, NULL,l_phone_country_code(j)),
1796 PHONE_NUMBER = DECODE(l_phone_number(j),NULL,decode(l_raw_phone_number(j),null,PHONE_NUMBER,null),P_DML_RECORD.GMISS_CHAR, NULL,l_phone_number(j)),
1797 PHONE_EXTENSION = DECODE(l_phone_extension(j),NULL,PHONE_EXTENSION,P_DML_RECORD.GMISS_CHAR, NULL,l_phone_extension(j)),
1798 PHONE_LINE_TYPE = DECODE(l_phone_line_type(j),NULL,PHONE_LINE_TYPE,P_DML_RECORD.GMISS_CHAR, NULL,l_phone_line_type(j)),
1799 TELEX_NUMBER = DECODE(l_telex_number(j),NULL,TELEX_NUMBER,P_DML_RECORD.GMISS_CHAR, NULL,l_telex_number(j)),
1800 WEB_TYPE = DECODE(l_web_type(j),NULL,WEB_TYPE,P_DML_RECORD.GMISS_CHAR, NULL,l_web_type(j)),
1801 URL = DECODE(l_url(j),NULL,URL,P_DML_RECORD.GMISS_CHAR, NULL,l_url(j)),
1802 --CONTENT_SOURCE_TYPE = DECODE(l_content_source_type(j),NULL,CONTENT_SOURCE_TYPE,P_DML_RECORD.GMISS_CHAR, NULL,l_content_source_type(j)),
1803 RAW_PHONE_NUMBER = DECODE(l_raw_phone_number(j),NULL,decode(l_phone_number(j),null,RAW_PHONE_NUMBER,null),P_DML_RECORD.GMISS_CHAR, NULL,l_raw_phone_number(j)),
1804 OBJECT_VERSION_NUMBER = nvl(OBJECT_VERSION_NUMBER,1) +1,
1808 CONTACT_POINT_PURPOSE = DECODE(l_contact_point_purpose(j),NULL,CONTACT_POINT_PURPOSE,P_DML_RECORD.GMISS_CHAR, NULL,l_contact_point_purpose(j)),
1805 --CREATED_BY_MODULE = nvl(CREATED_BY_MODULE, decode(l_created_by_module(j), P_DML_RECORD.GMISS_CHAR, CREATED_BY_MODULE, NULL, CREATED_BY_MODULE, l_created_by_module(j))),
1806 --APPLICATION_ID = DECODE(l_application_id(j),NULL,APPLICATION_ID,P_DML_RECORD.GMISS_CHAR, NULL,l_application_id(j)),
1807 TIMEZONE_ID = DECODE(l_timezone_code(j),NULL,TIMEZONE_ID,P_DML_RECORD.GMISS_CHAR, NULL,l_timezone_id(j)),
1809 --PRIMARY_BY_PURPOSE = DECODE(l_primary_by_purpose(j),NULL,PRIMARY_BY_PURPOSE,P_DML_RECORD.GMISS_CHAR, NULL,l_primary_by_purpose(j)),
1810 --TRANSPOSED_PHONE_NUMBER = DECODE(l_transposed_phone_number(j),NULL,TRANSPOSED_PHONE_NUMBER,P_DML_RECORD.GMISS_CHAR, NULL,l_transposed_phone_number(j)),
1811 EFT_TRANSMISSION_PROGRAM_ID = DECODE(l_eft_transmission_program_id(j),NULL,EFT_TRANSMISSION_PROGRAM_ID,P_DML_RECORD.GMISS_NUM, NULL,l_eft_transmission_program_id(j)),
1812 EFT_PRINTING_PROGRAM_ID = DECODE(l_eft_printing_program_id(j),NULL,EFT_PRINTING_PROGRAM_ID,P_DML_RECORD.GMISS_NUM, NULL,l_eft_printing_program_id(j)),
1813 EFT_USER_NUMBER = DECODE(l_eft_user_number(j),NULL,EFT_USER_NUMBER,P_DML_RECORD.GMISS_CHAR, NULL,l_eft_user_number(j)),
1814 EFT_SWIFT_CODE = DECODE(l_eft_swift_code(j),NULL,EFT_SWIFT_CODE,P_DML_RECORD.GMISS_CHAR, NULL,l_eft_swift_code(j)),
1815 --ACTUAL_CONTENT_SOURCE = DECODE(l_actual_content_source,NULL,ACTUAL_CONTENT_SOURCE,P_DML_RECORD.GMISS_CHAR, NULL,l_actual_content_source(j))
1816 ACTUAL_CONTENT_SOURCE = p_dml_record.actual_content_src /* Bug 4079902 */
1817 where contact_point_id = l_contact_point_id(j)
1818 and l_action_mismatch_error(j) is not null
1819 and l_cpt_type_updatable_error(j) is not null
1820 and l_cp_purpose_web_err(j) is not null
1821 and l_cp_purpose_error(j) is not null
1822 and l_edi_id_number_error(j) is not null
1823 and l_email_address_error(j) is not null
1824 and l_email_format_error(j) is not null
1825 and l_phone_country_code_error(j) is not null
1826 and l_phone_line_type_error(j) is not null
1827 and l_phone_number_error(j) is not null
1828 and l_telex_number_error(j) is not null
1829 and l_timezone_error(j) is not null
1830 and l_url_error(j) is not null
1831 and l_flex_val_errors(j) = 0
1832 and l_dss_security_errors(j) = 'T'
1833 and l_web_type_error(j) is not null
1834 --and l_orig_system_ref_upd_error(j) is not null
1835 and l_error_flag(j) is null
1836 --and l_owner_table_error(j) is not null
1837 and l_third_party_update_error(j) is not null /* Bug 4079902 */
1838 and l_createdby_errors(j) is not null;
1839
1840 FND_FILE.put_line(fnd_file.log, 'CPT:Rows updated = ' || SQL%ROWCOUNT);
1841
1842
1843 EXCEPTION
1844 WHEN OTHERS THEN
1845 -- dbms_output.put_line('Other exceptions');
1846 --Record the errors if occurred.
1847 l_dml_exception := 'Y';
1848 END;
1849 -- record errors
1850 report_errors(P_DML_RECORD,'U', l_dml_exception);
1851
1852 ForAll j in 1..l_contact_point_id.count
1853 update hz_parties hz_pty
1854 set (email_address,url ) =
1855 ( select email_address,url
1856 from hz_contact_points
1857 where contact_point_id = l_contact_point_id(j)
1858 )
1859 where hz_pty.party_id = l_party_id(j)
1860 and l_action_mismatch_error(j) is not null
1861 and l_cpt_type_updatable_error(j) is not null
1862 and l_cp_purpose_web_err(j) is not null
1863 and l_cp_purpose_error(j) is not null
1864 and l_edi_id_number_error(j) is not null
1865 and l_email_address_error(j) is not null
1866 and l_email_format_error(j) is not null
1867 and l_phone_country_code_error(j) is not null
1868 and l_phone_line_type_error(j) is not null
1869 and l_phone_number_error(j) is not null
1870 and l_telex_number_error(j) is not null
1871 and l_timezone_error(j) is not null
1872 and l_url_error(j) is not null
1873 and l_flex_val_errors(j) = 0
1874 and l_dss_security_errors(j) = 'T'
1875 and l_web_type_error(j) is not null
1876 --and l_orig_system_ref_upd_error(j) is not null
1877 and l_error_flag(j) is null
1878 --and l_owner_table_error(j) is not null
1879 and l_primary_flag(j) = 'Y'
1880 and l_third_party_update_error(j) is not null /* Bug 4079902 */
1881 and l_createdby_errors(j) is not null;
1882
1883
1884 /******************************************/
1885 /* Handle OSR change */
1886 /******************************************/
1887
1888 /* End date current MOSR mapping */
1889 ForAll j in 1..l_cp_orig_system_reference.count
1890 update HZ_ORIG_SYS_REFERENCES set
1891 STATUS = 'I',
1892 LAST_UPDATE_DATE = P_DML_RECORD.SYSDATE,
1893 LAST_UPDATED_BY = P_DML_RECORD.USER_ID,
1894 LAST_UPDATE_LOGIN = P_DML_RECORD.LAST_UPDATE_LOGIN,
1895 END_DATE_ACTIVE = P_DML_RECORD.SYSDATE,
1896 OBJECT_VERSION_NUMBER = NVL(OBJECT_VERSION_NUMBER,1)+1
1897 where ORIG_SYSTEM = l_cp_orig_system(j)
1898 and ORIG_SYSTEM_REFERENCE = l_old_cp_orig_system_ref(j)
1899 and OWNER_TABLE_NAME = 'HZ_CONTACT_POINTS'
1900 and OWNER_TABLE_ID = l_contact_point_id(j)
1901 -- and l_new_osr_exists_flag(j) is not null /* Bug 5383200 */
1902 and l_num_row_processed(j) = 1
1903 and status = 'A'
1904 --and trunc(nvl(end_date_active, P_DML_RECORD.SYSDATE)) >= trunc(P_DML_RECORD.SYSDATE)
1905 and l_cp_orig_system_reference(j) <> l_old_cp_orig_system_ref(j);
1906
1907 /* Bug 5383200
1908 End date the collided OSR mapping. This can happen when:
1909 There is OSR change and the new OSR already has a record in SSM.
1910 new_osr_exists_flag has value 'Y'. */
1914 LAST_UPDATE_DATE = P_DML_RECORD.SYSDATE,
1911 ForAll j in 1..l_cp_orig_system_reference.count
1912 update HZ_ORIG_SYS_REFERENCES set
1913 STATUS = 'I',
1915 LAST_UPDATED_BY = P_DML_RECORD.USER_ID,
1916 LAST_UPDATE_LOGIN = P_DML_RECORD.LAST_UPDATE_LOGIN,
1917 END_DATE_ACTIVE = P_DML_RECORD.SYSDATE,
1918 OBJECT_VERSION_NUMBER = NVL(OBJECT_VERSION_NUMBER,1)+1
1919 where
1920 ORIG_SYSTEM = l_cp_orig_system(j)
1921 and ORIG_SYSTEM_REFERENCE = l_cp_orig_system_reference(j)
1922 and OWNER_TABLE_NAME = 'HZ_CONTACT_POINTS'
1923 and l_new_osr_exists_flag(j) = 'Y'
1924 and l_num_row_processed(j) = 1
1925 and status = 'A'
1926 and trunc(nvl(end_date_active, P_DML_RECORD.SYSDATE)) >= trunc(P_DML_RECORD.SYSDATE);
1927
1928
1929 /* Insert new MOSR mapping in case of OSR change */
1930 ForAll j in 1..l_cp_orig_system_reference.count
1931 insert into HZ_ORIG_SYS_REFERENCES
1932 (
1933 ORIG_SYSTEM_REF_ID,
1934 ORIG_SYSTEM,
1935 ORIG_SYSTEM_REFERENCE,
1936 OWNER_TABLE_NAME,
1937 OWNER_TABLE_ID,
1938 PARTY_ID,
1939 STATUS,
1940 OLD_ORIG_SYSTEM_REFERENCE,
1941 START_DATE_ACTIVE,
1942 CREATED_BY,
1943 CREATION_DATE,
1944 LAST_UPDATED_BY,
1945 LAST_UPDATE_DATE,
1946 LAST_UPDATE_LOGIN,
1947 CREATED_BY_MODULE,
1948 APPLICATION_ID,
1949 OBJECT_VERSION_NUMBER
1950 )
1951 select
1952 HZ_ORIG_SYSTEM_REF_S.NEXTVAL,
1953 l_cp_orig_system(j),
1954 l_cp_orig_system_reference(j),
1955 'HZ_CONTACT_POINTS',
1956 l_contact_point_id(j),
1957 l_party_id(j),
1958 'A',
1959 l_old_cp_orig_system_ref(j),
1960 P_DML_RECORD.SYSDATE,
1961 P_DML_RECORD.USER_ID,
1962 P_DML_RECORD.SYSDATE,
1963 P_DML_RECORD.USER_ID,
1964 P_DML_RECORD.SYSDATE,
1965 P_DML_RECORD.LAST_UPDATE_LOGIN,
1966 nvl(l_created_by_module(j), 'HZ_IMPORT'),
1967 P_DML_RECORD.APPLICATION_ID,
1968 1
1969 from dual
1970 where
1971 l_old_cp_orig_system_ref(j) is not null
1972 and l_num_row_processed(j) = 1
1973 and l_cp_orig_system_reference(j) <> l_old_cp_orig_system_ref(j);
1974 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
1975 hz_utility_v2pub.debug(p_message=>'CPT: process_update_contactpoints-',
1976 p_prefix=>l_debug_prefix,
1977 p_msg_level=>fnd_log.level_procedure);
1978 END IF;
1979
1980 EXCEPTION
1981
1982 WHEN FND_API.G_EXC_ERROR THEN
1983 ROLLBACK TO process_update_cpts_pvt;
1984 x_return_status := FND_API.G_RET_STS_ERROR;
1985 FND_MSG_PUB.Count_And_Get(
1986 p_encoded => FND_API.G_FALSE,
1987 p_count => x_msg_count,
1988 p_data => x_msg_data);
1989
1990 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1991 ROLLBACK TO process_update_cpts_pvt;
1992 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1993 FND_MSG_PUB.Count_And_Get(
1994 p_encoded => FND_API.G_FALSE,
1995 p_count => x_msg_count,
1996 p_data => x_msg_data);
1997
1998 WHEN OTHERS THEN
1999 ROLLBACK TO process_update_cpts_pvt;
2000 FND_FILE.put_line(fnd_file.log,'Unexpected error occurs while updating contactpoints');
2001 FND_FILE.put_line(fnd_file.log, l_errm);
2002 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2003 FND_MESSAGE.SET_NAME('AR', 'HZ_API_OTHERS_EXCEP');
2004 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
2005 FND_MSG_PUB.ADD;
2006 FND_MSG_PUB.Count_And_Get(
2007 p_encoded => FND_API.G_FALSE,
2008 p_count => x_msg_count,
2009 p_data => x_msg_data);
2010
2011
2012 END process_update_contactpoints;
2013
2014
2015 /********************************************************************************
2016 *
2017 * load_contactpoints
2018 *
2019 ********************************************************************************/
2020
2021 PROCEDURE load_contactpoints
2022 (
2023 P_DML_RECORD IN HZ_IMP_LOAD_WRAPPER.DML_RECORD_TYPE
2024 ,x_return_status OUT NOCOPY VARCHAR2
2025 ,x_msg_count OUT NOCOPY NUMBER
2026 ,x_msg_data OUT NOCOPY VARCHAR2
2027 )
2028
2029 IS
2030 l_debug_prefix VARCHAR2(30) := '';
2031 BEGIN
2032
2033 savepoint load_contactpoints_pvt;
2034
2035 -- Check if API is called in debug mode. If yes, enable debug.
2036 --enable_debug;
2037 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
2038 hz_utility_v2pub.debug(p_message=>'CPT:load_contactpoints()+',
2039 p_prefix=>l_debug_prefix,
2040 p_msg_level=>fnd_log.level_procedure);
2041 END IF;
2042
2043 FND_MSG_PUB.initialize;
2044
2045 FND_FILE.put_line(fnd_file.log,'load_contactpoints+');
2046
2047 /**** ?? Remove later. Disable policy function ***/
2048 --hz_common_pub.disable_cont_source_security; Bug No:3387220
2049
2050 --Initialize API return status to success.
2051 x_return_status := FND_API.G_RET_STS_SUCCESS;
2052
2053 process_insert_contactpoints
2054 (
2055 P_DML_RECORD =>P_DML_RECORD
2056 ,x_return_status => x_return_status
2057 ,x_msg_count => x_msg_count
2058 ,x_msg_data => x_msg_data
2059 );
2060
2061
2062 process_update_contactpoints(
2063 P_DML_RECORD =>P_DML_RECORD
2064 ,x_return_status => x_return_status
2065 ,x_msg_count => x_msg_count
2066 ,x_msg_data => x_msg_data
2067 );
2068
2069 -- dbms_output.put_line('load_contactpoints-');
2070 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
2071 hz_utility_v2pub.debug(p_message=>'CPT:load_contactpoints()-',
2072 p_prefix=>l_debug_prefix,
2073 p_msg_level=>fnd_log.level_procedure);
2074 END IF;
2075 -- if enabled, disable debug
2076 --disable_debug;
2077
2078 EXCEPTION
2079
2080 WHEN FND_API.G_EXC_ERROR THEN
2081 ROLLBACK TO load_contactpoints_pvt;
2082 x_return_status := FND_API.G_RET_STS_ERROR;
2083 FND_MSG_PUB.Count_And_Get(
2084 p_encoded => FND_API.G_FALSE,
2085 p_count => x_msg_count,
2086 p_data => x_msg_data);
2087 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
2088 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
2089 p_msg_data=>x_msg_data,
2090 p_msg_type=>'ERROR',
2091 p_msg_level=>fnd_log.level_error);
2092 END IF;
2093 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
2094 hz_utility_v2pub.debug(p_message=>'CPT:load_contactpoints()-',
2095 p_prefix=>l_debug_prefix,
2096 p_msg_level=>fnd_log.level_procedure);
2097 END IF;
2098
2099
2100 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2101
2102 ROLLBACK TO load_contactpoints_pvt;
2103 FND_FILE.put_line(fnd_file.log,'Unexpected error occurs while loading contactpoints');
2104 FND_FILE.put_line(fnd_file.log, l_errm);
2105 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2106 FND_MESSAGE.SET_NAME('AR', 'HZ_API_OTHERS_EXCEP');
2107 FND_MESSAGE.SET_TOKEN('ERROR' ,l_errm);
2108 FND_MSG_PUB.ADD;
2109 FND_MSG_PUB.Count_And_Get(
2110 p_encoded => FND_API.G_FALSE,
2111 p_count => x_msg_count,
2112 p_data => x_msg_data);
2113 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
2114 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
2115 p_msg_data=>x_msg_data,
2116 p_msg_type=>'UNEXPECTED ERROR',
2117 p_msg_level=>fnd_log.level_error);
2118 END IF;
2119 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
2120 hz_utility_v2pub.debug(p_message=>'CPT:load_contactpoints()-',
2121 p_prefix=>l_debug_prefix,
2122 p_msg_level=>fnd_log.level_procedure);
2123 END IF;
2124
2125 WHEN OTHERS THEN
2126 ROLLBACK TO load_contactpoints_pvt;
2127 FND_FILE.put_line(fnd_file.log,'Unexpected error occurs while loading contactpoints');
2128 FND_FILE.put_line(fnd_file.log, l_errm);
2129 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2130 FND_MESSAGE.SET_NAME('AR', 'HZ_API_OTHERS_EXCEP');
2131 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
2132 FND_MSG_PUB.ADD;
2133 FND_MSG_PUB.Count_And_Get(
2134 p_encoded => FND_API.G_FALSE,
2135 p_count => x_msg_count,
2136 p_data => x_msg_data);
2137 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
2138 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
2139 p_msg_data=>x_msg_data,
2140 p_msg_type=>'SQL ERROR',
2141 p_msg_level=>fnd_log.level_error);
2142 END IF;
2143 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
2144 hz_utility_v2pub.debug(p_message=>'CPT:load_contactpoints()-',
2145 p_prefix=>l_debug_prefix,
2146 p_msg_level=>fnd_log.level_procedure);
2147 END IF;
2148 END load_contactpoints;
2149
2150 END HZ_IMP_LOAD_CPT_PKG;