[Home] [Help]
PACKAGE BODY: APPS.JG_ZZ_SUMMARY_AP_PKG
Source
1 PACKAGE BODY JG_ZZ_SUMMARY_AP_PKG
2 -- $Header: jgzzsummaryapb.pls 120.51.12020000.3 2012/10/18 07:13:22 sakekuma ship $
3 AS
4
5 /*
6 REM +======================================================================+
7 REM Name: UNPAID_AMT
8 REM
9 REM Description: This function is used to calculate the unpaid
10 REM amount on an invoice.
11 REM
12 REM Parameters:
13 REM pn_inv_id => The invoice id
14 REM pn_inv_amount => The invoice amount in functional currency.
15 REM pn_taxable_amount => The taxable amount of the invoice.
16 REM pv_offset_tax_code => The offset tax code for the invoice if any
17 REM pd_end_date => Period end date of the period.
18 REM +======================================================================+
19 */
20
21 g_debug constant boolean := true;
22
23 FUNCTION unpaid_amt(pn_inv_id NUMBER
24 ,pn_inv_amount NUMBER
25 ,pn_taxable_amount NUMBER
26 ,pv_offset_tax_code VARCHAR2
27 ,pd_end_date DATE) RETURN NUMBER IS
28
29 l_inv_amt NUMBER;
30 l_unpaid_amt NUMBER;
31 l_exchange_rate NUMBER;
32 l_prepay_on_inv_amt NUMBER;
33 l_sbe_flag VARCHAR2(10);
34 l_cleared_amount NUMBER;
35 CURSOR c_amount_remaining
36 IS
37 SELECT nvl(SUM(amount_remaining), -9999) amount_remaining
38 FROM ap_invoices_all INV
39 ,ap_invoice_payments_all PAY
40 ,ap_checks_all CHECKS
41 ,ap_payment_schedules_all APS
42 WHERE APS.invoice_id = inv.invoice_id
43 AND inv.invoice_id = pay.invoice_id
44 AND checks.check_id = pay.check_id
45 AND pn_inv_id = inv.invoice_id
46 AND checks.status_lookup_code IN ('CLEARED', 'RECONCILED','CLEARED BUT UNACCOUNTED', 'RECONCILED UNACCOUNTED')
47 AND trunc(checks.cleared_date) <= pd_end_date;
48
49 CURSOR c_prepay_inv_amount
50 IS
51 SELECT ABS(NVL(SUM(apid.amount), 0)) prepay_on_inv_amt
52 FROM ap_invoice_distributions_all apid
53 WHERE PREPAY_DISTRIBUTION_ID IS NOT NULL
54 AND invoice_id = pn_inv_id;
55
56 CURSOR c_inv_amount
57 IS
58 SELECT nvl(base_amount, invoice_amount) invoice_amount
59 ,nvl(exchange_rate, 1) exchange_rate
60 FROM ap_invoices_all
61 WHERE invoice_id = pn_inv_id;
62
63 CURSOR c_cleared_amount
64 IS
65 SELECT NVL(SUM(checks.amount),0)
66 FROM ap_invoices_all INV
67 ,ap_invoice_payments_all PAY
68 ,ap_checks_all CHECKS
69 WHERE pn_inv_id = inv.invoice_id
70 AND checks.check_id = pay.check_id
71 AND inv.invoice_id = pay.invoice_id
72 AND checks.status_lookup_code IN ('CLEARED', 'RECONCILED','CLEARED BUT UNACCOUNTED', 'RECONCILED UNACCOUNTED')
73 AND trunc(checks.cleared_date) <= pd_end_date;
74 CURSOR c_sbe_flag
75 IS
76 SELECT nvl(pv.small_business_flag,'N') small_business_flag
77 FROM po_vendors pv, ap_invoices_all inv
78 WHERE inv.invoice_id = pn_inv_id
79 AND inv.vendor_id = pv.vendor_id;
80 BEGIN
81
82 FOR c_amt_remaining IN c_amount_remaining
83 LOOP
84 l_unpaid_amt := c_amt_remaining.amount_remaining;
85 END LOOP;
86
87 -- get the prepayments applied to the invoice with
88 -- include prepay flag as 'NO'
89
90 FOR c_prepay_inv_amt IN c_prepay_inv_amount
91 LOOP
92 l_prepay_on_inv_amt := c_prepay_inv_amt.prepay_on_inv_amt;
93 END LOOP;
94
95 FOR c_inv_amt IN c_inv_amount
96 LOOP
97 l_inv_amt := c_inv_amt.invoice_amount;
98 l_exchange_rate := c_inv_amt.exchange_rate;
99 END LOOP;
100
101 FOR cv_sbe_flag IN c_sbe_flag
102 LOOP
103 l_sbe_flag := cv_sbe_flag.small_business_flag;
104 END LOOP;
105
106 IF l_inv_amt <> 0 THEN
107 IF pv_offset_tax_code IS NOT NULL THEN
108 IF l_unpaid_amt = -9999 THEN
109 l_unpaid_amt := l_inv_amt;
110 l_exchange_rate := 1;
111 END IF;
112 l_unpaid_amt := l_unpaid_amt - l_prepay_on_inv_amt;
113 l_unpaid_amt := (l_unpaid_amt * (pn_taxable_amount / l_inv_amt));
114 RETURN(l_unpaid_amt * l_exchange_rate);
115 ELSE
116 -- Added the modified Unpaid amount Logic for bug 5768048 starts
117 -- Here I am restricting my modified logic of unpaid amount
118 -- for Non-Offset Invoices with SBE flag as Y
119 IF l_sbe_flag ='Y' THEN
120 IF c_cleared_amount%ISOPEN THEN
121 CLOSE c_cleared_amount;
122 END IF;
123 OPEN c_cleared_amount;
124 FETCH c_cleared_amount INTO l_cleared_amount;
125 CLOSE c_cleared_amount;
126 l_unpaid_amt := l_inv_amt - l_cleared_amount;
127 -- Added the modified Unpaid amount Logic for bug 5768048 ends
128 ELSE
129 IF l_unpaid_amt = -9999 THEN
130 l_unpaid_amt := l_inv_amt;
131 l_exchange_rate := 1;
132 END IF;
133 END IF;
134 l_unpaid_amt := l_unpaid_amt - l_prepay_on_inv_amt;
135 l_unpaid_amt := (l_unpaid_amt * (pn_inv_amount / l_inv_amt));
136 RETURN(l_unpaid_amt * l_exchange_rate);
137 END IF;
138 ELSE
139 RETURN NULL;
140 END IF;
141
142 EXCEPTION
143 WHEN OTHERS THEN
144 if g_debug = true then
145 fnd_file.put_line(fnd_file.log,' An error occured while calculating the unpaid amount. Error : ' || SUBSTR(SQLERRM,1,200));
146 end if;
147 RETURN NULL;
148 END unpaid_amt;
149
150 /*
151 REM +======================================================================+
152 REM Name: BEFORE_REPORT
153 REM
154 REM Description: This function is called as a before report trigger by the
155 REM data template. It populates the data in the global_tmp table
156 REM and creates the dynamic where clause for the data template
157 REM queries(lexical reference).
158 REM
159 REM Parameters: None
160 REM +======================================================================+
161 */
162 FUNCTION before_report RETURN BOOLEAN IS
163 BEGIN
164
165 DECLARE
166
167 -- IL VAT Reporting 2010 ER -- Variables Declaration
168 l_calendar_name VARCHAR2(15);
169 l_percentage NUMBER;
170 l_max_rec_amt NUMBER;
171 l_min_rec_amt NUMBER;
172 l_total_vat_amt NUMBER;
173 l_petty_cash_vat_amt NUMBER;
174 l_calculated_max_rec_amt NUMBER;
175 l_declared_amount NUMBER;
176
177 l_tax_calendar_period VARCHAR2(15);
178 l_trx_date DATE;
179 l_trx_id NUMBER;
180 l_trx_number VARCHAR2(100);
181 l_cum_total_amt NUMBER;
182 l_rep_status_id NUMBER;
183 l_cur_period_end_date DATE;
184
185 l_count NUMBER := 1;
186
187 TYPE t_rep_status_id IS TABLE OF JG_ZZ_VAT_REP_STATUS.REPORTING_STATUS_ID%TYPE INDEX BY BINARY_INTEGER;
188 TYPE t_trx_number IS TABLE OF JG_ZZ_VAT_TRX_DETAILS.TRX_NUMBER%TYPE INDEX BY BINARY_INTEGER;
189 TYPE t_trx_date IS TABLE OF JG_ZZ_VAT_TRX_DETAILS.TRX_DATE%TYPE INDEX BY BINARY_INTEGER;
190 TYPE t_il_rep_status IS TABLE OF JG_ZZ_VAT_TRX_DETAILS.IL_VAT_REP_STATUS_ID%TYPE INDEX BY BINARY_INTEGER;
191
192 rep_status_id_rec_tab t_rep_status_id;
193 trx_number_rec_tab t_trx_number;
194 trx_date_rec_tab t_trx_date;
195 il_rep_status_rec_tab t_il_rep_status;
196
197 -- IL VAT Reporting 2010 ER -- Variables Declaration
198
199 l_cleared_select VARCHAR2(2000);
200 l_cleared_select1 VARCHAR2(2000);
201 l_unpaid_amt_select VARCHAR2(2000);
202 l_vat_or_offset NUMBER;
203 l_offset_tax_code_name VARCHAR2(50);
204 l_country_code VARCHAR2(5);
205 l_end_date DATE;
206 l_unpaid_amount NUMBER;
207 l_curr_code VARCHAR2(50);
208 l_rep_entity_name jg_zz_vat_trx_details.rep_context_entity_name%TYPE;
209 l_legal_entity_id NUMBER;
210 l_taxpayer_id jg_zz_vat_trx_details.taxpayer_id%TYPE;
211 l_company_name xle_registrations.registered_name%TYPE;
212 l_registration_number xle_registrations.registration_number%TYPE;
213 l_country hz_locations.country%TYPE;
214 l_address1 hz_locations.address1%TYPE;
215 l_address2 hz_locations.address2%TYPE;
216 l_address3 hz_locations.address3%TYPE;
217 l_address4 hz_locations.address4%TYPE;
218 l_city hz_locations.city%TYPE;
219 l_postal_code hz_locations.postal_code%TYPE;
220 l_contact hz_parties.party_name%TYPE;
221 l_phone_number hz_contact_points.phone_number%TYPE;
222 l_tax_registration_num VARCHAR2 (240);
223 l_period_end_date DATE;
224 l_period_start_date DATE;
225 l_reporting_status VARCHAR2 (60);
226 l_reporting_status_1 VARCHAR2 (60); --Bug 12773668
227 l_lineno NUMBER ;
228 v_count NUMBER :=0;
229 v_prev_vat_code VARCHAR2(230) :='';
230 v_is_seq_updated VARCHAR2(1) := 'N';
231 v_dummy NUMBER;
232 l_enable_report_sequence_flag VARCHAR2(2);
233 -- Added for Glob-006 ER
234 l_province VARCHAR2(120);
235 l_comm_num VARCHAR2(30);
236 l_vat_reg_num VARCHAR2(50);
237 l_ledger_id NUMBER;
238 l_entity_type_code VARCHAR2(30); -- Bug 8289960
239 l_ledger_category_code VARCHAR2(30); -- Bug 8289960
240 -- end here
241
242
243 CURSOR C_DATA IS
244 SELECT jzvtd.doc_seq_value SEQ_NUMBER
245 ,jzvtd.tax_invoice_date TAX_DATE
246 ,jzvtd.billing_tp_name CUSTOMER_NAME
247 ,jzvtd.accounting_date GL_DATE
248 ,jzvtd.trx_currency_code
249 ,sum(nvl(jzvtd.tax_amt_funcl_curr,jzvtd.tax_amt)
250 + nvl(jzvtd.taxable_amt_funcl_curr, jzvtd.taxable_amt)) TOTAL_ACCOUNTED_AMOUNT
251 ,sum(nvl(jzvtd.tax_amt,0) + nvl(jzvtd.taxable_amt, 0)) TOTAL_ENTERED_AMOUNT
252 ,sum(nvl(jzvtd.taxable_amt, 0)) TAXABLE_ENTERED_AMOUNT
253 ,sum(nvl(jzvtd.taxable_amt_funcl_curr, jzvtd.taxable_amt)) TAXABLE_AMOUNT
254 /* fixed during UT for Bug# 5258868
255 ,sum(nvl(jzvtd.tax_amt, 0)) tax_amount
256 ,sum(decode(jzvtd.tax_recoverable_flag, 'Y', jzvtd.tax_amt, 0)) RECOVERABLE
257 ,sum(decode(jzvtd.tax_recoverable_flag, 'N', 0, jzvtd.tax_amt)) NON_RECOVERABLE*/
258 /* following 3 columns added for Bug# 5258868 */
259 ,sum(nvl(jzvtd.tax_amt_funcl_curr, jzvtd.tax_amt)) tax_amount
260 ,sum(decode(jzvtd.tax_recoverable_flag, 'Y', nvl(jzvtd.tax_amt_funcl_curr, jzvtd.tax_amt), 0)) recoverable
261 ,sum(decode(nvl(jzvtd.tax_recoverable_flag,'Y'), 'N', nvl(jzvtd.tax_amt_funcl_curr, jzvtd.tax_amt), 0)) non_recoverable
262 ,jzvtd.trx_number INV_NUMBER
263 ,jzvtd.trx_date INV_DATE
264 ,jzvtd.billing_tp_tax_reg_num TAX_REG_NUM
265 ,jzvtd.tax_rate TAX_RATE
266 ,jzvtd.trx_id
267 ,jzvtd.tax_rate_vat_trx_type_desc
268 ,jzvtd.tax_rate_code_vat_trx_type_mng
269 ,jzvtd.tax_rate_code
270 ,jzvtd.trx_line_class
271 ,jzvtd.tax_rate_code_description
272 ,jzvtd.tax_recoverable_flag rec_flag
273 ,jzvtd.account_flexfield
274 ,jzvtd.doc_seq_name
275 /* UT TEST ,to_char(jzvtd.tax_invoice_date,'MON') GL_PERIOD */
276 , jzvrs.tax_calendar_period GL_PERIOD /* UT TEST */
277 ,jzvtd.tax_rate_vat_trx_type_code
278 ,jzvtd.reporting_code
279 ,jzvtd.tax_line_id
280 ,jzvtd.offset_flag
281 ,jzvtd.offset_tax_rate_code
282 ,jzvtd.chk_vat_amount_paid CHK_VAT_AMOUNT_PAID
283 ,jzvrs.period_end_date
284 FROM jg_zz_vat_trx_details jzvtd
285 ,jg_zz_vat_rep_status jzvrs
286 WHERE jzvrs.vat_reporting_entity_id = P_VAT_REPORTING_ENTITY_ID
287 -- AND jzvrs.tax_calendar_period = P_TAX_CALENDAR_PERIOD --bug5867390
288 AND jzvtd.tax_invoice_date <= (SELECT period_end_date
289 FROM jg_zz_vat_rep_status jzvrs
290 WHERE jzvrs.vat_reporting_entity_id = P_VAT_REPORTING_ENTITY_ID
291 AND jzvrs.source = 'AP'
292 AND jzvrs.tax_calendar_period = P_TAX_CALENDAR_PERIOD)
293 AND jzvrs.reporting_status_id = jzvtd.reporting_status_id
294 AND jzvtd.tax_rate_register_type_code = 'TAX'
295 AND jzvrs.source = 'AP'
296 AND (P_VAT_TRX_TYPE IS NULL OR jzvtd.tax_rate_vat_trx_type_code = P_VAT_TRX_TYPE)
297 AND (P_EX_VAT_TRX_TYPE is null or nvl(jzvtd.tax_rate_vat_trx_type_code,'#') <> P_EX_VAT_TRX_TYPE)
298 AND (P_INC_PREPAYMENTS = 'Y' OR (jzvtd.trx_line_class <> 'PREPAYMENT INVOICES' AND P_INC_PREPAYMENTS = 'N') )
299 GROUP BY jzvtd.reporting_code
300 ,jzvtd.doc_seq_value
301 , jzvrs.tax_calendar_period /* UT TEST addition */
302 ,jzvtd.tax_invoice_date
303 ,jzvtd.billing_tp_name
304 ,jzvtd.accounting_date
305 ,jzvtd.trx_currency_code
306 ,trx_number
307 ,trx_date
308 ,billing_tp_tax_reg_num
309 ,tax_rate
310 ,jzvtd.trx_id
311 ,jzvtd.tax_rate_vat_trx_type_desc
312 ,jzvtd.tax_rate_code_vat_trx_type_mng
313 ,jzvtd.tax_rate_code
314 ,jzvtd.trx_line_class
315 ,jzvtd.tax_rate_code_description
316 ,jzvtd.tax_recoverable_flag
317 ,jzvtd.account_flexfield
318 ,jzvtd.doc_seq_name
319 ,jzvtd.tax_rate_vat_trx_type_code
320 ,jzvtd.chk_vat_amount_paid
321 ,jzvtd.tax_line_id
322 ,jzvtd.offset_flag
323 ,jzvtd.offset_tax_rate_code
324 ,jzvrs.period_end_date ;
325
326
327 CURSOR C_NEW IS
328 SELECT jzvtd.doc_seq_value seq_number
329 ,jzvtd.tax_invoice_date tax_date
330 ,decode(jzvtd.event_class_code, 'EXPENSE REPORTS',
331 decode(jzvtd.merchant_party_name, null,
332 jzvtd.billing_tp_name, jzvtd.merchant_party_name), jzvtd.billing_tp_name) customer_name
333 ,jzvtd.accounting_date gl_date
334 ,jzvtd.trx_currency_code
335 ,sum(nvl(jzvtd.tax_amt_funcl_curr,jzvtd.tax_amt)
336 + nvl(jzvtd.taxable_amt_funcl_curr, jzvtd.taxable_amt)*jzvtd.tax_recovery_rate/100) total_accounted_amount
337 ,sum(nvl(jzvtd.tax_amt,0) + nvl(jzvtd.taxable_amt, 0)*jzvtd.tax_recovery_rate/100) total_entered_amount
338 ,sum(nvl(jzvtd.taxable_amt, 0)*jzvtd.tax_recovery_rate/100) taxable_entered_amount
339 ,sum(nvl(jzvtd.taxable_amt_funcl_curr, jzvtd.taxable_amt)*jzvtd.tax_recovery_rate/100) taxable_amount
340 /* fixed during UT for Bug# 5258868
341 ,sum(nvl(jzvtd.tax_amt, 0)) tax_amount
342 ,sum(decode(jzvtd.tax_recoverable_flag, 'Y', jzvtd.tax_amt, 0)) recoverable
343 ,sum(decode(jzvtd.tax_recoverable_flag, 'N', 0, jzvtd.tax_amt)) non_recoverable */
344 /* following 3 columns added for Bug# 5258868 */
345 ,sum(nvl(jzvtd.tax_amt_funcl_curr, jzvtd.tax_amt)) tax_amount
346 ,sum(decode(jzvtd.tax_recoverable_flag, 'Y', nvl(jzvtd.tax_amt_funcl_curr, jzvtd.tax_amt), 0)) recoverable
347 ,sum(decode(nvl(jzvtd.tax_recoverable_flag,'Y'), 'N', nvl(jzvtd.tax_amt_funcl_curr, jzvtd.tax_amt), 0)) non_recoverable
348 ,decode(jzvtd.event_class_code, 'EXPENSE REPORTS',
349 decode(jzvtd.merchant_party_document_number, null,
350 jzvtd.trx_number, jzvtd.merchant_party_document_number), jzvtd.trx_number) inv_number
351 ,decode(jzvtd.event_class_code, 'EXPENSE REPORTS',
352 decode(jzvtd.start_expense_date, null,
353 jzvtd.trx_date, jzvtd.start_expense_date), jzvtd.trx_date) inv_date
354 ,decode(jzvtd.event_class_code, 'EXPENSE REPORTS',
355 decode(jzvtd.merchant_party_tax_reg_number, null,
356 jzvtd.billing_tp_tax_reg_num, jzvtd.merchant_party_tax_reg_number), jzvtd.billing_tp_tax_reg_num) tax_reg_num
357 ,jzvtd.tax_rate tax_rate
358 ,jzvtd.trx_id
359 ,jzvtd.tax_rate_vat_trx_type_desc
360 ,jzvtd.tax_rate_code_vat_trx_type_mng
361 ,jzvtd.tax_rate_code
362 ,jzvtd.trx_line_class
363 ,jzvtd.tax_rate_code_description
364 ,jzvtd.doc_seq_name
365 ,jzvrs.tax_calendar_period gl_period /* UT TEST addition */
366 /* UT TEST ,to_char(jzvtd.tax_invoice_date,'MON') gl_period */
367 ,jzvtd.tax_rate_vat_trx_type_code
368 ,jzvtd.reporting_code
369 --bug 13248892 ,jzvtd.tax_line_id
370 ,jzvtd.offset_flag
371 ,jzvtd.offset_tax_rate_code
372 ,jzvtd.chk_vat_amount_paid chk_vat_amount_paid
373 ,jzvrs.period_end_date
374 FROM jg_zz_vat_trx_details jzvtd
375 ,jg_zz_vat_rep_status jzvrs
376 WHERE jzvrs.vat_reporting_entity_id = P_VAT_REPORTING_ENTITY_ID
377 -- AND jzvrs.tax_calendar_period = P_TAX_CALENDAR_PERIOD --bug5867390
378 AND jzvtd.tax_invoice_date <= (SELECT period_end_date
379 FROM jg_zz_vat_rep_status jzvrs
380 WHERE jzvrs.vat_reporting_entity_id = P_VAT_REPORTING_ENTITY_ID
381 AND jzvrs.source = 'AP'
382 AND jzvrs.tax_calendar_period = P_TAX_CALENDAR_PERIOD)
383 AND jzvrs.reporting_status_id = jzvtd.reporting_status_id
384 -- AND jzvtd.tax_rate_register_type_code = 'TAX'
385 AND jzvrs.source = 'AP'
386 AND (P_VAT_TRX_TYPE IS NULL OR jzvtd.tax_rate_vat_trx_type_code = P_VAT_TRX_TYPE)
387 AND (P_EX_VAT_TRX_TYPE IS NULL OR nvl(jzvtd.tax_rate_vat_trx_type_code,'#') <> P_EX_VAT_TRX_TYPE)
388 AND ((jzvtd.trx_line_class <> 'PREPAYMENT INVOICES' AND P_INC_PREPAYMENTS = 'N') OR P_INC_PREPAYMENTS = 'Y')
389 GROUP BY jzvtd.tax_rate_code_vat_trx_type_mng
390 ,jzvtd.tax_rate_vat_trx_type_desc
391 ,jzvtd.tax_rate_code
392 ,decode(jzvtd.event_class_code, 'EXPENSE REPORTS',
393 decode(jzvtd.merchant_party_document_number, null,
394 jzvtd.trx_number, jzvtd.merchant_party_document_number), jzvtd.trx_number)
395 ,jzvtd.doc_seq_value
396 ,jzvrs.tax_calendar_period /* UT TEST addition */
397 ,jzvtd.tax_invoice_date
398 ,decode(jzvtd.event_class_code, 'EXPENSE REPORTS',
399 decode(jzvtd.merchant_party_name, null,
400 jzvtd.billing_tp_name, jzvtd.merchant_party_name), jzvtd.billing_tp_name)
401 ,jzvtd.accounting_date
402 ,jzvtd.trx_currency_code
403 ,decode(jzvtd.event_class_code, 'EXPENSE REPORTS',
404 decode(jzvtd.start_expense_date, null,
405 jzvtd.trx_date, jzvtd.start_expense_date), jzvtd.trx_date)
406 ,decode(jzvtd.event_class_code, 'EXPENSE REPORTS',
407 decode(jzvtd.merchant_party_tax_reg_number, null,
408 jzvtd.billing_tp_tax_reg_num, jzvtd.merchant_party_tax_reg_number), jzvtd.billing_tp_tax_reg_num)
409 ,jzvtd.tax_rate
410 ,jzvtd.trx_id
411 ,jzvtd.trx_line_class
412 ,jzvtd.tax_rate_code_description
413 ,jzvtd.doc_seq_name
414 ,jzvtd.tax_rate_vat_trx_type_code
415 ,jzvtd.chk_vat_amount_paid
416 ,jzvtd.reporting_code
417 --bug 13248892 ,jzvtd.tax_line_id
418 ,jzvtd.offset_flag
419 ,jzvtd.offset_tax_rate_code
420 ,jzvrs.period_end_date;
421
422
423 -- Cursor for report level seq number --
424
425 -- Cursor for report level seq number --
426 CURSOR temp_cur IS
427 SELECT jg_info_n1 seq_num,
428 jg_info_v7 vat_code,
429 tmp.rowid,
430 jg_info_v14 tax_code,
431 jg_info_n5 trx_id,
432 jg_info_v15 reporting_code
433 FROM JG_ZZ_VAT_TRX_GT tmp,
434 gl_periods glp,
435 jg_zz_vat_rep_status JZVRS
436 WHERE jg_info_v9 = 'M'
437 AND JZVRS.vat_reporting_entity_id = P_VAT_REPORTING_ENTITY_ID
438 AND jzvrs.tax_calendar_period = P_TAX_CALENDAR_PERIOD
439 AND jg_info_v16 = jzvrs.tax_calendar_period --bug5867390
440 AND GLP.period_set_name = jzvrs.tax_calendar_name
441 AND jg_info_d1 between glp.start_date and glp.end_date
442 AND jzvrs.source = 'AP'
443 ORDER BY decode(jg_info_v15, 'OFFSET','XOFFSET','VAT'),
444 jg_info_v7, --TAX_RATE_VAT_TRX_TYPE_CODE
445 jg_info_v13,
446 jg_info_v12, --VAT_TRANSACTION_TYPE_CODE description.
447 period_year desc,
448 period_num desc,
449 jg_info_v6, --TAX CODE DESC
450 jg_info_v14, --TAX_RATE_CODE
451 jg_info_d1, --TAX_INVOICE_DATE
452 jg_info_v1,
453 jg_info_n3; /*tax_rate*/
454
455
456 CURSOR C_COMPLETE IS
457 SELECT jzvtd.doc_seq_value seq_number
458 ,jzvtd.tax_invoice_date tax_date
459 ,jzvtd.billing_tp_name customer_name
460 ,jzvtd.accounting_date gl_date
461 ,jzvtd.trx_currency_code
462 ,jzvtd.tax_amt_funcl_curr
463 ,jzvtd.taxable_amt_funcl_curr
464 ,jzvtd.tax_amt tax_amount
465 ,jzvtd.taxable_amt taxable_amount
466 ,jzvtd.tax_recoverable_flag
467 ,jzvtd.trx_number inv_number
468 ,jzvtd.trx_date inv_date
469 ,jzvtd.billing_tp_tax_reg_num tax_reg_num
470 ,jzvtd.tax_rate tax_rate
471 ,jzvtd.trx_id
472 ,jzvtd.tax_rate_vat_trx_type_desc
473 ,jzvtd.tax_rate_code_vat_trx_type_mng
474 ,jzvtd.tax_rate_code
475 ,jzvtd.trx_line_class
476 ,jzvtd.tax_rate_code_description
477 ,jzvtd.tax_recoverable_flag rec_flag
478 ,jzvtd.account_flexfield
479 ,jzvtd.doc_seq_name
480 , jzvrs.tax_calendar_period gl_period /* UT TEST */
481 /* UT TEST ,to_char(jzvtd.tax_invoice_date,'MON') gl_period */
482 ,jzvtd.tax_rate_vat_trx_type_code
483 ,jzvtd.tax_type_code
484 ,jzvtd.tax_line_id
485 ,jzvtd.offset_flag
486 ,jzvtd.offset_tax_rate_code
487 ,jzvtd.chk_vat_amount_paid chk_vat_amount_paid
488 ,jzvrs.period_end_date
489 ,jzvtd.reporting_code
490 FROM jg_zz_vat_trx_details jzvtd
491 ,jg_zz_vat_rep_status jzvrs
492 WHERE jzvrs.vat_reporting_entity_id = P_VAT_REPORTING_ENTITY_ID
493 AND jzvrs.tax_calendar_period = P_TAX_CALENDAR_PERIOD
494 AND jzvrs.reporting_status_id = jzvtd.reporting_status_id
495 AND jzvrs.source = 'AP'
496 AND jzvtd.tax_rate_register_type_code = 'TAX';
497
498 CURSOR c_get_lookup_values (p_trx_type VARCHAR2)
499 IS
500 SELECT meaning
501 ,description
502 FROM fnd_lookups
503 WHERE lookup_type = 'ZX_JEBE_VAT_TRANS_TYPE'
504 AND lookup_code = p_trx_type;
505
506 CURSOR c_company_details
507 IS
508 SELECT jzvtd.rep_context_entity_name company_name
509 ,jzvtd.functional_currency_code functional_currency_code
510 FROM jg_zz_vat_trx_details jzvtd
511 ,jg_zz_vat_rep_status jzvrs
512 WHERE jzvrs.vat_reporting_entity_id = P_VAT_REPORTING_ENTITY_ID
513 AND jzvrs.source = 'AP'
514 AND jzvrs.tax_calendar_period = P_TAX_CALENDAR_PERIOD
515 AND jzvrs.reporting_status_id = jzvtd.reporting_status_id
516 and rownum < 2; /* added during UT Bug# 5258868 */
517
518 CURSOR c_vat_or_offset (p_tax_rate_code VARCHAR2)
519 IS
520 SELECT MIN(-9999) vat_or_offset
521 FROM jg_zz_vat_trx_details jzvtd
522 ,jg_zz_vat_rep_status jzvrs
523 WHERE jzvtd.tax_rate_code = p_tax_rate_code
524 AND jzvrs.reporting_status_id = jzvtd.reporting_status_id
525 AND jzvrs.vat_reporting_entity_id = P_VAT_REPORTING_ENTITY_ID
526 AND jzvrs.source = 'AP'
527 AND jzvrs.tax_calendar_period = P_TAX_CALENDAR_PERIOD
528 AND jzvtd.offset_tax_rate_code IS NOT NULL;
529
530 CURSOR c_period_end_date
531 IS
532 SELECT period_end_date
533 FROM jg_zz_vat_rep_status jzvrs
534 WHERE jzvrs.vat_reporting_entity_id = P_VAT_REPORTING_ENTITY_ID
535 AND jzvrs.source = 'AP'
536 AND jzvrs.tax_calendar_period = P_TAX_CALENDAR_PERIOD;
537
538 -- IL VAT Reporting 2010 ER -- Cursors
539
540 CURSOR il_get_limits
541 IS
542 SELECT
543 limit.max_recoverable_percentage,
544 limit.max_recoverable_amt,
545 limit.min_recoverable_amt,
546 limit.period_set_name
547 FROM
548 je_il_vat_limits limit,
549 JG_ZZ_VAT_REP_STATUS JZVRS
550 WHERE
551 JZVRS.VAT_REPORTING_ENTITY_ID = P_VAT_REPORTING_ENTITY_ID
552 AND JZVRS.TAX_CALENDAR_PERIOD = P_TAX_CALENDAR_PERIOD
553 AND JZVRS.TAX_CALENDAR_NAME = limit.PERIOD_SET_NAME
554 AND limit.PERIOD_NAME = P_TAX_CALENDAR_PERIOD
555 AND ROWNUM = 1;
556
557
558 CURSOR il_get_total_vat_amt
559 IS
560 SELECT
561 sum(decode(tax_recoverable_flag,'Y', nvl(jzvtd.tax_amt_funcl_curr,jzvtd.tax_amt),0))
562 total_vat_amt
563 FROM
564 jg_zz_vat_trx_details jzvtd,
565 jg_zz_vat_rep_status jzvrs
566 WHERE jzvrs.source='AP'
567 and jzvrs.vat_reporting_entity_id = p_vat_reporting_entity_id
568 and jzvrs.tax_calendar_period = p_tax_calendar_period
569 and (p_vat_trx_type is null or jzvtd.tax_rate_vat_trx_type_code = p_vat_trx_type)
570 and jzvrs.reporting_status_id = jzvtd.reporting_status_id
571 and jzvtd.reporting_code in ('VAT-A','VAT-S', 'VAT-C', 'VAT-RA', 'VAT-RS', 'VAT-P', 'VAT-H');
572
573 CURSOR il_get_petty_cash_vat_amt
574 IS
575 SELECT nvl(sum(total_vatks_amt),0) total_petty_cash_vat_amt
576 FROM
577 (SELECT
578 sum(decode(tax_recoverable_flag,'Y', abs(nvl(jzvtd.tax_amt_funcl_curr,jzvtd.tax_amt)),0))
579 total_vatks_amt
580 FROM
581 jg_zz_vat_trx_details jzvtd,
582 jg_zz_vat_rep_status jzvrs
583 WHERE jzvrs.source='AP'
584 and jzvrs.vat_reporting_entity_id = p_vat_reporting_entity_id
585 and jzvrs.tax_calendar_period <> p_tax_calendar_period
586 and jzvrs.reporting_status_id = jzvtd.reporting_status_id
587 and (jzvtd.il_vat_rep_status_id = -999 OR jzvtd.il_vat_rep_status_id = l_rep_status_id)
588 and (p_vat_trx_type is null or jzvtd.tax_rate_vat_trx_type_code = p_vat_trx_type)
589 and jzvtd.reporting_code in ('VAT-KS','VAT-KA')
590 UNION ALL
591 SELECT
592 sum(decode(tax_recoverable_flag,'Y', abs(nvl(jzvtd.tax_amt_funcl_curr,jzvtd.tax_amt)),0))
593 total_vatks_amt
594 FROM
595 jg_zz_vat_trx_details jzvtd,
596 jg_zz_vat_rep_status jzvrs
597 WHERE jzvrs.source='AP'
598 and jzvrs.vat_reporting_entity_id = p_vat_reporting_entity_id
599 and jzvrs.tax_calendar_period = p_tax_calendar_period
600 and jzvrs.reporting_status_id = jzvtd.reporting_status_id
601 and (p_vat_trx_type is null or jzvtd.tax_rate_vat_trx_type_code = p_vat_trx_type)
602 and jzvtd.reporting_code in ('VAT-KS','VAT-KA'));
603
604 CURSOR il_get_rep_status_id
605 IS
606 SELECT
607 jzvrs.reporting_status_id,
608 jzvrs.period_end_date
609 FROM
610 jg_zz_vat_rep_status jzvrs
611 WHERE
612 jzvrs.source='AP'
613 and jzvrs.vat_reporting_entity_id = p_vat_reporting_entity_id
614 and jzvrs.tax_calendar_period = p_tax_calendar_period;
615
616 CURSOR il_get_petty_cash_trxs
617 IS
618 SELECT
619 trxs.billing_tp_name,
620 trxs.billing_tp_number,
621 trxs.billing_tp_site_name,
622 trxs.trx_date,
623 trxs.trx_number,
624 trxs.reporting_status_id,
625 trxs.total_vat_amt,
626 SUM(trxs.total_vat_amt) over (ORDER BY trxs.billing_tp_name,
627 trxs.billing_tp_number,
628 trxs.billing_tp_site_name,
629 trxs.trx_date,
630 trxs.trx_number,
631 trxs.reporting_status_id) cum_total_amt
632 FROM
633 (SELECT
634 jzvtd.billing_tp_name,
635 jzvtd.billing_tp_number,
636 jzvtd.billing_tp_site_name,
637 jzvtd.trx_date,
638 jzvtd.trx_number,
639 jzvrs.reporting_status_id,
640 SUM(DECODE(jzvtd.tax_recoverable_flag,'Y', NVL(jzvtd.tax_amt_funcl_curr,NVL(jzvtd.tax_amt,0)),0)) total_vat_amt
641 FROM
642 jg_zz_vat_trx_details jzvtd,
643 jg_zz_vat_rep_status jzvrs
644 WHERE
645 jzvrs.source='AP'
646 and jzvrs.vat_reporting_entity_id = p_vat_reporting_entity_id
647 and (jzvtd.il_vat_rep_status_id = -999 OR jzvtd.il_vat_rep_status_id = l_rep_status_id)
648 and jzvrs.tax_calendar_period <> p_tax_calendar_period
649 and jzvrs.reporting_status_id = jzvtd.reporting_status_id
650 and jzvrs.period_end_date <= l_cur_period_end_date
651 and (p_vat_trx_type is null or jzvtd.tax_rate_vat_trx_type_code = p_vat_trx_type)
652 and jzvtd.reporting_code in ('VAT-KS','VAT-KA')
653 group by jzvtd.billing_tp_name,
654 jzvtd.billing_tp_number,
655 jzvtd.billing_tp_site_name,
656 jzvtd.trx_date,
657 jzvtd.trx_number,
658 jzvrs.reporting_status_id
659 UNION ALL
660 SELECT
661 jzvtd.billing_tp_name,
662 jzvtd.billing_tp_number,
663 jzvtd.billing_tp_site_name,
664 jzvtd.trx_date,
665 jzvtd.trx_number,
666 jzvrs.reporting_status_id,
667 SUM(DECODE(jzvtd.tax_recoverable_flag,'Y', NVL(jzvtd.tax_amt_funcl_curr,NVL(jzvtd.tax_amt,0)),0)) total_vat_amt
668 FROM
669 jg_zz_vat_trx_details jzvtd,
670 jg_zz_vat_rep_status jzvrs
671 WHERE
672 jzvrs.source='AP'
673 and jzvrs.vat_reporting_entity_id = p_vat_reporting_entity_id
674 and jzvrs.tax_calendar_period = p_tax_calendar_period
675 and jzvrs.reporting_status_id = jzvtd.reporting_status_id
676 and jzvrs.period_end_date <= l_cur_period_end_date
677 and (p_vat_trx_type is null or jzvtd.tax_rate_vat_trx_type_code = p_vat_trx_type)
678 and jzvtd.reporting_code in ('VAT-KS','VAT-KA')
679 GROUP BY jzvtd.billing_tp_name,
680 jzvtd.billing_tp_number,
681 jzvtd.billing_tp_site_name,
682 jzvtd.trx_date,
683 jzvtd.trx_number,
684 jzvrs.reporting_status_id) trxs
685 ORDER BY trxs.billing_tp_name,
686 trxs.billing_tp_number,
687 trxs.billing_tp_site_name,
688 trxs.trx_date,
689 trxs.trx_number,
690 trxs.reporting_status_id;
691
692
693 CURSOR israel_new_details
694 IS
695 -- VAT File Line Type - T , C, P , H
696 -- Reporting Code :
697 -- 'VAT-A','VAT-KA','VAT-RA' - Fixed Assets ,
698 -- 'VAT-S','VAT-C','VAT-P','VAT-H' - Other Trxs
699 SELECT jzvtd.billing_tp_name vendor_name
700 ,jzvtd.billing_tp_number vendor_number
701 ,jzvtd.billing_tp_site_name site_name
702 ,NVL(jzvtd.billing_tp_site_tax_reg_num,jzvtd.billing_tp_tax_reg_num) tax_reg_num
703 ,jzvtd.trx_number inv_number
704 ,jzvtd.trx_date inv_date
705 ,NULL import_document_number
706 ,NULL import_document_date
707 ,DECODE(nvl2(apinv.cancelled_date,'Y','N'),'Y','CANCELLED',jzvtd.trx_line_class) trx_line_class
708 ,jzvtd.posted_flag
709 ,JG_ZZ_COMMON_PKG.get_amt_tot(jzvtd.trx_id,l_ledger_id,G_PRECISION) invoice_amount
710 ,sum(DECODE(tax_recoverable_flag,'Y',
711 (CASE
712 WHEN jzvtd.reporting_code IN ('VAT-A') THEN nvl(jzvtd.tax_amt_funcl_curr,jzvtd.tax_amt)
713 ELSE 0
714 END)
715 , 0)) vat_on_fixed_assets
716 ,sum(DECODE(tax_recoverable_flag,'Y',
717 (CASE
718 WHEN jzvtd.reporting_code IN ('VAT-S','VAT-C','VAT-P','VAT-H') THEN nvl(jzvtd.tax_amt_funcl_curr,jzvtd.tax_amt)
719 ELSE 0
720 END)
721 , 0)) vat_on_other_trxs
722 ,(CASE
723 WHEN jzvtd.reporting_code = 'VAT-C' THEN 'C'
724 WHEN jzvtd.reporting_code = 'VAT-P' THEN 'P'
725 WHEN jzvtd.reporting_code = 'VAT-H' THEN 'H'
726 ELSE 'T'
727 END) class
728 ,0 mark_trx_flag
729 FROM jg_zz_vat_trx_details jzvtd
730 ,jg_zz_vat_rep_status jzvrs
731 ,ap_invoices_all apinv
732 WHERE jzvrs.source='AP'
733 AND jzvrs.vat_reporting_entity_id = P_VAT_REPORTING_ENTITY_ID
734 AND jzvrs.tax_calendar_period = P_TAX_CALENDAR_PERIOD
735 AND JZVRS.REPORTING_STATUS_ID = JZVTD.REPORTING_STATUS_ID
736 AND (P_VAT_TRX_TYPE is null or jzvtd.tax_rate_vat_trx_type_code = P_VAT_TRX_TYPE)
737 AND jzvtd.reporting_code IN ('VAT-A','VAT-S','VAT-C','VAT-P','VAT-H')
738 AND apinv.invoice_id = jzvtd.trx_id
739 GROUP BY jzvtd.billing_tp_name
740 ,jzvtd.billing_tp_number
741 ,jzvtd.billing_tp_site_name
742 ,NVL(jzvtd.billing_tp_site_tax_reg_num,jzvtd.billing_tp_tax_reg_num)
743 ,jzvtd.trx_number
744 ,jzvtd.trx_id
745 ,jzvtd.trx_date
746 ,DECODE(nvl2(apinv.cancelled_date,'Y','N'),'Y','CANCELLED',jzvtd.trx_line_class)
747 ,jzvtd.posted_flag
748 ,(CASE
749 WHEN jzvtd.reporting_code = 'VAT-C' THEN 'C'
750 WHEN jzvtd.reporting_code = 'VAT-P' THEN 'P'
751 WHEN jzvtd.reporting_code = 'VAT-H' THEN 'H'
752 ELSE 'T'
753 END)
754
755 UNION ALL
756
757 -- VAT File Line Type - R
758 -- Reporting Code :
759 -- 'VAT-RS' - Other Trxs
760 SELECT jzvtd.billing_tp_name vendor_name
761 ,jzvtd.billing_tp_number vendor_number
762 ,jzvtd.billing_tp_site_name site_name
763 ,NVL(jzvtd.billing_tp_site_tax_reg_num,jzvtd.billing_tp_tax_reg_num) tax_reg_num
764 ,jzvtd.trx_number inv_number
765 ,DECODE(length(nvl(apinvl.GLOBAL_ATTRIBUTE13,'A')),
766 1,jzvtd.trx_date,
767 19,TO_DATE(apinvl.GLOBAL_ATTRIBUTE13,'RRRR/MM/DD hh24:mi:ss'),
768 to_date(apinvl.GLOBAL_ATTRIBUTE13, 'DD-MM-RRRR')) inv_date
769 ,NVL(apinvl.GLOBAL_ATTRIBUTE14,apinv.invoice_num) import_document_number
770 ,apinvl.GLOBAL_ATTRIBUTE13 import_document_date
771 ,DECODE(nvl2(apinv.cancelled_date,'Y','N'),'Y','CANCELLED',jzvtd.trx_line_class) trx_line_class
772 ,jzvtd.posted_flag
773 ,JG_ZZ_COMMON_PKG.get_amt_tot(jzvtd.trx_id,l_ledger_id,G_PRECISION) invoice_amount
774 ,SUM(DECODE(jzvtd.reporting_code,'VAT-RA',DECODE(tax_recoverable_flag,'Y',nvl(jzvtd.tax_amt_funcl_curr,jzvtd.tax_amt),0),0)) vat_on_fixed_assets
775 ,SUM(DECODE(jzvtd.reporting_code,'VAT-RS',DECODE(tax_recoverable_flag,'Y',nvl(jzvtd.tax_amt_funcl_curr,jzvtd.tax_amt),0),0)) vat_on_other_trxs
776 , 'R' class
777 ,0 mark_trx_flag
778 FROM jg_zz_vat_trx_details jzvtd
779 ,jg_zz_vat_rep_status jzvrs
780 ,ap_invoices_all apinv
781 ,ap_invoice_lines_all apinvl
782 ,zx_lines zxl
783 WHERE jzvrs.source='AP'
784 AND jzvrs.vat_reporting_entity_id = P_VAT_REPORTING_ENTITY_ID
785 AND jzvrs.tax_calendar_period = P_TAX_CALENDAR_PERIOD
786 AND JZVRS.REPORTING_STATUS_ID = JZVTD.REPORTING_STATUS_ID
787 AND ( P_VAT_TRX_TYPE is null or jzvtd.tax_rate_vat_trx_type_code = P_VAT_TRX_TYPE)
788 AND jzvtd.reporting_code IN ('VAT-RS','VAT-RA')
789 AND apinv.invoice_id = jzvtd.trx_id
790 AND zxl.trx_id= apinv.invoice_id
791 AND zxl.trx_id= apinvl.invoice_id(+)
792 AND zxl.tax_line_id =jzvtd.tax_line_id
793 AND zxl.summary_tax_line_id = apinvl.summary_tax_line_id(+)
794 GROUP BY jzvtd.billing_tp_name
795 ,jzvtd.billing_tp_number
796 ,jzvtd.billing_tp_site_name
797 ,NVL(jzvtd.billing_tp_site_tax_reg_num,jzvtd.billing_tp_tax_reg_num)
798 ,jzvtd.trx_number
799 ,jzvtd.trx_id
800 ,DECODE(length(nvl(apinvl.GLOBAL_ATTRIBUTE13,'A')),
801 1,jzvtd.trx_date,
802 19,TO_DATE(apinvl.GLOBAL_ATTRIBUTE13,'RRRR/MM/DD hh24:mi:ss'),
803 to_date(apinvl.GLOBAL_ATTRIBUTE13, 'DD-MM-RRRR'))
804 ,NVL(apinvl.GLOBAL_ATTRIBUTE14,apinv.invoice_num)
805 ,apinvl.GLOBAL_ATTRIBUTE13
806 ,DECODE(nvl2(apinv.cancelled_date,'Y','N'),'Y','CANCELLED',jzvtd.trx_line_class)
807 ,jzvtd.posted_flag
808
809 UNION ALL
810 -- VAT File Line Type - K
811 -- Reporting Code :
812 -- 'VAT-KS' - Other Trxs
813 -- Transactions that are considered for the current period.
814 SELECT jzvtd.billing_tp_name vendor_name
815 ,jzvtd.billing_tp_number vendor_number
816 ,jzvtd.billing_tp_site_name site_name
817 ,NVL(jzvtd.billing_tp_site_tax_reg_num,jzvtd.billing_tp_tax_reg_num) tax_reg_num
818 ,jzvtd.trx_number inv_number
819 ,jzvtd.trx_date inv_date
820 ,NULL import_document_number
821 ,NULL import_document_date
822 ,DECODE(nvl2(apinv.cancelled_date,'Y','N'),'Y','CANCELLED',jzvtd.trx_line_class) trx_line_class
823 ,jzvtd.posted_flag
824 ,JG_ZZ_COMMON_PKG.get_amt_tot(jzvtd.trx_id,l_ledger_id,G_PRECISION) invoice_amount
825 ,sum(DECODE(jzvtd.reporting_code,'VAT-KA',DECODE(tax_recoverable_flag,'Y', nvl(jzvtd.tax_amt_funcl_curr,jzvtd.tax_amt), 0),0)) vat_on_fixed_assets
826 ,sum(DECODE(jzvtd.reporting_code,'VAT-KS',DECODE(tax_recoverable_flag,'Y', nvl(jzvtd.tax_amt_funcl_curr,jzvtd.tax_amt), 0),0)) vat_on_other_trxs
827 ,'K' class
828 ,0 mark_trx_flag
829 FROM jg_zz_vat_trx_details jzvtd
830 ,jg_zz_vat_rep_status jzvrs
831 ,ap_invoices_all apinv
832 WHERE jzvrs.source='AP'
833 AND jzvrs.vat_reporting_entity_id = P_VAT_REPORTING_ENTITY_ID
834 AND JZVTD.IL_VAT_REP_STATUS_ID = l_rep_status_id
835 -- AND jzvrs.tax_calendar_period = P_TAX_CALENDAR_PERIOD -- No period check
836 AND JZVRS.REPORTING_STATUS_ID = JZVTD.REPORTING_STATUS_ID
837 AND jzvrs.period_end_date <= l_cur_period_end_date
838 AND (P_VAT_TRX_TYPE is null or jzvtd.tax_rate_vat_trx_type_code = P_VAT_TRX_TYPE)
839 AND jzvtd.reporting_code IN ('VAT-KS','VAT-KA')
840 AND apinv.invoice_id = jzvtd.trx_id
841 GROUP BY jzvtd.billing_tp_name
842 ,jzvtd.billing_tp_number
843 ,jzvtd.billing_tp_site_name
844 ,NVL(jzvtd.billing_tp_site_tax_reg_num,jzvtd.billing_tp_tax_reg_num)
845 ,jzvtd.trx_number
846 ,jzvtd.trx_id
847 ,jzvtd.trx_date
848 ,DECODE(nvl2(apinv.cancelled_date,'Y','N'),'Y','CANCELLED',jzvtd.trx_line_class)
849 ,jzvtd.posted_flag
850
851 UNION ALL
852 -- VAT File Line Type - K
853 -- Reporting Code :
854 -- 'VAT-KS' - Other Trxs
855 -- Transactions that are not stamped for the subsequent period.
856 SELECT jzvtd.billing_tp_name vendor_name
857 ,jzvtd.billing_tp_number vendor_number
858 ,jzvtd.billing_tp_site_name site_name
859 ,NVL(jzvtd.billing_tp_site_tax_reg_num,jzvtd.billing_tp_tax_reg_num) tax_reg_num
860 ,jzvtd.trx_number inv_number
861 ,jzvtd.trx_date inv_date
862 ,NULL import_document_number
863 ,NULL import_document_date
864 ,DECODE(nvl2(apinv.cancelled_date,'Y','N'),'Y','CANCELLED',jzvtd.trx_line_class) trx_line_class
865 ,jzvtd.posted_flag
866 ,JG_ZZ_COMMON_PKG.get_amt_tot(jzvtd.trx_id,l_ledger_id,G_PRECISION) invoice_amount
867 ,sum(DECODE(jzvtd.reporting_code,'VAT-KA',DECODE(tax_recoverable_flag,'Y', nvl(jzvtd.tax_amt_funcl_curr,jzvtd.tax_amt), 0),0)) vat_on_fixed_assets
868 ,sum(DECODE(jzvtd.reporting_code,'VAT-KS',DECODE(tax_recoverable_flag,'Y', nvl(jzvtd.tax_amt_funcl_curr,jzvtd.tax_amt), 0),0)) vat_on_other_trxs
869 ,'K' class
870 ,1 mark_trx_flag
871 FROM jg_zz_vat_trx_details jzvtd
872 ,jg_zz_vat_rep_status jzvrs
873 ,ap_invoices_all apinv
874 WHERE jzvrs.source='AP'
875 AND jzvrs.vat_reporting_entity_id = P_VAT_REPORTING_ENTITY_ID
876 --AND jzvrs.tax_calendar_period = P_TAX_CALENDAR_PERIOD ( Other periods also possible)
877 AND JZVTD.IL_VAT_REP_STATUS_ID = -999
878 AND jzvrs.period_end_date <= l_cur_period_end_date
879 AND JZVRS.REPORTING_STATUS_ID = JZVTD.REPORTING_STATUS_ID
880 AND (P_VAT_TRX_TYPE is null or jzvtd.tax_rate_vat_trx_type_code = P_VAT_TRX_TYPE)
881 AND jzvtd.reporting_code IN ('VAT-KS','VAT-KA')
882 AND apinv.invoice_id = jzvtd.trx_id
883 GROUP BY jzvtd.billing_tp_name
884 ,jzvtd.billing_tp_number
885 ,jzvtd.billing_tp_site_name
886 ,NVL(jzvtd.billing_tp_site_tax_reg_num,jzvtd.billing_tp_tax_reg_num)
887 ,jzvtd.trx_number
888 ,jzvtd.trx_id
889 ,jzvtd.trx_date
890 ,DECODE(nvl2(apinv.cancelled_date,'Y','N'),'Y','CANCELLED',jzvtd.trx_line_class)
891 ,jzvtd.posted_flag
892 ORDER BY vendor_name
893 ,vendor_number
894 ,site_name
895 ,class
896 ,inv_date
897 ,inv_number;
898
899 -- Cursor for Israel VAT Non-Related to 835 File.
900 CURSOR israel_details IS
901 SELECT jzvtd.billing_tp_name vendor_name
902 ,jzvtd.billing_tp_number vendor_number
903 ,jzvtd.billing_tp_site_name site_name
904 ,NVL(jzvtd.billing_tp_site_tax_reg_num,jzvtd.billing_tp_tax_reg_num) tax_reg_num
905 ,jzvtd.trx_number inv_number
906 ,jzvtd.trx_date inv_date
907 ,NULL import_document_number
908 ,NULL import_document_date
909 ,DECODE(nvl2(apinv.cancelled_date,'Y','N'),'Y','CANCELLED',jzvtd.trx_line_class) trx_line_class
910 ,jzvtd.posted_flag
911 -- Bug 7683525 ,SUM(Round(nvl(jzvtd.tax_amt_funcl_curr,jzvtd.tax_amt),G_PRECISION) + DECODE(tax_recoverable_flag,'N',Round(nvl(jzvtd.taxable_amt_funcl_curr,jzvtd.taxable_amt),G_PRECISION),0)) invoice_amount
912 ,JG_ZZ_COMMON_PKG.get_amt_tot(jzvtd.trx_id,l_ledger_id,G_PRECISION) invoice_amount
913 ,sum(DECODE(tax_recoverable_flag,'Y',decode(jzvtd.reporting_code,'VAT-A',Round(nvl(jzvtd.tax_amt_funcl_curr,jzvtd.tax_amt),G_PRECISION),0),0)) s_vat_on_fixed_assets
914 ,0 p_vat_on_fixed_assets
915 ,0 i_vat_on_fixed_assets
916 ,sum(DECODE(tax_recoverable_flag,'Y',decode(jzvtd.reporting_code,'VAT-S',Round(nvl(jzvtd.tax_amt_funcl_curr,jzvtd.tax_amt),G_PRECISION),0),0)) s_vat_on_other_trx
917 ,0 p_vat_on_other_trx
918 ,0 i_vat_on_other_trx
919 ,sum(DECODE(tax_recoverable_flag,'Y',decode(jzvtd.reporting_code,'VAT-A',Round(nvl(jzvtd.tax_amt_funcl_curr,jzvtd.tax_amt),G_PRECISION),0),0)) vat_fixed_assets
920 ,sum(DECODE(tax_recoverable_flag,'Y',decode(jzvtd.reporting_code,'VAT-S',Round(nvl(jzvtd.tax_amt_funcl_curr,jzvtd.tax_amt),G_PRECISION),0),0)) vat_other_trx
921 ,'S' class
922 FROM jg_zz_vat_trx_details jzvtd
923 ,jg_zz_vat_rep_status jzvrs
924 ,ap_invoices_all apinv
925 WHERE jzvrs.source='AP'
926 AND jzvrs.vat_reporting_entity_id = P_VAT_REPORTING_ENTITY_ID
927 AND jzvrs.tax_calendar_period = P_TAX_CALENDAR_PERIOD
928 AND JZVRS.REPORTING_STATUS_ID = JZVTD.REPORTING_STATUS_ID
929 --AND jzvtd.tax_rate_register_type_code = 'TAX'
930 AND ( P_VAT_TRX_TYPE is null or jzvtd.tax_rate_vat_trx_type_code = P_VAT_TRX_TYPE)
931 AND jzvtd.reporting_code IN ('VAT-A','VAT-S')
932 AND apinv.invoice_id = jzvtd.trx_id
933 GROUP BY jzvtd.billing_tp_name
934 ,jzvtd.billing_tp_number
935 ,jzvtd.billing_tp_site_name
936 ,NVL(jzvtd.billing_tp_site_tax_reg_num,jzvtd.billing_tp_tax_reg_num)
937 ,jzvtd.trx_number
938 ,jzvtd.trx_id
939 ,jzvtd.trx_date
940 ,DECODE(nvl2(apinv.cancelled_date,'Y','N'),'Y','CANCELLED',jzvtd.trx_line_class)
941 ,jzvtd.posted_flag
942
943 UNION ALL
944
945 SELECT jzvtd.billing_tp_name vendor_name
946 ,jzvtd.billing_tp_number vendor_number
947 ,jzvtd.billing_tp_site_name site_name
948 ,NVL(jzvtd.billing_tp_site_tax_reg_num,jzvtd.billing_tp_tax_reg_num) tax_reg_num
949 ,jzvtd.trx_number inv_number
950 ,jzvtd.trx_date inv_date
951 ,NULL import_document_number
952 ,NULL import_document_date
953 ,DECODE(nvl2(apinv.cancelled_date,'Y','N'),'Y','CANCELLED',jzvtd.trx_line_class) trx_line_class
954 ,jzvtd.posted_flag
955 -- Bug 7683525 ,SUM(Round(nvl(jzvtd.tax_amt_funcl_curr,jzvtd.tax_amt),G_PRECISION) + DECODE(tax_recoverable_flag,'N',Round(nvl(jzvtd.taxable_amt_funcl_curr,jzvtd.taxable_amt),G_PRECISION),0)) invoice_amount
956 ,JG_ZZ_COMMON_PKG.get_amt_tot(jzvtd.trx_id,l_ledger_id,G_PRECISION) invoice_amount
957 ,0 s_vat_on_fixed_assets
958 ,sum(DECODE(tax_recoverable_flag,'Y',decode(jzvtd.reporting_code,'VAT-KA',Round(nvl(jzvtd.tax_amt_funcl_curr,jzvtd.tax_amt),G_PRECISION),0),0)) p_vat_on_fixed_assets
959 ,0 i_vat_on_fixed_assets
960 ,0 s_vat_on_other_trx
961 ,sum(DECODE(tax_recoverable_flag,'Y',decode(jzvtd.reporting_code,'VAT-KS',Round(nvl(jzvtd.tax_amt_funcl_curr,jzvtd.tax_amt),G_PRECISION),0),0)) p_vat_on_other_trx
962 ,0 i_vat_on_other_trx
963 ,sum(DECODE(tax_recoverable_flag,'Y',decode(jzvtd.reporting_code,
964 'VAT-KA',Round(nvl(jzvtd.tax_amt_funcl_curr,jzvtd.tax_amt),G_PRECISION),0),0)) vat_fixed_assets
965 ,sum(DECODE(tax_recoverable_flag,'Y',decode(jzvtd.reporting_code,
966 'VAT-KS',Round(nvl(jzvtd.tax_amt_funcl_curr,jzvtd.tax_amt),G_PRECISION),0),0)) vat_other_trx
967 ,'P' class
968 FROM jg_zz_vat_trx_details jzvtd
969 ,jg_zz_vat_rep_status jzvrs
970 ,ap_invoices_all apinv
971 WHERE jzvrs.source='AP'
972 AND jzvrs.vat_reporting_entity_id = P_VAT_REPORTING_ENTITY_ID
973 AND jzvrs.tax_calendar_period = P_TAX_CALENDAR_PERIOD
974 AND JZVRS.REPORTING_STATUS_ID = JZVTD.REPORTING_STATUS_ID
975 --AND jzvtd.tax_rate_register_type_code = 'TAX'
976 AND ( P_VAT_TRX_TYPE is null or jzvtd.tax_rate_vat_trx_type_code = P_VAT_TRX_TYPE)
977 AND jzvtd.reporting_code IN ('VAT-KA','VAT-KS')
978 AND apinv.invoice_id = jzvtd.trx_id
979 GROUP BY jzvtd.billing_tp_name
980 ,jzvtd.billing_tp_number
981 ,jzvtd.billing_tp_site_name
982 ,NVL(jzvtd.billing_tp_site_tax_reg_num,jzvtd.billing_tp_tax_reg_num)
983 ,jzvtd.trx_number
984 ,jzvtd.trx_id
985 ,jzvtd.trx_date
986 -- ,jzvtd.import_document_number
987 -- ,jzvtd.import_document_date
988 ,DECODE(nvl2(apinv.cancelled_date,'Y','N'),'Y','CANCELLED',jzvtd.trx_line_class)
989 ,jzvtd.posted_flag
990
991 UNION ALL
992
993 SELECT jzvtd.billing_tp_name vendor_name
994 ,jzvtd.billing_tp_number vendor_number
995 ,jzvtd.billing_tp_site_name site_name
996 ,NVL(jzvtd.billing_tp_site_tax_reg_num,jzvtd.billing_tp_tax_reg_num) tax_reg_num
997 ,jzvtd.trx_number inv_number
998 ,DECODE(length(nvl(apinvl.GLOBAL_ATTRIBUTE13,'A')),
999 1,jzvtd.trx_date,
1000 19,TO_DATE(apinvl.GLOBAL_ATTRIBUTE13,'RRRR/MM/DD hh24:mi:ss'),
1001 to_date(apinvl.GLOBAL_ATTRIBUTE13, 'DD-MM-RRRR')) inv_date
1002 ,NVL(apinvl.GLOBAL_ATTRIBUTE14,apinv.invoice_num) import_document_number
1003 ,apinvl.GLOBAL_ATTRIBUTE13 import_document_date
1004 ,DECODE(nvl2(apinv.cancelled_date,'Y','N'),'Y','CANCELLED',jzvtd.trx_line_class) trx_line_class
1005 ,jzvtd.posted_flag
1006 -- Bug 7683525 ,SUM(Round(nvl(jzvtd.tax_amt_funcl_curr,jzvtd.tax_amt),G_PRECISION) + DECODE(tax_recoverable_flag,'N',Round(nvl(jzvtd.taxable_amt_funcl_curr,jzvtd.taxable_amt),G_PRECISION),0)) invoice_amount
1007 ,JG_ZZ_COMMON_PKG.get_amt_tot(jzvtd.trx_id,l_ledger_id,G_PRECISION) invoice_amount
1008 ,0 s_vat_on_fixed_assets
1009 ,0 p_vat_on_fixed_assets
1010 ,sum(DECODE(tax_recoverable_flag,'Y',decode(jzvtd.reporting_code,'VAT-RA',Round(nvl(jzvtd.tax_amt_funcl_curr,jzvtd.tax_amt),G_PRECISION),0),0)) i_vat_on_fixed_assets
1011 ,0 s_vat_on_other_trx
1012 ,0 p_vat_on_other_trx
1013 ,sum(DECODE(tax_recoverable_flag,'Y',decode(jzvtd.reporting_code,'VAT-RS',Round(nvl(jzvtd.tax_amt_funcl_curr,jzvtd.tax_amt),G_PRECISION),0),0)) i_vat_on_other_trx
1014 ,sum(DECODE(tax_recoverable_flag,'Y',decode(jzvtd.reporting_code,
1015 'VAT-RA',Round(nvl(jzvtd.tax_amt_funcl_curr,jzvtd.tax_amt),G_PRECISION),0),0)) vat_fixed_assets
1016 ,sum(DECODE(tax_recoverable_flag,'Y',decode(jzvtd.reporting_code,
1017 'VAT-RS',Round(nvl(jzvtd.tax_amt_funcl_curr,jzvtd.tax_amt),G_PRECISION),0),0)) vat_other_trx
1018 , 'I' class
1019 FROM jg_zz_vat_trx_details jzvtd
1020 ,jg_zz_vat_rep_status jzvrs
1021 ,ap_invoices_all apinv
1022 ,ap_invoice_lines_all apinvl
1023 ,zx_lines zxl
1024 WHERE jzvrs.source='AP'
1025 AND jzvrs.vat_reporting_entity_id = P_VAT_REPORTING_ENTITY_ID
1026 AND jzvrs.tax_calendar_period = P_TAX_CALENDAR_PERIOD
1027 AND JZVRS.REPORTING_STATUS_ID = JZVTD.REPORTING_STATUS_ID
1028 -- AND jzvtd.tax_rate_register_type_code = 'TAX'
1029 AND ( P_VAT_TRX_TYPE is null or jzvtd.tax_rate_vat_trx_type_code = P_VAT_TRX_TYPE)
1030 AND jzvtd.reporting_code IN ('VAT-RA','VAT-RS')
1031 AND apinv.invoice_id = jzvtd.trx_id
1032 AND zxl.trx_id= apinv.invoice_id
1033 AND zxl.trx_id= apinvl.invoice_id(+)
1034 AND zxl.tax_line_id =jzvtd.tax_line_id
1035 AND zxl.summary_tax_line_id = apinvl.summary_tax_line_id(+)
1036 -- AND apinvl.line_type_lookup_code = 'TAX'
1037 GROUP BY jzvtd.billing_tp_name
1038 ,jzvtd.billing_tp_number
1039 ,jzvtd.billing_tp_site_name
1040 ,NVL(jzvtd.billing_tp_site_tax_reg_num,jzvtd.billing_tp_tax_reg_num)
1041 ,jzvtd.trx_number
1042 ,jzvtd.trx_id
1043 ,DECODE(length(nvl(apinvl.GLOBAL_ATTRIBUTE13,'A')),
1044 1,jzvtd.trx_date,
1045 19,TO_DATE(apinvl.GLOBAL_ATTRIBUTE13,'RRRR/MM/DD hh24:mi:ss'),
1046 to_date(apinvl.GLOBAL_ATTRIBUTE13, 'DD-MM-RRRR'))
1047 ,NVL(apinvl.GLOBAL_ATTRIBUTE14,apinv.invoice_num)
1048 ,apinvl.GLOBAL_ATTRIBUTE13
1049 ,DECODE(nvl2(apinv.cancelled_date,'Y','N'),'Y','CANCELLED',jzvtd.trx_line_class)
1050 ,jzvtd.posted_flag;
1051
1052
1053 BEGIN
1054
1055 p_where_clause := ' AND 1 = 1 ';
1056
1057 if g_debug = true then fnd_file.put_line(fnd_file.log,'Calling JG_ZZ_COMMON_PKG.funct_curr_legal'); end if;
1058 JG_ZZ_COMMON_PKG.funct_curr_legal(l_curr_code
1059 ,l_rep_entity_name
1060 ,l_legal_entity_id
1061 ,l_taxpayer_id
1062 ,P_VAT_REPORTING_ENTITY_ID
1063 , pv_period_name => p_tax_calendar_period /* UT TEST addition */
1064 );
1065 if g_debug = true then fnd_file.put_line(fnd_file.log,'Calling JG_ZZ_COMMON_PKG.company_detail'); end if;
1066 JG_ZZ_COMMON_PKG.company_detail(x_company_name => l_company_name
1067 ,x_registration_number =>l_registration_number
1068 ,x_country => l_country
1069 ,x_address1 => l_address1
1070 ,x_address2 => l_address2
1071 ,x_address3 => l_address3
1072 ,x_address4 => l_address4
1073 ,x_city => l_city
1074 ,x_postal_code => l_postal_code
1075 ,x_contact => l_contact
1076 ,x_phone_number => l_phone_number
1077 ,x_province => l_province
1078 ,x_comm_number => l_comm_num
1079 ,x_vat_reg_num => l_vat_reg_num
1080 ,pn_legal_entity_id => l_legal_entity_id
1081 ,p_vat_reporting_entity_id => P_VAT_REPORTING_ENTITY_ID);
1082
1083 if g_debug = true then fnd_file.put_line(fnd_file.log,'Calling jg_zz_common_pkg.tax_registration'); end if;
1084 jg_zz_common_pkg.tax_registration(x_tax_registration => l_tax_registration_num
1085 ,x_period_start_date => l_period_start_date
1086 ,x_period_end_date => l_period_end_date
1087 ,x_status => l_reporting_status
1088 ,pn_vat_rep_entity_id => p_vat_reporting_entity_id
1089 ,pv_period_name => p_tax_calendar_period
1090 ,pv_source => 'AP');
1091
1092 l_reporting_status := JG_ZZ_VAT_REP_UTILITY.get_period_status(pn_vat_reporting_entity_id => p_vat_reporting_entity_id
1093 ,pv_tax_calendar_period => p_tax_calendar_period
1094 ,pv_tax_calendar_year => null
1095 ,pv_source => NULL
1096 ,pv_report_name => p_calling_report);
1097
1098 --- Bug 12773668 ---
1099 select meaning
1100 into l_reporting_status_1
1101 from fnd_lookups
1102 where lookup_type = 'JGZZ_REPORT_TYPE'
1103 and lookup_code = decode(l_reporting_status, 'COPY', 'R','FINAL','F','P');
1104 --- End Bug 12773668 ---
1105
1106 -- Bug 8285537. Exception handled
1107 BEGIN
1108 select distinct nvl(jzvre.ledger_id,0)
1109 , jzvre.entity_type_code
1110 into l_ledger_id,l_entity_type_code
1111 from jg_zz_vat_rep_entities jzvre
1112 where jzvre.vat_reporting_entity_id = P_VAT_REPORTING_ENTITY_ID;
1113 EXCEPTION
1114 WHEN OTHERS THEN
1115 l_ledger_id := 0;
1116 l_entity_type_code := 'NO_ENTITY_TYPE_CODE';
1117 END;
1118 -- Bug 8285537. Exception handled
1119 BEGIN
1120 select DISTINCT ledger_category_code
1121 into l_ledger_category_code
1122 from gl_ledgers
1123 where ledger_id = l_ledger_id;
1124 EXCEPTION
1125 WHEN OTHERS THEN
1126 l_ledger_category_code := 'NO_CATEGORY_CODE';
1127 END;
1128
1129 IF ( l_entity_type_code = 'LEGAL' OR ( l_entity_type_code = 'ACCOUNTING' AND l_ledger_category_code = 'PRIMARY' ))
1130 THEN
1131 l_ledger_id := -1;
1132 END IF;
1133
1134 INSERT INTO JG_ZZ_VAT_TRX_GT
1135 (jg_info_v1 -- curr_code
1136 ,jg_info_v2 -- entity_name
1137 ,jg_info_v3 -- taxpayer_id
1138 ,jg_info_v4 -- company_name
1139 ,jg_info_v5 -- registration_number
1140 ,jg_info_v6 -- country
1141 ,jg_info_v7 -- address1
1142 ,jg_info_v8 -- address2
1143 ,jg_info_v9 -- address3
1144 ,jg_info_v10 -- address4
1145 ,jg_info_v11 -- city
1146 ,jg_info_v12 -- postal_code
1147 ,jg_info_v13 -- contact
1148 ,jg_info_v14 -- phone_number
1149 ,jg_info_v30 -- Header record indicator
1150 ,jg_info_v15 --Tax Registration Number
1151 ,jg_info_d1 --Period start date
1152 ,jg_info_d2 --Period end date
1153 ,jg_info_v16 ) --Reporting Status
1154 VALUES(
1155 l_curr_code
1156 ,l_company_name ---l_rep_entity_name
1157 ,l_registration_number --l_taxpayer_id
1158 ,l_company_name
1159 ,l_tax_registration_num
1160 ,l_country
1161 ,l_address1
1162 ,l_address2
1163 ,l_address3
1164 ,l_address4
1165 ,l_city
1166 ,l_postal_code
1167 ,l_contact
1168 ,l_phone_number
1169 ,'H'
1170 ,l_tax_registration_num
1171 ,l_period_start_date
1172 ,l_period_end_date
1173 --,l_reporting_status); Bug 12773668
1174 ,l_reporting_status_1); --Bug 12773668
1175 if g_debug = true then fnd_file.put_line(fnd_file.log,'Inserted Company Details in JG_ZZ_VAT_TRX_GT table'); end if;
1176
1177 IF P_CALLING_REPORT IS NOT NULL THEN --A1
1178
1179 /* commented during UT TEST
1180 l_country_code := jg_zz_shared_pkg.GET_COUNTRY(mo_global.get_current_org_id);
1181 */
1182 -- Populate data for the other two sections
1183
1184 /* added during UT */
1185 l_country_code := jg_zz_shared_pkg.GET_COUNTRY;
1186 if l_country_code is null then
1187 l_country_code := jg_zz_shared_pkg.GET_COUNTRY(mo_global.get_current_org_id);
1188 end if;
1189
1190 if g_debug = true then fnd_file.put_line(fnd_file.log, 'l_country_code:'||l_country_code); end if;
1191
1192 IF P_VAT_TRX_TYPE IS NOT NULL THEN
1193 BEGIN
1194 FOR c_lookup_type IN c_get_lookup_values(P_VAT_TRX_TYPE)
1195 LOOP
1196 G_VAT_TRX_TYPE_MEANING := c_lookup_type.meaning;
1197 G_VAT_TRX_TYPE_DESC := c_lookup_type.description;
1198 END LOOP;
1199 EXCEPTION
1200 WHEN OTHERS THEN
1201 if g_debug = true then fnd_file.put_line(fnd_file.log,' Failed while extracting the transaction type meaning. Error : ' || SUBSTR(SQLERRM,1,200)); end if;
1202 END;
1203 END IF;
1204
1205 IF P_EX_VAT_TRX_TYPE IS NOT NULL THEN
1206 BEGIN
1207 FOR c_lookup_type_ex IN c_get_lookup_values(P_EX_VAT_TRX_TYPE)
1208 LOOP
1209 G_EX_VAT_TRX_TYPE_MEANING := c_lookup_type_ex.meaning;
1210 G_EX_VAT_TRX_TYPE_DESC := c_lookup_type_ex.description;
1211 END LOOP;
1212 EXCEPTION
1213 WHEN OTHERS THEN
1214 if g_debug = true then fnd_file.put_line(fnd_file.log,' Failed while extracting the exclude transaction type meaning. Error : ' || SUBSTR(SQLERRM,1,200)); end if;
1215 END;
1216
1217 END IF;
1218
1219 BEGIN
1220
1221 FOR c_company IN c_company_details
1222 LOOP
1223 G_COMPANY_NAME := c_company.Company_Name;
1224 G_FUNCTIONAL_CURRENCY := c_company.FUNCTIONAL_CURRENCY_CODE;
1225 END LOOP;
1226
1227 EXCEPTION
1228 WHEN OTHERS THEN
1229 if g_debug = true then fnd_file.put_line(fnd_file.log,' Failed while extracting the company name and the functional currency code. Error : ' || SUBSTR(SQLERRM,1,200)); end if;
1230 END;
1231
1232 /* Get Currency Precision */
1233
1234 BEGIN
1235 FND_FILE.PUT_LINE(FND_FILE.LOG,'Functional Currency Code :'||G_FUNCTIONAL_CURRENCY);
1236
1237 SELECT precision
1238 INTO G_PRECISION
1239 FROM fnd_currencies
1240 WHERE currency_code = G_FUNCTIONAL_CURRENCY;
1241
1242 FND_FILE.PUT_LINE(FND_FILE.LOG,'Functional Currency Precision :'||G_PRECISION);
1243
1244 EXCEPTION
1245 WHEN OTHERS THEN
1246 FND_FILE.PUT_LINE(FND_FILE.LOG,'error in getting currency precision');
1247 END;
1248
1249 -- Begin of code for Israeli reports
1250
1251 --Populated the data for VAT AP Detail Report
1252 IF ( P_CALLING_REPORT = 'JEILAPVR') THEN
1253 if g_debug = true then
1254 fnd_file.put_line(fnd_file.log,'Israeli VAT AP Detail Register Report concurrent is submitted');
1255 end if;
1256
1257 -- IL VAT Reporting 2010 ER - Logic Changes
1258
1259 -- Get the limits defined for the tax period.
1260 FOR cur_get_limits IN il_get_limits
1261 LOOP
1262 l_percentage := cur_get_limits.max_recoverable_percentage;
1263 l_max_rec_amt := cur_get_limits.max_recoverable_amt;
1264 l_min_rec_amt := cur_get_limits.min_recoverable_amt;
1265 l_calendar_name := cur_get_limits.period_set_name;
1266 END LOOP;
1267
1268 IF l_calendar_name IS NULL THEN
1269 fnd_file.put_line(fnd_file.log,'Please declare the VAT limits for the tax period '||p_tax_calendar_period||' for calendar in the Israel VAT Limits Setup form.');
1270 raise_application_error(-20010,'Please declare the VAT limits for the tax period '||p_tax_calendar_period||' for calendar in the Israel VAT Limits Setup form.');
1271 END IF;
1272 if g_debug = true then
1273 fnd_file.put_line(fnd_file.log,'Percentage =' || TO_CHAR(l_percentage));
1274 fnd_file.put_line(fnd_file.log,'Max. Rec. Amount =' || TO_CHAR(l_max_rec_amt));
1275 fnd_file.put_line(fnd_file.log,'Min. Rec. Amount =' || TO_CHAR(l_min_rec_amt));
1276 fnd_file.put_line(fnd_file.log,'Tax Calendar Name =' || l_calendar_name);
1277 end if;
1278
1279 -- Get the reporting status id
1280 FOR cur_get_rep_status_id IN il_get_rep_status_id
1281 LOOP
1282 l_rep_status_id := cur_get_rep_status_id.reporting_status_id;
1283 l_cur_period_end_date := cur_get_rep_status_id.period_end_date;
1284 END LOOP;
1285
1286 if g_debug = true then
1287 fnd_file.put_line(fnd_file.log,'Current Reporting Status ID = ' || TO_CHAR(l_rep_status_id));
1288 end if;
1289
1290 IF l_rep_status_id IS NULL THEN
1291 RETURN (FALSE);
1292 END IF;
1293
1294 -- If Percentage is defined then calculate the max_rec_amt.
1295 IF l_percentage IS NOT NULL THEN
1296 -- Get the total vat amt for the declared tax period. SUM('VAT-A','VAT-S',
1297 -- 'VAT-C', 'VAT-RA', 'VAT-P', 'VAT-H')
1298 FOR cur_get_total_vat_amt IN il_get_total_vat_amt
1299 LOOP
1300 l_total_vat_amt := cur_get_total_vat_amt.TOTAL_VAT_AMT;
1301 END LOOP;
1302 if g_debug = true then
1303 fnd_file.put_line(fnd_file.log,'Total VAT Amount = ' || TO_CHAR(l_total_vat_amt));
1304 end if;
1305 -- Find the Maximum Recoverable Amount from Percentage.
1306 l_max_rec_amt := NVL(l_total_vat_amt,0) * l_percentage / 100;
1307 if g_debug = true then
1308 fnd_file.put_line(fnd_file.log,'Max Recoverable Amount:'||to_char(l_max_rec_amt));
1309 end if;
1310 END IF;
1311
1312 IF l_max_rec_amt IS NULL OR l_min_rec_amt IS NULL THEN
1313 fnd_file.put_line(fnd_file.log,'Please declare the Minimum and/or Maximum Recoverable Amount limits for the tax period in the Israel VAT Limits Setup form.');
1314 raise_application_error(-20010,'Please declare the Minimum and/or Maximum Recoverable Amount limits for the tax period in the Israel VAT Limits Setup form.');
1315 RETURN (FALSE);
1316 END IF;
1317
1318 -- Get the total petty cash vat amt for the declared tax period. SUM (VAT-KS)
1319 FOR cur_get_petty_cash_vat_amt IN il_get_petty_cash_vat_amt
1320 LOOP
1321 l_petty_cash_vat_amt := cur_get_petty_cash_vat_amt.TOTAL_PETTY_CASH_VAT_AMT;
1322 END LOOP;
1323 if g_debug = true then
1324 fnd_file.put_line(fnd_file.log,'Total PETTY CASH Amount = ' || TO_CHAR(l_petty_cash_vat_amt));
1325 end if;
1326
1327 -- Find the Final VAT Amount
1328 IF l_max_rec_amt > l_min_rec_amt THEN
1329 IF l_petty_cash_vat_amt > l_max_rec_amt THEN
1330 l_declared_amount := l_max_rec_amt;
1331 ELSE
1332 l_declared_amount := l_petty_cash_vat_amt;
1333 END IF;
1334 ELSE
1335 IF l_petty_cash_vat_amt > l_min_rec_amt THEN
1336 l_declared_amount := l_min_rec_amt;
1337 ELSE
1338 l_declared_amount := l_petty_cash_vat_amt;
1339 END IF;
1340 END IF;
1341
1342 if g_debug = true then
1343 fnd_file.put_line(fnd_file.log,'Declared Amount = ' || TO_CHAR(l_declared_amount));
1344 end if;
1345
1346 -- Compare the petty cash trxs vat amt with limits.
1347 FOR cur_get_petty_cash_trxs IN il_get_petty_cash_trxs
1348 LOOP
1349
1350 l_cum_total_amt := cur_get_petty_cash_trxs.cum_total_amt;
1351
1352 IF l_cum_total_amt <= l_declared_amount THEN
1353 rep_status_id_rec_tab(l_count) := cur_get_petty_cash_trxs.reporting_status_id;
1354 trx_number_rec_tab(l_count) := cur_get_petty_cash_trxs.trx_number;
1355 trx_date_rec_tab(l_count) := cur_get_petty_cash_trxs.trx_date;
1356 il_rep_status_rec_tab(l_count) := l_rep_status_id;
1357 ELSE
1358 rep_status_id_rec_tab(l_count) := cur_get_petty_cash_trxs.reporting_status_id;
1359 trx_number_rec_tab(l_count) := cur_get_petty_cash_trxs.trx_number;
1360 trx_date_rec_tab(l_count) := cur_get_petty_cash_trxs.trx_date;
1361 il_rep_status_rec_tab(l_count) := -999;
1362 END IF;
1363 l_count := l_count + 1;
1364 END LOOP;
1365
1366 -- Stamp the petty cash transactions. Update the IL_REP_STATUS_ID column in JG_ZZ_VAT_TRX_DETAILS table.
1367 IF l_count > 1 THEN
1368 BEGIN
1369 FORALL i_index IN 1 .. l_count-1
1370 UPDATE JG_ZZ_VAT_TRX_DETAILS
1371 SET IL_VAT_REP_STATUS_ID = il_rep_status_rec_tab(i_index)
1372 WHERE REPORTING_STATUS_ID = rep_status_id_rec_tab(i_index)
1373 AND TRX_NUMBER = trx_number_rec_tab(i_index)
1374 AND TRX_DATE = trx_date_rec_tab(i_index);
1375 EXCEPTION
1376 WHEN OTHERS THEN
1377 if g_debug = true then
1378 fnd_file.put_line(fnd_file.log,' Failed while Updating IL_VAT_REP_STATUS_ID column in JG_ZZ_VAT_TRX_DETAILS table. Error : ' || SUBSTR(SQLERRM,1,200));
1379 end if;
1380 RETURN (FALSE);
1381 END;
1382 if g_debug = true then
1383 fnd_file.put_line(fnd_file.log,'Stamped IL_VAT_REP_STATUS_ID column for petty cash trxs');
1384 end if;
1385 END IF;
1386
1387 -- IL VAT Reporting 2010 ER - Logic Changes
1388
1389 BEGIN
1390 -- IL VAT Reporting 2010 ER - Logic Changes
1391 --Populated the data for Israel VAT AP Detail Report.
1392 FOR cur_israel_new_details IN israel_new_details
1393 LOOP
1394
1395 INSERT INTO JG_ZZ_VAT_TRX_GT
1396 (jg_info_v1 , --vendor_name
1397 jg_info_v6 , --Vendor Number
1398 jg_info_v2 , --SITE_NAME
1399 jg_info_v10 , --TAX_REG_NUM
1400 jg_info_v11 , --INV_NUMBER
1401 jg_info_d1 , --INV_DATE
1402 jg_info_v12 , --IMPORT_DOCUMENT_NUMBER
1403 jg_info_v3 , --TRX_LINE_CLASS
1404 jg_info_v4 , --POSTED_FLAG
1405 jg_info_n5 , --INVOICE_AMOUNT
1406 jg_info_n12 , --VAT_ON_FIXED_ASSETS
1407 jg_info_n13 , --VAT_ON_OTHER_TRXS
1408 jg_info_v5 , --CLASS
1409 jg_info_n1 --MARK_TRX_FLAG
1410 )
1411 VALUES(
1412 cur_israel_new_details.vendor_name
1413 ,cur_israel_new_details.vendor_number
1414 ,cur_israel_new_details.site_name
1415 ,cur_israel_new_details.tax_reg_num
1416 ,cur_israel_new_details.inv_number
1417 ,cur_israel_new_details.inv_date
1418 ,cur_israel_new_details.import_document_number
1419 ,cur_israel_new_details.trx_line_class
1420 ,cur_israel_new_details.posted_flag
1421 ,cur_israel_new_details.invoice_amount
1422 ,cur_israel_new_details.vat_on_fixed_assets
1423 ,cur_israel_new_details.vat_on_other_trxs
1424 ,cur_israel_new_details.class
1425 ,cur_israel_new_details.mark_trx_flag);
1426 END LOOP;
1427 EXCEPTION
1428 WHEN OTHERS THEN
1429 if g_debug = true then fnd_file.put_line(fnd_file.log,' Failed while Inserting the ISRAEL data into GTT. Error : ' || SUBSTR(SQLERRM,1,200)); end if;
1430 RETURN (FALSE);
1431 END;
1432
1433 -- Code for Israel VAT Non-related to 835 report
1434
1435 ELSIF P_CALLING_REPORT = 'JEILN835' THEN
1436
1437 if g_debug = true then
1438 fnd_file.put_line(fnd_file.log,'Israeli VAT-File Not Related 835 Report concurrent is submitted');
1439 end if;
1440
1441 BEGIN
1442 --Populated the data for VAT-File Not Related 835 -Israel
1443 FOR israel_inv_lines IN israel_details
1444 LOOP
1445
1446 INSERT INTO JG_ZZ_VAT_TRX_GT
1447 (jg_info_v1 , --vendor_name
1448 -- jg_info_n1 , --vendor_number
1449 jg_info_v6 , --Vendor Number
1450 jg_info_v2 , --SITE_NAME
1451 jg_info_v10 , --TAX_REG_NUM /* UT TEST jg_info_n2 => jg_info_v10*/
1452 jg_info_v11 , --INV_NUMBER /* UT TEST jg_info_n3 => jg_info_v11*/
1453 jg_info_d1 , --INV_DATE
1454 jg_info_v12 , --IMPORT_DOCUMENT_NUMBER /* UT TEST jg_info_n4 => jg_info_v12*/
1455 jg_info_d2 , --IMPORT_DOCUMENT_DATE
1456 jg_info_v3 , --TRX_LINE_CLASS
1457 jg_info_v4 , --POSTED_FLAG
1458 jg_info_n5 , --INVOICE_AMOUNT
1459 jg_info_n6 , --S_VAT_ON_FIXED_ASSETS
1460 jg_info_n7 , --P_VAT_ON_FIXED_ASSETS
1461 jg_info_n8 , --I_VAT_ON_FIXED_ASSETS
1462 jg_info_n9 , --S_VAT_ON_OTHER_TRX
1463 jg_info_n10 , --P_VAT_ON_OTHER_TRX
1464 jg_info_n11 , --I_VAT_ON_OTHER_TRX
1465 jg_info_n12 , --VAT_FIXED_ASSETS
1466 jg_info_n13 , --VAT_OTHER_TRX
1467 jg_info_v5 --CLASS
1468 )
1469 VALUES(
1470 israel_inv_lines.vendor_name
1471 ,israel_inv_lines.vendor_number
1472 ,israel_inv_lines.site_name
1473 ,israel_inv_lines.tax_reg_num
1474 ,israel_inv_lines.inv_number
1475 ,israel_inv_lines.inv_date
1476 ,israel_inv_lines.import_document_number
1477 ,israel_inv_lines.import_document_date
1478 ,israel_inv_lines.trx_line_class
1479 ,israel_inv_lines.posted_flag
1480 ,israel_inv_lines.invoice_amount
1481 ,israel_inv_lines.s_vat_on_fixed_assets
1482 ,israel_inv_lines.p_vat_on_fixed_assets
1483 ,israel_inv_lines.i_vat_on_fixed_assets
1484 ,israel_inv_lines.s_vat_on_other_trx
1485 ,israel_inv_lines.p_vat_on_other_trx
1486 ,israel_inv_lines.i_vat_on_other_trx
1487 ,israel_inv_lines.vat_fixed_assets
1488 ,israel_inv_lines.vat_other_trx
1489 ,israel_inv_lines.class);
1490 END LOOP;
1491 EXCEPTION
1492 WHEN OTHERS THEN
1493 if g_debug = true then fnd_file.put_line(fnd_file.log,' Failed while Inserting the ISRAEL data into GTT. Error : ' || SUBSTR(SQLERRM,1,200)); end if;
1494 END;
1495 -- End of code for Israeli reports
1496
1497 -- Begin of code for ECE and Crotia reports.
1498
1499 ELSE -- i.e P_CALLING_REPORT <> 'JEILAPVR' and JEILN835
1500
1501 BEGIN
1502 FOR c_data_rec1 IN c_new
1503 LOOP
1504
1505 FOR c_vat_offset IN c_vat_or_offset (c_data_rec1.tax_rate_code)
1506 LOOP
1507 l_vat_or_offset := c_vat_offset.vat_or_offset;
1508 END LOOP;
1509
1510 l_unpaid_amount := unpaid_amt(pn_inv_id => c_data_rec1.trx_id
1511 ,pn_inv_amount => c_data_rec1.total_accounted_amount
1512 ,pn_taxable_amount => c_data_rec1.taxable_amount
1513 ,pv_offset_tax_code => c_data_rec1.OFFSET_TAX_RATE_CODE
1514 ,pd_end_date => c_data_rec1.PERIOD_END_DATE);
1515
1516
1517 INSERT INTO JG_ZZ_VAT_TRX_GT
1518 (jg_info_n1 -- seq_number
1519 ,jg_info_v1 -- inv_number
1520 ,jg_info_v2 -- customer_name
1521 ,jg_info_d1 -- tax_date
1522 ,jg_info_d2 -- inv_date
1523 ,jg_info_d3 -- accounting_date
1524 ,jg_info_v3 -- tax_code_description
1525 ,jg_info_n2 -- taxable_amount
1526 ,jg_info_n3 -- tax_rate
1527 ,jg_info_n4 -- tax_amount
1528 ,jg_info_n5 -- trx_id , invoice_id
1529 ,jg_info_v4 -- trx_class_code
1530 ,jg_info_v5 -- trx_currency_code
1531 ,jg_info_n6 -- Recoverable Tax
1532 ,jg_info_n7 -- Non-Recoverable Tax
1533 ,jg_info_v6 -- Tax Code Description
1534 ,jg_info_n8 -- Functional Amount
1535 ,jg_info_n10 -- Transaction Amount
1536 ,jg_info_n13 -- Transaction Amount Offset
1537 ,jg_info_v9 -- Rec flag
1538 ,jg_info_v10 -- Doc Sequence Name
1539 ,jg_info_v11 -- tax_reg_num
1540 ,jg_info_v12 -- tax_code_vat_trx_type_code
1541 ,jg_info_v13 -- TAX_CODE_VAT_TRX_TYPE_MEANING
1542 ,jg_info_v14 -- TAX_CODE
1543 ,jg_info_v7 -- TAX_RATE_VAT_TRX_TYPE_CODE
1544 ,jg_info_n12
1545 ,jg_info_v15 -- Reporting_Code
1546 ,jg_info_n15 -- Pure VAT or VAT with Offset derived in the variable l_vat_or_offset
1547 ,jg_info_v16 -- period_name
1548 ,jg_info_v17 -- offset_flag
1549 ,jg_info_v18 -- offset_tax_rate_code
1550 ,jg_info_v19 -- CHK_VAT_AMOUNT_PAID
1551 ,jg_info_n14 -- non_recoverable_unpaid amount
1552 )
1553 VALUES
1554 (c_data_rec1.seq_number
1555 ,c_data_rec1.inv_number
1556 ,c_data_rec1.customer_name
1557 ,c_data_rec1.tax_date
1558 ,c_data_rec1.inv_date
1559 ,c_data_rec1.gl_date
1560 ,c_data_rec1.tax_rate_code_description
1561 ,c_data_rec1.taxable_amount
1562 ,c_data_rec1.tax_rate
1563 ,c_data_rec1.tax_amount
1564 ,c_data_rec1.trx_id
1565 ,c_data_rec1.trx_line_class
1566 ,c_data_rec1.trx_currency_code
1567 ,c_data_rec1.recoverable
1568 ,c_data_rec1.non_recoverable
1569 ,c_data_rec1.tax_rate_code_description
1570 ,c_data_rec1.total_accounted_amount
1571 ,c_data_rec1.total_entered_amount
1572 ,c_data_rec1.taxable_entered_amount
1573 ,'M'
1574 ,c_data_rec1.doc_seq_name
1575 ,c_data_rec1.tax_reg_num
1576 ,c_data_rec1.tax_rate_vat_trx_type_desc
1577 ,c_data_rec1.tax_rate_code_vat_trx_type_mng
1578 ,c_data_rec1.tax_rate_code
1579 ,c_data_rec1.tax_rate_vat_trx_type_code
1580 ,decode(NVL(c_data_rec1.chk_vat_amount_paid, 'N'), 'N', 0, 1)
1581 ,c_data_rec1.reporting_code
1582 ,l_vat_or_offset
1583 ,c_data_rec1.gl_period
1584 ,c_data_rec1.offset_flag
1585 ,c_data_rec1.offset_tax_rate_code
1586 ,c_data_rec1.chk_vat_amount_paid
1587 ,l_unpaid_amount);
1588
1589 END LOOP;
1590 EXCEPTION
1591 WHEN OTHERS THEN
1592 if g_debug = true then fnd_file.put_line(fnd_file.log,' Failed while populating the data into the global tmp table for c_new. Error : ' || SUBSTR(SQLERRM,1,200)); end if;
1593 END;
1594
1595 -- Populate data for the Summary by GL period
1596 BEGIN
1597 FOR c_data_rec IN c_data
1598 LOOP
1599
1600 l_unpaid_amount := unpaid_amt(pn_inv_id => c_data_rec.trx_id
1601 ,pn_inv_amount => c_data_rec.total_accounted_amount
1602 ,pn_taxable_amount => c_data_rec.taxable_amount
1603 ,pv_offset_tax_code => c_data_rec.OFFSET_TAX_RATE_CODE
1604 ,pd_end_date => c_data_rec.PERIOD_END_DATE);
1605
1606 INSERT INTO JG_ZZ_VAT_TRX_GT
1607 (jg_info_n1 -- seq_number
1608 ,jg_info_v1 -- inv_number
1609 ,jg_info_v2 -- customer_name
1610 ,jg_info_d1 -- tax_date
1611 ,jg_info_d2 -- inv_date
1612 ,jg_info_d3 -- accounting_date
1613 ,jg_info_v3 -- tax_code_description
1614 ,jg_info_n2 -- taxable_amount
1615 ,jg_info_n3 -- tax_rate
1616 ,jg_info_n4 -- tax_amount
1617 ,jg_info_n5 -- trx_id
1618 ,jg_info_v4 -- trx_class_code
1619 ,jg_info_v5 -- trx_currency_code
1620 ,jg_info_n6 -- Recoverable Tax
1621 ,jg_info_n7 -- Non-Recoverable Tax
1622 ,jg_info_v6 -- Tax Code Description
1623 ,jg_info_n8 -- Functional Amount
1624 ,jg_info_n10 -- Transaction Amount
1625 ,jg_info_n13 -- Transaction Amount Offset
1626 ,jg_info_v9 -- Rec flag
1627 ,jg_info_v8 -- ACCOUNT FLEXFIELD
1628 ,jg_info_v10 -- Doc Sequence Name
1629 ,jg_info_v11 -- tax_reg_num
1630 ,jg_info_v12 -- tax_rate_vat_trx_type_desc
1631 ,jg_info_v13 -- tax_rate_code_vat_trx_type_mng
1632 ,jg_info_v14 -- TAX_RATE_CODE
1633 ,jg_info_v7 -- TAX_CODE_VAT_TRX_TYPE_CODE
1634 ,jg_info_n12
1635 ,jg_info_v15 -- REPORTING_CODE
1636 ,jg_info_v16 -- period_name
1637 ,jg_info_v17 -- offset_flag
1638 ,jg_info_v18 -- offset_tax_rate_code
1639 ,jg_info_v19 -- CHK_VAT_AMOUNT_PAID
1640 ,jg_info_n14 -- non_recoverable_unpaid amount
1641 )
1642 VALUES
1643 (c_data_rec.seq_number
1644 ,c_data_rec.inv_number
1645 ,c_data_rec.customer_name
1646 ,c_data_rec.tax_date
1647 ,c_data_rec.inv_date
1648 ,c_data_rec.gl_date
1649 ,c_data_rec.tax_rate_code_description
1650 ,c_data_rec.taxable_amount
1651 ,c_data_rec.tax_rate
1652 ,c_data_rec.tax_amount
1653 ,c_data_rec.trx_id
1654 ,c_data_rec.trx_line_class
1655 ,c_data_rec.trx_currency_code
1656 ,c_data_rec.recoverable
1657 ,c_data_rec.non_recoverable
1658 ,c_data_rec.tax_rate_code_description
1659 ,c_data_rec.total_accounted_amount
1660 ,c_data_rec.total_entered_amount
1661 ,c_data_rec.taxable_entered_amount
1662 ,'S'
1663 ,c_data_rec.account_flexfield
1664 ,c_data_rec.doc_seq_name
1665 ,c_data_rec.tax_reg_num
1666 ,c_data_rec.tax_rate_vat_trx_type_desc
1667 ,c_data_rec.tax_rate_code_vat_trx_type_mng
1668 ,c_data_rec.tax_rate_code
1669 ,c_data_rec.tax_rate_vat_trx_type_code
1670 ,decode(NVL(c_data_rec.chk_vat_amount_paid, 'N'), 'N', 0, 1)
1671 ,c_data_rec.reporting_code
1672 ,c_data_rec.gl_period
1673 ,c_data_rec.offset_flag
1674 ,c_data_rec.offset_tax_rate_code
1675 ,c_data_rec.chk_vat_amount_paid
1676 ,l_unpaid_amount);
1677
1678 END LOOP;
1679 EXCEPTION
1680 WHEN OTHERS THEN
1681 if g_debug = true then fnd_file.put_line(fnd_file.log,' Failed while populating the data into the global tmp table for c_data. Error : ' || SUBSTR(SQLERRM,1,200)); end if;
1682 END;
1683
1684 BEGIN
1685 FOR c_end_date IN c_period_end_date
1686 LOOP
1687 l_end_date := c_end_date.PERIOD_END_DATE;
1688 END LOOP;
1689
1690 EXCEPTION
1691 WHEN OTHERS THEN
1692 if g_debug = true then fnd_file.put_line(fnd_file.log,' Failed while selecting the period end date. Error : ' || SUBSTR(SQLERRM,1,200)); end if;
1693 END;
1694
1695 -- Implementing seq num ---------
1696 BEGIN
1697 SELECT NVL(enable_report_sequence_flag ,'N')
1698 INTO l_enable_report_sequence_flag
1699 FROM jg_zz_vat_rep_entities
1700 WHERE vat_reporting_entity_id = P_VAT_REPORTING_ENTITY_ID;
1701
1702 IF l_enable_report_sequence_flag = 'Y' THEN
1703
1704 FOR c_data IN temp_cur
1705 LOOP
1706 IF c_data.vat_code <> v_prev_vat_code or c_data.vat_code IS NULL THEN
1707 v_count := 0;
1708 END IF;
1709
1710 SELECT distinct JG_INFO_V40
1711 INTO v_is_seq_updated
1712 FROM JG_ZZ_VAT_TRX_GT T1
1713 WHERE T1.jg_info_n5 = c_data.trx_id
1714 AND T1.jg_info_v7 = c_data.vat_code
1715 AND T1.jg_info_v15 = c_data.reporting_code;
1716
1717 IF NVL(v_is_seq_updated,'N') <> 'Y' THEN
1718
1719 v_count := v_count+1;
1720
1721 UPDATE JG_ZZ_VAT_TRX_GT SET jg_info_n1 = v_count ,
1722 jg_info_v40 = 'Y'
1723 WHERE jg_info_n5 = c_data.trx_id
1724 AND jg_info_v7 = c_data.vat_code
1725 AND jg_info_v15 = c_data.reporting_code;
1726 END IF;
1727
1728 v_prev_vat_code := c_data.vat_code;
1729
1730 END LOOP;
1731 END IF;
1732 EXCEPTION
1733 WHEN OTHERS THEN
1734 fnd_file.put_line(fnd_file.log,' Failed while Implementing seq num : ' || SUBSTR(SQLERRM,1,200));
1735 END;
1736 -- End of Implementing seq num ---------
1737
1738 P_WHERE_CLAUSE := ' AND ';
1739
1740 -- Exclude paid transactions for the
1741 -- Annex Report
1742 l_cleared_select := '(SELECT NVL((SELECT NVL(SUM(pay.payment_base_amount),0)
1743 FROM ap_invoices_all INV, ap_invoice_payments_all PAY, ap_checks_all CHECKS
1744 WHERE inv.invoice_id = pay.invoice_id
1745 AND checks.check_id = pay.check_id
1746 AND jg_info_n5 = inv.invoice_id
1747 AND checks.status_lookup_code IN ( ''CLEARED'' , ''RECONCILED'' , ''CLEARED BUT UNACCOUNTED'', ''RECONCILED UNACCOUNTED'' )';
1748
1749 l_cleared_select1 := '* decode(jg_info_n15, NULL, jg_info_n8, jg_info_n2)
1750 / (SELECT inv1.base_amount
1751 FROM ap_invoices_all INV1
1752 WHERE jg_info_n5 = inv1.invoice_id ),0) from dual )';
1753
1754 l_unpaid_amt_select := 'AND (SELECT nvl(sum(amount_remaining),99999)
1755 FROM ap_invoices_all INV,
1756 ap_invoice_payments_all PAY,
1757 ap_checks_all CHECKS,
1758 ap_payment_schedules_all APS
1759 WHERE APS.invoice_id = inv.invoice_id
1760 AND inv.invoice_id = pay.invoice_id
1761 AND checks.check_id = pay.check_id
1762 AND jg_info_n5 = inv.invoice_id
1763 AND checks.status_lookup_code IN ( ''CLEARED'' , ''RECONCILED'' , ''CLEARED BUT UNACCOUNTED'', ''RECONCILED UNACCOUNTED'' )';
1764
1765
1766 IF P_VAT_TRX_TYPE IS NULL
1767 AND P_EX_VAT_TRX_TYPE IS NULL THEN
1768
1769 IF ((l_country_code = 'HU' OR l_country_code = 'PL')) THEN
1770
1771 P_WHERE_CLAUSE := P_WHERE_CLAUSE || ' (((jg_info_n4 > ' || l_cleared_select
1772 || ' AND trunc(checks.cleared_date) <= ''' || l_end_date
1773 || ''' AND NVL(jg_info_v19,''N'') = ''Y'') ' || l_cleared_select1
1774 || ' ) AND (jg_info_n12 = 1 ) AND jg_info_n15 IS NULL ' || l_unpaid_amt_select
1775 || ' AND trunc(checks.cleared_date) <= '''||l_end_date||''' ) > 0 AND '''||l_country_code||''' <> ''PL'')'
1776 || ' OR ((decode(jg_info_n15, NULL, jg_info_n8, jg_info_n2) > ' ||l_cleared_select
1777 || ' AND trunc(checks.cleared_date) <= ''' || l_end_date || ''' AND ( ''' || l_country_code || ''' = ''PL'' OR '
1778 || ' NVL(jg_info_v19,''N'') = ''N'' )) ' || l_cleared_select1 || ' ) AND (''' || l_country_code || ''' = ''PL'' OR '
1779 || 'jg_info_n12 = 0 ) ' || l_unpaid_amt_select ||' AND trunc(checks.cleared_date) <= ''' || l_end_date || ''' ) > 0 ))';
1780
1781 ELSIF (l_country_code = 'SK') THEN
1782
1783 P_WHERE_CLAUSE := P_WHERE_CLAUSE || ' ((jg_info_n4 <= ' || l_cleared_select ||' AND trunc(checks.cleared_date) <= ''' ||l_end_date
1784 || ''' AND jg_info_v19 = ''Y'') AND (jg_info_n12 = 1 )) OR (jg_info_n12 = 0 ))';
1785
1786 ELSIF (l_country_code = 'HR') THEN
1787 /*
1788 || Vendor Invoice Tax Report, Croatia
1789 || ELSIF added by Ramananda.
1790 || Requirements of this report are satisfied with the ECE Payables Tax Report
1791 || No Filtering conditions are required for Croatia
1792 */
1793 if g_debug = true then fnd_file.put_line(fnd_file.log,'Croatian Supplier Invoice Tax Report concurrent is submitted' ); end if;
1794 NULL ;
1795 END IF;
1796
1797 ELSE
1798 IF ((l_country_code = 'HU' OR l_country_code = 'PL')) THEN
1799
1800
1801 P_WHERE_CLAUSE := P_WHERE_CLAUSE || ' (((jg_info_n4 > ' || l_cleared_select
1802 || ' AND trunc(checks.cleared_date) <= ''' ||l_end_date|| ''' AND NVL(jg_info_v19,''N'') = ''Y'') '
1803 || l_cleared_select1 || ' ) AND (jg_info_n12 = 1 ) AND jg_info_n15 IS NULL ' || l_unpaid_amt_select
1804 || ' AND trunc(checks.cleared_date) <= ''' ||l_end_date|| ''' ) > 0 AND ''' ||l_country_code|| ''' <> ''PL'' )'
1805 || ' OR ((decode(jg_info_n15, NULL, jg_info_n8, jg_info_n2) > ' || l_cleared_select
1806 || ' AND trunc(checks.cleared_date) <= ''' ||l_end_date|| ''' AND ( '''|| l_country_code
1807 || ''' = ''PL'' OR NVL(jg_info_v19,''N'') = ''N''))' || l_cleared_select1 || ') AND (''' ||l_country_code|| ''' = ''PL'''
1808 || ' OR jg_info_n12 = 0 ) ' || l_unpaid_amt_select|| ' AND trunc(checks.cleared_date) <= ''' ||l_end_date|| ''' ) > 0 ))';
1809
1810
1811 ELSIF (l_country_code = 'SK') THEN
1812
1813 P_WHERE_CLAUSE := P_WHERE_CLAUSE || ' ((jg_info_n4 <= ' || l_cleared_select
1814 || ' AND trunc(checks.cleared_date) <= ''' ||l_end_date
1815 || ''' AND jg_info_v19 = ''Y'') AND (jg_info_n12 = 1 )) OR (jg_info_n12 = 0 ))';
1816
1817 ELSIF (l_country_code = 'HR') THEN
1818 if g_debug = true then fnd_file.put_line(fnd_file.log,'Croatian Supplier Invoice Tax Report concurrent is submitted' ); end if;
1819 NULL ;
1820 END IF;
1821
1822 END IF ;
1823
1824 IF P_WHERE_CLAUSE = ' AND ' THEN
1825 P_WHERE_CLAUSE := ' AND 1 = 1 ';
1826 END IF;
1827
1828 END IF; --END IF for IF l_country_code = 'IL'
1829
1830 -- End of code for ECE and Crotia reports.
1831
1832 ELSE -- FOR IF calling report is NULL
1833
1834 BEGIN
1835 FOR c_data_rec2 IN c_complete
1836 LOOP
1837
1838
1839 INSERT INTO JG_ZZ_VAT_TRX_GT
1840 (jg_info_n1 -- seq_number
1841 ,jg_info_v1 -- inv_number
1842 ,jg_info_v2 -- customer_name
1843 ,jg_info_d1 -- tax_date
1844 ,jg_info_d2 -- inv_date
1845 ,jg_info_d3 -- accounting_date
1846 ,jg_info_v3 -- tax_code_description
1847 ,jg_info_n2 -- taxable_amount
1848 ,jg_info_n3 -- tax_rate
1849 ,jg_info_n4 -- tax_amount
1850 ,jg_info_n5 -- trx_id
1851 ,jg_info_v4 -- trx_class_code
1852 ,jg_info_v5 -- trx_currency_code
1853 ,jg_info_v20 -- TAX_RECOVERABLE_FLAG
1854 ,jg_info_v6 -- Tax Code Description
1855 ,jg_info_n8 -- TAX_AMT_FUNCL_CURR
1856 ,jg_info_n10 -- TAXABLE_AMT_FUNCL_CURR
1857 ,jg_info_v8 -- ACCOUNT FLEXFIELD
1858 ,jg_info_v10 -- Doc Sequence Name
1859 ,jg_info_v11 -- tax_reg_num
1860 ,jg_info_v12 -- TAX_RATE_VAT_TRX_TYPE_DESC
1861 ,jg_info_v13 -- TAX_CODE_VAT_TRX_TYPE_MEANING
1862 ,jg_info_v14 -- TAX_CODE
1863 ,jg_info_v7 -- TAX_CODE_VAT_TRX_TYPE_CODE
1864 ,jg_info_v15 -- TAX_CODE_TYPE_CODE
1865 ,jg_info_v16 -- period_name
1866 ,jg_info_v17 -- offset_flag
1867 ,jg_info_v18 -- offset_tax_rate_code
1868 ,jg_info_v19 -- CHK_VAT_AMOUNT_PAID
1869 ,jg_info_v21 -- reporting_code
1870 )
1871 VALUES
1872 (c_data_rec2.seq_number
1873 ,c_data_rec2.inv_number
1874 ,c_data_rec2.customer_name
1875 ,c_data_rec2.tax_date
1876 ,c_data_rec2.inv_date
1877 ,c_data_rec2.gl_date
1878 ,c_data_rec2.tax_rate_code_description
1879 ,c_data_rec2.taxable_amount
1880 ,c_data_rec2.tax_rate
1881 ,c_data_rec2.tax_amount
1882 ,c_data_rec2.trx_id
1883 ,c_data_rec2.trx_line_class
1884 ,c_data_rec2.trx_currency_code
1885 ,c_data_rec2.tax_recoverable_flag
1886 ,c_data_rec2.tax_rate_code_description
1887 ,c_data_rec2.tax_amt_funcl_curr
1888 ,c_data_rec2.taxable_amt_funcl_curr
1889 ,c_data_rec2.account_flexfield
1890 ,c_data_rec2.doc_seq_name
1891 ,c_data_rec2.tax_reg_num
1892 ,c_data_rec2.tax_rate_vat_trx_type_desc
1893 ,c_data_rec2.tax_rate_code_vat_trx_type_mng
1894 ,c_data_rec2.tax_rate_code
1895 ,c_data_rec2.tax_rate_vat_trx_type_code
1896 ,c_data_rec2.tax_type_code
1897 ,c_data_rec2.gl_period
1898 ,c_data_rec2.offset_flag
1899 ,c_data_rec2.offset_tax_rate_code
1900 ,c_data_rec2.chk_vat_amount_paid
1901 ,c_data_rec2.reporting_code);
1902
1903 END LOOP;
1904
1905 EXCEPTION
1906 WHEN OTHERS THEN
1907 if g_debug = true then fnd_file.put_line(fnd_file.log,' Failed while populating data in the global tmp table for the generic cursor. Error : ' || SUBSTR(SQLERRM,1,200)); end if;
1908 END;
1909
1910 END IF;
1911
1912
1913 EXCEPTION
1914 WHEN OTHERS THEN
1915 if g_debug = true then fnd_file.put_line(fnd_file.log,' An error occured in the before report trigger. Error : ' || SUBSTR(SQLERRM,1,200)); end if;
1916 RETURN(FALSE);
1917 END;
1918
1919 if g_debug = true then
1920 fnd_file.put_line(fnd_file.log,'P_WHERE_CLAUSE: ' ||P_WHERE_CLAUSE);
1921 end if;
1922
1923 RETURN(TRUE);
1924
1925 END before_report;
1926
1927 /*
1928 REM +======================================================================+
1929 REM Name: GET_EX_VAT_TRX_TYPE_MEANING
1930 REM
1931 REM Description: Returns the Vat Transaction type meaning
1932 REM
1933 REM Parameters: None
1934 REM +======================================================================+
1935 */
1936 FUNCTION get_vat_trx_type_meaning RETURN VARCHAR2 IS
1937 BEGIN
1938 RETURN G_VAT_TRX_TYPE_MEANING;
1939 END get_vat_trx_type_meaning;
1940
1941 /*
1942 REM +======================================================================+
1943 REM Name: GET_EX_VAT_TRX_TYPE_MEANING
1944 REM
1945 REM Description: Returns the Exclude Vat Transaction type meaning
1946 REM
1947 REM Parameters: None
1948 REM +======================================================================+
1949 */
1950 FUNCTION get_ex_vat_trx_type_meaning RETURN VARCHAR2 IS
1951 BEGIN
1952 RETURN G_EX_VAT_TRX_TYPE_MEANING;
1953 END get_ex_vat_trx_type_meaning;
1954
1955 /*
1956 REM +======================================================================+
1957 REM Name: GET_VAT_TRX_TYPE_DESC
1958 REM
1959 REM Description: Returns the Vat Transaction type description
1960 REM
1961 REM Parameters: None
1962 REM +======================================================================+
1963 */
1964 FUNCTION get_vat_trx_type_desc RETURN VARCHAR2 IS
1965 BEGIN
1966 RETURN G_VAT_TRX_TYPE_DESC;
1967 END get_vat_trx_type_desc;
1968
1969 /*
1970 REM +======================================================================+
1971 REM Name: GET_EX_VAT_TRX_TYPE_DESC
1972 REM
1973 REM Description: Returns the Exclude Vat Transaction type description
1974 REM
1975 REM Parameters: None
1976 REM +======================================================================+
1977 */
1978 FUNCTION get_ex_vat_trx_type_desc RETURN VARCHAR2 IS
1979 BEGIN
1980 RETURN G_EX_VAT_TRX_TYPE_DESC;
1981 END get_ex_vat_trx_type_desc;
1982
1983 /*
1984 REM +======================================================================+
1985 REM Name: GET_PREPAYMENTS_MEANING
1986 REM
1987 REM Description: Returns the Include Prepayments parameter meaning.
1988 REM
1989 REM Parameters: None
1990 REM +======================================================================+
1991 */
1992 FUNCTION get_prepayments_meaning RETURN VARCHAR2 IS
1993 CURSOR c_prepay_meaning
1994 IS
1995 SELECT meaning
1996 FROM fnd_lookups
1997 WHERE lookup_code = P_INC_PREPAYMENTS
1998 AND lookup_type = 'YES_NO';
1999
2000 BEGIN
2001
2002 IF P_INC_PREPAYMENTS IS NOT NULL THEN
2003 FOR c_meaning IN c_prepay_meaning
2004 LOOP
2005 G_INC_PREPAYMENTS := c_meaning.meaning;
2006 END LOOP;
2007 END IF;
2008
2009 RETURN G_INC_PREPAYMENTS;
2010
2011 EXCEPTION
2012 WHEN OTHERS THEN
2013 if g_debug = true then fnd_file.put_line(fnd_file.log,' Failed while extracting the prepayments meaning. Error : ' || SUBSTR(SQLERRM,1,200)); end if;
2014 RETURN NULL;
2015 END get_prepayments_meaning;
2016
2017 /*
2018 REM +======================================================================+
2019 REM Name: GET_FUNCTIONAL_CURRENCY
2020 REM
2021 REM Description: Returns the functional currency
2022 REM
2023 REM Parameters: None
2024 REM +======================================================================+
2025 */
2026 FUNCTION get_functional_currency RETURN VARCHAR2 IS
2027 BEGIN
2028 RETURN G_FUNCTIONAL_CURRENCY;
2029 END get_functional_currency;
2030
2031 /*
2032 REM +======================================================================+
2033 REM Name: CF_TAX_CODE_TYPE_CODE
2034 REM
2035 REM Description: Returns 1 or 0 based on the tax type.
2036 REM
2037 REM Parameters: TAX_CODE_TYPE_CODE => Whether tax is VAT or OFFSET
2038 REM +======================================================================+
2039 */
2040 FUNCTION CF_Tax_Code_Type_Code(TAX_CODE_TYPE_CODE VARCHAR2) RETURN NUMBER IS
2041 BEGIN
2042 IF TAX_CODE_TYPE_CODE = 'XOFFSET' THEN
2043 RETURN 1;
2044 ELSE
2045 RETURN 0;
2046 END IF;
2047 END;
2048
2049 /*
2050 REM +======================================================================+
2051 REM Name: GET_TRN
2052 REM
2053 REM Description: Fetches and returns the TRN number
2054 REM
2055 REM Parameters: None
2056 REM +======================================================================+
2057 */
2058 FUNCTION get_trn RETURN VARCHAR2 IS
2059 l_trn VARCHAR2(30) := '';
2060
2061 CURSOR c_get_trn
2062 IS
2063 SELECT JZVRS.TAX_REGISTRATION_NUMBER
2064 FROM JG_ZZ_VAT_REP_STATUS JZVRS
2065 WHERE JZVRS.VAT_REPORTING_ENTITY_ID = P_VAT_REPORTING_ENTITY_ID
2066 /* added during UT */
2067 AND (P_TAX_CALENDAR_PERIOD is null and jzvrs.tax_calendar_period = P_TAX_CALENDAR_PERIOD)
2068 AND jzvrs.source = 'AP'
2069 AND rownum = 1;
2070
2071 BEGIN
2072
2073 FOR c_trn IN c_get_trn
2074 LOOP
2075 l_trn := c_trn.TAX_REGISTRATION_NUMBER;
2076 END LOOP;
2077
2078 RETURN l_trn;
2079 EXCEPTION
2080 WHEN OTHERS THEN
2081 if g_debug = true then
2082 fnd_file.put_line(fnd_file.log,' Failed while extracting the Tax Reginstration Number. Error : ' || SUBSTR(SQLERRM,1,200));
2083 end if;
2084 Return NULL;
2085 END get_trn;
2086
2087 /*
2088 REM +======================================================================+
2089 REM Name: CF_SEQ_NO
2090 REM
2091 REM Description: This function returns the sequence number for a particular
2092 REM country. The sequence no is incremented each time this
2093 REM function is called.
2094 REM
2095 REM Parameters: None
2096 REM +======================================================================+
2097 */
2098 FUNCTION CF_seq_no RETURN NUMBER IS
2099 l_country_code VARCHAR2(5);
2100 BEGIN
2101 /* added during UT */
2102 l_country_code := jg_zz_shared_pkg.GET_COUNTRY;
2103 if l_country_code is null then
2104 l_country_code := jg_zz_shared_pkg.GET_COUNTRY(mo_global.get_current_org_id);
2105 end if;
2106
2107 IF l_country_code = 'PL' THEN
2108 G_SEQ_NO := G_SEQ_NO + 1;
2109 RETURN (G_SEQ_NO);
2110 ELSE
2111 RETURN NULL;
2112 END IF;
2113
2114 END CF_seq_no;
2115
2116 END JG_ZZ_SUMMARY_AP_PKG;