1 PACKAGE BODY jai_cmn_rgm_tax_dist_pkg AS
2 /* $Header: jai_cmn_rgm_dist.plb 120.41.12020000.3 2012/12/25 08:47:31 qioliu 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_T debit_amt and JAI_RGM_BALANCE_T pla_balance as 0
49 else (+ve value for PLA balance) let the value of o be populated into JAI_RGM_BALANCE_T 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 16 18-Mar-2009 Bug 7525691 File version 120.4.12000000.10/120.14.12010000.7/120.22
113 Added parameter p_distribution_type when calling jai_cmn_rgm_recording_pkg.insert_repository_entry
114
115 17 21-Jul-2009 CSahoo for bug#8702609, File Version 120.4.12000000.13
116 Issue: ISSUES WITH SERVICE TAX DISTRIBUTION AND SETTLEMENT FORMS
117 Fix: modified the code in the procedure calculate_balances_for_ou. Initially the settled amount
118 was getting added up to the amount the to be distributed. So modified the code so that the
119 settled amount no more gets added up.
120
121 18 22-Jul-2009 CSahoo for bug#8289991, File Version 120.4.12000000.14
122 Issue: FP12.0 :7828827 SERVICE TAX CREDIT AMOUNT IS NOT CARRYING FORWARD TO NEXT SETTLEMENT
123 FIX: Modified the procedure calculate_balances_for_ou. Removed the logic of calculating the
124 credit and debit balance on the basis of the service type. Added the code to obtain
125 total credit and debit amount from the last settlement date to the new settlement date.
126 Then check the last settled credit and debit amount for each tax type. If there is a
127 credit carry forward then it is added to the total credit amount to be settled.
128
129 19 28-jul-2009 vumaasha for bug 8657720, reverted the change done for the bug 7445742
130
131 20 11-sep-2009 vkaranam for bug#8873924
132 Issue:SERVICE TAX DISTRIBUTION WITH EXCISE-SERVICE TRANSFER IS HITTING RG23A
133 Reason:
134 If the service tax distribution in RG/PLA setup has been given as "RG" ,and excise to transfer is always
135 hitting RG23A' register eventhough the balance is not available in that.
136 register_type='A' has been hardcode with setup as "RG"
137 hence the issue.
138 Fix:
139
140 If the service tax distribution in RG/PLA setup as "RG" and with "Excise-service" Transfer
141 ,either RG23A/RG23C register will get hit based on the Register prefernces and the balance available in
142 the individual registers.
143
144 Changes are done in create_io_register_entry procedure to fetch the register_type based on
145 Register prefernces and the balance available in the RG23A/C registers for excise-service transfer.
146
147 20. 12-oct-2009 vkaranam for bug#9005474
148 issue:
149 TCS tax is geeting doubled during the settlement
150 Reason:
151 Issue is that jai_rgm_balance_tmp is popualted with double amount.
152 Issue is with the jai_cmn_rgm_tax_dist_pkg.CALCULATE_RGM_BALANCES procedure
153
154 JAI_RGM_ORG_REGNS_V is retreiving 2 rows for TCS type of taxes.
155 This will occuer only if the organization is associated with more than one location.
156
157 Fix:
158 Removed the JAI_RGM_ORG_REGNS_V in cursor for delta_rec in (
159
160 Added the table jai_rgm_registrations table.
161
162 21 14-oct-2009 vkaranam for bug#9005474
163 Added the condition jrr.organization_id=nvl(p_org_id,jrr.organization_id)
164 as per review comments
165
166 22 03-Dec-2009 Added by Jia for FP Bug#6174148
167 Issue:
168 Vendor_id has been updated with org_id in table JA_IN_RG23_PART_II,
169 this caused the vendor name to be displayed instead of org_name in rg23 Part II form.
170 Fix:
171 This was a forward port issue of the R11i Bug#6129789.
172 Code changes are done in insert_records_into_register procedure.
173 Vendor_id has been inserted with -1 * org_id to solve the above issue.
174
175 22 19-Dec-2009 Eric for bug#8333082 and bug8671217
176
177 23 10-Mar-2009 Bug 9445836
178 Issue - New transactions which have transaction date lying in settled period
179 are not considered for the next settlement.
180 Fix - Changed the filter condition to fetch delta records in calculate_balances_for_ou
181 procedure. Instead of getting the transactions with date between last settlement date
182 and new settlement date, we fetch all unsettled transactions with date less than
183 the new settlement date.
184 Also modified the filter for update statements in punch_settlement_id procedure.
185 24 18-mar-2011 vkaranam for er#11821537
186 Description: Notification # 18/2011 proposes change in Point of Taxation for Service Tax.
187 Settlement UI and Process for partial Credit Utilization
188 i.e. Adjustment of liability w.r.t the credit will be restricted and is based on the
189 ptg provided in the regime registration setup.
190 Fix:
191 Added the code in calculate_balances_for_ou procedure to get credit utilization amount
192 and further passed the same to insert_records_into_temp procedure.
193 25 30-mar-2011 vkaranam for er#11821537 ( QA bug 11923714)
194 Issue:
195 STAX.18-2011.11I.QA: ERROR IN SERVICE TAX SETTLEMENT
196 credit utilized shall not be rounded.
197 Fix:
198 removed the rounding factor while calculating the credit_utilized column.
199 25 02-aug-2011 vkaranam for bug#12706846
200 Issue:
201 VAT Settlement Invoice in case of Settlement at registration number level
202 is imbalanced.
203 VAT Payment Invoice that got created correctly.
204 But when querying the Invoice in Invoice Workbench ,at Invoice Distributions
205 level only the debit lines are appearing and credit lines are not there.
206 As a result the amount in distribution and header level is not matching.
207
208 Fix:
209 Step 1.accounting entry at each and every organization level for setting off the liability with the recovery available
210 example :
211 Recovery(Credit_balance) Liability(Debit_balance) net_balance
212 IN1 20000 25000 -5000
213 IN2 17000 14000 +3000
214
215 Accounting entry shall be :
216
217 IN1:
218
219 Dr VAT liability 20000
220 Cr VAT recovery 20000
221
222 IN2:
223
224 Dr VAT liability 14000
225 Cr VAT recovery 14000
226
227 Step 2:
228
229 Distribute the credit balance available in one organization to the other organization which has the liability (debit balance).
230 Here the repository will be populated source_trx_type as "SETTLEMENT"
231
232 Also the accounting entry will be
233
234 IN1 Dr VAT liability +3000
235 IN2 Cr VAT Recovery +3000
236
237 Logic for the distribution of credit balance from source org to debit balance of destination org is as follows:
238 --fetch the net_balance>0 i.e organizations with the credit available in the DESCENDING ORDER.
239 --fetch the net_balance<0 i.e organizations with the debit available in the ASCENDING ORDER.
240
241 Step 3:
242 Invoice lines shall be generated for the organizations if
243 the net_balance + distribution_amount received from other organizations is < 0
244
245 Changes are done in populate_all_orgs_vat procedure.
246 26 18-sep-2011 vkaranam for bug#12706846 ( 12996230)
247 Issue: Credit balance shown in the settlement screen is wrong.
248 Fix : changes are done in populate_all_orgs_vat procedure.
249
250 27 01-Feb-2012 amandali for bug 13534704
251 Issue: Distribution number not matching with invoice number in RG 23 Part II view transactions form
252 Fix: Added cursor c_get_src_rec to fetch distribution number from jai_rgm_dis_src_hdrs
253 the parameter p_excise_invoice_no in procedure call create_io_register_entry has been modified to distribution number rather
254 than the transfer id prefixed with Distribution
255
256 28 21-mar-2012 vkaranam for bug#13865856
257 Issue:Service type to be made optional for Service tax distribution.
258 fix:changes are done in calculate_balances_for_ou procedure.
259 added the nvl condition for service_type_code condition ,which are used for fetching
260 the balances.
261
262 29 10-AUG-2012 amandali for bug 14475128
263 Issue:SER. TAX DISTRIBUTION FORM DISPLAYING AMOUNT ALREADY DISTRIBUTED TO EXCISE
264 Fix:changes are done in calculate_balances_for_ou procedure.Modified the condition for service_type_code to handle the records with service_type_code as null
265
266 -- #
267 -- # Change History -
268 -- # Future Dependencies For the release Of this Object:-
269 -- # (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/
270 -- # A datamodel change )
271 --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
272 Current Version Current Bug Dependent Files Version Author Date Remarks
273 Of File On Bug/Patchset Dependent On
274 ------------------------------------------------------------------------------------------------------------------------------------------------------------------
275 115.1 4245365 4245089 rchandan 17/Mar/05 Changes made to implement VAT
276 115.2 4245365 4245089 rchandan 20/03/2005 Observations in VAT. From now when we are settling
277 balances the opening balance of the last settlemnt date is not considered if it was completely settled.
278 Only the transaction amount in the delta period is taken into consideration. If it is not settled then
279 the settlement balances are taken into consideration
280
281
282 11.22-jun-2007 kunkumar made changes for bug#6127194 file 120.11
283 Added package body to create_io_register_entry and
284 made calls to the proc from insert_into_register proc.
285
286 --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
287 ***************************************************************************************************/
288
289
290 PROCEDURE insert_records_into_temp(
291 p_request_id NUMBER ,
292 p_regime_id NUMBER ,
293 p_party_type VARCHAR2 ,
294 p_party_id NUMBER ,
295 p_location_id NUMBER ,
296 p_bal_date DATE ,
297 p_tax_type VARCHAR2 ,
298 p_debit_amt NUMBER ,
299 p_credit_amt NUMBER ,
300 /* Bug4568078. Added by Lakshmi Gopalsami */
301 p_pla_balance NUMBER default NULL,
302 p_service_type_code VARCHAR2 DEFAULT NULL,/*Bug 5879769 bduvarag*/
303 /**added p_credit_utilized for bug#11821537 by vkaranam,budget 2011 phase2*/
304 p_credit_utilized NUMBER default null
305 ,p_reverse_charge_flag VARCHAR2 DEFAULT NULL --Added by Qiong for reverse charge settlement
306 )
307 is
308 /* Added by Ramananda for bug#4407165 */
309 lv_object_name CONSTANT VARCHAR2(61) := 'jai_cmn_rgm_tax_dist_pkg.insert_records_into_temp';
310
311 BEGIN
312 INSERT INTO JAI_RGM_BALANCE_T
313 (
314 REQUEST_ID ,
315 REGIME_ID ,
316 PARTY_TYPE ,
317 PARTY_ID ,
318 LOCATION_ID ,
319 BALANCE_DATE ,
320 TAX_TYPE ,
321 DEBIT_AMT ,
322 CREDIT_AMT ,
323 CREATION_DATE ,
324 CREATED_BY ,
325 LAST_UPDATE_DATE ,
326 LAST_UPDATED_BY ,
327 LAST_UPDATE_LOGIN ,
328 program_application_id,
329 program_id,
330 program_login_id,
331 /* Bug 4568078. Added by Lakshmi Gopalsami */
332 pla_balance,
333 service_type_code /*Bug 5879769 bduvarag*/
334 ,credit_utilized --11821537
335 ,reverse_charge_flag --Added by qiong for reverse charge settlement
336 )
337 VALUES
338 (
339 p_request_id ,
340 p_regime_id ,
341 p_party_type ,
342 p_party_id ,
343 p_location_id ,
344 p_bal_date ,
345 p_tax_type ,
346 round(p_debit_amt,ln_rounding_precision) ,
347 round(p_credit_amt,ln_rounding_precision) ,
348 sysdate ,
349 fnd_global.user_id ,
350 sysdate ,
351 fnd_global.user_id ,
352 fnd_global.login_id ,
353 fnd_profile.value('PROG_APPL_ID'),
354 fnd_profile.value('CONC_PROGRAM_ID'),
355 fnd_profile.value('CONC_LOGIN_ID'),
356 /* Bug 4568078. Added by Lakshmi Gopalsami */
357 p_pla_balance,
358 p_service_type_code/*Bug 5879769 bduvarag*/
359 , p_credit_utilized --11821537
360 , p_reverse_charge_flag --Added by qiong for reverse charge settlement
361 );
362 /* Added by Ramananda for bug#4407165 */
363 EXCEPTION
364 WHEN OTHERS THEN
365 FND_MESSAGE.SET_NAME('JA','JAI_EXCEPTION_OCCURED');
366 FND_MESSAGE.SET_TOKEN('JAI_PROCESS_MSG', lv_object_name ||'. Err:'||sqlerrm );
367 app_exception.raise_exception;
368 END insert_records_into_temp;
369
370
371 --Added by Eric Ma for bug 8333082 on Dec-19-2009,begin
372 --------------------------------------------------------------------------------------
373 PROCEDURE populate_all_orgs_vat( p_regn_no jai_rgm_org_regns_v.attribute_value%TYPE,
374 p_regn_id jai_rgm_org_regns_v.registration_id%TYPE,
375 p_regime_id JAI_RGM_DEFINITIONS.regime_id%TYPE,
376 p_request_id NUMBER,
377 p_balance_date DATE,
378 p_org_type VARCHAR2 default NULL,
379 p_settlement_id number,
380 p_cramt_considered number --8671217
381 )
382 IS
383 /*12706846,populate_all_orgs_vat procedure logic has beem changed.
384 Prior to fix 12706846 : populate_all_orgs_vat will populate the credit_balances available for the organizations
385 which are not considered in the current settlement period .
386 and there will be only one record inserted in jai_rgm_balances_t as per the logic
387 and the organization,location insertion depends on the cursor fetching
388 fix 12706846 : populate_all_orgs_vat will populate the credit_balances available for ALL the organizations
389 which are not considered in the current settlement period .
390 */
391 --this cursor will fetch the credit balance from the organization which doesnot have transactions in the current settlement period
392 /*Added parameter cp_regime_id by mmurtuza for bug 12641455*/
393 CURSOR c_other_orgs(cp_regn_no jai_rgm_org_regns_v.attribute_value%TYPE,cp_regn_id jai_rgm_org_regns_v.registration_id%TYPE, cp_regime_id jai_rgm_org_regns_v.regime_id%type)
394 IS
395 SELECT organization_id,
396 location_id
397 FROM jai_rgm_org_regns_v
398 WHERE --regime_code = jai_constants.vat_regime -- commneted by mmurtuza for bug 12641455
399 regime_id = cp_regime_id -- added by mmurtuza for bug 12641455
400 AND attribute_code ='REGISTRATION_NO'
401 AND attribute_value = cp_regn_no
402 AND registration_id = cp_regn_id
403 AND (organization_id, location_id) NOT IN
404 (SELECT party_id, location_id FROM JAI_RGM_BALANCE_T where request_id=p_request_id
405 );
406
407 /*uncommeneted the code done in bug#8671217 for bug#12706846*/
408 CURSOR cur_cr_bal(cp_regime_id JAI_RGM_DEFINITIONS.regime_id%TYPE,cp_org_id number,cp_location_id number)
409 IS
410 SELECT
411 --(credit_balance - debit_balance) cr_balance, 8671217
412 ( sum(nvl(credit_balance,0))-sum(nvl(debit_balance,0))) cr_balance ,
413 tax_type
414 FROM jai_rgm_stl_balances stl
415 WHERE settlement_id =
416 (SELECT MAX(jstl.settlement_id)
417 FROM jai_rgm_stl_balances jstl,
418 jai_rgm_Settlements jrs
419 WHERE
420 jstl.party_id = cp_org_id /* 8671217 ,12706846*/
421 AND jstl.location_id = cp_location_id /* 8671217 ,12706846*/
422 AND jstl.settlement_id = jrs.settlement_id
423 and jstl.settlement_id<>p_settlement_id ---8671217 (To exclude from the current settlement)
424 AND jrs.regime_id = cp_regime_id
425 )
426 /*added the where clause for bug#12706846*/
427 and stl.party_id = cp_org_id
428 AND stl.location_id = cp_location_id
429 group by tax_type;---8671217
430 -- AND stl.party_id = cp_org_id 8671217
431 --- AND stl.location_id = cp_location_id; 8671217
432
433
434 /* start additions by vkaranam for bug#12706846 ( 12996230) ,15sep
435 Issue: Credit balance shown in the settlement screen is wrong.
436 Reason:
437
438 Consider the below example to understand the issue better:
439
440 I1 ,I2,I3,I4 are there under registration number 'ABC'.
441 registration level settlement has been performed in the month of may .
442
443 jai_rgm_Stl_balances are populated with the below for MAY:
444 Credit_balance debit_balance
445 I1 1000 2500
446 I2 3000 4000
447 I3 2000 5000
448 I4 5000 0
449
450 Payment amount : 500Rs.
451
452 Transactions in the month of Jun :
453 Credit Debit
454 I1 2000 1000
455 I2 0 5000
456 I3 1000 0
457
458 I4 does not have any transaction in the month of Jun.
459
460 Settlement is showing the below for JUN:
461
462 Credit Debit
463 8000 6000
464
465 Ideally the credit shall show 3000 (I1+I2+I3).
466 Difference is of 5000 (i.e I4 credit _balance available for the month of May
467 jai_rgm_Stl_balances.credit_balance)
468
469 The above discrepancy is caused due to the credit population logic in populate_all_orgs_vat
470
471 Fix :
472
473 If the I4 organization has been considered for the earlier settlement with the payment_amount>0
474 then the organization balance will not be carried forward.
475
476 Added the check_org_settled cursor.
477 */
478
479
480 cursor check_org_settled(cp_regime_id in number,cp_org_id in number,cp_location_id in number)
481 is
482 select '1'
483 FROM JAI_RGM_SETTLEMENTS stL
484 WHERE settlement_id =
485 (SELECT MAX(jstl.settlement_id)
486 FROM jai_rgm_stl_balances jstl,
487 jai_rgm_Settlements jrs
488 WHERE
489 jstl.party_id = cp_org_id /* 8671217 ,12706846*/
490 AND jstl.location_id = cp_location_id /* 8671217 ,12706846*/
491 AND jstl.settlement_id = jrs.settlement_id
492 and jstl.settlement_id<>p_settlement_id ---8671217 (To exclude from the current settlement)
493 AND jrs.regime_id = cp_regime_id
494 )
495 AND payment_amount>0;
496
497 lv_check_org_settled varchar2(1);
498 --end additions for bug#12706846 , 14 sep
499
500
501
502 rec_other_orgs c_other_orgs%ROWTYPE;
503 rec_cr_bal cur_cr_bal%ROWTYPE;
504 ln_cr_balance NUMBER ;
505 ln_credit_set_off NUMBER; /*Bug 12656570*/
506 BEGIN
507
508 ln_credit_set_off := 0;
509 OPEN c_other_orgs(p_regn_no,p_regn_id, p_regime_id); --added parameter p_regime_id while opening cursor by mmurtuza for bug 12641455
510 LOOP
511 FETCH c_other_orgs INTO rec_other_orgs ;
512 EXIT WHEN c_other_orgs%NOTFOUND;
513
514 --OPEN cur_cr_bal(rec_other_orgs.organization_id,rec_other_orgs.location_id,p_regime_id);
515 OPEN cur_cr_bal(p_regime_id,rec_other_orgs.organization_id,rec_other_orgs.location_id);--12789169
516 LOOP
517 ln_cr_balance:=0;--12789169
518 FETCH cur_cr_bal INTO rec_cr_bal;
519 EXIT
520 WHEN cur_cr_bal%NOTFOUND;
521 IF rec_cr_bal.cr_balance < 0 THEN
522 ln_cr_balance:= 0;
523 else
524 /*start additions by vkaranam for bug#12706846 ,15sep */
525 lv_check_org_settled := null;
526 open check_org_settled(p_regime_id,rec_other_orgs.organization_id,rec_other_orgs.location_id);
527 fetch check_org_settled into lv_check_org_settled;
528 close check_org_settled;
529 if lv_check_org_settled ='1'
530 then
531 ln_cr_balance := 0;
532 else
533 /*end additions by vkaranam for bug#12706846 ,15sep */
534 ln_cr_balance:= rec_cr_bal.cr_balance;
535 end if;
536 END IF;
537 /*commeneted by vkaranam for bug#12789169
538 --start additions for bug#8671217 (13-aug-2009)
539 /***credit balance will updated only for the 1st organization in the c_other_orgs loop
540 for the remaining orgs cr_balance is 0.
541 ***/
542 /*12656570 - Credit Balance should never go below zero*/
543 /*
544 IF ln_credit_set_off = 0 AND rec_cr_bal.cr_balance > 0 THEN
545 ln_cr_balance:= rec_cr_bal.cr_balance - nvl(p_cramt_considered,0);
546 IF ln_cr_balance < 0 THEN
547 ln_cr_balance := 0;
548 END IF;
549 ln_credit_set_off := 1;
550 else
551 ln_cr_balance :=0;
552 end if;
553 --end additions for bug#8671217 (13-aug-2009)
554 */
555
556 insert_records_into_temp( p_request_id => p_request_id ,
557 p_regime_id => p_regime_id ,
558 p_party_type => p_org_type ,
559 p_party_id => rec_other_orgs.organization_id,
560 p_location_id => rec_other_orgs.location_id ,
561 p_bal_date => p_balance_date ,
562 p_tax_type => rec_cr_bal.tax_type ,
563 p_debit_amt => 0 ,
564 p_credit_amt => ln_cr_balance ,--rec_cr_bal.cr_balance,
565 p_pla_balance => NULL,
566 p_service_type_code => NULL
567 );
568 END LOOP;
569 CLOSE cur_cr_bal;
570 END LOOP;
571 CLOSE c_other_orgs;
572 /*un commented above and commented the below for bug# 8671217
573
574 --start additions for bug#8671217
575 OPEN c_other_orgs(p_regn_no,p_regn_id);
576 fetch c_other_orgs INTO rec_other_orgs ;
577 close c_other_orgs;
578
579
580
581 open cur_cr_bal(p_regime_id);
582 loop
583 FETCH cur_cr_bal INTO rec_cr_bal;
584 EXIT WHEN cur_cr_bal%NOTFOUND;
585
586
587 IF rec_cr_bal.cr_balance < 0 THEN
588 rec_cr_bal.cr_balance :=0;
589 END IF;
590 insert_records_into_temp( p_request_id => p_request_id ,
591 p_regime_id => p_regime_id ,
592 p_party_type => p_org_type ,
593 p_party_id => rec_other_orgs.organization_id,
594 p_location_id => rec_other_orgs.location_id ,
595 p_bal_date => p_balance_date ,
596 p_tax_type => rec_cr_bal.tax_type ,
597 p_debit_amt => 0 ,
598 p_credit_amt => rec_cr_bal.cr_balance,
599 p_pla_balance => NULL,
600 p_service_type_code => NULL
601 );
602 END LOOP;
603 CLOSE cur_cr_bal;
604 --end bug#8671217
605 */
606
607 END populate_all_orgs_vat;
608 --------------------------------------------------------------------------------
609 --Added by Eric Ma for bug 8333082 on Dec-19-2009,End
610
611
612 PROCEDURE calculate_balances_for_io(p_regime_id number ,
613 p_balance_date date ,
614 p_request_id number,
615 p_service_type_code VARCHAR2 DEFAULT NULL/*Bug 5879769 bduvarag*/
616 ) is
617 /* Added by Ramananda for bug#4407165 */
618 lv_object_name CONSTANT VARCHAR2(61) := 'jai_cmn_rgm_tax_dist_pkg.calculate_balances_for_io';
619 /*
620 ||CSahoo. Bug 5073553. removed the where clause that the nvl(rg23A_balance,0) + nvl(rg23c_balance,0) <> 0
621 ||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.
622 */
623 CURSOR c_balance_cur is
624 SELECT org_unit_id org_unit_id ,
625 organization_id party_id ,
626 location_id location_id ,
627 'EXCISE' tax_type ,
628 nvl(rg23A_balance,0) + nvl(rg23c_balance,0) Balance ,
629 'IO' party_type,
630 /* Bug 4568078. Added by LGOPALSA */
631 nvl(pla_balance,0) pla_balance
632 FROM JAI_CMN_RG_BALANCES;
633 --WHERE nvl(rg23A_balance,0) + nvl(rg23c_balance,0) <> 0 ;
634
635 CURSOR c_cess_balance (cp_org_unit_id number) is
636 SELECT SUM(balance)
637 FROM JAI_CMN_RG_OTH_BALANCES
638 WHERE org_unit_id = cp_org_unit_id
639 AND register_type IN (jai_constants.reg_rg23a ,jai_constants.reg_rg23c)--rchandan for bug#4428980
640 AND tax_type IN (jai_constants.tax_type_cvd_edu_cess,jai_constants.tax_type_exc_edu_cess);--rchandan for bug#4428980
641 ln_cess_balance number :=0;
642 /* Bug 4568078. Added by Lakshmi Gopalsami */
643
644 CURSOR c_pla_cess_balance (cp_org_unit_id number) is
645 SELECT SUM(balance)
646 FROM JAI_CMN_RG_OTH_BALANCES
647 WHERE org_unit_id = cp_org_unit_id
648 AND register_type = jai_constants.reg_pla
649 AND tax_type IN (jai_constants.tax_type_cvd_edu_cess,jai_constants.tax_type_exc_edu_cess) ;
650
651 --added by csahoo for bug#6109941, start
652 CURSOR c_sh_cess_balance (cp_org_unit_id number) is
653 SELECT SUM(balance)
654 FROM JAI_CMN_RG_OTH_BALANCES
655 WHERE org_unit_id = cp_org_unit_id
656 AND register_type IN (jai_constants.reg_rg23a ,jai_constants.reg_rg23c)
657 AND tax_type IN (jai_constants.tax_type_sh_cvd_edu_cess,jai_constants.tax_type_sh_exc_edu_cess);
658
659
660
661 CURSOR c_sh_pla_cess_balance (cp_org_unit_id number) is
662 SELECT SUM(balance)
663 FROM JAI_CMN_RG_OTH_BALANCES
664 WHERE org_unit_id = cp_org_unit_id
665 AND register_type = jai_constants.reg_pla
666 AND tax_type IN (jai_constants.tax_type_sh_cvd_edu_cess,jai_constants.tax_type_sh_exc_edu_cess) ;
667
668 ln_sh_cess_balance number :=0;
669 ln_sh_pla_cess_balance NUMBER :=0;
670 ln_sh_debit_amt NUMBER :=0;
671 -- --added by csahoo for bug#6109941, end
672
673 ln_rg23_cess_balance NUMBER :=0;
674 ln_pla_cess_balance NUMBER :=0;
675 ln_debit_amt NUMBER :=0;
676 ln_pla_balance NUMBER :=0;
677 /* End for bug 4568078. Added by Lakshmi Gopalsami */
678
679 BEGIN
680 FOR bal_rec in c_balance_cur
681 LOOP
682
683 /*
684 ||Start of bug 5073553.Added by CSahoo
685 ||Initialize the variables
686 */
687
688 /*Start of bug 5073553*/
689 ln_debit_amt := 0;
690 ln_pla_balance := 0;
691 ln_rg23_cess_balance := 0;
692 ln_pla_cess_balance := 0;
693
694 ln_sh_cess_balance := 0; --added by csahoo for bug#6109941
695 ln_sh_pla_cess_balance := 0; --added by csahoo for bug#6109941
696
697 IF nvl(bal_rec.pla_balance,0) < 0 THEN
698 /*
699 ||-ve pla balance, then the amount should appear as the debit amt in the JAI_RGM_BALANCE_T table
700 */
701 /*Bug 8488788 - If the amount is represented on the debit side it infers that it is less than 0
702 Hence representing as positive. The net amount in Service Tax Distribution form is calculated by
703 subtracting debit from credit. If it is represented as -ve then it causes double effect*/
704 ln_debit_amt := -bal_rec.pla_balance;
705 ELSE
706 /*
707 || +ve pla balance then the amount should appear in the pla_balance
708 */
709 ln_pla_balance := bal_rec.pla_balance;
710 END IF;
711 /* End of bug 5073553*/
712 insert_records_into_temp(
713 p_request_id => p_request_id ,
714 p_regime_id => p_regime_id ,
715 p_party_type => bal_rec.party_type ,
716 p_party_id => bal_rec.party_id ,
717 p_location_id => bal_rec.location_id ,
718 p_bal_date => p_balance_date ,
719 p_tax_type => bal_rec.tax_type ,
720 /* changed by CSahoo for bug 5073553.
721 ||put the variable ln_debit_amt instead of 0
722 */
723 p_debit_amt => ln_debit_amt ,
724 p_credit_amt => bal_rec.balance ,
725 p_pla_balance => ln_pla_balance,
726 /* Bug 4568078. Added by Lakshmi Gopalsami */
727 p_service_type_code => p_service_type_code/*Bug 5879769 bduvarag*/
728 );
729
730 ln_debit_amt := 0; -- Added by CSahoo, BUG#5073553
731 OPEN c_cess_balance(bal_rec.org_unit_id);
732 FETCH c_cess_balance into ln_cess_balance;
733 CLOSE c_cess_balance;
734
735 OPEN c_pla_cess_balance(bal_rec.org_unit_id);
736 FETCH c_pla_cess_balance into ln_pla_cess_balance;
737 CLOSE c_pla_cess_balance;
738
739 /*
740 ||Start of bug 5073553.added by CSahoo
741 ||If the cess balance is less than 0 then the same should appear in the debit_amt column of the JAI_RGM_BALANCE_T table
742 || else it should appear in the pla_balance column
743 */
744 IF nvl(ln_pla_cess_balance,0) < 0 THEN
745 /*Bug 8488788 - If the amount is represented on the debit side it infers that it is less than 0
746 Hence representing as positive. The net amount in Service Tax Distribution form is calculated by
747 subtracting debit from credit. If it is represented as -ve then it causes double effect*/
748 ln_debit_amt := -ln_pla_cess_balance;
749 ln_pla_cess_balance := 0;
750 END IF;
751 /* End of bug 5073553*/
752 insert_records_into_temp(
753 p_request_id => p_request_id ,
754 p_regime_id => p_regime_id ,
755 p_party_type => bal_rec.party_type ,
756 p_party_id => bal_rec.party_id ,
757 p_location_id => bal_rec.location_id ,
758 p_bal_date => p_balance_date ,
759 p_tax_type => 'EXCISE-CESS' ,
760 p_debit_amt => ln_debit_amt , -- Added by CSahoo, BUG#5073553
761 p_credit_amt => ln_cess_balance ,
762 p_pla_balance => ln_pla_cess_balance,
763 p_service_type_code => p_service_type_code/*Bug 5879769 bduvarag*/
764 );
765 --added by csahoo for bug#6109941
766 --start
767 ln_sh_debit_amt := 0;
768 OPEN c_sh_cess_balance(bal_rec.org_unit_id);
769 FETCH c_sh_cess_balance into ln_sh_cess_balance;
770 CLOSE c_sh_cess_balance;
771
772 OPEN c_sh_pla_cess_balance(bal_rec.org_unit_id);
773 FETCH c_sh_pla_cess_balance into ln_sh_pla_cess_balance;
774 CLOSE c_sh_pla_cess_balance;
775
776 IF nvl(ln_sh_pla_cess_balance,0) < 0 THEN
777 /*Bug 8488788 - If the amount is represented on the debit side it infers that it is less than 0
778 Hence representing as positive. The net amount in Service Tax Distribution form is calculated by
779 subtracting debit from credit. If it is represented as -ve then it causes double effect*/
780 ln_sh_debit_amt := -ln_sh_pla_cess_balance;
781 ln_sh_pla_cess_balance := 0;
782 END IF;
783
784
785 insert_records_into_temp(
786 p_request_id => p_request_id ,
787 p_regime_id => p_regime_id ,
788 p_party_type => bal_rec.party_type ,
789 p_party_id => bal_rec.party_id ,
790 p_location_id => bal_rec.location_id ,
791 p_bal_date => p_balance_date ,
792 p_tax_type => 'EXCISE_SH_EDU_CESS' ,
793 p_debit_amt => ln_sh_debit_amt ,
794 p_credit_amt => ln_sh_cess_balance ,
795 p_pla_balance => ln_sh_pla_cess_balance,
796 p_service_type_code => p_service_type_code
797 );
798 --added by csahoo for bug#6109941, end
799
800 END LOOP;
801 /* Added by Ramananda for bug#4407165 */
802 EXCEPTION
803 WHEN OTHERS THEN
804 FND_MESSAGE.SET_NAME('JA','JAI_EXCEPTION_OCCURED');
805 FND_MESSAGE.SET_TOKEN('JAI_PROCESS_MSG', lv_object_name ||'. Err:'||sqlerrm );
806 app_exception.raise_exception;
807 END calculate_balances_for_io;
808
809
810 PROCEDURE punch_settlement_id( p_regime_id number,
811 p_settlement_id number ,
812 p_regn_id number ,
813 p_balance_date date,
814 P_TAN_NO VARCHAR2 DEFAULT NULL, -- ADDED BY SACSETHI ON 30-01-2007 FOR BUG 5631784
815 p_org_id NUMBER default NULL,/*rchandan for bug#5642053*/
816 p_location_id NUMBER default NULL,/*rchandan for bug#5642053*/
817 P_ITEM_CLASSIFICATION VARCHAR2 DEFAULT NULL, -- ADDED BY SACSETHI ON 30-01-2007 FOR BUG 5631784
818 p_regn_no VARCHAR2 default NULL/*6835541*/
819 ,p_reverse_charge_flag VARCHAR2 default NULL--Added by Qiong for reverse charege settlement
820 )
821 is
822 /* Added by Ramananda for bug#4407165 */
823 lv_object_name CONSTANT VARCHAR2(61) := 'jai_cmn_rgm_tax_dist_pkg.punch_settlement_id';
824
825 CURSOR cur_regime_code IS /* 4245365*/
826 SELECT regime_code
827 FROM JAI_RGM_DEFINITIONS -- ADDED BY SACSETHI ON 30-01-2007 FOR BUG 5631784
828 WHERE regime_id = p_regime_id;
829 lv_regime JAI_RGM_DEFINITIONS.regime_code%TYPE;
830
831 BEGIN
832 OPEN cur_regime_code; /* 4245365*/
833 FETCH cur_regime_code INTO lv_regime;
834 CLOSE cur_regime_code;
835 IF lv_regime IN ('SERVICE') THEN /*6835541.removed VAT*/
836 UPDATE jai_Rgm_trx_records
837 SET settlement_id = p_settlement_id
838 /*Bug 5879769 bduvarag*/
839 WHERE organization_id = p_org_id
840 AND location_id = p_location_id
841 AND regime_code = lv_regime
842 AND trunc(transaction_date) <= p_balance_date /*bug 9445836*/
843 AND settlement_id IS NULL /* added by ssawant for bug 5662296*/
844 AND ( (NVL(p_reverse_charge_flag,'N')='N' AND source_trx_type NOT IN ('REVERSE_PAID_ACCOUNTING','REVERSE_AVOID_ACCOUNTING','REVERSE_APPLY_ACCOUNTING','REVERSE_UNAPPLY_ACCOUNTING'))
845 OR (NVL(p_reverse_charge_flag,'N')='Y' AND source_trx_type IN ('REVERSE_PAID_ACCOUNTING','REVERSE_AVOID_ACCOUNTING','REVERSE_APPLY_ACCOUNTING','REVERSE_UNAPPLY_ACCOUNTING'))
846 ) ;--Added by Qiong for reverse charge settlement
847
848 ELSIF lv_regime IN ('VAT') THEN /*6835541*/
849
850 UPDATE jai_Rgm_trx_records
851 SET settlement_id = p_settlement_id
852 WHERE (organization_id,location_id) in
853 (SELECT organization_id,location_id
854 FROM JAI_RGM_ORG_REGNS_V
855 WHERE registration_id = p_regn_id
856 AND attribute_value = nvl(p_regn_no, attribute_value) -- 6835541. Added by Lakshmi Gopalsami
857 AND regime_code = 'VAT'
858 AND organization_id = nvl(p_org_id,organization_id)
859 AND location_id = nvl(p_location_id,location_id)
860 )
861 AND regime_code = lv_regime --added for bug#8289991
862 AND trunc(transaction_date) <= p_balance_date /*bug 9445836*/
863 AND settlement_id IS NULL;
864
865 ELSIF lv_regime = jai_constants.tcs_regime THEN
866 UPDATE JAI_RGM_REFS_ALL
867 SET SETTLEMENT_ID = P_SETTLEMENT_ID
868 WHERE ORG_TAN_NO = P_TAN_NO AND
869 ITEM_CLASSIFICATION = P_ITEM_CLASSIFICATION AND
870 TRUNC(SOURCE_DOCUMENT_DATE) <= P_BALANCE_DATE AND
871 SETTLEMENT_ID IS NULL;
872 -- end 5631784
873 END IF;
874 /* Added by Ramananda for bug#4407165 */
875 EXCEPTION
876 WHEN OTHERS THEN
877 FND_MESSAGE.SET_NAME('JA','JAI_EXCEPTION_OCCURED');
878 FND_MESSAGE.SET_TOKEN('JAI_PROCESS_MSG', lv_object_name ||'. Err:'||sqlerrm );
879 app_exception.raise_exception;
880 END punch_settlement_id;
881
882
883 PROCEDURE calculate_balances_for_ou(p_regime_id number ,
884 p_balance_date date ,
885 p_request_id number ,
886 p_org_id number ,
887 p_org_type varchar2 ,
888 p_regn_id number ,
889 p_regn_no varchar2 ,
890 p_settlement_id number ,
891 p_called_from varchar2,
892 p_location_id number default null, /*rchandan for bug#5642053*/
893 p_service_type_code varchar2 default null -- bduvarag for Bug 5694855
894 ,p_reverse_charge_flag VARCHAR2 DEFAULT NULL --Added by Qiong for reverse charge settlement
895 ) is
896 /* Added by Ramananda for bug#4407165 */
897 lv_object_name CONSTANT VARCHAR2(61) := 'jai_cmn_rgm_tax_dist_pkg.calculate_balances_for_ou';
898 lv_source_trx_type jai_rgm_trx_records.source_trx_type%TYPE ;--rchandan for bug#4428980
899 lv_reg_type jai_rgm_registrations.registration_type%TYPE ;
900
901 CURSOR c_regime_code IS
902 SELECT regime_code
903 FROM JAI_RGM_DEFINITIONS
904 WHERE regime_id = p_regime_id;
905 -- ld_trx_date DATE; /* commented by ssawant for bug 5662296*/
906 lv_regime_code JAI_RGM_DEFINITIONS.regime_code%TYPE;
907
908 /*Bug 5879769 bduvarag start*/
909
910 CURSOR cur_inv_payment
911 IS
912 /*
913 || This cursor is used to get the total invoice amount paid
914 || when the last settlement was made
915 */
916 SELECT sum(credit_amount)
917 FROM jai_rgm_trx_records
918 WHERE regime_primary_regno = p_regn_no
919 AND source_trx_type = 'Invoice Payment'
920 AND settlement_id = ( SELECT MAX(jbal.settlement_id)
921 FROM jai_rgm_stl_balances jbal,jai_rgm_settlements jstl
922 WHERE jbal.settlement_id = jstl.settlement_id
923 AND jstl.primary_registration_no = p_regn_no
924 AND jbal.party_type = p_org_type
925 AND jbal.party_id = p_org_id
926 AND nvl(jbal.location_id,-999) = nvl(p_location_id,-999)
927 AND jbal.settlement_id <> nvl(p_settlement_id,-999)
928 );
929
930 CURSOR cur_inv_payment_dist_io(cp_org_type VARCHAR2,
931 cp_org_id NUMBER ,
932 cp_tax_type VARCHAR2,
933 cp_location_id NUMBER ,
934 cp_service_type_code VARCHAR2
935 )
936 IS
937 /*
938 || This cursor is used to get the total invoice amount paid
939 || when the last settlement was made. This is same as above cursor but it does not use registration no.
940 || This cursor used when procedure is invoked from Distribution and so it does not have registration
941 || details
942 */
943 SELECT credit_amount
944 FROM jai_rgm_trx_records
945 WHERE source_trx_type = 'Invoice Payment'
946 -- AND service_type_code = nvl(cp_service_type_code,service_type_code)/*added nvl for bug#13865856*/
947 and (cp_service_type_code is null or service_type_code =cp_service_type_code) /*Commented the above and added the condition for bug 14475128 */
948 AND settlement_id = ( SELECT MAX(jbal.settlement_id)
949 FROM jai_rgm_stl_balances jbal,jai_rgm_settlements jstl
950 WHERE jbal.settlement_id = jstl.settlement_id
951 AND jbal.party_type = cp_org_type
952 AND jbal.party_id = cp_org_id
953 AND jbal.location_id = nvl(cp_location_id,jbal.location_id)/*5694855*/
954 AND jbal.tax_type = cp_tax_type
955 -- AND jbal.service_type_code = nvl(cp_service_type_code,jbal.service_type_code)/*added nvl for bug#13865856*/
956 and (cp_service_type_code is null or jbal.service_type_code =cp_service_type_code) /*Commented the above and added the condition for bug 14475128 */
957 );
958
959
960 CURSOR cur_inv_payment_dist(cp_org_type VARCHAR2,
961 cp_org_id NUMBER ,
962 cp_tax_type VARCHAR2
963 )
964 IS
965 /*
966 || This cursor is used to get the total invoice amount paid
967 || when the last settlement was made. This is same as above cursor but it does not use registration no.
968 || This cursor used when procedure is invoked from Distribution and so it does not have registration
969 || details
970 */
971 SELECT credit_amount
972 FROM jai_rgm_trx_records
973 WHERE /*regime_primary_regno = p_regn_no*/ -- Commented, Harshita for Bug 5694855
974 source_trx_type = 'Invoice Payment'
975 AND settlement_id = ( SELECT MAX(jbal.settlement_id)
976 FROM jai_rgm_stl_balances jbal,jai_rgm_settlements jstl
977 WHERE jbal.settlement_id = jstl.settlement_id
978 AND jbal.party_type = cp_org_type
979 AND jbal.party_id = cp_org_id
980 AND jbal.tax_type = cp_tax_type
981 );
982
983 CURSOR cur_balances /* Modified by vumaasha for bug 7606212 */
984 IS
985 /*
986 || This cursor is used to retrieve the sum of credit and debit balances as on
987 || last settlement date for the given registration number,organization and location grouped at the tax type
988 */
989 SELECT sum(credit_balance) credit_balance,sum(debit_balance) debit_balance,jrs.settlement_id,jrs.tax_type,jrs.location_id
990 FROM jai_rgm_stl_balances jrs,
991 (
992 SELECT MAX(jbal.settlement_id) settlement_id,tax_type
993 FROM jai_rgm_stl_balances jbal,jai_rgm_settlements jstl
994 WHERE jbal.settlement_id = jstl.settlement_id
995 AND jstl.primary_registration_no = p_regn_no
996 AND jbal.party_type = p_org_type
997 AND jbal.party_id = p_org_id
998 AND NVL(jbal.location_id,-999) = NVL(p_location_id,-999)
999 AND jbal.settlement_id <> p_settlement_id/*This clause is used to exclude the current settlement*/
1000 GROUP BY jbal.tax_type
1001 ) sv
1002 WHERE
1003 jrs.settlement_id=sv.settlement_id
1004 AND jrs.tax_type= sv.tax_type
1005 GROUP BY jrs.settlement_id,jrs.tax_type,jrs.location_id ;
1006
1007 CURSOR cur_last_settlement_id
1008 /* added this cursor for bug 7606212 by vumaasha */
1009 IS
1010 SELECT MAX(jbal.settlement_id)
1011 FROM jai_rgm_stl_balances jbal,jai_rgm_settlements jstl
1012 WHERE jbal.settlement_id = jstl.settlement_id
1013 AND jstl.primary_registration_no = p_regn_no
1014 AND jbal.party_type = p_org_type
1015 AND jbal.party_id = p_org_id
1016 AND NVL(jbal.location_id,-999) = NVL(p_location_id,-999)
1017 AND jbal.settlement_id <> p_settlement_id;
1018
1019 /*6835541..start*/
1020
1021 CURSOR cur_inv_payment_vat(cp_organization_type VARCHAR2,
1022 cp_organization_id NUMBER,
1023 cp_location_id NUMBER
1024 )
1025 IS
1026 /*
1027 || This cursor is used to get the total invoice amount paid
1028 || when the last settlement was made for VAT regime
1029 */
1030 SELECT sum(credit_amount)
1031 FROM jai_rgm_trx_records
1032 WHERE regime_primary_regno = p_regn_no
1033 AND source_trx_type = 'Invoice Payment'
1034 AND settlement_id = ( SELECT MAX(jbal.settlement_id)
1035 FROM jai_rgm_stl_balances jbal,
1036 jai_rgm_settlements jstl,
1037 jai_rgm_definitions jrg
1038 WHERE jbal.settlement_id = jstl.settlement_id
1039 AND jrg.regime_id = jstl.regime_id
1040 AND jrg.regime_code = 'VAT'
1041 AND jstl.primary_registration_no = p_regn_no
1042 AND jbal.party_type = cp_organization_type
1043 AND jbal.party_id = cp_organization_id
1044 AND jbal.location_id = cp_location_id
1045 AND jbal.settlement_id <> nvl(p_settlement_id,-999) /*This clause is used to exclude the current settlement*/
1046 );
1047
1048 CURSOR cur_balances_vat(cp_organization_type VARCHAR2,
1049 cp_organization_id NUMBER,
1050 cp_location_id NUMBER
1051 )
1052 IS
1053 /* Modified by vumaasha for bug 7606212 */
1054 /*
1055 || This cursor is used to retrieve the sum of credit and debit balances as on
1056 || last settlement date for the given registration number,organization and location grouped at the tax type
1057 || for VAT
1058 */
1059 SELECT sum(credit_balance) credit_balance,sum(debit_balance) debit_balance,jrs.settlement_id,jrs.tax_type,jrs.location_id
1060 FROM jai_rgm_stl_balances jrs,
1061 ( SELECT MAX(jbal.settlement_id) settlement_id,tax_type
1062 FROM jai_rgm_stl_balances jbal,
1063 jai_rgm_settlements jstl,
1064 JAI_RGM_DEFINITIONS jrg
1065 WHERE jbal.settlement_id = jstl.settlement_id
1066 AND jrg.regime_id = jstl.regime_id
1067 AND jrg.regime_code = 'VAT'
1068 AND jstl.primary_registration_no = p_regn_no
1069 AND jbal.party_type = cp_organization_type
1070 AND jbal.party_id = cp_organization_id
1071 AND jbal.location_id = cp_location_id
1072 AND jbal.settlement_id <> p_settlement_id/*This clause is used to exclude the current settlement*/
1073 GROUP BY jbal.tax_type) sv
1074 WHERE
1075 jrs.settlement_id=sv.settlement_id
1076 AND jrs.tax_type= sv.tax_type
1077 GROUP BY jrs.settlement_id,jrs.tax_type,jrs.location_id ;
1078 --bug#12706846
1079 cursor c_last_settlement_dtls(cp_stl_id in number)
1080 is
1081 select * from
1082 jai_rgm_settlements
1083 where settlement_id=cp_stl_id;
1084
1085 r_last_settlement_dtls c_last_settlement_dtls%rowtype;
1086
1087 CURSOR cur_last_settlement_id_vat(cp_organization_type VARCHAR2,
1088 cp_organization_id NUMBER,
1089 cp_location_id NUMBER
1090 )
1091 IS
1092 /* added this cursor for bug 7606212 by vumaasha */
1093 SELECT MAX(jbal.settlement_id)
1094 FROM jai_rgm_stl_balances jbal,
1095 jai_rgm_settlements jstl,
1096 JAI_RGM_DEFINITIONS jrg
1097 WHERE jbal.settlement_id = jstl.settlement_id
1098 AND jrg.regime_id = jstl.regime_id
1099 AND jrg.regime_code = 'VAT'
1100 AND jstl.primary_registration_no = p_regn_no
1101 AND jbal.party_type = cp_organization_type
1102 AND jbal.party_id = cp_organization_id
1103 AND jbal.location_id = cp_location_id
1104 AND jbal.settlement_id <> p_settlement_id;
1105
1106
1107 /*6835541..end*/
1108
1109 /*start ...rchandan for bug#5694855*/
1110
1111 CURSOR c_delta_rec( cp_regime_id NUMBER,
1112 cp_regime_code VARCHAR2,
1113 cp_organization_type VARCHAR2,
1114 cp_organization_id NUMBER,
1115 cp_location_id NUMBER,
1116 cp_tax_type VARCHAR2
1117 )
1118 IS
1119 SELECT
1120 organization_id ,
1121 location_id ,
1122 tax_type ,
1123 nvl(sum(debit_amount),0) debit_amt ,
1124 nvl(sum(credit_amount),0) credit_amt
1125 FROM
1126 jai_rgm_trx_records
1127 WHERE trunc(transaction_date) <= p_balance_date --changed the date condition for bug 9445836
1128 AND settlement_id IS NULL
1129 AND source_trx_type <> 'Invoice Payment'
1130 AND organization_id = cp_organization_id
1131 AND location_id = cp_location_id
1132 AND organization_type = cp_organization_type
1133 AND tax_type = cp_tax_type
1134 AND regime_code = cp_regime_code
1135 -- AND service_type_code = nvl(p_service_type_code,service_type_code)--added nvl condition for bug#13865856
1136 and (p_service_type_code is null or service_type_code =p_service_type_code) /* Commented the above and added the condition for bug 14475128 */
1137 GROUP BY
1138 organization_id,
1139 location_id ,
1140 tax_type
1141 ORDER BY
1142 tax_type;
1143
1144 /*added for bug#8289991*/
1145 CURSOR c_last_settlement_balance (cp_regime_id IN NUMBER,
1146 cp_org_id IN NUMBER,
1147 cp_location_id IN NUMBER,
1148 cp_tax_type IN VARCHAR2)
1149 IS
1150 SELECT sum(debit_balance), sum(credit_balance)
1151 FROM JAI_RGM_STL_BALANCES
1152 WHERE party_id = cp_org_id
1153 AND location_id = cp_location_id
1154 AND tax_type = cp_tax_type
1155 AND settlement_id = (SELECT MAX(jbal.settlement_id)
1156 FROM JAI_RGM_STL_BALANCES jbal,
1157 jai_rgm_settlements jstl
1158 WHERE jbal.settlement_id = jstl.settlement_id
1159 AND jstl.regime_id = cp_regime_id
1160 AND party_id = cp_org_id
1161 AND location_id = cp_location_id
1162 AND tax_type = cp_tax_type
1163 AND NVL(jstl.reverse_charge_flag,'N') = nvl(p_reverse_charge_flag,'N'));--Added by Qiong for reverse charge settlement
1164
1165 r_delta_rec c_delta_rec%ROWTYPE;
1166 /*Bug 5879769 bduvarag end*/
1167 lv_party_type varchar2(30) ; --:= 'OU'; File.Sql.35 BY Brathod
1168 ln_settled_debit_balance number ;
1169 ln_settled_credit_balance number ;
1170 -- lv_inv_amount jai_rgm_trx_records.credit_amount%type;/* commented by ssawant for bug 5662296*/
1171 ln_invoice_amount jai_rgm_trx_records.credit_amount%type;
1172 cr_balance jai_rgm_stl_balances.credit_balance%type; /*added by ssawant for bug 5662296*/
1173 dr_balance jai_rgm_stl_balances.debit_balance%type; /*added by ssawant for bug 5662296*/
1174 ln_settled_flag NUMBER(1) := 0;/*added by ssawant for bug 5662296*/
1175 v_last_settlement_id NUMBER; /* added for bug 7606212 by vumaasha */
1176 v_credit_exceeds_debit BOOLEAN:=FALSE;/* added by vumaasha for bug 7606212 */
1177
1178 lv_temp_insert varchar2(1);--Added by Eric Ma for bug 8671217 on Dec-19-2009
1179 ln_crbal_considered number; --Added by Eric Ma for bug 8671217 on Dec-19-2009
1180
1181 /**start additions for bug#11821537 by vkaranam,budget 2011 phase2 **/
1182 --Percentage of Credit to be utilized for Service Tax Settlement
1183 Cursor get_crptg_utilize_st(cp_regime_id in number)
1184 is
1185 SELECT attribute_value
1186 FROM jai_rgm_org_regns_v
1187 WHERE regime_id = cp_regime_id
1188 AND attribute_code = 'CRPTG_UTILIZE_ST'
1189 AND attribute_type_code = 'OTHERS'
1190 AND registration_type = 'OTHERS';
1191
1192 ln_crptg_utilize_st number;
1193 ln_total_debit_balance number;
1194 ln_total_credit_balance number;
1195 ln_utilization_amount number;
1196 ln_credit_utilized number;
1197
1198 /**end additions for bug#11821537 by vkaranam,budget 2011 phase2 **/
1199
1200 BEGIN
1201 lv_party_type := 'OU'; -- File.Sql.35 by Brathod
1202 /* first get the balance as on the last settlement date for the passed org id
1203 get the records from the repository for the org id for the dates between the last settlement date and
1204 the date passed
1205 put the plus into one type of variable and minus into another type of variable
1206 finally do the arithmatic on these two variables.
1207 */
1208 OPEN c_regime_code;
1209 FETCH c_regime_code INTO lv_regime_code;
1210 CLOSE c_regime_code;
1211
1212 IF p_called_from = 'SETTLEMENT' THEN
1213 IF lv_regime_code = jai_constants.service_regime THEN
1214 lv_source_trx_type := 'Invoice Payment';--rchandan for bug#4428980
1215
1216 /**start additions for bug#11821537 by vkaranam,budget 2011 phase2 **/
1217 open get_crptg_utilize_st(p_regime_id);
1218 fetch get_crptg_utilize_st into ln_crptg_utilize_st;
1219 close get_crptg_utilize_st;
1220 /**end additions for bug#11821537 by vkaranam,budget 2011 phase2 **/
1221
1222 FOR delta_rec in
1223 (
1224 SELECT a.organization_id ,
1225 a.location_id ,/*Bug 5879769 bduvarag*/
1226 a.tax_type ,
1227 nvl(sum(a.debit_amount),0) debit_amt ,
1228 nvl(sum(a.credit_amount),0) credit_amt
1229 FROM jai_rgm_trx_records a
1230 WHERE trunc(transaction_date) <= p_balance_date --date condition changed for bug 9445836
1231 AND a.settlement_id IS NULL/*rchandan for bug#5642053*/
1232 AND a.regime_code = lv_regime_code/*5694855*/
1233 AND a.organization_type = p_org_type/*5694855*/
1234 AND a.organization_id = nvl(p_org_id,a.organization_id )
1235 AND a.location_id = p_location_id/*5694855*/
1236 AND a.source_trx_type <> 'Invoice Payment'
1237 AND ( (NVL(p_reverse_charge_flag,'N')='N' AND a.source_trx_type NOT IN ('REVERSE_PAID_ACCOUNTING','REVERSE_AVOID_ACCOUNTING','REVERSE_APPLY_ACCOUNTING','REVERSE_UNAPPLY_ACCOUNTING'))
1238 OR (NVL(p_reverse_charge_flag,'N')='Y' AND a.source_trx_type IN ('REVERSE_PAID_ACCOUNTING','REVERSE_AVOID_ACCOUNTING','REVERSE_APPLY_ACCOUNTING','REVERSE_UNAPPLY_ACCOUNTING'))
1239 )
1240 GROUP BY a.organization_id ,
1241 a.location_id ,/*5694855*/
1242 a.tax_type
1243 )/*Bug 5879769 bduvarag*/
1244 LOOP
1245 /*
1246 insert the tax types for every operating unit for the delta period
1247 ie .. between the last settlement date and the date of transfer.
1248 get the debit balance and credit balance as on the last settlement for a given operating unit and tax type
1249 and add the value in this table.
1250 -- API call to settlement process.
1251 */
1252 --ld_trx_date := jai_cmn_rgm_settlement_pkg.get_last_settlement_date(delta_rec.organization_id) + 1;/* commented by ssawant for bug 5662296*/
1253 ln_settled_debit_balance :=0;
1254 ln_settled_credit_balance :=0;
1255 ln_total_debit_balance :=0; --11821537
1256 ln_total_credit_balance :=0; --11821537
1257 ln_utilization_amount :=0;--11821537
1258 ln_credit_utilized :=0;--11821537
1259 /* Commented for bug#8289991
1260 v_credit_exceeds_debit:= FALSE;/* added by vumaasha for bug 7606212 */
1261
1262 /* Modified below procedure call by vumaasha for bug 7606212
1263 jai_cmn_rgm_settlement_pkg.GET_LAST_BALANCE_AMOUNT(
1264 pn_regime_id => p_regime_id,
1265 pn_org_id => delta_rec.organization_id ,
1266 pn_location_id => delta_rec.location_id ,
1267 pv_tax_type => delta_rec.tax_type ,
1268 pn_debit_amount => ln_settled_debit_balance ,
1269 pn_credit_amount => ln_settled_credit_balance
1270 );
1271
1272 /* OPEN cur_inv_payment(delta_rec.organization_id,NULL,delta_rec.tax_type,'Invoice Payment');
1273 FETCH cur_inv_payment INTO lv_inv_amount;
1274 CLOSE cur_inv_payment;*/
1275
1276 --added the following for bug#8289991
1277 OPEN c_last_settlement_balance( p_regime_id,
1278 delta_rec.organization_id,
1279 delta_rec.location_id,
1280 delta_rec.tax_type);
1281 FETCH c_last_settlement_balance INTO ln_settled_debit_balance,ln_settled_credit_balance;
1282 CLOSE c_last_settlement_balance;
1283 --bug#8289991,end
1284
1285 /*COMMENTED THE FOLLOWING FOR BUG#8289991,START
1286 -- start additions by nprashar- bug#6348081
1287 -- getting the invoice payment amount for every service type of the last settlement.
1288 ln_invoice_amount := 0;
1289 cr_balance := 0;
1290 dr_balance := 0;
1291
1292 OPEN cur_inv_payment; --6348081
1293 FETCH cur_inv_payment INTO ln_invoice_amount;
1294 CLOSE cur_inv_payment;
1295
1296 /* added by vumaasha for bug 7606212
1297 OPEN cur_last_settlement_id;
1298 FETCH cur_last_settlement_id INTO v_last_settlement_id;
1299 CLOSE cur_last_settlement_id;
1300
1301 FOR rec_balances IN cur_balances
1302 LOOP
1303 /* If condition modified by vumaasha for bug 7606212
1304 IF nvl(rec_balances.debit_balance,0) - nvl(rec_balances.credit_balance,0) >= 0
1305 AND rec_balances.settlement_id = v_last_settlement_id THEN
1306
1307 /*
1308 Check condition on settlement id is added to ensure that debit balances from the last made settlement are only considered.
1309 || This check is put so that only the balances of those tax types which have net balance as
1310 || debit are only considered.
1311
1312 cr_balance := nvl(cr_balance,0) + nvl(rec_balances.credit_balance,0);
1313 dr_balance := nvl(dr_balance,0) + nvl(rec_balances.debit_balance,0);
1314
1315 ELSIF ( nvl(rec_balances.credit_balance,0) - nvl(rec_balances.debit_balance,0) >0 ) AND rec_balances.tax_type = delta_rec.tax_type
1316 AND ( rec_balances.location_id = delta_rec.location_id) THEN
1317
1318
1319 ln_settled_credit_balance:= ln_settled_credit_balance-ln_settled_debit_balance;
1320 ln_settled_debit_balance := 0;
1321 v_credit_exceeds_debit:=TRUE;
1322
1323 END IF;
1324
1325 END LOOP;
1326
1327 IF ( cr_balance + nvl(ln_invoice_amount,0) - dr_balance < 1 )
1328 AND NOT(v_credit_exceeds_debit) THEN
1329 /* If condition modified by vumaasha for bug 7606212*/
1330 /*
1331 ||If the balances at the last settlement balance are settled then flag is one, else flag is zero
1332 ||if flag is one then balances at last settlement date are not carried forward. Otherwise they
1333 ||are carried forward to the current settlement
1334
1335 ln_settled_flag := 1;
1336 ELSE
1337 ln_settled_flag := 0;
1338 END IF;
1339
1340 -- End additions by nprashar- bug#6348081
1341
1342 IF ln_settled_flag = 1 THEN /*added by ssawant for bug 5662296
1343 ln_settled_debit_balance :=0;
1344 ln_settled_credit_balance :=0;
1345 END IF;
1346 BUG#8289991,END */
1347
1348 --added the following for bug#8289991
1349 IF nvl(ln_settled_debit_balance,0) >= nvl(ln_settled_credit_balance,0) THEN
1350 ln_settled_debit_balance := 0;
1351 ln_settled_credit_balance := 0;
1352 ELSE
1353 ln_settled_credit_balance := ln_settled_credit_balance - ln_settled_debit_balance;
1354 ln_settled_debit_balance := 0;
1355 END IF;
1356 --bug#8289991, end
1357
1358 /**start additions for bug#11821537 by vkaranam,budget 2011 phase2,calculation logic for jai_rgm_stl_balances.credit_utilized
1359 column based on the ST credit utilization ptg provided on the regime registration setup **/
1360 ln_total_debit_balance := nvl(ln_settled_debit_balance,0) + delta_rec.debit_amt ;
1361 ln_total_credit_balance := nvl(ln_settled_credit_balance,0) + delta_rec.credit_amt ;
1362 -- ln_utilization_amount := round(ln_total_debit_balance*nvl(ln_crptg_utilize_st,0)/100, jai_constants.service_rgm_rnd_factor);
1363 ln_utilization_amount := ln_total_debit_balance*nvl(ln_crptg_utilize_st,0)/100; --removed the round function for 11821537 (qa bug# 11923714)
1364
1365
1366 if ln_crptg_utilize_st is not null
1367 then
1368 if ln_utilization_amount >0
1369 then
1370 If ln_utilization_amount <= ln_total_credit_balance
1371 then
1372 ln_credit_utilized := ln_utilization_amount;
1373 else
1374 ln_credit_utilized := ln_total_credit_balance;
1375 end if;
1376 else
1377 ln_credit_utilized := 0;
1378 end if;
1379 else
1380 ln_credit_utilized :=ln_total_credit_balance;
1381 end if;
1382 /**end additions for bug#11821537 by vkaranam,budget 2011 phase2 **/
1383
1384
1385 insert_records_into_temp(
1386 p_request_id => p_request_id ,
1387 p_regime_id => p_regime_id ,
1388 p_party_type => p_org_type ,
1389 p_party_id => delta_rec.organization_id ,
1390 p_location_id => delta_rec.location_id ,
1391 p_bal_date => p_balance_date ,
1392 p_tax_type => delta_rec.tax_type ,
1393 p_debit_amt => nvl(ln_settled_debit_balance,0) + delta_rec.debit_amt , --4557267
1394 p_credit_amt => nvl(ln_settled_credit_balance,0) + delta_rec.credit_amt, --4557267
1395 /* Bug 4568078. Added by Lakshmi Gopalsami */
1396 p_pla_balance => NULL,
1397 p_service_type_code => NULL /* modified by vumaasha for bug 7606212*/
1398 ,p_credit_utilized =>ln_credit_utilized /*added p_credit_utilized for er#11821537*/
1399 ,p_reverse_charge_flag => p_reverse_charge_flag --Added by Qiong for reverse charge settlement
1400 );
1401 END LOOP;
1402 punch_settlement_id(p_regime_id => p_regime_id,
1403 p_settlement_id => p_settlement_id ,
1404 p_regn_id => p_regn_id ,
1405 p_balance_date => p_balance_date,
1406 p_location_id => p_location_id, /*Bug 5879769 bduvarag*/
1407 p_org_id => p_org_id /*added by ssawant for bug 5662296*/
1408 ,p_reverse_charge_flag => p_reverse_charge_flag --Added by Qiong for reverse charge settlement
1409 );
1410 ELSIF lv_regime_code = jai_constants.vat_regime THEN /* 4245365*/
1411 /*Even though VAT is for IO , balances are calculated similar to an OU. i.e from
1412 jai_rgm_trx_records. hence the implementation is done in this procedure only*/
1413 lv_source_trx_type := 'Invoice Payment';--rchandan for bug#4428980
1414 FOR delta_rec in
1415 (
1416 SELECT
1417 b.regime_id ,
1418 a.organization_id ,
1419 a.location_id ,
1420 a.tax_type ,
1421 a.organization_type , /*6835541*/
1422 nvl(sum(a.debit_amount),0) debit_amt ,
1423 nvl(sum(a.credit_amount),0) credit_amt
1424 FROM
1425 jai_rgm_trx_records a, JAI_RGM_ORG_REGNS_V b
1426 WHERE trunc(transaction_date) <= p_balance_date --date condition changed for bug 9445836
1427 AND a.settlement_id IS NULL --added by csahoo for bug#6235971
1428 AND b.regime_id = p_regime_id/*5694855 bduvarag*/
1429 AND a.regime_code = lv_regime_code/*5694855 bduvarag*/
1430 AND a.organization_id = b.organization_id
1431 AND a.location_id = b.location_id
1432 AND a.organization_type = b.organization_type
1433 AND b.registration_id = p_regn_id
1434 AND a.organization_id = nvl(p_org_id,a.organization_id )
1435 AND a.organization_type = nvl(p_org_type,a.organization_type)
1436 AND b.attribute_value = p_regn_no
1437 AND a.location_id = nvl(p_location_id,a.location_id)/*rchandan for bug#6835541. Added nvl*/
1438 AND a.source_trx_type <> lv_source_trx_type--rchandan for bug#4428980
1439 GROUP BY a.organization_id , a.tax_type,a.location_id,b.regime_id,a.organization_type /*6835541. added organization_type*/
1440 )
1441 LOOP
1442 /*
1443 insert the tax types for every IO for the delta period
1444 ie .. between the last settlement date and the date of transfer.
1445 get the debit balance and credit balance as on the last settlement for a given IO , Location and tax type
1446 and add the value in this table.
1447 -- API call to settlement process.
1448 */
1449 --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*/
1450 ln_settled_debit_balance :=0;
1451 ln_settled_credit_balance :=0;
1452 v_credit_exceeds_debit:= FALSE;/* added by vumaasha for bug 7606212*/
1453
1454 jai_cmn_rgm_settlement_pkg.GET_LAST_BALANCE_AMOUNT(
1455 pn_regime_id => delta_rec.regime_id,
1456 pn_org_id => delta_rec.organization_id ,
1457 pn_location_id => delta_rec.location_id ,
1458 pv_tax_type => delta_rec.tax_type ,
1459 pn_debit_amount => ln_settled_debit_balance ,
1460 pn_credit_amount => ln_settled_credit_balance
1461 );
1462
1463 /*6835541..start*/
1464 ln_invoice_amount := 0;
1465 cr_balance := 0;
1466 dr_balance := 0;
1467
1468 OPEN cur_inv_payment_vat(cp_organization_type => delta_rec.organization_type,
1469 cp_organization_id => delta_rec.organization_id,
1470 cp_location_id => delta_rec.location_id);
1471 FETCH cur_inv_payment_vat INTO ln_invoice_amount;
1472 CLOSE cur_inv_payment_vat;
1473
1474 /* Added for bug 7606212 by vumaasha */
1475 OPEN cur_last_settlement_id_vat(cp_organization_type => delta_rec.organization_type,
1476 cp_organization_id => delta_rec.organization_id,
1477 cp_location_id => delta_rec.location_id
1478 );
1479 FETCH cur_last_settlement_id_vat INTO v_last_settlement_id;
1480 CLOSE cur_last_settlement_id_vat;
1481
1482 FOR rec_balances IN cur_balances_vat(cp_organization_type => delta_rec.organization_type,
1483 cp_organization_id => delta_rec.organization_id,
1484 cp_location_id => delta_rec.location_id
1485 )
1486 LOOP
1487 IF nvl(rec_balances.debit_balance,0) - nvl(rec_balances.credit_balance,0) >= 0
1488 AND rec_balances.settlement_id = v_last_settlement_id THEN
1489 /* If condition modified by vumaasha for bug 7606212
1490 Check condition on settlement id is added to ensure that debit balances from the last made settlement are only considered.
1491 */
1492 /*
1493 || This check is put so that only the balances of those tax types which have net balance as
1494 || debit are only considered.
1495 */
1496 cr_balance := nvl(cr_balance,0) + nvl(rec_balances.credit_balance,0);
1497 dr_balance := nvl(dr_balance,0) + nvl(rec_balances.debit_balance,0);
1498
1499 ELSIF ( nvl(rec_balances.credit_balance,0) - nvl(rec_balances.debit_balance,0) >0 ) AND rec_balances.tax_type = delta_rec.tax_type
1500 AND ( rec_balances.location_id = delta_rec.location_id) THEN
1501 /* elsif condition added by vumaasha for bug 7606212*/
1502 ln_settled_credit_balance:= ln_settled_credit_balance-ln_settled_debit_balance;
1503 ln_settled_debit_balance := 0;
1504 v_credit_exceeds_debit:=TRUE;
1505
1506 END IF;
1507 END LOOP;
1508
1509
1510 /*commented by vkaranam for bug#12706846
1511 IF ( cr_balance + nvl(ln_invoice_amount,0) - dr_balance < 1 )
1512 AND NOT(v_credit_exceeds_debit) THEN
1513 /*
1514 ||If the balances at the last settlement balance are settled then flag is one, else flag is zero
1515 ||if flag is one then balances at last settlement date are not carried forward. Otherwise they
1516 ||are carried forward to the current settlement
1517
1518 ln_settled_flag := 1;
1519 ELSE
1520 ln_settled_flag := 0;
1521 END IF;
1522 12706846 */
1523
1524 --start additions by vkaranam for bug#12706846
1525 open c_last_settlement_dtls(v_last_settlement_id);
1526 fetch c_last_settlement_dtls into r_last_settlement_dtls;
1527 close c_last_settlement_dtls;
1528
1529 if r_last_settlement_dtls.payment_amount>0 then
1530
1531 ln_settled_flag := 1;
1532 ELSE
1533 ln_settled_flag := 0;
1534 END IF;
1535 --end additions by vkaranam for bug#12706846
1536 /*6835541..end*/
1537 /* OPEN cur_inv_payment(delta_rec.organization_id,delta_rec.location_id,delta_rec.tax_type,'Invoice Payment');
1538 FETCH cur_inv_payment INTO lv_inv_amount;
1539 CLOSE cur_inv_payment;*/
1540 IF ln_settled_flag = 1 THEN /*added by ssawant for bug 5662296*/
1541 ln_settled_debit_balance :=0;
1542 ln_settled_credit_balance :=0;
1543 END IF;
1544 insert_records_into_temp(
1545 p_request_id => p_request_id ,
1546 p_regime_id => p_regime_id ,
1547 p_party_type => delta_rec.organization_type,/*6835541*/
1548 p_party_id => delta_rec.organization_id ,
1549 p_location_id => delta_rec.location_id ,
1550 p_bal_date => p_balance_date ,
1551 p_tax_type => delta_rec.tax_type ,
1552 p_debit_amt => nvl(ln_settled_debit_balance,0) + delta_rec.debit_amt , --4557267
1553 p_credit_amt => nvl(ln_settled_credit_balance,0) + delta_rec.credit_amt, --4557267
1554 /* Bug 4568078. Added by Lakshmi Gopalsami */
1555 p_pla_balance => NULL
1556 );
1557
1558 ln_crbal_considered := nvl( ln_crbal_considered,0)+ nvl(ln_settled_credit_balance,0); --Added by Eric Ma for bug 8671217 on Dec-19-2009
1559 -- lv_temp_insert:='Y'; --bug# 8671217 12-aug-2009
1560 END LOOP;
1561
1562
1563 --Added by Eric Ma for bug 8333082 on Dec-19-2009,Begin
1564 --------------------------------------------------------------------------------------
1565 IF ( p_org_id IS NULL AND p_location_id IS NULL )
1566 --and nvl(lv_temp_insert,'N')<>'Y' THEN --added by eric for bug#8671217 on Dec-19-2009
1567 THEN
1568 /* If condition added for the bug 8333082 */
1569
1570 populate_all_orgs_vat( p_regn_no => p_regn_no,
1571 p_regn_id => p_regn_id ,
1572 p_regime_id => p_regime_id ,
1573 p_request_id => p_request_id,
1574 p_balance_date => p_balance_date,
1575 p_org_type => p_org_type ,
1576 p_settlement_id=>p_settlement_id , --added by eric for bug#8671217 on Dec-19-2009
1577 p_cramt_considered=> ln_crbal_considered --added by eric for bug#8671217 on Dec-19-2009
1578 );
1579
1580 END IF;
1581 punch_settlement_id(p_regime_id => p_regime_id,
1582 p_settlement_id => p_settlement_id ,
1583 p_regn_id => p_regn_id ,
1584 p_balance_date => p_balance_date,
1585 p_org_id => p_org_id ,/*added by ssawant for bug 5662296*/
1586 p_location_id => p_location_id, /*added by ssawant for bug 5662296*/
1587 p_regn_no => p_regn_no /*6835541*/
1588 );
1589 END IF;
1590 --------------------------------------------------------------------------------------
1591 --Added by Eric Ma for bug 8333082 on Dec-19-2009,End
1592
1593
1594 /*Bug 5879769 bduvarag start*/
1595 ELSIF p_called_from = 'DISTRIBUTE_IO' THEN
1596
1597 FOR tax_types_rec in
1598 (
1599 SELECT regime_id,
1600 attribute_code tax_type
1601 FROM jai_rgm_org_regns_v
1602 WHERE organization_id = p_org_id
1603 AND location_id = p_location_id
1604 AND organization_type = p_org_type
1605 AND registration_type = jai_constants.regn_type_tax_types
1606 AND regime_code = lv_regime_code
1607 )
1608 LOOP
1609
1610 r_delta_rec := NULL;
1611 OPEN c_delta_rec(tax_types_rec.regime_id,
1612 lv_regime_code ,
1613 p_org_type ,
1614 p_org_id ,
1615 p_location_id ,
1616 tax_types_rec.tax_type
1617 );
1618 FETCH c_delta_rec INTO r_delta_rec;
1619 CLOSE c_delta_rec;
1620
1621 ln_settled_debit_balance :=0;
1622 ln_settled_credit_balance :=0;
1623 jai_cmn_rgm_settlement_pkg.GET_LAST_BALANCE_AMOUNT(
1624 pn_regime_id => tax_types_rec.regime_id ,
1625 pn_org_id => p_org_id ,
1626 pn_location_id => p_location_id ,
1627 pv_tax_type => tax_types_rec.tax_type ,
1628 pn_debit_amount => ln_settled_debit_balance ,
1629 pn_credit_amount => ln_settled_credit_balance ,
1630 pv_service_type_code => p_service_type_code
1631 );
1632 ln_invoice_amount := 0;
1633
1634 OPEN cur_inv_payment_dist_io(p_org_type,p_org_id,tax_types_rec.tax_type,p_location_id,p_service_type_code );
1635 FETCH cur_inv_payment_dist_io INTO ln_invoice_amount;
1636 CLOSE cur_inv_payment_dist_io;
1637
1638 ln_settled_credit_balance := nvl(ln_settled_credit_balance,0) + nvl(ln_invoice_amount,0);/*rchandan for bug#5642053*/
1639 insert_records_into_temp(
1640 p_request_id => p_request_id ,
1641 p_regime_id => p_regime_id ,
1642 p_party_type => p_org_type ,
1643 p_party_id => p_org_id ,
1644 p_location_id => p_location_id ,
1645 p_bal_date => p_balance_date ,
1646 p_tax_type => tax_types_rec.tax_type ,
1647 -- modified for bug#8702609, start
1648 p_debit_amt => nvl(r_delta_rec.debit_amt,0) ,
1649 p_credit_amt => nvl(r_delta_rec.credit_amt,0) ,
1650 -- bug#8702609, end
1651 p_pla_balance => NULL ,
1652 p_service_type_code => p_service_type_code
1653 );
1654 END LOOP;
1655 ELSIF p_called_from = 'DISTRIBUTE_OU' THEN
1656 /*Bug 5879769 bduvarag end*/
1657 lv_reg_type := 'TAX_TYPES';
1658 FOR delta_rec in
1659 (
1660 SELECT
1661 a.organization_id ,
1662 a.tax_type ,
1663 nvl(sum(debit_amount),0) debit_amt ,
1664 nvl(sum(credit_amount),0) credit_amt
1665 FROM
1666 /*Bug 5879769 bduvarag start*/
1667 jai_rgm_trx_records a
1668 WHERE trunc(transaction_date) <= p_balance_date --date condition removed for bug 9445836
1669 AND a.settlement_id IS NULL
1670 AND a.source_trx_type <> 'Invoice Payment'
1671 AND a.organization_id = p_org_id
1672 AND a.organization_type = p_org_type/*5694855*/
1673 GROUP BY
1674 a.organization_id,
1675 a.tax_type
1676 ORDER BY
1677 a.tax_type desc
1678 )/*Bug 5879769 bduvarag end*/
1679 LOOP
1680 ln_settled_debit_balance :=0;
1681 ln_settled_credit_balance :=0;
1682 jai_cmn_rgm_settlement_pkg.GET_LAST_BALANCE_AMOUNT(
1683 pn_org_id => delta_rec.organization_id ,
1684 pv_tax_type => delta_rec.tax_type ,
1685 pn_debit_amount => ln_settled_debit_balance ,
1686 pn_credit_amount => ln_settled_credit_balance
1687 );
1688 -- start block added by ssawant for bug 5662296
1689 ln_invoice_amount := 0;
1690
1691 OPEN cur_inv_payment_dist('OU',delta_rec.organization_id,delta_rec.tax_type);
1692 FETCH cur_inv_payment_dist INTO ln_invoice_amount;
1693 CLOSE cur_inv_payment_dist;
1694
1695 ln_settled_credit_balance := nvl(ln_settled_credit_balance,0) + nvl(ln_invoice_amount,0);
1696 -- end block added by ssawant for bug 5662296
1697
1698 insert_records_into_temp(
1699 p_request_id => p_request_id ,
1700 p_regime_id => p_regime_id ,
1701 p_party_type => p_org_type ,/*Bug 5879769 bduvarag*/
1702 p_party_id => delta_rec.organization_id ,
1703 p_location_id => null ,
1704 p_bal_date => p_balance_date ,
1705 p_tax_type => delta_rec.tax_type ,
1706 -- modified for bug#8702609, start
1707 p_debit_amt => nvl(delta_rec.debit_amt,0) ,
1708 p_credit_amt => nvl(delta_rec.credit_amt,0) ,
1709 -- bug#8702609, end
1710 /* Bug 4568078. Added by Lakshmi Gopalsami */
1711 p_pla_balance => NULL
1712 );
1713 END LOOP;
1714 END IF;
1715 /* Added by Ramananda for bug#4407165 */
1716 EXCEPTION
1717 WHEN OTHERS THEN
1718 FND_MESSAGE.SET_NAME('JA','JAI_EXCEPTION_OCCURED');
1719 FND_MESSAGE.SET_TOKEN('JAI_PROCESS_MSG', lv_object_name ||'. Err:'||sqlerrm );
1720 app_exception.raise_exception;
1721 END calculate_balances_for_ou;
1722
1723
1724
1725
1726 --------------------------------------------------------------------------------------------------
1727 -- Added by sacsethi for bug 5631784 on 30-01-2007
1728 -- FOR TCS
1729 -- START -- 5631784
1730 --------------------------------------------------------------------------------------------------
1731 PROCEDURE CALCULATE_RGM_BALANCES( P_REGIME_ID NUMBER ,
1732 P_BALANCE_DATE DATE ,
1733 P_REQUEST_ID NUMBER ,
1734 P_ORG_ID NUMBER ,
1735 P_REGN_ID VARCHAR2 ,
1736 P_TAN_NO VARCHAR2 ,
1737 P_ITEM_CLASSIFICATION VARCHAR2 ,
1738 P_SETTLEMENT_ID NUMBER ,
1739 P_CALLED_FROM VARCHAR2)
1740 IS
1741
1742 CURSOR C_REGIME_CODE IS
1743 SELECT REGIME_CODE
1744 FROM JAI_RGM_DEFINITIONS
1745 WHERE REGIME_ID = P_REGIME_ID;
1746 -- LD_TRX_DATE DATE; /*commented by ssawant for bug 5662296*/
1747 LV_REGIME_CODE JAI_RGM_DEFINITIONS.REGIME_CODE%TYPE;
1748
1749 /*Start addtions by mmurtuza for bug 12641455*/
1750 CURSOR cur_balances_tcs(cp_organization_type VARCHAR2, cp_organization_id NUMBER, cp_location_id NUMBER) IS
1751 SELECT SUM(credit_balance) credit_balance,
1752 SUM(debit_balance) debit_balance,
1753 jrs.settlement_id,
1754 jrs.tax_type,
1755 jrs.location_id
1756 FROM jai_rgm_stl_balances jrs,
1757 (SELECT MAX(jbal.settlement_id) settlement_id,
1758 tax_type
1759 FROM jai_rgm_stl_balances jbal,
1760 jai_rgm_settlements jstl,
1761 jai_rgm_definitions jrg
1762 WHERE jbal.settlement_id = jstl.settlement_id
1763 AND jrg.regime_id = jstl.regime_id
1764 AND jrg.regime_code = 'TCS'
1765 AND jstl.primary_registration_no = P_TAN_NO
1766 AND jbal.party_type = cp_organization_type
1767 AND jbal.party_id = cp_organization_id
1768 AND jbal.location_id = cp_location_id
1769 AND jbal.settlement_id <> p_settlement_id
1770 GROUP BY jbal.tax_type)
1771 sv
1772 WHERE jrs.settlement_id = sv.settlement_id
1773 AND jrs.tax_type = sv.tax_type
1774 GROUP BY jrs.settlement_id,
1775 jrs.tax_type,
1776 jrs.location_id;
1777
1778 CURSOR cur_last_settlement_id_tcs(cp_organization_type VARCHAR2, cp_organization_id NUMBER, cp_location_id NUMBER) IS
1779 SELECT MAX(jbal.settlement_id)
1780 FROM jai_rgm_stl_balances jbal,
1781 jai_rgm_settlements jstl,
1782 jai_rgm_definitions jrg
1783 WHERE jbal.settlement_id = jstl.settlement_id
1784 AND jrg.regime_id = jstl.regime_id
1785 AND jrg.regime_code = 'TCS'
1786 AND jstl.primary_registration_no = P_TAN_NO
1787 AND jbal.party_type = cp_organization_type
1788 AND jbal.party_id = cp_organization_id
1789 AND jbal.location_id = cp_location_id
1790 AND jbal.settlement_id <> p_settlement_id;
1791
1792 CURSOR cur_inv_payment_tcs(cp_organization_type VARCHAR2, cp_organization_id NUMBER, cp_location_id NUMBER) IS
1793 SELECT SUM(total_tax_amt * decode(sign(total_tax_amt), -1, -1, 0)) -- Negative amount converted to positive and taken as credit amount
1794 FROM jai_rgm_refs_all
1795 WHERE org_tan_no = P_TAN_NO
1796 --AND source_document_type = 'Invoice Payment' --mmurtuza need to discuss
1797 AND settlement_id =
1798 (SELECT MAX(jbal.settlement_id)
1799 FROM jai_rgm_stl_balances jbal,
1800 jai_rgm_settlements jstl,
1801 jai_rgm_definitions jrg
1802 WHERE jbal.settlement_id = jstl.settlement_id
1803 AND jrg.regime_id = jstl.regime_id
1804 AND jrg.regime_code = 'TCS'
1805 AND jstl.primary_registration_no = P_TAN_NO
1806 AND jbal.party_type = cp_organization_type
1807 AND jbal.party_id = cp_organization_id
1808 AND jbal.location_id = cp_location_id
1809 AND jbal.settlement_id <> nvl(p_settlement_id, -999))
1810 ;
1811
1812 CURSOR c_last_settlement_dtls(cp_stl_id IN NUMBER) IS
1813 SELECT *
1814 FROM jai_rgm_settlements
1815 WHERE settlement_id = cp_stl_id;
1816
1817 CURSOR c_get_organization_type(cp_organization_id NUMBER) IS
1818 SELECT DISTINCT organization_type
1819 FROM jai_rgm_org_regns_v
1820 WHERE regime_code = 'TCS'
1821 AND organization_id = cp_organization_id;
1822
1823 r_last_settlement_dtls c_last_settlement_dtls%rowtype;
1824 ln_settled_flag number(1) := 0;
1825 ln_settled_credit_balance number:= 0;
1826 ln_invoice_amount jai_rgm_trx_records.credit_amount%type;
1827 cr_balance jai_rgm_stl_balances.credit_balance%type;
1828 dr_balance jai_rgm_stl_balances.debit_balance%type:= 0;
1829 ln_settled_debit_balance number:= 0;
1830 v_credit_exceeds_debit BOOLEAN :=FALSE;
1831 ln_organization_type jai_rgm_org_regns_v.organization_type%type;
1832 v_last_settlement_id NUMBER;
1833 ln_crbal_considered number;
1834
1835 /*End addtions by mmurtuza for bug 12641455*/
1836
1837
1838 BEGIN
1839
1840 OPEN C_REGIME_CODE;
1841 FETCH C_REGIME_CODE INTO LV_REGIME_CODE;
1842 CLOSE C_REGIME_CODE;
1843
1844 IF LV_REGIME_CODE = JAI_CONSTANTS.TCS_REGIME THEN
1845 FOR DELTA_REC IN
1846 (
1847 SELECT SUM(DECODE(SIGN(JRT.TAX_AMT),-1,-1 * JRT.TAX_AMT,1,0)) CREDIT_AMOUNT,
1848 SUM(DECODE(SIGN(JRT.TAX_AMT),1,JRT.TAX_AMT,-1,0)) DEBIT_AMOUNT,
1849 DECODE(JRT.TAX_TYPE,'TCS_SURCHARGE_CESS','TCS_CESS',JRT.TAX_TYPE) TAX_TYPE,
1850 JRR.ORGANIZATION_ID,
1851 JRR.LOCATION_ID, --added by mmurtuza for bug 12641455
1852 JRR.REGIME_ID
1853 FROM JAI_RGM_REFS_ALL JRR,
1854 JAI_RGM_TAXES JRT,
1855 --JAI_RGM_ORG_REGNS_V JOR --commented for bug# 9005474
1856 JAI_RGM_REGISTRATIONS JOR --added for bug#9005474
1857 WHERE JRR.TRX_REF_ID = JRT.TRX_REF_ID
1858 -- AND JRR.ORGANIZATION_ID = JOR.ORGANIZATION_ID commented for bug# 9005474
1859 AND JOR.REGIME_ID = JRR.REGIME_ID
1860 AND JOR.REGISTRATION_TYPE = 'TAX_TYPES'
1861 AND JRT.TAX_TYPE = JOR.ATTRIBUTE_CODE
1862 AND JRR.REGIME_ID = P_REGIME_ID
1863 AND JRR.ORG_TAN_NO = P_TAN_NO
1864 AND JRR.ORGANIZATION_ID=NVL(P_ORG_ID,JRR.ORGANIZATION_ID)--added for bug 9005474
1865 AND JRR.ITEM_CLASSIFICATION = P_ITEM_CLASSIFICATION
1866 AND JRR.SETTLEMENT_ID IS NULL
1867 AND TRUNC(JRR.SOURCE_DOCUMENT_DATE) <= P_BALANCE_DATE
1868 GROUP BY DECODE(JRT.TAX_TYPE,'TCS_SURCHARGE_CESS','TCS_CESS',JRT.TAX_TYPE),
1869 JRR.ORGANIZATION_ID,JRR.REGIME_ID, JRR.LOCATION_ID --added location id by mmurtuza for bug 12641455
1870 ) LOOP
1871
1872 /*Start addtions by mmurtuza for bug 12641455*/
1873
1874 ln_settled_debit_balance := 0;
1875 ln_settled_credit_balance := 0;
1876 v_credit_exceeds_debit := FALSE;
1877
1878 jai_cmn_rgm_settlement_pkg.get_last_balance_amount(pn_regime_id => delta_rec.regime_id, pn_org_id => delta_rec.organization_id,
1879 pn_location_id => delta_rec.location_id, pv_tax_type => delta_rec.tax_type, pn_debit_amount => ln_settled_debit_balance,
1880 pn_credit_amount => ln_settled_credit_balance);
1881
1882 ln_invoice_amount := 0;
1883 cr_balance := 0;
1884 dr_balance := 0;
1885
1886 OPEN c_get_organization_type(delta_rec.organization_id);
1887 FETCH c_get_organization_type
1888 INTO ln_organization_type;
1889 CLOSE c_get_organization_type;
1890
1891 OPEN cur_inv_payment_tcs(cp_organization_type => ln_organization_type, cp_organization_id => delta_rec.organization_id, cp_location_id => delta_rec.location_id);
1892 FETCH cur_inv_payment_tcs
1893 INTO ln_invoice_amount;
1894 CLOSE cur_inv_payment_tcs;
1895
1896 OPEN cur_last_settlement_id_tcs(cp_organization_type => ln_organization_type, cp_organization_id => delta_rec.organization_id, cp_location_id => delta_rec.location_id);
1897 FETCH cur_last_settlement_id_tcs
1898 INTO v_last_settlement_id;
1899 CLOSE cur_last_settlement_id_tcs;
1900
1901 FOR rec_balances IN cur_balances_tcs(cp_organization_type => ln_organization_type, cp_organization_id => delta_rec.organization_id, cp_location_id => delta_rec.location_id)
1902 LOOP
1903 IF nvl(rec_balances.debit_balance, 0) -nvl(rec_balances.credit_balance, 0) >= 0
1904 AND rec_balances.settlement_id = v_last_settlement_id THEN
1905 cr_balance := nvl(cr_balance, 0) + nvl(rec_balances.credit_balance, 0);
1906 dr_balance := nvl(dr_balance, 0) + nvl(rec_balances.debit_balance, 0);
1907
1908 ELSIF(nvl(rec_balances.credit_balance, 0) -nvl(rec_balances.debit_balance, 0) > 0)
1909 AND rec_balances.tax_type = delta_rec.tax_type
1910 AND(rec_balances.location_id = delta_rec.location_id) THEN
1911
1912 ln_settled_credit_balance := ln_settled_credit_balance -ln_settled_debit_balance;
1913 ln_settled_debit_balance := 0;
1914 v_credit_exceeds_debit := TRUE;
1915 END IF;
1916 END LOOP;
1917
1918 OPEN c_last_settlement_dtls(v_last_settlement_id);
1919 FETCH c_last_settlement_dtls
1920 INTO r_last_settlement_dtls;
1921 CLOSE c_last_settlement_dtls;
1922
1923 IF r_last_settlement_dtls.payment_amount > 0 THEN
1924 ln_settled_flag := 1;
1925 ELSE
1926 ln_settled_flag := 0;
1927 END IF;
1928
1929 IF ln_settled_flag = 1 THEN
1930 /*added by ssawant for bug 5662296*/ ln_settled_debit_balance := 0;
1931 ln_settled_credit_balance := 0;
1932 END IF;
1933
1934 /*End addtions by mmurtuza for bug 12641455*/
1935
1936
1937 INSERT_RECORDS_INTO_TEMP(
1938 P_REQUEST_ID => P_REQUEST_ID ,
1939 P_REGIME_ID => P_REGIME_ID ,
1940 P_PARTY_TYPE => 'IO' ,
1941 P_PARTY_ID => DELTA_REC.ORGANIZATION_ID ,
1942 P_LOCATION_ID => DELTA_REC.LOCATION_ID , --initially null was inserted. Added JRR.LOCATION_ID by mmurtuza for bug 12641455
1943 P_BAL_DATE => P_BALANCE_DATE ,
1944 P_TAX_TYPE => DELTA_REC.TAX_TYPE ,
1945 P_DEBIT_AMT => nvl(ln_settled_debit_balance,0) + delta_rec.DEBIT_AMOUNT , --changed by mmurtuza for bug 12641455
1946 P_CREDIT_AMT => nvl(ln_settled_credit_balance,0) + delta_rec.CREDIT_AMOUNT , --changed by mmurtuza for bug 12641455
1947 P_PLA_BALANCE => NULL
1948 );
1949 END LOOP;
1950
1951 /*Calling populate_all_orgs_vat by mmurtuza for bug 12641455*/
1952 populate_all_orgs_vat(p_regn_no => P_TAN_NO, p_regn_id => p_regn_id, p_regime_id => p_regime_id, p_request_id => p_request_id,
1953 p_balance_date => p_balance_date, p_org_type => 'IO', p_settlement_id => p_settlement_id,
1954 p_cramt_considered => ln_crbal_considered --added by eric for bug#8671217 on Dec-19-2009
1955 );
1956
1957 PUNCH_SETTLEMENT_ID( P_REGIME_ID => P_REGIME_ID,
1958 P_SETTLEMENT_ID => P_SETTLEMENT_ID ,
1959 P_REGN_ID => P_REGN_ID ,
1960 P_BALANCE_DATE => P_BALANCE_DATE ,
1961 P_TAN_NO => P_TAN_NO,
1962 P_ITEM_CLASSIFICATION => P_ITEM_CLASSIFICATION
1963 );
1964 END IF;
1965 -- END 5631784
1966 END CALCULATE_RGM_BALANCES;
1967 --------------------------------------------------------------------------------------
1968
1969
1970
1971 PROCEDURE get_balances(p_request_id NUMBER ,
1972 p_balance_date DATE ,
1973 p_Called_from VARCHAR2 ,
1974 p_regime_id NUMBER Default NULL ,
1975 p_regn_no VARCHAR2 default NULL ,
1976 p_regn_id NUMBER default NULL ,
1977 p_org_id NUMBER default NULL ,
1978 p_org_type VARCHAR2 default NULL ,
1979 p_settlement_id NUMBER default NULL ,
1980 P_ITEM_CLASSIFICATION VARCHAR2 DEFAULT NULL,-- Added by sacsethi for bug 5631784 on 30-01-2007
1981 p_transfer_type VARCHAR2 default NULL ,/*Bug 5879769 bduvarag*/
1982 p_service_type_code VARCHAR2 default NULL, /*Bug 5879769 bduvarag*/
1983 p_location_id NUMBER DEFAULT NULL /*added by ssawant for bug 5662296 */
1984 ,p_reverse_charge_flag VARCHAR2 DEFAULT NULL --Added by Qiong for reverse charge settlement
1985 )
1986 IS
1987 PRAGMA AUTONOMOUS_TRANSACTION;
1988 -- Added by sacsethi for bug 5631784 on 30-01-2007
1989 -- for TCS
1990
1991 CURSOR CUR_REGIME_CODE(CP_REGIME_ID NUMBER)
1992 IS SELECT REGIME_CODE
1993 FROM JAI_RGM_DEFINITIONS
1994 WHERE REGIME_ID = CP_REGIME_ID;
1995 LV_REGIME_CODE JAI_RGM_DEFINITIONS.REGIME_CODE%TYPE;
1996
1997
1998
1999 BEGIN
2000
2001 IF p_called_from = 'SETTLEMENT' THEN
2002 -- Added by sacsethi for bug 5631784 on 30-01-2007
2003 --START 5631784
2004 -- CURSOR AND CALCULATE RGM BALANCES IS ADDED TO PROVIDE TCS FUNCTIONALITY
2005 OPEN CUR_REGIME_CODE( P_REGIME_ID);
2006 FETCH CUR_REGIME_CODE INTO LV_REGIME_CODE;
2007 CLOSE CUR_REGIME_CODE;
2008 IF LV_REGIME_CODE = JAI_CONSTANTS.TCS_REGIME THEN
2009 calculate_rgm_balances( p_regime_id => p_regime_id ,
2010 p_balance_date => p_balance_date ,
2011 p_request_id => p_request_id ,
2012 p_org_id => p_org_id ,
2013 p_regn_id => p_regn_id ,
2014 p_tan_no => p_regn_no ,
2015 p_item_classification => p_item_classification,
2016 p_settlement_id => p_settlement_id ,
2017 p_called_from => p_called_from
2018 ) ;
2019 --END 5631784
2020 ELSE
2021 CALCULATE_BALANCES_FOR_OU(P_REGIME_ID => P_REGIME_ID ,
2022 P_BALANCE_DATE => P_BALANCE_DATE ,
2023 P_REQUEST_ID => P_REQUEST_ID ,
2024 P_ORG_ID => P_ORG_ID ,
2025 P_ORG_TYPE => P_ORG_TYPE ,
2026 P_REGN_ID => P_REGN_ID ,
2027 P_REGN_NO => P_REGN_NO ,
2028 P_SETTLEMENT_ID => P_SETTLEMENT_ID,
2029 P_CALLED_FROM => P_CALLED_FROM,
2030 p_location_id => p_location_id /*rchandan for bug#5662296*/
2031 ,p_reverse_charge_flag => p_reverse_charge_flag --Added by Qiong for reverse charge settlement
2032 );
2033 END IF ;
2034 END IF;
2035 IF p_called_from = 'DISTRIBUTION' THEN
2036 /*Bug 5879769 bduvarag start*/
2037 IF p_transfer_type NOT IN ('S-S') THEN -- added, Harshita for Bug 5694855
2038
2039 calculate_balances_for_io(p_regime_id , p_balance_date , p_request_id, p_service_type_code);
2040 END IF ;
2041 IF p_transfer_type = 'BT' THEN -- added, Harshita for Bug 5694855
2042
2043 /*
2044 ||The loop is commented by rchandan for Bug 5694855
2045 ||This loop is no more required as OU can only be a source and not a destination now
2046 ||We have the parameters for the OU
2047 */
2048 /* FOR ou_rec in
2049 (
2050 SELECT organization_id party_id ,
2051 'OU' party_type ,
2052 set_of_books_id
2053 FROM
2054 hr_operating_units
2055 )
2056 LOOP
2057 IF jai_cmn_utils_pkg.check_jai_exists(P_CALLING_OBJECT => 'JAI_TAX_DISTRIB' ,
2058 P_SET_OF_BOOKS_ID => ou_rec.set_of_books_id
2059 ) = TRUE
2060 THEN*/
2061 /*Bug 5879769 bduvarag end*/
2062 calculate_balances_for_ou(p_regime_id => p_regime_id ,
2063 p_balance_date => p_balance_date ,
2064 p_request_id => p_request_id ,
2065 p_org_id => p_org_id ,/*Bug 5879769 bduvarag*/
2066 p_org_type => 'OU' ,/*Bug 5879769 bduvarag*/
2067 p_regn_id => NULL ,
2068 p_regn_no => NULL ,
2069 p_settlement_id => NULL ,
2070 p_called_from => 'DISTRIBUTE_OU' ,/*Bug 5879769 bduvarag*/
2071 p_service_type_code => p_service_type_code /*Bug 5879769 bduvarag*/
2072 );
2073 /*Bug 5879769 bduvarag start*/
2074 /* END IF;
2075 END LOOP;*/
2076 END IF;
2077 FOR io_rec IN
2078 ( select distinct
2079 organization_id, location_id
2080 from JAI_RGM_ORG_REGNS_V
2081 where organization_type = 'IO'
2082 and regime_code = 'SERVICE'
2083 )
2084 LOOP
2085
2086 calculate_balances_for_ou(p_regime_id => p_regime_id ,
2087 p_balance_date => p_balance_date ,
2088 p_request_id => p_request_id ,
2089 p_org_id => io_rec.organization_id ,
2090 p_org_type => 'IO' ,
2091 p_regn_id => NULL ,
2092 p_regn_no => NULL ,
2093 p_settlement_id => NULL ,
2094 p_called_from => 'DISTRIBUTE_IO' ,
2095 p_location_id => io_rec.location_id ,
2096 p_service_type_code => p_service_type_code
2097 );
2098 END LOOP ;
2099
2100 /*Bug 5879769 bduvarag end*/
2101 END IF;
2102 COMMIT;
2103 EXCEPTION
2104 WHEN OTHERS THEN
2105 raise_application_error (-20102 ,' Error Occured is ' || sqlerrm);
2106 END get_balances;
2107
2108
2109
2110
2111 PROCEDURE insert_records_into_register
2112 (
2113 p_repository_id OUT NOCOPY NUMBER ,
2114 p_regime_id NUMBER ,
2115 p_from_party_type VARCHAR2 ,
2116 p_from_party_id NUMBER ,
2117 p_from_locn_id NUMBER ,
2118 p_from_tax_type VARCHAR2 ,
2119 p_from_trx_amount NUMBER ,
2120 p_to_party_type VARCHAR2 ,
2121 p_to_party_id NUMBER ,
2122 p_to_tax_type VARCHAR2 ,
2123 p_to_trx_amount IN OUT NOCOPY NUMBER ,
2124 p_to_locn_id NUMBER ,
2125 p_called_from VARCHAR2 ,
2126 p_trx_date DATE ,
2127 p_acct_req VARCHAR2 ,
2128 p_source VARCHAR2 ,
2129 P_SOURCE_TRX_TYPE VARCHAR2 ,
2130 P_SOURCE_TABLE_NAME VARCHAR2 ,
2131 p_source_doc_id NUMBER ,
2132 p_settlement_id NUMBER ,
2133 p_reference_id NUMBER ,
2134 p_process_flag OUT NOCOPY VARCHAR2 ,
2135 p_process_message OUT NOCOPY VARCHAR2 ,
2136 p_accounting_date Date,
2137 p_from_service_type VARCHAR2 default null, -- bduvarag for Bug 5694855
2138 p_to_service_type VARCHAR2 default null -- bduvarag for Bug 5694855
2139 )
2140 is
2141 ln_repository_id NUMBER;
2142 lv_acct_req_flag VARCHAR2(10);
2143 lv_process_status VARCHAR2(30);
2144 lv_process_message VARCHAR2(1996);
2145 ln_register_id NUMBER;
2146 --lv_balancing_entry VARCHAR2(1) DEFAULT NULL; /*Added by nprashar for bug 7525691*/ commented for bug#6773684
2147 ln_transfer_id NUMBER;
2148 ln_transfer_source_id NUMBER;
2149 ln_transfer_Dest_id NUMBER;
2150 ln_transfer_dest_line_id NUMBER;
2151 lv_transfer_num VARCHAR2(30);
2152 lv_source VARCHAR2(30);
2153 v_register_type JAI_RGM_REGISTRATIONS.ATTRIBUTE_VALUE%TYPE ;--Added by CSahoo, BUG#5073553
2154 lv_rep_register_type VARCHAR2(15);--added for bug#6773684
2155 ln_cess_credit_amt NUMBER;
2156 ln_cess_debit_amt NUMBER;
2157 ln_discounted_amt NUMBER;
2158 ln_charge_account_id NUMBER;
2159 ln_cess_amount NUMBER;
2160 lv_regime_code JAI_RGM_DEFINITIONS.regime_code%TYPE;
2161 ln_charge_accounting_id NUMBER;
2162 ln_balance_accounting_id NUMBER;
2163 ln_credit_amount NUMBER;
2164 ln_debit_amount NUMBER;
2165 lv_excise_cess CONSTANT varchar2(30) := 'EXCISE-CESS'; --rchandan for bug#4428980
2166 /*Added by CSahoo, BUG#5073553*/
2167 lv_io_register JAI_RGM_REGISTRATIONS.ATTRIBUTE_VALUE%TYPE;
2168 ln_receipt_id JAI_CMN_RG_23AC_II_TRXS.receipt_ref%TYPE;
2169 ld_receipt_date JAI_CMN_RG_23AC_II_TRXS.receipt_date%TYPE;
2170 lv_reference_num JAI_CMN_RG_23AC_II_TRXS.reference_num%TYPE;
2171 ln_ref_document_id JAI_CMN_RG_PLA_TRXS.ref_document_id%TYPE;
2172 ld_ref_document_date JAI_CMN_RG_PLA_TRXS.ref_document_date%TYPE;
2173 lv_vendor_cust_flag JAI_CMN_RG_PLA_TRXS.vendor_cust_flag%TYPE;
2174
2175 lv_distribution_type VARCHAR2(30); /*bug 7525691*/
2176
2177
2178 ln_sh_cess_amount number;--Added by kunkumar for bug#6127194
2179
2180
2181 CURSOR c_pla_account(cp_inv_orgn_id number , cp_locn_id number) IS
2182 SELECT modvat_pla_account_id
2183 FROM JAI_CMN_INVENTORY_ORGS
2184 WHERE organization_id = cp_inv_orgn_id
2185 AND location_id = cp_locn_id;
2186
2187 /*
2188 ||Start of bug 5073553
2189 ||Added by CSahoo
2190 */
2191 CURSOR c_rg23a_account (cp_inv_orgn_id number , cp_locn_id number)
2192 IS
2193 SELECT modvat_rm_account_id
2194 FROM JAI_CMN_INVENTORY_ORGS
2195 WHERE organization_id = cp_inv_orgn_id
2196 AND location_id = cp_locn_id;
2197
2198 --start additions for bug#8873924
2199 CURSOR c_rg23c_account (cp_inv_orgn_id number , cp_locn_id number)
2200 IS
2201 SELECT modvat_cg_account_id
2202 FROM JAI_CMN_INVENTORY_ORGS
2203 WHERE organization_id = cp_inv_orgn_id
2204 AND location_id = cp_locn_id;
2205
2206 --start additions for bug#8873924
2207
2208 CURSOR pref_cur(p_organization_id NUMBER, p_location_id NUMBER)IS
2209 SELECT pref_rg23a, pref_rg23c
2210 FROM JAI_CMN_INVENTORY_ORGS
2211 WHERE organization_id = p_organization_id
2212 AND location_id = p_location_id ;
2213
2214 CURSOR rg_bal_cur(p_organization_id NUMBER, p_location_id NUMBER)IS
2215 SELECT NVL(rg23a_balance,0) rg23a_balance ,NVL(rg23c_balance,0) rg23c_balance
2216 FROM JAI_CMN_RG_BALANCES
2217 WHERE organization_id = p_organization_id
2218 AND location_id = p_location_id ;
2219
2220 v_pref_rg23a NUMBER;
2221 v_pref_rg23c NUMBER;
2222 v_rg23a_balance NUMBER;
2223 v_rg23c_balance NUMBER;
2224
2225
2226 --end additions for bug#8873924
2227
2228 /* End of bug 5073553 */
2229
2230
2231 -- abcd
2232 CURSOR c_get_transfer_Dest_id IS
2233 SELECT transfer_Destination_id
2234 FROM JAI_RGM_DIS_DES_TAXES
2235 WHERE transfer_destination_line_id = p_reference_id;
2236
2237 CURSOR c_cess_amt(cp_transfer_dest_id number) IS
2238 SELECT transfer_amount
2239 FROM JAI_RGM_DIS_DES_TAXES
2240 WHERE transfer_destination_id = cp_transfer_dest_id
2241 -- 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
2242 --commented the above and added the below by Sanjikum for Bug#6119459
2243 and tax_type in ('EXCISE-CESS','SERVICE_EDUCATION_CESS');
2244
2245 --Added the below by kunkumar for bug#6127194
2246 CURSOR c_sh_cess_amt(cp_transfer_dest_id number) IS
2247 SELECT transfer_amount
2248 FROM jai_rgm_dis_des_taxes
2249 WHERE transfer_destination_id = cp_transfer_dest_id
2250 and tax_type in ('SERVICE_SH_EDU_CESS','EXCISE_SH_EDU_CESS');
2251 /* bug 13534704 */
2252 cursor c_get_src_rec ( cp_transfer_id jai_rgm_dis_src_hdrs.transfer_id%type
2253 , cp_party_type jai_rgm_dis_src_hdrs.party_type%type
2254 , cp_party_id jai_rgm_dis_src_hdrs.party_id%type
2255 )
2256 is
2257 select transfer_number
2258 from jai_rgm_dis_src_hdrs
2259 where party_type = cp_party_type
2260 and party_id = cp_party_id
2261 and transfer_id = cp_transfer_id;
2262 ln_transfer_number number;
2263 /* bug 13534704 */
2264
2265 BEGIN
2266
2267 IF p_called_from = 'SETTLEMENT' THEN
2268
2269 SELECT jai_rgm_dis_src_hdrs_s.nextval ,
2270 JAI_RGM_DIS_SRC_TAXES_S.nextval ,
2271 jai_rgm_dis_des_hdrs_s.nextval ,
2272 JAI_RGM_DIS_DES_TAXES_S.nextval ,
2273 JAI_RGM_DIS_TRF_NUMS_S.nextval
2274 INTO ln_transfer_id ,
2275 ln_transfer_source_id ,
2276 ln_transfer_dest_id ,
2277 ln_transfer_dest_line_id ,
2278 lv_transfer_num
2279 FROM dual;
2280 INSERT INTO jai_rgm_dis_src_hdrs
2281 (
2282 TRANSFER_ID ,
2283 PARTY_ID ,
2284 PARTY_TYPE ,
2285 LOCATION_ID ,
2286 TRANSFER_NUMBER ,
2287 TRANSACTION_DATE ,
2288 SETTLEMENT_ID ,
2289 CREATION_DATE ,
2290 CREATED_BY ,
2291 LAST_UPDATE_DATE ,
2292 LAST_UPDATED_BY ,
2293 LAST_UPDATE_LOGIN
2294 )
2295 VALUES
2296 (
2297 ln_transfer_id ,
2298 p_from_party_id ,
2299 p_from_party_type ,
2300 p_from_locn_id ,
2301 lv_transfer_num ,
2302 p_trx_date ,
2303 p_settlement_id ,
2304 sysdate ,
2305 fnd_global.user_id ,
2306 sysdate ,
2307 fnd_global.user_id ,
2308 fnd_global.login_id
2309 );
2310 INSERT INTO JAI_RGM_DIS_SRC_TAXES
2311 (
2312 TRANSFER_ID ,
2313 TRANSFER_SOURCE_ID ,
2314 TAX_TYPE ,
2315 DEBIT_BALANCE ,
2316 CREDIT_BALANCE ,
2317 TRANSFER_AMOUNT ,
2318 PARENT_TAX_TYPE ,
2319 PERCENT_OF_PARENT ,
2320 CREATION_DATE ,
2321 CREATED_BY ,
2322 LAST_UPDATE_DATE ,
2323 LAST_UPDATED_BY ,
2324 LAST_UPDATE_LOGIN
2325 )
2326 VALUES
2327 (
2328 ln_transfer_id ,
2329 ln_transfer_source_id ,
2330 p_from_tax_type ,
2331 NULL ,
2332 NULL ,
2333 p_to_trx_amount ,
2334 NULL ,
2335 NULL ,
2336 sysdate ,
2337 fnd_global.user_id ,
2338 sysdate ,
2339 fnd_global.user_id ,
2340 fnd_global.login_id
2341 );
2342 INSERT INTO jai_rgm_dis_des_hdrs
2343 (
2344 TRANSFER_ID ,
2345 TRANSFER_SOURCE_ID ,
2346 TRANSFER_DESTINATION_ID ,
2347 DESTINATION_PARTY_TYPE ,
2348 DESTINATION_PARTY_ID ,
2349 LOCATION_ID ,
2350 AMOUNT_TO_TRANSFER ,
2351 TRANSFER_NUMBER ,
2352 CREATION_DATE ,
2353 CREATED_BY ,
2354 LAST_UPDATE_DATE ,
2355 LAST_UPDATED_BY ,
2356 LAST_UPDATE_LOGIN
2357 )
2358 VALUES
2359 (
2360 ln_transfer_id ,
2361 ln_transfer_source_id ,
2362 ln_transfer_Dest_id ,
2363 p_to_party_type ,
2364 p_to_party_id ,
2365 p_to_locn_id ,
2366 p_to_trx_amount ,
2367 lv_transfer_num ,
2368 sysdate ,
2369 fnd_global.user_id ,
2370 sysdate ,
2371 fnd_global.user_id ,
2372 fnd_global.login_id
2373 );
2374 INSERT INTO JAI_RGM_DIS_DES_TAXES
2375 (
2376 TRANSFER_SOURCE_ID ,
2377 TRANSFER_DESTINATION_ID ,
2378 TRANSFER_DESTINATION_LINE_ID ,
2379 TAX_TYPE ,
2380 DEBIT_BALANCE ,
2381 CREDIT_BALANCE ,
2382 TRANSFER_AMOUNT ,
2383 CREATION_DATE ,
2384 CREATED_BY ,
2385 LAST_UPDATE_DATE ,
2386 LAST_UPDATED_BY ,
2387 LAST_UPDATE_LOGIN
2388 )
2389 VALUES
2390 (
2391 ln_transfer_source_id ,
2392 ln_transfer_Dest_id ,
2393 ln_transfer_dest_line_id,
2394 p_to_tax_type ,
2395 NULL ,
2396 NULL ,
2397 p_to_trx_amount ,
2398 sysdate ,
2399 fnd_global.user_id ,
2400 sysdate ,
2401 fnd_global.user_id ,
2402 fnd_global.login_id
2403 );
2404 END IF;
2405 /*
2406 end of logic for entering into distribution tables when called from settlement program
2407 */
2408 /*
2409 for the source party
2410 */
2411 /*Bug 5879769 bduvarag start*/
2412 IF p_from_party_type IN ( 'OU' )
2413 OR
2414 ( p_from_party_type = 'IO'
2415 AND p_from_tax_type IN (jai_constants.tax_type_service, jai_constants.tax_type_service_edu_cess,
2416 jai_constants.tax_type_sh_service_edu_cess)) THEN -- added, Harshita for Bug 5694855
2417 --Modified by kunkumar for Bug#6127194
2418 /*Bug 5879769 bduvarag end*/
2419
2420 lv_acct_req_flag := jai_constants.YES;
2421
2422 IF p_source = 'SETTLEMENT' THEN
2423 lv_source := jai_constants.source_settle_out ;
2424 -- lv_balancing_entry := jai_constants.NO; --added by nprashar for bug # 7525691 commented for bug#6773684
2425 ELSIF p_source = 'DISTRIBUTION' THEN
2426 /*bug 7525691*/
2427 if 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)
2428 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 )
2429 then
2430 lv_distribution_type:='S-S';
2431 end if;
2432 /*end bug 7525691*/
2433 lv_source := jai_constants.service_src_distribute_out;
2434 END IF;
2435 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)
2436 THEN /*5694855*/
2437 /*
2438 ||Start of bug 5073553
2439 || Added by CSahoo
2440 || Determine the register setup from the from OU regime setup
2441 */
2442
2443 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
2444 p_from_party_type => p_from_party_type ,
2445 p_to_party_type => p_to_party_type
2446 ) = 'PLA'
2447 THEN
2448 v_register_type := jai_constants.register_type_pla;
2449 ELSE
2450 --v_register_type := jai_constants.REGISTER_TYPE_A; commented for bug#6773684
2451 v_register_type :='RG';--bug#6773684
2452 END IF;
2453 /* End of bug 5073553 */
2454 /*Bug 5879769 bduvarag start*/
2455 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
2456 lv_acct_req_flag := jai_constants.YES;
2457 v_register_type := NULL;
2458 /*Bug 5879769 bduvarag end*/
2459 ELSE
2460 v_register_type := NULL;
2461 END IF;
2462 IF p_called_from = 'DISTRIBUTION' THEN
2463 ln_transfer_id := p_reference_id;
2464 /* Bug 13534704 */
2465 open c_get_src_rec ( cp_transfer_id => p_source_doc_id
2466 , cp_party_type => p_from_party_type
2467 , cp_party_id => p_from_party_id
2468 );
2469 fetch c_get_src_rec into ln_transfer_number;
2470 close c_get_src_rec ;
2471 END IF;
2472
2473 --start additions for bug#6773684
2474 if v_register_type='RG'
2475 then
2476 lv_rep_register_type:=jai_constants.register_type_a;
2477 end if;
2478 --end additions for bug#6773684
2479 --this entry is for service to excise transfer
2480 jai_cmn_rgm_recording_pkg.insert_repository_entry
2481 (
2482 P_REPOSITORY_ID => ln_repository_id ,
2483 P_REGIME_ID => p_regime_id ,
2484 P_TAX_TYPE => p_from_tax_type ,
2485 P_ORGANIZATION_TYPE => p_from_party_type ,
2486 P_ORGANIZATION_ID => p_from_party_id ,
2487 P_LOCATION_ID => p_from_locn_id ,
2488 P_SOURCE => lv_source ,
2489 P_SOURCE_TRX_TYPE => p_source_trx_type ,
2490 P_SOURCE_TABLE_NAME => p_source_table_name,
2491 P_SOURCE_DOCUMENT_ID => p_source_doc_id ,
2492 P_TRANSACTION_DATE => p_trx_date ,
2493 P_ACCOUNT_NAME => NULL ,
2494 P_CHARGE_ACCOUNT_ID => NULL ,
2495 P_BALANCING_ACCOUNT_ID => NULL ,
2496 P_AMOUNT => p_to_trx_amount ,
2497 P_DISCOUNTED_AMOUNT => ln_discounted_amt ,
2498 P_ASSESSABLE_VALUE => NULL ,
2499 P_TAX_RATE => NULL ,
2500 P_REFERENCE_ID => ln_transfer_id ,
2501 P_BATCH_ID => NULL ,
2502 P_CALLED_FROM => p_called_from ,
2503 p_process_flag => p_process_flag ,
2504 p_process_message => p_process_message ,
2505 P_SETTLEMENT_ID => p_settlement_id ,
2506 p_accounting_date => p_accounting_date ,
2507 P_ACCNTG_REQUIRED_FLAG => lv_acct_req_flag ,
2508 P_BALANCING_ORGN_TYPE => p_to_party_type ,
2509 P_BALANCING_ORGN_ID => p_to_party_id ,
2510 P_BALANCING_LOCATION_ID => p_to_locn_id ,
2511 P_BALANCING_TAX_TYPE => p_to_tax_type ,
2512 P_BALANCING_ACCNT_NAME =>nvl(lv_rep_register_type ,v_register_type) ,--added nvl(lv_rep_register_type for bug#6773684
2513 P_CURRENCY_CODE => jai_constants.func_curr , -- File.Sql.35 by Brathod
2514 p_service_type_code => p_from_service_type,
2515 p_distribution_type => lv_distribution_type /*bug 7525691*/
2516 );
2517 IF NVL(p_process_flag,'$') <> jai_constants.successful THEN
2518 rollback;
2519 return;
2520 END IF;
2521 p_repository_id := ln_repository_id;
2522 ELSIF p_from_party_type = 'IO' THEN
2523 IF p_from_tax_type = 'EXCISE' THEN
2524 /*
2525 ||Start of bug 5073553
2526 || Added by CSahoo
2527 || Always debit the Rg register in case of IO to Ou distribution irrespective of the regime/regime party attribute setup. */
2528
2529
2530 -- v_register_type := 'RG';/*commented for bug#6773684*/
2531 --start additions for bug#6773684
2532 IF f_get_io_register ( p_party_id => p_from_party_id ,
2533 p_from_party_type => p_from_party_type ,
2534 p_to_party_type => p_to_party_type
2535 ) = 'PLA'
2536 THEN
2537 v_register_type := jai_constants.register_type_pla;
2538 ELSE
2539 --v_register_type := jai_constants.REGISTER_TYPE_A;commented for bug#6773684
2540 v_register_type :='RG';--added for bug#6773684
2541 END IF;
2542 --end additions for bug#6773684
2543
2544
2545 OPEN c_get_transfer_Dest_id;
2546 FETCH c_get_transfer_Dest_id INTO ln_transfer_dest_id;
2547 CLOSE c_get_transfer_Dest_id;
2548 OPEN c_cess_amt(ln_transfer_dest_id);
2549 FETCH c_cess_amt INTO ln_cess_amount;
2550 CLOSE c_cess_amt;
2551
2552 --Added by kunkumar for bug#6127194
2553 OPEN c_sh_cess_amt(ln_transfer_dest_id);
2554 FETCH c_sh_cess_amt INTO ln_sh_cess_amount;
2555 CLOSE c_sh_cess_amt;
2556
2557 IF v_register_type = 'RG' THEN
2558 ln_receipt_id := p_reference_id ;
2559 ld_receipt_date := p_trx_date ;
2560 lv_reference_num := p_source_doc_id ;
2561 --start additions for bug#8873924
2562 /*the preferences ,balances check has been added only for excise to service transfer,
2563 for service -excise transfer ,logic remains unchanged i.e always hit RG23A.
2564 **/
2565
2566 OPEN pref_cur(p_from_party_id, p_from_locn_id);
2567 FETCH pref_cur INTO v_pref_rg23a, v_pref_rg23c;
2568 CLOSE pref_cur;
2569
2570 OPEN rg_bal_cur(p_from_party_id, p_from_locn_id);
2571 FETCH rg_bal_cur INTO v_rg23a_balance, v_rg23c_balance;
2572 CLOSE rg_bal_cur;
2573 --BASED on the preferences and balance availble fetch either A/C register
2574 IF v_pref_rg23a < v_pref_rg23c THEN
2575 if v_rg23a_balance >= NVL(p_to_trx_amount,0) THEN
2576 v_register_type := 'A';
2577
2578 elsif v_rg23c_balance >= NVL(p_to_trx_amount,0) THEN
2579 v_register_type := 'C';
2580 end if;
2581 ELSIF v_pref_rg23c < v_pref_rg23a THEN
2582 if v_rg23c_balance >= NVL(p_to_trx_amount,0) THEN
2583 v_register_type := 'C';
2584
2585 elsif v_rg23a_balance >= NVL(p_to_trx_amount,0) THEN
2586 v_register_type := 'A';
2587 end if;
2588 END IF;
2589
2590
2591 /*added if--elsif condition for bug#8873924*/
2592 if v_register_type= 'A'
2593 then
2594 OPEN c_rg23a_account(p_from_party_id ,p_from_locn_id );
2595 FETCH c_rg23a_account INTO ln_charge_account_id;
2596 CLOSE c_rg23a_account;
2597 elsif v_register_type= 'C'
2598 then
2599 OPEN c_rg23c_account(p_from_party_id ,p_from_locn_id );
2600 FETCH c_rg23c_account INTO ln_charge_account_id;
2601 CLOSE c_rg23c_account;
2602 end if;
2603
2604 --end additions for bug#8873924
2605 --start additions for bug#6773684
2606 ELSIF v_register_type = 'PLA' THEN
2607 ln_ref_document_id := p_reference_id ;
2608 ld_ref_document_date := p_trx_date ;
2609 lv_vendor_cust_flag := 'O' ;
2610 OPEN c_pla_account(p_from_party_id ,p_from_locn_id );
2611 FETCH c_pla_account INTO ln_charge_account_id;
2612 CLOSE c_pla_account;
2613
2614 END IF;
2615 --end additions for bug#6773684
2616
2617 -- register_type = RG
2618 --commented the following by kunkumar for bug#6127194
2619
2620 /* jai_cmn_rg_23ac_ii_pkg.insert_row(
2621 P_REGISTER_ID => ln_register_id ,
2622 P_INVENTORY_ITEM_ID => -999 ,
2623 P_ORGANIZATION_ID => p_from_party_id ,
2624 P_RECEIPT_ID => p_reference_id ,
2625 P_RECEIPT_DATE => p_trx_date ,
2626 P_CR_BASIC_ED => NULL,
2627 P_CR_ADDITIONAL_ED => NULL,
2628 P_CR_OTHER_ED => NULL,
2629 P_DR_BASIC_ED => p_to_trx_amount ,
2630 P_DR_ADDITIONAL_ED => NULL,
2631 P_DR_OTHER_ED => NULL,
2632 P_EXCISE_INVOICE_NO => NULL,
2633 P_EXCISE_INVOICE_DATE => NULL,
2634 P_REGISTER_TYPE => jai_constants.REGISTER_TYPE_A ,
2635 P_REMARKS => 'DISTRIBUTION - OUT',
2636 P_VENDOR_ID => NULL,
2637 P_VENDOR_SITE_ID => NULL ,
2638 P_CUSTOMER_ID => NULL,
2639 P_CUSTOMER_SITE_ID => NULL,
2640 P_LOCATION_ID => p_from_locn_id,
2641 P_TRANSACTION_DATE => p_trx_date ,
2642 P_CHARGE_ACCOUNT_ID => NULL ,
2643 P_REGISTER_ID_PART_I => NULL ,
2644 P_REFERENCE_NUM => p_source_doc_id,
2645 P_ROUNDING_ID => NULL ,
2646 P_OTHER_TAX_CREDIT => NULL,
2647 P_OTHER_TAX_DEBIT => ln_cess_amount,
2648 P_TRANSACTION_TYPE => 'DISTRIBUTION' ,
2649 P_TRANSACTION_SOURCE => 'DISTRIBUTION' ,
2650 P_CALLED_FROM => p_called_from ,
2651 P_SIMULATE_FLAG => 'N' ,
2652 p_process_status => p_process_flag,
2653 P_PROCESS_MESSAGE => p_process_message
2654 );*/
2655 create_io_register_entry (
2656 p_register_type => v_register_type ,
2657 p_tax_type => 'EXCISE' ,
2658 p_organization_id => p_from_party_id ,
2659 p_location_id => p_from_locn_id ,
2660 p_cr_basic_ed => NULL ,
2661 p_cr_additional_ed => NULL ,
2662 p_cr_other_ed => NULL ,
2663 p_dr_basic_ed => p_to_trx_amount ,
2664 p_dr_additional_ed => NULL ,
2665 p_dr_other_ed => NULL ,
2666 p_excise_invoice_no => ln_transfer_number, --'DISTRIBUTION-'||ln_transfer_id ,/*rchandan, Bug 5563300*//*Modified for bug 13534704 */
2667 p_remarks => 'DISTRIBUTION - OUT' ,
2668 p_vendor_id => NULL ,
2669 p_vendor_site_id => NULL ,
2670 p_transaction_date => p_trx_date ,
2671 p_charge_account_id => ln_charge_account_id ,
2672 p_other_tax_credit => NULL ,
2673 p_other_tax_debit => NVL(ln_cess_amount,0) + NVL(ln_sh_cess_amount,0) ,
2674 p_transaction_type => 'DISTRIBUTION' ,
2675 p_transaction_source => 'DISTRIBUTION' ,
2676 p_called_from => p_called_from ,
2677 p_simulate_flag => 'N' ,
2678 p_debit_amt => ln_cess_amount ,
2679 p_credit_amt => NULL ,
2680 --Added the below 2 columns by Sanjikum for Bug#6119459
2681 p_sh_cess_debit_amt => ln_sh_cess_amount,
2682 p_sh_cess_credit_amt => NULL ,
2683 p_inventory_item_id => -999 ,
2684 /*RG specific parameters */
2685 p_receipt_id => ln_receipt_id ,
2686 p_receipt_date => ld_receipt_date ,
2687 p_excise_invoice_date => p_trx_date ,/*rchandan, Bug 5563300*/
2688 p_customer_id => NULL ,
2689 p_customer_site_id => NULL ,
2690 p_register_id_part_i => NULL ,
2691 p_reference_num => lv_reference_num ,
2692 p_rounding_id => NULL ,
2693 /*PLA specific parameters */
2694 p_ref_document_id => ln_ref_document_id ,
2695 p_ref_document_date => ld_ref_document_date ,
2696 p_dr_invoice_id => NULL ,
2697 p_dr_invoice_date => NULL ,
2698 p_bank_branch_id => NULL ,
2699 p_entry_date => NULL ,
2700 p_vendor_cust_flag => lv_vendor_cust_flag ,
2701 p_process_flag => p_process_flag ,
2702 p_process_message => p_process_message
2703 );
2704
2705 /*jai_cmn_utils_pkg.print_log('dis.log','after call to from io process flag = ' || p_process_flag);
2706 jai_cmn_utils_pkg.print_log('dis.log','err messg is '||p_process_message );*/
2707 IF nvl(p_process_flag,jai_constants.successful) <> jai_constants.successful THEN
2708 rollback;
2709 return;
2710 END IF;
2711 END IF;
2712 END IF;
2713 /*
2714 for the destination party
2715 */
2716 /*Bug 5879769 bduvarag*/
2717 IF p_to_party_type IN ('OU')
2718 OR
2719 ( 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
2720 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*/
2721 lv_acct_req_flag := jai_constants.YES;
2722 -- v_register_type := jai_constants.REGISTER_TYPE_A; commented for bug#6773684
2723
2724 --start additions for bug#6773684
2725 IF f_get_io_register ( p_party_id => p_from_party_id ,
2726 p_from_party_type => p_from_party_type ,
2727 p_to_party_type => p_to_party_type
2728 ) = 'PLA'
2729 THEN
2730 v_register_type := jai_constants.register_type_pla;
2731 ELSE
2732 --v_register_type := jai_constants.REGISTER_TYPE_A;--commneted for bug#6773684
2733 v_register_type :='RG';--added for bug#6773684
2734 END IF;
2735 --end additions for bug#6773684
2736 -- start additions for bug#8873924
2737 /**this check has been added to fetch the v_register_type which will be used to insert into repository
2738 with p from tax type as excise*/
2739
2740 IF v_register_type = 'RG' THEN
2741
2742 OPEN pref_cur(p_from_party_id, p_from_locn_id);
2743 FETCH pref_cur INTO v_pref_rg23a, v_pref_rg23c;
2744 CLOSE pref_cur;
2745
2746 OPEN rg_bal_cur(p_from_party_id, p_from_locn_id);
2747 FETCH rg_bal_cur INTO v_rg23a_balance, v_rg23c_balance;
2748 CLOSE rg_bal_cur;
2749 --BASED on the preferences and balance availble fetch either A/C register
2750 IF v_pref_rg23a < v_pref_rg23c THEN
2751 if v_rg23a_balance >= NVL(p_to_trx_amount,0) THEN
2752 v_register_type := 'A';
2753
2754 elsif v_rg23c_balance >= NVL(p_to_trx_amount,0) THEN
2755 v_register_type := 'C';
2756 end if;
2757 ELSIF v_pref_rg23c < v_pref_rg23a THEN
2758 if v_rg23c_balance >= NVL(p_to_trx_amount,0) THEN
2759 v_register_type := 'C';
2760
2761 elsif v_rg23a_balance >= NVL(p_to_trx_amount,0) THEN
2762 v_register_type := 'A';
2763 end if;
2764 END IF;
2765 end if;
2766
2767 --end additions for bug#8873924
2768
2769
2770
2771 /*Bug 5879769 bduvarag start*/
2772 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
2773 lv_acct_req_flag := jai_constants.YES;
2774 v_register_type := NULL;
2775 /*Bug 5879769 bduvarag end*/
2776 ELSE
2777 lv_acct_req_flag := jai_constants.NO;
2778 v_register_type := NULL;
2779 END IF;
2780 IF p_source = 'SETTLEMENT' THEN
2781 lv_source := jai_constants.source_settle_in ;
2782 -- lv_balancing_entry := jai_constants.NO; --added by nprashar for bug # 7525691 commented for bug#6773684
2783 ELSIF p_source = 'DISTRIBUTION' THEN
2784 /*bug 7525691*/
2785 if 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)
2786 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 )
2787 then
2788 lv_distribution_type:='S-S';
2789 end if;
2790 lv_source := jai_constants.service_src_distribute_in;
2791 END IF;
2792 /* jai_cmn_utils_pkg.print_log('dis.log',' p_to_trx_amount is :' || p_to_trx_amount); */
2793 IF p_called_from = 'DISTRIBUTION' THEN
2794 ln_transfer_id := p_reference_id;
2795 /* Bug 13534704 */
2796 open c_get_src_rec ( cp_transfer_id => p_source_doc_id
2797 , cp_party_type => p_from_party_type
2798 , cp_party_id => p_from_party_id
2799 );
2800 fetch c_get_src_rec into ln_transfer_number;
2801 close c_get_src_rec ;
2802 END IF;
2803
2804 --start additions for bug#6773684
2805 if v_register_type='RG'
2806 then
2807 lv_rep_register_type :=jai_constants.register_type_a;
2808 else
2809 lv_rep_register_type:= v_register_type;--added for bug#8873924
2810 end if;
2811 --end additions for bug#6773684
2812
2813
2814 jai_cmn_rgm_recording_pkg.insert_repository_entry
2815 (
2816 P_REPOSITORY_ID => ln_repository_id ,
2817 P_REGIME_ID => p_regime_id ,
2818 P_TAX_TYPE => p_to_tax_type ,
2819 P_ORGANIZATION_TYPE => p_to_party_type ,
2820 P_ORGANIZATION_ID => p_to_party_id ,
2821 P_LOCATION_ID => p_to_locn_id ,
2822 P_SOURCE => lv_source ,
2823 P_SOURCE_TRX_TYPE => p_source_trx_type ,
2824 P_SOURCE_TABLE_NAME => p_source_table_name,
2825 P_SOURCE_DOCUMENT_ID => p_source_doc_id ,
2826 P_TRANSACTION_DATE => p_trx_date ,
2827 P_ACCOUNT_NAME => NULL ,
2828 P_CHARGE_ACCOUNT_ID => NULL ,
2829 P_BALANCING_ACCOUNT_ID => NULL ,
2830 P_AMOUNT => p_to_trx_amount ,
2831 P_DISCOUNTED_AMOUNT => ln_discounted_amt ,
2832 P_ASSESSABLE_VALUE => NULL ,
2833 P_TAX_RATE => NULL ,
2834 P_REFERENCE_ID => ln_transfer_id ,
2835 P_BATCH_ID => NULL ,
2836 P_CALLED_FROM => p_called_from ,
2837 p_process_flag => p_process_flag ,
2838 p_process_message => p_process_message ,
2839 P_SETTLEMENT_ID => p_settlement_id ,
2840 p_accounting_date => p_trx_date ,
2841 P_ACCNTG_REQUIRED_FLAG => lv_acct_req_flag ,
2842 P_BALANCING_ORGN_TYPE => p_from_party_type ,
2843 P_BALANCING_ORGN_ID => p_from_party_id ,
2844 P_BALANCING_LOCATION_ID => p_from_locn_id ,
2845 P_BALANCING_TAX_TYPE => p_from_tax_type ,
2846 P_BALANCING_ACCNT_NAME =>lv_rep_register_type ,
2847 --added the nvl(lv_rep_register_type for bug#6773684
2848 P_CURRENCY_CODE => jai_constants.func_curr , -- File.Sql.35 by Brathod
2849 p_service_type_code => p_to_service_type ,
2850 p_distribution_type => lv_distribution_type /*bug 7525691*/
2851 );
2852 IF nvl(p_process_flag,'$') <> jai_constants.successful THEN
2853 rollback;
2854 return;
2855 END IF;
2856 p_repository_id := ln_repository_id;
2857 ELSIF p_to_party_type = 'IO' THEN
2858 IF p_to_tax_type = 'EXCISE' THEN
2859
2860 /*
2861 ||Start of bug 5073553
2862 || Added By CSahoo BUG#5073553
2863 || Determine the register setup from the OU regime setup
2864 */
2865
2866 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
2867 p_from_party_type => p_from_party_type ,
2868 p_to_party_type => p_to_party_type
2869 ) ;
2870
2871 OPEN c_get_transfer_Dest_id;
2872 FETCH c_get_transfer_Dest_id INTO ln_transfer_dest_id;
2873 CLOSE c_get_transfer_Dest_id;
2874 OPEN c_cess_amt(ln_transfer_dest_id);
2875 FETCH c_cess_amt INTO ln_cess_amount;
2876 CLOSE c_cess_amt;
2877 --Added by kunkumar for bug#6127194
2878 OPEN c_sh_cess_amt(ln_transfer_dest_id);
2879 FETCH c_sh_cess_amt INTO ln_sh_cess_amount;
2880 CLOSE c_sh_cess_amt;
2881
2882 IF v_register_type = 'RG' THEN
2883 ln_receipt_id := p_reference_id ;
2884 ld_receipt_date := p_trx_date ;
2885 lv_reference_num := p_source_doc_id ;
2886 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
2887 FETCH c_rg23a_account INTO ln_charge_account_id;
2888 CLOSE c_rg23a_account;
2889 ELSIF v_register_type = 'PLA' THEN
2890 ln_ref_document_id := p_reference_id ;
2891 ld_ref_document_date := p_trx_date ;
2892 lv_vendor_cust_flag := 'O' ;
2893 OPEN c_pla_account(p_to_party_id ,p_to_locn_id );
2894 FETCH c_pla_account INTO ln_charge_account_id;
2895 CLOSE c_pla_account;
2896 END IF; --v_register_type = 'RG'
2897 --Commented by kunkumar for Bug#6127194
2898 /* jai_cmn_rg_pla_trxs_pkg.insert_row(
2899 p_register_id => ln_register_id,
2900 p_tr6_challan_no => NULL,
2901 p_tr6_challan_date => NULL,
2902 p_cr_basic_ed => p_to_trx_amount,
2903 p_cr_additional_ed => NULL,
2904 p_cr_other_ed => NULL,
2905 p_ref_document_id => p_reference_id,
2906 p_ref_document_date => p_trx_date,
2907 p_dr_invoice_id => NULL,
2908 p_dr_invoice_date => NULL,
2909 p_dr_basic_ed => NULL,
2910 p_dr_additional_ed => NULL,
2911 p_dr_other_ed => NULL,
2912 p_organization_id => p_to_party_id,
2913 p_location_id => p_to_locn_id,
2914 p_bank_branch_id => NULL,
2915 p_entry_date => NULL,
2916 p_inventory_item_id => -999,
2917 p_vendor_cust_flag => 'O',
2918 p_vendor_id => p_from_party_id,
2919 p_vendor_site_id => NULL,
2920 p_excise_invoice_no => NULL,
2921 p_remarks => 'DISTRIBUTION',
2922 p_transaction_date => p_trx_date,
2923 p_charge_account_id => ln_charge_account_id,
2924 p_other_tax_credit => ln_cess_amount,
2925 p_other_tax_debit => NULL,
2926 p_transaction_type => 'DISTRIBUTION',
2927 p_transaction_source => 'DISTRIBUTION',
2928 p_called_from => p_called_from,
2929 p_simulate_flag => 'N',
2930 p_process_status => p_process_flag,
2931 p_process_message => p_process_message
2932 );*/ --Added the call to create_io_register entry by kunkumar
2933 create_io_register_entry (
2934 p_register_type => v_register_type ,
2935 p_tax_type => 'EXCISE' ,
2936 p_organization_id => p_to_party_id ,
2937 p_location_id => p_to_locn_id ,
2938 p_cr_basic_ed => p_to_trx_amount ,
2939 p_cr_additional_ed => NULL ,
2940 p_cr_other_ed => NULL ,
2941 p_dr_basic_ed => NULL ,
2942 p_dr_additional_ed => NULL ,
2943 p_dr_other_ed => NULL ,
2944 p_excise_invoice_no => ln_transfer_number, --'DISTRIBUTION-'||ln_transfer_id ,/*rchandan, Bug 5563300*/ /* Modified for bug 13534704 */
2945 p_remarks => 'DISTRIBUTION - IN' ,
2946 p_vendor_id => -1 * p_from_party_id , -- Added -1* by Jia for Bug#6174148
2947 p_vendor_site_id => NULL ,
2948 p_transaction_date => p_trx_date ,
2949 p_charge_account_id => ln_charge_account_id ,
2950 p_other_tax_credit => NVL(ln_cess_amount,0) + NVL(ln_sh_cess_amount,0) , --changed by Sanjikum for Bug#6119459
2951 p_other_tax_debit => NULL ,
2952 p_transaction_type => 'DISTRIBUTION' ,
2953 p_transaction_source => 'DISTRIBUTION' ,
2954 p_called_from => p_called_from ,
2955 p_simulate_flag => 'N' ,
2956 p_debit_amt => NULL ,
2957 p_credit_amt => ln_cess_amount ,
2958 --Added the below 2 columns by Sanjikum for Bug#6119459
2959 p_sh_cess_debit_amt => NULL ,
2960 p_sh_cess_credit_amt => ln_sh_cess_amount ,
2961 p_inventory_item_id => -999 ,
2962 /*RG specific parameters */
2963 p_receipt_id => ln_receipt_id ,
2964 p_receipt_date => ld_receipt_date ,
2965 p_excise_invoice_date => p_trx_date ,/*rchandan, Bug 5563300*/
2966 p_customer_id => NULL ,
2967 p_customer_site_id => NULL ,
2968 p_register_id_part_i => NULL ,
2969 p_reference_num => lv_reference_num ,
2970 p_rounding_id => NULL ,
2971 /*PLA specific parameters */
2972 p_ref_document_id => ln_ref_document_id ,
2973 p_ref_document_date => ld_ref_document_date ,
2974 p_dr_invoice_id => NULL ,
2975 p_dr_invoice_date => NULL ,
2976 p_bank_branch_id => NULL ,
2977 p_entry_date => NULL ,
2978 p_vendor_cust_flag => lv_vendor_cust_flag ,
2979 p_process_flag => p_process_flag ,
2980 p_process_message => p_process_message
2981 );
2982
2983 IF nvl(p_process_flag,jai_constants.successful) <> jai_constants.successful THEN
2984 rollback;
2985 return;
2986 END IF;--p_process_flag
2987 END IF;-- p_to_tax_type
2988 END IF; --p_to_party_type
2989 COMMIT;
2990 p_process_flag := 'SS';
2991 EXCEPTION
2992 WHEN OTHERS THEN
2993 p_process_flag := 'UE';
2994 p_process_message := 'Error in procedure - insert_records_into_register ' || substr(sqlerrm,1,1500);
2995 END insert_records_into_register;
2996
2997
2998
2999
3000 PROCEDURE delete_records(p_request_id number) IS
3001
3002 BEGIN
3003 DELETE FROM JAI_RGM_BALANCE_T
3004 WHERE request_id = p_request_id;
3005 COMMIT;
3006
3007 END delete_records;
3008
3009
3010
3011 -- added, Harshita for Bug 5096787
3012 PROCEDURE create_io_register_entry (
3013 p_register_type IN JAI_RGM_REGISTRATIONS.ATTRIBUTE_VALUE%TYPE ,
3014 p_tax_type IN VARCHAR2 ,
3015 p_organization_id IN JAI_CMN_RG_23AC_II_TRXS.organization_id%TYPE ,
3016 p_location_id IN JAI_CMN_RG_23AC_II_TRXS.location_id%TYPE ,
3017 p_cr_basic_ed IN JAI_CMN_RG_23AC_II_TRXS.cr_basic_ed%TYPE ,
3018 p_cr_additional_ed IN JAI_CMN_RG_23AC_II_TRXS.cr_additional_ed%TYPE ,
3019 p_cr_other_ed IN JAI_CMN_RG_23AC_II_TRXS.cr_other_ed%TYPE ,
3020 p_dr_basic_ed IN JAI_CMN_RG_23AC_II_TRXS.dr_basic_ed%TYPE ,
3021 p_dr_additional_ed IN JAI_CMN_RG_23AC_II_TRXS.dr_additional_ed%TYPE ,
3022 p_dr_other_ed IN JAI_CMN_RG_23AC_II_TRXS.dr_other_ed%TYPE ,
3023 p_excise_invoice_no IN JAI_CMN_RG_23AC_II_TRXS.excise_invoice_no%TYPE ,
3024 p_remarks IN JAI_CMN_RG_23AC_II_TRXS.remarks%TYPE ,
3025 p_vendor_id IN JAI_CMN_RG_23AC_II_TRXS.vendor_id%TYPE ,
3026 p_vendor_site_id IN JAI_CMN_RG_23AC_II_TRXS.vendor_site_id%TYPE ,
3027 p_transaction_date IN JAI_CMN_RG_23AC_II_TRXS.transaction_date%TYPE ,
3028 p_charge_account_id IN JAI_CMN_RG_23AC_II_TRXS.charge_account_id%TYPE ,
3029 p_other_tax_credit IN JAI_CMN_RG_23AC_II_TRXS.other_tax_credit%TYPE ,
3030 p_other_tax_debit IN JAI_CMN_RG_23AC_II_TRXS.other_tax_debit%TYPE ,
3031 p_transaction_type IN VARCHAR2 ,
3032 p_transaction_source IN VARCHAR2 ,
3033 p_called_from IN VARCHAR2 ,
3034 p_simulate_flag IN VARCHAR2 ,
3035 p_debit_amt IN JAI_CMN_RG_OTHERS.DEBIT%TYPE ,
3036 p_credit_amt IN JAI_CMN_RG_OTHERS.CREDIT%TYPE ,
3037 p_sh_cess_debit_amt IN JAI_CMN_RG_OTHERS.DEBIT%TYPE,--Added by kunkumar for bug#6127194
3038 p_sh_cess_credit_amt IN JAI_CMN_RG_OTHERS.CREDIT%TYPE,--Added by kunkumar for bug#6127194
3039 p_inventory_item_id IN JAI_CMN_RG_23AC_II_TRXS.INVENTORY_ITEM_ID%TYPE ,
3040 p_receipt_id IN JAI_CMN_RG_23AC_II_TRXS.RECEIPT_REF%TYPE Default NULL ,
3041 p_receipt_date IN JAI_CMN_RG_23AC_II_TRXS.receipt_date%TYPE Default NULL ,
3042 p_excise_invoice_date IN JAI_CMN_RG_23AC_II_TRXS.excise_invoice_date%TYPE Default NULL ,
3043 p_customer_id IN JAI_CMN_RG_23AC_II_TRXS.customer_id%TYPE Default NULL ,
3044 p_customer_site_id IN JAI_CMN_RG_23AC_II_TRXS.customer_site_id%TYPE Default NULL ,
3045 p_register_id_part_i IN JAI_CMN_RG_23AC_II_TRXS.register_id_part_i%TYPE Default NULL ,
3046 p_reference_num IN JAI_CMN_RG_23AC_II_TRXS.reference_num%TYPE Default NULL ,
3047 p_rounding_id IN JAI_CMN_RG_23AC_II_TRXS.rounding_id%TYPE Default NULL ,
3048 p_ref_document_id IN JAI_CMN_RG_PLA_TRXS.ref_document_id%TYPE Default NULL ,
3049 p_ref_document_date IN JAI_CMN_RG_PLA_TRXS.ref_document_date%TYPE Default NULL ,
3050 p_dr_invoice_id IN JAI_CMN_RG_PLA_TRXS.DR_INVOICE_NO%TYPE Default NULL ,
3051 p_dr_invoice_date IN JAI_CMN_RG_PLA_TRXS.dr_invoice_date%TYPE Default NULL ,
3052 p_bank_branch_id IN JAI_CMN_RG_PLA_TRXS.bank_branch_id%TYPE Default NULL ,
3053 p_entry_date IN JAI_CMN_RG_PLA_TRXS.entry_date%TYPE Default NULL ,
3054 p_vendor_cust_flag IN JAI_CMN_RG_PLA_TRXS.vendor_cust_flag%TYPE Default NULL ,
3055 p_process_flag OUT NOCOPY VARCHAR2 ,
3056 p_process_message OUT NOCOPY VARCHAR2
3057 )
3058 IS
3059 --Added by kunkumar for bug#6127194, Start
3060 ln_register_id NUMBER ;
3061 ln_transfer_Dest_id NUMBER ;
3062 ln_cess_amount NUMBER ;
3063 ln_source_type JAI_CMN_RG_OTHERS.SOURCE_TYPE%TYPE ;
3064 lv_source_register JAI_CMN_RG_OTHERS.SOURCE_REGISTER%TYPE ;
3065 lv_register_type VARCHAR2(10);
3066 --End, Added by kunkumar
3067
3068 BEGIN
3069 --Added the body of the procedure by kunkumar for bug#6127194
3070 /*
3071 ||Initialize the variables
3072 */
3073 p_process_flag := jai_constants.successful;
3074 ln_source_type := null ;
3075 lv_source_register := null ;
3076
3077 IF p_tax_type = 'EXCISE' THEN
3078 --start additions for bug#8873924
3079 IF p_register_type in ('RG','A','C') THEN
3080 if p_register_type='RG' then
3081 ln_source_type := 1 ;
3082 lv_source_register := 'RG23A_P2' ;
3083 lv_register_type:='A';
3084 elsif p_register_type='A' then
3085 lv_register_type:='A';
3086 ln_source_type := 1 ;
3087 lv_source_register := 'RG23A_P2' ;
3088 elsif p_register_type='C' then
3089 lv_register_type:='C';
3090 ln_source_type := 1 ;
3091 lv_source_register := 'RG23C_P2' ;
3092 end if;
3093
3094
3095 --end additions for bug#8873924
3096 jai_cmn_rg_23ac_ii_pkg.insert_row(
3097 p_register_id => ln_register_id ,
3098 p_inventory_item_id => p_inventory_item_id ,
3099 p_organization_id => p_organization_id ,
3100 p_receipt_id => p_receipt_id ,
3101 p_receipt_date => p_receipt_date ,
3102 p_cr_basic_ed => p_cr_basic_ed ,
3103 p_cr_additional_ed => p_cr_additional_ed ,
3104 p_cr_other_ed => p_cr_other_ed ,
3105 p_dr_basic_ed => p_dr_basic_ed ,
3106 p_dr_additional_ed => p_dr_additional_ed ,
3107 p_dr_other_ed => p_dr_other_ed ,
3108 p_excise_invoice_no => p_excise_invoice_no ,
3109 p_excise_invoice_date => p_excise_invoice_date ,
3110 p_register_type => lv_register_type ,--jai_constants.register_type_a , bug 8873924
3111 p_remarks => p_remarks ,
3112 p_vendor_id => p_vendor_id ,
3113 p_vendor_site_id => p_vendor_site_id ,
3114 p_customer_id => p_customer_id ,
3115 p_customer_site_id => p_customer_site_id ,
3116 p_location_id => p_location_id ,
3117 p_transaction_date => p_transaction_date ,
3118 p_charge_account_id => p_charge_account_id ,
3119 p_register_id_part_i => p_register_id_part_i ,
3120 p_reference_num => p_reference_num ,
3121 p_rounding_id => p_rounding_id ,
3122 p_other_tax_credit => p_other_tax_credit ,
3123 p_other_tax_debit => p_other_tax_debit ,
3124 p_transaction_type => p_transaction_type ,
3125 p_transaction_source => p_transaction_source ,
3126 p_called_from => p_called_from ,
3127 p_simulate_flag => p_simulate_flag ,
3128 p_process_status => p_process_flag ,
3129 p_process_message => p_process_message
3130 );
3131
3132
3133 ELSIF p_register_type = 'PLA' THEN
3134 ln_source_type := 2 ;
3135 lv_source_register := 'PLA' ;
3136
3137 jai_cmn_rg_pla_trxs_pkg.insert_row(
3138 p_register_id => ln_register_id ,
3139 p_tr6_challan_no => NULL ,
3140 p_tr6_challan_date => NULL ,
3141 p_cr_basic_ed => p_cr_basic_ed ,
3142 p_cr_additional_ed => p_cr_additional_ed ,
3143 p_cr_other_ed => p_cr_other_ed ,
3144 p_ref_document_id => p_ref_document_id ,
3145 p_ref_document_date => p_ref_document_date ,
3146 p_dr_invoice_id => p_dr_invoice_id ,
3147 p_dr_invoice_date => p_dr_invoice_date ,
3148 p_dr_basic_ed => p_dr_basic_ed ,
3149 p_dr_additional_ed => p_dr_additional_ed ,
3150 p_dr_other_ed => p_dr_other_ed ,
3151 p_organization_id => p_organization_id ,
3152 p_location_id => p_location_id ,
3153 p_bank_branch_id => p_bank_branch_id ,
3154 p_entry_date => p_entry_date ,
3155 p_inventory_item_id => p_inventory_item_id ,
3156 p_vendor_cust_flag => p_vendor_cust_flag ,
3157 p_vendor_id => p_vendor_id ,
3158 p_vendor_site_id => p_vendor_site_id ,
3159 p_excise_invoice_no => p_excise_invoice_no ,
3160 p_remarks => p_remarks ,
3161 p_transaction_date => p_transaction_date ,
3162 p_charge_account_id => p_charge_account_id ,
3163 p_other_tax_credit => p_other_tax_credit ,
3164 p_other_tax_debit => p_other_tax_debit ,
3165 p_transaction_type => p_transaction_type ,
3166 p_transaction_source => p_transaction_source ,
3167 p_called_from => p_called_from ,
3168 p_simulate_flag => p_simulate_flag ,
3169 p_process_status => p_process_flag ,
3170 p_process_message => p_process_message
3171 );
3172
3173 END IF;
3174
3175 IF nvl(p_process_flag,jai_constants.successful) <> jai_constants.successful THEN
3176 rollback;
3177 return;
3178 ELSE /* Pass cess entries in jai_rg_others table*/
3179 /* Update the cess amount in the ja_in_rg23_part_ii table*/
3180 IF nvl(p_credit_amt,0) <> 0 OR
3181 nvl(p_debit_amt,0) <> 0
3182 THEN
3183
3184 jai_cmn_rg_others_pkg.insert_row( p_source_type => ln_source_type ,
3185 p_source_name => lv_source_register ,
3186 p_source_id => ln_register_id ,
3187 p_tax_type => 'EXCISE_EDUCATION_CESS' ,
3188 debit_amt => p_debit_amt ,
3189 credit_amt => p_credit_amt ,
3190 p_process_flag => p_process_flag ,
3191 p_process_msg => p_process_message
3192 );
3193
3194 IF nvl(p_process_flag,jai_constants.successful) <> jai_constants.successful THEN
3195
3196 rollback;
3197 return;
3198 END IF;
3199 END IF;
3200
3201 IF nvl(p_sh_cess_debit_amt,0) <> 0 OR
3202 nvl(p_sh_cess_credit_amt,0) <> 0
3203 THEN
3204
3205 jai_cmn_rg_others_pkg.insert_row( p_source_type => ln_source_type ,
3206 p_source_name => lv_source_register ,
3207 p_source_id => ln_register_id ,
3208 p_tax_type => 'EXCISE_SH_EDU_CESS' ,
3209 debit_amt => p_sh_cess_debit_amt ,
3210 credit_amt => p_sh_cess_credit_amt ,
3211 p_process_flag => p_process_flag ,
3212 p_process_msg => p_process_message
3213 );
3214
3215 IF nvl(p_process_flag,jai_constants.successful) <> jai_constants.successful THEN
3216
3217 rollback;
3218 return;
3219 END IF;
3220 END IF;
3221 END IF;
3222 END IF; -- end if of tax_type = 'Excise'
3223 END create_io_register_entry;
3224 --END ;
3225
3226
3227 FUNCTION f_get_io_register ( p_party_id JAI_RGM_BALANCE_T.PARTY_ID%TYPE ,
3228 p_from_party_type JAI_RGM_BALANCE_T.PARTY_TYPE%TYPE ,
3229 p_to_party_type JAI_RGM_BALANCE_T.PARTY_TYPE%TYPE
3230 )
3231 RETURN VARCHAR2
3232 IS
3233 /*
3234 || 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
3235 */
3236 CURSOR cur_get_dist_plg_rg
3237 IS
3238 SELECT
3239 attribute_value
3240 FROM
3241 jai_rgm_org_regns_v
3242 WHERE
3243 --organization_type = 'OU' /*commented by vkaranam for bug#6773684*/
3244 organization_id = p_party_id
3245 AND regime_code = 'SERVICE'
3246 AND registration_type = 'OTHERS'
3247 AND attribute_code = 'DIST_PLA_RG';
3248 ln_attrval_dist_plarg JAI_RGM_REGISTRATIONS.ATTRIBUTE_VALUE%TYPE := NULL;
3249 BEGIN
3250 /*
3251 || Determine the register for the given OU based on the "Service Tax distribution in PLA/RG" attribute setup,
3252 || set it to PLA in case no setup is found
3253 */
3254 OPEN cur_get_dist_plg_rg ;
3255 FETCH cur_get_dist_plg_rg into ln_attrval_dist_plarg;
3256 CLOSE cur_get_dist_plg_rg;
3257 /*
3258 || If the attribute value for Service TAx Distribution in PLA/RG is not set then do the following
3259 || 1. In case of IO to OU debit RG register
3260 || 2. In case of Ou to IO credit PLA register
3261 */
3262 /* commented by vkaranam for bug#6773684*
3263 IF ln_attrval_dist_plarg IS NULL THEN
3264 IF p_from_party_type = 'IO' THEN
3265 ln_attrval_dist_plarg := 'RG';
3266 ELSIF p_to_party_type = 'IO' THEN
3267 ln_attrval_dist_plarg := 'PLA';
3268 END IF;
3269 END IF;
3270 */
3271
3272 /* || If the attribute value for Service TAx Distribution in PLA/RG is not set then Default register wil be RG Register
3273 start additons for Bug #6773684*/
3274 IF ln_attrval_dist_plarg IS NULL THEN
3275 ln_attrval_dist_plarg := 'RG';
3276 END IF;
3277 /*end bug#6773684*/
3278 return(ln_attrval_dist_plarg);
3279
3280 END ;
3281 -- ended, Harshita for Bug 5096787
3282 ----------------------------------------------------------------------------------------------------------------
3283 END JAI_CMN_RGM_TAX_DIST_PKG;
[Home] [Help]