[Home] [Help]
PACKAGE BODY: APPS.HZ_IMP_LOAD_ORG_CONTACT_PKG
Source
1 PACKAGE BODY HZ_IMP_LOAD_ORG_CONTACT_PKG AS
2 /*$Header: ARHLORCB.pls 120.36.12010000.4 2008/10/27 09:39:36 idali ship $*/
3
4 l_error_flag FLAG_COLUMN;
5 l_action_mismatch_errors FLAG_ERROR;
6 l_flex_val_errors NUMBER_COLUMN;
7
8 l_dup_rel_errors FLAG_ERROR;
9 l_department_code_errors LOOKUP_ERROR;
10 l_title_errors LOOKUP_ERROR;
11 l_job_title_code_errors LOOKUP_ERROR;
12 l_rel_code_errors LOOKUP_ERROR;
13 l_hierarchical_flag_errors FLAG_ERROR;
14 l_start_date_errors FLAG_ERROR;
15 l_start_end_date_errors FLAG_ERROR;
16 l_sbj_person_type_errors FLAG_ERROR;
17 l_obj_org_type_errors FLAG_ERROR;
18 l_decision_maker_flag_errors FLAG_ERROR;
19 l_reference_use_flag_errors FLAG_ERROR;
20 l_dss_security_errors FLAG_COLUMN;
21
22
23 l_exception_exists FLAG_ERROR;
24
25 l_batch_id NUMBER;
26 l_sub_orig_system SUB_ORIG_SYSTEM;
27 l_sub_owner_table_id SUB_ORIG_SYSTEM_REFERENCE;
28 l_obj_orig_system OBJ_ORIG_SYSTEM;
29 l_obj_owner_table_id OBJ_ORIG_SYSTEM_REFERENCE;
30 l_insert_update_flag INSERT_UPDATE_FLAG;
31 l_contact_number CONTACT_NUMBER;
32 l_department_code DEPARTMENT_CODE;
33 l_department DEPARTMENT;
34 l_title TITLE;
35 l_job_title JOB_TITLE;
36 l_job_title_code JOB_TITLE_CODE;
37 l_decision_maker_flag DECISION_MAKER_FLAG;
38 l_reference_use_flag REFERENCE_USE_FLAG;
39 l_comments COMMENTS;
40 l_relationship_type RELATIONSHIP_TYPE;
41 l_relationship_code RELATIONSHIP_CODE;
42 l_forward_rel_code RELATIONSHIP_CODE;
43 l_backward_rel_code RELATIONSHIP_CODE;
44 l_start_date START_DATE;
45 l_end_date END_DATE;
46 l_rel_comments REL_COMMENTS;
47 l_attribute_category ATTRIBUTE_CATEGORY;
48 l_attribute1 ATTRIBUTE;
49 l_attribute2 ATTRIBUTE;
50 l_attribute3 ATTRIBUTE;
51 l_attribute4 ATTRIBUTE;
52 l_attribute5 ATTRIBUTE;
53 l_attribute6 ATTRIBUTE;
54 l_attribute7 ATTRIBUTE;
55 l_attribute8 ATTRIBUTE;
56 l_attribute9 ATTRIBUTE;
57 l_attribute10 ATTRIBUTE;
58 l_attribute11 ATTRIBUTE;
59 l_attribute12 ATTRIBUTE;
60 l_attribute13 ATTRIBUTE;
61 l_attribute14 ATTRIBUTE;
62 l_attribute15 ATTRIBUTE;
63 l_attribute16 ATTRIBUTE;
64 l_attribute17 ATTRIBUTE;
65 l_attribute18 ATTRIBUTE;
66 l_attribute19 ATTRIBUTE;
67 l_attribute20 ATTRIBUTE;
68 l_interface_status INTERFACE_STATUS;
69 l_error_id ERROR_ID;
70
71 l_created_by_module CREATED_BY_MODULE;
72 l_row_id ROWID;
73
74 l_subject_name PARTY_NAME;
75 l_object_name PARTY_NAME;
76 l_subject_party_type PARTY_TYPE;
77 l_object_party_type PARTY_TYPE;
78 l_relationship_id PARTY_ID;
79 l_subject_party_id PARTY_ID;
80 l_object_party_id PARTY_ID;
81 l_rel_party_id PARTY_ID;
82 l_contact_id PARTY_ID;
83 l_subject_id PARTY_ID;
84 l_object_id PARTY_ID;
85 l_rel_party_number PARTY_NUMBER;
86 l_direction_code DIRECTION_CODE;
87 l_org_contact_id ORG_CONTACT_ID;
88
89 --l_old_orig_system_reference PARTY_ORIG_SYSTEM_REFERENCE;
90 l_osr_error_flag FLAG_COLUMN;
91
92 l_createdby_errors LOOKUP_ERROR;
93
94
95 /* Keep track of rows that do not get inserted or updated successfully.
96 Those are the rows that have some validation or DML errors.
97 Use this when inserting into or updating other tables so that we
98 do not need to check all the validation arrays. */
99
100 l_num_row_processed NUMBER_COLUMN;
101
102 l_no_end_date DATE;
103
104
105 /**********************************************
106 * private procedure validate_desc_flexfield
107 *
108 * DESCRIPTION
109 * Validate flexfield.
110 *
111 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
112 *
113 * ARGUMENTS
114 * IN:
115 *
116 *
117 * NOTES
118 *
119 * MODIFICATION HISTORY
120 *
121 * 07-10-03 Kate Shan o Created
122
123 ************************************************/
124
125 PROCEDURE validate_desc_flexfield(
126 p_validation_date IN DATE
127 ) IS
128
129 BEGIN
130
131 FOR i IN 1..l_relationship_id.count LOOP
132
133 FND_FLEX_DESCVAL.set_context_value(l_attribute_category(i));
134
135 FND_FLEX_DESCVAL.set_column_value('ATTRIBUTE1', l_attribute1(i));
136 FND_FLEX_DESCVAL.set_column_value('ATTRIBUTE2', l_attribute2(i));
137 FND_FLEX_DESCVAL.set_column_value('ATTRIBUTE3', l_attribute3(i));
138 FND_FLEX_DESCVAL.set_column_value('ATTRIBUTE4', l_attribute4(i));
139 FND_FLEX_DESCVAL.set_column_value('ATTRIBUTE5', l_attribute5(i));
140 FND_FLEX_DESCVAL.set_column_value('ATTRIBUTE6', l_attribute6(i));
141 FND_FLEX_DESCVAL.set_column_value('ATTRIBUTE7', l_attribute7(i));
142 FND_FLEX_DESCVAL.set_column_value('ATTRIBUTE8', l_attribute8(i));
143 FND_FLEX_DESCVAL.set_column_value('ATTRIBUTE9', l_attribute9(i));
144 FND_FLEX_DESCVAL.set_column_value('ATTRIBUTE10', l_attribute10(i));
145 FND_FLEX_DESCVAL.set_column_value('ATTRIBUTE11', l_attribute11(i));
146 FND_FLEX_DESCVAL.set_column_value('ATTRIBUTE12', l_attribute12(i));
147 FND_FLEX_DESCVAL.set_column_value('ATTRIBUTE13', l_attribute13(i));
148 FND_FLEX_DESCVAL.set_column_value('ATTRIBUTE14', l_attribute14(i));
149 FND_FLEX_DESCVAL.set_column_value('ATTRIBUTE15', l_attribute15(i));
150 FND_FLEX_DESCVAL.set_column_value('ATTRIBUTE16', l_attribute16(i));
151 FND_FLEX_DESCVAL.set_column_value('ATTRIBUTE17', l_attribute17(i));
152 FND_FLEX_DESCVAL.set_column_value('ATTRIBUTE18', l_attribute18(i));
153 FND_FLEX_DESCVAL.set_column_value('ATTRIBUTE19', l_attribute19(i));
154 FND_FLEX_DESCVAL.set_column_value('ATTRIBUTE20', l_attribute20(i));
155
156 IF (NOT FND_FLEX_DESCVAL.validate_desccols(
157 'AR',
158 'HZ_ORG_CONTACTS',
159 'V',
160 p_validation_date)) THEN
161 l_flex_val_errors(i) := 1;
162 END IF;
163
164 END LOOP;
165
166 END validate_desc_flexfield;
167
168
169 /**********************************************
170 * public function validate_desc_flexfield_f
171 *
172 * DESCRIPTION
173 * Validate flexfield Function.
174 *
175 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
176 *
177 * ARGUMENTS
178 * IN:
179 *
180 *
181 * NOTES
182 *
183 * MODIFICATION HISTORY
184 *
185 * 18-Sep-08 Idris Ali o Created
186
187 ************************************************/
188 /* Validate desc flexfield HZ_ORG_CONTACTS. Used only when inserting
189 new Contacts because need to have a function to be called in MTI.
190 Returns Y if flexfield is valid. Returns null if invalid. */
191 FUNCTION validate_desc_flexfield_f(
192 p_attr_category IN VARCHAR2,
193 p_attr1 IN VARCHAR2,
194 p_attr2 IN VARCHAR2,
195 p_attr3 IN VARCHAR2,
196 p_attr4 IN VARCHAR2,
197 p_attr5 IN VARCHAR2,
198 p_attr6 IN VARCHAR2,
199 p_attr7 IN VARCHAR2,
200 p_attr8 IN VARCHAR2,
201 p_attr9 IN VARCHAR2,
202 p_attr10 IN VARCHAR2,
203 p_attr11 IN VARCHAR2,
204 p_attr12 IN VARCHAR2,
205 p_attr13 IN VARCHAR2,
206 p_attr14 IN VARCHAR2,
207 p_attr15 IN VARCHAR2,
208 p_attr16 IN VARCHAR2,
209 p_attr17 IN VARCHAR2,
210 p_attr18 IN VARCHAR2,
211 p_attr19 IN VARCHAR2,
212 p_attr20 IN VARCHAR2,
213 p_validation_date IN DATE
214 ) RETURN VARCHAR2 IS
215 BEGIN
216
217 FND_FLEX_DESCVAL.set_context_value(p_attr_category);
218
219 FND_FLEX_DESCVAL.set_column_value('ATTRIBUTE1', p_attr1);
220 FND_FLEX_DESCVAL.set_column_value('ATTRIBUTE2', p_attr2);
221 FND_FLEX_DESCVAL.set_column_value('ATTRIBUTE3', p_attr3);
222 FND_FLEX_DESCVAL.set_column_value('ATTRIBUTE4', p_attr4);
223 FND_FLEX_DESCVAL.set_column_value('ATTRIBUTE5', p_attr5);
224 FND_FLEX_DESCVAL.set_column_value('ATTRIBUTE6', p_attr6);
225 FND_FLEX_DESCVAL.set_column_value('ATTRIBUTE7', p_attr7);
226 FND_FLEX_DESCVAL.set_column_value('ATTRIBUTE8', p_attr8);
227 FND_FLEX_DESCVAL.set_column_value('ATTRIBUTE9', p_attr9);
228 FND_FLEX_DESCVAL.set_column_value('ATTRIBUTE10', p_attr10);
229 FND_FLEX_DESCVAL.set_column_value('ATTRIBUTE11', p_attr11);
230 FND_FLEX_DESCVAL.set_column_value('ATTRIBUTE12', p_attr12);
231 FND_FLEX_DESCVAL.set_column_value('ATTRIBUTE13', p_attr13);
232 FND_FLEX_DESCVAL.set_column_value('ATTRIBUTE14', p_attr14);
233 FND_FLEX_DESCVAL.set_column_value('ATTRIBUTE15', p_attr15);
234 FND_FLEX_DESCVAL.set_column_value('ATTRIBUTE16', p_attr16);
235 FND_FLEX_DESCVAL.set_column_value('ATTRIBUTE17', p_attr17);
236 FND_FLEX_DESCVAL.set_column_value('ATTRIBUTE18', p_attr18);
237 FND_FLEX_DESCVAL.set_column_value('ATTRIBUTE19', p_attr19);
238 FND_FLEX_DESCVAL.set_column_value('ATTRIBUTE20', p_attr20);
239
240 IF (FND_FLEX_DESCVAL.validate_desccols(
241 'AR',
242 'HZ_ORG_CONTACTS',
243 'V',
244 p_validation_date)) THEN
245 return 'Y';
246 ELSE
247 return null;
248 END IF;
249
250 END validate_desc_flexfield_f;
251
252
253
254 /**********************************************
255 * private procedure validate_DSS_security
256 *
257 * DESCRIPTION
258 * Check if the DSS security is
259 * granted to the user.
260 *
261 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
262 *
263 * ARGUMENTS
264 * IN:
265 *
266 *
267 * NOTES
268 *
269 * MODIFICATION HISTORY
270 *
271 * 07-10-03 Kate Shan o Created
272
273 ************************************************/
274
275 PROCEDURE validate_DSS_security IS
276 dss_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
277 dss_msg_count NUMBER := 0;
278 dss_msg_data VARCHAR2(2000):= null;
279 BEGIN
280
281 /* Check if the DSS security is granted to the user.
282 Only check for update. */
283 FOR i IN 1..l_relationship_id.count LOOP
284 l_dss_security_errors(i) :=
285 hz_dss_util_pub.test_instance(
286 p_operation_code => 'UPDATE',
287 p_db_object_name => 'HZ_RELATIONSHIPS',
288 p_instance_pk1_value => l_relationship_id(i),
289 p_instance_pk2_value => 'F',
290 p_user_name => fnd_global.user_name,
291 x_return_status => dss_return_status,
292 x_msg_count => dss_msg_count,
293 x_msg_data => dss_msg_data);
294
295 END LOOP;
296
297 END validate_DSS_security;
298
299
300 /**********************************************
301 * private procedure report_errors
302 *
303 * DESCRIPTION
304 * Report error.
305 *
306 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
307 *
308 * ARGUMENTS
309 * IN:
310 * P_ACTION IN VARCHAR2,
311 * P_DML_EXCEPTION IN VARCHAR2,
312 *
313 * NOTES Used by update procedure.
314 * Error is caught individually, it's reported individually
315 *
316 * MODIFICATION HISTORY
317 *
318 * 07-10-03 Kate Shan o Created
319 *
320 **********************************************/
321
322 PROCEDURE report_errors(
323 P_DML_RECORD IN HZ_IMP_LOAD_WRAPPER.DML_RECORD_TYPE,
324 P_ACTION IN VARCHAR2,
325 P_DML_EXCEPTION IN VARCHAR2
326 ) IS
327 l_error_id HZ_IMP_CONTACTS_INT.ERROR_ID%TYPE;
328 m NUMBER := 1;
329 n NUMBER := 1;
330 num_exp NUMBER;
331 exp_ind NUMBER := 1;
332
333 BEGIN
334
335 -- FND_FILE.PUT_LINE(FND_FILE.LOG, 'report_errors (+)');
336 /**********************************/
337 /* Validation and Error reporting */
338 /**********************************/
339
340 IF l_org_contact_id.count = 0 THEN
341 return;
342 END IF;
343
344 /**********************************/
345 /* Validation and Error reporting */
346 /**********************************/
347 l_num_row_processed := null;
348 l_num_row_processed := NUMBER_COLUMN();
349 l_num_row_processed.extend(l_org_contact_id.count);
350 l_exception_exists := null;
351 l_exception_exists := FLAG_ERROR();
352 l_exception_exists.extend(l_org_contact_id.count);
353 num_exp := SQL%BULK_EXCEPTIONS.COUNT;
354
355 -- FND_FILE.PUT_LINE(FND_FILE.LOG, ' ' || P_ACTION || ' Action, ' || ' total ' || num_exp || ' exceptions');
356
357 FOR k IN 1..l_org_contact_id.count LOOP
358
359 /* If DML fails due to validation errors or exceptions */
360 IF SQL%BULK_ROWCOUNT(k) = 0 THEN
361 -- FND_FILE.PUT_LINE(FND_FILE.LOG, ' DML fails at record ' || k || '!');
362
363 l_num_row_processed(k) := 0;
364
365 /* Check for any exceptions during DML */
366 IF P_DML_EXCEPTION = 'Y' THEN
367 /* determine if exception at this index */
368 FOR i IN exp_ind..num_exp LOOP
369 IF SQL%BULK_EXCEPTIONS(i).ERROR_INDEX = k THEN
370 l_exception_exists(k) := 'Y';
371 ELSIF SQL%BULK_EXCEPTIONS(i).ERROR_INDEX > k THEN
372 EXIT;
373 END IF;
374 END LOOP;
375 END IF; /* P_DML_EXCEPTION = 'Y' */
376
377 ELSE
378 l_num_row_processed(k) := 1;
379 END IF; /* SQL%BULK_ROWCOUNT(k) = 0 */
380 END LOOP;
381
382 /* insert into tmp error tables */
383 forall j in 1..l_org_contact_id.count
384 insert into hz_imp_tmp_errors
385 (
386 request_id,
387 batch_id,
388 int_row_id,
389 interface_table_name,
390 error_id,
391 creation_date,
392 created_by,
393 last_update_date,
394 last_updated_by,
395 last_update_login,
396 program_application_id,
397 program_id,
398 program_update_date,
399 ACTION_MISMATCH_FLAG,
400 e1_flag, e2_flag, e3_flag,e4_flag,e5_flag,
401 e6_flag,e7_flag,e8_flag,e9_flag,
402 e10_flag,e11_flag,e12_flag,
403 e13_flag,
404 e14_flag,e15_flag,e16_flag,e17_flag,
405 e18_flag,
406 OTHER_EXCEP_FLAG
407 )
408 (
409 select P_DML_RECORD.REQUEST_ID,
410 P_DML_RECORD.BATCH_ID,
411 l_row_id(j),
412 'HZ_IMP_CONTACTS_INT',
413 HZ_IMP_ERRORS_S.NextVal,
414 P_DML_RECORD.SYSDATE,
415 P_DML_RECORD.USER_ID,
416 P_DML_RECORD.SYSDATE,
417 P_DML_RECORD.USER_ID,
418 P_DML_RECORD.LAST_UPDATE_LOGIN,
419 P_DML_RECORD.PROGRAM_APPLICATION_ID,
420 P_DML_RECORD.PROGRAM_ID,
421 P_DML_RECORD.SYSDATE,
422
423 l_action_mismatch_errors(j),
424 l_error_flag(j),
425 'Y', 'Y', 'Y', 'Y',
426 l_department_code_errors(j),
427 l_title_errors(j),
428 l_job_title_code_errors(j),
429 l_decision_maker_flag_errors(j),
430 l_reference_use_flag_errors(j),
431 l_start_end_date_errors(j),
432 decode(l_flex_val_errors(j), 1, null, 'Y'),
433 'Y',
434 l_dup_rel_errors(j),
435 decode(l_dss_security_errors(j), FND_API.G_TRUE,'Y',null),
436 l_start_date_errors(j),
437 'Y',
438 'Y',
439 l_exception_exists(j)
440 from dual
441 where l_num_row_processed(j) = 0
442 );
443
444 -- FND_FILE.PUT_LINE(FND_FILE.LOG, 'report_errors (-)');
445
446 END report_errors;
447
448
449 /********************************************************************************
450 *
451 * PROCEDURE populate_error_table
452 *
453 * DESCRIPTION
454 *
455 *
456 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
457 *
458 * ARGUMENTS
459 * IN:
460 * P_DML_RECORD IN HZ_IMP_LOAD_WRAPPER.DML_RECORD_TYPE
461 * P_DUP_VAL_EXP IN VARCHAR2
462 * P_SQL_ERRM IN VARCHAR2
463 *
464 * OUT
465 * NOTES record errors in temp error when exception happens during insert or update.
466 *
467 * MODIFICATION HISTORY
468 *
469 * 08-27-03 Kate Shan o Created
470 *
471 ********************************************************************************/
472
473 PROCEDURE populate_error_table(
474 P_DML_RECORD IN HZ_IMP_LOAD_WRAPPER.DML_RECORD_TYPE,
475 P_DUP_VAL_EXP IN VARCHAR2,
476 P_SQL_ERRM IN VARCHAR2 ) IS
477
478 dup_val_exp_val VARCHAR2(1) := null;
479 other_exp_val VARCHAR2(1) := 'Y';
480 BEGIN
481
482 /* other entities need to add checking for other constraints */
483 if (P_DUP_VAL_EXP = 'Y') then
484 other_exp_val := null;
485 dup_val_exp_val := 'A';
486 end if;
487
488 insert into hz_imp_tmp_errors
489 (
490 request_id,
491 batch_id,
492 int_row_id,
493 interface_table_name,
494 error_id,
495 creation_date,
496 created_by,
497 last_update_date,
498 last_updated_by,
499 last_update_login,
500 program_application_id,
501 program_id,
502 program_update_date,
503 e1_flag,
504 e2_flag,
505 e3_flag,
506 e4_flag,
507 e5_flag,
508 e6_flag,
509 e7_flag,
510 e8_flag,
511 e9_flag,
512 e10_flag,
513 e11_flag,
514 e12_flag,
515 e13_flag,
516 e14_flag,
517 e15_flag,
518 e16_flag,
519 e17_flag,
520 e18_flag,
521 DUP_VAL_IDX_EXCEP_FLAG,
522 OTHER_EXCEP_FLAG
523 )
524 (
525 select P_DML_RECORD.REQUEST_ID,
526 P_DML_RECORD.BATCH_ID,
527 cnt_sg.int_row_id,
528 'HZ_IMP_CONTACTS_INT',
529 HZ_IMP_ERRORS_S.NextVal,
530 P_DML_RECORD.SYSDATE,
531 P_DML_RECORD.USER_ID,
532 P_DML_RECORD.SYSDATE,
533 P_DML_RECORD.USER_ID,
534 P_DML_RECORD.LAST_UPDATE_LOGIN,
535 P_DML_RECORD.PROGRAM_APPLICATION_ID,
536 P_DML_RECORD.PROGRAM_ID,
537 P_DML_RECORD.SYSDATE,
538 'Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y',
539 'Y',
540 dup_val_exp_val,
541 other_exp_val
542 from hz_imp_contacts_sg cnt_sg, hz_imp_contacts_int cnt_int
543 where cnt_sg.action_flag = 'I'
544 and cnt_int.rowid = cnt_sg.int_row_id
545 and cnt_int.batch_id = P_DML_RECORD.BATCH_ID
546 and cnt_int.sub_orig_system = P_DML_RECORD.OS
547 and cnt_int.sub_orig_system_reference
548 between P_DML_RECORD.FROM_OSR and P_DML_RECORD.TO_OSR
549 );
550
551 END populate_error_table;
552
553 /********************************************************************************
554 *
555 * PROCEDURE process_insert_orgcontacts
556 *
557 * DESCRIPTION
558 *
559 *
560 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
561 *
562 * ARGUMENTS
563 * IN:
564 * p_wu_os IN VARCHAR2,
565 * p_from_osr IN VARCHAR2,
566 * p_to_osr IN VARCHAR2,
567 * p_batch_id IN NUMBER
568 *
569 * OUT
570 * x_return_status OUT NOCOPY VARCHAR2
571 * x_msg_count OUT NOCOPY NUMBER
572 * x_msg_data OUT NOCOPY VARCHAR2
573 * NOTES
574 *
575 * MODIFICATION HISTORY
576 *
577 * 07-10-03 Kate Shan o Created
578
579 *
580 ********************************************************************************/
581
582
583 PROCEDURE process_insert_org_contacts (
584 P_DML_RECORD IN HZ_IMP_LOAD_WRAPPER.DML_RECORD_TYPE
585 ,x_return_status OUT NOCOPY VARCHAR2
586 ,x_msg_count OUT NOCOPY NUMBER
587 ,x_msg_data OUT NOCOPY VARCHAR2
588 ) IS
589
590 c_handle_insert RefCurType;
591
592 l_insert_sql1 varchar2(20000) :=
593 'BEGIN
594 insert all
595 when (error_flag is null
596 and sub_id is not null
597 and obj_id is not null
598 and relationship_type is not null
599 and rel_code_error is not null
600 and start_end_date_error is not null
601 and hierarchical_flag_error is not null
602 and action_mismatch_error is not null
603 and dup_rel_error is not null
604 and department_code_error is not null
605 and title_error is not null
606 and job_title_code_error is not null
607 and sbj_person_type_error is not null
608 and obj_org_type_error is not null
609 and decision_maker_flag_error is not null
610 and reference_use_flag_error is not null
611 and relate_self_error is not null
612 and createdby_error is not null
613 and flex_val_error is not null) then
614 into hz_parties (
615 created_by,
616 creation_date,
617 last_updated_by,
618 last_update_date,
619 last_update_login,
620 program_application_id,
621 program_id,
622 program_update_date,
623 request_id,
624 party_id,
625 party_number,
626 party_name,
627 party_type,
628 created_by_module,
629 orig_system_reference,
630 status,
631 object_version_number,
632 validated_flag)
633 values (
634 :user_id,
635 :l_sysdate,
636 :user_id,
637 :l_sysdate,
638 :last_update_login,
639 :program_application_id,
640 :program_id,
641 :l_sysdate,
642 :request_id,
643 hz_parties_s.nextval,
644 hz_party_number_s.nextval,
645 party_name,
646 ''PARTY_RELATIONSHIP'',
647 created_by_module,
648 hz_parties_s.nextval,
649 ''A'',
650 1, --object_version_number
651 ''N'')
652 into hz_party_usg_assignments (
653 application_id,
654 created_by,
655 creation_date,
656 last_updated_by,
657 last_update_date,
658 last_update_login,
659 program_application_id,
660 program_id,
661 request_id,
662 party_usg_assignment_id,
663 party_id,
664 party_usage_code,
665 owner_table_name,
666 owner_table_id,
667 effective_start_date,
668 effective_end_date,
669 status_flag,
670 created_by_module,
671 object_version_number)
672 values (
673 :application_id,
674 :user_id,
675 :l_sysdate,
676 :user_id,
677 :l_sysdate,
678 :last_update_login,
679 :program_application_id,
680 :program_id,
681 :request_id,
682 hz_party_usg_assignments_s.nextval,
683 sub_id,
684 ''ORG_CONTACT'',
685 ''HZ_RELATIONSHIPS'',
686 hz_relationships_s.nextval,
687 start_date,
688 end_date,
689 ''A'',
690 created_by_module,
691 1)
692 into hz_relationships (
693 actual_content_source,
694 application_id,
695 content_source_type,
696 created_by,
697 creation_date,
698 last_updated_by,
699 last_update_date,
700 last_update_login,
701 program_application_id,
702 program_id,
703 program_update_date,
704 request_id,
705 relationship_id,
706 subject_id,
707 subject_type,
708 subject_table_name,
709 object_id,
710 object_type,
711 object_table_name,
712 party_id,
713 relationship_code,
714 directional_flag,
715 comments,
716 start_date,
717 end_date,
718 status,
719 relationship_type,
720 object_version_number,
721 created_by_module,
722 direction_code)
723 values (
724 :actual_content_src,
725 :application_id,
726 ''USER_ENTERED'',
727 :user_id,
728 :l_sysdate,
729 :user_id,
730 :l_sysdate,
731 :last_update_login,
732 :program_application_id,
733 :program_id,
734 :l_sysdate,
735 :request_id,
736 hz_relationships_s.nextval,
737 sub_id,
738 sp_type,
739 ''HZ_PARTIES'',
740 obj_id,
741 op_type,
742 ''HZ_PARTIES'',
743 hz_parties_s.nextval,
744 relationship_code,
745 ''F'',
746 rel_comments,
747 start_date,
748 end_date,
749 ''A'',
750 relationship_type,
751 1,
752 created_by_module,
753 direction_code)
754 into hz_relationships (
755 actual_content_source,
756 application_id,
757 content_source_type,
758 created_by,
759 creation_date,
760 last_updated_by,
761 last_update_date,
762 last_update_login,
763 program_application_id,
764 program_id,
765 program_update_date,
766 request_id,
767 relationship_id,
768 subject_id,
769 subject_type,
770 subject_table_name,
771 object_id,
772 object_type,
773 object_table_name,
774 party_id,
775 relationship_code,
776 directional_flag,
777 comments,
778 start_date,
779 end_date,
780 status,
781 relationship_type,
782 object_version_number,
783 created_by_module,
784 direction_code)
785 values (
786 :actual_content_src,
787 :application_id,
788 ''USER_ENTERED'',
789 :user_id,
790 :l_sysdate,
791 :user_id,
792 :l_sysdate,
793 :last_update_login,
794 :program_application_id,
795 :program_id,
796 :l_sysdate,
797 :request_id,
798 hz_relationships_s.nextval,
799 obj_id,
800 op_type,
801 ''HZ_PARTIES'',
802 sub_id,
803 sp_type,
804 ''HZ_PARTIES'',
805 hz_parties_s.nextval,
806 backward_rel_code,
807 ''B'',
808 rel_comments,
809 start_date,
810 end_date,
811 ''A'',
812 relationship_type,
813 1, -- object_version_number,
814 created_by_module,
815 decode(direction_code, ''P'', ''C'', ''C'', ''P'', ''N''))
816 into hz_org_contacts (
817 application_id,
818 created_by,
819 creation_date,
820 last_updated_by,
821 last_update_date,
822 last_update_login,
823 program_application_id,
824 program_id,
825 program_update_date,
826 request_id,
827 org_contact_id,
828 party_relationship_id,
829 contact_number,
830 department_code,
831 department,
832 title,
833 job_title,
834 job_title_code,
835 decision_maker_flag,
836 reference_use_flag,
837 comments,
838 orig_system_reference,
839 status,
840 object_version_number,
841 created_by_module,
842 attribute_category,
843 attribute1,
844 attribute2,
845 attribute3,
846 attribute4,
847 attribute5,
848 attribute6,
849 attribute7,
850 attribute8,
851 attribute9,
852 attribute10,
853 attribute11,
854 attribute12,
855 attribute13,
856 attribute14,
857 attribute15,
858 attribute16,
859 attribute17,
860 attribute18,
861 attribute19,
862 attribute20)
863 values (
864 :application_id,
865 :user_id,
866 :l_sysdate,
867 :user_id,
868 :l_sysdate,
869 :last_update_login,
870 :program_application_id,
871 :program_id,
872 :l_sysdate,
873 :request_id,
874 contact_id, --hz_org_contacts_s.nextval,
875 hz_relationships_s.nextval,
876 nvl(contact_number, hz_contact_numbers_s.nextval),
877 department_code,
878 department,
879 title,
880 job_title,
881 job_title_code,
882 decision_maker_flag,
883 reference_use_flag,
884 comments,
885 contact_orig_system_reference,
886 ''A'',
887 1,
888 created_by_module,
889 attribute_category,
890 attribute1,
891 attribute2,
892 attribute3,
893 attribute4,
894 attribute5,
895 attribute6,
896 attribute7,
897 attribute8,
898 attribute9,
899 attribute10,
900 attribute11,
901 attribute12,
902 attribute13,
903 attribute14,
904 attribute15,
905 attribute16,
906 attribute17,
907 attribute18,
908 attribute19,
909 attribute20)
910 into hz_orig_sys_references (
911 application_id,
912 created_by,
913 creation_date,
914 last_updated_by,
915 last_update_date,
916 last_update_login,
917 orig_system_ref_id,
918 orig_system,
919 orig_system_reference,
920 owner_table_name,
921 owner_table_id,
922 status,
923 party_id, -- relationship party party_id
924 start_date_active,
925 created_by_module,
926 object_version_number,
927 request_id,
928 program_application_id,
929 program_id,
930 program_update_date)
931 values (
932 :application_id,
933 :user_id,
934 :l_sysdate,
935 :user_id,
936 :l_sysdate,
937 :last_update_login,
938 hz_orig_system_ref_s.nextval,
939 contact_orig_system,
940 contact_orig_system_reference,
941 ''HZ_ORG_CONTACTS'',
942 contact_id, ''A'',
943 hz_parties_s.nextval,
944 :l_sysdate,
945 created_by_module,
946 1,
947 :request_id,
948 :program_application_id,
949 :program_id,
950 :l_sysdate)
951
952 else
953 into hz_imp_tmp_errors (
954 created_by,
955 creation_date,
956 last_updated_by,
957 last_update_date,
958 last_update_login,
959 program_application_id,
960 program_id,
961 program_update_date,
962
963 error_id,
964 batch_id,
965 request_id,
966 int_row_id,
967 interface_table_name,
968 ACTION_MISMATCH_FLAG,
969 e1_flag,
970 e2_flag,
971 e3_flag,
972 e4_flag,
973 e5_flag,
974 e6_flag,
975 e7_flag,
976 e8_flag,
977 e9_flag,
978 e10_flag,
979 e11_flag,
980 e12_flag,
981 e13_flag,
982 e14_flag,
983 e15_flag,
984 e16_flag,
985 e17_flag,
986 e18_flag
987 )
988 values (
989 :user_id,
990 :l_sysdate,
991 :user_id,
992 :l_sysdate,
993 :last_update_login,
994 :program_application_id,
995 :program_id,
996 :l_sysdate,
997
998 hz_imp_errors_s.nextval,
999 :p_batch_id,
1000 :request_id,
1001 row_id,
1002 ''HZ_IMP_CONTACTS_INT'',
1003 action_mismatch_error,
1004 nvl2(sub_id, ''Y'', null),
1005 nvl2(obj_id, ''Y'', null),
1006 sbj_person_type_error,
1007 obj_org_type_error,
1008 rel_code_error,
1009 department_code_error,
1010 title_error,
1011 job_title_code_error,
1012 decision_maker_flag_error,
1013 reference_use_flag_error,
1014 start_end_date_error,
1015 flex_val_error,
1016 hierarchical_flag_error,
1017 dup_rel_error,
1018 ''Y'',
1019 ''Y'',
1020 relate_self_error,
1021 createdby_error)';
1022 l_insert_sql2 varchar2(20000) :=
1023 '
1024 select /*+ use_nl(rt) */ rs.row_id,
1025 rs.contact_orig_system,
1026 rs.contact_orig_system_reference,
1027 rs.sub_id,
1028 rs.sp_type,
1029 rs.obj_id,
1030 rs.op_type,
1031 rs.party_name,
1032 rs.contact_id,
1033 rs.contact_number,
1034 rs.department_code,
1035 rs.department,
1036 rs.title,
1037 rs.job_title,
1038 rs.job_title_code,
1039 rs.decision_maker_flag,
1040 rs.reference_use_flag,
1041 rs.comments,
1042 rt.direction_code,
1043 rt.backward_rel_code,
1044 rs.relationship_type,
1045 rs.relationship_code,
1046 rs.start_date,
1047 rs.end_date,
1048 rs.rel_comments,
1049 rs.attribute_category,
1050 rs.attribute1,
1051 rs.attribute2,
1052 rs.attribute3,
1053 rs.attribute4,
1054 rs.attribute5,
1055 rs.attribute6,
1056 rs.attribute7,
1057 rs.attribute8,
1058 rs.attribute9,
1059 rs.attribute10,
1060 rs.attribute11,
1061 rs.attribute12,
1062 rs.attribute13,
1063 rs.attribute14,
1064 rs.attribute15,
1065 rs.attribute16,
1066 rs.attribute17,
1067 rs.attribute18,
1068 rs.attribute19,
1069 rs.attribute20,
1070 rs.created_by_module,
1071 rs.error_flag,
1072 rs.action_mismatch_error,
1073 rs.start_end_date_error,
1074 rs.sbj_person_type_error,
1075 rs.obj_org_type_error,
1076 rs.rel_code_error,
1077 rs.createdby_error,
1078 decode(rt.hierarchical_flag, null , ''Y'', ''N'', decode (rt.allow_circular_relationships, ''Y'', ''Y'', null), null) hierarchical_flag_error,
1079 rs.department_code_error,
1080 rs.title_error,
1081 rs.decision_maker_flag_error,
1082 rs.job_title_code_error,
1083 rs.reference_use_flag_error,
1084 rs.dup_rel_error,
1085 decode(rs.obj_id, rs.sub_id, decode(rt.allow_relate_to_self_flag, ''N'', null,''Y''), ''Y'') relate_self_error,
1086 decode(:l_val_flex, ''Y'',
1087 HZ_IMP_LOAD_ORG_CONTACT_PKG.validate_desc_flexfield_f(
1088 rs.attribute_category, rs.attribute1, rs.attribute2, rs.attribute3, rs.attribute4,
1089 rs.attribute5, rs.attribute6, rs.attribute7, rs.attribute8, rs.attribute9,
1090 rs.attribute10, rs.attribute11, rs.attribute12, rs.attribute13, rs.attribute14,
1091 rs.attribute15, rs.attribute16, rs.attribute17, rs.attribute18, rs.attribute19,
1092 rs.attribute20, :l_sysdate
1093 ), ''Y'') flex_val_error
1094 from hz_relationship_types rt, (
1095 select /*+ ordered index(sp, HZ_PARTIES_U1) index(op, HZ_PARTIES_U1) */
1096 ocint.rowid row_id,
1097 ocsg.contact_orig_system,
1098 ocsg.contact_orig_system_reference,
1099 ocsg.sub_id,
1100 sp.party_type sp_type,
1101 ocsg.obj_id,
1102 op.party_type op_type,
1103 substrb(sp.party_name || ''-'' || op.party_name, 1, 360) party_name,
1104 ocsg.contact_id,
1105 ocint.contact_number,
1106 nullif(ocint.department_code, :p_gmiss_char) department_code,
1107 nullif(ocint.department, :p_gmiss_char) department,
1108 nullif(ocint.title, :p_gmiss_char) title,
1109 nullif(ocint.job_title, :p_gmiss_char) job_title,
1110 nullif(ocint.job_title_code, :p_gmiss_char) job_title_code,
1111 nullif(ocint.decision_maker_flag, :p_gmiss_char) decision_maker_flag,
1112 nullif(ocint.reference_use_flag, :p_gmiss_char) reference_use_flag,
1113 nullif(ocint.comments, :p_gmiss_char) comments,
1114 ocint.relationship_type,
1115 ocint.relationship_code,
1116 nvl(nullif(ocint.start_date, :p_gmiss_date), :l_sysdate) start_date,
1117 nvl(nullif(ocint.end_date, :p_gmiss_date), :l_no_end_date) end_date,
1118 ocint.rel_comments,
1119 nullif(ocint.attribute_category, :p_gmiss_char) attribute_category,
1120 nullif(ocint.attribute1, :p_gmiss_char) attribute1,
1121 nullif(ocint.attribute2, :p_gmiss_char) attribute2,
1122 nullif(ocint.attribute3, :p_gmiss_char) attribute3,
1123 nullif(ocint.attribute4, :p_gmiss_char) attribute4,
1124 nullif(ocint.attribute5, :p_gmiss_char) attribute5,
1125 nullif(ocint.attribute6, :p_gmiss_char) attribute6,
1126 nullif(ocint.attribute7, :p_gmiss_char) attribute7,
1127 nullif(ocint.attribute8, :p_gmiss_char) attribute8,
1128 nullif(ocint.attribute9, :p_gmiss_char) attribute9,
1129 nullif(ocint.attribute10, :p_gmiss_char) attribute10,
1130 nullif(ocint.attribute11, :p_gmiss_char) attribute11,
1131 nullif(ocint.attribute12, :p_gmiss_char) attribute12,
1132 nullif(ocint.attribute13, :p_gmiss_char) attribute13,
1133 nullif(ocint.attribute14, :p_gmiss_char) attribute14,
1134 nullif(ocint.attribute15, :p_gmiss_char) attribute15,
1135 nullif(ocint.attribute16, :p_gmiss_char) attribute16,
1136 nullif(ocint.attribute17, :p_gmiss_char) attribute17,
1137 nullif(ocint.attribute18, :p_gmiss_char) attribute18,
1138 nullif(ocint.attribute19, :p_gmiss_char) attribute19,
1139 nullif(ocint.attribute20, :p_gmiss_char) attribute20,
1140 nvl(nullif(ocint.created_by_module, :p_gmiss_char), ''HZ_IMPORT'') created_by_module,
1141 ocsg.error_flag,
1142 nvl2(nullif(nullif(ocint.insert_update_flag, :p_gmiss_char), ocsg.action_flag), null, ''Y'') action_mismatch_error,
1143 decode(ocint.end_date, null, ''Y'', decode(sign(ocint.end_date - nvl(ocint.start_date, sysdate)), -1, null, ''Y'')) start_end_date_error,
1144 decode(sp.party_type, ''PERSON'', ''Y'', null) sbj_person_type_error,
1145 decode(op.party_type, ''ORGANIZATION'', ''Y'', ''PERSON'', ''Y'', null) obj_org_type_error,
1146 nvl2(party_rel_type_l.lookup_code, ''Y'', null) rel_code_error,
1147 nvl2(ocint.department_code, nvl2(dept_l.lookup_code, ''Y'', null), ''Y'') department_code_error,
1148 nvl2(ocint.title, nvl2(title_l.lookup_code, ''Y'', null), ''Y'') title_error,
1149 decode (ocint.decision_maker_flag, ''Y'', ''Y'', ''N'', ''N'', null, ''Z'', null) decision_maker_flag_error,
1150 nvl2(ocint.job_title_code, nvl2(job_title_code_l.lookup_code, ''Y'', null), ''Y'') job_title_code_error,
1151 decode (ocint.reference_use_flag, ''Y'', ''Y'', ''N'', ''N'', null, ''Z'', null) reference_use_flag_error,
1152 ocint.interface_status,
1153 decode(tc.a, 0, ''Y'') dup_rel_error,
1154 nvl2(nullif(ocint.created_by_module, :p_gmiss_char), nvl2(createdby_l.lookup_code, ''Y'', null), ''Y'') createdby_error
1155
1156 from hz_imp_contacts_sg ocsg,
1157 hz_imp_contacts_int ocint,
1158 (select 0 a from dual union all select 1 a from dual) tc,
1159 hz_parties sp,
1160 hz_parties op,
1161 fnd_lookup_values party_rel_type_l,
1162 fnd_lookup_values dept_l,
1163 fnd_lookup_values title_l,
1164 fnd_lookup_values job_title_code_l,
1165 fnd_lookup_values createdby_l
1166 where ocint.rowid = ocsg.int_row_id
1167 -- validate subject id and object id
1168 and ocsg.sub_id = sp.party_id (+)
1169 and ocsg.obj_id = op.party_id (+)
1170 and ocsg.action_flag = ''I''
1171 and ocsg.batch_mode_flag = :p_batch_mode_flag
1172 and ocsg.batch_id = :p_batch_id
1173 and ocsg.sub_orig_system = :p_wu_os
1174 and ocsg.sub_orig_system_reference between :p_from_osr and :p_to_osr
1175 -- validate relationship code
1176 and party_rel_type_l.lookup_code (+) = ocint.relationship_code
1177 and party_rel_type_l.lookup_type (+) = ''PARTY_RELATIONS_TYPE''
1178 and party_rel_type_l.language (+) = userenv(''LANG'')
1179 and party_rel_type_l.view_application_id (+) = 222
1180 and party_rel_type_l.security_group_id (+) =
1181 fnd_global.lookup_security_group(''PARTY_RELATIONS_TYPE'', 222)
1182 -- validate department_code
1183 and dept_l.lookup_code (+) = ocint.department_code
1184 and dept_l.lookup_type (+) = ''DEPARTMENT_TYPE''
1185 and dept_l.language (+) = userenv(''LANG'')
1186 and dept_l.view_application_id (+) = 222
1187 and dept_l.security_group_id (+) =
1188 fnd_global.lookup_security_group(''DEPARTMENT_TYPE'', 222)
1189 -- validate title
1190 and title_l.lookup_code (+) = ocint.title
1191 and title_l.lookup_type (+) = ''CONTACT_TITLE''
1192 and title_l.language (+) = userenv(''LANG'')
1193 and title_l.view_application_id (+) = 222
1194 and title_l.security_group_id (+) =
1195 fnd_global.lookup_security_group(''CONTACT_TITLE'', 222)
1196 -- validate job_title_code
1197 and job_title_code_l.lookup_code (+) = ocint.job_title_code
1198 and job_title_code_l.lookup_type (+) = ''RESPONSIBILITY''
1199 and job_title_code_l.language (+) = userenv(''LANG'')
1200 and job_title_code_l.view_application_id (+) = 222
1201 and job_title_code_l.security_group_id (+) =
1202 fnd_global.lookup_security_group(''RESPONSIBILITY'', 222)
1203 and createdby_l.lookup_code (+) = ocint.created_by_module
1204 and createdby_l.lookup_type (+) = ''HZ_CREATED_BY_MODULES''
1205 and createdby_l.language (+) = userenv(''LANG'')
1206 and createdby_l.view_application_id (+) = 222
1207 and createdby_l.security_group_id (+) =
1208 fnd_global.lookup_security_group(''HZ_CREATED_BY_MODULES'', 222)
1209 and tc.a = (select count(*) ---- check relationship duplicate, 0 indicates no error
1210 from hz_relationships r1
1211 where r1.subject_id = ocsg.sub_id
1212 and r1.subject_table_name = ''HZ_PARTIES''
1213 and r1.object_id = ocsg.obj_id
1214 and r1.relationship_type = ocint.relationship_type
1215 and r1.relationship_code = ocint.relationship_code
1216 and nvl(ocint.end_date, :l_no_end_date) >= r1.start_date
1217 and nvl(r1.end_date, :l_no_end_date) >= ocint.start_date
1218 and r1.actual_content_source= :actual_content_src
1219 and r1.status = ''A''
1220 and rownum = 1)
1221 ';
1222
1223 l_insert_sql3 varchar2(2000) :='
1224 ) rs
1225 where rs.relationship_type = rt.relationship_type (+)
1226 and rs.relationship_code = rt.forward_rel_code (+)
1227 and rs.sp_type = rt.subject_type (+)
1228 and rs.op_type = rt.object_type (+)
1229 ';
1230
1231 -- append this when P_DML_RECORD.ALLOW_DISABLED_LOOKUP = 'N'
1232 l_where_enabled_lookup_sql varchar2(3000) :=
1233 ' AND ( party_rel_type_l.ENABLED_FLAG(+) = ''Y'' AND
1234 TRUNC(:l_sysdate) BETWEEN
1235 TRUNC(NVL( party_rel_type_l.START_DATE_ACTIVE,:l_sysdate ) ) AND
1236 TRUNC(NVL( party_rel_type_l.END_DATE_ACTIVE,:l_sysdate ) ) )
1237 AND ( dept_l.ENABLED_FLAG(+) = ''Y'' AND
1238 TRUNC(:l_sysdate) BETWEEN
1239 TRUNC(NVL( dept_l.START_DATE_ACTIVE,:l_sysdate ) ) AND
1240 TRUNC(NVL( dept_l.END_DATE_ACTIVE,:l_sysdate ) ) )
1241 AND ( title_l.ENABLED_FLAG(+) = ''Y'' AND
1242 TRUNC(:l_sysdate) BETWEEN
1243 TRUNC(NVL( title_l.START_DATE_ACTIVE,:l_sysdate ) ) AND
1244 TRUNC(NVL( title_l.END_DATE_ACTIVE,:l_sysdate ) ) )
1245 AND ( job_title_code_l.ENABLED_FLAG(+) = ''Y'' AND
1246 TRUNC(:l_sysdate) BETWEEN
1247 TRUNC(NVL( job_title_code_l.START_DATE_ACTIVE,:l_sysdate ) ) AND
1248 TRUNC(NVL( job_title_code_l.END_DATE_ACTIVE,:l_sysdate ) ) )
1249 AND ( createdby_l.ENABLED_FLAG(+) = ''Y'' AND
1250 TRUNC(:l_sysdate) BETWEEN
1251 TRUNC(NVL( createdby_l.START_DATE_ACTIVE,:l_sysdate ) ) AND
1252 TRUNC(NVL( createdby_l.END_DATE_ACTIVE,:l_sysdate ) ) )
1253 ';
1254
1255 -- append this where clause when it is a new batch
1256 l_where_first_run_sql varchar2(35) := ' AND rs.interface_status is null';
1257
1258 -- append this where clause when it is a rerun batch
1259 l_where_rerun_sql varchar2(35) := ' AND rs.interface_status = ''C''';
1260
1261 l_end_sql VARCHAR2(10) := '; END;';
1262
1263 l_dml_exception varchar2(1) := 'N';
1264
1265 BEGIN
1266
1267 savepoint process_insert_contacts_pvt;
1268
1269 FND_FILE.PUT_LINE(FND_FILE.LOG, 'process_insert_org_contacts (+)');
1270
1271 FND_MSG_PUB.initialize;
1272
1273 --Initialize API return status to success.
1274 x_return_status := FND_API.G_RET_STS_SUCCESS;
1275
1276 /*
1277 FND_FILE.PUT_LINE(FND_FILE.LOG, 'fetch data from cursor start ' || dbms_utility.get_time);
1278 FND_FILE.PUT_LINE(FND_FILE.LOG, 'l_no_end_date = ' || l_no_end_date);
1279 FND_FILE.PUT_LINE(FND_FILE.LOG, 'P_DML_RECORD.ACTUAL_CONTENT_SRC = ' || P_DML_RECORD.ACTUAL_CONTENT_SRC);
1280 FND_FILE.PUT_LINE(FND_FILE.LOG, 'P_BATCH_ID = ' || P_BATCH_ID);
1281 FND_FILE.PUT_LINE(FND_FILE.LOG, 'P_WU_OS = ' || P_WU_OS);
1282 FND_FILE.PUT_LINE(FND_FILE.LOG, 'P_FROM_OSR = ' || P_FROM_OSR);
1283 FND_FILE.PUT_LINE(FND_FILE.LOG, 'P_TO_OSR = ' || P_TO_OSR);
1284 */
1285
1286 IF P_DML_RECORD.ALLOW_DISABLED_LOOKUP = 'Y' THEN
1287 --FND_FILE.PUT_LINE(FND_FILE.LOG,'l_allow_disabled_lookup = Y');
1288
1289 IF P_DML_RECORD.RERUN = 'N' THEN
1290 -- First Run
1291 --FND_FILE.PUT_LINE(FND_FILE.LOG, 'First run');
1292 EXECUTE IMMEDIATE l_insert_sql1 || l_insert_sql2 || l_insert_sql3 || l_where_first_run_sql || l_end_sql
1293 USING
1294 p_dml_record.user_id,
1295 p_dml_record.sysdate,
1296 p_dml_record.last_update_login,
1297 p_dml_record.program_application_id,
1298 p_dml_record.program_id,
1299 p_dml_record.request_id,
1300 p_dml_record.application_id,
1301 p_dml_record.actual_content_src,
1302
1303 p_dml_record.batch_id,
1304 p_dml_record.flex_validation,
1305 p_dml_record.gmiss_char,
1306 p_dml_record.gmiss_date,
1307 l_no_end_date,
1308 --p_dml_record.gmiss_char,
1309 p_dml_record.batch_mode_flag,
1310 p_dml_record.os,
1311 p_dml_record.from_osr,
1312 p_dml_record.to_osr;
1313
1314 ELSE
1315 -- Rerun
1316 -- FND_FILE.PUT_LINE(FND_FILE.LOG, 'Re-run');
1317 EXECUTE IMMEDIATE l_insert_sql1 || l_insert_sql2 || l_insert_sql3 || l_where_rerun_sql || l_end_sql
1318 USING
1319 p_dml_record.user_id,
1320 p_dml_record.sysdate,
1321 p_dml_record.last_update_login,
1322 p_dml_record.program_application_id,
1323 p_dml_record.program_id,
1324 p_dml_record.request_id,
1325
1326 p_dml_record.application_id,
1327 p_dml_record.actual_content_src,
1328
1329 p_dml_record.batch_id,
1330 p_dml_record.flex_validation,
1331 p_dml_record.gmiss_char,
1332 p_dml_record.gmiss_date,
1333 l_no_end_date,
1334 --p_dml_record.gmiss_char,
1335 p_dml_record.batch_mode_flag,
1336 p_dml_record.os,
1337 p_dml_record.from_osr,
1338 p_dml_record.to_osr;
1339
1340 END IF;
1341
1342 ELSE -- l_allow_disabled_lookup
1343 -- FND_FILE.PUT_LINE(FND_FILE.LOG,'l_allow_disabled_lookup = N');
1344
1345 IF P_DML_RECORD.RERUN = 'N' THEN
1346
1347 -- First Run
1348 -- FND_FILE.PUT_LINE(FND_FILE.LOG, 'First run');
1349 EXECUTE IMMEDIATE l_insert_sql1 || l_insert_sql2 || l_where_enabled_lookup_sql || l_insert_sql3 || l_where_first_run_sql || l_end_sql
1350 USING
1351 p_dml_record.user_id,
1352 p_dml_record.sysdate,
1353 p_dml_record.last_update_login,
1354 p_dml_record.program_application_id,
1355 p_dml_record.program_id,
1356 p_dml_record.request_id,
1357
1358 p_dml_record.application_id,
1359 p_dml_record.actual_content_src,
1360
1361 p_dml_record.batch_id,
1362 p_dml_record.flex_validation,
1363 p_dml_record.gmiss_char,
1364 p_dml_record.gmiss_date,
1365 l_no_end_date,
1366 --p_dml_record.gmiss_char,
1367 p_dml_record.batch_mode_flag,
1368 p_dml_record.os,
1369 p_dml_record.from_osr,
1370 p_dml_record.to_osr;
1371
1372
1373 ELSE
1374 -- Rerun
1375 -- FND_FILE.PUT_LINE(FND_FILE.LOG, 'Re-run');
1376 EXECUTE IMMEDIATE l_insert_sql1 || l_insert_sql2 || l_where_enabled_lookup_sql || l_insert_sql3 || l_where_rerun_sql || l_end_sql
1377 USING
1378 p_dml_record.user_id,
1379 p_dml_record.sysdate,
1380 p_dml_record.last_update_login,
1381 p_dml_record.program_application_id,
1382 p_dml_record.program_id,
1383 p_dml_record.request_id,
1384
1385 p_dml_record.application_id,
1386 p_dml_record.actual_content_src,
1387
1388 p_dml_record.batch_id,
1389 p_dml_record.flex_validation,
1390 p_dml_record.gmiss_char,
1391 p_dml_record.gmiss_date,
1392 l_no_end_date,
1393 --p_dml_record.gmiss_char,
1394 p_dml_record.batch_mode_flag,
1395 p_dml_record.os,
1396 p_dml_record.from_osr,
1397 p_dml_record.to_osr;
1398
1399 END IF;
1400
1401
1402 END IF;
1403
1404 FND_FILE.PUT_LINE(FND_FILE.LOG, 'process_insert_org_contacts (-)');
1405
1406 EXCEPTION
1407
1408 WHEN DUP_VAL_ON_INDEX THEN
1409 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Insert contacts dup val exception: ' || SQLERRM);
1410 ROLLBACK to process_insert_contacts_pvt;
1411
1412 populate_error_table(P_DML_RECORD, 'Y', SQLERRM);
1413 x_return_status := FND_API.G_RET_STS_ERROR;
1414
1415 FND_MESSAGE.SET_NAME('AR', 'HZ_API_OTHERS_EXCEP');
1416 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
1417 FND_MSG_PUB.ADD;
1418
1419 WHEN OTHERS THEN
1420 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Insert org contacts other exception: ' || SQLERRM);
1421 ROLLBACK to process_insert_contacts_pvt;
1422
1423 populate_error_table(P_DML_RECORD, 'N', SQLERRM);
1424 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1425
1426 FND_MESSAGE.SET_NAME('AR', 'HZ_API_OTHERS_EXCEP');
1427 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
1428 FND_MSG_PUB.ADD;
1429
1430 END process_insert_org_contacts;
1431
1432
1433
1434 /********************************************************************************
1435 *
1436 * PROCEDURE process_update_org_contacts
1437 *
1438 * DESCRIPTION
1439 *
1440 *
1441 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
1442 *
1443 * ARGUMENTS
1444 * IN:
1445 * p_wu_os IN VARCHAR2,
1446 * p_from_osr IN VARCHAR2,
1447 * p_to_osr IN VARCHAR2,
1448 * p_batch_id IN NUMBER
1449 *
1450 * OUT
1451 * x_return_status OUT NOCOPY VARCHAR2
1452 * x_msg_count OUT NOCOPY NUMBER
1453 * x_msg_data OUT NOCOPY VARCHAR2
1454 * NOTES
1455 *
1456 * MODIFICATION HISTORY
1457 *
1458 * 07-10-03 Kate Shan o Created
1459 *
1460 ********************************************************************************/
1461
1462 PROCEDURE process_update_org_contacts (
1463 P_DML_RECORD IN HZ_IMP_LOAD_WRAPPER.DML_RECORD_TYPE
1464 ,x_return_status OUT NOCOPY VARCHAR2
1465 ,x_msg_count OUT NOCOPY NUMBER
1466 ,x_msg_data OUT NOCOPY VARCHAR2
1467 ) IS
1468
1469 c_handle_update RefCurType;
1470
1471 /* Fewer validations than insert because many columns like subject, object,
1472 rel type, rel code etc are not updateable */
1473 l_update_sql varchar2(25000) :=
1474 'SELECT /*+ leading(cs) use_nl(ci) rowid(ci) */
1475 ci.ROWID,
1476
1477 -- org contact columns
1478 oc.org_contact_id,
1479 ci.contact_number,
1480 ci.department_code,
1481 ci.department,
1482 ci.title,
1483 ci.job_title,
1484 ci.job_title_code,
1485 ci.decision_maker_flag,
1486 ci.reference_use_flag,
1487 ci.comments,
1488
1489 -- relationship columns
1490 r.relationship_id,
1491 ci.start_date,
1492 ci.end_date,
1493 ci.rel_comments,
1494
1495 -- org contact attribute columns
1496 ci.ATTRIBUTE_CATEGORY,
1497 ci.ATTRIBUTE1,
1498 ci.ATTRIBUTE2,
1499 ci.ATTRIBUTE3,
1500 ci.ATTRIBUTE4,
1501 ci.ATTRIBUTE5,
1502 ci.ATTRIBUTE6,
1503 ci.ATTRIBUTE7,
1504 ci.ATTRIBUTE8,
1505 ci.ATTRIBUTE9,
1506 ci.ATTRIBUTE10,
1507 ci.ATTRIBUTE11,
1508 ci.ATTRIBUTE12,
1509 ci.ATTRIBUTE13,
1510 ci.ATTRIBUTE14,
1511 ci.ATTRIBUTE15,
1512 ci.ATTRIBUTE16,
1513 ci.ATTRIBUTE17,
1514 ci.ATTRIBUTE18,
1515 ci.ATTRIBUTE19,
1516 ci.ATTRIBUTE20,
1517
1518 -- errors
1519 cs.ERROR_FLAG error_flag,
1520 decode(nvl(ci.insert_update_flag, cs.action_flag), cs.action_flag, ''Y'', null) action_mismatch_error,
1521 0 flex_val_errors,
1522 ''T'' dss_security_errors,
1523 --decode(ci.department_code, null, ''Y'', dept_l.lookup_code) department_code_error,
1524 nvl2(ci.department_code, nvl2(dept_l.lookup_code, ''Y'', null), ''Y'') department_code_error, --bug 7034169
1525 -- decode(ci.title, null, ''Y'', title_l.lookup_code) title_error,
1526 nvl2(ci.title, nvl2(title_l.lookup_code, ''Y'', null), ''Y'') title_error,--bug 7034169
1527 decode(ci.decision_maker_flag , null, ''Y'', decision_l.lookup_code) decision_maker_flag_error,
1528 --decode(ci.job_title_code, null, ''Y'', job_title_code_l.lookup_code) job_title_code_error,
1529 nvl2(ci.job_title_code, nvl2(job_title_code_l.lookup_code, ''Y'', null), ''Y'') job_title_code_error,--bug7034169
1530 decode(ci.reference_use_flag, null, ''Y'', reference_use_l.lookup_code) reference_use_flag_error,
1531 decode(ci.START_DATE, :G_MISS_DATE, null, ''Y'') start_date_error,
1532 decode(ci.END_DATE,
1533 null,
1534 decode(r.END_DATE, null, ''Y'',
1535 decode(ci.START_DATE, null, decode(sign(r.END_DATE - r.START_DATE), -1, null, ''Y''),
1536 decode(sign(r.END_DATE - ci.START_DATE), -1, null, ''Y''))),
1537 :G_MISS_DATE, ''Y'',
1538 decode(ci.START_DATE, null, decode(sign(ci.END_DATE - r.START_DATE), -1, null, ''Y''),
1539 decode(sign(ci.END_DATE - ci.START_DATE), -1, null, ''Y''))
1540 ) start_end_date_error,
1541 decode(tc.a, 0, ''Y'') dup_rel_error
1542 /*
1543 (select r1.relationship_id from hz_relationships r1
1544 where r1.subject_id = r.subject_id
1545 and r1.object_id = r.object_id
1546 and r1.object_type = r.object_type
1547 and r1.subject_type = r.subject_type
1548 and r1.relationship_type = r.relationship_type
1549 and r1.relationship_code = r.relationship_code
1550 and decode(ci.end_date, :G_MISS_DATE, :l_no_end_date, null, nvl(r.end_date, :l_no_end_date)) >= r1.start_date
1551 and nvl(r1.end_date, :l_no_end_date) >= nvl(ci.start_date, r.start_date)
1552 and r1.actual_content_source= r.content_source_type
1553 and r1.status = ''A''
1554 and r1.relationship_id <> r.relationship_id
1555 and rownum = 1
1556 ) identical_rel
1557 */
1558 FROM HZ_IMP_CONTACTS_INT ci,
1559 HZ_IMP_CONTACTS_SG cs,
1560 HZ_RELATIONSHIPS r,
1561 HZ_ORG_CONTACTS oc,
1562 AR_LOOKUPS dept_l,
1563 AR_LOOKUPS title_l,
1564 AR_LOOKUPS decision_l,
1565 AR_LOOKUPS job_title_code_l,
1566 AR_LOOKUPS reference_use_l,
1567 (select 0 a from dual union all select 1 a from dual) tc
1568 WHERE
1569 ci.rowid = cs.int_row_id
1570 AND cs.contact_id = oc.org_contact_id
1571 AND oc.party_relationship_id = r.relationship_id
1572 AND r.directional_flag = ''F''
1573 AND cs.batch_id = :P_BATCH_ID
1574 AND cs.sub_orig_system = :P_WU_OS
1575 AND cs.sub_orig_system_reference between :P_FROM_OSR and :P_TO_OSR
1576 AND cs.ACTION_FLAG = ''U''
1577 AND cs.batch_mode_flag = :P_BATCH_MODE_FLAG
1578 AND dept_l.lookup_type(+) = ''DEPARTMENT_TYPE''
1579 AND ci.department_code = dept_l.lookup_code(+)
1580 AND title_l.lookup_type(+) = ''CONTACT_TITLE''
1581 AND ci.title = title_l.lookup_code(+)
1582 AND decision_l.lookup_type(+)=''YES/NO''
1583 AND ci.decision_maker_flag = decision_l.lookup_code(+)
1584 AND job_title_code_l.lookup_type(+) = ''RESPONSIBILITY''
1585 AND ci.job_title_code = job_title_code_l.lookup_code(+)
1586 and reference_use_l.lookup_type(+)=''YES/NO''
1587 and ci.reference_use_flag = reference_use_l.lookup_code(+)
1588 and tc.a = (select count(*) ---- check relationship duplicate, 0 indicates no error
1589 from hz_relationships r1
1590 where r1.subject_id = cs.sub_id
1591 and r1.subject_table_name = ''HZ_PARTIES''
1592 and r1.object_id = cs.obj_id
1593 and r1.relationship_id <> oc.party_relationship_id
1594 and r1.relationship_type = ci.relationship_type
1595 and r1.relationship_code = ci.relationship_code
1596 and nvl(ci.end_date, :l_no_end_date) >= r1.start_date
1597 and nvl(r1.end_date, :l_no_end_date) >= ci.start_date
1598 and r1.actual_content_source= :l_content_source_type
1599 and r1.status = ''A''
1600 and rownum = 1)
1601
1602 ';
1603
1604 l_where_first_run_sql varchar2(40) := ' AND ci.interface_status is null';
1605 l_where_rerun_sql varchar2(40) := ' AND ci.interface_status = ''C''';
1606
1607 l_dml_exception varchar2(1) := 'N';
1608
1609 BEGIN
1610
1611 savepoint process_update_contacts_pvt;
1612
1613 FND_FILE.PUT_LINE(FND_FILE.LOG, 'process_update_org_contacts (+)');
1614
1615 FND_MSG_PUB.initialize;
1616
1617 --Initialize API return status to success.
1618 x_return_status := FND_API.G_RET_STS_SUCCESS;
1619
1620 IF P_DML_RECORD.RERUN = 'N' THEN
1621
1622 -- First Run
1623 OPEN c_handle_update FOR l_update_sql || l_where_first_run_sql
1624 USING P_DML_RECORD.GMISS_DATE, P_DML_RECORD.GMISS_DATE,
1625 -- P_DML_RECORD.GMISS_DATE, l_no_end_date, l_no_end_date, l_no_end_date,
1626 P_DML_RECORD.batch_id, P_DML_RECORD.OS, P_DML_RECORD.FROM_OSR,
1627 P_DML_RECORD.TO_OSR, P_DML_RECORD.batch_mode_flag,
1628 l_no_end_date, l_no_end_date, p_dml_record.actual_content_src;
1629
1630 ELSE
1631 -- Rerun
1632 OPEN c_handle_update FOR l_update_sql || l_where_rerun_sql
1633 USING P_DML_RECORD.GMISS_DATE, P_DML_RECORD.GMISS_DATE,
1634 -- P_DML_RECORD.GMISS_DATE, l_no_end_date, l_no_end_date, l_no_end_date,
1635 P_DML_RECORD.batch_id, P_DML_RECORD.OS, P_DML_RECORD.FROM_OSR,
1636 P_DML_RECORD.TO_OSR, P_DML_RECORD.batch_mode_flag,
1637 l_no_end_date, l_no_end_date, p_dml_record.actual_content_src;
1638
1639 END IF;
1640
1641
1642 FETCH c_handle_update BULK COLLECT INTO
1643
1644 l_row_id,
1645
1646 -- org contact columns
1647 l_org_contact_id,
1648 l_contact_number,
1649 l_department_code,
1650 l_department,
1651 l_title,
1652 l_job_title,
1653 l_job_title_code,
1654 l_decision_maker_flag,
1655 l_reference_use_flag,
1656 l_comments,
1657
1658 -- relationship columns
1659 l_relationship_id,
1660 l_start_date,
1661 l_end_date,
1662 l_rel_comments,
1663
1664 -- org contact attribute columns
1665 l_attribute_category,
1666 l_attribute1,
1667 l_attribute2,
1668 l_attribute3,
1669 l_attribute4,
1670 l_attribute5,
1671 l_attribute6,
1672 l_attribute7,
1673 l_attribute8,
1674 l_attribute9,
1675 l_attribute10,
1676 l_attribute11,
1677 l_attribute12,
1678 l_attribute13,
1679 l_attribute14,
1680 l_attribute15,
1681 l_attribute16,
1682 l_attribute17,
1683 l_attribute18,
1684 l_attribute19,
1685 l_attribute20,
1686
1687 -- error flags
1688 l_error_flag,
1689 l_action_mismatch_errors,
1690 l_flex_val_errors,
1691 l_dss_security_errors,
1692 l_department_code_errors,
1693 l_title_errors,
1694 l_decision_maker_flag_errors,
1695 l_job_title_code_errors,
1696 l_reference_use_flag_errors,
1697 l_start_date_errors,
1698 l_start_end_date_errors,
1699 l_dup_rel_errors;
1700
1701 -- FND_FILE.PUT_LINE(FND_FILE.LOG, 'org_contact_id count = ' || l_org_contact_id.count);
1702
1703 /*** Do FND desc flex validation based on profile ***/
1704 IF P_DML_RECORD.FLEX_VALIDATION = 'Y' THEN
1705 validate_desc_flexfield(P_DML_RECORD.SYSDATE);
1706 END IF;
1707
1708
1709 /*** Do DSS security validation based on profile ***/
1710 IF P_DML_RECORD.DSS_SECURITY = 'Y' THEN
1711 validate_DSS_security;
1712 END IF;
1713
1714 /*************************************************/
1715 /* Update HZ_RELATIONSHIPS (Both directions) */
1716 /*************************************************/
1717
1718 -- FND_FILE.PUT_LINE(FND_FILE.LOG, 'update hz_party_relationships ');
1719
1720 BEGIN
1721 ForAll j in 1..l_relationship_id.count SAVE EXCEPTIONS
1722 update hz_relationships set
1723 START_DATE = /* No need to check G_MISS here as it is caught by l_start_date_errors */
1724 nvl(l_start_date(j), start_date),
1725 END_DATE =
1726 DECODE(l_end_date(j),
1727 NULL, end_date,
1728 P_DML_RECORD.GMISS_DATE, NULL,
1729 l_end_date(j)),
1730
1731 LAST_UPDATED_BY = P_DML_RECORD.USER_ID,
1732 LAST_UPDATE_DATE = trunc(P_DML_RECORD.SYSDATE),
1733 LAST_UPDATE_LOGIN = P_DML_RECORD.LAST_UPDATE_LOGIN,
1734 REQUEST_ID = P_DML_RECORD.REQUEST_ID,
1735 PROGRAM_APPLICATION_ID = P_DML_RECORD.PROGRAM_APPLICATION_ID,
1736 PROGRAM_ID = P_DML_RECORD.PROGRAM_ID,
1737 PROGRAM_UPDATE_DATE = P_DML_RECORD.SYSDATE,
1738 COMMENTS =
1739 DECODE(l_rel_comments(j),
1740 NULL, comments,
1741 P_DML_RECORD.GMISS_CHAR, NULL,
1742 l_rel_comments(j)),
1743 OBJECT_VERSION_NUMBER =
1744 DECODE(OBJECT_VERSION_NUMBER,
1745 NULL, 1,
1746 OBJECT_VERSION_NUMBER+1)
1747 where
1748 relationship_id = l_relationship_id(j)
1749 and l_start_date_errors(j) is not null
1750 and l_start_end_date_errors(j) is not null
1751 and l_error_flag(j) is null
1752 and l_action_mismatch_errors(j) is not null
1753 and l_dup_rel_errors(j) is not null
1754 and l_flex_val_errors(j) = 0
1755 and l_dss_security_errors(j) = 'T'
1756 and l_department_code_errors(j) is not null
1757 and l_title_errors(j) is not null
1758 and l_decision_maker_flag_errors(j) is not null
1759 and l_job_title_code_errors(j) is not null
1760 and l_reference_use_flag_errors(j) is not null;
1761 /* Bug 7416351
1762 and actual_content_source= P_DML_RECORD.ACTUAL_CONTENT_SRC
1763 and actual_content_source = P_DML_RECORD.ACTUAL_CONTENT_SRC;
1764 */
1765 EXCEPTION
1766 WHEN OTHERS THEN
1767 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Other exceptions when updating hz_relationships');
1768 l_dml_exception := 'Y';
1769
1770 FOR k IN 1..l_relationship_id.count LOOP
1771 FND_FILE.PUT_LINE(FND_FILE.LOG, 'dml_errors BULK_ROWCOUNT = ' || SQL%BULK_ROWCOUNT(k));
1772 END LOOP;
1773 END;
1774
1775 -- FND_FILE.PUT_LINE(FND_FILE.LOG, '3 ' || dbms_utility.get_time);
1776
1777 report_errors(P_DML_RECORD, 'U', l_dml_exception);
1778
1779
1780 /*************************************************/
1781 /* Update HZ_ORG_CONTACTS */
1782 /*************************************************/
1783
1784 -- FND_FILE.PUT_LINE(FND_FILE.LOG, 'update hz_org_contacts ');
1785
1786 BEGIN
1787 ForAll j in 1..l_org_contact_id.count
1788 update hz_org_contacts set
1789 CONTACT_NUMBER =
1790 DECODE(l_contact_number(j),
1791 NULL, CONTACT_NUMBER,
1792 P_DML_RECORD.GMISS_CHAR, NULL,
1793 l_contact_number(j)),
1794 DEPARTMENT_CODE =
1795 DECODE(l_department_code(j),
1796 NULL, DEPARTMENT_CODE,
1797 P_DML_RECORD.GMISS_CHAR, NULL,
1798 l_department_code(j)),
1799 DEPARTMENT =
1800 DECODE(l_department(j),
1801 NULL, DEPARTMENT,
1802 P_DML_RECORD.GMISS_CHAR, NULL,
1803 l_department(j)),
1804 TITLE =
1805 DECODE(l_title(j),
1806 NULL, TITLE,
1807 P_DML_RECORD.GMISS_CHAR, NULL,
1808 l_title(j)),
1809 JOB_TITLE =
1810 DECODE(l_job_title(j),
1811 NULL, JOB_TITLE,
1812 P_DML_RECORD.GMISS_CHAR, NULL,
1813 l_job_title(j)),
1814 JOB_TITLE_CODE =
1815 DECODE(l_job_title_code(j),
1816 NULL, JOB_TITLE_CODE,
1817 P_DML_RECORD.GMISS_CHAR, NULL,
1818 l_job_title_code(j)),
1819 DECISION_MAKER_FLAG =
1820 DECODE(l_decision_maker_flag(j),
1821 NULL, DECISION_MAKER_FLAG,
1822 P_DML_RECORD.GMISS_CHAR, NULL,
1823 l_decision_maker_flag(j)),
1824 REFERENCE_USE_FLAG =
1825 DECODE(l_reference_use_flag(j),
1826 NULL, REFERENCE_USE_FLAG,
1827 P_DML_RECORD.GMISS_CHAR, NULL,
1828 l_reference_use_flag(j)),
1829 COMMENTS =
1830 DECODE(l_comments(j),
1831 NULL, comments,
1832 P_DML_RECORD.GMISS_CHAR, NULL,
1833 l_comments(j)),
1834 ATTRIBUTE_CATEGORY =
1835 DECODE(l_attribute_category(j),
1836 NULL, attribute_category,
1837 P_DML_RECORD.GMISS_CHAR, NULL,
1838 l_attribute_category(j)),
1839 ATTRIBUTE1 =
1840 DECODE(l_attribute1(j),
1841 NULL, attribute1,
1842 P_DML_RECORD.GMISS_CHAR, NULL,
1843 l_attribute1(j)),
1844 ATTRIBUTE2 =
1845 DECODE(l_attribute2(j),
1846 NULL, attribute2,
1847 P_DML_RECORD.GMISS_CHAR, NULL,
1848 l_attribute2(j)),
1849 ATTRIBUTE3 =
1850 DECODE(l_attribute3(j),
1851 NULL, attribute3,
1852 P_DML_RECORD.GMISS_CHAR, NULL,
1853 l_attribute3(j)),
1854 ATTRIBUTE4 =
1855 DECODE(l_attribute4(j),
1856 NULL, attribute4,
1857 P_DML_RECORD.GMISS_CHAR, NULL,
1858 l_attribute4(j)),
1859 ATTRIBUTE5 =
1860 DECODE(l_attribute5(j),
1861 NULL, attribute5,
1862 P_DML_RECORD.GMISS_CHAR, NULL,
1863 l_attribute5(j)),
1864 ATTRIBUTE6 =
1865 DECODE(l_attribute6(j),
1866 NULL, attribute6,
1867 P_DML_RECORD.GMISS_CHAR, NULL,
1868 l_attribute6(j)),
1869 ATTRIBUTE7 =
1870 DECODE(l_attribute7(j),
1871 NULL, attribute7,
1872 P_DML_RECORD.GMISS_CHAR, NULL,
1873 l_attribute7(j)),
1874 ATTRIBUTE8 =
1875 DECODE(l_attribute8(j),
1876 NULL, attribute8,
1877 P_DML_RECORD.GMISS_CHAR, NULL,
1878 l_attribute8(j)),
1879 ATTRIBUTE9 =
1880 DECODE(l_attribute9(j),
1881 NULL, attribute9,
1882 P_DML_RECORD.GMISS_CHAR, NULL,
1883 l_attribute9(j)),
1884 ATTRIBUTE10 =
1885 DECODE(l_attribute10(j),
1886 NULL, attribute10,
1887 P_DML_RECORD.GMISS_CHAR, NULL,
1888 l_attribute10(j)),
1889 ATTRIBUTE11 =
1890 DECODE(l_attribute11(j),
1891 NULL, attribute11,
1892 P_DML_RECORD.GMISS_CHAR, NULL,
1893 l_attribute11(j)),
1894 ATTRIBUTE12 =
1895 DECODE(l_attribute12(j),
1896 NULL, attribute12,
1897 P_DML_RECORD.GMISS_CHAR, NULL,
1898 l_attribute12(j)),
1899 ATTRIBUTE13 =
1900 DECODE(l_attribute13(j),
1901 NULL, attribute13,
1902 P_DML_RECORD.GMISS_CHAR, NULL,
1903 l_attribute13(j)),
1904 ATTRIBUTE14 =
1905 DECODE(l_attribute14(j),
1906 NULL, attribute14,
1907 P_DML_RECORD.GMISS_CHAR, NULL,
1908 l_attribute14(j)),
1909 ATTRIBUTE15 =
1910 DECODE(l_attribute15(j),
1911 NULL, attribute15,
1912 P_DML_RECORD.GMISS_CHAR, NULL,
1913 l_attribute15(j)),
1914 ATTRIBUTE16 =
1915 DECODE(l_attribute16(j),
1916 NULL, attribute16,
1917 P_DML_RECORD.GMISS_CHAR, NULL,
1918 l_attribute16(j)),
1919 ATTRIBUTE17 =
1920 DECODE(l_attribute17(j),
1921 NULL, attribute17,
1922 P_DML_RECORD.GMISS_CHAR, NULL,
1923 l_attribute17(j)),
1924 ATTRIBUTE18 =
1925 DECODE(l_attribute18(j),
1926 NULL, attribute18,
1927 P_DML_RECORD.GMISS_CHAR, NULL,
1928 l_attribute18(j)),
1929 ATTRIBUTE19 =
1930 DECODE(l_attribute19(j),
1931 NULL, attribute19,
1932 P_DML_RECORD.GMISS_CHAR, NULL,
1933 l_attribute19(j)),
1934 ATTRIBUTE20 =
1935 DECODE(l_attribute20(j),
1936 NULL, attribute20,
1937 P_DML_RECORD.GMISS_CHAR, NULL,
1938 l_attribute20(j)),
1939 LAST_UPDATED_BY = P_DML_RECORD.USER_ID,
1940 LAST_UPDATE_DATE = trunc(P_DML_RECORD.SYSDATE),
1941 LAST_UPDATE_LOGIN = P_DML_RECORD.LAST_UPDATE_LOGIN,
1942 REQUEST_ID = P_DML_RECORD.REQUEST_ID,
1943 PROGRAM_APPLICATION_ID = P_DML_RECORD.PROGRAM_APPLICATION_ID,
1944 PROGRAM_ID = P_DML_RECORD.PROGRAM_ID,
1945 PROGRAM_UPDATE_DATE = P_DML_RECORD.SYSDATE
1946
1947 where
1948 org_contact_id = l_org_contact_id(j)
1949 and l_start_date_errors(j) is not null
1950 and l_start_end_date_errors(j) is not null
1951 and l_error_flag(j) is null
1952 and l_action_mismatch_errors(j) is not null
1953 and l_dup_rel_errors(j) is not null
1954 and l_flex_val_errors(j) = 0
1955 and l_dss_security_errors(j) = 'T'
1956 and l_department_code_errors(j) is not null
1957 and l_title_errors(j) is not null
1958 and l_decision_maker_flag_errors(j) is not null
1959 and l_job_title_code_errors(j) is not null
1960 and l_reference_use_flag_errors(j) is not null
1961 -- only update those rows which sucessfully updated in hz_relationships
1962 and l_num_row_processed(j) = 1;
1963
1964 EXCEPTION
1965 WHEN OTHERS THEN
1966 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Other exceptions when updating hz_org_contacts');
1967 ROLLBACK to process_update_contacts_pvt;
1968
1969 populate_error_table(P_DML_RECORD, 'N', SQLERRM);
1970 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1971
1972 FND_MESSAGE.SET_NAME('AR', 'HZ_API_OTHERS_EXCEP');
1973 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
1974 FND_MSG_PUB.ADD;
1975
1976 END;
1977
1978 CLOSE c_handle_update;
1979
1980 FND_FILE.PUT_LINE(FND_FILE.LOG, 'process_update_org_contacts (-)');
1981
1982 END process_update_org_contacts;
1983
1984 /********************************************************************************
1985 *
1986 * PROCEDURE load_org_contacts
1987 *
1988 * DESCRIPTION
1989 *
1990 *
1991 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
1992 *
1993 * ARGUMENTS
1994 * IN:
1995 * P_DML_RECORD IN HZ_IMP_LOAD_WRAPPER.DML_RECORD_TYPE
1996 *
1997 * OUT
1998 * x_return_status OUT NOCOPY VARCHAR2
1999 * x_msg_count OUT NOCOPY NUMBER
2000 * x_msg_data OUT NOCOPY VARCHAR2
2001 * NOTES
2002 *
2003 * MODIFICATION HISTORY
2004 *
2005 * 07-10-03 Kate Shan o Created
2006 *
2007 ********************************************************************************/
2008
2009 PROCEDURE load_org_contacts (
2010 P_DML_RECORD IN HZ_IMP_LOAD_WRAPPER.DML_RECORD_TYPE
2011 ,x_return_status OUT NOCOPY VARCHAR2
2012 ,x_msg_count OUT NOCOPY NUMBER
2013 ,x_msg_data OUT NOCOPY VARCHAR2
2014 ) IS
2015
2016 l_return_status VARCHAR2(30);
2017 l_msg_data VARCHAR2(2000);
2018 l_msg_count NUMBER;
2019
2020 BEGIN
2021
2022 savepoint load_org_contacts_pvt;
2023 FND_MSG_PUB.initialize;
2024 FND_FILE.PUT_LINE(FND_FILE.LOG, 'load_org_contacts (+)');
2025
2026 --Initialize API return status to success.
2027 x_return_status := FND_API.G_RET_STS_SUCCESS;
2028
2029 l_no_end_date := TO_DATE('31-12-4712 00:00:01', 'DD-MM-YYYY HH24:MI:SS');
2030
2031 process_insert_org_contacts(
2032 P_DML_RECORD => P_DML_RECORD
2033 ,x_return_status => x_return_status
2034 ,x_msg_count => x_msg_count
2035 ,x_msg_data => x_msg_data
2036 );
2037
2038 IF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2039 return;
2040 END IF;
2041
2042 process_update_org_contacts(
2043 P_DML_RECORD => P_DML_RECORD
2044 ,x_return_status => x_return_status
2045 ,x_msg_count => x_msg_count
2046 ,x_msg_data => x_msg_data
2047 );
2048
2049
2050 FND_FILE.PUT_LINE(FND_FILE.LOG, 'load_org_contacts (-)');
2051
2052 END load_org_contacts;
2053 END HZ_IMP_LOAD_ORG_CONTACT_PKG;