1 PACKAGE BODY FUN_CustomerMerge_PKG AS
2 /* $Header: funntcmb.pls 120.2 2006/01/25 14:11:13 asrivats noship $ */
3
4 PROCEDURE Merge_Customer (
5 req_id NUMBER,
6 set_num NUMBER,
7 process_mode VARCHAR2) IS
8
9 TYPE MERGE_HEADER_ID_LIST_TYPE IS TABLE OF
10 RA_CUSTOMER_MERGE_HEADERS.CUSTOMER_MERGE_HEADER_ID%TYPE
11 INDEX BY BINARY_INTEGER;
12 MERGE_HEADER_ID_LIST MERGE_HEADER_ID_LIST_TYPE;
13
14 TYPE NETTING_CUSTOMER_ID_LIST_TYPE IS TABLE OF
15 FUN_NET_CUSTOMERS_ALL.NETTING_CUSTOMER_ID%TYPE
16 INDEX BY BINARY_INTEGER;
17 PRIMARY_KEY_ID_LIST NETTING_CUSTOMER_ID_LIST_TYPE;
18
19 TYPE CUST_ACCOUNT_ID_LIST_TYPE IS TABLE OF
20 FUN_NET_CUSTOMERS_ALL.CUST_ACCOUNT_ID%TYPE
21 INDEX BY BINARY_INTEGER;
22 NUM_COL1_ORIG_LIST CUST_ACCOUNT_ID_LIST_TYPE;
23 NUM_COL1_NEW_LIST CUST_ACCOUNT_ID_LIST_TYPE;
24
25 TYPE CUST_SITE_USE_ID_LIST_TYPE IS TABLE OF
26 FUN_NET_CUSTOMERS_ALL.CUST_SITE_USE_ID%TYPE
27 INDEX BY BINARY_INTEGER;
28 NUM_COL2_ORIG_LIST CUST_SITE_USE_ID_LIST_TYPE;
29 NUM_COL2_NEW_LIST CUST_SITE_USE_ID_LIST_TYPE;
30
31 TYPE CUSTOMER_PRIORITY_LIST_TYPE IS TABLE OF
32 FUN_NET_CUSTOMERS_ALL.CUST_PRIORITY%TYPE
33 INDEX BY BINARY_INTEGER;
34 NUM_COL3_ORIG_LIST CUSTOMER_PRIORITY_LIST_TYPE;
35 NUM_COL3_NEW_LIST CUSTOMER_PRIORITY_LIST_TYPE;
36
37 l_profile_val VARCHAR2(30);
38 CURSOR merged_records IS
39 SELECT distinct CUSTOMER_MERGE_HEADER_ID
40 ,NETTING_CUSTOMER_ID
41 ,CUST_ACCOUNT_ID
42 ,CUST_SITE_USE_ID
43 ,CUST_PRIORITY
44 FROM FUN_NET_CUSTOMERS_ALL yt, ra_customer_merges m
45 WHERE (
46 yt.CUST_ACCOUNT_ID = m.DUPLICATE_ID
47 OR yt.CUST_SITE_USE_ID = m.DUPLICATE_SITE_ID
48 )
49 AND m.process_flag = 'N'
50 AND m.request_id = req_id
51 AND m.set_number = set_num;
52
53 l_last_fetch BOOLEAN := FALSE;
54 l_count NUMBER := 0;
55
56 BEGIN
57 IF process_mode='LOCK' THEN
58 NULL;
59 ELSE
60 ARP_MESSAGE.SET_NAME('AR','AR_UPDATING_TABLE');
61 ARP_MESSAGE.SET_TOKEN('TABLE_NAME','FUN_NET_CUSTOMERS_ALL',FALSE);
62 HZ_ACCT_MERGE_UTIL.load_set(set_num, req_id);
63
64 l_profile_val := FND_PROFILE.VALUE('HZ_AUDIT_ACCT_MERGE');
65
66 OPEN merged_records;
67 LOOP
68 FETCH merged_records BULK COLLECT INTO
69 MERGE_HEADER_ID_LIST
70 , PRIMARY_KEY_ID_LIST
71 , NUM_COL1_ORIG_LIST
72 , NUM_COL2_ORIG_LIST
73 , NUM_COL3_ORIG_LIST
74 ;
75 IF merged_records%NOTFOUND THEN
76 l_last_fetch := TRUE;
77 END IF;
78 IF MERGE_HEADER_ID_LIST.COUNT = 0 and l_last_fetch then
79 exit;
80 END IF;
81 FOR I in 1..MERGE_HEADER_ID_LIST.COUNT LOOP
82 NUM_COL1_NEW_LIST(I) := HZ_ACCT_MERGE_UTIL.GETDUP_ACCOUNT(NUM_COL1_ORIG_LIST(I));
83 NUM_COL2_NEW_LIST(I) := HZ_ACCT_MERGE_UTIL.GETDUP_SITE_USE(NUM_COL2_ORIG_LIST(I));
84 NUM_COL3_NEW_LIST(I) := NUM_COL3_ORIG_LIST(I);
85 END LOOP;
86 IF l_profile_val IS NOT NULL AND l_profile_val = 'Y' THEN
87 FORALL I in 1..MERGE_HEADER_ID_LIST.COUNT
88 INSERT INTO HZ_CUSTOMER_MERGE_LOG (
89 MERGE_LOG_ID,
90 TABLE_NAME,
91 MERGE_HEADER_ID,
92 PRIMARY_KEY_ID,
93 NUM_COL1_ORIG,
94 NUM_COL1_NEW,
95 NUM_COL2_ORIG,
96 NUM_COL2_NEW,
97 NUM_COL3_ORIG,
98 NUM_COL3_NEW,
99 ACTION_FLAG,
100 REQUEST_ID,
101 CREATED_BY,
102 CREATION_DATE,
103 LAST_UPDATE_LOGIN,
104 LAST_UPDATE_DATE,
105 LAST_UPDATED_BY
106 ) VALUES (
107 HZ_CUSTOMER_MERGE_LOG_s.nextval,
108 'FUN_NET_CUSTOMERS_ALL',
109 MERGE_HEADER_ID_LIST(I),
110 PRIMARY_KEY_ID_LIST(I),
111 NUM_COL1_ORIG_LIST(I),
112 NUM_COL1_NEW_LIST(I),
113 NUM_COL2_ORIG_LIST(I),
114 NUM_COL2_NEW_LIST(I),
115 NUM_COL3_ORIG_LIST(I),
116 NUM_COL3_NEW_LIST(I),
117 'U',
118 req_id,
119 hz_utility_pub.CREATED_BY,
120 hz_utility_pub.CREATION_DATE,
121 hz_utility_pub.LAST_UPDATE_LOGIN,
122 hz_utility_pub.LAST_UPDATE_DATE,
123 hz_utility_pub.LAST_UPDATED_BY
124 );
125 END IF;
126 FORALL I in 1..MERGE_HEADER_ID_LIST.COUNT
127
128 UPDATE FUN_NET_CUSTOMERS_ALL yt SET
129 CUST_ACCOUNT_ID=NUM_COL1_NEW_LIST(I)
130 ,CUST_SITE_USE_ID=NUM_COL2_NEW_LIST(I)
131 , LAST_UPDATE_DATE=SYSDATE
132 , last_updated_by=arp_standard.profile.user_id
133 , last_update_login=arp_standard.profile.last_update_login
134 WHERE NETTING_CUSTOMER_ID=PRIMARY_KEY_ID_LIST(I);
135
136 l_count := l_count + SQL%ROWCOUNT;
137 IF l_last_fetch THEN
138 EXIT;
139 END IF;
140 END LOOP;
141
142 arp_message.set_name('AR','AR_ROWS_UPDATED');
143 arp_message.set_token('NUM_ROWS',to_char(l_count));
144
145 /* If there is more than one record that has the same agreement_id,cust_account_id and cust_site_use_id then update the customer priority of the records to the highest priority amongst them */
146 BEGIN
147 UPDATE FUN_NET_CUSTOMERS_ALL yt SET
148 CUST_PRIORITY= (SELECT MIN(CUST_PRIORITY)
149 FROM FUN_NET_CUSTOMERS_ALL
150 WHERE AGREEMENT_ID = yt.AGREEMENT_ID
151 AND CUST_ACCOUNT_ID = yt.CUST_ACCOUNT_ID
152 AND nvl(CUST_SITE_USE_ID,0) = DECODE(
153 yt.CUST_SITE_USE_ID,NULL,0,yt.CUST_SITE_USE_ID)
154 )
155 WHERE EXISTS (SELECT 1
156 FROM FUN_NET_CUSTOMERS_ALL
157 WHERE yt.agreement_id = agreement_id
158 AND yt.cust_account_id = cust_account_id
159 AND nvl(CUST_SITE_USE_ID,0) = DECODE(
160 yt.CUST_SITE_USE_ID,NULL
161 ,0,yt.CUST_SITE_USE_ID)
162 GROUP BY agreement_id,cust_account_id,cust_site_use_id
163 HAVING count(agreement_id) > 1);
164 EXCEPTION
165 WHEN NO_DATA_FOUND THEN
166 null;
167 END;
168
169 /* If there is more than one row that has the same agreement_id , customer_priority,cust_account_id and cust_site_use_id , delete the record that has the minimum of the netting customer id */
170
171 BEGIN
172 DELETE FROM FUN_NET_CUSTOMERS_ALL yt
173 WHERE NETTING_CUSTOMER_ID = (
174 SELECT MIN(NETTING_CUSTOMER_ID)
175 FROM FUN_NET_CUSTOMERS_ALL
176 WHERE
177 yt.AGREEMENT_ID = AGREEMENT_ID
178 AND yt.CUST_ACCOUNT_ID = CUST_ACCOUNT_ID
179 AND nvl(yt.CUST_SITE_USE_ID,0) = nvl(CUST_SITE_USE_ID,0)
180 AND yt.CUST_PRIORITY = CUST_PRIORITY
181 GROUP BY AGREEMENT_ID,
182 CUST_ACCOUNT_ID,
183 CUST_SITE_USE_ID,
184 CUST_PRIORITY
185 HAVING COUNT(NETTING_CUSTOMER_ID) > 1);
186
187 EXCEPTION
188 WHEN NO_DATA_FOUND THEN
189 null;
190 END;
191 END IF;
192 EXCEPTION
193 WHEN OTHERS THEN
194 arp_message.set_line( 'Merge_Customer');
195 RAISE;
196 END Merge_Customer;
197 END FUN_CustomerMerge_PKG;
[Home] [Help]