[Home] [Help]
PACKAGE BODY: APPS.JL_ZZ_AP_MONOTRIB_AWT_PKG
Source
1 PACKAGE BODY JL_ZZ_AP_MONOTRIB_AWT_PKG AS
2 /* $Header: jlarmtbb.pls 120.10.12020000.2 2012/07/12 17:14:57 abuissa ship $ */
3
4 FUNCTION BeforeReport
5 RETURN BOOLEAN
6
7 IS
8
9
10 ---------------------------------------------------------------------------------
11 ------ Main Supplier Cursor to Fetch all Suppliers with Status as Monotributistas
12 ---------------------------------------------------------------------------------
13
14 CURSOR MonoTrib_Suppliers (p_supplier_id NUMBER) IS
15 SELECT vendor_name supplier_name,
16 vendor_id supplier_id,
17 NVL(individual_1099,num_1099)||'-'||global_attribute12 taxpayer_id,
18 global_attribute8 simplif_regime_cont_type
19 FROM ap_suppliers
20 WHERE global_attribute1 = '06'
21 AND global_attribute8 IN ('GOODS','SERVICES')
22 AND (vendor_id = NVL(p_supplier_id,vendor_id));
23
24
25
26 -------------------------------------------------------------------------------------
27 ------- Main Invoice Cursor to fetch Invoices pertaining to Monotributistas Suppliers
28 -------------------------------------------------------------------------------------
29
30 CURSOR monotrib_supp_inv(p_supplier_id NUMBER, p_to_date DATE, p_from_date DATE) IS
31 SELECT ai.invoice_id invoice_id
32 ,ai.invoice_num invoice_num
33 ,ai.invoice_date invoice_date
34 ,ai.payment_status_flag invoice_status
35 ,ai.global_attribute13 dgi_type
36 ,SUM(DECODE(ai.invoice_currency_code, 'ARS', aid.amount, aid.base_amount)) invoice_amt
37 FROM ap_invoices ai,
38 ap_invoice_lines ail,
39 ap_invoice_distributions aid
40 WHERE ai.vendor_id = p_supplier_id
41 AND ai.invoice_id = aid.invoice_id
42 AND ai.invoice_id = ail.invoice_id
43 AND ail.line_number = aid.invoice_line_number
44 AND ai.invoice_date BETWEEN p_from_date AND p_to_date
45 AND ail.line_type_lookup_code NOT IN ('AWT','TAX')
46 AND ai.invoice_type_lookup_code IN ('STANDARD','PREPAYMENT','CREDIT')
47 AND ai.payment_status_flag IN ('N','P','Y')
48 AND ai.cancelled_date IS NULL
49 AND ai.legal_entity_id = p_legal_entity_id
50 GROUP BY ai.invoice_id, ai.invoice_date, ai.invoice_num, ai.payment_status_flag, ai.global_attribute13
51 ORDER BY ai.invoice_date,ai.invoice_id;
52
53 type supp_inv_type is table of monotrib_supp_inv%rowtype;
54 c_supp_inv supp_inv_type;
55 type supp_type is table of MonoTrib_Suppliers%rowtype;
56 c_supp_rec supp_type;
57
58 ----------------------------------
59 ----- Local Variables Definition
60 ----------------------------------
61
62 v_from_date DATE;
63 v_to_date DATE;
64 v_flag NUMBER;
65 v_running_amount NUMBER;
66 v_threshold_met VARCHAR2(1);
67 Applicability_Chngd_flag VARCHAR2(1);
68 v_supp_monotrib_status VARCHAR2(1);
69 v_supp_update_status VARCHAR2(1);
70 v_inv_amt NUMBER;
71 v_inv_tax_amt NUMBER;
72 v_threshold_amt NUMBER;
73 v_threshold_chk NUMBER;
74 v_update_supp_appl VARCHAR2(1);
75 v_inv_amt_without_tax NUMBER;
76 BEGIN
77
78 p_debug_log := NVL(FND_PROFILE.value('AFLOG_ENABLED'), 'N');
79
80 ---------------------------------------------------------------------------------------------------
81 ----- Deriving the Threshold values for Goods and Services Suppliers as fixed by Simplified Regime
82 ---------------------------------------------------------------------------------------------------
83
84 BEGIN
85
86
87 SELECT nvl(threshold_amt,0) INTO p_goods_supp_thld
88 FROM jl_ar_ap_mtbt_thresholds
89 WHERE contributor_type = 'GOODS'
90 AND p_report_date BETWEEN start_date AND nvl(end_date,add_months(sysdate,12*50));
91
92 EXCEPTION
93 WHEN OTHERS THEN
94 NULL;
95 END;
96
97 BEGIN
98
99 SELECT nvl(threshold_amt,0) INTO p_service_supp_thld
100 FROM JL_AR_AP_MTBT_THRESHOLDS
101 WHERE contributor_type = 'SERVICES'
102 AND p_report_date BETWEEN start_date AND nvl(end_date,add_months(sysdate,12*50));
103
104 EXCEPTION
105 WHEN OTHERS THEN
106 NULL;
107 END;
108
109
110 -- Cursor OPEN for Monotributa Suppliers
111 --
112 --FOR c_supp IN MonoTrib_Suppliers(p_supplier_id)LOOP
113 --
114 OPEN MonoTrib_Suppliers(p_supplier_id);
115 FETCH MonoTrib_Suppliers BULK COLLECT INTO c_supp_rec;
116 CLOSE MonoTrib_Suppliers;
117
118 FOR i_supp IN 1..c_supp_rec.COUNT LOOP ---supplier loop start
119
120 v_running_amount := 0;
121 v_threshold_met := 'N';
122 v_update_supp_appl := 'N';
123
124 IF p_debug_log = 'Y' THEN
125 FND_FILE.put_line( FND_FILE.LOG, 'INSIDE MONOTRIBUTA SUPPLIER LOOP : SUPPLIER NAME - ' ||c_supp_rec(i_supp).SUPPLIER_NAME);
126 END IF;
127
128
129 IF (c_supp_rec(i_supp).simplif_regime_cont_type = 'GOODS') THEN
130 IF (p_goods_supp_thld = 0) THEN
131 FND_FILE.put_line( FND_FILE.LOG, 'ERROR : THRESHOLD value for "GOODS" Contributor type is not defined for Date:'||P_REPORT_DATE);
132 v_threshold_chk := 0;
133 ELSE
134 v_threshold_amt := p_goods_supp_thld;
135 END IF;
136 ELSIF (c_supp_rec(i_supp).simplif_regime_cont_type = 'SERVICES') THEN
137 IF (p_service_supp_thld = 0) THEN
138 FND_FILE.put_line( FND_FILE.LOG, 'ERROR : THRESHOLD value for "SERVICES" Contributor type is not defined for Date:'||P_REPORT_DATE);
139 v_threshold_chk := 0;
140 ELSE
141 v_threshold_amt := p_service_supp_thld;
142 END IF;
143 END IF;
144
145 IF (v_threshold_chk =0) THEN
146 ROLLBACK;
147 RETURN (FALSE);
148 END IF;
149
150
151
152 ---------------------------------------------------------------------------------------------
153 -- Deriving boder To and From Dates based on Reporting Date from Report Parameter
154 -- The Logic will derive closest Invoice Date to REPORTING_DATE value as To_Date and
155 -- From_Date will be the 11 months past date
156 ---------------------------------------------------------------------------------------------
157
158 BEGIN
159 SELECT max(invoice_date), trunc(add_months(max(invoice_date), -11),'MM')
160 INTO v_to_date, v_from_date
161 FROM AP_INVOICES
162 WHERE vendor_id = c_supp_rec(i_supp).supplier_id
163 AND cancelled_date IS NULL --BUG 9792829
164 AND invoice_date <= P_REPORT_DATE;
165
166 EXCEPTION
167 WHEN OTHERS THEN
168 v_to_date := NULL;
169 v_from_date := NULL;
170
171 IF (p_debug_log = 'Y') THEN
172 FND_FILE.put_line( FND_FILE.LOG,'AN ERROR IS ENCOUNTERED WHILE DERIVING TO_DATE and FROM_DATE '|| SQLCODE || 'ERROR ' || SQLERRM);
173 END IF;
174 END;
175
176 -----------------------------------------------------------------------------------------------------------
177 -- Logic to check if Simplified Regime Withholding Tax rate is already applicable to Monotributa Supplier
178 -----------------------------------------------------------------------------------------------------------
179 BEGIN
180 SELECT 1
181 INTO v_flag
182 FROM JL_ZZ_AP_AWT_TYPES awt,
183 JL_ZZ_AP_SUPP_AWT_TYPES swt
184 WHERE swt.vendor_id = c_supp_rec(i_supp).SUPPLIER_ID
185 AND swt.awt_type_code = awt.awt_type_code
186 AND awt.simplified_regime_flag = 'Y'
187 AND swt.wh_subject_flag = 'Y'
188 AND ROWNUM = 1;
189
190 EXCEPTION
191 WHEN OTHERS THEN
192 v_flag := 0;
193 NULL;
194 END;
195
196 ---- If Supplier is Monotributa and is not already subjected to Special Withholding tax
197 --
198 IF v_flag = 0 THEN ---
199
200 OPEN Monotrib_Supp_Inv(c_supp_rec(i_supp).SUPPLIER_ID, v_to_date, v_from_date);
201 LOOP --LOOP for LIMIT
202 FETCH monotrib_supp_inv BULK COLLECT INTO c_supp_inv LIMIT 100;
203
204
205 FOR inv_rec IN 1..c_supp_inv.COUNT
206 LOOP --for loop of inv_rec
207 IF p_debug_log = 'Y' THEN
208 FND_FILE.put_line( FND_FILE.LOG, 'INSIDE INVOICE LOOP : INVOICE ID '|| c_supp_inv(inv_rec).INVOICE_ID);
209 END IF;
210
211
212 ---- Logic to check if Running sum of Monotributa Supplier Invoices reached Threshold or not
213 v_running_amount := v_running_amount + c_supp_inv(inv_rec).INVOICE_AMT;
214 IF v_running_amount >= v_threshold_amt THEN
215 v_threshold_met := 'Y';
216 END IF;
217 IF p_debug_log = 'Y' THEN
218 FND_FILE.put_line( FND_FILE.LOG, 'Invoice_amount without awt and tax : '|| c_supp_inv(inv_rec).INVOICE_AMT);
219 FND_FILE.put_line( FND_FILE.LOG, 'Running sum : '|| v_running_amount);
220 END IF;
221 ---- Logic to derive Invoice Amount and Invoice amount excluding tax
222 BEGIN
223 SELECT DECODE(ai.invoice_currency_code,'ARS',ai.invoice_amount,ai.base_amount),
224 SUM(DECODE(ail.line_type_lookup_code,'TAX',(DECODE(ai.invoice_currency_code,'ARS', AID.amount, AID.base_amount)),0))
225 INTO v_inv_amt,
226 v_inv_tax_amt
227 FROM ap_invoices ai,
228 ap_invoice_lines ail,
229 ap_invoice_distributions aid
230 WHERE ai.invoice_id = AID.invoice_id
231 AND ai.invoice_id = ail.invoice_id
232 AND ail.line_number = aid.invoice_line_number
233 AND ai.invoice_id = c_supp_inv(inv_rec).invoice_id
234 GROUP BY DECODE(ai.invoice_currency_code,'ARS',ai.invoice_amount,ai.base_amount);
235
236 EXCEPTION
237 WHEN OTHERS THEN
238 FND_FILE.put_line( FND_FILE.LOG,'AN ERROR IS ENCOUNTERED WHILE FETCHING INVOICE AMT '|| SQLCODE || 'ERROR ' || SQLERRM);
239 END;
240 v_inv_amt_without_tax := v_inv_amt - v_inv_tax_amt;
241 /*---------------------------------------------------------------------------------------------------------
242 ----Logic for inserting Date into TEMP table
243 ----------------------------------------------------------------------------------------------------------
244 ----Report Mode = 'Verify(01)'
245 ------------If Threshold is met then Threshold Met = 'Y',
246 -------------------------------------Supplier Current Status for Sp.Witholding Tax = 'N',
247 -------------------------------------Supplier Updated in this cycle = 'N'
248 ------------If Threshold is met then Threshold Met = 'N',
249 -------------------------------------Supplier Current Status for Sp.Witholding Tax = 'N',
250 -------------------------------------Supplier Updated in this cycle = 'N'
251 ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
252 ----Report Mode = 'Update(02)'
253 ------------If Threshold is met then Threshold Met = 'Y',
254 -------------------------------------Supplier Current Status for Sp.Witholding Tax = 'Y',
255 -------------------------------------Supplier Updated in this cycle = 'Y'
256 ------------If Threshold is met then Threshold Met = 'N',
257 -------------------------------------Supplier Current Status for Sp.Witholding Tax = 'N',
258 -------------------------------------Supplier Updated in this cycle = 'N'
259 ------------------------------------------------------------------------------------------------------------*/
260
261 IF P_REPORT_MODE = '01' THEN
262 IF v_threshold_Met = 'Y' THEN
263 v_supp_monotrib_status := 'N';
264 v_supp_update_status := 'N';
265 Insert_temp_data( c_supp_rec(i_supp).SUPPLIER_NAME,
266 c_supp_rec(i_supp).SUPPLIER_ID,
267 c_supp_rec(i_supp).TAXPAYER_ID,
268 c_supp_rec(i_supp).SIMPLIF_REGIME_CONT_TYPE,
269 v_supp_monotrib_status,
270 v_supp_update_status,
271 v_threshold_amt,
272 c_supp_inv(inv_rec).INVOICE_ID,
273 c_supp_inv(inv_rec).INVOICE_NUM,
274 c_supp_inv(inv_rec).INVOICE_DATE,
275 c_supp_inv(inv_rec).INVOICE_STATUS,
276 c_supp_inv(inv_rec).DGI_TYPE,
277 v_inv_amt,
278 v_inv_amt_without_tax,
279 v_threshold_Met );
280 ELSIF v_threshold_Met = 'N' THEN
281 v_supp_monotrib_status := 'N';
282 v_supp_update_status := 'N';
283 Insert_temp_data( c_supp_rec(i_supp).SUPPLIER_NAME,
284 c_supp_rec(i_supp).SUPPLIER_ID,
285 c_supp_rec(i_supp).TAXPAYER_ID,
286 c_supp_rec(i_supp).SIMPLIF_REGIME_CONT_TYPE,
287 v_supp_monotrib_status,
288 v_supp_update_status,
289 v_threshold_amt,
290 c_supp_inv(inv_rec).INVOICE_ID,
291 c_supp_inv(inv_rec).INVOICE_NUM,
292 c_supp_inv(inv_rec).INVOICE_DATE,
293 c_supp_inv(inv_rec).INVOICE_STATUS,
294 c_supp_inv(inv_rec).DGI_TYPE,
295 v_inv_amt,
296 v_inv_amt_without_tax,
297 v_threshold_Met );
298 END IF;
299
300 ELSIF P_REPORT_MODE = '02' THEN
301 IF v_threshold_Met = 'Y' THEN
302 -----------------------------------------------------------------------------------------------------------------
303 ---- Call to Routine for Updating Supplier Special Witholding Tax applicability, once they have met the threshold
304 -----------------------------------------------------------------------------------------------------------------
305 IF (v_update_supp_appl = 'N') THEN
306 --call of procedure to update the isupplier's applicability
307 Update_Supplier_Applicability (c_supp_rec(i_supp).SUPPLIER_ID, Applicability_Chngd_flag);
308 v_update_supp_appl := 'Y';
309 END IF;
310
311 ---- Proceed if the Applicability is Successfully updated
312 IF Applicability_Chngd_flag = 'Y' THEN
313 IF c_supp_inv(inv_rec).invoice_status = 'N' THEN
314 --call of procedure to update the invoice withholding distribution
315 Update_Monotrib_Inv_Distrib_Wh(c_supp_inv(inv_rec).invoice_id, c_supp_rec(i_supp).SUPPLIER_ID);
316 IF p_debug_log = 'Y' THEN
317 FND_FILE.put_line( FND_FILE.LOG, 'INVOICE APPLICABILITY UPDATED SUCCESSFULLY FOR INVOICE ID : '|| c_supp_inv(inv_rec).invoice_id);
318 FND_FILE.put_line( FND_FILE.LOG, '*******************************');
319 END IF;
320 v_supp_monotrib_status := 'Y';
321 v_supp_update_status := 'Y';
322 Insert_temp_data( c_supp_rec(i_supp).SUPPLIER_NAME,
323 c_supp_rec(i_supp).SUPPLIER_ID,
324 c_supp_rec(i_supp).TAXPAYER_ID,
325 c_supp_rec(i_supp).SIMPLIF_REGIME_CONT_TYPE,
326 v_supp_monotrib_status,
327 v_supp_update_status,
328 v_threshold_amt,
329 c_supp_inv(inv_rec).INVOICE_ID,
330 c_supp_inv(inv_rec).INVOICE_NUM,
331 c_supp_inv(inv_rec).INVOICE_DATE,
332 c_supp_inv(inv_rec).INVOICE_STATUS,
333 c_supp_inv(inv_rec).DGI_TYPE,
334 v_inv_amt,
335 v_inv_amt_without_tax,
336 v_threshold_Met );
337 ELSE --BUG 9792829
338 IF p_debug_log = 'Y' THEN
339 FND_FILE.put_line( FND_FILE.LOG, 'SUPPLIER APPLICABILITY UPDATED: Scenario where no unpaid Invoice Existed ');
340 FND_FILE.put_line( FND_FILE.LOG, '*******************************');
341 END IF;
342 v_supp_monotrib_status := 'Y';
343 v_supp_update_status := 'Y';
344 Insert_temp_data( c_supp_rec(i_supp).SUPPLIER_NAME,
345 c_supp_rec(i_supp).SUPPLIER_ID,
346 c_supp_rec(i_supp).TAXPAYER_ID,
347 c_supp_rec(i_supp).SIMPLIF_REGIME_CONT_TYPE,
348 v_supp_monotrib_status,
349 v_supp_update_status,
350 NULL,
351 NULL,
352 NULL,
353 NULL,
354 NULL,
355 NULL,
356 NULL,
357 NULL,
358 NULL );
359 END IF;
360 END IF;
361 ELSIF v_threshold_Met = 'N' THEN
362 v_supp_monotrib_status := 'N';
363 v_supp_update_status := 'N';
364 Insert_temp_data( c_supp_rec(i_supp).SUPPLIER_NAME,
365 c_supp_rec(i_supp).SUPPLIER_ID,
366 c_supp_rec(i_supp).TAXPAYER_ID,
367 c_supp_rec(i_supp).SIMPLIF_REGIME_CONT_TYPE,
368 v_supp_monotrib_status,
369 v_supp_update_status,
370 v_threshold_amt,
371 c_supp_inv(inv_rec).INVOICE_ID,
372 c_supp_inv(inv_rec).INVOICE_NUM,
373 c_supp_inv(inv_rec).INVOICE_DATE,
374 c_supp_inv(inv_rec).INVOICE_STATUS,
375 c_supp_inv(inv_rec).DGI_TYPE,
376 v_inv_amt,
377 v_inv_amt_without_tax,
378 v_threshold_Met );
379 END IF; --end of v_threshold_Met check
380 END IF; -- end of P_REPORT_MODE check
381
382
383 END LOOP; ----LOOP for LIMIT
384 EXIT WHEN monotrib_supp_inv%NOTFOUND;
385
386 END LOOP; -- end of invoice cursor "Monotrib_Supp_Inv"
387 CLOSE monotrib_supp_inv;
388 ---- If the Supplier is already subjected to Special Withholding Tax
389 ELSE -- if v_flag <> 0
390 FND_FILE.put_line( FND_FILE.LOG,'Supplier is already subjected to Simplified Regime Special Tax');
391 v_supp_monotrib_status := 'Y';
392 Insert_temp_data(c_supp_rec(i_supp).SUPPLIER_NAME,
393 c_supp_rec(i_supp).SUPPLIER_ID,
394 c_supp_rec(i_supp).TAXPAYER_ID,
395 c_supp_rec(i_supp).SIMPLIF_REGIME_CONT_TYPE,
396 v_supp_monotrib_status,
397 'N',
398 NULL,
399 NULL,
400 NULL,
401 NULL,
402 NULL,
403 NULL,
404 NULL,
405 NULL,
406 NULL );
407
408 END IF; -- end of v_flag check
409
410 END LOOP; --supplier loop end
411 RETURN (TRUE);
412 FND_FILE.put_line( FND_FILE.LOG,'Returning TRUE');
413 EXCEPTION
414 WHEN OTHERS THEN
415 FND_FILE.put_line( FND_FILE.LOG,'AN ERROR IS ENCOUNTERED IN BEFORE REPORT FUNCTION '|| SQLCODE || 'ERROR ' || SQLERRM);
416 RETURN (FALSE);
417 END BeforeReport;
418
419 -------------------------------------------------------------
420 ----- Routine to Insert date into Temp Table
421 -------------------------------------------------------------
422
423 PROCEDURE Insert_temp_data(
424 P_SUPPLIER_NAME IN VARCHAR2,
425 P_SUPPLIER_ID IN NUMBER,
426 P_TAXPAYER_ID IN VARCHAR2,
427 P_SIMPLIF_REGIME_CONT_TYPE IN VARCHAR2,
428 P_supp_monotrib_status IN VARCHAR2,
429 P_supp_update_status IN VARCHAR2,
430 P_threshold_amt IN NUMBER,
431 P_INVOICE_ID IN NUMBER,
432 P_INVOICE_NUM IN VARCHAR2,
433 P_INVOICE_DATE IN DATE,
434 P_INVOICE_STATUS IN VARCHAR2,
435 P_DGI_TYPE IN VARCHAR2,
436 P_INV_AMOUNT IN NUMBER,
437 P_INV_AMT_WOUT_TAX IN NUMBER,
438 P_threshold_Met IN VARCHAR2
439 )
440 IS
441
442 BEGIN
443 INSERT
444 INTO JL_ZZ_INFO_T
445 (/*Supplier details*/
446 JL_INFO_V1
447 ,JL_INFO_N1
448 ,JL_INFO_V2
449 ,JL_INFO_V3
450 ,JL_INFO_V4
451 ,JL_INFO_V5
452 ,JL_INFO_N6
453 /*Invoice Details*/
454 ,JL_INFO_N3
455 ,JL_INFO_V6
456 ,JL_INFO_D1
457 ,JL_INFO_V7
458 ,JL_INFO_V8
459 ,JL_INFO_N4
460 ,JL_INFO_N5
461 ,JL_INFO_V9)
462 VALUES (/*Supplier details*/
463 P_SUPPLIER_NAME,
464 P_SUPPLIER_ID,
465 P_TAXPAYER_ID,
466 P_SIMPLIF_REGIME_CONT_TYPE,
467 P_supp_monotrib_status,
468 P_supp_update_status,
469 P_threshold_amt,
470 /*Invoice Details*/
471 P_INVOICE_ID,
472 P_INVOICE_NUM,
473 P_INVOICE_DATE,
474 P_INVOICE_STATUS,
475 P_DGI_TYPE,
476 P_INV_AMOUNT,
477 P_INV_AMT_WOUT_TAX,
478 P_threshold_Met);
479
480 EXCEPTION
481 WHEN OTHERS THEN
482 FND_FILE.put_line( FND_FILE.LOG,'AN ERROR IS ENCOUNTERED WHILE INSERTING INTO TEMP TABLE '|| SQLCODE || 'ERROR ' || SQLERRM);
483 RAISE;
484 END Insert_temp_data;
485
486 -----------------------------------------------------------------------------------------------------------------
487 ---- Call to Routine for Updating Supplier Special Witholding Tax applicability, once they have met the threshold
488 -----------------------------------------------------------------------------------------------------------------
489
490 PROCEDURE Update_Supplier_Applicability( P_Supplier_Id IN po_vendors.vendor_id%Type,
491 Applicability_Chngd_flag OUT NOCOPY VARCHAR2)
492 IS
493 CURSOR awt_types IS
494 SELECT awt_type_code, description, supplier_exempt_level, multilat_contrib_flag
495 FROM JL_ZZ_AP_AWT_TYPES
496 WHERE Simplified_Regime_Flag = 'Y'
497 AND nvl(start_date_active, sysdate) <= sysdate
498 AND nvl(end_date_active, sysdate) >= sysdate;
499
500 v_tax_id NUMBER(15);
501 v_tax_name VARCHAR2(15);
502 v_last_update_by NUMBER;
503 v_last_update_login NUMBER;
504 v_Org_Id NUMBER;
505 v_supp_awt_type_id NUMBER;
506 v_supp_awt_code_id NUMBER;
507 v_flag NUMBER;
508 v_temp1 NUMBER;
509 v_temp2 NUMBER;
510
511 BEGIN
512
513 -- Get the information of WHO Columns from FND_GLOBAL
514 v_last_update_by := FND_GLOBAL.User_ID;
515 v_last_update_login := FND_GLOBAL.Login_Id;
516 fnd_profile.get('ORG_ID',v_Org_Id);
517 Applicability_Chngd_flag := 'N';
518
519 IF p_debug_log = 'Y' THEN
520 FND_FILE.put_line( FND_FILE.LOG, 'INSIDE SUPPLIER APPLICABILITY UPDATE ROUTINE FOR SUPPLIER ID : '|| P_Supplier_Id);
521 END IF;
522
523 FOR c_rec IN awt_types LOOP
524 SELECT tax_id, name
525 INTO v_tax_id, v_tax_name
526 FROM AP_TAX_CODES
527 WHERE global_attribute4 = c_rec.awt_type_code
528 AND inactive_date IS NULL
529 AND creation_date >= ( SELECT max(creation_date)
530 FROM AP_TAX_CODES --bug 14274573
531 WHERE global_attribute4 = c_rec.awt_type_code
532 AND inactive_date IS NULL
533 )
534 AND ROWNUM = 1;
535
536 SELECT count(*)
537 INTO v_flag
538 FROM JL_ZZ_AP_SUPP_AWT_TYPES
539 WHERE awt_type_code = c_rec.awt_type_code
540 AND vendor_id = P_Supplier_Id;
541
542 IF v_flag = 0 THEN
543 SELECT jl_zz_ap_supp_awt_types_s.nextval
544 INTO v_supp_awt_type_id
545 FROM dual;
546 SELECT jl_zz_ap_sup_awt_cd_s.nextval
547 INTO v_supp_awt_code_id
548 FROM dual;
549
550 ---- Inserting into Supplier Applicability table
551 INSERT INTO JL_ZZ_AP_SUPP_AWT_TYPES (SUPP_AWT_TYPE_ID,
552 VENDOR_ID,
553 AWT_TYPE_CODE,
554 WH_SUBJECT_FLAG,
555 CREATED_BY,
556 CREATION_DATE,
557 LAST_UPDATED_BY,
558 LAST_UPDATE_DATE,
559 LAST_UPDATE_LOGIN)
560 VALUES (v_supp_awt_type_id,
561 P_Supplier_Id,
562 c_rec.awt_type_code,
563 'Y',
564 v_last_update_by,
565 sysdate,
566 v_last_update_by,
567 sysdate,
568 v_last_update_login
569 );
570
571 ---- Defaulting Tax Code for Special AWT Type
572 INSERT INTO JL_ZZ_AP_SUP_AWT_CD_ALL (SUPP_AWT_CODE_ID,
573 SUPP_AWT_TYPE_ID,
574 TAX_ID,
575 PRIMARY_TAX_FLAG,
576 CREATED_BY,
577 CREATION_DATE,
578 LAST_UPDATED_BY,
579 LAST_UPDATE_DATE,
580 LAST_UPDATE_LOGIN,
581 EFFECTIVE_START_DATE,
582 ORG_ID)
583 VALUES (v_supp_awt_code_id,
584 v_supp_awt_type_id,
585 v_tax_id,
586 'Y',
587 v_last_update_by,
588 sysdate,
589 v_last_update_by,
590 sysdate,
591 v_last_update_login,
592 TO_DATE('01/01/1950','DD/MM/YYYY'),
593 v_org_id
594 );
595 Applicability_Chngd_flag := 'Y';
596 IF p_debug_log = 'Y' THEN
597 FND_FILE.put_line( FND_FILE.LOG, '*******************************');
598 FND_FILE.put_line( FND_FILE.LOG, 'SUPPLIER APPLICABILITY UPDATED SUCCESSFULLY FOR SUPPLIER ID : '|| P_Supplier_Id);
599 END IF;
600
601 ELSE
602 UPDATE JL_ZZ_AP_SUPP_AWT_TYPES SET WH_SUBJECT_FLAG = 'Y'
603 WHERE awt_type_code = c_rec.awt_type_code
604 AND vendor_id = P_Supplier_Id;
605
606 SELECT SUPP_AWT_TYPE_ID INTO v_temp1
607 FROM JL_ZZ_AP_SUPP_AWT_TYPES
608 WHERE awt_type_code = c_rec.awt_type_code
609 AND vendor_id = P_Supplier_Id
610 AND ROWNUM = 1;
611
612 UPDATE JL_ZZ_AP_SUP_AWT_CD SET PRIMARY_TAX_FLAG = 'N'
613 WHERE SUPP_AWT_TYPE_ID = v_temp1;
614
615 SELECT count(*) INTO v_temp2
616 FROM JL_ZZ_AP_SUP_AWT_CD
617 WHERE TAX_ID = v_tax_id
618 AND SUPP_AWT_TYPE_ID = v_temp1;
619
620 IF v_temp2 > 0 THEN
621 UPDATE JL_ZZ_AP_SUP_AWT_CD SET PRIMARY_TAX_FLAG = 'Y'
622 WHERE TAX_ID = v_tax_id
623 AND SUPP_AWT_TYPE_ID = v_temp1;
624 ELSE
625 SELECT jl_zz_ap_sup_awt_cd_s.nextval
626 INTO v_supp_awt_code_id
627 FROM dual;
628 INSERT INTO JL_ZZ_AP_SUP_AWT_CD_ALL(SUPP_AWT_CODE_ID,
629 SUPP_AWT_TYPE_ID,
630 TAX_ID,
631 PRIMARY_TAX_FLAG,
632 CREATED_BY,
633 CREATION_DATE,
634 LAST_UPDATED_BY,
635 LAST_UPDATE_DATE,
636 LAST_UPDATE_LOGIN,
637 EFFECTIVE_START_DATE,
638 ORG_ID)
639 VALUES (v_supp_awt_code_id,
640 v_temp1,
641 v_tax_id,
642 'Y',
643 v_last_update_by,
644 sysdate,
645 v_last_update_by,
646 sysdate,
647 v_last_update_login,
648 TO_DATE('01/01/1950','DD/MM/YYYY'),
649 v_org_id
650 );
651 END IF;
652 Applicability_Chngd_flag := 'Y';
653 IF p_debug_log = 'Y' THEN
654 FND_FILE.put_line( FND_FILE.LOG, '*******************************');
655 FND_FILE.put_line( FND_FILE.LOG, 'SUPPLIER APPLICABILITY UPDATED SUCCESSFULLY FOR SUPPLIER ID : '|| P_Supplier_Id);
656 END IF;
657 END IF;
658
659 END LOOP;
660
661 EXCEPTION
662 WHEN OTHERS THEN
663 Applicability_Chngd_flag := 'N';
664 FND_FILE.put_line( FND_FILE.LOG,'AN ERROR IS ENCOUNTERED WHILE CHANGING SUPPLIER APPLICABILITY '|| SQLCODE || 'ERROR ' || SQLERRM);
665 RAISE;
666 END Update_Supplier_Applicability;
667
668
669 PROCEDURE Update_Monotrib_Inv_Distrib_Wh
670 ( P_Invoice_Id IN ap_invoices_all.invoice_id%TYPE
671 , P_vendor_id IN po_vendors.vendor_id%Type
672 --, P_Defaulting_flag BOOLEAN
673 ) IS
674 CURSOR Invoice_Distrib IS
675 SELECT invoice_distribution_id
676 FROM ap_invoice_distributions
677 WHERE invoice_id = P_Invoice_ID;
678 -- The following variables are used to get the information from the invoice
679 -- ditribution lines.
680 v_tax_payer_id ap_invoice_distributions_all.global_attribute2%TYPE;
681 v_ship_to_loc ap_invoice_distributions_all.global_attribute3%TYPE;
682 v_line_type ap_invoice_distributions_all. line_type_lookup_code%TYPE;
683
684 BEGIN
685
686 IF p_debug_log = 'Y' THEN
687 FND_FILE.put_line( FND_FILE.LOG, 'INSIDE MONOTRIBUTO UNPAID INVOICE UPDATE PROCEDURE : '|| p_vendor_id ||'-'|| p_invoice_id);
688 END IF;
689
690 FOR db_reg IN Invoice_Distrib LOOP
691 -------------------------------------------------------------------
692 -- Information Invoice Distribution Lines.
693 -------------------------------------------------------------------
694 SELECT apid.global_attribute2 -- Taxpayer Id for Colombia
695 ,apid.global_attribute3 -- Ship to Location Argentina
696 ,apid.line_type_lookup_code -- Line Type
697 INTO v_tax_payer_id,
698 v_ship_to_loc,
699 v_line_type
700 FROM AP_Invoice_Distributions apid,
701 AP_Invoice_Lines apil
702 WHERE apid.invoice_id = P_Invoice_Id
703 AND apid.invoice_distribution_id = db_reg.invoice_distribution_id
704 AND apil.line_number = apid.invoice_line_number
705 AND apid.invoice_id = apil.invoice_id;
706
707 Monotrib_Wh_Def_Line(P_Invoice_Id
708 ,db_reg.invoice_distribution_id
709 ,v_tax_payer_id
710 ,v_ship_to_loc
711 ,v_line_type
712 ,p_vendor_id
713 );
714 END LOOP;
715
716 EXCEPTION
717 WHEN OTHERS THEN
718 FND_FILE.put_line( FND_FILE.LOG,'AN ERROR IS ENCOUNTERED WHILE CHANGING INVOICE DISTRIBUTION FOR AWT '|| SQLCODE || 'ERROR ' || SQLERRM);
719 RAISE;
720 END Update_Monotrib_Inv_Distrib_Wh;
721
722
723 PROCEDURE Monotrib_Wh_Def_Line
724 ( p_invoice_id NUMBER,
725 p_inv_dist_id NUMBER,
726 p_tax_payer_id ap_invoice_distributions_all.global_attribute2%TYPE,
727 p_ship_to_loc VARCHAR2,
728 p_line_type VARCHAR2,
729 p_vendor_id NUMBER
730 ) IS
731 ---------------------------------------------------------------------
732 -- Cursor Supplier Withholding Types.
733 ---------------------------------------------------------------------
734 CURSOR Supp_Wh_Types(C_Vendor_Id jl_zz_ap_supp_awt_types.vendor_id%TYPE) Is
735 SELECT swt.supp_awt_type_id ,
736 swt.awt_type_code,
737 swc.supp_awt_code_id,
738 swc.org_id,
739 tca.tax_id,
740 tca.global_attribute7, -- Zone
741 awt.jurisdiction_type,
742 awt.province_code,
743 awt.city_code
744 FROM jl_zz_ap_supp_awt_types swt,
745 jl_zz_ap_sup_awt_cd swc,
746 ap_tax_codes tca,
747 jl_zz_ap_awt_types awt
748 WHERE swt.vendor_id = C_vendor_id -- Select only for this Supplier
749 AND swt.wh_subject_flag = 'Y' -- Supp subject to the withholding tax type
750 AND swc.supp_awt_type_id = swt.supp_awt_type_id -- Join
751 AND swc.tax_id = tca.tax_id -- Join
752 AND (tca.inactive_date > sysdate -- Verify Tax Name Inactive Date
753 OR tca.inactive_date IS NULL)
754 AND swc.primary_tax_flag = 'Y' -- Verify the Primary Withholding Tax
755 AND awt.Simplified_Regime_Flag = 'Y' -- Verify the Simplified Regime Withholding tax Type ONLY
756 AND awt.awt_type_code = swt.awt_type_code -- Join
757 AND sysdate between nvl(swc.effective_start_date,sysdate) and nvl(swc.effective_end_date,sysdate)
758 ; -- New Argentine AWT ER 6624809
759
760 v_provincial_code jl_ar_ap_provinces.province_code%TYPE;
761 v_hr_zone hr_locations_all.region_1%TYPE;
762 v_hr_province hr_locations_all.region_2%TYPE;
763 v_hr_city hr_locations_all.town_or_city%TYPE;
764 --pc_vendor_id number;
765 p_calling_sequence varchar2(2000):= 'Mono_trib_Supp_Wth';
766
767
768 BEGIN
769 IF p_debug_log = 'Y' THEN
770 FND_FILE.put_line( FND_FILE.LOG, 'INSIDE "Monotrib_Wh_Def_Line" PROCEDURE : '|| p_vendor_id ||'-'|| p_invoice_id);
771 END IF;
772
773
774 ------------------------------------------------------------------------------------------
775 -- Loop for each Supplier Withholding Type
776 ------------------------------------------------------------------------------------------
777 FOR db_reg IN Supp_Wh_Types(p_vendor_id) LOOP
778 FND_FILE.put_line( FND_FILE.LOG, 'INSIDE "Monotrib_Wh_Def_Line" PROCEDURE - Inside Loop db_reg');
779 FND_FILE.put_line( FND_FILE.LOG, 'INSIDE "Monotrib_Wh_Def_Line" AWT Code '||db_reg.awt_type_code);
780 ---------------------------------------------------------------------------------
781 -- The cursor verify the Supplier Withholding Applicability
782 -- Each Supp Withholding Type in the Cursor needs to be check.
783 -- Company Agent says if the company have to withhold by this Withholding Type.
784 ---------------------------------------------------------------------------------
785 IF ( JL_ZZ_AP_AWT_DEFAULT_PKG.Company_Agent(db_reg.awt_type_code,p_Invoice_Id)) THEN
786 FND_FILE.put_line( FND_FILE.LOG, 'INSIDE "Monotrib_Wh_Def_Line" PROCEDURE - Company validation success');
787 ----------------------------------------------------------------------------
788 -- Validate the withholding type is according to distribution line.
789 ----------------------------------------------------------------------------
790 IF JL_ZZ_AP_AWT_DEFAULT_PKG.Validate_Line_Type(p_line_type, db_reg.tax_id) THEN
791 FND_FILE.put_line( FND_FILE.LOG, 'INSIDE "Monotrib_Wh_Def_Line" PROCEDURE - LineType Validation Success');
792 -----------------------------------------------------------------------
793 -- Get the information from Zone, Province and City
794 -----------------------------------------------------------------------
795 JL_ZZ_AP_AWT_DEFAULT_PKG.Province_Zone_City
796 (p_ship_to_loc -- IN
797 ,v_hr_zone -- OUT NOCOPY
798 ,v_hr_province -- OUT NOCOPY
799 ,v_hr_city ); -- OUT NOCOPY
800 -----------------------------------------------------------------------
801 -- Validate the Jurisdiction
802 -----------------------------------------------------------------------
803 FND_FILE.put_line( FND_FILE.LOG, 'INSIDE "Monotrib_Wh_Def_Line" PROCEDURE - Jurisdiction Type '||db_reg.jurisdiction_type);
804 IF ( db_reg.jurisdiction_type = 'PROVINCIAL') THEN
805 --------------------------------------------------------------------
806 -- Verify if the Withholding Tax for the Province is TERRITORY
807 --------------------------------------------------------------------
808 IF JL_ZZ_AP_AWT_DEFAULT_PKG.Ver_Territorial_Flag (db_reg.province_code) THEN
809 FND_FILE.put_line( FND_FILE.LOG, 'INSIDE "Monotrib_Wh_Def_Line" PROCEDURE - WTax is TERRITORY');
810 -----------------------------------------------------------------
811 -- Validate if the Ship to Location from Inv Dis Line is in the province.
812 -----------------------------------------------------------------
813 IF db_reg.province_code = v_hr_province THEN
814 FND_FILE.put_line( FND_FILE.LOG, 'INSIDE "Monotrib_Wh_Def_Line" PROCEDURE - PROVINCIAL Before Insert');
815 JL_ZZ_AP_AWT_DEFAULT_PKG.Insert_AWT_Default
816 (p_Invoice_Id
817 , p_inv_dist_id
818 , db_reg. supp_awt_code_id
819 , p_calling_sequence
820 , db_reg.org_id );
821
822 END IF;
823 ELSE -- v_territorial_flag = 'N' is Country Wide
824 FND_FILE.put_line( FND_FILE.LOG, 'INSIDE "Monotrib_Wh_Def_Line" PROCEDURE - WTax is NOT TERRITORY');
825 FND_FILE.put_line( FND_FILE.LOG, 'INSIDE "Monotrib_Wh_Def_Line" PROCEDURE - PROVINCIAL Before Insert');
826 JL_ZZ_AP_AWT_DEFAULT_PKG.Insert_AWT_Default
827 (p_Invoice_Id
828 , p_inv_dist_id
829 , db_reg. supp_awt_code_id
830 , p_calling_sequence
831 , db_reg.org_id );
832
833 END IF; -- PROVINCE Class
834 ELSIF db_reg.jurisdiction_type = 'ZONAL' THEN
835 ---------------------------------------------------------------
836 -- The name of the zone is taken from AP_TAX_CODES Global Att 7
837 ---------------------------------------------------------------
838 IF db_reg.global_attribute7 = v_hr_zone THEN
839 FND_FILE.put_line( FND_FILE.LOG, 'INSIDE "Monotrib_Wh_Def_Line" PROCEDURE - ZONAL Before Insert');
840 JL_ZZ_AP_AWT_DEFAULT_PKG.Insert_AWT_Default
841 (p_Invoice_Id
842 , p_inv_dist_id
843 , db_reg. supp_awt_code_id
844 , p_calling_sequence
845 , db_reg.org_id );
846
847 END IF; --Tax_Zone
848
849 ELSIF db_reg.jurisdiction_type = 'MUNICIPAL' THEN
850 ---------------------------------------------------------------
851 -- Compare the Withholding Type City with the city in the line
852 ---------------------------------------------------------------
853 IF db_reg.city_code = v_hr_city THEN
854 FND_FILE.put_line( FND_FILE.LOG, 'INSIDE "Monotrib_Wh_Def_Line" PROCEDURE - MUNICIPAL Before Insert');
855 JL_ZZ_AP_AWT_DEFAULT_PKG.Insert_AWT_Default
856 (p_Invoice_Id
857 , p_inv_dist_id
858 , db_reg. supp_awt_code_id
859 , p_calling_sequence
860 , db_reg.org_id );
861
862 END IF;
863
864 ELSE -- db_reg.jurisdiction_type = 'FEDERAL'
865
866 JL_ZZ_AP_AWT_DEFAULT_PKG.Insert_AWT_Default
867 (p_Invoice_Id
868 , p_inv_dist_id
869 , db_reg. supp_awt_code_id
870 , p_calling_sequence
871 , db_reg.org_id );
872
873 END IF;--jurisdiction type
874 END IF;--validate line_type
875 END IF;--withholding applicability
876 END LOOP; -- Loop for each Supplier Withholding Type
877 EXCEPTION
878 WHEN OTHERS THEN
879 FND_FILE.put_line( FND_FILE.LOG,'AN ERROR IS ENCOUNTERED WHILE CHANGING INVOICE DISTRIBUTION FOR AWT 1 '|| SQLCODE || 'ERROR ' || SQLERRM);
880 RAISE;
881 END Monotrib_Wh_Def_Line;
882
883 FUNCTION AfterReport
884 RETURN BOOLEAN
885 IS
886 BEGIN
887 DELETE from JL_ZZ_INFO_T;
888 RETURN (TRUE);
889 END AfterReport;
890
891 END JL_ZZ_AP_MONOTRIB_AWT_PKG;