[Home] [Help]
PACKAGE BODY: APPS.JAI_AP_TDS_POP_RPST_PKG
Source
1 package body jai_ap_tds_pop_rpst_pkg as
2 /* $Header: jai_ap_tds_pop_rpst.plb 120.0.12020000.9 2013/06/04 05:07:10 cholei noship $ */
3 --+============================================================================================================+
4 --| Copyright (c) 2011 Oracle Corporation
5 --| Redwood Shores, CA, USA
6 --| All rights reserved.
7 --+============================================================================================================+
8 --| FILENAME |
9 --| jai_ap_tds_pop_rpst_pkg.plb |
10 --| |
11 --| DESCRIPTION |
12 --| Provid functions and procedures acessing eTDS repository. |
16 --| |
13 --| |
14 --| PROCEDURE LIST |
15 --| PROCEDURE populate_repository |
17 --| HISTORY |
18 --| 05/Dec/2011 Chong Created |
19 --| |
20 --+============================================================================================================*/
21 G_PKG_NAME CONSTANT VARCHAR2(30) := 'JAI_AP_TDS_POP_RPST_PKG';
22 G_MSG_UERROR CONSTANT NUMBER := FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR;
23 G_MSG_ERROR CONSTANT NUMBER := FND_MSG_PUB.G_MSG_LVL_ERROR;
24 G_MSG_SUCCESS CONSTANT NUMBER := FND_MSG_PUB.G_MSG_LVL_SUCCESS;
25 G_MSG_HIGH CONSTANT NUMBER := FND_MSG_PUB.G_MSG_LVL_DEBUG_HIGH;
26 G_MSG_MEDIUM CONSTANT NUMBER := FND_MSG_PUB.G_MSG_LVL_DEBUG_MEDIUM;
27 G_MSG_LOW CONSTANT NUMBER := FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW;
28
29 G_CURRENT_RUNTIME_LEVEL CONSTANT NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
30 G_LEVEL_UNEXPECTED CONSTANT NUMBER := FND_LOG.LEVEL_UNEXPECTED;
31 G_LEVEL_ERROR CONSTANT NUMBER := FND_LOG.LEVEL_ERROR;
32 G_LEVEL_EXCEPTION CONSTANT NUMBER := FND_LOG.LEVEL_EXCEPTION;
33 G_LEVEL_EVENT CONSTANT NUMBER := FND_LOG.LEVEL_EVENT;
34 G_LEVEL_PROCEDURE CONSTANT NUMBER := FND_LOG.LEVEL_PROCEDURE;
35 G_LEVEL_STATEMENT CONSTANT NUMBER := FND_LOG.LEVEL_STATEMENT;
36 G_MODULE_NAME CONSTANT VARCHAR2(40) := 'JAI.PLSQL.JAI_AP_TDS_POP_RPST_PKG.';
37 G_INVOICE_VALIDATE CONSTANT VARCHAR2(40) := 'INVOICE VALIDATE';
38 G_PREPAYMENT_APPLICATION CONSTANT VARCHAR2(40) := 'PREPAYMENT APPLICATION';
39 G_PREPAYMENT_UNAPPLICATION CONSTANT VARCHAR2(40) := 'PREPAYMENT UNAPPLICATION';
40 G_THRESHOLD_TRANSITION CONSTANT VARCHAR2(40) := 'THRESHOLD TRANSITION';
41 G_THRESHOLD_ROLLBACK CONSTANT VARCHAR2(40) := 'THRESHOLD ROLLBACK';
42 G_SURCHARGE_CALCULATE CONSTANT VARCHAR2(40) := 'SURCHARGE_CALCULATE';
43 G_SURCHARGE_ROLLBACK CONSTANT VARCHAR2(40) := 'SURCHARGE_ROLLBACK';
44 G_JAI_INDIA_TAX_REGIMES CONSTANT VARCHAR2(40) := 'JAI_INDIA_TAX_REGIMES';
45 G_TDS CONSTANT VARCHAR2(3) := 'TDS';
46 G_TDS_EDUCATION_CESS CONSTANT VARCHAR2(20) := 'TDS_EDUCATION_CESS';
47 G_TDS_SH_EDU_CESS CONSTANT VARCHAR2(20) := 'TDS_SH_EDU_CESS';
48 G_TDS_SURCHARGE CONSTANT VARCHAR2(20) := 'TDS_SURCHARGE';
49 G_TDS_SECTION CONSTANT VARCHAR2(20) := 'TDS_SECTION';
50 G_WCT_SECTION CONSTANT VARCHAR2(20) := 'WCT_SECTION';
51 G_ESSI_SECTION CONSTANT VARCHAR2(20) := 'ESSI_SECTION';
52 G_Y CONSTANT VARCHAR2(1) := 'Y';
53 G_N CONSTANT VARCHAR2(1) := 'N';
54
55 /*---------------------------------------------------------------------
56 --Function to set TDS invoice type
57 ---------------------------------------------------------------------*/
58 FUNCTION get_tds_invoice_type(pv_tds_event in varchar2) RETURN varchar2
59 IS
60
61 ln_tds_invoice_type varchar2(40);
62 l_api_name CONSTANT VARCHAR2(50) := 'get_tds_invoice_type()';
63 BEGIN
64 IF pv_tds_event = G_INVOICE_VALIDATE THEN
65 ln_tds_invoice_type := 'NORMAL';
66 ELSIF pv_tds_event = G_PREPAYMENT_APPLICATION THEN
67 ln_tds_invoice_type := 'RTN';
68 ELSIF pv_tds_event = G_PREPAYMENT_UNAPPLICATION THEN
69 ln_tds_invoice_type := 'REV-RTN';
70 ELSIF SUBSTR(pv_tds_event, 0, 20) = G_THRESHOLD_TRANSITION THEN
71 ln_tds_invoice_type := 'TRANSITION';
72 ELSIF SUBSTR(pv_tds_event, 0, 18) = G_THRESHOLD_ROLLBACK THEN
73 ln_tds_invoice_type := 'ROLLBACK';
74 ELSIF pv_tds_event = G_SURCHARGE_CALCULATE THEN
75 ln_tds_invoice_type := 'SURCHARGE';
76 ELSIF pv_tds_event = G_SURCHARGE_ROLLBACK THEN
77 ln_tds_invoice_type := 'SURCHARGE ROLLBACK';
78 END IF;
79
80 jai_cmn_utils_pkg.WRITE_FND_LOG(G_LEVEL_PROCEDURE, G_MODULE_NAME || l_api_name , 'Get Inovice Type: '|| ln_tds_invoice_type );
81 return ln_tds_invoice_type;
82 END get_tds_invoice_type;
83
84 FUNCTION map_tds_tax_to_ctg(p_tax_id IN NUMBER)RETURN NUMBER
85 IS
86 --
87 CURSOR c_get_category(pn_tax_id NUMBER) is
88 SELECT jctc.tax_category_id
89 FROM jai_cmn_tax_ctgs_all jctc
90 WHERE jctc.tax_category_desc like '% New TDS migrated from: ' || p_tax_id || '%'
91 ;
92 ln_tax_categeory_id NUMBER;
93 l_api_name CONSTANT VARCHAR2(50) := 'map_tds_tax_to_ctg()';
94 BEGIN
95
96 IF p_tax_id IS NULL THEN
97 ln_tax_categeory_id := NULL;
98 ELSE
99 OPEN c_get_category(p_tax_id);
100 FETCH c_get_category into ln_tax_categeory_id;
101 CLOSE c_get_category;
102 END IF;
103
104 jai_cmn_utils_pkg.WRITE_FND_LOG(G_LEVEL_PROCEDURE, G_MODULE_NAME || l_api_name , 'Mapping tax id: '|| p_tax_id ||' to category: ' || ln_tax_categeory_id);
105 RETURN ln_tax_categeory_id;
106
107 END map_tds_tax_to_ctg;
108
109
110 /*-------------------------------------------------------------------------------------------------------------------------------+
111 | Created By : Chong |
115 | Type : PROCEDURE |
112 | Creation Date : 05/MAR/2013 |
113 | Bug Number/ER Name : eTDS |
114 | SubProgram Name : populate_repository |
116 | Purpose : Procedure to populate eTDS repository table when. |
117 | Validation |
118 | Threshold Transition |
119 | Threshold Rollback |
120 | Preypayment Application/Unapplication |
121 | TDD Reference : |
122 | Assumptions : |
123 | Called From : |
124 |--------------------------------------------------------------------------------------------------------------------------------|
125 | parameters IN/OUT Type Required Description and Purpose |
126 | ------------ -------- ------ ---------- ------------------------- |
127 | pn_source_invoice_id IN NUMBER No source invoice id |
128 | pn_invoice_id IN NUMBER No TDS invoice id |
129 | pv_event IN VARCHAR2 yes TDS event |
130 | pv_section_type IN VARCHAR2 yes Section type |
131 | pv_section_code IN VARCHAR2 yes TDS Section Code |
132 | pn_threshold_grp_id IN NUMBER yes TDS threshold grop id |
133 | pn_threshold_hdr_id IN NUMBER No TDS threshold header id |
134 | pn_threshold_type_id IN NUMBER No TDS threshold type id |
135 | pn_threshold_slab_id IN NUMBER No TDS threshold slab id |
136 | pn_invoice_distribution_id IN NUMBER No invoice distribution id |
137 | pn_prepay_distribution_id IN NUMBER No prepayment invoice distribution id |
138 | Pn_tax_category_id IN NUMBER No tax category id |
139 ---------------------------------------------------------------------------------------------------------------------------------*/
140 PROCEDURE populate_repository(pn_source_invoice_id in number default null
141 ,pn_invoice_id in number default null
142 ,pv_event in varchar2
143 ,pv_section_type in varchar2
144 ,pv_section_code in varchar2
145 ,pn_threshold_grp_id in number
146 ,pn_threshold_hdr_id in number default null
147 ,pn_threshold_type_id in number default null
148 ,pn_threshold_slab_id in number default null
149 ,pn_invoice_distribution_id in number default null --new Created prepay distribution line
150 ,pn_prepay_distribution_id in number default null --Prepayment invoice distribution line
151 ,Pn_tax_category_id in number default null
152 ) IS
153
154 --Get transaction information by TDS invoice_id
155 CURSOR c_get_trx_tds_info(p_tds_invoice_id NUMBER) IS
156 SELECT jattt.threshold_trx_id
157 ,jattt.invoice_id
158 ,jattt.threshold_grp_id
159 ,jattt.threshold_hdr_id
160 ,jattt.invoice_to_tds_authority_num
161 ,tds_inv.gl_date
162 ,jattt.tds_event
163 ,jattt.invoice_to_tds_authority_amt
164 ,jattt.invoice_to_vendor_amt
165 ,jattt.taxable_amount
166 ,jattt.tax_category_id
167 ,jattt.tds_authority_vendor_id
168 ,jattt.tds_authority_vendor_site_id
169 --,NVL(REGEXP_SUBSTR(jattt.invoice_to_tds_authority_num, '(WCT|TDS|ESSI)-\w+-\d+$',1,1,'i',1), G_TDS) withhold_tax_type
170 ,jctca.effective_tds_rate
171 FROM jai_ap_tds_thhold_trxs jattt
172 ,ap_invoices_all tds_inv
173 ,jai_cmn_tax_ctgs_all jctca
174 WHERE jattt.invoice_to_tds_authority_id = tds_inv.invoice_id
175 AND jattt.tax_category_id = jctca.tax_category_id(+)
176 AND jattt.invoice_to_tds_authority_id = p_tds_invoice_id
177 ;
178
179 --Get source invoice information
183 ,inv.invoice_type_lookup_code
180 CURSOR c_get_inv_info(p_invoice_id NUMBER) IS
181 SELECT inv.invoice_id
182 ,inv.invoice_amount
184 ,inv.vendor_id
185 ,inv.vendor_site_id
186 ,inv.invoice_date
187 ,inv.gl_date
188 ,inv.org_id
189 ,inv.legal_entity_id
190 FROM ap_invoices_all inv
191 WHERE inv.invoice_id = p_invoice_id
192 ;
193 --Get tax rate of ESSI/WCT tax in given tax category
194 CURSOR c_get_tds_essi_rate(p_tax_category_id NUMBER
195 ,p_section_type varchar2
196 ) IS
197 SELECT jcta.tax_rate
198 ,jcta.tax_id
199 FROM jai_cmn_tax_ctg_lines jctcl
200 ,jai_cmn_taxes_all jcta
201 WHERE jctcl.tax_id = jcta.tax_id
202 AND jctcl.tax_category_id = p_tax_category_id
203 AND jcta.section_type = p_section_type
204 ;
205 --Get regime definition information
206 CURSOR c_get_tax_regime_info(pv_tax_regime varchar2
207 ) IS
208 SELECT jl.meaning MEANING
209 ,jl.lookup_code LOOKUP_CODE
210 ,jrd.regime_id REGIME_ID
211 FROM ja_lookups jl
212 ,jai_rgm_definitions jrd
213 WHERE jl.lookup_code = jrd.regime_code
214 AND jl.lookup_type = G_JAI_INDIA_TAX_REGIMES
215 AND jl.lookup_code = pv_tax_regime
216 ;
217 --Get TDS tan number from regime
218 CURSOR c_get_tds_org_tan(p_org_id number
219 ) IS
220 SELECT org_tan_num
221 FROM jai_ap_tds_org_tan_v
222 WHERE organization_id = p_org_id
223 ;
224 --Derive apply from invoice info
225 CURSOR c_get_apply_fromto_inv (pn_distribution_id NUMBER
226 )IS
227 SELECT aia.invoice_id
228 ,aia.invoice_type_lookup_code invoice_type
229 ,aia.invoice_amount
230 ,aia.vendor_id
231 ,aia.vendor_site_id
232 ,aia.invoice_date
233 ,aia.gl_date
234 ,ABS(aida.amount) apply_amount
235 FROM ap_invoices_all aia
236 ,ap_invoice_distributions_all aida
237 WHERE aia.invoice_id = aida.invoice_id
238 AND aida.invoice_distribution_id = pn_distribution_id
239 ;
240
241 CURSOR c_get_tds_repository_id IS
242 SELECT JAI_AP_TDS_REPOSITORY_S.NEXTVAL
243 FROM DUAL;
244
245 CURSOR c_get_apply_prepayments_info( pn_threshold_trx_id NUMBER
246 ) IS
247 SELECT jatp.tds_prepayment_id
248 ,jatp.invoice_id
249 ,jatp.invoice_distribution_id_prepay
250 ,jatp.invoice_distribution_id
251 ,application_amount
252 FROM jai_ap_tds_prepayments jatp
253 WHERE NVL(NVL(jatp.tds_threshold_trx_id_apply, jatp.wct_threshold_trx_id_apply)
254 ,jatp.essi_threshold_trx_id_apply) = pn_threshold_trx_id
255 ;
256
257 CURSOR c_get_unapply_prepayments_info( pn_threshold_trx_id NUMBER
258 ) IS
259 SELECT jatp.tds_prepayment_id
260 ,jatp.invoice_id
261 ,jatp.invoice_distribution_id_prepay
262 ,jatp.invoice_distribution_id
263 ,application_amount
264 FROM jai_ap_tds_prepayments jatp
265 WHERE NVL(NVL(jatp.tds_threshold_trx_id_unapply, jatp.wct_threshold_trx_id_unapply)
266 ,jatp.essi_threshold_trx_id_unapply) = pn_threshold_trx_id
267 ;
268
269 CURSOR c_get_prepay_dist_id( pn_inv_dist_id NUMBER
270 ) IS
271 SELECT aida.prepay_distribution_id
272 FROM ap_invoice_distributions_all aida
273 WHERE aida.invoice_distribution_id = pn_inv_dist_id
274 ;
275
276 r_c_get_trx_tds_info c_get_trx_tds_info%rowtype;
277 r_c_get_inv_info c_get_inv_info%rowtype;
278 r_c_get_tds_essi_rate c_get_tds_essi_rate%rowtype;
279 r_jai_ap_tds_repository jai_ap_tds_repository%rowtype;
280 r_c_get_tax_regime_info c_get_tax_regime_info%rowtype;
281 r_c_get_tds_org_tan c_get_tds_org_tan%rowtype;
282 r_c_get_apply_fromto_inv c_get_apply_fromto_inv%rowtype;
283 r_get_apply_prepayments_info c_get_apply_prepayments_info%rowtype;
284 r_get_unapply_prepayments_info c_get_unapply_prepayments_info%rowtype;
285
286 ln_inv_distribution_id NUMBER;
287 ln_inv_prepy_distribution_id NUMBER;
288 l_api_name CONSTANT VARCHAR2(50) := 'populate_repository()';
289 BEGIN
290 jai_cmn_utils_pkg.WRITE_FND_LOG(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_api_name||'.BEGIN', G_PKG_NAME || ': '||l_api_name||'()+');
291 jai_cmn_utils_pkg.WRITE_FND_LOG(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_api_name||'.Parameters', 'pn_source_invoice_id: '||pn_source_invoice_id);
292 jai_cmn_utils_pkg.WRITE_FND_LOG(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_api_name||'.Parameters', 'pn_invoice_id: '||pn_invoice_id);
293 jai_cmn_utils_pkg.WRITE_FND_LOG(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_api_name||'.Parameters', 'pv_event: '||pv_event);
294 jai_cmn_utils_pkg.WRITE_FND_LOG(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_api_name||'.Parameters', 'pv_section_type: '||pv_section_type);
295 jai_cmn_utils_pkg.WRITE_FND_LOG(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_api_name||'.Parameters', 'pv_section_code: '||pv_section_code);
296 jai_cmn_utils_pkg.WRITE_FND_LOG(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_api_name||'.Parameters', 'pn_threshold_grp_id: '||pn_threshold_grp_id);
297 jai_cmn_utils_pkg.WRITE_FND_LOG(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_api_name||'.Parameters', 'pn_threshold_hdr_id: '||pn_threshold_hdr_id);
298 jai_cmn_utils_pkg.WRITE_FND_LOG(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_api_name||'.Parameters', 'pn_threshold_type_id: '||pn_threshold_type_id);
302 jai_cmn_utils_pkg.WRITE_FND_LOG(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_api_name||'.Parameters', 'Pn_tax_category_id: ' || Pn_tax_category_id);
299 jai_cmn_utils_pkg.WRITE_FND_LOG(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_api_name||'.Parameters', 'pn_threshold_slab_id: '|| pn_threshold_slab_id);
300 jai_cmn_utils_pkg.WRITE_FND_LOG(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_api_name||'.Parameters', 'pn_invoice_distribution_id: ' || pn_invoice_distribution_id);
301 jai_cmn_utils_pkg.WRITE_FND_LOG(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_api_name||'.Parameters', 'pn_prepay_distribution_id: ' || pn_prepay_distribution_id);
303
304
305 /*Surcharge calculate and rollback will split amount, no need populating repository
306 Already processed in jai_ap_dtc_generation_pkg.threshold_transiton*/
307 IF pv_event IN (G_SURCHARGE_CALCULATE, G_SURCHARGE_ROLLBACK) THEN
308 RETURN;
309 END IF;
310
311 r_jai_ap_tds_repository.EVENT := pv_event;
312 r_jai_ap_tds_repository.SECTION_TYPE := pv_section_type;
313 r_jai_ap_tds_repository.SECTION_CODE := pv_section_code;
314 r_jai_ap_tds_repository.threshold_grp_id := pn_threshold_grp_id;
315 r_jai_ap_tds_repository.threshold_hdr_id := pn_threshold_hdr_id;
316 r_jai_ap_tds_repository.THRESHOLD_TYPE_ID := pn_threshold_type_id;
317 r_jai_ap_tds_repository.THRESHOLD_SLAB_ID := pn_threshold_slab_id;
318
319 /*Set TDS invoice information*/
320 r_jai_ap_tds_repository.invoice_id := pn_invoice_id;
321 r_jai_ap_tds_repository.REQUIRED_FOR_ETDS := G_N; --Added for bug#16889298
322
323 IF pn_invoice_id IS NOT NULL AND pn_invoice_id NOT IN (-9999, -9997) THEN
324 jai_cmn_utils_pkg.WRITE_FND_LOG(G_LEVEL_STATEMENT, G_MODULE_NAME||l_api_name, 'Set info TDS invoice: ' || pn_invoice_id);
325
326 OPEN c_get_trx_tds_info(pn_invoice_id);
327 FETCH c_get_trx_tds_info into r_c_get_trx_tds_info;
328 CLOSE c_get_trx_tds_info;
329
330 r_jai_ap_tds_repository.Invoice_number := r_c_get_trx_tds_info.invoice_to_tds_authority_num;
331 r_jai_ap_tds_repository.TDS_ACCOUNTING_DATE := r_c_get_trx_tds_info.gl_date;
332 --should in NORMAL, RTN, REV-RTN, TRANSITION , ROLLBACK, SURCHARGE, SURCHARGE ROLLBACK.
333 r_jai_ap_tds_repository.TDS_INVOICE_TYPE := get_tds_invoice_type(pv_event);
334 r_jai_ap_tds_repository.threshold_hdr_id := r_c_get_trx_tds_info.threshold_hdr_id;
335 r_jai_ap_tds_repository.TDS_INV_TAX_AMOUNT := r_c_get_trx_tds_info.invoice_to_tds_authority_amt;
336 r_jai_ap_tds_repository.SUPPLIER_CM_AMOUNT := r_c_get_trx_tds_info.invoice_to_vendor_amt;
337 r_jai_ap_tds_repository.BASE_TAX_AMOUNT := r_c_get_trx_tds_info.taxable_amount;
338 r_jai_ap_tds_repository.tax_category_id := r_c_get_trx_tds_info.tax_category_id;
339 r_jai_ap_tds_repository.TDS_AUTHORITY_ID := r_c_get_trx_tds_info.tds_authority_vendor_id;
340 r_jai_ap_tds_repository.TDS_AUTHORITY_SITE_ID := r_c_get_trx_tds_info.tds_authority_vendor_site_id;
341
342 jai_cmn_utils_pkg.WRITE_FND_LOG(G_LEVEL_STATEMENT, G_MODULE_NAME||l_api_name, 'TDS info: Invoice_number[' || r_jai_ap_tds_repository.Invoice_number || '],');
343 jai_cmn_utils_pkg.WRITE_FND_LOG(G_LEVEL_STATEMENT, G_MODULE_NAME||l_api_name, 'TDS info: TDS_ACCOUNTING_DATE[' || r_jai_ap_tds_repository.TDS_ACCOUNTING_DATE || '],');
344 jai_cmn_utils_pkg.WRITE_FND_LOG(G_LEVEL_STATEMENT, G_MODULE_NAME||l_api_name, 'TDS info: TDS_INVOICE_TYPE[' || r_jai_ap_tds_repository.TDS_INVOICE_TYPE || '],');
345 jai_cmn_utils_pkg.WRITE_FND_LOG(G_LEVEL_STATEMENT, G_MODULE_NAME||l_api_name, 'TDS info: threshold_hdr_id[' || r_jai_ap_tds_repository.threshold_hdr_id || '],');
346 jai_cmn_utils_pkg.WRITE_FND_LOG(G_LEVEL_STATEMENT, G_MODULE_NAME||l_api_name, 'TDS info: THRESHOLD_TYPE_ID[' || r_jai_ap_tds_repository.THRESHOLD_TYPE_ID || '],');
347 jai_cmn_utils_pkg.WRITE_FND_LOG(G_LEVEL_STATEMENT, G_MODULE_NAME||l_api_name, 'TDS info: THRESHOLD_SLAB_ID[' || r_jai_ap_tds_repository.THRESHOLD_SLAB_ID || '],');
348 jai_cmn_utils_pkg.WRITE_FND_LOG(G_LEVEL_STATEMENT, G_MODULE_NAME||l_api_name, 'TDS info: TDS_INV_TAX_AMOUNT[' || r_jai_ap_tds_repository.TDS_INV_TAX_AMOUNT || '],');
349 jai_cmn_utils_pkg.WRITE_FND_LOG(G_LEVEL_STATEMENT, G_MODULE_NAME||l_api_name, 'TDS info: SUPPLIER_CM_AMOUNT[' || r_jai_ap_tds_repository.SUPPLIER_CM_AMOUNT || '],');
350 jai_cmn_utils_pkg.WRITE_FND_LOG(G_LEVEL_STATEMENT, G_MODULE_NAME||l_api_name, 'TDS info: BASE_TAX_AMOUNT[' || r_jai_ap_tds_repository.BASE_TAX_AMOUNT || '],');
351 jai_cmn_utils_pkg.WRITE_FND_LOG(G_LEVEL_STATEMENT, G_MODULE_NAME||l_api_name, 'TDS info: tax_category_id[' || r_jai_ap_tds_repository.tax_category_id || '],');
352 jai_cmn_utils_pkg.WRITE_FND_LOG(G_LEVEL_STATEMENT, G_MODULE_NAME||l_api_name, 'TDS info: TDS_AUTHORITY_ID[' || r_jai_ap_tds_repository.TDS_AUTHORITY_ID || '],');
353 jai_cmn_utils_pkg.WRITE_FND_LOG(G_LEVEL_STATEMENT, G_MODULE_NAME||l_api_name, 'TDS info: TDS_AUTHORITY_SITE_ID[' || r_jai_ap_tds_repository.TDS_AUTHORITY_SITE_ID || '],');
354
355 IF pv_section_type <> G_TDS_SECTION THEN
356 --when section type in WCT or ESSI, get effective rate by WCT/ESSI from tax code
357 --Assume that in given TDS tax category, only one tax in WCT/ESSI type and its precedence always be 0.
358 OPEN c_get_tds_essi_rate(p_tax_category_id => r_c_get_trx_tds_info.tax_category_id
359 ,p_section_type => pv_section_type
360 );
361 FETCH c_get_tds_essi_rate into r_c_get_tds_essi_rate;
362 CLOSE c_get_tds_essi_rate;
363
364 r_jai_ap_tds_repository.TAX_RATE := r_c_get_tds_essi_rate.tax_rate;
365 r_jai_ap_tds_repository.TAX_ID := r_c_get_tds_essi_rate.tax_id;
366 ELSE
367 --When TDS transaction, just give the tds_effective_rate in trx table.
368 r_jai_ap_tds_repository.TAX_RATE := NVL(r_c_get_trx_tds_info.effective_tds_rate,0);
369 END iF;
370
371 --TDS invoice created
372 r_jai_ap_tds_repository.PRIOR_THRESHOLD := G_N;
373 ELSIF pn_invoice_id IS NULL THEN
374 --No TDS invoice created, means prior threshold transiton
375 r_jai_ap_tds_repository.PRIOR_THRESHOLD := G_Y;
379 r_jai_ap_tds_repository.REQUIRED_FOR_ETDS := G_Y; --Added for bug#16889298
376 ELSIF pn_invoice_id IN (-9999, -9997) THEN
377 -- for zero rate TDS, no TDS created
378 r_jai_ap_tds_repository.PRIOR_THRESHOLD := G_N;
380 END IF;
381 --r_jai_ap_tds_repository.REQUIRED_FOR_ETDS := G_N; --Commented out for bug#16889298
382
383 /*Threshold transition or threshold rollback can'tderive source invoice id*/
384 --ISSUE: for threshold rollback and threshold transition do we need split TDS invoice into each source invoice?
385 --Confirmed. Just use the trigger invoice id for threshold transition and rollback
386 IF pn_source_invoice_id IS NOT NULL THEN
387 jai_cmn_utils_pkg.WRITE_FND_LOG(G_LEVEL_STATEMENT, G_MODULE_NAME||l_api_name, 'Set Source invoice info: ' || pn_source_invoice_id);
388
389 OPEN c_get_inv_info(pn_source_invoice_id);
390 FETCH c_get_inv_info into r_c_get_inv_info;
391 CLOSE c_get_inv_info;
392
393 r_jai_ap_tds_repository.SOURCE_INVOICE_ID := r_c_get_inv_info.invoice_id;
394 r_jai_ap_tds_repository.SOURCE_INVOICE_TYPE := r_c_get_inv_info.invoice_type_lookup_code;
395 r_jai_ap_tds_repository.SOURCE_INVOICE_AMT := r_c_get_inv_info.invoice_amount;
396 r_jai_ap_tds_repository.SOURCE_ID := r_c_get_inv_info.vendor_id;
397 r_jai_ap_tds_repository.SOURCE_SITE_ID := r_c_get_inv_info.vendor_site_id;
398 --r_jai_ap_tds_repository.INVOICE_CHECK_ID := NULL;
399 r_jai_ap_tds_repository.SOURCE_INVOICE_DATE := r_c_get_inv_info.invoice_date;
400 r_jai_ap_tds_repository.ACCOUNTING_DATE := r_c_get_inv_info.gl_date;
401 r_jai_ap_tds_repository.ORG_ID := r_c_get_inv_info.org_id;
402 r_jai_ap_tds_repository.LEGAL_ENTITY_ID := r_c_get_inv_info.legal_entity_id;
403
404 /* @TODO
405 If BASE_TAX_AMOUNT is null then
406 --no TDS invoice created, derive base tax amount from current source invoice
407 -- amount should derive from jai_ap_tds_inv_taxes, as we need change amount to INR currency
408 -- by pn_source_invoice_id, tds_section_code, threshold_grp_id
409 BASE_TAX_AMOUNT <--- source invoice amount.
410 End if;*/
411 IF pn_invoice_id IN (-9999, -9997) THEN
412 -- for zero rate TDS, no TDS created
413 r_jai_ap_tds_repository.BASE_TAX_AMOUNT := r_c_get_inv_info.invoice_amount;
414 END IF;
415 END IF; --pn_source_invoice_id IS NOT NULL
416
417 --Derive tds regime ID and code from regime setup
418 OPEN c_get_tax_regime_info(G_TDS);
419 FETCH c_get_tax_regime_info INTO r_c_get_tax_regime_info;
420 CLOSE c_get_tax_regime_info;
421 r_jai_ap_tds_repository.REGIME_ID := r_c_get_tax_regime_info.regime_id;
422 r_jai_ap_tds_repository.REGIME_CODE := r_c_get_tax_regime_info.lookup_code;
423
424 --Derive org_tan number from regime registration by below cursor
425 OPEN c_get_tds_org_tan(r_jai_ap_tds_repository.ORG_ID);
426 FETCH c_get_tds_org_tan INTO r_c_get_tds_org_tan;
427 CLOSE c_get_tds_org_tan;
428 r_jai_ap_tds_repository.org_tan_number := r_c_get_tds_org_tan.org_tan_num;
429
430
431 --Process application/unapplication concerning info
432 IF pv_event IN (G_PREPAYMENT_APPLICATION, G_PREPAYMENT_UNAPPLICATION) THEN
433 jai_cmn_utils_pkg.WRITE_FND_LOG(G_LEVEL_STATEMENT, G_MODULE_NAME||l_api_name,
434 'Set prepayment application info: ' || pv_event || ' : ' || pn_prepay_distribution_id);
435
436 --IF pn_invoice_id IS NOT NULL AND pn_invoice_id NOT IN (-9999, -9997) THEN
437 IF r_c_get_trx_tds_info.threshold_trx_id IS NOT NULL THEN
438 --RTN TDS invoice created.
439 --As in this situation code is invoked while populdate TDS invoice, prepay_distribtuion and inv distribtuion not populdated.
440 --Need derive apply from/to info from jai_ap_tds_prepayments table.
441 IF pv_event = G_PREPAYMENT_APPLICATION THEN
442 --Application prepayment
443 OPEN c_get_apply_prepayments_info(r_c_get_trx_tds_info.threshold_trx_id);
444 FETCH c_get_apply_prepayments_info INTO r_get_apply_prepayments_info;
445 CLOSE c_get_apply_prepayments_info;
446
447 --get prepayment invoice_distribution_id -> apply from dist id
448 OPEN c_get_prepay_dist_id(r_get_apply_prepayments_info.invoice_distribution_id_prepay);
449 FETCH c_get_prepay_dist_id INTO ln_inv_prepy_distribution_id;
450 CLOSE c_get_prepay_dist_id;
451 --apply target line -> apply to dist id
452 ln_inv_distribution_id := r_get_apply_prepayments_info.invoice_distribution_id;
453 ELSE
454 --Unapplication prepayment
455 OPEN c_get_unapply_prepayments_info(r_c_get_trx_tds_info.threshold_trx_id);
456 FETCH c_get_unapply_prepayments_info INTO r_get_unapply_prepayments_info;
457 CLOSE c_get_unapply_prepayments_info;
458
459 --get prepayment invoice_distribution_id -> apply from dist id
460 OPEN c_get_prepay_dist_id(r_get_unapply_prepayments_info.invoice_distribution_id_prepay);
461 FETCH c_get_prepay_dist_id INTO ln_inv_prepy_distribution_id;
462 CLOSE c_get_prepay_dist_id;
463 --apply target line -> apply to dist id
464 ln_inv_distribution_id := r_get_unapply_prepayments_info.invoice_distribution_id;
465 END IF;
466 END IF;
467
468 ln_inv_prepy_distribution_id := NVL(ln_inv_prepy_distribution_id, pn_prepay_distribution_id);
469 ln_inv_distribution_id := NVL(ln_inv_distribution_id, pn_invoice_distribution_id);
470 jai_cmn_utils_pkg.WRITE_FND_LOG(G_LEVEL_STATEMENT, G_MODULE_NAME||l_api_name, 'ln_inv_prepy_distribution_id:' || ln_inv_prepy_distribution_id);
474 OPEN c_get_apply_fromto_inv(ln_inv_prepy_distribution_id);
471 jai_cmn_utils_pkg.WRITE_FND_LOG(G_LEVEL_STATEMENT, G_MODULE_NAME||l_api_name, 'ln_inv_distribution_id:' || ln_inv_distribution_id);
472
473 --derive apply from prepayment invoice info
475 FETCH c_get_apply_fromto_inv INTO r_c_get_apply_fromto_inv;
476 CLOSE c_get_apply_fromto_inv;
477 r_jai_ap_tds_repository.APPLIED_FROM_INVOICE_ID := r_c_get_apply_fromto_inv.invoice_id;
478 r_jai_ap_tds_repository.APPLIED_FROM_INVOICE_TYPE := r_c_get_apply_fromto_inv.invoice_type;
479 r_jai_ap_tds_repository.APPLIED_FROM_INVOICE_AMT := r_c_get_apply_fromto_inv.invoice_amount;
480 r_jai_ap_tds_repository.APPLIED_FROM_SUPPLIER_ID := r_c_get_apply_fromto_inv.vendor_id;
481 r_jai_ap_tds_repository.APPLIED_FROM_SUPPLIER_SITE_ID := r_c_get_apply_fromto_inv.vendor_site_id;
482 r_jai_ap_tds_repository.APPLIED_FROM_INVOICE_DATE := r_c_get_apply_fromto_inv.invoice_date;
483 r_jai_ap_tds_repository.APPLIED_FROM_ACCTG_DATE := r_c_get_apply_fromto_inv.gl_date;
484 --@TODO currently, no need ot populate this field.
485 --r_jai_ap_tds_repository.FROM_SLAB_ID :=
486
487 --derive apply to prepayment invoice info
488 OPEN c_get_apply_fromto_inv(ln_inv_distribution_id);
489 FETCH c_get_apply_fromto_inv INTO r_c_get_apply_fromto_inv;
490 CLOSE c_get_apply_fromto_inv;
491 r_jai_ap_tds_repository.APPLIED_TO_INVOICE_ID := r_c_get_apply_fromto_inv.invoice_id;
492 r_jai_ap_tds_repository.APPLIED_TO_INVOICE_TYPE := r_c_get_apply_fromto_inv.invoice_type;
493 r_jai_ap_tds_repository.APPLIED_TO_INVOICE_AMT := r_c_get_apply_fromto_inv.invoice_amount;
494 r_jai_ap_tds_repository.APPLIED_TO_SUPPLIER_ID := r_c_get_apply_fromto_inv.vendor_id;
495 r_jai_ap_tds_repository.APPLIED_TO_SUPPLIER_SITE_ID := r_c_get_apply_fromto_inv.vendor_site_id;
496 r_jai_ap_tds_repository.APPLIED_TO_INVOICE_DATE := r_c_get_apply_fromto_inv.invoice_date;
497 r_jai_ap_tds_repository.APPLIED_TO_ACCTG_DATE := r_c_get_apply_fromto_inv.gl_date;
498 --@TODO currently, no need ot populate this field.
499 --r_jai_ap_tds_repository.TO_SLAB_ID :=
500 IF pv_event = G_PREPAYMENT_APPLICATION THEN
501 r_jai_ap_tds_repository.APPLIED_AMOUNT := r_c_get_apply_fromto_inv.apply_amount;
502 ELSIF pv_event = G_PREPAYMENT_UNAPPLICATION THEN
503 r_jai_ap_tds_repository.UNAPPLIED_AMOUNT := r_c_get_apply_fromto_inv.apply_amount;
504 END IF;
505 END IF;
506
507 r_jai_ap_tds_repository.MIGRATED := G_N;
508 r_jai_ap_tds_repository.CREATION_DATE := sysdate;
509 r_jai_ap_tds_repository.CREATED_BY := fnd_global.user_id;
510 r_jai_ap_tds_repository.LAST_UPDATE_DATE := sysdate;
511 r_jai_ap_tds_repository.LAST_UPDATE_LOGIN := fnd_global.login_id;
512 r_jai_ap_tds_repository.LAST_UPDATED_BY := fnd_global.user_id;
513
514 --get repsitory id from sequence
515 OPEN c_get_tds_repository_id;
516 FETCH c_get_tds_repository_id INTO r_jai_ap_tds_repository.TDS_REPOSITORY_ID;
517 CLOSE c_get_tds_repository_id;
518
519 --Call procedure to insert one line into repository table.
520 insert_jai_tds_repository(r_jai_ap_tds_repository);
521
522 jai_cmn_utils_pkg.WRITE_FND_LOG(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_api_name||'.END', G_PKG_NAME || ': '||l_api_name||'()-');
523 EXCEPTION
524 WHEN OTHERS THEN
525 jai_cmn_utils_pkg.WRITE_FND_LOG(G_LEVEL_PROCEDURE
526 ,G_MODULE_NAME||l_api_name||'. Other_Exception'
527 ,SQLCODE || ':' || SQLERRM);
528 RAISE;
529 END populate_repository;
530
531
532 /*---------------------------------------------------------------------
533 --Procedure to insert one line into jai_ap_tds_repository table.
534 -----------------------------------------------------------------------*/
535 PROCEDURE process_cancellation(pn_source_invoice_id IN NUMBER)
536 IS
537 l_api_name CONSTANT VARCHAR2(50) := 'process_cancellation()';
538 BEGIN
539 jai_cmn_utils_pkg.WRITE_FND_LOG(G_LEVEL_PROCEDURE, G_MODULE_NAME || l_api_name ||'.BEGIN', G_PKG_NAME || ': '|| l_api_name || '()+');
540
541 If pn_source_invoice_id is not null then
542 Update jai_ap_tds_repository jatr
543 Set jatr.source_invoice_deleted = G_Y
544 ,jatr.invoice_deleted = G_Y
545 Where source_invoice_id = pn_source_invoice_id;
546 End if;
547 jai_cmn_utils_pkg.WRITE_FND_LOG(G_LEVEL_PROCEDURE, G_MODULE_NAME || l_api_name ||'.END', G_PKG_NAME || ': '|| l_api_name || '()-');
548 EXCEPTION
549 WHEN OTHERS THEN
550 jai_cmn_utils_pkg.WRITE_FND_LOG(G_LEVEL_PROCEDURE
551 ,G_MODULE_NAME||l_api_name||'. Other_Exception'
552 ,SQLCODE || ':' || SQLERRM);
553 RAISE;
554 END process_cancellation;
555
556
557 /*-------------------------------------------------------------------------------------------------------------------------------+
558 | Created By : Chong |
559 | Creation Date : 05/MAR/2013 |
560 | Bug Number/ER Name : eTDS |
561 | SubProgram Name : Process repository records while surcharge calculation or surcharge rollback |
562 | Type : PROCEDURE |
563 | Purpose : Splite surcharge amount into repository lines when surcharge calculation |
564 | Cancel surcharge lines when surcharge rollback |
568 |--------------------------------------------------------------------------------------------------------------------------------|
565 | TDD Reference : |
566 | Assumptions : |
567 | Called From : |
569 | parameters IN/OUT Type Required Description and Purpose |
570 | ------------ -------- ------ ---------- ------------------------- |
571 | pn_source_invoice_id IN NUMBER No source invoice id |
572 | pn_taxable_amount IN NUMBER yes taxable amount |
573 | pn_tax_amount IN NUMBER yes tax amount |
574 | pv_event IN VARCHAR2 yes TDS event |
575 | pn_threshold_grp_id IN NUMBER yes TDS threshold grop id |
576 | pn_surcharge_tax_id IN NUMBER Yes surcharge tax id |
577 ---------------------------------------------------------------------------------------------------------------------------------*/
578 PROCEDURE process_surcharge(pn_source_invoice_id IN NUMBER
579 ,pv_event IN VARCHAR2
580 ,pn_taxable_amount IN NUMBER
581 ,pn_tax_amount IN NUMBER
582 ,pn_threshold_grp_id IN NUMBER
583 ,pn_surcharge_tax_id IN NUMBER
584 )
585 IS
586 --Get all effective TDS invoice records, which under surcharge slab
587 cursor c_get_reocrds_nosur(p_threshold_grp_id in NUMBER) is
588 Select jatr.invoice_id
589 ,jatr.tds_invoice_type
590 ,jatr.threshold_grp_id
591 ,jatr.tax_category_id
592 ,jatr.tds_inv_tax_amount
593 ,jatr.base_tax_amount
594 From jai_ap_tds_repository jatr
595 Where jatr.threshold_grp_id = p_threshold_grp_id
596 And NVL(jatr.migrated,G_N) = G_N
597 And NVL(jatr.invoice_deleted, G_N) = G_N
598 And NVL(jatr.source_invoice_deleted, G_N) = G_N
599 And jatr.tax_category_id is not null
600 And not exists ( --make sure under surcharge slab
601 Select 1
602 From jai_cmn_tax_ctg_lines jctcl
603 ,jai_cmn_taxes_all jcta
604 ,jai_ap_tds_repo_lines jatrl
605 Where jctcl.tax_id = jcta.tax_id
606 And jctcl.tax_category_id = jatr.tax_category_id
607 AND jatrl.invoice_id = jatr.invoice_id
608 AND jatrl.tax_id = jcta.tax_id
609 AND NVL(jatrl.surcharge_rollback, G_N) =G_N
610 And jcta.tax_type = G_TDS_SURCHARGE
611 )
612 ;
613
614 ln_proportion NUMBER;
615 ln_taxable_amount NUMBER;
616 ln_surcharge_tax_amount NUMBER;
617 ln_tax_rounding_rule NUMBER;
618 r_jai_ap_tds_repo_lines jai_ap_tds_repo_lines%rowtype;
619
620 l_api_name CONSTANT VARCHAR2(50) := 'process_surcharge()';
621 BEGIN
622 jai_cmn_utils_pkg.WRITE_FND_LOG(G_LEVEL_PROCEDURE, G_MODULE_NAME || l_api_name ||'.BEGIN', G_PKG_NAME || ': '|| l_api_name || '()+');
623
624
625 IF pv_event = G_SURCHARGE_ROLLBACK THEN
626 --Process Surcharge rollback, mark all surcharge tax code to surcharge_rollback = 'Y'
627 UPDATE jai_ap_tds_repo_lines jatrl
628 SET jatrl.surcharge_rollback = G_Y
629 WHERE jatrl.tax_type= G_TDS_SURCHARGE
630 AND NVL(jatrl.surcharge_rollback, G_N) =G_N
631 AND EXISTS(
632 SELECT 1
633 FROM jai_ap_tds_repository jatr
634 WHERE jatr.invoice_id = jatrl.invoice_id --TDS invoice ID
635 AND jatr.threshold_grp_id = pn_threshold_grp_id
636 )
637 ;
638
639 ELSIF pv_event = G_SURCHARGE_CALCULATE THEN
640 --Process surcharge calculate
641 ln_proportion := 0;
642 ln_taxable_amount := pn_taxable_amount;
643 ln_surcharge_tax_amount := pn_tax_amount;
644 r_jai_ap_tds_repo_lines := NULL;
645 ln_tax_rounding_rule := jai_ap_dtc_generation_pkg.get_tax_rounding(pn_source_invoice_id);
646
647 --Surcharge calculation, split surcharge amount into each invoice below surcharge slab.
648 FOR r_get_reocrds_nosur IN c_get_reocrds_nosur (pn_threshold_grp_id)
649 LOOP
650
651 ln_proportion := r_get_reocrds_nosur.tds_inv_tax_amount / ln_taxable_amount;
652
653 r_jai_ap_tds_repo_lines.invoice_id := r_get_reocrds_nosur.invoice_id;
654 r_jai_ap_tds_repo_lines.tax_id := pn_surcharge_tax_id;
655 --r_jai_ap_tds_repo_lines.tax_name := derive tax_name by tax_id (or have no need to save tax name here)
656 r_jai_ap_tds_repo_lines.tax_type := G_TDS_SURCHARGE;
657
658 --calculate the proportion surcharge amount and round it by TDS rounding rule
659 r_jai_ap_tds_repo_lines.tax_amount := round(ln_surcharge_tax_amount * ln_proportion , ln_tax_rounding_rule);
660
661 r_jai_ap_tds_repo_lines.CREATION_DATE := sysdate;
662 r_jai_ap_tds_repo_lines.CREATED_BY := fnd_global.user_id;
663 r_jai_ap_tds_repo_lines.LAST_UPDATE_DATE := sysdate;
667 --Insert one repository line
664 r_jai_ap_tds_repo_lines.LAST_UPDATE_LOGIN := fnd_global.login_id;
665 r_jai_ap_tds_repo_lines.LAST_UPDATED_BY := fnd_global.user_id;
666
668 insert_jai_ap_tds_repo_lines(r_jai_ap_tds_repo_lines);
669 END LOOP;
670 END IF;
671
672
673 jai_cmn_utils_pkg.WRITE_FND_LOG(G_LEVEL_PROCEDURE, G_MODULE_NAME || l_api_name ||'.END', G_PKG_NAME || ': '|| l_api_name || '()-');
674 EXCEPTION
675 WHEN OTHERS THEN
676 jai_cmn_utils_pkg.WRITE_FND_LOG(G_LEVEL_PROCEDURE
677 ,G_MODULE_NAME||l_api_name||'. Other_Exception'
678 ,SQLCODE || ':' || SQLERRM);
679 RAISE;
680 END process_surcharge;
681
682
683 /*---------------------------------------------------------------------
684 --When Given challan info in TDS challan detail UI, populdate information into repository.
685 ---------------------------------------------------------------------*/
686 PROCEDURE populate_challan_info(pn_invoice_id IN NUMBER
687 ,pn_challan_no IN NUMBER
688 ,pd_check_date IN DATE
689 ,pd_check_deposit_date IN DATE
690 ,pv_bsr_code IN VARCHAR2
691 ,pv_payment_reference_id IN VARCHAR2
692 )
693 IS
694 l_api_name CONSTANT VARCHAR2(50) := 'populate_challan_info()';
695 BEGIN
696 jai_cmn_utils_pkg.WRITE_FND_LOG(G_LEVEL_PROCEDURE, G_MODULE_NAME || l_api_name ||'.BEGIN', G_PKG_NAME || ': '|| l_api_name || '()+');
697 jai_cmn_utils_pkg.WRITE_FND_LOG(G_LEVEL_PROCEDURE, G_MODULE_NAME || l_api_name ||'.Parameters', pn_invoice_id || ': '|| pn_invoice_id);
698 jai_cmn_utils_pkg.WRITE_FND_LOG(G_LEVEL_PROCEDURE, G_MODULE_NAME || l_api_name ||'.Parameters', pn_challan_no || ': '|| pn_challan_no);
699
700 IF pn_invoice_id IS NOT NULL THEN
701 --update challan information, and TDS_PAID,REQUIRED_FOR_ETDS as 'Y'
702 UPDATE jai_ap_tds_repository jatr
703 SET jatr.tds_paid = G_Y
704 ,jatr.required_for_etds = G_Y
705 ,jatr.challan_number = pn_challan_no
706 ,jatr.check_date = pd_check_date
707 ,jatr.check_deposit_date = pd_check_deposit_date
708 ,jatr.bsr_code = pv_bsr_code
709 ,jatr.payment_reference_id = pv_payment_reference_id
710 WHERE jatr.invoice_id = pn_invoice_id
711 ;
712 END IF;
713
714 jai_cmn_utils_pkg.WRITE_FND_LOG(G_LEVEL_PROCEDURE, G_MODULE_NAME || l_api_name ||'.END', G_PKG_NAME || ': '|| l_api_name || '()-');
715 EXCEPTION
716 WHEN OTHERS THEN
717 jai_cmn_utils_pkg.WRITE_FND_LOG(G_LEVEL_PROCEDURE
718 ,G_MODULE_NAME||l_api_name||'. Other_Exception'
719 ,SQLCODE || ':' || SQLERRM);
720 RAISE;
721 END populate_challan_info;
722
723 /*---------------------------------------------------------------------
724 --Procedure to migrate existing transactions to new TDS architecture.
725 ---------------------------------------------------------------------*/
726 PROCEDURE process_etds_migration
727 IS
728
729 --Get regime definition information
730 CURSOR c_get_tax_regime_info(pv_tax_regime varchar2
731 ) IS
732 SELECT jl.meaning MEANING
733 ,jl.lookup_code LOOKUP_CODE
734 ,jrd.regime_id REGIME_ID
735 FROM ja_lookups jl
736 ,jai_rgm_definitions jrd
737 WHERE jl.lookup_code = jrd.regime_code
738 AND jl.lookup_type = G_JAI_INDIA_TAX_REGIMES
739 AND jl.lookup_code = pv_tax_regime
740 ;
741
742 -- group_id in existing system (by jai_ap_tds_thhold_grps table)
743 CURSOR c_transition_count(pn_threshold_grp_id NUMBER) IS
744 SELECT COUNT(jatr.invoice_id) cnt_exists
745 FROM jai_ap_tds_repository jatr
746 WHERE jatr.threshold_grp_id = pn_threshold_grp_id
747 AND jatr.event like 'THRESHOLD TRANSITION%'
748 ;
749
750 CURSOR c_chk_threshold_rollback(p_threshold_grp_id IN NUMBER)
751 IS
752 SELECT invoice_to_tds_authority_id
753 FROM jai_ap_tds_thhold_trxs
754 WHERE tds_event LIKE 'THRESHOLD ROLLBACK%'
755 AND threshold_grp_id = p_threshold_grp_id
756 AND invoice_to_tds_authority_id > (SELECT MAX(invoice_to_tds_authority_id)
757 FROM jai_ap_tds_thhold_trxs
758 WHERE threshold_grp_id = p_threshold_grp_id
759 AND tds_event LIKE 'THRESHOLD TRANSITION%'
760 );
761
762 --Get effective surcharge calculate transactions, which not be rollbacked.
763 CURSOR c_effective_surcharge(p_threshold_grp_id IN NUMBER)
764 IS
765 SELECT jattt_out.invoice_to_tds_authority_id
766 FROM jai_ap_tds_thhold_trxs jattt_out
767 WHERE jattt_out.tds_event = G_SURCHARGE_CALCULATE
768 AND jattt_out.threshold_grp_id = p_threshold_grp_id
769 AND NOT EXISTS (SELECT 1
770 FROM jai_ap_tds_thhold_trxs jattt_in
771 WHERE jattt_in.threshold_grp_id = p_threshold_grp_id
772 AND jattt_in.tds_event = G_SURCHARGE_ROLLBACK
773 AND jattt_in.threshold_trx_id > jattt_out.threshold_trx_id
774 );
775
776
777 cursor c_get_tax_from_category(p_tax_category_id in NUMBER) is
778 Select jctcl.tax_id
779 From jai_cmn_tax_ctg_lines jctcl
780 ,Jai_cmn_taxes_all jcta
781 Where jcta.tax_id = jctcl.tax_id
785
782 AND jctcl.tax_category_id = p_tax_category_id
783 AND jcta.tax_type in (G_TDS, G_TDS_EDUCATION_CESS, G_TDS_SH_EDU_CESS, G_TDS_SURCHARGE ); --exclude WCT/ESSI
784
786 CURSOR c_tax_rates(p_tax_id JAI_CMN_TAXES_ALL.tax_id%TYPE)
787 IS
788 SELECT
789 NVL(tax_rate,0) tax_rate,
790 (NVL(tax_rate,0) - (NVL(surcharge_rate,0) + NVL(cess_rate,0) + NVL(sh_cess_rate,0))) tds_rate,
791 NVL(surcharge_rate,0) surcharge_rate,
792 NVL(cess_rate,0) cess_rate,
793 NVL(sh_cess_rate,0) sh_cess_rate
794 FROM
795 JAI_CMN_TAXES_ALL jtc
796 WHERE
797 tax_id = p_tax_id ;
798
799 --Get transaction information by TDS invoice_id
800 CURSOR c_get_trx_tds_info(p_thhold_trx_id NUMBER) IS
801 SELECT jattt.threshold_trx_id
802 ,jattt.invoice_id
803 ,jattt.threshold_grp_id
804 ,jattt.threshold_hdr_id
805 ,jattt.invoice_to_tds_authority_num
806 ,jattt.invoice_to_tds_authority_id
807 ,jattt.tds_event
808 ,jattt.invoice_to_tds_authority_amt
809 ,jattt.invoice_to_vendor_amt
810 ,jattt.taxable_amount
811 ,jattt.tax_category_id
812 ,jattt.tds_authority_vendor_id
813 ,jattt.tds_authority_vendor_site_id
814 FROM jai_ap_tds_thhold_trxs jattt
815 WHERE jattt.threshold_trx_id = p_thhold_trx_id
816 ;
817 --get prepayment distribution id
818 CURSOR c_get_prepay_dist_id( pn_inv_dist_id NUMBER
819 ) IS
820 SELECT aida.prepay_distribution_id
821 FROM ap_invoice_distributions_all aida
822 WHERE aida.invoice_distribution_id = pn_inv_dist_id
823 ;
824 --Derive apply from invoice info
825 CURSOR c_get_apply_fromto_inv (pn_distribution_id NUMBER
826 )IS
827 SELECT aia.invoice_id
828 ,aia.invoice_type_lookup_code invoice_type
829 ,aia.invoice_amount
830 ,aia.vendor_id
831 ,aia.vendor_site_id
832 ,aia.invoice_date
833 ,aia.gl_date
834 ,ABS(aida.amount) apply_amount
835 FROM ap_invoices_all aia
836 ,ap_invoice_distributions_all aida
837 WHERE aia.invoice_id = aida.invoice_id
838 AND aida.invoice_distribution_id = pn_distribution_id
839 ;
840
841 CURSOR c_threshold_tran_inv (p_threshold_grp_id IN NUMBER)
842 IS
843 SELECT invoice_id
844 --, tds_check_id
845 , tax_id
846 , TDS_INV_TAX_AMOUNT
847 , base_tax_amount
848 FROM jai_ap_tds_repository
849 WHERE invoice_id = (SELECT MAX(invoice_to_tds_authority_id)
850 FROM jai_ap_tds_thhold_trxs
851 WHERE threshold_grp_id = p_threshold_grp_id
852 AND tds_event LIKE 'THRESHOLD TRANSITION%'
853 )
854 AND MIGRATED = G_Y
855 ;
856
857 CURSOR c_gl_date(p_tds_invoice_id IN NUMBER)
858 IS
859 SELECT gl_date
860 FROM ap_invoices_all
861 WHERE invoice_id = p_tds_invoice_id
862 ;
863
864 v_legalEntityTan VARCHAR2(50);
865 lv_voided CONSTANT VARCHAR2(30) := 'VOIDED'; --rchandan for bug#4428980
866 lv_stop_init CONSTANT VARCHAR2(30) := 'STOP INITIATED';--rchandan for bug#4428980
867 lv_india_tds_source CONSTANT VARCHAR2(30) := 'INDIA TDS';--rchandan for bug#4428980
868 lv_source_attribute VARCHAR2(30);--rchandan for bug#4428980
869
870 CURSOR c_tds_payment_check_id(p_invoice_id IN NUMBER) IS
871 SELECT pay.check_id, apc.bank_account_name, JATP.Challan_no challan_num,
872 JATP.check_deposit_date challan_date, JATP.bsr_code branch_code, apc.check_number check_number
873 FROM ap_invoice_payments_all PAY
874 , ap_checks_all APC
875 , JAI_AP_TDS_PAYMENTS JATP
876 WHERE PAY.invoice_id = p_invoice_id
877 AND PAY.check_id = APC.check_id
878 AND APC.check_id = JATP.check_id
879 AND APC.status_lookup_code NOT IN (lv_voided,lv_stop_init);
880
881 r_c_get_tax_regime_info c_get_tax_regime_info%rowtype;
882 r_get_trx_tds_info c_get_trx_tds_info%rowtype;
883 r_get_prepay_dist_id c_get_prepay_dist_id%rowtype;
884 r_get_apply_from_inv c_get_apply_fromto_inv%rowtype;
885 r_get_apply_to_inv c_get_apply_fromto_inv%rowtype;
886 r_chk_threshold_rollback c_chk_threshold_rollback%rowtype;
887 r_threshold_tran_inv c_threshold_tran_inv%rowtype;
888 r_tds_payment_check_id c_tds_payment_check_id%rowtype;
889 r_tax_rates c_tax_rates%rowtype;
890 r_jai_ap_tds_repo_lines jai_ap_tds_repo_lines%rowtype;
891 ln_transition_count NUMBER;
892 ln_tax_category_id NUMBER;
893
894 ln_calculated_inv_amt NUMBER;
895 ln_pp_apply_amt NUMBER;
896 ln_remain_amt NUMBER;
897 lv_bank_branch_code AP_BANK_BRANCHES.bank_num%TYPE;
898 ln_surcharge_amount NUMBER;
899 ln_sh_cess_amount NUMBER;
900 ln_cess_amount NUMBER;
901 ln_tds_amount NUMBER;
902 ld_gl_date DATE;
903
904
905 l_api_name CONSTANT VARCHAR2(50) := 'process_etds_migration()';
906 BEGIN
907 jai_cmn_utils_pkg.WRITE_FND_LOG(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_api_name||'.BEGIN', G_PKG_NAME || ': '||l_api_name||'()+');
908
909 --Derive tds regime ID and code from regime setup
913
910 OPEN c_get_tax_regime_info(G_TDS);
911 FETCH c_get_tax_regime_info INTO r_c_get_tax_regime_info;
912 CLOSE c_get_tax_regime_info;
914
915 --Loop all group_id in existing system (by jai_ap_tds_thhold_grps table)
916 For r_thhold_grp_rec IN (SELECT DISTINCT jattg.threshold_grp_id
917 FROM jai_ap_tds_thhold_grps jattg
918 )
919 LOOP
920
921 --Step 1. Pick all TDS invoices base on jai_ap_tds_thhold_trxs table.
922 INSERT INTO jai_ap_tds_repository (
923 TDS_REPOSITORY_ID
924 ,INVOICE_ID
925 ,INVOICE_NUMBER
926 ,TDS_INVOICE_TYPE
927 ,THRESHOLD_GRP_ID
928 ,THRESHOLD_HDR_ID
929 ,THRESHOLD_TYPE_ID
930 ,THRESHOLD_SLAB_ID
931 ,EVENT
932 ,SOURCE_INVOICE_ID
933 ,SOURCE_INVOICE_TYPE
934 ,SOURCE_INVOICE_AMT
935 ,SOURCE_ID
936 ,SOURCE_SITE_ID
937 ,SOURCE_INVOICE_DATE
938 ,ACCOUNTING_DATE
939 ,APPLIED_FROM_INVOICE_ID
940 ,APPLIED_FROM_INVOICE_TYPE
941 ,APPLIED_FROM_INVOICE_AMT
942 ,APPLIED_FROM_SUPPLIER_ID
943 ,APPLIED_FROM_SUPPLIER_SITE_ID
944 ,APPLIED_FROM_INVOICE_DATE
945 ,APPLIED_FROM_ACCTG_DATE
946 ,FROM_SLAB_ID
947 ,APPLIED_TO_INVOICE_ID
948 ,APPLIED_TO_INVOICE_TYPE
949 ,APPLIED_TO_INVOICE_AMT
950 ,APPLIED_TO_SUPPLIER_ID
951 ,APPLIED_TO_SUPPLIER_SITE_ID
952 ,APPLIED_TO_INVOICE_DATE
953 ,APPLIED_TO_ACCTG_DATE
954 ,APPLIED_AMOUNT
955 ,TO_SLAB_ID
956 ,UNAPPLIED_AMOUNT
957 ,TDS_ACCOUNTING_DATE
958 ,REGIME_ID
959 ,REGIME_CODE
960 ,ORG_ID
961 ,LEGAL_ENTITY_ID
962 ,ORG_TAN_NUMBER
963 ,SECTION_TYPE
964 ,SECTION_CODE
965 ,TAX_RATE
966 ,TAX_ID
967 ,TAX_CATEGORY_ID
968 ,TDS_PAID
969 ,INVOICE_DELETED
970 ,SOURCE_INVOICE_DELETED
971 ,CHALLAN_NUMBER
972 ,Check_DATE
973 ,BSR_CODE
974 ,PAYMENT_REFERENCE_ID
975 ,TDS_INV_TAX_AMOUNT
976 ,SUPPLIER_CM_AMOUNT
977 ,BASE_TAX_AMOUNT
978 ,REQUIRED_FOR_ETDS
979 ,MIGRATED
980 ,CREATION_DATE
981 ,CREATED_BY
982 ,LAST_UPDATE_DATE
983 ,LAST_UPDATE_LOGIN
984 ,LAST_UPDATED_BY
985 ) SELECT
986 JAI_AP_TDS_REPOSITORY_S.NEXTVAL
987 ,jattt.invoice_to_tds_authority_id
988 ,tds_invoices.invoice_num
989 --,jattt.tds_event --get_tds_invoice_type(jattt.tds_event)
990 ,decode(jattt.tds_event, G_INVOICE_VALIDATE, 'NORMAL'
991 , G_PREPAYMENT_APPLICATION, 'RTN'
992 , G_PREPAYMENT_UNAPPLICATION, 'REV-RTN'
993 , G_SURCHARGE_CALCULATE, 'SURCHARGE'
994 , G_SURCHARGE_ROLLBACK, 'SURCHARGE ROLLBACK'
995 ,decode(SUBSTR(jattt.tds_event, 0, 20), G_THRESHOLD_TRANSITION, 'TRANSITION'
996 ,decode(SUBSTR(jattt.tds_event, 0, 18), G_THRESHOLD_ROLLBACK, 'ROLLBACK',NULL))
997 )
998 ,jattt.threshold_grp_id
999 ,jattt.threshold_hdr_id
1000 ,NULL
1001 ,NULL
1002 ,jattt.tds_event
1003 ,jattt.invoice_id
1004 ,base_invoices.invoice_type_lookup_code
1005 ,base_invoices.invoice_amount
1006 ,base_invoices.vendor_id
1007 ,base_invoices.vendor_site_id
1008 ,base_invoices.invoice_date
1009 ,base_invoices.gl_date
1010 ,NULL
1011 ,NULL
1012 ,NULL
1013 ,NULL
1014 ,NULL
1015 ,NULL
1016 ,NULL
1017 ,NULL
1018 ,NULL
1019 ,NULL
1020 ,NULL
1021 ,NULL
1022 ,NULL
1023 ,NULL
1024 ,NULL
1025 ,NULL
1026 ,NULL
1027 ,NULL
1028 ,tds_invoices.gl_date
1029 ,r_c_get_tax_regime_info.regime_id --TDS regiem ID
1030 ,r_c_get_tax_regime_info.lookup_code ---lv_regime_code
1031 ,tds_invoices.org_id
1032 ,tds_invoices.legal_entity_id
1033 ,jror.attribute_value --ORG_TAN_NUMBER
1034 ,DECODE(REGEXP_SUBSTR(jattt.invoice_to_tds_authority_num, '(WCT|TDS|ESSI)-\w+-\d+$',1,1,'i',1)
1035 ,'WCT', G_WCT_SECTION
1036 ,'ESSI',G_ESSI_SECTION
1037 , G_TDS_SECTION) --SECTION TYPE
1038 ,jattt.tds_section_code
1039 ,jattt.tax_rate
1040 ,jattt.tax_id
1041 ,NULL --TAX_CATEGORY_ID
1042 ,NULL --TDS_PAID
1043 ,decode(base_invoices.cancelled_date,NULL,'N','Y') --INVOICE_DELETED
1044 ,decode(base_invoices.cancelled_date,NULL,'N','Y') --SOURCE_INVOICE_DELETED
1045 ,NULL --CHALLAN_NUMBER
1046 ,NULL --CHALLAN_DATE
1047 ,NULL --BSR_CODE
1048 ,NULL --PAYMENT_REFERENCE_ID
1049 ,tds_invoices.invoice_amount --TDS_INV_TAX_AMOUNT
1050 ,vendor_invoices.invoice_amount --SUPPLIER_CM_AMOUNT
1051 ,jattt.taxable_amount --BASE_TAX_AMOUNT
1052 ,G_N --required for eTDS
1053 ,G_Y --MIGRATED
1054 ,sysdate
1055 ,fnd_global.user_id
1056 ,sysdate
1057 ,fnd_global.login_id
1058 ,fnd_global.user_id
1059 from jai_ap_tds_thhold_trxs jattt
1060 , ap_invoices_all base_invoices
1061 , ap_invoices_all tds_invoices
1062 , ap_invoices_all vendor_invoices
1063 , JAI_RGM_ORG_REGNS_V jror
1064 where jattt.invoice_id = base_invoices.invoice_id
1065 and jattt.invoice_to_tds_authority_id = tds_invoices.invoice_id
1066 and jattt.invoice_to_vendor_id = vendor_invoices.invoice_id
1067 and jattt.threshold_grp_id = r_thhold_grp_rec.threshold_grp_id
1068 and tds_invoices.org_id = jror.organization_id
1069 and jror.regime_code = G_TDS
1070 and jror.registration_type = 'OTHERS'
1071 and jror.attribute_code = 'TAN NO'
1072 and not exists (select '1'
1073 from jai_ap_tds_repository jatr
1074 where jatr.invoice_id = jattt.invoice_to_tds_authority_id
1075 )
1076 ;
1077
1078 --Step 2. Pick all Invoices with TDS Event as Prepayment Application/Unapplication. Populate APPLIED_FROM and APPLIED_TO columns
1079 FOR r_tds_pp_rec IN (SELECT jatp.INVOICE_DISTRIBUTION_ID_PREPAY
1080 ,jatp.INVOICE_DISTRIBUTION_ID
1081 ,jatp.Invoice_id apply_to_invoice_id
1082 ,jatp.TDS_THRESHOLD_TRX_ID_APPLY
1083 ,jatp.TDS_THRESHOLD_TRX_ID_UNAPPLY
1084 ,jatp.WCT_THRESHOLD_TRX_ID_APPLY
1085 ,jatp.WCT_THRESHOLD_TRX_ID_UNAPPLY
1086 ,jatp.ESSI_THRESHOLD_TRX_ID_APPLY
1087 ,jatp.ESSI_THRESHOLD_TRX_ID_UNAPPLY
1088 FROM jai_ap_tds_prepayments jatp
1089 WHERE jatp.tds_threshold_grp_id = r_thhold_grp_rec.threshold_grp_id
1090 )
1091 LOOP
1092 --process TDS application
1093 IF r_tds_pp_rec.tds_threshold_trx_id_apply IS NOT NULL
1094 THEN
1095 --Derive invoice_to_tds_authority_id from jai_ap_tds_thhold_trx table by given trx ID.
1096 OPEN c_get_trx_tds_info(r_tds_pp_rec.tds_threshold_trx_id_apply);
1097 FETCH c_get_trx_tds_info INTO r_get_trx_tds_info;
1098 CLOSE c_get_trx_tds_info;
1099 --Then update apply from/to column as needed
1100 OPEN c_get_prepay_dist_id(r_tds_pp_rec.invoice_distribution_id_prepay);
1101 FETCH c_get_prepay_dist_id INTO r_get_prepay_dist_id;
1102 CLOSE c_get_prepay_dist_id;
1103 --derive apply from prepayment invoice info
1104 OPEN c_get_apply_fromto_inv(r_get_prepay_dist_id.prepay_distribution_id);
1105 FETCH c_get_apply_fromto_inv INTO r_get_apply_from_inv;
1106 CLOSE c_get_apply_fromto_inv;
1107 --derive apply to prepayment invoice info
1108 OPEN c_get_apply_fromto_inv(r_tds_pp_rec.invoice_distribution_id);
1109 FETCH c_get_apply_fromto_inv INTO r_get_apply_to_inv;
1110 CLOSE c_get_apply_fromto_inv;
1111
1112 UPDATE jai_ap_tds_repository
1113 SET APPLIED_FROM_INVOICE_ID = r_get_apply_from_inv.invoice_id
1114 ,APPLIED_FROM_INVOICE_TYPE = r_get_apply_from_inv.invoice_type
1115 ,APPLIED_FROM_INVOICE_AMT = r_get_apply_from_inv.invoice_amount
1116 ,APPLIED_FROM_SUPPLIER_ID = r_get_apply_from_inv.vendor_id
1117 ,APPLIED_FROM_SUPPLIER_SITE_ID = r_get_apply_from_inv.vendor_site_id
1118 ,APPLIED_FROM_INVOICE_DATE = r_get_apply_from_inv.invoice_date
1119 ,APPLIED_FROM_ACCTG_DATE = r_get_apply_from_inv.gl_date
1120 ,APPLIED_TO_INVOICE_ID = r_get_apply_to_inv.invoice_id
1121 ,APPLIED_TO_INVOICE_TYPE = r_get_apply_to_inv.invoice_type
1122 ,APPLIED_TO_INVOICE_AMT = r_get_apply_to_inv.invoice_amount
1123 ,APPLIED_TO_SUPPLIER_ID = r_get_apply_to_inv.vendor_id
1124 ,APPLIED_TO_SUPPLIER_SITE_ID = r_get_apply_to_inv.vendor_site_id
1125 ,APPLIED_TO_INVOICE_DATE = r_get_apply_to_inv.invoice_date
1126 ,APPLIED_TO_ACCTG_DATE = r_get_apply_to_inv.gl_date
1127 ,APPLIED_AMOUNT = r_get_trx_tds_info.taxable_amount
1128 WHERE invoice_id = r_get_trx_tds_info.invoice_to_tds_authority_id;
1129 END IF;
1130 --process WCT application
1131 IF r_tds_pp_rec.wct_threshold_trx_id_apply IS NOT NULL
1132 THEN
1133 --Derive invoice_to_tds_authority_id from jai_ap_tds_thhold_trx table by given trx ID.
1134 OPEN c_get_trx_tds_info(r_tds_pp_rec.wct_threshold_trx_id_apply);
1135 FETCH c_get_trx_tds_info INTO r_get_trx_tds_info;
1139 FETCH c_get_prepay_dist_id INTO r_get_prepay_dist_id;
1136 CLOSE c_get_trx_tds_info;
1137 --Then update apply from/to column as needed
1138 OPEN c_get_prepay_dist_id(r_tds_pp_rec.invoice_distribution_id_prepay);
1140 CLOSE c_get_prepay_dist_id;
1141 --derive apply from prepayment invoice info
1142 OPEN c_get_apply_fromto_inv(r_get_prepay_dist_id.prepay_distribution_id);
1143 FETCH c_get_apply_fromto_inv INTO r_get_apply_from_inv;
1144 CLOSE c_get_apply_fromto_inv;
1145 --derive apply to prepayment invoice info
1146 OPEN c_get_apply_fromto_inv(r_tds_pp_rec.invoice_distribution_id);
1147 FETCH c_get_apply_fromto_inv INTO r_get_apply_to_inv;
1148 CLOSE c_get_apply_fromto_inv;
1149
1150 UPDATE jai_ap_tds_repository
1151 SET APPLIED_FROM_INVOICE_ID = r_get_apply_from_inv.invoice_id
1152 ,APPLIED_FROM_INVOICE_TYPE = r_get_apply_from_inv.invoice_type
1153 ,APPLIED_FROM_INVOICE_AMT = r_get_apply_from_inv.invoice_amount
1154 ,APPLIED_FROM_SUPPLIER_ID = r_get_apply_from_inv.vendor_id
1155 ,APPLIED_FROM_SUPPLIER_SITE_ID = r_get_apply_from_inv.vendor_site_id
1156 ,APPLIED_FROM_INVOICE_DATE = r_get_apply_from_inv.invoice_date
1157 ,APPLIED_FROM_ACCTG_DATE = r_get_apply_from_inv.gl_date
1158 ,APPLIED_TO_INVOICE_ID = r_get_apply_to_inv.invoice_id
1159 ,APPLIED_TO_INVOICE_TYPE = r_get_apply_to_inv.invoice_type
1160 ,APPLIED_TO_INVOICE_AMT = r_get_apply_to_inv.invoice_amount
1161 ,APPLIED_TO_SUPPLIER_ID = r_get_apply_to_inv.vendor_id
1162 ,APPLIED_TO_SUPPLIER_SITE_ID = r_get_apply_to_inv.vendor_site_id
1163 ,APPLIED_TO_INVOICE_DATE = r_get_apply_to_inv.invoice_date
1164 ,APPLIED_TO_ACCTG_DATE = r_get_apply_to_inv.gl_date
1165 ,APPLIED_AMOUNT = r_get_trx_tds_info.taxable_amount
1166 WHERE invoice_id = r_get_trx_tds_info.invoice_to_tds_authority_id;
1167 END IF;
1168 --process ESSI application
1169 IF r_tds_pp_rec.essi_threshold_trx_id_apply IS NOT NULL
1170 THEN
1171 --Derive invoice_to_tds_authority_id from jai_ap_tds_thhold_trx table by given trx ID.
1172 OPEN c_get_trx_tds_info(r_tds_pp_rec.essi_threshold_trx_id_apply);
1173 FETCH c_get_trx_tds_info INTO r_get_trx_tds_info;
1174 CLOSE c_get_trx_tds_info;
1175 --Then update apply from/to column as needed
1176 OPEN c_get_prepay_dist_id(r_tds_pp_rec.invoice_distribution_id_prepay);
1177 FETCH c_get_prepay_dist_id INTO r_get_prepay_dist_id;
1178 CLOSE c_get_prepay_dist_id;
1179 --derive apply from prepayment invoice info
1180 OPEN c_get_apply_fromto_inv(r_get_prepay_dist_id.prepay_distribution_id);
1181 FETCH c_get_apply_fromto_inv INTO r_get_apply_from_inv;
1182 CLOSE c_get_apply_fromto_inv;
1183 --derive apply to prepayment invoice info
1184 OPEN c_get_apply_fromto_inv(r_tds_pp_rec.invoice_distribution_id);
1185 FETCH c_get_apply_fromto_inv INTO r_get_apply_to_inv;
1186 CLOSE c_get_apply_fromto_inv;
1187
1188 UPDATE jai_ap_tds_repository
1189 SET APPLIED_FROM_INVOICE_ID = r_get_apply_from_inv.invoice_id
1190 ,APPLIED_FROM_INVOICE_TYPE = r_get_apply_from_inv.invoice_type
1191 ,APPLIED_FROM_INVOICE_AMT = r_get_apply_from_inv.invoice_amount
1192 ,APPLIED_FROM_SUPPLIER_ID = r_get_apply_from_inv.vendor_id
1193 ,APPLIED_FROM_SUPPLIER_SITE_ID = r_get_apply_from_inv.vendor_site_id
1194 ,APPLIED_FROM_INVOICE_DATE = r_get_apply_from_inv.invoice_date
1195 ,APPLIED_FROM_ACCTG_DATE = r_get_apply_from_inv.gl_date
1196 ,APPLIED_TO_INVOICE_ID = r_get_apply_to_inv.invoice_id
1197 ,APPLIED_TO_INVOICE_TYPE = r_get_apply_to_inv.invoice_type
1198 ,APPLIED_TO_INVOICE_AMT = r_get_apply_to_inv.invoice_amount
1199 ,APPLIED_TO_SUPPLIER_ID = r_get_apply_to_inv.vendor_id
1200 ,APPLIED_TO_SUPPLIER_SITE_ID = r_get_apply_to_inv.vendor_site_id
1201 ,APPLIED_TO_INVOICE_DATE = r_get_apply_to_inv.invoice_date
1202 ,APPLIED_TO_ACCTG_DATE = r_get_apply_to_inv.gl_date
1203 ,APPLIED_AMOUNT = r_get_trx_tds_info.taxable_amount
1204 WHERE invoice_id = r_get_trx_tds_info.invoice_to_tds_authority_id;
1205 END IF;
1206 ----------------------------------------------------------------------------------
1207 --process TDS unapplication
1208 IF r_tds_pp_rec.tds_threshold_trx_id_unapply IS NOT NULL
1209 THEN
1210 --Derive invoice_to_tds_authority_id from jai_ap_tds_thhold_trx table by given trx ID.
1211 OPEN c_get_trx_tds_info(r_tds_pp_rec.tds_threshold_trx_id_unapply);
1212 FETCH c_get_trx_tds_info INTO r_get_trx_tds_info;
1213 CLOSE c_get_trx_tds_info;
1214 --Then update apply from/to column as needed
1215 OPEN c_get_prepay_dist_id(r_tds_pp_rec.invoice_distribution_id_prepay);
1216 FETCH c_get_prepay_dist_id INTO r_get_prepay_dist_id;
1217 CLOSE c_get_prepay_dist_id;
1218 --derive apply from prepayment invoice info
1219 OPEN c_get_apply_fromto_inv(r_get_prepay_dist_id.prepay_distribution_id);
1220 FETCH c_get_apply_fromto_inv INTO r_get_apply_from_inv;
1221 CLOSE c_get_apply_fromto_inv;
1222 --derive apply to prepayment invoice info
1223 OPEN c_get_apply_fromto_inv(r_tds_pp_rec.invoice_distribution_id);
1224 FETCH c_get_apply_fromto_inv INTO r_get_apply_to_inv;
1225 CLOSE c_get_apply_fromto_inv;
1226
1227 UPDATE jai_ap_tds_repository
1228 SET APPLIED_FROM_INVOICE_ID = r_get_apply_from_inv.invoice_id
1229 ,APPLIED_FROM_INVOICE_TYPE = r_get_apply_from_inv.invoice_type
1230 ,APPLIED_FROM_INVOICE_AMT = r_get_apply_from_inv.invoice_amount
1231 ,APPLIED_FROM_SUPPLIER_ID = r_get_apply_from_inv.vendor_id
1232 ,APPLIED_FROM_SUPPLIER_SITE_ID = r_get_apply_from_inv.vendor_site_id
1233 ,APPLIED_FROM_INVOICE_DATE = r_get_apply_from_inv.invoice_date
1234 ,APPLIED_FROM_ACCTG_DATE = r_get_apply_from_inv.gl_date
1235 ,APPLIED_TO_INVOICE_ID = r_get_apply_to_inv.invoice_id
1236 ,APPLIED_TO_INVOICE_TYPE = r_get_apply_to_inv.invoice_type
1237 ,APPLIED_TO_INVOICE_AMT = r_get_apply_to_inv.invoice_amount
1238 ,APPLIED_TO_SUPPLIER_ID = r_get_apply_to_inv.vendor_id
1239 ,APPLIED_TO_SUPPLIER_SITE_ID = r_get_apply_to_inv.vendor_site_id
1240 ,APPLIED_TO_INVOICE_DATE = r_get_apply_to_inv.invoice_date
1241 ,APPLIED_TO_ACCTG_DATE = r_get_apply_to_inv.gl_date
1242 ,UNAPPLIED_AMOUNT = r_get_trx_tds_info.taxable_amount
1243 WHERE invoice_id = r_get_trx_tds_info.invoice_to_tds_authority_id;
1244 END IF;
1245 --process WCT unapplication
1246 IF r_tds_pp_rec.wct_threshold_trx_id_unapply IS NOT NULL
1247 THEN
1248 --Derive invoice_to_tds_authority_id from jai_ap_tds_thhold_trx table by given trx ID.
1249 OPEN c_get_trx_tds_info(r_tds_pp_rec.wct_threshold_trx_id_unapply);
1250 FETCH c_get_trx_tds_info INTO r_get_trx_tds_info;
1251 CLOSE c_get_trx_tds_info;
1252 --Then update apply from/to column as needed
1253 OPEN c_get_prepay_dist_id(r_tds_pp_rec.invoice_distribution_id_prepay);
1254 FETCH c_get_prepay_dist_id INTO r_get_prepay_dist_id;
1255 CLOSE c_get_prepay_dist_id;
1256 --derive apply from prepayment invoice info
1257 OPEN c_get_apply_fromto_inv(r_get_prepay_dist_id.prepay_distribution_id);
1258 FETCH c_get_apply_fromto_inv INTO r_get_apply_from_inv;
1259 CLOSE c_get_apply_fromto_inv;
1260 --derive apply to prepayment invoice info
1261 OPEN c_get_apply_fromto_inv(r_tds_pp_rec.invoice_distribution_id);
1262 FETCH c_get_apply_fromto_inv INTO r_get_apply_to_inv;
1263 CLOSE c_get_apply_fromto_inv;
1264
1265 UPDATE jai_ap_tds_repository
1266 SET APPLIED_FROM_INVOICE_ID = r_get_apply_from_inv.invoice_id
1267 ,APPLIED_FROM_INVOICE_TYPE = r_get_apply_from_inv.invoice_type
1268 ,APPLIED_FROM_INVOICE_AMT = r_get_apply_from_inv.invoice_amount
1269 ,APPLIED_FROM_SUPPLIER_ID = r_get_apply_from_inv.vendor_id
1270 ,APPLIED_FROM_SUPPLIER_SITE_ID = r_get_apply_from_inv.vendor_site_id
1271 ,APPLIED_FROM_INVOICE_DATE = r_get_apply_from_inv.invoice_date
1272 ,APPLIED_FROM_ACCTG_DATE = r_get_apply_from_inv.gl_date
1273 ,APPLIED_TO_INVOICE_ID = r_get_apply_to_inv.invoice_id
1274 ,APPLIED_TO_INVOICE_TYPE = r_get_apply_to_inv.invoice_type
1275 ,APPLIED_TO_INVOICE_AMT = r_get_apply_to_inv.invoice_amount
1276 ,APPLIED_TO_SUPPLIER_ID = r_get_apply_to_inv.vendor_id
1277 ,APPLIED_TO_SUPPLIER_SITE_ID = r_get_apply_to_inv.vendor_site_id
1278 ,APPLIED_TO_INVOICE_DATE = r_get_apply_to_inv.invoice_date
1279 ,APPLIED_TO_ACCTG_DATE = r_get_apply_to_inv.gl_date
1280 ,UNAPPLIED_AMOUNT = r_get_trx_tds_info.taxable_amount
1281 WHERE invoice_id = r_get_trx_tds_info.invoice_to_tds_authority_id;
1282 END IF;
1283 --process essi unapplication
1284 IF r_tds_pp_rec.essi_threshold_trx_id_unapply IS NOT NULL
1285 THEN
1286 --Derive invoice_to_tds_authority_id from jai_ap_tds_thhold_trx table by given trx ID.
1287 OPEN c_get_trx_tds_info(r_tds_pp_rec.essi_threshold_trx_id_unapply);
1288 FETCH c_get_trx_tds_info INTO r_get_trx_tds_info;
1289 CLOSE c_get_trx_tds_info;
1290 --Then update apply from/to column as needed
1291 OPEN c_get_prepay_dist_id(r_tds_pp_rec.invoice_distribution_id_prepay);
1292 FETCH c_get_prepay_dist_id INTO r_get_prepay_dist_id;
1293 CLOSE c_get_prepay_dist_id;
1294 --derive apply from prepayment invoice info
1295 OPEN c_get_apply_fromto_inv(r_get_prepay_dist_id.prepay_distribution_id);
1296 FETCH c_get_apply_fromto_inv INTO r_get_apply_from_inv;
1297 CLOSE c_get_apply_fromto_inv;
1298 --derive apply to prepayment invoice info
1299 OPEN c_get_apply_fromto_inv(r_tds_pp_rec.invoice_distribution_id);
1300 FETCH c_get_apply_fromto_inv INTO r_get_apply_to_inv;
1301 CLOSE c_get_apply_fromto_inv;
1302
1303 UPDATE jai_ap_tds_repository
1304 SET APPLIED_FROM_INVOICE_ID = r_get_apply_from_inv.invoice_id
1305 ,APPLIED_FROM_INVOICE_TYPE = r_get_apply_from_inv.invoice_type
1306 ,APPLIED_FROM_INVOICE_AMT = r_get_apply_from_inv.invoice_amount
1307 ,APPLIED_FROM_SUPPLIER_ID = r_get_apply_from_inv.vendor_id
1308 ,APPLIED_FROM_SUPPLIER_SITE_ID = r_get_apply_from_inv.vendor_site_id
1309 ,APPLIED_FROM_INVOICE_DATE = r_get_apply_from_inv.invoice_date
1310 ,APPLIED_FROM_ACCTG_DATE = r_get_apply_from_inv.gl_date
1311 ,APPLIED_TO_INVOICE_ID = r_get_apply_to_inv.invoice_id
1312 ,APPLIED_TO_INVOICE_TYPE = r_get_apply_to_inv.invoice_type
1316 ,APPLIED_TO_INVOICE_DATE = r_get_apply_to_inv.invoice_date
1313 ,APPLIED_TO_INVOICE_AMT = r_get_apply_to_inv.invoice_amount
1314 ,APPLIED_TO_SUPPLIER_ID = r_get_apply_to_inv.vendor_id
1315 ,APPLIED_TO_SUPPLIER_SITE_ID = r_get_apply_to_inv.vendor_site_id
1317 ,APPLIED_TO_ACCTG_DATE = r_get_apply_to_inv.gl_date
1318 ,UNAPPLIED_AMOUNT = r_get_trx_tds_info.taxable_amount
1319 WHERE invoice_id = r_get_trx_tds_info.invoice_to_tds_authority_id;
1320 END IF;
1321 END LOOP;
1322
1323
1324 --Setp 3.Split threshold transition TDS invoice into prior threshold invoices, then delete the transiton TDS invoice.
1325 OPEN c_transition_count(r_thhold_grp_rec.threshold_grp_id);
1326 FETCH c_transition_count INTO ln_transition_count;
1327 CLOSE c_transition_count;
1328
1329 IF ln_transition_count > 0 THEN
1330 /*Check if Threshold Rollback has occured post Threshold Transition*/
1331 OPEN c_chk_threshold_rollback(r_thhold_grp_rec.threshold_grp_id);
1332 FETCH c_chk_threshold_rollback INTO r_chk_threshold_rollback;
1333 CLOSE c_chk_threshold_rollback;
1334
1335 IF r_chk_threshold_rollback.invoice_to_tds_authority_id IS NULL THEN
1336
1337 INSERT INTO jai_ap_tds_repository (
1338 TDS_REPOSITORY_ID
1339 ,INVOICE_ID
1340 ,INVOICE_NUMBER
1341 ,TDS_AUTHORITY_ID
1342 ,TDS_AUTHORITY_SITE_ID
1343 ,TDS_INVOICE_TYPE
1344 ,THRESHOLD_GRP_ID
1345 ,THRESHOLD_HDR_ID
1346 ,THRESHOLD_TYPE_ID
1347 ,THRESHOLD_SLAB_ID
1348 ,EVENT
1349 ,SOURCE_INVOICE_ID
1350 ,SOURCE_INVOICE_TYPE
1351 ,SOURCE_INVOICE_AMT
1352 ,SOURCE_ID
1353 ,SOURCE_SITE_ID
1354 ,SOURCE_INVOICE_DATE
1355 ,ACCOUNTING_DATE
1356 ,TDS_ACCOUNTING_DATE
1357 ,REGIME_ID
1358 ,REGIME_CODE
1359 ,ORG_ID
1360 ,LEGAL_ENTITY_ID
1361 ,ORG_TAN_NUMBER
1362 ,SECTION_TYPE
1363 ,SECTION_CODE
1364 ,TAX_RATE
1365 ,TAX_ID
1366 ,TAX_CATEGORY_ID
1367 ,TDS_PAID
1368 ,INVOICE_DELETED
1369 ,SOURCE_INVOICE_DELETED
1370 ,PRIOR_THRESHOLD
1371 ,CHALLAN_NUMBER
1372 ,CHECK_DATE
1373 ,CHECK_DEPOSIT_DATE
1374 ,BSR_CODE
1375 ,PAYMENT_REFERENCE_ID
1376 ,TDS_INV_TAX_AMOUNT
1377 ,SUPPLIER_CM_AMOUNT
1378 ,BASE_TAX_AMOUNT
1379 ,REQUIRED_FOR_ETDS
1380 ,MIGRATED
1381 ,CREATION_DATE
1382 ,CREATED_BY
1383 ,LAST_UPDATE_DATE
1384 ,LAST_UPDATE_LOGIN
1385 ,LAST_UPDATED_BY
1386 )
1387 SELECT
1388 JAI_AP_TDS_REPOSITORY_S.NEXTVAL,
1389 -9999,
1390 NULL,
1391 NULL,
1392 NULL,
1393 'TRANSITION',
1394 r_thhold_grp_rec.threshold_grp_id,
1395 jatit.threshold_hdr_id,
1396 NULL,
1397 NULL,
1398 G_THRESHOLD_TRANSITION,
1399 jatit.invoice_id,
1400 aia.invoice_type_lookup_code,
1401 aia.invoice_amount,
1402 aia.vendor_id,
1403 aia.vendor_site_id,
1404 aia.invoice_date,
1405 aia.gl_date,
1406 aia.gl_date,
1407 r_c_get_tax_regime_info.regime_id,
1408 r_c_get_tax_regime_info.lookup_code,
1409 aia.org_id,
1410 aia.legal_entity_id,
1411 jror.attribute_value, --ORG_TAN_NUMBER
1412 G_TDS_SECTION,
1413 nvl(jatit.actual_section_code, jatit.default_section_code),
1414 jitc.tax_rate,
1415 nvl(jatit.actual_tax_id, jatit.default_tax_id),
1416 NULL,
1417 NULL,
1418 NULL,
1419 NULL,
1420 G_N,
1421 NULL,
1422 NULL,
1423 NULL,
1424 NULL,
1425 NULL,
1426 NULL,
1427 NULL,
1428 aia.invoice_amount,
1429 G_Y,
1430 G_Y,
1431 sysdate
1432 ,fnd_global.user_id
1433 ,sysdate
1434 ,fnd_global.login_id
1435 ,fnd_global.user_id
1436 FROM ap_invoices_all aia
1437 ,ap_invoice_distributions_all aida
1438 ,jai_ap_tds_inv_taxes jatit
1439 ,JAI_AP_TDS_VENDOR_HDRS jte
1440 ,JAI_CMN_TAXES_ALL jitc
1441 ,JAI_RGM_ORG_REGNS_V jror
1442 WHERE aia.invoice_id = jatit.invoice_id
1443 AND jatit.threshold_grp_id IS NOT NULL
1447 FROM jai_ap_tds_prepayments
1444 AND jatit.threshold_grp_id = r_thhold_grp_rec.threshold_grp_id
1445 AND aia.cancelled_date IS NULL
1446 AND NOT EXISTS (SELECT 1
1448 WHERE invoice_id = jatit.invoice_id)
1449 AND nvl(jatit.actual_tax_id, jatit.default_tax_id) = jitc.tax_id
1450 AND aia.vendor_id = jte.vendor_id
1451 AND aia.vendor_site_id = jte.vendor_site_id
1452 AND aia.invoice_id = aida.invoice_id
1453 AND aida.match_status_flag in ('A','T')
1454 AND aida.prepay_amount_remaining IS NULL
1455 AND aia.invoice_type_lookup_code IN ('STANDARD','PREPAYMENT')
1456 AND aia.org_id = jror.organization_id
1457 AND jror.regime_code = G_TDS
1458 AND jror.registration_type = 'OTHERS'
1459 AND jror.attribute_code = 'TAN NO'
1460 AND NOT EXISTS (SELECT 1
1461 FROM jai_ap_tds_repository
1462 WHERE source_invoice_id = jatit.invoice_id
1463 AND event = G_THRESHOLD_TRANSITION
1464 )
1465 ;
1466
1467
1468 /*Need to pick Invoices created before Threshold was breached and Prepayment applied*/
1469 INSERT INTO jai_ap_tds_repository (
1470 TDS_REPOSITORY_ID
1471 ,INVOICE_ID
1472 ,INVOICE_NUMBER
1473 ,TDS_AUTHORITY_ID
1474 ,TDS_AUTHORITY_SITE_ID
1475 ,TDS_INVOICE_TYPE
1476 ,THRESHOLD_GRP_ID
1477 ,THRESHOLD_HDR_ID
1478 ,THRESHOLD_TYPE_ID
1479 ,THRESHOLD_SLAB_ID
1480 ,EVENT
1481 ,SOURCE_INVOICE_ID
1482 ,SOURCE_INVOICE_TYPE
1483 ,SOURCE_INVOICE_AMT
1484 ,SOURCE_ID
1485 ,SOURCE_SITE_ID
1486 ,SOURCE_INVOICE_DATE
1487 ,ACCOUNTING_DATE
1488 ,TDS_ACCOUNTING_DATE
1489 ,REGIME_ID
1490 ,REGIME_CODE
1491 ,ORG_ID
1492 ,LEGAL_ENTITY_ID
1493 ,ORG_TAN_NUMBER
1494 ,SECTION_TYPE
1495 ,SECTION_CODE
1496 ,TAX_RATE
1497 ,TAX_ID
1498 ,TAX_CATEGORY_ID
1499 ,TDS_PAID
1500 ,INVOICE_DELETED
1501 ,SOURCE_INVOICE_DELETED
1502 ,PRIOR_THRESHOLD
1503 ,CHALLAN_NUMBER
1504 ,CHECK_DATE
1505 ,CHECK_DEPOSIT_DATE
1506 ,BSR_CODE
1507 ,PAYMENT_REFERENCE_ID
1508 ,TDS_INV_TAX_AMOUNT
1509 ,SUPPLIER_CM_AMOUNT
1510 ,BASE_TAX_AMOUNT
1511 ,REQUIRED_FOR_ETDS
1512 ,MIGRATED
1513 ,CREATION_DATE
1514 ,CREATED_BY
1515 ,LAST_UPDATE_DATE
1516 ,LAST_UPDATE_LOGIN
1517 ,LAST_UPDATED_BY
1518 )
1519 select
1520 JAI_AP_TDS_REPOSITORY_S.NEXTVAL,
1521 -9998,
1522 NULL,
1523 NULL,
1524 NULL,
1525 'TRANSITION',
1526 r_thhold_grp_rec.threshold_grp_id,
1527 jatit.threshold_hdr_id,
1528 NULL,
1529 NULL,
1530 G_THRESHOLD_TRANSITION,
1531 jatit.invoice_id,
1532 aia.invoice_type_lookup_code,
1533 aia.invoice_amount,
1534 aia.vendor_id,
1535 aia.vendor_site_id,
1536 aia.invoice_date,
1537 aia.gl_date,
1538 aia.gl_date,
1539 r_c_get_tax_regime_info.regime_id,
1540 r_c_get_tax_regime_info.lookup_code,
1541 aia.org_id,
1542 aia.legal_entity_id,
1543 jror.attribute_value, --ORG_TAN_NUMBER
1544 G_TDS_SECTION,
1545 nvl(jatit.actual_section_code, jatit.default_section_code),
1546 jitc.tax_rate,
1547 nvl(jatit.actual_tax_id, jatit.default_tax_id),
1548 NULL,
1549 NULL,
1550 NULL,
1551 NULL,
1552 G_N,
1553 NULL,
1554 NULL,
1555 NULL,
1556 NULL,
1557 NULL,
1558 NULL,
1559 NULL,
1560 aia.invoice_amount,
1561 G_Y,
1562 G_Y,
1563 sysdate
1564 ,fnd_global.user_id
1565 ,sysdate
1566 ,fnd_global.login_id
1567 ,fnd_global.user_id
1568 FROM ap_invoices_all aia
1569 ,ap_invoice_distributions_all aida
1570 ,jai_ap_tds_inv_taxes jatit
1571 ,JAI_AP_TDS_VENDOR_HDRS jte
1572 ,JAI_CMN_TAXES_ALL jitc
1573 ,JAI_RGM_ORG_REGNS_V jror
1574 WHERE aia.invoice_id = jatit.invoice_id
1575 AND jatit.threshold_grp_id IS NOT NULL
1576 AND jatit.threshold_grp_id = r_thhold_grp_rec.threshold_grp_id
1577 AND aia.cancelled_date IS NULL
1578 AND (EXISTS (SELECT 1
1579 FROM jai_ap_tds_prepayments
1580 WHERE invoice_id = jatit.invoice_id)
1581 OR aida.prepay_amount_remaining IS NOT NULL)
1582 AND nvl(jatit.actual_tax_id, jatit.default_tax_id) = jitc.tax_id
1583 AND aia.vendor_id = jte.vendor_id
1584 AND aia.vendor_site_id = jte.vendor_site_id
1585 AND aia.invoice_id = aida.invoice_id
1586 AND aida.match_status_flag in ('A','T')
1587 AND aia.invoice_type_lookup_code IN ('STANDARD','PREPAYMENT')
1588 AND aia.org_id = jror.organization_id
1589 AND jror.regime_code = G_TDS
1590 AND jror.registration_type = 'OTHERS'
1591 AND jror.attribute_code = 'TAN NO'
1592 AND NOT EXISTS (SELECT 1
1593 FROM jai_ap_tds_repository
1594 WHERE source_invoice_id = jatit.invoice_id
1595 AND event = G_THRESHOLD_TRANSITION
1596 )
1597 ;
1598
1599
1600 ln_surcharge_amount := 0;
1601 ln_sh_cess_amount := 0;
1602 ln_cess_amount := 0;
1603 ln_tds_amount := 0;
1604 r_threshold_tran_inv := NULL;
1605 r_tds_payment_check_id := NULL;
1606 lv_bank_branch_code := NULL;
1607 r_tax_rates := NULL;
1608 /*Fetch TDS Invoice details of the Threshold Transition Invoice*/
1609 OPEN c_threshold_tran_inv(r_thhold_grp_rec.threshold_grp_id);
1610 FETCH c_threshold_tran_inv INTO r_threshold_tran_inv;
1611 CLOSE c_threshold_tran_inv;
1612
1613 OPEN c_gl_date(r_threshold_tran_inv.invoice_id);
1614 FETCH c_gl_date INTO ld_gl_date;
1615 CLOSE c_gl_date;
1616
1617 OPEN c_tds_payment_check_id(r_threshold_tran_inv.invoice_id);
1618 FETCH c_tds_payment_check_id INTO r_tds_payment_check_id;
1619 CLOSE c_tds_payment_check_id;
1620
1621 OPEN c_tax_rates(r_threshold_tran_inv.tax_id);
1622 FETCH c_tax_rates INTO r_tax_rates;
1623 CLOSE c_tax_rates;
1624 /*Split the TDS Amount into appropriate components*/
1625 /*
1626 ln_surcharge_amount := round((r_threshold_tran_inv.tds_inv_tax_amount * r_tax_rates.surcharge_rate/r_tax_rates.tax_rate),2) ;
1627 ln_sh_cess_amount := round((r_threshold_tran_inv.tds_inv_tax_amount * r_tax_rates.sh_cess_rate/r_tax_rates.tax_rate),2);
1628 ln_cess_amount := round((r_threshold_tran_inv.tds_inv_tax_amount * r_tax_rates.cess_rate/r_tax_rates.tax_rate),2) + ln_sh_cess_amount;
1629 ln_tds_amount := r_threshold_tran_inv.tds_inv_tax_amount - NVL(ln_surcharge_amount,0) - NVL(ln_cess_amount,0) ;
1630 */
1631 /*Prorate TDS Amount of Threshold Transition invoice among the Invoices prior to Threshold*/
1632 UPDATE jai_ap_tds_repository
1633 SET --tds_check_id = r_threshold_tran_inv.tds_check_id,
1634 challan_number = r_tds_payment_check_id.challan_num,
1635 CHECK_DEPOSIT_DATE = r_tds_payment_check_id.challan_date,
1636 BSR_CODE = r_tds_payment_check_id.branch_code,
1637 /*
1638 amt_of_tds = ((tds_amount*ln_tds_amount)/r_threshold_tran_inv.tds_amount),
1639 amt_of_surcharge = ((tds_amount*ln_surcharge_amount)/r_threshold_tran_inv.tds_amount),
1640 amt_of_cess = ((tds_amount*ln_cess_amount)/r_threshold_tran_inv.tds_amount),
1641 */
1642 TDS_INV_TAX_AMOUNT = r_threshold_tran_inv.tds_inv_tax_amount * BASE_TAX_AMOUNT/r_threshold_tran_inv.base_tax_amount,
1643 TDS_ACCOUNTING_DATE = ld_gl_date /*GL Date and Invoice Date are same for TDS Invoices*/
1644 WHERE invoice_id IN (-9998, -9999)
1645 AND threshold_grp_id = r_thhold_grp_rec.threshold_grp_id
1646 ;
1647 --Remove consolidated threshold transition records
1648 DELETE jai_ap_tds_repository
1649 WHERE migrated = 'Y'
1650 AND event LIKE 'THRESHOLD TRANSITION%'
1651 AND threshold_grp_id = r_thhold_grp_rec.threshold_grp_id
1652 ;
1653
1654 END IF; --r_chk_threshold_rollback.invoice_to_tds_authority_id
1655 END IF; /*threshold grp contain threshold transition records*/
1656
1657 END LOOP; --r_thhold_grp_rec
1658
1659 ----Update payment and challan information
1660 -- if TDS invoice was paid, derive payment info from JAI_AP_TDS_INV_PAYMENTS table.
1661 UPDATE jai_ap_tds_repository jatr
1662 SET(jatr.tds_paid
1663 ,jatr.required_for_etds
1664 ,jatr.challan_number
1665 ,jatr.check_date
1666 ,jatr.check_deposit_date
1667 ,jatr.bsr_code
1668 ,jatr.payment_reference_id
1669 ) = (
1670 SELECT 'Y'
1671 ,'Y'
1672 ,jatp.challan_no
1673 ,jatp.check_date
1674 ,jatp.check_deposit_date
1675 ,jatp.bsr_code
1676 ,jatp.payment_reference_id
1677 FROM jai_ap_tds_payments jatp
1678 ,ap_invoice_payments_all aipa
1679 WHERE jatp.check_id = aipa.check_id
1680 AND aipa.invoice_id = jatr.invoice_id
1681 )
1682 WHERE jatr.migrated= 'Y'
1683 AND EXISTS(
1684 SELECT 1
1685 FROM jai_ap_tds_payments jatp
1686 ,ap_invoice_payments_all aipa
1687 WHERE jatp.check_id = aipa.check_id
1688 AND aipa.invoice_id = jatr.invoice_id
1689 );
1690
1691
1692 --Step5. Loop all vendors that have TDS supplier additional information setup.
1693 For r_tds_vendor_rec IN ( SELECT distinct jatvh.vendor_id
1694 FROM JAI_AP_TDS_VENDOR_HDRS jatvh
1695 )
1696 LOOP
1697 -- Step a. Insert 0% transactions under JAI_TDS_SECTION_ZERO_RATE_197,JAI_TDS_SECTION_ZERO_RATE_197A
1698 INSERT INTO jai_ap_tds_repository(
1699 TDS_REPOSITORY_ID
1700 ,INVOICE_ID
1701 ,INVOICE_NUMBER
1702 ,TDS_INVOICE_TYPE
1703 ,THRESHOLD_GRP_ID
1704 ,THRESHOLD_HDR_ID
1705 ,THRESHOLD_TYPE_ID
1706 ,THRESHOLD_SLAB_ID
1707 ,EVENT
1708 ,SOURCE_INVOICE_ID
1709 ,SOURCE_INVOICE_TYPE
1710 ,SOURCE_INVOICE_AMT
1711 ,SOURCE_ID
1712 ,SOURCE_SITE_ID
1713 ,SOURCE_INVOICE_DATE
1714 ,ACCOUNTING_DATE
1715 ,REGIME_ID
1716 ,REGIME_CODE
1717 ,ORG_ID
1718 ,LEGAL_ENTITY_ID
1719 ,ORG_TAN_NUMBER
1720 ,SECTION_TYPE
1721 ,SECTION_CODE
1722 ,TAX_RATE
1723 ,TAX_ID
1724 ,BASE_TAX_AMOUNT
1725 ,REQUIRED_FOR_ETDS
1726 ,MIGRATED
1727 ,CREATION_DATE
1728 ,CREATED_BY
1729 ,LAST_UPDATE_DATE
1730 ,LAST_UPDATE_LOGIN
1731 ,LAST_UPDATED_BY
1732 )
1733 SELECT JAI_AP_TDS_REPOSITORY_S.NEXTVAL
1734 ,tbl.INVOICE_ID
1735 ,tbl.INVOICE_NUMBER
1736 ,tbl.TDS_INVOICE_TYPE
1737 ,tbl.THRESHOLD_GRP_ID
1738 ,tbl.THRESHOLD_HDR_ID
1739 ,tbl.THRESHOLD_TYPE_ID
1740 ,tbl.THRESHOLD_SLAB_ID
1741 ,tbl.EVENT
1742 ,tbl.SOURCE_INVOICE_ID
1743 ,tbl.SOURCE_INVOICE_TYPE
1744 ,tbl.SOURCE_INVOICE_AMT
1745 ,tbl.SOURCE_ID
1746 ,tbl.SOURCE_SITE_ID
1747 ,tbl.SOURCE_INVOICE_DATE
1748 ,tbl.ACCOUNTING_DATE
1749 ,tbl.REGIME_ID
1750 ,tbl.REGIME_CODE
1751 ,tbl.ORG_ID
1752 ,tbl.LEGAL_ENTITY_ID
1753 ,tbl.ORG_TAN_NUMBER
1754 ,tbl.SECTION_TYPE
1755 ,tbl.SECTION_CODE
1756 ,tbl.TAX_RATE
1757 ,tbl.TAX_ID
1758 ,tbl.BASE_TAX_AMOUNT
1759 ,tbl.REQUIRED_FOR_ETDS
1760 ,tbl.MIGRATED
1761 ,sysdate
1762 ,fnd_global.user_id
1763 ,sysdate
1764 ,fnd_global.login_id
1765 ,fnd_global.user_id
1766 FROM (SELECT -9999 INVOICE_ID
1767 ,NULL INVOICE_NUMBER
1768 ,NULL TDS_INVOICE_TYPE
1769 ,nvl(jatit.THRESHOLD_GRP_ID,jatit.DEFAULT_THRESHOLD_GRP_ID) THRESHOLD_GRP_ID
1770 ,NULL THRESHOLD_HDR_ID
1771 ,NULL THRESHOLD_TYPE_ID
1772 ,NULL THRESHOLD_SLAB_ID
1773 ,NULL EVENT
1774 ,jatit.invoice_id SOURCE_INVOICE_ID
1775 ,max(aia.invoice_type_lookup_code) SOURCE_INVOICE_TYPE
1776 ,max(aia.invoice_amount) SOURCE_INVOICE_AMT
1777 ,max(aia.vendor_id) SOURCE_ID
1778 ,max(aia.vendor_site_id) SOURCE_SITE_ID
1779 ,max(aia.invoice_date) SOURCE_INVOICE_DATE
1780 ,max(aia.gl_date) ACCOUNTING_DATE
1781 ,r_c_get_tax_regime_info.regime_id REGIME_ID
1782 ,r_c_get_tax_regime_info.lookup_code REGIME_CODE
1783 ,max(aia.ORG_ID) ORG_ID
1784 ,max(aia.legal_entity_id) LEGAL_ENTITY_ID
1785 ,max(jror.attribute_value) ORG_TAN_NUMBER
1786 ,max(jatit.section_type) SECTION_TYPE
1787 ,max(nvl(jatit.actual_section_code, jatit.default_section_code)) SECTION_CODE
1788 ,0 TAX_RATE
1789 ,max(nvl(jatit.actual_tax_id, jatit.default_tax_id)) TAX_ID
1790 ,max(aia.invoice_amount) BASE_TAX_AMOUNT
1791 ,G_Y REQUIRED_FOR_ETDS
1792 ,G_Y MIGRATED
1793 FROM ap_invoices_all aia,
1794 ap_invoice_distributions_all aida,
1795 jai_ap_tds_inv_taxes jatit,
1796 JAI_AP_TDS_VENDOR_HDRS jte,
1797 JAI_CMN_TAXES_ALL jitc,
1798 JAI_RGM_ORG_REGNS_V jror
1799 WHERE aia.invoice_id = jatit.invoice_id
1800 AND jatit.threshold_grp_id IS NOT NULL
1801 AND aia.cancelled_date IS NULL
1802 AND nvl(jatit.actual_tax_id, jatit.default_tax_id) = jitc.tax_id
1803 AND aia.vendor_id = jte.vendor_id
1804 AND aia.vendor_site_id = jte.vendor_site_id
1805 AND aia.invoice_id = aida.invoice_id
1806 AND aida.match_status_flag in ('A','T')
1807 AND aia.invoice_type_lookup_code IN ('STANDARD','PREPAYMENT')
1808 and not exists (select 1 from jai_ap_tds_thhold_trxs where invoice_id = jatit.invoice_id)
1809 and jitc.tax_rate = 0
1810 and jitc.vendor_id = r_tds_vendor_rec.vendor_id
1811 AND aia.org_id = jror.organization_id
1812 AND jror.regime_code = G_TDS
1813 AND jror.registration_type = 'OTHERS'
1814 AND jror.attribute_code = 'TAN NO'
1815 and upper(jitc.section_code) in (select lookup_code
1816 from fnd_lookup_values_vl
1817 where lookup_type in ('JAI_TDS_SECTION_ZERO_RATE_197', 'JAI_TDS_SECTION_ZERO_RATE_197A'))
1818 AND NOT EXISTS (SELECT 1
1819 FROM jai_ap_tds_repository
1820 WHERE source_invoice_id = jatit.invoice_id
1821 AND invoice_id = -9999
1822 )
1823 group by jatit.invoice_id
1824 ,nvl(jatit.THRESHOLD_GRP_ID, jatit.DEFAULT_THRESHOLD_GRP_ID)
1825 ,nvl(jatit.actual_section_code, jatit.default_section_code)
1826 ,nvl(jatit.actual_tax_id, jatit.default_tax_id)
1827 )tbl;
1828
1829 --Step b. Insert 0% transactions Vendor Classification being Transporter or Software
1830 --Insert souce invoice information into repository, leave TDS invoice info as NULL
1831 --Populate tax_id and tax_rate, tax info will be update later
1832 INSERT INTO jai_ap_tds_repository (
1833 TDS_REPOSITORY_ID
1834 ,INVOICE_ID
1835 ,INVOICE_NUMBER
1836 ,TDS_INVOICE_TYPE
1837 ,THRESHOLD_GRP_ID
1838 ,THRESHOLD_HDR_ID
1839 ,THRESHOLD_TYPE_ID
1840 ,THRESHOLD_SLAB_ID
1841 ,EVENT
1842 ,SOURCE_INVOICE_ID
1843 ,SOURCE_INVOICE_TYPE
1844 ,SOURCE_INVOICE_AMT
1845 ,SOURCE_ID
1846 ,SOURCE_SITE_ID
1847 ,SOURCE_INVOICE_DATE
1848 ,ACCOUNTING_DATE
1849 ,REGIME_ID
1850 ,REGIME_CODE
1851 ,ORG_ID
1852 ,LEGAL_ENTITY_ID
1853 ,ORG_TAN_NUMBER
1854 ,SECTION_TYPE
1855 ,SECTION_CODE
1856 ,TAX_RATE
1857 ,TAX_ID
1858 ,BASE_TAX_AMOUNT
1859 ,REQUIRED_FOR_ETDS
1860 ,MIGRATED
1861 ,CREATION_DATE
1862 ,CREATED_BY
1863 ,LAST_UPDATE_DATE
1864 ,LAST_UPDATE_LOGIN
1865 ,LAST_UPDATED_BY
1866 )
1867 select JAI_AP_TDS_REPOSITORY_S.NEXTVAL
1868 ,INVOICE_ID
1869 ,INVOICE_NUMBER
1870 ,TDS_INVOICE_TYPE
1871 ,THRESHOLD_GRP_ID
1872 ,THRESHOLD_HDR_ID
1873 ,THRESHOLD_TYPE_ID
1874 ,THRESHOLD_SLAB_ID
1875 ,EVENT
1876 ,SOURCE_INVOICE_ID
1877 ,SOURCE_INVOICE_TYPE
1878 ,SOURCE_INVOICE_AMT
1879 ,SOURCE_ID
1880 ,SOURCE_SITE_ID
1881 ,SOURCE_INVOICE_DATE
1882 ,ACCOUNTING_DATE
1883 ,REGIME_ID
1884 ,REGIME_CODE
1885 ,ORG_ID
1886 ,LEGAL_ENTITY_ID
1887 ,ORG_TAN_NUMBER
1888 ,SECTION_TYPE
1889 ,SECTION_CODE
1890 ,TAX_RATE
1891 ,TAX_ID
1892 ,BASE_TAX_AMOUNT
1893 ,REQUIRED_FOR_ETDS
1894 ,MIGRATED
1895 ,sysdate
1896 ,fnd_global.user_id
1897 ,sysdate
1898 ,fnd_global.login_id
1899 ,fnd_global.user_id
1900 FROM (select distinct
1901 9999 INVOICE_ID
1902 ,NULL INVOICE_NUMBER
1903 ,NULL TDS_INVOICE_TYPE
1904 ,NULL THRESHOLD_GRP_ID
1905 ,NULL THRESHOLD_HDR_ID
1906 ,NULL THRESHOLD_TYPE_ID
1907 ,NULL THRESHOLD_SLAB_ID
1908 ,NULL EVENT
1909 ,base_invoices.invoice_id SOURCE_INVOICE_ID
1910 ,base_invoices.invoice_type_lookup_code SOURCE_INVOICE_TYPE
1911 ,base_invoices.invoice_amount SOURCE_INVOICE_AMT
1912 ,base_invoices.vendor_id SOURCE_ID
1913 ,base_invoices.vendor_site_id SOURCE_SITE_ID
1914 ,base_invoices.invoice_date SOURCE_INVOICE_DATE
1915 ,base_invoices.gl_date ACCOUNTING_DATE
1916 ,r_c_get_tax_regime_info.regime_id REGIME_ID
1917 ,r_c_get_tax_regime_info.lookup_code REGIME_CODE
1918 ,base_invoices.org_id ORG_ID
1919 ,base_invoices.legal_entity_id LEGAL_ENTITY_ID
1920 ,jror.attribute_value ORG_TAN_NUMBER
1921 ,G_TDS_SECTION SECTION_TYPE
1922 ,decode(jte.tds_vendor_classification,'Transporter','SEC. 194(C)','Software'
1923 , decode(povs.country, 'IN','SEC. 194(J)','SEC. 195')) SECTION_CODE
1924 ,0 TAX_RATE
1925 ,a.tax_id TAX_ID
1926 ,base_invoices.invoice_amount BASE_TAX_AMOUNT
1927 ,G_Y REQUIRED_FOR_ETDS
1928 ,G_Y MIGRATED
1929 from jai_cmn_taxes_all a
1930 , ap_invoices_all base_invoices
1931 , po_vendor_sites_all povs
1932 , ap_invoice_distributions_all aida
1933 , jai_ap_tds_vendor_hdrs jte
1934 , JAI_RGM_ORG_REGNS_V jror
1935 where jte.vendor_id = base_invoices.vendor_id
1936 and povs.vendor_id = jte.vendor_id
1937 and povs.vendor_site_id = jte.vendor_site_id
1938 and base_invoices.cancelled_Date is null
1939 and a.tax_rate = 0
1940 and a.tax_id = aida.global_attribute1
1941 and jte.vendor_site_id = base_invoices.vendor_site_id
1942 and jte.tds_vendor_classification in ('Transporter','Software')
1943 and aida.global_attribute_category = 'JA.IN.APXINWKB.DISTRIBUTIONS'
1944 and aida.invoice_id = base_invoices.invoice_id
1945 and aida.match_status_flag in ('A','T') /*10408793 - Unvalidated Invoices must not be picked*/
1946 and base_invoices.invoice_type_lookup_code IN ('STANDARD','PREPAYMENT')
1947 and not exists (select '1' from jai_ap_tds_thhold_trxs jattt where jattt.invoice_id = base_invoices.invoice_id) /*Bug 10408793 - TDS should not be deducted*/
1948 and not exists (select '1'
1949 from jai_ap_tds_repository jatr
1950 where jatr.source_invoice_id = base_invoices.invoice_id
1951 and jatr.migrated= G_Y)
1952 and jte.vendor_id = r_tds_vendor_rec.vendor_id
1953 AND base_invoices.org_id = jror.organization_id
1954 AND jror.regime_code = G_TDS
1955 AND jror.registration_type = 'OTHERS'
1956 AND jror.attribute_code = 'TAN NO'
1957 union all
1958 select distinct
1959 -9999
1960 ,NULL
1961 ,NULL
1962 ,NULL
1963 ,NULL
1964 ,NULL
1965 ,NULL
1966 ,NULL
1967 ,base_invoices.invoice_id
1968 ,base_invoices.invoice_type_lookup_code
1969 ,base_invoices.invoice_amount
1970 ,base_invoices.vendor_id
1971 ,base_invoices.vendor_site_id
1972 ,base_invoices.invoice_date
1973 ,base_invoices.gl_date
1974 ,r_c_get_tax_regime_info.regime_id --TDS regiem ID
1975 ,r_c_get_tax_regime_info.lookup_code ---lv_regime_code
1976 ,base_invoices.org_id
1977 ,base_invoices.legal_entity_id
1978 ,jror.attribute_value
1979 ,G_TDS_SECTION
1980 ,decode(jte.tds_vendor_classification,'Transporter','SEC. 194(C)','Software', decode(povs.country, 'IN','SEC. 194(J)','SEC. 195'))
1981 ,0
1982 ,-99
1983 ,base_invoices.invoice_amount
1984 ,G_Y
1985 ,G_Y
1986 from ap_invoices_all base_invoices
1987 ,ap_invoice_distributions_all aida
1988 ,jai_ap_tds_vendor_hdrs jte
1989 ,po_vendor_sites_all povs
1990 ,JAI_RGM_ORG_REGNS_V jror
1991 where jte.vendor_id = base_invoices.vendor_id
1992 and jte.vendor_site_id = base_invoices.vendor_site_id
1993 and jte.tds_vendor_classification in ('Transporter','Software')
1994 and aida.global_attribute1 is NULL
1995 and povs.vendor_id = jte.vendor_id
1996 and povs.vendor_site_id = jte.vendor_site_id
1997 and base_invoices.cancelled_Date is null
1998 and (aida.global_attribute_category = 'JA.IN.APXINWKB.DISTRIBUTIONS' or aida.global_attribute_category is NULL)
1999 and aida.match_status_flag in ('A','T') /*10408793 - Unvalidated Invoices must not be picked*/
2000 and aida.invoice_id = base_invoices.invoice_id
2001 AND base_invoices.org_id = jror.organization_id
2002 AND jror.regime_code = G_TDS
2003 AND jror.registration_type = 'OTHERS'
2004 AND jror.attribute_code = 'TAN NO'
2005 and base_invoices.invoice_type_lookup_code IN ('STANDARD','PREPAYMENT')
2006 and not exists (select '1' from jai_ap_tds_thhold_trxs jattt where jattt.invoice_id = base_invoices.invoice_id)
2007 and not exists (select '1'
2008 from jai_ap_tds_repository jatr
2009 where jatr.source_invoice_id = base_invoices.invoice_id
2010 and jatr.migrated= G_Y)
2011 and jte.vendor_id = r_tds_vendor_rec.vendor_id
2012 ) tbl
2013 ;
2014
2015 END LOOP;
2016
2017 --Setp 6. Derive Tax_category Id from old TDS tax_id. Then populate repository lines.
2018 --Loop all records in repository
2019 For r_tds_rpsty_rec IN ( SELECT jatr.tds_repository_id
2020 ,jatr.invoice_id
2021 ,jatr.tax_id
2022 ,jatr.tax_rate
2023 ,jatr.tds_inv_tax_amount
2024 FROM jai_ap_tds_repository jatr
2025 WHERE jatr.tax_id IS NOT NULL
2026 AND NVL(jatr.tax_rate,0) <> 0
2027 AND jatr.migrated = G_Y
2028 )
2029 LOOP
2030
2031 ln_tax_category_id := map_tds_tax_to_ctg(p_tax_id => r_tds_rpsty_rec.tax_id);
2032
2033 Update jai_ap_tds_repository jatr
2034 SET jatr.tax_category_id = ln_tax_category_id
2035 WHERE jatr.tds_repository_id = r_tds_rpsty_rec.tds_repository_id;
2036
2037
2038 IF r_tds_rpsty_rec.INVOICE_ID IS NOT NULL THEN
2039 --If TDS invoice created, populate repository lines for each tax id
2040 --(assume each tax type only exists one tax code)
2041 FOR r_c_get_taxes_info IN ( SELECT jcta.tax_id
2042 ,jcta.tax_name
2043 ,jcta.tax_rate
2044 ,jcta.tax_type
2045 FROM JAI_CMN_TAXES_ALL jcta
2046 ,JAI_CMN_TAX_CTG_LINES jctcl
2047 WHERE jctcl.tax_category_id = ln_tax_category_id
2048 AND jcta.tax_id = jctcl.tax_id
2049 AND jcta.tax_type IN (G_TDS, G_TDS_EDUCATION_CESS, G_TDS_EDUCATION_CESS, G_TDS_SURCHARGE)
2050 )
2051 LOOP
2052 jai_cmn_utils_pkg.WRITE_FND_LOG(G_LEVEL_STATEMENT, G_MODULE_NAME||l_api_name, 'tax_type: ' ||r_c_get_taxes_info.tax_type || ' tax_id: ' || r_c_get_taxes_info.tax_id);
2053
2054 r_jai_ap_tds_repo_lines := NULL;
2055 r_jai_ap_tds_repo_lines.invoice_id := r_tds_rpsty_rec.INVOICE_ID;
2056 r_jai_ap_tds_repo_lines.tax_id := r_c_get_taxes_info.tax_id;
2057 r_jai_ap_tds_repo_lines.tax_name := r_c_get_taxes_info.tax_name;
2058 r_jai_ap_tds_repo_lines.tax_type := r_c_get_taxes_info.tax_type;
2059 r_jai_ap_tds_repo_lines.tax_amount := round(r_tds_rpsty_rec.tds_inv_tax_amount * r_c_get_taxes_info.tax_rate / r_tds_rpsty_rec.tax_rate, 2);
2060 r_jai_ap_tds_repo_lines.CREATION_DATE := sysdate;
2061 r_jai_ap_tds_repo_lines.CREATED_BY := fnd_global.user_id;
2062 r_jai_ap_tds_repo_lines.LAST_UPDATE_DATE := sysdate;
2063 r_jai_ap_tds_repo_lines.LAST_UPDATE_LOGIN := fnd_global.login_id;
2064 r_jai_ap_tds_repo_lines.LAST_UPDATED_BY := fnd_global.user_id;
2065
2066 insert_jai_ap_tds_repo_lines(r_jai_ap_tds_repo_lines);
2067 END LOOP;
2068
2069 END IF;
2070
2071 END LOOP;
2072
2073 jai_cmn_utils_pkg.WRITE_FND_LOG(G_LEVEL_PROCEDURE, G_MODULE_NAME || '.' || l_api_name ||'.END', G_PKG_NAME || ': '|| l_api_name || '()-');
2074 EXCEPTION
2075 WHEN OTHERS THEN
2076 jai_cmn_utils_pkg.WRITE_FND_LOG(G_LEVEL_PROCEDURE
2077 ,G_MODULE_NAME||l_api_name||'. Other_Exception'
2078 ,SQLCODE || ':' || SQLERRM);
2079 RAISE;
2080 END process_etds_migration;
2081
2082
2083
2084 /*---------------------------------------------------------------------
2085 --Procedure to insert one line into jai_ap_tds_repository table.
2086 ---------------------------------------------------------------------*/
2087 PROCEDURE insert_jai_tds_repository(pr_jai_ap_tds_repository IN jai_ap_tds_repository%rowtype)
2088 IS
2089
2090
2091 CURSOR c_get_invoice_line_info(p_invoice_id NUMBER) IS
2092 SELECT REGEXP_SUBSTR(aila.description, '/.+/.+/(.+)/.+$',1,1,'i',1) tax_id --Update for bug16898321 in case tax rate contains '.'
2093 ,REGEXP_SUBSTR(aila.description, '/.+/(.+)/.+/.+$',1,1,'i',1) tax_type
2094 ,aila.amount
2095 ,aila.line_number
2096 FROM ap_invoice_lines_all aila
2097 WHERE aila.invoice_id = p_invoice_id
2098 ORDER by aila.line_number
2099 ;
2100
2101
2102 CURSOR c_get_taxes_info(p_tax_type VARCHAR2
2103 ,p_tax_id NUMBER
2104 ) IS
2105 SELECT jcta.tax_id
2106 ,jcta.tax_type
2107 ,jcta.tax_name
2108 FROM jai_cmn_taxes_all jcta
2109 WHERE jcta.tax_type = p_tax_type
2110 AND jcta.tax_id = p_tax_id
2111 ;
2112
2113 r_c_get_taxes_info c_get_taxes_info%rowtype;
2114 r_jai_ap_tds_repo_lines jai_ap_tds_repo_lines%rowtype;
2115 l_api_name CONSTANT VARCHAR2(50) := 'insert_jai_tds_repository()';
2116 BEGIN
2117 jai_cmn_utils_pkg.WRITE_FND_LOG(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_api_name||'.BEGIN', G_PKG_NAME || ': '||l_api_name||'()+');
2118
2119
2120 INSERT INTO jai_ap_tds_repository
2121 (TDS_REPOSITORY_ID
2122 ,INVOICE_ID
2123 ,INVOICE_NUMBER
2124 ,TDS_AUTHORITY_ID
2125 ,TDS_AUTHORITY_SITE_ID
2126 ,TDS_INVOICE_TYPE
2127 ,THRESHOLD_GRP_ID
2128 ,THRESHOLD_HDR_ID
2129 ,THRESHOLD_TYPE_ID
2130 ,THRESHOLD_SLAB_ID
2131 ,EVENT
2132 ,SOURCE_INVOICE_ID
2136 ,SOURCE_SITE_ID
2133 ,SOURCE_INVOICE_TYPE
2134 ,SOURCE_INVOICE_AMT
2135 ,SOURCE_ID
2137 ,SOURCE_INVOICE_DATE
2138 ,ACCOUNTING_DATE
2139 ,APPLIED_FROM_INVOICE_ID
2140 ,APPLIED_FROM_INVOICE_TYPE
2141 ,APPLIED_FROM_INVOICE_AMT
2142 ,APPLIED_FROM_SUPPLIER_ID
2143 ,APPLIED_FROM_SUPPLIER_SITE_ID
2144 ,APPLIED_FROM_INVOICE_DATE
2145 ,APPLIED_FROM_ACCTG_DATE
2146 ,FROM_SLAB_ID
2147 ,APPLIED_TO_INVOICE_ID
2148 ,APPLIED_TO_INVOICE_TYPE
2149 ,APPLIED_TO_INVOICE_AMT
2150 ,APPLIED_TO_SUPPLIER_ID
2151 ,APPLIED_TO_SUPPLIER_SITE_ID
2152 ,APPLIED_TO_INVOICE_DATE
2153 ,APPLIED_TO_ACCTG_DATE
2154 ,APPLIED_AMOUNT
2155 ,TO_SLAB_ID
2156 ,UNAPPLIED_AMOUNT
2157 ,TDS_ACCOUNTING_DATE
2158 ,REGIME_ID
2159 ,REGIME_CODE
2160 ,ORG_ID
2161 ,LEGAL_ENTITY_ID
2162 ,ORG_TAN_NUMBER
2163 ,SECTION_TYPE
2164 ,SECTION_CODE
2165 ,TAX_RATE
2166 ,TAX_ID
2167 ,TAX_CATEGORY_ID
2168 ,TDS_PAID
2169 ,INVOICE_DELETED
2170 ,SOURCE_INVOICE_DELETED
2171 ,PRIOR_THRESHOLD
2172 ,CHALLAN_NUMBER
2173 ,CHECK_DATE
2174 ,CHECK_DEPOSIT_DATE
2175 ,BSR_CODE
2176 ,PAYMENT_REFERENCE_ID
2177 ,TDS_INV_TAX_AMOUNT
2178 ,SUPPLIER_CM_AMOUNT
2179 ,BASE_TAX_AMOUNT
2180 ,REQUIRED_FOR_ETDS
2181 ,MIGRATED
2182 ,CREATION_DATE
2183 ,CREATED_BY
2184 ,LAST_UPDATE_DATE
2185 ,LAST_UPDATE_LOGIN
2186 ,LAST_UPDATED_BY
2187 )
2188 VALUES
2189 (pr_jai_ap_tds_repository.TDS_REPOSITORY_ID
2190 ,pr_jai_ap_tds_repository.INVOICE_ID
2191 ,pr_jai_ap_tds_repository.INVOICE_NUMBER
2192 ,pr_jai_ap_tds_repository.TDS_AUTHORITY_ID
2193 ,pr_jai_ap_tds_repository.TDS_AUTHORITY_SITE_ID
2194 ,pr_jai_ap_tds_repository.TDS_INVOICE_TYPE
2195 ,pr_jai_ap_tds_repository.THRESHOLD_GRP_ID
2196 ,pr_jai_ap_tds_repository.THRESHOLD_HDR_ID
2197 ,pr_jai_ap_tds_repository.THRESHOLD_TYPE_ID
2198 ,pr_jai_ap_tds_repository.THRESHOLD_SLAB_ID
2199 ,pr_jai_ap_tds_repository.EVENT
2200 ,pr_jai_ap_tds_repository.SOURCE_INVOICE_ID
2201 ,pr_jai_ap_tds_repository.SOURCE_INVOICE_TYPE
2202 ,pr_jai_ap_tds_repository.SOURCE_INVOICE_AMT
2203 ,pr_jai_ap_tds_repository.SOURCE_ID
2204 ,pr_jai_ap_tds_repository.SOURCE_SITE_ID
2205 ,pr_jai_ap_tds_repository.SOURCE_INVOICE_DATE
2206 ,pr_jai_ap_tds_repository.ACCOUNTING_DATE
2207 ,pr_jai_ap_tds_repository.APPLIED_FROM_INVOICE_ID
2208 ,pr_jai_ap_tds_repository.APPLIED_FROM_INVOICE_TYPE
2209 ,pr_jai_ap_tds_repository.APPLIED_FROM_INVOICE_AMT
2210 ,pr_jai_ap_tds_repository.APPLIED_FROM_SUPPLIER_ID
2211 ,pr_jai_ap_tds_repository.APPLIED_FROM_SUPPLIER_SITE_ID
2212 ,pr_jai_ap_tds_repository.APPLIED_FROM_INVOICE_DATE
2213 ,pr_jai_ap_tds_repository.APPLIED_FROM_ACCTG_DATE
2214 ,pr_jai_ap_tds_repository.FROM_SLAB_ID
2215 ,pr_jai_ap_tds_repository.APPLIED_TO_INVOICE_ID
2216 ,pr_jai_ap_tds_repository.APPLIED_TO_INVOICE_TYPE
2217 ,pr_jai_ap_tds_repository.APPLIED_TO_INVOICE_AMT
2218 ,pr_jai_ap_tds_repository.APPLIED_TO_SUPPLIER_ID
2219 ,pr_jai_ap_tds_repository.APPLIED_TO_SUPPLIER_SITE_ID
2220 ,pr_jai_ap_tds_repository.APPLIED_TO_INVOICE_DATE
2221 ,pr_jai_ap_tds_repository.APPLIED_TO_ACCTG_DATE
2222 ,pr_jai_ap_tds_repository.APPLIED_AMOUNT
2223 ,pr_jai_ap_tds_repository.TO_SLAB_ID
2224 ,pr_jai_ap_tds_repository.UNAPPLIED_AMOUNT
2225 ,pr_jai_ap_tds_repository.TDS_ACCOUNTING_DATE
2226 ,pr_jai_ap_tds_repository.REGIME_ID
2227 ,pr_jai_ap_tds_repository.REGIME_CODE
2228 ,pr_jai_ap_tds_repository.ORG_ID
2229 ,pr_jai_ap_tds_repository.LEGAL_ENTITY_ID
2230 ,pr_jai_ap_tds_repository.ORG_TAN_NUMBER
2231 ,pr_jai_ap_tds_repository.SECTION_TYPE
2232 ,pr_jai_ap_tds_repository.SECTION_CODE
2233 ,pr_jai_ap_tds_repository.TAX_RATE
2234 ,pr_jai_ap_tds_repository.TAX_ID
2235 ,pr_jai_ap_tds_repository.TAX_CATEGORY_ID
2236 ,pr_jai_ap_tds_repository.TDS_PAID
2237 ,pr_jai_ap_tds_repository.INVOICE_DELETED
2238 ,pr_jai_ap_tds_repository.SOURCE_INVOICE_DELETED
2239 ,pr_jai_ap_tds_repository.PRIOR_THRESHOLD
2240 ,pr_jai_ap_tds_repository.CHALLAN_NUMBER
2241 ,pr_jai_ap_tds_repository.CHECK_DATE
2242 ,pr_jai_ap_tds_repository.CHECK_DEPOSIT_DATE
2243 ,pr_jai_ap_tds_repository.BSR_CODE
2244 ,pr_jai_ap_tds_repository.PAYMENT_REFERENCE_ID
2245 ,pr_jai_ap_tds_repository.TDS_INV_TAX_AMOUNT
2246 ,pr_jai_ap_tds_repository.SUPPLIER_CM_AMOUNT
2247 ,pr_jai_ap_tds_repository.BASE_TAX_AMOUNT
2248 ,pr_jai_ap_tds_repository.REQUIRED_FOR_ETDS
2249 ,pr_jai_ap_tds_repository.MIGRATED
2250 ,pr_jai_ap_tds_repository.CREATION_DATE
2251 ,pr_jai_ap_tds_repository.CREATED_BY
2252 ,pr_jai_ap_tds_repository.LAST_UPDATE_DATE
2253 ,pr_jai_ap_tds_repository.LAST_UPDATE_LOGIN
2254 ,pr_jai_ap_tds_repository.LAST_UPDATED_BY
2255 );
2256
2257 jai_cmn_utils_pkg.WRITE_FND_LOG(G_LEVEL_STATEMENT, G_MODULE_NAME||l_api_name, 'Process eTDS repository line population');
2258
2259 IF pr_jai_ap_tds_repository.INVOICE_ID IS NOT NULL THEN
2260 --If TDS invoice created, populate repository lines for each tax id
2261 --(assume each tax type only exists one tax code)
2262 FOR rec_tax IN c_get_invoice_line_info(pr_jai_ap_tds_repository.INVOICE_ID)
2263 LOOP
2264 jai_cmn_utils_pkg.WRITE_FND_LOG(G_LEVEL_STATEMENT, G_MODULE_NAME||l_api_name, 'tax_type ' ||rec_tax.tax_type || ' tax_id: ' || rec_tax.tax_id);
2265
2266 OPEN c_get_taxes_info(p_tax_type => rec_tax.tax_type
2267 ,p_tax_id => rec_tax.tax_id
2268 );
2269 FETCH c_get_taxes_info INTO r_c_get_taxes_info;
2270 CLOSE c_get_taxes_info;
2271
2272 r_jai_ap_tds_repo_lines := NULL;
2273 jai_cmn_utils_pkg.WRITE_FND_LOG(G_LEVEL_STATEMENT, G_MODULE_NAME||l_api_name, '@1');
2274 r_jai_ap_tds_repo_lines.invoice_id := pr_jai_ap_tds_repository.INVOICE_ID;
2275 r_jai_ap_tds_repo_lines.tax_id := r_c_get_taxes_info.tax_id;
2276 r_jai_ap_tds_repo_lines.tax_name := r_c_get_taxes_info.tax_name;
2277 r_jai_ap_tds_repo_lines.tax_type := r_c_get_taxes_info.tax_type;
2278 r_jai_ap_tds_repo_lines.tax_amount := rec_tax.amount;
2279 r_jai_ap_tds_repo_lines.CREATION_DATE := pr_jai_ap_tds_repository.CREATION_DATE;
2280 r_jai_ap_tds_repo_lines.CREATED_BY := pr_jai_ap_tds_repository.CREATED_BY;
2281 r_jai_ap_tds_repo_lines.LAST_UPDATE_DATE := pr_jai_ap_tds_repository.LAST_UPDATE_DATE;
2282 r_jai_ap_tds_repo_lines.LAST_UPDATE_LOGIN := pr_jai_ap_tds_repository.LAST_UPDATE_LOGIN;
2283 r_jai_ap_tds_repo_lines.LAST_UPDATED_BY := pr_jai_ap_tds_repository.LAST_UPDATED_BY;
2284
2285 insert_jai_ap_tds_repo_lines(r_jai_ap_tds_repo_lines);
2286 END LOOP;
2287
2288 END IF;
2289
2290 jai_cmn_utils_pkg.WRITE_FND_LOG(G_LEVEL_PROCEDURE, G_MODULE_NAME || '.' || l_api_name ||'.END', G_PKG_NAME || ': '|| l_api_name || '()-');
2291 EXCEPTION
2292 WHEN OTHERS THEN
2293 jai_cmn_utils_pkg.WRITE_FND_LOG(G_LEVEL_PROCEDURE
2294 ,G_MODULE_NAME||l_api_name||'. Other_Exception'
2295 ,SQLCODE || ':' || SQLERRM);
2296 RAISE;
2297 END insert_jai_tds_repository;
2298
2299
2300 /*---------------------------------------------------------------------
2301 --Procedure to insert one line into jai_ap_tds_repo_lines table.
2302 ---------------------------------------------------------------------*/
2303 PROCEDURE insert_jai_ap_tds_repo_lines(pr_jai_ap_tds_repo_lines IN jai_ap_tds_repo_lines%rowtype)
2304 IS
2305 l_api_name CONSTANT VARCHAR2(50) := 'insert_jai_ap_tds_repo_lines()';
2306 BEGIN
2307 jai_cmn_utils_pkg.WRITE_FND_LOG(G_LEVEL_PROCEDURE, G_MODULE_NAME || '.' || l_api_name ||'.BEGIN', G_PKG_NAME || ': '|| l_api_name || '()+');
2308
2309 INSERT INTO jai_ap_tds_repo_lines
2310 (INVOICE_ID
2311 ,TAX_ID
2312 ,TAX_NAME
2313 ,TAX_TYPE
2314 ,TAX_AMOUNT
2315 ,SURCHARGE_ROLLBACK
2316 ,CREATION_DATE
2317 ,CREATED_BY
2318 ,LAST_UPDATE_DATE
2319 ,LAST_UPDATE_LOGIN
2320 ,LAST_UPDATED_BY
2321 )
2322 VALUES
2323 (pr_jai_ap_tds_repo_lines.INVOICE_ID
2324 ,pr_jai_ap_tds_repo_lines.TAX_ID
2325 ,pr_jai_ap_tds_repo_lines.TAX_NAME
2326 ,pr_jai_ap_tds_repo_lines.TAX_TYPE
2327 ,pr_jai_ap_tds_repo_lines.TAX_AMOUNT
2328 ,pr_jai_ap_tds_repo_lines.SURCHARGE_ROLLBACK
2329 ,pr_jai_ap_tds_repo_lines.CREATION_DATE
2330 ,pr_jai_ap_tds_repo_lines.CREATED_BY
2331 ,pr_jai_ap_tds_repo_lines.LAST_UPDATE_DATE
2332 ,pr_jai_ap_tds_repo_lines.LAST_UPDATE_LOGIN
2333 ,pr_jai_ap_tds_repo_lines.LAST_UPDATED_BY
2334 );
2335
2336 jai_cmn_utils_pkg.WRITE_FND_LOG(G_LEVEL_PROCEDURE, G_MODULE_NAME || '.' || l_api_name ||'.END', G_PKG_NAME || ': '|| l_api_name || '()-');
2337 EXCEPTION
2338 WHEN OTHERS THEN
2339 jai_cmn_utils_pkg.WRITE_FND_LOG(G_LEVEL_PROCEDURE
2340 ,G_MODULE_NAME||l_api_name||'. Other_Exception'
2341 ,SQLCODE || ':' || SQLERRM);
2342 RAISE;
2343 END insert_jai_ap_tds_repo_lines;
2344
2345 end jai_ap_tds_pop_rpst_pkg;