[Home] [Help]
PACKAGE BODY: APPS.HZ_MERGE_DUP_CHECK
Source
1 PACKAGE BODY HZ_MERGE_DUP_CHECK AS
2 /*$Header: ARHMDUPB.pls 120.15.12010000.2 2008/09/08 07:06:18 kguggila ship $ */
3
4 FUNCTION check_cust_account_dup(
5 p_from_id IN NUMBER:=FND_API.G_MISS_NUM,
6 x_to_id IN OUT NOCOPY NUMBER,
7 p_from_fk_id IN NUMBER:=FND_API.G_MISS_NUM,
8 p_to_fk_id IN NUMBER:=FND_API.G_MISS_NUM,
9 x_return_status IN OUT NOCOPY VARCHAR2)
10 RETURN VARCHAR2 IS
11
12 BEGIN
13 x_to_id := FND_API.G_MISS_NUM;
14 RETURN FND_API.G_FALSE;
15 END check_cust_account_dup;
16
17 FUNCTION check_cust_account_role_dup(
18 p_from_id IN NUMBER:=FND_API.G_MISS_NUM,
19 x_to_id IN OUT NOCOPY NUMBER,
20 p_from_fk_id IN NUMBER:=FND_API.G_MISS_NUM,
21 p_to_fk_id IN NUMBER:=FND_API.G_MISS_NUM,
22 x_return_status IN OUT NOCOPY VARCHAR2)
23 RETURN VARCHAR2 IS
24
25 BEGIN
26 x_to_id := FND_API.G_MISS_NUM;
27 RETURN FND_API.G_FALSE;
28 END check_cust_account_role_dup;
29
30 FUNCTION check_cust_account_site_dup(
31 p_from_id IN NUMBER:=FND_API.G_MISS_NUM,
32 x_to_id IN OUT NOCOPY NUMBER,
33 p_from_fk_id IN NUMBER:=FND_API.G_MISS_NUM,
34 p_to_fk_id IN NUMBER:=FND_API.G_MISS_NUM,
35 x_return_status IN OUT NOCOPY VARCHAR2)
36 RETURN VARCHAR2 IS
37
38 BEGIN
39 x_to_id := FND_API.G_MISS_NUM;
40 RETURN FND_API.G_FALSE;
41 END check_cust_account_site_dup;
42
43 FUNCTION check_financial_profile_dup(
44 p_from_id IN NUMBER:=FND_API.G_MISS_NUM,
45 x_to_id IN OUT NOCOPY NUMBER,
46 p_from_fk_id IN NUMBER:=FND_API.G_MISS_NUM,
47 p_to_fk_id IN NUMBER:=FND_API.G_MISS_NUM,
48 x_return_status IN OUT NOCOPY VARCHAR2)
49 RETURN VARCHAR2 IS
50
51 CURSOR dupcheck IS
52 SELECT
53 FINANCIAL_PROFILE_ID
54 FROM HZ_FINANCIAL_PROFILE
55 WHERE party_id = p_to_fk_id
56 AND (status IS NULL OR status = 'A')
57 AND TO_CHAR(ACCESS_AUTHORITY_DATE, 'DD/MM/YYYY') ||
58 ACCESS_AUTHORITY_GRANTED ||
59 TO_CHAR(BALANCE_AMOUNT) ||
60 TO_CHAR(BALANCE_VERIFIED_ON_DATE, 'DD/MM/YYYY') ||
61 FINANCIAL_ACCOUNT_NUMBER ||
62 FINANCIAL_ACCOUNT_TYPE ||
63 FINANCIAL_ORG_TYPE ||
64 FINANCIAL_ORGANIZATION_NAME = (
65 SELECT TO_CHAR(ACCESS_AUTHORITY_DATE, 'DD/MM/YYYY') ||
66 ACCESS_AUTHORITY_GRANTED ||
67 TO_CHAR(BALANCE_AMOUNT) ||
68 TO_CHAR(BALANCE_VERIFIED_ON_DATE, 'DD/MM/YYYY') ||
69 FINANCIAL_ACCOUNT_NUMBER ||
70 FINANCIAL_ACCOUNT_TYPE ||
71 FINANCIAL_ORG_TYPE ||
72 FINANCIAL_ORGANIZATION_NAME
73 FROM HZ_FINANCIAL_PROFILE
74 WHERE financial_profile_id = p_from_id);
75
76 l_record_id NUMBER;
77 BEGIN
78 x_to_id := FND_API.G_MISS_NUM;
79
80 OPEN dupcheck;
81 FETCH dupcheck INTO l_record_id;
82 IF dupcheck%NOTFOUND THEN
83 CLOSE dupcheck;
84 RETURN FND_API.G_FALSE;
85 END IF;
86
87 x_to_id := l_record_id;
88 CLOSE dupcheck;
89 RETURN FND_API.G_TRUE;
90
91 EXCEPTION
92 WHEN OTHERS THEN
93 FND_MESSAGE.SET_NAME('AR', 'HZ_MERGE_SQL_ERROR');
94 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
95 FND_MSG_PUB.ADD;
96 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
97 END check_financial_profile_dup;
98
99 FUNCTION check_contact_point_dup(
100 p_from_id IN NUMBER:=FND_API.G_MISS_NUM,
101 x_to_id IN OUT NOCOPY NUMBER,
102 p_from_fk_id IN NUMBER:=FND_API.G_MISS_NUM,
103 p_to_fk_id IN NUMBER:=FND_API.G_MISS_NUM,
104 p_owner_table_name IN VARCHAR2,
105 x_return_status IN OUT NOCOPY VARCHAR2)
106 RETURN VARCHAR2 IS
107
108 CURSOR dupcheck (source_type VARCHAR2) IS
109 SELECT
110 CONTACT_POINT_ID
111 FROM HZ_CONTACT_POINTS
112 WHERE owner_table_name = p_owner_table_name
113 AND owner_table_id = p_to_fk_id
114 AND (status IS NULL OR status = 'A')
115 AND
116 CONTACT_POINT_TYPE ||
117 STATUS ||
118 EDI_TRANSACTION_HANDLING ||
119 EDI_ID_NUMBER ||
120 EDI_PAYMENT_METHOD ||
121 EDI_PAYMENT_FORMAT ||
122 EDI_REMITTANCE_METHOD ||
123 EDI_REMITTANCE_INSTRUCTION ||
124 EDI_TP_HEADER_ID ||
125 EDI_ECE_TP_LOCATION_CODE ||
126 EMAIL_FORMAT ||
127 TO_CHAR(BEST_TIME_TO_CONTACT_START, 'DD/MM/YYYY') ||
128 TO_CHAR(BEST_TIME_TO_CONTACT_END, 'DD/MM/YYYY') ||
129 PHONE_CALLING_CALENDAR ||
130 DECLARED_BUSINESS_PHONE_FLAG ||
131 PHONE_PREFERRED_ORDER ||
132 TELEPHONE_TYPE ||
133 TIME_ZONE ||
134 PHONE_TOUCH_TONE_TYPE_FLAG ||
135 PHONE_AREA_CODE ||
136 PHONE_COUNTRY_CODE ||
137 PHONE_NUMBER ||
138 PHONE_EXTENSION ||
139 PHONE_LINE_TYPE ||
140 TELEX_NUMBER ||
141 WEB_TYPE ||
142 DECODE (source_type,'P',actual_content_source,'S')
143 = (SELECT
144 CONTACT_POINT_TYPE ||
145 STATUS ||
146 EDI_TRANSACTION_HANDLING ||
147 EDI_ID_NUMBER ||
148 EDI_PAYMENT_METHOD ||
149 EDI_PAYMENT_FORMAT ||
150 EDI_REMITTANCE_METHOD ||
151 EDI_REMITTANCE_INSTRUCTION ||
152 EDI_TP_HEADER_ID ||
153 EDI_ECE_TP_LOCATION_CODE ||
154 EMAIL_FORMAT ||
155 TO_CHAR(BEST_TIME_TO_CONTACT_START, 'DD/MM/YYYY') ||
156 TO_CHAR(BEST_TIME_TO_CONTACT_END, 'DD/MM/YYYY') ||
157 PHONE_CALLING_CALENDAR ||
158 DECLARED_BUSINESS_PHONE_FLAG ||
159 PHONE_PREFERRED_ORDER ||
160 TELEPHONE_TYPE ||
161 TIME_ZONE ||
162 PHONE_TOUCH_TONE_TYPE_FLAG ||
163 PHONE_AREA_CODE ||
164 PHONE_COUNTRY_CODE ||
165 PHONE_NUMBER ||
166 PHONE_EXTENSION ||
167 PHONE_LINE_TYPE ||
168 TELEX_NUMBER ||
169 WEB_TYPE ||
170 DECODE (source_type,'P',actual_content_source,'S')
171 FROM HZ_CONTACT_POINTS
172 WHERE contact_point_id = p_from_id)
173 AND upper(nvl(EMAIL_ADDRESS,'NOEMAIL')) = ( --7294111 Added Upper
174 SELECT upper(nvl(EMAIL_ADDRESS,'NOEMAIL')) --7294111 Added Upper
175 FROM HZ_CONTACT_POINTS
176 WHERE contact_point_id = p_from_id)
177 AND nvl(URL, 'NOURL') = (
178 SELECT nvl(URL, 'NOURL')
179 FROM HZ_CONTACT_POINTS
180 WHERE contact_point_id = p_from_id);
181
182 CURSOR c_cont_source IS
183 SELECT ACTUAL_CONTENT_SOURCE
184 FROM HZ_CONTACT_POINTS
185 WHERE contact_point_id = p_from_id;
186
187 l_record_id NUMBER;
188 l_cont_source VARCHAR2(255);
189 l_from_last_upd_date DATE;
190 l_to_last_upd_date DATE;
191 l_temp NUMBER;
192 l_cont_source_type VARCHAR2(255);
193 l_source_type VARCHAR2(1) :='S';
194 BEGIN
195
196 x_to_id := FND_API.G_MISS_NUM;
197
198 OPEN c_cont_source;
199 FETCH c_cont_source INTO l_cont_source;
200 CLOSE c_cont_source;
201
202 IF l_cont_source = 'DNB' THEN
203 RETURN FND_API.G_FALSE;
204 ELSE---Find Whether Purchased or Spoke
205 SELECT orig_system_type INTO l_cont_source_type
206 FROM HZ_ORIG_SYSTEMS_B WHERE orig_system = l_cont_source;
207 IF l_cont_source_type = 'PURCHASED' THEN
208 l_source_type :='P';
209 END IF;
210 END IF;
211 ----Bug 4114254. USE ACS column for dup check if the l_cont_source is PURCHASED otherwise dont use it.
212 OPEN dupcheck(l_source_type);
213 FETCH dupcheck INTO l_record_id;
214 IF dupcheck%NOTFOUND THEN
215 CLOSE dupcheck;
216 RETURN FND_API.G_FALSE;
217 END IF;
218
219 x_to_id := l_record_id;
220 CLOSE dupcheck;
221 RETURN FND_API.G_TRUE;
222
223 EXCEPTION
224 WHEN OTHERS THEN
225 FND_MESSAGE.SET_NAME('AR', 'HZ_MERGE_SQL_ERROR');
226 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
227 FND_MSG_PUB.ADD;
228 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
229 END check_contact_point_dup;
230
231 FUNCTION check_references_dup(
232 p_from_id IN NUMBER:=FND_API.G_MISS_NUM,
233 x_to_id IN OUT NOCOPY NUMBER,
234 p_from_fk_id IN NUMBER:=FND_API.G_MISS_NUM,
235 p_to_fk_id IN NUMBER:=FND_API.G_MISS_NUM,
236 x_return_status IN OUT NOCOPY VARCHAR2)
237 RETURN VARCHAR2 IS
238
239 CURSOR dupcheck IS
240 SELECT
241 REFERENCE_ID
242 FROM HZ_REFERENCES
243 WHERE referenced_party_id = p_to_fk_id
244 AND (status IS NULL OR status = 'A')
245 AND
246 TO_CHAR(COMMENTING_PARTY_ID) ||
247 EXTERNAL_ACCOUNT_NUMBER ||
248 TO_CHAR(REFERENCE_DATE, 'DD/MM/YYYY') =
249 (SELECT
250 TO_CHAR(COMMENTING_PARTY_ID) ||
251 EXTERNAL_ACCOUNT_NUMBER ||
252 TO_CHAR(REFERENCE_DATE, 'DD/MM/YYYY')
253 FROM HZ_REFERENCES
254 WHERE reference_id = p_from_id);
255
256 l_record_id NUMBER;
257 BEGIN
258
259 x_to_id := FND_API.G_MISS_NUM;
260
261 OPEN dupcheck;
262 FETCH dupcheck INTO l_record_id;
263 IF dupcheck%NOTFOUND THEN
264 CLOSE dupcheck;
265 RETURN FND_API.G_FALSE;
266 END IF;
267
268 x_to_id := l_record_id;
269 CLOSE dupcheck;
270 RETURN FND_API.G_TRUE;
271
272 EXCEPTION
273 WHEN OTHERS THEN
274 FND_MESSAGE.SET_NAME('AR', 'HZ_MERGE_SQL_ERROR');
275 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
276 FND_MSG_PUB.ADD;
277 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
278 END check_references_dup;
279
280
281 FUNCTION check_certification_dup(
282 p_from_id IN NUMBER:=FND_API.G_MISS_NUM,
283 x_to_id IN OUT NOCOPY NUMBER,
284 p_from_fk_id IN NUMBER:=FND_API.G_MISS_NUM,
285 p_to_fk_id IN NUMBER:=FND_API.G_MISS_NUM,
286 x_return_status IN OUT NOCOPY VARCHAR2)
287 RETURN VARCHAR2 IS
288
289 CURSOR dupcheck IS
290 SELECT
291 CERTIFICATION_ID
292 FROM HZ_CERTIFICATIONS
293 WHERE party_id = p_to_fk_id
294 AND (status IS NULL OR status = 'A')
295 AND
296 CERTIFICATION_NAME ||
297 CURRENT_STATUS ||
298 TO_CHAR(EXPIRES_ON_DATE, 'DD/MM/YYYY') ||
299 GRADE ||
300 ISSUED_BY_AUTHORITY ||
301 TO_CHAR(ISSUED_ON_DATE, 'DD/MM/YYYY') =
302 (SELECT
303 CERTIFICATION_NAME ||
304 CURRENT_STATUS ||
305 TO_CHAR(EXPIRES_ON_DATE, 'DD/MM/YYYY') ||
306 GRADE ||
307 ISSUED_BY_AUTHORITY ||
308 TO_CHAR(ISSUED_ON_DATE, 'DD/MM/YYYY')
309 FROM HZ_CERTIFICATIONS
310 WHERE certification_id = p_from_id);
311
312 l_record_id NUMBER;
313 BEGIN
314
315 x_to_id := FND_API.G_MISS_NUM;
316
317 OPEN dupcheck;
318 FETCH dupcheck INTO l_record_id;
319 IF dupcheck%NOTFOUND THEN
320 CLOSE dupcheck;
321 RETURN FND_API.G_FALSE;
322 END IF;
323
324 x_to_id := l_record_id;
325 CLOSE dupcheck;
326 RETURN FND_API.G_TRUE;
327
328 EXCEPTION
329 WHEN OTHERS THEN
330 FND_MESSAGE.SET_NAME('AR', 'HZ_MERGE_SQL_ERROR');
331 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
332 FND_MSG_PUB.ADD;
333 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
334 END check_certification_dup;
335
336
337 FUNCTION check_credit_ratings_dup(
338 p_from_id IN NUMBER:=FND_API.G_MISS_NUM,
339 x_to_id IN OUT NOCOPY NUMBER,
340 p_from_fk_id IN NUMBER:=FND_API.G_MISS_NUM,
341 p_to_fk_id IN NUMBER:=FND_API.G_MISS_NUM,
342 x_return_status IN OUT NOCOPY VARCHAR2)
343 RETURN VARCHAR2 IS
344
345 CURSOR dupcheck (source_type VARCHAR2) IS
346 SELECT
347 CREDIT_RATING_ID
348 FROM HZ_CREDIT_RATINGS
349 WHERE party_id = p_to_fk_id
350 AND
351 TO_CHAR(RATED_AS_OF_DATE, 'DD/MM/YYYY') ||
352 RATING_ORGANIZATION ||
353 DECODE (source_type,'P',actual_content_source,'S') =
354 (SELECT
355 TO_CHAR(RATED_AS_OF_DATE, 'DD/MM/YYYY') ||
356 RATING_ORGANIZATION ||
357 DECODE (source_type,'P',actual_content_source,'S') FROM HZ_CREDIT_RATINGS
358 WHERE credit_rating_id = p_from_id);
359
360 CURSOR c_cont_source IS
361 SELECT ACTUAL_CONTENT_SOURCE
362 FROM HZ_CREDIT_RATINGS
363 WHERE credit_rating_id = p_from_id;
364
365 l_cont_source VARCHAR2(255);
366 l_record_id NUMBER;
367 l_from_last_upd_date DATE;
368 l_to_last_upd_date DATE;
369 l_temp NUMBER;
370 l_cont_source_type VARCHAR2(255);
371 l_source_type VARCHAR2(1) :='S';
372 BEGIN
373
374 x_to_id := FND_API.G_MISS_NUM;
375
376 OPEN c_cont_source;
377 FETCH c_cont_source INTO l_cont_source;
378 CLOSE c_cont_source;
379
380 IF l_cont_source = 'DNB' THEN
381 RETURN FND_API.G_FALSE;
382 ELSE---Find Whether Purchased or Spoke
383 SELECT orig_system_type INTO l_cont_source_type
384 FROM HZ_ORIG_SYSTEMS_B WHERE orig_system = l_cont_source;
385 IF l_cont_source_type = 'PURCHASED' THEN
386 l_source_type :='P';
387 END IF;
388 END IF;
389 ----Bug 4114254. USE ACS column for dup check if the l_cont_source is PURCHASED otherwise dont use it.
390 OPEN dupcheck(l_source_type);
391 FETCH dupcheck INTO l_record_id;
392 IF dupcheck%NOTFOUND THEN
393 CLOSE dupcheck;
394 RETURN FND_API.G_FALSE;
395 END IF;
396
397 x_to_id := l_record_id;
398 CLOSE dupcheck;
399
400 RETURN FND_API.G_TRUE;
401 EXCEPTION
402 WHEN OTHERS THEN
403 FND_MESSAGE.SET_NAME('AR', 'HZ_MERGE_SQL_ERROR');
404 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
405 FND_MSG_PUB.ADD;
406 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
407 END check_credit_ratings_dup;
408
409
410 FUNCTION check_security_issued_dup(
411 p_from_id IN NUMBER:=FND_API.G_MISS_NUM,
412 x_to_id IN OUT NOCOPY NUMBER,
413 p_from_fk_id IN NUMBER:=FND_API.G_MISS_NUM,
414 p_to_fk_id IN NUMBER:=FND_API.G_MISS_NUM,
415 x_return_status IN OUT NOCOPY VARCHAR2)
416 RETURN VARCHAR2 IS
417
418 CURSOR dupcheck IS
419 SELECT
420 SECURITY_ISSUED_ID
421 FROM HZ_SECURITY_ISSUED
422 WHERE party_id = p_to_fk_id
423 AND (status IS NULL OR status = 'A')
424 AND
425 TO_CHAR(ESTIMATED_TOTAL_AMOUNT) ||
426 TO_CHAR(STOCK_EXCHANGE_ID) ||
427 SECURITY_ISSUED_CLASS ||
428 SECURITY_ISSUED_NAME ||
429 TOTAL_AMOUNT_IN_A_CURRENCY ||
430 STOCK_TICKER_SYMBOL ||
431 SECURITY_CURRENCY_CODE ||
432 TO_CHAR(BEGIN_DATE,'DD/MM/YYYY') ||
433 TO_CHAR(END_DATE,'DD/MM/YYYY') =
434 (SELECT
435 TO_CHAR(ESTIMATED_TOTAL_AMOUNT) ||
436 TO_CHAR(STOCK_EXCHANGE_ID) ||
437 SECURITY_ISSUED_CLASS ||
438 SECURITY_ISSUED_NAME ||
439 TOTAL_AMOUNT_IN_A_CURRENCY ||
440 STOCK_TICKER_SYMBOL ||
441 SECURITY_CURRENCY_CODE ||
442 TO_CHAR(BEGIN_DATE,'DD/MM/YYYY') ||
443 TO_CHAR(END_DATE,'DD/MM/YYYY')
444 FROM HZ_SECURITY_ISSUED
445 WHERE security_issued_id = p_from_id);
446
447 l_record_id NUMBER;
448 BEGIN
449
450 x_to_id := FND_API.G_MISS_NUM;
451
452 OPEN dupcheck;
453 FETCH dupcheck INTO l_record_id;
454 IF dupcheck%NOTFOUND THEN
455 CLOSE dupcheck;
456 RETURN FND_API.G_FALSE;
457 END IF;
458
459 x_to_id := l_record_id;
463 EXCEPTION
460 CLOSE dupcheck;
461 RETURN FND_API.G_TRUE;
462
464 WHEN OTHERS THEN
465 FND_MESSAGE.SET_NAME('AR', 'HZ_MERGE_SQL_ERROR');
466 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
467 FND_MSG_PUB.ADD;
468 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
469 END check_security_issued_dup;
470
471 FUNCTION check_financial_reports_dup(
472 p_from_id IN NUMBER:=FND_API.G_MISS_NUM,
473 x_to_id IN OUT NOCOPY NUMBER,
474 p_from_fk_id IN NUMBER:=FND_API.G_MISS_NUM,
475 p_to_fk_id IN NUMBER:=FND_API.G_MISS_NUM,
476 x_return_status IN OUT NOCOPY VARCHAR2)
477 RETURN VARCHAR2 IS
478
479 CURSOR dupcheck (source_type VARCHAR2) IS
480 SELECT
481 FINANCIAL_REPORT_ID
482 FROM HZ_FINANCIAL_REPORTS
483 WHERE party_id = p_to_fk_id
484 AND
485 TO_CHAR(DATE_REPORT_ISSUED, 'DD/MM/YYYY') ||
486 DOCUMENT_REFERENCE ||
487 ISSUED_PERIOD ||
488 TYPE_OF_FINANCIAL_REPORT ||
489 TO_CHAR(REPORT_START_DATE, 'DD/MM/YYYY') ||
490 TO_CHAR(REPORT_END_DATE, 'DD/MM/YYYY') ||
491 DECODE (source_type,'P',actual_content_source,'S') =
492 (SELECT
493 TO_CHAR(DATE_REPORT_ISSUED, 'DD/MM/YYYY') ||
494 DOCUMENT_REFERENCE ||
495 ISSUED_PERIOD ||
496 TYPE_OF_FINANCIAL_REPORT ||
497 TO_CHAR(REPORT_START_DATE, 'DD/MM/YYYY') ||
498 TO_CHAR(REPORT_END_DATE, 'DD/MM/YYYY') ||
499 DECODE (source_type,'P',actual_content_source,'S')
500 FROM HZ_FINANCIAL_REPORTS
501 WHERE financial_report_id = p_from_id);
502
503 CURSOR c_cont_source IS
504 SELECT ACTUAL_CONTENT_SOURCE
505 FROM HZ_FINANCIAL_REPORTS
506 WHERE financial_report_id = p_from_id;
507
508 CURSOR dnb_dup_check IS --5396227
509 SELECT financial_report_id
510 FROM HZ_FINANCIAL_REPORTS
511 WHERE party_id = p_to_fk_id
512 AND actual_content_source = 'DNB'
513 AND nvl(status, 'A') = 'A'
514 AND type_of_financial_report= (SELECT type_of_financial_report
515 FROM HZ_FINANCIAL_REPORTS
516 WHERE financial_report_id = p_from_id);
517
518 l_cont_source VARCHAR2(255);
519 l_cont_source_type VARCHAR2(255);
520 l_record_id NUMBER;
521 l_from_last_upd_date DATE;
522 l_to_last_upd_date DATE;
523 l_temp NUMBER;
524 l_temp1 NUMBER;
525
526 l_source_type VARCHAR2(1) :='S';
527
528 BEGIN
529
530 x_to_id := FND_API.G_MISS_NUM;
531
532 OPEN c_cont_source;
533 FETCH c_cont_source INTO l_cont_source;
534 CLOSE c_cont_source;
535
536 IF l_cont_source = 'DNB' THEN
537 -- 5396227
538 OPEN dnb_dup_check;
539 FETCH dnb_dup_check INTO l_record_id;
540 IF dnb_dup_check%NOTFOUND THEN
541 CLOSE dnb_dup_check;
542 RETURN FND_API.G_FALSE;
543 END IF;
544 x_to_id := l_record_id;
545 CLOSE dnb_dup_check;
546 RETURN FND_API.G_TRUE;
547
548 ELSE---Find Whether Purchased or Spoke
549 SELECT orig_system_type INTO l_cont_source_type
550 FROM HZ_ORIG_SYSTEMS_B WHERE orig_system = l_cont_source;
551 IF l_cont_source_type = 'PURCHASED' THEN
552 l_source_type :='P';
553 END IF;
554 END IF;
555
556 ----Bug 4114254. USE ACS column for dup check if the l_cont_source is PURCHASED otherwise dont use it.
557
558 OPEN dupcheck(l_source_type);
559 FETCH dupcheck INTO l_record_id;
560 IF dupcheck%NOTFOUND THEN
561 CLOSE dupcheck;
562 RETURN FND_API.G_FALSE;
563 END IF;
564
565 x_to_id := l_record_id;
566 CLOSE dupcheck;
567
568 RETURN FND_API.G_TRUE;
569
570 EXCEPTION
571 WHEN OTHERS THEN
572 FND_MESSAGE.SET_NAME('AR', 'HZ_MERGE_SQL_ERROR');
573 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
574 FND_MSG_PUB.ADD;
575 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
576 END check_financial_reports_dup;
577
578
579 FUNCTION check_org_indicators_dup(
580 p_from_id IN NUMBER:=FND_API.G_MISS_NUM,
581 x_to_id IN OUT NOCOPY NUMBER,
582 p_from_fk_id IN NUMBER:=FND_API.G_MISS_NUM,
583 p_to_fk_id IN NUMBER:=FND_API.G_MISS_NUM,
584 x_return_status IN OUT NOCOPY VARCHAR2)
585 RETURN VARCHAR2 IS
586
587 CURSOR dupcheck IS
588 SELECT
589 ORGANIZATION_INDICATOR_ID
590 FROM HZ_ORGANIZATION_INDICATORS
591 WHERE party_id = p_to_fk_id
592 AND (status IS NULL OR status = 'A')
593 AND
594 INDICATOR ||
595 TO_CHAR(START_DATE, 'DD/MM/YYYY') ||
596 TO_CHAR(END_DATE, 'DD/MM/YYYY') =
597 (SELECT
598 INDICATOR ||
599 TO_CHAR(START_DATE, 'DD/MM/YYYY') ||
600 TO_CHAR(END_DATE, 'DD/MM/YYYY')
601 FROM HZ_ORGANIZATION_INDICATORS
602 WHERE organization_indicator_id = p_from_id)
603 AND
604 nvl(DESCRIPTION , 'NODESC') =
605 (SELECT nvl(DESCRIPTION, 'NODESC')
606 FROM HZ_ORGANIZATION_INDICATORS
607 WHERE organization_indicator_id = p_from_id);
608
609 l_record_id NUMBER;
610 BEGIN
611
612 x_to_id := FND_API.G_MISS_NUM;
613
614 OPEN dupcheck;
615 FETCH dupcheck INTO l_record_id;
616 IF dupcheck%NOTFOUND THEN
617 CLOSE dupcheck;
618 RETURN FND_API.G_FALSE;
619 END IF;
620
621 x_to_id := l_record_id;
622 CLOSE dupcheck;
623 RETURN FND_API.G_TRUE;
624
625 EXCEPTION
626 WHEN OTHERS THEN
627 FND_MESSAGE.SET_NAME('AR', 'HZ_MERGE_SQL_ERROR');
628 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
629 FND_MSG_PUB.ADD;
633 FUNCTION check_ind_reference_dup(
630 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
631 END check_org_indicators_dup;
632
634 p_from_id IN NUMBER:=FND_API.G_MISS_NUM,
635 x_to_id IN OUT NOCOPY NUMBER,
636 p_from_fk_id IN NUMBER:=FND_API.G_MISS_NUM,
637 p_to_fk_id IN NUMBER:=FND_API.G_MISS_NUM,
638 x_return_status IN OUT NOCOPY VARCHAR2)
639 RETURN VARCHAR2 IS
640
641 CURSOR dupcheck IS
642 SELECT
643 INDUSTRY_REFERENCE_ID
644 FROM HZ_INDUSTRIAL_REFERENCE
645 WHERE party_id = p_to_fk_id
646 AND (status IS NULL OR status = 'A')
647 AND
648 INDUSTRY_REFERENCE ||
649 ISSUED_BY_AUTHORITY ||
650 NAME_OF_REFERENCE ||
651 TO_CHAR(RECOGNIZED_AS_OF_DATE,'DD/MM/YYYY') =
652 (SELECT
653 INDUSTRY_REFERENCE ||
654 ISSUED_BY_AUTHORITY ||
655 NAME_OF_REFERENCE ||
656 TO_CHAR(RECOGNIZED_AS_OF_DATE,'DD/MM/YYYY')
657 FROM HZ_INDUSTRIAL_REFERENCE
658 WHERE industry_reference_id = p_from_id);
659
660 l_record_id NUMBER;
661 BEGIN
662
663 x_to_id := FND_API.G_MISS_NUM;
664
665 OPEN dupcheck;
666 FETCH dupcheck INTO l_record_id;
667 IF dupcheck%NOTFOUND THEN
668 CLOSE dupcheck;
669 RETURN FND_API.G_FALSE;
670 END IF;
671
672 x_to_id := l_record_id;
673 CLOSE dupcheck;
674 RETURN FND_API.G_TRUE;
675
676 EXCEPTION
677 WHEN OTHERS THEN
678 FND_MESSAGE.SET_NAME('AR', 'HZ_MERGE_SQL_ERROR');
679 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
680 FND_MSG_PUB.ADD;
681 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
682 END check_ind_reference_dup;
683
684 FUNCTION check_per_interest_dup(
685 p_from_id IN NUMBER:=FND_API.G_MISS_NUM,
686 x_to_id IN OUT NOCOPY NUMBER,
687 p_from_fk_id IN NUMBER:=FND_API.G_MISS_NUM,
688 p_to_fk_id IN NUMBER:=FND_API.G_MISS_NUM,
689 x_return_status IN OUT NOCOPY VARCHAR2)
690 RETURN VARCHAR2 IS
691
692 CURSOR dupcheck IS
693 SELECT
694 PERSON_INTEREST_ID
695 FROM HZ_PERSON_INTEREST
696 WHERE party_id = p_to_fk_id
697 AND (status IS NULL OR status = 'A')
698 AND
699 INTEREST_TYPE_CODE ||
700 SUB_INTEREST_TYPE_CODE =
701 (SELECT
702 INTEREST_TYPE_CODE ||
703 SUB_INTEREST_TYPE_CODE
704 FROM HZ_PERSON_INTEREST
705 WHERE person_interest_id = p_from_id);
706
707 l_record_id NUMBER;
708 BEGIN
709
710 x_to_id := FND_API.G_MISS_NUM;
711
712 OPEN dupcheck;
713 FETCH dupcheck INTO l_record_id;
714 IF dupcheck%NOTFOUND THEN
715 CLOSE dupcheck;
716 RETURN FND_API.G_FALSE;
717 END IF;
718
719 x_to_id := l_record_id;
720 CLOSE dupcheck;
721 RETURN FND_API.G_TRUE;
722
723 EXCEPTION
724 WHEN OTHERS THEN
725 FND_MESSAGE.SET_NAME('AR', 'HZ_MERGE_SQL_ERROR');
726 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
727 FND_MSG_PUB.ADD;
728 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
729 END check_per_interest_dup;
730
731
732 FUNCTION check_citizenship_dup(
733 p_from_id IN NUMBER:=FND_API.G_MISS_NUM,
734 x_to_id IN OUT NOCOPY NUMBER,
735 p_from_fk_id IN NUMBER:=FND_API.G_MISS_NUM,
736 p_to_fk_id IN NUMBER:=FND_API.G_MISS_NUM,
737 x_return_status IN OUT NOCOPY VARCHAR2)
738 RETURN VARCHAR2 IS
739
740 CURSOR dupcheck IS
741 SELECT
742 CITIZENSHIP_ID
743 FROM HZ_CITIZENSHIP
744 WHERE party_id = p_to_fk_id
745 AND (status IS NULL OR status = 'A')
746 AND
747 BIRTH_OR_SELECTED ||
748 COUNTRY_CODE ||
749 TO_CHAR(DATE_DISOWNED, 'DD/MM/YYYY') ||
750 TO_CHAR(DATE_RECOGNIZED, 'DD/MM/YYYY') ||
751 DOCUMENT_REFERENCE ||
752 TO_CHAR(END_DATE, 'DD/MM/YYYY') ||
753 DOCUMENT_TYPE =
754 (SELECT
755 BIRTH_OR_SELECTED ||
756 COUNTRY_CODE ||
757 TO_CHAR(DATE_DISOWNED, 'DD/MM/YYYY') ||
758 TO_CHAR(DATE_RECOGNIZED, 'DD/MM/YYYY') ||
759 DOCUMENT_REFERENCE ||
760 TO_CHAR(END_DATE, 'DD/MM/YYYY') ||
761 DOCUMENT_TYPE
762 FROM HZ_CITIZENSHIP
763 WHERE citizenship_id = p_from_id);
764
765 l_record_id NUMBER;
766 BEGIN
767
768 x_to_id := FND_API.G_MISS_NUM;
769
770 OPEN dupcheck;
771 FETCH dupcheck INTO l_record_id;
772 IF dupcheck%NOTFOUND THEN
773 CLOSE dupcheck;
774 RETURN FND_API.G_FALSE;
775 END IF;
776
777 x_to_id := l_record_id;
778 CLOSE dupcheck;
779 RETURN FND_API.G_TRUE;
780
781 EXCEPTION
782 WHEN OTHERS THEN
783 FND_MESSAGE.SET_NAME('AR', 'HZ_MERGE_SQL_ERROR');
784 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
785 FND_MSG_PUB.ADD;
786 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
787 END check_citizenship_dup;
788
789
790 FUNCTION check_education_dup(
791 p_from_id IN NUMBER:=FND_API.G_MISS_NUM,
792 x_to_id IN OUT NOCOPY NUMBER,
793 p_from_fk_id IN NUMBER:=FND_API.G_MISS_NUM,
794 p_to_fk_id IN NUMBER:=FND_API.G_MISS_NUM,
795 x_return_status IN OUT NOCOPY VARCHAR2)
796 RETURN VARCHAR2 IS
797
798 CURSOR dupcheck IS
799 SELECT
800 EDUCATION_ID
801 FROM HZ_EDUCATION
802 WHERE party_id = p_to_fk_id
803 AND (status IS NULL OR status = 'A')
804 AND
805 COURSE_MAJOR ||
806 DEGREE_RECEIVED ||
807 TO_CHAR(LAST_DATE_ATTENDED, 'DD/MM/YYYY') ||
808 TO_CHAR(START_DATE_ATTENDED, 'DD/MM/YYYY') ||
809 TYPE_OF_SCHOOL ||
810 SCHOOL_ATTENDED_NAME ||
814 DEGREE_RECEIVED ||
811 SCHOOL_PARTY_ID=
812 (SELECT
813 COURSE_MAJOR ||
815 TO_CHAR(LAST_DATE_ATTENDED, 'DD/MM/YYYY') ||
816 TO_CHAR(START_DATE_ATTENDED, 'DD/MM/YYYY') ||
817 TYPE_OF_SCHOOL ||
818 SCHOOL_ATTENDED_NAME ||
819 SCHOOL_PARTY_ID
820 FROM HZ_EDUCATION
821 WHERE education_id = p_from_id);
822
823 l_record_id NUMBER;
824 BEGIN
825
826 x_to_id := FND_API.G_MISS_NUM;
827
828 OPEN dupcheck;
829 FETCH dupcheck INTO l_record_id;
830 IF dupcheck%NOTFOUND THEN
831 CLOSE dupcheck;
832 RETURN FND_API.G_FALSE;
833 END IF;
834
835 x_to_id := l_record_id;
836 CLOSE dupcheck;
837 RETURN FND_API.G_TRUE;
838
839 EXCEPTION
840 WHEN OTHERS THEN
841 FND_MESSAGE.SET_NAME('AR', 'HZ_MERGE_SQL_ERROR');
842 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
843 FND_MSG_PUB.ADD;
844 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
845 END check_education_dup;
846
847 FUNCTION check_work_class_dup(
848 p_from_id IN NUMBER:=FND_API.G_MISS_NUM,
849 x_to_id IN OUT NOCOPY NUMBER,
850 p_from_fk_id IN NUMBER:=FND_API.G_MISS_NUM,
851 p_to_fk_id IN NUMBER:=FND_API.G_MISS_NUM,
852 x_return_status IN OUT NOCOPY VARCHAR2)
853 RETURN VARCHAR2 IS
854 CURSOR dupcheck IS
855 SELECT
856 WORK_CLASS_ID
857 FROM HZ_WORK_CLASS
858 WHERE employment_history_id = p_to_fk_id
859 AND (status IS NULL OR status = 'A')
860 AND
861 WORK_CLASS_NAME =
862 (SELECT
863 WORK_CLASS_NAME
864 FROM HZ_WORK_CLASS
865 WHERE work_class_id = p_from_id);
866 l_record_id NUMBER;
867 BEGIN
868
869 x_to_id := FND_API.G_MISS_NUM;
870
871 OPEN dupcheck;
872 FETCH dupcheck INTO l_record_id;
873 IF dupcheck%NOTFOUND THEN
874 CLOSE dupcheck;
875 RETURN FND_API.G_FALSE;
876 END IF;
877
878 x_to_id := l_record_id;
879 CLOSE dupcheck;
880 RETURN FND_API.G_TRUE;
881
882 EXCEPTION
883 WHEN OTHERS THEN
884 FND_MESSAGE.SET_NAME('AR', 'HZ_MERGE_SQL_ERROR');
885 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
886 FND_MSG_PUB.ADD;
887 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
888 END check_work_class_dup;
889
890
891 FUNCTION check_emp_history_dup(
892 p_from_id IN NUMBER:=FND_API.G_MISS_NUM,
893 x_to_id IN OUT NOCOPY NUMBER,
894 p_from_fk_id IN NUMBER:=FND_API.G_MISS_NUM,
895 p_to_fk_id IN NUMBER:=FND_API.G_MISS_NUM,
896 x_return_status IN OUT NOCOPY VARCHAR2)
897 RETURN VARCHAR2 IS
898
899 CURSOR dupcheck IS
900 SELECT
901 EMPLOYMENT_HISTORY_ID
902 FROM HZ_EMPLOYMENT_HISTORY
903 WHERE party_id = p_to_fk_id
904 AND (status IS NULL OR status = 'A')
905 AND
906 TO_CHAR(BEGIN_DATE, 'DD/MM/YYYY') ||
907 EMPLOYED_AS_TITLE ||
908 EMPLOYED_BY_DIVISION_NAME ||
909 EMPLOYED_BY_NAME_COMPANY ||
910 TO_CHAR(END_DATE, 'DD/MM/YYYY') ||
911 SUPERVISOR_NAME ||
912 BRANCH ||
913 MILITARY_RANK ||
914 SERVED ||
915 STATION =
916 (SELECT
917 TO_CHAR(BEGIN_DATE, 'DD/MM/YYYY') ||
918 EMPLOYED_AS_TITLE ||
919 EMPLOYED_BY_DIVISION_NAME ||
920 EMPLOYED_BY_NAME_COMPANY ||
921 TO_CHAR(END_DATE, 'DD/MM/YYYY') ||
922 SUPERVISOR_NAME ||
923 BRANCH ||
924 MILITARY_RANK ||
925 SERVED ||
926 STATION
927 FROM HZ_EMPLOYMENT_HISTORY
928 WHERE employment_history_id = p_from_id);
929
930 l_record_id NUMBER;
931 BEGIN
932
933 x_to_id := FND_API.G_MISS_NUM;
934
935 OPEN dupcheck;
936 FETCH dupcheck INTO l_record_id;
937 IF dupcheck%NOTFOUND THEN
938 CLOSE dupcheck;
939 RETURN FND_API.G_FALSE;
940 END IF;
941
942 x_to_id := l_record_id;
943 CLOSE dupcheck;
944 RETURN FND_API.G_TRUE;
945
946 EXCEPTION
947 WHEN OTHERS THEN
948 FND_MESSAGE.SET_NAME('AR', 'HZ_MERGE_SQL_ERROR');
949 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
950 FND_MSG_PUB.ADD;
951 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
952 END check_emp_history_dup;
953
954
955 FUNCTION check_languages_dup(
956 p_from_id IN NUMBER:=FND_API.G_MISS_NUM,
957 x_to_id IN OUT NOCOPY NUMBER,
958 p_from_fk_id IN NUMBER:=FND_API.G_MISS_NUM,
959 p_to_fk_id IN NUMBER:=FND_API.G_MISS_NUM,
960 x_return_status IN OUT NOCOPY VARCHAR2)
961 RETURN VARCHAR2 IS
962
963 CURSOR dupcheck IS
964 SELECT LANGUAGE_USE_REFERENCE_ID
965 FROM HZ_PERSON_LANGUAGE
966 WHERE party_id = p_to_fk_id
967 AND (status IS NULL OR status = 'A')
968 AND
969 LANGUAGE_NAME =
970 (SELECT
971 LANGUAGE_NAME
972 FROM HZ_PERSON_LANGUAGE
973 WHERE language_use_reference_id = p_from_id);
974
975 l_record_id NUMBER;
976 BEGIN
977
978 x_to_id := FND_API.G_MISS_NUM;
979
980 OPEN dupcheck;
981 FETCH dupcheck INTO l_record_id;
982 IF dupcheck%NOTFOUND THEN
983 CLOSE dupcheck;
984 RETURN FND_API.G_FALSE;
985 END IF;
986
987 x_to_id := l_record_id;
988 CLOSE dupcheck;
989 RETURN FND_API.G_TRUE;
990
991 EXCEPTION
992 WHEN OTHERS THEN
993 FND_MESSAGE.SET_NAME('AR', 'HZ_MERGE_SQL_ERROR');
994 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
995 FND_MSG_PUB.ADD;
996 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1000 p_from_id IN NUMBER:=FND_API.G_MISS_NUM,
997 END check_languages_dup;
998
999 FUNCTION check_party_site_use_dup(
1001 x_to_id IN OUT NOCOPY NUMBER,
1002 p_from_fk_id IN NUMBER:=FND_API.G_MISS_NUM,
1003 p_to_fk_id IN NUMBER:=FND_API.G_MISS_NUM,
1004 x_return_status IN OUT NOCOPY VARCHAR2)
1005 RETURN VARCHAR2 IS
1006
1007 ---Bug:2619948 remove comments, begin_date and end_date from duplicate check
1008
1009 CURSOR dupcheck IS
1010 SELECT
1011 PARTY_SITE_USE_ID
1012 FROM HZ_PARTY_SITE_USES
1013 WHERE party_site_id = p_to_fk_id
1014 AND (status IS NULL OR status = 'A')
1015 AND SITE_USE_TYPE =
1016 (SELECT
1017 SITE_USE_TYPE
1018 FROM HZ_PARTY_SITE_USES
1019 WHERE party_site_use_id = p_from_id);
1020
1021 l_record_id NUMBER;
1022 BEGIN
1023
1024 x_to_id := FND_API.G_MISS_NUM;
1025
1026 OPEN dupcheck;
1027 FETCH dupcheck INTO l_record_id;
1028 IF dupcheck%NOTFOUND THEN
1029 CLOSE dupcheck;
1030 RETURN FND_API.G_FALSE;
1031 END IF;
1032
1033 x_to_id := l_record_id;
1034 CLOSE dupcheck;
1035 RETURN FND_API.G_TRUE;
1036
1037 EXCEPTION
1038 WHEN OTHERS THEN
1039 FND_MESSAGE.SET_NAME('AR', 'HZ_MERGE_SQL_ERROR');
1040 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
1041 FND_MSG_PUB.ADD;
1042 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1043 END check_party_site_use_dup;
1044
1045 FUNCTION check_party_site_dup(
1046 p_from_id IN NUMBER:=FND_API.G_MISS_NUM,
1047 x_to_id IN OUT NOCOPY NUMBER,
1048 p_from_fk_id IN NUMBER:=FND_API.G_MISS_NUM,
1049 p_to_fk_id IN NUMBER:=FND_API.G_MISS_NUM,
1050 x_return_status IN OUT NOCOPY VARCHAR2)
1051 RETURN VARCHAR2 IS
1052
1053 CURSOR dupcheck IS
1054 SELECT
1055 PARTY_SITE_ID
1056 FROM HZ_PARTY_SITES
1057 WHERE party_id = p_to_fk_id
1058 AND (status IS NULL OR status = 'A')
1059 AND LOCATION_ID = (
1060 SELECT LOCATION_ID
1061 FROM HZ_PARTY_SITES
1062 WHERE party_site_id = p_from_id);
1063
1064 l_record_id NUMBER;
1065 BEGIN
1066
1067 x_to_id := FND_API.G_MISS_NUM;
1068
1069 OPEN dupcheck;
1070 FETCH dupcheck INTO l_record_id;
1071 IF dupcheck%NOTFOUND THEN
1072 CLOSE dupcheck;
1073 RETURN FND_API.G_FALSE;
1074 END IF;
1075
1076 x_to_id := l_record_id;
1077 CLOSE dupcheck;
1078 RETURN FND_API.G_TRUE;
1079
1080 EXCEPTION
1081 WHEN OTHERS THEN
1082 FND_MESSAGE.SET_NAME('AR', 'HZ_MERGE_SQL_ERROR');
1083 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
1084 FND_MSG_PUB.ADD;
1085 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1086 END check_party_site_dup;
1087
1088 FUNCTION check_code_assignment_dup(
1089 p_from_id IN NUMBER:=FND_API.G_MISS_NUM,
1090 x_to_id IN OUT NOCOPY NUMBER,
1091 p_from_fk_id IN NUMBER:=FND_API.G_MISS_NUM,
1092 p_to_fk_id IN NUMBER:=FND_API.G_MISS_NUM,
1093 p_owner_table_name IN VARCHAR2,
1094 x_return_status IN OUT NOCOPY VARCHAR2)
1095 RETURN VARCHAR2 IS
1096
1097 CURSOR dupcheck IS
1098 SELECT
1099 CODE_ASSIGNMENT_ID
1100 FROM HZ_CODE_ASSIGNMENTS
1101 WHERE owner_table_name = p_owner_table_name
1102 AND owner_table_id = p_to_fk_id
1103 --AND (status IS NULL OR status = 'A')--Commented for Bug#3016319.
1104 AND CLASS_CATEGORY || CLASS_CODE = (
1105 SELECT CLASS_CATEGORY ||
1106 CLASS_CODE
1107 FROM HZ_CODE_ASSIGNMENTS
1108 WHERE code_assignment_id = p_from_id);
1109
1110 CURSOR c_cont_source IS
1111 SELECT CONTENT_SOURCE_TYPE
1112 FROM HZ_CODE_ASSIGNMENTS
1113 WHERE code_assignment_id = p_from_id;
1114
1115 l_cont_source VARCHAR2(255);
1116 l_record_id NUMBER;
1117 l_from_last_upd_date DATE;
1118 l_to_last_upd_date DATE;
1119 l_temp NUMBER;
1120
1121 BEGIN
1122
1123 x_to_id := FND_API.G_MISS_NUM;
1124
1125 OPEN c_cont_source;
1126 FETCH c_cont_source INTO l_cont_source;
1127 CLOSE c_cont_source;
1128
1129 IF l_cont_source = 'DNB' THEN
1130 RETURN FND_API.G_FALSE;
1131 END IF;
1132
1133 OPEN dupcheck;
1134 FETCH dupcheck INTO l_record_id;
1135 IF dupcheck%NOTFOUND THEN
1136 CLOSE dupcheck;
1137 RETURN FND_API.G_FALSE;
1138 END IF;
1139
1140 x_to_id := l_record_id;
1141 CLOSE dupcheck;
1142 RETURN FND_API.G_TRUE;
1143
1144 END check_code_assignment_dup;
1145
1146 FUNCTION check_financial_number_dup(
1147 p_from_id IN NUMBER:=FND_API.G_MISS_NUM,
1148 x_to_id IN OUT NOCOPY NUMBER,
1149 p_from_fk_id IN NUMBER:=FND_API.G_MISS_NUM,
1150 p_to_fk_id IN NUMBER:=FND_API.G_MISS_NUM,
1151 x_return_status IN OUT NOCOPY VARCHAR2)
1152 RETURN VARCHAR2 IS
1153
1154 CURSOR dupcheck IS
1155 SELECT
1156 FINANCIAL_NUMBER_ID
1157 FROM HZ_FINANCIAL_NUMBERS
1158 WHERE financial_report_id = p_to_fk_id
1159 AND NVL(FINANCIAL_NUMBER_NAME,'NONAME') = (
1160 SELECT
1161 NVL(FINANCIAL_NUMBER_NAME,'NONAME')
1162 FROM HZ_FINANCIAL_NUMBERS
1163 WHERE financial_number_id = p_from_id);
1164
1165 l_record_id NUMBER;
1166
1167 BEGIN
1168
1169 x_to_id := FND_API.G_MISS_NUM;
1170
1171 OPEN dupcheck;
1172 FETCH dupcheck INTO l_record_id;
1173 IF dupcheck%NOTFOUND THEN
1174 CLOSE dupcheck;
1175 RETURN FND_API.G_FALSE;
1176 END IF;
1177
1178 x_to_id := l_record_id;
1179 CLOSE dupcheck;
1180 RETURN FND_API.G_TRUE;
1181
1185 p_from_id IN NUMBER:=FND_API.G_MISS_NUM,
1182 END check_financial_number_dup;
1183
1184 FUNCTION check_org_contact_role_dup(
1186 x_to_id IN OUT NOCOPY NUMBER,
1187 p_from_fk_id IN NUMBER:=FND_API.G_MISS_NUM,
1188 p_to_fk_id IN NUMBER:=FND_API.G_MISS_NUM,
1189 x_return_status IN OUT NOCOPY VARCHAR2)
1190 RETURN VARCHAR2 IS
1191
1192 CURSOR dupcheck IS
1193 SELECT
1194 ORG_CONTACT_ROLE_ID
1195 FROM HZ_ORG_CONTACT_ROLES
1196 WHERE org_contact_id = p_to_fk_id
1197 AND ROLE_TYPE = (
1198 SELECT ROLE_TYPE
1199 FROM HZ_ORG_CONTACT_ROLES
1200 WHERE ORG_CONTACT_ROLE_ID = p_from_id);
1201
1202 l_record_id NUMBER;
1203
1204 BEGIN
1205
1206 x_to_id := FND_API.G_MISS_NUM;
1207
1208 OPEN dupcheck;
1209 FETCH dupcheck INTO l_record_id;
1210 IF dupcheck%NOTFOUND THEN
1211 CLOSE dupcheck;
1212 RETURN FND_API.G_FALSE;
1213 END IF;
1214
1215 x_to_id := l_record_id;
1216 CLOSE dupcheck;
1217 RETURN FND_API.G_TRUE;
1218
1219 END check_org_contact_role_dup;
1220
1221 FUNCTION check_contact_preference_dup(
1222 p_from_id IN NUMBER:=FND_API.G_MISS_NUM,
1223 x_to_id IN OUT NOCOPY NUMBER,
1224 p_from_fk_id IN NUMBER:=FND_API.G_MISS_NUM,
1225 p_to_fk_id IN NUMBER:=FND_API.G_MISS_NUM,
1226 p_owner_table_name IN VARCHAR2,
1227 x_return_status IN OUT NOCOPY VARCHAR2)
1228 RETURN VARCHAR2 IS
1229
1230 CURSOR dupcheck IS
1231 SELECT
1232 CONTACT_PREFERENCE_ID
1233 FROM HZ_CONTACT_PREFERENCES
1234 WHERE contact_level_table = p_owner_table_name
1235 AND contact_level_table_id = p_to_fk_id
1236 AND (status IS NULL OR status = 'A')
1237 AND --Bug:4390508 - contact_type || preference_code || preference_topic_type ||
1238 contact_type || preference_topic_type ||
1239 preference_topic_type_id || preference_topic_type_code =
1240 (SELECT
1241 --Bug:4390508 - contact_type || preference_code ||
1242 contact_type ||
1243 preference_topic_type ||
1244 preference_topic_type_id ||
1245 preference_topic_type_code
1246 FROM HZ_CONTACT_PREFERENCES
1247 WHERE contact_preference_id = p_from_id)
1248 AND NOT (
1249 ( preference_end_date is not null and
1250 preference_end_date <= ( SELECT preference_start_date
1251 FROM HZ_CONTACT_PREFERENCES
1252 WHERE contact_preference_id = p_from_id))
1253 OR
1254 ( exists (SELECT 1
1255 FROM HZ_CONTACT_PREFERENCES
1256 WHERE contact_preference_id = p_from_id
1257 AND preference_end_date is not null) AND
1258 preference_start_date >= ( SELECT preference_end_date
1259 FROM HZ_CONTACT_PREFERENCES
1260 WHERE contact_preference_id = p_from_id))
1261 )
1262 AND NOT (
1263 (
1264 (decode(preference_start_time_hr, null, 0, preference_start_time_hr) * 60 +
1265 decode(preference_start_time_mi, null, 0, preference_start_time_mi)) >
1266 ( select (decode(preference_end_time_hr, null, 24, preference_end_time_hr) * 60 +
1267 decode (preference_end_time_mi, null, 60, preference_end_time_mi))
1268 from HZ_CONTACT_PREFERENCES
1269 WHERE contact_preference_id = p_from_id)
1270 ) OR (
1271 (decode(preference_end_time_hr, null, 24, preference_end_time_hr ) * 60 +
1272 decode(preference_end_time_mi, null, 60, preference_end_time_mi)) <
1273 (select (decode(preference_start_time_hr, null, 0, preference_start_time_hr) * 60 +
1274 decode(preference_start_time_mi, null, 0, preference_start_time_mi ))
1275 FROM HZ_CONTACT_PREFERENCES
1276 WHERE contact_preference_id = p_from_id)
1277 )
1278 );
1279
1280 l_record_id NUMBER;
1281
1282 BEGIN
1283
1284 x_to_id := FND_API.G_MISS_NUM;
1285
1286 OPEN dupcheck;
1287 FETCH dupcheck INTO l_record_id;
1288 IF dupcheck%NOTFOUND THEN
1289 CLOSE dupcheck;
1290 RETURN FND_API.G_FALSE;
1291 END IF;
1292
1293 x_to_id := l_record_id;
1294 CLOSE dupcheck;
1295 RETURN FND_API.G_TRUE;
1296
1297 EXCEPTION
1298 WHEN OTHERS THEN
1299 FND_MESSAGE.SET_NAME('AR', 'HZ_MERGE_SQL_ERROR');
1300 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
1301 FND_MSG_PUB.ADD;
1302 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1303 END check_contact_preference_dup;
1304
1305 --Bug No: 4577535 fix checking for duplicate addresses
1306
1307 FUNCTION check_address_dup(
1308 p_from_location_id IN NUMBER:=FND_API.G_MISS_NUM,
1309 x_to_id IN OUT NOCOPY NUMBER,
1310 p_from_fk_id IN NUMBER:=FND_API.G_MISS_NUM,
1311 p_to_fk_id IN NUMBER:=FND_API.G_MISS_NUM,
1312 x_return_status IN OUT NOCOPY VARCHAR2)
1313 RETURN VARCHAR2 IS
1314
1315 CURSOR dupcheck IS
1316 SELECT PARTY_SITE_ID
1317 FROM HZ_PARTY_SITES ps,
1318 HZ_LOCATIONS l
1319 WHERE ps.party_id = p_to_fk_id
1320 AND ps.location_id = l.location_id
1321 AND (ps.status IS NULL OR ps.status = 'A')
1322 AND UPPER(TRIM(ADDRESS1) ||
1323 TRIM(ADDRESS2) ||
1324 TRIM(ADDRESS3) ||
1325 TRIM(ADDRESS4) ||
1326 TRIM(COUNTRY) ||
1327 TRIM(STATE) ||
1328 TRIM(CITY) ||
1329 TRIM(PROVINCE) ||
1330 TRIM(COUNTY) ||
1334 TRIM(ADDRESS3) ||
1331 TRIM(POSTAL_CODE)) =
1332 (SELECT UPPER(TRIM(ADDRESS1) ||
1333 TRIM(ADDRESS2) ||
1335 TRIM(ADDRESS4) ||
1336 TRIM(COUNTRY) ||
1337 TRIM(STATE) ||
1338 TRIM(CITY) ||
1339 TRIM(PROVINCE) ||
1340 TRIM(COUNTY) ||
1341 TRIM(POSTAL_CODE))
1342 FROM HZ_LOCATIONS
1343 WHERE LOCATION_ID = p_from_location_id);
1344
1345 l_record_id NUMBER;
1346 BEGIN
1347
1348 x_to_id := FND_API.G_MISS_NUM;
1349
1350 OPEN dupcheck;
1351 FETCH dupcheck INTO l_record_id;
1352 IF dupcheck%NOTFOUND THEN
1353 CLOSE dupcheck;
1354 RETURN FND_API.G_FALSE;
1355 END IF;
1356
1357 x_to_id := l_record_id;
1358 CLOSE dupcheck;
1359 RETURN FND_API.G_TRUE;
1360
1361 EXCEPTION
1362 WHEN OTHERS THEN
1363 FND_MESSAGE.SET_NAME('AR', 'HZ_MERGE_SQL_ERROR');
1364 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
1365 FND_MSG_PUB.ADD;
1366 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1367 END check_address_dup;
1368
1369 END HZ_MERGE_DUP_CHECK;