[Home] [Help]
PACKAGE BODY: APPS.JAI_AR_SUP_TRX_PKG
Source
1 PACKAGE BODY jai_ar_sup_trx_pkg AS
2 /* $Header: jai_ar_sup_trx.plb 120.38.12020000.3 2013/04/05 10:34:23 mmurtuza ship $ */
3
4 /* --------------------------------------------------------------------------------------
5 Filename:
6
7 Change History:
8
9 Date Bug Remarks
10 --------- ---------- -------------------------------------------------------------
11 08-Jun-2005 File Version 116.3. Object is Modified to refer to New DB Entity names in place of Old DB Entity Names
12 as required for CASE COMPLAINCE.
13
14 13-Jun-2005 File Version: 116.4
15 Ramananda for bug#4428980. Removal of SQL LITERALs is done
16
17 18-JUL-2005 Ramananda for bug# 4499078, File Version # 120.2
18 Sequence JAI_AR_SUP_HDRS_ALL_S1 is used instead of JAI_AR_SUP_HDRS_ALL_S2 and JAI_AR_SUP_HDRS_ALL_S3
19
20 26-JUL-2005 Ramananda for bug#4499078, File Version # 120.3
21 Added column Interface_Line_Attribute2 column in Cursor GET_SUPP_INV_LINES
22
23 07/12/2005 Hjujjuru for the bug 4866533 File version 120.3
24 added the who columns in the insert commands for the tables
25 JAI_AR_SUP_INV_T and JAI_AR_TRX_SUP_INV_T.
26 Dependencies Due to this bug:-
27 None
28
29 27-Dec-2005 Bug 4906958. Added by Lakshmi Gopalsami Version 120.6
30 Derived the value for default LE if the value is not retrieved via
31 default BSV.
32
33 07-Mar-2006 Modified by aiyer for the bug 4947681, File version 120.7
34 Issue :-
35 Performance tuning for the cursor get_invoices SQL_ID 14827611. Cost = 1287 and sharabale memory 182519
36
37
38 Fix :-
39 Modified the cursor get_invoices. Added the IL table jai_ar_trxs to the ra_customer_trx_all
40 and also merged the correlated query to the main query thus reducing the sharable memory also
41
42
43 01/11/2006 SACSETHI for bug 5228046, File version 120.10
44
45 1. Forward porting the change in 11i bug 5365523 (Additional CVD Enhancement).
46 This bug has datamodel and spec changes.
47
48 2. Forward porting of 11i bug 5219225
49
50
51 Dependency Due to This bug :-
52 None
53
54 10/09/2007 Bgowrava for Bug#6400997, File Version 120.9.12000000.3
55 Debug messages added to apply on the instance and test for errors.
56
57
58
59 28-Jan-2011 bug 10358786 shujuan Yan Modified
60 Inclusive Tax
61
62
63 11-May-2011 Bug#12537533 zhiwei Hou modified
64 The transaction from Open Interface with External event should not be picked up
65 while supplementary transaction generation.
66
67 20-Jan-2011 bug 12844458 mmurtuza
68 Description: INCORRECT TAX CALCULATION FOR SUPPLEMENTARY INVOICES
69 Fix: For the varibales v_tax_amt, vamt removed precision of (10,2). The declaration is just as NUMBER.
70
71 -----------------------------------------------------------------------------------------*/
72
73 PROCEDURE process_report_stpr(p_batch_id number) is
74 BEGIN
75 jai_ar_sup_trx_pkg.identify_invoices(p_batch_id);
76 jai_ar_sup_trx_pkg.process_invoices;
77
78 END process_report_stpr;
79
80 PROCEDURE identify_invoices(P_BATCH_ID NUMBER)
81 IS
82 N NUMBER;
83 O NUMBER;
84 K NUMBER;
85 V_LIST_CURRENCY VARCHAR2(15);
86 V_CURRENCY_MATCH VARCHAR2(1) ;
87 check_flag varchar2(1) ;
88
89 -- added, Harshita for Bug 4762433
90 ld_creation_date DATE;
91 ln_created_by NUMBER;
92
93 /* Added by Ramananda for bug#4407165 */
94 lv_object_name CONSTANT VARCHAR2(61) := 'jai_ar_sup_trx_pkg.identify_invoices';
95
96 CURSOR CH(N NUMBER,M VARCHAR2) IS SELECT 1 FROM JAI_AR_TRX_SUP_INV_T WHERE CUSTOMER_TRX_ID = N
97 AND MAPPING_TYPE = M;
98
99 Cursor ch_tax(id number,tax_name number) is select 1 from JAI_AR_TRX_SUP_INV_T where customer_trx_id = id
100 and mapping_type = 'T'
101 and existing_type_id = tax_name;
102
103 -- CURSOR FOR SELECTING ENTRIES FROM THE MASTER TABLE JAI_AR_SUP_CHANGES_ALL WHOSE PROCESS DATE IS NOT NULL --
104 CURSOR MAP_IDENT IS
105 SELECT SET_OF_BOOKS_ID,
106 ORG_ID,
107 CUSTOMER_ID,
108 SITE_USE_ID,
109 EXISTING_TYPE_ID,
110 MAPPING_ID,
111 MAPPING_TYPE,
112 NEW_TYPE_ID,
113 EFFECTIVE_FROM_DATE,
114 EFFECTIVE_TO_DATE,
115 CONVERSION_RATE,
116 BATCH_ID
117 FROM JAI_AR_SUP_CHANGES_ALL
118 WHERE PROCESS_DATE IS NULL
119 AND BATCH_ID = NVL(P_BATCH_ID,BATCH_ID)
120 ORDER BY SITE_USE_ID,
121 CUSTOMER_ID,
122 ORG_ID,
123 SET_OF_BOOKS_ID;
124
125 CURSOR GET_LIST_CURRENCY(P_LIST_ID NUMBER) IS
126 SELECT CURRENCY_CODE
127 FROM QP_LIST_HEADERS_B
128 WHERE LIST_HEADER_ID = P_LIST_ID;
129
130 -- TILL HERE
131
132 /*
133 || Cursor modified by aiyer for the bug 4947681.SQL ID 14827611
134 || Added the IL table jai_ar_trxs to the ra_customer_trx_all and
135 || also merged the correlated query to the main query thus reducing the sharable memory also
136 || New Cost = 28 Sharable memory = 113499
137 */
138 CURSOR GET_INVOICE ( effective_from_date DATE ,
139 p_effective_to_date DATE ,
140 cp_int_hdr_cxt RA_CUSTOMER_TRX_ALL.INTERFACE_HEADER_CONTEXT%TYPE
141 )
142 IS
143 SELECT
144 rcta.customer_trx_id,
145 rcta.trx_date,
146 rcta.set_of_books_id,
147 rcta.bill_to_customer_id,
148 rcta.bill_to_site_use_id,
149 rcta.org_id,
150 rcta.interface_header_context ,
151 rcta.interface_header_attribute1,
152 rcta.interface_header_attribute6,
153 rcta.interface_header_attribute7,
154 rcta.invoice_currency_code
155 FROM ra_customer_trx_all rcta,
156 jai_ar_trxs jat ,
157 ra_cust_trx_types_all rctta
158 WHERE
159 rcta.customer_trx_id = jat.customer_trx_id
160 AND rcta.cust_trx_type_id = rctta.cust_trx_type_id
161 AND rctta.type = 'INV'
162 AND trunc(rcta.trx_date) between trunc(effective_from_date) AND trunc(nvl(p_effective_to_date,sysdate))
163 --Added by zhiwei for Bug#12537533 on 20110511 begin
164 -----------------------------------------------------------
165 AND NOT EXISTS
166 (
167 SELECT 1
168 FROM JAI_AR_TRX_LINES
169 WHERE CUSTOMER_TRX_ID = rcta.customer_trx_id
170 AND INTERFACE_FLAG = 'Y'
171 AND UPPER(INTERFACE_EVENT) = 'EXTERNAL'
172 )
173 -----------------------------------------------------------
174 --Added by zhiwei for Bug#12537533 on 20110511 end
175 AND ( rcta.interface_header_context IS NULL
176 OR rcta.interface_header_context = cp_int_hdr_cxt
177 --Added by zhiwei for Bug#12615714 begin
178 ---------------------------------------------------------------------
179 --or rcta.interface_header_context = 'OFI TAX IMPORT'--Added by zhiwei for Bug#12615714 on 20110629
180 or exists(
181 select 1
182 from ra_customer_trx_lines_all
183 where customer_trx_id = rcta.customer_trx_id
184 and nvl( global_attribute_category,'###') = 'JG.IN.ARXTWMAI.OFI TAX IMPORT'
185 )
186 ---------------------------------------------------------------------
187 --Added by zhiwei for Bug#12615714 end
188 );
189
190
191 -- CURSOR FOR CHECKING WHETHER AN INVOICE HAS AN EXISTING ENTRY --
192
193 CURSOR CHK_FOR_SUPP_INV_ENT(ID NUMBER) IS
194 SELECT 1
195 FROM JAI_AR_SUP_HDRS_ALL
196 WHERE CUSTOMER_TRX_ID = ID ;
197
198 -- CURSOR FOR SELECTING INVOICE FILTERED BY EFFECTIVE DATE AND TYPE --
199
200 CURSOR SELECT_FILTER_INV IS
201 SELECT * FROM JAI_AR_SUP_INV_T;
202
203 /*
204 CURSOR COUNT_COMBINTIONS IS
205 SELECT SET_OF_BOOKS_ID,
206 ORG_ID,
207 CUSTOMER_ID,
208 SITE_USE_ID
209 FROM JAI_AR_SUP_CHANGES_ALL; */
210
211
212 ln_open_api number;--Added by zhiwei for Bug#12615714
213 BEGIN
214
215 -- added, Harshita for Bug 4762433
216 ld_creation_date := SYSDATE;
217 ln_created_by := FND_GLOBAL.user_id;
218
219 FOR MAIN IN MAP_IDENT
220 LOOP
221
222 FOR ONE IN GET_INVOICE(MAIN.EFFECTIVE_FROM_DATE,MAIN.EFFECTIVE_TO_DATE, 'ORDER ENTRY') /* Modified by Ramananda for removal of SQL LITERALs :bug#4428980*/
223
224 LOOP
225
226 OPEN CHK_FOR_SUPP_INV_ENT(ONE.CUSTOMER_TRX_ID);
227 FETCH CHK_FOR_SUPP_INV_ENT INTO N;
228 FETCH CHK_FOR_SUPP_INV_ENT INTO N;
229
230
231 IF CHK_FOR_SUPP_INV_ENT%FOUND THEN
232 NULL;
233 ELSIF CHK_FOR_SUPP_INV_ENT%NOTFOUND THEN
234
235
236
237 IF MAIN.MAPPING_TYPE IN ('I','P','A') THEN
238
239 OPEN GET_LIST_CURRENCY(MAIN.NEW_TYPE_ID);
240 FETCH GET_LIST_CURRENCY INTO V_LIST_CURRENCY;
241 CLOSE GET_LIST_CURRENCY;
242
243 IF V_LIST_CURRENCY <> ONE.INVOICE_CURRENCY_CODE THEN
244
245 V_CURRENCY_MATCH := 'N' ;
246
247 ELSIF V_LIST_CURRENCY = ONE.INVOICE_CURRENCY_CODE THEN
248
249 V_CURRENCY_MATCH := 'Y';
250 END IF;
251 ELSIF MAIN.MAPPING_TYPE = 'T' THEN
252
253 V_CURRENCY_MATCH := 'Y';
254 END IF;
255
256
257
258 IF V_CURRENCY_MATCH = 'Y' THEN
259
260 INSERT INTO JAI_AR_SUP_INV_T
261 (CUSTOMER_TRX_ID,
262 INTERFACE_HEADER_CONTEXT,
263 INTERFACE_HEADER_ATTRIBUTE1,
264 INTERFACE_HEADER_ATTRIBUTE6,
265 INTERFACE_HEADER_ATTRIBUTE7,
266 MAPPING_ID,
267 TRX_DATE,
268 EFFECTIVE_DATE,
269 SET_OF_BOOKS_ID,
270 CUSTOMER_ID,
271 ORG_ID,
272 CUSTOMER_SITE,
273 -- added, Harshita for Bug 4866533
274 CREATED_BY,
275 CREATION_DATE,
276 LAST_UPDATED_BY,
277 LAST_UPDATE_DATE
278 )
279 VALUES(ONE.CUSTOMER_TRX_ID,
280 ONE.INTERFACE_HEADER_CONTEXT ,
281 ONE.INTERFACE_HEADER_ATTRIBUTE1,
282 ONE.INTERFACE_HEADER_ATTRIBUTE6 ,
283 ONE.INTERFACE_HEADER_ATTRIBUTE7,
284 MAIN.MAPPING_ID,
285 ONE.TRX_DATE,
286 MAIN.EFFECTIVE_FROM_DATE,
287 ONE.SET_OF_BOOKS_ID,
288 ONE.BILL_TO_CUSTOMER_ID,
289 NVL(ONE.ORG_ID,0),
290 ONE.BILL_TO_SITE_USE_ID,
291 -- added, Harshita for Bug 4866533
292 LN_CREATED_BY,
293 LD_CREATION_DATE,
294 LN_CREATED_BY,
295 LD_CREATION_DATE
296 );
297 END IF;
298 END IF;
299 CLOSE CHK_FOR_SUPP_INV_ENT;
300 END LOOP;
301 END LOOP;
302
303
304 begin
305 for i in SELECT_FILTER_INV
306 loop
307 for j in MAP_IDENT
308 loop
309 IF I.TRX_DATE BETWEEN J.EFFECTIVE_FROM_DATE AND SYSDATE THEN
310 IF J.MAPPING_TYPE = 'T' THEN
311
312
313
314 OPEN CH_TAX(I.CUSTOMER_TRX_ID,J.EXISTING_TYPE_ID);
315 FETCH CH_TAX INTO K;
316 IF CH_TAX%FOUND THEN CHECK_FLAG := 'Y';
317 ELSE
318 CHECK_FLAG := 'N';
319 END IF;
320 CLOSE CH_TAX;
321 ELSE OPEN CH(I.CUSTOMER_TRX_ID,J.MAPPING_TYPE);
322 FETCH CH INTO K;
323 IF CH%FOUND THEN
324 CHECK_FLAG := 'Y';
325 ELSE
326 CHECK_FLAG := 'N';
327 END IF;
328 CLOSE CH;
329 END IF;
330
331
332
333 IF CHECK_FLAG = 'N' THEN
334
335 IF i.set_of_books_id = j.set_of_books_id
336 and i.org_id = nvl(j.org_id,i.org_id)
337 and i.customer_id = nvl(j.customer_id,i.customer_id)
338 and i.customer_site = nvl(j.site_use_id,i.customer_site) then
339
340 --Added by zhiwei for Bug#12615714 begin
341 -------------------------------------------
342 select count(1)
343 into ln_open_api
344 from ra_customer_trx_lines_all
345 where customer_trx_id = I.CUSTOMER_TRX_ID
346 and nvl( global_attribute_category,'###') = 'JG.IN.ARXTWMAI.OFI TAX IMPORT';
347 ------------------------------------------
348 --Added by zhiwei for Bug#12615714 end
349
350 IF I.INTERFACE_HEADER_CONTEXT LIKE '%ORDER%'
351 AND J.MAPPING_TYPE LIKE 'P' THEN
352
353 INSERT INTO JAI_AR_TRX_SUP_INV_T
354 (CUSTOMER_TRX_ID,
355 EXISTING_TYPE_ID,
356 NEW_TYPE_ID,
357 MAPPING_TYPE,
358 MAPPING_ID,
359 TRX_DATE,
360 EFFECTIVE_DATE,
361 SET_OF_BOOKS_ID,
362 ORG_ID,
363 CUSTOMER_SITE,
364 INTERFACE_HEADER_CONTEXT,
365 INTERFACE_HEADER_ATTRIBUTE1,
366 INTERFACE_HEADER_ATTRIBUTE6,
367 INTERFACE_HEADER_ATTRIBUTE7,
368 CONVERSION_RATE,
369 -- added, Harshita for Bug 4866533
370 CREATED_BY,
371 CREATION_DATE,
372 LAST_UPDATED_BY,
373 LAST_UPDATE_DATE
374 )
375 VALUES (I.CUSTOMER_TRX_ID,
376 J.EXISTING_TYPE_ID,
377 J.NEW_TYPE_ID,
378 J.MAPPING_TYPE,
379 J.MAPPING_ID,
380 I.TRX_DATE,
381 J.EFFECTIVE_FROM_DATE,
382 J.SET_OF_BOOKS_ID,
383 J.ORG_ID,
384 J.SITE_USE_ID,
385 I.INTERFACE_HEADER_CONTEXT,
386 I.INTERFACE_HEADER_ATTRIBUTE1,
387 I.INTERFACE_HEADER_ATTRIBUTE6,
388 I.INTERFACE_HEADER_ATTRIBUTE7,
389 J.CONVERSION_RATE,
390 -- added, Harshita for Bug 4866533
391 LN_CREATED_BY,
392 LD_CREATION_DATE,
393 LN_CREATED_BY,
394 LD_CREATION_DATE
395 );
396 END IF;
397 --IF I.INTERFACE_HEADER_CONTEXT IS NULL--Commented by zhiwei for Bug#12615714
398 IF (I.INTERFACE_HEADER_CONTEXT IS NULL
399 ----Added by zhiwei for Bug#12615714 begin
400 --------------------------------------------------
401 --or I.INTERFACE_HEADER_CONTEXT='OFI TAX IMPORT'
402 or nvl(ln_open_api,0) > 0
403 --------------------------------------------------
404 --Added by zhiwei for Bug#12615714 end
405 )
406 AND J.MAPPING_TYPE LIKE 'I' THEN
407
408
409 INSERT INTO JAI_AR_TRX_SUP_INV_T
410 (CUSTOMER_TRX_ID,
411 EXISTING_TYPE_ID,
412 NEW_TYPE_ID,
413 MAPPING_TYPE,
414 MAPPING_ID,
415 TRX_DATE,
416 EFFECTIVE_DATE,
417 SET_OF_BOOKS_ID,
418 ORG_ID,
419 CUSTOMER_SITE,
420 INTERFACE_HEADER_CONTEXT,
421 INTERFACE_HEADER_ATTRIBUTE1,
422 INTERFACE_HEADER_ATTRIBUTE6,
423 INTERFACE_HEADER_ATTRIBUTE7,
424 CONVERSION_RATE,
425 -- added, Harshita for Bug 4866533
426 CREATED_BY,
427 CREATION_DATE,
428 LAST_UPDATED_BY,
429 LAST_UPDATE_DATE
430 )
431 VALUES (I.CUSTOMER_TRX_ID,
432 J.EXISTING_TYPE_ID,
433 J.NEW_TYPE_ID,
434 J.MAPPING_TYPE,
435 J.MAPPING_ID,
436 I.TRX_DATE,
437 J.EFFECTIVE_FROM_DATE,
438 J.SET_OF_BOOKS_ID,
439 J.ORG_ID,
440 J.SITE_USE_ID,
441 I.INTERFACE_HEADER_CONTEXT,
442 I.INTERFACE_HEADER_ATTRIBUTE1,
443 I.INTERFACE_HEADER_ATTRIBUTE6,
444 I.INTERFACE_HEADER_ATTRIBUTE7,
445 J.CONVERSION_RATE,
446 -- added, Harshita for Bug 4866533
447 LN_CREATED_BY,
448 LD_CREATION_DATE,
449 LN_CREATED_BY,
450 LD_CREATION_DATE
451 );
452 END IF;
453 IF J.MAPPING_TYPE IN ('T','A') THEN
454
455
456 INSERT INTO JAI_AR_TRX_SUP_INV_T
457 (CUSTOMER_TRX_ID,
458 EXISTING_TYPE_ID,
459 NEW_TYPE_ID,
460 MAPPING_TYPE,
461 MAPPING_ID,
462 TRX_DATE,
463 EFFECTIVE_DATE,
464 SET_OF_BOOKS_ID,
465 ORG_ID,
466 CUSTOMER_SITE,
467 INTERFACE_HEADER_CONTEXT,
468 INTERFACE_HEADER_ATTRIBUTE1,
469 INTERFACE_HEADER_ATTRIBUTE6,
470 INTERFACE_HEADER_ATTRIBUTE7,
471 CONVERSION_RATE,
472 -- added, Harshita for Bug 4866533
473 CREATED_BY,
474 CREATION_DATE,
475 LAST_UPDATED_BY,
476 LAST_UPDATE_DATE
477 )
478 VALUES(I.CUSTOMER_TRX_ID,
479 J.EXISTING_TYPE_ID,
480 J.NEW_TYPE_ID,
481 J.MAPPING_TYPE,
482 J.MAPPING_ID,
483 I.TRX_DATE,
484 J.EFFECTIVE_FROM_DATE,
485 J.SET_OF_BOOKS_ID,
486 J.ORG_ID,
487 J.SITE_USE_ID,
488 I.INTERFACE_HEADER_CONTEXT,
489 I.INTERFACE_HEADER_ATTRIBUTE1,
490 I.INTERFACE_HEADER_ATTRIBUTE6,
491 I.INTERFACE_HEADER_ATTRIBUTE7,
492 J.CONVERSION_RATE,
493 -- added, Harshita for Bug 4866533
494 LN_CREATED_BY,
495 LD_CREATION_DATE,
496 LN_CREATED_BY,
497 LD_CREATION_DATE
498 );
499 END IF;
500 END IF;
501 END IF;
502 END IF;
503 END LOOP;
504 END LOOP;
505 END;
506
507
508
509 UPDATE JAI_AR_SUP_CHANGES_ALL
510 SET PROCESS_DATE = SYSDATE
511 WHERE BATCH_ID = P_BATCH_ID
512 AND PROCESS_DATE IS NULL;
513
514 /* Added by Ramananda for bug#4407165 */
515 EXCEPTION
516 WHEN OTHERS THEN
517 FND_MESSAGE.SET_NAME('JA','JAI_EXCEPTION_OCCURED');
518 FND_MESSAGE.SET_TOKEN('JAI_PROCESS_MSG', lv_object_name ||'. Err:'||sqlerrm );
519 app_exception.raise_exception;
520
521 END identify_invoices;
522
523 PROCEDURE process_invoices
524 IS
525
526 /* Added by Ramananda for bug#4407165 */
527 lv_object_name CONSTANT VARCHAR2(61) := 'jai_ar_sup_trx_pkg.process_invoices';
528
529 inv_no NUMBER;
530 v_assess_value NUMBER;
531 atax_amount NUMBER;
532 t NUMBER;
533 t_new_type_id NUMBER;
534 tax_diff_amt NUMBER;
535 pack_assess number;
536 customer_trx_line NUMBER;
537 v_p_mapping_id number;
538 v_i_mapping_id number;
539 v_a_mapping_id number;
540 t_mapping_id NUMBER;
541 check1 NUMBER;
542 check2 NUMBER;
543 check5 NUMBER;
544 old_assessable_value NUMBER;
545 p_customer_trx_id NUMBER;
546 amount NUMBER;
547 i_customer_trx_id NUMBER;
548 i_new_type_id NUMBER;
549 a_customer_trx_id NUMBER;
550 t_existing_type_id NUMBER;
551 old_assess_value NUMBER;
552 assess_list_id NUMBER;
553 aselling_price NUMBER;
554 aunit_code VARCHAR2(10);
555 aquantity NUMBER;
556 apicking_line_id NUMBER;
557 atax_category_id NUMBER;
558 new_tax_others NUMBER;
559 o_s_p NUMBER;
560 indicator VARCHAR2(10);
561 n_s_p NUMBER;
562 o_a_val NUMBER;
563 n_a_val NUMBER;
564 exst_ln_amt NUMBER;
565 new_ln_amt NUMBER;
566 p_mapping_id NUMBER;
567 a_mapping_id NUMBER;
568 new_tax_excise NUMBER := 0;
569 old_tax_excise NUMBER := 0;
570 old_tax_type JAI_CMN_TAXES_ALL.TAX_TYPE%TYPE ; /* Modified by aiyer for the bug 5378631*/
571 new_tax_type JAI_CMN_TAXES_ALL.TAX_TYPE%TYPE ; /* Modified by aiyer for the bug 5378631*/
572 old_tax_other NUMBER := 0;
573 new_tax_other NUMBER := 0;
574 old_tax_amount NUMBER := 0;
575 new_tax_amount NUMBER := 0;
576 new_assess_value NUMBER;
577 i_unit_selling_price NUMBER;
578 old_invoice_amount NUMBER;
579 new_invoice_amount NUMBER;
580 oamount NUMBER;
581 n_type_id NUMBER;
582 namount NUMBER;
583 price_list_change_indicator VARCHAR2(1);
584 invoice_list_change_indicator VARCHAR2(1);
585 tax_list_change_indicator VARCHAR2(1);
586 assessable_list_change VARCHAR2(1);
587 adescription VARCHAR2(250);
588 status VARCHAR2(20);
589 p_price_list NUMBER;
590 c_list_price NUMBER;
591 c_diff_price NUMBER;
592 tax_differ1 NUMBER;
593 tax2_DIFFER2 NUMBER;
594 p_existing_type_id NUMBER;
595 p_new_type_id NUMBER;
596 a_new_type_id NUMBER;
597 a_existing_type_id NUMBER;
598 passing_amount NUMBER;
599 passing_assessable NUMBER;
600 check_assess_inv NUMBER;
601 v_assesable_list_id NUMBER;
602 v_assessable_list_id NUMBER;
603
604 CURSOR FETCH_CMB_TAB_INV IS
605 SELECT A.*, B.BATCH_ID
606 FROM JAI_AR_TRX_SUP_INV_T A,
607 JAI_AR_SUP_CHANGES_ALL B
608 WHERE A.MAPPING_ID = B.MAPPING_ID
609 ORDER BY CUSTOMER_TRX_ID;
610
611 CURSOR CHECK_FOR_INV(INV NUMBER) IS
612 SELECT customer_trx_id
613 FROM JAI_AR_SUP_HDRS_ALL
614 WHERE customer_trx_id = INV;
615
616 CURSOR CHECK_SUPP_ASSESS_INV(STAT VARCHAR2,NO NUMBER) IS
617 SELECT 1
618 FROM JAI_AR_SUP_HDRS_ALL
619 WHERE customer_trx_id = NO
620 AND supp_inv_type = STAT;
621
622 CURSOR get_inv_line( ino NUMBER,
623 cp_line_type RA_CUSTOMER_TRX_LINES_ALL.LINE_TYPE%TYPE
624 )
625 IS /* Modified by Ramananda for removal of SQL LITERALs :bug#4428980*/
626 SELECT
627 b.customer_trx_line_id,
628 a.org_id,
629 a.set_of_books_id,
630 a.bill_to_customer_id,
631 a.bill_to_site_use_id,
632 b.line_number,
633 b.uom_code,
634 a.ship_to_site_use_id,
635 a.interface_header_context,
636 b.unit_selling_price,
637 b.line_type,
638 b.link_to_cust_trx_line_id,
639 b.description,
640 b.interface_line_attribute3, -- delivery id is stored here.
641 b.interface_line_attribute7,
642 b.interface_line_attribute6, -- oe_order_lines_all.line_id
643 b.inventory_item_id,
644 c.bom_item_type,
645 d.mapping_type, -- added by sriram - bug # 3143813 - 19/09/2003
646 d.new_type_id,
647 /* Bug 4224466. Added by LGOPALSA */
648 a.invoice_currency_code
649 ,b.global_attribute_category --Added by zhiwei for Bug#12615714
650 FROM ra_customer_trx_all a,
651 ra_customer_trx_lines_all b,
652 mtl_system_items c,
653 jai_ar_trx_sup_inv_t d, -- added by sriram - bug # 3143813 - 19/09/2003
654 jai_ar_trxs jat /* added the table and join condition - aiyer for the fwd porting bug 5225768*/
655 WHERE a.customer_trx_id = b.customer_trx_id
656 AND b.inventory_item_id = c.inventory_item_id
657 --AND c.organization_id = b.warehouse_id
658 AND ( b.interface_line_attribute11 IS NULL
659 OR b.interface_line_attribute11 = '0'
660 or b.global_attribute_category = 'JG.IN.ARXTWMAI.OFI TAX IMPORT'--Added by zhiwei for Bug#12615714 on 20110629
661 )
662 AND d.customer_trx_id = a.customer_trx_id
663 AND b.line_type = cp_line_type --'LINE'
664 AND a.customer_trx_id = ino
665 AND c.organization_id = jat.organization_id
666 AND a.customer_trx_id = jat.customer_trx_id ;
667
668 -- Above Cursor is modified as below by Vijay Shankar for Bug# 3515883
669 CURSOR C_PRICE_LIST_INFO (p_list_header_id NUMBER, p_inv_item_id IN VARCHAR2) IS
670 SELECT product_attr_value, list_header_id
671 FROM qp_List_Lines_v
672 WHERE List_header_id = p_list_header_id
673 AND product_attribute_context = 'ITEM'
674 AND product_attr_value = p_inv_item_id
675 AND NVL( Start_Date_Active, SYSDATE - 1 ) <= SYSDATE
676 AND NVL( End_Date_Active, SYSDATE + 1 ) >= SYSDATE;
677
678 CURSOR LIST_PRICE(ID NUMBER,UNT_CODE VARCHAR2, PRICE_LIST NUMBER,TRDATE DATE) IS
679 SELECT b.operand list_price
680 FROM QP_LIST_LINES b, qp_pricing_attributes c
681 WHERE c.PRODUCT_ATTR_VALUE = TO_CHAR(Id)
682 AND c.product_uom_code = UNT_CODE
683 AND b.list_header_id = PRICE_LIST
684 AND c.list_line_id = b.list_line_id
685 AND NVL(TRUNC(b.end_date_active),TRUNC(SYSDATE)) >= TRUNC(TRDATE);
686 -- Till Here
687 /* Removed ra_site_uses_all used hz_cust_site_uses_all for Bug# 4434287 */
688 CURSOR GET_ADDRESS_OE(PICKING_ID VARCHAR2 ) IS
689 SELECT DISTINCT A.CUST_ACCT_SITE_ID ADDRESS_ID
690 FROM HZ_CUST_SITE_USES_ALL A,
691 OE_ORDER_LINES_ALL B
692 WHERE A.SITE_USE_ID = B.SHIP_TO_ORG_ID
693 AND B.LINE_ID = PICKING_ID;
694 /* Removed ra_site_uses_all used hz_cust_site_uses_all for Bug# 4434287 */
695 CURSOR GET_ADDRESS_AR(p_ship_to_site_id number) IS
696 SELECT CUST_ACCT_SITE_ID ADDRESS_ID
697 FROM HZ_CUST_SITE_USES_ALL
698 WHERE SITE_USE_ID = p_ship_to_site_id;
699
700 CURSOR GET_ASSESS_PRICE(ADDRESS NUMBER,INO1 NUMBER) IS
701 SELECT PRICE_LIST_ID
702 FROM JAI_CMN_CUS_ADDRESSES
703 WHERE ADDRESS_ID = NVL(ADDRESS,0)
704 AND CUSTOMER_ID = INO1;
705
706 CURSOR GET_ASSESSABLE_PRICE_LIST(v_cust_id number) IS
707 SELECT PRICE_LIST_ID
708 FROM JAI_CMN_CUS_ADDRESSES
709 WHERE ADDRESS_ID = 0
710 AND CUSTOMER_ID = v_cust_id;
711
712 CURSOR GET_ASSESS_LIST (INVENT NUMBER,UNT VARCHAR2 ,NEW_NO NUMBER, ADATE DATE) IS
713 SELECT b.operand list_price
714 FROM QP_LIST_LINES b, qp_pricing_attributes c
715 WHERE c.PRODUCT_ATTR_VALUE = TO_CHAR(INVENT)
716 AND c.product_uom_code = UNT
717 AND b.list_header_id = NEW_NO
718 AND c.list_line_id = b.list_line_id
719 AND NVL(TRUNC(b.end_date_active),TRUNC(SYSDATE)) >= TRUNC(ADATE);
720
721 /* Bug 4224466. Added by LGOPALSA.
722 Amount in JAI_AR_SUP_LINES need to be rounded with
723 respect to currency precision as the same is recorded
724 in ra_interface_lines_all */
725
726 Cursor get_curr_prec( lv_currency_code varchar2) is
727 Select precision
728 from fnd_currencies
729 where currency_code = lv_currency_code;
730
731 ln_precision number;
732
733 -- End for bug 4224466.
734
735 -- Till Here
736 --23/03/01 Anuradha Parthasarathy
737 V_ASSESSABLE_VALUE NUMBER;
738
739 V_PROD_ATTR_VALUE VARCHAR2(100);
740 V_LIST_HEADER_ID NUMBER;
741
742 -- Start, Vijay Shankar for bug # 3515883
743 v_today DATE; -- := trunc(SYSDATE); --Ramananda for File.Sql.35
744 v_modifier_adhoc_amount NUMBER := 0;
745 v_modifier_percentage NUMBER := 0;
746
747 lv_list_line_type_code_dis oe_price_adjustments.list_line_type_code%type ;
748 lv_list_line_type_code_sur oe_price_adjustments.list_line_type_code%type ;
749
750 /* Modified by Ramananda for removal of SQL LITERALs :bug#4428980*/
751 CURSOR c_adjustment_details(p_price_adjustment_id IN NUMBER) IS
752 SELECT nvl( decode(list_line_type_code, lv_list_line_type_code_dis, operand, lv_list_line_type_code_sur, -operand), 0) operand, arithmetic_operator
753 --nvl( decode(list_line_type_code, 'DIS', operand, 'SUR', -operand), 0) operand, arithmetic_operator
754 FROM oe_price_adjustments a -- , qp_modifier_summary_v b
755 WHERE list_line_type_code IN ('DIS', 'SUR')
756 AND modifier_level_code = 'LINE'
757 AND price_adjustment_id = p_price_adjustment_id;
758
759 r_adj c_adjustment_details%ROWTYPE;
760 v_statement_id VARCHAR2(4); -- := '0'; --Ramananda for File.Sql.35
761 -- End, Vijay Shankar for bug # 3515883
762 /*
763 || Following cursor added by aiyer for bug the fwd porting bug 4955151 (11i bug #4764676)
764 || If the split has happened at the time of shipment, this cursor will take care of the multiple lines
765 || to get the total quantity and total tax amount
766 */
767 CURSOR c_get_so_picking_lines(
768 p_interface_line_attribute3 RA_CUSTOMER_TRX_LINES_ALL.interface_line_attribute3%TYPE ,
769 p_interface_line_attribute6 RA_CUSTOMER_TRX_LINES_ALL.interface_line_attribute6%TYPE ,
770 p_inventory_item_id RA_CUSTOMER_TRX_LINES_ALL.inventory_item_id%TYPE
771 )
772 IS
773 SELECT
774 SUM(a.quantity), SUM(a.tax_amount),
775 a.selling_price, a.unit_code, a.tax_category_id,
776 a.picking_line_id, a.assessable_value
777 FROM
778 jai_om_wsh_lines_all A
779 WHERE
780 to_char(a.delivery_id) = p_interface_line_attribute3
781 AND to_char(a.order_line_id) = p_interface_line_attribute6
782 AND a.inventory_item_id = p_inventory_item_id
783 GROUP BY
784 a.selling_price, a.unit_code, a.tax_category_id,
785 a.picking_line_id, a.assessable_value ;
786
787
788 /*Commented below by JMEENA for bug#7567647 */
789 /*Added the below by Bgowrava for Bug#6139693
790 CURSOR c_get_sup_seq_val
791 IS
792 SELECT JAI_AR_SUP_LINES_S.NEXTVAL
793 FROM DUAL;
794
795 v_cust_trx_line_no_s NUMBER;
796 v_cust_trx_line_no_s1 NUMBER;
797 END, Bug#6139693*/
798
799
800 BEGIN
801
802
803 /*----------------------------------------------------------------------------------------------------------------------------
804 CHANGE HISTORY: FILENAME: jai_ar_sup_trx_pkg.process_invoices.sql
805 S.No Date Author and Details
806 ------------------------------------------------------------------------------------------------------------------------------
807 1 24/03/2001 Bhaskar,
808 changed a query to select price_list_id basing on Header_Id rather than basing on order_number.
809
810 2 23/03/2001 Anuradha Parthasarathy
811 To ensure that correct assessable value is considered in case of Price List Mapping
812
813 3. 19/09/2003 ssumaith - bug # 3143813
814 Supplementary invoices in Ar are generated for all items in the invoice even though they are not part of the new price list. This has been rectified
815 by adding joins between the inventory item of the invoice and inventory item in the pricelist for price list types of mapping.
816
817 4. 28/03/2004 Vijay Shankar for bug # 3515883, Version: 619.1
818 - modified cursor GET_INV_LINE to filter out Modifier lines from RA_CUSTOMER_TRX_LINES_ALL table and added a
819 seperate code that reduces/adds discounts/surcharges from actual item price.
820 Added Cursor c_adjustment_details and required variables for discounts/surchages calculation
821 - Cursor C_PRICE_LIST_INFO is modified to use qp_list_lines_v View instead of qp_list_lines and qp_pricing_attributes tables.
822 Added inventory_item_id, Start and end date checks to where clause on qp_list_lines_v
823 - Query to fetch details from JAI_OM_WSH_LINES_ALL is modified by adding Order_line_id filter.
824 Also removed the usage of RA_CUSTOMER_TRX_LINES_ALL table from the query which is redundant
825
826 5. 15/03/2005 Bug 4224466. Added by LGOPALSA. Version 115.2
827 Added rounding code for Exist and new Sales amount.
828
829 File Renamed to jai_ar_sup_trx.plb
830 -----------------------------------
831 6. 25/05/2005 Brathod, For Bug# 4392001, Fileversion# 116.1
832 Issue:-
833 RA_INTERFACE_LINES DFF segments needs to be limited use only one segment
834 Fix:-
835 - Following four segments will be obsoleted
836 1. SUPPLEMENT CM
837 2. SUPPLEMENT DM
838 3. SUPPLEMENT INVOICE
839 4. TDS CREDIT
840 - A new segment (INDIA INVOICES) will be created with following attributes
841 1. INTERFACE_LINE_ATTRIBUTE1 - Invoice Type
842 2. INTERFACE_LINE_ATTRIBUTE2 - Unique Identifier
843 - As new dff uses the ATTRIBUTE1 field the existing values of ATTRIBUTE1 will be
844 migrated to ATTRIBUTE4
845 - Attribute context will be changed to INDIA INVOICES.
846 - INTERFACE_LINE_ATTRIBUTE1 will identify the type of invoice the possible values
847 for this field will be same as different segments used previously
848 i.e SUPPLEMENT CM, SUPPLEMENT DM, SUPPLEMENT INVOICE, TDS CREDIT
849
850 7. Aiyer for the bug 5378631, File version 120.9
851 Issues:-
852 India Suggestive Supplementary transactions report does not pick up any invoices.
853 As a part of this fix fwd porting the following fixes:-
854 ==================================================
855 Slno 11i Bug R12 FWD Porting bug
856 ==================================================
857 1. 4324523 No Fwd Porting bug logged
858 2. 4735409 No Fwd Porting bug logged
859 3. 4764676 4955151
860 4. 5104197 5225768
861 ===================================================
862 1. Bug 4324523:- Report India Suggestive Supplementary transactions errors out "PL/SQL: numeric or value error: character string buffer too small"
863 2. code text exceed 255 characters.
864 3.Supplementary transactions picks partial qty for dr/cr when line is split.
865 4.Supplementary invoice not being generated for tax change
866
867 Fix :-
868 1.Bug 4324523:-
869 Fixed the data type mismatch for old_tax_type and new_tax_type with the tax_type column of ja_in_tax_codes
870 in the declaration section. (11i bug 4324523)
871
872 2.Bug 4735409:-
873 Aligned so that the code doesn't exceed 255 characters (11i bug 4735409 )
874
875 3.Bug 4764676:-
876 When split happens at the time of shipment, code in this procedure, which is a Select..Into statement raises an exception.
877 Commented the Select..Into statment and added a Cursor. Added the cursor c_get_so_picking_lines which considers all the lines
878 (result of split) to get the Total Quantity and Total Tax Amount. (11i bug 4764676, Fwd porting bug 4955151)
879
880 4. Bug 5104197:-
881 Modified the cursor get_inv_line. Removed the join condition for organization_id of mtl_system_items and
882 warehouse_id of ra_customer_trx_lines_all. Included a join condition for organization_id of mtl_system_items and jai_ar_trx.
883 This has been done because there are cases where the warehouse_id in the ra_customer_trx_lines_all is null. Hence removed that link.
884 (11i bug 5104197, fwd porting bug 5225768)
885
886
887 25-June-2007 Bgowrava for Bug#6139693, File Version 120.12
888 Added Cursor c_get_sup_seq_val which is used to generate sequence numbers from the sequence
889 JAI_AR_SUP_LINES_S. and this is used as the value for the customer_trx_line_id
890 of the table JAI_AR_SUP_LINES.
891
892 07-Jul-2007 Bgowrava for Bug#6139693, File Version 120.13
893 Added the NVL condition for the EXIST_NET_SALES_AMT fields in the update statements of
894 JAI_AR_SUP_HDRS_ALL.
895
896 12-Sep-2007 Bgowrava for bug#6400997, File Version 120.9.12000000.6
897 Added the columns SUPP_INV_RAISED_IND, CHOSEN_FOR_PROCESS in the insert statements
898 and assigned values 'N' to both of them. This was done because the default values were
899 not getting defaulted.
900
901 08-AUG-2008 JMEENA for bug#7567647
902 Commented the cursor c_get_sup_seq_val and replaced the cursor generated v_cust_trx_line_no_s
903 with I.CUSTOMER_TRX_LINE_ID to insert in column CUSTOMER_TRX_LINE_ID and interface_line_Attribute2.
904
905 05-Apr-2013 mmurtuza for bug 16580917
906 Description: CESS and SH CESS LOADED TO EXISTING OTHERS AMT INSTEAD OF EXISTING EXCISE AMT
907 Fix: Added UPPER function to include all excise type of taxes in Excise Amount
908
909 ------------------------------------------------------------------------------------------------------------------------------*/
910
911 v_today := trunc(SYSDATE); --Ramananda for File.Sql.35
912 v_statement_id := '0'; --Ramananda for File.Sql.35
913
914
915
916
917 FND_FILE.put_line(FND_FILE.log, '~~~~~~~~~~~~~~ Start India Localization Log ~~~~~~~~~~~~~~');
918
919 v_statement_id := '1';
920
921 FOR fetch_inv IN fetch_cmb_tab_inv LOOP -- 00000a
922
923
924
925
926 inv_no := null;
927
928 v_statement_id := '2';
929
930 IF CHECK_FOR_INV%ISOPEN THEN
931 CLOSE CHECK_FOR_INV;
932 END IF;
933
934 v_statement_id := '3';
935
936 -- CURSOR TO CHECK IF AN INVOICE HAS ENTRIES EXISTING IN THE JAI_AR_SUP_HDRS_ALL --
937 OPEN CHECK_FOR_INV(FETCH_INV.CUSTOMER_TRX_ID);
938 FETCH CHECK_FOR_INV INTO INV_NO;
939
940 IF CHECK_FOR_INV%NOTFOUND THEN -- 0000a1
941 v_statement_id := '4';
942
943
944 CLOSE CHECK_FOR_INV;
945
946 BEGIN
947 FOR I IN GET_INV_LINE(FETCH_INV.CUSTOMER_TRX_ID, 'LINE') LOOP -- 0000aa
948
949 v_statement_id := '4';
950
951 -- following cursor and if condition added by sriram - bug # 3143813
952 OPEN C_PRICE_LIST_INFO (I.NEW_TYPE_ID, i.inventory_item_id);
953 FETCH C_PRICE_LIST_INFO INTO v_prod_attr_value, v_list_header_id;
954 CLOSE C_PRICE_LIST_INFO;
955
956 /* Bug 4224466. Added by LGOPALSA
957 Get the currency Precision */
958
959 Open get_curr_prec(i.invoice_currency_code);
960 Fetch get_curr_prec into ln_precision;
961 Close get_curr_prec;
962
963 -- This is to ensure that the precision need not be
964 -- kept as null. If it is null then precision is 0
965
966 ln_precision := nvl(ln_precision,0);
967
968 -- End for bug4224466
969
970 v_statement_id := '5';
971
972 IF (v_prod_attr_value = to_char(i.inventory_item_id) and i.mapping_type <> 'T')
973 OR i.mapping_type = 'T'
974 THEN -- 000aa1
975 v_statement_id := '6';
976 IF FETCH_INV.INTERFACE_HEADER_CONTEXT = 'ORDER ENTRY' THEN -- 00aa11
977 v_statement_id := '7';
978 /*
979 || Aiyer for fwd porting bug 4955151 (11i bug#4764676), Start
980 || Commented the select into query and added the below cursor statements.
981 || When Split is done at the time of shipment, the above SELECT..INTO returns more than one row, which causes an exception
982 || Added the following cursor statements which gets the Total Quantity and Total Tax Amount
983 || grouped on the selling_price, unit_code, tax_category_id, picking_line_id, assessable_value
984 */
985
986 OPEN c_get_so_picking_lines (
987 i.interface_line_attribute3 ,
988 i.interface_line_attribute6 ,
989 i.inventory_item_id
990 ) ;
991 FETCH c_get_so_picking_lines INTO
992 aquantity, atax_amount,
993 aselling_price, aunit_code, atax_category_id,
994 apicking_line_id, old_assessable_value ;
995 CLOSE c_get_so_picking_lines ;
996 /*
997 || Ramananda for bug#4764676, End
998 */
999
1000 END IF; -- 00aa11
1001
1002 v_statement_id := '8';
1003
1004 IF FETCH_INV.INTERFACE_HEADER_CONTEXT IS NULL
1005 --Added by zhiwei for Bug#12615714 begin
1006 -----------------------------------------------------------
1007 --or FETCH_INV.INTERFACE_HEADER_CONTEXT = 'OFI TAX IMPORT'
1008 or nvl(I.global_attribute_category,'###') = 'JG.IN.ARXTWMAI.OFI TAX IMPORT'
1009 -----------------------------------------------------------
1010 --Added by zhiwei for Bug#12615714 end
1011 THEN -- 00aa12
1012
1013 BEGIN
1014
1015 -- DISTINCT Key Word is not required, Vijay Shankar for bug # 3515883
1016 -- SELECT DISTINCT QUANTITY, TAX_CATEGORY_ID, ASSESSABLE_VALUE
1017 SELECT QUANTITY, TAX_CATEGORY_ID, ASSESSABLE_VALUE
1018 INTO AQUANTITY, ATAX_CATEGORY_ID, OLD_ASSESSABLE_VALUE
1019 FROM JAI_AR_TRX_LINES
1020 WHERE CUSTOMER_TRX_LINE_ID = I.CUSTOMER_TRX_LINE_ID;
1021 EXCEPTION
1022 WHEN OTHERS THEN
1023 NULL;
1024 END;
1025 END IF; -- 00aa12
1026
1027 v_statement_id := '9';
1028
1029 -- GETTING THE OLD PRICE ATTACHED TO AN ITEM --
1030 IF I.INTERFACE_HEADER_CONTEXT = 'ORDER ENTRY' THEN -- 00aa13
1031 O_S_P := ASELLING_PRICE;
1032 ELSIF I.INTERFACE_HEADER_CONTEXT IS NULL
1033 --Added by zhiwei for Bug#12615714 begin
1034 --------------------------------------------------
1035 --or I.INTERFACE_HEADER_CONTEXT = 'OFI TAX IMPORT'
1036 or nvl(I.global_attribute_category,'###') = 'JG.IN.ARXTWMAI.OFI TAX IMPORT'
1037 --------------------------------------------------
1038 --Added by zhiwei for Bug#12615714 end
1039 THEN
1040 AUNIT_CODE := I.UOM_CODE;
1041 O_S_P := I.UNIT_SELLING_PRICE;
1042 END IF; -- 00aa13
1043
1044 v_statement_id := '10';
1045
1046 PRICE_LIST_CHANGE_INDICATOR := 'N';
1047 INVOICE_LIST_CHANGE_INDICATOR := 'N';
1048 TAX_LIST_CHANGE_INDICATOR := 'N';
1049 ASSESSABLE_LIST_CHANGE := 'N';
1050
1051 IF I.INTERFACE_HEADER_CONTEXT = 'ORDER ENTRY' THEN -- 00aa14
1052
1053 BEGIN
1054
1055 v_statement_id := '11';
1056
1057 OAMOUNT := ASELLING_PRICE*AQUANTITY;
1058
1059 SELECT DISTINCT CUSTOMER_TRX_ID, EXISTING_TYPE_ID, NEW_TYPE_ID, Mapping_id
1060 INTO P_CUSTOMER_TRX_ID, P_EXISTING_TYPE_ID, P_NEW_TYPE_ID, v_p_mapping_id
1061 FROM JAI_AR_TRX_SUP_INV_T
1062 WHERE MAPPING_TYPE = 'P'
1063 AND CUSTOMER_TRX_ID = FETCH_INV.CUSTOMER_TRX_ID;
1064
1065 v_statement_id := '12';
1066
1067 IF SQL%FOUND THEN -- 0aa141
1068 -- BLOCK FOR GETTING THE ORIGINAL PRICE LIST ATTACHED TO INVOICE --
1069 BEGIN
1070
1071 SELECT PRICE_LIST_ID
1072 INTO P_PRICE_LIST
1073 FROM OE_ORDER_HEADERS_ALL
1074 WHERE header_id = (SELECT header_id
1075 FROM oe_order_lines_all
1076 WHERE line_id = FETCH_INV.INTERFACE_HEADER_ATTRIBUTE6
1077 AND org_id = FETCH_INV.org_id);
1078
1079 EXCEPTION
1080 When no_data_found then
1081 null;
1082 END;
1083
1084 v_statement_id := '13';
1085 IF P_PRICE_LIST = P_EXISTING_TYPE_ID
1086 OR (P_EXISTING_TYPE_ID IS NULL AND P_PRICE_LIST = P_NEW_TYPE_ID)
1087 THEN -- aa1411
1088
1089 v_statement_id := '14';
1090 IF I.BOM_ITEM_TYPE <> 1 THEN
1091 OPEN LIST_PRICE(I.INVENTORY_ITEM_ID,AUNIT_CODE,P_NEW_TYPE_ID,FETCH_INV.TRX_DATE);
1092 FETCH LIST_PRICE INTO C_LIST_PRICE;
1093 CLOSE list_price;
1094 ELSIF i.bom_item_type = 1 THEN
1095 jai_om_utils_pkg.get_ato_pricelist_value(P_NEW_TYPE_ID,AUNIT_CODE,I.INVENTORY_ITEM_ID, I.INTERFACE_LINE_ATTRIBUTE6, C_LIST_PRICE);
1096 END IF;
1097
1098 v_statement_id := '15';
1099
1100
1101 IF C_LIST_PRICE <> ASELLING_PRICE THEN
1102
1103 PRICE_LIST_CHANGE_INDICATOR := 'Y';
1104
1105 -- Start, Vijay Shankar for bug # 3515883
1106 -- This is to get the modifier amount linked to Customer Trx Line
1107 v_modifier_adhoc_amount := 0;
1108 v_modifier_percentage := 0;
1109 v_statement_id := '16';
1110 FOR dis IN (SELECT customer_trx_line_id, to_number(interface_line_attribute11) price_adjustment_id
1111 --, line_number, unit_standard_price, unit_selling_price, extended_amount, revenue_amount
1112 FROM ra_customer_trx_lines_all
1113 WHERE interface_line_attribute11 <> '0'
1114 AND interface_line_attribute6 = I.interface_line_attribute6
1115 AND customer_trx_id = fetch_inv.customer_trx_id
1116 AND line_type = 'LINE')
1117 LOOP
1118
1119 r_adj := null;
1120 v_statement_id := '17';
1121 lv_list_line_type_code_dis := 'DIS' ;
1122 lv_list_line_type_code_sur := 'SUR' ;
1123 OPEN c_adjustment_details(dis.price_adjustment_id);
1124 FETCH c_adjustment_details INTO r_adj;
1125 CLOSE c_adjustment_details;
1126
1127 v_statement_id := '18';
1128 IF r_adj.arithmetic_operator = 'AMT' THEN
1129 v_modifier_adhoc_amount := v_modifier_adhoc_amount + r_adj.operand;
1130 ELSIF r_adj.arithmetic_operator = '%' THEN
1131 v_modifier_percentage := v_modifier_percentage + r_adj.operand;
1132 END IF;
1133
1134 END LOOP;
1135
1136 v_statement_id := '18';
1137 C_LIST_PRICE := C_LIST_PRICE - (C_LIST_PRICE * v_modifier_percentage/100);
1138 C_LIST_PRICE := C_LIST_PRICE - v_modifier_adhoc_amount;
1139 -- End, Vijay Shankar for bug # 3515883
1140
1141 C_DIFF_PRICE := (C_LIST_PRICE - ASELLING_PRICE) *AQUANTITY;
1142 IF C_DIFF_PRICE < 0 THEN
1143 STATUS := 'CR';
1144 ELSIF C_DIFF_PRICE > 0 THEN
1145 STATUS := 'SI';
1146 END IF;
1147 /* Bug 4224466. Added by LGOPALSA.
1148 Rounded the Exist and new sales amount
1149 with respect to currency. */
1150
1151 OAMOUNT := round(ASELLING_PRICE*AQUANTITY ,ln_precision );
1152 NAMOUNT := round(C_LIST_PRICE*AQUANTITY , ln_precision);
1153
1154
1155 v_statement_id := '19';
1156 IF PRICE_LIST_CHANGE_INDICATOR = 'Y' THEN
1157
1158 v_statement_id := '20';
1159 OPEN CHECK_SUPP_ASSESS_INV(STATUS,FETCH_INV.CUSTOMER_TRX_ID);
1160 FETCH CHECK_SUPP_ASSESS_INV INTO CHECK_ASSESS_INV ;
1161
1162 IF CHECK_SUPP_ASSESS_INV%NOTFOUND THEN
1163 v_statement_id := '21';
1164 INSERT INTO JAI_AR_SUP_HDRS_ALL(SUP_HDR_ID,
1165 CUSTOMER_TRX_ID, CUSTOMER_ID, TRX_DATE, SUPP_INV_TYPE, LAST_UPDATE_DATE, CREATED_BY,
1166 CREATION_DATE, LAST_UPDATED_BY, EXIST_NET_SALES_AMT, NEW_NET_SALES_AMT,
1167 SET_OF_BOOKS_ID, ORG_ID, CUSTOMER_SITE, BATCH_ID, SUPP_INV_RAISED_IND, CHOSEN_FOR_PROCESS
1168 ) VALUES ( JAI_AR_SUP_HDRS_ALL_S.nextval,
1169 FETCH_INV.CUSTOMER_TRX_ID, I.BILL_TO_CUSTOMER_ID, FETCH_INV.TRX_DATE, STATUS, SYSDATE, fnd_global.user_id,
1170 SYSDATE, fnd_global.user_id, OAMOUNT, NAMOUNT,
1171 I.SET_OF_BOOKS_ID, I.ORG_ID, I.BILL_TO_SITE_USE_ID, FETCH_INV.BATCH_ID, 'N', 'N'
1172 );--Bgowrava for Bug#6400997, added SUPP_INV_RAISED_IND, CHOSEN_FOR_PROCESS
1173
1174 ELSE
1175 v_statement_id := '22';
1176
1177 UPDATE JAI_AR_SUP_HDRS_ALL
1178 SET EXIST_NET_SALES_AMT = nvl(EXIST_NET_SALES_AMT,0)+OAMOUNT,
1179 NEW_NET_SALES_AMT = nvl(NEW_NET_SALES_AMT,0) +NAMOUNT
1180 WHERE CUSTOMER_TRX_ID = FETCH_INV.CUSTOMER_TRX_ID
1181 AND SUPP_INV_TYPE = STATUS;
1182 END IF;
1183
1184 CLOSE CHECK_SUPP_ASSESS_INV;
1185
1186 v_statement_id := '23';
1187 /* Commented the below code by JMEENA for bug#7567647
1188 --Added by Bgowrava for Bug#6139693
1189
1190 OPEN c_get_sup_seq_val;
1191 FETCH c_get_sup_seq_val into v_cust_trx_line_no_s;
1192 CLOSE c_get_sup_seq_val; */
1193 /*bug#7567647 Replaced v_cust_trx_line_no_s with I.CUSTOMER_TRX_LINE_ID to insert in column CUSTOMER_TRX_LINE_ID and interface_line_Attribute2*/
1194 INSERT INTO JAI_AR_SUP_LINES(
1195 CUSTOMER_TRX_ID, CUSTOMER_TRX_LINE_ID, INVENTORY_ITEM_ID, LINE_NUMBER, SUP_INV_TYPE,
1196 DESCRIPTION, QUANTITY, UNIT_CODE, TAX_CATEGORY_ID,
1197 OLD_SELLING_PRICE, NEW_SELLING_PRICE, EXIST_LINE_AMT, NEW_LINE_AMT,
1198 LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN,
1199 CREATION_DATE, CREATED_BY, PL_MAPPING_ID, interface_line_Attribute2
1200 ) VALUES (
1201 FETCH_INV.CUSTOMER_TRX_ID, I.CUSTOMER_TRX_LINE_ID, I.INVENTORY_ITEM_ID, I.LINE_NUMBER, STATUS,
1202 I.DESCRIPTION, AQUANTITY, AUNIT_CODE, ATAX_CATEGORY_ID,
1203 ASELLING_PRICE, C_LIST_PRICE, OAMOUNT, NAMOUNT,
1204 SYSDATE, fnd_global.user_id, NULL,
1205 SYSDATE, fnd_global.user_id, v_p_mapping_id,I.CUSTOMER_TRX_LINE_ID
1206 );
1207
1208 END IF;
1209
1210 END IF;
1211
1212 END IF; -- aa1411
1213
1214 END IF; -- 0aa141
1215
1216 EXCEPTION
1217 WHEN NO_DATA_FOUND THEN
1218 NULL;
1219 END;
1220
1221 END IF; -- 00aa14
1222
1223 v_statement_id := '25';
1224 IF FETCH_INV.INTERFACE_HEADER_CONTEXT IS NULL
1225 --Added by zhiwei for Bug#12615714 begin
1226 ----------------------------------------------------------
1227 --or FETCH_INV.INTERFACE_HEADER_CONTEXT = 'OFI TAX IMPORT'
1228 or nvl(I.global_attribute_category,'###') = 'JG.IN.ARXTWMAI.OFI TAX IMPORT'
1229 ----------------------------------------------------------
1230 --Added by zhiwei for Bug#12615714 end
1231
1232 THEN -- 00aa15
1233 BEGIN
1234
1235 INVOICE_LIST_CHANGE_INDICATOR := 'N';
1236
1237 v_statement_id := '26';
1238 SELECT UNIT_SELLING_PRICE
1239 INTO I_UNIT_SELLING_PRICE
1240 FROM JAI_AR_TRX_LINES
1241 WHERE CUSTOMER_TRX_LINE_ID = I.CUSTOMER_TRX_LINE_ID;
1242
1243 OLD_INVOICE_AMOUNT := I_UNIT_SELLING_PRICE*AQUANTITY;
1244
1245 v_statement_id := '27';
1246 SELECT CUSTOMER_TRX_ID, NEW_TYPE_ID, mapping_id
1247 INTO I_CUSTOMER_TRX_ID, I_NEW_TYPE_ID, v_i_mapping_id
1248 FROM JAI_AR_TRX_SUP_INV_T
1249 WHERE MAPPING_TYPE = 'I'
1250 AND CUSTOMER_TRX_ID = FETCH_INV.CUSTOMER_TRX_ID;
1251
1252 v_statement_id := '28';
1253 IF SQL%FOUND THEN -- 0aa151
1254 IF I.BOM_ITEM_TYPE <> 1 THEN
1255 v_statement_id := '29';
1256 IF LIST_PRICE%ISOPEN THEN
1257 CLOSE LIST_PRICE;
1258 END IF;
1259
1260 v_statement_id := '30';
1261 OPEN LIST_PRICE(I.INVENTORY_ITEM_ID,I.UOM_CODE,I_NEW_TYPE_ID,FETCH_INV.TRX_DATE);
1262 FETCH LIST_PRICE INTO C_LIST_PRICE;
1263 CLOSE LIST_PRICE;
1264
1265 ELSIF I.BOM_ITEM_TYPE = 1 THEN
1266
1267 v_statement_id := '31';
1268
1269 jai_om_utils_pkg.get_ato_pricelist_value(I_NEW_TYPE_ID,AUNIT_CODE,I.INVENTORY_ITEM_ID, I.INTERFACE_LINE_ATTRIBUTE6,C_LIST_PRICE);
1270 END IF;
1271
1272 v_statement_id := '32';
1273 IF C_LIST_PRICE <> I_UNIT_SELLING_PRICE THEN -- aa1511
1274
1275 v_statement_id := '33';
1276
1277 INVOICE_LIST_CHANGE_INDICATOR := 'Y';
1278 C_DIFF_PRICE := C_LIST_PRICE*AQUANTITY - I_UNIT_SELLING_PRICE*AQUANTITY;
1279
1280 /* Bug 4224466. Added by LGOPALSA.
1281 Rounded the Exist and new sales amount
1282 with respect to currency. */
1283
1284 OLD_INVOICE_AMOUNT := round( I_UNIT_SELLING_PRICE*AQUANTITY , ln_precision);
1285 NEW_INVOICE_AMOUNT := round( C_LIST_PRICE*AQUANTITY , ln_precision );
1286
1287 v_statement_id := '34';
1288 IF C_DIFF_PRICE > 0 THEN
1289 STATUS := 'SI';
1290 ELSIF C_DIFF_PRICE < 0 THEN
1291 STATUS := 'CR';
1292 END IF;
1293
1294 v_statement_id := '35';
1295 IF INVOICE_LIST_CHANGE_INDICATOR = 'Y' THEN
1296
1297 IF CHECK_SUPP_ASSESS_INV%ISOPEN THEN
1298 CLOSE CHECK_SUPP_ASSESS_INV;
1299 END IF;
1300
1301 v_statement_id := '36';
1302 OPEN CHECK_SUPP_ASSESS_INV(STATUS,FETCH_INV.CUSTOMER_TRX_ID);
1303 FETCH CHECK_SUPP_ASSESS_INV INTO CHECK_ASSESS_INV ;
1304
1305 IF CHECK_SUPP_ASSESS_INV%NOTFOUND THEN
1306
1307 v_statement_id := '37';
1308
1309 INSERT INTO JAI_AR_SUP_HDRS_ALL(SUP_HDR_ID,
1310 CUSTOMER_TRX_ID, CUSTOMER_ID, TRX_DATE, SUPP_INV_TYPE,
1311 LAST_UPDATE_DATE, CREATED_BY, CREATION_DATE, LAST_UPDATED_BY,
1312 EXIST_NET_SALES_AMT, NEW_NET_SALES_AMT, SET_OF_BOOKS_ID,
1313 CUSTOMER_SITE, ORG_ID, BATCH_ID, SUPP_INV_RAISED_IND, CHOSEN_FOR_PROCESS
1314 ) VALUES ( JAI_AR_SUP_HDRS_ALL_S.nextval,
1315 FETCH_INV.CUSTOMER_TRX_ID, I.BILL_TO_CUSTOMER_ID, FETCH_INV.TRX_DATE, STATUS,
1316 SYSDATE, fnd_global.user_id, SYSDATE, fnd_global.user_id,
1317 OLD_INVOICE_AMOUNT, NEW_INVOICE_AMOUNT, I.SET_OF_BOOKS_ID,
1318 I.BILL_TO_SITE_USE_ID, I.ORG_ID, FETCH_INV.BATCH_ID, 'N', 'N'
1319 );--Bgowrava for Bug#6400997, added SUPP_INV_RAISED_IND, CHOSEN_FOR_PROCESS
1320
1321 ELSIF CHECK_SUPP_ASSESS_INV%FOUND THEN
1322
1323 v_statement_id := '38';
1324
1325 UPDATE JAI_AR_SUP_HDRS_ALL
1326 SET EXIST_NET_SALES_AMT = nvl(EXIST_NET_SALES_AMT,0)+ OLD_INVOICE_AMOUNT,
1327 NEW_NET_SALES_AMT = nvl(NEW_NET_SALES_AMT,0) +NEW_INVOICE_AMOUNT
1328 WHERE CUSTOMER_TRX_ID = FETCH_INV.CUSTOMER_TRX_ID
1329 AND SUPP_INV_TYPE = STATUS;
1330
1331 END IF;
1332
1333 CLOSE CHECK_SUPP_ASSESS_INV;
1334
1335 v_statement_id := '39';
1336 /*bug#7567647 Replaced v_cust_trx_line_no_s with I.CUSTOMER_TRX_LINE_ID to insert in column CUSTOMER_TRX_LINE_ID and interface_line_Attribute2*/
1337 INSERT INTO JAI_AR_SUP_LINES(
1338 CUSTOMER_TRX_ID, CUSTOMER_TRX_LINE_ID, INVENTORY_ITEM_ID, LINE_NUMBER,
1339 DESCRIPTION, SUP_INV_TYPE, QUANTITY, UNIT_CODE,
1340 TAX_CATEGORY_ID, OLD_SELLING_PRICE, NEW_SELLING_PRICE,
1341 EXIST_LINE_AMT, NEW_LINE_AMT, LAST_UPDATE_DATE, LAST_UPDATED_BY,
1342 LAST_UPDATE_LOGIN, CREATION_DATE, CREATED_BY, PL_MAPPING_ID,interface_line_attribute2
1343 ) VALUES (
1344 FETCH_INV.CUSTOMER_TRX_ID, I.CUSTOMER_TRX_LINE_ID, I.INVENTORY_ITEM_ID, I.LINE_NUMBER,
1345 I.DESCRIPTION, STATUS, AQUANTITY, AUNIT_CODE,
1346 ATAX_CATEGORY_ID, I_UNIT_SELLING_PRICE, C_LIST_PRICE,
1347 OLD_INVOICE_AMOUNT, NEW_INVOICE_AMOUNT, SYSDATE, fnd_global.user_id,
1348 NULL, SYSDATE, fnd_global.user_id, v_I_mapping_id,I.CUSTOMER_TRX_LINE_ID
1349 );
1350
1351 END IF;
1352
1353 END IF; -- aa1511
1354
1355 END IF; -- 0aa151
1356
1357 EXCEPTION
1358 WHEN OTHERS THEN
1359 NULL;
1360 END;
1361
1362 END IF; -- 00aa15
1363
1364 v_statement_id := '40';
1365 /* BLOCK FOR DEALING WITH ASSESSABLE VALUE CHANGES */
1366 DECLARE -- X1
1367 ADDRESS NUMBER;
1368 BEGIN
1369 v_a_mapping_id := null;
1370 IF FETCH_INV.INTERFACE_HEADER_CONTEXT = 'ORDER ENTRY' THEN
1371
1372 v_statement_id := '41';
1373 IF NOT GET_ADDRESS_OE%ISOPEN THEN
1374 OPEN GET_ADDRESS_OE(I.INTERFACE_LINE_ATTRIBUTE6);
1375 END IF;
1376 FETCH GET_ADDRESS_OE INTO ADDRESS;
1377 CLOSE GET_ADDRESS_OE;
1378 ELSIF FETCH_INV.INTERFACE_HEADER_CONTEXT IS NULL
1379 --Added by zhiwei for Bug#12615714 begin
1380 ----------------------------------------------------------
1381 --or FETCH_INV.INTERFACE_HEADER_CONTEXT = 'OFI TAX IMPORT'
1382 or nvl(I.global_attribute_category,'###') = 'JG.IN.ARXTWMAI.OFI TAX IMPORT'
1383 ----------------------------------------------------------
1384 --Added by zhiwei for Bug#12615714 end
1385 THEN
1386 v_statement_id := '42';
1387 OPEN GET_ADDRESS_AR(I.SHIP_TO_SITE_USE_ID);
1388 FETCH GET_ADDRESS_AR INTO ADDRESS;
1389 CLOSE GET_ADDRESS_AR;
1390 END IF;
1391
1392 v_statement_id := '43';
1393 OPEN GET_ASSESS_PRICE(ADDRESS,I.BILL_TO_CUSTOMER_ID);
1394 FETCH GET_ASSESS_PRICE INTO V_ASSESABLE_LIST_ID;
1395 IF GET_ASSESS_PRICE%NOTFOUND THEN
1396 v_statement_id := '44';
1397 CLOSE GET_ASSESS_PRICE;
1398
1399 OPEN GET_ASSESSABLE_PRICE_LIST(I.BILL_TO_CUSTOMER_ID);
1400 FETCH GET_ASSESSABLE_PRICE_LIST INTO V_ASSESABLE_LIST_ID;
1401 CLOSE GET_ASSESSABLE_PRICE_LIST;
1402 ELSIF GET_ASSESS_PRICE%FOUND THEN
1403 CLOSE GET_ASSESS_PRICE;
1404 END IF;
1405
1406 v_statement_id := '45';
1407 OPEN GET_ASSESS_LIST(I.INVENTORY_ITEM_ID,I.UOM_CODE,V_ASSESABLE_LIST_ID,FETCH_INV.TRX_DATE);
1408 FETCH GET_ASSESS_LIST INTO V_ASSESSABLE_VALUE;
1409 CLOSE GET_ASSESS_LIST;
1410
1411 IF V_ASSESSABLE_VALUE IS NULL THEN
1412 V_ASSESSABLE_VALUE := C_LIST_PRICE;
1413 END IF;
1414
1415 v_statement_id := '46';
1416 SELECT CUSTOMER_TRX_ID, EXISTING_TYPE_ID, NEW_TYPE_ID, mapping_id
1417 INTO A_CUSTOMER_TRX_ID, A_EXISTING_TYPE_ID, A_NEW_TYPE_ID, v_a_mapping_id
1418 FROM JAI_AR_TRX_SUP_INV_T
1419 WHERE MAPPING_TYPE = 'A'
1420 AND CUSTOMER_TRX_ID = FETCH_INV.CUSTOMER_TRX_ID;
1421
1422
1423 v_statement_id := '47';
1424 IF SQL%FOUND THEN
1425 v_statement_id := '48';
1426 IF V_ASSESABLE_LIST_ID = A_EXISTING_TYPE_ID
1427 OR (A_EXISTING_TYPE_ID IS NULL AND V_ASSESABLE_LIST_ID = A_NEW_TYPE_ID)
1428 THEN
1429 IF I.BOM_ITEM_TYPE = 1 THEN
1430 v_statement_id := '50';
1431 jai_om_utils_pkg.get_ato_assessable_value( A_NEW_TYPE_ID,I.INTERFACE_LINE_ATTRIBUTE6, NEW_ASSESS_VALUE);
1432 ELSIF I.BOM_ITEM_TYPE <> 1 THEN
1433 v_statement_id := '51';
1434 OPEN GET_ASSESS_LIST(I.INVENTORY_ITEM_ID,AUNIT_CODE,A_NEW_TYPE_ID,FETCH_INV.TRX_DATE) ;
1435 FETCH GET_ASSESS_LIST INTO NEW_ASSESS_VALUE;
1436 CLOSE GET_ASSESS_LIST;
1437 END IF;
1438
1439 v_statement_id := '53';
1440 IF OLD_ASSESSABLE_VALUE <> NEW_ASSESS_VALUE THEN
1441 v_statement_id := '54';
1442 ASSESSABLE_LIST_CHANGE := 'Y';
1443 ELSE
1444 ASSESSABLE_LIST_CHANGE := 'N';
1445 END IF;
1446 END IF;
1447 END IF;
1448
1449 EXCEPTION
1450 WHEN NO_DATA_FOUND THEN
1451 NULL;
1452 END; -- X1
1453
1454 DELETE JAI_AR_TRX_TAX_CALC_T;
1455
1456 v_statement_id := '55';
1457 -- BLOCK DEALING WITH TAX CHANGES
1458 DECLARE -- 00X2
1459 CURSOR GET_TAX IS
1460 SELECT *
1461 FROM JAI_AR_TRX_TAX_LINES
1462 WHERE LINK_TO_CUST_TRX_LINE_ID = I.CUSTOMER_TRX_LINE_ID;
1463
1464 CURSOR GET_NEW_TAX IS
1465 SELECT TEMP.TAX_LINE_NO,
1466 TEMP.CUSTOMER_TRX_LINE_ID,
1467 TEMP.LINK_TO_CUST_TRX_LINE_ID,
1468 TEMP.TAX_ID,
1469 TEMP.TAX_RATE,
1470 TEMP.QTY_RATE,
1471 TEMP.UOM,
1472 TEMP.TAX_AMOUNT,
1473 TEMP.FUNC_TAX_AMOUNT,
1474 TEMP.BASE_TAX_AMOUNT,
1475 TEMP.PRECEDENCE_1,
1476 TEMP.PRECEDENCE_2,
1477 TEMP.PRECEDENCE_3,
1478 TEMP.PRECEDENCE_4,
1479 TEMP.PRECEDENCE_5,
1480 TEMP.PRECEDENCE_6,
1481 TEMP.PRECEDENCE_7,
1482 TEMP.PRECEDENCE_8,
1483 TEMP.PRECEDENCE_9,
1484 TEMP.PRECEDENCE_10,
1485 COD.ADHOC_FLAG
1486 FROM JAI_AR_TRX_TAX_CALC_T TEMP,
1487 JAI_CMN_TAXES_ALL COD
1488 WHERE LINK_TO_CUST_TRX_LINE_ID = I.CUSTOMER_TRX_LINE_ID
1489 AND TEMP.TAX_ID = COD.TAX_ID;
1490
1491 CURSOR GET_INV_TAX IS
1492 SELECT EXISTING_TYPE_ID, NEW_TYPE_ID, MAPPING_ID
1493 FROM JAI_AR_TRX_SUP_INV_T
1494 WHERE MAPPING_TYPE = 'T'
1495 AND CUSTOMER_TRX_ID = FETCH_INV.CUSTOMER_TRX_ID;
1496
1497 BEGIN
1498
1499 T_mapping_id := null;
1500
1501 v_statement_id := '56';
1502
1503 INSERT INTO JAI_AR_TRX_TAX_CALC_T(
1504 TAX_LINE_NO,
1505 CUSTOMER_TRX_LINE_ID,
1506 LINK_TO_CUST_TRX_LINE_ID,
1507 PRECEDENCE_1,
1508 PRECEDENCE_2,
1509 PRECEDENCE_3,
1510 PRECEDENCE_4,
1511 PRECEDENCE_5,
1512 PRECEDENCE_6,
1513 PRECEDENCE_7,
1514 PRECEDENCE_8,
1515 PRECEDENCE_9,
1516 PRECEDENCE_10,
1517 TAX_ID,
1518 TAX_RATE,
1519 QTY_RATE,
1520 UOM,
1521 TAX_AMOUNT,
1522 INVOICE_CLASS,
1523 FUNC_TAX_AMOUNT,
1524 BASE_TAX_AMOUNT,
1525 CREATION_DATE,
1526 CREATED_BY,
1527 LAST_UPDATE_DATE,
1528 LAST_UPDATED_BY,
1529 LAST_UPDATE_LOGIN
1530 ) SELECT
1531 TAX_LINE_NO,
1532 CUSTOMER_TRX_LINE_ID,
1533 LINK_TO_CUST_TRX_LINE_ID,
1534 PRECEDENCE_1,
1535 PRECEDENCE_2,
1536 PRECEDENCE_3,
1537 PRECEDENCE_4,
1538 PRECEDENCE_5,
1539 PRECEDENCE_6,
1540 PRECEDENCE_7,
1541 PRECEDENCE_8,
1542 PRECEDENCE_9,
1543 PRECEDENCE_10,
1544 TAX_ID,
1545 TAX_RATE,
1546 QTY_RATE,
1547 UOM,
1548 TAX_AMOUNT,
1549 INVOICE_CLASS,
1550 FUNC_TAX_AMOUNT,
1551 BASE_TAX_AMOUNT,
1552 CREATION_DATE,
1553 CREATED_BY,
1554 LAST_UPDATE_DATE,
1555 LAST_UPDATED_BY,
1556 LAST_UPDATE_LOGIN
1557 FROM JAI_AR_TRX_TAX_LINES
1558 WHERE LINK_TO_CUST_TRX_LINE_ID = I.CUSTOMER_TRX_LINE_ID;
1559
1560 v_statement_id := '57';
1561 IF GET_INV_TAX%ISOPEN THEN
1562 CLOSE GET_INV_TAX;
1563 END IF;
1564
1565 v_statement_id := '58';
1566 OPEN GET_INV_TAX;
1567 FETCH GET_INV_TAX INTO T_EXISTING_TYPE_ID, T_NEW_TYPE_ID, T_MAPPING_ID;
1568 IF GET_INV_TAX%FOUND THEN
1569 CLOSE GET_INV_TAX;
1570 v_statement_id := '59';
1571 FOR GTAX IN GET_TAX LOOP
1572 FOR CTAX IN GET_INV_TAX LOOP
1573
1574 IF CTAX.EXISTING_TYPE_ID = GTAX.TAX_ID THEN
1575 TAX_LIST_CHANGE_INDICATOR := 'Y';
1576 v_statement_id := '60';
1577 UPDATE JAI_AR_TRX_TAX_CALC_T
1578 SET TAX_ID = CTAX.NEW_TYPE_ID,
1579 TAX_RATE = (SELECT TAX_RATE FROM JAI_CMN_TAXES_ALL WHERE TAX_ID = CTAX.NEW_TYPE_ID),
1580 UOM = (SELECT UOM FROM JAI_CMN_TAXES_ALL WHERE TAX_ID = CTAX.NEW_TYPE_ID)
1581 WHERE TAX_ID = CTAX.EXISTING_TYPE_ID;
1582 END IF;
1583
1584 END LOOP;
1585 END LOOP;
1586
1587 END IF;
1588
1589 v_statement_id := '61';
1590 IF I.INTERFACE_HEADER_CONTEXT = 'ORDER ENTRY' THEN
1591 IF PRICE_LIST_CHANGE_INDICATOR = 'Y' THEN
1592 v_statement_id := '62';
1593 O_S_P := ASELLING_PRICE;
1594 N_S_P := C_LIST_PRICE;
1595 PASSING_AMOUNT := NAMOUNT;
1596 NEW_LN_AMT := NAMOUNT;
1597 EXST_LN_AMT := OAMOUNT;
1598 ELSIF PRICE_LIST_CHANGE_INDICATOR = 'N' THEN
1599 v_statement_id := '63';
1600 O_S_P := ASELLING_PRICE;
1601 N_S_P := ASELLING_PRICE;
1602 NEW_LN_AMT := OAMOUNT;
1603 EXST_LN_AMT := OAMOUNT;
1604 PASSING_AMOUNT := OAMOUNT;
1605 END IF;
1606
1607 ELSIF I.INTERFACE_HEADER_CONTEXT IS NULL
1608 --Added by zhiwei for Bug#12615714 begin
1609 ---------------------------------------------------------
1610 --or I.INTERFACE_HEADER_CONTEXT = 'OFI TAX IMPORT'
1611 or nvl(I.global_attribute_category,'###') = 'JG.IN.ARXTWMAI.OFI TAX IMPORT'
1612 ---------------------------------------------------------
1613 --Added by zhiwei for Bug#12615714 end
1614 THEN
1615
1616 IF INVOICE_LIST_CHANGE_INDICATOR = 'Y' THEN
1617 v_statement_id := '64';
1618 O_S_P := I_UNIT_SELLING_PRICE;
1619 N_S_P := C_LIST_PRICE;
1620 NEW_LN_AMT := NAMOUNT;
1621 EXST_LN_AMT := OAMOUNT;
1622 PASSING_AMOUNT := NEW_INVOICE_AMOUNT;
1623 ELSIF INVOICE_LIST_CHANGE_INDICATOR = 'N' THEN
1624 v_statement_id := '65';
1625 O_S_P := I_UNIT_SELLING_PRICE;
1626 N_S_P := I_UNIT_SELLING_PRICE;
1627 PASSING_AMOUNT := OLD_INVOICE_AMOUNT;
1628 NEW_LN_AMT := OAMOUNT;
1629 EXST_LN_AMT := OAMOUNT;
1630 END IF;
1631
1632 END IF;
1633
1634 v_statement_id := '66';
1635 IF ASSESSABLE_LIST_CHANGE = 'Y' THEN
1636 PASSING_ASSESSABLE := NEW_ASSESS_VALUE;
1637 O_A_VAL := OLD_ASSESSABLE_VALUE;
1638 N_A_VAL := NEW_ASSESS_VALUE;
1639 ELSIF ASSESSABLE_LIST_CHANGE = 'N' THEN
1640 PASSING_ASSESSABLE := V_ASSESSABLE_VALUE; --23/03/01 Anuradha Parthasarathy
1641 O_A_VAL := OLD_ASSESSABLE_VALUE;
1642 N_A_VAL := OLD_ASSESSABLE_VALUE;
1643 END IF;
1644
1645 v_statement_id := '67';
1646 IF PRICE_LIST_CHANGE_INDICATOR = 'Y'
1647 OR INVOICE_LIST_CHANGE_INDICATOR = 'Y'
1648 OR TAX_LIST_CHANGE_INDICATOR = 'Y'
1649 OR ASSESSABLE_LIST_CHANGE = 'Y'
1650 THEN
1651 pack_assess := passing_assessable*aquantity;
1652 v_statement_id := '68';
1653
1654
1655 jai_ar_sup_trx_pkg.calculate_tax('OE_LINES_UPDATE',ATAX_CATEGORY_ID,I.CUSTOMER_TRX_LINE_ID,
1656 pack_assess ,PASSING_AMOUNT,NVL(FETCH_INV.CONVERSION_RATE,1),
1657 I.Inventory_item_id,Aquantity, AUNIT_CODE,NULL,
1658 NULL, NULL, SYSDATE,fnd_global.user_id,fnd_global.user_id );
1659 END IF;
1660
1661 v_statement_id := '69';
1662 IF ASSESSABLE_LIST_CHANGE = 'Y'
1663 OR PRICE_LIST_CHANGE_INDICATOR = 'Y'
1664 OR INVOICE_LIST_CHANGE_INDICATOR = 'Y'
1665 OR TAX_LIST_CHANGE_INDICATOR = 'Y'
1666 THEN -- 0X2a
1667
1668 v_statement_id := '70';
1669 FOR OLD_TAX_LINE_REC IN GET_TAX LOOP
1670 FOR NEW_TAX_LINE_REC IN GET_NEW_TAX LOOP
1671
1672 IF OLD_TAX_LINE_REC.TAX_LINE_NO = NEW_TAX_LINE_REC.TAX_LINE_NO
1673 AND OLD_TAX_LINE_REC.LINK_TO_CUST_TRX_LINE_ID = NEW_TAX_LINE_REC.LINK_TO_CUST_TRX_LINE_ID
1674 AND OLD_TAX_LINE_REC.TAX_AMOUNT <> NEW_TAX_LINE_REC.TAX_AMOUNT
1675 AND NEW_TAX_LINE_REC.ADHOC_FLAG <> 'Y'
1676 THEN
1677 v_statement_id := '71';
1678 TAX_DIFFER1 := NEW_TAX_LINE_REC.TAX_AMOUNT - OLD_TAX_LINE_REC.TAX_AMOUNT;
1679 OLD_TAX_AMOUNT := OLD_TAX_LINE_REC.TAX_AMOUNT ;
1680 NEW_TAX_AMOUNT := NEW_TAX_LINE_REC.TAX_AMOUNT;
1681
1682 IF TAX_DIFFER1 > 0 THEN
1683 STATUS := 'SI';
1684 ELSIF TAX_DIFFER1 < 0 THEN
1685 STATUS := 'CR';
1686 END IF;
1687
1688 END IF;
1689
1690 v_statement_id := '72';
1691 DECLARE
1692 CURSOR CHECK_SUPP_LINES IS
1693 SELECT 1 FROM JAI_AR_SUP_LINES
1694 WHERE CUSTOMER_TRX_ID = FETCH_INV.CUSTOMER_TRX_ID
1695 AND CUSTOMER_TRX_LINE_ID = I.CUSTOMER_TRX_LINE_ID
1696 AND SUP_INV_TYPE = STATUS;
1697 BEGIN
1698
1699 v_statement_id := '73';
1700 IF CHECK_SUPP_LINES%ISOPEN THEN
1701 CLOSE CHECK_SUPP_LINES;
1702 END IF;
1703
1704 v_statement_id := '74';
1705
1706 OPEN CHECK_SUPP_LINES;
1707 FETCH CHECK_SUPP_LINES INTO CHECK2;
1708
1709 IF CHECK_SUPP_LINES%NOTFOUND AND STATUS IN ('SI','CR')
1710 AND TAX_DIFFER1 <> 0
1711 THEN
1712 /* Commented the below code by JMEENA for bug#7567647
1713 --Added by Bgowrava for Bug#6139693
1714 OPEN c_get_sup_seq_val;
1715 FETCH c_get_sup_seq_val into v_cust_trx_line_no_s;
1716 CLOSE c_get_sup_seq_val; */
1717
1718
1719 v_statement_id := '75';
1720 /*bug#7567647 Replaced v_cust_trx_line_no_s with I.CUSTOMER_TRX_LINE_ID to insert in column CUSTOMER_TRX_LINE_ID and interface_line_Attribute2*/
1721 INSERT INTO JAI_AR_SUP_LINES(
1722 CUSTOMER_TRX_ID, CUSTOMER_TRX_LINE_ID, LINE_NUMBER, SUP_INV_TYPE,
1723 INVENTORY_ITEM_ID, DESCRIPTION, QUANTITY, UNIT_CODE,
1724 TAX_CATEGORY_ID, OLD_ASSESSABLE_VALUE, NEW_ASSESSABLE_VALUE, AL_MAPPING_ID,
1725 LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN, CREATION_DATE, CREATED_BY,interface_line_attribute2
1726 ) VALUES (
1727 FETCH_INV.CUSTOMER_TRX_ID, I.CUSTOMER_TRX_LINE_ID, I.LINE_NUMBER, STATUS,
1728 I.INVENTORY_ITEM_ID, I.DESCRIPTION, AQUANTITY, AUNIT_CODE,
1729 ATAX_CATEGORY_ID, O_A_VAL, N_A_VAL, v_a_mapping_id,
1730 SYSDATE, fnd_global.user_id, NULL, SYSDATE, fnd_global.user_id,I.CUSTOMER_TRX_LINE_ID
1731 );
1732
1733 ELSIF CHECK_SUPP_LINES%FOUND AND STATUS IN ('SI','CR')
1734 AND TAX_DIFFER1 <> 0
1735 THEN
1736
1737 v_statement_id := '76';
1738
1739 UPDATE JAI_AR_SUP_LINES
1740 SET OLD_ASSESSABLE_VALUE = O_A_VAL,
1741 NEW_ASSESSABLE_VALUE = N_A_VAL,
1742 AL_MAPPING_ID = v_a_mapping_id
1743 WHERE CUSTOMER_TRX_ID = FETCH_INV.CUSTOMER_TRX_ID
1744 AND CUSTOMER_TRX_LINE_ID = I.CUSTOMER_TRX_LINE_ID
1745 AND SUP_INV_TYPE = STATUS;
1746 END IF;
1747 CLOSE CHECK_SUPP_LINES;
1748
1749 IF CHECK_SUPP_ASSESS_INV%ISOPEN THEN
1750 CLOSE CHECK_SUPP_ASSESS_INV;
1751 END IF;
1752
1753 v_statement_id := '77';
1754 OPEN CHECK_SUPP_ASSESS_INV(STATUS,FETCH_INV.CUSTOMER_TRX_ID);
1755 FETCH CHECK_SUPP_ASSESS_INV INTO CHECK_ASSESS_INV ;
1756 v_statement_id := '77a';
1757
1758 IF CHECK_SUPP_ASSESS_INV%NOTFOUND AND STATUS IN ('CR','SI')
1759 AND TAX_DIFFER1 <> 0
1760 THEN
1761
1762 v_statement_id := '78';
1763
1764
1765 INSERT INTO JAI_AR_SUP_HDRS_ALL(SUP_HDR_ID,
1766 CUSTOMER_TRX_ID, CUSTOMER_ID, TRX_DATE, SUPP_INV_TYPE,
1767 LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN, CREATION_DATE,
1768 CREATED_BY, SET_OF_BOOKS_ID, ORG_ID, CUSTOMER_SITE, BATCH_ID, SUPP_INV_RAISED_IND, CHOSEN_FOR_PROCESS
1769 ) VALUES ( JAI_AR_SUP_HDRS_ALL_S.nextval,
1770 FETCH_INV.CUSTOMER_TRX_ID, I.BILL_TO_CUSTOMER_ID, FETCH_INV.TRX_DATE, STATUS,
1771 SYSDATE, fnd_global.user_id, NULL, SYSDATE,
1772 fnd_global.user_id, I.SET_OF_BOOKS_ID, I.ORG_ID, I.BILL_TO_SITE_USE_ID, FETCH_INV.BATCH_ID, 'N', 'N'
1773 );--Bgowrava for Bug#6400997, added SUPP_INV_RAISED_IND, CHOSEN_FOR_PROCESS
1774
1775 END IF;
1776
1777 CLOSE CHECK_SUPP_ASSESS_INV;
1778 v_statement_id := '79';
1779
1780 END;
1781
1782 END LOOP;
1783 END LOOP;
1784
1785 END IF; -- 0X2a
1786
1787 v_statement_id := '80';
1788 FOR A IN GET_TAX LOOP -- 1122
1789 v_statement_id := '81';
1790 FOR B IN GET_NEW_TAX LOOP -- 1123
1791
1792 v_statement_id := '82';
1793 TAX_DIFF_AMT := 0;
1794 IF A.TAX_LINE_NO = B.TAX_LINE_NO
1795 AND A.LINK_TO_CUST_TRX_LINE_ID = B.LINK_TO_CUST_TRX_LINE_ID
1796 AND ((A.TAX_ID <> B.TAX_ID) OR (A.TAX_AMOUNT <> B.TAX_AMOUNT))
1797 AND B.ADHOC_FLAG <> 'Y'
1798 THEN
1799
1800 TAX_DIFF_AMT := B.TAX_AMOUNT - A.TAX_AMOUNT;
1801 IF A.TAX_AMOUNT < B.TAX_AMOUNT THEN
1802 STATUS := 'SI';
1803 ELSIF A.TAX_AMOUNT > B.TAX_AMOUNT THEN
1804 STATUS := 'CR';
1805 END IF;
1806
1807 v_statement_id := '83';
1808
1809 INSERT INTO JAI_AR_SUP_TAXES(
1810 TAX_LINE_NO, CUSTOMER_TRX_LINE_ID, LINK_TO_CUST_TRX_LINE_ID, SUP_INV_TYPE,
1811 OLD_TAX_ID, OLD_RATE, OLD_UOM, OLD_QTY_RATE,
1812 NEW_TAX_ID, NEW_RATE, NEW_UOM, NEW_QTY_RATE,
1813 MAPPING_ID,
1814 PRECEDENCE_1, PRECEDENCE_2, PRECEDENCE_3, PRECEDENCE_4, PRECEDENCE_5,
1815 PRECEDENCE_6, PRECEDENCE_7, PRECEDENCE_8, PRECEDENCE_9, PRECEDENCE_10,
1816 EXIST_TAX_AMT, NEW_TAX_AMT,
1817 OLD_BASE_TAX_AMT, NEW_BASE_TAX_AMT, OLD_FUNC_TAX_AMT, NEW_FUNC_TAX_AMT,
1818 DIFF_AMT, LAST_UPDATE_DATE, LAST_UPDATED_BY,
1819 LAST_UPDATE_LOGIN, CREATION_DATE, CREATED_BY
1820 ) VALUES (
1821 A.TAX_LINE_NO, JAI_AR_SUP_TAXES_S.NEXTVAL, A.LINK_TO_CUST_TRX_LINE_ID, STATUS,
1822 A.TAX_ID, A.TAX_RATE, A.UOM, A.QTY_RATE,
1823 B.TAX_ID, B.TAX_RATE, B.UOM, B.QTY_RATE,
1824 T_MAPPING_ID,
1825 B.PRECEDENCE_1, B.PRECEDENCE_2, B.PRECEDENCE_3, B.PRECEDENCE_4, B.PRECEDENCE_5,
1826 B.PRECEDENCE_6, B.PRECEDENCE_7, B.PRECEDENCE_8, B.PRECEDENCE_9, B.PRECEDENCE_10,
1827 A.TAX_AMOUNT, B.TAX_AMOUNT,
1828 A.BASE_TAX_AMOUNT, B.BASE_TAX_AMOUNT, A.FUNC_TAX_AMOUNT, B.FUNC_TAX_AMOUNT,
1829 TAX_DIFF_AMT, SYSDATE, fnd_global.user_id,
1830 NULL, SYSDATE, fnd_global.user_id
1831 );
1832
1833 v_statement_id := '84';
1834 IF A.TAX_LINE_NO = B.TAX_LINE_NO THEN
1835
1836 v_statement_id := '85';
1837 SELECT TAX_TYPE INTO OLD_TAX_TYPE FROM JAI_CMN_TAXES_ALL WHERE TAX_ID = A.TAX_ID;
1838
1839 v_statement_id := '86';
1840 SELECT TAX_TYPE INTO NEW_TAX_TYPE FROM JAI_CMN_TAXES_ALL WHERE TAX_ID = B.TAX_ID;
1841
1842 IF UPPER(OLD_TAX_TYPE) LIKE '%EXCISE%' THEN /*Added UPPER to include all excise type of taxes by mmurtuza for bug 16580917*/
1843 v_statement_id := '87';
1844 UPDATE JAI_AR_SUP_LINES
1845 SET EXIST_EXCISE_AMT = NVL(EXIST_EXCISE_AMT,0) + A.TAX_AMOUNT
1846 WHERE CUSTOMER_TRX_LINE_ID = A.LINK_TO_CUST_TRX_LINE_ID
1847 AND SUP_INV_TYPE = STATUS;
1848
1849 v_statement_id := '88';
1850 UPDATE JAI_AR_SUP_HDRS_ALL
1851 SET EXIST_EXCISE_AMT = NVL(EXIST_EXCISE_AMT,0)+A.TAX_AMOUNT
1852 WHERE CUSTOMER_TRX_ID = FETCH_INV.CUSTOMER_TRX_ID
1853 AND SUPP_INV_TYPE = STATUS;
1854
1855 ELSIF UPPER(OLD_TAX_TYPE) NOT LIKE '%EXCISE%' THEN /*Added UPPER to exclude all excise type of taxes by mmurtuza for bug 16580917*/
1856 v_statement_id := '89';
1857 UPDATE JAI_AR_SUP_LINES
1858 SET EXIST_OTHER_AMT = NVL(EXIST_OTHER_AMT,0) + A.TAX_AMOUNT
1859 WHERE CUSTOMER_TRX_LINE_ID = A.LINK_TO_CUST_TRX_LINE_ID
1860 AND SUP_INV_TYPE = STATUS;
1861
1862 v_statement_id := '90';
1863
1864 UPDATE JAI_AR_SUP_HDRS_ALL
1865 SET EXIST_OTHER_AMT = NVL(EXIST_OTHER_AMT,0)+A.TAX_AMOUNT
1866 WHERE CUSTOMER_TRX_ID = FETCH_INV.CUSTOMER_TRX_ID
1867 AND SUPP_INV_TYPE = STATUS;
1868 END IF;
1869
1870 IF UPPER(NEW_TAX_TYPE) LIKE '%EXCISE%' THEN /*Added UPPER to include all excise type of taxes by mmurtuza for bug 16580917*/
1871 v_statement_id := '91';
1872 UPDATE JAI_AR_SUP_LINES
1873 SET NEW_EXCISE_AMT = NVL(NEW_EXCISE_AMT,0) + B.TAX_AMOUNT
1874 WHERE CUSTOMER_TRX_LINE_ID = A.LINK_TO_CUST_TRX_LINE_ID
1875 AND SUP_INV_TYPE = STATUS;
1876
1877 v_statement_id := '92';
1878
1879 UPDATE JAI_AR_SUP_HDRS_ALL
1880 SET NEW_EXCISE_AMT = NVL(NEW_EXCISE_AMT,0)+B.TAX_AMOUNT
1881 WHERE CUSTOMER_TRX_ID = FETCH_INV.CUSTOMER_TRX_ID
1882 AND SUPP_INV_TYPE = STATUS;
1883
1884 ELSIF UPPER(OLD_TAX_TYPE) NOT LIKE '%EXCISE%' THEN /*Added UPPER to exclude all excise type of taxes by mmurtuza for bug 16580917*/
1885
1886 v_statement_id := '93';
1887 UPDATE JAI_AR_SUP_LINES
1888 SET NEW_OTHER_AMT = NVL(NEW_OTHER_AMT,0) + B.TAX_AMOUNT
1889 WHERE CUSTOMER_TRX_LINE_ID = A.LINK_TO_CUST_TRX_LINE_ID
1890 AND SUP_INV_TYPE = STATUS;
1891
1892 v_statement_id := '94';
1893
1894 UPDATE JAI_AR_SUP_HDRS_ALL
1895 SET NEW_OTHER_AMT = NVL(NEW_OTHER_AMT,0)+B.TAX_AMOUNT
1896 WHERE CUSTOMER_TRX_ID = FETCH_INV.CUSTOMER_TRX_ID
1897 AND SUPP_INV_TYPE = STATUS;
1898
1899 END IF;
1900 END IF;
1901
1902 END IF;
1903
1904 v_statement_id := '95';
1905 /* Replaced v_cust_trx_line_no_s with A.LINK_TO_CUST_TRX_LINE_ID for bug 7567647 by JMEENA */
1906 UPDATE JAI_AR_SUP_LINES
1907 SET EXCISE_DIFF_AMT = NVL(NEW_EXCISE_AMT,0) - NVL(EXIST_EXCISE_AMT,0),
1908 OTHER_DIFF_AMT = NVL(NEW_OTHER_AMT,0) - NVL(EXIST_OTHER_AMT,0)
1909 WHERE CUSTOMER_TRX_LINE_ID = A.LINK_TO_CUST_TRX_LINE_ID;
1910
1911 v_statement_id := '96';
1912
1913 UPDATE JAI_AR_SUP_HDRS_ALL
1914 SET EXCISE_DIFF_AMT = NVL(NEW_EXCISE_AMT,0) - NVL(EXIST_EXCISE_AMT,0),
1915 OTHER_DIFF_AMT = NVL(NEW_OTHER_AMT,0) - NVL(EXIST_OTHER_AMT,0)
1916 WHERE CUSTOMER_TRX_ID = FETCH_INV.CUSTOMER_TRX_ID;
1917
1918 END LOOP; -- 1123
1919 END LOOP; -- 1122
1920
1921 -- END LOOP;
1922 END; -- 00X2
1923
1924 END IF; -- 000aa1 -- added by sriram - bug # 3143813
1925
1926 END LOOP; -- 0000aa
1927
1928 END;
1929
1930 END IF; -- 0000a1
1931
1932 END LOOP; -- 00000a
1933
1934
1935
1936 v_statement_id := '97';
1937 DELETE JAI_AR_TRX_SUP_INV_T;
1938
1939 v_statement_id := '98';
1940 DELETE JAI_AR_SUP_INV_T;
1941
1942 v_statement_id := '99';
1943 DELETE JAI_AR_TRX_TAX_CALC_T;
1944
1945 v_statement_id := '100';
1946 COMMIT;
1947
1948 /*
1949 EXCEPTION
1950 WHEN OTHERS THEN
1951 FND_FILE.put_line(fnd_file.log, 'Error at Statement_id->'||v_statement_id|| ', and Error is ->'||SQLERRM);
1952 RAISE;
1953 */
1954
1955
1956 /* Added by Ramananda for bug#4407165 */
1957 EXCEPTION
1958 WHEN OTHERS THEN
1959 FND_MESSAGE.SET_NAME('JA','JAI_EXCEPTION_OCCURED');
1960 FND_MESSAGE.SET_TOKEN('JAI_PROCESS_MSG', lv_object_name ||'. Err:'||sqlerrm );
1961 app_exception.raise_exception;
1962
1963 END process_invoices;
1964
1965 PROCEDURE create_invoices
1966 (ERRBUF OUT NOCOPY VARCHAR2,
1967 RETCODE OUT NOCOPY VARCHAR2,
1968 CHSN_FOR_CNSLDT IN VARCHAR2)
1969 IS
1970 v_amount number;
1971 v_conversion_rate number;
1972 v_address number;
1973 v_seq number;
1974 v_sp_diff number;
1975 v_term varchar2(100);
1976 v_invoice_curr_code VARCHAR2(10);
1977 v_sales_rep_id number;
1978 v_term_id number;
1979
1980 /* Added by Ramananda for bug#4407165 */
1981 lv_object_name CONSTANT VARCHAR2(61) := 'jai_ar_sup_trx_pkg.create_invoices';
1982
1983 Cursor GET_SUPP_INV IS
1984 SELECT A.customer_trx_id,
1985 A.chosen_for_process,
1986 A.exist_net_sales_amt,
1987 A.new_net_sales_amt,
1988 A.set_of_books_id,
1989 A.org_id,
1990 B.bill_to_site_use_id,
1991 B.bill_to_customer_id,
1992 A.supp_inv_type,
1993 B.invoice_currency_code,
1994 B.primary_salesrep_id,
1995 B.term_id,
1996 C.organization_id
1997 FROM JAI_AR_SUP_HDRS_ALL A,
1998 RA_CUSTOMER_TRX_ALL B,
1999 JAI_AR_TRXS C
2000 where A.CUSTOMER_TRX_ID = B.CUSTOMER_TRX_ID
2001 AND A.CUSTOMER_TRX_ID = C.CUSTOMER_TRX_ID
2002 AND A.chosen_for_process = 'Y'
2003 AND A.SUPPLEMENTARY_NUM IS NULL;
2004
2005 Cursor get_cnsldt_header is
2006 select distinct set_of_books_id,
2007 customer_id,
2008 customer_site,
2009 supp_inv_type,
2010 org_id
2011 from JAI_AR_SUP_HDRS_ALL;
2012
2013 cursor get_curr(id number,cid number,sid number) is
2014 select distinct invoice_currency_code,
2015 primary_salesrep_id,
2016 term_id
2017 from ra_customer_trx_all
2018 where set_of_books_id = id
2019 and bill_to_customer_id = cid
2020 and bill_to_site_use_id = sid;
2021
2022 Cursor get_inv_cnsldt(sob_id number,cust_id number,cust_site number,inv_type varchar2) is
2023 select sum(nvl(a.new_line_amt,0) - nvl(a.exist_line_amt,0)) AMT,
2024 B.SET_OF_BOOKS_ID,
2025 B.CUSTOMER_SITE,
2026 B.CUSTOMER_ID,
2027 B.supp_INV_TYPE ,
2028 INVENTORY_ITEM_ID,
2029 sum(QUANTITY) quant,
2030 a.description,
2031 a.unit_code, --2001/04/10 Anuradha Parthasarathy
2032 c.organization_id, --2001/05/10 Anuradha Parthasarathy,
2033 a.interface_line_attribute2
2034 from JAI_AR_SUP_HDRS_ALL b,
2035 JAI_AR_SUP_LINES a,
2036 JAI_AR_TRXS c
2037 where a.customer_trx_id = b.customer_trx_id
2038 and a.customer_trx_id = c.customer_trx_id
2039 and a.sup_inv_type = b.supp_inv_type
2040 and b.chosen_for_process = 'Y'
2041 and b.set_of_books_id = sob_id
2042 and b.customer_id = cust_id
2043 and b.customer_site = cust_site
2044 and b.supp_inv_type = inv_type
2045 and b.supp_inv_raised_ind = 'N'
2046 and b.SUPPLEMENTARY_NUM is null
2047 and b.supp_inv_date is null
2048 group by b.set_of_books_id,
2049 b.customer_site ,
2050 b.customer_id,
2051 a.inventory_item_id,
2052 b.supp_inv_type,
2053 a.description,
2054 a.unit_code,
2055 c.organization_id --Added by Nagaraj.s for Bug#3110461
2056 ,a.interface_line_attribute2
2057 ; --2001/04/10 Anuradha Parthasarathy
2058
2059
2060 cursor get_cnsldt_crdt_amt(sob_id number,cid number,sid number,item_id number,sup_inv_type varchar2) is
2061 select sum (C.REVENUE_AMOUNT_SPLIT) AMT,
2062 B.SET_OF_BOOKS_ID,
2063 B.CUSTOMER_SITE,
2064 B.CUSTOMER_ID,
2065 B.supp_INV_TYPE ,
2066 a.INVENTORY_ITEM_ID,
2067 a.interface_line_attribute2
2068 from JAI_AR_SUP_HDRS_ALL b,
2069 JAI_AR_SUP_LINES a,
2070 RA_CUST_TRX_LINE_SALESREPS_ALL C
2071 where a.customer_trx_id = b.customer_trx_id
2072 and a.sup_inv_type = b.supp_inv_type
2073 and c.customer_trx_line_id = a.customer_Trx_line_id
2074 and b.set_of_books_id = sob_id
2075 and b.customer_site = cid
2076 and b.customer_site = sid
2077 and a.inventory_item_id = item_id
2078 and a.sup_inv_type = sup_inv_type
2079 group by b.set_of_books_id,
2080 b.customer_site ,
2081 b.customer_id,
2082 a.inventory_item_id,
2083 b.supp_inv_type,
2084 a.description;
2085
2086 cursor get_cnsldt_crdt_rep_id(sob_id number,cid number,sid number,invnt_item_id number,sup_inv varchar2) is
2087 select sum (C.REVENUE_AMOUNT_SPLIT) REVENUE_AMT,
2088 c.salesrep_id,
2089 B.SET_OF_BOOKS_ID,
2090 B.CUSTOMER_SITE,
2091 B.CUSTOMER_ID,
2092 B.supp_INV_TYPE ,
2093 INVENTORY_ITEM_ID,
2094 D.SALES_CREDIT_TYPE_ID
2095 from JAI_AR_SUP_HDRS_ALL b,
2096 JAI_AR_SUP_LINES a,
2097 RA_CUST_TRX_LINE_SALESREPS_ALL C,
2098 ra_salesreps_all d
2099 where a.customer_trx_id = b.customer_trx_id
2100 and a.sup_inv_type = b.supp_inv_type
2101 and c.customer_trx_line_id = a.customer_Trx_line_id
2102 and c.salesrep_id = d.salesrep_id
2103 and b.set_of_books_id = sob_id
2104 and b.customer_id = cid
2105 and b.customer_site = sid
2106 and b.supp_inv_type = sup_inv
2107 and a.inventory_item_id = invnt_item_id
2108 group by b.set_of_books_id,
2109 b.customer_site ,
2110 b.customer_id,
2111 a.inventory_item_id,
2112 b.supp_inv_type,
2113 a.description,
2114 c.salesrep_iD,
2115 D.SALES_CREDIT_TYPE_ID;
2116 /* Removed ra_site_uses_all and used hz_cust_site_uses_all for Bug# 4434287 */
2117 cursor get_address(site_id number) is
2118 select cust_acct_site_id address_id
2119 from hz_cust_site_uses_all
2120 where site_use_id = site_id;
2121
2122 cursor GET_SUPP_INV_LINES(INV NUMBER,TYPE VARCHAR2) IS
2123 SELECT K.customer_trx_line_id,
2124 K. OLD_SELLING_PRICE,
2125 K.NEW_SELLING_PRICE,
2126 K.quantity,
2127 K.inventory_item_id,
2128 K.description,
2129 k.pl_mapping_id,
2130 k.exist_line_amt,
2131 k.new_line_amt,
2132 k.unit_code, --2001/04/10 Anuradha Parthasarathy
2133 k.interface_line_attribute2 --4499078
2134 FROM JAI_AR_SUP_LINES K
2135 WHERE K.CUSTOMER_TRX_ID = INV
2136 AND K.SUP_INV_TYPE = TYPE;
2137
2138 CURSOR GET_SALES_CRDT(INVLNO NUMBER) IS
2139 SELECT DISTINCT A.salesrep_id,
2140 A.revenue_percent_split,
2141 C.sales_credit_type_id,
2142 A.cust_trx_line_salesrep_id
2143 FROM RA_CUST_TRX_LINE_SALESREPS_ALL A,
2144 JAI_AR_SUP_LINES B,
2145 RA_SALESREPS_ALL C
2146 WHERE A.CUSTOMER_TRX_LINE_ID =INVLNO
2147 AND C.salesrep_id = A.salesrep_id;
2148
2149 CURSOR GET_CONVERSION_RATE(id number) IS
2150 SELECT conversion_rate
2151 FROM JAI_AR_SUP_CHANGES_ALL
2152 where mapping_id = id;
2153
2154 CURSOR GET_TERM(id number) is
2155 SELECT name
2156 FROM ra_terms
2157 WHERE term_id = id;
2158
2159
2160 lv_line_type varchar2(10);
2161 lv_user varchar2(10);
2162 lv_india_inv varchar2(20);
2163 lv_loc varchar2(20);
2164
2165
2166 cursor c_legal_entity_id (cp_org_id NUMBER) IS
2167 SELECT XLE_UTILITIES_GRP.Get_DefaultLegalContext_OU( cp_org_id )
2168 from dual;
2169
2170 /* Bug 4906958. Added by Lakshmi Gopalsami
2171 Get the value of legal entity from hr_operating_units
2172 if the above cursor is returning null.
2173 */
2174 /* Bug 5243532. Added by Lakshmi Gopalsami
2175 Removed the cursor c_get_default_LE_id which
2176 is referring to hr_operating_units
2177 and replaced with caching logic.
2178 Declared variable for implementing caching logic.
2179 */
2180 l_func_curr_det jai_plsql_cache_pkg.func_curr_details;
2181
2182 ln_legal_entity_id NUMBER;
2183
2184
2185 BEGIN
2186
2187
2188 -- 2:56 PM 8/16/99
2189
2190 /*------------------------------------------------------------------------------------------
2191 FILENAME: jai_ar_sup_trx_pkg.create_invoices.sql
2192
2193 CHANGE HISTORY:
2194 S.No Date Author and Details
2195 1 2001/04/10 Anuradha Parthasarathy
2196 Uom_Code inserted into Ra_Interface_Lines_All to ensure
2197 that Standard Lines used in Auto Accounting is supported
2198
2199 2 2001/05/10 Anuradha Parthasarathy
2200 Warehouse id added to make auto accounting to be organization specific.
2201
2202 3 2003/08/25 Nagaraj.s For Bug#3110461 Version : 616.1
2203 Organization_id Added in the Group by Clause of cursor get_inv_cnsldt.
2204 16/04/2007 Kunkumar for bugno 5989740 version 115.6.6107.2
2205 Forward porting to R12
2206 --------------------------------------------------------------------------------------------*/
2207
2208
2209
2210 IF CHSN_FOR_CNSLDT = 'N' OR CHSN_FOR_CNSLDT IS NULL THEN
2211 FOR SUPP_INV IN GET_SUPP_INV
2212 LOOP
2213 if supp_inv.supp_inv_type = 'CR' then
2214 --select JAI_AR_SUP_HDRS_ALL_S3.nextval into v_seq from dual;
2215 select JAI_AR_SUP_HDRS_ALL_S1.nextval into v_seq from dual;
2216 elsif supp_inv.supp_inv_type = 'DB' then
2217 --select JAI_AR_SUP_HDRS_ALL_S2.nextval into v_seq from dual;
2218 select JAI_AR_SUP_HDRS_ALL_S1.nextval into v_seq from dual;
2219 elsif supp_inv.supp_inv_type = 'SI' then
2220 select JAI_AR_SUP_HDRS_ALL_S1.nextval into v_seq from dual;
2221 end if ;
2222 open get_address(supp_inv.bill_to_site_use_id);
2223 fetch get_address into v_address;
2224 close get_address;
2225
2226
2227
2228 FOR SUPP_INV_LINE IN GET_SUPP_INV_LINES(SUPP_INV.CUSTOMER_TRX_ID,SUPP_INV.SUPP_INV_TYPE)
2229 LOOP
2230 if not get_conversion_rate%isopen then
2231 open get_conversion_rate(supp_inv_line.pl_mapping_id);
2232 end if;
2233 fetch get_conversion_rate into v_conversion_rate;
2234 close get_conversion_rate;
2235
2236
2237
2238 v_amount := NVL(supp_inv_line.new_line_amt,0) - NVL(supp_inv_line.exist_line_amt,0);
2239 v_sp_diff := supp_inv_line.NEW_SELLING_PRICE - supp_inv_line.OLD_SELLING_PRICE;
2240 open get_term(supp_inv.term_id);
2241 fetch get_term into v_term;
2242 close get_term;
2243
2244 /* added by ssumaith - for LE - bug# 4448789*/
2245 OPEN c_legal_entity_id(SUPP_INV.org_id);
2246 FETCH c_legal_entity_id into ln_legal_entity_id;
2247 close c_legal_entity_id;
2248 /* ends here additions by ssumaith - for LE - bug# 4448789*/
2249
2250 /* Bug 4906958. Added by Lakshmi Gopalsami
2251 If ln_legal_entity_id is null fetch the legal entity id
2252 from the default_legal_context_id from hr_operating_units
2253 */
2254
2255 If nvl(ln_legal_entity_id,-1) = -1 THEN
2256 /* bug 5243532. Added by Lakshmi Gopalsami
2257 Removed the reference to cursor c_get_default_LE_id
2258 and implemented caching logic.
2259 */
2260 l_func_curr_det := jai_plsql_cache_pkg.return_sob_curr
2261 (p_org_id =>SUPP_INV.org_id);
2262 ln_legal_entity_id := l_func_curr_det.legal_entity;
2263 END IF;
2264
2265 INSERT INTO RA_INTERFACE_LINES_ALL
2266 (amount,
2267 inventory_item_id,
2268 description,
2269 uom_code, --2001/04/10 Anuradha Parthasarathy
2270 orig_system_bill_customer_id,
2271 orig_system_bill_address_id,
2272 set_of_books_id,
2273 trx_date,
2274 trx_number,
2275 batch_source_name,
2276 cust_trx_type_name,
2277 line_type,
2278 conversion_rate,
2279 conversion_type,
2280 interface_line_context,
2281 interface_line_attribute3,
2282 interface_line_attribute2,
2283 currency_code,
2284 primary_salesrep_id,
2285 tax_code,
2286 term_id,
2287 term_name,
2288 interface_line_attribute1,
2289 interface_line_attribute4, -- Added by Brathod, bug 4392001
2290 INTERFACE_LINE_ATTRIBUTE10, --2001/05/10 Anuradha Parthasarathy
2291 warehouse_id, --2001/05/10 Anuradha Parthasarathy
2292 quantity,
2293 unit_selling_price,
2294 created_by,
2295 creation_date,
2296 last_updated_by,
2297 last_update_date,
2298 last_update_login,
2299 legal_entity_id , -- added by ssumaith - for LE - Bug# 4448789
2300 org_id -- added by ssumaith - for LE - Bug# 4448789
2301 )
2302 values (v_amount,
2303 supp_inv_line.inventory_item_id,
2304 SUPP_INV_LINE.DESCRIPTION,
2305 supp_inv_line.unit_code, --2001/04/10 Anuradha Parthasarathy
2306 supp_inv.bill_to_customer_id,
2307 v_address,
2308 supp_inv.set_of_books_id,
2309 null, -- sysdate,
2310 v_seq,
2311 decode(supp_inv.supp_inv_type,'DB','Supplement Debit Memo','CR','Supplement Credit Memo',
2312 'SI','Supplement Invoice'),
2313 decode(supp_inv.supp_inv_type,'DB','Supplement DM','CR','Supplement CM',
2314 'SI','Supplement Invoice'),
2315 'LINE',
2316 NVL(v_conversion_rate,1),
2317 'User',
2318 /* Commented by Brathod for Bug# 4392001
2319 -decode(supp_inv.supp_inv_type,'DB','SUPPLEMENT DM','CR','SUPPLEMENT CM',
2320 'SI','SUPPLEMENT INVOICE'), -- Context
2321 */
2322 'INDIA INVOICES', -- Added by brathod, Bug 4392001
2323 supp_inv_line.customer_trx_line_id, -- attribute3
2324 supp_inv_line.interface_line_attribute2, -- attribute2
2325 supp_inv.invoice_currency_code,
2326 supp_inv.primary_salesrep_id,
2327 'Localization',
2328 decode(supp_inv.supp_inv_type,'DB',supp_inv.term_id,'SI',supp_inv.term_id,null),
2329 decode(supp_inv.supp_inv_type,'CR',NULL,v_term),
2330 decode(supp_inv.supp_inv_type,'DB','SUPPLEMENT DM','CR','SUPPLEMENT CM',
2331 'SI','SUPPLEMENT INVOICE'), -- supp_inv.customer_trx_id ,Brathod, Bug# 4392001
2332 supp_inv.customer_trx_id, -- Added by Brathod for Bug# 4392001
2333 supp_inv.organization_id,
2334 supp_inv.organization_id,
2335 supp_inv_line.quantity,
2336 decode(v_amount,0,0,v_sp_diff),
2337 fnd_global.user_id,
2338 sysdate,
2339 fnd_global.user_id,
2340 sysdate,
2341 null ,
2342 ln_legal_entity_id ,
2343 SUPP_INV.org_id
2344 );
2345
2346 FOR SALES_CRDT IN GET_SALES_CRDT(supp_inv_line.customer_trx_line_id)
2347 LOOP
2348 INSERT INTO RA_INTERFACE_SALESCREDITS_ALL
2349 (sales_credit_percent_split,
2350 salesrep_id,
2351 /* Commented By Brathod, For Bug# 4392001
2352 -- interface_line_context,
2353 -- interface_line_attribute1,
2354 -- interface_line_attribute2,
2355 -- interface_line_attribute3,
2356 End of Bug# 4392001 */
2357 sales_credit_type_id,
2358 created_by,
2359 creation_date,
2360 last_updated_by,
2361 last_update_date)
2362 values (sales_crdt.revenue_percent_split,
2363 sales_crdt.salesrep_id,
2364 /* Commented By Brathod, For Bug# 4392001
2365 decode(supp_inv.supp_inv_type,'DB','SUPPLEMENT DM','CR','SUPPLEMENT CM',
2366 'SI','SUPPLEMENT INVOICE'),
2367 supp_inv.customer_trx_id,
2368 supp_inv_line.interface_line_attribute2,
2369 supp_inv_line.customer_trx_line_id,
2370 End of Bug# 4392001 */
2371 sales_crdt.sales_credit_type_id,
2372 fnd_global.user_id,
2373 sysdate,
2374 fnd_global.user_id,
2375 sysdate);
2376 END LOOP;
2377 END LOOP;
2378
2379 UPDATE JAI_AR_SUP_HDRS_ALL
2380 SET SUPPLEMENTARY_NUM = V_SEQ,
2381 SUPP_INV_DATE = SYSDATE,
2382 SUPP_INV_RAISED_IND = 'Y',
2383 LAST_UPDATE_DATE = SYSDATE,
2384 LAST_UPDATED_BY = fnd_global.user_id
2385 WHERE CUSTOMER_TRX_ID = SUPP_INV.CUSTOMER_TRX_ID
2386 AND SUPP_INV_TYPE = SUPP_INV.SUPP_INV_TYPE
2387 AND CHOSEN_FOR_PROCESS = 'Y';
2388 END LOOP;
2389 COMMIT;
2390 Elsif CHSN_FOR_CNSLDT = 'Y' THEN
2391 for cnsldt_header in get_cnsldt_header
2392 loop
2393 IF cnsldt_header.supp_inv_type = 'CR' then
2394 --select JAI_AR_SUP_HDRS_ALL_S3.nextval into v_seq from dual;
2395 select JAI_AR_SUP_HDRS_ALL_S1.nextval into v_seq from dual;
2396 elsif cnsldt_header.supp_inv_type = 'DB' then
2397 --select JAI_AR_SUP_HDRS_ALL_S2.nextval into v_seq from dual;
2398 select JAI_AR_SUP_HDRS_ALL_S1.nextval into v_seq from dual;
2399 elsif cnsldt_header.supp_inv_type = 'SI' then
2400 select JAI_AR_SUP_HDRS_ALL_S1.nextval into v_seq from dual;
2401 end if ;
2402
2403 for inv_cnsldt in get_inv_cnsldt(cnsldt_header.set_of_books_id,cnsldt_header.customer_id,
2404 cnsldt_header.customer_site,cnsldt_header.supp_inv_type)
2405 loop
2406 open get_address(cnsldt_header.customer_site);
2407 fetch get_address into v_address;
2408 close get_address;
2409
2410 open get_curr(cnsldt_header.set_of_books_id,cnsldt_header.customer_id,
2411 cnsldt_header.customer_site);
2412 fetch get_curr into v_invoice_curr_code ,v_sales_rep_id , v_term_id;
2413 close get_curr;
2414
2415 open get_term(v_term_id);
2416 fetch get_term into v_term;
2417 close get_term;
2418
2419 v_conversion_rate := NVL(v_conversion_rate,1) ;
2420 lv_line_type := 'LINE' ;
2421 lv_user := 'User' ;
2422 lv_india_inv := 'INDIA INVOICES' ;
2423 lv_loc := 'Localization' ;
2424
2425
2426 /* added by ssumaith - for LE - bug# 4448789*/
2427 OPEN c_legal_entity_id(cnsldt_header.org_id);
2428 FETCH c_legal_entity_id into ln_legal_entity_id;
2429 close c_legal_entity_id;
2430 /* ends here additions by ssumaith - for LE - bug# 4448789*/
2431
2432 /* Bug 4906958. Added by Lakshmi Gopalsami
2433 If ln_legal_entity_id is null fetch the legal entity id
2434 from the default_legal_context_id from hr_operating_units
2435 */
2436
2437 If nvl(ln_legal_entity_id,-1) = -1 THEN
2438 /* bug 5243532. Added by Lakshmi Gopalsami
2439 Removed the reference to cursor c_get_default_LE_id
2440 and implemented caching logic.
2441 */
2442 l_func_curr_det := jai_plsql_cache_pkg.return_sob_curr
2443 (p_org_id =>cnsldt_header.org_id);
2444 ln_legal_entity_id := l_func_curr_det.legal_entity;
2445 END IF;
2446
2447 INSERT INTO RA_INTERFACE_LINES_ALL
2448 (amount,
2449 inventory_item_id,
2450 description,
2451 uom_code, --2001/04/10 Anuradha Parthasarathy
2452 orig_system_bill_customer_id,
2453 orig_system_bill_address_id,
2454 set_of_books_id,
2455 trx_date,
2456 trx_number,
2457 batch_source_name,
2458 cust_trx_type_name,
2459 line_type,
2460 conversion_rate,
2461 conversion_type,
2462 interface_line_context,
2463 interface_line_attribute3,
2464 interface_line_attribute2,
2465 currency_code,
2466 primary_salesrep_id,
2467 tax_code,
2468 term_id,
2469 term_name,
2470 interface_line_attribute1,
2471 interface_line_attribute4, -- Added by brathod, Bug 4392001
2472 INTERFACE_LINE_ATTRIBUTE10, --2001/05/10 Anuradha Parthasarathy
2473 warehouse_id, --2001/05/10 Anuradha Parthasarathy
2474 quantity,
2475 unit_selling_price,
2476 created_by,
2477 creation_date,
2478 last_updated_by,
2479 last_update_date,
2480 last_update_login,
2481 org_id , /* added by ssumaith - LE - Bug # 4448789 */
2482 legal_entity_id /* added by ssumaith - LE - Bug # 4448789 */
2483 )
2484 values (inv_cnsldt.AMT,
2485 inv_cnsldt.inventory_item_id,
2486 inv_cnsldt.DESCRIPTION,
2487 inv_cnsldt.unit_code, --2001/04/10 Anuradha Parthasarathy
2488 inv_cnsldt.customer_id,
2489 v_address,
2490 inv_cnsldt.set_of_books_id,
2491 null, -- sysdate,
2492 v_seq,
2493 decode(inv_cnsldt.supp_inv_type,'DB','Supplement Debit Memo',
2494 'CR','Supplement Credit Memo','SI','Supplement Invoice'),
2495 decode(inv_cnsldt.supp_inv_type,'DB','Supplement DM','CR','Supplement CM',
2496 'SI','Supplement Invoice'),
2497 lv_line_type, --'LINE',
2498 v_conversion_rate, --NVL(v_conversion_rate,1),
2499 lv_user, --'User',
2500 /* Commented By Brathod, Bug 4392001
2501 decode(inv_cnsldt.supp_inv_type,'DB','SUPPLEMENT DM','CR','SUPPLEMENT CM',
2502 'SI','SUPPLEMENT INVOICE'),
2503 End of Bug# 4392001 */
2504 lv_india_inv, --'INDIA INVOICES', -- Added by brathod, Bug 4392001
2505 NULL,
2506 inv_cnsldt.interface_line_attribute2 ,
2507 v_invoice_curr_code ,
2508 v_sales_rep_id ,
2509 lv_loc, --'Localization',
2510 decode(inv_cnsldt.supp_inv_type,'DB',v_term_id,'SI', v_term_id,null),
2511 decode(inv_cnsldt.supp_inv_type,'CR',NULL,v_term),
2512 /* Added By Brathod for Bug# 4392001 */
2513 decode(inv_cnsldt.supp_inv_type,'DB','SUPPLEMENT DM','CR','SUPPLEMENT CM',
2514 'SI','SUPPLEMENT INVOICE'), -- attribute1
2515 null, -- attribute4
2516 inv_cnsldt.organization_id, --2001/05/10 Anuradha Parthasarathy
2517 inv_cnsldt.organization_id, --2001/05/10 Anuradha Parthasarathy
2518 inv_cnsldt.quant,
2519 ( inv_cnsldt.AMT/inv_cnsldt.quant),
2520 fnd_global.user_id,
2521 sysdate,
2522 fnd_global.user_id,
2523 sysdate,
2524 null,
2525 cnsldt_header.org_id,
2526 ln_legal_entity_id
2527 );
2528
2529 for cnsldt_crdt_amt in get_cnsldt_crdt_amt(cnsldt_header.set_of_books_id, cnsldt_header.customer_id,
2530 cnsldt_header.customer_site,inv_cnsldt.inventory_item_id,
2531 inv_cnsldt.supp_inv_type)
2532 loop
2533 for cnsldt_crdt_rep in get_cnsldt_crdt_rep_id(cnsldt_crdt_amt.set_of_books_id,cnsldt_crdt_amt.customer_id,
2534 cnsldt_crdt_amt.customer_site,cnsldt_crdt_amt.inventory_item_id,
2535 cnsldt_crdt_amt.supp_inv_type)
2536 loop
2537
2538 INSERT INTO RA_INTERFACE_SALESCREDITS_ALL
2539 (sales_credit_percent_split,
2540 salesrep_id,
2541 /* Commented By Brathod for Bug# 4392001
2542 interface_line_context,
2543 interface_line_attribute1,
2544 interface_line_attribute2,
2545 interface_line_attribute3,
2546 End Of Bug# 4392001*/
2547 sales_credit_type_id,
2548 created_by,
2549 creation_date,
2550 last_updated_by,
2551 last_update_date)
2552 values ((cnsldt_crdt_amt.AMT/cnsldt_crdt_rep.REVENUE_AMT)*100,
2553 cnsldt_crdt_rep.salesrep_id,
2554 /* Commented By Brathod for Bug# 4392001
2555 decode(cnsldt_crdt_AMT.supp_inv_type,'DB','SUPPLEMENT DM','CR','SUPPLEMENT CM',
2556 'SI','SUPPLEMENT INVOICE'),
2557 NULL,
2558 cnlsdt_crdt_amt.interface_line_attribute2,
2559 NULL,
2560 End of Bug# 4392001*/
2561 cnsldt_crdt_rep.sales_credit_type_id,
2562 fnd_global.user_id,
2563 sysdate,
2564 fnd_global.user_id,
2565 sysdate);
2566 --null;
2567 end loop;
2568 end loop;
2569 end loop;
2570
2571 UPDATE JAI_AR_SUP_HDRS_ALL
2572 SET SUPPLEMENTARY_NUM = V_SEQ,
2573 SUPP_INV_DATE = SYSDATE,
2574 SUPP_INV_RAISED_IND = 'Y',
2575 LAST_UPDATE_DATE = SYSDATE,
2576 LAST_UPDATED_BY = fnd_global.user_id
2577 WHERE SET_OF_BOOKS_ID = cnsldt_header.set_of_books_id
2578 AND CUSTOMER_ID = cnsldt_header.customer_id
2579 AND CUSTOMER_SITE = cnsldt_header.customer_site
2580 AND supp_inv_type = cnsldt_header.supp_inv_type
2581 AND CHOSEN_FOR_PROCESS = 'Y'
2582 AND SUPPLEMENTARY_NUM IS NULL
2583 AND SUPP_INV_DATE IS NULL
2584 AND SUPP_INV_RAISED_IND = 'N';
2585 end loop;
2586 COMMIT;
2587 end if;
2588
2589
2590 /* Added by Ramananda for bug#4407165 */
2591 EXCEPTION
2592 WHEN OTHERS THEN
2593 FND_MESSAGE.SET_NAME('JA','JAI_EXCEPTION_OCCURED');
2594 FND_MESSAGE.SET_TOKEN('JAI_PROCESS_MSG', lv_object_name ||'. Err:'||sqlerrm );
2595 app_exception.raise_exception;
2596
2597 END create_invoices;
2598 PROCEDURE calculate_tax( transaction_name VARCHAR2 ,
2599 P_tax_category_id NUMBER ,
2600 p_line_id NUMBER ,
2601 p_assessable_value NUMBER default 0 ,
2602 p_tax_amount IN OUT NOCOPY NUMBER ,
2603 p_currency_conv_factor NUMBER ,
2604 p_inventory_item_id NUMBER ,
2605 p_line_quantity NUMBER ,
2606 p_uom_code VARCHAR2 ,
2607 p_currency VARCHAR2 ,
2608 p_creation_date DATE ,
2609 p_created_by NUMBER ,
2610 p_last_update_date DATE ,
2611 p_last_updated_by NUMBER ,
2612 p_last_update_login NUMBER
2613 )
2614 IS
2615 TYPE num_tab IS TABLE OF NUMBER INDEX BY BINARY_INTEGER ;
2616 TYPE tax_amt_num_tab IS TABLE OF NUMBER INDEX BY BINARY_INTEGER ;
2617 p1 NUM_TAB ;
2618 p2 NUM_TAB ;
2619 p3 NUM_TAB ;
2620 p4 NUM_TAB ;
2621 p5 NUM_TAB ;
2622
2623 -- Date 01-NOV-2006 Bug 5228046 added by SACSETHI ( added column from Precedence 6 to 10 )
2624 -- START BUG 5228046
2625
2626 p6 NUM_TAB ;
2627 p7 NUM_TAB ;
2628 p8 NUM_TAB ;
2629 p9 NUM_TAB ;
2630 p10 NUM_TAB ;
2631
2632 -- END BUG 5228046
2633
2634
2635 tax_rate_tab NUM_TAB ;
2636 tax_type_tab NUM_TAB ;
2637 tax_amt_tab TAX_AMT_NUM_TAB ;
2638 base_tax_amt_tab TAX_AMT_NUM_TAB ;
2639 end_date_tab NUM_TAB ;
2640 bsln_amt NUMBER ; -- := p_tax_amount ; --Ramananda for File.Sql.35
2641 v_conversion_rate NUMBER := 0 ;
2642 v_currency_conv_factor NUMBER ; -- := p_currency_conv_factor ; --Ramananda for File.Sql.35
2643 --v_tax_amt NUMBER(10,2) := 0 ;
2644 --vamt NUMBER(10,2) := 0 ;
2645
2646 /*Commented above two lines and added below two lines by mmurtuza for bug 12844458*/
2647 v_tax_amt NUMBER := 0 ;
2648 vamt NUMBER := 0 ;
2649
2650 v_amt NUMBER ;
2651 row_count NUMBER := 0 ;
2652 counter NUMBER ;
2653 max_iter NUMBER := 10 ;
2654
2655 /* Added by Ramananda for bug#4407165 */
2656 lv_object_name CONSTANT VARCHAR2(61) := 'jai_ar_sup_trx_pkg.calculate_tax';
2657 ln_amount NUMBER; --Added by Shujuan for 10358786 on 28-Jan-2011
2658
2659 /* Added by LGOPALSA. Bug 4210102.
2660 * Added Excise and CVD Education Cess
2661 * */
2662 CURSOR tax_cur( p_line_id NUMBER)
2663 IS
2664 SELECT
2665 c.tax_line_no lno,
2666 c.tax_id,
2667 c.tax_rate,
2668 c.qty_rate,
2669 c.uom uom_code,
2670 c.func_tax_amount,
2671 c.base_tax_amount,
2672 c.precedence_1 p_1,
2673 c.precedence_2 p_2,
2674 c.precedence_3 p_3,
2675 c.precedence_4 p_4,
2676 c.precedence_5 p_5,
2677 c.precedence_6 p_6,
2678 c.precedence_7 p_7,
2679 c.precedence_8 p_8,
2680 c.precedence_9 p_9,
2681 c.precedence_10 p_10,
2682 c.tax_amount,
2683 d.tax_type,
2684 d.end_date valid_date,
2685 decode( upper(d.tax_type),
2686 'EXCISE', 1,
2687 'ADDL. EXCISE', 1,
2688 'OTHER EXCISE', 1,
2689 -- Delete by Jia Li for Tax Inclusive Computations on 2007/12/07, Begin
2690 -----------------------------------------------------------------------
2691 --jai_constants.tax_type_exc_edu_cess,1,
2692 --jai_constants.tax_type_sh_exc_edu_cess,1,--Added higher education cess by kundan kumar for bug#5907436 'TDS', 2,
2693 -----------------------------------------------------------------------
2694 -- Delete by Jia Li for Tax Inclusive Computations on 2007/12/07, End
2695
2696 -- Added by Jia Li for Tax Inclusive Computations on 2007/12/07, Begin
2697 -----------------------------------------------------------------------
2698 'TDS' , 2,
2699 'EXCISE_EDUCATION_CESS', 6,
2700 'CVD_EDUCATION_CESS' , 6,
2701 'EXCISE_SH_EDU_CESS' , 6,
2702 -----------------------------------------------------------------------
2703 -- Added by Jia Li for Tax Inclusive Computations on 2007/12/07, End
2704 0
2705 ) tax_type_val,
2706 d.inclusive_tax_flag -- Added by Jia Li for Tax Inclusive Computations on 2007/12/07
2707 FROM
2708 JAI_AR_TRX_TAX_CALC_T c,
2709 JAI_CMN_TAXES_ALL d
2710 WHERE
2711 c.link_to_cust_trx_line_id = p_line_id AND
2712 c.tax_id = d.tax_id
2713 ORDER BY
2714 c.tax_line_no;
2715
2716
2717 CURSOR uom_class_cur( p_line_uom_code IN VARCHAR2,
2718 p_tax_line_uom_code IN VARCHAR2
2719 )
2720 IS
2721 SELECT
2722 A.uom_class
2723 FROM
2724 mtl_units_of_measure A,
2725 mtl_units_of_measure B
2726 WHERE
2727 A.uom_code = p_line_uom_code AND
2728 B.uom_code = p_tax_line_uom_code AND
2729 A.uom_class = B.uom_class;
2730
2731 -- Added by Jia Li for Tax Inclusive Computations on 2007/12/07, Begin
2732 -----------------------------------------------------------------------
2733 TYPE char_tab IS TABLE OF VARCHAR2(10) INDEX BY BINARY_INTEGER;
2734 tax_amt_rate_tax_tab TAX_AMT_NUM_TAB;
2735 tax_amt_non_rate_tab TAX_AMT_NUM_TAB;
2736 func_tax_amt_tab TAX_AMT_NUM_TAB;
2737 tax_rate_zero_tab NUM_TAB;
2738 tax_rate_per_rupee NUM_TAB;
2739 tax_target_tab NUM_TAB;
2740 inclu_tax_tab CHAR_TAB;
2741 ln_assessable_value NUMBER;
2742 ln_vamt_nr NUMBER(38,10);
2743 ln_bsln_amt_nr NUMBER(38,10);
2744 ln_v_tax_amt_nr NUMBER(38,10);
2745 ln_v_func_tax_amt NUMBER(38,10);
2746 ln_exclusive_price NUMBER(38,10);
2747 ln_total_non_rate_tax NUMBER(38,10);
2748 ln_total_tax_per_rupee NUMBER(38,10);
2749 -----------------------------------------------------------------------
2750 -- Added by Jia Li for Tax Inclusive Computations on 2007/12/07, End
2751
2752 BEGIN
2753
2754
2755
2756 /*------------------------------------------------------------------------------------------
2757 FILENAME: jai_ar_sup_trx_pkg.calculate_tax.sql
2758
2759 CHANGE HISTORY:
2760 S.No Date Author and Details
2761 1 2001/04/04 Anuradha Parthasarathy
2762 Comments added to avoid unneccessary rounding of taxes.
2763
2764 2. 2004/06/14 Aiyer for bug#3659008. Version#115.1
2765 Issue:-
2766 New taxes are not getting calculated properly.
2767
2768 Reason:-
2769 The variable for the plsql tables tax_amt_num_tab and num_tab was number (10,2) and Number(2)
2770 respectively.This was causing rounding of tax amounts hence the reported issue.
2771
2772 Fix:-
2773 This datatype widths have been modified to Number to take care of all this issues.
2774
2775 Dependency Due to this Bug:-
2776 None
2777 3. 2005/03/12 Bug 4210102. Adde by LGOPALSA
2778 Added CVD and Excise education cess
2779 Search for bug number for complete fix details
2780
2781
2782 Future Dependencies For the release Of this Object:-
2783 (Please add a row in the section below only if your bug introduces a dependency due to spec change/ A new call to a object/
2784 A datamodel change )
2785
2786 ----------------------------------------------------------------------------------------------------------------------------------------------------
2787 Current Version Current Bug Dependent Files Version Author Date Remarks
2788 Of File On Bug/Patchset Dependent On
2789 jai_ar_sup_trx_pkg.calculate_tax
2790 ----------------------------------------------------------------------------------------------------------------------------------------------------
2791 115.1 3659008 IN60105D2 None -- Aiyer 14/06/2004 Row introduces to start dependency tracking
2792
2793 115.2 4210102 IN60106 + 4146708 LGOPALSA 12/03/2005 Introduction of Cess dependency
2794 ----------------------------------------------------------------------------------------------------------------------------------------------------*/
2795
2796 --------------------------------------------------------------------------------------------*/
2797 bsln_amt := p_tax_amount ; --Ramananda for File.Sql.35
2798 v_currency_conv_factor := p_currency_conv_factor ; --Ramananda for File.Sql.35
2799
2800 /************** Part - 1 ******************************/
2801 FOR rec in tax_cur( p_line_id) LOOP
2802 p1(rec.lno) := nvl(rec.p_1,-1) ;
2803 p2(rec.lno) := nvl(rec.p_2,-1) ;
2804 p3(rec.lno) := nvl(rec.p_3,-1) ;
2805 p4(rec.lno) := nvl(rec.p_4,-1) ;
2806 p5(rec.lno) := nvl(rec.p_5,-1) ;
2807
2808 -- Date 01-NOV-2006 Bug 5228046 added by SACSETHI ( added column from Precedence 6 to 10 )
2809 -- START BUG 5228046
2810
2811 p6(rec.lno) := nvl(rec.p_6,-1) ;
2812 p7(rec.lno) := nvl(rec.p_7,-1) ;
2813 p8(rec.lno) := nvl(rec.p_8,-1) ;
2814 p9(rec.lno) := nvl(rec.p_9,-1) ;
2815 p10(rec.lno) := nvl(rec.p_10,-1) ;
2816
2817 -- END BUG 5228046
2818
2819
2820 tax_rate_tab(rec.lno) := nvl(rec.tax_rate,0) ;
2821
2822 -- Added by Jia Li for Tax Inclusive Computations on 2007/12/07, Begin
2823 -----------------------------------------------------------------------
2824 tax_rate_per_rupee(rec.lno) := NVL(rec.tax_rate, 0)/100;
2825 ln_total_tax_per_rupee := 0;
2826 inclu_tax_tab(rec.lno) := NVL(rec.inclusive_tax_flag, 'N');
2827
2828 IF rec.tax_rate IS NULL
2829 THEN
2830 tax_rate_zero_tab(rec.lno) := 0;
2831 ELSIF rec.tax_rate = 0
2832 THEN
2833 tax_rate_zero_tab(rec.lno) := -9999;
2834 ELSE
2835 tax_rate_zero_tab(rec.lno) := rec.tax_rate;
2836 END IF; -- rec.tax_rate is null
2837
2838 tax_amt_rate_tax_tab(rec.lno) := 0;
2839 tax_amt_non_rate_tab(rec.lno) := 0; -- for tax inclusive
2840 -----------------------------------------------------------------------
2841 -- Added by Jia Li for Tax Inclusive Computations on 2007/12/07, End
2842
2843 tax_type_tab(rec.lno) := rec.tax_type_val ;
2844 tax_amt_tab(rec.lno) := 0 ;
2845 base_tax_amt_tab(rec.lno) := 0 ;
2846
2847 IF tax_rate_tab(rec.lno) = 0 THEN
2848 FOR uom_cls IN uom_class_cur(p_uom_code, rec.uom_code) LOOP
2849 INV_CONVERT.inv_um_conversion( p_uom_code, rec.uom_code, p_inventory_item_id, v_conversion_rate);
2850 IF nvl(v_conversion_rate, 0) <= 0 THEN
2851 INV_CONVERT.inv_um_conversion( p_uom_code, rec.uom_code, 0, v_conversion_rate);
2852 IF nvl(v_conversion_rate, 0) <= 0 THEN
2853 v_conversion_rate := 0;
2854 END IF;
2855 END IF;
2856 tax_amt_tab(rec.lno) := nvl(rec.qty_rate * v_conversion_rate, 0) * p_line_quantity;
2857
2858 -- Added by Jia Li for Tax Inclusive Computations on 2007/12/07, Begin
2859 -----------------------------------------------------------------------
2860 tax_amt_non_rate_tab(rec.lno) := NVL(rec.tax_amount * v_conversion_rate, 0)
2861 * p_line_quantity;
2862 base_tax_amt_tab(rec.lno) := tax_amt_non_rate_tab(rec.lno);
2863 -----------------------------------------------------------------------
2864 -- Added by Jia Li for Tax Inclusive Computations on 2007/12/07, End
2865
2866 END LOOP;
2867 END IF;
2868
2869 IF rec.valid_date is NULL OR rec.valid_date >= sysdate THEN
2870 end_date_tab(rec.lno) := 1;
2871 ELSE
2872 tax_amt_tab(rec.lno) := 0;
2873 end_date_tab(rec.lno) := 0;
2874 END IF;
2875 row_count := row_count + 1;
2876 END LOOP;
2877
2878 /************** Part - 2 ******************************/
2879 -- Added by Jia Li for Tax Inclusive Computations on 2007/12/07, Begin
2880 -----------------------------------------------------------------------
2881 IF p_assessable_value <> p_tax_amount
2882 THEN
2883 ln_assessable_value := p_assessable_value;
2884 ELSE
2885 ln_assessable_value := 1;
2886 END IF;
2887 -----------------------------------------------------------------------
2888 -- Added by Jia Li for Tax Inclusive Computations on 2007/12/07, End
2889
2890 FOR I in 1..row_count LOOP
2891 -- Delete by Jia Li for Tax Inclusive Computations on 2007/12/07, Begin
2892 -----------------------------------------------------------------------
2893 /*
2894 IF tax_type_tab(I) = 1 THEN
2895 bsln_amt := p_assessable_value;
2896 ELSE
2897 bsln_amt := p_tax_amount;
2898 END IF;
2899 */
2900 -----------------------------------------------------------------------
2901 -- Delete by Jia Li for Tax Inclusive Computations on 2007/12/07, End
2902
2903 -- Added by Jia Li for Tax Inclusive Computations on 2007/12/07, Begin
2904 -----------------------------------------------------------------------
2905 IF end_date_tab(I) <> 0
2906 THEN
2907 IF tax_type_tab(I) = 1
2908 THEN
2909 IF ln_assessable_value = 1
2910 THEN
2911 bsln_amt := 1;
2912 ln_bsln_amt_nr := 0;
2913 ELSE
2914 bsln_amt := 0;
2915 ln_bsln_amt_nr := ln_assessable_value;
2916 END IF;
2917 ELSIF tax_type_tab(I) = 4
2918 THEN
2919 IF ln_assessable_value = 1
2920 THEN
2921 bsln_amt := 1;
2922 ln_bsln_amt_nr := 0;
2923 ELSE
2924 bsln_amt := 0;
2925 ln_bsln_amt_nr := ln_assessable_value;
2926 END IF;
2927 ELSIF tax_type_tab(I) = 6
2928 THEN
2929 bsln_amt := 0;
2930 ln_bsln_amt_nr := 0;
2931 ELSE
2932 bsln_amt := 1;
2933 ln_bsln_amt_nr := 0;
2934 END IF; -- tax_type_tab(I) = 1
2935 -----------------------------------------------------------------------
2936 -- Added by Jia Li for Tax Inclusive Computations on 2007/12/07, End
2937
2938 IF tax_rate_tab(I) <> 0 THEN
2939 /* Commented out by Shyan for bug 10358786 on 28-Jan-2011 , Begin
2940 IF p1(I) < I and p1(I) not in (-1,0) THEN
2941 vamt := vamt + nvl(tax_amt_tab(p1(I)),0);
2942 ln_vamt_nr := ln_vamt_nr + NVL(tax_amt_non_rate_tab(p1(I)), 0); -- Added by Jia Li for Tax Inclusive Computations on 2007/12/07
2943 ELSIF p1(I) = 0 THEN
2944 vamt := vamt + bsln_amt;
2945 ln_vamt_nr := ln_vamt_nr + ln_bsln_amt_nr; -- Added by Jia Li for Tax Inclusive Computations on 2007/12/07
2946 END IF;
2947
2948 IF p2(I) < I and p2(I) not in (-1,0) THEN
2949 vamt := vamt + nvl(tax_amt_tab(p2(I)),0);
2950 ln_vamt_nr := ln_vamt_nr + NVL(tax_amt_non_rate_tab(p2(I)), 0); -- Added by Jia Li for Tax Inclusive Computations on 2007/12/07
2951 ELSIF p2(I) = 0 THEN
2952 vamt := vamt + bsln_amt;
2953 ln_vamt_nr := ln_vamt_nr + ln_bsln_amt_nr; -- Added by Jia Li for Tax Inclusive Computations on 2007/12/07
2954 END IF;
2955
2956 IF p3(I) < I and p3(I) not in (-1,0) THEN
2957 vamt := vamt + nvl(tax_amt_tab(p3(I)),0);
2958 ln_vamt_nr := ln_vamt_nr + NVL(tax_amt_non_rate_tab(p3(I)), 0); -- Added by Jia Li for Tax Inclusive Computations on 2007/12/07
2959 ELSIF p3(I) = 0 THEN
2960 vamt := vamt + bsln_amt;
2961 ln_vamt_nr := ln_vamt_nr + ln_bsln_amt_nr; -- Added by Jia Li for Tax Inclusive Computations on 2007/12/07
2962 END IF;
2963
2964 IF p4(I) < I and p4(I) not in (-1,0) THEN
2965 vamt := vamt + nvl(tax_amt_tab(p4(I)),0);
2966 ln_vamt_nr := ln_vamt_nr + NVL(tax_amt_non_rate_tab(p4(I)), 0); -- Added by Jia Li for Tax Inclusive Computations on 2007/12/07
2967 ELSIF p4(I) = 0 THEN
2968 vamt := vamt + bsln_amt;
2969 ln_vamt_nr := ln_vamt_nr + ln_bsln_amt_nr; -- Added by Jia Li for Tax Inclusive Computations on 2007/12/07
2970 END IF;
2971
2972 IF p5(I) < I and p5(I) not in (-1,0) THEN
2973 vamt := vamt + nvl(tax_amt_tab(p5(I)),0);
2974 ln_vamt_nr := ln_vamt_nr + NVL(tax_amt_non_rate_tab(p5(I)), 0); -- Added by Jia Li for Tax Inclusive Computations on 2007/12/07
2975 ELSIF p5(I) = 0 THEN
2976 vamt := vamt + bsln_amt;
2977 ln_vamt_nr := ln_vamt_nr + ln_bsln_amt_nr; -- Added by Jia Li for Tax Inclusive Computations on 2007/12/07
2978 END IF;
2979
2980
2981 -- Date 01-NOV-2006 Bug 5228046 added by SACSETHI ( added column from Precedence 6 to 10 )
2982 -- START BUG 5228046
2983
2984 IF p6(I) < I and p6(I) not in (-1,0) THEN
2985 vamt := vamt + nvl(tax_amt_tab(p6(I)),0);
2986 ln_vamt_nr := ln_vamt_nr + NVL(tax_amt_non_rate_tab(p6(I)), 0); -- Added by Jia Li for Tax Inclusive Computations on 2007/12/07
2987 ELSIF p6(I) = 0 THEN
2988 vamt := vamt + bsln_amt;
2989 ln_vamt_nr := ln_vamt_nr + ln_bsln_amt_nr; -- Added by Jia Li for Tax Inclusive Computations on 2007/12/07
2990 END IF;
2991
2992 IF p7(I) < I and p7(I) not in (-1,0) THEN
2993 vamt := vamt + nvl(tax_amt_tab(p7(I)),0);
2994 ln_vamt_nr := ln_vamt_nr + NVL(tax_amt_non_rate_tab(p7(I)), 0); -- Added by Jia Li for Tax Inclusive Computations on 2007/12/07
2995 ELSIF p7(I) = 0 THEN
2996 vamt := vamt + bsln_amt;
2997 ln_vamt_nr := ln_vamt_nr + ln_bsln_amt_nr; -- Added by Jia Li for Tax Inclusive Computations on 2007/12/07
2998 END IF;
2999
3000 IF p8(I) < I and p8(I) not in (-1,0) THEN
3001 vamt := vamt + nvl(tax_amt_tab(p8(I)),0);
3002 ln_vamt_nr := ln_vamt_nr + NVL(tax_amt_non_rate_tab(p8(I)), 0); -- Added by Jia Li for Tax Inclusive Computations on 2007/12/07
3003 ELSIF p8(I) = 0 THEN
3004 vamt := vamt + bsln_amt;
3005 ln_vamt_nr := ln_vamt_nr + ln_bsln_amt_nr; -- Added by Jia Li for Tax Inclusive Computations on 2007/12/07
3006 END IF;
3007
3008 IF p9(I) < I and p9(I) not in (-1,0) THEN
3009 vamt := vamt + nvl(tax_amt_tab(p9(I)),0);
3010 ln_vamt_nr := ln_vamt_nr + NVL(tax_amt_non_rate_tab(p9(I)), 0); -- Added by Jia Li for Tax Inclusive Computations on 2007/12/07
3011 ELSIF p9(I) = 0 THEN
3012 vamt := vamt + bsln_amt;
3013 ln_vamt_nr := ln_vamt_nr + ln_bsln_amt_nr; -- Added by Jia Li for Tax Inclusive Computations on 2007/12/07
3014 END IF;
3015
3016 IF p10(I) < I and p10(I) not in (-1,0) THEN
3017 vamt := vamt + nvl(tax_amt_tab(p10(I)),0);
3018 ln_vamt_nr := ln_vamt_nr + NVL(tax_amt_non_rate_tab(p10(I)), 0); -- Added by Jia Li for Tax Inclusive Computations on 2007/12/07
3019 ELSIF p10(I) = 0 THEN
3020 vamt := vamt + bsln_amt;
3021 ln_vamt_nr := ln_vamt_nr + ln_bsln_amt_nr; -- Added by Jia Li for Tax Inclusive Computations on 2007/12/07
3022 END IF;
3023
3024 -- END BUG 5228046
3025 Commented out by Shyan for bug 10358786 on 28-Jan-2011, End*/
3026
3027 --Added by Shujuan for 10358786 on 28-Jan-2011, Begin
3028 -------------------------------------------------------
3029 tax_amt_non_rate_tab(I):=0;
3030 IF P1(I) < I AND P1(I) NOT IN (-1, 0) THEN --replaced I by line_no_tab(I) for bug#9214366
3031 IF inclu_tax_tab(P1(I)) = 'Y' OR tax_amt_rate_tax_tab(P1(I)) IS NOT NULL then
3032 vamt := vamt + NVL(tax_amt_tab(P1(I)), 0);
3033 ln_vamt_nr := ln_vamt_nr + NVL(tax_amt_non_rate_tab(P1(I)), 0); --added by walton for inclusive tax
3034 END IF;
3035
3036 ELSIF P1(I) = 0 THEN
3037 IF inclu_tax_tab(I) = 'Y' then
3038 vamt := vamt + bsln_amt;
3039 ln_vamt_nr := ln_vamt_nr + ln_bsln_amt_nr; --added by walton for inclusive tax
3040 ELSE
3041 ln_vamt_nr := ln_vamt_nr + ln_bsln_amt_nr + p_tax_amount;
3042
3043 END IF;
3044 END IF;
3045 IF p2(I) < I AND p2(I) NOT IN (-1, 0) THEN --replaced I by line_no_tab(I) for bug#9214366
3046 IF inclu_tax_tab(P2(I)) = 'Y' OR
3047 tax_amt_rate_tax_tab(P2(I)) IS NOT NULL then
3048 vamt := vamt + NVL(tax_amt_tab(p2(I)), 0);
3049 ln_vamt_nr := ln_vamt_nr + NVL(tax_amt_non_rate_tab(P2(I)), 0); --added by walton for inclusive tax
3050 END IF;
3051
3052 ELSIF p2(I) = 0 THEN
3053 IF inclu_tax_tab(I) = 'Y' then
3054 vamt := vamt + bsln_amt;
3055 ln_vamt_nr := ln_vamt_nr + ln_bsln_amt_nr; --added by walton for inclusive tax
3056 ELSE
3057 ln_vamt_nr := ln_vamt_nr + ln_bsln_amt_nr + p_tax_amount;
3058 END IF;
3059
3060 END IF;
3061 IF p3(I) < I AND p3(I) NOT IN (-1, 0) THEN --replaced I by line_no_tab(I) for bug#9214366
3062 IF inclu_tax_tab(P3(I)) = 'Y' OR tax_amt_rate_tax_tab(P3(I)) IS NOT NULL then
3063 vamt := vamt + NVL(tax_amt_tab(p3(I)), 0);
3064 ln_vamt_nr := ln_vamt_nr + NVL(tax_amt_non_rate_tab(P3(I)), 0); --added by walton for inclusive tax
3065 END IF;
3066
3067 ELSIF p3(I) = 0 THEN
3068 IF inclu_tax_tab(I) = 'Y' then
3069 vamt := vamt + bsln_amt;
3070 ln_vamt_nr := ln_vamt_nr + ln_bsln_amt_nr; --added by walton for inclusive tax
3071 ELSE
3072 ln_vamt_nr := ln_vamt_nr + ln_bsln_amt_nr + p_tax_amount;
3073 END IF;
3074 END IF;
3075
3076 IF p4(I) < I AND p4(I) NOT IN (-1, 0) THEN --replaced I by line_no_tab(I) for bug#9214366
3077 IF inclu_tax_tab(P4(I)) = 'Y' OR tax_amt_rate_tax_tab(P4(I)) IS NOT NULL then
3078 vamt := vamt + NVL(tax_amt_tab(p4(I)), 0);
3079 ln_vamt_nr := ln_vamt_nr + NVL(tax_amt_non_rate_tab(P4(I)), 0); --added by walton for inclusive tax
3080 END IF;
3081 ELSIF p4(I) = 0 THEN
3082 IF inclu_tax_tab(I) = 'Y' then
3083 vamt := vamt + bsln_amt;
3084 ln_vamt_nr := ln_vamt_nr + ln_bsln_amt_nr; --added by walton for inclusive tax
3085 ELSE
3086 ln_vamt_nr := ln_vamt_nr + ln_bsln_amt_nr + p_tax_amount;
3087 END IF;
3088 END IF;
3089
3090 IF p5(I) < I AND p5(I) NOT IN (-1, 0) THEN --replaced I by line_no_tab(I) for bug#9214366
3091 IF inclu_tax_tab(P5(I)) = 'Y' OR tax_amt_rate_tax_tab(P5(I)) IS NOT NULL then
3092 vamt := vamt + NVL(tax_amt_tab(p5(I)), 0);
3093 ln_vamt_nr := ln_vamt_nr + NVL(tax_amt_non_rate_tab(P5(I)), 0); --added by walton for inclusive tax
3094 END IF;
3095 ELSIF p5(I) = 0 THEN
3096 IF inclu_tax_tab(I) = 'Y' then
3097 vamt := vamt + bsln_amt;
3098 ln_vamt_nr := ln_vamt_nr + ln_bsln_amt_nr; --added by walton for inclusive tax
3099 ELSE
3100 ln_vamt_nr := ln_vamt_nr + ln_bsln_amt_nr + p_tax_amount;
3101 END IF;
3102 END IF;
3103 -- Date 31/10/2006 Bug 5228046 added by SACSETHI ( added column from Precedence 6 to 10 )
3104 -- start bug 5228046
3105 IF p6(I) < I AND p6(I) NOT IN (-1, 0) THEN --replaced I by line_no_tab(I) for bug#9214366
3106 IF inclu_tax_tab(P6(I)) = 'Y' OR tax_amt_rate_tax_tab(P6(I)) IS NOT NULL then
3107 vamt := vamt + NVL(tax_amt_tab(p6(I)), 0);
3108 ln_vamt_nr := ln_vamt_nr + NVL(tax_amt_non_rate_tab(P6(I)), 0); --added by walton for inclusive tax
3109 END IF;
3110
3111 ELSIF p6(I) = 0 THEN
3112 IF inclu_tax_tab(I) = 'Y' then
3113 vamt := vamt + bsln_amt;
3114 ln_vamt_nr := ln_vamt_nr + ln_bsln_amt_nr; --added by walton for inclusive tax
3115 ELSE
3116 ln_vamt_nr := ln_vamt_nr + ln_bsln_amt_nr + p_tax_amount;
3117 END IF;
3118 END IF;
3119
3120 IF p7(I) < I AND p7(I) NOT IN (-1, 0) THEN --replaced I by line_no_tab(I) for bug#9214366
3121 IF inclu_tax_tab(P7(I)) = 'Y' OR tax_amt_rate_tax_tab(P7(I)) IS NOT NULL then
3122 vamt := vamt + NVL(tax_amt_tab(p7(I)), 0);
3123 ln_vamt_nr := ln_vamt_nr + NVL(tax_amt_non_rate_tab(P7(I)), 0); --added by walton for inclusive tax
3124 END IF;
3125
3126 ELSIF p7(I) = 0 THEN
3127 IF inclu_tax_tab(I) = 'Y' then
3128 vamt := vamt + bsln_amt;
3129 ln_vamt_nr := ln_vamt_nr + ln_bsln_amt_nr; --added by walton for inclusive tax
3130 ELSE
3131 ln_vamt_nr := ln_vamt_nr + ln_bsln_amt_nr + p_tax_amount;
3132 END IF;
3133 END IF;
3134
3135 IF p8(I) < I AND p8(I) NOT IN (-1, 0) THEN --replaced I by line_no_tab(I) for bug#9214366
3136 IF inclu_tax_tab(P4(I)) = 'Y' OR tax_amt_rate_tax_tab(P8(I)) IS NOT NULL then
3137 vamt := vamt + NVL(tax_amt_tab(p8(I)), 0);
3138 ln_vamt_nr := ln_vamt_nr + NVL(tax_amt_non_rate_tab(P8(I)), 0); --added by walton for inclusive tax
3139 END IF;
3140
3141 ELSIF p8(I) = 0 THEN
3142 IF inclu_tax_tab(I) = 'Y' then
3143 vamt := vamt + bsln_amt;
3144 ln_vamt_nr := ln_vamt_nr + ln_bsln_amt_nr; --added by walton for inclusive tax
3145 ELSE
3146 ln_vamt_nr := ln_vamt_nr + ln_bsln_amt_nr + p_tax_amount;
3147 END IF;
3148 END IF;
3149
3150 IF p9(I) <I AND p9(I) NOT IN (-1, 0) THEN --replaced I by line_no_tab(I) for bug#9214366
3151 IF inclu_tax_tab(P9(I)) = 'Y' OR tax_amt_rate_tax_tab(P9(I)) IS NOT NULL then
3152 vamt := vamt + NVL(tax_amt_tab(p4(I)), 0);
3153 ln_vamt_nr := ln_vamt_nr + NVL(tax_amt_non_rate_tab(P9(I)), 0); --added by walton for inclusive tax
3154 END IF;
3155
3156 ELSIF p9(I) = 0 THEN
3157 IF inclu_tax_tab(I) = 'Y' then
3158 vamt := vamt + bsln_amt;
3159 ln_vamt_nr := ln_vamt_nr + ln_bsln_amt_nr; --added by walton for inclusive tax
3160 ELSE
3161 ln_vamt_nr := ln_vamt_nr + ln_bsln_amt_nr + p_tax_amount;
3162 END IF;
3163 END IF;
3164
3165 IF p10(I) < I AND p10(I) NOT IN (-1, 0) THEN --replaced I by line_no_tab(I) for bug#9214366
3166 IF inclu_tax_tab(P10(I)) = 'Y' OR tax_amt_rate_tax_tab(P10(I)) IS NOT NULL then
3167 vamt := vamt + NVL(tax_amt_tab(p10(I)), 0);
3168 ln_vamt_nr := ln_vamt_nr + NVL(tax_amt_non_rate_tab(P10(I)), 0); --added by walton for inclusive tax
3169 END IF;
3170
3171 ELSIF p10(I) = 0 THEN
3172 IF inclu_tax_tab(I) = 'Y' then
3173 vamt := vamt + bsln_amt;
3174 ln_vamt_nr := ln_vamt_nr + ln_bsln_amt_nr; --added by walton for inclusive tax
3175 ELSE
3176 ln_vamt_nr := ln_vamt_nr + ln_bsln_amt_nr + p_tax_amount;
3177 END IF;
3178 END IF;
3179 -- end bug 5228046
3180 -------------------------------------------------------
3181 --Added by Shujuan for 10358786 on 28-Jan-2011, End
3182
3183 v_tax_amt := v_tax_amt + (vamt * (tax_rate_tab(I)/100));
3184 base_tax_amt_tab(I) := vamt;
3185 tax_amt_tab(I) := nvl(tax_amt_tab(I),0) + v_tax_amt;
3186
3187 -- Added by Jia Li for Tax Inclusive Computations on 2007/12/07, Begin
3188 -----------------------------------------------------------------------
3189 ln_v_tax_amt_nr := ln_v_tax_amt_nr + (ln_vamt_nr * (tax_rate_tab(I)/100));
3190 tax_amt_non_rate_tab(I) := NVL(tax_amt_non_rate_tab(I), 0) + ln_v_tax_amt_nr;
3191 --Commented out by Shyan for bug 10358786 on 28-Jan-2011 , Begin
3192 -------------------------------------------------------------------
3193 --tax_amt_rate_tax_tab(I):= tax_amt_tab(I);
3194 -------------------------------------------------------------------
3195 -- Commented out by Shyan for bug 10358786 on 28-Jan-2011 , End
3196
3197 -- Added by Shyan for bug 10358786 on 28-Jan-2011 , Begin
3198 -------------------------------------------------------------------
3199 IF inclu_tax_tab(I) = 'Y' then
3200 tax_amt_rate_tax_tab(I) := tax_amt_tab(I);
3201 END IF;
3202 -------------------------------------------------------------------
3203 -- Added by Shyan for bug 10358786 on 28-Jan-2011, End
3204 -----------------------------------------------------------------------
3205 -- Added by Jia Li for Tax Inclusive Computations on 2007/12/07, End
3206
3207 vamt := 0;
3208 v_tax_amt := 0;
3209 ln_vamt_nr := 0; -- Added by Jia Li for Tax Inclusive Computations on 2007/12/07
3210 ln_v_tax_amt_nr := 0; -- Added by Jia Li for Tax Inclusive Computations on 2007/12/07
3211
3212 END IF;
3213
3214 -- Added by Jia Li for Tax Inclusive Computations on 2007/12/07, Begin
3215 -----------------------------------------------------------------------
3216 ELSE
3217 tax_amt_tab(I) := 0;
3218 base_tax_amt_tab(I) := 0;
3219 END IF; -- end_date_tab(I) <> 0
3220 -----------------------------------------------------------------------
3221 -- Added by Jia Li for Tax Inclusive Computations on 2007/12/07, End
3222
3223 END LOOP;
3224
3225 /************** Part - 3 ******************************/
3226 FOR I in 1..row_count LOOP
3227 -- Added by Jia Li for Tax Inclusive Computations on 2007/12/07, Begin
3228 -----------------------------------------------------------------------
3229 IF end_date_tab(I) <> 0
3230 THEN
3231 -----------------------------------------------------------------------
3232 -- Added by Jia Li for Tax Inclusive Computations on 2007/12/07, End
3233
3234 IF tax_rate_tab(I) <> 0 THEN
3235 IF p1(I) > I THEN
3236 vamt := vamt + nvl(tax_amt_tab(p1(I)),0);
3237 ln_vamt_nr := ln_vamt_nr + NVL(tax_amt_non_rate_tab(p1(I)), 0); -- Added by Jia Li for Tax Inclusive Computations on 2007/12/07
3238 END IF;
3239
3240 IF p2(I) > I THEN
3241 vamt := vamt + nvl(tax_amt_tab(p2(I)),0);
3242 ln_vamt_nr := ln_vamt_nr + NVL(tax_amt_non_rate_tab(p2(I)), 0); -- Added by Jia Li for Tax Inclusive Computations on 2007/12/07
3243 END IF;
3244
3245 IF p3(I) > I THEN
3246 vamt := vamt + nvl(tax_amt_tab(p3(I)),0);
3247 ln_vamt_nr := ln_vamt_nr + NVL(tax_amt_non_rate_tab(p3(I)), 0); -- Added by Jia Li for Tax Inclusive Computations on 2007/12/07
3248 END IF;
3249
3250 IF p4(I) > I THEN
3251 vamt := vamt + nvl(tax_amt_tab(p4(I)),0);
3252 ln_vamt_nr := ln_vamt_nr + NVL(tax_amt_non_rate_tab(p4(I)), 0); -- Added by Jia Li for Tax Inclusive Computations on 2007/12/07
3253 END IF;
3254
3255 IF p5(I) > I THEN
3256 vamt := vamt + nvl(tax_amt_tab(p5(I)),0);
3257 ln_vamt_nr := ln_vamt_nr + NVL(tax_amt_non_rate_tab(p5(I)), 0); -- Added by Jia Li for Tax Inclusive Computations on 2007/12/07
3258 END IF;
3259
3260 -- Date 01-NOV-2006 Bug 5228046 added by SACSETHI ( added column from Precedence 6 to 10 )
3261 -- START BUG 5228046
3262
3263 IF p6(I) > I THEN
3264 vamt := vamt + nvl(tax_amt_tab(p6(I)),0);
3265 ln_vamt_nr := ln_vamt_nr + NVL(tax_amt_non_rate_tab(p6(I)), 0); -- Added by Jia Li for Tax Inclusive Computations on 2007/12/07
3266 END IF;
3267
3268 IF p7(I) > I THEN
3269 vamt := vamt + nvl(tax_amt_tab(p7(I)),0);
3270 ln_vamt_nr := ln_vamt_nr + NVL(tax_amt_non_rate_tab(p7(I)), 0); -- Added by Jia Li for Tax Inclusive Computations on 2007/12/07
3271 END IF;
3272
3273 IF p8(I) > I THEN
3274 vamt := vamt + nvl(tax_amt_tab(p8(I)),0);
3275 ln_vamt_nr := ln_vamt_nr + NVL(tax_amt_non_rate_tab(p8(I)), 0); -- Added by Jia Li for Tax Inclusive Computations on 2007/12/07
3276 END IF;
3277
3278 IF p9(I) > I THEN
3279 vamt := vamt + nvl(tax_amt_tab(p9(I)),0);
3280 ln_vamt_nr := ln_vamt_nr + NVL(tax_amt_non_rate_tab(p9(I)), 0); -- Added by Jia Li for Tax Inclusive Computations on 2007/12/07
3281 END IF;
3282
3283 IF p10(I) > I THEN
3284 vamt := vamt + nvl(tax_amt_tab(p10(I)),0);
3285 ln_vamt_nr := ln_vamt_nr + NVL(tax_amt_non_rate_tab(p10(I)), 0); -- Added by Jia Li for Tax Inclusive Computations on 2007/12/07
3286 END IF;
3287
3288 -- END BUG 5228046
3289
3290 v_tax_amt := v_tax_amt + (vamt * (tax_rate_tab(I)/100));
3291
3292 base_tax_amt_tab(I) := vamt; -- Added by Jia Li for Tax Inclusive Computations on 2007/12/07
3293 ln_v_tax_amt_nr := ln_v_tax_amt_nr + (ln_vamt_nr * (tax_rate_tab(I)/100)); -- Added by Jia Li for Tax Inclusive Computations on 2007/12/07
3294
3295 IF vamt <> 0 THEN
3296 base_tax_amt_tab(I) := base_tax_amt_tab(I) + vamt;
3297 END IF;
3298
3299 tax_amt_tab(I) := nvl(tax_amt_tab(I),0) + v_tax_amt;
3300
3301 -- Added by Jia Li for Tax Inclusive Computations on 2007/12/07, Begin
3302 -----------------------------------------------------------------------
3303 tax_amt_non_rate_tab(I) := NVL(tax_amt_non_rate_tab(I), 0) * ln_v_tax_amt_nr;
3304 tax_amt_rate_tax_tab(I) := tax_amt_tab(I);
3305 ln_vamt_nr := 0;
3306 ln_v_tax_amt_nr := 0;
3307 -----------------------------------------------------------------------
3308 -- Added by Jia Li for Tax Inclusive Computations on 2007/12/07, End
3309
3310 vamt := 0;
3311 v_tax_amt := 0;
3312 END IF;
3313
3314 -- Added by Jia Li for Tax Inclusive Computations on 2007/12/07, Begin
3315 -----------------------------------------------------------------------
3316 ELSE
3317 base_tax_amt_tab(I) := vamt;
3318 tax_amt_tab(I) := 0;
3319 END IF; -- end_date_tab(I) <> 0
3320 -----------------------------------------------------------------------
3321 -- Added by Jia Li for Tax Inclusive Computations on 2007/12/07, End
3322
3323 END LOOP;
3324
3325
3326 /************** Part - 4 ******************************/
3327 FOR counter IN 1 .. max_iter LOOP
3328 vamt := 0;
3329 ln_vamt_nr := 0; -- Added by Jia Li for Tax Inclusive Computations on 2007/12/07
3330 v_tax_amt := 0;
3331 FOR i IN 1 .. row_count LOOP
3332 -- Deleted by Jia Li for Tax Inclusive Computations on 2007/12/07, Begin
3333 ------------------------------------------------------------------------
3334 /*
3335 IF tax_rate_tab( i ) > 0 THEN
3336 IF tax_type_tab( I ) = 1 THEN
3337 v_amt := p_assessable_value;
3338 ELSE
3339 IF p_assessable_value IN ( 0, -1 ) OR tax_type_tab( I ) <> 1 THEN
3340 v_amt := p_tax_amount;
3341 END IF;
3342 END IF;
3343 */
3344 ------------------------------------------------------------------------
3345 -- Deleted by Jia Li for Tax Inclusive Computations on 2007/12/07, End
3346
3347 -- Added by Jia Li for Tax Inclusive Computations on 2007/12/07, Begin
3348 -----------------------------------------------------------------------
3349 IF ( tax_rate_tab(I) <> 0 OR tax_rate_zero_tab(I) = -9999 )
3350 AND
3351 ( end_date_tab(I) <> 0 )
3352 THEN
3353 IF tax_type_tab(I) = 1
3354 THEN
3355 IF ln_assessable_value = 1
3356 THEN
3357 v_amt := 1;
3358 ln_bsln_amt_nr := 0;
3359 ELSE
3360 v_amt := 0;
3361 ln_bsln_amt_nr := ln_assessable_value;
3362 END IF;
3363 ELSIF tax_type_tab(I) = 4
3364 THEN
3365 IF ln_assessable_value = 1
3366 THEN
3367 v_amt := 1;
3368 ln_bsln_amt_nr := 0;
3369 ELSE
3370 v_amt := 0;
3371 ln_bsln_amt_nr := ln_assessable_value;
3372 END IF;
3373 ELSIF tax_type_tab(I) = 6
3374 THEN
3375 v_amt := 0;
3376 ln_bsln_amt_nr := 0;
3377 ELSE
3378 v_amt := 1;
3379 ln_bsln_amt_nr := 0;
3380 END IF; -- tax_type_tab(I) = 1
3381 /* Commented out by Shyan for bug 10358786 on 28-Jan-2011 , Begin
3382 --------------------------------------------------------------------
3383 -----------------------------------------------------------------------
3384 -- Added by Jia Li for Tax Inclusive Computations on 2007/12/07, End
3385
3386 IF p1( i ) <> -1 THEN
3387 IF p1( i ) <> 0 THEN
3388 vamt := vamt + tax_amt_tab( p1( I ) );
3389 ln_vamt_nr := ln_vamt_nr + tax_amt_non_rate_tab(p1(I)); -- Added by Jia Li for Tax Inclusive Computations on 2007/12/07
3390 ELSIF p1(i) = 0 THEN
3391 vamt := vamt + v_amt;
3392 ln_vamt_nr := ln_vamt_nr + ln_bsln_amt_nr; -- Added by Jia Li for Tax Inclusive Computations on 2007/12/07
3393 END IF;
3394 END IF;
3395
3396 IF p2( i ) <> -1 THEN
3397 IF p2( i ) <> 0 THEN
3398 vamt := vamt + tax_amt_tab( p2( I ) );
3399 ln_vamt_nr := ln_vamt_nr + tax_amt_non_rate_tab(p2(I)); -- Added by Jia Li for Tax Inclusive Computations on 2007/12/07
3400 ELSIF p2(i) = 0 THEN
3401 vamt := vamt + v_amt;
3402 ln_vamt_nr := ln_vamt_nr + ln_bsln_amt_nr; -- Added by Jia Li for Tax Inclusive Computations on 2007/12/07
3403 END IF;
3404 END IF;
3405
3406 IF p3( i ) <> -1 THEN
3407 IF p3( i ) <> 0 THEN
3408 vamt := vamt + tax_amt_tab( p3( I ) );
3409 ln_vamt_nr := ln_vamt_nr + tax_amt_non_rate_tab(p3(I)); -- Added by Jia Li for Tax Inclusive Computations on 2007/12/07
3410 ELSIF p3(i) = 0 THEN
3411 vamt := vamt + v_amt;
3412 ln_vamt_nr := ln_vamt_nr + ln_bsln_amt_nr; -- Added by Jia Li for Tax Inclusive Computations on 2007/12/07
3413 END IF;
3414 END IF;
3415
3416 IF p4( i ) <> -1 THEN
3417 IF p4( i ) <> 0 THEN
3418 vamt := vamt + tax_amt_tab( p4( i ) );
3419 ln_vamt_nr := ln_vamt_nr + tax_amt_non_rate_tab(p4(I)); -- Added by Jia Li for Tax Inclusive Computations on 2007/12/07
3420 ELSIF p4(i) = 0 THEN
3421 vamt := vamt + v_amt;
3422 ln_vamt_nr := ln_vamt_nr + ln_bsln_amt_nr; -- Added by Jia Li for Tax Inclusive Computations on 2007/12/07
3423 END IF;
3424 END IF;
3425
3426 IF p5( i ) <> -1 THEN
3427 IF p5( i ) <> 0 THEN
3428 vamt := vamt + tax_amt_tab( p5( i ) );
3429 ln_vamt_nr := ln_vamt_nr + tax_amt_non_rate_tab(p5(I)); -- Added by Jia Li for Tax Inclusive Computations on 2007/12/07
3430 ELSIF p5(i) = 0 THEN
3431 vamt := vamt + v_amt;
3432 ln_vamt_nr := ln_vamt_nr + ln_bsln_amt_nr; -- Added by Jia Li for Tax Inclusive Computations on 2007/12/07
3433 END IF;
3434 END IF;
3435
3436 -- Date 01-NOV-2006 Bug 5228046 added by SACSETHI ( added column from Precedence 6 to 10 )
3437 -- START BUG 5228046
3438
3439 IF p6( i ) <> -1 THEN
3440 IF p6( i ) <> 0 THEN
3441 vamt := vamt + tax_amt_tab( p6( I ) );
3442 ln_vamt_nr := ln_vamt_nr + tax_amt_non_rate_tab(p6(I)); -- Added by Jia Li for Tax Inclusive Computations on 2007/12/07
3443 ELSIF p6(i) = 0 THEN
3444 vamt := vamt + v_amt;
3445 ln_vamt_nr := ln_vamt_nr + ln_bsln_amt_nr; -- Added by Jia Li for Tax Inclusive Computations on 2007/12/07
3446 END IF;
3447 END IF;
3448
3449 IF p7( i ) <> -1 THEN
3450 IF p7( i ) <> 0 THEN
3451 vamt := vamt + tax_amt_tab( p7( I ) );
3452 ln_vamt_nr := ln_vamt_nr + tax_amt_non_rate_tab(p7(I)); -- Added by Jia Li for Tax Inclusive Computations on 2007/12/07
3453 ELSIF p7(i) = 0 THEN
3454 vamt := vamt + v_amt;
3455 ln_vamt_nr := ln_vamt_nr + ln_bsln_amt_nr; -- Added by Jia Li for Tax Inclusive Computations on 2007/12/07
3456 END IF;
3457 END IF;
3458
3459 IF p8( i ) <> -1 THEN
3460 IF p8( i ) <> 0 THEN
3461 vamt := vamt + tax_amt_tab( p8( I ) );
3462 ln_vamt_nr := ln_vamt_nr + tax_amt_non_rate_tab(p8(I)); -- Added by Jia Li for Tax Inclusive Computations on 2007/12/07
3463 ELSIF p8(i) = 0 THEN
3464 vamt := vamt + v_amt;
3465 ln_vamt_nr := ln_vamt_nr + ln_bsln_amt_nr; -- Added by Jia Li for Tax Inclusive Computations on 2007/12/07
3466 END IF;
3467 END IF;
3468
3469 IF p9( i ) <> -1 THEN
3470 IF p9( i ) <> 0 THEN
3471 vamt := vamt + tax_amt_tab( p9( i ) );
3472 ln_vamt_nr := ln_vamt_nr + tax_amt_non_rate_tab(p9(I)); -- Added by Jia Li for Tax Inclusive Computations on 2007/12/07
3473 ELSIF p9(i) = 0 THEN
3474 vamt := vamt + v_amt;
3475 ln_vamt_nr := ln_vamt_nr + ln_bsln_amt_nr; -- Added by Jia Li for Tax Inclusive Computations on 2007/12/07
3476 END IF;
3477 END IF;
3478
3479 IF p10( i ) <> -1 THEN
3480 IF p10( i ) <> 0 THEN
3481 vamt := vamt + tax_amt_tab( p10( i ) );
3482 ln_vamt_nr := ln_vamt_nr + tax_amt_non_rate_tab(p10(I)); -- Added by Jia Li for Tax Inclusive Computations on 2007/12/07
3483 ELSIF p10(i) = 0 THEN
3484 vamt := vamt + v_amt;
3485 ln_vamt_nr := ln_vamt_nr + ln_bsln_amt_nr; -- Added by Jia Li for Tax Inclusive Computations on 2007/12/07
3486 END IF;
3487 END IF;
3488 -- END BUG 5228046
3489 ----------------------------------------------------------------
3490 Commented out by Shyan for bug 10358786 on 28-Jan-2011, End */
3491
3492 -- Added by shujuan for bug 10358786 on 28-Jan-2011, Begin
3493 -----------------------------------------------------------
3494 ln_vamt_nr :=0;
3495 tax_amt_non_rate_tab(I):=0;
3496 IF P1(i) <> -1 THEN
3497 IF P1(i) <> 0 THEN
3498 IF inclu_tax_tab(P1(I)) = 'Y' OR tax_amt_rate_tax_tab(P1(I)) IS NOT NULL then
3499 vamt:= vamt + tax_amt_tab(P1(I));
3500 ln_vamt_nr := ln_vamt_nr + NVL(tax_amt_non_rate_tab(P1(I)), 0); --added by walton for inclusive tax
3501 END IF;
3502 ELSIF P1(i) = 0 THEN
3503 IF inclu_tax_tab(I) = 'Y' then
3504 vamt := vamt + v_amt;
3505 ln_vamt_nr := ln_vamt_nr + ln_bsln_amt_nr;
3506 ELSE
3507 ln_vamt_nr := ln_vamt_nr + ln_bsln_amt_nr + p_tax_amount;
3508 END IF;
3509 END IF;
3510 END IF;
3511
3512 IF p2(i) <> -1 THEN
3513 IF p2(i) <> 0 THEN
3514 IF inclu_tax_tab(P2(I)) = 'Y' OR tax_amt_rate_tax_tab(P2(I)) IS NOT NULL then
3515 vamt := vamt + tax_amt_tab(p2(I));
3516 ln_vamt_nr := ln_vamt_nr + NVL(tax_amt_non_rate_tab(P2(I)), 0); --added by walton for inclusive tax
3517 END IF;
3518 ELSIF p2(i) = 0 THEN
3519 IF inclu_tax_tab(I) = 'Y' then
3520 vamt := vamt + v_amt;
3521 ln_vamt_nr := ln_vamt_nr + ln_bsln_amt_nr; --added by walton for inclusive tax
3522 ELSE
3523 ln_vamt_nr := ln_vamt_nr + ln_bsln_amt_nr + p_tax_amount;
3524 END IF;
3525 END IF;
3526 END IF;
3527
3528 IF p3(i) <> -1 THEN
3529 IF p3(i) <> 0 THEN
3530 IF inclu_tax_tab(P3(I)) = 'Y' OR tax_amt_rate_tax_tab(P3(I)) IS NOT NULL then
3531 vamt := vamt + tax_amt_tab(p3(I));
3532 ln_vamt_nr := ln_vamt_nr + NVL(tax_amt_non_rate_tab(P3(I)), 0); --added by walton for inclusive tax
3533 END IF;
3534 ELSIF p3(i) = 0 THEN
3535 IF inclu_tax_tab(I) = 'Y' then
3536 vamt := vamt + v_amt;
3537 ln_vamt_nr := ln_vamt_nr + ln_bsln_amt_nr; --added by walton for inclusive tax
3538 ELSE
3539 ln_vamt_nr := ln_vamt_nr + ln_bsln_amt_nr + p_tax_amount;
3540 END IF;
3541 END IF;
3542 END IF;
3543
3544 IF p4(i) <> -1 THEN
3545 IF p4(i) <> 0 THEN
3546 IF inclu_tax_tab(P4(I)) = 'Y' OR tax_amt_rate_tax_tab(P4(I)) IS NOT NULL then
3547 vamt := vamt + tax_amt_tab(p4(I));
3548 ln_vamt_nr := ln_vamt_nr + NVL(tax_amt_non_rate_tab(P4(I)), 0); --added by walton for inclusive tax
3549 END IF;
3550 ELSIF p4(i) = 0 THEN
3551 IF inclu_tax_tab(I) = 'Y' then
3552 vamt := vamt + v_amt;
3553 ln_vamt_nr := ln_vamt_nr + ln_bsln_amt_nr; --added by walton for inclusive tax
3554 ELSE
3555 ln_vamt_nr := ln_vamt_nr + ln_bsln_amt_nr + p_tax_amount;
3556 END IF;
3557 END IF;
3558 END IF;
3559
3560 IF p5(i) <> -1 THEN
3561 IF p5(i) <> 0 THEN
3562 IF inclu_tax_tab(P5(I)) = 'Y' OR tax_amt_rate_tax_tab(P5(I)) IS NOT NULL then
3563 vamt := vamt + tax_amt_tab(p5(I));
3564 ln_vamt_nr := ln_vamt_nr + NVL(tax_amt_non_rate_tab(P5(I)), 0); --added by walton for inclusive tax
3565 END IF;
3566 ELSIF p5(i) = 0 THEN
3567 IF inclu_tax_tab(I) = 'Y' then
3568 vamt := vamt + v_amt;
3569 ln_vamt_nr := ln_vamt_nr + ln_bsln_amt_nr; --added by walton for inclusive tax
3570 ELSE
3571 ln_vamt_nr := ln_vamt_nr + ln_bsln_amt_nr + p_tax_amount;
3572 END IF;
3573 END IF;
3574 END IF;
3575
3576 -- Date 31/10/2006 Bug 5228046 added by SACSETHI ( added column from Precedence 6 to 10 )
3577 -- start bug 5228046
3578 IF p6(i) <> -1 THEN
3579 IF p6(i) <> 0 THEN
3580 IF inclu_tax_tab(P6(I)) = 'Y' OR tax_amt_rate_tax_tab(P6(I)) IS NOT NULL then
3581 vamt := vamt + tax_amt_tab(p6(I));
3582 ln_vamt_nr := ln_vamt_nr + NVL(tax_amt_non_rate_tab(P6(I)), 0); --added by walton for inclusive tax
3583 END IF;
3584 ELSIF p6(i) = 0 THEN
3585 IF inclu_tax_tab(I) = 'Y' then
3586 vamt := vamt + v_amt;
3587 ln_vamt_nr := ln_vamt_nr + ln_bsln_amt_nr; --added by walton for inclusive tax
3588 ELSE
3589 ln_vamt_nr := ln_vamt_nr + ln_bsln_amt_nr + p_tax_amount;
3590 END IF;
3591 END IF;
3592 END IF;
3593 IF p7(i) <> -1 THEN
3594 IF p7(i) <> 0 THEN
3595 IF inclu_tax_tab(P7(I)) = 'Y' OR tax_amt_rate_tax_tab(P7(I)) IS NOT NULL then
3596 vamt := vamt + tax_amt_tab(p7(I));
3597 ln_vamt_nr := ln_vamt_nr + NVL(tax_amt_non_rate_tab(P7(I)), 0); --added by walton for inclusive tax
3598 END IF;
3599 ELSIF p7(i) = 0 THEN
3600 IF inclu_tax_tab(I) = 'Y' then
3601 vamt := vamt + v_amt;
3602 ln_vamt_nr := ln_vamt_nr + ln_bsln_amt_nr; --added by walton for inclusive tax
3603 ELSE
3604 ln_vamt_nr := ln_vamt_nr + ln_bsln_amt_nr + p_tax_amount;
3605 END IF;
3606 END IF;
3607 END IF;
3608
3609 IF p8(i) <> -1 THEN
3610 IF p8(i) <> 0 THEN
3611 IF inclu_tax_tab(P8(I)) = 'Y' OR tax_amt_rate_tax_tab(P8(I)) IS NOT NULL then
3612 vamt := vamt + tax_amt_tab(p8(I));
3613 ln_vamt_nr := ln_vamt_nr + NVL(tax_amt_non_rate_tab(P8(I)), 0); --added by walton for inclusive tax
3614 END IF;
3615 ELSIF p8(i) = 0 THEN
3616 IF inclu_tax_tab(I) = 'Y' then
3617 vamt := vamt + v_amt;
3618 ln_vamt_nr := ln_vamt_nr + ln_bsln_amt_nr; --added by walton for inclusive tax
3619 ELSE
3620 ln_vamt_nr := ln_vamt_nr + ln_bsln_amt_nr + p_tax_amount;
3621 END IF;
3622 END IF;
3623 END IF;
3624
3625 IF p9(i) <> -1 THEN
3626 IF p9(i) <> 0 THEN
3627 IF inclu_tax_tab(P9(I)) = 'Y' OR tax_amt_rate_tax_tab(P9(I)) IS NOT NULL then
3628 vamt := vamt + tax_amt_tab(p9(I));
3629 ln_vamt_nr := ln_vamt_nr + NVL(tax_amt_non_rate_tab(P9(I)), 0); --added by walton for inclusive tax
3630 END IF;
3631 ELSIF p9(i) = 0 THEN
3632 IF inclu_tax_tab(I) = 'Y' then
3633 vamt := vamt + v_amt;
3634 ln_vamt_nr := ln_vamt_nr + ln_bsln_amt_nr; --added by walton for inclusive tax
3635 ELSE
3636 ln_vamt_nr := ln_vamt_nr + ln_bsln_amt_nr + p_tax_amount;
3637 END IF;
3638 END IF;
3639 END IF;
3640
3641 IF p10(i) <> -1 THEN
3642 IF p10(i) <> 0 THEN
3643 IF inclu_tax_tab(P10(I)) = 'Y' OR tax_amt_rate_tax_tab(P10(I)) IS NOT NULL then
3644 vamt := vamt + tax_amt_tab(p10(I));
3645 ln_vamt_nr := ln_vamt_nr + NVL(tax_amt_non_rate_tab(P10(I)), 0); --added by walton for inclusive tax
3646 END IF;
3647 ELSIF p10(i) = 0 THEN
3648 IF inclu_tax_tab(I) = 'Y' then
3649 vamt := vamt + v_amt;
3650 ln_vamt_nr := ln_vamt_nr + ln_bsln_amt_nr; --added by walton for inclusive tax
3651 ELSE
3652 ln_vamt_nr := ln_vamt_nr + ln_bsln_amt_nr + p_tax_amount;
3653 END IF;
3654 END IF;
3655 END IF;
3656 -- end bug 5228046
3657 -----------------------------------------------------------
3658 -- Added by shujuan for bug 10358786 on 28-Jan-2011, End
3659
3660 v_tax_amt := v_tax_amt + ( vamt * ( tax_rate_tab( i )/100));
3661 -- Added by Jia Li for Tax Inclusive Computations on 2007/12/07, Begin
3662 -----------------------------------------------------------------------
3663 base_tax_amt_tab(I) := vamt;
3664 tax_target_tab(I) := vamt;
3665 ln_v_func_tax_amt := v_tax_amt + ( vamt * ( tax_rate_tab(I)/100 ));
3666 v_tax_amt := v_tax_amt + ( vamt * ( tax_rate_tab(I)/100 ));
3667 ELSIF tax_rate_tab(I) = 0
3668 THEN
3669 base_tax_amt_tab(I) := tax_amt_tab(I);
3670 v_tax_amt := tax_amt_tab(I);
3671 tax_target_tab(I) := v_tax_amt;
3672 ELSIF end_date_tab(I) = 0
3673 THEN
3674 tax_amt_tab(I) := 0;
3675 base_tax_amt_tab(I) := 0;
3676 tax_target_tab(I) := 0;
3677 END IF; -- (tax_rate_tab(I) <> 0 OR tax_rate_zero_tab(I) = -9999) AND (end_date_tab(I) <> 0)
3678 -----------------------------------------------------------------------
3679 -- Added by Jia Li for Tax Inclusive Computations on 2007/12/07, End
3680
3681 -- Deleted by Jia Li for Tax Inclusive Computations on 2007/12/07, Begin
3682 -----------------------------------------------------------------------
3683 /*
3684 ELSE
3685 v_tax_amt := tax_amt_tab( i );
3686 END IF;
3687 */
3688 -----------------------------------------------------------------------
3689 -- Deleted by Jia Li for Tax Inclusive Computations on 2007/12/07, End
3690
3691 tax_amt_tab( I ) := NVL( v_tax_amt, 0 );
3692
3693 -- Added by Jia Li for Tax Inclusive Computations on 2007/12/07, Begin
3694 -----------------------------------------------------------------------
3695 -- Commented out by Shyan for bug 10358786 on 28-Jan-2011 , Begin
3696 -------------------------------------------------------------------
3697 --tax_amt_rate_tax_tab(I) := tax_amt_tab(I);
3698 -------------------------------------------------------------------
3699 -- Commented out by Shyan for bug 10358786 on 28-Jan-2011 , End
3700
3701 -- Added by Shyan for bug 10358786 on 28-Jan-2011 , Begin
3702 -------------------------------------------------------------------
3703 IF inclu_tax_tab(I) = 'Y' THEN
3704 tax_amt_rate_tax_tab(I) := tax_amt_tab(I);
3705 END IF;
3706 -------------------------------------------------------------------
3707 -- Added by Shyan for bug 10358786 on 28-Jan-2011 , End
3708 func_tax_amt_tab(I) := NVL(ln_v_func_tax_amt, 0);
3709
3710 IF counter = max_iter
3711 THEN
3712 IF end_date_tab(I) = 0
3713 THEN
3714 tax_amt_tab(I) := 0;
3715 func_tax_amt_tab(I) := 0;
3716 END IF; -- end_date_tab(I) = 0
3717 END IF; -- counter = max_iter
3718 -----------------------------------------------------------------------
3719 -- Added by Jia Li for Tax Inclusive Computations on 2007/12/07, End
3720
3721 vamt := 0;
3722 v_amt := 0;
3723 v_tax_amt := 0;
3724 ln_v_func_tax_amt := 0; -- Added by Jia Li for Tax Inclusive Computations on 2007/12/07
3725 END LOOP;
3726 END LOOP;
3727
3728
3729 -- Added by Jia Li for Tax Inclusive Computations on 2007/12/07, Begin
3730 -- the following loop calculates the total tax per rupee and total tax thats not dependent on selling price.
3731 -- and calculation final tax amount
3732 -----------------------------------------------------------------------
3733 FOR I IN 1 .. row_count
3734 LOOP
3735 IF inclu_tax_tab(I) = 'Y'
3736 THEN
3737 ln_total_tax_per_rupee := ln_total_tax_per_rupee + NVL(tax_amt_rate_tax_tab(I),0) ;
3738 ln_total_non_rate_tax := ln_total_non_rate_tax + NVL(tax_amt_non_rate_tab(I),0);
3739 END IF;
3740 END LOOP;
3741
3742 ln_total_tax_per_rupee := ln_total_tax_per_rupee + 1;
3743
3744 IF ln_total_tax_per_rupee <> 0
3745 THEN
3746 ln_exclusive_price := (NVL(p_tax_amount,0) - ln_total_non_rate_tax ) / ln_total_tax_per_rupee;
3747 END If;
3748
3749 FOR I IN 1 .. row_count
3750 LOOP
3751 -- Commented out by Shyan for bug 10358786 on 28-Jan-2011 , Begin
3752 ------------------------------------------------------------------
3753 --tax_amt_tab (I) := (tax_amt_rate_tax_tab(I) * ln_exclusive_price ) + tax_amt_non_rate_tab(I);
3754 ------------------------------------------------------------------
3755 -- Commented out by Shyan for bug 10358786 on 28-Jan-2011 , End
3756
3757 -- Added by Shyan for bug 10358786 on 28-Jan-2011 , Begin
3758 ------------------------------------------------------------------
3759 tax_amt_tab (i):=0;
3760 IF inclu_tax_tab(I) = 'Y' THEN
3761 IF (tax_type_tab(I) = 1 and ln_assessable_value>1) OR
3762 (tax_type_tab(I) = 4 and ln_assessable_value> 1) THEN
3763
3764 IF tax_type_tab(I) = 1 and ln_assessable_value >1 Then
3765 ln_amount:= ln_assessable_value;
3766 ELSIF tax_type_tab(I) = 4 and ln_assessable_value >1 Then
3767 ln_amount:= ln_assessable_value;
3768 END IF;
3769
3770 IF P1(I) < I AND p1(I) NOT IN (-1, 0) then
3771 tax_amt_tab(i) := tax_amt_tab(i) + tax_amt_tab(P1(i))* tax_rate_tab(i)/100;
3772 ELSIF P1(I)=0 Then
3773 tax_amt_tab(i) := tax_amt_tab(i) + ln_amount * tax_rate_tab(i)/100;
3774 END IF;
3775
3776 IF P2(I) < I AND p2(I) NOT IN (-1, 0) then
3777 tax_amt_tab(i) := tax_amt_tab(i) + tax_amt_tab(P2(i))* tax_rate_tab(i)/100;
3778 ELSIF P2(I)=0 Then
3779 tax_amt_tab(i) := tax_amt_tab(i) + ln_amount * tax_rate_tab(i)/100;
3780 END IF;
3781
3782 IF P3(I) < I AND p3(I) NOT IN (-1, 0) then
3783 tax_amt_tab(i) := tax_amt_tab(i) + tax_amt_tab(P3(i))* tax_rate_tab(i)/100;
3784 ELSIF P3(I)=0 Then
3785 tax_amt_tab(i) := tax_amt_tab(i) + ln_amount * tax_rate_tab(i)/100;
3786 END IF;
3787
3788 IF P4(I) < I AND p4(I) NOT IN (-1, 0) then
3789 tax_amt_tab(i) := tax_amt_tab(i) + tax_amt_tab(P4(i))* tax_rate_tab(i)/100;
3790 ELSIF P4(I)=0 Then
3791 tax_amt_tab(i) := tax_amt_tab(i) + ln_amount * tax_rate_tab(i)/100;
3792 END IF;
3793
3794 IF P5(I) < I AND p5(I) NOT IN (-1, 0) then
3795 tax_amt_tab(i) := tax_amt_tab(i) + tax_amt_tab(P5(i))* tax_rate_tab(i)/100;
3796 ELSIF P5(I)=0 Then
3797 tax_amt_tab(i) := tax_amt_tab(i) + ln_amount * tax_rate_tab(i)/100;
3798 END IF;
3799
3800 IF P6(I) < I AND p6(I) NOT IN (-1, 0) then
3801 tax_amt_tab(i) := tax_amt_tab(i) + tax_amt_tab(P6(i))* tax_rate_tab(i)/100;
3802 ELSIF P6(I)=0 Then
3803 tax_amt_tab(i) := tax_amt_tab(i) + ln_amount * tax_rate_tab(i)/100;
3804 END IF;
3805
3806 IF P7(I) < I AND p7(I) NOT IN (-1, 0) then
3807 tax_amt_tab(i) := tax_amt_tab(i) + tax_amt_tab(P7(i))* tax_rate_tab(i)/100;
3808 ELSIF P7(I)=0 Then
3809 tax_amt_tab(i) := tax_amt_tab(i) + ln_amount * tax_rate_tab(i)/100;
3810 END IF;
3811
3812 IF P8(I) < I AND p8(I) NOT IN (-1, 0) then
3813 tax_amt_tab(i) := tax_amt_tab(i) + tax_amt_tab(P8(i))* tax_rate_tab(i)/100;
3814 ELSIF P8(I)=0 Then
3815 tax_amt_tab(i) := tax_amt_tab(i) + ln_amount * tax_rate_tab(i)/100;
3816 END IF;
3817
3818 IF P9(I) < I AND p9(I) NOT IN (-1, 0) then
3819 tax_amt_tab(i) := tax_amt_tab(i) + tax_amt_tab(P9(i))* tax_rate_tab(i)/100;
3820 ELSIF P9(I)=0 Then
3821 tax_amt_tab(i) := tax_amt_tab(i) + ln_amount * tax_rate_tab(i)/100;
3822 END IF;
3823
3824 IF P10(I) < I AND p10(I) NOT IN (-1, 0) then
3825 tax_amt_tab(i) := tax_amt_tab(i) + tax_amt_tab(P10(i))* tax_rate_tab(i)/100;
3826 ELSIF P10(I)=0 Then
3827 tax_amt_tab(i) := tax_amt_tab(i) + ln_amount * tax_rate_tab(i)/100;
3828 END IF;
3829 ELSE
3830 tax_amt_tab (i) := (tax_amt_rate_tax_tab(I) * ln_exclusive_price ) + tax_amt_non_rate_tab(I);
3831 END IF;
3832 ELSE
3833 IF (tax_type_tab(I) = 1 and ln_assessable_value >1) OR
3834 (tax_type_tab(I) = 4 and ln_assessable_value > 1) THEN
3835
3836 IF tax_type_tab(I) = 1 and ln_assessable_value >1 Then
3837 ln_amount:= ln_assessable_value;
3838
3839 END IF;
3840 ELSE ln_amount:= p_tax_amount;
3841 END IF;
3842 IF P1(I) <I AND p1(I) NOT IN (-1, 0) then
3843 tax_amt_tab(i) := tax_amt_tab(i) + tax_amt_tab(P1(i))* tax_rate_tab(i)/100;
3844 ELSIF P1(I)=0 Then
3845 tax_amt_tab(i) := tax_amt_tab(i) + ln_amount * tax_rate_tab(i)/100;
3846 END IF;
3847
3848 IF P2(I) < I AND p2(I) NOT IN (-1, 0) then
3849 tax_amt_tab(i) := tax_amt_tab(i) + tax_amt_tab(P2(i))* tax_rate_tab(i)/100;
3850 ELSIF P2(I)=0 Then
3851 tax_amt_tab(i) := tax_amt_tab(i) + ln_amount * tax_rate_tab(i)/100;
3852 END IF;
3853
3854 IF P3(I) < I AND p3(I) NOT IN (-1, 0) then
3855 tax_amt_tab(i) := tax_amt_tab(i) + tax_amt_tab(P3(i))* tax_rate_tab(i)/100;
3856 ELSIF P3(I)=0 Then
3857 tax_amt_tab(i) := tax_amt_tab(i) + ln_amount * tax_rate_tab(i)/100;
3858 END IF;
3859
3860 IF P4(I) < I AND p4(I) NOT IN (-1, 0) then
3861 tax_amt_tab(i) := tax_amt_tab(i) + tax_amt_tab(P4(i))* tax_rate_tab(i)/100;
3862 ELSIF P4(I)=0 Then
3863 tax_amt_tab(i) := tax_amt_tab(i) + ln_amount * tax_rate_tab(i)/100;
3864 END IF;
3865
3866 IF P5(I) < I AND p5(I) NOT IN (-1, 0) then
3867 tax_amt_tab(i) := tax_amt_tab(i) + tax_amt_tab(P5(i))* tax_rate_tab(i)/100;
3868 ELSIF P5(I)=0 Then
3869 tax_amt_tab(i) := tax_amt_tab(i) + ln_amount * tax_rate_tab(i)/100;
3870 END IF;
3871
3872 IF P6(I) < I AND p6(I) NOT IN (-1, 0) then
3873 tax_amt_tab(i) := tax_amt_tab(i) + tax_amt_tab(P6(i))* tax_rate_tab(i)/100;
3874 ELSIF P6(I)=0 Then
3875 tax_amt_tab(i) := tax_amt_tab(i) + ln_amount * tax_rate_tab(i)/100;
3876 END IF;
3877
3878 IF P7(I) < I AND p7(I) NOT IN (-1, 0) then
3879 tax_amt_tab(i) := tax_amt_tab(i) + tax_amt_tab(P7(i))* tax_rate_tab(i)/100;
3880 ELSIF P7(I)=0 Then
3881 tax_amt_tab(i) := tax_amt_tab(i) + ln_amount * tax_rate_tab(i)/100;
3882 END IF;
3883
3884 IF P8(I) < I AND p8(I) NOT IN (-1, 0) then
3885 tax_amt_tab(i) := tax_amt_tab(i) + tax_amt_tab(P8(i))* tax_rate_tab(i)/100;
3886 ELSIF P8(I)=0 Then
3887 tax_amt_tab(i) := tax_amt_tab(i) + ln_amount * tax_rate_tab(i)/100;
3888 END IF;
3889
3890 IF P9(I) < I AND p9(I) NOT IN (-1, 0) then
3891 tax_amt_tab(i) := tax_amt_tab(i) + tax_amt_tab(P9(i))* tax_rate_tab(i)/100;
3892 ELSIF P9(I)=0 Then
3893 tax_amt_tab(i) := tax_amt_tab(i) + ln_amount * tax_rate_tab(i)/100;
3894 END IF;
3895
3896 IF P10(I) < I AND p10(I) NOT IN (-1, 0) then
3897 tax_amt_tab(i) := tax_amt_tab(i) + tax_amt_tab(P10(i))* tax_rate_tab(i)/100;
3898 ELSIF P10(I)=0 Then
3899 tax_amt_tab(i) := tax_amt_tab(i) + ln_amount * tax_rate_tab(i)/100;
3900 END IF;
3901 END IF;
3902 ------------------------------------------------------------------
3903 -- Added by Shyan for bug 10358786 on 28-Jan-2011 , End
3904 END LOOP;
3905 -----------------------------------------------------------------------
3906 -- Added by Jia Li for Tax Inclusive Computations on 2007/12/07, End
3907
3908 /************** Part - 5 ******************************/
3909 FOR rec in tax_cur( p_line_id) LOOP
3910 IF tax_type_tab(rec.lno) <> 2 THEN
3911 v_tax_amt := v_tax_amt + nvl(tax_amt_tab(rec.lno),0);
3912 END IF;
3913
3914 IF transaction_name = 'OE_LINES_UPDATE' THEN
3915
3916 UPDATE JAI_AR_TRX_TAX_CALC_T
3917 SET
3918 tax_amount = nvl(tax_amt_tab(rec.lno),0) ,
3919 base_tax_amount = decode(nvl(base_tax_amt_tab(rec.lno),0) ,
3920 0 ,
3921 nvl(tax_amt_tab(rec.lno),0) ,
3922 nvl(base_tax_amt_tab(rec.lno),0)
3923 ) ,
3924 func_tax_amount = nvl(tax_amt_tab(rec.lno),0) * v_currency_conv_factor ,
3925 last_update_date = p_last_update_date ,
3926 last_updated_by = p_last_updated_by ,
3927 last_update_login = p_last_update_login
3928 WHERE
3929 link_to_cust_trx_line_id = p_line_id AND
3930 tax_line_no = rec.lno;
3931 END IF;
3932 END LOOP;
3933
3934 p_tax_amount := nvl(v_tax_amt,0);
3935
3936
3937 /* Added by Ramananda for bug#4407165 */
3938 EXCEPTION
3939 WHEN OTHERS THEN
3940 FND_MESSAGE.SET_NAME('JA','JAI_EXCEPTION_OCCURED');
3941 FND_MESSAGE.SET_TOKEN('JAI_PROCESS_MSG', lv_object_name ||'. Err:'||sqlerrm );
3942 app_exception.raise_exception;
3943
3944 END calculate_tax;
3945
3946 END jai_ar_sup_trx_pkg;
3947