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