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