[Home] [Help]
PACKAGE BODY: APPS.JG_ZZ_SUMMARY_AP_PKG
Source
1 PACKAGE BODY JG_ZZ_SUMMARY_AP_PKG
2 -- $Header: jgzzsummaryapb.pls 120.29.12010000.13 2009/02/26 10:37:20 pakumare 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 l_cleared_select VARCHAR2(2000);
168 l_cleared_select1 VARCHAR2(2000);
169 l_unpaid_amt_select VARCHAR2(2000);
170 l_vat_or_offset NUMBER;
171 l_offset_tax_code_name VARCHAR2(50);
172 l_country_code VARCHAR2(5);
173 l_end_date DATE;
174 l_unpaid_amount NUMBER;
175 l_curr_code VARCHAR2(50);
176 l_rep_entity_name jg_zz_vat_trx_details.rep_context_entity_name%TYPE;
177 l_legal_entity_id NUMBER;
178 l_taxpayer_id jg_zz_vat_trx_details.taxpayer_id%TYPE;
179 l_company_name xle_registrations.registered_name%TYPE;
180 l_registration_number xle_registrations.registration_number%TYPE;
181 l_country hz_locations.country%TYPE;
182 l_address1 hz_locations.address1%TYPE;
183 l_address2 hz_locations.address2%TYPE;
184 l_address3 hz_locations.address3%TYPE;
185 l_address4 hz_locations.address4%TYPE;
186 l_city hz_locations.city%TYPE;
187 l_postal_code hz_locations.postal_code%TYPE;
188 l_contact hz_parties.party_name%TYPE;
189 l_phone_number hz_contact_points.phone_number%TYPE;
190 l_tax_registration_num VARCHAR2 (240);
191 l_period_end_date DATE;
192 l_period_start_date DATE;
193 l_reporting_status VARCHAR2 (60);
194 l_lineno NUMBER ;
195 v_count NUMBER :=0;
196 v_prev_vat_code VARCHAR2(230) :='';
197 v_is_seq_updated VARCHAR2(1) := 'N';
198 v_dummy NUMBER;
199 l_enable_report_sequence_flag VARCHAR2(2);
200 -- Added for Glob-006 ER
201 l_province VARCHAR2(120);
202 l_comm_num VARCHAR2(30);
203 l_vat_reg_num VARCHAR2(50);
204 l_ledger_id NUMBER;
205 l_entity_type_code VARCHAR2(30); -- Bug 8289960
206 l_ledger_category_code VARCHAR2(30); -- Bug 8289960
207 -- end here
208
209
210 CURSOR C_DATA IS
211 SELECT jzvtd.doc_seq_value SEQ_NUMBER
212 ,jzvtd.tax_invoice_date TAX_DATE
213 ,jzvtd.billing_tp_name CUSTOMER_NAME
214 ,jzvtd.accounting_date GL_DATE
215 ,jzvtd.trx_currency_code
216 ,sum(nvl(jzvtd.tax_amt_funcl_curr,jzvtd.tax_amt)
217 + nvl(jzvtd.taxable_amt_funcl_curr, jzvtd.taxable_amt)) TOTAL_ACCOUNTED_AMOUNT
218 ,sum(nvl(jzvtd.tax_amt,0) + nvl(jzvtd.taxable_amt, 0)) TOTAL_ENTERED_AMOUNT
219 ,sum(nvl(jzvtd.taxable_amt, 0)) TAXABLE_ENTERED_AMOUNT
220 ,sum(nvl(jzvtd.taxable_amt_funcl_curr, jzvtd.taxable_amt)) TAXABLE_AMOUNT
221 /* fixed during UT for Bug# 5258868
222 ,sum(nvl(jzvtd.tax_amt, 0)) tax_amount
223 ,sum(decode(jzvtd.tax_recoverable_flag, 'Y', jzvtd.tax_amt, 0)) RECOVERABLE
224 ,sum(decode(jzvtd.tax_recoverable_flag, 'N', 0, jzvtd.tax_amt)) NON_RECOVERABLE*/
225 /* following 3 columns added for Bug# 5258868 */
226 ,sum(nvl(jzvtd.tax_amt_funcl_curr, jzvtd.tax_amt)) tax_amount
227 ,sum(decode(jzvtd.tax_recoverable_flag, 'Y', nvl(jzvtd.tax_amt_funcl_curr, jzvtd.tax_amt), 0)) recoverable
228 ,sum(decode(nvl(jzvtd.tax_recoverable_flag,'Y'), 'N', nvl(jzvtd.tax_amt_funcl_curr, jzvtd.tax_amt), 0)) non_recoverable
229 ,jzvtd.trx_number INV_NUMBER
230 ,jzvtd.trx_date INV_DATE
231 ,jzvtd.billing_tp_tax_reg_num TAX_REG_NUM
232 ,jzvtd.tax_rate TAX_RATE
233 ,jzvtd.trx_id
234 ,jzvtd.tax_rate_vat_trx_type_desc
235 ,jzvtd.tax_rate_code_vat_trx_type_mng
236 ,jzvtd.tax_rate_code
237 ,jzvtd.trx_line_class
238 ,jzvtd.tax_rate_code_description
239 ,jzvtd.tax_recoverable_flag rec_flag
240 ,jzvtd.account_flexfield
241 ,jzvtd.doc_seq_name
242 /* UT TEST ,to_char(jzvtd.tax_invoice_date,'MON') GL_PERIOD */
243 , jzvrs.tax_calendar_period GL_PERIOD /* UT TEST */
244 ,jzvtd.tax_rate_vat_trx_type_code
245 ,jzvtd.reporting_code
246 ,jzvtd.tax_line_id
247 ,jzvtd.offset_flag
248 ,jzvtd.offset_tax_rate_code
249 ,jzvtd.chk_vat_amount_paid CHK_VAT_AMOUNT_PAID
250 ,jzvrs.period_end_date
251 FROM jg_zz_vat_trx_details jzvtd
252 ,jg_zz_vat_rep_status jzvrs
253 WHERE jzvrs.vat_reporting_entity_id = P_VAT_REPORTING_ENTITY_ID
254 AND jzvrs.tax_calendar_period = P_TAX_CALENDAR_PERIOD
255 AND jzvrs.reporting_status_id = jzvtd.reporting_status_id
256 AND jzvtd.tax_rate_register_type_code = 'TAX'
257 AND jzvrs.source = 'AP'
258 AND (P_VAT_TRX_TYPE IS NULL OR jzvtd.tax_rate_vat_trx_type_code = P_VAT_TRX_TYPE)
259 AND (P_EX_VAT_TRX_TYPE is null or nvl(jzvtd.tax_rate_vat_trx_type_code,'#') <> P_EX_VAT_TRX_TYPE)
260 AND (P_INC_PREPAYMENTS = 'Y' OR (jzvtd.trx_line_class <> 'PREPAYMENT INVOICES' AND P_INC_PREPAYMENTS = 'N') )
261 GROUP BY jzvtd.reporting_code
262 ,jzvtd.doc_seq_value
263 , jzvrs.tax_calendar_period /* UT TEST addition */
264 ,jzvtd.tax_invoice_date
265 ,jzvtd.billing_tp_name
266 ,jzvtd.accounting_date
267 ,jzvtd.trx_currency_code
268 ,trx_number
269 ,trx_date
270 ,billing_tp_tax_reg_num
271 ,tax_rate
272 ,jzvtd.trx_id
273 ,jzvtd.tax_rate_vat_trx_type_desc
274 ,jzvtd.tax_rate_code_vat_trx_type_mng
275 ,jzvtd.tax_rate_code
276 ,jzvtd.trx_line_class
277 ,jzvtd.tax_rate_code_description
278 ,jzvtd.tax_recoverable_flag
279 ,jzvtd.account_flexfield
280 ,jzvtd.doc_seq_name
281 ,jzvtd.tax_rate_vat_trx_type_code
282 ,jzvtd.chk_vat_amount_paid
283 ,jzvtd.tax_line_id
284 ,jzvtd.offset_flag
285 ,jzvtd.offset_tax_rate_code
286 ,jzvrs.period_end_date ;
287
288
289 CURSOR C_NEW IS
290 SELECT jzvtd.doc_seq_value seq_number
291 ,jzvtd.tax_invoice_date tax_date
292 ,decode(jzvtd.event_class_code, 'EXPENSE REPORTS',
293 decode(jzvtd.merchant_party_name, null,
294 jzvtd.billing_tp_name, jzvtd.merchant_party_name), jzvtd.billing_tp_name) customer_name
295 ,jzvtd.accounting_date gl_date
296 ,jzvtd.trx_currency_code
297 ,sum(nvl(jzvtd.tax_amt_funcl_curr,jzvtd.tax_amt)
298 + nvl(jzvtd.taxable_amt_funcl_curr, jzvtd.taxable_amt)*jzvtd.tax_recovery_rate/100) total_accounted_amount
299 ,sum(nvl(jzvtd.tax_amt,0) + nvl(jzvtd.taxable_amt, 0)*jzvtd.tax_recovery_rate/100) total_entered_amount
300 ,sum(nvl(jzvtd.taxable_amt, 0)*jzvtd.tax_recovery_rate/100) taxable_entered_amount
301 ,sum(nvl(jzvtd.taxable_amt_funcl_curr, jzvtd.taxable_amt)*jzvtd.tax_recovery_rate/100) taxable_amount
302 /* fixed during UT for Bug# 5258868
303 ,sum(nvl(jzvtd.tax_amt, 0)) tax_amount
304 ,sum(decode(jzvtd.tax_recoverable_flag, 'Y', jzvtd.tax_amt, 0)) recoverable
305 ,sum(decode(jzvtd.tax_recoverable_flag, 'N', 0, jzvtd.tax_amt)) non_recoverable */
306 /* following 3 columns added for Bug# 5258868 */
307 ,sum(nvl(jzvtd.tax_amt_funcl_curr, jzvtd.tax_amt)) tax_amount
308 ,sum(decode(jzvtd.tax_recoverable_flag, 'Y', nvl(jzvtd.tax_amt_funcl_curr, jzvtd.tax_amt), 0)) recoverable
309 ,sum(decode(nvl(jzvtd.tax_recoverable_flag,'Y'), 'N', nvl(jzvtd.tax_amt_funcl_curr, jzvtd.tax_amt), 0)) non_recoverable
310 ,decode(jzvtd.event_class_code, 'EXPENSE REPORTS',
311 decode(jzvtd.merchant_party_document_number, null,
312 jzvtd.trx_number, jzvtd.merchant_party_document_number), jzvtd.trx_number) inv_number
313 ,decode(jzvtd.event_class_code, 'EXPENSE REPORTS',
314 decode(jzvtd.start_expense_date, null,
315 jzvtd.trx_date, jzvtd.start_expense_date), jzvtd.trx_date) inv_date
316 ,decode(jzvtd.event_class_code, 'EXPENSE REPORTS',
317 decode(jzvtd.merchant_party_tax_reg_number, null,
318 jzvtd.billing_tp_tax_reg_num, jzvtd.merchant_party_tax_reg_number), jzvtd.billing_tp_tax_reg_num) tax_reg_num
319 ,jzvtd.tax_rate tax_rate
320 ,jzvtd.trx_id
321 ,jzvtd.tax_rate_vat_trx_type_desc
322 ,jzvtd.tax_rate_code_vat_trx_type_mng
323 ,jzvtd.tax_rate_code
324 ,jzvtd.trx_line_class
325 ,jzvtd.tax_rate_code_description
326 ,jzvtd.doc_seq_name
327 ,jzvrs.tax_calendar_period gl_period /* UT TEST addition */
328 /* UT TEST ,to_char(jzvtd.tax_invoice_date,'MON') gl_period */
329 ,jzvtd.tax_rate_vat_trx_type_code
330 ,jzvtd.reporting_code
331 ,jzvtd.tax_line_id
332 ,jzvtd.offset_flag
333 ,jzvtd.offset_tax_rate_code
334 ,jzvtd.chk_vat_amount_paid chk_vat_amount_paid
335 ,jzvrs.period_end_date
336 FROM jg_zz_vat_trx_details jzvtd
337 ,jg_zz_vat_rep_status jzvrs
338 WHERE jzvrs.vat_reporting_entity_id = P_VAT_REPORTING_ENTITY_ID
339 AND jzvrs.tax_calendar_period = P_TAX_CALENDAR_PERIOD
340 AND jzvrs.reporting_status_id = jzvtd.reporting_status_id
341 -- AND jzvtd.tax_rate_register_type_code = 'TAX'
342 AND jzvrs.source = 'AP'
343 AND (P_VAT_TRX_TYPE IS NULL OR jzvtd.tax_rate_vat_trx_type_code = P_VAT_TRX_TYPE)
344 AND (P_EX_VAT_TRX_TYPE IS NULL OR nvl(jzvtd.tax_rate_vat_trx_type_code,'#') <> P_EX_VAT_TRX_TYPE)
345 AND ((jzvtd.trx_line_class <> 'PREPAYMENT INVOICES' AND P_INC_PREPAYMENTS = 'N') OR P_INC_PREPAYMENTS = 'Y')
346 GROUP BY jzvtd.tax_rate_code_vat_trx_type_mng
347 ,jzvtd.tax_rate_vat_trx_type_desc
348 ,jzvtd.tax_rate_code
349 ,decode(jzvtd.event_class_code, 'EXPENSE REPORTS',
350 decode(jzvtd.merchant_party_document_number, null,
351 jzvtd.trx_number, jzvtd.merchant_party_document_number), jzvtd.trx_number)
352 ,jzvtd.doc_seq_value
353 ,jzvrs.tax_calendar_period /* UT TEST addition */
354 ,jzvtd.tax_invoice_date
355 ,decode(jzvtd.event_class_code, 'EXPENSE REPORTS',
356 decode(jzvtd.merchant_party_name, null,
357 jzvtd.billing_tp_name, jzvtd.merchant_party_name), jzvtd.billing_tp_name)
358 ,jzvtd.accounting_date
359 ,jzvtd.trx_currency_code
360 ,decode(jzvtd.event_class_code, 'EXPENSE REPORTS',
361 decode(jzvtd.start_expense_date, null,
362 jzvtd.trx_date, jzvtd.start_expense_date), jzvtd.trx_date)
363 ,decode(jzvtd.event_class_code, 'EXPENSE REPORTS',
364 decode(jzvtd.merchant_party_tax_reg_number, null,
365 jzvtd.billing_tp_tax_reg_num, jzvtd.merchant_party_tax_reg_number), jzvtd.billing_tp_tax_reg_num)
366 ,jzvtd.tax_rate
367 ,jzvtd.trx_id
368 ,jzvtd.trx_line_class
369 ,jzvtd.tax_rate_code_description
370 ,jzvtd.doc_seq_name
371 ,jzvtd.tax_rate_vat_trx_type_code
372 ,jzvtd.chk_vat_amount_paid
373 ,jzvtd.reporting_code
374 ,jzvtd.tax_line_id
375 ,jzvtd.offset_flag
376 ,jzvtd.offset_tax_rate_code
377 ,jzvrs.period_end_date;
378
379
380 -- Cursor for report level seq number --
381
382 -- Cursor for report level seq number --
383 CURSOR temp_cur IS
384 SELECT jg_info_n1 seq_num,
385 jg_info_v7 vat_code,
386 tmp.rowid,
387 jg_info_v14 tax_code,
388 jg_info_n5 trx_id,
389 jg_info_v15 reporting_code
390 FROM JG_ZZ_VAT_TRX_GT tmp,
391 gl_periods glp,
392 jg_zz_vat_rep_status JZVRS
393 WHERE jg_info_v9 = 'M'
394 AND JZVRS.vat_reporting_entity_id = P_VAT_REPORTING_ENTITY_ID
395 AND jzvrs.tax_calendar_period = P_TAX_CALENDAR_PERIOD
396 AND GLP.period_set_name = jzvrs.tax_calendar_name
397 AND jg_info_d1 between glp.start_date and glp.end_date
398 AND jzvrs.source = 'AP'
399 ORDER BY decode(jg_info_v15, 'OFFSET','XOFFSET','VAT'),
400 jg_info_v7, --TAX_RATE_VAT_TRX_TYPE_CODE
401 jg_info_v13,
402 jg_info_v12, --VAT_TRANSACTION_TYPE_CODE description.
403 period_year desc,
404 period_num desc,
405 jg_info_v6, --TAX CODE DESC
406 jg_info_v14, --TAX_RATE_CODE
407 jg_info_d1, --TAX_INVOICE_DATE
408 jg_info_v1,
409 jg_info_n3; /*tax_rate*/
410
411
412 CURSOR C_COMPLETE IS
413 SELECT jzvtd.doc_seq_value seq_number
414 ,jzvtd.tax_invoice_date tax_date
415 ,jzvtd.billing_tp_name customer_name
416 ,jzvtd.accounting_date gl_date
417 ,jzvtd.trx_currency_code
418 ,jzvtd.tax_amt_funcl_curr
419 ,jzvtd.taxable_amt_funcl_curr
420 ,jzvtd.tax_amt tax_amount
421 ,jzvtd.taxable_amt taxable_amount
422 ,jzvtd.tax_recoverable_flag
423 ,jzvtd.trx_number inv_number
424 ,jzvtd.trx_date inv_date
425 ,jzvtd.billing_tp_tax_reg_num tax_reg_num
426 ,jzvtd.tax_rate tax_rate
427 ,jzvtd.trx_id
428 ,jzvtd.tax_rate_vat_trx_type_desc
429 ,jzvtd.tax_rate_code_vat_trx_type_mng
430 ,jzvtd.tax_rate_code
431 ,jzvtd.trx_line_class
432 ,jzvtd.tax_rate_code_description
433 ,jzvtd.tax_recoverable_flag rec_flag
434 ,jzvtd.account_flexfield
435 ,jzvtd.doc_seq_name
436 , jzvrs.tax_calendar_period gl_period /* UT TEST */
437 /* UT TEST ,to_char(jzvtd.tax_invoice_date,'MON') gl_period */
438 ,jzvtd.tax_rate_vat_trx_type_code
439 ,jzvtd.tax_type_code
440 ,jzvtd.tax_line_id
441 ,jzvtd.offset_flag
442 ,jzvtd.offset_tax_rate_code
443 ,jzvtd.chk_vat_amount_paid chk_vat_amount_paid
444 ,jzvrs.period_end_date
445 ,jzvtd.reporting_code
446 FROM jg_zz_vat_trx_details jzvtd
447 ,jg_zz_vat_rep_status jzvrs
448 WHERE jzvrs.vat_reporting_entity_id = P_VAT_REPORTING_ENTITY_ID
449 AND jzvrs.tax_calendar_period = P_TAX_CALENDAR_PERIOD
450 AND jzvrs.reporting_status_id = jzvtd.reporting_status_id
451 AND jzvrs.source = 'AP'
452 AND jzvtd.tax_rate_register_type_code = 'TAX';
453
454 CURSOR c_get_lookup_values (p_trx_type VARCHAR2)
455 IS
456 SELECT meaning
457 ,description
458 FROM fnd_lookups
459 WHERE lookup_type = 'ZX_JEBE_VAT_TRANS_TYPE'
460 AND lookup_code = p_trx_type;
461
462 CURSOR c_company_details
463 IS
464 SELECT jzvtd.rep_context_entity_name company_name
465 ,jzvtd.functional_currency_code functional_currency_code
466 FROM jg_zz_vat_trx_details jzvtd
467 ,jg_zz_vat_rep_status jzvrs
468 WHERE jzvrs.vat_reporting_entity_id = P_VAT_REPORTING_ENTITY_ID
469 AND jzvrs.source = 'AP'
470 AND jzvrs.tax_calendar_period = P_TAX_CALENDAR_PERIOD
471 AND jzvrs.reporting_status_id = jzvtd.reporting_status_id
472 and rownum < 2; /* added during UT Bug# 5258868 */
473
474 CURSOR c_vat_or_offset (p_tax_rate_code VARCHAR2)
475 IS
476 SELECT MIN(-9999) vat_or_offset
477 FROM jg_zz_vat_trx_details jzvtd
478 ,jg_zz_vat_rep_status jzvrs
479 WHERE jzvtd.tax_rate_code = p_tax_rate_code
480 AND jzvrs.reporting_status_id = jzvtd.reporting_status_id
481 AND jzvrs.vat_reporting_entity_id = P_VAT_REPORTING_ENTITY_ID
482 AND jzvrs.source = 'AP'
483 AND jzvrs.tax_calendar_period = P_TAX_CALENDAR_PERIOD
484 AND jzvtd.offset_tax_rate_code IS NOT NULL;
485
486 CURSOR c_period_end_date
487 IS
488 SELECT period_end_date
489 FROM jg_zz_vat_rep_status jzvrs
490 WHERE jzvrs.vat_reporting_entity_id = P_VAT_REPORTING_ENTITY_ID
491 AND jzvrs.source = 'AP'
492 AND jzvrs.tax_calendar_period = P_TAX_CALENDAR_PERIOD;
493
494 CURSOR israel_details IS
495 SELECT jzvtd.billing_tp_name vendor_name
496 ,jzvtd.billing_tp_number vendor_number
497 ,jzvtd.billing_tp_site_name site_name
498 ,NVL(jzvtd.billing_tp_site_tax_reg_num,jzvtd.billing_tp_tax_reg_num) tax_reg_num
499 ,jzvtd.trx_number inv_number
500 ,jzvtd.trx_date inv_date
501 ,NULL import_document_number
502 ,NULL import_document_date
503 ,DECODE(nvl2(apinv.cancelled_date,'Y','N'),'Y','CANCELLED',jzvtd.trx_line_class) trx_line_class
504 ,jzvtd.posted_flag
505 -- 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
506 ,JG_ZZ_COMMON_PKG.get_amt_tot(jzvtd.trx_id,l_ledger_id,G_PRECISION) invoice_amount
507 ,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
508 ,0 p_vat_on_fixed_assets
509 ,0 i_vat_on_fixed_assets
510 ,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
511 ,0 p_vat_on_other_trx
512 ,0 i_vat_on_other_trx
513 ,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
514 ,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
515 ,'S' class
516 FROM jg_zz_vat_trx_details jzvtd
517 ,jg_zz_vat_rep_status jzvrs
518 ,ap_invoices_all apinv
519 WHERE jzvrs.source='AP'
520 AND jzvrs.vat_reporting_entity_id = P_VAT_REPORTING_ENTITY_ID
521 AND jzvrs.tax_calendar_period = P_TAX_CALENDAR_PERIOD
522 AND JZVRS.REPORTING_STATUS_ID = JZVTD.REPORTING_STATUS_ID
523 --AND jzvtd.tax_rate_register_type_code = 'TAX'
524 AND ( P_VAT_TRX_TYPE is null or jzvtd.tax_rate_vat_trx_type_code = P_VAT_TRX_TYPE)
525 AND jzvtd.reporting_code IN ('VAT-A','VAT-S')
526 AND apinv.invoice_id = jzvtd.trx_id
527 GROUP BY jzvtd.billing_tp_name
528 ,jzvtd.billing_tp_number
529 ,jzvtd.billing_tp_site_name
530 ,NVL(jzvtd.billing_tp_site_tax_reg_num,jzvtd.billing_tp_tax_reg_num)
531 ,jzvtd.trx_number
532 ,jzvtd.trx_id
533 ,jzvtd.trx_date
534 ,DECODE(nvl2(apinv.cancelled_date,'Y','N'),'Y','CANCELLED',jzvtd.trx_line_class)
535 ,jzvtd.posted_flag
536
537 UNION ALL
538
539 SELECT jzvtd.billing_tp_name vendor_name
540 ,jzvtd.billing_tp_number vendor_number
541 ,jzvtd.billing_tp_site_name site_name
542 ,NVL(jzvtd.billing_tp_site_tax_reg_num,jzvtd.billing_tp_tax_reg_num) tax_reg_num
543 ,jzvtd.trx_number inv_number
544 ,jzvtd.trx_date inv_date
545 ,NULL import_document_number
546 ,NULL import_document_date
547 ,DECODE(nvl2(apinv.cancelled_date,'Y','N'),'Y','CANCELLED',jzvtd.trx_line_class) trx_line_class
548 ,jzvtd.posted_flag
549 -- 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
550 ,JG_ZZ_COMMON_PKG.get_amt_tot(jzvtd.trx_id,l_ledger_id,G_PRECISION) invoice_amount
551 ,0 s_vat_on_fixed_assets
552 ,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
553 ,0 i_vat_on_fixed_assets
554 ,0 s_vat_on_other_trx
555 ,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
556 ,0 i_vat_on_other_trx
557 ,sum(DECODE(tax_recoverable_flag,'Y',decode(jzvtd.reporting_code,
558 'VAT-KA',Round(nvl(jzvtd.tax_amt_funcl_curr,jzvtd.tax_amt),G_PRECISION),0),0)) vat_fixed_assets
559 ,sum(DECODE(tax_recoverable_flag,'Y',decode(jzvtd.reporting_code,
560 'VAT-KS',Round(nvl(jzvtd.tax_amt_funcl_curr,jzvtd.tax_amt),G_PRECISION),0),0)) vat_other_trx
561 ,'P' class
562 FROM jg_zz_vat_trx_details jzvtd
563 ,jg_zz_vat_rep_status jzvrs
564 ,ap_invoices_all apinv
565 WHERE jzvrs.source='AP'
566 AND jzvrs.vat_reporting_entity_id = P_VAT_REPORTING_ENTITY_ID
567 AND jzvrs.tax_calendar_period = P_TAX_CALENDAR_PERIOD
568 AND JZVRS.REPORTING_STATUS_ID = JZVTD.REPORTING_STATUS_ID
569 --AND jzvtd.tax_rate_register_type_code = 'TAX'
570 AND ( P_VAT_TRX_TYPE is null or jzvtd.tax_rate_vat_trx_type_code = P_VAT_TRX_TYPE)
571 AND jzvtd.reporting_code IN ('VAT-KA','VAT-KS')
572 AND apinv.invoice_id = jzvtd.trx_id
573 GROUP BY jzvtd.billing_tp_name
574 ,jzvtd.billing_tp_number
575 ,jzvtd.billing_tp_site_name
576 ,NVL(jzvtd.billing_tp_site_tax_reg_num,jzvtd.billing_tp_tax_reg_num)
577 ,jzvtd.trx_number
578 ,jzvtd.trx_id
579 ,jzvtd.trx_date
580 -- ,jzvtd.import_document_number
581 -- ,jzvtd.import_document_date
582 ,DECODE(nvl2(apinv.cancelled_date,'Y','N'),'Y','CANCELLED',jzvtd.trx_line_class)
583 ,jzvtd.posted_flag
584
585 UNION ALL
586
587 SELECT jzvtd.billing_tp_name vendor_name
588 ,jzvtd.billing_tp_number vendor_number
589 ,jzvtd.billing_tp_site_name site_name
590 ,NVL(jzvtd.billing_tp_site_tax_reg_num,jzvtd.billing_tp_tax_reg_num) tax_reg_num
591 ,jzvtd.trx_number inv_number
592 ,DECODE(length(nvl(apinvl.GLOBAL_ATTRIBUTE13,'A')),
593 1,jzvtd.trx_date,
594 9,to_date(substr(apinvl.GLOBAL_ATTRIBUTE13, 1, 9), 'DD-MM-RRRR'),
595 19,TO_DATE(SUBSTR(apinvl.GLOBAL_ATTRIBUTE13, 1, 19),'RRRR/MM/DD hh24:mi:ss'),
596 to_date(substr(apinvl.GLOBAL_ATTRIBUTE13, 1, 11), 'DD-MM-RRRR')) inv_date
597 ,NVL(apinvl.GLOBAL_ATTRIBUTE14,apinv.invoice_num) import_document_number
598 ,apinvl.GLOBAL_ATTRIBUTE13 import_document_date
599 ,DECODE(nvl2(apinv.cancelled_date,'Y','N'),'Y','CANCELLED',jzvtd.trx_line_class) trx_line_class
600 ,jzvtd.posted_flag
601 -- 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
602 ,JG_ZZ_COMMON_PKG.get_amt_tot(jzvtd.trx_id,l_ledger_id,G_PRECISION) invoice_amount
603 ,0 s_vat_on_fixed_assets
604 ,0 p_vat_on_fixed_assets
605 ,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
606 ,0 s_vat_on_other_trx
607 ,0 p_vat_on_other_trx
608 ,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
609 ,sum(DECODE(tax_recoverable_flag,'Y',decode(jzvtd.reporting_code,
610 'VAT-RA',Round(nvl(jzvtd.tax_amt_funcl_curr,jzvtd.tax_amt),G_PRECISION),0),0)) vat_fixed_assets
611 ,sum(DECODE(tax_recoverable_flag,'Y',decode(jzvtd.reporting_code,
612 'VAT-RS',Round(nvl(jzvtd.tax_amt_funcl_curr,jzvtd.tax_amt),G_PRECISION),0),0)) vat_other_trx
613 , 'I' class
614 FROM jg_zz_vat_trx_details jzvtd
615 ,jg_zz_vat_rep_status jzvrs
616 ,ap_invoices_all apinv
617 ,ap_invoice_lines_all apinvl
618 ,zx_lines zxl
619 WHERE jzvrs.source='AP'
620 AND jzvrs.vat_reporting_entity_id = P_VAT_REPORTING_ENTITY_ID
621 AND jzvrs.tax_calendar_period = P_TAX_CALENDAR_PERIOD
622 AND JZVRS.REPORTING_STATUS_ID = JZVTD.REPORTING_STATUS_ID
623 -- AND jzvtd.tax_rate_register_type_code = 'TAX'
624 AND ( P_VAT_TRX_TYPE is null or jzvtd.tax_rate_vat_trx_type_code = P_VAT_TRX_TYPE)
625 AND jzvtd.reporting_code IN ('VAT-RA','VAT-RS')
626 AND apinv.invoice_id = jzvtd.trx_id
627 AND apinvl.invoice_id= jzvtd.trx_id
628 AND zxl.trx_id= jzvtd.trx_id
629 AND zxl.tax_line_id =jzvtd.tax_line_id
630 AND zxl.summary_tax_line_id = apinvl.summary_tax_line_id
631 -- AND apinvl.line_type_lookup_code = 'TAX'
632 GROUP BY jzvtd.billing_tp_name
633 ,jzvtd.billing_tp_number
634 ,jzvtd.billing_tp_site_name
635 ,NVL(jzvtd.billing_tp_site_tax_reg_num,jzvtd.billing_tp_tax_reg_num)
636 ,jzvtd.trx_number
637 ,jzvtd.trx_id
638 ,DECODE(length(nvl(apinvl.GLOBAL_ATTRIBUTE13,'A')),
639 1,jzvtd.trx_date,
640 9,to_date(substr(apinvl.GLOBAL_ATTRIBUTE13, 1, 9), 'DD-MM-RRRR'),
641 19,TO_DATE(SUBSTR(apinvl.GLOBAL_ATTRIBUTE13, 1, 19),'RRRR/MM/DD hh24:mi:ss'),
642 to_date(substr(apinvl.GLOBAL_ATTRIBUTE13, 1, 11), 'DD-MM-RRRR'))
643 ,NVL(apinvl.GLOBAL_ATTRIBUTE14,apinv.invoice_num)
644 ,apinvl.GLOBAL_ATTRIBUTE13
645 ,DECODE(nvl2(apinv.cancelled_date,'Y','N'),'Y','CANCELLED',jzvtd.trx_line_class)
646 ,jzvtd.posted_flag;
647
648
649 BEGIN
650
651 p_where_clause := ' AND 1 = 1 ';
652
653 if g_debug = true then fnd_file.put_line(fnd_file.log,'Calling JG_ZZ_COMMON_PKG.funct_curr_legal'); end if;
654 JG_ZZ_COMMON_PKG.funct_curr_legal(l_curr_code
655 ,l_rep_entity_name
656 ,l_legal_entity_id
657 ,l_taxpayer_id
658 ,P_VAT_REPORTING_ENTITY_ID
659 , pv_period_name => p_tax_calendar_period /* UT TEST addition */
660 );
661 if g_debug = true then fnd_file.put_line(fnd_file.log,'Calling JG_ZZ_COMMON_PKG.company_detail'); end if;
662 JG_ZZ_COMMON_PKG.company_detail(x_company_name => l_company_name
663 ,x_registration_number =>l_registration_number
664 ,x_country => l_country
665 ,x_address1 => l_address1
666 ,x_address2 => l_address2
667 ,x_address3 => l_address3
668 ,x_address4 => l_address4
669 ,x_city => l_city
670 ,x_postal_code => l_postal_code
671 ,x_contact => l_contact
672 ,x_phone_number => l_phone_number
673 ,x_province => l_province
674 ,x_comm_number => l_comm_num
675 ,x_vat_reg_num => l_vat_reg_num
676 ,pn_legal_entity_id => l_legal_entity_id
677 ,p_vat_reporting_entity_id => P_VAT_REPORTING_ENTITY_ID);
678
679 if g_debug = true then fnd_file.put_line(fnd_file.log,'Calling jg_zz_common_pkg.tax_registration'); end if;
680 jg_zz_common_pkg.tax_registration(x_tax_registration => l_tax_registration_num
681 ,x_period_start_date => l_period_start_date
682 ,x_period_end_date => l_period_end_date
683 ,x_status => l_reporting_status
684 ,pn_vat_rep_entity_id => p_vat_reporting_entity_id
685 ,pv_period_name => p_tax_calendar_period
686 ,pv_source => 'AP');
687
688 l_reporting_status := JG_ZZ_VAT_REP_UTILITY.get_period_status(pn_vat_reporting_entity_id => p_vat_reporting_entity_id
689 ,pv_tax_calendar_period => p_tax_calendar_period
690 ,pv_tax_calendar_year => null
691 ,pv_source => NULL
692 ,pv_report_name => p_calling_report);
693
694 -- Bug 8285537. Exception handled
695 BEGIN
696 select distinct nvl(jzvre.ledger_id,0)
697 , jzvre.entity_type_code
698 into l_ledger_id,l_entity_type_code
699 from jg_zz_vat_rep_entities jzvre
700 where jzvre.vat_reporting_entity_id = P_VAT_REPORTING_ENTITY_ID;
701 EXCEPTION
702 WHEN OTHERS THEN
703 l_ledger_id := 0;
704 l_entity_type_code := 'NO_ENTITY_TYPE_CODE';
705 END;
706 -- Bug 8285537. Exception handled
707 BEGIN
708 select DISTINCT ledger_category_code
709 into l_ledger_category_code
710 from gl_ledgers
711 where ledger_id = l_ledger_id;
712 EXCEPTION
713 WHEN OTHERS THEN
714 l_ledger_category_code := 'NO_CATEGORY_CODE';
715 END;
716
717 IF ( l_entity_type_code = 'LEGAL' OR ( l_entity_type_code = 'ACCOUNTING' AND l_ledger_category_code = 'PRIMARY' ))
718 THEN
719 l_ledger_id := -1;
720 END IF;
721
722 INSERT INTO JG_ZZ_VAT_TRX_GT
723 (jg_info_v1 -- curr_code
724 ,jg_info_v2 -- entity_name
725 ,jg_info_v3 -- taxpayer_id
726 ,jg_info_v4 -- company_name
727 ,jg_info_v5 -- registration_number
728 ,jg_info_v6 -- country
729 ,jg_info_v7 -- address1
730 ,jg_info_v8 -- address2
731 ,jg_info_v9 -- address3
732 ,jg_info_v10 -- address4
733 ,jg_info_v11 -- city
734 ,jg_info_v12 -- postal_code
735 ,jg_info_v13 -- contact
736 ,jg_info_v14 -- phone_number
737 ,jg_info_v30 -- Header record indicator
738 ,jg_info_v15 --Tax Registration Number
739 ,jg_info_d1 --Period start date
740 ,jg_info_d2 --Period end date
741 ,jg_info_v16 ) --Reporting Status
742 VALUES(
743 l_curr_code
744 ,l_company_name ---l_rep_entity_name
745 ,l_registration_number --l_taxpayer_id
746 ,l_company_name
747 ,l_tax_registration_num
748 ,l_country
749 ,l_address1
750 ,l_address2
751 ,l_address3
752 ,l_address4
753 ,l_city
754 ,l_postal_code
755 ,l_contact
756 ,l_phone_number
757 ,'H'
758 ,l_tax_registration_num
759 ,l_period_start_date
760 ,l_period_end_date
761 ,l_reporting_status);
762 if g_debug = true then fnd_file.put_line(fnd_file.log,'Inserted Company Details in JG_ZZ_VAT_TRX_GT table'); end if;
763
764 IF P_CALLING_REPORT IS NOT NULL THEN --A1
765
766 /* commented during UT TEST
767 l_country_code := jg_zz_shared_pkg.GET_COUNTRY(mo_global.get_current_org_id);
768 */
769 -- Populate data for the other two sections
770
771 /* added during UT */
772 l_country_code := jg_zz_shared_pkg.GET_COUNTRY;
773 if l_country_code is null then
774 l_country_code := jg_zz_shared_pkg.GET_COUNTRY(mo_global.get_current_org_id);
775 end if;
776
777 if g_debug = true then fnd_file.put_line(fnd_file.log, 'l_country_code:'||l_country_code); end if;
778
779 IF P_VAT_TRX_TYPE IS NOT NULL THEN
780 BEGIN
781 FOR c_lookup_type IN c_get_lookup_values(P_VAT_TRX_TYPE)
782 LOOP
783 G_VAT_TRX_TYPE_MEANING := c_lookup_type.meaning;
784 G_VAT_TRX_TYPE_DESC := c_lookup_type.description;
785 END LOOP;
786 EXCEPTION
787 WHEN OTHERS THEN
788 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;
789 END;
790 END IF;
791
792 IF P_EX_VAT_TRX_TYPE IS NOT NULL THEN
793 BEGIN
794 FOR c_lookup_type_ex IN c_get_lookup_values(P_EX_VAT_TRX_TYPE)
795 LOOP
796 G_EX_VAT_TRX_TYPE_MEANING := c_lookup_type_ex.meaning;
797 G_EX_VAT_TRX_TYPE_DESC := c_lookup_type_ex.description;
798 END LOOP;
799 EXCEPTION
800 WHEN OTHERS THEN
801 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;
802 END;
803
804 END IF;
805
806 BEGIN
807
808 FOR c_company IN c_company_details
809 LOOP
810 G_COMPANY_NAME := c_company.Company_Name;
811 G_FUNCTIONAL_CURRENCY := c_company.FUNCTIONAL_CURRENCY_CODE;
812 END LOOP;
813
814 EXCEPTION
815 WHEN OTHERS THEN
816 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;
817 END;
818
819 /* Get Currency Precision */
820
821 BEGIN
822 FND_FILE.PUT_LINE(FND_FILE.LOG,'Functional Currency Code :'||G_FUNCTIONAL_CURRENCY);
823
824 SELECT precision
825 INTO G_PRECISION
826 FROM fnd_currencies
827 WHERE currency_code = G_FUNCTIONAL_CURRENCY;
828
829 FND_FILE.PUT_LINE(FND_FILE.LOG,'Functional Currency Precision :'||G_PRECISION);
830
831 EXCEPTION
832 WHEN OTHERS THEN
833 FND_FILE.PUT_LINE(FND_FILE.LOG,'error in getting currency precision');
834 END;
835
836 -- Begin of code for Israeli reports
837
838 --Populated the data for VAT AP Detail Report and VAT-File Not Related 835 -Israel
839 IF ( P_CALLING_REPORT = 'JEILAPVR' OR P_CALLING_REPORT = 'JEILN835' ) THEN
840 if g_debug = true then fnd_file.put_line(fnd_file.log,'Israeli VAT AP Detail Register Report concurrent is submitted'); end if;
841
842 BEGIN
843 --Populated the data for VAT AP Detail Report and VAT-File Not Related 835 -Israel
844 FOR israel_inv_lines IN israel_details
845 LOOP
846
847 INSERT INTO JG_ZZ_VAT_TRX_GT
848 (jg_info_v1 , --vendor_name
849 -- jg_info_n1 , --vendor_number
850 jg_info_v6 , --Vendor Number
851 jg_info_v2 , --SITE_NAME
852 jg_info_v10 , --TAX_REG_NUM /* UT TEST jg_info_n2 => jg_info_v10*/
853 jg_info_v11 , --INV_NUMBER /* UT TEST jg_info_n3 => jg_info_v11*/
854 jg_info_d1 , --INV_DATE
855 jg_info_v12 , --IMPORT_DOCUMENT_NUMBER /* UT TEST jg_info_n4 => jg_info_v12*/
856 jg_info_d2 , --IMPORT_DOCUMENT_DATE
857 jg_info_v3 , --TRX_LINE_CLASS
858 jg_info_v4 , --POSTED_FLAG
859 jg_info_n5 , --INVOICE_AMOUNT
860 jg_info_n6 , --S_VAT_ON_FIXED_ASSETS
861 jg_info_n7 , --P_VAT_ON_FIXED_ASSETS
862 jg_info_n8 , --I_VAT_ON_FIXED_ASSETS
863 jg_info_n9 , --S_VAT_ON_OTHER_TRX
864 jg_info_n10 , --P_VAT_ON_OTHER_TRX
865 jg_info_n11 , --I_VAT_ON_OTHER_TRX
866 jg_info_n12 , --VAT_FIXED_ASSETS
867 jg_info_n13 , --VAT_OTHER_TRX
868 jg_info_v5 --CLASS
869 )
870 VALUES(
871 israel_inv_lines.vendor_name
872 ,israel_inv_lines.vendor_number
873 ,israel_inv_lines.site_name
874 ,israel_inv_lines.tax_reg_num
875 ,israel_inv_lines.inv_number
876 ,israel_inv_lines.inv_date
877 ,israel_inv_lines.import_document_number
878 ,israel_inv_lines.import_document_date
879 ,israel_inv_lines.trx_line_class
880 ,israel_inv_lines.posted_flag
881 ,israel_inv_lines.invoice_amount
882 ,israel_inv_lines.s_vat_on_fixed_assets
883 ,israel_inv_lines.p_vat_on_fixed_assets
884 ,israel_inv_lines.i_vat_on_fixed_assets
885 ,israel_inv_lines.s_vat_on_other_trx
886 ,israel_inv_lines.p_vat_on_other_trx
887 ,israel_inv_lines.i_vat_on_other_trx
888 ,israel_inv_lines.vat_fixed_assets
889 ,israel_inv_lines.vat_other_trx
890 ,israel_inv_lines.class);
891 END LOOP;
892 EXCEPTION
893 WHEN OTHERS THEN
894 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;
895 END;
896 -- End of code for Israeli reports
897
898 -- Begin of code for ECE and Crotia reports.
899
900 ELSE -- i.e P_CALLING_REPORT <> 'JEILAPVR' and JEILN835
901
902 BEGIN
903 FOR c_data_rec1 IN c_new
904 LOOP
905
906 FOR c_vat_offset IN c_vat_or_offset (c_data_rec1.tax_rate_code)
907 LOOP
908 l_vat_or_offset := c_vat_offset.vat_or_offset;
909 END LOOP;
910
911 l_unpaid_amount := unpaid_amt(pn_inv_id => c_data_rec1.trx_id
912 ,pn_inv_amount => c_data_rec1.total_accounted_amount
913 ,pn_taxable_amount => c_data_rec1.taxable_amount
914 ,pv_offset_tax_code => c_data_rec1.OFFSET_TAX_RATE_CODE
915 ,pd_end_date => c_data_rec1.PERIOD_END_DATE);
916
917
918 INSERT INTO JG_ZZ_VAT_TRX_GT
919 (jg_info_n1 -- seq_number
920 ,jg_info_v1 -- inv_number
921 ,jg_info_v2 -- customer_name
922 ,jg_info_d1 -- tax_date
923 ,jg_info_d2 -- inv_date
924 ,jg_info_d3 -- accounting_date
925 ,jg_info_v3 -- tax_code_description
926 ,jg_info_n2 -- taxable_amount
927 ,jg_info_n3 -- tax_rate
928 ,jg_info_n4 -- tax_amount
929 ,jg_info_n5 -- trx_id , invoice_id
930 ,jg_info_v4 -- trx_class_code
931 ,jg_info_v5 -- trx_currency_code
932 ,jg_info_n6 -- Recoverable Tax
933 ,jg_info_n7 -- Non-Recoverable Tax
934 ,jg_info_v6 -- Tax Code Description
935 ,jg_info_n8 -- Functional Amount
936 ,jg_info_n10 -- Transaction Amount
937 ,jg_info_n13 -- Transaction Amount Offset
938 ,jg_info_v9 -- Rec flag
939 ,jg_info_v10 -- Doc Sequence Name
940 ,jg_info_v11 -- tax_reg_num
941 ,jg_info_v12 -- tax_code_vat_trx_type_code
942 ,jg_info_v13 -- TAX_CODE_VAT_TRX_TYPE_MEANING
943 ,jg_info_v14 -- TAX_CODE
944 ,jg_info_v7 -- TAX_RATE_VAT_TRX_TYPE_CODE
945 ,jg_info_n12
946 ,jg_info_v15 -- Reporting_Code
947 ,jg_info_n15 -- Pure VAT or VAT with Offset derived in the variable l_vat_or_offset
948 ,jg_info_v16 -- period_name
949 ,jg_info_v17 -- offset_flag
950 ,jg_info_v18 -- offset_tax_rate_code
951 ,jg_info_v19 -- CHK_VAT_AMOUNT_PAID
952 ,jg_info_n14 -- non_recoverable_unpaid amount
953 )
954 VALUES
955 (c_data_rec1.seq_number
956 ,c_data_rec1.inv_number
957 ,c_data_rec1.customer_name
958 ,c_data_rec1.tax_date
959 ,c_data_rec1.inv_date
960 ,c_data_rec1.gl_date
961 ,c_data_rec1.tax_rate_code_description
962 ,c_data_rec1.taxable_amount
963 ,c_data_rec1.tax_rate
964 ,c_data_rec1.tax_amount
965 ,c_data_rec1.trx_id
966 ,c_data_rec1.trx_line_class
967 ,c_data_rec1.trx_currency_code
968 ,c_data_rec1.recoverable
969 ,c_data_rec1.non_recoverable
970 ,c_data_rec1.tax_rate_code_description
971 ,c_data_rec1.total_accounted_amount
972 ,c_data_rec1.total_entered_amount
973 ,c_data_rec1.taxable_entered_amount
974 ,'M'
975 ,c_data_rec1.doc_seq_name
976 ,c_data_rec1.tax_reg_num
977 ,c_data_rec1.tax_rate_vat_trx_type_desc
978 ,c_data_rec1.tax_rate_code_vat_trx_type_mng
979 ,c_data_rec1.tax_rate_code
980 ,c_data_rec1.tax_rate_vat_trx_type_code
981 ,decode(NVL(c_data_rec1.chk_vat_amount_paid, 'N'), 'N', 0, 1)
982 ,c_data_rec1.reporting_code
983 ,l_vat_or_offset
984 ,c_data_rec1.gl_period
985 ,c_data_rec1.offset_flag
986 ,c_data_rec1.offset_tax_rate_code
987 ,c_data_rec1.chk_vat_amount_paid
988 ,l_unpaid_amount);
989
990 END LOOP;
991 EXCEPTION
992 WHEN OTHERS THEN
993 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;
994 END;
995
996 -- Populate data for the Summary by GL period
997 BEGIN
998 FOR c_data_rec IN c_data
999 LOOP
1000
1001 l_unpaid_amount := unpaid_amt(pn_inv_id => c_data_rec.trx_id
1002 ,pn_inv_amount => c_data_rec.total_accounted_amount
1003 ,pn_taxable_amount => c_data_rec.taxable_amount
1004 ,pv_offset_tax_code => c_data_rec.OFFSET_TAX_RATE_CODE
1005 ,pd_end_date => c_data_rec.PERIOD_END_DATE);
1006
1007 INSERT INTO JG_ZZ_VAT_TRX_GT
1008 (jg_info_n1 -- seq_number
1009 ,jg_info_v1 -- inv_number
1010 ,jg_info_v2 -- customer_name
1011 ,jg_info_d1 -- tax_date
1012 ,jg_info_d2 -- inv_date
1013 ,jg_info_d3 -- accounting_date
1014 ,jg_info_v3 -- tax_code_description
1015 ,jg_info_n2 -- taxable_amount
1016 ,jg_info_n3 -- tax_rate
1017 ,jg_info_n4 -- tax_amount
1018 ,jg_info_n5 -- trx_id
1019 ,jg_info_v4 -- trx_class_code
1020 ,jg_info_v5 -- trx_currency_code
1021 ,jg_info_n6 -- Recoverable Tax
1022 ,jg_info_n7 -- Non-Recoverable Tax
1023 ,jg_info_v6 -- Tax Code Description
1024 ,jg_info_n8 -- Functional Amount
1025 ,jg_info_n10 -- Transaction Amount
1026 ,jg_info_n13 -- Transaction Amount Offset
1027 ,jg_info_v9 -- Rec flag
1028 ,jg_info_v8 -- ACCOUNT FLEXFIELD
1029 ,jg_info_v10 -- Doc Sequence Name
1030 ,jg_info_v11 -- tax_reg_num
1031 ,jg_info_v12 -- tax_rate_vat_trx_type_desc
1032 ,jg_info_v13 -- tax_rate_code_vat_trx_type_mng
1033 ,jg_info_v14 -- TAX_RATE_CODE
1034 ,jg_info_v7 -- TAX_CODE_VAT_TRX_TYPE_CODE
1035 ,jg_info_n12
1036 ,jg_info_v15 -- REPORTING_CODE
1037 ,jg_info_v16 -- period_name
1038 ,jg_info_v17 -- offset_flag
1039 ,jg_info_v18 -- offset_tax_rate_code
1040 ,jg_info_v19 -- CHK_VAT_AMOUNT_PAID
1041 ,jg_info_n14 -- non_recoverable_unpaid amount
1042 )
1043 VALUES
1044 (c_data_rec.seq_number
1045 ,c_data_rec.inv_number
1046 ,c_data_rec.customer_name
1047 ,c_data_rec.tax_date
1048 ,c_data_rec.inv_date
1049 ,c_data_rec.gl_date
1050 ,c_data_rec.tax_rate_code_description
1051 ,c_data_rec.taxable_amount
1052 ,c_data_rec.tax_rate
1053 ,c_data_rec.tax_amount
1054 ,c_data_rec.trx_id
1055 ,c_data_rec.trx_line_class
1056 ,c_data_rec.trx_currency_code
1057 ,c_data_rec.recoverable
1058 ,c_data_rec.non_recoverable
1059 ,c_data_rec.tax_rate_code_description
1060 ,c_data_rec.total_accounted_amount
1061 ,c_data_rec.total_entered_amount
1062 ,c_data_rec.taxable_entered_amount
1063 ,'S'
1064 ,c_data_rec.account_flexfield
1065 ,c_data_rec.doc_seq_name
1066 ,c_data_rec.tax_reg_num
1067 ,c_data_rec.tax_rate_vat_trx_type_desc
1068 ,c_data_rec.tax_rate_code_vat_trx_type_mng
1069 ,c_data_rec.tax_rate_code
1070 ,c_data_rec.tax_rate_vat_trx_type_code
1071 ,decode(NVL(c_data_rec.chk_vat_amount_paid, 'N'), 'N', 0, 1)
1072 ,c_data_rec.reporting_code
1073 ,c_data_rec.gl_period
1074 ,c_data_rec.offset_flag
1075 ,c_data_rec.offset_tax_rate_code
1076 ,c_data_rec.chk_vat_amount_paid
1077 ,l_unpaid_amount);
1078
1079 END LOOP;
1080 EXCEPTION
1081 WHEN OTHERS THEN
1082 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;
1083 END;
1084
1085 BEGIN
1086 FOR c_end_date IN c_period_end_date
1087 LOOP
1088 l_end_date := c_end_date.PERIOD_END_DATE;
1089 END LOOP;
1090
1091 EXCEPTION
1092 WHEN OTHERS THEN
1093 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;
1094 END;
1095
1096 -- Implementing seq num ---------
1097 BEGIN
1098 SELECT NVL(enable_report_sequence_flag ,'N')
1099 INTO l_enable_report_sequence_flag
1100 FROM jg_zz_vat_rep_entities
1101 WHERE vat_reporting_entity_id = P_VAT_REPORTING_ENTITY_ID;
1102
1103 IF l_enable_report_sequence_flag = 'Y' THEN
1104
1105 FOR c_data IN temp_cur
1106 LOOP
1107 IF c_data.vat_code <> v_prev_vat_code or c_data.vat_code IS NULL THEN
1108 v_count := 0;
1109 END IF;
1110
1111 SELECT distinct JG_INFO_V40
1112 INTO v_is_seq_updated
1113 FROM JG_ZZ_VAT_TRX_GT T1
1114 WHERE T1.jg_info_n5 = c_data.trx_id
1115 AND T1.jg_info_v7 = c_data.vat_code
1116 AND T1.jg_info_v15 = c_data.reporting_code;
1117
1118 IF NVL(v_is_seq_updated,'N') <> 'Y' THEN
1119
1120 v_count := v_count+1;
1121
1122 UPDATE JG_ZZ_VAT_TRX_GT SET jg_info_n1 = v_count ,
1123 jg_info_v40 = 'Y'
1124 WHERE jg_info_n5 = c_data.trx_id
1125 AND jg_info_v7 = c_data.vat_code
1126 AND jg_info_v15 = c_data.reporting_code;
1127 END IF;
1128
1129 v_prev_vat_code := c_data.vat_code;
1130
1131 END LOOP;
1132 END IF;
1133 EXCEPTION
1134 WHEN OTHERS THEN
1135 fnd_file.put_line(fnd_file.log,' Failed while Implementing seq num : ' || SUBSTR(SQLERRM,1,200));
1136 END;
1137 -- End of Implementing seq num ---------
1138
1139 P_WHERE_CLAUSE := ' AND ';
1140
1141 -- Exclude paid transactions for the
1142 -- Annex Report
1143 l_cleared_select := '(SELECT NVL((SELECT NVL(SUM(pay.payment_base_amount),0)
1144 FROM ap_invoices_all INV, ap_invoice_payments_all PAY, ap_checks_all CHECKS
1145 WHERE inv.invoice_id = pay.invoice_id
1146 AND checks.check_id = pay.check_id
1147 AND jg_info_n5 = inv.invoice_id
1148 AND checks.status_lookup_code IN ( ''CLEARED'' , ''RECONCILED'' , ''CLEARED BUT UNACCOUNTED'', ''RECONCILED UNACCOUNTED'' )';
1149
1150 l_cleared_select1 := '* decode(jg_info_n15, NULL, jg_info_n8, jg_info_n2)
1151 / (SELECT inv1.base_amount
1152 FROM ap_invoices_all INV1
1153 WHERE jg_info_n5 = inv1.invoice_id ),0) from dual )';
1154
1155 l_unpaid_amt_select := 'AND (SELECT nvl(sum(amount_remaining),99999)
1156 FROM ap_invoices_all INV,
1157 ap_invoice_payments_all PAY,
1158 ap_checks_all CHECKS,
1159 ap_payment_schedules_all APS
1160 WHERE APS.invoice_id = inv.invoice_id
1161 AND inv.invoice_id = pay.invoice_id
1162 AND checks.check_id = pay.check_id
1163 AND jg_info_n5 = inv.invoice_id
1164 AND checks.status_lookup_code IN ( ''CLEARED'' , ''RECONCILED'' , ''CLEARED BUT UNACCOUNTED'', ''RECONCILED UNACCOUNTED'' )';
1165
1166
1167 IF P_VAT_TRX_TYPE IS NULL
1168 AND P_EX_VAT_TRX_TYPE IS NULL THEN
1169
1170 IF ((l_country_code = 'HU' OR l_country_code = 'PL')) THEN
1171
1172 P_WHERE_CLAUSE := P_WHERE_CLAUSE || ' (((jg_info_n4 > ' || l_cleared_select
1173 || ' AND trunc(checks.cleared_date) <= ''' || l_end_date
1174 || ''' AND NVL(jg_info_v19,''N'') = ''Y'') ' || l_cleared_select1
1175 || ' ) AND (jg_info_n12 = 1 ) AND jg_info_n15 IS NULL ' || l_unpaid_amt_select
1176 || ' AND trunc(checks.cleared_date) <= '''||l_end_date||''' ) > 0 AND '''||l_country_code||''' <> ''PL'')'
1177 || ' OR ((decode(jg_info_n15, NULL, jg_info_n8, jg_info_n2) > ' ||l_cleared_select
1178 || ' AND trunc(checks.cleared_date) <= ''' || l_end_date || ''' AND ( ''' || l_country_code || ''' = ''PL'' OR '
1179 || ' NVL(jg_info_v19,''N'') = ''N'' )) ' || l_cleared_select1 || ' ) AND (''' || l_country_code || ''' = ''PL'' OR '
1180 || 'jg_info_n12 = 0 ) ' || l_unpaid_amt_select ||' AND trunc(checks.cleared_date) <= ''' || l_end_date || ''' ) > 0 ))';
1181
1182 ELSIF (l_country_code = 'SK') THEN
1183
1184 P_WHERE_CLAUSE := P_WHERE_CLAUSE || ' ((jg_info_n4 <= ' || l_cleared_select ||' AND trunc(checks.cleared_date) <= ''' ||l_end_date
1185 || ''' AND jg_info_v19 = ''Y'') AND (jg_info_n12 = 1 )) OR (jg_info_n12 = 0 ))';
1186
1187 ELSIF (l_country_code = 'HR') THEN
1188 /*
1189 || Vendor Invoice Tax Report, Croatia
1190 || ELSIF added by Ramananda.
1191 || Requirements of this report are satisfied with the ECE Payables Tax Report
1192 || No Filtering conditions are required for Croatia
1193 */
1194 if g_debug = true then fnd_file.put_line(fnd_file.log,'Croatian Supplier Invoice Tax Report concurrent is submitted' ); end if;
1195 NULL ;
1196 END IF;
1197
1198 ELSE
1199 IF ((l_country_code = 'HU' OR l_country_code = 'PL')) THEN
1200
1201
1202 P_WHERE_CLAUSE := P_WHERE_CLAUSE || ' (((jg_info_n4 > ' || l_cleared_select
1203 || ' AND trunc(checks.cleared_date) <= ''' ||l_end_date|| ''' AND NVL(jg_info_v19,''N'') = ''Y'') '
1204 || l_cleared_select1 || ' ) AND (jg_info_n12 = 1 ) AND jg_info_n15 IS NULL ' || l_unpaid_amt_select
1205 || ' AND trunc(checks.cleared_date) <= ''' ||l_end_date|| ''' ) > 0 AND ''' ||l_country_code|| ''' <> ''PL'' )'
1206 || ' OR ((decode(jg_info_n15, NULL, jg_info_n8, jg_info_n2) > ' || l_cleared_select
1207 || ' AND trunc(checks.cleared_date) <= ''' ||l_end_date|| ''' AND ( '''|| l_country_code
1208 || ''' = ''PL'' OR NVL(jg_info_v19,''N'') = ''N''))' || l_cleared_select1 || ') AND (''' ||l_country_code|| ''' = ''PL'''
1209 || ' OR jg_info_n12 = 0 ) ' || l_unpaid_amt_select|| ' AND trunc(checks.cleared_date) <= ''' ||l_end_date|| ''' ) > 0 ))';
1210
1211
1212 ELSIF (l_country_code = 'SK') THEN
1213
1214 P_WHERE_CLAUSE := P_WHERE_CLAUSE || ' ((jg_info_n4 <= ' || l_cleared_select
1215 || ' AND trunc(checks.cleared_date) <= ''' ||l_end_date
1216 || ''' AND jg_info_v19 = ''Y'') AND (jg_info_n12 = 1 )) OR (jg_info_n12 = 0 ))';
1217
1218 ELSIF (l_country_code = 'HR') THEN
1219 if g_debug = true then fnd_file.put_line(fnd_file.log,'Croatian Supplier Invoice Tax Report concurrent is submitted' ); end if;
1220 NULL ;
1221 END IF;
1222
1223 END IF ;
1224
1225 IF P_WHERE_CLAUSE = ' AND ' THEN
1226 P_WHERE_CLAUSE := ' AND 1 = 1 ';
1227 END IF;
1228
1229 END IF; --END IF for IF l_country_code = 'IL'
1230
1231 -- End of code for ECE and Crotia reports.
1232
1233 ELSE -- FOR IF calling report is NULL
1234
1235 BEGIN
1236 FOR c_data_rec2 IN c_complete
1237 LOOP
1238
1239
1240 INSERT INTO JG_ZZ_VAT_TRX_GT
1241 (jg_info_n1 -- seq_number
1242 ,jg_info_v1 -- inv_number
1243 ,jg_info_v2 -- customer_name
1244 ,jg_info_d1 -- tax_date
1245 ,jg_info_d2 -- inv_date
1246 ,jg_info_d3 -- accounting_date
1247 ,jg_info_v3 -- tax_code_description
1248 ,jg_info_n2 -- taxable_amount
1249 ,jg_info_n3 -- tax_rate
1250 ,jg_info_n4 -- tax_amount
1251 ,jg_info_n5 -- trx_id
1252 ,jg_info_v4 -- trx_class_code
1253 ,jg_info_v5 -- trx_currency_code
1254 ,jg_info_v20 -- TAX_RECOVERABLE_FLAG
1255 ,jg_info_v6 -- Tax Code Description
1256 ,jg_info_n8 -- TAX_AMT_FUNCL_CURR
1257 ,jg_info_n10 -- TAXABLE_AMT_FUNCL_CURR
1258 ,jg_info_v8 -- ACCOUNT FLEXFIELD
1259 ,jg_info_v10 -- Doc Sequence Name
1260 ,jg_info_v11 -- tax_reg_num
1261 ,jg_info_v12 -- TAX_RATE_VAT_TRX_TYPE_DESC
1262 ,jg_info_v13 -- TAX_CODE_VAT_TRX_TYPE_MEANING
1263 ,jg_info_v14 -- TAX_CODE
1264 ,jg_info_v7 -- TAX_CODE_VAT_TRX_TYPE_CODE
1265 ,jg_info_v15 -- TAX_CODE_TYPE_CODE
1266 ,jg_info_v16 -- period_name
1267 ,jg_info_v17 -- offset_flag
1268 ,jg_info_v18 -- offset_tax_rate_code
1269 ,jg_info_v19 -- CHK_VAT_AMOUNT_PAID
1270 ,jg_info_v21 -- reporting_code
1271 )
1272 VALUES
1273 (c_data_rec2.seq_number
1274 ,c_data_rec2.inv_number
1275 ,c_data_rec2.customer_name
1276 ,c_data_rec2.tax_date
1277 ,c_data_rec2.inv_date
1278 ,c_data_rec2.gl_date
1279 ,c_data_rec2.tax_rate_code_description
1280 ,c_data_rec2.taxable_amount
1281 ,c_data_rec2.tax_rate
1282 ,c_data_rec2.tax_amount
1283 ,c_data_rec2.trx_id
1284 ,c_data_rec2.trx_line_class
1285 ,c_data_rec2.trx_currency_code
1286 ,c_data_rec2.tax_recoverable_flag
1287 ,c_data_rec2.tax_rate_code_description
1288 ,c_data_rec2.tax_amt_funcl_curr
1289 ,c_data_rec2.taxable_amt_funcl_curr
1290 ,c_data_rec2.account_flexfield
1291 ,c_data_rec2.doc_seq_name
1292 ,c_data_rec2.tax_reg_num
1293 ,c_data_rec2.tax_rate_vat_trx_type_desc
1294 ,c_data_rec2.tax_rate_code_vat_trx_type_mng
1295 ,c_data_rec2.tax_rate_code
1296 ,c_data_rec2.tax_rate_vat_trx_type_code
1297 ,c_data_rec2.tax_type_code
1298 ,c_data_rec2.gl_period
1299 ,c_data_rec2.offset_flag
1300 ,c_data_rec2.offset_tax_rate_code
1301 ,c_data_rec2.chk_vat_amount_paid
1302 ,c_data_rec2.reporting_code);
1303
1304 END LOOP;
1305
1306 EXCEPTION
1307 WHEN OTHERS THEN
1308 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;
1309 END;
1310
1311 END IF;
1312
1313
1314 EXCEPTION
1315 WHEN OTHERS THEN
1316 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;
1317 RETURN(FALSE);
1318 END;
1319
1320 if g_debug = true then
1321 fnd_file.put_line(fnd_file.log,'P_WHERE_CLAUSE: ' ||P_WHERE_CLAUSE);
1322 end if;
1323
1324 RETURN(TRUE);
1325
1326 END before_report;
1327
1328 /*
1329 REM +======================================================================+
1330 REM Name: GET_EX_VAT_TRX_TYPE_MEANING
1331 REM
1332 REM Description: Returns the Vat Transaction type meaning
1333 REM
1334 REM Parameters: None
1335 REM +======================================================================+
1336 */
1337 FUNCTION get_vat_trx_type_meaning RETURN VARCHAR2 IS
1338 BEGIN
1339 RETURN G_VAT_TRX_TYPE_MEANING;
1340 END get_vat_trx_type_meaning;
1341
1342 /*
1343 REM +======================================================================+
1344 REM Name: GET_EX_VAT_TRX_TYPE_MEANING
1345 REM
1346 REM Description: Returns the Exclude Vat Transaction type meaning
1347 REM
1348 REM Parameters: None
1349 REM +======================================================================+
1350 */
1351 FUNCTION get_ex_vat_trx_type_meaning RETURN VARCHAR2 IS
1352 BEGIN
1353 RETURN G_EX_VAT_TRX_TYPE_MEANING;
1354 END get_ex_vat_trx_type_meaning;
1355
1356 /*
1357 REM +======================================================================+
1358 REM Name: GET_VAT_TRX_TYPE_DESC
1359 REM
1360 REM Description: Returns the Vat Transaction type description
1361 REM
1362 REM Parameters: None
1363 REM +======================================================================+
1364 */
1365 FUNCTION get_vat_trx_type_desc RETURN VARCHAR2 IS
1366 BEGIN
1367 RETURN G_VAT_TRX_TYPE_DESC;
1368 END get_vat_trx_type_desc;
1369
1370 /*
1371 REM +======================================================================+
1372 REM Name: GET_EX_VAT_TRX_TYPE_DESC
1373 REM
1374 REM Description: Returns the Exclude Vat Transaction type description
1375 REM
1376 REM Parameters: None
1377 REM +======================================================================+
1378 */
1379 FUNCTION get_ex_vat_trx_type_desc RETURN VARCHAR2 IS
1380 BEGIN
1381 RETURN G_EX_VAT_TRX_TYPE_DESC;
1382 END get_ex_vat_trx_type_desc;
1383
1384 /*
1385 REM +======================================================================+
1386 REM Name: GET_PREPAYMENTS_MEANING
1387 REM
1388 REM Description: Returns the Include Prepayments parameter meaning.
1389 REM
1390 REM Parameters: None
1391 REM +======================================================================+
1392 */
1393 FUNCTION get_prepayments_meaning RETURN VARCHAR2 IS
1394 CURSOR c_prepay_meaning
1395 IS
1396 SELECT meaning
1397 FROM fnd_lookups
1398 WHERE lookup_code = P_INC_PREPAYMENTS
1399 AND lookup_type = 'YES_NO';
1400
1401 BEGIN
1402
1403 IF P_INC_PREPAYMENTS IS NOT NULL THEN
1404 FOR c_meaning IN c_prepay_meaning
1405 LOOP
1406 G_INC_PREPAYMENTS := c_meaning.meaning;
1407 END LOOP;
1408 END IF;
1409
1410 RETURN G_INC_PREPAYMENTS;
1411
1412 EXCEPTION
1413 WHEN OTHERS THEN
1414 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;
1415 RETURN NULL;
1416 END get_prepayments_meaning;
1417
1418 /*
1419 REM +======================================================================+
1420 REM Name: GET_FUNCTIONAL_CURRENCY
1421 REM
1422 REM Description: Returns the functional currency
1423 REM
1424 REM Parameters: None
1425 REM +======================================================================+
1426 */
1427 FUNCTION get_functional_currency RETURN VARCHAR2 IS
1428 BEGIN
1429 RETURN G_FUNCTIONAL_CURRENCY;
1430 END get_functional_currency;
1431
1432 /*
1433 REM +======================================================================+
1434 REM Name: CF_TAX_CODE_TYPE_CODE
1435 REM
1436 REM Description: Returns 1 or 0 based on the tax type.
1437 REM
1438 REM Parameters: TAX_CODE_TYPE_CODE => Whether tax is VAT or OFFSET
1439 REM +======================================================================+
1440 */
1441 FUNCTION CF_Tax_Code_Type_Code(TAX_CODE_TYPE_CODE VARCHAR2) RETURN NUMBER IS
1442 BEGIN
1443 IF TAX_CODE_TYPE_CODE = 'XOFFSET' THEN
1444 RETURN 1;
1445 ELSE
1446 RETURN 0;
1447 END IF;
1448 END;
1449
1450 /*
1451 REM +======================================================================+
1452 REM Name: GET_TRN
1453 REM
1454 REM Description: Fetches and returns the TRN number
1455 REM
1456 REM Parameters: None
1457 REM +======================================================================+
1458 */
1459 FUNCTION get_trn RETURN VARCHAR2 IS
1460 l_trn VARCHAR2(30) := '';
1461
1462 CURSOR c_get_trn
1463 IS
1464 SELECT JZVRS.TAX_REGISTRATION_NUMBER
1465 FROM JG_ZZ_VAT_REP_STATUS JZVRS
1466 WHERE JZVRS.VAT_REPORTING_ENTITY_ID = P_VAT_REPORTING_ENTITY_ID
1467 /* added during UT */
1468 AND (P_TAX_CALENDAR_PERIOD is null and jzvrs.tax_calendar_period = P_TAX_CALENDAR_PERIOD)
1469 AND jzvrs.source = 'AP'
1470 AND rownum = 1;
1471
1472 BEGIN
1473
1474 FOR c_trn IN c_get_trn
1475 LOOP
1476 l_trn := c_trn.TAX_REGISTRATION_NUMBER;
1477 END LOOP;
1478
1479 RETURN l_trn;
1480 EXCEPTION
1481 WHEN OTHERS THEN
1482 if g_debug = true then
1483 fnd_file.put_line(fnd_file.log,' Failed while extracting the Tax Reginstration Number. Error : ' || SUBSTR(SQLERRM,1,200));
1484 end if;
1485 Return NULL;
1486 END get_trn;
1487
1488 /*
1489 REM +======================================================================+
1490 REM Name: CF_SEQ_NO
1491 REM
1492 REM Description: This function returns the sequence number for a particular
1493 REM country. The sequence no is incremented each time this
1494 REM function is called.
1495 REM
1496 REM Parameters: None
1497 REM +======================================================================+
1498 */
1499 FUNCTION CF_seq_no RETURN NUMBER IS
1500 l_country_code VARCHAR2(5);
1501 BEGIN
1502 /* added during UT */
1503 l_country_code := jg_zz_shared_pkg.GET_COUNTRY;
1504 if l_country_code is null then
1505 l_country_code := jg_zz_shared_pkg.GET_COUNTRY(mo_global.get_current_org_id);
1506 end if;
1507
1508 IF l_country_code = 'PL' THEN
1509 G_SEQ_NO := G_SEQ_NO + 1;
1510 RETURN (G_SEQ_NO);
1511 ELSE
1512 RETURN NULL;
1513 END IF;
1514
1515 END CF_seq_no;
1516
1517 END JG_ZZ_SUMMARY_AP_PKG;