[Home] [Help]
PACKAGE BODY: APPS.JAI_CMN_RGM_TAX_DIST_PKG
Source
1 PACKAGE BODY jai_cmn_rgm_tax_dist_pkg AS
2 /* $Header: jai_cmn_rgm_dist.plb 120.14.12010000.6 2009/02/04 12:21:14 vkaranam ship $ */
3 /***************************************************************************************************
4 CREATED BY : ssumaith
5 CREATED DATE : 11-JAN-2005
6 ENHANCEMENT BUG : 4068911
7 PURPOSE : To get the balances , to insert records into repository
8 CALLED FROM : jai_cmn_rgm_settlement_pkg , JAIRGMDT.fmb , JAIRGMDT.fmb
9 /* -------------------------------------------------------------------------------------------------------------------
10 1. 08-Jun-2005 File Version 116.2. Object is Modified to refer to New DB Entity names in place of Old DB Entity Names
11 as required for CASE COMPLAINCE.
12
13 2. 13-Jun-2005 rchandan for bug#4428980. File Version: 116.3
14 Removal of SQL LITERALs is done
15
16 3. 17-Aug-2005 Ramananda for bug#4557267 (Fwd porting for the bug 4276280 ) during R12 Sanity Testing. File Version 120.2
17 The Settlement form was erroring out when get_details button was pressed giving a
18 message "cannot insert NULL into debit balances". This was happening if the last
19 settlement balnce amount was NULL. From now it will be taken as zero instead of NULL.
20 While inserting into temp table nvl check is added for ln_settled_credit_balance
21 and ln_settled_debit_balance.
22 NVL() is added to the following parameters:
23 p_debit_amt => nvl(ln_settled_debit_balance,0) + delta_rec.debit_amt
24 p_credit_amt => nvl(ln_settled_credit_balance,0) + delta_rec.credit_amt
25 4. 25-Aug-2005 Bug4568078. Added by Lakshmi Gopalsami Version 120.3
26 (1) Added parameter p_pla_balance in procedure
27 insert_records_into_temp
28 (2) Added pla_balance while inserting into jai_rgm_balance_t
29 (3) Added nvl(pla_balance,0) in cursor c_balance_cur in
30 procedure calculate_balances_for_io. Added cursor
31 c_pla_cess_balance to fetch the pla_balance and
32 passed the same to insert_records_into_temp
33 (4) Passed NULL for p_pla_balance in the call to
34 insert_records_into_temp in procedure
35 calculate_balances_for_ou.
36
37 Dependencies:(Functional+Compilation)
38 ------------
39 JAIRGMDT.fmb 120.3
40
41 5 27/03/2006 Hjujjuru for Bug 5096787 , File Version 120.4
42 Spec changes have been made in this file as a part og Bug 5096787.
43 Now, the r12 Procedure/Function specs is in this file are in
44 sync with their corrsponding 11i counterparts
45
46 6. 19/12/2006 CSahoo for Bug 5073553, File Version 120.5
47 1.Changed the procedure jai_rgm_distribution_pkg.calculate_balances_for_io such that in case
48 the pla balance is -ve, populate the column jai_rgm_balance_tmps debit_amt and jai_rgm_balance_tmps pla_balance as 0
49 else (+ve value for PLA balance) let the value of o be populated into jai_rgm_balance_tmps debit_amt and pla_balance
50 would be the register pla_amt amount for the IO.
51 2.Added a new function f_get_io_register in both package spec and body .
52 7. 30/01/2007 SACSETHI FOR BUG#5631784. FILE VERSION 120.7
53 FORWARD PORTING BUG FROM 11I BUG 4742259
54 NEW ENH: TAX COLLECTION AT SOURCE IN RECEIVABLES
55 Changes -
56
57 OBJECT TYPE OBJECT NAME CHANGE DESCRIPTION
58 --------------------------------------------------------------------------------------
59 PROCEDURE PUNCH_SETTLEMENT_ID ARGUMENT ADDED P_TAN_NO IS ADDED
60 PROCEDURE PUNCH_SETTLEMENT_ID ARGUMENT ADDED P_ITEM_CLASSIFICATION IS ADDED
61 PROCEDURE PUNCH_SETTLEMENT_ID CODE ADDED UPDATATION OF SETTLEMENT_ID IN TABLE
62 JAI_RGM_REFS_ALL FOR TCS
63 PROCEDURE GET_BALANCES ARGUMENT ADDED P_ITEM_CLASSIFICATIONIS ADDED
64 PROCEDURE GET_BALANCES CURSOR ADDED CUR_REGIME_CODE IS ADDED
65 PROCEDURE GET_BALANCES CODE ADDED PROCEDURE CALCULATE_RGM_BALANCES FOR TCS
66 PROCEDURE CALCULATE_RGM_BALANCES NEW CREATED NEWLY PROCEDURE ADDED FOR TCS
67 ----------------------------------------------------------------------------------------------------------------
68 8 23/04/2007 bduvarag for the Bug#5879769, file version 120.8
69 Forward porting the changes done in 11i bug#5694855
70
71 9 7-June-2007 ssawant for bug 5662296
72 Forward porting R11 bugs 5642053 and 4346527 to R12 bug 5662296.
73
74 10. 09-June-2007 CSahoo for BUG#6109941 , FileVersion 120.11
75 Added the sh cess types.
76
77 11. 16-Jul-2007 CSahoo for bug#6235971, File Version 120.13
78 added the following and condition in the for loop
79 "AND a.settlement_id IS NULL".
80 12. 10-OCT-2008 JMEENA for bug#7445742
81 Modified procedure calculate_balances_for_ou
82 and added condition source <> 'VAT REVERSAL' in the query.
83 13.12-Nov-2008 Changes by nprashar for bug 6359082, Forward port the changes from 11i bug 6348081.
84
85 14. 20-Nov-2008 Changes by nprashar for bug # 7525691, FP changes of 11 i bug 7518230.
86 Issue : SVC TX SETTLEMENT PROCESS WITH DIFF SVC TYPES DIDN'T CREATE NETTING SERVICE JE
87 Fix: modified the code in the procedure insert_records_into_register. Added a variable
88 lv_balancing_entry. The value is set as N for settlement else it is null. Passed this
89 variable to the procedure insert_repository_entry
90
91 15 30-dec-2008 Vkaranam for bug#6773684,file version 120.4.12000000.8/120.14.12010000.5/120.20
92 Issue:
93 SERVICE TAX DISTRIBUTION IN PLA/RG DOES NOT RESULT IN PLA REGISTER
94 Reason:
95 Cursor 'cur_get_dist_plg_rg' is used to fetch "Service Tax Distribution in PLA/RG" setup value.
96 In this cursor the organization_type is given as 'OU'.But after the 'Service tax by IO' enhancement\
97 service tax at OU level is not supported.Due to this 'cur_get_dist_plg_rg' always return null and the code in function f_get_io_register
98 always return 'RG'.
99 Fix:
100 1)
101 Changes are done in function f_get_io_register .
102 1.1 Removed organization_type='OU' condition from Cursor 'cur_get_dist_plg_rg'.
103 1.2 Added a conditon to get the value as 'RG' ,If the setup is not done.
104 2)
105 while calling the f_get_io_register_type ,party id is passed as p_to_party_id if the transfer is "Service --Excise"
106 and p_from_party_id is passed if the transfer is from "Excise-- Service" .
107 Changes are done as per the above.
108
109 15 04-feb-2009 Vkaranam for bug#6773684,file version 120.4.12000000.9/120.14.12010000.6/120.21
110 Revereted back the changes done in fp bug# 7525691 as the fix is not yet tested/released.
111
112
113 -- #
114 -- # Change History -
115 -- # Future Dependencies For the release Of this Object:-
116 -- # (Please add a row in the section below only if your bug introduces a dependency due to spec change/ A new call to a object/
117 -- # A datamodel change )
118 --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
119 Current Version Current Bug Dependent Files Version Author Date Remarks
120 Of File On Bug/Patchset Dependent On
121 ------------------------------------------------------------------------------------------------------------------------------------------------------------------
122 115.1 4245365 4245089 rchandan 17/Mar/05 Changes made to implement VAT
123 115.2 4245365 4245089 rchandan 20/03/2005 Observations in VAT. From now when we are settling
124 balances the opening balance of the last settlemnt date is not considered if it was completely settled.
125 Only the transaction amount in the delta period is taken into consideration. If it is not settled then
126 the settlement balances are taken into consideration
127
128
129 11.22-jun-2007 kunkumar made changes for bug#6127194 file 120.11
130 Added package body to create_io_register_entry and
131 made calls to the proc from insert_into_register proc.
132
133 --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
134 ***************************************************************************************************/
135
136
137 PROCEDURE insert_records_into_temp(
138 p_request_id NUMBER ,
139 p_regime_id NUMBER ,
140 p_party_type VARCHAR2 ,
141 p_party_id NUMBER ,
142 p_location_id NUMBER ,
143 p_bal_date DATE ,
144 p_tax_type VARCHAR2 ,
145 p_debit_amt NUMBER ,
146 p_credit_amt NUMBER ,
147 /* Bug4568078. Added by Lakshmi Gopalsami */
148 p_pla_balance NUMBER default NULL,
149 p_service_type_code VARCHAR2 DEFAULT NULL/*Bug 5879769 bduvarag*/
150 )
151 is
152 /* Added by Ramananda for bug#4407165 */
153 lv_object_name CONSTANT VARCHAR2(61) := 'jai_cmn_rgm_tax_dist_pkg.insert_records_into_temp';
154
155 BEGIN
156 INSERT INTO JAI_RGM_BALANCE_T
157 (
158 REQUEST_ID ,
159 REGIME_ID ,
160 PARTY_TYPE ,
161 PARTY_ID ,
162 LOCATION_ID ,
163 BALANCE_DATE ,
164 TAX_TYPE ,
165 DEBIT_AMT ,
166 CREDIT_AMT ,
167 CREATION_DATE ,
168 CREATED_BY ,
169 LAST_UPDATE_DATE ,
170 LAST_UPDATED_BY ,
171 LAST_UPDATE_LOGIN ,
172 program_application_id,
173 program_id,
174 program_login_id,
175 /* Bug 4568078. Added by Lakshmi Gopalsami */
176 pla_balance,
177 service_type_code /*Bug 5879769 bduvarag*/
178 )
179 VALUES
180 (
181 p_request_id ,
182 p_regime_id ,
183 p_party_type ,
184 p_party_id ,
185 p_location_id ,
186 p_bal_date ,
187 p_tax_type ,
188 round(p_debit_amt,ln_rounding_precision) ,
189 round(p_credit_amt,ln_rounding_precision) ,
190 sysdate ,
191 fnd_global.user_id ,
192 sysdate ,
193 fnd_global.user_id ,
194 fnd_global.login_id ,
195 fnd_profile.value('PROG_APPL_ID'),
196 fnd_profile.value('CONC_PROGRAM_ID'),
197 fnd_profile.value('CONC_LOGIN_ID'),
198 /* Bug 4568078. Added by Lakshmi Gopalsami */
199 p_pla_balance,
200 p_service_type_code/*Bug 5879769 bduvarag*/
201 );
202 /* Added by Ramananda for bug#4407165 */
203 EXCEPTION
204 WHEN OTHERS THEN
205 FND_MESSAGE.SET_NAME('JA','JAI_EXCEPTION_OCCURED');
206 FND_MESSAGE.SET_TOKEN('JAI_PROCESS_MSG', lv_object_name ||'. Err:'||sqlerrm );
207 app_exception.raise_exception;
208 END insert_records_into_temp;
209
210
211
212
213 PROCEDURE calculate_balances_for_io(p_regime_id number ,
214 p_balance_date date ,
215 p_request_id number,
216 p_service_type_code VARCHAR2 DEFAULT NULL/*Bug 5879769 bduvarag*/
217 ) is
218 /* Added by Ramananda for bug#4407165 */
219 lv_object_name CONSTANT VARCHAR2(61) := 'jai_cmn_rgm_tax_dist_pkg.calculate_balances_for_io';
220 /*
221 ||CSahoo. Bug 5073553. removed the where clause that the nvl(rg23A_balance,0) + nvl(rg23c_balance,0) <> 0
222 ||instead now the criteria should be that in the form JAIRGMDT.fmb , user should not be able to select a IO with this sum = 0.
223 */
224 CURSOR c_balance_cur is
225 SELECT org_unit_id org_unit_id ,
226 organization_id party_id ,
227 location_id location_id ,
228 'EXCISE' tax_type ,
229 nvl(rg23A_balance,0) + nvl(rg23c_balance,0) Balance ,
230 'IO' party_type,
231 /* Bug 4568078. Added by LGOPALSA */
232 nvl(pla_balance,0) pla_balance
233 FROM JAI_CMN_RG_BALANCES;
234 --WHERE nvl(rg23A_balance,0) + nvl(rg23c_balance,0) <> 0 ;
235
236 CURSOR c_cess_balance (cp_org_unit_id number) is
237 SELECT SUM(balance)
238 FROM JAI_CMN_RG_OTH_BALANCES
239 WHERE org_unit_id = cp_org_unit_id
240 AND register_type IN (jai_constants.reg_rg23a ,jai_constants.reg_rg23c)--rchandan for bug#4428980
241 AND tax_type IN (jai_constants.tax_type_cvd_edu_cess,jai_constants.tax_type_exc_edu_cess);--rchandan for bug#4428980
242 ln_cess_balance number :=0;
243 /* Bug 4568078. Added by Lakshmi Gopalsami */
244
245 CURSOR c_pla_cess_balance (cp_org_unit_id number) is
246 SELECT SUM(balance)
247 FROM JAI_CMN_RG_OTH_BALANCES
248 WHERE org_unit_id = cp_org_unit_id
249 AND register_type = jai_constants.reg_pla
250 AND tax_type IN (jai_constants.tax_type_cvd_edu_cess,jai_constants.tax_type_exc_edu_cess) ;
251
252 --added by csahoo for bug#6109941, start
253 CURSOR c_sh_cess_balance (cp_org_unit_id number) is
254 SELECT SUM(balance)
255 FROM JAI_CMN_RG_OTH_BALANCES
256 WHERE org_unit_id = cp_org_unit_id
257 AND register_type IN (jai_constants.reg_rg23a ,jai_constants.reg_rg23c)
258 AND tax_type IN (jai_constants.tax_type_sh_cvd_edu_cess,jai_constants.tax_type_sh_exc_edu_cess);
259
260
261
262 CURSOR c_sh_pla_cess_balance (cp_org_unit_id number) is
263 SELECT SUM(balance)
264 FROM JAI_CMN_RG_OTH_BALANCES
265 WHERE org_unit_id = cp_org_unit_id
266 AND register_type = jai_constants.reg_pla
267 AND tax_type IN (jai_constants.tax_type_sh_cvd_edu_cess,jai_constants.tax_type_sh_exc_edu_cess) ;
268
269 ln_sh_cess_balance number :=0;
270 ln_sh_pla_cess_balance NUMBER :=0;
271 ln_sh_debit_amt NUMBER :=0;
272 -- --added by csahoo for bug#6109941, end
273
274 ln_rg23_cess_balance NUMBER :=0;
275 ln_pla_cess_balance NUMBER :=0;
276 ln_debit_amt NUMBER :=0;
277 ln_pla_balance NUMBER :=0;
278 /* End for bug 4568078. Added by Lakshmi Gopalsami */
279
280 BEGIN
281 FOR bal_rec in c_balance_cur
282 LOOP
283
284 /*
285 ||Start of bug 5073553.Added by CSahoo
286 ||Initialize the variables
287 */
288
289 /*Start of bug 5073553*/
290 ln_debit_amt := 0;
291 ln_pla_balance := 0;
292 ln_rg23_cess_balance := 0;
293 ln_pla_cess_balance := 0;
294
295 ln_sh_cess_balance := 0; --added by csahoo for bug#6109941
296 ln_sh_pla_cess_balance := 0; --added by csahoo for bug#6109941
297
298 IF nvl(bal_rec.pla_balance,0) < 0 THEN
299 /*
300 ||-ve pla balance, then the amount should appear as the debit amt in the jai_rgm_balance_tmps table
301 */
302 ln_debit_amt := bal_rec.pla_balance;
303 ELSE
304 /*
305 || +ve pla balance then the amount should appear in the pla_balance
306 */
307 ln_pla_balance := bal_rec.pla_balance;
308 END IF;
309 /* End of bug 5073553*/
310 insert_records_into_temp(
311 p_request_id => p_request_id ,
312 p_regime_id => p_regime_id ,
313 p_party_type => bal_rec.party_type ,
314 p_party_id => bal_rec.party_id ,
315 p_location_id => bal_rec.location_id ,
316 p_bal_date => p_balance_date ,
317 p_tax_type => bal_rec.tax_type ,
318 /* changed by CSahoo for bug 5073553.
319 ||put the variable ln_debit_amt instead of 0
320 */
321 p_debit_amt => ln_debit_amt ,
322 p_credit_amt => bal_rec.balance ,
323 p_pla_balance => ln_pla_balance,
324 /* Bug 4568078. Added by Lakshmi Gopalsami */
325 p_service_type_code => p_service_type_code/*Bug 5879769 bduvarag*/
326 );
327
328 ln_debit_amt := 0; -- Added by CSahoo, BUG#5073553
329 OPEN c_cess_balance(bal_rec.org_unit_id);
330 FETCH c_cess_balance into ln_cess_balance;
331 CLOSE c_cess_balance;
332
333 OPEN c_pla_cess_balance(bal_rec.org_unit_id);
334 FETCH c_pla_cess_balance into ln_pla_cess_balance;
335 CLOSE c_pla_cess_balance;
336
337 /*
338 ||Start of bug 5073553.added by CSahoo
339 ||If the cess balance is less than 0 then the same should appear in the debit_amt column of the jai_rgm_balance_tmps table
340 || else it should appear in the pla_balance column
341 */
342 IF nvl(ln_pla_cess_balance,0) < 0 THEN
343 ln_debit_amt := ln_pla_cess_balance;
344 ln_pla_cess_balance := 0 ;
345 END IF;
346 /* End of bug 5073553*/
347 insert_records_into_temp(
348 p_request_id => p_request_id ,
349 p_regime_id => p_regime_id ,
350 p_party_type => bal_rec.party_type ,
351 p_party_id => bal_rec.party_id ,
352 p_location_id => bal_rec.location_id ,
353 p_bal_date => p_balance_date ,
354 p_tax_type => 'EXCISE-CESS' ,
355 p_debit_amt => ln_debit_amt , -- Added by CSahoo, BUG#5073553
356 p_credit_amt => ln_cess_balance ,
357 p_pla_balance => ln_pla_cess_balance,
358 p_service_type_code => p_service_type_code/*Bug 5879769 bduvarag*/
359 );
360 --added by csahoo for bug#6109941
361 --start
362 ln_sh_debit_amt := 0;
363 OPEN c_sh_cess_balance(bal_rec.org_unit_id);
364 FETCH c_sh_cess_balance into ln_sh_cess_balance;
365 CLOSE c_sh_cess_balance;
366
367 OPEN c_sh_pla_cess_balance(bal_rec.org_unit_id);
368 FETCH c_sh_pla_cess_balance into ln_sh_pla_cess_balance;
369 CLOSE c_sh_pla_cess_balance;
370
371 IF nvl(ln_sh_pla_cess_balance,0) < 0 THEN
372 ln_sh_debit_amt := ln_sh_pla_cess_balance;
373 ln_sh_pla_cess_balance := 0 ;
374 END IF;
375
376
377 insert_records_into_temp(
378 p_request_id => p_request_id ,
379 p_regime_id => p_regime_id ,
380 p_party_type => bal_rec.party_type ,
381 p_party_id => bal_rec.party_id ,
382 p_location_id => bal_rec.location_id ,
383 p_bal_date => p_balance_date ,
384 p_tax_type => 'EXCISE_SH_EDU_CESS' ,
385 p_debit_amt => ln_sh_debit_amt ,
386 p_credit_amt => ln_sh_cess_balance ,
387 p_pla_balance => ln_sh_pla_cess_balance,
388 p_service_type_code => p_service_type_code
389 );
390 --added by csahoo for bug#6109941, end
391
392 END LOOP;
393 /* Added by Ramananda for bug#4407165 */
394 EXCEPTION
395 WHEN OTHERS THEN
396 FND_MESSAGE.SET_NAME('JA','JAI_EXCEPTION_OCCURED');
397 FND_MESSAGE.SET_TOKEN('JAI_PROCESS_MSG', lv_object_name ||'. Err:'||sqlerrm );
398 app_exception.raise_exception;
399 END calculate_balances_for_io;
400
401
402 PROCEDURE punch_settlement_id( p_regime_id number,
403 p_settlement_id number ,
404 p_regn_id number ,
405 p_balance_date date,
406 P_TAN_NO VARCHAR2 DEFAULT NULL, -- ADDED BY SACSETHI ON 30-01-2007 FOR BUG 5631784
407 p_org_id NUMBER default NULL,/*rchandan for bug#5642053*/
408 p_location_id NUMBER default NULL,/*rchandan for bug#5642053*/
409 P_ITEM_CLASSIFICATION VARCHAR2 DEFAULT NULL, -- ADDED BY SACSETHI ON 30-01-2007 FOR BUG 5631784
410 p_regn_no VARCHAR2 default NULL/*6835541*/
411 )
412 is
413 /* Added by Ramananda for bug#4407165 */
414 lv_object_name CONSTANT VARCHAR2(61) := 'jai_cmn_rgm_tax_dist_pkg.punch_settlement_id';
415
416 CURSOR cur_regime_code IS /* 4245365*/
417 SELECT regime_code
418 FROM JAI_RGM_DEFINITIONS -- ADDED BY SACSETHI ON 30-01-2007 FOR BUG 5631784
419 WHERE regime_id = p_regime_id;
420 lv_regime JAI_RGM_DEFINITIONS.regime_code%TYPE;
421
422 BEGIN
423 OPEN cur_regime_code; /* 4245365*/
424 FETCH cur_regime_code INTO lv_regime;
425 CLOSE cur_regime_code;
426 IF lv_regime IN ('SERVICE') THEN /*6835541.removed VAT*/
427 UPDATE jai_Rgm_trx_records
428 SET settlement_id = p_settlement_id
429 /*Bug 5879769 bduvarag*/
430 WHERE organization_id = p_org_id
431 AND location_id = p_location_id
432 AND regime_code = lv_regime
433 AND trunc(transaction_date) BETWEEN nvl(jai_cmn_rgm_settlement_pkg.get_last_settlement_date(p_regime_id,organization_id,location_id),g_start_date) AND p_balance_date
434 AND settlement_id IS NULL; /* added by ssawant for bug 5662296*/
435
436 ELSIF lv_regime IN ('VAT') THEN /*6835541*/
437
438 UPDATE jai_Rgm_trx_records
439 SET settlement_id = p_settlement_id
440 WHERE (organization_id,location_id) in
441 (SELECT organization_id,location_id
442 FROM JAI_RGM_ORG_REGNS_V
443 WHERE registration_id = p_regn_id
444 AND attribute_value = nvl(p_regn_no, attribute_value) -- 6835541. Added by Lakshmi Gopalsami
445 AND regime_code = 'VAT'
446 AND organization_id = nvl(p_org_id,organization_id)
447 AND location_id = nvl(p_location_id,location_id)
448 )
449 AND trunc(transaction_date) BETWEEN nvl(jai_cmn_rgm_settlement_pkg.get_last_settlement_date(p_regime_id,organization_id,location_id),g_start_date) AND p_balance_date
450 AND settlement_id IS NULL;
451
452 ELSIF lv_regime = jai_constants.tcs_regime THEN
453 UPDATE JAI_RGM_REFS_ALL
454 SET SETTLEMENT_ID = P_SETTLEMENT_ID
455 WHERE ORG_TAN_NO = P_TAN_NO AND
456 ITEM_CLASSIFICATION = P_ITEM_CLASSIFICATION AND
457 TRUNC(SOURCE_DOCUMENT_DATE) <= P_BALANCE_DATE AND
458 SETTLEMENT_ID IS NULL;
459 -- end 5631784
460 END IF;
461 /* Added by Ramananda for bug#4407165 */
462 EXCEPTION
463 WHEN OTHERS THEN
464 FND_MESSAGE.SET_NAME('JA','JAI_EXCEPTION_OCCURED');
465 FND_MESSAGE.SET_TOKEN('JAI_PROCESS_MSG', lv_object_name ||'. Err:'||sqlerrm );
466 app_exception.raise_exception;
467 END punch_settlement_id;
468
469
470 PROCEDURE calculate_balances_for_ou(p_regime_id number ,
471 p_balance_date date ,
472 p_request_id number ,
473 p_org_id number ,
474 p_org_type varchar2 ,
475 p_regn_id number ,
476 p_regn_no varchar2 ,
477 p_settlement_id number ,
478 p_called_from varchar2,
479 p_location_id number default null, /*rchandan for bug#5642053*/
480 p_service_type_code varchar2 default null -- bduvarag for Bug 5694855
481 ) is
482 /* Added by Ramananda for bug#4407165 */
483 lv_object_name CONSTANT VARCHAR2(61) := 'jai_cmn_rgm_tax_dist_pkg.calculate_balances_for_ou';
484 lv_source_trx_type jai_rgm_trx_records.source_trx_type%TYPE ;--rchandan for bug#4428980
485 lv_reg_type jai_rgm_registrations.registration_type%TYPE ;
486
487 CURSOR c_regime_code IS
488 SELECT regime_code
489 FROM JAI_RGM_DEFINITIONS
490 WHERE regime_id = p_regime_id;
491 -- ld_trx_date DATE; /* commented by ssawant for bug 5662296*/
492 lv_regime_code JAI_RGM_DEFINITIONS.regime_code%TYPE;
493
494 /*Bug 5879769 bduvarag start*/
495
496 CURSOR cur_inv_payment (cp_service_type_code VARCHAR2) /*Added by nprashar for bug # 6359082*/
497 IS
498 /*
499 || This cursor is used to get the total invoice amount paid
500 || when the last settlement was made
501 */
502 SELECT sum(credit_amount)
503 FROM jai_rgm_trx_records
504 WHERE regime_primary_regno = p_regn_no
505 AND source_trx_type = 'Invoice Payment'
506 AND service_type_code = cp_service_type_code /*Added by nprashar for bug # 6359082*/
507 AND settlement_id = ( SELECT MAX(jbal.settlement_id)
508 FROM jai_rgm_stl_balances jbal,jai_rgm_settlements jstl
509 WHERE jbal.settlement_id = jstl.settlement_id
510 AND jstl.primary_registration_no = p_regn_no
511 AND jbal.party_type = p_org_type
512 AND jbal.party_id = p_org_id
513 AND nvl(jbal.location_id,-999) = nvl(p_location_id,-999)
514 AND jbal.settlement_id <> nvl(p_settlement_id,-999) /*This clause is used to exclude the current settlement*/
515 AND jbal.service_type_code = cp_service_type_code); /*Added by nprashar for bug # 6359082*/
516
517 CURSOR cur_inv_payment_dist_io(cp_org_type VARCHAR2,
518 cp_org_id NUMBER ,
519 cp_tax_type VARCHAR2,
520 cp_location_id NUMBER ,
521 cp_service_type_code VARCHAR2
522 )
523 IS
524 /*
525 || This cursor is used to get the total invoice amount paid
526 || when the last settlement was made. This is same as above cursor but it does not use registration no.
527 || This cursor used when procedure is invoked from Distribution and so it does not have registration
528 || details
529 */
530 SELECT credit_amount
531 FROM jai_rgm_trx_records
532 WHERE source_trx_type = 'Invoice Payment'
533 AND service_type_code = cp_service_type_code
534 AND settlement_id = ( SELECT MAX(jbal.settlement_id)
535 FROM jai_rgm_stl_balances jbal,jai_rgm_settlements jstl
536 WHERE jbal.settlement_id = jstl.settlement_id
537 AND jbal.party_type = cp_org_type
538 AND jbal.party_id = cp_org_id
539 AND jbal.location_id = nvl(cp_location_id,jbal.location_id)/*5694855*/
540 AND jbal.tax_type = cp_tax_type
541 AND jbal.service_type_code = cp_service_type_code
542 );
543
544
545 CURSOR cur_inv_payment_dist(cp_org_type VARCHAR2,
546 cp_org_id NUMBER ,
547 cp_tax_type VARCHAR2
548 )
549 IS
550 /*
551 || This cursor is used to get the total invoice amount paid
552 || when the last settlement was made. This is same as above cursor but it does not use registration no.
553 || This cursor used when procedure is invoked from Distribution and so it does not have registration
554 || details
555 */
556 SELECT credit_amount
557 FROM jai_rgm_trx_records
558 WHERE /*regime_primary_regno = p_regn_no*/ -- Commented, Harshita for Bug 5694855
559 source_trx_type = 'Invoice Payment'
560 AND settlement_id = ( SELECT MAX(jbal.settlement_id)
561 FROM jai_rgm_stl_balances jbal,jai_rgm_settlements jstl
562 WHERE jbal.settlement_id = jstl.settlement_id
563 AND jbal.party_type = cp_org_type
564 AND jbal.party_id = cp_org_id
565 AND jbal.tax_type = cp_tax_type
566 );
567
568 CURSOR cur_balances (cp_service_type_code VARCHAR2) /*Added by nprashar for bug #6359082*/
569 IS
570 /*
571 || This cursor is used to retrieve the sum of credit and debit balances as on
572 || last settlement date for the given registration number,organization and location grouped at the tax type
573 */
574 SELECT sum(credit_balance) credit_balance,sum(debit_balance) debit_balance
575 FROM jai_rgm_stl_balances
576 WHERE settlement_id = ( SELECT MAX(jbal.settlement_id)
577 FROM jai_rgm_stl_balances jbal,jai_rgm_settlements jstl
578 WHERE jbal.settlement_id = jstl.settlement_id
579 AND jstl.primary_registration_no = p_regn_no
580 AND jbal.party_type = p_org_type
581 AND jbal.party_id = p_org_id
582 AND nvl(jbal.location_id,-999) = nvl(p_location_id,-999)
583 AND jbal.service_type_code = NVL(cp_service_type_code,jbal.service_type_code) /*Added by nprashar for bug #6359082*/
584 AND jbal.settlement_id <> p_settlement_id/*This clause is used to exclude the current settlement*/
585 )
586 GROUP BY tax_type;
587
588 /*6835541..start*/
589
590 CURSOR cur_inv_payment_vat(cp_organization_type VARCHAR2,
591 cp_organization_id NUMBER,
592 cp_location_id NUMBER
593 )
594 IS
595 /*
596 || This cursor is used to get the total invoice amount paid
597 || when the last settlement was made for VAT regime
598 */
599 SELECT sum(credit_amount)
600 FROM jai_rgm_trx_records
601 WHERE regime_primary_regno = p_regn_no
602 AND source_trx_type = 'Invoice Payment'
603 AND settlement_id = ( SELECT MAX(jbal.settlement_id)
604 FROM jai_rgm_stl_balances jbal,
605 jai_rgm_settlements jstl,
606 jai_rgm_definitions jrg
607 WHERE jbal.settlement_id = jstl.settlement_id
608 AND jrg.regime_id = jstl.regime_id
609 AND jrg.regime_code = 'VAT'
610 AND jstl.primary_registration_no = p_regn_no
611 AND jbal.party_type = cp_organization_type
612 AND jbal.party_id = cp_organization_id
613 AND jbal.location_id = cp_location_id
614 AND jbal.settlement_id <> nvl(p_settlement_id,-999) /*This clause is used to exclude the current settlement*/
615 );
616
617 CURSOR cur_balances_vat(cp_organization_type VARCHAR2,
618 cp_organization_id NUMBER,
619 cp_location_id NUMBER
620 )
621 IS
622 /*
623 || This cursor is used to retrieve the sum of credit and debit balances as on
624 || last settlement date for the given registration number,organization and location grouped at the tax type
625 || for VAT
626 */
627 SELECT sum(credit_balance) credit_balance,sum(debit_balance) debit_balance
628 FROM jai_rgm_stl_balances
629 WHERE settlement_id = ( SELECT MAX(jbal.settlement_id)
630 FROM jai_rgm_stl_balances jbal,
631 jai_rgm_settlements jstl,
632 jai_rgm_definitions jrg
633 WHERE jbal.settlement_id = jstl.settlement_id
634 AND jrg.regime_id = jstl.regime_id
635 AND jrg.regime_code = 'VAT'
636 AND jstl.primary_registration_no = p_regn_no
637 AND jbal.party_type = cp_organization_type
638 AND jbal.party_id = cp_organization_id
639 AND jbal.location_id = cp_location_id
640 AND jbal.settlement_id <> p_settlement_id/*This clause is used to exclude the current settlement*/
641 )
642 GROUP BY tax_type;
643
644
645 /*6835541..end*/
646
647 /*start ...rchandan for bug#5694855*/
648
649 CURSOR c_delta_rec( cp_regime_id NUMBER,
650 cp_regime_code VARCHAR2,
651 cp_organization_type VARCHAR2,
652 cp_organization_id NUMBER,
653 cp_location_id NUMBER,
654 cp_tax_type VARCHAR2
655 )
656 IS
657 SELECT
658 organization_id ,
659 location_id ,
660 tax_type ,
661 nvl(sum(debit_amount),0) debit_amt ,
662 nvl(sum(credit_amount),0) credit_amt
663 FROM
664 jai_rgm_trx_records
665 WHERE trunc(transaction_date) between nvl(jai_cmn_rgm_settlement_pkg.get_last_settlement_date(cp_regime_id,cp_organization_id,cp_location_id),g_start_date) and p_balance_date
666 AND settlement_id IS NULL
667 AND source_trx_type <> 'Invoice Payment'
668 AND organization_id = cp_organization_id
669 AND location_id = cp_location_id
670 AND organization_type = cp_organization_type
671 AND tax_type = cp_tax_type
672 AND regime_code = cp_regime_code
673 AND service_type_code = p_service_type_code
674 GROUP BY
675 organization_id,
676 location_id ,
677 tax_type
678 ORDER BY
679 tax_type;
680
681 r_delta_rec c_delta_rec%ROWTYPE;
682 /*Bug 5879769 bduvarag end*/
683 lv_party_type varchar2(30) ; --:= 'OU'; File.Sql.35 BY Brathod
684 ln_settled_debit_balance number ;
685 ln_settled_credit_balance number ;
686 -- lv_inv_amount jai_rgm_trx_records.credit_amount%type;/* commented by ssawant for bug 5662296*/
687 ln_invoice_amount jai_rgm_trx_records.credit_amount%type;
688 cr_balance jai_rgm_stl_balances.credit_balance%type; /*added by ssawant for bug 5662296*/
689 dr_balance jai_rgm_stl_balances.debit_balance%type; /*added by ssawant for bug 5662296*/
690 ln_settled_flag NUMBER(1) := 0;/*added by ssawant for bug 5662296*/
691
692
693 BEGIN
694 lv_party_type := 'OU'; -- File.Sql.35 by Brathod
695 /* first get the balance as on the last settlement date for the passed org id
696 get the records from the repository for the org id for the dates between the last settlement date and
697 the date passed
698 put the plus into one type of variable and minus into another type of variable
699 finally do the arithmatic on these two variables.
700 */
701 OPEN c_regime_code;
702 FETCH c_regime_code INTO lv_regime_code;
703 CLOSE c_regime_code;
704
705 IF p_called_from = 'SETTLEMENT' THEN
706 IF lv_regime_code = jai_constants.service_regime THEN
707 lv_source_trx_type := 'Invoice Payment';--rchandan for bug#4428980
708 FOR delta_rec in
709 (
710 SELECT a.organization_id ,
711 a.location_id ,/*Bug 5879769 bduvarag*/
712 a.service_type_code ,/*Bug 5879769 bduvarag*/
713 a.tax_type ,
714 nvl(sum(a.debit_amount),0) debit_amt ,
715 nvl(sum(a.credit_amount),0) credit_amt
716 FROM jai_rgm_trx_records a
717 WHERE trunc(transaction_date) BETWEEN nvl(jai_cmn_rgm_settlement_pkg.get_last_settlement_date(p_regime_id,a.organization_id,a.location_id),g_start_date)
718 /*rchandan for bug#5003538*//*rchandan for bug#5642053. Removed +1 from last settlement date*/
719 AND p_balance_date /*5694855.location_id is also passed to get_last_settlement_date*/
720 AND a.settlement_id IS NULL/*rchandan for bug#5642053*/
721 AND a.regime_code = lv_regime_code/*5694855*/
722 AND a.organization_type = p_org_type/*5694855*/
723 AND a.organization_id = nvl(p_org_id,a.organization_id )
724 AND a.location_id = p_location_id/*5694855*/
725 AND a.source_trx_type <> 'Invoice Payment'
726 GROUP BY a.organization_id ,
727 a.location_id ,/*5694855*/
728 a.tax_type ,
729 a.service_type_code /*5694855*/
730 )/*Bug 5879769 bduvarag*/
731 LOOP
732 /*
733 insert the tax types for every operating unit for the delta period
734 ie .. between the last settlement date and the date of transfer.
735 get the debit balance and credit balance as on the last settlement for a given operating unit and tax type
736 and add the value in this table.
737 -- API call to settlement process.
738 */
739 --ld_trx_date := jai_cmn_rgm_settlement_pkg.get_last_settlement_date(delta_rec.organization_id) + 1;/* commented by ssawant for bug 5662296*/
740 ln_settled_debit_balance :=0;
741 ln_settled_credit_balance :=0;
742 jai_cmn_rgm_settlement_pkg.GET_LAST_BALANCE_AMOUNT(
743 pn_regime_id => p_regime_id , /*Bug 5879769 bduvarag*/
744 pn_org_id => delta_rec.organization_id ,
745 pn_location_id => delta_rec.location_id , /*Bug 5879769 bduvarag*/
746 pv_service_type_code => delta_rec.service_type_code ,/*Bug 5879769 bduvarag*/
747 pv_tax_type => delta_rec.tax_type ,
748 pn_debit_amount => ln_settled_debit_balance ,
749 pn_credit_amount => ln_settled_credit_balance
750 );
751 /* OPEN cur_inv_payment(delta_rec.organization_id,NULL,delta_rec.tax_type,'Invoice Payment');
752 FETCH cur_inv_payment INTO lv_inv_amount;
753 CLOSE cur_inv_payment;*/
754 -- start additions by nprashar- bug#6348081
755 -- getting the invoice payment amount for every service type of the last settlement.
756 ln_invoice_amount := 0;
757 cr_balance := 0;
758 dr_balance := 0;
759
760 OPEN cur_inv_payment(delta_rec.service_type_code); --6348081
761 FETCH cur_inv_payment INTO ln_invoice_amount;
762 CLOSE cur_inv_payment;
763
764 FOR rec_balances IN cur_balances(delta_rec.service_type_code) /* Added by nprashar for bug# 6348081*/
765 LOOP --4346527
766 IF nvl(rec_balances.debit_balance,0) - nvl(rec_balances.credit_balance,0) >= 0 THEN
767 /*
768 || This check is put so that only the balances of those tax types which have net balance as
769 || debit are only considered.
770 */
771 cr_balance := nvl(cr_balance,0) + nvl(rec_balances.credit_balance,0);
772 dr_balance := nvl(dr_balance,0) + nvl(rec_balances.debit_balance,0);
773 END IF;
774 END LOOP;
775
776 IF cr_balance + nvl(ln_invoice_amount,0) - dr_balance < 1 THEN --5642053
777 /*
778 ||If the balances at the last settlement balance are settled then flag is one, else flag is zero
779 ||if flag is one then balances at last settlement date are not carried forward. Otherwise they
780 ||are carried forward to the current settlement
781 */
782 ln_settled_flag := 1;
783 ELSE
784 ln_settled_flag := 0;
785 END IF;
786
787 -- End additions by nprashar- bug#6348081
788
789 IF ln_settled_flag = 1 THEN /*added by ssawant for bug 5662296*/
790 ln_settled_debit_balance :=0;
791 ln_settled_credit_balance :=0;
792 END IF;
793 insert_records_into_temp(
794 p_request_id => p_request_id ,
795 p_regime_id => p_regime_id ,
796 p_party_type => p_org_type ,
797 p_party_id => delta_rec.organization_id ,
798 p_location_id => delta_rec.location_id ,
799 p_bal_date => p_balance_date ,
800 p_tax_type => delta_rec.tax_type ,
801 p_debit_amt => nvl(ln_settled_debit_balance,0) + delta_rec.debit_amt , --4557267
802 p_credit_amt => nvl(ln_settled_credit_balance,0) + delta_rec.credit_amt, --4557267
803 /* Bug 4568078. Added by Lakshmi Gopalsami */
804 p_pla_balance => NULL,
805 p_service_type_code => delta_rec.service_type_code/*Bug 5879769 bduvarag*/
806 );
807 END LOOP;
808 punch_settlement_id(p_regime_id => p_regime_id,
809 p_settlement_id => p_settlement_id ,
810 p_regn_id => p_regn_id ,
811 p_balance_date => p_balance_date,
812 p_location_id => p_location_id, /*Bug 5879769 bduvarag*/
813 p_org_id => p_org_id /*added by ssawant for bug 5662296*/
814 );
815 ELSIF lv_regime_code = jai_constants.vat_regime THEN /* 4245365*/
816 /*Even though VAT is for IO , balances are calculated similar to an OU. i.e from
817 jai_rgm_trx_records. hence the implementation is done in this procedure only*/
818 lv_source_trx_type := 'Invoice Payment';--rchandan for bug#4428980
819 FOR delta_rec in
820 (
821 SELECT
822 b.regime_id ,
823 a.organization_id ,
824 a.location_id ,
825 a.tax_type ,
826 a.organization_type , /*6835541*/
827 nvl(sum(a.debit_amount),0) debit_amt ,
828 nvl(sum(a.credit_amount),0) credit_amt
829 FROM
830 jai_rgm_trx_records a, JAI_RGM_ORG_REGNS_V b
831 WHERE trunc(transaction_date) between nvl(jai_cmn_rgm_settlement_pkg.get_last_settlement_date(b.regime_id,a.organization_id,a.location_id) ,g_start_date) and p_balance_date /*+ 1 removed by ssawant for bug 5662296*/
832 AND a.settlement_id IS NULL --added by csahoo for bug#6235971
833 AND b.regime_id = p_regime_id/*5694855 bduvarag*/
834 AND a.regime_code = lv_regime_code/*5694855 bduvarag*/
835 AND a.organization_id = b.organization_id
836 AND a.location_id = b.location_id
837 AND a.organization_type = b.organization_type
838 AND b.registration_id = p_regn_id
839 AND a.organization_id = nvl(p_org_id,a.organization_id )
840 AND a.organization_type = nvl(p_org_type,a.organization_type)
841 AND b.attribute_value = p_regn_no
842 AND a.location_id = nvl(p_location_id,a.location_id)/*rchandan for bug#6835541. Added nvl*/
843 AND a.source_trx_type <> lv_source_trx_type--rchandan for bug#4428980
844 AND a.tax_type <> 'VAT REVERSAL' --Added by JMEENA for bug#7445742
845 GROUP BY a.organization_id , a.tax_type,a.location_id,b.regime_id,a.organization_type /*6835541. added organization_type*/
846 )
847 LOOP
848 /*
849 insert the tax types for every IO for the delta period
850 ie .. between the last settlement date and the date of transfer.
851 get the debit balance and credit balance as on the last settlement for a given IO , Location and tax type
852 and add the value in this table.
853 -- API call to settlement process.
854 */
855 --ld_trx_date := jai_cmn_rgm_settlement_pkg.get_last_settlement_date(delta_rec.regime_id,delta_rec.organization_id,delta_rec.location_id) + 1; /* commented by ssawant for bug 5662296*/
856 ln_settled_debit_balance :=0;
857 ln_settled_credit_balance :=0;
858 jai_cmn_rgm_settlement_pkg.GET_LAST_BALANCE_AMOUNT(
859 pn_regime_id => delta_rec.regime_id,
860 pn_org_id => delta_rec.organization_id ,
861 pn_location_id => delta_rec.location_id ,
862 pv_tax_type => delta_rec.tax_type ,
863 pn_debit_amount => ln_settled_debit_balance ,
864 pn_credit_amount => ln_settled_credit_balance
865 );
866
867 /*6835541..start*/
868 ln_invoice_amount := 0;
869 cr_balance := 0;
870 dr_balance := 0;
871
872 OPEN cur_inv_payment_vat(cp_organization_type => delta_rec.organization_type,
873 cp_organization_id => delta_rec.organization_id,
874 cp_location_id => delta_rec.location_id);
875 FETCH cur_inv_payment_vat INTO ln_invoice_amount;
876 CLOSE cur_inv_payment_vat;
877
878 FOR rec_balances IN cur_balances_vat(cp_organization_type => delta_rec.organization_type,
879 cp_organization_id => delta_rec.organization_id,
880 cp_location_id => delta_rec.location_id
881 )
882 LOOP
883 IF nvl(rec_balances.debit_balance,0) - nvl(rec_balances.credit_balance,0) >= 0 THEN
884 /*
885 || This check is put so that only the balances of those tax types which have net balance as
886 || debit are only considered.
887 */
888 cr_balance := nvl(cr_balance,0) + nvl(rec_balances.credit_balance,0);
889 dr_balance := nvl(dr_balance,0) + nvl(rec_balances.debit_balance,0);
890 END IF;
891 END LOOP;
892
893
894 IF cr_balance + nvl(ln_invoice_amount,0) - dr_balance < 1 THEN --5642053
895 /*
896 ||If the balances at the last settlement balance are settled then flag is one, else flag is zero
897 ||if flag is one then balances at last settlement date are not carried forward. Otherwise they
898 ||are carried forward to the current settlement
899 */
900 ln_settled_flag := 1;
901 ELSE
902 ln_settled_flag := 0;
903 END IF;
904 /*6835541..end*/
905 /* OPEN cur_inv_payment(delta_rec.organization_id,delta_rec.location_id,delta_rec.tax_type,'Invoice Payment');
906 FETCH cur_inv_payment INTO lv_inv_amount;
907 CLOSE cur_inv_payment;*/
908 IF ln_settled_flag = 1 THEN /*added by ssawant for bug 5662296*/
909 ln_settled_debit_balance :=0;
910 ln_settled_credit_balance :=0;
911 END IF;
912 insert_records_into_temp(
913 p_request_id => p_request_id ,
914 p_regime_id => p_regime_id ,
915 p_party_type => delta_rec.organization_type,/*6835541*/
916 p_party_id => delta_rec.organization_id ,
917 p_location_id => delta_rec.location_id ,
918 p_bal_date => p_balance_date ,
919 p_tax_type => delta_rec.tax_type ,
920 p_debit_amt => nvl(ln_settled_debit_balance,0) + delta_rec.debit_amt , --4557267
921 p_credit_amt => nvl(ln_settled_credit_balance,0) + delta_rec.credit_amt, --4557267
922 /* Bug 4568078. Added by Lakshmi Gopalsami */
923 p_pla_balance => NULL
924 );
925 END LOOP;
926 punch_settlement_id(p_regime_id => p_regime_id,
927 p_settlement_id => p_settlement_id ,
928 p_regn_id => p_regn_id ,
929 p_balance_date => p_balance_date,
930 p_org_id => p_org_id ,/*added by ssawant for bug 5662296*/
931 p_location_id => p_location_id, /*added by ssawant for bug 5662296*/
932 p_regn_no => p_regn_no /*6835541*/
933 );
934 END IF;
935 /*Bug 5879769 bduvarag start*/
936 ELSIF p_called_from = 'DISTRIBUTE_IO' THEN
937
938 FOR tax_types_rec in
939 (
940 SELECT regime_id,
941 attribute_code tax_type
942 FROM jai_rgm_org_regns_v
943 WHERE organization_id = p_org_id
944 AND location_id = p_location_id
945 AND organization_type = p_org_type
946 AND registration_type = jai_constants.regn_type_tax_types
947 AND regime_code = lv_regime_code
948 )
949 LOOP
950
951 r_delta_rec := NULL;
952 OPEN c_delta_rec(tax_types_rec.regime_id,
953 lv_regime_code ,
954 p_org_type ,
955 p_org_id ,
956 p_location_id ,
957 tax_types_rec.tax_type
958 );
959 FETCH c_delta_rec INTO r_delta_rec;
960 CLOSE c_delta_rec;
961
962 ln_settled_debit_balance :=0;
963 ln_settled_credit_balance :=0;
964 jai_cmn_rgm_settlement_pkg.GET_LAST_BALANCE_AMOUNT(
965 pn_regime_id => tax_types_rec.regime_id ,
966 pn_org_id => p_org_id ,
967 pn_location_id => p_location_id ,
968 pv_tax_type => tax_types_rec.tax_type ,
969 pn_debit_amount => ln_settled_debit_balance ,
970 pn_credit_amount => ln_settled_credit_balance ,
971 pv_service_type_code => p_service_type_code
972 );
973 ln_invoice_amount := 0;
974
975 OPEN cur_inv_payment_dist_io(p_org_type,p_org_id,tax_types_rec.tax_type,p_location_id,p_service_type_code );
976 FETCH cur_inv_payment_dist_io INTO ln_invoice_amount;
977 CLOSE cur_inv_payment_dist_io;
978
979 ln_settled_credit_balance := nvl(ln_settled_credit_balance,0) + nvl(ln_invoice_amount,0);/*rchandan for bug#5642053*/
980 insert_records_into_temp(
981 p_request_id => p_request_id ,
982 p_regime_id => p_regime_id ,
983 p_party_type => p_org_type ,
984 p_party_id => p_org_id ,
985 p_location_id => p_location_id ,
986 p_bal_date => p_balance_date ,
987 p_tax_type => tax_types_rec.tax_type ,
988 p_debit_amt => nvl(ln_settled_debit_balance,0) + nvl(r_delta_rec.debit_amt,0) ,
989 p_credit_amt => nvl(ln_settled_credit_balance,0) + nvl(r_delta_rec.credit_amt,0) ,
990 p_pla_balance => NULL ,
991 p_service_type_code => p_service_type_code
992 );
993 END LOOP;
994 ELSIF p_called_from = 'DISTRIBUTE_OU' THEN
995 /*Bug 5879769 bduvarag end*/
996 lv_reg_type := 'TAX_TYPES';
997 FOR delta_rec in
998 (
999 SELECT
1000 a.organization_id ,
1001 a.tax_type ,
1002 nvl(sum(debit_amount),0) debit_amt ,
1003 nvl(sum(credit_amount),0) credit_amt
1004 FROM
1005 /*Bug 5879769 bduvarag start*/
1006 jai_rgm_trx_records a
1007 WHERE trunc(transaction_date) between nvl(jai_cmn_rgm_settlement_pkg.get_last_settlement_date(p_regime_id,a.organization_id),g_start_date) and p_balance_date
1008 /*rchandan for bug#5003538*//*rchandan for bug#5642053. Removed +1 from last settlement date*/
1009 AND a.settlement_id IS NULL
1010 AND a.source_trx_type <> 'Invoice Payment'
1011 AND a.organization_id = p_org_id
1012 AND a.organization_type = p_org_type/*5694855*/
1013 GROUP BY
1014 a.organization_id,
1015 a.tax_type
1016 ORDER BY
1017 a.tax_type desc
1018 )/*Bug 5879769 bduvarag end*/
1019 LOOP
1020 ln_settled_debit_balance :=0;
1021 ln_settled_credit_balance :=0;
1022 jai_cmn_rgm_settlement_pkg.GET_LAST_BALANCE_AMOUNT(
1023 pn_org_id => delta_rec.organization_id ,
1024 pv_tax_type => delta_rec.tax_type ,
1025 pn_debit_amount => ln_settled_debit_balance ,
1026 pn_credit_amount => ln_settled_credit_balance
1027 );
1028 -- start block added by ssawant for bug 5662296
1029 ln_invoice_amount := 0;
1030
1031 OPEN cur_inv_payment_dist('OU',delta_rec.organization_id,delta_rec.tax_type);
1032 FETCH cur_inv_payment_dist INTO ln_invoice_amount;
1033 CLOSE cur_inv_payment_dist;
1034
1035 ln_settled_credit_balance := nvl(ln_settled_credit_balance,0) + nvl(ln_invoice_amount,0);
1036 -- end block added by ssawant for bug 5662296
1037
1038 insert_records_into_temp(
1039 p_request_id => p_request_id ,
1040 p_regime_id => p_regime_id ,
1041 p_party_type => p_org_type ,/*Bug 5879769 bduvarag*/
1042 p_party_id => delta_rec.organization_id ,
1043 p_location_id => null ,
1044 p_bal_date => p_balance_date ,
1045 p_tax_type => delta_rec.tax_type ,
1046 p_debit_amt => nvl(ln_settled_debit_balance,0) + delta_rec.debit_amt ,
1047 p_credit_amt => nvl(ln_settled_credit_balance,0) + delta_rec.credit_amt ,
1048 /* Bug 4568078. Added by Lakshmi Gopalsami */
1049 p_pla_balance => NULL
1050 );
1051 END LOOP;
1052 END IF;
1053 /* Added by Ramananda for bug#4407165 */
1054 EXCEPTION
1055 WHEN OTHERS THEN
1056 FND_MESSAGE.SET_NAME('JA','JAI_EXCEPTION_OCCURED');
1057 FND_MESSAGE.SET_TOKEN('JAI_PROCESS_MSG', lv_object_name ||'. Err:'||sqlerrm );
1058 app_exception.raise_exception;
1059 END calculate_balances_for_ou;
1060
1061
1062
1063
1064 --------------------------------------------------------------------------------------------------
1065 -- Added by sacsethi for bug 5631784 on 30-01-2007
1066 -- FOR TCS
1067 -- START -- 5631784
1068 --------------------------------------------------------------------------------------------------
1069 PROCEDURE CALCULATE_RGM_BALANCES( P_REGIME_ID NUMBER ,
1070 P_BALANCE_DATE DATE ,
1071 P_REQUEST_ID NUMBER ,
1072 P_ORG_ID NUMBER ,
1073 P_REGN_ID VARCHAR2 ,
1074 P_TAN_NO VARCHAR2 ,
1075 P_ITEM_CLASSIFICATION VARCHAR2 ,
1076 P_SETTLEMENT_ID NUMBER ,
1077 P_CALLED_FROM VARCHAR2)
1078 IS
1079
1080 CURSOR C_REGIME_CODE IS
1081 SELECT REGIME_CODE
1082 FROM JAI_RGM_DEFINITIONS
1083 WHERE REGIME_ID = P_REGIME_ID;
1084 -- LD_TRX_DATE DATE; /*commented by ssawant for bug 5662296*/
1085 LV_REGIME_CODE JAI_RGM_DEFINITIONS.REGIME_CODE%TYPE;
1086
1087
1088 BEGIN
1089
1090 OPEN C_REGIME_CODE;
1091 FETCH C_REGIME_CODE INTO LV_REGIME_CODE;
1092 CLOSE C_REGIME_CODE;
1093
1094 IF LV_REGIME_CODE = JAI_CONSTANTS.TCS_REGIME THEN
1095 FOR DELTA_REC IN
1096 (
1097 SELECT SUM(DECODE(SIGN(JRT.TAX_AMT),-1,-1 * JRT.TAX_AMT,1,0)) CREDIT_AMOUNT,
1098 SUM(DECODE(SIGN(JRT.TAX_AMT),1,JRT.TAX_AMT,-1,0)) DEBIT_AMOUNT,
1099 DECODE(JRT.TAX_TYPE,'TCS_SURCHARGE_CESS','TCS_CESS',JRT.TAX_TYPE) TAX_TYPE,
1100 JRR.ORGANIZATION_ID,
1101 JRR.REGIME_ID
1102 FROM JAI_RGM_REFS_ALL JRR,
1103 JAI_RGM_TAXES JRT,
1104 JAI_RGM_ORG_REGNS_V JOR
1105 WHERE JRR.TRX_REF_ID = JRT.TRX_REF_ID
1106 AND JRR.ORGANIZATION_ID = JOR.ORGANIZATION_ID
1107 AND JOR.REGIME_ID = JRR.REGIME_ID
1108 AND JOR.REGISTRATION_TYPE = 'TAX_TYPES'
1109 AND JRT.TAX_TYPE = JOR.ATTRIBUTE_CODE
1110 AND JRR.REGIME_ID = P_REGIME_ID
1111 AND JRR.ORG_TAN_NO = P_TAN_NO
1112 AND JRR.ITEM_CLASSIFICATION = P_ITEM_CLASSIFICATION
1113 AND JRR.SETTLEMENT_ID IS NULL
1114 AND TRUNC(JRR.SOURCE_DOCUMENT_DATE) <= P_BALANCE_DATE
1115 GROUP BY DECODE(JRT.TAX_TYPE,'TCS_SURCHARGE_CESS','TCS_CESS',JRT.TAX_TYPE),
1116 JRR.ORGANIZATION_ID,JRR.REGIME_ID
1117 ) LOOP
1118 INSERT_RECORDS_INTO_TEMP(
1119 P_REQUEST_ID => P_REQUEST_ID ,
1120 P_REGIME_ID => P_REGIME_ID ,
1121 P_PARTY_TYPE => 'IO' ,
1122 P_PARTY_ID => DELTA_REC.ORGANIZATION_ID ,
1123 P_LOCATION_ID => NULL ,
1124 P_BAL_DATE => P_BALANCE_DATE ,
1125 P_TAX_TYPE => DELTA_REC.TAX_TYPE ,
1126 P_DEBIT_AMT => DELTA_REC.DEBIT_AMOUNT ,
1127 P_CREDIT_AMT => DELTA_REC.CREDIT_AMOUNT ,
1128 P_PLA_BALANCE => NULL
1129 );
1130 END LOOP;
1131 PUNCH_SETTLEMENT_ID( P_REGIME_ID => P_REGIME_ID,
1132 P_SETTLEMENT_ID => P_SETTLEMENT_ID ,
1133 P_REGN_ID => P_REGN_ID ,
1134 P_BALANCE_DATE => P_BALANCE_DATE ,
1135 P_TAN_NO => P_TAN_NO,
1136 P_ITEM_CLASSIFICATION => P_ITEM_CLASSIFICATION
1137 );
1138 END IF;
1139 -- END 5631784
1140 END CALCULATE_RGM_BALANCES;
1141 --------------------------------------------------------------------------------------
1142
1143
1144
1145 PROCEDURE get_balances(p_request_id NUMBER ,
1146 p_balance_date DATE ,
1147 p_Called_from VARCHAR2 ,
1148 p_regime_id NUMBER Default NULL ,
1149 p_regn_no VARCHAR2 default NULL ,
1150 p_regn_id NUMBER default NULL ,
1151 p_org_id NUMBER default NULL ,
1152 p_org_type VARCHAR2 default NULL ,
1153 p_settlement_id NUMBER default NULL ,
1154 P_ITEM_CLASSIFICATION VARCHAR2 DEFAULT NULL,-- Added by sacsethi for bug 5631784 on 30-01-2007
1155 p_transfer_type VARCHAR2 default NULL ,/*Bug 5879769 bduvarag*/
1156 p_service_type_code VARCHAR2 default NULL, /*Bug 5879769 bduvarag*/
1157 p_location_id NUMBER DEFAULT NULL /*added by ssawant for bug 5662296 */
1158 )
1159 IS
1160 PRAGMA AUTONOMOUS_TRANSACTION;
1161 -- Added by sacsethi for bug 5631784 on 30-01-2007
1162 -- for TCS
1163
1164 CURSOR CUR_REGIME_CODE(CP_REGIME_ID NUMBER)
1165 IS SELECT REGIME_CODE
1166 FROM JAI_RGM_DEFINITIONS
1167 WHERE REGIME_ID = CP_REGIME_ID;
1168 LV_REGIME_CODE JAI_RGM_DEFINITIONS.REGIME_CODE%TYPE;
1169
1170
1171
1172 BEGIN
1173
1174 IF p_called_from = 'SETTLEMENT' THEN
1175 -- Added by sacsethi for bug 5631784 on 30-01-2007
1176 --START 5631784
1177 -- CURSOR AND CALCULATE RGM BALANCES IS ADDED TO PROVIDE TCS FUNCTIONALITY
1178 OPEN CUR_REGIME_CODE( P_REGIME_ID);
1179 FETCH CUR_REGIME_CODE INTO LV_REGIME_CODE;
1180 CLOSE CUR_REGIME_CODE;
1181 IF LV_REGIME_CODE = JAI_CONSTANTS.TCS_REGIME THEN
1182 calculate_rgm_balances( p_regime_id => p_regime_id ,
1183 p_balance_date => p_balance_date ,
1184 p_request_id => p_request_id ,
1185 p_org_id => p_org_id ,
1186 p_regn_id => p_regn_id ,
1187 p_tan_no => p_regn_no ,
1188 p_item_classification => p_item_classification,
1189 p_settlement_id => p_settlement_id ,
1190 p_called_from => p_called_from
1191 ) ;
1192 --END 5631784
1193 ELSE
1194 CALCULATE_BALANCES_FOR_OU(P_REGIME_ID => P_REGIME_ID ,
1195 P_BALANCE_DATE => P_BALANCE_DATE ,
1196 P_REQUEST_ID => P_REQUEST_ID ,
1197 P_ORG_ID => P_ORG_ID ,
1198 P_ORG_TYPE => P_ORG_TYPE ,
1199 P_REGN_ID => P_REGN_ID ,
1200 P_REGN_NO => P_REGN_NO ,
1201 P_SETTLEMENT_ID => P_SETTLEMENT_ID,
1202 P_CALLED_FROM => P_CALLED_FROM,
1203 p_location_id => p_location_id /*rchandan for bug#5662296*/
1204 );
1205 END IF ;
1206 END IF;
1207 IF p_called_from = 'DISTRIBUTION' THEN
1208 /*Bug 5879769 bduvarag start*/
1209 IF p_transfer_type NOT IN ('S-S') THEN -- added, Harshita for Bug 5694855
1210
1211 calculate_balances_for_io(p_regime_id , p_balance_date , p_request_id, p_service_type_code);
1212 END IF ;
1213 IF p_transfer_type = 'BT' THEN -- added, Harshita for Bug 5694855
1214
1215 /*
1216 ||The loop is commented by rchandan for Bug 5694855
1217 ||This loop is no more required as OU can only be a source and not a destination now
1218 ||We have the parameters for the OU
1219 */
1220 /* FOR ou_rec in
1221 (
1222 SELECT organization_id party_id ,
1223 'OU' party_type ,
1224 set_of_books_id
1225 FROM
1226 hr_operating_units
1227 )
1228 LOOP
1229 IF jai_cmn_utils_pkg.check_jai_exists(P_CALLING_OBJECT => 'JAI_TAX_DISTRIB' ,
1230 P_SET_OF_BOOKS_ID => ou_rec.set_of_books_id
1231 ) = TRUE
1232 THEN*/
1233 /*Bug 5879769 bduvarag end*/
1234 calculate_balances_for_ou(p_regime_id => p_regime_id ,
1235 p_balance_date => p_balance_date ,
1236 p_request_id => p_request_id ,
1237 p_org_id => p_org_id ,/*Bug 5879769 bduvarag*/
1238 p_org_type => 'OU' ,/*Bug 5879769 bduvarag*/
1239 p_regn_id => NULL ,
1240 p_regn_no => NULL ,
1241 p_settlement_id => NULL ,
1242 p_called_from => 'DISTRIBUTE_OU' ,/*Bug 5879769 bduvarag*/
1243 p_service_type_code => p_service_type_code /*Bug 5879769 bduvarag*/
1244 );
1245 /*Bug 5879769 bduvarag start*/
1246 /* END IF;
1247 END LOOP;*/
1248 END IF;
1249 FOR io_rec IN
1250 ( select distinct
1251 organization_id, location_id
1252 from JAI_RGM_ORG_REGNS_V
1253 where organization_type = 'IO'
1254 and regime_code = 'SERVICE'
1255 )
1256 LOOP
1257
1258 calculate_balances_for_ou(p_regime_id => p_regime_id ,
1259 p_balance_date => p_balance_date ,
1260 p_request_id => p_request_id ,
1261 p_org_id => io_rec.organization_id ,
1262 p_org_type => 'IO' ,
1263 p_regn_id => NULL ,
1264 p_regn_no => NULL ,
1265 p_settlement_id => NULL ,
1266 p_called_from => 'DISTRIBUTE_IO' ,
1267 p_location_id => io_rec.location_id ,
1268 p_service_type_code => p_service_type_code
1269 );
1270 END LOOP ;
1271
1272 /*Bug 5879769 bduvarag end*/
1273 END IF;
1274 COMMIT;
1275 EXCEPTION
1276 WHEN OTHERS THEN
1277 raise_application_error (-20102 ,' Error Occured is ' || sqlerrm);
1278 END get_balances;
1279
1280
1281
1282
1283 PROCEDURE insert_records_into_register
1284 (
1285 p_repository_id OUT NOCOPY NUMBER ,
1286 p_regime_id NUMBER ,
1287 p_from_party_type VARCHAR2 ,
1288 p_from_party_id NUMBER ,
1289 p_from_locn_id NUMBER ,
1290 p_from_tax_type VARCHAR2 ,
1291 p_from_trx_amount NUMBER ,
1292 p_to_party_type VARCHAR2 ,
1293 p_to_party_id NUMBER ,
1294 p_to_tax_type VARCHAR2 ,
1295 p_to_trx_amount IN OUT NOCOPY NUMBER ,
1296 p_to_locn_id NUMBER ,
1297 p_called_from VARCHAR2 ,
1298 p_trx_date DATE ,
1299 p_acct_req VARCHAR2 ,
1300 p_source VARCHAR2 ,
1301 P_SOURCE_TRX_TYPE VARCHAR2 ,
1302 P_SOURCE_TABLE_NAME VARCHAR2 ,
1303 p_source_doc_id NUMBER ,
1304 p_settlement_id NUMBER ,
1305 p_reference_id NUMBER ,
1306 p_process_flag OUT NOCOPY VARCHAR2 ,
1307 p_process_message OUT NOCOPY VARCHAR2 ,
1308 p_accounting_date Date,
1309 p_from_service_type VARCHAR2 default null, -- bduvarag for Bug 5694855
1310 p_to_service_type VARCHAR2 default null -- bduvarag for Bug 5694855
1311 )
1312 is
1313 ln_repository_id NUMBER;
1314 lv_acct_req_flag VARCHAR2(10);
1315 lv_process_status VARCHAR2(30);
1316 lv_process_message VARCHAR2(1996);
1317 ln_register_id NUMBER;
1318 --lv_balancing_entry VARCHAR2(1) DEFAULT NULL; /*Added by nprashar for bug 7525691*/ commented for bug#6773684
1319 ln_transfer_id NUMBER;
1320 ln_transfer_source_id NUMBER;
1321 ln_transfer_Dest_id NUMBER;
1322 ln_transfer_dest_line_id NUMBER;
1323 lv_transfer_num VARCHAR2(30);
1324 lv_source VARCHAR2(30);
1325 v_register_type JAI_RGM_REGISTRATIONS.ATTRIBUTE_VALUE%TYPE ;--Added by CSahoo, BUG#5073553
1326 lv_rep_register_type VARCHAR2(15);--added for bug#6773684
1327 ln_cess_credit_amt NUMBER;
1328 ln_cess_debit_amt NUMBER;
1329 ln_discounted_amt NUMBER;
1330 ln_charge_account_id NUMBER;
1331 ln_cess_amount NUMBER;
1332 lv_regime_code JAI_RGM_DEFINITIONS.regime_code%TYPE;
1333 ln_charge_accounting_id NUMBER;
1334 ln_balance_accounting_id NUMBER;
1335 ln_credit_amount NUMBER;
1336 ln_debit_amount NUMBER;
1337 lv_excise_cess CONSTANT varchar2(30) := 'EXCISE-CESS'; --rchandan for bug#4428980
1338 /*Added by CSahoo, BUG#5073553*/
1339 lv_io_register JAI_RGM_REGISTRATIONS.ATTRIBUTE_VALUE%TYPE;
1340 ln_receipt_id JAI_CMN_RG_23AC_II_TRXS.receipt_ref%TYPE;
1341 ld_receipt_date JAI_CMN_RG_23AC_II_TRXS.receipt_date%TYPE;
1342 lv_reference_num JAI_CMN_RG_23AC_II_TRXS.reference_num%TYPE;
1343 ln_ref_document_id JAI_CMN_RG_PLA_TRXS.ref_document_id%TYPE;
1344 ld_ref_document_date JAI_CMN_RG_PLA_TRXS.ref_document_date%TYPE;
1345 lv_vendor_cust_flag JAI_CMN_RG_PLA_TRXS.vendor_cust_flag%TYPE;
1346
1347
1348 ln_sh_cess_amount number;--Added by kunkumar for bug#6127194
1349
1350
1351 CURSOR c_pla_account(cp_inv_orgn_id number , cp_locn_id number) IS
1352 SELECT modvat_pla_account_id
1353 FROM JAI_CMN_INVENTORY_ORGS
1354 WHERE organization_id = cp_inv_orgn_id
1355 AND location_id = cp_locn_id;
1356
1357 /*
1358 ||Start of bug 5073553
1359 ||Added by CSahoo
1360 */
1361 CURSOR c_rg23a_account (cp_inv_orgn_id number , cp_locn_id number)
1362 IS
1363 SELECT modvat_rm_account_id
1364 FROM JAI_CMN_INVENTORY_ORGS
1365 WHERE organization_id = cp_inv_orgn_id
1366 AND location_id = cp_locn_id;
1367
1368 /* End of bug 5073553 */
1369
1370
1371 -- abcd
1372 CURSOR c_get_transfer_Dest_id IS
1373 SELECT transfer_Destination_id
1374 FROM JAI_RGM_DIS_DES_TAXES
1375 WHERE transfer_destination_line_id = p_reference_id;
1376
1377 CURSOR c_cess_amt(cp_transfer_dest_id number) IS
1378 SELECT transfer_amount
1379 FROM JAI_RGM_DIS_DES_TAXES
1380 WHERE transfer_destination_id = cp_transfer_dest_id
1381 -- and tax_type in (lv_excise_cess,jai_constants.tax_type_service_edu_cess, jai_constants.tax_type_sh_service_edu_cess); --rchandan for bug#4428980
1382 --commented the above and added the below by Sanjikum for Bug#6119459
1383 and tax_type in ('EXCISE-CESS','SERVICE_EDUCATION_CESS');
1384
1385 --Added the below by kunkumar for bug#6127194
1386 CURSOR c_sh_cess_amt(cp_transfer_dest_id number) IS
1387 SELECT transfer_amount
1388 FROM jai_rgm_dis_des_taxes
1389 WHERE transfer_destination_id = cp_transfer_dest_id
1390 and tax_type in ('SERVICE_SH_EDU_CESS','EXCISE_SH_EDU_CESS');
1391
1392
1393 BEGIN
1394
1395 IF p_called_from = 'SETTLEMENT' THEN
1396
1397 SELECT jai_rgm_dis_src_hdrs_s.nextval ,
1398 JAI_RGM_DIS_SRC_TAXES_S.nextval ,
1399 jai_rgm_dis_des_hdrs_s.nextval ,
1400 JAI_RGM_DIS_DES_TAXES_S.nextval ,
1401 JAI_RGM_DIS_TRF_NUMS_S.nextval
1402 INTO ln_transfer_id ,
1403 ln_transfer_source_id ,
1404 ln_transfer_dest_id ,
1405 ln_transfer_dest_line_id ,
1406 lv_transfer_num
1407 FROM dual;
1408 INSERT INTO jai_rgm_dis_src_hdrs
1409 (
1410 TRANSFER_ID ,
1411 PARTY_ID ,
1412 PARTY_TYPE ,
1413 LOCATION_ID ,
1414 TRANSFER_NUMBER ,
1415 TRANSACTION_DATE ,
1416 SETTLEMENT_ID ,
1417 CREATION_DATE ,
1418 CREATED_BY ,
1419 LAST_UPDATE_DATE ,
1420 LAST_UPDATED_BY ,
1421 LAST_UPDATE_LOGIN
1422 )
1423 VALUES
1424 (
1425 ln_transfer_id ,
1426 p_from_party_id ,
1427 p_from_party_type ,
1428 p_from_locn_id ,
1429 lv_transfer_num ,
1430 p_trx_date ,
1431 p_settlement_id ,
1432 sysdate ,
1433 fnd_global.user_id ,
1434 sysdate ,
1435 fnd_global.user_id ,
1436 fnd_global.login_id
1437 );
1438 INSERT INTO JAI_RGM_DIS_SRC_TAXES
1439 (
1440 TRANSFER_ID ,
1441 TRANSFER_SOURCE_ID ,
1442 TAX_TYPE ,
1443 DEBIT_BALANCE ,
1444 CREDIT_BALANCE ,
1445 TRANSFER_AMOUNT ,
1446 PARENT_TAX_TYPE ,
1447 PERCENT_OF_PARENT ,
1448 CREATION_DATE ,
1449 CREATED_BY ,
1450 LAST_UPDATE_DATE ,
1451 LAST_UPDATED_BY ,
1452 LAST_UPDATE_LOGIN
1453 )
1454 VALUES
1455 (
1456 ln_transfer_id ,
1457 ln_transfer_source_id ,
1458 p_from_tax_type ,
1459 NULL ,
1460 NULL ,
1461 p_to_trx_amount ,
1462 NULL ,
1463 NULL ,
1464 sysdate ,
1465 fnd_global.user_id ,
1466 sysdate ,
1467 fnd_global.user_id ,
1468 fnd_global.login_id
1469 );
1470 INSERT INTO jai_rgm_dis_des_hdrs
1471 (
1472 TRANSFER_ID ,
1473 TRANSFER_SOURCE_ID ,
1474 TRANSFER_DESTINATION_ID ,
1475 DESTINATION_PARTY_TYPE ,
1476 DESTINATION_PARTY_ID ,
1477 LOCATION_ID ,
1478 AMOUNT_TO_TRANSFER ,
1479 TRANSFER_NUMBER ,
1480 CREATION_DATE ,
1481 CREATED_BY ,
1482 LAST_UPDATE_DATE ,
1483 LAST_UPDATED_BY ,
1484 LAST_UPDATE_LOGIN
1485 )
1486 VALUES
1487 (
1488 ln_transfer_id ,
1489 ln_transfer_source_id ,
1490 ln_transfer_Dest_id ,
1491 p_to_party_type ,
1492 p_to_party_id ,
1493 p_to_locn_id ,
1494 p_to_trx_amount ,
1495 lv_transfer_num ,
1496 sysdate ,
1497 fnd_global.user_id ,
1498 sysdate ,
1499 fnd_global.user_id ,
1500 fnd_global.login_id
1501 );
1502 INSERT INTO JAI_RGM_DIS_DES_TAXES
1503 (
1504 TRANSFER_SOURCE_ID ,
1505 TRANSFER_DESTINATION_ID ,
1506 TRANSFER_DESTINATION_LINE_ID ,
1507 TAX_TYPE ,
1508 DEBIT_BALANCE ,
1509 CREDIT_BALANCE ,
1510 TRANSFER_AMOUNT ,
1511 CREATION_DATE ,
1512 CREATED_BY ,
1513 LAST_UPDATE_DATE ,
1514 LAST_UPDATED_BY ,
1515 LAST_UPDATE_LOGIN
1516 )
1517 VALUES
1518 (
1519 ln_transfer_source_id ,
1520 ln_transfer_Dest_id ,
1521 ln_transfer_dest_line_id,
1522 p_to_tax_type ,
1523 NULL ,
1524 NULL ,
1525 p_to_trx_amount ,
1526 sysdate ,
1527 fnd_global.user_id ,
1528 sysdate ,
1529 fnd_global.user_id ,
1530 fnd_global.login_id
1531 );
1532 END IF;
1533 /*
1534 end of logic for entering into distribution tables when called from settlement program
1535 */
1536 /*
1537 for the source party
1538 */
1539 /*Bug 5879769 bduvarag start*/
1540 IF p_from_party_type IN ( 'OU' )
1541 OR
1542 ( p_from_party_type = 'IO'
1543 AND p_from_tax_type IN (jai_constants.tax_type_service, jai_constants.tax_type_service_edu_cess,
1544 jai_constants.tax_type_sh_service_edu_cess)) THEN -- added, Harshita for Bug 5694855
1545 --Modified by kunkumar for Bug#6127194
1546 /*Bug 5879769 bduvarag end*/
1547
1548 lv_acct_req_flag := jai_constants.YES;
1549
1550 IF p_source = 'SETTLEMENT' THEN
1551 lv_source := jai_constants.source_settle_out ;
1552 -- lv_balancing_entry := jai_constants.NO; --added by nprashar for bug # 7525691 commented for bug#6773684
1553 ELSIF p_source = 'DISTRIBUTION' THEN
1554 lv_source := jai_constants.service_src_distribute_out;
1555 END IF;
1556 IF p_to_party_type = 'IO' and upper(p_to_tax_type) IN (upper(jai_constants.tax_type_excise), 'EXCISE-CESS',jai_constants.tax_type_sh_exc_edu_cess)
1557 THEN /*5694855*/
1558 /*
1559 ||Start of bug 5073553
1560 || Added by CSahoo
1561 || Determine the register setup from the from OU regime setup
1562 */
1563
1564 IF f_get_io_register ( p_party_id => p_to_party_id , --changed p_from_party_id to p_to_party_id for bug#6773684
1565 p_from_party_type => p_from_party_type ,
1566 p_to_party_type => p_to_party_type
1567 ) = 'PLA'
1568 THEN
1569 v_register_type := jai_constants.register_type_pla;
1570 ELSE
1571 --v_register_type := jai_constants.REGISTER_TYPE_A; commented for bug#6773684
1572 v_register_type :='RG';--bug#6773684
1573 END IF;
1574 /* End of bug 5073553 */
1575 /*Bug 5879769 bduvarag start*/
1576 ELSIF ( p_source = 'SETTLEMENT' or p_from_tax_type IN (jai_constants.tax_type_service, jai_constants.tax_type_service_edu_cess, jai_constants.tax_type_sh_service_edu_cess) ) THEN --Added by kunkumar for Bug#6127194
1577 lv_acct_req_flag := jai_constants.YES;
1578 v_register_type := NULL;
1579 /*Bug 5879769 bduvarag end*/
1580 ELSE
1581 v_register_type := NULL;
1582 END IF;
1583 IF p_called_from = 'DISTRIBUTION' THEN
1584 ln_transfer_id := p_reference_id;
1585 END IF;
1586
1587 --start additions for bug#6773684
1588 if v_register_type='RG'
1589 then
1590 lv_rep_register_type:=jai_constants.register_type_a;
1591 end if;
1592 --end additions for bug#6773684
1593
1594 jai_cmn_rgm_recording_pkg.insert_repository_entry
1595 (
1596 P_REPOSITORY_ID => ln_repository_id ,
1597 P_REGIME_ID => p_regime_id ,
1598 P_TAX_TYPE => p_from_tax_type ,
1599 P_ORGANIZATION_TYPE => p_from_party_type ,
1600 P_ORGANIZATION_ID => p_from_party_id ,
1601 P_LOCATION_ID => p_from_locn_id ,
1602 P_SOURCE => lv_source ,
1603 P_SOURCE_TRX_TYPE => p_source_trx_type ,
1604 P_SOURCE_TABLE_NAME => p_source_table_name,
1605 P_SOURCE_DOCUMENT_ID => p_source_doc_id ,
1606 P_TRANSACTION_DATE => p_trx_date ,
1607 P_ACCOUNT_NAME => NULL ,
1608 P_CHARGE_ACCOUNT_ID => NULL ,
1609 P_BALANCING_ACCOUNT_ID => NULL ,
1610 P_AMOUNT => p_to_trx_amount ,
1611 P_DISCOUNTED_AMOUNT => ln_discounted_amt ,
1612 P_ASSESSABLE_VALUE => NULL ,
1613 P_TAX_RATE => NULL ,
1614 P_REFERENCE_ID => ln_transfer_id ,
1615 P_BATCH_ID => NULL ,
1616 P_CALLED_FROM => p_called_from ,
1617 p_process_flag => p_process_flag ,
1618 p_process_message => p_process_message ,
1619 P_SETTLEMENT_ID => p_settlement_id ,
1620 p_accounting_date => p_accounting_date ,
1621 P_ACCNTG_REQUIRED_FLAG => lv_acct_req_flag ,
1622 P_BALANCING_ORGN_TYPE => p_to_party_type ,
1623 P_BALANCING_ORGN_ID => p_to_party_id ,
1624 P_BALANCING_LOCATION_ID => p_to_locn_id ,
1625 P_BALANCING_TAX_TYPE => p_to_tax_type ,
1626 P_BALANCING_ACCNT_NAME => nvl(lv_rep_register_type,v_register_type) ,--added nvl(lv_rep_register_type for bug#6773684
1627 P_CURRENCY_CODE => jai_constants.func_curr , -- File.Sql.35 by Brathod
1628 p_service_type_code => p_from_service_type
1629 /*
1630 , /*Bug 5879769 bduvarag
1631 p_balancing_entry => lv_balancing_entry --added by nprashar for bug # 7525691
1632 *//*commented for bug#6773684*/
1633 );
1634 IF NVL(p_process_flag,'$') <> jai_constants.successful THEN
1635 rollback;
1636 return;
1637 END IF;
1638 p_repository_id := ln_repository_id;
1639 ELSIF p_from_party_type = 'IO' THEN
1640 IF p_from_tax_type = 'EXCISE' THEN
1641 /*
1642 ||Start of bug 5073553
1643 || Added by CSahoo
1644 || Always debit the Rg register in case of IO to Ou distribution irrespective of the regime/regime party attribute setup. */
1645
1646
1647 -- v_register_type := 'RG';/*commented for bug#6773684*/
1648 --start additions for bug#6773684
1649 IF f_get_io_register ( p_party_id => p_from_party_id ,
1650 p_from_party_type => p_from_party_type ,
1651 p_to_party_type => p_to_party_type
1652 ) = 'PLA'
1653 THEN
1654 v_register_type := jai_constants.register_type_pla;
1655 ELSE
1656 --v_register_type := jai_constants.REGISTER_TYPE_A;commented for bug#6773684
1657 v_register_type :='RG';--added for bug#6773684
1658 END IF;
1659 --end additions for bug#6773684
1660
1661
1662 OPEN c_get_transfer_Dest_id;
1663 FETCH c_get_transfer_Dest_id INTO ln_transfer_dest_id;
1664 CLOSE c_get_transfer_Dest_id;
1665 OPEN c_cess_amt(ln_transfer_dest_id);
1666 FETCH c_cess_amt INTO ln_cess_amount;
1667 CLOSE c_cess_amt;
1668
1669 --Added by kunkumar for bug#6127194
1670 OPEN c_sh_cess_amt(ln_transfer_dest_id);
1671 FETCH c_sh_cess_amt INTO ln_sh_cess_amount;
1672 CLOSE c_sh_cess_amt;
1673
1674 IF v_register_type = 'RG' THEN
1675 ln_receipt_id := p_reference_id ;
1676 ld_receipt_date := p_trx_date ;
1677 lv_reference_num := p_source_doc_id ;
1678 OPEN c_rg23a_account(p_from_party_id ,p_from_locn_id );
1679 FETCH c_rg23a_account INTO ln_charge_account_id;
1680 CLOSE c_rg23a_account;
1681 --start additions for bug#6773684
1682 ELSIF v_register_type = 'PLA' THEN
1683 ln_ref_document_id := p_reference_id ;
1684 ld_ref_document_date := p_trx_date ;
1685 lv_vendor_cust_flag := 'O' ;
1686 OPEN c_pla_account(p_from_party_id ,p_from_locn_id );
1687 FETCH c_pla_account INTO ln_charge_account_id;
1688 CLOSE c_pla_account;
1689
1690 END IF;
1691 --end additions for bug#6773684
1692
1693 -- register_type = RG
1694 --commented the following by kunkumar for bug#6127194
1695
1696 /* jai_cmn_rg_23ac_ii_pkg.insert_row(
1697 P_REGISTER_ID => ln_register_id ,
1698 P_INVENTORY_ITEM_ID => -999 ,
1699 P_ORGANIZATION_ID => p_from_party_id ,
1700 P_RECEIPT_ID => p_reference_id ,
1701 P_RECEIPT_DATE => p_trx_date ,
1702 P_CR_BASIC_ED => NULL,
1703 P_CR_ADDITIONAL_ED => NULL,
1704 P_CR_OTHER_ED => NULL,
1705 P_DR_BASIC_ED => p_to_trx_amount ,
1706 P_DR_ADDITIONAL_ED => NULL,
1707 P_DR_OTHER_ED => NULL,
1708 P_EXCISE_INVOICE_NO => NULL,
1709 P_EXCISE_INVOICE_DATE => NULL,
1710 P_REGISTER_TYPE => jai_constants.REGISTER_TYPE_A ,
1711 P_REMARKS => 'DISTRIBUTION - OUT',
1712 P_VENDOR_ID => NULL,
1713 P_VENDOR_SITE_ID => NULL ,
1714 P_CUSTOMER_ID => NULL,
1715 P_CUSTOMER_SITE_ID => NULL,
1716 P_LOCATION_ID => p_from_locn_id,
1717 P_TRANSACTION_DATE => p_trx_date ,
1718 P_CHARGE_ACCOUNT_ID => NULL ,
1719 P_REGISTER_ID_PART_I => NULL ,
1720 P_REFERENCE_NUM => p_source_doc_id,
1721 P_ROUNDING_ID => NULL ,
1722 P_OTHER_TAX_CREDIT => NULL,
1723 P_OTHER_TAX_DEBIT => ln_cess_amount,
1724 P_TRANSACTION_TYPE => 'DISTRIBUTION' ,
1725 P_TRANSACTION_SOURCE => 'DISTRIBUTION' ,
1726 P_CALLED_FROM => p_called_from ,
1727 P_SIMULATE_FLAG => 'N' ,
1728 p_process_status => p_process_flag,
1729 P_PROCESS_MESSAGE => p_process_message
1730 );*/
1731 create_io_register_entry (
1732 p_register_type => v_register_type ,
1733 p_tax_type => 'EXCISE' ,
1734 p_organization_id => p_from_party_id ,
1735 p_location_id => p_from_locn_id ,
1736 p_cr_basic_ed => NULL ,
1737 p_cr_additional_ed => NULL ,
1738 p_cr_other_ed => NULL ,
1739 p_dr_basic_ed => p_to_trx_amount ,
1740 p_dr_additional_ed => NULL ,
1741 p_dr_other_ed => NULL ,
1742 p_excise_invoice_no => 'DISTRIBUTION-'||ln_transfer_id ,/*rchandan, Bug 5563300*/
1743 p_remarks => 'DISTRIBUTION - OUT' ,
1744 p_vendor_id => NULL ,
1745 p_vendor_site_id => NULL ,
1746 p_transaction_date => p_trx_date ,
1747 p_charge_account_id => ln_charge_account_id ,
1748 p_other_tax_credit => NULL ,
1749 p_other_tax_debit => NVL(ln_cess_amount,0) + NVL(ln_sh_cess_amount,0) ,
1750 p_transaction_type => 'DISTRIBUTION' ,
1751 p_transaction_source => 'DISTRIBUTION' ,
1752 p_called_from => p_called_from ,
1753 p_simulate_flag => 'N' ,
1754 p_debit_amt => ln_cess_amount ,
1755 p_credit_amt => NULL ,
1756 --Added the below 2 columns by Sanjikum for Bug#6119459
1757 p_sh_cess_debit_amt => ln_sh_cess_amount,
1758 p_sh_cess_credit_amt => NULL ,
1759 p_inventory_item_id => -999 ,
1760 /*RG specific parameters */
1761 p_receipt_id => ln_receipt_id ,
1762 p_receipt_date => ld_receipt_date ,
1763 p_excise_invoice_date => p_trx_date ,/*rchandan, Bug 5563300*/
1764 p_customer_id => NULL ,
1765 p_customer_site_id => NULL ,
1766 p_register_id_part_i => NULL ,
1767 p_reference_num => lv_reference_num ,
1768 p_rounding_id => NULL ,
1769 /*PLA specific parameters */
1770 p_ref_document_id => ln_ref_document_id ,
1771 p_ref_document_date => ld_ref_document_date ,
1772 p_dr_invoice_id => NULL ,
1773 p_dr_invoice_date => NULL ,
1774 p_bank_branch_id => NULL ,
1775 p_entry_date => NULL ,
1776 p_vendor_cust_flag => lv_vendor_cust_flag ,
1777 p_process_flag => p_process_flag ,
1778 p_process_message => p_process_message
1779 );
1780
1781 /*jai_cmn_utils_pkg.print_log('dis.log','after call to from io process flag = ' || p_process_flag);
1782 jai_cmn_utils_pkg.print_log('dis.log','err messg is '||p_process_message );*/
1783 IF nvl(p_process_flag,jai_constants.successful) <> jai_constants.successful THEN
1784 rollback;
1785 return;
1786 END IF;
1787 END IF;
1788 END IF;
1789 /*
1790 for the destination party
1791 */
1792 /*Bug 5879769 bduvarag*/
1793 IF p_to_party_type IN ('OU')
1794 OR
1795 ( p_to_party_type = 'IO' AND p_to_tax_type IN (jai_constants.tax_type_service, jai_constants.tax_type_service_edu_cess, jai_constants.tax_type_sh_service_edu_cess )) THEN -- added, Harshita for Bug 5694855 --Modified by kunkumar for Bug#6127194
1796 IF p_from_party_type = 'IO' AND upper(p_from_tax_type) IN (upper(jai_constants.tax_type_excise), 'EXCISE-CESS',upper(jai_constants.tax_type_sh_exc_edu_cess)) THEN/*Bug 5879769 bduvarag*/
1797 lv_acct_req_flag := jai_constants.YES;
1798 -- v_register_type := jai_constants.REGISTER_TYPE_A; commented for bug#6773684
1799
1800 --start additions for bug#6773684
1801 IF f_get_io_register ( p_party_id => p_from_party_id ,
1802 p_from_party_type => p_from_party_type ,
1803 p_to_party_type => p_to_party_type
1804 ) = 'PLA'
1805 THEN
1806 v_register_type := jai_constants.register_type_pla;
1807 ELSE
1808 --v_register_type := jai_constants.REGISTER_TYPE_A;--commneted for bug#6773684
1809 v_register_type :='RG';--added for bug#6773684
1810 END IF;
1811 --end additions for bug#6773684
1812
1813
1814 /*Bug 5879769 bduvarag start*/
1815 ELSIF ( p_source = 'SETTLEMENT' or p_to_tax_type IN (jai_constants.tax_type_service, jai_constants.tax_type_service_edu_cess, jai_constants.tax_type_sh_service_edu_cess )) THEN --Added by kunkumar for Bug#6127194
1816 lv_acct_req_flag := jai_constants.YES;
1817 v_register_type := NULL;
1818 /*Bug 5879769 bduvarag end*/
1819 ELSE
1820 lv_acct_req_flag := jai_constants.NO;
1821 v_register_type := NULL;
1822 END IF;
1823 IF p_source = 'SETTLEMENT' THEN
1824 lv_source := jai_constants.source_settle_in ;
1825 -- lv_balancing_entry := jai_constants.NO; --added by nprashar for bug # 7525691 commented for bug#6773684
1826 ELSIF p_source = 'DISTRIBUTION' THEN
1827 lv_source := jai_constants.service_src_distribute_in;
1828 END IF;
1829 /* jai_cmn_utils_pkg.print_log('dis.log',' p_to_trx_amount is :' || p_to_trx_amount); */
1830 IF p_called_from = 'DISTRIBUTION' THEN
1831 ln_transfer_id := p_reference_id;
1832 END IF;
1833 --start additions for bug#6773684
1834 if v_register_type='RG'
1835 then
1836 lv_rep_register_type :=jai_constants.register_type_a;
1837 end if;
1838 --end additions for bug#6773684
1839
1840 jai_cmn_rgm_recording_pkg.insert_repository_entry
1841 (
1842 P_REPOSITORY_ID => ln_repository_id ,
1843 P_REGIME_ID => p_regime_id ,
1844 P_TAX_TYPE => p_to_tax_type ,
1845 P_ORGANIZATION_TYPE => p_to_party_type ,
1846 P_ORGANIZATION_ID => p_to_party_id ,
1847 P_LOCATION_ID => p_to_locn_id ,
1848 P_SOURCE => lv_source ,
1849 P_SOURCE_TRX_TYPE => p_source_trx_type ,
1850 P_SOURCE_TABLE_NAME => p_source_table_name,
1851 P_SOURCE_DOCUMENT_ID => p_source_doc_id ,
1852 P_TRANSACTION_DATE => p_trx_date ,
1853 P_ACCOUNT_NAME => NULL ,
1854 P_CHARGE_ACCOUNT_ID => NULL ,
1855 P_BALANCING_ACCOUNT_ID => NULL ,
1856 P_AMOUNT => p_to_trx_amount ,
1857 P_DISCOUNTED_AMOUNT => ln_discounted_amt ,
1858 P_ASSESSABLE_VALUE => NULL ,
1859 P_TAX_RATE => NULL ,
1860 P_REFERENCE_ID => ln_transfer_id ,
1861 P_BATCH_ID => NULL ,
1862 P_CALLED_FROM => p_called_from ,
1863 p_process_flag => p_process_flag ,
1864 p_process_message => p_process_message ,
1865 P_SETTLEMENT_ID => p_settlement_id ,
1866 p_accounting_date => p_trx_date ,
1867 P_ACCNTG_REQUIRED_FLAG => lv_acct_req_flag ,
1868 P_BALANCING_ORGN_TYPE => p_from_party_type ,
1869 P_BALANCING_ORGN_ID => p_from_party_id ,
1870 P_BALANCING_LOCATION_ID => p_from_locn_id ,
1871 P_BALANCING_TAX_TYPE => p_from_tax_type ,
1872 P_BALANCING_ACCNT_NAME => nvl(lv_rep_register_type,v_register_type) ,--added the nvl(lv_rep_register_type for bug#6773684
1873 P_CURRENCY_CODE => jai_constants.func_curr , -- File.Sql.35 by Brathod
1874 p_service_type_code => p_to_service_type
1875 /*, /*Bug 5879769 bduvarag
1876 p_balancing_entry => lv_balancing_entry --added by nprashar for bug # 7525691
1877 *//*commented for bug#6773684*/
1878 );
1879 IF nvl(p_process_flag,'$') <> jai_constants.successful THEN
1880 rollback;
1881 return;
1882 END IF;
1883 p_repository_id := ln_repository_id;
1884 ELSIF p_to_party_type = 'IO' THEN
1885 IF p_to_tax_type = 'EXCISE' THEN
1886
1887 /*
1888 ||Start of bug 5073553
1889 || Added By CSahoo BUG#5073553
1890 || Determine the register setup from the OU regime setup
1891 */
1892
1893 v_register_type := f_get_io_register ( p_party_id => p_to_party_id , --changed p_from_party_id to p_to_party_id for bug#6773684
1894 p_from_party_type => p_from_party_type ,
1895 p_to_party_type => p_to_party_type
1896 ) ;
1897
1898 OPEN c_get_transfer_Dest_id;
1899 FETCH c_get_transfer_Dest_id INTO ln_transfer_dest_id;
1900 CLOSE c_get_transfer_Dest_id;
1901 OPEN c_cess_amt(ln_transfer_dest_id);
1902 FETCH c_cess_amt INTO ln_cess_amount;
1903 CLOSE c_cess_amt;
1904 --Added by kunkumar for bug#6127194
1905 OPEN c_sh_cess_amt(ln_transfer_dest_id);
1906 FETCH c_sh_cess_amt INTO ln_sh_cess_amount;
1907 CLOSE c_sh_cess_amt;
1908
1909 IF v_register_type = 'RG' THEN
1910 ln_receipt_id := p_reference_id ;
1911 ld_receipt_date := p_trx_date ;
1912 lv_reference_num := p_source_doc_id ;
1913 OPEN c_rg23a_account(p_to_party_id ,p_to_locn_id ); --changed from party id ,from locn id to to party id ,to locn id for bug#6773684
1914 FETCH c_rg23a_account INTO ln_charge_account_id;
1915 CLOSE c_rg23a_account;
1916 ELSIF v_register_type = 'PLA' THEN
1917 ln_ref_document_id := p_reference_id ;
1918 ld_ref_document_date := p_trx_date ;
1919 lv_vendor_cust_flag := 'O' ;
1920 OPEN c_pla_account(p_to_party_id ,p_to_locn_id );
1921 FETCH c_pla_account INTO ln_charge_account_id;
1922 CLOSE c_pla_account;
1923 END IF; --v_register_type = 'RG'
1924 --Commented by kunkumar for Bug#6127194
1925 /* jai_cmn_rg_pla_trxs_pkg.insert_row(
1926 p_register_id => ln_register_id,
1927 p_tr6_challan_no => NULL,
1928 p_tr6_challan_date => NULL,
1929 p_cr_basic_ed => p_to_trx_amount,
1930 p_cr_additional_ed => NULL,
1931 p_cr_other_ed => NULL,
1932 p_ref_document_id => p_reference_id,
1933 p_ref_document_date => p_trx_date,
1934 p_dr_invoice_id => NULL,
1935 p_dr_invoice_date => NULL,
1936 p_dr_basic_ed => NULL,
1937 p_dr_additional_ed => NULL,
1938 p_dr_other_ed => NULL,
1939 p_organization_id => p_to_party_id,
1940 p_location_id => p_to_locn_id,
1941 p_bank_branch_id => NULL,
1942 p_entry_date => NULL,
1943 p_inventory_item_id => -999,
1944 p_vendor_cust_flag => 'O',
1945 p_vendor_id => p_from_party_id,
1946 p_vendor_site_id => NULL,
1947 p_excise_invoice_no => NULL,
1948 p_remarks => 'DISTRIBUTION',
1949 p_transaction_date => p_trx_date,
1950 p_charge_account_id => ln_charge_account_id,
1951 p_other_tax_credit => ln_cess_amount,
1952 p_other_tax_debit => NULL,
1953 p_transaction_type => 'DISTRIBUTION',
1954 p_transaction_source => 'DISTRIBUTION',
1955 p_called_from => p_called_from,
1956 p_simulate_flag => 'N',
1957 p_process_status => p_process_flag,
1958 p_process_message => p_process_message
1959 );*/ --Added the call to create_io_register entry by kunkumar
1960 create_io_register_entry (
1961 p_register_type => v_register_type ,
1962 p_tax_type => 'EXCISE' ,
1963 p_organization_id => p_to_party_id ,
1964 p_location_id => p_to_locn_id ,
1965 p_cr_basic_ed => p_to_trx_amount ,
1966 p_cr_additional_ed => NULL ,
1967 p_cr_other_ed => NULL ,
1968 p_dr_basic_ed => NULL ,
1969 p_dr_additional_ed => NULL ,
1970 p_dr_other_ed => NULL ,
1971 p_excise_invoice_no => 'DISTRIBUTION-'||ln_transfer_id ,/*rchandan, Bug 5563300*/
1972 p_remarks => 'DISTRIBUTION - IN' ,
1973 p_vendor_id => p_from_party_id ,
1974 p_vendor_site_id => NULL ,
1975 p_transaction_date => p_trx_date ,
1976 p_charge_account_id => ln_charge_account_id ,
1977 p_other_tax_credit => NVL(ln_cess_amount,0) + NVL(ln_sh_cess_amount,0) , --changed by Sanjikum for Bug#6119459
1978 p_other_tax_debit => NULL ,
1979 p_transaction_type => 'DISTRIBUTION' ,
1980 p_transaction_source => 'DISTRIBUTION' ,
1981 p_called_from => p_called_from ,
1982 p_simulate_flag => 'N' ,
1983 p_debit_amt => NULL ,
1984 p_credit_amt => ln_cess_amount ,
1985 --Added the below 2 columns by Sanjikum for Bug#6119459
1986 p_sh_cess_debit_amt => NULL ,
1987 p_sh_cess_credit_amt => ln_sh_cess_amount ,
1988 p_inventory_item_id => -999 ,
1989 /*RG specific parameters */
1990 p_receipt_id => ln_receipt_id ,
1991 p_receipt_date => ld_receipt_date ,
1992 p_excise_invoice_date => p_trx_date ,/*rchandan, Bug 5563300*/
1993 p_customer_id => NULL ,
1994 p_customer_site_id => NULL ,
1995 p_register_id_part_i => NULL ,
1996 p_reference_num => lv_reference_num ,
1997 p_rounding_id => NULL ,
1998 /*PLA specific parameters */
1999 p_ref_document_id => ln_ref_document_id ,
2000 p_ref_document_date => ld_ref_document_date ,
2001 p_dr_invoice_id => NULL ,
2002 p_dr_invoice_date => NULL ,
2003 p_bank_branch_id => NULL ,
2004 p_entry_date => NULL ,
2005 p_vendor_cust_flag => lv_vendor_cust_flag ,
2006 p_process_flag => p_process_flag ,
2007 p_process_message => p_process_message
2008 );
2009
2010 IF nvl(p_process_flag,jai_constants.successful) <> jai_constants.successful THEN
2011 rollback;
2012 return;
2013 END IF;--p_process_flag
2014 END IF;-- p_to_tax_type
2015 END IF; --p_to_party_type
2016 COMMIT;
2017 p_process_flag := 'SS';
2018 EXCEPTION
2019 WHEN OTHERS THEN
2020 p_process_flag := 'UE';
2021 p_process_message := 'Error in procedure - insert_records_into_register ' || substr(sqlerrm,1,1500);
2022 END insert_records_into_register;
2023
2024
2025
2026
2027 PROCEDURE delete_records(p_request_id number) IS
2028
2029 BEGIN
2030 DELETE FROM JAI_RGM_BALANCE_T
2031 WHERE request_id = p_request_id;
2032 COMMIT;
2033
2034 END delete_records;
2035
2036
2037
2038 -- added, Harshita for Bug 5096787
2039 PROCEDURE create_io_register_entry (
2040 p_register_type IN JAI_RGM_REGISTRATIONS.ATTRIBUTE_VALUE%TYPE ,
2041 p_tax_type IN VARCHAR2 ,
2042 p_organization_id IN JAI_CMN_RG_23AC_II_TRXS.organization_id%TYPE ,
2043 p_location_id IN JAI_CMN_RG_23AC_II_TRXS.location_id%TYPE ,
2044 p_cr_basic_ed IN JAI_CMN_RG_23AC_II_TRXS.cr_basic_ed%TYPE ,
2045 p_cr_additional_ed IN JAI_CMN_RG_23AC_II_TRXS.cr_additional_ed%TYPE ,
2046 p_cr_other_ed IN JAI_CMN_RG_23AC_II_TRXS.cr_other_ed%TYPE ,
2047 p_dr_basic_ed IN JAI_CMN_RG_23AC_II_TRXS.dr_basic_ed%TYPE ,
2048 p_dr_additional_ed IN JAI_CMN_RG_23AC_II_TRXS.dr_additional_ed%TYPE ,
2049 p_dr_other_ed IN JAI_CMN_RG_23AC_II_TRXS.dr_other_ed%TYPE ,
2050 p_excise_invoice_no IN JAI_CMN_RG_23AC_II_TRXS.excise_invoice_no%TYPE ,
2051 p_remarks IN JAI_CMN_RG_23AC_II_TRXS.remarks%TYPE ,
2052 p_vendor_id IN JAI_CMN_RG_23AC_II_TRXS.vendor_id%TYPE ,
2053 p_vendor_site_id IN JAI_CMN_RG_23AC_II_TRXS.vendor_site_id%TYPE ,
2054 p_transaction_date IN JAI_CMN_RG_23AC_II_TRXS.transaction_date%TYPE ,
2055 p_charge_account_id IN JAI_CMN_RG_23AC_II_TRXS.charge_account_id%TYPE ,
2056 p_other_tax_credit IN JAI_CMN_RG_23AC_II_TRXS.other_tax_credit%TYPE ,
2057 p_other_tax_debit IN JAI_CMN_RG_23AC_II_TRXS.other_tax_debit%TYPE ,
2058 p_transaction_type IN VARCHAR2 ,
2059 p_transaction_source IN VARCHAR2 ,
2060 p_called_from IN VARCHAR2 ,
2061 p_simulate_flag IN VARCHAR2 ,
2062 p_debit_amt IN JAI_CMN_RG_OTHERS.DEBIT%TYPE ,
2063 p_credit_amt IN JAI_CMN_RG_OTHERS.CREDIT%TYPE ,
2064 p_sh_cess_debit_amt IN JAI_CMN_RG_OTHERS.DEBIT%TYPE,--Added by kunkumar for bug#6127194
2065 p_sh_cess_credit_amt IN JAI_CMN_RG_OTHERS.CREDIT%TYPE,--Added by kunkumar for bug#6127194
2066 p_inventory_item_id IN JAI_CMN_RG_23AC_II_TRXS.INVENTORY_ITEM_ID%TYPE ,
2067 p_receipt_id IN JAI_CMN_RG_23AC_II_TRXS.RECEIPT_REF%TYPE Default NULL ,
2068 p_receipt_date IN JAI_CMN_RG_23AC_II_TRXS.receipt_date%TYPE Default NULL ,
2069 p_excise_invoice_date IN JAI_CMN_RG_23AC_II_TRXS.excise_invoice_date%TYPE Default NULL ,
2070 p_customer_id IN JAI_CMN_RG_23AC_II_TRXS.customer_id%TYPE Default NULL ,
2071 p_customer_site_id IN JAI_CMN_RG_23AC_II_TRXS.customer_site_id%TYPE Default NULL ,
2072 p_register_id_part_i IN JAI_CMN_RG_23AC_II_TRXS.register_id_part_i%TYPE Default NULL ,
2073 p_reference_num IN JAI_CMN_RG_23AC_II_TRXS.reference_num%TYPE Default NULL ,
2074 p_rounding_id IN JAI_CMN_RG_23AC_II_TRXS.rounding_id%TYPE Default NULL ,
2075 p_ref_document_id IN JAI_CMN_RG_PLA_TRXS.ref_document_id%TYPE Default NULL ,
2076 p_ref_document_date IN JAI_CMN_RG_PLA_TRXS.ref_document_date%TYPE Default NULL ,
2077 p_dr_invoice_id IN JAI_CMN_RG_PLA_TRXS.DR_INVOICE_NO%TYPE Default NULL ,
2078 p_dr_invoice_date IN JAI_CMN_RG_PLA_TRXS.dr_invoice_date%TYPE Default NULL ,
2079 p_bank_branch_id IN JAI_CMN_RG_PLA_TRXS.bank_branch_id%TYPE Default NULL ,
2080 p_entry_date IN JAI_CMN_RG_PLA_TRXS.entry_date%TYPE Default NULL ,
2081 p_vendor_cust_flag IN JAI_CMN_RG_PLA_TRXS.vendor_cust_flag%TYPE Default NULL ,
2082 p_process_flag OUT NOCOPY VARCHAR2 ,
2083 p_process_message OUT NOCOPY VARCHAR2
2084 )
2085 IS
2086 --Added by kunkumar for bug#6127194, Start
2087 ln_register_id NUMBER ;
2088 ln_transfer_Dest_id NUMBER ;
2089 ln_cess_amount NUMBER ;
2090 ln_source_type JAI_CMN_RG_OTHERS.SOURCE_TYPE%TYPE ;
2091 lv_source_register JAI_CMN_RG_OTHERS.SOURCE_REGISTER%TYPE ;
2092 --End, Added by kunkumar
2093 BEGIN
2094 --Added the body of the procedure by kunkumar for bug#6127194
2095 /*
2096 ||Initialize the variables
2097 */
2098 p_process_flag := jai_constants.successful;
2099 ln_source_type := null ;
2100 lv_source_register := null ;
2101
2102 IF p_tax_type = 'EXCISE' THEN
2103 IF p_register_type = 'RG' THEN
2104 ln_source_type := 1 ;
2105 lv_source_register := 'RG23A_P2' ;
2106 jai_cmn_rg_23ac_ii_pkg.insert_row(
2107 p_register_id => ln_register_id ,
2108 p_inventory_item_id => p_inventory_item_id ,
2109 p_organization_id => p_organization_id ,
2110 p_receipt_id => p_receipt_id ,
2111 p_receipt_date => p_receipt_date ,
2112 p_cr_basic_ed => p_cr_basic_ed ,
2113 p_cr_additional_ed => p_cr_additional_ed ,
2114 p_cr_other_ed => p_cr_other_ed ,
2115 p_dr_basic_ed => p_dr_basic_ed ,
2116 p_dr_additional_ed => p_dr_additional_ed ,
2117 p_dr_other_ed => p_dr_other_ed ,
2118 p_excise_invoice_no => p_excise_invoice_no ,
2119 p_excise_invoice_date => p_excise_invoice_date ,
2120 p_register_type => jai_constants.register_type_a ,
2121 p_remarks => p_remarks ,
2122 p_vendor_id => p_vendor_id ,
2123 p_vendor_site_id => p_vendor_site_id ,
2124 p_customer_id => p_customer_id ,
2125 p_customer_site_id => p_customer_site_id ,
2126 p_location_id => p_location_id ,
2127 p_transaction_date => p_transaction_date ,
2128 p_charge_account_id => p_charge_account_id ,
2129 p_register_id_part_i => p_register_id_part_i ,
2130 p_reference_num => p_reference_num ,
2131 p_rounding_id => p_rounding_id ,
2132 p_other_tax_credit => p_other_tax_credit ,
2133 p_other_tax_debit => p_other_tax_debit ,
2134 p_transaction_type => p_transaction_type ,
2135 p_transaction_source => p_transaction_source ,
2136 p_called_from => p_called_from ,
2137 p_simulate_flag => p_simulate_flag ,
2138 p_process_status => p_process_flag ,
2139 p_process_message => p_process_message
2140 );
2141
2142
2143 ELSIF p_register_type = 'PLA' THEN
2144 ln_source_type := 2 ;
2145 lv_source_register := 'PLA' ;
2146
2147 jai_cmn_rg_pla_trxs_pkg.insert_row(
2148 p_register_id => ln_register_id ,
2149 p_tr6_challan_no => NULL ,
2150 p_tr6_challan_date => NULL ,
2151 p_cr_basic_ed => p_cr_basic_ed ,
2152 p_cr_additional_ed => p_cr_additional_ed ,
2153 p_cr_other_ed => p_cr_other_ed ,
2154 p_ref_document_id => p_ref_document_id ,
2155 p_ref_document_date => p_ref_document_date ,
2156 p_dr_invoice_id => p_dr_invoice_id ,
2157 p_dr_invoice_date => p_dr_invoice_date ,
2158 p_dr_basic_ed => p_dr_basic_ed ,
2159 p_dr_additional_ed => p_dr_additional_ed ,
2160 p_dr_other_ed => p_dr_other_ed ,
2161 p_organization_id => p_organization_id ,
2162 p_location_id => p_location_id ,
2163 p_bank_branch_id => p_bank_branch_id ,
2164 p_entry_date => p_entry_date ,
2165 p_inventory_item_id => p_inventory_item_id ,
2166 p_vendor_cust_flag => p_vendor_cust_flag ,
2167 p_vendor_id => p_vendor_id ,
2168 p_vendor_site_id => p_vendor_site_id ,
2169 p_excise_invoice_no => p_excise_invoice_no ,
2170 p_remarks => p_remarks ,
2171 p_transaction_date => p_transaction_date ,
2172 p_charge_account_id => p_charge_account_id ,
2173 p_other_tax_credit => p_other_tax_credit ,
2174 p_other_tax_debit => p_other_tax_debit ,
2175 p_transaction_type => p_transaction_type ,
2176 p_transaction_source => p_transaction_source ,
2177 p_called_from => p_called_from ,
2178 p_simulate_flag => p_simulate_flag ,
2179 p_process_status => p_process_flag ,
2180 p_process_message => p_process_message
2181 );
2182
2183 END IF;
2184
2185 IF nvl(p_process_flag,jai_constants.successful) <> jai_constants.successful THEN
2186 rollback;
2187 return;
2188 ELSE /* Pass cess entries in jai_rg_others table*/
2189 /* Update the cess amount in the ja_in_rg23_part_ii table*/
2190 IF nvl(p_credit_amt,0) <> 0 OR
2191 nvl(p_debit_amt,0) <> 0
2192 THEN
2193
2194 jai_cmn_rg_others_pkg.insert_row( p_source_type => ln_source_type ,
2195 p_source_name => lv_source_register ,
2196 p_source_id => ln_register_id ,
2197 p_tax_type => 'EXCISE_EDUCATION_CESS' ,
2198 debit_amt => p_debit_amt ,
2199 credit_amt => p_credit_amt ,
2200 p_process_flag => p_process_flag ,
2201 p_process_msg => p_process_message
2202 );
2203
2204 IF nvl(p_process_flag,jai_constants.successful) <> jai_constants.successful THEN
2205
2206 rollback;
2207 return;
2208 END IF;
2209 END IF;
2210
2211 IF nvl(p_sh_cess_debit_amt,0) <> 0 OR
2212 nvl(p_sh_cess_credit_amt,0) <> 0
2213 THEN
2214
2215 jai_cmn_rg_others_pkg.insert_row( p_source_type => ln_source_type ,
2216 p_source_name => lv_source_register ,
2217 p_source_id => ln_register_id ,
2218 p_tax_type => 'EXCISE_SH_EDU_CESS' ,
2219 debit_amt => p_sh_cess_debit_amt ,
2220 credit_amt => p_sh_cess_credit_amt ,
2221 p_process_flag => p_process_flag ,
2222 p_process_msg => p_process_message
2223 );
2224
2225 IF nvl(p_process_flag,jai_constants.successful) <> jai_constants.successful THEN
2226
2227 rollback;
2228 return;
2229 END IF;
2230 END IF;
2231 END IF;
2232 END IF; -- end if of tax_type = 'Excise'
2233 END create_io_register_entry;
2234 --END ;
2235
2236
2237 FUNCTION f_get_io_register ( p_party_id JAI_RGM_BALANCE_T.PARTY_ID%TYPE ,
2238 p_from_party_type JAI_RGM_BALANCE_T.PARTY_TYPE%TYPE ,
2239 p_to_party_type JAI_RGM_BALANCE_T.PARTY_TYPE%TYPE
2240 )
2241 RETURN VARCHAR2
2242 IS
2243 /*
2244 || Check the setup value for the "Service Tax Distribution in PLA/RG" attribute for the Service Tax regime for the source OU. This should be either PLA or RG
2245 */
2246 CURSOR cur_get_dist_plg_rg
2247 IS
2248 SELECT
2249 attribute_value
2250 FROM
2251 jai_rgm_org_regns_v
2252 WHERE
2253 --organization_type = 'OU' /*commented by vkaranam for bug#6773684*/
2254 organization_id = p_party_id
2255 AND regime_code = 'SERVICE'
2256 AND registration_type = 'OTHERS'
2257 AND attribute_code = 'DIST_PLA_RG';
2258 ln_attrval_dist_plarg JAI_RGM_REGISTRATIONS.ATTRIBUTE_VALUE%TYPE := NULL;
2259 BEGIN
2260 /*
2261 || Determine the register for the given OU based on the "Service Tax distribution in PLA/RG" attribute setup,
2262 || set it to PLA in case no setup is found
2263 */
2264 OPEN cur_get_dist_plg_rg ;
2265 FETCH cur_get_dist_plg_rg into ln_attrval_dist_plarg;
2266 CLOSE cur_get_dist_plg_rg;
2267 /*
2268 || If the attribute value for Service TAx Distribution in PLA/RG is not set then do the following
2269 || 1. In case of IO to OU debit RG register
2270 || 2. In case of Ou to IO credit PLA register
2271 */
2272 /* commented by vkaranam for bug#6773684*
2273 IF ln_attrval_dist_plarg IS NULL THEN
2274 IF p_from_party_type = 'IO' THEN
2275 ln_attrval_dist_plarg := 'RG';
2276 ELSIF p_to_party_type = 'IO' THEN
2277 ln_attrval_dist_plarg := 'PLA';
2278 END IF;
2279 END IF;
2280 */
2281
2282 /* || If the attribute value for Service TAx Distribution in PLA/RG is not set then Default register wil be RG Register
2283 start additons for Bug #6773684*/
2284 IF ln_attrval_dist_plarg IS NULL THEN
2285 ln_attrval_dist_plarg := 'RG';
2286 END IF;
2287 /*end bug#6773684*/
2288 return(ln_attrval_dist_plarg);
2289
2290 END ;
2291 -- ended, Harshita for Bug 5096787
2292 ----------------------------------------------------------------------------------------------------------------
2293 END JAI_CMN_RGM_TAX_DIST_PKG;