[Home] [Help]
PACKAGE BODY: APPS.ARP_ETAX_SERVICES_PKG
Source
1 PACKAGE BODY ARP_ETAX_SERVICES_PKG AS
2 /* $Header: AREBTSRB.pls 120.61.12020000.9 2013/04/09 23:24:08 hvaladip ship $ */
3
4
5 /*=======================================================================+
6 | Package Globals
7 +=======================================================================*/
8 G_PKG_NAME CONSTANT VARCHAR2(30) := 'ARP_ETAX_SERVICES_PKG';
9
10 -- for Manual tax lines, store the line information that the user overrides:
11 pg_extended_amount_changed BOOLEAN := FALSE;
12 pg_new_line_rec ra_customer_trx_lines%rowtype;
13 pg_tax_amount_changed BOOLEAN := FALSE;
14 pg_line_changed BOOLEAN := FALSE;
15
16 pg_use_inv_acctg VARCHAR2(1);
17 pg_so_org_id VARCHAR2(20);
18 pg_org_id NUMBER;
19
20 pg_salesrep_id NUMBER := -99;
21 pg_poo_party_id NUMBER;
22 pg_poo_location_id NUMBER;
23
24 /*--------------------------------------------------------+
25 | Table records for record and replace tax accounts |
26 +--------------------------------------------------------*/
27 TYPE table_id_type IS TABLE OF ra_customer_trx_all.customer_trx_id%TYPE
28 INDEX BY BINARY_INTEGER;
29 TYPE amount_type IS TABLE OF ra_cust_trx_line_gl_dist_all.amount%TYPE
30 INDEX BY BINARY_INTEGER;
31 TYPE regime_type IS TABLE OF zx_lines.tax_regime_code%TYPE
32 INDEX BY BINARY_INTEGER;
33 TYPE tax_type IS TABLE OF zx_lines.tax%TYPE
34 INDEX BY BINARY_INTEGER;
35 TYPE flag_type IS TABLE OF ra_cust_trx_line_gl_dist_all.account_set_flag%TYPE
36 INDEX BY BINARY_INTEGER;
37 TYPE account_id_type IS TABLE OF ra_cust_trx_line_gl_dist_all.code_combination_id%TYPE
38 INDEX BY BINARY_INTEGER;
39 TYPE tax_rate_type IS TABLE OF ra_customer_trx_lines_all.vat_tax_id%TYPE
40 INDEX BY BINARY_INTEGER;
41 TYPE collected_tax_ccid_type IS TABLE OF ra_cust_trx_line_gl_dist_all.collected_tax_ccid%TYPE
42 INDEX BY BINARY_INTEGER;
43 TYPE attr_cat_type IS TABLE OF ra_cust_trx_line_gl_dist_all.attribute_category%type
44 INDEX BY BINARY_INTEGER;
45 TYPE attr_type IS TABLE OF ra_cust_trx_line_gl_dist_all.attribute1%type
46 INDEX BY BINARY_INTEGER;
47 TYPE commnt_type IS TABLE OF ra_cust_trx_line_gl_dist_all.comments%type
48 INDEX BY BINARY_INTEGER;
49
50
51 t_customer_trx_id table_id_type;
52 t_customer_trx_line_id table_id_type;
53 t_cust_trx_line_gl_dist_id table_id_type;
54 t_cust_trx_line_salesrep_id table_id_type;
55 t_tax_line_id table_id_type;
56 t_amount amount_type;
57 t_account_set_flag flag_type;
58 t_tax_regime_code regime_type;
59 t_tax tax_type;
60 t_code_combination_id account_id_type;
61 t_tax_rate_id tax_rate_type;
62 t_collected_tax_ccid collected_tax_ccid_type;
63 t_attribute_category attr_cat_type;
64 t_attribute1 attr_type;
65 t_attribute2 attr_type;
66 t_attribute3 attr_type;
67 t_attribute4 attr_type;
68 t_attribute5 attr_type;
69 t_attribute6 attr_type;
70 t_attribute7 attr_type;
71 t_attribute8 attr_type;
72 t_attribute9 attr_type;
73 t_attribute10 attr_type;
74 t_attribute11 attr_type;
75 t_attribute12 attr_type;
76 t_attribute13 attr_type;
77 t_attribute14 attr_type;
78 t_attribute15 attr_type;
79 t_comments commnt_type;
80
81 /*--------------------------------------------------------+
82 | Dummy constants for use in update and lock operations |
83 +--------------------------------------------------------*/
84
85 AR_TEXT_DUMMY CONSTANT VARCHAR2(10) := '~~!@#$*&^';
86 AR_FLAG_DUMMY CONSTANT VARCHAR2(10) := '~';
87 AR_NUMBER_DUMMY CONSTANT NUMBER(15) := -999999999999999;
88
89 PG_DEBUG varchar2(1):= NVL(FND_PROFILE.value('AFLOG_ENABLED'), 'N');
90
91 FUNCTION use_invoice_accounting
92 RETURN BOOLEAN IS
93 BEGIN
94 IF pg_use_inv_acctg IS NULL
95 THEN
96 fnd_profile.get( 'AR_USE_INV_ACCT_FOR_CM_FLAG',
97 pg_use_inv_acctg );
98 IF pg_use_inv_acctg IS NULL
99 THEN
100 pg_use_inv_acctg := 'N';
101 END IF;
102
103 arp_util.debug(' pg_use_inv_acctg = ' || pg_use_inv_acctg);
104 END IF;
105
106 IF pg_use_inv_acctg = 'Y'
107 THEN
108 RETURN TRUE;
109 ELSE
110 RETURN FALSE;
111 END IF;
112
113 END use_invoice_accounting;
114
115 PROCEDURE copy_inv_tax_dists(p_customer_trx_id IN number)
116 IS
117 base_min_acc_unit NUMBER;
118 base_precision NUMBER;
119 l_rows NUMBER := 0;
120 BEGIN
121 IF PG_DEBUG in ('Y','C')
122 THEN
123 arp_util.debug('arp_etax_services_pkg.copy_inv_tax_dists()+');
124 END IF;
125
126 /* Get base precision and minimum accountable unit */
127 base_min_acc_unit := arp_trx_global.system_info.base_min_acc_unit;
128 base_precision := arp_trx_global.system_info.base_precision;
129
130 /* This insert copied from the logic in arp_credit_memo_module.
131 The idea is that copying invoice tax accounting is pretty
132 simple, but we need the logic to be callable at any time
133 rather than tied to other arp_credit_memo_module behavior */
134
135 /* 5413663 - due to concerns that this code might create
136 incorrect or poorly timed tax dists, I have modified
137 the program_id logic to use -5 instead of a valid value.
138 That way, we can tell if this code created the tax or
139 if it came from somewhere else (rev rec, autoaccounting) */
140
141 INSERT into ra_cust_trx_line_gl_dist
142 (
143 /* gl_dist_id used to be here - now populated by BRI trigger */
144 customer_trx_id, /* credit memo customer_trx_id */
145 customer_trx_line_id, /* credit memo customer_trx_line_id */
146 set_of_books_id,
147 last_update_date,
148 last_updated_by,
149 creation_date,
150 created_by,
151 last_update_login,
152 program_application_id,
153 program_id,
154 program_update_date,
155 account_class,
156 account_set_flag,
157 percent,
158 amount,
159 acctd_amount,
160 gl_date,
161 code_combination_id,
162 posting_control_id,
163 collected_tax_ccid,
164 ussgl_transaction_code,
165 org_id,
166 event_id
167 )
168 SELECT
169 ctl.customer_trx_id,
170 ctl.customer_trx_line_id,
171 ct.set_of_books_id,
172 sysdate,
173 ct.last_updated_by,
174 sysdate,
175 ct.created_by,
176 ct.last_update_login,
177 ctl.program_application_id, /* program_appl_id */
178 -5, /* program_id */
179 sysdate, /* program_update_date */
180 'TAX',
181 'N',
182 decode(ctl.extended_amount, 0, prev_ctlgd.percent,
183 round(((decode(foreign_fc.minimum_accountable_unit,
184 null, round(
185 NVL(prev_ctlgd.amount /
186 decode(prev_ctl.extended_amount,0,1,prev_ctl.extended_amount),1) *
187 decode(ctl.extended_amount,0,
188 decode(prev_ctl.extended_amount,0,-1,0),
189 ctl.extended_amount) , foreign_fc.precision),
190 round(
191 NVL(prev_ctlgd.amount /
192 decode(prev_ctl.extended_amount,0,1,prev_ctl.extended_amount),1) *
193 decode(ctl.extended_amount,0,
194 decode(prev_ctl.extended_amount,0,-1,0),
195 ctl.extended_amount)
196 / foreign_fc.minimum_accountable_unit) *
197 foreign_fc.minimum_accountable_unit) /
198 decode(ctl.extended_amount, 0, 1, ctl.extended_amount)) *
199 decode(ctl.extended_amount, 0, 0, 1))
200 * 100, 4)), /* percent */
201 decode(foreign_fc.minimum_accountable_unit,
202 null, round(NVL(prev_ctlgd.amount /
203 decode(prev_ctl.extended_amount,0,1,
204 prev_ctl.extended_amount),1) *
205 decode(ctl.extended_amount,0,
206 decode(prev_ctl.extended_amount,0,-1,0),
207 ctl.extended_amount), foreign_fc.precision),
208 round(NVL(prev_ctlgd.amount /
209 decode(prev_ctl.extended_amount,0,1,
210 prev_ctl.extended_amount),1) *
211 decode(ctl.extended_amount,0,
212 decode(prev_ctl.extended_amount,0,-1,0),
213 ctl.extended_amount)
214 / foreign_fc.minimum_accountable_unit) *
215 foreign_fc.minimum_accountable_unit
216 ), /* amount */
217 decode(base_min_acc_unit, NULL,
218 round(decode(foreign_fc.minimum_accountable_unit,
219 null, round(NVL(prev_ctlgd.amount /
220 decode(prev_ctl.extended_amount,0,1,
221 prev_ctl.extended_amount),1) *
222 decode(ctl.extended_amount,0,
223 decode(prev_ctl.extended_amount,0,-1,0),
224 ctl.extended_amount), foreign_fc.precision),
225 round(NVL(prev_ctlgd.amount /
226 decode(prev_ctl.extended_amount,0,1,
227 prev_ctl.extended_amount),1) *
228 decode(ctl.extended_amount,0,
229 decode(prev_ctl.extended_amount,0,-1,0),
230 ctl.extended_amount)
231 / foreign_fc.minimum_accountable_unit) *
232 foreign_fc.minimum_accountable_unit) *
233 nvl(ct.exchange_rate, 1),
234 base_precision),
235 round(decode(foreign_fc.minimum_accountable_unit,
236 null, round(NVL(prev_ctlgd.amount /
237 decode(prev_ctl.extended_amount,0,1,
238 prev_ctl.extended_amount),1) *
239 decode(ctl.extended_amount,0,
240 decode(prev_ctl.extended_amount,0,-1,0),
241 ctl.extended_amount), foreign_fc.precision),
242 round(NVL(prev_ctlgd.amount /
243 decode(prev_ctl.extended_amount,0,1,
244 prev_ctl.extended_amount),1) *
245 decode(ctl.extended_amount,0,
246 decode(prev_ctl.extended_amount,0,-1,0),
247 ctl.extended_amount)
248 / foreign_fc.minimum_accountable_unit) *
249 foreign_fc.minimum_accountable_unit) *
250 nvl(ct.exchange_rate, 1) /
251 base_min_acc_unit) * base_min_acc_unit),
252 /* acctd_amount */
253 rec_ctlgd.gl_date,
254 prev_ctlgd.code_combination_id,
255 -3,
256 prev_ctlgd.collected_tax_ccid,
257 ct.default_ussgl_transaction_code,
258 ct.org_id,
259 rec_ctlgd.event_id
260 FROM
261 fnd_currencies foreign_fc,
262 ra_customer_trx ct,
263 ra_customer_trx_lines ctl,
264 ra_cust_trx_line_gl_dist ctlgd,
265 ra_cust_trx_line_gl_dist rec_ctlgd, /* cm rec dist */
266 ra_customer_trx prev_ct,
267 ra_customer_trx_lines prev_ctl,
268 ra_cust_trx_line_gl_dist prev_ctlgd
269 WHERE
270 ct.customer_trx_id = p_customer_trx_id
271 AND ct.customer_trx_id = ctl.customer_trx_id
272 AND ctl.line_type = 'TAX'
273 /* Do not duplicate if already there */
274 AND ctl.customer_trx_line_id = ctlgd.customer_trx_line_id (+)
275 AND ctlgd.customer_trx_id IS NULL
276 /* Get CM Rec row (for gl_date) */
277 AND ct.customer_trx_id = rec_ctlgd.customer_trx_id (+)
278 AND rec_ctlgd.account_class (+) = 'REC'
279 AND rec_ctlgd.latest_rec_flag (+) = 'Y'
280 AND ct.invoice_currency_code = foreign_fc.currency_code
281 /* Join to the invoice */
282 AND ctl.previous_customer_trx_line_id
283 = prev_ctl.customer_trx_line_id(+)
284 AND prev_ctl.customer_trx_line_id
285 = prev_ctlgd.customer_trx_line_id(+)
286 AND prev_ctl.customer_trx_id = prev_ct.customer_trx_id(+)
287 /* 5413663 - only non-model dists */
288 AND prev_ctlgd.account_set_flag = 'N';
289
290 l_rows := SQL%ROWCOUNT;
291
292 IF PG_DEBUG in ('Y','C')
293 THEN
294 arp_util.debug(' tax dists inserted = ' || l_rows);
295 arp_util.debug('arp_etax_services_pkg.copy_inv_tax_dists()-');
296 END IF;
297 END copy_inv_tax_dists;
298
299 /* Records tax accounting prior to deletion in global plsql tables.
300 These rows are later used for a bulk update
301 of ra_cust_trx_line_gl_dist */
302
303 PROCEDURE record_tax_accounts(p_customer_trx_id IN number)
304 IS
305
306 CURSOR tax_line_and_dist(p_customer_trx_id NUMBER) IS
307 SELECT tl.customer_trx_id, -- trx_id
308 tl.link_to_cust_trx_line_id, -- parent line
309 tgl.cust_trx_line_gl_dist_id, -- tax dist ID
310 NVL(tgl.cust_trx_line_salesrep_id,
311 -99), -- SR ID (from dist)
312 tl.tax_line_id, -- originated tax line in ebt
313 tgl.amount, -- tax amount (not currently used)
314 tgl.account_set_flag, -- account set Y/N
315 zx.tax_regime_code, -- ZX tax regime code
316 zx.tax, -- ZX tax code
317 tgl.code_combination_id, -- tax account!
318 tl.vat_tax_id, -- Tax Rate ID
319 tgl.collected_tax_ccid, -- Collected Tax ccid for deferrred taxes
320 tgl.attribute_category,
321 tgl.attribute1,
322 tgl.attribute2,
323 tgl.attribute3,
324 tgl.attribute4,
325 tgl.attribute5,
326 tgl.attribute6,
327 tgl.attribute7,
328 tgl.attribute8,
329 tgl.attribute9,
330 tgl.attribute10,
331 tgl.attribute11,
332 tgl.attribute12,
333 tgl.attribute13,
334 tgl.attribute14,
335 tgl.attribute15,
336 tgl.comments
337 FROM ra_customer_trx_lines tl,
338 ra_cust_trx_line_gl_dist tgl,
339 zx_lines zx
340 WHERE tl.customer_trx_id = p_customer_trx_id
341 AND tl.line_type = 'TAX'
342 AND tl.customer_trx_line_id = tgl.customer_trx_line_id
343 AND tgl.code_combination_id <> -1 -- skip invalid accounts
344 -- Bug 9012585: This will have value only for deferred tax so using NVL
345 AND nvl(tgl.collected_tax_ccid, 0) <> -1
346 AND tl.tax_line_id = zx.tax_line_id;
347
348 l_rows NUMBER;
349
350 BEGIN
351 IF PG_DEBUG in ('Y','C')
352 THEN
353 arp_debug.debug('arp_etax_services_pkg.record_tax_accounts()+');
354 END IF;
355
356 OPEN tax_line_and_dist(P_CUSTOMER_TRX_ID);
357 FETCH tax_line_and_dist BULK COLLECT INTO
358 t_customer_trx_id,
359 t_customer_trx_line_id,
360 t_cust_trx_line_gl_dist_id,
361 t_cust_trx_line_salesrep_id,
362 t_tax_line_id,
363 t_amount,
364 t_account_set_flag,
365 t_tax_regime_code,
366 t_tax,
367 t_code_combination_id,
368 t_tax_rate_id,
369 t_collected_tax_ccid,
370 t_attribute_category,
371 t_attribute1,
372 t_attribute2,
373 t_attribute3,
374 t_attribute4,
375 t_attribute5,
376 t_attribute6,
377 t_attribute7,
378 t_attribute8,
379 t_attribute9,
380 t_attribute10,
381 t_attribute11,
382 t_attribute12,
383 t_attribute13,
384 t_attribute14,
385 t_attribute15,
386 t_comments;
387
388 l_rows := tax_line_and_dist%ROWCOUNT;
389
390 CLOSE tax_line_and_dist;
391
392 IF PG_DEBUG in ('Y','C')
393 THEN
394 arp_debug.debug(' distribution(s) recorded = ' || l_rows);
395 arp_debug.debug('arp_etax_services_pkg.record_tax_accounts()-');
396 END IF;
397 END record_tax_accounts;
398
399 /* Uses tax account tables to bulk update ra_cust_trx_line_gl_dist with
400 corrected or overridden accounts. */
401
402 PROCEDURE replace_tax_accounts
403 IS
404 l_rows NUMBER := 0;
405 BEGIN
406 IF PG_DEBUG in ('Y','C')
407 THEN
408 arp_debug.debug('arp_etax_services_pkg.replace_tax_accounts()+');
409
410 /* Debug Code - start +/
411 -- this code dumps the cached accounting lines so we can
412 -- see what is happening during the caching process.
413 IF t_customer_trx_id.EXISTS(1)
414 THEN
415 FOR acc in t_customer_trx_id.FIRST .. t_customer_trx_id.LAST LOOP
416 arp_debug.debug(acc || ':' ||
417 t_customer_trx_id(acc) || '~' ||
418 t_customer_trx_line_id(acc) || '~' ||
419 t_cust_trx_line_salesrep_id(acc) || '~' ||
420 t_tax_regime_code(acc) || '~' ||
421 t_tax(acc) || '~' ||
422 t_tax_rate_id(acc) || '~' ||
423 t_tax_line_id(acc) || '~' ||
424 t_account_set_flag(acc) || '~' ||
425 t_code_combination_id(acc) || '~' ||
426 t_attribute_category(acc) || '~' ||
427 t_comments(acc));
428 END LOOP;
429 END IF;
430 /+ Debug Code - end */
431 END IF;
432
433 /* Bulk update of gl_dist rows for tax...
434 Note that this code updates all tax accounting rows where
435 the new and old tax accounts are different. Additionally,
436 it will never bring forward an invalid account (ccid -1).
437 we match up the tax regime, tax, salesrep_id, account_set_flag,
438 and line_id. This may need to be adjusted later if we find
439 reasons to not preserve the original tax accounts */
440 IF t_customer_trx_id.EXISTS(1)
441 THEN
442 FORALL i IN t_customer_trx_id.FIRST .. t_customer_trx_id.LAST
443 UPDATE ra_cust_trx_line_gl_dist gld
444 SET code_combination_id = t_code_combination_id(i),
445 collected_tax_ccid = t_collected_tax_ccid(i),
446 comments = t_comments(i),
447 attribute_category = t_attribute_category(i),
448 attribute1 = t_attribute1(i),
449 attribute2 = t_attribute2(i),
450 attribute3 = t_attribute3(i),
451 attribute4 = t_attribute4(i),
452 attribute5 = t_attribute5(i),
453 attribute6 = t_attribute6(i),
454 attribute7 = t_attribute7(i),
455 attribute8 = t_attribute8(i),
456 attribute9 = t_attribute9(i),
457 attribute10 = t_attribute10(i),
458 attribute11 = t_attribute11(i),
459 attribute12 = t_attribute12(i),
460 attribute13 = t_attribute13(i),
461 attribute14 = t_attribute14(i),
462 attribute15 = t_attribute15(i)
463 WHERE customer_trx_id = t_customer_trx_id(i)
464 AND account_class = 'TAX'
465 AND cust_trx_line_gl_dist_id IN
466 (SELECT tgl.cust_trx_line_gl_dist_id
467 FROM ra_cust_trx_line_gl_dist tgl,
468 ra_customer_trx_lines tl,
469 zx_lines zx
470 WHERE tl.customer_trx_id = t_customer_trx_id(i)
471 AND tl.link_to_cust_trx_line_id =
472 t_customer_trx_line_id(i)
473 AND tl.line_type = 'TAX'
474 AND tl.customer_trx_line_id = tgl.customer_trx_line_id
475 AND tgl.account_class = 'TAX'
476 AND tgl.account_set_flag = t_account_set_flag(i)
477 AND ( tgl.code_combination_id <> t_code_combination_id(i)
478 -- Bug 9012585 : Honour manual override for collected_tax_ccid as well
479 OR NVL(tgl.collected_tax_ccid,0) <> NVL(t_collected_tax_ccid(i),0)
480 -- 13455779 - preserve comments and DFF too
481 OR t_comments(i) || t_attribute_category(i) IS NOT NULL)
482 AND nvl(tgl.cust_trx_line_salesrep_id, -99) =
483 t_cust_trx_line_salesrep_id(i)
484 AND tl.tax_line_id = zx.tax_line_id
485 AND tl.vat_tax_id = t_tax_rate_id(i)
486 AND tl.tax_line_id = t_tax_line_id(i));
487
488 l_rows := SQL%ROWCOUNT;
489 END IF;
490
491
492 IF PG_DEBUG in ('Y','C')
493 THEN
494 arp_debug.debug(' distribution(s) updated = ' || l_rows);
495 arp_debug.debug('arp_etax_services_pkg.replace_tax_accounts()-');
496 END IF;
497
498 EXCEPTION
499 WHEN NO_DATA_FOUND THEN
500 IF PG_DEBUG = 'Y' THEN
501 arp_debug.debug( 'no rows in tax account tables');
502 END IF;
503
504 END replace_tax_accounts;
505
506 /*=============================================================================
507 | FUNCTION - Calculate()
508 |
509 | DESCRIPTION
510 | Public function that will call the calculate_tax service for
511 | calculation and recalculation.
512 | This API assumes the calling code controls the commit cycle.
513 | This function returns TRUE if the call to the service is successful.
514 | Otherwise, FALSE.
515 |
516 | PARAMETERS
517 |
518 | MODIFICATION HISTORY
519 | DATE Author Description of Changes
520 | 14-Apr-2005 Debbie Sue Jancis Created
521 | 26-MAY-2006 M Raymond 5152340 - added call to
522 | delete_tax_lines_from_ar
523 *===========================================================================*/
524 FUNCTION Calculate( p_customer_trx_id IN NUMBER,
525 p_cust_trx_line_id IN NUMBER,
526 p_action IN VARCHAR2,
527 p_line_level_action IN VARCHAR2 ) RETURN BOOLEAN IS
528
529 l_transaction_rec zx_api_pub.transaction_rec_type;
530
531 l_return_status_service VARCHAR2(4000);
532 l_msg_count NUMBER;
533 l_msg_data VARCHAR2(4000);
534 l_msg_data_out VARCHAR2(4000);
535 l_mesg VARCHAR2(4000);
536 l_doc_level_recalc_flag VARCHAR2(1);
537
538 l_event_class_code VARCHAR2(80);
539 l_event_type_code VARCHAR2(80);
540 l_success BOOLEAN;
541 l_rows NUMBER;
542 BEGIN
543 arp_util.debug('ARP_ETAX_SERVICES_PKG.Calculate(+)');
544 /* get event class code */
545 l_success := arp_etax_util.get_event_information(
546 p_customer_trx_id => p_customer_trx_id,
547 p_action => p_action,
548 p_event_class_code => l_event_class_code,
549 p_event_type_code => l_event_type_code);
550
551 arp_util.debug('customer trx id = ' || p_customer_trx_id);
552 arp_util.debug('action = ' || p_action);
553 arp_util.debug('event class code = ' || l_event_class_code);
554 arp_util.debug('event type code = ' || l_event_type_code);
555
556 IF (l_success) THEN
557 /* populate transaction rec type */
558 l_transaction_rec.internal_organization_id := arp_global.sysparam.org_id;
559 l_transaction_rec.application_id := 222;
560 l_transaction_rec.entity_code := 'TRANSACTIONS';
561 l_transaction_rec.event_class_code := l_event_class_code;
562 l_transaction_rec.event_type_code := l_event_type_code;
563 l_transaction_rec.trx_id := p_customer_trx_id;
564
565 /* initialize the pl/sql table
566 ZX_GLOBAL_STRUCTURES_PKG.INIT_TRX_LINE_DIST_TBL(1); */
567
568 /* insert data into ebt plsql tables
569
570 arp_util.debug('calling populate_ebt_plsql_tables ');
571 populate_ebt_plsql_tables(
572 p_customer_trx_id => p_customer_trx_id,
573 p_customer_trx_line_id => p_cust_trx_line_id,
574 p_event_type_code => l_event_type_code,
575 p_event_class_code => l_event_class_code,
576 p_line_level_action => p_line_level_action); */
577
578
579 /* 5152340 - Remove AR tax lines before calculating tax */
580 arp_etax_util.delete_Tax_lines_from_ar(p_customer_trx_id);
581
582 /* call Tax */
583 arp_util.debug('calling ZX api to calculate tax');
584 arp_util.debug('ORG ID = ' || l_transaction_rec.internal_organization_id);
585
586 zx_api_pub.calculate_tax(
587 p_api_version => 1.0,
588 p_init_msg_list => FND_API.G_TRUE,
589 p_commit => FND_API.G_FALSE,
590 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
591 p_transaction_rec => l_transaction_rec,
592 p_quote_flag => 'N',
593 p_data_transfer_mode => 'WIN',
594 x_return_status => l_return_status_service,
595 x_msg_count => l_msg_count,
596 x_msg_data => l_msg_data,
597 x_doc_level_recalc_flag => l_doc_level_recalc_flag );
598
599 arp_util.debug('return status service = ' || l_return_status_service);
600
601 IF (l_return_status_service = 'S') THEN
602 -- insert Tax records into ra_customer_trx_lines based upon
603 -- customer trx line id
604 arp_util.debug('calling build_ar_tax_lines ...');
605 arp_util.debug('customer trx id = ' || p_customer_trx_id);
606
607 arp_etax_util.build_ar_tax_lines(
608 p_customer_trx_id => p_customer_trx_id,
609 p_rows_inserted => l_rows);
610
611 ELSE
612 arp_util.debug('Calculate returned error');
613 IF ( l_msg_count = 1 ) THEN
614 -- then there is only 1 message raised by the API, and
615 -- it has been sent out in the parameter x_msg_data.
616 l_msg_data_out := l_msg_data;
617 arp_util.debug('API failed with : ' || l_msg_data_out);
618 l_mesg := l_msg_data_out;
619
620 ELSIF (l_msg_count > 1) THEN
621 -- the messages are on the stack and there is more then
622 -- 1 so call them in a loop
623 loop
624 l_mesg := FND_MSG_PUB.GET(FND_MSG_PUB.G_NEXT,
625 FND_API.G_FALSE);
626 if (l_mesg IS NULL) THEN
627 EXIT;
628 end if;
629 arp_util.debug('API failed with : ' || l_mesg);
630 end loop;
631 END IF;
632
633 -- raise error
634 /* 4919401 - Added generic message fetch */
635 FND_MESSAGE.SET_NAME('FND', 'FND_GENERIC_MESSAGE');
636 FND_MESSAGE.SET_TOKEN('MESSAGE', l_mesg);
637 FND_MSG_PUB.ADD;
638
639 APP_EXCEPTION.RAISE_EXCEPTION;
640
641 RETURN FALSE;
642 END IF;
643
644 ELSE
645 arp_standard.debug('could not get an event class code');
646 RETURN FALSE;
647 END IF;
648
649 RETURN TRUE;
650
651 END Calculate;
652
653 PROCEDURE populate_ebt_plsql_tables(
654 p_customer_trx_id IN NUMBER,
655 p_customer_trx_line_id IN NUMBER,
656 p_event_type_code IN VARCHAR2,
657 p_event_class_code IN VARCHAR2,
658 p_line_level_action IN VARCHAR2,
659 p_tax_amount IN NUMBER DEFAULT NULL,
660 p_called_from IN VARCHAR2 DEFAULT NULL) IS
661
662 l_line_ship_to_cust_id RA_CUSTOMER_TRX_LINES.ship_to_customer_id%TYPE;
663 l_line_ship_to_su_id RA_CUSTOMER_TRX_LINES.ship_to_site_use_id%TYPE;
664 l_hdr_ship_to_cust_id RA_CUSTOMER_TRX.ship_to_customer_id%TYPE;
665 l_hdr_ship_to_su_id RA_CUSTOMER_TRX.ship_to_site_use_id%TYPE;
666 l_cust_id RA_CUSTOMER_TRX.ship_to_customer_id%TYPE;
667 l_site_use_id RA_CUSTOMER_TRX.ship_to_site_use_id%TYPE;
668 l_memo_line_id RA_CUSTOMER_TRX_LINES.memo_line_id%TYPE; --bug6770861
669
670 --Variables defined for Bug6126010.
671 p_salesrep_id NUMBER;
672 p_ccid NUMBER;
673 p_conc_seg VARCHAR2(240);
674 p_num_fail NUMBER;
675 p_error_buf VARCHAR2(2000);
676 p_trx_type_id NUMBER;
677 p_inv_item_id NUMBER;
678 p_memo_line_id NUMBER;
679 l_trx_date DATE;
680 p_warehouse_id NUMBER; /* Bug 8758638 */
681 l_allow_overapp VARCHAR2(1); /* 11785145 */
682 l_inv_line_remaining NUMBER;
683 l_inv_tax_remaining NUMBER;
684 l_cm_line_total NUMBER;
685 l_cmline_line_amount NUMBER;
686 l_cmline_tax_amount NUMBER;
687 BEGIN
688 IF PG_DEBUG in ('Y','C') THEN
689 arp_util.debug('populate_ebt_plsql_tables(+)');
690 END IF;
691
692 /* Set pg_so_org_id any time it is not set or
693 any time the OU changes. This supports cases where
694 users change OU without exiting form */
695 IF NVL(pg_org_id,-99) <> arp_global.sysparam.org_id
696 THEN
697 pg_org_id := arp_global.sysparam.org_id;
698
699 pg_so_org_id := oe_profile.value('SO_ORGANIZATION_ID',
700 pg_org_id);
701 pg_salesrep_id := -99;
702 END IF;
703
704 /* 12323123 - added code to pass INV exempt data if current
705 transaction is regular credit memo */
706
707 SELECT
708 TRX.org_id, -- internal_organization_id
709 222, -- application_id
710 'TRANSACTIONS', -- entity_code
711 p_event_class_code, -- event_class_code
712 p_event_type_code, -- event_type_code
713 p_customer_trx_id, -- trx_id
714 TRX.trx_date, -- trx_date
715 AR.set_of_books_id, -- ledger_id
716 TRX.invoice_currency_code, -- trx_currency_code
717 TRX.exchange_date, -- currency_conversion_date
718 TRX.exchange_rate, -- currency_conversion_rate
719 TRX.exchange_rate_type, -- currency_conversion_type
720 CURR.minimum_accountable_unit, -- minimum_accountable_unit
721 CURR.precision, -- precision
722 TRX.legal_entity_id, -- legal_entity_id
723 'LINE', -- trx_level_type
724 ----p_line_level_action, -- line_level_action ?????? *****
725 DECODE(TRX.previous_customer_trx_id,
726 NULL, p_line_level_action, DECODE(INV_TT.TYPE,'DEP','RECORD_WITH_NO_TAX',p_line_level_action)), -- p_line_level_action
727 p_customer_trx_line_id, -- trx_line_id
728 -- trx_business_category
729 TRX.cust_trx_type_id, -- receivables_trx_type_id
730 'Y', -- tax_reporting_flag
731 'N', -- Quote_Flag
732 LINES.tax_classification_code, -- output_tax_classification_code
733 NULL, -- interface_entity_code
734 NULL, -- interface_line_id
735 LINES.line_number, -- trx_line_number
736 LINES.historical_flag, -- historical_flag
737 TRX.trx_number, -- trx_number
738 substrb(TRX.comments,1,240), -- trx_description
739 TRX.printing_original_date, -- trx_communicated_date
740 TRX.batch_source_id, -- batch_source_id
741 BS.NAME, -- batch_source_name
742 TRX.doc_sequence_id, -- doc_seq_id
743 SEQ.name, -- doc_seq_name
744 TRX.doc_sequence_value, -- doc_seq_value
745 TRX.term_due_date, -- trx_due_date
746 TYPES.description, -- trx_type_description
747 NVL(REC.gl_date, TRUNC(sysdate)), --trx_line_gl_date
748 DECODE(TYPES.type,
749 'CM', 'CREDIT_MEMO',
750 'DM', 'DEBIT_MEMO',
751 'INVOICE'), -- line_class
752 LINES.sales_order_date, -- trx_shipping_date
753 DECODE(LINES.inventory_item_id, NULL, 'MISC', 'ITEM'), -- trx_line_type
754 NULL, -- trx_line_date
755 DECODE(LINES.amount_includes_tax_flag, 'Y',
756 'A','N', 'N', 'S'), -- line_amt_includes_tax_flag
757 NVL(LINES.GROSS_EXTENDED_AMOUNT,LINES.extended_amount), -- line_amt Bug 7692158
758 DECODE(TYPES.type,
759 'CM', LINES.quantity_credited,
760 LINES.quantity_invoiced), -- trx_line_quantity -- Bug 8717137
761 LINES.unit_selling_price, -- unit_price
762 DECODE(LINES.previous_customer_trx_line_id,
763 NULL, LINES.tax_exempt_flag,
764 INV_L.tax_exempt_flag), -- exemption_control_flag
765 DECODE(LINES.previous_customer_trx_line_id,
766 NULL, LINES.tax_exempt_number,
767 INV_L.tax_exempt_number), -- exempt_certificate_number
768 DECODE(LINES.previous_customer_trx_line_id,
769 NULL, LINES.tax_exempt_reason_code,
770 INV_L.tax_exempt_reason_code),-- exempt_reason
771 NVL(LINES.inventory_item_id,
772 LINES.memo_line_id), -- product_id
773 LINES.uom_code, -- uom_code
774 TRX.fob_point, -- fob_point
775 LINES.warehouse_id, -- ship_from_party_id
776 HR.location_id, -- ship_from_location_id
777 BILL_CUST.party_id, -- bill_to_party_id
778 BILL_CUST.party_id, -- rounding_bill_to_party_id
779 BILL_AS.party_site_id, -- bill_to_party_site_id
780 BILL_AS.party_site_id, -- rndg_bill_to_party_site_id
781 BILL_LOC.location_id, -- bill_to_location_id
782 -- account_ccid ***see select below due to possible multiple records
783 -- source_application_id
784 -- source_entity_code
785 -- source_event_class_code
786 -- source_trx_id
787 -- source_lines_id
788 -- source_trx_level_type
789 -- tax_amt_included_flag
790 TRX.ship_to_customer_id,
791 TRX.ship_to_site_use_id,
792 LINES.ship_to_customer_id,
793 LINES.ship_to_site_use_id,
794 TRX.invoice_currency_code, -- trx_line_currency_code
795 CURR.precision, -- trx_line_precision
796 /*Bug8650264, Modified the code to pass adjusted_doc details as NULL for
797 Deposit and Guarantee.*/
798 /*Bug8731231, Modified the code to pass adjusted_doc details as NULL for
799 Chargeback */
800 DECODE(TRX.previous_customer_trx_id,
801 NULL, NULL, DECODE(INV_TT.TYPE,'DEP',NULL,'GUAR',NULL,'CB',NULL,222)), -- adjusted_doc_application_id
802 DECODE(TRX.previous_customer_trx_id,
803 NULL, NULL, DECODE(INV_TT.TYPE,'DEP',NULL,'GUAR',NULL,'CB',NULL,'TRANSACTIONS')),-- adjusted_doc_entity_code
804 /* bug6769106 vavenugo
805 modified the line below to pass the correct value for adjusted_doc_event_class_code based on the type of the document */
806 DECODE(TRX.previous_customer_trx_id,
807 NULL, NULL, DECODE(INV_TT.TYPE,'DM','DEBIT_MEMO','DEP',NULL,'GUAR',NULL,'CB',NULL,'INVOICE')), -- adjusted_doc_event_class_Code
808 DECODE(TRX.previous_customer_trx_id,
809 NULL, NULL, DECODE(INV_TT.TYPE,'DEP',NULL,'GUAR',NULL,'CB',NULL,TRX.previous_customer_trx_id)), -- adjusted_doc_trx_id
810 DECODE(LINES.previous_customer_trx_line_id, NULL, NULL,
811 DECODE(INV_TT.TYPE,'DEP',NULL,'GUAR',NULL,'CB',NULL,LINES.previous_customer_trx_line_id)), -- adjusted_doc_line_id
812 DECODE(TRX.previous_customer_trx_id,
813 NULL, NULL, DECODE(INV_TT.TYPE,'DEP',NULL,'GUAR',NULL,'CB',NULL,'LINE')), -- adjusted_doc_trx_level_type
814 DECODE(TRX.previous_customer_trx_id, NULL,
815 NULL, DECODE(INV_TT.TYPE,'DEP',NULL,'GUAR',NULL,'CB',NULL,INV.trx_number)), -- adjusted_doc_number
816 DECODE(TRX.previous_customer_trx_id, NULL,
817 NULL, DECODE(INV_TT.TYPE,'DEP',NULL,'GUAR',NULL,'CB',NULL,INV.trx_date)), -- adjusted_doc_date
818 /* 4666566 */
819 TRX.bill_to_customer_id,
820 TRX.bill_to_site_use_id,
821 BILL_AS.cust_acct_site_id,
822 DECODE(LINES.memo_line_id, NULL,
823 NVL(LINES.warehouse_id,to_number(pg_so_org_id)),NULL),
824 TRX.org_id, -- poa_party_id
825 HRL.location_id, -- poa_location_id
826 DECODE(REL_T.customer_trx_id, NULL, NULL, 222),
827 DECODE(REL_T.customer_trx_id, NULL, NULL, 'TRANSACTIONS'),
828 DECODE(REL_T.customer_trx_id, NULL, NULL,
829 DECODE(REL_TT.type, 'INV', 'INVOICE',
830 'DM', 'DEBIT_MEMO',
831 'CM', 'CREDIT_MEMO')),
832 DECODE(REL_T.customer_trx_id, NULL, NULL, REL_T.customer_trx_id),
833 DECODE(REL_T.customer_trx_id, NULL, NULL, REL_T.trx_number),
834 DECODE(REL_T.customer_trx_id, NULL, NULL, REL_T.trx_date),
835 HRL.location_id, -- bill_from_location_id
836 ML.tax_product_category, -- bug6770861, 6874006
837 LINES.description,
838 INV_TT.allow_overapplication_flag
839 INTO
840 -- internal_organization_id
841 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.INTERNAL_ORGANIZATION_ID(1),
842 -- application_id
843 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.APPLICATION_ID(1),
844 -- entity_code
845 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.ENTITY_CODE(1),
846 -- event_class_code
847 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.EVENT_CLASS_CODE(1),
848 -- event_type_code
849 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.EVENT_TYPE_CODE(1),
850 -- trx_id
851 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.TRX_ID(1),
852 -- trx_date
853 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.TRX_DATE(1),
854 -- ledger_id
855 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.LEDGER_ID(1),
856 -- trx_currency_code
857 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.TRX_CURRENCY_CODE(1),
858 -- currency_conversion_date
859 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.CURRENCY_CONVERSION_DATE(1),
860 -- currency_conversion_rate
861 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.CURRENCY_CONVERSION_RATE(1),
862 -- currency_conversion_type
863 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.CURRENCY_CONVERSION_TYPE(1),
864 -- minimum_accountable_unit
865 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.MINIMUM_ACCOUNTABLE_UNIT(1),
866 -- precision
867 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.PRECISION(1),
868 -- legal_entity_id
869 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.LEGAL_ENTITY_ID(1),
870 -- trx_level_type
871 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.TRX_LEVEL_TYPE(1),
872 -- line_level_action ?????? *****
873 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.LINE_LEVEL_ACTION(1),
874 -- trx_line_id
875 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.TRX_LINE_ID(1),
876 -- trx_business_category
877 -- receivables_trx_type_id
878 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.RECEIVABLES_TRX_TYPE_ID(1),
879 -- tax_reporting_flag
880 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.TAX_REPORTING_FLAG(1),
881 -- Quote_Flag
882 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.QUOTE_FLAG(1),
883 -- output_tax_classification_code
884 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.OUTPUT_TAX_CLASSIFICATION_CODE(1),
885 -- interface_entity_code
886 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.INTERFACE_ENTITY_CODE(1),
887 -- interface_line_id
888 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.INTERFACE_LINE_ID(1),
889 -- trx_line_number
890 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.TRX_LINE_NUMBER(1),
891 -- historical_flag
892 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.HISTORICAL_FLAG(1),
893 -- trx_number
894 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.TRX_NUMBER(1),
895 -- trx_description
896 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.TRX_DESCRIPTION(1),
897 -- trx_communicated_date
898 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.TRX_COMMUNICATED_DATE(1),
899 -- batch_source_id
900 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.BATCH_SOURCE_ID(1),
901 -- batch_source_name
902 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.BATCH_SOURCE_NAME(1),
903 -- doc_seq_id
904 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.DOC_SEQ_ID(1),
905 -- doc_seq_name
906 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.DOC_SEQ_NAME(1),
907 -- doc_seq_value
908 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.DOC_SEQ_VALUE(1),
909 -- trx_due_date
910 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.TRX_DUE_DATE(1),
911 -- trx_type_description
912 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.TRX_TYPE_DESCRIPTION(1),
913 -- trx_line_gl_date
914 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.TRX_LINE_GL_DATE(1),
915 -- line_class
916 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.LINE_CLASS(1),
917 -- trx_shipping_date
918 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.TRX_SHIPPING_DATE(1),
919 -- trx_line_type
920 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.TRX_LINE_TYPE(1),
921 -- trx_line_date
922 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.TRX_LINE_DATE(1),
923 -- line_amt_includes_tax_flag
924 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.LINE_AMT_INCLUDES_TAX_FLAG(1),
925 -- line_amt
926 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.LINE_AMT(1),
927 -- trx_line_quantity
928 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.TRX_LINE_QUANTITY(1),
929 -- unit_price
930 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.UNIT_PRICE(1),
931 -- exemption_control_flag
932 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.EXEMPTION_CONTROL_FLAG(1),
933 -- exempt_certificate_number
934 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.EXEMPT_CERTIFICATE_NUMBER(1),
935 -- exempt_reason
936 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.EXEMPT_REASON_CODE(1),
937 -- product_id
938 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.PRODUCT_ID(1),
939 -- uom_code
940 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.UOM_CODE(1),
941 -- fob_point
942 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.FOB_POINT(1),
943 -- ship_from_party_id, location_id
944 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.SHIP_FROM_PARTY_ID(1),
945 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.SHIP_FROM_LOCATION_ID(1),
946 -- bill_to_party_id
947 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.BILL_TO_PARTY_ID(1),
948 -- rounding_bill_to_party_id
949 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.ROUNDING_BILL_TO_PARTY_ID(1),
950 -- bill_to_party_site_id
951 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.BILL_TO_PARTY_SITE_ID(1),
952 -- rndg_bill_to_party_site_id
953 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.RNDG_BILL_TO_PARTY_SITE_ID(1),
954 -- bill_to_location_id
955 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.BILL_TO_LOCATION_ID(1),
956 -- SHIP TO information for later derivation
957 l_hdr_ship_to_cust_id,
958 l_hdr_ship_to_su_id,
959 l_line_ship_to_cust_id,
960 l_line_ship_to_su_id,
961 -- trx_line_currency_code
962 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.TRX_LINE_CURRENCY_CODE(1),
963 -- trx_line_precison
964 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.TRX_LINE_PRECISION(1),
965 --adjusted_doc_application_id,
966 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.ADJUSTED_DOC_APPLICATION_ID(1),
967 --adjusted_doc_entity_code,
968 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.ADJUSTED_DOC_ENTITY_CODE(1),
969 --adjusted_doc_event_class_code,
970 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.ADJUSTED_DOC_EVENT_CLASS_CODE(1),
971 --adjusted_doc_trx_id,
972 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.ADJUSTED_DOC_TRX_ID(1),
973 --adjusted_doc_line_id,
974 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.ADJUSTED_DOC_LINE_ID(1),
975 --adjusted_doc_trx_level_type,
976 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.ADJUSTED_DOC_TRX_LEVEL_TYPE(1),
977 --adjusted_doc_number,
978 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.ADJUSTED_DOC_NUMBER(1),
979 --adjusted_doc_date
980 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.ADJUSTED_DOC_DATE(1),
981 /* 4666566 */
982 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.BILL_THIRD_PTY_ACCT_ID(1),
983 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.BILL_TO_CUST_ACCT_SITE_USE_ID(1),
984 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.BILL_THIRD_PTY_ACCT_SITE_ID(1),
985 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.PRODUCT_ORG_ID(1),
986 /* 5082548 - poo and poa values */
987 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.poa_party_id(1),
988 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.poa_location_id(1),
989 /* 6874006 - moved poo values to separate statement below */
990 /* 5345904 - related_doc columns */
991 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.related_doc_application_id(1),
992 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.related_doc_entity_code(1),
993 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.related_doc_event_class_code(1),
994 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.related_doc_trx_id(1),
995 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.related_doc_number(1),
996 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.related_doc_date(1),
997 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.bill_from_location_id(1),
998 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.product_category(1),
999 ZX_GLOBAL_STRUCTURES_PKG.TRX_LINE_DIST_TBL.TRX_LINE_DESCRIPTION(1),
1000 l_allow_overapp
1001 FROM
1002 RA_CUSTOMER_TRX TRX,
1003 RA_CUSTOMER_TRX_LINES LINES,
1004 RA_CUST_TRX_LINE_GL_DIST REC,
1005 FND_CURRENCIES CURR,
1006 FND_DOCUMENT_SEQUENCES SEQ,
1007 AR_SYSTEM_PARAMETERS AR,
1008 RA_BATCH_SOURCES BS,
1009 RA_CUST_TRX_TYPES TYPES,
1010 HZ_CUST_ACCOUNTS BILL_CUST,
1011 HZ_PARTIES BILL_PARTY,
1012 HZ_CUST_ACCT_SITES BILL_AS,
1013 HZ_CUST_SITE_USES BILL_SU,
1014 HZ_PARTY_SITES BILL_PS,
1015 HZ_LOCATIONS BILL_LOC,
1016 RA_CUSTOMER_TRX INV,
1017 RA_CUST_TRX_TYPES INV_TT,
1018 RA_CUSTOMER_TRX_LINES INV_L,
1019 HR_ALL_ORGANIZATION_UNITS HR,
1020 HR_ORGANIZATION_UNITS HRL,
1021 RA_CUSTOMER_TRX REL_T,
1022 RA_CUST_TRX_TYPES REL_TT,
1023 AR_MEMO_LINES_B ML
1024 WHERE
1025 TRX.customer_trx_id = p_customer_trx_id and
1026 TRX.customer_trx_id = LINES.customer_trx_id and
1027 TRX.previous_customer_trx_id = INV.customer_trx_id (+) and
1028 LINES.previous_customer_trx_line_id = INV_L.customer_trx_line_id (+) and
1029 INV.cust_trx_type_id = INV_TT.cust_trx_type_id (+) and
1030 TRX.doc_sequence_id = SEQ.doc_sequence_id (+) and
1031 LINES.customer_trx_line_id = p_customer_trx_line_id and
1032 REC.customer_Trx_id = TRX.customer_Trx_id and
1033 REC.account_class = 'REC' and
1034 REC.latest_rec_flag = 'Y' and
1035 TRX.invoice_currency_code = CURR.currency_code and
1036 TRX.org_id = AR.org_id and
1037 TRX.batch_source_id = BS.batch_source_id and
1038 TRX.cust_trx_type_id = TYPES.cust_trx_type_id and
1039 TRX.bill_to_customer_id = BILL_CUST.cust_account_id and
1040 BILL_CUST.party_id = BILL_PARTY.party_id and
1041 BILL_CUST.cust_account_id = BILL_AS.cust_account_id and
1042 BILL_AS.cust_acct_site_id = BILL_SU.cust_acct_site_id and
1043 BILL_SU.site_use_id = TRX.bill_to_site_use_id and
1044 BILL_AS.party_site_id = BILL_PS.party_site_id and
1045 BILL_PS.location_id = BILL_LOC.location_id and
1046 LINES.warehouse_id = HR.organization_id (+) and
1047 TRX.org_id = HRL.organization_id and
1048 TRX.related_customer_trx_id = REL_T.customer_trx_id (+) and
1049 REL_T.cust_trx_type_id = REL_TT.cust_trx_type_id (+) and
1050 LINES.memo_line_id = ML.memo_line_id (+) and
1051 LINES.org_id = ML.org_id(+);
1052
1053 --bug6770861, Passing product category to ZX structure.
1054 -- 6874006 - merged memo line/product category into main insert
1055 --Bug6126010 begin, removed the existing query to find revenue CCID and added new query to retrieve CCID for REV account and passing it for tax calculation.
1056
1057 IF PG_DEBUG in ('Y','C') THEN
1058 arp_util.debug('Fetching values of primary_salesrep_id, memo_line_id, inventory_item_id and cust_trx_type_id to use it for calculating CCID of REV account.');
1059 END IF;
1060
1061 SELECT ctl.inventory_item_id, ctl.memo_line_id,
1062 ctx.cust_trx_type_id, ctx.primary_salesrep_id
1063 INTO p_inv_item_id, p_memo_line_id, p_trx_type_id, p_salesrep_id
1064 FROM ra_customer_trx_lines ctl, ra_customer_trx ctx
1065 WHERE ctl.customer_trx_id = p_customer_trx_id
1066 AND ctl.customer_trx_line_id = p_customer_trx_line_id
1067 AND ctl.customer_trx_id=ctx.customer_trx_id;
1068
1069 IF PG_DEBUG in ('Y','C') THEN
1070 arp_util.debug('Calling ARP_Auto_Accounting');
1071 arp_util.debug('customer_trx_id : '||p_customer_trx_id);
1072 arp_util.debug('customer_trx__line_id : '||p_customer_trx_line_id);
1073 arp_util.debug('trx_type_id : '||p_trx_type_id);
1074 arp_util.debug('primary_salesrep_id : '||p_salesrep_id);
1075 arp_util.debug('inventory_item_id : '||p_inv_item_id);
1076 arp_util.debug('Memo_line_id : '||p_memo_line_id);
1077 END IF;
1078
1079 ARP_Auto_Accounting.do_autoaccounting(
1080 p_mode => 'G',
1081 p_account_class => 'REV',
1082 p_customer_trx_id => p_customer_trx_id,
1083 p_customer_trx_line_id => p_customer_trx_line_id,
1084 p_cust_trx_line_salesrep_id => NULL,
1085 p_request_id => NULL,
1086 p_gl_date => NULL,
1087 p_original_gl_date => NULL,
1088 p_total_trx_amount => NULL,
1089 p_passed_ccid => NULL,
1090 p_force_account_set_no => NULL,
1091 p_cust_trx_type_id => p_trx_type_id,
1092 p_primary_salesrep_id => p_salesrep_id,
1093 p_inventory_item_id => p_inv_item_id,
1094 p_memo_line_id => p_memo_line_id,
1095 p_warehouse_id => p_warehouse_id, /* Bug 8758638 */
1096 p_ccid => p_ccid,
1097 p_concat_segments => p_conc_seg,
1098 p_failure_count => p_num_fail);
1099 /* Bug 8758638 */
1100 IF NVL(p_ccid,-1) = -1 THEN
1101 IF PG_DEBUG in ('Y','C') THEN
1102 arp_util.debug('Failure Count ' || p_num_fail);
1103 arp_util.debug('EXCEPTION: ARP_Auto_Accounting returned 0'||
1104 ' and no ccid is being passed for Tax Calculation.');
1105 END IF;
1106 p_ccid := NULL;
1107
1108 ELSE
1109 IF PG_DEBUG in ('Y','C') THEN
1110 arp_util.debug('REV CCID passed for tax calculation :'|| p_ccid);
1111 END IF;
1112
1113 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.ACCOUNT_CCID(1):= p_ccid;
1114 END IF;
1115 --Bug6126010 End.
1116
1117 l_trx_date := ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.TRX_DATE(1);
1118
1119 /* 6874006 - set POO columns based on primary salesrep */
1120 IF p_salesrep_id IS NOT NULL AND
1121 p_salesrep_id <> -3
1122 THEN
1123 IF p_salesrep_id <> pg_salesrep_id
1124 THEN
1125 pg_salesrep_id := p_salesrep_id;
1126
1127 IF PG_DEBUG in ('Y','C') THEN
1128 arp_util.debug('Fetching poo values for salesrep');
1129 arp_util.debug(' salesrep_id = ' || pg_salesrep_id);
1130 arp_util.debug(' org_id = ' || pg_org_id);
1131 END IF;
1132
1133 BEGIN
1134 select SR_PER.organization_id, -- poo_party_id
1135 SR_HRL.location_id -- poo_location_id
1136 into pg_poo_party_id, pg_poo_location_id
1137 from JTF_RS_SALESREPS SR,
1138 PER_ALL_ASSIGNMENTS_F SR_PER,
1139 HR_ORGANIZATION_UNITS SR_HRL
1140 where SR.salesrep_id = pg_salesrep_id
1141 and SR.org_id = pg_org_id
1142 and SR.person_id = SR_PER.person_id
1143 and l_trx_date BETWEEN
1144 nvl(SR_PER.effective_start_date, l_trx_date)
1145 AND nvl(SR_PER.effective_end_date, l_trx_date)
1146 and NVL(SR_PER.primary_flag, 'Y') = 'Y'
1147 and SR_PER.assignment_type = 'E'
1148 and SR_PER.organization_id = SR_HRL.organization_id;
1149 EXCEPTION
1150 WHEN NO_DATA_FOUND THEN
1151 pg_poo_party_id := NULL;
1152 pg_poo_location_id := NULL;
1153 END;
1154 END IF;
1155 ELSE
1156 pg_poo_party_id := NULL;
1157 pg_poo_location_id := NULL;
1158 END IF;
1159
1160 IF PG_DEBUG in ('Y','C') THEN
1161 arp_util.debug('poo_party_id := ' || pg_poo_party_id);
1162 arp_util.debug('poo_location_id = ' || pg_poo_location_id);
1163 END IF;
1164
1165 /* Now copy POO values from cache or POA */
1166 IF pg_poo_party_id IS NOT NULL
1167 THEN
1168 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.poo_party_id(1) :=
1169 pg_poo_party_id;
1170 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.poo_location_id(1) :=
1171 pg_poo_location_id;
1172 ELSE
1173 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.poo_party_id(1) :=
1174 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.poa_party_id(1);
1175 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.poo_location_id(1) :=
1176 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.poa_location_id(1);
1177 END IF;
1178
1179 /* 5235410 - Set max discount amount */
1180 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.CASH_DISCOUNT(1) :=
1181 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.LINE_AMT(1) *
1182 arp_etax_util.get_discount_rate(p_customer_trx_id);
1183
1184 /* need to derive the ship_to_party_id and the ship_to_party_site_id
1185 * from either the line or the header level if possible */
1186
1187 IF ( l_line_ship_to_cust_id IS NOT NULL and
1188 l_line_ship_to_su_id IS NOT NULL) THEN
1189 l_cust_id := l_line_ship_to_cust_id;
1190 l_site_use_id := l_line_ship_to_su_id;
1191 ELSIF ( l_hdr_ship_to_cust_id IS NOT NULL and
1192 l_hdr_ship_to_su_id IS NOT NULL) THEN
1193 l_cust_id := l_hdr_ship_to_cust_id;
1194 l_site_use_id := l_hdr_ship_to_su_id;
1195 ELSE
1196 l_cust_id := NULL;
1197 l_site_use_id := NULL;
1198
1199 -- ship_to_party_id
1200 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.SHIP_TO_PARTY_ID(1) := NULL;
1201 -- rounding_ship_to_party_id
1202 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.ROUNDING_SHIP_TO_PARTY_ID(1)
1203 := NULL;
1204 -- ship_to_party_site_id
1205 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.SHIP_TO_PARTY_SITE_ID(1)
1206 := NULL;
1207 -- rndg_ship_to_party_site_id
1208 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.RNDG_SHIP_TO_PARTY_SITE_ID(1)
1209 := NULL;
1210 -- ship_to_location_id
1211 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.SHIP_TO_LOCATION_ID(1) := NULL;
1212
1213 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.SHIP_THIRD_PTY_ACCT_SITE_ID(1)
1214 := NULL;
1215 END IF;
1216
1217 IF (l_cust_id IS NOT NULL and l_site_use_id IS NOT NULL) THEN
1218
1219 /* 4666566 - set these fields */
1220 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.SHIP_THIRD_PTY_ACCT_ID(1) :=
1221 l_cust_id;
1222 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.SHIP_TO_CUST_ACCT_SITE_USE_ID(1) :=
1223 l_site_use_id;
1224
1225 SELECT
1226 CUST_ACCT.party_id,
1227 CUST_ACCT.party_id,
1228 ACCT_SITE.party_site_id,
1229 ACCT_SITE.party_site_id,
1230 LOC.location_id,
1231 ACCT_SITE.cust_acct_site_id
1232 INTO
1233 -- ship_to_party_id
1234 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.SHIP_TO_PARTY_ID(1),
1235 -- rounding_ship_to_party_id
1236 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.ROUNDING_SHIP_TO_PARTY_ID(1),
1237 -- ship_to_party_site_id
1238 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.SHIP_TO_PARTY_SITE_ID(1),
1239 -- rndg_ship_to_party_site_id
1240 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.RNDG_SHIP_TO_PARTY_SITE_ID(1),
1241 -- ship_to_location_id
1242 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.SHIP_TO_LOCATION_ID(1),
1243 /* 4666566 */
1244 -- ship_third_pty_acct_site_id (warehouse id)
1245 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.SHIP_THIRD_PTY_ACCT_SITE_ID(1)
1246 FROM
1247 hz_cust_accounts CUST_ACCT,
1248 hz_parties PARTY,
1249 hz_cust_acct_sites ACCT_SITE,
1250 hz_cust_site_uses SITE_USES,
1251 hz_party_sites PARTY_SITE,
1252 hz_locations LOC
1253 WHERE
1254 CUST_ACCT.cust_account_id = l_cust_id AND
1255 CUST_ACCT.party_id = PARTY.party_id AND
1256 CUST_ACCT.cust_account_id = ACCT_SITE.cust_account_id AND
1257 ACCT_SITE.cust_acct_site_id = SITE_USES.cust_acct_site_id AND
1258 SITE_USES.site_use_id = l_site_use_id AND
1259 ACCT_SITE.party_site_id = PARTY_SITE.party_site_id AND
1260 PARTY_SITE.location_id = LOC.location_id;
1261
1262 END IF;
1263
1264 /* 11785145 - calculate total tax recoverable remaining on invoice */
1265 IF ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.adjusted_doc_trx_id(1)
1266 IS NOT NULL
1267 AND (p_called_from IS NULL OR p_called_from <> 'CM_CREDIT_LINES') --Bug 14724380
1268 THEN
1269 IF l_allow_overapp <> 'Y' THEN
1270 SELECT sum(invp.amount_line_items_remaining) /
1271 count(distinct cml.customer_trx_line_id),
1272 sum(invp.tax_remaining) /
1273 count(distinct cml.customer_trx_line_id),
1274 sum(cml.extended_amount) /
1275 count(distinct invp.payment_schedule_id)
1276 INTO l_inv_line_remaining,
1277 l_inv_tax_remaining,
1278 l_cm_line_total
1279 FROM ra_customer_trx cm,
1280 ra_customer_trx_lines cml,
1281 ar_payment_schedules invp
1282 WHERE cm.customer_trx_id = p_customer_trx_id
1283 AND cm.customer_trx_id = cml.customer_trx_id
1284 AND cml.line_type = 'LINE'
1285 AND cm.previous_customer_trx_id = invp.customer_trx_id;
1286
1287 IF PG_DEBUG in ('Y','C') THEN
1288 arp_util.debug('setting credit memo tax limit');
1289 arp_util.debug(' allow_overapp : ' || l_allow_overapp);
1290 arp_util.debug(' invoice line_remaining: ' || l_inv_line_remaining);
1291 arp_util.debug(' cm line total : ' || l_cm_line_total);
1292 arp_util.debug(' invoice tax remaining : ' || l_inv_tax_remaining);
1293 END IF;
1294
1295 IF l_cm_line_total * -1 = l_inv_line_remaining
1296 THEN
1297 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.CTRL_TOTAL_HDR_TX_AMT(1) :=
1298 l_inv_tax_remaining * -1;
1299 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.CTRL_HDR_TX_APPL_FLAG(1) := 'Y';
1300 ELSE
1301 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.CTRL_TOTAL_HDR_TX_AMT(1) := NULL;
1302 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.CTRL_HDR_TX_APPL_FLAG(1) := 'N';
1303 END IF;
1304 ELSE --- Over application = 'Y'
1305 /*
1306 Bug# 16559866 : we now insure that if you are crediting the remaining line balance of an invoice,
1307 we will provide a tax amount that also closes the tax bucket.
1308 */
1309 BEGIN
1310 SELECT inv_pay.amount_line_items_remaining, inv_pay.tax_remaining,
1311 cm_line.line_amount, cm_line.tax_amount
1312 INTO l_inv_line_remaining, l_inv_tax_remaining, l_cmline_line_amount, l_cmline_tax_amount
1313 FROM ar_payment_schedules inv_pay, ra_customer_trx cm, ra_cm_requests cm_line
1314 WHERE cm.customer_trx_id = p_customer_trx_id
1315 AND cm_line.customer_trx_id = cm.previous_customer_trx_id
1316 AND cm_line.cm_customer_trx_id is null
1317 AND cm.previous_customer_trx_id = inv_pay.customer_trx_id;
1318
1319 IF PG_DEBUG in ('Y','C') THEN
1320 arp_util.debug('setting credit memo tax limit for over application');
1321 arp_util.debug(' l_inv_line_remaining : ' || l_inv_line_remaining);
1322 arp_util.debug(' l_inv_tax_remaining : ' || l_inv_tax_remaining);
1323 arp_util.debug(' l_cmline_line_amount : ' || l_cmline_line_amount);
1324 arp_util.debug(' l_cmline_tax_amount : ' || l_cmline_tax_amount);
1325 END IF;
1326
1327 IF (l_inv_line_remaining > 0) THEN
1328 l_inv_line_remaining := l_inv_line_remaining * -1;
1329 l_inv_tax_remaining := l_inv_tax_remaining * -1;
1330 END IF;
1331
1332 IF (l_inv_tax_remaining = l_cmline_tax_amount and l_inv_line_remaining = l_cmline_line_amount)
1333 THEN
1334 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.CTRL_TOTAL_HDR_TX_AMT(1) := l_cmline_tax_amount;
1335 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.CTRL_HDR_TX_APPL_FLAG(1) := 'Y';
1336 ELSE
1337 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.CTRL_TOTAL_HDR_TX_AMT(1) := NULL;
1338 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.CTRL_HDR_TX_APPL_FLAG(1) := 'N';
1339 END IF;
1340 --
1341 EXCEPTION
1342 WHEN NO_DATA_FOUND THEN
1343 SELECT sum(invp.amount_line_items_remaining) /
1344 count(distinct cml.customer_trx_line_id),
1345 sum(invp.tax_remaining) /
1346 count(distinct cml.customer_trx_line_id),
1347 sum(cml.extended_amount) /
1348 count(distinct invp.payment_schedule_id)
1349 INTO l_inv_line_remaining,
1350 l_inv_tax_remaining,
1351 l_cm_line_total
1352 FROM ra_customer_trx cm,
1353 ra_customer_trx_lines cml,
1354 ar_payment_schedules invp
1355 WHERE cm.customer_trx_id = p_customer_trx_id
1356 AND cm.customer_trx_id = cml.customer_trx_id
1357 AND cml.line_type = 'LINE'
1358 AND cm.previous_customer_trx_id = invp.customer_trx_id;
1359 --
1360 IF PG_DEBUG in ('Y','C') THEN
1361 arp_util.debug('provides a tax amount that also closes the tax bucket');
1362 arp_util.debug(' invoice line_remaining: ' || l_inv_line_remaining);
1363 arp_util.debug(' cm line total : ' || l_cm_line_total);
1364 arp_util.debug(' invoice tax remaining : ' || l_inv_tax_remaining);
1365 END IF;
1366
1367 IF l_cm_line_total * -1 = l_inv_line_remaining
1368 THEN
1369 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.CTRL_TOTAL_HDR_TX_AMT(1) :=
1370 l_inv_tax_remaining * -1;
1371 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.CTRL_HDR_TX_APPL_FLAG(1) := 'Y';
1372 ELSE
1373 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.CTRL_TOTAL_HDR_TX_AMT(1) := NULL;
1374 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.CTRL_HDR_TX_APPL_FLAG(1) := 'N';
1375 END IF;
1376 WHEN OTHERS THEN
1377 arp_util_tax.debug( 'EXCEPTION: arp_etax_services_pkg.setting credit memo tax limit for over application');
1378 RAISE;
1379 END;
1380 END IF; -- l_allow_overapp = 'Y
1381 ELSE
1382 /* Not a regular credit memo, do not use */
1383 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.CTRL_TOTAL_HDR_TX_AMT(1) := NULL;
1384 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.CTRL_HDR_TX_APPL_FLAG(1) := 'N';
1385 END IF;
1386
1387 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.RELATED_DOC_DATE(1) := NULL;
1388 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.ROUNDING_SHIP_FROM_PARTY_ID(1) := NULL;
1389 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.ROUNDING_BILL_FROM_PARTY_ID(1) := NULL;
1390 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.RNDG_SHIP_FROM_PARTY_SITE_ID(1) := NULL;
1391 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.RNDG_BILL_FROM_PARTY_SITE_ID(1) := NULL;
1392 /** Following is for tax only CMs **/
1393 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.CTRL_TOTAL_LINE_TX_AMT(1) := p_tax_amount;
1394
1395 IF PG_DEBUG in ('Y','C') THEN
1396 arp_etax_services_pkg.print_ebt_plsql_vars;
1397 arp_util.debug('populate_ebt_plsql_tables(-)');
1398 END IF;
1399 END populate_ebt_plsql_tables;
1400
1401 /*=============================================================================
1402 | PROCEDURE set_line_flags
1403 |
1404 | DESCRIPTION
1405 | Checks each of the attributes of an invoice line that can affect the
1406 | tax amount calculated, and returns true for each field that has changed.
1407 |
1408 | PARAMETERS:
1409 | IN : p_customer_trx_line_id
1410 | p_item_line_rec
1411 | OUT : p_inventory_item_changed
1412 | p_extended_amount_changed
1413 | p_tax_exempt_flag_change
1414 | p_tax_exempt_reason_changed
1415 | p_tax_exempt_cert_changed
1416 | p_memo_line_changed
1417 | p_quantity_changed
1418 | p_tax_code_changed
1419 | p_warehouse_flag_changed
1420 | p_ship_to_changed
1421 |
1422 | MODIFICATION HISTORY
1423 | DATE Author Description of Changes
1424 | 13-JUN-2005 Debbie Sue Jancis Created
1425 |
1426 *===========================================================================*/
1427 PROCEDURE set_line_flags(
1428 p_customer_trx_line_id IN NUMBER,
1429 p_line_rec IN ra_customer_trx_lines%rowtype,
1430 p_inventory_item_changed OUT NOCOPY boolean,
1431 p_memo_line_changed OUT NOCOPY boolean,
1432 p_quantity_changed OUT NOCOPY boolean,
1433 p_extended_amount_changed OUT NOCOPY boolean,
1434 p_tax_exempt_flag_changed OUT NOCOPY boolean,
1435 p_tax_exempt_reason_changed OUT NOCOPY boolean,
1436 p_tax_exempt_cert_changed OUT NOCOPY boolean,
1437 p_tax_code_changed OUT NOCOPY boolean,
1438 p_warehouse_flag_changed OUT NOCOPY boolean,
1439 p_ship_to_changed OUT NOCOPY boolean ) IS
1440
1441 l_old_line_rec ra_customer_trx_lines%rowtype;
1442
1443 l_inventory_item_changed BOOLEAN;
1444 l_memo_line_changed BOOLEAN;
1445 l_quantity_changed BOOLEAN;
1446 l_extended_amount_changed BOOLEAN;
1447 l_tax_exempt_flag_changed BOOLEAN;
1448 l_tax_exempt_reason_changed BOOLEAN;
1449 l_tax_exempt_cert_changed BOOLEAN;
1450 l_tax_code_changed BOOLEAN;
1451 l_warehouse_flag_changed BOOLEAN;
1452 l_ship_to_changed BOOLEAN;
1453
1454 BEGIN
1455 arp_util.debug('ARP_ETAX_SERVICES_PKG.set_line_flags()+');
1456 arp_util.debug('p_cust_trx_line_id = ' || to_char(p_customer_trx_line_id));
1457
1458 /*-----------------------------------------+
1459 | Fetch the old record from the database |
1460 +-----------------------------------------*/
1461
1462 arp_ctl_pkg.fetch_p(l_old_line_rec, p_customer_trx_line_id);
1463
1464 /*--------------------------------------------------+
1465 | Compare the fetched record with the new record |
1466 +--------------------------------------------------*/
1467
1468 -- pg_new_line_rec := p_line_rec;
1469
1470 -- did inventory_item_id change?
1471 IF ( nvl(l_old_line_rec.inventory_item_id, 0) <>
1472 nvl(p_line_rec.inventory_item_id, 0)
1473 AND nvl(p_line_rec.inventory_item_id,0) <> AR_NUMBER_DUMMY ) THEN
1474 l_inventory_item_changed := TRUE;
1475 arp_standard.debug('inventory item id changed');
1476 ELSE
1477 l_inventory_item_changed := FALSE;
1478 END IF;
1479
1480 -- did memo_line_id change?
1481 IF ( nvl(l_old_line_rec.memo_line_id, 0) <> nvl(p_line_rec.memo_line_id, 0)
1482 AND nvl(p_line_rec.memo_line_id,0) <> AR_NUMBER_DUMMY ) THEN
1483 l_memo_line_changed := TRUE;
1484 arp_standard.debug('memo line id changed');
1485 ELSE
1486 l_memo_line_changed := FALSE;
1487 END IF;
1488
1489 -- did quantity_invoiced change?
1490 IF ( nvl(l_old_line_rec.quantity_invoiced, 0) <>
1491 nvl(p_line_rec.quantity_invoiced, 0)
1492 AND nvl(p_line_rec.quantity_invoiced,0) <> AR_NUMBER_DUMMY ) THEN
1493 l_quantity_changed := TRUE;
1494 arp_standard.debug('quantity changed');
1495 ELSE
1496 l_quantity_changed := FALSE;
1497 END IF;
1498
1499 -- did gross extended amount change?
1500 IF ( nvl(l_old_line_rec.gross_extended_amount,
1501 l_old_line_rec.extended_amount) =
1502 nvl(p_line_rec.gross_extended_amount, p_line_rec.extended_amount) AND
1503 pg_tax_amount_changed = FALSE) THEN
1504 l_extended_amount_changed := FALSE;
1505 arp_standard.debug('extended amount did not change');
1506 ELSE
1507 l_extended_amount_changed := TRUE;
1508 arp_standard.debug('extended amount chnged');
1509 END IF;
1510
1511 -- did tax_exempt_flag change?
1512 IF ( nvl(l_old_line_rec.tax_exempt_flag, 'S') <>
1513 nvl(p_line_rec.tax_exempt_flag, 'S')
1514 AND nvl(p_line_rec.tax_exempt_flag, 'S') <> AR_FLAG_DUMMY )
1515 THEN
1516 l_tax_exempt_flag_changed := TRUE;
1517 arp_standard.debug('tax exempt flag chnged');
1518 ELSE
1519 l_tax_exempt_flag_changed := FALSE;
1520 END IF;
1521
1522 -- did tax_exempt_reason_code change?
1523 IF ( nvl(l_old_line_rec.tax_exempt_reason_code, '0') <>
1524 nvl(p_line_rec.tax_exempt_reason_code, '0')
1525 AND nvl(p_line_rec.tax_exempt_reason_code, '0') <> AR_TEXT_DUMMY )
1526 THEN
1527 l_tax_exempt_reason_changed := TRUE;
1528 arp_standard.debug('tax exempt reason flag chnged');
1529 ELSE
1530 l_tax_exempt_reason_changed := FALSE;
1531 END IF;
1532
1533 -- did tax_exempt_number change?
1534 IF ( nvl(l_old_line_rec.tax_exempt_number, '0') <>
1535 nvl(p_line_rec.tax_exempt_number, '0')
1536 AND nvl(p_line_rec.tax_exempt_number, '0') <> AR_TEXT_DUMMY )
1537 THEN
1538 l_tax_exempt_cert_changed := TRUE;
1539 arp_standard.debug('tax exempt cert chnged');
1540 ELSE
1541 l_tax_exempt_cert_changed := FALSE;
1542 END IF;
1543
1544 -- did vat_tax_id or tax classification_code change?
1545 -- Added the if condition for historical transactions for the Bug Fix 6804913
1546 IF (NVL(l_old_line_rec.historical_flag, 'Y') = 'Y') AND (l_old_line_rec.tax_classification_code IS NULL)
1547 THEN
1548 l_tax_code_changed := FALSE;
1549 ELSIF ( ( nvl(l_old_line_rec.vat_tax_id, 0) <> nvl(p_line_rec.vat_tax_id, 0)
1550 AND nvl(p_line_rec.vat_tax_id,0) <> AR_NUMBER_DUMMY) OR
1551 ( nvl(l_old_line_rec.tax_classification_code, '0') <>
1552 nvl(p_line_rec.tax_classification_code, '0') AND
1553 nvl(p_line_rec.tax_classification_code,'0') <> AR_TEXT_DUMMY))
1554 THEN
1555 l_tax_code_changed := TRUE;
1556 arp_standard.debug('tax code changed');
1557 ELSE
1558 l_tax_code_changed := FALSE;
1559 END IF;
1560
1561 -- did warehouse_id change?
1562 IF ( nvl(l_old_line_rec.warehouse_id, 0) <> nvl(p_line_rec.warehouse_id, 0)
1563 AND nvl(p_line_rec.warehouse_id,0) <> AR_NUMBER_DUMMY) THEN
1564 l_warehouse_flag_changed := TRUE;
1565 arp_standard.debug('warehouse flag changed');
1566 ELSE
1567 l_warehouse_flag_changed := FALSE;
1568 END IF;
1569
1570 -- did ship to location change at the line level?
1571 IF ( nvl(l_old_line_rec.ship_to_site_use_id, 0) <>
1572 nvl(p_line_rec.ship_to_site_use_id, 0 ) AND
1573 nvl(p_line_rec.ship_to_site_use_id,0) <> AR_NUMBER_DUMMY) THEN
1574 l_ship_to_changed := TRUE;
1575 arp_standard.debug('ship to changed');
1576 ELSE
1577 l_ship_to_changed := FALSE;
1578 END IF;
1579
1580 -- do we need to check for GDF changes???
1581
1582 -- IF PG_DEBUG = 'Y' THEN
1583 arp_util_tax.debug('p_inventory_item_changed : '||
1584 arp_trx_util.boolean_to_varchar2(l_inventory_item_changed));
1585 arp_util_tax.debug('p_memo_line_changed : '||
1586 arp_trx_util.boolean_to_varchar2(l_memo_line_changed));
1587 arp_util_tax.debug('p_quantity_changed : '||
1588 arp_trx_util.boolean_to_varchar2(l_quantity_changed));
1589 arp_util_tax.debug('p_extended_amount_changed : ' ||
1590 arp_trx_util.boolean_to_varchar2(l_extended_amount_changed )); arp_util_tax.debug('p_tax_exempt_flag_changed : ' ||
1591 arp_trx_util.boolean_to_varchar2(l_tax_exempt_flag_changed )); arp_util_tax.debug('p_tax_exempt_reason_changed : ' ||
1592 arp_trx_util.boolean_to_varchar2(l_tax_exempt_reason_changed ));
1593 arp_util_tax.debug('p_tax_exempt_cert_changed : ' ||
1594 arp_trx_util.boolean_to_varchar2(l_tax_exempt_cert_changed )); arp_util_tax.debug('p_tax_code_changed : '||
1595 arp_trx_util.boolean_to_varchar2(l_tax_code_changed));
1596 arp_util_tax.debug('p_warehouse_flag_changed : '||
1597 arp_trx_util.boolean_to_varchar2(l_warehouse_flag_changed));
1598 arp_util_tax.debug('p_ship_to_changed : '||
1599 arp_trx_util.boolean_to_varchar2(l_ship_to_changed));
1600 -- END IF;
1601
1602 p_inventory_item_changed := l_inventory_item_changed;
1603 p_memo_line_changed := l_memo_line_changed;
1604 p_quantity_changed := l_quantity_changed;
1605 p_extended_amount_changed := l_extended_amount_changed;
1606 p_tax_exempt_flag_changed := l_tax_exempt_flag_changed;
1607 p_tax_exempt_reason_changed := l_tax_exempt_reason_changed;
1608 p_tax_exempt_cert_changed := l_tax_exempt_cert_changed;
1609 p_tax_code_changed := l_tax_code_changed;
1610 p_warehouse_flag_changed := l_warehouse_flag_changed;
1611 p_ship_to_changed := l_ship_to_changed;
1612
1613 pg_extended_amount_changed := l_extended_amount_changed;
1614
1615 arp_util.debug('ARP_ETAX_SERVICES_PKG.set_line_flags()-');
1616
1617 END set_line_flags;
1618
1619 /*===========================================================================+
1620 | PROCEDURE |
1621 | delete_tax_f_ctl_id |
1622 | |
1623 | DESCRIPTION |
1624 | This routine will delete one or more tax lines given the invoice line |
1625 | of type LINE that they can all be linked too, returning old and new |
1626 | tax amounts. |
1627 | |
1628 | SCOPE - PUBLIC |
1629 | |
1630 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
1631 | |
1632 | ARGUMENTS : IN: p_customer_trx_line_id |
1633 | p_error_mode -- default 'STANDARD' |
1634 | OUT: p_old_tax_amount |
1635 | p_new_tax_amount |
1636 | |
1637 | NOTES
1638 | |
1639 | MODIFICATION HISTORY |
1640 | 14-Jun-2005 Debbie Jancis Created |
1641 | |
1642 +===========================================================================*/
1643 PROCEDURE delete_tax_f_ctl_id( p_customer_trx_line_id IN Number ) IS
1644
1645 cursor s_tax_lines_for_inv_line( p_customer_trx_line_id in number ) IS
1646 SELECT lines.customer_trx_line_id
1647 FROM ra_customer_trx_lines lines
1648 WHERE link_to_cust_trx_line_id = p_customer_trx_line_id
1649 AND line_type = 'TAX';
1650
1651 BEGIN
1652
1653 arp_util.debug('ARP_ETAX_SERVICES_PKG.delete_tax_f_ctl_id()+');
1654
1655 FOR tax in s_tax_lines_for_inv_line( p_customer_trx_line_id )
1656 LOOP
1657
1658 arp_util.debug('customer trx line id = ' || to_char(p_customer_Trx_line_id));
1659 arp_util.debug('assoc customer trx line id = ' ||
1660 to_char(tax.customer_Trx_line_id));
1661
1662 /*********************************************************************
1663 | Delete the account assignments and account sets associated with |
1664 | this tax line. |
1665 *********************************************************************/
1666
1667 arp_ctlgd_pkg.delete_f_ctl_id( tax.customer_trx_line_id, null, null );
1668
1669 /*********************************************************************
1670 | Call the table handler to delete the tax record |
1671 *********************************************************************/
1672
1673 arp_ctl_pkg.delete_p( tax.customer_trx_line_id );
1674
1675
1676 END LOOP;
1677 arp_util.debug('ARP_ETAX_SERVICES_PKG.delete_tax_f_ctl_id()-');
1678
1679 EXCEPTION
1680 WHEN OTHERS
1681 THEN
1682 IF PG_DEBUG = 'Y' THEN
1683 arp_util_tax.debug( 'EXCEPTION: arp_etax_services_pkg.delete_tax_f_ctl_id()');
1684 END IF;
1685 RAISE;
1686
1687 END delete_tax_f_ctl_id;
1688
1689
1690 /*=============================================================================
1691 | PROCEDURE Before_Update_Line
1692 |
1693 | DESCRIPTION
1694 | Called from Invoice Line Entity handler. This proceudre will
1695 | check each of the attributes of an invoice line that can affect
1696 | tax and will return TRUE in p_recalc_tax if any of those attributes
1697 | have changed.
1698 |
1699 | PARAMETERS:
1700 | IN : p_customer_trx_line_id
1701 | p_item_line_rec
1702 | p_error_mode
1703 | OUT : p_old_tax_amount
1704 | p_new_tax_amount
1705 | p_recalc_tax
1706 |
1707 | MODIFICATION HISTORY
1708 | DATE Author Description of Changes
1709 | 13-JUN-2005 Debbie Sue Jancis Created
1710 |
1711 *===========================================================================*/
1712
1713 PROCEDURE before_update_line(
1714 p_customer_trx_line_id IN Number,
1715 p_line_rec IN ra_customer_trx_lines%rowtype,
1716 p_recalc_tax OUT NOCOPY BOOLEAN ) IS
1717
1718 l_inventory_item_changed BOOLEAN;
1719 l_memo_line_changed BOOLEAN;
1720 l_quantity_changed BOOLEAN;
1721 l_extended_amount_changed BOOLEAN;
1722 l_tax_exempt_flag_changed BOOLEAN;
1723 l_tax_exempt_reason_changed BOOLEAN;
1724 l_tax_exempt_cert_changed BOOLEAN;
1725 l_tax_code_changed BOOLEAN;
1726 l_warehouse_flag_changed BOOLEAN;
1727 l_ship_to_changed BOOLEAN;
1728 l_trx_id NUMBER;
1729
1730 BEGIN
1731 arp_util.debug('ARP_ETAX_SERVICES_PKG.before_update_line()+');
1732 arp_util.debug('customer_trx_line_id = ' || to_char(p_customer_trx_line_id));
1733
1734 arp_etax_services_pkg.set_line_flags(
1735 p_customer_trx_line_id,
1736 p_line_rec,
1737 l_inventory_item_changed,
1738 l_memo_line_changed,
1739 l_quantity_changed,
1740 l_extended_amount_changed,
1741 l_tax_exempt_flag_changed,
1742 l_tax_exempt_reason_changed,
1743 l_tax_exempt_cert_changed,
1744 l_tax_code_changed,
1745 l_warehouse_flag_changed,
1746 l_ship_to_changed);
1747
1748 pg_line_changed := l_inventory_item_changed OR
1749 l_memo_line_changed OR
1750 l_quantity_changed OR
1751 l_extended_amount_changed OR
1752 l_tax_exempt_flag_changed OR
1753 l_tax_exempt_reason_changed OR
1754 l_tax_exempt_cert_changed OR
1755 l_tax_code_changed OR
1756 l_warehouse_flag_changed OR
1757 l_ship_to_changed;
1758
1759 IF (pg_line_changed) THEN
1760 -- need to delete tax lines and distributions associated with the line_id
1761 arp_etax_services_pkg.delete_tax_f_ctl_id (p_customer_trx_line_id);
1762 END IF;
1763
1764 p_recalc_tax := pg_line_changed;
1765
1766 arp_util.debug('ARP_ETAX_SERVICES_PKG.before_update_line()-');
1767
1768 END before_update_line;
1769
1770 /*=============================================================================
1771 | FUNCTION Mark_tax_lines_deleted()
1772 |
1773 | DESCRIPTION
1774 | This function will call the ETAX mark_tax_lines_deleted service. This
1775 | API assumes that the calling code controls the commit cycle. This
1776 | function will return a TRUE if the call to the ETAX service is
1777 | successful, Otherwise, it will return FALSE.
1778 |
1779 | This should be called per invoice line.
1780 |
1781 | PARAMETERS:
1782 | IN : p_customer_trx_line_id
1783 | p_customer_trx_id
1784 |
1785 | MODIFICATION HISTORY
1786 | DATE Author Description of Changes
1787 | 14-JUN-2005 Debbie Sue Jancis Created
1788 |
1789 *===========================================================================*/
1790 FUNCTION Mark_Tax_Lines_Deleted( p_customer_trx_line_id IN Number,
1791 p_customer_trx_id IN Number)
1792 RETURN BOOLEAN IS
1793
1794
1795 CURSOR TRX_Header IS
1796 SELECT *
1797 FROM ra_customer_trx
1798 WHERE customer_trx_id = p_customer_trx_id;
1799
1800 l_event_class_code zx_trx_headers_gt.event_class_code%TYPE;
1801 l_event_type_code zx_trx_headers_gt.event_type_code%TYPE;
1802 l_transaction_line_rec zx_api_pub.transaction_line_rec_type;
1803 l_trx_header_rec ra_customer_trx%ROWTYPE;
1804
1805 l_return_status_service VARCHAR2(4000);
1806 l_msg_count NUMBER;
1807 l_msg_data VARCHAR2(4000);
1808 l_msg VARCHAR2(4000);
1809
1810 l_return_status BOOLEAN := TRUE;
1811 l_success BOOLEAN;
1812 BEGIN
1813
1814 arp_util.debug('ARP_ETAX_SERVICES_PKG.Mark_Tax_Lines_Deleted()+');
1815
1816 -- populate the trx header local record.
1817 BEGIN
1818 OPEN Trx_Header;
1819 FETCH Trx_Header INTO l_trx_header_rec;
1820 CLOSE Trx_Header;
1821 END;
1822
1823 -- get event class and event type codes
1824 l_success := arp_etax_util.get_event_information(
1825 p_customer_trx_id => p_customer_trx_id,
1826 p_action => 'UPDATE',
1827 p_event_class_code => l_event_class_code,
1828 p_event_type_code => l_event_type_code);
1829
1830 arp_util.debug('customer trx id = ' || p_customer_trx_id);
1831 arp_util.debug('event class code = ' || l_event_class_code);
1832 arp_util.debug('event type code = ' || l_event_type_code);
1833
1834 IF (l_success) THEN
1835 -- populate the transaction_line_rec for use in the tax service
1836 l_transaction_line_rec.internal_organization_id :=
1837 arp_global.sysparam.org_id;
1838 l_transaction_line_rec.application_id := 222;
1839 l_transaction_line_rec.entity_code := 'TRANSACTIONS';
1840 l_transaction_line_rec.event_class_code := l_event_class_code;
1841 l_transaction_line_rec.event_type_code := l_event_type_code;
1842 l_transaction_line_rec.trx_id := p_customer_trx_id;
1843 l_transaction_line_rec.trx_level_type := 'LINE';
1844 l_transaction_line_rec.trx_line_id := p_customer_trx_line_id;
1845
1846 -- Call the ETAX API
1847 zx_api_pub.mark_tax_lines_deleted(
1848 p_api_version => 1.0,
1849 p_init_msg_list => FND_API.G_TRUE,
1850 p_commit => FND_API.G_FALSE,
1851 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
1852 p_transaction_line_rec => l_transaction_line_rec,
1853 x_return_status => l_return_status_service,
1854 x_msg_count => l_msg_count,
1855 x_msg_data => l_msg_data);
1856
1857 -- verify the return status from the tax api.
1858 IF (l_return_status_service <> 'S') THEN -- handle errors
1859 l_return_status := FALSE;
1860 END IF;
1861 ELSE
1862 RETURN FALSE;
1863 END IF;
1864
1865 arp_util.debug('ARP_ETAX_SERVICES_PKG.Mark_Tax_Lines_Deleted()-');
1866 RETURN l_return_status;
1867
1868 EXCEPTION
1869 WHEN OTHERS THEN
1870 APP_EXCEPTION.RAISE_EXCEPTION;
1871
1872 END Mark_Tax_Lines_Deleted;
1873
1874 /*=============================================================================
1875 | PROCEDURE Before_Delete_Line
1876 |
1877 | DESCRIPTION
1878 | Called from Invoice Line Entity handler. This procedure will delete
1879 | the tax lines from ra_Customer_Trx_lines and its associated accounting
1880 | and call the etax api's to mark the records for deletion in the ZX tables
1881 |
1882 | PARAMETERS:
1883 | IN : p_customer_trx_line_id
1884 | p_customer_trx_id
1885 |
1886 | MODIFICATION HISTORY
1887 | DATE Author Description of Changes
1888 | 14-JUN-2005 Debbie Sue Jancis Created
1889 |
1890 *===========================================================================*/
1891 PROCEDURE Before_Delete_Line( p_customer_trx_line_id IN Number,
1892 p_customer_trx_id IN Number) IS
1893 l_success BOOLEAN;
1894 BEGIN
1895
1896 arp_util.debug('ARP_ETAX_SERVICES_PKG.before_delete_line()+');
1897
1898 -- delete tax line from ra_customer_Trx_lines and associated accting.
1899
1900 arp_etax_services_pkg.delete_tax_f_ctl_id(
1901 p_customer_trx_line_id => p_customer_trx_line_id);
1902
1903 l_success := arp_etax_services_pkg.Mark_Tax_Lines_Deleted (
1904 p_customer_trx_line_id => p_customer_trx_line_id,
1905 p_customer_trx_id => p_customer_trx_id);
1906
1907 IF (not l_success) THEN
1908 arp_util.debug('unable to mark tax for deletion');
1909 END IF;
1910
1911 arp_util.debug('ARP_ETAX_SERVICES_PKG.before_delete_line()-');
1912
1913 END Before_Delete_Line;
1914
1915
1916 PROCEDURE print_ebt_plsql_vars IS
1917 BEGIN
1918
1919 arp_util.debug(' internal_organization_id i:' ||
1920 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.INTERNAL_ORGANIZATION_ID(1) );
1921
1922 arp_util.debug('application_id: ' ||
1923 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.APPLICATION_ID(1) );
1924
1925 arp_util.debug('entity_code : ' ||
1926 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.ENTITY_CODE(1));
1927
1928 arp_util.debug('event_class_code : ' ||
1929 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.EVENT_CLASS_CODE(1));
1930
1931 arp_util.debug('event_type_code : ' ||
1932 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.EVENT_TYPE_CODE(1) );
1933
1934 arp_util.debug('trx_id : ' ||
1935 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.TRX_ID(1));
1936
1937 arp_util.debug('trx_date : ' ||
1938 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.TRX_DATE(1));
1939
1940 arp_util.debug('ledger_id : ' ||
1941 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.LEDGER_ID(1));
1942
1943 arp_util.debug('trx_currency_code : ' ||
1944 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.TRX_CURRENCY_CODE(1));
1945
1946 arp_util.debug('currency_conversion_date : ' ||
1947 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.CURRENCY_CONVERSION_DATE(1));
1948
1949 arp_util.debug('currency_conversion_rate : ' ||
1950 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.CURRENCY_CONVERSION_RATE(1));
1951
1952 arp_util.debug('currency_conversion_type : ' ||
1953 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.CURRENCY_CONVERSION_TYPE(1));
1954
1955 arp_util.debug('minimum_accountable_unit : ' ||
1956 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.MINIMUM_ACCOUNTABLE_UNIT(1));
1957
1958 arp_util.debug('precision : ' ||
1959 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.PRECISION(1));
1960
1961 arp_util.debug('legal_entity_id : ' ||
1962 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.LEGAL_ENTITY_ID(1));
1963
1964 arp_util.debug('rounding_ship_to_party_id : ' ||
1965 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.ROUNDING_SHIP_TO_PARTY_ID(1));
1966
1967 arp_util.debug('rounding_bill_to_party_id : ' ||
1968 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.ROUNDING_BILL_TO_PARTY_ID(1));
1969
1970 arp_util.debug('rndg_ship_to_party_site_id : ' ||
1971 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.RNDG_SHIP_TO_PARTY_SITE_ID(1));
1972
1973 arp_util.debug('rndg_bill_to_party_site_id :' ||
1974 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.RNDG_BILL_TO_PARTY_SITE_ID(1));
1975
1976 arp_util.debug('receivables_trx_type_id : ' ||
1977 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.RECEIVABLES_TRX_TYPE_ID(1));
1978
1979 arp_util.debug('tax_reporting_flag : ' ||
1980 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.TAX_REPORTING_FLAG(1));
1981
1982 arp_util.debug('quote_flag : ' ||
1983 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.QUOTE_FLAG(1));
1984
1985 arp_util.debug('trx_number : ' ||
1986 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.TRX_NUMBER(1));
1987
1988 arp_util.debug('trx_description : ' ||
1989 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.TRX_DESCRIPTION(1));
1990
1991 arp_util.debug('trx_communicated_date : ' ||
1992 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.TRX_COMMUNICATED_DATE(1));
1993
1994 arp_util.debug('batch_source_id : ' ||
1995 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.BATCH_SOURCE_ID(1));
1996
1997 arp_util.debug('batch_source_name : ' ||
1998 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.BATCH_SOURCE_NAME(1));
1999
2000 arp_util.debug('doc_seq_id : ' ||
2001 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.DOC_SEQ_ID(1));
2002
2003 arp_util.debug('doc_seq_name :' ||
2004 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.DOC_SEQ_NAME(1));
2005
2006 arp_util.debug('doc_seq_value : ' ||
2007 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.DOC_SEQ_VALUE(1));
2008
2009 arp_util.debug('trx_due_date : ' ||
2010 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.TRX_DUE_DATE(1));
2011
2012 arp_util.debug('trx_type_description : ' ||
2013 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.TRX_TYPE_DESCRIPTION(1));
2014
2015 arp_util.debug('trx_level_type : ' ||
2016 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.TRX_LEVEL_TYPE(1));
2017
2018 arp_util.debug('trx_line_id : ' ||
2019 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.TRX_LINE_ID(1));
2020
2021 arp_util.debug('line_class : ' ||
2022 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.LINE_CLASS(1));
2023
2024 arp_util.debug('line_level_action : ' ||
2025 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.LINE_LEVEL_ACTION(1));
2026
2027 arp_util.debug('trx_shipping_date : ' ||
2028 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.TRX_SHIPPING_DATE(1));
2029
2030 arp_util.debug('trx_line_type : ' ||
2031 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.TRX_LINE_TYPE(1));
2032
2033 arp_util.debug('trx_line_date : ' ||
2034 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.TRX_LINE_DATE(1));
2035
2036 arp_util.debug('line_amt_includes_tax_flag : ' ||
2037 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.LINE_AMT_INCLUDES_TAX_FLAG(1));
2038
2039 arp_util.debug('line_amt : ' ||
2040 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.LINE_AMT(1));
2041
2042 arp_util.debug('trx_line_quantity : ' ||
2043 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.TRX_LINE_QUANTITY(1));
2044
2045 arp_util.debug('unit_price : ' ||
2046 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.UNIT_PRICE(1));
2047
2048 arp_util.debug('exemption_control_flag : ' ||
2049 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.EXEMPTION_CONTROL_FLAG(1));
2050
2051 arp_util.debug('exempt_certificate_number : ' ||
2052 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.EXEMPT_CERTIFICATE_NUMBER(1));
2053
2054 arp_util.debug('exempt_reason : ' ||
2055 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.EXEMPT_REASON_CODE(1));
2056
2057 arp_util.debug('product_id : ' ||
2058 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.PRODUCT_ID(1));
2059
2060 arp_util.debug('product_org_id : ' ||
2061 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.PRODUCT_ORG_ID(1));
2062
2063 arp_util.debug('uom_code : ' ||
2064 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.UOM_CODE(1));
2065
2066 arp_util.debug('fob_point : ' ||
2067 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.FOB_POINT(1));
2068
2069 arp_util.debug('ship_to_party_id : ' ||
2070 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.SHIP_TO_PARTY_ID(1));
2071
2072 arp_util.debug('ship_from_party_id : ' ||
2073 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.SHIP_FROM_PARTY_ID(1));
2074
2075 arp_util.debug('bill_to_party_id : ' ||
2076 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.BILL_TO_PARTY_ID(1));
2077
2078 arp_util.debug('ship_to_party_site_id : ' ||
2079 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.SHIP_TO_PARTY_SITE_ID(1));
2080
2081 arp_util.debug('ship_to_location_id : ' ||
2082 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.SHIP_TO_LOCATION_ID(1));
2083
2084 arp_util.debug('bill_to_location_id : ' ||
2085 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.BILL_TO_LOCATION_ID(1));
2086
2087 arp_util.debug('account_ccid : ' ||
2088 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.ACCOUNT_CCID(1));
2089
2090 arp_util.debug('output_tax_classification_code : ' ||
2091 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.OUTPUT_TAX_CLASSIFICATION_CODE(1));
2092
2093 arp_util.debug('interface_entity_code : ' ||
2094 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.INTERFACE_ENTITY_CODE(1));
2095
2096 arp_util.debug('interface_line_id : ' ||
2097 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.INTERFACE_LINE_ID(1));
2098
2099 arp_util.debug('trx_line_number : ' ||
2100 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.TRX_LINE_NUMBER(1));
2101
2102 arp_util.debug('historical_flag : ' ||
2103 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.HISTORICAL_FLAG(1));
2104
2105 arp_util.debug('ctrl_hdr_tx_appl_flag : ' ||
2106 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.CTRL_HDR_TX_APPL_FLAG(1));
2107
2108 arp_util.debug('ship_third_pty_acct_site_id : ' ||
2109 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.SHIP_THIRD_PTY_ACCT_SITE_ID(1));
2110
2111 arp_util.debug('bill_third_pty_acct_site_id : ' ||
2112 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.BILL_THIRD_PTY_ACCT_SITE_ID(1));
2113
2114 arp_util.debug('ship_third_pty_acct_id : ' ||
2115 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.SHIP_THIRD_PTY_ACCT_ID(1));
2116
2117 arp_util.debug('bill_third_pty_acct_id : ' ||
2118 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.BILL_THIRD_PTY_ACCT_ID(1));
2119
2120 arp_util.debug('ship_to_cust_acct_site_use_id : ' ||
2121 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.SHIP_TO_CUST_ACCT_SITE_USE_ID(1));
2122
2123 arp_util.debug('bill_to_cust_acct_site_use_id : ' ||
2124 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.BILL_TO_CUST_ACCT_SITE_USE_ID(1));
2125
2126 END print_ebt_plsql_vars;
2127
2128 /*=============================================================================
2129 | PROCEDURE - line_det_factors
2130 |
2131 | DESCRIPTION
2132 | Public function that will call the INSERT_LINE_DET_FACTORS or
2133 | UPDATE_LINE_DET_FACTORS service
2134 | This API assumes the calling code controls the commit cycle.
2135 |
2136 | PARAMETERS
2137 |
2138 | MODIFICATION HISTORY
2139 | DATE Author Description of Changes
2140 | 17-JUN-2005 Debbie Sue Jancis Created
2141 | 16-AUG-2005 Jon Beckett Introduced INSERT_NO_LINE and
2142 | INSERT_NO_TAX modes for lines where
2143 | line amount or tax amount are zero.
2144 | 08-MAY-2006 M Raymond 5197390 - Added logic to support
2145 | calls for lines with memo line of
2146 | type 'TAX'
2147 |
2148 *===========================================================================*/
2149 PROCEDURE Line_det_factors ( p_customer_trx_line_id IN Number,
2150 p_customer_trx_id IN Number,
2151 p_mode IN VARCHAR2,
2152 p_tax_amount IN NUMBER DEFAULT NULL,
2153 p_called_from IN VARCHAR2 DEFAULT NULL) IS
2154
2155 l_event_class_code zx_trx_headers_gt.event_class_code%TYPE;
2156 l_event_type_code zx_trx_headers_gt.event_type_code%TYPE;
2157 l_transaction_line_rec zx_api_pub.transaction_line_rec_type;
2158
2159 l_return_status_service VARCHAR2(4000);
2160 l_msg_count NUMBER;
2161 l_msg_data VARCHAR2(4000);
2162 l_msg_data_out VARCHAR2(4000);
2163 l_mesg VARCHAR2(4000);
2164 l_success BOOLEAN;
2165 l_action VARCHAR2(12);
2166 l_line_level_action VARCHAR2(30);
2167 l_line_type VARCHAR2(20);
2168 l_tax_amount NUMBER;
2169
2170 BEGIN
2171
2172 arp_util.debug('ARP_ETAX_SERVICES_PKG.Line_det_factors()+');
2173
2174 IF (p_mode IN ('INSERT','INSERT_NO_TAX','INSERT_NO_TAX_EVER',
2175 'INSERT_NO_LINE')) THEN
2176 l_action := 'CREATE';
2177 ELSE
2178 l_action := 'UPDATE';
2179 END IF;
2180
2181 l_tax_amount := NULL;
2182
2183 IF (p_mode = 'INSERT_NO_TAX') THEN
2184 /* 5197390 - Changed to LINE_INFO_TAX_ONLY, was
2185 ALLOCATE_LINE_ONLY_ADJUSTMENT */
2186 l_line_level_action := 'LINE_INFO_TAX_ONLY';
2187 ELSIF (p_mode = 'INSERT_NO_TAX_EVER') THEN
2188 l_line_level_action := 'RECORD_WITH_NO_TAX';
2189 ELSIF (p_mode = 'INSERT_NO_LINE') THEN
2190 l_tax_amount := p_tax_amount;
2191 l_line_level_action := 'ALLOCATE_TAX_ONLY_ADJUSTMENT';
2192 ELSIF (p_mode = 'INSERT') THEN
2193 l_line_level_action := 'CREATE';
2194 ELSE
2195 l_line_level_action := 'UPDATE';
2196 END IF;
2197
2198 -- get event class and event type codes
2199 l_success := arp_etax_util.get_event_information(
2200 p_customer_trx_id => p_customer_trx_id,
2201 p_action => l_action,
2202 p_event_class_code => l_event_class_code,
2203 p_event_type_code => l_event_type_code);
2204
2205 arp_util.debug('customer trx id = ' || p_customer_trx_id);
2206 arp_util.debug('event class code = ' || l_event_class_code);
2207 arp_util.debug('event type code = ' || l_event_type_code);
2208 arp_util.debug('line level action = ' || l_line_level_action);
2209
2210 arp_util.debug('line level action = ' || l_line_level_action);
2211 IF (l_success) THEN
2212
2213 -- populate the transaction_line_rec for use in the tax service
2214 l_transaction_line_rec.internal_organization_id := NULL;
2215 l_transaction_line_rec.application_id := NULL;
2216 l_transaction_line_rec.entity_code := NULL;
2217 l_transaction_line_rec.event_class_code := NULL;
2218 l_transaction_line_rec.event_type_code := NULL;
2219 l_transaction_line_rec.trx_id := NULL;
2220 l_transaction_line_rec.trx_level_type := NULL;
2221 l_transaction_line_rec.trx_line_id := NULL;
2222
2223 /* initialize the plsql table */
2224 ZX_GLOBAL_STRUCTURES_PKG.INIT_TRX_LINE_DIST_TBL(1);
2225
2226 arp_util.debug('calling populate_ebt_plsql_tables ');
2227 populate_ebt_plsql_tables(
2228 p_customer_trx_id => p_customer_trx_id,
2229 p_customer_trx_line_id => p_customer_trx_line_id,
2230 p_event_type_code => l_event_type_code,
2231 p_event_class_code => l_event_class_code,
2232 p_line_level_action => l_line_level_action,
2233 p_tax_amount => l_tax_amount,
2234 p_called_from => p_called_from);
2235
2236 IF (p_mode IN ('INSERT','INSERT_NO_TAX','INSERT_NO_LINE')) THEN
2237 ZX_API_PUB.insert_line_det_factors (
2238 p_api_version => 1.0,
2239 p_init_msg_list => FND_API.G_TRUE,
2240 p_commit => FND_API.G_FALSE,
2241 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
2242 x_return_status => l_return_status_service,
2243 x_msg_count => l_msg_count,
2244 x_msg_data => l_msg_data,
2245 p_duplicate_line_rec => l_transaction_line_rec);
2246 else
2247 /* Added the initialization part before calling ZX update API --- Bug - 13097079 */
2248 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.TAX_INVOICE_DATE(1) := FND_API.G_MISS_DATE;
2249 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.TAX_INVOICE_NUMBER(1) := FND_API.G_MISS_CHAR;
2250 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.SOURCE_EVENT_CLASS_CODE(1):= FND_API.G_MISS_CHAR;
2251 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.INTERFACE_ENTITY_CODE(1) := FND_API.G_MISS_CHAR;
2252 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.INTERFACE_LINE_ID(1) := FND_API.G_MISS_NUM;
2253 ZX_API_PUB.update_line_det_factors (
2254 p_api_version => 1.0,
2255 p_init_msg_list => FND_API.G_TRUE,
2256 p_commit => FND_API.G_FALSE,
2257 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
2258 x_return_status => l_return_status_service,
2259 x_msg_count => l_msg_count,
2260 x_msg_data => l_msg_data );
2261 end if;
2262
2263 -- verify the return status from the tax api.
2264 IF (l_return_status_service <> 'S') THEN -- handle errors
2265 arp_util.debug('line_det_factors returned error');
2266 IF ( l_msg_count = 1 ) THEN
2267 -- then there is only 1 message raised by the API, and
2268 -- it has been sent out in the parameter x_msg_data.
2269 l_msg_data_out := l_msg_data;
2270 arp_util.debug('API failed with : ' || l_msg_data_out);
2271 l_mesg := l_msg_data_out;
2272
2273 ELSIF (l_msg_count > 1) THEN
2274 -- the messages are on the stack and there is more then
2275 -- 1 so call them in a loop
2276 loop
2277 l_mesg := FND_MSG_PUB.GET(FND_MSG_PUB.G_NEXT,
2278 FND_API.G_FALSE);
2279 if (l_mesg IS NULL) THEN
2280 EXIT;
2281 end if;
2282 arp_util.debug('API failed with : ' || l_mesg);
2283 end loop;
2284 END IF;
2285
2286 -- raise error
2287 /* 4919401 - Added generic message fetch */
2288 FND_MESSAGE.SET_NAME('FND', 'FND_GENERIC_MESSAGE');
2289 FND_MESSAGE.SET_TOKEN('MESSAGE', l_mesg);
2290 FND_MSG_PUB.ADD;
2291
2292 APP_EXCEPTION.RAISE_EXCEPTION;
2293 END IF;
2294 ELSE
2295 arp_util.debug('ERROR getting EVENT INFORMATION');
2296 END IF;
2297 arp_util.debug('ARP_ETAX_SERVICES_PKG.Line_det_factors()-');
2298
2299 END line_det_factors;
2300
2301 /*=============================================================================
2302 | PROCEDURE - Header_det_factors
2303 |
2304 | DESCRIPTION
2305 | Public function that will call the UPDATE_DET_FACTORS_HDR
2306 | This API assumes the calling code controls the commit cycle.
2307 |
2308 | PARAMETERS
2309 |
2310 | MODIFICATION HISTORY
2311 | DATE Author Description of Changes
2312 | 20-JUN-2005 Debbie Sue Jancis Created
2313 | 04-NOV-2005 M Raymond 4713671 - initialize header det
2314 | factor structure to not override
2315 | ship to (and other) columns
2316 | 29-NOV-2005 M Raymond 4763946 - init all header det factor
2317 | parameters to G_MISS values.
2318 | 09-JAN-2006 M Raymond 4928019 - handle void trx for etax
2319 *===========================================================================*/
2320 PROCEDURE Header_det_factors ( p_customer_trx_id IN Number,
2321 p_mode IN VARCHAR2,
2322 x_return_status OUT NOCOPY VARCHAR2,
2323 x_msg_count OUT NOCOPY NUMBER,
2324 x_msg_data OUT NOCOPY VARCHAR2 ) IS
2325
2326 l_hdr_det_factors_rec zx_api_pub.header_det_factors_rec_type;
2327 l_hdr_ship_to_cust_id RA_CUSTOMER_TRX.ship_to_customer_id%TYPE;
2328 l_hdr_ship_to_su_id RA_CUSTOMER_TRX.ship_to_site_use_id%TYPE;
2329 l_action VARCHAR2(12);
2330 l_success BOOLEAN;
2331 l_event_class_code zx_trx_headers_gt.event_class_code%TYPE;
2332 l_event_type_code zx_trx_headers_gt.event_type_code%TYPE;
2333 l_llst_exists NUMBER := 0; -- 4713671
2334 BEGIN
2335 arp_util.debug('arp_etax_services_pkg.Header_det_factors(+)');
2336
2337 /* Initializing return status ..*/
2338 x_return_status := FND_API.G_RET_STS_SUCCESS;
2339
2340 IF (p_mode = 'UPDATE') THEN
2341 l_action := 'UPDATE';
2342
2343 /* 4713671/4763946 initialize various columns in structure
2344 to prevent overlay of data in update det call */
2345 l_hdr_det_factors_rec.trx_date := FND_API.G_MISS_DATE;
2346 l_hdr_det_factors_rec.trx_doc_revision := FND_API.G_MISS_CHAR;
2347 l_hdr_det_factors_rec.ledger_id := FND_API.G_MISS_NUM;
2348 l_hdr_det_factors_rec.trx_currency_code := FND_API.G_MISS_CHAR;
2349 l_hdr_det_factors_rec.currency_conversion_date := FND_API.G_MISS_DATE;
2350 l_hdr_det_factors_rec.currency_conversion_rate := FND_API.G_MISS_NUM;
2351 l_hdr_det_factors_rec.currency_conversion_type := FND_API.G_MISS_CHAR;
2352 l_hdr_det_factors_rec.minimum_accountable_unit := FND_API.G_MISS_NUM;
2353 l_hdr_det_factors_rec.precision := FND_API.G_MISS_NUM;
2354 l_hdr_det_factors_rec.legal_entity_id := FND_API.G_MISS_NUM;
2355 l_hdr_det_factors_rec.rounding_ship_to_party_id := FND_API.G_MISS_NUM;
2356 l_hdr_det_factors_rec.rounding_ship_from_party_id := FND_API.G_MISS_NUM;
2357 l_hdr_det_factors_rec.rounding_bill_to_party_id := FND_API.G_MISS_NUM;
2358 l_hdr_det_factors_rec.rounding_bill_from_party_id := FND_API.G_MISS_NUM;
2359 l_hdr_det_factors_rec.rndg_ship_to_party_site_id := FND_API.G_MISS_NUM;
2360 l_hdr_det_factors_rec.rndg_ship_from_party_site_id := FND_API.G_MISS_NUM;
2361 l_hdr_det_factors_rec.rndg_bill_from_party_site_id := FND_API.G_MISS_NUM;
2362 l_hdr_det_factors_rec.rndg_bill_to_party_site_id := FND_API.G_MISS_NUM;
2363 l_hdr_det_factors_rec.bill_to_cust_acct_site_use_id := FND_API.G_MISS_NUM;
2364 l_hdr_det_factors_rec.ship_third_pty_acct_id := FND_API.G_MISS_NUM;
2365 l_hdr_det_factors_rec.bill_third_pty_acct_id := FND_API.G_MISS_NUM;
2366 l_hdr_det_factors_rec.ship_third_pty_acct_site_id := FND_API.G_MISS_NUM;
2367 l_hdr_det_factors_rec.bill_third_pty_acct_site_id := FND_API.G_MISS_NUM;
2368 l_hdr_det_factors_rec.ship_to_cust_acct_site_use_id := FND_API.G_MISS_NUM;
2369 l_hdr_det_factors_rec.provnl_tax_determination_date := FND_API.G_MISS_DATE;
2370 l_hdr_det_factors_rec.establishment_id := FND_API.G_MISS_NUM;
2371 l_hdr_det_factors_rec.trx_batch_id := FND_API.G_MISS_NUM;
2372 l_hdr_det_factors_rec.application_doc_status := FND_API.G_MISS_CHAR;
2373 l_hdr_det_factors_rec.receivables_trx_type_id := FND_API.G_MISS_NUM;
2374 l_hdr_det_factors_rec.related_doc_application_id := FND_API.G_MISS_NUM;
2375 l_hdr_det_factors_rec.related_doc_entity_code := FND_API.G_MISS_CHAR;
2376 l_hdr_det_factors_rec.related_doc_event_class_code := FND_API.G_MISS_CHAR;
2377 l_hdr_det_factors_rec.related_doc_trx_id := FND_API.G_MISS_NUM;
2378 l_hdr_det_factors_rec.related_doc_number := FND_API.G_MISS_CHAR;
2379 l_hdr_det_factors_rec.related_doc_date := FND_API.G_MISS_DATE;
2380 l_hdr_det_factors_rec.default_taxation_country := FND_API.G_MISS_CHAR;
2381 l_hdr_det_factors_rec.tax_reporting_flag := FND_API.G_MISS_CHAR;
2382 l_hdr_det_factors_rec.port_of_entry_code := FND_API.G_MISS_CHAR;
2383 l_hdr_det_factors_rec.ship_to_party_id := FND_API.G_MISS_NUM;
2384 l_hdr_det_factors_rec.ship_from_party_id := FND_API.G_MISS_NUM;
2385 l_hdr_det_factors_rec.poa_party_id := FND_API.G_MISS_NUM;
2386 l_hdr_det_factors_rec.poo_party_id := FND_API.G_MISS_NUM;
2387 l_hdr_det_factors_rec.bill_to_party_id := FND_API.G_MISS_NUM;
2388 l_hdr_det_factors_rec.bill_from_party_id := FND_API.G_MISS_NUM;
2389 l_hdr_det_factors_rec.ship_from_party_site_id := FND_API.G_MISS_NUM;
2390 l_hdr_det_factors_rec.ship_to_party_site_id := FND_API.G_MISS_NUM;
2391 l_hdr_det_factors_rec.poa_party_site_id := FND_API.G_MISS_NUM;
2392 l_hdr_det_factors_rec.poo_party_site_id := FND_API.G_MISS_NUM;
2393 l_hdr_det_factors_rec.bill_to_party_site_id := FND_API.G_MISS_NUM;
2394 l_hdr_det_factors_rec.bill_from_party_site_id := FND_API.G_MISS_NUM;
2395 l_hdr_det_factors_rec.ship_to_location_id := FND_API.G_MISS_NUM;
2396 l_hdr_det_factors_rec.ship_from_location_id := FND_API.G_MISS_NUM;
2397 l_hdr_det_factors_rec.poa_location_id := FND_API.G_MISS_NUM;
2398 l_hdr_det_factors_rec.poo_location_id := FND_API.G_MISS_NUM;
2399 l_hdr_det_factors_rec.bill_to_location_id := FND_API.G_MISS_NUM;
2400 l_hdr_det_factors_rec.bill_from_location_id := FND_API.G_MISS_NUM;
2401 l_hdr_det_factors_rec.document_sub_type := FND_API.G_MISS_CHAR;
2402 l_hdr_det_factors_rec.quote_flag := FND_API.G_MISS_CHAR;
2403 l_hdr_det_factors_rec.ctrl_total_hdr_tx_amt := FND_API.G_MISS_NUM;
2404 l_hdr_det_factors_rec.applied_to_trx_number := FND_API.G_MISS_CHAR;
2405 l_hdr_det_factors_rec.trx_number := FND_API.G_MISS_CHAR;
2406 l_hdr_det_factors_rec.trx_description := FND_API.G_MISS_CHAR;
2407 l_hdr_det_factors_rec.trx_communicated_date := FND_API.G_MISS_DATE;
2408 l_hdr_det_factors_rec.batch_source_id := FND_API.G_MISS_NUM;
2409 l_hdr_det_factors_rec.batch_source_name := FND_API.G_MISS_CHAR;
2410 l_hdr_det_factors_rec.doc_seq_id := FND_API.G_MISS_NUM;
2411 l_hdr_det_factors_rec.doc_seq_name := FND_API.G_MISS_CHAR;
2412 l_hdr_det_factors_rec.doc_seq_value := FND_API.G_MISS_CHAR;
2413 l_hdr_det_factors_rec.trx_due_date := FND_API.G_MISS_DATE;
2414 l_hdr_det_factors_rec.trx_type_description := FND_API.G_MISS_CHAR;
2415 l_hdr_det_factors_rec.supplier_tax_invoice_number := FND_API.G_MISS_CHAR;
2416 l_hdr_det_factors_rec.supplier_tax_invoice_date := FND_API.G_MISS_DATE;
2417 l_hdr_det_factors_rec.supplier_exchange_rate := FND_API.G_MISS_NUM;
2418 l_hdr_det_factors_rec.tax_invoice_date := FND_API.G_MISS_DATE;
2419 l_hdr_det_factors_rec.tax_invoice_number := FND_API.G_MISS_CHAR;
2420
2421 -- get event class and event type codes
2422 l_success := arp_etax_util.get_event_information(
2423 p_customer_trx_id => p_customer_trx_id,
2424 p_action => l_action,
2425 p_event_class_code => l_event_class_code,
2426 p_event_type_code => l_event_type_code);
2427
2428 arp_util.debug('customer trx id = ' || p_customer_trx_id);
2429 arp_util.debug('event class code = ' || l_event_class_code);
2430 arp_util.debug('event type code = ' || l_event_type_code);
2431
2432 IF (l_success) THEN
2433 -- need to select from the table as items have been posted to
2434 -- populate the header_det_factors_rec_type
2435
2436 SELECT
2437 TRX.org_id, -- internal_organization_id
2438 222, -- application_id
2439 'TRANSACTIONS', -- entity_code
2440 l_event_class_code, -- event_class_code
2441 l_event_type_code, -- event_type_code
2442 p_customer_trx_id, -- trx_id
2443 TRX.trx_date, -- trx_date
2444 AR.set_of_books_id, -- ledger_id
2445 TRX.invoice_currency_code, -- trx_currency_code
2446 TRX.exchange_date, -- currency_conversion_date
2447 TRX.exchange_rate, -- currency_conversion_rate
2448 TRX.exchange_rate_type, -- currency_conversion_type
2449 CURR.minimum_accountable_unit, -- minimum_accountable_unit
2450 CURR.precision, -- precision
2451 TRX.legal_entity_id, -- legal_entity_id
2452 BILL_CUST.party_id, -- rounding_bill_to_party_id
2453 BILL_AS.party_site_id, -- rndg_bill_to_party_site_id
2454 TRX.cust_trx_type_id, -- receivables_trx_type_id
2455 'Y', -- tax_reporting_flag
2456 BILL_CUST.party_id, -- bill_to_party_id
2457 BILL_AS.party_site_id, -- bill_to_party_site_id
2458 BILL_LOC.location_id, -- bill_to_location_id
2459 TRX.trx_number, -- trx_number
2460 substrb(TRX.comments,1,240), -- trx_description
2461 TRX.printing_original_date, -- trx_communicated_date
2462 TRX.batch_source_id, -- batch_source_id
2463 BS.NAME, -- batch_source_name
2464 TRX.doc_sequence_id, -- doc_seq_id
2465 -- bug 6806843
2466 --TYPES.name, -- doc_seq_name
2467 SEQ.name, -- doc_seq_name
2468 TRX.doc_sequence_value, -- doc_seq_value
2469 TRX.term_due_date, -- trx_due_date
2470 TYPES.description, -- trx_type_description
2471 TRX.ship_to_customer_id,
2472 TRX.ship_to_site_use_id,
2473 BILL_SU.site_use_id, --bill_to_cust_acct_site_use_id
2474 DECODE(TRX.status_trx,'VD','VD',NULL),
2475 TRX.bill_to_customer_id, --bill_third_pty_acct_id
2476 BILL_AS.cust_acct_site_id --bill_third_pty_acct_site_id
2477 INTO
2478 l_hdr_det_factors_rec.internal_organization_id,
2479 l_hdr_det_factors_rec.application_id,
2480 l_hdr_det_factors_rec.entity_code,
2481 l_hdr_det_factors_rec.event_class_code,
2482 l_hdr_det_factors_rec.event_type_code,
2483 l_hdr_det_factors_rec.trx_id,
2484 l_hdr_det_factors_rec.trx_date,
2485 l_hdr_det_factors_rec.ledger_id,
2486 l_hdr_det_factors_rec.trx_currency_code,
2487 l_hdr_det_factors_rec.currency_conversion_date,
2488 l_hdr_det_factors_rec.currency_conversion_rate,
2489 l_hdr_det_factors_rec.currency_conversion_type,
2490 l_hdr_det_factors_rec.minimum_accountable_unit,
2491 l_hdr_det_factors_rec.precision,
2492 l_hdr_det_factors_rec.legal_entity_id,
2493 l_hdr_det_factors_rec.rounding_bill_to_party_id,
2494 l_hdr_det_factors_rec.rndg_bill_to_party_site_id,
2495 l_hdr_det_factors_rec.receivables_trx_type_id,
2496 l_hdr_det_factors_rec.tax_reporting_flag,
2497 l_hdr_det_factors_rec.bill_to_party_id,
2498 l_hdr_det_factors_rec.bill_to_party_site_id,
2499 l_hdr_det_factors_rec.bill_to_location_id,
2500 l_hdr_det_factors_rec.trx_number,
2501 l_hdr_det_factors_rec.trx_description,
2502 l_hdr_det_factors_rec.trx_communicated_date,
2503 l_hdr_det_factors_rec.batch_source_id,
2504 l_hdr_det_factors_rec.batch_source_name,
2505 l_hdr_det_factors_rec.doc_seq_id,
2506 l_hdr_det_factors_rec.doc_seq_name,
2507 l_hdr_det_factors_rec.doc_seq_value,
2508 l_hdr_det_factors_rec.trx_due_date,
2509 l_hdr_det_factors_rec.trx_type_description,
2510 l_hdr_ship_to_cust_id,
2511 l_hdr_ship_to_su_id,
2512 l_hdr_det_factors_rec.bill_to_cust_acct_site_use_id,
2513 l_hdr_det_factors_rec.application_doc_status,
2514 l_hdr_det_factors_rec.bill_third_pty_acct_id,
2515 l_hdr_det_factors_rec.bill_third_pty_acct_site_id
2516 FROM
2517 RA_CUSTOMER_TRX TRX,
2518 FND_CURRENCIES CURR,
2519 FND_DOCUMENT_SEQUENCES SEQ,
2520 AR_SYSTEM_PARAMETERS AR,
2521 RA_BATCH_SOURCES BS,
2522 RA_CUST_TRX_TYPES TYPES,
2523 HZ_CUST_ACCOUNTS BILL_CUST,
2524 HZ_PARTIES BILL_PARTY,
2525 HZ_CUST_ACCT_SITES BILL_AS,
2526 HZ_CUST_SITE_USES BILL_SU,
2527 HZ_PARTY_SITES BILL_PS,
2528 HZ_LOCATIONS BILL_LOC
2529 WHERE
2530 TRX.customer_trx_id = p_customer_trx_id and
2531 TRX.invoice_currency_code = CURR.currency_code and
2532 TRX.org_id = AR.org_id and
2533 TRX.batch_source_id = BS.batch_source_id and
2534 TRX.cust_trx_type_id = TYPES.cust_trx_type_id and
2535 TRX.doc_sequence_id = SEQ.doc_sequence_id (+) and
2536 TRX.bill_to_customer_id = BILL_CUST.cust_account_id and
2537 BILL_CUST.party_id = BILL_PARTY.party_id and
2538 BILL_CUST.cust_account_id = BILL_AS.cust_account_id and
2539 BILL_AS.cust_acct_site_id = BILL_SU.cust_acct_site_id and
2540 BILL_SU.site_use_id = TRX.bill_to_site_use_id and
2541 BILL_AS.party_site_id = BILL_PS.party_site_id AND
2542 BILL_PS.location_id = BILL_LOC.location_id;
2543
2544 /* Detect line-level ship to info first, used to
2545 determine if the user is nulling the ship to
2546 out, or it is not changed */
2547 SELECT count(*)
2548 INTO l_llst_exists
2549 FROM ra_customer_trx_lines
2550 WHERE customer_trx_id = p_customer_trx_id
2551 AND line_type = 'LINE'
2552 AND ship_to_customer_id IS NOT NULL
2553 AND ship_to_site_use_id IS NOT NULL;
2554
2555 IF l_llst_exists > 0
2556 THEN
2557 /* Line level ship_to values exist, Ignore
2558 changes to header-level ship-to */
2559 NULL;
2560 ELSE
2561 IF (l_hdr_ship_to_cust_id IS NULL OR
2562 l_hdr_ship_to_su_id IS NULL)
2563 THEN
2564 /* Header ship_to is now null, clear
2565 what was there in LDF */
2566 l_hdr_det_factors_rec.ship_to_party_id := NULL;
2567 l_hdr_det_factors_rec.rounding_ship_to_party_id := NULL;
2568 l_hdr_det_factors_rec.ship_to_party_site_id := NULL;
2569 l_hdr_det_factors_rec.rndg_ship_to_party_site_id := NULL;
2570 l_hdr_det_factors_rec.ship_to_location_id := NULL;
2571 l_hdr_det_factors_rec.ship_to_cust_acct_site_use_id:= NULL;
2572
2573 ELSE
2574 /* Header ship_to is populated, set
2575 LDF accordingly */
2576 SELECT
2577 CUST_ACCT.party_id,
2578 CUST_ACCT.party_id,
2579 ACCT_SITE.party_site_id,
2580 ACCT_SITE.party_site_id,
2581 LOC.location_id,
2582 SITE_USES.site_use_id
2583 INTO
2584 l_hdr_det_factors_rec.ship_to_party_id,
2585 l_hdr_det_factors_rec.rounding_ship_to_party_id,
2586 l_hdr_det_factors_rec.ship_to_party_site_id,
2587 l_hdr_det_factors_rec.rndg_ship_to_party_site_id,
2588 l_hdr_det_factors_rec.ship_to_location_id,
2589 l_hdr_det_factors_rec.ship_to_cust_acct_site_use_id
2590 FROM
2591 hz_cust_accounts CUST_ACCT,
2592 hz_parties PARTY,
2593 hz_cust_acct_sites ACCT_SITE,
2594 hz_cust_site_uses SITE_USES,
2595 hz_party_sites PARTY_SITE,
2596 hz_locations LOC
2597 WHERE
2598 CUST_ACCT.cust_account_id = l_hdr_ship_to_cust_id AND
2599 CUST_ACCT.party_id = PARTY.party_id AND
2600 CUST_ACCT.cust_account_id = ACCT_SITE.cust_account_id AND
2601 ACCT_SITE.cust_acct_site_id = SITE_USES.cust_acct_site_id AND
2602 SITE_USES.site_use_id = l_hdr_ship_to_su_id AND
2603 ACCT_SITE.party_site_id = PARTY_SITE.party_site_id AND
2604 PARTY_SITE.location_id = LOC.location_id;
2605
2606 END IF; -- end header is null
2607 END IF; -- end llst exists
2608 ELSE
2609 arp_util.debug('ERROR getting EVENT INFORMATION');
2610 END IF;
2611
2612 -- need to call the tax api
2613 zx_api_pub.update_det_factors_hdr(
2614 p_api_version => 1.0,
2615 p_init_msg_list => FND_API.G_TRUE,
2616 p_commit => FND_API.G_FALSE,
2617 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
2618 x_return_status => x_return_status,
2619 x_msg_count => x_msg_count,
2620 x_msg_data => x_msg_data,
2621 p_hdr_det_factors_rec => l_hdr_det_factors_rec
2622 );
2623
2624 END IF;
2625 arp_util.debug('arp_etax_services_pkg.Header_det_factors(-)');
2626
2627 END Header_det_factors;
2628
2629 /*=============================================================================
2630 | FUNCTION - Calculate_tax()
2631 |
2632 | DESCRIPTION
2633 | Public function that will call the calculate_tax service for
2634 | calculation and recalculation.
2635 | This API assumes the calling code controls the commit cycle.
2636 | This function returns TRUE if the call to the service is successful.
2637 | Otherwise, FALSE.
2638 |
2639 | PARAMETERS
2640 |
2641 | MODIFICATION HISTORY
2642 | DATE Author Description of Changes
2643 | 14-Apr-2005 Debbie Sue Jancis Created
2644 | 21-JUL-2006 M Raymond 5211848 - added call to arp_rounding
2645 | 04-OCT-2006 M Raymond 5457495 - cache and honor tax
2646 | account overrides
2647 *===========================================================================*/
2648 PROCEDURE Calculate_tax (p_customer_trx_id IN NUMBER,
2649 p_action IN VARCHAR2,
2650 x_return_status OUT NOCOPY VARCHAR2,
2651 x_msg_count OUT NOCOPY NUMBER,
2652 x_msg_data OUT NOCOPY VARCHAR2 ) IS
2653
2654 l_transaction_rec zx_api_pub.transaction_rec_type;
2655
2656 l_return_status_service VARCHAR2(4000);
2657 l_doc_level_recalc_flag VARCHAR2(1);
2658
2659 l_event_class_code VARCHAR2(80);
2660 l_event_type_code VARCHAR2(80);
2661 l_success BOOLEAN;
2662 l_ccid NUMBER;
2663 l_concat_segments VARCHAR2(2000);
2664 l_num_failed_dist_rows NUMBER;
2665 l_rows NUMBER;
2666 --Added for Bug5125882
2667 l_is_reg_cm NUMBER;
2668
2669 --Added for bug 5211848 (call to arp_rounding)
2670 pg_base_precision fnd_currencies.precision%type;
2671 pg_base_min_acc_unit fnd_currencies.minimum_accountable_unit%type;
2672 pg_trx_header_level_rounding ar_system_parameters.trx_header_level_rounding%type;
2673 l_error_message VARCHAR2(128);
2674 l_dist_count NUMBER;
2675 l_rules_check_flag VARCHAR2(1);
2676 l_account_set_flag VARCHAR2(1);
2677 l_rtn NUMBER;
2678 l_xla_ev_rec ARP_XLA_EVENTS.XLA_EVENTS_TYPE;
2679 BEGIN
2680 arp_util.debug('ARP_ETAX_SERVICES_PKG.Calculate_tax(+)');
2681 arp_util.debug('p_action = ' || p_action);
2682
2683 /* initializing precision, mau, hdr level rdn */
2684 pg_base_precision := arp_trx_global.system_info.base_precision;
2685 pg_base_min_acc_unit := arp_trx_global.system_info.base_min_acc_unit;
2686 pg_trx_header_level_rounding :=
2687 arp_global.sysparam.trx_header_level_rounding;
2688
2689 /* Initializing return status ..*/
2690 x_return_status := FND_API.G_RET_STS_SUCCESS;
2691
2692 -- get event class code
2693 l_success := arp_etax_util.get_event_information(
2694 p_customer_trx_id => p_customer_trx_id,
2695 p_action => p_action,
2696 p_event_class_code => l_event_class_code,
2697 p_event_type_code => l_event_type_code);
2698
2699 arp_util.debug('customer trx id = ' || p_customer_trx_id);
2700 arp_util.debug('action = ' || p_action);
2701 arp_util.debug('event class code = ' || l_event_class_code);
2702 arp_util.debug('event type code = ' || l_event_type_code);
2703
2704 IF (l_success) THEN
2705 /* populate transaction rec type */
2706 l_transaction_rec.internal_organization_id := arp_global.sysparam.org_id; l_transaction_rec.application_id := 222;
2707 l_transaction_rec.entity_code := 'TRANSACTIONS';
2708 l_transaction_rec.event_class_code := l_event_class_code;
2709 l_transaction_rec.event_type_code := l_event_type_code;
2710 l_transaction_rec.trx_id := p_customer_trx_id;
2711
2712 /* initialize the pl/sql table. We do not need to populate this
2713 table if we are calling calculate tax at commit time. */
2714 ZX_GLOBAL_STRUCTURES_PKG.INIT_TRX_LINE_DIST_TBL(1);
2715
2716 /* 5457495 - cache tax accounting for use later */
2717 record_tax_accounts(p_customer_trx_id);
2718
2719 /* 5152340 - Remove AR tax lines prior to calculate call */
2720 arp_etax_util.delete_tax_lines_from_ar(p_customer_trx_id);
2721
2722 /* call Tax */
2723 arp_util.debug('calling ZX api to calculate tax');
2724
2725 zx_api_pub.calculate_tax(
2726 p_api_version => 1.0,
2727 p_init_msg_list => FND_API.G_TRUE,
2728 p_commit => FND_API.G_FALSE,
2729 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
2730 p_transaction_rec => l_transaction_rec,
2731 p_quote_flag => 'N',
2732 p_data_transfer_mode => 'WIN',
2733 x_return_status => x_return_status,
2734 x_msg_count => x_msg_count,
2735 x_msg_data => x_msg_data,
2736 x_doc_level_recalc_flag => l_doc_level_recalc_flag );
2737
2738 arp_util.debug('return status service = ' || x_return_status);
2739
2740 IF (x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
2741 -- insert Tax records into ra_customer_trx_lines based upon
2742 -- customer trx id
2743 arp_util.debug('calling build_ar_tax_lines ...');
2744
2745 arp_etax_util.build_ar_tax_lines(
2746 p_customer_trx_id => p_customer_trx_id,
2747 p_rows_inserted => l_rows);
2748
2749 IF l_rows > 0
2750 THEN
2751
2752 /* 5125882 - This routine is called upon completion of
2753 credit memos (happens way after arp_credit_memo_module
2754 call). So we need to check the use_inv_acct profile
2755 and do an insert (like CMM code) if the profile is yes
2756 and otherwise call autoaccounting */
2757
2758 /*GGADHAMS 5125882 Added this to check whether the CM is Regular
2759 or On Account. If it is a On Account we call autoaccounting
2760 else if use_inv_acct profile set to Y we copy from the Invoice
2761 otherwise we use auotaccounting*/
2762
2763 select previous_customer_trx_id,
2764 DECODE(invoicing_rule_id, NULL, 'N', 'Y')
2765 into l_is_reg_cm,
2766 l_rules_check_flag
2767 from ra_customer_trx
2768 where customer_trx_id = p_customer_trx_id;
2769
2770
2771 IF l_event_class_code = 'CREDIT_MEMO' and
2772 use_invoice_accounting and
2773 l_is_reg_cm IS NOT NULL
2774 THEN
2775 copy_inv_tax_dists(p_customer_trx_id);
2776 ELSE
2777
2778 BEGIN
2779 -- need to call autoaccounting for these lines:
2780
2781 ARP_AUTO_ACCOUNTING.do_autoaccounting( 'I', -- p_mode
2782 'TAX', --p_account_class
2783 p_customer_trx_id, -- p_customer_trx_id
2784 NULL, -- p_customer_trx_line_id
2785 NULL, -- p_cust_trx_line_salesrep_id
2786 null, --p_request_id
2787 NULL, --p_gl_date
2788 NULL, --p_original_gl_date
2789 null, --p_total_trx_amount
2790 null, --p_passed_ccid,
2791 null, --p_force_account_set_no
2792 null, --p_cust_trx_type_id
2793 null, --p_primary_salesrep_id,
2794 null, --p_inventory_item_id,
2795 null, --p_memo_line_id,
2796 l_ccid, --p_ccid
2797 l_concat_segments, --p_concat_segments
2798 l_num_failed_dist_rows ); --p_failure_count
2799
2800 EXCEPTION
2801 WHEN arp_auto_accounting.no_ccid THEN
2802 fnd_message.set_name('AR', 'ARP_AUTO_ACCOUNTING.NO_CCID');
2803 WHEN NO_DATA_FOUND THEN
2804 null;
2805 WHEN OTHERS THEN
2806 RAISE;
2807 END;
2808
2809 /* 7131147 - recreate the tax accounting dists */
2810 IF l_rules_check_flag = 'Y'
2811 THEN
2812 SELECT account_set_flag
2813 INTO l_account_set_flag
2814 FROM ra_cust_trx_line_gl_dist
2815 WHERE customer_trx_id = p_customer_trx_id
2816 AND account_class = 'REC'
2817 AND latest_rec_flag = 'Y';
2818
2819 IF l_account_set_flag = 'N'
2820 THEN
2821 /* This executes if transaction has rules,
2822 and the lines have already been generated */
2823 l_rtn := arp_auto_rule.create_other_tax(
2824 p_trx_id => p_customer_trx_id,
2825 p_base_precision => pg_base_precision ,
2826 p_bmau => pg_base_min_acc_unit,
2827 p_ignore_rule_flag => 'Y');
2828
2829 /* Call SLA to stamp the event ids on new rows */
2830 l_xla_ev_rec.xla_from_doc_id := p_customer_trx_id;
2831 l_xla_ev_rec.xla_to_doc_id := p_customer_trx_id;
2832 l_xla_ev_rec.xla_doc_table := 'CT';
2833 l_xla_ev_rec.xla_mode := 'O';
2834 l_xla_ev_rec.xla_call := 'D';
2835 arp_xla_events.create_events(l_xla_ev_rec);
2836 END IF;
2837 END IF;
2838 END IF;
2839
2840 /* 5457495 - Replace resulting code_combination_ids
2841 with previous ones if any existed. Note that
2842 we have made a concious decision to always use
2843 previously existing accounts when possible
2844 and we do so if the tax values and salesrep
2845 match. */
2846 replace_tax_accounts;
2847
2848 /* 5211848 - Once we insert accounting distributions,
2849 we must call arp_rounding to fix the amounts on
2850 the REC dist to reflect the new tax */
2851 IF arp_rounding.correct_dist_rounding_errors(
2852 NULL,
2853 p_customer_trx_id ,
2854 NULL,
2855 l_dist_count,
2856 l_error_message ,
2857 pg_base_precision ,
2858 pg_base_min_acc_unit ,
2859 'ALL' ,
2860 l_rules_check_flag,
2861 'N' ,
2862 pg_trx_header_level_rounding ,
2863 'N',
2864 'N') = 0 -- FALSE
2865 THEN
2866 arp_util.debug('EXCEPTION: arp_etax_services_pkg.calculate_tax()');
2867 arp_util.debug(l_error_message);
2868 fnd_message.set_name('AR', 'AR_ROUNDING_ERROR');
2869 fnd_message.set_token('ROUTINE','ARP_ETAX_SERVICES_PKG.CALCULATE_TAX');
2870 APP_EXCEPTION.raise_exception;
2871 END IF;
2872 /* end 5211848 */
2873 END IF; -- l_rows
2874 ELSE -- Bug7300346
2875 x_return_status := FND_API.G_RET_STS_ERROR; -- Bug7300346
2876 END IF;
2877 ELSE
2878 arp_standard.debug('could not get an event class code');
2879 END IF;
2880
2881 arp_util.debug('ARP_ETAX_SERVICES_PKG.Calculate_tax(-)');
2882 END Calculate_tax;
2883
2884 /*=============================================================================
2885 | FUNCTION - Get_Tax_Action()
2886 |
2887 | DESCRIPTION
2888 | This function will be called at commit time before the table handers
2889 | to determine if data exists for this transaction before current
2890 | actions. IF there is no data in the ra_customer_Trx_lines table
2891 | then by default the tax action is 'CREATE' else it is 'UPDATE'
2892 |
2893 | PARAMETERS
2894 |
2895 | MODIFICATION HISTORY
2896 | DATE Author Description of Changes
2897 | 14-Apr-2005 Debbie Sue Jancis Created
2898 |
2899 *===========================================================================*/
2900
2901 FUNCTION Get_Tax_Action (p_customer_trx_id IN NUMBER) RETURN VARCHAR2 IS
2902 l_count NUMBER;
2903 l_action VARCHAR2(12);
2904
2905 BEGIN
2906
2907 arp_util.debug('ARP_ETAX_SERVICES_PKG.Get_Tax_Action(+)');
2908
2909 select count(customer_trx_id)
2910 INTO l_count
2911 FROM ra_customer_trx_lines
2912 where customer_Trx_id = p_customer_trx_id and
2913 line_type = 'LINE';
2914
2915 IF (l_count = 0 ) then
2916 l_action := 'CREATE';
2917 ELSE
2918 l_action := 'UPDATE';
2919 END IF;
2920
2921 arp_util.debug('ARP_ETAX_SERVICES_PKG.Get_Tax_Action(-)');
2922
2923 return l_action;
2924
2925 END Get_Tax_Action;
2926
2927 /*=============================================================================
2928 | PROCEDURE- Override_tax_lines ()
2929 |
2930 | DESCRIPTION
2931 | This procedure will be called if there were changes in the
2932 | Detail TAX Lines window.
2933 |
2934 | PARAMETERS
2935 |
2936 | MODIFICATION HISTORY
2937 | DATE Author Description of Changes
2938 | 23-Jun-2005 Debbie Sue Jancis Created
2939 |
2940 *===========================================================================*/
2941 PROCEDURE Override_Tax_Lines (p_customer_trx_id IN NUMBER,
2942 p_action IN VARCHAR2,
2943 x_return_status OUT NOCOPY VARCHAR2,
2944 x_msg_count OUT NOCOPY NUMBER,
2945 x_msg_data OUT NOCOPY VARCHAR2,
2946 p_event_id IN NUMBER,
2947 p_override_status IN VARCHAR2) IS
2948
2949 l_transaction_rec zx_api_pub.transaction_rec_type;
2950 l_return_status_service VARCHAR2(4000);
2951
2952 l_event_class_code VARCHAR2(80);
2953 l_event_type_code VARCHAR2(80);
2954 l_success BOOLEAN;
2955 l_ccid NUMBER;
2956 l_concat_segments VARCHAR2(2000);
2957 l_msg_count NUMBER;
2958 l_num_failed_dist_rows NUMBER;
2959 l_msg_data VARCHAR2(4000);
2960 l_rows NUMBER;
2961 l_is_reg_cm NUMBER;
2962 --Added for Bug 8220233 (call to arp_rounding)
2963 l_dist_count NUMBER;
2964 l_error_message VARCHAR2(128);
2965 pg_base_precision fnd_currencies.precision%type;
2966 pg_base_min_acc_unit fnd_currencies.minimum_accountable_unit%type;
2967 pg_trx_header_level_rounding ar_system_parameters.trx_header_level_rounding%type;
2968
2969 BEGIN
2970 arp_util.debug('ARP_ETAX_SERVICES_PKG.Override_Tax_Lines(+)');
2971
2972 /* Bug 8220233: Initializing precision, mau, hdr level rounding */
2973 pg_base_precision := arp_trx_global.system_info.base_precision;
2974 pg_base_min_acc_unit := arp_trx_global.system_info.base_min_acc_unit;
2975 pg_trx_header_level_rounding := arp_global.sysparam.trx_header_level_rounding;
2976
2977 /* Initializing return status ..*/
2978 x_return_status := FND_API.G_RET_STS_SUCCESS;
2979
2980 -- get event information (OVERRIDE_TAX)
2981 l_success := arp_etax_util.get_event_information(
2982 p_customer_trx_id => p_customer_trx_id,
2983 p_action => p_action,
2984 p_event_class_code => l_event_class_code,
2985 p_event_type_code => l_event_type_code);
2986
2987 arp_util.debug('customer trx id = ' || p_customer_trx_id);
2988 arp_util.debug('action = ' || p_action);
2989 arp_util.debug('event class code = ' || l_event_class_code);
2990 arp_util.debug('event type code = ' || l_event_type_code);
2991
2992 IF (l_success) THEN
2993 /* populate transaction rec type */
2994 l_transaction_rec.internal_organization_id := arp_global.sysparam.org_id;
2995 l_transaction_rec.application_id := 222;
2996 l_transaction_rec.entity_code := 'TRANSACTIONS';
2997 l_transaction_rec.event_class_code := l_event_class_code;
2998 l_transaction_rec.event_type_code := l_event_type_code;
2999 l_transaction_rec.trx_id := p_customer_trx_id;
3000
3001 /*Bug 8402096 - Record any manual override of CCID by user*/
3002 record_tax_accounts(p_customer_trx_id);
3003
3004 /* 5152340 - Remove tax lines from AR before call */
3005 arp_etax_util.delete_tax_lines_from_ar(p_customer_trx_id);
3006
3007 -- CAll override_tax service
3008 zx_api_pub.override_tax(
3009 p_api_version => 1.0,
3010 p_init_msg_list => FND_API.G_TRUE,
3011 p_commit => FND_API.G_FALSE,
3012 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
3013 p_override_level => p_override_status,
3014 p_transaction_rec => l_transaction_rec,
3015 p_event_id => p_event_id,
3016 x_return_status => x_return_status,
3017 x_msg_count => x_msg_count,
3018 x_msg_data => x_msg_data);
3019
3020 -- update AR with return from tax
3021 IF (x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
3022 -- insert Tax records into ra_customer_trx_lines based upon
3023 -- customer trx id
3024 arp_util.debug('calling build_ar_tax_lines ...');
3025
3026 arp_etax_util.build_ar_tax_lines(
3027 p_customer_trx_id => p_customer_trx_id,
3028 p_rows_inserted => l_rows);
3029
3030 /* 4694486 - Prevent call to autoaccounting if
3031 no rows were inserted */
3032 IF l_rows > 0
3033 THEN
3034
3035 /*Bug 8402096 - Calling copy_inv_tax_dists() if use_invoice_accounting is yes*/
3036 select previous_customer_trx_id
3037 into l_is_reg_cm
3038 from ra_customer_trx
3039 where customer_trx_id = p_customer_trx_id;
3040
3041
3042 IF l_event_class_code = 'CREDIT_MEMO' and
3043 use_invoice_accounting and
3044 l_is_reg_cm IS NOT NULL
3045 THEN
3046 copy_inv_tax_dists(p_customer_trx_id);
3047
3048 ELSE
3049
3050 BEGIN
3051 -- need to call autoaccounting for these lines:
3052
3053 ARP_AUTO_ACCOUNTING.do_autoaccounting( 'I', -- p_mode
3054 'TAX', --p_account_class
3055 p_customer_trx_id, -- p_customer_trx_id
3056 NULL, -- p_customer_trx_line_id
3057 NULL, -- p_cust_trx_line_salesrep_id
3058 null, --p_request_id
3059 NULL, --p_gl_date
3060 NULL, --p_original_gl_date
3061 null, --p_total_trx_amount
3062 null, --p_passed_ccid,
3063 null, --p_force_account_set_no
3064 null, --p_cust_trx_type_id
3065 null, --p_primary_salesrep_id,
3066 null, --p_inventory_item_id,
3067 null, --p_memo_line_id,
3068 l_ccid, --p_ccid
3069 l_concat_segments, --p_concat_segments
3070 l_num_failed_dist_rows ); --p_failure_count
3071
3072 EXCEPTION
3073 WHEN arp_auto_accounting.no_ccid THEN
3074 fnd_message.set_name('AR', 'ARP_AUTO_ACCOUNTING.NO_CCID');
3075 WHEN NO_DATA_FOUND THEN
3076 null;
3077 WHEN OTHERS THEN
3078 RAISE;
3079 END;
3080 END IF;
3081
3082 /*Bug 8402096 - Replace the manual override of CCID by user*/
3083 replace_tax_accounts;
3084
3085 /* Bug 8220233 - Once we insert accounting distributions,
3086 we must call arp_rounding to fix the amounts on
3087 the REC dist to reflect the new tax */
3088
3089 IF arp_rounding.correct_dist_rounding_errors(
3090 NULL,
3091 p_customer_trx_id ,
3092 NULL,
3093 l_dist_count,
3094 l_error_message ,
3095 pg_base_precision ,
3096 pg_base_min_acc_unit ,
3097 'ALL' ,
3098 NULL,
3099 'N' ,
3100 pg_trx_header_level_rounding ,
3101 'N',
3102 'N') = 0 -- FALSE
3103 THEN
3104 arp_util.debug('EXCEPTION: arp_etax_services_pkg.Override_Tax_Lines()');
3105 arp_util.debug(l_error_message);
3106 fnd_message.set_name('AR', 'AR_ROUNDING_ERROR');
3107 fnd_message.set_token('ROUTINE','ARP_ETAX_SERVICES_PKG.OVERRIDE_TAX_LINES');
3108 APP_EXCEPTION.raise_exception;
3109 END IF;
3110
3111 /* End Bug 8220233 */
3112
3113 END IF; -- l_rows
3114 END IF;
3115
3116 END IF; -- if event codes were derived.
3117 arp_util.debug('ARP_ETAX_SERVICES_PKG.Override_Tax_Lines(-)');
3118 END Override_Tax_Lines;
3119
3120 /*=============================================================================
3121 | FUNCTION - is_trx_completed()
3122 |
3123 | DESCRIPTION
3124 | This function will be called by the following etax program units:
3125 | o IS_CANCEL_TAX_LINE_ALLOWED
3126 | o IS_MANUAL_DTL_TX_LINE_ALLOWED
3127 | o IS_TAX_LINE_DELETE_ALLOWED
3128 | o IS_TRX_LINE_FROZEN
3129 |
3130 | In general, these actions are not allowed for transactions that are
3131 | in a complete state.
3132 |
3133 | PARAMETERS
3134 | p_trx_id NUMBER (customer_trx_id of target transaction)
3135 |
3136 | MODIFICATION HISTORY
3137 | DATE Author Description of Changes
3138 | 03-MAR-2005 M Raymond Created
3139 |
3140 *===========================================================================*/
3141
3142 FUNCTION is_tax_update_allowed (p_customer_trx_id IN NUMBER) RETURN BOOLEAN IS
3143 l_update boolean;
3144 l_complete_flag varchar2(1);
3145 BEGIN
3146
3147 arp_util.debug('ARP_ETAX_SERVICES_PKG.is_tax_update_allowed()+');
3148
3149 SELECT complete_flag
3150 INTO l_complete_flag
3151 FROM ra_customer_trx
3152 WHERE customer_trx_id = p_customer_trx_id;
3153
3154 IF (l_complete_flag = 'Y' )
3155 THEN
3156 /* trx is complete, prevent updates */
3157 l_update := FALSE;
3158 arp_util.debug(' updates prevented by complete_flag');
3159 ELSE
3160 /* trx is incomplete, changes are allowed */
3161 l_update := TRUE;
3162 END IF;
3163
3164 arp_util.debug('ARP_ETAX_SERVICES_PKG.is_tax_update_allowed()-');
3165
3166 return l_update;
3167
3168 END is_tax_update_allowed;
3169
3170 /*=============================================================================
3171 | PROCEDURE - validate_for_tax
3172 |
3173 | DESCRIPTION
3174 | This routine calls etax API validate_document_for_tax to insure
3175 | that the tax, rate, status, juris, and regime are still valid
3176 | at the time of completion.
3177 |
3178 |
3179 | NOTE: This was intended specifically for calls from
3180 | arp_trx_complete_chk package for forms issues.
3181 | PARAMETERS
3182 | p_customer_trx_id NUMBER (customer_trx_id of target transaction)
3183 | p_error_mode VARCHAR IN (passed from do_completion_chk)
3184 | p_valid_for_tax VARCHAR OUT (Y or N)
3185 | p_number_of_errors NUMBER OUT (count of returned errors from etax)
3186 |
3187 |
3188 | MODIFICATION HISTORY
3189 | DATE Author Description of Changes
3190 | 11-JUL-2006 M Raymond Created
3191 |
3192 *===========================================================================*/
3193
3194 PROCEDURE validate_for_tax (p_customer_trx_id IN NUMBER,
3195 p_error_mode IN VARCHAR2,
3196 p_valid_for_tax OUT NOCOPY VARCHAR2,
3197 p_number_of_errors OUT NOCOPY NUMBER) IS
3198
3199 l_return_status VARCHAR2(50) := FND_API.G_RET_STS_SUCCESS;
3200 l_msg_count NUMBER;
3201 l_msg_data VARCHAR2(2000);
3202 l_trx_rec ZX_API_PUB.transaction_rec_type;
3203 l_validation_status VARCHAR2(1);
3204 l_hold_codes_tbl ZX_API_PUB.hold_codes_tbl_type;
3205 l_error_count NUMBER;
3206 l_trx_number RA_CUSTOMER_TRX.trx_number%type;
3207 l_msg VARCHAR2(2000);
3208 l_ttype ra_cust_trx_types_all.type%type;
3209 l_line_count NUMBER;
3210
3211 CURSOR c_errors IS
3212 select trx_id, trx_line_id, message_name, message_text
3213 from zx_validation_errors_gt
3214 where application_id = l_trx_rec.application_id
3215 and entity_code = l_trx_rec.entity_code
3216 and event_class_code = l_trx_rec.event_class_code
3217 and trx_id = l_trx_rec.trx_id;
3218
3219 BEGIN
3220 IF PG_DEBUG in ('Y', 'C')
3221 THEN
3222 arp_debug.debug('arp_etax_services_pkg.validate_for_tax()+');
3223 END IF;
3224
3225 /* Set l_trx_rec values before call to API */
3226 select t.customer_trx_id,
3227 222,
3228 t.org_id,
3229 'TRANSACTIONS',
3230 DECODE(tt.type,
3231 'INV', 'INVOICE',
3232 'DM', 'DEBIT_MEMO',
3233 'CM', 'CREDIT_MEMO'),
3234 tt.type || '_COMPLETE',
3235 t.trx_number,
3236 tt.type,
3237 SUM(decode(ctl.line_type, 'LINE', 1, 0))
3238 into
3239 l_trx_rec.trx_id,
3240 l_trx_rec.application_id,
3241 l_trx_rec.internal_organization_id,
3242 l_trx_rec.entity_code,
3243 l_trx_rec.event_class_code,
3244 l_trx_rec.event_type_code,
3245 l_trx_number,
3246 l_ttype, -- 7668830
3247 l_line_count
3248 from ra_customer_trx t,
3249 ra_cust_trx_types tt,
3250 ra_customer_trx_lines ctl
3251 where t.customer_trx_id = p_customer_trx_id
3252 and t.cust_trx_type_id = tt.cust_trx_type_id
3253 and t.org_id = tt.org_id
3254 and t.customer_trx_id = ctl.customer_trx_id
3255 group by
3256 t.customer_trx_id,
3257 222,
3258 t.org_id,
3259 'TRANSACTIONS',
3260 DECODE(tt.type,
3261 'INV', 'INVOICE',
3262 'DM', 'DEBIT_MEMO',
3263 'CM', 'CREDIT_MEMO'),
3264 tt.type || '_COMPLETE',
3265 t.trx_number,
3266 tt.type,
3267 t.customer_trx_id;
3268
3269 IF l_ttype NOT IN ('DEP','GUAR') AND l_line_count > 0
3270 THEN
3271 zx_api_pub.validate_document_for_tax(
3272 p_api_version => 1.0,
3273 p_init_msg_list => FND_API.G_TRUE,
3274 p_commit => FND_API.G_FALSE,
3275 p_validation_level => NULL,
3276 x_return_status => l_return_status,
3277 x_msg_count => l_msg_count,
3278 x_msg_data => l_msg_data,
3279 p_transaction_rec => l_trx_rec,
3280 x_validation_status=> l_validation_status,
3281 x_hold_codes_tbl => l_hold_codes_tbl);
3282 END IF;
3283
3284 IF l_return_status <> FND_API.G_RET_STS_SUCCESS
3285 THEN
3286 /* Retrieve and log errors */
3287 IF l_msg_count = 1
3288 THEN
3289 arp_debug.debug(l_msg_data);
3290 arp_trx_validate.add_to_error_list( -- Added for Bug 7260572
3291 p_error_mode,
3292 l_error_count,
3293 p_customer_trx_id,
3294 l_trx_number,
3295 NULL, -- line_number
3296 NULL, -- other_line_number
3297 'GENERIC_MESSAGE',
3298 NULL, -- p_error_location,
3299 'GENERIC_TEXT', -- token name 1
3300 l_msg_data, -- token 1
3301 NULL, -- token name 2
3302 NULL -- token 2
3303 );
3304 ELSIF l_msg_count > 1
3305 THEN
3306 LOOP
3307 l_msg := FND_MSG_PUB.Get(FND_MSG_PUB.G_NEXT,
3308 FND_API.G_FALSE);
3309 IF l_msg IS NULL
3310 THEN
3311 EXIT;
3312 ELSE
3313 arp_debug.debug(l_msg);
3314 arp_trx_validate.add_to_error_list( -- Added for Bug 7260572
3315 p_error_mode,
3316 l_error_count,
3317 p_customer_trx_id,
3318 l_trx_number,
3319 NULL, -- line_number
3320 NULL, -- other_line_number
3321 'GENERIC_MESSAGE',
3322 NULL, -- p_error_location,
3323 'GENERIC_TEXT', -- token name 1
3324 l_msg, -- token 1
3325 NULL, -- token name 2
3326 NULL -- token 2
3327 );
3328 END IF;
3329 END LOOP;
3330 END IF;
3331
3332 ELSE
3333 /* Successful return, copy parameters and distribute messages */
3334 IF l_validation_status = 'Y'
3335 THEN
3336 /* Do nothing, there was no problems with the validation */
3337 IF PG_DEBUG in ('Y','C')
3338 THEN
3339 arp_debug.debug(' transaction is valid');
3340 END IF;
3341
3342 l_error_count := 0;
3343 ELSE
3344 /* Transaction has failed validation, indicate as
3345 much back to arp_trx_completion_chk so completion
3346 is not allowed */
3347 IF PG_DEBUG in ('Y','C')
3348 THEN
3349 arp_debug.debug(' transaction is invalid');
3350 END IF;
3351
3352 FOR errors IN c_errors LOOP
3353
3354 arp_debug.debug(errors.trx_id || '-' || errors.message_text);
3355
3356 arp_trx_validate.add_to_error_list(
3357 p_error_mode,
3358 l_error_count,
3359 errors.trx_id,
3360 l_trx_number,
3361 NULL, -- line_number
3362 NULL, -- other_line_number
3363 'GENERIC_MESSAGE',
3364 NULL, -- p_error_location,
3365 'GENERIC_TEXT', -- token name 1
3366 errors.message_text, -- token 1
3367 NULL, -- token name 2
3368 NULL -- token 2
3369 );
3370
3371 END LOOP;
3372
3373 END IF;
3374
3375 p_number_of_errors := l_error_count;
3376 p_valid_for_tax := l_validation_status;
3377
3378 END IF;
3379
3380 IF PG_DEBUG in ('Y', 'C')
3381 THEN
3382 arp_debug.debug(' validation_status = ' || l_validation_status);
3383 arp_debug.debug('arp_etax_services_pkg.validate_for_tax()-');
3384 END IF;
3385
3386 END validate_for_tax;
3387
3388
3389 /*=============================================================================
3390 | PROCEDURE - update_exchange_info
3391 |
3392 | DESCRIPTION
3393 | This routine calls etax API ZX_API_PUB.update_exchange_rate to update
3394 | the Exchange Rate, Exchange Date and Exchange Rate Type in ZX
3395 | repository.
3396 |
3397 |
3398 | PARAMETERS
3399 | p_customer_trx_id NUMBER (customer_trx_id of transaction)
3400 | p_exchange_rate NUMBER IN (current Exchange Rate)
3401 | p_exchange_date DATE IN (current Exhange Date)
3402 | p_exchange_rate_type VARCHAR2 OUT (current Exchange Rate Type)
3403 |
3404 |
3405 | MODIFICATION HISTORY
3406 | DATE Author Description of Changes
3407 | 09-JUL-2009 Deep Gaurab Created
3408 |
3409 *===========================================================================*/
3410
3411 PROCEDURE update_exchange_info (p_customer_trx_id IN NUMBER,
3412 p_exchange_rate IN NUMBER,
3413 p_exchange_date IN DATE,
3414 p_exchange_rate_type IN VARCHAR2) IS
3415
3416 l_success Boolean;
3417 l_event_class_code zx_trx_headers_gt.event_class_code%TYPE;
3418 l_event_type_code zx_trx_headers_gt.event_type_code%TYPE;
3419 l_transaction_rec zx_api_pub.transaction_rec_type;
3420 l_ret_status VARCHAR2(50);
3421 l_msg_count NUMBER;
3422 l_msg_data VARCHAR2(4000);
3423 l_mesg VARCHAR2(4000);
3424
3425 BEGIN
3426
3427 IF PG_DEBUG in ('Y','C') THEN
3428 arp_debug.debug('arp_etax_services_pkg.update_exchange_info (+)');
3429 END IF;
3430
3431 l_success := arp_etax_util.get_event_information(
3432 p_customer_trx_id => p_customer_trx_id,
3433 p_action => 'UPDATE',
3434 p_event_class_code => l_event_class_code,
3435 p_event_type_code => l_event_type_code);
3436
3437 IF l_success THEN
3438 l_transaction_rec.internal_organization_id := arp_global.sysparam.org_id;
3439 l_transaction_rec.application_id := 222;
3440 l_transaction_rec.entity_code := 'TRANSACTIONS';
3441 l_transaction_rec.event_class_code := l_event_class_code;
3442 l_transaction_rec.event_type_code := l_event_type_code;
3443 l_transaction_rec.trx_id := p_customer_trx_id;
3444
3445 IF PG_DEBUG in ('Y','C')
3446 THEN
3447 arp_debug.debug('Calling ZX_API_PUB.update_exchange_rate.');
3448 arp_debug.debug('Parameters within p_transaction_rec::');
3449 arp_debug.debug('======================================');
3450 arp_debug.debug('Internal_Organization_id: '|| l_transaction_rec.internal_organization_id);
3451 arp_debug.debug('Application_Id: '|| l_transaction_rec.application_id);
3452 arp_debug.debug('Entity_Code: '|| l_transaction_rec.entity_code);
3453 arp_debug.debug('Event_Class_Code: '|| l_transaction_rec.event_class_code);
3454 arp_debug.debug('Event_Type_Code: '|| l_transaction_rec.event_type_code);
3455 arp_debug.debug('Customer_trx_id:: '|| l_transaction_rec.trx_id);
3456 arp_debug.debug('======================================');
3457 END IF;
3458
3459 ZX_API_PUB.update_exchange_rate(
3460 p_api_version => 1.0,
3461 p_init_msg_list => FND_API.G_TRUE,
3462 p_commit => FND_API.G_FALSE,
3463 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
3464 x_return_status => l_ret_status,
3465 x_msg_count => l_msg_count,
3466 x_msg_data => l_msg_data,
3467 p_transaction_rec => l_transaction_rec,
3468 p_curr_conv_rate => p_exchange_rate,
3469 p_curr_conv_date => p_exchange_date,
3470 p_curr_conv_type => p_exchange_rate_type);
3471
3472 IF (l_ret_status <> 'S') THEN
3473 IF PG_DEBUG in ('Y','C') THEN
3474 arp_debug.debug('ZX_API_PUB.update_exchange_rate returned error');
3475 END IF;
3476
3477 IF ( l_msg_count = 1 ) THEN
3478
3479 IF PG_DEBUG in ('Y','C') THEN
3480 arp_debug.debug('API failed with : ' || l_msg_data);
3481 END IF;
3482
3483 l_mesg := l_msg_data;
3484 ELSIF (l_msg_count > 1) THEN
3485 LOOP
3486 l_mesg := FND_MSG_PUB.GET(FND_MSG_PUB.G_NEXT,FND_API.G_FALSE);
3487 IF (l_mesg IS NULL) THEN
3488 Exit;
3489 End IF;
3490
3491 IF PG_DEBUG in ('Y','C') THEN
3492 arp_debug.debug('API failed with : ' || l_mesg);
3493 END IF;
3494 END LOOP;
3495 END IF;
3496
3497 FND_MESSAGE.SET_NAME('FND', 'FND_GENERIC_MESSAGE');
3498 FND_MESSAGE.SET_TOKEN('MESSAGE', l_mesg);
3499 FND_MSG_PUB.ADD;
3500 APP_EXCEPTION.RAISE_EXCEPTION;
3501 END IF;
3502
3503 ELSE
3504 IF PG_DEBUG in ('Y','C') THEN
3505 arp_debug.debug('ERROR getting EVENT INFORMATION');
3506 END IF;
3507 END IF; -- END IF for l_success
3508
3509 IF PG_DEBUG in ('Y','C') THEN
3510 arp_debug.debug('arp_etax_services_pkg.update_exchange_info (-)');
3511 END IF;
3512
3513 END update_exchange_info;
3514
3515 END ARP_ETAX_SERVICES_PKG;