1 package body pos_sup_prof_mrg_grp as
2 /* $Header: POSSPMGB.pls 120.12 2006/06/22 19:01:00 bitang noship $ */
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 IF (p_new_vendor_site_id <> p_old_vendor_site_id) THEN
73
74 -- Merge site
75 UPDATE ak_web_user_sec_attr_values
76 SET number_value = p_new_vendor_site_id
77 WHERE attribute_code = 'ICX_SUPPLIER_SITE_ID'
78 AND attribute_application_id = 177
79 AND number_value = p_old_vendor_site_id;
80
81 -- If it is the last site then merge vendor
82 IF (l_num_active_sites = 0) THEN
83 UPDATE ak_web_user_sec_attr_values
84 SET number_value = p_new_vendor_id
85 WHERE attribute_code = 'ICX_SUPPLIER_ORG_ID'
86 AND attribute_application_id = 177
87 AND number_value = p_old_vendor_id;
88
89 UPDATE fnd_registration_details
90 SET field_value_string = (SELECT vendor_name FROM po_vendors WHERE vendor_id = p_new_vendor_id)
91 WHERE field_name = 'Supplier Name'
92 AND registration_id IN
93 (SELECT DISTINCT registration_id
94 FROM fnd_registration_details WHERE field_name = 'Supplier Number'
95 AND field_value_number = p_old_vendor_id
96 );
97
98 UPDATE fnd_registration_details
99 SET field_value_number = p_new_vendor_id
100 WHERE field_name = 'Supplier Number'
101 AND registration_id IN
102 (SELECT DISTINCT registration_id
103 FROM fnd_registration_details WHERE field_name = 'Supplier Number'
104 AND field_value_number = p_old_vendor_id
105 );
106
107 END IF;
108
109 END IF;
110
111 -- I'm also going to maintain the following FKs for the moment,
112 -- just in case (12.0)
113
114 IF (p_new_vendor_id IS NOT NULL AND p_new_vendor_id <> p_old_vendor_id)
115 THEN
116
117 /* Commented out -mji
118
119 UPDATE pos_acct_addr_rel
120 SET vendor_id = p_new_vendor_id,
121 last_update_date = SYSDATE,
122 last_updated_by = FND_GLOBAL.user_id,
123 last_update_login = FND_GLOBAL.login_id
124 WHERE vendor_id = p_old_vendor_id;
125
126
127 UPDATE pos_supplier_mappings
128 SET vendor_id = p_new_vendor_id,
129 last_update_date = SYSDATE,
130 last_updated_by = FND_GLOBAL.user_id,
131 last_update_login = FND_GLOBAL.login_id
132 WHERE vendor_id = p_old_vendor_id;
133
134 UPDATE pos_supplier_registrations
135 SET po_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 po_vendor_id = p_old_vendor_id;
140
141 UPDATE pos_sup_bank_account_requests
142 SET vendor_id = p_new_vendor_id,
143 last_update_date = SYSDATE,
144 last_updated_by = FND_GLOBAL.user_id,
145 last_update_login = FND_GLOBAL.login_id
146 WHERE vendor_id = p_old_vendor_id;
147 */
148
149 -- If it is the last site then merge supplier profile info
150 IF (l_num_active_sites = 0) THEN
151 -- delete classifications of the merged-from vendor that
152 -- the merged-to vendor also has
153
154 DELETE pos_sup_products_services t1
155 WHERE vendor_id = p_old_vendor_id
156 AND exists
157 (SELECT 1 FROM pos_sup_products_services t2
158 WHERE (t1.segment1 = t2.segment1 OR t1.segment1 IS NULL AND t2.segment1 IS NULL)
159 AND (t1.segment2 = t2.segment2 OR t1.segment2 IS NULL AND t2.segment2 IS NULL)
160 AND (t1.segment3 = t2.segment3 OR t1.segment3 IS NULL AND t2.segment3 IS NULL)
161 AND (t1.segment4 = t2.segment4 OR t1.segment4 IS NULL AND t2.segment4 IS NULL)
162 AND (t1.segment5 = t2.segment5 OR t1.segment5 IS NULL AND t2.segment5 IS NULL)
163 AND (t1.segment6 = t2.segment6 OR t1.segment6 IS NULL AND t2.segment6 IS NULL)
164 AND (t1.segment7 = t2.segment7 OR t1.segment7 IS NULL AND t2.segment7 IS NULL)
165 AND (t1.segment8 = t2.segment8 OR t1.segment8 IS NULL AND t2.segment8 IS NULL)
166 AND (t1.segment9 = t2.segment9 OR t1.segment9 IS NULL AND t2.segment9 IS NULL)
167 AND (t1.segment10 = t2.segment10 OR t1.segment10 IS NULL AND t2.segment10 IS NULL)
168 AND (t1.segment11 = t2.segment11 OR t1.segment11 IS NULL AND t2.segment11 IS NULL)
169 AND (t1.segment12 = t2.segment12 OR t1.segment12 IS NULL AND t2.segment12 IS NULL)
170 AND (t1.segment13 = t2.segment13 OR t1.segment13 IS NULL AND t2.segment13 IS NULL)
171 AND (t1.segment14 = t2.segment14 OR t1.segment14 IS NULL AND t2.segment14 IS NULL)
172 AND (t1.segment15 = t2.segment15 OR t1.segment15 IS NULL AND t2.segment15 IS NULL)
173 AND (t1.segment16 = t2.segment16 OR t1.segment16 IS NULL AND t2.segment16 IS NULL)
174 AND (t1.segment17 = t2.segment17 OR t1.segment17 IS NULL AND t2.segment17 IS NULL)
175 AND (t1.segment18 = t2.segment18 OR t1.segment18 IS NULL AND t2.segment18 IS NULL)
176 AND (t1.segment19 = t2.segment19 OR t1.segment19 IS NULL AND t2.segment19 IS NULL)
177 AND (t1.segment20 = t2.segment20 OR t1.segment20 IS NULL AND t2.segment20 IS NULL)
178 AND t2.vendor_id = p_new_vendor_id);
179
180 -- transfer classifications of the merged-from vendor to the merged-to vendor
181 UPDATE pos_sup_products_services
182 SET vendor_id = p_new_vendor_id,
183 last_update_date = SYSDATE,
184 last_updated_by = FND_GLOBAL.user_id,
185 last_update_login = FND_GLOBAL.login_id
186 WHERE vendor_id = p_old_vendor_id;
187 END IF;
188
189 ELSE
190 -- l_return_status := FND_API.g_ret_sts_error; Bug 5048890
191 null;
192
193 END IF;
194
195
196 IF l_return_status = fnd_api.g_ret_sts_success THEN
197 x_return_status := fnd_api.g_ret_sts_success;
198 ELSE
199 IF ( fnd_log.level_error >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
200 fnd_log.string(fnd_log.level_error, g_module_prefix || l_module,
201 'return status ' || l_return_status);
202
203 END IF;
204
205 ROLLBACK TO pos_handle_merge;
206 RAISE pos_merge_exception;
207
208 END IF;
209
210 IF ( fnd_log.level_procedure >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
211 fnd_log.string(fnd_log.level_procedure, g_module_prefix || l_module,'5.End');
212 END IF;
213
214
215 EXCEPTION
216 WHEN pos_merge_exception THEN
217 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
218 --raise_application_error(-20001, g_module_prefix || l_module || ' merge error', true);
219 IF ( fnd_log.level_error >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
220 fnd_log.string(fnd_log.level_error, g_module_prefix||l_module,' merge error');
221 END IF;
222
223
224 WHEN OTHERS THEN
225 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
226 -- raise_application_error(-20002, g_module_prefix || l_module || Sqlerrm, TRUE);
227
228 IF ( fnd_log.level_error >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
229 fnd_log.string(fnd_log.level_error, g_module_prefix||l_module||' SQL error', Sqlerrm);
230 END IF;
231 END handle_merge;
232
233
234
235 FUNCTION GET_ACTIVE_SITE_COUNT (p_dup_vendor_id IN NUMBER,
236 p_dup_vendor_site_id IN NUMBER)
237 RETURN NUMBER
238 IS
239
240 l_num_active_sites NUMBER;
241
242 BEGIN
243 -- select count of active sites (besides the site being merged)
244
245 SELECT count(*)
246 INTO l_num_active_sites
247 FROM po_vendor_sites_all
248 WHERE vendor_id = p_dup_vendor_id
249 AND vendor_site_id <> p_dup_vendor_site_id
250 AND nvl(inactive_date, sysdate+1) > sysdate;
251
252 return l_num_active_sites;
253
254 EXCEPTION
255 WHEN OTHERS THEN
256 return 1;
257
258 END GET_ACTIVE_SITE_COUNT;
259
260
261 END pos_sup_prof_mrg_grp;
[Home] [Help]