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