1 PACKAGE BODY JL_ZZ_AR_UPLOAD_TAXES AS
2 /* $Header: jlzzutxb.pls 120.2.12010000.1 2009/02/05 07:25:36 nivnaray noship $ */
3
4 -------------------------------------------------------------------------------
5 --Global Variables
6 -------------------------------------------------------------------------------
7
8 g_current_runtime_level NUMBER;
9 g_level_statement CONSTANT NUMBER := FND_LOG.LEVEL_STATEMENT;
10 g_level_exception CONSTANT NUMBER := FND_LOG.LEVEL_EXCEPTION;
11 g_level_unexpected CONSTANT NUMBER := FND_LOG.LEVEL_UNEXPECTED;
12 -- Variables for processing
13
14 l_start_date_max DATE;
15 l_start_date_sec_max DATE;
16 l_end_date_sec_max DATE;
17 l_end_date_max DATE;
18 l_def_tax_code AR_VAT_TAX_ALL.TAX_CODE%TYPE;
19 l_def_tax_rate AR_VAT_TAX_ALL.TAX_RATE%TYPE;
20 l_exist_in_all VARCHAR2(2);
21 l_proper_tax_rate_code VARCHAR2(2);
22 l_exist_in_tmp VARCHAR2(2);
23 l_data_change_flag VARCHAR2(2);
24 l_def_tax_flag VARCHAR2(2);
25
26 PROCEDURE JL_AR_UPDATE_CUST_SITE_TAX
27 (P_TAXPAYER_ID IN NUMBER,
28 P_TAX_TYPE IN VARCHAR2 := 'TURN_BSAS',
29 P_CATEG IN VARCHAR2 := 'TOPBA',
30 P_ORG_ID IN NUMBER,
31 P_PUBLISH_DATE IN DATE,
32 P_START_DATE IN DATE,
33 P_END_DATE IN DATE,
34 X_RETURN_STATUS OUT NOCOPY VARCHAR2) IS
35
36 CURSOR FIND_CT_FOR_TAXPAYER(C_TAXPAYER_ID NUMBER) IS
37 SELECT CUST.CUST_ACCOUNT_ID
38 FROM HZ_PARTIES PARTY,
39 HZ_CUST_ACCOUNTS CUST
40 WHERE CUST.PARTY_ID = PARTY.PARTY_ID
41 AND PARTY.JGZZ_FISCAL_CODE = To_Char(C_TAXPAYER_ID);
42
43 CURSOR GET_SITES_FOR_CT(C_CUSTOMER_ID NUMBER) IS
44 SELECT HZSU.SITE_USE_ID
45 FROM HZ_PARTIES HZP,
46 HZ_CUST_ACCOUNTS HZCA,
47 HZ_CUST_ACCT_SITES HZAS,
48 HZ_CUST_SITE_USES HZSU
49 WHERE HZCA.CUST_ACCOUNT_ID = C_CUSTOMER_ID
50 AND HZCA.PARTY_ID = HZP.PARTY_ID
51 AND HZCA.CUST_ACCOUNT_ID = HZAS.CUST_ACCOUNT_ID
52 AND HZAS.CUST_ACCT_SITE_ID = HZSU.CUST_ACCT_SITE_ID
53 AND HZSU.ORG_ID = P_ORG_ID
54 ORDER BY HZSU.SITE_USE_ID;
55
56 CURSOR GET_DATA_FROM_TMP(C_TAX_PAYER_ID NUMBER) IS
57 SELECT * FROM JL_AR_TURN_UPL_T
58 WHERE TAXPAYER_ID = C_TAX_PAYER_ID;
59
60 l_all_valid_rec GET_DATA_FROM_TMP%ROWTYPE;
61
62 -- Variables to hold the data in final table
63
64 l_publish_date DATE;
65 l_start_date DATE;
66 l_end_date DATE;
67 l_taxpayer_id NUMBER(15);
68 l_contributor_type_code VARCHAR2(1);
69 l_new_contributor_flag VARCHAR2(1);
70 l_rate_change_flag VARCHAR2(1);
71 l_perception_rate NUMBER(15,2);
72 l_wht_rate NUMBER(15,2);
73 l_perception_group_num NUMBER(15);
74 l_wht_group_num NUMBER(15);
75 l_wht_default VARCHAR2(1);
76
77 -- Variables for Final Table updation
78
79 l_site_use_id NUMBER;
80 l_customer_id NUMBER;
81 l_cust_site_use_rec HZ_CUST_ACCOUNT_SITE_V2PUB.CUST_SITE_USE_REC_TYPE;
82 l_old_customer_profile_rec HZ_CUSTOMER_PROFILE_V2PUB.CUSTOMER_PROFILE_REC_TYPE;
83 x_msg_count NUMBER;
84 x_msg_data VARCHAR2(1200);
85 l_obj_version NUMBER;
86
87
88 BEGIN
89 IF g_level_statement >= g_current_runtime_level THEN
90 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_APPLICABLE_TAXES','JL_AR_UPDATE_CUST_SITE_TAX(+)');
91 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_APPLICABLE_TAXES',
92 'I/P - Taxpayer ID: '||P_TAXPAYER_ID
93 ||',Tax Type: '||P_TAX_TYPE
94 ||',Category: '||P_CATEG
95 ||',Org ID: '||P_ORG_ID
96 ||',Start Date: '||P_START_DATE
97 ||',End Date: '||P_END_DATE
98 ||',Publish Date: '||P_PUBLISH_DATE);
99 END IF;
100
101 -- Initialization Section
102
103 X_RETURN_STATUS := 'S';
104 INITIALIZE;
105
106 BEGIN -- Checking whether Taxpayer Exists in Current Month
107 SELECT 'Y'
108 INTO l_exist_in_tmp
109 FROM JL_AR_TURN_UPL_T
110 WHERE TAXPAYER_ID = P_TAXPAYER_ID;
111 EXCEPTION
112 WHEN No_Data_Found THEN
113 l_exist_in_tmp := 'N';
114 WHEN OTHERS THEN
115 X_RETURN_STATUS := 'E';
116 RAISE;
117 END;
118
119 IF g_level_statement >= g_current_runtime_level THEN
120 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_APPLICABLE_TAXES',
124 IF l_exist_in_tmp = 'Y' THEN
121 'Exists in Current Month: '||l_exist_in_tmp);
122 END IF;
123
125 l_def_tax_flag := 'N'; -- SINCE WE WILL NOT ASSIGN THE DEF CODE IN THIS SCENARIO.
126
127 OPEN GET_DATA_FROM_TMP(P_TAXPAYER_ID);
128 FETCH GET_DATA_FROM_TMP INTO l_all_valid_rec;
129
130 l_publish_date := l_all_valid_rec.publish_date;
131 l_start_date := l_all_valid_rec.start_date;
132 l_end_date := l_all_valid_rec.end_date;
133 l_taxpayer_id := l_all_valid_rec.taxpayer_id;
134 l_contributor_type_code := l_all_valid_rec.contributor_type_code;
135 l_new_contributor_flag := l_all_valid_rec.new_contributor_flag;
136 l_rate_change_flag := l_all_valid_rec.rate_change_flag;
137 l_perception_rate := l_all_valid_rec.perception_rate;
138 l_wht_rate := l_all_valid_rec.wht_rate;
139 l_perception_group_num := l_all_valid_rec.perception_group_num;
140 l_wht_group_num := l_all_valid_rec.wht_group_num;
141
142 CLOSE GET_DATA_FROM_TMP;
143
144 BEGIN
145 SELECT 'Y'
146 INTO l_proper_tax_rate_code
147 FROM AR_VAT_TAX VAT,
148 JL_ZZ_AR_TX_CATEG JZ
149 WHERE VAT.ORG_ID = P_ORG_ID
150 AND VAT.GLOBAL_ATTRIBUTE1 = JZ.TAX_CATEGORY_ID
151 AND VAT.ORG_ID = JZ.ORG_ID
152 AND JZ.TAX_CATEGORY = P_CATEG
153 AND VAT.ENABLED_FLAG = 'Y'
154 AND VAT.TAX_CODE = P_TAX_TYPE||'_GRP'||l_perception_group_num
155 AND VAT.TAX_RATE = l_perception_rate
156 AND VAT.START_DATE <= P_START_DATE
157 AND NVL(VAT.END_DATE,TO_DATE('31/12/4092','DD/MM/RRRR'))
158 >= NVL(P_END_DATE,LAST_DAY(P_START_DATE));
159 EXCEPTION
160 WHEN No_Data_Found THEN
161 X_RETURN_STATUS := 'E';
162
163 UPDATE JGZZ_AR_TAX_GLOBAL_TMP
164 SET JG_INFO_V1 = 'JL_AR_AR_GRP_NO_MATCH'
165 WHERE JG_INFO_N1 = P_TAXPAYER_ID
166 AND JG_INFO_D1 = P_START_DATE
167 AND JG_INFO_D2 = P_END_DATE;
168
169 RAISE_APPLICATION_ERROR(- 20999,'Perception Rate and Group Combination was not proper - '|| SQLCODE ||' -ERROR- '|| SQLERRM);
170 WHEN OTHERS THEN
171 X_RETURN_STATUS := 'E';
172 RAISE_APPLICATION_ERROR(- 20999,'Failed during Rate and Group Combination Check - '|| SQLCODE ||' -ERROR- '|| SQLERRM);
173 END;
174
175 -- CHECK WHETHER TAXPAYER ID EXISTS IN THE LAST MONTH FOR COMPARISION.
176
177 BEGIN
178 SELECT Max(JALL.START_DATE),Max(JALL.END_DATE)
179 INTO l_start_date_sec_max,l_end_date_sec_max
180 FROM JL_AR_TURN_UPL_ALL JALL
181 WHERE JALL.TAXPAYER_ID = P_TAXPAYER_ID;
182 IF l_start_date_sec_max IS NOT NULL THEN
183 l_exist_in_all := 'Y'; -- IF EXISTS IN LAST MONTH
184 ELSE
185 l_exist_in_all := 'N';
186 END IF;
187 EXCEPTION
188 WHEN No_Data_Found THEN
189 l_exist_in_all := 'N'; -- IF NOT EXISTS.
190 WHEN OTHERS THEN
191 X_RETURN_STATUS := 'E';
192 RAISE;
193 END;
194
195 IF g_level_statement >= g_current_runtime_level THEN
196 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_APPLICABLE_TAXES',
197 'Max Start Date in All table: '||l_start_date_sec_max
198 ||',Max End Date in All table: '||l_end_date_sec_max
199 ||',Tax Payer ID Exists in Previous Month?: '||l_exist_in_all
200 ||',Tax Rate and Group Info is proper?: '||l_proper_tax_rate_code);
201 END IF;
202
203 /* IF CT IS TRYING UPLOADING THE DATA FOR THE FIRST TIME (OR)
204 THE TAXPAYER ID NOT EXISTS IN THE LAST MONTH THEN INSERT THE RECORDS
205 INTO THE FINAL TABLE AND UPDATE THE CUST SITE WITH THE TAXC0DE GIVEN.*/
206
207 IF l_exist_in_all = 'N' THEN
208 IF Nvl(l_proper_tax_rate_code,'N') = 'Y' THEN
209 l_data_change_flag := 'Y';
210 END IF; -- PROPER TAX RATE CODE CHECK
211 ELSIF l_exist_in_all = 'Y' THEN
212 IF Nvl(l_proper_tax_rate_code,'N') = 'Y' THEN
213 BEGIN
214 SELECT DISTINCT 'Y'
215 INTO l_data_change_flag
216 FROM HZ_PARTIES HZP,
217 HZ_CUST_ACCOUNTS HZCA,
218 HZ_CUST_ACCT_SITES HZAS,
219 HZ_CUST_SITE_USES HZSU
220 WHERE HZP.JGZZ_FISCAL_CODE = To_Char(l_taxpayer_id)
221 AND HZCA.PARTY_ID = HZP.PARTY_ID
222 AND HZCA.CUST_ACCOUNT_ID = HZAS.CUST_ACCOUNT_ID
223 AND HZAS.CUST_ACCT_SITE_ID = HZSU.CUST_ACCT_SITE_ID
224 AND HZSU.ORG_ID = P_ORG_ID
225 AND (HZSU.TAX_CODE IS NULL OR
226 HZSU.TAX_CODE <> P_TAX_TYPE||'_GRP'||l_perception_group_num);
227 EXCEPTION
228 WHEN No_Data_Found THEN
229 l_data_change_flag := 'N';
230 WHEN OTHERS THEN
231 X_RETURN_STATUS := 'E';
232 RAISE;
233 END;
234 END IF; -- PROPER TAX RATE CODE CHECK
235 END IF; -- EXISTS IN ALL TABLE CHECK
236 ELSE -- l_exist_in_tmp IS 'N'
237 -- DO THE PROCESSING FOR THE RECORDS WHICH ARE NOT EXISTS IN CURRENT MONTH
238 -- DATA FILE, BUT EXISTS IN PREVIOS MONTH.
239 -- DERIVING THE DEFAULT TAX CODE AND VAT TAX ID
240 BEGIN
241 SELECT VAT.TAX_CODE, VAT.TAX_RATE
242 INTO l_def_tax_code, l_def_tax_rate
243 FROM AR_VAT_TAX VAT,
244 JL_ZZ_AR_TX_CATEG JZ
245 WHERE VAT.GLOBAL_ATTRIBUTE7 = 'Y'
246 AND VAT.ORG_ID = P_ORG_ID
247 AND VAT.GLOBAL_ATTRIBUTE1 = JZ.TAX_CATEGORY_ID
248 AND VAT.ORG_ID = JZ.ORG_ID
249 AND JZ.TAX_CATEGORY = P_CATEG
250 AND VAT.ENABLED_FLAG = 'Y'
251 AND VAT.START_DATE <= P_START_DATE
252 AND NVL(VAT.END_DATE,TO_DATE('31/12/4092','DD/MM/RRRR'))
253 >= NVL(P_END_DATE,LAST_DAY(P_START_DATE));
254 EXCEPTION
255 WHEN No_Data_Found THEN
256 X_RETURN_STATUS := 'E';
257
258 UPDATE JGZZ_AR_TAX_GLOBAL_TMP
259 SET JG_INFO_V1 = 'JL_AR_AR_NO_DFLT_FLAG_SET'
260 WHERE JG_INFO_N1 = P_TAXPAYER_ID
261 AND JG_INFO_D1 = P_START_DATE
262 AND JG_INFO_D2 = P_END_DATE;
263
264 RAISE_APPLICATION_ERROR(- 20999,'Define Atleast one Default Tax Code - '|| SQLCODE ||' -ERROR- '|| SQLERRM);
265 WHEN OTHERS THEN
266 X_RETURN_STATUS := 'E';
267 RAISE_APPLICATION_ERROR(- 20999,'Failed when fetching the Default Tax Code - '|| SQLCODE ||' -ERROR- '|| SQLERRM);
268 END;
269
270 IF g_level_statement >= g_current_runtime_level THEN
271 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_APPLICABLE_TAXES',
272 'Default Tax Code: '||l_def_tax_code);
273 END IF;
274
275 l_def_tax_flag := 'Y';
276 l_data_change_flag := 'Y';
277
278 -- GET THE VALUES TO INSERT THE RECORD INTO _ALL TABLE.
279 l_publish_date := P_PUBLISH_DATE;
280 l_start_date := P_START_DATE;
281 l_end_date := P_END_DATE;
282 l_taxpayer_id := P_TAXPAYER_ID;
283 l_perception_group_num := SubStr(l_def_tax_code,(Length(P_TAX_TYPE)+5));
284
285 IF g_level_statement >= g_current_runtime_level THEN
286 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_APPLICABLE_TAXES',
287 'Perception Group Number: '||l_perception_group_num);
288 END IF;
289
290 BEGIN
291 SELECT Max(JALL.START_DATE),Max(JALL.END_DATE)
292 INTO l_start_date_sec_max,l_end_date_sec_max
293 FROM JL_AR_TURN_UPL_ALL JALL
294 WHERE JALL.TAXPAYER_ID = P_TAXPAYER_ID;
295
296 SELECT CONTRIBUTOR_TYPE_CODE,
297 NEW_CONTRIBUTOR_FLAG,
298 PERCEPTION_RATE,
299 WHT_RATE,
300 WHT_GROUP_NUM
301 INTO l_contributor_type_code,
302 l_new_contributor_flag,
303 l_perception_rate,
304 l_wht_rate,
305 l_wht_group_num
306 FROM JL_AR_TURN_UPL_ALL
307 WHERE TAXPAYER_ID = P_TAXPAYER_ID
308 AND START_DATE = l_start_date_sec_max
309 AND END_DATE = l_end_date_sec_max;
310 EXCEPTION
311 WHEN No_Data_Found THEN
312 X_RETURN_STATUS := 'E';
313 RAISE_APPLICATION_ERROR(- 20999,'Taxpayer ID not exists in both tmp and all table - '|| SQLCODE ||' -ERROR- '|| SQLERRM);
314 WHEN OTHERS THEN
315 X_RETURN_STATUS := 'E';
316 RAISE_APPLICATION_ERROR(- 20999,'Failed when fetching the data from ALL table for given Taxpayer: '
317 ||l_taxpayer_id||' - '|| SQLCODE ||' -ERROR- '|| SQLERRM);
318 END;
319
320 IF l_perception_rate <> l_def_tax_rate THEN
321 l_rate_change_flag := 'S';
322 l_perception_rate := l_def_tax_rate;
323 ELSE
324 l_rate_change_flag := 'N';
325 END IF;
326
327 END IF; -- TAX PAYER ID CHECK IN TMP TABLE
328
329 BEGIN
330 JL_AR_APPLICABLE_TAXES.Insert_Row( l_publish_date,
331 l_start_date,
332 l_end_date,
333 l_taxpayer_id,
334 l_contributor_type_code,
335 l_new_contributor_flag,
336 l_rate_change_flag,
337 l_perception_rate,
338 l_wht_rate,
339 l_perception_group_num,
340 l_wht_group_num,
341 l_def_tax_flag,
342 'AR');
343 EXCEPTION
344 WHEN OTHERS THEN
345 IF SQLCODE = 0 THEN
346 IF g_level_statement >= g_current_runtime_level THEN
347 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_APPLICABLE_TAXES',
348 'Normal Completion Exception');
349 END IF;
350 ELSE
351 X_RETURN_STATUS := 'E';
352 RAISE_APPLICATION_ERROR(-20999,'Failed when inserting the details for given Taxpayer: '
353 ||l_taxpayer_id||' - '||SQLCODE||'- ERROR - '||SQLERRM);
354 END IF;
355 END;
356 IF g_level_statement >= g_current_runtime_level THEN
357 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_APPLICABLE_TAXES',
358 'Data Change Flag: '||l_data_change_flag);
359 END IF;
360 IF l_data_change_flag = 'Y' THEN
361 -- UPDATE THE TAX CODE AT SITE LEVEL.
362 OPEN FIND_CT_FOR_TAXPAYER(l_taxpayer_id);
363 LOOP
364 FETCH FIND_CT_FOR_TAXPAYER INTO l_customer_id;
365 IF g_level_statement >= g_current_runtime_level THEN
366 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_APPLICABLE_TAXES',
367 'Customer Account ID for given Taxpayer ID: '||l_customer_id);
368 END IF;
369 EXIT WHEN FIND_CT_FOR_TAXPAYER%NOTFOUND;
370
371 OPEN GET_SITES_FOR_CT(l_customer_id);
372 LOOP
373 FETCH GET_SITES_FOR_CT INTO l_site_use_id;
374 EXIT WHEN GET_SITES_FOR_CT%NOTFOUND;
375 IF g_level_statement >= g_current_runtime_level THEN
376 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_APPLICABLE_TAXES',
377 'Updated Site Use Ids:'||l_site_use_id);
378 END IF;
379 -- UPDATE THE TAX CODE AT SITE LEVELS.
380 HZ_CUST_ACCOUNT_SITE_V2PUB.get_cust_site_use_rec (
381 p_site_use_id => l_site_use_id,
382 x_cust_site_use_rec => l_cust_site_use_rec,
383 x_customer_profile_rec => l_old_customer_profile_rec,
384 x_return_status => x_return_status,
385 x_msg_count => x_msg_count,
386 x_msg_data => x_msg_data);
387
388 IF g_level_statement >= g_current_runtime_level THEN
389 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_APPLICABLE_TAXES',
390 'Return Status after getting the site use info:'||x_return_status);
391 END IF;
392 IF x_return_status = 'S' THEN
393 BEGIN
394 l_cust_site_use_rec.tax_code := P_TAX_TYPE||'_GRP'||l_perception_group_num;
395 IF g_level_statement >= g_current_runtime_level THEN
396 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_APPLICABLE_TAXES',
397 'Updating the Site with Tax Code: '||l_cust_site_use_rec.tax_code);
398 END IF;
399
400 BEGIN
401 SELECT OBJECT_VERSION_NUMBER
402 INTO l_obj_version
403 FROM HZ_CUST_SITE_USES
404 WHERE SITE_USE_ID = l_site_use_id;
405 EXCEPTION
406 WHEN OTHERS THEN
407 l_obj_version := NULL;
408 END;
409 IF g_level_statement >= g_current_runtime_level THEN
410 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_APPLICABLE_TAXES',
411 'Object Version for this Site: '||l_obj_version);
412 END IF;
413
414 HZ_CUST_ACCOUNT_SITE_V2PUB.update_cust_site_use (
415 FND_API.G_TRUE,
416 l_cust_site_use_rec,
417 l_obj_version,
418 x_return_status,
419 x_msg_count,
420 x_msg_data );
421 EXCEPTION
422 WHEN OTHERS THEN
423 X_RETURN_STATUS := 'E';
424 RAISE_APPLICATION_ERROR(-20999,'Failed when Updating Site Use Id: '
425 ||l_site_use_id||'WITH - '||SQLCODE||'-ERROR-'||SQLERRM);
426 END;
427 ELSE
428 X_RETURN_STATUS := 'E';
429 RAISE_APPLICATION_ERROR(-20999,'Failed when picking the data for Site Use Id: '
430 ||l_site_use_id||'WITH - '||SQLCODE||'-ERROR-'||SQLERRM);
431 END IF;
432 END LOOP;
433 CLOSE GET_SITES_FOR_CT;
434 END LOOP;
435 CLOSE FIND_CT_FOR_TAXPAYER;
436 END IF;
437 -- COMMIT;
438 IF g_level_statement >= g_current_runtime_level THEN
439 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_APPLICABLE_TAXES','JL_AR_UPDATE_CUST_SITE_TAX(-)');
440 END IF;
441 END JL_AR_UPDATE_CUST_SITE_TAX;
442
443 PROCEDURE INITIALIZE IS
444 BEGIN
445 IF g_level_statement >= g_current_runtime_level THEN
446 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_APPLICABLE_TAXES','Initialize(+)');
447 END IF;
448 l_start_date_max := NULL;
449 l_start_date_sec_max := NULL;
450 l_end_date_sec_max := NULL;
451 l_end_date_max := NULL;
452 l_def_tax_code := NULL;
453 l_def_tax_rate := NULL;
454 l_exist_in_all := NULL;
455 l_proper_tax_rate_code := NULL;
456 l_exist_in_tmp := NULL;
457 l_data_change_flag := NULL;
458 l_def_tax_flag := NULL;
459 IF g_level_statement >= g_current_runtime_level THEN
460 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_APPLICABLE_TAXES','Initialize(-)');
461 END IF;
462 END INITIALIZE;
463
464 END JL_ZZ_AR_UPLOAD_TAXES;
[Home] [Help]