[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.16.12010000.2 2008/10/17 09:48:21 jmeena 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 14-OCT-2008 JMEENA for bug#7445742
80 Incorporate the changes of bug#6835541
81
82
83
84 --------------------------------------------------------------------------------------*/
85
86 PROCEDURE insert_into_vat_register(
87 p_repository_id OUT NOCOPY NUMBER ,
88 p_regime_id NUMBER ,
89 p_from_party_type VARCHAR2 ,
90 p_from_party_id NUMBER ,
91 p_from_locn_id NUMBER ,
92 p_from_tax_type VARCHAR2 ,
93 p_from_trx_amount NUMBER ,
94 p_to_party_type VARCHAR2 ,
95 p_to_party_id NUMBER ,
96 p_to_tax_type VARCHAR2 ,
97 p_to_trx_amount IN OUT NOCOPY NUMBER ,
98 p_to_locn_id NUMBER ,
99 p_called_from VARCHAR2 ,
100 p_trx_date DATE ,
101 p_acct_req VARCHAR2 ,
102 p_source VARCHAR2 ,
103 P_SOURCE_TRX_TYPE VARCHAR2 ,
104 P_SOURCE_TABLE_NAME VARCHAR2 ,
105 p_source_doc_id NUMBER ,
106 p_settlement_id NUMBER ,
107 p_reference_id NUMBER ,
108 p_process_flag OUT NOCOPY VARCHAR2 ,
109 p_process_message OUT NOCOPY VARCHAR2 ,
110 p_accounting_date Date
111 )
112 IS
113 ln_repository_id number;
114 lv_process_status varchar2(30);
115 lv_process_message VARCHAR2(1996);
116
117 lv_source varchar2(30);
118 lv_regime_code JAI_RGM_DEFINITIONS.regime_code%TYPE;
119 ln_charge_accounting_id NUMBER;
120 ln_balance_accounting_id NUMBER;
121 ln_credit_amount NUMBER;
122 ln_debit_amount NUMBER;
123 lv_statement NUMBER;
124
125 BEGIN
126 lv_source := jai_constants.source_settle_out;
127 ln_credit_amount:= NULL; --- these amounts are with respect to repository not w.r.t accounting
128 ln_debit_amount := p_to_trx_amount; --- its is reverse w.r.t accounting
129
130
131 ln_charge_accounting_id := jai_cmn_rgm_recording_pkg.get_account(
132 p_regime_id => p_regime_id,
133 p_organization_type => p_from_party_type,
134 p_organization_id => p_from_party_id,
135 p_location_id => p_from_locn_id,
136 p_tax_type => p_from_tax_type,
137 p_account_name => jai_constants.recovery
138 );
139
140 ln_balance_accounting_id := jai_cmn_rgm_recording_pkg.get_account(
141 p_regime_id => p_regime_id,
142 p_organization_type => p_to_party_type,
143 p_organization_id => p_to_party_id,
144 p_location_id => p_to_locn_id,
145 p_tax_type => p_to_tax_type,
146 p_account_name => jai_constants.recovery
147 );
148
149
150 jai_cmn_rgm_recording_pkg.insert_vat_repository_entry(
151 pn_repository_id => ln_repository_id,
152 pn_regime_id => p_regime_id,
153 pv_tax_type => p_from_tax_type ,
154 pv_organization_type => p_from_party_type,
155 pn_organization_id => p_from_party_id,
156 pn_location_id => p_from_locn_id,
157 pv_source => lv_source,
158 pv_source_trx_type => p_source_trx_type ,
159 pv_source_table_name => p_source_table_name,
160 pn_source_id => p_source_doc_id ,
161 pd_transaction_date => p_trx_date,
162 pv_account_name => jai_constants.recovery,
163 pn_charge_account_id => ln_charge_accounting_id,
164 pn_balancing_account_id => ln_balance_accounting_id,
165 pn_credit_amount => ln_credit_amount,
166 pn_debit_amount => ln_debit_amount,
167 pn_assessable_value => NULL,
168 pn_tax_rate => NULL,
169 pn_reference_id => NULL,
170 pn_batch_id => NULL,
171 pn_inv_organization_id => p_from_party_id,
172 pv_invoice_no => NULL,
173 pv_called_from => p_called_from,
174 pv_process_flag => p_process_flag,
175 pv_process_message => p_process_message,
176 pd_invoice_date => NULL
177 );
178
179
180
181 IF p_process_flag <> 'SS' THEN
182 rollback;
183 return;
184 END IF;
185 p_repository_id := ln_repository_id;
186
187
188 jai_cmn_rgm_recording_pkg.do_vat_accounting(
189 pn_regime_id => p_regime_id,
190 pn_repository_id => ln_repository_id,
191 pv_organization_type => p_from_party_type,
192 pn_organization_id => p_from_party_id,
193 pd_accounting_date => trunc(sysdate),
194 pd_transaction_date => p_trx_date,
195 pn_credit_amount => ln_debit_amount,
196 pn_debit_amount => ln_credit_amount,
197 pn_credit_ccid => ln_charge_accounting_id,
198 pn_debit_ccid => ln_balance_accounting_id,
199 pv_called_from => p_called_from,
200 pv_process_flag => p_process_flag,
201 pv_process_message => p_process_message,
202 pv_tax_type => p_from_tax_type,
203 pv_source => lv_source,
204 pv_source_trx_type => p_source_trx_type,
205 pv_source_table_name => p_source_table_name,
206 pn_source_id => p_source_doc_id,
207 pv_reference_name => jai_constants.repository_name,
208 pn_reference_id => ln_repository_id
209 );
210
211 IF p_process_flag <> 'SS' THEN
212 rollback;
213 return;
214 END IF;
215
216
217 /*for destination*/
218
219
220 lv_source := jai_constants.source_settle_in ;
221 ln_credit_amount := p_from_trx_amount; --- these amounts are with respect to repository not w.r.t accounting
222 ln_debit_amount := NULL; --- its is reverse w.r.t accounting
223
224 ln_charge_accounting_id := jai_cmn_rgm_recording_pkg.get_account(
225 p_regime_id => p_regime_id,
226 p_organization_type => p_to_party_type,
227 p_organization_id => p_to_party_id,
228 p_location_id => p_to_locn_id,
229 p_tax_type => p_to_tax_type,
230 p_account_name => jai_constants.recovery
231 );
232
233 ln_balance_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
243 jai_cmn_rgm_recording_pkg.insert_vat_repository_entry(
244 pn_repository_id => ln_repository_id,
245 pn_regime_id => p_regime_id,
246 pv_tax_type => p_to_tax_type ,
247 pv_organization_type => p_to_party_type,
248 pn_organization_id => p_to_party_id,
249 pn_location_id => p_to_locn_id,
250 pv_source => lv_source,
251 pv_source_trx_type => p_source_trx_type ,
252 pv_source_table_name => p_source_table_name,
253 pn_source_id => p_source_doc_id ,
254 pd_transaction_date => p_trx_date,
255 pv_account_name => jai_constants.recovery,
256 pn_charge_account_id => ln_charge_accounting_id,
257 pn_balancing_account_id => ln_balance_accounting_id,
258 pn_credit_amount => ln_credit_amount,
259 pn_debit_amount => ln_debit_amount,
260 pn_assessable_value => NULL,
261 pn_tax_rate => NULL,
262 pn_reference_id => NULL,
263 pn_batch_id => NULL,
264 pn_inv_organization_id => p_to_party_id,
265 pv_invoice_no => NULL,
266 pv_called_from => p_called_from,
267 pv_process_flag => p_process_flag,
268 pv_process_message => p_process_message,
269 pd_invoice_date => NULL
270 );
271
272
273
274 IF p_process_flag <> 'SS' THEN
275 rollback;
276 return;
277 END IF;
278 p_repository_id := ln_repository_id;
279
280
281 jai_cmn_rgm_recording_pkg.do_vat_accounting(
282 pn_regime_id => p_regime_id,
283 pn_repository_id => ln_repository_id,
284 pv_organization_type => p_to_party_type,
285 pn_organization_id => p_to_party_id,
286 pd_accounting_date => trunc(sysdate),
287 pd_transaction_date => p_trx_date,
288 pn_credit_amount => ln_debit_amount,
289 pn_debit_amount => ln_credit_amount,
290 pn_credit_ccid => ln_balance_accounting_id,
291 pn_debit_ccid => ln_charge_accounting_id,
292 pv_called_from => p_called_from,
293 pv_process_flag => p_process_flag,
294 pv_process_message => p_process_message,
295 pv_tax_type => p_to_tax_type,
296 pv_source => lv_source,
297 pv_source_trx_type => p_source_trx_type,
298 pv_source_table_name => p_source_table_name,
299 pn_source_id => p_source_doc_id,
300 pv_reference_name => jai_constants.repository_name,
301 pn_reference_id => ln_repository_id
302 );
303
304
305 IF p_process_flag <> 'SS' THEN
306 rollback;
307 return;
308 END IF;
309
310 commit;
311
312 p_process_flag := 'SS';
313
314 exception
315 when others then
316 p_process_flag := 'UE';
317 p_process_message := 'Error in procedure - insert_records_into_register ' || substr(sqlerrm,1,1500);
318
319 end insert_into_vat_register;
320
321
322 PROCEDURE transfer_balance( pn_settlement_id IN jai_rgm_stl_balances.settlement_id%TYPE,
323 pv_process_flag OUT NOCOPY VARCHAR2,
324 pv_process_message OUT NOCOPY VARCHAR2)
325 IS
326 CURSOR c_debit_balance(lv_tax_type jai_rgm_stl_balances.tax_type%TYPE) IS
327 SELECT NVL(debit_balance,0) - NVL(credit_balance,0) debit_balance,
328 party_id,
329 location_id,
330 service_type_code , /* added by ssawant for bug 5879769 */
331 party_type,
332 rowid
333 FROM jai_rgm_stl_balances
334 WHERE settlement_id = pn_settlement_id
335 AND tax_type = lv_tax_type
336 AND NVL(debit_balance,0) - NVL(credit_balance,0) > 0
337 ORDER BY 1 desc;
338
339 CURSOR c_credit_balance(lv_tax_type jai_rgm_stl_balances.tax_type%TYPE) IS
340 SELECT NVL(credit_balance,0) - NVL(debit_balance,0) credit_balance,
341 party_id,
342 location_id,
343 service_type_code ,/* added by ssawant for bug 5879769 */
344 party_type,
345 rowid
346 FROM jai_rgm_stl_balances
347 WHERE settlement_id = pn_settlement_id
348 AND tax_type = lv_tax_type
349 AND NVL(credit_balance,0) - NVL(debit_balance,0) > 0
350 ORDER BY 1 desc;
351
352 CURSOR c_debit_balance_trx(lv_tax_type jai_rgm_stl_balances.tax_type%TYPE,
353 ln_party_id jai_rgm_stl_balances.party_id%TYPE,
354 ln_location_id jai_rgm_stl_balances.location_id%TYPE,
355 lv_party_type jai_rgm_stl_balances.party_type%TYPE
356 ) IS
357 SELECT (NVL(credit_amount,0) - NVL(debit_amount,0) - NVL(settled_amount,0))*-1 debit_balance, organization_id party_id, rowid
358 FROM jai_rgm_trx_records
359 WHERE tax_type = lv_tax_type
360 AND organization_id = ln_party_id
361 AND nvl(location_id,-999) = nvl(ln_location_id,-999)
362 AND organization_type = lv_party_type
363 AND settlement_id <= pn_settlement_id
364 AND NVL(settled_flag,'N') <> 'Y'
365 AND NVL(credit_amount,0) - NVL(debit_amount,0) - NVL(settled_amount,0) < 0
366 ORDER BY 1 desc;
367
368 CURSOR c_credit_balance_trx(lv_tax_type jai_rgm_stl_balances.tax_type%TYPE,
369 ln_party_id jai_rgm_stl_balances.party_id%TYPE,
370 ln_location_id jai_rgm_stl_balances.location_id%TYPE,
371 lv_party_type jai_rgm_stl_balances.party_type%TYPE
372 ) IS
373 SELECT NVL(credit_amount,0) - NVL(debit_amount,0) - NVL(settled_amount,0) credit_balance, organization_id party_id, rowid
374 FROM jai_rgm_trx_records
375 WHERE tax_type = lv_tax_type
376 AND organization_id = ln_party_id
377 AND nvl(location_id,-999) = nvl(ln_location_id,-999)/*rchandan for Service Type FP*/
378 AND organization_type = lv_party_type
379 AND settlement_id <= pn_settlement_id
380 AND NVL(settled_flag,'N') <> 'Y'
381 AND NVL(credit_amount,0) - NVL(debit_amount,0) - NVL(settled_amount,0) > 0
382 ORDER BY 1 desc;
383
384
385 CURSOR cur_regime_id IS
386 SELECT regime_id
387 FROM jai_rgm_settlements
388 WHERE settlement_id = pn_settlement_id;
389
390 lv_regime_id jai_rgm_settlements.regime_id%type;
391
392 cursor cur_regime_code is /*Ravi */
393 select regime_code
394 from JAI_RGM_DEFINITIONS
395 where regime_id = lv_regime_id;
396
397 cursor cur_dist_detail IS -- This is ditribution detail sequence
398 SELECT JAI_RGM_DIS_DES_TAXES_S.nextval
399 FROM DUAL;
400
401
402 /* added by ssawant for bug 6020629 */
403 CURSOR c_acct_balances IS
404 SELECT *
405 FROM jai_rgm_stl_balances
406 WHERE NVL(debit_balance,0) >= 0
407 AND NVL(credit_balance,0) >= 0
408 AND settlement_id = pn_settlement_id;
409
410 /*CURSOR cur_regno IS
411 SELECT primary_registration_no
412 FROM jai_rgm_settlements
413 WHERE settlement_id = pn_settlement_id;*/
414 /*rchandan for bug#5642053..commented the above cursor and defined the following cursor*/
415
416 CURSOR cur_stl_details IS
417 SELECT jstl.primary_registration_no,
418 jbal.party_type ,
419 jbal.party_id ,
420 jbal.location_id
421 FROM jai_rgm_stl_balances jbal,jai_rgm_settlements jstl
422 WHERE jbal.settlement_id = jstl.settlement_id
423 AND jbal.settlement_id = pn_settlement_id;
424
425
426 /* CURSOR cur_inv_payment(lp_regn_no VARCHAR2)
427 IS
428 -- || This cursor is used to get the total invoice amount paid
429 -- || when the last settlement was made
430 SELECT sum(credit_amount)
431 FROM jai_rgm_trx_records
432 WHERE regime_primary_regno = lp_regn_no
433 AND source_trx_type = 'Invoice Payment'
434 AND transaction_date = ( select max(settlement_date) + 1
435 from jai_rgm_stl_balances a
436 where 2 = (select count(distinct jbal.settlement_date)
437 from jai_rgm_stl_balances jbal,jai_rgm_settlements jstl
438 where jbal.settlement_id = jstl.settlement_id
439 and jstl.primary_registration_no = lp_regn_no and jbal.settlement_date >= a.settlement_date));
440
441
442 CURSOR cur_balances(lp_org_id number,lp_tax_type varchar2,lp_regn_no varchar2) --4287372
443 IS
444 --|| This cursor is used to retrieve the sum of credit and debit balances as on
445 --|| last settlement date for the given registration number
446 SELECT sum(jbal.credit_balance) credit_balance,sum(jbal.debit_balance) debit_balance
447 FROM jai_rgm_stl_balances jbal,jai_rgm_settlements jstl
448 WHERE jbal.settlement_id = jstl.settlement_id
449 AND jstl.primary_registration_no = lp_regn_no
450 AND jbal.tax_type = lp_tax_type
451 AND jbal.party_id = lp_org_id
452 AND jstl.settlement_date = ( select max(settlement_date)
453 from jai_rgm_stl_balances a
454 where 2 = (select count(distinct jbal.settlement_date)
455 from jai_rgm_stl_balances jbal,jai_rgm_settlements jstl
456 where jbal.settlement_id = jstl.settlement_id
457 and jstl.primary_registration_no = lp_regn_no and jbal.settlement_date >= a.settlement_date ));
458
459 */
460
461 /*rchandan for bug#5642053. Commented the above cursors and redefined them as follows*/
462
463 CURSOR cur_inv_payment(cp_regn_no VARCHAR2,
464 cp_org_type VARCHAR2,
465 cp_org_id NUMBER,
466 cp_location_id NUMBER)
467 IS
468 /*
469 || This cursor is used to get the total invoice amount paid
470 || when the last settlement was made
471 */
472 SELECT sum(credit_amount)
473 FROM jai_rgm_trx_records
474 WHERE source_trx_type = 'Invoice Payment'
475 AND settlement_id = ( SELECT MAX(jbal.settlement_id)
476 FROM jai_rgm_stl_balances jbal,jai_rgm_settlements jstl
477 WHERE jbal.settlement_id = jstl.settlement_id
478 AND jstl.primary_registration_no = cp_regn_no
479 AND jbal.party_type = cp_org_type
480 AND jbal.party_id = cp_org_id
481 AND nvl(jbal.location_id,-999) = nvl(cp_location_id,-999)
482 AND jbal.settlement_id <> pn_settlement_id /*This clause is used to exclude the current settlement*/
483 );
484
485 CURSOR cur_balances(cp_regn_no VARCHAR2 ,
486 cp_org_type VARCHAR2 ,
487 cp_org_id NUMBER ,
488 cp_location_id NUMBER ,
489 cp_tax_type VARCHAR2 )
490 IS
491 /*
492 || This cursor is used to retrieve the sum of credit and debit balances as on
493 || last settlement date for the given registration number,organization and location grouped at the tax type
494 */
495 SELECT sum(credit_balance) credit_balance,sum(debit_balance) debit_balance
496 FROM jai_rgm_stl_balances
497 WHERE settlement_id = ( SELECT MAX(jbal.settlement_id)
498 FROM jai_rgm_stl_balances jbal,jai_rgm_settlements jstl
499 WHERE jbal.settlement_id = jstl.settlement_id
500 AND jstl.primary_registration_no = cp_regn_no
501 AND jbal.party_type = cp_org_type
502 AND jbal.party_id = cp_org_id
503 AND nvl(jbal.location_id,-999) = nvl(cp_location_id,-999)
504 AND jbal.settlement_id <> pn_settlement_id/*This clause is used to exclude the current settlement*/
505 )
506 AND tax_type = cp_tax_type;
507
508
509
510 ln_debit_cnt NUMBER;
511 ln_credit_cnt NUMBER;
512 ln_credit_balance NUMBER;
513 ln_transfer_amt NUMBER;
514 ln_repository_id NUMBER;
515 lv_regime_code JAI_RGM_DEFINITIONS.regime_code%type;/* Ravi*/
516 lv_statement NUMBER;
517 ln_dist_dtl_id NUMBER;
518 ln_acct_amount NUMBER; /* added by ssawant for bug 6020629 */
519 ln_charge_accounting_id jai_rgm_trx_records.charge_account_id%type; /* added by ssawant for bug 6020629 */
520 lv_organization_type VARCHAR2(10); /* added by ssawant for bug 6020629 */
521 ln_invoice_amount jai_rgm_trx_records.credit_amount%type ; /* added by ssawant for bug 6020629 */
522 rec_balances cur_balances%ROWTYPE;/* added by ssawant for bug 6020629 */
523 lv_regn_no jai_rgm_settlements.primary_registration_no%type;
524 /*rchandan for bug#5642053 start*/
525 ln_organization_id jai_rgm_stl_balances.party_id%TYPE;
526 ln_location_id jai_rgm_stl_balances.location_id%TYPE;
527 lv_org_type jai_rgm_stl_balances.party_type%TYPE;
528 /*rchandan for bug#5642053 end*/
529
530 BEGIN
531 /* */
532 -- #****************************************************************************************************************************************************************************************
533 -- #
534 -- # Change History -
535 -- # 1. 27-Jan-2005 Sanjikum for Bug #4059774 Version #115.0
536 -- # New Package created for Service Tax settlement
537
538 -- # Future Dependencies For the release Of this Object:-
539 -- # (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/
540 -- # A datamodel change )
541
542 --==============================================================================================================
543 -- # --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
544 -- # Current Version Current Bug Dependent Files Version Author Date Remarks
545 -- # Of File On Bug/Patchset Dependent On
546 -- # jai_rgm_settlement_pkg_b.sql
547 -- # --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
548 -- # 115.0 4068930 4146708 Sanjikum 27/01/2005 This file is part of Service tax enhancement. So
549 -- # dependent on Service Tax and Education Cess Enhancement
550 -- # 115.1 4245365 4245089 rchandan 17/03/2005 Changes made to implement VAT
551 -- # 115.2 4245365 4245089 rchandan 20/03/2005 Punching of settlement id in the repository for Invoice Payment happens here from now
552 -- # as this record is not considered while settlement.
553 -- # --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
554 -- # ****************************************************************************************************************************************************************************************
555
556 pv_process_flag := 'SS';
557
558
559 OPEN cur_regime_id;
560 FETCH cur_regime_id INTO lv_regime_id;
561 CLOSE cur_regime_id;
562
563 OPEN cur_regime_code;
564 FETCH cur_regime_code INTO lv_regime_code;
565 CLOSE cur_regime_code;
566 /*rchandan for bug#5642053*/
567 OPEN cur_stl_details;
568 FETCH cur_stl_details INTO lv_regn_no,lv_org_type,ln_organization_id,ln_location_id;
569 CLOSE cur_stl_details;
570
571
572 OPEN cur_inv_payment(lv_regn_no,lv_org_type,ln_organization_id,ln_location_id); /*rchandan for bug#5642053*/
573 FETCH cur_inv_payment INTO ln_invoice_amount;
574 CLOSE cur_inv_payment;
575
576
577 /*start added by ssawant for bug 6020629 */
578 FOR r_acct_balances IN c_acct_balances
579 LOOP
580 ln_acct_amount := 0;
581
582 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
583 FETCH cur_balances INTO rec_balances;
584 CLOSE cur_balances;
585
586 IF NVL(r_acct_balances.debit_balance,0) - NVL(r_acct_balances.credit_balance,0) > 0 THEN
587
588 ln_acct_amount := NVL(r_acct_balances.credit_balance,0);
589
590 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 */
591 /*
592 || If no invoice payment was made at the last settlement and if the credit and debit balances are not
593 || equal in the previous settlement then the previous settled balance is deducted before making
594 || accounting entries
595 */
596 ln_acct_amount := ln_acct_amount - nvl( rec_balances.credit_balance,0);
597 END IF;
598
599 ELSE
600
601 ln_acct_amount := NVL(r_acct_balances.debit_balance,0);
602 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 */
603 /*
604 || If no invoice payment was made at the last settlement and if the credit and debit balances are not
605 || equal in the previous settlement then the previous settled balance is deducted before making an
606 || accounting entries
607 */
608 ln_acct_amount := ln_acct_amount - nvl( rec_balances.debit_balance,0);
609 END IF;
610
611 END IF;
612
613 IF lv_regime_code = jai_constants.service_regime THEN
614 lv_organization_type := jai_constants.orgn_type_io;/* added by ssawant for bug 5879769 */
615 ln_acct_amount := ROUND(ln_acct_amount, jai_constants.service_rgm_rnd_factor);
616 ELSIF lv_regime_code = jai_constants.vat_regime THEN
617 lv_organization_type := jai_constants.orgn_type_io;
618 ln_acct_amount := ROUND(ln_acct_amount, jai_constants.vat_rgm_rnd_factor);
619 END IF;
620
621 IF ln_acct_amount <> 0 THEN /* added by ssawant for bug 6020629 */
622 ln_charge_accounting_id :=
623 jai_cmn_rgm_recording_pkg.get_account(p_regime_id => lv_regime_id,
624 p_organization_type => lv_organization_type,
625 p_organization_id => r_acct_balances.party_id,
626 p_location_id => r_acct_balances.location_id,
627 p_tax_type => r_acct_balances.tax_type,
628 p_account_name => jai_constants.liability);
629
630
631 jai_cmn_rgm_recording_pkg.post_accounting(
632 p_regime_code => lv_regime_code,
633 p_tax_type => r_acct_balances.tax_type,
634 p_organization_type => lv_organization_type,
635 p_organization_id => r_acct_balances.party_id,
636 p_source => jai_constants.source_settle_in,
637 p_source_trx_type => 'Invoice Payment',
638 p_source_table_name => 'JAI_RGM_SETTLEMENTS',
639 p_source_document_id => pn_settlement_id,
640 p_code_combination_id => ln_charge_accounting_id,
641 p_entered_cr => NULL,
642 p_entered_dr => ln_acct_amount,
643 p_accounted_cr => NULL,
644 p_accounted_dr => ln_acct_amount,
645 -- p_accounting_date => SYSDATE, commented by anujsax for bug #6126142
646 p_accounting_date => r_acct_balances.settlement_date, --added by anujsax for Bug#6126142
647 p_transaction_date => r_acct_balances.settlement_date,
648 p_calling_object => 'JAIRGMSP',
649 p_repository_name => jai_constants.repository_name,
650 p_repository_id => NULL,
651 p_reference_name => NULL,
652 p_reference_id => NULL,
653 p_currency_code => jai_constants.func_curr);
654
655 IF pv_process_flag <> 'SS' THEN
656 goto MAIN_EXIT;
657 END IF;
658
659 END IF;
660
661 IF ln_acct_amount <> 0 THEN
662
663 ln_charge_accounting_id :=
664 jai_cmn_rgm_recording_pkg.get_account(p_regime_id => lv_regime_id,
665 p_organization_type => lv_organization_type,
666 p_organization_id => r_acct_balances.party_id,
667 p_location_id => r_acct_balances.location_id,
668 p_tax_type => r_acct_balances.tax_type,
669 p_account_name => jai_constants.recovery);
670
671 jai_cmn_rgm_recording_pkg.post_accounting(
672 p_regime_code => lv_regime_code,
673 p_tax_type => r_acct_balances.tax_type,
674 p_organization_type => lv_organization_type,
675 p_organization_id => r_acct_balances.party_id,
676 p_source => jai_constants.source_settle_in,
677 p_source_trx_type => 'Invoice Payment',
678 p_source_table_name => 'JAI_RGM_SETTLEMENTS',
679 p_source_document_id => pn_settlement_id,
680 p_code_combination_id => ln_charge_accounting_id,
681 p_entered_cr => ln_acct_amount,
682 p_entered_dr => NULL,
683 p_accounted_cr => ln_acct_amount,
684 p_accounted_dr => NULL,
685 -- p_accounting_date => SYSDATE, commented by anujsax for bug #6126142
686 p_accounting_date => r_acct_balances.settlement_date, --added by anujsax for Bug#6126142
687 p_transaction_date => r_acct_balances.settlement_date,
688 p_calling_object => 'JAIRGMSP',
689 p_repository_name => jai_constants.repository_name,
690 p_repository_id => NULL,
691 p_reference_name => NULL,
692 p_reference_id => NULL,
693 p_currency_code => jai_constants.func_curr);
694
695 IF pv_process_flag <> 'SS' THEN
696 goto MAIN_EXIT;
697 END IF;
698
699 END IF;
700
701
702 END LOOP;
703 /*End added by ssawant for bug 6020629 */
704
705
706
707
708 FOR I in (select distinct b.regime_id, b.settlement_date, a.tax_type
709 from jai_rgm_stl_balances a,
710 jai_rgm_settlements b
711 where a.settlement_id = b.settlement_id
712 AND a.settlement_id = pn_settlement_id)
713 LOOP
714 SELECT count(*)
715 INTO ln_debit_cnt
716 FROM jai_rgm_stl_balances
717 WHERE settlement_id = pn_settlement_id
718 AND debit_balance >0;
719
720
721 IF ln_debit_cnt = 0 THEN
722 --There is no Debit balance
723 goto End_loop;
724 END IF;
725
726 SELECT count(*)
727 INTO ln_credit_cnt
728 FROM jai_rgm_stl_balances
729 WHERE settlement_id = pn_settlement_id
730 AND credit_balance >0;
731
732
733 IF ln_credit_cnt = 0 THEN
734 --There is no Credit balance
735 goto End_loop;
736 END IF;
737
738 FOR cur_credit in c_credit_balance(i.tax_type) LOOP
739 ln_credit_balance := cur_credit.credit_balance;
740
741
742 FOR cur_debit in c_debit_balance(i.tax_type) LOOP
743
744
745 IF ln_credit_balance >= cur_debit.debit_balance THEN
746 ln_credit_balance := ln_credit_balance - cur_debit.debit_balance;
747 ln_transfer_amt := cur_debit.debit_balance;
748 ELSE
749 ln_transfer_amt := ln_credit_balance;
750 ln_credit_balance := 0;
751 END IF;
752
753 OPEN cur_regime_id;
754 FETCH cur_regime_id INTO lv_regime_id;
755 CLOSE cur_regime_id;
756
757 OPEN cur_regime_code;
758 FETCH cur_regime_code INTO lv_regime_code;
759 CLOSE cur_regime_code;
760
761
762 IF lv_regime_code = jai_constants.service_regime THEN /* 4245365*/
763
764
765 jai_cmn_rgm_tax_dist_pkg.insert_records_into_register
766 (p_repository_id => ln_repository_id,
767 p_regime_id => i.regime_id,
768 p_from_party_type => cur_credit.party_type,
769 p_from_party_id => cur_credit.party_id,
770 p_from_locn_id => cur_credit.location_id,/* added by ssawant for bug 5879769 */
771 p_from_tax_type => i.tax_type,
772 p_from_service_type => cur_credit.service_type_code,/* added by ssawant for bug 5879769 */
773 p_from_trx_amount => ln_transfer_amt,
774 p_to_party_type => cur_debit.party_type,
775 p_to_party_id => cur_debit.party_id,
776 p_to_locn_id => cur_debit.location_id,/* added by ssawant for bug 5879769 */
777 p_to_tax_type => i.tax_type,
778 p_to_service_type => cur_debit.service_type_code,/* added by ssawant for bug 5879769 */
779 p_to_trx_amount => ln_transfer_amt,
780 p_called_from => 'SETTLEMENT',
781 p_trx_date => i.settlement_date,
782 p_acct_req => jai_constants.yes,
783 p_source => 'SETTLEMENT',
784 p_source_trx_type => 'SETTLEMENT',
785 p_source_table_name => 'JAI_RGM_SETTLEMENTS',
786 p_source_doc_id => pn_settlement_id,
787 p_settlement_id => pn_settlement_id,
788 p_reference_id => NULL,
789 p_process_flag => pv_process_flag,
790 p_process_message => pv_process_message,
791 p_accounting_date => i.settlement_date);
792
793 IF pv_process_flag <> 'SS' THEN
794 goto MAIN_EXIT;
795 END IF;
796
797 ELSIF lv_regime_code = jai_constants.vat_regime THEN /* 4245365*/
798
799
800 OPEN cur_dist_detail;
801 FETCH cur_dist_detail INTO ln_dist_dtl_id;
802 CLOSE cur_dist_detail;
803
804 insert_into_vat_register(p_repository_id => ln_repository_id,
805 p_regime_id => i.regime_id,
806 p_from_party_type => cur_credit.party_type,
807 p_from_party_id => cur_credit.party_id,
808 p_from_locn_id => NULL,
809 p_from_tax_type => i.tax_type,
810 p_from_trx_amount => ln_transfer_amt,
811 p_to_party_type => cur_debit.party_type,
812 p_to_party_id => cur_debit.party_id,
813 p_to_locn_id => cur_debit.location_id,
814 p_to_tax_type => i.tax_type,
815 p_to_trx_amount => ln_transfer_amt,
816 p_called_from => 'SETTLEMENT',
817 p_trx_date => i.settlement_date,
818 p_acct_req => jai_constants.yes,
819 p_source => 'SETTLEMENT',
820 p_source_trx_type => 'SETTLEMENT',
821 p_source_table_name => 'JAI_RGM_SETTLEMENTS',
822 p_source_doc_id => ln_dist_dtl_id,
823 p_settlement_id => pn_settlement_id,
824 p_reference_id => NULL,
825 p_process_flag => pv_process_flag,
826 p_process_message => pv_process_message,
827 p_accounting_date => i.settlement_date);
828
829
830 IF pv_process_flag <> 'SS' THEN
831 goto MAIN_EXIT;
832 END IF;
833
834
835
836 END IF;
837
838
839 update jai_rgm_stl_balances
840 SET debit_balance = debit_balance - ln_transfer_amt
841 WHERE rowid = cur_debit.rowid;
842
843 update jai_rgm_stl_balances
844 SET credit_balance = credit_balance - ln_transfer_amt
845 WHERE rowid = cur_credit.rowid;
846
847 EXIT WHEN ln_credit_balance = 0;
848 END LOOP;
849 END LOOP;
850
851 <<End_loop>>
852 NULL;
853 END LOOP;
854
855 --for each transaction
856 FOR I in (select *
857 from jai_rgm_stl_balances
858 where settlement_id = pn_settlement_id)
859 LOOP
860 IF NVL(i.debit_balance,0) = NVL(i.credit_balance,0) THEN
861 UPDATE jai_rgm_trx_records
862 SET settled_flag = 'Y',
863 settled_amount = NULL
864 WHERE tax_type = i.tax_type
865 AND organization_id = i.party_id
866 AND nvl(location_id,-999) = nvl(i.location_id,-999)
867 AND organization_type = i.party_type
868 AND settlement_id <= pn_settlement_id;
869 ELSE
870
871 SELECT count(*)
872 INTO ln_debit_cnt
873 FROM jai_rgm_trx_records
874 WHERE tax_type = i.tax_type
875 AND organization_id = i.party_id
876 AND nvl(location_id,-999) = nvl(i.location_id,-999)
877 AND organization_type = i.party_type
878 AND settlement_id <= pn_settlement_id
879 AND NVL(settled_flag,'N') <> 'Y'
880 AND NVL(credit_amount,0) - NVL(debit_amount,0) - NVL(settled_amount,0) < 0;
881
882 IF ln_debit_cnt = 0 THEN
883 --There is no Debit balance
884 goto End_loop_txn;
885 END IF;
886
887 SELECT count(*)
888 INTO ln_credit_cnt
889 FROM jai_rgm_trx_records
890 WHERE tax_type = i.tax_type
891 AND organization_id = i.party_id
892 AND nvl(location_id,-999) = nvl(i.location_id,-999)
893 AND organization_type = i.party_type
894 AND settlement_id <= pn_settlement_id
895 AND NVL(settled_flag,'N') <> 'Y'
896 AND NVL(credit_amount,0) - NVL(debit_amount,0) - NVL(settled_amount,0) > 0;
897
898 IF ln_credit_cnt = 0 THEN
899 --There is no Credit balance
900 goto End_loop_txn;
901 END IF;
902
903 FOR cur_credit in c_credit_balance_trx(i.tax_type, i.party_id,i.location_id, i.party_type) LOOP
904 ln_credit_balance := cur_credit.credit_balance;
905
906 FOR cur_debit in c_debit_balance_trx(i.tax_type, i.party_id,i.location_id, i.party_type) LOOP
907 IF ln_credit_balance >= cur_debit.debit_balance THEN
908 ln_credit_balance := ln_credit_balance - cur_debit.debit_balance;
909 ln_transfer_amt := cur_debit.debit_balance;
910 ELSE
911 ln_transfer_amt := ln_credit_balance;
912 ln_credit_balance := 0;
913 END IF;
914
915 UPDATE jai_rgm_trx_records
916 SET settled_amount = NVL(settled_amount,0) - ln_transfer_amt,
917 settled_flag = 'P'
918 WHERE rowid = cur_debit.rowid;
919
920 UPDATE jai_rgm_trx_records
921 SET settled_amount = NVL(settled_amount,0) + ln_transfer_amt,
922 settled_flag = 'P'
923 WHERE rowid = cur_credit.rowid;
924
925 EXIT WHEN ln_credit_balance = 0;
926 END LOOP;
927
928 END LOOP;
929 END IF;
930
931 <<End_loop_txn>>
932
933 UPDATE jai_rgm_trx_records
934 SET settled_flag = 'Y',
935 settled_amount = debit_amount*-1
936 WHERE settlement_id <= pn_settlement_id
937 AND organization_id = i.party_id
938 AND organization_type = i.party_type
939 AND nvl(location_id,-999) = nvl(i.location_id,-999)
940 AND tax_type = i.tax_type
941 AND debit_amount > 0
942 AND debit_amount = settled_amount*-1;
943
944 UPDATE jai_rgm_trx_records
945 SET settled_flag = 'Y',
946 settled_amount = credit_amount
947 WHERE settlement_id <= pn_settlement_id
948 AND organization_id = i.party_id
949 AND nvl(location_id,-999) = nvl(i.location_id,-999)
950 AND organization_type = i.party_type
951 AND tax_type = i.tax_type
952 AND credit_amount > 0
953 AND credit_amount = settled_amount;
954
955 END LOOP;
956
957 <<MAIN_EXIT>>
958 NULL;
959
960 EXCEPTION
961 WHEN OTHERS THEN
962 pv_process_flag := 'UE';
963 pv_process_message := SUBSTR(SQLERRM,1,200);
964 END transfer_balance;
965
966
967 PROCEDURE create_invoice( pn_regime_id IN jai_rgm_settlements.regime_id%TYPE,
968 pn_settlement_id IN jai_rgm_settlements.settlement_id%TYPE,
969 pd_settlement_date IN jai_rgm_settlements.settlement_date%TYPE,
970 pn_vendor_id IN jai_rgm_settlements.tax_authority_id%TYPE,
971 pn_vendor_site_id IN jai_rgm_settlements.tax_authority_site_id%TYPE,
972 pn_calculated_amount IN jai_rgm_settlements.calculated_amount%TYPE,
973 pn_invoice_amount IN jai_rgm_settlements.payment_amount%TYPE,
974 pn_org_id IN jai_rgm_stl_balances.party_id%TYPE,
975 pv_regsitration_no IN jai_rgm_settlements.primary_registration_no%TYPE,
976 pn_created_by IN ap_invoices_interface.created_by%TYPE,
977 pd_creation_date IN ap_invoices_interface.creation_date%TYPE,
978 pn_last_updated_by IN ap_invoices_interface.last_updated_by%TYPE,
979 pd_last_update_date IN ap_invoices_interface.last_update_date%TYPE,
980 pn_last_update_login IN ap_invoices_interface.last_update_login%TYPE,
981 pv_system_invoice_no OUT NOCOPY jai_rgm_settlements.system_invoice_no%TYPE,
982 pv_process_flag OUT NOCOPY VARCHAR2,
983 pv_process_message OUT NOCOPY VARCHAR2)
984 IS
985
986 /* Bug 5243532. Added by Lakshmi Gopalsami
987 * (1) Removed the cursor c_functional_currency which is referring
988 * to hr_operating_units and implemented using caching logic.
989 * (2) Removed cursor cur_currency_precision as the precision
990 * is derived using caching logic.
991 */
992
993 CURSOR for_terms_id(ven_id NUMBER,ven_site_id NUMBER) IS
994 SELECT terms_id,
995 --payment_method_lookup_code, --commented the column by Sanjikum for Bug#4482462
996 pay_group_lookup_code
997 FROM po_vendor_sites_all
998 WHERE vendor_id = pn_vendor_id
999 AND vendor_site_id = pn_vendor_site_id;
1000
1001 CURSOR for_terms_id_2(ven_id NUMBER) IS
1002 SELECT terms_id,
1003 --payment_method_lookup_code, --commented the column by Sanjikum for Bug#4482462
1004 pay_group_lookup_code
1005 FROM po_vendors
1006 WHERE vendor_id = pn_vendor_id;
1007
1008 CURSOR counter_cur(pn_invoice_id ap_invoices_interface.invoice_id%TYPE) IS
1009 SELECT NVL(MAX(line_number),0)
1010 FROM ap_invoice_lines_interface
1011 -- bug 4929081. Added by Lakshmi Gopalsami
1012 WHERE invoice_id = pn_invoice_id;
1013
1014 CURSOR cur_invoice_no IS
1015 SELECT jai_rgm_settlements_s1.NEXTVAL --rchandan for bug#4487676. JAI_RGM_SETTLEMENT_INVOICE_S is replaced with JAI_RGM_SETTLEMENTS_S1
1016 FROM dual;
1017
1018 /* commented the below cursor by ssawant for bug 5879769
1019 CURSOR cur_distributions IS
1020 SELECT tax_type, debit, credit, NVL(debit,0) - NVL(credit,0) balance_amount
1021 FROM JAI_RGM_STL_BALANCES_V
1022 WHERE settlement_id = pn_settlement_id
1023 AND NVL(debit,0) - NVL(credit,0) > 0;
1024 */
1025 /*
1026 || added by ssawant for bug 5879769 . Commented the above cursor and added the following
1027 */
1028 CURSOR cur_distributions_SERVICE
1029 IS
1030 SELECT party_id ,
1031 location_id ,
1032 service_type_code ,
1033 tax_type ,
1034 sum(debit_balance) debit_balance ,
1035 sum(credit_balance) credit_balance,
1036 NVL(sum(debit_balance),0) - NVL(sum(credit_balance),0) balance_amount
1037 FROM JAI_RGM_STL_BALANCES
1038 WHERE settlement_id = pn_settlement_id
1039 GROUP BY party_id,location_id,service_type_code,tax_type
1040 HAVING sum(debit_balance) - sum(credit_balance) > 0 ;
1041
1042
1043 CURSOR cur_distributions_VAT IS
1044 SELECT party_id,location_id,tax_type,
1045 sum(debit_balance) debit_balance, sum(credit_balance) credit_balance,
1046 NVL(sum(debit_balance),0) - NVL(sum(credit_balance),0) balance_amount
1047 FROM JAI_RGM_STL_BALANCES
1048 WHERE settlement_id = pn_settlement_id
1049 GROUP BY party_id,location_id,tax_type
1050 HAVING sum(debit_balance) - sum(credit_balance) > 0 ;
1051
1052 CURSOR cur_distributions_TCS IS /*Added By CSahoo BUG#5631784*/
1053 SELECT party_id,location_id,tax_type,
1054 sum(debit_balance) debit_balance, sum(credit_balance) credit_balance,
1055 NVL(sum(debit_balance),0) - NVL(sum(credit_balance),0) balance_amount
1056 FROM JAI_RGM_STL_BALANCES
1057 WHERE settlement_id = pn_settlement_id
1058 GROUP BY party_id,location_id,tax_type;
1059
1060 CURSOR cur_tax_types(p_reg_type jai_rgm_registrations.registration_type%TYPE )IS --rchandan for bug#4428980
1061 SELECT attribute_sequence, attribute_code tax_type, RATE
1062 FROM JAI_RGM_REGISTRATIONS
1063 WHERE regime_id = pn_regime_id
1064 AND registration_type = p_reg_type--rchandan for bug#4428980
1065 ORDER BY 1 ASC;
1066
1067 CURSOR cur_regime_code IS /* 4245365*/
1068 SELECT regime_code,description
1069 FROM JAI_RGM_DEFINITIONS
1070 WHERE regime_id = pn_regime_id;
1071
1072 CURSOR cur_org_io IS
1073 SELECT party_id,location_id
1074 FROM jai_rgm_stl_balances
1075 WHERE settlement_id = pn_settlement_id
1076 GROUP BY party_id,location_id
1077 HAVING sum(debit_balance) - sum(credit_balance) > 0;
1078
1079 -- Bug 4929081. Added by Lakshmi Gopalsami
1080
1081 CURSOR cur_inv_exists(pn_invoice_id IN ap_invoices_interface.invoice_id%TYPE) IS
1082 SELECT 'Y'
1083 FROM ap_invoices_interface
1084 WHERE invoice_id = pn_invoice_id;
1085
1086
1087 lv_invoice_num ap_invoices_interface.invoice_num%TYPE;
1088 lv_currency_code gl_sets_of_books.currency_code%TYPE;
1089 for_terms_id_rec for_terms_id%ROWTYPE;
1090 counter_tds_dm_v NUMBER;
1091 ln_tmp NUMBER;
1092 ln_dist_code_combination_id ap_invoice_lines_interface.dist_code_combination_id%TYPE;
1093 lv_tax_type jai_rgm_stl_balances.tax_type%TYPE;
1094 lv_tax_type1 jai_rgm_stl_balances.tax_type%TYPE;
1095 lv_tax_type2 jai_rgm_stl_balances.tax_type%TYPE;
1096 ln_amount jai_rgm_stl_balances.debit_balance%TYPE;
1097 ln_amount1 jai_rgm_stl_balances.debit_balance%TYPE;
1098 ln_amount2 jai_rgm_stl_balances.debit_balance%TYPE;
1099 ln_rate JAI_RGM_REGISTRATIONS.rate%TYPE;
1100 v_open_period gl_period_statuses.period_name%type;
1101 v_open_gl_date date;
1102 lv_inv_exists varchar2(1) := 'N' ; -- Bug 4929081
1103 req_id NUMBER;
1104 ln_invoice_amount NUMBER;
1105 ln_precision fnd_currencies.precision%TYPE;
1106 ln_invoice_id NUMBER;
1107 ln_invoice_line_id NUMBER;
1108 lv_regime cur_regime_code%rowtype ;/* 4245365*/
1109 ln_org_id NUMBER;
1110 org_io_rec cur_org_io%ROWTYPE;
1111
1112 /* Bug5243532. Added by Lakshmi Gopalsami
1113 Defined the variable for implementing caching logic.
1114 */
1115 l_func_curr_det jai_plsql_cache_pkg.func_curr_details;
1116
1117 BEGIN
1118
1119 pv_process_flag := 'SS';
1120
1121 OPEN cur_regime_code; /* 4245365*/
1122 FETCH cur_regime_code INTO lv_regime;
1123 CLOSE cur_regime_code;
1124
1125
1126 OPEN cur_invoice_no;
1127 FETCH cur_invoice_no INTO ln_tmp;
1128 CLOSE cur_invoice_no;
1129
1130 lv_invoice_num := upper(lv_regime.regime_code)||'/'||pn_org_id||'/'||ln_tmp; /*4245365*/
1131
1132 /* Bug 5373747. Added by Lakshmi Gopalsami
1133 * Set the policy context before calling AP API
1134 */
1135 mo_global.set_policy_context('S', pn_org_id);
1136
1137 /* Bug 5243532. Added by Lakshmi Gopalsami
1138 * Removed the reference to hr_operating_units
1139 * and implemented using caching logic for getting functional currency
1140 * and precision.
1141 */
1142 l_func_curr_det := jai_plsql_cache_pkg.return_sob_curr
1143 (p_org_id => pn_org_id );
1144
1145 lv_currency_code := l_func_curr_det.currency_code;
1146 ln_precision := l_func_curr_det.precision;
1147
1148 -- Bug 4929081. Added by Lakshmi Gopalsami
1149 -- Moved the cursor after inserting headers.
1150
1151 OPEN for_terms_id(pn_vendor_id,pn_vendor_site_id);
1152 FETCH for_terms_id INTO for_terms_id_rec;
1153 CLOSE for_terms_id;
1154
1155 IF ((for_terms_id_rec.terms_id IS NULL) OR
1156 --(for_terms_id_rec.payment_method_lookup_code IS NULL) OR --commented by Sanjikum for Bug#4482462
1157 (for_terms_id_rec.pay_group_lookup_code IS NULL)
1158 ) THEN
1159
1160 OPEN for_terms_id_2(pn_vendor_id);
1161 FETCH for_terms_id_2 INTO for_terms_id_rec;
1162 CLOSE for_terms_id_2;
1163 END IF;
1164 --commented by anujsax for bug 6126142
1165 -- v_open_period := ap_utilities_pkg.get_current_gl_date(TRUNC(pd_creation_date)
1166 -- ,pn_org_id /* Added by Ramananda for bug#4559828 */
1167 -- );
1168 -- added by anujsax for bug 6126142
1169 v_open_period := ap_utilities_pkg.get_current_gl_date(TRUNC(pd_settlement_date)
1170 ,pn_org_id /* Added by Ramananda for bug#4559828 */
1171 );
1172 --ended by anujsax for bug 6126142
1173 if v_open_period is null then
1174
1175 ap_utilities_pkg.get_open_gl_date (
1176 -- TRUNC(pd_creation_date),/*commented by anujsax for bug#6126142*/
1177 TRUNC(pd_settlement_date),/* addded by anujsax for bug#6126142*/
1178 v_open_period,
1179 v_open_gl_date
1180 ,pn_org_id /* Added by Ramananda for bug#4559828 */
1181 );
1182
1183 if v_open_period is null then
1184 raise_application_error(-20001,'No Open period ... after '||pd_settlement_date);
1185 end if;
1186 else
1187 --commented by anujsax for bug 6126142
1188 -- v_open_gl_date := TRUNC(pd_creation_date);
1189 --added by anujsax for bug 6126142
1190 v_open_gl_date := TRUNC(pd_settlement_date);
1191 --ended by anujsax for bug 6126142
1192 end if;
1193
1194 jai_ap_utils_pkg.insert_ap_inv_interface(
1195 p_jai_source => 'SETTLEMENT',
1196 p_invoice_id => ln_invoice_id,
1197 p_invoice_num => lv_invoice_num,
1198 p_invoice_date => v_open_gl_date,
1199 p_gl_date => v_open_gl_date,
1200 p_vendor_id => pn_vendor_id,
1201 p_vendor_site_id => pn_vendor_site_id,
1202 p_invoice_amount => ROUND(pn_invoice_amount, ln_precision),
1203 p_invoice_currency_code => lv_currency_code,
1204 p_terms_id => for_terms_id_rec.terms_id,
1205 p_description => 'Settlement of '||lv_regime.description||' Liability on '||pd_settlement_date||' for registration no '||pv_regsitration_no, /*4245365*/
1206 /* Bug 5359044. Added by Lakshmi Gopalsami
1207 * Changed the p_source from 'EXTERNAL'
1208 * to 'INDIA TAX SETTLEMENT INVOICES'
1209 */
1210 /* Bug 5373747. Added by Lakshmi Gopalsami
1211 * As per the discussion with AP Team changing the source
1212 * as 'INDIA TAX SETTLEMENT'
1213 */
1214 p_source => 'INDIA TAX SETTLEMENT',
1215 p_voucher_num => lv_invoice_num,
1216 --p_payment_method_lookup_code => for_terms_id_rec.payment_method_lookup_code, --commented by Sanjikum for Bug#4482462
1217 p_pay_group_lookup_code => for_terms_id_rec.pay_group_lookup_code,
1218 p_org_id => pn_org_id,
1219 p_created_by => pn_created_by,
1220 p_creation_date => pd_creation_date,
1221 p_last_updated_by => pn_last_updated_by,
1222 p_last_update_date => pd_last_update_date,
1223 p_last_update_login => pn_last_update_login);
1224
1225 -- Bug 4929081. Added by Lakshmi Gopalsami
1226 -- Moved the cursor here so that invoice_id can be used
1227 -- in the cursor.
1228 OPEN counter_cur(ln_invoice_id);
1229 FETCH counter_cur INTO counter_tds_dm_v ;
1230 CLOSE counter_cur;
1231
1232 IF upper(lv_regime.regime_code) = 'SERVICE' THEN
1233
1234 FOR i IN cur_distributions_SERVICE LOOP /* added by ssawant for bug 5879769 */
1235
1236 ln_dist_code_combination_id := jai_cmn_rgm_recording_pkg.get_account(
1237 p_regime_id => pn_regime_id,
1238 p_organization_type => jai_constants.orgn_type_io,/* added by ssawant for bug 5879769 */
1239 p_organization_id => i.party_id,/* added by ssawant for bug 5879769 */
1240 p_location_id => i.location_id,/* added by ssawant for bug 5879769 */
1241 p_tax_type => i.tax_type,
1242 p_account_name => jai_constants.liability);
1243
1244 IF ln_dist_code_combination_id IS NULL THEN
1245 pv_process_flag := 'EE';
1246 pv_process_message := 'There is no account defined for AP Invoice creation. Can''t proceed';
1247 goto MAIN_EXIT;
1248 END IF;
1249
1250 IF i.balance_amount <> 0 THEN
1251
1252 counter_tds_dm_v := counter_tds_dm_v + 1;
1253
1254 jai_ap_utils_pkg.insert_ap_inv_lines_interface(
1255 p_jai_source => 'SETTLEMENT',
1256 p_invoice_id => ln_invoice_id,
1257 p_invoice_line_id => ln_invoice_line_id,
1258 p_line_number => counter_tds_dm_v,
1259 p_line_type_lookup_code => 'ITEM',
1260 p_amount => ROUND(i.balance_amount,ln_precision),
1261 p_accounting_date => v_open_gl_date,
1262 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*/
1263 p_dist_code_combination_id => ln_dist_code_combination_id,
1264 p_created_by => pn_created_by,
1265 p_creation_date => pd_creation_date,
1266 p_last_updated_by => pn_last_updated_by,
1267 p_last_update_date => pd_last_update_date,
1268 p_last_update_login => pn_last_update_login);
1269
1270 END IF;
1271
1272 END LOOP;
1273
1274 ELSIF upper(lv_regime.regime_code) = 'VAT' THEN
1275
1276 FOR i IN cur_distributions_VAT LOOP
1277
1278 ln_dist_code_combination_id := jai_cmn_rgm_recording_pkg.get_account(
1279 p_regime_id => pn_regime_id,
1280 p_organization_type => jai_constants.orgn_type_io,
1281 p_organization_id => i.party_id,
1282 p_location_id => i.location_id,
1283 p_tax_type => i.tax_type,
1284 p_account_name => jai_constants.liability);
1285
1286
1287 IF ln_dist_code_combination_id IS NULL THEN
1288 pv_process_flag := 'EE';
1289 pv_process_message := 'There is no account defined for AP Invoice creation. Can''t proceed';
1290 goto MAIN_EXIT;
1291 END IF;
1292
1293 IF i.balance_amount <> 0 THEN
1294
1295 counter_tds_dm_v := counter_tds_dm_v + 1;
1296
1297 jai_ap_utils_pkg.insert_ap_inv_lines_interface(
1298 p_jai_source => 'SETTLEMENT',
1299 p_invoice_id => ln_invoice_id,
1300 p_invoice_line_id => ln_invoice_line_id,
1301 p_line_number => counter_tds_dm_v,
1302 p_line_type_lookup_code => 'ITEM',
1303 p_amount => ROUND(i.balance_amount,ln_precision),
1304 p_accounting_date => v_open_gl_date,
1305 p_description => lv_regime.description||' Liability Payment for Organization:'||i.party_id||'Location:'||i.location_id||' Tax Type: '||i.tax_type, /*4245365*/
1306 p_dist_code_combination_id => ln_dist_code_combination_id,
1307 p_created_by => pn_created_by,
1308 p_creation_date => pd_creation_date,
1309 p_last_updated_by => pn_last_updated_by,
1310 p_last_update_date => pd_last_update_date,
1311 p_last_update_login => pn_last_update_login);
1312
1313 END IF;
1314 END LOOP;
1315
1316 /*Added By CSahoo, BUG#5631784*/
1317 ELSIF upper(lv_regime.regime_code) = jai_constants.tcs_regime THEN
1318
1319 FOR i IN cur_distributions_TCS LOOP
1320
1321 ln_dist_code_combination_id := jai_cmn_rgm_recording_pkg.get_account(
1322 p_regime_id => pn_regime_id,
1323 p_organization_type => jai_constants.orgn_type_io,
1324 p_organization_id => i.party_id,
1325 p_location_id => i.location_id,
1326 p_tax_type => i.tax_type,
1327 p_account_name => jai_constants.liability);
1328
1329
1330 IF ln_dist_code_combination_id IS NULL THEN
1331 pv_process_flag := 'EE';
1332 pv_process_message := pn_regime_id||'There is no account defined for AP Invoice creation. Can''t proceed';
1333 goto MAIN_EXIT;
1334 END IF;
1335
1336 IF i.balance_amount <> 0 THEN
1337
1338 counter_tds_dm_v := counter_tds_dm_v + 1;
1339
1340 jai_ap_utils_pkg.insert_ap_inv_lines_interface(
1341 p_jai_source => 'SETTLEMENT',
1342 p_invoice_id => ln_invoice_id,
1343 p_invoice_line_id => ln_invoice_line_id,
1344 p_line_number => counter_tds_dm_v,
1345 p_line_type_lookup_code => 'ITEM',
1346 p_amount => ROUND(i.balance_amount,ln_precision),
1347 p_accounting_date => v_open_gl_date,
1348 p_description => lv_regime.description||' Liability Payment for Organization:'||i.party_id||'Location:'||i.location_id||' Tax Type: '||i.tax_type,
1349 p_dist_code_combination_id => ln_dist_code_combination_id,
1350 p_created_by => pn_created_by,
1351 p_creation_date => pd_creation_date,
1352 p_last_updated_by => pn_last_updated_by,
1353 p_last_update_date => pd_last_update_date,
1354 p_last_update_login => pn_last_update_login);
1355
1356 END IF;
1357 END LOOP;
1358 END IF;
1359 /*The following condition would never be met and hence not tested. This may not be correct as well
1360 This is the case where the amount paid is more than the amount to be settled and the
1361 following code does the proportioning of the excess amount to the differnt tax types
1362 */
1363 IF pn_invoice_amount > pn_calculated_amount THEN
1364
1365 IF upper(lv_regime.regime_code) = 'SERVICE' THEN /*4245365*/
1366
1367 FOR j in cur_tax_types('TAX_TYPES') LOOP--rchandan for bug#4428980
1368 IF cur_tax_types%ROWCOUNT = 1 THEN
1369 lv_tax_type1 := j.tax_type;
1370 END IF;
1371
1372 IF cur_tax_types%ROWCOUNT = 2 THEN
1373 lv_tax_type2 := j.tax_type;
1374 ln_rate := j.rate;
1375 END IF;
1376 END LOOP;
1377
1378 IF ln_rate IS NOT NULL THEN
1379 ln_amount2 := ROUND((pn_invoice_amount - pn_calculated_amount)*(ln_rate/(100+ln_rate)),ln_precision);
1380 END IF;
1381
1382 ln_amount1 := pn_invoice_amount - pn_calculated_amount - NVL(ln_amount2,0);
1383
1384 IF ln_amount1 <> 0 THEN
1385
1386
1387 ln_dist_code_combination_id := jai_cmn_rgm_recording_pkg.get_account(
1388 p_regime_id => pn_regime_id,
1389 p_organization_type => jai_constants.orgn_type_ou,
1390 p_organization_id => pn_org_id,
1391 p_location_id => NULL,
1392 p_tax_type => lv_tax_type1,
1393 p_account_name => jai_constants.liability);
1394
1395 IF ln_dist_code_combination_id IS NULL THEN
1396 pv_process_flag := 'EE';
1397 pv_process_message := 'There is no account defined for AP Invoice creation. Cannot proceed';
1398 goto MAIN_EXIT;
1399 END IF;
1400
1401 counter_tds_dm_v := counter_tds_dm_v + 1;
1402
1403 jai_ap_utils_pkg.insert_ap_inv_lines_interface(
1404 p_jai_source => 'SETTLEMENT',
1405 p_invoice_id => ln_invoice_id,
1406 p_invoice_line_id => ln_invoice_line_id,
1407 p_line_number => counter_tds_dm_v,
1408 p_line_type_lookup_code => 'ITEM',
1409 p_amount => ln_amount1,
1410 p_accounting_date => v_open_gl_date,
1411 p_description => 'Service Tax Excess Payment for Tax Type '||lv_tax_type1,
1412 p_dist_code_combination_id => ln_dist_code_combination_id,
1413 p_created_by => pn_created_by,
1414 p_creation_date => pd_creation_date,
1415 p_last_updated_by => pn_last_updated_by,
1416 p_last_update_date => pd_last_update_date,
1417 p_last_update_login => pn_last_update_login);
1418
1419 END IF;
1420
1421 IF ln_amount2 <> 0 THEN
1422 ln_dist_code_combination_id := jai_cmn_rgm_recording_pkg.get_account(
1423 p_regime_id => pn_regime_id,
1424 p_organization_type => jai_constants.orgn_type_ou,
1425 p_organization_id => pn_org_id,
1426 p_location_id => NULL,
1427 p_tax_type => lv_tax_type2,
1428 p_account_name => jai_constants.liability);
1429
1430 IF ln_dist_code_combination_id IS NULL THEN
1431 pv_process_flag := 'EE';
1432 pv_process_message := 'There is no account defined for AP Invoice creation. Cannot proceed';
1433 goto MAIN_EXIT;
1434 END IF;
1435
1436 counter_tds_dm_v := counter_tds_dm_v + 1;
1437
1438 jai_ap_utils_pkg.insert_ap_inv_lines_interface(
1439 p_jai_source => 'SETTLEMENT',
1440 p_invoice_id => ln_invoice_id,
1441 p_invoice_line_id => ln_invoice_line_id,
1442 p_line_number => counter_tds_dm_v,
1443 p_line_type_lookup_code => 'ITEM',
1444 p_amount => ln_amount2,
1445 p_accounting_date => v_open_gl_date,
1446 p_description => 'Service Tax Excess Payment for Tax Type '||lv_tax_type2,
1447 p_dist_code_combination_id => ln_dist_code_combination_id,
1448 p_created_by => pn_created_by,
1449 p_creation_date => pd_creation_date,
1450 p_last_updated_by => pn_last_updated_by,
1451 p_last_update_date => pd_last_update_date,
1452 p_last_update_login => pn_last_update_login);
1453
1454 END IF;
1455
1456 ELSIF lv_regime.regime_code = 'VAT' THEN
1457
1458 FOR j in cur_tax_types('TAX_TYPES') LOOP--rchandan for bug#4428980
1459
1460 lv_tax_type := j.tax_type;
1461 ln_rate := j.rate;
1462
1463 IF ln_rate IS NOT NULL THEN
1464 ln_amount := ROUND((pn_invoice_amount - pn_calculated_amount)*(ln_rate/(100+ln_rate)),ln_precision);
1465 END IF;
1466
1467
1468 IF nvl(ln_amount,0) <> 0 THEN
1469 ln_dist_code_combination_id := jai_cmn_rgm_recording_pkg.get_account(
1470 p_regime_id => pn_regime_id,
1471 p_organization_type => jai_constants.orgn_type_io,
1472 p_organization_id => org_io_rec.party_id,
1473 p_location_id => org_io_rec.location_id,
1474 p_tax_type => lv_tax_type,
1475 p_account_name => jai_constants.liability);
1476
1477 IF ln_dist_code_combination_id IS NULL THEN
1478 pv_process_flag := 'EE';
1479 pv_process_message := 'There is no account defined for AP Invoice creation. Cannot proceed';
1480 goto MAIN_EXIT;
1481 END IF;
1482
1483 counter_tds_dm_v := counter_tds_dm_v + 1;
1484
1485 jai_ap_utils_pkg.insert_ap_inv_lines_interface(
1486 p_jai_source => 'SETTLEMENT',
1487 p_invoice_id => ln_invoice_id,
1488 p_invoice_line_id => ln_invoice_line_id,
1489 p_line_number => counter_tds_dm_v,
1490 p_line_type_lookup_code => 'ITEM',
1491 p_amount => ln_amount,
1492 p_accounting_date => v_open_gl_date,
1493 p_description => 'Value Added Tax Excess Payment for Tax Type '||lv_tax_type,
1494 p_dist_code_combination_id => ln_dist_code_combination_id,
1495 p_created_by => pn_created_by,
1496 p_creation_date => pd_creation_date,
1497 p_last_updated_by => pn_last_updated_by,
1498 p_last_update_date => pd_last_update_date,
1499 p_last_update_login => pn_last_update_login);
1500
1501 END IF;
1502
1503 END LOOP;
1504
1505
1506
1507 END IF; ---regime_code
1508
1509 END IF;
1510
1511 -- bug 4929081. Added by Lakshmi Gopalsami
1512 -- Removed the select and created cursor.
1513
1514 OPEN cur_inv_exists(ln_invoice_id);
1515 FETCH cur_inv_exists INTO lv_inv_exists;
1516 CLOSE cur_inv_exists;
1517
1518 IF lv_inv_exists = 'Y' THEN
1519 req_id := Fnd_Request.submit_request(
1520 'SQLAP',
1521 'APXIIMPT',
1522 'Localization Payables Open Interface Import',
1523 '',
1524 FALSE,
1525 /* Bug 4774647. Added by Lakshmi Gopalsami
1526 * Passed operating unit also as this parameter has been
1527 * added by base .
1528 */
1529 '',
1530 /* Bug 5359044. Added by Lakshmi Gopalsami
1531 * Changed the p_source from 'EXTERNAL'
1532 * to 'INDIA TAX SETTLEMENT INVOICES'
1533 */
1534 /* Bug 5373747. Added by Lakshmi Gopalsami
1535 * As per the discussion with AP Team changing the source
1536 * as 'INDIA TAX SETTLEMENT'
1537 */
1538 'INDIA TAX SETTLEMENT',
1539 '',
1540 'EXTERNAL'||TO_CHAR(TRUNC(SYSDATE)),
1541 '',
1542 '',
1543 '',
1544 'Y',
1545 'N',
1546 'N',
1547 'N',
1548 1000,
1549 pn_created_by,
1550 pd_creation_date);
1551 END IF;
1552
1553 pv_system_invoice_no := lv_invoice_num;
1554
1555 <<MAIN_EXIT>>
1556 NULL;
1557 EXCEPTION
1558 WHEN OTHERS THEN
1559 pv_process_flag := 'UE';
1560 pv_process_message := SUBSTR(SQLERRM,1,200);
1561 END create_invoice;
1562
1563 FUNCTION get_last_settlement_date
1564 (pn_org_id IN jai_rgm_stl_balances.party_id%TYPE,
1565 /* Bug 5096787. Added by Lakshmi Gopalsami */
1566 pn_regime_id IN jai_rgm_settlements.regime_id%TYPE DEFAULT NULL
1567 )
1568 RETURN DATE
1569 IS
1570 CURSOR c_last_settlement_date
1571 IS
1572 SELECT MAX(settlement_date)
1573 FROM JAI_RGM_STL_BALANCES
1574 WHERE party_id = pn_org_id;
1575
1576 ld_last_settlement_date jai_rgm_stl_balances.settlement_date%TYPE;
1577
1578 BEGIN
1579 OPEN c_last_settlement_date;
1580 FETCH c_last_settlement_date INTO ld_last_settlement_date;
1581 CLOSE c_last_settlement_date;
1582
1583 RETURN ld_last_settlement_date;
1584
1585 END get_last_settlement_date;
1586
1587 FUNCTION get_last_settlement_date(pn_regime_id IN jai_rgm_settlements.regime_id%type,
1588 pn_org_id IN jai_rgm_stl_balances.party_id%TYPE,
1589 pn_location_id IN jai_rgm_stl_balances.location_id%TYPE)
1590 RETURN DATE
1591 IS
1592 CURSOR c_last_settlement_date
1593 IS
1594 SELECT MAX(jbal.settlement_date)
1595 FROM JAI_RGM_STL_BALANCES jbal,jai_rgm_settlements jstl
1596 WHERE jbal.settlement_id = jstl.settlement_id
1597 AND jstl.regime_id = pn_regime_id
1598 AND party_id = pn_org_id
1599 AND location_id = pn_location_id;
1600
1601 ld_last_settlement_date jai_rgm_stl_balances.settlement_date%TYPE;
1602
1603 BEGIN
1604 OPEN c_last_settlement_date;
1605 FETCH c_last_settlement_date INTO ld_last_settlement_date;
1606 CLOSE c_last_settlement_date;
1607
1608 RETURN ld_last_settlement_date;
1609
1610 END get_last_settlement_date;
1611
1612
1613 PROCEDURE get_last_balance_amount(pn_org_id IN jai_rgm_stl_balances.party_id%TYPE,
1614 pv_tax_type IN jai_rgm_stl_balances.tax_type%TYPE,
1615 pn_debit_amount OUT NOCOPY jai_rgm_stl_balances.debit_balance%TYPE,
1616 pn_credit_amount OUT NOCOPY jai_rgm_stl_balances.credit_balance%TYPE
1617 )
1618 IS
1619
1620 /*ssawant : comenting the below cursor and redefining it for bug 5662296*/
1621 /*
1622 CURSOR c_last_settlement_balance
1623 IS
1624 SELECT debit_balance, credit_balance
1625 FROM JAI_RGM_STL_BALANCES
1626 WHERE party_id = pn_org_id
1627 AND tax_type = pv_tax_type
1628 AND settlement_date = (SELECT MAX(settlement_date)
1629 FROM JAI_RGM_STL_BALANCES
1630 WHERE party_id = pn_org_id
1631 AND tax_type = pv_tax_type);
1632
1633 */
1634
1635 /*cursor added for bug 5662296*/
1636 CURSOR c_last_settlement_balance
1637 IS
1638 SELECT debit_balance, credit_balance
1639 FROM JAI_RGM_STL_BALANCES
1640 WHERE party_id = pn_org_id
1641 AND tax_type = pv_tax_type
1642 AND settlement_id = (SELECT MAX(settlement_id)
1643 FROM JAI_RGM_STL_BALANCES
1644 WHERE party_id = pn_org_id
1645 AND tax_type = pv_tax_type);
1646
1647 /* Added by Ramananda for bug#4407165 */
1648 lv_object_name CONSTANT VARCHAR2(61) := 'jai_cmn_rgm_settlement_pkg.get_last_balance_amount';
1649
1650 BEGIN
1651 OPEN c_last_settlement_balance;
1652 FETCH c_last_settlement_balance INTO pn_debit_amount, pn_credit_amount;
1653 CLOSE c_last_settlement_balance;
1654
1655
1656 /* Added by Ramananda for bug#4407165 */
1657 EXCEPTION
1658 WHEN OTHERS THEN
1659 pn_debit_amount := null;
1660 pn_credit_amount := null;
1661 FND_MESSAGE.SET_NAME('JA','JAI_EXCEPTION_OCCURED');
1662 FND_MESSAGE.SET_TOKEN('JAI_PROCESS_MSG', lv_object_name ||'. Err:'||sqlerrm );
1663 app_exception.raise_exception;
1664
1665 END get_last_balance_amount;
1666
1667 PROCEDURE get_last_balance_amount(pn_regime_id IN jai_rgm_settlements.regime_id%type,
1668 pn_org_id IN jai_rgm_stl_balances.party_id%TYPE,
1669 pn_location_id IN jai_rgm_stl_balances.location_id%TYPE,
1670 pv_tax_type IN jai_rgm_stl_balances.tax_type%TYPE,
1671 pn_debit_amount OUT NOCOPY jai_rgm_stl_balances.debit_balance%TYPE,
1672 pn_credit_amount OUT NOCOPY jai_rgm_stl_balances.credit_balance%TYPE,
1673 pv_service_type_code IN jai_rgm_stl_balances.service_type_code%TYPE DEFAULT NULL /* added by ssawant for bug 5879769 */
1674 )
1675 IS
1676 CURSOR c_last_settlement_balance
1677 IS
1678 SELECT sum(debit_balance), sum(credit_balance) /* added sum by ssawant for bug 5879769 */
1679 FROM JAI_RGM_STL_BALANCES
1680 WHERE party_id = pn_org_id
1681 AND location_id = pn_location_id
1682 AND tax_type = pv_tax_type
1683 AND nvl(service_type_code,'-999') = nvl(pv_service_type_code, '-999' ) /* added by ssawant for bug 5879769 */
1684 AND settlement_id = (SELECT MAX(jbal.settlement_id)
1685 FROM JAI_RGM_STL_BALANCES jbal,
1686 jai_rgm_settlements jstl
1687 WHERE jbal.settlement_id = jstl.settlement_id
1688 AND jstl.regime_id = pn_regime_id
1689 AND party_id = pn_org_id
1690 AND location_id = pn_location_id
1691 AND nvl(service_type_code,'-999') = nvl(pv_service_type_code, '-999' ) /* added by ssawant for bug 5879769 */
1692 AND tax_type = pv_tax_type);
1693
1694 /* Added by Ramananda for bug#4407165 */
1695 lv_object_name CONSTANT VARCHAR2(61) := 'jai_cmn_rgm_settlement_pkg.get_last_balance_amount';
1696
1697 BEGIN
1698 OPEN c_last_settlement_balance;
1699 FETCH c_last_settlement_balance INTO pn_debit_amount, pn_credit_amount;
1700 CLOSE c_last_settlement_balance;
1701
1702 /* Added by Ramananda for bug#4407165 */
1703 EXCEPTION
1704 WHEN OTHERS THEN
1705 pn_debit_amount := null;
1706 pn_credit_amount := null;
1707 FND_MESSAGE.SET_NAME('JA','JAI_EXCEPTION_OCCURED');
1708 FND_MESSAGE.SET_TOKEN('JAI_PROCESS_MSG', lv_object_name ||'. Err:'||sqlerrm );
1709 app_exception.raise_exception;
1710
1711 END get_last_balance_amount;
1712
1713
1714
1715 PROCEDURE register_entry( pn_regime_id IN NUMBER,
1716 pn_settlement_id IN NUMBER,
1717 pd_transaction_date IN DATE,
1718 pv_process_flag OUT NOCOPY VARCHAR2,
1719 pv_process_message OUT NOCOPY VARCHAR2)
1720 IS
1721
1722 /* Added by Ramananda for bug#4407165 */
1723 lv_object_name CONSTANT VARCHAR2(61) := 'jai_cmn_rgm_settlement_pkg.register_entry';
1724
1725 CURSOR cur_distributions IS
1726 SELECT tax_type ,
1727 SUM(debit_balance) debit ,
1728 SUM(credit_balance) credit,
1729 NVL(SUM(debit_balance),0) - NVL(SUM(credit_balance),0) balance_amount,
1730 party_id ,
1731 party_type ,
1732 location_id ,
1733 service_type_code /* added by ssawant for bug 5879769 */
1734 FROM jai_rgm_stl_balances
1735 WHERE settlement_id = pn_settlement_id
1736 GROUP BY tax_type, party_type, party_id,location_id,service_type_code /* added by ssawant for bug 5879769 */
1737 HAVING NVL(SUM(debit_balance),0) - NVL(SUM(credit_balance),0) > 0;
1738
1739 CURSOR cur_settlement IS
1740 SELECT *
1741 FROM jai_rgm_settlements
1742 WHERE settlement_id = pn_settlement_id;
1743
1744 CURSOR cur_tax_types(p_reg_type jai_rgm_registrations.registration_type%TYPE ) IS --rchandan for bug#4428980
1745 SELECT attribute_sequence, attribute_code tax_type, RATE
1746 FROM JAI_RGM_REGISTRATIONS
1747 WHERE regime_id = pn_regime_id
1748 AND registration_type = p_reg_type --rchandan for bug#4428980
1749 ORDER BY 1 ASC;
1750
1751 CURSOR cur_vendor_org_id(c_vendor_id po_vendor_sites_all.vendor_id%TYPE,
1752 c_vendor_site_id po_vendor_sites_all.vendor_site_id%TYPE)
1753 IS
1754 SELECT org_id
1755 FROM po_vendor_sites_all
1756 WHERE vendor_id = c_vendor_id
1757 AND vendor_site_id = c_vendor_site_id;
1758
1759 CURSOR cur_regime_code IS /* 4245365*/
1760 SELECT regime_code,description
1761 FROM JAI_RGM_DEFINITIONS
1762 WHERE regime_id = pn_regime_id;
1763
1764 CURSOR cur_org_io IS
1765 SELECT party_id,location_id
1766 FROM jai_rgm_stl_balances
1767 WHERE settlement_id = pn_settlement_id
1768 GROUP BY party_id,location_id
1769 HAVING sum(debit_balance) - sum(credit_balance) > 0;
1770
1771 /*SELECT organization_id,location_id
1772 FROM JAI_RGM_ORG_REGNS_V
1773 WHERE regime_code = 'VAT'
1774 AND rownum = 1; */
1775
1776 rec_settlement cur_settlement%ROWTYPE;
1777
1778 ln_repository_id jai_rgm_trx_records.repository_id%TYPE;
1779
1780 lv_tax_type1 jai_rgm_stl_balances.tax_type%TYPE;
1781 lv_tax_type2 jai_rgm_stl_balances.tax_type%TYPE;
1782 ln_amount1 jai_rgm_stl_balances.debit_balance%TYPE;
1783 ln_amount2 jai_rgm_stl_balances.debit_balance%TYPE;
1784 ln_rate JAI_RGM_REGISTRATIONS.rate%TYPE;
1785 ln_org_id jai_rgm_stl_balances.party_id%TYPE;
1786 ln_amount jai_rgm_stl_balances.debit_balance%TYPE;
1787 lv_tax_type jai_rgm_stl_balances.tax_type%TYPE;
1788
1789 ln_discounted_amount NUMBER;
1790 lv_regime cur_regime_code%rowtype ;/* 4245365*/
1791 org_io_rec cur_org_io%ROWTYPE;
1792 ln_credit_amount NUMBER;
1793 ln_debit_amount NUMBER;
1794 ln_charge_accounting_id jai_rgm_trx_records.charge_account_id%type;
1795
1796 BEGIN
1797
1798 pv_process_flag := 'SS';
1799
1800 OPEN cur_settlement;
1801 FETCH cur_settlement INTO rec_settlement;
1802 CLOSE cur_settlement;
1803
1804 OPEN cur_regime_code; /* 4245365*/
1805 FETCH cur_regime_code INTO lv_regime;
1806 CLOSE cur_regime_code;
1807
1808 FOR i in cur_distributions LOOP
1809
1810 IF lv_regime.regime_code = 'SERVICE' THEN
1811
1812 jai_cmn_rgm_recording_pkg.insert_repository_entry(
1813 p_repository_id => ln_repository_id,
1814 p_regime_id => pn_regime_id,
1815 p_tax_type => i.tax_type,
1816 p_organization_type => jai_constants.orgn_type_io, /* added by ssawant for bug 5879769 */
1817 p_organization_id => i.party_id,
1818 p_location_id => i.location_id , /* added by ssawant for bug 5879769 */
1819 p_source => jai_constants.source_settle_in,
1820 p_source_trx_type => 'Invoice Payment',
1821 p_source_table_name => 'JAI_RGM_SETTLEMENTS',
1822 p_source_document_id => pn_settlement_id,
1823 p_transaction_date => pd_transaction_date,/* +1 is removed by ssawant for bug 5662296 */
1824 p_account_name => NULL,
1825 p_charge_account_id => NULL,
1826 p_balancing_account_id => NULL,
1827 p_amount => i.balance_amount,
1828 p_discounted_amount => ln_discounted_amount,
1829 p_assessable_value => NULL,
1830 p_tax_rate => NULL,
1831 p_reference_id => NULL,
1832 p_batch_id => NULL,
1833 p_called_from => 'JAIRGMSP',
1834 p_accntg_required_flag => jai_constants.no,
1835 p_process_flag => pv_process_flag,
1836 p_process_message => pv_process_message,
1837 p_accounting_date => pd_transaction_date,
1838 p_currency_code => jai_constants.func_curr, --File.Sql.35 Cbabu
1839 p_service_type_code => i.service_type_code /* added by ssawant for bug 5879769 */
1840 );
1841
1842
1843 IF pv_process_flag <> 'SS' THEN
1844 goto MAIN_EXIT;
1845 END IF;
1846
1847 UPDATE jai_rgm_trx_records
1848 SET settlement_id = pn_settlement_id
1849 WHERE repository_id = ln_repository_id;
1850
1851 ELSIF lv_regime.regime_code = 'VAT' THEN
1852
1853 ln_credit_amount := i.balance_amount;
1854 ln_debit_amount := NULL;
1855
1856
1857 ln_charge_accounting_id :=
1858 jai_cmn_rgm_recording_pkg.get_account(p_regime_id => pn_regime_id,
1859 p_organization_type => jai_constants.orgn_type_io,
1860 p_organization_id => i.party_id,
1861 p_location_id => i.location_id,
1862 p_tax_type => i.tax_type,
1863 p_account_name => jai_constants.recovery);
1864
1865 jai_cmn_rgm_recording_pkg.insert_vat_repository_entry(
1866 pn_repository_id => ln_repository_id,
1867 pn_regime_id => pn_regime_id,
1868 pv_tax_type => i.tax_type,
1869 pv_organization_type => jai_constants.orgn_type_io,
1870 pn_organization_id => i.party_id,
1871 pn_location_id => i.location_id,
1872 pv_source => jai_constants.source_settle_in,
1873 pv_source_trx_type => 'Invoice Payment',
1874 pv_source_table_name => 'JAI_RGM_SETTLEMENTS',
1875 pn_source_id => pn_settlement_id,
1876 pd_transaction_date => pd_transaction_date,/* +1 is removed by ssawant for bug 5662296 */
1877 pv_account_name => jai_constants.recovery,
1878 pn_charge_account_id => ln_charge_accounting_id,
1879 pn_balancing_account_id => NULL,
1880 pn_credit_amount => ln_credit_amount,
1881 pn_debit_amount => ln_debit_amount,
1882 pn_assessable_value => NULL,
1883 pn_tax_rate => NULL,
1884 pn_reference_id => NULL,
1885 pn_batch_id => NULL,
1886 pn_inv_organization_id => i.party_id,
1887 pv_invoice_no => NULL,
1888 pv_called_from => 'JAIRGMSP',
1889 pv_process_flag => pv_process_flag,
1890 pv_process_message => pv_process_message,
1891 pd_invoice_date => NULL
1892 );
1893
1894 END IF;
1895
1896 IF pv_process_flag <> 'SS' THEN
1897 goto MAIN_EXIT;
1898 END IF;
1899
1900 UPDATE jai_rgm_trx_records
1901 SET settlement_id = pn_settlement_id
1902 WHERE repository_id = ln_repository_id;
1903 END LOOP;
1904
1905 /*The following condition would never be met and hence not tested. This may not be correct as well*/
1906 IF rec_settlement.payment_amount > rec_settlement.calculated_amount*-1 THEN
1907
1908 IF lv_regime.regime_code = 'SERVICE' THEN
1909
1910 FOR j in cur_tax_types('TAX_TYPES') LOOP --rchandan for bug#4428980
1911
1912 IF cur_tax_types%ROWCOUNT = 1 THEN
1913 lv_tax_type1 := j.tax_type;
1914 END IF;
1915
1916 IF cur_tax_types%ROWCOUNT = 2 THEN
1917 lv_tax_type2 := j.tax_type;
1918 ln_rate := j.rate;
1919 END IF;
1920 END LOOP;
1921
1922 IF ln_rate IS NOT NULL THEN
1923 ln_amount2 := ROUND((rec_settlement.payment_amount - rec_settlement.calculated_amount)*(ln_rate/(100+ln_rate)),2);
1924 END IF;
1925
1926 ln_amount1 := rec_settlement.payment_amount - rec_settlement.calculated_amount - NVL(ln_amount2,0);
1927
1928 OPEN cur_vendor_org_id(rec_settlement.tax_authority_id, rec_settlement.tax_authority_site_id);
1929 FETCH cur_vendor_org_id INTO ln_org_id;
1930 CLOSE cur_vendor_org_id;
1931
1932 IF NVL(ln_amount1,0) <> 0 THEN
1933 jai_cmn_rgm_recording_pkg.insert_repository_entry(
1934 p_repository_id => ln_repository_id,
1935 p_regime_id => pn_regime_id,
1936 p_tax_type => lv_tax_type1,
1937 p_organization_type => jai_constants.orgn_type_ou,
1938 p_organization_id => ln_org_id,
1939 p_location_id => NULL,
1940 p_source => jai_constants.source_settle_in,
1941 p_source_trx_type => 'Invoice Payment',
1942 p_source_table_name => 'JAI_RGM_SETTLEMENTS',
1943 p_source_document_id => pn_settlement_id,
1944 p_transaction_date => pd_transaction_date,/* +1 is removed by ssawant for bug 5662296 */
1945 p_account_name => NULL,
1946 p_charge_account_id => NULL,
1947 p_balancing_account_id => NULL,
1948 p_amount => ln_amount1,
1949 p_discounted_amount => ln_discounted_amount,
1950 p_assessable_value => NULL,
1951 p_tax_rate => NULL,
1952 p_reference_id => NULL,
1953 p_batch_id => NULL,
1954 p_called_from => 'JAIRGMSP',
1955 p_accntg_required_flag => jai_constants.no,
1956 p_process_flag => pv_process_flag,
1957 p_process_message => pv_process_message,
1958 p_accounting_date => pd_transaction_date
1959 , p_currency_code => jai_constants.func_curr --File.Sql.35 Cbabu
1960 );
1961 IF pv_process_flag <> 'SS' THEN
1962 goto MAIN_EXIT;
1963 END IF;
1964
1965 ELSIF NVL(ln_amount2,0) <> 0 THEN
1966
1967 jai_cmn_rgm_recording_pkg.insert_repository_entry(
1968 p_repository_id => ln_repository_id,
1969 p_regime_id => pn_regime_id,
1970 p_tax_type => lv_tax_type2,
1971 p_organization_type => jai_constants.orgn_type_ou,
1972 p_organization_id => ln_org_id,
1973 p_location_id => NULL,
1974 p_source => jai_constants.source_settle_in,
1975 p_source_trx_type => 'Invoice Payment',
1976 p_source_table_name => 'JAI_RGM_SETTLEMENTS',
1977 p_source_document_id => pn_settlement_id,
1978 p_transaction_date => pd_transaction_date, /* +1 is removed by ssawant for bug 5662296 */
1979 p_account_name => NULL,
1980 p_charge_account_id => NULL,
1981 p_balancing_account_id => NULL,
1982 p_amount => ln_amount2,
1983 p_discounted_amount => ln_discounted_amount,
1984 p_assessable_value => NULL,
1985 p_tax_rate => NULL,
1986 p_reference_id => NULL,
1987 p_batch_id => NULL,
1988 p_called_from => 'JAIRGMSP',
1989 p_accntg_required_flag => jai_constants.no,
1990 p_process_flag => pv_process_flag,
1991 p_process_message => pv_process_message,
1992 p_accounting_date => pd_transaction_date
1993 , p_currency_code => jai_constants.func_curr --File.Sql.35 Cbabu
1994 );
1995 IF pv_process_flag <> 'SS' THEN
1996 goto MAIN_EXIT;
1997 END IF;
1998
1999 END IF;
2000
2001 ELSIF lv_regime.regime_code = 'VAT' THEN
2002
2003 FOR j in cur_tax_types('TAX_TYPES') LOOP --rchandan for bug#4428980
2004
2005 lv_tax_type := j.tax_type;
2006 ln_rate := j.rate;
2007
2008
2009 IF ln_rate IS NOT NULL THEN
2010 ln_amount := ROUND((rec_settlement.payment_amount - rec_settlement.calculated_amount)*(ln_rate/(100+ln_rate)),2);
2011 END IF;
2012
2013 OPEN cur_org_io;
2014 FETCH cur_org_io INTO org_io_rec;
2015 CLOSE cur_org_io;
2016
2017 IF nvl(ln_amount,0) <> 0 THEN
2018
2019 ln_credit_amount := ln_amount;
2020 ln_debit_amount := NULL;
2021 jai_cmn_rgm_recording_pkg.insert_vat_repository_entry(
2022 pn_repository_id => ln_repository_id,
2023 pn_regime_id => pn_regime_id,
2024 pv_tax_type => lv_tax_type,
2025 pv_organization_type => jai_constants.orgn_type_io,
2026 pn_organization_id => org_io_rec.party_id,
2027 pn_location_id => org_io_rec.location_id,
2028 pv_source => jai_constants.source_settle_in,
2029 pv_source_trx_type => 'Invoice Payment',
2030 pv_source_table_name => 'JAI_RGM_SETTLEMENTS',
2031 pn_source_id => pn_settlement_id,
2032 pd_transaction_date => pd_transaction_date, /* +1 is removed by ssawant for bug 5662296 */
2033 pv_account_name => NULL,
2034 pn_charge_account_id => NULL,
2035 pn_balancing_account_id => NULL,
2036 pn_credit_amount => ln_credit_amount,
2037 pn_debit_amount => ln_debit_amount,
2038 pn_assessable_value => NULL,
2039 pn_tax_rate => NULL,
2040 pn_reference_id => NULL,
2041 pn_batch_id => NULL,
2042 pn_inv_organization_id => org_io_rec.party_id,
2043 pv_invoice_no => NULL,
2044 pv_called_from => 'JAIRGMSP',
2045 pv_process_flag => pv_process_flag,
2046 pv_process_message => pv_process_message,
2047 pd_invoice_date => NULL
2048 );
2049
2050 IF pv_process_flag <> 'SS' THEN
2051 goto MAIN_EXIT;
2052 END IF;
2053
2054
2055 END IF;
2056
2057 END LOOP;
2058
2059 END IF;
2060
2061 END IF;
2062 <<MAIN_EXIT>>
2063 null;
2064
2065 EXCEPTION
2066 WHEN OTHERS THEN
2067 pv_process_flag := 'UE';
2068 pv_process_message := SUBSTR(SQLERRM,1,200);
2069 END register_entry;
2070 --Added by JMEENAbug#7445742
2071 /*
2072 ||The following function addded by rchandan for bug#6835541
2073 ||This function is used for VAT settlement where the user has the flexibility of
2074 || of doing settlement at either registartion or organization or organization-location level
2075 */
2076 FUNCTION get_last_settlement_date(pn_regime_id IN NUMBER,
2077 pn_regn_no IN VARCHAR2,
2078 pn_organization_id IN NUMBER,
2079 pn_location_id IN NUMBER)
2080 RETURN DATE
2081 IS
2082 CURSOR c_last_settlement_date
2083 IS
2084 SELECT MAX(jbal.settlement_date)
2085 FROM JAI_RGM_STL_BALANCES jbal,jai_rgm_settlements jstl
2086 WHERE jbal.settlement_id = jstl.settlement_id
2087 AND jstl.regime_id = pn_regime_id
2088 AND jstl.primary_registration_no = pn_regn_no
2089 AND jbal.party_id = nvl(pn_organization_id,jbal.party_id)
2090 AND jbal.location_id = nvl(pn_location_id,jbal.location_id);
2091
2092 ld_settlement_date date;
2093 BEGIN
2094
2095 OPEN c_last_settlement_date;
2096 FETCH c_last_settlement_date INTO ld_settlement_date;
2097 CLOSE c_last_settlement_date;
2098
2099 return ld_settlement_date;
2100
2101 end get_last_settlement_date;
2102
2103
2104 END jai_cmn_rgm_settlement_pkg;