1 PACKAGE jai_cmn_tax_defaultation_pkg AUTHID CURRENT_USER AS
2 /* $Header: jai_cmn_tax_dflt.pls 120.8.12020000.3 2013/03/19 03:03:42 qimeng ship $ */
3
4 /*----------------------------------------------------------------------------------------------------------------------
5 CHANGE HISTORY: FILENAME: ja_in_tax_pkg.sql
6 S.No Date Author and Details
7 ------------------------------------------------------------------------------------------------------------------------
8 1 04/07/2002 Aparajita for bug # 2381492
9 RMA without reference -- LEGACY changes
10
11 2 06/12/2002 cbabu for EnhancementBug# 2427465, FileVersion# 615.1
12 tax_category_id column is populated into PO and SO localization tables, which will be used to
13 identify from which tax_category the taxes are defaulted. Also the tax_category_id populated into the
14 tax table will be useful to identify whether the tax is a defaulted or a manual tax
15
16 3 25/03/2003 cbabu for Bug# 2837970, FileVersion# 615.2
17 Modified to include data population into JAI_CRM_QUOTE_TAXES table when ja_in_calc_prec_taxes procedure
18 is called with required parameters.
19 p_operation_flag parameter contains aso_shipments.shipment_id when invoked from ja_in_crm_quote_taxes (called
20 from JAINPQTEL.rdf report) procedure for quote taxes calculation
21
22 4. 08/05/2003 sriram - bug # 2812781 File Version 615.3
23 When a po is created with very large quantity , upon saving the purchase order
24 a numeric or value error . The reason for this behaviour is that because there were
25 two table of numbers wose width was (14,3) This has been changed to (30,3). This has solved the
26 issue.
27 5. 12/07/2003 Aiyer - Bug #3749294 File Version 115.1
28 Issue:-
29 Uom based taxes do not get calculated correctly if the transaction UOM is different from the
30 UOM setup in the tax definitions India Localization (JAI_CMN_TAXES_ALL table).
31
32 Fix:-
33 ----
34 Modified the procedure ja_in_calc_prec_taxes.For more details please refer the change history
35 section of the concerned procedure.
36
37 Dependency Due to This Bug:-
38 ----------------------------
39 None
40
41 6 22/09/2004 Aiyer for bug#3792765. Version#115.2
42 Issue:-
43 Warehouse ID is not currently being allowed to be left null from the base apps sales order. When placing a order from 2
44 different manufacturing organizations, it is required that customer temporarily leaves the warehouseid as Null and then
45 updates the same before pick release. However this is currently not allowed by localization even though base
46 apps allows this feature.
47
48 Fix:-
49 ----
50 Modified the procedure ja_in_tax_pkg.ja_in_org_default_taxes. For more details please refer the change history
51 section of the concerned procedure.
52
53 Dependency Due to this Bug:-
54 ----------------------------
55 Functional dependency with the trigger ja_in_oe_order_lines_aiu_trg.sql version 115.4
56
57
58 7 27/01/2005 ssumaith - bug#4136981
59
60 In case of Bond Register Scenario taxes should not be defaulted to the Sales order or Manual Ar transaction.
61
62 This fix does not introduce dependency on this object , but this patch cannot be sent alone to the CT
63 because it relies on the alter done and the new tables created as part of the education cess enhancement
64 bug# 4146708 creates the objects
65
66
67 8. 08-Jun-2005 Version 116.1 jai_cmn_tax_dflt -Object is Modified to refer to New DB Entity names in place of Old DB Entity Names
68 as required for CASE COMPLAINCE.
69
70
71 9 30/01/2007 Bgowrava for forward porting Bug#5631784, Version 116.2
72 1. Added a record type tax_rec_typ to fetch the record using reference cursor
73 2. Added a REF CURSOR ref_cur_typ. The cursor will refer to a active sql object
74 depending upon the value of p_action. If it is DEFAULT then a normal cursor
75 will be used. If it is RECALCULATE then a dynamic cursor will be used based on the
76 p_source_trx_type
77 3. Added a procedure GET_TAX_CAT_TAXES_CUR. The procedure is used to retrieve cursor object.
78 Procedure is also used in JAI_RGM_THHOLD_PROC_PKG.DEFAULT_THHOLD_TAXES
79
80 10 09/08/2007 rchandan for bug#6030615. File Version 120.3
81 Issue: Forward Porting of Inter Org
82 Fix : Uncommented adhoc_flag in pl/sql table
83 Future Dependencies For the release Of this Object:-
84 (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/
85 A datamodel change )
86
87 11 04/09/2007 Jason Liu Update the procedure ja_in_calc_prec_taxes
88 to handle the AP Standalone Invoice
89
90 12 01/15/2008 Kevin Cheng Add a parameter in ja_in_calc_prec_taxes and get_tax_cat_taxes_cur to distinguish retroactive
91 price update action.
92
93 13 10/09/2010 Jia for GST Bug#10043656
94
95 14. 11-Mar-2011 Bo Li for BOE Enhancement bug#11684111
96
97 15. 27-Sep-2012 mmurtuza for bug 14675629
98 Issue: IN SHIP CONFIRM LOCALIZED FORM, WRONG TCS TAX AMOUNTS GETTING DEFAULTED
99 Fix: Added qty_rate variable in type tax_rec_typ
100
101 ----------------------------------------------------------------------------------------------------------------------------------------------------
102 Current Version Current Bug Dependent Files Version Author Date Remarks
103 Of File On Bug/Patchset Dependent On
104 ja_in_tax_pkg
105 ----------------------------------------------------------------------------------------------------------------------------------------------------
106 115.2 3792765 IN60105D2 ja_in_oe_order_lines_aiu_trg.sql 115.4 Aiyer 22/09/2004 Functional dependency
107
108
109
110 ----------------------------------------------------------------------------------------------------------------------------------------------------
111
112 -----------------------------------------------------------------------------------------------------------------------*/
113
114 /* bgowrava for forward porting bug#5631784, TCS Enh.
115 || Following record must be modified according to any modification done in select column list of curosr REFC_TAX_CUR
116 */
117 type ref_cur_typ is ref cursor;
118 type tax_rec_typ is record
119 (
120 tax_id JAI_CMN_TAXES_ALL.tax_id%type
121 , lno JAI_CMN_TAX_CTG_LINES.line_no%type
122 , p_1 JAI_CMN_TAX_CTG_LINES.precedence_1%type
123 , p_2 JAI_CMN_TAX_CTG_LINES.precedence_2%type
124 , p_3 JAI_CMN_TAX_CTG_LINES.precedence_3%type
125 , p_4 JAI_CMN_TAX_CTG_LINES.precedence_4%type
126 , p_5 JAI_CMN_TAX_CTG_LINES.precedence_5%type
127 , p_6 JAI_CMN_TAX_CTG_LINES.precedence_6%type
128 , p_7 JAI_CMN_TAX_CTG_LINES.precedence_7%type
129 , p_8 JAI_CMN_TAX_CTG_LINES.precedence_8%type
130 , p_9 JAI_CMN_TAX_CTG_LINES.precedence_9%type
131 , p_10 JAI_CMN_TAX_CTG_LINES.precedence_10%type
132 , tax_rate JAI_CMN_TAXES_ALL.tax_rate%type
133 , tax_amount JAI_CMN_TAXES_ALL.tax_amount%type
134 , qty_rate JAI_CMN_TAXES_ALL.tax_amount%type /*Added qty_rate by mmurtuza for bug 14675629*/
135 , uom_code JAI_CMN_TAXES_ALL.uom_code%type
136 , valid_date JAI_CMN_TAXES_ALL.end_date%type
137 , tax_type_val number
138 , mod_cr_percentage JAI_CMN_TAXES_ALL.mod_cr_percentage%type
139 , vendor_id JAI_CMN_TAXES_ALL.vendor_id%type
140 , tax_type JAI_CMN_TAXES_ALL.tax_type%type
141 , rounding_factor JAI_CMN_TAXES_ALL.rounding_factor%type
142 , adhoc_flag JAI_CMN_TAXES_ALL.adhoc_flag%type
143 , tax_category_id JAI_CMN_TAX_CTGS_ALL.tax_category_id%type
144 , inclusive_tax_flag JAI_CMN_TAXES_ALL.inclusive_tax_flag%type --added by walton for inclusive tax
145 );
146 /*end of bug#5631784 */
147
148 PROCEDURE ja_in_cust_default_taxes(
149 p_org_id NUMBER,
150 p_customer_id NUMBER,
151 p_ship_to_site_use_id NUMBER,
152 p_inventory_item_id IN NUMBER,
153 p_header_id NUMBER,
154 p_line_id NUMBER,
155 p_tax_category_id IN OUT NOCOPY NUMBER
156 );
157
158 PROCEDURE ja_in_vendor_default_taxes(
159 p_org_id NUMBER,
160 p_vendor_id NUMBER,
161 p_vendor_site_id NUMBER,
162 p_inventory_item_id IN NUMBER,
163 p_header_id NUMBER,
164 p_line_id NUMBER,
165 p_tax_category_id IN OUT NOCOPY NUMBER
166 );
167
168 PROCEDURE ja_in_org_default_taxes(
169 p_org_id NUMBER,
170 p_inventory_item_id IN NUMBER,
171 p_tax_category_id IN OUT NOCOPY NUMBER
172 );
173
174 PROCEDURE ja_in_calc_prec_taxes(
175 transaction_name VARCHAR2,
176 P_tax_category_id NUMBER,
177 p_header_id NUMBER,
178 p_line_id NUMBER,
179 p_assessable_value NUMBER DEFAULT 0,
180 p_tax_amount IN OUT NOCOPY NUMBER,
181 p_inventory_item_id NUMBER,
182 p_line_quantity NUMBER,
183 p_uom_code VARCHAR2,
184 p_vendor_id NUMBER,
185 p_currency VARCHAR2,
186 p_currency_conv_factor NUMBER,
187 p_creation_date DATE,
188 p_created_by NUMBER,
189 p_last_update_date DATE,
190 p_last_updated_by NUMBER,
191 p_last_update_login NUMBER,
192 p_operation_flag NUMBER DEFAULT NULL,
193 p_vat_assessable_value NUMBER DEFAULT 0
194 /** bgowrava for forward porting bug#5631784,Following parameters are added for TCS enh.*/
195 , p_thhold_cat_base_tax_typ JAI_CMN_TAXES_ALL.tax_type%type default null -- tax type to be considered as base when calculating threshold taxes
196 , p_threshold_tax_cat_id JAI_AP_TDS_THHOLD_TAXES.tax_category_id%type default null
197 , p_source_trx_type jai_cmn_document_taxes.source_doc_type%type default null
198 , p_source_table_name jai_cmn_document_taxes.source_table_name%type default null
199 , p_action varchar2 default null
200 /** End bug 5631784 */
201 , pv_retroprice_changed IN VARCHAR2 DEFAULT 'N' --Added by Kevin Cheng for Retroactive Price 2008/01/13
202 , p_modified_by_agent_flag po_requisition_lines_all.modified_by_agent_flag%type default NULL /*Added for Bug 8241905*/
203 , p_parent_req_line_id po_requisition_lines_all.parent_req_line_id%type default NULL /*Added for Bug 8241905*/
204 , p_max_tax_line NUMBER DEFAULT 0 /*Added for Bug 8371741*/
205 , p_max_rgm_tax_line NUMBER DEFAULT 0 /*Added for Bug 8371741*/
206 --, pn_gst_assessable_value NUMBER DEFAULT 0 --Added by Jia for GST Bug#10043656 on 2010/09/10
207 , pn_customs_assessable_value NUMBER DEFAULT 0 --Added by Bo Li for Bug#11684111 BOE Ehancement
208 );
209
210 /* bgowrava for forward porting bug#5631784, TCS Enh. */
211 procedure get_tax_cat_taxes_cur
215 ,p_max_rgm_tax_line number default 0
212 ( p_tax_category_id number
213 ,p_threshold_tax_cat_id number default null
214 ,p_max_tax_line number default 0
216 ,p_base number default 0
217 ,p_refc_tax_cat_taxes_cur out nocopy ref_cur_typ
218 , pv_retroprice_changed IN VARCHAR2 DEFAULT 'N' --Added by Kevin Cheng for Retroactive Price 2008/01/13
219 );
220 /*end of bug#5631784 */
221
222 END jai_cmn_tax_defaultation_pkg;