[Home] [Help]
PACKAGE BODY: APPS.FUN_VENDORMERGE_GRP
Source
1 PACKAGE BODY FUN_VendorMerge_GRP AS
2 /* $Header: funntsmb.pls 120.4 2006/03/30 14:15:05 asrivats noship $ */
3
4 --Declare all required global variables
5 g_user_id NUMBER;
6 g_login_id NUMBER;
7 g_today DATE;
8
9 --===========================FND_LOG.START=====================================
10
11 g_state_level NUMBER;
12 g_proc_level NUMBER;
13 g_event_level NUMBER;
14 g_excep_level NUMBER;
15 g_error_level NUMBER;
16 g_unexp_level NUMBER;
17 g_path VARCHAR2(100);
18
19 --===========================FND_LOG.END=======================================
20
21 PROCEDURE Merge_Vendor(
22 -- ***** Standard API Parameters *****
23 p_api_version IN NUMBER,
24 p_init_msg_list IN VARCHAR2 default FND_API.G_FALSE,
25 p_commit IN VARCHAR2 default FND_API.G_FALSE,
26 p_validation_level IN NUMBER default FND_API.G_VALID_LEVEL_FULL,
27 p_return_status OUT NOCOPY VARCHAR2,
28 p_msg_count OUT NOCOPY NUMBER ,
29 p_msg_data OUT NOCOPY VARCHAR2,
30 -- ****** Merge Input Parameters ******
31 p_vendor_id IN NUMBER ,
32 p_dup_vendor_id IN NUMBER ,
33 p_vendor_site_id IN NUMBER ,
34 p_dup_vendor_site_id IN NUMBER
35 )
36 IS
37
38 -- ***** local variables *****
39 l_return_status VARCHAR2(1);
40 l_msg_count NUMBER;
41 l_msg_data VARCHAR2(2000);
42 l_path VARCHAR2(100);
43 PROCEDURE Check_Mandatory_Parameters (
44 x_return_status OUT NOCOPY VARCHAR2)
45 IS
46 BEGIN
47 x_return_status := FND_API.G_TRUE;
48 IF p_vendor_id IS NULL or p_dup_vendor_id IS NULL
49 or p_vendor_site_id IS NULL or p_dup_vendor_site_id IS NULL
50 THEN
51 x_return_status := FND_API.G_FALSE;
52 RETURN;
53 END IF;
54
55 EXCEPTION
56 WHEN OTHERS THEN
57 x_return_status := FND_API.G_FALSE;
58
59 END Check_Mandatory_Parameters;
60
61 BEGIN
62 p_msg_count := NULL;
63 p_msg_data := NULL;
64 g_user_id := fnd_global.user_id;
65 g_login_id := fnd_global.login_id;
66
67 l_path := g_path||'Supplier Merge:';
68 fun_net_util.Log_String(g_event_level,l_path,'Supplier Merge(+)');
69
70 -- **** Standard start of API savepoint ****
71
72 SAVEPOINT Merge_Vendor_SP;
73
74 -- **** Initialize message list if p_init_msg_list is set to TRUE. ****
75
76 IF FND_API.to_Boolean( p_init_msg_list ) THEN
77 FND_MSG_PUB.initialize;
78 END IF;
79
80 -- **** Initialize return status to SUCCESS *****
81 p_return_status := FND_API.G_RET_STS_SUCCESS;
82
83 /*-----------------------------------------------+
84 | ======== START OF API BODY ============ |
85 +-----------------------------------------------*/
86
87 -- **** Check for mandatory parameters ****
88
89 /* fun_net_util.Log_String(g_event_level,
90 l_path
91 ,'Check Mandatory Params(+)');
92
93 Check_Mandatory_Parameters(
94 x_return_status => l_return_status);
95
96 fun_net_util.Log_String(g_event_level,
97 l_path
98 ,'Return Status'|| l_return_status);
99
100 fun_net_util.Log_String(g_event_level,
101 l_path,
102 'Check Mandatory Params(-)');
103
104 IF l_return_status = FND_API.G_FALSE THEN
105 RAISE FND_API.G_EXC_ERROR;
106 END IF;
107 */
108 -- Update Vendor Id and Vendor Site id with the Merged Vendor Id
109 -- and Vendor Site Id
110 -- If the Vendor Id and the Merged Vendor Id and in the same
111 -- agreement with the same vendor sites or no vendor sites and
112 -- if they have different priorities ,update the vendor ids
113 -- with the higher priority of the two
114
115 BEGIN
116
117 fun_net_util.Log_String(g_event_level,
118 l_path
119 ,'Updating Netting Suppliers');
120
121 UPDATE fun_net_suppliers_all s
122 SET supplier_id = p_vendor_id,
123 supplier_site_id = decode(supplier_site_id,p_dup_vendor_site_id,
124 p_vendor_site_id, supplier_site_id)
125 WHERE supplier_id = p_dup_vendor_id
126 AND nvl(supplier_site_id, 0) =
127 decode(supplier_site_id, NULL,
128 0, p_dup_vendor_site_id);
129
130 fun_net_util.Log_String(g_event_level
131 ,l_path
132 ,'Rows Updated'|| sql%rowcount);
133
134 UPDATE fun_net_suppliers_all s
135 SET supplier_priority = (
136 SELECT min(supplier_priority)
137 FROM fun_net_suppliers_all
138 WHERE agreement_id = s.agreement_id
139 AND supplier_id = s.supplier_id
140 AND nvl(supplier_site_id,0) =
141 decode(s.supplier_site_id,
142 NULL,0,s.supplier_site_id))
143 WHERE supplier_id = p_vendor_id
144 AND nvl(supplier_site_id, 0) =
145 decode(supplier_site_id, NULL,
146 0, p_vendor_site_id);
147
148 EXCEPTION
149 WHEN NO_DATA_FOUND THEN
150 null;
151
152 WHEN OTHERS THEN
153
154 fun_net_util.Log_String(g_event_level,
155 l_path
156 ,'sqlcode '||sqlcode||' sqlerrm ' || sqlerrm);
157 RAISE FND_API.G_EXC_ERROR;
158 END;
159
160 /* Delete the record that has the same agreement id, supplier priority , vendor and vendor site */
161
162 BEGIN
163
164 fun_net_util.Log_String(g_event_level,
165 l_path
166 ,'Deleting Records');
167
168 DELETE FROM fun_net_suppliers_all s
169 WHERE netting_supplier_id = (SELECT min(netting_supplier_id)
170 FROM fun_net_suppliers_all
171 WHERE
172 s.agreement_id = agreement_id
173 AND s.supplier_id = supplier_id
174 AND nvl(s.supplier_site_id,0) = nvl(supplier_site_id,0)
175 AND s.supplier_priority = supplier_priority
176 GROUP BY agreement_id,
177 supplier_id,
178 supplier_site_id,
179 supplier_priority
180 HAVING COUNT(netting_supplier_id) > 1);
181
182 fun_net_util.Log_String(g_event_level,
183 l_path
184 ,'Records Deleted' || sql%rowcount);
185
186 fun_net_util.Log_String(g_event_level,
187 l_path
188 ,'Supplier Merge (-)');
189 EXCEPTION
190 WHEN NO_DATA_FOUND THEN
191 null;
192 WHEN OTHERS THEN
193
194 fun_net_util.Log_String
195 (g_event_level,
196 l_path
197 ,'sqlcode' || sqlcode || 'sqlerrm ' || sqlerrm);
198
199 END;
200
201 -- Standard check of p_commit.
202 IF FND_API.To_Boolean( p_commit ) THEN
203 COMMIT WORK;
204 END IF;
205
206 EXCEPTION
207 WHEN FND_API.G_EXC_ERROR THEN
208 ROLLBACK TO Merge_Vendor_SP;
209 p_return_status := FND_API.G_RET_STS_ERROR;
210 FND_MSG_PUB.Count_And_Get (
211 p_count => p_msg_count,
212 p_data => p_msg_data );
213
214 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
215 ROLLBACK TO Merge_Vendor_SP;
216 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
217 FND_MSG_PUB.Count_And_Get (
218 p_count => p_msg_count,
219 p_data => p_msg_data );
220
221 WHEN OTHERS THEN
222 ROLLBACK TO Merge_Vendor_SP;
223 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
224 IF FND_MSG_PUB.Check_Msg_Level(
225 FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
226 FND_MSG_PUB.Add_Exc_Msg( 'FUN_VendorMerge_PKG', 'Merge_Vendor');
227 END IF;
228 FND_MSG_PUB.Count_And_Get (
229 p_count => p_msg_count,
230 p_data => p_msg_data );
231 END Merge_Vendor;
232
233 BEGIN
234 g_today := TRUNC(sysdate);
235 END FUN_VendorMerge_GRP;