1 PACKAGE BODY jai_gst_tax_defaultation_pkg AS
2 /* $Header: jai_gst_tax_dflt.plb 120.4 2010/10/11 08:44:58 pezheng noship $ */
3
4
5
6 --==========================================================================
7 -- FUNCTION NAME:
8 -- get_organization_state Private
9 --
10 -- DESCRIPTION:
11 -- Get state code of Organization+Location setup
12 --
13 -- Return:
14 -- Varchar2
15 -- The state code
16 --
17 -- ER NAME/BUG#
18 -- GST Enhancement/ bug 10043656
19 --
20 -- PARAMETERS:
21 -- In: pn_organization_id Organization ID
22 -- pn_location_id Location ID
23 --
24 --
25 -- DESIGN REFERENCES:
26 -- <<TDD_12_2_FIN_JAI_Enhanced_GST.doc>>
27 --
28 -- CALL FROM
29 --
30 --
31 -- CHANGE HISTORY:
32 -- 20-Sep-2010 Created by peng.zheng
33 --==========================================================================
34 FUNCTION get_organization_state(pn_organization_id IN NUMBER,
35 pn_location_id IN NUMBER)
36 RETURN VARCHAR2 IS
37
38 CURSOR cur_get_master_org_id IS
39 SELECT master_organization_id FROM oe_system_parameters;
40
41 CURSOR cur_state(pn_organization_id IN NUMBER) IS
42 SELECT jrp.state_code state_code
43 FROM JAI_RGM_PARTIES jrp, JAI_RGM_DEFINITIONS jrd
44 WHERE jrp.ORGANIZATION_ID = pn_organization_id
45 AND jrp.ORGANIZATION_TYPE = 'IO' --inventory organization
46 AND jrd.regime_id = jrp.regime_id
47 AND jrd.regime_code = jai_constants.cgst_regime
48 AND jrp.location_id = pn_location_id;
49
50 CURSOR cur_sgst_state(pn_organization_id IN NUMBER) IS
51 SELECT jrp.state_code state_code
52 FROM JAI_RGM_PARTIES jrp, JAI_RGM_DEFINITIONS jrd
53 WHERE jrp.ORGANIZATION_ID = pn_organization_id
54 AND jrp.ORGANIZATION_TYPE = 'IO' --inventory organization
55 AND jrd.regime_id = jrp.regime_id
56 AND jrd.regime_code = jai_constants.sgst_regime
57 AND jrp.location_id = pn_location_id;
58
59 v_organization_id NUMBER;
60 v_state JAI_RGM_PARTIES.state_code%TYPE;
61 BEGIN
62 IF pn_organization_id IS NULL THEN
63 OPEN cur_get_master_org_id;
64 FETCH cur_get_master_org_id
65 INTO v_organization_id;
66 CLOSE cur_get_master_org_id;
67 ELSE
68 v_organization_id := pn_organization_id;
69 END IF;
70
71 OPEN cur_state(v_organization_id);
72 FETCH cur_state
73 INTO v_state;
74 CLOSE cur_state;
75
76 --If not found in CGST, get from SGST
77 IF v_state IS NULL THEN
78 OPEN cur_sgst_state(v_organization_id);
79 FETCH cur_sgst_state
80 INTO v_state;
81 CLOSE cur_sgst_state;
82 END IF;
83
84 RETURN v_state;
85
86 END get_organization_state;
87
88 --==========================================================================
89 -- FUNCTION NAME:
90 -- get_org_state Private
91 --
92 -- DESCRIPTION:
93 -- Get state code of org setup
94 --
95 -- Return:
96 -- Varchar2
97 -- The state code
98 --
99 -- ER NAME/BUG#
100 -- GST Enhancement/ bug 10043656
101 --
102 -- PARAMETERS:
103 -- In: pn_org_id Org ID
104 --
105 --
106 -- DESIGN REFERENCES:
107 -- <<TDD_12_2_FIN_JAI_Enhanced_GST.doc>>
108 --
109 -- CALL FROM
110 --
111 --
112 -- CHANGE HISTORY:
113 -- 20-Sep-2010 Created by peng.zheng
114 --==========================================================================
115 FUNCTION get_org_state(pn_org_id IN NUMBER) RETURN VARCHAR2 IS
116
117 CURSOR cur_state IS
118 SELECT jrp.state_code state_code
119 FROM JAI_RGM_PARTIES jrp, JAI_RGM_DEFINITIONS jrd
120 WHERE jrp.ORGANIZATION_ID = pn_org_id
121 AND jrp.ORGANIZATION_TYPE = 'OU' --operating unit
122 AND jrd.regime_id = jrp.regime_id
123 AND jrd.regime_code = jai_constants.cgst_regime
124 AND jrp.state_code IS NOT NULL;
125
126 CURSOR cur_sgst_state IS
127 SELECT jrp.state_code state_code
128 FROM JAI_RGM_PARTIES jrp, JAI_RGM_DEFINITIONS jrd
129 WHERE jrp.ORGANIZATION_ID = pn_org_id
130 AND jrp.ORGANIZATION_TYPE = 'OU' --operating unit
131 AND jrd.regime_id = jrp.regime_id
132 AND jrd.regime_code = jai_constants.sgst_regime
133 AND jrp.state_code IS NOT NULL;
134
135 v_state JAI_RGM_PARTIES.state_code%TYPE;
136 BEGIN
137
138 OPEN cur_state;
139 FETCH cur_state
140 INTO v_state;
141 CLOSE cur_state;
142
143 IF v_state IS NULL THEN
144 OPEN cur_sgst_state;
145 FETCH cur_sgst_state
146 INTO v_state;
147 CLOSE cur_sgst_state;
148
149 END IF;
150
151 RETURN v_state;
152
153 END get_org_state;
154
155 --==========================================================================
156 -- FUNCTION NAME:
157 -- get_cust_inter_flag Private
158 --
159 -- DESCRIPTION:
160 -- to check if transaction is inter-state or not
161 -- compare the state of customer/site and location setup
162 -- Return:
163 -- Varchar2
164 -- Y: inter-state
165 -- N: intra-state
166 --
167 -- ER NAME/BUG#
168 -- GST Enhancement/ bug 10043656
169 --
170 -- PARAMETERS:
171 -- In: pn_organization_id Organization ID
172 -- pn_location_id Location ID
173 -- pn_customer_id Customer ID
174 -- pn_ship_to_site_use_id Ship to site use id
175 --
176 --
177 -- DESIGN REFERENCES:
178 -- <<TDD_12_2_FIN_JAI_Enhanced_GST.doc>>
179 --
180 -- CALL FROM
181 --
182 --
183 -- CHANGE HISTORY:
184 -- 20-Sep-2010 Created by peng.zheng
185 --==========================================================================
186 FUNCTION get_cust_inter_flag(pn_organization_id IN NUMBER,
187 pn_location_id IN NUMBER,
188 pn_customer_id IN NUMBER,
189 pn_ship_to_site_use_id IN NUMBER)
190 RETURN VARCHAR2 IS
191
192 -- to get address_id
193 CURSOR cur_address IS
194 SELECT cust_acct_site_id address_id
195 FROM hz_cust_site_uses_all A
196 WHERE A.site_use_id = pn_ship_to_site_use_id;
197
198 -- to get cust state
199 CURSOR cur_state(p_address_id IN NUMBER DEFAULT 0) IS
200 SELECT STATE_CODE
201 FROM JAI_CMN_CUS_ADDRESSES a
202 WHERE A.customer_id = pn_customer_id
203 AND A.address_id = p_address_id;
204
205 v_address_id NUMBER;
206 v_cust_state VARCHAR2(120);
207 v_organization_state VARCHAR2(120);
208
209 BEGIN
210
211 OPEN cur_address;
212 FETCH cur_address
213 INTO v_address_id;
214 CLOSE cur_address;
215
216 IF v_address_id IS NOT NULL THEN
217 OPEN cur_state(v_address_id);
218 FETCH cur_state
219 INTO v_cust_state;
220 CLOSE cur_state;
221 END IF;
222
223 IF v_cust_state IS NULL THEN
224 OPEN cur_state(0);
225 FETCH cur_state
226 INTO v_cust_state;
227 CLOSE cur_state;
228 END IF;
229
230 v_organization_state := get_organization_state(pn_organization_id,
231 pn_location_id);
232
233 IF v_cust_state IS NOT NULL AND v_organization_state IS NOT NULL THEN
234 IF v_cust_state = v_organization_state THEN
235 RETURN 'N';
236 ELSE
237 RETURN 'Y';
238 END IF;
239 ELSE
240 RETURN NULL;
241 END IF;
242
243 END get_cust_inter_flag;
244
245 --==========================================================================
246 -- FUNCTION NAME:
247 -- get_vendor_inter_flag Private
248 --
249 -- DESCRIPTION:
250 -- to check if transaction is inter-state or not
251 -- compare the state of vendor/site and location setup
252 -- Return:
253 -- Varchar2
254 -- Y: inter-state
255 -- N: intra-state
256 --
257 -- ER NAME/BUG#
258 -- GST Enhancement/ bug 10043656
259 --
260 -- PARAMETERS:
261 -- In: pn_organization_id Organization ID
262 -- pn_location_id Location ID
263 -- pn_vendor_id Vendor ID
264 -- pn_vendor_site_id Vendor Site ID
265 --
266 --
267 -- DESIGN REFERENCES:
268 -- <<TDD_12_2_FIN_JAI_Enhanced_GST.doc>>
269 --
270 -- CALL FROM
271 --
272 --
273 -- CHANGE HISTORY:
274 -- 20-Sep-2010 Created by peng.zheng
275 --==========================================================================
276
277 FUNCTION get_vendor_inter_flag(pn_organization_id IN NUMBER,
278 pn_location_id IN NUMBER,
279 pn_vendor_id NUMBER,
280 pn_vendor_site_id NUMBER) RETURN VARCHAR2 IS
281
282 -- to get state
283 CURSOR cur_state(pn_vendor_site_id IN NUMBER DEFAULT 0) IS
284 SELECT STATE_CODE
285 FROM JAI_CMN_VENDOR_SITES A
286 WHERE a.vendor_id = pn_vendor_id
287 AND a.vendor_site_id = pn_vendor_site_id;
288
289 v_vendor_state VARCHAR2(120);
290 v_organization_state VARCHAR2(120);
291
292 BEGIN
293
294 IF pn_vendor_site_id IS NOT NULL THEN
295 OPEN cur_state(pn_vendor_site_id);
296 FETCH cur_state
297 INTO v_vendor_state;
298 CLOSE cur_state;
299 END IF;
300
301 IF v_vendor_state IS NULL THEN
302 OPEN cur_state(0);
303 FETCH cur_state
304 INTO v_vendor_state;
305 CLOSE cur_state;
306 END IF;
307
308 v_organization_state := get_organization_state(pn_organization_id,
309 pn_location_id);
310
311 IF v_vendor_state IS NOT NULL AND v_organization_state IS NOT NULL THEN
312 IF v_vendor_state = v_organization_state THEN
313 RETURN 'N';
314 ELSE
315 RETURN 'Y';
316 END IF;
317 ELSE
318 RETURN NULL;
319 END IF;
320
321 END get_vendor_inter_flag;
322
323 --==========================================================================
324 -- FUNCTION NAME:
325 -- get_vendor_inter_by_ou Private
326 --
327 -- DESCRIPTION:
328 -- to check if transaction is inter-state or not
329 -- compare the state of vendor/site and org setup
330 -- Return:
331 -- Varchar2
332 -- Y: inter-state
333 -- N: intra-state
334 --
335 -- ER NAME/BUG#
336 -- GST Enhancement/ bug 10043656
337 --
338 -- PARAMETERS:
339 -- In:
340 -- pn_org_id Org ID
341 -- pn_vendor_id Vendor ID
342 -- pn_vendor_site_id Vendor Site ID
343 --
344 --
345 -- DESIGN REFERENCES:
346 -- <<TDD_12_2_FIN_JAI_Enhanced_GST.doc>>
347 --
348 -- CALL FROM
349 --
350 --
351 -- CHANGE HISTORY:
352 -- 20-Sep-2010 Created by peng.zheng
353 --==========================================================================
354
355 FUNCTION get_vendor_inter_by_ou(pn_org_id IN NUMBER,
356 pn_vendor_id NUMBER,
357 pn_vendor_site_id NUMBER) RETURN VARCHAR2 IS
358
359 -- to get state
360 CURSOR cur_state(pn_vendor_site_id IN NUMBER DEFAULT 0) IS
361 SELECT STATE_CODE
362 FROM JAI_CMN_VENDOR_SITES A
363 WHERE a.vendor_id = pn_vendor_id
364 AND a.vendor_site_id = pn_vendor_site_id;
365
366 v_vendor_state VARCHAR2(120);
367 v_org_state VARCHAR2(120);
368
369 BEGIN
370
371 IF pn_vendor_site_id IS NOT NULL THEN
372 OPEN cur_state(pn_vendor_site_id);
373 FETCH cur_state
374 INTO v_vendor_state;
375 CLOSE cur_state;
376 END IF;
377
378 IF v_vendor_state IS NULL THEN
379 OPEN cur_state(0);
380 FETCH cur_state
381 INTO v_vendor_state;
382 CLOSE cur_state;
383 END IF;
384
385 v_org_state := get_org_state(pn_org_id);
386
387 IF v_vendor_state IS NOT NULL AND v_org_state IS NOT NULL THEN
388 IF v_vendor_state =
389 v_org_state THEN
390 RETURN 'N';
391 ELSE
392 RETURN 'Y';
393 END IF;
394 ELSE
395 RETURN NULL;
396 END IF;
397
398 END get_vendor_inter_by_ou;
399
400
401 --==========================================================================
402 -- PROCEDURE NAME:
403 -- jai_gst_cust_default_taxes Public
404 --
405 -- DESCRIPTION:
406 -- To get a customer/site's tax default category
407 --
408 -- ER NAME/BUG#
409 -- GST Enhancement/ bug 10043656
410 --
411 -- PARAMETERS:
412 -- In: pn_organization_id 1st Party Organization ID
413 -- pn_location_id 1st Party Location ID
414 -- pn_customer_id Customer ID
415 -- pn_ship_to_site_use_id Ship to site use id
416 -- pn_inventory_item_id Inventory Item
417 -- pd_transaction_date Transaction date, used to compare with the tax category's start date and end date.
418 -- In/Out: pn_tax_category_id The default tax category ID
419 --
420 --
421 -- DESIGN REFERENCES:
422 -- <<TDD_12_2_FIN_JAI_Enhanced_GST.doc>>
423 --
424 -- CALL FROM
425 -- JAI_AR_RCTLA_TRIGGER_PKG
426 -- JAI_AR_RCTA_TRIGGER_PKG
427 -- JAI_MTL_TRXS_PKG
428 -- JAI_OE_OLA_TRIGGER_PKG
429 --
430 --
431 -- CHANGE HISTORY:
432 -- 20-Sep-2010 Created by peng.zheng
433 --==========================================================================
434 PROCEDURE jai_gst_cust_default_taxes(pn_organization_id IN NUMBER,
435 pn_location_id IN NUMBER,
436 pn_customer_id IN NUMBER,
437 pn_ship_to_site_use_id IN NUMBER,
438 pn_inventory_item_id IN NUMBER,
439 pd_transaction_date IN DATE,
440 pn_tax_category_id IN OUT NOCOPY NUMBER) IS
441 v_address_id NUMBER;
442 v_tax_category_list VARCHAR2(30);
443 v_tax_category_id NUMBER;
444
445 lv_object_name CONSTANT VARCHAR2(61) := 'jai_gst_tax_defaultation_pkg.jai_gst_cust_default_taxes';
446
447 -- to get address_id
448 CURSOR address_cur(pn_ship_to_site_use_id IN NUMBER) IS
449 SELECT cust_acct_site_id address_id
450 FROM hz_cust_site_uses_all A
451 WHERE A.site_use_id = pn_ship_to_site_use_id;
452
453 -- to get tax_category_list
454 CURSOR tax_catg_list_cur(pn_customer_id IN NUMBER, p_address_id IN NUMBER DEFAULT 0) IS
455 SELECT tax_category_list
456 FROM JAI_CMN_CUS_ADDRESSES a
457 WHERE A.customer_id = pn_customer_id
458 AND A.address_id = p_address_id;
459
460 CURSOR tax_catg_id_cur(p_tax_category_list IN VARCHAR2, pn_inventory_item_id IN NUMBER, p_inter_flag IN VARCHAR2) IS
461 SELECT t.tax_category_id
462 FROM JAI_INV_ITM_TAXCTG_DTLS a, JAI_CMN_TAX_CTGS_ALL t
463 WHERE pd_transaction_date >= nvl(t.start_date, pd_transaction_date)
464 AND pd_transaction_date < trunc(nvl(t.end_date, pd_transaction_date ) +1)
465 AND nvl(t.INTER_STATE_FLAG, 'N') = p_inter_flag
466 AND t.tax_category_id = a.tax_category_id
467 AND a.tax_category_list = p_tax_category_list
468 AND a.inventory_item_id = pn_inventory_item_id;
469
470 v_inter_flag VARCHAR2(1);
471
472 BEGIN
473
474 OPEN address_cur(pn_ship_to_site_use_id);
475 FETCH address_cur
476 INTO v_address_id;
477 CLOSE address_cur;
478
479 IF pn_customer_id IS NOT NULL AND v_address_id IS NOT NULL THEN
480 OPEN tax_catg_list_cur(pn_customer_id, v_address_id);
481 FETCH tax_catg_list_cur
482 INTO v_tax_category_list;
483 CLOSE tax_catg_list_cur;
484 END IF;
485
486 IF v_tax_category_list IS NULL THEN
487 OPEN tax_catg_list_cur(pn_customer_id, 0);
488 FETCH tax_catg_list_cur
489 INTO v_tax_category_list;
490 CLOSE tax_catg_list_cur;
491 END IF;
492
493 v_inter_flag := get_cust_inter_flag(pn_organization_id,
494 pn_location_id,
495 pn_customer_id,
496 pn_ship_to_site_use_id);
497
498 IF v_inter_flag IS NOT NULL THEN
499
500
501 IF v_tax_category_list IS NOT NULL THEN
502 OPEN tax_catg_id_cur(v_tax_category_list,
503 pn_inventory_item_id,
504 v_inter_flag); --added by peng.zheng for bug 10043656
505 FETCH tax_catg_id_cur
506 INTO v_tax_category_id;
507 CLOSE tax_catg_id_cur;
508
509 END IF;
510
511 IF v_tax_category_id IS NULL THEN
512
513
514 jai_gst_org_default_taxes(pn_organization_id,
515 pn_location_id,
516 pn_customer_id,
517 pn_ship_to_site_use_id,
518 'C',
519 pn_inventory_item_id,
520 pd_transaction_date,
521 v_tax_category_id);
522
523 END IF;
524 END IF; --added by peng.zheng for bug 10043656
525
526 pn_tax_category_id := v_tax_category_id;
527
528
529 EXCEPTION
530 WHEN OTHERS THEN
531 FND_MESSAGE.SET_NAME('JA', 'JAI_EXCEPTION_OCCURED');
532 FND_MESSAGE.SET_TOKEN('JAI_PROCESS_MSG',
533 lv_object_name || '. Err:' || SQLERRM);
534 app_exception.raise_exception;
535
536 END jai_gst_cust_default_taxes;
537
538
539 --==========================================================================
540 -- PROCEDURE NAME:
541 -- jai_gst_vendor_default_taxes Public
542 --
543 -- DESCRIPTION:
544 -- To get a venodr/site's tax category
545 --
546 -- ER NAME/BUG#
547 -- GST Enhancement/ bug 10043656
548 --
549 -- PARAMETERS:
550 -- In: pn_organization_id 1st Party Organization ID
551 -- pn_location_id 1st Party Location ID
552 -- pn_vendor_id Vendor ID
553 -- pn_vendor_site_id Vendor Site ID
554 -- pn_inventory_item_id Inventory Item
555 -- pd_transaction_date Transaction date, used to compare with the tax category's start date and end date.
556 -- In/Out: pn_tax_category_id The default tax category ID
557 --
558 --
559 -- DESIGN REFERENCES:
560 -- <<TDD_12_2_FIN_JAI_Enhanced_GST.doc>>
561 --
562 -- CALL FROM
563 -- JAI_PO_RLA_TRIGGER_PKG
564 -- jai_po_tax_pkg
565 -- JAI_MTL_TRXS_PKG
566 --
567 --
568 -- CHANGE HISTORY:
569 -- 20-Sep-2010 Created by peng.zheng
570 --==========================================================================
571 PROCEDURE jai_gst_vendor_default_taxes(pn_organization_id IN NUMBER,
572 pn_location_id IN NUMBER,
573 pn_vendor_id IN NUMBER,
574 pn_vendor_site_id IN NUMBER,
575 pn_inventory_item_id IN NUMBER,
576 pd_transaction_date IN DATE,
577 pn_tax_category_id IN OUT NOCOPY NUMBER) IS
578
579 v_tax_category_list VARCHAR2(30);
580 v_tax_category_id NUMBER;
581
582 v_inter_flag VARCHAR2(1);
583
584 lv_object_name CONSTANT VARCHAR2(61) := 'jai_gst_tax_defaultation_pkg.jai_gst_vendor_default_taxes';
585
586 -- to get tax_category_list
587 CURSOR tax_catg_list_cur(pn_vendor_id IN NUMBER, pn_vendor_site_id IN NUMBER DEFAULT 0) IS
588 SELECT tax_category_list
589 FROM JAI_CMN_VENDOR_SITES A
590 WHERE a.vendor_id = pn_vendor_id
591 AND a.vendor_site_id = pn_vendor_site_id;
592
593 -- to get tax_category_id
594 CURSOR tax_catg_id_cur(p_tax_category_list IN VARCHAR2, pn_inventory_item_id IN NUMBER, p_inter_flag IN VARCHAR2) IS
595 SELECT t.tax_category_id
596 FROM JAI_INV_ITM_TAXCTG_DTLS a, JAI_CMN_TAX_CTGS_ALL t
597 WHERE pd_transaction_date >= nvl(t.start_date, pd_transaction_date)
598 AND pd_transaction_date < trunc(nvl(t.end_date, pd_transaction_date ) +1)
599 AND nvl(t.INTER_STATE_FLAG, 'N') = p_inter_flag
600 AND a.tax_category_id = t.tax_category_id
601 AND a.tax_category_list = p_tax_category_list
602 AND a.inventory_item_id = pn_inventory_item_id;
603
604 BEGIN
605
606 IF pn_vendor_id IS NOT NULL AND pn_vendor_site_id IS NOT NULL THEN
607 OPEN tax_catg_list_cur(pn_vendor_id, pn_vendor_site_id);
608 FETCH tax_catg_list_cur
609 INTO v_tax_category_list;
610 CLOSE tax_catg_list_cur;
611 END IF;
612
613 IF v_tax_category_list IS NULL THEN
614 OPEN tax_catg_list_cur(pn_vendor_id, 0);
615 FETCH tax_catg_list_cur
616 INTO v_tax_category_list;
617 CLOSE tax_catg_list_cur;
618 END IF;
619
620 v_inter_flag := get_vendor_inter_flag(pn_organization_id,
621 pn_location_id,
622 pn_vendor_id,
623 pn_vendor_site_id);
624 IF v_inter_flag IS NOT NULL THEN
625
626 IF v_tax_category_list IS NOT NULL THEN
627 OPEN tax_catg_id_cur(v_tax_category_list,
628 pn_inventory_item_id,
629 v_inter_flag);
630 FETCH tax_catg_id_cur
631 INTO v_tax_category_id;
632 CLOSE tax_catg_id_cur;
633 END IF;
634
635 IF NVL(v_tax_category_id, 0) = 0 THEN
636
637 jai_gst_org_default_taxes(pn_organization_id,
638 pn_location_id,
639 pn_vendor_id,
640 pn_vendor_site_id,
641 'V',
642 pn_inventory_item_id,
643 pd_transaction_date,
644 v_tax_category_id);
645
646 END IF;
647 END IF; --end IF v_inter_flag IS NOT NULL
648
649 pn_tax_category_id := v_tax_category_id;
650
651 EXCEPTION
652 WHEN OTHERS THEN
653 FND_MESSAGE.SET_NAME('JA', 'JAI_EXCEPTION_OCCURED');
654 FND_MESSAGE.SET_TOKEN('JAI_PROCESS_MSG',
655 lv_object_name || '. Err:' || SQLERRM);
656 app_exception.raise_exception;
657
658 END jai_gst_vendor_default_taxes;
659
660
661 --==========================================================================
662 -- PROCEDURE NAME:
663 -- jai_gst_org_default_taxes Public
664 --
665 -- DESCRIPTION:
666 -- If not get category from customer or vendor's setup, this procedure will using tax classification to fetch tax category
667 --
668 -- ER NAME/BUG#
669 -- GST Enhancement/ bug 10043656
670 --
671 -- PARAMETERS:
672 -- In: pn_organization_id Organization ID
673 -- pn_location_id Location ID
674 -- pn_party_id Customer_id/vendor_id
675 -- pn_party_site_id ship_to_site_use_id when SO(NOT customer_site_id)/vendor_site_id when PO
676 -- pv_party_type C:Customer/V:Vendor
677 -- pn_inventory_item_id Inventory Item
678 -- pd_transaction_date Transaction date, used to compare with the tax category's start date and end date.
679 -- In/Out: pn_tax_category_id The default tax category ID
680 --
681 --
682 -- DESIGN REFERENCES:
683 -- <<TDD_12_2_FIN_JAI_Enhanced_GST.doc>>
684 --
685 -- CALL FROM
686 -- JAI_PO_RLA_TRIGGER_PKG
687 -- JAI_AR_RCTLA_TRIGGER_PKG
688 -- JAI_AR_RCTA_TRIGGER_PKG
689 -- JAI_OE_OLA_TRIGGER_PKG
690 --
691 --
692 -- CHANGE HISTORY:
693 -- 20-Sep-2010 Created by peng.zheng
694 --==========================================================================
695
696 PROCEDURE jai_gst_org_default_taxes(pn_organization_id IN NUMBER,
697 pn_location_id IN NUMBER,
698 pn_party_id IN NUMBER,
699 pn_party_site_id IN NUMBER,
700 pv_party_type IN VARCHAR2,
701 pn_inventory_item_id IN NUMBER,
702 pd_transaction_date IN DATE,
703 pn_tax_category_id IN OUT NOCOPY NUMBER) IS
704
705 lv_object_name CONSTANT VARCHAR2(61) := 'jai_gst_tax_defaultation_pkg.jai_gst_org_default_taxes';
706
707 v_operating_unit NUMBER;
708
709 CURSOR cur_get_master_org_id IS
710 SELECT org_id operating_unit, master_organization_id
711 FROM oe_system_parameters;
712
713 CURSOR operating_unit_cur(c_org_id NUMBER) IS
714 SELECT operating_unit
715 FROM org_organization_definitions
716 WHERE organization_id = NVL(c_org_id, 0);
717
718 rec_cur_get_master_org_id CUR_GET_MASTER_ORG_ID%ROWTYPE;
719
720 --get tax_classification, if cgst not found, get from SGST
724 WHERE a.attribute_code = jai_constants.tax_classification
721 CURSOR tax_classification_cur(v_org_id IN NUMBER, v_inventory_item_id IN NUMBER) IS
722 SELECT a.attribute_value
723 FROM JAI_RGM_ITM_TMPL_ATTRS a, JAI_RGM_ITM_REGNS r
725 AND r.RGM_ITEM_REGNS_ID = a.RGM_ITEM_REGNS_ID
726 AND r.inventory_item_id = v_inventory_item_id
727 AND r.organization_id = v_org_id
728 AND r.regime_code = jai_constants.cgst_regime;
729 CURSOR sgst_tax_classification_cur(v_org_id IN NUMBER, v_inventory_item_id IN NUMBER) IS
730 SELECT a.attribute_value
731 FROM JAI_RGM_ITM_TMPL_ATTRS a, JAI_RGM_ITM_REGNS r
732 WHERE a.attribute_code = jai_constants.tax_classification
733 AND r.RGM_ITEM_REGNS_ID = a.RGM_ITEM_REGNS_ID
734 AND r.inventory_item_id = v_inventory_item_id
735 AND r.organization_id = v_org_id
736 AND r.regime_code = jai_constants.sgst_regime;
737
738 v_inter_flag VARCHAR2(1);
739 v_tax_classification VARCHAR2(240);
740 v_organization_id NUMBER;
741
742 BEGIN
743
744 IF pn_organization_id IS NULL THEN
745 OPEN cur_get_master_org_id;
746 FETCH cur_get_master_org_id
747 INTO rec_cur_get_master_org_id;
748 CLOSE cur_get_master_org_id;
749 v_operating_unit := rec_cur_get_master_org_id.operating_unit;
750 ELSE
751
752 OPEN operating_unit_cur(pn_organization_id);
753 FETCH operating_unit_cur
754 INTO v_operating_unit;
755 CLOSE operating_unit_cur;
756 END IF;
757
758 v_operating_unit := NVL(v_operating_unit, 0);
759 v_organization_id := nvl(pn_organization_id, rec_cur_get_master_org_id.master_organization_id);
760
761 IF pv_party_type = 'C' THEN
762 v_inter_flag := get_cust_inter_flag(v_organization_id,
763 pn_location_id,
764 pn_party_id,
765 pn_party_site_id);
766 ELSIF pv_party_type = 'V' THEN
767 v_inter_flag := get_vendor_inter_flag(v_organization_id,
768 pn_location_id,
769 pn_party_id,
770 pn_party_site_id);
771 END IF;
772
773 IF v_inter_flag IS NOT NULL THEN
774 OPEN tax_classification_cur(v_organization_id, pn_inventory_item_id);
775 FETCH tax_classification_cur
776 INTO v_tax_classification;
777 CLOSE tax_classification_cur;
778
779 IF v_tax_classification IS NULL THEN
780 OPEN sgst_tax_classification_cur(v_organization_id,
781 pn_inventory_item_id);
782 FETCH sgst_tax_classification_cur
783 INTO v_tax_classification;
784 CLOSE sgst_tax_classification_cur;
785
786 END IF;
787
788 IF v_tax_classification IS NOT NULL THEN
789 BEGIN
790 SELECT b.tax_category_id
791 INTO pn_tax_category_id
792 FROM JAI_CMN_TAX_CTGS_ALL b
793 WHERE pd_transaction_date >= nvl(b.start_date, pd_transaction_date)
794 AND pd_transaction_date < trunc(nvl(b.end_date, pd_transaction_date ) +1)
795 AND nvl(b.inter_state_flag, 'N') = v_inter_flag
796 AND b.tax_classification = v_tax_classification
797 AND b.org_id = v_operating_unit;
798 EXCEPTION
799 WHEN OTHERS THEN
800 --Not normally found, no_data_found or too_much_record or others.
801 --pn_tax_category_id remains NULL.
802 pn_tax_category_id := NULL;
803 END;
804
805 END IF;
806 END IF; --IF v_inter_flag IS NOT NULL
807
808 EXCEPTION
809 WHEN OTHERS THEN
810 FND_MESSAGE.SET_NAME('JA', 'JAI_EXCEPTION_OCCURED');
811 FND_MESSAGE.SET_TOKEN('JAI_PROCESS_MSG',
812 lv_object_name || '. Err:' || SQLERRM);
813 app_exception.raise_exception;
814
815 END jai_gst_org_default_taxes;
816
817 END jai_gst_tax_defaultation_pkg;
[Home] [Help]