[Home] [Help]
PACKAGE BODY: APPS.JAI_CMN_RGM_SETTLEMENT_PKG
Source
1 PACKAGE BODY jai_cmn_rgm_settlement_pkg AS
2 /* $Header: jai_cmn_rgm_stl.plb 120.28.12020000.3 2012/12/25 09:00:39 qioliu ship $ */
3
4 /* --------------------------------------------------------------------------------------
5 Filename:
6
7 Change History:
8
9 Date Bug Remarks
10 --------- ---------- -------------------------------------------------------------
11 08-Jun-2005 File Version 116.2. Object is Modified to refer to New DB Entity names in place of
12 Old DB Entity Names as required for CASE COMPLAINCE.
13
14 14-Jun-2005 rchandan for bug#4428980, Version 116.3
15 Modified the object to remove literals from DML statements and CURSORS.
16
17 08-Jul-2005 Sanjikum for Bug#4482462
18 1) Removed the column payment_method_lookup_code from cursors - for_terms_id, for_terms_id_2
19 2) In the procedure create_invoice, commented the if condition of payment_method_lookup_code
20 3) In the procedure create_invoice, commented the value of parameter - p_payment_method_lookup_code
21 while calling procedure - jai_ap_utils_pkg.insert_ap_inv_interface
22
23 18-Jul-2005 rchandan for bug#4487676.Version 117.2
24 JAI_RGM_SETTLEMENT_INVOICE_S is replaced with JAI_RGM_SETTLEMENT_S1
25
26 23-Aug-2005 Ramananda for bug#4559828. File Version 120.3
27 Problem:
28 -------
29 R12.FIN.A.QA.ST.2: GETTING ERROR ON PERFORMING SERVICE TAX SETTLEMENT
30 This error is coming inspite of GL and AP periods being open
31
32 Reason:
33 ------
34 Org_id in the form is populated when authority site is selected from the
35 front end. When 'Process' Button is pressed, form makes a call to
36 jai_cmn_rgm_settlement_pkg.create_invoice passing org_id.
37
38 Presently, org_id is not passed to ap_utilities_pkg.get_open_gl_date and
39 ap_utilities_pkg.get_current_gl_date. This is defaulted from mo_global.GET_CURRENT_ORG_ID.
40 However the value is not retrieved from the same, hence the above reported error
41
42 Fix:
43 ----
44 Added pn_org_id parameter while making a call to
45 1. ap_utilities_pkg.get_open_gl_date
46 2. ap_utilities_pkg.get_current_gl_date
47 in jai_cmn_rgm_settlement_pkg.create_invoice is modified to pass org_id, which is solving the problem.
48 i.e "APP-JA-460204: ORA 20001: No Open Period...after <settlement date in the form>"
49
50 02-Dec-2005 Bug 4774647. Added by Lakshmi Gopalsami Version 120.4
51 Passed operating unit also as this parameter has been added by base.
52
53 27-Feb-2006 Bug 4929081. Added by Lakshmi Gopalsami version 120.5
54 (1) Moved cursor counter_cur after inserting into
55 ap_invoices_interface so that invoice_id condition can be used.
56 (2) Removed the select for count(*) and put the same in the cursor.
57 30-JAN-2007 Bug#5631784. Added by CSahoo File Version 120.11
58 Forward Porting of BUG#4742259 (TCS solution)
59 Changes made in the procedure create_invoice to create invoice at the
60 time of TCS settlement. A new cursor cur_distributions_TCS is defined to fetch
61 tax balances.
62
63 27-April-2007 ssawant for bug 5879769,6020629 ,File version 120.6
64 Forward porting of
65 ENH : SERVICE TAX BY INVENTORY ORGANIZATION AND SERVICE TYPE SOLUTION
66 from 11.5( bug no 5694855) to R12 (bug no 5879769).
67 forward porting of bug
68 ACCOUNTING ENTRY ON SETTLEMENT NOT PASSED
69 from 11.5( bug no 4287372) to R12 (bug no 6020629).
70
71 7-June-2007 ssawant for bug 5662296
72 Forward porting R11 bug 5642053 to R12 bug 5662296.
73
74 19-Sep-2007 anujsax for bug#6126142, File Version 120.16
75 Issue : VAT SETTLEMENT ENTRIES NOT GENERATED FOR OFFSET VALUE AT THE TIME OF PAYMENT.
76 The above issue was happening due to passing of SYSDATE for the accounting date
77 for creating AP Invoices and GL Interface.
78 Fix : The seettlement date has been passed as accounting date for AP Invoice and GL Interface
79
80 28-jun-2009 vumaasha for bug 8657720
81 Added an IF condition to consider 'VAT REVERSAL' tax type equivalent to 'VALUE ADDED TAX' during settlement.
82
83 30-sep-2009 vkaranam for bug#8974544
84 Fix:
85 Added regime_id condition in the get_last_Settlement_date(pn_regime_id.pn_or_id) procedure.
86
87 13-Dec-2009 Eric Ma for bug#7031751
88 Fix: FP 12.0 : INDIA LOC- SETTLEMENT ENTRIES ARE NOT GETTING GENERATED
89
90 22-Dec-2009 Eric Ma for bug#8333082,8671217
91 Fix: FP:8281389: VAT SETTLEMENT PAYMENT DETAILS FORM NOT SHOWING THE DATA PROPERLY
92 18-MAR-2011 vkaranam for bug#11821537
93 Description: Notification # 18/2011 proposes change in Point of Taxation for Service Tax.
94 Settlement UI and Process for partial Credit Utilization
95 i.e. Adjustment of liability w.r.t the credit will be restricted and is based on the
96 ptg provided in the regime registration setup.
97 Fix:
98 changes are done in transfeR_balance procedure.
99
100 added nvlcredit_utilized,NVL(r_acct_balances.credit_balance,0)) in transfer_balance procedure.
101 25 02-aug-2011 vkaranam for bug#12706846
102 Issue:
103 VAT Settlement Invoice in case of Settlement at registration number level
104 is imbalanced.
105 VAT Payment Invoice that got created correctly.
106 But when querying the Invoice in Invoice Workbench ,at Invoice Distributions
107 level only the debit lines are appearing and credit lines are not there.
108 As a result the amount in distribution and header level is not matching.
109
110 Fix:
111 Step 1.accounting entry at each and every organization level for setting off the liability with the recovery available
112 example :
113 Recovery(Credit_balance) Liability(Debit_balance) net_balance
114 IN1 20000 25000 -5000
115 IN2 17000 14000 +3000
116
117 Accounting entry shall be :
118
119 IN1:
120
121 Dr VAT liability 20000
122 Cr VAT recovery 20000
123
124 IN2:
125
126 Dr VAT liability 14000
127 Cr VAT recovery 14000
128
129 Step 2:
130
131 Distribute the credit balance available in one organization to the other organization which has the liability (debit balance).
132 Here the repository will be populated source_trx_type as "SETTLEMENT"
133
134 Also the accounting entry will be
135
136 IN1 Dr VAT liability +3000
137 IN2 Cr VAT Recovery +3000
138
139 Logic for the distribution of credit balance from source org to debit balance of destination org is as follows:
140 --fetch the net_balance>0 i.e organizations with the credit available in the DESCENDING ORDER.
141 --fetch the net_balance<0 i.e organizations with the debit available in the ASCENDING ORDER.
142
143 Step 3:
144 Invoice lines shall be generated for the organizations if
145 the net_balance + distribution_amount received from other organizations is < 0
146
147 Changes are done in transfer_balance,create_invoice procedure for step2 and step3.
148 25-aug-2011 vkaranam for bug#12706846
149 Transfer_balance procedure is not distributing the credit balance evenly to the liability.
150 example:
151 credit balance debit_balance
152 2k 2.5k
153 1k 3K
154
155 In the first run of credit balance loop 2k will be setoff against 2.5k
156 In the second run of credit balance loop 1k will be setoff against 2.5k only.
157 as the jai_rgm_Stl_balances are not updated after distribution
158
159 fix:
160
161 added the pl/sql table logic in the transfer_balance procude to distribute the
162 credit available evenly to the liability.
163
164 18-sep-2011 vkaranam for bug#12706846 / 12996230
165 Issue :
166 Clicking on the process button in settlement screen is giving the following error:
167 reference to uninitialized collection
168 fix:
169 Used the extend method to initialized the ln_crdt_transfer pl/sql varaible.
170
171 vkaranam for bug#12996230
172 issue:last settlement date shown for the regime registration level settlement is wrong.
173 fix:changes are done in get_last_Settlement_date function
174
175 28 21-mar-2012 vkaranam for bug#13865856
176 Issue:Service type to be made optional for Service tax distribution.
177 fix:changes are done in get_last_balance_amount procedure.
178 added the nvl condition for service_type_code condition ,which are used for fetching
179 the balances.
180
181 29. 18-Apr-2012 mmurtuza for bug 12641455
182 Description: FOR TCS SETTLEMENT ORGANIZATION AND LOCATION FIELD IS DISABLED IN SETTLEMENT FORM
183 Fix: modified create_invoice and transfer_balance procedures
184
185
186 --------------------------------------------------------------------------------------*/
187
188 PROCEDURE insert_into_vat_register(
189 p_repository_id OUT NOCOPY NUMBER ,
190 p_regime_id NUMBER ,
191 p_from_party_type VARCHAR2 ,
192 p_from_party_id NUMBER ,
193 p_from_locn_id NUMBER ,
194 p_from_tax_type VARCHAR2 ,
195 p_from_trx_amount NUMBER ,
196 p_to_party_type VARCHAR2 ,
197 p_to_party_id NUMBER ,
198 p_to_tax_type VARCHAR2 ,
199 p_to_trx_amount IN OUT NOCOPY NUMBER ,
200 p_to_locn_id NUMBER ,
201 p_called_from VARCHAR2 ,
202 p_trx_date DATE ,
203 p_acct_req VARCHAR2 ,
204 p_source VARCHAR2 ,
205 P_SOURCE_TRX_TYPE VARCHAR2 ,
206 P_SOURCE_TABLE_NAME VARCHAR2 ,
207 p_source_doc_id NUMBER ,
208 p_settlement_id NUMBER ,
209 p_reference_id NUMBER ,
210 p_process_flag OUT NOCOPY VARCHAR2 ,
211 p_process_message OUT NOCOPY VARCHAR2 ,
212 p_accounting_date Date
213 )
214 IS
215 ln_repository_id number;
216 lv_process_status varchar2(30);
217 lv_process_message VARCHAR2(1996);
218
219 lv_source varchar2(30);
220 lv_regime_code JAI_RGM_DEFINITIONS.regime_code%TYPE;
221 ln_charge_accounting_id NUMBER;
222 ln_balance_accounting_id NUMBER;
223 ln_credit_amount NUMBER;
224 ln_debit_amount NUMBER;
225 lv_statement NUMBER;
226
227 BEGIN
228 lv_source := jai_constants.source_settle_out;
229 ln_credit_amount:= NULL; --- these amounts are with respect to repository not w.r.t accounting
230 ln_debit_amount := p_to_trx_amount; --- its is reverse w.r.t accounting
231
232
233 ln_charge_accounting_id := jai_cmn_rgm_recording_pkg.get_account(
234 p_regime_id => p_regime_id,
235 p_organization_type => p_from_party_type,
236 p_organization_id => p_from_party_id,
237 p_location_id => p_from_locn_id,
238 p_tax_type => p_from_tax_type,
239 p_account_name => jai_constants.recovery
240 );
241
242 ln_balance_accounting_id := jai_cmn_rgm_recording_pkg.get_account(
243 p_regime_id => p_regime_id,
244 p_organization_type => p_to_party_type,
245 p_organization_id => p_to_party_id,
246 p_location_id => p_to_locn_id,
247 p_tax_type => p_to_tax_type,
248 p_account_name => jai_constants.recovery
249 );
250
251
252 jai_cmn_rgm_recording_pkg.insert_vat_repository_entry(
253 pn_repository_id => ln_repository_id,
254 pn_regime_id => p_regime_id,
255 pv_tax_type => p_from_tax_type ,
256 pv_organization_type => p_from_party_type,
257 pn_organization_id => p_from_party_id,
258 pn_location_id => p_from_locn_id,
259 pv_source => lv_source,
260 pv_source_trx_type => p_source_trx_type ,
261 pv_source_table_name => p_source_table_name,
262 pn_source_id => p_source_doc_id ,
263 pd_transaction_date => p_trx_date,
264 pv_account_name => jai_constants.recovery,
265 pn_charge_account_id => ln_charge_accounting_id,
266 pn_balancing_account_id => ln_balance_accounting_id,
267 pn_credit_amount => ln_credit_amount,
268 pn_debit_amount => ln_debit_amount,
269 pn_assessable_value => NULL,
270 pn_tax_rate => NULL,
271 pn_reference_id => NULL,
272 pn_batch_id => NULL,
273 pn_inv_organization_id => p_from_party_id,
274 pv_invoice_no => NULL,
275 pv_called_from => p_called_from,
276 pv_process_flag => p_process_flag,
277 pv_process_message => p_process_message,
278 pd_invoice_date => NULL,
279 pn_settlement_id => p_settlement_id --added for bug#7145898 on 25-Dec-2009 by Eric Ma
280 );
281
282
283
284 IF p_process_flag <> 'SS' THEN
285 rollback;
286 return;
287 END IF;
288 p_repository_id := ln_repository_id;
289
290
291 jai_cmn_rgm_recording_pkg.do_vat_accounting(
292 pn_regime_id => p_regime_id,
293 pn_repository_id => ln_repository_id,
294 pv_organization_type => p_from_party_type,
295 pn_organization_id => p_from_party_id,
296 pd_accounting_date => trunc(sysdate),
297 pd_transaction_date => p_trx_date,
298 pn_credit_amount => ln_debit_amount,
299 pn_debit_amount => ln_credit_amount,
300 pn_credit_ccid => ln_charge_accounting_id,
301 pn_debit_ccid => ln_balance_accounting_id,
302 pv_called_from => p_called_from,
303 pv_process_flag => p_process_flag,
304 pv_process_message => p_process_message,
305 pv_tax_type => p_from_tax_type,
306 pv_source => lv_source,
307 pv_source_trx_type => p_source_trx_type,
308 pv_source_table_name => p_source_table_name,
309 pn_source_id => p_source_doc_id,
310 pv_reference_name => jai_constants.repository_name,
311 pn_reference_id => ln_repository_id
312 );
313
314 IF p_process_flag <> 'SS' THEN
315 rollback;
316 return;
317 END IF;
318
319
320 /*for destination*/
321
322
323 lv_source := jai_constants.source_settle_in ;
324 ln_credit_amount := p_from_trx_amount; --- these amounts are with respect to repository not w.r.t accounting
325 ln_debit_amount := NULL; --- its is reverse w.r.t accounting
326
327 ln_charge_accounting_id := jai_cmn_rgm_recording_pkg.get_account(
328 p_regime_id => p_regime_id,
329 p_organization_type => p_to_party_type,
330 p_organization_id => p_to_party_id,
331 p_location_id => p_to_locn_id,
332 p_tax_type => p_to_tax_type,
333 p_account_name => jai_constants.recovery
334 );
335
336 ln_balance_accounting_id := jai_cmn_rgm_recording_pkg.get_account(
337 p_regime_id => p_regime_id,
338 p_organization_type => p_from_party_type,
339 p_organization_id => p_from_party_id,
340 p_location_id => p_from_locn_id,
341 p_tax_type => p_from_tax_type,
342 p_account_name => jai_constants.recovery
343 );
344
345
346 jai_cmn_rgm_recording_pkg.insert_vat_repository_entry(
347 pn_repository_id => ln_repository_id,
348 pn_regime_id => p_regime_id,
349 pv_tax_type => p_to_tax_type ,
350 pv_organization_type => p_to_party_type,
351 pn_organization_id => p_to_party_id,
352 pn_location_id => p_to_locn_id,
353 pv_source => lv_source,
354 pv_source_trx_type => p_source_trx_type ,
355 pv_source_table_name => p_source_table_name,
356 pn_source_id => p_source_doc_id ,
357 pd_transaction_date => p_trx_date,
358 pv_account_name => jai_constants.recovery,
359 pn_charge_account_id => ln_charge_accounting_id,
360 pn_balancing_account_id => ln_balance_accounting_id,
361 pn_credit_amount => ln_credit_amount,
362 pn_debit_amount => ln_debit_amount,
363 pn_assessable_value => NULL,
364 pn_tax_rate => NULL,
365 pn_reference_id => NULL,
366 pn_batch_id => NULL,
367 pn_inv_organization_id => p_to_party_id,
368 pv_invoice_no => NULL,
369 pv_called_from => p_called_from,
370 pv_process_flag => p_process_flag,
371 pv_process_message => p_process_message,
372 pd_invoice_date => NULL,
373 pn_settlement_id => p_settlement_id --added for bug#7145898 on 25-Dec-2009 by Eric Ma
374 );
375
376
377
378 IF p_process_flag <> 'SS' THEN
379 rollback;
380 return;
381 END IF;
382 p_repository_id := ln_repository_id;
383
384
385 jai_cmn_rgm_recording_pkg.do_vat_accounting(
386 pn_regime_id => p_regime_id,
387 pn_repository_id => ln_repository_id,
388 pv_organization_type => p_to_party_type,
389 pn_organization_id => p_to_party_id,
390 pd_accounting_date => trunc(sysdate),
391 pd_transaction_date => p_trx_date,
392 pn_credit_amount => ln_debit_amount,
393 pn_debit_amount => ln_credit_amount,
394 pn_credit_ccid => ln_balance_accounting_id,
395 pn_debit_ccid => ln_charge_accounting_id,
396 pv_called_from => p_called_from,
397 pv_process_flag => p_process_flag,
398 pv_process_message => p_process_message,
399 pv_tax_type => p_to_tax_type,
400 pv_source => lv_source,
401 pv_source_trx_type => p_source_trx_type,
402 pv_source_table_name => p_source_table_name,
403 pn_source_id => p_source_doc_id,
404 pv_reference_name => jai_constants.repository_name,
405 pn_reference_id => ln_repository_id
406 );
407
408
409 IF p_process_flag <> 'SS' THEN
410 rollback;
411 return;
412 END IF;
413
414 /*Bug 13788285 - Start*/
415 /*
416 When VAT Settlement is made at Registration Level, the accounting entries are incorrect
417 Organization - IN1
418 Liability - 450 INR
419 Recovery - 200 INR
420 Organization - IN2
421 Liability - 400 INR
422 Recovery - 500 INR
423 When Settlement is at Registration Level the excess credit in IN2 needs to settled against IN1
424 Recovery IN2 - Cr 100
425 Recovery IN1 - Dr 100
426 But the Recovery form IN1 is not transferred to set off liability in IN1 (below entry is not generated)
427 Recovery IN1 - Cr 100
428 Libility IN1 - Dr 100
429 */
430 lv_source := jai_constants.source_settle_in;
431 ln_credit_amount := p_to_trx_amount;
432 ln_debit_amount := NULL;
433 ln_charge_accounting_id := jai_cmn_rgm_recording_pkg.get_account(
434 p_regime_id => p_regime_id,
435 p_organization_type => p_to_party_type,
436 p_organization_id => p_to_party_id,
437 p_location_id => p_to_locn_id,
438 p_tax_type => p_to_tax_type,
439 p_account_name => jai_constants.liability );
440
441 ln_balance_accounting_id := jai_cmn_rgm_recording_pkg.get_account(
442 p_regime_id => p_regime_id,
443 p_organization_type => p_to_party_type,
444 p_organization_id => p_to_party_id,
445 p_location_id => p_to_locn_id,
446 p_tax_type => p_to_tax_type,
447 p_account_name => jai_constants.liability );
448
449 jai_cmn_rgm_recording_pkg.insert_vat_repository_entry(
450 pn_repository_id => ln_repository_id,
451 pn_regime_id => p_regime_id,
452 pv_tax_type => p_to_tax_type,
453 pv_organization_type => p_to_party_type,
454 pn_organization_id => p_to_party_id,
455 pn_location_id => p_to_locn_id,
456 pv_source => lv_source,
457 pv_source_trx_type => p_source_trx_type,
458 pv_source_table_name => p_source_table_name,
459 pn_source_id => p_source_doc_id,
460 pd_transaction_date => p_trx_date,
461 pv_account_name => jai_constants.liability,
462 pn_charge_account_id => ln_charge_accounting_id,
463 pn_balancing_account_id => ln_balance_accounting_id,
464 pn_credit_amount => ln_credit_amount,
465 pn_debit_amount => ln_debit_amount,
466 pn_assessable_value => NULL,
467 pn_tax_rate => NULL,
468 pn_reference_id => -9999,
469 pn_batch_id => NULL,
470 pn_inv_organization_id => p_to_party_id,
471 pv_invoice_no => NULL,
472 pv_called_from => p_called_from,
473 pv_process_flag => p_process_flag,
474 pv_process_message => p_process_message,
475 pd_invoice_date => NULL,
476 pn_settlement_id => p_settlement_id
477 );
478
479 IF p_process_flag <> 'SS' THEN
480 ROLLBACK;
481 RETURN;
482 END IF;
483
484 p_repository_id := ln_repository_id;
485 jai_cmn_rgm_recording_pkg.do_vat_accounting(
486 pn_regime_id => p_regime_id,
487 pn_repository_id => ln_repository_id,
488 pv_organization_type => p_to_party_type,
489 pn_organization_id => p_to_party_id,
490 pd_accounting_date => TRUNC(sysdate),
491 pd_transaction_date => p_trx_date,
492 pn_credit_amount => ln_debit_amount,
493 pn_debit_amount => ln_credit_amount,
494 pn_credit_ccid => ln_charge_accounting_id,
495 pn_debit_ccid => ln_balance_accounting_id,
496 pv_called_from => p_called_from,
497 pv_process_flag => p_process_flag,
498 pv_process_message => p_process_message,
499 pv_tax_type => p_to_tax_type,
500 pv_source => lv_source,
501 pv_source_trx_type => p_source_trx_type,
502 pv_source_table_name => p_source_table_name,
503 pn_source_id => p_source_doc_id,
504 pv_reference_name => jai_constants.repository_name,
505 pn_reference_id => ln_repository_id
506 );
507
508 IF p_process_flag <> 'SS' THEN
509 ROLLBACK;
510 RETURN;
511 END IF;
512
513 lv_source := jai_constants.source_settle_out ;
514 ln_credit_amount := NULL;
515 ln_debit_amount := p_to_trx_amount;
516
517 ln_charge_accounting_id := jai_cmn_rgm_recording_pkg.get_account(
518 p_regime_id => p_regime_id,
519 p_organization_type => p_to_party_type,
520 p_organization_id => p_to_party_id,
521 p_location_id => p_to_locn_id,
522 p_tax_type => p_to_tax_type,
523 p_account_name => jai_constants.recovery );
524
525 ln_balance_accounting_id := jai_cmn_rgm_recording_pkg.get_account(
526 p_regime_id => p_regime_id,
527 p_organization_type => p_to_party_type,
528 p_organization_id => p_to_party_id,
529 p_location_id => p_to_locn_id,
530 p_tax_type => p_to_tax_type,
531 p_account_name => jai_constants.recovery );
532
533 jai_cmn_rgm_recording_pkg.insert_vat_repository_entry(
534 pn_repository_id => ln_repository_id,
535 pn_regime_id => p_regime_id,
536 pv_tax_type => p_to_tax_type,
537 pv_organization_type => p_to_party_type,
538 pn_organization_id => p_to_party_id,
539 pn_location_id => p_to_locn_id,
540 pv_source => lv_source,
541 pv_source_trx_type => p_source_trx_type,
542 pv_source_table_name => p_source_table_name,
543 pn_source_id => p_source_doc_id,
544 pd_transaction_date => p_trx_date,
545 pv_account_name => jai_constants.recovery,
546 pn_charge_account_id => ln_charge_accounting_id,
547 pn_balancing_account_id => ln_balance_accounting_id,
548 pn_credit_amount => ln_credit_amount,
549 pn_debit_amount => ln_debit_amount,
550 pn_assessable_value => NULL,
551 pn_tax_rate => NULL,
552 pn_reference_id => -9998,
553 pn_batch_id => NULL,
554 pn_inv_organization_id => p_to_party_id,
555 pv_invoice_no => NULL,
556 pv_called_from => p_called_from,
557 pv_process_flag => p_process_flag,
558 pv_process_message => p_process_message,
559 pd_invoice_date => NULL,
560 pn_settlement_id => p_settlement_id
561 );
562
563 IF p_process_flag <> 'SS' THEN
564 ROLLBACK;
565 RETURN;
566 END IF;
567
568 p_repository_id := ln_repository_id;
569 jai_cmn_rgm_recording_pkg.do_vat_accounting(
570 pn_regime_id => p_regime_id,
571 pn_repository_id => ln_repository_id,
572 pv_organization_type => p_to_party_type,
573 pn_organization_id => p_to_party_id,
574 pd_accounting_date => TRUNC(sysdate),
575 pd_transaction_date => p_trx_date,
576 pn_credit_amount => ln_debit_amount,
577 pn_debit_amount => ln_credit_amount,
578 pn_credit_ccid => ln_balance_accounting_id,
579 pn_debit_ccid => ln_charge_accounting_id,
580 pv_called_from => p_called_from,
581 pv_process_flag => p_process_flag,
582 pv_process_message => p_process_message,
583 pv_tax_type => p_to_tax_type,
584 pv_source => lv_source,
585 pv_source_trx_type => p_source_trx_type,
586 pv_source_table_name => p_source_table_name,
587 pn_source_id => p_source_doc_id,
588 pv_reference_name => jai_constants.repository_name,
589 pn_reference_id => ln_repository_id );
590
591 IF p_process_flag <> 'SS' THEN
592 ROLLBACK;
593 RETURN;
594 END IF;
595 /*Bug 13788285 - End*/
596
597 commit;
598
599 p_process_flag := 'SS';
600
601 exception
602 when others then
603 p_process_flag := 'UE';
604 p_process_message := 'Error in procedure - insert_records_into_register ' || substr(sqlerrm,1,1500);
605
606 end insert_into_vat_register;
607
608
609 PROCEDURE transfer_balance( pn_settlement_id IN jai_rgm_stl_balances.settlement_id%TYPE,
610 pv_process_flag OUT NOCOPY VARCHAR2,
611 pv_process_message OUT NOCOPY VARCHAR2,
612 pv_reverse_charge_flag IN jai_rgm_settlements.reverse_charge_flag%TYPE DEFAULT NULL)--Added by Qiong for reverse charge settlement
613
614 IS
615 CURSOR c_debit_balance(lv_tax_type jai_rgm_stl_balances.tax_type%TYPE) IS
616 SELECT NVL(debit_balance,0) - NVL(credit_balance,0) debit_balance,
617 party_id,
618 location_id,
619 service_type_code , /* added by ssawant for bug 5879769 */
620 party_type,
621 rowid
622 FROM jai_rgm_stl_balances
623 WHERE settlement_id = pn_settlement_id
624 AND tax_type = lv_tax_type
625 AND NVL(debit_balance,0) - NVL(credit_balance,0) > 0
626 -- ORDER BY 1 desc; 12706846
627 ORDER BY 1;
628
629 CURSOR c_credit_balance(lv_tax_type jai_rgm_stl_balances.tax_type%TYPE) IS
630 SELECT NVL(credit_balance,0) - NVL(debit_balance,0) credit_balance,
631 party_id,
632 location_id,
633 service_type_code ,/* added by ssawant for bug 5879769 */
634 party_type,
635 rowid
636 FROM jai_rgm_stl_balances
637 WHERE settlement_id = pn_settlement_id
638 AND tax_type = lv_tax_type
639 AND NVL(credit_balance,0) - NVL(debit_balance,0) > 0
640 ORDER BY 1 desc;
641
642 CURSOR c_debit_balance_trx(lv_tax_type jai_rgm_stl_balances.tax_type%TYPE,
643 ln_party_id jai_rgm_stl_balances.party_id%TYPE,
644 ln_location_id jai_rgm_stl_balances.location_id%TYPE,
645 lv_party_type jai_rgm_stl_balances.party_type%TYPE
646 ) IS
647 SELECT (NVL(credit_amount,0) - NVL(debit_amount,0) - NVL(settled_amount,0))*-1 debit_balance, organization_id party_id, rowid
648 FROM jai_rgm_trx_records
649 WHERE tax_type = lv_tax_type
650 AND organization_id = ln_party_id
651 AND nvl(location_id,-999) = nvl(ln_location_id,-999)
652 AND organization_type = lv_party_type
653 AND settlement_id <= pn_settlement_id
654 AND NVL(settled_flag,'N') <> 'Y'
655 AND NVL(credit_amount,0) - NVL(debit_amount,0) - NVL(settled_amount,0) < 0
656 --Added by Qiong for reverse charge settlement
657 AND ( (NVL(pv_reverse_charge_flag,'N')='N' AND source_trx_type NOT IN ('REVERSE_PAID_ACCOUNTING','REVERSE_AVOID_ACCOUNTING','REVERSE_APPLY_ACCOUNTING','REVERSE_UNAPPLY_ACCOUNTING'))
658 OR (NVL(pv_reverse_charge_flag,'N')='Y' AND source_trx_type IN ('REVERSE_PAID_ACCOUNTING','REVERSE_AVOID_ACCOUNTING','REVERSE_APPLY_ACCOUNTING','REVERSE_UNAPPLY_ACCOUNTING'))
659 )
660 ORDER BY 1 desc;
661
662 CURSOR c_credit_balance_trx(lv_tax_type jai_rgm_stl_balances.tax_type%TYPE,
663 ln_party_id jai_rgm_stl_balances.party_id%TYPE,
664 ln_location_id jai_rgm_stl_balances.location_id%TYPE,
665 lv_party_type jai_rgm_stl_balances.party_type%TYPE
666 ) IS
667 SELECT NVL(credit_amount,0) - NVL(debit_amount,0) - NVL(settled_amount,0) credit_balance, organization_id party_id, rowid
668 FROM jai_rgm_trx_records
669 WHERE tax_type = lv_tax_type
670 AND organization_id = ln_party_id
671 AND nvl(location_id,-999) = nvl(ln_location_id,-999)/*rchandan for Service Type FP*/
672 AND organization_type = lv_party_type
673 AND settlement_id <= pn_settlement_id
674 AND NVL(settled_flag,'N') <> 'Y'
675 --Added by Qiong for reverse charge settlement
676 AND ( (NVL(pv_reverse_charge_flag,'N')='N' AND source_trx_type NOT IN ('REVERSE_PAID_ACCOUNTING','REVERSE_AVOID_ACCOUNTING','REVERSE_APPLY_ACCOUNTING','REVERSE_UNAPPLY_ACCOUNTING'))
677 OR (NVL(pv_reverse_charge_flag,'N')='Y' AND source_trx_type IN ('REVERSE_PAID_ACCOUNTING','REVERSE_AVOID_ACCOUNTING','REVERSE_APPLY_ACCOUNTING','REVERSE_UNAPPLY_ACCOUNTING'))
678 )
679 AND NVL(credit_amount,0) - NVL(debit_amount,0) - NVL(settled_amount,0) > 0
680 ORDER BY 1 desc;
681
682
683 CURSOR cur_regime_id IS
684 SELECT regime_id
685 FROM jai_rgm_settlements
686 WHERE settlement_id = pn_settlement_id;
687
688 lv_regime_id jai_rgm_settlements.regime_id%type;
689
690 cursor cur_regime_code is /*Ravi */
691 select regime_code
692 from JAI_RGM_DEFINITIONS
693 where regime_id = lv_regime_id;
694
695 cursor cur_dist_detail IS -- This is ditribution detail sequence
696 SELECT JAI_RGM_DIS_DES_TAXES_S.nextval
697 FROM DUAL;
698
699
700 /* added by ssawant for bug 6020629 */
701 CURSOR c_acct_balances IS
702 SELECT *
703 FROM jai_rgm_stl_balances
704 WHERE NVL(debit_balance,0) >= 0
705 AND NVL(credit_balance,0) >= 0
706 AND settlement_id = pn_settlement_id;
707
708 /*CURSOR cur_regno IS
709 SELECT primary_registration_no
710 FROM jai_rgm_settlements
711 WHERE settlement_id = pn_settlement_id;*/
712 /*rchandan for bug#5642053..commented the above cursor and defined the following cursor*/
713
714 CURSOR cur_stl_details IS
715 SELECT jstl.primary_registration_no,
716 jbal.party_type ,
717 jbal.party_id ,
718 jbal.location_id
719 FROM jai_rgm_stl_balances jbal,jai_rgm_settlements jstl
720 WHERE jbal.settlement_id = jstl.settlement_id
721 AND jbal.settlement_id = pn_settlement_id;
722
723
724 /* CURSOR cur_inv_payment(lp_regn_no VARCHAR2)
725 IS
726 -- || This cursor is used to get the total invoice amount paid
727 -- || when the last settlement was made
728 SELECT sum(credit_amount)
729 FROM jai_rgm_trx_records
730 WHERE regime_primary_regno = lp_regn_no
731 AND source_trx_type = 'Invoice Payment'
732 AND transaction_date = ( select max(settlement_date) + 1
733 from jai_rgm_stl_balances a
734 where 2 = (select count(distinct jbal.settlement_date)
735 from jai_rgm_stl_balances jbal,jai_rgm_settlements jstl
736 where jbal.settlement_id = jstl.settlement_id
737 and jstl.primary_registration_no = lp_regn_no and jbal.settlement_date >= a.settlement_date));
738
739
740 CURSOR cur_balances(lp_org_id number,lp_tax_type varchar2,lp_regn_no varchar2) --4287372
741 IS
742 --|| This cursor is used to retrieve the sum of credit and debit balances as on
743 --|| last settlement date for the given registration number
744 SELECT sum(jbal.credit_balance) credit_balance,sum(jbal.debit_balance) debit_balance
745 FROM jai_rgm_stl_balances jbal,jai_rgm_settlements jstl
746 WHERE jbal.settlement_id = jstl.settlement_id
747 AND jstl.primary_registration_no = lp_regn_no
748 AND jbal.tax_type = lp_tax_type
749 AND jbal.party_id = lp_org_id
750 AND jstl.settlement_date = ( select max(settlement_date)
751 from jai_rgm_stl_balances a
752 where 2 = (select count(distinct jbal.settlement_date)
753 from jai_rgm_stl_balances jbal,jai_rgm_settlements jstl
754 where jbal.settlement_id = jstl.settlement_id
755 and jstl.primary_registration_no = lp_regn_no and jbal.settlement_date >= a.settlement_date ));
756
757 */
758
759 /*rchandan for bug#5642053. Commented the above cursors and redefined them as follows*/
760
761 CURSOR cur_inv_payment(cp_regn_no VARCHAR2,
762 cp_org_type VARCHAR2,
763 cp_org_id NUMBER,
764 cp_location_id NUMBER)
765 IS
766 /*
767 || This cursor is used to get the total invoice amount paid
768 || when the last settlement was made
769 */
770 SELECT sum(credit_amount)
771 FROM jai_rgm_trx_records
772 WHERE source_trx_type = 'Invoice Payment'
773 AND settlement_id = ( SELECT MAX(jbal.settlement_id)
774 FROM jai_rgm_stl_balances jbal,jai_rgm_settlements jstl
775 WHERE jbal.settlement_id = jstl.settlement_id
776 AND jstl.primary_registration_no = cp_regn_no
777 AND jbal.party_type = cp_org_type
778 AND jbal.party_id = cp_org_id
779 AND nvl(jbal.location_id,-999) = nvl(cp_location_id,-999)
780 AND jbal.settlement_id <> pn_settlement_id /*This clause is used to exclude the current settlement*/
781 );
782
783 CURSOR cur_balances(cp_regn_no VARCHAR2 ,
784 cp_org_type VARCHAR2 ,
785 cp_org_id NUMBER ,
786 cp_location_id NUMBER ,
787 cp_tax_type VARCHAR2 )
788 IS
789 /*
790 || This cursor is used to retrieve the sum of credit and debit balances as on
791 || last settlement date for the given registration number,organization and location grouped at the tax type
792 */
793 SELECT sum(credit_balance) credit_balance,sum(debit_balance) debit_balance
794 FROM jai_rgm_stl_balances
795 WHERE settlement_id = ( SELECT MAX(jbal.settlement_id)
796 FROM jai_rgm_stl_balances jbal,jai_rgm_settlements jstl
797 WHERE jbal.settlement_id = jstl.settlement_id
798 AND jstl.primary_registration_no = cp_regn_no
799 AND jbal.party_type = cp_org_type
800 AND jbal.party_id = cp_org_id
801 AND nvl(jbal.location_id,-999) = nvl(cp_location_id,-999)
802 AND jbal.settlement_id <> pn_settlement_id/*This clause is used to exclude the current settlement*/
803 )
804 AND tax_type = cp_tax_type;
805
806
807 /*Start Additions by mmurtuza for bug 12641455*/
808
809 /*For bug 12641455. Remove parameters organization_id and location_id condition as for TCS , these two fields are not passed*/
810
811 CURSOR cur_inv_payment_tcs(cp_regn_no VARCHAR2, cp_organization_type VARCHAR2) IS
812 SELECT SUM(total_tax_amt * decode(sign(total_tax_amt), -1, -1, 0)) -- Negative amount converted to positive and taken as credit amount
813 FROM jai_rgm_refs_all
814 WHERE org_tan_no = cp_regn_no
815 --AND source_document_type = 'Invoice Payment'
816 AND settlement_id =
817 (SELECT MAX(jbal.settlement_id)
818 FROM jai_rgm_stl_balances jbal,
819 jai_rgm_settlements jstl,
820 jai_rgm_definitions jrg
821 WHERE jbal.settlement_id = jstl.settlement_id
822 AND jrg.regime_id = jstl.regime_id
823 AND jrg.regime_code = 'TCS'
824 AND jstl.primary_registration_no = cp_regn_no
825 AND jbal.party_type = cp_organization_type
826 --AND jbal.party_id = cp_organization_id /*For bug 12641455. Commented organization_id and location_id condition as for TCS , these two fields are not passed*/
827 --AND jbal.location_id = cp_location_id
828 AND jbal.settlement_id <> nvl(pn_settlement_id, -999))
829 ;
830
831 CURSOR c_tcs_credit_balance_trx(lv_tax_type jai_rgm_stl_balances.tax_type%TYPE,
832 ln_party_id jai_rgm_stl_balances.party_id%TYPE,
833 ln_location_id jai_rgm_stl_balances.location_id%TYPE,
834 lv_party_type jai_rgm_stl_balances.party_type%TYPE
835 ) IS
836 SELECT NVL(total_tax_amt * decode(sign(total_tax_amt), -1, -1, 0),0) credit_balance, organization_id party_id, rowid
837 FROM jai_rgm_refs_all
838 WHERE organization_id = ln_party_id
839 AND nvl(location_id,-999) = nvl(ln_location_id,-999)/*rchandan for Service Type FP*/
840 AND settlement_id <= pn_settlement_id
841 ORDER BY 1 desc;
842
843
844 CURSOR c_tcs_dedit_balance_trx(lv_tax_type jai_rgm_stl_balances.tax_type%TYPE,
845 ln_party_id jai_rgm_stl_balances.party_id%TYPE,
846 ln_location_id jai_rgm_stl_balances.location_id%TYPE,
847 lv_party_type jai_rgm_stl_balances.party_type%TYPE
848 ) IS
849 SELECT NVL(total_tax_amt * decode(sign(total_tax_amt), -1, 0, 1),0) debit_balance, organization_id party_id, rowid
850 FROM jai_rgm_refs_all
851 WHERE organization_id = ln_party_id
852 AND nvl(location_id,-999) = nvl(ln_location_id,-999)/*rchandan for Service Type FP*/
853 AND settlement_id <= pn_settlement_id
854 ORDER BY 1 desc;
855
856 /*End Additions by mmurtuza for bug 12641455*/
857
858
859 ln_debit_cnt NUMBER;
860 ln_credit_cnt NUMBER;
861 ln_credit_balance NUMBER;
862 ln_transfer_amt NUMBER;
863 ln_repository_id NUMBER;
864 lv_regime_code JAI_RGM_DEFINITIONS.regime_code%type;/* Ravi*/
865 lv_statement NUMBER;
866 ln_dist_dtl_id NUMBER;
867 ln_acct_amount NUMBER; /* added by ssawant for bug 6020629 */
868 ln_charge_accounting_id jai_rgm_trx_records.charge_account_id%type; /* added by ssawant for bug 6020629 */
869 lv_organization_type VARCHAR2(10); /* added by ssawant for bug 6020629 */
870 ln_invoice_amount jai_rgm_trx_records.credit_amount%type ; /* added by ssawant for bug 6020629 */
871 rec_balances cur_balances%ROWTYPE;/* added by ssawant for bug 6020629 */
872 lv_regn_no jai_rgm_settlements.primary_registration_no%type;
873 /*rchandan for bug#5642053 start*/
874 ln_organization_id jai_rgm_stl_balances.party_id%TYPE;
875 ln_location_id jai_rgm_stl_balances.location_id%TYPE;
876 lv_org_type jai_rgm_stl_balances.party_type%TYPE;
877 /*rchandan for bug#5642053 end*/
878 --12706846
879 type crdt_transfer is table of NUMBER ;
880 ln_crdt_transfer crdt_transfer :=crdt_transfer();--added crdt_transfer() for bug#12706846 / 12996230 on 12thsep
881
882 ln_debit_ctr NUMBER:=0;
883 BEGIN
884 /* */
885 -- #****************************************************************************************************************************************************************************************
886 -- #
887 -- # Change History -
888 -- # 1. 27-Jan-2005 Sanjikum for Bug #4059774 Version #115.0
889 -- # New Package created for Service Tax settlement
890 -- #
891 -- # 2. 23-Dec-2009 Eric Ma for bug#7145898
892 -- # ISSUE: VAT SETTLEMENT NOT HAPPENING AT REGISTRATION LEVEL ON HAVING MORE THEN ONE OU
893 -- # Fix: Added the parameter pn_settlement_id in the call to the procedure
894 -- # jai_rgm_trx_recording_pkg.insert_vat_repository_entry.
895 -- # Future Dependencies For the release Of this Object:-
896 -- # (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/
897 -- # A datamodel change )
898
899 --==============================================================================================================
900 -- # --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
901 -- # Current Version Current Bug Dependent Files Version Author Date Remarks
902 -- # Of File On Bug/Patchset Dependent On
903 -- # jai_rgm_settlement_pkg_b.sql
904 -- # --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
905 -- # 115.0 4068930 4146708 Sanjikum 27/01/2005 This file is part of Service tax enhancement. So
906 -- # dependent on Service Tax and Education Cess Enhancement
907 -- # 115.1 4245365 4245089 rchandan 17/03/2005 Changes made to implement VAT
908 -- # 115.2 4245365 4245089 rchandan 20/03/2005 Punching of settlement id in the repository for Invoice Payment happens here from now
909 -- # as this record is not considered while settlement.
910 -- # --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
911 -- # ****************************************************************************************************************************************************************************************
912
913 pv_process_flag := 'SS';
914
915
916 OPEN cur_regime_id;
917 FETCH cur_regime_id INTO lv_regime_id;
918 CLOSE cur_regime_id;
919
920 OPEN cur_regime_code;
921 FETCH cur_regime_code INTO lv_regime_code;
922 CLOSE cur_regime_code;
923 /*rchandan for bug#5642053*/
924 OPEN cur_stl_details;
925 FETCH cur_stl_details INTO lv_regn_no,lv_org_type,ln_organization_id,ln_location_id;
926 CLOSE cur_stl_details;
927
928
929 /*Start changes by mmurtuza for bug 12641455*/
930 if (lv_regime_code <> 'TCS') then
931 OPEN cur_inv_payment(lv_regn_no,lv_org_type,ln_organization_id,ln_location_id); /*rchandan for bug#5642053*/
932 FETCH cur_inv_payment INTO ln_invoice_amount;
933 CLOSE cur_inv_payment;
934 else
935
936 /*For bug 12641455. Remove parameters organization_id and location_id condition as for TCS , these two fields are not passed*/
937
938 OPEN cur_inv_payment_tcs(lv_regn_no, lv_org_type);
939 FETCH cur_inv_payment_tcs
940 INTO ln_invoice_amount;
941 CLOSE cur_inv_payment_tcs; /*For bug 12641455. Earlier it was close cur_inv_payment. This was causing invalid cursor exception*/
942 end if;
943 /*End changes by mmurtuza for bug 12641455*/
944
945
946 /*start added by ssawant for bug 6020629 */
947 FOR r_acct_balances IN c_acct_balances
948 LOOP
949 ln_acct_amount := 0;
950
951 OPEN cur_balances(lv_regn_no,r_acct_balances.party_type,r_acct_balances.party_id,r_acct_balances.location_id,r_acct_balances.tax_type); -- rchandan for bug#5642053
952 FETCH cur_balances INTO rec_balances;
953 CLOSE cur_balances;
954 --added nvl(r_acct_balances.credit_utilized for bug#11821537 ,budget 2011 phase2
955 IF NVL(r_acct_balances.debit_balance,0) - nvl(r_acct_balances.credit_utilized,NVL(r_acct_balances.credit_balance,0)) > 0 THEN
956
957 --added nvl(r_acct_balances.credit_utilized for bug#11821537 ,budget 2011 phase2
958 ln_acct_amount := nvl(r_acct_balances.credit_utilized,NVL(r_acct_balances.credit_balance,0));
959
960 /*Commented out by Eric Ma on 12-dec-2009, for bug 7031751,begin
961 IF nvl(ln_invoice_amount,0) = 0 and ( nvl( rec_balances.credit_balance,0) <> nvl( rec_balances.debit_balance,0) ) THEN -- added by ssawant for bug 6020629
962
963 || If no invoice payment was made at the last settlement and if the credit and debit balances are not
964 || equal in the previous settlement then the previous settled balance is deducted before making
965 || accounting entries
966
967 ln_acct_amount := ln_acct_amount - nvl( rec_balances.credit_balance,0);
968 END IF;
969 Commented out by Eric Ma for bug 7031751,end*/
970
971 ELSE
972
973 ln_acct_amount := NVL(r_acct_balances.debit_balance,0);
974 /* Commented out by Eric Ma on 12-dec-2009, for bug 7031751,begin
975 IF nvl(ln_invoice_amount,0) = 0 and ( nvl( rec_balances.credit_balance,0) <> nvl( rec_balances.debit_balance,0) ) THEN-- added by ssawant for bug 6020629
976
977 || If no invoice payment was made at the last settlement and if the credit and debit balances are not
978 || equal in the previous settlement then the previous settled balance is deducted before making an
979 || accounting entries
980
981 ln_acct_amount := ln_acct_amount - nvl( rec_balances.debit_balance,0);
982 END IF;
983 Commented out by Eric Ma for bug 7031751,end*/
984 END IF;
985
986 IF lv_regime_code = jai_constants.service_regime THEN
987 lv_organization_type := jai_constants.orgn_type_io;/* added by ssawant for bug 5879769 */
988 ln_acct_amount := ROUND(ln_acct_amount, jai_constants.service_rgm_rnd_factor);
989 ELSIF lv_regime_code = jai_constants.vat_regime THEN
990 lv_organization_type := jai_constants.orgn_type_io;
991 ln_acct_amount := ROUND(ln_acct_amount, jai_constants.vat_rgm_rnd_factor);
992
993 ELSIF lv_regime_code = 'TCS' THEN /*Added code for tcs by mmurtuza for bug 12641455*/
994 lv_organization_type := jai_constants.orgn_type_io;
995 /*ln_acct_amount := ROUND(ln_acct_amount, jai_constants.tcs_rgm_rnd_factor);*/ --Need to discuss
996
997 END IF;
998
999 IF ln_acct_amount <> 0 THEN /* added by ssawant for bug 6020629 */
1000 ln_charge_accounting_id :=
1001 jai_cmn_rgm_recording_pkg.get_account(p_regime_id => lv_regime_id,
1002 p_organization_type => lv_organization_type,
1003 p_organization_id => r_acct_balances.party_id,
1004 p_location_id => r_acct_balances.location_id,
1005 p_tax_type => r_acct_balances.tax_type,
1006 p_account_name => jai_constants.liability);
1007
1008 /*Modifed call for jai_cmn_rgm_recording_pkg.post_accounting for post accounting*/
1009
1010 if (lv_regime_code <> 'TCS') then
1011 jai_cmn_rgm_recording_pkg.post_accounting(
1012 p_regime_code => lv_regime_code,
1013 p_tax_type => r_acct_balances.tax_type,
1014 p_organization_type => lv_organization_type,
1015 p_organization_id => r_acct_balances.party_id,
1016 p_source => jai_constants.source_settle_in,
1017 p_source_trx_type => 'Invoice Payment',
1018 p_source_table_name => 'JAI_RGM_SETTLEMENTS',
1019 p_source_document_id => pn_settlement_id,
1020 p_code_combination_id => ln_charge_accounting_id,
1021 p_entered_cr => NULL,
1022 p_entered_dr => ln_acct_amount,
1023 p_accounted_cr => NULL,
1024 p_accounted_dr => ln_acct_amount,
1025 -- p_accounting_date => SYSDATE, commented by anujsax for bug #6126142
1026 p_accounting_date => r_acct_balances.settlement_date, --added by anujsax for Bug#6126142
1027 p_transaction_date => r_acct_balances.settlement_date,
1028 p_calling_object => 'JAIRGMSP',
1029 p_repository_name => jai_constants.repository_name,
1030 p_repository_id => NULL,
1031 p_reference_name => NULL,
1032 p_reference_id => NULL,
1033 p_currency_code => jai_constants.func_curr);
1034 else
1035 jai_cmn_rgm_recording_pkg.post_accounting(
1036 p_regime_code => lv_regime_code,
1037 p_tax_type => r_acct_balances.tax_type,
1038 p_organization_type => lv_organization_type,
1039 p_organization_id => r_acct_balances.party_id,
1040 p_source => jai_constants.source_settle_in,
1041 p_source_trx_type => 'Invoice Payment',
1042 p_source_table_name => 'JAI_RGM_SETTLEMENTS',
1043 p_source_document_id => pn_settlement_id,
1044 p_code_combination_id => ln_charge_accounting_id,
1045 p_entered_cr => NULL,
1046 p_entered_dr => ln_acct_amount,
1047 p_accounted_cr => NULL,
1048 p_accounted_dr => ln_acct_amount,
1049 -- p_accounting_date => SYSDATE, commented by anujsax for bug #6126142
1050 p_accounting_date => r_acct_balances.settlement_date, --added by anujsax for Bug#6126142
1051 p_transaction_date => r_acct_balances.settlement_date,
1052 p_calling_object => 'JAIRGMSP',
1053 p_repository_name => 'JAI_RGM_REFS_ALL',
1054 p_repository_id => NULL,
1055 p_reference_name => NULL,
1056 p_reference_id => NULL,
1057 p_currency_code => jai_constants.func_curr);
1058
1059 end if;
1060
1061
1062 IF pv_process_flag <> 'SS' THEN
1063 goto MAIN_EXIT;
1064 END IF;
1065
1066 END IF;
1067
1068 IF ln_acct_amount <> 0 THEN
1069
1070 ln_charge_accounting_id :=
1071 jai_cmn_rgm_recording_pkg.get_account(p_regime_id => lv_regime_id,
1072 p_organization_type => lv_organization_type,
1073 p_organization_id => r_acct_balances.party_id,
1074 p_location_id => r_acct_balances.location_id,
1075 p_tax_type => r_acct_balances.tax_type,
1076 p_account_name => jai_constants.recovery);
1077
1078 /*Modifed call for jai_cmn_rgm_recording_pkg.post_accounting for post accounting*/
1079
1080 if (lv_regime_code <> 'TCS') then
1081
1082 jai_cmn_rgm_recording_pkg.post_accounting(
1083 p_regime_code => lv_regime_code,
1084 p_tax_type => r_acct_balances.tax_type,
1085 p_organization_type => lv_organization_type,
1086 p_organization_id => r_acct_balances.party_id,
1087 p_source => jai_constants.source_settle_in,
1088 p_source_trx_type => 'Invoice Payment',
1089 p_source_table_name => 'JAI_RGM_SETTLEMENTS',
1090 p_source_document_id => pn_settlement_id,
1091 p_code_combination_id => ln_charge_accounting_id,
1092 p_entered_cr => ln_acct_amount,
1093 p_entered_dr => NULL,
1094 p_accounted_cr => ln_acct_amount,
1095 p_accounted_dr => NULL,
1096 -- p_accounting_date => SYSDATE, commented by anujsax for bug #6126142
1097 p_accounting_date => r_acct_balances.settlement_date, --added by anujsax for Bug#6126142
1098 p_transaction_date => r_acct_balances.settlement_date,
1099 p_calling_object => 'JAIRGMSP',
1100 p_repository_name => jai_constants.repository_name,
1101 p_repository_id => NULL,
1102 p_reference_name => NULL,
1103 p_reference_id => NULL,
1104 p_currency_code => jai_constants.func_curr);
1105
1106 else
1107 jai_cmn_rgm_recording_pkg.post_accounting(
1108 p_regime_code => lv_regime_code,
1109 p_tax_type => r_acct_balances.tax_type,
1110 p_organization_type => lv_organization_type,
1111 p_organization_id => r_acct_balances.party_id,
1112 p_source => jai_constants.source_settle_in,
1113 p_source_trx_type => 'Invoice Payment',
1114 p_source_table_name => 'JAI_RGM_SETTLEMENTS',
1115 p_source_document_id => pn_settlement_id,
1116 p_code_combination_id => ln_charge_accounting_id,
1117 p_entered_cr => ln_acct_amount,
1118 p_entered_dr => NULL,
1119 p_accounted_cr => ln_acct_amount,
1120 p_accounted_dr => NULL,
1121 -- p_accounting_date => SYSDATE, commented by anujsax for bug #6126142
1122 p_accounting_date => r_acct_balances.settlement_date, --added by anujsax for Bug#6126142
1123 p_transaction_date => r_acct_balances.settlement_date,
1124 p_calling_object => 'JAIRGMSP',
1125 p_repository_name => 'JAI_RGM_REFS_ALL',
1126 p_repository_id => NULL,
1127 p_reference_name => NULL,
1128 p_reference_id => NULL,
1129 p_currency_code => jai_constants.func_curr);
1130 end if;
1131
1132 IF pv_process_flag <> 'SS' THEN
1133 goto MAIN_EXIT;
1134 END IF;
1135
1136 END IF;
1137
1138
1139 END LOOP;
1140 /*End added by ssawant for bug 6020629 */
1141
1142
1143
1144 /**Observation :11821537 ,settle in ,settle out repository update is not required as the service tax is by IO and for settlement
1145 organization and location are mandatory ,need to comment the code**/
1146 FOR I in (select distinct b.regime_id, b.settlement_date, a.tax_type
1147 from jai_rgm_stl_balances a,
1148 jai_rgm_settlements b
1149 where a.settlement_id = b.settlement_id
1150 AND a.settlement_id = pn_settlement_id)
1151 LOOP
1152 SELECT count(*)
1153 INTO ln_debit_cnt
1154 FROM jai_rgm_stl_balances
1155 WHERE settlement_id = pn_settlement_id
1156 AND debit_balance >0;
1157
1158
1159 IF ln_debit_cnt = 0 THEN
1160 --There is no Debit balance
1161 goto End_loop;
1162 END IF;
1163
1164 SELECT count(*)
1165 INTO ln_credit_cnt
1166 FROM jai_rgm_stl_balances
1167 WHERE settlement_id = pn_settlement_id
1168 AND credit_balance >0;
1169
1170
1171 IF ln_credit_cnt = 0 THEN
1172 --There is no Credit balance
1173 goto End_loop;
1174 END IF;
1175
1176 FOR cur_credit in c_credit_balance(i.tax_type) LOOP
1177 ln_credit_balance := cur_credit.credit_balance;
1178 --added the plsqltable logic for credit trasferred amt for bug#12706846
1179
1180 ln_debit_ctr :=0;
1181 FOR cur_debit in c_debit_balance(i.tax_type) LOOP
1182 ln_debit_ctr := ln_debit_ctr +1;
1183 ln_crdt_transfer.extend;--added for bug#12706846 ( 12996230 ),12th sep for reference to uninitialized collection.
1184 /*Bug 13788285 - Removed NOT Clause. As ln_crdt_transfer(ln_debit_ctr) is not initialized, all calculations
1185 involving ln_crdt_transfer(ln_debit_ctr) result in NULL*/
1186 if ( ln_crdt_transfer.exists(ln_debit_ctr))
1187 then
1188 ln_crdt_transfer(ln_debit_ctr):=0;
1189
1190 end if;
1191
1192
1193 IF ln_credit_balance >= cur_debit.debit_balance-ln_crdt_transfer(ln_debit_ctr) THEN
1194 ln_credit_balance := ln_credit_balance - ( cur_debit.debit_balance-ln_crdt_transfer(ln_debit_ctr));
1195 ln_transfer_amt := cur_debit.debit_balance-ln_crdt_transfer(ln_debit_ctr);
1196 ln_crdt_transfer(ln_debit_ctr):= ln_crdt_transfer(ln_debit_ctr)+ln_transfer_amt;
1197 ELSE
1198 ln_transfer_amt := ln_credit_balance;
1199 ln_credit_balance := 0;
1200 ln_crdt_transfer(ln_debit_ctr):= ln_crdt_transfer(ln_debit_ctr)+ ln_transfer_amt;
1201 END IF;
1202
1203
1204 OPEN cur_regime_id;
1205 FETCH cur_regime_id INTO lv_regime_id;
1206 CLOSE cur_regime_id;
1207
1208 OPEN cur_regime_code;
1209 FETCH cur_regime_code INTO lv_regime_code;
1210 CLOSE cur_regime_code;
1211
1212
1213 IF lv_regime_code = jai_constants.service_regime THEN /* 4245365*/
1214
1215 /**Observation :11821537 ,settle in ,settle out i.e distribution repository update is not required as the service tax is by IO and for settlement
1216 organization and location are mandatory ,hence commenting the below code**/
1217 /*
1218 jai_cmn_rgm_tax_dist_pkg.insert_records_into_register
1219 (p_repository_id => ln_repository_id,
1220 p_regime_id => i.regime_id,
1221 p_from_party_type => cur_credit.party_type,
1222 p_from_party_id => cur_credit.party_id,
1223 p_from_locn_id => cur_credit.location_id,
1224 p_from_tax_type => i.tax_type,
1225 p_from_service_type => cur_credit.service_type_code,
1226 p_from_trx_amount => ln_transfer_amt,
1227 p_to_party_type => cur_debit.party_type,
1228 p_to_party_id => cur_debit.party_id,
1229 p_to_locn_id => cur_debit.location_id,
1230 p_to_tax_type => i.tax_type,
1231 p_to_service_type => cur_debit.service_type_code,
1232 p_to_trx_amount => ln_transfer_amt,
1233 p_called_from => 'SETTLEMENT',
1234 p_trx_date => i.settlement_date,
1235 p_acct_req => jai_constants.yes,
1236 p_source => 'SETTLEMENT',
1237 p_source_trx_type => 'SETTLEMENT',
1238 p_source_table_name => 'JAI_RGM_SETTLEMENTS',
1239 p_source_doc_id => pn_settlement_id,
1240 p_settlement_id => pn_settlement_id,
1241 p_reference_id => NULL,
1242 p_process_flag => pv_process_flag,
1243 p_process_message => pv_process_message,
1244 p_accounting_date => i.settlement_date);
1245
1246 IF pv_process_flag <> 'SS' THEN
1247 goto MAIN_EXIT;
1248 END IF;
1249 */
1250 null;
1251
1252 ELSIF lv_regime_code = jai_constants.vat_regime THEN /* 4245365*/
1253
1254
1255 OPEN cur_dist_detail;
1256 FETCH cur_dist_detail INTO ln_dist_dtl_id;
1257 CLOSE cur_dist_detail;
1258
1259 insert_into_vat_register(p_repository_id => ln_repository_id,
1260 p_regime_id => i.regime_id,
1261 p_from_party_type => cur_credit.party_type,
1262 p_from_party_id => cur_credit.party_id,
1263 p_from_locn_id => cur_credit.location_id, --added for bug#7145898 on 25-Dec-2009 by Eric Ma
1264 p_from_tax_type => i.tax_type,
1265 p_from_trx_amount => ln_transfer_amt,
1266 p_to_party_type => cur_debit.party_type,
1267 p_to_party_id => cur_debit.party_id,
1268 p_to_locn_id => cur_debit.location_id,
1269 p_to_tax_type => i.tax_type,
1270 p_to_trx_amount => ln_transfer_amt,
1271 p_called_from => 'SETTLEMENT',
1272 p_trx_date => i.settlement_date,
1273 p_acct_req => jai_constants.yes,
1274 p_source => 'SETTLEMENT',
1275 p_source_trx_type => 'SETTLEMENT',
1276 p_source_table_name => 'JAI_RGM_SETTLEMENTS',
1277 p_source_doc_id => ln_dist_dtl_id,
1278 p_settlement_id => pn_settlement_id,
1279 p_reference_id => NULL,
1280 p_process_flag => pv_process_flag,
1281 p_process_message => pv_process_message,
1282 p_accounting_date => i.settlement_date);
1283
1284
1285 IF pv_process_flag <> 'SS' THEN
1286 goto MAIN_EXIT;
1287 END IF;
1288
1289
1290
1291 END IF;
1292
1293 /*Start commenting by mmurtuza for bug 12641455*/
1294
1295 /*IF lv_regime_code NOT IN (jai_constants.service_regime,jai_constants.vat_regime) THEN
1296 --added by vumaasha for bug 7606212
1297
1298 update jai_rgm_stl_balances
1299 SET debit_balance = debit_balance - ln_transfer_amt
1300 WHERE rowid = cur_debit.rowid;
1301
1302 update jai_rgm_stl_balances
1303 SET credit_balance = credit_balance - ln_transfer_amt
1304 WHERE rowid = cur_credit.rowid;
1305
1306 END IF;*/
1307 /*End commenting by mmurtuza for bug 12641455*/
1308
1309 EXIT WHEN ln_credit_balance = 0;
1310 END LOOP;
1311 END LOOP;
1312 --start additions for bug#12706846
1313 if ln_crdt_transfer.count>0 then
1314 ln_crdt_transfer.delete;
1315 end if;
1316 --end additions for bug#12706846
1317 <<End_loop>>
1318 NULL;
1319 END LOOP;
1320
1321 --for each transaction
1322 /**haven't touch the below code for budget 2011 phase 2,as the settled_amount will not be used anywhere*/
1323 FOR I in (select *
1324 from jai_rgm_stl_balances
1325 where settlement_id = pn_settlement_id)
1326 LOOP
1327 IF NVL(i.debit_balance,0) = NVL(i.credit_balance,0) THEN
1328 UPDATE jai_rgm_trx_records
1329 SET settled_flag = 'Y',
1330 settled_amount = NULL
1331 WHERE tax_type = i.tax_type
1332 AND organization_id = i.party_id
1333 AND nvl(location_id,-999) = nvl(i.location_id,-999)
1334 AND organization_type = i.party_type
1335 --Added by Qiong for reverse charge settlement
1336 AND ( (NVL(pv_reverse_charge_flag,'N')='N' AND source_trx_type NOT IN ('REVERSE_PAID_ACCOUNTING','REVERSE_AVOID_ACCOUNTING','REVERSE_APPLY_ACCOUNTING','REVERSE_UNAPPLY_ACCOUNTING'))
1337 OR (NVL(pv_reverse_charge_flag,'N')='Y' AND source_trx_type IN ('REVERSE_PAID_ACCOUNTING','REVERSE_AVOID_ACCOUNTING','REVERSE_APPLY_ACCOUNTING','REVERSE_UNAPPLY_ACCOUNTING'))
1338 )
1339 AND settlement_id <= pn_settlement_id;
1340 ELSE
1341
1342 SELECT count(*)
1343 INTO ln_debit_cnt
1344 FROM jai_rgm_trx_records
1345 WHERE tax_type = i.tax_type
1346 AND organization_id = i.party_id
1347 AND nvl(location_id,-999) = nvl(i.location_id,-999)
1348 AND organization_type = i.party_type
1349 AND settlement_id <= pn_settlement_id
1350 AND NVL(settled_flag,'N') <> 'Y'
1351 --Added by Qiong for reverse charge settlement
1352 AND ( (NVL(pv_reverse_charge_flag,'N')='N' AND source_trx_type NOT IN ('REVERSE_PAID_ACCOUNTING','REVERSE_AVOID_ACCOUNTING','REVERSE_APPLY_ACCOUNTING','REVERSE_UNAPPLY_ACCOUNTING'))
1353 OR (NVL(pv_reverse_charge_flag,'N')='Y' AND source_trx_type IN ('REVERSE_PAID_ACCOUNTING','REVERSE_AVOID_ACCOUNTING','REVERSE_APPLY_ACCOUNTING','REVERSE_UNAPPLY_ACCOUNTING'))
1354 )
1355 AND NVL(credit_amount,0) - NVL(debit_amount,0) - NVL(settled_amount,0) < 0;
1356
1357 IF ln_debit_cnt = 0 THEN
1358 --There is no Debit balance
1359 goto End_loop_txn;
1360 END IF;
1361
1362 SELECT count(*)
1363 INTO ln_credit_cnt
1364 FROM jai_rgm_trx_records
1365 WHERE tax_type = i.tax_type
1366 AND organization_id = i.party_id
1367 AND nvl(location_id,-999) = nvl(i.location_id,-999)
1368 AND organization_type = i.party_type
1369 AND settlement_id <= pn_settlement_id
1370 AND NVL(settled_flag,'N') <> 'Y'
1371 --Added by Qiong for reverse charge settlement
1372 AND ( (NVL(pv_reverse_charge_flag,'N')='N' AND source_trx_type NOT IN ('REVERSE_PAID_ACCOUNTING','REVERSE_AVOID_ACCOUNTING','REVERSE_APPLY_ACCOUNTING','REVERSE_UNAPPLY_ACCOUNTING'))
1373 OR (NVL(pv_reverse_charge_flag,'N')='Y' AND source_trx_type IN ('REVERSE_PAID_ACCOUNTING','REVERSE_AVOID_ACCOUNTING','REVERSE_APPLY_ACCOUNTING','REVERSE_UNAPPLY_ACCOUNTING'))
1374 )
1375 AND NVL(credit_amount,0) - NVL(debit_amount,0) - NVL(settled_amount,0) > 0;
1376
1377 IF ln_credit_cnt = 0 THEN
1378 --There is no Credit balance
1379 goto End_loop_txn;
1380 END IF;
1381
1382 /*Start Additions by mmurtuza for bug 12641455*/
1383 if(lv_regime_code = 'TCS') THEN
1384
1385 FOR cur_credit IN c_tcs_credit_balance_trx(i.tax_type, i.party_id, i.location_id, i.party_type)
1386 LOOP
1387 ln_credit_balance := cur_credit.credit_balance;
1388 FOR cur_debit IN c_tcs_dedit_balance_trx(i.tax_type, i.party_id, i.location_id, i.party_type)
1389 LOOP
1390 IF ln_credit_balance >= cur_debit.debit_balance THEN
1391 ln_credit_balance := ln_credit_balance -cur_debit.debit_balance;
1392 ln_transfer_amt := cur_debit.debit_balance;
1393 ELSE
1394 ln_transfer_amt := ln_credit_balance;
1395 ln_credit_balance := 0;
1396 END IF;
1397
1398 /*Need to discuss how to update for TCS*/
1399
1400 UPDATE jai_rgm_trx_records
1401 SET settled_amount = nvl(settled_amount, 0) -ln_transfer_amt,
1402 settled_flag = 'P'
1403 WHERE rowid = cur_debit.rowid;
1404 UPDATE jai_rgm_trx_records
1405 SET settled_amount = nvl(settled_amount, 0) + ln_transfer_amt,
1406 settled_flag = 'P'
1407 WHERE rowid = cur_credit.rowid;
1408
1409 EXIT
1410 WHEN ln_credit_balance = 0;
1411 END LOOP;
1412 END LOOP;
1413
1414 ELSE
1415 /*End Additions by mmurtuza for bug 12641455*/
1416
1417 FOR cur_credit in c_credit_balance_trx(i.tax_type, i.party_id,i.location_id, i.party_type) LOOP
1418 ln_credit_balance := cur_credit.credit_balance;
1419
1420 FOR cur_debit in c_debit_balance_trx(i.tax_type, i.party_id,i.location_id, i.party_type) LOOP
1421 IF ln_credit_balance >= cur_debit.debit_balance THEN
1422 ln_credit_balance := ln_credit_balance - cur_debit.debit_balance;
1423 ln_transfer_amt := cur_debit.debit_balance;
1424 ELSE
1425 ln_transfer_amt := ln_credit_balance;
1426 ln_credit_balance := 0;
1427 END IF;
1428
1429 UPDATE jai_rgm_trx_records
1430 SET settled_amount = NVL(settled_amount,0) - ln_transfer_amt,
1431 settled_flag = 'P'
1432 WHERE rowid = cur_debit.rowid;
1433
1434 UPDATE jai_rgm_trx_records
1435 SET settled_amount = NVL(settled_amount,0) + ln_transfer_amt,
1436 settled_flag = 'P'
1437 WHERE rowid = cur_credit.rowid;
1438
1439 EXIT WHEN ln_credit_balance = 0;
1440 END LOOP;
1441
1442 END LOOP;
1443
1444 END IF; -- Added by mmurtuza for bug 12641455
1445
1446 END IF;
1447
1448 <<End_loop_txn>>
1449
1450 UPDATE jai_rgm_trx_records
1451 SET settled_flag = 'Y',
1452 settled_amount = debit_amount*-1
1453 WHERE settlement_id <= pn_settlement_id
1454 AND organization_id = i.party_id
1455 AND organization_type = i.party_type
1456 AND nvl(location_id,-999) = nvl(i.location_id,-999)
1457 AND tax_type = i.tax_type
1458 AND debit_amount > 0
1459 AND debit_amount = settled_amount*-1
1460 --Added by Qiong for reverse charge settlement
1461 AND ( (NVL(pv_reverse_charge_flag,'N')='N' AND source_trx_type NOT IN ('REVERSE_PAID_ACCOUNTING','REVERSE_AVOID_ACCOUNTING','REVERSE_APPLY_ACCOUNTING','REVERSE_UNAPPLY_ACCOUNTING'))
1462 OR (NVL(pv_reverse_charge_flag,'N')='Y' AND source_trx_type IN ('REVERSE_PAID_ACCOUNTING','REVERSE_AVOID_ACCOUNTING','REVERSE_APPLY_ACCOUNTING','REVERSE_UNAPPLY_ACCOUNTING'))
1463 );
1464
1465 UPDATE jai_rgm_trx_records
1466 SET settled_flag = 'Y',
1467 settled_amount = credit_amount
1468 WHERE settlement_id <= pn_settlement_id
1469 AND organization_id = i.party_id
1470 AND nvl(location_id,-999) = nvl(i.location_id,-999)
1471 AND organization_type = i.party_type
1472 AND tax_type = i.tax_type
1473 AND credit_amount > 0
1474 AND credit_amount = settled_amount
1475 --Added by Qiong for reverse charge settlement
1476 AND ( (NVL(pv_reverse_charge_flag,'N')='N' AND source_trx_type NOT IN ('REVERSE_PAID_ACCOUNTING','REVERSE_AVOID_ACCOUNTING','REVERSE_APPLY_ACCOUNTING','REVERSE_UNAPPLY_ACCOUNTING'))
1477 OR (NVL(pv_reverse_charge_flag,'N')='Y' AND source_trx_type IN ('REVERSE_PAID_ACCOUNTING','REVERSE_AVOID_ACCOUNTING','REVERSE_APPLY_ACCOUNTING','REVERSE_UNAPPLY_ACCOUNTING'))
1478 );
1479
1480 END LOOP;
1481
1482 <<MAIN_EXIT>>
1483 NULL;
1484
1485 EXCEPTION
1486 WHEN OTHERS THEN
1487 pv_process_flag := 'UE';
1488 pv_process_message := SUBSTR(SQLERRM,1,200);
1489 END transfer_balance;
1490
1491
1492 PROCEDURE create_invoice( pn_regime_id IN jai_rgm_settlements.regime_id%TYPE,
1493 pn_settlement_id IN jai_rgm_settlements.settlement_id%TYPE,
1494 pd_settlement_date IN jai_rgm_settlements.settlement_date%TYPE,
1495 pn_vendor_id IN jai_rgm_settlements.tax_authority_id%TYPE,
1496 pn_vendor_site_id IN jai_rgm_settlements.tax_authority_site_id%TYPE,
1497 pn_calculated_amount IN jai_rgm_settlements.calculated_amount%TYPE,
1498 pn_invoice_amount IN jai_rgm_settlements.payment_amount%TYPE,
1499 pn_org_id IN jai_rgm_stl_balances.party_id%TYPE,
1500 pv_regsitration_no IN jai_rgm_settlements.primary_registration_no%TYPE,
1501 pn_created_by IN ap_invoices_interface.created_by%TYPE,
1502 pd_creation_date IN ap_invoices_interface.creation_date%TYPE,
1503 pn_last_updated_by IN ap_invoices_interface.last_updated_by%TYPE,
1504 pd_last_update_date IN ap_invoices_interface.last_update_date%TYPE,
1505 pn_last_update_login IN ap_invoices_interface.last_update_login%TYPE,
1506 pv_system_invoice_no OUT NOCOPY jai_rgm_settlements.system_invoice_no%TYPE,
1507 pv_process_flag OUT NOCOPY VARCHAR2,
1508 pv_process_message OUT NOCOPY VARCHAR2)
1509 IS
1510
1511 /* Bug 5243532. Added by Lakshmi Gopalsami
1512 * (1) Removed the cursor c_functional_currency which is referring
1513 * to hr_operating_units and implemented using caching logic.
1514 * (2) Removed cursor cur_currency_precision as the precision
1515 * is derived using caching logic.
1516 */
1517
1518 CURSOR for_terms_id(ven_id NUMBER,ven_site_id NUMBER) IS
1519 SELECT terms_id,
1520 --payment_method_lookup_code, --commented the column by Sanjikum for Bug#4482462
1521 pay_group_lookup_code
1522 FROM po_vendor_sites_all
1523 WHERE vendor_id = pn_vendor_id
1524 AND vendor_site_id = pn_vendor_site_id;
1525
1526 CURSOR for_terms_id_2(ven_id NUMBER) IS
1527 SELECT terms_id,
1528 --payment_method_lookup_code, --commented the column by Sanjikum for Bug#4482462
1529 pay_group_lookup_code
1530 FROM po_vendors
1531 WHERE vendor_id = pn_vendor_id;
1532
1533 CURSOR counter_cur(pn_invoice_id ap_invoices_interface.invoice_id%TYPE) IS
1534 SELECT NVL(MAX(line_number),0)
1535 FROM ap_invoice_lines_interface
1536 -- bug 4929081. Added by Lakshmi Gopalsami
1537 WHERE invoice_id = pn_invoice_id;
1538
1539 CURSOR cur_invoice_no IS
1540 SELECT jai_rgm_settlements_s1.NEXTVAL --rchandan for bug#4487676. JAI_RGM_SETTLEMENT_INVOICE_S is replaced with JAI_RGM_SETTLEMENTS_S1
1541 FROM dual;
1542
1543 /* commented the below cursor by ssawant for bug 5879769
1544 CURSOR cur_distributions IS
1545 SELECT tax_type, debit, credit, NVL(debit,0) - NVL(credit,0) balance_amount
1546 FROM JAI_RGM_STL_BALANCES_V
1547 WHERE settlement_id = pn_settlement_id
1548 AND NVL(debit,0) - NVL(credit,0) > 0;
1549 */
1550 /*
1551 || added by ssawant for bug 5879769 . Commented the above cursor and added the following
1552 */
1553 CURSOR cur_distributions_SERVICE
1554 IS
1555 SELECT party_id ,
1556 location_id ,
1557 service_type_code ,
1558 tax_type ,
1559 sum(debit_balance) debit_balance ,
1560 sum(credit_balance) credit_balance,
1561 /**added nvl(credit_utilized for bugdget 2011 ,er#11821537*/
1562 NVL(sum(debit_balance),0) - NVL(sum(nvl(credit_utilized,credit_balance)),0) balance_amount
1563 FROM JAI_RGM_STL_BALANCES
1564 WHERE settlement_id = pn_settlement_id
1565 GROUP BY party_id,location_id,service_type_code,tax_type
1566 HAVING sum(debit_balance) - sum(nvl(credit_utilized,credit_balance)) > 0 ;/**added nvl(credit_utilized for bugdget 2011 ,er#11821537*/
1567
1568
1569 CURSOR cur_distributions_VAT IS
1570 SELECT party_id,location_id,tax_type,
1571 sum(debit_balance) debit_balance, sum(credit_balance) credit_balance,
1572 NVL(sum(debit_balance),0) - NVL(sum(credit_balance),0) balance_amount
1573 FROM JAI_RGM_STL_BALANCES
1574 WHERE settlement_id = pn_settlement_id
1575 GROUP BY party_id,location_id,tax_type
1576 HAVING sum(debit_balance) - sum(credit_balance) > 0 ;
1577
1578 /*start additions by vkaranam for bug#12706846
1579 cursor to fetch the credit taken amount from the credit balances available in the organizations under regime registration
1580 level during the VAT settlement ,this cursor will retrieve the data if the VAT settlement is performed at the OU level.*/
1581 CURSOR cur_recovervat_fromorg (cp_org_id in number,cp_loc_id in number,cp_tax_type varchar2)
1582 IS
1583 SELECT sum(nvl(trx_credit_amount,0))
1584 FROM JAI_RGM_trX_records
1585 WHERE settlement_id = pn_settlement_id
1586 and source_trx_type='SETTLEMENT'
1587 and source_table_name='JAI_RGM_SETTLEMENTS'
1588 and nvl(trx_credit_amount,0)>0
1589 and organization_id=cp_org_id
1590 and location_id=cp_loc_id
1591 and tax_type=cp_tax_type
1592 AND account_name = 'RECOVERY';/*Bug 13788285*/
1593
1594
1595 ln_recovery_vat jai_rgm_trx_Records.credit_amount%type;
1596 ln_line_amount AP_INVOICE_LINES_ALL.AMOUNT%TYPE;
1597 --end additions by vkaranam for bug#12706846
1598
1599 /*Start additions by mmurtuza for bug12641455*/
1600 CURSOR cur_recovertcs_fromorg (cp_org_id in number,cp_loc_id in number,cp_tax_type varchar2)
1601 IS
1602 SELECT SUM(total_tax_amt * decode(sign(total_tax_amt), -1, -1, 0))
1603 FROM jai_rgm_refs_all
1604 WHERE settlement_id = pn_settlement_id
1605 -- and source_document_type='SETTLEMENT'
1606 -- and source_table_name='JAI_RGM_SETTLEMENTS' --mmurtuza need to discuss
1607 --and nvl(trx_credit_amount,0)>0
1608 and organization_id=cp_org_id
1609 and location_id=cp_loc_id;
1610 --and tax_type=cp_tax_type;
1611
1612 ln_recovery_tcs jai_rgm_refs_all.total_tax_amt%type;
1613
1614 /*end additions by mmurtuza for bug12641455*/
1615
1616
1617 CURSOR cur_distributions_TCS IS /*Added By CSahoo BUG#5631784*/
1618 SELECT party_id,location_id,tax_type,
1619 sum(debit_balance) debit_balance, sum(credit_balance) credit_balance,
1620 NVL(sum(debit_balance),0) - NVL(sum(credit_balance),0) balance_amount
1621 FROM JAI_RGM_STL_BALANCES
1622 WHERE settlement_id = pn_settlement_id
1623 GROUP BY party_id,location_id,tax_type;
1624
1625 CURSOR cur_tax_types(p_reg_type jai_rgm_registrations.registration_type%TYPE )IS --rchandan for bug#4428980
1626 SELECT attribute_sequence, attribute_code tax_type, RATE
1627 FROM JAI_RGM_REGISTRATIONS
1628 WHERE regime_id = pn_regime_id
1629 AND registration_type = p_reg_type--rchandan for bug#4428980
1630 ORDER BY 1 ASC;
1631
1632 CURSOR cur_regime_code IS /* 4245365*/
1633 SELECT regime_code,description
1634 FROM JAI_RGM_DEFINITIONS
1635 WHERE regime_id = pn_regime_id;
1636
1637 CURSOR cur_org_io IS
1638 SELECT party_id,location_id
1639 FROM jai_rgm_stl_balances
1640 WHERE settlement_id = pn_settlement_id
1641 GROUP BY party_id,location_id
1642 HAVING sum(debit_balance) - sum(credit_balance) > 0;
1643
1644 -- Bug 4929081. Added by Lakshmi Gopalsami
1645
1646 CURSOR cur_inv_exists(pn_invoice_id IN ap_invoices_interface.invoice_id%TYPE) IS
1647 SELECT 'Y'
1648 FROM ap_invoices_interface
1649 WHERE invoice_id = pn_invoice_id;
1650
1651
1652 lv_invoice_num ap_invoices_interface.invoice_num%TYPE;
1653 lv_currency_code gl_sets_of_books.currency_code%TYPE;
1654 for_terms_id_rec for_terms_id%ROWTYPE;
1655 counter_tds_dm_v NUMBER;
1656 ln_tmp NUMBER;
1657 ln_dist_code_combination_id ap_invoice_lines_interface.dist_code_combination_id%TYPE;
1658 lv_tax_type jai_rgm_stl_balances.tax_type%TYPE;
1659 lv_tax_type1 jai_rgm_stl_balances.tax_type%TYPE;
1660 lv_tax_type2 jai_rgm_stl_balances.tax_type%TYPE;
1661 ln_amount jai_rgm_stl_balances.debit_balance%TYPE;
1662 ln_amount1 jai_rgm_stl_balances.debit_balance%TYPE;
1663 ln_amount2 jai_rgm_stl_balances.debit_balance%TYPE;
1664 ln_rate JAI_RGM_REGISTRATIONS.rate%TYPE;
1665 v_open_period gl_period_statuses.period_name%type;
1666 v_open_gl_date date;
1667 lv_inv_exists varchar2(1) := 'N' ; -- Bug 4929081
1668 req_id NUMBER;
1669 ln_invoice_amount NUMBER;
1670 ln_precision fnd_currencies.precision%TYPE;
1671 ln_invoice_id NUMBER;
1672 ln_invoice_line_id NUMBER;
1673 lv_regime cur_regime_code%rowtype ;/* 4245365*/
1674 ln_org_id NUMBER;
1675 org_io_rec cur_org_io%ROWTYPE;
1676
1677 /* Bug5243532. Added by Lakshmi Gopalsami
1678 Defined the variable for implementing caching logic.
1679 */
1680 l_func_curr_det jai_plsql_cache_pkg.func_curr_details;
1681
1682 BEGIN
1683
1684 pv_process_flag := 'SS';
1685
1686 OPEN cur_regime_code; /* 4245365*/
1687 FETCH cur_regime_code INTO lv_regime;
1688 CLOSE cur_regime_code;
1689
1690
1691 OPEN cur_invoice_no;
1692 FETCH cur_invoice_no INTO ln_tmp;
1693 CLOSE cur_invoice_no;
1694
1695 lv_invoice_num := upper(lv_regime.regime_code)||'/'||pn_org_id||'/'||ln_tmp; /*4245365*/
1696
1697 /* Bug 5373747. Added by Lakshmi Gopalsami
1698 * Set the policy context before calling AP API
1699 */
1700 mo_global.set_policy_context('S', pn_org_id);
1701
1702 /* Bug 5243532. Added by Lakshmi Gopalsami
1703 * Removed the reference to hr_operating_units
1704 * and implemented using caching logic for getting functional currency
1705 * and precision.
1706 */
1707 l_func_curr_det := jai_plsql_cache_pkg.return_sob_curr
1708 (p_org_id => pn_org_id );
1709
1710 lv_currency_code := l_func_curr_det.currency_code;
1711 ln_precision := l_func_curr_det.precision;
1712
1713 -- Bug 4929081. Added by Lakshmi Gopalsami
1714 -- Moved the cursor after inserting headers.
1715
1716 OPEN for_terms_id(pn_vendor_id,pn_vendor_site_id);
1717 FETCH for_terms_id INTO for_terms_id_rec;
1718 CLOSE for_terms_id;
1719
1720 IF ((for_terms_id_rec.terms_id IS NULL) OR
1721 --(for_terms_id_rec.payment_method_lookup_code IS NULL) OR --commented by Sanjikum for Bug#4482462
1722 (for_terms_id_rec.pay_group_lookup_code IS NULL)
1723 ) THEN
1724
1725 OPEN for_terms_id_2(pn_vendor_id);
1726 FETCH for_terms_id_2 INTO for_terms_id_rec;
1727 CLOSE for_terms_id_2;
1728 END IF;
1729 --commented by anujsax for bug 6126142
1730 -- v_open_period := ap_utilities_pkg.get_current_gl_date(TRUNC(pd_creation_date)
1731 -- ,pn_org_id /* Added by Ramananda for bug#4559828 */
1732 -- );
1733 -- added by anujsax for bug 6126142
1734 v_open_period := ap_utilities_pkg.get_current_gl_date(TRUNC(pd_settlement_date)
1735 ,pn_org_id /* Added by Ramananda for bug#4559828 */
1736 );
1737 --ended by anujsax for bug 6126142
1738 if v_open_period is null then
1739
1740 ap_utilities_pkg.get_open_gl_date (
1741 -- TRUNC(pd_creation_date),/*commented by anujsax for bug#6126142*/
1742 TRUNC(pd_settlement_date),/* addded by anujsax for bug#6126142*/
1743 v_open_period,
1744 v_open_gl_date
1745 ,pn_org_id /* Added by Ramananda for bug#4559828 */
1746 );
1747
1748 if v_open_period is null then
1749 raise_application_error(-20001,'No Open period ... after '||pd_settlement_date);
1750 end if;
1751 else
1752 --commented by anujsax for bug 6126142
1753 -- v_open_gl_date := TRUNC(pd_creation_date);
1754 --added by anujsax for bug 6126142
1755 v_open_gl_date := TRUNC(pd_settlement_date);
1756 --ended by anujsax for bug 6126142
1757 end if;
1758
1759 jai_ap_utils_pkg.insert_ap_inv_interface(
1760 p_jai_source => 'SETTLEMENT',
1761 p_invoice_id => ln_invoice_id,
1762 p_invoice_num => lv_invoice_num,
1763 p_invoice_date => v_open_gl_date,
1764 p_gl_date => v_open_gl_date,
1765 p_vendor_id => pn_vendor_id,
1766 p_vendor_site_id => pn_vendor_site_id,
1767 p_invoice_amount => ROUND(pn_invoice_amount, ln_precision),
1768 p_invoice_currency_code => lv_currency_code,
1769 p_terms_id => for_terms_id_rec.terms_id,
1770 p_description => 'Settlement of '||lv_regime.description||' Liability on '||pd_settlement_date||' for registration no '||pv_regsitration_no, /*4245365*/
1771 /* Bug 5359044. Added by Lakshmi Gopalsami
1772 * Changed the p_source from 'EXTERNAL'
1773 * to 'INDIA TAX SETTLEMENT INVOICES'
1774 */
1775 /* Bug 5373747. Added by Lakshmi Gopalsami
1776 * As per the discussion with AP Team changing the source
1777 * as 'INDIA TAX SETTLEMENT'
1778 */
1779 p_source => 'INDIA TAX SETTLEMENT',
1780 p_voucher_num => lv_invoice_num,
1781 --p_payment_method_lookup_code => for_terms_id_rec.payment_method_lookup_code, --commented by Sanjikum for Bug#4482462
1782 p_pay_group_lookup_code => for_terms_id_rec.pay_group_lookup_code,
1783 p_org_id => pn_org_id,
1784 p_created_by => pn_created_by,
1785 p_creation_date => pd_creation_date,
1786 p_last_updated_by => pn_last_updated_by,
1787 p_last_update_date => pd_last_update_date,
1788 p_last_update_login => pn_last_update_login);
1789
1790 -- Bug 4929081. Added by Lakshmi Gopalsami
1791 -- Moved the cursor here so that invoice_id can be used
1792 -- in the cursor.
1793 OPEN counter_cur(ln_invoice_id);
1794 FETCH counter_cur INTO counter_tds_dm_v ;
1795 CLOSE counter_cur;
1796
1797 IF upper(lv_regime.regime_code) = 'SERVICE' THEN
1798
1799 FOR i IN cur_distributions_SERVICE LOOP /* added by ssawant for bug 5879769 */
1800
1801
1802 ln_dist_code_combination_id := jai_cmn_rgm_recording_pkg.get_account(
1803 p_regime_id => pn_regime_id,
1804 p_organization_type => jai_constants.orgn_type_io,/* added by ssawant for bug 5879769 */
1805 p_organization_id => i.party_id,/* added by ssawant for bug 5879769 */
1806 p_location_id => i.location_id,/* added by ssawant for bug 5879769 */
1807 p_tax_type => i.tax_type,
1808 p_account_name => jai_constants.liability);
1809
1810 IF ln_dist_code_combination_id IS NULL THEN
1811 pv_process_flag := 'EE';
1812 pv_process_message := 'There is no account defined for AP Invoice creation. Can''t proceed';
1813 goto MAIN_EXIT;
1814 END IF;
1815
1816 IF i.balance_amount <> 0 THEN
1817
1818 counter_tds_dm_v := counter_tds_dm_v + 1;
1819
1820 jai_ap_utils_pkg.insert_ap_inv_lines_interface(
1821 p_jai_source => 'SETTLEMENT',
1822 p_invoice_id => ln_invoice_id,
1823 p_invoice_line_id => ln_invoice_line_id,
1824 p_line_number => counter_tds_dm_v,
1825 p_line_type_lookup_code => 'ITEM',
1826 p_amount => ROUND(i.balance_amount,ln_precision),
1827 p_accounting_date => v_open_gl_date,
1828 p_description => lv_regime.description||' Liability Payment for Tax Type '||i.tax_type||' of Service Type '||i.service_type_code, /*4245365*//* added by ssawant for bug 5879769 . Added service_type_code*/
1829 p_dist_code_combination_id => ln_dist_code_combination_id,
1830 p_created_by => pn_created_by,
1831 p_creation_date => pd_creation_date,
1832 p_last_updated_by => pn_last_updated_by,
1833 p_last_update_date => pd_last_update_date,
1834 p_last_update_login => pn_last_update_login);
1835
1836 END IF;
1837
1838 END LOOP;
1839
1840 ELSIF upper(lv_regime.regime_code) = 'VAT' THEN
1841
1842 FOR i IN cur_distributions_VAT LOOP
1843
1844 /*start additions by vkaranam for bug#12706846*/
1845 ln_line_amount:=0;
1846 ln_Recovery_vat :=0;
1847 open cur_recovervat_fromorg(i.party_id,i.location_id,i.tax_type);
1848 fetch cur_recovervat_fromorg into ln_Recovery_vat;
1849 close cur_recovervat_fromorg;
1850
1851 ln_line_amount := nvl(i.balance_amount,0) - nvl(ln_Recovery_vat,0);
1852
1853
1854
1855 /* added for bug 8657720 */
1856 IF i.tax_type='VAT REVERSAL' THEN
1857 i.tax_type:='VALUE ADDED TAX';
1858 END IF;
1859 /* end for bug 8657720 */
1860
1861
1862 ln_dist_code_combination_id := jai_cmn_rgm_recording_pkg.get_account(
1863 p_regime_id => pn_regime_id,
1864 p_organization_type => jai_constants.orgn_type_io,
1865 p_organization_id => i.party_id,
1866 p_location_id => i.location_id,
1867 p_tax_type => i.tax_type,
1868 p_account_name => jai_constants.liability);
1869
1870
1871 IF ln_dist_code_combination_id IS NULL THEN
1872 pv_process_flag := 'EE';
1873 pv_process_message := 'There is no account defined for AP Invoice creation. Can''t proceed';
1874 goto MAIN_EXIT;
1875 END IF;
1876
1877 -- IF i.balance_amount <> 0 THEN 12706846
1878 if ln_line_amount <>0
1879 then
1880
1881 counter_tds_dm_v := counter_tds_dm_v + 1;
1882
1883 jai_ap_utils_pkg.insert_ap_inv_lines_interface(
1884 p_jai_source => 'SETTLEMENT',
1885 p_invoice_id => ln_invoice_id,
1886 p_invoice_line_id => ln_invoice_line_id,
1887 p_line_number => counter_tds_dm_v,
1888 p_line_type_lookup_code => 'ITEM',
1889 -- p_amount => ROUND(i.balance_amount,ln_precision),
1890 p_amount => ROUND(ln_line_amount,ln_precision),
1891 p_accounting_date => v_open_gl_date,
1892 p_description => lv_regime.description||' Liability Payment for Organization:'||i.party_id||'Location:'||i.location_id||' Tax Type: '||i.tax_type, /*4245365*/
1893 p_dist_code_combination_id => ln_dist_code_combination_id,
1894 p_created_by => pn_created_by,
1895 p_creation_date => pd_creation_date,
1896 p_last_updated_by => pn_last_updated_by,
1897 p_last_update_date => pd_last_update_date,
1898 p_last_update_login => pn_last_update_login);
1899
1900 END IF;
1901 END LOOP;
1902
1903 /*Added By CSahoo, BUG#5631784*/
1904 ELSIF upper(lv_regime.regime_code) = jai_constants.tcs_regime THEN
1905
1906 FOR i IN cur_distributions_TCS LOOP
1907
1908 /*Start Additions by mmurtuza for bug12641455*/
1909 ln_line_amount := 0;
1910 ln_recovery_tcs := 0;
1911
1912 OPEN cur_recovertcs_fromorg(i.party_id, i.location_id, i.tax_type);
1913 FETCH cur_recovertcs_fromorg INTO ln_recovery_tcs;
1914 CLOSE cur_recovertcs_fromorg;
1915
1916 ln_line_amount := nvl(i.balance_amount, 0) -nvl(ln_recovery_tcs, 0);
1917
1918 /*End Additions by mmurtuza for bug12641455*/
1919
1920 ln_dist_code_combination_id := jai_cmn_rgm_recording_pkg.get_account(
1921 p_regime_id => pn_regime_id,
1922 p_organization_type => jai_constants.orgn_type_io,
1923 p_organization_id => i.party_id,
1924 p_location_id => i.location_id,
1925 p_tax_type => i.tax_type,
1926 p_account_name => jai_constants.liability);
1927
1928
1929 IF ln_dist_code_combination_id IS NULL THEN
1930 pv_process_flag := 'EE';
1931 pv_process_message := pn_regime_id||'There is no account defined for AP Invoice creation. Can''t proceed';
1932 goto MAIN_EXIT;
1933 END IF;
1934
1935 IF i.balance_amount <> 0 THEN
1936
1937 counter_tds_dm_v := counter_tds_dm_v + 1;
1938
1939 jai_ap_utils_pkg.insert_ap_inv_lines_interface(
1940 p_jai_source => 'SETTLEMENT',
1941 p_invoice_id => ln_invoice_id,
1942 p_invoice_line_id => ln_invoice_line_id,
1943 p_line_number => counter_tds_dm_v,
1944 p_line_type_lookup_code => 'ITEM',
1945 p_amount => ROUND(i.balance_amount,ln_precision),
1946 p_accounting_date => v_open_gl_date,
1947 p_description => lv_regime.description||' Liability Payment for Organization:'||i.party_id||'Location:'||i.location_id||' Tax Type: '||i.tax_type,
1948 p_dist_code_combination_id => ln_dist_code_combination_id,
1949 p_created_by => pn_created_by,
1950 p_creation_date => pd_creation_date,
1951 p_last_updated_by => pn_last_updated_by,
1952 p_last_update_date => pd_last_update_date,
1953 p_last_update_login => pn_last_update_login);
1954
1955 END IF;
1956 END LOOP;
1957 END IF;
1958 /*The following condition would never be met and hence not tested. This may not be correct as well
1959 This is the case where the amount paid is more than the amount to be settled and the
1960 following code does the proportioning of the excess amount to the differnt tax types
1961 */
1962 IF pn_invoice_amount > pn_calculated_amount THEN
1963
1964 IF upper(lv_regime.regime_code) = 'SERVICE' THEN /*4245365*/
1965
1966 FOR j in cur_tax_types('TAX_TYPES') LOOP--rchandan for bug#4428980
1967 IF cur_tax_types%ROWCOUNT = 1 THEN
1968 lv_tax_type1 := j.tax_type;
1969 END IF;
1970
1971 IF cur_tax_types%ROWCOUNT = 2 THEN
1972 lv_tax_type2 := j.tax_type;
1973 ln_rate := j.rate;
1974 END IF;
1975 END LOOP;
1976
1977 IF ln_rate IS NOT NULL THEN
1978 ln_amount2 := ROUND((pn_invoice_amount - pn_calculated_amount)*(ln_rate/(100+ln_rate)),ln_precision);
1979 END IF;
1980
1981 ln_amount1 := pn_invoice_amount - pn_calculated_amount - NVL(ln_amount2,0);
1982
1983 IF ln_amount1 <> 0 THEN
1984
1985
1986 ln_dist_code_combination_id := jai_cmn_rgm_recording_pkg.get_account(
1987 p_regime_id => pn_regime_id,
1988 p_organization_type => jai_constants.orgn_type_ou,
1989 p_organization_id => pn_org_id,
1990 p_location_id => NULL,
1991 p_tax_type => lv_tax_type1,
1992 p_account_name => jai_constants.liability);
1993
1994 IF ln_dist_code_combination_id IS NULL THEN
1995 pv_process_flag := 'EE';
1996 pv_process_message := 'There is no account defined for AP Invoice creation. Cannot proceed';
1997 goto MAIN_EXIT;
1998 END IF;
1999
2000 counter_tds_dm_v := counter_tds_dm_v + 1;
2001
2002 jai_ap_utils_pkg.insert_ap_inv_lines_interface(
2003 p_jai_source => 'SETTLEMENT',
2004 p_invoice_id => ln_invoice_id,
2005 p_invoice_line_id => ln_invoice_line_id,
2006 p_line_number => counter_tds_dm_v,
2007 p_line_type_lookup_code => 'ITEM',
2008 p_amount => ln_amount1,
2009 p_accounting_date => v_open_gl_date,
2010 p_description => 'Service Tax Excess Payment for Tax Type '||lv_tax_type1,
2011 p_dist_code_combination_id => ln_dist_code_combination_id,
2012 p_created_by => pn_created_by,
2013 p_creation_date => pd_creation_date,
2014 p_last_updated_by => pn_last_updated_by,
2015 p_last_update_date => pd_last_update_date,
2016 p_last_update_login => pn_last_update_login);
2017
2018 END IF;
2019
2020 IF ln_amount2 <> 0 THEN
2021 ln_dist_code_combination_id := jai_cmn_rgm_recording_pkg.get_account(
2022 p_regime_id => pn_regime_id,
2023 p_organization_type => jai_constants.orgn_type_ou,
2024 p_organization_id => pn_org_id,
2025 p_location_id => NULL,
2026 p_tax_type => lv_tax_type2,
2027 p_account_name => jai_constants.liability);
2028
2029 IF ln_dist_code_combination_id IS NULL THEN
2030 pv_process_flag := 'EE';
2031 pv_process_message := 'There is no account defined for AP Invoice creation. Cannot proceed';
2032 goto MAIN_EXIT;
2033 END IF;
2034
2035 counter_tds_dm_v := counter_tds_dm_v + 1;
2036
2037 jai_ap_utils_pkg.insert_ap_inv_lines_interface(
2038 p_jai_source => 'SETTLEMENT',
2039 p_invoice_id => ln_invoice_id,
2040 p_invoice_line_id => ln_invoice_line_id,
2041 p_line_number => counter_tds_dm_v,
2042 p_line_type_lookup_code => 'ITEM',
2043 p_amount => ln_amount2,
2044 p_accounting_date => v_open_gl_date,
2045 p_description => 'Service Tax Excess Payment for Tax Type '||lv_tax_type2,
2046 p_dist_code_combination_id => ln_dist_code_combination_id,
2047 p_created_by => pn_created_by,
2048 p_creation_date => pd_creation_date,
2049 p_last_updated_by => pn_last_updated_by,
2050 p_last_update_date => pd_last_update_date,
2051 p_last_update_login => pn_last_update_login);
2052
2053 END IF;
2054
2055 ELSIF lv_regime.regime_code = 'VAT' THEN
2056
2057 FOR j in cur_tax_types('TAX_TYPES') LOOP--rchandan for bug#4428980
2058
2059 lv_tax_type := j.tax_type;
2060 ln_rate := j.rate;
2061
2062 IF ln_rate IS NOT NULL THEN
2063 ln_amount := ROUND((pn_invoice_amount - pn_calculated_amount)*(ln_rate/(100+ln_rate)),ln_precision);
2064 END IF;
2065
2066
2067 IF nvl(ln_amount,0) <> 0 THEN
2068 ln_dist_code_combination_id := jai_cmn_rgm_recording_pkg.get_account(
2069 p_regime_id => pn_regime_id,
2070 p_organization_type => jai_constants.orgn_type_io,
2071 p_organization_id => org_io_rec.party_id,
2072 p_location_id => org_io_rec.location_id,
2073 p_tax_type => lv_tax_type,
2074 p_account_name => jai_constants.liability);
2075
2076 IF ln_dist_code_combination_id IS NULL THEN
2077 pv_process_flag := 'EE';
2078 pv_process_message := 'There is no account defined for AP Invoice creation. Cannot proceed';
2079 goto MAIN_EXIT;
2080 END IF;
2081
2082 counter_tds_dm_v := counter_tds_dm_v + 1;
2083
2084 jai_ap_utils_pkg.insert_ap_inv_lines_interface(
2085 p_jai_source => 'SETTLEMENT',
2086 p_invoice_id => ln_invoice_id,
2087 p_invoice_line_id => ln_invoice_line_id,
2088 p_line_number => counter_tds_dm_v,
2089 p_line_type_lookup_code => 'ITEM',
2090 p_amount => ln_amount,
2091 p_accounting_date => v_open_gl_date,
2092 p_description => 'Value Added Tax Excess Payment for Tax Type '||lv_tax_type,
2093 p_dist_code_combination_id => ln_dist_code_combination_id,
2094 p_created_by => pn_created_by,
2095 p_creation_date => pd_creation_date,
2096 p_last_updated_by => pn_last_updated_by,
2097 p_last_update_date => pd_last_update_date,
2098 p_last_update_login => pn_last_update_login);
2099
2100 END IF;
2101
2102 END LOOP;
2103
2104
2105
2106 END IF; ---regime_code
2107
2108 END IF;
2109
2110 -- bug 4929081. Added by Lakshmi Gopalsami
2111 -- Removed the select and created cursor.
2112
2113 OPEN cur_inv_exists(ln_invoice_id);
2114 FETCH cur_inv_exists INTO lv_inv_exists;
2115 CLOSE cur_inv_exists;
2116
2117 IF lv_inv_exists = 'Y' THEN
2118 req_id := Fnd_Request.submit_request(
2119 'SQLAP',
2120 'APXIIMPT',
2121 'Localization Payables Open Interface Import',
2122 '',
2123 FALSE,
2124 /* Bug 4774647. Added by Lakshmi Gopalsami
2125 * Passed operating unit also as this parameter has been
2126 * added by base .
2127 */
2128 '',
2129 /* Bug 5359044. Added by Lakshmi Gopalsami
2130 * Changed the p_source from 'EXTERNAL'
2131 * to 'INDIA TAX SETTLEMENT INVOICES'
2132 */
2133 /* Bug 5373747. Added by Lakshmi Gopalsami
2134 * As per the discussion with AP Team changing the source
2135 * as 'INDIA TAX SETTLEMENT'
2136 */
2137 'INDIA TAX SETTLEMENT',
2138 '',
2139 'EXTERNAL'||TO_CHAR(TRUNC(SYSDATE)),
2140 '',
2141 '',
2142 '',
2143 'Y',
2144 'N',
2145 'N',
2146 'N',
2147 1000,
2148 pn_created_by,
2149 pd_creation_date);
2150 END IF;
2151
2152 pv_system_invoice_no := lv_invoice_num;
2153
2154 <<MAIN_EXIT>>
2155 NULL;
2156 EXCEPTION
2157 WHEN OTHERS THEN
2158 pv_process_flag := 'UE';
2159 pv_process_message := SUBSTR(SQLERRM,1,200);
2160 END create_invoice;
2161
2162 FUNCTION get_last_settlement_date
2163 (pn_org_id IN jai_rgm_stl_balances.party_id%TYPE,
2164 /* Bug 5096787. Added by Lakshmi Gopalsami */
2165 pn_regime_id IN jai_rgm_settlements.regime_id%TYPE DEFAULT NULL,
2166 pv_reverse_charge_flag IN jai_rgm_settlements.reverse_charge_flag%TYPE DEFAULT NULL--Added by Qiong for reverse charge settlement
2167 )
2168 RETURN DATE
2169 IS
2170 CURSOR c_last_settlement_date
2171 IS
2172 SELECT MAX(jbal.settlement_date)
2173 FROM JAI_RGM_STL_BALANCES jbal,jai_rgm_settlements jstl --bug 8974544
2174 WHERE jbal.settlement_id = jstl.settlement_id --bug 8974544
2175 AND NVL(jstl.reverse_charge_flag,'N') = NVL(pv_reverse_charge_flag,'N')--Added by Qiong for reverse charge settlment
2176 AND jstl.regime_id = pn_regime_id
2177 and party_id = pn_org_id;
2178
2179 ld_last_settlement_date jai_rgm_stl_balances.settlement_date%TYPE;
2180
2181 BEGIN
2182 OPEN c_last_settlement_date;
2183 FETCH c_last_settlement_date INTO ld_last_settlement_date;
2184 CLOSE c_last_settlement_date;
2185
2186 RETURN ld_last_settlement_date;
2187
2188 END get_last_settlement_date;
2189
2190 FUNCTION get_last_settlement_date(pn_regime_id IN jai_rgm_settlements.regime_id%type,
2191 pn_org_id IN jai_rgm_stl_balances.party_id%TYPE,
2192 pn_location_id IN jai_rgm_stl_balances.location_id%TYPE,
2193 pv_reverse_charge_flag IN jai_rgm_settlements.reverse_charge_flag%type DEFAULT NULL--Added by Qiong for reverse charge settle
2194 )
2195 RETURN DATE
2196 IS
2197 CURSOR c_last_settlement_date
2198 IS
2199 SELECT MAX(jbal.settlement_date)
2200 FROM JAI_RGM_STL_BALANCES jbal,jai_rgm_settlements jstl
2201 WHERE jbal.settlement_id = jstl.settlement_id
2202 AND jstl.regime_id = pn_regime_id
2203 AND party_id = pn_org_id
2204 AND location_id = pn_location_id
2205 AND NVL(jstl.reverse_charge_flag,'N') = NVL(pv_reverse_charge_flag,'N');--Added by Qiong for reverse charge settle
2206
2207 ld_last_settlement_date jai_rgm_stl_balances.settlement_date%TYPE;
2208
2209 BEGIN
2210 OPEN c_last_settlement_date;
2211 FETCH c_last_settlement_date INTO ld_last_settlement_date;
2212 CLOSE c_last_settlement_date;
2213
2214 RETURN ld_last_settlement_date;
2215
2216 END get_last_settlement_date;
2217
2218
2219 PROCEDURE get_last_balance_amount(pn_org_id IN jai_rgm_stl_balances.party_id%TYPE,
2220 pv_tax_type IN jai_rgm_stl_balances.tax_type%TYPE,
2221 pn_debit_amount OUT NOCOPY jai_rgm_stl_balances.debit_balance%TYPE,
2222 pn_credit_amount OUT NOCOPY jai_rgm_stl_balances.credit_balance%TYPE
2223 )
2224 IS
2225
2226 /*ssawant : comenting the below cursor and redefining it for bug 5662296*/
2227 /*
2228 CURSOR c_last_settlement_balance
2229 IS
2230 SELECT debit_balance, credit_balance
2231 FROM JAI_RGM_STL_BALANCES
2232 WHERE party_id = pn_org_id
2233 AND tax_type = pv_tax_type
2234 AND settlement_date = (SELECT MAX(settlement_date)
2235 FROM JAI_RGM_STL_BALANCES
2236 WHERE party_id = pn_org_id
2237 AND tax_type = pv_tax_type);
2238
2239 */
2240
2241 /*cursor added for bug 5662296*/
2242 CURSOR c_last_settlement_balance
2243 IS
2244 SELECT debit_balance, credit_balance
2245 FROM JAI_RGM_STL_BALANCES
2246 WHERE party_id = pn_org_id
2247 AND tax_type = pv_tax_type
2248 AND settlement_id = (SELECT MAX(settlement_id)
2249 FROM JAI_RGM_STL_BALANCES
2250 WHERE party_id = pn_org_id
2251 AND tax_type = pv_tax_type);
2252
2253 /* Added by Ramananda for bug#4407165 */
2254 lv_object_name CONSTANT VARCHAR2(61) := 'jai_cmn_rgm_settlement_pkg.get_last_balance_amount';
2255
2256 BEGIN
2257 OPEN c_last_settlement_balance;
2258 FETCH c_last_settlement_balance INTO pn_debit_amount, pn_credit_amount;
2259 CLOSE c_last_settlement_balance;
2260
2261
2262 /* Added by Ramananda for bug#4407165 */
2263 EXCEPTION
2264 WHEN OTHERS THEN
2265 pn_debit_amount := null;
2266 pn_credit_amount := null;
2267 FND_MESSAGE.SET_NAME('JA','JAI_EXCEPTION_OCCURED');
2268 FND_MESSAGE.SET_TOKEN('JAI_PROCESS_MSG', lv_object_name ||'. Err:'||sqlerrm );
2269 app_exception.raise_exception;
2270
2271 END get_last_balance_amount;
2272
2273 PROCEDURE get_last_balance_amount(pn_regime_id IN jai_rgm_settlements.regime_id%type,
2274 pn_org_id IN jai_rgm_stl_balances.party_id%TYPE,
2275 pn_location_id IN jai_rgm_stl_balances.location_id%TYPE,
2276 pv_tax_type IN jai_rgm_stl_balances.tax_type%TYPE,
2277 pn_debit_amount OUT NOCOPY jai_rgm_stl_balances.debit_balance%TYPE,
2278 pn_credit_amount OUT NOCOPY jai_rgm_stl_balances.credit_balance%TYPE,
2279 pv_service_type_code IN jai_rgm_stl_balances.service_type_code%TYPE DEFAULT NULL /* added by ssawant for bug 5879769 */
2280 )
2281 IS
2282 CURSOR c_last_settlement_balance
2283 IS
2284 SELECT sum(debit_balance), sum(credit_balance) /* added sum by ssawant for bug 5879769 */
2285 FROM JAI_RGM_STL_BALANCES
2286 WHERE party_id = pn_org_id
2287 AND location_id = pn_location_id
2288 AND tax_type = pv_tax_type
2289 -- AND nvl(service_type_code,'-999') = nvl(pv_service_type_code, '-999' ) /* added by ssawant for bug 5879769 */
2290 AND nvl(service_type_code,'-999') = nvl(pv_service_type_code,nvl(service_type_code,'-999') ) /* added nvl for bug#13865856*/
2291 AND settlement_id = (SELECT MAX(jbal.settlement_id)
2292 FROM JAI_RGM_STL_BALANCES jbal,
2293 jai_rgm_settlements jstl
2294 WHERE jbal.settlement_id = jstl.settlement_id
2295 AND jstl.regime_id = pn_regime_id
2296 AND party_id = pn_org_id
2297 AND location_id = pn_location_id
2298 -- AND nvl(service_type_code,'-999') = nvl(pv_service_type_code, '-999' ) /* added by ssawant for bug 5879769 */
2299 AND nvl(service_type_code,'-999') = nvl(pv_service_type_code,nvl(service_type_code,'-999') ) /* added nvl for bug#13865856*/
2300 AND tax_type = pv_tax_type);
2301
2302 /* Added by Ramananda for bug#4407165 */
2303 lv_object_name CONSTANT VARCHAR2(61) := 'jai_cmn_rgm_settlement_pkg.get_last_balance_amount';
2304
2305 BEGIN
2306 OPEN c_last_settlement_balance;
2307 FETCH c_last_settlement_balance INTO pn_debit_amount, pn_credit_amount;
2308 CLOSE c_last_settlement_balance;
2309
2310 /* Added by Ramananda for bug#4407165 */
2311 EXCEPTION
2312 WHEN OTHERS THEN
2313 pn_debit_amount := null;
2314 pn_credit_amount := null;
2315 FND_MESSAGE.SET_NAME('JA','JAI_EXCEPTION_OCCURED');
2316 FND_MESSAGE.SET_TOKEN('JAI_PROCESS_MSG', lv_object_name ||'. Err:'||sqlerrm );
2317 app_exception.raise_exception;
2318
2319 END get_last_balance_amount;
2320
2321
2322
2323 PROCEDURE register_entry( pn_regime_id IN NUMBER,
2324 pn_settlement_id IN NUMBER,
2325 pd_transaction_date IN DATE,
2326 pv_process_flag OUT NOCOPY VARCHAR2,
2327 pv_process_message OUT NOCOPY VARCHAR2)
2328 IS
2329
2330 /* Added by Ramananda for bug#4407165 */
2331 lv_object_name CONSTANT VARCHAR2(61) := 'jai_cmn_rgm_settlement_pkg.register_entry';
2332
2333 CURSOR cur_distributions IS
2334 SELECT tax_type ,
2335 SUM(debit_balance) debit ,
2336 SUM(credit_balance) credit,
2337 NVL(SUM(debit_balance),0) - NVL(SUM(credit_balance),0) balance_amount,
2338 party_id ,
2339 party_type ,
2340 location_id ,
2341 service_type_code /* added by ssawant for bug 5879769 */
2342 FROM jai_rgm_stl_balances
2343 WHERE settlement_id = pn_settlement_id
2344 GROUP BY tax_type, party_type, party_id,location_id,service_type_code /* added by ssawant for bug 5879769 */
2345 HAVING NVL(SUM(debit_balance),0) - NVL(SUM(credit_balance),0) > 0;
2346
2347 CURSOR cur_settlement IS
2348 SELECT *
2349 FROM jai_rgm_settlements
2350 WHERE settlement_id = pn_settlement_id;
2351
2352 CURSOR cur_tax_types(p_reg_type jai_rgm_registrations.registration_type%TYPE ) IS --rchandan for bug#4428980
2353 SELECT attribute_sequence, attribute_code tax_type, RATE
2354 FROM JAI_RGM_REGISTRATIONS
2355 WHERE regime_id = pn_regime_id
2356 AND registration_type = p_reg_type --rchandan for bug#4428980
2357 ORDER BY 1 ASC;
2358
2359 CURSOR cur_vendor_org_id(c_vendor_id po_vendor_sites_all.vendor_id%TYPE,
2360 c_vendor_site_id po_vendor_sites_all.vendor_site_id%TYPE)
2361 IS
2362 SELECT org_id
2363 FROM po_vendor_sites_all
2364 WHERE vendor_id = c_vendor_id
2365 AND vendor_site_id = c_vendor_site_id;
2366
2367 CURSOR cur_regime_code IS /* 4245365*/
2368 SELECT regime_code,description
2369 FROM JAI_RGM_DEFINITIONS
2370 WHERE regime_id = pn_regime_id;
2371
2372 CURSOR cur_org_io IS
2373 SELECT party_id,location_id
2374 FROM jai_rgm_stl_balances
2375 WHERE settlement_id = pn_settlement_id
2376 GROUP BY party_id,location_id
2377 HAVING sum(debit_balance) - sum(credit_balance) > 0;
2378
2379 /*SELECT organization_id,location_id
2380 FROM JAI_RGM_ORG_REGNS_V
2381 WHERE regime_code = 'VAT'
2382 AND rownum = 1; */
2383
2384 rec_settlement cur_settlement%ROWTYPE;
2385
2386 ln_repository_id jai_rgm_trx_records.repository_id%TYPE;
2387
2388 lv_tax_type1 jai_rgm_stl_balances.tax_type%TYPE;
2389 lv_tax_type2 jai_rgm_stl_balances.tax_type%TYPE;
2390 ln_amount1 jai_rgm_stl_balances.debit_balance%TYPE;
2391 ln_amount2 jai_rgm_stl_balances.debit_balance%TYPE;
2392 ln_rate JAI_RGM_REGISTRATIONS.rate%TYPE;
2393 ln_org_id jai_rgm_stl_balances.party_id%TYPE;
2394 ln_amount jai_rgm_stl_balances.debit_balance%TYPE;
2395 lv_tax_type jai_rgm_stl_balances.tax_type%TYPE;
2396
2397 ln_discounted_amount NUMBER;
2398 lv_regime cur_regime_code%rowtype ;/* 4245365*/
2399 org_io_rec cur_org_io%ROWTYPE;
2400 ln_credit_amount NUMBER;
2401 ln_debit_amount NUMBER;
2402 ln_charge_accounting_id jai_rgm_trx_records.charge_account_id%type;
2403
2404 BEGIN
2405
2406 pv_process_flag := 'SS';
2407
2408 OPEN cur_settlement;
2409 FETCH cur_settlement INTO rec_settlement;
2410 CLOSE cur_settlement;
2411
2412 OPEN cur_regime_code; /* 4245365*/
2413 FETCH cur_regime_code INTO lv_regime;
2414 CLOSE cur_regime_code;
2415
2416 FOR i in cur_distributions LOOP
2417
2418 IF lv_regime.regime_code = 'SERVICE' THEN
2419
2420 jai_cmn_rgm_recording_pkg.insert_repository_entry(
2421 p_repository_id => ln_repository_id,
2422 p_regime_id => pn_regime_id,
2423 p_tax_type => i.tax_type,
2424 p_organization_type => jai_constants.orgn_type_io, /* added by ssawant for bug 5879769 */
2425 p_organization_id => i.party_id,
2426 p_location_id => i.location_id , /* added by ssawant for bug 5879769 */
2427 p_source => jai_constants.source_settle_in,
2428 p_source_trx_type => 'Invoice Payment',
2429 p_source_table_name => 'JAI_RGM_SETTLEMENTS',
2430 p_source_document_id => pn_settlement_id,
2431 p_transaction_date => pd_transaction_date,/* +1 is removed by ssawant for bug 5662296 */
2432 p_account_name => NULL,
2433 p_charge_account_id => NULL,
2434 p_balancing_account_id => NULL,
2435 p_amount => i.balance_amount,
2436 p_discounted_amount => ln_discounted_amount,
2437 p_assessable_value => NULL,
2438 p_tax_rate => NULL,
2439 p_reference_id => NULL,
2440 p_batch_id => NULL,
2441 p_called_from => 'JAIRGMSP',
2442 p_accntg_required_flag => jai_constants.no,
2443 p_process_flag => pv_process_flag,
2444 p_process_message => pv_process_message,
2445 p_accounting_date => pd_transaction_date,
2446 p_currency_code => jai_constants.func_curr, --File.Sql.35 Cbabu
2447 p_service_type_code => i.service_type_code /* added by ssawant for bug 5879769 */
2448 );
2449
2450
2451 IF pv_process_flag <> 'SS' THEN
2452 goto MAIN_EXIT;
2453 END IF;
2454
2455 UPDATE jai_rgm_trx_records
2456 SET settlement_id = pn_settlement_id
2457 WHERE repository_id = ln_repository_id;
2458
2459 ELSIF lv_regime.regime_code = 'VAT' THEN
2460
2461 ln_credit_amount := i.balance_amount;
2462 ln_debit_amount := NULL;
2463
2464
2465 ln_charge_accounting_id :=
2466 jai_cmn_rgm_recording_pkg.get_account(p_regime_id => pn_regime_id,
2467 p_organization_type => jai_constants.orgn_type_io,
2468 p_organization_id => i.party_id,
2469 p_location_id => i.location_id,
2470 p_tax_type => i.tax_type,
2471 p_account_name => jai_constants.recovery);
2472
2473 jai_cmn_rgm_recording_pkg.insert_vat_repository_entry(
2474 pn_repository_id => ln_repository_id,
2475 pn_regime_id => pn_regime_id,
2476 pv_tax_type => i.tax_type,
2477 pv_organization_type => jai_constants.orgn_type_io,
2478 pn_organization_id => i.party_id,
2479 pn_location_id => i.location_id,
2480 pv_source => jai_constants.source_settle_in,
2481 pv_source_trx_type => 'Invoice Payment',
2482 pv_source_table_name => 'JAI_RGM_SETTLEMENTS',
2483 pn_source_id => pn_settlement_id,
2484 pd_transaction_date => pd_transaction_date,/* +1 is removed by ssawant for bug 5662296 */
2485 pv_account_name => jai_constants.recovery,
2486 pn_charge_account_id => ln_charge_accounting_id,
2487 pn_balancing_account_id => NULL,
2488 pn_credit_amount => ln_credit_amount,
2489 pn_debit_amount => ln_debit_amount,
2490 pn_assessable_value => NULL,
2491 pn_tax_rate => NULL,
2492 pn_reference_id => NULL,
2493 pn_batch_id => NULL,
2494 pn_inv_organization_id => i.party_id,
2495 pv_invoice_no => NULL,
2496 pv_called_from => 'JAIRGMSP',
2497 pv_process_flag => pv_process_flag,
2498 pv_process_message => pv_process_message,
2499 pd_invoice_date => NULL
2500 );
2501
2502 END IF;
2503
2504 IF pv_process_flag <> 'SS' THEN
2505 goto MAIN_EXIT;
2506 END IF;
2507
2508 UPDATE jai_rgm_trx_records
2509 SET settlement_id = pn_settlement_id
2510 WHERE repository_id = ln_repository_id;
2511 END LOOP;
2512
2513 /*The following condition would never be met and hence not tested. This may not be correct as well*/
2514 IF rec_settlement.payment_amount > rec_settlement.calculated_amount*-1 THEN
2515
2516 IF lv_regime.regime_code = 'SERVICE' THEN
2517
2518 FOR j in cur_tax_types('TAX_TYPES') LOOP --rchandan for bug#4428980
2519
2520 IF cur_tax_types%ROWCOUNT = 1 THEN
2521 lv_tax_type1 := j.tax_type;
2522 END IF;
2523
2524 IF cur_tax_types%ROWCOUNT = 2 THEN
2525 lv_tax_type2 := j.tax_type;
2526 ln_rate := j.rate;
2527 END IF;
2528 END LOOP;
2529
2530 IF ln_rate IS NOT NULL THEN
2531 ln_amount2 := ROUND((rec_settlement.payment_amount - rec_settlement.calculated_amount)*(ln_rate/(100+ln_rate)),2);
2532 END IF;
2533
2534 ln_amount1 := rec_settlement.payment_amount - rec_settlement.calculated_amount - NVL(ln_amount2,0);
2535
2536 OPEN cur_vendor_org_id(rec_settlement.tax_authority_id, rec_settlement.tax_authority_site_id);
2537 FETCH cur_vendor_org_id INTO ln_org_id;
2538 CLOSE cur_vendor_org_id;
2539
2540 IF NVL(ln_amount1,0) <> 0 THEN
2541 jai_cmn_rgm_recording_pkg.insert_repository_entry(
2542 p_repository_id => ln_repository_id,
2543 p_regime_id => pn_regime_id,
2544 p_tax_type => lv_tax_type1,
2545 p_organization_type => jai_constants.orgn_type_ou,
2546 p_organization_id => ln_org_id,
2547 p_location_id => NULL,
2548 p_source => jai_constants.source_settle_in,
2549 p_source_trx_type => 'Invoice Payment',
2550 p_source_table_name => 'JAI_RGM_SETTLEMENTS',
2551 p_source_document_id => pn_settlement_id,
2552 p_transaction_date => pd_transaction_date,/* +1 is removed by ssawant for bug 5662296 */
2553 p_account_name => NULL,
2554 p_charge_account_id => NULL,
2555 p_balancing_account_id => NULL,
2556 p_amount => ln_amount1,
2557 p_discounted_amount => ln_discounted_amount,
2558 p_assessable_value => NULL,
2559 p_tax_rate => NULL,
2560 p_reference_id => NULL,
2561 p_batch_id => NULL,
2562 p_called_from => 'JAIRGMSP',
2563 p_accntg_required_flag => jai_constants.no,
2564 p_process_flag => pv_process_flag,
2565 p_process_message => pv_process_message,
2566 p_accounting_date => pd_transaction_date
2567 , p_currency_code => jai_constants.func_curr --File.Sql.35 Cbabu
2568 );
2569 IF pv_process_flag <> 'SS' THEN
2570 goto MAIN_EXIT;
2571 END IF;
2572
2573 ELSIF NVL(ln_amount2,0) <> 0 THEN
2574
2575 jai_cmn_rgm_recording_pkg.insert_repository_entry(
2576 p_repository_id => ln_repository_id,
2577 p_regime_id => pn_regime_id,
2578 p_tax_type => lv_tax_type2,
2579 p_organization_type => jai_constants.orgn_type_ou,
2580 p_organization_id => ln_org_id,
2581 p_location_id => NULL,
2582 p_source => jai_constants.source_settle_in,
2583 p_source_trx_type => 'Invoice Payment',
2584 p_source_table_name => 'JAI_RGM_SETTLEMENTS',
2585 p_source_document_id => pn_settlement_id,
2586 p_transaction_date => pd_transaction_date, /* +1 is removed by ssawant for bug 5662296 */
2587 p_account_name => NULL,
2588 p_charge_account_id => NULL,
2589 p_balancing_account_id => NULL,
2590 p_amount => ln_amount2,
2591 p_discounted_amount => ln_discounted_amount,
2592 p_assessable_value => NULL,
2593 p_tax_rate => NULL,
2594 p_reference_id => NULL,
2595 p_batch_id => NULL,
2596 p_called_from => 'JAIRGMSP',
2597 p_accntg_required_flag => jai_constants.no,
2598 p_process_flag => pv_process_flag,
2599 p_process_message => pv_process_message,
2600 p_accounting_date => pd_transaction_date
2601 , p_currency_code => jai_constants.func_curr --File.Sql.35 Cbabu
2602 );
2603 IF pv_process_flag <> 'SS' THEN
2604 goto MAIN_EXIT;
2605 END IF;
2606
2607 END IF;
2608
2609 ELSIF lv_regime.regime_code = 'VAT' THEN
2610
2611 FOR j in cur_tax_types('TAX_TYPES') LOOP --rchandan for bug#4428980
2612
2613 lv_tax_type := j.tax_type;
2614 ln_rate := j.rate;
2615
2616
2617 IF ln_rate IS NOT NULL THEN
2618 ln_amount := ROUND((rec_settlement.payment_amount - rec_settlement.calculated_amount)*(ln_rate/(100+ln_rate)),2);
2619 END IF;
2620
2621 OPEN cur_org_io;
2622 FETCH cur_org_io INTO org_io_rec;
2623 CLOSE cur_org_io;
2624
2625 IF nvl(ln_amount,0) <> 0 THEN
2626
2627 ln_credit_amount := ln_amount;
2628 ln_debit_amount := NULL;
2629 jai_cmn_rgm_recording_pkg.insert_vat_repository_entry(
2630 pn_repository_id => ln_repository_id,
2631 pn_regime_id => pn_regime_id,
2632 pv_tax_type => lv_tax_type,
2633 pv_organization_type => jai_constants.orgn_type_io,
2634 pn_organization_id => org_io_rec.party_id,
2635 pn_location_id => org_io_rec.location_id,
2636 pv_source => jai_constants.source_settle_in,
2637 pv_source_trx_type => 'Invoice Payment',
2638 pv_source_table_name => 'JAI_RGM_SETTLEMENTS',
2639 pn_source_id => pn_settlement_id,
2640 pd_transaction_date => pd_transaction_date, /* +1 is removed by ssawant for bug 5662296 */
2641 pv_account_name => NULL,
2642 pn_charge_account_id => NULL,
2643 pn_balancing_account_id => NULL,
2644 pn_credit_amount => ln_credit_amount,
2645 pn_debit_amount => ln_debit_amount,
2646 pn_assessable_value => NULL,
2647 pn_tax_rate => NULL,
2648 pn_reference_id => NULL,
2649 pn_batch_id => NULL,
2650 pn_inv_organization_id => org_io_rec.party_id,
2651 pv_invoice_no => NULL,
2652 pv_called_from => 'JAIRGMSP',
2653 pv_process_flag => pv_process_flag,
2654 pv_process_message => pv_process_message,
2655 pd_invoice_date => NULL
2656 );
2657
2658 IF pv_process_flag <> 'SS' THEN
2659 goto MAIN_EXIT;
2660 END IF;
2661
2662
2663 END IF;
2664
2665 END LOOP;
2666
2667 END IF;
2668
2669 END IF;
2670 <<MAIN_EXIT>>
2671 null;
2672
2673 EXCEPTION
2674 WHEN OTHERS THEN
2675 pv_process_flag := 'UE';
2676 pv_process_message := SUBSTR(SQLERRM,1,200);
2677 END register_entry;
2678
2679 /*
2680 ||The following function addded by rchandan for bug#6835541
2681 ||This function is used for VAT settlement where the user has the flexibility of
2682 || of doing settlement at either registartion or organization or organization-location level
2683 */
2684 FUNCTION get_last_settlement_date(pn_regime_id IN NUMBER,
2685 pn_regn_no IN VARCHAR2,
2686 pn_organization_id IN NUMBER,
2687 pn_location_id IN NUMBER)
2688 RETURN DATE
2689 IS
2690 CURSOR c_last_settlement_date
2691 IS
2692 SELECT MAX(jbal.settlement_date)
2693 FROM JAI_RGM_STL_BALANCES jbal,jai_rgm_settlements jstl
2694 WHERE jbal.settlement_id = jstl.settlement_id
2695 AND jstl.regime_id = pn_regime_id
2696 AND jstl.primary_registration_no = pn_regn_no
2697 AND jbal.party_id = nvl(pn_organization_id,jbal.party_id)
2698 AND jbal.location_id = nvl(pn_location_id,jbal.location_id);
2699 --Added by Eric Ma for bug 8333082,8671217 on Dec-19-2009,begin
2700 -------------------------------------------------------------------
2701 CURSOR c_last_reg_level_Settlement IS
2702 select settlement_id from
2703 (
2704 select jstl.party_id,jstl.location_id,jstl.settlement_id from
2705 jai_rgm_stl_balances jstl,
2706 jai_rgm_Settlements jrs,
2707 JAI_RGM_DEFINITIONS jr
2708 WHERE jstl.settlement_id = jrs.settlement_id
2709 AND jrs.regime_id = jr.regime_id
2710 AND jr.regime_code = 'VAT'
2711 AND jrs.primary_registration_no = pn_regn_no -- 12996230
2712 group by jstl.party_id,jstl.location_id,jstl.settlement_id
2713 )group by settlement_id
2714 having count(*) >1
2715 order by settlement_id desc;
2716
2717 CURSOR c_settlement_date(cp_settlement_id jai_rgm_stl_balances.settlement_id%TYPE) is
2718 SELECT settlement_date FROM
2719 jai_rgm_stl_balances where settlement_id = cp_settlement_id
2720 and rownum=1;
2721
2722 l_last_reg_settlement jai_rgm_stl_balances.settlement_id%TYPE;
2723 -----------------------------------------------------
2724 --Added by Eric Ma for bug 8333082,8671217 on Dec-19-2009,end
2725
2726 ld_settlement_date date;
2727
2728 BEGIN
2729
2730 --Added by Eric Ma for bug 8333082,8671217 on Dec-19-2009,begin
2731 -----------------------------------------------------
2732 IF ( pn_organization_id IS NULL AND pn_location_id IS NULL ) THEN
2733
2734 OPEN c_last_reg_level_Settlement;
2735 FETCH c_last_reg_level_Settlement INTO l_last_reg_settlement;
2736 CLOSE c_last_reg_level_Settlement;
2737
2738 IF l_last_reg_settlement IS NOT NULL THEN
2739
2740 OPEN c_settlement_date(l_last_reg_settlement);
2741 FETCH c_settlement_date INTO ld_settlement_date;
2742 CLOSE c_settlement_date;
2743
2744 return ld_settlement_date;
2745
2746 END IF;
2747
2748 END IF;
2749 -----------------------------------------------------
2750 --Added by Eric Ma for bug 8333082,8671217 on Dec-19-2009,end
2751 OPEN c_last_settlement_date;
2752 FETCH c_last_settlement_date INTO ld_settlement_date;
2753 CLOSE c_last_settlement_date;
2754
2755 return ld_settlement_date;
2756
2757 end get_last_settlement_date;
2758
2759
2760 END jai_cmn_rgm_settlement_pkg;