[Home] [Help]
PACKAGE BODY: APPS.JL_AR_APPLICABLE_TAXES
Source
1 PACKAGE BODY JL_AR_APPLICABLE_TAXES AS
2 /* $Header: jlarpatb.pls 120.8.12020000.2 2013/01/03 14:42:39 mbarrett ship $ */
3
4 -------------------------------------------------------------------------------
5 --Global Variables
6 -------------------------------------------------------------------------------
7
8 -- Bug 14563117 Start
9 p_debug_log VARCHAR2(1) := NVL(FND_PROFILE.value('AFLOG_ENABLED'), 'N');
10 -- Bug 14563117 End
11 l_AWT_TAX_TYPE VARCHAR2(10) := 'TURN_BSAS';
12 l_PERCEPTION_TAX_TYPE VARCHAR2(10) := 'TOPBA';
13 l_ORG_ID NUMBER := MO_GLOBAL.get_current_org_id; --bug 9682967
14
15 g_current_runtime_level CONSTANT NUMBER := fnd_log.g_current_runtime_level;
16 g_level_statement CONSTANT NUMBER := fnd_log.level_statement;
17 g_level_procedure CONSTANT NUMBER := fnd_log.level_procedure;
18 g_level_event CONSTANT NUMBER := fnd_log.level_event;
19 g_level_exception CONSTANT NUMBER := fnd_log.level_exception;
20 g_level_error CONSTANT NUMBER := fnd_log.level_error;
21 g_level_unexpected CONSTANT NUMBER := fnd_log.level_unexpected;
22 l_RETURN_STATUS VARCHAR2(1);
23 l_taxpayer VARCHAR2(1) := 'N';
24 l_taxpayer_ar VARCHAR2(1) := 'N';
25
26
27 PROCEDURE Insert_Row (l_PUBLISH_DATE DATE,
28 l_START_DATE DATE,
29 l_END_DATE DATE,
30 l_TAXPAYER_ID NUMBER,
31 l_CONTRIBUTOR_TYPE_CODE VARCHAR2,
32 l_NEW_CONTRIBUTOR_FLAG VARCHAR2,
33 l_RATE_CHANGE_FLAG VARCHAR2,
34 l_PERCEPTION_RATE NUMBER,
35 l_WHT_RATE NUMBER,
36 l_PERCEPTION_GROUP_NUM NUMBER,
37 l_WHT_GROUP_NUM NUMBER,
38 l_WHT_DEFAULT_FLAG VARCHAR2,
39 l_CALLING_RESP VARCHAR2
40 ) IS
41
42
43 final_insert_check VARCHAR2(1) := 'N';
44 l_created_by NUMBER(15) := NVL(fnd_profile.value('USER_ID'), 1);
45 l_creation_DATE DATE := SYSDATE;
46 l_last_UPDATEd_by NUMBER(15) := NVL(fnd_profile.value('USER_ID'), 1);
47 l_last_UPDATE_DATE DATE := SYSDATE;
48 l_last_UPDATE_login NUMBER(15) := NVL(fnd_global.conc_login_id, 1);
49
50
51 BEGIN
52
53 BEGIN
54 SELECT 'Y' INTO final_insert_check FROM JL_AR_TURN_UPL
55 WHERE TAXPAYER_ID = l_TAXPAYER_ID
56 AND START_DATE = l_START_DATE
57 AND END_DATE = l_END_DATE;
58
59 EXCEPTION
60 WHEN NO_DATA_FOUND THEN
61 final_insert_check := 'N';
62 END;
63
64 IF p_debug_log = 'Y' THEN
65 FND_FILE.put_line( FND_FILE.LOG, 'In Insert_Row, value of final_insert_check'|| final_insert_check);
66 END IF;
67
68 IF final_insert_check = 'N' THEN
69
70 INSERT INTO JL_AR_TURN_UPL (
71 ORG_ID,
72 PUBLISH_DATE,
73 START_DATE,
74 END_DATE,
75 TAXPAYER_ID,
76 CONTRIBUTOR_TYPE_CODE,
77 NEW_CONTRIBUTOR_FLAG,
78 RATE_CHANGE_FLAG,
79 PERCEPTION_RATE,
80 WHT_RATE,
81 PERCEPTION_GROUP_NUM,
82 WHT_GROUP_NUM,
83 WHT_DEFAULT_FLAG,
84 LAST_UPDATE_DATE,
85 LAST_UPDATED_BY,
86 LAST_UPDATE_LOGIN,
87 CREATION_DATE,
88 CREATED_BY)
89 VALUES (
90 l_ORG_ID, --ORG_ID
91 l_PUBLISH_DATE, --PUBLISH_DATE
92 l_START_DATE, --START_DATE
93 l_END_DATE, --END_DATE
94 l_TAXPAYER_ID, --TAXPAYER_ID
95 l_CONTRIBUTOR_TYPE_CODE, --CONTRIBUTOR_TYPE_CODE
96 l_NEW_CONTRIBUTOR_FLAG, --NEW_CONTRIBUTOR_FLAG
97 l_RATE_CHANGE_FLAG, --RATE_CHANGE_FLAG
98 l_PERCEPTION_RATE, --PERCEPTION_RATE
99 l_WHT_RATE, --WHT_RATE
100 l_PERCEPTION_GROUP_NUM, --PERCEPTION_GROUP_NUM
101 l_WHT_GROUP_NUM, --WHT_GROUP_NUM
102 l_WHT_DEFAULT_FLAG, --WHT_DEFAULT_FLAG
103 l_last_UPDATE_DATE, --LAST_UPDATE_DATE
104 l_last_UPDATEd_by, --LAST_UPDATED_BY
105 l_last_UPDATE_login, --LAST_UPDATE_LOGIN
106 l_creation_DATE, --CREATION_DATE
107 l_created_by); --CREATED_BY
108
109 IF p_debug_log = 'Y' THEN
110 FND_FILE.put_line( FND_FILE.LOG, 'In Insert_Row, inserted record into JL_AR_TURN_UPL');
111 END IF;
112
113 ELSE
114
115 IF l_CALLING_RESP = 'AP' THEN
116
117 UPDATE JL_AR_TURN_UPL SET WHT_RATE = l_WHT_RATE, WHT_GROUP_NUM = l_WHT_GROUP_NUM, WHT_DEFAULT_FLAG = l_WHT_DEFAULT_FLAG
118 WHERE TAXPAYER_ID = l_TAXPAYER_ID
119 AND START_DATE = l_START_DATE
120 AND END_DATE = l_END_DATE;
121
122 IF p_debug_log = 'Y' THEN
123 FND_FILE.put_line( FND_FILE.LOG, 'UPDATED record in JL_AR_TURN_UPL for Payables');
124 END IF;
125
126
127 ELSIF l_CALLING_RESP = 'AR' THEN
128
129 UPDATE JL_AR_TURN_UPL SET PERCEPTION_RATE = l_PERCEPTION_RATE, PERCEPTION_GROUP_NUM = l_PERCEPTION_GROUP_NUM
130 WHERE TAXPAYER_ID = l_TAXPAYER_ID
131 AND START_DATE = l_START_DATE
132 AND END_DATE = l_END_DATE;
133
134 IF p_debug_log = 'Y' THEN
135 FND_FILE.put_line( FND_FILE.LOG, 'UPDATED record in JL_AR_TURN_UPL for Receivables');
136 END IF;
137
138 END IF;
139
140 END IF;
141
142
143 EXCEPTION
144 WHEN OTHERS THEN
145 IF p_debug_log = 'Y' THEN
146 FND_FILE.put_line( FND_FILE.LOG,'AN ERROR IS ENCOUNTERED WHILE INSERTING INTO FINAL TABLE '|| SQLCODE || 'ERROR' || SQLERRM);
147 END IF;
148 END Insert_Row;
149
150
151
152
153
154 FUNCTION FORMAT_DATE(INPUT_DATE IN DATE)
155 RETURN DATE
156 IS
157
158 l_DATE DATE;
159
160 BEGIN
161
162 l_DATE := TO_DATE(INPUT_DATE, 'DD/MM/YYYY');
163 RETURN l_DATE;
164
165 EXCEPTION
166 WHEN OTHERS THEN
167 IF p_debug_log = 'Y' THEN
168 FND_FILE.put_line( FND_FILE.LOG,'AN ERROR IS ENCOUNTERED WHEN VALIDATING DATE'|| SQLCODE ||' -ERROR- '|| SQLERRM);
169 END IF;
170 END FORMAT_DATE;
171
172
173
174
175
176 FUNCTION VALID_NUMBER(INPUT_NUM IN NUMBER)
177 RETURN BOOLEAN
178 IS
179 l_valid_num NUMBER;
180
181 BEGIN
182 l_valid_num := TO_NUMBER(INPUT_NUM);
183 RETURN TRUE;
184
185 EXCEPTION
186 WHEN OTHERS THEN
187 IF p_debug_log = 'Y' THEN
188 FND_FILE.put_line( FND_FILE.LOG,'AN ERROR IS ENCOUNTERED WHEN VALIDATING NUMBER'|| SQLCODE ||' -ERROR- '|| SQLERRM);
189 END IF;
190 RETURN FALSE;
191 END VALID_NUMBER;
192
193
194
195
196
197 -- Bug 14563117 Start
198 FUNCTION BASIC_VALIDATION(P_TEMPREC IN JL_AR_TURN_UPL_T%ROWTYPE)
199 -- Bug 14563117 Start
200 RETURN BOOLEAN
201 IS
202
203 l_PUBLISH_DATE DATE;
204 l_START_DATE DATE;
205 l_END_DATE DATE;
206 l_TAXPAYER_ID NUMBER(15);
207 l_CONTRIBUTOR_TYPE_CODE VARCHAR2(1);
208 l_NEW_CONTRIBUTOR_FLAG VARCHAR2(1);
209 l_RATE_CHANGE_FLAG VARCHAR2(1);
210 l_PERCEPTION_RATE NUMBER(15,2);
211 l_WHT_RATE NUMBER(15,2);
212 l_PERCEPTION_GROUP_NUM NUMBER(15);
213 l_WHT_GROUP_NUM NUMBER(15);
214
215 l_PUBLISHDATE_ALL DATE;
216 valid_flag VARCHAR2(1) := 'Y';
217
218 -- Bug 14563117 Start
219 --CURSOR C2 IS SELECT * FROM JL_AR_TURN_UPL_T WHERE TAXPAYER_ID = l_TAXPAYERID;
220
221 --V_TEMPREC C2%ROWTYPE;
222
223 -- Bug 14563117 End
224
225
226 BEGIN
227
228 -- Bug 14563117 Start
229 -- OPEN C2;
230 -- LOOP
231 -- FETCH C2 INTO V_TEMPREC;
232 -- EXIT WHEN C2%NOTFOUND;
233
234 -- Bug 14563117 End
235 l_PUBLISH_DATE := P_TEMPREC.PUBLISH_DATE;
236 l_START_DATE := P_TEMPREC.START_DATE;
237 l_END_DATE := P_TEMPREC.END_DATE;
238
239 l_TAXPAYER_ID := P_TEMPREC.TAXPAYER_ID;
240 l_CONTRIBUTOR_TYPE_CODE := P_TEMPREC.CONTRIBUTOR_TYPE_CODE;
241 l_NEW_CONTRIBUTOR_FLAG := P_TEMPREC.NEW_CONTRIBUTOR_FLAG;
242 l_RATE_CHANGE_FLAG := P_TEMPREC.RATE_CHANGE_FLAG;
243 l_PERCEPTION_RATE := P_TEMPREC.PERCEPTION_RATE;
244 l_WHT_RATE := P_TEMPREC.WHT_RATE;
245 l_PERCEPTION_GROUP_NUM := P_TEMPREC.PERCEPTION_GROUP_NUM;
246 l_WHT_GROUP_NUM := P_TEMPREC.WHT_GROUP_NUM;
247
248
249 BEGIN
250
251 SELECT MAX(PUBLISH_DATE) INTO l_PUBLISHDATE_ALL FROM JL_AR_TURN_UPL
252 WHERE TAXPAYER_ID = l_TAXPAYER_ID;
253
254 IF (l_PUBLISH_DATE < l_PUBLISHDATE_ALL) AND (l_START_DATE > l_END_DATE) THEN
255 valid_flag := 'N';
256 IF p_debug_log = 'Y' THEN
257 FND_FILE.PUT_LINE( FND_FILE.LOG,'RECORD FAILED DURING DATE CHECK VALIDATION');
258 END IF;
259 END IF;
260
261 EXCEPTION
262 WHEN OTHERS THEN
263 IF p_debug_log = 'Y' THEN
264 FND_FILE.PUT_LINE( FND_FILE.LOG,'NO PREVIOUS RECORD IN JL_AR_TURN_UPL');
265 END IF;
266 END;
267
268 IF VALID_NUMBER(P_TEMPREC.TAXPAYER_ID) THEN
269 l_TAXPAYER_ID := TO_NUMBER(P_TEMPREC.TAXPAYER_ID, '99999999999');
270 ELSE
271 valid_flag := 'N';
272 IF p_debug_log = 'Y' THEN
273 FND_FILE.PUT_LINE( FND_FILE.LOG,'RECORD FAILED IN TAXPAYER ID (NUMBER) CHECK VALIDATION');
274 END IF;
275 END IF;
276
277
278 -- AP tax payer id available check
279 -- Bug 14563117 Start
280 DECLARE
281 Cursor C_TaxPayer Is
282 -- SELECT DISTINCT 'Y' taxpayer FROM PO_VENDORS PV, PER_ALL_PEOPLE_F PAPF
283 SELECT /*+ cardinality(PV.PAV,1) */ 'Y' taxpayer FROM PO_VENDORS PV, PER_ALL_PEOPLE_F PAPF
284 -- Bug 14563117 End
285 WHERE NVL(pv.employee_id, - 99) = papf.person_id (+)
286 AND NVL(papf.EFFECTIVE_START_DATE, SYSDATE) <= SYSDATE
287 AND NVL(papf.EFFECTIVE_END_DATE, SYSDATE) >= SYSDATE
288 --bug 8530918 AND NVL(papf.national_identifier, NVL(pv.individual_1099, pv.num_1099)) = TO_CHAR(l_TAXPAYER_ID);
289 AND rtrim(
290 substr(
291 replace(
292 nvl(papf.national_identifier,
293 nvl(pv.individual_1099,pv.num_1099)
294 ),
295 '-'),
296 1,10)
297 ) ||
298 substr(pv.global_attribute12,1,1) = TO_CHAR(l_TAXPAYER_ID)
299 -- Bug 14563117 Start
300 AND pv.global_attribute12 like substr(l_TAXPAYER_ID,-1)||'%'
301 AND rownum = 1;
302 -- Bug 14563117 End
303
304 BEGIN
305 -- Bug 14563117 Start
306 l_taxpayer := 'N';
307 For C_TaxPayerRec in C_TaxPayer Loop
308 l_taxpayer := C_TaxPayerRec.taxpayer;
309 End Loop;
310 -- Bug 14563117 End
311 EXCEPTION
312
313 -- Bug 14563117 Start
314 --WHEN NO_DATA_FOUND THEN
315 -- l_taxpayer := 'N';
316 -- Bug 14563117 End
317
318 WHEN OTHERS THEN
319 IF p_debug_log = 'Y' THEN
320 FND_FILE.PUT_LINE( FND_FILE.LOG,'AN ERROR WAS ENCOUNTERED IN TAX_PAYER VALIDATION FOR AP '|| SQLCODE || 'ERROR' || SQLERRM);
321 END IF;
322 RETURN FALSE; -- fetch next record
323
324 END;
325
326 -- AR tax payer id available check
327
328 BEGIN
329
330 SELECT DISTINCT 'Y' INTO l_taxpayer_ar
331 FROM HZ_PARTIES HZP,
332 HZ_CUST_ACCOUNTS_ALL HZCA,
333 HZ_CUST_ACCT_SITES_ALL HZAS,
334 HZ_CUST_SITE_USES_ALL HZSU
335 WHERE HZCA.PARTY_ID = HZP.PARTY_ID
336 AND HZCA.CUST_ACCOUNT_ID = HZAS.CUST_ACCOUNT_ID
337 AND HZAS.CUST_ACCT_SITE_ID = HZSU.CUST_ACCT_SITE_ID
338 AND HZSU.ORG_ID = l_ORG_ID
339 AND HZP.JGZZ_FISCAL_CODE = TO_CHAR(l_TAXPAYER_ID);
340
341 EXCEPTION
342 WHEN NO_DATA_FOUND THEN
343 l_taxpayer_ar := 'N';
344
345 WHEN OTHERS THEN
346 IF p_debug_log = 'Y' THEN
347 FND_FILE.PUT_LINE( FND_FILE.LOG,'AN ERROR WAS ENCOUNTERED IN TAX_PAYER VALIDATION FOR AR'|| SQLCODE || 'ERROR' || SQLERRM);
348 END IF;
349 RETURN FALSE; -- fetch next record
350
351 END;
352
353
354
355 IF l_taxpayer = 'N' AND l_taxpayer_ar = 'N' THEN
356 IF p_debug_log = 'Y' THEN
357 FND_FILE.put_line( FND_FILE.LOG,'TAXPAYER ID IS NOT AVAILABLE');
358 END IF;
359 UPDATE JGZZ_AR_TAX_GLOBAL_TMP SET JG_INFO_V1 = 'JLZZ_TAXPAYER_ID_NOT_AVAILABLE'
360 WHERE JG_INFO_N1 = l_TAXPAYER_ID AND JG_INFO_D1 = l_START_DATE AND JG_INFO_D2 = l_END_DATE;
361 RETURN FALSE;
362
363 END IF;
364
365 l_CONTRIBUTOR_TYPE_CODE := P_TEMPREC.CONTRIBUTOR_TYPE_CODE;
366 l_NEW_CONTRIBUTOR_FLAG := P_TEMPREC.NEW_CONTRIBUTOR_FLAG;
367 l_RATE_CHANGE_FLAG := P_TEMPREC.RATE_CHANGE_FLAG;
368
369
370 IF (l_CONTRIBUTOR_TYPE_CODE NOT IN ('D', 'C')) THEN
371 valid_flag := 'N';
372 IF p_debug_log = 'Y' THEN
373 FND_FILE.PUT_LINE( FND_FILE.LOG,'RECORD FAILED DURING CONTRIBUTOR TYPE CHECK VALIDATION');
374 END IF;
375
376 ELSIF (l_NEW_CONTRIBUTOR_FLAG NOT IN ('S', 'N', 'B')) THEN
377 valid_flag := 'N';
378 IF p_debug_log = 'Y' THEN
379 FND_FILE.PUT_LINE( FND_FILE.LOG,'RECORD FAILED DURING NEW CONTRIBUTOR CHECK VALIDATION');
380 END IF;
381
382 ELSIF (l_RATE_CHANGE_FLAG NOT IN ('S', 'N')) THEN
383 valid_flag := 'N';
384 IF p_debug_log = 'Y' THEN
385 FND_FILE.PUT_LINE( FND_FILE.LOG,'RECORD FAILED DURING RATE CHANGE FLAG CHECK VALIDATION');
386 END IF;
387
388 ELSE
389 valid_flag := 'Y';
390 END IF;
391
392
393 IF VALID_NUMBER(P_TEMPREC.PERCEPTION_RATE) THEN
394 l_PERCEPTION_RATE := TO_NUMBER(P_TEMPREC.PERCEPTION_RATE);
395
396 ELSE
397 valid_flag := 'N';
398 IF p_debug_log = 'Y' THEN
399 FND_FILE.PUT_LINE( FND_FILE.LOG,'RECORD FAILED DURING PERCEPTION_RATE CHECK VALIDATION');
400 END IF;
401
402 END IF;
403
404
405 IF VALID_NUMBER(P_TEMPREC.WHT_RATE) THEN
406 l_WHT_RATE := TO_NUMBER(P_TEMPREC.WHT_RATE);
407
408 ELSE
409 valid_flag := 'N';
410 IF p_debug_log = 'Y' THEN
411 FND_FILE.PUT_LINE( FND_FILE.LOG,'RECORD FAILED DURING WHT_RATE CHECK VALIDATION');
412 END IF;
413
414 END IF;
415
416
417 IF VALID_NUMBER(NVL(P_TEMPREC.PERCEPTION_GROUP_NUM, 0)) THEN
418 l_PERCEPTION_GROUP_NUM := TO_NUMBER(NVL(P_TEMPREC.PERCEPTION_GROUP_NUM, 0), '99');
419
420 ELSE
421 valid_flag := 'N';
422 IF p_debug_log = 'Y' THEN
423 FND_FILE.PUT_LINE( FND_FILE.LOG,'RECORD FAILED DURING PERCEPTION_GROUP_NUM CHECK VALIDATION');
424 END IF;
425
426 END IF;
427
428
429 IF VALID_NUMBER(NVL(P_TEMPREC.WHT_GROUP_NUM, 0)) THEN
430 l_WHT_GROUP_NUM := TO_NUMBER(NVL(P_TEMPREC.WHT_GROUP_NUM, 0), '99');
431
432 ELSE
433 valid_flag := 'N';
434 IF p_debug_log = 'Y' THEN
435 FND_FILE.PUT_LINE( FND_FILE.LOG,'RECORD FAILED DURING WHT_GROUP_NUM CHECK VALIDATION');
436 END IF;
437
438 END IF;
439
440
441 IF (valid_flag = 'Y') THEN
442 RETURN TRUE;
443
444 ELSIF (valid_flag = 'N') THEN
445 IF p_debug_log = 'Y' THEN
446 FND_FILE.PUT_LINE( FND_FILE.LOG,'RECORD FAILED DURING BASIC CHECK VALIDATION, PLEASE REFER LOG FILE/REPORT ');
447 END IF;
448 UPDATE JGZZ_AR_TAX_GLOBAL_TMP SET JG_INFO_V1 = 'JLZZ_RECORD_FAIL_BASIC_CHECK'
449 WHERE JG_INFO_N1 = l_TAXPAYER_ID AND JG_INFO_D1 = l_START_DATE AND JG_INFO_D2 = l_END_DATE;
450
451 RETURN FALSE;
452 END IF;
453
454
455 -- Bug 14563117 Start
456 -- END LOOP;
457 -- CLOSE C2;
458 -- Bug 14563117 Start
459
460 EXCEPTION
461 WHEN OTHERS THEN
462 IF p_debug_log = 'Y' THEN
463 FND_FILE.PUT_LINE( FND_FILE.LOG,'AN ERROR WAS ENCOUNTERED DURING BASIC CHECK VALIDATION'|| SQLCODE || 'ERROR' || SQLERRM);
464 END IF;
465 UPDATE JGZZ_AR_TAX_GLOBAL_TMP SET JG_INFO_V1 = 'JLZZ_RECORD_FAIL_BASIC_CHECK'
466 WHERE JG_INFO_N1 = l_TAXPAYER_ID AND JG_INFO_D1 = l_START_DATE AND JG_INFO_D2 = l_END_DATE;
467
468 RETURN FALSE;
469
470 END BASIC_VALIDATION;
471
472
473
474
475
476
477
478 PROCEDURE FINAL_VALIDATION
479 IS
480
481 l_PUBLISH_DATE DATE;
482 l_START_DATE DATE;
483 l_END_DATE DATE;
484 l_TAXPAYER_ID NUMBER(15);
485 l_CONTRIBUTOR_TYPE_CODE VARCHAR2(1);
486 l_NEW_CONTRIBUTOR_FLAG VARCHAR2(1);
487 l_RATE_CHANGE_FLAG VARCHAR2(1);
488 l_PERCEPTION_RATE NUMBER(15,2);
489 l_WHT_RATE NUMBER(15,2);
490 l_PERCEPTION_GROUP_NUM NUMBER(15);
491 l_WHT_GROUP_NUM NUMBER(15);
492 l_WHT_DEFAULT_FLAG VARCHAR2(1) := 'N';
493
494 l_TAX_NAME_AP_AWT NUMBER(15);
495 l_TAX_RATE_AP_AWT NUMBER(15);
496 l_EFFECTIVE_START_DATE DATE;
497 l_EFFECTIVE_END_DATE DATE;
498
499 l_WHT_RATE_ALL AP_AWT_TAX_RATES_ALL.TAX_RATE%TYPE;
500 l_WHT_GROUP_NUM_ALL AP_AWT_TAX_RATES_ALL.TAX_NAME%TYPE;
501
502 l_SUPP_AWT_CODE_ID_CD JL_ZZ_AP_SUP_AWT_CD_ALL.SUPP_AWT_CODE_ID%TYPE;
503 l_SUPP_AWT_TYPE_ID_CD JL_ZZ_AP_SUP_AWT_CD_ALL.SUPP_AWT_TYPE_ID%TYPE;
504 l_SUPP_AWT_CODE_ID_SEQ JL_ZZ_AP_SUP_AWT_CD_ALL.SUPP_AWT_CODE_ID%TYPE;
505 l_SUPP_AWT_TYPE_ID_SEQ JL_ZZ_AP_SUP_AWT_CD_ALL.SUPP_AWT_TYPE_ID%TYPE;
506 l_tax_id ap_tax_codes.tax_id%TYPE;
507 l_SUPP_AWT_TYPE_ID_TYPES JL_ZZ_AP_SUPP_AWT_TYPES.SUPP_AWT_TYPE_ID%TYPE;
508 l_VENDOR_ID PO_VENDORS.VENDOR_ID%TYPE;
509 l_INV_DISTRIB_AWT_ID_INV JL_ZZ_AP_INV_DIS_WH_ALL.INV_DISTRIB_AWT_ID%TYPE;
510 l_INVOICE_ID_INA AP_INVOICES_ALL.INVOICE_ID%TYPE;
511 l_DISTRIBUTION_LINE_NUMBER_IND AP_INVOICE_DISTRIBUTIONS_ALL.DISTRIBUTION_LINE_NUMBER%TYPE;
512 l_INVOICE_DISTRIBUTION_ID_IND AP_INVOICE_DISTRIBUTIONS_ALL.INVOICE_DISTRIBUTION_ID%TYPE;
513 l_START_DATE_SEC_MAX DATE := NULL;
514 l_END_DATE_SEC_MAX DATE := NULL;
515 l_EFFECTIVE_START_DATE_COMP DATE;
516 l_INV_DISTRIB_AWT_ID_DIS JL_ZZ_AP_INV_DIS_WH_ALL.INV_DISTRIB_AWT_ID%TYPE;
517
518
519 CURSOR CUR3(l_TAXPAYERID_C NUMBER) IS
520 SELECT APINA.INVOICE_ID,
521 APIND.DISTRIBUTION_LINE_NUMBER,
522 APIND.INVOICE_DISTRIBUTION_ID
523 FROM PO_VENDORS PV,
524 AP_INVOICES APINA,
525 AP_INVOICE_DISTRIBUTIONS APIND,
526 PER_ALL_PEOPLE_F PAPF
527 WHERE PV.VENDOR_ID = APINA.VENDOR_ID
528 AND NVL(pv.employee_id, - 99) = papf.person_id (+)
529 AND NVL(papf.EFFECTIVE_START_DATE, SYSDATE) <= SYSDATE
530 AND NVL(papf.EFFECTIVE_END_DATE, SYSDATE) >= SYSDATE
531 --AND APIND.TAX_CODE_ID in (SELECT TAX_ID FROM AP_TAX_CODES WHERE name like 'TURN_BSAS_GRP%')
532 AND APINA.INVOICE_ID = APIND.INVOICE_ID
533 AND APIND.LINE_TYPE_LOOKUP_CODE = 'ITEM'
534 AND APIND.GLOBAL_ATTRIBUTE3 IN
535 -- (SELECT LOCATION_ID FROM HR_LOCATIONS_ALL WHERE UPPER(LOCATION_CODE) = 'BUENOS AIRES' --bug 8622329
536 (SELECT LOCATION_ID FROM HR_LOCATIONS_ALL WHERE UPPER(REGION_2) = 'BUENOS AIRES' --bug 9865805
537 AND trunc(SYSDATE) <= NVL(inactive_DATE, trunc(SYSDATE)))
538 AND NVL(APINA.INVOICE_AMOUNT,0) <> NVL(APINA.AMOUNT_PAID,0)
539 --bug 8530918 AND NVL(papf.national_identifier, NVL(pv.individual_1099, pv.num_1099)) = TO_CHAR(l_TAXPAYERID_C);
540 AND rtrim(
541 substr(
542 replace(
543 nvl(papf.national_identifier,
544 nvl(pv.individual_1099,pv.num_1099)
545 ),
546 '-'),
547 1,10)
548 ) ||
549 substr(pv.global_attribute12,1,1) = TO_CHAR(l_TAXPAYERID_C)
550 AND pv.vendor_id in (select vendor_id from po_vendor_sites_all where org_id = l_org_id); --bug 10091261
551
552
553
554 INV_REC CUR3%ROWTYPE;
555
556
557
558 CURSOR C3 IS
559 SELECT * FROM JL_AR_TURN_UPL_T;
560
561 --WHERE start_DATE >= P_START_DATE and end_DATE <= P_END_DATE;
562
563 V_TEMPREC C3%ROWTYPE;
564
565
566
567 --WHO COLUMNS:
568 ----------------
569
570 l_created_by NUMBER(15) := NVL(fnd_profile.value('USER_ID'), 1);
571 l_creation_DATE DATE := SYSDATE;
572 l_last_UPDATEd_by NUMBER(15) := NVL(fnd_profile.value('USER_ID'), 1);
573 l_last_UPDATE_DATE DATE := SYSDATE;
574 l_last_UPDATE_login NUMBER(15) := NVL(fnd_global.conc_login_id, 1);
575
576
577 --FLAGS:
578 ----------
579
580
581 duplicate_check_count NUMBER := NULL;
582 wht_check_unique VARCHAR2(1) := 'Y';
583 duplicate_check_flag VARCHAR2(1) := 'N';
584 same_rec_flag VARCHAR2(1) := 'N';
585 wht_check_flag VARCHAR2(1) := 'N';
586 exist_check_flag VARCHAR2(1) := 'N';
587 WHT_GROUP_NUM_rate_flag VARCHAR2(1) := 'N';
588 same_taxpayerid_flag VARCHAR2(1) := 'N';
589 taxtype_code_check VARCHAR2(1) := 'N';
590 AWT_CODE_INV_AVAIL_FLAG VARCHAR2(1) := 'N';
591 same_prev_rec_flag VARCHAR2(1) := 'N';
592
593
594 BEGIN
595
596 -- To ensure the single start_DATE/end_DATE in tmp table (9.4.A.2)
597
598 SELECT COUNT(*) INTO duplicate_check_count FROM
599 (SELECT DISTINCT PUBLISH_DATE, START_DATE, END_DATE FROM JL_AR_TURN_UPL_T) TMP;
600
601 IF duplicate_check_count > 1 THEN
602 duplicate_check_flag := 'Y';
603 IF p_debug_log = 'Y' THEN
604 FND_FILE.put_line( FND_FILE.LOG,'1 .Found more than one set of START_DATE or END_DATE');
605 END IF;
606 UPDATE JGZZ_AR_TAX_GLOBAL_TMP SET JG_INFO_V1 = 'JL_AR_AP_WRONG_DATE'
607 WHERE JG_INFO_N1 = l_TAXPAYER_ID AND JG_INFO_D1 = l_START_DATE AND JG_INFO_D2 = l_END_DATE;
608 RAISE_APPLICATION_ERROR(- 20999,'Found more than one set of START_DATE / END_DATE'|| SQLCODE ||' -ERROR- '|| SQLERRM);
609 END IF; -- will Stop the process, because of duplicate PUBLISH_DATE, START_DATE, END_DATE in TMP table
610
611
612
613 OPEN C3;
614 LOOP
615 FETCH C3 INTO V_TEMPREC;
616 EXIT WHEN C3%NOTFOUND;
617
618 BEGIN
619
620 l_PUBLISH_DATE := V_TEMPREC.PUBLISH_DATE;
621 l_START_DATE := V_TEMPREC.START_DATE;
622 l_END_DATE := V_TEMPREC.END_DATE;
623
624 l_TAXPAYER_ID := V_TEMPREC.TAXPAYER_ID;
625 l_CONTRIBUTOR_TYPE_CODE := V_TEMPREC.CONTRIBUTOR_TYPE_CODE;
626 l_NEW_CONTRIBUTOR_FLAG := V_TEMPREC.NEW_CONTRIBUTOR_FLAG;
627 l_RATE_CHANGE_FLAG := V_TEMPREC.RATE_CHANGE_FLAG;
628 l_PERCEPTION_RATE := V_TEMPREC.PERCEPTION_RATE;
629 l_WHT_RATE := V_TEMPREC.WHT_RATE;
630 l_PERCEPTION_GROUP_NUM := V_TEMPREC.PERCEPTION_GROUP_NUM;
631 l_WHT_GROUP_NUM := V_TEMPREC.WHT_GROUP_NUM;
632
633
634 INSERT INTO JGZZ_AR_TAX_GLOBAL_TMP(JG_INFO_N1, JG_INFO_D1, JG_INFO_D2, JG_INFO_V1)
635 VALUES (l_TAXPAYER_ID, l_START_DATE, l_END_DATE, NULL);
636 -- Bug 14563117 Start
637 IF BASIC_VALIDATION(V_TEMPREC) THEN
638 -- Bug 14563117 End
639 IF p_debug_log = 'Y' THEN
640 FND_FILE.PUT_LINE( FND_FILE.LOG,'2 .RECORD PASSED IN BASIC VALIDATION FOR TAXPAYER: '|| l_TAXPAYER_ID);
641 END IF;
642 ELSE
643 IF p_debug_log = 'Y' THEN
644 FND_FILE.put_line( FND_FILE.LOG,'3 .RECORD FAILED DURING BASIC VALIDATION FOR TAXPAYER : '|| l_TAXPAYER_ID);
645 END IF;
646 GOTO L3;
647 END IF; -- AR Code Hook
648
649
650 IF l_taxpayer_ar = 'Y' THEN
651
652 IF p_debug_log = 'Y' THEN
653 FND_FILE.put_line( FND_FILE.LOG,'3 A .GOING TO START THE AR VALIDATION FOR TAXPAYER: '|| l_TAXPAYER_ID);
654 END IF;
655
656 BEGIN
657
658 JL_ZZ_AR_UPLOAD_TAXES.JL_AR_UPDATE_CUST_SITE_TAX(l_TAXPAYER_ID,
659 l_AWT_TAX_TYPE,
660 l_PERCEPTION_TAX_TYPE,
661 l_ORG_ID,
662 l_PUBLISH_DATE,
663 l_START_DATE,
664 l_END_DATE,
665 l_RETURN_STATUS ); -- out parameter for status
666
667 IF l_RETURN_STATUS = 'S' THEN --bug 9907885
668 FND_FILE.put_line(FND_FILE.LOG,'58. AR validation completed successfully');
669 END IF;
670
671 EXCEPTION
672 WHEN OTHERS THEN
673 IF p_debug_log = 'Y' THEN
674 FND_FILE.put_line(FND_FILE.LOG,'AR VALIDATION FAILED WITH RETUN STATUS'|| l_RETURN_STATUS || SQLCODE || 'ERROR' || SQLERRM);
675 END IF;
676 RAISE_APPLICATION_ERROR(- 20999,'AR VALIDATION FAILED'|| SQLCODE ||' -ERROR- '|| SQLERRM);
677 END;
678
679 END IF;
680
681
682 -- If data present in AP tax payer id, then AP Validation starts here
683
684 IF l_taxpayer = 'Y' THEN
685
686 IF p_debug_log = 'Y' THEN
687 FND_FILE.put_line( FND_FILE.LOG,'3 B .GOING TO START THE AP VALIDATION FOR TAXPAYER: '|| l_TAXPAYER_ID);
688 END IF;
689
690 BEGIN
691
692 SELECT MAX(START_DATE) INTO l_START_DATE_SEC_MAX FROM JL_AR_TURN_UPL WHERE TAXPAYER_ID = l_TAXPAYER_ID AND
693 START_DATE NOT IN (SELECT MAX(START_DATE) FROM JL_AR_TURN_UPL WHERE TAXPAYER_ID = l_TAXPAYER_ID);
694
695 SELECT MAX(END_DATE) INTO l_END_DATE_SEC_MAX FROM JL_AR_TURN_UPL WHERE TAXPAYER_ID = l_TAXPAYER_ID AND
696 END_DATE NOT IN (SELECT MAX(END_DATE) FROM JL_AR_TURN_UPL WHERE TAXPAYER_ID = l_TAXPAYER_ID);
697
698
699 EXCEPTION
700
701 WHEN OTHERS THEN
702 IF p_debug_log = 'Y' THEN
703 FND_FILE.PUT_LINE( FND_FILE.LOG,'4 .NO PREVIOUS RECORD AVAILABLE IN JL_AR_TURN_UPL TABLE');
704 END IF;
705 END;
706
707 l_START_DATE_SEC_MAX := NVL(l_START_DATE_SEC_MAX, l_START_DATE);
708 l_END_DATE_SEC_MAX := NVL(l_END_DATE_SEC_MAX, l_END_DATE);
709
710
711 -- To check the WHT_GROUP_NUM + wht_rate (TDD 9.4.A. Additional Check 1)
712
713 BEGIN
714
715 SELECT 'TURN_BSAS_GRP' || lpad(WHT_GROUP_NUM, 2, '0'),
716 WHT_RATE
717 INTO l_WHT_GROUP_NUM_ALL,
718 l_WHT_RATE_ALL
719 FROM JL_AR_TURN_UPL_T
720 WHERE TAXPAYER_ID = l_TAXPAYER_ID
721 GROUP BY WHT_RATE, WHT_GROUP_NUM;
722
723 SELECT 'Y'
724 INTO WHT_GROUP_NUM_rate_flag
725 FROM AP_AWT_TAX_RATES
726 WHERE tax_name = l_WHT_GROUP_NUM_ALL
727 AND tax_rate = l_WHT_RATE_ALL;
728
729 SELECT tax_id
730 INTO l_tax_id
731 FROM AP_TAX_CODES
732 WHERE name = l_WHT_GROUP_NUM_ALL
733 AND tax_type = 'AWT';
734
735 EXCEPTION
736
737 WHEN TOO_MANY_ROWS THEN
738 IF p_debug_log = 'Y' THEN
739 FND_FILE.PUT_LINE( FND_FILE.LOG,'4 .MORE THAN ONE WHT_RATE AND WHT_GROUP_NUM FOUND FOR A TAX_PAYER_ID');
740 END IF;
741 UPDATE JGZZ_AR_TAX_GLOBAL_TMP SET JG_INFO_V1 = 'JLZZ_MANY_WHT_RATE_GROUP'
742 WHERE JG_INFO_N1 = l_TAXPAYER_ID AND JG_INFO_D1 = l_START_DATE AND JG_INFO_D2 = l_END_DATE;
743 RAISE_APPLICATION_ERROR(- 20999,'AN ERROR WAS ENCOUNTERED IN WHT_GROUP_NUM AND WHT_RATE VALIDATION '|| SQLCODE ||' -ERROR- '|| SQLERRM);
744
745 WHEN OTHERS THEN
746 IF p_debug_log = 'Y' THEN
747 FND_FILE.put_line( FND_FILE.LOG,'5 .Wht Rate and Wht group for this record from Government File doesnt match with AP Wht Tax Setup.');
748 END IF;
749 UPDATE JGZZ_AR_TAX_GLOBAL_TMP SET JG_INFO_V1 = 'JL_AR_AP_GRP_NO_MATCH'
750 WHERE JG_INFO_N1 = l_TAXPAYER_ID AND JG_INFO_D1 = l_START_DATE AND JG_INFO_D2 = l_END_DATE;
751 RAISE_APPLICATION_ERROR(- 20999,'AN ERROR WAS ENCOUNTERED IN WHT_GROUP_NUM AND WHT_RATE VALIDATION '|| SQLCODE ||' -ERROR- '|| SQLERRM);
752 END;
753
754
755
756 IF WHT_GROUP_NUM_rate_flag = 'N' THEN
757 IF p_debug_log = 'Y' THEN
758 FND_FILE.put_line( FND_FILE.LOG,'6 .Wht Rate and Wht group for this record from Government File doesnt match with AP Wht Tax Setup.');
759 END IF;
760 UPDATE JGZZ_AR_TAX_GLOBAL_TMP SET JG_INFO_V1 = 'JL_AR_AP_GRP_NO_MATCH'
761 WHERE JG_INFO_N1 = l_TAXPAYER_ID AND JG_INFO_D1 = l_START_DATE AND JG_INFO_D2 = l_END_DATE;
762 RAISE_APPLICATION_ERROR(- 20999,'WHT_RATE AND WHT_GROUP_NUM FROM GOVERNMENT FILE DOESNT MATCH WITH AP WHT TAX SETUP '|| SQLCODE ||' -ERROR- '|| SQLERRM);
763
764 END IF;
765
766 /*
767 -- To compare JL_AR_TURN_UPL_T Upload Table records with JL_AR_TURN_UPL (9.4.A.3)
768
769 BEGIN
770
771 same_prev_rec_flag := 'N'; --Intialising the value of same_prev_rec_flag everytime to check whether the record available already
772
773 SELECT DISTINCT 'Y' INTO same_prev_rec_flag FROM JL_AR_TURN_UPL WHERE
774 PUBLISH_DATE = l_PUBLISH_DATE
775 AND START_DATE = l_START_DATE
776 AND END_DATE = l_END_DATE
777 AND TAXPAYER_ID = l_TAXPAYER_ID
778 AND CONTRIBUTOR_TYPE_CODE = l_CONTRIBUTOR_TYPE_CODE
779 AND NEW_CONTRIBUTOR_FLAG = l_NEW_CONTRIBUTOR_FLAG
780 AND RATE_CHANGE_FLAG = l_RATE_CHANGE_FLAG
781 AND PERCEPTION_RATE = l_PERCEPTION_RATE
782 AND WHT_RATE = l_WHT_RATE
783 AND PERCEPTION_GROUP_NUM = l_PERCEPTION_GROUP_NUM
784 AND WHT_GROUP_NUM = l_WHT_GROUP_NUM;
785
786
787 IF p_debug_log = 'Y' THEN
788 FND_FILE.PUT_LINE( FND_FILE.LOG,'7 .THE VALUE FOR SAME_PREV_RECORD_FLAG:'||same_prev_rec_flag);
789 END IF;
790
791 EXCEPTION
792 WHEN NO_DATA_FOUND THEN
793 IF p_debug_log = 'Y' THEN
794 FND_FILE.PUT_LINE( FND_FILE.LOG,'7A .NO CORRESPONDING DATA IN JL_AR_TURN_UPL');
795 END IF;
796 WHEN OTHERS THEN
797 IF p_debug_log = 'Y' THEN
798 FND_FILE.PUT_LINE( FND_FILE.LOG,'8 .FAILED WHILE CHECKING THE CORRESPONDING DATA IN JL_AR_TURN_UPL');
799 END IF;
800 END;
801
802 IF p_debug_log = 'Y' THEN
803 FND_FILE.PUT_LINE( FND_FILE.LOG,'9 .VALUE OF SAME_PREV_RECORD_FLAG :'|| same_prev_rec_flag);
804 END IF;
805
806 -- For exactly same records simply copying that to ALL table FROM TMP and fetching next record (TDD 3.1)
807
808 IF same_prev_rec_flag = 'Y' then
809 FND_FILE.PUT_LINE( FND_FILE.LOG,'10. Going to fetch next record as Taxpayer Id was already present');
810 GOTO L3; -- If the same exact Taxpayer value was already present in final table then simply fetch next record
811 END IF;
812
813 ---/*
814
815 IF same_rec_flag = 'Y' THEN
816
817 Insert_Row (l_PUBLISH_DATE,
818 l_START_DATE,
819 l_END_DATE,
820 l_TAXPAYER_ID,
821 l_CONTRIBUTOR_TYPE_CODE,
822 l_NEW_CONTRIBUTOR_FLAG,
823 l_RATE_CHANGE_FLAG,
824 l_PERCEPTION_RATE,
825 l_WHT_RATE,
826 l_PERCEPTION_GROUP_NUM,
827 l_WHT_GROUP_NUM,
828 l_WHT_DEFAULT_FLAG,
829 'AP');
830
831
832 --delete JL_AR_TURN_UPL_T WHERE TAXPAYER_ID = l_TAXPAYER_ID;
833
834 IF p_debug_log = 'Y' THEN
835 FND_FILE.PUT_LINE( FND_FILE.LOG,'10 .INSERTED DATA IN JL_AR_TURN_UPL SAME_REC_FLAG IS Y AND DELETED FROM TMP TABLE');
836 END IF;
837
838 GOTO L3;
839 --fetching next record
840 END IF;
841
842 */
843 --To check whether TMP taxpayer id present already in previous months in ALL table (3.2)
844
845 BEGIN
846
847 SELECT DISTINCT 'Y' INTO same_taxpayerid_flag FROM JL_AR_TURN_UPL WHERE
848 PUBLISH_DATE <> l_PUBLISH_DATE
849 AND START_DATE <> l_START_DATE
850 AND END_DATE <> l_END_DATE
851 AND TAXPAYER_ID = l_TAXPAYER_ID;
852
853 EXCEPTION
854 WHEN NO_DATA_FOUND THEN
855 IF p_debug_log = 'Y' THEN
856 FND_FILE.PUT_LINE( FND_FILE.LOG,'12 .NO CORRESPONDING TAXPAYER_ID IN JL_AR_TURN_UPL');
857 END IF;
858
859 WHEN OTHERS THEN
860 IF p_debug_log = 'Y' THEN
861 FND_FILE.PUT_LINE( FND_FILE.LOG,'13 .FAILED WHILE CHECKING THE CORRESPONDING TAXPAYER_ID IN JL_AR_TURN_UPL');
862 END IF;
863
864 END;
865
866
867
868
869 IF same_taxpayerid_flag = 'Y' AND l_RATE_CHANGE_FLAG = 'S' THEN
870 wht_check_flag := 'N';
871
872 BEGIN
873
874 SELECT 'Y' INTO wht_check_flag FROM JL_AR_TURN_UPL WHERE
875 TAXPAYER_ID = l_TAXPAYER_ID
876 AND WHT_GROUP_NUM = l_WHT_GROUP_NUM
877 AND WHT_RATE = l_WHT_RATE
878 AND START_DATE = l_START_DATE_SEC_MAX
879 AND END_DATE = l_END_DATE_SEC_MAX;
880
881 EXCEPTION
882 WHEN NO_DATA_FOUND THEN
883 IF p_debug_log = 'Y' THEN
884 FND_FILE.PUT_LINE( FND_FILE.LOG,'14. NO CORRESPONDING DATA FOR l_WHT_GROUP_NUM AND l_WHT_RATE');
885 END IF;
886 WHEN OTHERS THEN
887 IF p_debug_log = 'Y' THEN
888 FND_FILE.PUT_LINE( FND_FILE.LOG,'15 .FAILED WHILE CHECKING THE CORRESPONDING DATA IN l_WHT_GROUP_NUM AND l_WHT_RATE');
889 END IF;
890 END;
891
892 -- for records of same wht group/wht rate, simply copy (TDD 3.2.1.1)
893 IF wht_check_flag = 'Y' THEN
894
895 Insert_Row (l_PUBLISH_DATE,
896 l_START_DATE,
897 l_END_DATE,
898 l_TAXPAYER_ID,
899 l_CONTRIBUTOR_TYPE_CODE,
900 l_NEW_CONTRIBUTOR_FLAG,
901 l_RATE_CHANGE_FLAG,
902 l_PERCEPTION_RATE,
903 l_WHT_RATE,
904 l_PERCEPTION_GROUP_NUM,
905 l_WHT_GROUP_NUM,
906 l_WHT_DEFAULT_FLAG,
907 'AP');
908
909 -- delete JL_AR_TURN_UPL_T WHERE TAXPAYER_ID = l_TAXPAYER_ID;
910
911 IF p_debug_log = 'Y' THEN
912 FND_FILE.PUT_LINE( FND_FILE.LOG,'16 .INSERTED DATA IN TABLE JL_AR_TURN_UPL WHEN WHT_CHECK_FLAG IS Y ');
913 END IF;
914 GOTO L3;
915
916 -- If the wht rate and wht group are different (3.2.1.2) then UPDATE the tax SETups
917 ELSE
918
919 BEGIN
920
921 SELECT SAWT.SUPP_AWT_TYPE_ID
922 INTO l_SUPP_AWT_TYPE_ID_CD
923 FROM PO_VENDORS PV,
924 JL_ZZ_AP_SUPP_AWT_TYPES SAWT,
925 PER_ALL_PEOPLE_F PAPF
926 WHERE SAWT.VENDOR_ID = PV.VENDOR_ID
927 AND NVL(pv.employee_id, - 99) = papf.person_id (+)
928 AND NVL(papf.EFFECTIVE_START_DATE, SYSDATE) <= SYSDATE
929 AND NVL(papf.EFFECTIVE_END_DATE, SYSDATE) >= SYSDATE
930 AND SAWT.AWT_TYPE_CODE = l_AWT_TAX_TYPE
931 --bug 8530918 AND NVL(papf.national_identifier, NVL(pv.individual_1099, pv.num_1099)) = TO_CHAR(l_TAXPAYER_ID);
932 AND rtrim(
933 substr(
934 replace(
935 nvl(papf.national_identifier,
936 nvl(pv.individual_1099,pv.num_1099)
937 ),
938 '-'),
939 1,10)
940 ) ||
941 substr(pv.global_attribute12,1,1) = TO_CHAR(l_TAXPAYER_ID);
942
943 IF p_debug_log = 'Y' THEN
944 FND_FILE.put_line( FND_FILE.LOG,'17. EFFECTIVE_END_DATE (l_START_DATE-1) : '|| l_START_DATE);
945 FND_FILE.put_line( FND_FILE.LOG,'17. l_tax_id : '|| l_tax_id || 'l_TAXPAYER_ID : '|| l_TAXPAYER_ID);
946 END IF;
947
948
949 SELECT jl_zz_ap_sup_awt_cd_s.nextval INTO l_SUPP_AWT_CODE_ID_SEQ FROM dual;
950
951 IF p_debug_log = 'Y' THEN
952 FND_FILE.put_line( FND_FILE.LOG,'18. l_SUPP_AWT_CODE_ID_SEQ'|| l_SUPP_AWT_CODE_ID_SEQ);
953 FND_FILE.put_line( FND_FILE.LOG,'18. l_SUPP_AWT_TYPE_ID_CD'|| l_SUPP_AWT_TYPE_ID_CD);
954 END IF;
955
956
957 BEGIN
958 SELECT 'Y',
959 SAWTC.SUPP_AWT_CODE_ID
960 INTO taxtype_code_check,
961 l_SUPP_AWT_CODE_ID_CD
962 FROM JL_ZZ_AP_SUP_AWT_CD SAWTC
963 WHERE SAWTC.SUPP_AWT_TYPE_ID = l_SUPP_AWT_TYPE_ID_CD
964 AND SAWTC.TAX_ID = l_tax_id
965 AND (SAWTC.EFFECTIVE_START_DATE = l_START_DATE OR SAWTC.EFFECTIVE_END_DATE = l_END_DATE);
966
967 EXCEPTION
968 WHEN NO_DATA_FOUND THEN
969 taxtype_code_check := 'N';
970 WHEN OTHERS THEN
971 taxtype_code_check := 'N';
972 FND_FILE.PUT_LINE( FND_FILE.LOG,'21. Error occured while fetching data in JL_ZZ_AP_SUP_AWT_CD');
973 END;
974
975 EXCEPTION
976 WHEN OTHERS THEN
977 FND_FILE.PUT_LINE( FND_FILE.LOG,'21. No records fetched from JL_ZZ_AP_SUP_AWT_CD for Taxpayer Id :||l_TAXPAYER_ID');
978
979 END;
980
981
982 IF taxtype_code_check = 'Y' THEN
983
984 FND_FILE.PUT_LINE( FND_FILE.LOG,'21. Records already present in JL_ZZ_AP_SUP_AWT_CD table, no modifications');
985 FND_FILE.PUT_LINE( FND_FILE.LOG,'21 . L_TAX_ID'|| L_TAX_ID || 'l_SUPP_AWT_TYPE_ID_CD' || l_SUPP_AWT_TYPE_ID_CD);
986 FND_FILE.PUT_LINE( FND_FILE.LOG,'21 . L_EFFECTIVE_START_DATE'|| l_START_DATE || 'EFFECTIVE_END_DATE' || l_END_DATE);
987
988 ELSE
989
990 BEGIN
991 -- CODE TO UPDATE THE EFFECTIVE_END_DATE FOR OTHER PRIMARY_TAX_FLAG = 'Y' AND OTHER L_TAX_ID
992 UPDATE JL_ZZ_AP_SUP_AWT_CD SAWTC SET SAWTC.EFFECTIVE_END_DATE = l_START_DATE - 1
993 WHERE SAWTC.primary_tax_flag = 'Y'
994 AND SAWTC.SUPP_AWT_CODE_ID <> l_SUPP_AWT_CODE_ID_SEQ
995 AND SAWTC.SUPP_AWT_TYPE_ID = l_SUPP_AWT_TYPE_ID_CD
996 --AND SAWTC.TAX_ID <> l_tax_id -- other l_tax_id
997 AND SAWTC.EFFECTIVE_END_DATE IS NULL;
998
999
1000 IF p_debug_log = 'Y' THEN
1001 FND_FILE.PUT_LINE( FND_FILE.LOG,'19 .UPDATED DATA IN JL_ZZ_AP_SUP_AWT_CD FOR '|| SQL%ROWCOUNT || 'RECORDS');
1002 FND_FILE.PUT_LINE( FND_FILE.LOG,'19. EFFECTIVE_END_DATE (L_START_DATE-1) : '|| L_START_DATE);
1003 FND_FILE.PUT_LINE( FND_FILE.LOG,'19. L_TAX_ID : '|| L_TAX_ID || ' L_TAXPAYER_ID : '|| L_TAXPAYER_ID);
1004 END IF;
1005
1006
1007
1008 INSERT INTO JL_ZZ_AP_SUP_AWT_CD
1009 (SUPP_AWT_CODE_ID,
1010 SUPP_AWT_TYPE_ID,
1011 TAX_ID,
1012 PRIMARY_TAX_FLAG,
1013 CREATED_BY,
1014 CREATION_DATE,
1015 LAST_UPDATED_BY,
1016 LAST_UPDATE_DATE,
1017 LAST_UPDATE_LOGIN,
1018 ORG_ID,
1019 EFFECTIVE_START_DATE,
1020 EFFECTIVE_END_DATE)
1021 VALUES
1022 (l_SUPP_AWT_CODE_ID_SEQ, --SUPP_AWT_CODE_ID
1023 l_SUPP_AWT_TYPE_ID_CD, --SUPP_AWT_TYPE_ID
1024 l_tax_id, --TAX_ID
1025 'Y', --PRIMARY_TAX_FLAG
1026 l_created_by, --CREATED_BY
1027 l_creation_DATE, --CREATION_DATE
1028 l_last_UPDATEd_by, --LAST_UPDATED_BY
1029 l_last_UPDATE_DATE, --LAST_UPDATE_DATE
1030 l_last_UPDATE_login, --LAST_UPDATE_LOGIN
1031 l_ORG_ID, --ORG_ID
1032 l_START_DATE, --EFFECTIVE_START_DATE
1033 NULL); --EFFECTIVE_END_DATE
1034
1035
1036 IF p_debug_log = 'Y' THEN
1037 FND_FILE.PUT_LINE( FND_FILE.LOG,'21 . INSERTED DATA IN JL_ZZ_AP_SUP_AWT_CD FOR '|| SQL%ROWCOUNT || 'RECORDS');
1038 END IF;
1039
1040
1041 EXCEPTION
1042 WHEN OTHERS THEN
1043
1044 IF p_debug_log = 'Y' THEN
1045 FND_FILE.PUT_LINE( FND_FILE.LOG,'21. INSERT NOT DONE IN JL_ZZ_AP_SUP_AWT_CD '|| SQLCODE ||' -ERROR- '|| SQLERRM);
1046 END IF;
1047 END;
1048
1049 END IF;
1050
1051 -- CODE TO UPDATE/INSERT JL_ZZ_AP_INV_DIS_WH_ALL (TDD 3.2.1.1)
1052
1053 BEGIN
1054 -- TO GET THE CURRENT MONTH SUPP_AWT_CODE_ID FOR THE PRESENT TAX_ID
1055
1056 SELECT SAWTC.SUPP_AWT_CODE_ID INTO l_SUPP_AWT_CODE_ID_CD
1057 FROM JL_ZZ_AP_SUP_AWT_CD SAWTC,
1058 PO_VENDORS PV, JL_ZZ_AP_SUPP_AWT_TYPES SAWT, PER_ALL_PEOPLE_F PAPF
1059 WHERE SAWT.VENDOR_ID = PV.VENDOR_ID
1060 AND NVL(pv.employee_id, - 99) = papf.person_id (+)
1061 AND NVL(papf.EFFECTIVE_START_DATE, SYSDATE) <= SYSDATE
1062 AND NVL(papf.EFFECTIVE_END_DATE, SYSDATE) >= SYSDATE
1063 AND SAWT.AWT_TYPE_CODE = l_AWT_TAX_TYPE
1064 AND SAWT.SUPP_AWT_TYPE_ID = SAWTC.SUPP_AWT_TYPE_ID
1065 --AND SAWTC.TAX_ID in (SELECT TAX_ID FROM AP_TAX_CODES WHERE name like 'TURN_BSAS_GRP%')
1066 AND SAWTC.TAX_ID = l_tax_id
1067 AND SAWTC.EFFECTIVE_START_DATE = l_START_DATE
1068 AND SAWTC.primary_tax_flag = 'Y'
1069 AND sawtc.effective_end_DATE IS NULL
1070 --bug 8530918 AND NVL(papf.national_identifier, NVL(pv.individual_1099, pv.num_1099)) = TO_CHAR(l_TAXPAYER_ID);
1071 AND rtrim(
1072 substr(
1073 replace(
1074 nvl(papf.national_identifier,
1075 nvl(pv.individual_1099,pv.num_1099)
1076 ),
1077 '-'),
1078 1,10)
1079 ) ||
1080 substr(pv.global_attribute12,1,1) = TO_CHAR(l_TAXPAYER_ID);
1081
1082 SELECT JL_ZZ_AP_INV_DIS_WH_S.NEXTVAL INTO l_INV_DISTRIB_AWT_ID_INV FROM dual;
1083
1084 EXCEPTION
1085 WHEN OTHERS THEN
1086 IF p_debug_log = 'Y' THEN
1087 FND_FILE.PUT_LINE( FND_FILE.LOG,'22 B SELECT TAX CODE NOT DONE FOR JL_ZZ_AP_INV_DIS_WH_ALL - '|| SQLCODE ||' -ERROR- '|| SQLERRM);
1088 END IF;
1089 END;
1090
1091 OPEN CUR3(l_TAXPAYER_ID);
1092 LOOP
1093 FETCH CUR3 INTO INV_REC;
1094 EXIT WHEN CUR3%NOTFOUND;
1095
1096
1097
1098 BEGIN
1099
1100 AWT_CODE_INV_AVAIL_FLAG := 'N';
1101
1102 SELECT 'Y', INV_DISTRIB_AWT_ID INTO AWT_CODE_INV_AVAIL_FLAG, l_INV_DISTRIB_AWT_ID_DIS
1103 FROM JL_ZZ_AP_INV_DIS_WH
1104 WHERE INVOICE_ID = INV_REC.INVOICE_ID
1105 AND DISTRIBUTION_LINE_NUMBER = INV_REC.DISTRIBUTION_LINE_NUMBER
1106 AND INVOICE_DISTRIBUTION_ID = INV_REC.INVOICE_DISTRIBUTION_ID
1107 AND SUPP_AWT_CODE_ID = l_SUPP_AWT_CODE_ID_CD;
1108 /*(SELECT SUPP_AWT_CODE_ID FROM JL_ZZ_AP_SUP_AWT_CD SAWTC, JL_ZZ_AP_SUPP_AWT_TYPES SAWT
1109 WHERE SAWT.SUPP_AWT_TYPE_ID = SAWTC.SUPP_AWT_TYPE_ID
1110 -- AND SAWTC.EFFECTIVE_START_DATE = l_START_DATE
1111 AND SAWTC.TAX_ID IN (SELECT TAX_ID FROM AP_TAX_CODES WHERE name LIKE 'TURN_BSAS_GRP%')); */
1112
1113 EXCEPTION
1114 WHEN NO_DATA_FOUND THEN
1115 AWT_CODE_INV_AVAIL_FLAG := 'N';
1116 FND_FILE.put_line( FND_FILE.LOG,'22 B No earlier same tax code id in Inv Dist'|| SQLCODE ||' -ERROR- '|| SQLERRM);
1117
1118 WHEN OTHERS THEN
1119 AWT_CODE_INV_AVAIL_FLAG := 'N';
1120 FND_FILE.put_line( FND_FILE.LOG,'22 B Error in Inv Dist'|| SQLCODE ||' -ERROR- '|| SQLERRM);
1121 END;
1122
1123
1124
1125 IF AWT_CODE_INV_AVAIL_FLAG = 'N' THEN
1126
1127 IF p_debug_log = 'Y' THEN
1128 FND_FILE.put_line( FND_FILE.LOG,'22 B l_INV_DISTRIB_AWT_ID_INV'|| l_INV_DISTRIB_AWT_ID_INV);
1129 FND_FILE.put_line( FND_FILE.LOG,'22 B INV_REC.INVOICE_ID'|| INV_REC.INVOICE_ID);
1130 FND_FILE.put_line( FND_FILE.LOG,'22 B INV_REC.DISTRIBUTION_LINE_NUMBER'|| INV_REC.DISTRIBUTION_LINE_NUMBER);
1131 FND_FILE.put_line( FND_FILE.LOG,'22 B l_SUPP_AWT_CODE_ID_CD'|| l_SUPP_AWT_CODE_ID_CD);
1132 FND_FILE.put_line( FND_FILE.LOG,'22 B INVOICE_DISTRIBUTION_ID'|| INV_REC.INVOICE_DISTRIBUTION_ID);
1133 END IF;
1134
1135 BEGIN ---code to insert data INTO jl_zz_ap_inv_dis_wh_all
1136
1137 INSERT INTO JL_ZZ_AP_INV_DIS_WH
1138 (INV_DISTRIB_AWT_ID,
1139 INVOICE_ID,
1140 DISTRIBUTION_LINE_NUMBER,
1141 SUPP_AWT_CODE_ID,
1142 CREATED_BY,
1143 CREATION_DATE,
1144 LAST_UPDATED_BY,
1145 LAST_UPDATE_DATE,
1146 LAST_UPDATE_LOGIN,
1147 ORG_ID,
1148 INVOICE_DISTRIBUTION_ID)
1149 VALUES
1150 (l_INV_DISTRIB_AWT_ID_INV, --INV_DISTRIB_AWT_ID
1151 INV_REC.INVOICE_ID, --INVOICE_ID
1152 INV_REC.DISTRIBUTION_LINE_NUMBER, --DISTRIBUTION_LINE_NUMBER
1153 l_SUPP_AWT_CODE_ID_CD, --SUPP_AWT_CODE_ID
1154 l_created_by, --CREATED_BY
1155 l_creation_DATE, --CREATION_DATE
1156 l_last_UPDATEd_by, --LAST_UPDATED_BY
1157 l_last_UPDATE_DATE, --LAST_UPDATE_DATE
1158 l_last_UPDATE_login, --LAST_UPDATE_LOGIN
1159 l_ORG_ID, --ORG_ID
1160 INV_REC.INVOICE_DISTRIBUTION_ID); --Invoice distribution ID -- R12 Changes
1161
1162
1163
1164 SELECT JL_ZZ_AP_INV_DIS_WH_S.NEXTVAL INTO l_INV_DISTRIB_AWT_ID_INV FROM dual;
1165
1166 IF p_debug_log = 'Y' THEN
1167 FND_FILE.put_line( FND_FILE.LOG,'22. C '|| SQL%ROWCOUNT ||'Inserted records in JL_ZZ_AP_INV_DIS_WH '|| SQLCODE || 'ERROR' || SQLERRM);
1168 END IF;
1169
1170 EXCEPTION
1171 WHEN OTHERS THEN
1172 IF p_debug_log = 'Y' THEN
1173 FND_FILE.put_line( FND_FILE.LOG,'22. C Failed while Inserted records in JL_ZZ_AP_INV_DIS_WH '|| SQLCODE || 'ERROR' || SQLERRM);
1174 END IF;
1175 END;
1176
1177 ELSE
1178
1179 BEGIN
1180 IF p_debug_log = 'Y' THEN
1181 FND_FILE.put_line( FND_FILE.LOG,'23 A l_INV_DISTRIB_AWT_ID_INV'|| l_INV_DISTRIB_AWT_ID_INV);
1182 FND_FILE.put_line( FND_FILE.LOG,'23 A INV_REC.INVOICE_ID'|| INV_REC.INVOICE_ID);
1183 FND_FILE.put_line( FND_FILE.LOG,'23 A INV_REC.DISTRIBUTION_LINE_NUMBER'|| INV_REC.DISTRIBUTION_LINE_NUMBER);
1184 FND_FILE.put_line( FND_FILE.LOG,'23 A l_SUPP_AWT_CODE_ID_CD'|| l_SUPP_AWT_CODE_ID_CD);
1185 FND_FILE.put_line( FND_FILE.LOG,'23 A l_INV_DISTRIB_AWT_ID_DIS'|| l_INV_DISTRIB_AWT_ID_DIS);
1186 FND_FILE.put_line( FND_FILE.LOG,'23 A INVOICE_DISTRIBUTION_ID'|| INV_REC.INVOICE_DISTRIBUTION_ID);
1187 END IF;
1188
1189 /*SELECT INV_DISTRIB_AWT_ID INTO l_INV_DISTRIB_AWT_ID_DIS
1190 FROM JL_ZZ_AP_INV_DIS_WH
1191 WHERE INVOICE_ID = INV_REC.INVOICE_ID
1192 AND DISTRIBUTION_LINE_NUMBER = INV_REC.DISTRIBUTION_LINE_NUMBER
1193 AND INVOICE_DISTRIBUTION_ID = INV_REC.INVOICE_DISTRIBUTION_ID
1194 AND SUPP_AWT_CODE_ID IN
1195 (SELECT SUPP_AWT_CODE_ID FROM JL_ZZ_AP_SUP_AWT_CD SAWTC, JL_ZZ_AP_SUPP_AWT_TYPES SAWT
1196 WHERE SAWT.SUPP_AWT_TYPE_ID = SAWTC.SUPP_AWT_TYPE_ID
1197 AND SAWTC.primary_tax_flag = 'Y'
1198 AND SAWTC.TAX_ID IN (SELECT TAX_ID FROM AP_TAX_CODES WHERE name LIKE 'TURN_BSAS_GRP%')
1199 AND sawtc.effective_end_DATE IS NULL);*/
1200
1201
1202 UPDATE JL_ZZ_AP_INV_DIS_WH SET SUPP_AWT_CODE_ID = l_SUPP_AWT_CODE_ID_CD
1203 WHERE INV_DISTRIB_AWT_ID = l_INV_DISTRIB_AWT_ID_DIS;
1204
1205 IF p_debug_log = 'Y' THEN
1206 FND_FILE.put_line( FND_FILE.LOG,'23 B. Updated '|| SQL%ROWCOUNT ||' records in JL_ZZ_AP_INV_DIS_WH '|| SQLCODE || 'ERROR' || SQLERRM);
1207 FND_FILE.put_line( FND_FILE.LOG,'23 B. l_SUPP_AWT_CODE_ID_CD:'|| l_SUPP_AWT_CODE_ID_CD);
1208 FND_FILE.put_line( FND_FILE.LOG,'23 B. l_INV_DISTRIB_AWT_ID_DIS:'|| l_INV_DISTRIB_AWT_ID_DIS);
1209 END IF;
1210
1211 EXCEPTION
1212 WHEN OTHERS THEN
1213 IF p_debug_log = 'Y' THEN
1214 FND_FILE.put_line( FND_FILE.LOG,'23 B. Failed while updating records in JL_ZZ_AP_INV_DIS_WH '|| SQLCODE || 'ERROR' || SQLERRM);
1215 END IF;
1216 END;
1217
1218 END IF;
1219
1220 END LOOP;
1221 CLOSE CUR3;
1222
1223 IF p_debug_log = 'Y' THEN
1224 FND_FILE.PUT_LINE( FND_FILE.LOG,'24. UPDATED DATA IN JL_ZZ_AP_INV_DIS_WH FOR SUPP_AWT_CODE_ID ');
1225 END IF;
1226
1227
1228
1229 Insert_Row (l_PUBLISH_DATE,
1230 l_START_DATE,
1231 l_END_DATE,
1232 l_TAXPAYER_ID,
1233 l_CONTRIBUTOR_TYPE_CODE,
1234 l_NEW_CONTRIBUTOR_FLAG,
1235 l_RATE_CHANGE_FLAG,
1236 l_PERCEPTION_RATE,
1237 l_WHT_RATE,
1238 l_PERCEPTION_GROUP_NUM,
1239 l_WHT_GROUP_NUM,
1240 l_WHT_DEFAULT_FLAG,
1241 'AP');
1242
1243 -- delete JL_AR_TURN_UPL_T WHERE TAXPAYER_ID = l_TAXPAYER_ID;
1244
1245 IF p_debug_log = 'Y' THEN
1246 FND_FILE.put_line( FND_FILE.LOG,'26 . Inserted data in JL_AR_TURN_UPL for All Records ');
1247 END IF;
1248
1249 END IF;
1250
1251 GOTO L3;
1252
1253 --code to insert in ALL table for rate_change_flag N (TDD 3.2.2)
1254
1255 ELSIF same_taxpayerid_flag = 'Y' AND l_RATE_CHANGE_FLAG = 'N' THEN
1256
1257 Insert_Row (l_PUBLISH_DATE,
1258 l_START_DATE,
1259 l_END_DATE,
1260 l_TAXPAYER_ID,
1261 l_CONTRIBUTOR_TYPE_CODE,
1262 l_NEW_CONTRIBUTOR_FLAG,
1263 l_RATE_CHANGE_FLAG,
1264 l_PERCEPTION_RATE,
1265 l_WHT_RATE,
1266 l_PERCEPTION_GROUP_NUM,
1267 l_WHT_GROUP_NUM,
1268 l_WHT_DEFAULT_FLAG,
1269 'AP');
1270
1271 IF p_debug_log = 'Y' THEN
1272 FND_FILE.put_line( FND_FILE.LOG,'28. Inserted data in JL_AR_TURN_UPL when same_rec_flag is N AND l_RATE_CHANGE_FLAG is N');
1273 FND_FILE.put_line( FND_FILE.LOG,'Fetching Record Record after Taxpayer Id '|| l_TAXPAYER_ID);
1274 END IF;
1275 --delete JL_AR_TURN_UPL_T WHERE TAXPAYER_ID = l_TAXPAYER_ID;
1276 --GOTO L3;
1277
1278 END IF;
1279 -- To check the data EXIST in JL_AR_TURN_UPL_T, but DO NOT EXIST in JL_AR_TURN_UPL table (TDD 3.3)
1280
1281
1282 BEGIN
1283
1284 SELECT 'Y' INTO exist_check_flag FROM JL_AR_TURN_UPL
1285 WHERE TAXPAYER_ID NOT IN (SELECT TAXPAYER_ID FROM JL_AR_TURN_UPL_T WHERE START_DATE = l_START_DATE
1286 AND END_DATE = l_END_DATE
1287 AND PUBLISH_DATE = l_PUBLISH_DATE
1288 AND TAXPAYER_ID = l_TAXPAYER_ID);
1289
1290 EXCEPTION
1291
1292 WHEN NO_DATA_FOUND THEN
1293 exist_check_flag := 'Y';
1294 IF p_debug_log = 'Y' THEN
1295 FND_FILE.PUT_LINE( FND_FILE.LOG,' 29 . NO DATA EXIST IN TMP TABLE OTHER THAN ALL TABLE DATA');
1296 END IF;
1297 WHEN OTHERS THEN
1298 exist_check_flag := 'Y';
1299 IF p_debug_log = 'Y' THEN
1300 FND_FILE.PUT_LINE( FND_FILE.LOG,' 30 .FAILED IN EXIST_CHECK_FLAG VALIDATION'|| EXIST_CHECK_FLAG);
1301 END IF;
1302
1303 END;
1304
1305 IF p_debug_log = 'Y' THEN
1306 FND_FILE.PUT_LINE( FND_FILE.LOG,' 31 . EXIST_CHECK_FLAG'|| EXIST_CHECK_FLAG);
1307 END IF;
1308
1309 SELECT MAX(START_DATE) INTO l_START_DATE_SEC_MAX FROM JL_AR_TURN_UPL WHERE TAXPAYER_ID = l_TAXPAYER_ID AND
1310 START_DATE NOT IN (SELECT MAX(START_DATE) FROM JL_AR_TURN_UPL WHERE TAXPAYER_ID = l_TAXPAYER_ID);
1311
1312 SELECT MAX(END_DATE) INTO l_END_DATE_SEC_MAX FROM JL_AR_TURN_UPL WHERE TAXPAYER_ID = l_TAXPAYER_ID AND
1313 END_DATE NOT IN (SELECT MAX(END_DATE) FROM JL_AR_TURN_UPL WHERE TAXPAYER_ID = l_TAXPAYER_ID);
1314
1315
1316
1317
1318 IF exist_check_flag = 'Y' THEN
1319 wht_check_unique := 'Y';
1320
1321 BEGIN
1322
1323 SELECT 'N' INTO wht_check_unique FROM JL_AR_TURN_UPL_T WHERE
1324 TAXPAYER_ID = l_TAXPAYER_ID
1325 AND WHT_GROUP_NUM = l_WHT_GROUP_NUM
1326 AND WHT_RATE = l_WHT_RATE;
1327
1328 EXCEPTION
1329
1330 WHEN NO_DATA_FOUND THEN
1331 wht_check_unique := 'N';
1332
1333 IF p_debug_log = 'Y' THEN
1334 FND_FILE.PUT_LINE( FND_FILE.LOG,' 32 .NO DATA IN WHT_CHECK_FLAG IN JL_AR_TURN_UPL');
1335 END IF;
1336
1337 WHEN OTHERS THEN
1338 wht_check_unique := 'Y';
1339 IF p_debug_log = 'Y' THEN
1340 FND_FILE.PUT_LINE( FND_FILE.LOG,'33 .FAILED IN WHT_CHECK_FLAG VALIDATION');
1341 END IF;
1342 RAISE_APPLICATION_ERROR(- 20999,'FAILED IN WHT_CHECK_FLAG VALIDATION '|| SQLCODE ||' -ERROR- '|| SQLERRM);
1343 END;
1344
1345
1346 END IF;
1347 IF p_debug_log = 'Y' THEN
1348 FND_FILE.PUT_LINE( FND_FILE.LOG,' 31 . WHT_CHECK_UNIQUE'|| WHT_CHECK_UNIQUE);
1349 END IF;
1350 -- If the wht_rate/WHT_GROUP_NUM in TMP were not unique then raise error (3.3.1)
1351
1352 IF wht_check_unique = 'Y' THEN
1353
1354 IF p_debug_log = 'Y' THEN
1355 FND_FILE.put_line( FND_FILE.LOG,' 33 WHT_rate and WHT_GROUP_NUM FROM government file was not unique');
1356 END IF;
1357
1358 UPDATE JGZZ_AR_TAX_GLOBAL_TMP SET JG_INFO_V1 = 'JL_AR_GRP_NOT_UNIQUE'
1359 WHERE JG_INFO_N1 = l_TAXPAYER_ID AND JG_INFO_D1 = l_START_DATE AND JG_INFO_D2 = l_END_DATE;
1360 RAISE_APPLICATION_ERROR(- 20999,'33 A An error was encountered in JL_AR_GRP_NOT_UNIQUE- '|| SQLCODE ||' -ERROR- '|| SQLERRM);
1361 GOTO L3;
1362 --raise error and fetch next record.
1363
1364 ELSE --- If wht unique then INSERT INTO JL_ZZ_AP_SUPP_AWT_TYPES (3.3.2)
1365
1366 BEGIN
1367
1368 SELECT JL_ZZ_AP_SUPP_AWT_TYPES_s.nextval INTO l_SUPP_AWT_TYPE_ID_TYPES FROM dual;
1369
1370 SELECT PV.VENDOR_ID INTO l_VENDOR_ID FROM PO_VENDORS PV, PER_ALL_PEOPLE_F PAPF
1371 WHERE NVL(pv.employee_id, - 99) = papf.person_id (+)
1372 AND NVL(papf.EFFECTIVE_START_DATE, SYSDATE) <= SYSDATE
1373 AND NVL(papf.EFFECTIVE_END_DATE, SYSDATE) >= SYSDATE
1374 --bug 8530918 AND NVL(papf.national_identifier, NVL(pv.individual_1099, pv.num_1099)) = TO_CHAR(l_TAXPAYER_ID);
1375 AND rtrim(
1376 substr(
1377 replace(
1378 nvl(papf.national_identifier,
1379 nvl(pv.individual_1099,pv.num_1099)
1380 ),
1381 '-'),
1382 1,10)
1383 ) ||
1384 substr(pv.global_attribute12,1,1) = TO_CHAR(l_TAXPAYER_ID);
1385
1386 IF p_debug_log = 'Y' THEN
1387 FND_FILE.put_line( FND_FILE.LOG,'34 . l_SUPP_AWT_TYPE_ID_TYPES'|| l_SUPP_AWT_TYPE_ID_TYPES || 'and' || 'l_VENDOR_ID' || l_VENDOR_ID);
1388 END IF;
1389
1390 INSERT INTO JL_ZZ_AP_SUPP_AWT_TYPES(
1391 SUPP_AWT_TYPE_ID,
1392 VENDOR_ID,
1393 AWT_TYPE_CODE,
1394 WH_SUBJECT_FLAG,
1395 CREATED_BY,
1396 CREATION_DATE,
1397 LAST_UPDATED_BY,
1398 LAST_UPDATE_DATE,
1399 LAST_UPDATE_LOGIN)
1400 VALUES
1401 (l_SUPP_AWT_TYPE_ID_TYPES, --SUPP_AWT_TYPE_ID,
1402 l_VENDOR_ID, --VENDOR_ID
1403 'TURN_BSAS', --AWT_TYPE_CODE
1404 'Y', --WH_SUBJECT_FLAG
1405 l_created_by, --CREATED_BY
1406 l_creation_DATE, --CREATION_DATE
1407 l_last_UPDATEd_by, --LAST_UPDATED_BY
1408 l_last_UPDATE_DATE, --LAST_UPDATE_DATE
1409 l_last_UPDATE_login); --LAST_UPDATE_LOGIN
1410
1411 IF p_debug_log = 'Y' THEN
1412 FND_FILE.put_line( FND_FILE.LOG,'34 . Inserted data in JL_ZZ_AP_SUPP_AWT_TYPES for '|| SQL%ROWCOUNT ||' Records');
1413 END IF;
1414
1415 EXCEPTION
1416 WHEN OTHERS THEN
1417
1418 IF p_debug_log = 'Y' THEN
1419 FND_FILE.put_line( FND_FILE.LOG,'35 .Insert in JL_ZZ_AP_SUPP_AWT_TYPES not done - '|| SQLCODE ||' -ERROR- '|| SQLERRM);
1420 END IF;
1421
1422 END;
1423
1424
1425
1426 BEGIN
1427 --- TO INSERT INTO JL_ZZ_AP_SUP_AWT_CD (3.3.2)
1428
1429 SELECT SAWT.SUPP_AWT_TYPE_ID
1430 INTO l_SUPP_AWT_TYPE_ID_CD
1431 FROM PO_VENDORS PV,
1432 JL_ZZ_AP_SUPP_AWT_TYPES SAWT,
1433 PER_ALL_PEOPLE_F PAPF
1434 WHERE SAWT.VENDOR_ID = PV.VENDOR_ID
1435 AND NVL(pv.employee_id, - 99) = papf.person_id (+)
1436 AND NVL(papf.EFFECTIVE_START_DATE, SYSDATE) <= SYSDATE
1437 AND NVL(papf.EFFECTIVE_END_DATE, SYSDATE) >= SYSDATE
1438 AND SAWT.AWT_TYPE_CODE = l_AWT_TAX_TYPE
1439 --bug 8530918 AND NVL(papf.national_identifier, NVL(pv.individual_1099, pv.num_1099)) = TO_CHAR(l_TAXPAYER_ID);
1440 AND rtrim(
1441 substr(
1442 replace(
1443 nvl(papf.national_identifier,
1444 nvl(pv.individual_1099,pv.num_1099)
1445 ),
1446 '-'),
1447 1,10)
1448 ) ||
1449 substr(pv.global_attribute12,1,1) = TO_CHAR(l_TAXPAYER_ID);
1450
1451 IF p_debug_log = 'Y' THEN
1452 FND_FILE.put_line( FND_FILE.LOG,'36. EFFECTIVE_END_DATE (l_START_DATE-1) : '|| l_START_DATE);
1453 FND_FILE.put_line( FND_FILE.LOG,'36. l_tax_id : '|| l_tax_id || 'l_TAXPAYER_ID : '|| l_TAXPAYER_ID);
1454 END IF;
1455
1456
1457 SELECT jl_zz_ap_sup_awt_cd_s.nextval INTO l_SUPP_AWT_CODE_ID_SEQ FROM dual;
1458
1459 IF p_debug_log = 'Y' THEN
1460 FND_FILE.put_line( FND_FILE.LOG,'37. l_SUPP_AWT_CODE_ID_SEQ'|| l_SUPP_AWT_CODE_ID_SEQ);
1461 FND_FILE.put_line( FND_FILE.LOG,'37. l_SUPP_AWT_TYPE_ID_CD'|| l_SUPP_AWT_TYPE_ID_CD);
1462 END IF;
1463 -- code to UPDATE the EFFECTIVE_END_DATE for other primary_tax_flag = 'Y' and other l_tax_id
1464
1465 UPDATE JL_ZZ_AP_SUP_AWT_CD SAWTC SET SAWTC.EFFECTIVE_END_DATE = l_START_DATE - 1
1466 WHERE SAWTC.primary_tax_flag = 'Y'
1467 AND SAWTC.SUPP_AWT_CODE_ID <> l_SUPP_AWT_CODE_ID_SEQ
1468 AND SAWTC.SUPP_AWT_TYPE_ID = l_SUPP_AWT_TYPE_ID_CD
1469 --AND SAWTC.TAX_ID <> l_tax_id -- other l_tax_id
1470 AND SAWTC.EFFECTIVE_END_DATE IS NULL;
1471
1472 IF p_debug_log = 'Y' THEN
1473 FND_FILE.PUT_LINE( FND_FILE.LOG,'38 .UPDATED DATA IN JL_ZZ_AP_SUP_AWT_CD FOR '|| SQL%ROWCOUNT || 'RECORDS');
1474 FND_FILE.PUT_LINE( FND_FILE.LOG,'38. EFFECTIVE_END_DATE (L_START_DATE-1) : '|| L_START_DATE);
1475 FND_FILE.PUT_LINE( FND_FILE.LOG,'38. L_TAX_ID : '|| L_TAX_ID || 'for L_TAXPAYER_ID : '|| L_TAXPAYER_ID);
1476
1477 END IF;
1478
1479
1480 INSERT INTO JL_ZZ_AP_SUP_AWT_CD
1481 (SUPP_AWT_CODE_ID,
1482 SUPP_AWT_TYPE_ID,
1483 TAX_ID,
1484 PRIMARY_TAX_FLAG,
1485 CREATED_BY,
1486 CREATION_DATE,
1487 LAST_UPDATED_BY,
1488 LAST_UPDATE_DATE,
1489 LAST_UPDATE_LOGIN,
1490 ORG_ID,
1491 EFFECTIVE_START_DATE,
1492 EFFECTIVE_END_DATE)
1493 VALUES
1494 (l_SUPP_AWT_CODE_ID_SEQ, --SUPP_AWT_CODE_ID
1495 l_SUPP_AWT_TYPE_ID_CD, --SUPP_AWT_TYPE_ID
1496 l_tax_id, --TAX_ID
1497 'Y', --PRIMARY_TAX_FLAG
1498 l_created_by, --CREATED_BY
1499 l_creation_DATE, --CREATION_DATE
1500 l_last_UPDATEd_by, --LAST_UPDATED_BY
1501 l_last_UPDATE_DATE, --LAST_UPDATE_DATE
1502 l_last_UPDATE_login, --LAST_UPDATE_LOGIN
1503 l_ORG_ID, --ORG_ID
1504 l_START_DATE, --EFFECTIVE_START_DATE
1505 NULL); --EFFECTIVE_END_DATE
1506
1507
1508 IF p_debug_log = 'Y' THEN
1509 FND_FILE.put_line( FND_FILE.LOG,'38 . Inserted data in JL_ZZ_AP_SUP_AWT_CD for '|| SQL%ROWCOUNT ||' Records and code id is :'|| l_SUPP_AWT_TYPE_ID_CD);
1510 END IF;
1511
1512 COMMIT;
1513
1514
1515 EXCEPTION
1516 WHEN OTHERS THEN
1517
1518 IF p_debug_log = 'Y' THEN
1519 FND_FILE.put_line( FND_FILE.LOG,'39 . Failed to INSERT into JL_ZZ_AP_SUP_AWT_CD Insert not done '|| SQLCODE || 'ERROR' || SQLERRM);
1520 END IF;
1521
1522
1523 IF p_debug_log = 'Y' THEN
1524 FND_FILE.put_line( FND_FILE.LOG,'39. l_SUPP_AWT_CODE_ID_CD'|| l_SUPP_AWT_CODE_ID_CD);
1525 END IF;
1526
1527 BEGIN
1528 SELECT 'Y', SAWTC.SUPP_AWT_CODE_ID INTO taxtype_code_check, l_SUPP_AWT_CODE_ID_CD
1529 FROM JL_ZZ_AP_SUP_AWT_CD SAWTC
1530 WHERE SAWTC.SUPP_AWT_TYPE_ID = l_SUPP_AWT_TYPE_ID_CD
1531 AND SAWTC.TAX_ID = l_tax_id;
1532 --AND (SAWTC.EFFECTIVE_START_DATE = l_START_DATE OR SAWTC.EFFECTIVE_END_DATE = l_END_DATE);
1533
1534
1535 EXCEPTION
1536 WHEN NO_DATA_FOUND THEN
1537 taxtype_code_check := 'N';
1538 END;
1539
1540 IF taxtype_code_check = 'Y' THEN
1541
1542 UPDATE JL_ZZ_AP_SUP_AWT_CD SET EFFECTIVE_END_DATE = NULL
1543 WHERE SUPP_AWT_CODE_ID = l_SUPP_AWT_CODE_ID_CD;
1544 FND_FILE.PUT_LINE( FND_FILE.LOG,'40 Records already present in JL_ZZ_AP_SUP_AWT_CD table TYPE ID'|| l_SUPP_AWT_TYPE_ID_CD);
1545 FND_FILE.PUT_LINE( FND_FILE.LOG,'40. l_tax_id'|| l_tax_id || 'L_EFFECTIVE_START_DATE' || L_START_DATE);
1546
1547
1548 NULL;
1549 ELSE
1550 ----If insertion fails, then will do the UPDATE in awt_cd_all
1551 UPDATE JL_ZZ_AP_SUP_AWT_CD SET
1552 TAX_ID = l_tax_id,
1553 PRIMARY_TAX_FLAG = 'Y',
1554 EFFECTIVE_START_DATE = l_START_DATE,
1555 EFFECTIVE_END_DATE = NULL
1556 WHERE SUPP_AWT_CODE_ID = l_SUPP_AWT_CODE_ID_CD;
1557
1558 END IF;
1559
1560 IF p_debug_log = 'Y' THEN
1561 FND_FILE.put_line( FND_FILE.LOG,'41 . l_tax_id'|| l_tax_id || 'l_EFFECTIVE_START_DATE' || l_START_DATE);
1562 FND_FILE.PUT_LINE( FND_FILE.LOG,'41 . UPDATED DATA IN JL_ZZ_AP_SUP_AWT_CD for '|| SQL%ROWCOUNT ||' Records and code id is :'|| l_SUPP_AWT_TYPE_ID_CD);
1563 END IF;
1564
1565 END;
1566
1567
1568 IF p_debug_log = 'Y' THEN
1569 FND_FILE.put_line( FND_FILE.LOG,'42 . l_tax_id'|| l_tax_id || 'l_SUPP_AWT_CODE_ID_CD' || l_SUPP_AWT_CODE_ID_CD || 'l_EFFECTIVE_START_DATE-1' || l_START_DATE);
1570 FND_FILE.PUT_LINE( FND_FILE.LOG,'42 . UPDATED DATA IN JL_ZZ_AP_SUP_AWT_CD FOR PRIMARY_TAX_FLAG AS N AND EFFECTIVE_START_DATE FOR '|| SQL%ROWCOUNT ||' RECORDS');
1571 END IF;
1572
1573 -- Code to insert/UPDATE JL_ZZ_AP_INV_DIS_WH (TDD 3.3.2)
1574
1575
1576 BEGIN
1577 -- To get the current month Supp_Awt_Code_id for the present tax_id
1578
1579 SELECT SAWTC.SUPP_AWT_CODE_ID
1580 INTO l_SUPP_AWT_CODE_ID_CD
1581 FROM JL_ZZ_AP_SUP_AWT_CD SAWTC,
1582 PO_VENDORS PV,
1583 JL_ZZ_AP_SUPP_AWT_TYPES SAWT,
1584 PER_ALL_PEOPLE_F PAPF
1585 WHERE SAWT.VENDOR_ID = PV.VENDOR_ID
1586 AND NVL(pv.employee_id, - 99) = papf.person_id (+)
1587 AND NVL(papf.EFFECTIVE_START_DATE, SYSDATE) <= SYSDATE
1588 AND NVL(papf.EFFECTIVE_END_DATE, SYSDATE) >= SYSDATE
1589 AND SAWT.AWT_TYPE_CODE = l_AWT_TAX_TYPE
1590 AND SAWT.SUPP_AWT_TYPE_ID = SAWTC.SUPP_AWT_TYPE_ID
1591 --AND SAWTC.TAX_ID in (SELECT TAX_ID FROM AP_TAX_CODES WHERE name like 'TURN_BSAS_GRP%')
1592 AND SAWTC.TAX_ID = l_tax_id
1593 AND SAWTC.primary_tax_flag = 'Y'
1594 AND SAWTC.EFFECTIVE_START_DATE = l_START_DATE
1595 AND sawtc.effective_end_DATE IS NULL
1596 --bug 8530918 AND NVL(papf.national_identifier, NVL(pv.individual_1099, pv.num_1099)) = TO_CHAR(l_TAXPAYER_ID);
1597 AND rtrim(
1598 substr(
1599 replace(
1600 nvl(papf.national_identifier,
1601 nvl(pv.individual_1099,pv.num_1099)
1602 ),
1603 '-'),
1604 1,10)
1605 ) ||
1606 substr(pv.global_attribute12,1,1) = TO_CHAR(l_TAXPAYER_ID);
1607
1608 SELECT JL_ZZ_AP_INV_DIS_WH_S.NEXTVAL INTO l_INV_DISTRIB_AWT_ID_INV FROM dual;
1609
1610 EXCEPTION
1611 WHEN OTHERS THEN
1612 IF p_debug_log = 'Y' THEN
1613 FND_FILE.PUT_LINE( FND_FILE.LOG,'43 UPDATE NOT DONE FOR JL_ZZ_AP_INV_DIS_WH - '|| SQLCODE ||' -ERROR- '|| SQLERRM);
1614 END IF;
1615 END;
1616
1617 OPEN CUR3(l_TAXPAYER_ID);
1618 LOOP
1619 FETCH CUR3 INTO INV_REC;
1620 EXIT WHEN CUR3%NOTFOUND;
1621
1622
1623 BEGIN
1624
1625 AWT_CODE_INV_AVAIL_FLAG := 'N';
1626
1627 SELECT 'Y', INV_DISTRIB_AWT_ID INTO AWT_CODE_INV_AVAIL_FLAG, l_INV_DISTRIB_AWT_ID_DIS
1628 FROM JL_ZZ_AP_INV_DIS_WH
1629 WHERE INVOICE_ID = INV_REC.INVOICE_ID
1630 AND DISTRIBUTION_LINE_NUMBER = INV_REC.DISTRIBUTION_LINE_NUMBER
1631 AND INVOICE_DISTRIBUTION_ID = INV_REC.INVOICE_DISTRIBUTION_ID
1632 AND SUPP_AWT_CODE_ID = l_SUPP_AWT_CODE_ID_CD;
1633 /*(SELECT SUPP_AWT_CODE_ID FROM JL_ZZ_AP_SUP_AWT_CD SAWTC, JL_ZZ_AP_SUPP_AWT_TYPES SAWT
1634 WHERE SAWT.SUPP_AWT_TYPE_ID = SAWTC.SUPP_AWT_TYPE_ID
1635 AND SAWTC.TAX_ID IN (SELECT TAX_ID FROM AP_TAX_CODES WHERE name LIKE 'TURN_BSAS_GRP%')); */
1636
1637 EXCEPTION
1638 WHEN NO_DATA_FOUND THEN
1639 AWT_CODE_INV_AVAIL_FLAG := 'N';
1640 FND_FILE.put_line( FND_FILE.LOG,'44 No earlier data in Inv Dist for this Invoice'|| SQLCODE ||' -ERROR- '|| SQLERRM);
1641
1642 WHEN OTHERS THEN
1643 AWT_CODE_INV_AVAIL_FLAG := 'N';
1644 FND_FILE.put_line( FND_FILE.LOG,'44 Error in Inv Dist select'|| SQLCODE ||' -ERROR- '|| SQLERRM);
1645 END;
1646
1647 IF AWT_CODE_INV_AVAIL_FLAG = 'N' THEN
1648
1649 IF p_debug_log = 'Y' THEN
1650 FND_FILE.put_line( FND_FILE.LOG,'45 l_INV_DISTRIB_AWT_ID_INV'|| l_INV_DISTRIB_AWT_ID_INV);
1651 FND_FILE.put_line( FND_FILE.LOG,'45 INV_REC.INVOICE_ID'|| INV_REC.INVOICE_ID);
1652 FND_FILE.put_line( FND_FILE.LOG,'45 INV_REC.DISTRIBUTION_LINE_NUMBER'|| INV_REC.DISTRIBUTION_LINE_NUMBER);
1653 FND_FILE.put_line( FND_FILE.LOG,'45 l_SUPP_AWT_CODE_ID_CD'|| l_SUPP_AWT_CODE_ID_CD);
1654 FND_FILE.put_line( FND_FILE.LOG,'45 INVOICE_DISTRIBUTION_ID'|| INV_REC.INVOICE_DISTRIBUTION_ID);
1655 END IF;
1656 ---code to insert data INTO jl_zz_ap_inv_dis_wh_all
1657 BEGIN
1658
1659 INSERT INTO JL_ZZ_AP_INV_DIS_WH
1660 (INV_DISTRIB_AWT_ID,
1661 INVOICE_ID,
1662 DISTRIBUTION_LINE_NUMBER,
1663 SUPP_AWT_CODE_ID,
1664 CREATED_BY,
1665 CREATION_DATE,
1666 LAST_UPDATED_BY,
1667 LAST_UPDATE_DATE,
1668 LAST_UPDATE_LOGIN,
1669 ORG_ID,
1670 INVOICE_DISTRIBUTION_ID)
1671 VALUES
1672 (l_INV_DISTRIB_AWT_ID_INV, --INV_DISTRIB_AWT_ID
1673 INV_REC.INVOICE_ID, --INVOICE_ID
1674 INV_REC.DISTRIBUTION_LINE_NUMBER, --DISTRIBUTION_LINE_NUMBER
1675 l_SUPP_AWT_CODE_ID_CD, --SUPP_AWT_CODE_ID
1676 l_created_by, --CREATED_BY
1677 l_creation_DATE, --CREATION_DATE
1678 l_last_UPDATEd_by, --LAST_UPDATED_BY
1679 l_last_UPDATE_DATE, --LAST_UPDATE_DATE
1680 l_last_UPDATE_login, --LAST_UPDATE_LOGIN
1681 l_ORG_ID, --ORG_ID
1682 INV_REC.INVOICE_DISTRIBUTION_ID); --Invoice distribution ID -- R12 Changes
1683
1684
1685
1686
1687 SELECT JL_ZZ_AP_INV_DIS_WH_S.NEXTVAL INTO l_INV_DISTRIB_AWT_ID_INV FROM dual;
1688
1689 IF p_debug_log = 'Y' THEN
1690 FND_FILE.put_line( FND_FILE.LOG,'45 A. '|| SQL%ROWCOUNT ||'Inserted records in JL_ZZ_AP_INV_DIS_WH '|| SQLCODE || 'ERROR' || SQLERRM);
1691 END IF;
1692
1693 EXCEPTION
1694 WHEN OTHERS THEN
1695 IF p_debug_log = 'Y' THEN
1696 FND_FILE.put_line( FND_FILE.LOG,'45 A. Failed while Inserting records in JL_ZZ_AP_INV_DIS_WH '|| SQLCODE || 'ERROR' || SQLERRM);
1697 END IF;
1698 END;
1699 ELSE
1700 BEGIN
1701 IF p_debug_log = 'Y' THEN
1702 FND_FILE.put_line( FND_FILE.LOG,'45 B l_INV_DISTRIB_AWT_ID_INV'|| l_INV_DISTRIB_AWT_ID_INV);
1703 FND_FILE.put_line( FND_FILE.LOG,'45 B INV_REC.INVOICE_ID'|| INV_REC.INVOICE_ID);
1704 FND_FILE.put_line( FND_FILE.LOG,'45 B INV_REC.DISTRIBUTION_LINE_NUMBER'|| INV_REC.DISTRIBUTION_LINE_NUMBER);
1705 FND_FILE.put_line( FND_FILE.LOG,'45 B l_SUPP_AWT_CODE_ID_CD'|| l_SUPP_AWT_CODE_ID_CD);
1706 FND_FILE.put_line( FND_FILE.LOG,'45 B l_INV_DISTRIB_AWT_ID_DIS'|| l_INV_DISTRIB_AWT_ID_DIS);
1707 FND_FILE.put_line( FND_FILE.LOG,'45 B INVOICE_DISTRIBUTION_ID'|| INV_REC.INVOICE_DISTRIBUTION_ID);
1708 END IF;
1709
1710 /*SELECT INV_DISTRIB_AWT_ID INTO l_INV_DISTRIB_AWT_ID_DIS
1711 FROM JL_ZZ_AP_INV_DIS_WH
1712 WHERE INVOICE_ID = INV_REC.INVOICE_ID
1713 AND DISTRIBUTION_LINE_NUMBER = INV_REC.DISTRIBUTION_LINE_NUMBER
1714 AND INVOICE_DISTRIBUTION_ID = INV_REC.INVOICE_DISTRIBUTION_ID
1715 AND SUPP_AWT_CODE_ID IN
1716 (SELECT SUPP_AWT_CODE_ID FROM JL_ZZ_AP_SUP_AWT_CD SAWTC, JL_ZZ_AP_SUPP_AWT_TYPES SAWT
1717 WHERE SAWT.SUPP_AWT_TYPE_ID = SAWTC.SUPP_AWT_TYPE_ID
1718 AND SAWTC.primary_tax_flag = 'Y'
1719 AND SAWTC.TAX_ID IN (SELECT TAX_ID FROM AP_TAX_CODES WHERE name LIKE 'TURN_BSAS_GRP%')
1720 AND sawtc.effective_end_DATE IS NULL);*/
1721
1722
1723 UPDATE JL_ZZ_AP_INV_DIS_WH SET SUPP_AWT_CODE_ID = l_SUPP_AWT_CODE_ID_CD
1724 WHERE INV_DISTRIB_AWT_ID = l_INV_DISTRIB_AWT_ID_DIS;
1725
1726 IF p_debug_log = 'Y' THEN
1727 FND_FILE.put_line( FND_FILE.LOG,'46 A. Updated '|| SQL%ROWCOUNT ||' records in JL_ZZ_AP_INV_DIS_WH '|| SQLCODE || 'ERROR' || SQLERRM);
1728 FND_FILE.put_line( FND_FILE.LOG,'46 A. l_SUPP_AWT_CODE_ID_CD:'|| l_SUPP_AWT_CODE_ID_CD);
1729 FND_FILE.put_line( FND_FILE.LOG,'46 A. l_INV_DISTRIB_AWT_ID_DIS:'|| l_INV_DISTRIB_AWT_ID_DIS);
1730 END IF;
1731
1732 EXCEPTION
1733 WHEN OTHERS THEN
1734 IF p_debug_log = 'Y' THEN
1735 FND_FILE.put_line( FND_FILE.LOG,'46 B. Failed while updating records in JL_ZZ_AP_INV_DIS_WH '|| SQLCODE || 'ERROR' || SQLERRM);
1736 END IF;
1737 END;
1738
1739 END IF;
1740
1741 END LOOP;
1742 CLOSE CUR3;
1743
1744 IF p_debug_log = 'Y' THEN
1745 FND_FILE.PUT_LINE( FND_FILE.LOG,'46 C. UPDATED DATA IN JL_ZZ_AP_INV_DIS_WH FOR SUPP_AWT_CODE_ID ');
1746 END IF;
1747
1748
1749
1750 Insert_Row (l_PUBLISH_DATE,
1751 l_START_DATE,
1752 l_END_DATE,
1753 l_TAXPAYER_ID,
1754 l_CONTRIBUTOR_TYPE_CODE,
1755 l_NEW_CONTRIBUTOR_FLAG,
1756 l_RATE_CHANGE_FLAG,
1757 l_PERCEPTION_RATE,
1758 l_WHT_RATE,
1759 l_PERCEPTION_GROUP_NUM,
1760 l_WHT_GROUP_NUM,
1761 l_WHT_DEFAULT_FLAG,
1762 'AP');
1763
1764 -- delete JL_AR_TURN_UPL_T WHERE TAXPAYER_ID = l_TAXPAYER_ID;
1765
1766 IF p_debug_log = 'Y' THEN
1767 FND_FILE.put_line( FND_FILE.LOG,'47 . Inserted data in JL_AR_TURN_UPL for All Records ');
1768 END IF;
1769
1770 END IF;
1771
1772 END IF; -- AP coding and validation was completed here
1773
1774
1775 <<L3>>
1776
1777 NULL;
1778
1779 --Re Initialising the FLAGS:
1780 -----------------------------
1781
1782 duplicate_check_count := NULL;
1783 wht_check_unique := 'Y';
1784 duplicate_check_flag := 'N';
1785 same_rec_flag := 'N';
1786 wht_check_flag := 'N';
1787 exist_check_flag := 'N';
1788 WHT_GROUP_NUM_rate_flag := 'N';
1789 same_taxpayerid_flag := 'N';
1790 taxtype_code_check := 'N';
1791 l_taxpayer := 'N';
1792 l_taxpayer_ar := 'N';
1793 AWT_CODE_INV_AVAIL_FLAG := 'N';
1794
1795 EXCEPTION
1796 WHEN OTHERS THEN
1797 IF p_debug_log = 'Y' THEN
1798 FND_FILE.PUT_LINE( FND_FILE.LOG,'49 .TAXPAYER ID FAILED IN FINAL VALIDATION, FETCHING NEXT RECORD - '|| SQLCODE ||' -ERROR- '|| SQLERRM);
1799 END IF;
1800 UPDATE JGZZ_AR_TAX_GLOBAL_TMP SET JG_INFO_V1 = 'JLZZ_RECORD_FAILED_FINAL_CHECK'
1801 WHERE JG_INFO_N1 = l_TAXPAYER_ID AND JG_INFO_D1 = l_START_DATE AND JG_INFO_D2 = l_END_DATE;
1802
1803 END;
1804
1805
1806 COMMIT;
1807
1808 END LOOP;
1809 CLOSE C3;
1810
1811
1812
1813
1814 EXCEPTION
1815 WHEN OTHERS THEN
1816 IF p_debug_log = 'Y' THEN
1817 FND_FILE.PUT_LINE( FND_FILE.LOG,'49 .AN ERROR WAS ENCOUNTERED WHEN FINAL VALIDATION - '|| SQLCODE ||' -ERROR- '|| SQLERRM);
1818 END IF;
1819 UPDATE JGZZ_AR_TAX_GLOBAL_TMP SET JG_INFO_V1 = 'JLZZ_RECORD_FAILED_FINAL_CHECK'
1820 WHERE JG_INFO_N1 = l_TAXPAYER_ID AND JG_INFO_D1 = l_START_DATE AND JG_INFO_D2 = l_END_DATE;
1821
1822 END FINAL_VALIDATION;
1823
1824
1825
1826 -- Default Setup for records not present in current TMP file (TDD 9.4.B)
1827
1828 PROCEDURE VALIDATE_AWT_SETUP
1829 IS
1830
1831
1832 l_PUBLISH_DATE DATE;
1833 l_START_DATE DATE;
1834 l_END_DATE DATE;
1835 l_TAXPAYER_ID NUMBER(15);
1836 l_CONTRIBUTOR_TYPE_CODE VARCHAR2(1);
1837 l_NEW_CONTRIBUTOR_FLAG VARCHAR2(1);
1838 l_RATE_CHANGE_FLAG VARCHAR2(1);
1839 l_PERCEPTION_RATE NUMBER(15,2);
1840 l_WHT_RATE NUMBER(15,2);
1841 l_PERCEPTION_GROUP_NUM NUMBER(15);
1842 l_WHT_GROUP_NUM NUMBER(15);
1843 l_WHT_DEFAULT_FLAG VARCHAR2(1);
1844
1845 l_TAXPAYER_ID_ALL NUMBER(15);
1846 l_START_DATE_CURR_MAX DATE;
1847 l_START_DATE_SEC_MAX DATE := NULL;
1848 l_END_DATE_SEC_MAX DATE := NULL;
1849 l_END_DATE_CURR_MAX DATE;
1850 l_PUBLISH_DATE_CURR_MAX DATE;
1851 l_EFFECTIVE_START_DATE DATE;
1852 l_WHT_GROUP_NUM_DEF_NO NUMBER;
1853 def_taxtype_code_check VARCHAR2(1) := 'N';
1854 AWT_CODE_INV_AVAIL_FLAG VARCHAR2(1) := 'N';
1855
1856 l_WHT_GROUP_NUM_DEF_ATC AP_TAX_CODES_ALL.NAME%TYPE;
1857 l_TAX_ID_DEF_ATC AP_TAX_CODES_ALL.TAX_ID%TYPE;
1858 l_WHT_RATE_DEF_ATR AP_AWT_TAX_RATES_ALL.TAX_RATE%TYPE;
1859 ALL_REC JL_AR_TURN_UPL%ROWTYPE;
1860
1861 l_SUPP_AWT_CODE_ID_CD JL_ZZ_AP_SUP_AWT_CD_ALL.SUPP_AWT_CODE_ID%TYPE;
1862 l_SUPP_AWT_TYPE_ID_CD JL_ZZ_AP_SUP_AWT_CD_ALL.SUPP_AWT_TYPE_ID%TYPE;
1863 l_tax_id ap_tax_codes.tax_id%TYPE;
1864 l_SUPP_AWT_TYPE_ID_TYPES JL_ZZ_AP_SUPP_AWT_TYPES.SUPP_AWT_TYPE_ID%TYPE;
1865 l_VENDOR_ID PO_VENDORS.VENDOR_ID%TYPE;
1866 l_INV_DISTRIB_AWT_ID_INV JL_ZZ_AP_INV_DIS_WH_ALL.INV_DISTRIB_AWT_ID%TYPE;
1867 l_INVOICE_ID_INA AP_INVOICES_ALL.INVOICE_ID%TYPE;
1868 l_DISTRIBUTION_LINE_NUMBER_IND AP_INVOICE_DISTRIBUTIONS_ALL.DISTRIBUTION_LINE_NUMBER%TYPE;
1869 l_INVOICE_DISTRIBUTION_ID_IND AP_INVOICE_DISTRIBUTIONS_ALL.INVOICE_DISTRIBUTION_ID%TYPE;
1870 l_INV_DISTRIB_AWT_ID_DIS JL_ZZ_AP_INV_DIS_WH_ALL.INV_DISTRIB_AWT_ID%TYPE;
1871 l_SUPP_AWT_CODE_ID_SEQ JL_ZZ_AP_SUP_AWT_CD_ALL.SUPP_AWT_CODE_ID%TYPE;
1872
1873
1874 --WHO COLUMNS:
1875 -----------------
1876
1877 l_created_by NUMBER(15) := NVL(fnd_profile.value('USER_ID'), 1);
1878 l_creation_DATE DATE := SYSDATE;
1879 l_last_UPDATEd_by NUMBER(15) := NVL(fnd_profile.value('USER_ID'), 1);
1880 l_last_UPDATE_DATE DATE := SYSDATE;
1881 l_last_UPDATE_login NUMBER(15) := NVL(fnd_global.conc_login_id, 1);
1882
1883
1884 CURSOR CUR4(l_TAXPAYERID_C NUMBER) IS
1885 SELECT APINA.INVOICE_ID,
1886 APIND.DISTRIBUTION_LINE_NUMBER,
1887 APIND.INVOICE_DISTRIBUTION_ID
1888 FROM PO_VENDORS PV,
1889 AP_INVOICES APINA,
1890 AP_INVOICE_DISTRIBUTIONS APIND,
1891 PER_ALL_PEOPLE_F PAPF
1892 WHERE PV.VENDOR_ID = APINA.VENDOR_ID
1893 AND NVL(pv.employee_id, - 99) = papf.person_id (+)
1894 AND NVL(papf.EFFECTIVE_START_DATE, SYSDATE) <= SYSDATE
1895 AND NVL(papf.EFFECTIVE_END_DATE, SYSDATE) >= SYSDATE
1896 --AND APIND.TAX_CODE_ID in (SELECT TAX_ID FROM AP_TAX_CODES WHERE name like 'TURN_BSAS_GRP%')
1897 AND APINA.INVOICE_ID = APIND.INVOICE_ID
1898 AND APIND.LINE_TYPE_LOOKUP_CODE = 'ITEM'
1899 AND APIND.GLOBAL_ATTRIBUTE3 IN
1900 -- (SELECT LOCATION_ID FROM HR_LOCATIONS_ALL WHERE UPPER(LOCATION_CODE) = 'BUENOS AIRES' AND trunc(SYSDATE) <= NVL(inactive_DATE, trunc(SYSDATE))) --bug 8622329
1901 (SELECT LOCATION_ID FROM HR_LOCATIONS_ALL WHERE UPPER(REGION_2) = 'BUENOS AIRES' AND trunc(SYSDATE) <= NVL(inactive_DATE, trunc(SYSDATE))) --bug 9865805
1902 AND APINA.INVOICE_ID = APIND.INVOICE_ID
1903 AND NVL(APINA.INVOICE_AMOUNT,0) <> NVL(APINA.AMOUNT_PAID,0)
1904 --bug 8530918 AND NVL(papf.national_identifier, NVL(pv.individual_1099, pv.num_1099)) = TO_CHAR(l_TAXPAYERID_C);
1905 AND rtrim(
1906 substr(
1907 replace(
1908 nvl(papf.national_identifier,
1909 nvl(pv.individual_1099,pv.num_1099)
1910 ),
1911 '-'),
1912 1,10)
1913 ) ||
1914 substr(pv.global_attribute12,1,1) = TO_CHAR(l_TAXPAYERID_C)
1915 AND pv.vendor_id in (select vendor_id from po_vendor_sites_all where org_id = l_org_id); --bug 10091261
1916
1917 INV_REC CUR4%ROWTYPE;
1918
1919
1920 -- To get the datas (having the MAX start DATE- current) FROM ALL table
1921
1922 CURSOR C4(l_START_DATE_CURR_MAX DATE) IS
1923 SELECT * FROM JL_AR_TURN_UPL WHERE START_DATE <> l_START_DATE_CURR_MAX;
1924
1925 V_ALLREC C4%ROWTYPE;
1926
1927
1928
1929
1930 BEGIN
1931
1932 BEGIN
1933 SELECT MAX(START_DATE) INTO l_START_DATE_CURR_MAX FROM JL_AR_TURN_UPL;
1934 SELECT MAX(END_DATE) INTO l_END_DATE_CURR_MAX FROM JL_AR_TURN_UPL;
1935 --SELECT MAX(PUBLISH_DATE) INTO l_PUBLISH_DATE_CURR_MAX FROM JL_AR_TURN_UPL;
1936 SELECT name
1937 INTO l_WHT_GROUP_NUM_DEF_ATC
1938 FROM AP_TAX_CODES ATC
1939 WHERE global_attribute1 = 'Y'
1940 AND tax_type = 'AWT';
1941
1942 SELECT tax_id
1943 INTO l_TAX_ID_DEF_ATC
1944 FROM AP_TAX_CODES
1945 WHERE name = l_WHT_GROUP_NUM_DEF_ATC
1946 AND tax_type = 'AWT';
1947
1948 SELECT TAX_RATE
1949 INTO l_WHT_RATE_DEF_ATR
1950 FROM AP_AWT_TAX_RATES
1951 WHERE TAX_NAME = l_WHT_GROUP_NUM_DEF_ATC;
1952
1953 SELECT TRIM(leading '0' FROM (SUBSTR(tax_name, 14, 2)))
1954 INTO l_WHT_GROUP_NUM_DEF_NO
1955 FROM AP_AWT_TAX_RATES
1956 WHERE tax_name = l_WHT_GROUP_NUM_DEF_ATC;
1957
1958 EXCEPTION
1959 WHEN OTHERS THEN
1960 IF p_debug_log = 'Y' THEN
1961 FND_FILE.PUT_LINE( FND_FILE.LOG,'50 .AN ERROR WAS ENCOUNTERED WHILE CHECKING DEFAULT AP TAX CODE FOR PREV MONTH- '|| SQLCODE ||' -ERROR- '|| SQLERRM);
1962 END IF;
1963 END;
1964 IF p_debug_log = 'Y' THEN
1965 FND_FILE.PUT_LINE( FND_FILE.LOG, 'l_START_DATE_CURR_MAX' || l_START_DATE_CURR_MAX);
1966 FND_FILE.PUT_LINE( FND_FILE.LOG, 'l_END_DATE_CURR_MAX' || l_END_DATE_SEC_MAX);
1967 FND_FILE.PUT_LINE( FND_FILE.LOG, 'l_WHT_GROUP_NUM_DEF_ATC' || l_WHT_GROUP_NUM_DEF_ATC);
1968 FND_FILE.PUT_LINE( FND_FILE.LOG, 'l_TAX_ID_DEF_ATC' || l_TAX_ID_DEF_ATC);
1969 FND_FILE.PUT_LINE( FND_FILE.LOG, 'l_WHT_RATE_DEF_ATR' || l_WHT_RATE_DEF_ATR);
1970 FND_FILE.PUT_LINE( FND_FILE.LOG, 'l_WHT_GROUP_NUM_DEF_NO' || l_WHT_GROUP_NUM_DEF_NO);
1971 END IF;
1972
1973
1974 OPEN C4(l_START_DATE_CURR_MAX);
1975 LOOP
1976 FETCH C4 INTO V_ALLREC;
1977 EXIT WHEN C4%NOTFOUND;
1978
1979
1980 l_PUBLISH_DATE := V_ALLREC.PUBLISH_DATE;
1981 l_START_DATE := V_ALLREC.START_DATE;
1982 l_END_DATE := V_ALLREC.END_DATE;
1983
1984 l_TAXPAYER_ID := V_ALLREC.TAXPAYER_ID;
1985 l_CONTRIBUTOR_TYPE_CODE := V_ALLREC.CONTRIBUTOR_TYPE_CODE;
1986 l_NEW_CONTRIBUTOR_FLAG := V_ALLREC.NEW_CONTRIBUTOR_FLAG;
1987 l_RATE_CHANGE_FLAG := V_ALLREC.RATE_CHANGE_FLAG;
1988 l_PERCEPTION_RATE := V_ALLREC.PERCEPTION_RATE;
1989 l_WHT_RATE := V_ALLREC.WHT_RATE;
1990 l_PERCEPTION_GROUP_NUM := V_ALLREC.PERCEPTION_GROUP_NUM;
1991 l_WHT_GROUP_NUM := V_ALLREC.WHT_GROUP_NUM;
1992
1993
1994 IF p_debug_log = 'Y' THEN
1995 FND_FILE.PUT_LINE( FND_FILE.LOG,'Processing for TAXPAYER_ID'|| l_TAXPAYER_ID || SQLCODE || 'ERROR' || SQLERRM);
1996 END IF;
1997 -- AP tax payer id available check
1998
1999 BEGIN
2000 SELECT DISTINCT 'Y' INTO l_taxpayer FROM PO_VENDORS PV, PER_ALL_PEOPLE_F PAPF
2001 WHERE NVL(pv.employee_id, - 99) = papf.person_id (+)
2002 AND NVL(papf.EFFECTIVE_START_DATE, SYSDATE) <= SYSDATE
2003 AND NVL(papf.EFFECTIVE_END_DATE, SYSDATE) >= SYSDATE
2004 --bug 8530918 AND NVL(papf.national_identifier, NVL(pv.individual_1099, pv.num_1099)) = TO_CHAR(l_TAXPAYER_ID);
2005 AND rtrim(
2006 substr(
2007 replace(
2008 nvl(papf.national_identifier,
2009 nvl(pv.individual_1099,pv.num_1099)
2010 ),
2011 '-'),
2012 1,10)
2013 ) ||
2014 substr(pv.global_attribute12,1,1) = TO_CHAR(l_TAXPAYER_ID);
2015
2016 EXCEPTION
2017
2018 WHEN NO_DATA_FOUND THEN
2019 l_taxpayer := 'N';
2020
2021 END;
2022 -- AR tax payer id available check
2023
2024 BEGIN
2025
2026 SELECT DISTINCT 'Y' INTO l_taxpayer_ar
2027 FROM HZ_PARTIES HZP,
2028 HZ_CUST_ACCOUNTS_ALL HZCA,
2029 HZ_CUST_ACCT_SITES_ALL HZAS,
2030 HZ_CUST_SITE_USES_ALL HZSU
2031 WHERE HZCA.PARTY_ID = HZP.PARTY_ID
2032 AND HZCA.CUST_ACCOUNT_ID = HZAS.CUST_ACCOUNT_ID
2033 AND HZAS.CUST_ACCT_SITE_ID = HZSU.CUST_ACCT_SITE_ID
2034 AND HZSU.ORG_ID = l_ORG_ID
2035 AND HZP.JGZZ_FISCAL_CODE = TO_CHAR(l_TAXPAYER_ID);
2036
2037 EXCEPTION
2038 WHEN NO_DATA_FOUND THEN
2039 l_taxpayer_ar := 'N';
2040
2041 END;
2042
2043 IF l_taxpayer_ar = 'Y' THEN
2044
2045 -- AR Code Hook
2046 BEGIN
2047
2048 JL_ZZ_AR_UPLOAD_TAXES.JL_AR_UPDATE_CUST_SITE_TAX(l_TAXPAYER_ID,
2049 l_AWT_TAX_TYPE,
2050 l_PERCEPTION_TAX_TYPE,
2051 l_ORG_ID,
2052 l_PUBLISH_DATE,
2053 l_START_DATE_CURR_MAX,
2054 l_END_DATE_CURR_MAX,
2055 l_RETURN_STATUS ); -- out parameter for status
2056
2057 IF l_RETURN_STATUS = 'S' THEN --bug 9907885
2058 FND_FILE.put_line(FND_FILE.LOG,'58. AR validtion completed successfully');
2059 END IF;
2060
2061 EXCEPTION
2062 WHEN OTHERS THEN
2063 IF p_debug_log = 'Y' THEN
2064 FND_FILE.put_line(FND_FILE.LOG,'58. AR validation failed with return status'|| l_RETURN_STATUS || SQLCODE || 'ERROR' || SQLERRM);
2065 END IF;
2066 RAISE_APPLICATION_ERROR(- 20999,'AR validation failed'|| SQLCODE ||' -ERROR- '|| SQLERRM);
2067 END;
2068
2069
2070 END IF;
2071
2072 IF l_taxpayer = 'Y' THEN -- AP validation starts here
2073
2074 BEGIN -- To get the datas (having the Second MAX start DATE) FROM ALL table
2075
2076
2077 SELECT MAX(START_DATE) INTO l_START_DATE_SEC_MAX FROM JL_AR_TURN_UPL WHERE TAXPAYER_ID = l_TAXPAYER_ID;
2078
2079 SELECT last_day(l_START_DATE_SEC_MAX) INTO l_END_DATE_SEC_MAX FROM dual;
2080
2081 EXCEPTION
2082 WHEN NO_DATA_FOUND THEN
2083 IF p_debug_log = 'Y' THEN
2084 FND_FILE.PUT_LINE( FND_FILE.LOG,'51 . NO DATA FOUND FOR TAXPAYER ID FOR PREV MONTH '|| L_TAXPAYER_ID);
2085 END IF;
2086 --GOTO L4; --For current month data, will get no data found and fetching next record
2087 WHEN OTHERS THEN
2088 IF p_debug_log = 'Y' THEN
2089 FND_FILE.PUT_LINE( FND_FILE.LOG,'51 .FAILED IN CHECKING TAXPAYER ID AND DEF ATC FOR PREV MONTH- '|| SQLCODE ||' -ERROR- '|| SQLERRM);
2090 END IF;
2091 END;
2092
2093 IF p_debug_log = 'Y' THEN
2094 FND_FILE.PUT_LINE( FND_FILE.LOG,'51 l_START_DATE_SEC_MAX'|| l_START_DATE_SEC_MAX ||'l_END_DATE_SEC_MAX '|| l_END_DATE_SEC_MAX);
2095 END IF;
2096
2097 IF p_debug_log = 'Y' THEN
2098 FND_FILE.PUT_LINE( FND_FILE.LOG,'51 . TAXPAYER ID FOR PREV MONTH '|| L_TAXPAYER_ID);
2099 END IF;
2100
2101
2102 --- To process only the last month data and to avoid the current month taxpayer id if any
2103 IF l_START_DATE_SEC_MAX < l_START_DATE_CURR_MAX THEN
2104 --- code to UPDATE awt_cd jl_zz_ap_sup_awt_cd_all (9.4 B)
2105 BEGIN
2106
2107 SELECT SAWTC.SUPP_AWT_TYPE_ID,
2108 SAWTC.SUPP_AWT_CODE_ID
2109 INTO l_SUPP_AWT_TYPE_ID_CD,
2110 l_SUPP_AWT_CODE_ID_CD
2111 FROM JL_ZZ_AP_SUP_AWT_CD SAWTC,
2112 PO_VENDORS PV,
2113 JL_ZZ_AP_SUPP_AWT_TYPES SAWT,
2114 PER_ALL_PEOPLE_F PAPF
2115 WHERE SAWT.VENDOR_ID = PV.VENDOR_ID
2116 AND NVL(pv.employee_id, - 99) = papf.person_id (+)
2117 AND NVL(papf.EFFECTIVE_START_DATE, SYSDATE) <= SYSDATE
2118 AND NVL(papf.EFFECTIVE_END_DATE, SYSDATE) >= SYSDATE
2119 AND SAWT.AWT_TYPE_CODE = l_AWT_TAX_TYPE
2120 AND SAWT.SUPP_AWT_TYPE_ID = SAWTC.SUPP_AWT_TYPE_ID
2121 AND SAWTC.primary_tax_flag = 'Y'
2122 --AND SAWT.WH_SUBJECT_FLAG = 'Y' -- Indicates whether the supplier is subject to the withholding tax type
2123 AND SAWTC.TAX_ID IN (SELECT TAX_ID FROM AP_TAX_CODES WHERE name LIKE 'TURN_BSAS_GRP%')
2124 AND SAWTC.EFFECTIVE_END_DATE IS NULL
2125 --bug 8530918 AND NVL(papf.national_identifier, NVL(pv.individual_1099, pv.num_1099)) = TO_CHAR(l_TAXPAYER_ID);
2126 AND rtrim(
2127 substr(
2128 replace(
2129 nvl(papf.national_identifier,
2130 nvl(pv.individual_1099,pv.num_1099)
2131 ),
2132 '-'),
2133 1,10)
2134 ) ||
2135 substr(pv.global_attribute12,1,1) = TO_CHAR(l_TAXPAYER_ID);
2136
2137
2138 --code to UPDATE/insert JL_ZZ_AP_SUP_AWT_CD
2139
2140 UPDATE JL_ZZ_AP_SUP_AWT_CD SET EFFECTIVE_END_DATE = l_END_DATE_SEC_MAX
2141 WHERE SUPP_AWT_CODE_ID = l_SUPP_AWT_CODE_ID_CD;
2142
2143 IF p_debug_log = 'Y' THEN
2144 FND_FILE.PUT_LINE( FND_FILE.LOG,'52 .UPDATED DATA IN JL_ZZ_AP_SUP_AWT_CD FOR '|| SQL%ROWCOUNT || 'RECORDS');
2145 FND_FILE.PUT_LINE( FND_FILE.LOG,'52. EFFECTIVE_END_DATE (l_END_DATE_SEC_MAX-1) :'||l_END_DATE_SEC_MAX||' FOR CODE_ID :'||l_SUPP_AWT_CODE_ID_CD);
2146 FND_FILE.PUT_LINE( FND_FILE.LOG,'52 l_TAX_ID_DEF_ATC : '|| l_TAX_ID_DEF_ATC || ' l_TAXPAYER_ID : '|| l_TAXPAYER_ID);
2147 END IF;
2148
2149
2150 SELECT jl_zz_ap_sup_awt_cd_s.nextval INTO l_SUPP_AWT_CODE_ID_SEQ FROM dual;
2151
2152
2153 INSERT INTO JL_ZZ_AP_SUP_AWT_CD
2154 (SUPP_AWT_CODE_ID,
2155 SUPP_AWT_TYPE_ID,
2156 TAX_ID,
2157 PRIMARY_TAX_FLAG,
2158 CREATED_BY,
2159 CREATION_DATE,
2160 LAST_UPDATED_BY,
2161 LAST_UPDATE_DATE,
2162 LAST_UPDATE_LOGIN,
2163 ORG_ID,
2164 EFFECTIVE_START_DATE,
2165 EFFECTIVE_END_DATE)
2166 VALUES
2167 (l_SUPP_AWT_CODE_ID_SEQ, --SUPP_AWT_CODE_ID
2168 l_SUPP_AWT_TYPE_ID_CD, --SUPP_AWT_TYPE_ID
2169 l_TAX_ID_DEF_ATC, -- default tax code id
2170 'Y', --PRIMARY_TAX_FLAG
2171 l_created_by, --CREATED_BY
2172 l_creation_DATE, --CREATION_DATE
2173 l_last_UPDATEd_by, --LAST_UPDATED_BY
2174 l_last_UPDATE_DATE, --LAST_UPDATE_DATE
2175 l_last_UPDATE_login, --LAST_UPDATE_LOGIN
2176 l_ORG_ID, --ORG_ID
2177 l_START_DATE_CURR_MAX, --EFFECTIVE_START_DATE
2178 NULL); --EFFECTIVE_END_DATE
2179
2180
2181 IF p_debug_log = 'Y' THEN
2182 FND_FILE.PUT_LINE( FND_FILE.LOG,'53 .INSERTED DATA IN JL_ZZ_AP_SUP_AWT_CD FOR SUPP_AWT_CODE_ID '|| L_SUPP_AWT_CODE_ID_SEQ);
2183 END IF;
2184
2185 EXCEPTION
2186 WHEN OTHERS THEN
2187
2188 IF p_debug_log = 'Y' THEN
2189 FND_FILE.PUT_LINE( FND_FILE.LOG,'53. INSERTED NOT DONE FOR JL_ZZ_AP_SUP_AWT_CD '|| SQLCODE ||' -ERROR- '|| SQLERRM);
2190 END IF;
2191 --If insertion fails, then will check already present in JL_ZZ_AP_SUP_AWT_CD
2192
2193 BEGIN
2194 SELECT 'Y', SAWTC.SUPP_AWT_CODE_ID INTO def_taxtype_code_check, l_SUPP_AWT_CODE_ID_CD
2195 FROM JL_ZZ_AP_SUP_AWT_CD SAWTC
2196 WHERE SAWTC.SUPP_AWT_TYPE_ID = l_SUPP_AWT_TYPE_ID_CD
2197 AND SAWTC.TAX_ID = l_TAX_ID_DEF_ATC;
2198
2199 EXCEPTION
2200 WHEN NO_DATA_FOUND THEN
2201 def_taxtype_code_check := 'N';
2202 END;
2203
2204 IF def_taxtype_code_check = 'Y' THEN
2205 UPDATE JL_ZZ_AP_SUP_AWT_CD SET EFFECTIVE_END_DATE = NULL
2206 WHERE SUPP_AWT_CODE_ID = l_SUPP_AWT_CODE_ID_CD;
2207
2208 FND_FILE.PUT_LINE( FND_FILE.LOG,'53 Records already present in JL_ZZ_AP_SUP_AWT_CD table TYPE ID'|| l_SUPP_AWT_TYPE_ID_CD);
2209 FND_FILE.PUT_LINE( FND_FILE.LOG,'53. l_TAX_ID_DEF_ATC'|| l_TAX_ID_DEF_ATC || 'L_EFFECTIVE_START_DATE' || L_START_DATE);
2210
2211 ELSE
2212 ----If insertion fails, then will do the UPDATE in awt_cd_all
2213 UPDATE JL_ZZ_AP_SUP_AWT_CD SET
2214 TAX_ID = l_TAX_ID_DEF_ATC, --- default tax code id
2215 PRIMARY_TAX_FLAG = 'Y',
2216 EFFECTIVE_START_DATE = l_START_DATE_CURR_MAX,
2217 EFFECTIVE_END_DATE = NULL
2218 WHERE SUPP_AWT_CODE_ID = l_SUPP_AWT_CODE_ID_CD;
2219
2220 IF p_debug_log = 'Y' THEN
2221 FND_FILE.PUT_LINE( FND_FILE.LOG,'54 .UPDATED DATA IN JL_ZZ_AP_SUP_AWT_CD FOR SUPP_AWT_CODE_ID WITH OLD TAX_ID for CODE ID'|| l_SUPP_AWT_CODE_ID_CD);
2222 END IF;
2223 END IF;
2224
2225 END;
2226
2227
2228 -- code to UPDATE/insert jl_zz_ap_inv_dis_wh_all
2229
2230 BEGIN
2231 -- To get the current month Supp_Awt_Code_id for the present tax_id
2232
2233 SELECT SAWTC.SUPP_AWT_CODE_ID
2234 INTO l_SUPP_AWT_CODE_ID_CD
2235 FROM JL_ZZ_AP_SUP_AWT_CD SAWTC,
2236 PO_VENDORS PV,
2237 JL_ZZ_AP_SUPP_AWT_TYPES SAWT,
2238 PER_ALL_PEOPLE_F PAPF
2239 WHERE SAWT.VENDOR_ID = PV.VENDOR_ID
2240 AND NVL(pv.employee_id, - 99) = papf.person_id (+)
2241 AND NVL(papf.EFFECTIVE_START_DATE, SYSDATE) <= SYSDATE
2242 AND NVL(papf.EFFECTIVE_END_DATE, SYSDATE) >= SYSDATE
2243 AND SAWT.AWT_TYPE_CODE = l_AWT_TAX_TYPE
2244 AND SAWT.SUPP_AWT_TYPE_ID = SAWTC.SUPP_AWT_TYPE_ID
2245 AND SAWTC.TAX_ID = l_TAX_ID_DEF_ATC -- to pick up the tax code WHERE tax id is def ATC
2246 AND SAWTC.primary_tax_flag = 'Y'
2247 --AND SAWT.WH_SUBJECT_FLAG = 'Y'
2248 AND sawtc.effective_end_DATE IS NULL
2249 --bug 8530918 AND NVL(papf.national_identifier, NVL(pv.individual_1099, pv.num_1099)) = TO_CHAR(l_TAXPAYER_ID);
2250 AND rtrim(
2251 substr(
2252 replace(
2253 nvl(papf.national_identifier,
2254 nvl(pv.individual_1099,pv.num_1099)
2255 ),
2256 '-'),
2257 1,10)
2258 ) ||
2259 substr(pv.global_attribute12,1,1) = TO_CHAR(l_TAXPAYER_ID);
2260
2261 SELECT JL_ZZ_AP_INV_DIS_WH_S.NEXTVAL INTO l_INV_DISTRIB_AWT_ID_INV FROM dual;
2262
2263 EXCEPTION
2264 WHEN OTHERS THEN
2265 IF p_debug_log = 'Y' THEN
2266 FND_FILE.PUT_LINE( FND_FILE.LOG,'54 UPDATE NOT DONE FOR JL_ZZ_AP_INV_DIS_WH - '|| SQLCODE ||' -ERROR- '|| SQLERRM);
2267 END IF;
2268 END;
2269
2270 OPEN CUR4(l_TAXPAYER_ID);
2271 LOOP
2272 FETCH CUR4 INTO INV_REC;
2273 EXIT WHEN CUR4%NOTFOUND;
2274
2275 ---code to insert data INTO jl_zz_ap_inv_dis_wh_all
2276
2277 BEGIN
2278
2279 AWT_CODE_INV_AVAIL_FLAG := 'N';
2280
2281 SELECT 'Y', INV_DISTRIB_AWT_ID INTO AWT_CODE_INV_AVAIL_FLAG, l_INV_DISTRIB_AWT_ID_DIS
2282 FROM JL_ZZ_AP_INV_DIS_WH
2283 WHERE INVOICE_ID = INV_REC.INVOICE_ID
2284 AND DISTRIBUTION_LINE_NUMBER = INV_REC.DISTRIBUTION_LINE_NUMBER
2285 AND INVOICE_DISTRIBUTION_ID = INV_REC.INVOICE_DISTRIBUTION_ID
2286 AND SUPP_AWT_CODE_ID = l_SUPP_AWT_CODE_ID_CD;
2287 /*(SELECT SUPP_AWT_CODE_ID FROM JL_ZZ_AP_SUP_AWT_CD SAWTC, JL_ZZ_AP_SUPP_AWT_TYPES SAWT
2288 WHERE SAWT.SUPP_AWT_TYPE_ID = SAWTC.SUPP_AWT_TYPE_ID
2289 AND SAWTC.TAX_ID IN (SELECT TAX_ID FROM AP_TAX_CODES WHERE name LIKE 'TURN_BSAS_GRP%')); */
2290
2291 EXCEPTION
2292 WHEN NO_DATA_FOUND THEN
2293 AWT_CODE_INV_AVAIL_FLAG := 'N';
2294 FND_FILE.put_line( FND_FILE.LOG,'54 A No earlier data in Inv Dist'|| SQLCODE ||' -ERROR- '|| SQLERRM);
2295
2296 WHEN OTHERS THEN
2297 AWT_CODE_INV_AVAIL_FLAG := 'N';
2298 FND_FILE.put_line( FND_FILE.LOG,'54 A Error in Inv Dist'|| SQLCODE ||' -ERROR- '|| SQLERRM);
2299 END;
2300
2301 IF AWT_CODE_INV_AVAIL_FLAG = 'N' THEN
2302
2303 IF p_debug_log = 'Y' THEN
2304 FND_FILE.put_line( FND_FILE.LOG,'54 B l_INV_DISTRIB_AWT_ID_INV'|| l_INV_DISTRIB_AWT_ID_INV);
2305 FND_FILE.put_line( FND_FILE.LOG,'54 B INV_REC.INVOICE_ID'|| INV_REC.INVOICE_ID);
2306 FND_FILE.put_line( FND_FILE.LOG,'54 B INV_REC.DISTRIBUTION_LINE_NUMBER'|| INV_REC.DISTRIBUTION_LINE_NUMBER);
2307 FND_FILE.put_line( FND_FILE.LOG,'54 B l_SUPP_AWT_CODE_ID_CD'|| l_SUPP_AWT_CODE_ID_CD);
2308 FND_FILE.put_line( FND_FILE.LOG,'54 B INVOICE_DISTRIBUTION_ID'|| INV_REC.INVOICE_DISTRIBUTION_ID);
2309 END IF;
2310
2311 BEGIN
2312
2313 INSERT INTO JL_ZZ_AP_INV_DIS_WH
2314 (INV_DISTRIB_AWT_ID,
2315 INVOICE_ID,
2316 DISTRIBUTION_LINE_NUMBER,
2317 SUPP_AWT_CODE_ID,
2318 CREATED_BY,
2319 CREATION_DATE,
2320 LAST_UPDATED_BY,
2321 LAST_UPDATE_DATE,
2322 LAST_UPDATE_LOGIN,
2323 ORG_ID,
2324 INVOICE_DISTRIBUTION_ID)
2325 VALUES
2326 (l_INV_DISTRIB_AWT_ID_INV, --INV_DISTRIB_AWT_ID
2327 INV_REC.INVOICE_ID, --INVOICE_ID
2328 INV_REC.DISTRIBUTION_LINE_NUMBER, --DISTRIBUTION_LINE_NUMBER
2329 l_SUPP_AWT_CODE_ID_CD, --Def SUPP_AWT_CODE_ID
2330 l_created_by, --CREATED_BY
2331 l_creation_DATE, --CREATION_DATE
2332 l_last_UPDATEd_by, --LAST_UPDATED_BY
2333 l_last_UPDATE_DATE, --LAST_UPDATE_DATE
2334 l_last_UPDATE_login, --LAST_UPDATE_LOGIN
2335 l_ORG_ID, --ORG_ID
2336 INV_REC.INVOICE_DISTRIBUTION_ID); --Invoice distribution ID -- R12 Changes
2337
2338
2339 SELECT JL_ZZ_AP_INV_DIS_WH_S.NEXTVAL INTO l_INV_DISTRIB_AWT_ID_INV FROM dual;
2340
2341 IF p_debug_log = 'Y' THEN
2342 FND_FILE.PUT_LINE( FND_FILE.LOG,'55 A. INSERTED RECORDS INTO JL_ZZ_AP_INV_DIS_WH_S '|| SQL%ROWCOUNT || 'RECORDS' || SQLCODE ||' -ERROR- '|| SQLERRM);
2343 END IF;
2344
2345 EXCEPTION
2346 WHEN OTHERS THEN
2347 IF p_debug_log = 'Y' THEN
2348 FND_FILE.put_line( FND_FILE.LOG,'55 A. Failed while Inserted records in JL_ZZ_AP_INV_DIS_WH '|| SQLCODE || 'ERROR' || SQLERRM);
2349 END IF;
2350 END;
2351
2352 ELSE
2353
2354 BEGIN
2355 IF p_debug_log = 'Y' THEN
2356 FND_FILE.put_line( FND_FILE.LOG,'55 B l_INV_DISTRIB_AWT_ID_INV'|| l_INV_DISTRIB_AWT_ID_INV);
2357 FND_FILE.put_line( FND_FILE.LOG,'55 B INV_REC.INVOICE_ID'|| INV_REC.INVOICE_ID);
2358 FND_FILE.put_line( FND_FILE.LOG,'55 B INV_REC.DISTRIBUTION_LINE_NUMBER'|| INV_REC.DISTRIBUTION_LINE_NUMBER);
2359 FND_FILE.put_line( FND_FILE.LOG,'55 B l_SUPP_AWT_CODE_ID_CD'|| l_SUPP_AWT_CODE_ID_CD);
2360 FND_FILE.put_line( FND_FILE.LOG,'55 B l_INV_DISTRIB_AWT_ID_DIS'|| l_INV_DISTRIB_AWT_ID_DIS);
2361 FND_FILE.put_line( FND_FILE.LOG,'55 B INVOICE_DISTRIBUTION_ID'|| INV_REC.INVOICE_DISTRIBUTION_ID);
2362 END IF;
2363
2364 /*SELECT INV_DISTRIB_AWT_ID INTO l_INV_DISTRIB_AWT_ID_DIS
2365 FROM JL_ZZ_AP_INV_DIS_WH
2366 WHERE INVOICE_ID = INV_REC.INVOICE_ID
2367 AND DISTRIBUTION_LINE_NUMBER = INV_REC.DISTRIBUTION_LINE_NUMBER
2368 AND INVOICE_DISTRIBUTION_ID = INV_REC.INVOICE_DISTRIBUTION_ID
2369 AND SUPP_AWT_CODE_ID IN
2370 (SELECT SUPP_AWT_CODE_ID FROM JL_ZZ_AP_SUP_AWT_CD SAWTC, JL_ZZ_AP_SUPP_AWT_TYPES SAWT
2371 WHERE SAWT.SUPP_AWT_TYPE_ID = SAWTC.SUPP_AWT_TYPE_ID
2372 AND SAWTC.primary_tax_flag = 'Y'
2373 AND SAWTC.TAX_ID IN (SELECT TAX_ID FROM AP_TAX_CODES WHERE name LIKE 'TURN_BSAS_GRP%')
2374 AND sawtc.effective_end_DATE IS NULL);*/
2375
2376 UPDATE JL_ZZ_AP_INV_DIS_WH SET SUPP_AWT_CODE_ID = l_SUPP_AWT_CODE_ID_CD
2377 WHERE INV_DISTRIB_AWT_ID = l_INV_DISTRIB_AWT_ID_DIS;
2378
2379 IF p_debug_log = 'Y' THEN
2380 FND_FILE.PUT_LINE( FND_FILE.LOG,'55 C.UPDATED DATA IN JL_ZZ_AP_INV_DIS_WH FOR SUPP_AWT_CODE_ID WITH CURRENT (DEF) TAX_ID for'|| SQL%ROWCOUNT || 'RECORD' || SQLCODE ||' -ERROR- '|| SQLERRM);
2381 FND_FILE.put_line( FND_FILE.LOG,'55 C l_INV_DISTRIB_AWT_ID_DIS:'|| l_INV_DISTRIB_AWT_ID_DIS || 'l_SUPP_AWT_CODE_ID_CD:' || l_SUPP_AWT_CODE_ID_CD);
2382 END IF;
2383
2384
2385 EXCEPTION
2386 WHEN OTHERS THEN
2387 IF p_debug_log = 'Y' THEN
2388 FND_FILE.put_line( FND_FILE.LOG,'56. Failed while updating records in JL_ZZ_AP_INV_DIS_WH '|| SQLCODE || 'ERROR' || SQLERRM);
2389 END IF;
2390 END;
2391
2392 END IF;
2393
2394 END LOOP;
2395 CLOSE CUR4;
2396
2397 IF p_debug_log = 'Y' THEN
2398 FND_FILE.PUT_LINE( FND_FILE.LOG,'56. UPDATED DATA IN JL_ZZ_AP_INV_DIS_WH FOR SUPP_AWT_CODE_ID ');
2399 END IF;
2400
2401
2402
2403 -- insert data in ALL table
2404
2405 SELECT * INTO ALL_REC FROM JL_AR_TURN_UPL
2406 WHERE START_DATE = l_START_DATE_SEC_MAX AND TAXPAYER_ID = l_TAXPAYER_ID;
2407
2408
2409 Insert_Row (ALL_REC.PUBLISH_DATE, -- inserting the old original publish DATE FROM government
2410 l_START_DATE_CURR_MAX, -- inserting the current max start and end DATEs
2411 l_END_DATE_CURR_MAX,
2412 ALL_REC.TAXPAYER_ID,
2413 ALL_REC.CONTRIBUTOR_TYPE_CODE,
2414 ALL_REC.NEW_CONTRIBUTOR_FLAG,
2415 ALL_REC.RATE_CHANGE_FLAG,
2416 ALL_REC.PERCEPTION_RATE,
2417 l_WHT_RATE_DEF_ATR, -- inserting the defualt tax rate
2418 ALL_REC.PERCEPTION_GROUP_NUM,
2419 l_WHT_GROUP_NUM_DEF_NO, -- inserting the defualt tax group
2420 'Y',
2421 'AP');
2422
2423 IF p_debug_log = 'Y' THEN
2424 FND_FILE.put_line( FND_FILE.LOG,'57 .Fetching Record Record after Taxpayer Id '|| l_TAXPAYER_ID);
2425 END IF;
2426
2427 END IF;
2428
2429 END IF; -- AP validation ends here
2430
2431 <<L4>>
2432
2433 NULL;
2434
2435
2436 --Reinitialising the local variables:
2437 --------------------------------------
2438 l_START_DATE_SEC_MAX := NULL;
2439 l_taxpayer := 'N';
2440 l_taxpayer_ar := 'N';
2441 AWT_CODE_INV_AVAIL_FLAG := 'N';
2442
2443 COMMIT;
2444
2445 END LOOP;
2446 CLOSE C4;
2447
2448
2449
2450 EXCEPTION
2451 WHEN OTHERS THEN
2452 IF p_debug_log = 'Y' THEN
2453 FND_FILE.PUT_LINE( FND_FILE.LOG,'58 .AN ERROR WAS ENCOUNTERED IN VALIDATION_AWT_SETUP - '|| SQLCODE ||' -ERROR- '|| SQLERRM);
2454 END IF;
2455 UPDATE JGZZ_AR_TAX_GLOBAL_TMP SET JG_INFO_V1 = 'JLZZ_RECORD_FAILED_AWT_SETUP'
2456 WHERE JG_INFO_N1 = l_TAXPAYER_ID AND JG_INFO_D1 = l_START_DATE AND JG_INFO_D2 = l_END_DATE;
2457
2458
2459 END VALIDATE_AWT_SETUP;
2460
2461
2462
2463
2464 FUNCTION beforeReport
2465 RETURN BOOLEAN
2466 IS
2467
2468 BEGIN
2469
2470
2471 P_REV_TEMP_DATA := UPPER(P_REV_TEMP_DATA);
2472 P_VALIDATION_DATA := UPPER(P_VALIDATION_DATA);
2473 P_FINALIZE_DATA := UPPER(P_FINALIZE_DATA);
2474 P_START_DATE := to_DATE(P_START_DATE);
2475 P_END_DATE := to_DATE(P_END_DATE);
2476
2477 IF p_debug_log = 'Y' THEN
2478 FND_FILE.put_line( FND_FILE.LOG,'Starting PKG JL_AR_APPLICABLE_TAXES.beforeReport');
2479 FND_FILE.put_line( FND_FILE.LOG,'P_REV_TEMP_DATA :'|| P_REV_TEMP_DATA);
2480 FND_FILE.put_line( FND_FILE.LOG,'P_VALIDATION_DATA :'|| P_VALIDATION_DATA);
2481 FND_FILE.put_line( FND_FILE.LOG,'P_FINALIZE_DATA :'|| P_FINALIZE_DATA);
2482 FND_FILE.put_line( FND_FILE.LOG,'P_START_DATE :'|| P_START_DATE);
2483 FND_FILE.put_line( FND_FILE.LOG,'P_END_DATE :'|| P_END_DATE);
2484 END IF;
2485 FND_FILE.put_line( FND_FILE.LOG,'ORG_ID :'|| l_org_id);
2486 IF (P_REV_TEMP_DATA = 'Y') AND (P_VALIDATION_DATA = 'N') AND (P_FINALIZE_DATA = 'N') THEN
2487
2488 table_name := 'JL_AR_TURN_UPL_T'; -- will goto xml file and print the Temp Table Data output directly
2489
2490
2491 ELSIF (P_REV_TEMP_DATA = 'N') AND (P_VALIDATION_DATA = 'N') AND (P_FINALIZE_DATA = 'N') THEN
2492
2493 table_name := 'JL_AR_TURN_UPL'; -- will goto xml file and print the FINAL ALL Table Data output directly
2494
2495
2496 ELSIF (P_REV_TEMP_DATA = 'Y') AND (P_VALIDATION_DATA = 'Y') AND (P_FINALIZE_DATA = 'N') THEN
2497
2498 table_name := 'JL_AR_TURN_UPL'; -- will do validation for the govt flat file data and shows the valid and invalid records
2499
2500
2501 IF p_debug_log = 'Y' THEN
2502 FND_FILE.put_line( FND_FILE.LOG,'Starting the FINAL_VALIDATION procedure');
2503 END IF;
2504
2505 DELETE JGZZ_AR_TAX_GLOBAL_TMP; -- Truncating previous records, if any, in JGZZ_AR_TAX_GLOBAL_TMP table
2506 FINAL_VALIDATION; -- will goto FINAL_VALIDATION package and validate
2507
2508 IF p_debug_log = 'Y' THEN
2509 FND_FILE.put_line( FND_FILE.LOG,'After the FINAL_VALIDATION procedure');
2510 END IF;
2511
2512 ELSIF (P_FINALIZE_DATA = 'Y') AND (P_REV_TEMP_DATA = 'N') AND (P_VALIDATION_DATA = 'N') THEN
2513
2514 table_name := 'JL_AR_TURN_UPL'; -- will do final validation for the govt flat file data and defaulting will happens for missing taxpayerid
2515
2516 IF p_debug_log = 'Y' THEN
2517 FND_FILE.put_line( FND_FILE.LOG,'Starting the VALIDATE_AWT_SETUP procedure');
2518 END IF;
2519
2520 VALIDATE_AWT_SETUP; -- will goto VALIDATE_AWT_SETUP package and valiDATE and then print xml data
2521
2522 IF p_debug_log = 'Y' THEN
2523 FND_FILE.put_line( FND_FILE.LOG,'After the VALIDATE_AWT_SETUP procedure');
2524 END IF;
2525
2526 ELSE
2527
2528 table_name := 'JL_AR_TURN_UPL';
2529
2530 IF p_debug_log = 'Y' THEN
2531 FND_FILE.put_line( FND_FILE.LOG,' ');
2532 FND_FILE.put_line( FND_FILE.LOG,' ');
2533 FND_FILE.put_line( FND_FILE.LOG,'--------------------------------------------------------------------------------------------------------');
2534 FND_FILE.put_line( FND_FILE.LOG,' ');
2535 FND_FILE.put_line( FND_FILE.LOG,' ');
2536 FND_FILE.put_line( FND_FILE.LOG,' -------------- E R R O R : P A R A M E T E R W R O N G L Y S E L E C T E D -------------');
2537 FND_FILE.put_line( FND_FILE.LOG,' ');
2538 FND_FILE.put_line( FND_FILE.LOG,' ');
2539 FND_FILE.put_line( FND_FILE.LOG,' ');
2540 FND_FILE.put_line( FND_FILE.LOG,'--------------- PLEASE MAKE SURE TO RUN THE ARGENTINA AWT REPORT WITH VALID PARAMETERS -------------');
2541 FND_FILE.put_line( FND_FILE.LOG,' ');
2542 FND_FILE.put_line( FND_FILE.LOG,' ');
2543 FND_FILE.put_line( FND_FILE.LOG,'------------------------------- VALID PARAMETER SELECTION CRITERIA ---------------------------------');
2544 FND_FILE.put_line( FND_FILE.LOG,' ');
2545 FND_FILE.put_line( FND_FILE.LOG,' ');
2546 FND_FILE.put_line( FND_FILE.LOG,' To Review the Temporary Upload Data (without validation), Enter the Review Temporary Data as YES, Validate Temporary Data as NO, Finalize Data as NO');
2547 FND_FILE.put_line( FND_FILE.LOG,' To Validate the Temporary Upload Data, Enter the Review Temporary Data as YES, Validate Temporary Data as YES, Finalize Data as NO');
2548 FND_FILE.put_line( FND_FILE.LOG,' To Finalize the Temporary Upload Data, Enter the Review Temporary Data as NO, Validate Temporary Data as NO, Finalize Data as YES');
2549 FND_FILE.put_line( FND_FILE.LOG,' To view only the Valid Data (After validation), Enter the Review Temporary Data as NO, Validate Temporary Data as NO, Finalize Data as NO and select the Responsibility and Dates Appropriately');
2550 FND_FILE.put_line( FND_FILE.LOG,' ');
2551 FND_FILE.put_line( FND_FILE.LOG,' ');
2552 FND_FILE.put_line( FND_FILE.LOG,'----------------------------------------------------------------------------------------------------------');
2553 FND_FILE.put_line( FND_FILE.LOG,' ');
2554 FND_FILE.put_line( FND_FILE.LOG,' ');
2555 END IF;
2556
2557 END IF;
2558
2559
2560 IF p_debug_log = 'Y' THEN
2561 FND_FILE.put_line( FND_FILE.LOG,'Closing the PKG JL_AR_APPLICABLE_TAXES.beforeReport');
2562 END IF;
2563
2564 IF (P_START_DATE IS NULL) THEN
2565 SELECT MAX(start_DATE) INTO P_START_DATE FROM JL_AR_TURN_UPL;
2566 END IF;
2567
2568 IF (P_END_DATE IS NULL) THEN
2569 SELECT MAX(end_DATE) INTO P_END_DATE FROM JL_AR_TURN_UPL;
2570 END IF;
2571 IF p_debug_log = 'Y' THEN
2572 FND_FILE.put_line( FND_FILE.LOG,'Before Report P_START_DATE :'|| P_START_DATE ||'P_END_DATE :'|| P_END_DATE);
2573 FND_FILE.put_line( FND_FILE.LOG,' ');
2574 FND_FILE.put_line( FND_FILE.LOG,' ');
2575 END IF;
2576
2577 --DELETE JGZZ_AR_TAX_GLOBAL_TMP; -- Deleting previous records, if any in JGZZ_AR_TAX_GLOBAL_TMP table
2578 --FINAL_VALIDATION;
2579 --VALIDATE_AWT_SETUP;
2580
2581 RETURN TRUE;
2582 END beforeReport;
2583
2584
2585 END JL_AR_APPLICABLE_TAXES;
2586