[Home] [Help]
PACKAGE BODY: APPS.JE_ZZ_AUDIT_AP_PKG
Source
1 PACKAGE BODY JE_ZZ_AUDIT_AP_PKG
2 -- $Header: jezzauditapb.pls 120.23.12020000.2 2012/07/17 09:03:08 rshergil noship $
3 AS
4
5 /*
6 REM +======================================================================+
7 REM Name: BeforeReport
8 REM
9 REM Description: This function is called before the data template is processed
10 REM and sets all the required variables and populates the data in
11 REM the required tmp tables.
12 REM
13 REM Parameters:
14 REM
15 REM +======================================================================+
16 */
17 FUNCTION BeforeReport RETURN BOOLEAN IS
18 BEGIN
19
20 DECLARE
21 l_functcurr VARCHAR2(15);
22
23 l_curr_name VARCHAR2(40);
24 l_real_inv_amt NUMBER;
25 l_payment_amt NUMBER;
26 l_txbl_disc_amt NUMBER;
27 l_txbl_amt NUMBER;
28 l_tax_disc_amt NUMBER;
29 l_txbl_amt2 NUMBER;
30 l_rec_tax_amt NUMBER;
31 l_tax_amt NUMBER;
32 l_item_tax_amt NUMBER;
33 l_coaid NUMBER;
34 l_ledger_name VARCHAR2(30);
35 l_errbuf VARCHAR2(132);
36 l_errbuf2 VARCHAR2(132);
37 l_ledger_id NUMBER;
38 l_start_date DATE;
39 l_end_date DATE;
40 l_prt_inv_amt NUMBER;
41 l_address_line_1 VARCHAR (240);
42 l_address_line_2 VARCHAR (240);
43 l_address_line_3 VARCHAR (240);
44 l_address_line_4 VARCHAR (240);
45 l_city VARCHAR (60);
46 l_company_name VARCHAR (240);
47 l_contact_name VARCHAR (360);
48 l_country VARCHAR (60);
49 l_func_curr VARCHAR (30);
50 l_legal_entity_name VARCHAR (240);
51 l_period_end_date DATE;
52 l_period_start_date DATE;
53 l_phone_number VARCHAR (40);
54 l_postal_code VARCHAR (60);
55 l_registration_num VARCHAR (30);
56 l_reporting_status VARCHAR (60);
57 l_tax_payer_id VARCHAR (60);
58 l_tax_registration_num VARCHAR (240);
59 l_tax_regime VARCHAR2(240);
60 l_entity_identifier VARCHAR2(360);
61 l_trx_ccid NUMBER;
62 l_tax_ccid NUMBER;
63 l_acc_no VARCHAR2(25);
64 l_company VARCHAR2(25);
65 errbuf VARCHAR2(1000);
66 INVALID_LEDGER EXCEPTION;
67 l_company_desc VARCHAR2(240);
68 -- Added for GLOB-006 ER
69 l_province VARCHAR2(120);
70 l_comm_num VARCHAR2(30);
71 l_vat_reg_num VARCHAR2(50);
72 -- end here
73
74
75 CURSOR company_info IS
76 SELECT DISTINCT jg_info_v5
77 FROM jg_zz_vat_trx_gt;
78
79 CURSOR C_GENERIC(p_start_date DATE, p_end_date DATE) IS
80 SELECT SUBSTR(ven.vendor_name, 1, 10) ven_name
81 ,SUBSTR(ven.segment1, 1, 8) ven_no
82 ,inv.invoice_type_lookup_code inv_type
83 ,SUM(nvl(dis.base_amount, dis.amount)) tax_amt
84 ,item.tax_recovery_rate rec_per
85 ,NULL company
86 ,NULL acc_no
87 ,tax.percentage_rate tax_rate
88 ,tax.tax_rate_id tax_id
89 ,tax.offset_tax_rate_code offset_tax_rate_code
90 ,inv.global_attribute1 tax_type
91 ,SUBSTR(inv.invoice_num, 1, 10) inv_no
92 ,MIN(dis.accounting_date) acc_date
93 ,inv.invoice_id invoice_id
94 ,inv.cancelled_date cancelled_date
95 ,COUNT(dis.charge_applicable_to_dist_id) item_line_cnt
96 ,MAX(dis.charge_applicable_to_dist_id) charge_dist_id
97 ,chk.void_date check_void_date
98 ,SUM(NVL(aip.invoice_base_amount, aip.amount)) pay_amt
99 ,dis.line_type_lookup_code line_type_lookup_code
100 ,item.line_type_lookup_code line_type_lookup_code_item
101 ,ppdis.line_type_lookup_code line_type_lookup_code_prepay
102 ,item.reversal_flag reversal_flag_item
103 ,aip.reversal_flag reversal_flag_pay
104 ,ppdis.reversal_flag reversal_flag_prepay
105 ,dis.parent_reversal_id parent_reversal_id
106 ,inv.base_amount base_amount
107 ,inv.invoice_amount invoice_amount
108 ,chk.void_date void_date
109 ,chk.future_pay_due_date future_pay_due_date
110 ,chk.check_date check_date
111 ,inv.payment_status_flag payment_status_flag
112 ,aip.accounting_date accounting_date
113 ,aip.reversal_inv_pmt_id reversal_inv_pmt_id
114 ,zl.application_id
115 ,zl.event_class_code
116 ,zl.trx_line_id
117 ,zl.entity_code
118 FROM po_vendors ven
119 ,ap_invoices inv
120 ,ap_invoice_distributions dis
121 ,zx_rates_b tax
122 ,ap_invoice_distributions item
123 ,ap_invoices pp
124 ,ap_invoice_distributions ppdis
125 ,ap_checks chk
126 ,ap_invoice_payments aip
127 ,ap_invoice_lines apl
128 ,zx_lines zl
129 ,zx_lines_det_factors zldf
130 WHERE ( ( P_LEDGER_ID IS NULL AND P_COMPANY IS NULL AND inv.legal_entity_id = G_LE_ID )
131 OR ( P_LEDGER_ID IS NOT NULL AND P_COMPANY IS NULL AND inv.set_of_books_id = P_LEDGER_ID )
132 OR ( P_COMPANY IS NOT NULL AND inv.set_of_books_id = P_LEDGER_ID
133 and get_balancing_segment(dis.dist_code_combination_id) = P_COMPANY) )
134 AND ven.vendor_id = inv.vendor_id
135 AND dis.invoice_id = inv.invoice_id
136 AND tax.tax_rate_id = dis.tax_code_id
137 AND dis.charge_applicable_to_dist_id =
138 item.invoice_distribution_id
139 AND inv.global_attribute_category = zldf.document_sub_type
140 -- AND dis.line_type_lookup_code = 'TAX'
141 AND inv.invoice_id = apl.invoice_id
142 AND apl.invoice_id = zl.trx_id
143 AND apl.line_number = zl.trx_line_number
144 AND apl.application_id = zl.application_id
145 AND zl.entity_code = 'AP_INVOICES'
146 AND inv.invoice_type_lookup_code = zl.event_class_code
147 AND dis.invoice_line_number = apl.line_number
148 AND zl.application_id = zldf.application_id
149 AND zl.event_class_code = zldf.event_class_code
150 AND zl.entity_code = zldf.entity_code
151 AND zl.trx_id = zldf.trx_id
152 AND zl.trx_line_id = zldf.trx_line_id
153 --
154 AND dis.match_status_flag IS NOT NULL
155 AND dis.accounting_date BETWEEN p_start_date AND p_end_date
156 AND dis.tax_recoverable_flag = 'Y'
157 AND item.prepay_distribution_id = ppdis.invoice_distribution_id
158 AND ppdis.invoice_id = pp.invoice_id
159 AND inv.invoice_id = aip.invoice_id
160 AND chk.check_id = aip.check_id
161 AND aip.accounting_date BETWEEN p_start_date AND p_end_date
162 GROUP BY SUBSTR(ven.vendor_name, 1, 10)
163 ,SUBSTR(ven.segment1, 1, 8)
164 ,inv.invoice_type_lookup_code
165 ,item.tax_recovery_rate
166 ,tax.percentage_rate
167 ,tax.tax_rate_id
168 ,tax.offset_tax_rate_code
169 ,inv.global_attribute1
170 ,SUBSTR(inv.invoice_num, 1, 10)
171 ,inv.invoice_id
172 ,inv.cancelled_date
173 ,chk.void_date
174 ,dis.line_type_lookup_code
175 ,item.line_type_lookup_code
176 ,ppdis.line_type_lookup_code
177 ,item.reversal_flag
178 ,aip.reversal_flag
179 ,ppdis.reversal_flag
180 ,dis.parent_reversal_id
181 ,inv.base_amount
182 ,inv.invoice_amount
183 ,chk.void_date
184 ,chk.future_pay_due_date
185 ,chk.check_date
186 ,inv.payment_status_flag
187 ,aip.accounting_date
188 ,aip.reversal_inv_pmt_id
189 ,zl.application_id
190 ,zl.event_class_code
191 ,zl.trx_line_id
192 ,zl.entity_code;
193
194 CURSOR C_FETCH_LEDGER_INFO IS
195 SELECT ledger_id
196 ,chart_of_accounts_id
197 ,ledger_name
198 ,currency_code
199 FROM gl_ledger_le_v
200 WHERE legal_entity_id = G_LE_ID
201 AND ledger_category_code = 'PRIMARY';
202
203
204
205 CURSOR C_FETCH_PERIOD IS
206 SELECt last_day(add_months((P_REP_DATE),-1))+1 START_DATE,
207 last_day((P_REP_DATE)) END_DATE
208 FROM dual;
209
210 CURSOR C_TAX_AMT IS
211 SELECT SUM(jg_info_n17) cs_item_tax_amt
212 ,jg_info_n8 charge_dist_id
213 ,jg_info_n7 item_line_cnt
214 ,jg_info_n1 tax_amt
215 ,jg_info_n5 invoice_id
216 ,jg_info_v8 tax_type
217 ,jg_info_v4 inv_type
218 ,jg_info_n10 l_real_inv_amt
219 ,jg_info_n11 l_txbl_disc_amt
220 ,jg_info_n12 l_payment_amt
221 ,jg_info_d2 check_void_date
222 FROM JG_ZZ_VAT_TRX_GT
223 WHERE jg_info_v30='JEFRTXDC'
224 GROUP BY jg_info_n8
225 ,jg_info_n7
226 ,jg_info_n1
227 ,jg_info_n5
228 ,jg_info_v8
229 ,jg_info_v4
230 ,jg_info_n10
231 ,jg_info_n11
232 ,jg_info_n12
233 ,jg_info_d2;
234
235 CURSOR c_curr_name (p_functcurr VARCHAR2)
236 IS
237 SELECT substr(name, 1, 40) name
238 ,precision
239 INTO l_curr_name
240 ,g_precision
241 FROM fnd_currencies_vl
242 WHERE currency_code = p_functcurr;
243
244 CURSOR c_inv_less_tax_flag
245 IS
246 SELECT disc_is_inv_less_tax_flag
247 FROM ap_system_parameters;
248
249 CURSOR c_get_trx_ccid (p_trx_id NUMBER
250 ,p_application_id NUMBER
251 ,p_event_class_code VARCHAR2
252 ,p_trx_line_id NUMBER
253 ,p_entity_code VARCHAR2)
254 IS
255 SELECT xla_event.event_id
256 ,xla_head.ae_header_id
257 ,xla_line.code_combination_id
258 ,xla_head.period_name
259 ,zx_dist.rec_nrec_tax_dist_id
260 FROM zx_lines zx_line
261 ,zx_lines_det_factors zx_det
262 ,zx_rec_nrec_dist zx_dist
263 ,zx_taxes_vl zx_tax
264 ,zx_rates_vl zx_rate
265 ,xla_transaction_entities xla_ent
266 ,xla_events xla_event
267 ,xla_ae_headers xla_head
268 ,xla_ae_lines xla_line
269 ,xla_distribution_links xla_dist
270 ,xla_acct_class_assgns acs
271 ,xla_assignment_defns_b asd
272 WHERE zx_det.internal_organization_id = zx_line.internal_organization_id
273 AND zx_det.application_id = zx_line.application_id
274 AND zx_det.application_id = 200
275 AND zx_det.entity_code = zx_line.entity_code
276 AND zx_det.event_class_code = zx_line.event_class_code
277 AND zx_det.trx_id = zx_line.trx_id
278 AND zx_line.trx_id = xla_ent.source_id_int_1 -- Accounting Joins
279 AND zx_det.application_id = xla_ent.application_id
280 AND xla_ent.entity_code = 'AP_INVOICES'
281 AND xla_ent.entity_id = xla_event.entity_id
282 AND xla_event.event_id = xla_head.event_id
283 AND xla_head.ae_header_id = xla_line.ae_header_id
284 AND xla_dist.event_id = xla_event.event_id
285 AND acs.program_code = 'TAX_REP_LEDGER_PROCUREMENT'
286 AND acs.program_code = asd.program_code
287 AND asd.assignment_code = acs.assignment_code
288 AND asd.enabled_flag = 'Y'
289 AND acs.accounting_class_code = xla_line.accounting_class_code -- Accounting Joins Enda
290 AND xla_dist.source_distribution_id_num_1 = zx_dist.trx_line_dist_id
291 AND xla_dist.source_distribution_type = 'AP_INVOICE_DISTRIBUTIONS'
292 AND zx_line.tax_line_id = zx_dist.tax_line_id
293 AND zx_det.tax_reporting_flag = 'Y'
294 AND zx_line.tax_id = zx_tax.tax_id
295 AND zx_line.tax_rate_id = nvl(zx_rate.source_id, zx_rate.tax_rate_id)
296 AND zx_line.entity_code = p_entity_code
297 AND zx_line.trx_id = p_trx_id
298 AND zx_line.application_id = p_application_id
299 AND zx_line.event_class_code = p_event_class_code
300 AND zx_line.trx_line_id = p_trx_line_id
301 AND (zx_rate.source_id IS NOT NULL);
302
303 CURSOR c_get_tax_ccid (p_trx_id NUMBER
304 ,p_application_id NUMBER
305 ,p_event_class_code VARCHAR2
306 ,p_trx_line_id NUMBER
307 ,p_entity_code VARCHAR2)
308 IS
309 SELECT xla_event.event_id
310 ,xla_head.ae_header_id
311 ,xla_line.code_combination_id
312 ,xla_head.period_name
313 ,zx_dist.rec_nrec_tax_dist_id
314 FROM zx_lines zx_line
315 ,zx_lines_det_factors zx_det
316 ,zx_rec_nrec_dist zx_dist
317 ,zx_taxes_vl zx_tax
318 ,zx_rates_vl zx_rate
319 ,xla_transaction_entities xla_ent
320 ,xla_events xla_event
321 ,xla_ae_headers xla_head
322 ,xla_ae_lines xla_line
323 ,xla_distribution_links xla_dist
324 ,xla_acct_class_assgns acs
325 ,xla_assignment_defns_b asd
326 WHERE zx_det.internal_organization_id = zx_line.internal_organization_id
327 AND zx_det.application_id = zx_line.application_id
328 AND zx_det.application_id = 200
329 AND zx_det.entity_code = zx_line.entity_code
330 AND zx_det.event_class_code = zx_line.event_class_code
331 AND zx_det.trx_id = zx_line.trx_id
332 AND zx_line.trx_id = xla_ent.source_id_int_1 -- Accounting Joins
333 AND zx_det.application_id = xla_ent.application_id
334 AND xla_ent.entity_code = 'AP_INVOICES'
335 AND xla_ent.entity_id = xla_event.entity_id
336 AND xla_event.event_id = xla_head.event_id
337 AND xla_head.ae_header_id = xla_line.ae_header_id
338 AND xla_dist.event_id = xla_event.event_id
339 AND acs.program_code = 'TAX_REP_LEDGER_PROCUREMENT'
340 AND acs.program_code = asd.program_code
341 AND asd.assignment_code = acs.assignment_code
342 AND asd.enabled_flag = 'Y'
343 AND acs.accounting_class_code = xla_line.accounting_class_code -- Accounting Joins Enda
344 AND xla_dist.tax_rec_nrec_dist_ref_id = zx_dist.rec_nrec_tax_dist_id
345 AND xla_dist.source_distribution_type = 'AP_INVOICE_DISTRIBUTIONS'
346 AND zx_line.tax_line_id = zx_dist.tax_line_id
347 AND zx_det.tax_reporting_flag = 'Y'
348 AND zx_line.tax_id = zx_tax.tax_id
349 AND zx_line.tax_rate_id = nvl(zx_rate.source_id, zx_rate.tax_rate_id)
350 AND zx_line.entity_code = p_entity_code
351 AND zx_line.trx_id = p_trx_id
352 AND zx_line.application_id = p_application_id
353 AND zx_line.event_class_code = p_event_class_code
354 AND zx_line.trx_line_id = p_trx_line_id
355 AND (zx_rate.source_id IS NOT NULL);
356
357 BEGIN
358 BEGIN
359
360 BEGIN
361 /* If calling report is AUDIT-AP Non TRL Extract(coomon extract) */
362
363 IF P_CALLING_REPORT = 'JGZZAPAE' OR P_REPORT_BY='Y' THEN
364 BEGIN
365 select cfgd.legal_entity_id,
366 cfg.ledger_id,
367 cfg.balancing_segment_value,
368 cfg.entity_identifier
369 INTO P_LEGAL_ENTITY_ID,
370 P_LEDGER_ID,
371 P_COMPANY,
372 l_entity_identifier
373 from jg_zz_vat_rep_entities cfg
374 ,jg_zz_vat_rep_entities cfgd
375 where cfg.vat_reporting_entity_id = P_VAT_REPORTING_ENTITY_ID
376 and ( ( cfg.entity_type_code = 'ACCOUNTING'
377 and cfg.mapping_vat_rep_entity_id = cfgd.vat_reporting_entity_id
378 )
379 or
380 ( cfg.entity_type_code = 'LEGAL'
381 and cfg.vat_reporting_entity_id = cfgd.vat_reporting_entity_id
382 )
383 );
384
385 SELECT period_start_date,
386 period_end_date
387 INTO l_start_date,
388 l_end_date
389 FROM JG_ZZ_VAT_REP_STATUS
390 WHERE VAT_REPORTING_ENTITY_ID=P_VAT_REPORTING_ENTITY_ID
391 AND TAX_CALENDAR_PERIOD= P_PERIOD
392 AND ROWNUM = 1;
393
394 l_period_start_date := l_start_date;
395 l_period_end_date := l_end_date;
396
397 EXCEPTION
398 WHEN OTHERS THEN
399 fnd_file.put_line(fnd_file.log,' An error occured while extracting the LE ,Ledger and BSV for entered Reporting Identifier. Error : ' || SUBSTR(SQLERRM, 1, 200));
400 END;
401 END IF;
402
403 fnd_file.put_line(fnd_file.log,'***Parameters and Locla Variables Values : ');
404 fnd_file.put_line(fnd_file.log,'P_VAT_REPORTING_ENTITY_ID :'||P_VAT_REPORTING_ENTITY_ID );
405 fnd_file.put_line(fnd_file.log,'P_LEGAL_ENTITY_ID :'||P_LEGAL_ENTITY_ID );
406 fnd_file.put_line(fnd_file.log,'P_LEDGER_ID :'||P_LEDGER_ID);
407 fnd_file.put_line(fnd_file.log,'P_COMPANY :'||P_COMPANY);
408 fnd_file.put_line(fnd_file.log,'l_period_start_date :'||l_period_start_date);
409 fnd_file.put_line(fnd_file.log,'l_period_end_date :'||l_period_end_date);
410
411 SELECT registration_number,legal_entity_name INTO l_tax_payer_id,l_legal_entity_name
412 FROM xle_registrations_v
413 WHERE legal_entity_id= P_LEGAL_ENTITY_ID
414 and legislative_category= 'INCOME_TAX'
415 and identifying = 'Y';
416
417 fnd_file.put_line(fnd_file.log,'l_tax_payer_id (registration Number) :'||l_tax_payer_id);
418 fnd_file.put_line(fnd_file.log,'legal_entity_name :'||l_legal_entity_name );
419
420 G_LE_ID := P_LEGAL_ENTITY_ID;
421
422 EXCEPTION
423 WHEN OTHERS THEN
424 fnd_file.put_line(fnd_file.log,' An error occured while extracting the Tax Payer ID for a LE. Error : ' || SUBSTR(SQLERRM, 1, 200));
425
426 END;
427
428
429 IF P_LEDGER_ID IS NULL THEN
430
431 FOR c_ledger_info IN C_FETCH_LEDGER_INFO
432 LOOP
433 l_ledger_id := c_ledger_info.ledger_id;
434 l_coaid := c_ledger_info.chart_of_accounts_id;
435 l_ledger_name := c_ledger_info.ledger_name;
436 l_functcurr := c_ledger_info.currency_code;
437 l_func_curr := c_ledger_info.currency_code;
438 END LOOP;
439
440 fnd_file.put_line(fnd_file.log,'l_ledger_id :'||l_ledger_id );
441 fnd_file.put_line(fnd_file.log,'l_coaid :'||l_coaid );
442 fnd_file.put_line(fnd_file.log,'l_ledger_name :'||l_ledger_name);
443 fnd_file.put_line(fnd_file.log,'l_functcurr :'||l_functcurr);
444
445 ELSE
446 BEGIN
447 GL_INFO.gl_get_ledger_info(P_LEDGER_ID,l_coaid,l_ledger_name,l_functcurr,errbuf);
448 l_func_curr := l_functcurr;
449
450 IF errbuf IS NOT NULL THEN
451
452 RAISE INVALID_LEDGER;
453
454 END IF;
455
456 fnd_file.put_line(fnd_file.log,'l_ledger_id :'||l_ledger_id );
457 fnd_file.put_line(fnd_file.log,'l_coaid :'||l_coaid );
458 fnd_file.put_line(fnd_file.log,'l_ledger_name :'||l_ledger_name);
459 fnd_file.put_line(fnd_file.log,'l_functcurr :'||l_functcurr);
460
461 EXCEPTION
462 WHEN INVALID_LEDGER THEN
463 fnd_file.put_line(fnd_file.log,errbuf);
464 END;
465 END IF;
466
467
468 IF P_REPORT_BY='N' THEN
469
470 BEGIN
471
472 FOR c_period_info IN C_FETCH_PERIOD
473 LOOP
474 l_start_date := c_period_info.start_date;
475 l_end_date := c_period_info.end_date;
476 l_period_start_date := l_start_date;
477 l_period_end_date := l_end_date;
478 END LOOP;
479
480 fnd_file.put_line(fnd_file.log,'l_start_date :'||l_start_date);
481 fnd_file.put_line(fnd_file.log,' l_end_date :'|| l_end_date );
482
483 EXCEPTION
484 WHEN OTHERS THEN
485 fnd_file.put_line(fnd_file.log,' An error occured while extracting the period start and end date. Error : ' || SUBSTR(SQLERRM, 1, 200));
486 END;
487 END IF;
488
489
490 fnd_file.put_line(fnd_file.log,'Calling jg_zz_common_pkg.company_detail');
491 jg_zz_common_pkg.company_detail(x_company_name => l_company_name
492 ,x_registration_number => l_registration_num
493 ,x_country => l_country
494 ,x_address1 => l_address_line_1
495 ,x_address2 => l_address_line_2
496 ,x_address3 => l_address_line_3
497 ,x_address4 => l_address_line_4
498 ,x_city => l_city
499 ,x_postal_code => l_postal_code
500 ,x_contact => l_contact_name
501 ,x_phone_number => l_phone_number
502 ,x_province => l_province
503 ,x_comm_number => l_comm_num
504 ,x_vat_reg_num => l_vat_reg_num
505 ,pn_legal_entity_id => G_LE_ID
506 ,p_vat_reporting_entity_id => P_VAT_REPORTING_ENTITY_ID);
507
508 fnd_file.put_line(fnd_file.log,'Company Information :');
509 fnd_file.put_line(fnd_file.log,'l_company_name :'||l_company_name);
510 fnd_file.put_line(fnd_file.log,'l_registration_num :'||l_registration_num);
511 fnd_file.put_line(fnd_file.log,'l_country :'||l_country);
512 fnd_file.put_line(fnd_file.log,'l_address_line_1 :'||l_address_line_1);
513 fnd_file.put_line(fnd_file.log,'l_address_line_3 :'||l_address_line_3);
514 fnd_file.put_line(fnd_file.log,'l_address_line_4 :'||l_address_line_4);
515 fnd_file.put_line(fnd_file.log,'l_city :'||l_city);
516
517
518
519
520 INSERT INTO JG_ZZ_VAT_TRX_GT
521 (
522 jg_info_n1
523 ,jg_info_v1
524 ,jg_info_v2
525 ,jg_info_v3
526 ,jg_info_v4 --l_tax_payer_id
527 ,jg_info_v5
528 ,jg_info_v6
529 ,jg_info_v7
530 ,jg_info_v8
531 ,jg_info_v9
532 ,jg_info_v10
533 ,jg_info_v11
534 ,jg_info_v12
535 ,jg_info_v13
536 ,jg_info_v14 --l_func_curr
537 -- ,jg_info_v15 --l_reporting_status
538 ,jg_info_v16
539 ,jg_info_d1
540 ,jg_info_d2
541 ,jg_info_v18
542 ,jg_info_v19
543 ,jg_info_v30
544 )
545 VALUES
546 (
547 G_LE_ID
548 ,l_company_name
549 ,l_legal_entity_name
550 ,l_registration_num
551 ,l_tax_payer_id
552 ,l_contact_name
553 ,l_address_line_1
554 ,l_address_line_2
555 ,l_address_line_3
556 ,l_address_line_4
557 ,l_city
558 ,l_country
559 ,l_phone_number
560 ,l_postal_code
561 ,l_func_curr
562 -- ,l_reporting_status
563 ,l_tax_regime
564 ,l_period_end_date
565 ,l_period_start_date
566 ,l_ledger_name
567 ,l_entity_identifier
568 ,'H'
569 );
570
571 EXCEPTION
572 WHEN OTHERS THEN
573 fnd_file.put_line(fnd_file.log,' An error occured while extracting the legal entity information. Error : ' || SUBSTR(SQLERRM, 1, 200));
574 END;
575
576 BEGIN
577
578 FOR c_curr IN c_curr_name (l_functcurr)
579 LOOP
580 l_curr_name := c_curr.name;
581 g_precision := c_curr.precision;
582 END LOOP;
583
584 fnd_file.put_line(fnd_file.log,'l_curr_name :'||l_curr_name);
585 fnd_file.put_line(fnd_file.log,'g_precision :'||g_precision);
586
587 G_CURR_NAME := l_curr_name;
588 G_LEDGER_CURR := l_functcurr;
589 G_STRUCT_NUM := l_coaid;
590 G_LEDGER_NAME := l_ledger_name;
591 EXCEPTION
592 WHEN OTHERS THEN
593 fnd_file.put_line(fnd_file.log,' An error occured while extracting the currency name and precision. Error : ' || SUBSTR(SQLERRM, 1, 200));
594 END;
595
596 BEGIN
597 FOR c_inv_less_tax IN c_inv_less_tax_flag
598 LOOP
599 G_disc_isinvlesstax_flag := c_inv_less_tax.disc_is_inv_less_tax_flag;
600 END LOOP;
601
602 fnd_file.put_line(fnd_file.log,'G_disc_isinvlesstax_flag :'||G_disc_isinvlesstax_flag);
603
604 EXCEPTION
605 WHEN OTHERS THEN
606 fnd_file.put_line(fnd_file.log,' An error occured while extracting the discount invoice less tax flag. Error : ' || SUBSTR(SQLERRM, 1, 200));
607 END;
608
609 fnd_file.put_line(fnd_file.log,'Calling Main Fucntion ....');
610
611 IF P_CALLING_REPORT = 'JEFRTXDC' THEN
612 BEGIN
613 fnd_file.put_line(fnd_file.log,' P_CALLING_REPORT = JEFRTXDC - Ture ');
614
615 --Query A1
616 --Bug 5337430 : Code added to pick up DEB/M invoices
617 --This logic is based on the following rules:
618 --1. Any validated DEB/M invoice will be picked up based on the accounting date
619 --2. A payment made to a DEB/M invoice does not impact a DEB/M invoice
620 --3. Invoice cancellation will result in an additional negative line being picked up
621 -- The invoice amount, taxable amount and recoverable tax amount will be
622 -- negative for this line
623 --4. Payment amount will be null for a DEB/M invoice
624 --5. A DEB/M prepayment invoice will be picked up if it satisfies conditions 1
625 INSERT
626 INTO jg_zz_vat_trx_gt(
627 jg_info_v2 --ven_name
628 , jg_info_v3 --ven_no
629 , jg_info_n18 --recoverable_tax_amount
630 , jg_info_n2 --rec_per
631 , jg_info_v5 --company
632 , jg_info_v6 --acc_no
633 , jg_info_n4 --tax_rate
634 , jg_info_v7 --tax_id
635 , jg_info_v8 --tax_type
636 , jg_info_v9 --inv_no
637 , jg_info_d1 --acc_date
638 , jg_info_n12 --l_payment_amt
639 , jg_info_n13 --l_txbl_amt
640 , jg_info_n19 --l_prt_inv_amt
641 , jg_info_v10 -- company_desc
642 , jg_info_v11 -- invoice status
643 , jg_info_v30)
644 SELECT
645 -- bug 8299240 - start
646 -- SUBSTR(ven.vendor_name, 1, 10) ven_name,
647 ven.vendor_name ven_name,
648 -- bug 8299240 - end
649 SUBSTR(ven.segment1, 1, 8) ven_no,
650 SUM(nvl(inv.exchange_rate,1)*zl.rec_nrec_tax_amt) recoverable_tax_amount,
651 zl.rec_nrec_rate rec_per,
652 get_balancing_segment(acctinfo.dist_code_combination_id) company,
653 get_accounting_segment(acctinfo.dist_code_combination_id) acc_no,
654 tax.percentage_rate tax_rate,
655 tax.tax_rate_id tax_id,
656 'DEB/M' tax_type,
657 -- bug 8299240 - start
658 -- SUBSTR(inv.invoice_num, 1, 10) inv_no,
659 inv.invoice_num inv_no,
660 -- bug 8299240 - end
661 dis.accounting_date acc_date,
662 NULL payment_amt,
663 nvl(inv.exchange_rate,1)*dis.amount txbl_amt,
664 decode(dis.parent_reversal_id, NULL, decode(nvl(dis.reversal_flag, 'N'), 'Y', nvl(inv.exchange_rate,1)*inv.cancelled_amount, nvl(inv.exchange_rate,1)*inv.invoice_amount),
665 -1 * nvl(inv.exchange_rate,1) * inv.cancelled_amount) prt_inv_amt,
666 NULL,
667 decode(dis.parent_reversal_id, NULL,' ','C'),
668 'JEFRTXDC'
669 FROM zx_rec_nrec_dist zl,
670 zx_rates_b tax,
671 ap_invoices inv,
672 ap_invoice_distributions dis,
673 po_vendors ven,
674 ap_invoice_distributions acctinfo
675 WHERE ((P_LEDGER_ID IS NULL AND P_COMPANY IS NULL AND inv.legal_entity_id = G_LE_ID)
676 OR (P_LEDGER_ID IS NOT NULL AND P_COMPANY IS NULL AND inv.set_of_books_id = P_LEDGER_ID)
677 OR (P_COMPANY IS NOT NULL AND inv.set_of_books_id = P_LEDGER_ID AND get_balancing_segment(dis.dist_code_combination_id) = P_COMPANY))
678 AND ven.vendor_id = inv.vendor_id
679 AND inv.invoice_id = dis.invoice_id
680 AND zl.rec_nrec_tax_dist_id = acctinfo.detail_tax_dist_id
681 AND inv.invoice_id = acctinfo.invoice_id
682 AND zl.trx_id = inv.invoice_id
683 AND zl.recoverable_flag = 'Y'
684 AND tax.tax_rate_id = zl.tax_rate_id
685 AND zl.entity_code = 'AP_INVOICES'
686 AND dis.line_type_lookup_code <> 'PREPAY'
687 AND zl.trx_line_dist_id = dis.invoice_distribution_id
688 AND dis.match_status_flag IS NOT NULL
689 AND dis.accounting_date BETWEEN l_start_date AND l_end_date
690 --bug10422464 - start
691 AND ((nvl(dis.reversal_flag,'N') = 'N' AND nvl(zl.reverse_flag,'N') = 'N')
692 or
693 (nvl(dis.reversal_flag,'N') = 'Y' AND nvl(zl.reverse_flag,'N') = 'Y'
694 and exists (select 'x'
695 from ap_invoice_distributions dis2
696 where dis2.invoice_id = inv.invoice_id
697 and dis2.accounting_date not between l_start_date and l_end_date)))
698 --bug10422464 - end
699 AND EXISTS ( SELECT 1 FROM zx_lines_det_factors zldf
700 WHERE SUBSTR(zldf.document_sub_type, LENGTH(zldf.document_sub_type) -4, 5) = 'DEB/M'
701 AND zldf.trx_id = inv.invoice_id )
702 GROUP BY zl.trx_line_dist_id,
703 tax.tax_rate_id,
704 tax.percentage_rate,
705 zl.rec_nrec_rate,
706 decode(dis.parent_reversal_id, NULL, decode(nvl(dis.reversal_flag, 'N'), 'Y', nvl(inv.exchange_rate,1)*inv.cancelled_amount, nvl(inv.exchange_rate,1)*inv.invoice_amount),
707 -1 * nvl(inv.exchange_rate,1) * inv.cancelled_amount),
708 -- bug 8299240 - start
709 -- SUBSTR(ven.vendor_name, 1, 10) ven_name,
710 ven.vendor_name,
711 -- bug 8299240 - end
712 SUBSTR(ven.segment1, 1, 8),
713 inv.invoice_type_lookup_code,
714 get_balancing_segment(acctinfo.dist_code_combination_id),
715 get_accounting_segment(acctinfo.dist_code_combination_id),
716 dis.accounting_date,
717 inv.invoice_num,
718 nvl(inv.exchange_rate,1)*dis.amount,
719 inv.cancelled_date,
720 decode(dis.parent_reversal_id, NULL,' ','C');
721
722 --Query A2
723 --Bug 5383153 : Code added to pick up application of prepayment with tax lines
724 --to DEB/M invoices
725 --This logic is based on the following rules:
726 --The following are the two types of prepayments possible:
727 --
728 --Type I. Prepayment without a tax line
729 --Type II. Prepayment with a tax line (and is of type DEB/M)
730 --
731 --1. Prepayments of Type I will not be shown in the report irrespective of
732 -- whether type are paid or not
733 --2. The application of prepayment of Type I on DEB/M invoices will not impact
734 -- the way the invoice is displayed on the report
735 --3. Prepayments of Type II will be shown in the report based on their invoice
736 -- accounting date. This case is handled by Query A1
737 --4. When Prepayments of Type II are applied to a DEB/M invoice, A negative
738 -- line will appear on the invoice with amounts equal to the applied prepayment
739 -- amounts. This is necessary as tax on prepayment has already be reported (in Case 3).
740 -- Case 4 is handled in this query
741 INSERT
742 INTO jg_zz_vat_trx_gt(
743 jg_info_v2 --ven_name
744 , jg_info_v3 --ven_no
745 , jg_info_n18 --recoverable_tax_amount
746 , jg_info_n2 --rec_per
747 , jg_info_v5 --company
748 , jg_info_v6 --acc_no
749 , jg_info_n4 --tax_rate
750 , jg_info_v7 --tax_id
751 , jg_info_v8 --tax_type
752 , jg_info_v9 --inv_no
753 , jg_info_d1 --acc_date
754 , jg_info_n12 --l_payment_amt
755 , jg_info_n13 --l_txbl_amt
756 , jg_info_n19 --l_prt_inv_amt
757 , jg_info_v10 -- company_desc
758 , jg_info_v11 -- invoice status
759 , jg_info_v30)
760 SELECT
761 -- bug 8299240 - start
762 -- SUBSTR(ven.vendor_name, 1, 10) ven_name,
763 ven.vendor_name ven_name,
764 -- bug 8299240 - end
765 SUBSTR(ven.segment1, 1, 8) ven_no,
766 SUM(nvl(inv.exchange_rate,1)*zl.rec_nrec_tax_amt) recoverable_tax_amount,
767 zl.rec_nrec_rate rec_per,
768 get_balancing_segment(acctinfo.dist_code_combination_id) company,
769 get_accounting_segment(acctinfo.dist_code_combination_id) acc_no,
770 tax.percentage_rate tax_rate,
771 tax.tax_rate_id tax_id,
772 'DEB/M' tax_type,
773 -- bug 8299240 - start
774 -- SUBSTR(inv.invoice_num, 1, 10) inv_no,
775 inv.invoice_num inv_no,
776 -- bug 8299240 - end
777 dis.accounting_date acc_date,
778 NULL payment_amt,
779 nvl(inv.exchange_rate,1)*dis.amount txbl_amt,
780 decode(inv.cancelled_date,NULL,nvl(inv.exchange_rate,1)*inv.invoice_amount, nvl(inv.exchange_rate,1)*inv.cancelled_amount) prt_inv_amt,
781 NULL,
782 'P',
783 'JEFRTXDC'
784 FROM zx_rec_nrec_dist zl,
785 zx_rates_b tax,
786 ap_invoices inv,
787 ap_invoice_distributions dis,
788 po_vendors ven,
789 ap_invoice_distributions acctinfo
790 WHERE ((P_LEDGER_ID IS NULL AND P_COMPANY IS NULL AND inv.legal_entity_id = G_LE_ID)
791 OR (P_LEDGER_ID IS NOT NULL AND P_COMPANY IS NULL AND inv.set_of_books_id = P_LEDGER_ID)
792 OR (P_COMPANY IS NOT NULL AND inv.set_of_books_id = P_LEDGER_ID AND get_balancing_segment(dis.dist_code_combination_id) = P_COMPANY))
793 AND ven.vendor_id = inv.vendor_id
794 AND inv.invoice_id = dis.invoice_id
795 AND zl.rec_nrec_tax_dist_id = acctinfo.detail_tax_dist_id
796 AND inv.invoice_id = acctinfo.invoice_id
797 AND zl.trx_id = inv.invoice_id
798 AND zl.recoverable_flag = 'Y'
799 AND tax.tax_rate_id = zl.tax_rate_id
800 AND zl.entity_code = 'AP_INVOICES'
801 AND dis.line_type_lookup_code = 'PREPAY'
802 AND zl.trx_line_dist_id = dis.invoice_distribution_id
803 AND dis.match_status_flag IS NOT NULL
804 AND (dis.accounting_date BETWEEN l_start_date AND l_end_date)
805 AND EXISTS ( SELECT 1 FROM zx_lines_det_factors zldf
806 WHERE SUBSTR(zldf.document_sub_type, LENGTH(zldf.document_sub_type) -4, 5) = 'DEB/M'
807 AND zldf.trx_id = inv.invoice_id )
808 GROUP BY zl.trx_line_dist_id,
809 tax.tax_rate_id,
810 tax.percentage_rate,
811 zl.rec_nrec_rate,
812 decode(inv.cancelled_date,NULL,nvl(inv.exchange_rate,1)*inv.invoice_amount, nvl(inv.exchange_rate,1)*inv.cancelled_amount),
813 -- bug 8299240 - start
814 -- SUBSTR(ven.vendor_name, 1, 10) ven_name,
815 ven.vendor_name ,
816 -- bug 8299240 - end
817 SUBSTR(ven.segment1, 1, 8),
818 inv.invoice_type_lookup_code,
819 get_balancing_segment(acctinfo.dist_code_combination_id),
820 get_accounting_segment(acctinfo.dist_code_combination_id),
821 dis.accounting_date,
822 inv.invoice_num,
823 nvl(inv.exchange_rate,1)*dis.amount,
824 inv.cancelled_date,
825 inv.invoice_amount,
826 inv.cancelled_amount;
827
828 --Query B1
829 --Bug 5383171 : Query to pick up all CRE/M invoices that have been paid
830 --This logic is based on the following rules:
831 --1. A CRE/M invoice that is not paid will not be picked up
832 --2. CRE/M invoices will be picked based on their future payment date
833 -- (or payment date, if the future payment date is null)
834 --3. If a CRE/M invoice is partially paid, then the taxable amount and
835 -- the recoverable tax amount will be prorated using this formula
836 -- Taxable amount = (Original Taxable Amount)*(Payment Amount/Invoice Amount)
837 -- Recoverable tax amount = (Original Recoveral tax amount)*(Payment Amount/Invoice Amount)
838 --4. A CRE/M invoice that does not have a tax line will not be picked
839 --5. A CRE/M prepayment invoice will be picked up if it satisfies conditions 1 to 4
840 --6. Invoice cancellation does not affect a CRE/M invoice
841 --7. Voiding a payment will result in an additional negative line being
842 -- picked up for the invoice. The payment amount, taxable amount and
843 -- recoverable tax amount will be negative for this line
844 INSERT
845 INTO jg_zz_vat_trx_gt(
846 jg_info_v2 --ven_name
847 , jg_info_v3 --ven_no
848 , jg_info_n18 --recoverable_tax_amount
849 , jg_info_n2 --rec_per
850 , jg_info_v5 --company
851 , jg_info_v6 --acc_no
852 , jg_info_n4 --tax_rate
853 , jg_info_v7 --tax_id
854 , jg_info_v8 --tax_type
855 , jg_info_v9 --inv_no
856 , jg_info_d1 --acc_date
857 , jg_info_n12 --l_payment_amt
858 , jg_info_n13 --l_txbl_amt
859 , jg_info_n19 --l_prt_inv_amt
860 , jg_info_v10 -- company_desc
861 , jg_info_v11 -- invoice status
862 , jg_info_v30)
863 SELECT
864 -- bug 8299240 - start
865 -- SUBSTR(ven.vendor_name, 1, 10) ven_name,
866 ven.vendor_name ven_name,
867 -- bug 8299240 - end
868 SUBSTR(ven.segment1, 1, 8) ven_no,
869 SUM(nvl(inv.exchange_rate,1)*zl.rec_nrec_tax_amt) * ((nvl(aip.exchange_rate,1)*aip.amount + nvl(aip.exchange_rate,1) * nvl(aip.discount_taken,0))
870 /nullif(decode(inv.cancelled_date,NULL,nvl(inv.exchange_rate,1)*inv.invoice_amount, nvl(inv.exchange_rate,1)*inv.cancelled_amount),0)) recoverable_tax_amount, --bug 13596406
871 zl.rec_nrec_rate rec_per,
872 get_balancing_segment(acctinfo.dist_code_combination_id) company,
873 get_accounting_segment(decode(zl.def_rec_settlement_option_code,'DEFERRED', ACCOUNTS.tax_account_ccid, acctinfo.dist_code_combination_id)) acc_no,
874 tax.percentage_rate tax_rate,
875 tax.tax_rate_id tax_id,
876 'CRE/M' tax_type,
877 -- bug 8299240 - start
878 -- SUBSTR(inv.invoice_num, 1, 10) inv_no,
879 inv.invoice_num inv_no,
880 -- bug 8299240 - end
881 decode(chk.future_pay_due_date, NULL, aip.accounting_date, decode(sign(aip.accounting_date-chk.future_pay_due_date),1, aip.accounting_date, chk.future_pay_due_date)) acc_date,
882 (nvl(aip.exchange_rate,1)*aip.amount + nvl(aip.exchange_rate,1) * nvl(aip.discount_taken,0)) payment_amt,
883 nvl(inv.exchange_rate,1)*nvl(dis.total_dist_amount,dis.amount) * ((nvl(aip.exchange_rate,1)*aip.amount + nvl(aip.exchange_rate,1) * nvl(aip.discount_taken,0))
884 /nullif(decode(inv.cancelled_date,NULL,nvl(inv.exchange_rate,1)*inv.invoice_amount, nvl(inv.exchange_rate,1)*inv.cancelled_amount),0)) txbl_amt, --bug 13596406
885 decode(inv.cancelled_date,NULL,nvl(inv.exchange_rate,1)*inv.invoice_amount, nvl(inv.exchange_rate,1)*inv.cancelled_amount) prt_inv_amt,
886 NULL,
887 decode(aip.reversal_inv_pmt_id,NULL,' ','V'),
888 'JEFRTXDC'
889 FROM zx_rates_b tax,
890 ap_invoices inv,
891 ap_invoice_distributions dis,
892 po_vendors ven,
893 ap_invoice_payments aip,
894 ap_checks_all chk,
895 ap_invoice_distributions acctinfo,
896 zx_rec_nrec_dist zl LEFT OUTER JOIN zx_accounts ACCOUNTS
897 ON (accounts.TAX_ACCOUNT_ENTITY_ID =
898 nvl(zl.ACCOUNT_SOURCE_TAX_RATE_ID, zl.TAX_RATE_ID)
899 AND accounts.TAX_ACCOUNT_ENTITY_CODE = 'RATES'
900 AND accounts.INTERNAL_ORGANIZATION_ID = zl.INTERNAL_ORGANIZATION_ID
901 AND accounts.LEDGER_ID = zl.LEDGER_ID )
902 WHERE ((p_ledger_id IS NULL
903 AND p_company IS NULL
904 AND inv.legal_entity_id = g_le_id) OR(p_ledger_id IS NOT NULL
905 AND p_company IS NULL
906 AND inv.set_of_books_id = p_ledger_id) OR(p_company IS NOT NULL
907 AND inv.set_of_books_id = p_ledger_id
908 AND get_balancing_segment(dis.dist_code_combination_id) = p_company))
909 AND ven.vendor_id = inv.vendor_id
910 AND inv.invoice_id = dis.invoice_id
911 AND aip.invoice_id = inv.invoice_id
912 AND zl.rec_nrec_tax_dist_id = acctinfo.detail_tax_dist_id
913 AND inv.invoice_id = acctinfo.invoice_id
914 AND chk.check_id = aip.check_id
915 AND zl.trx_id = inv.invoice_id
916 AND zl.recoverable_flag = 'Y'
917 AND tax.tax_rate_id = zl.tax_rate_id
918 AND zl.entity_code = 'AP_INVOICES'
919 AND zl.trx_line_dist_id = dis.invoice_distribution_id
920 AND dis.line_type_lookup_code <> 'PREPAY'
921 AND dis.match_status_flag IS NOT NULL
922 AND decode(chk.future_pay_due_date, NULL, aip.accounting_date, decode(sign(aip.accounting_date-chk.future_pay_due_date),1, aip.accounting_date, chk.future_pay_due_date)) BETWEEN l_start_date AND l_end_date
923 -- AND dis.parent_reversal_id IS NULL --Bug13717126
924 --bug10422464 - start
925 AND ((nvl(dis.reversal_flag,'N') = 'N' AND nvl(zl.reverse_flag,'N') = 'N')
926 or
927 (nvl(dis.reversal_flag,'N') = 'Y' AND nvl(zl.reverse_flag,'N') = 'Y'
928 and exists (select 'x'
929 from ap_invoice_distributions dis2
930 where dis2.invoice_id = inv.invoice_id
931 and dis2.accounting_date not between l_start_date and l_end_date)))
932 --bug10422464 - end
933 AND EXISTS ( SELECT 1 FROM zx_lines_det_factors zldf WHERE
934 SUBSTR(zldf.document_sub_type, LENGTH(zldf.document_sub_type) -4, 5) = 'CRE/M'
935 AND zldf.trx_id = inv.invoice_id )
936 GROUP BY zl.trx_line_dist_id,
937 tax.tax_rate_id,
938 tax.percentage_rate,
939 zl.rec_nrec_rate,
940 inv.cancelled_amount,
941 -- bug 8299240 - start
942 -- SUBSTR(ven.vendor_name, 1, 10) ven_name,
943 ven.vendor_name ,
944 -- bug 8299240 - end
945 SUBSTR(ven.segment1, 1, 8),
946 inv.invoice_type_lookup_code,
947 get_balancing_segment(acctinfo.dist_code_combination_id),
948 get_accounting_segment(decode(zl.def_rec_settlement_option_code,'DEFERRED', ACCOUNTS.tax_account_ccid, acctinfo.dist_code_combination_id)),
949 aip.accounting_date,
950 inv.invoice_num,
951 dis.total_dist_amount,
952 dis.amount,
953 inv.cancelled_date,
954 aip.amount,
955 aip.discount_taken,
956 inv.invoice_amount,
957 chk.future_pay_due_date,
958 decode(aip.reversal_inv_pmt_id,NULL,' ','V'),
959 inv.exchange_rate,
960 aip.exchange_rate;
961
962 --Query B2
963 --Bug 5383181 : Query to pick up all prepayments with tax lines that have
964 --been applied to a CRE/M invoice
965 --Prepayments with tax lines will be handled as follows:
966 --1. Prepayments of this type will be shown in the report based on their payment
967 -- due date (or payment date, if payment due date is null). This is handled
968 -- in the previous query
969 --2. When such prepayments are applied to a CRE/M invoice, a postive
970 -- line will appear on the invoice with tax amount equal to the prepayment tax
971 -- amount. A corrosponding negative line will also appear in order to
972 -- compensate the positive line. The sum of payment, taxable and recoverable
973 -- tax amounts of both the lines will be zero. This is necessary as the tax on
974 -- prepayment has already be reported in case 1 and the lines are
975 -- displayed on the report for informational purposes only.
976 --
977 -- The first insert is for the positive line, the second insert
978 -- is for the negative line
979 INSERT
980 INTO jg_zz_vat_trx_gt(
981 jg_info_v2 --ven_name
982 , jg_info_v3 --ven_no
983 , jg_info_n18 --recoverable_tax_amount
984 , jg_info_n2 --rec_per
985 , jg_info_v5 --company
986 , jg_info_v6 --acc_no
987 , jg_info_n4 --tax_rate
988 , jg_info_v7 --tax_id
989 , jg_info_v8 --tax_type
990 , jg_info_v9 --inv_no
991 , jg_info_d1 --acc_date
992 , jg_info_n12 --l_payment_amt
993 , jg_info_n13 --l_txbl_amt
994 , jg_info_n19 --l_prt_inv_amt
995 , jg_info_v10 -- company_desc
996 , jg_info_v11 -- invoice status
997 , jg_info_v30)
998 SELECT
999 -- bug 8299240 - start
1000 -- SUBSTR(ven.vendor_name, 1, 10) ven_name,
1001 ven.vendor_name ven_name,
1002 -- bug 8299240 - end
1003 SUBSTR(ven.segment1, 1, 8) ven_no,
1004 SUM(nvl(inv.exchange_rate,1)*zl.rec_nrec_tax_amt)*-1 recoverable_tax_amount,
1005 zl.rec_nrec_rate rec_per,
1006 get_balancing_segment(acctinfo.dist_code_combination_id) company,
1007 get_accounting_segment(decode(zl.def_rec_settlement_option_code,'DEFERRED', ACCOUNTS.tax_account_ccid, acctinfo.dist_code_combination_id)) acc_no,
1008 tax.percentage_rate tax_rate,
1009 tax.tax_rate_id tax_id,
1010 'CRE/M' tax_type,
1011 -- bug 8299240 - start
1012 -- SUBSTR(inv.invoice_num, 1, 10) inv_no,
1013 inv.invoice_num inv_no,
1014 -- bug 8299240 - end
1015 dis.accounting_date acc_date,
1016 (SUM(nvl(inv.exchange_rate,1)*zl.rec_nrec_tax_amt) + SUM(nvl(inv.exchange_rate,1)*nrec.rec_nrec_tax_amt) + nvl(inv.exchange_rate,1)*dis.amount)*-1 payment_amt,
1017 nvl(inv.exchange_rate,1)*dis.amount*-1 txbl_amt,
1018 decode(inv.cancelled_date,NULL,nvl(inv.exchange_rate,1)*inv.invoice_amount, nvl(inv.exchange_rate,1)*inv.cancelled_amount) prt_inv_amt,
1019 NULL,
1020 'P',
1021 'JEFRTXDC'
1022 FROM zx_rates_b tax,
1023 ap_invoices inv,
1024 ap_invoice_distributions dis,
1025 po_vendors ven,
1026 zx_rec_nrec_dist nrec,
1027 ap_invoice_distributions acctinfo,
1028 zx_rec_nrec_dist zl LEFT OUTER JOIN zx_accounts ACCOUNTS
1029 ON (accounts.TAX_ACCOUNT_ENTITY_ID =
1030 nvl(zl.ACCOUNT_SOURCE_TAX_RATE_ID, zl.TAX_RATE_ID)
1031 AND accounts.TAX_ACCOUNT_ENTITY_CODE = 'RATES'
1032 AND accounts.INTERNAL_ORGANIZATION_ID = zl.INTERNAL_ORGANIZATION_ID
1033 AND accounts.LEDGER_ID = zl.LEDGER_ID )
1034 WHERE ((P_LEDGER_ID IS NULL AND P_COMPANY IS NULL AND inv.legal_entity_id = G_LE_ID)
1035 OR (P_LEDGER_ID IS NOT NULL AND P_COMPANY IS NULL AND inv.set_of_books_id = P_LEDGER_ID)
1036 OR (P_COMPANY IS NOT NULL AND inv.set_of_books_id = P_LEDGER_ID AND get_balancing_segment(dis.dist_code_combination_id) = P_COMPANY))
1037 AND ven.vendor_id = inv.vendor_id
1038 AND inv.invoice_id = dis.invoice_id
1039 AND zl.rec_nrec_tax_dist_id = acctinfo.detail_tax_dist_id
1040 AND inv.invoice_id = acctinfo.invoice_id
1041 AND zl.trx_id = inv.invoice_id
1042 AND zl.recoverable_flag = 'Y'
1043 AND tax.tax_rate_id = zl.tax_rate_id
1044 AND zl.entity_code = 'AP_INVOICES'
1045 AND zl.trx_line_dist_id = dis.invoice_distribution_id
1046 AND dis.match_status_flag IS NOT NULL
1047 AND dis.accounting_date BETWEEN l_start_date AND l_end_date
1048 AND dis.line_type_lookup_code = 'PREPAY'
1049 AND nrec.entity_code = 'AP_INVOICES'
1050 AND nrec.trx_line_dist_id = dis.invoice_distribution_id
1051 AND nrec.trx_id = inv.invoice_id
1052 AND nrec.recoverable_flag = 'N'
1053 AND EXISTS ( SELECT 1 FROM zx_lines_det_factors zldf WHERE
1054 SUBSTR(zldf.document_sub_type, LENGTH(zldf.document_sub_type) -4, 5) = 'CRE/M'
1055 AND zldf.trx_id = inv.invoice_id )
1056 GROUP BY zl.trx_line_dist_id,
1057 tax.tax_rate_id,
1058 tax.percentage_rate,
1059 zl.rec_nrec_rate,
1060 decode(inv.cancelled_date,NULL,nvl(inv.exchange_rate,1)*inv.invoice_amount, nvl(inv.exchange_rate,1)*inv.cancelled_amount),
1061 -- bug 8299240 - start
1062 -- SUBSTR(ven.vendor_name, 1, 10) ven_name,
1063 ven.vendor_name ,
1064 -- bug 8299240 - end
1065 SUBSTR(ven.segment1, 1, 8),
1066 inv.invoice_type_lookup_code,
1067 get_balancing_segment(acctinfo.dist_code_combination_id),
1068 get_accounting_segment(decode(zl.def_rec_settlement_option_code,'DEFERRED', ACCOUNTS.tax_account_ccid, acctinfo.dist_code_combination_id)),
1069 dis.accounting_date,
1070 inv.invoice_num,
1071 dis.amount,
1072 inv.cancelled_date,
1073 inv.exchange_rate;
1074
1075
1076
1077 INSERT
1078 INTO jg_zz_vat_trx_gt(
1079 jg_info_v2 --ven_name
1080 , jg_info_v3 --ven_no
1081 , jg_info_n18 --recoverable_tax_amount
1082 , jg_info_n2 --rec_per
1083 , jg_info_v5 --company
1084 , jg_info_v6 --acc_no
1085 , jg_info_n4 --tax_rate
1086 , jg_info_v7 --tax_id
1087 , jg_info_v8 --tax_type
1088 , jg_info_v9 --inv_no
1089 , jg_info_d1 --acc_date
1090 , jg_info_n12 --l_payment_amt
1091 , jg_info_n13 --l_txbl_amt
1092 , jg_info_n19 --l_prt_inv_amt
1093 , jg_info_v10 -- company_desc
1094 , jg_info_v11 -- invoice status
1095 , jg_info_v30)
1096 SELECT
1097 -- bug 8299240 - start
1098 -- SUBSTR(ven.vendor_name, 1, 10) ven_name,
1099 ven.vendor_name ven_name,
1100 -- bug 8299240 - end
1101 SUBSTR(ven.segment1, 1, 8) ven_no,
1102 SUM(nvl(inv.exchange_rate,1)*zl.rec_nrec_tax_amt) recoverable_tax_amount,
1103 zl.rec_nrec_rate rec_per,
1104 get_balancing_segment(acctinfo.dist_code_combination_id) company,
1105 get_accounting_segment(decode(zl.def_rec_settlement_option_code,'DEFERRED', ACCOUNTS.tax_account_ccid, acctinfo.dist_code_combination_id)) acc_no,
1106 tax.percentage_rate tax_rate,
1107 tax.tax_rate_id tax_id,
1108 'CRE/M' tax_type,
1109 -- bug 8299240 - start
1110 -- SUBSTR(inv.invoice_num, 1, 10) inv_no,
1111 inv.invoice_num inv_no,
1112 -- bug 8299240 - end
1113 dis.accounting_date acc_date,
1114 SUM(nvl(inv.exchange_rate,1)*zl.rec_nrec_tax_amt) + SUM(nvl(inv.exchange_rate,1)*nrec.rec_nrec_tax_amt) + nvl(inv.exchange_rate,1)*dis.amount payment_amt,
1115 nvl(inv.exchange_rate,1)*dis.amount txbl_amt,
1116 decode(inv.cancelled_date,NULL,nvl(inv.exchange_rate,1)*inv.invoice_amount, nvl(inv.exchange_rate,1)*inv.cancelled_amount) prt_inv_amt,
1117 NULL,
1118 'P',
1119 'JEFRTXDC'
1120 FROM zx_rates_b tax,
1121 ap_invoices inv,
1122 ap_invoice_distributions dis,
1123 po_vendors ven,
1124 zx_rec_nrec_dist nrec,
1125 ap_invoice_distributions acctinfo,
1126 zx_rec_nrec_dist zl LEFT OUTER JOIN zx_accounts ACCOUNTS
1127 ON (accounts.TAX_ACCOUNT_ENTITY_ID =
1128 nvl(zl.ACCOUNT_SOURCE_TAX_RATE_ID, zl.TAX_RATE_ID)
1129 AND accounts.TAX_ACCOUNT_ENTITY_CODE = 'RATES'
1130 AND accounts.INTERNAL_ORGANIZATION_ID = zl.INTERNAL_ORGANIZATION_ID
1131 AND accounts.LEDGER_ID = zl.LEDGER_ID )
1132 WHERE ((P_LEDGER_ID IS NULL AND P_COMPANY IS NULL AND inv.legal_entity_id = G_LE_ID)
1133 OR (P_LEDGER_ID IS NOT NULL AND P_COMPANY IS NULL AND inv.set_of_books_id = P_LEDGER_ID)
1134 OR (P_COMPANY IS NOT NULL AND inv.set_of_books_id = P_LEDGER_ID AND get_balancing_segment(dis.dist_code_combination_id) = P_COMPANY))
1135 AND ven.vendor_id = inv.vendor_id
1136 AND inv.invoice_id = dis.invoice_id
1137 AND zl.rec_nrec_tax_dist_id = acctinfo.detail_tax_dist_id
1138 AND inv.invoice_id = acctinfo.invoice_id
1139 AND zl.trx_id = inv.invoice_id
1140 AND zl.recoverable_flag = 'Y'
1141 AND tax.tax_rate_id = zl.tax_rate_id
1142 AND zl.entity_code = 'AP_INVOICES'
1143 AND zl.trx_line_dist_id = dis.invoice_distribution_id
1144 AND dis.match_status_flag IS NOT NULL
1145 AND dis.accounting_date BETWEEN l_start_date AND l_end_date
1146 AND dis.line_type_lookup_code = 'PREPAY'
1147 AND nrec.entity_code = 'AP_INVOICES'
1148 AND nrec.trx_line_dist_id = dis.invoice_distribution_id
1149 AND nrec.trx_id = inv.invoice_id
1150 AND nrec.recoverable_flag = 'N'
1151 AND EXISTS ( SELECT 1 FROM zx_lines_det_factors zldf WHERE
1152 SUBSTR(zldf.document_sub_type, LENGTH(zldf.document_sub_type) -4, 5) = 'CRE/M'
1153 AND zldf.trx_id = inv.invoice_id )
1154 GROUP BY zl.trx_line_dist_id,
1155 tax.tax_rate_id,
1156 tax.percentage_rate,
1157 zl.rec_nrec_rate,
1158 decode(inv.cancelled_date,NULL,nvl(inv.exchange_rate,1)*inv.invoice_amount, nvl(inv.exchange_rate,1)*inv.cancelled_amount),
1159 -- bug 8299240 - start
1160 -- SUBSTR(ven.vendor_name, 1, 10) ven_name,
1161 ven.vendor_name ,
1162 -- bug 8299240 - end
1163 SUBSTR(ven.segment1, 1, 8),
1164 inv.invoice_type_lookup_code,
1165 get_balancing_segment(acctinfo.dist_code_combination_id),
1166 get_accounting_segment(decode(zl.def_rec_settlement_option_code,'DEFERRED', ACCOUNTS.tax_account_ccid, acctinfo.dist_code_combination_id)),
1167 dis.accounting_date,
1168 inv.invoice_num,
1169 dis.amount,
1170 inv.cancelled_date,
1171 inv.exchange_rate;
1172
1173
1174 --Query B3
1175 --Bug 5383181 : Query to pick up all prepayments without a tax line that have
1176 --been applied to a CRE/M invoice
1177 --Prepayments without tax lines will be handled as follows:
1178 --1. Prepayments of this type will not be shown in the report irrespective
1179 -- of whether they are paid or not
1180 --2. The application of such prepayments on CRE/M invoices will be consider
1181 -- as a normal payment. In such a case the recoveral tax amount and the
1182 -- taxable amount will be prorated based on prepayment amount
1183 -- Taxable amount = (Original Taxable Amount)*(prepayment Amount/Invoice Amount)
1184 -- Recoverable tax amount = (Original Recoveral tax amount)*(prepayment Amount/Invoice Amount)
1185 INSERT
1186 INTO jg_zz_vat_trx_gt(
1187 jg_info_v2 --ven_name
1188 , jg_info_v3 --ven_no
1189 , jg_info_n18 --recoverable_tax_amount
1190 , jg_info_n2 --rec_per
1191 , jg_info_v5 --company
1192 , jg_info_v6 --acc_no
1193 , jg_info_n4 --tax_rate
1194 , jg_info_v7 --tax_id
1195 , jg_info_v8 --tax_type
1196 , jg_info_v9 --inv_no
1197 , jg_info_d1 --acc_date
1198 , jg_info_n12 --l_payment_amt
1199 , jg_info_n13 --l_txbl_amt
1200 , jg_info_n19 --l_prt_inv_amt
1201 , jg_info_v10 -- company_desc
1202 , jg_info_v11 -- invoice status
1203 , jg_info_v30)
1204 SELECT
1205 -- bug 8299240 - start
1206 -- SUBSTR(ven.vendor_name, 1, 10) ven_name,
1207 ven.vendor_name ven_name,
1208 -- bug 8299240 - end
1209 SUBSTR(ven.segment1, 1, 8) ven_no,
1210 SUM(nvl(inv.exchange_rate,1)*zl.rec_nrec_tax_amt) * ((-1 * nvl(inv.exchange_rate,1) * pre.amount)
1211 /decode(inv.cancelled_date,NULL,nvl(inv.exchange_rate,1)*inv.invoice_amount, nvl(inv.exchange_rate,1)*inv.cancelled_amount)) recoverable_tax_amount,
1212 zl.rec_nrec_rate rec_per,
1213 get_balancing_segment(acctinfo.dist_code_combination_id) company,
1214 get_accounting_segment(decode(zl.def_rec_settlement_option_code,'DEFERRED', ACCOUNTS.tax_account_ccid, acctinfo.dist_code_combination_id)) acc_no,
1215 tax.percentage_rate tax_rate,
1216 tax.tax_rate_id tax_id,
1217 'CRE/M' tax_type,
1218 -- bug 8299240 - start
1219 -- SUBSTR(inv.invoice_num, 1, 10) inv_no,
1220 inv.invoice_num inv_no,
1221 -- bug 8299240 - end
1222 pre.accounting_date acc_date,
1223 -1 * nvl(inv.exchange_rate,1) * pre.amount payment_amt,
1224 nvl(inv.exchange_rate,1) * dis.amount * ((-1 * nvl(inv.exchange_rate,1) * pre.amount)
1225 /decode(inv.cancelled_date,NULL,nvl(inv.exchange_rate,1)*inv.invoice_amount, nvl(inv.exchange_rate,1)*inv.cancelled_amount)) txbl_amt,
1226 decode(inv.cancelled_date,NULL,nvl(inv.exchange_rate,1)*inv.invoice_amount, nvl(inv.exchange_rate,1)*inv.cancelled_amount) prt_inv_amt,
1227 NULL,
1228 'PP',
1229 'JEFRTXDC'
1230 FROM zx_rates_b tax,
1231 ap_invoices inv,
1232 ap_invoice_distributions dis,
1233 po_vendors ven,
1234 ap_invoice_distributions pre,
1235 ap_invoice_distributions acctinfo,
1236 zx_rec_nrec_dist zl LEFT OUTER JOIN zx_accounts ACCOUNTS
1237 ON (accounts.TAX_ACCOUNT_ENTITY_ID =
1238 nvl(zl.ACCOUNT_SOURCE_TAX_RATE_ID, zl.TAX_RATE_ID)
1239 AND accounts.TAX_ACCOUNT_ENTITY_CODE = 'RATES'
1240 AND accounts.INTERNAL_ORGANIZATION_ID = zl.INTERNAL_ORGANIZATION_ID
1241 AND accounts.LEDGER_ID = zl.LEDGER_ID )
1242 WHERE ((p_ledger_id IS NULL
1243 AND p_company IS NULL
1244 AND inv.legal_entity_id = g_le_id) OR(p_ledger_id IS NOT NULL
1245 AND p_company IS NULL
1246 AND inv.set_of_books_id = p_ledger_id) OR(p_company IS NOT NULL
1247 AND inv.set_of_books_id = p_ledger_id
1248 AND get_balancing_segment(dis.dist_code_combination_id) = p_company))
1249 AND ven.vendor_id = inv.vendor_id
1250 AND inv.invoice_id = dis.invoice_id
1251 AND inv.invoice_id = pre.invoice_id
1252 AND zl.rec_nrec_tax_dist_id = acctinfo.detail_tax_dist_id
1253 AND inv.invoice_id = acctinfo.invoice_id
1254 AND zl.trx_id = inv.invoice_id
1255 AND zl.recoverable_flag = 'Y'
1256 AND tax.tax_rate_id = zl.tax_rate_id
1257 AND zl.entity_code = 'AP_INVOICES'
1258 AND zl.trx_line_dist_id = dis.invoice_distribution_id
1259 AND dis.line_type_lookup_code = 'ITEM'
1260 AND dis.match_status_flag IS NOT NULL
1261 AND pre.line_type_lookup_code = 'PREPAY'
1262 AND pre.match_status_flag IS NOT NULL
1263 AND NOT EXISTS ( SELECT 1 FROM zx_rec_nrec_dist zlp where
1264 zlp.trx_id = inv.invoice_id
1265 AND zlp.entity_code = 'AP_INVOICES'
1266 AND zlp.trx_line_dist_id = pre.invoice_distribution_id )
1267 AND pre.accounting_date BETWEEN l_start_date AND l_end_date
1268 AND dis.parent_reversal_id IS NULL
1269 --bug10422464 - start
1270 AND ((nvl(dis.reversal_flag,'N') = 'N' AND nvl(zl.reverse_flag,'N') = 'N')
1271 or
1272 (nvl(dis.reversal_flag,'N') = 'Y' AND nvl(zl.reverse_flag,'N') = 'Y'
1273 and exists (select 'x'
1274 from ap_invoice_distributions dis2
1275 where dis2.invoice_id = inv.invoice_id
1276 and dis2.accounting_date not between l_start_date and l_end_date)))
1277 --bug10422464 - end
1278 AND EXISTS ( SELECT 1 FROM zx_lines_det_factors zldf WHERE
1279 SUBSTR(zldf.document_sub_type, LENGTH(zldf.document_sub_type) -4, 5) = 'CRE/M'
1280 AND zldf.trx_id = inv.invoice_id )
1281 GROUP BY zl.trx_line_dist_id,
1282 tax.tax_rate_id,
1283 tax.percentage_rate,
1284 zl.rec_nrec_rate,
1285 inv.cancelled_amount,
1286 -- bug 8299240 - start
1287 -- SUBSTR(ven.vendor_name, 1, 10) ven_name,
1288 ven.vendor_name ,
1289 -- bug 8299240 - end
1290 SUBSTR(ven.segment1, 1, 8),
1291 inv.invoice_type_lookup_code,
1292 get_balancing_segment(acctinfo.dist_code_combination_id),
1293 get_accounting_segment(decode(zl.def_rec_settlement_option_code,'DEFERRED', ACCOUNTS.tax_account_ccid, acctinfo.dist_code_combination_id)),
1294 pre.accounting_date,
1295 inv.invoice_num,
1296 dis.amount,
1297 inv.cancelled_date,
1298 pre.amount,
1299 inv.invoice_amount,
1300 inv.exchange_rate;
1301
1302
1303 --Code to updated the records with the balancing segment description
1304 FOR c_balancing_segment IN company_info
1305 LOOP
1306 SELECT distinct ffv.description
1307 INTO l_company_desc
1308 FROM fnd_id_flex_segments_vl fif
1309 ,fnd_flex_values_vl ffv
1310 WHERE fif.id_flex_code = 'GL#'
1311 AND fif.application_id = 101
1312 AND fif.id_flex_num = g_struct_num
1313 AND ffv.flex_value = c_balancing_segment.jg_info_v5
1314 AND ffv.flex_value_set_id = fif.flex_value_set_id;
1315
1316 UPDATE jg_zz_vat_trx_gt
1317 SET jg_info_v10 = l_company_desc
1318 WHERE jg_info_v5 = c_balancing_segment.jg_info_v5;
1319 END LOOP;
1320
1321 EXCEPTION
1322 WHEN OTHERS THEN
1323 fnd_file.put_line(fnd_file.log,' An error occured while inserting and updating data to the global tmp table. Error : ' || SUBSTR(SQLERRM, 1, 200));
1324 END;
1325
1326 ELSE
1327 --If calling report is not JEFRTXDC
1328 BEGIN
1329 FOR c_data_rec1 IN C_GENERIC(l_start_date, l_end_date)
1330 LOOP
1331
1332 FOR c_trx_ccid IN c_get_trx_ccid (c_data_rec1.invoice_id
1333 , c_data_rec1.application_id
1334 , c_data_rec1.event_class_code
1335 , c_data_rec1.trx_line_id
1336 , c_data_rec1.entity_code)
1337 LOOP
1338 l_trx_ccid := c_trx_ccid.code_combination_id;
1339 END LOOP;
1340
1341 FOR c_tax_ccid IN c_get_tax_ccid (c_data_rec1.invoice_id
1342 , c_data_rec1.application_id
1343 , c_data_rec1.event_class_code
1344 , c_data_rec1.trx_line_id
1345 , c_data_rec1.entity_code)
1346 LOOP
1347 l_tax_ccid := c_tax_ccid.code_combination_id;
1348 END LOOP;
1349
1350 l_acc_no := get_accounting_segment(l_tax_ccid);
1351 l_company := get_balancing_segment(l_trx_ccid, l_company_desc);
1352
1353 INSERT INTO JG_ZZ_VAT_TRX_GT
1354 (jg_info_v1 --ven_name
1355 ,jg_info_v2 --ven_no
1356 ,jg_info_v3 --inv_type
1357 ,jg_info_n1 --tax_amt
1358 ,jg_info_n2 --rec_per
1359 ,jg_info_v4 --company
1360 ,jg_info_v5 --acc_no
1361 ,jg_info_n3 --tax_rate
1362 ,jg_info_v6 --tax_id
1363 ,jg_info_v7 --offset_tax_rate_code
1364 ,jg_info_v8 --tax_type
1365 ,jg_info_v9 --inv_no
1366 ,jg_info_d1 --acc_date
1367 ,jg_info_n4 --invoice_id
1368 ,jg_info_d2 --cancelled_date
1369 ,jg_info_n5 --item_line_cnt
1370 ,jg_info_n6 --charge_dist_id
1371 ,jg_info_d3 --check_void_date
1372 ,jg_info_n7 --pay_amt
1373 ,jg_info_v10 --line_type_lookup_code
1374 ,jg_info_v11 --line_type_lookup_code_item
1375 ,jg_info_v12 --line_type_lookup_code_prepay
1376 ,jg_info_v13 --reversal_flag_item
1377 ,jg_info_v14 --reversal_flag_pay
1378 ,jg_info_v15 --reversal_flag_prepay
1379 ,jg_info_n8 --parent_reversal_id
1380 ,jg_info_n9 --base_amount
1381 ,jg_info_n10 --invoice_amount
1382 ,jg_info_d4 --void_date
1383 ,jg_info_d5 --future_pay_due_date
1384 ,jg_info_d6 --check_date
1385 ,jg_info_v16 --payment_status_flag
1386 ,jg_info_d7 --accounting_date
1387 ,jg_info_n11 --reversal_inv_pmt_id
1388 ,jg_info_v17 --c_company
1389 )
1390 VALUES
1391 (c_data_rec1.ven_name --jg_info_v1
1392 ,c_data_rec1.ven_no --jg_info_v2
1393 ,c_data_rec1.inv_type --jg_info_v3
1394 ,c_data_rec1.tax_amt --jg_info_n1
1395 ,c_data_rec1.rec_per --jg_info_n2
1396 ,c_data_rec1.company --jg_info_v4
1397 ,c_data_rec1.acc_no --jg_info_v5
1398 ,c_data_rec1.tax_rate --jg_info_n3
1399 ,c_data_rec1.tax_id --jg_info_v6
1400 ,c_data_rec1.offset_tax_rate_code --jg_info_v7
1401 ,c_data_rec1.tax_type --jg_info_v8
1402 ,c_data_rec1.inv_no --jg_info_v9
1403 ,c_data_rec1.acc_date --jg_info_d1
1404 ,c_data_rec1.invoice_id --jg_info_n4
1405 ,c_data_rec1.cancelled_date --jg_info_d2
1406 ,c_data_rec1.item_line_cnt --jg_info_n5
1407 ,c_data_rec1.charge_dist_id --jg_info_n6
1408 ,c_data_rec1.check_void_date --jg_info_d3
1409 ,c_data_rec1.pay_amt --jg_info_n7
1410 ,c_data_rec1.line_type_lookup_code --jg_info_v10
1411 ,c_data_rec1.line_type_lookup_code_item --jg_info_v11
1412 ,c_data_rec1.line_type_lookup_code_prepay --jg_info_v12
1413 ,c_data_rec1.reversal_flag_item --jg_info_v13
1414 ,c_data_rec1.reversal_flag_pay --jg_info_v14
1415 ,c_data_rec1.reversal_flag_prepay --jg_info_v15
1416 ,c_data_rec1.parent_reversal_id --jg_info_n8
1417 ,c_data_rec1.base_amount --jg_info_n9
1418 ,c_data_rec1.invoice_amount --jg_info_n10
1419 ,c_data_rec1.void_date --jg_info_d4
1420 ,c_data_rec1.future_pay_due_date --jg_info_d5
1421 ,c_data_rec1.check_date --jg_info_d6
1422 ,c_data_rec1.payment_status_flag --jg_info_v16
1423 ,c_data_rec1.accounting_date --jg_info_d7
1424 ,c_data_rec1.reversal_inv_pmt_id --jg_info_n11
1425 ,l_company_desc --jg_info_v17
1426 );
1427
1428 END LOOP;
1429
1430 EXCEPTION
1431 WHEN OTHERS THEN
1432 fnd_file.put_line(fnd_file.log,' An error occured while inserting data into the global tmp table in the generic cursor. Error : ' || SUBSTR(SQLERRM, 1, 200));
1433 END;
1434 END IF;
1435 IF (P_COMPANY IS NOT NULL) THEN
1436
1437 get_boiler_plates;
1438
1439 END IF;
1440 EXCEPTION
1441 WHEN OTHERS THEN
1442 fnd_file.put_line(fnd_file.log,' An unexpected error occured in the before report trigger. Error : ' || SUBSTR(SQLERRM, 1, 200));
1443 RETURN FALSE;
1444 END;
1445 fnd_file.put_line(fnd_file.log,'End of the before report trigger ');
1446
1447 RETURN(TRUE);
1448 END;
1449
1450 /*
1451 REM +======================================================================+
1452 REM Name: C_PRT_INV_AMTFormula
1453 REM
1454 REM Description: This function is used to calculate the invoice
1455 REM amount.
1456 REM
1457 REM Parameters:
1458 REM p_cancelled_date => The invoice cancellation date
1459 REM P_START_DATE => Period Start date
1460 REM P_END_DATE => Period end date
1461 REM p_real_inv_amt => actual invoice amount
1462 REM +======================================================================+
1463 */
1464 FUNCTION C_PRT_INV_AMTFormula
1465 (
1466 p_cancelled_date IN DATE
1467 ,p_start_date IN DATE
1468 ,p_end_date IN DATE
1469 ,p_real_inv_amt IN NUMBER
1470 ) RETURN VARCHAR2 IS
1471 BEGIN
1472
1473 IF p_cancelled_date IS NOT NULL
1474 AND p_cancelled_date BETWEEN p_start_date AND p_end_date THEN
1475 RETURN(0);
1476 ELSE
1477 RETURN(p_real_inv_amt);
1478
1479 END IF;
1480 END;
1481
1482 /*
1483 REM +======================================================================+
1484 REM Name: g_companygroupfilter
1485 REM
1486 REM Description: This function is used as a group filter for grouping the data
1487 REM
1488 REM Parameters:
1489 REM +======================================================================+
1490 */
1491 FUNCTION g_companygroupfilter(company IN VARCHAR2) RETURN BOOLEAN IS
1492 BEGIN
1493
1494 IF (G_DATA_FOUND IS NULL) THEN
1495 G_DATA_FOUND := company;
1496 RETURN(TRUE);
1497 ELSE
1498 RETURN(TRUE);
1499 END IF;
1500 RETURN(TRUE);
1501 END;
1502
1503 /*
1504 REM +======================================================================+
1505 REM Name: get_lookup_meaning
1506 REM
1507 REM Description: This procedure returns the lookup meaning of the lookup code provided
1508 REM
1509 REM Parameters:
1510 REM p_lookup_type => The lookup type
1511 REM p_lookup_code => Lookup code
1512 REM x_lookup_meaning => Lookup meaning.
1513 REM +======================================================================+
1514 */
1515 PROCEDURE get_lookup_meaning
1516 (
1517 p_lookup_type IN VARCHAR2
1518 ,p_lookup_code IN VARCHAR2
1519 ,x_lookup_meaning IN OUT NOCOPY VARCHAR2
1520 ) IS
1521
1522 w_meaning VARCHAR2(80);
1523
1524 CURSOR c_lookup_meaning (p_lookup_type VARCHAR2
1525 ,p_lookup_code VARCHAR2)
1526 IS
1527 SELECT meaning
1528 FROM fnd_lookups
1529 WHERE lookup_type = p_lookup_type
1530 AND lookup_code = p_lookup_code;
1531
1532 BEGIN
1533
1534 FOR c_meaning IN c_lookup_meaning (p_lookup_type, p_lookup_code)
1535 LOOP
1536 w_meaning := c_meaning.meaning;
1537 END LOOP;
1538
1539 x_lookup_meaning := w_meaning;
1540
1541 EXCEPTION
1542 WHEN no_data_found THEN
1543 x_lookup_meaning := NULL;
1544 END;
1545
1546 /*
1547 REM +======================================================================+
1548 REM Name: get_boiler_plates
1549 REM
1550 REM Description: This procedure sets the company title and industry code
1551 REM
1552 REM Parameters:
1553 REM +======================================================================+
1554 */
1555 PROCEDURE get_boiler_plates IS
1556
1557 w_industry_code VARCHAR2(20);
1558 w_industry_stat VARCHAR2(20);
1559
1560 BEGIN
1561
1562 IF fnd_installation.get(0, 0, w_industry_stat, w_industry_code) THEN
1563 IF w_industry_code = 'C' THEN
1564 G_company_title := NULL;
1565 ELSE
1566 get_lookup_meaning('IND_COMPANY', w_industry_code, G_company_title);
1567 END IF;
1568 END IF;
1569
1570 G_INDUSTRY_CODE := w_Industry_code;
1571
1572 END;
1573
1574
1575 /*
1576 REM +======================================================================+
1577 REM Name: c_payment_amtformula
1578 REM
1579 REM Description: This function calculates the payment amount
1580 REM
1581 REM Parameters:
1582 REM p_tax_type => Tax type
1583 REM p_inv_type => Invoice Type
1584 REM p_invoice_id => Invoice Id
1585 REM p_pay_amt =>
1586 REM p_start_date => Period start date
1587 REM p_end_date => period end date
1588 REM +======================================================================+
1589 */
1590 FUNCTION c_payment_amtformula
1591 (
1592 p_tax_type IN VARCHAR2
1593 ,p_inv_type IN VARCHAR2
1594 ,p_invoice_id IN NUMBER
1595 ,p_pay_amt IN NUMBER
1596 ,p_start_date IN DATE
1597 ,p_end_date IN DATE
1598 ) RETURN NUMBER IS
1599 l_prep_pay_amt NUMBER := 0;
1600 l_payment_amt NUMBER := 0;
1601 l_ppp_amt NUMBER := 0;
1602 l_total_payment NUMBER := 0;
1603
1604 CURSOR c_get_amount (p_invoice_id NUMBER
1605 ,p_start_date DATE
1606 ,p_end_date DATE)
1607 IS
1608 SELECT SUM(nvl(p.invoice_base_amount, p.amount)) amount
1609 FROM ap_invoice_payments p
1610 WHERE p.invoice_id = p_invoice_id
1611 AND p.accounting_date BETWEEN p_start_date AND p_end_date;
1612
1613 CURSOR c_get_prepay_amount ( p_invoice_id NUMBER
1614 ,p_start_date DATE
1615 ,p_end_date DATE)
1616 IS
1617 SELECT SUM(nvl(pp.base_amount, pp.amount) + nvl(ppt.base_amount, ppt.amount)) prepay_amount
1618 FROM ap_invoice_distributions pp
1619 ,ap_invoice_distributions ppt
1620 WHERE pp.invoice_id = p_invoice_id
1621 AND ppt.invoice_id = p_invoice_id
1622 AND pp.line_type_lookup_code = 'PREPAY'
1623 AND pp.charge_applicable_to_dist_id = ppt.invoice_distribution_id
1624 AND nvl(pp.reversal_flag, 'N') <> 'Y'
1625 AND pp.accounting_date BETWEEN p_start_date AND p_end_date;
1626
1627 CURSOR c_get_pre_pay_amount (p_invoice_id NUMBER
1628 ,p_start_date DATE
1629 ,p_end_date DATE)
1630 IS
1631 SELECT (-1) * SUM(nvl(ppp.base_amount, ppp.amount)) prepay_amount
1632 FROM ap_invoice_distributions ppp
1633 WHERE ppp.invoice_id = p_invoice_id
1634 AND ppp.line_type_lookup_code = 'PREPAY'
1635 AND nvl(ppp.reversal_flag, 'N') <> 'Y'
1636 AND ppp.accounting_date BETWEEN p_start_date AND p_end_date
1637 AND NOT EXISTS (SELECT 'x'
1638 FROM ap_invoice_distributions ptax
1639 WHERE ptax.invoice_id = p_invoice_id
1640 AND ppp.charge_applicable_to_dist_id = ptax.invoice_distribution_id);
1641
1642
1643 BEGIN
1644
1645 IF p_tax_type = 'CRE/M'
1646 AND p_inv_type <> 'PREPAYMENT_APPLICATION' THEN
1647 IF p_inv_type LIKE '%JEPP' THEN
1648
1649 FOR c_amount IN c_get_amount (p_invoice_id, p_start_date, p_end_date)
1650 LOOP
1651 l_payment_amt := c_amount.amount;
1652 END LOOP;
1653
1654 FOR c_prepay_amount IN c_get_prepay_amount (p_invoice_id, p_start_date, p_end_date)
1655 LOOP
1656 l_prep_pay_amt := c_prepay_amount.prepay_amount;
1657 END LOOP;
1658
1659 IF l_prep_pay_amt IS NULL THEN
1660
1661 FOR c_pre_pay_amount IN c_get_pre_pay_amount (p_invoice_id, p_start_date, p_end_date)
1662 LOOP
1663 l_prep_pay_amt := c_pre_pay_amount.prepay_amount;
1664 END LOOP;
1665
1666 END IF;
1667
1668 l_total_payment := abs(l_prep_pay_amt) + nvl(l_payment_amt, 0);
1669
1670 RETURN(l_total_payment);
1671
1672 ELSE
1673
1674 l_prep_pay_amt := 0;
1675
1676 END IF;
1677
1678 FOR c_pre_pay_amount IN c_get_pre_pay_amount (p_invoice_id, p_start_date, p_end_date)
1679 LOOP
1680 l_ppp_amt := c_pre_pay_amount.prepay_amount;
1681 END LOOP;
1682
1683 FOR c_amount IN c_get_amount (p_invoice_id, p_start_date, p_end_date)
1684 LOOP
1685 l_payment_amt := c_amount.amount;
1686 END LOOP;
1687
1688 l_total_payment := round(nvl(l_payment_amt, 0) -
1689 nvl(l_prep_pay_amt, 0) - nvl(l_ppp_amt, 0),
1690 G_PRECISION);
1691
1692 ELSE
1693 l_total_payment := p_pay_amt;
1694 END IF;
1695
1696 RETURN(l_total_payment);
1697 EXCEPTION
1698 WHEN OTHERS THEN
1699 fnd_file.put_line(fnd_file.log,' An error occured while calculating the payment amount. Error : ' || SUBSTR(SQLERRM, 1, 200));
1700 END;
1701
1702 /*
1703 REM +======================================================================+
1704 REM Name: cf_rec_tax_calcformula
1705 REM
1706 REM Description: This function calculates the recoverable tax
1707 REM
1708 REM Parameters:
1709 REM p_rec_per =>
1710 REM +======================================================================+
1711 */
1712 FUNCTION cf_rec_tax_calcformula(p_rec_per IN NUMBER) RETURN CHAR IS
1713 l_rec_rate NUMBER;
1714 BEGIN
1715
1716 IF p_rec_per IS NOT NULL THEN
1717 RETURN(to_char(round(p_rec_per, 2)) || '%');
1718 ELSE
1719 RETURN(NULL);
1720 END IF;
1721 END;
1722
1723 /*
1724 REM +======================================================================+
1725 REM Name: C_PRT_AMT_TXBLFormula
1726 REM
1727 REM Description: This function calculates the amount taxable
1728 REM
1729 REM Parameters:
1730 REM p_tax_type,p_const_num,p_inv_type,p_tax_rate,p_invoice_id
1731 REM p_tax_id,p_start_date,p_end_date,p_real_inv_amt,p_cancelled_date
1732 REM p_check_void_date,p_payment_amt,p_txbl_disc_amt,p_tax_disc_amt
1733 REM +======================================================================+
1734 */
1735 FUNCTION C_PRT_AMT_TXBLFormula
1736 (
1737 p_tax_type VARCHAR2
1738 ,p_const_num VARCHAR2
1739 ,p_inv_type VARCHAR2
1740 ,p_tax_rate NUMBER
1741 ,p_invoice_id NUMBER
1742 ,p_tax_id NUMBER
1743 ,p_offset_tax_rate_code VARCHAR2
1744 ,p_start_date DATE
1745 ,p_end_date DATE
1746 ,p_real_inv_amt NUMBER
1747 ,p_cancelled_date DATE
1748 ,p_check_void_date DATE
1749 ,p_payment_amt NUMBER
1750 ,p_txbl_disc_amt NUMBER
1751 ,p_tax_disc_amt NUMBER
1752 ) RETURN VARCHAR2 IS
1753
1754 BEGIN
1755
1756 DECLARE
1757 tbl_amt NUMBER;
1758 l_payment_amt NUMBER;
1759 tbl_amt1 NUMBER;
1760 l_error_position VARCHAR2(20);
1761 l_taxable_amount NUMBER;
1762
1763 CURSOR c_get_amount ( p_invoice_id NUMBER
1764 ,p_tax_id NUMBER
1765 ,p_start_date DATE
1766 ,p_end_date DATE)
1767 IS
1768 SELECT SUM(nvl(dis.base_amount, dis.amount)) amount
1769 FROM ap_invoice_distributions dis
1770 WHERE dis.invoice_id = p_invoice_id
1771 AND dis.line_type_lookup_code NOT IN ('REC_TAX','NONREC_TAX','PREPAY')
1772 AND dis.accounting_date BETWEEN p_start_date AND p_end_date;
1773
1774
1775 CURSOR c_get_off_amount ( p_invoice_id NUMBER
1776 ,p_offset_tax_rate_code VARCHAR2
1777 ,p_start_date DATE
1778 ,p_end_date DATE)
1779 IS
1780 SELECT SUM(nvl(dis.base_amount, dis.amount)) amount
1781 FROM ap_invoice_distributions dis
1782 ,zx_rates_b tax
1783 WHERE dis.line_type_lookup_code NOT IN ('TAX', 'PREPAY')
1784 AND tax.tax_rate_id = dis.tax_code_id
1785 AND dis.invoice_id = p_invoice_id
1786 AND tax.offset_tax_rate_code = p_offset_tax_rate_code
1787 AND dis.accounting_date BETWEEN p_start_date AND p_end_date;
1788
1789 CURSOR c_get_amount_prepay ( p_invoice_id NUMBER
1790 ,p_tax_id NUMBER
1791 ,p_start_date DATE
1792 ,p_end_date DATE)
1793 IS
1794 SELECT SUM(nvl(dis.base_amount, dis.amount)) amount
1795 FROM ap_invoice_distributions dis
1796 ,zx_rates_b tax
1797 WHERE tax.tax_rate_id = dis.tax_code_id
1798 AND dis.invoice_id = p_invoice_id
1799 AND tax.tax_rate_id = p_tax_id
1800 AND dis.line_type_lookup_code = 'PREPAY'
1801 AND dis.accounting_date BETWEEN p_start_date AND p_end_date;
1802
1803 CURSOR c_get_amount_deb ( p_invoice_id NUMBER
1804 ,p_tax_id NUMBER
1805 ,p_start_date DATE
1806 ,p_end_date DATE)
1807 IS
1808 SELECT SUM(nvl(dis.base_amount, dis.amount) * aip.discount_lost / p_real_inv_amt) amount
1809 FROM ap_invoice_distributions dis
1810 ,zx_rates_b tax
1811 ,ap_invoice_payments aip
1812 ,ap_invoices inv
1813 WHERE dis.line_type_lookup_code NOT IN ('TAX', 'PREPAY')
1814 AND inv.invoice_id = dis.invoice_id
1815 AND inv.invoice_id = aip.invoice_id
1816 AND dis.invoice_id = p_invoice_id
1817 AND tax.tax_rate_id = dis.tax_code_id
1818 AND tax.tax_rate_id = p_tax_id
1819 AND aip.accounting_date BETWEEN p_start_date AND p_end_date;
1820
1821 CURSOR c_get_off_amount_deb ( p_invoice_id NUMBER
1822 ,p_offset_tax_rate_code VARCHAR2
1823 ,p_start_date DATE
1824 ,p_end_date DATE)
1825 IS
1826 SELECT SUM(nvl(dis.base_amount, dis.amount) * aip.discount_lost / p_real_inv_amt) amount
1827 FROM ap_invoice_distributions dis
1828 ,zx_rates_b tax
1829 ,ap_invoice_payments aip
1830 ,ap_invoices inv
1831 WHERE dis.line_type_lookup_code NOT IN ('TAX', 'PREPAY')
1832 AND inv.invoice_id = dis.invoice_id
1833 AND inv.invoice_id = aip.invoice_id
1834 AND dis.invoice_id = p_invoice_id
1835 AND tax.tax_rate_id = dis.tax_code_id
1836 AND tax.offset_tax_rate_code = p_offset_tax_rate_code
1837 AND aip.accounting_date BETWEEN p_start_date AND p_end_date;
1838
1839 CURSOR c_get_amount_cre_rev ( p_invoice_id NUMBER
1840 ,p_tax_id NUMBER)
1841 IS
1842 SELECT (-1) * SUM(nvl(dis.base_amount, dis.amount)) amount
1843 FROM ap_invoice_distributions dis
1844 ,zx_rates_b tax
1845 WHERE dis.line_type_lookup_code NOT IN ('TAX', 'PREPAY')
1846 AND dis.invoice_id = p_invoice_id
1847 AND tax.tax_rate_id = p_tax_id
1848 AND dis.tax_code_id = tax.tax_rate_id
1849 AND dis.parent_reversal_id IS NOT NULL;
1850
1851 CURSOR c_get_amount_cre ( p_invoice_id NUMBER
1852 ,p_tax_id NUMBER)
1853 IS
1854 SELECT SUM(nvl(dis.base_amount, dis.amount)) amount
1855 FROM ap_invoice_distributions dis
1856 ,zx_rates_b tax
1857 WHERE dis.line_type_lookup_code NOT IN ('TAX', 'PREPAY')
1858 AND dis.invoice_id = p_invoice_id
1859 AND tax.tax_rate_id = p_tax_id
1860 AND dis.tax_code_id = tax.tax_rate_id;
1861
1862 CURSOR c_get_off_amount_cre ( p_invoice_id NUMBER
1863 ,p_offset_tax_rate_code VARCHAR2)
1864 IS
1865 SELECT SUM(nvl(dis.base_amount, dis.amount)) amount
1866 FROM ap_invoice_distributions dis
1867 ,zx_rates_b tax
1868 WHERE dis.line_type_lookup_code NOT IN ('TAX', 'PREPAY')
1869 AND dis.invoice_id = p_invoice_id
1870 AND tax.offset_tax_rate_code = p_offset_tax_rate_code
1871 AND dis.tax_code_id = tax.tax_rate_id;
1872
1873 CURSOR c_get_amount_cre_calc ( p_invoice_id NUMBER
1874 ,p_tax_id NUMBER)
1875 IS
1876 SELECT SUM(nvl(dis.base_amount, dis.amount)) * (p_payment_amt / p_real_inv_amt) amount
1877 FROM ap_invoice_distributions dis
1878 ,zx_rates_b tax
1879 WHERE dis.line_type_lookup_code NOT IN ('TAX', 'PREPAY')
1880 AND dis.invoice_id = p_invoice_id
1881 AND tax.tax_rate_id = p_tax_id
1882 AND dis.tax_code_id = tax.tax_rate_id;
1883
1884 BEGIN
1885
1886 IF p_tax_type = 'DEB/M'
1887 AND p_const_num = 'A' THEN
1888 IF p_inv_type <> 'PREPAYMENT_APPLICATION' THEN
1889 IF p_tax_rate >= 0 THEN
1890
1891 FOR c_amt IN c_get_amount (p_invoice_id, p_tax_id, p_start_date, p_end_date )
1892 LOOP
1893 tbl_amt := c_amt.amount;
1894 END LOOP;
1895
1896 ELSIF p_tax_rate < 0 THEN
1897 FOR c_off_amt IN c_get_off_amount (p_invoice_id, p_offset_tax_rate_code, p_start_date, p_end_date )
1898 LOOP
1899 tbl_amt := c_off_amt.amount;
1900 END LOOP;
1901
1902 END IF;
1903 ELSE
1904
1905 FOR c_amt_prepay IN c_get_amount_prepay (p_invoice_id, p_tax_id, p_start_date, p_end_date )
1906 LOOP
1907 tbl_amt := c_amt_prepay.amount;
1908 END LOOP;
1909
1910 END IF;
1911 ELSIF p_tax_type = 'DEB/M'
1912 AND p_const_num = 'C' THEN
1913 IF p_tax_rate >= 0 THEN
1914 FOR c_amt_deb IN c_get_amount_deb (p_invoice_id, p_tax_id, p_start_date, p_end_date )
1915 LOOP
1916 tbl_amt := c_amt_deb.amount;
1917 END LOOP;
1918
1919 ELSIF p_tax_rate < 0 THEN
1920 FOR c_off_amt_deb IN c_get_off_amount_deb (p_invoice_id, p_offset_tax_rate_code, p_start_date, p_end_date )
1921 LOOP
1922 tbl_amt := c_off_amt_deb.amount;
1923 END LOOP;
1924
1925 END IF;
1926 ELSIF p_tax_type = 'CRE/M' THEN
1927 IF p_inv_type <> 'PREPAYMENT_APPLICATION' THEN
1928 IF p_inv_type NOT LIKE '%JEPP' THEN
1929
1930 IF p_tax_rate >= 0 THEN
1931
1932 IF p_cancelled_date IS NOT NULL THEN
1933
1934 IF p_check_void_date BETWEEN p_start_date AND p_end_date THEN
1935 tbl_amt := 0;
1936 ELSE
1937
1938 FOR c_amt_cre_rev IN c_get_amount_cre_rev (p_invoice_id, p_tax_id)
1939 LOOP
1940 tbl_amt := c_amt_cre_rev.amount;
1941 END LOOP;
1942
1943 END IF;
1944
1945 ELSE
1946
1947 FOR c_amt_cre IN c_get_amount_cre (p_invoice_id, p_tax_id)
1948 LOOP
1949 tbl_amt := c_amt_cre.amount;
1950 END LOOP;
1951
1952 END IF;
1953
1954 ELSIF p_tax_rate < 0 THEN
1955 FOR c_off_amt_cre IN c_get_off_amount_cre (p_invoice_id, p_offset_tax_rate_code)
1956 LOOP
1957 tbl_amt := c_off_amt_cre.amount;
1958 END LOOP;
1959
1960 END IF;
1961
1962 ELSE
1963
1964 IF p_tax_rate >= 0 THEN
1965 FOR c_amt_cre_calc IN c_get_amount_cre_calc (p_invoice_id, p_tax_id)
1966 LOOP
1967 tbl_amt := c_amt_cre_calc.amount;
1968 END LOOP;
1969
1970 l_taxable_amount := to_char(round(tbl_amt, G_PRECISION));
1971
1972 ELSIF p_tax_rate < 0 THEN
1973 FOR c_off_amt_cre_l IN c_get_off_amount_cre (p_invoice_id, p_offset_tax_rate_code)
1974 LOOP
1975 tbl_amt := c_off_amt_cre_l.amount;
1976 END LOOP;
1977
1978 l_taxable_amount := tbl_amt;
1979 END IF;
1980
1981 END IF;
1982
1983 ELSE
1984 FOR c_amt_prepay_l IN c_get_amount_prepay (p_invoice_id, p_tax_id, p_start_date, p_end_date )
1985 LOOP
1986 tbl_amt := c_amt_prepay_l.amount;
1987 END LOOP;
1988
1989 END IF;
1990 END IF;
1991
1992 IF ((p_tax_type = 'DEB/M') OR ((p_tax_type = 'CRE/M') AND
1993 (p_inv_type = 'PREPAYMENT_APPLICATION' OR
1994 p_inv_type LIKE '%JEPP'))) THEN
1995 l_taxable_amount := to_char(round(tbl_amt, G_PRECISION));
1996 ELSE
1997 IF p_real_inv_amt - p_txbl_disc_amt - p_tax_disc_amt = 0 THEN
1998 l_taxable_amount := to_char(round(((-1) * tbl_amt -
1999 p_txbl_disc_amt), G_PRECISION));
2000 ELSE
2001 l_taxable_amount := to_char(round((tbl_amt - p_txbl_disc_amt) *
2002 p_payment_amt / (p_real_inv_amt -
2003 p_txbl_disc_amt -
2004 p_tax_disc_amt), G_PRECISION));
2005 END IF;
2006 END IF;
2007
2008 RETURN(l_taxable_amount); -- currency format this amount for use.
2009
2010 EXCEPTION
2011 WHEN OTHERS THEN
2012 fnd_file.put_line(fnd_file.log,' An error occured while calculating the amount taxable. Error : ' || SUBSTR(SQLERRM, 1, 200));
2013 raise_application_error(-20101, NULL);
2014 END;
2015
2016 END;
2017
2018 /*
2019 REM +======================================================================+
2020 REM Name: cf_tax_amtformula
2021 REM
2022 REM Description: This function calculates the amount taxable
2023 REM
2024 REM Parameters:
2025 REM p_invoice_id,p_tax_type,p_inv_type,p_real_inv_amt,p_txbl_discount_amt
2026 REM p_tax_amt,p_payment_amt,p_check_void_date,p_start_date,p_end_date
2027 REM +======================================================================+
2028 */
2029 FUNCTION cf_tax_amtformula
2030 (
2031 p_invoice_id IN NUMBER
2032 ,p_tax_type IN VARCHAR2
2033 ,p_inv_type IN VARCHAR2
2034 ,p_real_inv_amt IN NUMBER
2035 ,p_txbl_discount_amt IN NUMBER
2036 ,p_tax_amt IN NUMBER
2037 ,p_payment_amt IN NUMBER
2038 ,p_check_void_date IN DATE
2039 ,p_start_date IN DATE
2040 ,p_end_date IN DATE
2041 ) RETURN NUMBER IS
2042 l_inv_remaining_amount NUMBER := 0;
2043 l_pp_tax_amount NUMBER;
2044
2045 CURSOR c_get_inv_remain_amt (p_end_date DATE)
2046 IS
2047 SELECT round(SUM(nvl(dis.base_amount, dis.amount)), G_PRECISION) remaining_amount
2048 FROM ap_invoice_distributions dis
2049 WHERE dis.invoice_id = p_invoice_id
2050 AND dis.accounting_date <= p_end_date;
2051
2052 BEGIN
2053
2054 FOR c_inv_remain_amt IN c_get_inv_remain_amt (p_end_date)
2055 LOOP
2056 l_inv_remaining_amount := c_inv_remain_amt.remaining_amount;
2057 END LOOP;
2058
2059 IF ((p_tax_type = 'DEB/M') OR
2060 ((p_tax_type = 'CRE/M') AND
2061 (p_inv_type = 'PREPAYMENT_APPLICATION' OR p_inv_type LIKE '%JEPP'))) THEN
2062 IF (((p_real_inv_amt - p_txbl_discount_amt - G_tax_discount_amt) = 0) or (p_tax_type = 'DEB/M')) THEN
2063 RETURN(round(p_tax_amt, G_PRECISION));
2064 ELSE
2065 RETURN(round((p_tax_amt - G_tax_discount_amt) * p_payment_amt /
2066 (p_real_inv_amt - p_txbl_discount_amt -
2067 G_tax_discount_amt), G_PRECISION));
2068 END IF;
2069 ELSE
2070 IF (p_real_inv_amt - p_txbl_discount_amt - G_tax_discount_amt = 0)
2071 OR (l_inv_remaining_amount - p_payment_amt - p_txbl_discount_amt -
2072 G_tax_discount_amt <= 0) THEN
2073
2074 IF p_check_void_date IS NOT NULL
2075 AND p_check_void_date BETWEEN p_start_date AND p_end_date THEN
2076 IF p_check_void_date BETWEEN p_start_date AND p_end_date THEN
2077
2078 RETURN 0;
2079 ELSE
2080
2081 RETURN(round((-1 * p_tax_amt - G_tax_discount_amt), G_PRECISION));
2082 END IF;
2083 ELSE
2084
2085
2086 RETURN(round((p_tax_amt - G_tax_discount_amt) * p_payment_amt /
2087 (p_real_inv_amt - p_txbl_discount_amt -
2088 G_tax_discount_amt), G_PRECISION));
2089 END IF;
2090 ELSE
2091
2092 RETURN(round((p_tax_amt - G_tax_discount_amt) * p_payment_amt /
2093 (p_real_inv_amt - p_txbl_discount_amt -
2094 G_tax_discount_amt), G_PRECISION));
2095 END IF;
2096 END IF;
2097 EXCEPTION
2098 WHEN no_data_found THEN
2099 RETURN(round(p_tax_amt, G_PRECISION));
2100 END;
2101
2102 /*
2103 REM +======================================================================+
2104 REM Name: cf_txbl_discount_amtformula
2105 REM
2106 REM Description: This function calculates the taxable discount amount
2107 REM
2108 REM Parameters:
2109 REM p_invoice_id,p_tax_type,p_inv_type,p_tax_rate,
2110 REM p_start_date,p_end_date
2111 REM +======================================================================+
2112 */
2113 FUNCTION cf_txbl_discount_amtformula
2114 (
2115 p_tax_type IN VARCHAR2
2116 ,p_inv_type IN VARCHAR2
2117 ,p_invoice_id IN NUMBER
2118 ,p_tax_rate IN NUMBER
2119 ,p_start_date IN DATE
2120 ,p_end_date IN DATE
2121 ) RETURN NUMBER IS
2122 l_discount_amt NUMBER := 0;
2123 l_txbl_damt NUMBER := 0;
2124
2125 CURSOR c_get_discount_amt (p_invoice_id NUMBER
2126 ,p_start_date DATE
2127 ,p_end_date DATE)
2128 IS
2129 SELECT SUM(nvl(disc.discount_taken, 0)) discount_taken
2130 FROM ap_invoice_payments disc
2131 ,ap_checks chk
2132 WHERE disc.invoice_id = p_invoice_id
2133 AND disc.check_id = chk.check_id
2134 AND nvl(chk.future_pay_due_date, chk.check_date) BETWEEN
2135 p_start_date AND p_end_date;
2136
2137 BEGIN
2138 IF p_tax_type = 'CRE/M' AND p_inv_type <> 'PREPAYMENT_APPLICATION' THEN
2139 BEGIN
2140
2141 FOR c_discount_amt IN c_get_discount_amt (p_invoice_id, p_start_date, p_end_date)
2142 LOOP
2143 l_discount_amt := c_discount_amt.discount_taken;
2144 END LOOP;
2145
2146 EXCEPTION
2147 WHEN OTHERS THEN
2148 fnd_file.put_line(fnd_file.log,' An error occured while calculating the taxable discount amount. Error : ' || SUBSTR(SQLERRM, 1, 200));
2149 l_discount_amt := 0;
2150 END;
2151 END IF;
2152
2153 IF G_disc_isinvlesstax_flag <> 'Y' THEN
2154 l_txbl_damt := round(l_discount_amt * (100 - p_tax_rate) / 100, G_PRECISION);
2155 ELSE
2156 l_txbl_damt := l_discount_amt;
2157 END IF;
2158
2159 G_tax_discount_amt := l_discount_amt - l_txbl_damt;
2160
2161 RETURN(l_txbl_damt);
2162 END;
2163
2164 /*
2165 REM +======================================================================+
2166 REM Name: cf_item_tax_amtformula
2167 REM
2168 REM Description: This function calculates the item tax amount
2169 REM
2170 REM Parameters:
2171 REM p_cancelled_date,p_acc_date,p_tax_amt,p_item_line_cnt,
2172 REM p_start_date,p_end_date
2173 REM +======================================================================+
2174 */
2175 FUNCTION cf_item_tax_amtformula
2176 (
2177 p_cancelled_date IN DATE
2178 ,p_acc_date IN DATE
2179 ,p_tax_amt IN NUMBER
2180 ,p_item_line_cnt IN NUMBER
2181 ,p_start_date IN DATE
2182 ,p_end_date IN DATE
2183 ) RETURN NUMBER IS
2184 BEGIN
2185 IF p_cancelled_date IS NOT NULL
2186 AND p_acc_date BETWEEN p_start_date AND p_end_date THEN
2187 RETURN((-1) * (p_tax_amt / p_item_line_cnt));
2188 ELSE
2189
2190 RETURN(p_tax_amt / p_item_line_cnt);
2191
2192 END IF;
2193 END;
2194
2195 /*
2196 REM +======================================================================+
2197 REM Name: cf_real_inv_amtformula
2198 REM
2199 REM Description: This function calculates the actual invoice amount
2200 REM
2201 REM Parameters:
2202 REM p_invoice_id,p_invoice_amount
2203 REM +======================================================================+
2204 */
2205 FUNCTION cf_real_inv_amtformula
2206 (
2207 p_invoice_id IN NUMBER
2208 ,p_invoice_amount IN NUMBER
2209 ) RETURN NUMBER IS
2210 l_iipp_amt NUMBER;
2211
2212 CURSOR c_get_inv_prepay_amt (p_invoice_id NUMBER)
2213 IS
2214 SELECT SUM(decode(invoice_includes_prepay_flag, 'Y', nvl(base_amount, amount), 0)) iipp_amt
2215 FROM ap_invoice_distributions
2216 WHERE invoice_id = p_invoice_id;
2217
2218 BEGIN
2219
2220 FOR c_inv_prepay_amt IN c_get_inv_prepay_amt (p_invoice_id)
2221 LOOP
2222 l_iipp_amt := c_inv_prepay_amt.iipp_amt;
2223 END LOOP;
2224
2225 RETURN(p_invoice_amount - l_iipp_amt);
2226 EXCEPTION
2227 WHEN OTHERS THEN
2228 RETURN(p_invoice_amount);
2229 END;
2230
2231
2232 /*
2233 REM +======================================================================+
2234 REM Name: G_DATA_FOUND_formula
2235 REM
2236 REM Description: Checks if company was found
2237 REM
2238 REM Parameters:
2239 REM +======================================================================+
2240 */
2241 FUNCTION G_DATA_FOUND_formula RETURN VARCHAR2 IS
2242 BEGIN
2243 RETURN G_DATA_FOUND;
2244 END;
2245
2246 /*
2247 REM +======================================================================+
2248 REM Name: G_CURR_NAME_formula
2249 REM
2250 REM Description: Returns the currency
2251 REM
2252 REM Parameters:
2253 REM +======================================================================+
2254 */
2255 FUNCTION G_CURR_NAME_formula RETURN VARCHAR2 IS
2256 BEGIN
2257 RETURN G_CURR_NAME;
2258 END;
2259
2260 /*
2261 REM +======================================================================+
2262 REM Name: G_company_title_formula
2263 REM
2264 REM Description: Returns the company title
2265 REM
2266 REM Parameters:
2267 REM +======================================================================+
2268 */
2269 FUNCTION G_company_title_formula RETURN VARCHAR2 IS
2270 BEGIN
2271 RETURN G_company_title;
2272 END;
2273
2274 /*
2275 REM +======================================================================+
2276 REM Name: G_PRECISION_formula
2277 REM
2278 REM Description: Returns the rounding off precision.
2279 REM
2280 REM Parameters:
2281 REM +======================================================================+
2282 */
2283 FUNCTION G_PRECISION_formula RETURN NUMBER IS
2284 BEGIN
2285 RETURN G_PRECISION;
2286 END;
2287
2288 /*
2289 REM +======================================================================+
2290 REM Name: set_display_for_gov
2291 REM
2292 REM Description: Setting display for gov
2293 REM
2294 REM Parameters:
2295 REM +======================================================================+
2296 */
2297 FUNCTION set_display_for_gov RETURN BOOLEAN IS
2298 BEGIN
2299 IF G_INDUSTRY_CODE = 'C' THEN
2300 RETURN(FALSE);
2301 ELSE
2302 IF G_company_title IS NOT NULL THEN
2303 RETURN(TRUE);
2304 ELSE
2305 RETURN(FALSE);
2306 END IF;
2307 END IF;
2308 RETURN NULL;
2309 END;
2310
2311 /*
2312 REM +======================================================================+
2313 REM Name: set_display_for_core
2314 REM
2315 REM Description: Setting display for core
2316 REM
2317 REM Parameters:
2318 REM +======================================================================+
2319 */
2320 FUNCTION set_display_for_core RETURN BOOLEAN IS
2321 BEGIN
2322 IF G_INDUSTRY_CODE = 'C' THEN
2323 RETURN(TRUE);
2324 ELSE
2325 IF G_company_title IS NOT NULL THEN
2326 RETURN(FALSE);
2327 ELSE
2328 RETURN(TRUE);
2329 END IF;
2330 END IF;
2331 RETURN NULL;
2332 END;
2333
2334
2335 /*
2336 REM +======================================================================+
2337 REM Name: G_LEDGER_CURR_FORMULA
2338 REM
2339 REM Description: Retuns the G_LEDGER_CURR
2340 REM
2341 REM Parameters:
2342 REM +======================================================================+
2343 */
2344 FUNCTION G_LEDGER_CURR_FORMULA RETURN VARCHAR2 IS
2345 BEGIN
2346
2347 RETURN G_LEDGER_CURR;
2348 END;
2349
2350 /*
2351 REM +======================================================================+
2352 REM Name: G_INDUSTRY_CODE_FORMULA
2353 REM
2354 REM Description: Retuns the G_INDUSTRY_CODE
2355 REM
2356 REM Parameters:
2357 REM +======================================================================+
2358 */
2359 FUNCTION G_INDUSTRY_CODE_FORMULA RETURN VARCHAR2 IS
2360 BEGIN
2361
2362 RETURN G_INDUSTRY_CODE;
2363 END;
2364
2365
2366
2367 /*
2368 REM +======================================================================+
2369 REM Name: get_accounting_segment
2370 REM
2371 REM Description: Fetch the accounting segment values
2372 REM
2373 REM Parameters:
2374 REM +======================================================================+
2375 */
2376 FUNCTION get_accounting_segment(p_ccid IN NUMBER) RETURN VARCHAR2 IS
2377 l_accounting_segment VARCHAR2(20);
2378 l_acc_no VARCHAR2(25);
2379 l_stmt VARCHAR2(1000);
2380 TYPE t_crs IS REF CURSOR;
2381 c_crs t_crs;
2382 BEGIN
2383 l_accounting_segment := fa_rx_flex_pkg.flex_sql(p_application_id => 101
2384 ,p_id_flex_code => 'GL#'
2385 ,p_id_flex_num => G_STRUCT_NUM
2386 ,p_table_alias => ''
2387 ,p_mode => 'SELECT'
2388 ,p_qualifier => 'GL_ACCOUNT');
2389 -- The above function will return account segment in the form CC.SEGMENT1
2390 -- we need to drop CC. to get the actual account segment.
2391 l_accounting_segment := substrb(l_accounting_segment
2392 ,instrb(l_accounting_segment
2393 ,'.') + 1);
2394
2395 -- Fetch the company and acc_no information
2396 l_stmt := ' SELECT ' || l_accounting_segment ||
2397 ' FROM GL_CODE_COMBINATIONS ' ||
2398 ' WHERE CODE_COMBINATION_ID = :LLCID';
2399 OPEN c_crs FOR l_stmt
2400 USING p_ccid;
2401 LOOP
2402 FETCH c_crs
2403 INTO l_acc_no;
2404 EXIT WHEN c_crs%NOTFOUND;
2405 END LOOP;
2406 CLOSE c_crs;
2407 RETURN l_acc_no;
2408 EXCEPTION
2409 WHEN NO_DATA_FOUND THEN
2410 fnd_file.put_line(fnd_file.log,' No record was returned for the GL_Account segment. Error : ' || SUBSTR(SQLERRM,1,200));
2411 RETURN NULL;
2412 END;
2413
2414 /*
2415 REM +======================================================================+
2416 REM Name: get_balancing_segment
2417 REM
2418 REM Description: Fetch the GL Balancing segment value and description
2419 REM
2420 REM Parameters:
2421 REM +======================================================================+
2422 */
2423 FUNCTION get_balancing_segment
2424 (
2425 p_ccid IN NUMBER
2426 ,x_company_desc IN OUT NOCOPY VARCHAR2
2427 )
2428 RETURN VARCHAR2 IS
2429 l_balancing_segment VARCHAR2(20);
2430 l_company VARCHAR2(25);
2431 l_stmt VARCHAR2(1000);
2432 TYPE t_crs IS REF CURSOR;
2433 c_crs t_crs;
2434
2435 CURSOR c_balancing_desc(p_coaid NUMBER, p_company VARCHAR2)
2436 IS
2437 SELECT ffv.description
2438 FROM fnd_id_flex_segments_vl fif
2439 ,fnd_flex_values_vl ffv
2440 WHERE fif.id_flex_code = 'GL#'
2441 AND fif.application_id = 101
2442 AND fif.id_flex_num = p_coaid
2443 AND ffv.flex_value = p_company
2444 AND ffv.flex_value_set_id = fif.flex_value_set_id;
2445
2446 BEGIN
2447 l_balancing_segment := fa_rx_flex_pkg.flex_sql(p_application_id => 101
2448 ,p_id_flex_code => 'GL#'
2449 ,p_id_flex_num => G_STRUCT_NUM
2450 ,p_table_alias => ''
2451 ,p_mode => 'SELECT'
2452 ,p_qualifier => 'GL_BALANCING');
2453
2454 -- The above function will return balancing segment in the form CC.SEGMENT1
2455 -- we need to drop CC. to get the actual balancing segment.
2456 l_balancing_segment := substrb(l_balancing_segment
2457 ,instrb(l_balancing_segment
2458 ,'.') + 1);
2459
2460 -- Fetch the company and acc_no information
2461 l_stmt := ' SELECT ' || l_balancing_segment ||
2462 ' FROM GL_CODE_COMBINATIONS ' ||
2463 ' WHERE CODE_COMBINATION_ID = :LLCID';
2464 OPEN c_crs FOR l_stmt
2465 USING p_ccid;
2466 LOOP
2467 FETCH c_crs
2468 INTO l_company;
2469 EXIT WHEN c_crs%NOTFOUND;
2470 END LOOP;
2471 CLOSE c_crs;
2472 FOR c_bal_desc IN c_balancing_desc(G_STRUCT_NUM
2473 ,l_company)
2474 LOOP
2475 x_company_desc := c_bal_desc.description;
2476 END LOOP;
2477
2478 RETURN l_company;
2479 EXCEPTION
2480 WHEN NO_DATA_FOUND THEN
2481 fnd_file.put_line(fnd_file.log,' No record was returned for the GL_Balancing segment. Error : ' || SUBSTR(SQLERRM,1,200));
2482 RETURN NULL;
2483 END;
2484
2485 /*
2486 REM +======================================================================+
2487 REM Name: get_balancing_segment
2488 REM
2489 REM Description: Fetch the GL Balancing segment value and description
2490 REM
2491 REM Parameters:
2492 REM +======================================================================+
2493 */
2494 FUNCTION get_balancing_segment
2495 (
2496 p_ccid IN NUMBER
2497 )
2498 RETURN VARCHAR2 IS
2499 l_balancing_segment VARCHAR2(20);
2500 l_company VARCHAR2(25);
2501 l_stmt VARCHAR2(1000);
2502 TYPE t_crs IS REF CURSOR;
2503 c_crs t_crs;
2504
2505 CURSOR c_balancing_desc(p_coaid NUMBER, p_company VARCHAR2)
2506 IS
2507 SELECT ffv.description
2508 FROM fnd_id_flex_segments_vl fif
2509 ,fnd_flex_values_vl ffv
2510 WHERE fif.id_flex_code = 'GL#'
2511 AND fif.application_id = 101
2512 AND fif.id_flex_num = p_coaid
2513 AND ffv.flex_value = p_company
2514 AND ffv.flex_value_set_id = fif.flex_value_set_id;
2515
2516 BEGIN
2517 l_balancing_segment := fa_rx_flex_pkg.flex_sql(p_application_id => 101
2518 ,p_id_flex_code => 'GL#'
2519 ,p_id_flex_num => G_STRUCT_NUM
2520 ,p_table_alias => ''
2521 ,p_mode => 'SELECT'
2522 ,p_qualifier => 'GL_BALANCING');
2523
2524 -- The above function will return balancing segment in the form CC.SEGMENT1
2525 -- we need to drop CC. to get the actual balancing segment.
2526 l_balancing_segment := substrb(l_balancing_segment
2527 ,instrb(l_balancing_segment
2528 ,'.') + 1);
2529
2530 -- Fetch the company and acc_no information
2531 l_stmt := ' SELECT ' || l_balancing_segment ||
2532 ' FROM GL_CODE_COMBINATIONS ' ||
2533 ' WHERE CODE_COMBINATION_ID = :LLCID';
2534 OPEN c_crs FOR l_stmt
2535 USING p_ccid;
2536 LOOP
2537 FETCH c_crs
2538 INTO l_company;
2539 EXIT WHEN c_crs%NOTFOUND;
2540 END LOOP;
2541 CLOSE c_crs;
2542 RETURN l_company;
2543 EXCEPTION
2544 WHEN NO_DATA_FOUND THEN
2545 fnd_file.put_line(fnd_file.log,' No record was returned for the GL_Balancing segment. Error : ' || SUBSTR(SQLERRM,1,200));
2546 RETURN NULL;
2547 END;
2548
2549 END JE_ZZ_AUDIT_AP_PKG;