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