[Home] [Help]
PACKAGE BODY: APPS.HZ_IMP_LOAD_RELATIONSHIPS_PKG
Source
1 PACKAGE BODY HZ_IMP_LOAD_RELATIONSHIPS_PKG AS
2 /*$Header: ARHLRELB.pls 120.36 2006/02/04 01:21:07 achung noship $*/
3
4 g_debug_count NUMBER := 0;
5 --g_debug BOOLEAN := FALSE;
6
7 l_action_mismatch_errors FLAG_ERROR;
8 l_flex_val_errors NUMBER_COLUMN;
9 l_dss_security_errors FLAG_COLUMN;
10
11 l_row_id ROWID;
12 l_relationship_id RELATIONSHIP_ID;
13 l_subject_id PARTY_ID;
14 l_object_id PARTY_ID;
15 l_relationship_type RELATIONSHIP_TYPE;
16
17 l_comments COMMENTS;
18 l_attr_category ATTRIBUTE_CATEGORY;
19 l_attr1 ATTRIBUTE;
20 l_attr2 ATTRIBUTE;
21 l_attr3 ATTRIBUTE;
22 l_attr4 ATTRIBUTE;
23 l_attr5 ATTRIBUTE;
24 l_attr6 ATTRIBUTE;
25 l_attr7 ATTRIBUTE;
26 l_attr8 ATTRIBUTE;
27 l_attr9 ATTRIBUTE;
28 l_attr10 ATTRIBUTE;
29 l_attr11 ATTRIBUTE;
30 l_attr12 ATTRIBUTE;
31 l_attr13 ATTRIBUTE;
32 l_attr14 ATTRIBUTE;
33 l_attr15 ATTRIBUTE;
34 l_attr16 ATTRIBUTE;
35 l_attr17 ATTRIBUTE;
36 l_attr18 ATTRIBUTE;
37 l_attr19 ATTRIBUTE;
38 l_attr20 ATTRIBUTE;
39 l_exception_exists FLAG_ERROR;
40
41 l_errm varchar2(100);
42
43 /* Keep track of rows that do not get inserted or updated successfully.
44 Those are the rows that have some validation or DML errors.
45 Use this when inserting into or updating other tables so that we
46 do not need to check all the validation arrays. */
47 l_num_row_processed NUMBER_COLUMN;
48
49 l_no_end_date DATE;
50 l_actual_content_source varchar2(100);
51
52 TYPE CREATED_BY_MODULE IS TABLE OF HZ_IMP_RELSHIPS_INT.CREATED_BY_MODULE%TYPE;
53 TYPE LOOKUP_ERROR IS TABLE OF ar_lookups.lookup_code%TYPE;
54
55 l_created_by_module CREATED_BY_MODULE;
56 l_createdby_errors LOOKUP_ERROR;
57
58
59
60
61 /*PROCEDURE enable_debug IS
62 BEGIN
63 g_debug_count := g_debug_count + 1;
64
65 IF g_debug_count = 1 THEN
66 IF fnd_profile.value('HZ_API_FILE_DEBUG_ON') = 'Y' OR
67 fnd_profile.value('HZ_API_DBMS_DEBUG_ON') = 'Y'
68 THEN
69 hz_utility_v2pub.enable_debug;
70 g_debug := TRUE;
71 END IF;
72 END IF;
73 END enable_debug;
74
75 PROCEDURE disable_debug IS
76 BEGIN
77
78 IF g_debug THEN
79 g_debug_count := g_debug_count - 1;
80 IF g_debug_count = 0 THEN
81 hz_utility_v2pub.disable_debug;
82 g_debug := FALSE;
83 END IF;
84 END IF;
85
86 END disable_debug;
87 */
88
89
90 /* Validate desc flexfield HZ_RELATIONSHIPS. Used only when updating
91 parties. If invalid, set l_flex_val_errors(i) to 1. Else do nothing. */
92 PROCEDURE validate_desc_flexfield(
93 p_validation_date IN DATE
94 ) IS
95 l_flex_exists VARCHAR2(1);
96
97 BEGIN
98
99 FOR i IN 1..l_relationship_id.count LOOP
100
101 FND_FLEX_DESCVAL.set_context_value(l_attr_category(i));
102
103 FND_FLEX_DESCVAL.set_column_value('ATTRIBUTE1', l_attr1(i));
104 FND_FLEX_DESCVAL.set_column_value('ATTRIBUTE2', l_attr2(i));
105 FND_FLEX_DESCVAL.set_column_value('ATTRIBUTE3', l_attr3(i));
106 FND_FLEX_DESCVAL.set_column_value('ATTRIBUTE4', l_attr4(i));
107 FND_FLEX_DESCVAL.set_column_value('ATTRIBUTE5', l_attr5(i));
108 FND_FLEX_DESCVAL.set_column_value('ATTRIBUTE6', l_attr6(i));
109 FND_FLEX_DESCVAL.set_column_value('ATTRIBUTE7', l_attr7(i));
110 FND_FLEX_DESCVAL.set_column_value('ATTRIBUTE8', l_attr8(i));
111 FND_FLEX_DESCVAL.set_column_value('ATTRIBUTE9', l_attr9(i));
112 FND_FLEX_DESCVAL.set_column_value('ATTRIBUTE10', l_attr10(i));
113 FND_FLEX_DESCVAL.set_column_value('ATTRIBUTE11', l_attr11(i));
114 FND_FLEX_DESCVAL.set_column_value('ATTRIBUTE12', l_attr12(i));
115 FND_FLEX_DESCVAL.set_column_value('ATTRIBUTE13', l_attr13(i));
116 FND_FLEX_DESCVAL.set_column_value('ATTRIBUTE14', l_attr14(i));
117 FND_FLEX_DESCVAL.set_column_value('ATTRIBUTE15', l_attr15(i));
118 FND_FLEX_DESCVAL.set_column_value('ATTRIBUTE16', l_attr16(i));
119 FND_FLEX_DESCVAL.set_column_value('ATTRIBUTE17', l_attr17(i));
120 FND_FLEX_DESCVAL.set_column_value('ATTRIBUTE18', l_attr18(i));
121 FND_FLEX_DESCVAL.set_column_value('ATTRIBUTE19', l_attr19(i));
122 FND_FLEX_DESCVAL.set_column_value('ATTRIBUTE20', l_attr20(i));
123
124 IF (NOT FND_FLEX_DESCVAL.validate_desccols(
125 'AR',
126 'HZ_RELATIONSHIPS',
127 'V',
128 p_validation_date)) THEN
129 l_flex_val_errors(i) := 1;
130 END IF;
131
132 END LOOP;
133
134 END validate_desc_flexfield;
135
136
137 /* Validate desc flexfield HZ_RELATIONSHIPS. Used only when inserting
138 new parties because need to have a function to be called in MTI.
139 Returns Y if flexfield is valid. Returns null if invalid. */
140 FUNCTION validate_desc_flexfield_f(
141 p_attr_category IN VARCHAR2,
142 p_attr1 IN VARCHAR2,
143 p_attr2 IN VARCHAR2,
144 p_attr3 IN VARCHAR2,
145 p_attr4 IN VARCHAR2,
146 p_attr5 IN VARCHAR2,
147 p_attr6 IN VARCHAR2,
148 p_attr7 IN VARCHAR2,
149 p_attr8 IN VARCHAR2,
150 p_attr9 IN VARCHAR2,
151 p_attr10 IN VARCHAR2,
152 p_attr11 IN VARCHAR2,
153 p_attr12 IN VARCHAR2,
154 p_attr13 IN VARCHAR2,
155 p_attr14 IN VARCHAR2,
156 p_attr15 IN VARCHAR2,
157 p_attr16 IN VARCHAR2,
158 p_attr17 IN VARCHAR2,
159 p_attr18 IN VARCHAR2,
160 p_attr19 IN VARCHAR2,
161 p_attr20 IN VARCHAR2,
162 p_validation_date IN DATE
163 ) RETURN VARCHAR2 IS
164 BEGIN
165
166 FND_FLEX_DESCVAL.set_context_value(p_attr_category);
167
168 FND_FLEX_DESCVAL.set_column_value('ATTRIBUTE1', p_attr1);
169 FND_FLEX_DESCVAL.set_column_value('ATTRIBUTE2', p_attr2);
170 FND_FLEX_DESCVAL.set_column_value('ATTRIBUTE3', p_attr3);
171 FND_FLEX_DESCVAL.set_column_value('ATTRIBUTE4', p_attr4);
172 FND_FLEX_DESCVAL.set_column_value('ATTRIBUTE5', p_attr5);
173 FND_FLEX_DESCVAL.set_column_value('ATTRIBUTE6', p_attr6);
174 FND_FLEX_DESCVAL.set_column_value('ATTRIBUTE7', p_attr7);
175 FND_FLEX_DESCVAL.set_column_value('ATTRIBUTE8', p_attr8);
176 FND_FLEX_DESCVAL.set_column_value('ATTRIBUTE9', p_attr9);
177 FND_FLEX_DESCVAL.set_column_value('ATTRIBUTE10', p_attr10);
178 FND_FLEX_DESCVAL.set_column_value('ATTRIBUTE11', p_attr11);
179 FND_FLEX_DESCVAL.set_column_value('ATTRIBUTE12', p_attr12);
180 FND_FLEX_DESCVAL.set_column_value('ATTRIBUTE13', p_attr13);
181 FND_FLEX_DESCVAL.set_column_value('ATTRIBUTE14', p_attr14);
182 FND_FLEX_DESCVAL.set_column_value('ATTRIBUTE15', p_attr15);
183 FND_FLEX_DESCVAL.set_column_value('ATTRIBUTE16', p_attr16);
184 FND_FLEX_DESCVAL.set_column_value('ATTRIBUTE17', p_attr17);
185 FND_FLEX_DESCVAL.set_column_value('ATTRIBUTE18', p_attr18);
186 FND_FLEX_DESCVAL.set_column_value('ATTRIBUTE19', p_attr19);
187 FND_FLEX_DESCVAL.set_column_value('ATTRIBUTE20', p_attr20);
188
189 IF (FND_FLEX_DESCVAL.validate_desccols(
190 'AR',
191 'HZ_RELATIONSHIPS',
192 'V',
193 p_validation_date)) THEN
194 return 'Y';
195 ELSE
196 return null;
197 END IF;
198
199 END validate_desc_flexfield_f;
200
201
202 PROCEDURE validate_DSS_security IS
203 dss_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
204 dss_msg_count NUMBER := 0;
205 dss_msg_data VARCHAR2(2000):= null;
206 l_debug_prefix VARCHAR2(30) := '';
207
208 BEGIN
209 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
210 hz_utility_v2pub.debug(p_message=>'REL:validate_DSS_security()+',
211 p_prefix=>l_debug_prefix,
212 p_msg_level=>fnd_log.level_procedure);
213 END IF;
214 /* Check if the DSS security is granted to the user.
215 Only check for update. */
216 FOR i IN 1..l_relationship_id.count LOOP
217 l_dss_security_errors(i) :=
218 hz_dss_util_pub.test_instance(
219 p_operation_code => 'UPDATE',
220 p_db_object_name => 'HZ_RELATIONSHIPS',
221 p_instance_pk1_value => l_relationship_id(i),
222 p_instance_pk2_value => 'F',
223 p_user_name => fnd_global.user_name,
224 x_return_status => dss_return_status,
225 x_msg_count => dss_msg_count,
226 x_msg_data => dss_msg_data);
227
228 END LOOP;
229
230 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
231 hz_utility_v2pub.debug(p_message=>'REL:validate_DSS_security()-',
232 p_prefix=>l_debug_prefix,
233 p_msg_level=>fnd_log.level_procedure);
234 END IF;
235 END validate_DSS_security;
236
237
238 /* Only used when updating parties */
239 PROCEDURE report_errors(
240 P_DML_RECORD IN HZ_IMP_LOAD_WRAPPER.DML_RECORD_TYPE,
241 P_ACTION IN VARCHAR2,
242 P_DML_EXCEPTION IN VARCHAR2
243 ) IS
244 num_exp NUMBER;
245 exp_ind NUMBER := 1;
246 l_debug_prefix VARCHAR2(30) := '';
247 l_exception_exists FLAG_ERROR;
248 BEGIN
249
250 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
251 hz_utility_v2pub.debug(p_message=>'REL:report_errors()+',
252 p_prefix=>l_debug_prefix,
253 p_msg_level=>fnd_log.level_procedure);
254 END IF;
255
256 /**********************************/
257 /* Validation and Error reporting */
258 /**********************************/
259 IF l_relationship_id.count = 0 THEN
260 return;
261 END IF;
262
263 l_num_row_processed := null;
264 l_num_row_processed := NUMBER_COLUMN();
265 l_num_row_processed.extend(l_relationship_id.count);
266 l_exception_exists := null;
267 l_exception_exists := FLAG_ERROR();
268 l_exception_exists.extend(l_relationship_id.count);
269 num_exp := SQL%BULK_EXCEPTIONS.COUNT;
270
271 FOR k IN 1..l_relationship_id.count LOOP
272
273 IF SQL%BULK_ROWCOUNT(k) = 0 THEN
274 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
275 hz_utility_v2pub.debug(p_message=>'DML fails at ' || k,
276 p_prefix=>'ERROR',
277 p_msg_level=>fnd_log.level_error);
278 END IF;
279 l_num_row_processed(k) := 0;
280
281 /* Check for any exceptions during DML */
282 IF P_DML_EXCEPTION = 'Y' THEN
283 /* determine if exception at this index */
284 FOR i IN exp_ind..num_exp LOOP
285 IF SQL%BULK_EXCEPTIONS(i).ERROR_INDEX = k THEN
286 l_exception_exists(k) := 'Y';
287 ELSIF SQL%BULK_EXCEPTIONS(i).ERROR_INDEX > k THEN
288 EXIT;
289 END IF;
290 END LOOP;
291 END IF; /* P_DML_EXCEPTION = 'Y' */
292
293 ELSE
294 l_num_row_processed(k) := 1;
295 END IF; /* SQL%BULK_ROWCOUNT(k) = 0 */
296 END LOOP;
297
298 /* insert into tmp error tables */
299 forall j in 1..l_relationship_id.count
300 insert into hz_imp_tmp_errors
301 (
302 request_id,
303 batch_id,
304 int_row_id,
305 interface_table_name,
306 error_id,
307 creation_date,
308 created_by,
309 last_update_date,
310 last_updated_by,
311 last_update_login,
312 program_application_id,
313 program_id,
314 program_update_date,
315 e1_flag,e2_flag,e3_flag,
316 e9_flag,e10_flag,
317 e11_flag,
318 action_mismatch_flag,
319 OTHER_EXCEP_FLAG
320 )
321 (
322 select P_DML_RECORD.REQUEST_ID,
323 P_DML_RECORD.BATCH_ID,
324 l_row_id(j),
325 'HZ_IMP_FINREPORTS_INT',
326 HZ_IMP_ERRORS_S.NextVal,
327 P_DML_RECORD.SYSDATE,
328 P_DML_RECORD.USER_ID,
329 P_DML_RECORD.SYSDATE,
330 P_DML_RECORD.USER_ID,
331 P_DML_RECORD.LAST_UPDATE_LOGIN,
332 P_DML_RECORD.PROGRAM_APPLICATION_ID,
333 P_DML_RECORD.PROGRAM_ID,
334 P_DML_RECORD.SYSDATE,
335 nvl2(l_subject_id(j), 'Y', null), --HZ_IMP_REL_SUBJ_OBJ_ERROR,SUB_OR_OBJ,SUBJECT
336 nvl2(l_object_id(j), 'Y', null), --HZ_IMP_REL_SUBJ_OBJ_ERROR,SUB_OR_OBJ,OBJECT
337 decode(l_subject_id(j), null, 'Y',
338 decode(l_object_id(j), null, 'Y', nvl2(l_relationship_type(j), 'Y', null))), --HZ_IMP_REL_TYPE_ERROR
339 decode(l_flex_val_errors(j), 1, null, 'Y'), --AR_RAPI_DESC_FLEX_INVALID,DFF_NAME,HZ_RELATIONSHIPS
340 decode(l_dss_security_errors(j), FND_API.G_TRUE,'Y',null), --HZ_DSS_SECURITY_FAIL,USER_NAME,FND_GLOBAL.user_name,OPER_NAME,UPDATE,OBJECT_NAME,HZ_RELATIONSHIPS
341 'Y',
342 nvl2(l_action_mismatch_errors(j), 'Y', null), --HZ_IMP_ACTION_MISMATCH
343 l_exception_exists(j)
344 from dual
345 where l_num_row_processed(j) = 0
346 );
347
348 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
349 hz_utility_v2pub.debug(p_message=>'REL:report_errors()-',
350 p_prefix=>l_debug_prefix,
351 p_msg_level=>fnd_log.level_procedure);
352 END IF;
353 END report_errors;
354
355
356 /* Populate error table when exception happens during updating or
357 inserting parties. */
358 PROCEDURE populate_error_table(
359 P_DML_RECORD IN HZ_IMP_LOAD_WRAPPER.DML_RECORD_TYPE,
360 P_DUP_VAL_EXP IN VARCHAR2,
361 P_SQL_ERRM IN VARCHAR2 ) IS
362
363 dup_val_exp_val VARCHAR2(1) := null;
364 other_exp_val VARCHAR2(1) := 'Y';
365 l_debug_prefix VARCHAR2(30) := '';
366 BEGIN
367
368 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
369 hz_utility_v2pub.debug(p_message=>'REL:populate_error_table()+',
370 p_prefix=>l_debug_prefix,
371 p_msg_level=>fnd_log.level_procedure);
372 END IF;
373
374 /* other entities need to add checking for other constraints */
375 if (P_DUP_VAL_EXP = 'Y') then
376 other_exp_val := null;
377 dup_val_exp_val := 'A';
378 end if;
379
380 insert into hz_imp_tmp_errors
381 (
382 request_id,
383 batch_id,
384 int_row_id,
385 interface_table_name,
386 error_id,
387 creation_date,
388 created_by,
389 last_update_date,
390 last_updated_by,
391 last_update_login,
392 program_application_id,
393 program_id,
394 program_update_date,
395 e1_flag,
396 e2_flag,
397 e3_flag,
398 e4_flag,
399 e5_flag,
400 e6_flag,
401 e7_flag,
402 e8_flag,
403 e9_flag,
404 e10_flag,
405 e11_flag,
406 ACTION_MISMATCH_FLAG,
407 DUP_VAL_IDX_EXCEP_FLAG,
408 OTHER_EXCEP_FLAG
409 )
410 (
411 select P_DML_RECORD.REQUEST_ID,
412 P_DML_RECORD.BATCH_ID,
413 rel_sg.int_row_id,
414 'HZ_IMP_RELSHIPS_INT',
418 P_DML_RECORD.SYSDATE,
415 HZ_IMP_ERRORS_S.NextVal,
416 P_DML_RECORD.SYSDATE,
417 P_DML_RECORD.USER_ID,
419 P_DML_RECORD.USER_ID,
420 P_DML_RECORD.LAST_UPDATE_LOGIN,
421 P_DML_RECORD.PROGRAM_APPLICATION_ID,
422 P_DML_RECORD.PROGRAM_ID,
423 P_DML_RECORD.SYSDATE,
424 'Y','Y','Y','Y','Y','Y','Y','Y','Y','Y',
425 'Y','Y',
426 dup_val_exp_val,
427 other_exp_val
428 from hz_imp_relships_sg rel_sg, hz_imp_relships_int rel_int
429 where rel_sg.action_flag = 'I'
430 and rel_int.rowid = rel_sg.int_row_id
431 and rel_sg.batch_id = P_DML_RECORD.BATCH_ID
432 and rel_sg.sub_orig_system = P_DML_RECORD.OS
433 and rel_sg.sub_orig_system_reference
434 between P_DML_RECORD.FROM_OSR and P_DML_RECORD.TO_OSR
435 );
436
437 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
438 hz_utility_v2pub.debug(p_message=>'REL:populate_error_table()-',
439 p_prefix=>l_debug_prefix,
440 p_msg_level=>fnd_log.level_procedure);
441 END IF;
442 END populate_error_table;
443
444
445 /********************************************************************************
446 *
447 * process_insert_rels
448 *
449 ********************************************************************************/
450
451 PROCEDURE process_insert_rels (
452 P_DML_RECORD IN HZ_IMP_LOAD_WRAPPER.DML_RECORD_TYPE
453 ,x_return_status OUT NOCOPY VARCHAR2
454 ,x_msg_count OUT NOCOPY NUMBER
455 ,x_msg_data OUT NOCOPY VARCHAR2
456 ) IS
457
458 l_insert_sql1 varchar2(32767) :=
459 'BEGIN insert all
460 when (sub_id is not null
461 and obj_id is not null
462 and relationship_type is not null
463 and rel_code_error is not null
464 and start_end_date_error is not null
465 and hierarchical_flag_error is not null
466 and action_mismatch_error is not null
467 and relate_self_error is not null
468 and createdby_error is not null
469 and dup_rel_error is not null
470 and flex_val_error is not null) then
471 into hz_parties ( /* insert relationship party if no validation error */
472 created_by,
473 creation_date,
474 last_updated_by,
475 last_update_date,
476 last_update_login,
477 program_application_id,
478 program_id,
479 program_update_date,
480 request_id,
481 party_id,
482 party_number,
483 party_name,
484 party_type,
485 created_by_module,
486 orig_system_reference,
487 status,
488 object_version_number,
489 validated_flag,
490 application_id)
491 values (
492 :user_id,
493 :l_sysdate,
494 :user_id,
495 :l_sysdate,
496 :last_update_login,
497 :program_application_id,
498 :program_id,
499 :l_sysdate,
500 :request_id,
501 hz_parties_s.nextval,
502 hz_party_number_s.nextval,
503 party_name,
504 ''PARTY_RELATIONSHIP'',
505 created_by_module,
506 hz_parties_s.nextval,
507 ''A'',
508 1,
509 ''N'',
510 :application_id)
511 into hz_relationships ( /* insert forward relationship */
512 actual_content_source,
513 application_id,
514 content_source_type,
515 created_by,
516 creation_date,
517 last_updated_by,
521 program_id,
518 last_update_date,
519 last_update_login,
520 program_application_id,
522 program_update_date,
523 request_id,
524 relationship_id,
525 subject_id,
526 subject_type,
527 subject_table_name,
528 object_id,
529 object_type,
530 object_table_name,
531 party_id,
532 relationship_code,
533 directional_flag,
534 comments,
535 start_date,
536 end_date,
537 status,
538 attribute_category,
539 attribute1,
540 attribute2,
541 attribute3,
542 attribute4,
543 attribute5,
544 attribute6,
545 attribute7,
546 attribute8,
547 attribute9,
548 attribute10,
549 attribute11,
550 attribute12,
551 attribute13,
552 attribute14,
553 attribute15,
554 attribute16,
555 attribute17,
556 attribute18,
557 attribute19,
558 attribute20,
559 relationship_type,
560 object_version_number,
561 created_by_module,
562 direction_code)
563 values (
564 :actual_content_src,
565 :application_id,
566 ''USER_ENTERED'',
567 :user_id,
568 :l_sysdate,
569 :user_id,
570 :l_sysdate,
571 :last_update_login,
572 :program_application_id,
573 :program_id,
574 :l_sysdate,
575 :request_id,
576 relationship_id,
577 sub_id,
578 sp_type,
579 ''HZ_PARTIES'',
580 obj_id,
581 op_type,
582 ''HZ_PARTIES'',
583 hz_parties_s.nextval,
584 forward_rel_code,
585 ''F'',
586 comments,
587 start_date,
588 end_date,
589 ''A'',
590 attribute_category,
591 attribute1,
592 attribute2,
593 attribute3,
594 attribute4,
595 attribute5,
596 attribute6,
597 attribute7,
598 attribute8,
599 attribute9,
600 attribute10,
601 attribute11,
602 attribute12,
603 attribute13,
604 attribute14,
605 attribute15,
606 attribute16,
607 attribute17,
608 attribute18,
609 attribute19,
610 attribute20,
611 relationship_type,
612 1, -- OBJECT_VERSION_NUMBER,
613 created_by_module,
614 direction_code)
615 into hz_relationships ( /* insert backward relationship */
616 actual_content_source,
617 application_id,
618 content_source_type,
619 created_by,
620 creation_date,
621 last_updated_by,
622 last_update_date,
623 last_update_login,
624 program_application_id,
625 program_id,
626 program_update_date,
627 request_id,
628 relationship_id,
629 subject_id,
630 subject_type,
631 subject_table_name,
632 object_id,
633 object_type,
634 object_table_name,
635 party_id,
636 relationship_code,
637 directional_flag,
638 comments,
639 start_date,
640 end_date,
641 status,
642 attribute_category,
643 attribute1,
644 attribute2,
645 attribute3,
646 attribute4,
650 attribute8,
647 attribute5,
648 attribute6,
649 attribute7,
651 attribute9,
652 attribute10,
653 attribute11,
654 attribute12,
655 attribute13,
656 attribute14,
657 attribute15,
658 attribute16,
659 attribute17,
660 attribute18,
661 attribute19,
662 attribute20,
663 relationship_type,
664 object_version_number,
665 created_by_module,
666 direction_code)
667 values (
668 :actual_content_src,
669 :application_id,
670 ''USER_ENTERED'',
671 :user_id,
672 :l_sysdate,
673 :user_id,
674 :l_sysdate,
675 :last_update_login,
676 :program_application_id,
677 :program_id,
678 :l_sysdate,
679 :request_id,
680 relationship_id,
681 obj_id,
682 op_type,
683 ''HZ_PARTIES'',
684 sub_id,
685 sp_type,
686 ''HZ_PARTIES'',
687 hz_parties_s.nextval,
688 backward_rel_code,
689 ''B'',
690 comments,
691 start_date,
692 end_date,
693 ''A'',
694 attribute_category,
695 attribute1,
696 attribute2,
697 attribute3,
698 attribute4,
699 attribute5,
700 attribute6,
701 attribute7,
702 attribute8,
703 attribute9,
704 attribute10,
705 attribute11,
706 attribute12,
707 attribute13,
708 attribute14,
709 attribute15,
710 attribute16,
711 attribute17,
712 attribute18,
713 attribute19,
714 attribute20,
715 relationship_type,
716 1, -- OBJECT_VERSION_NUMBER,
717 created_by_module,
718 decode(direction_code, ''P'', ''C'', ''C'', ''P'', ''N''))
719 when (sub_id is not null
720 and obj_id is not null
721 and relationship_type is not null
722 and rel_code_error is not null
723 and start_end_date_error is not null
724 and hierarchical_flag_error is not null
725 and action_mismatch_error is not null
726 and relate_self_error is not null
727 and dup_rel_error is not null
728 and flex_val_error is not null
729 -- Bug 4455041. To create a row in HZ_ORG_CONTACTS for all relationships
730 and sp_type in (''PERSON'',''ORGANIZATION'',''GROUP'')
731 and op_type in (''PERSON'',''ORGANIZATION'',''GROUP'')
732 ) then
733 into hz_org_contacts ( /* insert org contact if at least one party is person */
734 application_id,
735 created_by,
736 creation_date,
737 last_updated_by,
738 last_update_date,
739 last_update_login,
740 program_application_id,
741 program_id,
742 program_update_date,
743 request_id,
744 org_contact_id,
745 party_relationship_id,
746 contact_number,
747 orig_system_reference,
748 status,
749 object_version_number,
750 created_by_module)
751 values (
752 :application_id,
753 :user_id,
754 :l_sysdate,
755 :user_id,
756 :l_sysdate,
757 :last_update_login,
758 :program_application_id,
759 :program_id,
760 :l_sysdate,
761 :request_id,
762 hz_org_contacts_s.nextval,
763 relationship_id,
764 hz_contact_numbers_s.nextval,
765 hz_org_contacts_s.nextval,
766 ''A'',
767 1,
768 ''HZ_IMPORT'')
769 when (sub_id is not null
770 and obj_id is not null
771 and relationship_type is not null
772 and rel_code_error is not null
773 and start_end_date_error is not null
774 and hierarchical_flag_error is not null
775 and action_mismatch_error is not null
776 and relate_self_error is not null
777 and dup_rel_error is not null
778 and flex_val_error is not null
779 and ((sp_type=''PERSON'' and op_type=''ORGANIZATION'')
780 or (op_type=''PERSON'' and sp_type=''ORGANIZATION''))
781 ) then
782 into hz_party_usg_assignments (
783 application_id,
784 created_by,
785 creation_date,
786 last_updated_by,
787 last_update_date,
788 last_update_login,
789 program_application_id,
790 program_id,
791 request_id,
792 party_usg_assignment_id,
793 party_id,
794 party_usage_code,
795 owner_table_name,
796 owner_table_id,
797 effective_start_date,
798 effective_end_date,
799 status_flag,
800 created_by_module,
801 object_version_number)
802 values (
803 :application_id,
804 :user_id,
805 :l_sysdate,
806 :user_id,
807 :l_sysdate,
808 :last_update_login,
809 :program_application_id,
810 :program_id,
814 ''ORG_CONTACT'',
811 :request_id,
812 hz_party_usg_assignments_s.nextval,
813 decode(sp_type,''PERSON'',sub_id,obj_id),
815 ''HZ_RELATIONSHIPS'',
816 relationship_id,
817 start_date,
818 end_date,
819 ''A'',
820 created_by_module,
821 1)
822 when (sub_id is not null
823 and obj_id is not null
824 and relationship_type is not null
825 and rel_code_error is not null
826 and start_end_date_error is not null
827 and hierarchical_flag_error is not null
828 and action_mismatch_error is not null
829 and relate_self_error is not null
830 and dup_rel_error is not null
831 and flex_val_error is not null
832 and sp_type=''PERSON''
833 and op_type=''PERSON''
834 ) then
835 into hz_party_usg_assignments (
836 application_id,
837 created_by,
838 creation_date,
839 last_updated_by,
840 last_update_date,
841 last_update_login,
842 program_application_id,
843 program_id,
844 request_id,
845 party_usg_assignment_id,
846 party_id,
847 party_usage_code,
848 owner_table_name,
849 owner_table_id,
850 effective_start_date,
851 effective_end_date,
852 status_flag,
853 created_by_module,
854 object_version_number)
855 values (
856 :application_id,
857 :user_id,
858 :l_sysdate,
859 :user_id,
860 :l_sysdate,
861 :last_update_login,
862 :program_application_id,
863 :program_id,
864 :request_id,
865 hz_party_usg_assignments_s.nextval,
866 sub_id,
867 ''RELATED_PERSON'',
868 ''HZ_RELATIONSHIPS'',
869 relationship_id,
870 start_date,
871 end_date,
872 ''A'',
873 created_by_module,
874 1)
875 into hz_party_usg_assignments (
876 application_id,
877 created_by,
878 creation_date,
879 last_updated_by,
880 last_update_date,
881 last_update_login,
882 program_application_id,
883 program_id,
884 request_id,
885 party_usg_assignment_id,
886 party_id,
887 party_usage_code,
888 owner_table_name,
889 owner_table_id,
890 effective_start_date,
891 effective_end_date,
892 status_flag,
893 created_by_module,
894 object_version_number)
895 values (
896 :application_id,
897 :user_id,
898 :l_sysdate,
899 :user_id,
900 :l_sysdate,
901 :last_update_login,
902 :program_application_id,
903 :program_id,
904 :request_id,
905 hz_party_usg_assignments_s.nextval+1,
906 obj_id,
907 ''RELATED_PERSON'',
908 ''HZ_RELATIONSHIPS'',
909 relationship_id,
910 start_date,
911 end_date,
912 ''A'',
913 created_by_module,
914 1)
915 else
916 into hz_imp_tmp_errors ( /* insert into tmp errors for any validation error */
917 created_by,
918 creation_date,
919 last_updated_by,
920 last_update_date,
921 last_update_login,
922 program_application_id,
923 program_id,
924 program_update_date,
925 error_id,
926 batch_id,
927 request_id,
928 int_row_id,
929 interface_table_name,
930 e1_flag,
931 e2_flag,
932 e3_flag,
933 e4_flag,
934 e5_flag,
935 e6_flag,
936 e7_flag,
937 e8_flag,
938 e9_flag,
939 e10_flag,
940 e11_flag,
941 action_mismatch_flag)
942 values (
943 :user_id,
944 :l_sysdate,
945 :user_id,
946 :l_sysdate,
947 :last_update_login,
948 :program_application_id,
949 :program_id,
950 :l_sysdate,
951 HZ_IMP_ERRORS_S.NextVal,
952 :p_batch_id,
953 :request_id,
954 row_id,
955 ''HZ_IMP_RELSHIPS_INT'',
956 nvl2(sub_pid, ''Y'', null),
957 nvl2(obj_pid, ''Y'', null),
958 decode(sub_pid, null, ''Y'',
959 decode(obj_pid, null, ''Y'', nvl2(relationship_type, ''Y'', nvl2(rel_code_error, null, ''Y'')))),
960 rel_code_error,
961 start_end_date_error,
962 nvl2(relationship_type, hierarchical_flag_error, ''Y''),
963 relate_self_error,
964 dup_rel_error,
965 flex_val_error,
966 ''Y'',
967 createdby_error,
968 action_mismatch_error)
969 select /*+ index(rt, hz_relationship_types_n3) use_nl(rt, party_rel_type_l) */
970 rs.row_id,
971 rs.sub_pid,
972 rs.obj_pid,
973 rs.relationship_id,
974 rt.direction_code,
975 rt.backward_rel_code,
979 rs.obj_id,
976 rs.sub_id,
977 rs.sp_type,
978 rs.party_name,
980 rs.op_type,
981 rt.relationship_type,
982 rt.forward_rel_code,
983 rs.start_date,
984 rs.end_date,
985 rs.attribute_category,
986 rs.attribute1,
987 rs.attribute2,
988 rs.attribute3,
989 rs.attribute4,
990 rs.attribute5,
991 rs.attribute6,
992 rs.attribute7,
993 rs.attribute8,
994 rs.attribute9,
995 rs.attribute10,
996 rs.attribute11,
997 rs.attribute12,
998 rs.attribute13,
999 rs.attribute14,
1000 rs.attribute15,
1001 rs.attribute16,
1002 rs.attribute17,
1003 rs.attribute18,
1004 rs.attribute19,
1005 rs.attribute20,
1006 rs.interface_status,
1007 rs.comments,
1008 rs.created_by_module,
1009 nvl2(createdby_l.lookup_code,''Y'',null) createdby_error,
1010 rs.start_end_date_error,
1011 rs.action_mismatch_error,
1012 nvl2(party_rel_type_l.lookup_code, ''Y'', null) rel_code_error,
1013 decode(rt.hierarchical_flag, ''N'', decode (rt.allow_circular_relationships, ''Y'',
1014 ''Y'', null), null) hierarchical_flag_error,
1015 decode(:l_val_flex, ''Y'',
1016 HZ_IMP_LOAD_RELATIONSHIPS_PKG.validate_desc_flexfield_f(
1017 rs.attribute_category, rs.attribute1, rs.attribute2, rs.attribute3,
1018 rs.attribute4,
1019 rs.attribute5, rs.attribute6, rs.attribute7, rs.attribute8, rs.attribute9,
1020 rs.attribute10, rs.attribute11, rs.attribute12, rs.attribute13,
1021 rs.attribute14,
1022 rs.attribute15, rs.attribute16, rs.attribute17, rs.attribute18,
1023 rs.attribute19,
1024 rs.attribute20, :l_sysdate
1025 ), ''T'') flex_val_error,
1026 decode(rs.obj_id, rs.sub_id, decode(rt.allow_relate_to_self_flag, ''N'', null,
1027 ''Y''), ''Y'') relate_self_error,
1028 dup_rel_error,
1029 dup_rel_count
1030 from hz_relationship_types rt,
1031 fnd_lookup_values party_rel_type_l,
1032 fnd_lookup_values createdby_l,
1033 (
1034 select /*+ ordered index(sp, HZ_PARTIES_U1) index(op, HZ_PARTIES_U1) */ ri.rowid row_id,
1035 sp.party_id sub_pid,
1036 op.party_id obj_pid,
1037 irs.relationship_id,
1038 ri.relationship_code,
1039 irs.sub_id,
1040 sp.party_type sp_type,
1041 substrb(sp.party_name || ''-'' || op.party_name, 1, 360) party_name,
1042 irs.obj_id,
1043 op.party_type op_type,
1044 ri.relationship_type,
1045 nvl(nullif(ri.start_date, :p_gmiss_date), :l_sysdate) start_date,
1046 nvl(nullif(ri.end_date, :p_gmiss_date), :l_no_end_date) end_date,
1047 nullif(ri.attribute_category, :p_gmiss_char) attribute_category,
1048 nullif(ri.attribute1, :p_gmiss_char) attribute1,
1049 nullif(ri.attribute2, :p_gmiss_char) attribute2,
1050 nullif(ri.attribute3, :p_gmiss_char) attribute3,
1051 nullif(ri.attribute4, :p_gmiss_char) attribute4,
1052 nullif(ri.attribute5, :p_gmiss_char) attribute5,
1053 nullif(ri.attribute6, :p_gmiss_char) attribute6,
1054 nullif(ri.attribute7, :p_gmiss_char) attribute7,
1055 nullif(ri.attribute8, :p_gmiss_char) attribute8,
1056 nullif(ri.attribute9, :p_gmiss_char) attribute9,
1057 nullif(ri.attribute10, :p_gmiss_char) attribute10,
1058 nullif(ri.attribute11, :p_gmiss_char) attribute11,
1059 nullif(ri.attribute12, :p_gmiss_char) attribute12,
1060 nullif(ri.attribute13, :p_gmiss_char) attribute13,
1061 nullif(ri.attribute14, :p_gmiss_char) attribute14,
1062 nullif(ri.attribute15, :p_gmiss_char) attribute15,
1063 nullif(ri.attribute16, :p_gmiss_char) attribute16,
1064 nullif(ri.attribute17, :p_gmiss_char) attribute17,
1065 nullif(ri.attribute18, :p_gmiss_char) attribute18,
1066 nullif(ri.attribute19, :p_gmiss_char) attribute19,
1067 nullif(ri.attribute20, :p_gmiss_char) attribute20,
1068 nullif(ri.interface_status, :p_gmiss_char) interface_status,
1069 nullif(ri.comments, :p_gmiss_char) comments,
1070 nvl(nullif(ri.created_by_module, :p_gmiss_char), ''HZ_IMPORT'') created_by_module,
1071 decode(ri.end_date, null, ''Y'', decode(sign(ri.end_date - ri.start_date), -1,
1072 null, ''Y'')) start_end_date_error,
1073 nvl2(nullif(nullif(ri.insert_update_flag, :p_gmiss_char), irs.action_flag),
1077 from hz_imp_relships_sg irs,
1074 null, ''Y'') action_mismatch_error,
1075 decode(tc.a, 0, ''Y'') dup_rel_error,
1076 tc.a dup_rel_count
1078 hz_imp_relships_int ri,
1079 (select 0 a from dual union all select 1 a from dual) tc,
1080 hz_parties sp,
1081 hz_parties op
1082 where irs.batch_mode_flag = :p_batch_mode_flag
1083 and irs.action_flag = ''I''
1084 and irs.sub_id = sp.party_id (+)
1085 and irs.obj_id = op.party_id (+)
1086 and ri.rowid = irs.int_row_id
1087 and irs.batch_id = :p_batch_id
1088 and irs.sub_orig_system = :p_os
1089 and irs.sub_orig_system_reference between :p_from_osr and :p_to_osr';
1090
1091 l_insert_sql2 varchar2(4000) :=
1092 ' and tc.a = (select count(*)
1093 from hz_relationships r1
1094 where r1.subject_id = irs.sub_id
1095 and r1.subject_table_name = ''HZ_PARTIES''
1096 and r1.object_id = irs.obj_id
1097 and r1.relationship_type = ri.relationship_type
1098 and r1.relationship_code = ri.relationship_code
1099 and nvl(ri.end_date, :l_no_end_date) >= nvl(r1.start_date, :l_sysdate)
1100 and nvl(r1.end_date, :l_no_end_date) >= nvl(ri.start_date, :l_sysdate)
1101 and r1.actual_content_source = :actual_content_src
1102 and r1.status = ''A''
1103 and rownum = 1)) rs
1104 where party_rel_type_l.lookup_code (+) = rs.relationship_code
1105 and party_rel_type_l.lookup_type (+) = ''PARTY_RELATIONS_TYPE''
1106 and party_rel_type_l.language (+) = userenv(''LANG'')
1107 and party_rel_type_l.view_application_id (+) = 222
1108 and party_rel_type_l.security_group_id (+) =
1109 fnd_global.lookup_security_group(''PARTY_RELATIONS_TYPE'', 222)
1110 and createdby_l.lookup_code (+) = rs.created_by_module
1111 and createdby_l.lookup_type (+) = ''HZ_CREATED_BY_MODULES''
1112 and createdby_l.language (+) = userenv(''LANG'')
1113 and createdby_l.view_application_id (+) = 222
1114 and createdby_l.security_group_id (+) =
1115 fnd_global.lookup_security_group(''HZ_CREATED_BY_MODULES'', 222)
1116 and rs.relationship_type = rt.relationship_type (+)
1117 and rs.relationship_code = rt.forward_rel_code (+)
1118 and rs.sp_type = rt.subject_type (+)
1119 and rs.op_type = rt.object_type (+)';
1120
1121 l_dnb_rel_sql varchar2(35) := ' and dup_rel_count = 0';
1122
1123 l_where_first_run_sql varchar2(35) := ' AND ri.interface_status is null';
1124 l_where_rerun_sql varchar2(35) := ' AND ri.interface_status = ''C''';
1125
1126 l_where_enabled_lookup_sql varchar2(1000) :=
1127 ' AND ( party_rel_type_l.ENABLED_FLAG(+) = ''Y'' AND
1128 TRUNC(:l_sysdate) BETWEEN
1129 TRUNC(NVL( party_rel_type_l.START_DATE_ACTIVE,:l_sysdate ) ) AND
1130 TRUNC(NVL( party_rel_type_l.END_DATE_ACTIVE,:l_sysdate ) ) )
1131 AND ( createdby_l.ENABLED_FLAG(+) = ''Y'' AND
1132 TRUNC(:l_sysdate) BETWEEN
1133 TRUNC(NVL( createdby_l.START_DATE_ACTIVE,:l_sysdate ) ) AND
1134 TRUNC(NVL( createdby_l.END_DATE_ACTIVE,:l_sysdate ) ) )';
1135
1136 l_end_sql VARCHAR2(10) := '; END;';
1137 l_dml_exception varchar2(1) := 'N';
1138 l_debug_prefix VARCHAR2(30) := '';
1139
1140 BEGIN
1141 savepoint process_insert_rels_pvt;
1142
1143 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
1144 hz_utility_v2pub.debug(p_message=>'REL:process_insert_rels()+',
1145 p_prefix=>l_debug_prefix,
1146 p_msg_level=>fnd_log.level_procedure);
1147 END IF;
1148
1149 FND_MSG_PUB.initialize;
1150
1151 --Initialize API return status to success.
1152 x_return_status := FND_API.G_RET_STS_SUCCESS;
1153
1154 IF l_actual_content_source <> 'USER_ENTERED' THEN
1155 l_insert_sql2 := l_insert_sql2 || l_dnb_rel_sql;
1156 END IF;
1157
1158 IF P_DML_RECORD.ALLOW_DISABLED_LOOKUP = 'Y' THEN
1159
1160 IF P_DML_RECORD.RERUN = 'N' /*** First Run ***/ THEN
1161
1162 EXECUTE IMMEDIATE l_insert_sql1 || l_where_first_run_sql || l_insert_sql2 || l_end_sql
1163 using
1164 p_dml_record.user_id,
1165 p_dml_record.sysdate,
1166 p_dml_record.last_update_login,
1167 p_dml_record.program_application_id,
1168 p_dml_record.program_id,
1169 p_dml_record.request_id,
1170 p_dml_record.application_id,
1171 p_dml_record.actual_content_src,
1172
1173 p_dml_record.batch_id,
1174 p_dml_record.flex_validation,
1175 p_dml_record.gmiss_date,
1176 l_no_end_date,
1177 p_dml_record.gmiss_char,
1178 p_dml_record.batch_mode_flag,
1179 p_dml_record.os,
1180 p_dml_record.from_osr,
1181 p_dml_record.to_osr;
1182
1183 ELSE /* Rerun to correct errors */
1184
1185 EXECUTE IMMEDIATE l_insert_sql1 || l_where_rerun_sql || l_insert_sql2 || l_end_sql
1186 using
1187 p_dml_record.user_id,
1188 p_dml_record.sysdate,
1189 p_dml_record.last_update_login,
1190 p_dml_record.program_application_id,
1191 p_dml_record.program_id,
1192 p_dml_record.request_id,
1193 p_dml_record.application_id,
1194 p_dml_record.actual_content_src,
1195
1196 p_dml_record.batch_id,
1197 p_dml_record.flex_validation,
1198 p_dml_record.gmiss_date,
1199 l_no_end_date,
1200 p_dml_record.gmiss_char,
1201 p_dml_record.batch_mode_flag,
1202 p_dml_record.os,
1203 p_dml_record.from_osr,
1204 p_dml_record.to_osr;
1205
1206 END IF;
1207
1208 ELSE -- l_allow_disabled_lookup
1209
1210 IF p_dml_record.RERUN = 'N' /*** First Run ***/ THEN
1211
1212 EXECUTE IMMEDIATE l_insert_sql1 || l_where_first_run_sql || l_insert_sql2 || l_where_enabled_lookup_sql || l_end_sql
1213 using
1214 p_dml_record.user_id,
1215 p_dml_record.sysdate,
1216 p_dml_record.last_update_login,
1217 p_dml_record.program_application_id,
1218 p_dml_record.program_id,
1219 p_dml_record.request_id,
1223 p_dml_record.batch_id,
1220 p_dml_record.application_id,
1221 p_dml_record.actual_content_src,
1222
1224 p_dml_record.flex_validation,
1225 p_dml_record.gmiss_date,
1226 l_no_end_date,
1227 p_dml_record.gmiss_char,
1228 p_dml_record.batch_mode_flag,
1229 p_dml_record.os,
1230 p_dml_record.from_osr,
1231 p_dml_record.to_osr;
1232
1233 ELSE /* Rerun to correct errors */
1234
1235 EXECUTE IMMEDIATE l_insert_sql1 || l_where_rerun_sql || l_insert_sql2 || l_where_enabled_lookup_sql || l_end_sql
1236 using
1237 p_dml_record.user_id,
1238 p_dml_record.sysdate,
1239 p_dml_record.last_update_login,
1240 p_dml_record.program_application_id,
1241 p_dml_record.program_id,
1242 p_dml_record.request_id,
1243 p_dml_record.application_id,
1244 p_dml_record.actual_content_src,
1245
1246 p_dml_record.batch_id,
1247 p_dml_record.flex_validation,
1248 p_dml_record.gmiss_date,
1249 l_no_end_date,
1250 p_dml_record.gmiss_char,
1251 p_dml_record.batch_mode_flag,
1252 p_dml_record.os,
1253 p_dml_record.from_osr,
1254 p_dml_record.to_osr;
1255 END IF;
1256
1257 END IF;
1258
1259 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1260 hz_utility_v2pub.debug(p_message=>'REL:Rows inserted in MTI = ' || SQL%ROWCOUNT,
1261 p_prefix =>l_debug_prefix,
1262 p_msg_level=>fnd_log.level_statement);
1263 END IF;
1264
1265 IF P_DML_RECORD.OS = 'DNB' THEN
1266 /* End date old DNB parents */
1267
1268 UPDATE /*+ index(r, hz_relationships_u1) */ hz_relationships r
1269 SET end_date = decode(TRUNC(start_date), TRUNC(p_dml_record.sysdate),
1270 TRUNC(p_dml_record.sysdate),
1271 TRUNC(p_dml_record.sysdate-1)),
1272 status = 'I',
1273 last_update_date = p_dml_record.sysdate,
1274 last_updated_by = p_dml_record.user_id,
1275 last_update_login = p_dml_record.last_update_login,
1276 request_id = p_dml_record.request_id
1277 WHERE (relationship_id, directional_flag) in (
1278 SELECT t.relationship_id, t.directional_flag
1279 FROM hz_imp_tmp_rel_end_date t
1280 WHERE int_row_id is not null
1281 and batch_id = p_dml_record.batch_id
1282 and sub_orig_system_reference between p_dml_record.from_osr and p_dml_record.to_osr
1283 and int_row_id not in (
1284 select /*+ hash_aj */ e.int_row_id
1285 from hz_imp_tmp_errors e
1286 where e.request_id = p_dml_record.request_id
1287 AND e.interface_table_name = 'HZ_IMP_RELATIONSHIPS_INT'
1288 and e.batch_id = p_dml_record.batch_id
1289 and e.int_row_id is not null));
1290
1291 END IF;
1292
1293 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
1294 hz_utility_v2pub.debug(p_message=>'REL:process_insert_rels()-',
1295 p_prefix=>l_debug_prefix,
1296 p_msg_level=>fnd_log.level_procedure);
1297 END IF;
1298
1299 EXCEPTION
1300 WHEN DUP_VAL_ON_INDEX THEN
1301 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Insert rels dup val exception: ' || SQLERRM);
1302
1303 ROLLBACK to process_insert_rels_pvt;
1304
1305 populate_error_table(P_DML_RECORD, 'Y', SQLERRM);
1306
1307 x_return_status := FND_API.G_RET_STS_ERROR;
1308
1309 FND_MESSAGE.SET_NAME('AR', 'HZ_API_OTHERS_EXCEP');
1310 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
1311 FND_MSG_PUB.ADD;
1312
1313 WHEN OTHERS THEN
1314 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Insert rels other exception: ' || SQLERRM);
1315
1316 ROLLBACK to process_insert_rels_pvt;
1317
1318 populate_error_table(P_DML_RECORD, 'N', SQLERRM);
1319
1320 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1321
1322 FND_MESSAGE.SET_NAME('AR', 'HZ_API_OTHERS_EXCEP');
1323 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
1324 FND_MSG_PUB.ADD;
1325
1326 END process_insert_rels;
1327
1328
1329 /********************************************************************************
1330 *
1331 * process_update_rels
1332 *
1333 ********************************************************************************/
1334
1335 PROCEDURE process_update_rels (
1336 P_DML_RECORD IN HZ_IMP_LOAD_WRAPPER.DML_RECORD_TYPE
1337 ,x_return_status OUT NOCOPY VARCHAR2
1338 ,x_msg_count OUT NOCOPY NUMBER
1339 ,x_msg_data OUT NOCOPY VARCHAR2
1340 ) IS
1341
1342 c_handle_update RefCurType;
1343
1344 /* Fewer validations than insert because many columns like subject, object,
1345 rel type, rel code etc are not updateable */
1346 l_update_sql varchar2(11000) :=
1347 'SELECT /*+ leading(rs) use_nl(ri) rowid(ri) */
1348 ri.ROWID,
1349 rs.relationship_id,
1350 rs.sub_id,
1351 rs.obj_id,
1352 ri.relationship_type,
1353 ri.COMMENTS,
1354 ri.ATTRIBUTE_CATEGORY,
1355 ri.ATTRIBUTE1,
1356 ri.ATTRIBUTE2,
1357 ri.ATTRIBUTE3,
1358 ri.ATTRIBUTE4,
1359 ri.ATTRIBUTE5,
1360 ri.ATTRIBUTE6,
1361 ri.ATTRIBUTE7,
1362 ri.ATTRIBUTE8,
1363 ri.ATTRIBUTE9,
1364 ri.ATTRIBUTE10,
1365 ri.ATTRIBUTE11,
1366 ri.ATTRIBUTE12,
1367 ri.ATTRIBUTE13,
1368 ri.ATTRIBUTE14,
1369 ri.ATTRIBUTE15,
1370 ri.ATTRIBUTE16,
1371 ri.ATTRIBUTE17,
1372 ri.ATTRIBUTE18,
1373 ri.ATTRIBUTE19,
1374 ri.ATTRIBUTE20,
1375 decode(nvl(ri.insert_update_flag, rs.action_flag), rs.action_flag, ''Y'', null) action_mismatch_error,
1376 0 flex_val_errors,
1377 ''T'' dss_security_error
1378 FROM HZ_IMP_RELSHIPS_INT ri, HZ_IMP_RELSHIPS_SG rs,
1379 HZ_RELATIONSHIPS r
1380 WHERE
1384 AND rs.batch_id = :P_BATCH_ID
1381 ri.rowid = rs.int_row_id
1382 AND rs.relationship_id = r.relationship_id
1383 AND r.directional_flag = ''F''
1385 AND rs.sub_orig_system = :P_OS
1386 AND rs.sub_orig_system_reference between :P_FROM_OSR and :P_TO_OSR
1387 AND rs.batch_mode_flag = :l_batch_mode_flag
1388 AND rs.ACTION_FLAG = ''U''';
1389
1390 l_where_first_run_sql varchar2(40) := ' AND ri.interface_status is null';
1391 l_where_rerun_sql varchar2(40) := ' AND ri.interface_status = ''C''';
1392 l_dnb_rel varchar2(200) :=
1393 ' AND ri.relationship_type not in (''HEADQUARTERS/DIVISION'',''PARENT/SUBSIDIARY'',''DOMESTIC_ULTIMATE'',''GLOBAL_ULTIMATE'')';
1394
1395
1396 l_dml_exception varchar2(1) := 'N';
1397 l_debug_prefix VARCHAR2(30) := '';
1398 BEGIN
1399
1400 savepoint process_update_rels_pvt;
1401
1402 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
1403 hz_utility_v2pub.debug(p_message=>'REL:process_update_rels()+',
1404 p_prefix=>l_debug_prefix,
1405 p_msg_level=>fnd_log.level_procedure);
1406 END IF;
1407
1408 FND_MSG_PUB.initialize;
1409
1410 --Initialize API return status to success.
1411 x_return_status := FND_API.G_RET_STS_SUCCESS;
1412
1413 IF P_DML_RECORD.RERUN = 'N' /*** First Run ***/ THEN
1414
1415 IF l_actual_content_source <> 'USER_ENTERED' THEN
1416 OPEN c_handle_update FOR l_update_sql || l_where_first_run_sql || l_dnb_rel
1417 USING
1418 P_DML_RECORD.BATCH_ID, P_DML_RECORD.OS,
1419 P_DML_RECORD.FROM_OSR, P_DML_RECORD.TO_OSR, P_DML_RECORD.BATCH_MODE_FLAG;
1420 ELSE
1421 OPEN c_handle_update FOR l_update_sql || l_where_first_run_sql
1422 USING
1423 P_DML_RECORD.BATCH_ID, P_DML_RECORD.OS,
1424 P_DML_RECORD.FROM_OSR, P_DML_RECORD.TO_OSR, P_DML_RECORD.BATCH_MODE_FLAG;
1425 END IF;
1426 ELSE /* Rerun to correct errors */
1427 IF l_actual_content_source <> 'USER_ENTERED' THEN
1428 OPEN c_handle_update FOR l_update_sql || l_where_rerun_sql || l_dnb_rel
1429 USING
1430 P_DML_RECORD.BATCH_ID, P_DML_RECORD.OS,
1431 P_DML_RECORD.FROM_OSR, P_DML_RECORD.TO_OSR, P_DML_RECORD.BATCH_MODE_FLAG;
1432 ELSE
1433 OPEN c_handle_update FOR l_update_sql || l_where_rerun_sql
1434 USING
1435 P_DML_RECORD.BATCH_ID, P_DML_RECORD.OS,
1436 P_DML_RECORD.FROM_OSR, P_DML_RECORD.TO_OSR, P_DML_RECORD.BATCH_MODE_FLAG;
1437 END IF;
1438 END IF;
1439
1440 FETCH c_handle_update BULK COLLECT INTO
1441 l_row_id,
1442 l_relationship_id,
1443 l_subject_id,
1444 l_object_id,
1445 l_relationship_type,
1446 l_comments,
1447 l_attr_category,
1448 l_attr1,
1449 l_attr2,
1450 l_attr3,
1451 l_attr4,
1452 l_attr5,
1453 l_attr6,
1454 l_attr7,
1455 l_attr8,
1456 l_attr9,
1457 l_attr10,
1458 l_attr11,
1459 l_attr12,
1460 l_attr13,
1461 l_attr14,
1462 l_attr15,
1463 l_attr16,
1464 l_attr17,
1465 l_attr18,
1466 l_attr19,
1467 l_attr20,
1468 l_action_mismatch_errors,
1469 l_flex_val_errors,
1470 l_dss_security_errors;
1471
1472 /* Do FND desc flex validation based on profile */
1473 IF P_DML_RECORD.FLEX_VALIDATION = 'Y' THEN
1474 validate_desc_flexfield(P_DML_RECORD.SYSDATE);
1475 END IF;
1476
1477 /* Do DSS security validation based on profile */
1478 IF P_DML_RECORD.DSS_SECURITY = 'Y' THEN
1479 validate_DSS_security;
1480 END IF;
1481
1482 /*************************************************/
1483 /*** Update HZ_RELATIONSHIPS (Both directions) ***/
1484 /*************************************************/
1485
1486
1487 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1488 hz_utility_v2pub.debug(p_message=>'REL:Update relationships',
1489 p_prefix =>l_debug_prefix,
1490 p_msg_level=>fnd_log.level_statement);
1491 END IF;
1492
1493 /*
1494 Reason for disallowing update to start_date and end_date:
1495 Let's say in TCA in hz_relationships, there are 2 records
1496
1497 Subj ID Obj ID Rel Type Rel Code Start Date End Date
1498 1 2 abc xyz 01/01/01 01/20/01
1499 1 2 abc xyz 02/01/01 02/20/01
1500
1501 In Relationships Interface Table, there is 1 record as follows:
1502 Subj ID Obj ID Rel Type Rel Code Start Date End Date
1503 1 2 abc xyz 03/01/01 03/20/01
1504
1505 There is no way to know if we should a new record or update one of the records.
1506 If Update, then which one?
1507
1508 In the Matching phase, we should include both start and end dates
1509 (actual value, not range/ overlap) when checking for existence of records in TCA.
1510 In the above case, the record will be marked for Insert.
1511
1512 In the DML phase, we anyways check for date overlap, so if a record were to be
1513 marked as Insert due to different dates, but if they were overlapping with those of
1514 an existing record in TCA, in DML phase, record will be marked for 'Error'.
1515 */
1516
1517 BEGIN
1518 ForAll j in 1..l_relationship_id.count SAVE EXCEPTIONS
1519 update hz_relationships set
1520 /* Not allow update to start_date, end_date */
1521 LAST_UPDATED_BY = P_DML_RECORD.USER_ID,
1522 LAST_UPDATE_DATE = trunc(P_DML_RECORD.SYSDATE),
1523 LAST_UPDATE_LOGIN = P_DML_RECORD.LAST_UPDATE_LOGIN,
1524 REQUEST_ID = P_DML_RECORD.REQUEST_ID,
1525 PROGRAM_APPLICATION_ID = P_DML_RECORD.PROGRAM_APPLICATION_ID,
1526 PROGRAM_ID = P_DML_RECORD.PROGRAM_ID,
1530 NULL, comments,
1527 PROGRAM_UPDATE_DATE = P_DML_RECORD.SYSDATE,
1528 COMMENTS =
1529 DECODE(l_comments(j),
1531 P_DML_RECORD.GMISS_CHAR, NULL,
1532 l_comments(j)),
1533 ATTRIBUTE_CATEGORY =
1534 DECODE(l_attr_category(j),
1535 NULL, attribute_category,
1536 P_DML_RECORD.GMISS_CHAR, NULL,
1537 l_attr_category(j)),
1538 ATTRIBUTE1 =
1539 DECODE(l_attr1(j),
1540 NULL, attribute1,
1541 P_DML_RECORD.GMISS_CHAR, NULL,
1542 l_attr1(j)),
1543 ATTRIBUTE2 =
1544 DECODE(l_attr2(j),
1545 NULL, attribute2,
1546 P_DML_RECORD.GMISS_CHAR, NULL,
1547 l_attr2(j)),
1548 ATTRIBUTE3 =
1549 DECODE(l_attr3(j),
1550 NULL, attribute3,
1551 P_DML_RECORD.GMISS_CHAR, NULL,
1552 l_attr3(j)),
1553 ATTRIBUTE4 =
1554 DECODE(l_attr4(j),
1555 NULL, attribute4,
1556 P_DML_RECORD.GMISS_CHAR, NULL,
1557 l_attr4(j)),
1558 ATTRIBUTE5 =
1559 DECODE(l_attr5(j),
1560 NULL, attribute5,
1561 P_DML_RECORD.GMISS_CHAR, NULL,
1562 l_attr5(j)),
1563 ATTRIBUTE6 =
1564 DECODE(l_attr6(j),
1565 NULL, attribute6,
1566 P_DML_RECORD.GMISS_CHAR, NULL,
1567 l_attr6(j)),
1568 ATTRIBUTE7 =
1569 DECODE(l_attr7(j),
1570 NULL, attribute7,
1571 P_DML_RECORD.GMISS_CHAR, NULL,
1572 l_attr7(j)),
1573 ATTRIBUTE8 =
1574 DECODE(l_attr8(j),
1575 NULL, attribute8,
1576 P_DML_RECORD.GMISS_CHAR, NULL,
1577 l_attr8(j)),
1578 ATTRIBUTE9 =
1579 DECODE(l_attr9(j),
1580 NULL, attribute9,
1581 P_DML_RECORD.GMISS_CHAR, NULL,
1582 l_attr9(j)),
1583 ATTRIBUTE10 =
1584 DECODE(l_attr10(j),
1585 NULL, attribute10,
1586 P_DML_RECORD.GMISS_CHAR, NULL,
1587 l_attr10(j)),
1588 ATTRIBUTE11 =
1589 DECODE(l_attr11(j),
1590 NULL, attribute11,
1591 P_DML_RECORD.GMISS_CHAR, NULL,
1592 l_attr11(j)),
1593 ATTRIBUTE12 =
1594 DECODE(l_attr12(j),
1595 NULL, attribute12,
1596 P_DML_RECORD.GMISS_CHAR, NULL,
1597 l_attr12(j)),
1598 ATTRIBUTE13 =
1599 DECODE(l_attr13(j),
1600 NULL, attribute13,
1601 P_DML_RECORD.GMISS_CHAR, NULL,
1602 l_attr13(j)),
1603 ATTRIBUTE14 =
1604 DECODE(l_attr14(j),
1605 NULL, attribute14,
1606 P_DML_RECORD.GMISS_CHAR, NULL,
1607 l_attr14(j)),
1608 ATTRIBUTE15 =
1609 DECODE(l_attr15(j),
1610 NULL, attribute15,
1611 P_DML_RECORD.GMISS_CHAR, NULL,
1612 l_attr15(j)),
1613 ATTRIBUTE16 =
1614 DECODE(l_attr16(j),
1615 NULL, attribute16,
1616 P_DML_RECORD.GMISS_CHAR, NULL,
1617 l_attr16(j)),
1618 ATTRIBUTE17 =
1619 DECODE(l_attr17(j),
1620 NULL, attribute17,
1621 P_DML_RECORD.GMISS_CHAR, NULL,
1622 l_attr17(j)),
1623 ATTRIBUTE18 =
1624 DECODE(l_attr18(j),
1625 NULL, attribute18,
1626 P_DML_RECORD.GMISS_CHAR, NULL,
1627 l_attr18(j)),
1628 ATTRIBUTE19 =
1629 DECODE(l_attr19(j),
1630 NULL, attribute19,
1631 P_DML_RECORD.GMISS_CHAR, NULL,
1632 l_attr19(j)),
1633 ATTRIBUTE20 =
1634 DECODE(l_attr20(j),
1635 NULL, attribute20,
1636 P_DML_RECORD.GMISS_CHAR, NULL,
1637 l_attr20(j)),
1638 OBJECT_VERSION_NUMBER =
1639 DECODE(OBJECT_VERSION_NUMBER,
1640 NULL, 1,
1641 OBJECT_VERSION_NUMBER+1)
1642 where
1643 relationship_id = l_relationship_id(j)
1644 and l_action_mismatch_errors(j) is not null
1645 and l_flex_val_errors(j) = 0
1646 and l_dss_security_errors(j) ='T'
1647 and actual_content_source = l_actual_content_source;
1648
1649 EXCEPTION
1650 WHEN OTHERS THEN
1651 l_dml_exception := 'Y';
1652
1653 END;
1654
1655 report_errors(P_DML_RECORD, 'U', l_dml_exception);
1656
1657 CLOSE c_handle_update;
1658
1659 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
1660 hz_utility_v2pub.debug(p_message=>'REL:process_update_rels()-',
1661 p_prefix=>l_debug_prefix,
1662 p_msg_level=>fnd_log.level_procedure);
1663 END IF;
1664
1665 EXCEPTION
1666
1667 WHEN OTHERS THEN
1668 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Update rels other exception: ' || SQLERRM);
1669
1670 ROLLBACK to process_update_rels_pvt;
1671
1672 populate_error_table(P_DML_RECORD, 'N', SQLERRM);
1673
1674 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1675
1676 FND_MESSAGE.SET_NAME('AR', 'HZ_API_OTHERS_EXCEP');
1677 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
1678 FND_MSG_PUB.ADD;
1679
1680 END process_update_rels;
1681
1682
1683 /********************************************************************************
1684 *
1685 * load_relationships
1686 *
1687 ********************************************************************************/
1688
1689 PROCEDURE load_relationships (
1690 P_DML_RECORD IN HZ_IMP_LOAD_WRAPPER.DML_RECORD_TYPE
1691 ,x_return_status OUT NOCOPY VARCHAR2
1692 ,x_msg_count OUT NOCOPY NUMBER
1693 ,x_msg_data OUT NOCOPY VARCHAR2
1694 ) IS
1695
1696 l_return_status VARCHAR2(30);
1697 l_msg_data VARCHAR2(2000);
1698 l_msg_count NUMBER;
1699 l_debug_prefix VARCHAR2(30) := '';
1700 BEGIN
1701
1702 savepoint load_rels_pvt;
1703 FND_MSG_PUB.initialize;
1704
1705 -- Check if API is called in debug mode. If yes, enable debug.
1706 --enable_debug;
1707 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
1708 hz_utility_v2pub.debug(p_message=>'REL:load_relationships()+',
1709 p_prefix=>l_debug_prefix,
1710 p_msg_level=>fnd_log.level_procedure);
1711 END IF;
1712 --Initialize API return status to success.
1713 x_return_status := FND_API.G_RET_STS_SUCCESS;
1714
1715 l_actual_content_source := P_DML_RECORD.ACTUAL_CONTENT_SRC;
1716 l_no_end_date := TO_DATE('31-12-4712 00:00:01', 'DD-MM-YYYY HH24:MI:SS');
1717
1718 process_insert_rels(
1719 P_DML_RECORD => P_DML_RECORD
1720 ,x_return_status => x_return_status
1721 ,x_msg_count => x_msg_count
1722 ,x_msg_data => x_msg_data
1723 );
1724
1725 IF x_return_status <> FND_API.G_RET_STS_UNEXP_ERROR THEN
1726 process_update_rels(
1727 P_DML_RECORD => P_DML_RECORD
1728 ,x_return_status => x_return_status
1729 ,x_msg_count => x_msg_count
1730 ,x_msg_data => x_msg_data
1731 );
1732 END IF;
1733
1734 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
1735 hz_utility_v2pub.debug(p_message=>'REL:load_relationships()-',
1736 p_prefix=>l_debug_prefix,
1737 p_msg_level=>fnd_log.level_procedure);
1738 END IF;
1739
1740 -- if enabled, disable debug
1741 --disable_debug;
1742
1743 EXCEPTION
1744
1745 WHEN FND_API.G_EXC_ERROR THEN
1746 ROLLBACK TO load_rels_pvt;
1747 x_return_status := FND_API.G_RET_STS_ERROR;
1748 FND_MSG_PUB.Count_And_Get(
1749 p_encoded => FND_API.G_FALSE,
1750 p_count => x_msg_count,
1751 p_data => x_msg_data);
1752
1753 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1754 ROLLBACK TO load_rels_pvt;
1755 FND_FILE.put_line(fnd_file.log,'Unexpected error occurs while loading relationships');
1756 FND_FILE.put_line(fnd_file.log, l_errm);
1757 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1758 FND_MESSAGE.SET_NAME('AR', 'HZ_API_OTHERS_EXCEP');
1759 FND_MESSAGE.SET_TOKEN('ERROR' ,l_errm);
1760 FND_MSG_PUB.ADD;
1761 FND_MSG_PUB.Count_And_Get(
1762 p_encoded => FND_API.G_FALSE,
1763 p_count => x_msg_count,
1764 p_data => x_msg_data);
1765
1766 WHEN OTHERS THEN
1767 ROLLBACK TO load_rels_pvt;
1768 FND_FILE.put_line(fnd_file.log,'Unexpected error occurs while loading relationships');
1769 FND_FILE.put_line(fnd_file.log, l_errm);
1770 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1771 FND_MESSAGE.SET_NAME('AR', 'HZ_API_OTHERS_EXCEP');
1772 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
1773 FND_MSG_PUB.ADD;
1774 FND_MSG_PUB.Count_And_Get(
1775 p_encoded => FND_API.G_FALSE,
1776 p_count => x_msg_count,
1777 p_data => x_msg_data);
1778
1779 END load_relationships;
1780
1781
1782 END HZ_IMP_LOAD_RELATIONSHIPS_PKG;