1 PACKAGE BODY CSP_MERGE_PKG AS
2 /* $Header: cspvmrgb.pls 115.6 2003/05/22 18:46:46 jjalla noship $ */
3 -- Start of Comments
4 -- Package name : CSP_MERGE_PKG
5 -- Purpose : Merges duplicate parties in Spares tables. The
6 -- Spares tables that need to be considered for
7 -- Party Merge are:
8 -- CSP_MOVEORDER_HEADERS
9 -- CSP_PACKLIST_HEADERS
10 -- CSP_RS_CUST_RELATIONS
11
12 --
13 -- History
14 -- Date NAME MODIFICATIONS
15 -- ----------- ------------- ------------------------------------------
16 -- 23-JUL-2001 iouyang Created.
17 --
18 --
19 -- End of Comments
20
21
22 G_PROC_NAME CONSTANT VARCHAR2(30) := 'CSP_MERGE_PKG';
23 G_USER_ID CONSTANT NUMBER(15) := FND_GLOBAL.USER_ID;
24 G_LOGIN_ID CONSTANT NUMBER(15) := FND_GLOBAL.LOGIN_ID;
25
26
27
28 PROCEDURE mo_merge_party_site(
29 p_entity_name IN VARCHAR2,
30 p_from_id IN NUMBER,
31 x_to_id OUT NOCOPY NUMBER,
32 p_from_fk_id IN NUMBER,
33 p_to_fk_id IN NUMBER,
34 p_parent_entity_name IN VARCHAR2,
35 p_batch_id IN NUMBER,
36 p_batch_party_id IN NUMBER,
37 x_return_status OUT NOCOPY VARCHAR2)
38 IS
39
40 l_merge_reason_code VARCHAR2(30);
41 l_api_name VARCHAR2(30) := 'merge_party_site';
42 l_count NUMBER(10) := 0;
43
44 RESOURCE_BUSY EXCEPTION;
45 PRAGMA EXCEPTION_INIT(RESOURCE_BUSY, -0054);
46
47 BEGIN
48 x_return_status := FND_API.G_RET_STS_SUCCESS;
49
50 -- 1. Do all Validations
51
52 -- Check the Merge reason. If Merge Reason is Duplicate Record then no validation is performed. Otherwise check if the resource is being used somewhere.
53 select merge_reason_code
54 into l_merge_reason_code
55 from hz_merge_batch
56 where batch_id = p_batch_id;
57
58 if l_merge_reason_code = 'DUPLICATE' then
59 -- if reason code is duplicate then allow the party merge to happen without
60 -- any validations.
61 null;
62 else
63 -- if there are any validations to be done, include it in this section
64 null;
65 end if;
66
67 -- 2. Perform the Merge Operation.
68
69 -- If the parent has NOT changed (ie. Parent getting transferred) then nothing
70 -- needs to be done. Set Merged To Id is same as Merged From Id and return
71
72 if p_from_fk_id = p_to_fk_id then
73 x_to_id := p_from_id;
74 return;
75 end if;
76
77 -- If the parent has changed(ie. Parent is getting merged) then transfer the
78 -- dependent record to the new parent. Before transferring check if a similar
79 -- dependent record exists on the new parent. If a duplicate exists then do
80 -- not transfer and return the id of the duplicate record as the Merged To Id
81
82 if p_from_fk_id <> p_to_fk_id then
83 begin
84 update csp_moveorder_headers
85 set party_site_id = decode(party_site_id, p_from_fk_id, p_to_fk_id, party_site_id),
86 last_update_date = SYSDATE,
87 last_updated_by = G_USER_ID,
88 last_update_login = G_LOGIN_ID
89 where party_site_id = p_from_fk_id;
90
91 Exception
92 when others then
93 FND_MESSAGE.SET_NAME('CSP', 'CSP_PARTY_MERGE_API_EXCEP');
94 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
95 FND_MSG_PUB.ADD;
96 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
97 raise;
98 end;
99 end if;
100 END mo_merge_party_site;
101
102
103 PROCEDURE pl_merge_party_site(
104 p_entity_name IN VARCHAR2,
105 p_from_id IN NUMBER,
106 x_to_id OUT NOCOPY NUMBER,
107 p_from_fk_id IN NUMBER,
108 p_to_fk_id IN NUMBER,
109 p_parent_entity_name IN VARCHAR2,
110 p_batch_id IN NUMBER,
111 p_batch_party_id IN NUMBER,
112 x_return_status OUT NOCOPY VARCHAR2)
113 IS
114
115 l_merge_reason_code VARCHAR2(30);
116 l_api_name VARCHAR2(30) := 'merge_party_site';
117 l_count NUMBER(10) := 0;
118
119 RESOURCE_BUSY EXCEPTION;
120 PRAGMA EXCEPTION_INIT(RESOURCE_BUSY, -0054);
121
122 BEGIN
123 x_return_status := FND_API.G_RET_STS_SUCCESS;
124
125 -- 1. Do all Validations
126
127 -- Check the Merge reason. If Merge Reason is Duplicate Record then no validation is performed. Otherwise check if the resource is being used somewhere.
128 select merge_reason_code
129 into l_merge_reason_code
130 from hz_merge_batch
131 where batch_id = p_batch_id;
132
133 if l_merge_reason_code = 'DUPLICATE' then
134 -- if reason code is duplicate then allow the party merge to happen without
135 -- any validations.
136 null;
137 else
138 -- if there are any validations to be done, include it in this section
139 null;
140 end if;
141
142 -- 2. Perform the Merge Operation.
143
144 -- If the parent has NOT changed (ie. Parent getting transferred) then nothing
145 -- needs to be done. Set Merged To Id is same as Merged From Id and return
146
147 if p_from_fk_id = p_to_fk_id then
148 x_to_id := p_from_id;
149 return;
150 end if;
151
152 -- If the parent has changed(ie. Parent is getting merged) then transfer the
153 -- dependent record to the new parent. Before transferring check if a similar
154 -- dependent record exists on the new parent. If a duplicate exists then do
155 -- not transfer and return the id of the duplicate record as the Merged To Id
156
157 if p_from_fk_id <> p_to_fk_id then
158 begin
159 update csp_packlist_headers
160 set party_site_id = decode(party_site_id, p_from_fk_id, p_to_fk_id, party_site_id),
161 last_update_date = SYSDATE,
162 last_updated_by = G_USER_ID,
163 last_update_login = G_LOGIN_ID
164 where party_site_id = p_from_fk_id;
165
166 Exception
167 when others then
168 FND_MESSAGE.SET_NAME('CSP', 'CSP_PARTY_MERGE_API_EXCEP');
169 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
170 FND_MSG_PUB.ADD;
171 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
172 raise;
173 end;
174 end if;
175 END pl_merge_party_site;
176
177
178 PROCEDURE merge_cust_account (
179 req_id Number,
180 set_num Number,
181 process_mode Varchar2) IS
182
183 TYPE MERGE_HEADER_ID_LIST_TYPE IS TABLE OF
184 RA_CUSTOMER_MERGE_HEADERS.CUSTOMER_MERGE_HEADER_ID%TYPE
185 INDEX BY BINARY_INTEGER;
186 MERGE_HEADER_ID_LIST MERGE_HEADER_ID_LIST_TYPE;
187
188 TYPE RS_CUST_RELATION_ID_LIST_TYPE IS TABLE OF
189 CSP_RS_CUST_RELATIONS.RS_CUST_RELATION_ID%TYPE
190 INDEX BY BINARY_INTEGER;
191 PRIMARY_KEY_ID_LIST RS_CUST_RELATION_ID_LIST_TYPE;
192
193 TYPE CUSTOMER_ID_LIST_TYPE IS TABLE OF
194 CSP_RS_CUST_RELATIONS.CUSTOMER_ID%TYPE
195 INDEX BY BINARY_INTEGER;
196 NUM_COL1_ORIG_LIST CUSTOMER_ID_LIST_TYPE;
197 NUM_COL1_NEW_LIST CUSTOMER_ID_LIST_TYPE;
198
199 l_profile_val VARCHAR2(30);
200 CURSOR merged_records IS
201 SELECT distinct m.CUSTOMER_MERGE_HEADER_ID
202 ,yt.RS_CUST_RELATION_ID
203 ,yt.CUSTOMER_ID
204 FROM CSP_RS_CUST_RELATIONS yt, ra_customer_merges m
205 WHERE (
206 yt.CUSTOMER_ID = m.DUPLICATE_ID
207 ) AND m.process_flag = 'N'
208 AND m.request_id = req_id
209 AND m.set_number = set_num;
210 l_last_fetch BOOLEAN := FALSE;
211 l_count NUMBER;
212 BEGIN
213 IF process_mode='LOCK' THEN
214 NULL;
215 ELSE
216 ARP_MESSAGE.SET_NAME('AR','AR_UPDATING_TABLE');
217 ARP_MESSAGE.SET_TOKEN('TABLE_NAME','CSP_RS_CUST_RELATIONS',FALSE);
218 HZ_ACCT_MERGE_UTIL.load_set(set_num, req_id);
219 l_profile_val := FND_PROFILE.VALUE('HZ_AUDIT_ACCT_MERGE');
220
221 open merged_records;
222 LOOP
223 FETCH merged_records BULK COLLECT INTO
224 MERGE_HEADER_ID_LIST
225 , PRIMARY_KEY_ID_LIST
226 , NUM_COL1_ORIG_LIST
227 limit 1000 ;
228 IF merged_records%NOTFOUND THEN
229 l_last_fetch := TRUE;
230 END IF;
231 IF MERGE_HEADER_ID_LIST.COUNT = 0 and l_last_fetch then
232 exit;
233 END IF;
234 FOR I in 1..MERGE_HEADER_ID_LIST.COUNT LOOP
235 NUM_COL1_NEW_LIST(I) := HZ_ACCT_MERGE_UTIL.GETDUP_ACCOUNT(NUM_COL1_ORIG_LIST(I));
236 END LOOP;
237 IF l_profile_val IS NOT NULL AND l_profile_val = 'Y' THEN
238 FORALL I in 1..MERGE_HEADER_ID_LIST.COUNT
239 INSERT INTO HZ_CUSTOMER_MERGE_LOG (
240 MERGE_LOG_ID,
241 TABLE_NAME,
242 MERGE_HEADER_ID,
243 PRIMARY_KEY_ID,
244 NUM_COL1_ORIG,
245 NUM_COL1_NEW,
246 ACTION_FLAG,
247 REQUEST_ID,
248 CREATED_BY,
249 CREATION_DATE,
250 LAST_UPDATE_LOGIN,
251 LAST_UPDATE_DATE,
252 LAST_UPDATED_BY
253 ) VALUES ( HZ_CUSTOMER_MERGE_LOG_s.nextval,
254 'CSP_RS_CUST_RELATIONS',
255 MERGE_HEADER_ID_LIST(I),
256 PRIMARY_KEY_ID_LIST(I),
257 NUM_COL1_ORIG_LIST(I),
258 NUM_COL1_NEW_LIST(I),
259 'U',
260 req_id,
261 hz_utility_pub.CREATED_BY,
262 hz_utility_pub.CREATION_DATE,
263 hz_utility_pub.LAST_UPDATE_LOGIN,
264 hz_utility_pub.LAST_UPDATE_DATE,
265 hz_utility_pub.LAST_UPDATED_BY
266 );
267
268 END IF; FORALL I in 1..MERGE_HEADER_ID_LIST.COUNT
269 UPDATE CSP_RS_CUST_RELATIONS yt SET
270 CUSTOMER_ID=NUM_COL1_NEW_LIST(I)
271 , LAST_UPDATE_DATE=SYSDATE
272 , last_updated_by=arp_standard.profile.user_id
273 , last_update_login=arp_standard.profile.last_update_login
274 , REQUEST_ID=req_id
275 , PROGRAM_APPLICATION_ID=arp_standard.profile.program_application_id
276 , PROGRAM_ID=arp_standard.profile.program_id
277 , PROGRAM_UPDATE_DATE=SYSDATE
278 WHERE RS_CUST_RELATION_ID=PRIMARY_KEY_ID_LIST(I)
279 ;
280 l_count := l_count + SQL%ROWCOUNT;
281 IF l_last_fetch THEN
282 EXIT;
283 END IF;
284 END LOOP;
285
286 arp_message.set_name('AR','AR_ROWS_UPDATED');
287 arp_message.set_token('NUM_ROWS',to_char(l_count));
288 END IF;
289 EXCEPTION
290 WHEN OTHERS THEN
291 arp_message.set_line( 'merge_cust_account');
292 RAISE;
293 END merge_cust_account;
294
295 END CSP_MERGE_PKG;
[Home] [Help]