1 PACKAGE BODY JE_ZZ_AUDIT_AP_PKG
2 -- $Header: jezzauditapb.pls 120.13 2007/12/31 06:33:12 anusaxen ship $
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
331 AND zx_det.trx_id = zx_line.trx_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
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
450 IF errbuf IS NOT NULL THEN
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
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
590 G_LEDGER_NAME := l_ledger_name;
587 G_CURR_NAME := l_curr_name;
588 G_LEDGER_CURR := l_functcurr;
589 G_STRUCT_NUM := l_coaid;
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 SUBSTR(ven.vendor_name, 1, 10) ven_name,
646 SUBSTR(ven.segment1, 1, 8) ven_no,
647 SUM(nvl(inv.exchange_rate,1)*zl.rec_nrec_tax_amt) recoverable_tax_amount,
648 zl.rec_nrec_rate rec_per,
649 get_balancing_segment(acctinfo.dist_code_combination_id) company,
650 get_accounting_segment(acctinfo.dist_code_combination_id) acc_no,
651 tax.percentage_rate tax_rate,
652 tax.tax_rate_id tax_id,
653 'DEB/M' tax_type,
654 SUBSTR(inv.invoice_num, 1, 10) inv_no,
655 dis.accounting_date acc_date,
656 NULL payment_amt,
657 nvl(inv.exchange_rate,1)*dis.amount txbl_amt,
658 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),
659 -1 * nvl(inv.exchange_rate,1) * inv.cancelled_amount) prt_inv_amt,
660 NULL,
661 decode(dis.parent_reversal_id, NULL,' ','C'),
662 'JEFRTXDC'
663 FROM zx_rec_nrec_dist zl,
664 zx_rates_b tax,
665 ap_invoices inv,
666 ap_invoice_distributions dis,
667 po_vendors ven,
668 ap_invoice_distributions acctinfo
669 WHERE ((P_LEDGER_ID IS NULL AND P_COMPANY IS NULL AND inv.legal_entity_id = G_LE_ID)
670 OR (P_LEDGER_ID IS NOT NULL AND P_COMPANY IS NULL AND inv.set_of_books_id = P_LEDGER_ID)
671 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))
672 AND ven.vendor_id = inv.vendor_id
673 AND inv.invoice_id = dis.invoice_id
674 AND zl.rec_nrec_tax_dist_id = acctinfo.detail_tax_dist_id
675 AND inv.invoice_id = acctinfo.invoice_id
676 AND zl.trx_id = inv.invoice_id
677 AND zl.recoverable_flag = 'Y'
678 AND tax.tax_rate_id = zl.tax_rate_id
679 AND zl.entity_code = 'AP_INVOICES'
680 AND dis.line_type_lookup_code <> 'PREPAY'
681 AND zl.trx_line_dist_id = dis.invoice_distribution_id
682 AND dis.match_status_flag IS NOT NULL
683 AND dis.accounting_date BETWEEN l_start_date AND l_end_date
684 AND EXISTS ( SELECT 1 FROM zx_lines_det_factors zldf
685 WHERE SUBSTR(zldf.document_sub_type, LENGTH(zldf.document_sub_type) -4, 5) = 'DEB/M'
686 AND zldf.trx_id = inv.invoice_id )
687 GROUP BY zl.trx_line_dist_id,
688 tax.tax_rate_id,
689 tax.percentage_rate,
690 zl.rec_nrec_rate,
691 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),
692 -1 * nvl(inv.exchange_rate,1) * inv.cancelled_amount),
693 SUBSTR(ven.vendor_name, 1, 10),
694 SUBSTR(ven.segment1, 1, 8),
695 inv.invoice_type_lookup_code,
696 get_balancing_segment(acctinfo.dist_code_combination_id),
697 get_accounting_segment(acctinfo.dist_code_combination_id),
698 dis.accounting_date,
699 inv.invoice_num,
700 nvl(inv.exchange_rate,1)*dis.amount,
701 inv.cancelled_date,
705 --Bug 5383153 : Code added to pick up application of prepayment with tax lines
702 decode(dis.parent_reversal_id, NULL,' ','C');
703
704 --Query A2
706 --to DEB/M invoices
707 --This logic is based on the following rules:
708 --The following are the two types of prepayments possible:
709 --
710 --Type I. Prepayment without a tax line
711 --Type II. Prepayment with a tax line (and is of type DEB/M)
712 --
713 --1. Prepayments of Type I will not be shown in the report irrespective of
714 -- whether type are paid or not
715 --2. The application of prepayment of Type I on DEB/M invoices will not impact
716 -- the way the invoice is displayed on the report
717 --3. Prepayments of Type II will be shown in the report based on their invoice
718 -- accounting date. This case is handled by Query A1
719 --4. When Prepayments of Type II are applied to a DEB/M invoice, A negative
720 -- line will appear on the invoice with amounts equal to the applied prepayment
721 -- amounts. This is necessary as tax on prepayment has already be reported (in Case 3).
722 -- Case 4 is handled in this query
723 INSERT
724 INTO jg_zz_vat_trx_gt(
725 jg_info_v2 --ven_name
726 , jg_info_v3 --ven_no
727 , jg_info_n18 --recoverable_tax_amount
728 , jg_info_n2 --rec_per
729 , jg_info_v5 --company
730 , jg_info_v6 --acc_no
731 , jg_info_n4 --tax_rate
732 , jg_info_v7 --tax_id
733 , jg_info_v8 --tax_type
734 , jg_info_v9 --inv_no
735 , jg_info_d1 --acc_date
736 , jg_info_n12 --l_payment_amt
737 , jg_info_n13 --l_txbl_amt
738 , jg_info_n19 --l_prt_inv_amt
739 , jg_info_v10 -- company_desc
740 , jg_info_v11 -- invoice status
741 , jg_info_v30)
742 SELECT
743 SUBSTR(ven.vendor_name, 1, 10) ven_name,
744 SUBSTR(ven.segment1, 1, 8) ven_no,
745 SUM(nvl(inv.exchange_rate,1)*zl.rec_nrec_tax_amt) recoverable_tax_amount,
746 zl.rec_nrec_rate rec_per,
747 get_balancing_segment(acctinfo.dist_code_combination_id) company,
748 get_accounting_segment(acctinfo.dist_code_combination_id) acc_no,
749 tax.percentage_rate tax_rate,
750 tax.tax_rate_id tax_id,
751 'DEB/M' tax_type,
752 SUBSTR(inv.invoice_num, 1, 10) inv_no,
753 dis.accounting_date acc_date,
754 NULL payment_amt,
755 nvl(inv.exchange_rate,1)*dis.amount txbl_amt,
756 decode(inv.cancelled_date,NULL,nvl(inv.exchange_rate,1)*inv.invoice_amount, nvl(inv.exchange_rate,1)*inv.cancelled_amount) prt_inv_amt,
757 NULL,
758 'P',
759 'JEFRTXDC'
760 FROM zx_rec_nrec_dist zl,
761 zx_rates_b tax,
762 ap_invoices inv,
763 ap_invoice_distributions dis,
764 po_vendors ven,
765 ap_invoice_distributions acctinfo
766 WHERE ((P_LEDGER_ID IS NULL AND P_COMPANY IS NULL AND inv.legal_entity_id = G_LE_ID)
767 OR (P_LEDGER_ID IS NOT NULL AND P_COMPANY IS NULL AND inv.set_of_books_id = P_LEDGER_ID)
768 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))
769 AND ven.vendor_id = inv.vendor_id
770 AND inv.invoice_id = dis.invoice_id
771 AND zl.rec_nrec_tax_dist_id = acctinfo.detail_tax_dist_id
772 AND inv.invoice_id = acctinfo.invoice_id
773 AND zl.trx_id = inv.invoice_id
774 AND zl.recoverable_flag = 'Y'
775 AND tax.tax_rate_id = zl.tax_rate_id
776 AND zl.entity_code = 'AP_INVOICES'
777 AND dis.line_type_lookup_code = 'PREPAY'
778 AND zl.trx_line_dist_id = dis.invoice_distribution_id
779 AND dis.match_status_flag IS NOT NULL
780 AND (dis.accounting_date BETWEEN l_start_date AND l_end_date)
781 AND EXISTS ( SELECT 1 FROM zx_lines_det_factors zldf
782 WHERE SUBSTR(zldf.document_sub_type, LENGTH(zldf.document_sub_type) -4, 5) = 'DEB/M'
783 AND zldf.trx_id = inv.invoice_id )
784 GROUP BY zl.trx_line_dist_id,
785 tax.tax_rate_id,
786 tax.percentage_rate,
787 zl.rec_nrec_rate,
788 decode(inv.cancelled_date,NULL,nvl(inv.exchange_rate,1)*inv.invoice_amount, nvl(inv.exchange_rate,1)*inv.cancelled_amount),
789 SUBSTR(ven.vendor_name, 1, 10),
790 SUBSTR(ven.segment1, 1, 8),
791 inv.invoice_type_lookup_code,
792 get_balancing_segment(acctinfo.dist_code_combination_id),
793 get_accounting_segment(acctinfo.dist_code_combination_id),
794 dis.accounting_date,
795 inv.invoice_num,
796 nvl(inv.exchange_rate,1)*dis.amount,
797 inv.cancelled_date,
798 inv.invoice_amount,
799 inv.cancelled_amount;
800
801 --Query B1
802 --Bug 5383171 : Query to pick up all CRE/M invoices that have been paid
803 --This logic is based on the following rules:
804 --1. A CRE/M invoice that is not paid will not be picked up
805 --2. CRE/M invoices will be picked based on their future payment date
806 -- (or payment date, if the future payment date is null)
807 --3. If a CRE/M invoice is partially paid, then the taxable amount and
808 -- the recoverable tax amount will be prorated using this formula
809 -- Taxable amount = (Original Taxable Amount)*(Payment Amount/Invoice Amount)
810 -- Recoverable tax amount = (Original Recoveral tax amount)*(Payment Amount/Invoice Amount)
811 --4. A CRE/M invoice that does not have a tax line will not be picked
815 -- picked up for the invoice. The payment amount, taxable amount and
812 --5. A CRE/M prepayment invoice will be picked up if it satisfies conditions 1 to 4
813 --6. Invoice cancellation does not affect a CRE/M invoice
814 --7. Voiding a payment will result in an additional negative line being
816 -- recoverable tax amount will be negative for this line
817 INSERT
818 INTO jg_zz_vat_trx_gt(
819 jg_info_v2 --ven_name
820 , jg_info_v3 --ven_no
821 , jg_info_n18 --recoverable_tax_amount
822 , jg_info_n2 --rec_per
823 , jg_info_v5 --company
824 , jg_info_v6 --acc_no
825 , jg_info_n4 --tax_rate
826 , jg_info_v7 --tax_id
827 , jg_info_v8 --tax_type
828 , jg_info_v9 --inv_no
829 , jg_info_d1 --acc_date
830 , jg_info_n12 --l_payment_amt
831 , jg_info_n13 --l_txbl_amt
832 , jg_info_n19 --l_prt_inv_amt
833 , jg_info_v10 -- company_desc
834 , jg_info_v11 -- invoice status
835 , jg_info_v30)
836 SELECT
837 SUBSTR(ven.vendor_name, 1, 10) ven_name,
838 SUBSTR(ven.segment1, 1, 8) ven_no,
839 SUM(nvl(inv.exchange_rate,1)*zl.rec_nrec_tax_amt) * ((nvl(aip.exchange_rate,1)*aip.amount + nvl(aip.exchange_rate,1)*aip.discount_taken)
840 /decode(inv.cancelled_date,NULL,nvl(inv.exchange_rate,1)*inv.invoice_amount, nvl(inv.exchange_rate,1)*inv.cancelled_amount)) recoverable_tax_amount,
841 zl.rec_nrec_rate rec_per,
842 get_balancing_segment(acctinfo.dist_code_combination_id) company,
843 get_accounting_segment(acctinfo.dist_code_combination_id) acc_no,
844 tax.percentage_rate tax_rate,
845 tax.tax_rate_id tax_id,
846 'CRE/M' tax_type,
847 SUBSTR(inv.invoice_num, 1, 10) inv_no,
848 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,
849 (nvl(aip.exchange_rate,1)*aip.amount + nvl(aip.exchange_rate,1)*aip.discount_taken) payment_amt,
850 nvl(inv.exchange_rate,1)*dis.amount * ((nvl(aip.exchange_rate,1)*aip.amount + nvl(aip.exchange_rate,1)*aip.discount_taken)
851 /decode(inv.cancelled_date,NULL,nvl(inv.exchange_rate,1)*inv.invoice_amount, nvl(inv.exchange_rate,1)*inv.cancelled_amount)) txbl_amt,
852 decode(inv.cancelled_date,NULL,nvl(inv.exchange_rate,1)*inv.invoice_amount, nvl(inv.exchange_rate,1)*inv.cancelled_amount) prt_inv_amt,
853 NULL,
854 decode(aip.reversal_inv_pmt_id,NULL,' ','V'),
855 'JEFRTXDC'
856 FROM zx_rec_nrec_dist zl,
857 zx_rates_b tax,
858 ap_invoices inv,
859 ap_invoice_distributions dis,
860 po_vendors ven,
861 ap_invoice_payments aip,
862 ap_checks_all chk,
863 ap_invoice_distributions acctinfo
864 WHERE ((p_ledger_id IS NULL
865 AND p_company IS NULL
866 AND inv.legal_entity_id = g_le_id) OR(p_ledger_id IS NOT NULL
867 AND p_company IS NULL
868 AND inv.set_of_books_id = p_ledger_id) OR(p_company IS NOT NULL
869 AND inv.set_of_books_id = p_ledger_id
870 AND get_balancing_segment(dis.dist_code_combination_id) = p_company))
871 AND ven.vendor_id = inv.vendor_id
872 AND inv.invoice_id = dis.invoice_id
873 AND aip.invoice_id = inv.invoice_id
874 AND zl.rec_nrec_tax_dist_id = acctinfo.detail_tax_dist_id
875 AND inv.invoice_id = acctinfo.invoice_id
876 AND chk.check_id = aip.check_id
877 AND zl.trx_id = inv.invoice_id
878 AND zl.recoverable_flag = 'Y'
879 AND tax.tax_rate_id = zl.tax_rate_id
880 AND zl.entity_code = 'AP_INVOICES'
881 AND zl.trx_line_dist_id = dis.invoice_distribution_id
882 AND dis.line_type_lookup_code <> 'PREPAY'
883 AND dis.match_status_flag IS NOT NULL
884 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
885 AND dis.parent_reversal_id IS NULL
886 AND EXISTS ( SELECT 1 FROM zx_lines_det_factors zldf WHERE
887 SUBSTR(zldf.document_sub_type, LENGTH(zldf.document_sub_type) -4, 5) = 'CRE/M'
888 AND zldf.trx_id = inv.invoice_id )
889 GROUP BY zl.trx_line_dist_id,
890 tax.tax_rate_id,
891 tax.percentage_rate,
892 zl.rec_nrec_rate,
893 inv.cancelled_amount,
894 SUBSTR(ven.vendor_name, 1, 10),
895 SUBSTR(ven.segment1, 1, 8),
896 inv.invoice_type_lookup_code,
897 get_balancing_segment(acctinfo.dist_code_combination_id),
901 dis.amount,
898 get_accounting_segment(acctinfo.dist_code_combination_id),
899 aip.accounting_date,
900 inv.invoice_num,
902 inv.cancelled_date,
903 aip.amount,
904 aip.discount_taken,
905 inv.invoice_amount,
906 chk.future_pay_due_date,
907 decode(aip.reversal_inv_pmt_id,NULL,' ','V'),
908 inv.exchange_rate,
909 aip.exchange_rate;
910
911 --Query B2
912 --Bug 5383181 : Query to pick up all prepayments with tax lines that have
913 --been applied to a CRE/M invoice
914 --Prepayments with tax lines will be handled as follows:
915 --1. Prepayments of this type will be shown in the report based on their payment
916 -- due date (or payment date, if payment due date is null). This is handled
917 -- in the previous query
918 --2. When such prepayments are applied to a CRE/M invoice, a postive
919 -- line will appear on the invoice with tax amount equal to the prepayment tax
920 -- amount. A corrosponding negative line will also appear in order to
921 -- compensate the positive line. The sum of payment, taxable and recoverable
922 -- tax amounts of both the lines will be zero. This is necessary as the tax on
923 -- prepayment has already be reported in case 1 and the lines are
924 -- displayed on the report for informational purposes only.
925 --
926 -- The first insert is for the positive line, the second insert
927 -- is for the negative line
928 INSERT
929 INTO jg_zz_vat_trx_gt(
930 jg_info_v2 --ven_name
931 , jg_info_v3 --ven_no
932 , jg_info_n18 --recoverable_tax_amount
933 , jg_info_n2 --rec_per
934 , jg_info_v5 --company
935 , jg_info_v6 --acc_no
936 , jg_info_n4 --tax_rate
937 , jg_info_v7 --tax_id
938 , jg_info_v8 --tax_type
939 , jg_info_v9 --inv_no
940 , jg_info_d1 --acc_date
941 , jg_info_n12 --l_payment_amt
942 , jg_info_n13 --l_txbl_amt
943 , jg_info_n19 --l_prt_inv_amt
944 , jg_info_v10 -- company_desc
945 , jg_info_v11 -- invoice status
946 , jg_info_v30)
947 SELECT
948 SUBSTR(ven.vendor_name, 1, 10) ven_name,
949 SUBSTR(ven.segment1, 1, 8) ven_no,
950 SUM(nvl(inv.exchange_rate,1)*zl.rec_nrec_tax_amt)*-1 recoverable_tax_amount,
951 zl.rec_nrec_rate rec_per,
952 get_balancing_segment(acctinfo.dist_code_combination_id) company,
953 get_accounting_segment(acctinfo.dist_code_combination_id) acc_no,
954 tax.percentage_rate tax_rate,
955 tax.tax_rate_id tax_id,
956 'CRE/M' tax_type,
957 SUBSTR(inv.invoice_num, 1, 10) inv_no,
958 dis.accounting_date acc_date,
959 (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,
960 nvl(inv.exchange_rate,1)*dis.amount*-1 txbl_amt,
961 decode(inv.cancelled_date,NULL,nvl(inv.exchange_rate,1)*inv.invoice_amount, nvl(inv.exchange_rate,1)*inv.cancelled_amount) prt_inv_amt,
962 NULL,
963 'P',
964 'JEFRTXDC'
965 FROM zx_rec_nrec_dist zl,
966 zx_rates_b tax,
967 ap_invoices inv,
968 ap_invoice_distributions dis,
969 po_vendors ven,
970 zx_rec_nrec_dist nrec,
971 ap_invoice_distributions acctinfo
972 WHERE ((P_LEDGER_ID IS NULL AND P_COMPANY IS NULL AND inv.legal_entity_id = G_LE_ID)
973 OR (P_LEDGER_ID IS NOT NULL AND P_COMPANY IS NULL AND inv.set_of_books_id = P_LEDGER_ID)
974 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))
975 AND ven.vendor_id = inv.vendor_id
976 AND inv.invoice_id = dis.invoice_id
977 AND zl.rec_nrec_tax_dist_id = acctinfo.detail_tax_dist_id
978 AND inv.invoice_id = acctinfo.invoice_id
979 AND zl.trx_id = inv.invoice_id
980 AND zl.recoverable_flag = 'Y'
981 AND tax.tax_rate_id = zl.tax_rate_id
982 AND zl.entity_code = 'AP_INVOICES'
983 AND zl.trx_line_dist_id = dis.invoice_distribution_id
984 AND dis.match_status_flag IS NOT NULL
985 AND dis.accounting_date BETWEEN l_start_date AND l_end_date
986 AND dis.line_type_lookup_code = 'PREPAY'
987 AND nrec.entity_code = 'AP_INVOICES'
988 AND nrec.trx_line_dist_id = dis.invoice_distribution_id
989 AND nrec.trx_id = inv.invoice_id
990 AND nrec.recoverable_flag = 'N'
991 AND EXISTS ( SELECT 1 FROM zx_lines_det_factors zldf WHERE
992 SUBSTR(zldf.document_sub_type, LENGTH(zldf.document_sub_type) -4, 5) = 'CRE/M'
993 AND zldf.trx_id = inv.invoice_id )
994 GROUP BY zl.trx_line_dist_id,
995 tax.tax_rate_id,
996 tax.percentage_rate,
997 zl.rec_nrec_rate,
998 decode(inv.cancelled_date,NULL,nvl(inv.exchange_rate,1)*inv.invoice_amount, nvl(inv.exchange_rate,1)*inv.cancelled_amount),
999 SUBSTR(ven.vendor_name, 1, 10),
1000 SUBSTR(ven.segment1, 1, 8),
1001 inv.invoice_type_lookup_code,
1002 get_balancing_segment(acctinfo.dist_code_combination_id),
1003 get_accounting_segment(acctinfo.dist_code_combination_id),
1004 dis.accounting_date,
1005 inv.invoice_num,
1006 dis.amount,
1007 inv.cancelled_date,
1008 inv.exchange_rate;
1009 INSERT
1010 INTO jg_zz_vat_trx_gt(
1011 jg_info_v2 --ven_name
1012 , jg_info_v3 --ven_no
1013 , jg_info_n18 --recoverable_tax_amount
1014 , jg_info_n2 --rec_per
1015 , jg_info_v5 --company
1016 , jg_info_v6 --acc_no
1017 , jg_info_n4 --tax_rate
1018 , jg_info_v7 --tax_id
1022 , jg_info_n12 --l_payment_amt
1019 , jg_info_v8 --tax_type
1020 , jg_info_v9 --inv_no
1021 , jg_info_d1 --acc_date
1023 , jg_info_n13 --l_txbl_amt
1024 , jg_info_n19 --l_prt_inv_amt
1025 , jg_info_v10 -- company_desc
1026 , jg_info_v11 -- invoice status
1027 , jg_info_v30)
1028 SELECT
1029 SUBSTR(ven.vendor_name, 1, 10) ven_name,
1030 SUBSTR(ven.segment1, 1, 8) ven_no,
1031 SUM(nvl(inv.exchange_rate,1)*zl.rec_nrec_tax_amt) recoverable_tax_amount,
1032 zl.rec_nrec_rate rec_per,
1033 get_balancing_segment(acctinfo.dist_code_combination_id) company,
1034 get_accounting_segment(acctinfo.dist_code_combination_id) acc_no,
1035 tax.percentage_rate tax_rate,
1036 tax.tax_rate_id tax_id,
1037 'CRE/M' tax_type,
1038 SUBSTR(inv.invoice_num, 1, 10) inv_no,
1039 dis.accounting_date acc_date,
1040 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,
1041 nvl(inv.exchange_rate,1)*dis.amount txbl_amt,
1042 decode(inv.cancelled_date,NULL,nvl(inv.exchange_rate,1)*inv.invoice_amount, nvl(inv.exchange_rate,1)*inv.cancelled_amount) prt_inv_amt,
1043 NULL,
1044 'P',
1045 'JEFRTXDC'
1046 FROM zx_rec_nrec_dist zl,
1047 zx_rates_b tax,
1048 ap_invoices inv,
1049 ap_invoice_distributions dis,
1050 po_vendors ven,
1051 zx_rec_nrec_dist nrec,
1052 ap_invoice_distributions acctinfo
1053 WHERE ((P_LEDGER_ID IS NULL AND P_COMPANY IS NULL AND inv.legal_entity_id = G_LE_ID)
1054 OR (P_LEDGER_ID IS NOT NULL AND P_COMPANY IS NULL AND inv.set_of_books_id = P_LEDGER_ID)
1055 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))
1056 AND ven.vendor_id = inv.vendor_id
1057 AND inv.invoice_id = dis.invoice_id
1058 AND zl.rec_nrec_tax_dist_id = acctinfo.detail_tax_dist_id
1059 AND inv.invoice_id = acctinfo.invoice_id
1060 AND zl.trx_id = inv.invoice_id
1061 AND zl.recoverable_flag = 'Y'
1062 AND tax.tax_rate_id = zl.tax_rate_id
1063 AND zl.entity_code = 'AP_INVOICES'
1064 AND zl.trx_line_dist_id = dis.invoice_distribution_id
1065 AND dis.match_status_flag IS NOT NULL
1066 AND dis.accounting_date BETWEEN l_start_date AND l_end_date
1067 AND dis.line_type_lookup_code = 'PREPAY'
1068 AND nrec.entity_code = 'AP_INVOICES'
1069 AND nrec.trx_line_dist_id = dis.invoice_distribution_id
1070 AND nrec.trx_id = inv.invoice_id
1071 AND nrec.recoverable_flag = 'N'
1072 AND EXISTS ( SELECT 1 FROM zx_lines_det_factors zldf WHERE
1073 SUBSTR(zldf.document_sub_type, LENGTH(zldf.document_sub_type) -4, 5) = 'CRE/M'
1074 AND zldf.trx_id = inv.invoice_id )
1075 GROUP BY zl.trx_line_dist_id,
1076 tax.tax_rate_id,
1077 tax.percentage_rate,
1078 zl.rec_nrec_rate,
1079 decode(inv.cancelled_date,NULL,nvl(inv.exchange_rate,1)*inv.invoice_amount, nvl(inv.exchange_rate,1)*inv.cancelled_amount),
1080 SUBSTR(ven.vendor_name, 1, 10),
1081 SUBSTR(ven.segment1, 1, 8),
1082 inv.invoice_type_lookup_code,
1083 get_balancing_segment(acctinfo.dist_code_combination_id),
1084 get_accounting_segment(acctinfo.dist_code_combination_id),
1085 dis.accounting_date,
1086 inv.invoice_num,
1087 dis.amount,
1088 inv.cancelled_date,
1089 inv.exchange_rate;
1090
1091
1092 --Query B3
1093 --Bug 5383181 : Query to pick up all prepayments without a tax line that have
1094 --been applied to a CRE/M invoice
1095 --Prepayments without tax lines will be handled as follows:
1096 --1. Prepayments of this type will not be shown in the report irrespective
1097 -- of whether they are paid or not
1098 --2. The application of such prepayments on CRE/M invoices will be consider
1099 -- as a normal payment. In such a case the recoveral tax amount and the
1100 -- taxable amount will be prorated based on prepayment amount
1101 -- Taxable amount = (Original Taxable Amount)*(prepayment Amount/Invoice Amount)
1102 -- Recoverable tax amount = (Original Recoveral tax amount)*(prepayment Amount/Invoice Amount)
1103 INSERT
1104 INTO jg_zz_vat_trx_gt(
1105 jg_info_v2 --ven_name
1106 , jg_info_v3 --ven_no
1107 , jg_info_n18 --recoverable_tax_amount
1108 , jg_info_n2 --rec_per
1109 , jg_info_v5 --company
1110 , jg_info_v6 --acc_no
1111 , jg_info_n4 --tax_rate
1112 , jg_info_v7 --tax_id
1113 , jg_info_v8 --tax_type
1114 , jg_info_v9 --inv_no
1115 , jg_info_d1 --acc_date
1116 , jg_info_n12 --l_payment_amt
1117 , jg_info_n13 --l_txbl_amt
1118 , jg_info_n19 --l_prt_inv_amt
1119 , jg_info_v10 -- company_desc
1120 , jg_info_v11 -- invoice status
1121 , jg_info_v30)
1122 SELECT
1126 /decode(inv.cancelled_date,NULL,nvl(inv.exchange_rate,1)*inv.invoice_amount, nvl(inv.exchange_rate,1)*inv.cancelled_amount)) recoverable_tax_amount,
1123 SUBSTR(ven.vendor_name, 1, 10) ven_name,
1124 SUBSTR(ven.segment1, 1, 8) ven_no,
1125 SUM(nvl(inv.exchange_rate,1)*zl.rec_nrec_tax_amt) * ((-1 * nvl(inv.exchange_rate,1) * pre.amount)
1127 zl.rec_nrec_rate rec_per,
1128 get_balancing_segment(acctinfo.dist_code_combination_id) company,
1129 get_accounting_segment(acctinfo.dist_code_combination_id) acc_no,
1130 tax.percentage_rate tax_rate,
1131 tax.tax_rate_id tax_id,
1132 'CRE/M' tax_type,
1133 SUBSTR(inv.invoice_num, 1, 10) inv_no,
1134 pre.accounting_date acc_date,
1135 -1 * nvl(inv.exchange_rate,1) * pre.amount payment_amt,
1136 nvl(inv.exchange_rate,1) * dis.amount * ((-1 * nvl(inv.exchange_rate,1) * pre.amount)
1137 /decode(inv.cancelled_date,NULL,nvl(inv.exchange_rate,1)*inv.invoice_amount, nvl(inv.exchange_rate,1)*inv.cancelled_amount)) txbl_amt,
1138 decode(inv.cancelled_date,NULL,nvl(inv.exchange_rate,1)*inv.invoice_amount, nvl(inv.exchange_rate,1)*inv.cancelled_amount) prt_inv_amt,
1139 NULL,
1140 'PP',
1141 'JEFRTXDC'
1142 FROM zx_rec_nrec_dist zl,
1143 zx_rates_b tax,
1144 ap_invoices inv,
1145 ap_invoice_distributions dis,
1146 po_vendors ven,
1147 ap_invoice_distributions pre,
1148 ap_invoice_distributions acctinfo
1149 WHERE ((p_ledger_id IS NULL
1150 AND p_company IS NULL
1151 AND inv.legal_entity_id = g_le_id) OR(p_ledger_id IS NOT NULL
1152 AND p_company IS NULL
1153 AND inv.set_of_books_id = p_ledger_id) OR(p_company IS NOT NULL
1154 AND inv.set_of_books_id = p_ledger_id
1155 AND get_balancing_segment(dis.dist_code_combination_id) = p_company))
1156 AND ven.vendor_id = inv.vendor_id
1157 AND inv.invoice_id = dis.invoice_id
1158 AND inv.invoice_id = pre.invoice_id
1159 AND zl.rec_nrec_tax_dist_id = acctinfo.detail_tax_dist_id
1160 AND inv.invoice_id = acctinfo.invoice_id
1161 AND zl.trx_id = inv.invoice_id
1162 AND zl.recoverable_flag = 'Y'
1163 AND tax.tax_rate_id = zl.tax_rate_id
1164 AND zl.entity_code = 'AP_INVOICES'
1165 AND zl.trx_line_dist_id = dis.invoice_distribution_id
1166 AND dis.line_type_lookup_code = 'ITEM'
1167 AND dis.match_status_flag IS NOT NULL
1168 AND pre.line_type_lookup_code = 'PREPAY'
1169 AND pre.match_status_flag IS NOT NULL
1170 AND NOT EXISTS ( SELECT 1 FROM zx_rec_nrec_dist zlp where
1171 zlp.trx_id = inv.invoice_id
1172 AND zlp.entity_code = 'AP_INVOICES'
1173 AND zlp.trx_line_dist_id = pre.invoice_distribution_id )
1174 AND pre.accounting_date BETWEEN l_start_date AND l_end_date
1175 AND dis.parent_reversal_id IS NULL
1176 AND EXISTS ( SELECT 1 FROM zx_lines_det_factors zldf WHERE
1177 SUBSTR(zldf.document_sub_type, LENGTH(zldf.document_sub_type) -4, 5) = 'CRE/M'
1178 AND zldf.trx_id = inv.invoice_id )
1179 GROUP BY zl.trx_line_dist_id,
1180 tax.tax_rate_id,
1181 tax.percentage_rate,
1182 zl.rec_nrec_rate,
1183 inv.cancelled_amount,
1184 SUBSTR(ven.vendor_name, 1, 10),
1185 SUBSTR(ven.segment1, 1, 8),
1186 inv.invoice_type_lookup_code,
1187 get_balancing_segment(acctinfo.dist_code_combination_id),
1188 get_accounting_segment(acctinfo.dist_code_combination_id),
1189 pre.accounting_date,
1190 inv.invoice_num,
1191 dis.amount,
1192 inv.cancelled_date,
1193 pre.amount,
1194 inv.invoice_amount,
1195 inv.exchange_rate;
1196
1197
1198 --Code to updated the records with the balancing segment description
1199 FOR c_balancing_segment IN company_info
1200 LOOP
1201 SELECT distinct ffv.description
1202 INTO l_company_desc
1203 FROM fnd_id_flex_segments_vl fif
1204 ,fnd_flex_values_vl ffv
1205 WHERE fif.id_flex_code = 'GL#'
1206 AND fif.application_id = 101
1207 AND fif.id_flex_num = g_struct_num
1208 AND ffv.flex_value = c_balancing_segment.jg_info_v5
1209 AND ffv.flex_value_set_id = fif.flex_value_set_id;
1210
1211 UPDATE jg_zz_vat_trx_gt
1212 SET jg_info_v10 = l_company_desc
1213 WHERE jg_info_v5 = c_balancing_segment.jg_info_v5;
1214 END LOOP;
1215 CLOSE company_info;
1216
1217
1218 EXCEPTION
1219 WHEN OTHERS THEN
1220 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));
1221 END;
1222
1223 ELSE
1224 --If calling report is not JEFRTXDC
1225 BEGIN
1226 FOR c_data_rec1 IN C_GENERIC(l_start_date, l_end_date)
1227 LOOP
1228
1229 FOR c_trx_ccid IN c_get_trx_ccid (c_data_rec1.invoice_id
1233 , c_data_rec1.entity_code)
1230 , c_data_rec1.application_id
1231 , c_data_rec1.event_class_code
1232 , c_data_rec1.trx_line_id
1234 LOOP
1235 l_trx_ccid := c_trx_ccid.code_combination_id;
1236 END LOOP;
1237
1238 FOR c_tax_ccid IN c_get_tax_ccid (c_data_rec1.invoice_id
1239 , c_data_rec1.application_id
1240 , c_data_rec1.event_class_code
1241 , c_data_rec1.trx_line_id
1242 , c_data_rec1.entity_code)
1243 LOOP
1244 l_tax_ccid := c_tax_ccid.code_combination_id;
1245 END LOOP;
1246
1247 l_acc_no := get_accounting_segment(l_tax_ccid);
1248 l_company := get_balancing_segment(l_trx_ccid, l_company_desc);
1249
1250 INSERT INTO JG_ZZ_VAT_TRX_GT
1251 (jg_info_v1 --ven_name
1252 ,jg_info_v2 --ven_no
1253 ,jg_info_v3 --inv_type
1254 ,jg_info_n1 --tax_amt
1255 ,jg_info_n2 --rec_per
1256 ,jg_info_v4 --company
1257 ,jg_info_v5 --acc_no
1258 ,jg_info_n3 --tax_rate
1259 ,jg_info_v6 --tax_id
1260 ,jg_info_v7 --offset_tax_rate_code
1261 ,jg_info_v8 --tax_type
1262 ,jg_info_v9 --inv_no
1263 ,jg_info_d1 --acc_date
1264 ,jg_info_n4 --invoice_id
1265 ,jg_info_d2 --cancelled_date
1266 ,jg_info_n5 --item_line_cnt
1267 ,jg_info_n6 --charge_dist_id
1268 ,jg_info_d3 --check_void_date
1269 ,jg_info_n7 --pay_amt
1270 ,jg_info_v10 --line_type_lookup_code
1271 ,jg_info_v11 --line_type_lookup_code_item
1272 ,jg_info_v12 --line_type_lookup_code_prepay
1273 ,jg_info_v13 --reversal_flag_item
1274 ,jg_info_v14 --reversal_flag_pay
1275 ,jg_info_v15 --reversal_flag_prepay
1276 ,jg_info_n8 --parent_reversal_id
1277 ,jg_info_n9 --base_amount
1278 ,jg_info_n10 --invoice_amount
1279 ,jg_info_d4 --void_date
1280 ,jg_info_d5 --future_pay_due_date
1281 ,jg_info_d6 --check_date
1282 ,jg_info_v16 --payment_status_flag
1283 ,jg_info_d7 --accounting_date
1284 ,jg_info_n11 --reversal_inv_pmt_id
1285 ,jg_info_v17 --c_company
1286 )
1287 VALUES
1288 (c_data_rec1.ven_name --jg_info_v1
1289 ,c_data_rec1.ven_no --jg_info_v2
1290 ,c_data_rec1.inv_type --jg_info_v3
1291 ,c_data_rec1.tax_amt --jg_info_n1
1292 ,c_data_rec1.rec_per --jg_info_n2
1293 ,c_data_rec1.company --jg_info_v4
1294 ,c_data_rec1.acc_no --jg_info_v5
1295 ,c_data_rec1.tax_rate --jg_info_n3
1296 ,c_data_rec1.tax_id --jg_info_v6
1297 ,c_data_rec1.offset_tax_rate_code --jg_info_v7
1298 ,c_data_rec1.tax_type --jg_info_v8
1299 ,c_data_rec1.inv_no --jg_info_v9
1300 ,c_data_rec1.acc_date --jg_info_d1
1301 ,c_data_rec1.invoice_id --jg_info_n4
1302 ,c_data_rec1.cancelled_date --jg_info_d2
1303 ,c_data_rec1.item_line_cnt --jg_info_n5
1304 ,c_data_rec1.charge_dist_id --jg_info_n6
1305 ,c_data_rec1.check_void_date --jg_info_d3
1306 ,c_data_rec1.pay_amt --jg_info_n7
1307 ,c_data_rec1.line_type_lookup_code --jg_info_v10
1308 ,c_data_rec1.line_type_lookup_code_item --jg_info_v11
1309 ,c_data_rec1.line_type_lookup_code_prepay --jg_info_v12
1310 ,c_data_rec1.reversal_flag_item --jg_info_v13
1311 ,c_data_rec1.reversal_flag_pay --jg_info_v14
1312 ,c_data_rec1.reversal_flag_prepay --jg_info_v15
1313 ,c_data_rec1.parent_reversal_id --jg_info_n8
1314 ,c_data_rec1.base_amount --jg_info_n9
1315 ,c_data_rec1.invoice_amount --jg_info_n10
1316 ,c_data_rec1.void_date --jg_info_d4
1317 ,c_data_rec1.future_pay_due_date --jg_info_d5
1318 ,c_data_rec1.check_date --jg_info_d6
1322 ,l_company_desc --jg_info_v17
1319 ,c_data_rec1.payment_status_flag --jg_info_v16
1320 ,c_data_rec1.accounting_date --jg_info_d7
1321 ,c_data_rec1.reversal_inv_pmt_id --jg_info_n11
1323 );
1324
1325 END LOOP;
1326
1327 EXCEPTION
1328 WHEN OTHERS THEN
1329 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));
1330 END;
1331 END IF;
1332 IF (P_COMPANY IS NOT NULL) THEN
1333
1334 get_boiler_plates;
1335
1336 END IF;
1337 EXCEPTION
1338 WHEN OTHERS THEN
1339 fnd_file.put_line(fnd_file.log,' An unexpected error occured in the before report trigger. Error : ' || SUBSTR(SQLERRM, 1, 200));
1340 RETURN FALSE;
1341 END;
1342 fnd_file.put_line(fnd_file.log,'End of the before report trigger ');
1343
1344 RETURN(TRUE);
1345 END;
1346
1347 /*
1348 REM +======================================================================+
1349 REM Name: C_PRT_INV_AMTFormula
1350 REM
1351 REM Description: This function is used to calculate the invoice
1352 REM amount.
1353 REM
1354 REM Parameters:
1355 REM p_cancelled_date => The invoice cancellation date
1356 REM P_START_DATE => Period Start date
1357 REM P_END_DATE => Period end date
1358 REM p_real_inv_amt => actual invoice amount
1359 REM +======================================================================+
1360 */
1361 FUNCTION C_PRT_INV_AMTFormula
1362 (
1363 p_cancelled_date IN DATE
1364 ,p_start_date IN DATE
1365 ,p_end_date IN DATE
1366 ,p_real_inv_amt IN NUMBER
1367 ) RETURN VARCHAR2 IS
1368 BEGIN
1369
1370 IF p_cancelled_date IS NOT NULL
1371 AND p_cancelled_date BETWEEN p_start_date AND p_end_date THEN
1372 RETURN(0);
1373 ELSE
1374 RETURN(p_real_inv_amt);
1375
1376 END IF;
1377 END;
1378
1379 /*
1380 REM +======================================================================+
1381 REM Name: g_companygroupfilter
1382 REM
1383 REM Description: This function is used as a group filter for grouping the data
1384 REM
1385 REM Parameters:
1386 REM +======================================================================+
1387 */
1388 FUNCTION g_companygroupfilter(company IN VARCHAR2) RETURN BOOLEAN IS
1389 BEGIN
1390
1391 IF (G_DATA_FOUND IS NULL) THEN
1392 G_DATA_FOUND := company;
1393 RETURN(TRUE);
1394 ELSE
1395 RETURN(TRUE);
1396 END IF;
1397 RETURN(TRUE);
1398 END;
1399
1400 /*
1401 REM +======================================================================+
1402 REM Name: get_lookup_meaning
1403 REM
1404 REM Description: This procedure returns the lookup meaning of the lookup code provided
1405 REM
1406 REM Parameters:
1407 REM p_lookup_type => The lookup type
1408 REM p_lookup_code => Lookup code
1409 REM x_lookup_meaning => Lookup meaning.
1410 REM +======================================================================+
1411 */
1412 PROCEDURE get_lookup_meaning
1413 (
1414 p_lookup_type IN VARCHAR2
1415 ,p_lookup_code IN VARCHAR2
1416 ,x_lookup_meaning IN OUT NOCOPY VARCHAR2
1417 ) IS
1418
1419 w_meaning VARCHAR2(80);
1420
1421 CURSOR c_lookup_meaning (p_lookup_type VARCHAR2
1422 ,p_lookup_code VARCHAR2)
1423 IS
1424 SELECT meaning
1425 FROM fnd_lookups
1426 WHERE lookup_type = p_lookup_type
1427 AND lookup_code = p_lookup_code;
1428
1429 BEGIN
1430
1431 FOR c_meaning IN c_lookup_meaning (p_lookup_type, p_lookup_code)
1432 LOOP
1433 w_meaning := c_meaning.meaning;
1434 END LOOP;
1435
1436 x_lookup_meaning := w_meaning;
1437
1438 EXCEPTION
1439 WHEN no_data_found THEN
1440 x_lookup_meaning := NULL;
1441 END;
1442
1443 /*
1444 REM +======================================================================+
1445 REM Name: get_boiler_plates
1446 REM
1447 REM Description: This procedure sets the company title and industry code
1448 REM
1449 REM Parameters:
1450 REM +======================================================================+
1451 */
1452 PROCEDURE get_boiler_plates IS
1453
1454 w_industry_code VARCHAR2(20);
1455 w_industry_stat VARCHAR2(20);
1456
1457 BEGIN
1458
1459 IF fnd_installation.get(0, 0, w_industry_stat, w_industry_code) THEN
1460 IF w_industry_code = 'C' THEN
1461 G_company_title := NULL;
1462 ELSE
1463 get_lookup_meaning('IND_COMPANY', w_industry_code, G_company_title);
1464 END IF;
1465 END IF;
1466
1467 G_INDUSTRY_CODE := w_Industry_code;
1468
1469 END;
1470
1471
1472 /*
1473 REM +======================================================================+
1474 REM Name: c_payment_amtformula
1475 REM
1476 REM Description: This function calculates the payment amount
1477 REM
1478 REM Parameters:
1482 REM p_pay_amt =>
1479 REM p_tax_type => Tax type
1480 REM p_inv_type => Invoice Type
1481 REM p_invoice_id => Invoice Id
1483 REM p_start_date => Period start date
1484 REM p_end_date => period end date
1485 REM +======================================================================+
1486 */
1487 FUNCTION c_payment_amtformula
1488 (
1489 p_tax_type IN VARCHAR2
1490 ,p_inv_type IN VARCHAR2
1491 ,p_invoice_id IN NUMBER
1492 ,p_pay_amt IN NUMBER
1493 ,p_start_date IN DATE
1494 ,p_end_date IN DATE
1495 ) RETURN NUMBER IS
1496 l_prep_pay_amt NUMBER := 0;
1497 l_payment_amt NUMBER := 0;
1498 l_ppp_amt NUMBER := 0;
1499 l_total_payment NUMBER := 0;
1500
1501 CURSOR c_get_amount (p_invoice_id NUMBER
1502 ,p_start_date DATE
1503 ,p_end_date DATE)
1504 IS
1505 SELECT SUM(nvl(p.invoice_base_amount, p.amount)) amount
1506 FROM ap_invoice_payments p
1507 WHERE p.invoice_id = p_invoice_id
1508 AND p.accounting_date BETWEEN p_start_date AND p_end_date;
1509
1510 CURSOR c_get_prepay_amount ( p_invoice_id NUMBER
1511 ,p_start_date DATE
1512 ,p_end_date DATE)
1513 IS
1514 SELECT SUM(nvl(pp.base_amount, pp.amount) + nvl(ppt.base_amount, ppt.amount)) prepay_amount
1515 FROM ap_invoice_distributions pp
1516 ,ap_invoice_distributions ppt
1517 WHERE pp.invoice_id = p_invoice_id
1518 AND ppt.invoice_id = p_invoice_id
1519 AND pp.line_type_lookup_code = 'PREPAY'
1520 AND pp.charge_applicable_to_dist_id = ppt.invoice_distribution_id
1521 AND nvl(pp.reversal_flag, 'N') <> 'Y'
1522 AND pp.accounting_date BETWEEN p_start_date AND p_end_date;
1523
1524 CURSOR c_get_pre_pay_amount (p_invoice_id NUMBER
1525 ,p_start_date DATE
1526 ,p_end_date DATE)
1527 IS
1528 SELECT (-1) * SUM(nvl(ppp.base_amount, ppp.amount)) prepay_amount
1529 FROM ap_invoice_distributions ppp
1530 WHERE ppp.invoice_id = p_invoice_id
1531 AND ppp.line_type_lookup_code = 'PREPAY'
1532 AND nvl(ppp.reversal_flag, 'N') <> 'Y'
1533 AND ppp.accounting_date BETWEEN p_start_date AND p_end_date
1534 AND NOT EXISTS (SELECT 'x'
1535 FROM ap_invoice_distributions ptax
1536 WHERE ptax.invoice_id = p_invoice_id
1537 AND ppp.charge_applicable_to_dist_id = ptax.invoice_distribution_id);
1538
1539
1540 BEGIN
1541
1542 IF p_tax_type = 'CRE/M'
1543 AND p_inv_type <> 'PREPAYMENT_APPLICATION' THEN
1544 IF p_inv_type LIKE '%JEPP' THEN
1545
1546 FOR c_amount IN c_get_amount (p_invoice_id, p_start_date, p_end_date)
1547 LOOP
1548 l_payment_amt := c_amount.amount;
1549 END LOOP;
1550
1551 FOR c_prepay_amount IN c_get_prepay_amount (p_invoice_id, p_start_date, p_end_date)
1552 LOOP
1553 l_prep_pay_amt := c_prepay_amount.prepay_amount;
1554 END LOOP;
1555
1556 IF l_prep_pay_amt IS NULL THEN
1557
1558 FOR c_pre_pay_amount IN c_get_pre_pay_amount (p_invoice_id, p_start_date, p_end_date)
1559 LOOP
1560 l_prep_pay_amt := c_pre_pay_amount.prepay_amount;
1561 END LOOP;
1562
1563 END IF;
1564
1565 l_total_payment := abs(l_prep_pay_amt) + nvl(l_payment_amt, 0);
1566
1567 RETURN(l_total_payment);
1568
1569 ELSE
1570
1571 l_prep_pay_amt := 0;
1572
1573 END IF;
1574
1575 FOR c_pre_pay_amount IN c_get_pre_pay_amount (p_invoice_id, p_start_date, p_end_date)
1576 LOOP
1577 l_ppp_amt := c_pre_pay_amount.prepay_amount;
1578 END LOOP;
1579
1580 FOR c_amount IN c_get_amount (p_invoice_id, p_start_date, p_end_date)
1581 LOOP
1582 l_payment_amt := c_amount.amount;
1583 END LOOP;
1584
1585 l_total_payment := round(nvl(l_payment_amt, 0) -
1586 nvl(l_prep_pay_amt, 0) - nvl(l_ppp_amt, 0),
1587 G_PRECISION);
1588
1589 ELSE
1590 l_total_payment := p_pay_amt;
1591 END IF;
1592
1593 RETURN(l_total_payment);
1594 EXCEPTION
1595 WHEN OTHERS THEN
1596 fnd_file.put_line(fnd_file.log,' An error occured while calculating the payment amount. Error : ' || SUBSTR(SQLERRM, 1, 200));
1597 END;
1598
1599 /*
1600 REM +======================================================================+
1601 REM Name: cf_rec_tax_calcformula
1602 REM
1603 REM Description: This function calculates the recoverable tax
1604 REM
1605 REM Parameters:
1606 REM p_rec_per =>
1607 REM +======================================================================+
1608 */
1609 FUNCTION cf_rec_tax_calcformula(p_rec_per IN NUMBER) RETURN CHAR IS
1610 l_rec_rate NUMBER;
1611 BEGIN
1612
1613 IF p_rec_per IS NOT NULL THEN
1614 RETURN(to_char(round(p_rec_per, 2)) || '%');
1615 ELSE
1616 RETURN(NULL);
1620 /*
1617 END IF;
1618 END;
1619
1621 REM +======================================================================+
1622 REM Name: C_PRT_AMT_TXBLFormula
1623 REM
1624 REM Description: This function calculates the amount taxable
1625 REM
1626 REM Parameters:
1627 REM p_tax_type,p_const_num,p_inv_type,p_tax_rate,p_invoice_id
1628 REM p_tax_id,p_start_date,p_end_date,p_real_inv_amt,p_cancelled_date
1629 REM p_check_void_date,p_payment_amt,p_txbl_disc_amt,p_tax_disc_amt
1630 REM +======================================================================+
1631 */
1632 FUNCTION C_PRT_AMT_TXBLFormula
1633 (
1634 p_tax_type VARCHAR2
1635 ,p_const_num VARCHAR2
1636 ,p_inv_type VARCHAR2
1637 ,p_tax_rate NUMBER
1638 ,p_invoice_id NUMBER
1639 ,p_tax_id NUMBER
1640 ,p_offset_tax_rate_code VARCHAR2
1641 ,p_start_date DATE
1642 ,p_end_date DATE
1643 ,p_real_inv_amt NUMBER
1644 ,p_cancelled_date DATE
1645 ,p_check_void_date DATE
1646 ,p_payment_amt NUMBER
1647 ,p_txbl_disc_amt NUMBER
1648 ,p_tax_disc_amt NUMBER
1649 ) RETURN VARCHAR2 IS
1650
1651 BEGIN
1652
1653 DECLARE
1654 tbl_amt NUMBER;
1655 l_payment_amt NUMBER;
1656 tbl_amt1 NUMBER;
1657 l_error_position VARCHAR2(20);
1658 l_taxable_amount NUMBER;
1659
1660 CURSOR c_get_amount ( p_invoice_id NUMBER
1661 ,p_tax_id NUMBER
1662 ,p_start_date DATE
1663 ,p_end_date DATE)
1664 IS
1665 SELECT SUM(nvl(dis.base_amount, dis.amount)) amount
1666 FROM ap_invoice_distributions dis
1667 WHERE dis.invoice_id = p_invoice_id
1668 AND dis.line_type_lookup_code NOT IN ('REC_TAX','NONREC_TAX','PREPAY')
1669 AND dis.accounting_date BETWEEN p_start_date AND p_end_date;
1670
1671
1672 CURSOR c_get_off_amount ( p_invoice_id NUMBER
1673 ,p_offset_tax_rate_code VARCHAR2
1674 ,p_start_date DATE
1675 ,p_end_date DATE)
1676 IS
1677 SELECT SUM(nvl(dis.base_amount, dis.amount)) amount
1678 FROM ap_invoice_distributions dis
1679 ,zx_rates_b tax
1680 WHERE dis.line_type_lookup_code NOT IN ('TAX', 'PREPAY')
1681 AND tax.tax_rate_id = dis.tax_code_id
1682 AND dis.invoice_id = p_invoice_id
1683 AND tax.offset_tax_rate_code = p_offset_tax_rate_code
1684 AND dis.accounting_date BETWEEN p_start_date AND p_end_date;
1685
1686 CURSOR c_get_amount_prepay ( p_invoice_id NUMBER
1687 ,p_tax_id NUMBER
1688 ,p_start_date DATE
1689 ,p_end_date DATE)
1690 IS
1691 SELECT SUM(nvl(dis.base_amount, dis.amount)) amount
1692 FROM ap_invoice_distributions dis
1693 ,zx_rates_b tax
1694 WHERE tax.tax_rate_id = dis.tax_code_id
1695 AND dis.invoice_id = p_invoice_id
1696 AND tax.tax_rate_id = p_tax_id
1697 AND dis.line_type_lookup_code = 'PREPAY'
1698 AND dis.accounting_date BETWEEN p_start_date AND p_end_date;
1699
1700 CURSOR c_get_amount_deb ( p_invoice_id NUMBER
1701 ,p_tax_id NUMBER
1702 ,p_start_date DATE
1703 ,p_end_date DATE)
1704 IS
1705 SELECT SUM(nvl(dis.base_amount, dis.amount) * aip.discount_lost / p_real_inv_amt) amount
1706 FROM ap_invoice_distributions dis
1707 ,zx_rates_b tax
1708 ,ap_invoice_payments aip
1709 ,ap_invoices inv
1710 WHERE dis.line_type_lookup_code NOT IN ('TAX', 'PREPAY')
1711 AND inv.invoice_id = dis.invoice_id
1712 AND inv.invoice_id = aip.invoice_id
1713 AND dis.invoice_id = p_invoice_id
1714 AND tax.tax_rate_id = dis.tax_code_id
1715 AND tax.tax_rate_id = p_tax_id
1716 AND aip.accounting_date BETWEEN p_start_date AND p_end_date;
1717
1718 CURSOR c_get_off_amount_deb ( p_invoice_id NUMBER
1719 ,p_offset_tax_rate_code VARCHAR2
1720 ,p_start_date DATE
1721 ,p_end_date DATE)
1722 IS
1723 SELECT SUM(nvl(dis.base_amount, dis.amount) * aip.discount_lost / p_real_inv_amt) amount
1724 FROM ap_invoice_distributions dis
1725 ,zx_rates_b tax
1726 ,ap_invoice_payments aip
1727 ,ap_invoices inv
1728 WHERE dis.line_type_lookup_code NOT IN ('TAX', 'PREPAY')
1729 AND inv.invoice_id = dis.invoice_id
1730 AND inv.invoice_id = aip.invoice_id
1731 AND dis.invoice_id = p_invoice_id
1732 AND tax.tax_rate_id = dis.tax_code_id
1736 CURSOR c_get_amount_cre_rev ( p_invoice_id NUMBER
1733 AND tax.offset_tax_rate_code = p_offset_tax_rate_code
1734 AND aip.accounting_date BETWEEN p_start_date AND p_end_date;
1735
1737 ,p_tax_id NUMBER)
1738 IS
1739 SELECT (-1) * SUM(nvl(dis.base_amount, dis.amount)) amount
1740 FROM ap_invoice_distributions dis
1741 ,zx_rates_b tax
1742 WHERE dis.line_type_lookup_code NOT IN ('TAX', 'PREPAY')
1743 AND dis.invoice_id = p_invoice_id
1744 AND tax.tax_rate_id = p_tax_id
1745 AND dis.tax_code_id = tax.tax_rate_id
1746 AND dis.parent_reversal_id IS NOT NULL;
1747
1748 CURSOR c_get_amount_cre ( p_invoice_id NUMBER
1749 ,p_tax_id NUMBER)
1750 IS
1751 SELECT SUM(nvl(dis.base_amount, dis.amount)) amount
1752 FROM ap_invoice_distributions dis
1753 ,zx_rates_b tax
1754 WHERE dis.line_type_lookup_code NOT IN ('TAX', 'PREPAY')
1755 AND dis.invoice_id = p_invoice_id
1756 AND tax.tax_rate_id = p_tax_id
1757 AND dis.tax_code_id = tax.tax_rate_id;
1758
1759 CURSOR c_get_off_amount_cre ( p_invoice_id NUMBER
1760 ,p_offset_tax_rate_code VARCHAR2)
1761 IS
1762 SELECT SUM(nvl(dis.base_amount, dis.amount)) amount
1763 FROM ap_invoice_distributions dis
1764 ,zx_rates_b tax
1765 WHERE dis.line_type_lookup_code NOT IN ('TAX', 'PREPAY')
1766 AND dis.invoice_id = p_invoice_id
1767 AND tax.offset_tax_rate_code = p_offset_tax_rate_code
1768 AND dis.tax_code_id = tax.tax_rate_id;
1769
1770 CURSOR c_get_amount_cre_calc ( p_invoice_id NUMBER
1771 ,p_tax_id NUMBER)
1772 IS
1773 SELECT SUM(nvl(dis.base_amount, dis.amount)) * (p_payment_amt / p_real_inv_amt) amount
1774 FROM ap_invoice_distributions dis
1775 ,zx_rates_b tax
1776 WHERE dis.line_type_lookup_code NOT IN ('TAX', 'PREPAY')
1777 AND dis.invoice_id = p_invoice_id
1778 AND tax.tax_rate_id = p_tax_id
1779 AND dis.tax_code_id = tax.tax_rate_id;
1780
1781 BEGIN
1782
1783 IF p_tax_type = 'DEB/M'
1784 AND p_const_num = 'A' THEN
1785 IF p_inv_type <> 'PREPAYMENT_APPLICATION' THEN
1786 IF p_tax_rate >= 0 THEN
1787
1788 FOR c_amt IN c_get_amount (p_invoice_id, p_tax_id, p_start_date, p_end_date )
1789 LOOP
1790 tbl_amt := c_amt.amount;
1791 END LOOP;
1792
1793 ELSIF p_tax_rate < 0 THEN
1794 FOR c_off_amt IN c_get_off_amount (p_invoice_id, p_offset_tax_rate_code, p_start_date, p_end_date )
1795 LOOP
1796 tbl_amt := c_off_amt.amount;
1797 END LOOP;
1798
1799 END IF;
1800 ELSE
1801
1802 FOR c_amt_prepay IN c_get_amount_prepay (p_invoice_id, p_tax_id, p_start_date, p_end_date )
1803 LOOP
1804 tbl_amt := c_amt_prepay.amount;
1805 END LOOP;
1806
1807 END IF;
1808 ELSIF p_tax_type = 'DEB/M'
1809 AND p_const_num = 'C' THEN
1810 IF p_tax_rate >= 0 THEN
1811 FOR c_amt_deb IN c_get_amount_deb (p_invoice_id, p_tax_id, p_start_date, p_end_date )
1812 LOOP
1813 tbl_amt := c_amt_deb.amount;
1814 END LOOP;
1815
1816 ELSIF p_tax_rate < 0 THEN
1817 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 )
1818 LOOP
1819 tbl_amt := c_off_amt_deb.amount;
1820 END LOOP;
1821
1822 END IF;
1823 ELSIF p_tax_type = 'CRE/M' THEN
1824 IF p_inv_type <> 'PREPAYMENT_APPLICATION' THEN
1825 IF p_inv_type NOT LIKE '%JEPP' THEN
1826
1827 IF p_tax_rate >= 0 THEN
1828
1829 IF p_cancelled_date IS NOT NULL THEN
1830
1831 IF p_check_void_date BETWEEN p_start_date AND p_end_date THEN
1832 tbl_amt := 0;
1833 ELSE
1834
1835 FOR c_amt_cre_rev IN c_get_amount_cre_rev (p_invoice_id, p_tax_id)
1836 LOOP
1837 tbl_amt := c_amt_cre_rev.amount;
1838 END LOOP;
1839
1840 END IF;
1841
1842 ELSE
1843
1844 FOR c_amt_cre IN c_get_amount_cre (p_invoice_id, p_tax_id)
1845 LOOP
1846 tbl_amt := c_amt_cre.amount;
1847 END LOOP;
1848
1849 END IF;
1850
1851 ELSIF p_tax_rate < 0 THEN
1852 FOR c_off_amt_cre IN c_get_off_amount_cre (p_invoice_id, p_offset_tax_rate_code)
1853 LOOP
1854 tbl_amt := c_off_amt_cre.amount;
1855 END LOOP;
1856
1857 END IF;
1858
1859 ELSE
1860
1861 IF p_tax_rate >= 0 THEN
1862 FOR c_amt_cre_calc IN c_get_amount_cre_calc (p_invoice_id, p_tax_id)
1863 LOOP
1864 tbl_amt := c_amt_cre_calc.amount;
1865 END LOOP;
1866
1870 FOR c_off_amt_cre_l IN c_get_off_amount_cre (p_invoice_id, p_offset_tax_rate_code)
1867 l_taxable_amount := to_char(round(tbl_amt, G_PRECISION));
1868
1869 ELSIF p_tax_rate < 0 THEN
1871 LOOP
1872 tbl_amt := c_off_amt_cre_l.amount;
1873 END LOOP;
1874
1875 l_taxable_amount := tbl_amt;
1876 END IF;
1877
1878 END IF;
1879
1880 ELSE
1881 FOR c_amt_prepay_l IN c_get_amount_prepay (p_invoice_id, p_tax_id, p_start_date, p_end_date )
1882 LOOP
1883 tbl_amt := c_amt_prepay_l.amount;
1884 END LOOP;
1885
1886 END IF;
1887 END IF;
1888
1889 IF ((p_tax_type = 'DEB/M') OR ((p_tax_type = 'CRE/M') AND
1890 (p_inv_type = 'PREPAYMENT_APPLICATION' OR
1891 p_inv_type LIKE '%JEPP'))) THEN
1892 l_taxable_amount := to_char(round(tbl_amt, G_PRECISION));
1893 ELSE
1894 IF p_real_inv_amt - p_txbl_disc_amt - p_tax_disc_amt = 0 THEN
1895 l_taxable_amount := to_char(round(((-1) * tbl_amt -
1896 p_txbl_disc_amt), G_PRECISION));
1897 ELSE
1898 l_taxable_amount := to_char(round((tbl_amt - p_txbl_disc_amt) *
1899 p_payment_amt / (p_real_inv_amt -
1900 p_txbl_disc_amt -
1901 p_tax_disc_amt), G_PRECISION));
1902 END IF;
1903 END IF;
1904
1905 RETURN(l_taxable_amount); -- currency format this amount for use.
1906
1907 EXCEPTION
1908 WHEN OTHERS THEN
1909 fnd_file.put_line(fnd_file.log,' An error occured while calculating the amount taxable. Error : ' || SUBSTR(SQLERRM, 1, 200));
1910 raise_application_error(-20101, NULL);
1911 END;
1912
1913 END;
1914
1915 /*
1916 REM +======================================================================+
1917 REM Name: cf_tax_amtformula
1918 REM
1919 REM Description: This function calculates the amount taxable
1920 REM
1921 REM Parameters:
1922 REM p_invoice_id,p_tax_type,p_inv_type,p_real_inv_amt,p_txbl_discount_amt
1923 REM p_tax_amt,p_payment_amt,p_check_void_date,p_start_date,p_end_date
1924 REM +======================================================================+
1925 */
1926 FUNCTION cf_tax_amtformula
1927 (
1928 p_invoice_id IN NUMBER
1929 ,p_tax_type IN VARCHAR2
1930 ,p_inv_type IN VARCHAR2
1931 ,p_real_inv_amt IN NUMBER
1932 ,p_txbl_discount_amt IN NUMBER
1933 ,p_tax_amt IN NUMBER
1934 ,p_payment_amt IN NUMBER
1935 ,p_check_void_date IN DATE
1936 ,p_start_date IN DATE
1937 ,p_end_date IN DATE
1938 ) RETURN NUMBER IS
1939 l_inv_remaining_amount NUMBER := 0;
1940 l_pp_tax_amount NUMBER;
1941
1942 CURSOR c_get_inv_remain_amt (p_end_date DATE)
1943 IS
1944 SELECT round(SUM(nvl(dis.base_amount, dis.amount)), G_PRECISION) remaining_amount
1945 FROM ap_invoice_distributions dis
1946 WHERE dis.invoice_id = p_invoice_id
1947 AND dis.accounting_date <= p_end_date;
1948
1949 BEGIN
1950
1951 FOR c_inv_remain_amt IN c_get_inv_remain_amt (p_end_date)
1952 LOOP
1953 l_inv_remaining_amount := c_inv_remain_amt.remaining_amount;
1954 END LOOP;
1955
1956 IF ((p_tax_type = 'DEB/M') OR
1957 ((p_tax_type = 'CRE/M') AND
1958 (p_inv_type = 'PREPAYMENT_APPLICATION' OR p_inv_type LIKE '%JEPP'))) THEN
1959 IF (((p_real_inv_amt - p_txbl_discount_amt - G_tax_discount_amt) = 0) or (p_tax_type = 'DEB/M')) THEN
1960 RETURN(round(p_tax_amt, G_PRECISION));
1961 ELSE
1962 RETURN(round((p_tax_amt - G_tax_discount_amt) * p_payment_amt /
1963 (p_real_inv_amt - p_txbl_discount_amt -
1964 G_tax_discount_amt), G_PRECISION));
1965 END IF;
1966 ELSE
1967 IF (p_real_inv_amt - p_txbl_discount_amt - G_tax_discount_amt = 0)
1968 OR (l_inv_remaining_amount - p_payment_amt - p_txbl_discount_amt -
1969 G_tax_discount_amt <= 0) THEN
1970
1971 IF p_check_void_date IS NOT NULL
1972 AND p_check_void_date BETWEEN p_start_date AND p_end_date THEN
1973 IF p_check_void_date BETWEEN p_start_date AND p_end_date THEN
1974
1975 RETURN 0;
1976 ELSE
1977
1978 RETURN(round((-1 * p_tax_amt - G_tax_discount_amt), G_PRECISION));
1979 END IF;
1980 ELSE
1981
1982
1983 RETURN(round((p_tax_amt - G_tax_discount_amt) * p_payment_amt /
1984 (p_real_inv_amt - p_txbl_discount_amt -
1985 G_tax_discount_amt), G_PRECISION));
1986 END IF;
1987 ELSE
1988
1989 RETURN(round((p_tax_amt - G_tax_discount_amt) * p_payment_amt /
1990 (p_real_inv_amt - p_txbl_discount_amt -
1991 G_tax_discount_amt), G_PRECISION));
1992 END IF;
1993 END IF;
1994 EXCEPTION
1995 WHEN no_data_found THEN
1996 RETURN(round(p_tax_amt, G_PRECISION));
1997 END;
1998
1999 /*
2000 REM +======================================================================+
2001 REM Name: cf_txbl_discount_amtformula
2005 REM Parameters:
2002 REM
2003 REM Description: This function calculates the taxable discount amount
2004 REM
2006 REM p_invoice_id,p_tax_type,p_inv_type,p_tax_rate,
2007 REM p_start_date,p_end_date
2008 REM +======================================================================+
2009 */
2010 FUNCTION cf_txbl_discount_amtformula
2011 (
2012 p_tax_type IN VARCHAR2
2013 ,p_inv_type IN VARCHAR2
2014 ,p_invoice_id IN NUMBER
2015 ,p_tax_rate IN NUMBER
2016 ,p_start_date IN DATE
2017 ,p_end_date IN DATE
2018 ) RETURN NUMBER IS
2019 l_discount_amt NUMBER := 0;
2020 l_txbl_damt NUMBER := 0;
2021
2022 CURSOR c_get_discount_amt (p_invoice_id NUMBER
2023 ,p_start_date DATE
2024 ,p_end_date DATE)
2025 IS
2026 SELECT SUM(nvl(disc.discount_taken, 0)) discount_taken
2027 FROM ap_invoice_payments disc
2028 ,ap_checks chk
2029 WHERE disc.invoice_id = p_invoice_id
2030 AND disc.check_id = chk.check_id
2031 AND nvl(chk.future_pay_due_date, chk.check_date) BETWEEN
2032 p_start_date AND p_end_date;
2033
2034 BEGIN
2035 IF p_tax_type = 'CRE/M' AND p_inv_type <> 'PREPAYMENT_APPLICATION' THEN
2036 BEGIN
2037
2038 FOR c_discount_amt IN c_get_discount_amt (p_invoice_id, p_start_date, p_end_date)
2039 LOOP
2040 l_discount_amt := c_discount_amt.discount_taken;
2041 END LOOP;
2042
2043 EXCEPTION
2044 WHEN OTHERS THEN
2045 fnd_file.put_line(fnd_file.log,' An error occured while calculating the taxable discount amount. Error : ' || SUBSTR(SQLERRM, 1, 200));
2046 l_discount_amt := 0;
2047 END;
2048 END IF;
2049
2050 IF G_disc_isinvlesstax_flag <> 'Y' THEN
2051 l_txbl_damt := round(l_discount_amt * (100 - p_tax_rate) / 100, G_PRECISION);
2052 ELSE
2053 l_txbl_damt := l_discount_amt;
2054 END IF;
2055
2056 G_tax_discount_amt := l_discount_amt - l_txbl_damt;
2057
2058 RETURN(l_txbl_damt);
2059 END;
2060
2061 /*
2062 REM +======================================================================+
2063 REM Name: cf_item_tax_amtformula
2064 REM
2065 REM Description: This function calculates the item tax amount
2066 REM
2067 REM Parameters:
2068 REM p_cancelled_date,p_acc_date,p_tax_amt,p_item_line_cnt,
2069 REM p_start_date,p_end_date
2070 REM +======================================================================+
2071 */
2072 FUNCTION cf_item_tax_amtformula
2073 (
2074 p_cancelled_date IN DATE
2075 ,p_acc_date IN DATE
2076 ,p_tax_amt IN NUMBER
2077 ,p_item_line_cnt IN NUMBER
2078 ,p_start_date IN DATE
2079 ,p_end_date IN DATE
2080 ) RETURN NUMBER IS
2081 BEGIN
2082 IF p_cancelled_date IS NOT NULL
2083 AND p_acc_date BETWEEN p_start_date AND p_end_date THEN
2084 RETURN((-1) * (p_tax_amt / p_item_line_cnt));
2085 ELSE
2086
2087 RETURN(p_tax_amt / p_item_line_cnt);
2088
2089 END IF;
2090 END;
2091
2092 /*
2093 REM +======================================================================+
2094 REM Name: cf_real_inv_amtformula
2095 REM
2096 REM Description: This function calculates the actual invoice amount
2097 REM
2098 REM Parameters:
2099 REM p_invoice_id,p_invoice_amount
2100 REM +======================================================================+
2101 */
2102 FUNCTION cf_real_inv_amtformula
2103 (
2104 p_invoice_id IN NUMBER
2105 ,p_invoice_amount IN NUMBER
2106 ) RETURN NUMBER IS
2107 l_iipp_amt NUMBER;
2108
2109 CURSOR c_get_inv_prepay_amt (p_invoice_id NUMBER)
2110 IS
2111 SELECT SUM(decode(invoice_includes_prepay_flag, 'Y', nvl(base_amount, amount), 0)) iipp_amt
2112 FROM ap_invoice_distributions
2113 WHERE invoice_id = p_invoice_id;
2114
2115 BEGIN
2116
2117 FOR c_inv_prepay_amt IN c_get_inv_prepay_amt (p_invoice_id)
2118 LOOP
2119 l_iipp_amt := c_inv_prepay_amt.iipp_amt;
2120 END LOOP;
2121
2122 RETURN(p_invoice_amount - l_iipp_amt);
2123 EXCEPTION
2124 WHEN OTHERS THEN
2125 RETURN(p_invoice_amount);
2126 END;
2127
2128
2129 /*
2130 REM +======================================================================+
2131 REM Name: G_DATA_FOUND_formula
2132 REM
2133 REM Description: Checks if company was found
2134 REM
2135 REM Parameters:
2139 BEGIN
2136 REM +======================================================================+
2137 */
2138 FUNCTION G_DATA_FOUND_formula RETURN VARCHAR2 IS
2140 RETURN G_DATA_FOUND;
2141 END;
2142
2143 /*
2144 REM +======================================================================+
2145 REM Name: G_CURR_NAME_formula
2146 REM
2147 REM Description: Returns the currency
2148 REM
2149 REM Parameters:
2150 REM +======================================================================+
2151 */
2152 FUNCTION G_CURR_NAME_formula RETURN VARCHAR2 IS
2153 BEGIN
2154 RETURN G_CURR_NAME;
2155 END;
2156
2157 /*
2158 REM +======================================================================+
2159 REM Name: G_company_title_formula
2160 REM
2161 REM Description: Returns the company title
2162 REM
2163 REM Parameters:
2164 REM +======================================================================+
2165 */
2166 FUNCTION G_company_title_formula RETURN VARCHAR2 IS
2167 BEGIN
2168 RETURN G_company_title;
2169 END;
2170
2171 /*
2172 REM +======================================================================+
2173 REM Name: G_PRECISION_formula
2174 REM
2175 REM Description: Returns the rounding off precision.
2176 REM
2177 REM Parameters:
2178 REM +======================================================================+
2179 */
2180 FUNCTION G_PRECISION_formula RETURN NUMBER IS
2181 BEGIN
2182 RETURN G_PRECISION;
2183 END;
2184
2185 /*
2186 REM +======================================================================+
2187 REM Name: set_display_for_gov
2188 REM
2189 REM Description: Setting display for gov
2190 REM
2191 REM Parameters:
2192 REM +======================================================================+
2193 */
2194 FUNCTION set_display_for_gov RETURN BOOLEAN IS
2195 BEGIN
2196 IF G_INDUSTRY_CODE = 'C' THEN
2197 RETURN(FALSE);
2198 ELSE
2199 IF G_company_title IS NOT NULL THEN
2200 RETURN(TRUE);
2201 ELSE
2202 RETURN(FALSE);
2203 END IF;
2204 END IF;
2205 RETURN NULL;
2206 END;
2207
2208 /*
2209 REM +======================================================================+
2210 REM Name: set_display_for_core
2211 REM
2212 REM Description: Setting display for core
2213 REM
2214 REM Parameters:
2215 REM +======================================================================+
2216 */
2217 FUNCTION set_display_for_core RETURN BOOLEAN IS
2218 BEGIN
2219 IF G_INDUSTRY_CODE = 'C' THEN
2220 RETURN(TRUE);
2221 ELSE
2222 IF G_company_title IS NOT NULL THEN
2223 RETURN(FALSE);
2224 ELSE
2225 RETURN(TRUE);
2226 END IF;
2227 END IF;
2228 RETURN NULL;
2229 END;
2230
2231
2232 /*
2233 REM +======================================================================+
2234 REM Name: G_LEDGER_CURR_FORMULA
2235 REM
2236 REM Description: Retuns the G_LEDGER_CURR
2237 REM
2238 REM Parameters:
2239 REM +======================================================================+
2240 */
2241 FUNCTION G_LEDGER_CURR_FORMULA RETURN VARCHAR2 IS
2242 BEGIN
2243
2244 RETURN G_LEDGER_CURR;
2245 END;
2246
2247 /*
2248 REM +======================================================================+
2249 REM Name: G_INDUSTRY_CODE_FORMULA
2250 REM
2251 REM Description: Retuns the G_INDUSTRY_CODE
2252 REM
2253 REM Parameters:
2254 REM +======================================================================+
2255 */
2256 FUNCTION G_INDUSTRY_CODE_FORMULA RETURN VARCHAR2 IS
2257 BEGIN
2258
2259 RETURN G_INDUSTRY_CODE;
2260 END;
2261
2262
2263
2264 /*
2265 REM +======================================================================+
2266 REM Name: get_accounting_segment
2267 REM
2268 REM Description: Fetch the accounting segment values
2269 REM
2270 REM Parameters:
2271 REM +======================================================================+
2272 */
2273 FUNCTION get_accounting_segment(p_ccid IN NUMBER) RETURN VARCHAR2 IS
2274 l_accounting_segment VARCHAR2(20);
2275 l_acc_no VARCHAR2(25);
2276 l_stmt VARCHAR2(1000);
2277 TYPE t_crs IS REF CURSOR;
2278 c_crs t_crs;
2279 BEGIN
2280 l_accounting_segment := fa_rx_flex_pkg.flex_sql(p_application_id => 101
2281 ,p_id_flex_code => 'GL#'
2282 ,p_id_flex_num => G_STRUCT_NUM
2283 ,p_table_alias => ''
2284 ,p_mode => 'SELECT'
2285 ,p_qualifier => 'GL_ACCOUNT');
2286 -- The above function will return account segment in the form CC.SEGMENT1
2287 -- we need to drop CC. to get the actual account segment.
2288 l_accounting_segment := substrb(l_accounting_segment
2289 ,instrb(l_accounting_segment
2293 l_stmt := ' SELECT ' || l_accounting_segment ||
2290 ,'.') + 1);
2291
2292 -- Fetch the company and acc_no information
2294 ' FROM GL_CODE_COMBINATIONS ' ||
2295 ' WHERE CODE_COMBINATION_ID = :LLCID';
2296 OPEN c_crs FOR l_stmt
2297 USING p_ccid;
2298 LOOP
2299 FETCH c_crs
2300 INTO l_acc_no;
2301 EXIT WHEN c_crs%NOTFOUND;
2302 END LOOP;
2303 CLOSE c_crs;
2304 RETURN l_acc_no;
2305 EXCEPTION
2306 WHEN NO_DATA_FOUND THEN
2307 fnd_file.put_line(fnd_file.log,' No record was returned for the GL_Account segment. Error : ' || SUBSTR(SQLERRM,1,200));
2308 RETURN NULL;
2309 END;
2310
2311 /*
2312 REM +======================================================================+
2313 REM Name: get_balancing_segment
2314 REM
2315 REM Description: Fetch the GL Balancing segment value and description
2316 REM
2317 REM Parameters:
2318 REM +======================================================================+
2319 */
2320 FUNCTION get_balancing_segment
2321 (
2322 p_ccid IN NUMBER
2323 ,x_company_desc IN OUT NOCOPY VARCHAR2
2324 )
2325 RETURN VARCHAR2 IS
2326 l_balancing_segment VARCHAR2(20);
2327 l_company VARCHAR2(25);
2328 l_stmt VARCHAR2(1000);
2329 TYPE t_crs IS REF CURSOR;
2330 c_crs t_crs;
2331
2332 CURSOR c_balancing_desc(p_coaid NUMBER, p_company VARCHAR2)
2333 IS
2334 SELECT ffv.description
2335 FROM fnd_id_flex_segments_vl fif
2336 ,fnd_flex_values_vl ffv
2337 WHERE fif.id_flex_code = 'GL#'
2338 AND fif.application_id = 101
2339 AND fif.id_flex_num = p_coaid
2340 AND ffv.flex_value = p_company
2341 AND ffv.flex_value_set_id = fif.flex_value_set_id;
2342
2343 BEGIN
2344 l_balancing_segment := fa_rx_flex_pkg.flex_sql(p_application_id => 101
2345 ,p_id_flex_code => 'GL#'
2346 ,p_id_flex_num => G_STRUCT_NUM
2347 ,p_table_alias => ''
2348 ,p_mode => 'SELECT'
2349 ,p_qualifier => 'GL_BALANCING');
2350
2351 -- The above function will return balancing segment in the form CC.SEGMENT1
2352 -- we need to drop CC. to get the actual balancing segment.
2353 l_balancing_segment := substrb(l_balancing_segment
2354 ,instrb(l_balancing_segment
2355 ,'.') + 1);
2356
2357 -- Fetch the company and acc_no information
2358 l_stmt := ' SELECT ' || l_balancing_segment ||
2359 ' FROM GL_CODE_COMBINATIONS ' ||
2360 ' WHERE CODE_COMBINATION_ID = :LLCID';
2361 OPEN c_crs FOR l_stmt
2362 USING p_ccid;
2363 LOOP
2364 FETCH c_crs
2365 INTO l_company;
2366 EXIT WHEN c_crs%NOTFOUND;
2367 END LOOP;
2368 CLOSE c_crs;
2369 FOR c_bal_desc IN c_balancing_desc(G_STRUCT_NUM
2370 ,l_company)
2371 LOOP
2372 x_company_desc := c_bal_desc.description;
2373 END LOOP;
2374
2375 RETURN l_company;
2376 EXCEPTION
2377 WHEN NO_DATA_FOUND THEN
2378 fnd_file.put_line(fnd_file.log,' No record was returned for the GL_Balancing segment. Error : ' || SUBSTR(SQLERRM,1,200));
2379 RETURN NULL;
2380 END;
2381
2382 /*
2383 REM +======================================================================+
2384 REM Name: get_balancing_segment
2385 REM
2386 REM Description: Fetch the GL Balancing segment value and description
2387 REM
2388 REM Parameters:
2389 REM +======================================================================+
2390 */
2391 FUNCTION get_balancing_segment
2392 (
2393 p_ccid IN NUMBER
2394 )
2395 RETURN VARCHAR2 IS
2396 l_balancing_segment VARCHAR2(20);
2397 l_company VARCHAR2(25);
2398 l_stmt VARCHAR2(1000);
2399 TYPE t_crs IS REF CURSOR;
2400 c_crs t_crs;
2401
2402 CURSOR c_balancing_desc(p_coaid NUMBER, p_company VARCHAR2)
2403 IS
2404 SELECT ffv.description
2405 FROM fnd_id_flex_segments_vl fif
2406 ,fnd_flex_values_vl ffv
2407 WHERE fif.id_flex_code = 'GL#'
2408 AND fif.application_id = 101
2409 AND fif.id_flex_num = p_coaid
2410 AND ffv.flex_value = p_company
2411 AND ffv.flex_value_set_id = fif.flex_value_set_id;
2412
2413 BEGIN
2414 l_balancing_segment := fa_rx_flex_pkg.flex_sql(p_application_id => 101
2415 ,p_id_flex_code => 'GL#'
2416 ,p_id_flex_num => G_STRUCT_NUM
2417 ,p_table_alias => ''
2418 ,p_mode => 'SELECT'
2419 ,p_qualifier => 'GL_BALANCING');
2420
2421 -- The above function will return balancing segment in the form CC.SEGMENT1
2422 -- we need to drop CC. to get the actual balancing segment.
2423 l_balancing_segment := substrb(l_balancing_segment
2424 ,instrb(l_balancing_segment
2425 ,'.') + 1);
2426
2427 -- Fetch the company and acc_no information
2428 l_stmt := ' SELECT ' || l_balancing_segment ||
2429 ' FROM GL_CODE_COMBINATIONS ' ||
2430 ' WHERE CODE_COMBINATION_ID = :LLCID';
2431 OPEN c_crs FOR l_stmt
2432 USING p_ccid;
2433 LOOP
2434 FETCH c_crs
2435 INTO l_company;
2436 EXIT WHEN c_crs%NOTFOUND;
2437 END LOOP;
2438 CLOSE c_crs;
2439 RETURN l_company;
2440 EXCEPTION
2441 WHEN NO_DATA_FOUND THEN
2442 fnd_file.put_line(fnd_file.log,' No record was returned for the GL_Balancing segment. Error : ' || SUBSTR(SQLERRM,1,200));
2443 RETURN NULL;
2444 END;
2445
2446 END JE_ZZ_AUDIT_AP_PKG;
[Home] [Help]