1 Package Body IBW_MERGE_PVT As
2 /* $Header: ibwvmrgb.pls 120.3 2006/11/06 14:48:53 pakrishn noship $ */
3
4
5 /*------------------------------------------------------------------------------------------------------------------------*
6 | PUBLIC PROCEDURES |
7 | MERGE_PAGES -- |
8 | API registered to merge, party_id and party_relationship_id in ibw_page_views
9 | These API's will be called when party_id in the HZ_parties will be merged.
10 *--------------------------------------------------------------------------------------------------------------------------*/
11
12
13 PROCEDURE MERGE_PAGES(
14 P_entity_name IN VARCHAR2,
15 P_from_id IN NUMBER,
16 X_to_id OUT NOCOPY NUMBER,
17 P_from_fk_id IN NUMBER,
18 P_to_fk_id IN NUMBER,
19 P_parent_entity_name IN VARCHAR2,
20 P_batch_id IN NUMBER,
21 P_batch_party_id IN NUMBER,
22 X_return_status OUT NOCOPY VARCHAR2
23 ) IS
24
25 l_count NUMBER(10) := 0;
26 l_message_text fnd_new_messages.message_text%type;
27
28
29 RESOURCE_BUSY EXCEPTION;
30 PRAGMA EXCEPTION_INIT(RESOURCE_BUSY, -0054);
31
32 BEGIN
33
34 begin
35 fnd_message.set_name('IBW','IBW_PARTY_MERGE');
36 fnd_message.set_token('DESC',' IBW_PAGE_VIEWS merge started ' );
37 l_message_text := fnd_message.get;
38 fnd_file.put_line(FND_FILE.LOG,l_message_text);
39 fnd_file.put_line(FND_FILE.OUTPUT,l_message_text);
40 end ;
41
42 x_return_status := FND_API.G_RET_STS_SUCCESS;
43
44 /* Perform the merge operation */
45
46 /* If the parent has NOT Changed(i.e Parent is getting transfered), then nothing needs to be done. Set Merge To id same
47 as Merged from id and return */
48
49 if p_from_fk_id = p_to_fk_id then
50 x_to_id := p_from_id;
51 return;
52 End If;
53
54
55 /* If the Parent has changed(i.e. Parent is getting merged), then transfer the dependent record to the new parent. */
56
57 if p_from_fk_id <> p_to_fk_id Then
58 IF p_parent_entity_name = 'HZ_PARTIES' Then
59
60 begin
61 fnd_message.set_name('IBW','IBW_PARTY_MERGE');
62 fnd_message.set_token('DESC',' Updating ibw_page_views : Start' );
63 l_message_text := fnd_message.get;
64 fnd_file.put_line(FND_FILE.LOG,l_message_text);
65 fnd_file.put_line(FND_FILE.OUTPUT,l_message_text);
66 end ;
67
68 /* The below query would be executed when PARTY_ID of type 'ORGANIZATION' or 'PERSON' or 'PARTY RELATIONSHIP' is merged
69 in HZ_PARTIES Table.
70 */
71
72 UPDATE IBW_PAGE_VIEWS SET
73 party_id = DECODE(party_id,p_from_fk_id,p_to_fk_id,party_id),
74 party_relationship_id = DECODE( party_relationship_id,p_from_fk_id,p_to_fk_id, party_relationship_id),
75 visitant_id = case when visitant_id like 'p%' then 'p'||DECODE(party_id,p_from_fk_id,p_to_fk_id,party_id) else visitant_id end, /* Bug 5624186*/
76 last_update_date = hz_utility_pub.last_update_date,
77 last_updated_by = hz_utility_pub.user_id,
78 last_update_login = hz_utility_pub.last_update_login,
79 request_id = hz_utility_pub.request_id,
80 program_application_id = hz_utility_pub.program_application_id,
81 program_id = hz_utility_pub.program_id
82 Where party_id = p_from_fk_id
83 OR party_relationship_id = p_from_fk_id ;
84
85 l_count := sql%rowcount;
86
87
88 begin
89 fnd_message.set_name('IBW','IBW_PARTY_MERGE');
90 fnd_message.set_token('DESC',' IBW_PAGE_VIEWS Rows updated :'||to_char(l_count) );
91 l_message_text := fnd_message.get;
92 fnd_file.put_line(FND_FILE.LOG,l_message_text);
93 fnd_file.put_line(FND_FILE.OUTPUT,l_message_text);
94 end ;
95
96 return;
97 END IF;
98 End If;
99
100 Exception
101 When RESOURCE_BUSY Then
102 begin
103 fnd_message.set_name('IBW','IBW_PARTY_MERGE');
104 fnd_message.set_token('DESC',' IBW_MERGE_PVT.MERGE_PAGES; Could not obtain lock on table IBW_PAGE_VIEWS' );
105 l_message_text := fnd_message.get;
106 fnd_file.put_line(FND_FILE.LOG,l_message_text);
107 end ;
108 x_return_status := FND_API.G_RET_STS_ERROR;
109 raise;
110
111 When Others Then
112
113 begin
114 fnd_message.set_name('IBW','IBW_PARTY_MERGE');
115 fnd_message.set_token('DESC','IBW_MERGE_PVT.MERGE_PAGES : '||sqlerrm );
116 l_message_text := fnd_message.get;
117 fnd_file.put_line(FND_FILE.LOG,l_message_text);
118 end ;
119
120 x_return_status := FND_API.G_RET_STS_ERROR;
121 raise;
122 END MERGE_PAGES;
123
124
125 /*------------------------------------------------------------------------------------------------------------------------*
126 | PUBLIC PROCEDURES |
127 | MERGE_SITES -- |
128 | API registered to merge, party_id in ibw_site_visits
129 | These API's will be called when party_id in the HZ_parties will be merged.
130 *--------------------------------------------------------------------------------------------------------------------------*/
131
132 PROCEDURE MERGE_SITES(
133 P_entity_name IN VARCHAR2,
134 P_from_id IN NUMBER,
135 X_to_id OUT NOCOPY NUMBER,
136 P_from_fk_id IN NUMBER,
137 P_to_fk_id IN NUMBER,
138 P_parent_entity_name IN VARCHAR2,
139 P_batch_id IN NUMBER,
140 P_batch_party_id IN NUMBER,
141 X_return_status OUT NOCOPY VARCHAR2
142 ) IS
143
144 l_merge_reason_code VARCHAR2(30);
145 l_count NUMBER(10) := 0;
146 l_message_text fnd_new_messages.message_text%type;
147
148
149 RESOURCE_BUSY EXCEPTION;
150 PRAGMA EXCEPTION_INIT(RESOURCE_BUSY, -0054);
151
152 BEGIN
153
154 begin
155 fnd_message.set_name('IBW','IBW_PARTY_MERGE');
156 fnd_message.set_token('DESC',' IBW_SITE_VISITS merge started ' );
157 l_message_text := fnd_message.get;
158 fnd_file.put_line(FND_FILE.LOG,l_message_text);
159 fnd_file.put_line(FND_FILE.OUTPUT,l_message_text);
160 end ;
161
162 x_return_status := FND_API.G_RET_STS_SUCCESS;
163
164 /* Perform the merge operation */
165
166 /* If the parent has NOT Changed(i.e Parent is getting transfered), then nothing needs to be done. Set Merge To id same
167 as Merged from id and return */
168
169 if p_from_fk_id = p_to_fk_id then
170 x_to_id := p_from_id;
171 return;
172 End If;
173
174
175 /* If the Parent has changed(i.e. Parent is getting merged), then transfer the dependent record to the new parent. */
176 if p_from_fk_id <> p_to_fk_id Then
177 IF p_parent_entity_name = 'HZ_PARTIES' Then
178
179 begin
180 fnd_message.set_name('IBW','IBW_PARTY_MERGE');
181 fnd_message.set_token('DESC',' Updating ibw_site_visits : Start' );
182 l_message_text := fnd_message.get;
183 fnd_file.put_line(FND_FILE.LOG,l_message_text);
184 fnd_file.put_line(FND_FILE.OUTPUT,l_message_text);
185 end ;
186
187 /* The below query would be executed when PARTY_ID of type 'ORGANIZATION' or 'PERSON' is merged
188 in HZ_PARTIES Table.
189 */
190
191 UPDATE IBW_SITE_VISITS SET
192 party_id = DECODE(party_id,p_from_fk_id,p_to_fk_id,party_id),
193 visitant_id = case when visitant_id like 'p%' then 'p'||DECODE(party_id,p_from_fk_id,p_to_fk_id,party_id) else visitant_id end, /* Bug 5624186*/
194 last_update_date = hz_utility_pub.last_update_date,
195 last_updated_by = hz_utility_pub.user_id,
196 last_update_login = hz_utility_pub.last_update_login,
197 request_id = hz_utility_pub.request_id,
198 program_application_id = hz_utility_pub.program_application_id,
199 program_id = hz_utility_pub.program_id
200 Where party_id = p_from_fk_id ;
201
202 l_count := sql%rowcount;
203
204 begin
205 fnd_message.set_name('IBW','IBW_PARTY_MERGE');
206 fnd_message.set_token('DESC',' IBW_SITE_VISITS Rows updated :'||to_char(l_count) );
207 l_message_text := fnd_message.get;
208 fnd_file.put_line(FND_FILE.LOG,l_message_text);
209 fnd_file.put_line(FND_FILE.OUTPUT,l_message_text);
210 end ;
211
212 return;
213 END IF;
214 End If;
215
216
217 Exception
218 When RESOURCE_BUSY Then
219 begin
220 fnd_message.set_name('IBW','IBW_PARTY_MERGE');
221 fnd_message.set_token('DESC',' IBW_MERGE_PVT.MERGE_SITES; Could not obtain lock on table IBW_SITE_VISITS' );
222 l_message_text := fnd_message.get;
223 fnd_file.put_line(FND_FILE.LOG,l_message_text);
224 end ;
225
226 x_return_status := FND_API.G_RET_STS_ERROR;
227 raise;
228
229 When Others Then
230
231 begin
232 fnd_message.set_name('IBW','IBW_PARTY_MERGE');
233 fnd_message.set_token('DESC','IBW_MERGE_PVT.MERGE_SITES : '||sqlerrm );
234 l_message_text := fnd_message.get;
235 fnd_file.put_line(FND_FILE.LOG,l_message_text);
236 end ;
237 x_return_status := FND_API.G_RET_STS_ERROR;
238
239 begin
240 fnd_message.set_name('IBW','IBW_PARTY_MERGE');
241 fnd_message.set_token('DESC',' IBW_MERGE_PVT. MERGE_SITESsql error ' );
242 l_message_text := fnd_message.get;
243 fnd_file.put_line(FND_FILE.LOG,l_message_text);
244 fnd_file.put_line(FND_FILE.OUTPUT,l_message_text);
245 end ;
246 raise;
247
248 END MERGE_SITES;
249
250
251 END IBW_MERGE_PVT;
[Home] [Help]