1 package body pos_sup_prof_mrg_grp as
2 /* $Header: POSSPMGB.pls 120.14 2011/12/12 15:02:22 nchundur ship $ */
3
4 pos_merge_exception EXCEPTION;
5
6 g_module_prefix VARCHAR2(35) := 'POS.plsql.POS_SUP_PROF_MRG_GRP.';
7
8
9 FUNCTION GET_ACTIVE_SITE_COUNT (p_dup_vendor_id IN NUMBER,
10 p_dup_vendor_site_id IN NUMBER)
11 RETURN NUMBER;
12
13
14 PROCEDURE log_fnd_message_stack
15 (p_module IN VARCHAR2,
16 p_return_status IN VARCHAR2,
17 p_msg_count IN NUMBER,
18 p_msg_data IN VARCHAR2)
19 IS
20 l_msg fnd_log_messages.message_text%TYPE;
21 BEGIN
22 IF ( fnd_log.level_error >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
23 fnd_log.string(fnd_log.level_error, p_module, 'return status is ' || p_return_status);
24 END IF;
25 IF p_msg_count = 1 THEN
26 IF ( fnd_log.level_error >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
27 fnd_log.string(fnd_log.level_error, p_module, p_msg_data);
28 END IF;
29 ELSE
30 FOR i IN 1..p_msg_count LOOP
31 l_msg := fnd_msg_pub.get(p_encoded => fnd_api.g_false);
32 IF ( fnd_log.level_error >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
33 fnd_log.string(fnd_log.level_error, p_module, l_msg);
34 END IF;
35 END LOOP;
36 END IF;
37 END log_fnd_message_stack;
38
39 -- v12 Only updating securing attributes, everything else should be
40 -- handled by AP or TCA
41
42 procedure handle_merge
43 (p_new_vendor_id IN NUMBER,
44 p_new_vendor_site_id IN NUMBER,
45 p_old_vendor_id IN NUMBER,
46 p_old_vendor_site_id IN NUMBER,
47 x_return_status OUT NOCOPY VARCHAR2 )
48 IS
49 l_module VARCHAR2(31);
50 l_return_status VARCHAR2(2) := fnd_api.g_ret_sts_success; -- Initialize return status
51 l_num_active_sites NUMBER;
52
53 BEGIN
54 SAVEPOINT pos_handle_merge;
55
56 l_module := 'handle_merge';
57
58 IF ( fnd_log.level_procedure >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
59 fnd_log.string(fnd_log.level_procedure, g_module_prefix || l_module,
60 '1.Begin handle_merge:'||
61 'new vendorid:'|| p_new_vendor_id||
62 ':new vendor site id:' || p_new_vendor_site_id ||
63 ':old vendor id :'|| p_old_vendor_id ||
64 ':old vendor site id:'|| p_old_vendor_site_id);
65 END IF;
66
67 l_num_active_sites := GET_ACTIVE_SITE_COUNT(p_old_vendor_id, p_old_vendor_site_id);
68
69 -- V12, iSP is no longer maintaining TCA, so the only thing to do is
70 -- maintaining securing attributes
71
72 fnd_file.put_line(fnd_file.log,
73 '1.Begin handle_merge:' || 'new vendorid:' ||
74 p_new_vendor_id || ':new vendor site id:' ||
75 p_new_vendor_site_id || ':old vendor id :' ||
76 p_old_vendor_id || ':old vendor site id:' ||
77 p_old_vendor_site_id);
78
79
80 IF (p_new_vendor_site_id <> p_old_vendor_site_id) THEN
81
82 -- Merge site
83 -- Bugfix#12817586 check for unique constraint violation before while updating
84 UPDATE ak_web_user_sec_attr_values ak1 SET ak1.number_value = p_new_vendor_site_id
85 WHERE ak1.attribute_code = 'ICX_SUPPLIER_SITE_ID' AND ak1.attribute_application_id = 177
86 AND ak1.number_value = p_old_vendor_site_id
87 AND NOT EXISTS
88 (SELECT 'DUPLICATE'
89 FROM ak_web_user_sec_attr_values ak2
90 WHERE ak2.web_user_id = ak1.web_user_id
91 AND ak2.attribute_code = 'ICX_SUPPLIER_SITE_ID'
92 AND ak2.attribute_application_id = 177
93 AND ak2.number_value = p_new_vendor_site_id
94 );
95 --End bugfix#12817586
96 -- If it is the last site then merge vendor
97 IF (l_num_active_sites = 0) THEN
98 UPDATE ak_web_user_sec_attr_values
99 SET number_value = p_new_vendor_id
100 WHERE attribute_code = 'ICX_SUPPLIER_ORG_ID'
101 AND attribute_application_id = 177
102 AND number_value = p_old_vendor_id;
103
104 UPDATE fnd_registration_details
105 SET field_value_string = (SELECT vendor_name FROM po_vendors WHERE vendor_id = p_new_vendor_id)
106 WHERE field_name = 'Supplier Name'
107 AND registration_id IN
108 (SELECT DISTINCT registration_id
109 FROM fnd_registration_details WHERE field_name = 'Supplier Number'
110 AND field_value_number = p_old_vendor_id
111 );
112
113 UPDATE fnd_registration_details
114 SET field_value_number = p_new_vendor_id
115 WHERE field_name = 'Supplier Number'
116 AND registration_id IN
117 (SELECT DISTINCT registration_id
118 FROM fnd_registration_details WHERE field_name = 'Supplier Number'
119 AND field_value_number = p_old_vendor_id
120 );
121
122 END IF;
123
124 END IF;
125
126 -- I'm also going to maintain the following FKs for the moment,
127 -- just in case (12.0)
128
129 IF (p_new_vendor_id IS NOT NULL AND p_new_vendor_id <> p_old_vendor_id)
130 THEN
131
132 /* Commented out -mji
133
134 UPDATE pos_acct_addr_rel
135 SET vendor_id = p_new_vendor_id,
136 last_update_date = SYSDATE,
137 last_updated_by = FND_GLOBAL.user_id,
138 last_update_login = FND_GLOBAL.login_id
139 WHERE vendor_id = p_old_vendor_id;
140
141
142 UPDATE pos_supplier_mappings
143 SET vendor_id = p_new_vendor_id,
144 last_update_date = SYSDATE,
145 last_updated_by = FND_GLOBAL.user_id,
146 last_update_login = FND_GLOBAL.login_id
147 WHERE vendor_id = p_old_vendor_id;
148
149 UPDATE pos_supplier_registrations
150 SET po_vendor_id = p_new_vendor_id,
151 last_update_date = SYSDATE,
152 last_updated_by = FND_GLOBAL.user_id,
153 last_update_login = FND_GLOBAL.login_id
154 WHERE po_vendor_id = p_old_vendor_id;
155
156 UPDATE pos_sup_bank_account_requests
157 SET vendor_id = p_new_vendor_id,
158 last_update_date = SYSDATE,
159 last_updated_by = FND_GLOBAL.user_id,
160 last_update_login = FND_GLOBAL.login_id
161 WHERE vendor_id = p_old_vendor_id;
162 */
163
164 -- If it is the last site then merge supplier profile info
165 IF (l_num_active_sites = 0) THEN
166 -- delete classifications of the merged-from vendor that
167 -- the merged-to vendor also has
168
169 DELETE pos_sup_products_services t1
170 WHERE vendor_id = p_old_vendor_id
171 AND exists
172 (SELECT 1 FROM pos_sup_products_services t2
173 WHERE (t1.segment1 = t2.segment1 OR t1.segment1 IS NULL AND t2.segment1 IS NULL)
174 AND (t1.segment2 = t2.segment2 OR t1.segment2 IS NULL AND t2.segment2 IS NULL)
175 AND (t1.segment3 = t2.segment3 OR t1.segment3 IS NULL AND t2.segment3 IS NULL)
176 AND (t1.segment4 = t2.segment4 OR t1.segment4 IS NULL AND t2.segment4 IS NULL)
177 AND (t1.segment5 = t2.segment5 OR t1.segment5 IS NULL AND t2.segment5 IS NULL)
178 AND (t1.segment6 = t2.segment6 OR t1.segment6 IS NULL AND t2.segment6 IS NULL)
179 AND (t1.segment7 = t2.segment7 OR t1.segment7 IS NULL AND t2.segment7 IS NULL)
180 AND (t1.segment8 = t2.segment8 OR t1.segment8 IS NULL AND t2.segment8 IS NULL)
181 AND (t1.segment9 = t2.segment9 OR t1.segment9 IS NULL AND t2.segment9 IS NULL)
182 AND (t1.segment10 = t2.segment10 OR t1.segment10 IS NULL AND t2.segment10 IS NULL)
183 AND (t1.segment11 = t2.segment11 OR t1.segment11 IS NULL AND t2.segment11 IS NULL)
184 AND (t1.segment12 = t2.segment12 OR t1.segment12 IS NULL AND t2.segment12 IS NULL)
185 AND (t1.segment13 = t2.segment13 OR t1.segment13 IS NULL AND t2.segment13 IS NULL)
186 AND (t1.segment14 = t2.segment14 OR t1.segment14 IS NULL AND t2.segment14 IS NULL)
187 AND (t1.segment15 = t2.segment15 OR t1.segment15 IS NULL AND t2.segment15 IS NULL)
188 AND (t1.segment16 = t2.segment16 OR t1.segment16 IS NULL AND t2.segment16 IS NULL)
189 AND (t1.segment17 = t2.segment17 OR t1.segment17 IS NULL AND t2.segment17 IS NULL)
190 AND (t1.segment18 = t2.segment18 OR t1.segment18 IS NULL AND t2.segment18 IS NULL)
191 AND (t1.segment19 = t2.segment19 OR t1.segment19 IS NULL AND t2.segment19 IS NULL)
192 AND (t1.segment20 = t2.segment20 OR t1.segment20 IS NULL AND t2.segment20 IS NULL)
193 AND t2.vendor_id = p_new_vendor_id);
194
195 -- transfer classifications of the merged-from vendor to the merged-to vendor
196 UPDATE pos_sup_products_services
197 SET vendor_id = p_new_vendor_id,
198 last_update_date = SYSDATE,
199 last_updated_by = FND_GLOBAL.user_id,
200 last_update_login = FND_GLOBAL.login_id
201 WHERE vendor_id = p_old_vendor_id;
202 END IF;
203
204 ELSE
205 -- l_return_status := FND_API.g_ret_sts_error; Bug 5048890
206 null;
207
208 END IF;
209
210 IF (p_new_vendor_id IS NOT NULL AND
211 p_old_vendor_site_id <> p_new_vendor_site_id) THEN
212 pos_merge_supplier_pkg.supplier_site_uda_merge(p_from_id => p_new_vendor_id,
213 p_from_fk_id => p_old_vendor_site_id,
214 p_to_fk_id => p_new_vendor_site_id,
215 x_return_status => l_return_status);
216 END IF;
217
218 IF l_return_status = fnd_api.g_ret_sts_success THEN
219 x_return_status := fnd_api.g_ret_sts_success;
220 ELSE
221 IF ( fnd_log.level_error >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
222 fnd_log.string(fnd_log.level_error, g_module_prefix || l_module,
223 'return status ' || l_return_status);
224
225 END IF;
226
227 ROLLBACK TO pos_handle_merge;
228 RAISE pos_merge_exception;
229
230 END IF;
231
232 IF ( fnd_log.level_procedure >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
233 fnd_log.string(fnd_log.level_procedure, g_module_prefix || l_module,'5.End');
234 END IF;
235
236
237 EXCEPTION
238 WHEN pos_merge_exception THEN
239 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
240 --raise_application_error(-20001, g_module_prefix || l_module || ' merge error', true);
241 IF ( fnd_log.level_error >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
242 fnd_log.string(fnd_log.level_error, g_module_prefix||l_module,' merge error');
243 END IF;
244
245
246 WHEN OTHERS THEN
247 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
248 -- raise_application_error(-20002, g_module_prefix || l_module || Sqlerrm, TRUE);
249
250 IF ( fnd_log.level_error >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
251 fnd_log.string(fnd_log.level_error, g_module_prefix||l_module||' SQL error', Sqlerrm);
252 END IF;
253 END handle_merge;
254
255
256
257 FUNCTION GET_ACTIVE_SITE_COUNT (p_dup_vendor_id IN NUMBER,
258 p_dup_vendor_site_id IN NUMBER)
259 RETURN NUMBER
260 IS
261
262 l_num_active_sites NUMBER;
263
264 BEGIN
265 -- select count of active sites (besides the site being merged)
266
267 SELECT count(*)
268 INTO l_num_active_sites
269 FROM po_vendor_sites_all
270 WHERE vendor_id = p_dup_vendor_id
271 AND vendor_site_id <> p_dup_vendor_site_id
272 AND nvl(inactive_date, sysdate+1) > sysdate;
273
274 return l_num_active_sites;
275
276 EXCEPTION
277 WHEN OTHERS THEN
278 return 1;
279
280 END GET_ACTIVE_SITE_COUNT;
281
282
283 END pos_sup_prof_mrg_grp;
[Home] [Help]