1 PACKAGE BODY IGI_VENDOR_MERGE_GRP AS
2 -- $Header: igismrgb.pls 120.8 2007/06/22 10:23:35 smannava ship $
3 --
4 -- Global Variables
5 g_org_id NUMBER := to_number(fnd_profile.value('ORG_ID'));
6 g_pkg_name CONSTANT VARCHAR2(30) := 'IGI_VENDOR_MERGE_GRP';
7 --
8 -- PRIVATE ROUTINES
9 --
10 --
11 -- *************************************************************************
12 -- Procedure : Update_EXP_Tables
13 -- Bug 3282938, EXP table IGI_EXP_DUS should be updated for
14 -- Supplier merge.
15 -- *************************************************************************
16 PROCEDURE update_EXP_tables (p_paid_invoices_flag IN VARCHAR2,
17 p_old_vendor_id IN NUMBER,
18 p_old_vendor_site_id IN NUMBER,
19 p_new_vendor_id IN NUMBER,
20 p_new_vendor_site_id IN NUMBER,
21 x_return_status OUT NOCOPY VARCHAR2 ,
22 x_msg_count OUT NOCOPY NUMBER ,
23 x_msg_data OUT NOCOPY VARCHAR2);
24
25 --
26 -- PUBLIC ROUTINES
27 --
28 --
29 -- *************************************************************************
30 -- Procedure : Merge_Vendor
31 -- Off all the IGI tables containing VENDOR_ID, only the CIS table
32 -- needs updating. If new tables are added which store vendor_id,
33 -- this procedure should be updated to modify the new tables too.
34 -- The AP supplier merge report APXINUPD.rdf which performs all the
35 -- updates on the core table will call this API.
36 -- Bug 3282938, EXP table IGI_EXP_DUS should be updated for
37 -- Supplier merge.
38 -- *************************************************************************
39
40 PROCEDURE merge_vendor(p_api_version IN NUMBER
41 ,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
42 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
43 ,p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
44 ,x_return_status OUT NOCOPY VARCHAR2
45 ,x_msg_count OUT NOCOPY NUMBER
46 ,x_msg_data OUT NOCOPY VARCHAR2
47 ,p_new_vendor_id IN NUMBER
48 ,p_new_vendor_site_id IN NUMBER
49 ,p_old_vendor_id IN NUMBER
50 ,p_old_vendor_site_id IN NUMBER)
51 IS
52
53 l_api_version CONSTANT NUMBER := 1.0;
54 l_api_name CONSTANT VARCHAR2(30) := 'VENDOR_MERGE';
55
56 CURSOR c_chk_prms IS
57 SELECT adv.paid_invoices_flag,
58 adv.process
59 FROM ap_duplicate_vendors adv
60 WHERE adv.vendor_id = p_new_vendor_id
61 AND adv.duplicate_vendor_id = p_old_vendor_id
62 AND adv.duplicate_vendor_site_id = p_old_vendor_site_id
63 AND adv.process_flag IN ('S','D');
64
65 l_paid_invoices_flag ap_duplicate_vendors.paid_invoices_flag%TYPE;
66 l_process ap_duplicate_vendors.process%TYPE;
67
68 BEGIN
69
70 -- Standard call to check for call compatibility
71 IF (NOT FND_API.Compatible_API_Call(l_api_version
72 ,p_api_version
73 ,l_api_name
74 ,G_PKG_NAME))
75 THEN
76 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
77 END IF;
78
79 -- Check p_init_msg_list
80 IF FND_API.to_Boolean(p_init_msg_list)
81 THEN
82 FND_MSG_PUB.initialize;
83 END IF;
84
85 -- Initialize API return status to success
86 x_return_status := FND_API.G_RET_STS_SUCCESS;
87
88 OPEN c_chk_prms;
89 FETCH c_chk_prms INTO l_paid_invoices_flag,
90 l_process;
91 CLOSE c_chk_prms;
92
93 -- If user has chosen to update paid invoices, and if CIS
94 -- is enabled, then update the CIS table.
95 IF l_paid_invoices_flag = 'Y'
96 AND l_process <> 'P' -- not only purchase orders.
97 AND igi_gen.is_req_installed('CIS')
98 THEN
99 UPDATE igi_cis_payment_vouchers
100 SET vendor_id = p_new_vendor_id,
101 vendor_site_id = p_new_vendor_site_id,
102 last_update_date = SYSDATE,
103 last_update_login = FND_GLOBAL.login_id,
104 last_updated_by = FND_GLOBAL.user_id
105 WHERE vendor_id = p_old_vendor_id
106 AND vendor_site_id = p_old_vendor_site_id;
107
108 END IF;
109
110 -- If user has chosen to update invoices
111 -- And EXP is enabled, then update the EXP table.
112 IF l_process <> 'P' -- not only purchase orders.
113 AND igi_gen.is_req_installed('EXP')
114 THEN
115 update_EXP_tables (p_paid_invoices_flag => l_paid_invoices_flag,
116 p_old_vendor_id => p_old_vendor_id,
117 p_old_vendor_site_id => p_old_vendor_site_id,
118 p_new_vendor_id => p_new_vendor_id,
119 p_new_vendor_site_id => p_new_vendor_site_id,
120 x_return_status => x_return_status,
121 x_msg_count => x_msg_count,
122 x_msg_data => x_msg_data);
123 END IF;
124
125
126 -- If user has chosen to process PO's and if
127 -- CC is enabled then call the CC API to update the CC table
128 IF l_process <> 'I' -- not only invoices
129 AND igi_gen.is_req_installed('CC')
130 THEN
131 IGC_VENDOR_MERGE_PVT.MERGE_VENDOR
132 (p_api_version => 1.0
133 ,p_init_msg_list => FND_API.G_FALSE
134 ,p_commit => FND_API.G_FALSE
135 ,p_validation_level => FND_API.G_VALID_LEVEL_FULL
136 ,x_return_status => x_return_status
137 ,x_msg_count => x_msg_count
138 ,x_msg_data => x_msg_data
139 ,p_new_vendor_id => p_new_vendor_id
140 ,p_new_vendor_site_id => p_new_vendor_site_id
141 ,p_old_vendor_id => p_old_vendor_id
142 ,p_old_vendor_site_id => p_old_vendor_site_id);
143 END IF;
144
145 IF p_commit = FND_API.G_TRUE
146 AND x_return_status = FND_API.G_RET_STS_SUCCESS
147 THEN
148 COMMIT;
149 END IF;
150
151 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
152 p_data => x_msg_data);
153
154 EXCEPTION
155 WHEN FND_API.G_EXC_UNEXPECTED_ERROR
156 THEN
157 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
158 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
159 p_data => x_msg_data);
160
161 WHEN OTHERS
162 THEN
163 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
164 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
165 THEN
166 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name);
167 END IF;
168
169 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count,
170 p_data => x_msg_data);
171 END merge_vendor;
172
173 -- *************************************************************************
174 -- Procedure : Update_EXP_Tables
175 -- Bug 3282938, EXP table IGI_EXP_DUS should be updated for
176 -- Supplier merge.
177 -- *************************************************************************
178 PROCEDURE update_EXP_tables (p_paid_invoices_flag IN VARCHAR2,
179 p_old_vendor_id IN NUMBER,
180 p_old_vendor_site_id IN NUMBER,
181 p_new_vendor_id IN NUMBER,
182 p_new_vendor_site_id IN NUMBER,
183 x_return_status OUT NOCOPY VARCHAR2 ,
184 x_msg_count OUT NOCOPY NUMBER ,
185 x_msg_data OUT NOCOPY VARCHAR2)
186 IS
187 l_api_name VARCHAR2(30) := 'Update_EXP_Tables';
188 BEGIN
189
190 -- Initialize API return status to success
191 x_return_status := FND_API.G_RET_STS_SUCCESS;
192
193 -- If user has chosen to update all invoices (paid + unpaid)
194 -- then update all the dialog units irrespective of their status.
195 IF p_paid_invoices_flag = 'Y'
196 THEN
197 -- Update all the Dialog Units associcated with payables.
198 UPDATE igi_exp_dus du
199 SET du.du_stp_id = p_new_vendor_id,
200 du.du_stp_site_id = p_new_vendor_site_id,
201 du.last_update_date = SYSDATE,
202 du.last_update_login = FND_GLOBAL.login_id,
203 du.last_updated_by = FND_GLOBAL.user_id
204 WHERE du.du_stp_id = p_old_vendor_id
205 AND du.du_stp_site_id = p_old_vendor_site_id
206 AND du.du_type_header_id IN
207 (SELECT du_type_header_id
208 FROM igi_exp_du_type_headers
209 WHERE application_id = 200) ;
210 ELSE
211 -- User has chosen to update only unpaid invoices
212 -- Update all dialog units which are not completed.
213 UPDATE igi_exp_dus du
214 SET du.du_stp_id = p_new_vendor_id,
215 du.du_stp_site_id = p_new_vendor_site_id,
216 du.last_update_date = SYSDATE,
217 du.last_update_login = FND_GLOBAL.login_id,
218 du.last_updated_by = FND_GLOBAL.user_id
219 WHERE du.du_stp_id = p_old_vendor_id
220 AND du.du_stp_site_id = p_old_vendor_site_id
221 AND du.du_status <> 'COM'
222 AND du.du_type_header_id IN
223 (SELECT du_type_header_id
224 FROM igi_exp_du_type_headers
225 WHERE application_id = 200) ;
226
227 --
228 -- Then update all the dialog units which are completed but
229 -- none of the invoices have been paid.
230 UPDATE igi_exp_dus du
231 SET du.du_stp_id = p_new_vendor_id,
232 du.du_stp_site_id = p_new_vendor_site_id,
233 du.last_update_date = SYSDATE,
234 du.last_update_login = FND_GLOBAL.login_id,
235 du.last_updated_by = FND_GLOBAL.user_id
236 WHERE du.du_stp_id = p_old_vendor_id
237 AND du.du_stp_site_id = p_old_vendor_site_id
238 AND du.du_status = 'COM'
239 AND du.du_type_header_id IN
240 (SELECT du_type_header_id
241 FROM igi_exp_du_type_headers
242 WHERE application_id = 200)
243 AND NOT EXISTS
244 (SELECT 'X'
245 FROM ap_invoices api,
246 igi_exp_ap_trans eapi
247 WHERE eapi.du_id = du.du_id
248 AND eapi.invoice_id = api.invoice_id
249 AND Nvl(api.payment_status_flag,'N') = 'Y');
250
251 END IF ; -- p_paid_invoices_flag = 'Y'
252
253 EXCEPTION
254 WHEN OTHERS
255 THEN
256 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
257 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
258 THEN
259 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name);
260 END IF;
261
262 END update_EXP_tables;
263
264 END IGI_VENDOR_MERGE_GRP;
265
[Home] [Help]