[Home] [Help]
PACKAGE BODY: APPS.JAI_RGM_THHOLD_PROC_PKG
Source
1 package body jai_rgm_thhold_proc_pkg AS
2 /*$Header: jai_rgm_thld_prc.plb 120.4.12000000.1 2007/07/24 06:56:10 rallamse noship $ */
3
4 /* ----------------------------------------------------------------------------
5
6 CHANGE HISTORY:
7 -------------------------------------------------------------------------------
8 S.No Date Author and Details
9 -------------------------------------------------------------------------------
10 1. 01/02/2007 Created by Bgowrava for forward porting bug#5631784. Version#120.0.
11 This was Directly created from 11i version 115.9
12
13 2. 16/04/2007 Bgowrava for Forwrad porting Bug#5989740, 11i bug#5907436, version 120.1
14 ENH : HANDLING SECONDARY AND HIGHER EDUCATION CESS
15 additional cess of 1% on all taxes to be levied to fund secondary education and higher
16 education .
17 Changes - -
18 Changes is done to capture Secondary and Higher cess .
19 Most of places , we added secondary cess with normal cess
20
21 3 18/06/2007 bduvarag for the bug#6127213, File version of 120.3
22 FP of 6084533
23 4. 28/06/2007 CSahoo for BUG#6156619, File Version 120.4
24 added a AND condition in the cursor c_get_cust_typ_lkup_code.
25 ---------------------------------------------------------------------------- */
26
27 /*----------------------------------------- PRIVATE MEMBERS DECLRATION -------------------------------------*/
28
29 /** Package level variables used in debug package*/
30 lv_object_name jai_cmn_debug_contexts.log_context%type default 'TCS.JAI_RGM_THHOLD_PROC_PKG';
31 lv_member_name jai_cmn_debug_contexts.log_context%type;
32 lv_context jai_cmn_debug_contexts.log_context%type;
33
34 procedure set_debug_context
35 is
36
37 begin
38 lv_context := rtrim(lv_object_name || '.'||lv_member_name,'.');
39 end set_debug_context;
40 /*------------------------------------------END - PRIVATE MEMBERS DECLRATION --------------------------------*/
41 procedure generate_consolidated_doc
42 (p_threshold_id in jai_rgm_thresholds.threshold_id%type
43 ,p_transaction_id in jai_rgm_refs_all.transaction_id%type
44 ,p_org_id in jai_rgm_refs_all.org_id%type
45 ,p_process_flag out nocopy varchar2
46 ,p_process_message out nocopy varchar2
47 )
48 is
49
50 /*-------------------------------DECLARE SECTION OF GENERATE_CONSOLIDATED_DOC ---------------------------*/
51
52 ln_reg_id number;
53 lr_dtl_record jai_rgm_threshold_dtls%rowtype;
54 ln_threshold_slab_id jai_rgm_thresholds.threshold_slab_id%type;
55 ln_threshold_tax_cat_id jai_ap_tds_thhold_taxes.tax_category_id%type;
56
57 ln_surcharge_doc_amt number;
58 ln_surcharge_doc_cess_amt number;
59 ln_surcharge_line_no number;
60 ln_surcharge_cess_line_no number;
61 ln_surcharge_tax_id jai_rgm_taxes.tax_id%type;
62 ln_surcharge_cess_tax_id jai_rgm_taxes.tax_id%type;
63 ln_surcharge_tax_rate jai_rgm_taxes.tax_rate%type;
64 ln_surcharge_cess_tax_rate jai_rgm_taxes.tax_rate%type;
65
66 ln_trx_ref_id jai_rgm_refs_all.trx_ref_id%type;
67 ln_currency_code jai_rgm_taxes.currency_code%type;
68
69 ln_user_id fnd_user.user_id%type := fnd_global.user_id;
70 ln_login_id fnd_logins.login_id%type := fnd_global.login_id;
71
72 -- start, Bgowrava for Forward porting Bug#5989740
73 ln_surcharge_sh_cess_tax_id jai_rgm_taxes.tax_id%type;
74 ln_surcharge_sh_cess_tax_rate jai_rgm_taxes.tax_rate%type;
75 ln_surcharge_sh_cess_line_no number;
76 ln_surcharge_doc_sh_cess_amt number;
77 -- end Bgowrava for Forward porting Bug#5989740
78
79
80 cursor c_get_thhold_info
81 is
82 select hdr.regime_id
83 ,hdr.threshold_slab_id
84 ,dtls.threshold_dtl_id
85 ,dtls.threshold_base_amt
86 ,dtls.item_classification
87 ,nvl(dtls.manual_surcharge_amt,0) manual_surcharge_amt
88 ,nvl(dtls.system_surcharge_amt,0) system_surcharge_amt
89 ,nvl(dtls.system_surcharge_cess_amt,0) system_surcharge_cess_amt
90 ,nvl(dtls.system_surcharge_sh_cess_amt,0) system_surcharge_sh_cess_amt --Bgowrava for forward porting bug#5989740
91 from jai_rgm_threshold_dtls dtls
92 ,jai_rgm_thresholds hdr
93 where hdr.threshold_id = p_threshold_id
94 and dtls.threshold_id = hdr.threshold_id ;
95
96 cursor c_curr_code (cp_org_id jai_rgm_refs_all.org_id%type)
97 is
98 select currency_code
99 from gl_sets_of_books gsb
100 , hr_operating_units hou
101 where gsb.set_of_books_id = hou.set_of_books_id
102 and hou.organization_id = cp_org_id;
103
104 cursor c_get_ref_dtls (cp_transaction_id jai_rgm_refs_all.transaction_id%type default null
105 ,cp_trx_ref_id jai_rgm_refs_all.trx_ref_id%type default null
106 )
107 is
108 select *
109 from jai_rgm_refs_all
110 where ( (cp_transaction_id is not null and transaction_id = cp_transaction_id)
111 or (cp_trx_ref_id is not null and trx_ref_id = cp_trx_ref_id)
112 );
113
114 cursor c_get_taxes_for_last_doc ( cp_org_tan_no jai_rgm_refs_all.org_tan_no%type
115 , cp_party_id jai_rgm_refs_all.party_id%type
116 , cp_fin_year jai_rgm_refs_all.fin_year%type
117 , cp_item_classification jai_rgm_refs_all.item_classification%type
118 )
119 is
120 select tax_id
121 ,tax_type
122 ,tax_rate
123 from jai_rgm_taxes
124 where trx_ref_id = ( select max(trx_ref_id)
125 from jai_rgm_refs_all
126 where source_document_id = jai_constants.tcs_surcharge_id
127 and org_tan_no = cp_org_tan_no
128 and fin_year = cp_fin_year
129 and party_id = cp_party_id
130 and item_classification = cp_item_classification
131 and party_type = jai_constants.party_type_customer
132 );
133
134
135 r_ref_dtls c_get_ref_dtls%rowtype;
136 ln_transaction_id JAI_RGM_REFS_ALL.TRANSACTION_ID%TYPE;
137
138 /*
139 || Generate the transaction_id from the sequence
140 */
141 CURSOR cur_get_transaction_id
142 IS
143 SELECT
144 jai_rgm_refs_all_s2.nextval
145 FROM
146 dual;
147
148
149 /*-------------------------------BEGIN LOCAL METHOD CALCULATE_TCS_DOC_AMOUNT -----------------------------*/
150
151 procedure calculate_tcs_doc_amount
152 ( p_threshold_tax_cat_id in jai_ap_tds_thhold_taxes.tax_category_id%type
153 , p_tcs_amt in number
154 , p_manual_surcharge_amt in jai_rgm_threshold_dtls.manual_surcharge_amt%type
155 , p_system_surcharge_amt in jai_rgm_threshold_dtls.system_surcharge_amt%type
156 , p_system_surcharge_cess_amt in jai_rgm_threshold_dtls.system_surcharge_cess_amt%type
157 , p_system_surcharge_sh_cess_amt in jai_rgm_threshold_dtls.system_surcharge_sh_cess_amt%type --Bgowrava for forward porting bug#5989740
158 , p_surcharge_doc_amt out nocopy number
159 , p_surcharge_doc_cess_amt out nocopy number
160 , p_surcharge_doc_sh_cess_amt out nocopy number --Bgowrava for forward porting bug#5989740
161 , p_process_flag out nocopy varchar2
162 , p_process_message out nocopy varchar2
163 )
164 is
165
166 ln_tax_rate JAI_CMN_TAXES_ALL.tax_rate%type;
167 ln_rounding_factor JAI_CMN_TAXES_ALL.rounding_factor%type;
168 ln_tax_id JAI_CMN_TAXES_ALL.tax_id%type;
169
170 cursor c_get_surcharge_taxes (cp_tax_type JAI_CMN_TAXES_ALL.tax_type%type
171 ,cp_line_no number default null
172 )
173 is
174 select tax.tax_rate
175 , tax.rounding_factor
176 , tax.tax_id
177 , cat.line_no
178 from JAI_CMN_TAXES_ALL tax
179 ,JAI_CMN_TAX_CTG_LINES cat
180 where cat.tax_id = tax.tax_id
181 and cat.tax_category_id = p_threshold_tax_cat_id
182 and tax.tax_type = cp_tax_type
183 and cat.precedence_1 = nvl(cp_line_no, cat.precedence_1); -- This will check that for CESS precedence should be of surcharge
184
185 begin
186
187 p_process_flag := jai_constants.successful;
188 p_process_message := null;
189
190 /* jai_cmn_debug_contexts_pkg.print ( ln_reg_id,'Begin CALCULATE_TCS_DOC_AMOUNT');
191 jai_cmn_debug_contexts_pkg.print ( ln_reg_id
192 ,'p_threshold_tax_cat_id ='||p_threshold_tax_cat_id
193 ||'p_tcs_amt ='||p_tcs_amt
194 ||'p_manual_surcharge_amt ='||p_manual_surcharge_amt
195 ||'p_system_surcharge_amt ='||p_system_surcharge_amt
196 ||'p_system_surcharge_cess_amt ='||p_system_surcharge_cess_amt
197 );*/ --commented by bgowrava for bug#5631784
198
199 p_surcharge_doc_amt := 0;
200 p_surcharge_doc_cess_amt := 0;
201
202 if p_threshold_tax_cat_id = -1 then
203
204 /*
205 No slab is applicable and hence no surcharge needs to be paid.
206 All system generated surcharge / cess needs to be reversed
207 */
208
209 p_surcharge_doc_amt := -1 * p_system_surcharge_amt;
210 p_surcharge_doc_cess_amt := -1 * p_system_surcharge_cess_amt;
211 return;
212
213 end if;
214
215 /*
216 Get details regarding surcharge type of tax attached to the category.
217 */
218 /* jai_cmn_debug_contexts_pkg.print (ln_reg_id, 'Open/Fetch/Close C_GET_SURCHARGE_TAXES'); */ --commented by bgowrava for bug#5631784
219 open c_get_surcharge_taxes (cp_tax_type => jai_constants.tax_type_tcs_surcharge );
220 fetch c_get_surcharge_taxes into ln_tax_rate
221 ,ln_rounding_factor
222 ,ln_surcharge_tax_id
223 ,ln_surcharge_line_no;
224 close c_get_surcharge_taxes ;
225
226 if ln_tax_rate is null then
227 p_process_flag := jai_constants.expected_error;
228 p_process_message := 'Cannot find surcharge type of taxes for tax_id ='||ln_tax_id||', tax_category_id='||p_threshold_tax_cat_id;
229 return;
230 end if;
231
232
233 ln_rounding_factor := nvl(ln_rounding_factor,0);
234 ln_surcharge_tax_rate := ln_tax_rate;
235
236 /* jai_cmn_debug_contexts_pkg.print (ln_reg_id
237 ,'ln_surcharge_line_no='||ln_surcharge_line_no||'
238 , ln_surcharge_tax_rate='||ln_surcharge_tax_rate||'
239 , ln_rounding_factor='||ln_rounding_factor
240 ); */ --commented by bgowrava for bug#5631784
241
242 /*
243 Calculate surcharge amount by using following equation
244 ----------------------------------------------------------------
245 Surcharge = TCS Amount * Tax Rate (Surcharge type of tax)
246 ----------------------------------------------------------------
247 */
248
249 p_surcharge_doc_amt := round( p_tcs_amt * (ln_tax_rate/100), ln_rounding_factor);
250
251 /*
252 Document amount may be different from surcharge amount because of either system deducted surcharge
253 or manually deducated surcharge. Document amount is calculated using following equation
254
255 ------------------------------------------------------------------------------------------------------------------
256 Document Amount = (SurchargeAmount [What needs to be paid]) - (System+Manual Surcharge) [What is already deducted])
257 ------------------------------------------------------------------------------------------------------------------
258
259 In case if document amount is less than surcharge amount we should reverse deduction amounts
260
261 Assumption: We can only reverse system surcharge amount and we should never reverse manual surcharge
262 */
263
264 p_surcharge_doc_amt := p_surcharge_doc_amt - (p_system_surcharge_amt + p_manual_surcharge_amt);
265
266 /* jai_cmn_debug_contexts_pkg.print (ln_reg_id, 'p_surcharge_doc_amt='||p_surcharge_doc_amt);*/ --commented by bgowrava for bug#5631784
267
268 if p_surcharge_doc_amt < 0 then
269 /* Document amount is negative means we need to initiate reversal (only for system surcharge amount). */
270
271 if (p_system_surcharge_amt + p_surcharge_doc_amt ) >= 0 then
272 /* System surcharge has potential for reversal */
273 p_surcharge_doc_amt := p_system_surcharge_amt + p_surcharge_doc_amt ;
274 else
275 /* System surcharge is not sufficient to reverse the complete document amount hence reverse only whatever is system surcharge amount */
276 p_surcharge_doc_amt := -1 * p_system_surcharge_amt ;
277 end if;
278 end if;
279
280 /*
281 Get details regarding surcharge cess type of taxes
282 */
283 ln_tax_rate := null;
284 ln_rounding_factor := null;
285
286 /*
287 || Get tax details for CESS which is defined for Surcharge
288 */
289 /* jai_cmn_debug_contexts_pkg.print (ln_reg_id, 'Open/Fetch/Close C_GET_SURCHARGE_TAXES'); */ --commented by bgowrava for bug#5631784
290 open c_get_surcharge_taxes (cp_tax_type => jai_constants.tax_type_tcs_cess
291 ,cp_line_no => ln_surcharge_line_no
292 );
293 fetch c_get_surcharge_taxes into ln_tax_rate
294 ,ln_rounding_factor
295 ,ln_surcharge_cess_tax_id
296 ,ln_surcharge_cess_line_no;
297 close c_get_surcharge_taxes ;
298
299 if ln_tax_rate is null then
300 ln_tax_rate := 0;
301 /* jai_cmn_debug_contexts_pkg.print (ln_reg_id, 'Surcharge Cess is not available, continuing with zero cess');*/ --commented by bgowrava for bug#5631784
302 end if;
303
304 ln_rounding_factor := nvl(ln_rounding_factor,0);
305 ln_surcharge_cess_tax_rate := ln_tax_rate;
306
307 /* jai_cmn_debug_contexts_pkg.print (ln_reg_id, 'ln_surcharge_cess_tax_rate='||ln_surcharge_cess_tax_rate||', ln_rounding_factor='||ln_rounding_factor);*/ --commented by bgowrava for bug#5631784
308
309 /*
310 Calculate document cess amount by using following equation
311 ------------------------------------------------------------------------------
312 Cess on Doc.Amount = Document Amount * Tax Rate (Surcharge Cess type of tax)
313 ------------------------------------------------------------------------------
314 */
315 p_surcharge_doc_cess_amt := round( p_surcharge_doc_amt * (ln_tax_rate/100), ln_rounding_factor);
316
317 -- start, Bgowrava for forward porting bug#5989740
318
319 ln_tax_rate := null;
320 ln_rounding_factor := null;
321
322 open c_get_surcharge_taxes (cp_tax_type => jai_constants.tax_type_sh_tcs_edu_cess
323 ,cp_line_no => ln_surcharge_line_no
324 );
325 fetch c_get_surcharge_taxes into ln_tax_rate
326 ,ln_rounding_factor
327 ,ln_surcharge_sh_cess_tax_id
328 ,ln_surcharge_sh_cess_line_no;
329 close c_get_surcharge_taxes ;
330
331 if ln_tax_rate is null then
332 ln_tax_rate := 0;
333 end if;
334 ln_rounding_factor := nvl(ln_rounding_factor,0);
335 ln_surcharge_sh_cess_tax_rate := ln_tax_rate;
336
337 p_surcharge_doc_sh_cess_amt := round( p_surcharge_doc_amt * (ln_tax_rate/100), ln_rounding_factor);
338
339 -- end Bgowrava for forward porting bug#5989740
340
341
342
343 /* jai_cmn_debug_contexts_pkg.print (ln_reg_id, 'p_surcharge_doc_amt='||p_surcharge_doc_amt);
344 jai_cmn_debug_contexts_pkg.print (ln_reg_id, 'p_surcharge_doc_cess_amt='||p_surcharge_doc_cess_amt); */--commented by bgowrava for bug#5631784
345
346 end calculate_tcs_doc_amount;
347
348 /*-------------------------------END LOCAL METHOD CALCULATE_TCS_DOC_AMOUNT -----------------------------*/
349
350 /*------------------------------------ BEGIN GENERATE_CONSOLIDATED_DOC ------------------------------------*/
351 begin
352
353 /*----------------------------------------------------------------------
354 Aim: Calculate Surcharge Amount and Generate a Consolidated Document
355 ------------------------------------------------------------------------*/
356
357 /** Initialize process variables */
358 p_process_flag := jai_constants.successful;
359 p_process_message := null;
360
361 /** Register procedure for debuging */
362 lv_member_name := 'GENERATE_CONSOLIDATED_DOC';
363 set_debug_context;
364 /*jai_cmn_debug_contexts_pkg.register ( pv_context => lv_context
365 , pn_reg_id => ln_reg_id
366 ); */
367
368 /* jai_cmn_debug_contexts_pkg.print ( ln_reg_id
369 ,'GENERATE_CONSOLIDATED_DOC Call Parameters:'
370 ||'p_threshold_id='||p_threshold_id
371 ||'p_org_id='||p_org_id
372 ,jai_cmn_debug_contexts_pkg.summary
373 ); */ --commented by bgowrava for bug#5631784
374
375 /** For TCS, Surcharge document needs to be generated for each of the item classification */
376 for r_thhold_info in c_get_thhold_info
377 loop
378 if r_thhold_info.threshold_slab_id is null then
379 /**
380 THRESHOLD_SLAB_ID is null means, no surcharge slab is applicable. Hence any amount in system surcharge
381 must be reverted by generating a credit memo.
382 */
383 /*
384 Setting threshold_tax_cat_id to a value which never exists, so calculate_tcs_doc_amount (see below) will be able to process
385 reversal for system surcharge / cess amount
386 */
387 ln_threshold_tax_cat_id := -1;
388 end if;
389
390 /**
391 if this is the first iteration, get tax category for the slab . In other way if we already have tax_category_id (say -1) don't
392 even make a call to get_threshold_tax_cat_id API
393 */
394 jai_cmn_debug_contexts_pkg.print ( ln_reg_id, 'Before: JAI_RGM_THHOLD_PROC_PKG.GET_THRESHOLD_TAX_CAT_ID');
395
396 if ln_threshold_tax_cat_id is null then
397 /** Control will never come here if THRESHOLD_SLAB_ID is null (i.e. if no slab is applicable) */
398 jai_rgm_thhold_proc_pkg.get_threshold_tax_cat_id
399 ( p_threshold_slab_id => r_thhold_info.threshold_slab_id
400 , p_org_id => p_org_id
401 , p_threshold_tax_cat_id => ln_threshold_tax_cat_id
402 , p_process_flag => p_process_flag
403 , p_process_message => p_process_message
404 );
405
406 end if;
407
408 /* jai_cmn_debug_contexts_pkg.print ( ln_reg_id, 'After: JAI_RGM_THHOLD_PROC_PKG.GET_THRESHOLD_TAX_CAT_ID');
409 jai_cmn_debug_contexts_pkg.print ( ln_reg_id, 'p_process_flag='||p_process_flag||', p_process_message='||p_process_message); */ --commented by bgowrava for bug#5631784
410
411 if p_process_flag <> jai_constants.successful then
412 return;
413 end if;
414
415 /* jai_cmn_debug_contexts_pkg.print ( ln_reg_id, 'Before: CALCULATE_TCS_DOC_AMOUNT');*/ --commented by bgowrava for bug#5631784
416 calculate_tcs_doc_amount ( p_threshold_tax_cat_id => ln_threshold_tax_cat_id
417 , p_tcs_amt => r_thhold_info.threshold_base_amt
418 , p_manual_surcharge_amt => r_thhold_info.manual_surcharge_amt
419 , p_system_surcharge_amt => r_thhold_info.system_surcharge_amt
420 , p_system_surcharge_cess_amt => r_thhold_info.system_surcharge_cess_amt
421 , p_system_surcharge_sh_cess_amt => r_thhold_info.system_surcharge_sh_cess_amt --Bgowrava for forward porting bug#5989740
422 , p_surcharge_doc_amt => ln_surcharge_doc_amt
423 , p_surcharge_doc_cess_amt => ln_surcharge_doc_cess_amt
424 , p_surcharge_doc_sh_cess_amt => ln_surcharge_doc_sh_cess_amt --Bgowrava for forward porting bug#5989740
425 , p_process_flag => p_process_flag
426 , p_process_message => p_process_message
427 );
428 /* jai_cmn_debug_contexts_pkg.print ( ln_reg_id, 'After: CALCULATE_TCS_DOC_AMOUNT');
429 jai_cmn_debug_contexts_pkg.print ( ln_reg_id, 'p_process_flag='||p_process_flag||', p_process_message='||p_process_message); */ --commented by bgowrava for bug#5631784
430
431 if p_process_flag <> jai_constants.successful then
432 return;
433 end if;
434
435 /*
436 -- Check if document amount is not zero. If it is zero, no need to generate document and hence no need create repository references
437 */
438
439 if (ln_surcharge_doc_amt + ln_surcharge_doc_cess_amt + ln_surcharge_doc_sh_cess_amt) = 0 then -- Added ln_surcharge_doc_sh_cess_amt, Bgowrava for forward porting bug#5989740
440 goto update_and_return;
441 end if;
442
443 /*
444 If threshold is down we will not be able to get tax category and hence tax details, so we should look at the last consolidated document and
445 to find out the tax structure
446 */
447
448 open c_get_ref_dtls (cp_transaction_id => p_transaction_id);
449 fetch c_get_ref_dtls into r_ref_dtls;
450 close c_get_ref_dtls;
451
452
453 if r_thhold_info.threshold_slab_id is null then
454 /* jai_cmn_debug_contexts_pkg.print ( ln_reg_id
455 , 'For loop of C_GET_TAXES_FOR_LAST_DOC'||chr(10)||
456 'cp_org_tan_no= '||r_ref_dtls.org_tan_no||chr(10)||
457 'cp_party_id = '||r_ref_dtls.party_id ||chr(10)||
458 'cp_fin_year = '||r_ref_dtls.fin_year ||chr(10)||
459 'cp_item_classification= '||r_ref_dtls.item_classification||chr(10)||
460 'jai_constants.tcs_surcharge_id='||jai_constants.tcs_surcharge_id||chr(10)||
461 'jai_constants.party_type_customer='||jai_constants.party_type_customer
462 ); */ --commented by bgowrava for bug#5631784
463
464 for r_taxes_for_last_doc in c_get_taxes_for_last_doc
465 ( cp_org_tan_no => r_ref_dtls.org_tan_no
466 ,cp_party_id => r_ref_dtls.party_id
467 ,cp_fin_year => r_ref_dtls.fin_year
468 ,cp_item_classification => r_ref_dtls.item_classification
469 )
470 loop
471 /* jai_cmn_debug_contexts_pkg.print ( ln_reg_id
472 , 'Inside For: r_taxes_for_last_doc.tax_type='||r_taxes_for_last_doc.tax_type
473 ); */ --commented by bgowrava for bug#5631784
474 if r_taxes_for_last_doc.tax_type = jai_constants.tax_type_tcs_surcharge then
475 ln_surcharge_tax_id := r_taxes_for_last_doc.tax_id;
476 ln_surcharge_tax_rate := r_taxes_for_last_doc.tax_rate;
477 elsif r_taxes_for_last_doc.tax_type = jai_constants.tax_type_tcs_cess then
478 ln_surcharge_cess_tax_id := r_taxes_for_last_doc.tax_id;
479 ln_surcharge_cess_tax_rate:= r_taxes_for_last_doc.tax_rate;
480 -- start Bgowrava for forward porting bug#5989740
481 elsif r_taxes_for_last_doc.tax_type = jai_constants.tax_type_sh_tcs_edu_cess then
482 ln_surcharge_sh_cess_tax_id := r_taxes_for_last_doc.tax_id;
483 ln_surcharge_sh_cess_tax_rate:= r_taxes_for_last_doc.tax_rate;
484 -- end Bgowrava for forward porting bug#5989740
485 end if;
486 end loop;
487
488 if ln_surcharge_tax_id is null or ln_surcharge_cess_tax_id is null or ln_surcharge_sh_cess_tax_id is null then --Bgowrava for forward porting bug#5989740
489 p_process_flag := jai_constants.expected_error;
490 p_process_message := 'Unable to find tax structure to generate a consolidated document, tax_id(SURCH)='||ln_surcharge_tax_id||', tax_id(CESS)='||ln_surcharge_cess_tax_id;
491 return;
492 end if;
493 end if; /* r_thhold_info.threshold_slab_id is null */
494
495 /*
496 ||Get the sequence generated unique key for the transaction
497 */
498 OPEN cur_get_transaction_id ;
499 FETCH cur_get_transaction_id INTO ln_transaction_id ;
500 CLOSE cur_get_transaction_id ;
501
502 /*
503 || Insert repository reference for the document to be generated
504 */
505
506 /* jai_cmn_debug_contexts_pkg.print ( ln_reg_id, 'Before: JAI_AR_TCS_REP_PKG.INSERT_REPOSITORY_REFERENCES',jai_cmn_debug_contexts_pkg.summary);*/ --commented by bgowrava for bug#5631784
507 jai_ar_tcs_rep_pkg.insert_repository_references
508 ( p_regime_id => r_thhold_info.regime_id
509 , p_transaction_id => ln_transaction_id
510 , p_source_ref_document_id => r_ref_dtls.source_ref_document_id
511 , p_source_ref_document_type => r_ref_dtls.source_ref_document_type
512 , p_parent_transaction_id => r_ref_dtls.parent_transaction_id
513 , p_org_tan_no => r_ref_dtls.org_tan_no
514 , p_document_id => jai_constants.tcs_surcharge_id
515 , p_document_type => jai_constants.tcs_event_surcharge
516 , p_document_line_id => jai_constants.tcs_surcharge_id
517 , p_document_date => r_ref_dtls.source_document_date
518 , p_table_name => jai_constants.jai_rgm_thresholds
519 , p_line_amount => (ln_surcharge_doc_amt + ln_surcharge_doc_cess_amt + ln_surcharge_doc_sh_cess_amt) --Bgowrava for forward porting bug#5989740
520 , p_document_amount => (ln_surcharge_doc_amt + ln_surcharge_doc_cess_amt + ln_surcharge_doc_sh_cess_amt) --Bgowrava for forward porting bug#5989740
521 , p_org_id => r_ref_dtls.org_id
522 , p_organization_id => r_ref_dtls.organization_id
523 , p_party_id => r_ref_dtls.party_id
524 , p_party_site_id => r_ref_dtls.party_site_id
525 , p_item_classification => r_thhold_info.item_classification
526 , p_trx_ref_id => ln_trx_ref_id
527 , p_process_flag => p_process_flag
528 , p_process_message => p_process_message
529 );
530 /* jai_cmn_debug_contexts_pkg.print ( ln_reg_id, 'After: JAI_AR_TCS_REP_PKG.INSERT_REPOSITORY_REFERENCES',jai_cmn_debug_contexts_pkg.summary);
531 jai_cmn_debug_contexts_pkg.print ( ln_reg_id, 'p_process_flag='||p_process_flag||', p_process_message='||p_process_message,jai_cmn_debug_contexts_pkg.summary);*/ --commented by bgowrava for bug#5631784
532
533 if p_process_flag <> jai_constants.successful then
534 return;
535 end if;
536
537 open c_curr_code (cp_org_id => r_ref_dtls.org_id);
538 fetch c_curr_code into ln_currency_code;
539 close c_curr_code ;
540
541 /*
542 || Insert repository taxes
543 */
544 /*
545 Insert surcharge type of tax
546 */
547
548 /* jai_cmn_debug_contexts_pkg.print ( ln_reg_id, 'Before Surcharge Tax: JAI_AR_TCS_REP_PKG.INSERT_REPOSITORY_TAXES',jai_cmn_debug_contexts_pkg.summary);*/ --commented by bgowrava for bug#5631784
549 jai_ar_tcs_rep_pkg.insert_repository_taxes
550 (
551 p_trx_ref_id => ln_trx_ref_id
552 , p_tax_id => ln_surcharge_tax_id
553 , p_tax_type => jai_constants.tax_type_tcs_surcharge
554 , p_tax_rate => ln_surcharge_tax_rate
555 , p_tax_amount => ln_surcharge_doc_amt
556 , p_func_tax_amount => ln_surcharge_doc_amt /* Functional currency is same as trx currency */
557 , p_currency_code => ln_currency_code
558 , p_process_flag => p_process_flag
559 , p_process_message => p_process_message
560 );
561 /* jai_cmn_debug_contexts_pkg.print ( ln_reg_id, 'After Surcharge Tax: JAI_AR_TCS_REP_PKG.INSERT_REPOSITORY_TAXES',jai_cmn_debug_contexts_pkg.summary);
562 jai_cmn_debug_contexts_pkg.print ( ln_reg_id, 'p_process_flag='||p_process_flag||', p_process_message='||p_process_message,jai_cmn_debug_contexts_pkg.summary);*/ --commented by bgowrava for bug#5631784
563
564 if p_process_flag <> jai_constants.successful then
565 return;
566 end if;
567
568 /* jai_cmn_debug_contexts_pkg.print ( ln_reg_id, 'Before Surcharge Cess : JAI_AR_TCS_REP_PKG.INSERT_REPOSITORY_TAXES',jai_cmn_debug_contexts_pkg.summary);*/ --commented by bgowrava for bug#5631784
569 /* Insert surcharge cess type of tax */
570 jai_ar_tcs_rep_pkg.insert_repository_taxes
571 (
572 p_trx_ref_id => ln_trx_ref_id
573 , p_tax_id => ln_surcharge_cess_tax_id
574 , p_tax_type => jai_constants.tax_type_tcs_cess
575 , p_tax_rate => ln_surcharge_cess_tax_rate
576 , p_tax_amount => ln_surcharge_doc_cess_amt
577 , p_func_tax_amount => ln_surcharge_doc_cess_amt /* Functional currency is same as trx currency */
578 , p_currency_code => ln_currency_code
579 , p_process_flag => p_process_flag
580 , p_process_message => p_process_message
581 );
582 /* jai_cmn_debug_contexts_pkg.print ( ln_reg_id, 'After Surcharge Cess : JAI_AR_TCS_REP_PKG.INSERT_REPOSITORY_TAXES',jai_cmn_debug_contexts_pkg.summary);
583 jai_cmn_debug_contexts_pkg.print ( ln_reg_id, 'p_process_flag='||p_process_flag||', p_process_message='||p_process_message,jai_cmn_debug_contexts_pkg.summary);*/ --commented by bgowrava for bug#5631784
584
585 if p_process_flag <> jai_constants.successful then
586 return;
587 end if;
588
589 -- start Bgowrava for forward porting bug#5989740
590
591 jai_cmn_debug_contexts_pkg.print ( ln_reg_id, 'Before Secondary and higher Surcharge Cess : JAI_AR_TCS_REP_PKG.INSERT_REPOSITORY_TAXES',jai_cmn_debug_contexts_pkg.summary);
592 /* Insert surcharge cess type of tax */
593 jai_ar_tcs_rep_pkg.insert_repository_taxes
594 (
595 p_trx_ref_id => ln_trx_ref_id
596 , p_tax_id => ln_surcharge_cess_tax_id
597 , p_tax_type => jai_constants.tax_type_sh_tcs_edu_cess
598 , p_tax_rate => ln_surcharge_cess_tax_rate
599 , p_tax_amount => ln_surcharge_doc_sh_cess_amt
600 , p_func_tax_amount => ln_surcharge_doc_sh_cess_amt /* Functional currency is same as trx currency */
601 , p_currency_code => ln_currency_code
602 , p_process_flag => p_process_flag
603 , p_process_message => p_process_message
604 );
605 jai_cmn_debug_contexts_pkg.print ( ln_reg_id, 'After Surcharge Cess : JAI_AR_TCS_REP_PKG.INSERT_REPOSITORY_TAXES',jai_cmn_debug_contexts_pkg.summary);
606 jai_cmn_debug_contexts_pkg.print ( ln_reg_id, 'p_process_flag='||p_process_flag||', p_process_message='||p_process_message,jai_cmn_debug_contexts_pkg.summary);
607
608 if p_process_flag <> jai_constants.successful then
609 return;
610 end if;
611
612 -- end Bgowrava for forward porting bug#5989740
613
614 /** Generate document */
615
616 /* jai_cmn_debug_contexts_pkg.print ( ln_reg_id, 'Before : JAI_AR_TCS_REP_PKG.GENERATE_DOCUMENT',jai_cmn_debug_contexts_pkg.summary);*/ --commented by bgowrava for bug#5631784
617
618 /*
619 || Get Repository Reference for consolidated document record inserted above
620 */
621 open c_get_ref_dtls (cp_trx_ref_id => ln_trx_ref_id) ;
622 fetch c_get_ref_dtls into r_ref_dtls;
623 close c_get_ref_dtls;
624
625 jai_ar_tcs_rep_pkg.generate_document
626 ( p_rgm_ref => r_ref_dtls
627 , p_total_tax_amt => (ln_surcharge_doc_amt + ln_surcharge_doc_cess_amt + ln_surcharge_doc_sh_cess_amt ) --Bgowrava for forward porting bug#5989740
628 , p_process_flag => p_process_flag
629 , p_process_message => p_process_message
630 );
631 /* jai_cmn_debug_contexts_pkg.print ( ln_reg_id, 'After : JAI_AR_TCS_REP_PKG.GENERATE_DOCUMENT',jai_cmn_debug_contexts_pkg.summary);
632 jai_cmn_debug_contexts_pkg.print ( ln_reg_id, 'p_process_flag='||p_process_flag||', p_process_message='||p_process_message,jai_cmn_debug_contexts_pkg.summary);*/ --commented by bgowrava for bug#5631784
633
634 if p_process_flag <> jai_constants.successful then
635 return;
636 end if;
637
638
639 /*
640 -- This must be the last statement in the loop as update should happen only if all the above processing is completed successfully
641 */
642 <<update_and_return>>
643 /** Update System surcharge/cess amouts */
644 update jai_rgm_threshold_dtls
645 set system_surcharge_amt = system_surcharge_amt + ln_surcharge_doc_amt
646 , system_surcharge_cess_amt = system_surcharge_cess_amt + ln_surcharge_doc_cess_amt
647 , system_surcharge_sh_cess_amt = system_surcharge_sh_cess_amt + ln_surcharge_doc_sh_cess_amt --Bgowrava for forward porting bug#5989740
648 , last_update_date = sysdate
649 , last_updated_by = ln_user_id
650 , last_update_login = ln_login_id
651 where threshold_dtl_id = r_thhold_info.threshold_dtl_id;
652
653
654 end loop; /** r_thhold_info */
655
656 /** Deregister procedure and return*/
657 <<deregister_and_return>>
658 /* jai_cmn_debug_contexts_pkg.deregister (pn_reg_id => ln_reg_id); */ --commented by bgowrava for bug#5631784
659 return;
660 exception
661 when others then
662 p_process_flag := jai_constants.unexpected_error;
663 p_process_message := lv_context||'->'||sqlerrm;
664 /* jai_cmn_debug_contexts_pkg.print(ln_reg_id,lv_context||'->'||sqlerrm,jai_cmn_debug_contexts_pkg.summary);
665 jai_cmn_debug_contexts_pkg.print_stack;*/ -- */ --commented by bgowrava for bug#5631784
666
667 end generate_consolidated_doc ;
668
669 /*------------------------------------------------------------------------------------------------------------*/
670
671 /*---------------------------------------------PUBLIC SECTION-------------------------------------------------*/
672 /**
673 get_threshold_slab_id - returns identifier for current threshold slab as out parameter
674 IN
675 p_regime_id - A valid regime_id from jai_rgm_thresholds.
676 p_org_tan_no - Organiztion TAN as defined in the regime setup
677 p_organization_id - Inventory organization defined in the regime setup
678 p_party_type - Party type. Can be either CUSTOMER or VENDOR. Currently only CUSTOMER is valid.
679 p_party_id - Party identifier.
680 p_fin_year - Financial year
681 p_org_id - Optional parameter. If fin_year is not given, operating unit is used to derive the fin_year
682 p_source_trx_date - Optional parameter. If fin_year is not given, transaction date is used to derive the fin_year
683 OUT
684 p_threshold_slab_id - Current threshold slab identifier
685 p_process_flag - Flag indicates the process status, can be either
686 Successful (SS)
687 Expected Error (EE)
688 Unexpected Error (UE)
689 p_process_message - Message to be passed to caller of the api. It can be null in case of p_process_flag = 'SS'
690 */
691 /*------------------------------------------------------------------------------------------------------------*/
692 procedure get_threshold_slab_id
693 (
694 p_regime_id in jai_rgm_thresholds.regime_id%type
695 , p_org_tan_no in JAI_RGM_REGISTRATIONS.attribute_value%type default null
696 , p_organization_id in hr_organization_units.organization_id%type default null
697 , p_party_type in jai_rgm_thresholds.party_type%type
698 , p_party_id in jai_rgm_thresholds.party_id%type
699 , p_fin_year in jai_rgm_thresholds.fin_year%type default null
700 , p_org_id in jai_ap_tds_thhold_taxes.operating_unit_id%type default null
701 , p_source_trx_date in date default null
702 , p_called_from in varchar2 default null
703 , p_threshold_slab_id out nocopy jai_rgm_thresholds.threshold_slab_id%type
704 , p_process_flag out nocopy varchar2
705 , p_process_message out nocopy varchar2
706 )
707 is
708 ln_reg_id number;
709 ln_org_tan_no JAI_RGM_REGISTRATIONS.attribute_value%type;
710 ln_fin_year jai_rgm_thresholds.fin_year%type;
711 lv_customer_type_lkup_code jai_ap_tds_thhold_hdrs.customer_type_lookup_code%type;
712
713 /** cursor will fetch org_tan_no from regime setup */
714 cursor c_get_rgm_attribute ( cp_regime_id JAI_RGM_DEFINITIONS.regime_id%type
715 , cp_attribute_type_code JAI_RGM_REGISTRATIONS.attribute_type_code%type
716 , cp_attribute_code JAI_RGM_REGISTRATIONS.attribute_code%type
717 , cp_organization_id jai_rgm_parties.organization_id%type
718 )
719 is
720 select
721 attribute_value org_tan_no
722 from
723 JAI_RGM_ORG_REGNS_V rgm_attr_v
724 where
725 rgm_attr_v.regime_id = cp_regime_id
726 and rgm_attr_v.attribute_code = cp_attribute_code
727 and rgm_attr_v.attribute_type_code = cp_attribute_type_code
728 and rgm_attr_v.organization_id = cp_organization_id;
729
730 /**
731 Following cursor will derrive threshold_slab_id for a given combination of
732 fin_year, org_tan_no, party_type and party_id form the threshold setup
733 */
734 cursor c_get_threshold_slab ( cp_fin_year jai_rgm_thresholds.fin_year%type
735 , cp_org_tan_no jai_rgm_thresholds.org_tan_no%type
736 , cp_party_type jai_rgm_thresholds.party_type%type
737 , cp_party_id jai_rgm_thresholds.party_id%type
738 , cp_customer_type_lkup_code jai_ap_tds_thhold_hdrs.customer_type_lookup_code%type
739 )
740 is
741 select
742 thslbs.threshold_slab_id
743 from
744 jai_ap_tds_thhold_slabs thslbs
745 ,jai_ap_tds_thhold_types thtyps
746 ,jai_ap_tds_thhold_hdrs thhdrs
747 ,jai_rgm_thresholds rgmths
748 where
749 thslbs.threshold_type_id = thtyps.threshold_type_id
750 and thtyps.threshold_hdr_id = thhdrs.threshold_hdr_id
751 and thhdrs.regime_id = rgmths.regime_id
752 and thtyps.threshold_type = jai_constants.thhold_typ_cumulative
753 and rgmths.fin_year = cp_fin_year
754 and rgmths.party_id = cp_party_id
755 and rgmths.org_tan_no = cp_org_tan_no
756 and rgmths.party_type = cp_party_type
757 and thhdrs.customer_type_lookup_code = cp_customer_type_lkup_code
758 and trunc(p_source_trx_date) between thtyps.from_date
759 and nvl(thtyps.to_date, trunc(p_source_trx_date))
760 and rgmths.total_threshold_amt between thslbs.from_amount
761 and nvl(thslbs.to_amount,rgmths.total_threshold_amt);
762
763 cursor c_get_thhold_hdr_slab ( cp_fin_year jai_rgm_thresholds.fin_year%type
764 , cp_org_tan_no jai_rgm_thresholds.org_tan_no%type
765 , cp_party_type jai_rgm_thresholds.party_type%type
766 , cp_party_id jai_rgm_thresholds.party_id%type
767 )
768 is
769 select threshold_slab_id
770 from jai_rgm_thresholds
771 where fin_year = cp_fin_year
772 and org_tan_no = cp_org_tan_no
773 and party_type = cp_party_type
774 and party_id = cp_party_id
775 and regime_id = p_regime_id;
776
777 cursor c_get_cust_typ_lkup_code
778 is
779 select tcs_customer_type
780 from JAI_CMN_CUS_ADDRESSES
781 where customer_id = p_party_id
782 AND tcs_customer_type IS NOT NULL; --added the AND condition for bug#6156619
783
784
785
786 begin
787 /** Initialize process variables */
788 p_process_flag := jai_constants.successful ;--'SS';
789 p_process_message := null;
790 p_threshold_slab_id := null;
791
792
793 /** Register procedure for debuging */
794 lv_member_name := 'GET_THRESHOLD_SLAB_ID';
795 set_debug_context;
796 /* jai_cmn_debug_contexts_pkg.register ( pv_context => lv_context
797 , pn_reg_id => ln_reg_id
798 ); */ -- */ --commented by bgowrava for bug#5631784
799
800 /* jai_cmn_debug_contexts_pkg.print (ln_reg_id
801 ,'Call Parameters:
802 P_REGIME_ID='||p_regime_id
803 ||' P_org_tan_no='||p_org_tan_no
804 ||' P_ORGANIZATION_ID='||p_organization_id
805 ||' P_PARTY_TYPE='||p_party_type
806 ||' P_PARTY_ID='||p_party_id
807 ||' P_FIN_YEAR='||p_fin_year
808 ||' P_ORG_ID='||p_org_id
809 ||' P_SOURCE_TRX_DATE='||p_source_trx_date
810 ,jai_cmn_debug_contexts_pkg.detail
811 ); */ -- */ --commented by bgowrava for bug#5631784
812
813 if p_org_tan_no is null then
814 if p_organization_id is not null then
815 /** Get org_tan_no using inventory organization from regime setup */
816 open c_get_rgm_attribute ( cp_regime_id => p_regime_id
817 , cp_attribute_type_code => jai_constants.rgm_attr_type_code_primary /* PRIMARY */
818 , cp_attribute_code => jai_constants.rgm_attr_code_org_tan /*ORG_TAN_NUM */
819 , cp_organization_id => p_organization_id
820 );
821 fetch c_get_rgm_attribute into ln_org_tan_no;
822 close c_get_rgm_attribute;
823 else
824 /** Both org_tan_no and organization_id are null */
825 p_process_flag := jai_constants.expected_error;
826 p_process_message := 'P_ORG_TAN_NO and P_ORGANIZATION_ID both cannot be null';
827 return;
828 end if;
829 else
830 /** Use the p_org_tan_no */
831 ln_org_tan_no := p_org_tan_no;
832 end if;
833
834 /** Assumption: If org_tan_no is null cannot derrive threshold amount*/
835 if ln_org_tan_no is null then
836 p_process_flag := jai_constants.expected_error;
837 p_process_message := 'Unable to get mandatory attribute ORG_TAN_NUM using the arguments
838 || P_REGIME_ID='||p_regime_id
839 ||' P_ORGANIZATION_ID=' || p_organization_id;
840 return;
841 end if;
842
843 if p_fin_year is null then
844 if p_org_id is not null then
845 /** Fin_year is not given but org_id is available. Hence derive fin_year using org_id and trx_date */
846
847 open jai_ar_tcs_rep_pkg.get_tcs_fin_year ( cp_org_id => p_org_id
848 , cp_trx_date => p_source_trx_date
849 );
850 fetch jai_ar_tcs_rep_pkg.get_tcs_fin_year into ln_fin_year;
851 close jai_ar_tcs_rep_pkg.get_tcs_fin_year;
852
853 else
854 /** Both fin_year and org_id are null*/
855 p_process_flag := jai_constants.expected_error;
856 p_process_message := 'Both P_FIN_YEAR and P_ORG_ID cannot be null';
857 return;
858 end if;
859 else
860 ln_fin_year := p_fin_year;
861 end if;
862
863 /** Assumption: If fin_year is null cannot a unique threshold hdr record */
864 if ln_fin_year is null then
865 p_process_flag := jai_constants.expected_error;
866 p_process_message := 'Unable to derive mandatory LN_FIN_YEAR using the given arguments'
867 ||' P_FIN_YEAR='||p_fin_year
868 ||' P_ORG_ID='||p_organization_id
869 ||' P_SOURCE_TRX_DATE='||p_source_trx_date;
870 return;
871 end if;
872
873 /* jai_cmn_debug_contexts_pkg.print (ln_reg_id
874 , 'ln_fin_year= '||ln_fin_year
875 ||'ln_org_tan_no='||ln_org_tan_no
876 ||'party_type='||jai_constants.party_type_customer
877 ||'p_party_id='||p_party_id
878 ,jai_cmn_debug_contexts_pkg.detail
879 ); */ -- */ --commented by bgowrava for bug#5631784
880
881 if p_called_from is not null
882 and p_called_from = jai_constants.tcs_event_surcharge then
883 /** This is an internal call. Hence fetch the applicable slab id for amount updated in the jai_rgm_thresholds */
884
885 /**
886 Assumption: To fetch slab from the setup transaction date is mandatory
887 */
888 if p_source_trx_date is null then
889 p_process_flag := jai_constants.expected_error;
890 p_process_message := 'When deriving the threshold slab from setup, P_SOURCE_TRX_DATE cannot be null. P_SOURCE_TRX_DATE='||nvl(to_char(p_source_trx_date),'null');
891 return;
892 end if;
893
894 open c_get_cust_typ_lkup_code;
895 fetch c_get_cust_typ_lkup_code into lv_customer_type_lkup_code;
896 close c_get_cust_typ_lkup_code;
897
898 /**
899 Assumption: Party classification cannot be null for a customer as it is one of
900 the attribute used to derrive the slab
901 */
902 if lv_customer_type_lkup_code is null then
903 p_process_flag := jai_constants.expected_error;
904 p_process_message := 'Unable to derive party classification for party_id='||p_party_id;
905 return;
906 end if;
907
908 /** Get threshold slab for the current threshold amount for combination of
909 org_tan_num, fin_year, party_type and party_id
910
911 Assumption: for TCS, party_type will be CUSTOMER only
912 */
913 /* jai_cmn_debug_contexts_pkg.print (ln_reg_id, 'lv_customer_type_lkup_code='||lv_customer_type_lkup_code, jai_cmn_debug_contexts_pkg.detail);*/ -- */ --commented by bgowrava for bug#5631784
914 open c_get_threshold_slab ( cp_fin_year => ln_fin_year
915 , cp_org_tan_no => ln_org_tan_no
916 , cp_party_type => jai_constants.party_type_customer
917 , cp_party_id => p_party_id
918 , cp_customer_type_lkup_code => lv_customer_type_lkup_code
919 );
920 fetch c_get_threshold_slab into p_threshold_slab_id;
921 close c_get_threshold_slab ;
922
923 else /** p_called_from is null means the call is from outside. So, fetch the threshold slab from jai_rgm_thresholds and return */
924
925 open c_get_thhold_hdr_slab ( cp_fin_year => ln_fin_year
926 , cp_org_tan_no => ln_org_tan_no
927 , cp_party_type => jai_constants.party_type_customer
928 , cp_party_id => p_party_id
929 );
930 fetch c_get_thhold_hdr_slab into p_threshold_slab_id;
931 close c_get_thhold_hdr_slab;
932
933 end if;
934
935 /* jai_cmn_debug_contexts_pkg.print (ln_reg_id, 'P_THRESHOLD_SLAB_ID='||p_threshold_slab_id,jai_cmn_debug_contexts_pkg.detail);*/ -- */ --commented by bgowrava for bug#5631784
936
937 /** Process completed successfully */
938 p_process_flag := jai_constants.successful; --'SS'
939 p_process_message := null;
940
941 /** Deregister procedure and return*/
942 <<deregister_and_return>>
943 /* jai_cmn_debug_contexts_pkg.deregister (pn_reg_id => ln_reg_id);*/ -- */ --commented by bgowrava for bug#5631784
944 return;
945 exception
946 when others then
947 p_process_flag := jai_constants.unexpected_error;
948 p_process_message := lv_context||'->'||sqlerrm;
949 /* jai_cmn_debug_contexts_pkg.print(ln_reg_id,sqlerrm,jai_cmn_debug_contexts_pkg.summary);
950 jai_cmn_debug_contexts_pkg.print_stack;*/ -- */ --commented by bgowrava for bug#5631784
951
952 end get_threshold_slab_id;
953
954 /*------------------------------------------------------------------------------------------------------------*/
955 /*
956 get_threshold_tax_cat_id - returns tax category defined in the threshold setup as out parameter
957 IN
958 p_threshold_slab_id - Threshold slab identifier
959 p_org_id - Operating unit
960 OUT
961 p_threshold_tax_cat_id - Tax category identifier
962 p_process_flag - Flag indicates the process status, can be either
963 Successful (SS)
964 Expected Error (EE)
965 Unexpected Error (UE)
966 p_process_message - Message to be passed to caller of the api. It can be null in case of
967 p_process_flag = 'SS'
968 */
969 /*------------------------------------------------------------------------------------------------------------*/
970 procedure get_threshold_tax_cat_id
971 (
972 p_threshold_slab_id in jai_rgm_thresholds.threshold_slab_id%type
973 , p_org_id in jai_ap_tds_thhold_taxes.operating_unit_id%type
974 , p_threshold_tax_cat_id out nocopy jai_ap_tds_thhold_taxes.tax_category_id%type
975 , p_process_flag out nocopy varchar2
976 , p_process_message out nocopy varchar2
977 )
978 is
979 ln_reg_id number;
980 /** Get tax category attached to the operating unit */
981 cursor c_get_threshold_tax_cat_id
982 is
983 select
984 thtaxes.tax_category_id
985 from
986 jai_ap_tds_thhold_taxes thtaxes
987 where
988 thtaxes.threshold_slab_id = p_threshold_slab_id
989 and operating_unit_id = p_org_id;
990
991 begin
992 /** Initialize process variables */
993 p_process_flag := jai_constants.successful; --'SS'
994 p_process_message := null;
995
996 /** Register this procedure for debuging */
997 lv_member_name := 'GET_THRESHOLD_TAX_CAT_ID';
998 set_debug_context;
999 /* jai_cmn_debug_contexts_pkg.register ( pv_context => lv_context
1000 , pn_reg_id => ln_reg_id
1001 ) ;
1002 jai_cmn_debug_contexts_pkg.print (ln_reg_id
1003 , 'Opening and fetching c_get_threshold_tax_cat_id
1004 p_threshold_slab_id='||p_threshold_slab_id
1005 ||'p_org_id='||p_org_id
1006 ,jai_cmn_debug_contexts_pkg.detail
1007 ); */ --commented by bgowrava for bug#5631784
1008
1009 if p_threshold_slab_id is null then
1010 p_threshold_tax_cat_id := -1;
1011 goto deregister_and_return;
1012 end if;
1013
1014 open c_get_threshold_tax_cat_id;
1015 fetch c_get_threshold_tax_cat_id into p_threshold_tax_cat_id;
1016 close c_get_threshold_tax_cat_id;
1017 jai_cmn_debug_contexts_pkg.print (ln_reg_id, '(Out) P_THRESHOLD_TAX_CAT_ID='||p_threshold_tax_cat_id,jai_cmn_debug_contexts_pkg.detail);
1018
1019 /** Deregister and return */
1020 <<deregister_and_return>>
1021 /* jai_cmn_debug_contexts_pkg.deregister (pn_reg_id => ln_reg_id);*/ --commented by bgowrava for bug#5631784
1022 return;
1023
1024 exception
1025
1026 when others then
1027 p_process_flag := jai_constants.unexpected_error;
1028 p_process_message := lv_context||'->'||sqlerrm;
1029 jai_cmn_debug_contexts_pkg.print(ln_reg_id,sqlerrm,jai_cmn_debug_contexts_pkg.summary);
1030 jai_cmn_debug_contexts_pkg.print_stack;
1031
1032 end get_threshold_tax_cat_id;
1033
1034 /*------------------------------------------------------------------------------------------------------------*/
1035 /*
1036 default_thhold_taxes - defaults threshold taxes defined by the tax category
1037 IN
1038 p_source_trx_id - transaction identifier
1039 p_source_trx_line_id - transaction line identifier
1040 p_source_event - Event for which taxes to be defaulted. Currently only 'SHIPPING'
1041 p_source_action - Action on which taxes are defaulted. Currently only 'CONFIRM'
1042 p_tax_base_line_number - Line number to be used as base line when calculating taxes. Default is 0
1043 p_last_line_number - Line number after which threshold taxes to be defaulted
1044 p_threshold_tax_cat_id - Tax category identifier for taxes to be defaulted
1045
1046 OUT
1047 p_process_flag - Flag indicates the process status, can be either
1048 Successful (SS)
1049 Expected Error (EE)
1050 Unexpected Error (UE)
1051 p_process_message - Message to be passed to caller of the api. It can be null in case of p_process_flag = 'SS'
1052 */
1053 /*------------------------------------------------------------------------------------------------------------*/
1054 procedure default_thhold_taxes
1055 (
1056 p_source_trx_id in number
1057 , p_source_trx_line_id in number
1058 , p_source_event in varchar2
1059 , p_action in varchar2
1060 , p_threshold_tax_cat_id in jai_ap_tds_thhold_taxes.tax_category_id%type
1061 , p_tax_base_line_number in number default 0
1062 , p_last_line_number in number default 0
1063 , p_currency_code in varchar2 default null
1064 , p_currency_conv_rate in number default null
1065 , p_quantity in number default null
1066 , p_base_tax_amt in number default null
1067 , p_assessable_value in number default null
1068 , p_inventory_item_id in number default null
1069 , p_uom_code in varchar2 default null
1070 , p_vat_assessable_value in number default null
1071 , p_process_flag out nocopy varchar2
1072 , p_process_message out nocopy varchar2
1073 )
1074 is
1075 ln_reg_id number;
1076 ln_base number := 0;
1077
1078 type ref_cur_typ is ref cursor;
1079 refc_tax_cur ref_cur_typ;
1080
1081 r_taxes jai_cmn_tax_defaultation_pkg.tax_rec_typ;
1082
1083 ln_tax_amount number ;
1084 ln_user_id fnd_user.user_id%type := fnd_global.user_id;
1085 ln_login_id fnd_logins.login_id%type := fnd_global.login_id;
1086 lv_currency fnd_currencies.currency_code%type;
1087 ln_curr_conv_factor number;
1088 ln_exists number(2);
1089
1090 begin
1091 /** Initialize process variables */
1092 p_process_flag := jai_constants.successful; --'SS'
1093 p_process_message := null;
1094
1095 /** Register this procedure for debuging */
1096 lv_member_name := 'DEFAULT_THHOLD_TAXES';
1097 set_debug_context;
1098 /* jai_cmn_debug_contexts_pkg.register ( pv_context => lv_context
1099 , pn_reg_id => ln_reg_id
1100 ) ;
1101 jai_cmn_debug_contexts_pkg.print
1102 ( ln_reg_id
1103 , ' Call Parameters:
1104 p_source_trx_id ='||p_source_trx_id ||'
1105 , p_source_trx_line_id ='||p_source_trx_line_id ||'
1106 , p_source_event ='||p_source_event ||'
1107 , p_action ='||p_action ||'
1108 , p_threshold_tax_cat_id ='||p_threshold_tax_cat_id ||'
1109 , p_tax_base_line_number ='||p_tax_base_line_number ||'
1110 , p_last_line_number ='||p_last_line_number ||'
1111 , p_currency_code ='||p_currency_code ||'
1112 , p_currency_conv_rate ='||p_currency_conv_rate ||'
1113 , p_quantity ='||p_quantity ||'
1114 , p_base_tax_amt ='||p_base_tax_amt ||'
1115 , p_assessable_value ='||p_assessable_value ||'
1116 , p_inventory_item_id ='||p_inventory_item_id ||'
1117 , p_uom_code ='||p_uom_code ||'
1118 , p_vat_assessable_value ='||p_vat_assessable_value ||'
1119 , p_process_flag ='||p_process_flag ||'
1120 , p_process_message ='||p_process_message
1121 , jai_cmn_debug_contexts_pkg.summary
1122 ); */ --commented by bgowrava for bug#5631784
1123 ln_exists := null;
1124 if p_source_event = jai_constants.source_ttype_delivery then
1125 /* Temporary pl-sql block to check if surcharge/surcharge-cess type of tax already exists in the picking line */
1126 declare
1127 cursor c_chk_picking_tax_exists
1128 is
1129 select 1
1130 from JAI_OM_WSH_LINE_TAXES line, JAI_CMN_TAX_CTG_LINES cat
1131 where line.tax_id = cat.tax_id
1132 and line.delivery_detail_id = p_source_trx_line_id
1133 and cat.tax_category_id = p_threshold_tax_cat_id;
1134 begin
1135 open c_chk_picking_tax_exists;
1136 fetch c_chk_picking_tax_exists into ln_exists;
1137 close c_chk_picking_tax_exists ;
1138 end;
1139 elsif p_source_event = jai_constants.bill_only_invoice then
1140 /* Temporary pl-sql block to check if surcharge/surcharge-cess type of tax already exists in the ra_customer_trx_lines */
1141
1142 declare
1143 cursor c_chk_ra_trx_tax_exists
1144 is
1145 select 1
1146 from JAI_AR_TRX_TAX_LINES line, JAI_CMN_TAX_CTG_LINES cat
1147 where line.tax_id = cat.tax_id
1148 and line.link_to_cust_trx_line_id = p_source_trx_line_id
1149 and cat.tax_category_id = p_threshold_tax_cat_id;
1150 begin
1151 open c_chk_ra_trx_tax_exists ;
1152 fetch c_chk_ra_trx_tax_exists into ln_exists;
1153 close c_chk_ra_trx_tax_exists ;
1154 end;
1155
1156 end if; /* p_source_event */
1157
1158 if ln_exists is not null then
1159 -- Tax is already present hence no need to default it
1160 p_process_flag := jai_constants.successful;
1161 p_process_message := null;
1162 /* jai_cmn_debug_contexts_pkg.print (ln_reg_id, 'Skipping surcharge tax defaultaion as one of the tax is already present in the transaction');*/ --commented by bgowrava for bug#5631784
1163 return;
1164 end if;
1165
1166 --for r_taxes in c_get_taxes_from_category
1167 jai_cmn_tax_defaultation_pkg.get_tax_cat_taxes_cur
1168 (p_tax_category_id => -1 -- pass the value which never exists in JAI_CMN_TAX_CTG_LINES
1169 ,p_threshold_tax_cat_id => p_threshold_tax_cat_id
1170 ,p_max_tax_line => p_last_line_number
1171 ,p_max_rgm_tax_line => p_tax_base_line_number
1172 ,p_base => 0--p_last_line_number/*bduvarag for the bug#6081966, FP of 6084563*/
1173 ,p_refc_tax_cat_taxes_cur => refc_tax_cur
1174 );
1175 loop
1176 fetch refc_tax_cur into r_taxes;
1177 exit when refc_tax_cur%notfound;
1178
1179 if p_source_event = jai_constants.source_ttype_delivery then
1180 /* jai_cmn_debug_contexts_pkg.print (ln_reg_id, 'r_taxes.tax_id='||r_taxes.tax_id||', ln_exists='||ln_exists); */ --commented by bgowrava for bug#5631784
1181 insert into JAI_OM_WSH_LINE_TAXES
1182 ( picking_line_id
1183 , tax_line_no
1184 , precedence_1
1185 , precedence_2
1186 , precedence_3
1187 , precedence_4
1188 , precedence_5
1189 , tax_id
1190 , tax_rate
1191 , qty_rate
1192 , uom
1193 , tax_amount
1194 , func_tax_amount
1195 , base_tax_amount
1196 , creation_date
1197 , created_by
1198 , last_update_date
1199 , last_updated_by
1200 , last_update_login
1201 , delivery_detail_id
1202 , precedence_6
1203 , precedence_7
1204 , precedence_8
1205 , precedence_9
1206 , precedence_10
1207 )
1208 values ( null
1209 , r_taxes.lno
1210 , r_taxes.p_1
1211 , r_taxes.p_2
1212 , r_taxes.p_3
1213 , r_taxes.p_4
1214 , r_taxes.p_5
1215 , r_taxes.tax_id
1216 , r_taxes.tax_rate
1217 , null
1218 , r_taxes.tax_amount
1219 , r_taxes.uom_code
1220 , null
1221 , null
1222 , sysdate
1223 , ln_user_id
1224 , sysdate
1225 , ln_user_id
1226 , ln_login_id
1227 , p_source_trx_line_id
1228 , r_taxes.p_6
1229 , r_taxes.p_7
1230 , r_taxes.p_8
1231 , r_taxes.p_9
1232 , r_taxes.p_10
1233 );
1234 elsif p_source_event = jai_constants.bill_only_invoice then
1235 insert into JAI_AR_TRX_TAX_LINES
1236 (
1237 tax_line_no
1238 ,customer_trx_line_id
1239 ,link_to_cust_trx_line_id
1240 ,precedence_1
1241 ,precedence_2
1242 ,precedence_3
1243 ,precedence_4
1244 ,precedence_5
1245 ,tax_id
1246 ,tax_rate
1247 ,qty_rate
1248 ,uom
1249 ,tax_amount
1250 ,invoice_class
1251 ,func_tax_amount
1252 ,base_tax_amount
1253 ,creation_date
1254 ,created_by
1255 ,last_update_date
1256 ,last_updated_by
1257 ,last_update_login
1258 ,precedence_6
1259 ,precedence_7
1260 ,precedence_8
1261 ,precedence_9
1262 ,precedence_10
1263 )
1264 values (
1265 r_taxes.lno --tax_line_no
1266 , ra_customer_trx_lines_s.nextval --customer_trx_line_id
1267 , p_source_trx_line_id --link_to_cust_trx_line_id
1268 , r_taxes.p_1 --precedence_1
1269 , r_taxes.p_2 --precedence_2
1270 , r_taxes.p_3 --precedence_3
1271 , r_taxes.p_4 --precedence_4
1272 , r_taxes.p_5 --precedence_5
1273 , r_taxes.tax_id --tax_id
1274 , r_taxes.tax_rate --tax_rate
1275 , null --qty_rate
1276 , r_taxes.uom_code --uom
1277 , null --tax_amount
1278 , null --invoice_class
1279 , null --func_tax_amount
1280 , null --base_tax_amount
1281 , sysdate --creation_date
1282 , ln_user_id --created_by
1283 , sysdate --last_update_date
1284 , ln_user_id --last_updated_by
1285 , ln_login_id --last_update_login
1286 , r_taxes.p_6 --precedence_6
1287 , r_taxes.p_7 --precedence_7
1288 , r_taxes.p_8 --precedence_8
1289 , r_taxes.p_9 --precedence_9
1290 , r_taxes.p_10 --precedence_10
1291 );
1292 end if; /** p_source_event */
1293
1294 end loop;
1295 /*
1296 || Close the reference cursor. This will acutally close the cursor object in the server memory
1297 */
1298 close refc_tax_cur;
1299
1300 /* jai_cmn_debug_contexts_pkg.print (ln_reg_id, 'Before: jai_cmn_tax_defaultation_pkg.JA_IN_CALC_PREC_TAXES', jai_cmn_debug_contexts_pkg.summary);*/ --commented by bgowrava for bug#5631784
1301 /** Call ja_in_calc_prec_taxes procedure in recalculate taxes mode to recalculate taxes and update the related table */
1302 ln_tax_amount := p_base_tax_amt ;
1303 jai_cmn_tax_defaultation_pkg.ja_in_calc_prec_taxes
1304 ( transaction_name => p_source_event
1305 , p_tax_category_id => -1 /** Pass normal tax category as negative value so it will not be considered */
1306 , p_header_id => p_source_trx_id
1307 , p_line_id => p_source_trx_line_id
1308 , p_assessable_value => p_assessable_value
1309 , p_tax_amount => ln_tax_amount /** Final calculated tax amount is returned in this variable */
1310 , p_inventory_item_id => p_inventory_item_id
1311 , p_line_quantity => p_quantity
1312 , p_uom_code => p_uom_code
1313 , p_vendor_id => ''
1314 , p_currency => p_currency_code
1315 , p_currency_conv_factor => p_currency_conv_rate
1316 , p_creation_date => sysdate
1317 , p_created_by => ln_user_id
1318 , p_last_update_date => sysdate
1319 , p_last_updated_by => ln_user_id
1320 , p_last_update_login => ln_login_id
1321 , p_vat_assessable_value => p_vat_assessable_value
1322 , p_thhold_cat_base_tax_typ=> jai_constants.tax_type_tcs
1323 , p_threshold_tax_cat_id => p_threshold_tax_cat_id
1324 , p_source_trx_type => p_source_event
1325 , p_action => jai_constants.recalculate_taxes
1326 );
1327 /* jai_cmn_debug_contexts_pkg.print (ln_reg_id, 'After: jai_cmn_tax_defaultation_pkg.JA_IN_CALC_PREC_TAXES', jai_cmn_debug_contexts_pkg.summary);*/ --commented by bgowrava for bug#5631784
1328
1329 /** Deregister and return */
1330 <<deregister_and_return>>
1331 /* jai_cmn_debug_contexts_pkg.deregister (pn_reg_id => ln_reg_id);*/ --commented by bgowrava for bug#5631784
1332 return;
1333
1334 exception
1335
1336 when others then
1337 p_process_flag := jai_constants.unexpected_error;
1338 p_process_message := lv_context||'->'||sqlerrm;
1339 /* jai_cmn_debug_contexts_pkg.print(ln_reg_id,sqlerrm,jai_cmn_debug_contexts_pkg.summary);
1340 jai_cmn_debug_contexts_pkg.print_stack;*/ --commented by bgowrava for bug#5631784
1341
1342 end default_thhold_taxes;
1343
1344 /*------------------------------------------------------------------------------------------------------------*/
1345 procedure maintain_threshold
1346 ( p_transaction_id in jai_rgm_refs_all.transaction_id%type
1347 , p_last_line_flag in varchar2 default jai_constants.yes
1348 , p_process_flag out nocopy varchar2
1349 , p_process_message out nocopy varchar2
1350 )
1351 is
1352
1353 ln_reg_id number;
1354
1355 cursor c_get_trx_details
1356 is
1357 select regime_id
1358 , org_tan_no
1359 , party_id
1360 , party_type
1361 , fin_year
1362 , item_classification
1363 , source_document_type
1364 , sum(nvl(source_document_amt,0)) source_document_amt
1365 , source_document_date
1366 , org_id
1367 from jai_rgm_refs_all trxref
1368 where trxref.transaction_id = p_transaction_id
1369 group by
1370 regime_id
1371 , org_tan_no
1372 , party_id
1373 , party_type
1374 , fin_year
1375 , item_classification
1376 , source_document_type
1377 , source_document_date
1378 , org_id;
1379
1380 cursor c_get_threshold_id ( cp_regime_id jai_rgm_thresholds.regime_id%type
1381 , cp_org_tan_no jai_rgm_thresholds.org_tan_no%type
1382 , cp_party_type jai_rgm_thresholds.party_type%type
1383 , cp_party_id jai_rgm_thresholds.party_id%type
1384 , cp_fin_year jai_rgm_thresholds.fin_year%type
1385 )
1386 is
1387 select threshold_id
1388 , threshold_slab_id
1389 from jai_rgm_thresholds
1390 where regime_id = cp_regime_id
1391 and org_tan_no = cp_org_tan_no
1392 and party_id = cp_party_id
1393 and party_type = cp_party_type
1394 and fin_year = cp_fin_year;
1395
1396 cursor c_get_threshold_dtl ( cp_threshold_id jai_rgm_threshold_dtls.threshold_id%type
1397 , cp_item_classification jai_rgm_threshold_dtls.item_classification%type
1398 )
1399 is
1400 select threshold_dtl_id
1401 , invoice_amt
1402 , cash_receipt_amt
1403 , application_amt
1404 , unapplication_amt
1405 , reversal_amt
1406 , threshold_base_amt
1407 , manual_surcharge_amt
1408 , system_surcharge_amt
1409 , system_surcharge_cess_amt
1410 , system_surcharge_sh_cess_amt --Bgowrava for forward porting bug#5989740
1411 from jai_rgm_threshold_dtls
1412 where threshold_id = cp_threshold_id
1413 and item_classification = cp_item_classification;
1414
1415 cursor c_get_customer_pan (cp_customer_id JAI_CMN_CUS_ADDRESSES.customer_id%type)
1416 is
1417 select pan_no
1418 from JAI_CMN_CUS_ADDRESSES
1419 where customer_id = cp_customer_id
1420 and confirm_pan = jai_constants.yes;
1421
1422 cursor c_get_ref_thhold_base_amt (cp_base_tax_type jai_rgm_taxes.tax_type%type)
1423 is
1424 select sum(rtax.func_tax_amt)
1425 from jai_rgm_taxes rtax
1426 , jai_rgm_refs_all refs
1427 where rtax.trx_ref_id = refs.trx_ref_id
1428 and refs.transaction_id = p_transaction_id
1429 and rtax.tax_type = cp_base_tax_type;
1430
1431 cursor c_get_surcharge_amt (cp_tax_type varchar2
1432 ,cp_tax_modified_by varchar2
1433 )
1434 is
1435 select sum(rtax.func_tax_amt)
1436 from jai_rgm_taxes rtax
1437 , jai_rgm_refs_all refs
1438 where rtax.trx_ref_id = refs.trx_ref_id
1439 and rtax.tax_type = cp_tax_type
1440 and refs.transaction_id = p_transaction_id
1441 and rtax.tax_modified_by = nvl(cp_tax_modified_by,rtax.tax_modified_by);
1442
1443
1444 /*
1445 || Following cursor will derive tax amount from the TCS reporsitory taxes for SURCHARGE CESS. Technically, SURCHARGE CESS can be identified by
1446 || looking at tax_type and precedence_1 (which should be line number of TCS_SURCHARGE type of tax ). Functionally, SURCHARGE_CESS should depende
1447 || upon SURCHARGE type of tax
1448 */
1449
1450 cursor c_get_surcharge_cess (cp_thhold_tax_cat_id jai_ap_tds_thhold_taxes.tax_category_id%type)
1451 is
1452 select sum(rtax.func_tax_amt)
1453 from jai_rgm_taxes rtax
1454 , jai_rgm_refs_all refs
1455 , JAI_CMN_TAX_CTG_LINES srch
1456 , JAI_CMN_TAX_CTG_LINES srchcess
1457 , JAI_CMN_TAXES_ALL tax
1458 where rtax.trx_ref_id = refs.trx_ref_id
1459 and rtax.tax_type = jai_constants.tax_type_tcs_cess
1460 and srch.tax_category_id = cp_thhold_tax_cat_id
1461 and srchcess.tax_category_id = srch.tax_category_id
1462 and srchcess.precedence_1 = srch.line_no
1463 and srchcess.tax_id = rtax.tax_id
1464 and srch.tax_id = tax.tax_id
1465 and tax.tax_type = jai_constants.tax_type_tcs_surcharge
1466 and refs.transaction_id = p_transaction_id;
1467
1468 -- start Bgowrava for forward porting bug#5989740
1469
1470 cursor c_get_surcharge_sh_cess (cp_thhold_tax_cat_id jai_ap_tds_thhold_taxes.tax_category_id%type)
1471 is
1472 select sum(rtax.func_tax_amt)
1473 from jai_rgm_taxes rtax
1474 , jai_rgm_refs_all refs
1475 , JAI_CMN_TAX_CTG_LINES srch
1476 , JAI_CMN_TAX_CTG_LINES srchcess
1477 , JAI_CMN_TAXES_ALL tax
1478 where rtax.trx_ref_id = refs.trx_ref_id
1479 and rtax.tax_type = jai_constants.tax_type_sh_tcs_edu_cess
1480 and srch.tax_category_id = cp_thhold_tax_cat_id
1481 and srchcess.tax_category_id = srch.tax_category_id
1482 and srchcess.precedence_1 = srch.line_no
1483 and srchcess.tax_id = rtax.tax_id
1484 and srch.tax_id = tax.tax_id
1485 and tax.tax_type = jai_constants.tax_type_tcs_surcharge
1486 and refs.transaction_id = p_transaction_id;
1487
1488 -- end Bgowrava for forward porting bug#5989740
1489
1490
1491
1492 ln_threshold_id jai_rgm_thresholds.threshold_id%type;
1493 ln_threshold_dtl_id jai_rgm_threshold_dtls.threshold_dtl_id%type;
1494 lr_hdr_record jai_rgm_thresholds%rowtype;
1495 lr_dtl_record jai_rgm_threshold_dtls%rowtype;
1496 lx_row_id rowid;
1497 ln_ref_thhold_base_amt number;
1498 /*
1499 ln_manual_surcharge_amt number;
1500 ln_system_surcharge_amt number;
1501 ln_system_surcharge_cess_amt number;
1502 */
1503 ln_surcharge_amt number;
1504 ln_surcharge_cess_amt number;
1505 ln_surcharge_sh_cess_amt number; --Bgowrava for forward porting bug#5989740
1506 lv_thhold_slab_change_flag varchar2(2);
1507 ln_new_thhold_slab_id jai_rgm_thresholds.threshold_slab_id%type;
1508 ln_thhold_tax_cat_id jai_ap_tds_thhold_taxes.tax_category_id%type;
1509
1510
1511 ln_user_id fnd_user.user_id%type := fnd_global.user_id;
1512 ln_login_id fnd_logins.login_id%type := fnd_global.login_id;
1513
1514
1515
1516 begin
1517 /** Initialize process variables */
1518 p_process_flag := jai_constants.successful;
1519 p_process_message := null;
1520
1521 /** Register this procedure for debuging */
1522 lv_member_name := 'MAINTAIN_THRESHOLD';
1523 set_debug_context;
1524 /*jai_cmn_debug_contexts_pkg.register ( pv_context => lv_context
1525 , pn_reg_id => ln_reg_id
1526 ) ; */
1527
1528 /* jai_cmn_debug_contexts_pkg.print (ln_reg_id
1529 , 'Call Parameters:-' ||chr(10)
1530 ||'p_transaction_id ='||p_transaction_id||chr(10)
1531 ||'p_last_line_flag=' ||p_last_line_flag
1532 ,jai_cmn_debug_contexts_pkg.summary
1533 );*/ --commented by bgowrava for bug#5631784
1534
1535 for r_trx_lines in c_get_trx_details
1536 loop
1537
1538 /** Check for combination of ORG_TAN_NO, PARTY_ID, FIN_YEAR a record exists in the jai_rgm_thresholds table */
1539 open c_get_threshold_id
1540 ( cp_regime_id => r_trx_lines.regime_id
1541 , cp_org_tan_no => r_trx_lines.org_tan_no
1542 , cp_party_type => jai_constants.party_type_customer
1543 , cp_party_id => r_trx_lines.party_id
1544 , cp_fin_year => r_trx_lines.fin_year
1545 );
1546 fetch c_get_threshold_id into ln_threshold_id
1547 ,lr_hdr_record.threshold_slab_id;
1548 close c_get_threshold_id ;
1549
1550 /* jai_cmn_debug_contexts_pkg.print ( ln_reg_id
1551 , 'ln_threshold_id='||ln_threshold_id
1552 , jai_cmn_debug_contexts_pkg.detail
1553 );*/ --commented by bgowrava for bug#5631784
1554
1555 /**************************************************************************************
1556 ||Part -1 :- FIRST TIME HEADER CREATION IN TABLE JAI_RGM_THRESHOLDS
1557 ***************************************************************************************/
1558
1559 if ln_threshold_id is null then
1560
1561 /** Record does not exists for the combination, so create a header record */
1562 /** Initialize loop variables*/
1563
1564 /* jai_cmn_debug_contexts_pkg.print (ln_reg_id
1565 ,'Threshold header does not exists for combination of ORG_TAN_NO, PARTY_ID and FIN_YEAR. Creating ...'
1566 ); */ --commented by bgowrava for bug#5631784
1567 lr_hdr_record:=null;
1568
1569 /** Populate header record */
1570
1571 lr_hdr_record.threshold_id := ln_threshold_id ;
1572 lr_hdr_record.regime_id := r_trx_lines.regime_id ;
1573 lr_hdr_record.org_tan_no := r_trx_lines.org_tan_no ;
1574 lr_hdr_record.party_id := r_trx_lines.party_id ;
1575 lr_hdr_record.party_type := r_trx_lines.party_type ;
1576
1577 /* jai_cmn_debug_contexts_pkg.print (ln_reg_id
1578 ,'Open/Fetch/Close cursor C_GET_CUSTOMER_PAN'
1579 ); */ --commented by bgowrava for bug#5631784
1580
1581 open c_get_customer_pan (cp_customer_id => r_trx_lines.party_id);
1582 fetch c_get_customer_pan into lr_hdr_record.party_pan_no;
1583 close c_get_customer_pan;
1584 /**
1585 Assumption: Customer must have the PAN and it must be confirmed
1586 */
1587 if lr_hdr_record.party_pan_no is null then
1588 p_process_flag := jai_constants.expected_error;
1589 p_process_message := 'Cannot find a confirmed PAN for customer_id='||r_trx_lines.party_id
1590 ||'.Please define a confirmed PAN for the customer in the customer setup';
1591 return;
1592 end if;
1593
1594 lr_hdr_record.threshold_slab_id := null ;
1595 lr_hdr_record.fin_year := r_trx_lines.fin_year ;
1596 lr_hdr_record.total_threshold_amt := null ;
1597 lr_hdr_record.total_threshold_base_amt := null ;
1598 lr_hdr_record.creation_date := sysdate ;
1599 lr_hdr_record.created_by := ln_user_id ;
1600 lr_hdr_record.last_update_date := sysdate ;
1601 lr_hdr_record.last_updated_by := ln_user_id ;
1602 lr_hdr_record.last_update_login := ln_login_id ;
1603
1604
1605 /* jai_cmn_debug_contexts_pkg.print ( ln_reg_id
1606 , 'Before insert into jai_rgm_thresholds' || chr(10)
1607 ||'lr_hdr_record.threshold_id ='||lr_hdr_record.threshold_id || chr(10)
1608 ||'lr_hdr_record.regime_id ='||lr_hdr_record.regime_id || chr(10)
1609 ||'lr_hdr_record.org_tan_no ='||lr_hdr_record.org_tan_no || chr(10)
1610 ||'lr_hdr_record.party_id ='||lr_hdr_record.party_id || chr(10)
1611 ||'lr_hdr_record.party_type ='||lr_hdr_record.party_type || chr(10)
1612 ||'lr_hdr_record.party_pan_no ='||lr_hdr_record.party_pan_no || chr(10)
1613 ||'lr_hdr_record.threshold_slab_id ='||lr_hdr_record.threshold_slab_id || chr(10)
1614 ||'lr_hdr_record.fin_year ='||lr_hdr_record.fin_year || chr(10)
1615 ||'lr_hdr_record.total_threshold_amt ='||lr_hdr_record.total_threshold_amt || chr(10)
1616 ||'lr_hdr_record.total_threshold_base_amt ='||lr_hdr_record.total_threshold_base_amt || chr(10)
1617 ||'lr_hdr_record.creation_date ='||lr_hdr_record.creation_date || chr(10)
1618 ||'lr_hdr_record.created_by ='||lr_hdr_record.created_by || chr(10)
1619 ||'lr_hdr_record.last_update_date ='||lr_hdr_record.last_update_date || chr(10)
1620 ||'lr_hdr_record.last_updated_by ='||lr_hdr_record.last_updated_by || chr(10)
1621 ||'lr_hdr_record.last_update_login ='||lr_hdr_record.last_update_login || chr(10)
1622 );*/ --commented by bgowrava for bug#5631784
1623
1624 insert_threshold_hdr ( p_record => lr_hdr_record
1625 , p_threshold_id => ln_threshold_id
1626 , p_row_id => lx_row_id
1627 );
1628
1629 end if; /** ln_threshold_id is null */
1630
1631
1632 /**************************************************************************************
1633 ||Part -2 :- CREATE OR UPDATE TABLE JAI_RGM_THRESHOLD_DTLS
1634 ***************************************************************************************/
1635 /**
1636 Assumption: When control comes here ln_threshold_id should NOT BE NULL.
1637 */
1638 if ln_threshold_id is null then
1639 p_process_flag := jai_constants.expected_error;
1640 p_process_message := 'Cannot create threshold header record in jai_rgm_thresholds';
1641 return;
1642 end if;
1643
1644 /** Initialize record */
1645 lr_dtl_record := null;
1646 lr_dtl_record.invoice_amt := 0;
1647 lr_dtl_record.cash_receipt_amt := 0;
1648 lr_dtl_record.application_amt := 0;
1649 lr_dtl_record.unapplication_amt := 0;
1650 lr_dtl_record.reversal_amt := 0;
1651 lr_dtl_record.threshold_base_amt:= 0;
1652
1653 /* jai_cmn_debug_contexts_pkg.print (ln_reg_id, 'Open/Fetch/Close cursor c_get_threshold_dtl');*/ --commented by bgowrava for bug#5631784
1654
1655 /** Check if for the given threshold header and item classification a record is already present */
1656 open c_get_threshold_dtl ( cp_threshold_id => ln_threshold_id
1657 , cp_item_classification => r_trx_lines.item_classification
1658 );
1659 fetch c_get_threshold_dtl into lr_dtl_record.threshold_dtl_id
1660 , lr_dtl_record.invoice_amt
1661 , lr_dtl_record.cash_receipt_amt
1662 , lr_dtl_record.application_amt
1663 , lr_dtl_record.unapplication_amt
1664 , lr_dtl_record.reversal_amt
1665 , lr_dtl_record.threshold_base_amt
1666 , lr_dtl_record.manual_surcharge_amt
1667 , lr_dtl_record.system_surcharge_amt
1668 , lr_dtl_record.system_surcharge_cess_amt
1669 , lr_dtl_record.system_surcharge_sh_cess_amt; --Bgowrava for forward porting bug#5989740
1670 close c_get_threshold_dtl ;
1671
1672 /* jai_cmn_debug_contexts_pkg.print ( ln_reg_id
1673 ,'Before Assignment:'||chr(10)
1674 ||' lr_dtl_record.threshold_dtl_id='||lr_dtl_record.threshold_dtl_id||chr(10)
1675 ||', lr_dtl_record.invoice_amt ='||lr_dtl_record.invoice_amt ||chr(10)
1676 ||', lr_dtl_record.cash_receipt_amt='|| lr_dtl_record.cash_receipt_amt||chr(10)
1677 ||', lr_dtl_record.application_amt ='|| lr_dtl_record.application_amt ||chr(10)
1678 ||', lr_dtl_record.unapplication_amt='|| lr_dtl_record.unapplication_amt ||chr(10)
1679 ||', lr_dtl_record.reversal_amt ='||lr_dtl_record.reversal_amt ||chr(10)
1680 ||', lr_dtl_record.threshold_base_amt='||lr_dtl_record.threshold_base_amt||chr(10)
1681 ||', lr_dtl_record.manual_surcharge_amt='||lr_dtl_record.manual_surcharge_amt ||chr(10)
1682 ||', lr_dtl_record.system_surcharge_amt='||lr_dtl_record.system_surcharge_amt ||chr(10)
1683 ||', lr_dtl_record.system_surcharge_cess_amt='||lr_dtl_record.system_surcharge_cess_amt
1684 ); */ --commented by bgowrava for bug#5631784
1685
1686 /** Assign value to amount holders based on the source trx (document) type */
1687 if r_trx_lines.source_document_type in (jai_constants.trx_type_inv_comp, jai_constants.trx_type_inv_incomp ) then
1688
1689 lr_dtl_record.invoice_amt := nvl(lr_dtl_record.invoice_amt,0) + r_trx_lines.source_document_amt ;
1690
1691
1692 elsif r_trx_lines.source_document_type = jai_constants.ar_cash_tax_confirmed then
1693
1694 lr_dtl_record.cash_receipt_amt := nvl(lr_dtl_record.cash_receipt_amt,0) + r_trx_lines.source_document_amt;
1695
1696 elsif r_trx_lines.source_document_type in (jai_constants.trx_type_rct_app , jai_constants.trx_type_cm_app) then
1697
1698 lr_dtl_record.application_amt := nvl(lr_dtl_record.application_amt,0) + r_trx_lines.source_document_amt;
1699
1700 elsif r_trx_lines.source_document_type in (jai_constants.trx_type_rct_unapp, jai_constants.trx_type_cm_unapp) then
1701
1702 lr_dtl_record.unapplication_amt := nvl(lr_dtl_record.unapplication_amt,0) + r_trx_lines.source_document_amt;
1703
1704 elsif r_trx_lines.source_document_type = jai_constants.trx_type_rct_rvs then
1705
1706 lr_dtl_record.reversal_amt := nvl (lr_dtl_record.reversal_amt,0) + r_trx_lines.source_document_amt;
1707
1708 end if;
1709
1710 /** Get threshold base amount (sum of tax amount for tcs type of taxes) for current trx line */
1711
1712 /* jai_cmn_debug_contexts_pkg.print (ln_reg_id, 'Open/Fetch/Close cursor C_GET_REF_THHOLD_BASE_AMT');*/ --commented by bgowrava for bug#5631784
1713 open c_get_ref_thhold_base_amt (cp_base_tax_type => jai_constants.tax_type_tcs);
1714 fetch c_get_ref_thhold_base_amt into ln_ref_thhold_base_amt;
1715 close c_get_ref_thhold_base_amt;
1716
1717 lr_dtl_record.threshold_base_amt := nvl (lr_dtl_record.threshold_base_amt,0)
1718 + nvl (ln_ref_thhold_base_amt,0);
1719
1720 /*---------------------------------------------------------------------------------------------------------------
1721 Following code is intentionally kept commented. It can be used whenever there is strong req. to distinguish
1722 between MANUAL and SYSTEM surcharge
1723 -----------------------------------------------------------------------------------------------------------------*/
1724
1725 /** Get manual surcharge amount if any */
1726 /*jai_cmn_debug_contexts_pkg.print (ln_reg_id, 'Open/Fetch/Close cursor C_GET_SURCHARGE_AMT to get MANUAL surcharge');
1727 open c_get_surcharge_amt (cp_tax_type => jai_constants.tax_type_tcs_surcharge
1728 ,cp_tax_modified_by => jai_constants.tax_modified_by_user
1729 );
1730 fetch c_get_surcharge_amt into ln_manual_surcharge_amt;
1731 close c_get_surcharge_amt ;
1732
1733 lr_dtl_record.manual_surcharge_amt := nvl (lr_dtl_record.manual_surcharge_amt,0)
1734 + nvl (ln_manual_surcharge_amt,0);
1735 */
1736 /** Get system surcharge amount if any */
1737 /*
1738 jai_cmn_debug_contexts_pkg.print (ln_reg_id, 'Open/Fetch/Close cursor C_GET_SURCHARGE_AMT to get SYSTEM surcharge');
1739 open c_get_surcharge_amt (cp_tax_type => jai_constants.tax_type_tcs_surcharge
1740 ,cp_tax_modified_by => jai_constants.tax_modified_by_system
1741 );
1742 fetch c_get_surcharge_amt into ln_system_surcharge_amt;
1743 close c_get_surcharge_amt ;
1744
1745 lr_dtl_record.system_surcharge_amt := nvl (lr_dtl_record.system_surcharge_amt,0)
1746 + nvl (ln_system_surcharge_amt,0);
1747 */
1748 /** Get system surcharge cess amount if any */
1749 /*
1750 jai_cmn_debug_contexts_pkg.print (ln_reg_id, 'Open/Fetch/Close cursor C_GET_SURCHARGE_AMT to get SYSTEM surcharge cess');
1751 open c_get_surcharge_amt (cp_tax_type => jai_constants.tax_type_tcs_surcharge_cess
1752 ,cp_tax_modified_by => jai_constants.tax_modified_by_system
1753 );
1754 fetch c_get_surcharge_amt into ln_system_surcharge_cess_amt;
1755 close c_get_surcharge_amt ;
1756
1757
1758 lr_dtl_record.system_surcharge_cess_amt := nvl (lr_dtl_record.system_surcharge_cess_amt,0)
1759 + nvl (ln_system_surcharge_cess_amt,0);
1760 */
1761
1762 /*
1763 || Get the SURCHARGE tax amount
1764 */
1765 /* jai_cmn_debug_contexts_pkg.print (ln_reg_id, 'Open/Fetch/Close cursor C_GET_SURCHARGE_AMT');*/ --commented by bgowrava for bug#5631784
1766 open c_get_surcharge_amt (cp_tax_type => jai_constants.tax_type_tcs_surcharge
1767 ,cp_tax_modified_by => null
1768 );
1769 fetch c_get_surcharge_amt into ln_surcharge_amt;
1770 close c_get_surcharge_amt ;
1771
1772 /*
1773 || Get the SURCHARGE CESS tax amount
1774 || To get surcharge cess,
1775 || 1. First get tax_cat_id for the current threshold_slab_id
1776 || 2. Find out tax of type TCS_CESS which has precedence 1 defined as TCS_SURCHARGE
1777 */
1778
1779 /* jai_cmn_debug_contexts_pkg.print (ln_reg_id, 'Calling GET_THRESHOLD_TAX_CAT_ID to get tax category for current slab='||lr_hdr_record.threshold_slab_id );*/ --commented by bgowrava for bug#5631784
1780
1781 if lr_hdr_record.threshold_slab_id is not null then
1782 jai_rgm_thhold_proc_pkg.get_threshold_tax_cat_id
1783 (
1784 p_threshold_slab_id => lr_hdr_record.threshold_slab_id
1785 , p_org_id => r_trx_lines.org_id
1786 , p_threshold_tax_cat_id => ln_thhold_tax_cat_id
1787 , p_process_flag => p_process_flag
1788 , p_process_message => p_process_message
1789 );
1790
1791 if p_process_flag <> jai_constants.successful then
1792 return;
1793 end if;
1794
1795
1796 if ln_thhold_tax_cat_id = -1 then
1797 p_process_flag := jai_constants.expected_error;
1798 p_process_message := 'Cannot find tax category for active threshold slab. Please check the threshold setup';
1799 return;
1800 end if;
1801
1802 /* jai_cmn_debug_contexts_pkg.print (ln_reg_id, 'Open/Fetch/Close cursor C_GET_SURCHARGE_CESS');*/ --commented by bgowrava for bug#5631784
1803
1804 open c_get_surcharge_cess (cp_thhold_tax_cat_id => ln_thhold_tax_cat_id);
1805 fetch c_get_surcharge_cess into ln_surcharge_cess_amt;
1806 close c_get_surcharge_cess;
1807
1808
1809
1810 -- start 5907436
1811 open c_get_surcharge_sh_cess (cp_thhold_tax_cat_id => ln_thhold_tax_cat_id);
1812 fetch c_get_surcharge_sh_cess into ln_surcharge_sh_cess_amt;
1813 close c_get_surcharge_sh_cess ;
1814 -- end 5907436
1815
1816
1817 end if;
1818
1819 /*
1820 || If current threshold_slab_id is not null means it is a system surcharge. Otherwise (when slab is not applicable) it is a MANUAL
1821 || surcharge
1822 */
1823 if lr_hdr_record.threshold_slab_id is not null then
1824
1825 lr_dtl_record.system_surcharge_amt := nvl (lr_dtl_record.system_surcharge_amt,0)
1826 + nvl (ln_surcharge_amt,0);
1827
1828 lr_dtl_record.system_surcharge_cess_amt := nvl (lr_dtl_record.system_surcharge_cess_amt,0)
1829 + nvl (ln_surcharge_cess_amt,0);
1830
1831 lr_dtl_record.system_surcharge_sh_cess_amt := nvl (lr_dtl_record.system_surcharge_sh_cess_amt,0)
1832 + nvl (ln_surcharge_sh_cess_amt,0) ; --Bgowrava for forward porting bug#5989740
1833
1834
1835 else
1836
1837 lr_dtl_record.manual_surcharge_amt := nvl (lr_dtl_record.manual_surcharge_amt,0)
1838 + nvl (ln_surcharge_amt,0);
1839
1840 end if;
1841
1842
1843
1844 lr_dtl_record.threshold_id := ln_threshold_id ;
1845 lr_dtl_record.item_classification := r_trx_lines.item_classification;
1846 lr_dtl_record.creation_date := sysdate;
1847 lr_dtl_record.created_by := ln_user_id;
1848 lr_dtl_record.last_update_date := sysdate;
1849 lr_dtl_record.last_updated_by := ln_user_id;
1850 lr_dtl_record.last_update_login := ln_login_id;
1851
1852
1853 /* jai_cmn_debug_contexts_pkg.print
1854 ( ln_reg_id
1855 ,'After Assignment: ' ||chr(10)
1856 ||' lr_dtl_record.threshold_dtl_id='||lr_dtl_record.threshold_dtl_id ||chr(10)
1857 ||', lr_dtl_record.invoice_amt ='||lr_dtl_record.invoice_amt ||chr(10)
1858 ||', lr_dtl_record.cash_receipt_amt='|| lr_dtl_record.cash_receipt_amt ||chr(10)
1859 ||', lr_dtl_record.application_amt ='|| lr_dtl_record.application_amt ||chr(10)
1860 ||', lr_dtl_record.unapplication_amt='|| lr_dtl_record.unapplication_amt ||chr(10)
1861 ||', lr_dtl_record.reversal_amt ='||lr_dtl_record.reversal_amt ||chr(10)
1862 ||', lr_dtl_record.threshold_base_amt='||lr_dtl_record.threshold_base_amt ||chr(10)
1863 ||', lr_dtl_record.manual_surcharge_amt='||lr_dtl_record.manual_surcharge_amt ||chr(10)
1864 ||', lr_dtl_record.system_surcharge_amt='||lr_dtl_record.system_surcharge_amt ||chr(10)
1865 ||', lr_dtl_record.system_surcharge_cess_amt='||lr_dtl_record.system_surcharge_cess_amt||chr(10)
1866 ||', lr_dtl_record.item_classification='|| lr_dtl_record.item_classification
1867 ); */ --commented by bgowrava for bug#5631784
1868
1869 if lr_dtl_record.threshold_dtl_id is null then
1870
1871 insert_threshold_dtl ( p_record => lr_dtl_record
1872 , p_threshold_dtl_id => ln_threshold_dtl_id
1873 , p_row_id => lx_row_id
1874 );
1875
1876 else /** Threshold detils record already exists. Hence update the existing record to increment
1877 per item classificaton amounts */
1878
1879 /* jai_cmn_debug_contexts_pkg.print (ln_reg_id
1880 ,'Updating JAI_RGM_THRESHOLD_DTLS' || chr(10)
1881 ||'invoice_amt ='||lr_dtl_record.invoice_amt || chr(10)
1882 ||'cash_receipt_amt ='||lr_dtl_record.cash_receipt_amt || chr(10)
1883 ||'application_amt ='||lr_dtl_record.application_amt || chr(10)
1884 ||'unapplication_amt ='||lr_dtl_record.unapplication_amt || chr(10)
1885 ||'reversal_amt ='||lr_dtl_record.reversal_amt || chr(10)
1886 ||'manual_surcharge_amt ='||lr_dtl_record.manual_surcharge_amt || chr(10)
1887 ||'system_surcharge_amt ='||lr_dtl_record.system_surcharge_amt || chr(10)
1888 ||'system_surcharge_cess_amt ='||lr_dtl_record.system_surcharge_cess_amt || chr(10)
1889 ||'threshold_base_amt ='||lr_dtl_record.threshold_base_amt || chr(10)
1890 ||'last_update_date ='||sysdate || chr(10)
1891 ||'last_updated_by ='||ln_user_id || chr(10)
1892 ||'last_update_login ='||ln_login_id || chr(10)
1893 ||'last_update_login ='||ln_threshold_dtl_id
1894 ); */ --commented by bgowrava for bug#5631784
1895
1896 update jai_rgm_threshold_dtls
1897 set invoice_amt = lr_dtl_record.invoice_amt
1898 , cash_receipt_amt = lr_dtl_record.cash_receipt_amt
1899 , application_amt = lr_dtl_record.application_amt
1900 , unapplication_amt = lr_dtl_record.unapplication_amt
1901 , reversal_amt = lr_dtl_record.reversal_amt
1902 , manual_surcharge_amt = lr_dtl_record.manual_surcharge_amt
1903 , system_surcharge_amt = lr_dtl_record.system_surcharge_amt
1904 , system_surcharge_cess_amt = lr_dtl_record.system_surcharge_cess_amt
1905 , system_surcharge_sh_cess_amt = lr_dtl_record.system_surcharge_sh_cess_amt --Bgowrava for forward porting bug#5989740
1906 , threshold_base_amt = lr_dtl_record.threshold_base_amt
1907 , last_update_date = sysdate
1908 , last_updated_by = ln_user_id
1909 , last_update_login = ln_login_id
1910 where threshold_dtl_id = lr_dtl_record.threshold_dtl_id;
1911
1912 end if; /** lr_dtl_record.threshold_dtl_id is null */
1913
1914 if p_last_line_flag = jai_constants.yes then
1915 /*
1916 || This is the last line of the document, so update the summary amounts maintained by header table.
1917 || As repository package is always making a call at document level (not at line level), this flag will
1918 || always be yes
1919 */
1920 /* jai_cmn_debug_contexts_pkg.print ( ln_reg_id, 'Before : sync_threshold_header'); */ --commented by bgowrava for bug#5631784
1921
1922 ln_new_thhold_slab_id := null;
1923 lv_thhold_slab_change_flag := jai_constants.no;
1924
1925 sync_threshold_header
1926 ( p_threshold_id => ln_threshold_id
1927 , p_source_trx_date => r_trx_lines.source_document_date
1928 , p_thhold_slab_change_flag => lv_thhold_slab_change_flag
1929 , p_new_thhold_slab_id => ln_new_thhold_slab_id
1930 , p_process_flag => p_process_flag
1931 , p_process_message => p_process_message
1932 ) ;
1933 /* jai_cmn_debug_contexts_pkg.print ( ln_reg_id, 'After: sync_threshold_header');
1934 jai_cmn_debug_contexts_pkg.print ( ln_reg_id, 'p_process_flag='||p_process_flag||',p_process_message='||p_process_message);*/ --commented by bgowrava for bug#5631784
1935
1936 if p_process_flag <> jai_constants.successful then
1937 return;
1938 end if;
1939
1940 /** Update jai_rgm_refs_all to punch the threshold_slab_id against all the transaction lines */
1941
1942 if ln_new_thhold_slab_id is not null then
1943 update jai_rgm_refs_all
1944 set threshold_slab_id = ln_new_thhold_slab_id
1945 where transaction_id = p_transaction_id;
1946 end if;
1947
1948 if lv_thhold_slab_change_flag = jai_constants.yes then
1949 /**
1950 The threshold slab has changed hence delegate the call to generate_consolidated_doc.
1951 Based on the new slab consolidated document needs to be generated which can be either DM or CM.
1952 The API generate_consolidated_doc will calculate document amount and will decide which document
1953 to generate.
1954 */
1955 /* jai_cmn_debug_contexts_pkg.print ( ln_reg_id, 'Before : generate_consolidated_doc');*/ --commented by bgowrava for bug#5631784
1956 generate_consolidated_doc ( p_threshold_id => ln_threshold_id
1957 , p_transaction_id => p_transaction_id
1958 , p_org_id => r_trx_lines.org_id
1959 , p_process_flag => p_process_flag
1960 , p_process_message => p_process_message
1961 );
1962 /* jai_cmn_debug_contexts_pkg.print ( ln_reg_id, 'After : generate_consolidated_doc');
1963 jai_cmn_debug_contexts_pkg.print ( ln_reg_id, 'p_process_flag='||p_process_flag||',p_process_message='||p_process_message);*/ --commented by bgowrava for bug#5631784
1964
1965 if p_process_flag <> jai_constants.successful then
1966 return;
1967 end if;
1968
1969 end if; /** lv_thhold_slab_change_flag = jai_constants.yes */
1970
1971 end if; /** p_last_line_flag = jai_constants.yes */
1972
1973 end loop; /** r_trx_lines */
1974
1975 /* jai_cmn_debug_contexts_pkg.print ( ln_reg_id,' MAINTAIN_THRESHOLD completed successfully',jai_cmn_debug_contexts_pkg.summary) ;*/ --commented by bgowrava for bug#5631784
1976 /** Deregister and return */
1977 <<deregister_and_return>>
1978 /* jai_cmn_debug_contexts_pkg.deregister (pn_reg_id => ln_reg_id);*/ --commented by bgowrava for bug#5631784
1979 return;
1980
1981 exception
1982
1983 when others then
1984 p_process_flag := jai_constants.unexpected_error;
1985 p_process_message := lv_context||'->'||sqlerrm;
1986 jai_cmn_debug_contexts_pkg.print(ln_reg_id,sqlerrm,jai_cmn_debug_contexts_pkg.summary);
1987 jai_cmn_debug_contexts_pkg.print_stack;
1988
1989 end maintain_threshold;
1990
1991 /*------------------------------------------------------------------------------------------------------------*/
1992 procedure insert_threshold_dtl ( p_record in jai_rgm_threshold_dtls%rowtype
1993 , p_threshold_dtl_id out nocopy jai_rgm_threshold_dtls.threshold_dtl_id%type
1994 , p_row_id out nocopy rowid
1995 )
1996 is
1997
1998 begin
1999
2000 if p_record.threshold_dtl_id is null then
2001 select jai_rgm_thresholds_s.nextval
2002 into p_threshold_dtl_id
2003 from dual;
2004 end if;
2005 /** Threshold detail record does not exists */
2006 insert into jai_rgm_threshold_dtls
2007 ( threshold_dtl_id
2008 , threshold_id
2009 , item_classification
2010 , invoice_amt
2011 , cash_receipt_amt
2012 , application_amt
2013 , unapplication_amt
2014 , reversal_amt
2015 , manual_surcharge_amt
2016 , system_surcharge_amt
2017 , system_surcharge_cess_amt
2018 , system_surcharge_sh_cess_amt --Bgowrava for forward porting bug#5989740
2019 , threshold_base_amt
2020 , creation_date
2021 , created_by
2022 , last_update_date
2023 , last_updated_by
2024 , last_update_login
2025 )
2026 values
2027 (
2028 p_threshold_dtl_id
2029 , p_record.threshold_id
2030 , p_record.item_classification
2031 , p_record.invoice_amt
2032 , p_record.cash_receipt_amt
2033 , p_record.application_amt
2034 , p_record.unapplication_amt
2035 , p_record.reversal_amt
2036 , p_record.manual_surcharge_amt
2037 , p_record.system_surcharge_amt
2038 , p_record.system_surcharge_cess_amt
2039 , p_record.system_surcharge_sh_cess_amt --Bgowrava for forward porting bug#5989740
2040 , p_record.threshold_base_amt
2041 , p_record.creation_date
2042 , p_record.created_by
2043 , p_record.last_update_date
2044 , p_record.last_updated_by
2045 , p_record.last_update_login
2046 )
2047 returning rowid
2048 ,threshold_dtl_id
2049 into p_row_id
2050 ,p_threshold_dtl_id ;
2051 exception
2052 when others then
2053 p_row_id := null;
2054 p_threshold_dtl_id := null;
2055 raise;
2056
2057 end insert_threshold_dtl;
2058 /*------------------------------------------------------------------------------------------------------------*/
2059 procedure insert_threshold_hdr ( p_record in jai_rgm_thresholds%rowtype
2060 , p_threshold_id out nocopy jai_rgm_thresholds.threshold_id%type
2061 , p_row_id out nocopy rowid
2062 )
2063 is
2064 begin
2065
2066 if p_record.threshold_id is null then
2067 select jai_rgm_thresholds_s.nextval
2068 into p_threshold_id
2069 from dual;
2070 end if;
2071
2072 insert into jai_rgm_thresholds
2073 ( threshold_id
2074 , regime_id
2075 , org_tan_no
2076 , party_id
2077 , party_type
2078 , party_pan_no
2079 , threshold_slab_id
2080 , fin_year
2081 , total_threshold_amt
2082 , total_threshold_base_amt
2083 , creation_date
2084 , created_by
2085 , last_update_date
2086 , last_updated_by
2087 , last_update_login
2088 )
2089 values
2090 ( p_threshold_id
2091 , p_record.regime_id
2092 , p_record.org_tan_no
2093 , p_record.party_id
2094 , p_record.party_type
2095 , p_record.party_pan_no
2096 , p_record.threshold_slab_id
2097 , p_record.fin_year
2098 , p_record.total_threshold_amt
2099 , p_record.total_threshold_base_amt
2100 , p_record.creation_date
2101 , p_record.created_by
2102 , p_record.last_update_date
2103 , p_record.last_updated_by
2104 , p_record.last_update_login
2105 )
2106 returning rowid
2107 ,threshold_id
2108 into p_row_id
2109 ,p_threshold_id ;
2110 exception
2111 when others then
2112 p_row_id := null;
2113 p_threshold_id := null;
2114 raise;
2115 end insert_threshold_hdr;
2116
2117 /*------------------------------------------------------------------------------------------------------------*/
2118 procedure sync_threshold_header
2119 ( p_threshold_id in jai_rgm_thresholds.threshold_id%type
2120 , p_source_trx_date in date
2121 , p_thhold_slab_change_flag out nocopy varchar2
2122 , p_new_thhold_slab_id out nocopy jai_rgm_thresholds.threshold_slab_id%type
2123 , p_process_flag out nocopy varchar2
2124 , p_process_message out nocopy varchar2
2125 )
2126
2127 is
2128 ln_reg_id number;
2129 cursor c_get_thhold_summary
2130 is
2131 select sum ( nvl(invoice_amt ,0)
2132 + nvl(cash_receipt_amt ,0)
2133 + nvl(application_amt ,0)
2134 + nvl(unapplication_amt ,0)
2135 + nvl(reversal_amt ,0)
2136 ) total_threshold_amt
2137 ,sum ( nvl(threshold_base_amt ,0)
2138 ) total_threshold_base_amt
2139 from jai_rgm_threshold_dtls
2140 where threshold_id = p_threshold_id;
2141
2142 cursor c_get_thhold_hdr_info
2143 is
2144 select regime_id
2145 ,org_tan_no
2146 ,party_type
2147 ,party_id
2148 ,fin_year
2149 ,threshold_slab_id
2150 from jai_rgm_thresholds
2151 where threshold_id = p_threshold_id;
2152
2153 ln_regime_id jai_rgm_thresholds.regime_id%type;
2154 lv_org_tan_no jai_rgm_thresholds.org_tan_no%type;
2155 lv_party_type jai_rgm_thresholds.party_type%type;
2156 ln_party_id jai_rgm_thresholds.party_id%type;
2157 ln_fin_year jai_rgm_thresholds.fin_year%type;
2158
2159 ln_curr_thhold_slab_id jai_rgm_thresholds.threshold_slab_id%type;
2160 ln_new_thhold_slab_id jai_rgm_thresholds.threshold_slab_id%type;
2161
2162 ln_total_thhold_amt jai_rgm_thresholds.total_threshold_amt%type;
2163 ln_total_thhold_base_amt jai_rgm_thresholds.total_threshold_base_amt%type;
2164
2165 ln_user_id fnd_user.user_id%type := fnd_global.user_id;
2166 ln_login_id fnd_logins.login_id%type := fnd_global.login_id;
2167
2168
2169 begin
2170 /** Initialize process variables */
2171 p_process_flag := jai_constants.successful;
2172 p_process_message := null;
2173 p_thhold_slab_change_flag := jai_constants.no;
2174
2175 /** Register this procedure for debuging */
2176 lv_member_name := 'SYNC_THRESHOLD_HEADER';
2177 set_debug_context;
2178 /* jai_cmn_debug_contexts_pkg.register ( pv_context => lv_context
2179 , pn_reg_id => ln_reg_id
2180 ) ;
2181 jai_cmn_debug_contexts_pkg.print ( ln_reg_id
2182 , 'SYNC_THRESHOLD_HEADER Call Parameters:'
2183 ||'p_threshold_id='||p_threshold_id
2184 ||'p_source_trx_date='||p_source_trx_date
2185 ,jai_cmn_debug_contexts_pkg.summary
2186 ); */ --commented by bgowrava for bug#5631784
2187 /** Fetch the summary information for a particular threshold_id */
2188 open c_get_thhold_summary ;
2189 fetch c_get_thhold_summary into ln_total_thhold_amt
2190 , ln_total_thhold_base_amt;
2191 close c_get_thhold_summary ;
2192
2193 ln_total_thhold_amt := nvl(ln_total_thhold_amt ,0);
2194 ln_total_thhold_base_amt := nvl(ln_total_thhold_base_amt ,0);
2195
2196 update jai_rgm_thresholds
2197 set total_threshold_amt = ln_total_thhold_amt
2198 ,total_threshold_base_amt = ln_total_thhold_base_amt
2199 ,last_updated_by = ln_user_id
2200 ,last_update_date = sysdate
2201 ,last_update_login = ln_login_id
2202 where threshold_id = p_threshold_id;
2203
2204 /* jai_cmn_debug_contexts_pkg.print ( ln_reg_id, 'ln_total_thhold_amt='||ln_total_thhold_amt||', ln_total_thhold_base_amt='||ln_total_thhold_base_amt);*/ --commented by bgowrava for bug#5631784
2205
2206 /** Updating a threshold amount may change threshold slab */
2207
2208 /** Fetch threshold header information to call get_thrthe API */
2209 open c_get_thhold_hdr_info;
2210 fetch c_get_thhold_hdr_info into ln_regime_id
2211 ,lv_org_tan_no
2212 ,lv_party_type
2213 ,ln_party_id
2214 ,ln_fin_year
2215 ,ln_curr_thhold_slab_id;
2216 close c_get_thhold_hdr_info;
2217
2218 /** Get what is new threshold_slab_id*/
2219
2220 /* jai_cmn_debug_contexts_pkg.print ( ln_reg_id, 'Before JAI_RGM_THHOLD_PROC_PKG.GET_THRESHOLD_SLAB_ID'); */ --commented by bgowrava for bug#5631784
2221 jai_rgm_thhold_proc_pkg.get_threshold_slab_id
2222 ( p_regime_id => ln_regime_id
2223 , p_org_tan_no => lv_org_tan_no
2224 , p_party_type => lv_party_type
2225 , p_party_id => ln_party_id
2226 , p_fin_year => ln_fin_year
2227 , p_source_trx_date => p_source_trx_date
2228 , p_called_from => jai_constants.tcs_event_surcharge
2229 , p_threshold_slab_id => ln_new_thhold_slab_id
2230 , p_process_flag => p_process_flag
2231 , p_process_message => p_process_message
2232 );
2233 /* jai_cmn_debug_contexts_pkg.print ( ln_reg_id, 'After JAI_RGM_THHOLD_PROC_PKG.GET_THRESHOLD_SLAB_ID');
2234 jai_cmn_debug_contexts_pkg.print ( ln_reg_id, 'p_process_flag='||p_process_flag||',p_process_message='||p_process_message);*/ --commented by bgowrava for bug#5631784
2235 if p_process_flag <> jai_constants.successful then
2236 return;
2237 end if;
2238
2239 p_new_thhold_slab_id := ln_new_thhold_slab_id;
2240
2241 if nvl(ln_new_thhold_slab_id,-9999) <> nvl(ln_curr_thhold_slab_id,-9999) then
2242 /** Slab is changed. Hence update threshold slab in the jai_rgm_thresholds */
2243 update jai_rgm_thresholds
2244 set threshold_slab_id = ln_new_thhold_slab_id
2245 ,last_update_date = sysdate
2246 ,last_updated_by = ln_user_id
2247 ,last_update_login = ln_login_id
2248 where threshold_id = p_threshold_id;
2249
2250 p_thhold_slab_change_flag := jai_constants.yes;
2251
2252 end if;
2253
2254 /** Deregister and return */
2255 <<deregister_and_return>>
2256 /* jai_cmn_debug_contexts_pkg.deregister (pn_reg_id => ln_reg_id);*/ --commented by bgowrava for bug#5631784
2257 return;
2258
2259 exception
2260
2261 when others then
2262 p_thhold_slab_change_flag := null;
2263 p_new_thhold_slab_id := null;
2264 p_process_flag := jai_constants.unexpected_error;
2265 p_process_message := lv_context||'->'||sqlerrm;
2266 /* jai_cmn_debug_contexts_pkg.print(ln_reg_id,sqlerrm,jai_cmn_debug_contexts_pkg.summary);
2267 jai_cmn_debug_contexts_pkg.print_stack; */ --commented by bgowrava for bug#5631784
2268
2269 end sync_threshold_header;
2270 /*------------------------------------------------------------------------------------------------------------*/
2271
2272 end jai_rgm_thhold_proc_pkg;