1 PACKAGE BODY JTF_NOTES_MERGE_PKG as
2 /* $Header: jtfntmgb.pls 115.5 2002/11/16 00:27:08 hbouten noship $ */
3
4 PROCEDURE MERGE_NOTES
5 ( p_entity_name IN VARCHAR2
6 , p_from_id IN NUMBER
7 , x_to_id OUT NOCOPY NUMBER
8 , p_from_fk_id IN NUMBER
9 , p_to_fk_id IN NUMBER
10 , p_parent_entity_name IN VARCHAR2
11 , p_batch_id IN NUMBER
12 , p_batch_party_id IN NUMBER
13 , x_return_status OUT NOCOPY VARCHAR2
14 )
15 IS
16
17 l_merge_reason_code VARCHAR2(30);
18
19 CURSOR c_duplicate
20 IS SELECT merge_reason_code
21 FROM hz_merge_batch
22 WHERE batch_id = p_batch_id;
23
24
25 BEGIN
26
27 x_return_status := FND_API.G_RET_STS_SUCCESS;
28
29 IF ( (p_entity_name <> 'JTF_NOTES_B')
30 OR (p_parent_entity_name NOT IN ('HZ_PARTIES','HZ_PARTY_SITES'))
31 )
32 THEN
33 FND_MESSAGE.SET_NAME ('JTF', 'NOTES_MSG');
34 FND_MSG_PUB.ADD;
35 RAISE FND_API.G_EXC_ERROR;
36 END IF;
37
38
39 OPEN c_duplicate;
40 FETCH c_duplicate INTO l_merge_reason_code;
41 CLOSE c_duplicate;
42
43 IF l_merge_reason_code <> 'DUPLICATE'
44 THEN
45 -- if there are any validations to be done, include it in this section
46 -- if reason code is duplicate then allow the party merge to happen without
47 -- any validations.
48 NULL;
49 END IF;
50
51 -- perform the merge operation
52 -- if the parent has NOT changed(i.e. parent getting transferred) then nothing
53 -- needs to be done. Set merged to id (x_to_id) the same as merged from id and return
54
55 IF p_from_fk_id = p_to_fk_id
56 THEN
57 x_to_id := p_from_id;
58 RETURN;
59 END IF;
60
61 -- If the parent has changed(ie. Parent is getting merged) then transfer the
62 -- dependent record to the new parent.
63 -- For example in JTF_NOTES_B table, if party_id 1 got merged to party_id 2
64 -- then, we have to update all records with new_source_object_id 1 to 2
65 -- p_to_fk_id is the new value which has to be put
66 -- p_from_id is the jtf_note_id
67 IF ( (P_FROM_FK_ID <> P_TO_FK_ID)
68 AND (p_parent_entity_name = 'HZ_PARTIES')
69 )
70 THEN
71 UPDATE jtf_notes_b
72 SET source_object_id = p_to_fk_id
73 , last_update_date = hz_utility_pub.last_update_date
74 , last_updated_by = hz_utility_pub.user_id
75 , last_update_login = hz_utility_pub.last_update_login
76 WHERE source_object_code IN (SELECT ojt.object_code
77 FROM jtf_objects_b ojt
78 , jtf_object_usages oue
79 WHERE ojt.object_code = oue.object_code
80 AND oue.object_user_code = 'NOTES'
81 AND ojt.from_table ='HZ_PARTIES'
82 )
83 AND source_object_id = p_from_fk_id;
84
85 ELSIF ( (P_FROM_FK_ID <> P_TO_FK_ID)
86 AND (p_parent_entity_name = 'HZ_PARTY_SITES')
87 )
88 THEN
89 UPDATE jtf_notes_b
90 SET source_object_id = p_to_fk_id
91 , last_update_date = hz_utility_pub.last_update_date
92 , last_updated_by = hz_utility_pub.user_id
93 , last_update_login = hz_utility_pub.last_update_login
94 WHERE source_object_code IN (SELECT ojt.object_code
95 FROM jtf_objects_b ojt
96 , jtf_object_usages oue
97 WHERE ojt.object_code = oue.object_code
98 AND oue.object_user_code = 'NOTES'
99 AND ojt.from_table ='HZ_PARTY_SITES'
100 )
101 AND source_object_id = p_from_fk_id;
102 END IF;
103
104 EXCEPTION
105 WHEN OTHERS
106 THEN
107 IF (c_duplicate%ISOPEN)
108 THEN
109 CLOSE c_duplicate;
110 END IF;
111 FND_MESSAGE.SET_NAME('AR', 'HZ_API_OTHERS_EXCEP');
112 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
113 FND_MSG_PUB.ADD;
114 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
115 x_to_id := NULL;
116
117 END MERGE_NOTES;
118
119 PROCEDURE MERGE_CONTEXT
120 ( p_entity_name IN VARCHAR2
121 , p_from_id IN NUMBER
122 , x_to_id OUT NOCOPY NUMBER
123 , p_from_fk_id IN NUMBER
124 , p_to_fk_id IN NUMBER
125 , p_parent_entity_name IN VARCHAR2
126 , p_batch_id IN NUMBER
127 , p_batch_party_id IN NUMBER
128 , x_return_status OUT NOCOPY VARCHAR2
129 )
130 IS
131
132 l_merge_reason_code VARCHAR2(30);
133
134 CURSOR c_duplicate
135 IS SELECT merge_reason_code
136 FROM hz_merge_batch
137 WHERE batch_id = p_batch_id;
138
139
140 BEGIN
141
142 x_return_status := FND_API.G_RET_STS_SUCCESS;
143
144 IF ( (p_entity_name <> 'JTF_NOTE_CONTEXTS')
145 OR (p_parent_entity_name NOT IN ('HZ_PARTIES','HZ_PARTY_SITES'))
146 )
147 THEN
148 FND_MESSAGE.SET_NAME('JTF', 'NOTES_MSG');
149 FND_MSG_PUB.ADD;
150 RAISE FND_API.G_EXC_ERROR;
151 END IF;
152
153 OPEN c_duplicate;
154 FETCH c_duplicate INTO l_merge_reason_code;
155 CLOSE c_duplicate;
156
157 IF l_merge_reason_code <> 'DUPLICATE'
158 THEN
159 -- if there are any validations to be done, include it in this section
160 -- if reason code is duplicate then allow the party merge to happen without
161 -- any validations.
162 NULL;
163 END IF;
164
165 -- perform the merge operation
166 -- if the parent has NOT changed(i.e. parent getting transferred) then
167 -- nothing needs to be done. Set merged to id (x_to_id) the same as merged
168 -- from id and return
169
170 IF p_from_fk_id = p_to_fk_id
171 THEN
172 x_to_id := p_from_id;
173 RETURN;
174 END IF;
175
176 -- If the parent has changed(ie. Parent is getting merged) then transfer the
177 -- dependent record to the new parent.
178 -- For example in JTF_NOTES_B table, if party_id 1 got merged to party_id 2
179 -- then, we have to update all records with new_source_object_id 1 to 2
180 -- p_to_fk_id is the new value which has to be put
181 -- p_from_id is the jtf_note_id
182
183 IF ( (p_from_fk_id <> p_to_fk_id)
184 AND (p_parent_entity_name = 'HZ_PARTIES')
185 )
186 THEN
187 UPDATE jtf_note_contexts
188 SET note_context_type_id = p_to_fk_id
189 , last_update_date = hz_utility_pub.last_update_date
190 , last_updated_by = hz_utility_pub.user_id
191 , last_update_login = hz_utility_pub.last_update_login
192 WHERE note_context_type IN (SELECT ojt.object_code
193 FROM jtf_objects_b ojt
194 , jtf_object_usages oue
195 WHERE ojt.object_code = oue.object_code
196 AND oue.object_user_code = 'NOTES'
197 AND ojt.from_table ='HZ_PARTIES'
198 )
199 AND note_context_type_id = p_from_fk_id;
200
201 ELSIF ( (p_from_fk_id <> p_to_fk_id)
202 AND (p_parent_entity_name = 'HZ_PARTY_SITES')
203 )
204 THEN
205 UPDATE jtf_note_contexts
206 SET note_context_type_id = p_to_fk_id
207 , last_update_date = hz_utility_pub.last_update_date
208 , last_updated_by = hz_utility_pub.user_id
209 , last_update_login = hz_utility_pub.last_update_login
210 WHERE note_context_type IN (SELECT ojt.object_code
211 FROM jtf_objects_b ojt
212 , jtf_object_usages oue
213 WHERE ojt.object_code = oue.object_code
214 AND oue.object_user_code = 'NOTES'
215 AND ojt.from_table ='HZ_PARTY_SITES'
216 )
217 AND note_context_type_id = p_from_fk_id;
218 END IF;
219
220 EXCEPTION
221 WHEN OTHERS
222 THEN
223 IF (c_duplicate%ISOPEN)
224 THEN
225 CLOSE c_duplicate;
226 END IF;
227
228 FND_MESSAGE.SET_NAME('AR', 'HZ_API_OTHERS_EXCEP');
229 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
230 FND_MSG_PUB.ADD;
231 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
232 x_to_id := NULL;
233
234 END MERGE_CONTEXT;
235
236
237 END JTF_NOTES_MERGE_PKG;
238
[Home] [Help]