[Home] [Help]
PACKAGE BODY: APPS.JG_ZZ_SUMMARY_AR_PKG
Source
1 PACKAGE BODY JG_ZZ_SUMMARY_AR_PKG
2 -- $Header: jgzzsummaryarb.pls 120.14 2008/01/23 10:45:07 anusaxen ship $
3 AS
4
5 gv_debug constant boolean := true;
6
7 FUNCTION app_vatformula(p_applied IN VARCHAR2
8 , p_tax_rate_id IN NUMBER
9 , p_a_date IN VARCHAR2
10 , p_amount_applied IN NUMBER
11 , p_amount IN NUMBER) RETURN NUMBER
12 -- +======================================================================+
13 -- | Name : app_vatformula |
14 -- | Description : This function returns the applied vat amount |
15 -- | . |
16 -- | |
17 -- +======================================================================+
18 IS
19 BEGIN
20
21 DECLARE
22 l_taxrate NUMBER;
23 BEGIN
24 IF p_applied = JG_ZZ_SUMMARY_AR_PKG.g_unapplied THEN
25 RETURN(0);
26 END IF;
27
28 SELECT NVL(ZRB.percentage_rate,ZRB.quantity_rate)
29 INTO l_taxrate
30 FROM zx_rates_b ZRB
31 WHERE ZRB.tax_rate_id = P_TAX_RATE_ID
32 /* UT TEST. these effectivity dates checking is not required for Reporting
33 AND p_a_date >= NVL(ZRB.effective_from, TO_DATE('01-01-1895', 'DD-MM-YYYY'))
34 AND p_a_date <= NVL(ZRB.effective_to, TO_DATE('31-12-2195', 'DD-MM-YYYY')) */
35 ;
36
37 IF p_amount>= p_amount_applied THEN
38 RETURN(ROUND(ABS(p_amount_applied)*(1-1/(1+l_taxrate/100.0)),2)*-1);
39 ELSE
40 RETURN(ROUND(p_amount*(1-1/(1+l_taxrate/100.0)),2)*-1);
41 END IF;
42 EXCEPTION
43 WHEN OTHERS THEN
44 NULL;
45 END;
46
47 RETURN NULL;
48 END;
49
50 FUNCTION new_receiptformula(p_r_date IN VARCHAR2
51 , p_period_start_date IN DATE
52 , p_period_end_date IN DATE) RETURN VARCHAR2
53 -- +======================================================================+
54 -- | Name : new_receiptformula |
55 -- | Description : This function returns the new receipt |
56 -- | . |
57 -- | |
58 -- +======================================================================+
59 IS
60 BEGIN
61
62 DECLARE
63 l_new VARCHAR2(3);
64 BEGIN
65 SELECT 'Yes' INTO l_new
66 FROM DUAL
67 WHERE p_r_date BETWEEN NVL(P_PERIOD_START_DATE,TO_DATE('01-01-1690','DD-MM-YYYY'))
68 AND NVL(P_PERIOD_END_DATE,TO_DATE('31-12-2690','DD-MM-YYYY'));
69 RETURN(l_new);
70 EXCEPTION
71 WHEN NO_DATA_FOUND THEN
72 RETURN(l_new);
73 END;
74
75 RETURN NULL;
76 END;
77
78 FUNCTION new_applicationformula(p_a_date IN VARCHAR2
79 , p_period_start_date IN DATE
80 , p_period_end_date IN DATE) RETURN VARCHAR2
81 -- +======================================================================+
82 -- | Name : new_applicationformula |
83 -- | Description : This function returns the new application |
84 -- | . |
85 -- | |
86 -- +======================================================================+
87 IS
88 BEGIN
89
90 DECLARE
91 l_new VARCHAR2(3);
92 BEGIN
93 SELECT 'Yes'
94 INTO l_new
95 FROM DUAL
96 WHERE p_a_date BETWEEN NVL(p_period_start_date,TO_DATE('01-01-1700','DD-MM-YYYY')) AND NVL(p_period_end_date,TO_DATE('31-12-4000','DD-MM-YYYY'));
97 RETURN(l_new);
98 EXCEPTION
99 WHEN NO_DATA_FOUND THEN
100 RETURN(l_new);
101 END;
102 RETURN NULL;
103 END;
104
105 FUNCTION new_amountformula(p_r_date IN VARCHAR2
106 , p_amount IN NUMBER
107 , p_period_start_date IN DATE
108 , p_period_end_date IN DATE) RETURN NUMBER
109 -- +======================================================================+
110 -- | Name : new_amountformula |
111 -- | Description : This function returns the new amount |
112 -- | . |
113 -- | |
114 -- +======================================================================+
115 IS
116 BEGIN
117
118 BEGIN
119 IF p_r_date BETWEEN NVL(p_period_start_date,TO_DATE('01-01-1690','DD-MM-YYYY')) AND NVL(p_period_end_date,TO_DATE('31-12-2690','DD-MM-YYYY')) THEN
120 RETURN(p_amount);
121 ELSE
122 RETURN(0);
123 END IF;
124 END;
125 RETURN NULL;
126 END;
127
128 FUNCTION new_app_vatformula(p_a_date IN VARCHAR2
129 , p_app_vat IN NUMBER
130 , p_period_start_date IN DATE
131 , p_period_end_date IN DATE) RETURN NUMBER
132 -- +======================================================================+
133 -- | Name : new_app_vatformula |
134 -- | Description : This function returns the new applied vat amount |
135 -- | . |
136 -- | |
137 -- +======================================================================+
138 IS
139 BEGIN
140
141 BEGIN
142 IF p_a_date between NVL(p_period_start_date,TO_DATE('01-01-1700','DD-MM-YYYY')) AND NVL(p_period_end_date, to_date('31-12-4000','DD-MM-YYYY')) THEN
143 RETURN (p_app_vat);
144 ELSE
145 RETURN(0);
146 END IF;
147 END;
148 RETURN NULL;
149 END;
150
151 FUNCTION new_unapp_vatformula(p_a_date IN VARCHAR2
152 , p_unapp_vat IN NUMBER
153 , p_period_start_date IN DATE
154 , p_period_end_date IN DATE) RETURN NUMBER
155 -- +======================================================================+
156 -- | Name : new_unapp_vatformula |
157 -- | Description : This function returns the new unapplied vat amount|
158 -- | . |
159 -- | |
160 -- +======================================================================+
161 IS
162 BEGIN
163
164 BEGIN
165 IF p_a_date BETWEEN NVL(p_period_start_date,TO_DATE('01-01-1700','DD-MM-YYYY')) AND NVL(p_period_end_date,TO_DATE('31-12-4000','DD-MM-YYYY')) THEN
166 RETURN (p_unapp_vat);
167 ELSE
168 RETURN(0);
169 END IF;
170 END;
171 RETURN NULL;
172 END;
173
174 FUNCTION unapp_vatformula(p_applied IN VARCHAR2
175 , p_tax_rate_id IN NUMBER
176 , p_a_date IN VARCHAR2
177 , p_amount_applied IN NUMBER
178 , p_amount IN NUMBER) RETURN NUMBER
179 -- +======================================================================+
180 -- | Name : unapp_vatformula |
181 -- | Description : This function returns the unapplied vat amount |
182 -- | . |
183 -- | |
184 -- +======================================================================+
185 IS
186 BEGIN
187
188 DECLARE
189 l_taxrate number;
190 BEGIN
191 IF p_applied = JG_ZZ_SUMMARY_AR_PKG.g_applied THEN
192 RETURN(0);
193 END IF;
194
195 SELECT NVL(ZRB.percentage_rate,ZRB.quantity_rate)
196 INTO l_taxrate
197 FROM zx_rates_b ZRB
198 WHERE ZRB.tax_rate_id = P_TAX_RATE_ID
199 AND p_a_date >= NVL(ZRB.effective_from, TO_DATE('01-01-1895', 'DD-MM-YYYY'))
200 AND p_a_date <= NVL(ZRB.effective_to, TO_DATE('31-12-2195', 'DD-MM-YYYY'));
201
202 IF p_amount>= p_amount_applied THEN
203 RETURN(ROUND(ABS(p_amount_applied)*(1-1/(1+l_taxrate/100.0)),2));
204 ELSE
205 RETURN(ROUND(p_amount*(1-1/(1+l_taxrate/100.0)),2));
206 END IF;
207 END;
208 RETURN NULL;
209 END;
210
211 FUNCTION new_aaformula(p_a_date IN VARCHAR2
212 , p_applied IN VARCHAR2
213 , p_amount_applied IN NUMBER
214 , p_period_start_date IN DATE
215 , p_period_end_date IN DATE) RETURN NUMBER
216 -- +======================================================================+
217 -- | Name : new_aaformula |
218 -- | Description : This function returns the new applied amount |
219 -- | . |
220 -- | |
221 -- +======================================================================+
222 IS
223 BEGIN
224
225 BEGIN
226 IF p_a_date BETWEEN NVL(p_period_start_date,TO_DATE('01-01-1700','DD-MM-YYYY')) AND NVL(p_period_end_date,TO_DATE('31-12-4000','DD-MM-YYYY'))
227 AND p_Applied = JG_ZZ_SUMMARY_AR_PKG.g_applied THEN
228 RETURN (p_amount_applied);
229 ELSE
230 RETURN(0);
231 END IF;
232 END;
233 RETURN NULL;
234 END;
235
236 FUNCTION new_auformula(p_a_date IN VARCHAR2
237 , p_applied IN VARCHAR2
238 , p_amount_applied IN NUMBER
239 , p_period_start_date IN DATE
240 , p_period_end_date IN DATE) RETURN NUMBER
241 -- +======================================================================+
242 -- | Name : new_auformula |
243 -- | Description : This function returns the new applied amount |
244 -- | . |
245 -- | |
246 -- +======================================================================+
247 IS
248 BEGIN
249
250 BEGIN
251 IF p_a_date BETWEEN NVL(p_period_start_date,TO_DATE('01-01-1700','DD-MM-YYYY')) AND NVL(p_period_end_date,TO_DATE('31-12-4000','DD-MM-YYYY'))
252 AND p_applied = JG_ZZ_SUMMARY_AR_PKG.g_unapplied THEN
253 RETURN (p_amount_applied);
254 ELSE
255 RETURN(0);
256 END IF;
257 END;
258 RETURN NULL;
259 END;
260
261 FUNCTION ZEROFormula RETURN NUMBER
262 -- +======================================================================+
263 -- | Name : ZEROFormula |
264 -- | Description : This function returns the zero |
265 -- | . |
266 -- | |
267 -- +======================================================================+
268 IS
269 BEGIN
270
271 RETURN(0);
272 END;
273
274 FUNCTION vatformula(p_applied IN VARCHAR2
275 , p_disp_app_vat IN VARCHAR2
276 , p_disp_unapp_vat IN VARCHAR2) RETURN VARCHAR2
277 -- +======================================================================+
278 -- | Name : vatformula |
279 -- | Description : This function returns the vat |
280 -- | . |
281 -- | |
282 -- +======================================================================+
283 IS
284 BEGIN
285
286 BEGIN
287 IF p_applied = JG_ZZ_SUMMARY_AR_PKG.g_applied THEN
288 RETURN (p_disp_app_vat);
289 END IF;
290 RETURN (p_disp_unapp_vat);
291 END;
292 RETURN NULL;
293 END;
294
295 FUNCTION rdformula(p_rev_date IN DATE
296 , p_period_start_date IN DATE
297 , p_period_end_date IN DATE) RETURN NUMBER
298 -- +======================================================================+
299 -- | Name : rdformula |
300 -- | Description : This function returns the rd value |
301 -- | . |
302 -- | |
303 -- +======================================================================+
304 IS
305 BEGIN
306
307 DECLARE
308 l_rd NUMBER;
309 BEGIN
310 l_rd := 0;
311 SELECT 1
312 INTO l_rd
313 FROM DUAL
314 WHERE p_rev_date BETWEEN NVL(p_period_start_date,TO_DATE('01-01-1890' ,'DD-MM-YYYY')) AND NVL(p_period_end_date,TO_DATE('30-12-2199','DD-MM-YYYY'));
315 RETURN (l_rd);
316 EXCEPTION
317 WHEN NO_DATA_FOUND THEN
318 RETURN(l_rd);
319 END;
320 RETURN NULL;
321 END;
322
323 FUNCTION new_reversalformula(p_rev_date IN DATE
324 , p_period_start_date IN DATE
325 , p_period_end_date IN DATE) RETURN VARCHAR2
326 -- +======================================================================+
327 -- | Name : new_reversalformula |
328 -- | Description : This function returns the new reversal value |
329 -- | . |
330 -- | |
331 -- +======================================================================+
332 IS
333 BEGIN
334
335 DECLARE
336 l_new VARCHAR2(3);
337 BEGIN
338 SELECT 'Yes'
339 INTO l_new
340 FROM DUAL
341 WHERE p_rev_date BETWEEN NVL(p_period_start_date,TO_DATE('01-01-1690','DD-MM-YYYY')) AND NVl(p_period_end_date,TO_DATE('31-12-2690','DD-MM-YYYY'));
342 RETURN(l_new);
343 EXCEPTION
344 WHEN NO_DATA_FOUND THEN
345 RETURN(l_new);
346 END;
347 RETURN NULL;
348 END;
349
350 FUNCTION new_rev_amountformula(p_rd IN NUMBER
351 , p_rev_amount IN NUMBER) RETURN NUMBER
352
353 -- +======================================================================+
354 -- | Name : new_rev_amountformula |
355 -- | Description : This function returns the new reversal amount |
356 -- | . |
357 -- | |
358 -- +======================================================================+
359 IS
360 BEGIN
361
362 RETURN (p_rd * p_rev_amount);
363 END;
364
365 FUNCTION new_rev_taxformula(p_rd IN NUMBER
366 , p_rev_tax IN NUMBER) RETURN NUMBER
367 -- +======================================================================+
368 -- | Name : new_rev_taxformula |
369 -- | Description : This function returns the new reversal tax amount|
370 -- | . |
371 -- | |
372 -- +======================================================================+
373 IS
374 BEGIN
375 RETURN (p_rd * p_rev_tax);
376 END;
377
378 FUNCTION new_r_taxformula(p_r_date IN VARCHAR2
379 , p_r_tax IN NUMBER
380 , p_period_start_date IN DATE
381 , p_period_end_date IN DATE) RETURN NUMBER
382 -- +======================================================================+
383 -- | Name : new_r_taxformula |
384 -- | Description : This function returns the new tax amount |
385 -- | . |
386 -- | |
387 -- +======================================================================+
388 IS
389 BEGIN
390
391 BEGIN
392 IF p_r_date BETWEEN NVL(p_period_start_date,TO_DATE('01-01-1690','DD-MM-YYYY')) AND NVL(p_period_end_date,TO_DATE('31-12-2690','DD-MM-YYYY')) THEN
393 RETURN(p_r_tax);
394 ELSE
395 RETURN(0);
396 END IF;
397 END;
398 RETURN NULL;
399 END;
400
401
402 /*
403 || Added by Ramananda
404 || The following function will consider the tax line based on the
405 || arguments passed from the data template
406 */
407 FUNCTION is_tax_status( p_tax_status IN VARCHAR2
408 ,p_context IN VARCHAR2
409 ,p_tax_rate IN NUMBER DEFAULT NULL)
410 RETURN BOOLEAN
411 IS
412 BEGIN
413
414 IF p_tax_status = 'Non-Taxable' AND
415 p_context = 'NON_TAXABLE' THEN
416 return(TRUE) ;
417 ELSIF p_tax_status = 'Exempt Exports' AND
418 p_context = 'EXEMPT_EXPORTS' THEN
419 return(TRUE) ;
420 ELSIF p_tax_status = 'Exempt Other' AND
421 p_context = 'EXEMPT_OTHER' THEN
422 return(TRUE) ;
423 ELSIF p_tax_status = 'Zero Rated' AND
424 p_tax_rate = 0 AND
425 p_context = 'ZERO_RATE' THEN
426 return(TRUE) ;
427 ELSIF p_tax_status IN ( 'Standard','Reduced') AND
428 p_tax_rate <> 0 AND
429 p_context = 'NON_FINAL_CONSUMPTION' THEN
430 return(TRUE) ;
431 ELSIF p_tax_status = 'Final Consumption' AND
432 p_tax_rate <> 0 AND
433 p_context = 'FINAL_CONSUMPTION' THEN
434 return(TRUE) ;
435 ELSE
436 return(FALSE) ;
437 END IF;
438
439 return(TRUE) ;
440
441 END is_tax_status;
442
443 /* The tax_date_maintenance_program procedure not suppose to call here.
444 We should run this procedure before the selection process run i.e Before the TRL call (as in 11i)
445 Hence commenting this total procedure code.
446 */
447
448 /*
449
450 PROCEDURE tax_date_maintenance_program(p_period_end_date IN DATE)
451 IS
452 -- +======================================================================+
453 -- | Name : tax_date_maintenance_program |
454 -- | Description : This procedure maintain the tax date |
455 -- | . |
456 -- | |
457 -- +======================================================================+
458
459 CURSOR lcu_cust_trx
460 IS
461 SELECT JG.trx_id CUSTOMER_TRX_ID
462 , MAX(RPT.apply_date) APPLY_DATE
463 , FND_DATE.CANONICAL_TO_DATE(JG.tax_invoice_date) TAX_INVOICE_DATE
464 FROM jg_zz_vat_trx_details JG
465 , ar_receivable_applications RPT
466 WHERE JG.trx_id = RPT.applied_customer_trx_id
467 AND JG.ledger_id = RPT.set_of_books_id
468 AND RPT.status = 'APP'
469 AND JG.tax_status_code = 'CL'
470 AND RPT.amount_applied >= 0
471 AND TRUNC(RPT.apply_date) <= TRUNC(P_PERIOD_END_DATE)
472 AND RPT.apply_date < FND_DATE.CANONICAL_TO_DATE(JG.tax_invoice_date)
473 AND NOT EXISTS (SELECT 1
474 FROM jg_zz_vat_trx_details JGZZ
475 WHERE JGZZ.trx_id = JG.trx_id
476 AND JGZZ.tax_invoice_date IS NOT NULL
477 )
478 GROUP BY JG.trx_id
479 ,JG.tax_invoice_date
480 ;
481
482 BEGIN
483
484 FOR rec_cust_trx IN lcu_cust_trx
485 LOOP
486 BEGIN
487 UPDATE jg_zz_vat_trx_details JG
488 SET JG.tax_invoice_date = FND_DATE.DATE_TO_CANONICAL(rec_cust_trx.apply_date )
489 WHERE JG.trx_id = rec_cust_trx.customer_trx_id;
490 EXCEPTION
491 WHEN OTHERS THEN
492 NULL;
493 END;
494
495 END LOOP;
496 COMMIT;
497
498 EXCEPTION
499 WHEN OTHERS THEN
500 NULL;
501 END; */
502
503 PROCEDURE InsertIntoGlobal (
504 p_jg_info_n1 IN NUMBER DEFAULT NULL
505 , p_jg_info_n2 IN NUMBER DEFAULT NULL
506 , p_jg_info_n3 IN NUMBER DEFAULT NULL
507 , p_jg_info_n4 IN NUMBER DEFAULT NULL
508 , p_jg_info_n5 IN NUMBER DEFAULT NULL
509 , p_jg_info_n6 IN NUMBER DEFAULT NULL
510 , p_jg_info_n7 IN NUMBER DEFAULT NULL
511 , p_jg_info_n8 IN NUMBER DEFAULT NULL
512 , p_jg_info_n9 IN NUMBER DEFAULT NULL
513 , p_jg_info_n11 IN NUMBER DEFAULT NULL
514 , p_jg_info_n14 IN NUMBER DEFAULT NULL
515 , p_jg_info_n15 IN NUMBER DEFAULT NULL
516 , p_jg_info_n16 IN NUMBER DEFAULT NULL
517 , p_jg_info_n17 IN NUMBER DEFAULT NULL
518 , p_jg_info_n18 IN NUMBER DEFAULT NULL
519 , p_jg_info_n19 IN NUMBER DEFAULT NULL
520 , p_jg_info_n20 IN NUMBER DEFAULT NULL
521 , p_jg_info_n21 IN NUMBER DEFAULT NULL
522 , p_jg_info_n22 IN NUMBER DEFAULT NULL
523 , p_jg_info_n23 IN NUMBER DEFAULT NULL
524 , p_jg_info_n24 IN NUMBER DEFAULT NULL
525 , p_jg_info_n25 IN NUMBER DEFAULT NULL
526 , p_jg_info_n26 IN NUMBER DEFAULT NULL
527 , p_jg_info_n27 IN NUMBER DEFAULT NULL
528 , p_jg_info_d1 IN DATE DEFAULT NULL
529 , p_jg_info_d2 IN DATE DEFAULT NULL
530 , p_jg_info_d3 IN DATE DEFAULT NULL
531 , p_jg_info_d4 IN DATE DEFAULT NULL
532 , p_jg_info_d5 IN DATE DEFAULT NULL
533 , p_jg_info_v1 IN CHAR DEFAULT NULL
534 , p_jg_info_v2 IN CHAR DEFAULT NULL
535 , p_jg_info_v3 IN CHAR DEFAULT NULL
536 , p_jg_info_v4 IN CHAR DEFAULT NULL
537 , p_jg_info_v5 IN CHAR DEFAULT NULL
538 , p_jg_info_v6 IN CHAR DEFAULT NULL
539 , p_jg_info_v7 IN CHAR DEFAULT NULL
540 , p_jg_info_v8 IN CHAR DEFAULT NULL
541 , p_jg_info_v9 IN CHAR DEFAULT NULL
542 , p_jg_info_v10 IN CHAR DEFAULT NULL
543 , p_jg_info_v11 IN CHAR DEFAULT NULL
544 , p_jg_info_v12 IN CHAR DEFAULT NULL
545 , p_jg_info_v13 IN CHAR DEFAULT NULL
546 , p_jg_info_v14 IN CHAR DEFAULT NULL
547 , p_jg_info_v15 IN CHAR DEFAULT NULL
548 , p_jg_info_v16 IN CHAR DEFAULT NULL
549 , p_jg_info_v17 IN CHAR DEFAULT NULL
550 , p_jg_info_v18 IN CHAR DEFAULT NULL
551 , p_jg_info_v19 IN CHAR DEFAULT NULL
552 , p_jg_info_v20 IN CHAR DEFAULT NULL
553 , p_jg_info_v21 IN CHAR DEFAULT NULL
554 , p_jg_info_v22 IN CHAR DEFAULT NULL
555 , p_jg_info_v23 IN CHAR DEFAULT NULL
556 , p_jg_info_v24 IN CHAR DEFAULT NULL
557 , p_jg_info_v25 IN CHAR DEFAULT NULL
558 , p_jg_info_v26 IN CHAR DEFAULT NULL
559 , p_jg_info_v27 IN CHAR DEFAULT NULL
560 , p_jg_info_v28 IN CHAR DEFAULT NULL
561 , p_jg_info_v29 IN CHAR DEFAULT NULL
562 , p_jg_info_v30 IN CHAR DEFAULT NULL
563 , p_jg_info_v31 IN CHAR DEFAULT NULL
564 , p_jg_info_v32 IN CHAR DEFAULT NULL
565 , p_jg_info_v33 IN CHAR DEFAULT NULL
566 , p_jg_info_v34 IN CHAR DEFAULT NULL
567 , p_jg_info_v35 IN CHAR DEFAULT NULL
568 , p_jg_info_v36 IN CHAR DEFAULT NULL
569 , p_jg_info_v37 IN CHAR DEFAULT NULL
570 )
571 IS
572 -- +======================================================================+
573 -- | Name : InsertIntoGlobal |
574 -- | Description : This procedure inserts data into the Global Temp |
575 -- | table |
576 -- +======================================================================+
577 BEGIN
578 INSERT INTO JG_ZZ_VAT_TRX_GT(
579 jg_info_n1
580 , jg_info_n2
581 , jg_info_n3
582 , jg_info_n4
583 , jg_info_n5
584 , jg_info_n6
585 , jg_info_n7
586 , jg_info_n8
587 , jg_info_n9
588 , jg_info_n11
589 , jg_info_n14
590 , jg_info_n15
591 , jg_info_n16
592 , jg_info_n17
593 , jg_info_n18
594 , jg_info_n19
595 , jg_info_n20
596 , jg_info_n21
597 , jg_info_n22
598 , jg_info_n23
599 , jg_info_n24
600 , jg_info_n25
601 , jg_info_n26
602 , jg_info_n27
603 , jg_info_d1
604 , jg_info_d2
605 , jg_info_d3
606 , jg_info_d4
607 , jg_info_d5
608 , jg_info_v1
609 , jg_info_v2
610 , jg_info_v3
611 , jg_info_v4
612 , jg_info_v5
613 , jg_info_v6
614 , jg_info_v7
615 , jg_info_v8
616 , jg_info_v9
617 , jg_info_v10
618 , jg_info_v11
619 , jg_info_v12
620 , jg_info_v13
621 , jg_info_v14
622 , jg_info_v15
623 , jg_info_v16
624 , jg_info_v17
625 , jg_info_v18
626 , jg_info_v19
627 , jg_info_v20
628 , jg_info_v21
629 , jg_info_v22
630 , jg_info_v23
631 , jg_info_v24
632 , jg_info_v25
633 , jg_info_v26
634 , jg_info_v27
635 , jg_info_v28
636 , jg_info_v29
637 , jg_info_v30
638 , jg_info_v31
639 , jg_info_v32
640 , jg_info_v33
641 , jg_info_v34
642 , jg_info_v35
643 , jg_info_v36
644 , jg_info_v37
645 )
646 VALUES
647 (
648 p_jg_info_n1
649 , p_jg_info_n2
650 , p_jg_info_n3
651 , p_jg_info_n4
652 , p_jg_info_n5
653 , p_jg_info_n6
654 , p_jg_info_n7
655 , p_jg_info_n8
656 , p_jg_info_n9
657 , p_jg_info_n11
658 , p_jg_info_n14
659 , p_jg_info_n15
660 , p_jg_info_n16
661 , p_jg_info_n17
662 , p_jg_info_n18
663 , p_jg_info_n19
664 , p_jg_info_n20
665 , p_jg_info_n21
666 , p_jg_info_n22
667 , p_jg_info_n23
668 , p_jg_info_n24
669 , p_jg_info_n25
670 , p_jg_info_n26
671 , p_jg_info_n27
672 , p_jg_info_d1
673 , p_jg_info_d2
674 , p_jg_info_d3
675 , p_jg_info_d4
676 , p_jg_info_d5
677 , p_jg_info_v1
678 , p_jg_info_v2
679 , p_jg_info_v3
680 , p_jg_info_v4
681 , p_jg_info_v5
682 , p_jg_info_v6
683 , p_jg_info_v7
684 , p_jg_info_v8
685 , p_jg_info_v9
686 , p_jg_info_v10
687 , p_jg_info_v11
688 , p_jg_info_v12
689 , p_jg_info_v13
690 , p_jg_info_v14
691 , p_jg_info_v15
692 , p_jg_info_v16
693 , p_jg_info_v17
694 , p_jg_info_v18
695 , p_jg_info_v19
696 , p_jg_info_v20
697 , p_jg_info_v21
698 , p_jg_info_v22
699 , p_jg_info_v23
700 , p_jg_info_v24
701 , p_jg_info_v25
702 , p_jg_info_v26
703 , p_jg_info_v27
704 , p_jg_info_v28
705 , p_jg_info_v29
706 , p_jg_info_v30
707 , p_jg_info_v31
708 , p_jg_info_v32
709 , p_jg_info_v33
710 , p_jg_info_v34
711 , p_jg_info_v35
712 , p_jg_info_v36
713 , p_jg_info_v37
714 );
715 EXCEPTION
716 WHEN OTHERS THEN
717 NULL;
718 END InsertIntoGlobal;
719
720 /*
721 REM +======================================================================+
722 REM Name: get_bsv
723 REM
724 REM Description: This function is called in the lcu_ger_receipt cursor for getting the
725 REM BSV for each receipt.
726 REM
727 REM
728 REM Parameters: p_ccid (code combination id)
729 REM p_coid (chart of account id)
730 REM p_ledger_id (Ldger ID)
731 REM +======================================================================+
732 */
733
734 FUNCTION get_bsv(p_ccid NUMBER,p_coid NUMBER,p_ledger_id NUMBER) RETURN NUMBER IS
735
736 l_segment VARCHAR2(30);
737 bal_segment_value VARCHAR2(25);
738
739 BEGIN
740
741 SELECT application_column_name
742 INTO l_segment
743 FROM fnd_segment_attribute_values ,
744 gl_ledgers gl
745 WHERE id_flex_code = 'GL#'
746 AND attribute_value = 'Y'
747 AND segment_attribute_type = 'GL_BALANCING'
748 AND application_id = 101
749 AND id_flex_num = gl.chart_of_accounts_id
750 AND gl.chart_of_accounts_id = p_coid
751 AND gl.ledger_id = p_ledger_id;
752
753 EXECUTE IMMEDIATE 'SELECT '||l_segment ||
754 ' FROM gl_code_combinations '||
755 ' WHERE code_combination_id = '||p_ccid
756 INTO bal_segment_value;
757
758 RETURN (bal_segment_value);
759
760 EXCEPTION
761 WHEN NO_DATA_FOUND THEN
762 fnd_file.put_line(fnd_file.log,' No record was returned for the GL_Balancing segment. Error : ' || SUBSTR(SQLERRM,1,200));
763 RETURN NULL;
764
765 END get_bsv;
766
767 FUNCTION beforeReport RETURN BOOLEAN
768 -- +======================================================================+
769 -- | Name : beforeReport |
770 -- | Description : This procedure processes the data before the |
771 -- | execution of report. |
772 -- | |
773 -- +======================================================================+
774 IS
775 --JEDEDVOR - German VAT for On-account Receipts Report
776 /* Modified the below query a lot during UT TEST */
777 CURSOR lcu_ger_receipt ( P_VAT_REP_ENTITY_ID IN NUMBER
778 , P_PERIOD IN VARCHAR2
779 , P_LEGAL_ENTITY_ID IN NUMBER
780 , P_LEDGER_ID IN NUMBER
781 , P_CHART_OF_ACC_ID IN NUMBER
782 , P_COMPANY IN NUMBER
783 , P_TAX_RATE_ID IN NUMBER
784 , P_REPORTING_LEVEL IN VARCHAR2
785 , P_GL_PERIOD_START_DATE IN DATE
786 , P_GL_PERIOD_END_DATE IN DATE)
787 IS
788 SELECT DISTINCT 'Receipt' RECEIPT,
789 ac.receipt_number RECEIPT_NUMBER,
790 ac.reversal_date REV_DATE,
791 decode(ac.status,'REV','Reversed',
792 'NSF','Reversed',
793 'STOP','Reversed') STATUS,
794 ac.receipt_date R_DATE ,
795 nvl(ac.amount,0) AMOUNT,
796 -1 * nvl(ac.amount,0) REV_AMOUNT,
797 at.tax_rate_code TAX_CODE,
798 ac.currency_code CURRENCT_CODE,
799 round(ac.amount * (1 - 1/(1 + at.PERCENTAGE_RATE/100.0)),2) R_TAX,
800 -1 * round(ac.amount * (1 - 1/(1 + at.PERCENTAGE_RATE/100.0)),2) REV_TAX,
801 aa.cash_receipt_id CASH_RECEIPT_ID
802 FROM ar_cash_receipts_all ac,
803 ar_receivable_applications_all aa,
804 zx_rates_b at
805 WHERE aa.cash_receipt_id = ac.cash_receipt_id
806 AND ac.org_id = aa.org_id
807 AND at.tax_rate_id = ac.vat_tax_id
808 AND ( ( P_REPORTING_LEVEL = 'LE' AND ac.legal_entity_id = P_LEGAL_ENTITY_ID)
809 OR ( P_REPORTING_LEVEL = 'LEDGER' AND ac.set_of_books_id = P_LEDGER_ID)
810 OR ( P_REPORTING_LEVEL = 'BSV' AND JG_ZZ_SUMMARY_AR_PKG.get_bsv(aa.code_combination_id,P_CHART_OF_ACC_ID,P_LEDGER_ID)= P_COMPANY )
811 )
812 AND (ac.vat_tax_id = P_TAX_RATE_ID OR P_TAX_RATE_ID IS NULL)
813 AND ( (aa.status = 'ACC')
814 OR (aa.applied_customer_trx_id IN(
815 SELECT trx1.customer_trx_id
816 FROM ra_customer_trx_all trx1, ra_cust_trx_types_all type1
817 WHERE trx1.cust_trx_type_id = type1.cust_trx_type_id
818 AND trx1.org_id = type1.org_id
819 AND type1.type ='DEP' ) ) )
820 AND (ac.receipt_date between
821 NVL(TO_DATE(P_GL_PERIOD_START_DATE ,'DD-MM-YYYY'),TO_DATE('01-01-1890' ,'DD-MM-YYYY'))
822 AND NVL(TO_DATE(P_GL_PERIOD_END_DATE,'DD-MM-YYYY'),TO_DATE('30-12-2099','DD-MM-YYYY'))
823 OR aa.cash_receipt_id IN
824 ( SELECT distinct a1.cash_receipt_id
825 FROM ar_receivable_applications_all a1,
826 ar_receivable_applications_all a2,
827 hz_cust_accounts rc
828 WHERE
829 DECODE(SIGN(a1.amount_applied),-1,a1.gl_date,a1.gl_date) BETWEEN
830 NVL(TO_DATE(P_GL_PERIOD_START_DATE,'DD-MM-YYYY'),TO_DATE('01-01-1890','DD-MM-YYYY'))
831 AND nvl(TO_DATE(P_GL_PERIOD_END_DATE,'DD-MM-YYYY'), TO_DATE('30-12-2199','DD-MM-YYYY'))
832 AND a1.status = 'APP'
833 AND ( (a2.status = 'ACC')
834 OR (a2.applied_customer_trx_id IN(
835 SELECT trx2.customer_trx_id
836 FROM ra_customer_trx trx2, ra_cust_trx_types type2
837 WHERE trx2.cust_trx_type_id = type2.cust_trx_type_id
838 AND type2.type ='DEP' ) ) )
839 AND a1.cash_receipt_id = a2.cash_receipt_id
840 AND a1.org_id = a2.org_id))
841 ORDER BY ac.currency_code, at.tax_rate_code, ac.receipt_number,
842 SUBSTR(ac.receipt_date,1,10);
843
844 CURSOR lcu_ger_appl (P_CASH_RECEIPT_ID IN NUMBER)
845 IS
846 SELECT
847 DECODE(SIGN(aa.amount_applied),-1,'Unapplied','Applied') APPLIED,
848 aa.receivable_application_id RECEIVABLE_APPLICATION_ID,
849 aa.applied_customer_trx_id APPLIED_CUSTOMER_TRX_ID,
850 aa.gl_date A_DATE,
851 -1 * aa.amount_applied AMOUNT_APPLIED,
852 tr.invoice_currency_code INVOICE_CURRENCY_CODE,
853 at.tax_rate_code TAX_CODE,
854 -- aa.cash_receipt_id,
855 SUBSTR(hzp.party_name,1,20) CUSTOMER_NAME,
856 SUBSTR(csu.location,1,20) LOCATION,
857 at.tax_rate_id TAX_RATE_ID
858 -- ac.RECEIPT_NUMBER
859 FROM ar_receivable_applications_all aa,
860 zx_rates_b at,
861 ar_cash_receipts_all ac,
862 ra_customer_trx tr,
863 hz_cust_accounts rc,
864 hz_parties hzp,
865 hz_cust_acct_sites cs,
866 hz_cust_site_uses csu
867 WHERE aa.applied_customer_trx_id = tr.customer_trx_id
868 AND aa.cash_receipt_id = P_CASH_RECEIPT_ID
869 AND ac.cash_receipt_id = aa.cash_receipt_id
870 AND ac.org_id = aa.org_id
871 AND aa.status = 'APP'
872 AND rc.cust_account_id = tr.bill_to_customer_id
873 AND rc.party_id = hzp.party_id
874 AND rc.cust_account_id = cs.cust_account_id
875 AND cs.cust_acct_site_id = csu.cust_acct_site_id
876 AND tr.bill_to_site_use_id = csu.site_use_id
877 AND at.tax_rate_id = ac.vat_tax_id
878 AND aa.gl_date <= nvl(to_date(P_GL_PERIOD_END_DATE,'DD-MM-YYYY'),
879 to_date('31-12-4000','DD-MM-YYYY'))
880 ORDER BY aa.receivable_application_id;
881
882 CURSOR get_tax_rate_code(p_tax_rate_id IN NUMBER)
883 IS
884 SELECT tax_rate_code
885 FROM zx_rates_b
886 WHERE tax_rate_id = p_tax_rate_id;
887
888 --JGZZARVR - ECE Receivables VAT Register Report
889
890 CURSOR lcu_euar_vatreg ( p_vat_rep_config_id IN NUMBER
891 , p_period IN VARCHAR2
892 , p_vat_trx_type IN VARCHAR2
893 , p_ex_vat_trx_type IN VARCHAR2)
894 IS
895 SELECT JG.doc_seq_value SEQ_NUM
896 , JG.tax_rate TAX_RATE
897 , (NVL(JG.tax_amt,0)
898 + NVL(JG.taxable_amt,0)) TRX_AMOUNT
899 /* UT TEST change nvl(xxxamt__funcl_curr,0) to nvl(xxx_func_curr, xxx_amt) */
900 , (NVL(JG.tax_amt_funcl_curr, tax_amt)
901 + NVL(JG.taxable_amt_funcl_curr, taxable_amt)) FUNC_AMOUNT
902 , NVL(JG.taxable_amt_funcl_curr, taxable_amt) TAXABLE_AMOUNT
903 , NVL(JG.tax_amt_funcl_curr, tax_amt) TAX_AMOUNT
904 , JG.tax_invoice_date TAX_DATE
905 , JG.trx_date INVOICE_DATE
906 , JG.accounting_date GL_DATE
907 , DECODE(JG.trx_line_class
908 ,'ADJUSTMENT',JG.applied_to_trx_number
909 , JG.trx_number) INVOICE_NUMBER
910 , JG.trx_id TRX_ID
911 , JG.billing_tp_name CUST_NAME
912 , JG.billing_tp_tax_reg_num TAX_REG_NUM
913 , JG.trx_currency_code CURR
914 , JG.tax_rate_code TAX_CODE
915 , JG.tax_rate_code_name TAX_DESC
916 , JG.tax_rate_vat_trx_type_desc VAT_DESC
917 , JG.tax_rate_vat_trx_type_code VAT_CODE
918 -- , JGR.tax_calendar_period PERIOD_NAME
919 , glp.period_name PERIOD_NAME
920 , JG.tax_rate_code TAX_RATE_CODE
921 , JG.tax_rate_code_vat_trx_type_mng VAT_TYPE
922 , glp.period_year PERIOD_YEAR
923 , JG.account_flexfield GL_ACcOUNT
924 , JG.trx_line_class CLASS_CODE
925 , JG.ledger_id LEDGER_ID
926 , JGVRE.enable_report_sequence_flag ENABLE_REPORT_SEQUENCE_FLAG
927 , glp.period_num PERIOD_NUM
928 FROM jg_zz_vat_trx_details JG
929 , jg_zz_vat_rep_status JGR
930 , jg_zz_vat_rep_entities JGVRE
931 , gl_periods glp
932 WHERE JGR.vat_reporting_entity_id = P_VAT_REP_ENTITY_ID
933 AND JGR.mapping_vat_rep_entity_id = JGVRE.vat_reporting_entity_id
934 AND JG.reporting_status_id = JGR.reporting_status_id
935 AND JGR.tax_calendar_period = P_PERIOD
936 AND glp.period_set_name = JGR.tax_calendar_name
937 AND JG.tax_invoice_date between glp.start_date and glp.end_date
938 AND JG.tax_rate_vat_trx_type_code IS NOT NULL
939 AND (JG.tax_rate_vat_trx_type_code <> P_EX_VAT_TRX_TYPE OR P_EX_VAT_TRX_TYPE IS NULL)
940 AND (JG.tax_rate_vat_trx_type_code = P_VAT_TRX_TYPE OR P_VAT_TRX_TYPE IS NULL)
941 AND JG.tax_rate_register_type_code = 'TAX'
942 AND JGR.source = 'AR'
943 /* UT CHANGE AND JG.extract_source_ledger = 'AR'
944 AND JG.tax_invoice_date BETWEEN JGR.period_start_date AND JGR.period_end_date */
945 ORDER BY VAT_CODE
946 ,PERIOD_YEAR DESC
947 ,PERIOD_NUM DESC
948 ,TRX_ID
949 ,JG.tax_invoice_date
950 ,JG.tax_rate_code;
951
952 -- Created a temp_cur cursor for implementing the reporting sequence number logic.
953
954 CURSOR temp_cur ( p_vat_rep_config_id IN NUMBER
955 , p_period IN VARCHAR2
956 , p_vat_trx_type IN VARCHAR2
957 , p_ex_vat_trx_type IN VARCHAR2)
958 IS
959 SELECT JG.tax_rate_vat_trx_type_code VAT_TRX_TYPE_CODE,
960 JG.trx_id TRX_ID
961 FROM jg_zz_vat_trx_details JG
962 , jg_zz_vat_rep_status JGR
963 , jg_zz_vat_rep_entities JGVRE
964 , gl_periods glp
965 WHERE JGR.vat_reporting_entity_id = P_VAT_REP_ENTITY_ID
966 AND JGR.mapping_vat_rep_entity_id = JGVRE.vat_reporting_entity_id
967 AND JG.reporting_status_id = JGR.reporting_status_id
968 AND JGR.tax_calendar_period = P_PERIOD
969 AND glp.period_set_name = JGR.tax_calendar_name
970 AND JG.tax_invoice_date between glp.start_date and glp.end_date
971 AND JG.tax_rate_vat_trx_type_code IS NOT NULL
972 AND (JG.tax_rate_vat_trx_type_code <> P_EX_VAT_TRX_TYPE OR P_EX_VAT_TRX_TYPE IS NULL)
973 AND (JG.tax_rate_vat_trx_type_code = P_VAT_TRX_TYPE OR P_VAT_TRX_TYPE IS NULL)
974 AND JG.tax_rate_register_type_code = 'TAX'
975 AND JGR.source = 'AR'
976 /* UT CHANGE AND JG.extract_source_ledger = 'AR'
977 AND JG.tax_invoice_date BETWEEN JGR.period_start_date AND JGR.period_end_date */
978 ORDER BY VAT_TRX_TYPE_CODE
979 ,PERIOD_YEAR DESC
980 ,PERIOD_NUM DESC
981 ,JG.tax_rate_code
982 ,JG.tax_invoice_date;
983
984
985 -- Generic Cursor
986 CURSOR C_TRX_DTLS( p_vat_rep_config_id IN NUMBER
987 , p_period IN VARCHAR2 )
988 IS
989 SELECT JG.doc_seq_value SEQ_NUM
990 , JG.tax_rate TAX_RATE
991 , (NVL(JG.tax_amt,0)
992 + NVL(JG.taxable_amt,0)) TRX_AMOUNT
993 , (NVL(JG.tax_amt_funcl_curr,0)
994 + NVL(JG.taxable_amt_funcl_curr,0)) FUNC_AMOUNT
995 , NVL(JG.taxable_amt_funcl_curr,0) TAXABLE_AMOUNT
996 , NVL(JG.tax_amt_funcl_curr,0) TAX_AMOUNT
997 , JG.tax_invoice_date TAX_DATE
998 , JG.trx_date INVOICE_DATE
999 , JG.accounting_date GL_DATE
1000 , DECODE(JG.trx_line_class
1001 ,'ADJUSTMENT',JG.applied_to_trx_number
1002 , JG.trx_number) INVOICE_NUMBER
1003 , JG.billing_tp_name CUST_NAME
1004 , JG.billing_tp_tax_reg_num TAX_REG_NUM
1005 , JG.trx_currency_code CURR
1006 , JG.tax_rate_code TAX_CODE
1007 , JG.tax_rate_code_name TAX_DESC
1008 , JG.tax_rate_vat_trx_type_desc VAT_DESC
1009 , JG.tax_rate_vat_trx_type_code VAT_TYPE
1010 , JGR.tax_calendar_period PERIOD_NAME
1011 , JG.tax_rate_code_vat_trx_type_mng VAT_CODE
1012 , JGR.tax_calendar_year PERIOD_YEAR
1013 , JG.account_flexfield GL_ACcOUNT
1014 , JG.trx_line_class CLASS_CODE
1015 , JG.trx_number RECEIPT_NUMBER
1016 , JG.ar_cash_receipt_reverse_date REV_DATE
1017 , JG.ar_cash_receipt_reverse_status STATUS
1018 FROM jg_zz_vat_trx_details JG
1019 , jg_zz_vat_rep_status JGR
1020 WHERE JGR.vat_reporting_entity_id = P_VAT_REP_ENTITY_ID
1021 AND JG.reporting_status_id = JGR.reporting_status_id
1022 AND JGR.tax_calendar_period = P_PERIOD
1023 AND JG.tax_rate_register_type_code = 'TAX'
1024 AND JGR.source = 'AR'
1025 /* UT CHANGE AND JG.extract_source_ledger = 'AR'
1026 AND JG.tax_invoice_date BETWEEN JGR.period_start_date AND JGR.period_end_date */
1027 ;
1028
1029 CURSOR lcu_euar_data_count ( p_ex_vat_trx_type IN VARCHAR2)
1030 IS
1031 SELECT COUNT(1) C_NO_DATA_COUNT
1032 FROM jg_zz_vat_trx_gt
1033 WHERE jg_info_v14 <> P_EX_VAT_TRX_TYPE
1034 OR P_EX_VAT_TRX_TYPE IS NULL
1035 ;
1036
1037 CURSOR lcu_get_acc_method
1038 IS
1039 SELECT 1
1040 FROM ar_system_parameters
1041 WHERE accounting_method = 'CASH';
1042
1043 CURSOR get_entity_identifier(p_vat_rep_entity_id number)
1044 IS
1045 SELECT entity_identifier
1046 FROM jg_zz_vat_rep_entities
1047 WHERE vat_reporting_entity_id=p_vat_rep_entity_id;
1048
1049
1050 l_tax_document_date DATE;
1051 l_reporting_mode VARCHAR2(240);
1052 l_func_curr_code VARCHAR2(240);
1053 l_taxpayer_id VARCHAR2 (100);
1054 l_period_year NUMBER;
1055 l_rep_legal_entity VARCHAR2(240);
1056 l_trx_num VARCHAR2(240);
1057 l_rep_legal_entity_id NUMBER;
1058 l_period_start_date DATE;
1059 l_period_end_date DATE;
1060 l_rd NUMBER;
1061 l_new_r_tax NUMBER;
1062 l_new_amount NUMBER;
1063 l_new_rev_tax NUMBER;
1064 l_new_rev_amount NUMBER;
1065 l_new_receipt VARCHAR2(10);
1066 l_new_reversal VARCHAR2(10);
1067 l_vat VARCHAR2(20);
1068 l_dummy VARCHAR2(1);
1069 l_new_au NUMBER;
1070 l_new_aa NUMBER;
1071 l_new_application VARCHAR2(10);
1072 l_app_vat NUMBER;
1073 l_new_app_vat NUMBER;
1074 l_unapp_vat NUMBER;
1075 l_new_unapp_vat NUMBER;
1076 l_zero NUMBER;
1077 l_company_name xle_registrations.registered_name%TYPE;
1078 l_registration_number xle_registrations.registration_number%TYPE;
1079 l_country hz_locations.country%TYPE;
1080 l_address1 hz_locations.address1%TYPE;
1081 l_address2 hz_locations.address2%TYPE;
1082 l_address3 hz_locations.address3%TYPE;
1083 l_address4 hz_locations.address4%TYPE;
1084 l_city hz_locations.city%TYPE;
1085 l_postal_code hz_locations.postal_code%TYPE;
1086 l_contact hz_parties.party_name%TYPE;
1087 l_phone_number hz_contact_points.phone_number%TYPE;
1088 -- Added for Glob-006 ER
1089 l_province VARCHAR2(120);
1090 l_comm_num VARCHAR2(30);
1091 l_vat_reg_num VARCHAR2(50);
1092 -- end here
1093 l_entity_identifier VARCHAR2(600);
1094 l_tax_rate_code VARCHAR2(30);
1095 LOG_MESSAGE VARCHAR2(240);
1096 INVALID_ENTRY EXCEPTION;
1097 errbuf VARCHAR2(1000);
1098 l_functcurr VARCHAR2(15);
1099 l_coaid NUMBER;
1100 l_ledger_name VARCHAR2(30);
1101 INVALID_LEDGER EXCEPTION;
1102 l_receipt_application number;
1103 v_count NUMBER :=0;
1104 v_prev_vat_code VARCHAR2(240) :='';
1105 v_is_seq_updated VARCHAR2(1) := 'N';
1106 v_enable_report_sequence_flag VARCHAR2(1) := 'N';
1107 BEGIN
1108 if gv_debug then FND_FILE.PUT_LINE(FND_FILE.LOG,'funct_curr_legal'); end if;
1109 jg_zz_common_pkg.funct_curr_legal( x_func_curr_code => l_func_curr_code
1110 , x_rep_entity_name => l_rep_legal_entity
1111 , x_legal_entity_id => l_rep_legal_entity_id
1112 , x_taxpayer_id => l_taxpayer_id
1113 , pn_vat_rep_entity_id => p_vat_rep_entity_id
1114 , pv_period_name => p_period
1115 , pn_period_year => l_period_year
1116 );
1117
1118 if gv_debug then FND_FILE.PUT_LINE(FND_FILE.LOG,'2'); end if;
1119
1120 jg_zz_common_pkg.tax_registration(x_tax_registration => l_trx_num
1121 , x_period_start_date => l_period_start_date
1122 , x_period_end_date => l_period_end_date
1123 , x_status => l_reporting_mode
1124 , pn_vat_rep_entity_id => p_vat_rep_entity_id
1125 , pv_period_name => p_period
1126 , pv_source => 'AR'
1127 );
1128 l_reporting_mode := jg_zz_vat_rep_utility.get_period_status
1129 (
1130 pn_vat_reporting_entity_id => p_vat_rep_entity_id,
1131 pv_tax_calendar_period => p_period,
1132 pv_tax_calendar_year => NULL,
1133 pv_source => NULL,
1134 pv_report_name => P_CALLINGREPORT
1135 );
1136
1137 if gv_debug then FND_FILE.PUT_LINE(FND_FILE.LOG,'3'); end if;
1138
1139 /* jg_zz_common_pkg.company_detail(l_company_name
1140 ,l_registration_number
1141 ,l_country
1142 ,l_address1
1143 ,l_address2
1144 ,l_address3
1145 ,l_address4
1146 ,l_city
1147 ,l_postal_code
1148 ,l_contact
1149 ,l_phone_number
1150 ,nvl(l_rep_legal_entity_id,p_legal_entity_id) -- for German On Account Report - l_rep_legal_entity_id is null, p_legal_entity_id is the direct parameter to the report.
1151 );
1152 */
1153
1154 JG_ZZ_COMMON_PKG.company_detail(x_company_name => l_company_name
1155 ,x_registration_number =>l_registration_number
1156 ,x_country => l_country
1157 ,x_address1 => l_address1
1158 ,x_address2 => l_address2
1159 ,x_address3 => l_address3
1160 ,x_address4 => l_address4
1161 ,x_city => l_city
1162 ,x_postal_code => l_postal_code
1163 ,x_contact => l_contact
1164 ,x_phone_number => l_phone_number
1165 ,x_province => l_province
1166 ,x_comm_number => l_comm_num
1167 ,x_vat_reg_num => l_vat_reg_num
1168 ,pn_legal_entity_id => nvl(l_rep_legal_entity_id,p_legal_entity_id)
1169 ,p_vat_reporting_entity_id => p_vat_rep_entity_id);
1170
1171 /* Get ENTITY_IDENTIFIER */
1172
1173 OPEN get_entity_identifier(p_vat_rep_entity_id);
1174 FETCH get_entity_identifier INTO l_entity_identifier;
1175 CLOSE get_entity_identifier;
1176
1177 /* Get Tax Rate Code.
1178 The value for German On Account Reciept Report */
1179
1180 OPEN get_tax_rate_code(p_tax_rate_id);
1181 FETCH get_tax_rate_code INTO l_tax_rate_code;
1182 CLOSE get_tax_rate_code;
1183
1184
1185
1186 if gv_debug then FND_FILE.PUT_LINE(FND_FILE.LOG,'INSERT INTO GLOBAL'); end if;
1187 InsertIntoGlobal(
1188 p_jg_info_v20 => l_entity_identifier -- entity_identifier
1189 ,p_jg_info_v21 => l_func_curr_code -- curr_code
1190 ,p_jg_info_v22 => l_company_name -- l_rep_legal_entity -- entity_name
1191 ,p_jg_info_v23 => l_registration_number -- l_taxpayer_id -- taxpayer_id
1192 ,p_jg_info_v24 => l_company_name -- company_name
1193 ,p_jg_info_v25 => l_trx_num -- registration_number
1194 ,p_jg_info_v26 => l_country -- country
1195 ,p_jg_info_v27 => l_address1 -- address1
1196 ,p_jg_info_v28 => l_address2 -- address2
1197 ,p_jg_info_v29 => l_address3 -- address3
1198 ,p_jg_info_v30 => l_address4 -- address4
1199 ,p_jg_info_v31 => l_city -- city
1200 ,p_jg_info_v32 => l_postal_code -- postal_code
1201 ,p_jg_info_v33 => l_contact -- contact
1202 ,p_jg_info_v34 => l_phone_number -- phone_number
1203 ,p_jg_info_v35 => l_reporting_mode -- reporting mode
1204 ,p_jg_info_v37 => l_trx_num -- trx_num
1205 ,p_jg_info_d4 => l_period_start_date -- period_start_date
1206 ,p_jg_info_d5 => l_period_end_date -- period_end_date
1207 ,p_jg_info_n26 => l_rep_legal_entity_id -- legalentity_id
1208 ,p_jg_info_n27 => l_period_year -- period_year
1209 ,p_jg_info_v19 => l_registration_number -- company tax Payer Id
1210 ,p_jg_info_v18 => l_tax_rate_code -- tax rate code
1211 ,p_jg_info_v36 => 'H' -- Header record indicator
1212 );
1213
1214
1215
1216 if gv_debug then FND_FILE.PUT_LINE(FND_FILE.LOG,'5'); end if;
1217
1218 IF P_CALLINGREPORT = 'JEDEDVOR' THEN
1219
1220 /* validating the parameters */
1221
1222 IF ( p_reporting_level = 'LE' AND p_legal_entity_id IS NULL) THEN
1223
1224 fnd_message.set_name('JG', 'JG_ZZ_VAT_INVALID_ENTITY');
1225 fnd_message.set_token('PARAMETER', 'Legal Entity');
1226 fnd_message.set_token('LEVEL',p_reporting_level);
1227 LOG_MESSAGE := fnd_message.get;
1228
1229 RAISE INVALID_ENTRY;
1230
1231 ELSIF ( p_reporting_level = 'LEDGER' AND p_ledger_id IS NULL) THEN
1232
1233 fnd_message.set_name('JG', 'JG_ZZ_VAT_INVALID_ENTITY');
1234 fnd_message.set_token('PARAMETER', 'Ledger');
1235 fnd_message.set_token('LEVEL',p_reporting_level);
1236 LOG_MESSAGE := fnd_message.get;
1237
1238 RAISE INVALID_ENTRY;
1239 ELSIF ( p_reporting_level = 'BSV' AND p_company IS NULL) THEN
1240
1241 fnd_message.set_name('JG', 'JG_ZZ_VAT_INVALID_ENTITY');
1242 fnd_message.set_token('PARAMETER', 'Balancing Segment');
1243 fnd_message.set_token('LEVEL',p_reporting_level);
1244 LOG_MESSAGE := fnd_message.get;
1245
1246 RAISE INVALID_ENTRY;
1247 END IF;
1248
1249 BEGIN
1250 GL_INFO.gl_get_ledger_info(P_LEDGER_ID,l_coaid,l_ledger_name,l_functcurr,errbuf);
1251
1252 IF errbuf IS NOT NULL THEN
1253 RAISE INVALID_LEDGER;
1254 END IF;
1255
1256 EXCEPTION
1257 WHEN INVALID_LEDGER THEN
1258 fnd_file.put_line(fnd_file.log,errbuf);
1259 END;
1260
1261 FOR r_ger_receipt IN lcu_ger_receipt ( p_vat_rep_entity_id,
1262 p_period,
1263 p_legal_entity_id,
1264 p_ledger_id,
1265 l_coaid,
1266 p_company,
1267 p_tax_rate_id,
1268 p_reporting_level,
1269 p_gl_period_start_date,
1270 p_gl_period_end_date
1271 )
1272 LOOP
1273
1274 if gv_debug then FND_FILE.PUT_LINE(FND_FILE.LOG,'6'); end if;
1275
1276 l_rd := rdformula(r_ger_receipt.rev_date,p_gl_period_start_date,p_gl_period_end_date);
1277
1278 l_new_r_tax := new_r_taxformula(r_ger_receipt.r_date , r_ger_receipt.r_tax ,p_gl_period_start_date,p_gl_period_end_date);
1279
1280 l_new_amount := new_amountformula(r_ger_receipt.r_date , r_ger_receipt.amount,p_gl_period_start_date,p_gl_period_end_date);
1281
1282 l_new_rev_tax := new_rev_taxformula(l_rd , r_ger_receipt.rev_tax );
1283
1284 l_new_rev_amount := new_rev_amountformula(l_rd, r_ger_receipt.rev_amount );
1285
1286 l_new_receipt := new_receiptformula(r_ger_receipt.r_date,p_gl_period_start_date,p_gl_period_end_date);
1287
1288 l_new_reversal := new_reversalformula(r_ger_receipt.rev_date,p_gl_period_start_date,p_gl_period_end_date);
1289
1290 l_zero := zeroformula;
1291 if gv_debug then
1292 FND_FILE.PUT_LINE(FND_FILE.LOG,'7. CrId:'||r_ger_receipt.cash_receipt_id);
1293 end if;
1294
1295 l_receipt_application := 0;
1296
1297 FOR r_ger_appl IN lcu_ger_appl (r_ger_receipt.cash_receipt_id)
1298 LOOP
1299 if gv_debug then FND_FILE.PUT_LINE(FND_FILE.LOG,'in 2nd loop'); end if;
1300
1301 l_new_au := new_auformula(r_ger_appl.a_date , r_ger_appl.applied , r_ger_appl.amount_applied,p_gl_period_start_date,p_gl_period_end_date );
1302
1303 l_new_aa := new_aaformula(r_ger_appl.a_date , r_ger_appl.applied , r_ger_appl.amount_applied,p_gl_period_start_date,p_gl_period_end_date);
1304
1305 l_new_application := new_applicationformula(r_ger_appl.a_date,p_gl_period_start_date,p_gl_period_end_date);
1306
1307 l_app_vat := app_vatformula(r_ger_appl.applied , r_ger_appl.tax_rate_id , r_ger_appl.a_date , r_ger_appl.amount_applied , r_ger_receipt.amount);
1308
1309 l_new_app_vat := new_app_vatformula(r_ger_appl.a_date, l_app_vat,p_gl_period_start_date,p_gl_period_end_date);
1310
1311 l_unapp_vat := unapp_vatformula(r_ger_appl.applied , r_ger_appl.tax_rate_id , r_ger_appl.a_date , r_ger_appl.amount_applied , r_ger_receipt.amount);
1312
1313 l_new_unapp_vat := new_unapp_vatformula(r_ger_appl.a_date , l_unapp_vat,p_gl_period_start_date,p_gl_period_end_date );
1314
1315 l_vat := vatformula(r_ger_appl.applied, l_app_vat, l_unapp_vat);
1316 if gv_debug then FND_FILE.PUT_LINE(FND_FILE.LOG,'insert into global'); end if;
1317 InsertIntoGlobal(
1318 p_jg_info_v1 => r_ger_receipt.receipt
1319 , p_jg_info_v2 => r_ger_receipt.status
1320 , p_jg_info_v3 => r_ger_receipt.tax_code
1321 , p_jg_info_v4 => r_ger_receipt.currenct_code
1322 , p_jg_info_v10 => r_ger_receipt.receipt_number
1323 , p_jg_info_d1 => r_ger_receipt.rev_date
1324 , p_jg_info_d2 => r_ger_receipt.r_date
1325 , p_jg_info_n1 => r_ger_receipt.amount
1326 , p_jg_info_n2 => l_zero
1327 , p_jg_info_n3 => r_ger_receipt.rev_amount
1328 , p_jg_info_n4 => r_ger_receipt.r_tax
1329 , p_jg_info_n5 => r_ger_receipt.rev_tax
1330 , p_jg_info_n6 => r_ger_receipt.cash_receipt_id
1331 , p_jg_info_n11 => l_rd
1332 , p_jg_info_n14 => l_new_r_tax
1333 , p_jg_info_n15 => l_new_amount
1334 , p_jg_info_n16 => l_new_rev_tax
1335 , p_jg_info_n17 => l_new_rev_amount
1336 -- , p_jg_info_n18 => l_new_receipt
1337 -- , p_jg_info_n19 => l_new_reversal
1338 , p_jg_info_v15 => l_new_receipt /* modified during UT TEST */
1339 , p_jg_info_v16 => l_new_reversal /* modified during UT TEST */
1340 , p_jg_info_v5 => r_ger_appl.applied
1341 , p_jg_info_v6 => r_ger_appl.invoice_currency_code
1342 , p_jg_info_v7 => r_ger_appl.tax_code
1343 , p_jg_info_v8 => r_ger_appl.customer_name
1344 , p_jg_info_v9 => r_ger_appl.location
1345 , p_jg_info_v13 => l_vat
1346 , p_jg_info_v14 => l_new_application
1347 , p_jg_info_n7 => r_ger_appl.receivable_application_id
1348 , p_jg_info_n8 => r_ger_appl.applied_customer_trx_id
1349 , p_jg_info_n9 => r_ger_appl.amount_applied
1350 , p_jg_info_n20 => l_new_au
1351 , p_jg_info_n21 => l_new_aa
1352 , p_jg_info_n22 => l_app_vat
1353 , p_jg_info_n23 => l_new_app_vat
1354 , p_jg_info_n24 => l_unapp_vat
1355 , p_jg_info_n25 => l_new_unapp_vat
1356 , p_jg_info_d3 => r_ger_appl.a_date
1357 , p_jg_info_v30 => 'JEDEDVOR'
1358 );
1359 l_receipt_application := 1;
1360 END LOOP;
1361
1362 /* Will insert the Onc-Account receipt details,which is not applied */
1363
1364 IF l_receipt_application = 0 THEN
1365
1366 InsertIntoGlobal(
1367 p_jg_info_v1 => r_ger_receipt.receipt
1368 , p_jg_info_v2 => r_ger_receipt.status
1369 , p_jg_info_v3 => r_ger_receipt.tax_code
1370 , p_jg_info_v4 => r_ger_receipt.currenct_code
1371 , p_jg_info_v10 => r_ger_receipt.receipt_number
1372 , p_jg_info_d1 => r_ger_receipt.rev_date
1373 , p_jg_info_d2 => r_ger_receipt.r_date
1374 , p_jg_info_n1 => r_ger_receipt.amount
1375 , p_jg_info_n2 => l_zero
1376 , p_jg_info_n3 => r_ger_receipt.rev_amount
1377 , p_jg_info_n4 => r_ger_receipt.r_tax
1378 , p_jg_info_n5 => r_ger_receipt.rev_tax
1379 , p_jg_info_n6 => r_ger_receipt.cash_receipt_id
1380 , p_jg_info_n11 => l_rd
1381 , p_jg_info_n14 => l_new_r_tax
1382 , p_jg_info_n15 => l_new_amount
1383 , p_jg_info_n16 => l_new_rev_tax
1384 , p_jg_info_n17 => l_new_rev_amount
1385 -- , p_jg_info_n18 => l_new_receipt
1386 -- , p_jg_info_n19 => l_new_reversal
1387 , p_jg_info_v15 => l_new_receipt /* modified during UT TEST */
1388 , p_jg_info_v16 => l_new_reversal /* modified during UT TEST */
1389 , p_jg_info_v5 => NULL
1390 , p_jg_info_v6 => NULL
1391 , p_jg_info_v7 => NULL
1392 , p_jg_info_v8 => NULL
1393 , p_jg_info_v9 => NULL
1394 , p_jg_info_v13 => NULL
1395 , p_jg_info_v14 => NULL
1396 , p_jg_info_n7 => NULL
1397 , p_jg_info_n8 => NULL
1398 , p_jg_info_n9 => NULL
1399 , p_jg_info_n20 => NULL
1400 , p_jg_info_n21 => NULL
1401 , p_jg_info_n22 => NULL
1402 , p_jg_info_n23 => NULL
1403 , p_jg_info_n24 => NULL
1404 , p_jg_info_n25 => NULL
1405 , p_jg_info_d3 => NULL
1406 , p_jg_info_v30 => 'JEDEDVOR'
1407 );
1408 END IF;
1409 END LOOP;
1410 ELSIF P_CALLINGREPORT = 'JGZZARVR' THEN
1411
1412 BEGIN
1413
1414 if gv_debug then FND_FILE.PUT_LINE(FND_FILE.LOG,'JGZZARVR - 1'); end if;
1415 FOR r_euar IN lcu_euar_vatreg (p_vat_rep_entity_id,p_period,p_vat_trx_type, p_ex_vat_trx_type)
1416 LOOP
1417
1418 if gv_debug then FND_FILE.PUT_LINE(FND_FILE.LOG,'JGZZARVR - 2'); end if;
1419
1420 FND_FILE.PUT_LINE(FND_FILE.LOG,'v_count - '||v_count);
1421 FND_FILE.PUT_LINE(FND_FILE.LOG,'r_euar.vat_code - '||r_euar.vat_code);
1422 FND_FILE.PUT_LINE(FND_FILE.LOG,'v_prev_vat_code - '||v_prev_vat_code);
1423
1424
1425 InsertIntoGlobal(
1426 p_jg_info_n1 => r_euar.seq_num
1427 , p_jg_info_n2 => r_euar.tax_rate
1428 , p_jg_info_n3 => r_euar.trx_amount
1429 , p_jg_info_n4 => r_euar.func_amount
1430 , p_jg_info_n5 => r_euar.taxable_amount
1431 , p_jg_info_n6 => r_euar.tax_amount
1432 , p_jg_info_n7 => r_euar.period_year
1433 , p_jg_info_n8 => r_euar.ledger_id
1434 , p_jg_info_n9 => r_euar.period_num
1435 , p_jg_info_d1 => r_euar.tax_date
1436 , p_jg_info_d2 => r_euar.invoice_date
1437 , p_jg_info_d3 => r_euar.gl_date
1438 , p_jg_info_v1 => r_euar.invoice_number
1439 , p_jg_info_n25 => r_euar.trx_id
1440 , p_jg_info_v2 => r_euar.cust_name
1441 , p_jg_info_v3 => r_euar.tax_reg_num
1442 , p_jg_info_v4 => r_euar.curr
1443 , p_jg_info_v5 => r_euar.tax_code
1444 , p_jg_info_v6 => r_euar.vat_code
1445 , p_jg_info_v7 => r_euar.tax_desc
1446 , p_jg_info_v8 => r_euar.vat_desc
1447 , p_jg_info_v9 => r_euar.period_name
1448 , p_jg_info_v10 => r_euar.gl_account
1449 , p_jg_info_v14 => r_euar.vat_type
1450 , p_jg_info_v15 => r_euar.enable_report_sequence_flag
1451 , p_jg_info_v17 => r_euar.tax_rate_code
1452 , p_jg_info_v18 => r_euar.class_code
1453 );
1454 v_enable_report_sequence_flag := r_euar.enable_report_sequence_flag;
1455 END LOOP;
1456
1457 if gv_debug then FND_FILE.PUT_LINE(FND_FILE.LOG,'JGZZARVR - 3'); end if;
1458 FOR r_euar_data_count IN lcu_euar_data_count ( p_ex_vat_trx_type)
1459 LOOP
1460 UPDATE jg_zz_vat_trx_gt
1461 SET jg_info_n20 = r_euar_data_count.c_no_data_count;
1462 END LOOP;
1463 if gv_debug then FND_FILE.PUT_LINE(FND_FILE.LOG,'JGZZARVR - 4'); end if;
1464
1465 -- Implement the report level seq number --
1466
1467 IF v_enable_report_sequence_flag = 'Y' THEN
1468
1469 FOR r_seq_impl IN temp_cur(p_vat_rep_entity_id,p_period,p_vat_trx_type, p_ex_vat_trx_type)
1470 LOOP
1471
1472 IF r_seq_impl.VAT_TRX_TYPE_CODE <> v_prev_vat_code or r_seq_impl.VAT_TRX_TYPE_CODE IS NULL THEN
1473 v_count := 0;
1474 END IF;
1475
1476 SELECT distinct JG_INFO_V40 INTO v_is_seq_updated FROM JG_ZZ_VAT_TRX_GT T1
1477 WHERE T1.jg_info_n25 = r_seq_impl.trx_id
1478 AND T1.jg_info_v6 = r_seq_impl.VAT_TRX_TYPE_CODE;
1479
1480
1481 IF nvl(v_is_seq_updated,'N') <> 'Y' THEN
1482
1483 v_count := v_count+1;
1484
1485 UPDATE JG_ZZ_VAT_TRX_GT SET jg_info_n1 = v_count ,
1486 jg_info_v40 = 'Y'
1487 WHERE jg_info_n25 = r_seq_impl.trx_id
1488 AND jg_info_v6 = r_seq_impl.VAT_TRX_TYPE_CODE;
1489
1490 END IF;
1491
1492 v_prev_vat_code := r_seq_impl.VAT_TRX_TYPE_CODE;
1493
1494 END LOOP;
1495
1496 END IF;
1497
1498 -- End of Implement the report level seq number --
1499
1500 END;
1501
1502 ELSIF P_CALLINGREPORT = 'JEILARDR' THEN
1503 /*
1504 || Israeli VAT AR Detailed Report
1505 || ELSIF added by Ramananda
1506 || Logic for this report is handled in the DataTemplate.
1507 */
1508 NULL;
1509
1510 ELSIF P_CALLINGREPORT = 'JEHRCITR' THEN
1511 /*
1512 || Customer Invoice Tax Report, Croatia
1513 || ELSIF added by Ramananda
1514 || Logic for this report is handled in the DataTemplate.
1515 */
1516 NULL;
1517
1518 ELSIF P_CALLINGREPORT = 'SUMMARY-AR' OR P_CALLINGREPORT IS NULL THEN
1519 -- Call Generic Cursor C_TRX_DTLS
1520 NULL;
1521 END IF;
1522
1523 IF l_reporting_mode = 'PRELIMINARY' OR l_reporting_mode = 'FINAL' THEN
1524
1525 -- Get accounting method --
1526 OPEN lcu_get_acc_method;
1527 FETCH lcu_get_acc_method
1528 INTO l_dummy;
1529 IF lcu_get_acc_method%FOUND THEN
1530 CLOSE lcu_get_acc_method;
1531
1532 /* The tax_date_maintenance_program procedure not suppose to call here.
1533 We should call this procedure before selection process ran i.e. Before the TRL call (as in 11i)
1534 Hence commenting the call to procedure.
1535 */
1536 -- tax_date_maintenance_program(l_period_end_date);
1537 ELSE
1538 CLOSE lcu_get_acc_method;
1539 END IF;
1540 END IF;
1541
1542 RETURN (TRUE);
1543 EXCEPTION
1544 WHEN INVALID_ENTRY THEN
1545 fnd_file.put_line(fnd_file.log,LOG_MESSAGE);
1546 raise;
1547 RETURN (FALSE);
1548 WHEN OTHERS THEN
1549 FND_FILE.PUT_LINE(FND_FILE.LOG,'Unexpected error in JG_ZZ_SUMMARY_AR_PKG.beforeReport. Error-' ||SUBSTR(SQLERRM,1,200));
1550 raise;
1551 RETURN (FALSE);
1552 END beforeReport;
1553
1554 END JG_ZZ_SUMMARY_AR_PKG;