1 PACKAGE BODY ARP_ETAX_UTIL AS
2 /* $Header: AREBTUTB.pls 120.92.12020000.4 2012/07/26 05:25:14 ashlkuma ship $ */
3
4 /*=======================================================================+
5 | Package Globals
6 +=======================================================================*/
7
8 /* caching values for get_tax_account function */
9 g_tax_customer_trx_line_id ra_customer_trx_lines.customer_trx_line_id%type;
10 g_tax_rate_id NUMBER;
11 g_tax_account_ccid gl_code_combinations.code_combination_id%type;
12 g_interim_tax_ccid gl_code_combinations.code_combination_id%type;
13 g_adj_ccid gl_code_combinations.code_combination_id%type := -1;
14 g_edisc_ccid gl_code_combinations.code_combination_id%type := -1;
15 g_unedisc_ccid gl_code_combinations.code_combination_id%type := -1;
16 g_finchrg_ccid gl_code_combinations.code_combination_id%type := -1;
17 g_adj_non_rec_tax_ccid gl_code_combinations.code_combination_id%type := -1;
18 g_edisc_non_rec_tax_ccid gl_code_combinations.code_combination_id%type := -1;
19 g_unedisc_non_rec_tax_ccid gl_code_combinations.code_combination_id%type := -1;
20 g_finchrg_non_rec_tax_ccid gl_code_combinations.code_combination_id%type := -1;
21
22 g_trx_id_for_disc NUMBER;
23 g_rate_for_disc NUMBER;
24
25 l_status VARCHAR2(1); -- junk variable
26 l_industry VARCHAR2(1); -- junk variable
27 PG_DEBUG VARCHAR2(1) := NVL(FND_PROFILE.value('AFLOG_ENABLED'), 'N');
28
29 pg_so_org_id VARCHAR2(20);
30 pg_org_id NUMBER;
31
32 TYPE number_table_type IS
33 TABLE OF ra_customer_trx_lines_all.line_recoverable%type
34 INDEX BY VARCHAR2(100); --Bug 9763252
35
36 TYPE l_line_id_type IS TABLE OF
37 ra_customer_trx_lines_all.customer_trx_line_id%type
38 INDEX BY BINARY_INTEGER;
39
40 TYPE l_tax_classif_type IS TABLE OF
41 ra_customer_trx_lines_all.tax_classification_code%type
42 INDEX BY BINARY_INTEGER;
43
44 /*========================================================================
45 | Prototype Declarations Procedures
46 *=======================================================================*/
47
48
49 /*========================================================================
50 | Prototype Declarations Functions
51 *=======================================================================*/
52
53 PROCEDURE debug(text IN VARCHAR2) IS
54 BEGIN
55 --fnd_file.put_line(FND_FILE.LOG, text);
56 arp_debug.debug(text);
57 END;
58
59 /*6932455 1. Removed the Pragma Autonomous Exception.
60 * 2. Changed the Truncate to Delete
61 * 3. Removed the Commit statement
62 *
63 * 7329586 - Removed schema logic and reverted DELETE statements
64 to static sql (no need for dynamic sql here)
65 */
66 PROCEDURE clear_ebt_gt IS
67 l_owner VARCHAR2(30);
68
69 BEGIN
70 IF PG_DEBUG in ('Y', 'C') THEN
71 debug('arp_etax_util.clear_ebt_gt()+');
72 END IF;
73
74 /* The eTax GT tables are cleared upon commit. However
75 we cannot blindly issue a commit in between our first
76 (INV) and second (CM) calls. So I am adding this tidbit
77 of code to clear the tables prior to the call (for credits).
78
79 NOTE: The truncate command for GT tables only truncates
80 data for this session, not other unrelated sessions (per
81 SQL documentation) */
82
83 DELETE FROM ZX_TRX_HEADERS_GT;
84 DELETE FROM ZX_TRANSACTION_LINES_GT;
85 DELETE FROM ZX_IMPORT_TAX_LINES_GT;
86 DELETE FROM ZX_TRX_TAX_LINK_GT;
87 DELETE FROM ZX_DETAIL_TAX_LINES_GT;
88
89 IF PG_DEBUG in ('Y', 'C') THEN
90 debug('arp_etax_util.clear_ebt_gt()-');
91 END IF;
92 END;
93
94 /* Procedure to retrieve TAX lines from ZX and populate
95 RA_CUSTOMER_TRX_LINES accordingly.
96
97 During later testing, we discovered that this procedure actually
98 needs to remove existing tax lines and distributions and create
99 new tax lines. At this point, we are leaving the call to autoaccounting
100 out as the calling code already contains that call.
101
102 NOTE: This is not used by autoinvoice as a similar
103 procedure is defined in ARP_ETAX_AUTOINV_UTIL
104
105 24-MAR-2006 MRAYMOND 5114068 - Indian Localization guys
106 asked us to preserve their non-etax
107 lines.
108
109 */
110 PROCEDURE build_ar_tax_lines(
111 p_customer_trx_id IN NUMBER,
112 p_rows_inserted OUT NOCOPY NUMBER) IS
113
114 l_rows NUMBER;
115
116 BEGIN
117 IF PG_DEBUG in ('Y', 'C') THEN
118 debug('arp_etax_util.build_ar_tax_lines()+');
119 END IF;
120
121 /* Dev Notes:
122
123 1) We set autotax flag as inverse of manaully_entered_flag
124 coming from eTax
125
126 RESP: Ok. That should be fine
127
128 2) Does eTax generate autotax lines when manual ones for
129 same inv/cm line are present? If so, can we still rely
130 upon manually_entered_flag to determine which is which?
131
132 RESP: That is soft-configurable. So we can use
133 manually_entered_flag to determine what value
134 of autotax should be.
135
136 3) Previous_customer_trx_line_id.. we need a way to set this
137 for each CM tax line. I communicated this to Santosh on
138 3-MAR-05 along with my suggestion which was to have them add
139 a 45th column for link_to_trx_line_id or perhaps applied_to_trx_line_id
140 The idea for this is that they pass me the eTax line_id of the target
141 tax line and I use that to fetch the customer_trx_line_id of the
142 corresponding line in ra_customer_trx_lines.
143
144 However, I'll probably need an index based on tax_line_id in
145 RA_CUSTOMER_TRX_LINES table to make that search fast enough
146
147 4) Changed from trx_line_id to trx_id per djancis. We have to
148 build all tax lines each time.
149
150 5) Added deletes for ra_customer_trx_lines and ra_cust_trx_line_gl_dist
151 tables.
152
153 6) Added logic to populate previous_customer_trx_line_id of CM
154 tax lines. Used Navigator to tweak for improved perf.
155
156 7) Added logic to preserve IL localization tax lines.
157 spoke with Ling (etax) and she said I need to join
158 to ZX lines to confirm that there is (or isnt) a
159 tax line over there. Just a value in tax_line_id is not
160 sufficient to guarantee that it is a migrated or native R12
161 tax line.
162
163 8) Number 7 didnt work. Only way to resolve is to separate
164 delete logic from insert logic. Execute delete before
165 call to calculate_tax and insert afterwards.
166
167 9) 5487466 - always call adjust_for_inclusive_tax because we
168 may need to alter the line values if the inclusive/excl state
169 of the tax changes or the tax lines go away.
170
171 End Dev Notes */
172
173 /* Bug 5152340 - Removed delete logic to its own procedure */
174
175 INSERT INTO RA_CUSTOMER_TRX_LINES
176 (
177 CUSTOMER_TRX_LINE_ID,
178 LAST_UPDATE_DATE,
179 LAST_UPDATED_BY,
180 CREATION_DATE,
181 CREATED_BY,
182 LAST_UPDATE_LOGIN,
183 PROGRAM_ID,
184 PROGRAM_APPLICATION_ID,
185 CUSTOMER_TRX_ID,
186 LINE_NUMBER,
187 SET_OF_BOOKS_ID,
188 LINE_TYPE, -- TAX
189 LINK_TO_CUST_TRX_LINE_ID, -- parent line
190 DEFAULT_USSGL_TRANSACTION_CODE,
191 REQUEST_ID,
192 EXTENDED_AMOUNT,
193 TAX_RATE,
194 AUTOTAX,
195 AMOUNT_INCLUDES_TAX_FLAG,
196 TAXABLE_AMOUNT,
197 VAT_TAX_ID,
198 TAX_LINE_ID, -- ID in ZX_ table
199 PREVIOUS_CUSTOMER_TRX_LINE_ID,
200 PREVIOUS_CUSTOMER_TRX_ID, -- 5125882
201 ORG_ID
202 )
203 SELECT
204 ra_customer_trx_lines_s.nextval,
205 sysdate,
206 arp_standard.profile.user_id,
207 sysdate,
208 arp_standard.profile.user_id,
209 arp_standard.profile.user_id,
210 arp_standard.profile.program_id,
211 arp_standard.application_id,
212 zxt.trx_id,
213 zxt.tax_line_number,
214 arp_standard.sysparm.set_of_books_id,
215 'TAX',
216 zxt.trx_line_id,
217 plin.default_ussgl_transaction_code,
218 NULL, -- request_id
219 zxt.tax_amt,
220 zxt.tax_rate,
221 DECODE(NVL(zxt.manually_entered_flag, 'N'), 'Y', NULL, 'Y'),
222 zxt.tax_amt_included_flag,
223 zxt.taxable_amt,
224 zxt.tax_rate_id,
225 zxt.tax_line_id,
226 inv_lin.customer_trx_line_id, -- invoice tax line id
227 inv_lin.customer_trx_id, -- inv trx_id, 5125882
228 plin.org_id
229 FROM ZX_LINES zxt,
230 RA_CUSTOMER_TRX_LINES plin,
231 ZX_LINES inv_zxt,
232 RA_CUSTOMER_TRX_LINES inv_lin
233 WHERE
234 zxt.application_id = 222
235 AND zxt.entity_code = 'TRANSACTIONS'
236 AND zxt.event_class_code in ('INVOICE','DEBIT_MEMO','CREDIT_MEMO')
237 AND zxt.trx_id = p_customer_trx_id
238 AND zxt.trx_level_type = 'LINE'
239 AND zxt.trx_line_id = plin.customer_trx_line_id
240 AND zxt.adjusted_doc_tax_line_id = inv_zxt.tax_line_id (+)
241 AND inv_zxt.trx_line_id = inv_lin.link_to_cust_trx_line_id (+)
242 AND inv_zxt.tax_line_id = inv_lin.tax_line_id (+);
243
244 l_rows := SQL%ROWCOUNT;
245
246 /* The routine below was written to be called only if tax
247 returned lines. However it was recently modified to handle
248 cases where tax changes from incl to excl or when tax lines
249 are completely removed from the transaction. */
250
251 /* reduce line amount for inclusive tax lines */
252 adjust_for_inclusive_tax(p_customer_trx_id);
253
254 /* set line_recoverable and tax_recoverable */
255 set_recoverable(p_customer_trx_id);
256
257 /* Return number of rows inserted to limit calls
258 to autoaccounting and other followon code */
259 p_rows_inserted := l_rows;
260
261 IF PG_DEBUG in ('Y', 'C') THEN
262 debug(' Number of tax lines retrieved = ' || l_rows);
263 debug('arp_etax_util.build_ar_tax_lines()-');
264 END IF;
265 END build_ar_tax_lines;
266
267 /* Procedure for removing tax lines from AR prior to calculate
268 and build_ar_tax_lines calls. This code was separated out so
269 we could delete from AR based on existing tax lines just before
270 calling calculate_tax (which recreates them in ZX). This means
271 that we can use the presence of lines in ZX_LINES as a basis
272 for our delete. So the intended flow is now like this:
273
274 1) call delete_tax_lines_from_ar
275 2) call eTax calculate_tax
276 3) call build_ar_tax_lines
277
278 NOTE: This is really only relevant for forms code as the invoice
279 API, autoinvoice, and invoice copy only create transactions so there
280 should not be a case where we recalculate tax (again) or manipulate
281 existing transactions with localization tax.
282 */
283
284 PROCEDURE delete_tax_lines_from_ar(
285 p_customer_trx_id IN NUMBER) IS
286
287 l_rows NUMBER;
288 l_posted VARCHAR2(50);
289
290 BEGIN
291 IF PG_DEBUG in ('Y', 'C') THEN
292 debug('arp_etax_util.delete_tax_lines_from_ar()+');
293 END IF;
294
295 /* 8578810 - prevent deleting of posted rows or creation of additional
296 or duplicate distributions for late tax calculations */
297 BEGIN
298
299 SELECT 'Transaction is not posted'
300 INTO l_posted
301 FROM ra_cust_trx_line_gl_dist
302 WHERE customer_trx_id = p_customer_trx_id
303 AND account_class = 'REC'
304 AND latest_rec_flag = 'Y'
305 AND posting_control_id = -3;
306
307 EXCEPTION
308 WHEN NO_DATA_FOUND THEN
309 IF PG_DEBUG in ('Y','C')
310 THEN
311 debug('EXCEPTION: Transaction is posted, cannot delete');
312 END IF;
313 fnd_message.set_name('AR','AR_CANT_UPDATE_IF_POSTED');
314 app_exception.raise_exception;
315 END;
316
317
318 DELETE FROM RA_CUST_TRX_LINE_GL_DIST gld
319 WHERE customer_trx_line_id in (
320 SELECT tl.customer_trx_line_id
321 FROM RA_CUSTOMER_TRX_LINES tl,
322 ZX_LINES zx
323 WHERE tl.customer_trx_id = p_customer_trx_id
324 AND tl.line_type = 'TAX'
325 AND tl.tax_line_id IS NOT NULL
326 AND tl.tax_line_id = zx.tax_line_id)
327 AND customer_trx_id = p_customer_trx_id
328 AND account_class = 'TAX'
329 AND posting_control_id = -3;
330
331 IF PG_DEBUG in ('Y', 'C') THEN
332 l_rows := SQL%ROWCOUNT;
333 debug(' Deleted tax dists = ' || l_rows);
334 END IF;
335
336 /* NOTE: zx_lines_u2 uses only tax_line_id as key */
337
338 DELETE FROM RA_CUSTOMER_TRX_LINES
339 WHERE customer_trx_id = p_customer_trx_id
340 AND line_type = 'TAX'
341 AND tax_line_id IN
342 (SELECT tax_line_id
343 FROM ZX_LINES);
344
345 IF PG_DEBUG in ('Y', 'C') THEN
346 l_rows := SQL%ROWCOUNT;
347 debug(' Deleted tax lines = ' || l_rows);
348 debug('arp_etax_util.delete_tax_lines_from_ar()-');
349 END IF;
350
351 END delete_tax_lines_from_ar;
352
353 /* Wrapper for call to zx_api_pub.validate_and_default_tax_attr */
354 PROCEDURE validate_tax_int (p_return_status OUT NOCOPY NUMBER,
355 p_called_from_AI IN VARCHAR2 DEFAULT 'N') IS
356 l_return_status VARCHAR2(50);
357 l_message_count NUMBER;
358 l_message_data VARCHAR2(2000);
359 l_msg VARCHAR2(2000);
360 BEGIN
361
362 ZX_API_PUB.validate_and_default_tax_attr(
363 p_api_version => 1.0,
364 p_init_msg_list => FND_API.G_FALSE,
365 p_commit => FND_API.G_FALSE,
366 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
367 x_return_status => l_return_status,
368 x_msg_count => l_message_count,
369 x_msg_data => l_message_data
370 );
371
372 IF l_return_status = FND_API.G_RET_STS_SUCCESS
373 THEN
374 IF PG_DEBUG in ('Y', 'C') THEN
375 debug('validate_and_default_tax_attr returns successfully');
376 END IF;
377 p_return_status := 0;
378 ELSE /* fatal error */
379 IF PG_DEBUG in ('Y', 'C') THEN
380 debug('validate_and_default_tax_attr returns failure');
381 END IF;
382
383 IF p_called_from_AI = 'Y' THEN
384 debug('arp_etax_util.validate_tax_int()+');
385 debug('ZX_API_PUB.validate_and_default_tax_attr returns failure');
386 END IF;
387
388 IF l_return_status = FND_API.G_RET_STS_ERROR
389 THEN
390 p_return_status := 1;
391 ELSE
392 /* Unexpected error */
393 p_return_status := 2;
394 END IF;
395
396 /* Retrieve and log errors */
397 IF l_message_count = 1
398 THEN
399 debug(l_message_data);
400 IF p_called_from_AI = 'Y' THEN
401 debug(l_message_data);
402 END IF;
403 ELSIF l_message_count > 1
404 THEN
405 LOOP
406 l_msg := FND_MSG_PUB.Get(FND_MSG_PUB.G_NEXT,
407 FND_API.G_FALSE);
408 IF l_msg IS NULL
409 THEN
410 EXIT;
411 ELSE
412 debug(l_msg);
413 IF p_called_from_AI = 'Y' THEN
414 debug(l_msg);
415 END IF;
416 END IF;
417 END LOOP;
418 END IF;
419 END IF;
420
421 END validate_tax_int;
422
423 /* wrapper for call to zx_api_pub.import_document_with_tax */
424 /* 6743811 - returns 0, 1, or 2 as p_return_status
425 0=success, 1=Error, 2=Unexpected Error */
426 PROCEDURE calculate_tax_int (p_return_status OUT NOCOPY NUMBER,
427 p_called_from_AI IN VARCHAR2 DEFAULT 'N') IS
428 l_return_status VARCHAR2(50);
429 l_message_count NUMBER;
430 l_message_data VARCHAR2(2000);
431 l_msg VARCHAR2(2000);
432 BEGIN
433 ZX_API_PUB.import_document_with_tax(
434 p_api_version => 1.0,
435 p_init_msg_list => FND_API.G_FALSE,
436 p_commit => FND_API.G_FALSE,
437 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
438 x_return_status => l_return_status,
439 x_msg_count => l_message_count,
440 x_msg_data => l_message_data
441 );
442
443 IF l_return_status = FND_API.G_RET_STS_SUCCESS
444 THEN
445 IF PG_DEBUG in ('Y', 'C') THEN
446 debug('import_document_with_tax returns successfully');
447 END IF;
448 p_return_status := 0;
449 ELSE /* fatal error */
450 IF PG_DEBUG in ('Y', 'C') THEN
451 debug('import_document_with_tax returns failure');
452 END IF;
453
454 IF p_called_from_AI = 'Y' THEN
455 debug('arp_etax_util.calculate_tax_int()+');
456 debug('ZX_API_PUB.import_document_with_tax returns failure');
457 END IF;
458
459 IF l_return_status = FND_API.G_RET_STS_ERROR
460 THEN
461 p_return_status := 1;
462 ELSE
463 /* Unexpected error */
464 p_return_status := 2;
465 END IF;
466
467 /* Retrieve and log errors */
468 IF l_message_count = 1
469 THEN
470 debug(l_message_data);
471 IF p_called_from_AI = 'Y' THEN
472 debug(l_message_data);
473 END IF;
474 ELSIF l_message_count > 1
475 THEN
476 LOOP
477 l_msg := FND_MSG_PUB.Get(FND_MSG_PUB.G_NEXT,
478 FND_API.G_FALSE);
479 IF l_msg IS NULL
480 THEN
481 EXIT;
482 ELSE
483 debug(l_msg);
484 IF p_called_from_AI = 'Y' THEN
485 debug(l_msg);
486 END IF;
487 END IF;
488 END LOOP;
489 END IF;
490
491 END IF;
492
493
494 END calculate_tax_int;
495
496 /* External procedure to fetch legal entity and country
497 this is a mock up since LE should be in our tables
498 for full implementation */
499
500 /* Dev Note: This is a hack that only fetches the first
501 LE/Country.. but the underlying code supports multiple
502 LE/Country combinations. Good thing it defaults to
503 204/US! */
504 PROCEDURE get_country_and_legal_ent(
505 p_org_id IN NUMBER,
506 p_def_country OUT NOCOPY VARCHAR2,
507 p_legal_ent OUT NOCOPY NUMBER)
508 IS
509
510
511 xle_tbl xle_businessinfo_grp.OU_LE_Tbl_Type;
512
513 l_return_status VARCHAR2(50);
514 l_message_count NUMBER;
515 l_message_data VARCHAR2(2000);
516 l_msg VARCHAR2(2000);
517
518 BEGIN
519 IF PG_DEBUG in ('Y', 'C') THEN
520 debug('arp_etax_util.get_country_and_legal_ent()+');
521 END IF;
522
523
524 xle_businessinfo_grp.get_operatingunit_info(l_return_status,
525 l_message_data,
526 p_org_id,
527 NULL,
528 NULL,
529 xle_tbl);
530
531 /* Using first one for simplicity */
532 p_def_country := xle_tbl(1).country;
533 p_legal_ent := xle_tbl(1).legal_entity_id;
534
535
536 IF PG_DEBUG in ('Y', 'C') THEN
537 debug('OU = ' || p_org_id);
538 debug('LE = ' || p_legal_ent);
539 debug('DEF Country = ' || p_def_country);
540 debug('arp_etax_util.get_country_and_legal_ent()-');
541 END IF;
542
543 END get_country_and_legal_ent;
544
545 /* Public Procedure - to update doc sequence data on batch
546 transactions after insert
547
548 5468039 - added support for trx_line_gl_date
549 specifically for when gl_date changes */
550
551 PROCEDURE synchronize_for_doc_seq(p_trx_id IN NUMBER,
552 p_return_status OUT NOCOPY NUMBER,
553 p_request_id IN NUMBER DEFAULT NULL,
554 p_sync_line_data IN VARCHAR2 DEFAULT 'N')
555 IS
556 l_return_status VARCHAR2(50);
557 l_message_count NUMBER;
558 l_message_data VARCHAR2(2000);
559 l_msg VARCHAR2(2000);
560 l_default_country VARCHAR2(50);
561 l_legal_entity_id NUMBER;
562 l_sync_trx_rec ZX_API_PUB.sync_trx_rec_type;
563 l_sync_trx_lines_t ZX_API_PUB.sync_trx_lines_tbl_type%type;
564 l_ttype ra_cust_trx_types_all.type%type;
565
566 CURSOR c_req(p_request_id NUMBER) IS
567 SELECT
568 DECODE(TT.type, 'INV', 'INVOICE',
569 'DM', 'DEBIT_MEMO',
570 'CM', 'CREDIT_MEMO') event_class,
571 TT.type || '_UPDATE' event_type,
572 'Y' tax_reporting_flag,
573 T.customer_trx_id customer_trx_id,
574 T.trx_number trx_number,
575 SUBSTRB(T.comments,1,240) description,
576 T.doc_sequence_id doc_sequence_id,
577 TT.name trx_type_name,
578 -- bug 6806843
579 SEQ.name doc_seq_name,
580 T.doc_sequence_value doc_sequence_value,
581 T.batch_source_id batch_source_id,
582 TB.name batch_source_name,
583 T.cust_trx_type_id cust_trx_type_id,
584 T.trx_date trx_date,
585 T.printing_original_date printing_original_date,
586 T.term_due_date term_due_date,
587 T.bill_to_site_use_id bill_to_site_use_id
588 FROM RA_CUSTOMER_TRX T,
589 RA_CUST_TRX_TYPES TT,
590 RA_BATCH_SOURCES TB,
591 FND_DOCUMENT_SEQUENCES SEQ
592 WHERE T.request_id = p_request_id
593 AND T.cust_trx_type_id = TT.cust_trx_type_id
594 AND T.doc_sequence_id = SEQ.doc_sequence_id (+)
595 AND T.batch_source_id = TB.batch_source_id
596 AND (T.doc_sequence_id IS NOT NULL OR
597 T.doc_sequence_value IS NOT NULL OR
598 NVL(T.old_trx_number, T.trx_number) <> T.trx_number OR
599 p_sync_line_data = 'Y');
600
601 CURSOR c_trx(trx_id NUMBER, sync_line_data VARCHAR2) IS
602 SELECT
603 DECODE(TT.type, 'INV', 'INVOICE',
604 'DM', 'DEBIT_MEMO',
605 'CM', 'CREDIT_MEMO') event_class,
606 TT.type || '_UPDATE' event_type,
607 T.customer_trx_id customer_trx_id,
608 T.trx_number trx_number,
609 SUBSTRB(T.comments,1,240) description,
610 T.doc_sequence_id doc_sequence_id,
611 -- bug 6806843
612 --TT.name trx_type_name,
613 SEQ.name doc_seq_name,
614 T.doc_sequence_value doc_sequence_value,
615 T.batch_source_id batch_source_id,
616 TB.name batch_source_name,
617 TT.description trx_type_description,
618 T.printing_original_date printing_original_date,
619 T.term_due_date term_due_date,
620 TT.type type
621 FROM RA_CUSTOMER_TRX T,
622 RA_CUST_TRX_TYPES TT,
623 RA_BATCH_SOURCES TB,
624 FND_DOCUMENT_SEQUENCES SEQ
625 WHERE T.customer_trx_id = trx_id
626 AND T.cust_trx_type_id = TT.cust_trx_type_id
627 AND T.doc_sequence_id = SEQ.doc_sequence_id (+)
628 AND T.batch_source_id = TB.batch_source_id
629 AND (T.doc_sequence_id IS NOT NULL OR
630 T.doc_sequence_value IS NOT NULL OR
631 NVL(T.old_trx_number, T.trx_number) <> T.trx_number OR
632 sync_line_data = 'Y');
633
634 CURSOR c_trx_lines(trx_id NUMBER) IS
635 SELECT 222 application_id,
636 'TRANSACTIONS' entity_code,
637 DECODE(TT.type, 'INV', 'INVOICE',
638 'DM', 'DEBIT_MEMO',
639 'CM', 'CREDIT_MEMO') event_class_code,
640 T.customer_trx_id trx_id,
641 'LINE' trx_level_type,
642 TL.customer_trx_line_id trx_line_id,
643 NULL trx_waybill_number,
644 TL.description trx_line_description,
645 NULL product_description,
646 REC.gl_date trx_line_gl_date,
647 NULL merchant_party_name,
648 NULL merchant_party_document_number,
649 NULL merchant_party_reference,
650 NULL merchant_party_taxpayer_id,
651 NULL merchant_party_tax_reg_number,
652 NULL asset_number
653 FROM
654 RA_CUSTOMER_TRX T,
655 RA_CUSTOMER_TRX_LINES TL,
656 RA_CUST_TRX_TYPES TT,
657 RA_CUST_TRX_LINE_GL_DIST REC
658 WHERE T.customer_trx_id = trx_id
659 AND T.cust_trx_type_id = TT.cust_trx_type_id
660 AND T.org_id = TT.org_id
661 AND T.customer_trx_id = TL.customer_trx_id
662 AND TL.line_type = 'LINE'
663 AND T.customer_trx_id = REC.customer_trx_id (+)
664 AND REC.account_class (+) = 'REC'
665 AND REC.latest_rec_flag (+) = 'Y';
666
667 BEGIN
668 IF PG_DEBUG in ('Y', 'C')
669 THEN
670 debug('arp_etax_util.synchronize_for_doc_seq()+');
671 debug(' p_sync_line_data = ' || p_sync_line_data);
672 END IF;
673
674 l_sync_trx_rec.application_id := 222;
675 l_sync_trx_rec.entity_code := 'TRANSACTIONS';
676 p_return_status := 0;
677
678 IF p_trx_id is NOT NULL
679 THEN
680
681 OPEN c_trx(p_trx_id, p_sync_line_data);
682 FETCH c_trx INTO
683 l_sync_trx_rec.event_class_code,
684 l_sync_trx_rec.event_type_code,
685 l_sync_trx_rec.trx_id,
686 l_sync_trx_rec.trx_number,
687 l_sync_trx_rec.trx_description,
688 l_sync_trx_rec.doc_seq_id,
689 l_sync_trx_rec.doc_seq_name,
690 l_sync_trx_rec.doc_seq_value,
691 l_sync_trx_rec.batch_source_id,
692 l_sync_trx_rec.batch_source_name,
693 l_sync_trx_rec.trx_type_description,
694 l_sync_trx_rec.trx_communicated_date,
695 l_sync_trx_rec.trx_due_date,
696 l_ttype;
697
698 IF PG_DEBUG in ('Y', 'C')
699 THEN
700 debug('event_class_code: '||l_sync_trx_rec.event_class_code);
701 debug('event_type_code: '||l_sync_trx_rec.event_type_code);
702 debug('trx_id: '||l_sync_trx_rec.trx_id);
703 debug('trx_number: '||l_sync_trx_rec.trx_number);
704 debug('trx_description: '||l_sync_trx_rec.trx_description);
705 debug('doc_seq_id: '||l_sync_trx_rec.doc_seq_id);
706 debug('doc_seq_name: '||l_sync_trx_rec.doc_seq_name);
707 debug('doc_seq_value: '||l_sync_trx_rec.doc_seq_value);
708 debug('batch_source_id: '||l_sync_trx_rec.batch_source_id);
709 debug('batch_source_name: '||l_sync_trx_rec.batch_source_name);
710 debug('trx_type_description: '||l_sync_trx_rec.trx_type_description);
711 debug('trx_communicated_date: '||l_sync_trx_rec.trx_communicated_date);
712 debug('trx_due_date: '||l_sync_trx_rec.trx_due_date);
713 debug('trx_type.type: ' || l_ttype);
714 END IF;
715
716 /* 5748090 - preserve values in columns that are not
717 directly used by AR */
718 l_sync_trx_rec.supplier_tax_invoice_number := FND_API.G_MISS_CHAR;
719 l_sync_trx_rec.supplier_tax_invoice_date := FND_API.G_MISS_DATE;
720 l_sync_trx_rec.supplier_exchange_rate := FND_API.G_MISS_NUM;
721 l_sync_trx_rec.tax_invoice_date := FND_API.G_MISS_DATE;
722 l_sync_trx_rec.tax_invoice_number := FND_API.G_MISS_CHAR;
723 l_sync_trx_rec.port_of_entry_code := FND_API.G_MISS_CHAR;
724 l_sync_trx_rec.application_doc_status := FND_API.G_MISS_CHAR;
725
726 IF c_trx%rowcount > 0 AND l_ttype NOT IN ('DEP','GUAR')
727 THEN
728 /* 5468039 - set p_sync_trx_lines_tbl if gl_date
729 has to get updated */
730 IF p_sync_line_data = 'Y'
731 THEN
732 OPEN c_trx_lines(p_trx_id);
733 FETCH c_trx_lines BULK COLLECT INTO
734 l_sync_trx_lines_t;
735 CLOSE c_trx_lines;
736 END IF;
737
738 /* Now call the API to synchronize the repository */
739 ZX_API_PUB.synchronize_tax_repository(
740 p_api_version => 1.0,
741 p_init_msg_list => FND_API.G_FALSE,
742 p_commit => FND_API.G_FALSE,
743 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
744 x_return_status => l_return_status,
745 x_msg_count => l_message_count,
746 x_msg_data => l_message_data,
747 p_sync_trx_rec => l_sync_trx_rec,
748 p_sync_trx_lines_tbl => l_sync_trx_lines_t
749 );
750
751 /* If a problem arises with synchronization, document it */
752 IF l_return_status = FND_API.G_RET_STS_SUCCESS
753 THEN
754 IF PG_DEBUG in ('Y', 'C') THEN
755 debug('sychronize_tax returns successfully');
756 END IF;
757 p_return_status := 0;
758 ELSIF l_return_status = FND_API.G_RET_STS_ERROR
759 THEN
760 IF PG_DEBUG in ('Y', 'C') THEN
761 debug('synchronize_tax returns with validation errors');
762 END IF;
763 p_return_status := 1;
764 ELSE /* fatal error */
765 p_return_status := 2;
766 IF PG_DEBUG in ('Y', 'C') THEN
767 debug('synchronize_tax returns failure');
768 END IF;
769
770 /* Retrieve and log errors */
771 IF l_message_count = 1
772 THEN
773 debug(l_message_data);
774 ELSIF l_message_count > 1
775 THEN
776 LOOP
777 l_msg := FND_MSG_PUB.Get(FND_MSG_PUB.G_NEXT,
778 FND_API.G_FALSE);
779 IF l_msg IS NULL
780 THEN
781 EXIT;
782 ELSE
783 debug(l_msg);
784 END IF;
785 END LOOP;
786 END IF;
787 END IF;
788 END IF;
789 CLOSE c_trx;
790
791 ELSIF p_request_id IS NOT NULL
792 THEN
793
794 /* 8401487 - preserve values in columns that are not
795 directly used by AR */
796 l_sync_trx_rec.supplier_tax_invoice_number := FND_API.G_MISS_CHAR;
797 l_sync_trx_rec.supplier_tax_invoice_date := FND_API.G_MISS_DATE;
798 l_sync_trx_rec.supplier_exchange_rate := FND_API.G_MISS_NUM;
799 l_sync_trx_rec.tax_invoice_date := FND_API.G_MISS_DATE;
800 l_sync_trx_rec.tax_invoice_number := FND_API.G_MISS_CHAR;
801 l_sync_trx_rec.port_of_entry_code := FND_API.G_MISS_CHAR;
802 l_sync_trx_rec.application_doc_status := FND_API.G_MISS_CHAR;
803
804 FOR trx IN c_req(p_request_id) LOOP
805
806 /* move columns from cursor to record */
807 l_sync_trx_rec.event_class_code := trx.event_class;
808 l_sync_trx_rec.event_type_code := trx.event_type;
809 l_sync_trx_rec.trx_id := trx.customer_trx_id;
810 l_sync_trx_rec.trx_number := trx.trx_number;
811 l_sync_trx_rec.trx_description := trx.description;
812 l_sync_trx_rec.doc_seq_id := trx.doc_sequence_id;
813 l_sync_trx_rec.doc_seq_name := trx.doc_seq_name;
814 l_sync_trx_rec.doc_seq_value := trx.doc_sequence_value;
815 l_sync_trx_rec.batch_source_id := trx.batch_source_id;
816 l_sync_trx_rec.batch_source_name := trx.batch_source_name;
817 l_sync_trx_rec.trx_type_description := trx.trx_type_name;
818 l_sync_trx_rec.trx_communicated_date := trx.printing_original_date;
819 l_sync_trx_rec.trx_due_date := trx.term_due_date;
820
821 IF PG_DEBUG in ('Y', 'C')
822 THEN
823 debug('event_class_code: '||l_sync_trx_rec.event_class_code);
824 debug('event_type_code: '||l_sync_trx_rec.event_type_code);
825 debug('trx_id: '||l_sync_trx_rec.trx_id);
826 debug('trx_number: '||l_sync_trx_rec.trx_number);
827 debug('trx_description: '||l_sync_trx_rec.trx_description);
828 debug('doc_seq_id: '||l_sync_trx_rec.doc_seq_id);
829 debug('doc_seq_name: '||l_sync_trx_rec.doc_seq_name);
830 debug('doc_seq_value: '||l_sync_trx_rec.doc_seq_value);
831 debug('batch_source_id: '||l_sync_trx_rec.batch_source_id);
832 debug('batch_source_name: '||l_sync_trx_rec.batch_source_name);
833 debug('trx_type_description: '||l_sync_trx_rec.trx_type_description);
834 debug('trx_communicated_date: '||l_sync_trx_rec.trx_communicated_date);
835 debug('trx_due_date: '||l_sync_trx_rec.trx_due_date);
836 END IF;
837
838 /* Now call the API to synchronize the repository */
839 ZX_API_PUB.synchronize_tax_repository(
840 p_api_version => 1.0,
841 p_init_msg_list => FND_API.G_FALSE,
842 p_commit => FND_API.G_FALSE,
843 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
844 x_return_status => l_return_status,
845 x_msg_count => l_message_count,
846 x_msg_data => l_message_data,
847 p_sync_trx_rec => l_sync_trx_rec,
848 p_sync_trx_lines_tbl => l_sync_trx_lines_t
849 );
850
851 /* If a problem arises with synchronization, document it */
852 IF l_return_status = FND_API.G_RET_STS_SUCCESS
853 THEN
854 IF PG_DEBUG in ('Y', 'C') THEN
855 debug('sychronize_tax returns successfully');
856 END IF;
857 p_return_status := 0;
858 ELSIF l_return_status = FND_API.G_RET_STS_ERROR
859 THEN
860 IF PG_DEBUG in ('Y', 'C') THEN
861 debug('synchronize_tax returns with validation errors');
862 END IF;
863 p_return_status := 1;
864 ELSE /* fatal error */
865 p_return_status := 2;
866 IF PG_DEBUG in ('Y', 'C') THEN
867 debug('synchronize_tax returns failure');
868 END IF;
869
870 /* Retrieve and log errors */
871 IF l_message_count = 1
872 THEN
873 debug(l_message_data);
874 ELSIF l_message_count > 1
875 THEN
876 LOOP
877 l_msg := FND_MSG_PUB.Get(FND_MSG_PUB.G_NEXT,
878 FND_API.G_FALSE);
879 IF l_msg IS NULL
880 THEN
881 EXIT;
882 ELSE
883 debug(l_msg);
884 END IF;
885 END LOOP;
886 END IF;
887 END IF;
888
889 END LOOP;
890
891 END IF;
892
893 IF PG_DEBUG in ('Y', 'C')
894 THEN
895 debug('arp_etax_util.synchronize_for_doc_seq()-');
896 END IF;
897
898 END synchronize_for_doc_seq;
899
900 /*bug 6806843. Removing the procedure synchronize_for_auto_trxnum. Please see
901 * the bug for details */
902
903
904 /* Internal Helper Procedure for calling zx api */
905 PROCEDURE zx_global_document_update(
906 p_trx_rec IN OUT NOCOPY ZX_API_PUB.transaction_rec_type)
907 IS
908 l_return_status VARCHAR2(50);
909 l_message_count NUMBER;
910 l_message_data VARCHAR2(2000);
911 l_msg VARCHAR2(2000);
912
913 BEGIN
914 IF PG_DEBUG in ('Y', 'C') THEN
915 debug('zx_global_document_update called for ' ||
916 p_trx_rec.trx_id);
917 END IF;
918
919 /* Now call the API to synchronize the repository */
920 ZX_API_PUB.global_document_update(
921 p_api_version => 1.0,
922 p_init_msg_list => FND_API.G_FALSE,
923 p_commit => FND_API.G_FALSE,
924 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
925 x_return_status => l_return_status,
926 x_msg_count => l_message_count,
927 x_msg_data => l_message_data,
928 p_transaction_rec => p_trx_rec
929 );
930
931 /* If a problem arises with synchronization, document it */
932 IF l_return_status = FND_API.G_RET_STS_SUCCESS
933 THEN
934 IF PG_DEBUG in ('Y', 'C') THEN
935 debug('gdu returns successfully');
936 END IF;
937 ELSIF l_return_status = FND_API.G_RET_STS_ERROR
938 THEN
939 IF PG_DEBUG in ('Y', 'C') THEN
940 debug('gdu returns with validation errors');
941 END IF;
942 ELSE /* fatal error */
943 IF PG_DEBUG in ('Y', 'C') THEN
944 debug('gdu returns failure');
945 END IF;
946
947 /* Retrieve and log errors */
948 IF l_message_count = 1
949 THEN
950 debug(l_message_data);
951 ELSIF l_message_count > 1
952 THEN
953 LOOP
954 l_msg := FND_MSG_PUB.Get(FND_MSG_PUB.G_NEXT,
955 FND_API.G_FALSE);
956 IF l_msg IS NULL
957 THEN
958 EXIT;
959 ELSE
960 debug(l_msg);
961 END IF;
962 END LOOP;
963 END IF;
964 END IF;
965
966 END zx_global_document_update;
967
968 /* Public Procedure - notifies etax that we are freezing the transaction.
969 Supported Actions:
970
971 PRINT */
972
973 PROCEDURE global_document_update(p_customer_trx_id IN NUMBER,
974 p_request_id IN NUMBER,
975 p_action IN VARCHAR2)
976 IS
977 l_return_status VARCHAR2(50);
978 l_message_count NUMBER;
979 l_message_data VARCHAR2(2000);
980 l_msg VARCHAR2(2000);
981
982 l_trx_rec ZX_API_PUB.transaction_rec_type;
983
984 CURSOR batch_trx(p_request_id NUMBER) IS
985 SELECT t.customer_trx_id customer_trx_id,
986 DECODE(tt.type,
987 'INV', 'INVOICE',
988 'DM', 'DEBIT_MEMO',
989 'CM', 'CREDIT_MEMO') event_class,
990 tt.type event_prefix
991 FROM ra_customer_trx t,
992 ra_cust_trx_types tt
993 WHERE t.request_id = p_request_id
994 AND t.cust_trx_type_id = tt.cust_trx_type_id;
995
996 BEGIN
997 IF PG_DEBUG in ('Y', 'C')
998 THEN
999 debug('arp_etax_util.global_document_update()+');
1000 debug(' trx_id = ' || p_customer_trx_id);
1001 debug(' req_id = ' || p_request_id);
1002 debug(' action = ' || p_action);
1003 END IF;
1004
1005 l_trx_rec.internal_organization_id := arp_global.sysparam.org_id;
1006 l_trx_rec.application_id := 222;
1007 l_trx_rec.entity_code := 'TRANSACTIONS';
1008
1009 IF p_customer_trx_id IS NOT NULL
1010 THEN
1011
1012 /* set event_class based on trx type */
1013 SELECT t.customer_trx_id,
1014 DECODE(tt.type,
1015 'INV', 'INVOICE',
1016 'DM', 'DEBIT_MEMO',
1017 'CM', 'CREDIT_MEMO'),
1018 tt.type || '_' || p_action
1019 INTO l_trx_rec.trx_id,
1020 l_trx_rec.event_class_code,
1021 l_trx_rec.event_type_code
1022 FROM ra_customer_trx t,
1023 ra_cust_trx_types tt
1024 WHERE t.customer_trx_id = p_customer_trx_id
1025 AND t.cust_trx_type_id = tt.cust_trx_type_id;
1026
1027 zx_global_document_update(l_trx_rec);
1028
1029 ELSIF p_request_id IS NOT NULL
1030 THEN
1031
1032 FOR c_trx IN batch_trx(p_request_id) LOOP
1033
1034 l_trx_rec.trx_id := c_trx.customer_trx_id;
1035 l_trx_rec.event_class_code := c_trx.event_class;
1036 l_trx_rec.event_type_code := c_trx.event_prefix || '_' || p_action;
1037
1038 zx_global_document_update(l_trx_rec);
1039
1040 END LOOP;
1041
1042 END IF;
1043
1044 IF PG_DEBUG in ('Y', 'C')
1045 THEN
1046 debug('arp_etax_util.global_document_update()-');
1047 END IF;
1048 END global_document_update;
1049
1050 /*=======================================================================
1051 |
1052 | PROCEDURE
1053 | get_default_tax_classification
1054 |
1055 | DESCRIPTION
1056 | This routine will call the Etax Api: get_default_Tax_classification
1057 | and will return just a tax classification code. This will replace
1058 | the 11i get_default_tax_code calls (which returned tax code,
1059 | tax override flag, tax code id, tax type and description)
1060 |
1061 |
1062 | ARGUMENTS : IN
1063 | p_ship_to_site_use_id (line, header or null)
1064 | p_bill_to_site_use_id (Header level)
1065 | p_inv_item_id
1066 | p_org_id
1067 | p_sob_id
1068 | p_trx_date
1069 | p_trx_type_id
1070 | p_memo_line_id
1071 | p_salesrep_id
1072 | p_warehouse_id
1073 | p_customer_id
1074 | p_cust_trx_id
1075 | p_cust_trx_line_id
1076 | p_func_short_name (ACCT_RULES, ACCT_DIST, GL_ACCT_FIXUP,
1077 | GL_ACCT_FIRST)
1078 | : OUT
1079 | tax_classification_code
1080 |
1081 | NOTES:
1082 |
1083 | MODIFICATION HISTORY:
1084 |
1085 | 03/07/05 Debbie Sue Jancis Created
1086 |
1087 +========================================================================*/
1088 PROCEDURE get_default_tax_classification(
1089 p_ship_to_site_use_id IN NUMBER DEFAULT NULL,
1090 p_bill_to_site_use_id IN NUMBER DEFAULT NULL,
1091 p_inv_item_id IN NUMBER DEFAULT NULL,
1092 p_org_id IN NUMBER,
1093 p_sob_id IN NUMBER,
1094 p_trx_date IN DATE,
1095 p_trx_type_id IN NUMBER,
1096 p_cust_trx_id IN NUMBER,
1097 p_cust_trx_line_id IN NUMBER DEFAULT NULL,
1098 p_customer_id IN NUMBER DEFAULT NULL,
1099 p_memo_line_id IN NUMBER DEFAULT NULL,
1100 p_salesrep_id IN NUMBER DEFAULT NULL,
1101 p_warehouse_id IN NUMBER DEFAULT NULL,
1102 p_entity_code IN VARCHAR2,
1103 p_event_class_code IN VARCHAR2,
1104 p_function_short_name IN VARCHAR2,
1105 p_tax_classification_code OUT NOCOPY VARCHAR2 ) IS
1106
1107 l_ccid ra_cust_trx_line_gl_dist_all.code_combination_id%type;
1108 l_concat_segments VARCHAR2(2000);
1109 l_fail_count NUMBER;
1110 BEGIN
1111 debug('arp_etax_utils.get_default_tax_classification()+');
1112
1113 /* 4928047 - Call autoaccounting to get the ccid first, then
1114 feed it to zx */
1115 ARP_AUTO_ACCOUNTING.do_autoaccounting(
1116 'G'
1117 ,'REV'
1118 ,p_cust_trx_id
1119 ,NULL
1120 ,NULL
1121 ,NULL
1122 ,p_trx_date
1123 ,NULL
1124 ,NULL
1125 ,NULL
1126 ,NULL
1127 ,p_trx_type_id
1128 ,p_salesrep_id
1129 ,p_inv_item_id
1130 ,p_memo_line_id
1131 ,p_warehouse_id
1132 ,l_ccid
1133 ,l_concat_segments
1134 ,l_fail_count);
1135
1136 IF l_ccid = -1
1137 THEN
1138 debug('Unable to fetch ccid');
1139 l_ccid := NULL;
1140 END IF;
1141
1142 zx_ar_tax_classificatn_def_pkg.get_default_tax_classification(
1143 p_ship_to_site_use_id => p_ship_to_site_use_id,
1144 p_bill_to_site_use_id => p_bill_to_site_use_id,
1145 p_inventory_item_id => p_inv_item_id,
1146 p_organization_id => p_warehouse_id,
1147 p_set_of_books_id => p_sob_id,
1148 p_trx_date => p_trx_date,
1149 p_trx_type_id => p_trx_type_id,
1150 p_cust_trx_id => p_cust_trx_id,
1151 p_cust_trx_line_id => p_cust_trx_line_id,
1152 p_customer_id => p_customer_id,
1153 p_memo_line_id => p_memo_line_id,
1154 APPL_SHORT_NAME => 'AR',
1155 FUNC_SHORT_NAME => p_function_short_name,
1156 p_entity_code => p_entity_code,
1157 p_event_class_code => p_event_class_code,
1158 p_application_id => 222,
1159 p_internal_organization_id => p_org_id,
1160 p_ccid => l_ccid,
1161 p_tax_classification_code => p_tax_classification_code);
1162
1163 debug('arp_etax_util.get_default_tax_classification()-)');
1164
1165 END get_default_tax_classification;
1166
1167 /*=======================================================================
1168 |
1169 | PROCEDURE
1170 | set_default_tax_classification
1171 |
1172 | DESCRIPTION
1173 | This routine copies the tax_classification back from
1174 | zx_lines to the corresponding LINE row in ra_customer_trx_lines.
1175 |
1176 |
1177 | ARGUMENTS : IN
1178 | p_request_id IN NUMBER
1179 | p_phase IN VARCHAR2 DEFAULT 'INV'
1180 | : OUT
1181 |
1182 |
1183 | NOTES:
1184 |
1185 | MODIFICATION HISTORY:
1186 |
1187 | 01/06/06 MRAYMOND Created
1188 |
1189 +========================================================================*/
1190 PROCEDURE set_default_tax_classification(p_request_id IN NUMBER,
1191 p_phase IN VARCHAR2 DEFAULT 'INV')
1192 IS
1193
1194 t_line_id l_line_id_type;
1195 t_class_code l_tax_classif_type;
1196
1197 l_rows_needing_update NUMBER;
1198 l_rows_updated NUMBER;
1199
1200 CURSOR line_to_tax_class(p_request_id NUMBER, p_phase VARCHAR2) IS
1201 select /*+ index (tl RA_CUSTOMER_TRX_LINES_N4) */
1202 tl.customer_trx_line_id,
1203 nvl(tl.tax_classification_code, zx.output_tax_classification_code)
1204 from ra_customer_trx t,
1205 ra_customer_trx_lines tl,
1206 zx_lines_det_factors zx
1207 where t.request_id = p_request_id
1208 and t.customer_trx_id = tl.customer_trx_id
1209 and tl.line_type = 'LINE'
1210 and tl.request_id = p_request_id
1211 and NVL(t.previous_customer_trx_id, -99) =
1212 DECODE(p_phase, 'INV', -99, t.previous_customer_trx_id)
1213 and zx.application_id = 222
1214 and zx.entity_code = 'TRANSACTIONS'
1215 and zx.event_class_code in ('INVOICE','DEBIT_MEMO','CREDIT_MEMO')
1216 and zx.trx_id = tl.customer_trx_id
1217 and zx.trx_level_type = 'LINE'
1218 and zx.trx_line_id = tl.customer_trx_line_id;
1219
1220 BEGIN
1221 IF PG_DEBUG = 'Y' THEN
1222 debug( 'arp_etax_util.set_default_tax_classification()+' );
1223 END IF;
1224
1225 OPEN line_to_tax_class(p_request_id, p_phase);
1226 FETCH line_to_tax_class BULK COLLECT INTO
1227 t_line_id,
1228 t_class_code;
1229
1230 l_rows_needing_update := line_to_tax_class%ROWCOUNT;
1231
1232 IF l_rows_needing_update > 0
1233 THEN
1234 FORALL i IN t_line_id.FIRST..t_line_id.LAST
1235 UPDATE ra_customer_trx_lines
1236 SET tax_classification_code = t_class_code(i)
1237 WHERE customer_trx_line_id = t_line_id(i);
1238 END IF;
1239
1240 l_rows_updated := SQL%ROWCOUNT;
1241
1242 IF PG_DEBUG = 'Y' THEN
1243 debug( ' rows found : ' || l_rows_needing_update);
1244 debug( ' rows updated : ' || l_rows_updated);
1245 debug( 'arp_etax_util.set_default_tax_classification()-' );
1246 END IF;
1247
1248 END;
1249
1250 FUNCTION get_event_information (p_customer_trx_id IN NUMBER,
1251 p_action IN VARCHAR2,
1252 p_event_class_code OUT NOCOPY VARCHAR2,
1253 p_event_type_code OUT NOCOPY VARCHAR2)
1254 RETURN BOOLEAN IS
1255
1256 l_trx_class RA_CUST_TRX_TYPES.TYPE%TYPE;
1257 l_return_var BOOLEAN := TRUE;
1258
1259 BEGIN
1260
1261 IF PG_DEBUG = 'Y' THEN
1262 debug('arp_etax_util.get_event_information()+)');
1263 END IF;
1264
1265 BEGIN
1266
1267 Select type.type
1268 into l_trx_class
1269 from ra_customer_trx trx,
1270 ra_cust_trx_types type
1271 where trx.customer_trx_id = p_customer_trx_id
1272 and trx.cust_trx_type_id = type.cust_Trx_type_id;
1273
1274
1275 IF PG_DEBUG = 'Y' THEN
1276 debug('TRX_TYPE for customer_trx_id ' || p_customer_trx_id ||
1277 ' is ' || l_trx_class);
1278 END IF;
1279
1280 EXCEPTION
1281 WHEN OTHERS THEN
1282 IF PG_DEBUG = 'Y' THEN
1283 debug('EXCEPTION occured while getting trx_type: ' ||
1284 sqlcode || ', ' || sqlerrm);
1285 END IF;
1286 END;
1287
1288 IF (l_trx_class = 'INV') THEN
1289 p_event_class_code := 'INVOICE';
1290 ELSIF (l_trx_class = 'DM') THEN
1291 p_event_class_code := 'DEBIT_MEMO';
1292 ELSIF (l_trx_class = 'CM') THEN
1293 p_event_class_code := 'CREDIT_MEMO';
1294 ELSE
1295 -- Event Class code is null so the function will return false because
1296 -- eTax is not defined to be called for this Type
1297 p_event_class_code := NULL;
1298 p_event_type_code := NULL;
1299 RETURN FALSE;
1300 END IF;
1301
1302 p_event_type_code := l_trx_class || '_' || p_action;
1303
1304 IF PG_DEBUG = 'Y' THEN
1305 debug('TRX_CLASS = ' || l_trx_class || ', TRX_CLASS_CODE = ' ||
1306 p_event_class_code || ', P_EVENT_TYPE_CODE = ' ||
1307 p_event_type_code);
1308 debug('arp_etax_util.get_event_information()-)');
1309 END IF;
1310
1311 RETURN l_return_var;
1312
1313 END get_event_information;
1314
1315 /* Pulled from arp_tax_compound verbatum. Need to support same logic
1316 but wanted to get away from dependency to old tax logic */
1317 FUNCTION tax_curr_round( p_amount IN NUMBER,
1318 p_trx_currency_code IN VARCHAR2 default null,
1319 p_precision IN NUMBER,
1320 p_min_acct_unit IN NUMBER,
1321 p_rounding_rule IN VARCHAR2 default 'NEAREST',
1322 p_autotax_flag IN VARCHAR2 default 'Y' )
1323
1324 RETURN NUMBER IS
1325
1326 l_rounded_amount NUMBER;
1327 l_precision NUMBER;
1328 l_rounding_rule VARCHAR2(30);
1329 l_min_acct_unit NUMBER;
1330 l_round_adj NUMBER;
1331
1332 BEGIN
1333 IF PG_DEBUG = 'Y' THEN
1334 debug( 'arp_etax_util.tax_curr_round(' || p_amount || ')+' );
1335 END IF;
1336
1337 l_rounding_rule := p_rounding_rule;
1338
1339 if p_trx_currency_code = arp_standard.sysparm.tax_currency_code and p_autotax_flag in ( 'Y','U')
1340 THEN
1341
1342 l_precision := least( p_precision, nvl(arp_standard.sysparm.tax_precision, p_precision) );
1343 l_min_acct_unit := greatest( nvl(p_min_acct_unit, arp_standard.sysparm.tax_minimum_accountable_unit),
1344 nvl(arp_standard.sysparm.tax_minimum_accountable_unit, p_min_acct_unit));
1345
1346 ELSE
1347
1348 l_precision := p_precision;
1349 l_min_acct_unit := p_min_acct_unit;
1350
1351 END IF;
1352
1353 IF PG_DEBUG = 'Y' THEN
1354 debug(' trx currency = :'||p_trx_currency_code||':');
1355 debug(' sys currency = :'||arp_standard.sysparm.tax_currency_code||':');
1356 debug(' autotax = :'||p_autotax_flag||':');
1357 debug(' rounding rule = :'||l_rounding_rule||':');
1358 debug(' precision = :'||l_precision||':');
1359 debug(' mau = :'||l_min_acct_unit||':');
1360 END IF;
1361
1362 IF ( nvl(l_min_acct_unit,0) <> 0 )
1363 THEN
1364
1365 IF nvl(l_rounding_rule, 'NEAREST' ) = 'UP'
1366 THEN
1367 --
1368 -- Round the amount Up to next Min Accountable Unit
1369 --
1370 l_rounded_amount := sign(p_amount)* (CEIL(abs(p_amount) / l_min_acct_unit) * l_min_acct_unit);
1371
1372 ELSIF nvl(l_rounding_rule, 'NEAREST' ) = 'DOWN'
1373 THEN
1374
1375 --
1376 -- Round the amount Down to the prior Min Accountable Unit
1377 --
1378 l_rounded_amount := TRUNC(p_amount/l_min_acct_unit) * l_min_acct_unit;
1379
1380 ELSE /* ROUND NEAREST BY DEFAULT */
1381
1382 --
1383 -- Round the amount to the nearest Min Accountable Unit
1384 --
1385 l_rounded_amount := ROUND(p_amount / l_min_acct_unit) * l_min_acct_unit;
1386
1387 END IF;
1388
1389
1390 ELSE
1391
1392 --
1393 -- Minimum Accountable Unit is not specified, use
1394 -- the precision to control the rounding
1395 --
1396 IF nvl(l_rounding_rule, 'NEAREST' ) = 'UP'
1397 THEN
1398 --
1399 -- Round the amount Up at the given precision
1400 -- Amounts that are already at this precision
1401 -- are not changed.
1402 --
1403 IF p_amount <> trunc(p_amount, l_precision)
1404 THEN
1405 l_rounded_amount := ROUND( p_amount + (sign( p_amount)*(power( 10, (l_precision*-1))/2)), l_precision );
1406 ELSE
1407 l_rounded_amount := p_amount;
1408 END IF;
1409 ELSIF nvl(l_rounding_rule, 'NEAREST' ) = 'DOWN'
1410 THEN
1411 --
1412 -- Round the amount Down to the prior precision
1413 --
1414 l_rounded_amount:= TRUNC( p_amount, l_precision );
1415
1416 ELSE /* Default Nearest */
1417 --
1418 -- Round the amount to the nearest precision
1419 --
1420 l_rounded_amount := ROUND( p_amount, l_precision );
1421
1422 END IF;
1423
1424 END IF;
1425
1426 IF PG_DEBUG = 'Y' THEN
1427 debug( 'arp_tax_compound.tax_curr_round('||l_rounded_amount||')-');
1428 END IF;
1429 RETURN (l_rounded_amount);
1430
1431 EXCEPTION
1432 WHEN OTHERS THEN
1433 debug( 'EXCEPTION: arp_tax_compound.tax_curr_round(-)');
1434 RAISE;
1435 END tax_curr_round;
1436
1437 /* init accounting structure */
1438 PROCEDURE init_ae_struct(p_ae_sys_rec IN OUT NOCOPY arp_acct_main.ae_sys_rec_type)
1439
1440 IS
1441 BEGIN
1442 SELECT sob.set_of_books_id,
1443 sob.chart_of_accounts_id,
1444 sob.currency_code,
1445 c.precision,
1446 c.minimum_accountable_unit,
1447 sysp.code_combination_id_gain,
1448 sysp.code_combination_id_loss,
1449 sysp.code_combination_id_round
1450 INTO p_ae_sys_rec.set_of_books_id,
1451 p_ae_sys_rec.coa_id,
1452 p_ae_sys_rec.base_currency,
1453 p_ae_sys_rec.base_precision,
1454 p_ae_sys_rec.base_min_acc_unit,
1455 p_ae_sys_rec.gain_cc_id,
1456 p_ae_sys_rec.loss_cc_id,
1457 p_ae_sys_rec.round_cc_id
1458 FROM ar_system_parameters sysp,
1459 gl_sets_of_books sob,
1460 fnd_currencies c
1461 WHERE sob.set_of_books_id = sysp.set_of_books_id
1462 AND sob.currency_code = c.currency_code;
1463 END;
1464
1465 /* Internal procedure for prorating the accounting entires
1466 associated with adjustments or discounts */
1467
1468 /* Dev Note: I debated adding the line_id to this call - but
1469 I dont think it is necessary. These routines only process the
1470 lines that are present in zx_lines for the given adjustment. There
1471 is no expectation that the same adj would be processed more than once.
1472 */
1473
1474 /* 4607809 - The call to arp_det_dist_pkg requires that the RA row
1475 be present. However, in many cases, receipts do not have
1476 application rows when this is called. To facilitate that,
1477 I am removing the call from here and creating a new (separate)
1478 public function that can be called near ARP_ACCT_MAIN calls
1479 */
1480
1481 /* 5677984 - Add p_ra_app_id parameter. For receipt UNAPPLY, we call
1482 with the same parameters as an APPLY and get a duplicate set of dists
1483 because the sql returns both APP and UNAPP ones. Using the p_ra_app_id
1484 (passed as APP or UNAPP application row, allows us to limit returns
1485 to only the current APP or UNAPP row. */
1486
1487 -- Added parameter for line level adjustment ER.
1488 PROCEDURE prorate_accounting(p_transaction_rec IN zx_api_pub.transaction_rec_type,
1489 p_mode IN VARCHAR2,
1490 p_ra_app_id IN NUMBER,
1491 p_gt_id IN OUT NOCOPY number,
1492 p_from_llca_call IN varchar2 DEFAULT 'N',
1493 p_target_line_id IN ra_customer_trx_lines.customer_trx_line_id%TYPE DEFAULT NULL)
1494 IS
1495 l_gt_id NUMBER;
1496 l_return_status_service VARCHAR2(4000);
1497 l_msg_count NUMBER;
1498 l_msg_data VARCHAR2(4000);
1499 l_msg VARCHAR2(2000);
1500 l_mode VARCHAR2(20);
1501 l_adj_rec ar_adjustments%ROWTYPE;
1502 l_trx_rec ra_customer_trx%ROWTYPE;
1503 l_app_rec ar_receivable_applications%ROWTYPE;
1504 l_rows_inserted NUMBER;
1505 l_acct_meth ar_system_parameters.accounting_method%TYPE;
1506 l_ae_sys_rec arp_acct_main.ae_sys_rec_type;
1507 l_gt_passed BOOLEAN := FALSE;
1508
1509 -- Added for Line Level Adjustment
1510 l_from_llca_call VARCHAR2(1);
1511
1512 CURSOR debug_gt IS
1513 SELECT gt_id,
1514 source_id,
1515 source_table,
1516 customer_trx_id,
1517 customer_trx_line_id,
1518 line_type,
1519 line_amount,
1520 ed_line_amount,
1521 uned_line_amount,
1522 tax_amount,
1523 ed_tax_amount,
1524 uned_tax_amount
1525 FROM AR_LINE_DIST_INTERFACE_GT;
1526
1527
1528 BEGIN
1529 IF PG_DEBUG in ('Y', 'C') THEN
1530 arp_util.debug('arp_etax_util.prorate_accounting()+');
1531 arp_util.debug(' p_mode = ' || p_mode);
1532 arp_util.debug(' p_trans_rec.trx_id = ' || p_transaction_rec.trx_id);
1533 arp_util.debug(' p_ra_app_id = ' || p_ra_app_id);
1534 arp_util.debug(' p_from_llca_call = ' || p_from_llca_call);
1535 arp_util.debug(' p_target_line_id = ' || p_target_line_id);
1536 END IF;
1537
1538
1539 -- Added for Line Level Adjustment
1540 l_from_llca_call := p_from_llca_call;
1541
1542 /* set local mode variable for use in sql */
1543 IF p_mode in ('INV','LINE','TAX')
1544 THEN
1545 l_mode := 'ADJUST';
1546 ELSIF p_mode in ('APP_ED','UNAPP_ED')
1547 THEN
1548 l_mode := 'EDISC';
1549 ELSE
1550 l_mode := 'UNEDISC';
1551 END IF;
1552
1553 IF PG_DEBUG in ('Y','C') THEN
1554 arp_util.debug(' local mode = ' || l_mode);
1555 END IF;
1556
1557 IF NVL(p_gt_id,0) = 0
1558 THEN
1559 /* Get sequence for line level distributions API */
1560 arp_det_dist_pkg.get_gt_sequence
1561 (l_gt_id,
1562 l_return_status_service,
1563 l_msg_count,
1564 l_msg_data);
1565
1566 p_gt_id := l_gt_id;
1567 ELSE
1568 l_gt_passed := TRUE; -- don't allow it to get nulled in this
1569 -- program.
1570 l_gt_id := p_gt_id;
1571 END IF;
1572
1573 IF PG_DEBUG in ('Y','C') THEN
1574 arp_util.debug('l_gt_id = ' || l_gt_id);
1575 END IF;
1576
1577 /* Insert tax lines into _GT table for processing */
1578
1579 /* Dev Note: Discounts are calculated as negative amounts
1580 in etax because they are decreasing the tax liability.
1581 However, for proration purposes, the allocation code
1582 expects them to be positive values. As such, we
1583 have to reverse the sign of the amount(s) from ZX
1584 specifically for discounts. Adjustments are already
1585 in the correct sign (same sign for AR and ZX) */
1586
1587 INSERT INTO AR_LINE_DIST_INTERFACE_GT
1588 ( GT_ID,
1589 SOURCE_ID,
1590 SOURCE_TABLE,
1591 CUSTOMER_TRX_ID,
1592 CUSTOMER_TRX_LINE_ID,
1593 LINE_TYPE,
1594 TAX_AMOUNT,
1595 ED_TAX_AMOUNT,
1596 UNED_TAX_AMOUNT)
1597 (SELECT
1598 l_gt_id,
1599 zx.trx_id,
1600 DECODE(l_mode, 'ADJUST' ,'ADJ',
1601 'UNEDISC','RA',
1602 'EDISC' ,'RA'),
1603 tl.customer_trx_id,
1604 tl.customer_trx_line_id,
1605 'TAX',
1606 DECODE(l_mode, 'ADJUST', zx.tax_amt, NULL),
1607 DECODE(l_mode, 'EDISC', zx.tax_amt, NULL),
1608 DECODE(l_mode, 'UNEDISC',zx.tax_amt, NULL)
1609 FROM
1610 zx_lines zx,
1611 ra_customer_trx_lines tl
1612 WHERE
1613 zx.application_id = p_transaction_rec.application_id
1614 AND zx.entity_code = p_transaction_rec.entity_code
1615 AND zx.event_class_code = p_transaction_rec.event_class_code
1616 AND zx.trx_id = p_transaction_rec.trx_id
1617 AND zx.trx_level_type =
1618 DECODE(l_mode, 'EDISC','LINE_EARNED',
1619 'UNEDISC','LINE_UNEARNED',
1620 'LINE')
1621 -- AND zx.trx_line_id = NVL(p_ra_app_id, zx.trx_line_id)
1622 AND tl.link_to_cust_trx_line_id = zx.adjusted_doc_line_id
1623 AND tl.line_type = 'TAX'
1624 AND tl.tax_line_id = zx.adjusted_doc_tax_line_id);
1625
1626 l_rows_inserted := SQL%ROWCOUNT;
1627
1628 arp_util.debug('tax lines inserted = ' || l_rows_inserted);
1629
1630 /* Only insert LINEs if it is not a tax-only adj */
1631 IF p_mode <> 'TAX'
1632 THEN
1633
1634 /* Insert line amounts */
1635 INSERT INTO AR_LINE_DIST_INTERFACE_GT
1636 ( GT_ID,
1637 SOURCE_ID,
1638 SOURCE_TABLE,
1639 CUSTOMER_TRX_ID,
1640 CUSTOMER_TRX_LINE_ID,
1641 LINE_TYPE,
1642 LINE_AMOUNT,
1643 ED_LINE_AMOUNT,
1644 UNED_LINE_AMOUNT)
1645 (SELECT
1646 l_gt_id,
1647 zx.trx_id,
1648 DECODE(l_mode, 'ADJUST' ,'ADJ',
1649 'UNEDISC','RA',
1650 'EDISC' ,'RA'),
1651 il.customer_trx_id,
1652 il.customer_trx_line_id,
1653 'LINE',
1654 DECODE(l_mode, 'ADJUST', max(zx.line_amt) -
1655 sum(zx.tax_amt), NULL),
1656 DECODE(l_mode, 'EDISC',max(zx.line_amt) -
1657 sum(zx.tax_amt), NULL),
1658 DECODE(l_mode, 'UNEDISC',max(zx.line_amt) -
1659 sum(zx.tax_amt), NULL)
1660 FROM
1661 zx_lines zx,
1662 ra_customer_trx_lines il
1663 WHERE
1664 zx.application_id = p_transaction_rec.application_id
1665 AND zx.entity_code = p_transaction_rec.entity_code
1666 AND zx.event_class_code = p_transaction_rec.event_class_code
1667 AND zx.trx_id = p_transaction_rec.trx_id
1668 AND il.customer_trx_id = zx.adjusted_doc_trx_id
1669 AND il.customer_trx_line_id = zx.adjusted_doc_line_id
1670 AND il.line_type = 'LINE'
1671 GROUP BY zx.trx_id, zx.adjusted_doc_line_id,
1672 il.customer_trx_id, il.customer_trx_line_id);
1673
1674 l_rows_inserted := l_rows_inserted + SQL%ROWCOUNT;
1675 arp_util.debug('Total line and tax rows inserted = ' ||
1676 l_rows_inserted);
1677
1678 END IF;
1679
1680 /* Check total rows inserted.. if none, set p_gt_id to zero
1681 and exit */
1682 IF l_rows_inserted = 0
1683 THEN
1684 IF l_gt_passed
1685 THEN
1686 /* gt_id was passed into this program from a prior
1687 call. Do not null it here as there are other
1688 accounting entries using it */
1689 NULL;
1690 ELSE
1691 /* set gt_id to zero so we don't use
1692 it later */
1693 p_gt_id := 0;
1694 END IF;
1695 RETURN;
1696 ELSE
1697 /* Displays content of GT table. This was necessary
1698 because the GT table was doing some strange things
1699 in the early days of SLA */
1700 IF PG_DEBUG in ('Y', 'C') THEN
1701 FOR d IN debug_gt LOOP
1702 arp_util.debug(d.customer_trx_id || '~' ||
1703 d.customer_trx_line_id || '~' ||
1704 d.line_type || ' line=' ||
1705 d.line_amount || ' ed_line=' ||
1706 d.ed_line_amount || ' uned_line=' ||
1707 d.uned_line_amount || ' tax=' ||
1708 d.tax_amount || ' ed_tax=' ||
1709 d.ed_tax_amount || ' uned_tax=' ||
1710 d.uned_tax_amount);
1711 END LOOP;
1712 END IF;
1713 /* End debug code */
1714 END IF;
1715
1716 /* Set up various parameter records */
1717 IF l_mode = 'ADJUST'
1718 THEN
1719 SELECT *
1720 INTO l_adj_rec
1721 FROM ar_adjustments
1722 WHERE adjustment_id = p_transaction_rec.trx_id;
1723
1724 SELECT *
1725 INTO l_trx_rec
1726 FROM ra_customer_trx
1727 WHERE customer_trx_id = l_adj_rec.customer_trx_id;
1728
1729 /* Now initialize the acct engine and
1730 call the distribution routine (adjustments) */
1731 init_ae_struct(l_ae_sys_rec);
1732
1733 arp_det_dist_pkg.adjustment_with_interface(
1734 p_customer_trx => l_trx_rec,
1735 p_adj_rec => l_adj_rec,
1736 p_ae_sys_rec => l_ae_sys_rec,
1737 p_gt_id => l_gt_id,
1738 p_line_flag => 'INTERFACE',
1739 p_tax_flag => 'INTERFACE',
1740 x_return_status=> l_return_status_service,
1741 x_msg_count => l_msg_count,
1742 x_msg_data => l_msg_data,
1743 p_llca_from_call => l_from_llca_call,
1744 p_customer_trx_line_id => p_target_line_id);
1745
1746 ELSIF l_mode in ('EDISC', 'UNEDISC')
1747 THEN
1748 /* Call arp_det_dist_pkg later by
1749 calling distribute_recoverable */
1750 IF PG_DEBUG in ('Y', 'C') THEN
1751 arp_util.debug('Discount accounting skipped');
1752 arp_util.debug(' see distribute_recoverable later in log');
1753 END IF;
1754
1755 ELSE
1756 arp_util.debug('EXCEPTION: Invalid mode. ' || l_mode);
1757 p_gt_id := 0;
1758 RETURN;
1759 END IF;
1760
1761 /* Check for errors */
1762 IF l_return_status_service <> FND_API.G_RET_STS_SUCCESS THEN
1763
1764 /* Retrieve and log errors */
1765 IF l_msg_count = 1
1766 THEN
1767 debug(l_msg_data);
1768 p_gt_id := 0;
1769 ELSIF l_msg_count > 1
1770 THEN
1771 LOOP
1772 l_msg := FND_MSG_PUB.Get(FND_MSG_PUB.G_NEXT,
1773 FND_API.G_FALSE);
1774 IF l_msg IS NULL
1775 THEN
1776 EXIT;
1777 ELSE
1778 debug(l_msg);
1779 END IF;
1780 END LOOP;
1781 p_gt_id := 0;
1782 END IF;
1783 END IF;
1784
1785 IF PG_DEBUG in ('Y', 'C') THEN
1786 arp_util.debug('arp_etax_util.prorate_accounting()-');
1787 END IF;
1788
1789 END prorate_accounting;
1790
1791 PROCEDURE distribute_recoverable(p_rec_app_id IN NUMBER,
1792 p_gt_id IN NUMBER)
1793 IS
1794 l_return_status_service VARCHAR2(4000);
1795 l_msg_count NUMBER;
1796 l_msg_data VARCHAR2(4000);
1797 l_msg VARCHAR2(2000);
1798 l_trx_rec ra_customer_trx%ROWTYPE;
1799 l_app_rec ar_receivable_applications%ROWTYPE;
1800 l_acct_meth ar_system_parameters.accounting_method%TYPE;
1801 l_ae_sys_rec arp_acct_main.ae_sys_rec_type;
1802
1803 BEGIN
1804 IF PG_DEBUG in ('Y', 'C') THEN
1805 arp_util.debug('arp_etax_util.distribute_recoverable()+');
1806 arp_util.debug(' p_rec_app_id = ' || p_rec_app_id);
1807 END IF;
1808
1809 SELECT *
1810 INTO l_app_rec
1811 FROM ar_receivable_applications
1812 WHERE receivable_application_id = p_rec_app_id;
1813
1814 SELECT *
1815 INTO l_trx_rec
1816 FROM ra_customer_trx
1817 WHERE customer_trx_id = l_app_rec.applied_customer_trx_id;
1818
1819 init_ae_struct(l_ae_sys_rec);
1820
1821 /* Now call the distribution routine (receipts) */
1822 /* 5159129 Added p_line_flag and p_tax_flag for discounts */
1823 /* 5677984 Added p_uned* parameters so this handles
1824 unearned discounts properly */
1825 arp_det_dist_pkg.application_with_interface(
1826 p_customer_trx => l_trx_rec,
1827 p_app_rec => l_app_rec,
1828 p_ae_sys_rec => l_ae_sys_rec,
1829 p_gt_id => p_gt_id,
1830 p_line_flag => 'NORMAL',
1831 p_tax_flag => 'NORMAL',
1832 p_ed_line_flag => 'INTERFACE',
1833 p_ed_tax_flag => 'INTERFACE',
1834 p_uned_line_flag => 'INTERFACE',
1835 p_uned_tax_flag => 'INTERFACE',
1836 x_return_status=> l_return_status_service,
1837 x_msg_count => l_msg_count,
1838 x_msg_data => l_msg_data);
1839
1840 /* Check for errors */
1841 IF l_return_status_service <> FND_API.G_RET_STS_SUCCESS THEN
1842
1843 /* Retrieve and log errors */
1844 IF l_msg_count = 1
1845 THEN
1846 debug(l_msg_data);
1847 ELSIF l_msg_count > 1
1848 THEN
1849 LOOP
1850 l_msg := FND_MSG_PUB.Get(FND_MSG_PUB.G_NEXT,
1851 FND_API.G_FALSE);
1852 IF l_msg IS NULL
1853 THEN
1854 EXIT;
1855 ELSE
1856 debug(l_msg);
1857 END IF;
1858 END LOOP;
1859 END IF;
1860 END IF;
1861
1862 IF PG_DEBUG in ('Y', 'C') THEN
1863 arp_util.debug('arp_etax_util.distribute_recoverable()-');
1864 END IF;
1865
1866 END distribute_recoverable;
1867
1868
1869 /* Procedure for updating adjustment and PS rows in cases where the
1870 originals were inserted before the etax call. Supports the following
1871 values:
1872
1873 Y - update both
1874 A - update adjustments only
1875 N - update neither
1876 */
1877
1878 PROCEDURE update_adj_and_ps(
1879 p_upd_adj_and_ps IN VARCHAR2,
1880 p_adj_id IN NUMBER,
1881 p_prorated_line IN NUMBER,
1882 p_prorated_tax IN NUMBER)
1883 IS
1884 l_adj_rec ar_adjustments%ROWTYPE;
1885 l_ps_rec ar_payment_schedules%ROWTYPE;
1886 l_orig_line_adj NUMBER := 0;
1887 l_orig_tax_adj NUMBER := 0;
1888 l_ps_update_needed BOOLEAN := TRUE;
1889 BEGIN
1890 IF PG_DEBUG in ('Y', 'C') THEN
1891 arp_util.debug('arp_etax_util.update_adj_and_ps()+');
1892 arp_util.debug(' p_upd_adj_and_ps = ' || p_upd_adj_and_ps);
1893 arp_util.debug(' p_prorated_line = ' || p_prorated_line);
1894 arp_util.debug(' p_prorated_tax = ' || p_prorated_tax);
1895 END IF;
1896
1897 IF p_upd_adj_and_ps in ('Y','A')
1898 THEN
1899 /* get original adjustment amounts */
1900 SELECT nvl(line_adjusted,0),
1901 nvl(tax_adjusted,0)
1902 INTO l_orig_line_adj,
1903 l_orig_tax_adj
1904 FROM ar_adjustments
1905 WHERE adjustment_id = p_adj_id;
1906
1907 /* if the original and new amounts are
1908 different, update adjustment here */
1909 IF l_orig_line_adj <> p_prorated_line OR
1910 l_orig_tax_adj <> p_prorated_tax
1911 THEN
1912
1913 /* update adjustment manually */
1914 UPDATE ar_adjustments
1915 SET line_adjusted = p_prorated_line,
1916 tax_adjusted = p_prorated_tax
1917 WHERE adjustment_id = p_adj_id;
1918 ELSE
1919 /* the new and original adj amounts
1920 are equal, set bool so we skip the
1921 PS update */
1922 l_ps_update_needed := FALSE;
1923
1924 IF PG_DEBUG in ('Y', 'C') THEN
1925 arp_util.debug(' orig and prorate amts same, skip update(s)');
1926 END IF;
1927 END IF;
1928 END IF;
1929
1930 /* Do the PS update if specified and if
1931 it is still deemed necessary */
1932 IF p_upd_adj_and_ps in ('Y','P') AND
1933 l_ps_update_needed = TRUE
1934 THEN
1935
1936 /* select adj back from db for PS update
1937 NOTE: This includes update line and tax
1938 from above */
1939 SELECT *
1940 INTO l_adj_rec
1941 FROM ar_adjustments
1942 WHERE adjustment_id = p_adj_id;
1943
1944 arp_util.debug('from adj record <in db>');
1945 arp_util.debug(' line adjusted = ' || l_adj_rec.line_adjusted);
1946 arp_util.debug(' tax adjusted = ' || l_adj_rec.tax_adjusted);
1947
1948
1949 /* Incorporate the original adj line and tax amounts.
1950 These columns are zero if the adjustment was not changed
1951 by this routine */
1952 l_adj_rec.line_adjusted := l_adj_rec.line_adjusted - l_orig_line_adj;
1953 l_adj_rec.tax_adjusted := l_adj_rec.tax_adjusted - l_orig_tax_adj;
1954
1955 arp_ps_util.update_adj_related_columns(
1956 l_adj_rec.payment_schedule_id,
1957 l_adj_rec.type,
1958 l_adj_rec.amount,
1959 null,
1960 l_adj_rec.line_adjusted,
1961 l_adj_rec.tax_adjusted,
1962 l_adj_rec.freight_adjusted,
1963 l_adj_rec.receivables_charges_adjusted,
1964 l_adj_rec.apply_date,
1965 l_adj_rec.gl_date,
1966 l_adj_rec.acctd_amount,
1967 l_ps_rec );
1968
1969 END IF;
1970
1971 IF PG_DEBUG in ('Y', 'C') THEN
1972 arp_util.debug('arp_etax_util.update_adj_and_ps()-');
1973 END IF;
1974 END update_adj_and_ps;
1975
1976 /* 5677984 redirected original prorate_recoverable to the new
1977 overloaded version (see next definition)
1978
1979 Procedure for prorating recoverable and calculating tax. Making this
1980 generic so it can be used for adjustments, discounts, and perhaps
1981 other places as needed */
1982
1983 /* PARAMETERS:
1984 p_adj_id ID for adjustment or receivable application
1985
1986 p_target_id customer_trx_id of target invoice
1987
1988 p_amount raw adjustment amount (to be allocated)
1989
1990 p_apply_date date of application or adjustment
1991
1992 p_mode LINE Prorate line and tax (normal line adj mode)
1993 TAX Adjust tax-only
1994 INV Adjust entire transaction
1995 APP_ED Receipt apply (earned discount)
1996 APP_UED Receipt apply (unearned discount)
1997 UNAPP_ED Receipt unapply (earned discount)
1998 UNAPP_UED Receipt unapply (unearned discount)
1999
2000 p_upd_adj_and_ps Flag to indicate if we need to do the mainteannce
2001 of adj and ps rows for this adjustment
2002 Y=update both
2003 A=update adj only
2004 NULL/N=do nothing
2005
2006 p_gt_id ID assigned for proration logic
2007
2008 p_prorated_line amount allocated to lines
2009
2010 p_prorated_tax amount allocated to tax
2011 */
2012 PROCEDURE prorate_recoverable(
2013 p_adj_id IN NUMBER,
2014 p_target_id IN NUMBER,
2015 p_target_line_id IN NUMBER,
2016 p_amount IN NUMBER,
2017 p_apply_date IN DATE,
2018 p_mode IN VARCHAR2,
2019 p_upd_adj_and_ps IN VARCHAR2,
2020 p_gt_id IN OUT NOCOPY NUMBER,
2021 p_prorated_line IN OUT NOCOPY NUMBER,
2022 p_prorated_tax IN OUT NOCOPY NUMBER,
2023 p_quote IN VARCHAR2 DEFAULT 'N')
2024 IS
2025 l_junk_ra_app_id NUMBER := -1;
2026 BEGIN
2027 /* call new prorate_recoverable with -1 for receivable_application_id */
2028 prorate_recoverable(
2029 p_adj_id,
2030 p_target_id,
2031 p_target_line_id,
2032 p_amount,
2033 p_apply_date,
2034 p_mode,
2035 p_upd_adj_and_ps,
2036 p_gt_id,
2037 p_prorated_line,
2038 p_prorated_tax,
2039 p_quote,
2040 l_junk_ra_app_id);
2041
2042 END prorate_recoverable;
2043
2044 /* Procedure for prorating recoverable and calculating tax. Making this
2045 generic so it can be used for adjustments, discounts, and perhaps
2046 other places as needed */
2047
2048 /* PARAMETERS:
2049 p_adj_id ID for adjustment or cash_receipt
2050
2051 p_target_id customer_trx_id of target invoice
2052
2053 p_target_line_id customer_trx_line_id of target invoice
2054
2055 p_amount raw adjustment amount (to be allocated)
2056
2057 p_apply_date date of application or adjustment
2058
2059 p_mode LINE Prorate line and tax (normal line adj mode)
2060 TAX Adjust tax-only
2061 INV Adjust entire transaction
2062 APP_ED Receipt apply (earned discount)
2063 APP_UED Receipt apply (unearned discount)
2064 UNAPP_ED Receipt unapply (earned discount)
2065 UNAPP_UED Receipt unapply (unearned discount)
2066
2067 p_upd_adj_and_ps Flag to indicate if we need to do the mainteannce
2068 of adj and ps rows for this adjustment
2069 Y=update both
2070 A=update adj only
2071 NULL/N=do nothing
2072
2073 p_gt_id ID assigned for proration logic
2074
2075 p_prorated_line amount allocated to lines
2076
2077 p_prorated_tax amount allocated to tax
2078
2079 p_ra_app_id the application_id to be used for receipt
2080 APP and UNAPP activities.
2081 If passed in as -1, ignore.
2082 If passed in as NULL, assign a value.
2083 If passed as value other than -1, use as is
2084
2085 */
2086 PROCEDURE prorate_recoverable(
2087 p_adj_id IN NUMBER,
2088 p_target_id IN NUMBER,
2089 p_target_line_id IN NUMBER,
2090 p_amount IN NUMBER,
2091 p_apply_date IN DATE,
2092 p_mode IN VARCHAR2,
2093 p_upd_adj_and_ps IN VARCHAR2,
2094 p_gt_id IN OUT NOCOPY NUMBER,
2095 p_prorated_line IN OUT NOCOPY NUMBER,
2096 p_prorated_tax IN OUT NOCOPY NUMBER,
2097 p_quote IN VARCHAR2 DEFAULT 'N',
2098 p_ra_app_id IN OUT NOCOPY NUMBER)
2099 IS
2100
2101 l_recov_flag VARCHAR2(1);
2102 l_historical_flag VARCHAR2(1);
2103 l_sum NUMBER; -- divisor for proration logic
2104 l_row NUMBER := 0; -- row counter for inserting
2105 -- line_det_fact rows
2106 l_round_target_amt NUMBER := 0; -- rounding corrections
2107 l_round_target_line_id NUMBER := NULL; -- rounding corrections
2108 l_amount NUMBER; -- working copy of adj/disc amount
2109 l_tax_amount NUMBER; -- for tax_only adj calcs
2110 l_line_proration NUMBER; -- amount prorated to given line
2111 l_total_proration NUMBER := 0; -- total prorated for transaction
2112 l_total_tax_recov NUMBER := 0; -- tax recoverable for entire trx
2113
2114 l_transaction_rec zx_api_pub.transaction_rec_type;
2115 l_transaction_line_rec zx_api_pub.transaction_line_rec_type;
2116
2117 l_return_status_service VARCHAR2(4000);
2118 l_msg_count NUMBER;
2119 l_msg_data VARCHAR2(4000);
2120 l_doc_level_recalc_flag VARCHAR2(1);
2121 l_adj_number VARCHAR2(30);
2122 l_rounding_correction NUMBER;
2123 l_prorated_tax NUMBER;
2124 l_prorated_line NUMBER;
2125 l_cust_id NUMBER;
2126 l_site_use_id NUMBER;
2127 t_prorated_amt number_table_type; -- total prorated adj amount per line
2128
2129 l_lines_processed BOOLEAN := FALSE;
2130
2131 l_adj_ra_id NUMBER; -- adj_id or ra_app_id
2132 -- depending on whether its an adj or app
2133
2134 -- Added for Line Level Adjustment ER
2135 l_from_llca_call VARCHAR2(1);
2136
2137 /* Bug 8512053 */
2138 l_amt_due_remaining NUMBER;
2139 l_ctrl_hdr_tx_appl_flag VARCHAR2(1) DEFAULT 'N';
2140 l_tax_adjusted NUMBER DEFAULT 0;
2141
2142 CURSOR trx_lines(p_trx_id NUMBER, p_trx_line_id NUMBER) IS
2143 SELECT
2144 TRX.org_id internal_organization_id,
2145 TRX.customer_trx_id inv_trx_id,
2146 TRX.trx_number trx_number,
2147 TRX.invoice_currency_code trx_currency_code,
2148 CUR.precision trx_precision,
2149 CUR.minimum_accountable_unit trx_mau,
2150 TRX.exchange_date,
2151 TRX.exchange_rate,
2152 TRX.exchange_rate_type,
2153 TRX.legal_entity_id legal_entity_id,
2154 TRX.trx_date inv_trx_date,
2155 DECODE(TT.type,'CM','CREDIT_MEMO',
2156 'DM','DEBIT_MEMO',
2157 'INVOICE') trx_event_class,
2158 TRX.ship_to_customer_id trx_ship_to_customer_id,
2159 TRX.ship_to_site_use_id trx_ship_to_site_use_id,
2160 AR.set_of_books_id ledger_id,
2161 BILL_CUST.party_id bill_to_party_id,
2162 BILL_AS.party_site_id bill_to_party_site_id,
2163 BILL_PS.location_id bill_to_location_id,
2164 LINES.customer_trx_line_id inv_trx_line_id,
2165 LINES.line_number inv_trx_line_number,
2166 NVL(LINES.historical_flag,'N') historical_flag,
2167 LINES.extended_amount line_amt,
2168 LINES.tax_exempt_flag exemption_control_flag,
2169 LINES.tax_exempt_number exempt_certificate_number,
2170 LINES.tax_exempt_reason_code exempt_reason,
2171 DECODE(LINES.memo_line_id, NULL,
2172 NVL(LINES.warehouse_id,to_number(pg_so_org_id))) warehouse_id,
2173 LINES.line_recoverable,
2174 LINES.tax_recoverable,
2175 LINES.ship_to_customer_id line_ship_to_customer_id,
2176 LINES.ship_to_site_use_id line_ship_to_site_use_id,
2177 LINES.inventory_item_id inv_product_id,
2178 HR.location_id ship_from_location_id,
2179 HRL.location_id poa_location_id,
2180 SR_PER.organization_id poo_party_id,
2181 SR_HRL.location_id poo_location_id
2182 FROM
2183 RA_CUSTOMER_TRX TRX,
2184 RA_CUST_TRX_TYPES TT,
2185 RA_CUSTOMER_TRX_LINES LINES,
2186 AR_SYSTEM_PARAMETERS AR,
2187 FND_CURRENCIES CUR,
2188 HZ_CUST_ACCOUNTS BILL_CUST,
2189 HZ_PARTIES BILL_PARTY,
2190 HZ_CUST_ACCT_SITES BILL_AS,
2191 HZ_CUST_SITE_USES BILL_SU,
2192 HZ_PARTY_SITES BILL_PS,
2193 HR_ALL_ORGANIZATION_UNITS HR,
2194 HR_ORGANIZATION_UNITS HRL,
2195 JTF_RS_SALESREPS SR,
2196 PER_ALL_ASSIGNMENTS_F SR_PER,
2197 HR_ORGANIZATION_UNITS SR_HRL
2198 WHERE
2199 TRX.customer_trx_id = p_trx_id and
2200 LINES.customer_trx_id = TRX.customer_trx_id and
2201 LINES.customer_trx_line_id =
2202 NVL(p_trx_line_id,LINES.customer_trx_line_id) and
2203 LINES.line_type IN ('LINE' ,'CB') and
2204 TRX.org_id = AR.org_id and
2205 TRX.cust_trx_type_id = TT.cust_trx_type_id and
2206 TRX.invoice_currency_code = CUR.currency_code and
2207 TRX.bill_to_customer_id = BILL_CUST.cust_account_id and
2208 BILL_CUST.party_id = BILL_PARTY.party_id and
2209 BILL_CUST.cust_account_id = BILL_AS.cust_account_id and
2210 BILL_AS.cust_acct_site_id = BILL_SU.cust_acct_site_id and
2211 BILL_SU.site_use_id = TRX.bill_to_site_use_id and
2212 BILL_AS.party_site_id = BILL_PS.party_site_id and
2213 LINES.warehouse_id = HR.organization_id (+) and
2214 TRX.org_id = HRL.organization_id and
2215 TRX.primary_salesrep_id = SR.salesrep_id (+) and
2216 TRX.org_id = SR.org_id (+) and
2217 SR.person_id = SR_PER.person_id (+) and
2218 TRX.trx_date BETWEEN nvl(SR_PER.effective_start_date, TRX.trx_date)
2219 AND nvl(SR_PER.effective_end_date, TRX.trx_date) and
2220 NVL(SR_PER.primary_flag, 'Y') = 'Y' and
2221 SR_PER.assignment_type (+) = 'E' and
2222 SR_PER.organization_id = SR_HRL.organization_id (+)
2223 order by LINES.extended_amount;
2224
2225 /* Selects detail tax lines back from ZX by adj_id/app_id */
2226 /* 4937059 - grouped by line_id */
2227 CURSOR tax_lines(p_entity VARCHAR2, p_event_class VARCHAR2,
2228 p_trx_id NUMBER, p_trx_line_id NUMBER,
2229 p_mode VARCHAR2) IS
2230 SELECT adjusted_doc_line_id, SUM(tax_amt) tax_amt, MAX(line_amt) line_amt
2231 FROM zx_lines
2232 WHERE application_id = 222
2233 AND entity_code = p_entity
2234 AND event_class_code = p_event_class
2235 AND trx_id = p_trx_id
2236 AND trx_line_id = NVL(p_trx_line_id, trx_line_id)
2237 AND trx_level_type = DECODE(p_mode,
2238 'APP_ED', 'LINE_EARNED',
2239 'UNAPP_ED', 'LINE_EARNED',
2240 'APP_UED', 'LINE_UNEARNED',
2241 'UNAPP_UED','LINE_UNEARNED',
2242 'LINE')
2243 GROUP BY adjusted_doc_line_id;
2244
2245 /* For Quotes */
2246 CURSOR est_tax_lines(p_entity VARCHAR2, p_event_class VARCHAR2,
2247 p_trx_id NUMBER, p_trx_line_id NUMBER,
2248 p_mode VARCHAR2) IS
2249 SELECT adjusted_doc_line_id, SUM(tax_amt) tax_amt, MAX(line_amt) line_amt
2250 FROM zx_detail_tax_lines_gt
2251 WHERE application_id = 222
2252 AND entity_code = p_entity
2253 AND event_class_code = p_event_class
2254 AND trx_id = p_trx_id
2255 AND trx_line_id = NVL(p_trx_line_id, trx_line_id)
2256 AND trx_level_type = DECODE(p_mode,
2257 'APP_ED', 'LINE_EARNED',
2258 'UNAPP_ED', 'LINE_EARNED',
2259 'APP_UED', 'LINE_UNEARNED',
2260 'UNAPP_UED','LINE_UNEARNED',
2261 'LINE')
2262 GROUP BY adjusted_doc_line_id;
2263
2264
2265 BEGIN
2266 IF PG_DEBUG in ('Y', 'C') THEN
2267 debug('arp_etax_debug.prorate_recoverable()+');
2268 debug(' p_adj_id = ' || p_adj_id);
2269 debug(' p_target_id = ' || p_target_id);
2270 debug(' p_target_line_id = ' || p_target_line_id);
2271 debug(' p_amount = ' || p_amount);
2272 debug(' p_apply_date = ' || p_apply_date);
2273 debug(' p_mode = ' || p_mode);
2274 debug(' p_upd_adj_and_ps = ' || p_upd_adj_and_ps);
2275 debug(' p_gt_id = ' || p_gt_id);
2276 debug(' p_quote = ' || p_quote);
2277 debug(' p_ra_app_id = ' || p_ra_app_id);
2278 END IF;
2279
2280 /* Set pg_so_org_id any time it is not set or
2281 any time the OU changes. This supports cases where
2282 users change OU without exiting form */
2283 IF NVL(pg_org_id,-99) <> arp_global.sysparam.org_id
2284 THEN
2285 pg_org_id := arp_global.sysparam.org_id;
2286
2287 pg_so_org_id := oe_profile.value('SO_ORGANIZATION_ID',
2288 pg_org_id);
2289 END IF;
2290
2291 /* Set event class, type, and entity based on
2292 receivables_trx.type */
2293 IF p_mode in ('LINE','TAX','INV')
2294 THEN
2295 /* ADJUSTMENTS */
2296 l_amount := p_amount; /* adj in correct sign already */
2297 l_transaction_rec.entity_code := 'ADJUSTMENTS';
2298 l_transaction_rec.event_class_code := 'INVOICE_ADJUSTMENT';
2299 l_transaction_rec.event_type_code := 'ADJ_CREATE';
2300
2301 /* Get adj number */
2302 SELECT a.adjustment_number, r.tax_recoverable_flag,a.tax_adjusted
2303 INTO l_adj_number,
2304 l_recov_flag,
2305 l_tax_adjusted /* Bug 8512053 */
2306 FROM ar_adjustments a,
2307 ar_receivables_trx r
2308 WHERE a.adjustment_id = p_adj_id
2309 AND a.receivables_trx_id = r.receivables_trx_id
2310 AND a.org_id = r.org_id;
2311
2312 ELSIF p_mode in ('APP_ED', 'APP_UED', 'UNAPP_ED', 'UNAPP_UED')
2313 THEN
2314 /* RECEIPTS */
2315 l_transaction_rec.entity_code := 'RECEIPTS';
2316 l_transaction_rec.event_class_code := 'RECEIPTS';
2317 IF p_mode in ('APP_ED','APP_UED') /* APPLY */
2318 THEN
2319 l_amount := p_amount * -1; /* apps are passed as positive */
2320 l_transaction_rec.event_type_code := 'RECP_APPLY';
2321 ELSE /* UNAPPLY */
2322 l_amount := p_amount; /* unapp should be kept positive */
2323 l_transaction_rec.event_type_code := 'RECP_UNAPPLY';
2324 END IF;
2325
2326 /* 5677984 - deal with receipt application id.
2327 It may be zero coming from arcpau */
2328 IF NVL(p_ra_app_id,0) = 0
2329 THEN
2330 /* get it from sequence */
2331 SELECT ar_receivable_applications_s.nextval
2332 INTO p_ra_app_id
2333 FROM DUAL;
2334
2335 IF PG_DEBUG IN ('Y','C')
2336 THEN
2337 debug('assigned p_ra_app_id = ' ||
2338 p_ra_app_id);
2339 END IF;
2340 ELSIF p_ra_app_id = -1
2341 THEN
2342 /* we have a problem -- wrong prorate_recoverable routine
2343 was called and -1 was defaulted */
2344 debug('EXCEPTION: p_ra_app_id is -1 for a receipt');
2345 ELSE
2346 /* application_id passed in, don't change a thing */
2347 NULL;
2348 END IF;
2349
2350 /* Get receipt number into adj_number */
2351 /* set local l_recov_flag based on mode and outer joins,
2352 no return = N */
2353 select cr.receipt_number,
2354 NVL(decode(p_mode, 'APP_ED', earn.tax_recoverable_flag,
2355 'UNAPP_ED', earn.tax_recoverable_flag,
2356 'APP_UED', unearn.tax_recoverable_flag,
2357 'UNAPP_UED', unearn.tax_recoverable_flag),
2358 'N')
2359 into l_adj_number,
2360 l_recov_flag
2361 from ar_cash_receipts cr,
2362 ar_receipt_method_accounts arm,
2363 ar_receivables_trx earn,
2364 ar_receivables_trx unearn
2365 where cr.cash_receipt_id = p_adj_id
2366 and cr.receipt_method_id = arm.receipt_method_id
2367 and arm.edisc_receivables_trx_id = earn.receivables_trx_id (+)
2368 and arm.unedisc_receivables_trx_id = unearn.receivables_trx_id (+)
2369 and cr.remit_bank_acct_use_id = arm.remit_bank_acct_use_id; --bug6401710
2370
2371 ELSE
2372 /* Unknown condition, note it in log for debugging */
2373 debug('EXCEPTION: unknown mode : ' || p_mode);
2374 p_prorated_line := p_amount;
2375 p_prorated_tax := 0;
2376 RETURN;
2377 END IF;
2378
2379 /* If the activity is not recoverable, then bail out without
2380 doing anything */
2381 IF NVL(l_recov_flag, 'N') = 'N'
2382 THEN
2383 IF (p_quote = 'N')
2384 THEN
2385 p_prorated_line := p_amount;
2386 p_prorated_tax := 0;
2387 END IF;
2388 IF (PG_DEBUG in ('Y','C')) THEN
2389 debug('receivables activity is not recoverable');
2390 debug('arp_etax_util.prorate_recoverable()-');
2391 END IF;
2392 RETURN;
2393 END IF;
2394
2395 /* Set header structure for calculate_tax call */
2396 l_transaction_rec.internal_organization_id := arp_global.sysparam.org_id;
2397 l_transaction_rec.application_id := 222;
2398 IF p_mode in ('APP_ED','APP_UED','UNAPP_ED','UNAPP_UED')
2399 THEN
2400 l_transaction_rec.trx_id := p_ra_app_id;
2401 ELSE
2402 l_transaction_rec.trx_id := p_adj_id;
2403 END IF;
2404
2405 /* Initialize line record to null */
2406 l_transaction_line_rec.internal_organization_id := NULL;
2407 l_transaction_line_rec.application_id := NULL;
2408 l_transaction_line_rec.entity_code := NULL;
2409 l_transaction_line_rec.event_class_code := NULL;
2410 l_transaction_line_rec.event_type_code := NULL;
2411 l_transaction_line_rec.trx_id := NULL;
2412 l_transaction_line_rec.trx_level_type := NULL;
2413 l_transaction_line_rec.trx_line_id := NULL;
2414
2415 /* Start of proration code */
2416 /* Get divisor for proration equation */
2417 /* Bug 8964860, Handled the condition for tax_recoverable and
2418 line_recoverable being NULL. */
2419 IF p_mode = 'TAX'
2420 THEN
2421 /* TAX only adjustment
2422 NOTE: This was not coded to handle line-level adjustments. */
2423 SELECT sum(least(tax_line.extended_amount, nvl(line.tax_recoverable, tax_line.extended_amount))),
2424 sum(nvl(line.tax_recoverable,0))
2425 INTO l_sum,
2426 l_total_tax_recov
2427 FROM ra_customer_trx_lines line,
2428 ra_customer_trx_lines tax_line
2429 WHERE line.customer_trx_id = p_target_id
2430 AND line.line_type = 'LINE'
2431 AND tax_line.link_to_cust_trx_line_id = line.customer_trx_line_id
2432 AND tax_line.line_type = 'TAX';
2433
2434 IF (PG_DEBUG in ('Y','C')) THEN
2435 debug('tax-only adjustment so divisor only considers tax');
2436 END IF;
2437
2438 ELSE
2439 /* Both LINE and TAX or discounts that prorate LINE and TAX */
2440 SELECT
2441 sum(least(tl.extended_amount,nvl(tl.line_recoverable, tl.extended_amount)) *
2442 (1 + nvl(tl.tax_recoverable, 0)/
2443 DECODE(tl.line_recoverable,0,1,NULL,1,
2444 tl.line_recoverable))),
2445 sum(nvl(tl.tax_recoverable,0))
2446 INTO l_sum,
2447 l_total_tax_recov
2448 FROM ra_customer_trx_lines tl
2449 WHERE tl.customer_trx_id = p_target_id
2450 AND tl.customer_trx_line_id =
2451 NVL(p_target_line_id, tl.customer_trx_line_id)
2452 AND tl.line_type = 'LINE';
2453
2454 END IF;
2455
2456 /* Bug 8512053: If the transaction is fully adjusted, then AR has to pass
2457 the balance tax amount as 'CTRL_TOTAL_HDR_TX_AMT' and
2458 CTRL_HDR_TX_APPL_FLAG as 'Y' to EBTAX */
2459 IF p_mode in ('LINE','TAX','INV')
2460 THEN
2461 SELECT sum(amount_due_remaining)
2462 INTO l_amt_due_remaining
2463 FROM ar_payment_schedules ps
2464 where ps.customer_trx_id = p_target_id
2465 group by ps.customer_trx_id;
2466
2467 IF (PG_DEBUG in ('Y','C')) THEN
2468 debug('remaining balance on the transaction = ' || l_amt_due_remaining);
2469 END IF;
2470
2471 IF l_amt_due_remaining = 0 THEN
2472 l_total_tax_recov := l_tax_adjusted;
2473 l_ctrl_hdr_tx_appl_flag := 'Y';
2474 END IF;
2475
2476 END IF;
2477
2478 IF (PG_DEBUG in ('Y','C')) THEN
2479 debug('sum (divisor) for proration calc = ' || l_sum);
2480 END IF;
2481
2482 /* Now iterate through the lines and figure the prorated amounts.
2483 Insert them into the ZX structure for processing */
2484 FOR c_tl in trx_lines(p_target_id, p_target_line_id) LOOP
2485
2486 IF (PG_DEBUG in ('Y','C')) THEN
2487 debug('processing trx_line_id ' || c_tl.inv_trx_line_id);
2488 debug(' extended_amount = ' || c_tl.line_amt);
2489 debug(' line_recoverable = ' || c_tl.line_recoverable);
2490 debug(' tax_recoverable = ' || c_tl.tax_recoverable);
2491 END IF;
2492
2493 /* calculate the prorated adjustment */
2494 IF p_mode = 'TAX'
2495 THEN
2496 /* In this case, the proration is based on tax only and will be
2497 allocated for tax. This sql uses link_to in order
2498 to find the tax lines for a given invoice line. */
2499 SELECT LEAST(nvl(c_tl.tax_recoverable, sum(extended_amount)), sum(extended_amount))
2500 INTO l_tax_amount
2501 FROM RA_CUSTOMER_TRX_LINES
2502 WHERE customer_trx_id = c_tl.inv_trx_id
2503 AND link_to_cust_trx_line_id = c_tl.inv_trx_line_id
2504 AND line_type = 'TAX';
2505
2506 l_line_proration := l_amount * (nvl(l_tax_amount,0)/l_sum);
2507
2508 ELSE
2509 /* In this case, the proration is line + tax and will eventually
2510 be allocated for line and tax separately */
2511 IF NVL(c_tl.line_recoverable,0) = 0
2512 THEN
2513 l_line_proration := 0;
2514 ELSE
2515 l_line_proration := (l_amount *
2516 LEAST(c_tl.line_amt, c_tl.line_recoverable) *
2517 (1 + c_tl.tax_recoverable / c_tl.line_recoverable))
2518 / l_sum;
2519 END IF;
2520 END IF;
2521
2522 /* Round l_line_proration amount to the currency (uses NEAREST) */
2523 IF c_tl.trx_precision is not null
2524 THEN
2525 l_line_proration := round(l_line_proration, c_tl.trx_precision);
2526 ELSE
2527 l_line_proration := (round(l_line_proration / c_tl.trx_mau)
2528 * c_tl.trx_mau);
2529 END IF;
2530
2531 /* Store the line proration in a simple table
2532 for later use (figuring recoverable) */
2533 t_prorated_amt(c_tl.inv_trx_line_id) := l_line_proration;
2534
2535 /* track what we have allocated so far,
2536 used to correct rounding at end */
2537 l_total_proration := l_total_proration + l_line_proration;
2538
2539 /* Identify a line for rounding corrections. This will
2540 note the largest positive or smallest negative line
2541 and assign rounding to that line. */
2542 l_row := l_row + 1;
2543 IF nvl(l_line_proration,0) <> 0 AND
2544 (l_round_target_amt <= l_line_proration AND
2545 sign(l_line_proration) = 1) OR
2546 (l_round_target_amt >= l_line_proration AND
2547 sign(l_line_proration) = -1)
2548 THEN
2549 l_round_target_amt := l_line_proration;
2550 l_round_target_line_id := l_row;
2551 END IF;
2552
2553 /* Initialize ZX tables */
2554 ZX_GLOBAL_STRUCTURES_PKG.INIT_TRX_LINE_DIST_TBL(l_row);
2555
2556 /* Set ZX table variables specific to adjustments */
2557 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.adjusted_doc_application_id(l_row) := 222;
2558 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.adjusted_doc_entity_code(l_row) := 'TRANSACTIONS';
2559 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.adjusted_doc_event_class_code(l_row):= c_tl.trx_event_class;
2560 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.adjusted_doc_trx_id(l_row) := c_tl.inv_trx_id;
2561 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.adjusted_doc_line_id(l_row) := c_tl.inv_trx_line_id;
2562 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.adjusted_doc_trx_level_type(l_row) := 'LINE';
2563 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.adjusted_doc_number(l_row) := c_tl.trx_number;
2564 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.adjusted_doc_date(l_row) := c_tl.inv_trx_date;
2565
2566 /* Set ZX tables for Tax only allocations */
2567 IF p_mode = 'TAX'
2568 THEN
2569 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.LINE_LEVEL_ACTION(l_row)
2570 := 'ALLOCATE_TAX_ONLY_ADJUSTMENT';
2571 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.CTRL_TOTAL_LINE_TX_AMT(l_row):= l_line_proration;
2572 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.LINE_AMT(l_row) := 0;
2573 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.LINE_AMT_INCLUDES_TAX_FLAG(l_row) := 'N';
2574 ELSE
2575 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.LINE_LEVEL_ACTION(l_row) := 'CREATE';
2576 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.LINE_AMT(l_row) := l_line_proration;
2577 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.LINE_AMT_INCLUDES_TAX_FLAG(l_row) := 'A';
2578 END IF;
2579
2580 /* Set ZX table variables from l_transaction_rec structure */
2581 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.INTERNAL_ORGANIZATION_ID(l_row) :=
2582 l_transaction_rec.internal_organization_id;
2583 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.APPLICATION_ID(l_row) :=
2584 l_transaction_rec.application_id;
2585 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.ENTITY_CODE(l_row) :=
2586 l_transaction_rec.entity_code;
2587 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.EVENT_CLASS_CODE(l_row) :=
2588 l_transaction_rec.event_class_code;
2589 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.EVENT_TYPE_CODE(l_row) :=
2590 l_transaction_rec.event_type_code;
2591
2592 /* Set line level variables from cursor or parameters */
2593 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.TRX_DATE(l_row) := p_apply_date;
2594 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.LEDGER_ID(l_row) := c_tl.ledger_id;
2595 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.TRX_CURRENCY_CODE(l_row) := c_tl.trx_currency_code;
2596 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.PRECISION(l_row) := c_tl.trx_precision;
2597 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.MINIMUM_ACCOUNTABLE_UNIT(l_row) := c_tl.trx_mau;
2598 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.CURRENCY_CONVERSION_DATE(l_row) := c_tl.exchange_date;
2599 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.CURRENCY_CONVERSION_RATE(l_row) := c_tl.exchange_rate;
2600 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.CURRENCY_CONVERSION_TYPE(l_row) := c_tl.exchange_rate_type;
2601 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.LEGAL_ENTITY_ID(l_row) := c_tl.legal_entity_id;
2602 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.CTRL_TOTAL_HDR_TX_AMT(l_row) := l_total_tax_recov;
2603 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.TRX_NUMBER(l_row) := l_adj_number;
2604 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.TRX_LINE_ID(l_row) :=
2605 c_tl.inv_trx_line_id;
2606
2607 /* 5677984 - applications use passed app_id, all others (adj)
2608 use customer_trx_line_id */
2609 IF p_mode in ('APP_ED', 'APP_UED', 'UNAPP_ED', 'UNAPP_UED')
2610 THEN
2611 l_adj_ra_id := p_ra_app_id;
2612 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.TRX_ID(l_row)
2613 := p_ra_app_id;
2614
2615 /* Set Applied_from columns */
2616 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.applied_from_application_id(l_row) := 222;
2617 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.applied_from_entity_code(l_row) := 'RECEIPTS';
2618 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.applied_from_event_class_code(l_row) := 'RECEIPTS';
2619 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.applied_from_trx_id(l_row) := p_adj_id; -- cash_receipt_id
2620 -- need to set line_id = LLCA.detail.line_id for LLCA
2621 -- ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.applied_from_line_id := NULL;
2622
2623 IF p_mode in ('APP_ED','UNAPP_ED')
2624 THEN
2625 /* Earned */
2626 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.TRX_LEVEL_TYPE(l_row)
2627 := 'LINE_EARNED';
2628 ELSE
2629 /* Unearned */
2630 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.TRX_LEVEL_TYPE(l_row)
2631 := 'LINE_UNEARNED';
2632 END IF;
2633 ELSE
2634 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.TRX_ID(l_row)
2635 := p_adj_id;
2636 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.TRX_LEVEL_TYPE(l_row)
2637 := 'LINE';
2638 l_adj_ra_id := p_adj_id;
2639 END IF;
2640
2641 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.PRODUCT_ID(l_row) := c_tl.inv_product_id;
2642 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.APPLIED_TO_TRX_NUMBER(l_row) := c_tl.trx_number;
2643 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.BILL_TO_PARTY_ID(l_row) := c_tl.bill_to_party_id;
2644 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.ROUNDING_BILL_TO_PARTY_ID(l_row) := c_tl.bill_to_party_id;
2645 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.BILL_TO_PARTY_SITE_ID(l_row) := c_tl.bill_to_party_site_id;
2646 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.RNDG_BILL_TO_PARTY_SITE_ID(l_row) := c_tl.bill_to_party_site_id;
2647 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.BILL_TO_LOCATION_ID(l_row) := c_tl.bill_to_location_id;
2648 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.HISTORICAL_FLAG(l_row) := c_tl.historical_flag;
2649 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.CTRL_HDR_TX_APPL_FLAG(l_row) := l_ctrl_hdr_tx_appl_flag;
2650 /* 5393508 - Do not populate trx_line_date
2651 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.TRX_LINE_DATE(l_row) :=
2652 p_apply_date;
2653 */
2654 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.EXEMPTION_CONTROL_FLAG(l_row):= c_tl.exemption_control_flag;
2655 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.EXEMPT_CERTIFICATE_NUMBER(l_row) := c_tl.exempt_certificate_number;
2656 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.EXEMPT_REASON_CODE(l_row) := c_tl.exempt_reason;
2657 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.SHIP_FROM_PARTY_ID(l_row) := c_tl.warehouse_id;
2658 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.SHIP_FROM_LOCATION_ID(l_row) := c_tl.ship_from_location_id;
2659 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.POA_LOCATION_ID(l_row) := c_tl.poa_location_id;
2660 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.POO_PARTY_ID(l_row) := c_tl.poo_party_id;
2661 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.POO_LOCATION_ID(l_row) := c_tl.poo_location_id;
2662
2663 /* Determine ship_to cust and site info */
2664 IF ( c_tl.line_ship_to_customer_id IS NOT NULL and
2665 c_tl.line_ship_to_site_use_id IS NOT NULL)
2666 THEN
2667 l_cust_id := c_tl.line_ship_to_customer_id;
2668 l_site_use_id := c_tl.line_ship_to_site_use_id;
2669 ELSIF ( c_tl.trx_ship_to_customer_id IS NOT NULL and
2670 c_tl.trx_ship_to_site_use_id IS NOT NULL)
2671 THEN
2672 l_cust_id := c_tl.trx_ship_to_customer_id;
2673 l_site_use_id := c_tl.trx_ship_to_site_use_id;
2674 ELSE
2675 l_cust_id := NULL;
2676 l_site_use_id := NULL;
2677
2678 END IF;
2679
2680 /* Fetch ship_to party info */
2681 IF (l_cust_id IS NOT NULL and l_site_use_id IS NOT NULL)
2682 THEN
2683
2684 SELECT
2685 CUST_ACCT.party_id,
2686 ACCT_SITE.party_site_id,
2687 PARTY_SITE.location_id
2688 INTO
2689 -- ship_to_party_id
2690 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.SHIP_TO_PARTY_ID(l_row),
2691 -- ship_to_party_site_id
2692 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.SHIP_TO_PARTY_SITE_ID(l_row),
2693 -- ship_to_location_id
2694 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.SHIP_TO_LOCATION_ID(l_row)
2695 FROM
2696 hz_cust_accounts CUST_ACCT,
2697 hz_parties PARTY,
2698 hz_cust_acct_sites ACCT_SITE,
2699 hz_cust_site_uses SITE_USES,
2700 hz_party_sites PARTY_SITE
2701 WHERE
2702 CUST_ACCT.cust_account_id = l_cust_id AND
2703 CUST_ACCT.party_id = PARTY.party_id AND
2704 CUST_ACCT.cust_account_id = ACCT_SITE.cust_account_id AND
2705 ACCT_SITE.cust_acct_site_id = SITE_USES.cust_acct_site_id AND
2706 SITE_USES.site_use_id = l_site_use_id and
2707 PARTY_SITE.party_site_id = ACCT_SITE.party_site_id;
2708
2709 END IF; /* end fetch */
2710
2711 END LOOP;
2712
2713 /* correct rounding if needed */
2714 IF l_total_proration <> l_amount
2715 THEN
2716 l_rounding_correction := l_amount - l_total_proration;
2717
2718 /* make sure we identified a line for rounding.. if not
2719 then use the last row processed */
2720 IF l_round_target_line_id IS NULL
2721 THEN
2722 l_round_target_line_id := l_row;
2723 END IF;
2724
2725 IF p_mode = 'TAX'
2726 THEN
2727 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.CTRL_TOTAL_LINE_TX_AMT(l_round_target_line_id):=
2728 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.CTRL_TOTAL_LINE_TX_AMT(l_round_target_line_id) + l_rounding_correction;
2729 ELSE
2730 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.LINE_AMT(l_round_target_line_id) :=
2731 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.LINE_AMT(l_round_target_line_id) +
2732 l_rounding_correction;
2733 END IF;
2734
2735 /* fix the line proration table, too */
2736 t_prorated_amt(ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.TRX_LINE_ID(l_round_target_line_id)) :=
2737 t_prorated_amt(ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.TRX_LINE_ID(l_round_target_line_id)) +
2738 l_rounding_correction;
2739 END IF;
2740
2741 /* insert det factors */
2742 ZX_API_PUB.insert_line_det_factors (
2743 p_api_version => 1.0,
2744 p_init_msg_list => FND_API.G_TRUE,
2745 p_commit => FND_API.G_FALSE,
2746 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
2747 x_return_status => l_return_status_service,
2748 x_msg_count => l_msg_count,
2749 x_msg_data => l_msg_data,
2750 p_duplicate_line_rec => l_transaction_line_rec);
2751
2752 /* calculate tax */
2753 zx_api_pub.calculate_tax(
2754 p_api_version => 1.0,
2755 p_init_msg_list => FND_API.G_TRUE,
2756 p_commit => FND_API.G_FALSE,
2757 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
2758 p_transaction_rec => l_transaction_rec,
2759 p_quote_flag => p_quote, -- quote
2760 p_data_transfer_mode => 'WIN',
2761 x_return_status => l_return_status_service,
2762 x_msg_count => l_msg_count,
2763 x_msg_data => l_msg_data,
2764 x_doc_level_recalc_flag => l_doc_level_recalc_flag );
2765
2766 /* When the API returns success, prorate the tax out and
2767 update the line and tax_recoverable columns */
2768 IF (l_return_status_service = 'S')
2769 THEN
2770
2771 /* initialize prorated tax and line amounts to zero */
2772 p_prorated_line := 0;
2773 p_prorated_tax := 0;
2774
2775 IF (p_quote = 'N')
2776 THEN
2777 /* Existing logic for actual tax calculations */
2778 /* Set out parameter totals and update LINES recoverable columns */
2779 FOR c_tax_lines IN tax_lines(l_transaction_rec.entity_code,
2780 l_transaction_rec.event_class_code,
2781 l_adj_ra_id,
2782 p_target_line_id,
2783 p_mode) LOOP
2784 l_lines_processed := TRUE;
2785
2786 /* switch sign of tax_amt (discounts are normally positive
2787 but pos is reflected as negative in etax) */
2788
2789 IF PG_DEBUG in ('Y', 'C') THEN
2790 arp_util.debug(c_tax_lines.adjusted_doc_line_id ||
2791 ' t_prorated_amt =' || t_prorated_amt(c_tax_lines.adjusted_doc_line_id) ||
2792 ' zx.tax =' || c_tax_lines.tax_amt);
2793 END IF;
2794
2795 l_prorated_tax := c_tax_lines.tax_amt;
2796
2797 l_prorated_line := t_prorated_amt(c_tax_lines.adjusted_doc_line_id)
2798 - l_prorated_tax;
2799
2800 UPDATE RA_CUSTOMER_TRX_LINES
2801 SET line_recoverable = line_recoverable + l_prorated_line,
2802 tax_recoverable = tax_recoverable + l_prorated_tax,
2803 last_updated_by = arp_standard.profile.user_id,
2804 last_update_date = sysdate
2805 WHERE customer_trx_line_id = c_tax_lines.adjusted_doc_line_id;
2806
2807 IF PG_DEBUG in ('Y', 'C') THEN
2808 arp_util.debug('before swap: ' || c_tax_lines.adjusted_doc_line_id ||
2809 ' line=' || l_prorated_line ||
2810 ' tax=' || l_prorated_tax);
2811 END IF;
2812
2813 IF p_mode in ('APP_ED', 'APP_UED')
2814 THEN
2815 /* for receipt applications, we switched the sign
2816 at the beginning.. now we have to switch it back */
2817 l_prorated_tax := l_prorated_tax * -1;
2818 l_prorated_line := l_prorated_line * -1;
2819
2820 /* This means that the returned values are in the same
2821 sign as the p_amount that was passed in now */
2822 END IF;
2823
2824 /* accumulate into the parameter columns */
2825 p_prorated_tax := p_prorated_tax + l_prorated_tax;
2826 p_prorated_line := p_prorated_line + l_prorated_line;
2827
2828 IF PG_DEBUG in ('Y', 'C') THEN
2829 arp_util.debug('after swap: ' || c_tax_lines.adjusted_doc_line_id ||
2830 ' line=' || l_prorated_line ||
2831 ' tax=' || l_prorated_tax);
2832 END IF;
2833
2834 END LOOP;
2835
2836 IF l_lines_processed = FALSE
2837 THEN
2838 arp_util.debug('initializing prorated_line to adj amt');
2839
2840 /* initalize the outbound parameters as LINE=amt, TAX=0
2841 In a situation where etax does nothing, then we should
2842 act as if there was no tax in our proration logic. */
2843 p_prorated_line := l_amount;
2844 p_prorated_tax := 0;
2845 END IF;
2846
2847 /* Update PS and ADJ records if required */
2848 IF NVL(p_upd_adj_and_ps, 'N') <> 'N'
2849 THEN
2850 update_adj_and_ps(p_upd_adj_and_ps,
2851 p_adj_id,
2852 p_prorated_line,
2853 p_prorated_tax);
2854 END IF;
2855
2856 /* Call line-level proration logic for accounting entries */
2857 -- Added for Line Level Adjustment ER
2858 IF p_target_line_id IS NOT NULL
2859 THEN
2860 l_from_llca_call := 'Y';
2861 ELSE
2862 l_from_llca_call := 'N';
2863 END IF;
2864 prorate_accounting(l_transaction_rec,
2865 p_mode,
2866 p_ra_app_id, -- isolates APP/UNAPP
2867 p_gt_id,
2868 l_from_llca_call,
2869 p_target_line_id);
2870
2871 ELSE
2872 IF PG_DEBUG in ('Y', 'C') THEN
2873 arp_util.debug(' prorate_recoverable called in quote mode');
2874 END IF;
2875
2876 /* p_quote = Y -- so this is quote logic */
2877 FOR c_tax_lines IN est_tax_lines(l_transaction_rec.entity_code,
2878 l_transaction_rec.event_class_code,
2879 l_transaction_rec.trx_id,
2880 l_adj_ra_id,
2881 p_mode) LOOP
2882
2883 l_lines_processed := TRUE;
2884
2885 /* switch sign of tax_amt (discounts are normally positive
2886 but pos is reflected as negative in etax) */
2887
2888 IF PG_DEBUG in ('Y', 'C') THEN
2889 arp_util.debug(c_tax_lines.adjusted_doc_line_id ||
2890 ' t_prorated_amt =' || t_prorated_amt(c_tax_lines.adjusted_doc_line_id) ||
2891 ' zx.tax =' || c_tax_lines.tax_amt);
2892 END IF;
2893
2894 l_prorated_tax := c_tax_lines.tax_amt;
2895
2896 l_prorated_line := t_prorated_amt(c_tax_lines.adjusted_doc_line_id)
2897 - l_prorated_tax;
2898
2899 IF p_mode in ('APP_ED', 'APP_UED', 'UNAPP_ED', 'UNAPP_UED')
2900 THEN
2901 /* for receipt applications, we switched the sign
2902 at the beginning.. now we have to switch it back */
2903 l_prorated_tax := l_prorated_tax * -1;
2904 l_prorated_line := l_prorated_line * -1;
2905
2906 /* This means that the returned values are in the same
2907 sign as the p_amount that was passed in now */
2908 END IF;
2909
2910 /* accumulate into the parameter columns */
2911 p_prorated_tax := p_prorated_tax + l_prorated_tax;
2912 p_prorated_line := p_prorated_line + l_prorated_line;
2913
2914 IF PG_DEBUG in ('Y', 'C') THEN
2915 debug(c_tax_lines.adjusted_doc_line_id ||
2916 ' line=' || l_prorated_line ||
2917 ' tax=' || l_prorated_tax);
2918 END IF;
2919
2920 END LOOP;
2921
2922 IF l_lines_processed = FALSE
2923 THEN
2924 debug('initializing prorated_line to adj amt');
2925
2926 /* initalize the outbound parameters as LINE=amt, TAX=0
2927 In a situation where etax does nothing, then we should
2928 act as if there was no tax in our proration logic. */
2929 p_prorated_line := l_amount;
2930 p_prorated_tax := 0;
2931 END IF;
2932
2933 END IF;
2934
2935 IF PG_DEBUG in ('Y','C')
2936 THEN
2937 debug('returned values');
2938 debug(' p_prorated_line = ' || p_prorated_line);
2939 debug(' p_prorated_tax = ' || p_prorated_tax);
2940 END IF;
2941
2942 ELSE
2943 /* When the API returns a failure, do something bad! */
2944 debug('EXCEPTION: Unable to calculate tax ');
2945
2946 p_prorated_tax := 0;
2947 RETURN;
2948 END IF;
2949
2950 IF PG_DEBUG in ('Y', 'C') THEN
2951 debug('arp_etax_util.prorate_recoverable()-');
2952 END IF;
2953
2954 END prorate_recoverable;
2955
2956 /* Public Procedure - adjusted line amounts for inclusive tax.
2957 can be used for individual transactions or batches.
2958
2959 This will adjust the extended_amount
2960 unit_selling_price
2961 gross_extended_amount
2962 gross_unit_selling_price
2963
2964 06-APR-2006 5146437 - fixed syntax error for inclusive tax logic
2965 05-OCT-2006 5487466 - Revised c_trx to handle removal of incl
2966 taxes and removal of all taxes from a transaction
2967
2968 28-MAR-2007 5942753 - Handle cases where line amount is zero
2969 and transaction is tax-only
2970 01-MAR-2011 11671073 - CODEFIX: THE TRANSACTION TOTAL DOUBLES
2971 IF INCLULSIVE TAX AMOUNT IS SAME AS LINE .
2972 */
2973
2974 PROCEDURE adjust_for_inclusive_tax(p_trx_id IN NUMBER,
2975 p_request_id IN NUMBER DEFAULT NULL,
2976 p_phase IN VARCHAR2 DEFAULT NULL)
2977 IS
2978
2979 l_new_extended_amount NUMBER;
2980 l_new_unit_selling_price NUMBER;
2981 l_base_currency FND_CURRENCIES.currency_code%type;
2982 l_base_precision FND_CURRENCIES.precision%type;
2983 l_base_mau FND_CURRENCIES.minimum_accountable_unit%type;
2984
2985 CURSOR c_trx(p_trx_id NUMBER) IS
2986 SELECT line.CUSTOMER_TRX_LINE_ID customer_trx_line_id,
2987 /* sum(decode(tax.amount_includes_tax_flag,
2988 'Y', decode(line.extended_amount, 0, 0,
2989 tax.extended_amount), 0)) inclusive_amount,*/
2990 sum(decode(tax.amount_includes_tax_flag,
2991 'Y', decode(nvl(line.gross_extended_amount, line.extended_amount), 0, 0,
2992 tax.extended_amount), 0)) inclusive_amount,--qiong fix bug :11671073
2993 header.invoice_currency_code currency_code,
2994 header.exchange_rate exchange_rate,
2995 currency.precision precision,
2996 currency.minimum_accountable_unit mau
2997 FROM RA_CUSTOMER_TRX header,
2998 FND_CURRENCIES currency,
2999 RA_CUSTOMER_TRX_LINES line,
3000 RA_CUSTOMER_TRX_LINES tax
3001 WHERE header.CUSTOMER_TRX_ID = p_trx_id
3002 AND line.CUSTOMER_TRX_ID = header.CUSTOMER_TRX_ID
3003 AND line.LINE_TYPE = 'LINE'
3004 AND tax.LINK_TO_CUST_TRX_LINE_ID = line.CUSTOMER_TRX_LINE_ID
3005 AND tax.LINE_TYPE = 'TAX'
3006 AND (tax.AMOUNT_INCLUDES_TAX_FLAG = 'Y' OR
3007 (nvl(tax.AMOUNT_INCLUDES_TAX_FLAG, 'N') = 'N' AND
3008 nvl(line.gross_extended_amount,0) <> 0 ))
3009 AND header.INVOICE_CURRENCY_CODE = currency.CURRENCY_CODE
3010 GROUP BY line.CUSTOMER_TRX_LINE_ID, header.INVOICE_CURRENCY_CODE,
3011 header.EXCHANGE_RATE, currency.PRECISION,
3012 currency.MINIMUM_ACCOUNTABLE_UNIT
3013 UNION -- following is for lines w/out tax
3014 SELECT line.CUSTOMER_TRX_LINE_ID customer_trx_line_id,
3015 0 inclusive_amount,
3016 header.invoice_currency_code currency_code,
3017 header.exchange_rate exchange_rate,
3018 currency.precision precision,
3019 currency.minimum_accountable_unit mau
3020 FROM RA_CUSTOMER_TRX header,
3021 FND_CURRENCIES currency,
3022 RA_CUSTOMER_TRX_LINES line
3023 WHERE header.CUSTOMER_TRX_ID = p_trx_id
3024 AND line.CUSTOMER_TRX_ID = header.CUSTOMER_TRX_ID
3025 AND line.LINE_TYPE = 'LINE'
3026 AND nvl(line.gross_extended_amount,0) <> 0
3027 AND header.INVOICE_CURRENCY_CODE = currency.CURRENCY_CODE
3028 AND NOT EXISTS
3029 (SELECT 'any tax line'
3030 FROM ra_customer_trx_lines tax
3031 WHERE tax.customer_trx_id = line.customer_trx_id
3032 AND tax.link_to_cust_trx_line_id =
3033 line.customer_trx_line_id
3034 AND tax.line_type = 'TAX');
3035
3036 CURSOR c_req(p_request_id NUMBER) IS
3037 SELECT /*+ index (line RA_CUSTOMER_TRX_LINES_N4) */
3038 line.CUSTOMER_TRX_LINE_ID customer_trx_line_id,
3039 sum(decode(line.extended_amount, 0, 0,
3040 tax.extended_amount)) inclusive_amount,
3041 header.invoice_currency_code currency_code,
3042 header.exchange_rate exchange_rate,
3043 currency.precision precision,
3044 currency.minimum_accountable_unit mau
3045 FROM RA_CUSTOMER_TRX header,
3046 FND_CURRENCIES currency,
3047 RA_CUSTOMER_TRX_LINES line,
3048 RA_CUSTOMER_TRX_LINES tax
3049 WHERE header.REQUEST_ID = p_request_id
3050 AND NVL(header.PREVIOUS_CUSTOMER_TRX_ID, -99) =
3051 DECODE(p_phase, 'CM', header.PREVIOUS_CUSTOMER_TRX_ID, -99)
3052 AND line.CUSTOMER_TRX_ID = header.CUSTOMER_TRX_ID
3053 AND line.LINE_TYPE = 'LINE'
3054 AND line.request_id = p_request_id -- 7039838
3055 AND tax.LINK_TO_CUST_TRX_LINE_ID = line.CUSTOMER_TRX_LINE_ID
3056 AND tax.LINE_TYPE = 'TAX'
3057 AND tax.AMOUNT_INCLUDES_TAX_FLAG = 'Y'
3058 AND tax.CUSTOMER_TRX_ID = line.CUSTOMER_TRX_ID
3059 AND header.INVOICE_CURRENCY_CODE = currency.CURRENCY_CODE
3060 GROUP BY line.CUSTOMER_TRX_LINE_ID, header.INVOICE_CURRENCY_CODE,
3061 header.EXCHANGE_RATE, currency.PRECISION, currency.MINIMUM_ACCOUNTABLE_UNIT;
3062
3063 BEGIN
3064 IF PG_DEBUG in ('Y', 'C')
3065 THEN
3066 debug('arp_etax_util.adjust_for_inclusive_tax()+');
3067 debug(' trx_id = ' || p_trx_id);
3068 debug(' req_id = ' || p_request_id);
3069 debug(' phase = ' || p_phase);
3070 END IF;
3071
3072 /* Initialize currency info */
3073 SELECT
3074 sob.currency_code,
3075 c.precision,
3076 c.minimum_accountable_unit
3077 INTO
3078 l_base_currency,
3079 l_base_precision,
3080 l_base_mau
3081 FROM gl_sets_of_books sob,
3082 fnd_currencies c,
3083 ar_system_parameters sp
3084 WHERE sob.set_of_books_id = sp.set_of_books_id
3085 AND sob.currency_code = c.currency_code;
3086
3087
3088 IF p_trx_id IS NOT NULL
3089 THEN
3090
3091 /* execute a loop for each line that has at least one
3092 inclusive tax calculated for it. Fetch the sum
3093 of the inclusive taxes and update the lines, sc, and dist
3094 for the reduction of that amount. */
3095
3096 FOR trx IN c_trx(p_trx_id) LOOP
3097
3098 IF PG_DEBUG IN ('C','Y')
3099 THEN
3100 debug(trx.customer_trx_line_id || ':' ||
3101 trx.inclusive_amount);
3102 END IF;
3103
3104 arp_ctl_pkg.update_amount_f_ctl_id(
3105 trx.customer_trx_line_id,
3106 trx.inclusive_amount,
3107 l_new_extended_amount,
3108 l_new_unit_selling_price,
3109 trx.precision, trx.mau);
3110
3111 arp_ctls_pkg.update_amounts_f_ctl_id(
3112 trx.customer_trx_line_id,
3113 l_new_extended_amount,
3114 trx.currency_code);
3115
3116 arp_ctlgd_pkg.update_amount_f_ctl_id(
3117 trx.customer_trx_line_id,
3118 l_new_extended_amount,
3119 trx.currency_code,
3120 l_base_currency,
3121 trx.exchange_rate,
3122 l_base_precision,
3123 l_base_mau);
3124
3125 END LOOP;
3126
3127 ELSIF p_request_id IS NOT NULL
3128 THEN
3129
3130 /* execute a loop for each line that has at least one
3131 inclusive tax calculated for it. Fetch the sum
3132 of the inclusive taxes and update the lines, sc, and dist
3133 for the reduction of that amount. */
3134
3135 FOR trx IN c_req(p_request_id) LOOP
3136
3137 IF PG_DEBUG IN ('C','Y')
3138 THEN
3139 debug(trx.customer_trx_line_id || ':' ||
3140 trx.inclusive_amount);
3141 END IF;
3142
3143 arp_ctl_pkg.update_amount_f_ctl_id(
3144 trx.customer_trx_line_id,
3145 trx.inclusive_amount,
3146 l_new_extended_amount,
3147 l_new_unit_selling_price,
3148 trx.precision, trx.mau);
3149
3150 arp_ctls_pkg.update_amounts_f_ctl_id(
3151 trx.customer_trx_line_id,
3152 l_new_extended_amount,
3153 trx.currency_code);
3154
3155 arp_ctlgd_pkg.update_amount_f_ctl_id(
3156 trx.customer_trx_line_id,
3157 l_new_extended_amount,
3158 trx.currency_code,
3159 l_base_currency,
3160 trx.exchange_rate,
3161 l_base_precision,
3162 l_base_mau);
3163
3164 END LOOP;
3165
3166
3167 END IF;
3168
3169 IF PG_DEBUG in ('Y', 'C')
3170 THEN
3171 debug('arp_etax_util.adjust_for_inclusive_tax()-');
3172 END IF;
3173 END adjust_for_inclusive_tax;
3174
3175 /* Public Procedure - sets recoverable columns for transaction or batch.
3176
3177 */
3178
3179 PROCEDURE set_recoverable(p_trx_id IN NUMBER,
3180 p_request_id IN NUMBER DEFAULT NULL,
3181 p_phase IN VARCHAR2 DEFAULT NULL)
3182 IS
3183
3184 BEGIN
3185 IF PG_DEBUG in ('Y', 'C')
3186 THEN
3187 debug('arp_etax_util.set_recoverable()+');
3188 debug(' trx_id = ' || p_trx_id);
3189 debug(' req_id = ' || p_request_id);
3190 debug(' phase = ' || p_phase);
3191 END IF;
3192
3193 IF p_trx_id IS NOT NULL
3194 THEN
3195
3196 UPDATE ra_customer_trx_lines mtl
3197 SET line_recoverable = extended_amount,
3198 tax_recoverable = (select sum(extended_amount)
3199 from ra_customer_trx_lines sqtl
3200 where sqtl.link_to_cust_trx_line_id =
3201 mtl.customer_trx_line_id
3202 and sqtl.customer_trx_id =
3203 mtl.customer_trx_id
3204 and sqtl.line_type = 'TAX')
3205 WHERE mtl.customer_trx_id = p_trx_id
3206 AND mtl.line_type = 'LINE';
3207
3208 ELSIF p_request_id IS NOT NULL
3209 THEN
3210
3211 /* mode logic is different here. if CM, then we need to join
3212 by previous_customer_trx_id, otherwise, NVL to -99. This allows this
3213 logic to be called for invoice copy (by request_id) but with phase not
3214 specified */
3215 UPDATE ra_customer_trx_lines mtl
3216 SET line_recoverable = extended_amount,
3217 tax_recoverable = (select sum(extended_amount)
3218 from ra_customer_trx_lines sqtl
3219 where sqtl.link_to_cust_trx_line_id =
3220 mtl.customer_trx_line_id
3221 and sqtl.customer_trx_id =
3222 mtl.customer_trx_id
3223 and sqtl.line_type = 'TAX')
3224 WHERE mtl.request_id = p_request_id
3225 AND NVL(mtl.previous_customer_trx_id, -99) =
3226 DECODE(p_phase, 'CM', mtl.previous_customer_trx_id, -99)
3227 AND mtl.line_type = 'LINE';
3228
3229 END IF;
3230
3231 IF PG_DEBUG in ('Y', 'C')
3232 THEN
3233 debug('arp_etax_util.set_recoverable()-');
3234 END IF;
3235 END set_recoverable;
3236
3237 /* public function get_tax_account
3238 Intended to call etax account procedure to fetch tax and interim
3239 accounts. Will cache accounts by customer_trx_line_id to prevent
3240 function from calling out to etax twice for each loop of
3241 autoaccounting sql.
3242
3243 Parameters:
3244 subject_id IN NUMBER autoaccounting (tax trx_line_id)
3245 adjustment (adj_id)...
3246 gl_date IN DATE date for acct validation
3247 desired_account IN VARCHAR2 TAX
3248 INTERIM
3249 ADJUSTMENT
3250 subject_table IN VARCHAR2 default TAX_LINE
3251 TAX_RATE
3252
3253 Called From Parameters Results
3254 ================= ========================= ===================================
3255 Autoaccounting 1. tax.customer_trx_line_id
3256 2. rec.gl_date
3257 3a. TAX Tax ccid
3258 3b. INTERIM Interim tax ccid
3259 ADJ, EDISC, UNEDISC, FINCHRG,
3260 ADJ_NON_REC, EDISC_NON_REC,
3261 UNEDISC_NON_REC, FINCHRG_NON_REC,
3262 4. Null or TAX_LINE
3263
3264
3265 Allocations 1. tax_rate_id
3266 2. sysdate or appropriate
3267 3. See above Accounts from zx_accounts
3268 based on tax_rate_id alone.
3269 4. TAX_RATE
3270
3271 DEV NOTE: The ZX routine currently does not return accounts for anything other
3272 than TAX and INTERIM
3273 */
3274
3275 FUNCTION get_tax_account(
3276 p_subject_id IN NUMBER,
3277 p_gl_date IN DATE,
3278 p_desired_account IN VARCHAR2,
3279 p_subject_table IN VARCHAR2 DEFAULT 'TAX_LINE')
3280 RETURN NUMBER
3281 IS
3282 l_location_segment_id NUMBER;
3283 l_org_id NUMBER;
3284 l_sob_id NUMBER;
3285 l_tax_line_id NUMBER;
3286 l_tax_rate_id NUMBER;
3287 l_tax_account_ccid NUMBER;
3288 l_interim_tax_ccid NUMBER;
3289 l_adj_ccid NUMBER;
3290 l_edisc_ccid NUMBER;
3291 l_unedisc_ccid NUMBER;
3292 l_finchrg_ccid NUMBER;
3293 l_adj_non_rec_tax_ccid NUMBER;
3294 l_edisc_non_rec_tax_ccid NUMBER;
3295 l_unedisc_non_rec_tax_ccid NUMBER;
3296 l_finchrg_non_rec_tax_ccid NUMBER;
3297 l_return_status VARCHAR2(128);
3298 l_gl_date DATE;
3299 BEGIN
3300 /* Debug +/
3301 debug('arp_etax_util.get_tax_account()+');
3302 debug(' p_subject_id = ' || p_subject_id);
3303 debug(' p_gl_date = ' || p_gl_date);
3304 debug(' p_desired = ' || p_desired_account);
3305 debug(' p_subject_tab= ' || p_subject_table);
3306 /+ end debug */
3307
3308 /* Process from cache or zx based on subject_table and
3309 cached ID */
3310 IF NVL(g_tax_customer_trx_line_id,-99) = p_subject_id AND
3311 p_subject_table = 'TAX_LINE'
3312 THEN
3313 /* we have already gone to etax so just return the desired account */
3314 NULL;
3315 ELSIF NVL(g_tax_rate_id, -99) = p_subject_id AND
3316 p_subject_table = 'TAX_RATE'
3317 THEN
3318 /* already got the ccids */
3319 NULL;
3320
3321 ELSE
3322 /* init return to prevent false returns */
3323 l_return_status := FND_API.G_RET_STS_SUCCESS;
3324
3325 IF p_subject_table = 'TAX_LINE'
3326 THEN
3327
3328 g_tax_customer_trx_line_id := p_subject_id;
3329 g_tax_rate_id := NULL; -- so we dont accidentally cross over
3330
3331 /* new line, get tax_line info and call etax */
3332 SELECT
3333 ar_tax.location_segment_id,
3334 ar_tax.org_id,
3335 ar_tax.tax_line_id,
3336 ar_tax.vat_tax_id,
3337 ar_tax.set_of_books_id,
3338 NVL(ar_rec.gl_date, TRUNC(sysdate))
3339 INTO
3340 l_location_segment_id,
3341 l_org_id,
3342 l_tax_line_id,
3343 l_tax_rate_id,
3344 l_sob_id,
3345 l_gl_date
3346 FROM
3347 ra_customer_trx_lines ar_tax,
3348 ra_cust_trx_line_gl_dist ar_rec
3349 WHERE
3350 ar_tax.customer_trx_line_id = p_subject_id
3351 AND ar_tax.customer_trx_id = ar_rec.customer_trx_id
3352 AND ar_rec.account_class = 'REC'
3353 AND ar_rec.latest_rec_flag = 'Y';
3354
3355 IF p_gl_date IS NOT NULL
3356 THEN
3357 l_gl_date := p_gl_date;
3358 END IF;
3359
3360 zx_trd_services_pub_pkg.get_output_tax_ccid(
3361 p_gl_date => l_gl_date,
3362 p_tax_rate_id => l_tax_rate_id,
3363 p_location_segment_id => l_location_segment_id,
3364 p_tax_line_id => l_tax_line_id,
3365 p_org_id => l_org_id,
3366 p_ledger_id => l_sob_id,
3367 p_event_class_code => null,
3368 p_entity_code => 'TRANSACTIONS',
3369 p_application_id => 222,
3370 p_document_id => to_number(null),
3371 p_document_line_id => to_number(null),
3372 p_trx_level_type => null,
3373 p_tax_account_ccid => l_tax_account_ccid,
3374 p_interim_tax_ccid => l_interim_tax_ccid,
3375 p_adj_ccid => l_adj_ccid,
3376 p_edisc_ccid => l_edisc_ccid,
3377 p_unedisc_ccid => l_unedisc_ccid,
3378 p_finchrg_ccid => l_finchrg_ccid,
3379 p_adj_non_rec_tax_ccid => l_adj_non_rec_tax_ccid,
3380 p_edisc_non_rec_tax_ccid => l_edisc_non_rec_tax_ccid,
3381 p_unedisc_non_rec_tax_ccid => l_unedisc_non_rec_tax_ccid,
3382 p_finchrg_non_rec_tax_ccid => l_finchrg_non_rec_tax_ccid,
3383 x_return_status => l_return_status);
3384
3385 ELSIF p_subject_table = 'TAX_RATE'
3386 THEN
3387 /* Limited call to ZX to get accounts for a tax rate */
3388 g_tax_customer_trx_line_id := NULL;
3389 g_tax_rate_id := p_subject_id;
3390
3391 /* 5599088 - pass org_id for TAX_RATE search */
3392 l_org_id := arp_global.sysparam.org_id;
3393 l_sob_id := arp_global.sysparam.set_of_books_id;
3394
3395 /* Insure that we have a date to use */
3396 IF p_gl_date IS NULL
3397 THEN
3398 l_gl_date := TRUNC(sysdate);
3399 ELSE
3400 l_gl_date := p_gl_date;
3401 END IF;
3402
3403 zx_trd_services_pub_pkg.get_output_tax_ccid(
3404 p_gl_date => l_gl_date,
3405 p_tax_rate_id => g_tax_rate_id,
3406 p_location_segment_id => null,
3407 p_tax_line_id => null,
3408 p_org_id => l_org_id,
3409 p_ledger_id => l_sob_id,
3410 p_event_class_code => null,
3411 p_entity_code => null,
3412 p_application_id => 222,
3413 p_document_id => null,
3414 p_document_line_id => null,
3415 p_trx_level_type => null,
3416 p_tax_account_ccid => l_tax_account_ccid,
3417 p_interim_tax_ccid => l_interim_tax_ccid,
3418 p_adj_ccid => l_adj_ccid,
3419 p_edisc_ccid => l_edisc_ccid,
3420 p_unedisc_ccid => l_unedisc_ccid,
3421 p_finchrg_ccid => l_finchrg_ccid,
3422 p_adj_non_rec_tax_ccid => l_adj_non_rec_tax_ccid,
3423 p_edisc_non_rec_tax_ccid => l_edisc_non_rec_tax_ccid,
3424 p_unedisc_non_rec_tax_ccid => l_unedisc_non_rec_tax_ccid,
3425 p_finchrg_non_rec_tax_ccid => l_finchrg_non_rec_tax_ccid,
3426 x_return_status => l_return_status);
3427
3428 ELSE
3429 debug('EXCEPTION: Unknown subject table ' || p_subject_Table);
3430 RETURN -1;
3431 END IF;
3432
3433 /* 4917065 - Moved return logic inside IF/ELSE */
3434 IF l_return_status = FND_API.G_RET_STS_SUCCESS
3435 THEN
3436 g_tax_account_ccid := nvl(l_tax_account_ccid, -1);
3437 g_interim_tax_ccid := nvl(l_interim_tax_ccid, -1);
3438 g_adj_ccid := nvl(l_adj_ccid,-1);
3439 g_edisc_ccid := nvl(l_edisc_ccid,-1);
3440 g_unedisc_ccid := nvl(l_unedisc_ccid,-1);
3441 g_finchrg_ccid := nvl(l_finchrg_ccid,-1);
3442 g_adj_non_rec_tax_ccid := nvl(l_adj_non_rec_tax_ccid,-1);
3443 g_edisc_non_rec_tax_ccid := nvl(l_edisc_non_rec_tax_ccid,-1);
3444 g_unedisc_non_rec_tax_ccid := nvl(l_unedisc_non_rec_tax_ccid,-1);
3445 g_finchrg_non_rec_tax_ccid := nvl(l_finchrg_non_rec_tax_ccid,-1);
3446 ELSE
3447 g_tax_account_ccid := -1;
3448 g_interim_tax_ccid := -1;
3449 g_adj_ccid := -1;
3450 g_edisc_ccid := -1;
3451 g_unedisc_ccid := -1;
3452 g_finchrg_ccid := -1;
3453 g_adj_non_rec_tax_ccid := -1;
3454 g_edisc_non_rec_tax_ccid := -1;
3455 g_unedisc_non_rec_tax_ccid := -1;
3456 g_finchrg_non_rec_tax_ccid := -1;
3457
3458 debug('EXCEPTION: get_output_tax_ccid returns error');
3459 RETURN -1;
3460 END IF;
3461
3462 END IF;
3463
3464 /* Debug +/
3465 debug('Returning ccids:');
3466 debug(' tax: ' || g_tax_account_ccid);
3467 debug(' interim: ' || g_interim_tax_ccid);
3468 debug(' adj: ' || g_adj_ccid);
3469 debug(' adj_non_rec: ' || g_adj_non_rec_tax_ccid);
3470 debug(' finchrg: ' || g_finchrg_ccid);
3471 debug(' finchrg_non_rec: ' || g_finchrg_non_rec_tax_ccid);
3472 debug(' edisc: ' || g_edisc_ccid);
3473 debug(' unedisc: ' || g_unedisc_ccid);
3474 debug(' edisc_non_rec: ' || g_edisc_non_rec_tax_ccid);
3475 debug(' unedisc_non_rec: ' || g_unedisc_non_rec_tax_ccid);
3476 /+ End debug */
3477
3478 /* Now return a value */
3479 IF p_desired_account = 'TAX'
3480 THEN
3481 RETURN g_tax_account_ccid;
3482 ELSIF p_desired_account = 'INTERIM'
3483 THEN
3484 RETURN g_interim_tax_ccid;
3485 ELSIF p_desired_account = 'ADJ'
3486 THEN
3487 RETURN g_adj_ccid;
3488 ELSIF p_desired_account = 'ADJ_NON_REC'
3489 THEN
3490 RETURN g_adj_non_rec_tax_ccid;
3491 ELSIF p_desired_account = 'FINCHRG'
3492 THEN
3493 RETURN g_finchrg_ccid;
3494 ELSIF p_desired_account = 'FINCHRG_NON_REC'
3495 THEN
3496 RETURN g_finchrg_non_rec_tax_ccid;
3497 ELSIF p_desired_account = 'EDISC'
3498 THEN
3499 RETURN g_edisc_ccid;
3500 ELSIF p_desired_account = 'UNEDISC'
3501 THEN
3502 RETURN g_unedisc_ccid;
3503 ELSIF p_desired_account = 'EDISC_NON_REC'
3504 THEN
3505 RETURN g_edisc_non_rec_tax_ccid;
3506 ELSIF p_desired_account = 'UNEDISC_NON_REC'
3507 THEN
3508 RETURN g_unedisc_non_rec_tax_ccid;
3509 ELSE
3510 /* no idea what they want */
3511 debug('EXCEPTION: Invalid desired account = ' || p_desired_account);
3512 RETURN -1;
3513 END IF;
3514
3515 END get_tax_account;
3516
3517 /* PUBLIC PROCEDURE calc_applied_and_remaining
3518
3519 Implemented here as a wrapper for ARP_APP_CALC_PKG version. In
3520 lockbox/cash, we need to call this routine to prorate discount and determine
3521 if the tax portion is recoverable. When it is recoverable, we have to
3522 call etax to calculate the tax amount, then remove it from the line
3523 amount.
3524
3525 If the transaction is not recoverable, then the etax code will not
3526 be called and this routine will behave exactly as the original.
3527
3528 p_mode currently only supports APP_ED and APP_UED
3529
3530 p_rec_app_id - takes and/or returns receivable_application_id
3531 this is used by the etax calculate calls.
3532 */
3533
3534 PROCEDURE calc_applied_and_remaining ( p_amt in number
3535 ,p_receipt_id in number
3536 ,p_apply_date in date
3537 ,p_trx_id in number
3538 ,p_mode in varchar2
3539 ,p_rule_set_id number
3540 ,p_currency in varchar2
3541 ,p_line_remaining in out NOCOPY number
3542 ,p_line_tax_remaining in out NOCOPY number
3543 ,p_freight_remaining in out NOCOPY number
3544 ,p_charges_remaining in out NOCOPY number
3545 ,p_line_applied out NOCOPY number
3546 ,p_line_tax_applied out NOCOPY number
3547 ,p_freight_applied out NOCOPY number
3548 ,p_charges_applied out NOCOPY number
3549 ,p_rec_app_id in out NOCOPY number)
3550 IS
3551 l_tax_recov VARCHAR2(1);
3552 l_rec_act_id NUMBER;
3553 l_line_applied NUMBER;
3554 l_line_tax_applied NUMBER;
3555 l_amt NUMBER;
3556
3557 l_line_applied_orig NUMBER;
3558 l_tax_applied_orig NUMBER;
3559
3560 l_gt_id NUMBER;
3561 BEGIN
3562 IF PG_DEBUG in ('Y', 'C')
3563 THEN
3564 debug('arp_etax_util.calc_applied_and_remaining()+');
3565 debug(' p_amt = ' || p_amt);
3566 debug(' p_receipt_id = ' || p_receipt_id);
3567 debug(' p_apply_date = ' || p_apply_date);
3568 debug(' p_trx_id = ' || p_trx_id);
3569 debug(' p_mode = ' || p_mode);
3570 debug(' p_rule_set_id = ' || p_rule_set_id);
3571 debug(' p_currency = ' || p_currency);
3572 debug(' p_line_remaining = ' || p_line_remaining);
3573 debug(' p_line_tax_remaining = ' || p_line_tax_remaining);
3574 debug(' p_rec_app_id = ' || p_rec_app_id);
3575 END IF;
3576 IF p_receipt_id IS NOT NULL
3577 THEN
3578
3579 /* Determine if the discount is recoverable */
3580 --Included remit_bank_acct_use_id condition for bug 6955088
3581 SELECT NVL(rt.tax_recoverable_flag, 'N'),
3582 rt.receivables_trx_id
3583 INTO l_tax_recov,
3584 l_rec_act_id
3585 FROM ar_cash_receipts cr,
3586 ar_receipt_method_accounts arm,
3587 ar_receivables_trx rt
3588 WHERE cr.cash_receipt_id = p_receipt_id
3589 AND cr.receipt_method_id = arm.receipt_method_id
3590 AND cr.remit_bank_acct_use_id = arm.remit_bank_acct_use_id
3591 AND DECODE(p_mode,
3592 'APP_ED',arm.edisc_receivables_trx_id,
3593 'APP_UED',arm.unedisc_receivables_trx_id) =
3594 rt.receivables_trx_id (+);
3595 ELSE
3596 l_tax_recov := 'N';
3597 END IF;
3598
3599 /* Call original calc_applied_and_remaining */
3600
3601 ARP_APP_CALC_PKG.calc_applied_and_remaining(
3602 p_amt,
3603 p_rule_set_id,
3604 p_currency,
3605 p_line_remaining,
3606 p_line_tax_remaining,
3607 p_freight_remaining,
3608 p_charges_remaining,
3609 p_line_applied,
3610 p_line_tax_applied,
3611 p_freight_applied,
3612 p_charges_applied);
3613
3614 IF PG_DEBUG in ('Y', 'C')
3615 THEN
3616 debug(' returned from arp_app_calc_pkg.calc_applied_and_remaining');
3617 END IF;
3618
3619 IF l_tax_recov = 'Y'
3620 THEN
3621 IF PG_DEBUG in ('Y', 'C')
3622 THEN
3623 debug(' tax is recoverable ');
3624 END IF;
3625 /* Deduction activity is recoverable. Put the
3626 original tax amount back into the line bucket
3627 and call etax to prorate it */
3628
3629 /* Put the line and tax applied back into remaining */
3630 p_line_tax_remaining := p_line_tax_remaining +
3631 NVL(p_line_tax_applied,0);
3632
3633 p_line_remaining := p_line_remaining +
3634 NVL(p_line_applied,0);
3635
3636 /* Get total applied (the discount) for use in
3637 prorate_recoverable */
3638 l_amt := NVL(p_line_applied, 0) + NVL(p_line_tax_applied,0);
3639
3640 prorate_recoverable(p_receipt_id,
3641 p_trx_id,
3642 NULL,
3643 l_amt,
3644 p_apply_date,
3645 p_mode,
3646 'N',
3647 g_gt_id,
3648 l_line_applied,
3649 l_line_tax_applied,
3650 'N',
3651 p_rec_app_id);
3652
3653 IF PG_DEBUG in ('Y', 'C')
3654 THEN
3655 debug(' g_gt_id = ' || g_gt_id);
3656 debug(' p_rec_app_id = ' || p_rec_app_id);
3657 END IF;
3658
3659 IF nvl(g_gt_id,0) <> 0
3660 THEN
3661 p_line_applied := l_line_applied;
3662 p_line_tax_applied := l_line_tax_applied;
3663 p_line_remaining := p_line_remaining - p_line_applied;
3664 p_line_tax_remaining := p_line_tax_remaining - p_line_tax_applied;
3665
3666 IF PG_DEBUG in ('Y', 'C')
3667 THEN
3668 debug(' tax now prorated ');
3669 debug(' line_applied (post etax) = ' ||
3670 l_line_applied);
3671 debug(' tax_applied (post etax) = ' ||
3672 l_line_tax_applied);
3673 debug(' line_remaining = ' || p_line_remaining);
3674 debug(' tax_remaining = ' ||
3675 p_line_tax_remaining);
3676 END IF;
3677 END IF;
3678 ELSE
3679 IF PG_DEBUG in ('Y', 'C')
3680 THEN
3681 debug(' Non-recoverable activity (' || l_rec_act_id ||
3682 ') etax skipped');
3683 END IF;
3684
3685 END IF;
3686
3687 IF PG_DEBUG in ('Y', 'C')
3688 THEN
3689 debug('arp_etax_util.calc_applied_and_remaining()-');
3690 END IF;
3691 END calc_applied_and_remaining;
3692
3693 /* PUBLIC FUNCTION get_discount_rate
3694 returns max(percentage) from ra_terms_lines_discounts
3695 for use in transaction tax calculations. Note that the
3696 discount is /100 to make it ready for direct use in
3697 calculations.
3698 */
3699
3700 FUNCTION get_discount_rate (p_trx_id IN NUMBER)
3701 RETURN NUMBER IS
3702 BEGIN
3703
3704 IF nvl(g_trx_id_for_disc, -99) = p_trx_id
3705 THEN
3706 RETURN g_rate_for_disc;
3707 ELSE
3708 g_trx_id_for_disc := p_trx_id;
3709
3710 select max(nvl(tld.discount_percent/100,0))
3711 into g_rate_for_disc
3712 from ra_terms_lines_discounts tld,
3713 ra_customer_trx trx
3714 where trx.customer_trx_id = p_trx_id
3715 and trx.term_id = tld.term_id (+);
3716
3717 RETURN g_rate_for_disc;
3718 END IF;
3719
3720 RETURN 0;
3721
3722 END get_discount_rate;
3723
3724 /*=============================================================================
3725 | PROCEDURE - validate_for_tax
3726 |
3727 | DESCRIPTION
3728 | This routine calls etax API validate_document_for_tax to insure
3729 | that the tax, rate, status, juris, and regime are still valid
3730 | at the time of completion.
3731 |
3732 |
3733 | NOTE: This was intended specifically for calls from
3734 | arp_trx_complete_chk package for forms issues.
3735 | PARAMETERS
3736 | p_request_id NUMBER (customer_trx_id of target transaction)
3737 |
3738 |
3739 | MODIFICATION HISTORY
3740 | DATE Author Description of Changes
3741 | 11-JUL-2006 M Raymond Created
3742 |
3743 *===========================================================================*/
3744
3745 PROCEDURE validate_for_tax (p_request_id IN NUMBER) IS
3746
3747 l_return_status VARCHAR2(50) := FND_API.G_RET_STS_SUCCESS;
3748 l_msg_count NUMBER;
3749 l_msg_data VARCHAR2(2000);
3750 -- l_trx_rec ZX_API_PUB.transaction_rec_type;
3751 -- l_validation_status VARCHAR2(1);
3752 -- l_hold_codes_tbl ZX_API_PUB.hold_codes_tbl_type;
3753 -- l_error_count NUMBER;
3754 -- l_trx_number RA_CUSTOMER_TRX.trx_number%type;
3755 l_msg VARCHAR2(2000);
3756
3757 /* CURSOR c_errors IS
3758 select trx_id, trx_line_id, message_name, message_text
3759 from zx_validation_errors_gt
3760 where application_id = l_trx_rec.application_id
3761 and entity_code = l_trx_rec.entity_code
3762 and event_class_code = l_trx_rec.event_class_code
3763 and trx_id = l_trx_rec.trx_id;
3764 */
3765 BEGIN
3766 IF PG_DEBUG in ('Y', 'C')
3767 THEN
3768 debug('arp_etax_util.validate_for_tax()+');
3769 END IF;
3770
3771
3772 DELETE from ZX_TRX_HEADERS_GT zx
3773 WHERE application_id = 222
3774 AND entity_code = 'TRANSACTIONS'
3775 AND (trx_id, event_class_code) IN
3776 (SELECT trx.customer_trx_id, decode(t.type, 'INV', 'INVOICE',
3777 'CM', 'CREDIT_MEMO', 'DM','DEBIT_MEMO')
3778 FROM ra_customer_trx trx, ra_cust_trx_types t
3779 WHERE trx.request_id = p_request_id
3780 AND trx.complete_flag = 'N'
3781 AND trx.cust_trx_type_id = t.cust_trx_type_id
3782 AND trx.org_id = t.org_id);
3783
3784
3785 IF PG_DEBUG in ('Y', 'C') THEN
3786 debug('before calling etax bulk processing api ');
3787 END IF;
3788 zx_api_pub.validate_document_for_tax(
3789 p_api_version => 1.0,
3790 p_init_msg_list => FND_API.G_TRUE,
3791 p_commit => FND_API.G_FALSE,
3792 p_validation_level => NULL,
3793 x_return_status => l_return_status,
3794 x_msg_count => l_msg_count,
3795 x_msg_data => l_msg_data);
3796 IF PG_DEBUG in ('Y', 'C') THEN
3797 debug('after calling etax bulk processing api');
3798 END IF;
3799
3800 IF l_return_status <> FND_API.G_RET_STS_SUCCESS
3801 THEN
3802 /* Retrieve and log errors */
3803 IF l_msg_count = 1
3804 THEN
3805 debug(l_msg_data);
3806 ELSIF l_msg_count > 1
3807 THEN
3808 LOOP
3809 l_msg := FND_MSG_PUB.Get(FND_MSG_PUB.G_NEXT,
3810 FND_API.G_FALSE);
3811 IF l_msg IS NULL
3812 THEN
3813 EXIT;
3814 ELSE
3815 debug(l_msg);
3816 END IF;
3817 END LOOP;
3818 END IF;
3819 END IF;
3820
3821 IF PG_DEBUG in ('Y', 'C')
3822 THEN
3823 debug('arp_etax_util.validate_for_tax()-');
3824 END IF;
3825
3826 END validate_for_tax;
3827
3828
3829 /*========================================================================
3830 | INITIALIZATION SECTION
3831 |
3832 | DESCRIPTION
3833 | Initialized global variables for controlling program flow
3834 |
3835 | KNOWN ISSUES
3836 |
3837 | NOTES
3838 |
3839 | MODIFICATION HISTORY
3840 | Date Author Description of Changes
3841 | 28-FEB-2005 MRAYMOND Created
3842 | 10-SEP-2008 MRAYMOND 7329586 - Removed schema logic
3843 *=======================================================================*/
3844
3845 BEGIN
3846 NULL;
3847 END ARP_ETAX_UTIL;
[Home] [Help]