1 PACKAGE BODY AP_TCA_SUPPLIER_SYNC_PKG AS
2 /* $Header: aptcasyb.pls 120.3 2007/12/20 07:18:18 mahkumar ship $ */
3
4 -- Logging Infrastructure
5 G_CURRENT_RUNTIME_LEVEL NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
6 G_LEVEL_UNEXPECTED CONSTANT NUMBER := FND_LOG.LEVEL_UNEXPECTED;
7 G_LEVEL_ERROR CONSTANT NUMBER := FND_LOG.LEVEL_ERROR;
8 G_LEVEL_EXCEPTION CONSTANT NUMBER := FND_LOG.LEVEL_EXCEPTION;
9 G_LEVEL_EVENT CONSTANT NUMBER := FND_LOG.LEVEL_EVENT;
10 G_LEVEL_PROCEDURE CONSTANT NUMBER := FND_LOG.LEVEL_PROCEDURE;
11 G_LEVEL_STATEMENT CONSTANT NUMBER := FND_LOG.LEVEL_STATEMENT;
12 G_MODULE_NAME CONSTANT VARCHAR2(50) := 'AP.PLSQL.AP_TCA_SUPPLIER_SYNC_PKG.';
13
14 -- Procedure Sync Supplier
15 PROCEDURE SYNC_Supplier
16 (x_return_status OUT NOCOPY VARCHAR2,
17 x_msg_count OUT NOCOPY NUMBER,
18 x_msg_data OUT NOCOPY VARCHAR2,
19 x_party_id IN NUMBER) IS
20
21 l_num_1099 ap_suppliers.num_1099%type;
22 l_vat_registration_num ap_suppliers.vat_registration_num%type;
23 l_vendor_name_alt ap_suppliers.vendor_name_alt%type;
24 l_vendor_name ap_suppliers.vendor_name%type;
25 l_tca_vendor_name ap_suppliers.tca_sync_vendor_name%type;
26 l_tca_vat_registration_num ap_suppliers.tca_sync_vat_reg_num%type;
27 l_check_party_id hz_parties.party_id%type;
28 l_upgraded_num_1099 ap_suppliers.num_1099%type;
29 l_check_num_1099 ap_suppliers.tca_sync_num_1099%type;
30
31 -- Logging Infra:
32 l_procedure_name CONSTANT VARCHAR2(30) := 'SYNC_Supplier';
33 l_log_msg FND_LOG_MESSAGES.MESSAGE_TEXT%TYPE;
34
35 BEGIN
36
37 -- Initialize API return status to success
38 x_return_status := fnd_api.g_ret_sts_success;
39
40 -- Logging Infra: Setting up runtime level
41 G_CURRENT_RUNTIME_LEVEL := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
42
43 -- Logging Infra: Procedure level
44 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
45 l_log_msg := 'Begin of procedure '|| l_procedure_name;
46 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||
47 l_procedure_name||'.begin', l_log_msg);
48 END IF;
49
50 -- Logging Infra: Statement level
51 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
52 l_log_msg := 'Party ID '|| x_party_id;
53 FND_LOG.STRING(G_LEVEL_STATEMENT, G_MODULE_NAME||
54 '.'||l_procedure_name, l_log_msg);
55 END IF;
56
57
58 IF x_party_id IS NOT NULL THEN
59
60 -- Logging Infra: Statement level
61 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
62 l_log_msg := 'Select sync attrributes from TCA for '|| x_party_id;
63 FND_LOG.STRING(G_LEVEL_STATEMENT, G_MODULE_NAME||
64 '.'||l_procedure_name, l_log_msg);
65 END IF;
66
67 SELECT jgzz_fiscal_code,
68 substrb(tax_reference,1,20),
69 organization_name_phonetic,
70 substrb(party_name,1,240),
71 party_name,
72 tax_reference
73 INTO l_num_1099,
74 l_vat_registration_num,
75 l_vendor_name_alt,
76 l_vendor_name,
77 l_tca_vendor_name,
78 l_tca_vat_registration_num
79 FROM hz_parties
80 WHERE party_id = x_party_id;
81
82 -- Logging Infra: Statement level
83 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
84 l_log_msg := 'After Selecting Attributes for '|| x_party_id;
85 FND_LOG.STRING(G_LEVEL_STATEMENT, G_MODULE_NAME||
86 '.'||l_procedure_name, l_log_msg);
87 END IF;
88
89 -- Logging Infra: Statement level
90 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
91 l_log_msg := 'Select to check if supplier exists for '|| x_party_id;
92 FND_LOG.STRING(G_LEVEL_STATEMENT, G_MODULE_NAME||
93 '.'||l_procedure_name, l_log_msg);
94 END IF;
95
96 --
97 -- Here we are also selecting the num_1099 columns
98 -- the num_1099 (tax payer id) has a greater length in
99 -- AP than in TCA.
100 -- In the upgraded case, we wanted to preserve the old value
101 -- hence we are moving the old value from the num_1099 column
102 -- to tca_sync_num_1099 column. We will do this only if the
103 -- tca_sync_num_1099 column is null (only the first time).
104 --
105
106 BEGIN
107 SELECT party_id,
108 num_1099,
109 tca_sync_num_1099
110 INTO l_check_party_id,
111 l_upgraded_num_1099,
112 l_check_num_1099
113 FROM ap_suppliers
114 WHERE party_id = x_party_id;
115 EXCEPTION
116 WHEN OTHERS THEN
117 NULL;
118 END;
119
120 IF l_check_party_id IS NOT NULL THEN
121
122 --bug6050423.We maintain the sync from tca to ap only if the vendor type is
123 --not contractor individual.For contractor individuals,if we update the
124 --the jgzz_fisacl_code in tca,the same thing will not be reflected in
125 --num_1099 of the ap_suppliers.We store the TIN numbers of the contractors
126 --in the field individual_1099 of ap_suppliers and not in TCA.
127
128
129 UPDATE ap_suppliers
130 SET
131 --bug6691916.commented the below assignment statement and added
132 --the one below that.As per analysis,only organization type lookup
133 --code of individual or foreign individual are considered
134 /*num_1099 = decode(UPPER(vendor_type_lookup_code),'CONTRACTOR',
135 decode(UPPER(organization_type_lookup_code),
136 'INDIVIDUAL',NULL,
137 'FOREIGN INDIVIDUAL',NULL,
138 'PARTNERSHIP',NULL,
139 'FOREIGN PARTNERSHIP',NULL,
140 l_num_1099),
141 l_num_1099),*/
142 num_1099 = decode(UPPER(organization_type_lookup_code),
143 'INDIVIDUAL',NULL,
144 'FOREIGN INDIVIDUAL',NULL,
145 l_num_1099),
146 vat_registration_num = l_vat_registration_num,
147 vendor_name_alt = l_vendor_name_alt,
148 vendor_name = l_vendor_name,
149 tca_sync_vendor_name = l_tca_vendor_name,
150 tca_sync_vat_reg_num = l_tca_vat_registration_num,
151 tca_sync_num_1099 = nvl(l_check_num_1099,
152 l_upgraded_num_1099)
153 WHERE party_id = x_party_id;
154
155 -- Logging Infra: Statement level
156 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
157 l_log_msg := 'After updating suppliers for '|| x_party_id;
158 FND_LOG.STRING(G_LEVEL_STATEMENT, G_MODULE_NAME||
159 '.'||l_procedure_name, l_log_msg);
160 END IF;
161 END IF;
162 END IF;
163
164 -- Logging Infra: Procedure level
165 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
166 l_log_msg := 'End of procedure '|| l_procedure_name;
167 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||
168 l_procedure_name||'.begin', l_log_msg);
169 END IF;
170
171 EXCEPTION
172 WHEN OTHERS THEN
173 IF (SQLCODE <> -20001) THEN
174 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
175 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
176 FND_MESSAGE.SET_TOKEN('PARAMETERS','Party ID = '|| x_party_id);
177 FND_MSG_PUB.Add;
178 END IF;
179 x_return_status := fnd_api.g_ret_sts_error;
180 FND_MSG_PUB.Count_And_Get
181 (p_count=>x_msg_count,
182 p_data=>x_msg_data
183 );
184 END SYNC_Supplier;
185
186 -- Procedure Sync Supplier Site
187 PROCEDURE SYNC_Supplier_Sites
188 (x_return_status OUT NOCOPY VARCHAR2,
189 x_msg_count OUT NOCOPY NUMBER,
190 x_msg_data OUT NOCOPY VARCHAR2,
191 x_location_id IN NUMBER,
192 x_party_site_id IN NUMBER) IS
193
194 -- Logging Infra:
195 l_procedure_name CONSTANT VARCHAR2(30) := 'SYNC_Supplier_Sites';
196 l_log_msg FND_LOG_MESSAGES.MESSAGE_TEXT%TYPE;
197
198 --
199 l_state ap_supplier_sites_all.state%type;
200 l_province ap_supplier_sites_all.province%type;
201 l_county ap_supplier_sites_all.county%type;
202 l_tca_sync_city ap_supplier_sites_all.tca_sync_city%type;
203 l_tca_sync_zip ap_supplier_sites_all.tca_sync_zip%type;
204 l_tca_sync_country ap_supplier_sites_all.tca_sync_country%type;
205 l_city ap_supplier_sites_all.city%type;
206 l_zip ap_supplier_sites_all.zip%type;
207 l_country ap_supplier_sites_all.country%type;
208 l_address_style ap_supplier_sites_all.address_style%type;
209 l_language ap_supplier_sites_all.language%type;
210 l_address1 ap_supplier_sites_all.address_line1%type;
211 l_address2 ap_supplier_sites_all.address_line2%type;
212 l_address3 ap_supplier_sites_all.address_line3%type;
213 l_address4 ap_supplier_sites_all.address_line4%type;
214 l_address_line_alt ap_supplier_sites_all.address_lines_alt%type;
215
216 l_check_vendor_site_id ap_supplier_sites_all.vendor_site_id%type;
217
218
219 l_duns_number ap_supplier_sites_all.duns_number%type;
220
221 BEGIN
222
223 -- Initialize API return status to success
224 x_return_status := fnd_api.g_ret_sts_success;
225
226 -- Logging Infra: Setting up runtime level
227 G_CURRENT_RUNTIME_LEVEL := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
228
229 -- Logging Infra: Procedure level
230 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
231 l_log_msg := 'Begin of procedure '|| l_procedure_name;
232 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||
233 l_procedure_name||'.begin', l_log_msg);
234 END IF;
235
236 -- Logging Infra: Statement level
237 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
238 l_log_msg := 'Party Site ID '|| x_party_site_id;
239 FND_LOG.STRING(G_LEVEL_STATEMENT, G_MODULE_NAME||
240 '.'||l_procedure_name, l_log_msg);
241 END IF;
242
243 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
244 l_log_msg := 'Location ID '|| x_location_id;
245 FND_LOG.STRING(G_LEVEL_STATEMENT, G_MODULE_NAME||
246 '.'||l_procedure_name, l_log_msg);
247 END IF;
248
249 IF x_location_id IS NOT NULL THEN
250
251 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
252 l_log_msg := 'Selecting Attributes for : '|| x_location_id;
253 FND_LOG.STRING(G_LEVEL_STATEMENT, G_MODULE_NAME||
254 '.'||l_procedure_name, l_log_msg);
255 END IF;
256
257 SELECT hl.state,
258 hl.province,
259 hl.county,
260 hl.city,
261 hl.postal_code,
262 hl.country,
263 substrb(hl.city,1,25),
264 substrb(hl.postal_code,1,20),
265 substrb(hl.country,1,25),
266 hl.address_style,
267 fl.nls_language,
268 hl.address1,
269 hl.address2,
270 hl.address3,
271 hl.address4,
272 hl.address_lines_phonetic
273 INTO l_state,
274 l_province,
275 l_county,
276 l_tca_sync_city,
277 l_tca_sync_zip,
278 l_tca_sync_country,
279 l_city,
280 l_zip,
281 l_country,
282 l_address_style,
283 l_language,
284 l_address1,
285 l_address2,
286 l_address3,
287 l_address4,
288 l_address_line_alt
289 FROM hz_locations hl,
290 fnd_languages fl
291 WHERE hl.language = fl.language_code (+)
292 AND hl.location_id = x_location_id;
293
294 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
295 l_log_msg := 'Check atleast one Supplier Site exist for : '
296 || x_location_id;
297 FND_LOG.STRING(G_LEVEL_STATEMENT, G_MODULE_NAME||
298 '.'||l_procedure_name, l_log_msg);
299 END IF;
300
301 BEGIN
302 SELECT vendor_site_id
303 INTO l_check_vendor_site_id
304 FROM ap_supplier_sites_all
305 WHERE location_id = x_location_id
306 AND rownum = 1;
307 EXCEPTION
308 WHEN OTHERS THEN
309 NULL;
310 END;
311
312 IF l_check_vendor_site_id IS NOT NULL THEN
313
314 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
315 l_log_msg := 'Update Supplier Sites Upgrade Cases: '
316 || x_location_id;
317 FND_LOG.STRING(G_LEVEL_STATEMENT, G_MODULE_NAME||
318 '.'||l_procedure_name, l_log_msg);
319 END IF;
320
321 --
322 -- There are cases where some column lengths are longer in AP
323 -- than in TCA. For the upgrade cases, in order to prevent the
324 -- data loss, we will copy the values of these longer length AP
325 -- column values to the corresponding TCA SYNC columns.
326 -- We will do this only once so that the upgraded value is not lost.
327 -- The columns at the site level that fall into this category are
328 -- state, province and county.
329 --
330
331 UPDATE ap_supplier_sites_all
332 SET tca_sync_state = nvl(tca_sync_state,state),
333 tca_sync_county = nvl(tca_sync_county,county),
334 tca_sync_province = nvl(tca_sync_province,province)
335 WHERE location_id = x_location_id;
336
337 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
338 l_log_msg := 'Update Supplier Sites for : '|| x_location_id;
339 FND_LOG.STRING(G_LEVEL_STATEMENT, G_MODULE_NAME||
340 '.'||l_procedure_name, l_log_msg);
341 END IF;
342
343 UPDATE ap_supplier_sites_all
344 SET state = l_state,
345 province = l_province,
346 county = l_county,
347 tca_sync_city = l_tca_sync_city,
348 tca_sync_zip = l_tca_sync_zip,
349 tca_sync_country = l_tca_sync_country,
350 city = l_city,
351 zip = l_zip,
352 country = l_country,
353 address_style = l_address_style,
354 language = l_language,
355 address_line1 = l_address1,
356 address_line2 = l_address2,
357 address_line3 = l_address3,
358 address_line4 = l_address4,
359 address_lines_alt = l_address_line_alt
360 WHERE location_id = x_location_id;
361
362 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
363 l_log_msg := 'After Update of Site Attributes for : '
364 || x_location_id;
365 FND_LOG.STRING(G_LEVEL_STATEMENT, G_MODULE_NAME||
369 END IF;
366 '.'||l_procedure_name, l_log_msg);
367 END IF;
368 END IF;
370
371 -- Desicon form the PM team states that
372 -- duns number is an attribute that needs to
373 -- be stored at the supplier site and not at
374 -- the aprty site level.
375
376 -- Hence the update and the insert supplier
377 -- site API's would take care to insert the
378 -- duns number directly into the table
379 -- ap_supplier_sites_sites instead of having
380 -- it sync up from the TCA data
381
382 -- Commenting out the code below thus.
383 -- Bug 6388041
384
385 /*
386 IF x_party_site_id IS NOT NULL THEN
387
388 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
389 l_log_msg := 'Selecting Attributes for : '|| x_party_site_id;
390 FND_LOG.STRING(G_LEVEL_STATEMENT, G_MODULE_NAME||
391 '.'||l_procedure_name, l_log_msg);
392 END IF;
393
394 SELECT duns_number_c
395 INTO l_duns_number
396 FROM hz_party_sites
397 WHERE party_site_id = x_party_site_id;
398
399 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
400 l_log_msg := 'Update Supplier Sites for : '|| x_party_site_id;
401 FND_LOG.STRING(G_LEVEL_STATEMENT, G_MODULE_NAME||
402 '.'||l_procedure_name, l_log_msg);
403 END IF;
404
405 UPDATE ap_supplier_sites_all
406 SET duns_number = l_duns_number
407 WHERE party_site_id = x_party_site_id
408 AND EXISTS (SELECT 'Site Exists'
409 FROM ap_supplier_sites_all a
410 WHERE a.party_site_id = x_party_site_id);
411
412 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
413 l_log_msg := 'After Update of Site Attributes for : '|| x_party_site_id;
414 FND_LOG.STRING(G_LEVEL_STATEMENT, G_MODULE_NAME||
415 '.'||l_procedure_name, l_log_msg);
416 END IF;
417
418 END IF;
419 */
420
421 -- Logging Infra: Procedure level
422 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
423 l_log_msg := 'End of procedure '|| l_procedure_name;
424 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||
425 l_procedure_name||'.begin', l_log_msg);
426 END IF;
427
428 EXCEPTION
429
430 WHEN OTHERS THEN
431 IF (SQLCODE <> -20001) THEN
432 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
433 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
434 FND_MESSAGE.SET_TOKEN('PARAMETERS','Party Site ID = '|| x_party_site_id);
435 FND_MESSAGE.SET_TOKEN('PARAMETERS','Location ID = '|| x_location_id);
436 FND_MSG_PUB.Add;
437 END IF;
438 x_return_status := fnd_api.g_ret_sts_error;
439 FND_MSG_PUB.Count_And_Get
440 (p_count=>x_msg_count,
441 p_data=>x_msg_data
442 );
443
444 END SYNC_Supplier_Sites;
445
446 END AP_TCA_SUPPLIER_SYNC_PKG;
[Home] [Help]