[Home] [Help]
PACKAGE BODY: APPS.HZ_PROFILE_MERGE_PKG
Source
1 PACKAGE BODY hz_profile_merge_pkg AS
2 /*$Header: ARHMPROB.pls 120.1 2005/06/16 21:12:38 jhuang noship $ */
3
4 /* Private Routine Spec*/
5 FUNCTION has_to_prof_this_currency
6 ( p_to_profile_id IN NUMBER,
7 p_currency_code IN VARCHAR2)
8 RETURN NUMBER;
9
10 PROCEDURE do_profile_merge
11 ( p_from_id IN NUMBER:=FND_API.G_MISS_NUM,
12 x_to_id IN OUT NOCOPY NUMBER,
13 p_from_fk_id IN NUMBER:=FND_API.G_MISS_NUM,
14 p_to_fk_id IN NUMBER:=FND_API.G_MISS_NUM,
15 x_return_status IN OUT NOCOPY VARCHAR2);
16
17 PROCEDURE do_profile_amt_transf
18 (p_from_profile_id IN NUMBER,
19 p_to_profile_id IN NUMBER,
20 x_return_status IN OUT NOCOPY VARCHAR2);
21 /**********/
22
23
24 PROCEDURE profile_merge(
25 p_entity_name IN VARCHAR2:=FND_API.G_MISS_CHAR,
26 p_from_id IN NUMBER:=FND_API.G_MISS_NUM,
27 x_to_id IN OUT NOCOPY NUMBER,
28 p_from_fk_id IN NUMBER:=FND_API.G_MISS_NUM,
29 p_to_fk_id IN NUMBER:=FND_API.G_MISS_NUM,
30 p_par_entity_name IN VARCHAR2:=FND_API.G_MISS_CHAR,
31 p_batch_id IN NUMBER:=FND_API.G_MISS_NUM,
32 p_batch_party_id IN NUMBER:=FND_API.G_MISS_NUM,
33 x_return_status OUT NOCOPY VARCHAR2)
34 IS
35 l_to_id NUMBER;
36 BEGIN
37
38 IF (x_to_id IS NULL) THEN
39 l_to_id := FND_API.G_MISS_NUM;
40 ELSE
41 l_to_id := x_to_id;
42 END IF;
43
44 x_return_status := FND_API.G_RET_STS_SUCCESS;
45
46 hz_merge_pkg.check_params(
47 p_entity_name => 'HZ_CUSTOMER_PROFILES',
48 p_from_id => p_from_id,
49 p_to_id => x_to_id,
50 p_from_fk_id => p_from_fk_id,
51 p_to_fk_id => p_to_fk_id,
52 p_par_entity_name => 'HZ_PARTIES',
53 p_proc_name => 'HZ_MERGE_PKG.party_profile_merge',
54 p_exp_ent_name => 'HZ_CUSTOMER_PROFILES',
55 p_exp_par_ent_name => 'HZ_PARTIES',
56 p_pk_column => 'CUST_ACCOUNT_PROFILE_ID',
57 p_par_pk_column => 'PARTY_ID',
58 x_return_status => x_return_status );
59
60 IF x_return_status = FND_API.G_RET_STS_SUCCESS THEN
61
62 do_profile_merge
63 ( p_from_id => p_from_id,
64 x_to_id => x_to_id,
65 p_from_fk_id => p_from_fk_id,
66 p_to_fk_id => p_to_fk_id,
67 x_return_status => x_return_status);
68
69 END IF;
70
71 EXCEPTION
72 WHEN OTHERS THEN
73 FND_MESSAGE.SET_NAME('AR', 'HZ_MERGE_SQL_ERROR');
74 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
75 FND_MSG_PUB.ADD;
76 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
77 END;
78
79 FUNCTION has_to_prof_this_currency
80 ( p_to_profile_id IN NUMBER,
81 p_currency_code IN VARCHAR2)
82 RETURN NUMBER
83 IS
84 CURSOR c1 IS
85 SELECT cust_acct_profile_amt_id,
86 cust_account_profile_id,
87 currency_code
88 FROM hz_cust_profile_amts
89 WHERE cust_account_profile_id = p_to_profile_id;
90 l_rec c1%ROWTYPE;
91 Ret NUMBER := FND_API.G_MISS_NUM;
92 BEGIN
93 OPEN c1;
94 LOOP
95 FETCH c1 INTO l_rec;
96 EXIT WHEN c1%NOTFOUND;
97 IF p_currency_code = l_rec.currency_code THEN
98 ret := l_rec.cust_acct_profile_amt_id;
99 EXIT;
100 END IF;
101 END LOOP;
102 CLOSE c1;
103 RETURN ret;
104 END;
105
106 PROCEDURE do_profile_amt_transf
107 (p_from_profile_id IN NUMBER,
108 p_to_profile_id IN NUMBER,
109 x_return_status IN OUT NOCOPY VARCHAR2)
110 IS
111
112 CURSOR c1 IS
113 SELECT cust_acct_profile_amt_id,
114 cust_account_profile_id,
115 currency_code
116 FROM hz_cust_profile_amts
117 WHERE cust_account_profile_id = p_from_profile_id;
118 l_rec c1%ROWTYPE;
119 l_to_prof_amt_id NUMBER;
120 l_temp NUMBER;
121
122 BEGIN
123
124 OPEN c1;
125 LOOP
126 FETCH c1 INTO l_rec;
127 EXIT WHEN c1%NOTFOUND;
128
129 l_to_prof_amt_id := has_to_prof_this_currency
130 ( p_to_profile_id => p_to_profile_id,
131 p_currency_code => l_rec.currency_code);
132
133 IF l_to_prof_amt_id = FND_API.G_MISS_NUM THEN
134
135 UPDATE hz_cust_profile_amts
136 SET cust_account_profile_id = p_to_profile_id,
137 last_update_date = hz_utility_pub.last_update_date,
138 last_updated_by = hz_utility_pub.user_id,
139 last_update_login = hz_utility_pub.last_update_login,
140 request_id = hz_utility_pub.request_id,
141 program_application_id = hz_utility_pub.program_application_id,
142 program_id = hz_utility_pub.program_id,
143 program_update_date = sysdate
144 WHERE cust_acct_profile_amt_id = l_rec.cust_acct_profile_amt_id;
145
146 END IF;
147 END LOOP;
148 CLOSE c1;
149
150 EXCEPTION
151
152 WHEN OTHERS THEN
153 IF c1%ISOPEN THEN
154 CLOSE c1;
155 END IF;
156 FND_MESSAGE.SET_NAME('AR', 'HZ_MERGE_SQL_ERROR');
157 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
158 FND_MSG_PUB.ADD;
159 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
160
161 END;
162
163
164 PROCEDURE do_profile_merge
165 ( p_from_id IN NUMBER:=FND_API.G_MISS_NUM,
166 x_to_id IN OUT NOCOPY NUMBER,
167 p_from_fk_id IN NUMBER:=FND_API.G_MISS_NUM,
168 p_to_fk_id IN NUMBER:=FND_API.G_MISS_NUM,
169 x_return_status IN OUT NOCOPY VARCHAR2)
170 IS
171
172 CURSOR c_from_profile
173 IS
174 SELECT cust_account_profile_id,
175 cust_account_id,
176 party_id,
177 site_use_id,
178 status
179 FROM hz_customer_profiles
180 WHERE cust_account_profile_id = p_from_id;
181
182 CURSOR c_to_party_profile
183 IS
184 SELECT cust_account_profile_id,
185 cust_account_id,
186 party_id,
187 site_use_id
188 FROM hz_customer_profiles
189 WHERE party_id = p_to_fk_id
190 AND cust_account_id = -1;
191
192 l_from_rec c_from_profile%ROWTYPE;
193 l_to_party_rec c_to_party_profile%ROWTYPE;
194
195 BEGIN
196
197 OPEN c_from_profile;
198 LOOP
199 FETCH c_from_profile INTO l_from_rec;
200 EXIT WHEN c_from_profile%NOTFOUND;
201
202 IF l_from_rec.site_use_id IS NOT NULL THEN
203
204 -- Site Use Level Stuff
205 -- Transfert
206 UPDATE hz_customer_profiles
207 SET party_id = p_to_fk_id,
208 last_update_date = hz_utility_pub.last_update_date,
209 last_updated_by = hz_utility_pub.user_id,
210 last_update_login = hz_utility_pub.last_update_login,
211 request_id = hz_utility_pub.request_id,
212 program_application_id = hz_utility_pub.program_application_id,
213 program_id = hz_utility_pub.program_id,
214 program_update_date = sysdate
215 WHERE cust_account_profile_id = l_from_rec.cust_account_profile_id;
216
217
218 ELSIF l_from_rec.cust_account_id <> -1 THEN
219
220 -- Account Level Stuff
221 -- Transfert
222 UPDATE hz_customer_profiles
223 SET party_id = p_to_fk_id,
224 last_update_date = hz_utility_pub.last_update_date,
225 last_updated_by = hz_utility_pub.user_id,
226 last_update_login = hz_utility_pub.last_update_login,
227 request_id = hz_utility_pub.request_id,
228 program_application_id = hz_utility_pub.program_application_id,
229 program_id = hz_utility_pub.program_id,
230 program_update_date = sysdate
231 WHERE cust_account_profile_id = l_from_rec.cust_account_profile_id;
232
233 ELSE
234 -- Party Level Stuff
235 -- Merge or Transfert
236
237 OPEN c_to_party_profile;
238 FETCH c_to_party_profile INTO l_to_party_rec;
239 IF c_to_party_profile%NOTFOUND THEN
240
241 -- Transfert Party Profile
242 UPDATE hz_customer_profiles
243 SET party_id = p_to_fk_id,
244 last_update_date = hz_utility_pub.last_update_date,
245 last_updated_by = hz_utility_pub.user_id,
246 last_update_login = hz_utility_pub.last_update_login,
247 request_id = hz_utility_pub.request_id,
248 program_application_id = hz_utility_pub.program_application_id,
249 program_id = hz_utility_pub.program_id,
250 program_update_date = sysdate
251 WHERE cust_account_profile_id = l_from_rec.cust_account_profile_id;
252
253 ELSE
254 -- Merge Party Profile
255 x_to_id := l_to_party_rec.cust_account_profile_id;
256
257 IF l_from_rec.status = 'A' OR l_from_rec.status IS NULL THEN
258 do_profile_amt_transf(p_from_id, x_to_id, x_return_status );
259 END IF;
260
261 UPDATE hz_customer_profiles
262 SET status = 'M',
263 last_update_date = hz_utility_pub.last_update_date,
264 last_updated_by = hz_utility_pub.user_id,
265 last_update_login = hz_utility_pub.last_update_login,
266 request_id = hz_utility_pub.request_id,
267 program_application_id = hz_utility_pub.program_application_id,
268 program_id = hz_utility_pub.program_id,
269 program_update_date = sysdate
270 WHERE cust_account_profile_id = l_from_rec.cust_account_profile_id;
271
272 END IF;
273 CLOSE c_to_party_profile;
274
275 END IF;
276
277 END LOOP;
278
279 CLOSE c_from_profile;
280
281 EXCEPTION
282
283 WHEN OTHERS THEN
284
285 FND_MESSAGE.SET_NAME('AR', 'HZ_MERGE_SQL_ERROR');
286 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
287 FND_MSG_PUB.ADD;
288 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
289
290 END;
291
292 END;