1 PACKAGE jai_cmn_rgm_recording_pkg AUTHID CURRENT_USER AS
2 /* $Header: jai_cmn_rgm_rec.pls 120.12 2011/12/13 06:56:14 qioliu ship $ */
3
4 /* */
5 /*----------------------------------------------------------------------------------------------------------------------------
6 CHANGE HISTORY for FILENAME: jai_rgm_trx_recording_pkg_s.sql
7 S.No dd/mm/yyyy Author and Details
8 ------------------------------------------------------------------------------------------------------------------------------
9 1 15/12/2004 Vijay Shankar for Bug# 4068823, Version:115.0
10
11 Coded for recording Service Tax into repository and related Accounting into GL
12
13 2 25-April-2007 ssawant for bug 5879769 ,File version
14 Forward porting of
15 ENH : SERVICE TAX BY INVENTORY ORGANIZATION AND SERVICE TYPE SOLUTION from 11.5( bug no 5694855) to R12 (bug no 5879769).
16 Fix : A new parameter p_service_type_code is added to insert_repository procedure
17
18 DEPENDANCY:
19 -----------
20 IN60106 + 4068823
21
22
23 2. 08-Jun-2005 Version 116.1 jai_cmn_rgm_rec -Object is Modified to refer to New DB Entity names in place of Old DB Entity Names
24 as required for CASE COMPLAINCE.
25
26 3. 21-Nov-2008 Changes by nprashar for bug # 7525691
27 Issue: SVC TX SETTLEMENT PROCESS WITH DIFF SVC TYPES DIDN'T CREATE NETTING SERVICE JE
28 FIX: added a parameter p_balancing_entry to the procedure insert_repository_entry
29 4. 06-Feb-2009 Bug 7525691 - The fix can be done without the p_balancing_entry paramter. Doing so will
30 avoid any dependencies due to this bug. Therefore, removed the p_balancing_entry from
31 the specification.
32
33 5. 18-Mar-2009 Bug 7525691 - File version 120.1.12000000.6/120.2.12010000.5/120.6
34 Introduced a new parameter p_distribution_type for procedure insert_repository_entry.
35
36 6. 22-May-2009 Bug 8294236
37 Issue: Service Tax Transaction created Fr Exchange Balances on Tax Accounts after Settlement
38 Fix: Created a new procedure exc_gain_loss_accounting for creating the accounting
39 entries for foreign exchange gain or loss amount.
40
41 7. 25-Dec-2009 Code change for bug7145898
42 Issue: VAT SETTLEMENT NOT HAPPENING AT REGN LEVEL ON HAVING MORE THEN ONE OU
43 Fix : The settlment can be done for this case.
44
45 8. 4-Apr-2010 Bo Li for Bug9305067
46 Modify the procedure insert_repository_entry and insert_vat_repository_entry.
47 Replace the attribute parameters with new meaningful parameters
48
49 9. 24-May-2011 Xiao for pot change, reg bug#12533434.
50 Add parameter p_accrual_basis for procedure insert_repository_entry, and it indicates accrual basis.
51 If accrual basis, do not generate accounting.
52 ----------------------------------------------------------------------------------------------------------------------------*/
53
54 CURSOR c_regime_code(cp_regime_id IN NUMBER) IS
55 SELECT regime_code
56 FROM JAI_RGM_DEFINITIONS
57 WHERE regime_id = cp_regime_id;
58
59 CURSOR c_repository_dtl(cp_repository_id IN NUMBER) IS
60 SELECT regime_code, tax_type, source, source_table_name, source_document_id,
61 source_trx_type, organization_id
62 FROM jai_rgm_trx_records
63 WHERE repository_id = cp_repository_id;
64
65 g_debug CONSTANT VARCHAR2(1) := 'Y';
66 ap_discount_accnt CONSTANT VARCHAR2(30) := 'AP_DISCOUNT_ACCOUNT';
67 gd_accounting_date_dflt CONSTANT DATE := SYSDATE;
68
69 -- COMMON API that will be called from DIFFERENT Transactions of the Regime
70 -- This will call APIS to insert data into regime repository and GL Tables
71 PROCEDURE insert_repository_entry(
72 p_repository_id OUT NOCOPY NUMBER,
73 p_regime_id IN NUMBER,
74 p_tax_type IN VARCHAR2,
75 p_organization_type IN VARCHAR2,
76 p_organization_id IN NUMBER,
77 p_location_id IN NUMBER,
78 p_source IN VARCHAR2,
79 p_source_trx_type IN VARCHAR2,
80 p_source_table_name IN VARCHAR2,
81 p_source_document_id IN NUMBER,
82 p_transaction_date IN DATE,
83 p_account_name IN VARCHAR2,
84 p_charge_account_id IN NUMBER,
85 p_balancing_account_id IN NUMBER,
86 p_amount IN OUT NOCOPY NUMBER, -- Recovered/Liable Service Tax Amount in INR Currency i.e functional
87 p_assessable_value IN NUMBER,
88 p_tax_rate IN NUMBER,
89 p_reference_id IN NUMBER,
90 p_batch_id IN NUMBER,
91 p_called_from IN VARCHAR2,
92 p_process_flag OUT NOCOPY VARCHAR2,
93 p_process_message OUT NOCOPY VARCHAR2,
94 p_discounted_amount IN OUT NOCOPY NUMBER,
95 p_inv_organization_id IN NUMBER DEFAULT NULL,
96 p_settlement_id IN NUMBER DEFAULT NULL,
97 -- Following all parameters are required for GL Accounting if p_balancing_account_id value is not passed to this procedure call
98 p_accntg_required_flag IN VARCHAR2, -- DEFAULT jai_constants.yes File.Sql.35 by Brathod
99 p_accounting_date IN DATE , -- DEFAULT sysdate File.Sql.35 by Brathod
100 p_balancing_orgn_type IN VARCHAR2 DEFAULT NULL,
101 p_balancing_orgn_id IN NUMBER DEFAULT NULL,
102 p_balancing_location_id IN NUMBER DEFAULT NULL,
103 p_balancing_tax_type IN VARCHAR2 DEFAULT NULL,
104 p_balancing_accnt_name IN VARCHAR2 DEFAULT NULL,
105 p_currency_code IN VARCHAR2 , -- DEFAULT jai_constants.func_curr File.Sql.35 by Brathod
106 p_curr_conv_date IN VARCHAR2 DEFAULT NULL,
107 p_curr_conv_type IN VARCHAR2 DEFAULT NULL,
108 p_curr_conv_rate IN VARCHAR2 DEFAULT NULL,
109 p_trx_amount IN NUMBER DEFAULT NULL, -- recovered/liable service tax amount in foreign currency
110 --Added by Bo Li for Bug9305067 BEGIN
111 ------------------------------------------------------------
112 p_trx_reference_context IN VARCHAR2 DEFAULT NULL,
113 p_trx_reference1 IN VARCHAR2 DEFAULT NULL,
114 p_trx_reference2 IN VARCHAR2 DEFAULT NULL,
115 p_trx_reference3 IN VARCHAR2 DEFAULT NULL,
116 p_trx_reference4 IN VARCHAR2 DEFAULT NULL,
117 p_trx_reference5 IN VARCHAR2 DEFAULT NULL,
118 ----------------------------------------------------------
119 --Added by Bo Li for Bug9305067 END
120 p_service_type_code IN VARCHAR2 DEFAULT NULL, /* added by ssawant for bug 5989740 */
121 p_distribution_type IN VARCHAR2 DEFAULT NULL, /*bug 7525691*/
122 p_accrual_basis IN VARCHAR2 DEFAULT NULL ,--Add by Xiao for POT change, reg bug#12533434 on 16-May-2011
123 p_invoice_no IN VARCHAR2 DEFAULT NULL --Added by Qiong for advanced receipts bug#13361952
124 );
125
126 PROCEDURE post_accounting(
127 p_regime_code IN VARCHAR2,
128 p_tax_type IN VARCHAR2,
129 p_organization_type IN VARCHAR2,
130 p_organization_id IN NUMBER,
131 p_source IN VARCHAR2,
132 p_source_trx_type IN VARCHAR2,
133 p_source_table_name IN VARCHAR2,
134 p_source_document_id IN NUMBER,
135 p_code_combination_id IN NUMBER,
136 -- Transaction Currency Amount
137 p_entered_cr IN NUMBER,
138 p_entered_dr IN NUMBER,
139 -- Functional Currency Amount
140 p_accounted_cr IN NUMBER,
141 p_accounted_dr IN NUMBER,
142 p_accounting_date IN DATE,
143 p_transaction_date IN DATE,
144 p_calling_object IN VARCHAR2,
145 p_repository_name IN VARCHAR2 DEFAULT NULL,
146 p_repository_id IN NUMBER DEFAULT NULL,
147 p_reference_name IN VARCHAR2 DEFAULT NULL,
148 p_reference_id IN NUMBER DEFAULT NULL,
149 p_currency_code IN VARCHAR2 DEFAULT NULL,
150 p_curr_conv_date IN DATE DEFAULT NULL,
151 p_curr_conv_type IN VARCHAR2 DEFAULT NULL,
152 p_curr_conv_rate IN NUMBER DEFAULT NULL,
153 p_attribute1 IN VARCHAR2 DEFAULT NULL,
154 p_attribute2 IN VARCHAR2 DEFAULT NULL
155 );
156
157 PROCEDURE insert_reference(
158 p_reference_id OUT NOCOPY NUMBER,
159 p_organization_id IN NUMBER,
160 p_source IN VARCHAR2,
161 p_invoice_id IN NUMBER,
162 p_line_id IN NUMBER,
163 p_tax_type IN VARCHAR2,
164 p_tax_id IN NUMBER,
165 p_tax_rate IN NUMBER,
166 p_recoverable_ptg IN NUMBER,
167 p_party_type IN VARCHAR2,
168 p_party_id IN NUMBER,
169 p_party_site_id IN NUMBER,
170 p_trx_tax_amount IN NUMBER,
171 p_trx_currency IN VARCHAR2,
172 p_curr_conv_date IN DATE,
173 p_curr_conv_rate IN NUMBER,
174 p_tax_amount IN NUMBER,
175 p_recoverable_amount IN NUMBER,
176 p_recovered_amount IN NUMBER,
177 p_item_line_id IN NUMBER,
178 p_item_id IN NUMBER,
179 p_taxable_basis IN NUMBER,
180 p_parent_reference_id IN NUMBER,
181 p_reversal_flag IN VARCHAR2,
182 p_batch_id IN NUMBER,
183 p_process_flag OUT NOCOPY VARCHAR2,
184 p_process_message OUT NOCOPY VARCHAR2
185 );
186
187 FUNCTION get_account(
188 p_regime_id IN NUMBER,
189 p_organization_type IN VARCHAR2,
190 p_organization_id IN NUMBER,
191 p_location_id IN NUMBER,
192 p_tax_type IN VARCHAR2,
193 p_account_name IN VARCHAR2
194 ) RETURN NUMBER;
195
196 PROCEDURE get_period_name(
197 p_organization_type IN VARCHAR2,
198 p_organization_id IN NUMBER,
199 p_accounting_date IN OUT NOCOPY DATE,
200 p_period_name OUT NOCOPY VARCHAR2,
201 p_sob_id OUT NOCOPY NUMBER
202 );
203
204 PROCEDURE update_reference(
205 p_source IN VARCHAR2,
206 p_reference_id IN NUMBER,
207 p_recovered_amount IN NUMBER,
208 p_discounted_amount IN NUMBER DEFAULT NULL,
209 p_process_flag OUT NOCOPY VARCHAR2,
210 p_process_message OUT NOCOPY VARCHAR2
211 );
212
213 /* following procedure added by Vijay Shankar for Bug#4250236(4245089). VAT Impl. */
214 PROCEDURE insert_vat_repository_entry(
215 pn_repository_id OUT NOCOPY NUMBER,
216 pn_regime_id IN NUMBER,
217 pv_tax_type IN VARCHAR2,
218 pv_organization_type IN VARCHAR2,
219 pn_organization_id IN NUMBER,
220 pn_location_id IN NUMBER,
221 pv_source IN VARCHAR2,
222 pv_source_trx_type IN VARCHAR2,
223 pv_source_table_name IN VARCHAR2,
224 pn_source_id IN NUMBER,
225 pd_transaction_date IN DATE,
226 pv_account_name IN VARCHAR2,
227 pn_charge_account_id IN NUMBER,
228 pn_balancing_account_id IN NUMBER,
229 pn_credit_amount IN OUT NOCOPY NUMBER,
230 pn_debit_amount IN OUT NOCOPY NUMBER,
231 pn_assessable_value IN NUMBER,
232 pn_tax_rate IN NUMBER,
233 pn_reference_id IN NUMBER,
234 pn_batch_id IN NUMBER,
235 pn_inv_organization_id IN NUMBER,
236 pv_invoice_no IN VARCHAR2, /* this holds either generated VAT Invoice Number or Vendor Inovice Number */
237 pd_invoice_date IN DATE, /* this holds VAT Invoice Date or Vendor VAT Inovice Date */
238 pv_called_from IN VARCHAR2,
239 pv_process_flag OUT NOCOPY VARCHAR2,
240 pv_process_message OUT NOCOPY VARCHAR2,
241 --Added by Bo Li for Bug9305067 BEGIN
242 ---------------------------------------------------------------
243 pv_trx_reference_context IN VARCHAR2 DEFAULT NULL,
244 pv_trx_reference1 IN VARCHAR2 DEFAULT NULL,
245 pv_trx_reference2 IN VARCHAR2 DEFAULT NULL,
246 pv_trx_reference3 IN VARCHAR2 DEFAULT NULL,
247 pv_trx_reference4 IN VARCHAR2 DEFAULT NULL,
248 pv_trx_reference5 IN VARCHAR2 DEFAULT NULL,
249 ------------------------------------------------------------------
250 --Added by Bo Li for Bug9305067 END
251 pn_settlement_id IN NUMBER DEFAULT NULL --added for bug#7145898, Eric Ma
252 );
253
254 /* following Procedure added by Vijay Shankar for Bug#4250236(4245089). VAT Impl. */
255 PROCEDURE do_vat_accounting(
256 pn_regime_id IN NUMBER,
257 pn_repository_id IN NUMBER,
258 pv_organization_type IN VARCHAR2,
259 pn_organization_id IN NUMBER,
260 pd_accounting_date IN DATE,
261 pd_transaction_date IN DATE,
262 pn_credit_amount IN NUMBER,
263 pn_debit_amount IN NUMBER,
264 pn_credit_ccid IN NUMBER,
265 pn_debit_ccid IN NUMBER,
266 pv_called_from IN VARCHAR2,
267 pv_process_flag OUT NOCOPY VARCHAR2,
268 pv_process_message OUT NOCOPY VARCHAR2,
269 pv_tax_type IN VARCHAR2 DEFAULT NULL,
270 pv_source IN VARCHAR2 DEFAULT NULL,
271 pv_source_trx_type IN VARCHAR2 DEFAULT NULL,
272 pv_source_table_name IN VARCHAR2 DEFAULT NULL,
273 pn_source_id IN NUMBER DEFAULT NULL,
274 pv_reference_name IN VARCHAR2 DEFAULT NULL,
275 pn_reference_id IN NUMBER DEFAULT NULL
276 );
277
278 /* following function added by Vijay Shankar for Bug#4250236(4245089). VAT Impl. */
279 FUNCTION get_rgm_attribute_value(
280 pv_regime_code IN VARCHAR2,
281 pv_organization_type IN VARCHAR2,
282 pn_organization_id IN NUMBER,
283 pn_location_id IN NUMBER,
284 pv_registration_type IN VARCHAR2,
285 pv_attribute_type_code IN VARCHAR2,
286 pv_attribute_code IN VARCHAR2
287 ) RETURN VARCHAR2;
288
289 /*Added this procedure for Bug 8294236*/
290 PROCEDURE exc_gain_loss_accounting(
291 p_repository_id IN NUMBER,
292 p_regime_id IN NUMBER,
293 p_tax_type IN VARCHAR2,
294 p_organization_type IN VARCHAR2,
295 p_organization_id IN NUMBER,
296 p_location_id IN NUMBER,
297 p_source IN VARCHAR2,
298 p_source_trx_type IN VARCHAR2,
299 p_source_table_name IN VARCHAR2,
300 p_source_document_id IN NUMBER,
301 p_transaction_date IN DATE,
302 p_account_name IN VARCHAR2,
303 p_charge_account_id IN NUMBER,
304 p_balancing_account_id IN NUMBER,
305 p_exc_gain_loss_amt IN OUT NOCOPY NUMBER,
306 p_reference_id IN NUMBER,
307 p_called_from IN VARCHAR2,
308 p_process_flag OUT NOCOPY VARCHAR2,
309 p_process_message OUT NOCOPY VARCHAR2,
310 p_accounting_date IN DATE DEFAULT sysdate,
311 p_balancing_orgn_type IN VARCHAR2 DEFAULT NULL,
312 p_balancing_orgn_id IN NUMBER DEFAULT NULL,
313 p_balancing_location_id IN NUMBER DEFAULT NULL,
314 p_balancing_tax_type IN VARCHAR2 DEFAULT NULL,
315 p_balancing_accnt_name IN VARCHAR2 DEFAULT NULL,
316 p_currency_code IN VARCHAR2 DEFAULT jai_constants.func_curr,
317 p_curr_conv_date IN VARCHAR2 DEFAULT NULL,
318 p_curr_conv_type IN VARCHAR2 DEFAULT NULL,
319 p_curr_conv_rate IN VARCHAR2 DEFAULT NULL
320 );
321
322 END jai_cmn_rgm_recording_pkg;
[Home] [Help]