[Home] [Help]
PACKAGE BODY: APPS.FA_VENDORMERGE_GRP
Source
1 PACKAGE BODY FA_VendorMerge_GRP AS
2 /* $Header: FAPVDMGB.pls 120.1 2005/08/30 06:06:32 tkawamur noship $ */
3
4 --
5 -- Global Constant Variables
6 --
7 G_PKG_NAME CONSTANT varchar2(30) := 'FA_VendorMerge_GRP';
8 G_API_NAME CONSTANT varchar2(30) := 'Vendor_Merge';
9 G_API_VERSION CONSTANT number := 1.0;
10
11 g_log_level_rec fa_api_types.log_level_rec_type;
12
13 PROCEDURE Merge_Vendor(
14 p_api_version IN NUMBER
15 , p_init_msg_list IN VARCHAR2 default FND_API.G_FALSE
16 , p_commit IN VARCHAR2 default FND_API.G_FALSE
17 , p_validation_level IN NUMBER default FND_API.G_VALID_LEVEL_FULL
18 , x_return_status OUT NOCOPY VARCHAR2
19 , x_msg_count OUT NOCOPY NUMBER
20 , x_msg_data OUT NOCOPY VARCHAR2
21 , p_vendor_id IN NUMBER
22 , p_dup_vendor_id IN NUMBER
23 , p_vendor_site_id IN NUMBER
24 , p_dup_vendor_site_id IN NUMBER
25 , p_party_id IN NUMBER
26 , p_dup_party_id IN NUMBER
27 , p_party_site_id IN NUMBER
28 , p_dup_party_site_id IN NUMBER
29 , p_segment1 IN VARCHAR2 -- Vendor Number
30 , p_dup_segment1 IN VARCHAR2
31 , p_vendor_name IN VARCHAR2 default NULL
32 ) IS
33
34
35 l_calling_fn varchar2(50) := 'FA_VendorMerge_GRP.Merge_Vendor';
36 l_location varchar2(50);
37 l_row_count binary_integer;
38 --
39 -- This can be removed if parameters for vendor name are added
40 --
41 l_vendor_name varchar2(240);
42
43 CURSOR c_get_vendor_name IS
44 select vendor_name
45 from po_vendors
46 where vendor_id = p_vendor_id;
47 --
48 --
49 --
50
51 mrg_err exception;
52
53
54 BEGIN
55 if (g_log_level_rec.statement_level) then
56 fa_debug_pkg.add(l_calling_fn, 'BEGIN', to_char(p_vendor_id)||':'||to_char(p_dup_vendor_id)||':'||
57 to_char(p_vendor_site_id)||':'||to_char(p_dup_vendor_site_id),
58 p_log_level_rec => g_log_level_rec);
59 end if;
60
61 SAVEPOINT sp_vendor_merge;
62
63 if (not g_log_level_rec.initialized) then
64 if (NOT fa_util_pub.get_log_level_rec (
65 x_log_level_rec => g_log_level_rec)) then
66
67 if (g_log_level_rec.statement_level) then
68 fa_debug_pkg.add(l_calling_fn, 'Failed calling', 'fa_util_pub.get_log_level_rec',
69 p_log_level_rec => g_log_level_rec);
70 end if;
71
72 raise mrg_err;
73 end if;
74 end if;
75
76 l_row_count := 0;
77
78 l_location := 'Calling fnd_api.compatible_api_call';
79 -- Check version of the API
80 -- Standard call to check for API call compatibility.
81 if NOT fnd_api.compatible_api_call (
82 G_API_VERSION,
83 p_api_version,
84 G_API_NAME,
85 G_PKG_NAME) then
86 if (g_log_level_rec.statement_level) then
87 fa_debug_pkg.add(l_calling_fn, 'Failed calling', 'fnd_api.compatible_api_call',
88 p_log_level_rec => g_log_level_rec);
89 end if;
90
91 raise mrg_err;
92 end if;
93
94
95 --
96 -- This can be removed if parameters for vendor name are added
97 --
98 if (p_vendor_name is null) then
99 l_location := 'Getting vendor info';
100 open c_get_vendor_name;
101 fetch c_get_vendor_name INTO l_vendor_name;
102
103 if c_get_vendor_name%notfound then
104 close c_get_vendor_name;
105
106 if (g_log_level_rec.statement_level) then
107 fa_debug_pkg.add(l_calling_fn, 'Failed getting', 'vendor informatioin',
108 p_log_level_rec => g_log_level_rec);
109 end if;
110
111 raise mrg_err;
112 end if;
113
114 close c_get_vendor_name;
115 else
116 l_vendor_name := p_vendor_name;
117 end if;
118 --
119 --
120 --
121
122 if (g_log_level_rec.statement_level) then
123 fa_debug_pkg.add(l_calling_fn, 'Start Updating', 'processing...',
124 p_log_level_rec => g_log_level_rec);
125 end if;
126
127 -- **********************************************************
128 -- Updating FA_INS_LINES : VENDOR_ID
129 -- **********************************************************
130 l_location := 'FA_INS_LINES';
131 update FA_INS_LINES
132 set VENDOR_ID = p_vendor_id
133 where VENDOR_ID = p_dup_vendor_id;
134
135 -- **********************************************************
136 -- Updating FA_INS_MST_POLS : VENDOR_ID, VENDOR_SITE_ID
137 -- **********************************************************
138 l_location := 'FA_INS_MST_POLS';
139 update FA_INS_MST_POLS
140 set VENDOR_ID = p_vendor_id
141 , VENDOR_SITE_ID = decode(VENDOR_SITE_ID, p_dup_vendor_site_id, p_vendor_site_id, VENDOR_SITE_ID)
142 where VENDOR_ID = p_dup_vendor_id
143 and nvl(VENDOR_SITE_ID, 0) = decode(VENDOR_SITE_ID, NULL, 0, p_dup_vendor_site_id);
144
145 -- **********************************************************
146 -- Updating FA_INS_POLICIES : VENDOR_ID, VENDOR_SITE_ID
147 -- **********************************************************
148 l_location := 'FA_INS_POLICIES';
149 update FA_INS_POLICIES
150 set VENDOR_ID = p_vendor_id
151 , VENDOR_SITE_ID = decode(VENDOR_SITE_ID, p_dup_vendor_site_id, p_vendor_site_id, VENDOR_SITE_ID)
152 where VENDOR_ID = p_dup_vendor_id
153 and nvl(VENDOR_SITE_ID, 0) = decode(VENDOR_SITE_ID, NULL, 0, p_dup_vendor_site_id);
154
155 -- **********************************************************
156 -- Updating FA_INS_VALUES : VENDOR_ID
157 -- **********************************************************
158 l_location := 'FA_INS_VALUES';
159 update FA_INS_VALUES
160 set VENDOR_ID = p_vendor_id
161 where VENDOR_ID = p_dup_vendor_id;
162
163 -- **********************************************************
164 -- Updating FA_MAINT_EVENTS : VENDOR_ID
165 -- **********************************************************
166 l_location := 'FA_MAINT_EVENTS';
167 update FA_MAINT_EVENTS
168 set VENDOR_ID = p_vendor_id
169 where VENDOR_ID = p_dup_vendor_id;
170
171 -- **********************************************************
172 -- Updating FA_MAINT_SCHEDULE_DTL : VENDOR_ID, VENDOR_NAME
173 -- **********************************************************
174 l_location := 'FA_MAINT_SCHEDULE_DTL';
175 update FA_MAINT_SCHEDULE_DTL
176 set VENDOR_ID = p_vendor_id
177 , VENDOR_NAME = decode(VENDOR_NAME, NULL, NULL, l_vendor_name)
178 , VENDOR_NUMBER = decode(VENDOR_NUMBER, NULL, NULL, p_segment1)
179 where VENDOR_ID = p_dup_vendor_id;
180
181 -- **********************************************************
182 -- Updating FA_WARRANTIES : PO_VENDOR_ID
183 -- **********************************************************
184 l_location := 'FA_WARRANTIES';
185 update FA_WARRANTIES
186 set PO_VENDOR_ID = p_vendor_id
187 where PO_VENDOR_ID = p_dup_vendor_id;
188
189 -- **********************************************************
190 -- Updating FA_LEASES : LESSOR_ID, LESSOR_SITE_ID
191 -- **********************************************************
192 l_location := 'FA_LEASES';
193 update FA_LEASES
194 set LESSOR_ID = p_vendor_id
195 , LESSOR_SITE_ID = decode(LESSOR_SITE_ID, p_dup_vendor_site_id, p_vendor_site_id, LESSOR_SITE_ID)
196 where LESSOR_ID = p_dup_vendor_id
197 and nvl(LESSOR_SITE_ID, 0) = decode(LESSOR_SITE_ID, NULL, 0, p_dup_vendor_site_id);
198
199 -- **********************************************************
200 -- Updating FA_LEASE_PAYMENT_ITEMS: LESSOR_ID, LESSOR_SITE_ID
201 -- **********************************************************
202 l_location := 'FA_LEASE_PAYMENT_ITEMS';
203 update FA_LEASE_PAYMENT_ITEMS
204 set LESSOR_ID = p_vendor_id
205 , LESSOR_SITE_ID = p_vendor_site_id
206 where LESSOR_ID = p_dup_vendor_id
207 and LESSOR_SITE_ID = p_dup_vendor_site_id;
208
209 -- **********************************************************
210 -- Updating FA_ASSET_INVOICES : PO_VENDOR_ID
211 -- **********************************************************
212 l_location := 'FA_ASSET_INVOICES';
213 update FA_ASSET_INVOICES
214 set PO_VENDOR_ID = p_vendor_id
215 where PO_VENDOR_ID = p_dup_vendor_id;
216
217 -- **********************************************************
218 -- Updating FA_MC_ASSET_INVOICES : PO_VENDOR_ID
219 -- **********************************************************
220 l_location := 'FA_MC_ASSET_INVOICES';
221 update FA_MC_ASSET_INVOICES
222 set PO_VENDOR_ID = p_vendor_id
223 where PO_VENDOR_ID = p_dup_vendor_id;
224
225 -- **********************************************************
226 -- Updating FA_MASS_ADDITIONS : PO_VENDOR_ID, LESSOR_ID, VENDOR_NUMBER
227 -- **********************************************************
228 l_location := 'FA_MASS_ADDITIONS';
229 update FA_MASS_ADDITIONS
230 set PO_VENDOR_ID = decode(PO_VENDOR_ID, p_dup_vendor_id, p_vendor_id, PO_VENDOR_ID)
231 , LESSOR_ID = decode(LESSOR_ID, p_dup_vendor_id, p_vendor_id, LESSOR_ID)
232 , VENDOR_NUMBER = decode(PO_VENDOR_ID, p_dup_vendor_id,
233 decode(VENDOR_NUMBER, NULL, NULL, p_segment1),
234 VENDOR_NUMBER)
235 where (PO_VENDOR_ID = p_dup_vendor_id
236 or LESSOR_ID = p_dup_vendor_id);
237
238
239 -- **********************************************************
240 --
241 -- ITF(RXi) tables
242 --
243 -- **********************************************************
244
245 --
246 -- Updating FA_ADDITION_REP_ITF : VENDOR_NUMBER
247 --
248 l_location := 'FA_ADDITION_REP_ITF';
249 update FA_ADDITION_REP_ITF
250 set VENDOR_NUMBER = p_segment1
251 where VENDOR_NUMBER = p_dup_segment1;
252
253 --
254 -- Updating FA_MAINT_REP_ITF : VENDOR_NAME, VENDOR_NUMBER
255 --
256 l_location := 'FA_MAINT_REP_ITF';
257 update FA_MAINT_REP_ITF
258 set VENDOR_NAME = l_vendor_name
259 , VENDOR_NUMBER = p_segment1
260 where VENDOR_NUMBER = p_dup_segment1;
261
262 --
263 -- Updating FA_MASSADD_REP_ITF : VENDOR_NAME, VENDOR_NUMBER
264 --
265 l_location := 'FA_MASSADD_REP_ITF';
266 update FA_MASSADD_REP_ITF
267 set VENDOR_NAME = l_vendor_name
268 , VENDOR_NUMBER = p_segment1
269 where VENDOR_NUMBER = p_dup_segment1;
270
271
272 -- Commenting out as this is not necessary
273 -- if FND_API.to_boolean(p_commit) then
274 -- COMMIT;
275 -- end if;
276
277 x_return_status := FND_API.G_RET_STS_SUCCESS;
278
279 if (g_log_level_rec.statement_level) then
280 fa_debug_pkg.add(l_calling_fn, 'End', 'SUCCESS',
281 p_log_level_rec => g_log_level_rec);
282 end if;
283
284 EXCEPTION
285 WHEN mrg_err THEN
286 ROLLBACK TO sp_vendor_merge;
287
288 fa_srvr_msg.add_message(calling_fn => l_calling_fn,
289 p_log_level_rec => g_log_level_rec);
290 FND_MSG_PUB.count_and_get(p_count => x_msg_count,
291 p_data => x_msg_data);
292 x_return_status := FND_API.G_RET_STS_ERROR;
293
294 WHEN OTHERS THEN
295 ROLLBACK TO sp_vendor_merge;
296
297 if (g_log_level_rec.statement_level) then
298 fa_debug_pkg.add(l_calling_fn, 'EXCEPTION(OTHERS)', l_location,
299 p_log_level_rec => g_log_level_rec);
300 end if;
301
302 fa_srvr_msg.add_message(calling_fn => l_calling_fn,
303 name => 'FA_SHARED_ACTION_TABLE',
304 token1 => 'ACTION',
305 value1 => 'Update',
306 token2 => 'TABLE',
307 value2 => l_location,
308 p_log_level_rec => g_log_level_rec);
309
310 fa_srvr_msg.add_sql_error(calling_fn => l_calling_fn,
311 p_log_level_rec => g_log_level_rec);
312
313 FND_MSG_PUB.count_and_get(p_count => x_msg_count,
314 p_data => x_msg_data);
315 x_return_status := FND_API.G_RET_STS_ERROR;
316
317 END Merge_Vendor;
318
319 END FA_VENDORMERGE_GRP;