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