1 PACKAGE jai_cmn_rgm_recording_pkg AS
2 /* $Header: jai_cmn_rgm_rec.pls 120.2 2007/04/26 03:59:24 ssawant 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
27 ----------------------------------------------------------------------------------------------------------------------------*/
28
29 CURSOR c_regime_code(cp_regime_id IN NUMBER) IS
30 SELECT regime_code
31 FROM JAI_RGM_DEFINITIONS
32 WHERE regime_id = cp_regime_id;
33
34 CURSOR c_repository_dtl(cp_repository_id IN NUMBER) IS
35 SELECT regime_code, tax_type, source, source_table_name, source_document_id,
36 source_trx_type, organization_id
37 FROM jai_rgm_trx_records
38 WHERE repository_id = cp_repository_id;
39
40 g_debug CONSTANT VARCHAR2(1) := 'Y';
41 ap_discount_accnt CONSTANT VARCHAR2(30) := 'AP_DISCOUNT_ACCOUNT';
42 gd_accounting_date_dflt CONSTANT DATE := SYSDATE;
43
44 -- COMMON API that will be called from DIFFERENT Transactions of the Regime
45 -- This will call APIS to insert data into regime repository and GL Tables
46 PROCEDURE insert_repository_entry(
47 p_repository_id OUT NOCOPY NUMBER,
48 p_regime_id IN NUMBER,
49 p_tax_type IN VARCHAR2,
50 p_organization_type IN VARCHAR2,
51 p_organization_id IN NUMBER,
52 p_location_id IN NUMBER,
53 p_source IN VARCHAR2,
54 p_source_trx_type IN VARCHAR2,
55 p_source_table_name IN VARCHAR2,
56 p_source_document_id IN NUMBER,
57 p_transaction_date IN DATE,
58 p_account_name IN VARCHAR2,
59 p_charge_account_id IN NUMBER,
60 p_balancing_account_id IN NUMBER,
61 p_amount IN OUT NOCOPY NUMBER, -- Recovered/Liable Service Tax Amount in INR Currency i.e functional
62 p_assessable_value IN NUMBER,
63 p_tax_rate IN NUMBER,
64 p_reference_id IN NUMBER,
65 p_batch_id IN NUMBER,
66 p_called_from IN VARCHAR2,
67 p_process_flag OUT NOCOPY VARCHAR2,
68 p_process_message OUT NOCOPY VARCHAR2,
69 p_discounted_amount IN OUT NOCOPY NUMBER,
70 p_inv_organization_id IN NUMBER DEFAULT NULL,
71 p_settlement_id IN NUMBER DEFAULT NULL,
72 -- Following all parameters are required for GL Accounting if p_balancing_account_id value is not passed to this procedure call
73 p_accntg_required_flag IN VARCHAR2, -- DEFAULT jai_constants.yes File.Sql.35 by Brathod
74 p_accounting_date IN DATE , -- DEFAULT sysdate File.Sql.35 by Brathod
75 p_balancing_orgn_type IN VARCHAR2 DEFAULT NULL,
76 p_balancing_orgn_id IN NUMBER DEFAULT NULL,
77 p_balancing_location_id IN NUMBER DEFAULT NULL,
78 p_balancing_tax_type IN VARCHAR2 DEFAULT NULL,
79 p_balancing_accnt_name IN VARCHAR2 DEFAULT NULL,
80 p_currency_code IN VARCHAR2 , -- DEFAULT jai_constants.func_curr File.Sql.35 by Brathod
81 p_curr_conv_date IN VARCHAR2 DEFAULT NULL,
82 p_curr_conv_type IN VARCHAR2 DEFAULT NULL,
83 p_curr_conv_rate IN VARCHAR2 DEFAULT NULL,
84 p_trx_amount IN NUMBER DEFAULT NULL, -- recovered/liable service tax amount in foreign currency
85 p_attribute_context IN VARCHAR2 DEFAULT NULL,
86 p_attribute1 IN VARCHAR2 DEFAULT NULL,
87 p_attribute2 IN VARCHAR2 DEFAULT NULL,
88 p_attribute3 IN VARCHAR2 DEFAULT NULL,
89 p_attribute4 IN VARCHAR2 DEFAULT NULL,
90 p_attribute5 IN VARCHAR2 DEFAULT NULL,
91 p_service_type_code IN VARCHAR2 DEFAULT NULL/* added by ssawant for bug 5989740 */
92 );
93
94 PROCEDURE post_accounting(
95 p_regime_code IN VARCHAR2,
96 p_tax_type IN VARCHAR2,
97 p_organization_type IN VARCHAR2,
98 p_organization_id IN NUMBER,
99 p_source IN VARCHAR2,
100 p_source_trx_type IN VARCHAR2,
101 p_source_table_name IN VARCHAR2,
102 p_source_document_id IN NUMBER,
103 p_code_combination_id IN NUMBER,
104 -- Transaction Currency Amount
105 p_entered_cr IN NUMBER,
106 p_entered_dr IN NUMBER,
107 -- Functional Currency Amount
108 p_accounted_cr IN NUMBER,
109 p_accounted_dr IN NUMBER,
110 p_accounting_date IN DATE,
111 p_transaction_date IN DATE,
112 p_calling_object IN VARCHAR2,
113 p_repository_name IN VARCHAR2 DEFAULT NULL,
114 p_repository_id IN NUMBER DEFAULT NULL,
115 p_reference_name IN VARCHAR2 DEFAULT NULL,
116 p_reference_id IN NUMBER DEFAULT NULL,
117 p_currency_code IN VARCHAR2 DEFAULT NULL,
118 p_curr_conv_date IN DATE DEFAULT NULL,
119 p_curr_conv_type IN VARCHAR2 DEFAULT NULL,
120 p_curr_conv_rate IN NUMBER DEFAULT NULL
121 );
122
123 PROCEDURE insert_reference(
124 p_reference_id OUT NOCOPY NUMBER,
125 p_organization_id IN NUMBER,
126 p_source IN VARCHAR2,
127 p_invoice_id IN NUMBER,
128 p_line_id IN NUMBER,
129 p_tax_type IN VARCHAR2,
130 p_tax_id IN NUMBER,
131 p_tax_rate IN NUMBER,
132 p_recoverable_ptg IN NUMBER,
133 p_party_type IN VARCHAR2,
134 p_party_id IN NUMBER,
135 p_party_site_id IN NUMBER,
136 p_trx_tax_amount IN NUMBER,
137 p_trx_currency IN VARCHAR2,
138 p_curr_conv_date IN DATE,
139 p_curr_conv_rate IN NUMBER,
140 p_tax_amount IN NUMBER,
141 p_recoverable_amount IN NUMBER,
142 p_recovered_amount IN NUMBER,
143 p_item_line_id IN NUMBER,
144 p_item_id IN NUMBER,
145 p_taxable_basis IN NUMBER,
146 p_parent_reference_id IN NUMBER,
147 p_reversal_flag IN VARCHAR2,
148 p_batch_id IN NUMBER,
149 p_process_flag OUT NOCOPY VARCHAR2,
150 p_process_message OUT NOCOPY VARCHAR2
151 );
152
153 FUNCTION get_account(
154 p_regime_id IN NUMBER,
155 p_organization_type IN VARCHAR2,
156 p_organization_id IN NUMBER,
157 p_location_id IN NUMBER,
158 p_tax_type IN VARCHAR2,
159 p_account_name IN VARCHAR2
160 ) RETURN NUMBER;
161
162 PROCEDURE get_period_name(
163 p_organization_type IN VARCHAR2,
164 p_organization_id IN NUMBER,
165 p_accounting_date IN OUT NOCOPY DATE,
166 p_period_name OUT NOCOPY VARCHAR2,
167 p_sob_id OUT NOCOPY NUMBER
168 );
169
170 PROCEDURE update_reference(
171 p_source IN VARCHAR2,
172 p_reference_id IN NUMBER,
173 p_recovered_amount IN NUMBER,
174 p_discounted_amount IN NUMBER DEFAULT NULL,
175 p_process_flag OUT NOCOPY VARCHAR2,
176 p_process_message OUT NOCOPY VARCHAR2
177 );
178
179 /* following procedure added by Vijay Shankar for Bug#4250236(4245089). VAT Impl. */
180 PROCEDURE insert_vat_repository_entry(
181 pn_repository_id OUT NOCOPY NUMBER,
182 pn_regime_id IN NUMBER,
183 pv_tax_type IN VARCHAR2,
184 pv_organization_type IN VARCHAR2,
185 pn_organization_id IN NUMBER,
189 pv_source_table_name IN VARCHAR2,
186 pn_location_id IN NUMBER,
187 pv_source IN VARCHAR2,
188 pv_source_trx_type IN VARCHAR2,
190 pn_source_id IN NUMBER,
191 pd_transaction_date IN DATE,
192 pv_account_name IN VARCHAR2,
193 pn_charge_account_id IN NUMBER,
194 pn_balancing_account_id IN NUMBER,
195 pn_credit_amount IN OUT NOCOPY NUMBER,
196 pn_debit_amount IN OUT NOCOPY NUMBER,
197 pn_assessable_value IN NUMBER,
198 pn_tax_rate IN NUMBER,
199 pn_reference_id IN NUMBER,
200 pn_batch_id IN NUMBER,
201 pn_inv_organization_id IN NUMBER,
202 pv_invoice_no IN VARCHAR2, /* this holds either generated VAT Invoice Number or Vendor Inovice Number */
203 pd_invoice_date IN DATE, /* this holds VAT Invoice Date or Vendor VAT Inovice Date */
204 pv_called_from IN VARCHAR2,
205 pv_process_flag OUT NOCOPY VARCHAR2,
206 pv_process_message OUT NOCOPY VARCHAR2,
207 pv_attribute_context IN VARCHAR2 DEFAULT NULL,
208 pv_attribute1 IN VARCHAR2 DEFAULT NULL,
209 pv_attribute2 IN VARCHAR2 DEFAULT NULL,
210 pv_attribute3 IN VARCHAR2 DEFAULT NULL,
211 pv_attribute4 IN VARCHAR2 DEFAULT NULL,
212 pv_attribute5 IN VARCHAR2 DEFAULT NULL
213 );
214
215 /* following Procedure added by Vijay Shankar for Bug#4250236(4245089). VAT Impl. */
216 PROCEDURE do_vat_accounting(
217 pn_regime_id IN NUMBER,
218 pn_repository_id IN NUMBER,
219 pv_organization_type IN VARCHAR2,
220 pn_organization_id IN NUMBER,
221 pd_accounting_date IN DATE,
222 pd_transaction_date IN DATE,
223 pn_credit_amount IN NUMBER,
224 pn_debit_amount IN NUMBER,
225 pn_credit_ccid IN NUMBER,
226 pn_debit_ccid IN NUMBER,
227 pv_called_from IN VARCHAR2,
228 pv_process_flag OUT NOCOPY VARCHAR2,
229 pv_process_message OUT NOCOPY VARCHAR2,
230 pv_tax_type IN VARCHAR2 DEFAULT NULL,
231 pv_source IN VARCHAR2 DEFAULT NULL,
232 pv_source_trx_type IN VARCHAR2 DEFAULT NULL,
233 pv_source_table_name IN VARCHAR2 DEFAULT NULL,
234 pn_source_id IN NUMBER DEFAULT NULL,
235 pv_reference_name IN VARCHAR2 DEFAULT NULL,
236 pn_reference_id IN NUMBER DEFAULT NULL
237 );
238
239 /* following function added by Vijay Shankar for Bug#4250236(4245089). VAT Impl. */
240 FUNCTION get_rgm_attribute_value(
241 pv_regime_code IN VARCHAR2,
242 pv_organization_type IN VARCHAR2,
243 pn_organization_id IN NUMBER,
244 pn_location_id IN NUMBER,
245 pv_registration_type IN VARCHAR2,
246 pv_attribute_type_code IN VARCHAR2,
247 pv_attribute_code IN VARCHAR2
248 ) RETURN VARCHAR2;
249
250 END jai_cmn_rgm_recording_pkg;
[Home] [Help]