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