[Home] [Help]
PACKAGE BODY: APPS.JG_ZZ_SUMMARY_AR_PKG
Source
1 PACKAGE BODY JG_ZZ_SUMMARY_AR_PKG
2 -- $Header: jgzzsummaryarb.pls 120.20.12020000.2 2012/07/18 07:50:04 rahulkum noship $
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-RRRR'))
34 AND p_a_date <= NVL(ZRB.effective_to, TO_DATE('31-12-2195', 'DD-MM-RRRR')) */
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-RRRR'))
68 AND NVL(P_PERIOD_END_DATE,TO_DATE('31-12-2690','DD-MM-RRRR'));
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-RRRR')) AND NVL(p_period_end_date,TO_DATE('31-12-4000','DD-MM-RRRR'));
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-RRRR')) AND NVL(p_period_end_date,TO_DATE('31-12-2690','DD-MM-RRRR')) 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-RRRR')) AND NVL(p_period_end_date, to_date('31-12-4000','DD-MM-RRRR')) 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-RRRR')) AND NVL(p_period_end_date,TO_DATE('31-12-4000','DD-MM-RRRR')) 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-RRRR'))
200 AND p_a_date <= NVL(ZRB.effective_to, TO_DATE('31-12-2195', 'DD-MM-RRRR'));
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-RRRR')) AND NVL(p_period_end_date,TO_DATE('31-12-4000','DD-MM-RRRR'))
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-RRRR')) AND NVL(p_period_end_date,TO_DATE('31-12-4000','DD-MM-RRRR'))
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-RRRR')) AND NVL(p_period_end_date,TO_DATE('30-12-2199','DD-MM-RRRR'));
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-RRRR')) AND NVl(p_period_end_date,TO_DATE('31-12-2690','DD-MM-RRRR'));
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-RRRR')) AND NVL(p_period_end_date,TO_DATE('31-12-2690','DD-MM-RRRR')) 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-RRRR'),TO_DATE('01-01-1890' ,'DD-MM-RRRR'))
822 AND NVL(TO_DATE(P_GL_PERIOD_END_DATE,'DD-MM-RRRR'),TO_DATE('30-12-2099','DD-MM-RRRR'))
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-RRRR'),TO_DATE('01-01-1890','DD-MM-RRRR'))
831 AND nvl(TO_DATE(P_GL_PERIOD_END_DATE,'DD-MM-RRRR'), TO_DATE('30-12-2199','DD-MM-RRRR'))
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-RRRR'),
879 to_date('31-12-4000','DD-MM-RRRR'))
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 , sum((NVL(JG.tax_amt,0)
898 + NVL(JG.taxable_amt,0))) TRX_AMOUNT --bug 12325571
899 /* UT TEST change nvl(xxxamt__funcl_curr,0) to nvl(xxx_func_curr, xxx_amt) */
900 , sum((NVL(JG.tax_amt_funcl_curr, tax_amt)
901 + NVL(JG.taxable_amt_funcl_curr, taxable_amt))) FUNC_AMOUNT --bug 12325571
902 , sum(NVL(JG.taxable_amt_funcl_curr, taxable_amt)) TAXABLE_AMOUNT --bug 12325571
903 , sum(NVL(JG.tax_amt_funcl_curr, tax_amt)) TAX_AMOUNT --bug 12325571
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 glp.adjustment_period_flag = 'N' -- bug 14015901
938 AND JG.tax_invoice_date between glp.start_date and glp.end_date
939 AND JG.tax_rate_vat_trx_type_code IS NOT NULL
940 AND (JG.tax_rate_vat_trx_type_code <> P_EX_VAT_TRX_TYPE OR P_EX_VAT_TRX_TYPE IS NULL)
941 AND (JG.tax_rate_vat_trx_type_code = P_VAT_TRX_TYPE OR P_VAT_TRX_TYPE IS NULL)
942 AND JG.tax_rate_register_type_code = 'TAX'
943 AND JGR.source = 'AR'
944 /* UT CHANGE AND JG.extract_source_ledger = 'AR'
945 AND JG.tax_invoice_date BETWEEN JGR.period_start_date AND JGR.period_end_date */
946 -- added group by for bug 12325571
947 group by JG.doc_seq_value
948 , JG.tax_rate
949 , JG.tax_invoice_date
950 , JG.trx_date
951 , JG.accounting_date
952 , DECODE(JG.trx_line_class
953 ,'ADJUSTMENT',JG.applied_to_trx_number
954 , JG.trx_number)
955 , JG.trx_id
956 , JG.billing_tp_name
957 , JG.billing_tp_tax_reg_num
958 , JG.trx_currency_code
959 , JG.tax_rate_code
960 , JG.tax_rate_code_name
961 , JG.tax_rate_vat_trx_type_desc
962 , JG.tax_rate_vat_trx_type_code
963 , glp.period_name
964 , JG.tax_rate_code
965 , JG.tax_rate_code_vat_trx_type_mng
966 , glp.period_year
967 , JG.account_flexfield
968 , JG.trx_line_class
969 , JG.ledger_id
970 , JGVRE.enable_report_sequence_flag
971 , glp.period_num
972 ORDER BY VAT_CODE
973 ,PERIOD_YEAR DESC
974 ,PERIOD_NUM DESC
975 ,TRX_ID
976 ,JG.tax_invoice_date
977 ,JG.tax_rate_code;
978
979 -- Created a temp_cur cursor for implementing the reporting sequence number logic.
980
981 CURSOR temp_cur ( p_vat_rep_config_id IN NUMBER
982 , p_period IN VARCHAR2
983 , p_vat_trx_type IN VARCHAR2
984 , p_ex_vat_trx_type IN VARCHAR2)
985 IS
986 SELECT JG.tax_rate_vat_trx_type_code VAT_TRX_TYPE_CODE,
987 JG.trx_id TRX_ID
988 FROM jg_zz_vat_trx_details JG
989 , jg_zz_vat_rep_status JGR
990 , jg_zz_vat_rep_entities JGVRE
991 , gl_periods glp
992 WHERE JGR.vat_reporting_entity_id = P_VAT_REP_ENTITY_ID
993 AND JGR.mapping_vat_rep_entity_id = JGVRE.vat_reporting_entity_id
994 AND JG.reporting_status_id = JGR.reporting_status_id
995 AND JGR.tax_calendar_period = P_PERIOD
996 AND glp.period_set_name = JGR.tax_calendar_name
997 AND glp.adjustment_period_flag = 'N' -- bug 14015901
998 AND JG.tax_invoice_date between glp.start_date and glp.end_date
999 AND JG.tax_rate_vat_trx_type_code IS NOT NULL
1000 AND (JG.tax_rate_vat_trx_type_code <> P_EX_VAT_TRX_TYPE OR P_EX_VAT_TRX_TYPE IS NULL)
1001 AND (JG.tax_rate_vat_trx_type_code = P_VAT_TRX_TYPE OR P_VAT_TRX_TYPE IS NULL)
1002 AND JG.tax_rate_register_type_code = 'TAX'
1003 AND JGR.source = 'AR'
1004 /* UT CHANGE AND JG.extract_source_ledger = 'AR'
1005 AND JG.tax_invoice_date BETWEEN JGR.period_start_date AND JGR.period_end_date */
1006 ORDER BY VAT_TRX_TYPE_CODE
1007 ,PERIOD_YEAR DESC
1008 ,PERIOD_NUM DESC
1009 ,JG.tax_rate_code
1010 ,JG.tax_invoice_date;
1011
1012
1013 -- Generic Cursor
1014 CURSOR C_TRX_DTLS( p_vat_rep_config_id IN NUMBER
1015 , p_period IN VARCHAR2 )
1016 IS
1017 SELECT JG.doc_seq_value SEQ_NUM
1018 , JG.tax_rate TAX_RATE
1019 , (NVL(JG.tax_amt,0)
1020 + NVL(JG.taxable_amt,0)) TRX_AMOUNT
1021 , (NVL(JG.tax_amt_funcl_curr,0)
1022 + NVL(JG.taxable_amt_funcl_curr,0)) FUNC_AMOUNT
1023 , NVL(JG.taxable_amt_funcl_curr,0) TAXABLE_AMOUNT
1024 , NVL(JG.tax_amt_funcl_curr,0) TAX_AMOUNT
1025 , JG.tax_invoice_date TAX_DATE
1026 , JG.trx_date INVOICE_DATE
1027 , JG.accounting_date GL_DATE
1028 , DECODE(JG.trx_line_class
1029 ,'ADJUSTMENT',JG.applied_to_trx_number
1030 , JG.trx_number) INVOICE_NUMBER
1031 , JG.billing_tp_name CUST_NAME
1032 , JG.billing_tp_tax_reg_num TAX_REG_NUM
1033 , JG.trx_currency_code CURR
1034 , JG.tax_rate_code TAX_CODE
1035 , JG.tax_rate_code_name TAX_DESC
1036 , JG.tax_rate_vat_trx_type_desc VAT_DESC
1037 , JG.tax_rate_vat_trx_type_code VAT_TYPE
1038 , JGR.tax_calendar_period PERIOD_NAME
1039 , JG.tax_rate_code_vat_trx_type_mng VAT_CODE
1040 , JGR.tax_calendar_year PERIOD_YEAR
1041 , JG.account_flexfield GL_ACcOUNT
1042 , JG.trx_line_class CLASS_CODE
1043 , JG.trx_number RECEIPT_NUMBER
1044 , JG.ar_cash_receipt_reverse_date REV_DATE
1045 , JG.ar_cash_receipt_reverse_status STATUS
1046 FROM jg_zz_vat_trx_details JG
1047 , jg_zz_vat_rep_status JGR
1048 WHERE JGR.vat_reporting_entity_id = P_VAT_REP_ENTITY_ID
1049 AND JG.reporting_status_id = JGR.reporting_status_id
1050 AND JGR.tax_calendar_period = P_PERIOD
1051 AND JG.tax_rate_register_type_code = 'TAX'
1052 AND JGR.source = 'AR'
1053 /* UT CHANGE AND JG.extract_source_ledger = 'AR'
1054 AND JG.tax_invoice_date BETWEEN JGR.period_start_date AND JGR.period_end_date */
1055 ;
1056
1057 CURSOR lcu_euar_data_count ( p_ex_vat_trx_type IN VARCHAR2)
1058 IS
1059 SELECT COUNT(1) C_NO_DATA_COUNT
1060 FROM jg_zz_vat_trx_gt
1061 WHERE jg_info_v14 <> P_EX_VAT_TRX_TYPE
1062 OR P_EX_VAT_TRX_TYPE IS NULL
1063 ;
1064
1065 CURSOR lcu_get_acc_method
1066 IS
1067 SELECT 1
1068 FROM ar_system_parameters
1069 WHERE accounting_method = 'CASH';
1070
1071 CURSOR get_entity_identifier(p_vat_rep_entity_id number)
1072 IS
1073 SELECT entity_identifier
1074 FROM jg_zz_vat_rep_entities
1075 WHERE vat_reporting_entity_id=p_vat_rep_entity_id;
1076
1077
1078 l_tax_document_date DATE;
1079 l_reporting_mode VARCHAR2(240);
1080 l_reporting_mode_mng VARCHAR2(150);
1081 l_func_curr_code VARCHAR2(240);
1082 l_taxpayer_id VARCHAR2 (100);
1083 l_period_year NUMBER;
1084 l_rep_legal_entity VARCHAR2(240);
1085 l_trx_num VARCHAR2(240);
1086 l_rep_legal_entity_id NUMBER;
1087 l_period_start_date DATE;
1088 l_period_end_date DATE;
1089 l_rd NUMBER;
1090 l_new_r_tax NUMBER;
1091 l_new_amount NUMBER;
1092 l_new_rev_tax NUMBER;
1093 l_new_rev_amount NUMBER;
1094 l_new_receipt VARCHAR2(10);
1095 l_new_reversal VARCHAR2(10);
1096 l_vat VARCHAR2(20);
1097 l_dummy VARCHAR2(1);
1098 l_new_au NUMBER;
1099 l_new_aa NUMBER;
1100 l_new_application VARCHAR2(10);
1101 l_app_vat NUMBER;
1102 l_new_app_vat NUMBER;
1103 l_unapp_vat NUMBER;
1104 l_new_unapp_vat NUMBER;
1105 l_zero NUMBER;
1106 l_company_name xle_registrations.registered_name%TYPE;
1107 l_registration_number xle_registrations.registration_number%TYPE;
1108 l_country hz_locations.country%TYPE;
1109 l_address1 hz_locations.address1%TYPE;
1110 l_address2 hz_locations.address2%TYPE;
1111 l_address3 hz_locations.address3%TYPE;
1112 l_address4 hz_locations.address4%TYPE;
1113 l_city hz_locations.city%TYPE;
1114 l_postal_code hz_locations.postal_code%TYPE;
1115 l_contact hz_parties.party_name%TYPE;
1116 l_phone_number hz_contact_points.phone_number%TYPE;
1117 -- Added for Glob-006 ER
1118 l_province VARCHAR2(120);
1119 l_comm_num VARCHAR2(30);
1120 l_vat_reg_num VARCHAR2(50);
1121 -- end here
1122 l_entity_identifier VARCHAR2(600);
1123 l_tax_rate_code VARCHAR2(30);
1124 LOG_MESSAGE VARCHAR2(240);
1125 INVALID_ENTRY EXCEPTION;
1126 errbuf VARCHAR2(1000);
1127 l_functcurr VARCHAR2(15);
1128 l_coaid NUMBER;
1129 l_ledger_name VARCHAR2(30);
1130 INVALID_LEDGER EXCEPTION;
1131 l_receipt_application number;
1132 v_count NUMBER :=0;
1133 v_prev_vat_code VARCHAR2(240) :='';
1134 v_is_seq_updated VARCHAR2(1) := 'N';
1135 v_enable_report_sequence_flag VARCHAR2(1) := 'N';
1136 l_precision NUMBER;
1137
1138 BEGIN
1139 if gv_debug then FND_FILE.PUT_LINE(FND_FILE.LOG,'funct_curr_legal'); end if;
1140 jg_zz_common_pkg.funct_curr_legal( x_func_curr_code => l_func_curr_code
1141 , x_rep_entity_name => l_rep_legal_entity
1142 , x_legal_entity_id => l_rep_legal_entity_id
1143 , x_taxpayer_id => l_taxpayer_id
1144 , pn_vat_rep_entity_id => p_vat_rep_entity_id
1145 , pv_period_name => p_period
1146 , pn_period_year => l_period_year
1147 );
1148
1149 if gv_debug then FND_FILE.PUT_LINE(FND_FILE.LOG,'2'); end if;
1150
1151 jg_zz_common_pkg.tax_registration(x_tax_registration => l_trx_num
1152 , x_period_start_date => l_period_start_date
1153 , x_period_end_date => l_period_end_date
1154 , x_status => l_reporting_mode
1155 , pn_vat_rep_entity_id => p_vat_rep_entity_id
1156 , pv_period_name => p_period
1157 , pv_source => 'AR'
1158 );
1159 l_reporting_mode := jg_zz_vat_rep_utility.get_period_status
1160 (
1161 pn_vat_reporting_entity_id => p_vat_rep_entity_id,
1162 pv_tax_calendar_period => p_period,
1163 pv_tax_calendar_year => NULL,
1164 pv_source => NULL,
1165 pv_report_name => P_CALLINGREPORT
1166 );
1167
1168 -- Bug 14024932
1169
1170 select meaning
1171 into l_reporting_mode_mng
1172 from fnd_lookups
1173 where lookup_type = 'JGZZ_REPORT_TYPE'
1174 and lookup_code = decode(l_reporting_mode, 'COPY', 'R','FINAL','F','P');
1175
1176 if gv_debug then FND_FILE.PUT_LINE(FND_FILE.LOG,'3'); end if;
1177
1178 /* jg_zz_common_pkg.company_detail(l_company_name
1179 ,l_registration_number
1180 ,l_country
1181 ,l_address1
1182 ,l_address2
1183 ,l_address3
1184 ,l_address4
1185 ,l_city
1186 ,l_postal_code
1187 ,l_contact
1188 ,l_phone_number
1189 ,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.
1190 );
1191 */
1192
1193 JG_ZZ_COMMON_PKG.company_detail(x_company_name => l_company_name
1194 ,x_registration_number =>l_registration_number
1195 ,x_country => l_country
1196 ,x_address1 => l_address1
1197 ,x_address2 => l_address2
1198 ,x_address3 => l_address3
1199 ,x_address4 => l_address4
1200 ,x_city => l_city
1201 ,x_postal_code => l_postal_code
1202 ,x_contact => l_contact
1203 ,x_phone_number => l_phone_number
1204 ,x_province => l_province
1205 ,x_comm_number => l_comm_num
1206 ,x_vat_reg_num => l_vat_reg_num
1207 ,pn_legal_entity_id => nvl(l_rep_legal_entity_id,p_legal_entity_id)
1208 ,p_vat_reporting_entity_id => p_vat_rep_entity_id);
1209
1210 /* Get ENTITY_IDENTIFIER */
1211
1212 OPEN get_entity_identifier(p_vat_rep_entity_id);
1213 FETCH get_entity_identifier INTO l_entity_identifier;
1214 CLOSE get_entity_identifier;
1215
1216 /* Get Tax Rate Code.
1217 The value for German On Account Reciept Report */
1218
1219 OPEN get_tax_rate_code(p_tax_rate_id);
1220 FETCH get_tax_rate_code INTO l_tax_rate_code;
1221 CLOSE get_tax_rate_code;
1222
1223 /* Get Currency Precision */
1224
1225 BEGIN
1226 FND_FILE.PUT_LINE(FND_FILE.LOG,'Functional Currency Code :'||l_func_curr_code);
1227
1228 SELECT precision
1229 INTO l_precision
1230 FROM fnd_currencies
1231 WHERE currency_code = l_func_curr_code;
1232
1233 FND_FILE.PUT_LINE(FND_FILE.LOG,'Functional Currency Precision :'||l_precision);
1234
1235 EXCEPTION
1236 WHEN OTHERS THEN
1237 FND_FILE.PUT_LINE(FND_FILE.LOG,'error in getting currency precision');
1238 END;
1239
1240
1241
1242 if gv_debug then FND_FILE.PUT_LINE(FND_FILE.LOG,'INSERT INTO GLOBAL'); end if;
1243 InsertIntoGlobal(
1244 p_jg_info_v20 => l_entity_identifier -- entity_identifier
1245 ,p_jg_info_v21 => l_func_curr_code -- curr_code
1246 ,p_jg_info_v22 => l_company_name -- l_rep_legal_entity -- entity_name
1247 ,p_jg_info_v23 => l_registration_number -- l_taxpayer_id -- taxpayer_id
1248 ,p_jg_info_v24 => l_company_name -- company_name
1249 ,p_jg_info_v25 => l_trx_num -- registration_number
1250 ,p_jg_info_v26 => l_country -- country
1251 ,p_jg_info_v27 => l_address1 -- address1
1252 ,p_jg_info_v28 => l_address2 -- address2
1253 ,p_jg_info_v29 => l_address3 -- address3
1254 ,p_jg_info_v30 => l_address4 -- address4
1255 ,p_jg_info_v31 => l_city -- city
1256 ,p_jg_info_v32 => l_postal_code -- postal_code
1257 ,p_jg_info_v33 => l_contact -- contact
1258 ,p_jg_info_v34 => l_phone_number -- phone_number
1259 ,p_jg_info_v35 => l_reporting_mode_mng -- reporting mode meaning (bug 14024932)
1260 ,p_jg_info_v37 => l_trx_num -- trx_num
1261 ,p_jg_info_d4 => l_period_start_date -- period_start_date
1262 ,p_jg_info_d5 => l_period_end_date -- period_end_date
1263 ,p_jg_info_n26 => l_rep_legal_entity_id -- legalentity_id
1264 ,p_jg_info_n27 => l_period_year -- period_year
1265 ,p_jg_info_v19 => l_registration_number -- company tax Payer Id
1266 ,p_jg_info_v18 => l_tax_rate_code -- tax rate code
1267 ,p_jg_info_n25 => l_precision -- currency precision
1268 ,p_jg_info_v36 => 'H' -- Header record indicator
1269 );
1270
1271
1272
1273 if gv_debug then FND_FILE.PUT_LINE(FND_FILE.LOG,'5'); end if;
1274
1275 IF P_CALLINGREPORT = 'JEDEDVOR' THEN
1276
1277 /* validating the parameters */
1278
1279 IF ( p_reporting_level = 'LE' AND p_legal_entity_id IS NULL) THEN
1280
1281 fnd_message.set_name('JG', 'JG_ZZ_VAT_INVALID_ENTITY');
1282 fnd_message.set_token('PARAMETER', 'Legal Entity');
1283 fnd_message.set_token('LEVEL',p_reporting_level);
1284 LOG_MESSAGE := fnd_message.get;
1285
1286 RAISE INVALID_ENTRY;
1287
1288 ELSIF ( p_reporting_level = 'LEDGER' AND p_ledger_id IS NULL) THEN
1289
1290 fnd_message.set_name('JG', 'JG_ZZ_VAT_INVALID_ENTITY');
1291 fnd_message.set_token('PARAMETER', 'Ledger');
1292 fnd_message.set_token('LEVEL',p_reporting_level);
1293 LOG_MESSAGE := fnd_message.get;
1294
1295 RAISE INVALID_ENTRY;
1296 ELSIF ( p_reporting_level = 'BSV' AND p_company IS NULL) THEN
1297
1298 fnd_message.set_name('JG', 'JG_ZZ_VAT_INVALID_ENTITY');
1299 fnd_message.set_token('PARAMETER', 'Balancing Segment');
1300 fnd_message.set_token('LEVEL',p_reporting_level);
1301 LOG_MESSAGE := fnd_message.get;
1302
1303 RAISE INVALID_ENTRY;
1304 END IF;
1305
1306 BEGIN
1307 GL_INFO.gl_get_ledger_info(P_LEDGER_ID,l_coaid,l_ledger_name,l_functcurr,errbuf);
1308
1309 IF errbuf IS NOT NULL THEN
1310 RAISE INVALID_LEDGER;
1311 END IF;
1312
1313 EXCEPTION
1314 WHEN INVALID_LEDGER THEN
1315 fnd_file.put_line(fnd_file.log,errbuf);
1316 END;
1317
1318 FOR r_ger_receipt IN lcu_ger_receipt ( p_vat_rep_entity_id,
1319 p_period,
1320 p_legal_entity_id,
1321 p_ledger_id,
1322 l_coaid,
1323 p_company,
1324 p_tax_rate_id,
1325 p_reporting_level,
1326 p_gl_period_start_date,
1327 p_gl_period_end_date
1328 )
1329 LOOP
1330
1331 if gv_debug then FND_FILE.PUT_LINE(FND_FILE.LOG,'6'); end if;
1332
1333 l_rd := rdformula(r_ger_receipt.rev_date,p_gl_period_start_date,p_gl_period_end_date);
1334
1335 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);
1336
1337 l_new_amount := new_amountformula(r_ger_receipt.r_date , r_ger_receipt.amount,p_gl_period_start_date,p_gl_period_end_date);
1338
1339 l_new_rev_tax := new_rev_taxformula(l_rd , r_ger_receipt.rev_tax );
1340
1341 l_new_rev_amount := new_rev_amountformula(l_rd, r_ger_receipt.rev_amount );
1342
1343 l_new_receipt := new_receiptformula(r_ger_receipt.r_date,p_gl_period_start_date,p_gl_period_end_date);
1344
1345 l_new_reversal := new_reversalformula(r_ger_receipt.rev_date,p_gl_period_start_date,p_gl_period_end_date);
1346
1347 l_zero := zeroformula;
1348 if gv_debug then
1349 FND_FILE.PUT_LINE(FND_FILE.LOG,'7. CrId:'||r_ger_receipt.cash_receipt_id);
1350 end if;
1351
1352 l_receipt_application := 0;
1353
1354 FOR r_ger_appl IN lcu_ger_appl (r_ger_receipt.cash_receipt_id)
1355 LOOP
1356 if gv_debug then FND_FILE.PUT_LINE(FND_FILE.LOG,'in 2nd loop'); end if;
1357
1358 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 );
1359
1360 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);
1361
1362 l_new_application := new_applicationformula(r_ger_appl.a_date,p_gl_period_start_date,p_gl_period_end_date);
1363
1364 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);
1365
1366 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);
1367
1368 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);
1369
1370 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 );
1371
1372 l_vat := vatformula(r_ger_appl.applied, l_app_vat, l_unapp_vat);
1373 if gv_debug then FND_FILE.PUT_LINE(FND_FILE.LOG,'insert into global'); end if;
1374 InsertIntoGlobal(
1375 p_jg_info_v1 => r_ger_receipt.receipt
1376 , p_jg_info_v2 => r_ger_receipt.status
1377 , p_jg_info_v3 => r_ger_receipt.tax_code
1378 , p_jg_info_v4 => r_ger_receipt.currenct_code
1379 , p_jg_info_v10 => r_ger_receipt.receipt_number
1380 , p_jg_info_d1 => r_ger_receipt.rev_date
1381 , p_jg_info_d2 => r_ger_receipt.r_date
1382 , p_jg_info_n1 => r_ger_receipt.amount
1383 , p_jg_info_n2 => l_zero
1384 , p_jg_info_n3 => r_ger_receipt.rev_amount
1385 , p_jg_info_n4 => r_ger_receipt.r_tax
1386 , p_jg_info_n5 => r_ger_receipt.rev_tax
1387 , p_jg_info_n6 => r_ger_receipt.cash_receipt_id
1388 , p_jg_info_n11 => l_rd
1389 , p_jg_info_n14 => l_new_r_tax
1390 , p_jg_info_n15 => l_new_amount
1391 , p_jg_info_n16 => l_new_rev_tax
1392 , p_jg_info_n17 => l_new_rev_amount
1393 -- , p_jg_info_n18 => l_new_receipt
1394 -- , p_jg_info_n19 => l_new_reversal
1395 , p_jg_info_v15 => l_new_receipt /* modified during UT TEST */
1396 , p_jg_info_v16 => l_new_reversal /* modified during UT TEST */
1397 , p_jg_info_v5 => r_ger_appl.applied
1398 , p_jg_info_v6 => r_ger_appl.invoice_currency_code
1399 , p_jg_info_v7 => r_ger_appl.tax_code
1400 , p_jg_info_v8 => r_ger_appl.customer_name
1401 , p_jg_info_v9 => r_ger_appl.location
1402 , p_jg_info_v13 => l_vat
1403 , p_jg_info_v14 => l_new_application
1404 , p_jg_info_n7 => r_ger_appl.receivable_application_id
1405 , p_jg_info_n8 => r_ger_appl.applied_customer_trx_id
1406 , p_jg_info_n9 => r_ger_appl.amount_applied
1407 , p_jg_info_n20 => l_new_au
1408 , p_jg_info_n21 => l_new_aa
1409 , p_jg_info_n22 => l_app_vat
1410 , p_jg_info_n23 => l_new_app_vat
1411 , p_jg_info_n24 => l_unapp_vat
1412 , p_jg_info_n25 => l_new_unapp_vat
1413 , p_jg_info_d3 => r_ger_appl.a_date
1414 , p_jg_info_v30 => 'JEDEDVOR'
1415 );
1416 l_receipt_application := 1;
1417 END LOOP;
1418
1419 /* Will insert the Onc-Account receipt details,which is not applied */
1420
1421 IF l_receipt_application = 0 THEN
1422
1423 InsertIntoGlobal(
1424 p_jg_info_v1 => r_ger_receipt.receipt
1425 , p_jg_info_v2 => r_ger_receipt.status
1426 , p_jg_info_v3 => r_ger_receipt.tax_code
1427 , p_jg_info_v4 => r_ger_receipt.currenct_code
1428 , p_jg_info_v10 => r_ger_receipt.receipt_number
1429 , p_jg_info_d1 => r_ger_receipt.rev_date
1430 , p_jg_info_d2 => r_ger_receipt.r_date
1431 , p_jg_info_n1 => r_ger_receipt.amount
1432 , p_jg_info_n2 => l_zero
1433 , p_jg_info_n3 => r_ger_receipt.rev_amount
1434 , p_jg_info_n4 => r_ger_receipt.r_tax
1435 , p_jg_info_n5 => r_ger_receipt.rev_tax
1436 , p_jg_info_n6 => r_ger_receipt.cash_receipt_id
1437 , p_jg_info_n11 => l_rd
1438 , p_jg_info_n14 => l_new_r_tax
1439 , p_jg_info_n15 => l_new_amount
1440 , p_jg_info_n16 => l_new_rev_tax
1441 , p_jg_info_n17 => l_new_rev_amount
1442 -- , p_jg_info_n18 => l_new_receipt
1443 -- , p_jg_info_n19 => l_new_reversal
1444 , p_jg_info_v15 => l_new_receipt /* modified during UT TEST */
1445 , p_jg_info_v16 => l_new_reversal /* modified during UT TEST */
1446 , p_jg_info_v5 => NULL
1447 , p_jg_info_v6 => NULL
1448 , p_jg_info_v7 => NULL
1449 , p_jg_info_v8 => NULL
1450 , p_jg_info_v9 => NULL
1451 , p_jg_info_v13 => NULL
1452 , p_jg_info_v14 => NULL
1453 , p_jg_info_n7 => NULL
1454 , p_jg_info_n8 => NULL
1455 , p_jg_info_n9 => NULL
1456 , p_jg_info_n20 => NULL
1457 , p_jg_info_n21 => NULL
1458 , p_jg_info_n22 => NULL
1459 , p_jg_info_n23 => NULL
1460 , p_jg_info_n24 => NULL
1461 , p_jg_info_n25 => NULL
1462 , p_jg_info_d3 => NULL
1463 , p_jg_info_v30 => 'JEDEDVOR'
1464 );
1465 END IF;
1466 END LOOP;
1467 ELSIF P_CALLINGREPORT = 'JGZZARVR' THEN
1468
1469 BEGIN
1470
1471 if gv_debug then FND_FILE.PUT_LINE(FND_FILE.LOG,'JGZZARVR - 1'); end if;
1472 FOR r_euar IN lcu_euar_vatreg (p_vat_rep_entity_id,p_period,p_vat_trx_type, p_ex_vat_trx_type)
1473 LOOP
1474
1475 if gv_debug then FND_FILE.PUT_LINE(FND_FILE.LOG,'JGZZARVR - 2'); end if;
1476
1477 FND_FILE.PUT_LINE(FND_FILE.LOG,'v_count - '||v_count);
1478 FND_FILE.PUT_LINE(FND_FILE.LOG,'r_euar.vat_code - '||r_euar.vat_code);
1479 FND_FILE.PUT_LINE(FND_FILE.LOG,'v_prev_vat_code - '||v_prev_vat_code);
1480
1481
1482 InsertIntoGlobal(
1483 p_jg_info_n1 => r_euar.seq_num
1484 , p_jg_info_n2 => r_euar.tax_rate
1485 , p_jg_info_n3 => r_euar.trx_amount
1486 , p_jg_info_n4 => r_euar.func_amount
1487 , p_jg_info_n5 => r_euar.taxable_amount
1488 , p_jg_info_n6 => r_euar.tax_amount
1489 , p_jg_info_n7 => r_euar.period_year
1490 , p_jg_info_n8 => r_euar.ledger_id
1491 , p_jg_info_n9 => r_euar.period_num
1492 , p_jg_info_d1 => r_euar.tax_date
1493 , p_jg_info_d2 => r_euar.invoice_date
1494 , p_jg_info_d3 => r_euar.gl_date
1495 , p_jg_info_v1 => r_euar.invoice_number
1496 , p_jg_info_n25 => r_euar.trx_id
1497 , p_jg_info_v2 => r_euar.cust_name
1498 , p_jg_info_v3 => r_euar.tax_reg_num
1499 , p_jg_info_v4 => r_euar.curr
1500 , p_jg_info_v5 => r_euar.tax_code
1501 , p_jg_info_v6 => r_euar.vat_code
1502 , p_jg_info_v7 => r_euar.tax_desc
1503 , p_jg_info_v8 => r_euar.vat_desc
1504 , p_jg_info_v9 => r_euar.period_name
1505 , p_jg_info_v10 => r_euar.gl_account
1506 , p_jg_info_v14 => r_euar.vat_type
1507 , p_jg_info_v15 => r_euar.enable_report_sequence_flag
1508 , p_jg_info_v17 => r_euar.tax_rate_code
1509 , p_jg_info_v18 => r_euar.class_code
1510 );
1511 v_enable_report_sequence_flag := r_euar.enable_report_sequence_flag;
1512 END LOOP;
1513
1514 if gv_debug then FND_FILE.PUT_LINE(FND_FILE.LOG,'JGZZARVR - 3'); end if;
1515 FOR r_euar_data_count IN lcu_euar_data_count ( p_ex_vat_trx_type)
1516 LOOP
1517 UPDATE jg_zz_vat_trx_gt
1518 SET jg_info_n20 = r_euar_data_count.c_no_data_count;
1519 END LOOP;
1520 if gv_debug then FND_FILE.PUT_LINE(FND_FILE.LOG,'JGZZARVR - 4'); end if;
1521
1522 -- Implement the report level seq number --
1523
1524 IF v_enable_report_sequence_flag = 'Y' THEN
1525
1526 FOR r_seq_impl IN temp_cur(p_vat_rep_entity_id,p_period,p_vat_trx_type, p_ex_vat_trx_type)
1527 LOOP
1528
1529 IF r_seq_impl.VAT_TRX_TYPE_CODE <> v_prev_vat_code or r_seq_impl.VAT_TRX_TYPE_CODE IS NULL THEN
1530 v_count := 0;
1531 END IF;
1532
1533 SELECT distinct JG_INFO_V40 INTO v_is_seq_updated FROM JG_ZZ_VAT_TRX_GT T1
1534 WHERE T1.jg_info_n25 = r_seq_impl.trx_id
1535 AND T1.jg_info_v6 = r_seq_impl.VAT_TRX_TYPE_CODE;
1536
1537
1538 IF nvl(v_is_seq_updated,'N') <> 'Y' THEN
1539
1540 v_count := v_count+1;
1541
1542 UPDATE JG_ZZ_VAT_TRX_GT SET jg_info_n1 = v_count ,
1543 jg_info_v40 = 'Y'
1544 WHERE jg_info_n25 = r_seq_impl.trx_id
1545 AND jg_info_v6 = r_seq_impl.VAT_TRX_TYPE_CODE;
1546
1547 END IF;
1548
1549 v_prev_vat_code := r_seq_impl.VAT_TRX_TYPE_CODE;
1550
1551 END LOOP;
1552
1553 END IF;
1554
1555 -- End of Implement the report level seq number --
1556
1557 END;
1558
1559 ELSIF P_CALLINGREPORT = 'JEILARDR' THEN
1560 /*
1561 || Israeli VAT AR Detailed Report
1562 || ELSIF added by Ramananda
1563 || Logic for this report is handled in the DataTemplate.
1564 */
1565 --IL VAT 2010 ER Start
1566 FND_FILE.PUT_LINE(FND_FILE.LOG,'Report name :'||'JEILARDR');
1567 BEGIN
1568 SELECT jivl.vat_aggregate_limit_amt
1569 INTO g_vat_agg_limit
1570 FROM je_il_vat_limits jivl,
1571 jg_zz_vat_rep_status jzvrs
1572 WHERE jzvrs.vat_reporting_entity_id = p_vat_rep_entity_id
1573 AND jzvrs.tax_calendar_period = p_period
1574 AND jzvrs.tax_calendar_name = jivl.period_set_name
1575 AND jivl.period_name = p_period
1576 AND rownum = 1;
1577
1578 EXCEPTION
1579 WHEN NO_DATA_FOUND THEN
1580 fnd_file.put_line(fnd_file.log,'Please declare the VAT Aggregation Limit Amount for the tax period:'||p_period ||' for Calendar in the Israel VAT Limits Setup form.');
1581 raise_application_error(-20010,'Please declare the VAT Aggregation Limit Amount for the tax period:'||p_period ||' in the Israel VAT Limits Setup form.');
1582 RETURN (FALSE);
1583 END;
1584
1585 g_precision := l_precision;
1586 FND_FILE.PUT_LINE(FND_FILE.LOG,'VAT Aggregation Limit :'||g_vat_agg_limit);
1587 FND_FILE.PUT_LINE(FND_FILE.LOG,'G_PRECISION :'||g_precision);
1588 --IL VAT 2010 ER End
1589 ELSIF P_CALLINGREPORT = 'JEHRCITR' THEN
1590 /*
1591 || Customer Invoice Tax Report, Croatia
1592 || ELSIF added by Ramananda
1593 || Logic for this report is handled in the DataTemplate.
1594 */
1595 NULL;
1596
1597 ELSIF P_CALLINGREPORT = 'SUMMARY-AR' OR P_CALLINGREPORT IS NULL THEN
1598 -- Call Generic Cursor C_TRX_DTLS
1599 NULL;
1600 END IF;
1601
1602 IF l_reporting_mode = 'PRELIMINARY' OR l_reporting_mode = 'FINAL' THEN
1603
1604 -- Get accounting method --
1605 OPEN lcu_get_acc_method;
1606 FETCH lcu_get_acc_method
1607 INTO l_dummy;
1608 IF lcu_get_acc_method%FOUND THEN
1609 CLOSE lcu_get_acc_method;
1610
1611 /* The tax_date_maintenance_program procedure not suppose to call here.
1612 We should call this procedure before selection process ran i.e. Before the TRL call (as in 11i)
1613 Hence commenting the call to procedure.
1614 */
1615 -- tax_date_maintenance_program(l_period_end_date);
1616 ELSE
1617 CLOSE lcu_get_acc_method;
1618 END IF;
1619 END IF;
1620
1621 RETURN (TRUE);
1622 EXCEPTION
1623 WHEN INVALID_ENTRY THEN
1624 fnd_file.put_line(fnd_file.log,LOG_MESSAGE);
1625 raise;
1626 RETURN (FALSE);
1627 WHEN OTHERS THEN
1628 FND_FILE.PUT_LINE(FND_FILE.LOG,'Unexpected error in JG_ZZ_SUMMARY_AR_PKG.beforeReport. Error-' ||SUBSTR(SQLERRM,1,200));
1629 raise;
1630 RETURN (FALSE);
1631 END beforeReport;
1632
1633 END JG_ZZ_SUMMARY_AR_PKG;