[Home] [Help]
PACKAGE BODY: APPS.HZ_IMP_LOAD_ADDRESSES_PKG
Source
1 PACKAGE BODY HZ_IMP_LOAD_ADDRESSES_PKG AS
2 /*$Header: ARHLADDB.pls 120.48 2011/09/22 08:26:23 vsegu ship $*/
3
4
5 /* Commented the code for bug 4079902. */
6
7 -- bug fix 3851810
8 /*
9 g_pst_mixnmatch_enabled VARCHAR2(1);
10 g_pst_selected_datasources VARCHAR2(255);
11 g_pst_is_datasource_selected VARCHAR2(1) := 'N';
12 g_pst_entity_attr_id NUMBER;
13 */
14 g_debug_count NUMBER := 0;
15 --g_debug BOOLEAN := FALSE;
16
17 c_end_date DATE := to_date('4712.12.31 00:01','YYYY.MM.DD HH24:MI');
18
19 l_owner_table_id OWNER_TABLE_ID;
20 l_site_orig_system SITE_ORIG_SYSTEM;
21 l_site_orig_system_reference SITE_ORIG_SYSTEM_REFERENCE;
22 l_old_site_osr SITE_ORIG_SYSTEM_REFERENCE;
23 l_site_id SITE_ID;
24 l_new_site_id SITE_ID;
25 l_party_id PARTY_ID;
26 l_site_name SITE_NAME;
27 l_party_site_number PARTY_SITE_NUMBER;
28 l_error_party_id PARTY_ID;
29 l_error_site_id SITE_ID;
30 l_update_party_id PARTY_ID;
31 l_update_site_id SITE_ID;
32 l_val_status_code VALIDATION_STATUS_CODE;
33 l_old_profile_sst_flag PROFILE_SST_FLAG;
34
35 l_attr_category ATTRIBUTE_CATEGORY;
36 l_attr1 ATTRIBUTE;
37 l_attr2 ATTRIBUTE;
38 l_attr3 ATTRIBUTE;
39 l_attr4 ATTRIBUTE;
40 l_attr5 ATTRIBUTE;
41 l_attr6 ATTRIBUTE;
42 l_attr7 ATTRIBUTE;
43 l_attr8 ATTRIBUTE;
44 l_attr9 ATTRIBUTE;
45 l_attr10 ATTRIBUTE;
46 l_attr11 ATTRIBUTE;
47 l_attr12 ATTRIBUTE;
48 l_attr13 ATTRIBUTE;
49 l_attr14 ATTRIBUTE;
50 l_attr15 ATTRIBUTE;
51 l_attr16 ATTRIBUTE;
52 l_attr17 ATTRIBUTE;
53 l_attr18 ATTRIBUTE;
54 l_attr19 ATTRIBUTE;
55 l_attr20 ATTRIBUTE;
56
57 l_old_attr_category ATTRIBUTE_CATEGORY;
58 l_old_attr1 ATTRIBUTE;
59 l_old_attr2 ATTRIBUTE;
60 l_old_attr3 ATTRIBUTE;
61 l_old_attr4 ATTRIBUTE;
62 l_old_attr5 ATTRIBUTE;
63 l_old_attr6 ATTRIBUTE;
64 l_old_attr7 ATTRIBUTE;
65 l_old_attr8 ATTRIBUTE;
66 l_old_attr9 ATTRIBUTE;
67 l_old_attr10 ATTRIBUTE;
68 l_old_attr11 ATTRIBUTE;
69 l_old_attr12 ATTRIBUTE;
70 l_old_attr13 ATTRIBUTE;
71 l_old_attr14 ATTRIBUTE;
72 l_old_attr15 ATTRIBUTE;
73 l_old_attr16 ATTRIBUTE;
74 l_old_attr17 ATTRIBUTE;
75 l_old_attr18 ATTRIBUTE;
76 l_old_attr19 ATTRIBUTE;
77 l_old_attr20 ATTRIBUTE;
78
79 l_country COUNTRY;
80 l_addr1 ADDRESS;
81 l_addr2 ADDRESS;
82 l_addr3 ADDRESS;
83 l_addr4 ADDRESS;
84 l_city CITY;
85 l_postal_code POSTAL_CODE;
86 l_state STATE;
87 l_province PROVINCE;
88 l_county COUNTY;
89
90 l_old_country COUNTRY;
91 l_old_addr1 ADDRESS;
92 l_old_addr2 ADDRESS;
93 l_old_addr3 ADDRESS;
94 l_old_addr4 ADDRESS;
95 l_old_city CITY;
96 l_old_postal_code POSTAL_CODE;
97 l_old_state STATE;
98 l_old_province PROVINCE;
99 l_old_county COUNTY;
100
101 l_action_flag ACTION_FLAG;
102 l_country_std COUNTRY;
103 l_addr1_std ADDRESS;
104 l_addr2_std ADDRESS;
105 l_addr3_std ADDRESS;
106 l_addr4_std ADDRESS;
107 l_city_std CITY;
108 l_postal_code_std POSTAL_CODE;
109 l_ps_admin_int STATE;
110 l_ps_admin_std STATE;
111 l_county_std COUNTY;
112
113 l_old_timezone TIMEZONE;
114 l_timezone TIMEZONE;
115 l_timezone_code TIMEZONE_CODE;
116
117 l_addr_phonetic ADDRESS_LINES_PHONETIC;
118 l_postal_plus4 POSTAL_PLUS4_CODE;
119 l_loc_dir LOCATION_DIRECTIONS;
120 l_clli_code CLLI_CODE;
121 l_language LANGUAGE;
122 l_short_desc SHORT_DESCRIPTION;
123 l_desc DESCRIPTION;
124 l_delvy_pt_code DELIVERY_POINT_CODE;
125 l_last_updated_by LAST_UPDATED_BY;
126 l_sales_tax_code SALES_TAX_GEOCODE;
127 l_sales_tax_limit SALES_TAX_LIMITS;
128
129 l_old_addr_phonetic ADDRESS_LINES_PHONETIC;
130 l_old_postal_plus4 POSTAL_PLUS4_CODE;
131 l_old_loc_dir LOCATION_DIRECTIONS;
132 l_old_clli_code CLLI_CODE;
133 l_old_language LANGUAGE;
134 l_old_short_desc SHORT_DESCRIPTION;
135 l_old_desc DESCRIPTION;
136 l_old_delvy_pt_code DELIVERY_POINT_CODE;
137 l_old_sales_tax_code SALES_TAX_GEOCODE;
138 l_old_sales_tax_limit SALES_TAX_LIMITS;
139
140 -- l_fa_loc_id FA_LOCATION_ID;
141 l_created_by_module CREATED_BY_MODULE;
142 --l_location_profile_id LOCATION_PROFILE_ID;
143 l_location_id LOCATION_ID;
144 l_new_loc_id LOCATION_ID;
145 l_accept_std_flag ACCEPT_STANDARDIZED_FLAG;
146 l_adptr_content_src ADAPTER_CONTENT_SRC;
147 l_corr_mv_ind CORRECT_MOVE_INDICATOR;
148 l_ident_addr_flag IDENT_ADDR_FLAG;
149
150 l_valid_status_code VALID_STATUS_CODE;
151 l_old_valid_status_code VALID_STATUS_CODE;
152 l_date_validated DATE_VALIDATED;
153
154 l_application_id APPLICATION_ID;
155 l_action_error_flag FLAG_ERROR;
156 l_error_flag FLAG_ERROR;
157 l_address_err LOOKUP_ERROR;
158 l_country_err LOOKUP_ERROR;
159 l_lang_err LOOKUP_ERROR;
160 l_timezone_err LOOKUP_ERROR;
161 l_flex_val_errors NUMBER_COLUMN;
162 l_dss_security_errors FLAG_ERROR;
163 l_addr_ch_flag FLAG_ERROR;
164 l_tax_ch_flag FLAG_ERROR;
165 l_move_count NUMBER_COLUMN; -- number of moved records
166 l_corr_count NUMBER_COLUMN; -- number of corrected records
167 l_init_upd_count NUMBER_COLUMN; -- total number of corrected/updated records
168 l_corr_upd_count NUMBER_COLUMN; -- total number of corrected/updated records
169 l_temp_upd_count NUMBER_COLUMN; -- number of temp update records
170 l_NEW_OSR_EXISTS FLAG_ERROR;
171 l_primary_flag FLAG_ERROR;
172
173 l_createdby_errors LOOKUP_ERROR;
174
175 l_exception_exists FLAG_ERROR;
176 l_num_row_processed NUMBER_COLUMN;
177 l_row_id ROWID;
178 l_errm VARCHAR2(100);
179
180 l_allow_correction VARCHAR2(1);
181 l_maintain_loc_hist VARCHAR2(1);
182 l_allow_std_update VARCHAR2(1);
183
184 l_third_party_update_error FLAG_ERROR; /* bug 4079902 */
185
186 TYPE OWNING_PARTY_ID_LIST IS TABLE OF HZ_PARTIES.PARTY_ID%TYPE;
187 l_owning_party_id OWNING_PARTY_ID_LIST;
188
189 --------------------------------------
190 -- forward declaration of private procedures and functions
191 --------------------------------------
192
193 /*PROCEDURE enable_debug;
194 PROCEDURE disable_debug;
195 */
196
197 PROCEDURE open_update_cursor (update_cursor IN OUT NOCOPY update_cursor_type,
198 P_DML_RECORD IN HZ_IMP_LOAD_WRAPPER.DML_RECORD_TYPE
199 );
200
201 PROCEDURE process_insert_addresses (
202 P_DML_RECORD IN HZ_IMP_LOAD_WRAPPER.DML_RECORD_TYPE,
203 x_return_status OUT NOCOPY VARCHAR2,
204 x_msg_count OUT NOCOPY NUMBER,
205 x_msg_data OUT NOCOPY VARCHAR2
206 );
207
208 PROCEDURE populate_error_table(
209 P_DML_RECORD IN HZ_IMP_LOAD_WRAPPER.DML_RECORD_TYPE,
210 P_DUP_VAL_EXP IN VARCHAR2,
211 P_SQL_ERRM IN VARCHAR2 );
212
213 PROCEDURE process_update_addresses (
214 P_DML_RECORD IN HZ_IMP_LOAD_WRAPPER.DML_RECORD_TYPE,
215 x_return_status OUT NOCOPY VARCHAR2,
216 x_msg_count OUT NOCOPY NUMBER,
217 x_msg_data OUT NOCOPY VARCHAR2
218 );
219
220 PROCEDURE report_errors(
221 P_DML_RECORD IN HZ_IMP_LOAD_WRAPPER.DML_RECORD_TYPE,
222 P_DML_EXCEPTION IN VARCHAR2);
223
224 PROCEDURE sync_party_tax_profile
225 ( P_BATCH_ID IN NUMBER,
226 P_REQUEST_ID IN NUMBER,
227 P_ORIG_SYSTEM IN VARCHAR2,
228 P_FROM_OSR IN VARCHAR2,
229 P_TO_OSR IN VARCHAR2,
230 P_BATCH_MODE_FLAG IN VARCHAR2,
231 P_PROGRAM_ID IN NUMBER
232 );
233
234 FUNCTION validate_desc_flexfield_f(
235 p_attr_category IN VARCHAR2,
236 p_attr1 IN VARCHAR2,
237 p_attr2 IN VARCHAR2,
238 p_attr3 IN VARCHAR2,
239 p_attr4 IN VARCHAR2,
240 p_attr5 IN VARCHAR2,
241 p_attr6 IN VARCHAR2,
242 p_attr7 IN VARCHAR2,
243 p_attr8 IN VARCHAR2,
244 p_attr9 IN VARCHAR2,
245 p_attr10 IN VARCHAR2,
246 p_attr11 IN VARCHAR2,
247 p_attr12 IN VARCHAR2,
248 p_attr13 IN VARCHAR2,
249 p_attr14 IN VARCHAR2,
250 p_attr15 IN VARCHAR2,
251 p_attr16 IN VARCHAR2,
252 p_attr17 IN VARCHAR2,
253 p_attr18 IN VARCHAR2,
254 p_attr19 IN VARCHAR2,
255 p_attr20 IN VARCHAR2,
256 p_validation_date IN DATE,
257 p_gmiss_char IN VARCHAR2
258 ) RETURN VARCHAR2 IS
259 l_debug_prefix VARCHAR2(30) := '';
260 BEGIN
261 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
262 hz_utility_v2pub.debug(p_message=>'ADDR:validate_desc_flexfield_f()+',
263 p_prefix=>l_debug_prefix,
264 p_msg_level=>fnd_log.level_procedure);
265 END IF;
266
267 FND_FLEX_DESCVAL.set_context_value(nullif(p_attr_category, p_gmiss_char));
268
269 FND_FLEX_DESCVAL.set_column_value('ATTRIBUTE1', nullif(p_attr1, p_gmiss_char));
270 FND_FLEX_DESCVAL.set_column_value('ATTRIBUTE2', nullif(p_attr2, p_gmiss_char));
271 FND_FLEX_DESCVAL.set_column_value('ATTRIBUTE3', nullif(p_attr3, p_gmiss_char));
272 FND_FLEX_DESCVAL.set_column_value('ATTRIBUTE4', nullif(p_attr4, p_gmiss_char));
273 FND_FLEX_DESCVAL.set_column_value('ATTRIBUTE5', nullif(p_attr5, p_gmiss_char));
274 FND_FLEX_DESCVAL.set_column_value('ATTRIBUTE6', nullif(p_attr6, p_gmiss_char));
275 FND_FLEX_DESCVAL.set_column_value('ATTRIBUTE7', nullif(p_attr7, p_gmiss_char));
276 FND_FLEX_DESCVAL.set_column_value('ATTRIBUTE8', nullif(p_attr8, p_gmiss_char));
277 FND_FLEX_DESCVAL.set_column_value('ATTRIBUTE9', nullif(p_attr9, p_gmiss_char));
278 FND_FLEX_DESCVAL.set_column_value('ATTRIBUTE10', nullif(p_attr10, p_gmiss_char));
279 FND_FLEX_DESCVAL.set_column_value('ATTRIBUTE11', nullif(p_attr11, p_gmiss_char));
280 FND_FLEX_DESCVAL.set_column_value('ATTRIBUTE12', nullif(p_attr12, p_gmiss_char));
281 FND_FLEX_DESCVAL.set_column_value('ATTRIBUTE13', nullif(p_attr13, p_gmiss_char));
282 FND_FLEX_DESCVAL.set_column_value('ATTRIBUTE14', nullif(p_attr14, p_gmiss_char));
283 FND_FLEX_DESCVAL.set_column_value('ATTRIBUTE15', nullif(p_attr15, p_gmiss_char));
284 FND_FLEX_DESCVAL.set_column_value('ATTRIBUTE16', nullif(p_attr16, p_gmiss_char));
285 FND_FLEX_DESCVAL.set_column_value('ATTRIBUTE17', nullif(p_attr17, p_gmiss_char));
286 FND_FLEX_DESCVAL.set_column_value('ATTRIBUTE18', nullif(p_attr18, p_gmiss_char));
287 FND_FLEX_DESCVAL.set_column_value('ATTRIBUTE19', nullif(p_attr19, p_gmiss_char));
288 FND_FLEX_DESCVAL.set_column_value('ATTRIBUTE20', nullif(p_attr20, p_gmiss_char));
289
290 IF (FND_FLEX_DESCVAL.validate_desccols(
291 'AR',
292 'HZ_PARTY_SITES',
293 'V',
294 p_validation_date)) THEN
295 return 'Y';
296 ELSE
297 return null;
298 END IF;
299
300 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
301 hz_utility_v2pub.debug(p_message=>'ADDR:validate_desc_flexfield_f()-',
302 p_prefix=>l_debug_prefix,
303 p_msg_level=>fnd_log.level_procedure);
304 END IF;
305
306 END validate_desc_flexfield_f;
307
308
309 PROCEDURE load_addresses (
310 P_DML_RECORD IN HZ_IMP_LOAD_WRAPPER.DML_RECORD_TYPE,
311 P_UPDATE_STR_ADDR IN VARCHAR2,
312 P_MAINTAIN_LOC_HIST IN VARCHAR2,
313 P_ALLOW_ADDR_CORR IN VARCHAR2,
314 x_return_status OUT NOCOPY VARCHAR2,
315 x_msg_count OUT NOCOPY NUMBER,
316 x_msg_data OUT NOCOPY VARCHAR2
317 ) IS
318 l_debug_prefix VARCHAR2(30) := '';
319 BEGIN
320 savepoint load_addresses_pvt;
321 FND_MSG_PUB.initialize;
322 x_return_status := FND_API.G_RET_STS_SUCCESS;
323
324 --enable_debug;
325 -- Debug info.
326 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
327 hz_utility_v2pub.debug(p_message=>'ADDR:load_addresses()+',
328 p_prefix=>l_debug_prefix,
329 p_msg_level=>fnd_log.level_procedure);
330 END IF;
331
332 l_allow_correction := P_ALLOW_ADDR_CORR;
333 l_maintain_loc_hist := P_MAINTAIN_LOC_HIST;
334 l_allow_std_update := P_UPDATE_STR_ADDR;
335
336 l_move_count := null;
337 l_init_upd_count := null;
338 l_corr_count := null;
339 l_corr_upd_count := null;
340 l_temp_upd_count := null;
341 l_move_count := NUMBER_COLUMN();
342 l_init_upd_count := NUMBER_COLUMN();
343 l_corr_count := NUMBER_COLUMN();
344 l_corr_upd_count := NUMBER_COLUMN();
345 l_temp_upd_count := NUMBER_COLUMN();
346
347
348 process_insert_addresses(P_DML_RECORD,
349 x_return_status, x_msg_count, x_msg_data );
350
351 IF x_return_status <> FND_API.G_RET_STS_UNEXP_ERROR THEN
352 process_update_addresses(P_DML_RECORD, x_return_status,
353 x_msg_count, x_msg_data );
354 END IF;
355
356 sync_party_tax_profile
357 ( P_BATCH_ID => P_DML_RECORD.batch_id ,
358 P_REQUEST_ID => P_DML_RECORD.request_id ,
359 P_ORIG_SYSTEM => P_DML_RECORD.os ,
360 P_FROM_OSR => P_DML_RECORD.from_osr ,
361 P_TO_OSR => P_DML_RECORD.to_osr ,
362 P_BATCH_MODE_FLAG => P_DML_RECORD.batch_mode_flag,
363 P_PROGRAM_ID => P_DML_RECORD.program_id
364 );
365
366 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
367 hz_utility_v2pub.debug(p_message=>'ADDR:load_addresses()-',
368 p_prefix=>l_debug_prefix,
369 p_msg_level=>fnd_log.level_procedure);
370 END IF;
371
372
373
374 ----dbms_output.put_line('end of loading address');
375 EXCEPTION
376 WHEN FND_API.G_EXC_ERROR THEN
377 ----dbms_output.put_line('===============G_EXC_ERROR error');
378 ROLLBACK TO load_addresses_pvt;
379 FND_FILE.put_line(fnd_file.log,'Execution error occurs while loading addresses');
380 FND_FILE.put_line(fnd_file.log, SQLERRM);
381 x_return_status := FND_API.G_RET_STS_ERROR;
382 FND_MSG_PUB.Count_And_Get(
383 p_encoded => FND_API.G_FALSE,
384 p_count => x_msg_count,
385 p_data => x_msg_data);
386 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
387 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
388 p_msg_data=>x_msg_data,
389 p_msg_type=>'ERROR',
390 p_msg_level=>fnd_log.level_error);
391 END IF;
392 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
393 hz_utility_v2pub.debug(p_message=>'ADDR:load_addresses()-',
394 p_prefix=>l_debug_prefix,
395 p_msg_level=>fnd_log.level_procedure);
396 END IF;
397
398 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
399 ----dbms_output.put_line('================unexpected error');
400
401 ROLLBACK TO load_addresses_pvt;
402 FND_FILE.put_line(fnd_file.log,'Unexpected error occurs while loading addresses');
403 FND_FILE.put_line(fnd_file.log, SQLERRM);
404 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
405 FND_MESSAGE.SET_NAME('AR', 'HZ_API_OTHERS_EXCEP');
406 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
407 FND_MSG_PUB.ADD;
408 FND_MSG_PUB.Count_And_Get(
409 p_encoded => FND_API.G_FALSE,
410 p_count => x_msg_count,
411 p_data => x_msg_data);
412 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
413 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
414 p_msg_data=>x_msg_data,
415 p_msg_type=>'UNEXPECTED ERROR',
416 p_msg_level=>fnd_log.level_error);
417 END IF;
418 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
419 hz_utility_v2pub.debug(p_message=>'ADDR:load_addresses()-',
420 p_prefix=>l_debug_prefix,
421 p_msg_level=>fnd_log.level_procedure);
422 END IF;
423
424 WHEN OTHERS THEN
425 ----dbms_output.put_line('================load_addresses Exception: ' || SQLERRM);
426
427 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
428 hz_utility_v2pub.debug(p_message=>'load_addresses Exception: ',
429 p_prefix=>'ERROR',
430 p_msg_level=>fnd_log.level_error);
431 hz_utility_v2pub.debug(p_message=>SQLERRM,
432 p_prefix=>'ERROR',
433 p_msg_level=>fnd_log.level_error);
434 END IF;
435 ROLLBACK TO load_addresses_pvt;
436 FND_FILE.put_line(fnd_file.log,'Unexpected error occurs while loading addresses');
437 FND_FILE.put_line(fnd_file.log, SQLERRM);
438 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
439 FND_MESSAGE.SET_NAME('AR', 'HZ_API_OTHERS_EXCEP');
440 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
441 FND_MSG_PUB.ADD;
442 FND_MSG_PUB.Count_And_Get(
443 p_encoded => FND_API.G_FALSE,
444 p_count => x_msg_count,
445 p_data => x_msg_data);
446 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
447 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
448 p_msg_data=>x_msg_data,
449 p_msg_type=>'SQL ERROR',
450 p_msg_level=>fnd_log.level_error);
451 END IF;
452 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
453 hz_utility_v2pub.debug(p_message=>'ADDR:load_addresses()-',
454 p_prefix=>l_debug_prefix,
455 p_msg_level=>fnd_log.level_procedure);
456 END IF;
457
458 END load_addresses;
459
460
461 PROCEDURE process_insert_addresses (
462 P_DML_RECORD IN HZ_IMP_LOAD_WRAPPER.DML_RECORD_TYPE,
463 x_return_status OUT NOCOPY VARCHAR2,
464 x_msg_count OUT NOCOPY NUMBER,
465 x_msg_data OUT NOCOPY VARCHAR2
466 ) IS
467 l_sql_query VARCHAR2(20000) :=
468 'begin insert all
469 when (action_mismatch_error is not null
470 and error_flag is null -- e1
471 and address_error is not null -- e2
472 and country_error is not null -- e3
473 and lang_error is not null -- e4
474 and timezone_error is not null -- e5
475 and owner_table_id is not null
476 and flex_val_error is not null -- e6
477 and createdby_error is not null -- e6
478 ) then
479 into hz_party_sites (
480 actual_content_source,
481 party_site_name,
482 request_id,
483 created_by,
484 creation_date,
485 last_updated_by,
486 last_update_date,
487 last_update_login,
488 program_id,
489 program_application_id,
490 program_update_date,
491 application_id,
492 party_site_id,
493 party_id,
494 location_id,
495 party_site_number,
496 orig_system_reference,
497 status,
498 object_version_number,
499 identifying_address_flag,
500 created_by_module,
501 attribute_category,
502 attribute1,
503 attribute2,
504 attribute3,
505 attribute4,
506 attribute5,
507 attribute6,
508 attribute7,
509 attribute8,
510 attribute9,
511 attribute10,
512 attribute11,
513 attribute12,
514 attribute13,
515 attribute14,
516 attribute15,
517 attribute16,
518 attribute17,
519 attribute18,
520 attribute19,
521 attribute20)
522 values (
523 :1,
524 nullif(party_site_name, :2),
525 :3,
526 :4,
527 :5,
528 :4,
529 :5,
530 :6,
531 :7,
532 :8,
533 :5,
534 :9,
535 party_site_id,
536 party_id,
537 hr_locations_s.NextVal,
538 nvl(party_site_number, hz_party_site_number_s.nextval),
539 site_orig_system_reference,
540 ''A'',
541 1,
542 nvl(primary_flag, ''N''),
543 created_by_module,
544 nullif(attr_category, :2),
545 nullif(attr1, :2),
546 nullif(attr2, :2),
547 nullif(attr3, :2),
548 nullif(attr4, :2),
549 nullif(attr5, :2),
550 nullif(attr6, :2),
551 nullif(attr7, :2),
552 nullif(attr8, :2),
553 nullif(attr9, :2),
554 nullif(attr10, :2),
555 nullif(attr11, :2),
556 nullif(attr12, :2),
557 nullif(attr13, :2),
558 nullif(attr14, :2),
559 nullif(attr15, :2),
560 nullif(attr16, :2),
561 nullif(attr17, :2),
562 nullif(attr18, :2),
563 nullif(attr19, :2),
564 nullif(attr20, :2))
565 into hz_orig_sys_references (
566 application_id,
567 created_by,
568 creation_date,
569 last_updated_by,
570 last_update_date,
571 last_update_login,
572 orig_system_ref_id,
573 orig_system,
574 orig_system_reference,
575 owner_table_name,
576 owner_table_id,
577 status,
578 start_date_active,
579 object_version_number,
580 created_by_module,
581 party_id,
582 request_id,
583 program_application_id,
584 program_id,
585 program_update_date)
586 values (
587 :9,
588 :4,
589 :5,
590 :4,
591 :5,
592 :6,
593 hz_orig_system_ref_s.nextval,
594 site_orig_system,
595 site_orig_system_reference,
596 ''HZ_PARTY_SITES'',
597 party_site_id,
598 ''A'',
599 :5,
600 1,
601 created_by_module,
602 party_id,
603 :3,
604 :8,
605 :7,
606 :5)
607 into hz_locations (
608 actual_content_source,
609 application_id,
610 content_source_type,
611 created_by,
612 creation_date,
613 last_updated_by,
614 last_update_date,
615 last_update_login,
616 program_application_id,
617 program_id,
618 program_update_date,
619 request_id,
620 location_id,
621 orig_system_reference,
622 country,
623 address1,
624 address2,
625 address3,
626 address4,
627 city,
628 postal_code,
629 state,
630 province,
631 county,
632 validated_flag,
633 address_lines_phonetic,
634 postal_plus4_code,
635 timezone_id,
636 location_directions,
637 clli_code,
638 language,
639 short_description,
640 description,
641 delivery_point_code,
642 sales_tax_geocode,
643 sales_tax_inside_city_limits,
644 geometry_status_code,
645 object_version_number,
646 validation_status_code,
647 date_validated,
648 created_by_module)
649 values (
650 :1,
651 :9,
652 ''USER_ENTERED'',
653 :4,
654 :5,
655 :4,
656 :5,
657 :6,
658 :8,
659 :7,
660 :5,
661 :3,
662 hr_locations_s.NextVal,
663 site_orig_system_reference,
664 decode(accept_std_flag, ''Y'', country_std, country),
665 decode(accept_std_flag, ''Y'', address1_std, address1),
666 decode(accept_std_flag, ''Y'', address2_std, address2),
667 decode(accept_std_flag, ''Y'', address3_std, address3),
668 decode(accept_std_flag, ''Y'', address4_std, address4),
669 decode(accept_std_flag, ''Y'', city_std, city),
670 decode(accept_std_flag, ''Y'', postal_code_std, postal_code),
671 decode(accept_std_flag, ''Y'', nvl2(province, null, prov_state_admin_code_std), state),
672 decode(accept_std_flag, ''Y'', nvl2(province, prov_state_admin_code_std, null), province),
673 decode(accept_std_flag, ''Y'', county_std, county),
674 ''N'',
675 nullif(address_lines_phonetic, :2),
676 nullif(postal_plus4_code, :2),
677 upgrade_tz_id,
678 nullif(location_directions, :2),
679 nullif(clli_code, :2),
680 nullif(language, :2),
681 nullif(short_description, :2),
682 nullif(description, :2),
683 nullif(delivery_point_code, :2),
684 nullif(sales_tax_geocode, :2),
685 nvl(nullif(sales_tax_inside_city_limits, :2), ''1''),
686 ''DIRTY'',
687 1,
688 decode(accept_std_flag, ''Y'', addr_valid_status_code, null),
689 decode(accept_std_flag, ''Y'', date_validated, null),
690 created_by_module)
691 -- insert ino location profile with user data
692 into hz_location_profiles (
693 actual_content_source,
694 created_by,
695 creation_date,
696 last_updated_by,
697 last_update_date,
698 last_update_login,
699 location_id,
700 location_profile_id,
701 address1,
702 address2,
703 address3,
704 address4,
705 city,
706 prov_state_admin_code,
707 county,
708 country,
709 postal_code,
710 effective_start_date,
711 validation_sst_flag,
712 object_version_number,
713 request_id,
714 program_application_id,
715 program_id,
716 program_update_date)
717 values (
718 :1,
719 :4,
720 :5,
721 :4,
722 :5,
723 :6,
724 hr_locations_s.NextVal,
725 hz_location_profiles_s.nextval,
726 address1,
727 address2,
728 address3,
729 address4,
730 city,
731 nvl(state, province),
732 county,
733 country,
734 postal_code,
735 :5,
736 ''Y'', -- validation_sst_flag
737 1,
738 :3,
739 :8,
740 :7,
741 :5)
742 when (action_mismatch_error is not null
743 and error_flag is null
744 and address_error is not null
745 and country_error is not null
746 and lang_error is not null
747 and timezone_error is not null
748 and owner_table_id is not null
749 and accept_std_flag is not null -- if validated data present
750 and flex_val_error is not null
751 ) then
752 into hz_location_profiles (
753 actual_content_source,
754 created_by,
755 creation_date,
756 last_updated_by,
757 last_update_date,
758 last_update_login,
759 location_id,
760 location_profile_id,
761 address1,
762 address2,
763 address3,
764 address4,
765 city,
766 prov_state_admin_code,
767 county,
768 country,
769 postal_code,
770 effective_start_date,
771 validation_status_code,
772 date_validated,
773 validation_sst_flag,
774 object_version_number,
775 request_id,
776 program_application_id,
777 program_id,
778 program_update_date)
779 values (
780 adapter_content_source,
781 :4,
782 :5,
783 :4,
784 :5,
785 :6,
786 hr_locations_s.NextVal,
787 hz_location_profiles_s.nextval+1,
788 address1_std,
789 address2_std,
790 address3_std,
791 address4_std,
792 city_std,
793 prov_state_admin_code_std,
794 county_std,
795 country_std,
796 postal_code_std,
797 :5,
798 addr_valid_status_code,
799 date_validated,
800 decode(accept_std_flag, ''Y'', ''Y'', ''N''), -- validation_sst_flag
801 1,
802 :3,
803 :8,
804 :7,
805 :5)
806 else
807 into hz_imp_tmp_errors (
808 created_by,
809 creation_date,
810 last_updated_by,
811 last_update_date,
812 last_update_login,
813 program_application_id,
814 program_id,
815 program_update_date,
816 error_id,
817 batch_id,
818 request_id,
819 int_row_id,
820 interface_table_name,
821 ACTION_MISMATCH_FLAG,
822 MISSING_PARENT_FLAG,
823 e1_flag,
824 e2_flag,
825 e3_flag,
826 e4_flag,
827 e5_flag,
828 e6_flag,
829 e7_flag,
830 e8_flag,
831 e9_flag/* bug 4079902 */,
832 e10_flag,
833 e11_flag)
834 values (
835 :4,
836 :5,
837 :4,
838 :5,
839 :6,
840 :8,
841 :7,
842 :5,
843 hz_imp_errors_s.nextval,
844 :10,
845 :3,
846 row_id,
847 ''HZ_IMP_ADDRESSES_INT'',
848 action_mismatch_error,
849 nvl2(owner_table_id, ''Y'', null),
850 nvl2(error_flag, DECODE(error_flag,3,''Y'', null), ''Y''),
851 address_error,
852 country_error,
853 lang_error,
854 timezone_error,
855 flex_val_error,
856 ''Y'',
857 ''Y'',
858 ''Y'',
859 nvl2(error_flag, DECODE(error_flag,2,''Y'', null), ''Y''),
860 createdby_error)
861 select /*+ leading(site_sg) use_nl(site_int) rowid(site_int) use_nl(timezone) */
862 hp.party_id owner_table_id,
863 site_int.addr_valid_status_code,
864 site_int.date_validated,
865 site_int.rowid row_id,
866 site_sg.party_site_id,
867 site_int.party_site_number,
868 site_int.party_site_name,
869 site_sg.party_id,
870 site_int.site_orig_system,
871 site_int.site_orig_system_reference,
872 site_int.country,
873 site_int.country_std,
874 site_int.address1,
875 nullif(site_int.address2, :2) address2,
876 nullif(site_int.address3, :2) address3,
877 nullif(site_int.address4, :2) address4,
878 site_int.address1_std,
879 site_int.address2_std,
880 site_int.address3_std,
881 site_int.address4_std,
882 nullif(site_int.city, :2) city,
883 site_int.city_std,
884 nullif(site_int.postal_code, :2) postal_code,
885 site_int.postal_code_std,
886 nullif(site_int.state, :2) state,
887 site_int.prov_state_admin_code_std,
888 site_int.province,
889 nullif(site_int.county, :2) county,
890 site_int.county_std,
891 site_int.address_lines_phonetic,
892 site_int.postal_plus4_code,
893 timezone.upgrade_tz_id,
894 site_int.location_directions,
895 site_int.clli_code,
896 site_int.language,
897 site_int.short_description,
898 site_int.description,
899 site_int.delivery_point_code,
900 site_int.sales_tax_geocode,
901 site_int.sales_tax_inside_city_limits,
902 nvl(nullif(site_int.created_by_module, :2), ''HZ_IMPORT'') created_by_module,
903 site_int.last_updated_by,
904 site_int.accept_standardized_flag accept_std_flag,
905 site_int.adapter_content_source adapter_content_source,
906 site_int.attribute_category attr_category,
907 site_int.attribute1 attr1,
908 site_int.attribute2 attr2,
909 site_int.attribute3 attr3,
910 site_int.attribute4 attr4,
911 site_int.attribute5 attr5,
912 site_int.attribute6 attr6,
913 site_int.attribute7 attr7,
914 site_int.attribute8 attr8,
915 site_int.attribute9 attr9,
916 site_int.attribute10 attr10,
917 site_int.attribute11 attr11,
918 site_int.attribute12 attr12,
919 site_int.attribute13 attr13,
920 site_int.attribute14 attr14,
921 site_int.attribute15 attr15,
922 site_int.attribute16 attr16,
923 site_int.attribute17 attr17,
924 site_int.attribute18 attr18,
925 site_int.attribute19 attr19,
926 site_int.attribute20 attr20,
927 nvl2(nullif(site_int.address1, :2),
928 nvl2(site_int.accept_standardized_flag, nvl2(nullif(site_int.address1_std, :2), ''Y'' ,null), ''Y'')
929 , null) address_error,
930 nvl2(nullif(site_int.country, :2),
931 nvl2(fnd_terr.territory_code,
932 nvl2(site_int.accept_standardized_flag,
933 nvl2(nullif(site_int.country_std, :2),
934 nvl2(fnd_terr2.territory_code, ''Y'', null),
935 null), ''Y''),
936 null), null) country_error,
937 nvl2(nullif(site_int.language, :2), nvl2(fnd_lang.language_code, ''Y'', null), ''Y'') lang_error,
938 nvl2(nullif(site_int.timezone_code, :2), nvl2(timezone.timezone_code, ''Y'', null), ''Y'') timezone_error,
939 nvl2(nullif(nullif(site_int.insert_update_flag, :2), site_sg.action_flag), null, ''Y'') action_mismatch_error,
940 site_sg.error_flag,
941 site_sg.primary_flag primary_flag,
942 nvl2(nullif(site_int.created_by_module, :2), nvl2(createdby_l.lookup_code, ''Y'', null), ''Y'') createdby_error,
943 decode(:11, ''Y'',
944 HZ_IMP_LOAD_ADDRESSES_PKG.validate_desc_flexfield_f(
945 site_int.attribute_category, site_int.attribute1, site_int.attribute2, site_int.attribute3, site_int.attribute4,
946 site_int.attribute5, site_int.attribute6, site_int.attribute7, site_int.attribute8, site_int.attribute9,
947 site_int.attribute10, site_int.attribute11, site_int.attribute12, site_int.attribute13, site_int.attribute14,
948 site_int.attribute15, site_int.attribute16, site_int.attribute17, site_int.attribute18, site_int.attribute19,
949 site_int.attribute20, :5, :2
950 ), ''T'') flex_val_error
951 FROM HZ_IMP_ADDRESSES_INT site_int,
952 HZ_IMP_ADDRESSES_SG site_sg,
953 FND_TERRITORIES fnd_terr,
954 FND_TERRITORIES fnd_terr2,
955 (select language_code
956 from FND_LANGUAGES
957 where installed_flag in (''B'', ''I'')) fnd_lang,
958 fnd_timezones_b timezone,
959 HZ_PARTIES hp,
960 fnd_lookup_values createdby_l
961 WHERE hp.party_id (+) = site_sg.party_id
962 AND hp.status (+) = ''A''
963 AND site_sg.action_flag = ''I''
964 AND site_sg.party_orig_system = :12
965 AND site_sg.party_orig_system_reference between :13 AND :14
966 AND site_int.rowid = site_sg.int_row_id
967 and site_sg.batch_id = :10
968 and site_sg.batch_mode_flag = :15
969 AND fnd_terr.territory_code (+) = nullif(site_int.country, :2)
970 AND fnd_terr2.territory_code (+) = nullif(site_int.country_std, :2)
971 AND fnd_lang.language_code (+) = site_int.language
972 --AND fnd_lang.installed_flag (+) in (''B'', ''I'')
973 and createdby_l.lookup_code (+) = site_int.created_by_module
974 and createdby_l.lookup_type (+) = ''HZ_CREATED_BY_MODULES''
975 and createdby_l.language (+) = userenv(''LANG'')
976 and createdby_l.view_application_id (+) = 222
977 and createdby_l.security_group_id (+) =
978 fnd_global.lookup_security_group(''HZ_CREATED_BY_MODULES'', 222)
979 AND timezone.timezone_code (+) = site_int.timezone_code';
980
981 l_where_enabled_lookup_sql varchar2(4000) :=
982 'AND ( createdby_l.ENABLED_FLAG(+) = ''Y'' AND
983 TRUNC(:5) BETWEEN
984 TRUNC(NVL( createdby_l.START_DATE_ACTIVE,:5 ) ) AND
985 TRUNC(NVL( createdby_l.END_DATE_ACTIVE,:5 ) ) )';
986
987 l_sql_query_end varchar2(15000):= '; end;';
988 l_first_run_clause varchar2(40) := ' AND site_int.interface_status is null';
989 l_re_run_clause varchar2(40) := ' AND site_int.interface_status = ''C''';
990 --l_where_enabled_lookup_sql varchar2(3000) := ' AND ( timezone.ENABLED_FLAG(+) = ''Y'' )';
991 l_final_qry varchar2(20000);
992 primary_flag_err_cursor pri_flag_cursor_type;
993 de_norm_cursor de_norm_cursor_type;
994 l_debug_prefix VARCHAR2(30) := '';
995 BEGIN
996
997 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
998 hz_utility_v2pub.debug(p_message=>'ADDR:process_insert_addresses()+',
999 p_prefix=>l_debug_prefix,
1000 p_msg_level=>fnd_log.level_procedure);
1001 END IF;
1002
1003 savepoint process_insert_addresses_pvt;
1004 x_return_status := FND_API.G_RET_STS_SUCCESS;
1005
1006 -- add clause for first run/re-run
1007 if(P_DML_RECORD.RERUN='N') then
1008 l_final_qry := l_sql_query || l_first_run_clause;
1009 else
1010 l_final_qry := l_sql_query || l_re_run_clause;
1011 end if;
1012
1013 -- add clause for filtering out disabled lookup
1014
1015 if P_DML_RECORD.ALLOW_DISABLED_LOOKUP <> 'Y' then
1016 l_final_qry := l_final_qry || l_where_enabled_lookup_sql;
1017 end if;
1018
1019 l_final_qry := l_final_qry || l_sql_query_end;
1020
1021 execute immediate l_final_qry using
1022 P_DML_RECORD.ACTUAL_CONTENT_SRC, P_DML_RECORD.GMISS_CHAR,
1023 P_DML_RECORD.REQUEST_ID, P_DML_RECORD.USER_ID, P_DML_RECORD.SYSDATE,
1024 P_DML_RECORD.LAST_UPDATE_LOGIN, P_DML_RECORD.PROGRAM_ID,
1025 P_DML_RECORD.PROGRAM_APPLICATION_ID, P_DML_RECORD.APPLICATION_ID,
1026 P_DML_RECORD.BATCH_ID, P_DML_RECORD.flex_validation,P_DML_RECORD.OS,
1027 P_DML_RECORD.FROM_OSR, P_DML_RECORD.TO_OSR, P_DML_RECORD.BATCH_MODE_FLAG;
1028
1029 /* DE-NORM */
1030 /* for all the failed record of primary_flag = 'Y', update the party with */
1031 /* the next available address */
1032 OPEN primary_flag_err_cursor FOR
1033 'select
1034 party_id,
1035 party_site_id
1036 from
1037 (
1038 select
1039 party_id,party_site_id,
1040 rank() over (partition by all_site_ids.party_id
1041 order by all_site_ids.party_site_id) new_rank
1042 from
1043 (
1044 select addr_sg.party_id,
1045 hz_ps.party_site_id
1046 from HZ_IMP_TMP_ERRORS err_table,
1047 hz_imp_addresses_sg addr_sg,
1048 hz_party_sites hz_ps
1049 where err_table.request_id = :request_id
1050 and interface_table_name = ''HZ_IMP_ADDRESSES_INT''
1051 and addr_sg.batch_id = :batch_id
1052 and addr_sg.batch_mode_flag = :batch_mode_flag
1053 and addr_sg.party_orig_system = :orig_system
1054 and addr_sg.party_orig_system_reference between :from_osr and :to_osr
1055 and addr_sg.primary_flag = ''Y''
1056 and addr_sg.int_row_id = err_table.int_row_id
1057 and addr_sg.action_flag = ''I''
1058 and hz_ps.party_id (+) = addr_sg.party_id
1059 and addr_sg.party_id is not null
1060 ) all_site_ids
1061 )
1062 where new_rank = 1'
1063 using P_DML_RECORD.REQUEST_ID,P_DML_RECORD.BATCH_ID,--P_DML_RECORD.BATCH_ID,
1064 P_DML_RECORD.BATCH_MODE_FLAG, P_DML_RECORD.OS,
1065 P_DML_RECORD.FROM_OSR, P_DML_RECORD.TO_OSR;
1066
1067 fetch primary_flag_err_cursor BULK COLLECT INTO
1068 l_error_party_id, l_error_site_id;
1069 close primary_flag_err_cursor;
1070
1071 forall i in 1..l_error_party_id.count
1072 update hz_parties hz_pty
1073 set ( address1, address2, address3, address4,
1074 country, county, city, state, province,
1075 postal_code ) =
1076 ( select address1, address2, address3, address4,
1077 country, county, city, state, province,
1078 postal_code
1079 from hz_party_sites hz_ps,
1080 hz_locations hz_loc
1081 where hz_ps.location_id = hz_loc.location_id
1082 and hz_ps.party_site_id = l_error_site_id(i)
1083 union -- nullify if no next available address
1084 select null,null,null,null,null,
1085 null,null,null,null,null
1086 from dual
1087 where l_error_site_id(i) is null
1088 ),
1089 object_version_number = object_version_number + 1,
1090 last_update_date = P_DML_RECORD.SYSDATE,
1091 last_updated_by = P_DML_RECORD.USER_ID,
1092 last_update_login = P_DML_RECORD.LAST_UPDATE_LOGIN,
1093 program_update_date = P_DML_RECORD.SYSDATE
1094 where hz_pty.party_id = l_error_party_id(i);
1095
1096 forall i in 1..l_error_party_id.count
1097 update hz_party_sites
1098 set identifying_address_flag = 'Y',
1099 object_version_number = object_version_number + 1,
1100 last_update_date = P_DML_RECORD.SYSDATE,
1101 last_updated_by = P_DML_RECORD.USER_ID,
1102 last_update_login = P_DML_RECORD.LAST_UPDATE_LOGIN,
1103 program_update_date = P_DML_RECORD.SYSDATE
1104 where party_site_id = l_error_site_id(i);
1105
1106 /* de-norm the primary address to parties */
1107 /* Note: for error case, the party site with the id will just be not found */
1108 /* in update. Not necessary to filter out here. */
1109
1110 /* bug fix 3851810 */
1111 /* If DNB is not selected as a visible data soruce, we should not */
1112 /* denormalize it even it is the first active address created for the */
1113 /* party. We should only denormalize the visible address. */
1114
1115 -- check if the data source is seleted.
1116
1117 /* Commented the code for bug 4079902. */
1118
1119 /*
1120 HZ_MIXNM_UTILITY.LoadDataSources(
1121 p_entity_name => 'HZ_LOCATIONS',
1122 p_entity_attr_id => g_pst_entity_attr_id,
1123 p_mixnmatch_enabled => g_pst_mixnmatch_enabled,
1124 p_selected_datasources => g_pst_selected_datasources );
1125
1126 g_pst_is_datasource_selected :=
1127 HZ_MIXNM_UTILITY.isDataSourceSelected (
1128 p_selected_datasources => g_pst_selected_datasources,
1129 p_actual_content_source => p_dml_record.actual_content_src );
1130
1131
1132 IF g_pst_is_datasource_selected = 'Y' THEN
1133 */
1134 OPEN de_norm_cursor FOR
1135 'select addr_sg.party_id, addr_sg.party_site_id
1136 from hz_imp_addresses_sg addr_sg
1137 where addr_sg.batch_id = :batch_id
1138 and addr_sg.batch_mode_flag = :batch_mode_flag
1139 and addr_sg.party_orig_system = :orig_system
1140 and addr_sg.party_orig_system_reference
1141 between :from_osr and :to_osr
1142 and addr_sg.primary_flag = ''Y''
1143 and addr_sg.action_flag = ''I''
1144 and addr_sg.party_action_flag = ''U''
1145 '
1146 using P_DML_RECORD.BATCH_ID,
1147 P_DML_RECORD.BATCH_MODE_FLAG, P_DML_RECORD.OS,
1148 P_DML_RECORD.FROM_OSR, P_DML_RECORD.TO_OSR;
1149
1150 fetch de_norm_cursor BULK COLLECT INTO
1151 l_update_party_id, l_update_site_id;
1152 close de_norm_cursor;
1153
1154 forall i in 1..l_update_party_id.count
1155 update hz_parties hz_pty
1156 set ( address1, address2, address3, address4,
1157 country, county, city, state, province,
1158 postal_code
1159 ) =
1160 ( select address1, address2, address3, address4,
1161 country, county, city, state, province,
1162 postal_code
1163 from hz_party_sites hz_ps,
1164 hz_locations hz_loc
1165 where hz_ps.location_id = hz_loc.location_id
1166 and hz_ps.party_site_id = l_update_site_id(i)
1167 ),
1168 object_version_number = object_version_number + 1,
1169 last_update_date = P_DML_RECORD.SYSDATE,
1170 last_updated_by = P_DML_RECORD.USER_ID,
1171 last_update_login = P_DML_RECORD.LAST_UPDATE_LOGIN,
1172 program_update_date = P_DML_RECORD.SYSDATE
1173 where hz_pty.party_id = l_update_party_id(i);
1174
1175 -- END IF;
1176
1177 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
1178 hz_utility_v2pub.debug(p_message=>'ADDR:process_insert_addresses()-',
1179 p_prefix=>l_debug_prefix,
1180 p_msg_level=>fnd_log.level_procedure);
1181 END IF;
1182
1183 return;
1184 exception
1185 when DUP_VAL_ON_INDEX then
1186 ----dbms_output.put_line('=================dup val exception');
1187 ----dbms_output.put_line(sqlerrm);
1188 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Insert addresses dup val exception: ' || SQLERRM);
1189 ROLLBACK to process_insert_addresses_pvt;
1190
1191 populate_error_table(P_DML_RECORD, 'Y', sqlerrm);
1192 x_return_status := FND_API.G_RET_STS_ERROR;
1193
1194 FND_MESSAGE.SET_NAME('AR', 'HZ_API_OTHERS_EXCEP');
1195 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
1196 FND_MSG_PUB.ADD;
1197 when others then
1198 ----dbms_output.put_line('===================other exception');
1199 ----dbms_output.put_line(sqlerrm);
1200
1201 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Insert addresses other exception: ' || SQLERRM);
1202 ROLLBACK to process_insert_addresses_pvt;
1203
1204 populate_error_table(P_DML_RECORD, 'N', sqlerrm);
1205 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1206
1207 FND_MESSAGE.SET_NAME('AR', 'HZ_API_OTHERS_EXCEP');
1208 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
1209 FND_MSG_PUB.ADD;
1210
1211 end process_insert_addresses;
1212
1213
1214 PROCEDURE populate_error_table(
1215 P_DML_RECORD IN HZ_IMP_LOAD_WRAPPER.DML_RECORD_TYPE,
1216 P_DUP_VAL_EXP IN VARCHAR2,
1217 P_SQL_ERRM IN VARCHAR2 ) IS
1218
1219 dup_val_exp_val VARCHAR2(1) := null;
1220 other_exp_val VARCHAR2(1) := 'Y';
1221 l_debug_prefix VARCHAR2(30) := '';
1222 BEGIN
1223
1224 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
1225 hz_utility_v2pub.debug(p_message=>'ADDR:populate_error_table()+',
1226 p_prefix=>l_debug_prefix,
1227 p_msg_level=>fnd_log.level_procedure);
1228 END IF;
1229
1230 /* other entities need to add checking for other constraints */
1231 if (P_DUP_VAL_EXP = 'Y') then
1232 other_exp_val := null;
1233 if(instr(P_SQL_ERRM, 'PARTY_SITES_U1')<>0) then
1234 dup_val_exp_val := 'A';
1235 elsif(instr(P_SQL_ERRM, 'PARTY_SITES_U2')<>0) then
1236 dup_val_exp_val := 'B';
1237 else -- '_U2'
1238 dup_val_exp_val := 'C';
1239 end if;
1240 end if;
1241
1242 insert into hz_imp_tmp_errors
1243 (
1244 request_id,
1245 batch_id,
1246 int_row_id,
1247 interface_table_name,
1248 error_id,
1249 creation_date,
1250 created_by,
1251 last_update_date,
1252 last_updated_by,
1253 last_update_login,
1254 program_application_id,
1255 program_id,
1256 program_update_date,
1257 DUP_VAL_IDX_EXCEP_FLAG,
1258 OTHER_EXCEP_FLAG, missing_parent_flag,
1259 e1_flag,e2_flag,e3_flag,e4_flag,e5_flag,e6_flag,e7_flag,e9_flag,e8_flag,e10_flag,
1260 e11_flag
1261 )
1262 (
1263 select P_DML_RECORD.REQUEST_ID,
1264 P_DML_RECORD.BATCH_ID,
1265 fr_sg.int_row_id,
1266 'HZ_IMP_ADDRESSES_INT',
1267 hz_imp_errors_s.NextVal,
1268 P_DML_RECORD.SYSDATE,
1269 P_DML_RECORD.USER_ID,
1270 P_DML_RECORD.SYSDATE,
1271 P_DML_RECORD.USER_ID,
1272 P_DML_RECORD.LAST_UPDATE_LOGIN,
1273 P_DML_RECORD.PROGRAM_APPLICATION_ID,
1274 P_DML_RECORD.PROGRAM_ID,
1275 P_DML_RECORD.SYSDATE,
1276 dup_val_exp_val,
1277 other_exp_val, 'Y',
1278 'Y','Y','Y','Y','Y','Y','Y','Y','Y','Y',
1279 'Y'
1280 from hz_imp_addresses_sg fr_sg
1281 where fr_sg.action_flag = 'I'
1282 and fr_sg.batch_id = P_DML_RECORD.BATCH_ID
1283 and fr_sg.party_orig_system = P_DML_RECORD.OS
1284 and fr_sg.party_orig_system_reference
1285 between P_DML_RECORD.FROM_OSR and P_DML_RECORD.TO_OSR
1286 );
1287
1288 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
1289 hz_utility_v2pub.debug(p_message=>'ADDR:populate_error_table()-',
1290 p_prefix=>l_debug_prefix,
1291 p_msg_level=>fnd_log.level_procedure);
1292 END IF;
1293
1294 END populate_error_table;
1295
1296
1297 PROCEDURE open_update_cursor (update_cursor IN OUT NOCOPY update_cursor_type,
1298 P_DML_RECORD IN HZ_IMP_LOAD_WRAPPER.DML_RECORD_TYPE
1299 ) IS
1300
1301 /* Note: Is it a problem to generate foreign key party site, location id */
1302 /* here for move? Many ids from sequence would be lost. */
1303 l_sql_query VARCHAR2(20000) :=
1304 'SELECT hz_loc.ADDRESS_LINES_PHONETIC,
1305 hz_loc.POSTAL_PLUS4_CODE,
1306 hz_loc.LOCATION_DIRECTIONS,
1307 hz_loc.CLLI_CODE,
1308 hz_loc.LANGUAGE,
1309 hz_loc.SHORT_DESCRIPTION,
1310 hz_loc.DESCRIPTION,
1311 hz_loc.DELIVERY_POINT_CODE,
1312 hz_loc.SALES_TAX_GEOCODE,
1313 hz_loc.SALES_TAX_INSIDE_CITY_LIMITS,
1314 0 flex_val_errors,
1315 ''T'' dss_security_errors,
1316 hz_loc.validation_status_code,
1317 mosr.owner_table_id,
1318 nvl2(nullif(mosr2.party_id,site_sg.party_id),decode(hz_ps1.Identifying_address_flag,
1319 ''Y'',''N'',''Y''),hz_ps.IDENTIFYING_ADDRESS_FLAG)
1320 identifying_address_flag,
1321 mosr2.party_id owning_party_id,
1322 site_int.addr_valid_status_code,
1323 hz_loc.validation_status_code,
1324 site_int.date_validated,
1325 site_int.CORRECT_MOVE_INDICATOR,
1326 site_sg.action_flag,
1327 site_int.ROWID,
1328 hz_ps.location_id,
1329 hr_locations_s.NextVal,
1330 site_sg.party_site_id,
1331 hz_party_sites_s.NextVal,
1332 site_int.party_site_number,
1333 site_int.party_site_name,
1334 site_sg.party_id,
1335 site_int.site_orig_system,
1336 site_int.site_orig_system_reference,
1337 site_sg.old_site_orig_system_ref,
1338 site_int.country,
1339 site_int.country_std,
1340 site_int.address1, site_int.address2, site_int.address3, site_int.address4,
1341 site_int.address1_std, site_int.address2_std, site_int.address3_std, site_int.address4_std,
1342 site_int.city, site_int.city_std, site_int.postal_code, site_int.postal_code_std,
1343 site_int.state, site_int.prov_state_admin_code_std,
1344 site_int.PROVINCE, site_int.county, site_int.county_std,
1345 hz_loc.country,
1346 hz_loc.address1,hz_loc.address2,hz_loc.address3,hz_loc.address4,
1347 hz_loc.city, hz_loc.postal_code,
1348 hz_loc.state, hz_loc.province, hz_loc.county,
1349 site_int.ADDRESS_LINES_PHONETIC,
1350 site_int.POSTAL_PLUS4_CODE,
1351 hz_loc.time_zone,
1352 timezone.UPGRADE_TZ_ID,
1353 site_int.TIMEZONE_CODE,
1354 site_int.location_directions,
1355 site_int.clli_code,
1356 site_int.language,
1357 site_int.short_description,
1358 site_int.description,
1359 site_int.delivery_point_code,
1360 site_int.SALES_TAX_GEOCODE,
1361 site_int.SALES_TAX_INSIDE_CITY_LIMITS,
1362 site_int.CREATED_BY_MODULE,
1363 site_int.LAST_UPDATED_BY,
1364 site_int.ACCEPT_STANDARDIZED_FLAG,
1365 site_int.ADAPTER_CONTENT_SOURCE,
1366 site_int.attribute_category, site_int.attribute1, site_int.attribute2,
1367 site_int.attribute3, site_int.attribute4, site_int.attribute5,
1368 site_int.attribute6, site_int.attribute7, site_int.attribute8,
1369 site_int.attribute9, site_int.attribute10, site_int.attribute11,
1370 site_int.attribute12, site_int.attribute13, site_int.attribute14,
1371 site_int.attribute15, site_int.attribute16, site_int.attribute17,
1372 site_int.attribute18, site_int.attribute19, site_int.attribute20,
1373 hz_loc.attribute_category, hz_loc.attribute1, hz_loc.attribute2,
1374 hz_loc.attribute3, hz_loc.attribute4, hz_loc.attribute5,
1375 hz_loc.attribute6, hz_loc.attribute7, hz_loc.attribute8,
1376 hz_loc.attribute9, hz_loc.attribute10, hz_loc.attribute11,
1377 hz_loc.attribute12, hz_loc.attribute13, hz_loc.attribute14,
1378 hz_loc.attribute15, hz_loc.attribute16, hz_loc.attribute17,
1379 hz_loc.attribute18, hz_loc.attribute19, hz_loc.attribute20,
1380 site_sg.NEW_OSR_EXISTS_FLAG,
1381 decode(site_int.state,
1382 null, decode(hz_loc.state,
1383 null, decode(site_int.province, null, hz_loc.province, :GMISS_CHAR, null, site_int.province),hz_loc.state),
1384 :GMISS_CHAR, decode(site_int.province, null, hz_loc.province, :GMISS_CHAR, null, site_int.province),
1385 site_int.state) ps_admin_code,
1386
1387 nvl2(nullif(site_int.address1, :GMISS_CHAR),
1388 nvl2(site_int.accept_standardized_flag, nvl2(nullif(site_int.address1_std, :GMISS_CHAR), ''Y'' ,null), ''Y'')
1389 , null) address_error,
1390
1391 nvl2(nullif(site_int.country, :GMISS_CHAR),
1392 nvl2(fnd_terr.territory_code,
1393 nvl2(site_int.accept_standardized_flag,
1394 nvl2(nullif(site_int.country_std, :GMISS_CHAR),
1395 nvl2(fnd_terr2.territory_code, ''Y'', null),
1396 null), ''Y''),
1397 null), null) country_error,
1398
1399 decode(site_int.language, null, ''Y'', :GMISS_CHAR, ''Y'', fnd_lang.language_code) lang_error,
1400 decode(site_int.timezone_code, null, ''Y'', :GMISS_CHAR, ''Y'', timezone.timezone_code) timezone_error,
1401 decode(nvl(site_int.insert_update_flag, site_sg.action_flag), site_sg.action_flag, ''Y'', null) action_mismatch_error,
1402 site_sg.error_flag,
1403 decode(site_int.ACCEPT_STANDARDIZED_FLAG, ''Y'',
1404 decode(hz_loc.country, site_int.country_std,
1405 decode(nvl(hz_loc.state,hz_loc.province), site_int.prov_state_admin_code_std,
1406 decode(hz_loc.county, site_int.county_std,
1407 decode(hz_loc.city, site_int.city_std,
1408 decode(hz_loc.postal_code, site_int.postal_code_std,
1409 decode(hz_loc.address1, site_int.address1_std,
1410 decode(hz_loc.address2, site_int.address2_std,
1411 decode(hz_loc.address3, site_int.address3_std,
1412 decode(hz_loc.address4, site_int.address4_std,
1413 null, ''Y''), ''Y''), ''Y''), ''Y''), ''Y''), ''Y''), ''Y''), ''Y''), ''Y''),
1414 decode(hz_loc.country,
1415 decode(site_int.country, :GMISS_CHAR, null, null, hz_loc.country, site_int.country),
1416 decode(hz_loc.state,
1417 decode(site_int.state, :GMISS_CHAR, null, null, hz_loc.state, site_int.state),
1418 decode(hz_loc.province,
1419 decode(site_int.province, :GMISS_CHAR, null, null, hz_loc.province, site_int.province),
1420 decode(hz_loc.county,
1421 decode(site_int.county, :GMISS_CHAR, null, null, hz_loc.county, site_int.county),
1422 decode(hz_loc.city,
1423 decode(site_int.city, :GMISS_CHAR, null, null, hz_loc.city, site_int.city),
1424 decode(hz_loc.postal_code,
1425 decode(site_int.postal_code, :GMISS_CHAR, null, null, hz_loc.postal_code, site_int.postal_code),
1426 decode(hz_loc.address1,
1427 decode(site_int.address1, :GMISS_CHAR, null, null, hz_loc.address1, site_int.address1),
1428 decode(hz_loc.address2,
1429 decode(site_int.address2, :GMISS_CHAR, null, null, hz_loc.address2, site_int.address2),
1430 decode(hz_loc.address3,
1431 decode(site_int.address3, :GMISS_CHAR, null, null, hz_loc.address3, site_int.address3),
1432 decode(hz_loc.address4,
1433 decode(site_int.address4, :GMISS_CHAR, null, null, hz_loc.address4, site_int.address4),
1434 null, ''Y''), ''Y''), ''Y''), ''Y''), ''Y''), ''Y''), ''Y''), ''Y''), ''Y''), ''Y'')) addr_ch_flag,
1435 decode(site_int.ACCEPT_STANDARDIZED_FLAG, ''Y'',
1436 decode(hz_loc.country, site_int.country_std,
1437 decode(nvl(hz_loc.state,hz_loc.province), site_int.prov_state_admin_code_std,
1438 decode(hz_loc.county, site_int.county_std,
1439 decode(hz_loc.city, site_int.city_std,
1440 decode(hz_loc.postal_code, site_int.postal_code_std,
1441 null, ''Y''), ''Y''), ''Y''), ''Y''), ''Y''),
1442 decode(hz_loc.country,
1443 decode(site_int.country, :GMISS_CHAR, null, null, hz_loc.country, site_int.country),
1444 decode(hz_loc.state,
1445 decode(site_int.state, :GMISS_CHAR, null, null, hz_loc.state, site_int.state),
1446 decode(hz_loc.province,
1447 decode(site_int.province, :GMISS_CHAR, null, null, hz_loc.province, site_int.province),
1448 decode(hz_loc.county,
1449 decode(site_int.county, :GMISS_CHAR, null, null, hz_loc.county, site_int.county),
1450 decode(hz_loc.city,
1451 decode(site_int.city, :GMISS_CHAR, null, null, hz_loc.city, site_int.city),
1452 decode(hz_loc.postal_code,
1453 decode(site_int.postal_code, :GMISS_CHAR, null, null, hz_loc.postal_code, site_int.postal_code),
1454 null, ''Y''), ''Y''), ''Y''), ''Y''), ''Y''), ''Y'')) tax_ch_flag,
1455 hz_ps.identifying_address_flag primary_flag,
1456 /* bug 4079902 */
1457 nvl2(nullif(hz_ps.actual_content_source,:l_os),
1458 nvl2(nullif(hos.orig_system_type,''PURCHASED''),''Y'',null),
1459 ''Y'') third_party_update_error,
1460 nvl2(nullif(site_int.created_by_module,:GMISS_CHAR),
1461 decode(site_int.CORRECT_MOVE_INDICATOR,
1462 ''M'',nvl2(createdby_l.lookup_code,''Y'',null),
1463 nvl2(site_sg.new_osr_exists_flag,
1464 nvl2(nullif(site_int.site_orig_system_reference,site_sg.old_site_orig_system_ref),
1465 nvl2(createdby_l.lookup_code,''Y'',null),
1466 ''Y''
1467 ),
1468 ''Y''
1469 )
1470 ),
1471 ''Y'') createdby_error
1472
1473 FROM HZ_IMP_ADDRESSES_INT site_int,
1474 HZ_IMP_ADDRESSES_SG site_sg,
1475 FND_TERRITORIES fnd_terr,
1476 FND_TERRITORIES fnd_terr2,
1477 ( select language_code
1478 from FND_LANGUAGES
1479 where installed_flag in (''B'', ''I'')
1480 ) fnd_lang,
1481 fnd_timezones_b timezone,
1482 hz_party_sites hz_ps,
1483 hz_locations hz_loc,
1484 HZ_ORIG_SYS_REFERENCES mosr,
1485 HZ_ORIG_SYSTEMS_B hos,
1486 hz_party_sites hz_ps1,
1487 HZ_ORIG_SYS_REFERENCES mosr2,
1488 fnd_lookup_values createdby_l
1489
1490 WHERE mosr.orig_system (+) = site_sg.party_orig_system
1491 AND mosr.orig_system_reference (+) = site_sg.party_orig_system_reference
1492 AND mosr.status (+) = ''A''
1493 AND mosr.owner_table_name (+) = ''HZ_PARTIES''
1494 AND mosr.owner_table_id (+) = site_sg.party_id
1495 AND site_sg.action_flag = ''U''
1496 AND site_int.batch_id = :CP_BATCH_ID
1497 AND site_sg.batch_id = :CP_BATCH_ID
1498 AND site_sg.batch_mode_flag = :CP_BATCH_MODE_FLAG
1499 AND site_sg.party_orig_system = :CP_OS
1500 AND site_sg.party_orig_system_reference between :CP_FROM_OSR AND :CP_TO_OSR
1501 AND site_int.rowid = site_sg.int_row_id
1502
1503 AND fnd_terr.territory_code (+) = nullif(site_int.country, :GMSSS_CHAR)
1504 AND fnd_terr2.territory_code (+) = nullif(site_int.country_std, :GMSSS_CHAR)
1505
1506 AND fnd_lang.language_code (+) = site_int.language
1507 AND timezone.timezone_code (+) = site_int.timezone_code
1508 AND hz_ps.party_site_id = site_sg.party_site_id
1509 AND hz_loc.location_id = hz_ps.location_id
1510 AND hz_ps.actual_content_source=hos.orig_system
1511 AND hz_ps1.party_id(+) = site_sg.party_id
1512 AND hz_ps1.status(+) = ''A''
1513 AND hz_ps1.identifying_address_flag(+) =''Y''
1514 AND mosr2.orig_system (+) = site_sg.site_orig_system
1515 AND mosr2.orig_system_reference (+) = site_sg.site_orig_system_reference
1516 AND mosr2.status (+) = ''A''
1517 AND mosr2.owner_table_name (+) = ''HZ_PARTY_SITES''
1518 AND mosr2.owner_table_id (+) = site_sg.party_site_id
1519 AND createdby_l.lookup_code (+) = site_int.created_by_module
1520 AND createdby_l.lookup_type (+) = ''HZ_CREATED_BY_MODULES''
1521 AND createdby_l.language (+) = userenv(''LANG'')
1522 AND createdby_l.view_application_id (+) = 222
1523 AND createdby_l.security_group_id (+) =
1524 fnd_global.lookup_security_group(''HZ_CREATED_BY_MODULES'', 222)
1525 ';
1526
1527 l_sql_query_w_prf VARCHAR2(20000) :=
1528 'SELECT --hz_loc_prf.validation_status_code,
1529 ( select validation_sst_flag
1530 from hz_location_profiles hz_loc_prf
1531 where hz_loc_prf.actual_content_source = site_int.ADAPTER_CONTENT_SOURCE
1532 and hz_loc_prf.location_id = hz_loc.location_id
1533 and nullif(EFFECTIVE_END_DATE, :CP_END_DATE) is null
1534 ) old_prf_sst,
1535 hz_loc.ADDRESS_LINES_PHONETIC,
1536 hz_loc.POSTAL_PLUS4_CODE,
1537 hz_loc.LOCATION_DIRECTIONS,
1538 hz_loc.CLLI_CODE,
1539 hz_loc.LANGUAGE,
1540 hz_loc.SHORT_DESCRIPTION,
1541 hz_loc.DESCRIPTION,
1542 hz_loc.DELIVERY_POINT_CODE,
1543 hz_loc.SALES_TAX_GEOCODE,
1544 hz_loc.SALES_TAX_INSIDE_CITY_LIMITS,
1545 0 flex_val_errors,
1546 ''T'' dss_security_errors,
1547 hz_loc.validation_status_code,
1548 mosr.owner_table_id,
1549 nvl2(nullif(mosr2.party_id,site_sg.party_id),decode(hz_ps1.Identifying_address_flag,
1550 ''Y'',''N'',''Y''),hz_ps.IDENTIFYING_ADDRESS_FLAG)
1551 identifying_address_flag,
1552 mosr2.party_id owning_party_id,
1553 site_int.addr_valid_status_code,
1554 hz_loc.validation_status_code,
1555 site_int.date_validated,
1556 site_int.CORRECT_MOVE_INDICATOR,
1557 site_sg.action_flag,
1558 site_int.ROWID,
1559 hz_ps.location_id,
1560 hr_locations_s.NextVal,
1561 site_sg.party_site_id,
1562 hz_party_sites_s.NextVal,
1563 site_int.party_site_number,
1564 site_int.party_site_name,
1565 site_sg.party_id,
1566 site_int.site_orig_system,
1567 site_int.site_orig_system_reference,
1568 site_sg.old_site_orig_system_ref,
1569 site_int.country,
1570 site_int.country_std,
1571 site_int.address1, site_int.address2, site_int.address3, site_int.address4,
1572 site_int.address1_std, site_int.address2_std, site_int.address3_std, site_int.address4_std,
1573 site_int.city, site_int.city_std, site_int.postal_code, site_int.postal_code_std,
1574 site_int.state, site_int.prov_state_admin_code_std,
1575 site_int.PROVINCE, site_int.county, site_int.county_std,
1576 hz_loc.country,
1577 hz_loc.address1,hz_loc.address2,hz_loc.address3,hz_loc.address4,
1578 hz_loc.city, hz_loc.postal_code,
1579 hz_loc.state, hz_loc.province, hz_loc.county,
1580 site_int.ADDRESS_LINES_PHONETIC,
1581 site_int.POSTAL_PLUS4_CODE,
1582 hz_loc.time_zone,
1583 timezone.UPGRADE_TZ_ID,
1584 site_int.TIMEZONE_CODE,
1585 site_int.location_directions,
1586 site_int.clli_code,
1587 site_int.language,
1588 site_int.short_description,
1589 site_int.description,
1590 site_int.delivery_point_code,
1591 site_int.SALES_TAX_GEOCODE,
1592 site_int.SALES_TAX_INSIDE_CITY_LIMITS,
1593 site_int.CREATED_BY_MODULE,
1594 site_int.LAST_UPDATED_BY,
1595 site_int.ACCEPT_STANDARDIZED_FLAG,
1596 site_int.ADAPTER_CONTENT_SOURCE,
1597 site_int.attribute_category, site_int.attribute1, site_int.attribute2,
1598 site_int.attribute3, site_int.attribute4, site_int.attribute5,
1599 site_int.attribute6, site_int.attribute7, site_int.attribute8,
1600 site_int.attribute9, site_int.attribute10, site_int.attribute11,
1601 site_int.attribute12, site_int.attribute13, site_int.attribute14,
1602 site_int.attribute15, site_int.attribute16, site_int.attribute17,
1603 site_int.attribute18, site_int.attribute19, site_int.attribute20,
1604 hz_loc.attribute_category, hz_loc.attribute1, hz_loc.attribute2,
1605 hz_loc.attribute3, hz_loc.attribute4, hz_loc.attribute5,
1606 hz_loc.attribute6, hz_loc.attribute7, hz_loc.attribute8,
1607 hz_loc.attribute9, hz_loc.attribute10, hz_loc.attribute11,
1608 hz_loc.attribute12, hz_loc.attribute13, hz_loc.attribute14,
1609 hz_loc.attribute15, hz_loc.attribute16, hz_loc.attribute17,
1610 hz_loc.attribute18, hz_loc.attribute19, hz_loc.attribute20,
1611 site_sg.NEW_OSR_EXISTS_FLAG,
1612 decode(site_int.state,
1613 null, decode(hz_loc.state,
1614 null, decode(site_int.province, null, hz_loc.province, :GMISS_CHAR, null, site_int.province),hz_loc.state),
1615 :GMISS_CHAR, decode(site_int.province, null, hz_loc.province, :GMISS_CHAR, null, site_int.province),
1616 site_int.state) ps_admin_code,
1617
1618 nvl2(nullif(site_int.address1, :GMISS_CHAR),
1619 nvl2(site_int.accept_standardized_flag, nvl2(nullif(site_int.address1_std, :GMISS_CHAR), ''Y'' ,null), ''Y'')
1620 , null) address_error,
1621
1622 nvl2(nullif(site_int.country, :GMISS_CHAR),
1623 nvl2(fnd_terr.territory_code,
1624 nvl2(site_int.accept_standardized_flag,
1625 nvl2(nullif(site_int.country_std, :GMISS_CHAR),
1626 nvl2(fnd_terr2.territory_code, ''Y'', null),
1627 null), ''Y''),
1628 null), null) country_error,
1629
1630 decode(site_int.language, null, ''Y'', :GMISS_CHAR, ''Y'', fnd_lang.language_code) lang_error,
1631 decode(site_int.timezone_code, null, ''Y'', :GMISS_CHAR, ''Y'', timezone.timezone_code) timezone_error,
1632 decode(nvl(site_int.insert_update_flag, site_sg.action_flag), site_sg.action_flag, ''Y'', null) action_mismatch_error,
1633 site_sg.error_flag,
1634 decode(site_int.ACCEPT_STANDARDIZED_FLAG, ''Y'',
1635 decode(hz_loc.country, site_int.country_std,
1636 decode(nvl(hz_loc.state,hz_loc.province), site_int.prov_state_admin_code_std,
1637 decode(hz_loc.county, site_int.county_std,
1638 decode(hz_loc.city, site_int.city_std,
1639 decode(hz_loc.postal_code, site_int.postal_code_std,
1640 decode(hz_loc.address1, site_int.address1_std,
1641 decode(hz_loc.address2, site_int.address2_std,
1642 decode(hz_loc.address3, site_int.address3_std,
1643 decode(hz_loc.address4, site_int.address4_std,
1644 null, ''Y''), ''Y''), ''Y''), ''Y''), ''Y''), ''Y''), ''Y''), ''Y''), ''Y''),
1645 decode(hz_loc.country,
1646 decode(site_int.country, :GMISS_CHAR, null, null, hz_loc.country, site_int.country),
1647 decode(hz_loc.state,
1648 decode(site_int.state, :GMISS_CHAR, null, null, hz_loc.state, site_int.state),
1649 decode(hz_loc.province,
1650 decode(site_int.province, :GMISS_CHAR, null, null, hz_loc.province, site_int.province),
1651 decode(hz_loc.county,
1652 decode(site_int.county, :GMISS_CHAR, null, null, hz_loc.county, site_int.county),
1653 decode(hz_loc.city,
1654 decode(site_int.city, :GMISS_CHAR, null, null, hz_loc.city, site_int.city),
1655 decode(hz_loc.postal_code,
1656 decode(site_int.postal_code, :GMISS_CHAR, null, null, hz_loc.postal_code, site_int.postal_code),
1657 decode(hz_loc.address1,
1658 decode(site_int.address1, :GMISS_CHAR, null, null, hz_loc.address1, site_int.address1),
1659 decode(hz_loc.address2,
1660 decode(site_int.address2, :GMISS_CHAR, null, null, hz_loc.address2, site_int.address2),
1661 decode(hz_loc.address3,
1662 decode(site_int.address3, :GMISS_CHAR, null, null, hz_loc.address3, site_int.address3),
1663 decode(hz_loc.address4,
1664 decode(site_int.address4, :GMISS_CHAR, null, null, hz_loc.address4, site_int.address4),
1665 null, ''Y''), ''Y''), ''Y''), ''Y''), ''Y''), ''Y''), ''Y''), ''Y''), ''Y''), ''Y'')) addr_ch_flag,
1666 decode(site_int.ACCEPT_STANDARDIZED_FLAG, ''Y'',
1667 decode(hz_loc.country, site_int.country_std,
1668 decode(nvl(hz_loc.state,hz_loc.province), site_int.prov_state_admin_code_std,
1669 decode(hz_loc.county, site_int.county_std,
1670 decode(hz_loc.city, site_int.city_std,
1671 decode(hz_loc.postal_code, site_int.postal_code_std,
1672 null, ''Y''), ''Y''), ''Y''), ''Y''), ''Y''),
1673 decode(hz_loc.country,
1674 decode(site_int.country, :GMISS_CHAR, null, null, hz_loc.country, site_int.country),
1675 decode(hz_loc.state,
1676 decode(site_int.state, :GMISS_CHAR, null, null, hz_loc.state, site_int.state),
1677 decode(hz_loc.province,
1678 decode(site_int.province, :GMISS_CHAR, null, null, hz_loc.province, site_int.province),
1679 decode(hz_loc.county,
1680 decode(site_int.county, :GMISS_CHAR, null, null, hz_loc.county, site_int.county),
1681 decode(hz_loc.city,
1682 decode(site_int.city, :GMISS_CHAR, null, null, hz_loc.city, site_int.city),
1683 decode(hz_loc.postal_code,
1684 decode(site_int.postal_code, :GMISS_CHAR, null, null, hz_loc.postal_code, site_int.postal_code),
1685 null, ''Y''), ''Y''), ''Y''), ''Y''), ''Y''), ''Y'')) tax_ch_flag,
1686 hz_ps.identifying_address_flag primary_flag,
1687 /* bug 4079902 */
1688 nvl2(nullif(hz_ps.actual_content_source,:l_os),
1689 nvl2(nullif(hos.orig_system_type,''PURCHASED''),''Y'',null),
1690 ''Y'') third_party_update_error,
1691 nvl2(nullif(site_int.created_by_module,:GMISS_CHAR),
1692 decode(site_int.CORRECT_MOVE_INDICATOR,
1693 ''M'',nvl2(createdby_l.lookup_code,''Y'',null),
1694 nvl2(site_sg.new_osr_exists_flag,
1695 nvl2(nullif(site_int.site_orig_system_reference,site_sg.old_site_orig_system_ref),
1696 nvl2(createdby_l.lookup_code,''Y'',null),
1697 ''Y''
1698 ),
1699 ''Y''
1700 )
1701 ),
1702 ''Y'') createdby_error
1703
1704 FROM HZ_IMP_ADDRESSES_INT site_int,
1705 HZ_IMP_ADDRESSES_SG site_sg,
1706 FND_TERRITORIES fnd_terr,
1707 FND_TERRITORIES fnd_terr2,
1708 ( select language_code
1709 from FND_LANGUAGES
1710 where installed_flag in (''B'', ''I'')
1711 ) fnd_lang,
1712 fnd_timezones_b timezone,
1713 hz_party_sites hz_ps,
1714 hz_locations hz_loc,
1715 HZ_ORIG_SYS_REFERENCES mosr,
1716 HZ_ORIG_SYSTEMS_B hos,
1717 hz_party_sites hz_ps1,
1718 HZ_ORIG_SYS_REFERENCES mosr2,
1719 fnd_lookup_values createdby_l
1720 WHERE mosr.orig_system (+) = site_sg.party_orig_system
1721 AND mosr.orig_system_reference (+) = site_sg.party_orig_system_reference
1722 AND mosr.status (+) = ''A''
1723 AND mosr.owner_table_name (+) = ''HZ_PARTIES''
1724 AND mosr.owner_table_id (+) = site_sg.party_id
1725 AND site_sg.action_flag = ''U''
1726 AND site_int.batch_id = :CP_BATCH_ID
1727 AND site_sg.batch_id = :CP_BATCH_ID
1728 AND site_sg.batch_mode_flag = :CP_BATCH_MODE_FLAG
1729 AND site_sg.party_orig_system = :CP_OS
1730 AND site_sg.party_orig_system_reference between :CP_FROM_OSR AND :CP_TO_OSR
1731 AND site_int.rowid = site_sg.int_row_id
1732 AND fnd_terr.territory_code (+) = nullif(site_int.country, :GMSSS_CHAR)
1733 AND fnd_terr2.territory_code (+) = nullif(site_int.country_std, :GMSSS_CHAR)
1734 AND fnd_lang.language_code (+) = site_int.language
1735 AND timezone.timezone_code (+) = site_int.timezone_code
1736 AND hz_ps.party_site_id = site_sg.party_site_id
1737 --AND hz_loc_prf.location_id (+) = hz_loc.location_id
1738 AND hz_loc.location_id = hz_ps.location_id
1739 AND hz_ps.actual_content_source=hos.orig_system
1740 AND hz_ps1.party_id(+) = site_sg.party_id
1741 AND hz_ps1.status(+) = ''A''
1742 AND hz_ps1.identifying_address_flag(+) =''Y''AND mosr2.orig_system (+) = site_sg.site_orig_system
1743 AND mosr2.orig_system_reference (+) = site_sg.site_orig_system_reference
1744 AND mosr2.status (+) = ''A''
1745 AND mosr2.owner_table_name (+) = ''HZ_PARTY_SITES''
1746 AND mosr2.owner_table_id (+) = site_sg.party_site_id
1747 AND createdby_l.lookup_code (+) = site_int.created_by_module
1748 AND createdby_l.lookup_type (+) = ''HZ_CREATED_BY_MODULES''
1749 AND createdby_l.language (+) = userenv(''LANG'')
1750 AND createdby_l.view_application_id (+) = 222
1751 AND createdby_l.security_group_id (+) =
1752 fnd_global.lookup_security_group(''HZ_CREATED_BY_MODULES'', 222)
1753 ';
1754
1755 l_where_enabled_lookup_sql varchar2(4000) :=
1756 'AND ( createdby_l.ENABLED_FLAG(+) = ''Y'' AND
1757 TRUNC(:5) BETWEEN
1758 TRUNC(NVL( createdby_l.START_DATE_ACTIVE,:5 ) ) AND
1759 TRUNC(NVL( createdby_l.END_DATE_ACTIVE,:5 ) ) )';
1760
1761 l_first_run_clause varchar2(40) := ' AND site_int.interface_status is null';
1762 l_re_run_clause varchar2(40) := ' AND site_int.interface_status = ''C''';
1763
1764 --l_where_enabled_lookup_sql varchar2(3000) := ' AND ( timezone.ENABLED_FLAG(+) = ''Y'' )';
1765
1766 l_final_qry varchar2(20000);
1767 l_debug_prefix VARCHAR2(30) := '';
1768 BEGIN
1769
1770 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
1771 hz_utility_v2pub.debug(p_message=>'ADDR:open_update_cursor()+',
1772 p_prefix=>l_debug_prefix,
1773 p_msg_level=>fnd_log.level_procedure);
1774 END IF;
1775
1776 if(l_allow_std_update <> 'Y') then
1777 l_final_qry := l_sql_query_w_prf;
1778 else
1779 l_final_qry := l_sql_query;
1780 end if;
1781
1782 if(P_DML_RECORD.RERUN='N') then
1783 l_final_qry := l_final_qry || l_first_run_clause;
1784 else
1785 l_final_qry := l_final_qry || l_re_run_clause;
1786 end if;
1787
1788
1789 IF P_DML_RECORD.ALLOW_DISABLED_LOOKUP = 'N' THEN
1790 l_final_qry := l_final_qry || l_where_enabled_lookup_sql;
1791
1792 if(l_allow_std_update = 'Y') then
1793 OPEN update_cursor FOR l_final_qry
1794 USING P_DML_RECORD.GMISS_CHAR, P_DML_RECORD.GMISS_CHAR, P_DML_RECORD.GMISS_CHAR, P_DML_RECORD.GMISS_CHAR,
1795 P_DML_RECORD.GMISS_CHAR, P_DML_RECORD.GMISS_CHAR, P_DML_RECORD.GMISS_CHAR, P_DML_RECORD.GMISS_CHAR, P_DML_RECORD.GMISS_CHAR, P_DML_RECORD.GMISS_CHAR, P_DML_RECORD.GMISS_CHAR,
1796 P_DML_RECORD.GMISS_CHAR, P_DML_RECORD.GMISS_CHAR, P_DML_RECORD.GMISS_CHAR, P_DML_RECORD.GMISS_CHAR, P_DML_RECORD.GMISS_CHAR, P_DML_RECORD.GMISS_CHAR, P_DML_RECORD.GMISS_CHAR,
1797 P_DML_RECORD.GMISS_CHAR, P_DML_RECORD.GMISS_CHAR, P_DML_RECORD.GMISS_CHAR, P_DML_RECORD.GMISS_CHAR, P_DML_RECORD.GMISS_CHAR, P_DML_RECORD.GMISS_CHAR, P_DML_RECORD.GMISS_CHAR,
1798 P_DML_RECORD.ACTUAL_CONTENT_SRC,P_DML_RECORD.GMISS_CHAR,
1799 P_DML_RECORD.BATCH_ID,
1800 P_DML_RECORD.BATCH_ID, P_DML_RECORD.BATCH_MODE_FLAG,
1801 P_DML_RECORD.OS, P_DML_RECORD.FROM_OSR, P_DML_RECORD.TO_OSR,
1802 P_DML_RECORD.GMISS_CHAR, P_DML_RECORD.GMISS_CHAR,
1803 P_DML_RECORD.SYSDATE, P_DML_RECORD.SYSDATE, P_DML_RECORD.SYSDATE;
1804
1805 else
1806 OPEN update_cursor FOR l_final_qry
1807 USING c_end_date, P_DML_RECORD.GMISS_CHAR, P_DML_RECORD.GMISS_CHAR, P_DML_RECORD.GMISS_CHAR, P_DML_RECORD.GMISS_CHAR,
1808 P_DML_RECORD.GMISS_CHAR, P_DML_RECORD.GMISS_CHAR, P_DML_RECORD.GMISS_CHAR, P_DML_RECORD.GMISS_CHAR, P_DML_RECORD.GMISS_CHAR, P_DML_RECORD.GMISS_CHAR, P_DML_RECORD.GMISS_CHAR,
1809 P_DML_RECORD.GMISS_CHAR, P_DML_RECORD.GMISS_CHAR, P_DML_RECORD.GMISS_CHAR, P_DML_RECORD.GMISS_CHAR, P_DML_RECORD.GMISS_CHAR, P_DML_RECORD.GMISS_CHAR, P_DML_RECORD.GMISS_CHAR,
1810 P_DML_RECORD.GMISS_CHAR, P_DML_RECORD.GMISS_CHAR, P_DML_RECORD.GMISS_CHAR, P_DML_RECORD.GMISS_CHAR, P_DML_RECORD.GMISS_CHAR, P_DML_RECORD.GMISS_CHAR, P_DML_RECORD.GMISS_CHAR,
1811 P_DML_RECORD.ACTUAL_CONTENT_SRC,
1812 P_DML_RECORD.GMISS_CHAR,
1813 P_DML_RECORD.BATCH_ID, P_DML_RECORD.BATCH_ID, P_DML_RECORD.BATCH_MODE_FLAG,
1814 P_DML_RECORD.OS, P_DML_RECORD.FROM_OSR, P_DML_RECORD.TO_OSR,
1815 P_DML_RECORD.GMISS_CHAR, P_DML_RECORD.GMISS_CHAR,
1816 P_DML_RECORD.SYSDATE, P_DML_RECORD.SYSDATE, P_DML_RECORD.SYSDATE;
1817 end if;
1818 ELSE
1819 if(l_allow_std_update = 'Y') then
1820 OPEN update_cursor FOR l_final_qry
1821 USING P_DML_RECORD.GMISS_CHAR, P_DML_RECORD.GMISS_CHAR, P_DML_RECORD.GMISS_CHAR, P_DML_RECORD.GMISS_CHAR,
1822 P_DML_RECORD.GMISS_CHAR, P_DML_RECORD.GMISS_CHAR, P_DML_RECORD.GMISS_CHAR, P_DML_RECORD.GMISS_CHAR, P_DML_RECORD.GMISS_CHAR, P_DML_RECORD.GMISS_CHAR, P_DML_RECORD.GMISS_CHAR,
1823 P_DML_RECORD.GMISS_CHAR, P_DML_RECORD.GMISS_CHAR, P_DML_RECORD.GMISS_CHAR, P_DML_RECORD.GMISS_CHAR, P_DML_RECORD.GMISS_CHAR, P_DML_RECORD.GMISS_CHAR, P_DML_RECORD.GMISS_CHAR,
1824 P_DML_RECORD.GMISS_CHAR, P_DML_RECORD.GMISS_CHAR, P_DML_RECORD.GMISS_CHAR, P_DML_RECORD.GMISS_CHAR, P_DML_RECORD.GMISS_CHAR, P_DML_RECORD.GMISS_CHAR, P_DML_RECORD.GMISS_CHAR,
1825 P_DML_RECORD.ACTUAL_CONTENT_SRC,P_DML_RECORD.GMISS_CHAR,
1826 P_DML_RECORD.BATCH_ID,
1827 P_DML_RECORD.BATCH_ID, P_DML_RECORD.BATCH_MODE_FLAG,
1828 P_DML_RECORD.OS, P_DML_RECORD.FROM_OSR, P_DML_RECORD.TO_OSR,
1829 P_DML_RECORD.GMISS_CHAR, P_DML_RECORD.GMISS_CHAR;
1830
1831 else
1832 OPEN update_cursor FOR l_final_qry
1833 USING c_end_date, P_DML_RECORD.GMISS_CHAR, P_DML_RECORD.GMISS_CHAR, P_DML_RECORD.GMISS_CHAR, P_DML_RECORD.GMISS_CHAR,
1834 P_DML_RECORD.GMISS_CHAR, P_DML_RECORD.GMISS_CHAR, P_DML_RECORD.GMISS_CHAR, P_DML_RECORD.GMISS_CHAR, P_DML_RECORD.GMISS_CHAR, P_DML_RECORD.GMISS_CHAR, P_DML_RECORD.GMISS_CHAR,
1835 P_DML_RECORD.GMISS_CHAR, P_DML_RECORD.GMISS_CHAR, P_DML_RECORD.GMISS_CHAR, P_DML_RECORD.GMISS_CHAR, P_DML_RECORD.GMISS_CHAR, P_DML_RECORD.GMISS_CHAR, P_DML_RECORD.GMISS_CHAR,
1836 P_DML_RECORD.GMISS_CHAR, P_DML_RECORD.GMISS_CHAR, P_DML_RECORD.GMISS_CHAR, P_DML_RECORD.GMISS_CHAR, P_DML_RECORD.GMISS_CHAR, P_DML_RECORD.GMISS_CHAR, P_DML_RECORD.GMISS_CHAR,
1837 P_DML_RECORD.ACTUAL_CONTENT_SRC,
1838 P_DML_RECORD.GMISS_CHAR,
1839 P_DML_RECORD.BATCH_ID, P_DML_RECORD.BATCH_ID, P_DML_RECORD.BATCH_MODE_FLAG,
1840 P_DML_RECORD.OS, P_DML_RECORD.FROM_OSR, P_DML_RECORD.TO_OSR,
1841 P_DML_RECORD.GMISS_CHAR, P_DML_RECORD.GMISS_CHAR;
1842 end if;
1843 END IF;
1844
1845 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
1846 hz_utility_v2pub.debug(p_message=>'ADDR:open_update_cursor()-',
1847 p_prefix=>l_debug_prefix,
1848 p_msg_level=>fnd_log.level_procedure);
1849 END IF;
1850
1851 END open_update_cursor;
1852
1853
1854 PROCEDURE validate_desc_flexfield(
1855 p_validation_date IN DATE
1856 ) IS
1857 l_flex_exists VARCHAR2(1);
1858 l_debug_prefix VARCHAR2(30) := '';
1859 BEGIN
1860
1861 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
1862 hz_utility_v2pub.debug(p_message=>'ADDR:validate_desc_flexfield()+',
1863 p_prefix=>l_debug_prefix,
1864 p_msg_level=>fnd_log.level_procedure);
1865 END IF;
1866
1867 FOR i IN 1..l_site_id.count LOOP
1868
1869 FND_FLEX_DESCVAL.set_context_value(l_attr_category(i));
1870
1871 FND_FLEX_DESCVAL.set_column_value('ATTRIBUTE1', l_attr1(i));
1872 FND_FLEX_DESCVAL.set_column_value('ATTRIBUTE2', l_attr2(i));
1873 FND_FLEX_DESCVAL.set_column_value('ATTRIBUTE3', l_attr3(i));
1874 FND_FLEX_DESCVAL.set_column_value('ATTRIBUTE4', l_attr4(i));
1875 FND_FLEX_DESCVAL.set_column_value('ATTRIBUTE5', l_attr5(i));
1876 FND_FLEX_DESCVAL.set_column_value('ATTRIBUTE6', l_attr6(i));
1877 FND_FLEX_DESCVAL.set_column_value('ATTRIBUTE7', l_attr7(i));
1878 FND_FLEX_DESCVAL.set_column_value('ATTRIBUTE8', l_attr8(i));
1879 FND_FLEX_DESCVAL.set_column_value('ATTRIBUTE9', l_attr9(i));
1880 FND_FLEX_DESCVAL.set_column_value('ATTRIBUTE10', l_attr10(i));
1881 FND_FLEX_DESCVAL.set_column_value('ATTRIBUTE11', l_attr11(i));
1882 FND_FLEX_DESCVAL.set_column_value('ATTRIBUTE12', l_attr12(i));
1883 FND_FLEX_DESCVAL.set_column_value('ATTRIBUTE13', l_attr13(i));
1884 FND_FLEX_DESCVAL.set_column_value('ATTRIBUTE14', l_attr14(i));
1885 FND_FLEX_DESCVAL.set_column_value('ATTRIBUTE15', l_attr15(i));
1886 FND_FLEX_DESCVAL.set_column_value('ATTRIBUTE16', l_attr16(i));
1887 FND_FLEX_DESCVAL.set_column_value('ATTRIBUTE17', l_attr17(i));
1888 FND_FLEX_DESCVAL.set_column_value('ATTRIBUTE18', l_attr18(i));
1889 FND_FLEX_DESCVAL.set_column_value('ATTRIBUTE19', l_attr19(i));
1890 FND_FLEX_DESCVAL.set_column_value('ATTRIBUTE20', l_attr20(i));
1891
1892 IF (NOT FND_FLEX_DESCVAL.validate_desccols(
1893 'AR',
1894 'HZ_PARTY_SITES',
1895 'V',
1896 p_validation_date)) THEN
1897 l_flex_val_errors(i) := 1;
1898 END IF;
1899
1900 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
1901 hz_utility_v2pub.debug(p_message=>'ADDR:validate_desc_flexfield()-',
1902 p_prefix=>l_debug_prefix,
1903 p_msg_level=>fnd_log.level_procedure);
1904 END IF;
1905
1906 END LOOP;
1907
1908 ----dbms_output.put_line('validate_desc_flexfield-');
1909 END validate_desc_flexfield;
1910
1911
1912 PROCEDURE validate_DSS_security IS
1913 dss_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
1914 dss_msg_count NUMBER := 0;
1915 dss_msg_data VARCHAR2(2000):= null;
1916 l_debug_prefix VARCHAR2(30) := '';
1917 BEGIN
1918
1919 FND_FILE.PUT_LINE(FND_FILE.LOG, 'validate_DSS_security for address.');
1920
1921 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
1922 hz_utility_v2pub.debug(p_message=>'PTY:validate_DSS_security()+',
1923 p_prefix=>l_debug_prefix,
1924 p_msg_level=>fnd_log.level_procedure);
1925 END IF;
1926
1927 /* Check if the DSS security is granted to the user.
1928 Only check for update. */
1929 FOR i IN 1..l_site_id.count LOOP
1930 l_dss_security_errors(i) :=
1931 hz_dss_util_pub.test_instance(
1932 p_operation_code => 'UPDATE',
1933 p_db_object_name => 'HZ_PARTY_SITES',
1934 p_instance_pk1_value => l_site_id(i),
1935 p_user_name => fnd_global.user_name,
1936 x_return_status => dss_return_status,
1937 x_msg_count => dss_msg_count,
1938 x_msg_data => dss_msg_data);
1939 END LOOP;
1940
1941 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
1942 hz_utility_v2pub.debug(p_message=>'PTY:validate_DSS_security()-',
1943 p_prefix=>l_debug_prefix,
1944 p_msg_level=>fnd_log.level_procedure);
1945 END IF;
1946
1947 END validate_DSS_security;
1948
1949
1950 PROCEDURE process_update_addresses (
1951 P_DML_RECORD IN HZ_IMP_LOAD_WRAPPER.DML_RECORD_TYPE,
1952 x_return_status OUT NOCOPY VARCHAR2,
1953 x_msg_count OUT NOCOPY NUMBER,
1954 x_msg_data OUT NOCOPY VARCHAR2
1955 ) IS
1956 c_update_cursor update_cursor_type;
1957 l_dml_exception varchar2(1) := 'N';
1958 l_debug_prefix VARCHAR2(30) := '';
1959 BEGIN
1960
1961 ----dbms_output.put_line('process_update_addresses+');
1962 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
1963 hz_utility_v2pub.debug(p_message=>'process_update_addresses()+',
1964 p_prefix=>l_debug_prefix,
1965 p_msg_level=>fnd_log.level_procedure);
1966 END IF;
1967
1968 savepoint process_update_addresses_pvt;
1969 FND_MSG_PUB.initialize;
1970 --Initialize API return status to success.
1971 x_return_status := FND_API.G_RET_STS_SUCCESS;
1972 open_update_cursor(c_update_cursor, P_DML_RECORD);
1973
1974 if(l_allow_std_update= 'Y') then
1975 fetch c_update_cursor BULK COLLECT INTO
1976 l_old_addr_phonetic,
1977 l_old_postal_plus4,
1978 l_old_loc_dir,
1979 l_old_clli_code,
1980 l_old_language,
1981 l_old_short_desc,
1982 l_old_desc,
1983 l_old_delvy_pt_code,
1984 l_old_sales_tax_code,
1985 l_old_sales_tax_limit,
1986 l_flex_val_errors,
1987 l_dss_security_errors,
1988 l_val_status_code,
1989 l_owner_table_id,
1990 l_ident_addr_flag,
1991 l_owning_party_id,
1992 l_valid_status_code,
1993 l_old_valid_status_code,
1994 l_date_validated,
1995 l_corr_mv_ind,
1996 l_action_flag,
1997 l_row_id,
1998 l_location_id,
1999 l_new_loc_id,
2000 l_site_id,
2001 l_new_site_id,
2002 l_party_site_number,
2003 l_site_name,
2004 l_party_id,
2005 l_site_orig_system,
2006 l_site_orig_system_reference,
2007 l_old_site_osr,
2008 l_country, l_country_std,
2009 l_addr1, l_addr2, l_addr3, l_addr4,
2010 l_addr1_std, l_addr2_std, l_addr3_std, l_addr4_std,
2011 l_city, l_city_std, l_postal_code, l_postal_code_std, l_state,
2012 l_ps_admin_std,
2013 l_province, l_county, l_county_std,
2014 l_old_country,
2015 l_old_addr1, l_old_addr2, l_old_addr3, l_old_addr4,
2016 l_old_city, l_old_postal_code,
2017 l_old_state, l_old_province, l_old_county,
2018 l_addr_phonetic, l_postal_plus4, l_old_timezone, l_timezone, l_timezone_code,
2019 l_loc_dir, l_clli_code, l_language,
2020 l_short_desc, l_desc,
2021 l_delvy_pt_code,
2022 l_sales_tax_code,
2023 l_sales_tax_limit,
2024 --l_fa_loc_id,
2025 l_created_by_module,
2026 l_last_updated_by,
2027 l_accept_std_flag,
2028 l_adptr_content_src,
2029 l_attr_category, l_attr1, l_attr2, l_attr3, l_attr4, l_attr5, l_attr6,
2030 l_attr7, l_attr8, l_attr9, l_attr10, l_attr11, l_attr12, l_attr13,
2031 l_attr14, l_attr15, l_attr16, l_attr17, l_attr18, l_attr19, l_attr20,
2032 l_old_attr_category, l_old_attr1, l_old_attr2, l_old_attr3, l_old_attr4, l_old_attr5, l_old_attr6,
2033 l_old_attr7, l_old_attr8, l_old_attr9, l_old_attr10, l_old_attr11, l_old_attr12, l_old_attr13,
2034 l_old_attr14, l_old_attr15, l_old_attr16, l_old_attr17, l_old_attr18, l_old_attr19, l_old_attr20,
2035 l_NEW_OSR_EXISTS,
2036 l_ps_admin_int,
2037 l_address_err,
2038 l_country_err,
2039 l_lang_err,
2040 l_timezone_err,
2041 l_action_error_flag,
2042 l_error_flag,
2043 l_addr_ch_flag,
2044 l_tax_ch_flag,
2045 l_primary_flag,
2046 --l_moved_site_id,
2047 --l_moved_site_number
2048 l_third_party_update_error, /* Bug 4079902 */
2049 l_createdby_errors;
2050
2051
2052 else
2053 fetch c_update_cursor BULK COLLECT INTO
2054 l_old_profile_sst_flag,
2055 l_old_addr_phonetic,
2056 l_old_postal_plus4,
2057 l_old_loc_dir,
2058 l_old_clli_code,
2059 l_old_language,
2060 l_old_short_desc,
2061 l_old_desc,
2062 l_old_delvy_pt_code,
2063 l_old_sales_tax_code,
2064 l_old_sales_tax_limit,
2065 l_flex_val_errors,
2066 l_dss_security_errors,
2067 l_val_status_code,
2068 l_owner_table_id,
2069 l_ident_addr_flag,
2070 l_owning_party_id,
2071 l_valid_status_code,
2072 l_old_valid_status_code,
2073 l_date_validated,
2074 l_corr_mv_ind,
2075 l_action_flag,
2076 l_row_id,
2077 l_location_id,
2078 l_new_loc_id,
2079 l_site_id,
2080 l_new_site_id,
2081 l_party_site_number,
2082 l_site_name,
2083 l_party_id,
2084 l_site_orig_system,
2085 l_site_orig_system_reference,
2086 l_old_site_osr,
2087 l_country, l_country_std,
2088 l_addr1, l_addr2, l_addr3, l_addr4,
2089 l_addr1_std, l_addr2_std, l_addr3_std, l_addr4_std,
2090 l_city, l_city_std, l_postal_code, l_postal_code_std, l_state,
2091 l_ps_admin_std,
2092 l_province, l_county, l_county_std,
2093 l_old_country,
2094 l_old_addr1, l_old_addr2, l_old_addr3, l_old_addr4,
2095 l_old_city, l_old_postal_code,
2096 l_old_state, l_old_province, l_old_county,
2097 l_addr_phonetic, l_postal_plus4, l_old_timezone, l_timezone, l_timezone_code,
2098 l_loc_dir, l_clli_code, l_language,
2099 l_short_desc, l_desc,
2100 l_delvy_pt_code,
2101 l_sales_tax_code,
2102 l_sales_tax_limit,
2103 --l_fa_loc_id,
2104 l_created_by_module,
2105 l_last_updated_by,
2106 l_accept_std_flag,
2107 l_adptr_content_src,
2108 l_attr_category, l_attr1, l_attr2, l_attr3, l_attr4, l_attr5, l_attr6,
2109 l_attr7, l_attr8, l_attr9, l_attr10, l_attr11, l_attr12, l_attr13,
2110 l_attr14, l_attr15, l_attr16, l_attr17, l_attr18, l_attr19, l_attr20,
2111 l_old_attr_category, l_old_attr1, l_old_attr2, l_old_attr3, l_old_attr4, l_old_attr5, l_old_attr6,
2112 l_old_attr7, l_old_attr8, l_old_attr9, l_old_attr10, l_old_attr11, l_old_attr12, l_old_attr13,
2113 l_old_attr14, l_old_attr15, l_old_attr16, l_old_attr17, l_old_attr18, l_old_attr19, l_old_attr20,
2114 l_NEW_OSR_EXISTS,
2115 l_ps_admin_int,
2116 l_address_err,
2117 l_country_err,
2118 l_lang_err,
2119 l_timezone_err,
2120 l_action_error_flag,
2121 l_error_flag,
2122 l_addr_ch_flag,
2123 l_tax_ch_flag,
2124 l_primary_flag,
2125 --l_moved_site_id,
2126 --l_moved_site_number
2127 l_third_party_update_error, /* Bug 4079902 */
2128 l_createdby_errors;
2129
2130
2131 end if;
2132
2133 close c_update_cursor;
2134
2135 /* Do FND desc flex validation based on profile */
2136 IF P_DML_RECORD.FLEX_VALIDATION = 'Y' THEN
2137 validate_desc_flexfield(P_DML_RECORD.SYSDATE);
2138 END IF;
2139
2140 /* Do DSS security validation based on profile */
2141 IF P_DML_RECORD.DSS_SECURITY = 'Y' THEN
2142 validate_DSS_security;
2143 END IF;
2144
2145 ----dbms_output.put_line('number of input records: ' || l_site_id.count);
2146 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2147 hz_utility_v2pub.debug(p_message=>'number of input records: ' || l_site_id.count,
2148 p_prefix =>l_debug_prefix,
2149 p_msg_level=>fnd_log.level_statement);
2150 END IF;
2151
2152 l_move_count.extend(l_site_id.count);
2153 l_init_upd_count.extend(l_site_id.count);
2154 l_corr_count.extend(l_site_id.count);
2155 l_corr_upd_count.extend(l_site_id.count);
2156 l_temp_upd_count.extend(l_site_id.count);
2157
2158 /* handle correction and no-address-column-change records */
2159 /* update location profile for user data */
2160 /* if profile Maintain Location History = 'Y', end-date old one */
2161 /* otherwise, update the entry directly */
2162 forall j in 1..l_site_id.count save exceptions
2163 update hz_location_profiles
2164 set effective_end_date = decode(l_maintain_loc_hist, 'Y', P_DML_RECORD.SYSDATE, null),
2165 address1 = decode(l_maintain_loc_hist, 'Y', address1, decode(l_addr1(j), P_DML_RECORD.GMISS_CHAR, null, null, address1, l_addr1(j))),
2166 address2 = decode(l_maintain_loc_hist, 'Y', address2, decode(l_addr2(j), P_DML_RECORD.GMISS_CHAR, null, null, address2, l_addr2(j))),
2167 address3 = decode(l_maintain_loc_hist, 'Y', address3, decode(l_addr3(j), P_DML_RECORD.GMISS_CHAR, null, null, address3, l_addr3(j))),
2168 address4 = decode(l_maintain_loc_hist, 'Y', address4, decode(l_addr4(j), P_DML_RECORD.GMISS_CHAR, null, null, address4, l_addr4(j))),
2169 city = decode(l_maintain_loc_hist, 'Y', city, decode(l_city(j), P_DML_RECORD.GMISS_CHAR, null, null, city, l_city(j))),
2170 prov_state_admin_code = decode(l_maintain_loc_hist, 'Y', prov_state_admin_code, l_ps_admin_int(j)),
2171 COUNTY = decode(l_maintain_loc_hist, 'Y', COUNTY, decode(l_county(j), P_DML_RECORD.GMISS_CHAR, null, null, city, l_county(j))),
2172 COUNTRY = decode(l_maintain_loc_hist, 'Y', country, nvl(l_country(j), COUNTRY)),
2173 postal_code = decode(l_maintain_loc_hist, 'Y', postal_code, decode(l_postal_code(j), P_DML_RECORD.GMISS_CHAR, null, null, postal_code, l_postal_code(j))),
2174 --validation_sst_flag = decode(l_maintain_loc_hist, 'Y', validation_sst_flag, decode(l_accept_std_flag(j), 'Y', 'N', 'Y')),
2175 /* no need to update validation status code for user data */
2176 object_version_number = object_version_number+1,
2177 last_update_date = P_DML_RECORD.SYSDATE,
2178 last_updated_by = P_DML_RECORD.USER_ID,
2179 last_update_login = P_DML_RECORD.LAST_UPDATE_LOGIN
2180 where location_id=l_location_id(j) /* */
2181 and actual_content_source = P_DML_RECORD.ACTUAL_CONTENT_SRC /* keys for location profile */
2182 and nullif(effective_end_date, c_end_date) is null /* */
2183 and l_action_error_flag(j) is not null -- error checks
2184 and l_error_flag(j) is null
2185 and l_address_err(j) is not null
2186 and l_country_err(j) is not null
2187 and l_lang_err(j) is not null
2188 and l_timezone_err(j) is not null
2189 and l_flex_val_errors(j) = 0
2190 and l_dss_security_errors(j) = 'T'
2191 and (( nvl(l_corr_mv_ind(j), 'M') = 'C' -- correction
2192 and l_allow_correction = 'Y') -- and allowed
2193 or nvl(l_addr_ch_flag(j), 'N') <> 'Y') -- or no change in addr cols
2194 and l_third_party_update_error(j) IS NOT NULL /* bug 4079902 */
2195 and l_createdby_errors(j) IS NOT NULL;
2196
2197
2198 /* record row count for corrected/updated records */
2199 FOR k IN 1..l_site_id.count LOOP
2200 if l_addr_ch_flag(k)='Y' then
2201 l_corr_count(k) := SQL%BULK_ROWCOUNT(k);
2202 else
2203 l_corr_count(k) := 0;
2204 end if;
2205 l_init_upd_count(k) := SQL%BULK_ROWCOUNT(k);
2206 end loop;
2207
2208 /* update row count for corrected/updated records */
2209 FOR k IN 1..l_site_id.count LOOP
2210 if l_addr_ch_flag(k)='Y' then
2211 l_corr_count(k) := l_init_upd_count(k);
2212 end if;
2213 l_corr_upd_count(k) := l_init_upd_count(k);
2214 end loop;
2215
2216 /* handle move records */
2217 /* create new party site and invalidate old one if it is necessary to move */
2218 /* condition: if address-related column change and correct_move_indicator='N'/null */
2219 begin
2220 forall j in 1..l_site_id.count save exceptions
2221 insert into hz_party_sites
2222 ( PARTY_SITE_ID,
2223 PARTY_ID,
2224 LOCATION_ID,
2225 PARTY_SITE_NUMBER,
2226 LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN,
2227 REQUEST_ID, PROGRAM_APPLICATION_ID, PROGRAM_ID, PROGRAM_UPDATE_DATE,
2228 ORIG_SYSTEM_REFERENCE,
2229 STATUS,
2230 PARTY_SITE_NAME,
2231 OBJECT_VERSION_NUMBER,
2232 CREATED_BY_MODULE,
2233 APPLICATION_ID,
2234 ACTUAL_CONTENT_SOURCE,
2235 IDENTIFYING_ADDRESS_FLAG,
2236 attribute_category,
2237 attribute1,
2238 attribute2,
2239 attribute3,
2240 attribute4,
2241 attribute5,
2242 attribute6,
2243 attribute7,
2244 attribute8,
2245 attribute9,
2246 attribute10,
2247 attribute11,
2248 attribute12,
2249 attribute13,
2250 attribute14,
2251 attribute15,
2252 attribute16,
2253 attribute17,
2254 attribute18,
2255 attribute19,
2256 attribute20
2257 )
2258 ( select
2259 l_new_site_id(j),
2260 l_party_id(j),
2261 l_new_loc_id(j),
2262 hz_party_site_number_s.NextVal,
2263 P_DML_RECORD.SYSDATE, P_DML_RECORD.USER_ID, P_DML_RECORD.SYSDATE, P_DML_RECORD.USER_ID, P_DML_RECORD.USER_ID,
2264 P_DML_RECORD.REQUEST_ID,
2265 P_DML_RECORD.PROGRAM_APPLICATION_ID, P_DML_RECORD.PROGRAM_ID, P_DML_RECORD.SYSDATE,
2266 l_site_orig_system_reference(j),
2267 'A',
2268 nullif(l_site_name(j),P_DML_RECORD.GMISS_CHAR),
2269 1,
2270 nvl(l_created_by_module(j), 'HZ_IMPORT'),
2271 P_DML_RECORD.APPLICATION_ID,
2272 --'ACS for inserted party site',
2273 P_DML_RECORD.ACTUAL_CONTENT_SRC,
2274 nvl(l_ident_addr_flag(j), 'N'),
2275 nullif(nvl(l_attr_category(j), l_old_attr_category(j)), P_DML_RECORD.GMISS_CHAR),
2276 nullif(nvl(l_attr1(j), l_attr1(j)), P_DML_RECORD.GMISS_CHAR),
2277 nullif(nvl(l_attr2(j), l_attr2(j)), P_DML_RECORD.GMISS_CHAR),
2278 nullif(nvl(l_attr3(j), l_attr3(j)), P_DML_RECORD.GMISS_CHAR),
2279 nullif(nvl(l_attr4(j), l_attr4(j)), P_DML_RECORD.GMISS_CHAR),
2280 nullif(nvl(l_attr5(j), l_attr5(j)), P_DML_RECORD.GMISS_CHAR),
2281 nullif(nvl(l_attr6(j), l_attr6(j)), P_DML_RECORD.GMISS_CHAR),
2282 nullif(nvl(l_attr7(j), l_attr7(j)), P_DML_RECORD.GMISS_CHAR),
2283 nullif(nvl(l_attr8(j), l_attr8(j)), P_DML_RECORD.GMISS_CHAR),
2284 nullif(nvl(l_attr9(j), l_attr9(j)), P_DML_RECORD.GMISS_CHAR),
2285 nullif(nvl(l_attr10(j), l_attr10(j)), P_DML_RECORD.GMISS_CHAR),
2286 nullif(nvl(l_attr11(j), l_attr11(j)), P_DML_RECORD.GMISS_CHAR),
2287 nullif(nvl(l_attr12(j), l_attr12(j)), P_DML_RECORD.GMISS_CHAR),
2288 nullif(nvl(l_attr13(j), l_attr13(j)), P_DML_RECORD.GMISS_CHAR),
2289 nullif(nvl(l_attr14(j), l_attr14(j)), P_DML_RECORD.GMISS_CHAR),
2290 nullif(nvl(l_attr15(j), l_attr15(j)), P_DML_RECORD.GMISS_CHAR),
2291 nullif(nvl(l_attr16(j), l_attr16(j)), P_DML_RECORD.GMISS_CHAR),
2292 nullif(nvl(l_attr17(j), l_attr17(j)), P_DML_RECORD.GMISS_CHAR),
2293 nullif(nvl(l_attr18(j), l_attr18(j)), P_DML_RECORD.GMISS_CHAR),
2294 nullif(nvl(l_attr19(j), l_attr19(j)), P_DML_RECORD.GMISS_CHAR),
2295 nullif(nvl(l_attr20(j), l_attr20(j)), P_DML_RECORD.GMISS_CHAR)
2296 from dual
2297 where l_action_error_flag(j) is not null
2298 and l_error_flag(j) is null
2299 and l_address_err(j) is not null
2300 and l_country_err(j) is not null
2301 and l_lang_err(j) is not null
2302 and l_timezone_err(j) is not null
2303 and l_flex_val_errors(j) = 0
2304 and l_dss_security_errors(j) = 'T'
2305 and nvl(l_corr_mv_ind(j), 'M') = 'M'
2306 and l_addr_ch_flag(j) = 'Y'
2307 and l_third_party_update_error(j) IS NOT NULL /* bug 4079902 */
2308 and l_createdby_errors(j) IS NOT NULL);
2309 EXCEPTION
2310 WHEN OTHERS THEN
2311 ----dbms_output.put_line('Other exceptions hz_party_sites:' || SQLERRM);
2312 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
2313 hz_utility_v2pub.debug(p_message=>'Other exceptions hz_party_sites:',
2314 p_prefix=>'ERROR',
2315 p_msg_level=>fnd_log.level_error);
2316 hz_utility_v2pub.debug(p_message=>SQLERRM,
2317 p_prefix=>'ERROR',
2318 p_msg_level=>fnd_log.level_error);
2319
2320 END IF;
2321
2322 l_dml_exception := 'Y';
2323 END;
2324
2325 /* record row count for moved records */
2326 FOR k IN 1..l_site_id.count LOOP
2327 l_move_count(k) := SQL%BULK_ROWCOUNT(k);
2328 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2329 hz_utility_v2pub.debug(p_message=>'number of records moved(' || k ||'):' || l_move_count(k),
2330 p_prefix =>l_debug_prefix,
2331 p_msg_level=>fnd_log.level_statement);
2332 END IF;
2333 end loop;
2334
2335 report_errors(P_DML_RECORD, l_dml_exception);
2336
2337 /* update hz_locations if */
2338 /* a) address corrected, or */
2339 /* b) address updated directly */
2340 /* for addr cols, update only if not standardized address or */
2341 /* profile option allows update */
2342 forall j in 1..l_site_id.count
2343 update hz_locations
2344 set LAST_UPDATE_DATE = P_DML_RECORD.SYSDATE,
2345 LAST_UPDATED_BY = P_DML_RECORD.USER_ID,
2346 LAST_UPDATE_LOGIN = P_DML_RECORD.LAST_UPDATE_LOGIN,
2347 REQUEST_ID = P_DML_RECORD.REQUEST_ID,
2348 PROGRAM_APPLICATION_ID = P_DML_RECORD.PROGRAM_APPLICATION_ID,
2349 PROGRAM_ID = P_DML_RECORD.PROGRAM_ID,
2350 PROGRAM_UPDATE_DATE = P_DML_RECORD.SYSDATE,
2351 COUNTRY = decode(VALIDATION_STATUS_CODE, null, -- not standardized, alway ok to update
2352 decode(l_accept_std_flag(j), 'Y', l_country_std(j),
2353 decode(l_country(j), null, COUNTRY, l_country(j))),
2354 decode(l_allow_std_update, 'N', COUNTRY,
2355 decode(l_accept_std_flag(j), 'Y', l_country_std(j),
2356 decode(l_country(j), null, COUNTRY, l_country(j))))),
2357 ADDRESS1 = decode(VALIDATION_STATUS_CODE, null, -- not standardized, alway ok to update
2358 decode(l_accept_std_flag(j), 'Y', l_addr1_std(j),
2359 decode(l_addr1(j), null, ADDRESS1, l_addr1(j))),
2360 decode(l_allow_std_update, 'N', ADDRESS1,
2361 decode(l_accept_std_flag(j), 'Y', l_addr1_std(j),
2362 decode(l_addr1(j), null, ADDRESS1, l_addr1(j))))),
2363 ADDRESS2 = decode(VALIDATION_STATUS_CODE, null, -- not standardized, alway ok to update
2364 decode(l_accept_std_flag(j), 'Y', l_addr2_std(j),
2365 decode(l_addr2(j), null, ADDRESS2, P_DML_RECORD.GMISS_CHAR, null, l_addr2(j))),
2366 decode(l_allow_std_update, 'N', ADDRESS2,
2367 decode(l_accept_std_flag(j), 'Y', l_addr2_std(j),
2368 decode(l_addr2(j), null, ADDRESS2, P_DML_RECORD.GMISS_CHAR, null, l_addr2(j))))),
2369 ADDRESS3 = decode(VALIDATION_STATUS_CODE, null, -- not standardized, alway ok to update
2370 decode(l_accept_std_flag(j), 'Y', l_addr3_std(j),
2371 decode(l_addr3(j), null, ADDRESS3, P_DML_RECORD.GMISS_CHAR, null, l_addr3(j))),
2372 decode(l_allow_std_update, 'N', ADDRESS3,
2373 decode(l_accept_std_flag(j), 'Y', l_addr3_std(j),
2374 decode(l_addr3(j), null, ADDRESS3, P_DML_RECORD.GMISS_CHAR, null, l_addr3(j))))),
2375 ADDRESS4 = decode(VALIDATION_STATUS_CODE, null, -- not standardized, alway ok to update
2376 decode(l_accept_std_flag(j), 'Y', l_addr4_std(j),
2377 decode(l_addr4(j), null, ADDRESS4, P_DML_RECORD.GMISS_CHAR, null, l_addr4(j))),
2378 decode(l_allow_std_update, 'N', ADDRESS4,
2379 decode(l_accept_std_flag(j), 'Y', l_addr4_std(j),
2380 decode(l_addr4(j), null, ADDRESS4, P_DML_RECORD.GMISS_CHAR, null, l_addr4(j))))),
2381 CITY = decode(VALIDATION_STATUS_CODE, null, -- not standardized, alway ok to update
2382 decode(l_accept_std_flag(j), 'Y', l_city_std(j),
2383 decode(l_city(j), null, CITY, P_DML_RECORD.GMISS_CHAR, null, l_city(j))),
2384 decode(l_allow_std_update, 'N', CITY,
2385 decode(l_accept_std_flag(j), 'Y', l_city_std(j),
2386 decode(l_city(j), null, CITY, P_DML_RECORD.GMISS_CHAR, null, l_city(j))))),
2387 POSTAL_CODE = decode(VALIDATION_STATUS_CODE, null, -- not standardized, alway ok to update
2388 decode(l_accept_std_flag(j), 'Y', l_postal_code_std(j),
2389 decode(l_postal_code(j), null, POSTAL_CODE, P_DML_RECORD.GMISS_CHAR, null, l_postal_code(j))),
2390 decode(l_allow_std_update, 'N', POSTAL_CODE,
2391 decode(l_accept_std_flag(j), 'Y', l_postal_code_std(j),
2392 decode(l_postal_code(j), null, POSTAL_CODE, P_DML_RECORD.GMISS_CHAR, null, l_postal_code(j))))),
2393 STATE = decode(VALIDATION_STATUS_CODE, null, -- not standardized, alway ok to update
2394 decode(l_accept_std_flag(j), 'Y', decode(l_province(j), null, l_ps_admin_std(j), null),
2395 decode(l_state(j), null, STATE, P_DML_RECORD.GMISS_CHAR, null, l_state(j))),
2396 decode(l_allow_std_update, 'N', STATE,
2397 decode(l_accept_std_flag(j), 'Y', decode(l_province(j), null, l_ps_admin_std(j), null),
2398 decode(l_state(j), null, STATE, P_DML_RECORD.GMISS_CHAR, null, l_state(j))))),
2399 PROVINCE = decode(VALIDATION_STATUS_CODE, null, -- not standardized, alway ok to update
2400 decode(l_accept_std_flag(j), 'Y', decode(l_province(j), null, null, l_ps_admin_std(j)),
2401 decode(l_province(j), null, PROVINCE, P_DML_RECORD.GMISS_CHAR, null, l_province(j))),
2402 decode(l_allow_std_update, 'N', PROVINCE,
2403 decode(l_accept_std_flag(j), 'Y', decode(l_province(j), null, null, l_ps_admin_std(j)),
2404 decode(l_province(j), null, PROVINCE, P_DML_RECORD.GMISS_CHAR, null, l_province(j))))),
2405 COUNTY = decode(VALIDATION_STATUS_CODE, null, -- not standardized, alway ok to update
2406 decode(l_accept_std_flag(j), 'Y', l_county_std(j),
2407 decode(l_county(j), null, COUNTY, P_DML_RECORD.GMISS_CHAR, null, l_county(j))),
2408 decode(l_allow_std_update, 'N', COUNTY,
2409 decode(l_accept_std_flag(j), 'Y', l_county_std(j),
2410 decode(l_county(j), null, COUNTY, P_DML_RECORD.GMISS_CHAR, null, l_county(j))))),
2411 ADDRESS_LINES_PHONETIC = DECODE(l_addr_phonetic(j), NULL, ADDRESS_LINES_PHONETIC, P_DML_RECORD.GMISS_CHAR, NULL, l_addr_phonetic(j)),
2412 POSTAL_PLUS4_CODE = DECODE(l_postal_plus4(j), NULL, POSTAL_PLUS4_CODE, P_DML_RECORD.GMISS_CHAR, NULL, l_postal_plus4(j)),
2413 DELIVERY_POINT_CODE = DECODE(l_delvy_pt_code(j), NULL, DELIVERY_POINT_CODE, P_DML_RECORD.GMISS_CHAR, NULL, l_delvy_pt_code(j)),
2414 LOCATION_DIRECTIONS = DECODE(l_loc_dir(j), NULL, LOCATION_DIRECTIONS, P_DML_RECORD.GMISS_CHAR, NULL, l_loc_dir(j)),
2415 CLLI_CODE = DECODE(l_clli_code(j), NULL, CLLI_CODE, P_DML_RECORD.GMISS_CHAR, NULL, l_clli_code(j)),
2416 LANGUAGE = DECODE(l_language(j), NULL, LANGUAGE, P_DML_RECORD.GMISS_CHAR, NULL, l_language(j)),
2417 SHORT_DESCRIPTION = DECODE(l_short_desc(j), NULL, SHORT_DESCRIPTION, P_DML_RECORD.GMISS_CHAR, NULL, l_short_desc(j)),
2418 DESCRIPTION = DECODE(l_desc(j), NULL, DESCRIPTION, P_DML_RECORD.GMISS_CHAR, NULL, l_desc(j)),
2419 SALES_TAX_GEOCODE = DECODE(l_sales_tax_code(j), NULL, SALES_TAX_GEOCODE, P_DML_RECORD.GMISS_CHAR, NULL, l_sales_tax_code(j)),
2420 SALES_TAX_INSIDE_CITY_LIMITS = DECODE(l_sales_tax_limit(j), NULL, SALES_TAX_INSIDE_CITY_LIMITS, P_DML_RECORD.GMISS_CHAR, NULL, l_sales_tax_limit(j)),
2421 OBJECT_VERSION_NUMBER = OBJECT_VERSION_NUMBER +1,
2422 --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))),
2423 APPLICATION_ID = nvl(APPLICATION_ID, P_DML_RECORD.APPLICATION_ID),
2424 TIMEZONE_ID = DECODE(l_timezone_code(j), NULL, TIMEZONE_ID, P_DML_RECORD.GMISS_CHAR, NULL, l_timezone(j)),
2425 ACTUAL_CONTENT_SOURCE = P_DML_RECORD.ACTUAL_CONTENT_SRC,
2426 /* if validation_status_code was null, the location was not standardized.
2427 For standardized address, status would not change if not allowed to update
2428 standardized address.
2429 Otherwise, just set value according to accept standardized flag
2430 */
2431 VALIDATION_STATUS_CODE = decode(VALIDATION_STATUS_CODE, null,
2432 decode(l_accept_std_flag(j), 'Y', l_valid_status_code(j), null),
2433 decode(l_allow_std_update, 'N', VALIDATION_STATUS_CODE,
2434 decode(l_accept_std_flag(j), 'Y', l_valid_status_code(j), null))),
2435 DATE_VALIDATED = decode(VALIDATION_STATUS_CODE, null,
2436 decode(l_accept_std_flag(j), 'Y', l_date_validated(j), null),
2437 decode(l_allow_std_update, 'N', DATE_VALIDATED,
2438 decode(l_accept_std_flag(j), 'Y', l_date_validated(j), null)))
2439 where location_id = l_location_id(j)
2440 and l_corr_upd_count(j) = 1;
2441
2442 forall j in 1..l_site_id.count
2443 update hz_party_sites
2444 set ATTRIBUTE_CATEGORY = DECODE(l_attr_category(j), NULL, ATTRIBUTE_CATEGORY, P_DML_RECORD.GMISS_CHAR, NULL, l_attr_category(j)),
2445 ATTRIBUTE1 = DECODE(l_attr1(j), NULL, attribute1, P_DML_RECORD.GMISS_CHAR, NULL, l_attr1(j)),
2446 ATTRIBUTE2 = DECODE(l_attr2(j), NULL, attribute2, P_DML_RECORD.GMISS_CHAR, NULL, l_attr2(j)),
2447 ATTRIBUTE3 = DECODE(l_attr3(j), NULL, attribute3, P_DML_RECORD.GMISS_CHAR, NULL, l_attr3(j)),
2448 ATTRIBUTE4 = DECODE(l_attr4(j), NULL, attribute4, P_DML_RECORD.GMISS_CHAR, NULL, l_attr4(j)),
2449 ATTRIBUTE5 = DECODE(l_attr5(j), NULL, attribute5, P_DML_RECORD.GMISS_CHAR, NULL, l_attr5(j)),
2450 ATTRIBUTE6 = DECODE(l_attr6(j), NULL, attribute6, P_DML_RECORD.GMISS_CHAR, NULL, l_attr6(j)),
2451 ATTRIBUTE7 = DECODE(l_attr7(j), NULL, attribute7, P_DML_RECORD.GMISS_CHAR, NULL, l_attr7(j)),
2452 ATTRIBUTE8 = DECODE(l_attr8(j), NULL, attribute8, P_DML_RECORD.GMISS_CHAR, NULL, l_attr8(j)),
2453 ATTRIBUTE9 = DECODE(l_attr9(j), NULL, attribute9, P_DML_RECORD.GMISS_CHAR, NULL, l_attr9(j)),
2454 ATTRIBUTE10 = DECODE(l_attr10(j), NULL, attribute10, P_DML_RECORD.GMISS_CHAR, NULL, l_attr10(j)),
2455 ATTRIBUTE11 = DECODE(l_attr11(j), NULL, attribute11, P_DML_RECORD.GMISS_CHAR, NULL, l_attr11(j)),
2456 ATTRIBUTE12 = DECODE(l_attr12(j), NULL, attribute12, P_DML_RECORD.GMISS_CHAR, NULL, l_attr12(j)),
2457 ATTRIBUTE13 = DECODE(l_attr13(j), NULL, attribute13, P_DML_RECORD.GMISS_CHAR, NULL, l_attr13(j)),
2458 ATTRIBUTE14 = DECODE(l_attr14(j), NULL, attribute14, P_DML_RECORD.GMISS_CHAR, NULL, l_attr14(j)),
2459 ATTRIBUTE15 = DECODE(l_attr15(j), NULL, attribute15, P_DML_RECORD.GMISS_CHAR, NULL, l_attr15(j)),
2460 ATTRIBUTE16 = DECODE(l_attr16(j), NULL, attribute16, P_DML_RECORD.GMISS_CHAR, NULL, l_attr16(j)),
2461 ATTRIBUTE17 = DECODE(l_attr17(j), NULL, attribute17, P_DML_RECORD.GMISS_CHAR, NULL, l_attr17(j)),
2462 ATTRIBUTE18 = DECODE(l_attr18(j), NULL, attribute18, P_DML_RECORD.GMISS_CHAR, NULL, l_attr18(j)),
2463 ATTRIBUTE19 = DECODE(l_attr19(j), NULL, attribute19, P_DML_RECORD.GMISS_CHAR, NULL, l_attr19(j)),
2464 ATTRIBUTE20 = DECODE(l_attr20(j), NULL, attribute20, P_DML_RECORD.GMISS_CHAR, NULL, l_attr20(j))
2465 where hz_party_sites.party_site_id = l_site_id(j)
2466 and l_corr_upd_count(j) = 1;
2467
2468
2469 /* insert into hz_location_profiles with user data if maintaining old record */
2470 /* insert if */
2471 /* 1) record moved, or */
2472 /* 2) record corrected, and maintaining history and old record exist,or */
2473 /* 3) record update, and maintaining historyand old record exist, */
2474 ForAll j in 1..l_site_id.count
2475 insert into HZ_LOCATION_PROFILES
2476 ( LOCATION_ID, LOCATION_PROFILE_ID,
2477 ADDRESS1, ADDRESS2, ADDRESS3, ADDRESS4,
2478 CITY, PROV_STATE_ADMIN_CODE,
2479 COUNTY, COUNTRY, POSTAL_CODE,
2480 ACTUAL_CONTENT_SOURCE,
2481 EFFECTIVE_START_DATE,
2482 validation_sst_flag,
2483 OBJECT_VERSION_NUMBER,
2484 LAST_UPDATE_DATE,
2485 LAST_UPDATED_BY,
2486 LAST_UPDATE_LOGIN,
2487 CREATION_DATE,
2488 CREATED_BY,
2489 REQUEST_ID,
2490 PROGRAM_APPLICATION_ID,
2491 PROGRAM_ID,
2492 PROGRAM_UPDATE_DATE
2493 )
2494 ( select
2495 decode(l_move_count(j), 1, l_new_loc_id(j), l_location_id(j)),
2496 hz_location_profiles_s.NextVal,
2497 nvl(l_addr1(j), l_old_addr1(j)),
2498 nullif(nvl(l_addr2(j), l_old_addr2(j)), P_DML_RECORD.GMISS_CHAR),
2499 nullif(nvl(l_addr3(j), l_old_addr3(j)), P_DML_RECORD.GMISS_CHAR),
2500 nullif(nvl(l_addr4(j), l_old_addr4(j)), P_DML_RECORD.GMISS_CHAR),
2501 nullif(nvl(l_city(j), l_old_city(j)), P_DML_RECORD.GMISS_CHAR),
2502 nullif( nvl(decode(l_state(j), null, l_province(j),
2503 P_DML_RECORD.GMISS_CHAR, l_province(j), l_state(j)),
2504 nvl(l_old_state(j), l_old_province(j))),
2505 P_DML_RECORD.GMISS_CHAR),
2506 nullif(nvl(l_county(j), l_old_county(j)), P_DML_RECORD.GMISS_CHAR),
2507 nvl(l_country(j), l_old_country(j)),
2508 nullif(nvl(l_postal_code(j), l_old_postal_code(j)), P_DML_RECORD.GMISS_CHAR),
2509 --'ACS for usr data', --P_CONTENT_SRC_TYPE,
2510 P_DML_RECORD.ACTUAL_CONTENT_SRC,
2511 P_DML_RECORD.SYSDATE,
2512 'Y', -- validation_sst_flag
2513 1,
2514 P_DML_RECORD.SYSDATE,
2515 P_DML_RECORD.USER_ID,
2516 P_DML_RECORD.LAST_UPDATE_LOGIN,
2517 P_DML_RECORD.SYSDATE,
2518 P_DML_RECORD.USER_ID,
2519 P_DML_RECORD.REQUEST_ID,
2520 P_DML_RECORD.PROGRAM_APPLICATION_ID,
2521 P_DML_RECORD.PROGRAM_ID,
2522 P_DML_RECORD.SYSDATE
2523 from dual
2524 where ( l_maintain_loc_hist = 'Y' and (l_init_upd_count(j) = 1) )
2525 or l_move_count(j) = 1
2526 );
2527
2528 /* modify location profiles of std data if std data exist and */
2529 /* 1) record corrected, or */
2530 /* 2) record update */
2531 /* If maitaining history, end-date eixisting one. Otherwise update it. */
2532 forall j in 1..l_site_id.count
2533 update hz_location_profiles
2534 set EFFECTIVE_END_DATE = decode(l_maintain_loc_hist, 'Y',
2535 --do not end-date if sst and not allowed to
2536 --correct standardized address
2537 decode(validation_sst_flag, 'Y',
2538 decode(l_allow_std_update, 'N', null, P_DML_RECORD.SYSDATE),
2539 P_DML_RECORD.SYSDATE),
2540 null),
2541 address1 = decode(l_maintain_loc_hist, 'Y', address1,
2542 --do not update if sst and not allowed to
2543 --correct standardized address
2544 decode(validation_sst_flag, 'Y',
2545 decode(l_allow_std_update, 'N', address1, l_addr1_std(j)),
2546 l_addr1_std(j))),
2547 address2 = decode(l_maintain_loc_hist, 'Y', address2,
2548 decode(validation_sst_flag, 'Y',
2549 decode(l_allow_std_update, 'N', address2, l_addr2_std(j)),
2550 l_addr2_std(j))),
2551 address3 = decode(l_maintain_loc_hist, 'Y', address3,
2552 decode(validation_sst_flag, 'Y',
2553 decode(l_allow_std_update, 'N', address3, l_addr3_std(j)),
2554 l_addr3_std(j))),
2555 address4 = decode(l_maintain_loc_hist, 'Y', address4,
2556 decode(validation_sst_flag, 'Y',
2557 decode(l_allow_std_update, 'N', address4, l_addr4_std(j)),
2558 l_addr4_std(j))),
2559 city = decode(l_maintain_loc_hist, 'Y', city,
2560 decode(validation_sst_flag, 'Y',
2561 decode(l_allow_std_update, 'N', city, l_city_std(j)),
2562 l_city_std(j))),
2563 prov_state_admin_code = decode(l_maintain_loc_hist, 'Y', prov_state_admin_code,
2564 decode(validation_sst_flag, 'Y',
2565 decode(l_allow_std_update, 'N', prov_state_admin_code, l_ps_admin_std(j)),
2566 l_ps_admin_std(j))),
2567 county = decode(l_maintain_loc_hist, 'Y', county,
2568 decode(validation_sst_flag, 'Y',
2569 decode(l_allow_std_update, 'N', county, l_county_std(j)),
2570 l_county_std(j))),
2571 country = decode(l_maintain_loc_hist, 'Y', country,
2572 decode(validation_sst_flag, 'Y',
2573 decode(l_allow_std_update, 'N', country, l_country_std(j)),
2574 l_country_std(j))),
2575 postal_code = decode(l_maintain_loc_hist, 'Y', postal_code,
2576 decode(validation_sst_flag, 'Y',
2577 decode(l_allow_std_update, 'N', postal_code, l_postal_code_std(j)),
2578 l_postal_code_std(j))),
2579 validation_sst_flag = decode(l_maintain_loc_hist, 'Y', validation_sst_flag,
2580 decode(l_allow_std_update, 'Y', decode(l_accept_std_flag(j), 'Y', 'Y', 'N'),
2581 -- if not allowed to update standardized
2582 decode(l_val_status_code(j), null,
2583 -- not a standarized address
2584 decode(l_accept_std_flag(j), 'Y', 'Y', 'N'),
2585 validation_sst_flag))),
2586 validation_status_code = decode(l_maintain_loc_hist, 'Y', validation_status_code, l_valid_status_code(j)),
2587 date_validated = decode(l_maintain_loc_hist, 'Y', date_validated, P_DML_RECORD.SYSDATE),
2588 object_version_number = object_version_number+1,
2589 last_update_date = P_DML_RECORD.SYSDATE,
2590 last_updated_by = P_DML_RECORD.USER_ID,
2591 last_update_login = P_DML_RECORD.LAST_UPDATE_LOGIN
2592 where location_id = l_location_id(j) /* */
2593 and actual_content_source = l_adptr_content_src(j) /* keys for location profile */
2594 and nullif(effective_end_date, c_end_date) is null /* */
2595 and (l_corr_upd_count(j) = 1)
2596 and l_accept_std_flag(j) is not null; -- make sure std data available
2597
2598 /* get row count for updated location profiles */
2599 FOR k IN 1..l_site_id.count LOOP
2600 l_temp_upd_count(k) := SQL%BULK_ROWCOUNT(k);
2601 end loop;
2602
2603 /* insert into hz_location_profiles with std data */
2604 /* insert if std data available and the location record is */
2605 /* 1) record moved or */
2606 /* 2) record corrected, and maintaining history ,or */
2607 /* 3) record update, and maintaining history , or */
2608 /* 4) record corrected, but no existing hz_location_profiles (l_temp_upd_count(k) = 0), or */
2609 /* 5) record update, but no existing hz_location_profiles (l_temp_upd_count(k) = 0) */
2610 if l_allow_std_update = 'Y' then
2611 ForAll j in 1..l_site_id.count
2612 insert into HZ_LOCATION_PROFILES
2613 ( LOCATION_PROFILE_ID, LOCATION_ID,
2614 ADDRESS1, ADDRESS2, ADDRESS3, ADDRESS4,
2615 CITY, PROV_STATE_ADMIN_CODE,
2616 COUNTY, COUNTRY, POSTAL_CODE,
2617 ACTUAL_CONTENT_SOURCE,
2618 EFFECTIVE_START_DATE,
2619 validation_status_code,
2620 DATE_VALIDATED,
2621 OBJECT_VERSION_NUMBER,
2622 LAST_UPDATE_DATE,
2623 LAST_UPDATED_BY,
2624 LAST_UPDATE_LOGIN,
2625 CREATION_DATE,
2626 CREATED_BY,
2627 VALIDATION_SST_FLAG,
2628 REQUEST_ID,
2629 PROGRAM_APPLICATION_ID,
2630 PROGRAM_ID,
2631 PROGRAM_UPDATE_DATE
2632 )
2633 ( select
2634 hz_location_profiles_s.NextVal,
2635 decode(l_move_count(j), 1, l_new_loc_id(j), l_location_id(j)),
2636 l_addr1_std(j),
2637 l_addr2_std(j),
2638 l_addr3_std(j),
2639 l_addr4_std(j),
2640 l_city_std(j),
2641 l_ps_admin_std(j),
2642 l_county_std(j),
2643 l_country_std(j),
2644 l_postal_code_std(j),
2645 l_adptr_content_src(j), --ACTUAL_CONTENT_SOURCE
2646 --'ACS for std data',
2647 P_DML_RECORD.SYSDATE,
2648 l_valid_status_code(j), -- validation_status_code
2649 P_DML_RECORD.SYSDATE, -- DATE_VALIDATED
2650 1,
2651 P_DML_RECORD.SYSDATE,
2652 P_DML_RECORD.USER_ID,
2653 P_DML_RECORD.LAST_UPDATE_LOGIN,
2654 P_DML_RECORD.SYSDATE,
2655 P_DML_RECORD.USER_ID,
2656 decode(l_accept_std_flag(j), 'Y', 'Y', 'N'),
2657 P_DML_RECORD.REQUEST_ID,
2658 P_DML_RECORD.PROGRAM_APPLICATION_ID,
2659 P_DML_RECORD.PROGRAM_ID,
2660 P_DML_RECORD.SYSDATE
2661 from dual
2662 where l_accept_std_flag(j) is not null
2663 and (l_move_count(j) = 1 -- record moved
2664 or ( l_corr_upd_count(j) = 1 -- record corrected/updated
2665 and ( l_temp_upd_count(j) = 0 -- std loc profile does not exist
2666 or l_maintain_loc_hist = 'Y' -- to maintain history
2667 ))));
2668 else
2669 ForAll j in 1..l_site_id.count
2670 insert into HZ_LOCATION_PROFILES
2671 ( LOCATION_PROFILE_ID, LOCATION_ID,
2672 ADDRESS1, ADDRESS2, ADDRESS3, ADDRESS4,
2673 CITY, PROV_STATE_ADMIN_CODE,
2674 COUNTY, COUNTRY, POSTAL_CODE,
2675 ACTUAL_CONTENT_SOURCE,
2676 EFFECTIVE_START_DATE,
2677 validation_status_code,
2678 DATE_VALIDATED,
2679 OBJECT_VERSION_NUMBER,
2680 LAST_UPDATE_DATE,
2681 LAST_UPDATED_BY,
2682 LAST_UPDATE_LOGIN,
2683 CREATION_DATE,
2684 CREATED_BY,
2685 VALIDATION_SST_FLAG,
2686 EFFECTIVE_END_DATE,
2687 REQUEST_ID,
2688 PROGRAM_APPLICATION_ID,
2689 PROGRAM_ID,
2690 PROGRAM_UPDATE_DATE
2691 )
2692 ( select
2693 hz_location_profiles_s.NextVal,
2694 decode(l_move_count(j), 1, l_new_loc_id(j), l_location_id(j)),
2695 l_addr1_std(j),
2696 l_addr2_std(j),
2697 l_addr3_std(j),
2698 l_addr4_std(j),
2699 l_city_std(j),
2700 l_ps_admin_std(j),
2701 l_county_std(j),
2702 l_country_std(j),
2703 l_postal_code_std(j),
2704 l_adptr_content_src(j), --ACTUAL_CONTENT_SOURCE
2705 --'ACS for std data',
2706 P_DML_RECORD.SYSDATE,
2707 l_valid_status_code(j), -- validation_status_code
2708 P_DML_RECORD.SYSDATE, -- DATE_VALIDATED
2709 1,
2710 P_DML_RECORD.SYSDATE,
2711 P_DML_RECORD.USER_ID,
2712 P_DML_RECORD.LAST_UPDATE_LOGIN,
2713 P_DML_RECORD.SYSDATE,
2714 P_DML_RECORD.USER_ID,
2715 decode(l_move_count(j), 1, decode(l_accept_std_flag(j), 'Y', 'Y', 'N'),
2716 -- record corrected/moved
2717 decode(l_val_status_code(j), null,
2718 -- not a standarized address
2719 decode(l_accept_std_flag(j), 'Y', 'Y', 'N'),
2720 -- get old value
2721 -- if l_old_profile_sst_flag is null, the profile is new. There must be
2722 -- another profile with SST = 'Y'. Set this to 'N'
2723 nvl(l_old_profile_sst_flag(j), 'N'))),
2724 decode(l_move_count(j), 1,null,
2725 -- record corrected/moved
2726 decode(l_val_status_code(j), null,
2727 -- not a standardized address, don't end-date new profile
2728 null,
2729 -- a standardized address,
2730 -- if l_old_profile_sst_flag(j) is null, no old profile exists,
2731 -- don't end-date this one
2732 -- of l_old_profile_sst_flag(j) = 'Y', old profile exists and is SST,
2733 -- end-date it
2734 -- else it is an updated profile, don't end-date
2735 decode(l_old_profile_sst_flag(j), 'Y', P_DML_RECORD.SYSDATE, null))),
2736 P_DML_RECORD.REQUEST_ID,
2737 P_DML_RECORD.PROGRAM_APPLICATION_ID,
2738 P_DML_RECORD.PROGRAM_ID,
2739 P_DML_RECORD.SYSDATE
2740 from dual
2741 where l_accept_std_flag(j) is not null
2742 and (l_move_count(j) = 1 -- record moved
2743 or ( l_corr_upd_count(j) = 1 -- record corrected/updated
2744 and ( l_temp_upd_count(j) = 0 -- std loc profile does not exist
2745 or l_maintain_loc_hist = 'Y' -- to maintain history
2746 ))));
2747 end if;
2748
2749 /* end-date old mosr entries if */
2750 /* 1) record moved
2751 /* 2) record corrected and osr change (collision or not) */
2752 forall j in 1..l_site_id.count
2753 update hz_orig_sys_references mosr
2754 set status = 'I',
2755 last_updated_by = P_DML_RECORD.USER_ID,
2756 last_update_login = P_DML_RECORD.LAST_UPDATE_LOGIN,
2757 last_update_date = P_DML_RECORD.SYSDATE,
2758 end_date_active = P_DML_RECORD.SYSDATE,
2759 object_version_number = object_version_number + 1
2760 where status = 'A'
2761 and orig_system = l_site_orig_system(j)
2762 and orig_system_reference = l_old_site_osr(j)
2763 and owner_table_name = 'HZ_PARTY_SITES'
2764 and ( l_move_count(j) = 1 or
2765 (l_corr_upd_count(j) = 1 and
2766 l_site_orig_system_reference(j) <> l_old_site_osr(j)));
2767
2768 /* insert into hz_locations with user or std data based on accept std flag */
2769 /* insert if move */
2770 forall j in 1..l_site_id.count
2771 insert into hz_locations
2772 ( LOCATION_ID,
2773 LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN,
2774 CONTENT_SOURCE_TYPE,
2775 ACTUAL_CONTENT_SOURCE,
2776 REQUEST_ID, PROGRAM_APPLICATION_ID, PROGRAM_ID, PROGRAM_UPDATE_DATE,
2777 ORIG_SYSTEM_REFERENCE,
2778 COUNTRY, ADDRESS1, ADDRESS2, ADDRESS3, ADDRESS4,
2779 CITY, POSTAL_CODE, STATE, PROVINCE, COUNTY,
2780 VALIDATED_FLAG,
2781 ADDRESS_LINES_PHONETIC, POSTAL_PLUS4_CODE, TIMEZONE_ID,
2782 LOCATION_DIRECTIONS, CLLI_CODE, LANGUAGE,
2783 SHORT_DESCRIPTION, DESCRIPTION,
2784 DELIVERY_POINT_CODE,
2785 SALES_TAX_GEOCODE,
2786 SALES_TAX_INSIDE_CITY_LIMITS,
2787 GEOMETRY_STATUS_CODE, OBJECT_VERSION_NUMBER, CREATED_BY_MODULE, APPLICATION_ID,
2788 VALIDATION_STATUS_CODE, DATE_VALIDATED
2789 )
2790 ( select
2791 l_new_loc_id(j),
2792 P_DML_RECORD.SYSDATE, P_DML_RECORD.USER_ID, P_DML_RECORD.SYSDATE, P_DML_RECORD.USER_ID, P_DML_RECORD.USER_ID,
2793 'USER_ENTERED',
2794 P_DML_RECORD.ACTUAL_CONTENT_SRC, -- ACTUAL_CONTENT_SOURCE
2795 --'ACS for usr/std data',
2796 P_DML_RECORD.REQUEST_ID,
2797 P_DML_RECORD.PROGRAM_APPLICATION_ID, P_DML_RECORD.PROGRAM_ID, P_DML_RECORD.SYSDATE,
2798 l_site_orig_system_reference(j),
2799 decode(l_accept_std_flag(j), 'Y', l_country_std(j), nvl(l_country(j), l_old_country(j))),
2800 decode(l_accept_std_flag(j), 'Y', l_addr1_std(j), nvl(l_addr1(j), l_old_addr1(j))),
2801 decode(l_accept_std_flag(j), 'Y', l_addr2_std(j), nullif(nvl(l_addr2(j), l_old_addr2(j)), P_DML_RECORD.GMISS_CHAR)),
2802 decode(l_accept_std_flag(j), 'Y', l_addr3_std(j), nullif(nvl(l_addr3(j), l_old_addr3(j)), P_DML_RECORD.GMISS_CHAR)),
2803 decode(l_accept_std_flag(j), 'Y', l_addr4_std(j), nullif(nvl(l_addr4(j), l_old_addr4(j)), P_DML_RECORD.GMISS_CHAR)),
2804 decode(l_accept_std_flag(j), 'Y', l_city_std(j), nullif(nvl(l_city(j), l_old_city(j)), P_DML_RECORD.GMISS_CHAR)),
2805 decode(l_accept_std_flag(j), 'Y', l_postal_code_std(j), nullif(nvl(l_postal_code(j), l_old_postal_code(j)), P_DML_RECORD.GMISS_CHAR)),
2806 decode(l_accept_std_flag(j), 'Y', decode(l_province(j), null, l_ps_admin_std(j), null), nullif(nvl(l_state(j), l_old_state(j)), P_DML_RECORD.GMISS_CHAR)),
2807 decode(l_accept_std_flag(j), 'Y', decode(l_province(j), null, null, l_ps_admin_std(j)), nullif(nvl(l_province(j), l_old_province(j)), P_DML_RECORD.GMISS_CHAR)),
2808 decode(l_accept_std_flag(j), 'Y', l_county_std(j), nullif(nvl(l_county(j), l_old_county(j)), P_DML_RECORD.GMISS_CHAR)),
2809 'N',
2810 nullif(nvl(l_addr_phonetic(j), l_old_addr_phonetic(j)), P_DML_RECORD.GMISS_CHAR),
2811 nullif(nvl(l_postal_plus4(j), l_old_postal_plus4(j)), P_DML_RECORD.GMISS_CHAR),
2812 decode(l_timezone_code(j), null, l_old_timezone(j), P_DML_RECORD.GMISS_CHAR, null, l_timezone(j)),
2813 nullif(nvl(l_loc_dir(j), l_old_loc_dir(j)), P_DML_RECORD.GMISS_CHAR),
2814 nullif(nvl(l_clli_code(j), l_old_clli_code(j)),P_DML_RECORD.GMISS_CHAR),
2815 nullif(nvl(l_language(j), l_old_language(j)),P_DML_RECORD.GMISS_CHAR),
2816 nullif(nvl(l_short_desc(j), l_old_short_desc(j)),P_DML_RECORD.GMISS_CHAR),
2817 nullif(nvl(l_desc(j),l_old_desc(j)), P_DML_RECORD.GMISS_CHAR),
2818 nullif(nvl(l_delvy_pt_code(j), l_old_delvy_pt_code(j)), P_DML_RECORD.GMISS_CHAR),
2819 nullif(nvl(l_sales_tax_code(j),l_old_sales_tax_code(j)), P_DML_RECORD.GMISS_CHAR),
2820 nullif(nvl(l_sales_tax_limit(j),l_old_sales_tax_limit(j)), P_DML_RECORD.GMISS_CHAR),
2821 'DIRTY', 1,
2822 nvl(l_created_by_module(j), 'HZ_IMPORT'),
2823 P_DML_RECORD.APPLICATION_ID,
2824 decode(l_accept_std_flag(j), 'Y', l_valid_status_code(j), null),
2825 decode(l_accept_std_flag(j), 'Y', l_date_validated(j), null)
2826 from dual
2827 where l_move_count(j) = 1);
2828
2829 /* end-date collided entries if */
2830 /* 1) record moved and collision happens
2831 /* 2) record corrected and collision happens */
2832 forall j in 1..l_site_id.count
2833 update hz_orig_sys_references mosr
2834 set status = 'I',
2835 last_updated_by = P_DML_RECORD.USER_ID,
2836 last_update_login = P_DML_RECORD.LAST_UPDATE_LOGIN,
2837 last_update_date = P_DML_RECORD.SYSDATE,
2838 end_date_active = P_DML_RECORD.SYSDATE
2839 ,--reason_code = 'end-dated mosr',
2840 object_version_number = object_version_number + 1
2841 where status = 'A'
2842 and orig_system = l_site_orig_system(j)
2843 and orig_system_reference = l_site_orig_system_reference(j)
2844 and owner_table_name = 'HZ_PARTY_SITES'
2845 and l_NEW_OSR_EXISTS(j) is not null
2846 and (l_move_count(j) = 1 or l_corr_upd_count(j) = 1)
2847 ;
2848 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2849 FOR k IN 1..l_site_id.count LOOP
2850 hz_utility_v2pub.debug(p_message=>'end-dating collided mosr(' || k ||'):' || SQL%BULK_ROWCOUNT(k),
2851 p_prefix =>l_debug_prefix,
2852 p_msg_level=>fnd_log.level_statement);
2853 END LOOP;
2854 END IF;
2855
2856 /* insert new mosr entrues if */
2857 /* 1) record moved, or */
2858 /* 2) record corrected and osr change */
2859 /* insert new mosr entry for new address */
2860 ForAll j in 1..l_site_id.count
2861 insert into HZ_ORIG_SYS_REFERENCES
2862 ( ORIG_SYSTEM_REF_ID,
2863 ORIG_SYSTEM,
2864 ORIG_SYSTEM_REFERENCE,
2865 OWNER_TABLE_NAME,
2866 OWNER_TABLE_ID,
2867 STATUS,
2868 START_DATE_ACTIVE,
2869 CREATED_BY,
2870 CREATION_DATE,
2871 LAST_UPDATED_BY,
2872 LAST_UPDATE_DATE,
2873 LAST_UPDATE_LOGIN,
2874 CREATED_BY_MODULE,
2875 APPLICATION_ID,
2876 OBJECT_VERSION_NUMBER,
2877 PARTY_ID,
2878 REQUEST_ID,
2879 PROGRAM_APPLICATION_ID,
2880 PROGRAM_ID,
2881 PROGRAM_UPDATE_DATE
2882 )
2883 (select
2884 HZ_ORIG_SYSTEM_REF_S.NEXTVAL,
2885 l_site_orig_system(j),
2886 l_site_orig_system_reference(j),
2887 'HZ_PARTY_SITES',
2888 decode(l_move_count(j),1,l_new_site_id(j),l_site_id(j)),
2889 'A',
2890 P_DML_RECORD.SYSDATE,
2891 P_DML_RECORD.USER_ID,
2892 P_DML_RECORD.SYSDATE,
2893 P_DML_RECORD.USER_ID,
2894 P_DML_RECORD.SYSDATE,
2895 P_DML_RECORD.LAST_UPDATE_LOGIN,
2896 nvl(l_created_by_module(j), 'HZ_IMPORT'),
2897 P_DML_RECORD.APPLICATION_ID,
2898 1,
2899 l_party_id(j),
2900 P_DML_RECORD.REQUEST_ID,
2901 P_DML_RECORD.PROGRAM_APPLICATION_ID,
2902 P_DML_RECORD.PROGRAM_ID,
2903 P_DML_RECORD.SYSDATE
2904 from dual
2905 where ( l_move_count(j) = 1 or
2906 (l_corr_upd_count(j) = 1 and
2907 l_site_orig_system_reference(j) <> l_old_site_osr(j)))
2908 );
2909
2910 /* */
2911 /* Perform remaining process for moved records */
2912 /* */
2913 /* invalidate old party sites */
2914 /* Bug 5383200 : when doing OSR change and move address with l_NEW_OSR_EXISTS(j) = 'Y'
2915 the l_owning_party_id(j) will be NULL
2916 Add logic to handle this case */
2917
2918 forall j in 1..l_site_id.count
2919 update hz_party_sites hz_ps
2920 set program_update_date = P_DML_RECORD.SYSDATE,
2921 status = 'I',
2922 last_update_date = P_DML_RECORD.SYSDATE,
2923 last_updated_by = P_DML_RECORD.USER_ID,
2924 last_update_login = P_DML_RECORD.LAST_UPDATE_LOGIN,
2925 object_version_number = object_version_number + 1,
2926 identifying_address_flag = 'N',
2927 --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))),
2928 APPLICATION_ID = nvl(APPLICATION_ID, P_DML_RECORD.APPLICATION_ID)
2929 where hz_ps.party_site_id = l_site_id(j)
2930 and (l_owning_party_id(j)= l_party_id(j)
2931 OR l_site_orig_system_reference(j) <> l_old_site_osr(j) OR l_new_osr_exists(j) = 'Y')
2932 and l_move_count(j) = 1;
2933
2934 /* Reset SST flag of other profiles of other actual content source */
2935 forall j in 1..l_site_id.count
2936 update hz_location_profiles
2937 set validation_sst_flag = 'N',
2938 object_version_number = object_version_number + 1,
2939 last_update_date = P_DML_RECORD.SYSDATE,
2940 last_updated_by = P_DML_RECORD.USER_ID,
2941 last_update_login = P_DML_RECORD.LAST_UPDATE_LOGIN
2942 where actual_content_source
2943 not in ( P_DML_RECORD.ACTUAL_CONTENT_SRC, l_adptr_content_src(j))
2944 and nullif(effective_end_date, c_end_date) is null
2945 and location_id = l_location_id(j)
2946 and l_accept_std_flag(j) = 'Y'
2947 and (l_allow_std_update = 'Y' or l_val_status_code(j) is null);
2948
2949
2950 /* DE-NORM */
2951 /* For corrected address, de-norm the value to hz_parties if */
2952 /* 1) it is primary address, and */
2953 /* 2) it is not standarzed or allowed update to standardized address */
2954
2955 /* bug fix 3851810 */
2956 /* If DNB is not selected as a visible data soruce, we should not */
2957 /* denormalize it even it is the first active address created for the */
2958 /* party. We should only denormalize the visible address. */
2959
2960 -- check if the data source is seleted.
2961
2962
2963 /* Commented the code for bug 4079902. */
2964
2965 /*
2966 HZ_MIXNM_UTILITY.LoadDataSources(
2967 p_entity_name => 'HZ_LOCATIONS',
2968 p_entity_attr_id => g_pst_entity_attr_id,
2969 p_mixnmatch_enabled => g_pst_mixnmatch_enabled,
2970 p_selected_datasources => g_pst_selected_datasources );
2971
2972 g_pst_is_datasource_selected :=
2973 HZ_MIXNM_UTILITY.isDataSourceSelected (
2974 p_selected_datasources => g_pst_selected_datasources,
2975 p_actual_content_source => p_dml_record.actual_content_src );
2976
2977 IF g_pst_is_datasource_selected = 'Y' THEN
2978 */
2979 forall j in 1..l_site_id.count
2980 update hz_parties
2981 set country = decode(l_accept_std_flag(j), 'Y', l_country_std(j),
2982 decode(l_country(j), null, COUNTRY, l_country(j))),
2983 address1 = decode(l_accept_std_flag(j), 'Y', l_addr1_std(j),
2984 decode(l_addr1(j), null, ADDRESS1, l_addr1(j))),
2985 address2 = decode(l_accept_std_flag(j), 'Y', l_addr2_std(j),
2986 decode(l_addr2(j), null, ADDRESS2, P_DML_RECORD.GMISS_CHAR, null, l_addr2(j))),
2987 address3 = decode(l_accept_std_flag(j), 'Y', l_addr3_std(j),
2988 decode(l_addr3(j), null, ADDRESS3, P_DML_RECORD.GMISS_CHAR, null, l_addr3(j))),
2989 address4 = decode(l_accept_std_flag(j), 'Y', l_addr4_std(j),
2990 decode(l_addr4(j), null, ADDRESS4, P_DML_RECORD.GMISS_CHAR, null, l_addr4(j))),
2991 county = decode(l_accept_std_flag(j), 'Y', l_county_std(j),
2992 decode(l_county(j), null, COUNTY, P_DML_RECORD.GMISS_CHAR, null, l_county(j))),
2993 city = decode(l_accept_std_flag(j), 'Y', l_city_std(j),
2994 decode(l_city(j), null, CITY, P_DML_RECORD.GMISS_CHAR, null, l_city(j))),
2995 postal_code = decode(l_accept_std_flag(j), 'Y', l_postal_code_std(j),
2996 decode(l_postal_code(j), null, POSTAL_CODE, P_DML_RECORD.GMISS_CHAR, null, l_postal_code(j))),
2997 state = decode(l_accept_std_flag(j), 'Y',
2998 decode(l_province(j), null, l_ps_admin_std(j), null),
2999 decode(l_state(j), null, STATE, P_DML_RECORD.GMISS_CHAR, null, l_state(j))),
3000 province = decode(l_accept_std_flag(j), 'Y',
3001 decode(l_province(j), null, null, l_ps_admin_std(j)),
3002 decode(l_province(j), null, PROVINCE, P_DML_RECORD.GMISS_CHAR, null, l_province(j))),
3003 object_version_number = object_version_number + 1,
3004 last_update_date = P_DML_RECORD.SYSDATE,
3005 last_updated_by = P_DML_RECORD.USER_ID,
3006 last_update_login = P_DML_RECORD.LAST_UPDATE_LOGIN,
3007 program_update_date = P_DML_RECORD.SYSDATE
3008 where party_id = l_party_id(j)
3009 and l_ident_addr_flag(j) = 'Y'
3010 and ( l_move_count(j) = 1 or
3011 (l_corr_count(j) = 1
3012 and (l_allow_std_update = 'Y' or l_old_valid_status_code(j) is null)));
3013
3014 -- END IF;
3015
3016 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
3017 hz_utility_v2pub.debug(p_message=>'process_update_addresses()-',
3018 p_prefix=>l_debug_prefix,
3019 p_msg_level=>fnd_log.level_procedure);
3020 END IF;
3021
3022 EXCEPTION
3023 WHEN OTHERS THEN
3024 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Update addresses other exception: ' || SQLERRM);
3025
3026 ROLLBACK to process_update_addresses_pvt;
3027 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3028
3029 FND_MESSAGE.SET_NAME('AR', 'HZ_API_OTHERS_EXCEP');
3030 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
3031 FND_MSG_PUB.ADD;
3032
3033 end process_update_addresses;
3034
3035
3036 PROCEDURE report_errors(
3037 P_DML_RECORD IN HZ_IMP_LOAD_WRAPPER.DML_RECORD_TYPE,
3038 P_DML_EXCEPTION IN VARCHAR2
3039 ) IS
3040 num_exp NUMBER;
3041 exp_ind NUMBER := 1;
3042 l_debug_prefix VARCHAR2(30) := '';
3043 begin
3044 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
3045 hz_utility_v2pub.debug(p_message=>'report_errors()+',
3046 p_prefix=>l_debug_prefix,
3047 p_msg_level=>fnd_log.level_procedure);
3048 END IF;
3049
3050 /**********************************/
3051 /* Validation and Error reporting */
3052 /**********************************/
3053 IF l_site_id.count = 0 THEN
3054 return;
3055 END IF;
3056
3057 l_num_row_processed := null;
3058 l_num_row_processed := NUMBER_COLUMN();
3059 l_num_row_processed.extend(l_site_id.count);
3060 l_exception_exists := null;
3061 l_exception_exists := FLAG_ERROR();
3062 l_exception_exists.extend(l_site_id.count);
3063 num_exp := SQL%BULK_EXCEPTIONS.COUNT;
3064
3065 FOR k IN 1..l_site_id.count LOOP
3066
3067 IF (l_corr_upd_count(k) <> 0)or
3068 (l_move_count(k) <> 0) then
3069 l_num_row_processed(k) := 1;
3070 else
3071 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
3072 hz_utility_v2pub.debug(p_message=>'DML fails at ' || k,
3073 p_prefix=>'ERROR',
3074 p_msg_level=>fnd_log.level_error);
3075 END IF;
3076 l_num_row_processed(k) := 0;
3077
3078 /* Check for any exceptions during DML */
3079 IF P_DML_EXCEPTION = 'Y' THEN
3080 /* determine if exception at this index */
3081 FOR i IN exp_ind..num_exp LOOP
3082 IF SQL%BULK_EXCEPTIONS(i).ERROR_INDEX = k THEN
3083 l_exception_exists(k) := 'Y';
3084 ELSIF SQL%BULK_EXCEPTIONS(i).ERROR_INDEX > k THEN
3085 EXIT;
3086 END IF;
3087 END LOOP;
3088 END IF;
3089 END IF;
3090 END LOOP;
3091 /* insert into tmp error tables */
3092
3093 forall j in 1..l_site_id.count
3094 insert into hz_imp_tmp_errors
3095 ( request_id,
3096 batch_id,
3097 int_row_id,
3098 interface_table_name,
3099 error_id,
3100 creation_date,
3101 created_by,
3102 last_update_date,
3103 last_updated_by,
3104 last_update_login,
3105 program_application_id,
3106 program_id,
3107 program_update_date,
3108 ACTION_MISMATCH_FLAG,
3109 e1_flag,e2_flag,e3_flag,e4_flag,e5_flag,e6_flag,e7_flag,e8_flag,
3110 e9_flag,/* Bug 4079902 */
3111 e10_flag,
3112 e11_flag,
3113 OTHER_EXCEP_FLAG,
3114 MISSING_PARENT_FLAG
3115 )
3116 (
3117 select P_DML_RECORD.REQUEST_ID,
3118 P_DML_RECORD.BATCH_ID,
3119 l_row_id(j),
3120 'HZ_IMP_ADDRESSES_INT',
3121 hz_imp_errors_s.NextVal,
3122 P_DML_RECORD.SYSDATE,
3123 P_DML_RECORD.USER_ID,
3124 P_DML_RECORD.SYSDATE,
3125 P_DML_RECORD.USER_ID,
3126 P_DML_RECORD.LAST_UPDATE_LOGIN,
3127 P_DML_RECORD.PROGRAM_APPLICATION_ID,
3128 P_DML_RECORD.PROGRAM_ID,
3129 P_DML_RECORD.SYSDATE,
3130 v.ACTION_MISMATCH_FLAG,
3131 v.E1_FLAG,
3132 v.E2_FLAG,
3133 v.E3_FLAG,
3134 v.E4_FLAG,
3135 v.E5_FLAG,
3136 v.E6_FLAG,
3137 v.E7_FLAG,
3138 v.E8_FLAG,
3139 v.E9_FLAG,
3140 v.E10_FLAG,
3141 v.E11_FLAG,
3142 v.OTHER_EXCEP_FLAG,
3143 v.MISSING_PARENT_FLAG
3144 FROM (
3145 select
3146 l_action_error_flag(j) ACTION_MISMATCH_FLAG,
3147 nvl2(l_error_flag(j), DECODE(l_error_flag(j),3,'Y', null), 'Y') E1_FLAG, -- e1
3148 l_address_err(j) E2_FLAG, -- e2
3149 nvl2(l_country_err(j), 'Y', null) E3_FLAG, -- e3
3150 nvl2(l_lang_err(j), 'Y', null) E4_FLAG, -- e4
3151 nvl2(l_timezone_err(j), 'Y', null) E5_FLAG, -- e5
3152 nvl2(l_flex_val_errors(j),'Y', null) E6_FLAG, -- e6
3153 decode(l_corr_mv_ind(j), 'C', -- corection only error (e7),
3154 decode(l_addr_ch_flag(j), 'Y', -- error if correction not allowed
3155 decode(l_allow_correction, 'N', null, 'Y'), 'Y'), 'Y') E7_FLAG,
3156 nvl2(l_error_flag(j), DECODE(l_error_flag(j),2,'Y', null), 'Y') E10_FLAG, -- e10
3157 l_exception_exists(j) OTHER_EXCEP_FLAG,
3158 nvl2(l_owner_table_id(j),'Y',null) MISSING_PARENT_FLAG,
3159 decode(l_dss_security_errors(j), FND_API.G_TRUE,'Y',null) E8_FLAG, -- e8
3160 l_third_party_update_error(j) E9_FLAG, --e9
3161 l_createdby_errors(j) E11_FLAG
3162 from dual
3163 where l_num_row_processed(j) = 0
3164 ) v
3165 WHERE v.ACTION_MISMATCH_FLAG is null
3166 OR v.E1_FLAG is null
3167 OR v.E2_FLAG is null
3168 OR v.E3_FLAG is null
3169 OR v.E4_FLAG is null
3170 OR v.E5_FLAG is null
3171 OR v.E6_FLAG is null
3172 OR v.E7_FLAG is null
3173 OR v.E8_FLAG is null
3174 OR v.E9_FLAG is null
3175 OR v.E10_FLAG is null
3176 OR v.E11_FLAG is null
3177 OR v.MISSING_PARENT_FLAG is null
3178 OR v.OTHER_EXCEP_FLAG is not null
3179 );
3180
3181
3182 /* Update errored records in interface table */
3183 -- this update should be performed along with final error table population
3184
3185 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
3186 hz_utility_v2pub.debug(p_message=>'report_errors()-',
3187 p_prefix=>l_debug_prefix,
3188 p_msg_level=>fnd_log.level_procedure);
3189 END IF;
3190
3191 END report_errors;
3192
3193 --------------------------------------
3194 -- private procedures and functions
3195 --------------------------------------
3196 --------------------------------------
3197 /*PROCEDURE enable_debug IS
3198 BEGIN
3199 g_debug_count := g_debug_count + 1;
3200
3201 IF g_debug_count = 1 THEN
3202 IF fnd_profile.value('HZ_API_FILE_DEBUG_ON') = 'Y' OR
3203 fnd_profile.value('HZ_API_DBMS_DEBUG_ON') = 'Y'
3204 THEN
3205 hz_utility_v2pub.enable_debug;
3206 g_debug := TRUE;
3207 END IF;
3208 END IF;
3209 END enable_debug; -- end procedure
3210 */
3211 --------------------------------------
3212 --------------------------------------
3213 /*PROCEDURE disable_debug IS
3214 BEGIN
3215
3216 IF g_debug THEN
3217 g_debug_count := g_debug_count - 1;
3218 IF g_debug_count = 0 THEN
3219 hz_utility_v2pub.disable_debug;
3220 g_debug := FALSE;
3221 END IF;
3222 END IF;
3223
3224 END disable_debug;
3225 */
3226
3227 PROCEDURE sync_party_tax_profile
3228 ( P_BATCH_ID IN NUMBER,
3229 P_REQUEST_ID IN NUMBER,
3230 P_ORIG_SYSTEM IN VARCHAR2,
3231 P_FROM_OSR IN VARCHAR2,
3232 P_TO_OSR IN VARCHAR2,
3233 P_BATCH_MODE_FLAG IN VARCHAR2,
3234 P_PROGRAM_ID IN NUMBER
3235 )
3236 IS
3237
3238 BEGIN
3239
3240 -- Import Party Sites
3241 MERGE INTO ZX_PARTY_TAX_PROFILE PTP
3242 USING
3243 (SELECT 'THIRD_PARTY_SITE' PARTY_TYPE_CODE,
3244 ps.party_site_id PARTY_ID,
3245 loc.country COUNTRY_CODE,--4742586
3246 FND_GLOBAL.Login_ID PROGRAM_LOGIN_ID ,
3247 NULL TAX_REFERENCE,
3248 SYSDATE CREATION_DATE,
3249 FND_GLOBAL.User_ID CREATED_BY,
3250 SYSDATE LAST_UPDATE_DATE,
3251 FND_GLOBAL.User_ID LAST_UPDATED_BY,
3252 FND_GLOBAL.Login_ID LAST_UPDATE_LOGIN
3253 FROM HZ_PARTY_SITES ps, HZ_IMP_ADDRESSES_SG pssg,HZ_LOCATIONS loc, --4742586
3254 HZ_IMP_ADDRESSES_INT psint
3255 WHERE loc.request_id = p_request_id --Bug No.4956874.SQLID:14455142
3256 AND ps.party_site_id = pssg.party_site_id -- Bug 5210879.
3257 AND loc.location_id = ps.location_id --4742586
3258 AND pssg.batch_mode_flag = p_batch_mode_flag
3259 AND pssg.batch_id = p_batch_id
3260 AND pssg.party_orig_system = p_orig_system
3261 AND pssg.party_orig_system_reference BETWEEN p_from_osr AND p_to_osr
3262 AND psint.rowid=pssg.int_row_id
3263 AND (psint.interface_status is NULL or psint.interface_status='C')
3264 ) PTY
3265 ON (PTY.PARTY_ID = PTP.PARTY_ID AND PTP.PARTY_TYPE_CODE = 'THIRD_PARTY_SITE')
3266 WHEN MATCHED THEN
3267 UPDATE SET
3268 PTP.LAST_UPDATE_DATE=PTY.LAST_UPDATE_DATE,
3269 PTP.LAST_UPDATED_BY=PTY.LAST_UPDATED_BY,
3270 PTP.LAST_UPDATE_LOGIN=PTY.LAST_UPDATE_LOGIN,
3271 PTP.OBJECT_VERSION_NUMBER = PTP.OBJECT_VERSION_NUMBER +1,
3272 PTP.PROGRAM_ID = P_PROGRAM_ID,
3273 PTP.REQUEST_ID = P_REQUEST_ID
3274 WHEN NOT MATCHED THEN
3275 INSERT (
3276 PARTY_TYPE_CODE,
3277 PARTY_TAX_PROFILE_ID,
3278 PARTY_ID,
3279 PROGRAM_LOGIN_ID,
3280 REP_REGISTRATION_NUMBER,
3281 CREATION_DATE,
3282 CREATED_BY,
3283 LAST_UPDATE_DATE,
3284 LAST_UPDATED_BY,
3285 LAST_UPDATE_LOGIN,
3286 OBJECT_VERSION_NUMBER,
3287 COUNTRY_CODE,
3288 REQUEST_ID,
3289 PROGRAM_ID) --4742586
3290 VALUES (
3291 PTY.PARTY_TYPE_CODE,
3292 ZX_PARTY_TAX_PROFILE_S.NEXTVAL,
3293 PTY.PARTY_ID,
3294 PTY.PROGRAM_LOGIN_ID,
3295 PTY.TAX_REFERENCE,
3296 PTY.CREATION_DATE,
3297 PTY.CREATED_BY,
3298 PTY.LAST_UPDATE_DATE,
3299 PTY.LAST_UPDATED_BY,
3300 PTY.LAST_UPDATE_LOGIN,
3301 1,
3302 PTY.COUNTRY_CODE,
3303 P_REQUEST_ID,
3304 P_PROGRAM_ID);
3305
3306 END sync_party_tax_profile;
3307
3308 END HZ_IMP_LOAD_ADDRESSES_PKG;