[Home] [Help]
PACKAGE BODY: APPS.JG_ZZ_JOURNAL_AP_PKG
Source
1 PACKAGE BODY JG_ZZ_JOURNAL_AP_PKG
2 -- $Header: jgzzjournalapb.pls 120.55.12020000.11 2013/04/03 09:43:16 abhijsar ship $
3 AS
4
5 -- +======================================================================+
6 -- Name: GET_START_SEQUENCE
7 --
8 -- Description: This function is private to this package. It is called to
9 -- get the start_sequence_num for the report JEITAPPV and JEITAPSR
10 -- transactions.
11 --
12 -- Parameters: None
13 -- +======================================================================+
14
15 FUNCTION get_start_sequence RETURN NUMBER IS
16 l_start_seq NUMBER;
17 l_period_start_date DATE;
18 BEGIN
19 SELECT max(vrs.period_start_date)
20 INTO l_period_start_date
21 FROM jg_zz_vat_final_reports vfr,
22 jg_zz_vat_rep_status vrs
23 WHERE vfr.report_name = p_report_name
24 AND vfr.vat_register_id = p_vat_register_id
25 AND vrs.reporting_status_id = vfr.reporting_status_id;
26
27 SELECT nvl(start_sequence_num,0)
28 INTO l_start_seq
29 FROM jg_zz_vat_final_reports vfr,
30 jg_zz_vat_rep_status vrs
31 WHERE vfr.report_name = p_report_name
32 AND vfr.vat_register_id = p_vat_register_id
33 AND vrs.reporting_status_id = vfr.reporting_status_id
34 AND vrs.period_start_date = l_period_start_date;
35
36 RETURN l_start_seq;
37 EXCEPTION
38 WHEN others THEN
39 RETURN 0;
40 END get_start_sequence;
41
42
43 -- +======================================================================+
44 -- Name: BEFORE_REPORT
45 --
46 -- Description: This function is called as a before report trigger by the
47 -- data template. It populates the data in the global temporary table
48 -- and creates the dynamic where clause for the data template
49 -- queries(lexical reference).
50 --
51 -- Parameters: None
52 -- +======================================================================+
53 --
54 FUNCTION beforeReport RETURN BOOLEAN
55 IS
56 l_address_line_1 VARCHAR2 (240);
57 l_address_line_2 VARCHAR2 (240);
58 l_address_line_3 VARCHAR2 (240);
59 l_address_line_4 VARCHAR2 (240);
60 l_city VARCHAR2 (60);
61 l_company_name VARCHAR2 (240);
62 l_contact_name VARCHAR2 (360);
63 l_country VARCHAR2 (60);
64 l_func_curr VARCHAR2 (30);
65 l_legal_entity_id NUMBER;
66 l_legal_entity_name VARCHAR2 (240);
67 l_period_end_date DATE;
68 l_period_start_date DATE;
69 l_to_period_end_date DATE; -- Bug8267272
70 l_to_period_start_date DATE; -- Bug8267272
71 l_phone_number VARCHAR2 (40);
72 l_postal_code VARCHAR2 (60);
73 -- Added for GLOB-006 ER
74 l_province VARCHAR2(120);
75 l_comm_num VARCHAR2(30);
76 l_vat_reg_num VARCHAR2(50);
77 -- end here
78 l_registration_num VARCHAR2 (30);
79 l_reporting_status VARCHAR2 (60);
80 l_to_reporting_status VARCHAR2 (60); -- Bug8267272
81 l_tax_payer_id VARCHAR2 (60);
82 l_tax_registration_num VARCHAR2 (240);
83 l_to_tax_registration_num VARCHAR2 (240); -- Bug8267272
84 l_tax_regime VARCHAR2(240);
85 l_vat_register_name VARCHAR2(240);
86 l_start_seq NUMBER(15);
87
88
89 BEGIN
90 fnd_file.put_line(fnd_file.log,' ** Inside BeforeReport ** ');
91 p_debug_flag := 'Y' ;
92 g_precision :=0;
93
94 IF P_REPORT_NAME IN ('JEITAPSR', 'JEITAPPV') THEN
95 BEGIN
96 fnd_file.put_line(fnd_file.log,'Fetching Register name...');
97 SELECT register_name
98 INTO l_vat_register_name
99 FROM jg_zz_vat_registers_vl
100 WHERE vat_register_id = p_vat_register_id ;
101 EXCEPTION
102 WHEN NO_DATA_FOUND THEN
103 fnd_file.put_line(fnd_file.log,'Cannot derive VAT Register Name for Register ID:'||p_vat_register_id) ;
104 l_rec_count := 0;
105 RAISE ;
106 END ;
107 END IF ;
108 IF p_debug_flag = 'Y' THEN
109 fnd_file.put_line(fnd_file.log,'**********************************************************');
110 IF P_REPORT_NAME = 'JEITAPSR' THEN
111 fnd_file.put_line(fnd_file.log,' Italian Sales VAT Register ');
112 ELSIF P_REPORT_NAME = 'JEITAPPV' THEN
113 fnd_file.put_line(fnd_file.log,' Italian Purchase VAT Register ');
114 ELSIF P_REPORT_NAME = 'JEBEDV08' THEN
115 fnd_file.put_line(fnd_file.log,' Belgian VAT Purchases Journal ');
116 ELSIF P_REPORT_NAME = 'JEESRVAR' THEN
117 fnd_file.put_line(fnd_file.log,' Spanish Inter-EU Invoices Journal Report ');
118 ELSIF P_REPORT_NAME = 'JEESRPVP' THEN
119 fnd_file.put_line(fnd_file.log,' Spanish Input VAT Journal Report ');
120 END IF;
121 fnd_file.put_line(fnd_file.log,'**********************************************************');
122 fnd_file.put_line(fnd_file.log,' ** Report Paramters ** ');
123 fnd_file.put_line(fnd_file.log,'P_VAT_REP_ENTITY_ID :'|| P_VAT_REP_ENTITY_ID );
124 fnd_file.put_line(fnd_file.log,'P_PERIOD :'|| P_PERIOD );
125 fnd_file.put_line(fnd_file.log,'P_PERIOD_TO :'|| P_PERIOD_TO ); -- Bug8267272
126 fnd_file.put_line(fnd_file.log,'P_DOCUMENT_SEQUENCE_NAME_FROM:'|| P_DOCUMENT_SEQUENCE_NAME_FROM);
127 fnd_file.put_line(fnd_file.log,'P_DOCUMENT_SEQUENCE_NAME_TO :'|| P_DOCUMENT_SEQUENCE_NAME_TO );
128 fnd_file.put_line(fnd_file.log,'P_VENDOR_NAME_FROM :'|| P_VENDOR_NAME_FROM );
129 fnd_file.put_line(fnd_file.log,'P_VENDOR_NAME_TO :'|| P_VENDOR_NAME_TO );
130 fnd_file.put_line(fnd_file.log,'P_DETAIL_SUMMARY :'|| P_DETAIL_SUMMARY );
131 fnd_file.put_line(fnd_file.log,'P_VAT_REGISTER_ID :'|| P_VAT_REGISTER_ID );
132 fnd_file.put_line(fnd_file.log,'P_TAX_TYPE :'|| P_TAX_TYPE );
133 fnd_file.put_line(fnd_file.log,'P_REGISTER_TYPE :'|| P_REGISTER_TYPE );
134 fnd_file.put_line(fnd_file.log,'P_START_INV_SEQUENCE :'|| P_START_INV_SEQUENCE );
135 fnd_file.put_line(fnd_file.log,'P_BALANCING_SEGMENT :'|| P_BALANCING_SEGMENT );
136 fnd_file.put_line(fnd_file.log,'P_REPORT_NAME :'|| P_REPORT_NAME );
137 fnd_file.put_line(fnd_file.log,' ');
138 END IF ;
139
140 fnd_file.put_line(fnd_file.log,'Before Report Trigger');
141 jg_zz_common_pkg.funct_curr_legal(x_func_curr_code => l_func_curr
142 ,x_rep_entity_name => l_legal_entity_name
143 ,x_legal_entity_id => l_legal_entity_id
144 ,x_taxpayer_id => l_tax_payer_id
145 ,pn_vat_rep_entity_id => p_vat_rep_entity_id
146 ,pv_period_name => p_period);
147 IF p_debug_flag = 'Y' THEN
148 fnd_file.put_line(fnd_file.log,'l_func_curr :'|| l_func_curr );
149 fnd_file.put_line(fnd_file.log,'l_legal_entity_name :'|| l_legal_entity_name );
150 fnd_file.put_line(fnd_file.log,'l_legal_entity_id :'|| l_legal_entity_id );
151 fnd_file.put_line(fnd_file.log,'l_tax_payer_id :'|| l_tax_payer_id );
152 fnd_file.put_line(fnd_file.log,' ');
153 END IF ;
154
155 fnd_file.put_line(fnd_file.log,'Calling jg_zz_common_pkg.tax_registration');
156 jg_zz_common_pkg.tax_registration(x_tax_registration => l_tax_registration_num
157 ,x_period_start_date => l_period_start_date
158 ,x_period_end_date => l_period_end_date
159 ,x_status => l_reporting_status
160 ,pn_vat_rep_entity_id => p_vat_rep_entity_id
161 ,pv_period_name => p_period
162 ,pv_source => 'ALL');
163
164 -- Bug8267272 Start Below code added to get end_date of TO_PERIOD
165 IF nvl(P_REPORT_NAME,'ZZ') in('JEESRVAR','JEESRPVP') THEN
166
167 fnd_file.put_line(fnd_file.log,'Getting TO_PERIOD_INFO');
168
169 jg_zz_common_pkg.tax_registration(x_tax_registration => l_to_tax_registration_num
170 ,x_period_start_date => l_to_period_start_date
171 ,x_period_end_date => l_to_period_end_date
172 ,x_status => l_to_reporting_status
173 ,pn_vat_rep_entity_id => p_vat_rep_entity_id
174 ,pv_period_name => p_period_to
175 ,pv_source => 'ALL');
176 fnd_file.put_line(fnd_file.log,'To Period Start Date :'||l_to_period_start_date);
177 fnd_file.put_line(fnd_file.log,'To Period End Date :'||l_to_period_end_date);
178 END IF;
179 -- Bug8267272 End
180
181 fnd_file.put_line(fnd_file.log,'JG_ZZ_VAT_REP_UTILITY.get_period_status');
182 IF P_REPORT_NAME = 'JEITAPSR' OR P_REPORT_NAME = 'JEITAPPV' THEN
183 -- Get the Start Sequence Number for the Previous Finally Reported period for same report and vat register.
184 l_start_seq := get_start_sequence;
185
186 l_reporting_status := JG_ZZ_VAT_REP_UTILITY.get_period_status(pn_vat_reporting_entity_id => p_vat_rep_entity_id
187 ,pv_tax_calendar_period => p_period
188 ,pv_tax_calendar_year => null
189 ,pv_source => NULL
190 ,pv_report_name => p_report_name
191 ,pv_vat_register_id => p_vat_register_id);
192
193 -- Insert the record into jg_zz_vat_trx_gt with Sequence and reporting mode info
194 IF l_reporting_status = 'COPY' THEN
195 SELECT last_start_sequence_num
196 INTO l_start_seq
197 FROM jg_zz_vat_final_reports vfr,
198 jg_zz_vat_rep_status vrs
199 WHERE vfr.report_name = p_report_name
200 AND vfr.vat_register_id = p_vat_register_id
201 AND vrs.reporting_status_id = vfr.reporting_status_id
202 AND vrs.tax_calendar_period = p_period;
203 END IF;
204
205 INSERT INTO jg_zz_vat_trx_gt (jg_info_n1,
206 jg_info_v1,
207 jg_info_v30)
208 VALUES (l_start_seq,
209 l_reporting_status,
210 'SEQ');
211 ELSE
212 l_reporting_status := JG_ZZ_VAT_REP_UTILITY.get_period_status(pn_vat_reporting_entity_id => p_vat_rep_entity_id
213 ,pv_tax_calendar_period => p_period
214 ,pv_tax_calendar_year => null
215 ,pv_source => NULL
216 ,pv_report_name => p_report_name);
217 END IF;
218
219 IF p_debug_flag = 'Y' THEN
220 fnd_file.put_line(fnd_file.log,'l_tax_registration_num:'|| l_tax_registration_num );
221 fnd_file.put_line(fnd_file.log,'l_period_start_date :'|| l_period_start_date );
222 fnd_file.put_line(fnd_file.log,'l_period_end_date :'|| l_period_end_date );
223 fnd_file.put_line(fnd_file.log,'l_reporting_status :'|| l_reporting_status );
224 fnd_file.put_line(fnd_file.log,' ');
225 END IF ;
226 fnd_file.put_line(fnd_file.log,'Calling jg_zz_common_pkg.company_detail');
227 jg_zz_common_pkg.company_detail(x_company_name => l_company_name
228 ,x_registration_number => l_registration_num
229 ,x_country => l_country
230 ,x_address1 => l_address_line_1
231 ,x_address2 => l_address_line_2
232 ,x_address3 => l_address_line_3
233 ,x_address4 => l_address_line_4
234 ,x_city => l_city
235 ,x_postal_code => l_postal_code
236 ,x_contact => l_contact_name
237 ,x_phone_number => l_phone_number
238 ,x_province => l_province
239 ,x_comm_number => l_comm_num
240 ,x_vat_reg_num => l_vat_reg_num
241 ,pn_legal_entity_id => l_legal_entity_id
242 ,p_vat_reporting_entity_id => P_VAT_REP_ENTITY_ID);
243
244 IF p_debug_flag = 'Y' THEN
245 fnd_file.put_line(fnd_file.log,'l_company_name :'|| l_company_name );
246 fnd_file.put_line(fnd_file.log,'l_registration_num:'|| l_registration_num );
247 fnd_file.put_line(fnd_file.log,'l_country :'|| l_country );
248 fnd_file.put_line(fnd_file.log,'l_address_line_1 :'|| l_address_line_1 );
249 fnd_file.put_line(fnd_file.log,'l_address_line_2 :'|| l_address_line_2 );
250 fnd_file.put_line(fnd_file.log,'l_address_line_3 :'|| l_address_line_3 );
251 fnd_file.put_line(fnd_file.log,'l_address_line_4 :'|| l_address_line_4 );
252 fnd_file.put_line(fnd_file.log,'l_city :'|| l_city );
253 fnd_file.put_line(fnd_file.log,'l_postal_code :'|| l_postal_code );
254 fnd_file.put_line(fnd_file.log,'l_contact_name :'|| l_contact_name );
255 fnd_file.put_line(fnd_file.log,'l_phone_number :'|| l_phone_number );
256 fnd_file.put_line(fnd_file.log,' ');
257 END IF ;
258
259 /* Get Currency Precision */ --Bug:8201935
260
261 BEGIN
262 FND_FILE.PUT_LINE(FND_FILE.LOG,'Functional Currency Code :'||l_func_curr);
263
264 SELECT precision
265 INTO g_precision
266 FROM fnd_currencies
267 WHERE currency_code = l_func_curr;
268
269 FND_FILE.PUT_LINE(FND_FILE.LOG,'Functional Currency Precision :'||g_precision);
270
271 EXCEPTION
272 WHEN OTHERS THEN
273 FND_FILE.PUT_LINE(FND_FILE.LOG,'Error in getting currency precision');
274 END;
275
276 IF P_REPORT_NAME = 'JEBEDV08' THEN
277 fnd_file.put_line(fnd_file.log,'Calling JEBEDV08');
278 jebedv08(p_vat_rep_entity_id => p_vat_rep_entity_id
279 ,p_period => p_period
280 ,p_document_sequence_name_from => p_document_sequence_name_from
281 ,p_document_sequence_name_to => p_document_sequence_name_to
282 ,p_vendor_name_from => p_vendor_name_from
283 ,p_vendor_name_to => p_vendor_name_to
284 ,p_detail_summary => p_detail_summary
285 ,x_err_msg => l_err_msg);
286 fnd_file.put_line(fnd_file.log,'After Calling JEBEDV08');
287 ELSIF P_REPORT_NAME = 'JEITAPSR' THEN
288 fnd_file.put_line(fnd_file.log,'Calling JEITAPSR');
289 jeitapsr(p_vat_rep_entity_id => p_vat_rep_entity_id
290 ,p_period => p_period
291 ,p_vat_register_id => p_vat_register_id
292 ,x_err_msg => l_err_msg);
293 fnd_file.put_line(fnd_file.log,'After Calling JEITAPSR');
294 ELSIF P_REPORT_NAME = 'JEITAPPV' THEN
295 fnd_file.put_line(fnd_file.log,'Calling JEITAPPV');
296 jeitappv(p_vat_rep_entity_id => p_vat_rep_entity_id
297 ,p_period => p_period
298 ,p_vat_register_id => p_vat_register_id
299 ,x_err_msg => l_err_msg);
300 fnd_file.put_line(fnd_file.log,'After Calling JEITAPPV');
301 ELSIF P_REPORT_NAME = 'JEESRVAR' THEN
302 fnd_file.put_line(fnd_file.log,'Calling JEESRVAR');
303 jeesrvar(p_vat_rep_entity_id => p_vat_rep_entity_id
304 ,p_period => p_period
305 ,p_period_to => p_period_to -- Bug8267272
306 ,p_tax_type => p_tax_type
307 ,p_balancing_segment => p_balancing_segment
308 ,p_start_inv_sequence => p_start_inv_sequence
309 ,x_err_msg => l_err_msg);
310 fnd_file.put_line(fnd_file.log,'After Calling JEESRVAR');
311 ELSIF P_REPORT_NAME = 'JEESRPVP' THEN
312 fnd_file.put_line(fnd_file.log,'Calling Procedure JEESRPVP');
313 jeesrpvp(p_vat_rep_entity_id => p_vat_rep_entity_id
314 ,p_period => p_period
315 ,p_period_to => p_period_to -- Bug8267272
316 ,p_tax_type => p_tax_type
317 ,p_register_type => p_register_type
318 ,p_balancing_segment => p_balancing_segment
319 ,p_start_inv_sequence => p_start_inv_sequence
320 ,x_err_msg => l_err_msg);
321 fnd_file.put_line(fnd_file.log,'After Calling JEESRPVP');
322 ELSIF P_REPORT_NAME is null THEN
323 fnd_file.put_line(fnd_file.log,'Calling JOURNAL_AP');
324 journal_ap(p_vat_rep_entity_id => p_vat_rep_entity_id
325 ,p_period => p_period
326 ,x_err_msg => l_err_msg);
327 fnd_file.put_line(fnd_file.log,'After Calling JOURNAL_AP');
328 END IF;
329
330 INSERT INTO jg_zz_vat_trx_gt
331 ( jg_info_n1
332 ,jg_info_v1
333 ,jg_info_v2
334 ,jg_info_v3
335 ,jg_info_v4
336 ,jg_info_v5
337 ,jg_info_v6
338 ,jg_info_v7
339 ,jg_info_v8
340 ,jg_info_v9
341 ,jg_info_v10
342 ,jg_info_v11
343 ,jg_info_v12
344 ,jg_info_v13
345 ,jg_info_v14
346 ,jg_info_v15
347 ,jg_info_v16
348 ,jg_info_v17
349 ,jg_info_d1
350 ,jg_info_d2
351 ,jg_info_n2
352 ,jg_info_v30
353 ,jg_info_v18
354 ,jg_info_v19
355 ,jg_info_v20
356 )
357 VALUES
358 ( l_legal_entity_id
359 ,l_company_name
360 ,l_company_name -- l_legal_entity_name
361 ,l_tax_registration_num -- l_registration_num
362 ,l_registration_num -- l_tax_payer_id
363 ,l_contact_name
364 ,l_address_line_1
365 ,l_address_line_2
366 ,l_address_line_3
367 ,l_address_line_4
368 ,l_city
369 ,l_country
370 ,l_phone_number
371 ,l_postal_code
372 ,l_func_curr
373 ,l_reporting_status
374 ,l_tax_regime
375 ,l_vat_register_name
376 ,decode(nvl(P_REPORT_NAME,'ZZ'),'JEESRVAR',l_to_period_end_date,
377 'JEESRPVP',l_to_period_end_date,
378 l_period_end_date)
379 ,l_period_start_date
380 ,l_rec_count
381 ,'H'
382 ,l_province
383 ,l_comm_num
384 ,l_vat_reg_num
385 );
386
387 fnd_file.put_line(fnd_file.log,'P_REPORT_NAME :'||P_REPORT_NAME);
388 fnd_file.put_line(fnd_file.log,'l_to_period_end_date :'||l_to_period_end_date);
389
390
391 RETURN (TRUE);
392 EXCEPTION
393 WHEN OTHERS THEN
394 fnd_file.put_line(fnd_file.log,'Error occurred in beforeReport.' || SQLERRM || SQLCODE);
395 RETURN (FALSE);
396 END beforeReport;
397
398 --
399 -- +======================================================================+
400 -- Name: AFTER_REPORT
401 --
402 -- Description: This function is called as a after report trigger by the
403 -- data template. This trigger may to used to do any post processing
404 -- operations required by the Extract. The JOURNAL-AR Data extract
405 -- does not have any implementaions with the AFTER_REPORT trigger.
406 -- However the Procedure is defined to allow the further customizations.
407 -- Parameters: None
408 -- +======================================================================+
409 --
410 FUNCTION afterReport RETURN BOOLEAN
411 IS
412 BEGIN
413 NULL;
414 RETURN (TRUE);
415 END afterReport;
416
417 --
418 -- +======================================================================+
419 -- Name: JEBEDV08
420 --
421 -- Description: This procedure used by the Extract when the Concurrent
422 -- Program 'Belgian VAT Purchases Journal' is run.
423 --
424 -- Parameters: P_VAT_REP_ENTITY_ID => VAT Reporting Entity ID
425 -- P_PERIOD => Tax Calendar Year
426 -- P_DOCUMENT_SEQUENCE_NAME_FROM => Document Sequence Name From
427 -- P_DOCUMENT_SEQUENCE_NAME_TO => Document Sequence Name To
428 -- P_VENDOR_NAME_FROM => Vendor Name From
429 -- P_VENDOR_NAME_TO => Vendor Name To
430 -- P_DETAIL_SUMMARY => Detail Summary
431 -- +======================================================================+
432 --
433 PROCEDURE jebedv08(p_vat_rep_entity_id IN NUMBER
434 ,p_period IN VARCHAR2
435 ,p_document_sequence_name_from IN VARCHAR2
436 ,p_document_sequence_name_to IN VARCHAR2
437 ,p_vendor_name_from IN VARCHAR2
438 ,p_vendor_name_to IN VARCHAR2
439 ,p_detail_summary IN VARCHAR2
440 ,x_err_msg OUT NOCOPY VARCHAR2)
441 IS
442 CURSOR c_belgian_vat IS
443 SELECT JZVRS.tax_calendar_year PERIOD_YEAR
444 ,JZVRS.tax_calendar_period PERIOD_NAME
445 ,JZVTD.doc_seq_name SEQUENCE_NAME
446 ,JZVTD.doc_seq_value SEQUENCE_NUMBER
447 ,JZVTD.doc_seq_id DOCUMENT_SEQUENCE_ID -- Bug 9597258
448 ,JZVTD.trx_date DOCUMENT_DATE --bug7197984
449 ,JZVTD.billing_tp_number VENDOR_NUM
450 ,JZVTD.billing_tp_name VENDOR_NAME
451 ,JZVTD.trx_number INVOICE_NUM
452 ,ROUND(NVL(JZVTD.taxable_amt_funcl_curr,0),g_precision)* to_number (jzvar.taxable_rec_sign_flag || '1')
453 INV_AMT_WITHOUT_VAT
454 ,ROUND(NVL(JZVTD.tax_amt_funcl_curr,0)+NVL(JZVTD.taxable_amt_funcl_curr,0),g_precision) * to_number
455 (decode
456 ( jzvtd.tax_recoverable_flag
457 , 'Y', jzvar.tax_rec_sign_flag /* can be '+' or '-' */
458 ,jzvar.tax_non_rec_sign_flag
459 )||'1'
460 ) TOT_INV_AMT_WITH_VAT
461 ,ROUND( NVL(JZVTD.tax_amt_funcl_curr,0),g_precision) * to_number
462 (decode
463 ( jzvtd.tax_recoverable_flag
464 , 'Y', jzvar.taxable_rec_sign_flag /* can be '+' or '-' */
465 ,jzvar.taxable_non_rec_sign_flag
466 )||'1'
467 ) VAT_AMOUNT
468 ,DECODE(JZVTD.tax_recoverable_flag
469 , 'Y', NVL(JZVTD.tax_amt_funcl_curr,JZVTD.tax_amt))
470 * to_number(tax_rec_sign_flag||'1') TAX_REC_AMOUNT
471 ,DECODE (JZVTD.tax_recoverable_flag
472 , 'N' , NVL(JZVTD.tax_amt_funcl_curr,JZVTD.tax_amt)
473 )
474 * to_number(tax_non_rec_sign_flag||'1') TAX_NREC_AMOUNT
475 ,JZVTD.tax_rate_code VAT_CODE
476 ,JZVTD.trx_line_number LINE_NUM
477 ,JZVTD.account_flexfield FLEXFIELD
478 ,JZVTD.trx_control_account_flexfield TXBL_FLEXFIELD
479 ,JZVTD.account_description ACCT_DESC
480 ,FA_RX_FLEX_PKG.GET_DESCRIPTION ( 101, 'GL#',
481 (select chart_of_accounts_id from gl_ledgers where ledger_id = JZVTD.ledger_id),
482 'ALL', JZVTD.trx_control_account_flexfield ) TXBL_ACCT_DESC
483 ,NVL(JZVTD.taxable_amt_funcl_curr,JZVTD.taxable_amt) * to_number
484 (decode
485 ( jzvtd.tax_recoverable_flag
486 , 'Y', jzvar.taxable_rec_sign_flag /* can be '+' or '-' */
487 ,jzvar.taxable_non_rec_sign_flag
488 )||'1'
489 ) ACCTD_AMOUNT
490 ,NVL(JZVTD.tax_amt_funcl_curr, JZVTD.tax_amt) ACCTD_VAT_AMT
491 --,NVL(JZVTD.taxable_amt_funcl_curr, JZVTD.taxable_amt) ACCTD_INV_AMT
492 ,DECODE(JZVTD.OFFSET_FLAG,'N',DECODE(NVL(JZVTD.taxable_amt_funcl_curr,0),0
493 ,JZVTD.taxable_amt,JZVTD.taxable_amt_funcl_curr),0) ACCTD_INV_AMT
494 ,NVL(JZVTD.tax_amt_funcl_curr, JZVTD.tax_amt)
495 -- + NVL(JZVTD.taxable_amt_funcl_curr, JZVTD.taxable_amt) ACCTD_TOT_AMT
496 + DECODE(JZVTD.OFFSET_FLAG,'N',DECODE(NVL(JZVTD.taxable_amt_funcl_curr,0),0
497 ,JZVTD.taxable_amt,JZVTD.taxable_amt_funcl_curr),0) ACCTD_TOT_AMT
498 ,DECODE(JZVTD.tax_recoverable_flag, 'Y',
499 NVL(JZVTD.tax_amt_funcl_curr, JZVTD.tax_amt)) ACCTD_TAX_REC_AMT
500 ,DECODE (JZVTD.tax_recoverable_flag, 'N',
501 NVL(JZVTD.tax_amt_funcl_curr, JZVTD.tax_amt)) ACCTD_TAX_NREC_AMT
502 ,JZVTD.tax_rate_vat_trx_type_desc VAT_TRT
503 ,NVL(JZVBA.taxable_box, '99') VAT_REPORT_BOX
504 ,NVL(JZVBA.tax_box, '99') TAX_BOX
505 ,JZVTD.tax_recovery_rate TAX_RECOVERY_RATE
506 --bug12415845 - start
507 -- ,ROW_NUMBER() OVER (PARTITION BY JZVTD.tax_line_id,JZVBA.allocation_rule_id,JZVTD.cancel_flag ORDER BY JZVTD.tax_line_id,JZVBA.allocation_rule_id,JZVTD.cancel_flag DESC NULLS LAST) ROW_NUM
508 ,ROW_NUMBER() OVER (PARTITION BY JZVTD.tax_line_id,JZVBA.allocation_rule_id,JZVTD.cancel_flag,JZVTD.taxable_item_source_id,JZVTD.taxable_amt
509 ORDER BY JZVTD.tax_line_id,JZVBA.allocation_rule_id,JZVTD.cancel_flag,JZVTD.taxable_item_source_id,JZVTD.taxable_amt DESC NULLS LAST) ROW_NUM
510 -- ,ROW_NUMBER() OVER (PARTITION BY JZVTD.tax_line_id ORDER BY JZVTD.tax_line_id DESC NULLS LAST) ROW_NUM_2
511 ,ROW_NUMBER() OVER (PARTITION BY JZVTD.tax_line_id,JZVTD.taxable_item_source_id,JZVTD.cancel_flag,JZVTD.taxable_amt ORDER BY JZVTD.tax_line_id,JZVTD.taxable_item_source_id,JZVTD.cancel_flag,JZVTD.taxable_amt DESC NULLS LAST) ROW_NUM_2
512 --bug12415845 - end
513 ,LAG(DECODE(JZVTD.OFFSET_FLAG,'N',DECODE(NVL(JZVTD.taxable_amt_funcl_curr,0),0
514 ,JZVTD.taxable_amt,JZVTD.taxable_amt_funcl_curr),0),1,0) OVER (PARTITION BY JZVTD.tax_line_id,JZVBA.allocation_rule_id,JZVTD.cancel_flag,JZVTD.taxable_item_source_id,JZVTD.taxable_amt
515 ORDER BY JZVTD.tax_line_id,JZVBA.allocation_rule_id,JZVTD.cancel_flag,JZVTD.taxable_item_source_id,JZVTD.taxable_amt DESC NULLS LAST)
516 PREV_AMT
517 ,DECODE(JZVTD.OFFSET_FLAG,'N',DECODE(NVL(JZVTD.taxable_amt_funcl_curr,0),0
518 ,JZVTD.taxable_amt,JZVTD.taxable_amt_funcl_curr),0) TAXABLE_AMT_FOR_LAG
519 FROM jg_zz_vat_trx_details JZVTD
520 , jg_zz_vat_rep_status JZVRS
521 , jg_zz_vat_box_allocs JZVBA
522 , jg_zz_vat_alloc_rules jzvar
523 WHERE JZVBA.PERIOD_TYPE = 'PERIODIC'
524 AND JZVRS.source = 'AP'
525 /* AND JZVTD.trx_line_class IN ('STANDARD','CREDIT','DEBIT','EXPENSE REPORT','PREPAYMENT' ) Bug#5235824 */
526 AND JZVTD.trx_line_class IN ('STANDARD INVOICES','AP_CREDIT_MEMO','AP_DEBIT_MEMO','EXPENSE REPORTS','PREPAYMENT INVOICES','PREPAY_APPLICATION')
527 AND ((JZVTD.billing_tp_name BETWEEN NVL(P_VENDOR_NAME_FROM, JZVTD.billing_tp_name)
528 AND NVL(P_VENDOR_NAME_TO, JZVTD.billing_tp_name)) OR P_VENDOR_NAME_FROM is null)
529 AND ((JZVTD.doc_seq_name BETWEEN NVL(P_DOCUMENT_SEQUENCE_NAME_FROM,JZVTD.doc_seq_name)
530 AND NVL(P_DOCUMENT_SEQUENCE_NAME_TO,JZVTD.doc_seq_name)) OR P_DOCUMENT_SEQUENCE_NAME_FROM IS NULL)
531 AND JZVRS.tax_calendar_period = P_PERIOD
532 AND JZVRS.vat_reporting_entity_id = P_VAT_REP_ENTITY_ID
533 AND JZVRS.reporting_status_id = JZVTD.reporting_status_id
534 AND JZVTD.vat_transaction_id = JZVBA.vat_transaction_id
535 AND jzvar.allocation_rule_id = jzvba.allocation_rule_id
536 AND jzvtd.tax_recovery_rate <>0;
537
538 BEGIN
539 FOR c_belgian_rec IN c_belgian_vat
540 LOOP
541 INSERT INTO jg_zz_vat_trx_gt(
542 jg_info_n1
543 ,jg_info_v1
544 ,jg_info_v2
545 ,jg_info_v11 /* jg_info_n2. Bug#5235824 */
546 ,jg_info_n3
547 ,jg_info_d1
548 ,jg_info_v9
549 ,jg_info_v3
550 ,jg_info_v10
551 ,jg_info_n6
552 ,jg_info_n7
553 ,jg_info_n8
554 ,jg_info_n9
555 ,jg_info_n10
556 ,jg_info_v4
557 ,jg_info_n11
558 ,jg_info_v5
559 ,jg_info_v31
560 ,jg_info_v6
561 ,jg_info_v32
562 ,jg_info_n12
563 ,jg_info_v7
564 ,jg_info_v8
565 ,jg_info_v12
566 ,jg_info_v30
567 ,jg_info_n13
568 ,jg_info_n14
569 ,jg_info_n15
570 ,jg_info_n16
571 ,jg_info_n17
572 ,jg_info_n28
573 ,jg_info_n29
574 ,jg_info_n30 --bug8601603
575 ,jg_info_n27
576 ,jg_info_n26
577 )
578 VALUES (
579 c_belgian_rec.period_year
580 ,c_belgian_rec.period_name
581 ,c_belgian_rec.sequence_name
582 ,c_belgian_rec.sequence_number
583 ,c_belgian_rec.document_sequence_id
584 ,c_belgian_rec.document_date
585 ,c_belgian_rec.vendor_num
586 ,c_belgian_rec.vendor_name
587 ,c_belgian_rec.invoice_num
588 ,c_belgian_rec.inv_amt_without_vat
589 ,c_belgian_rec.tot_inv_amt_with_vat
590 ,c_belgian_rec.vat_amount
591 ,c_belgian_rec.tax_rec_amount
592 ,c_belgian_rec.tax_nrec_amount
593 ,c_belgian_rec.vat_code
594 ,c_belgian_rec.line_num
595 ,c_belgian_rec.flexfield
596 ,c_belgian_rec.txbl_flexfield
597 ,c_belgian_rec.acct_desc
598 ,c_belgian_rec.txbl_acct_desc
599 ,c_belgian_rec.acctd_amount
600 ,c_belgian_rec.vat_trt
601 ,c_belgian_rec.vat_report_box
602 ,c_belgian_rec.tax_box
603 ,'JEBEDV08'
604 ,c_belgian_rec.acctd_vat_amt
605 ,c_belgian_rec.acctd_inv_amt
606 ,c_belgian_rec.acctd_tot_amt
607 ,c_belgian_rec.acctd_tax_rec_amt
608 ,c_belgian_rec.acctd_tax_nrec_amt
609 ,c_belgian_rec.tax_recovery_rate
610 ,c_belgian_rec.row_num --bug8601603
611 ,c_belgian_rec.row_num_2 --bug8601603
612 ,c_belgian_rec.prev_amt
613 ,c_belgian_rec.taxable_amt_for_lag
614 );
615 END LOOP;
616 --update table bug8601603
617 --update acctd_inv_amt of the second record for same trx_line_id having tax_recovery_rate <>= 0
618 update jg_zz_vat_trx_gt
619 set
620 jg_info_n14 = 0 --acctd_inv_amt
621 where
622 mod(jg_info_n29,2) = 0
623 and jg_info_n28 <>100;
624
625
626 --update acctd_inv_amt for the record having tax_recovery_rate <>= 0
627 update jg_zz_vat_trx_gt
628 set
629 jg_info_n14 = 0 --acctd_inv_amt
630 ,jg_info_n16 = 0 --acctd_tax_rec_amt
631 ,jg_info_n17 = 0 --acctd_tax_nrec_amt
632 where
633 jg_info_n29 <> jg_info_n30
634 ;
635
636 --calculate acctd_vat_amt to displat total vat amount for invoice
637 update jg_zz_vat_trx_gt
638 set
639 jg_info_n13 = nvl(jg_info_n16,0) + nvl(jg_info_n17,0) --acctd_vat_amt
640 ;
641
642 --calculate acctd_tot_amt to displat total amount for invoice
643 update jg_zz_vat_trx_gt
644 set
645 jg_info_n15 = nvl(jg_info_n13,0) + nvl(jg_info_n14,0) --acctd_tot_amt
646 ;
647
648 --update used in case of multiple adjusted invoices
649 --update the 2nd record having the same amount for same combination used in partition logic
650 --
651 fnd_file.put_line(fnd_file.log,'Updating jg_zz_vat_trx_gt using lag logic' );
652 update jg_zz_vat_trx_gt
653 set
654 jg_info_n29 = 1
655 where
656 ((mod(jg_info_n29,2) = 0
657 and jg_info_n28 = 100)
658 or (mod(jg_info_n29,2) = 1
659 and jg_info_n28 <>100))
660 and jg_info_n27 = jg_info_n26 ;
661
662 SELECT COUNT(*) INTO l_rec_count
663 FROM jg_zz_vat_trx_gt
664 WHERE jg_info_v30='JEBEDV08';
665
666 IF p_debug_flag = 'Y' THEN
667 fnd_file.put_line(fnd_file.log,'Number of records inserted into jg_zz_vat_trx_gt table: ' || l_rec_count );
668 END IF ;
669
670 EXCEPTION
671 WHEN OTHERS THEN
672 fnd_file.put_line(fnd_file.log,'Error occurred in jebedv08 procedure.' || SQLERRM || SQLCODE);
673 END jebedv08;
674
675 --
676 -- +======================================================================+
677 -- Name: JEITAPSR
678 --
679 -- Description: This procedure used by the Extract when the Concurrent
680 -- Program 'Italian Payables Sales VAT Register(Self Invoices, EEC VAT)' is run.
681 --
682 -- Parameters: P_VAT_REP_ENTITY_ID => VAT Reporting Entity ID
683 -- P_PERIOD => Tax Calendar Year
684 -- P_VAT_REGISTER_ID => VAT Register ID
685 -- +======================================================================+
686 --
687 PROCEDURE jeitapsr(p_vat_rep_entity_id IN NUMBER
688 ,p_period IN VARCHAR2
689 ,p_vat_register_id IN VARCHAR2
690 ,x_err_msg OUT NOCOPY VARCHAR2)
691 IS
692 CURSOR c_italian_payables_sales_vat IS
693 SELECT JZVTD.doc_seq_name SEQ_NAME
694 ,JZVTD.billing_tp_name VNAME
695 ,NVL(JZVTD.billing_tp_site_tax_reg_num,JZVTD.billing_tp_tax_reg_num) VAT_NUM
696 ,JZVTD.tax_rate_code_name VAT_NAME
697 ,JZVTD.reporting_code TAX_TYPE
698 ,JZVTD.tax_rate VAT_RATE
699 ,JZVTD.tax_rate_code_description VAT_DESC
700 ,JZVTD.doc_seq_value SEQ_VAL
701 ,JZVTD.trx_id INVOICE_ID
702 ,JZVTD.trx_number INVOICE_NUM
703 ,JZVTD.trx_date INVOICE_DATE
704 ,JZVTD.trx_currency_code CURRENCY_CODE
705 ,JZVTD.tax_recovery_rate RECOVERY_RATE
706 ,JZVTD.tax_recoverable_flag TAX_RECOVERABLE_FLAG
707 ,JZVTD.gl_date ACCOUNTING_DATE
708 ,NVL(JZVTD.taxable_amt,0) TAXABLE_AMOUNT
709 ,NVL(JZVTD.taxable_amt_funcl_curr,0) TAXABLE_BASE_AMOUNT
710 ,NVL(DECODE(JZVTD.offset_flag,'Y', -1*JZVTD.tax_amt,JZVTD.tax_amt),0) TAX_AMOUNT
711 ,NVL(DECODE(JZVTD.offset_flag,'Y', -1*JZVTD.tax_amt_funcl_curr,JZVTD.tax_amt_funcl_curr),0) TAX_BASE_AMOUNT
712 ,NVL(fnd_number.canonical_to_number(JZVTD.assessable_value),0) TAXABLE_SEL_INV
713 ,JZVTD.offset_tax_rate_code OFFSET_TAX_CODE_ID
714 ,DECODE(JZVTD.tax_recoverable_flag ,'Y',JZVTD.taxable_amt*(JZVTD.tax_recovery_rate/100)) TAXABLE_REC_AMT
715 ,DECODE(JZVTD.tax_recoverable_flag ,'N',JZVTD.taxable_amt*(JZVTD.tax_recovery_rate/100)) TAXABLE_NREC_AMT
716 ,DECODE(JZVTD.tax_recoverable_flag ,'Y',DECODE(JZVTD.offset_flag,'Y', -1*JZVTD.tax_amt,JZVTD.tax_amt)) TAX_REC_AMT
717 ,DECODE(JZVTD.tax_recoverable_flag ,'N',DECODE(JZVTD.offset_flag,'Y', -1*JZVTD.tax_amt,JZVTD.tax_amt)) TAX_NREC_AMT
718 ,DECODE(JZVTD.tax_recoverable_flag,'Y',JZVTD.taxable_amt_funcl_curr*(JZVTD.tax_recovery_rate/100)) TAXABLE_REC_BASE_AMT
719 ,DECODE(JZVTD.tax_recoverable_flag,'N',JZVTD.taxable_amt_funcl_curr*(JZVTD.tax_recovery_rate/100)) TAXABLE_NREC_BASE_AMT
720 ,DECODE(JZVTD.tax_recoverable_flag,'Y',DECODE(JZVTD.offset_flag,'Y', -1*tax_amt_funcl_curr,tax_amt_funcl_curr)) TAX_REC_BASE_AMT
721 ,DECODE(JZVTD.tax_recoverable_flag,'N',DECODE(JZVTD.offset_flag,'Y', -1*tax_amt_funcl_curr,tax_amt_funcl_curr)) TAX_NREC_BASE_AMT
722 ,JZVTD.functional_currency_code FUNCTIONAL_CURRENCY_CODE
723 -- Bug 6238170 Start
724 --,JZVTD.tax_type_code TAX_TYPE_CODE
725 ,JZVTD.reporting_code TAX_TYPE_CODE
726 -- Bug 6238170 End
727 ,NVL(JZVTD.offset_tax_rate_code,'N') OFFSET_TAX_RATE_CODE
728 FROM jg_zz_vat_trx_details JZVTD
729 ,jg_zz_vat_rep_status JZVRS
730 ,jg_zz_vat_registers_b JZVRB
731 ,jg_zz_vat_doc_sequences JZVDS /* Bug#5235824 */
732 WHERE JZVRS.vat_reporting_entity_id = P_VAT_REP_ENTITY_ID
733 AND JZVRS.tax_calendar_period = P_PERIOD
734 AND JZVRB.vat_register_id = P_VAT_REGISTER_ID
735 -- Bug 6238170 Start
736 -- AND JZVTD.tax_type_code NOT IN ('OFFSET')
737 -- AND JZVTD.reporting_code NOT IN ('OFFSET') --bug11064227
738 AND JZVTD.offset_tax_rate_code is NULL --bug11064227
739 -- Bug 6238170 End
740 AND JZVRS.source = 'AP'
741 /* AND JZVTD.tax_invoice_date BETWEEN JZVRS.period_start_date and JZVRS.period_end_date */
742 AND JZVTD.reporting_status_id = JZVRS.reporting_status_id
743 AND JZVRS.mapping_vat_rep_entity_id = JZVRB.vat_reporting_entity_id
744 AND JZVDS.vat_register_id = JZVRB.vat_register_id
745 AND JZVDS.doc_sequence_id = JZVTD.doc_seq_id;
746 -- AND JZVTD.tax_amt <> 0;
747 -- BUG 7451529 : Invoices with zero tax amounts should also get picked up. For eg:
748 -- the ones which use 'EXEMPT' tax-type.
749
750
751 lv_start_seq jg_zz_vat_final_reports.start_sequence_num%type;
752 lv_reporting_status varchar2(15);
753
754 BEGIN
755
756 SELECT jg_info_n1, jg_info_v1
757 INTO lv_start_seq, lv_reporting_status
758 FROM jg_zz_vat_trx_gt
759 WHERE jg_info_v30 = 'SEQ';
760
761 FOR c_italian_pay_sales_rec IN c_italian_payables_sales_vat
762 LOOP
763 INSERT into jg_zz_vat_trx_gt(jg_info_v1
764 ,jg_info_v2
765 ,jg_info_v3
766 ,jg_info_v4
767 ,jg_info_v5
768 ,jg_info_n1
769 ,jg_info_v6
770 ,jg_info_n2
771 ,jg_info_v7
772 ,jg_info_d1
773 ,jg_info_v8
774 ,jg_info_v9
775 ,jg_info_d2
776 ,jg_info_n3
777 ,jg_info_n4
778 ,jg_info_n5
779 ,jg_info_n6
780 --,jg_info_n7,
781 ,jg_info_n8
782 ,jg_info_n9
783 ,jg_info_v26 /* jg_info_n10. Bug#5235824 */
784 ,jg_info_n20
785 ,jg_info_n21
786 ,jg_info_n22
787 ,jg_info_n23
788 ,jg_info_n13
789 ,jg_info_n14
790 ,jg_info_n15
791 ,jg_info_n16
792 ,jg_info_v20
793 ,jg_info_v21
794 ,jg_info_v30
795 ,jg_info_v39
796 )
797 VALUES (
798 c_italian_pay_sales_rec.seq_name
799 ,SUBSTR(c_italian_pay_sales_rec.vname,1,150)
800 ,c_italian_pay_sales_rec.vat_num
801 ,c_italian_pay_sales_rec.vat_name
802 ,c_italian_pay_sales_rec.tax_type
803 ,c_italian_pay_sales_rec.vat_rate
804 ,c_italian_pay_sales_rec.vat_desc
805 ,c_italian_pay_sales_rec.seq_val
806 ,c_italian_pay_sales_rec.invoice_num
807 ,c_italian_pay_sales_rec.invoice_date
808 ,c_italian_pay_sales_rec.currency_code
809 ,c_italian_pay_sales_rec.tax_recoverable_flag
810 ,c_italian_pay_sales_rec.accounting_date
811 ,c_italian_pay_sales_rec.taxable_amount
812 ,c_italian_pay_sales_rec.taxable_base_amount
813 ,c_italian_pay_sales_rec.tax_amount
814 ,c_italian_pay_sales_rec.tax_base_amount
815 -- c_italian_pay_sales_rec.taxable_sel_inv, Check Sumanth
816 ,c_italian_pay_sales_rec.invoice_id
817 ,c_italian_pay_sales_rec.recovery_rate
818 ,c_italian_pay_sales_rec.offset_tax_code_id
819 ,c_italian_pay_sales_rec.taxable_rec_amt
820 ,c_italian_pay_sales_rec.taxable_nrec_amt
821 ,c_italian_pay_sales_rec.tax_rec_amt
822 ,c_italian_pay_sales_rec.tax_nrec_amt
823 ,c_italian_pay_sales_rec.taxable_rec_base_amt
824 ,c_italian_pay_sales_rec.taxable_nrec_base_amt
825 ,c_italian_pay_sales_rec.tax_rec_base_amt
826 ,c_italian_pay_sales_rec.tax_nrec_base_amt
827 ,c_italian_pay_sales_rec.functional_currency_code
828 ,c_italian_pay_sales_rec.tax_type_code
829 ,'JEITAPSR'
830 ,c_italian_pay_sales_rec.offset_tax_rate_code);
831 END LOOP;
832
833 SELECT COUNT(*)
834 INTO l_rec_count
835 FROM jg_zz_vat_trx_gt
836 WHERE jg_info_v30='JEITAPSR';
837
838 IF p_debug_flag = 'Y' THEN
839 fnd_file.put_line(fnd_file.log,'Number of records inserted into jg_zz_vat_trx_gt table: ' || l_rec_count );
840 END IF ;
841
842 -- Update teh jg_zz_vat_final_reports table for print sequence numbers if lv_reporting_status = 'FINAL'
843 IF lv_reporting_status = 'FINAL' THEN
844
845 SELECT count(*)
846 INTO l_rec_count
847 FROM (SELECT 1
848 FROM jg_zz_vat_trx_gt
849 WHERE jg_info_v30 = 'JEITAPSR'
850 GROUP BY jg_info_v1
851 ,jg_info_d2
852 ,jg_info_n2
853 ,jg_info_v7
854 ,jg_info_d1
855 ,jg_info_v2
856 ,jg_info_v3
857 ,jg_info_v8
858 ,jg_info_v20
859 ORDER BY jg_info_v1
860 ,jg_info_d2
861 ,jg_info_n2
862 ,jg_info_v7
863 ,jg_info_d1
864 ,jg_info_v2
865 ,jg_info_v8);
866
867 -- Update the entry in JG_ZZ_VAT_FINAL_REPORTS table
868 UPDATE jg_zz_vat_final_reports
869 SET start_sequence_num = lv_start_seq + l_rec_count,
870 last_start_sequence_num = lv_start_seq
871 WHERE report_name = p_report_name
872 AND vat_register_id = p_vat_register_id
873 AND reporting_status_id = (SELECT reporting_status_id
874 FROM jg_zz_vat_rep_status
875 WHERE vat_reporting_entity_id = p_vat_rep_entity_id
876 AND source = 'AP'
877 AND tax_calendar_period = p_period);
878
879 END IF;
880
881 EXCEPTION
882 WHEN OTHERS THEN
883 fnd_file.put_line(fnd_file.log,'Error in procedure jeitapsr.' || SQLERRM || SQLCODE);
884 END jeitapsr;
885
886 --
887 -- +======================================================================+
888 -- Name: JEITAPPV
889 --
890 -- Description: This procedure used by the Extract when the Concurrent
891 -- Program 'Italian Purchase VAT Register' is run.
892 --
893 -- Parameters: P_VAT_REP_ENTITY_ID => VAT Reporting Entity ID
894 -- P_PERIOD => Tax Calendar Year
895 -- P_VAT_REGISTER_ID => VAT Register ID
896 -- +======================================================================+
897 --
898 PROCEDURE jeitappv(p_vat_rep_entity_id IN NUMBER
899 ,p_period IN VARCHAR2
900 ,p_vat_register_id IN VARCHAR2
901 ,x_err_msg OUT NOCOPY VARCHAR2)
902 IS
903
904 CURSOR c_italian_purchase_vat(cp_tax_tag varchar2) IS
905 SELECT JZVTD.doc_seq_name SEQ_NAME
906 ,NVL(JZVTD.merchant_party_name,JZVTD.billing_tp_name) VNAME
907 ,NVL(JZVTD.merchant_party_tax_reg_number,NVL(JZVTD.billing_tp_site_tax_reg_num,JZVTD.billing_tp_tax_reg_num)) VAT_NUM
908 ,JZVTD.tax_rate_code_name VAT_NAME
909 ,JZVTD.reporting_code TAX_TYPE
910 ,JZVTD.tax_rate VAT_RATE
911 ,JZVTD.tax_rate_code_description VAT_DESC
912 ,JZVTD.doc_seq_value SEQ_VAL
913 ,JZVTD.trx_id INVOICE_ID
914 ,NVL(JZVTD.merchant_party_document_number,JZVTD.trx_number) INVOICE_NUM
915 ,NVL(JZVTD.start_expense_date,JZVTD.trx_date) INVOICE_DATE
916 ,JZVTD.trx_currency_code CURRENCY_CODE
917 ,JZVTD.gl_date ACCOUNTING_DATE
918 --,NVL(DECODE(tax_recoverable_flag,'Y',taxable_amt_funcl_curr*(JZVTD.tax_recovery_rate/100)),0) TAXABLE_REC_BASE_AMOUNT
919 ,decode(ZX.historical_flag,'Y',decode(ZX.global_attribute_category,'JE.IT.APXINWKB.DISTRIBUTIONS',
920 decode(JZVTD.trx_currency_code,cur.currency_code,
921 NVL(DECODE(JZVTD.tax_recoverable_flag,'Y',NVL(fnd_number.canonical_to_number(zx.global_attribute1),0)*(JZVTD.tax_recovery_rate/100)),0),
922 NVL(DECODE(JZVTD.tax_recoverable_flag,'Y',(NVL(fnd_number.canonical_to_number(zx.global_attribute1),0)*nvl(JZVTD.currency_conversion_rate,1)/
923 nvl(cur.minimum_accountable_unit,power(10,(-1*cur.precision))))*
924 nvl(cur.minimum_accountable_unit,power(10,(-1*cur.precision))))*(JZVTD.tax_recovery_rate/100),0)),
925 NVL(DECODE(JZVTD.tax_recoverable_flag,'Y',JZVTD.taxable_amt_funcl_curr*(JZVTD.tax_recovery_rate/100)),0)),
926 NVL(DECODE(JZVTD.tax_recoverable_flag,'Y',JZVTD.taxable_amt_funcl_curr*(JZVTD.tax_recovery_rate/100)),0)) TAXABLE_REC_BASE_AMOUNT
927 -- ,NVL(DECODE(tax_recoverable_flag,'Y',taxable_amt*(JZVTD.tax_recovery_rate/100)) ,0) TAXABLE_REC_AMOUNT
928 ,decode(ZX.historical_flag,'Y',decode(ZX.global_attribute_category,'JE.IT.APXINWKB.DISTRIBUTIONS',
929 NVL(DECODE(JZVTD.tax_recoverable_flag,'Y',NVL(fnd_number.canonical_to_number(zx.global_attribute1),0)*(JZVTD.tax_recovery_rate/100)),0),
930 NVL(DECODE(JZVTD.tax_recoverable_flag,'Y',JZVTD.taxable_amt*(JZVTD.tax_recovery_rate/100)),0)),
931 NVL(DECODE(JZVTD.tax_recoverable_flag,'Y',JZVTD.taxable_amt*(JZVTD.tax_recovery_rate/100)),0)) TAXABLE_REC_AMOUNT
932 ,NVL(DECODE(JZVTD.tax_recoverable_flag,'Y',JZVTD.tax_amt_funcl_curr),0) TAX_REC_BASE_AMOUNT
933 ,NVL(DECODE(JZVTD.tax_recoverable_flag,'Y',JZVTD.tax_amt),0) TAX_REC_AMOUNT
934 -- ,NVL(DECODE(tax_recoverable_flag,'N',taxable_amt_funcl_curr*(JZVTD.tax_recovery_rate/100)),0) TAXABLE_NREC_BASE_AMOUNT
935 ,decode(ZX.historical_flag,'Y',decode(ZX.global_attribute_category,'JE.IT.APXINWKB.DISTRIBUTIONS',
936 decode(JZVTD.trx_currency_code,cur.currency_code,
937 NVL(DECODE(JZVTD.tax_recoverable_flag,'N',NVL(fnd_number.canonical_to_number(zx.global_attribute1),0)*(JZVTD.tax_recovery_rate/100)),0),
938 NVL(DECODE(JZVTD.tax_recoverable_flag,'N',(NVL(fnd_number.canonical_to_number(zx.global_attribute1),0)*nvl(JZVTD.currency_conversion_rate,1)/
939 nvl(cur.minimum_accountable_unit,power(10,(-1*cur.precision))))*
940 nvl(cur.minimum_accountable_unit,power(10,(-1*cur.precision))))*(JZVTD.tax_recovery_rate/100),0)),
941 NVL(DECODE(JZVTD.tax_recoverable_flag,'N',JZVTD.taxable_amt_funcl_curr*(JZVTD.tax_recovery_rate/100)),0)),
942 NVL(DECODE(JZVTD.tax_recoverable_flag,'N',JZVTD.taxable_amt_funcl_curr*(JZVTD.tax_recovery_rate/100)),0)) TAXABLE_NREC_BASE_AMOUNT
943 --,NVL(DECODE(tax_recoverable_flag,'N',taxable_amt*(JZVTD.tax_recovery_rate/100)),0) TAXABLE_NREC_AMOUNT
944 ,decode(ZX.historical_flag,'Y',decode(ZX.global_attribute_category,'JE.IT.APXINWKB.DISTRIBUTIONS',
945 NVL(DECODE(JZVTD.tax_recoverable_flag,'N',NVL(fnd_number.canonical_to_number(zx.global_attribute1),0)*(JZVTD.tax_recovery_rate/100)),0),
946 NVL(DECODE(JZVTD.tax_recoverable_flag,'N',JZVTD.taxable_amt*(JZVTD.tax_recovery_rate/100)),0)),
947 NVL(DECODE(JZVTD.tax_recoverable_flag,'N',JZVTD.taxable_amt*(JZVTD.tax_recovery_rate/100)),0)) TAXABLE_NREC_AMOUNT
948 ,NVL(DECODE(JZVTD.tax_recoverable_flag,'N',JZVTD.tax_amt_funcl_curr),0) TAX_NREC_BASE_AMOUNT
949 ,NVL(DECODE(JZVTD.tax_recoverable_flag,'N',JZVTD.tax_amt) ,0) TAX_NREC_AMOUNT
950 ,NVL(fnd_number.canonical_to_number(JZVTD.assessable_value),0) SEL_INV_AMOUNT
951 ,JZVTD.tax_recovery_rate RECOVERY_RATE
952 ,DECODE(JZVTD.offset_tax_rate_code, NULL,NVL(JZVTD.tax_amt,0) + NVL(JZVTD.taxable_amt,0),NVL(DECODE(JZVTD.TAX_RECOVERABLE_FLAG ,'Y',
953 JZVTD.TAXABLE_AMT),0) + NVL(DECODE(JZVTD.TAX_RECOVERABLE_FLAG ,'N',JZVTD.TAXABLE_AMT),0)) FOREIGN_AMT
954 ,JZVTD.tax_recoverable_flag TAX_RECOVERABLE_FLAG
955 -- ,DECODE(flv.tag, cp_tax_tag , '^', NULL) EXEMPT_FLAG
956 ,DECODE(JZVTD.reporting_code, cp_tax_tag , '^', NULL) EXEMPT_FLAG
957 ,DECODE(JZVTD.accounting_date, NULL, '*', NULL) INV_DIST_POSTED_FLAG
958 ,JZVTD.functional_currency_code FUNCTIONAL_CURRENCY_CODE
959 ,JZVTD.reporting_code TAX_TYPE_CODE
960 ,NVL(JZVTD.offset_tax_rate_code,'N') OFFSET_TAX_RATE_CODE
961 FROM jg_zz_vat_trx_details JZVTD
962 ,jg_zz_vat_rep_status JZVRS
963 ,jg_zz_vat_registers_b JZVRB
964 ,jg_zz_vat_doc_sequences JZVDS /* Bug#5235824 */
965 -- ,fnd_lookup_values_vl flv /* Bug#5235824 */
966 ,zx_lines ZX
967 ,fnd_currencies CUR
968 ,gl_sets_of_books SOB
969
970
971 WHERE JZVRS.vat_reporting_entity_id = P_VAT_REP_ENTITY_ID
972 AND JZVRS.tax_calendar_period = P_PERIOD
973 AND JZVRB.vat_register_id = P_VAT_REGISTER_ID
974 AND JZVTD.TAX_LINE_ID = ZX.TAX_LINE_ID
975 AND JZVTD.LEDGER_ID = SOB.SET_OF_BOOKS_ID
976 AND CUR.CURRENCY_CODE = SOB.CURRENCY_CODE
977 --AND flv.lookup_type = 'TAX TYPE'
978 -- AND JZVTD.TAX_TYPE_CODE NOT IN ('OFFSET')
979 AND JZVTD.REPORTING_CODE NOT IN ('OFFSET')
980 AND JZVRS.source = 'AP'
981 /*AND JZVTD.tax_invoice_date BETWEEN JZVRS.period_start_date and JZVRS.period_end_date */
982 -- AND JZVTD.tax_type_code = flv.lookup_code
983 AND JZVTD.reporting_status_id = JZVRS.reporting_status_id
984 AND JZVRS.mapping_vat_rep_entity_id = JZVRB.vat_reporting_entity_id
985 AND JZVRB.vat_register_id = JZVDS.vat_register_id
986 AND JZVDS.doc_sequence_id = JZVTD.doc_seq_id;
987 -- AND JZVTD.tax_amt <> 0;
988 -- BUG 7451529 : Invoices with zero tax amounts should also get picked up. For eg:
989 -- the ones which use 'EXEMPT' tax-type.
990
991
992 -- lv_tax_tag fnd_lookup_values_vl.tag%type ;
993 lv_tax_code VARCHAR2(60);
994 lv_start_seq jg_zz_vat_final_reports.start_sequence_num%type;
995 lv_reporting_status varchar2(15);
996
997 BEGIN
998 -- lv_tax_tag := nvl(FND_PROFILE.VALUE('JEIT_EXEMPT_TAX_TAG'),'JEIT_NO_EXEMPT_TAX_TAG'); /* Bug#5235824 */
999 lv_tax_code := nvl(FND_PROFILE.VALUE('JEIT_EXEMPT_TAX_TAG'),'JEIT_NO_EXEMPT_TAX_TAG'); /* Bug#5235824 */
1000
1001 SELECT jg_info_n1, jg_info_v1
1002 INTO lv_start_seq, lv_reporting_status
1003 FROM jg_zz_vat_trx_gt
1004 WHERE jg_info_v30 = 'SEQ';
1005
1006 FOR c_italian_purchase_vat_rec IN c_italian_purchase_vat(lv_tax_code)
1007 LOOP
1008
1009 INSERT INTO jg_zz_vat_trx_gt (jg_info_v1
1010 ,jg_info_v2
1011 ,jg_info_v3
1012 ,jg_info_v4
1013 ,jg_info_v5
1014 ,jg_info_n1
1015 ,jg_info_v6
1016 ,jg_info_n2
1017 ,jg_info_n3
1018 ,jg_info_v7
1019 ,jg_info_d1
1020 ,jg_info_v8
1021 ,jg_info_d2
1022 ,jg_info_n4
1023 ,jg_info_n5
1024 ,jg_info_n6
1025 ,jg_info_n7
1026 ,jg_info_n8
1027 ,jg_info_n9
1028 ,jg_info_n10
1029 ,jg_info_n11
1030 -- ,jg_info_n12
1031 ,jg_info_n13
1032 ,jg_info_n14
1033 ,jg_info_v39
1034 ,jg_info_v9
1035 ,jg_info_v10
1036 ,jg_info_v11 /* Bug#5235824 */
1037 ,jg_info_n20
1038 ,jg_info_n21
1039 ,jg_info_n22
1040 ,jg_info_n23
1041 ,jg_info_v20
1042 ,jg_info_v30
1043 ,jg_info_v31
1044 )
1045 VALUES (c_italian_purchase_vat_rec.seq_name
1046 ,SUBSTR(c_italian_purchase_vat_rec.vname,1,150)
1047 ,c_italian_purchase_vat_rec.vat_num
1048 ,c_italian_purchase_vat_rec.vat_name
1049 ,c_italian_purchase_vat_rec.tax_type
1050 ,c_italian_purchase_vat_rec.vat_rate
1051 ,c_italian_purchase_vat_rec.vat_desc
1052 ,c_italian_purchase_vat_rec.seq_val
1053 ,c_italian_purchase_vat_rec.invoice_id
1054 ,c_italian_purchase_vat_rec.invoice_num
1055 ,c_italian_purchase_vat_rec.invoice_date
1056 ,c_italian_purchase_vat_rec.currency_code
1057 ,c_italian_purchase_vat_rec.accounting_date
1058 ,c_italian_purchase_vat_rec.taxable_rec_amount
1059 ,c_italian_purchase_vat_rec.taxable_rec_base_amount
1060 ,c_italian_purchase_vat_rec.tax_rec_amount
1061 ,c_italian_purchase_vat_rec.tax_rec_base_amount
1062 ,c_italian_purchase_vat_rec.taxable_nrec_amount
1063 ,c_italian_purchase_vat_rec.taxable_nrec_base_amount
1064 ,c_italian_purchase_vat_rec.tax_nrec_amount
1065 ,c_italian_purchase_vat_rec.tax_nrec_base_amount
1066 -- ,c_italian_purchase_vat_rec.sel_inv_amount
1067 ,c_italian_purchase_vat_rec.recovery_rate
1068 ,c_italian_purchase_vat_rec.foreign_amt
1069 ,c_italian_purchase_vat_rec.offset_tax_rate_code
1070 ,c_italian_purchase_vat_rec.tax_recoverable_flag
1071 ,c_italian_purchase_vat_rec.inv_dist_posted_flag
1072 ,c_italian_purchase_vat_rec.exempt_flag
1073 ,c_italian_purchase_vat_rec.taxable_rec_amount
1074 ,c_italian_purchase_vat_rec.taxable_nrec_amount
1075 ,c_italian_purchase_vat_rec.tax_rec_amount
1076 ,c_italian_purchase_vat_rec.tax_nrec_amount
1077 ,c_italian_purchase_vat_rec.functional_currency_code
1078 ,'JEITAPPV'
1079 ,c_italian_purchase_vat_rec.tax_type_code);
1080 END LOOP;
1081
1082 -- Count the number of records inserted into jg_zz_vat_trx_gt table
1083 SELECT COUNT(*)
1084 INTO l_rec_count
1085 FROM jg_zz_vat_trx_gt
1086 WHERE jg_info_v30='JEITAPPV';
1087
1088 IF p_debug_flag = 'Y' THEN
1089 fnd_file.put_line(fnd_file.log,'Number of records inserted into jg_zz_vat_trx_gt table: ' || l_rec_count );
1090 END IF ;
1091
1092 -- Update teh jg_zz_vat_final_reports table for print sequence numbers if lv_reporting_status = 'FINAL'
1093 IF lv_reporting_status = 'FINAL' THEN
1094
1095 SELECT count(*)
1096 INTO l_rec_count
1097 FROM (SELECT 1
1098 FROM jg_zz_vat_trx_gt
1099 WHERE jg_info_v30 = 'JEITAPPV'
1100 GROUP BY jg_info_v1
1101 ,jg_info_d2
1102 ,jg_info_n2
1103 ,jg_info_v7
1104 ,jg_info_d1
1105 ,jg_info_v2
1106 ,jg_info_v3
1107 ,jg_info_v8
1108 ,jg_info_v20
1109 ,jg_info_v11
1110 ,jg_info_v10
1111 ORDER BY jg_info_v1
1112 ,jg_info_d2
1113 ,jg_info_n2
1114 ,jg_info_v7
1115 ,jg_info_d1
1116 ,jg_info_v2
1117 ,jg_info_v3
1118 ,jg_info_v8
1119 ,jg_info_v20 );
1120
1121 -- Update the entry in JG_ZZ_VAT_FINAL_REPORTS table
1122 UPDATE jg_zz_vat_final_reports
1123 SET start_sequence_num = lv_start_seq + l_rec_count,
1124 last_start_sequence_num = lv_start_seq
1125 WHERE report_name = p_report_name
1126 AND vat_register_id = p_vat_register_id
1127 AND reporting_status_id = (SELECT reporting_status_id
1128 FROM jg_zz_vat_rep_status
1129 WHERE vat_reporting_entity_id = p_vat_rep_entity_id
1130 AND source = 'AP'
1131 AND tax_calendar_period = p_period);
1132
1133 END IF;
1134
1135 EXCEPTION
1136 WHEN OTHERS THEN
1137 fnd_file.put_line(fnd_file.log,'Error occurred in procedure jeitappv.' || SQLERRM || SQLCODE);
1138 END jeitappv;
1139
1140 --
1141 -- +======================================================================+
1142 -- Name: JEESRVAR
1143 --
1144 -- Description: This procedure used by the Extract when the Concurrent
1145 -- Program 'Spanish Inter-EU Invoices Journal Report' is run.
1146 --
1147 -- Parameters: P_VAT_REP_ENTITY_ID => VAT Reporting Entity ID
1148 -- P_PERIOD => Tax Calendar Year
1149 -- P_TAX_TYPE => Tax Type
1150 -- P_BALANCING_SEGMENT => Balancing Segment
1151 -- P_START_INV_SEQUENCE => Start Invoice Sequence
1152 -- +======================================================================+
1153 --
1154 PROCEDURE jeesrvar(p_vat_rep_entity_id IN NUMBER
1155 ,p_period IN VARCHAR2
1156 ,p_period_to IN VARCHAR2 -- Bug8267272
1157 ,p_tax_type IN VARCHAR2
1158 ,p_balancing_segment IN VARCHAR2
1159 ,p_start_inv_sequence IN VARCHAR2
1160 ,x_err_msg OUT NOCOPY VARCHAR2)
1161 IS
1162 CURSOR c_spanish_inter_eu_invoices IS
1163 SELECT JZVTD.accounting_date ACCOUNTING_DATE
1164 ,JZVTD.doc_seq_name||'/'||JZVTD.doc_seq_value DOC_SEQUENCE_VALUE
1165 ,JZVTD.trx_date TRX_DATE
1166 ,JZVTD.trx_number TRX_NUMBER
1167 ,JZVTD.billing_tp_name BILLING_TP_NAME
1168 ,JZVTD.billing_tp_tax_reg_num TAX_REG_NUM
1169 ,(NVL(JZVTD.taxable_amt_funcl_curr,JZVTD.taxable_amt))*(NVL(TAX_RECOVERY_RATE,0)/100) NET_AMT_ORIG --Modified for Bug 7457763 -- bug 12824503
1170 ,JZVTD.tax_rate TAX_RATE
1171 ,JZVTD.tax_rate_code TAX_CODE
1172 ,JZVTD.tax_rate_code_description TAX_DESCR
1173 ,NVL(JZVTD.tax_amt_funcl_curr,JZVTD.tax_amt) TAX_AMT_ORIG -- Modified for bug 12824503
1174 ,JZVTD.trx_line_class INVOICE_TYPE
1175 ,JZVTD.tax_rate_id TAX_CODE_ID
1176 ,JZVTD.offset_tax_rate_code OFFSET_TAX_CODE_ID
1177 ,JZVTD.reporting_code LINE_TYPE
1178 ,JZVTD.trx_id TRX_ID
1179 FROM jg_zz_vat_trx_details JZVTD
1180 ,jg_zz_vat_rep_status JZVRS
1181 WHERE JZVRS.vat_reporting_entity_id = P_VAT_REP_ENTITY_ID
1182 -- Bug8267272 Start
1183 AND JZVRS.tax_calendar_period IN (SELECT RPS1.tax_calendar_period
1184 FROM JG_ZZ_VAT_REP_STATUS RPS1,
1185 (Select min(vat_reporting_entity_id) vat_reporting_entity_id,
1186 min(period_start_date) period_start_date
1187 From JG_ZZ_VAT_REP_STATUS
1188 Where vat_reporting_entity_id = p_vat_rep_entity_id
1189 And tax_calendar_period =p_period) RPS2,
1190 (Select min(vat_reporting_entity_id) vat_reporting_entity_id,
1191 min(period_end_date) period_end_date
1192 From JG_ZZ_VAT_REP_STATUS
1193 Where vat_reporting_entity_id = p_vat_rep_entity_id
1194 And tax_calendar_period = p_period_to) RPS3
1195 WHERE RPS1.vat_reporting_entity_id = p_vat_rep_entity_id
1196 AND RPS2.vat_reporting_entity_id = RPS1.vat_reporting_entity_id
1197 AND RPS3.vat_reporting_entity_id = RPS2.vat_reporting_entity_id
1198 AND trunc(RPS1.period_start_date) >=
1199 trunc(RPS2.period_start_date)
1200 AND trunc(RPS1.period_end_date) <= trunc(RPS3.period_end_date)
1201 GROUP by RPS1.tax_calendar_period)
1202 -- Bug8267272 End
1203 AND (JZVTD.trx_tax_balancing_segment = P_BALANCING_SEGMENT OR P_BALANCING_SEGMENT is null)
1204 AND (JZVTD.reporting_code = P_TAX_TYPE OR P_TAX_TYPE is null)
1205 AND JZVRS.source = 'AP'
1206 AND JZVTD.reporting_status_id in (SELECT DISTINCT JZRS.reporting_status_id JZRS
1207 FROM jg_zz_vat_rep_status JZRS
1208 WHERE JZRS.vat_reporting_entity_id = P_VAT_REP_ENTITY_ID
1209 AND JZRS.source = 'AP')
1210 AND JZVTD.gl_date BETWEEN JZVRS.period_start_date and JZVRS.period_end_date;
1211 -- AND JZVTD.reporting_status_id = JZVRS.reporting_status_id ;
1212
1213 BEGIN
1214 FOR c_spanish_inter_eu_inv_rec IN c_spanish_inter_eu_invoices
1215 LOOP
1216 INSERT INTO jg_zz_vat_trx_gt (jg_info_d1
1217 ,jg_info_v1
1218 ,jg_info_d2
1219 ,jg_info_v2
1220 ,jg_info_v3
1221 ,jg_info_v4
1222 ,jg_info_n1
1223 ,jg_info_n2
1224 ,jg_info_v5
1225 ,jg_info_v6
1226 ,jg_info_v7
1227 ,jg_info_n3
1228 ,jg_info_n4
1229 ,jg_info_v9
1230 ,jg_info_v8 --line_type
1231 ,jg_info_n6
1232 ,jg_info_v30
1233 )
1234 VALUES ( c_spanish_inter_eu_inv_rec.accounting_date
1235 ,c_spanish_inter_eu_inv_rec.doc_sequence_value
1236 ,c_spanish_inter_eu_inv_rec.trx_date
1237 ,c_spanish_inter_eu_inv_rec.trx_number
1238 ,SUBSTR(c_spanish_inter_eu_inv_rec.billing_tp_name,1,150)
1239 ,c_spanish_inter_eu_inv_rec.tax_reg_num
1240 ,c_spanish_inter_eu_inv_rec.net_amt_orig
1241 ,c_spanish_inter_eu_inv_rec.tax_rate
1242 ,c_spanish_inter_eu_inv_rec.tax_code
1243 ,c_spanish_inter_eu_inv_rec.tax_descr
1244 ,c_spanish_inter_eu_inv_rec.invoice_type
1245 ,c_spanish_inter_eu_inv_rec.tax_amt_orig
1246 ,c_spanish_inter_eu_inv_rec.tax_code_id
1247 ,c_spanish_inter_eu_inv_rec.offset_tax_code_id
1248 ,c_spanish_inter_eu_inv_rec.line_type
1249 ,c_spanish_inter_eu_inv_rec.trx_id
1250 ,'JEESRVAR');
1251 END LOOP;
1252
1253 SELECT COUNT(*)
1254 INTO l_rec_count
1255 FROM jg_zz_vat_trx_gt
1256 WHERE jg_info_v30='JEESRVAR';
1257
1258 IF p_debug_flag = 'Y' THEN
1259 fnd_file.put_line(fnd_file.log,'Number of records inserted into jg_zz_vat_trx_gt table: ' || l_rec_count );
1260 END IF ;
1261
1262 EXCEPTION
1263 WHEN OTHERS THEN
1264 fnd_file.put_line(fnd_file.log,'EXCEPTION' || SQLERRM || SQLCODE);
1265 END jeesrvar;
1266
1267 --
1268 -- +======================================================================+
1269 -- Name: JEESRPVP
1270 --
1271 -- Description: This procedure used by the Extract when the Concurrent
1272 -- Program 'Spanish Input VAT Journal Report' is run.
1273 --
1274 -- Parameters: P_VAT_REP_ENTITY_ID => VAT Reporting Entity ID
1275 -- P_PERIOD => Tax Calendar Year
1276 -- P_TAX_TYPE => Tax Type
1277 -- P_BALANCING_SEGMENT => Balancing Segment
1278 -- P_START_INV_SEQUENCE => Start Invoice Sequence
1279 -- +======================================================================+
1280 --
1281 PROCEDURE jeesrpvp(p_vat_rep_entity_id IN NUMBER
1282 ,p_period IN VARCHAR2
1283 ,p_period_to IN VARCHAR2 -- Bug8267272
1284 ,p_tax_type IN VARCHAR2
1285 ,p_register_type IN VARCHAR2
1286 ,p_balancing_segment IN VARCHAR2
1287 ,p_start_inv_sequence IN VARCHAR2
1288 ,x_err_msg OUT NOCOPY VARCHAR2)
1289 IS
1290 lc_customer VARCHAR2(240);
1291 lc_tax_reg VARCHAR2(80);
1292 lc_ae_event_type_code VARCHAR2(80);
1293
1294 CURSOR c_spanish_input_vat
1295 IS
1296 SELECT JZVTD.trx_id TRX_ID
1297 ,JZVTD.doc_seq_name||'/'||JZVTD.doc_seq_value DOC_SEQ
1298 ,JZVTD.trx_date TRX_DATE
1299 ,JZVTD.trx_number TRX_NUMBER
1300 ,JZVTD.billing_tp_name BILLING_TP_NAME
1301 ,JZVTD.billing_tp_tax_reg_num BILLING_TP_TAX_REG_NUM
1302 ,nvl(JZVTD.taxable_amt_funcl_curr,JZVTD.taxable_amt)*JZVTD.tax_recovery_rate/100 TAXABLE_AMOUNT
1303 ,JZVTD.tax_rate_code TAX_CODE
1304 ,JZVTD.tax_rate TAX_CODE_RATE
1305 ,JZVTD.tax_rate_code_description TAX_CODE_DESCRIPTION
1306 ,DECODE (JZVTD.trx_line_class,'MISC_CASH_RECEIPT',nvl(JZVTD.tax_amt_funcl_curr,JZVTD.tax_amt), DECODE(JZVTD.tax_recoverable_flag ,'Y',nvl(JZVTD.tax_amt_funcl_curr,JZVTD.tax_amt),0)) TAX1_ACCOUNTED_AMOUNT
1307 ,DECODE(JZVTD.tax_recoverable_flag ,'N',nvl(JZVTD.tax_amt_funcl_curr,JZVTD.tax_amt)) TAX2_ACCOUNTED_AMOUNT
1308 ,DECODE(P_REGISTER_TYPE,'NON RECOVERABLE TAX REGISTER'
1309 ,DECODE(JZVTD.tax_recoverable_flag ,'N',nvl(JZVTD.tax_amt_funcl_curr,JZVTD.tax_amt),0)
1310 ,'BOTH',NVL(DECODE(JZVTD.tax_recoverable_flag ,'N',nvl(JZVTD.tax_amt_funcl_curr,JZVTD.tax_amt)),0)+NVL(DECODE(TRX_LINE_CLASS,'MISC_CASH_RECEIPT'
1311 ,nvl(JZVTD.tax_amt_funcl_curr,JZVTD.tax_amt),DECODE(JZVTD.tax_recoverable_flag ,'Y',nvl(JZVTD.tax_amt_funcl_curr,JZVTD.tax_amt))),0)
1312 ,DECODE(JZVTD.tax_recoverable_flag ,'Y',nvl(JZVTD.tax_amt_funcl_curr,JZVTD.tax_amt),0)) TAX_ACCOUNTED_AMOUNT
1313 ,nvl(JZVTD.tax_amt_funcl_curr,JZVTD.tax_amt) TOTAL_ACCOUNTED_AMOUNT
1314 ,JZVTD.merchant_party_document_number AP_TAXABLE_MERCHANT_DOC_NO
1315 ,JZVTD.merchant_party_name AP_TAXABLE_MERCHANT_NAME
1316 ,JZVTD.merchant_party_tax_reg_number AP_TAXABLE_MERCHANT_TAX_REG_NO
1317 ,JZVTD.extract_source_ledger EXTRACT_SOURCE_LEDGER
1318 ,JZVTD.actg_event_type_code AE_EVENT_TYPE_CODE
1319 ,JZVTD.trx_line_class TRX_CLASS_CODE
1320 ,JZVTD.trx_line_type TAXABLE_LINE_TYPE_CODE
1321 ,JZVTD.accounting_date AH_ACCOUNTING_DATE
1322 ,JZVTD.banking_tp_taxpayer_id /* taxpayer_id */ BANKING_TP_TAXPAYER_ID /* TAXPAYER_ID. Bug#5235824 */
1323 ,JZVTD.bank_account_id BANK_ACCOUNT_ID
1324 ,JZVTD.offset_flag
1325 ,JZVTD.tax_recoverable_flag
1326 ,nvl(JZVTD.tax_amt_funcl_curr,JZVTD.tax_amt) tax_amt
1327 FROM jg_zz_vat_trx_details JZVTD
1328 ,jg_zz_vat_rep_status JZVRS
1329 WHERE JZVRS.vat_reporting_entity_id = P_VAT_REP_ENTITY_ID
1330 -- Bug8267272 Start
1331 AND JZVRS.tax_calendar_period IN (SELECT RPS1.tax_calendar_period
1332 FROM JG_ZZ_VAT_REP_STATUS RPS1,
1333 (Select min(vat_reporting_entity_id) vat_reporting_entity_id,
1334 min(period_start_date) period_start_date
1335 From JG_ZZ_VAT_REP_STATUS
1336 Where vat_reporting_entity_id = p_vat_rep_entity_id
1337 And tax_calendar_period =p_period) RPS2,
1338 (Select min(vat_reporting_entity_id) vat_reporting_entity_id,
1339 min(period_end_date) period_end_date
1340 From JG_ZZ_VAT_REP_STATUS
1341 Where vat_reporting_entity_id = p_vat_rep_entity_id
1342 And tax_calendar_period = p_period_to) RPS3
1343 WHERE RPS1.vat_reporting_entity_id = p_vat_rep_entity_id
1344 AND RPS2.vat_reporting_entity_id = RPS1.vat_reporting_entity_id
1345 AND RPS3.vat_reporting_entity_id = RPS2.vat_reporting_entity_id
1346 AND trunc(RPS1.period_start_date) >=
1347 trunc(RPS2.period_start_date)
1348 AND trunc(RPS1.period_end_date) <= trunc(RPS3.period_end_date)
1349 GROUP by RPS1.tax_calendar_period)
1350 -- Bug8267272 Start
1351 AND ( JZVTD.trx_tax_balancing_segment = P_BALANCING_SEGMENT OR P_BALANCING_SEGMENT is NULL )
1352 -- AND JZVTD.tax_type_code = P_TAX_TYPE -- Bug 6238170
1353 AND JZVTD.reporting_code = P_TAX_TYPE
1354 AND JZVRS.source = 'AP'
1355 AND JZVTD.reporting_status_id in (SELECT DISTINCT JZRS.reporting_status_id JZRS
1356 FROM jg_zz_vat_rep_status JZRS
1357 WHERE JZRS.vat_reporting_entity_id = P_VAT_REP_ENTITY_ID
1358 AND JZRS.source = 'AP')
1359 AND JZVTD.gl_date BETWEEN JZVRS.period_start_date and JZVRS.period_end_date
1360 -- AND JZVTD.tax_amt <> 0
1361 AND ( (P_REGISTER_TYPE = 'BOTH') or
1362 (P_REGISTER_TYPE='NON RECOVERABLE TAX REGISTER' AND JZVTD.tax_recoverable_flag='N')
1363 or
1364 (P_REGISTER_TYPE='TAX REGISTER' AND JZVTD.tax_recoverable_flag='Y')
1365 );
1366 --AND JZVTD.reporting_status_id = JZVRS.reporting_status_id ;
1367 --AND JZVTD.tax_invoice_date BETWEEN JZVRS.period_start_date and JZVRS.period_end_date ;
1368
1369 BEGIN
1370
1371 FOR c_spanish_input_vat_data_rec IN c_spanish_input_vat
1372 LOOP
1373 IF c_spanish_input_vat_data_rec.trx_class_code='MISC_CASH_RECEIPT' THEN
1374 lc_customer :=c_spanish_input_vat_data_rec.bank_account_id ;
1375 lc_tax_reg :=c_spanish_input_vat_data_rec.banking_tp_taxpayer_id; /* taxpayer_id; Bug#5235824 */
1376 ELSIF c_spanish_input_vat_data_rec.trx_class_code='EXPENSE REPORTS' THEN
1377 lc_customer:=c_spanish_input_vat_data_rec.ap_taxable_merchant_name;
1378 lc_tax_reg:=c_spanish_input_vat_data_rec.ap_taxable_merchant_tax_reg_no;
1379 ELSE
1380 lc_customer:=c_spanish_input_vat_data_rec.billing_tp_name;
1381 lc_tax_reg:=c_spanish_input_vat_data_rec.billing_tp_tax_reg_num;
1382 END IF;
1383
1384 IF c_spanish_input_vat_data_rec.ae_event_type_code = 'INVOICE CANCELLATION' THEN
1385 lc_ae_event_type_code := c_spanish_input_vat_data_rec.ae_event_type_code;
1386 ELSE
1387 lc_ae_event_type_code := 'JE_NORMAL';
1388 END IF;
1389
1390 IF P_register_type = 'NON RECOVERABLE TAX REGISTER' AND c_spanish_input_vat_data_rec.tax2_accounted_amount IS NULL then
1391 NULL;
1392 ELSIF c_spanish_input_vat_data_rec.taxable_line_type_code = 'PREPAY' and c_spanish_input_vat_data_rec.taxable_amount IS NULL then
1393 NULL;
1394 ELSE
1395 INSERT INTO jg_zz_vat_trx_gt(jg_info_v1
1396 ,jg_info_d1
1397 ,jg_info_v2
1398 ,jg_info_v3
1399 ,jg_info_v4
1400 ,jg_info_n1
1401 ,jg_info_v5
1402 ,jg_info_n2
1403 ,jg_info_v6
1404 ,jg_info_n3
1405 ,jg_info_n4
1406 ,jg_info_n5
1407 ,jg_info_n6
1408 ,jg_info_v7
1409 ,jg_info_v8
1410 ,jg_info_d2
1411 ,jg_info_v30
1412 )
1413 VALUES (
1414 c_spanish_input_vat_data_rec.doc_seq
1415 ,c_spanish_input_vat_data_rec.trx_date
1416 ,DECODE (c_spanish_input_vat_data_rec.trx_class_code,'EXPENSE_REPORT',c_spanish_input_vat_data_rec.ap_taxable_merchant_doc_no,c_spanish_input_vat_data_rec.trx_number)
1417 ,SUBSTR(lc_customer,1,150)
1418 ,lc_tax_reg
1419 ,DECODE (c_spanish_input_vat_data_rec.trx_class_code,'MISC_CASH_RECEIPT',-c_spanish_input_vat_data_rec.taxable_amount,c_spanish_input_vat_data_rec.taxable_amount)
1420 ,c_spanish_input_vat_data_rec.tax_code
1421 ,c_spanish_input_vat_data_rec.tax_code_rate
1422 ,c_spanish_input_vat_data_rec.tax_code_description
1423 -- ,DECODE (c_spanish_input_vat_data_rec.trx_class_code,'MISC_CASH_RECEIPT',-c_spanish_input_vat_data_rec.tax1_accounted_amount,c_spanish_input_vat_data_rec.tax1_accounted_amount)
1424 ,c_spanish_input_vat_data_rec.tax1_accounted_amount
1425 ,c_spanish_input_vat_data_rec.tax2_accounted_amount
1426 ,DECODE (c_spanish_input_vat_data_rec.trx_class_code,'MISC_CASH_RECEIPT',-c_spanish_input_vat_data_rec.tax_amt,c_spanish_input_vat_data_rec.tax_amt)
1427 ,DECODE (c_spanish_input_vat_data_rec.trx_class_code,'MISC_CASH_RECEIPT',-c_spanish_input_vat_data_rec.taxable_amount - c_spanish_input_vat_data_rec.tax_accounted_amount
1428 --,c_spanish_input_vat_data_rec.taxable_amount + c_spanish_input_vat_data_rec.tax_accounted_amount)
1429 ,c_spanish_input_vat_data_rec.taxable_amount + c_spanish_input_vat_data_rec.tax_amt)
1430 ,c_spanish_input_vat_data_rec.extract_source_ledger
1431 ,lc_ae_event_type_code
1432 ,c_spanish_input_vat_data_rec.ah_accounting_date
1433 ,'JEESRPVP'
1434 );
1435 END IF;
1436 END LOOP;
1437
1438 SELECT COUNT(*)
1439 INTO l_rec_count
1440 FROM jg_zz_vat_trx_gt
1441 WHERE jg_info_v30='JEESRPVP';
1442
1443 IF p_debug_flag = 'Y' THEN
1444 fnd_file.put_line(fnd_file.log,'Number of records inserted into jg_zz_vat_trx_gt table: ' || l_rec_count );
1445 END IF ;
1446
1447 EXCEPTION
1448 WHEN OTHERS THEN
1449 fnd_file.put_line(fnd_file.log,'EXCEPTION' || SQLERRM || SQLCODE);
1450 END jeesrpvp;
1451
1452 --
1453 -- +======================================================================+
1454 -- Name: JOURNAL_AP
1455 --
1456 -- Description: This procedure used by the Extract when the Concurrent
1457 -- Program Journal_AP is run.
1458 --
1459 -- Parameters: P_VAT_REP_ENTITY_ID => VAT Reporting Entity ID
1460 -- P_PERIOD => Tax Calendar Year
1461 -- +======================================================================+
1462 --
1463 PROCEDURE journal_ap(p_vat_rep_entity_id IN NUMBER
1464 ,p_period IN VARCHAR2
1465 ,x_err_msg OUT NOCOPY VARCHAR2)
1466 IS
1467 CURSOR c_journal_ap IS
1468 SELECT JZVRS.tax_calendar_year PERIOD_YEAR
1469 ,JZVTD.period_name PERIOD_NAME
1470 ,JZVTD.doc_seq_name SEQUENCE_NAME
1471 ,JZVTD.doc_seq_value SEQUENCE_NUMBER
1472 ,JZVTD.doc_seq_id DOCUMENT_SEQUENCE_ID -- Bug 9597258
1473 ,FND_DATE.DATE_TO_DISPLAYDATE(JZVTD.trx_date) DOCUMENT_DATE
1474 ,JZVTD.billing_tp_number VENDOR_NUM
1475 ,JZVTD.billing_tp_name VENDOR_NAME
1476 ,JZVTD.trx_number INVOICE_NUM
1477 ,NVL(JZVTD.TAXABLE_AMT_FUNCL_CURR,JZVTD.TAXABLE_AMT) INVOICE_AMOUNT
1478 ,DECODE(JZVTD.TRX_LINE_TYPE,'TAX' , 0,NVL(JZVTD.TAXABLE_AMT_FUNCL_CURR, JZVTD.TAXABLE_AMT)) TAXABLE_AMOUNT
1479 ,DECODE(JZVTD.TRX_LINE_TYPE,'TAX',NVL(JZVTD.TAXABLE_AMT_FUNCL_CURR,JZVTD.TAXABLE_AMT)) TAX_AMOUNT
1480 ,JZVTD.tax_rate_code VAT_CODE
1481 ,JZVTD.tax_rate_vat_trx_type_desc VAT_TRT
1482 ,JZVBA.taxable_box VAT_REPORT_BOX
1483 ,JZVTD.trx_line_number LINE_NUM
1484 ,JZVTD.account_flexfield FLEXDATA
1485 ,NVL(TAXABLE_AMT_FUNCL_CURR,TAXABLE_AMT) ACCTD_AMOUNT
1486 ,NVL(JZVTD.billing_tp_site_tax_reg_num,JZVTD.billing_tp_tax_reg_num) VAT_NUM
1487 ,JZVTD.tax_rate_code_name VAT_NAME
1488 ,JZVTD.tax_type_code TAX_TYPE
1489 ,JZVTD.tax_rate VAT_RATE
1490 ,JZVTD.tax_rate_code_description VAT_DESC
1491 ,JZVTD.trx_id INVOICE_ID
1492 ,JZVTD.trx_currency_code CURRENCY_CODE
1493 ,JZVTD.tax_recovery_rate RECOVERY_RATE
1494 ,JZVTD.tax_recoverable_flag TAX_RECOVERABLE_FLAG
1495 ,JZVTD.accounting_date ACCOUNTING_DATE
1496 ,NVL(JZVTD.taxable_amt,0) TAXABLE_BASE_AMOUNT
1497 ,NVL(JZVTD.tax_amt,0) TAX_BASE_AMOUNT
1498 ,JZVTD.offset_tax_rate_code OFFSET_TAX_CODE_ID
1499 ,NVL(JZVTD.merchant_party_name,JZVTD.billing_tp_name) VNAME
1500 ,NVL(JZVTD.start_expense_date,JZVTD.trx_date) INVOICE_DATE
1501 ,NVL(DECODE(JZVTD.tax_recoverable_flag,'Y',taxable_amt_funcl_curr),0) TAXABLE_REC_BASE_AMOUNT
1502 ,NVL(DECODE(JZVTD.tax_recoverable_flag,'Y',taxable_amt) ,0) TAXABLE_REC_AMOUNT
1503 ,NVL(DECODE(JZVTD.tax_recoverable_flag,'Y',tax_amt_funcl_curr),0) TAX_REC_BASE_AMOUNT
1504 ,NVL(DECODE(JZVTD.tax_recoverable_flag,'Y',tax_amt),0) TAX_REC_AMOUNT
1505 ,NVL(DECODE(JZVTD.tax_recoverable_flag,'Y',taxable_amt_funcl_curr),0) TAXABLE_NREC_BASE_AMOUNT
1506 ,NVL(DECODE(JZVTD.tax_recoverable_flag,'N',taxable_amt),0) TAXABLE_NREC_AMOUNT
1507 ,NVL(DECODE(JZVTD.tax_recoverable_flag,'N',tax_amt_funcl_curr),0) TAX_NREC_BASE_AMOUNT
1508 ,NVL(DECODE(JZVTD.tax_recoverable_flag,'Y',tax_amt) ,0) TAX_NREC_AMOUNT
1509 ,JZVTD.gl_transfer_flag INV_DIST_POSTED_FLAG
1510 ,JZVTD.doc_seq_name||'/'||JZVTD.doc_seq_value DOC_SEQUENCE_VALUE
1511 ,NVL(JZVTD.tax_amt, JZVTD.tax_amt_funcl_curr) TAX_AMT_ORIG
1512 ,JZVTD.trx_line_class INVOICE_TYPE
1513 ,JZVTD.tax_rate_id TAX_CODE_ID
1514 ,SUBSTR(NVL(JZVTD.merchant_party_name,JZVTD.billing_tp_name),1,13) VAT_REGISTER_NAME
1515 ,JZVTD.tax_rate_code TAX_CODE
1516 ,JZVTD.merchant_party_document_number AP_TAXABLE_MERCHANT_DOC_NO
1517 ,JZVTD.merchant_party_name AP_TAXABLE_MERCHANT_NAME
1518 ,JZVTD.merchant_party_tax_reg_number AP_TAXABLE_MERCHANT_TAX_REG_NO
1519 ,JZVTD.extract_source_ledger EXTRACT_SOURCE_LEDGER
1520 ,JZVTD.actg_event_type_code AE_EVENT_TYPE_CODE
1521 ,JZVTD.trx_line_type TAXABLE_LINE_TYPE_CODE
1522 FROM jg_zz_vat_trx_details JZVTD
1523 ,jg_zz_vat_rep_status JZVRS
1524 ,jg_zz_vat_box_allocs JZVBA
1525 WHERE JZVRS.tax_calendar_period = P_PERIOD
1526 AND JZVRS.vat_reporting_entity_id = P_VAT_REP_ENTITY_ID
1527 AND JZVRS.source = 'AP'
1528 AND JZVRS.reporting_status_id = JZVTD.reporting_status_id
1529 AND JZVTD.vat_transaction_id = JZVBA.vat_transaction_id(+) ;
1530
1531 BEGIN
1532 l_rec_count:=0;
1533 FOR c_journal_ap_rec IN c_journal_ap
1534 LOOP
1535 INSERT INTO jg_zz_vat_trx_gt(
1536 jg_info_n29
1537 ,jg_info_v2
1538 ,jg_info_v3
1539 ,jg_info_v24
1540 ,jg_info_n2
1541 ,jg_info_d1
1542 ,jg_info_v26
1543 ,jg_info_v4
1544 ,jg_info_v27
1545 ,jg_info_n5
1546 ,jg_info_n6
1547 ,jg_info_n7
1548 ,jg_info_v5
1549 ,jg_info_v6
1550 ,jg_info_v7
1551 ,jg_info_n10
1552 ,jg_info_n11
1553 ,jg_info_v8
1554 ,jg_info_n12
1555 ,jg_info_n13
1556 ,jg_info_v9
1557 ,jg_info_v10
1558 ,jg_info_n14
1559 ,jg_info_v11
1560 ,jg_info_n15
1561 ,jg_info_v12
1562 ,jg_info_v13
1563 ,jg_info_v14
1564 ,jg_info_d2
1565 ,jg_info_n16
1566 ,jg_info_n17
1567 ,jg_info_v28
1568 ,jg_info_d3
1569 ,jg_info_n19
1570 ,jg_info_n20
1571 ,jg_info_n21
1572 ,jg_info_n22
1573 ,jg_info_n23
1574 ,jg_info_n24
1575 ,jg_info_n25
1576 ,jg_info_n26
1577 ,jg_info_v15
1578 ,jg_info_v16
1579 ,jg_info_n27
1580 ,jg_info_v29
1581 ,jg_info_v17
1582 ,jg_info_v18
1583 ,jg_info_v19
1584 ,jg_info_v20
1585 ,jg_info_v21
1586 ,jg_info_v22
1587 ,jg_info_v23
1588 ,jg_info_v25
1589 ,jg_info_v30
1590 )
1591 VALUES (
1592 c_journal_ap_rec.period_year
1593 ,c_journal_ap_rec.period_name
1594 ,c_journal_ap_rec.sequence_name
1595 ,c_journal_ap_rec.sequence_number
1596 ,c_journal_ap_rec.document_sequence_id
1597 ,c_journal_ap_rec.document_date
1598 ,c_journal_ap_rec.vendor_num
1599 ,c_journal_ap_rec.vendor_name
1600 ,c_journal_ap_rec.invoice_num
1601 ,c_journal_ap_rec.invoice_amount
1602 ,c_journal_ap_rec.taxable_amount
1603 ,c_journal_ap_rec.tax_amount
1604 ,c_journal_ap_rec.vat_code
1605 ,c_journal_ap_rec.vat_trt
1606 ,c_journal_ap_rec.vat_report_box
1607 ,c_journal_ap_rec.tax_nrec_amount
1608 ,c_journal_ap_rec.line_num
1609 ,c_journal_ap_rec.flexdata
1610 ,c_journal_ap_rec.acctd_amount
1611 ,c_journal_ap_rec.vat_num
1612 ,c_journal_ap_rec.vat_name
1613 ,c_journal_ap_rec.tax_type
1614 ,c_journal_ap_rec.vat_rate
1615 ,c_journal_ap_rec.vat_desc
1616 ,c_journal_ap_rec.invoice_id
1617 ,c_journal_ap_rec.currency_code
1618 ,c_journal_ap_rec.recovery_rate
1619 ,c_journal_ap_rec.tax_recoverable_flag
1620 ,c_journal_ap_rec.accounting_date
1621 ,c_journal_ap_rec.taxable_base_amount
1622 ,c_journal_ap_rec.tax_base_amount
1623 ,c_journal_ap_rec.offset_tax_code_id
1624 ,c_journal_ap_rec.invoice_date
1625 ,c_journal_ap_rec.taxable_rec_base_amount
1626 ,c_journal_ap_rec.taxable_rec_amount
1627 ,c_journal_ap_rec.tax_rec_base_amount
1628 ,c_journal_ap_rec.tax_rec_amount
1629 ,c_journal_ap_rec.taxable_nrec_base_amount
1630 ,c_journal_ap_rec.taxable_nrec_amount
1631 ,c_journal_ap_rec.tax_nrec_base_amount
1632 ,c_journal_ap_rec.tax_nrec_amount
1633 ,c_journal_ap_rec.inv_dist_posted_flag
1634 ,c_journal_ap_rec.doc_sequence_value
1635 ,c_journal_ap_rec.tax_amt_orig
1636 ,c_journal_ap_rec.invoice_type
1637 ,c_journal_ap_rec.vat_register_name
1638 ,c_journal_ap_rec.tax_code
1639 ,c_journal_ap_rec.ap_taxable_merchant_doc_no
1640 ,c_journal_ap_rec.ap_taxable_merchant_name
1641 ,c_journal_ap_rec.ap_taxable_merchant_tax_reg_no
1642 ,c_journal_ap_rec.extract_source_ledger
1643 ,c_journal_ap_rec.ae_event_type_code
1644 ,c_journal_ap_rec.taxable_line_type_code
1645 ,'JOURNAL_AP'
1646 );
1647 END LOOP;
1648
1649 SELECT COUNT(*) INTO l_rec_count
1650 FROM jg_zz_vat_trx_gt
1651 WHERE jg_info_v30='JOURNAL_AP';
1652
1653 IF p_debug_flag = 'Y' THEN
1654 fnd_file.put_line(fnd_file.log,'Number of records inserted into jg_zz_vat_trx_gt table: ' || l_rec_count );
1655 END IF ;
1656 EXCEPTION
1657 WHEN OTHERS THEN
1658 fnd_file.put_line(fnd_file.log,'EXCEPTION' || SQLERRM || SQLCODE);
1659 END journal_ap;
1660
1661 FUNCTION lcu_trans_line_tax_amt (P_TRX_ID IN NUMBER) RETURN NUMBER IS
1662 ln_tax_amt_funcl_curr NUMBER;
1663 BEGIN
1664 SELECT SUM(decode(JZVTD.tax_recoverable_flag, 'Y',
1665 nvl(JZVTD.tax_amt,JZVTD.tax_amt_funcl_curr),'N', 0 )) /* Bug#5235824 JZVTD.tax_amt_funcl_curr */
1666 INTO ln_tax_amt_funcl_curr
1667 FROM jg_zz_vat_trx_details JZVTD
1668 WHERE JZVTD.TRX_ID = P_TRX_ID
1669 AND JZVTD.extract_source_ledger in ('AP') /* AND JZVTD.posted_flag = 'P' Bug#5235824 */
1670 GROUP BY JZVTD.TRX_LINE_ID;
1671
1672 RETURN ln_tax_amt_funcl_curr;
1673
1674 EXCEPTION
1675 WHEN OTHERS THEN
1676 fnd_file.put_line(fnd_file.log,'EXCEPTION' || SQLERRM || SQLCODE);
1677 RETURN 0;
1678 END lcu_trans_line_tax_amt;
1679
1680
1681 FUNCTION lcu_trans_line_taxable_amt (p_trx_id IN NUMBER) RETURN NUMBER IS
1682 ln_taxable_amt_funcl_curr NUMBER;
1683 BEGIN
1684 SELECT SUM (decode(JZVTD.tax_recoverable_flag, 'Y',
1685 nvl(JZVTD.taxable_amt,JZVTD.taxable_amt_funcl_curr), 'N', 0)) /* Bug#5235824 JZVTD.taxable_amt_funcl_curr */
1686 INTO ln_taxable_amt_funcl_curr
1687 FROM jg_zz_vat_trx_details JZVTD
1688 WHERE JZVTD.TRX_ID = P_TRX_ID
1689 AND JZVTD.extract_source_ledger = 'AP' /* AND JZVTD.posted_flag ='P' Bug#5235824 */
1690 GROUP BY JZVTD.trx_line_id;
1691
1692 RETURN ln_taxable_amt_funcl_curr;
1693
1694 EXCEPTION
1695 WHEN OTHERS THEN
1696 fnd_file.put_line(fnd_file.log,'EXCEPTION' || SQLERRM || SQLCODE);
1697 RETURN 0;
1698 END lcu_trans_line_taxable_amt;
1699
1700 FUNCTION lcu_trans_line_tax_taxable_amt (p_trx_id IN NUMBER) RETURN NUMBER IS
1701 ln_taxable_amt_funcl_curr NUMBER;
1702 BEGIN
1703 SELECT SUM(decode(JZVTD.tax_recoverable_flag, 'Y', nvl(JZVTD.tax_amt,JZVTD.tax_amt_funcl_curr),'N', 0 ))
1704 + SUM (decode(JZVTD.tax_recoverable_flag, 'Y', nvl(JZVTD.taxable_amt,JZVTD.taxable_amt_funcl_curr), 'N', 0))
1705 /* Bug#5235824. SUM (JZVTD.tax_amt_funcl_curr)+ SUM (JZVTD.taxable_amt_funcl_curr) */
1706 INTO ln_taxable_amt_funcl_curr
1707 FROM jg_zz_vat_trx_details JZVTD
1708 WHERE JZVTD.TRX_ID = P_TRX_ID
1709 AND JZVTD.extract_source_ledger = 'AP' /* AND JZVTD.posted_flag = 'P' Bug#5235824 */
1710 GROUP BY JZVTD.trx_line_id;
1711
1712 RETURN ln_taxable_amt_funcl_curr;
1713
1714 EXCEPTION
1715 WHEN OTHERS THEN
1716 fnd_file.put_line(fnd_file.log,'EXCEPTION' || SQLERRM || SQLCODE);
1717 RETURN 0;
1718 END lcu_trans_line_tax_taxable_amt;
1719
1720
1721 FUNCTION get_current_date RETURN VARCHAR2
1722 IS
1723 BEGIN
1724 fnd_file.put_line(fnd_file.log,'Calling Current Date Conversions');
1725 RETURN fnd_date.date_to_charDT(sysdate);
1726 END get_current_date;
1727
1728
1729 FUNCTION get_sequence_number RETURN NUMBER IS
1730 l_start_seq NUMBER;
1731 BEGIN
1732 IF p_report_name = 'JEITAPPV' or p_report_name = 'JEITAPSR' THEN
1733 SELECT jg_info_n1
1734 INTO l_start_seq
1735 FROM jg_zz_vat_trx_gt
1736 WHERE jg_info_v30 = 'SEQ';
1737
1738 RETURN l_start_seq;
1739 ELSE
1740 RETURN 0;
1741 END IF;
1742 EXCEPTION
1743 WHEN others THEN
1744 RETURN 0;
1745 END get_sequence_number;
1746
1747 END JG_ZZ_JOURNAL_AP_PKG;