1 PACKAGE BODY HZ_ACCT_MERGE_UTIL AS
2 /*$Header: ARHACTUB.pls 120.2.12010000.2 2009/07/10 11:53:25 pnallabo ship $ */
3
4 TYPE NumberList IS TABLE OF NUMBER(15) INDEX BY VARCHAR2(100); /* bug 8623796 */
5
6 g_cust_list NumberList;
7 g_addr_list NumberList;
8 g_site_list NumberList;
9
10 g_last_set NUMBER;
11 g_last_request_id NUMBER;
12
13 PROCEDURE load_set (
14 p_set_num NUMBER,
15 p_request_id NUMBER) IS
16
17 BEGIN
18 IF (g_last_set IS NOT NULL AND g_last_request_id IS NOT NULL AND
19 g_last_set=p_set_num AND g_last_request_id=p_request_id) THEN
20 RETURN;
21 END IF;
22
23 g_cust_list.DELETE;
24 g_site_list.DELETE;
25 g_addr_list.DELETE;
26 FOR CUST IN (
27 SELECT distinct customer_id, duplicate_id
28 FROM ra_customer_merges
29 WHERE set_number = p_set_num
30 AND request_id = p_request_id
31 AND process_flag = 'N' ) LOOP
32 g_cust_list(CUST.duplicate_id) := CUST.customer_id;
33 END LOOP;
34 FOR ADDR IN (
35 SELECT distinct customer_address_id, duplicate_address_id
36 FROM ra_customer_merges
37 WHERE set_number = p_set_num
38 AND request_id = p_request_id
39 AND process_flag = 'N' ) LOOP
40 g_addr_list(ADDR.duplicate_address_id) := ADDR.customer_address_id;
41 END LOOP;
42
43 FOR SITE IN (
44 SELECT distinct customer_site_id, duplicate_site_id
45 FROM ra_customer_merges
46 WHERE set_number = p_set_num
47 AND request_id = p_request_id
48 AND process_flag = 'N' ) LOOP
49 g_site_list(SITE.duplicate_site_id) := SITE.customer_site_id;
50 END LOOP;
51 g_last_set:=p_set_num;
52 g_last_request_id:=p_request_id;
53 END;
54
55
56
57 FUNCTION GETDUP_SITE_USE (
58 p_site_use_id NUMBER) RETURN NUMBER IS
59 BEGIN
60 IF p_site_use_id IS NULL THEN
61 RETURN p_site_use_id;
62 END IF;
63 RETURN g_site_list(p_site_use_id);
67 END;
64 EXCEPTION
65 WHEN NO_DATA_FOUND THEN
66 return p_site_use_id;
68
69 FUNCTION GETDUP_SITE(
70 p_site_id NUMBER) RETURN NUMBER IS
71 BEGIN
72 IF p_site_id IS NULL THEN
73 RETURN p_site_id;
74 END IF;
75 RETURN g_addr_list(p_site_id);
76 EXCEPTION
77 WHEN NO_DATA_FOUND THEN
78 return p_site_id;
79 END;
80
81
82 FUNCTION GETDUP_ACCOUNT (
83 p_acct_id NUMBER) RETURN NUMBER IS
84 BEGIN
85 IF p_acct_id IS NULL THEN
86 RETURN p_acct_id;
87 END IF;
88 return g_cust_list(p_acct_id);
89 EXCEPTION
90 WHEN NO_DATA_FOUND THEN
91 return p_acct_id;
92 END;
93 END;
94
[Home] [Help]