[Home] [Help]
PACKAGE BODY: APPS.HZ_IMP_LOAD_WRAPPER
Source
1 PACKAGE BODY HZ_IMP_LOAD_WRAPPER AS
2 /*$Header: ARHLWRPB.pls 120.81 2008/10/27 09:41:48 idali ship $*/
3
4 WORK_UNIT_CAP_SIZE NUMBER := 200000;
5
6 -- Bug 5264069
7 TYPE cleanup_ssm_pid_csr_type IS REF CURSOR;
8 TYPE ROWID IS TABLE OF VARCHAR2(50);
9 TYPE OWNER_TABLE_ID IS TABLE OF HZ_ORIG_SYS_REFERENCES.OWNER_TABLE_ID%TYPE;
10
11 TYPE T_ORIG_SYS_REF_ID IS TABLE OF HZ_ORIG_SYS_REFERENCES.ORIG_SYSTEM_REF_ID%TYPE;
12 TYPE T_PARTY_SITE_ID IS TABLE OF HZ_PARTY_SITES.PARTY_SITE_ID%TYPE;
13
14 l_row_id ROWID;
15 l_row_id_new ROWID;
16 l_row_id_old ROWID;
17 l_party_owner_table_id OWNER_TABLE_ID;
18 l_orig_sys_ref_id T_ORIG_SYS_REF_ID;
19 l_primary_flag T_ACTION_FLAG;
20 l_old_psid T_PARTY_SITE_ID;
21 l_new_psid T_PARTY_SITE_ID;
22
23 l_rows NUMBER := 1000;
24 l_last_fetch BOOLEAN;
25
26 PROCEDURE CHECK_INVALID_PARTY(
27 P_BATCH_ID IN NUMBER,
28 P_REQUEST_ID IN NUMBER,
29 P_USER_ID IN NUMBER,
30 P_LAST_UPDATE_LOGIN IN NUMBER,
31 P_PROGRAM_ID IN NUMBER,
32 P_PROGRAM_APPLICATION_ID IN NUMBER,
33 X_RETURN_STATUS OUT NOCOPY VARCHAR2);
34
35 /* bug fix 3849232 */
36 PROCEDURE add_policy
37 IS
38
39 l_ar_schema VARCHAR2(30);
40 l_apps_schema VARCHAR2(30);
41 l_aol_schema VARCHAR2(30);
42 l_apps_mls_schema VARCHAR2(30);
43
44 l_status VARCHAR2(30);
45 l_industry VARCHAR2(30);
46 l_return_value BOOLEAN;
47
48 -- Bug 4079902.
49 l_result BOOLEAN;
50
51 BEGIN
52
53 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
54 hz_utility_v2pub.debug(p_message=>'WRP:add_policy()+',
55 p_prefix=>'',
56 p_msg_level=>fnd_log.level_procedure);
57 END IF;
58
59 --Get ar and apps schema name
60 l_return_value := fnd_installation.get_app_info(
61 'AR', l_status, l_industry, l_ar_schema);
62
63 IF NOT l_return_value THEN
64 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
65 END IF;
66
67 l_return_value := fnd_installation.get_app_info(
68 'FND', l_status, l_industry, l_aol_schema);
69
70 IF NOT l_return_value THEN
71 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
72 END IF;
73
74 system.ad_apps_private.get_apps_schema_name(
75 1, l_aol_schema, l_apps_schema, l_apps_mls_schema);
76
77 --Add policy functions
78 /*
79 FND_ACCESS_CONTROL_UTIL.ADD_POLICY(l_ar_schema, 'HZ_ORGANIZATION_PROFILES', 'content_source_type_sec', l_apps_schema, 'hz_common_pub.content_source_type_security');
80 FND_ACCESS_CONTROL_UTIL.ADD_POLICY(l_ar_schema, 'HZ_PERSON_PROFILES', 'content_source_type_sec', l_apps_schema, 'hz_common_pub.content_source_type_security');
81 */
82
83 -- Bug 4079902
84 DBMS_RLS.ADD_POLICY(l_ar_schema, 'HZ_ORGANIZATION_PROFILES', 'content_source_type_sec', l_apps_schema, 'hz_common_pub.content_source_type_security');
85 DBMS_RLS.ADD_POLICY(l_ar_schema, 'HZ_PERSON_PROFILES', 'content_source_type_sec', l_apps_schema, 'hz_common_pub.content_source_type_security');
86
87
88 /* Commented code for bug 4079902 */
89
90 /*
91 FND_ACCESS_CONTROL_UTIL.ADD_POLICY(l_ar_schema, 'HZ_RELATIONSHIPS', 'content_source_type_sec', l_apps_schema, 'hz_common_pub.content_source_type_security');
92 FND_ACCESS_CONTROL_UTIL.ADD_POLICY(l_ar_schema, 'HZ_LOCATIONS', 'content_source_type_sec', l_apps_schema, 'hz_common_pub.content_source_type_security');
93 FND_ACCESS_CONTROL_UTIL.ADD_POLICY(l_ar_schema, 'HZ_CONTACT_POINTS', 'content_source_type_sec', l_apps_schema, 'hz_common_pub.content_source_type_security');
94 FND_ACCESS_CONTROL_UTIL.ADD_POLICY(l_ar_schema, 'HZ_CREDIT_RATINGS', 'content_source_type_sec', l_apps_schema, 'hz_common_pub.content_source_type_security');
95 FND_ACCESS_CONTROL_UTIL.ADD_POLICY(l_ar_schema, 'HZ_FINANCIAL_REPORTS', 'content_source_type_sec', l_apps_schema, 'hz_common_pub.content_source_type_security');
96 FND_ACCESS_CONTROL_UTIL.ADD_POLICY(l_ar_schema, 'HZ_FINANCIAL_NUMBERS', 'content_source_type_sec', l_apps_schema, 'hz_common_pub.content_source_type_security');
97 FND_ACCESS_CONTROL_UTIL.ADD_POLICY(l_ar_schema, 'HZ_CODE_ASSIGNMENTS', 'content_source_type_sec', l_apps_schema, 'hz_common_pub.content_source_type_security');
98 FND_ACCESS_CONTROL_UTIL.ADD_POLICY(l_ar_schema, 'HZ_ORGANIZATION_INDICATORS', 'content_source_type_sec', l_apps_schema, 'hz_common_pub.content_source_type_security');
99 FND_ACCESS_CONTROL_UTIL.ADD_POLICY(l_ar_schema, 'HZ_PARTY_SITES', 'content_source_type_sec', l_apps_schema, 'hz_common_pub.content_source_type_security');
100 */
101
102
103 -- Code added for Bug 4079902.
104
105 l_result := FND_PROFILE.SAVE('HZ_DNB_POLICY_EXIST','Y','SITE');
106
107 IF NOT l_result THEN
108 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
109 END IF;
110
111
112 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
113 hz_utility_v2pub.debug(p_message=>'WRP:add_policy()-',
114 p_prefix=>'',
115 p_msg_level=>fnd_log.level_procedure);
116 END IF;
117
118 END add_policy;
119
120
121
122 /********************************************************************************
123 *
124 * ident_dup_within_int:
125 * 1. Identify dup relationships within relationship interface and contacts
126 * interface tables
127 * 2. Identify dup address uses with addr uses interface table
128 * 3. Identify dup code assignments
129 *
130 ********************************************************************************/
131
132 PROCEDURE ident_dup_within_int (
133 P_BATCH_ID IN NUMBER
134 ,P_OS IN VARCHAR2
135 ,P_BATCH_MODE_FLAG IN VARCHAR2
136 ,P_REQUEST_ID IN NUMBER
137 ,P_SYSDATE IN DATE
138 ,P_USER_ID IN NUMBER
139 ,P_LAST_UPDATE_LOGIN IN NUMBER
140 ,P_PROGRAM_APP_ID IN NUMBER
141 ,P_PROGRAM_ID IN NUMBER
142 ) IS
143
144 l_int_row_id T_ROWID;
145 l_err T_ERROR;
146 l_err_id T_ERROR_ID;
147 l_table_name T_TABLE_NAME;
148 --l_debug_prefix VARCHAR2(30):= '';
149
150 CURSOR dup_rel (p_batch_id NUMBER, p_batch_mode_flag VARCHAR2) IS
151 select int_row_id, table_name, hz_imp_errors_s.nextval
152 from (
153 select /*+ ordered parallel(r) use_hash(r) */
154 table_name, int_row_id, lead(sd,1) over (partition by
155 decode(t.direction_code, 'C', obj_id, sub_id),
156 decode(t.direction_code, 'C', sub_id, obj_id),
157 decode(t.direction_code, 'C', t.backward_rel_code, rc),
158 t.subject_type, t.object_type /* Fix 3931139 */
159 order by sd, ed) lsd, sub_id, obj_id, rc, sd, ed,
160 t.subject_type, t.object_type
161 from hz_relationship_types t,
162 (
163 select /*+ parallel(s) */ sub_id, obj_id, int_row_id,
164 nvl(start_date, sysdate) sd, nvl(end_date,
165 to_date('31-12-4712 00:00:01', 'DD-MM-YYYY HH24:MI:SS'))
166 ed, relationship_code rc, relationship_type rt,
167 'HZ_IMP_RELSHIPS_INT' table_name
168 from hz_imp_relships_sg s
169 where batch_id = p_batch_id
170 and batch_mode_flag = p_batch_mode_flag
171 and action_flag = 'I'
172 union all
173 select /*+ parallel(s) */ sub_id, obj_id, int_row_id,
174 nvl(start_date, sysdate) sd, nvl(end_date,
175 to_date('31-12-4712 00:00:01', 'DD-MM-YYYY HH24:MI:SS'))
176 ed, relationship_code rc, relationship_type rt,
177 'HZ_IMP_CONTACTS_INT' table_name
178 from hz_imp_contacts_sg s
179 where batch_id = p_batch_id
180 and batch_mode_flag = p_batch_mode_flag
181 and action_flag = 'I'
182 ) r
183 where t.forward_rel_code = r.rc
184 and t.relationship_type = r.rt)
185 where lsd < ed
186 and exists (
187 select /*+ index(p, HZ_PARTIES_U1) */ 1
188 from hz_parties p
189 where subject_type = p.party_type
190 and sub_id = p.party_id)
191 and exists (
192 select /*+ index(p, HZ_PARTIES_U1) */ 1
193 from hz_parties p
194 where object_type = p.party_type
195 and obj_id = p.party_id);
196
197 /* Combination of party_site_id and site_use_type is unique.
198 Order by 's.primary_flag desc' such that primary address uses will not
199 be marked as duplicate and leave a non-primary one there */
200 CURSOR dup_addr_uses (p_batch_id NUMBER, p_batch_mode_flag VARCHAR2) IS
201 select int_row_id, hz_imp_errors_s.NextVal from (
202 select /*+ parallel(s) */ party_site_use_id suid, party_site_id sid,
203 site_use_type sut, int_row_id, lead(party_site_id, 1) over(
204 partition by site_use_type order by primary_flag desc) n_sid
205 from hz_imp_addressuses_sg s
206 where s.batch_id = p_batch_id
207 and s.batch_mode_flag = p_batch_mode_flag
208 and s.action_flag = 'I'
209 ) r
210 where sid = n_sid;
211
212
213 /* There should not be overlapping assignment for the same
214 party and same classification (class category / class code)
215 within the same content source type */
216 CURSOR dup_code_assignments (p_batch_id NUMBER, p_batch_mode_flag VARCHAR2) IS
217 select int_row_id, decode(allow_multi_assign_flag, 'N', 'M', 'E'), hz_imp_errors_s.NextVal from (
218 select /*+ parallel(s) */
219 s.int_row_id, s.party_id, s.party_orig_system_reference,
220 s.primary_flag, s.start_date_active, s.end_date_active,
221 lead(s.start_date_active, 1) over(partition by s.party_id,
222 s.class_category, s.class_code order by s.start_date_active,
223 s.end_date_active nulls last, s.primary_flag desc) lsd1,
224 lead(s.start_date_active, 1) over(partition by s.party_id,
225 s.class_category order by s.start_date_active,
226 s.end_date_active nulls last, s.primary_flag desc) lsd2,
227 c_cat.allow_multi_assign_flag
228 from hz_imp_classifics_sg s,
229 hz_class_categories c_cat,
230 hz_imp_classifics_int c_int
231 where s.batch_id = p_batch_id
232 and s.batch_mode_flag = p_batch_mode_flag
233 and s.action_flag = 'I'
234 and c_cat.class_category = s.class_category
235 and ( c_int.INTERFACE_STATUS is null OR
236 (c_int.INTERFACE_STATUS is not null AND c_int.INTERFACE_STATUS <> 'D')
237 )
238 and c_int.rowid = s.int_row_id )
239 where decode(allow_multi_assign_flag, 'N', lsd2, lsd1) < nvl(end_date_active,
240 to_date('31-12-4712 00:00:01', 'DD-MM-YYYY HH24:MI:SS'));
241
242
243
244 BEGIN
245
246 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
247 hz_utility_v2pub.debug(p_message=>'WRP:ident_dup_within_int()+',
248 p_prefix=>'',
249 p_msg_level=>fnd_log.level_procedure);
250 END IF;
251
252 /* Check dup relationships */
253 OPEN dup_rel(P_BATCH_ID, P_BATCH_MODE_FLAG);
254 FETCH dup_rel BULK COLLECT INTO l_int_row_id, l_table_name, l_err_id;
255
256 forall j in 1..l_table_name.count
257 insert into hz_imp_tmp_errors
258 (
259 request_id,
260 batch_id,
261 int_row_id,
262 interface_table_name,
263 error_id,
264 creation_date,
265 created_by,
266 last_update_date,
267 last_updated_by,
268 last_update_login,
269 program_application_id,
270 program_id,
271 program_update_date,
272 e1_flag,e2_flag,e3_flag,e4_flag,e5_flag,
273 e6_flag,e7_flag,e8_flag,e9_flag,e10_flag,
274 e11_flag,e12_flag,e13_flag,e14_flag,e15_flag,e16_flag,
275 ACTION_MISMATCH_FLAG,
276 e38_flag -- HZ_IMP_DUP_REL_IN_INT_ERROR
277 )
278 values
279 (
280 P_REQUEST_ID,
281 P_BATCH_ID,
282 l_int_row_id(j),
283 l_table_name(j),
284 l_err_id(j),
285 P_SYSDATE,
286 P_USER_ID,
287 P_SYSDATE,
288 P_USER_ID,
289 P_LAST_UPDATE_LOGIN,
290 P_PROGRAM_APP_ID,
291 P_PROGRAM_ID,
292 P_SYSDATE,
293 'Y','Y','Y','Y','Y',
294 'Y','Y','Y','Y','Y',
295 'Y','Y','Y','Y','Y','Y',
296 'Y',
297 'E'
298 );
299
300
301 /* Dup rel may come from HZ_IMP_RELSHIPS_INT or HZ_IMP_CONTACTS_INT.
302 So update both tables. Update interface_status to 'E' so that
303 these records will not be picked up during V+DML. Error_id column
304 in interface table will be updated later when records are
305 copied from tmp error table to error table. */
306 ForAll j in 1..l_int_row_id.count
307 update HZ_IMP_RELSHIPS_INT
308 set interface_status = 'E', error_id = l_err_id(j)
309 where rowid = l_int_row_id(j)
310 and l_table_name(j) = 'HZ_IMP_RELSHIPS_INT';
311
312 ForAll j in 1..l_int_row_id.count
313 update HZ_IMP_CONTACTS_INT
314 set interface_status = 'E', error_id = l_err_id(j)
315 where rowid = l_int_row_id(j)
316 and l_table_name(j) = 'HZ_IMP_CONTACTS_INT';
317
318 CLOSE dup_rel;
319
320
321 /* Check dup address uses */
322 OPEN dup_addr_uses(P_BATCH_ID, P_BATCH_MODE_FLAG);
323 FETCH dup_addr_uses BULK COLLECT INTO l_int_row_id, l_err_id;
324
325 ForAll j in 1..l_int_row_id.count
326 insert into hz_imp_tmp_errors
327 (
328 request_id,
329 batch_id,
330 int_row_id,
331 interface_table_name,
332 error_id,
333 creation_date,
334 created_by,
335 last_update_date,
336 last_updated_by,
337 last_update_login,
338 program_application_id,
339 program_id,
340 program_update_date,
341 e1_flag,e2_flag,e3_flag,ACTION_MISMATCH_FLAG,MISSING_PARENT_FLAG,
342 e38_flag -- HZ_IMP_DUP_ADDRUSE_IN_INT
343 )
344 values
345 (
346 P_REQUEST_ID,
347 P_BATCH_ID,
348 l_int_row_id(j),
349 'HZ_IMP_ADDRESSUSES_INT',
350 l_err_id(j),
351 P_SYSDATE,
352 P_USER_ID,
353 P_SYSDATE,
354 P_USER_ID,
355 P_LAST_UPDATE_LOGIN,
356 P_PROGRAM_APP_ID,
357 P_PROGRAM_ID,
358 P_SYSDATE,
359 'Y','Y','Y','Y','Y',
360 'E'
361 );
362
363 ForAll j in 1..l_int_row_id.count
364 update HZ_IMP_ADDRESSUSES_INT
365 set interface_status = 'E', error_id = l_err_id(j)
366 where rowid = l_int_row_id(j);
367
368 CLOSE dup_addr_uses;
369
370 /* Check dup code assignments */
371 OPEN dup_code_assignments(P_BATCH_ID, P_BATCH_MODE_FLAG);
372 FETCH dup_code_assignments BULK COLLECT INTO l_int_row_id, l_err, l_err_id;
373
374 ForAll j in 1..l_int_row_id.count
375 insert into hz_imp_tmp_errors
376 (
377 request_id,
378 batch_id,
379 int_row_id,
380 interface_table_name,
381 error_id,
382 creation_date,
383 created_by,
384 last_update_date,
385 last_updated_by,
386 last_update_login,
387 program_application_id,
388 program_id,
389 program_update_date,
390 e2_flag,e3_flag,e4_flag,e5_flag,
391 e6_flag,e7_flag,e8_flag,e9_flag,ACTION_MISMATCH_FLAG,
392 e38_flag -- HZ_IMP_DUP_CLASSIFIC_IN_INT
393 )
394 values
395 (
396 P_REQUEST_ID,
397 P_BATCH_ID,
398 l_int_row_id(j),
399 'HZ_IMP_CLASSIFICS_INT',
400 l_err_id(j),
401 P_SYSDATE,
402 P_USER_ID,
403 P_SYSDATE,
404 P_USER_ID,
405 P_LAST_UPDATE_LOGIN,
406 P_PROGRAM_APP_ID,
407 P_PROGRAM_ID,
408 P_SYSDATE,
409 'Y','Y','Y','Y',
410 'Y','Y','Y','Y','Y',
411 l_err(j)
412 );
413
414 ForAll j in 1..l_int_row_id.count
415 update HZ_IMP_CLASSIFICS_INT
416 set interface_status = 'E', error_id = l_err_id(j)
417 where rowid = l_int_row_id(j);
418
419 CLOSE dup_code_assignments;
420
421 -- Bug 4398179 end dating DNB old relationships
422 IF P_OS = 'DNB' THEN
423 insert into hz_imp_tmp_rel_end_date(batch_id, sub_orig_system_reference, relationship_id, directional_flag, int_row_id,
424 creation_date, created_by, last_update_date, last_updated_by)
425 SELECT /*+ parallel(rs) parallel(r) full(rs) leading(rs) use_nl(r) */
426 P_BATCH_ID, rs.sub_orig_system_reference, r.relationship_id,
427 r.directional_flag, rs.int_row_id int_row_id,
428 P_SYSDATE, P_USER_ID, P_SYSDATE, P_USER_ID
429 FROM hz_imp_relships_sg rs,
430 hz_relationships r
431 WHERE rs.batch_id=P_BATCH_ID
432 AND rs.sub_orig_system = P_OS
433 AND rs.batch_mode_flag = P_BATCH_MODE_FLAG
434 AND rs.action_flag = 'I'
435 AND rs.relationship_type in ('HEADQUARTERS/DIVISION', 'PARENT/SUBSIDIARY','DOMESTIC_ULTIMATE','GLOBAL_ULTIMATE')
436 AND r.relationship_type in ('HEADQUARTERS/DIVISION', 'PARENT/SUBSIDIARY','DOMESTIC_ULTIMATE','GLOBAL_ULTIMATE')
437 AND rs.obj_id=r.object_id
438 AND decode(r.relationship_type,'PARENT/SUBSIDIARY','HEADQUARTERS/DIVISION',r.relationship_type)
439 =decode(rs.relationship_type,'PARENT/SUBSIDIARY','HEADQUARTERS/DIVISION',rs.relationship_type)
440 AND decode(r.relationship_code,'PARENT_OF','HEADQUARTERS_OF',r.relationship_code)
441 =decode(rs.relationship_code,'PARENT_OF','HEADQUARTERS_OF',rs.relationship_code)
442 AND r.subject_table_name='HZ_PARTIES'
443 AND r.object_table_name='HZ_PARTIES'
444 AND r.object_type='ORGANIZATION'
445 AND r.status='A'
446 AND P_SYSDATE > r.start_date
447 AND P_SYSDATE <= nvl(r.end_date, TO_DATE('31-12-4712 00:00:01', 'DD-MM-YYYY HH24:MI:SS'))
448 AND r.actual_content_source='DNB';
449
450 insert into hz_imp_tmp_rel_end_date(batch_id, sub_orig_system_reference, relationship_id, directional_flag, int_row_id,
451 creation_date, created_by, last_update_date, last_updated_by)
452 SELECT batch_id, sub_orig_system_reference, relationship_id, decode(directional_flag,'B','F','F','B'), int_row_id,
453 creation_date, created_by, last_update_date, last_updated_by
454 FROM hz_imp_tmp_rel_end_date WHERE batch_id=P_BATCH_ID;
455 END IF;
456
457 /* Records in hz_tmp_errors for global validations will be ignored when
458 copy from tmp error to permanent error table. Insert into tmp errors table
459 for keeping error count. */
460 insert into hz_imp_errors (
461 error_id, batch_id, request_id, interface_table_name, message_name
462 )
463 select error_id, batch_id, request_id, interface_table_name,
464 decode(interface_table_name, 'HZ_IMP_RELSHIPS_INT', 'HZ_IMP_DUP_REL_IN_INT_ERROR',
465 'HZ_IMP_CONTACTS_INT', 'HZ_IMP_DUP_REL_IN_INT_ERROR',
466 'HZ_IMP_ADDRESSUSES_INT', 'HZ_IMP_DUP_ADDRUSE_IN_INT',
467 'HZ_IMP_CLASSIFICS_INT', decode(e38_flag, 'M', 'HZ_API_ALLOW_MUL_ASSIGN_FG',
468 'HZ_IMP_DUP_CLASSIFIC_IN_INT')
469 )
470 from hz_imp_tmp_errors
471 where batch_id = P_BATCH_ID
472 and request_id = P_REQUEST_ID
473 and interface_table_name in ('HZ_IMP_RELSHIPS_INT', 'HZ_IMP_CONTACTS_INT',
474 'HZ_IMP_ADDRESSUSES_INT', 'HZ_IMP_CLASSIFICS_INT');
475
476 COMMIT;
477 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
478 hz_utility_v2pub.debug(p_message=>'WRP:ident_dup_within_int()-',
479 p_prefix=>'',
480 p_msg_level=>fnd_log.level_procedure);
481 END IF;
482
483 END ident_dup_within_int;
484
485
486 PROCEDURE update_import_status(
487 P_BATCH_ID IN NUMBER,
488 P_STATUS IN VARCHAR2
489 ) IS
490 l_detail_import_status VARCHAR2(30);
491 l_batch_import_status VARCHAR2(30);
492 BEGIN
493
494
495 IF P_STATUS = 'COMPL_ERRORS2' THEN
496 l_detail_import_status := 'COMPLETED';
497 l_batch_import_status := 'COMPL_ERRORS';
498 ELSE
499 l_detail_import_status := P_STATUS;
500 l_batch_import_status := P_STATUS;
501 END IF;
502
503 update hz_imp_batch_summary
504 set import_status = l_batch_import_status,
505 import_req_id = hz_utility_v2pub.request_id
506 where batch_id = P_BATCH_ID;
507
508 update hz_imp_batch_details
509 set import_status = l_detail_import_status,
510 import_req_id = hz_utility_v2pub.request_id
511 where batch_id = P_BATCH_ID
512 and run_number = (select max(run_number)
513 from hz_imp_batch_details
514 where batch_id = P_BATCH_ID);
515
516 COMMIT;
517
518 END update_import_status;
519
520
521 PROCEDURE ALTER_SEQUENCES(
522 P_OPERATION IN VARCHAR2, /* I - increase, R - restore */
523 P_BATCH_MODE_FLAG IN VARCHAR2
524 ) IS
525 l_running_dl_wrapper VARCHAR2(1);
526 CURSOR c_running_dl_wrapper IS
527 select 'Y' from fnd_conc_req_summary_v
528 where program_short_name like 'ARHLWRPB'
529 and phase_code <> 'C'
530 and rownum = 1;
531
532 l_bool BOOLEAN;
533 l_status VARCHAR2(255);
534 l_schema VARCHAR2(255);
535 l_tmp VARCHAR2(2000);
536 --l_debug_prefix VARCHAR2(30) := '';
537 BEGIN
538
539
540 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
541 hz_utility_v2pub.debug(p_message=>'WRP:ALTER_SEQUENCES()+',
542 p_prefix=>'',
543 p_msg_level=>fnd_log.level_procedure);
544 END IF;
545
546 l_bool := fnd_installation.GET_APP_INFO('AR',l_status,l_tmp,l_schema);
547
548 IF P_OPERATION = 'I' THEN
549 /* Increase increment for muti-table insert for parties */
550 execute immediate 'alter sequence ' || l_schema || '.hz_organization_profiles_s increment by 3';
551 execute immediate 'alter sequence ' || l_schema || '.hz_person_profiles_s increment by 3';
552 execute immediate 'alter sequence ' || l_schema || '.hz_location_profiles_s increment by 2';
553 execute immediate 'alter sequence ' || l_schema || '.hz_party_usg_assignments_s increment by 2';
554
555 /* Alter sequence only for batch mode */
556 /* Wallace: remove alter cache size to be compilant with standard.
557 alter cache size before run instead
558 IF P_BATCH_MODE_FLAG = 'Y' THEN
559 execute immediate 'alter sequence ' || l_schema || '.hz_code_assignments_s cache 20000';
560 execute immediate 'alter sequence ' || l_schema || '.hz_contact_numbers_s cache 20000';
561 execute immediate 'alter sequence ' || l_schema || '.hz_contact_points_s cache 20000';
562 execute immediate 'alter sequence ' || l_schema || '.hz_credit_ratings_s cache 20000';
563 execute immediate 'alter sequence ' || l_schema || '.hz_financial_numbers_s cache 20000';
564 execute immediate 'alter sequence ' || l_schema || '.hz_financial_reports_s cache 20000';
565 execute immediate 'alter sequence ' || l_schema || '.hz_imp_errors_s cache 20000';
566 execute immediate 'alter sequence hr.hr_locations_s cache 20000';
567 execute immediate 'alter sequence ' || l_schema || '.hz_location_profiles_s cache 20000';
568 execute immediate 'alter sequence ' || l_schema || '.hz_organization_profiles_s cache 20000';
569 execute immediate 'alter sequence ' || l_schema || '.hz_org_contacts_s cache 20000';
570 execute immediate 'alter sequence ' || l_schema || '.hz_org_contact_roles_s cache 20000';
571 execute immediate 'alter sequence ' || l_schema || '.hz_orig_system_ref_s cache 20000';
572 execute immediate 'alter sequence ' || l_schema || '.hz_parties_s cache 20000';
573 execute immediate 'alter sequence ' || l_schema || '.hz_party_number_s cache 20000';
574 execute immediate 'alter sequence ' || l_schema || '.hz_party_sites_s cache 20000';
575 execute immediate 'alter sequence ' || l_schema || '.hz_party_site_number_s cache 20000';
576 execute immediate 'alter sequence ' || l_schema || '.hz_party_site_uses_s cache 20000';
577 execute immediate 'alter sequence ' || l_schema || '.hz_person_profiles_s cache 20000';
578 execute immediate 'alter sequence ' || l_schema || '.hz_relationships_s cache 20000';
579 END IF;
580 */
581 ELSE
582
583 /* Check if any outstanding data load concurrent req. If so, do not reset */
584 open c_running_dl_wrapper;
585 fetch c_running_dl_wrapper into l_running_dl_wrapper;
586 IF l_running_dl_wrapper is null THEN
587 execute immediate 'alter sequence ' || l_schema || '.hz_organization_profiles_s increment by 1';
588 execute immediate 'alter sequence ' || l_schema || '.hz_person_profiles_s increment by 1';
589 execute immediate 'alter sequence ' || l_schema || '.hz_location_profiles_s increment by 1';
590 execute immediate 'alter sequence ' || l_schema || '.hz_party_usg_assignments_s increment by 1';
591 END IF;
592 close c_running_dl_wrapper;
593
594 /*
595 IF P_BATCH_MODE_FLAG = 'Y' THEN
596 execute immediate 'alter sequence ' || l_schema || '.hz_code_assignments_s cache 20';
597 execute immediate 'alter sequence ' || l_schema || '.hz_contact_numbers_s cache 20';
598 execute immediate 'alter sequence ' || l_schema || '.hz_contact_points_s cache 20';
599 execute immediate 'alter sequence ' || l_schema || '.hz_credit_ratings_s cache 20';
600 execute immediate 'alter sequence ' || l_schema || '.hz_financial_numbers_s cache 20';
601 execute immediate 'alter sequence ' || l_schema || '.hz_financial_reports_s cache 20';
602 execute immediate 'alter sequence ' || l_schema || '.hz_imp_errors_s cache 20';
603 execute immediate 'alter sequence hr.hr_locations_s cache 20';
604 execute immediate 'alter sequence ' || l_schema || '.hz_location_profiles_s cache 20';
605 execute immediate 'alter sequence ' || l_schema || '.hz_organization_profiles_s cache 20';
606 execute immediate 'alter sequence ' || l_schema || '.hz_org_contacts_s cache 20';
607 execute immediate 'alter sequence ' || l_schema || '.hz_org_contact_roles_s cache 20';
608 execute immediate 'alter sequence ' || l_schema || '.hz_orig_system_ref_s cache 20';
609 execute immediate 'alter sequence ' || l_schema || '.hz_parties_s cache 20';
610 execute immediate 'alter sequence ' || l_schema || '.hz_party_number_s cache 20';
611 execute immediate 'alter sequence ' || l_schema || '.hz_party_sites_s cache 20';
612 execute immediate 'alter sequence ' || l_schema || '.hz_party_site_number_s cache 20';
613 execute immediate 'alter sequence ' || l_schema || '.hz_party_site_uses_s cache 20';
614 execute immediate 'alter sequence ' || l_schema || '.hz_person_profiles_s cache 20';
615 execute immediate 'alter sequence ' || l_schema || '.hz_relationships_s cache 20';
616 END IF;
617 */
618 END IF;
619 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
620 hz_utility_v2pub.debug(p_message=>'WRP:ALTER_SEQUENCES()-',
621 p_prefix=>'',
622 p_msg_level=>fnd_log.level_procedure);
623 END IF;
624
625 END ALTER_SEQUENCES;
626
627
628 /* Clean up staging. Delete for online, truncate for batch */
629 /* Also chean up the following tables: */
630 /* hz_imp_osr_change */
631 /* HZ_IMP_INT_DEDUP_RESULTS */
632 /* HZ_IMP_TMP_REL_END_DATE */
633 PROCEDURE CLEANUP_STAGING(
634 P_BATCH_ID IN NUMBER,
635 P_BATCH_MODE_FLAG IN VARCHAR2
636 ) IS
637 l_bool BOOLEAN;
638 l_status VARCHAR2(255);
639 l_schema VARCHAR2(255);
640 l_tmp VARCHAR2(2000);
641 --l_debug_prefix VARCHAR2(30) := '';
642 BEGIN
643
644 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
645 hz_utility_v2pub.debug(p_message=>'WRP:CLEANUP_STAGING()+',
646 p_prefix=>'',
647 p_msg_level=>fnd_log.level_procedure);
648 END IF;
649
650 l_bool := fnd_installation.GET_APP_INFO('AR',l_status,l_tmp,l_schema);
651
652 IF P_BATCH_MODE_FLAG = 'Y' THEN
653 execute immediate 'ALTER TABLE ' || l_schema || '.HZ_IMP_PARTIES_SG TRUNCATE PARTITION batchpar DROP STORAGE';
654 execute immediate 'ALTER TABLE ' || l_schema || '.HZ_IMP_ADDRESSES_SG TRUNCATE PARTITION batchpar DROP STORAGE';
655 execute immediate 'ALTER TABLE ' || l_schema || '.HZ_IMP_CONTACTPTS_SG TRUNCATE PARTITION batchpar DROP STORAGE';
656 execute immediate 'ALTER TABLE ' || l_schema || '.HZ_IMP_CREDITRTNGS_SG TRUNCATE PARTITION batchpar DROP STORAGE';
657 execute immediate 'ALTER TABLE ' || l_schema || '.HZ_IMP_CLASSIFICS_SG TRUNCATE PARTITION batchpar DROP STORAGE';
658 execute immediate 'ALTER TABLE ' || l_schema || '.HZ_IMP_FINREPORTS_SG TRUNCATE PARTITION batchpar DROP STORAGE';
659 execute immediate 'ALTER TABLE ' || l_schema || '.HZ_IMP_FINNUMBERS_SG TRUNCATE PARTITION batchpar DROP STORAGE';
660 execute immediate 'ALTER TABLE ' || l_schema || '.HZ_IMP_RELSHIPS_SG TRUNCATE PARTITION batchpar DROP STORAGE';
661 execute immediate 'ALTER TABLE ' || l_schema || '.HZ_IMP_CONTACTS_SG TRUNCATE PARTITION batchpar DROP STORAGE';
662 execute immediate 'ALTER TABLE ' || l_schema || '.HZ_IMP_CONTACTROLES_SG TRUNCATE PARTITION batchpar DROP STORAGE';
663 execute immediate 'ALTER TABLE ' || l_schema || '.HZ_IMP_ADDRESSUSES_SG TRUNCATE PARTITION batchpar DROP STORAGE';
664
665 ELSE
666 DELETE HZ_IMP_PARTIES_SG
667 WHERE batch_id = P_BATCH_ID
668 AND batch_mode_flag = P_BATCH_MODE_FLAG;
669 DELETE HZ_IMP_ADDRESSES_SG
670 WHERE batch_id = P_BATCH_ID
671 AND batch_mode_flag = P_BATCH_MODE_FLAG;
672 DELETE HZ_IMP_CONTACTPTS_SG
673 WHERE batch_id = P_BATCH_ID
674 AND batch_mode_flag = P_BATCH_MODE_FLAG;
675 DELETE HZ_IMP_CREDITRTNGS_SG
676 WHERE batch_id = P_BATCH_ID
677 AND batch_mode_flag = P_BATCH_MODE_FLAG;
678 DELETE HZ_IMP_CLASSIFICS_SG
679 WHERE batch_id = P_BATCH_ID
680 AND batch_mode_flag = P_BATCH_MODE_FLAG;
681 DELETE HZ_IMP_FINREPORTS_SG
682 WHERE batch_id = P_BATCH_ID
683 AND batch_mode_flag = P_BATCH_MODE_FLAG;
684 DELETE HZ_IMP_FINNUMBERS_SG
685 WHERE batch_id = P_BATCH_ID
686 AND batch_mode_flag = P_BATCH_MODE_FLAG;
687 DELETE HZ_IMP_RELSHIPS_SG
688 WHERE batch_id = P_BATCH_ID
689 AND batch_mode_flag = P_BATCH_MODE_FLAG;
690 DELETE HZ_IMP_CONTACTS_SG
691 WHERE batch_id = P_BATCH_ID
692 AND batch_mode_flag = P_BATCH_MODE_FLAG;
693 DELETE HZ_IMP_CONTACTROLES_SG
694 WHERE batch_id = P_BATCH_ID
695 AND batch_mode_flag = P_BATCH_MODE_FLAG;
696 DELETE HZ_IMP_ADDRESSUSES_SG
697 WHERE batch_id = P_BATCH_ID
698 AND batch_mode_flag = P_BATCH_MODE_FLAG;
699
700 END IF;
701
702 DELETE hz_imp_osr_change WHERE batch_id = P_BATCH_ID;
703 --DELETE HZ_IMP_INT_DEDUP_RESULTS WHERE batch_id = P_BATCH_ID;
704 DELETE HZ_IMP_TMP_REL_END_DATE WHERE batch_id = P_BATCH_ID;
705
706 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
707 hz_utility_v2pub.debug(p_message=>'WRP:CLEANUP_STAGING()-',
708 p_prefix=>'',
709 p_msg_level=>fnd_log.level_procedure);
710 END IF;
711
712 END CLEANUP_STAGING;
713
714
715 /* Fix bug 4374278: Clean up the SSM table to remove any active duplicate
716 records that have the same OS+OSR. Duplicates are created as the fix for
717 4374278 will allow multiple rows in party interface with same OS+OSR but
718 different party_id to be imported. In parties loading, since we'll check
719 for existence of OS+OSR in SSM table to decide if we'll insert new records,
720 if it's new DNB purchase for two existing parties, duplicate SSM records
721 can be created */
722 PROCEDURE CLEANUP_SSM(
723 P_CONTENT_SRC_TYPE IN VARCHAR2,
724 P_BATCH_ID IN NUMBER,
725 P_BATCH_MODE_FLAG IN VARCHAR2,
726 P_ORIG_SYSTEM IN VARCHAR2,
727 P_REQUEST_ID IN NUMBER
728 ) IS
729
730 l_cleanup_ssm_site_qry VARCHAR2(4000) :=
731 ' SELECT psosr.rowid,posr.owner_table_id
732 FROM HZ_IMP_ADDRESSES_SG site_sg,
733 HZ_ORIG_SYS_REFERENCES posr,
734 HZ_ORIG_SYS_REFERENCES psosr
735 WHERE site_sg.batch_id = :CP_BATCH_ID
736 AND site_sg.batch_mode_flag = :CP_BATCH_MODE_FLAG
737 AND site_sg.party_orig_system = :CP_OS
738 AND posr.orig_system = site_sg.party_orig_system
739 AND posr.orig_system_reference = site_sg.party_orig_system_reference
740 AND posr.status = ''A''
741 AND posr.owner_table_name = ''HZ_PARTIES''
742 AND psosr.orig_system = site_sg.site_orig_system
743 AND psosr.orig_system_reference = site_sg.site_orig_system_reference
744 AND psosr.status = ''A''
745 AND psosr.owner_table_name = ''HZ_PARTY_SITES''
746 AND psosr.owner_table_id = site_sg.party_site_id
747 AND psosr.party_id <> posr.owner_table_id
748 AND NOT EXISTS
749 (SELECT 1 FROM HZ_IMP_TMP_ERRORS err
750 WHERE err.batch_id = :CP_BATCH_ID
751 AND err.request_id = :REQ_ID
752 AND err.interface_table_name = ''HZ_IMP_ADDRESSES_INT''
753 AND err.int_row_id = site_sg.int_row_id)
754 ';
755
756 l_cleanup_ssm_cpt_qry VARCHAR2(4000) :=
757 ' SELECT cposr.rowid,posr.owner_table_id
758 FROM HZ_IMP_CONTACTPTS_SG cpt_sg,HZ_IMP_CONTACTPTS_INT cpi,
759 HZ_ORIG_SYS_REFERENCES posr,
760 HZ_ORIG_SYS_REFERENCES cposr
761 WHERE cpt_sg.batch_id = :CP_BATCH_ID
762 AND cpt_sg.batch_mode_flag = :CP_BATCH_MODE_FLAG
763 AND cpt_sg.party_orig_system = :CP_OS
764 AND posr.orig_system = cpt_sg.party_orig_system
765 AND posr.orig_system_reference = cpt_sg.party_orig_system_reference
766 AND posr.status = ''A''
767 AND posr.owner_table_name = ''HZ_PARTIES''
768 AND cposr.orig_system = cpi.cp_orig_system
769 AND cposr.orig_system_reference = cpi.cp_orig_system_reference
770 AND cposr.status = ''A''
771 AND cposr.owner_table_name = ''HZ_CONTACT_POINTS''
772 AND cposr.party_id <> posr.owner_table_id
773 AND cpi.rowid = cpt_sg.int_row_id
774 AND NOT EXISTS
775 (SELECT 1 FROM HZ_IMP_TMP_ERRORS err
776 WHERE err.batch_id = :CP_BATCH_ID
777 AND err.request_id = :REQ_ID
778 AND err.interface_table_name = ''HZ_IMP_CONTACTPTS_INT''
779 AND err.int_row_id = cpt_sg.int_row_id)
780 ';
781
782
783 l_cleanup_ssm_add_dnb VARCHAR2(4000) :=
784 ' select hos.rowid, hps_old.identifying_Address_flag, hps_old.rowid,hps_new.rowid
785 from hz_imp_addresses_sg has, hz_orig_sys_references hos, hz_party_sites hps_new,hz_party_sites hps_old
786 where has.site_orig_system=''DNB''
787 and has.old_site_orig_system_ref=hos.orig_system_reference
788 and hos.orig_system=has.site_orig_system
789 and hos.owner_table_name=''HZ_PARTY_SITES''
790 and hos.status=''A''
791 and has.old_site_orig_system_ref is not null
792 and has.old_site_orig_system_ref<> has.site_orig_system_reference
793 and has.site_orig_system_reference=hps_new.orig_system_reference
794 and hps_old.party_site_id = hos.owner_table_id
795 and has.party_site_id=hps_new.party_site_id
796 and has.action_flag=''I''
797 and hps_old.status=''A''
798 and hps_new.status=''A''
799 and has.party_id=hps_old.party_id
800 and hps_old.party_id=hps_new.party_id
801 and has.batch_id = :CP_BATCH_ID
802 and has.batch_mode_flag=:CP_BATCH_MODE_FLAG
803 ';
804
805 l_cleanup_ssm_cpt_dnb VARCHAR2(4000) :=
806 ' select hos.rowid, hcp_old.primary_flag, hcp_old.rowid,hcp_new.rowid
807 from hz_imp_contactpts_sg hcs, hz_orig_sys_references hos, hz_contact_points hcp_new,hz_contact_points hcp_old
808 where hcs.party_orig_system=''DNB''
809 and hcs.old_cp_orig_system_ref=hos.orig_system_reference
810 and hos.orig_system=hcs.party_orig_system
811 and hos.owner_table_name=''HZ_CONTACT_POINTS''
812 and hos.status=''A''
813 and hcs.old_cp_orig_system_ref is not null
814 and hcp_old.contact_point_id = hos.owner_table_id
815 and hcs.contact_point_id=hcp_new.contact_point_id
816 and hcs.action_flag=''I''
817 and hcp_old.status=''A''
818 and hcp_new.status=''A''
819 and (hcs.party_id=hcp_old.owner_table_id
820 or hcs.party_site_id = hcp_old.owner_table_id)
821 and hcp_old.owner_table_id=hcp_new.owner_table_id
822 and hcp_old.owner_table_name=hcp_new.owner_table_name
823 and hcp_old.contact_point_id <> hcp_new.contact_point_id
824 and hcp_old.contact_point_type = hcp_new.contact_point_type
825 and hcp_old.contact_point_type = hcs.contact_point_type
826 and hcs.batch_id = :CP_BATCH_ID
827 and hcs.batch_mode_flag=:CP_BATCH_MODE_FLAG
828 ';
829
830 c_cleanup_ssm_pid cleanup_ssm_pid_csr_type;
831
832 BEGIN
833 update hz_orig_sys_references set status = 'I', end_date_active = sysdate
834 where rowid in (
835 select row_id from (
836 select /*+ parallel(osr) */ rowid row_id, orig_system_ref_id osrid, orig_system_reference osr,
837 owner_table_name, rank() over
838 (partition by orig_system_reference, owner_table_name order by last_update_date desc, orig_system_ref_id desc) rn
839 from hz_orig_sys_references osr
840 where osr.orig_system = P_CONTENT_SRC_TYPE
841 and osr.status = 'A'
842 and osr.end_date_active is null
843 ) r
844 where rn > 1);
845
846 -- Set the HZ_ORIG_SYS_REFERENCES.party_id of party site to be the current
847 -- active party in SSM
848 l_last_fetch := FALSE;
849
850 OPEN c_cleanup_ssm_pid FOR l_cleanup_ssm_site_qry
851 USING P_batch_id,P_batch_mode_flag, P_ORIG_SYSTEM,P_batch_id,P_REQUEST_ID ;
852 LOOP
853 FETCH c_cleanup_ssm_pid BULK COLLECT INTO l_row_id ,l_party_owner_table_id LIMIT l_rows;
854 IF c_cleanup_ssm_pid%NOTFOUND THEN
855 l_last_fetch := TRUE;
856 END IF;
857 IF l_row_id.COUNT = 0 AND l_last_fetch THEN
858 EXIT;
859 END IF;
860
861 FORALL j IN l_row_id.FIRST.. l_row_id.LAST
862 UPDATE HZ_ORIG_SYS_REFERENCES
863 SET party_id = l_party_owner_table_id(j)
864 WHERE rowid = l_row_id(j);
865
866 IF l_last_fetch = TRUE THEN
867 EXIT;
868 END IF;
869 END LOOP;
870 CLOSE c_cleanup_ssm_pid;
871
872 -- Set the HZ_ORIG_SYS_REFERENCES.party_id of contact point to be the current
873 -- active party in SSM
874 l_last_fetch := FALSE;
875 OPEN c_cleanup_ssm_pid FOR l_cleanup_ssm_cpt_qry
876 USING P_batch_id,P_batch_mode_flag, P_ORIG_SYSTEM,P_batch_id,P_REQUEST_ID ;
877
878 LOOP
879 FETCH c_cleanup_ssm_pid BULK COLLECT INTO l_row_id ,l_party_owner_table_id LIMIT l_rows;
880 IF c_cleanup_ssm_pid%NOTFOUND THEN
881 l_last_fetch := TRUE;
882 END IF;
883 IF l_row_id.COUNT = 0 AND l_last_fetch THEN
884 EXIT;
885 END IF;
886
887 FORALL j IN l_row_id.FIRST.. l_row_id.LAST
888 UPDATE HZ_ORIG_SYS_REFERENCES
889 SET party_id = l_party_owner_table_id(j)
890 WHERE rowid = l_row_id(j);
891
892 IF l_last_fetch = TRUE THEN
893 EXIT;
894 END IF;
895 END LOOP;
896 CLOSE c_cleanup_ssm_pid;
897
898 -- Bug 6268875.
899 l_last_fetch := FALSE;
900 OPEN c_cleanup_ssm_pid FOR l_cleanup_ssm_add_dnb
901 USING P_batch_id,P_batch_mode_flag ;
902
903 LOOP
904 FETCH c_cleanup_ssm_pid BULK COLLECT INTO l_row_id, l_primary_flag, l_row_id_old, l_row_id_new LIMIT l_rows;
905
906 IF c_cleanup_ssm_pid%NOTFOUND THEN
907 l_last_fetch := TRUE;
908 END IF;
909 IF l_row_id.COUNT = 0 AND l_last_fetch THEN
910 EXIT;
911 END IF;
912
913
914 FORALL j IN l_row_id.FIRST.. l_row_id.LAST
915 UPDATE HZ_ORIG_SYS_REFERENCES
916 SET status='I',end_date_active = sysdate
917 WHERE rowid = l_row_id(j);
918
919 FORALL j IN l_row_id_old.FIRST.. l_row_id_old.LAST
920 UPDATE HZ_PARTY_SITES
921 SET status='I', identifying_address_flag = 'N'
922 WHERE rowid = l_row_id_old(j);
923
924 FORALL j IN l_row_id_new.FIRST.. l_row_id_new.LAST
925 UPDATE HZ_PARTY_SITES
926 SET identifying_address_flag = l_primary_flag(j)
927 WHERE rowid = l_row_id_new(j);
928
929 IF l_last_fetch = TRUE THEN
930 EXIT;
931 END IF;
932 END LOOP;
933 CLOSE c_cleanup_ssm_pid;
934
935 -- Bug 6268875.
936 l_last_fetch := FALSE;
937 OPEN c_cleanup_ssm_pid FOR l_cleanup_ssm_cpt_dnb
938 USING P_batch_id,P_batch_mode_flag ;
939
940 LOOP
941 FETCH c_cleanup_ssm_pid BULK COLLECT INTO l_row_id, l_primary_flag, l_row_id_old, l_row_id_new LIMIT l_rows;
942
943 IF c_cleanup_ssm_pid%NOTFOUND THEN
944 l_last_fetch := TRUE;
945 END IF;
946 IF l_row_id.COUNT = 0 AND l_last_fetch THEN
947 EXIT;
948 END IF;
949
950
951 FORALL j IN l_row_id.FIRST.. l_row_id.LAST
952 UPDATE HZ_ORIG_SYS_REFERENCES
953 SET status='I',end_date_active = sysdate
954 WHERE rowid = l_row_id(j);
955
956 FORALL j IN l_row_id_old.FIRST.. l_row_id_old.LAST
957 UPDATE HZ_CONTACT_POINTS
958 SET status='I', primary_flag = 'N'
959 WHERE rowid = l_row_id_old(j);
960
961 FORALL j IN l_row_id_new.FIRST.. l_row_id_new.LAST
962 UPDATE HZ_CONTACT_POINTS
963 SET primary_flag = l_primary_flag(j)
964 WHERE rowid = l_row_id_new(j);
965
966 IF l_last_fetch = TRUE THEN
967 EXIT;
968 END IF;
969
970 END LOOP;
971 CLOSE c_cleanup_ssm_pid;
972
973 END CLEANUP_SSM;
974
975 PROCEDURE CLEANUP_DUP_OSR(
976 P_BATCH_ID IN NUMBER,
977 P_BATCH_MODE_FLAG IN VARCHAR2,
978 P_ENTITY IN VARCHAR2,
979 P_PARTY_OS IN VARCHAR2)
980 IS
981
982 TYPE L_ROWIDList IS TABLE OF HZ_IMP_PARTIES_SG.INT_ROW_ID%TYPE;
983 TYPE L_PIDList IS TABLE OF HZ_IMP_PARTIES_SG.PARTY_ID%TYPE;
984 TYPE L_POSList IS TABLE OF HZ_IMP_PARTIES_SG.PARTY_ORIG_SYSTEM%TYPE;
985 TYPE L_POSRList IS TABLE OF HZ_IMP_PARTIES_SG.PARTY_ORIG_SYSTEM_REFERENCE%TYPE;
986 l_rowid L_ROWIDList;
987 l_pid L_PIDList;
988 l_pos L_POSList;
989 l_posr L_POSRList;
990 TYPE L_PSOSList IS TABLE OF hz_imp_addresses_sg.SITE_ORIG_SYSTEM%TYPE;
991 TYPE L_PSOSRList IS TABLE OF hz_imp_addresses_sg.SITE_ORIG_SYSTEM_REFERENCE%TYPE;
992 l_psos L_PSOSList;
993 l_psosr L_PSOSRList;
994
995 rows NUMBER;
996 l_last_fetch BOOLEAN;
997 I NUMBER;
998
999 CURSOR c_parties IS
1000 SELECT ROW_ID,party_id,party_os, party_osr
1001 FROM
1002 ( SELECT int_row_id row_id,party_id,PARTY_ORIG_SYSTEM party_os,PARTY_ORIG_SYSTEM_REFERENCE party_osr
1003 ,row_number() over (partition by party_id order by int_row_id) rn
1004 from hz_imp_parties_sg
1005 where batch_id=P_BATCH_ID
1006 and batch_mode_flag=P_BATCH_MODE_FLAG
1007 and party_orig_system = P_PARTY_OS
1008 and action_flag='U'
1009 )
1010 WHERE rn>1;
1011
1012 CURSOR c_addresses IS
1013 SELECT ROW_ID,site_os, site_osr
1014 FROM
1015 ( SELECT int_row_id row_id,SITE_ORIG_SYSTEM site_os,SITE_ORIG_SYSTEM_REFERENCE site_osr
1016 ,row_number() over (partition by party_site_id order by int_row_id) rn
1017 from hz_imp_addresses_sg
1018 where batch_id=P_BATCH_ID
1019 and party_orig_system = P_PARTY_OS
1020 and action_flag = 'U'
1021 )
1022 WHERE rn>1;
1023
1024 CURSOR c_parties_d IS
1025 SELECT pint.party_orig_system, pint.party_orig_system_reference,psg.party_id
1026 FROM hz_imp_parties_int pint, hz_imp_parties_sg psg
1027 WHERE pint.batch_id=P_BATCH_ID
1028 AND pint.party_orig_system=P_PARTY_OS
1029 AND pint.interface_status='D'
1030 AND Pint.rowid=psg.int_row_id
1031 AND pint.party_orig_system=psg.party_orig_system
1032 AND pint.party_orig_system_reference=psg.party_orig_system_reference
1033 AND psg.batch_mode_flag=P_BATCH_MODE_FLAG
1034 AND psg.action_flag='U' ;
1035
1036 BEGIN
1037
1038 rows := 10000;
1039 l_last_fetch:= FALSE;
1040
1041 IF P_ENTITY='PARTY'
1042 THEN
1043 OPEN c_parties;
1044 LOOP
1045 FETCH c_parties BULK COLLECT INTO
1046 l_rowid, l_pid, l_pos, l_posr
1047 LIMIT rows;
1048
1049 IF c_parties%NOTFOUND THEN
1050 l_last_fetch := TRUE ;
1051 END IF;
1052
1053 IF l_rowid.COUNT = 0 AND l_last_fetch = TRUE THEN
1054 EXIT;
1055 END IF;
1056
1057 FORALL i in l_rowid.FIRST..l_rowid.LAST
1058 UPDATE HZ_IMP_PARTIES_INT party
1059 SET INTERFACE_STATUS = 'D'
1060 WHERE rowid=l_rowid(i);
1061
1062 IF l_last_fetch = TRUE THEN
1063 EXIT;
1064 END IF;
1065
1066 END LOOP;
1067 CLOSE c_parties;
1068 ELSIF P_ENTITY='ADDRESS'
1069 THEN
1070 OPEN c_addresses;
1071 LOOP
1072 FETCH c_addresses BULK COLLECT INTO
1073 l_rowid, l_psos, l_psosr
1074 LIMIT rows;
1075
1076
1077 IF c_addresses%NOTFOUND THEN
1078 l_last_fetch := TRUE ;
1079 END IF;
1080
1081 IF l_rowid.COUNT = 0 AND l_last_fetch = TRUE THEN
1082 EXIT;
1083 END IF;
1084
1085 FORALL i in l_rowid.FIRST..l_rowid.LAST
1086 UPDATE HZ_IMP_ADDRESSES_INT party
1087 SET INTERFACE_STATUS = 'D'
1088 WHERE rowid=l_rowid(i);
1089
1090 --Update child entities
1091 --Update site uses
1092 FORALL i in l_psosr.FIRST..l_psosr.LAST
1093 UPDATE HZ_IMP_ADDRESSUSES_INT
1094 SET interface_status = 'D'
1095 WHERE batch_id = p_batch_id
1096 AND site_orig_system = l_psos(i)
1097 AND site_orig_system_reference = l_psosr(i);
1098
1099 IF l_last_fetch = TRUE THEN
1100 EXIT;
1101 END IF;
1102
1103 END LOOP;
1104 CLOSE c_addresses;
1105
1106 OPEN c_parties_d;
1107 LOOP
1108 FETCH c_parties_d BULK COLLECT INTO
1109 l_pos, l_posr, l_pid
1110 LIMIT rows;
1111
1112 IF c_parties_d%NOTFOUND THEN
1113 l_last_fetch := TRUE ;
1114 END IF;
1115
1116 IF l_pid.COUNT = 0 AND l_last_fetch = TRUE THEN
1117 EXIT;
1118 END IF;
1119
1120 -- Update child entities
1121 --Update contact records
1122 FORALL i in l_posr.FIRST..l_posr.LAST
1123 UPDATE HZ_IMP_CONTACTS_INT
1124 SET interface_status = 'D'
1125 WHERE batch_id = p_batch_id
1126 AND ((sub_orig_system = l_pos(i)
1127 AND sub_orig_system_reference = l_posr(i))
1128 OR
1129 (obj_orig_system = l_pos(i)
1130 AND obj_orig_system_reference = l_posr(i))
1131 );
1132 --Update contact roles
1133 FORALL i in l_posr.FIRST..l_posr.LAST
1134 UPDATE HZ_IMP_CONTACTROLES_INT
1135 SET interface_status = 'D'
1136 WHERE batch_id = p_batch_id
1137 AND sub_orig_system = l_pos(i)
1138 AND sub_orig_system_reference = l_posr(i);
1139
1140 -- Update contact point records
1141 FORALL i in l_posr.FIRST..l_posr.LAST
1142 UPDATE HZ_IMP_CONTACTPTS_INT
1143 SET interface_status = 'D'
1144 WHERE batch_id = p_batch_id AND Nvl(party_id,l_pid(i)) = l_pid(i)
1145 AND party_orig_system = l_pos(i)
1146 AND party_orig_system_reference = l_posr(i);
1147
1148 -- Update relationship records
1149 FORALL i in l_posr.FIRST..l_posr.LAST
1150 UPDATE HZ_IMP_RELSHIPS_INT
1151 SET interface_status = 'D'
1152 WHERE batch_id = p_batch_id
1153 AND ((sub_orig_system = l_pos(i)
1154 AND sub_orig_system_reference = l_posr(i))
1155 OR
1156 (obj_orig_system = l_pos(i)
1157 AND obj_orig_system_reference = l_posr(i)
1158 AND Nvl(obj_id,l_pid(i)) = l_pid(i))
1159 );
1160
1161 --Update Classifications
1162 FORALL i in l_posr.FIRST..l_posr.LAST
1163 UPDATE HZ_IMP_CLASSIFICS_INT
1164 SET interface_status = 'D'
1165 WHERE batch_id = p_batch_id AND Nvl(party_id,l_pid(i)) = l_pid(i)
1166 AND party_orig_system = l_pos(i)
1167 AND party_orig_system_reference = l_posr(i);
1168
1169 --Update Credit Ratings
1170 FORALL i in l_posr.FIRST..l_posr.LAST
1171 UPDATE HZ_IMP_CREDITRTNGS_INT
1172 SET interface_status = 'D'
1173 WHERE batch_id = p_batch_id AND Nvl(party_id,l_pid(i)) = l_pid(i)
1174 AND party_orig_system = l_pos(i)
1175 AND party_orig_system_reference = l_posr(i);
1176
1177 --Update Financial Numbers
1178 FORALL i in l_posr.FIRST..l_posr.LAST
1179 UPDATE HZ_IMP_FINNUMBERS_INT
1180 SET interface_status = 'D'
1181 WHERE batch_id = p_batch_id AND Nvl(party_id,l_pid(i)) = l_pid(i)
1182 AND party_orig_system = l_pos(i)
1183 AND party_orig_system_reference = l_posr(i);
1184
1185 --Update Financial Reports
1186 FORALL i in l_posr.FIRST..l_posr.LAST
1187 UPDATE HZ_IMP_FINREPORTS_INT
1188 SET interface_status = 'D'
1189 WHERE batch_id = p_batch_id AND Nvl(party_id,l_pid(i)) = l_pid(i)
1190 AND party_orig_system = l_pos(i)
1191 AND party_orig_system_reference = l_posr(i);
1192
1193 IF l_last_fetch = TRUE THEN
1194 EXIT;
1195 END IF;
1196 END LOOP;
1197 CLOSE c_parties_d;
1198 END IF;
1199 END CLEANUP_DUP_OSR;
1200
1201 PROCEDURE UPDATE_DISPLAYED_DUNS_PID(
1202 P_BATCH_ID IN NUMBER,
1203 P_BATCH_MODE_FLAG IN VARCHAR2
1204 ) IS
1205 l_party_id T_ENTITY_ID;
1206 l_displayed_duns_party_id T_ENTITY_ID;
1207
1208 CURSOR c_displayed_duns(p_batch_id number, p_batch_mode_flag varchar2) IS
1209 select /*+ parallel(pi) leading (pi) use_nl(ps) use_nl(osr) */
1210 ps.party_id, osr.owner_table_id displayed_duns_party_id
1211 from hz_orig_sys_references osr,
1212 hz_imp_parties_int pi,
1213 hz_imp_parties_sg ps
1214 where osr.owner_table_name = 'HZ_PARTIES'
1215 and osr.orig_system = 'DNB'
1216 and osr.orig_system_reference = pi.displayed_duns
1217 and pi.batch_id = p_batch_id
1218 and pi.party_orig_system = 'DNB'
1219 and pi.rowid = ps.int_row_id
1220 and ps.batch_mode_flag = p_batch_mode_flag
1221 and pi.batch_id = ps.batch_id
1222 and pi.party_orig_system = ps.party_orig_system
1223 and pi.party_orig_system_reference = ps.party_orig_system_reference
1224 and pi.party_orig_system_reference <> pi.displayed_duns;
1225
1226 --l_debug_prefix VARCHAR2(30) := '';
1227 BEGIN
1228
1229 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
1230 hz_utility_v2pub.debug(p_message=>'WRP:UPDATE_DISPLAYED_DUNS_PID()+',
1231 p_prefix=>'',
1232 p_msg_level=>fnd_log.level_procedure);
1233 END IF;
1234
1235 hz_common_pub.disable_cont_source_security;
1236
1237 OPEN c_displayed_duns(P_BATCH_ID, P_BATCH_MODE_FLAG);
1238 FETCH c_displayed_duns BULK COLLECT INTO
1239 l_party_id, l_displayed_duns_party_id;
1240
1241 ForAll j in 1..l_party_id.count
1242 update hz_organization_profiles
1243 set displayed_duns_party_id = l_displayed_duns_party_id(j)
1244 where party_id = l_party_id(j)
1245 and effective_end_date is null
1246 and actual_content_source = 'DNB';
1247
1248 CLOSE c_displayed_duns;
1249
1250 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
1251 hz_utility_v2pub.debug(p_message=>'WRP:UPDATE_DISPLAYED_DUNS_PID()-',
1252 p_prefix=>'',
1253 p_msg_level=>fnd_log.level_procedure);
1254 END IF;
1255
1256 END UPDATE_DISPLAYED_DUNS_PID;
1257
1258
1259 PROCEDURE GENERATE_ERRORS (
1260 P_BATCH_ID IN NUMBER,
1261 P_REQUEST_ID IN NUMBER,
1262 P_ORIG_SYSTEM IN VARCHAR2,
1263 P_RERUN_FLAG IN VARCHAR2,
1264 P_END_OF_DL_FLAG IN VARCHAR2
1265 ) IS
1266
1267 CURSOR c_err(p_batch_id number, p_request_id number) IS
1268 select int_row_id, interface_table_name, error_id
1269 from hz_imp_tmp_errors
1270 where batch_id = p_batch_id
1271 and request_id = p_request_id;
1272
1273 l_row_id T_ROWID;
1274 l_error_id T_ERROR_ID;
1275 l_table_name T_TABLE_NAME;
1276
1277 -- Bug 3871136
1278 l_dss_person_err VARCHAR2(2000) := hz_dss_util_pub.get_display_name(null,'PERSON');
1279 l_dss_org_err VARCHAR2(2000) := hz_dss_util_pub.get_display_name(null,'ORGANIZATION');
1280 l_dss_others_err VARCHAR2(2000) := hz_dss_util_pub.get_display_name('HZ_PARTIES',null);
1281 l_dss_rel_err VARCHAR2(2000) := hz_dss_util_pub.get_display_name('HZ_RELATIONSHIPS',null);
1282 l_dss_ca_err VARCHAR2(2000) := hz_dss_util_pub.get_display_name('HZ_CODE_ASSIGNMENTS',null);
1283 l_dss_cp_err VARCHAR2(2000) := hz_dss_util_pub.get_display_name(null,'PARTY_CONTACT_POINTS');
1284 l_dss_ps_err VARCHAR2(2000) := hz_dss_util_pub.get_display_name(null,'PARTY_SITE_CONTACT_POINTS');
1285
1286 --l_debug_prefix VARCHAR2(30) := '';
1287 BEGIN
1288 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
1289 hz_utility_v2pub.debug(p_message=>'WRP:GENERATE_ERRORS()+',
1290 p_prefix=>'',
1291 p_msg_level=>fnd_log.level_procedure);
1292 END IF;
1293
1294 /* If not a new batch, clear all 'C' interface_status */
1295 IF P_RERUN_FLAG <> 'N' THEN
1296
1297 IF P_END_OF_DL_FLAG = 'Y' THEN
1298
1299 update HZ_IMP_PARTIES_INT
1300 set error_id = null,
1301 interface_status = null
1302 where BATCH_ID = P_BATCH_ID
1303 and PARTY_ORIG_SYSTEM = P_ORIG_SYSTEM
1304 and INTERFACE_STATUS = 'C';
1305
1306 update HZ_IMP_ADDRESSES_INT
1307 set error_id = null,
1308 interface_status = null
1309 where BATCH_ID = P_BATCH_ID
1310 and PARTY_ORIG_SYSTEM = P_ORIG_SYSTEM
1311 and INTERFACE_STATUS = 'C';
1312
1313 update HZ_IMP_CONTACTPTS_INT
1314 set error_id = null,
1315 interface_status = null
1316 where BATCH_ID = P_BATCH_ID
1317 and PARTY_ORIG_SYSTEM = P_ORIG_SYSTEM
1318 and INTERFACE_STATUS = 'C';
1319
1320 update HZ_IMP_CREDITRTNGS_INT
1321 set error_id = null,
1322 interface_status = null
1323 where BATCH_ID = P_BATCH_ID
1324 and PARTY_ORIG_SYSTEM = P_ORIG_SYSTEM
1325 and INTERFACE_STATUS = 'C';
1326
1327 update HZ_IMP_CLASSIFICS_INT
1328 set error_id = null,
1329 interface_status = null
1330 where BATCH_ID = P_BATCH_ID
1331 and PARTY_ORIG_SYSTEM = P_ORIG_SYSTEM
1332 and INTERFACE_STATUS = 'C';
1333
1334 update HZ_IMP_FINREPORTS_INT
1335 set error_id = null,
1336 interface_status = null
1337 where BATCH_ID = P_BATCH_ID
1338 and PARTY_ORIG_SYSTEM = P_ORIG_SYSTEM
1339 and INTERFACE_STATUS = 'C';
1340
1341 update HZ_IMP_FINNUMBERS_INT
1342 set error_id = null,
1343 interface_status = null
1344 where BATCH_ID = P_BATCH_ID
1345 and PARTY_ORIG_SYSTEM = P_ORIG_SYSTEM
1346 and INTERFACE_STATUS = 'C';
1347
1348 update HZ_IMP_RELSHIPS_INT
1349 set error_id = null,
1350 interface_status = null
1351 where BATCH_ID = P_BATCH_ID
1352 and SUB_ORIG_SYSTEM = P_ORIG_SYSTEM
1353 and INTERFACE_STATUS = 'C';
1354
1355 update HZ_IMP_CONTACTS_INT
1356 set error_id = null,
1357 interface_status = null
1358 where BATCH_ID = P_BATCH_ID
1359 and SUB_ORIG_SYSTEM = P_ORIG_SYSTEM
1360 and INTERFACE_STATUS = 'C';
1361
1362 update HZ_IMP_CONTACTROLES_INT
1363 set error_id = null,
1364 interface_status = null
1365 where BATCH_ID = P_BATCH_ID
1366 and SUB_ORIG_SYSTEM = P_ORIG_SYSTEM
1367 and INTERFACE_STATUS = 'C';
1368
1369 update HZ_IMP_ADDRESSUSES_INT
1370 set error_id = null,
1371 interface_status = null
1372 where BATCH_ID = P_BATCH_ID
1373 and PARTY_ORIG_SYSTEM = P_ORIG_SYSTEM
1374 and INTERFACE_STATUS = 'C';
1375
1376 ELSE
1377
1378 /* Parties are loaded in stage 2. So if work unit is
1379 completed for either stage 2 or stage 3, it means the
1380 parties should have been loaded. For other entities,
1381 only need to check stage 3. */
1382 update HZ_IMP_PARTIES_INT
1383 set error_id = null, interface_status = null
1384 where batch_id = P_BATCH_ID
1385 and party_orig_system = P_ORIG_SYSTEM
1386 and interface_status = 'C'
1387 and exists (
1388 select 1 from
1389 hz_imp_work_units
1390 where party_orig_system_reference
1391 between from_orig_system_ref and to_orig_system_ref
1392 and batch_id = P_BATCH_ID
1393 and stage in (2, 3)
1394 and status = 'C');
1395
1396 update HZ_IMP_ADDRESSES_INT
1397 set error_id = null, interface_status = null
1398 where batch_id = P_BATCH_ID
1399 and party_orig_system = P_ORIG_SYSTEM
1400 and interface_status = 'C'
1401 and exists (
1402 select 1 from
1403 hz_imp_work_units
1404 where party_orig_system_reference
1405 between from_orig_system_ref and to_orig_system_ref
1406 and batch_id = P_BATCH_ID
1407 and stage = 3
1408 and status = 'C');
1409
1410 update HZ_IMP_CONTACTPTS_INT
1411 set error_id = null, interface_status = null
1412 where batch_id = P_BATCH_ID
1413 and party_orig_system = P_ORIG_SYSTEM
1414 and interface_status = 'C'
1415 and exists (
1416 select 1 from
1417 hz_imp_work_units
1418 where party_orig_system_reference
1419 between from_orig_system_ref and to_orig_system_ref
1420 and batch_id = P_BATCH_ID
1421 and stage = 3
1422 and status = 'C');
1423
1424 update HZ_IMP_CREDITRTNGS_INT
1425 set error_id = null, interface_status = null
1426 where batch_id = P_BATCH_ID
1427 and party_orig_system = P_ORIG_SYSTEM
1428 and interface_status = 'C'
1429 and exists (
1430 select 1 from
1431 hz_imp_work_units
1432 where party_orig_system_reference
1433 between from_orig_system_ref and to_orig_system_ref
1434 and batch_id = P_BATCH_ID
1435 and stage = 3
1436 and status = 'C');
1437
1438 update HZ_IMP_CLASSIFICS_INT
1439 set error_id = null, interface_status = null
1440 where batch_id = P_BATCH_ID
1441 and party_orig_system = P_ORIG_SYSTEM
1442 and interface_status = 'C'
1443 and exists (
1444 select 1 from
1445 hz_imp_work_units
1446 where party_orig_system_reference
1447 between from_orig_system_ref and to_orig_system_ref
1448 and batch_id = P_BATCH_ID
1449 and stage = 3
1450 and status = 'C');
1451
1452 update HZ_IMP_FINREPORTS_INT
1453 set error_id = null, interface_status = null
1454 where batch_id = P_BATCH_ID
1455 and party_orig_system = P_ORIG_SYSTEM
1456 and interface_status = 'C'
1457 and exists (
1458 select 1 from
1459 hz_imp_work_units
1460 where party_orig_system_reference
1461 between from_orig_system_ref and to_orig_system_ref
1462 and batch_id = P_BATCH_ID
1463 and stage = 3
1464 and status = 'C');
1465
1466 update HZ_IMP_FINNUMBERS_INT
1467 set error_id = null, interface_status = null
1468 where batch_id = P_BATCH_ID
1469 and party_orig_system = P_ORIG_SYSTEM
1470 and interface_status = 'C'
1471 and exists (
1472 select 1 from
1473 hz_imp_work_units
1474 where party_orig_system_reference
1475 between from_orig_system_ref and to_orig_system_ref
1476 and batch_id = P_BATCH_ID
1477 and stage = 3
1478 and status = 'C');
1479
1480 update HZ_IMP_RELSHIPS_INT
1481 set error_id = null, interface_status = null
1482 where batch_id = P_BATCH_ID
1483 and sub_orig_system = P_ORIG_SYSTEM
1484 and interface_status = 'C'
1485 and exists (
1486 select 1 from
1487 hz_imp_work_units
1488 where sub_orig_system_reference
1489 between from_orig_system_ref and to_orig_system_ref
1490 and batch_id = P_BATCH_ID
1491 and stage = 3
1492 and status = 'C');
1493
1494 update HZ_IMP_CONTACTS_INT
1495 set error_id = null, interface_status = null
1496 where batch_id = P_BATCH_ID
1497 and sub_orig_system = P_ORIG_SYSTEM
1498 and interface_status = 'C'
1499 and exists (
1500 select 1 from
1501 hz_imp_work_units
1502 where sub_orig_system_reference
1503 between from_orig_system_ref and to_orig_system_ref
1504 and batch_id = P_BATCH_ID
1505 and stage = 3
1506 and status = 'C');
1507
1508 update HZ_IMP_CONTACTROLES_INT
1509 set error_id = null, interface_status = null
1510 where batch_id = P_BATCH_ID
1511 and sub_orig_system = P_ORIG_SYSTEM
1512 and interface_status = 'C'
1513 and exists (
1514 select 1 from
1515 hz_imp_work_units
1516 where sub_orig_system_reference
1517 between from_orig_system_ref and to_orig_system_ref
1518 and batch_id = P_BATCH_ID
1519 and stage = 3
1520 and status = 'C');
1521
1522 update HZ_IMP_ADDRESSUSES_INT
1523 set error_id = null, interface_status = null
1524 where batch_id = P_BATCH_ID
1525 and party_orig_system = P_ORIG_SYSTEM
1526 and interface_status = 'C'
1527 and exists (
1528 select 1 from
1529 hz_imp_work_units
1530 where party_orig_system_reference
1531 between from_orig_system_ref and to_orig_system_ref
1532 and batch_id = P_BATCH_ID
1533 and stage = 3
1534 and status = 'C');
1535
1536 END IF; /* P_END_OF_DL_FLAG = 'Y' */
1537
1538 END IF; /* P_RERUN_FLAG <> 'N' */
1539
1540 /* Populate permanent errors tables from tmp errors table */
1541 insert all
1542 when (E1_FLAG is null) then
1543 into hz_imp_errors (
1544 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name)
1545 values (
1546 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_ADDRESSES_INT',
1547 'HZ_IMP_INVALD_ADDR_ASSIGN')
1548 when (E2_FLAG is null) then
1549 into hz_imp_errors (
1550 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name,
1551 token1_name, token1_value)
1552 values (
1553 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_ADDRESSES_INT',
1554 'HZ_API_MISSING_COLUMN', 'COLUMN', 'ADDRESS1')
1555 when (E3_FLAG is null) then
1556 into hz_imp_errors (
1557 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name,
1558 token1_name, token1_value)
1559 values (
1560 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_ADDRESSES_INT',
1561 'HZ_API_MISSING_COLUMN', 'COLUMN', 'COUNTRY')
1562 when (E4_FLAG is null) then
1563 into hz_imp_errors (
1564 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name,
1565 token1_name, token1_value, token2_name, token2_value, token3_name,
1566 token3_value)
1567 values (
1568 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_ADDRESSES_INT',
1569 'HZ_API_INVALID_FK', 'FK', 'LANGUAGE', 'COLUMN', 'LANGUAGE_CODE',
1570 'TABLE', 'FND_LANGUAGES')
1571 when (E5_FLAG is null) then
1572 into hz_imp_errors (
1573 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name,
1574 token1_name, token1_value, token2_name, token2_value, token3_name,
1575 token3_value)
1576 values (
1577 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_ADDRESSES_INT',
1578 'HZ_API_INVALID_FK', 'FK', 'TIMEZONE_CODE', 'COLUMN',
1579 'TIMEZONE_CODE', 'TABLE', 'FND_TIMEZONES_B')
1580 when (E6_FLAG is null) then
1581 into hz_imp_errors (
1582 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name,
1583 token1_name, token1_value)
1584 values (
1585 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_ADDRESSES_INT',
1586 'AR_RAPI_DESC_FLEX_INVALID', 'DEF_NAME', 'HZ_PARTY_SITES')
1587 when (E7_FLAG is null) then
1588 into hz_imp_errors (
1589 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name)
1590 values (
1591 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_ADDRESSES_INT',
1592 'HZ_IMP_ADDR_NO_CORRECTION')
1593 when (E8_FLAG is null) then
1594 into hz_imp_errors (
1595 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name,
1596 token1_name, token1_value, token2_name, token2_value, token3_name,
1597 token3_value)
1598 values (
1599 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_ADDRESSES_INT',
1600 'HZ_DSS_SECURITY_FAIL', 'USER_NAME', USER_NAME,
1601 'OPER_NAME', 'UPDATE', 'OBJECT_NAME', 'HZ_PARTY_SITES')
1602 when (DUP_VAL_IDX_EXCEP_FLAG = 'A') then
1603 into hz_imp_errors (
1604 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name,
1605 token1_name, token1_value, token2_name,
1606 token2_value)
1607 values (
1608 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_ADDRESSES_INT',
1609 'HZ_IMP_DUP_VAL', 'INDEX', 'HZ_PARTY_SITES_U1', 'ENTITY',
1610 'HZ_PARTY_SITES')
1611 when (DUP_VAL_IDX_EXCEP_FLAG = 'B') then
1612 into hz_imp_errors (
1613 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name,
1614 token1_name, token1_value, token2_name, token2_value)
1615 values (
1616 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_ADDRESSES_INT',
1617 'HZ_IMP_DUP_VAL', 'INDEX', 'HZ_PARTY_SITES_U2', 'ENTITY',
1618 'HZ_PARTY_SITES')
1619 when (DUP_VAL_IDX_EXCEP_FLAG = 'C') then
1620 into hz_imp_errors (
1621 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name,
1622 token1_name, token1_value, token2_name, token2_value)
1623 values (
1624 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_ADDRESSES_INT',
1625 'HZ_IMP_DUP_VAL', 'INDEX', 'HZ_LOCATIONS_U1', 'ENTITY', 'HZ_LOCATIONS')
1626 when (ACTION_MISMATCH_FLAG is null) then
1627 into hz_imp_errors (
1628 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name)
1629 values (
1630 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_ADDRESSES_INT',
1631 'HZ_IMP_ACTION_MISMATCH')
1632 when (MISSING_PARENT_FLAG is null) then
1633 into hz_imp_errors (
1634 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name)
1635 values (
1636 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_ADDRESSES_INT',
1637 'HZ_IMP_PARENT_PARTY_NOT_FOUND')
1638 when (E9_FLAG is null) then
1639 into hz_imp_errors (
1640 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name)
1641 values (
1642 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_ADDRESSES_INT',
1643 'HZ_NOTALLOW_UPDATE_THIRD_PARTY')
1644 when (E10_FLAG is null) then
1645 into hz_imp_errors (
1646 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name)
1647 values (
1648 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_ADDRESSES_INT',
1649 'HZ_IMP_PARENT_PARTY_NOT_FOUND')
1650 when (E11_FLAG is null) then
1651 into hz_imp_errors (
1652 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name,
1653 token1_name, token1_value, token2_name, token2_value)
1654 values (
1655 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_ADDRESSES_INT',
1656 'HZ_API_INVALID_LOOKUP', 'COLUMN', 'CREATED_BY_MODULE', 'LOOKUP_TYPE',
1657 'HZ_CREATED_BY_MODULES')
1658 select creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, E1_FLAG, E2_FLAG,
1659 E3_FLAG, E4_FLAG, E5_FLAG, E6_FLAG, E7_FLAG, E8_FLAG, E9_FLAG, E10_FLAG,
1660 E11_FLAG, DUP_VAL_IDX_EXCEP_FLAG,
1661 ACTION_MISMATCH_FLAG, MISSING_PARENT_FLAG, FND_GLOBAL.USER_NAME
1662 from hz_imp_tmp_errors e
1663 where e.batch_id = P_BATCH_ID
1664 and e.request_id = P_REQUEST_ID
1665 and e.interface_table_name = 'HZ_IMP_ADDRESSES_INT';
1666
1667 insert all
1668 when (E1_FLAG is null) then
1669 into hz_imp_errors (
1670 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name,
1671 token1_name, token1_value, token2_name, token2_value)
1672 values (
1673 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_FINREPORTS_INT',
1674 'HZ_API_DATE_GREATER', 'DATE2', 'REPORT_END_DATE', 'DATE1',
1675 'REPORT_START_DATE')
1676 when (E2_FLAG is null) then
1677 into hz_imp_errors (
1678 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name,
1679 token1_name, token1_value, token2_name, token2_value)
1680 values (
1681 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_FINREPORTS_INT',
1682 'HZ_API_INVALID_LOOKUP', 'COLUMN', 'AUDIT_IND', 'LOOKUP_TYPE', 'YES/NO')
1683 when (E3_FLAG is null) then
1684 into hz_imp_errors (
1685 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name,
1686 token1_name, token1_value, token2_name, token2_value)
1687 values (
1688 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_FINREPORTS_INT',
1689 'HZ_API_INVALID_LOOKUP', 'COLUMN', 'CONSOLIDATED_IND',
1690 'LOOKUP_TYPE', 'YES/NO')
1691 when (E4_FLAG is null) then
1692 into hz_imp_errors (
1693 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name,
1694 token1_name, token1_value, token2_name, token2_value)
1695 values (
1696 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_FINREPORTS_INT',
1697 'HZ_API_INVALID_LOOKUP', 'COLUMN', 'ESTIMATED_IND', 'LOOKUP_TYPE',
1698 'YES/NO')
1699 when (E5_FLAG is null) then
1700 into hz_imp_errors (
1701 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name,
1702 token1_name, token1_value, token2_name, token2_value)
1703 values (
1704 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_FINREPORTS_INT',
1705 'HZ_API_INVALID_LOOKUP', 'COLUMN', 'FINAL_IND', 'LOOKUP_TYPE', 'YES/NO')
1706 when (E6_FLAG is null) then
1707 into hz_imp_errors (
1708 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name,
1709 token1_name, token1_value, token2_name, token2_value)
1710 values (
1711 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_FINREPORTS_INT',
1712 'HZ_API_INVALID_LOOKUP', 'COLUMN', 'FISCAL_IND', 'LOOKUP_TYPE', 'YES/NO')
1713 when (E7_FLAG is null) then
1714 into hz_imp_errors (
1715 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name,
1716 token1_name, token1_value, token2_name, token2_value)
1717 values (
1718 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_FINREPORTS_INT',
1719 'HZ_API_INVALID_LOOKUP', 'COLUMN', 'FORECAST_IND', 'LOOKUP_TYPE',
1720 'YES/NO')
1721 when (E8_FLAG is null) then
1722 into hz_imp_errors (
1723 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name,
1724 token1_name, token1_value, token2_name, token2_value)
1725 values (
1726 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_FINREPORTS_INT',
1727 'HZ_API_INVALID_LOOKUP', 'COLUMN', 'OPENING_IND', 'LOOKUP_TYPE',
1728 'YES/NO')
1729 when (E9_FLAG is null) then
1730 into hz_imp_errors (
1731 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name,
1732 token1_name, token1_value, token2_name, token2_value)
1733 values (
1734 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_FINREPORTS_INT',
1735 'HZ_API_INVALID_LOOKUP', 'COLUMN', 'PROFORMA_IND', 'LOOKUP_TYPE',
1736 'YES/NO')
1737 when (E10_FLAG is null) then
1738 into hz_imp_errors (
1739 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name,
1740 token1_name, token1_value, token2_name, token2_value)
1741 values (
1742 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_FINREPORTS_INT',
1743 'HZ_API_INVALID_LOOKUP', 'COLUMN', 'QUALIFIED_IND', 'LOOKUP_TYPE',
1744 'YES/NO')
1745 when (E11_FLAG is null) then
1746 into hz_imp_errors (
1747 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name,
1748 token1_name, token1_value, token2_name, token2_value)
1749 values (
1750 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_FINREPORTS_INT',
1751 'HZ_API_INVALID_LOOKUP', 'COLUMN', 'RESTATED_IND', 'LOOKUP_TYPE',
1752 'YES/NO')
1753 when (E12_FLAG is null) then
1754 into hz_imp_errors (
1755 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name,
1756 token1_name, token1_value, token2_name, token2_value)
1757 values (
1758 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_FINREPORTS_INT',
1759 'HZ_API_INVALID_LOOKUP', 'COLUMN', 'SIGNED_BY_PRINCIPALS_IND',
1760 'LOOKUP_TYPE', 'YES/NO')
1761 when (E13_FLAG is null) then
1762 into hz_imp_errors (
1763 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name,
1764 token1_name, token1_value, token2_name, token2_value)
1765 values (
1766 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_FINREPORTS_INT',
1767 'HZ_API_INVALID_LOOKUP', 'COLUMN', 'TRIAL_BALANCE_IND',
1768 'LOOKUP_TYPE', 'YES/NO')
1769
1770 when (E14_FLAG is null) then
1771 into hz_imp_errors (
1772 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name,
1773 token1_name, token1_value, token2_name, token2_value)
1774 values (
1775 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_FINREPORTS_INT',
1776 'HZ_API_INVALID_LOOKUP', 'COLUMN', 'UNBALANCED_IND', 'LOOKUP_TYPE',
1777 'YES/NO')
1778 when (E15_FLAG is null) then
1779 into hz_imp_errors (
1780 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name,
1781 token1_name, token1_value, token2_name, token2_value)
1782 values (
1783 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_FINREPORTS_INT',
1784 'HZ_API_INVALID_COMBINATION2', 'COLUMN1', 'issued_period', 'COLUMN2',
1785 'report_start_date')
1786 when (E16_FLAG is null) then
1787 into hz_imp_errors (
1788 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name,
1789 token1_name, token1_value, token2_name, token2_value)
1790 values (
1791 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_FINREPORTS_INT',
1792 'HZ_API_INVALID_COMBINATION3', 'COLUMN1', 'report_start_date', 'COLUMN2',
1793 'report_end_date')
1794 when (E17_FLAG is null) then
1795 into hz_imp_errors (
1796 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name,
1797 token1_name, token1_value, token2_name, token2_value)
1798 values (
1799 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_FINREPORTS_INT',
1800 'HZ_API_INVALID_LOOKUP', 'COLUMN', 'CREATED_BY_MODULE', 'LOOKUP_TYPE',
1801 'HZ_CREATED_BY_MODULES')
1802 when (DUP_VAL_IDX_EXCEP_FLAG = 'A') then
1803 into hz_imp_errors (
1804 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name,
1805 token1_name, token1_value, token2_name, token2_value)
1806 values (
1807 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_FINREPORTS_INT',
1808 'HZ_IMP_DUP_VAL', 'INDEX', 'HZ_FINANCIAL_REPORTS_U1', 'ENTITY',
1809 'HZ_FINANCIAL_REPORTS')
1810 when (DUP_VAL_IDX_EXCEP_FLAG = 'B') then
1811 into hz_imp_errors (
1812 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name,
1813 token1_name, token1_value, token2_name, token2_value)
1814 values (
1815 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_FINREPORTS_INT',
1816 'HZ_IMP_DUP_VAL', 'INDEX', 'HZ_FINANCIAL_REPORTS_U2', 'ENTITY',
1817 'HZ_FINANCIAL_REPORTS')
1818 when (ACTION_MISMATCH_FLAG is null) then
1819 into hz_imp_errors (
1820 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name)
1821 values (
1822 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_FINREPORTS_INT',
1823 'HZ_IMP_ACTION_MISMATCH')
1824 when (MISSING_PARENT_FLAG is null) then
1825 into hz_imp_errors (
1826 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name)
1827 values (
1828 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_FINREPORTS_INT',
1829 'HZ_IMP_PARENT_PARTY_NOT_FOUND')
1830 select /*+ leading(e) use_nl(int) rowid(int) */ e.creation_date, e.created_by, e.last_update_date, e.last_updated_by, e.last_update_login, e.program_application_id,
1831 e.program_id, e.program_update_date, e.error_id, e.batch_id, e.request_id, e.interface_table_name, E1_FLAG, E2_FLAG,
1832 E3_FLAG, E4_FLAG, E5_FLAG, E6_FLAG, E7_FLAG, E8_FLAG, E9_FLAG,
1833 E10_FLAG, E11_FLAG, E12_FLAG, E13_FLAG, E14_FLAG, E15_FLAG, E16_FLAG,
1834 E17_FLAG,
1835 DUP_VAL_IDX_EXCEP_FLAG, ACTION_MISMATCH_FLAG, MISSING_PARENT_FLAG,
1836 INT.REPORT_START_DATE, INT.REPORT_END_DATE
1837 from hz_imp_tmp_errors e,
1838 HZ_IMP_FINREPORTS_INT int
1839 where e.batch_id = P_BATCH_ID
1840 and e.request_id = P_REQUEST_ID
1841 and e.int_row_id = int.rowid
1842 and e.interface_table_name = 'HZ_IMP_FINREPORTS_INT';
1843
1844 insert all
1845 when (E1_FLAG is null) then
1846 into hz_imp_errors (
1847 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name,
1848 token1_name, token1_value, token2_name, token2_value)
1849 values (
1850 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_FINNUMBERS_INT',
1851 'HZ_API_INVALID_LOOKUP', 'COLUMN', 'FINANCIAL_NUMBER_NAME',
1852 'LOOKUP_TYPE', 'FIN_NUM_NAME')
1853 when (E2_FLAG is null) then
1854 into hz_imp_errors (
1855 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name,
1856 token1_name, token1_value, token2_name, token2_value)
1857 values (
1858 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_FINNUMBERS_INT',
1859 'HZ_API_INVALID_LOOKUP', 'COLUMN', 'CREATED_BY_MODULE', 'LOOKUP_TYPE',
1860 'HZ_CREATED_BY_MODULES')
1861 when (DUP_VAL_IDX_EXCEP_FLAG = 'A') then
1862 into hz_imp_errors (
1863 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name,
1864 token1_name, token1_value, token2_name, token2_value)
1865 values (
1866 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_FINNUMBERS_INT',
1867 'HZ_IMP_DUP_VAL', 'INDEX', 'HZ_FINANCIAL_NUMBERS_U1', 'ENTITY',
1868 'HZ_FINANCIAL_NUMBERS')
1869 when (DUP_VAL_IDX_EXCEP_FLAG = 'B') then
1870 into hz_imp_errors (
1871 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name,
1872 token1_name, token1_value, token2_name, token2_value)
1873 values (
1874 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_FINNUMBERS_INT',
1875 'HZ_IMP_DUP_VAL', 'INDEX', 'HZ_FINANCIAL_NUMBERS_U2', 'ENTITY',
1876 'HZ_FINANCIAL_NUMBERS')
1877 when (ACTION_MISMATCH_FLAG is null) then
1878 into hz_imp_errors (
1879 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name)
1880 values (
1881 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_FINNUMBERS_INT',
1882 'HZ_IMP_ACTION_MISMATCH')
1883 when (MISSING_PARENT_FLAG is null) then
1884 into hz_imp_errors (
1885 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name)
1886 values (
1887 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_FINNUMBERS_INT',
1888 'HZ_IMP_FINREPORT_NOT_FOUND')
1889 select creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, E1_FLAG, E2_FLAG,
1890 DUP_VAL_IDX_EXCEP_FLAG, ACTION_MISMATCH_FLAG, MISSING_PARENT_FLAG
1891 from hz_imp_tmp_errors e
1892 where e.batch_id = P_BATCH_ID
1893 and e.request_id = P_REQUEST_ID
1894 and e.interface_table_name = 'HZ_IMP_FINNUMBERS_INT';
1895
1896 insert all
1897 when (E1_FLAG is null) then
1898 into hz_imp_errors (
1899 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name,
1900 token1_name, token1_value, token2_name, token2_value)
1901 values (
1902 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_PARTIES_INT',
1903 'HZ_API_INVALID_LOOKUP', 'COLUMN', 'FISCAL_YEAREND_MONTH',
1904 'LOOKUP_TYPE', 'MONTH')
1905 when (E2_FLAG is null) then
1906 into hz_imp_errors (
1907 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name,
1908 token1_name, token1_value, token2_name, token2_value)
1909 values (
1910 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_PARTIES_INT',
1911 'HZ_API_INVALID_LOOKUP', 'COLUMN', 'LEGAL_STATUS', 'LOOKUP_TYPE',
1912 'LEGAL_STATUS')
1913 when (E3_FLAG is null) then
1914 into hz_imp_errors (
1915 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name,
1916 token1_name, token1_value, token2_name, token2_value)
1917 values (
1918 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_PARTIES_INT',
1919 'HZ_API_INVALID_LOOKUP', 'COLUMN', 'LOCAL_BUS_IDEN_TYPE',
1920 'LOOKUP_TYPE', 'LOCAL_BUS_IDEN_TYPE')
1921 when (E4_FLAG is null) then
1922 into hz_imp_errors (
1923 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name,
1924 token1_name, token1_value, token2_name, token2_value)
1925 values (
1926 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_PARTIES_INT',
1927 'HZ_API_INVALID_LOOKUP', 'COLUMN', 'REGISTRATION_TYPE',
1928 'LOOKUP_TYPE', 'REGISTRATION_TYPE')
1929 when (E5_FLAG is null) then
1930 into hz_imp_errors (
1931 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name,
1932 token1_name, token1_value, token2_name, token2_value)
1933 values (
1934 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_PARTIES_INT',
1935 'HZ_API_INVALID_LOOKUP', 'COLUMN', 'HQ_BRANCH_IND', 'LOOKUP_TYPE',
1936 'HQ_BRANCH_IND')
1937 when (E6_FLAG is null) then
1938 into hz_imp_errors (
1939 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name,
1940 token1_name, token1_value, token2_name, token2_value)
1941 values (
1942 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_PARTIES_INT',
1943 'HZ_API_INVALID_LOOKUP', 'COLUMN', 'MINORITY_OWNED_IND',
1944 'LOOKUP_TYPE', 'YES/NO')
1945 when (E7_FLAG is null) then
1946 into hz_imp_errors (
1947 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name,
1948 token1_name, token1_value, token2_name, token2_value)
1949 values (
1950 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_PARTIES_INT',
1951 'HZ_API_INVALID_LOOKUP', 'COLUMN', 'GSA_INDICATOR_FLAG', 'LOOKUP_TYPE',
1952 'YES/NO')
1953 when (E8_FLAG is null) then
1954 into hz_imp_errors (
1955 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name,
1956 token1_name, token1_value, token2_name, token2_value)
1957 values (
1958 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_PARTIES_INT',
1959 'HZ_API_INVALID_LOOKUP', 'COLUMN', 'IMPORT_IND', 'LOOKUP_TYPE', 'YES/NO')
1960 when (E9_FLAG is null) then
1961 into hz_imp_errors (
1962 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name,
1963 token1_name, token1_value, token2_name, token2_value)
1964 values (
1965 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_PARTIES_INT',
1966 'HZ_API_INVALID_LOOKUP', 'COLUMN', 'EXPORT_IND', 'LOOKUP_TYPE', 'YES/NO')
1967 when (E10_FLAG is null) then
1968 into hz_imp_errors (
1969 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name,
1970 token1_name, token1_value, token2_name, token2_value)
1971 values (
1972 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_PARTIES_INT',
1973 'HZ_API_INVALID_LOOKUP', 'COLUMN', 'BRANCH_FLAG', 'LOOKUP_TYPE',
1974 'YES/NO')
1975 when (E11_FLAG is null) then
1976 into hz_imp_errors (
1977 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name,
1978 token1_name, token1_value, token2_name, token2_value)
1979 values (
1980 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_PARTIES_INT',
1981 'HZ_API_INVALID_LOOKUP', 'COLUMN', 'DISADV_8A_IND', 'LOOKUP_TYPE',
1982 'YES/NO')
1983 when (E12_FLAG is null) then
1984 into hz_imp_errors (
1985 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name,
1986 token1_name, token1_value, token2_name, token2_value)
1987 values (
1988 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_PARTIES_INT',
1989 'HZ_API_INVALID_LOOKUP', 'COLUMN', 'LABOR_SURPLUS_IND',
1990 'LOOKUP_TYPE', 'YES/NO')
1991 when (E13_FLAG is null) then
1992 into hz_imp_errors (
1993 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name,
1994 token1_name, token1_value, token2_name, token2_value)
1995 values (
1996 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_PARTIES_INT',
1997 'HZ_API_INVALID_LOOKUP', 'COLUMN', 'OOB_IND', 'LOOKUP_TYPE', 'YES/NO')
1998 when (E14_FLAG is null) then
1999 into hz_imp_errors (
2000 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name,
2001 token1_name, token1_value, token2_name, token2_value)
2002 values (
2003 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_PARTIES_INT',
2004 'HZ_API_INVALID_LOOKUP', 'COLUMN', 'PARENT_SUB_IND', 'LOOKUP_TYPE',
2005 'YES/NO')
2006 when (E15_FLAG is null) then
2007 into hz_imp_errors (
2008 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name,
2009 token1_name, token1_value, token2_name, token2_value)
2010 values (
2011 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_PARTIES_INT',
2012 'HZ_API_INVALID_LOOKUP', 'COLUMN', 'PUBLIC_PRIVATE_OWNERSHIP_FLAG',
2013 'LOOKUP_TYPE', 'YES/NO')
2014 when (E16_FLAG is null) then
2015 into hz_imp_errors (
2016 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name,
2017 token1_name, token1_value, token2_name, token2_value)
2018 values (
2019 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_PARTIES_INT',
2020 'HZ_API_INVALID_LOOKUP', 'COLUMN', 'SMALL_BUS_IND', 'LOOKUP_TYPE',
2021 'YES/NO')
2022 when (E17_FLAG is null) then
2023 into hz_imp_errors (
2024 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name,
2025 token1_name, token1_value, token2_name, token2_value)
2026 values (
2027 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_PARTIES_INT',
2028 'HZ_API_INVALID_LOOKUP', 'COLUMN', 'TOTAL_EMP_EST_IND',
2029 'LOOKUP_TYPE', 'TOTAL_EMP_EST_IND')
2030 when (E18_FLAG is null) then
2031 into hz_imp_errors (
2032 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name,
2033 token1_name, token1_value, token2_name, token2_value)
2034 values (
2035 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_PARTIES_INT',
2036 'HZ_API_INVALID_LOOKUP', 'COLUMN', 'TOTAL_EMP_MIN_IND',
2037 'LOOKUP_TYPE', 'TOTAL_EMP_MIN_IND')
2038 when (E19_FLAG is null) then
2039 into hz_imp_errors (
2040 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name,
2041 token1_name, token1_value, token2_name, token2_value)
2042 values (
2043 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_PARTIES_INT',
2044 'HZ_API_INVALID_LOOKUP', 'COLUMN', 'TOTAL_EMPLOYEES_IND', 'LOOKUP_TYPE',
2045 'TOTAL_EMPLOYEES_INDICATOR')
2046 when (E20_FLAG is null) then
2047 into hz_imp_errors (
2048 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name,
2049 token1_name, token1_value, token2_name, token2_value)
2050 values (
2051 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_PARTIES_INT',
2052 'HZ_API_INVALID_LOOKUP', 'COLUMN', 'WOMAN_OWNED_IND', 'LOOKUP_TYPE',
2053 'YES/NO')
2054 when (E21_FLAG is null) then
2055 into hz_imp_errors (
2056 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name,
2057 token1_name, token1_value, token2_name, token2_value)
2058 values (
2059 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_PARTIES_INT',
2060 'HZ_API_INVALID_LOOKUP', 'COLUMN', 'EMP_AT_PRIMARY_ADR_EST_IND',
2061 'LOOKUP_TYPE', 'EMP_AT_PRIMARY_ADR_EST_IND')
2062 when (E22_FLAG is null) then
2063 into hz_imp_errors (
2064 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name,
2065 token1_name, token1_value, token2_name, token2_value)
2066 values (
2067 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_PARTIES_INT',
2068 'HZ_API_INVALID_LOOKUP', 'COLUMN', 'EMP_AT_PRIMARY_ADR_MIN_IND',
2069 'LOOKUP_TYPE', 'EMP_AT_PRIMARY_ADR_MIN_IND')
2070 when (E23_FLAG is null) then
2071 into hz_imp_errors (
2072 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name,
2073 token1_name, token1_value, token2_name, token2_value)
2074 values (
2075 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_PARTIES_INT',
2076 'HZ_API_INVALID_LOOKUP', 'COLUMN', 'MARITAL_STATUS', 'LOOKUP_TYPE',
2077 'MARITAL_STATUS')
2078 when (E24_FLAG is null) then
2079 into hz_imp_errors (
2080 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name,
2081 token1_name, token1_value, token2_name, token2_value)
2082 values (
2083 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_PARTIES_INT',
2084 'HZ_API_INVALID_LOOKUP', 'COLUMN', 'PERSON_PRE_NAME_ADJUNCT',
2085 'LOOKUP_TYPE', 'CONTACT_TITLE')
2086 when (E25_FLAG is null) then
2087 into hz_imp_errors (
2088 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name)
2089 values (
2090 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_PARTIES_INT',
2091 'HZ_IMP_DECEASED_FLAG_ERROR')
2092 when (E26_FLAG is null) then
2093 into hz_imp_errors (
2094 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name,
2095 token1_name, token1_value, token2_name, token2_value)
2096 values (
2097 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_PARTIES_INT',
2098 'HZ_API_INVALID_LOOKUP', 'COLUMN', 'HEAD_OF_HOUSEHOLD_FLAG',
2099 'LOOKUP_TYPE', 'YES/NO')
2100 when (E27_FLAG is null) then
2101 into hz_imp_errors (
2102 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name,
2103 token1_name, token1_value, token2_name, token2_value)
2104 values (
2105 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_PARTIES_INT',
2106 'HZ_API_DATE_GREATER', 'DATE2', 'SYSDATE', 'DATE1', 'DATE_OF_BIRTH')
2107 when (E28_FLAG is null) then
2108 into hz_imp_errors (
2109 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name,
2110 token1_name, token1_value, token2_name, token2_value)
2111 values (
2112 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_PARTIES_INT',
2113 'HZ_API_DATE_GREATER', 'DATE2', 'SYSDATE', 'DATE1', 'DATE_OF_DEATH')
2114 when (E29_FLAG is null) then
2115 into hz_imp_errors (
2116 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name,
2117 token1_name, token1_value, token2_name, token2_value)
2118 values (
2119 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_PARTIES_INT',
2120 'HZ_API_DATE_GREATER', 'DATE2', 'DATE_OF_DEATH', 'DATE1', 'DATE_OF_BIRTH')
2121 when (E30_FLAG is null) then
2122 into hz_imp_errors (
2123 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name)
2124 values (
2125 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_PARTIES_INT',
2126 'HZ_IMP_PARTY_TYPE_ERROR')
2127 when (E31_FLAG is null) then
2128 into hz_imp_errors (
2129 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name,
2130 token1_name, token1_value, token2_name, token2_value)
2131 values (
2132 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_PARTIES_INT',
2133 'HZ_API_INVALID_LOOKUP', 'COLUMN', 'RENT_OWN_IND', 'LOOKUP_TYPE',
2134 'OWN_RENT_IND')
2135 when (E32_FLAG is null) then
2136 into hz_imp_errors (
2137 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name,
2138 token1_name, token1_value)
2139 values (
2140 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_PARTIES_INT',
2141 'AR_RAPI_DESC_FLEX_INVALID', 'DFF_NAME', 'HZ_PARTIES')
2142 when (E33_FLAG is null) then
2143 into hz_imp_errors (
2144 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name)
2145 values (
2146 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_PARTIES_INT',
2147 'HZ_IMP_PARTY_NAME_ERROR')
2148 when (E34_FLAG='P') then
2149 into hz_imp_errors (
2150 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name,
2151 token1_name, token1_value)
2152 values (
2153 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_PARTIES_INT',
2154 'HZ_DSS_NO_UPDATE_PRIVILEGE', 'ENTITY_NAME', l_dss_person_err)
2155 when (E34_FLAG='O') then
2156 into hz_imp_errors (
2157 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name,
2158 token1_name, token1_value)
2159 values (
2160 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_PARTIES_INT',
2161 'HZ_DSS_NO_UPDATE_PRIVILEGE', 'ENTITY_NAME', l_dss_org_err)
2162 when (E34_FLAG is null) then
2163 into hz_imp_errors (
2164 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name,
2165 token1_name, token1_value)
2166 values (
2167 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_PARTIES_INT',
2168 'HZ_DSS_NO_UPDATE_PRIVILEGE', 'ENTITY_NAME', l_dss_others_err)
2169 when (E35_FLAG is null) then
2170 into hz_imp_errors (
2171 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name,
2172 token1_name, token1_value, token2_name, token2_value)
2173 values (
2174 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_PARTIES_INT',
2175 'HZ_API_NO_RECORD', 'RECORD', 'PARTY', 'VALUE', ' ')
2176 -- Bug 4310257
2177 when (E36_FLAG is null) then
2178 into hz_imp_errors (
2179 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name,
2180 token1_name, token1_value, token2_name, token2_value)
2181 values (
2182 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_PARTIES_INT',
2183 'HZ_API_INVALID_LOOKUP', 'COLUMN', 'GENDER', 'LOOKUP_TYPE',
2184 'HZ_GENDER')
2185 when (E37_FLAG is null) then
2186 into hz_imp_errors (
2187 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name,
2188 token1_name, token1_value, token2_name, token2_value)
2189 values (
2190 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_PARTIES_INT',
2191 'HZ_API_INVALID_LOOKUP', 'COLUMN', 'PERSON_IDEN_TYPE', 'LOOKUP_TYPE',
2192 'HZ_PERSON_IDEN_TYPE')
2193 when (E38_FLAG is null) then
2194 into hz_imp_errors (
2195 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name,
2196 token1_name, token1_value, token2_name, token2_value)
2197 values (
2198 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_PARTIES_INT',
2199 'HZ_API_INVALID_LOOKUP', 'COLUMN', 'CREATED_BY_MODULE', 'LOOKUP_TYPE',
2200 'HZ_CREATED_BY_MODULES')
2201 when (E39_FLAG is null) then
2202 into hz_imp_errors (
2203 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name)
2204 values (
2205 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_PARTIES_INT',
2206 'HZ_NO_CHANGE_PARTY_NAME')
2207 when (DUP_VAL_IDX_EXCEP_FLAG = 'A') then
2208 into hz_imp_errors (
2209 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name,
2210 token1_name, token1_value, token2_name, token2_value)
2211 values (
2212 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_PARTIES_INT',
2213 'HZ_IMP_DUP_VAL', 'INDEX', 'HZ_PARTIES_U1', 'ENTITY', 'HZ_PARTIES')
2214 when (DUP_VAL_IDX_EXCEP_FLAG = 'B') then
2215 into hz_imp_errors (
2216 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name,
2217 token1_name, token1_value, token2_name, token2_value)
2218 values (
2219 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_PARTIES_INT',
2220 'HZ_IMP_DUP_VAL', 'INDEX', 'HZ_PARTIES_U2', 'ENTITY', 'HZ_PARTIES')
2221 when (ACTION_MISMATCH_FLAG is null) then
2222 into hz_imp_errors (
2223 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name)
2224 values (
2225 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_PARTIES_INT',
2226 'HZ_IMP_ACTION_MISMATCH')
2227 select /*+ leading(e) use_nl(int) rowid(int) */ e.creation_date, e.created_by, e.last_update_date, e.last_updated_by, e.last_update_login, e.program_application_id,
2228 e.program_id, e.program_update_date, e.error_id, e.batch_id, e.request_id, e.interface_table_name, E1_FLAG, E2_FLAG,
2229 E3_FLAG, E4_FLAG, E5_FLAG, E6_FLAG, E7_FLAG, E8_FLAG, E9_FLAG,
2230 E10_FLAG, E11_FLAG, E12_FLAG, E13_FLAG, E14_FLAG, E15_FLAG,
2231 E16_FLAG, E17_FLAG, E18_FLAG, E19_FLAG, E20_FLAG, E21_FLAG,
2232 E22_FLAG, E23_FLAG, E24_FLAG, E25_FLAG, E26_FLAG, E27_FLAG,
2233 E28_FLAG, E29_FLAG, E30_FLAG, E31_FLAG, E32_FLAG, E33_FLAG,
2234 E34_FLAG, E35_FLAG, E36_FLAG, E37_FLAG, E38_FLAG, E39_FLAG,
2235 DUP_VAL_IDX_EXCEP_FLAG, ACTION_MISMATCH_FLAG,
2236 INT.DATE_OF_BIRTH, INT.DATE_OF_DEATH, FND_GLOBAL.USER_NAME
2237 from hz_imp_tmp_errors e,
2238 HZ_IMP_PARTIES_INT int
2239 where e.batch_id = P_BATCH_ID
2240 and e.request_id = P_REQUEST_ID
2241 and e.int_row_id = int.rowid
2242 and e.interface_table_name = 'HZ_IMP_PARTIES_INT';
2243
2244 insert all
2245 when (E1_FLAG is null) then
2246 into hz_imp_errors (
2247 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name,
2248 token1_name, token1_value)
2249 values (
2250 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_RELSHIPS_INT',
2251 'HZ_IMP_REL_SUBJ_OBJ_ERROR', 'SUB_OR_OBJ', 'SUBJECT')
2252 when (E2_FLAG is null) then
2253 into hz_imp_errors (
2254 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name,
2255 token1_name, token1_value)
2256 values (
2257 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_RELSHIPS_INT',
2258 'HZ_IMP_REL_SUBJ_OBJ_ERROR', 'SUB_OR_OBJ', 'OBJECT')
2259 when (E3_FLAG is null) then
2260 into hz_imp_errors (
2261 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name)
2262 values (
2263 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_RELSHIPS_INT',
2264 'HZ_IMP_REL_TYPE_ERROR')
2265 when (E4_FLAG is null) then
2266 into hz_imp_errors (
2267 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name,
2268 token1_name, token1_value, token2_name, token2_value)
2269 values (
2270 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_RELSHIPS_INT',
2271 'HZ_API_INVALID_LOOKUP', 'COLUMN', 'RELATIONSHIP_CODE',
2272 'LOOKUP_TYPE', 'PARTY_RELATIONS_TYPE')
2273 when (E5_FLAG is null) then
2274 into hz_imp_errors (
2275 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name,
2276 token1_name, token1_value, token2_name, token2_value)
2277 values (
2278 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_RELSHIPS_INT',
2279 'HZ_API_DATE_GREATER', 'DATE2', 'END_DATE', 'DATE1', 'START_DATE')
2280 when (E6_FLAG is null) then
2281 into hz_imp_errors (
2282 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name)
2283 values (
2284 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_RELSHIPS_INT',
2285 'HZ_IMP_HIERARCHICAL_FLAG_ERROR')
2286 when (E7_FLAG is null) then
2287 into hz_imp_errors (
2288 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name)
2289 values (
2290 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_RELSHIPS_INT',
2291 'HZ_API_SUBJECT_OBJECT_IDS')
2292 when (E8_FLAG is null) then
2293 into hz_imp_errors (
2294 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name)
2295 values (
2296 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_RELSHIPS_INT',
2297 'HZ_RELATIONSHIP_DATE_OVERLAP')
2298 when (E9_FLAG is null) then
2299 into hz_imp_errors (
2300 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name,
2301 token1_name, token1_value)
2302 values (
2303 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_RELSHIPS_INT',
2304 'AR_RAPI_DESC_FLEX_INVALID', 'DFF_NAME', 'HZ_RELATIONSHIPS')
2305 when (E10_FLAG is null) then
2306 into hz_imp_errors (
2307 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name,
2308 token1_name, token1_value)
2309 values (
2310 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_RELSHIPS_INT',
2311 'HZ_DSS_NO_UPDATE_PRIVILEGE', 'ENTITY_NAME', l_dss_rel_err)
2312 when (E11_FLAG is null) then
2313 into hz_imp_errors (
2314 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name,
2315 token1_name, token1_value, token2_name, token2_value)
2316 values (
2317 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_RELSHIPS_INT',
2318 'HZ_API_INVALID_LOOKUP', 'COLUMN', 'CREATED_BY_MODULE', 'LOOKUP_TYPE',
2319 'HZ_CREATED_BY_MODULES')
2320 when (DUP_VAL_IDX_EXCEP_FLAG = 'A') then
2321 into hz_imp_errors (
2322 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name,
2323 token1_name, token1_value, token2_name, token2_value)
2324 values (
2325 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_RELSHIPS_INT',
2326 'HZ_IMP_DUP_VAL', 'INDEX', 'HZ_RELATIONSHIPS_U1', 'ENTITY',
2327 'HZ_RELATIONSHIPS')
2328 when (ACTION_MISMATCH_FLAG is null) then
2329 into hz_imp_errors (
2330 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name)
2331 values (
2332 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_RELSHIPS_INT',
2333 'HZ_IMP_ACTION_MISMATCH')
2334 select /*+ leading(e) use_nl(int) rowid(int) */ e.creation_date, e.created_by, e.last_update_date, e.last_updated_by, e.last_update_login, e.program_application_id,
2335 e.program_id, e.program_update_date, e.error_id, e.batch_id, e.request_id, e.interface_table_name, E1_FLAG, E2_FLAG,
2336 E3_FLAG, E4_FLAG, E5_FLAG, E6_FLAG, E7_FLAG, E8_FLAG, E9_FLAG,
2337 E10_FLAG, E11_FLAG, DUP_VAL_IDX_EXCEP_FLAG, ACTION_MISMATCH_FLAG,
2338 FND_GLOBAL.USER_NAME, INT.START_DATE, INT.END_DATE
2339 from hz_imp_tmp_errors e,
2340 HZ_IMP_RELSHIPS_INT int
2341 where e.batch_id = P_BATCH_ID
2342 and e.request_id = P_REQUEST_ID
2343 and e.int_row_id = int.rowid
2344 and e.interface_table_name = 'HZ_IMP_RELSHIPS_INT';
2345
2346 insert all
2347 when (E1_FLAG is null) then
2348 into hz_imp_errors (
2349 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name,
2350 token1_name, token1_value)
2351 values (
2352 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_CONTACTS_INT',
2353 'HZ_IMP_REL_SUBJ_OBJ_ERROR', 'SUB_OR_OBJ', 'SUBJECT')
2354 when (E2_FLAG is null) then
2355 into hz_imp_errors (
2356 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name,
2357 token1_name, token1_value)
2358 values (
2359 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_CONTACTS_INT',
2360 'HZ_IMP_REL_SUBJ_OBJ_ERROR', 'SUB_OR_OBJ', 'OBJECT')
2361 when (E3_FLAG is null) then
2362 into hz_imp_errors (
2363 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name,
2364 token1_name, token1_value, token2_name, token2_value)
2365 values (
2366 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_CONTACTS_INT',
2367 'HZ_API_PARTY_NOT_PERSON', 'TABLE_NAME', 'HZ_IMP_CONTACTS_INT',
2368 'PARTY_ID_COL', SUB_ORIG_SYSTEM_REFERENCE)
2369 when (E4_FLAG is null) then
2370 into hz_imp_errors (
2371 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name,
2372 token1_name, token1_value, token2_name, token2_value)
2373 values (
2374 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_CONTACTS_INT',
2375 'HZ_API_PARTY_NOT_ORG', 'TABLE_NAME', 'HZ_IMP_CONTACTS_INT',
2376 'PARTY_ID_COL', OBJ_ORIG_SYSTEM_REFERENCE)
2377 when (E5_FLAG is null) then
2378 into hz_imp_errors (
2379 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name,
2380 token1_name, token1_value, token2_name, token2_value)
2381 values (
2382 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_CONTACTS_INT',
2383 'HZ_API_INVALID_LOOKUP', 'COLUMN', 'RELATIONSHIP_CODE',
2384 'LOOKUP_TYPE', 'PARTY_RELATIONS_TYPE')
2385 when (E6_FLAG is null) then
2386 into hz_imp_errors (
2387 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name,
2388 token1_name, token1_value, token2_name, token2_value)
2389 values (
2390 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_CONTACTS_INT',
2391 'HZ_API_INVALID_LOOKUP', 'COLUMN', 'DEPARTMENT_CODE', 'LOOKUP_TYPE',
2392 'DEPARTMENT_CODE')
2393 when (E7_FLAG is null) then
2394 into hz_imp_errors (
2395 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name,
2396 token1_name, token1_value, token2_name, token2_value)
2397 values (
2398 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_CONTACTS_INT',
2399 'HZ_API_INVALID_LOOKUP', 'COLUMN', 'TITLE', 'LOOKUP_TYPE',
2400 'CONTACT_TITLE')
2401 when (E8_FLAG is null) then
2402 into hz_imp_errors (
2403 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name,
2404 token1_name, token1_value, token2_name, token2_value)
2405 values (
2406 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_CONTACTS_INT',
2407 'HZ_API_INVALID_LOOKUP', 'COLUMN', 'JOB_TITLE_CODE', 'LOOKUP_TYPE',
2408 'RESPONSIBILITY')
2409 when (E9_FLAG is null) then
2410 into hz_imp_errors (
2411 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name,
2412 token1_name, token1_value, token2_name, token2_value)
2413 values (
2414 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_CONTACTS_INT',
2415 'HZ_API_INVALID_LOOKUP', 'COLUMN', 'DECISION_MAKER_FLAG',
2416 'LOOKUP_TYPE', 'YES/NO')
2417 when (E10_FLAG is null) then
2418 into hz_imp_errors (
2419 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name,
2420 token1_name, token1_value, token2_name, token2_value)
2421 values (
2422 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_CONTACTS_INT',
2423 'HZ_API_INVALID_LOOKUP', 'COLUMN', 'REFERENCE_USE_FLAG',
2424 'LOOKUP_TYPE', 'YES/NO')
2425 when (E11_FLAG is null) then
2426 into hz_imp_errors (
2427 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name,
2428 token1_name, token1_value, token2_name, token2_value)
2429 values (
2430 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_CONTACTS_INT',
2431 'HZ_API_DATE_GREATER', 'DATE2', 'END_DATE', 'DATE1', 'START_DATE')
2432 when (E12_FLAG is null) then
2433 into hz_imp_errors (
2434 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name,
2435 token1_name, token1_value)
2436 values (
2437 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_CONTACTS_INT',
2438 'AR_RAPI_DESC_FLEX_INVALID', 'DFF_NAME', 'HZ_ORG_CONTACTS')
2439 when (E13_FLAG is null) then
2440 into hz_imp_errors (
2441 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name)
2442 values (
2443 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_CONTACTS_INT',
2444 'HZ_IMP_HIERARCHICAL_FLAG_ERROR')
2445 when (E14_FLAG is null) then
2446 into hz_imp_errors (
2447 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name)
2448 values (
2449 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_CONTACTS_INT', 'HZ_IMP_DUP_REL_IN_INT_ERROR')
2450 when (E15_FLAG is null) then
2451 into hz_imp_errors (
2452 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name,
2453 token1_name, token1_value)
2454 values (
2455 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_CONTACTS_INT',
2456 'HZ_DSS_NO_UPDATE_PRIVILEGE', 'ENTITY_NAME', l_dss_rel_err)
2457 when (E16_FLAG is null) then
2458 into hz_imp_errors (
2459 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name,
2460 token1_name, token1_value)
2461 values (
2462 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_CONTACTS_INT',
2463 'HZ_API_NONUPDATEABLE_TO_NULL', 'COLUMN', 'START_DATE')
2464 -- Bug 4156586
2465 when (E17_FLAG is null) then
2466 into hz_imp_errors (
2467 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name)
2468 values (
2469 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_CONTACTS_INT', 'HZ_API_SUBJECT_OBJECT_IDS')
2470 when (E18_FLAG is null) then
2471 into hz_imp_errors (
2472 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name,
2473 token1_name, token1_value, token2_name, token2_value)
2474 values (
2475 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_CONTACTS_INT',
2476 'HZ_API_INVALID_LOOKUP', 'COLUMN', 'CREATED_BY_MODULE', 'LOOKUP_TYPE',
2477 'HZ_CREATED_BY_MODULES')
2478 when (DUP_VAL_IDX_EXCEP_FLAG = 'A') then
2479 into hz_imp_errors (
2480 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name,
2481 token1_name, token1_value, token2_name, token2_value)
2482 values (
2483 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_CONTACTS_INT',
2484 'HZ_IMP_DUP_VAL', 'INDEX', 'HZ_ORG_CONTACTS_U1', 'ENTITY',
2485 'HZ_ORG_CONTACTS')
2486 when (ACTION_MISMATCH_FLAG is null) then
2487 into hz_imp_errors (
2488 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name)
2489 values (
2490 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_CONTACTS_INT',
2491 'HZ_IMP_ACTION_MISMATCH')
2492 select /*+ leading(e) use_nl(int) rowid(int) */ e.creation_date, e.created_by, e.last_update_date, e.last_updated_by, e.last_update_login, e.program_application_id,
2493 e.program_id, e.program_update_date, e.error_id, e.batch_id, e.request_id, e.interface_table_name, E1_FLAG, E2_FLAG,
2494 E3_FLAG, E4_FLAG, E5_FLAG, E6_FLAG, E7_FLAG, E8_FLAG, E9_FLAG,
2495 E10_FLAG, E11_FLAG, E12_FLAG, E13_FLAG, E14_FLAG, E15_FLAG,
2496 E16_FLAG, E17_FLAG , /* Bug 4156586 */
2497 E18_FLAG, DUP_VAL_IDX_EXCEP_FLAG, ACTION_MISMATCH_FLAG,
2498 FND_GLOBAL.USER_NAME, INT.SUB_ORIG_SYSTEM_REFERENCE,
2499 INT.OBJ_ORIG_SYSTEM_REFERENCE, INT.END_DATE, INT.START_DATE
2500 from hz_imp_tmp_errors e,
2501 HZ_IMP_CONTACTS_INT int
2502 where e.batch_id = P_BATCH_ID
2503 and e.request_id = P_REQUEST_ID
2504 and e.int_row_id = int.rowid
2505 and e.interface_table_name = 'HZ_IMP_CONTACTS_INT';
2506
2507 insert all
2508 when (E2_FLAG is null) then
2509 into hz_imp_errors (
2510 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name,
2511 token1_name, token1_value, token2_name, token2_value, token3_name,
2512 token3_value)
2513 values (
2514 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_CLASSIFICS_INT',
2515 'HZ_API_INVALID_FK', 'FK', 'CLASS_CATEGORY', 'COLUMN',
2516 'CLASS_CATEGORY', 'TABLE', 'HZ_CLASS_CATEGORIES')
2517 when (E3_FLAG is null) then
2518 into hz_imp_errors (
2519 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name,
2520 token1_name, token1_value, token2_name, token2_value)
2521 values (
2522 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_CLASSIFICS_INT',
2523 'HZ_API_INVALID_LOOKUP', 'COLUMN', 'CLASS_CODE', 'LOOKUP_TYPE',
2524 'HZ_IMP_CLASSIFICS_INT.CLASS_CATEGORY')
2525 when (E4_FLAG is null) then
2526 into hz_imp_errors (
2527 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name,
2528 token1_name, token1_value, token2_name, token2_value)
2529 values (
2530 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_CLASSIFICS_INT',
2531 'HZ_API_DATE_GREATER', 'DATE2', 'END_DATE_ACTIVE', 'DATE1',
2532 'START_DATE_ACTIVE')
2533 when (E5_FLAG is null) then
2534 into hz_imp_errors (
2535 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name)
2536 values (
2537 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_CLASSIFICS_INT',
2538 'HZ_IMP_CODE_ASSG_DATE_OVERLAP')
2539 when (E6_FLAG is null) then
2540 into hz_imp_errors (
2541 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name)
2542 values (
2543 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_CLASSIFICS_INT',
2544 'HZ_API_ALLOW_MUL_ASSIGN_FG')
2545 when (E7_FLAG is null) then
2546 into hz_imp_errors (
2547 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name)
2548 values (
2549 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_CLASSIFICS_INT',
2550 'HZ_API_LEAFNODE_FLAG')
2551 when (E8_FLAG is null) then
2552 into hz_imp_errors (
2553 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name,
2554 token1_name, token1_value)
2555 values (
2556 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_CLASSIFICS_INT',
2557 'HZ_API_NONUPDATEABLE_TO_NULL', 'COLUMN', 'START_DATE')
2558 when (E9_FLAG is null) then
2559 into hz_imp_errors (
2560 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name,
2561 token1_name, token1_value)
2562 values (
2563 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_CLASSIFICS_INT',
2564 'HZ_DSS_NO_UPDATE_PRIVILEGE', 'ENTITY_NAME', l_dss_ca_err)
2565 when (E10_FLAG is null) then
2566 into hz_imp_errors (
2567 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name,
2568 token1_name, token1_value, token2_name, token2_value)
2569 values (
2570 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_CLASSIFICS_INT',
2571 'HZ_API_INVALID_LOOKUP', 'COLUMN', 'CREATED_BY_MODULE', 'LOOKUP_TYPE',
2572 'HZ_CREATED_BY_MODULES')
2573 when (DUP_VAL_IDX_EXCEP_FLAG = 'A') then
2574 into hz_imp_errors (
2575 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name,
2576 token1_name, token1_value, token2_name, token2_value)
2577 values (
2578 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_CLASSIFICS_INT',
2579 'HZ_IMP_DUP_VAL', 'INDEX', 'HZ_CODE_ASSIGNMENTS_U1', 'ENTITY',
2580 'HZ_CODE_ASSIGNMENTS')
2581 when (DUP_VAL_IDX_EXCEP_FLAG = 'B') then
2582 into hz_imp_errors (
2583 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name,
2584 token1_name, token1_value, token2_name, token2_value)
2585 values (
2586 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_CLASSIFICS_INT',
2587 'HZ_IMP_DUP_VAL', 'INDEX', 'HZ_CODE_ASSIGNMENTS_U2', 'ENTITY',
2588 'HZ_CODE_ASSIGNMENTS')
2589 when (ACTION_MISMATCH_FLAG is null) then
2590 into hz_imp_errors (
2591 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name)
2592 values (
2593 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_CLASSIFICS_INT',
2594 'HZ_IMP_ACTION_MISMATCH')
2595 when (MISSING_PARENT_FLAG is null) then -- Bug 4403736
2596 into hz_imp_errors (
2597 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name)
2598 values (
2599 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_CLASSIFICS_INT',
2600 'HZ_IMP_PARENT_PARTY_NOT_FOUND')
2601 select /*+ leading(e) use_nl(int) rowid(int) */ e.creation_date, e.created_by, e.last_update_date, e.last_updated_by, e.last_update_login, e.program_application_id,
2602 e.program_id, e.program_update_date, e.error_id, e.batch_id, e.request_id, e.interface_table_name, E2_FLAG, E3_FLAG,
2603 E4_FLAG, E5_FLAG, E6_FLAG, E7_FLAG, E8_FLAG, E9_FLAG, E10_FLAG,
2604 DUP_VAL_IDX_EXCEP_FLAG, ACTION_MISMATCH_FLAG, MISSING_PARENT_FLAG, FND_GLOBAL.USER_NAME,
2605 INT.END_DATE_ACTIVE, INT.START_DATE_ACTIVE
2606 from hz_imp_tmp_errors e,
2607 HZ_IMP_CLASSIFICS_INT int
2608 where e.batch_id = P_BATCH_ID
2609 and e.request_id = P_REQUEST_ID
2610 and e.int_row_id = int.rowid
2611 and e.interface_table_name = 'HZ_IMP_CLASSIFICS_INT';
2612
2613 insert all
2614 when (E1_FLAG is null) then
2615 into hz_imp_errors (
2616 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name,
2617 token1_name, token1_value, token2_name, token2_value)
2618 values (
2619 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_CREDITRTNGS_INT',
2620 'HZ_API_INVALID_LOOKUP', 'COLUMN', 'BANKRUPTCY_IND', 'LOOKUP_TYPE',
2621 'YES/NO')
2622 when (E2_FLAG is null) then
2623 into hz_imp_errors (
2624 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name,
2625 token1_name, token1_value, token2_name, token2_value)
2626 values (
2627 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_CREDITRTNGS_INT',
2628 'HZ_API_INVALID_LOOKUP', 'COLUMN', 'SUIT_IND', 'LOOKUP_TYPE', 'YES/NO')
2629 when (E4_FLAG is null) then
2630 into hz_imp_errors (
2631 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name,
2632 token1_name, token1_value, token2_name, token2_value)
2633 values (
2634 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_CREDITRTNGS_INT',
2635 'HZ_API_INVALID_LOOKUP', 'COLUMN', 'DEBARMENT_IND', 'LOOKUP_TYPE',
2636 'YES/NO')
2637 when (E5_FLAG is null) then
2638 into hz_imp_errors (
2639 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name,
2640 token1_name, token1_value, token2_name,
2641 token2_value)
2642 values (
2643 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_CREDITRTNGS_INT',
2644 'HZ_API_INVALID_LOOKUP', 'COLUMN', 'FINCL_EMBT_IND', 'LOOKUP_TYPE',
2645 'YES/NO')
2646 when (E6_FLAG is null) then
2647 into hz_imp_errors (
2648 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name,
2649 token1_name, token1_value, token2_name, token2_value)
2650 values (
2651 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_CREDITRTNGS_INT',
2652 'HZ_API_INVALID_LOOKUP', 'COLUMN', 'NO_TRADE_IND', 'LOOKUP_TYPE',
2653 'YES/NO')
2654 when (E7_FLAG is null) then
2655 into hz_imp_errors (
2656 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name,
2657 token1_name, token1_value, token2_name, token2_value)
2658 values (
2659 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_CREDITRTNGS_INT',
2660 'HZ_API_INVALID_LOOKUP', 'COLUMN', 'JUDGEMENT_IND', 'LOOKUP_TYPE',
2661 'YES/NO')
2662 when (E8_FLAG is null) then
2663 into hz_imp_errors (
2664 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name,
2665 token1_name, token1_value, token2_name, token2_value)
2666 values (
2667 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_CREDITRTNGS_INT',
2668 'HZ_API_INVALID_LOOKUP', 'COLUMN', 'LIEN_IND', 'LOOKUP_TYPE', 'YES/NO')
2669 when (E9_FLAG is null) then
2670 into hz_imp_errors (
2671 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name,
2672 token1_name, token1_value, token2_name, token2_value)
2673 values (
2674 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_CREDITRTNGS_INT',
2675 'HZ_API_INVALID_LOOKUP', 'COLUMN', 'CREDIT_SCORE_OVERRIDE_CODE',
2676 'LOOKUP_TYPE', 'FAILURE_SCORE_OVERRIDE_CODE')
2677 when (E10_FLAG is null) then
2678 into hz_imp_errors (
2679 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name,
2680 token1_name, token1_value, token2_name, token2_value)
2681 values (
2682 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_CREDITRTNGS_INT',
2683 'HZ_API_INVALID_LOOKUP', 'COLUMN', 'FAILURE_SCORE_COMMENTARY',
2684 'LOOKUP_TYPE', 'FAILURE_SCORE_COMMENTARY')
2685 when (E11_FLAG is null) then
2686 into hz_imp_errors (
2687 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name,
2688 token1_name, token1_value, token2_name, token2_value)
2689 values (
2690 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_CREDITRTNGS_INT',
2691 'HZ_API_INVALID_LOOKUP', 'COLUMN', 'FAILURE_SCORE_COMMENTARY2',
2692 'LOOKUP_TYPE', 'FAILURE_SCORE_COMMENTARY')
2693 when (E12_FLAG is null) then
2694 into hz_imp_errors (
2695 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name,
2696 token1_name, token1_value, token2_name, token2_value)
2697 values (
2698 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_CREDITRTNGS_INT',
2699 'HZ_API_INVALID_LOOKUP', 'COLUMN', 'FAILURE_SCORE_COMMENTARY3',
2700 'LOOKUP_TYPE', 'FAILURE_SCORE_COMMENTARY')
2701 when (E13_FLAG is null) then
2702 into hz_imp_errors (
2703 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name,
2704 token1_name, token1_value, token2_name, token2_value)
2705 values (
2706 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_CREDITRTNGS_INT',
2707 'HZ_API_INVALID_LOOKUP', 'COLUMN', 'FAILURE_SCORE_COMMENTARY4',
2708 'LOOKUP_TYPE', 'FAILURE_SCORE_COMMENTARY')
2709 when (E14_FLAG is null) then
2710 into hz_imp_errors (
2711 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name,
2712 token1_name, token1_value, token2_name, token2_value)
2713 values (
2714 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_CREDITRTNGS_INT',
2715 'HZ_API_INVALID_LOOKUP', 'COLUMN', 'FAILURE_SCORE_COMMENTARY5',
2716 'LOOKUP_TYPE', 'FAILURE_SCORE_COMMENTARY')
2717 when (E15_FLAG is null) then
2718 into hz_imp_errors (
2719 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name,
2720 token1_name, token1_value, token2_name, token2_value)
2721 values (
2722 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_CREDITRTNGS_INT',
2723 'HZ_API_INVALID_LOOKUP', 'COLUMN', 'FAILURE_SCORE_COMMENTARY6',
2724 'LOOKUP_TYPE', 'FAILURE_SCORE_COMMENTARY')
2725 when (E16_FLAG is null) then
2726 into hz_imp_errors (
2727 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name,
2728 token1_name, token1_value, token2_name, token2_value)
2729 values (
2730 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_CREDITRTNGS_INT',
2731 'HZ_API_INVALID_LOOKUP', 'COLUMN', 'FAILURE_SCORE_COMMENTARY7',
2732 'LOOKUP_TYPE', 'FAILURE_SCORE_COMMENTARY')
2733 when (E17_FLAG is null) then
2734 into hz_imp_errors (
2735 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name,
2736 token1_name, token1_value, token2_name, token2_value)
2737 values (
2738 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_CREDITRTNGS_INT',
2739 'HZ_API_INVALID_LOOKUP', 'COLUMN', 'FAILURE_SCORE_COMMENTARY8',
2740 'LOOKUP_TYPE', 'FAILURE_SCORE_COMMENTARY')
2741 when (E18_FLAG is null) then
2742 into hz_imp_errors (
2743 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name,
2744 token1_name, token1_value, token2_name, token2_value)
2745 values (
2746 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_CREDITRTNGS_INT',
2747 'HZ_API_INVALID_LOOKUP', 'COLUMN', 'FAILURE_SCORE_COMMENTARY9',
2748 'LOOKUP_TYPE', 'FAILURE_SCORE_COMMENTARY')
2749 when (E19_FLAG is null) then
2750 into hz_imp_errors (
2751 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name,
2752 token1_name, token1_value, token2_name, token2_value)
2753 values (
2754 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_CREDITRTNGS_INT',
2755 'HZ_API_INVALID_LOOKUP', 'COLUMN', 'FAILURE_SCORE_COMMENTARY10',
2756 'LOOKUP_TYPE', 'FAILURE_SCORE_COMMENTARY')
2757 when (E20_FLAG is null) then
2758 into hz_imp_errors (
2759 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name,
2760 token1_name, token1_value, token2_name, token2_value)
2761 values (
2762 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_CREDITRTNGS_INT',
2763 'HZ_API_INVALID_LOOKUP', 'COLUMN', 'FAILURE_SCORE_OVERRIDE_CODE',
2764 'LOOKUP_TYPE', 'FAILURE_SCORE_OVERRIDE_CODE')
2765 when (E21_FLAG is null) then
2766 into hz_imp_errors (
2767 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name,
2768 token1_name, token1_value, token2_name, token2_value)
2769 values (
2770 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_CREDITRTNGS_INT',
2771 'HZ_API_INVALID_LOOKUP', 'COLUMN', 'CREDIT_SCORE_COMMENTARY',
2772 'LOOKUP_TYPE', 'CREDIT_SCORE_COMMENTARY')
2773 when (E22_FLAG is null) then
2774 into hz_imp_errors (
2775 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name,
2776 token1_name, token1_value, token2_name, token2_value)
2777 values (
2778 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_CREDITRTNGS_INT',
2779 'HZ_API_INVALID_LOOKUP', 'COLUMN', 'CREDIT_SCORE_COMMENTARY2',
2780 'LOOKUP_TYPE', 'CREDIT_SCORE_COMMENTARY')
2781 when (E23_FLAG is null) then
2782 into hz_imp_errors (
2783 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name,
2784 token1_name, token1_value, token2_name, token2_value)
2785 values (
2786 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_CREDITRTNGS_INT',
2787 'HZ_API_INVALID_LOOKUP', 'COLUMN', 'CREDIT_SCORE_COMMENTARY3',
2788 'LOOKUP_TYPE', 'CREDIT_SCORE_COMMENTARY')
2789 when (E24_FLAG is null) then
2790 into hz_imp_errors (
2791 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name,
2792 token1_name, token1_value, token2_name, token2_value)
2793 values (
2794 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_CREDITRTNGS_INT',
2795 'HZ_API_INVALID_LOOKUP', 'COLUMN', 'CREDIT_SCORE_COMMENTARY4',
2796 'LOOKUP_TYPE', 'CREDIT_SCORE_COMMENTARY')
2797 when (E25_FLAG is null) then
2798 into hz_imp_errors (
2799 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name,
2800 token1_name, token1_value, token2_name, token2_value)
2801 values (
2802 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_CREDITRTNGS_INT',
2803 'HZ_API_INVALID_LOOKUP', 'COLUMN', 'CREDIT_SCORE_COMMENTARY5',
2804 'LOOKUP_TYPE', 'CREDIT_SCORE_COMMENTARY')
2805 when (E26_FLAG is null) then
2806 into hz_imp_errors (
2807 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name,
2808 token1_name, token1_value, token2_name, token2_value)
2809 values (
2810 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_CREDITRTNGS_INT',
2811 'HZ_API_INVALID_LOOKUP', 'COLUMN', 'CREDIT_SCORE_COMMENTARY6',
2812 'LOOKUP_TYPE', 'CREDIT_SCORE_COMMENTARY')
2813 when (E27_FLAG is null) then
2814 into hz_imp_errors (
2815 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name,
2816 token1_name, token1_value, token2_name, token2_value)
2817 values (
2818 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_CREDITRTNGS_INT',
2819 'HZ_API_INVALID_LOOKUP', 'COLUMN', 'CREDIT_SCORE_COMMENTARY7',
2820 'LOOKUP_TYPE', 'CREDIT_SCORE_COMMENTARY')
2821 when (E28_FLAG is null) then
2822 into hz_imp_errors (
2823 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name,
2824 token1_name, token1_value, token2_name, token2_value)
2825 values (
2826 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_CREDITRTNGS_INT',
2827 'HZ_API_INVALID_LOOKUP', 'COLUMN', 'CREDIT_SCORE_COMMENTARY8',
2828 'LOOKUP_TYPE', 'CREDIT_SCORE_COMMENTARY')
2829 when (E29_FLAG is null) then
2830 into hz_imp_errors (
2831 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name,
2832 token1_name, token1_value, token2_name, token2_value)
2833 values (
2834 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_CREDITRTNGS_INT',
2835 'HZ_API_INVALID_LOOKUP', 'COLUMN', 'CREDIT_SCORE_COMMENTARY9',
2836 'LOOKUP_TYPE', 'CREDIT_SCORE_COMMENTARY')
2837 when (E30_FLAG is null) then
2838 into hz_imp_errors (
2839 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name,
2840 token1_name, token1_value, token2_name, token2_value)
2841 values (
2842 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_CREDITRTNGS_INT',
2843 'HZ_API_INVALID_LOOKUP', 'COLUMN', 'CREDIT_SCORE_COMMENTARY10',
2844 'LOOKUP_TYPE', 'CREDIT_SCORE_COMMENTARY')
2845 when (E31_FLAG is null) then
2846 into hz_imp_errors (
2847 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name,
2848 token1_name, token1_value, token2_name, token2_value)
2849 values (
2850 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_CREDITRTNGS_INT',
2851 'HZ_API_INVALID_LOOKUP', 'COLUMN', 'PRNT_HQ_BKCY_IND',
2852 'LOOKUP_TYPE', 'PRNT_HQ_IND')
2853 when (E32_FLAG is null) then
2854 into hz_imp_errors (
2855 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name,
2856 token1_name, token1_value, token2_name, token2_value, token3_name,
2857 token3_value)
2858 values (
2859 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_CREDITRTNGS_INT',
2860 'HZ_API_INVALID_FK', 'FK', 'CURRENCY_CODE', 'COLUMN',
2861 'MAXIMUM_CREDIT_CURRENCY_CODE', 'TABLE', 'FND_CURRENCIES')
2862 when (E33_FLAG is null) then
2863 into hz_imp_errors (
2864 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name,
2865 token1_name, token1_value, token2_name, token2_value)
2866 values (
2867 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_CREDITRTNGS_INT',
2868 'HZ_API_INVALID_LOOKUP', 'COLUMN', 'CREATED_BY_MODULE', 'LOOKUP_TYPE',
2869 'HZ_CREATED_BY_MODULES')
2870 when (DUP_VAL_IDX_EXCEP_FLAG = 'A') then
2871 into hz_imp_errors (
2872 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name,
2873 token1_name, token1_value, token2_name, token2_value)
2874 values (
2875 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_CREDITRTNGS_INT',
2876 'HZ_IMP_DUP_VAL', 'INDEX', 'HZ_CREDIT_RATINGS_U1', 'ENTITY',
2877 'HZ_CREDIT_RATINGS')
2878 when (DUP_VAL_IDX_EXCEP_FLAG = 'B') then
2879 into hz_imp_errors (
2880 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name,
2881 token1_name, token1_value, token2_name, token2_value)
2882 values (
2883 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_CREDITRTNGS_INT',
2884 'HZ_IMP_DUP_VAL', 'INDEX', 'HZ_CREDIT_RATINGS_U2', 'ENTITY',
2885 'HZ_CREDIT_RATINGS')
2886 when (ACTION_MISMATCH_FLAG is null) then
2887 into hz_imp_errors (
2888 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name)
2889 values (
2890 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_CREDITRTNGS_INT',
2891 'HZ_IMP_ACTION_MISMATCH')
2892 when (MISSING_PARENT_FLAG is null) then
2893 into hz_imp_errors (
2894 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name)
2895 values (
2896 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_CREDITRTNGS_INT',
2897 'HZ_IMP_PARENT_PARTY_NOT_FOUND')
2898 select creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, E1_FLAG, E2_FLAG,
2899
2900 E4_FLAG, E5_FLAG, E6_FLAG, E7_FLAG, E8_FLAG, E9_FLAG, E10_FLAG,
2901 E11_FLAG, E12_FLAG, E13_FLAG, E14_FLAG, E15_FLAG, E16_FLAG,
2902 E17_FLAG, E18_FLAG, E19_FLAG, E20_FLAG, E21_FLAG, E22_FLAG,
2903 E23_FLAG, E24_FLAG, E25_FLAG, E26_FLAG, E27_FLAG, E28_FLAG,
2904 E29_FLAG, E30_FLAG, E31_FLAG, E32_FLAG, E33_FLAG,
2905 DUP_VAL_IDX_EXCEP_FLAG,
2906 ACTION_MISMATCH_FLAG, MISSING_PARENT_FLAG
2907 from hz_imp_tmp_errors e
2908 where e.batch_id = P_BATCH_ID
2909 and e.request_id = P_REQUEST_ID
2910 and e.interface_table_name = 'HZ_IMP_CREDITRTNGS_INT';
2911
2912
2913 insert all
2914 when (E1_FLAG is null) then
2915 into hz_imp_errors (
2916 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name,
2917 token1_name, token1_value, token2_name, token2_value)
2918 values (
2919 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_ADDRESSUSES_INT',
2920 'HZ_API_INVALID_LOOKUP', 'COLUMN', 'SITE_USE_TYPE', 'LOOKUP_TYPE',
2921 'PARTY_SITE_USE_CODE')
2922 when (E2_FLAG is null) then
2923 into hz_imp_errors (
2924 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name)
2925 values (
2926 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_ADDRESSUSES_INT',
2927 'HZ_API_UNIQUE_SITE_USE_TYPE')
2928 when (E3_FLAG is null) then
2929 into hz_imp_errors (
2930 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name)
2931 values (
2932 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_ADDRESSUSES_INT',
2933 'HZ_IMP_ADDRUSE_OSR_MISMATCH')
2934 when (E4_FLAG is null) then
2935 into hz_imp_errors (
2936 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name,
2937 token1_name, token1_value, token2_name, token2_value)
2938 values (
2939 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_ADDRESSUSES_INT',
2940 'HZ_API_INVALID_LOOKUP', 'COLUMN', 'CREATED_BY_MODULE', 'LOOKUP_TYPE',
2941 'HZ_CREATED_BY_MODULES')
2942 when (DUP_VAL_IDX_EXCEP_FLAG = 'A') then
2943 into hz_imp_errors (
2944 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name,
2945 token1_name, token1_value, token2_name, token2_value)
2946 values (
2947 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_ADDRESSUSES_INT',
2948 'HZ_IMP_DUP_VAL', 'INDEX', 'HZ_PARTY_SITE_USES_U1', 'ENTITY',
2949 'HZ_PARTY_SITE_USES')
2950 when (ACTION_MISMATCH_FLAG is null) then
2951 into hz_imp_errors (
2952 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name)
2953 values (
2954 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_ADDRESSUSES_INT',
2955 'HZ_IMP_ACTION_MISMATCH')
2956 when (MISSING_PARENT_FLAG is null) then
2957 into hz_imp_errors (
2958 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name)
2959 values (
2960 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_ADDRESSUSES_INT',
2961 'HZ_IMP_ADDR_NOT_FOUND')
2962 select creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, E1_FLAG, E2_FLAG, E3_FLAG, E4_FLAG,
2963 DUP_VAL_IDX_EXCEP_FLAG, ACTION_MISMATCH_FLAG, MISSING_PARENT_FLAG
2964 from hz_imp_tmp_errors e
2965 where e.batch_id = P_BATCH_ID
2966 and e.request_id = P_REQUEST_ID
2967 and e.interface_table_name = 'HZ_IMP_ADDRESSUSES_INT';
2968
2969
2970 insert all
2971 when (E1_FLAG is null) then
2972 into hz_imp_errors (
2973 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name,
2974 token1_name, token1_value, token2_name, token2_value)
2975 values (
2976 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_CONTACTROLES_INT',
2977 'HZ_API_INVALID_LOOKUP', 'COLUMN', 'ROLE_TYPE', 'LOOKUP_TYPE',
2978 'CONTACT_ROLE_TYPE')
2979 when (E2_FLAG is null) then
2980 into hz_imp_errors (
2981 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name)
2982 values (
2983 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_CONTACTROLES_INT',
2984 'HZ_IMP_CONTROLE_OSR_MISMATCH')
2985 when (E3_FLAG is null) then
2986 into hz_imp_errors (
2987 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name,
2988 token1_name, token1_value, token2_name, token2_value)
2989 values (
2990 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_CONTACTROLES_INT',
2991 'HZ_API_INVALID_LOOKUP', 'COLUMN', 'CREATED_BY_MODULE', 'LOOKUP_TYPE',
2992 'HZ_CREATED_BY_MODULES')
2993 when (DUP_VAL_IDX_EXCEP_FLAG = 'A') then
2994 into hz_imp_errors (
2995 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name,
2996 token1_name, token1_value, token2_name, token2_value)
2997 values (
2998 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_CONTACTROLES_INT',
2999 'HZ_IMP_DUP_VAL', 'INDEX', 'HZ_ORG_CONTACT_ROLES_U1', 'ENTITY',
3000 'HZ_ORG_CONTACT_ROLES')
3001 when (DUP_VAL_IDX_EXCEP_FLAG = 'B') then
3002 into hz_imp_errors (
3003 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name,
3004 token1_name, token1_value, token2_name, token2_value)
3005 values (
3006 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_CONTACTROLES_INT',
3007 'HZ_IMP_DUP_VAL', 'INDEX', 'HZ_ORG_CONTACT_ROLES_U2', 'ENTITY',
3008 'HZ_ORG_CONTACT_ROLES')
3009 when (ACTION_MISMATCH_FLAG is null) then
3010 into hz_imp_errors (
3011 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name)
3012 values (
3013 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_CONTACTROLES_INT',
3014 'HZ_IMP_ACTION_MISMATCH')
3015 when (MISSING_PARENT_FLAG is null) then
3016 into hz_imp_errors (
3017 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name)
3018 values (
3019 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_CONTACTROLES_INT',
3020 'HZ_IMP_CONTACT_NOT_FOUND')
3021 select creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, E1_FLAG, E2_FLAG,
3022 E3_FLAG, DUP_VAL_IDX_EXCEP_FLAG, ACTION_MISMATCH_FLAG, MISSING_PARENT_FLAG
3023 from hz_imp_tmp_errors
3024 where batch_id = P_BATCH_ID
3025 and request_id = P_REQUEST_ID
3026 and interface_table_name = 'HZ_IMP_CONTACTROLES_INT';
3027
3028
3029
3030 insert all
3031 when (E1_FLAG is null) then
3032 into hz_imp_errors (
3033 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name,
3034 token1_name, token1_value, token2_name, token2_value)
3035 values (
3036 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_CONTACTPTS_INT',
3037 'HZ_API_INVALID_LOOKUP', 'COLUMN', 'CONTACT_POINT_TYPE',
3038 'LOOKUP_TYPE', 'CONTACT_POINT_TYPE')
3039 when (E2_FLAG is null) then
3040 into hz_imp_errors (
3041 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name,
3042 token1_name, token1_value, token2_name, token2_value)
3043 values (
3044 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_CONTACTPTS_INT',
3045 'HZ_API_INVALID_LOOKUP', 'COLUMN', 'CONTACT_POINT_PURPOSE',
3046 'LOOKUP_TYPE', 'CONTACT_POINT_PURPOSE')
3047 when (E3_FLAG is null) then
3048 into hz_imp_errors (
3049 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name,
3050 token1_name, token1_value)
3051 values (
3052 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_CONTACTPTS_INT',
3053 'HZ_API_MISSING_COLUMN', 'COLUMN', 'EDI_ID_NUMBER')
3054 when (E4_FLAG is null) then
3055 into hz_imp_errors (
3056 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name,
3057 token1_name, token1_value)
3058 values (
3059 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_CONTACTPTS_INT',
3060 'HZ_API_MISSING_COLUMN', 'COLUMN', 'EMAIL_ADDRESS')
3061 when (E5_FLAG is null) then
3062 into hz_imp_errors (
3063 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name,
3064 token1_name, token1_value, token2_name, token2_value)
3065 values (
3066 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_CONTACTPTS_INT',
3067 'HZ_API_INVALID_LOOKUP', 'COLUMN', 'EMAIL_FORMAT', 'LOOKUP_TYPE',
3068 'EMAIL_FORMAT')
3069 when (E6_FLAG is null) then
3070 into hz_imp_errors (
3071 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name,
3072 token1_name, token1_value, token2_name, token2_value, token3_name,
3073 token3_value)
3074 values (
3075 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_CONTACTPTS_INT',
3076 'HZ_API_INVALID_FK', 'FK', 'PHONE_COUNTRY_CODE', 'COLUMN',
3077 'PHONE_COUNTRY_CODE', 'TABLE', 'HZ_PHONE_COUNTRY_CODES')
3078 when (E7_FLAG is null) then
3079 into hz_imp_errors (
3080 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name,
3081 token1_value, token2_name, token2_value)
3082 values (
3083 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_CONTACTPTS_INT',
3084 'HZ_API_INVALID_LOOKUP', 'PHONE_LINE_TYPE', 'LOOKUP_TYPE',
3085 'PHONE_LINE_TYPE')
3086 when (E8_FLAG is null) then
3087 into hz_imp_errors (
3088 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name)
3089 values (
3090 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_CONTACTPTS_INT',
3091 'HZ_INVALID_PHONE_PARAMETER')
3092 when (E9_FLAG is null) then
3093 into hz_imp_errors (
3094 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name,
3095 token1_name, token1_value)
3096 values (
3097 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_CONTACTPTS_INT',
3098 'HZ_API_MISSING_COLUMN', 'COLUMN', 'TELEX_NUMBER')
3099 when (E10_FLAG is null) then
3100 into hz_imp_errors (
3101 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name,
3102 token1_name, token1_value, token2_name, token2_value, token3_name,
3103 token3_value)
3104 values (
3105 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_CONTACTPTS_INT',
3106 'HZ_API_INVALID_FK', 'FK', 'TIMEZONE_CODE', 'COLUMN',
3107 'TIMEZONE_CODE', 'TABLE', 'FND_TIMEZONES_B')
3108 when (E11_FLAG is null) then
3109 into hz_imp_errors (
3110 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name,
3111 token1_name, token1_value)
3112 values (
3113 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_CONTACTPTS_INT',
3114 'HZ_API_MISSING_COLUMN', 'COLUMN', 'URL')
3115 when (E12_FLAG is null) then
3116 into hz_imp_errors (
3117 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name,
3118 token1_name, token1_value)
3119 values (
3120 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_CONTACTPTS_INT',
3121 'HZ_API_MISSING_COLUMN', 'COLUMN', 'WEB_TYPE')
3122 when (E13_FLAG is null) then
3123 into hz_imp_errors (
3124 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name,
3125 token1_name, token1_value)
3126 values (
3127 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_CONTACTPTS_INT',
3128 'AR_RAPI_DESC_FLEX_INVALID', 'DFF_NAME', 'HZ_CONTACT_POINTS')
3129 when (E15_FLAG is null) then
3130 into hz_imp_errors (
3131 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name,
3132 token1_name, token1_value)
3133 values (
3134 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_CONTACTPTS_INT',
3135 'HZ_API_MISSING_COLUMN', 'COLUMN', 'CONTACT_POINT_TYPE')
3136 when (E16_FLAG is null) then
3137 into hz_imp_errors (
3138 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name)
3139 values (
3140 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_CONTACTPTS_INT',
3141 'HZ_IMP_CPT_ADDR_OSR_MISMATCH')
3142 when (E17_FLAG is null) then
3143 into hz_imp_errors (
3144 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name,
3145 token1_name, token1_value)
3146 values (
3147 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_CONTACTPTS_INT',
3148 'HZ_API_NONUPDATEABLE_COLUMN', 'COLUMN', 'CONTACT_POINT_TYPE')
3149 when (E18_FLAG='P') then
3150 into hz_imp_errors (
3151 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name,
3152 token1_name, token1_value)
3153 values (
3154 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_CONTACTPTS_INT',
3155 'HZ_DSS_NO_UPDATE_PRIVILEGE', 'ENTITY_NAME', l_dss_cp_err)
3156 when (E18_FLAG='S') then
3157 into hz_imp_errors (
3158 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name,
3159 token1_name, token1_value)
3160 values (
3161 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_CONTACTPTS_INT',
3162 'HZ_DSS_NO_UPDATE_PRIVILEGE', 'ENTITY_NAME', l_dss_ps_err)
3163 when (E19_FLAG is null) then
3164 into hz_imp_errors (
3165 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name,
3166 token1_name, token1_value, token2_name, token2_value)
3167 values (
3168 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_CONTACTPTS_INT',
3169 'HZ_API_INVALID_LOOKUP', 'COLUMN', 'CONTACT_POINT_PURPOSE',
3170 'LOOKUP_TYPE', 'CONTACT_POINT_PURPOSE_WEB')
3171 when (DUP_VAL_IDX_EXCEP_FLAG = 'A') then
3172 into hz_imp_errors (
3173 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name,
3174 token1_name, token1_value, token2_name, token2_value)
3175 values (
3176 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_CONTACTPTS_INT',
3177 'HZ_IMP_DUP_VAL', 'INDEX', 'HZ_CONTACT_POINTS_U1', 'ENTITY',
3178 'HZ_CONTACT_POINTS')
3179 when (ACTION_MISMATCH_FLAG is null) then
3180 into hz_imp_errors (
3181 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name)
3182 values (
3183 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_CONTACTPTS_INT',
3184 'HZ_IMP_ACTION_MISMATCH')
3185 when (MISSING_PARENT_FLAG = 'P') then
3186 into hz_imp_errors (
3187 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name)
3188 values (
3189 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_CONTACTPTS_INT',
3190 'HZ_IMP_PARENT_PARTY_NOT_FOUND')
3191 when (MISSING_PARENT_FLAG = 'A') then
3192 into hz_imp_errors (
3193 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name)
3194 values (
3195 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_CONTACTPTS_INT',
3196 'HZ_IMP_PARENT_ADDR_NOT_FOUND')
3197 when (E20_FLAG IS NULL) then /* Bug 4079902 */
3198 into hz_imp_errors (
3199 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name)
3200 values (
3201 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_CONTACTPTS_INT',
3202 'HZ_NOTALLOW_UPDATE_THIRD_PARTY')
3203 when (E21_FLAG is null) then
3204 into hz_imp_errors (
3205 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name,
3206 token1_name, token1_value, token2_name, token2_value)
3207 values (
3208 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_CONTACTPTS_INT',
3209 'HZ_API_INVALID_LOOKUP', 'COLUMN', 'CREATED_BY_MODULE', 'LOOKUP_TYPE',
3210 'HZ_CREATED_BY_MODULES')
3211 select creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, E1_FLAG, E2_FLAG,
3212 E3_FLAG, E4_FLAG, E5_FLAG, E6_FLAG, E7_FLAG, E8_FLAG, E9_FLAG,
3213 E10_FLAG, E11_FLAG, E12_FLAG, E13_FLAG, E15_FLAG,
3214 E16_FLAG, E17_FLAG, E18_FLAG, E19_FLAG, E20_FLAG /* Bug 4079902 */,
3215 E21_FLAG, DUP_VAL_IDX_EXCEP_FLAG,
3216 ACTION_MISMATCH_FLAG, MISSING_PARENT_FLAG
3217 from hz_imp_tmp_errors e
3218 where e.batch_id = P_BATCH_ID
3219 and request_id = P_REQUEST_ID
3220 and e.interface_table_name = 'HZ_IMP_CONTACTPTS_INT';
3221
3222 /* Update interface status of errored records to 'E' */
3223 OPEN c_err(P_BATCH_ID, P_REQUEST_ID);
3224 FETCH c_err BULK COLLECT INTO l_row_id, l_table_name, l_error_id;
3225 CLOSE c_err;
3226
3227 ForAll i in 1..l_row_id.count
3228 update hz_imp_parties_int
3229 set interface_status = 'E',
3230 error_id = l_error_id(i)
3231 where rowid = l_row_id(i)
3232 and l_table_name(i) = 'HZ_IMP_PARTIES_INT';
3233
3234 ForAll i in 1..l_row_id.count
3235 update hz_imp_addresses_int
3236 set interface_status = 'E',
3237 error_id = l_error_id(i)
3238 where rowid = l_row_id(i)
3239 and l_table_name(i) = 'HZ_IMP_ADDRESSES_INT';
3240
3241 ForAll i in 1..l_row_id.count
3242 update hz_imp_contactpts_int
3243 set interface_status = 'E',
3244 error_id = l_error_id(i)
3245 where rowid = l_row_id(i)
3246 and l_table_name(i) = 'HZ_IMP_CONTACTPTS_INT';
3247
3248 ForAll i in 1..l_row_id.count
3249 update hz_imp_contacts_int
3250 set interface_status = 'E',
3251 error_id = l_error_id(i)
3252 where rowid = l_row_id(i)
3253 and l_table_name(i) = 'HZ_IMP_CONTACTS_INT';
3254
3255 ForAll i in 1..l_row_id.count
3256 update hz_imp_contactroles_int
3257 set interface_status = 'E',
3258 error_id = l_error_id(i)
3259 where rowid = l_row_id(i)
3260 and l_table_name(i) = 'HZ_IMP_CONTACTROLES_INT';
3261
3262 ForAll i in 1..l_row_id.count
3263 update hz_imp_addressuses_int
3264 set interface_status = 'E',
3265 error_id = l_error_id(i)
3266 where rowid = l_row_id(i)
3267 and l_table_name(i) = 'HZ_IMP_ADDRESSUSES_INT';
3268
3269 ForAll i in 1..l_row_id.count
3270 update hz_imp_finreports_int
3271 set interface_status = 'E',
3272 error_id = l_error_id(i)
3273 where rowid = l_row_id(i)
3274 and l_table_name(i) = 'HZ_IMP_FINREPORTS_INT';
3275
3276 ForAll i in 1..l_row_id.count
3277 update hz_imp_finnumbers_int
3278 set interface_status = 'E',
3279 error_id = l_error_id(i)
3280 where rowid = l_row_id(i)
3281 and l_table_name(i) = 'HZ_IMP_FINNUMBERS_INT';
3282
3283 ForAll i in 1..l_row_id.count
3284 update hz_imp_creditrtngs_int
3285 set interface_status = 'E',
3286 error_id = l_error_id(i)
3287 where rowid = l_row_id(i)
3288 and l_table_name(i) = 'HZ_IMP_CREDITRTNGS_INT';
3289
3290 ForAll i in 1..l_row_id.count
3291 update hz_imp_classifics_int
3292 set interface_status = 'E',
3293 error_id = l_error_id(i)
3294 where rowid = l_row_id(i)
3295 and l_table_name(i) = 'HZ_IMP_CLASSIFICS_INT';
3296
3297 ForAll i in 1..l_row_id.count
3298 update hz_imp_relships_int
3299 set interface_status = 'E',
3300 error_id = l_error_id(i)
3301 where rowid = l_row_id(i)
3302 and l_table_name(i) = 'HZ_IMP_RELSHIPS_INT';
3303
3304 COMMIT;
3305
3306 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
3307 hz_utility_v2pub.debug(p_message=>'WRP:GENERATE_ERRORS()-',
3308 p_prefix=>'',
3309 p_msg_level=>fnd_log.level_procedure);
3310 END IF;
3311
3312 END GENERATE_ERRORS;
3313
3314
3315 PROCEDURE DATA_LOAD_PREPROCESSING(
3316 P_BATCH_ID IN NUMBER,
3317 P_ORIG_SYSTEM IN VARCHAR2,
3318 P_WHAT_IF_ANALYSIS IN VARCHAR2,
3319 P_RERUN_FLAG OUT NOCOPY VARCHAR2
3320 ) IS
3321 l_batch_status VARCHAR2(150);
3322 l_phase_code VARCHAR2(1);
3323 l_rerun VARCHAR2(1);
3324 l_wu_exists VARCHAR2(1);
3325
3326 CURSOR c_batch_status(p_batch_id number) IS
3327 select bs.import_status, r.phase_code
3328 from hz_imp_batch_details bs, FND_CONCURRENT_REQUESTS r
3329 where bs.batch_id = p_batch_id
3330 and bs.import_req_id = r.request_id(+)
3331 and bs.run_number = (select max(run_number)-1
3332 from hz_imp_batch_details
3333 where batch_id = p_batch_id);
3334
3335 CURSOR c_wu(p_batch_id number) IS
3336 SELECT 'Y'
3337 FROM hz_imp_work_units
3338 WHERE batch_id = p_batch_id
3339 AND rownum = 1;
3340 --l_debug_prefix VARCHAR2(30) := '';
3341 BEGIN
3342 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
3343 hz_utility_v2pub.debug(p_message=>'WRP:DATA_LOAD_PREPROCESSING()+',
3344 p_prefix=>'',
3345 p_msg_level=>fnd_log.level_procedure);
3346 END IF;
3347
3348 /* Get latest batch details status */
3349 OPEN c_batch_status(P_BATCH_ID);
3350 FETCH c_batch_status INTO l_batch_status, l_phase_code;
3351 CLOSE c_batch_status;
3352
3353 if l_batch_status in ('COMPLETED', 'COMPL_ERRORS') then
3354 l_rerun := 'E'; /* For a COMPLETED status in batch details, it may mean the batch
3355 run is ok but there're still errors in the batch. So we also
3356 set rerun flag to 'E'. The import wrapper will filter out any
3357 batch that has a COMPLETED batch status. */
3358 elsif l_batch_status is null then
3359 l_rerun := 'N'; -- New
3360
3361 elsif l_batch_status = 'COMPL_ERROR_LIMIT' then
3362 l_rerun := 'L';
3363
3364 elsif l_batch_status = 'ERROR' then
3365 l_rerun := 'U';
3366
3367 elsif l_batch_status = 'PROCESSING' then
3368 if l_phase_code <> 'C' then
3369 l_rerun := 'D'; -- Duplicate run of a running request
3370 else
3371 l_rerun := 'U'; -- unexpected error. Concurrent process completed but
3372 -- batch status is still 'PROCESSING'
3373 end if;
3374
3375 elsif l_batch_status = 'ACTION_REQUIRED' then -- what-if analysis pause
3376 if P_WHAT_IF_ANALYSIS = 'R' then
3377 l_rerun := 'R'; -- Resume
3378 else
3379 l_rerun := 'N'; -- New
3380 end if;
3381 end if;
3382
3383 P_RERUN_FLAG := l_rerun;
3384 OPEN c_wu(P_BATCH_ID);
3385 FETCH c_wu INTO l_wu_exists;
3386 CLOSE c_wu;
3387
3388 /* Generate work units */
3389 IF l_rerun = 'N' OR l_rerun = 'E' THEN
3390
3391 IF l_wu_exists = 'Y' THEN
3392 delete hz_imp_work_units
3393 where batch_id = P_BATCH_ID;
3394 END IF;
3395
3396 GENERATE_ENTITIES_WORK_UNITS(P_BATCH_ID, P_ORIG_SYSTEM);
3397
3398 END IF;
3399
3400 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
3401 hz_utility_v2pub.debug(p_message=>'WRP:DATA_LOAD_PREPROCESSING()-',
3402 p_prefix=>'',
3403 p_msg_level=>fnd_log.level_procedure);
3404 END IF;
3405
3406 END DATA_LOAD_PREPROCESSING;
3407
3408
3409 /* Clean up postprocessing status in work units table if previous
3410 run did not complete successfully */
3411 PROCEDURE RESET_PP_WU(
3412 P_BATCH_ID IN NUMBER
3413 ) IS
3414 CURSOR c_unfinished_pp(p_batch_id number) IS
3415 select 'Y' from hz_imp_work_units
3416 where batch_id = p_batch_id
3417 and (postprocess_status is null or postprocess_status <> 'C')
3418 and rownum = 1;
3419 l_unfinished_pp_exists VARCHAR2(1);
3420
3421 BEGIN
3422
3423 OPEN c_unfinished_pp(P_BATCH_ID);
3424 FETCH c_unfinished_pp INTO l_unfinished_pp_exists;
3425 IF c_unfinished_pp%NOTFOUND
3426 THEN
3427 l_unfinished_pp_exists := 'N';
3428 END IF;
3429 CLOSE c_unfinished_pp;
3430
3431 IF l_unfinished_pp_exists = 'N' THEN
3432 /* If all WUs were processed successfully in previous run,
3433 update postprocess_status to NULL so that PP will be
3434 done for the current request_id */
3435 update hz_imp_work_units
3436 set postprocess_status = NULL
3437 where batch_id = P_BATCH_ID;
3438 ELSE
3439 /* For WUs with NULL postprocess_status, they were not processed
3440 at all in previous run. Update postprocess_status to 'U'
3441 so that PP will be done for the current request_id and all
3442 previous request_ids
3443
3444 For WUs with 'U' postprocess_status, they will be left as is
3445 because they were not processed in some previous runs and
3446 also had errors in the last run. PP will be done for the
3447 current request_id and all previous request_ids*/
3448 update hz_imp_work_units
3449 set postprocess_status = 'U'
3450 where batch_id = P_BATCH_ID
3451 and (postprocess_status is NULL or postprocess_status <> 'C');
3452
3453 /* For WUs with 'C' postprocess_status, they were processed
3454 successfully in previous run. Update postprocess_status to NULL
3455 so that PP will be done for the current request_id */
3456 update hz_imp_work_units
3457 set postprocess_status = NULL
3458 where batch_id = P_BATCH_ID
3459 and postprocess_status = 'C';
3460 END IF;
3461
3462 END RESET_PP_WU;
3463
3464
3465 -- Wrapper for running batch data load.
3466 PROCEDURE BATCH_DATA_LOAD (
3467 Errbuf OUT NOCOPY VARCHAR2,
3468 Retcode OUT NOCOPY VARCHAR2,
3469 P_BATCH_ID IN NUMBER,
3470 P_ORIG_SYSTEM IN VARCHAR2,
3471 P_WHAT_IF_ANALYSIS IN VARCHAR2,
3472 P_REGISTRY_DEDUP IN VARCHAR2,
3473 P_REGISTRY_DEDUP_MATCH_RULE_ID IN NUMBER,
3474 P_SYSDATE IN VARCHAR2,
3475 P_NUM_OF_WORKERS IN NUMBER,
3476 P_ERROR_LIMIT IN NUMBER,
3477 P_RERUN_FLAG IN VARCHAR2,
3478 P_REQUEST_ID IN NUMBER,
3479 P_PROGRAM_APPLICATION_ID IN NUMBER,
3480 P_PROGRAM_ID IN NUMBER
3481 ) IS
3482
3483 BEGIN
3484
3485 DATA_LOAD (
3486 Errbuf ,
3487 Retcode ,
3488 P_BATCH_ID ,
3489 P_ORIG_SYSTEM ,
3490 P_WHAT_IF_ANALYSIS ,
3491 P_REGISTRY_DEDUP,
3492 P_REGISTRY_DEDUP_MATCH_RULE_ID ,
3493 P_SYSDATE ,
3494 'Y',
3495 P_NUM_OF_WORKERS,
3496 P_ERROR_LIMIT,
3497 P_RERUN_FLAG,
3498 P_REQUEST_ID,
3499 P_PROGRAM_APPLICATION_ID,
3500 P_PROGRAM_ID
3501 );
3502
3503 END BATCH_DATA_LOAD;
3504
3505
3506 -- Wrapper for running online data load. Call DATA_LOAD.
3507 PROCEDURE ONLINE_DATA_LOAD (
3508 Errbuf OUT NOCOPY VARCHAR2,
3509 Retcode OUT NOCOPY VARCHAR2,
3510 P_BATCH_ID IN NUMBER,
3511 P_ORIG_SYSTEM IN VARCHAR2,
3512 P_WHAT_IF_ANALYSIS IN VARCHAR2,
3513 P_REGISTRY_DEDUP IN VARCHAR2,
3514 P_REGISTRY_DEDUP_MATCH_RULE_ID IN NUMBER,
3515 P_SYSDATE IN VARCHAR2,
3516 P_ERROR_LIMIT IN NUMBER,
3517 P_RERUN_FLAG IN VARCHAR2,
3518 P_REQUEST_ID IN NUMBER,
3519 P_PROGRAM_APPLICATION_ID IN NUMBER,
3520 P_PROGRAM_ID IN NUMBER
3521 ) IS
3522 BEGIN
3523
3524 DATA_LOAD (
3525 Errbuf ,
3526 Retcode ,
3527 P_BATCH_ID ,
3528 P_ORIG_SYSTEM ,
3529 P_WHAT_IF_ANALYSIS ,
3530 P_REGISTRY_DEDUP,
3531 P_REGISTRY_DEDUP_MATCH_RULE_ID ,
3532 P_SYSDATE ,
3533 'N',
3534 1,
3535 P_ERROR_LIMIT,
3536 P_RERUN_FLAG,
3537 P_REQUEST_ID,
3538 P_PROGRAM_APPLICATION_ID,
3539 P_PROGRAM_ID
3540 );
3541
3542 END ONLINE_DATA_LOAD;
3543
3544 FUNCTION GET_COUNTS(p_batch_id IN NUMBER)
3545 RETURN VARCHAR2 IS
3546 l_chk_cnts_flag VARCHAR2(1);
3547
3548 CURSOR c_check_counts IS
3549 SELECT 'Y'
3550 FROM HZ_IMP_WORK_UNITS
3551 WHERE batch_id=p_batch_id
3552 AND
3553 ((stage>=2
3554 AND status='C')
3555 OR
3556 (stage=3
3557 AND status='P')
3558 )
3559 AND rownum=1;
3560
3561 BEGIN
3562 OPEN c_check_counts;
3563 FETCH c_check_counts INTO l_chk_cnts_flag;
3564 IF c_check_counts%NOTFOUND
3565 THEN
3566 l_chk_cnts_flag := 'N';
3567 END IF;
3568 CLOSE c_check_counts;
3569 RETURN l_chk_cnts_flag;
3570 END GET_COUNTS;
3571
3572
3573 PROCEDURE DATA_LOAD (
3574 Errbuf OUT NOCOPY VARCHAR2,
3575 Retcode OUT NOCOPY VARCHAR2,
3576 P_BATCH_ID IN NUMBER,
3577 P_ORIG_SYSTEM IN VARCHAR2,
3578 P_WHAT_IF_ANALYSIS IN VARCHAR2,
3579 P_REGISTRY_DEDUP IN VARCHAR2,
3580 P_REGISTRY_DEDUP_MATCH_RULE_ID IN NUMBER,
3581 P_SYSDATE IN VARCHAR2,
3582 P_BATCH_MODE_FLAG IN VARCHAR2,
3583 P_NUM_OF_WORKERS IN NUMBER,
3584 P_ERROR_LIMIT IN NUMBER,
3585 P_RERUN_FLAG IN VARCHAR2,
3586 P_REQUEST_ID IN NUMBER,
3587 P_PROGRAM_APPLICATION_ID IN NUMBER,
3588 P_PROGRAM_ID IN NUMBER
3589 ) IS
3590
3591 TOTAL_NUM_STAGES NUMBER := 3; -- total number of stages with multiple workers
3592 --i NUMBER := 1;
3593 l_request_id NUMBER;-- := 0;
3594 program_name VARCHAR2(30); -- program name for current stage
3595 stage NUMBER; -- current stage
3596 req_data VARCHAR2(10); -- request data
3597 l_content_src_type VARCHAR2(30); -- content source type, equivalent to OS if
3598 -- matched in lookup, 'USER_ENTERED' otherwise
3599 l_error_message fnd_new_messages.message_text%TYPE;
3600 l_batch_status VARCHAR2(150);
3601 l_what_if_flag VARCHAR2(1);
3602 l_phase_code VARCHAR2(1);
3603 l_hr_data_exists VARCHAR2(1);
3604 l_wu_exists VARCHAR2(1);
3605 l_err_exists VARCHAR2(1);
3606 l_num_invalid_pid NUMBER;
3607
3608 l_user_id NUMBER;
3609 l_resp_appl_id NUMBER;
3610 l_last_update_login NUMBER;
3611 l_program_update_date DATE;
3612 l_g_miss_num NUMBER;
3613 l_g_miss_char VARCHAR2(240);
3614 l_g_miss_date DATE;
3615 l_flex_validation_prof VARCHAR2(1);
3616 l_dss_security_prof VARCHAR2(1);
3617 l_allow_disabled_lookup_prof VARCHAR2(1);
3618 l_profile_version_prof VARCHAR2(30);
3619 l_update_str_addr_prof VARCHAR2(1);
3620 l_maintain_loc_hist_prof VARCHAR2(1);
3621 l_allow_addr_corr_prof VARCHAR2(1);
3622 l_total_records_imported NUMBER;
3623 l_start_error_id NUMBER;
3624 l_current_error_id NUMBER;
3625
3626 l_sst_flag VARCHAR2(1);
3627
3628 D_SYSDATE DATE;
3629
3630 l_return_status VARCHAR2(1);
3631
3632 CURSOR c_hr_data(p_batch_id number) IS
3633 SELECT 'Y'
3634 FROM hz_imp_parties_int
3635 WHERE batch_id = p_batch_id
3636 AND party_orig_system = 'DEFAULT'
3637 AND interface_status is null
3638 AND party_orig_system_reference like 'PER%'
3639 AND rownum = 1;
3640
3641 CURSOR c_error(p_batch_id number, p_main_req_id number) IS
3642 SELECT 'Y'
3643 FROM hz_imp_tmp_errors
3644 WHERE batch_id = p_batch_id
3645 and request_id = p_main_req_id
3646 AND rownum = 1;
3647
3648 CURSOR c_batch_error(p_batch_id number) IS
3649 SELECT decode(nvl(total_errors, 0), 0, 'N', 'Y')
3650 FROM HZ_IMP_BATCH_SUMMARY
3651 WHERE batch_id = p_batch_id;
3652
3653 CURSOR c_sg_data(p_batch_id number) IS
3654 SELECT 'Y'
3655 FROM hz_imp_parties_sg
3656 WHERE batch_id = p_batch_id
3657 AND rownum = 1;
3658
3659 CURSOR c_wu(p_batch_id number) IS
3660 SELECT 'Y'
3661 FROM hz_imp_work_units
3662 WHERE batch_id = p_batch_id
3663 AND rownum = 1;
3664
3665 --l_debug_prefix VARCHAR2(30) := '';
3666 BEGIN
3667 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
3668 hz_utility_v2pub.debug(p_message=>'WRP:DATA_LOAD()+',
3669 p_prefix=>'',
3670 p_msg_level=>fnd_log.level_procedure);
3671 END IF;
3672
3673 OPEN c_wu(P_BATCH_ID);
3674 FETCH c_wu INTO l_wu_exists;
3675 CLOSE c_wu;
3676
3677 /* Exit if the batch is completed or is a duplicate run of a running request */
3678 IF P_RERUN_FLAG = 'C' THEN
3679 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Completed batch cannot be run again.');
3680 return;
3681 ELSIF P_RERUN_FLAG = 'D' THEN
3682 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Same batch cannot be run at the same time.');
3683 return;
3684 END IF;
3685
3686 /*
3687 Import stages
3688 '1' - Matching of Parties
3689 '2' - Matching of other entities, DQM, V+DML of Parties
3690 '3' - Dup check within rel/contacts, addr uses, and code assignments
3691 '4' - Dup check of relationships and address uses within interface
3692 - V+DML of other entities, PP-Wait
3693 */
3694
3695 /* Get batch summary status */
3696 select bs.import_status, bs.what_if_flag, r.phase_code, bs.validate_flexfield_flag
3697 into l_batch_status, l_what_if_flag, l_phase_code , l_flex_validation_prof
3698 from hz_imp_batch_summary bs, FND_CONCURRENT_REQUESTS r
3699 where bs.batch_id = P_BATCH_ID
3700 and bs.import_req_id = r.request_id(+);
3701
3702 req_data := fnd_conc_global.request_data;
3703 stage := 0;
3704 IF (req_data is not null) THEN
3705 stage := to_number(req_data);
3706 END IF;
3707
3708 /* bug 4079902 */
3709 -- bug 4374278. Moved the fix here because the CLEANUP_SSM requires l_content_src_type
3710 SELECT SST_FLAG into l_sst_flag
3711 FROM HZ_ORIG_SYSTEMS_B
3712 WHERE ORIG_SYSTEM=P_ORIG_SYSTEM
3713 AND STATUS='A';
3714
3715 IF l_sst_flag='Y'
3716 THEN
3717 l_content_src_type := P_ORIG_SYSTEM;
3718 ELSE
3719 l_content_src_type := 'USER_ENTERED';
3720 END IF;
3721
3722 IF stage = 2 THEN
3723 /* delete duplicate record in sg with same int_row_id. This would happen
3724 when party loading for 1 worker finishes before the relationship matching
3725 another worker. Therefore when the latter worker is matching the object party
3726 it finds multiple active entries in the SSM table. Therefore due to the outer
3727 joins which are used in SSM matching, it results in muliple duplicate rows
3728 in relationship staging table.
3729 */
3730
3731 delete /*+ ROWID(SG) push_subq */ from hz_imp_relships_sg SG
3732 where batch_id = P_BATCH_ID
3733 and rowid in (
3734 select /*+ no_merge */ rowid
3735 from (
3736 select /*+ parallel(SG) */ rowid,
3737 rank() over (partition by int_row_id order by relationship_id desc) rn
3738 from hz_imp_relships_sg SG
3739 where batch_id = P_BATCH_ID
3740 )
3741 where rn > 1
3742 );
3743
3744 IF l_content_src_type = 'DNB' THEN
3745 /*
3746 Fix bug 4175285: Remove potential duplicates in hz_imp_relships_sg after
3747 matching for DNB. Since parties with same OS+OSR but different party_id
3748 can exist in a batch, when we do matching, duplicate records may be
3749 created. E.g.
3750 There are 3 parties in a DNB batch:
3751 OSR PID
3752 -----------------
3753 123 1001
3754 456 1002
3755 456 1003
3756
3757 456 is the HQ, domestic and global ultimate of 123. When we do
3758 relationship matching, we'll create duplicate rows in rel staging:
3759 rel code sub OSR obj OSR sub id obj id
3760 --------------------------------------------------------
3761 HEADQUARTERS_OF 456 123 1002 1001
3762 HEADQUARTERS_OF 456 123 1003 1001
3763
3764 It is a problem only for DNB OS because we build the DNB hierarchy
3765 in post-processing. It will cause problem if a party has two HQs.
3766 For other OS, we'll allow duplicate relationships to be created
3767 because the 2nd and 3rd parties are duplicates and so creating
3768 relationships to both should not be a problem. Confirmed this with Indrajit.
3769 */
3770
3771 delete /*+ ROWID(SG) push_subq */ from hz_imp_relships_sg SG
3772 where batch_id = P_BATCH_ID
3773 and sub_orig_system = 'DNB' /* hardcode DNB as it only happens to DNB data */
3774 and rowid in (
3775 select /*+ no_merge */ rowid
3776 from (
3777 select /*+ parallel(SG) */ rowid,
3778 rank() over (partition by relationship_type, relationship_code, sub_orig_system_reference, obj_id
3779 order by sub_id desc) rn
3780 from hz_imp_relships_sg SG
3781 where batch_id = P_BATCH_ID
3782 and sub_orig_system = 'DNB'
3783 )
3784 where rn > 1
3785 );
3786 END IF;
3787 END IF;
3788
3789 l_profile_version_prof := NVL(FND_PROFILE.value('HZ_PROFILE_VERSION'), 'ONE_DAY_VERSION');
3790
3791 /* If it's after stage 1 then
3792 if batch status = ACTION_REQUIRED, this run is for
3793 what-if analysis and is done, exit.
3794 if batch status = COMPL_ERROR_LIMIT, error limit
3795 is reached, exit.
3796 These two status can only be set in stage 2 and 3. */
3797 if stage > 1 AND
3798 (l_batch_status = 'ACTION_REQUIRED'
3799 OR l_batch_status = 'COMPL_ERROR_LIMIT') then
3800 retcode := 0;
3801 IF l_batch_status = 'ACTION_REQUIRED'
3802 THEN
3803 CLEANUP_DUP_OSR(P_BATCH_ID,P_BATCH_MODE_FLAG,'ADDRESS',P_ORIG_SYSTEM);
3804 ELSIF l_batch_status = 'COMPL_ERROR_LIMIT' THEN
3805 CLEANUP_SSM(l_content_src_type,P_BATCH_ID,P_BATCH_MODE_FLAG,P_ORIG_SYSTEM,P_REQUEST_ID);
3806 GENERATE_ERRORS(P_BATCH_ID, P_REQUEST_ID, P_ORIG_SYSTEM, P_RERUN_FLAG, 'N');
3807
3808 IF stage>=2 AND
3809 ( P_WHAT_IF_ANALYSIS is null
3810 OR (P_WHAT_IF_ANALYSIS is not null AND P_WHAT_IF_ANALYSIS<>'A'))
3811 AND GET_COUNTS(P_BATCH_ID)='Y'
3812 THEN
3813 HZ_IMP_LOAD_BATCH_COUNTS_PKG.post_import_counts(P_BATCH_ID, P_ORIG_SYSTEM,
3814 P_BATCH_MODE_FLAG,P_REQUEST_ID, P_RERUN_FLAG);
3815 END IF;
3816
3817 END IF;
3818 RETURN;
3819 end if;
3820
3821 /* If it's after stage 0 and batch status is not PROCESSING,
3822 some exceptions occur from some later stage and hence exit.
3823 Error may occur in any of the 3 stages. */
3824 if stage > 0 and l_batch_status <> 'PROCESSING' then
3825 CLEANUP_SSM(l_content_src_type,P_BATCH_ID,P_BATCH_MODE_FLAG,P_ORIG_SYSTEM,P_REQUEST_ID);
3826 GENERATE_ERRORS(P_BATCH_ID, P_REQUEST_ID, P_ORIG_SYSTEM, P_RERUN_FLAG, 'N');
3827
3828 IF stage>=2 AND
3829 ( P_WHAT_IF_ANALYSIS is null
3830 OR (P_WHAT_IF_ANALYSIS is not null AND P_WHAT_IF_ANALYSIS<>'A'))
3831 AND GET_COUNTS(P_BATCH_ID)='Y'
3832 THEN
3833 HZ_IMP_LOAD_BATCH_COUNTS_PKG.post_import_counts(P_BATCH_ID, P_ORIG_SYSTEM,
3834 P_BATCH_MODE_FLAG,P_REQUEST_ID, P_RERUN_FLAG);
3835 END IF;
3836
3837 retcode := 2;
3838 RETURN;
3839 end if;
3840
3841 /* Save who column values in variables for performance */
3842 l_user_id := NVL(FND_GLOBAL.user_id,-1);
3843 l_resp_appl_id := hz_utility_v2pub.application_id;
3844 l_last_update_login := hz_utility_v2pub.last_update_login;
3845 l_g_miss_num := NVL(FND_PROFILE.value('HZ_IMP_G_MISS_NUM'), -9999);
3846 l_g_miss_char := NVL(FND_PROFILE.value('HZ_IMP_G_MISS_CHAR'), '!');
3847 l_g_miss_date := NVL(to_date(FND_PROFILE.value('HZ_IMP_G_MISS_DATE'), 'DD/MM/YYYY'),
3848 to_date('01/01/4000', 'DD/MM/YYYY'));
3849 l_flex_validation_prof := NVL(l_flex_validation_prof,NVL(FND_PROFILE.value('HZ_IMP_FLEX_VALIDATION'), 'N'));
3850 l_dss_security_prof := NVL(FND_PROFILE.value('HZ_IMP_DSS_SECURITY'), 'N');
3851 l_allow_disabled_lookup_prof := NVL(FND_PROFILE.value('HZ_IMP_ALLOW_DISABLED_LOOKUP'), 'Y');
3852 l_update_str_addr_prof := NVL(FND_PROFILE.value('HZ_UPDATE_STD_ADDRESS'), 'N');
3853 l_maintain_loc_hist_prof := NVL(FND_PROFILE.value('HZ_MAINTAIN_LOC_HISTORY'), 'Y');
3854 l_allow_addr_corr_prof := NVL(FND_PROFILE.value('HZ_IMP_ALLOW_ADDR_CORRECTION'), 'Y');
3855
3856 /* Hardcode date format mask as P_SYSDATE is passed from main
3857 wrapper and it hardcodes with this date format */
3858 D_SYSDATE := TO_DATE(P_SYSDATE,'DD-MM-YY HH24:MI:SS');
3859
3860 /* Check if 3rd party data */
3861 -- l_content_src_type := GET_CONTENT_SRC_TYPE(P_ORIG_SYSTEM);
3862
3863 IF stage = 0 THEN
3864 IF P_RERUN_FLAG <> 'R' THEN -- Regular process if not resume
3865
3866
3867 /* Add policy function if not exists */
3868
3869 IF l_content_src_type <> 'USER_ENTERED' AND
3870 NVL(fnd_profile.value('HZ_DNB_POLICY_EXIST'), 'N') = 'N' THEN
3871
3872 /* bug fix 3849232 - add policy functions unconditionally */
3873 add_policy();
3874 END IF;
3875
3876
3877 /* Check HR security */
3878 IF NVL(FND_PROFILE.value('HZ_IMP_HR_SECURITY'), 'N') = 'Y' THEN
3879 -- ARH2RGVB.pls
3880 /*
3881 check in the Parties Interface Table if there are any records
3882 with OS='DEFAULT' and OSR = 'PER%' (because that's how HR data
3883 is migrated to SSM model). If there are such records, exit
3884 the process with Error.
3885 */
3886
3887 OPEN c_hr_data(P_BATCH_ID);
3888 FETCH c_hr_data INTO l_hr_data_exists;
3889 CLOSE c_hr_data;
3890
3891 IF l_hr_data_exists = 'Y' THEN
3892 FND_MESSAGE.SET_NAME('AR', 'HZ_IMP_NO_HR_DATALOAD');
3893 l_error_message := FND_MESSAGE.get;
3894
3895 FND_FILE.PUT_LINE(FND_FILE.LOG, 'log:' || l_error_message);
3896 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, l_error_message);
3897 update_import_status(P_BATCH_ID, 'ERROR');
3898
3899 errbuf := l_error_message;
3900 retcode := 2;
3901 RETURN;
3902 END IF;
3903
3904 END IF; -- HR security
3905
3906 /* Cleanup staging */
3907 /* CLEANUP_STAGING(P_BATCH_ID, P_BATCH_MODE_FLAG); */
3908 END IF; -- P_RERUN_FLAG <> 'R'
3909 END IF; -- stage = 0
3910
3911 stage := stage + 1; -- proceed to next stage
3912 IF (stage=1) THEN
3913
3914 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3915 hz_utility_v2pub.debug(p_message=>'WRP:Stage 1, P_RERUN_FLAG: ' || P_RERUN_FLAG,
3916 p_prefix =>'',
3917 p_msg_level=>fnd_log.level_statement);
3918 END IF;
3919
3920 /* Update batch summary status to PROCESSING */
3921 update_import_status(P_BATCH_ID, 'PROCESSING');
3922 /* Increase sequence number cache size */
3923 ALTER_SEQUENCES('I', P_BATCH_MODE_FLAG);
3924
3925
3926 CHECK_INVALID_PARTY(P_BATCH_ID,P_REQUEST_ID,l_user_id,l_last_update_login,P_PROGRAM_ID,
3927 P_PROGRAM_APPLICATION_ID,l_return_status);
3928
3929 IF l_return_status = 'E' THEN
3930 l_error_message := 'Invalid party_id(s) is found in hz_imp_parties_int that does not exist in hz_parties. Please correct the party_id and resubmit the batch for import. Please check error report for invalid party_id(s).';
3931 FND_FILE.PUT_LINE(FND_FILE.LOG, 'log:' || l_error_message);
3932 update_import_status(P_BATCH_ID, 'ERROR');
3933
3934 errbuf := l_error_message;
3935 retcode := 2;
3936 RETURN;
3937 END IF;
3938
3939 IF P_BATCH_MODE_FLAG = 'Y' AND P_RERUN_FLAG = 'N' THEN
3940 -- Analyze interface tables
3941 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Analyze all interface tables ' ||
3942 to_char(sysdate, 'DD-MON-YYYY HH24:MI:SS'));
3943
3944 fnd_stats.gather_table_stats('AR', 'HZ_IMP_PARTIES_INT');
3945 fnd_stats.gather_table_stats('AR', 'HZ_IMP_ADDRESSES_INT');
3946 fnd_stats.gather_table_stats('AR', 'HZ_IMP_ADDRESSUSES_INT');
3947 fnd_stats.gather_table_stats('AR', 'HZ_IMP_CLASSIFICS_INT');
3948 fnd_stats.gather_table_stats('AR', 'HZ_IMP_CONTACTPTS_INT');
3949 fnd_stats.gather_table_stats('AR', 'HZ_IMP_CONTACTROLES_INT');
3950 fnd_stats.gather_table_stats('AR', 'HZ_IMP_CONTACTS_INT');
3951 fnd_stats.gather_table_stats('AR', 'HZ_IMP_CREDITRTNGS_INT');
3952 fnd_stats.gather_table_stats('AR', 'HZ_IMP_FINNUMBERS_INT');
3953 fnd_stats.gather_table_stats('AR', 'HZ_IMP_FINREPORTS_INT');
3954 fnd_stats.gather_table_stats('AR', 'HZ_IMP_RELSHIPS_INT');
3955 END IF;
3956
3957 /* Stage 1 preprocess before calling parties matching */
3958 IF P_RERUN_FLAG = 'U' OR P_RERUN_FLAG = 'L' THEN
3959
3960 /* Clean up any processing work units */
3961 UPDATE hz_imp_work_units
3962 SET status = 'C', stage = stage - 1
3963 WHERE batch_id = P_BATCH_ID
3964 and status = 'P';
3965
3966 /* Keep track of the high water mark stage such that when we do V+DML,
3967 the appropriate records are picked up. Stage 2 workers check if stage <
3968 hwm stage, if so it will pick up the corrected records ("C" interface status).
3969 Else it will pick up the null interface status records. */
3970 UPDATE hz_imp_work_units
3971 SET hwm_stage = case when nvl(hwm_stage, 0) > stage
3972 then hwm_stage else stage end
3973 WHERE batch_id = P_BATCH_ID;
3974
3975 /* Reset all stage to 0 to redo all matching because the staging tables are
3976 always cleaned up except for resume of what-if */
3977 UPDATE hz_imp_work_units
3978 SET stage = 0
3979 WHERE batch_id = P_BATCH_ID;
3980
3981 /* If previous run is not successful, reset the postprocessing status
3982 of work units to prepare for processing in the current run */
3983 IF P_RERUN_FLAG = 'U' THEN
3984 RESET_PP_WU(P_BATCH_ID);
3985 END IF;
3986
3987 /* Clean up staging if old data from previous run exists. If error
3988 happens in stage 3 and by the time the batch is resubmitted, all the
3989 staging data from previous runs are there, we may get unique index
3990 failure when we redo matching for some of the failed work units since
3991 we'll insert the exact same row into staging tables. */
3992 CLEANUP_STAGING(P_BATCH_ID, P_BATCH_MODE_FLAG);
3993
3994 ELSIF P_RERUN_FLAG = 'R' THEN
3995 /* Check if data present in staging. If so,
3996 go to stage 2. Reset the stage so that WUs
3997 will be worked on by the appropriate workers.
3998 Else go to stage 1 to redo matching.
3999 */
4000 IF STAGING_DATA_EXISTS(P_BATCH_ID, P_BATCH_MODE_FLAG, 1) = 'Y' THEN
4001
4002 UPDATE hz_imp_work_units
4003 SET stage = 1, status = 'C'
4004 WHERE batch_id = P_BATCH_ID;
4005
4006 stage := 2; /* Skip stage 1 matching */
4007 ELSE
4008
4009 UPDATE hz_imp_work_units
4010 SET stage = 0, status = 'C'
4011 WHERE batch_id = P_BATCH_ID;
4012
4013 /* If this is batch mode, clean up staging tables to handle the case of:
4014 Run what-if for batch 1
4015 Run what-if for batch 2
4016 Resume batch 1, but batch 2 staging data is still there */
4017 IF P_BATCH_MODE_FLAG = 'Y' THEN
4018 CLEANUP_STAGING(P_BATCH_ID, 'Y');
4019 END IF;
4020
4021 END IF;
4022 END IF;
4023
4024
4025 elsif (stage=2) then
4026 FND_FILE.PUT_LINE(FND_FILE.LOG, '****** Finished processing stage 1 ');
4027
4028 /* Bug7374773 : Call cleanup_dup_osr for NO_VERSION profile also */
4029 CLEANUP_DUP_OSR(P_BATCH_ID,P_BATCH_MODE_FLAG,'PARTY',P_ORIG_SYSTEM);
4030
4031
4032 /* Matching of other entities, DQM, V+DML of Parties */
4033 IF P_BATCH_MODE_FLAG = 'Y' THEN
4034 -- Analyze parties staging after matching
4035 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Analyze party staging table ' ||
4036 to_char(sysdate, 'DD-MON-YYYY HH24:MI:SS'));
4037 fnd_stats.gather_table_stats('AR', 'HZ_IMP_PARTIES_SG', percent=>10, degree=>4);
4038 END IF;
4039
4040 elsif (stage=3) then
4041 FND_FILE.PUT_LINE(FND_FILE.LOG, '****** Finished processing stage 2 ');
4042 /* Bug7374773 : Call cleanup_dup_osr for NO_VERSION profile also */
4043 CLEANUP_DUP_OSR(P_BATCH_ID,P_BATCH_MODE_FLAG,'ADDRESS',P_ORIG_SYSTEM);
4044
4045
4046 IF P_BATCH_MODE_FLAG = 'Y' THEN
4047 -- Analyze staging table after matching
4048 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Analyze other entity staging tables ' ||
4049 to_char(sysdate, 'DD-MON-YYYY HH24:MI:SS'));
4050 fnd_stats.gather_table_stats('AR', 'HZ_IMP_ADDRESSES_SG', percent=>5, degree=>4);
4051 fnd_stats.gather_table_stats('AR', 'HZ_IMP_ADDRESSUSES_SG', percent=>5, degree=>4);
4052 fnd_stats.gather_table_stats('AR', 'HZ_IMP_CLASSIFICS_SG', percent=>5, degree=>4);
4053 fnd_stats.gather_table_stats('AR', 'HZ_IMP_CONTACTPTS_SG', percent=>5, degree=>4);
4054 fnd_stats.gather_table_stats('AR', 'HZ_IMP_CONTACTROLES_SG', percent=>5, degree=>4);
4055 fnd_stats.gather_table_stats('AR', 'HZ_IMP_CONTACTS_SG', percent=>5, degree=>4);
4056 fnd_stats.gather_table_stats('AR', 'HZ_IMP_CREDITRTNGS_SG', percent=>5, degree=>4);
4057 fnd_stats.gather_table_stats('AR', 'HZ_IMP_FINNUMBERS_SG', percent=>5, degree=>4);
4058 fnd_stats.gather_table_stats('AR', 'HZ_IMP_FINREPORTS_SG', percent=>5, degree=>4);
4059 fnd_stats.gather_table_stats('AR', 'HZ_IMP_RELSHIPS_SG', percent=>5, degree=>4);
4060 fnd_stats.gather_table_stats('AR', 'HZ_ORIG_SYS_REFERENCES', percent=>5, degree=>4);
4061 END IF;
4062
4063 -- get the start error_id sequence number
4064 SELECT hz_imp_errors_s.NEXTVAL INTO l_start_error_id FROM dual;
4065
4066 /* Dup check of relationships and address uses within interface,
4067 V+DML of other entities, PP-Wait */
4068 ident_dup_within_int(P_BATCH_ID, P_ORIG_SYSTEM, P_BATCH_MODE_FLAG, P_REQUEST_ID,
4069 D_SYSDATE, l_user_id, l_last_update_login,
4070 P_PROGRAM_APPLICATION_ID, P_PROGRAM_ID);
4071
4072 SELECT hz_imp_errors_s.CURRVAL INTO l_current_error_id FROM dual;
4073
4074 -- if error is greater than error limit
4075 IF l_current_error_id - l_start_error_id >=
4076 NVL(P_ERROR_LIMIT, NVL(FND_PROFILE.value('HZ_IMP_ERROR_LIMIT'), 10000)) THEN
4077
4078 -- update batch summary table and detail table
4079 -- set status as complete with reaching error limit
4080
4081 update hz_imp_batch_summary
4082 set IMPORT_STATUS = 'COMPL_ERROR_LIMIT'
4083 where BATCH_ID = P_BATCH_ID;
4084
4085 UPDATE hz_imp_batch_details
4086 SET import_status = 'COMPL_ERROR_LIMIT'
4087 WHERE batch_id = P_BATCH_ID
4088 AND run_number = (SELECT max(run_number)
4089 FROM hz_imp_batch_details
4090 WHERE batch_id = P_BATCH_ID);
4091 COMMIT;
4092
4093 retcode := 2;
4094
4095 GENERATE_ERRORS(P_BATCH_ID, P_REQUEST_ID, P_ORIG_SYSTEM, P_RERUN_FLAG, 'N');
4096
4097 IF P_WHAT_IF_ANALYSIS is null
4098 OR (P_WHAT_IF_ANALYSIS is not null AND P_WHAT_IF_ANALYSIS<>'A')
4099 AND GET_COUNTS(P_BATCH_ID)='Y'
4100 THEN
4101 HZ_IMP_LOAD_BATCH_COUNTS_PKG.post_import_counts(P_BATCH_ID, P_ORIG_SYSTEM,
4102 P_BATCH_MODE_FLAG,P_REQUEST_ID, P_RERUN_FLAG);
4103 END IF;
4104
4105 RETURN;
4106
4107 END IF;
4108
4109 elsif (stage>TOTAL_NUM_STAGES) then
4110 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
4111 hz_utility_v2pub.debug(p_message=>'WRP:All stages done: Start Postprocessing Wait',
4112 p_prefix =>'',
4113 p_msg_level=>fnd_log.level_statement);
4114 END IF;
4115
4116 /* Update displayed duns party id. Originally done in PP-Wait */
4117 IF P_ORIG_SYSTEM = 'DNB' THEN
4118 UPDATE_DISPLAYED_DUNS_PID(P_BATCH_ID, P_BATCH_MODE_FLAG);
4119 END IF;
4120
4121 /* Populate error tables with data from temp error table */
4122 GENERATE_ERRORS(P_BATCH_ID, P_REQUEST_ID, P_ORIG_SYSTEM, P_RERUN_FLAG, 'Y');
4123
4124 /* Clean up Staging table */
4125 /* CLEANUP_STAGING(P_BATCH_ID, P_BATCH_MODE_FLAG); */
4126
4127 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
4128 hz_utility_v2pub.debug(p_message=>'WRP:Staging table cleaned up',
4129 p_prefix =>'',
4130 p_msg_level=>fnd_log.level_statement);
4131 END IF;
4132 /* Delete Work Unit */
4133 /* delete hz_imp_work_units where batch_id = P_BATCH_ID;
4134
4135 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
4136 hz_utility_v2pub.debug(p_message=>'WRP:work united deleted',
4137 p_prefix =>'',
4138 p_msg_level=>fnd_log.level_statement);
4139 END IF;
4140
4141 /* Update counts in batch summary */
4142
4143 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
4144 hz_utility_v2pub.debug(p_message=>'WRP:post_import_counts()+',
4145 p_prefix=>'',
4146 p_msg_level=>fnd_log.level_procedure);
4147 END IF;
4148
4149 HZ_IMP_LOAD_BATCH_COUNTS_PKG.post_import_counts(P_BATCH_ID, P_ORIG_SYSTEM,
4150 P_BATCH_MODE_FLAG,P_REQUEST_ID, P_RERUN_FLAG);
4151
4152 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
4153 hz_utility_v2pub.debug(p_message=>'WRP:post_import_counts()-',
4154 p_prefix=>'',
4155 p_msg_level=>fnd_log.level_procedure);
4156 END IF;
4157
4158 /* Update import status */
4159 /* Check if any error in current run. If so, update import_status in
4160 both batch summary and details to COMPL_ERRORS. */
4161 OPEN c_error(P_BATCH_ID, P_REQUEST_ID);
4162 FETCH c_error INTO l_err_exists;
4163 CLOSE c_error;
4164 if l_err_exists = 'Y' then
4165 update_import_status(P_BATCH_ID, 'COMPL_ERRORS');
4166 else
4167 /* If no error in current run, check if there is any error records in
4168 all the interface tables. If so, set batch_summary import_status to
4169 COMPL_ERRORS and batch_details import_status to COMPLETED.
4170 Else set both import_status in batch_summary and batch_details
4171 to COMPLETED */
4172 OPEN c_batch_error(P_BATCH_ID);
4173 FETCH c_batch_error INTO l_err_exists;
4174 CLOSE c_batch_error;
4175
4176 if l_err_exists = 'Y' then
4177 update_import_status(P_BATCH_ID, 'COMPL_ERRORS2');
4178 else
4179 update_import_status(P_BATCH_ID, 'COMPLETED');
4180 end if;
4181 end if;
4182 /* Reset sequence number cache size to 20 */
4183 ALTER_SEQUENCES('R', P_BATCH_MODE_FLAG);
4184
4185 CLEANUP_SSM(l_content_src_type,P_BATCH_ID,P_BATCH_MODE_FLAG,P_ORIG_SYSTEM,P_REQUEST_ID);
4186
4187 /*
4188
4189 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
4190 hz_utility_v2pub.debug(p_message=>'WRP:post_import_counts()+',
4191 p_prefix=>'',
4192 p_msg_level=>fnd_log.level_procedure);
4193 END IF;
4194
4195 HZ_IMP_LOAD_BATCH_COUNTS_PKG.post_import_counts(P_BATCH_ID, P_ORIG_SYSTEM,
4196 P_BATCH_MODE_FLAG,P_REQUEST_ID, P_RERUN_FLAG);
4197
4198 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
4199 hz_utility_v2pub.debug(p_message=>'WRP:post_import_counts()-',
4200 p_prefix=>'',
4201 p_msg_level=>fnd_log.level_procedure);
4202 END IF;*/
4203
4204 /* Give warning if no records are imported. Need to check only
4205 for a new batch because if it is a rerun, total_records_imported is
4206 greater than 0 */
4207 IF P_RERUN_FLAG = 'N' THEN
4208 select nvl(total_records_imported, 0)
4209 into l_total_records_imported
4210 from hz_imp_batch_summary
4211 where batch_id = P_BATCH_ID;
4212
4213 IF l_total_records_imported <= 0 THEN
4214 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Warning: No data is imported by the batch.');
4215 END IF;
4216 END IF;
4217
4218 -- all stages have finished
4219 errbuf := 'All stages of Data Import have finished.';
4220 retcode := 0;
4221 RETURN;
4222 end if;
4223 -- kick off all concurrent workers
4224 FOR i IN 1..P_NUM_OF_WORKERS LOOP
4225
4226 -- submit request for each worker process
4227 IF (stage=1) THEN
4228 FND_FILE.PUT_LINE(FND_FILE.LOG, '****** Submitted stage 1 for worker no. '||to_char(i));
4229 l_request_id := FND_REQUEST.SUBMIT_REQUEST(
4230 'AR', 'ARHLSG1W',
4231 'Worker '||i,
4232 SYSDATE,
4233 TRUE,
4234 P_BATCH_ID,
4235 l_content_src_type,
4236 P_RERUN_FLAG,
4237 P_BATCH_MODE_FLAG
4238 );
4239
4240
4241 ELSIF (stage=2) THEN
4242 FND_FILE.PUT_LINE(FND_FILE.LOG, '****** Submitted stage 2 for worker no. '||to_char(i));
4243 l_request_id := FND_REQUEST.SUBMIT_REQUEST(
4244 'AR', 'ARHLSG2W',
4245 'Worker '||i,
4246 SYSDATE,
4247 TRUE,
4248 P_BATCH_ID,
4249 l_content_src_type,
4250 P_RERUN_FLAG,
4251 NVL(P_ERROR_LIMIT, NVL(FND_PROFILE.value('HZ_IMP_ERROR_LIMIT'), 10000)),
4252 P_BATCH_MODE_FLAG,
4253 l_user_id,
4254 --bug 3932987
4255 --D_SYSDATE,
4256 to_char(D_SYSDATE,'DD-MM-YY HH24:MI:SS'),
4257 l_last_update_login,
4258 P_PROGRAM_ID,
4259 P_PROGRAM_APPLICATION_ID,
4260 P_REQUEST_ID,
4261 l_resp_appl_id,
4262 l_g_miss_char,
4263 l_g_miss_num,
4264 l_g_miss_date,
4265 l_flex_validation_prof,
4266 l_dss_security_prof,
4267 l_allow_disabled_lookup_prof,
4268 l_profile_version_prof,
4269 P_WHAT_IF_ANALYSIS,
4270 P_REGISTRY_DEDUP,
4271 P_REGISTRY_DEDUP_MATCH_RULE_ID
4272 );
4273
4274
4275 ELSIF (stage=3) THEN
4276 FND_FILE.PUT_LINE(FND_FILE.LOG, '****** Submitted stage 3 for worker no. '||to_char(i));
4277 l_request_id := FND_REQUEST.SUBMIT_REQUEST(
4278 'AR', 'ARHLSG3W',
4279 'Worker '||i,
4280 SYSDATE,
4281 TRUE,
4282 P_BATCH_ID,
4283 l_content_src_type,
4284 P_RERUN_FLAG,
4285 NVL(P_ERROR_LIMIT, NVL(FND_PROFILE.value('HZ_IMP_ERROR_LIMIT'), 10000)),
4286 P_BATCH_MODE_FLAG,
4287 l_user_id,
4288 --bug 3932987
4289 --D_SYSDATE,
4290 to_char(D_SYSDATE,'DD-MM-YY HH24:MI:SS'),
4291 l_last_update_login,
4292 P_PROGRAM_ID,
4293 P_PROGRAM_APPLICATION_ID,
4294 P_REQUEST_ID,
4295 l_resp_appl_id,
4296 l_g_miss_char,
4297 l_g_miss_num,
4298 l_g_miss_date,
4299 l_flex_validation_prof,
4300 l_dss_security_prof,
4301 l_allow_disabled_lookup_prof,
4302 l_profile_version_prof,
4303 l_update_str_addr_prof,
4304 l_maintain_loc_hist_prof,
4305 l_allow_addr_corr_prof
4306 );
4307 END IF;
4308 if (l_request_id is null or l_request_id=0) then
4309 l_error_message := FND_MESSAGE.get;
4310 errbuf := l_error_message;
4311 retcode := 2;
4312 RETURN;
4313 end if;
4314 END LOOP; -- submitting workers
4315 -- set main program to pause mode
4316 fnd_conc_global.set_req_globals(conc_status => 'PAUSED',
4317 request_data => TO_CHAR(stage)) ;
4318
4319 errbuf := 'Concurrent Workers submitted.';
4320 retcode := 0;
4321 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
4322 hz_utility_v2pub.debug(p_message=>'WRP:DATA_LOAD()-',
4323 p_prefix=>'',
4324 p_msg_level=>fnd_log.level_procedure);
4325 END IF;
4326 RETURN;
4327 EXCEPTION
4328 WHEN OTHERS THEN
4329 IF SQLCODE between -1899 and -1800 THEN
4330 FND_MESSAGE.SET_NAME('AR', 'HZ_IMP_INVALID_DATE_FORMAT');
4331 ELSE
4332 FND_MESSAGE.SET_NAME('AR', 'HZ_API_OTHERS_EXCEP');
4333 FND_MESSAGE.SET_TOKEN('ERROR', SQLERRM);
4334 END IF;
4335 l_error_message := FND_MESSAGE.get;
4336 errbuf := l_error_message;
4337 retcode := 2;
4338 FND_FILE.PUT_LINE(FND_FILE.LOG, '******* Unexpected Error occured in stage '||to_char(stage)||' if worker submitted. Else it may have occured before submitting stage '||to_char(stage)||' in the main wrapper');
4339 FND_FILE.PUT_LINE(FND_FILE.LOG, '******* Unexpected error: ' || SQLERRM);
4340 FND_FILE.PUT_LINE(FND_FILE.LOG, '******* Other errors: '|| l_error_message);
4341
4342 IF stage>=2 AND
4343 ( P_WHAT_IF_ANALYSIS is null
4344 OR (P_WHAT_IF_ANALYSIS is not null AND P_WHAT_IF_ANALYSIS<>'A'))
4345 AND GET_COUNTS(P_BATCH_ID)='Y'
4346 THEN
4347 HZ_IMP_LOAD_BATCH_COUNTS_PKG.post_import_counts(P_BATCH_ID, P_ORIG_SYSTEM,
4348 P_BATCH_MODE_FLAG,P_REQUEST_ID, P_RERUN_FLAG);
4349 END IF;
4350
4351 END DATA_LOAD;
4352
4353 PROCEDURE RETRIEVE_WORK_UNIT(
4354 P_BATCH_ID IN NUMBER,
4355 P_STAGE IN NUMBER,
4356 P_OS IN OUT NOCOPY VARCHAR2,
4357 P_FROM_OSR IN OUT NOCOPY VARCHAR2,
4358 P_TO_OSR IN OUT NOCOPY VARCHAR2,
4359 P_HWM_STAGE OUT NOCOPY NUMBER,
4360 P_PP_STATUS OUT NOCOPY VARCHAR2
4361 ) IS
4362 BEGIN
4363 UPDATE HZ_IMP_WORK_UNITS
4364 SET STATUS = 'P',
4365 STAGE = P_STAGE
4366 WHERE STATUS = 'C'
4367 AND BATCH_ID = P_BATCH_ID
4368 AND STAGE = P_STAGE - 1
4369 AND ROWNUM = 1
4370 RETURNING ORIG_SYSTEM,
4371 FROM_ORIG_SYSTEM_REF,
4372 TO_ORIG_SYSTEM_REF,
4373 HWM_STAGE,
4374 POSTPROCESS_STATUS
4375 INTO P_OS, P_FROM_OSR, P_TO_OSR, P_HWM_STAGE, P_PP_STATUS;
4376 COMMIT;
4377 END RETRIEVE_WORK_UNIT;
4378
4379 /* Retrieve work units that have completed stage 2 or 3 successfully
4380 for postprocessing */
4381 PROCEDURE RETRIEVE_PP_WORK_UNIT(
4382 P_BATCH_ID IN NUMBER,
4383 P_PP_STATUS IN VARCHAR2,
4384 P_OS IN OUT NOCOPY VARCHAR2,
4385 P_FROM_OSR IN OUT NOCOPY VARCHAR2,
4386 P_TO_OSR IN OUT NOCOPY VARCHAR2
4387 ) IS
4388 BEGIN
4389 UPDATE HZ_IMP_WORK_UNITS
4390 SET POSTPROCESS_STATUS = 'P'
4391 WHERE STATUS = 'C'
4392 AND BATCH_ID = P_BATCH_ID
4393 AND STAGE IN (2, 3)
4394 AND ROWNUM = 1
4395 AND NVL(POSTPROCESS_STATUS, 'X') = NVL(P_PP_STATUS, 'X')
4396 RETURNING ORIG_SYSTEM,
4397 FROM_ORIG_SYSTEM_REF,
4398 TO_ORIG_SYSTEM_REF
4399 INTO P_OS, P_FROM_OSR, P_TO_OSR;
4400 COMMIT;
4401 END RETRIEVE_PP_WORK_UNIT;
4402
4403 PROCEDURE GENERATE_ENTITIES_WORK_UNITS(
4404 P_BATCH_ID IN NUMBER,
4405 P_ORIG_SYSTEM IN VARCHAR2
4406 ) IS
4407
4408 ENTITIES_WORK_UNIT_H_DML VARCHAR2(32767) :=
4409 'begin INSERT into hz_imp_work_units
4410 ( batch_id,
4411 orig_system,
4412 from_orig_system_ref,
4413 to_orig_system_ref,
4414 status,
4415 stage
4416 )
4417 ( select :1,
4418 :2,
4419 min(party_orig_system_reference),
4420 max(party_orig_system_reference),
4421 ''C'',
4422 0
4423 from
4424 ( select party_orig_system_reference,
4425 floor(sum(count(*)) over
4426 ( order by
4427 party_orig_system_reference
4428 rows unbounded preceding
4429 )/:3
4430 ) wu
4431 from
4432 ( -- Party
4433 select /*+ index_ffs(a,hz_imp_parties_int_u1) parallel_index(a)*/
4434 party_orig_system_reference
4435 from hz_imp_parties_int a
4436 where party_orig_system = :2
4437 and batch_id=:1
4438 union all -- Party Site
4439 select /*+ index_ffs(a,hz_imp_addresses_int_n1) parallel_index(a)*/
4440 party_orig_system_reference
4441 from hz_imp_addresses_int a
4442 where party_orig_system = :2
4443 and batch_id=:1
4444 union all -- Contact Points
4445 select /*+ index_ffs(a,hz_imp_contactpts_int_n1) parallel_index(a)*/
4446 party_orig_system_reference
4447 from hz_imp_contactpts_int a
4448 where party_orig_system = :2
4449 and batch_id=:1
4450 union all -- Credit Ratings
4451 select /*+ index_ffs(a,hz_imp_creditrtngs_int_n1) parallel_index(a)*/
4452 party_orig_system_reference
4453 from hz_imp_creditrtngs_int a
4454 where party_orig_system = :2
4455 and batch_id=:1
4456 union all -- Financial Reports
4457 select /*+ index_ffs(a,hz_imp_finreports_int_n1) parallel_index(a)*/
4458 party_orig_system_reference
4459 from hz_imp_finreports_int a
4460 where party_orig_system = :2
4461 and batch_id=:1
4462 union all -- Financial Numbers
4463 select /*+ index_ffs(a,hz_imp_finnumbers_int_n1) parallel_index(a)*/
4464 party_orig_system_reference
4465 from hz_imp_finnumbers_int a
4466 where party_orig_system = :2
4467 and batch_id=:1
4468 union all -- Code Assignments
4469 select /*+ index_ffs(a,hz_imp_classifics_int_n1) parallel_index(a)*/
4470 party_orig_system_reference
4471 from hz_imp_classifics_int a
4472 where party_orig_system = :2
4473 and batch_id=:1
4474 union all -- Relationships
4475 select /*+ index_ffs(a,hz_imp_relships_int_n1) parallel_index(a)*/
4476 sub_orig_system_reference party_orig_system_reference
4477 from hz_imp_relships_int a
4478 where sub_orig_system = :2
4479 and batch_id=:1
4480 union all -- Contacts
4481 select /*+ index_ffs(a,hz_imp_contacts_int_n1) parallel_index(a)*/
4482 sub_orig_system_reference party_orig_system_reference
4483 from hz_imp_contacts_int a
4484 where sub_orig_system = :2
4485 and batch_id=:1
4486 union all -- Contact Roles
4487 select /*+ index_ffs(a,hz_imp_contactroles_int_n1)
4488 parallel_index(a)*/
4489 sub_orig_system_reference party_orig_system_reference
4490 from hz_imp_contactroles_int a
4491 where sub_orig_system = :2
4492 and batch_id=:1
4493 union all -- Address Uses
4494 select /*+ index_ffs(a,hz_imp_addressuses_int_n1) parallel_index(a)*/
4495 party_orig_system_reference
4496 from hz_imp_addressuses_int a
4497 where party_orig_system = :2
4498 and batch_id=:1
4499 )
4500 group by party_orig_system_reference
4501 )
4502 group by wu
4503 ); end;';
4504
4505 ENTITIES_WORK_UNIT_L_DML VARCHAR2(32767) :=
4506 'begin INSERT into hz_imp_work_units
4507 ( batch_id,
4508 orig_system,
4509 from_orig_system_ref,
4510 to_orig_system_ref,
4511 status,
4512 stage
4513 )
4514 ( select :1,
4515 :2,
4516 min(party_orig_system_reference),
4517 max(party_orig_system_reference),
4518 ''C'',
4519 0
4520 from
4521 ( select party_orig_system_reference,
4522 floor(sum(count(*)) over
4523 ( order by
4524 party_orig_system_reference
4525 rows unbounded preceding
4526 )/:3
4527 ) wu
4528 from
4529 ( -- Party
4530 select /*+ index(a,hz_imp_parties_int_u1) */
4531 party_orig_system_reference
4532 from hz_imp_parties_int a
4533 where party_orig_system = :2
4534 and batch_id=:1
4535 union all -- Party Site
4536 select /*+ index(a,hz_imp_addresses_int_n1) */
4537 party_orig_system_reference
4538 from hz_imp_addresses_int a
4539 where party_orig_system = :2
4540 and batch_id=:1
4541 union all -- Contact Points
4542 select /*+ index(a,hz_imp_contactpts_int_n1) */
4543 party_orig_system_reference
4544 from hz_imp_contactpts_int a
4545 where party_orig_system = :2
4546 and batch_id=:1
4547 union all -- Credit Ratings
4548 select /*+ index(a,hz_imp_creditrtngs_int_n1) */
4549 party_orig_system_reference
4550 from hz_imp_creditrtngs_int a
4551 where party_orig_system = :2
4552 and batch_id=:1
4553 union all -- Financial Reports
4554 select /*+ index(a,hz_imp_finreports_int_n1) */
4555 party_orig_system_reference
4556 from hz_imp_finreports_int a
4557 where party_orig_system = :2
4558 and batch_id=:1
4559 union all -- Financial Numbers
4560 select /*+ index(a,hz_imp_finnumbers_int_n1) */
4561 party_orig_system_reference
4562 from hz_imp_finnumbers_int a
4563 where party_orig_system = :2
4564 and batch_id=:1
4565 union all -- Code Assignments
4566 select /*+ index(a,hz_imp_classifics_int_n1) */
4567 party_orig_system_reference
4568 from hz_imp_classifics_int a
4569 where party_orig_system = :2
4570 and batch_id=:1
4571 union all -- Relationships
4572 select /*+ index(a,hz_imp_relships_int_n1) */
4573 sub_orig_system_reference party_orig_system_reference
4574 from hz_imp_relships_int a
4575 where sub_orig_system = :2
4576 and batch_id=:1
4577 union all -- Contacts
4578 select /*+ index(a,hz_imp_contacts_int_n1) */
4579 sub_orig_system_reference party_orig_system_reference
4580 from hz_imp_contacts_int a
4581 where sub_orig_system = :2
4582 and batch_id=:1
4583 union all -- Contact Roles
4584 select /*+ index(a,hz_imp_contactroles_int_n1) */
4585 sub_orig_system_reference party_orig_system_reference
4586 from hz_imp_contactroles_int a
4587 where sub_orig_system = :2
4588 and batch_id=:1
4589 union all -- Address Uses
4590 select /*+ index(a,hz_imp_addressuses_int_n1) */
4591 party_orig_system_reference
4592 from hz_imp_addressuses_int a
4593 where party_orig_system = :2
4594 and batch_id=:1
4595 )
4596 group by party_orig_system_reference
4597 )
4598 group by wu
4599 ); end;';
4600
4601 P_VOLUME_FLAG VARCHAR2(1) := 'H'; -- 'H' High Volume, 'L' Low Volume
4602 WORK_UNIT_DML VARCHAR2(32767) := ENTITIES_WORK_UNIT_H_DML;
4603 profile_unit_size VARCHAR2(10);
4604 unit_size NUMBER;
4605 est_count NUMBER;
4606 party_count NUMBER;
4607 l_bool BOOLEAN;
4608 l_status VARCHAR2(255);
4609 l_schema VARCHAR2(255);
4610 l_tmp VARCHAR2(2000);
4611
4612 BEGIN
4613
4614 -- retrieve profile option of work unit size
4615 profile_unit_size := NVL(FND_PROFILE.value('HZ_IMP_WORK_UNIT_SIZE'),
4616 to_char(WORK_UNIT_CAP_SIZE));
4617 unit_size := to_number(profile_unit_size);
4618 if(unit_size>WORK_UNIT_CAP_SIZE or
4619 unit_size<=0) then
4620 unit_size := WORK_UNIT_CAP_SIZE;
4621 end if;
4622
4623 --dbms_output.put_line('unit size:'||unit_size);
4624
4625 -- compute volume flag
4626 begin
4627 select est_no_of_records into est_count
4628 from HZ_IMP_BATCH_SUMMARY where batch_id = P_BATCH_ID;
4629 exception
4630 when others then
4631 est_count := null;
4632 end;
4633
4634 l_bool := fnd_installation.GET_APP_INFO('AR',l_status,l_tmp,l_schema);
4635
4636 select num_rows into party_count
4637 from sys.dba_tables where upper(table_name) = 'HZ_IMP_PARTIES_INT'
4638 and owner = l_schema;
4639
4640 if(est_count is not null and party_count is not null) then
4641 if party_count>(est_count*0.15) and party_count>50000 then
4642 P_VOLUME_FLAG := 'H';
4643 else
4644 P_VOLUME_FLAG := 'L';
4645 end if;
4646 end if;
4647
4648 if(P_VOLUME_FLAG='L') then
4649 WORK_UNIT_DML := ENTITIES_WORK_UNIT_L_DML;
4650 end if;
4651 EXECUTE IMMEDIATE WORK_UNIT_DML USING P_BATCH_ID,
4652 P_ORIG_SYSTEM,
4653 unit_size;
4654 COMMIT;
4655
4656 END GENERATE_ENTITIES_WORK_UNITS;
4657
4658
4659 FUNCTION STAGING_DATA_EXISTS(
4660 P_BATCH_ID IN NUMBER,
4661 P_BATCH_MODE_FLAG IN VARCHAR2,
4662 P_STAGE IN NUMBER
4663 ) RETURN VARCHAR2 IS
4664
4665 CURSOR c_what_if_sg_data(p_batch_id number, p_batch_mode_flag varchar2) IS
4666 SELECT 'Y'
4667 FROM dual
4668 WHERE EXISTS (
4669 SELECT 'Y'
4670 FROM HZ_IMP_PARTIES_SG
4671 WHERE batch_id = p_batch_id
4672 AND batch_mode_flag = p_batch_mode_flag
4673 AND rownum = 1)
4674 OR EXISTS (
4675 SELECT 'Y'
4676 FROM HZ_IMP_ADDRESSES_SG
4677 WHERE batch_id = p_batch_id
4678 AND batch_mode_flag = p_batch_mode_flag
4679 AND rownum = 1)
4680 OR EXISTS (
4681 SELECT 'Y'
4682 FROM HZ_IMP_CONTACTPTS_SG
4683 WHERE batch_id = p_batch_id
4684 AND batch_mode_flag = p_batch_mode_flag
4685 AND rownum = 1)
4686 OR EXISTS (
4687 SELECT 'Y'
4688 FROM HZ_IMP_CREDITRTNGS_SG
4689 WHERE batch_id = p_batch_id
4690 AND batch_mode_flag = p_batch_mode_flag
4691 AND rownum = 1)
4692 OR EXISTS (
4693 SELECT 'Y'
4694 FROM HZ_IMP_FINREPORTS_SG
4695 WHERE batch_id = p_batch_id
4696 AND batch_mode_flag = p_batch_mode_flag
4697 AND rownum = 1)
4698 OR EXISTS (
4699 SELECT 'Y'
4700 FROM HZ_IMP_FINNUMBERS_SG
4701 WHERE batch_id = p_batch_id
4702 AND batch_mode_flag = p_batch_mode_flag
4703 AND rownum = 1)
4704 OR EXISTS (
4705 SELECT 'Y'
4706 FROM HZ_IMP_CLASSIFICS_SG
4707 WHERE batch_id = p_batch_id
4708 AND batch_mode_flag = p_batch_mode_flag
4709 AND rownum = 1)
4710 OR EXISTS (
4711 SELECT 'Y'
4712 FROM HZ_IMP_RELSHIPS_SG
4713 WHERE batch_id = p_batch_id
4714 AND batch_mode_flag = p_batch_mode_flag
4715 AND rownum = 1)
4716 OR EXISTS (
4717 SELECT 'Y'
4718 FROM HZ_IMP_CONTACTROLES_SG
4719 WHERE batch_id = p_batch_id
4720 AND batch_mode_flag = p_batch_mode_flag
4721 AND rownum = 1)
4722 OR EXISTS (
4723 SELECT 'Y'
4724 FROM HZ_IMP_CONTACTS_SG
4725 WHERE batch_id = p_batch_id
4726 AND batch_mode_flag = p_batch_mode_flag
4727 AND rownum = 1)
4728 OR EXISTS (
4729 SELECT 'Y'
4730 FROM HZ_IMP_ADDRESSUSES_SG
4731 WHERE batch_id = p_batch_id
4732 AND batch_mode_flag = p_batch_mode_flag
4733 AND rownum = 1);
4734
4735 CURSOR c_what_if_sg_data2(p_batch_id number, p_batch_mode_flag varchar2) IS
4736
4737 SELECT 'Y'
4738 FROM dual
4739 WHERE EXISTS (
4740 SELECT 'Y'
4741 FROM HZ_IMP_ADDRESSES_SG
4742 WHERE batch_id = p_batch_id
4743 AND batch_mode_flag = p_batch_mode_flag
4744 AND rownum = 1)
4745 OR EXISTS (
4746 SELECT 'Y'
4747 FROM HZ_IMP_CONTACTPTS_SG
4748 WHERE batch_id = p_batch_id
4749 AND batch_mode_flag = p_batch_mode_flag
4750 AND rownum = 1)
4751 OR EXISTS (
4752 SELECT 'Y'
4753 FROM HZ_IMP_CREDITRTNGS_SG
4754 WHERE batch_id = p_batch_id
4755 AND batch_mode_flag = p_batch_mode_flag
4756 AND rownum = 1)
4757 OR EXISTS (
4758 SELECT 'Y'
4759 FROM HZ_IMP_FINREPORTS_SG
4760 WHERE batch_id = p_batch_id
4761 AND batch_mode_flag = p_batch_mode_flag
4762 AND rownum = 1)
4763 OR EXISTS (
4764 SELECT 'Y'
4765 FROM HZ_IMP_FINNUMBERS_SG
4766 WHERE batch_id = p_batch_id
4767 AND batch_mode_flag = p_batch_mode_flag
4768 AND rownum = 1)
4769 OR EXISTS (
4770 SELECT 'Y'
4771 FROM HZ_IMP_CLASSIFICS_SG
4772 WHERE batch_id = p_batch_id
4773 AND batch_mode_flag = p_batch_mode_flag
4774 AND rownum = 1)
4775 OR EXISTS (
4776 SELECT 'Y'
4777 FROM HZ_IMP_RELSHIPS_SG
4778 WHERE batch_id = p_batch_id
4779 AND batch_mode_flag = p_batch_mode_flag
4780 AND rownum = 1)
4781 OR EXISTS (
4782 SELECT 'Y'
4783 FROM HZ_IMP_CONTACTROLES_SG
4784 WHERE batch_id = p_batch_id
4785 AND batch_mode_flag = p_batch_mode_flag
4786 AND rownum = 1)
4787 OR EXISTS (
4788 SELECT 'Y'
4789 FROM HZ_IMP_CONTACTS_SG
4790 WHERE batch_id = p_batch_id
4791 AND batch_mode_flag = p_batch_mode_flag
4792 AND rownum = 1)
4793 OR EXISTS (
4794 SELECT 'Y'
4795 FROM HZ_IMP_ADDRESSUSES_SG
4796 WHERE batch_id = p_batch_id
4797 AND batch_mode_flag = p_batch_mode_flag
4798 AND rownum = 1);
4799
4800 l_what_if_sg_data_exists VARCHAR2(1);
4801
4802 BEGIN
4803
4804 IF P_STAGE <= 1 THEN
4805 OPEN c_what_if_sg_data(P_BATCH_ID, P_BATCH_MODE_FLAG);
4806 FETCH c_what_if_sg_data INTO l_what_if_sg_data_exists;
4807 CLOSE c_what_if_sg_data;
4808 ELSE
4809 OPEN c_what_if_sg_data2(P_BATCH_ID, P_BATCH_MODE_FLAG);
4810 FETCH c_what_if_sg_data2 INTO l_what_if_sg_data_exists;
4811 CLOSE c_what_if_sg_data2;
4812 END IF;
4813
4814 RETURN NVL(l_what_if_sg_data_exists, 'N');
4815 END STAGING_DATA_EXISTS;
4816
4817 PROCEDURE CHECK_INVALID_PARTY(
4818 P_BATCH_ID IN NUMBER,
4819 P_REQUEST_ID IN NUMBER,
4820 P_USER_ID IN NUMBER,
4821 P_LAST_UPDATE_LOGIN IN NUMBER,
4822 P_PROGRAM_ID IN NUMBER,
4823 P_PROGRAM_APPLICATION_ID IN NUMBER,
4824 X_RETURN_STATUS OUT NOCOPY VARCHAR2)
4825 IS
4826
4827
4828 l_row_id T_ROWID;
4829 l_error_id T_ERROR_ID;
4830 l_party_id T_ERROR_ID;
4831 l_sysdate DATE := sysdate;
4832
4833 CURSOR c_invalid_party(L_BATCH_ID NUMBER) IS
4834 SELECT rowid,party_id
4835 FROM hz_imp_parties_int hip
4836 WHERE
4837 batch_id=L_BATCH_ID
4838 AND hip.party_id is not null
4839 AND not exists
4840 (select 1
4841 from hz_parties hp
4842 where hp.party_id=hip.party_id
4843 );
4844
4845 BEGIN
4846
4847 OPEN c_invalid_party(P_BATCH_ID);
4848 FETCH c_invalid_party BULK COLLECT INTO l_row_id,l_party_id;
4849
4850 IF l_party_id.count=0
4851 THEN
4852 x_return_status := 'S';
4853 ELSE
4854 x_return_status := 'E';
4855
4856 FORALL i in l_party_id.first..l_party_id.last
4857 INSERT into hz_imp_errors (
4858 creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id,
4859 program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name,
4860 token1_name, token1_value)
4861 values (
4862 l_sysdate, P_USER_ID, l_sysdate, P_USER_ID, P_LAST_UPDATE_LOGIN, P_PROGRAM_APPLICATION_ID,
4863 P_PROGRAM_ID, l_sysdate, HZ_IMP_ERRORS_S.NextVal, P_BATCH_ID, P_REQUEST_ID, 'HZ_IMP_PARTIES_INT',
4864 'HZ_IMP_INVALID_PARTY_ID','PARTY_ID',l_party_id(i))
4865 RETURNING error_id BULK COLLECT INTO l_error_id;
4866
4867
4868 FORALL i in l_row_id.first..l_row_id.last
4869 UPDATE HZ_IMP_PARTIES_INT
4870 SET INTERFACE_STATUS='E',
4871 ERROR_ID=l_error_id(i)
4872 WHERE ROWID=l_row_id(i);
4873
4874 END IF;
4875 CLOSE c_invalid_party;
4876 END CHECK_INVALID_PARTY;
4877
4878 END HZ_IMP_LOAD_WRAPPER;