1 PACKAGE BODY PRP_PARTY_MERGE_PVT AS
2 /* $Header: PRPVPMGB.pls 115.0 2003/04/02 00:12:51 vpalaiya noship $ */
3
4 --
5 -- Start of Comments
6 --
7 -- NAME
8 -- PRP_PARTY_MERGE_PVT
9 --
10 -- PURPOSE
11 --
12 -- NOTES
13 --
14 --+
15
16 G_PKG_NAME CONSTANT VARCHAR2(30):='PRP_PARTY_MERGE_PVT';
17 G_FILE_NAME CONSTANT VARCHAR2(12):='PRPVPMGB.pls';
18
19 ----------------------------------------------------------------------------
20 -- PUBLIC PROCEDURES
21 -- Merge_Proposals
22 -- When in ERP Parties are merged, the foriegn keys to party_id and other
23 -- columns should also be updated in Proposal tables. This procedure will
24 -- update PRP_PROPOSALS table and will be called from party merge
25 -- concurrent program.
26 --
27 -- DESCRIPTION
28 --
29 -- REQUIRES
30 --
31 -- EXCEPTIONS RAISED
32 --
33 -- KNOWN BUGS
34 --
35 -- NOTES
36 --
37 -- HISTORY
38 --
39 ----------------------------------------------------------------------------+
40 PROCEDURE Merge_Proposals
41 (
42 p_entity_name IN VARCHAR2,
43 p_from_id IN NUMBER,
44 x_to_id OUT NOCOPY NUMBER,
45 p_from_fk_id IN NUMBER,
46 p_to_fk_id IN NUMBER,
47 p_parent_entity_name IN VARCHAR2,
48 p_batch_id IN NUMBER,
49 p_batch_party_id IN NUMBER,
50 x_return_status OUT NOCOPY VARCHAR2
51 )
52 IS
53 l_api_name CONSTANT VARCHAR2(30) :=
54 'Merge_Proposals';
55 l_api_version CONSTANT NUMBER := 1.0;
56 l_count NUMBER(10) := 0;
57
58 -- Get all the rows from PRP_PROPOSALS that are to be merged and lock them
59 CURSOR c1 IS SELECT 1 FROM prp_proposals
60 WHERE party_id = p_from_fk_id
61 OR contact_party_id = p_from_fk_id
62 FOR UPDATE NOWAIT;
63
64 BEGIN
65
66 -- Log message
67 arp_message.set_line(G_PKG_NAME || '.' || l_api_name || '()+');
68
69 -- Initialize API return status to success
70 x_return_status := FND_API.G_RET_STS_SUCCESS;
71
72 --
73 -- Do All Validations
74 --
75
76 -- Check the Merge Reason code. If the merge reason is duplicate record,
77 -- then no validation is required. Otherwise do the required validations.
78
79 -- Commenting this section for now as we are not doing any validations,
80 -- if the reason is not 'Duplicate Record'. In future if we need any
81 -- validations we can uncomment this sections and add validations.
82
83 -- SELECT merge_reason_code INTO l_merge_reason_code
84 -- FROM hz_merge_batch
85 -- WHERE batch_id = p_batch_id;
86
87 -- IF l_merge_reason_code = 'DUPLICATE' THEN
88 -- NULL;
89 -- ELSE
90 -- NULL;
91 -- END IF;
92
93 --
94 -- Perform the merge operation
95 --
96
97 -- If the parent has not changed (i.e. parent is getting transfered),
98 -- then nothing needs to be done. Set Merge To id same as Merged From id
99 -- and return.
100 IF p_from_fk_id = p_to_fk_id THEN
101 x_to_id := p_from_id;
102 RETURN;
103 END IF;
104
105 --
106 -- If the parent has changed (i.e. parent is getting merged), then transfer
107 -- the dependent record to the new parent.
108 --
109 IF p_from_fk_id <> p_to_fk_id THEN
110
111 IF p_parent_entity_name = 'HZ_PARTIES' THEN
112
113 -- Log message
114 arp_message.set_name('AR', 'AR_LOCKING_TABLE');
115 arp_message.set_token('TABLE_NAME', 'PRP_PROPOSALS', FALSE);
116
117 -- Lock table
118 OPEN C1;
119 CLOSE C1;
120
121 -- Log message
122 arp_message.set_name('AR', 'AR_UPDATING_TABLE');
123 arp_message.set_token('TABLE_NAME','PRP_PROPOSALS', FALSE);
124
125 -- Update table
126 UPDATE prp_proposals SET
127 party_id = DECODE(party_id, p_from_fk_id, p_to_fk_id, party_id),
128 contact_party_id = DECODE(contact_party_id, p_from_fk_id, p_to_fk_id,
129 contact_party_id),
130 last_update_date = hz_utility_pub.last_update_date,
131 last_updated_by = hz_utility_pub.user_id,
132 last_update_login = hz_utility_pub.last_update_login,
133 program_id = hz_utility_pub.program_id,
134 program_login_id = hz_utility_pub.last_update_login,
135 program_application_id = hz_utility_pub.program_application_id,
136 request_id = hz_utility_pub.request_id
137 WHERE party_id = p_from_fk_id
138 OR contact_party_id = p_from_fk_id;
139
140 -- Get the row count
141 l_count := sql%rowcount;
142
143 -- Log message
144 arp_message.set_name('AR', 'AR_ROWS_UPDATED');
145 arp_message.set_token('NUM_ROWS', to_char(l_count));
146
147 END IF;
148
149 END IF;
150
151 -- Log message
152 arp_message.set_line(G_PKG_NAME || '.' || l_api_name || '()-');
153
154 EXCEPTION
155
156 WHEN OTHERS THEN
157 arp_message.set_line
158 (
159 G_PKG_NAME || '.' || l_api_name || '():'
160 || 'sqlerrm=' || SQLERRM || ','
161 || 'sqlcode=' || SQLCODE
162 );
163 x_return_status := FND_API.G_RET_STS_ERROR;
164 RAISE;
165
166 END Merge_Proposals;
167
168 ----------------------------------------------------------------------------
169 -- PUBLIC PROCEDURES
170 -- Merge_Email_Recipients
171 -- When in ERP Parties are merged, the foriegn keys to party_id and other
172 -- columns should also be updated in Proposal tables. This procedure will
173 -- update PRP_EMAIL_RECIPIENTS table and will be called from party merge
174 -- concurrent program.
175 --
176 -- DESCRIPTION
177 --
178 -- REQUIRES
179 --
180 -- EXCEPTIONS RAISED
181 --
182 -- KNOWN BUGS
183 --
184 -- NOTES
185 --
186 -- HISTORY
187 --
188 ----------------------------------------------------------------------------+
189 PROCEDURE Merge_Email_Recipients
190 (
191 p_entity_name IN VARCHAR2,
192 p_from_id IN NUMBER,
193 x_to_id OUT NOCOPY NUMBER,
194 p_from_fk_id IN NUMBER,
195 p_to_fk_id IN NUMBER,
196 p_parent_entity_name IN VARCHAR2,
197 p_batch_id IN NUMBER,
198 p_batch_party_id IN NUMBER,
199 x_return_status OUT NOCOPY VARCHAR2
200 )
201 IS
202 l_api_name CONSTANT VARCHAR2(30) :=
203 'Merge_Email_Recipients';
204 l_api_version CONSTANT NUMBER := 1.0;
205 l_count NUMBER(10) := 0;
206
207 -- Get all the rows from PRP_EMAIL_RECIPIENTS that are to be merged
208 -- and lock them
209 CURSOR c1 IS SELECT 1 FROM prp_email_recipients
210 WHERE party_id = p_from_fk_id
211 FOR UPDATE NOWAIT;
212
213 BEGIN
214
215 -- Log message
216 arp_message.set_line(G_PKG_NAME || '.' || l_api_name || '()+');
217
218 -- Initialize API return status to success
219 x_return_status := FND_API.G_RET_STS_SUCCESS;
220
221 --
222 -- Do All Validations
223 --
224
225 -- Check the Merge Reason code. If the merge reason is duplicate record,
226 -- then no validation is required. Otherwise do the required validations.
227
228 -- Commenting this section for now as we are not doing any validations,
229 -- if the reason is not 'Duplicate Record'. In future if we need any
230 -- validations we can uncomment this sections and add validations.
231
232 -- SELECT merge_reason_code INTO l_merge_reason_code
233 -- FROM hz_merge_batch
234 -- WHERE batch_id = p_batch_id;
235
236 -- IF l_merge_reason_code = 'DUPLICATE' THEN
237 -- NULL;
238 -- ELSE
239 -- NULL;
240 -- END IF;
241
242 --
243 -- Perform the merge operation
244 --
245
246 -- If the parent has not changed (i.e. parent is getting transfered),
247 -- then nothing needs to be done. Set Merge To id same as Merged From id
248 -- and return.
249 IF p_from_fk_id = p_to_fk_id THEN
250 x_to_id := p_from_id;
251 RETURN;
252 END IF;
253
254 --
255 -- If the parent has changed (i.e. parent is getting merged), then transfer
256 -- the dependent record to the new parent.
257 --
258 IF p_from_fk_id <> p_to_fk_id THEN
259
260 IF p_parent_entity_name = 'HZ_PARTIES' THEN
261
262 -- Log message
263 arp_message.set_name('AR', 'AR_LOCKING_TABLE');
264 arp_message.set_token('TABLE_NAME', 'PRP_EMAIL_RECIPIENTS', FALSE);
265
266 -- Lock table
267 OPEN C1;
268 CLOSE C1;
269
270 -- Log message
271 arp_message.set_name('AR', 'AR_UPDATING_TABLE');
272 arp_message.set_token('TABLE_NAME','PRP_EMAIL_RECIPIENTS', FALSE);
273
274 -- Update table
275 UPDATE prp_email_recipients SET
276 party_id = p_to_fk_id,
277 last_update_date = hz_utility_pub.last_update_date,
278 last_updated_by = hz_utility_pub.user_id,
279 last_update_login = hz_utility_pub.last_update_login,
280 program_id = hz_utility_pub.program_id,
281 program_login_id = hz_utility_pub.last_update_login,
282 program_application_id = hz_utility_pub.program_application_id,
283 request_id = hz_utility_pub.request_id
284 WHERE party_id = p_from_fk_id;
285
286 -- Get the row count
287 l_count := sql%rowcount;
288
289 -- Log message
290 arp_message.set_name('AR', 'AR_ROWS_UPDATED');
291 arp_message.set_token('NUM_ROWS', to_char(l_count));
292
293 END IF;
294
295 END IF;
296
297 -- Log message
298 arp_message.set_line(G_PKG_NAME || '.' || l_api_name || '()-');
299
300 EXCEPTION
301
302 WHEN OTHERS THEN
303 arp_message.set_line
304 (
305 G_PKG_NAME || '.' || l_api_name || '():'
306 || 'sqlerrm=' || SQLERRM || ','
307 || 'sqlcode=' || SQLCODE
308 );
309 x_return_status := FND_API.G_RET_STS_ERROR;
310 RAISE;
311
312 END Merge_Email_Recipients;
313
314 END PRP_PARTY_MERGE_PVT;
[Home] [Help]