[Home] [Help]
PACKAGE BODY: APPS.JAI_CMN_REVERSE_CHARGE_PKG
Source
1 package body jai_cmn_reverse_charge_pkg AS
2 /* $Header: jai_cmn_reverse_charge_pkg.plb 120.1.12020000.4 2013/03/13 06:42:20 anupgupt noship $ */
3
4 CURSOR c_rgm_repository_id(cp_source IN VARCHAR2,
5 cp_source_table_name IN VARCHAR2,
6 cp_source_document_id IN NUMBER,
7 cp_reference_id IN NUMBER) IS
8 SELECT repository_id
9 FROM jai_rgm_trx_records
10 WHERE source = cp_source
11 AND source_table_name = cp_source_table_name
12 AND source_document_id = cp_source_document_id
13 AND reference_id = cp_reference_id;
14
15 CURSOR c_invoice_payment(cp_invoice_payment_id IN NUMBER) IS
16 SELECT a.invoice_payment_id,
17 a.check_id,
18 a.amount,
19 a.payment_base_amount,
20 a.org_id,
21 b.status_lookup_code,
22 b.check_date,
23 b.void_date,
24 b.future_pay_due_date,
25 a.accounting_date,
26 a.reversal_inv_pmt_id,
27 discount_taken,
28 b.currency_code,
29 b.exchange_rate,
30 b.exchange_date,
31 b.exchange_rate_type
32 FROM ap_invoice_payments_all a, ap_checks_all b
33 WHERE a.check_id = b.check_id
34 AND a.invoice_payment_id = cp_invoice_payment_id;
35
36 cursor c_get_curr_dtls(cp_invoice_id NUMBER) IS
37 SELECT payment_currency_code,
38 exchange_rate,
39 exchange_date,
40 exchange_rate_type
41 FROM ap_invoices_all
42 WHERE invoice_id = cp_invoice_id;
43
44 CURSOR c_repo_recovered_amt(cp_source IN VARCHAR2,
45 cp_source_table_name IN VARCHAR2,
46 cp_source_document_id IN NUMBER,
47 cp_reference_id IN NUMBER) IS
48 SELECT nvl(credit_amount, debit_amount) amount
49 FROM jai_rgm_trx_records
50 WHERE source = cp_source
51 AND source_table_name = cp_source_table_name
52 AND source_document_id = cp_source_document_id
53 AND reference_id = cp_reference_id;
54
55 CURSOR c_invoice_distribution(cp_invoice_distribution_id IN NUMBER) IS
56 SELECT a.invoice_id,
57 a.invoice_distribution_id,
58 a.invoice_line_number, /* INVOICE LINES UPTAKE */
59 a.distribution_line_number,
60 a.prepay_distribution_id,
61 a.amount,
62 a.reversal_flag,
63 a.parent_reversal_id,
64 a.accounting_event_id,
65 a.posted_flag,
66 a.org_id,
67 a.accounting_date,
68 b.invoice_amount,
69 b.amount_paid,
70 b.cancelled_date,
71 b.invoice_type_lookup_code invoice_type,
72 a.creation_date,
73 a.po_distribution_id
74 FROM ap_invoice_distributions_all a, ap_invoices_all b
75 WHERE a.invoice_id = b.invoice_id
76 AND invoice_distribution_id = cp_invoice_distribution_id;
77
78 PROCEDURE get_org_location(p_invoice_id IN NUMBER,
79 p_invoice_line_number IN NUMBER,
80 p_po_distribution_id IN NUMBER,
81 p_organization_id OUT NOCOPY NUMBER,
82 p_location_id OUT NOCOPY NUMBER);
83 FUNCTION get_accrual_date(p_regime_id IN NUMBER,
84 p_organization_id NUMBER,
85 p_location_id NUMBER) RETURN DATE;
86
87 /*
88 REM +======================================================================+
89 REM Created By : Junjian Xu
90 REM Creation Date : Jul 10, 2012
91 REM Bug Number/ER Name : 14302307/SERVICE TAX REVERSE CHARGE MECHANISM
92 REM SubProgram Name : process_reverse_payment
93 REM Type : Process API
94 REM Purpose : Retrive reverse charge data which needed to handle, then call procedure to
95 REM handle them.
96 REM
97 REM
98 REM TDD Reference : TDD_122_FIN_JAI_Service_Tax_for_Reverse_Charege.doc
99 REM
100 REM Assumptions :
101 REM
102 REM Parameter IN/OUT Type Required Description and Purpose
103 REM ----------------------- ------ ------------------ ---------- ------------------------------------
104 REM p_org_id IN NUMBER Y Operation Units id.
105 REM pn_batch_id IN NUMBER Y Batch id from table jai_rgm_conc_requests.
106 REM pn_regime_id IN NUMBER Y Regime id
107 REM p_trx_from_date IN DATE Y Transaction start date
108 REM p_trx_to_date IN DATE Y Transaction end date
109 REM pn_organization_id IN NUMBER Y Inventory id
110 REM p_debug IN VARCHAR2 Y Debug flag from concurrent program paraments
111 REM p_process_flag OUT VARCHAR2 Y Process handling flag.
112 REM p_process_message OUT VARCHAR2 Y Process handling message.
113 REM
114 REM CALLED BY
115 REM jai_cmn_rgm_processing_pkg.process_payments
116 REM +=====================================================================================================+
117 */
118 PROCEDURE process_reverse_payment(p_org_id IN NUMBER,
119 pn_batch_id IN NUMBER,
120 pn_regime_id IN NUMBER,
121 p_trx_from_date IN DATE,
122 p_trx_to_date IN DATE,
123 pn_organization_id IN NUMBER,
124 p_debug IN VARCHAR2,
125 p_process_flag OUT NOCOPY VARCHAR2,
126 p_process_message OUT NOCOPY VARCHAR2) IS
127 lv_source_reverse_charge VARCHAR2(30) := 'AP_REVERSE_CHARGE';
128 ld_today DATE := trunc(sysdate);
129 l_organization_id NUMBER;
130 l_location_id NUMBER;
131 ld_st_accrual_date DATE;
132 ln_invoice_id NUMBER;
133 lv_process_flag VARCHAR2(2);
134 lv_process_message VARCHAR2(1000);
135
136 ln_commit_interval NUMBER(5) := 500;
137 ln_uncommited_trxs NUMBER(6) := 0;
138 lv_codepath varchar2(2000);
139
140 CURSOR c_batch_references(cp_batch_id IN NUMBER, cp_source IN VARCHAR2) IS
141 SELECT distinct invoice_id
142 FROM jai_rgm_trx_refs
143 WHERE batch_id = cp_batch_id
144 AND source = cp_source;
145
146 CURSOR c_previous_payments_of_inv(cp_invoice_id IN NUMBER,
147 cp_start_date IN DATE) IS
148 SELECT a.invoice_payment_id,
149 a.reversal_flag,
150 a.amount,
151 a.payment_base_amount,
152 a.org_id,
153 aida.invoice_distribution_id,
154 a.accounting_date,
155 aida.distribution_line_number,
156 aida.dist_match_type,
157 aida.invoice_line_number,
158 aida.parent_reversal_id,
159 aida.rcv_transaction_id,
160 aida.po_distribution_id
161 FROM ap_invoice_payments_all a,
162 ap_checks_all b,
163 ap_invoice_distributions_all aida
164 WHERE a.invoice_id = cp_invoice_id
165 AND a.check_id = b.check_id
166 AND a.invoice_id = aida.invoice_id
167 AND a.accounting_date < cp_start_date
168 AND nvl(b.future_pay_due_date, ld_today) <= ld_today
169 AND a.amount <> 0
170 AND jai_cmn_reverse_charge_pkg.is_rvs_chrg_liablity_dist(aida.invoice_distribution_id) = 'Y'
171 AND NOT EXISTS
172 (select 1
173 from jai_rgm_trx_records -- CHK is this required
174 where source = lv_source_reverse_charge
175 AND ((a.amount > 0 and
176 source_trx_type = 'REVERSE_PAID_ACCOUNTING') OR
177 (a.amount < 0 and
178 source_trx_type = 'REVERSE_AVOID_ACCOUNTING'))
179 and source_table_name = jai_constants.ap_payments
180 and source_document_id = a.invoice_payment_id)
181 AND EXISTS
182 (SELECT 1
183 FROM jai_rgm_trx_refs jrtr
184 WHERE jrtr.source = jai_constants.source_ap
185 AND jrtr.invoice_id = aida.invoice_id
186 AND jrtr.line_id = aida.invoice_distribution_id
187
188 )
189 ORDER BY invoice_payment_id, invoice_distribution_id;
190
191 CURSOR c_period_payments(cp_start_date IN DATE, cp_till_date IN DATE) IS
192 SELECT ainvd.invoice_id,
193 ainvd.invoice_distribution_id,
194 ainvd.distribution_line_number,
195 ainvd.dist_match_type,
196 ainvd.invoice_line_number, /* INVOICE LINES UPTAKE */
197 ainvd.parent_reversal_id,
198 ainvd.reversal_flag,
199 ainvd.rcv_transaction_id,
200 ainvd.po_distribution_id,
201 apinvp.invoice_payment_id,
202 apinvp.amount,
203 apinvp.org_id,
204 apinvp.accounting_date
205 FROM ap_invoice_payments_all apinvp,
206 ap_checks_all apc,
207 ap_invoice_distributions_all ainvd,
208 jai_rgm_trx_refs jrtr /* second table is used for join just to take IL records */
209 WHERE apinvp.org_id = p_org_id
210 AND apinvp.check_id = apc.check_id
211 AND nvl(apc.future_pay_due_date, SYSDATE) <= SYSDATE
212 AND apinvp.accounting_date BETWEEN cp_start_date AND cp_till_date
213 AND ainvd.invoice_id IN
214 (SELECT invoice_id
215 FROM ap_invoice_distributions_all
216 WHERE org_id = p_org_id
217 AND po_distribution_id IN
218 (SELECT pda.po_distribution_id
219 FROM po_line_locations_all pll,
220 po_distributions_all pda,
221 jai_po_line_locations jpll
222 WHERE pll.line_location_id = jpll.line_location_id
223 AND pll.line_location_id = pda.line_location_id
224 AND pll.ship_to_organization_id = pn_organization_id)
225 UNION
226 SELECT aia.invoice_id
227 FROM ap_invoices_all aia, jai_ap_invoice_lines jail
228 WHERE aia.invoice_id = jail.invoice_id
229 AND jail.organization_id = pn_organization_id)
230 AND jrtr.SOURCE = jai_constants.source_ap
231 AND jrtr.invoice_id = ainvd.invoice_id
232 AND apinvp.invoice_id = ainvd.invoice_id
233 AND jrtr.line_id = ainvd.invoice_distribution_id
234 AND jai_cmn_reverse_charge_pkg.is_rvs_chrg_liablity_dist(ainvd.invoice_distribution_id) = 'Y'
235 ORDER BY invoice_payment_id, invoice_distribution_id;
236
237 CURSOR c_previous_prepayment_app(cp_invoice_id IN NUMBER,
238 cp_start_date IN DATE) IS
239 SELECT ap_dist.invoice_id,
240 ap_dist.invoice_distribution_id,
241 ap_dist.amount,
242 prepay.invoice_distribution_id prepay_dist_id,
243 prepay.amount apply_amt,
244 prepay.reversal_flag,
245 prepay.parent_reversal_id,
246 ap_dist.org_id,
247 ap_dist.invoice_line_number,
248 prepay.accounting_date,
249 ap_dist.po_distribution_id
250 FROM ap_invoice_distributions_all prepay,
251 ap_invoice_distributions_all ap_dist,
252 jai_rgm_trx_refs jrtr
253 WHERE prepay.invoice_id = cp_invoice_id
254 AND ap_dist.invoice_id = prepay.invoice_id
255 AND prepay.line_type_lookup_code = jai_constants.prepay_line
256 AND jai_cmn_reverse_charge_pkg.is_rvs_chrg_liablity_dist(ap_dist.invoice_distribution_id) = 'Y'
257 AND prepay.accounting_date < cp_start_date
258 AND jrtr.SOURCE = jai_constants.source_ap
259 AND jrtr.invoice_id = ap_dist.invoice_id
260 AND jrtr.line_id = ap_dist.invoice_distribution_id
261 AND NOT EXISTS
262 (select 1
263 from jai_rgm_trx_records -- CHK is this required
264 where source = jai_constants.source_ap
265 AND ((prepay.amount < 0 AND
266 source_trx_type = 'REVERSE_APPLY_ACCOUNTING') OR
267 (prepay.amount > 0 AND
268 source_trx_type = 'REVERSE_UNAPPLY_ACCOUNTING'))
269 and source_table_name = jai_constants.ap_prepayments
270 and source_document_id = prepay.invoice_distribution_id)
271 ORDER BY invoice_distribution_id;
272
273 CURSOR c_prepayment_applications(cp_start_date IN DATE,
274 cp_till_date IN DATE) IS
275 SELECT ap_dist.invoice_id,
276 ap_dist.invoice_distribution_id,
277 ap_dist.amount,
278 prepay.invoice_distribution_id prepay_dist_id,
279 prepay.amount apply_amt,
280 prepay.reversal_flag,
281 prepay.parent_reversal_id,
282 ap_dist.org_id,
283 ap_dist.invoice_line_number,
284 prepay.accounting_date,
285 ap_dist.po_distribution_id
286 FROM ap_invoice_distributions_all prepay,
287 ap_invoice_distributions_all ap_dist,
288 jai_rgm_trx_refs jrtr
289 WHERE ap_dist.org_id = p_org_id
290 AND prepay.org_id = p_org_id -- for bug 16071477 by anupgupt
291 AND ap_dist.invoice_id = prepay.invoice_id
292 AND prepay.line_type_lookup_code = jai_constants.prepay_line
293 AND jrtr.SOURCE = jai_constants.source_ap
294 AND jrtr.invoice_id = ap_dist.invoice_id
295 AND jrtr.line_id = ap_dist.invoice_distribution_id
296 AND jai_cmn_reverse_charge_pkg.is_rvs_chrg_liablity_dist(ap_dist.invoice_distribution_id) = 'Y'
297 /* AND ((cp_start_date IS NULL AND
298 prepay.accounting_date < cp_till_date) OR
299 (cp_start_date IS NOT NULL AND
300 trunc(prepay.accounting_date) BETWEEN cp_start_date AND
301 cp_till_date)) */ /* commented and added below condition for bug 15862236 */
302 AND prepay.accounting_date BETWEEN cp_start_date AND cp_till_date
303 AND (EXISTS
304 (SELECT 1
305 FROM po_line_locations_all pll,
306 po_distributions_all pda,
307 jai_po_line_locations jpll
308 WHERE pll.line_location_id = jpll.line_location_id
309 AND pll.line_location_id = pda.line_location_id
310 AND pll.ship_to_organization_id =pn_organization_id /* commented below and added pn_organization_id for bug 15862236 */
311 --nvl(pn_organization_id, pll.ship_to_organization_id)
312 AND pda.po_distribution_id = ap_dist.po_distribution_id) OR
313 EXISTS
314 (SELECT 1
315 FROM jai_ap_invoice_lines jail
316 WHERE ap_dist.invoice_id = jail.invoice_id
317 and jail.organization_id = pn_organization_id))
318 ORDER BY ap_dist.invoice_distribution_id;
319 BEGIN
320
321 g_debug := p_debug;
322 IF g_debug = 'Y' THEN
323 fnd_file.put_line(fnd_file.log,
324 'Enter Reverse Charge Process. start');
325 END IF;
326
327 lv_codepath := jai_general_pkg.plot_codepath(1,
328 lv_codepath,
329 'PROCESS_PAYMENT',
330 'START');
331 FOR invo IN c_batch_references(pn_batch_id, jai_constants.source_ap) LOOP
332 ln_invoice_id := invo.invoice_id;
333 lv_codepath := jai_general_pkg.plot_codepath(2, lv_codepath);
334 -- Logic to Process the PAST DATED PAYMENTS that are not processed due to Invoice Accounting did not happen
335 FOR inv_payment IN c_previous_payments_of_inv(ln_invoice_id,
336 p_trx_from_date) LOOP
337 lv_process_flag := null;
338 IF inv_payment.amount > 0 AND inv_payment.reversal_flag = 'Y' THEN
339 null;
340 else
341
342 lv_codepath := jai_general_pkg.plot_codepath(3, lv_codepath);
343 get_org_location(ln_invoice_id,
344 inv_payment.invoice_line_number,
345 inv_payment.po_distribution_id,
346 l_organization_id,
347 l_location_id);
348
349 ld_st_accrual_date := get_accrual_date(pn_regime_id,
350 l_organization_id,
351 l_location_id);
352
353 IF inv_payment.accounting_date >= ld_st_accrual_date THEN
354
355 lv_codepath := jai_general_pkg.plot_codepath(4, lv_codepath);
356 SAVEPOINT process_reverse_payment;
357
358 process_charge_acct(pn_batch_id => pn_batch_id,
359 pn_regime_id => pn_regime_id,
360 pn_organization_id => l_organization_id,
361 pn_location_id => l_location_id,
362 pv_payment_table_name => jai_constants.ap_payments,
363 pn_invoice_id => ln_invoice_id,
364 pn_line_number => inv_payment.invoice_line_number,
365 pn_distribution_id => inv_payment.invoice_distribution_id,
366 pd_date => inv_payment.accounting_date,
367 pn_invoice_payment_id => inv_payment.invoice_payment_id,
368 pn_payment_amount => inv_payment.amount,
369 p_inv_accounting_chk_done => jai_constants.yes,
370 p_process_flag => lv_process_flag,
371 p_process_message => lv_process_message);
372
373 IF lv_process_flag IN
374 (jai_constants.expected_error, jai_constants.unexpected_error) THEN
375 ROLLBACK TO process_reverse_payment;
376
377 p_process_flag := lv_process_flag;
378 p_process_message := lv_process_message;
379 END IF;
380
381 ln_uncommited_trxs := ln_uncommited_trxs + 1;
382 IF ln_uncommited_trxs >= ln_commit_interval THEN
383 COMMIT;
384 ln_uncommited_trxs := 0;
385 END IF;
386
387 END IF;
388 END IF;
389
390 END LOOP; -- invoice payments
391
392 FOR ppa IN c_previous_prepayment_app(ln_invoice_id, p_trx_from_date) LOOP
393 lv_codepath := jai_general_pkg.plot_codepath(9, lv_codepath);
394 ln_invoice_id := ppa.invoice_id;
395 get_org_location(ln_invoice_id,
396 ppa.invoice_line_number,
397 ppa.po_distribution_id,
398 l_organization_id,
399 l_location_id);
400
401 ld_st_accrual_date := get_accrual_date(pn_regime_id,
402 l_organization_id,
403 l_location_id);
404
405 IF ppa.accounting_date >= ld_st_accrual_date THEN
406 lv_codepath := jai_general_pkg.plot_codepath(10, lv_codepath);
407 SAVEPOINT process_reverse_payment;
408
409 process_charge_acct(pn_batch_id => pn_batch_id,
410 pn_regime_id => pn_regime_id,
411 pn_organization_id => l_organization_id,
412 pn_location_id => l_location_id,
413 pn_invoice_id => ln_invoice_id,
414 pv_payment_table_name => jai_constants.ap_prepayments,
415 pn_line_number => ppa.invoice_line_number,
416 pn_distribution_id => ppa.invoice_distribution_id,
417 pd_date => ppa.accounting_date,
418 pn_invoice_payment_id => ppa.prepay_dist_id,
419 pn_payment_amount => ppa.apply_amt,
420 p_inv_accounting_chk_done => jai_constants.yes,
421 p_process_flag => lv_process_flag,
422 p_process_message => lv_process_message);
423
424 IF lv_process_flag IN
425 (jai_constants.expected_error, jai_constants.unexpected_error) THEN
426 ROLLBACK TO process_reverse_payment;
427 p_process_flag := lv_process_flag;
428 p_process_message := lv_process_message;
429 END IF;
430
431 ln_uncommited_trxs := ln_uncommited_trxs + 1;
432 IF ln_uncommited_trxs >= ln_commit_interval THEN
433 COMMIT;
434 ln_uncommited_trxs := 0;
435 END IF;
436 end if;
437 end loop;
438
439 END LOOP; -- batch_references
440
441 -- Logic to Process Payments that fall for the specified period
442
443 FOR inv_payment IN c_period_payments(p_trx_from_date, p_trx_to_date) LOOP
444 lv_process_flag := null;
445 ln_invoice_id := inv_payment.invoice_id;
446 lv_codepath := jai_general_pkg.plot_codepath(5, lv_codepath);
447 get_org_location(ln_invoice_id,
448 inv_payment.invoice_line_number,
449 inv_payment.po_distribution_id,
450 l_organization_id,
451 l_location_id);
452
453 ld_st_accrual_date := get_accrual_date(pn_regime_id,
454 l_organization_id,
455 l_location_id);
456
457 IF inv_payment.accounting_date >= ld_st_accrual_date THEN
458
459 lv_codepath := jai_general_pkg.plot_codepath(6, lv_codepath);
460 SAVEPOINT process_reverse_payment;
461
462 process_charge_acct(pn_batch_id => pn_batch_id,
463 pn_regime_id => pn_regime_id,
464 pn_organization_id => l_organization_id,
465 pn_location_id => l_location_id,
466 pn_invoice_id => ln_invoice_id,
467 pv_payment_table_name => jai_constants.ap_payments,
468 pn_line_number => inv_payment.invoice_line_number,
469 pn_distribution_id => inv_payment.invoice_distribution_id,
470 pd_date => inv_payment.accounting_date,
471 pn_invoice_payment_id => inv_payment.invoice_payment_id,
472 pn_payment_amount => inv_payment.amount,
473 p_inv_accounting_chk_done => jai_constants.no,
474 p_process_flag => lv_process_flag,
475 p_process_message => lv_process_message);
476
477 IF lv_process_flag IN
478 (jai_constants.expected_error, jai_constants.unexpected_error) THEN
479 ROLLBACK TO process_reverse_payment;
480 p_process_flag := lv_process_flag;
481 p_process_message := lv_process_message;
482 END IF;
483
484 ln_uncommited_trxs := ln_uncommited_trxs + 1;
485 IF ln_uncommited_trxs >= ln_commit_interval THEN
486 COMMIT;
487 ln_uncommited_trxs := 0;
488 END IF;
489 END IF;
490
491 END LOOP;
492
493 FOR pp IN c_prepayment_applications(p_trx_from_date, p_trx_to_date) LOOP
494
495 lv_codepath := jai_general_pkg.plot_codepath(7, lv_codepath);
496 ln_invoice_id := pp.invoice_id;
497
498 IF g_debug = 'Y' THEN
499 FND_FILE.put_line(fnd_file.log,
500 'Before get organization locattion,Invoice id:' ||
501 ln_invoice_id || ', Invoice line number:' ||
502 pp.invoice_line_number || ', PO distribution id:' ||
503 pp.po_distribution_id);
504 END IF;
505
506 get_org_location(ln_invoice_id,
507 pp.invoice_line_number,
508 pp.po_distribution_id,
509 l_organization_id,
510 l_location_id);
511
512 ld_st_accrual_date := get_accrual_date(pn_regime_id,
513 l_organization_id,
514 l_location_id);
515 IF pp.accounting_date >= ld_st_accrual_date THEN
516
517 lv_codepath := jai_general_pkg.plot_codepath(8, lv_codepath);
518 SAVEPOINT process_reverse_payment;
519
520 process_charge_acct(pn_batch_id => pn_batch_id,
521 pn_regime_id => pn_regime_id,
522 pn_organization_id => l_organization_id,
523 pn_location_id => l_location_id,
524 pn_invoice_id => ln_invoice_id,
525 pv_payment_table_name => jai_constants.ap_prepayments,
526 pn_line_number => pp.invoice_line_number,
527 pn_distribution_id => pp.invoice_distribution_id,
528 pd_date => pp.accounting_date,
529 pn_invoice_payment_id => pp.prepay_dist_id,
530 pn_payment_amount => pp.apply_amt,
531 p_inv_accounting_chk_done => jai_constants.no,
532 p_process_flag => lv_process_flag,
533 p_process_message => lv_process_message);
534
535 IF lv_process_flag IN
536 (jai_constants.expected_error, jai_constants.unexpected_error) THEN
537 ROLLBACK TO process_reverse_payment;
538 p_process_flag := lv_process_flag;
539 p_process_message := lv_process_message;
540 END IF;
541
542 ln_uncommited_trxs := ln_uncommited_trxs + 1;
543 IF ln_uncommited_trxs >= ln_commit_interval THEN
544 COMMIT;
545 ln_uncommited_trxs := 0;
546 END IF;
547 end if;
548 end loop;
549
550 IF g_debug = 'Y' THEN
551 FND_FILE.put_line(fnd_file.log, 'Codepath:' || lv_codepath);
552 END IF;
553
554 p_process_flag := jai_constants.successful;
555
556 EXCEPTION
557 WHEN OTHERS THEN
558 ROLLBACK;
559 p_process_flag := jai_constants.unexpected_error;
560 p_process_message := 'Unexpected Error Occured in Process_reverse_charge:' ||
561 SQLERRM;
562 FND_FILE.put_line(fnd_file.log,
563 'Unexpected Error Occured:' || p_process_message);
564 FND_FILE.put_line(fnd_file.log, 'Error Codepath:' || lv_codepath);
565
566 END process_reverse_payment;
567
568 /*
569 REM +======================================================================+
570 REM Created By : Junjian Xu
571 REM Creation Date : Jul 10, 2012
572 REM Bug Number/ER Name : 14302307/SERVICE TAX REVERSE CHARGE MECHANISM
573 REM SubProgram Name : process_charge_acct
574 REM Type : Process API
575 REM Purpose : Populate reverse charge data into general ledger interface and
576 REM service tax repository.
577 REM
578 REM
579 REM TDD Reference : TDD_122_FIN_JAI_Service_Tax_for_Reverse_Charege.doc
580 REM
581 REM Assumptions :
582 REM
583 REM Parameter IN/OUT Type Required Description and Purpose
584 REM ----------------------- ------ ------------------ ---------- ------------------------------------
585 REM pn_batch_id IN NUMBER Y Batch id from table jai_rgm_conc_requests.
586 REM pn_regime_id IN NUMBER Y Regime id
587 REM pn_organization_id IN DATE Y Inventory id
588 REM pn_location_id IN DATE Y Location id
589 REM pv_payment_table_name IN DATE Y Payment table name
590 REM pn_invoice_id IN DATE Y Invoice id
591 REM pn_line_number IN NUMBER Y Invoice line id
592 REM pn_distribution_id IN NUMBER Y Invoice distribution id
593 REM pd_date IN DATE Y GL date
594 REM pn_invoice_payment_id in NUMBER Y Invoice payment id
595 REM p_inv_accounting_chk_done IN VARCHAR2 Y Flag of if accounting is checked
596 REM pn_payment_amount IN NUMBER Y Payment amount
597 REM p_process_flag OUT VARCHAR2 Y Process handling flag.
598 REM p_process_message OUT VARCHAR2 Y Process handling message.
599 REM
600 REM CALLED BY
601 REM process_reverse_payment
602 REM +=====================================================================================================+
603 */
604 PROCEDURE process_charge_acct(pn_batch_id IN NUMBER,
605 pn_regime_id IN NUMBER,
606 pn_organization_id IN NUMBER,
607 pn_location_id IN NUMBER,
608 pv_payment_table_name IN VARCHAR2,
609 pn_invoice_id IN NUMBER,
610 pn_line_number IN NUMBER,
611 pn_distribution_id IN NUMBER,
612 pd_date IN DATE,
613 pn_invoice_payment_id in number,
614 p_inv_accounting_chk_done IN VARCHAR2,
615 pn_payment_amount IN NUMBER,
616 p_process_flag OUT NOCOPY VARCHAR2,
617 p_process_message OUT NOCOPY VARCHAR2) IS
618
619 lv_source_trx_type VARCHAR2(30) := 'REVERSE_CHARGE_ACCOUNTING';
620 lv_called_from VARCHAR2(30) := 'REVERSE_CHARGE_ACCOUNTING';
621 lv_repository_name VARCHAR2(30);
622 lv_source_table VARCHAR2(30) := pv_payment_table_name;
623 lv_currency_code VARCHAR2(30) := 'INR';
624 lv_account_name VARCHAR2(30);
625
626 ld_curr_conv_date DATE;
627 lv_curr_conv_type VARCHAR2(30);
628 ln_curr_conv_rate NUMBER;
629
630 ln_entered_amt NUMBER;
631 ln_accounted_amt NUMBER;
632
633 lv_tax_type VARCHAR2(30);
634 ln_organization_id NUMBER := pn_organization_id;
635 ln_location_id NUMBER := pn_location_id;
636 ld_gl_date DATE := pd_date;
637 lv_service_type_code VARCHAR2(50);
638 ln_tax_amount NUMBER;
639 lv_process_message VARCHAR2(200);
640 ln_repository_id NUMBER;
641 lv_process_flag VARCHAR2(100);
642
643 ln_rgm_reposotory_id NUMBER;
644
645 ln_recovered_amount NUMBER;
646 ln_parent_recovered_amt NUMBER;
647
648 ln_payment_amount NUMBER;
649 ld_transaction_date JAI_RGM_TRX_RECORDS.transaction_date%TYPE;
650 ld_accounting_date DATE;
651 ln_validate_amount NUMBER;
652 ln_discounted_amount NUMBER := 0;
653 ln_payment_discount NUMBER := 0;
654 lv_total_inv_amount number;
655
656 ln_diff_amount NUMBER;
657
658 lv_codepath VARCHAR2(1996);
659 rec_get_curr_dtls c_get_curr_dtls%rowtype;
660 ln_exc_gain_loss_amt NUMBER;
661 ln_tot_tax_amt NUMBER;
662
663 CURSOR get_curr_dtls_cur IS
664 SELECT payment_currency_code,
665 exchange_date,
666 exchange_rate,
667 exchange_rate_type,
668 invoice_date
669 FROM ap_invoices_all
670 WHERE invoice_id = pn_invoice_id;
671
672 CURSOR get_dist_line_cur IS
673 SELECT ap_dist.invoice_distribution_id,
674 ap_dist.invoice_id,
675 abs(ap_dist.amount) tax_amt,
676 tax.tax_rate,
677 tax.tax_type
678 FROM ap_invoice_lines_all ap_line,
679 ap_invoice_distributions_all ap_dist,
680 jai_cmn_taxes_all tax
681 WHERE ap_line.invoice_id = ap_dist.invoice_id
682 AND ap_dist.invoice_distribution_id = pn_distribution_id
683 AND ap_dist.invoice_line_number = ap_line.line_number
684 AND ap_dist.line_type_lookup_code = 'MISCELLANEOUS'
685 AND ap_line.application_id = 7000
686 AND ap_dist.amount < 0
687 AND ap_line.REFERENCE_KEY1 = to_char(tax.tax_id)
688 AND tax.reverse_charge_flag = 'Y'
689 and ap_dist.invoice_id = pn_invoice_id
690 UNION ALL
691 SELECT jamt.invoice_distribution_id,
692 jamt.invoice_id,
693 jamt.tax_amount tax_amt,
694 jcta.tax_rate,
695 jcta.tax_type
696 FROM jai_ap_match_inv_taxes jamt, jai_cmn_taxes_all jcta
697 WHERE jamt.invoice_id = pn_invoice_id
698 AND jamt.parent_invoice_line_number = pn_line_number
699 AND jamt.invoice_distribution_id = pn_distribution_id
700 AND jamt.tax_id = jcta.tax_id
701 AND jamt.recoverable_flag = 'Y'
702 AND jcta.reverse_charge_flag = 'Y'
703 AND jcta.tax_type IN
704 ('Service', 'SERVICE_EDUCATION_CESS', 'SERVICE_SH_EDU_CESS');
705
706 CURSOR c_invoice_distribution(cp_invoice_distribution_id IN NUMBER) IS
707 SELECT a.invoice_id,
708 a.invoice_distribution_id,
709 a.invoice_line_number, /* INVOICE LINES UPTAKE */
710 a.distribution_line_number,
711 a.prepay_distribution_id,
712 a.amount,
713 a.reversal_flag,
714 a.parent_reversal_id,
715 a.accounting_event_id,
716 a.posted_flag,
717 a.org_id,
718 a.accounting_date,
719 b.invoice_amount,
720 b.amount_paid,
721 b.cancelled_date,
722 b.invoice_type_lookup_code invoice_type,
723 a.creation_date,
724 a.po_distribution_id
725 FROM ap_invoice_distributions_all a, ap_invoices_all b
726 WHERE a.invoice_id = b.invoice_id
727 AND invoice_distribution_id = cp_invoice_distribution_id;
728
729 CURSOR c_reference(cp_source IN VARCHAR2,
730 cp_invoice_id IN NUMBER,
731 cp_invoice_distribution_id IN NUMBER) IS
732 SELECT reference_id,
733 parent_reference_id,
734 item_line_id,
735 reversal_flag,
736 nvl(recovered_amount, 0) recovered_amount,
737 tax_type,
738 recoverable_amount,
739 nvl(discounted_amount, 0) discounted_amount
740 FROM jai_rgm_trx_refs
741 WHERE source = cp_source
742 AND invoice_id = cp_invoice_id
743 AND line_id = cp_invoice_distribution_id;
744
745 CURSOR c_total_inv_amount(cp_invoice_id NUMBER) IS
746 select sum(amount)
747 from ap_invoice_distributions_all a
748 where invoice_id = cp_invoice_id
749 and prepay_distribution_id is null;
750
751 r_ref c_reference%ROWTYPE;
752 r_dist c_invoice_distribution%rowtype;
753 r_payment c_invoice_payment%rowtype;
754 r_parent_ref c_reference%ROWTYPE;
755
756 r_prepayment c_invoice_distribution%ROWTYPE;
757 --Added by Junjian for bug#14459664 on 13-Aug-2012 start
758 CURSOR c_get_inv_amount(cp_invoice_id NUMBER) IS
759 select sum(amount)
760 from ap_invoice_distributions_all
761 where invoice_id = cp_invoice_id
762 and cancellation_flag is null;
763 --Added by Junjian for bug#14459664 on 13-Aug-2012 end
764
765 BEGIN
766 OPEN get_curr_dtls_cur;
767 FETCH get_curr_dtls_cur
768 INTO lv_currency_code,
769 ld_curr_conv_date,
770 ln_curr_conv_rate,
771 lv_curr_conv_type,
772 ld_transaction_date;
773 CLOSE get_curr_dtls_cur;
774
775 lv_codepath := jai_general_pkg.plot_codepath(1,
776 lv_codepath,
777 'PROCESS_CHARGE_ACCT',
778 'START');
779 open c_total_inv_amount(pn_invoice_id);
780 fetch c_total_inv_amount
781 into lv_total_inv_amount;
782 close c_total_inv_amount;
783
784 OPEN c_get_curr_dtls(pn_invoice_id);
785 FETCH c_get_curr_dtls
786 INTO rec_get_curr_dtls;
787 CLOSE c_get_curr_dtls;
788
789 -- Accounting check for the invoice_distribution, whether it is accounted or not
790 IF p_inv_accounting_chk_done = jai_constants.no THEN
791 OPEN c_invoice_distribution(pn_distribution_id);
792 FETCH c_invoice_distribution
793 INTO r_dist;
794 CLOSE c_invoice_distribution;
795 r_dist.invoice_amount := lv_total_inv_amount;
796 -- Following condition is true only if Invoice Distribution Accounting did not happen
797 IF r_dist.posted_flag IS NULL OR r_dist.posted_flag <> 'Y' THEN
798
799 p_process_flag := jai_constants.not_accounted;
800 --p_process_message := 'Invoice is not accounted';
801 RETURN;
802 END IF;
803
804 END IF;
805
806 /*
807 cal the entered amount
808 */
809
810 --ln_regime_id := pn_regime_id;
811 FOR lr_dist_line IN get_dist_line_cur LOOP
812 lv_codepath := jai_general_pkg.plot_codepath(2, lv_codepath);
813 OPEN c_reference(jai_constants.source_ap,
814 lr_dist_line.invoice_id,
815 lr_dist_line.invoice_distribution_id);
816 FETCH c_reference
817 INTO r_ref;
818 CLOSE c_reference;
819
820 IF r_ref.reference_id is null THEN
821 null;
822 ELSE
823
824
825 OPEN c_rgm_repository_id(jai_constants.source_ap,
826 pv_payment_table_name,
827 pn_invoice_payment_id,
828 r_ref.reference_id);
829 FETCH c_rgm_repository_id
830 INTO ln_rgm_reposotory_id;
831 CLOSE c_rgm_repository_id;
832 lv_codepath := jai_general_pkg.plot_codepath(3, lv_codepath);
833 -- if the following is satisfied, then it means the payment against the invoice is already processed
834 IF ln_rgm_reposotory_id IS NOT NULL THEN
835 p_process_flag := jai_constants.already_processed;
836 RETURN;
837 END IF;
838
839 IF r_dist.invoice_distribution_id IS NULL THEN
840 OPEN c_invoice_distribution(pn_distribution_id);
841 FETCH c_invoice_distribution
842 INTO r_dist;
843 CLOSE c_invoice_distribution;
844 END IF;
845
846 --Decide the source transaction type: REVERSE_PAI_ACCOUNTING, REVERSE_AVOID_ACCOUNTING,
847 --REVERSE_APPLY_ACCOUNTING, REVERSE_CHARGE_ACCOUNTING
848 IF pv_payment_table_name = jai_constants.ap_payments THEN
849
850 lv_codepath := jai_general_pkg.plot_codepath(8, lv_codepath);
851 OPEN c_invoice_payment(pn_invoice_payment_id);
852 FETCH c_invoice_payment
853 INTO r_payment;
854 CLOSE c_invoice_payment;
855
856 IF r_payment.future_pay_due_date IS NOT NULL AND
857 r_payment.future_pay_due_date > trunc(sysdate) THEN
858 lv_codepath := jai_general_pkg.plot_codepath(9, lv_codepath);
859 p_process_flag := 'FP';
860 p_process_message := 'Future payment which is not yet matured';
861 FND_FILE.put_line(fnd_file.log, 'Codepath:' || lv_codepath);
862 RETURN;
863 END IF;
864
865 ln_payment_amount := r_payment.amount;
866 ln_payment_discount := r_payment.discount_taken;
867
868 ld_accounting_date := r_payment.accounting_date;
869
870 -- To Derive Src Trx Type and Transaction Date for Normal Payment
871 IF r_payment.amount > 0 THEN
872 IF r_payment.future_pay_due_date IS NOT NULL THEN
873 lv_codepath := jai_general_pkg.plot_codepath(10,
874 lv_codepath);
875 lv_source_trx_type := jai_constants.payment;
876 ld_transaction_date := r_payment.future_pay_due_date;
877 ELSE
878 lv_codepath := jai_general_pkg.plot_codepath(11,
879 lv_codepath);
880 lv_source_trx_type := 'REVERSE_PAID_ACCOUNTING';
881 ld_transaction_date := r_payment.check_date;
882 END IF;
883
884 -- Void Case
885 ELSE
886 IF r_payment.void_date IS NOT NULL THEN
887 lv_codepath := jai_general_pkg.plot_codepath(12,
888 lv_codepath);
889 lv_source_trx_type := 'REVERSE_AVOID_ACCOUNTING';
890 ld_transaction_date := r_payment.void_date;
891 ELSE
892 lv_codepath := jai_general_pkg.plot_codepath(13,
893 lv_codepath);
894 lv_source_trx_type := jai_constants.payment_reversal;
895 ld_transaction_date := r_payment.check_date;
896 END IF;
897 END IF;
898
899 ELSIF pv_payment_table_name = jai_constants.ap_prepayments THEN
900
901 lv_codepath := jai_general_pkg.plot_codepath(14, lv_codepath);
902 OPEN c_invoice_distribution(pn_invoice_payment_id);
903 FETCH c_invoice_distribution
904 INTO r_prepayment;
905 CLOSE c_invoice_distribution;
906
907 -- Prepayment Application is always a -ve line in invoice distributions, so to make it as +ve we need to negate it
908 ln_payment_amount := -r_prepayment.amount;
909
910 ld_accounting_date := r_prepayment.accounting_date;
911 ld_transaction_date := trunc(r_prepayment.creation_date);
912
913 -- if the following condition is satisfied, then it means a prepayment unapplication onto invoice
914 IF r_prepayment.parent_reversal_id IS NOT NULL THEN
915 lv_codepath := jai_general_pkg.plot_codepath(15,
916 lv_codepath);
917 lv_source_trx_type := 'REVERSE_UNAPPLY_ACCOUNTING';
918 ELSE
919 lv_codepath := jai_general_pkg.plot_codepath(16,
920 lv_codepath);
921 lv_source_trx_type := 'REVERSE_APPLY_ACCOUNTING';
922 END IF;
923
924 END IF;
925
926 -- Following condition is satisfied if the distribution tax line is reversal of a parent distribution tax line
927 IF r_dist.reversal_flag = 'Y' AND
928 r_dist.parent_reversal_id IS NOT NULL THEN
929
930 lv_codepath := jai_general_pkg.plot_codepath(17, lv_codepath);
931 OPEN c_reference(jai_constants.source_ap,
932 pn_invoice_id,
933 r_dist.parent_reversal_id);
934 FETCH c_reference
935 INTO r_parent_ref;
936 CLOSE c_reference;
937
938 UPDATE jai_rgm_trx_refs
939 SET reversal_flag = 'Y', last_update_date = sysdate
940 WHERE source = jai_constants.source_ap
941 AND invoice_id = pn_invoice_id
942 AND line_id in (pn_distribution_id, r_dist.parent_reversal_id);
943
944 ln_recovered_amount := -r_parent_ref.recovered_amount;
945 ln_discounted_amount := -r_parent_ref.discounted_amount;
946
947 -- following elsif is added to take care of void scenarios, where in the recovered amt againt the main payment is reversed
948 ELSIF lv_source_trx_type = 'REVERSE_AVOID_ACCOUNTING'/*jai_constants.payment_voided*/ THEN --Modified by Junjian for bug#14459664 on 08-Aug-2012
949
950 lv_codepath := jai_general_pkg.plot_codepath(18, lv_codepath);
951 OPEN c_repo_recovered_amt(jai_constants.source_ap,
952 pv_payment_table_name,
953 r_payment.reversal_inv_pmt_id,
954 r_ref.reference_id);
955 FETCH c_repo_recovered_amt
956 INTO ln_parent_recovered_amt;
957 CLOSE c_repo_recovered_amt;
958
959 ln_recovered_amount := -ln_parent_recovered_amt;
960
961 if r_payment.amount = 0 THEN
962 r_payment.amount := 1;
963 end if;
964
965 ln_discounted_amount := -ln_parent_recovered_amt *
966 (nvl(r_payment.discount_taken, 0) /
967 nvl(r_payment.amount, 1));
968
969 -- following elsif is added to take care of Prepay Unapply scenarios, where in the recovered amt againt the main payment is reversed
970 ELSIF lv_source_trx_type = 'REVERSE_UNAPPLY_ACCOUNTING'/*jai_constants.prepay_unapplication*/ THEN --Modified by Junjian for bug#14459664 on 08-Aug-2012
971 lv_codepath := jai_general_pkg.plot_codepath(20, lv_codepath);
972 OPEN c_repo_recovered_amt(jai_constants.source_ap,
973 pv_payment_table_name,
974 r_prepayment.parent_reversal_id,
975 r_ref.reference_id);
976 FETCH c_repo_recovered_amt
977 INTO ln_parent_recovered_amt;
978 CLOSE c_repo_recovered_amt;
979
980 ln_recovered_amount := -ln_parent_recovered_amt;
981
982 ELSE
983 IF r_dist.invoice_amount = 0 THEN
984 --Modified by Junjian for bug#14459664 on 13-Aug-2012 start
985 --r_dist.invoice_amount := 1;
986 open c_get_inv_amount(pn_invoice_id);
987 fetch c_get_inv_amount into r_dist.invoice_amount;
988 close c_get_inv_amount;
989 --Modified by Junjian for bug#14459664 on 13-Aug-2012 end
990 end if;
991 lv_codepath := jai_general_pkg.plot_codepath(22,
992 lv_codepath);
993 ln_recovered_amount := (abs(r_ref.recoverable_amount) *
994 ln_payment_amount) / r_dist.invoice_amount; -- CHK
995
996 /* Discount is considered only for payments and not for prepayments */
997 if r_payment.amount = 0 THEN
998 r_payment.amount := 1;
999 end if;
1000 ln_discounted_amount := ln_recovered_amount * (r_payment.discount_taken /
1001 r_payment.amount);
1002
1003 END IF;
1004
1005 lv_codepath := jai_general_pkg.plot_codepath(23, lv_codepath);
1006 ln_recovered_amount := nvl(ln_recovered_amount, 0);
1007 ln_discounted_amount := nvl(ln_discounted_amount, 0);
1008
1009 ln_validate_amount := r_ref.recovered_amount +
1010 r_ref.discounted_amount + ln_recovered_amount +
1011 ln_discounted_amount;
1012
1013 if ln_validate_amount = 0 THEN
1014 ln_validate_amount := 1;
1015 end if;
1016
1017 IF r_ref.recoverable_amount > 0 AND
1018 ln_validate_amount > r_ref.recoverable_amount THEN
1019 lv_codepath := jai_general_pkg.plot_codepath(24, lv_codepath);
1020
1021 ln_diff_amount := ln_validate_amount -
1022 r_ref.recoverable_amount;
1023 ln_discounted_amount := ln_discounted_amount -
1024 (ln_discounted_amount * ln_diff_amount /
1025 ln_validate_amount);
1026 ln_recovered_amount := ln_recovered_amount -
1027 (ln_recovered_amount * ln_diff_amount /
1028 ln_validate_amount);
1029
1030 ELSIF r_ref.recoverable_amount < 0 AND
1031 ln_validate_amount < r_ref.recoverable_amount THEN
1032 lv_codepath := jai_general_pkg.plot_codepath(25, lv_codepath);
1033
1034 ln_diff_amount := ln_validate_amount -
1035 r_ref.recoverable_amount;
1036 ln_discounted_amount := ln_discounted_amount -
1037 (ln_discounted_amount * ln_diff_amount /
1038 ln_validate_amount);
1039 ln_recovered_amount := ln_recovered_amount -
1040 (ln_recovered_amount * ln_diff_amount /
1041 ln_validate_amount);
1042
1043 END IF;
1044
1045 IF g_debug = 'Y' THEN
1046 FND_FILE.put_line(fnd_file.log,
1047 'Reverse Charge Process, DiffAmt:' ||
1048 ln_diff_amount || ', ValidtAmt:' ||
1049 ln_validate_amount || ', RecoAmt:' ||
1050 ln_recovered_amount || ', DiscRecoAmt:' ||
1051 ln_discounted_amount);
1052 END IF;
1053
1054 IF ln_recovered_amount = 0 THEN
1055 lv_codepath := jai_general_pkg.plot_codepath(26, lv_codepath);
1056 IF g_debug = 'Y' THEN
1057 FND_FILE.put_line(fnd_file.log, 'Codepath:' || lv_codepath);
1058 fnd_file.put_line(fnd_file.log, 'Allready amount is recovered');
1059 END IF;
1060 RETURN;
1061 END IF;
1062
1063 ln_tot_tax_amt := ln_recovered_amount + nvl(ln_discounted_amount, 0);
1064 IF (nvl(r_payment.exchange_rate, 1) <>
1065 nvl(rec_get_curr_dtls.exchange_rate, 1) AND
1066 r_payment.currency_code = rec_get_curr_dtls.payment_currency_code) THEN
1067 ln_exc_gain_loss_amt := (ln_tot_tax_amt *
1068 nvl(r_payment.exchange_rate, 1)) -
1069 (ln_tot_tax_amt *
1070 nvl(rec_get_curr_dtls.exchange_rate, 1));
1071 ELSE
1072 ln_exc_gain_loss_amt := 0;
1073 END IF;
1074
1075 lv_tax_type := lr_dist_line.tax_type;
1076 ln_tax_amount := lr_dist_line.tax_amt;
1077 lv_repository_name := lv_source_trx_type;
1078 ln_entered_amt := ln_recovered_amount;
1079 ln_accounted_amt := ln_entered_amt * nvl(r_payment.exchange_rate, 1);
1080
1081 lv_account_name := jai_constants.liability_interim;
1082 lv_codepath := jai_general_pkg.plot_codepath(20, lv_codepath);
1083
1084 jai_trx_repo_extract_pkg.get_doc_from_reference(p_reference_id => r_ref.reference_id,
1085 p_organization_id => ln_organization_id,
1086 p_location_id => ln_location_id,
1087 p_service_type_code => lv_service_type_code,
1088 p_process_flag => lv_process_flag,
1089 p_process_message => lv_process_message);
1090 IF lv_process_flag <> jai_constants.successful THEN
1091 lv_codepath := jai_general_pkg.plot_codepath(20.1, lv_codepath);
1092 FND_FILE.put_line(fnd_file.log,
1093 'Error Flag:' || lv_process_flag ||
1094 ' Error Message:' || lv_process_message);
1095 IF g_debug = 'Y' THEN
1096 FND_FILE.put_line(fnd_file.log, 'Codepath:' || lv_codepath);
1097 END IF;
1098 return;
1099 END IF;
1100
1101 IF nvl(ln_entered_amt, 0) <> 0 THEN
1102
1103 lv_codepath := jai_general_pkg.plot_codepath(23, lv_codepath);
1104 jai_cmn_rgm_recording_pkg.insert_repository_entry(p_repository_id => ln_repository_id,
1105 p_regime_id => pn_regime_id,
1106 p_tax_type => lv_tax_type,
1107 p_organization_type => jai_constants.service_tax_orgn_type,
1108 p_organization_id => ln_organization_id,
1109 p_location_id => ln_location_id,
1110 p_service_type_code => lv_service_type_code,
1111 p_source => jai_constants.source_ap,
1112 p_source_trx_type => lv_source_trx_type,
1113 p_source_table_name => lv_source_table,
1114 p_source_document_id => pn_invoice_payment_id,
1115 p_transaction_date => ld_gl_date,
1116 p_account_name => NULL,
1117 p_charge_account_id => NULL,
1118 p_balancing_account_id => NULL,
1119 p_amount => ln_accounted_amt,
1120 p_assessable_value => NULL,
1121 p_tax_rate => lr_dist_line.tax_rate,
1122 p_reference_id => r_ref.reference_id,
1123 p_batch_id => pn_batch_id,
1124 p_called_from => lv_called_from,
1125 p_process_flag => lv_process_flag,
1126 p_process_message => lv_process_message,
1127 p_discounted_amount => ln_discounted_amount,
1128 p_inv_organization_id => ln_organization_id,
1129 p_accounting_date => ld_gl_date,
1130 p_currency_code => nvl(r_payment.currency_code,
1131 rec_get_curr_dtls.payment_currency_code),
1132 p_curr_conv_date => nvl(r_payment.exchange_date,
1133 rec_get_curr_dtls.exchange_date),
1134 p_curr_conv_type => nvl(r_payment.exchange_rate_type,
1135 rec_get_curr_dtls.exchange_rate_type),
1136 p_curr_conv_rate => nvl(r_payment.exchange_rate,
1137 rec_get_curr_dtls.exchange_rate),
1138 p_trx_amount => ln_entered_amt,
1139 p_accntg_required_flag => jai_constants.yes,
1140 p_accrual_basis => 'Y');
1141
1142 IF lv_process_flag = jai_constants.expected_error OR
1143 lv_process_flag = jai_constants.unexpected_error THEN
1144 fnd_file.put_line(fnd_file.log,
1145 '5.1 ERROR IN CALL TO jai_cmn_reverse_charge_pkg.process_charge_acct - lv_process_flag ' ||
1146 lv_process_flag || ', lv_process_message' ||
1147 lv_process_message);
1148 p_process_flag := lv_process_flag;
1149 p_process_message := lv_process_message;
1150 END IF;
1151 END IF;
1152 END IF;
1153 END LOOP;
1154
1155 IF nvl(ln_exc_gain_loss_amt, 0) <> 0 THEN
1156
1157 jai_cmn_rgm_recording_pkg.exc_gain_loss_accounting(p_repository_id => ln_rgm_reposotory_id,
1158 p_regime_id => pn_regime_id,
1159 p_tax_type => r_ref.tax_type,
1160 p_organization_type => jai_constants.orgn_type_io,
1161 p_organization_id => ln_organization_id,
1162 p_location_id => ln_location_id,
1163 p_source => jai_constants.source_ap,
1164 p_source_trx_type => lv_source_trx_type,
1165 p_source_table_name => lv_source_table,
1166 p_source_document_id => pn_invoice_payment_id,
1167 p_transaction_date => ld_transaction_date,
1168 p_account_name => NULL,
1169 p_charge_account_id => NULL,
1170 p_balancing_account_id => NULL,
1171 p_exc_gain_loss_amt => ln_exc_gain_loss_amt,
1172 p_reference_id => r_ref.reference_id,
1173 p_called_from => lv_called_from,
1174 p_process_flag => lv_process_flag,
1175 p_process_message => lv_process_message,
1176 p_accounting_date => ld_accounting_date);
1177
1178 IF lv_process_flag = jai_constants.expected_error OR
1179 lv_process_flag = jai_constants.unexpected_error THEN
1180 p_process_flag := lv_process_flag;
1181 p_process_message := lv_process_message;
1182 fnd_file.put_line(fnd_file.log,
1183 'error in call to jai_cmn_reverse_charge_pkg.process_charge_acct - lv_process_flag ' ||
1184 lv_process_flag || ', lv_process_message' ||
1185 lv_process_message);
1186 RETURN;
1187 END IF;
1188 END IF;
1189
1190 jai_cmn_rgm_recording_pkg.update_reference(p_source => jai_constants.source_ap,
1191 p_reference_id => r_ref.reference_id,
1192 p_recovered_amount => ln_recovered_amount,
1193 p_discounted_amount => ln_discounted_amount, -- CHK (Implementation)
1194 p_process_flag => p_process_flag,
1195 p_process_message => p_process_message);
1196
1197 p_process_flag := jai_constants.successful;
1198 IF g_debug = 'Y' THEN
1199 FND_FILE.put_line(fnd_file.log, 'Codepath:' || lv_codepath);
1200 END IF;
1201
1202 END process_charge_acct;
1203
1204 /*
1205 REM +======================================================================+
1206 REM Created By : Junjian Xu
1207 REM Creation Date : Jul 10, 2012
1208 REM Bug Number/ER Name : 14302307/SERVICE TAX REVERSE CHARGE MECHANISM
1209 REM SubProgram Name : is_reverse_charge_dist
1210 REM Type : Internal Function API
1211 REM Purpose : Return wether the distribution line is a reverse charge line or not.
1212 REM
1213 REM
1214 REM TDD Reference : TDD_122_FIN_JAI_Service_Tax_for_Reverse_Charege.doc
1215 REM
1216 REM Assumptions :
1217 REM
1218 REM Parameter IN/OUT Type Required Description and Purpose
1219 REM ----------------------- ------ ------------------ ---------- ------------------------------------
1220 REM pn_distribution_id IN NUMBER Y AP distribution line id.
1221 REM
1222 REM CALLED BY
1223 REM cursors of jai_cmn_reverse_charge_pkg
1224 REM +=====================================================================================================+
1225 */
1226 FUNCTION is_reverse_charge_dist(pn_distribution_id IN NUMBER)
1227 RETURN VARCHAR2 IS
1228 PRAGMA AUTONOMOUS_TRANSACTION;
1229
1230 lv_flag VARCHAR2(1) := 'N';
1231 CURSOR c_reverse_charge_flag IS
1232 SELECT 'Y'
1233 FROM AP_INVOICE_LINES_ALL ap_line,
1234 AP_INVOICE_DISTRIBUTIONS_ALL ap_dist,
1235 JAI_CMN_TAXES_ALL tax
1236 WHERE ap_line.invoice_id = ap_dist.invoice_id
1237 AND ap_dist.invoice_distribution_id = pn_distribution_id
1238 AND ap_dist.invoice_line_number = ap_line.line_number
1239 AND ap_dist.line_type_lookup_code = 'MISCELLANEOUS'
1240 AND ap_line.application_id = 7000
1241 AND ap_line.REFERENCE_KEY1 = to_char(tax.tax_id)
1242 AND tax.reverse_charge_flag = 'Y';
1243 BEGIN
1244 OPEN c_reverse_charge_flag;
1245 FETCH c_reverse_charge_flag
1246 INTO lv_flag;
1247 CLOSE c_reverse_charge_flag;
1248 lv_flag := nvl(lv_flag, 'N');
1249
1250 RETURN lv_flag;
1251 END is_reverse_charge_dist;
1252
1253 /*
1254 REM +======================================================================+
1255 REM Created By : Junjian Xu
1256 REM Creation Date : Jul 10, 2012
1257 REM Bug Number/ER Name : 14302307/SERVICE TAX REVERSE CHARGE MECHANISM
1258 REM SubProgram Name : is_rvs_chrg_liablity_dist
1259 REM Type : Internal Function API
1260 REM Purpose : Judge wether the distribution line is a reverse charge line about liability.
1261 REM
1262 REM
1263 REM TDD Reference : TDD_122_FIN_JAI_Service_Tax_for_Reverse_Charege.doc
1264 REM
1265 REM Assumptions :
1266 REM
1267 REM Parameter IN/OUT Type Required Description and Purpose
1268 REM ----------------------- ------ ------------------ ---------- ------------------------------------
1269 REM pn_distribution_id IN NUMBER Y AP distribution line id.
1270 REM
1271 REM CALLED BY
1272 REM cursors of jai_cmn_reverse_charge_pkg
1273 REM +=====================================================================================================+
1274 */
1275 FUNCTION is_rvs_chrg_liablity_dist(pn_distribution_id IN NUMBER)
1276 RETURN VARCHAR2 IS
1277 PRAGMA AUTONOMOUS_TRANSACTION;
1278 lv_flag VARCHAR2(1) := 'N';
1279 CURSOR c_reverse_charge_flag IS
1280 SELECT 'Y'
1281 FROM AP_INVOICE_LINES_ALL ap_line,
1282 AP_INVOICE_DISTRIBUTIONS_ALL ap_dist,
1283 JAI_CMN_TAXES_ALL tax
1284 WHERE ap_line.invoice_id = ap_dist.invoice_id
1285 AND ap_dist.invoice_distribution_id = pn_distribution_id
1286 AND ap_dist.invoice_line_number = ap_line.line_number
1287 AND ap_dist.line_type_lookup_code = 'MISCELLANEOUS'
1288 AND ap_line.application_id = 7000
1289 AND ap_dist.amount < 0
1290 --AND ap_line.REFERENCE_KEY1 = to_char(tax.tax_id)
1291 AND TO_NUMBER(DECODE(RTRIM(TRANSLATE(AP_LINE.REFERENCE_KEY1,'0123456789',' ')),NULL,AP_LINE.REFERENCE_KEY1,-99999)) = TAX.TAX_ID -- for bug 16071477 by anupgupt
1292 AND tax.reverse_charge_flag = 'Y';
1293 BEGIN
1294 OPEN c_reverse_charge_flag;
1295 FETCH c_reverse_charge_flag
1296 INTO lv_flag;
1297 CLOSE c_reverse_charge_flag;
1298 lv_flag := nvl(lv_flag, 'N');
1299
1300 RETURN lv_flag;
1301 END is_rvs_chrg_liablity_dist;
1302
1303 /*
1304 REM +======================================================================+
1305 REM Created By : Junjian Xu
1306 REM Creation Date : Jul 10, 2012
1307 REM Bug Number/ER Name : 14302307/SERVICE TAX REVERSE CHARGE MECHANISM
1308 REM SubProgram Name : get_accrual_date
1309 REM Type : Internal Function API
1310 REM Purpose : Get the accrual date.
1311 REM
1312 REM
1313 REM TDD Reference : TDD_122_FIN_JAI_Service_Tax_for_Reverse_Charege.doc
1314 REM
1315 REM Assumptions :
1316 REM
1317 REM Parameter IN/OUT Type Required Description and Purpose
1318 REM ----------------------- ------ ------------------ ---------- ------------------------------------
1319 REM p_regime_id IN NUMBER Y Regime id.
1320 REM p_organization_id IN NUMBER Y Organization Units id.
1321 REM p_location_id IN NUMBER Y Location id.
1322 REM
1323 REM CALLED BY
1324 REM process_charge_acct
1325 REM +=====================================================================================================+
1326 */
1327 FUNCTION get_accrual_date(p_regime_id IN NUMBER,
1328 p_organization_id NUMBER,
1329 p_location_id NUMBER) RETURN DATE IS
1330 CURSOR c_get_st_accrual_date IS
1331 select to_date(attribute_value, 'DD/MM/YYYY')
1332 from JAI_RGM_ORG_REGNS_V
1333 where regime_id =
1334 (SELECT regime_id
1335 FROM JAI_RGM_DEFINITIONS
1336 WHERE regime_code = jai_constants.service_regime)
1337 and organization_id = p_organization_id
1338 and location_id = p_location_id
1339 AND attribute_code = 'EFF_DATE_ST_PT'
1340 AND attribute_type_code = 'OTHERS'
1341 AND registration_type = 'OTHERS'
1342 AND (NOT EXISTS
1343 (select '1'
1344 from JAI_RGM_ORG_REGNS_V
1345 where regime_id = p_regime_id
1346 and attribute_code IN 'INV_ORG_CLASSIFICATION'
1347 and attribute_value <> 'ORGANIZATION'
1348 and organization_id = p_organization_id
1349 and location_id = p_location_id) OR NOT EXISTS
1350 (select '1'
1351 from JAI_RGM_ORG_REGNS_V
1352 where regime_id = p_regime_id
1353 and attribute_code IN 'SERVICE TYPE'
1354 and attribute_value <> 'OTHER'
1355 and organization_id = p_organization_id
1356 and location_id = p_location_id));
1357
1358 ld_st_accrual_date DATE;
1359 BEGIN
1360 OPEN c_get_st_accrual_date;
1361 FETCH c_get_st_accrual_date
1362 INTO ld_st_accrual_date;
1363 CLOSE c_get_st_accrual_date;
1364 RETURN ld_st_accrual_date;
1365 END get_accrual_date;
1366
1367 /*
1368 REM +======================================================================+
1369 REM Created By : Junjian Xu
1370 REM Creation Date : Jul 10, 2012
1371 REM Bug Number/ER Name : 14302307/SERVICE TAX REVERSE CHARGE MECHANISM
1372 REM SubProgram Name : get_org_location
1373 REM Type : Internal Function API
1374 REM Purpose : Get the invoice distributions organization id and location id.
1375 REM
1376 REM
1377 REM TDD Reference : TDD_122_FIN_JAI_Service_Tax_for_Reverse_Charege.doc
1378 REM
1379 REM Assumptions :
1380 REM
1381 REM Parameter IN/OUT Type Required Description and Purpose
1382 REM ----------------------- ------ ------------------ ---------- ------------------------------------
1383 REM p_invoice_id IN NUMBER Y Invoice id.
1384 REM p_invoice_line_number IN NUMBER Y Invoice line number id.
1385 REM p_po_distribution_id IN NUMBER Y Po distribution id.
1386 REM p_organization_id OUT NUMBER Y Invoice organization id.
1387 REM p_location_id OUT NUMBER Y Location id.
1388 REM
1389 REM CALLED BY
1390 REM process_charge_acct
1391 REM +=====================================================================================================+
1392 */
1393 PROCEDURE get_org_location(p_invoice_id IN NUMBER,
1394 p_invoice_line_number IN NUMBER,
1395 p_po_distribution_id IN NUMBER,
1396 p_organization_id OUT NOCOPY NUMBER,
1397 p_location_id OUT NOCOPY NUMBER) IS
1398 CURSOR get_match_item_cur IS
1399 SELECT line.match_type, line.line_number
1400 FROM ap_invoice_lines_all line
1401 WHERE line.invoice_id = p_invoice_id
1402 AND line.po_distribution_id = line.po_distribution_id
1403 AND line.line_type_lookup_code = 'ITEM'
1404 AND line.match_type IS NOT NULL;
1405
1406 CURSOR get_org_cur IS
1407 SELECT organization_id, location_id
1408 FROM jai_ap_invoice_lines
1409 WHERE invoice_id = p_invoice_id
1410 AND invoice_line_number =
1411 (SELECT parent_invoice_line_number
1412 FROM jai_ap_invoice_lines jail
1413 WHERE jail.invoice_line_number = p_invoice_line_number
1414 AND jail.invoice_id = p_invoice_id)
1415 AND parent_invoice_line_number IS NULL;
1416
1417 CURSOR get_po_matched_org_cur(pn_line_number NUMBER) IS
1418 SELECT po.ship_to_organization_id, po.ship_to_location_id
1419 FROM po_line_locations_all po, ap_invoice_lines_all ap
1420 WHERE po.line_location_id = ap.po_line_location_id
1421 AND ap.invoice_id = p_invoice_id
1422 AND ap.line_number = pn_line_number;
1423
1424 CURSOR get_rcpt_matched_org_cur(pn_line_number NUMBER) IS
1425 SELECT rcv.organization_id, rcv.location_id
1426 FROM jai_rcv_transactions rcv, ap_invoice_lines_all ap
1427 WHERE ap.rcv_transaction_id = rcv.transaction_id
1428 AND ap.invoice_id = p_invoice_id
1429 AND ap.line_number = pn_line_number;
1430
1431 lv_match_type VARCHAR2(50);
1432 ln_item_line_num NUMBER;
1433 ln_organization_id NUMBER;
1434 ln_location_id NUMBER;
1435 BEGIN
1436 IF p_po_distribution_id IS NOT NULL THEN
1437
1438 OPEN get_match_item_cur;
1439 FETCH get_match_item_cur
1440 INTO lv_match_type, ln_item_line_num;
1441 CLOSE get_match_item_cur;
1442
1443 IF lv_match_type IN ('ITEM_TO_PO', 'ITEM_TO_RECEIPT') THEN
1444
1445 IF lv_match_type = 'ITEM_TO_PO' THEN
1446
1447 OPEN get_po_matched_org_cur(ln_item_line_num);
1448 FETCH get_po_matched_org_cur
1449 INTO ln_organization_id, ln_location_id;
1450 CLOSE get_po_matched_org_cur;
1451
1452 ELSE
1453
1454 OPEN get_rcpt_matched_org_cur(ln_item_line_num);
1455 FETCH get_rcpt_matched_org_cur
1456 INTO ln_organization_id, ln_location_id;
1457 CLOSE get_rcpt_matched_org_cur;
1458
1459 IF ln_organization_id IS NULL OR ln_location_id IS NULL THEN
1460
1461 OPEN get_po_matched_org_cur(ln_item_line_num);
1462 FETCH get_po_matched_org_cur
1463 INTO ln_organization_id, ln_location_id;
1464 CLOSE get_po_matched_org_cur;
1465
1466 END IF; /*ln_organization_id IS NULL OR ln_location_id IS NULL */
1467
1468 END IF; /*lv_match_type = 'ITEM_TO_PO' */
1469
1470 END IF; /*lv_match_type IN('ITEM_TO_PO','ITEM_TO_RECEIPT')*/
1471
1472 ELSE
1473
1474 OPEN get_org_cur;
1475 FETCH get_org_cur
1476 INTO ln_organization_id, ln_location_id;
1477 CLOSE get_org_cur;
1478
1479 END IF;
1480 p_organization_id := ln_organization_id;
1481 p_location_id := ln_location_id;
1482 END get_org_location;
1483
1484 PROCEDURE process_settlement_payment_acc
1485 (
1486 pn_regime_id IN NUMBER,
1487 pn_settlement_id IN NUMBER,
1488 pv_process_flag OUT NOCOPY VARCHAR2,
1489 pv_process_message OUT NOCOPY VARCHAR2
1490 ) IS
1491 CURSOR c_rev_chg IS
1492 SELECT jrf.invoice_id invoice_id,
1493 jrf.line_id invoice_line_id,
1494 jrf.tax_id tax_id,
1495 jct.tax_type tax_type,
1496 nvl(jct.mod_cr_percentage, 0) mod_cr_percentage,
1497 jrr.organization_id organization_id,
1498 jrr.location_id location_id,
1499 sum(nvl(jrr.debit_amount, 0)) debit_amount,
1500 sum(nvl(jrr.trx_debit_amount, 0)) trx_debit_amount
1501 FROM jai_rgm_trx_records jrr,
1502 jai_rgm_trx_refs jrf,
1503 JAI_CMN_TAXES_ALL jct
1504 Where jrr.reference_id = jrf.reference_id
1505 AND jrr.settlement_id = pn_settlement_id
1506 AND jrf.source = jai_constants.source_ap
1507 AND jrr.source = jai_constants.source_ap
1508 AND jrr.source_trx_type in ( 'REVERSE_PAID_ACCOUNTING', 'REVERSE_AVOID_ACCOUNTING','REVERSE_APPLY_ACCOUNTING','REVERSE_UNAPPLY_ACCOUNTING' )
1509 AND jrf.tax_id = jct.tax_id
1510 AND jct.reverse_charge_flag = 'Y'
1511 GROUP BY jrf.invoice_id,
1512 jrf.line_id,
1513 jrf.tax_id,
1514 jct.tax_type,
1515 jrr.organization_id,
1516 jrr.location_id,
1517 jct.mod_cr_percentage;
1518
1519
1520 cursor cur_regime_code is
1521 select regime_code
1522 from JAI_RGM_DEFINITIONS
1523 where regime_id = pn_regime_id;
1524
1525 r_rec c_rev_chg%rowtype;
1526 ln_recovery_amount NUMBER;
1527 lv_regime_code JAI_RGM_DEFINITIONS.regime_code%type;
1528 lv_organization_type VARCHAR2(10);
1529 ln_charge_accounting_id NUMBER;
1530 BEGIN
1531 pv_process_flag := 'SS' ;
1532 For r_rec in c_rev_chg loop
1533
1534 if r_rec.mod_cr_percentage >0 and r_rec.debit_amount<>0 then
1535 ln_recovery_amount := r_rec.debit_amount*r_rec.mod_cr_percentage/100 ;
1536 OPEN cur_regime_code;
1537 FETCH cur_regime_code INTO lv_regime_code;
1538 CLOSE cur_regime_code;
1539
1540 IF lv_regime_code = jai_constants.service_regime THEN
1541 lv_organization_type := jai_constants.orgn_type_io;
1542 ln_recovery_amount := ROUND(ln_recovery_amount, jai_constants.service_rgm_rnd_factor);
1543 END IF;
1544 /*Insert recovery accounting entry into GL INTERFACE*/
1545 ln_charge_accounting_id :=jai_cmn_rgm_recording_pkg.get_account
1546 (
1547 p_regime_id => pn_regime_id,
1548 p_organization_type => lv_organization_type,
1549 p_organization_id => r_rec.organization_id,
1550 p_location_id => r_rec.location_id,
1551 p_tax_type => r_rec.tax_type,
1552 p_account_name => jai_constants.recovery
1553 );
1554
1555
1556 jai_cmn_reverse_charge_pkg.post_accounting(
1557 p_regime_code => lv_regime_code,
1558 p_tax_type => r_rec.tax_type,
1559 p_organization_type => lv_organization_type,
1560 p_organization_id => r_rec.organization_id,
1561 p_source => jai_constants.source_ap,
1562 p_source_trx_type => 'INVOICE_SETTLEMENT',
1563 p_source_table_name => 'JAI_RGM_SETTLEMENTS',
1564 p_source_document_id => pn_settlement_id,
1565 p_code_combination_id => ln_charge_accounting_id,
1566 p_entered_cr => NULL,
1567 p_entered_dr => ln_recovery_amount,
1568 p_accounted_cr => NULL,
1569 p_accounted_dr => ln_recovery_amount,
1570 p_accounting_date => trunc(sysdate),
1571 p_transaction_date => trunc(sysdate),
1572 p_calling_object => 'JAIRGMSP',
1573 p_repository_name => jai_constants.repository_name,
1574 p_invoice_id => r_rec.invoice_id,
1575 p_reference_name => NULL,
1576 p_reference_id => NULL,
1577 p_currency_code => jai_constants.func_curr);
1578
1579 /*Insert reverse charge accounting entry into GL INTERFACE*/
1580 ln_charge_accounting_id :=jai_cmn_rgm_recording_pkg.get_account
1581 (
1582 p_regime_id => pn_regime_id,
1583 p_organization_type => lv_organization_type,
1584 p_organization_id => r_rec.organization_id,
1585 p_location_id => r_rec.location_id,
1586 p_tax_type => r_rec.tax_type,
1587 p_account_name => 'REVERSE_CHARGE'
1588 );
1589
1590 jai_cmn_reverse_charge_pkg.post_accounting(
1591 p_regime_code => lv_regime_code,
1592 p_tax_type => r_rec.tax_type,
1593 p_organization_type => lv_organization_type,
1594 p_organization_id => r_rec.organization_id,
1595 p_source => jai_constants.source_ap,
1596 p_source_trx_type => 'INVOICE_SETTLEMENT',
1597 p_source_table_name => 'JAI_RGM_SETTLEMENTS',
1598 p_source_document_id => pn_settlement_id,
1599 p_code_combination_id => ln_charge_accounting_id,
1600 p_entered_cr => ln_recovery_amount,
1601 p_entered_dr => NULL,
1602 p_accounted_cr => ln_recovery_amount,
1603 p_accounted_dr => NULL,
1604 p_accounting_date => trunc(sysdate),
1605 p_transaction_date => trunc(sysdate),
1606 p_calling_object => 'JAIRGMSP',
1607 p_repository_name => jai_constants.repository_name,
1608 p_invoice_id => r_rec.invoice_id,
1609 p_reference_name => NULL,
1610 p_reference_id => NULL,
1611 p_currency_code => jai_constants.func_curr);
1612 end if;
1613 END loop;
1614 EXCEPTION
1615 When others then
1616 pv_process_flag := jai_constants.unexpected_error;
1617 pv_process_message := 'SQLCODE='||TO_CHAR(SQLCODE)||' settlement_payment Occured:'||SQLERRM;
1618 END process_settlement_payment_acc;
1619
1620
1621 PROCEDURE post_accounting(
1622 p_regime_code IN VARCHAR2,
1623 p_tax_type IN VARCHAR2,
1624 p_organization_type IN VARCHAR2,
1625 p_organization_id IN NUMBER,
1626 p_source IN VARCHAR2,
1627 p_source_trx_type IN VARCHAR2,
1628 p_source_table_name IN VARCHAR2,
1629 p_source_document_id IN NUMBER,
1630 p_code_combination_id IN NUMBER,
1631 p_entered_cr IN NUMBER,
1632 p_entered_dr IN NUMBER,
1633 p_accounted_cr IN NUMBER,
1634 p_accounted_dr IN NUMBER,
1635 p_accounting_date IN DATE,
1636 p_transaction_date IN DATE,
1637 p_calling_object IN VARCHAR2,
1638 p_repository_name IN VARCHAR2 DEFAULT NULL,
1639 p_invoice_id IN NUMBER,
1640 p_reference_name IN VARCHAR2 DEFAULT NULL,
1641 p_reference_id IN NUMBER DEFAULT NULL,
1642 p_currency_code IN VARCHAR2 DEFAULT NULL,
1643 p_curr_conv_date IN DATE DEFAULT NULL,
1644 p_curr_conv_type IN VARCHAR2 DEFAULT NULL,
1645 p_curr_conv_rate IN NUMBER DEFAULT NULL
1646 ) IS
1647
1648 lv_object_name CONSTANT VARCHAR2(61) := 'jai_cmn_rgm_recording_pkg.post_accounting';
1649
1650 lv_reference10 GL_INTERFACE.reference10%type;
1651 lv_reference23 GL_INTERFACE.reference23%type;
1652 lv_reference24 GL_INTERFACE.reference24%type;
1653 lv_reference25 GL_INTERFACE.reference25%type;
1654 lv_reference26 GL_INTERFACE.reference26%type;
1655
1656 ld_accounting_date DATE;
1657 lv_message VARCHAR2(100);
1658
1659 lv_period_name GL_PERIODS.period_name%TYPE;
1660 ln_sob_id GL_SETS_OF_BOOKS.set_of_books_id%TYPE;
1661 ln_currency_precision FND_CURRENCIES.precision%TYPE;
1662
1663 ln_entered_dr NUMBER;
1664 ln_entered_cr NUMBER;
1665 ln_accounted_dr NUMBER;
1666 ln_accounted_cr NUMBER;
1667
1668 lv_regime_code JAI_RGM_DEFINITIONS.regime_code%TYPE;
1669 lv_reference_name VARCHAR2(30);
1670 ln_reference_id NUMBER(15);
1671 lv_gl_je_category varchar2(30);
1672 lv_status gl_interface.status%TYPE ;
1673 lv_source_name VARCHAR2(30);
1674
1675
1676 lv_organization_code mtl_parameters.organization_code%TYPE;
1677
1678 CURSOR c_organization_code(cp_organization_id IN NUMBER)
1679 IS
1680 SELECT organization_code
1681 FROM mtl_parameters
1682 WHERE organization_id = cp_organization_id;
1683
1684
1685 Cursor c_get_source_info(cp_transfer_id NUMBER)
1686 IS
1687 select party_id
1688 from jai_rgm_dis_src_hdrs
1689 where transfer_id = cp_transfer_id ;
1690
1691
1692 Cursor c_get_dest_info(cp_transfer_id NUMBER)
1693 IS
1694 select destination_party_id
1695 from jai_rgm_dis_des_hdrs
1696 where transfer_id = cp_transfer_id ;
1697
1698 Cursor c_get_source(cp_repository_id NUMBER)
1699 IS
1700 select source
1701 from jai_rgm_trx_records
1702 where repository_id = cp_repository_id ;
1703
1704
1705 CURSOR get_inv_num_settlement IS
1706 SELECT MANUAL_INVOICE_NO
1707 FROM JAI_RGM_SETTLEMENTS
1708 WHERE settlement_id = p_source_document_id;
1709
1710 CURSOR get_inv_num_ap IS
1711 SELECT INVOICE_NUM
1712 FROM AP_INVOICES_ALL
1713 WHERE invoice_id = p_invoice_id;
1714
1715 lv_invoice_num ap_invoices_all.invoice_num%TYPE;
1716 lv_invoice_type ap_invoices_all.invoice_type_lookup_code%TYPE;
1717 lv_invoice_num_str VARCHAR2(300);
1718
1719
1720 lv_source jai_rgm_trx_records.source%TYPE ;
1721 lv_src_party_id jai_rgm_dis_src_hdrs.party_id%TYPE ;
1722 lv_reference1 gl_interface.reference1%TYPE ;
1723 ln_loop_cnt NUMBER;
1724
1725
1726 BEGIN
1727 jai_cmn_utils_pkg.print_log('6395039.log', 'Start of post_accounting');
1728
1729 lv_reference1 := null ;
1730
1731
1732 IF p_regime_code = jai_constants.service_regime
1733 AND p_source_trx_type= 'INVOICE_SETTLEMENT' THEN
1734
1735 OPEN c_organization_code(p_organization_id);
1736 FETCH c_organization_code INTO lv_organization_code;
1737 CLOSE c_organization_code;
1738 lv_reference1 := lv_organization_code ;
1739
1740
1741 OPEN get_inv_num_ap;
1742 FETCH get_inv_num_ap INTO lv_invoice_num;
1743 CLOSE get_inv_num_ap;
1744
1745 lv_invoice_num_str := 'Invoice Number: ' || lv_invoice_num;
1746
1747 lv_reference10 := 'Service Tax Accounting for '||p_source||'. Transaction Type:'||nvl(p_source_trx_type,'~~')
1748 || '. ' ||lv_invoice_num_str;
1749 lv_source_name := jai_constants.service_tax_source;
1750
1751
1752 ld_accounting_date := nvl( trunc(p_accounting_date), trunc(sysdate) );
1753
1754 IF p_code_combination_id IS NULL THEN
1755 lv_message := 'Account not given';
1756 RAISE_APPLICATION_ERROR( -20011, lv_message);
1757 END IF;
1758
1759 ln_currency_precision := jai_general_pkg.get_currency_precision(null);
1760 ln_entered_dr := round(p_entered_dr, ln_currency_precision);
1761 ln_entered_cr := round(p_entered_cr, ln_currency_precision);
1762 ln_accounted_dr := round(p_accounted_dr, ln_currency_precision);
1763 ln_accounted_cr := round(p_accounted_cr, ln_currency_precision);
1764
1765 IF ( nvl(ln_entered_dr, 0) = 0 AND nvl(ln_entered_cr,0) = 0
1766 OR nvl(ln_accounted_dr, 0) = 0 AND nvl(ln_accounted_cr,0) = 0 )
1767 THEN
1768
1769 FND_FILE.put_line( FND_FILE.log, 'Accounting not done as Both Credit and Debit are Zero ');
1770 RETURN ;
1771
1772 END IF;
1773
1774 jai_cmn_rgm_recording_pkg.get_period_name(
1775 p_organization_type => p_organization_type,
1776 p_organization_id => p_organization_id,
1777 p_accounting_date => ld_accounting_date,
1778 p_period_name => lv_period_name,
1779 p_sob_id => ln_sob_id
1780 );
1781 lv_gl_je_category := jai_constants.je_category_rg_entry;
1782 lv_status := 'NEW';
1783
1784 INSERT INTO gl_interface (
1785 status, set_of_books_id, user_je_source_name, user_je_category_name,
1786 accounting_date, currency_code, date_created, created_by,
1787 actual_flag, entered_cr, entered_dr, accounted_cr, accounted_dr, transaction_date,
1788 code_combination_id, currency_conversion_date, user_currency_conversion_type, currency_conversion_rate,
1789 reference10, reference22, reference23, reference1,
1790 reference24, reference25, reference26, reference27
1791 ) VALUES (
1792 lv_status, ln_sob_id, lv_source_name, lv_gl_je_category,
1793 ld_accounting_date, p_currency_code, sysdate, FND_GLOBAL.user_id,
1794 'A', ln_entered_cr, ln_entered_dr, ln_accounted_cr, ln_accounted_dr, p_transaction_date,
1795 p_code_combination_id, p_curr_conv_date, p_curr_conv_type, p_curr_conv_rate,
1796 lv_reference10, jai_constants.gl_je_source_name, p_calling_object, lv_reference1,
1797 p_source_table_name, p_source_document_id, p_invoice_id ,p_organization_id
1798 );
1799
1800 IF p_reference_id IS NOT NULL OR p_reference_name IS NOT NULL THEN
1801 lv_reference_name := p_reference_name;
1802 ln_reference_id := p_reference_id;
1803 ELSE
1804 lv_reference_name := p_repository_name;
1805 ln_reference_id := p_source_document_id;
1806 END IF;
1807
1808 INSERT INTO JAI_CMN_JOURNAL_ENTRIES(JOURNAL_ENTRY_ID,
1809 regime_code, organization_id, set_of_books_id, tax_type, period_name,
1810 code_combination_id, accounted_dr, accounted_cr, transaction_date,
1811 source, source_table_name, source_trx_id, reference_name, reference_id, repository_id,
1812 currency_code, curr_conv_rate, creation_date, created_by, last_update_date, last_updated_by, last_update_login
1813 ) VALUES ( JAI_CMN_JOURNAL_ENTRIES_S.nextval,
1814 p_regime_code, p_organization_id, ln_sob_id, p_tax_type, lv_period_name,
1815 p_code_combination_id, ln_accounted_dr, ln_accounted_cr, p_transaction_date,
1816 p_source, p_source_table_name, p_source_document_id,p_source_trx_type , p_reference_id,p_source_document_id,
1817 p_currency_code, p_curr_conv_rate, sysdate, FND_GLOBAL.user_id, sysdate, fnd_global.user_id, fnd_global.login_id
1818 );
1819 END IF ;
1820
1821 EXCEPTION
1822 WHEN OTHERS THEN
1823 FND_MESSAGE.SET_NAME('JA','JAI_EXCEPTION_OCCURED');
1824 FND_MESSAGE.SET_TOKEN('JAI_PROCESS_MSG', lv_object_name ||'. Err:'||sqlerrm );
1825 app_exception.raise_exception;
1826
1827 END post_accounting;
1828
1829 PROCEDURE process_settlement_repository
1830 (
1831 p_batch_id IN NUMBER,
1832 p_regime_id IN NUMBER,
1833 p_organization_type IN VARCHAR2,
1834 p_from_date IN DATE,
1835 p_to_date IN DATE,
1836 p_org_id IN NUMBER,
1837 p_organization_id IN NUMBER,
1838 p_process_flag OUT NOCOPY VARCHAR2,
1839 p_process_message OUT NOCOPY VARCHAR2
1840 ) IS
1841
1842 CURSOR c_settle IS
1843 SELECT jrs.*
1844 FROM JAI_RGM_SETTLEMENTS jrs,
1845 JAI_RGM_DEFINITIONS jrd
1846 where jrs.regime_id = jrd.regime_id
1847 and jrd.regime_code = jai_constants.service_regime
1848 and jrs.payment_date between p_from_date and p_to_date
1849 and jrs.status = 'SETTLED';
1850
1851
1852 CURSOR c_rev_chg (pn_settlement_id NUMBER)IS
1853 SELECT jrf.reference_id reference_id,
1854 jrf.invoice_id invoice_id,
1855 jrf.line_id invoice_line_id,
1856 jrf.tax_id tax_id,
1857 jct.tax_rate tax_rate,
1858 jct.tax_type tax_type,
1859 nvl(jct.mod_cr_percentage, 0) mod_cr_percentage,
1860 jrr.organization_id organization_id,
1861 jrr.location_id location_id,
1862 jrr.settlement_id settlement_id,
1863 sum(nvl(jrr.debit_amount, 0)) debit_amount,
1864 sum(nvl(jrr.trx_debit_amount, 0)) trx_debit_amount
1865 FROM jai_rgm_trx_records jrr,
1866 jai_rgm_trx_refs jrf,
1867 JAI_CMN_TAXES_ALL jct
1868 Where jrr.reference_id = jrf.reference_id
1869 AND jrr.settlement_id = pn_settlement_id
1870 AND jrf.source = jai_constants.source_ap
1871 AND jrr.organization_id = nvl(p_organization_id,jrr.organization_id)
1872 AND jrr.source = jai_constants.source_ap
1873 AND jrr.source_trx_type in ( 'REVERSE_PAID_ACCOUNTING', 'REVERSE_AVOID_ACCOUNTING','REVERSE_APPLY_ACCOUNTING','REVERSE_UNAPPLY_ACCOUNTING' )
1874 AND jrf.tax_id = jct.tax_id
1875 AND jct.reverse_charge_flag = 'Y'
1876 GROUP BY jrf.reference_id,
1877 jrf.invoice_id,
1878 jrf.line_id,
1879 jrf.tax_id,
1880 jct.tax_rate,
1881 jct.tax_type,
1882 jct.mod_cr_percentage,
1883 jrr.organization_id,
1884 jrr.location_id,
1885 jrr.settlement_id;
1886
1887 cursor cur_regime_code is
1888 select regime_code
1889 from JAI_RGM_DEFINITIONS
1890 where regime_id = p_regime_id;
1891
1892 CURSOR c_get_curr_dtls (pn_invoice_id NUMBER) IS
1893 SELECT payment_currency_code,
1894 exchange_rate,
1895 exchange_date,
1896 exchange_rate_type
1897 FROM ap_invoices_all
1898 WHERE invoice_id = pn_invoice_id;
1899
1900 CURSOR c_get_repository_flag ( pn_settlement_id NUMBER, pn_reference_id NUMBER,pn_invoice_id NUMBER, pn_invoice_line_id NUMBER, pn_tax_id NUMBER) IS
1901 SELECT 'Y'
1902 FROM jai_rgm_trx_records jrr,
1903 jai_rgm_trx_refs jrf
1904 Where jrr.reference_id = jrf.reference_id
1905 AND jrf.invoice_id = pn_invoice_id
1906 AND jrr.source = jai_constants.source_ap
1907 AND jrr.source_trx_type = 'INVOICE_SETTLEMENT'
1908 AND jrr.source_table_name = 'JAI_RGM_SETTLEMENTS'
1909 AND jrr.source_document_id = pn_settlement_id
1910 AND jrf.reference_id = pn_reference_id
1911 AND jrf.line_id = pn_invoice_line_id
1912 AND jrf.tax_id = pn_tax_id;
1913
1914 CURSOR c_get_service_type_code ( pn_settlement_id NUMBER, pn_invoice_id NUMBER )IS
1915 SELECT jrr.service_type_code service_type_code
1916 FROM jai_rgm_trx_records jrr,
1917 jai_rgm_trx_refs jrf
1918 Where jrr.reference_id = jrf.reference_id
1919 AND jrr.settlement_id = pn_settlement_id
1920 AND jrf.source = jai_constants.source_ap
1921 AND jrr.source = jai_constants.source_ap
1922 AND jrr.source_trx_type in ( 'REVERSE_PAID_ACCOUNTING', 'REVERSE_AVOID_ACCOUNTING','REVERSE_APPLY_ACCOUNTING','REVERSE_UNAPPLY_ACCOUNTING' );
1923
1924 CURSOR c_get_reference_id ( pn_invoice_id NUMBER, pn_invoice_line_id NUMBER, pn_tax_id NUMBER )IS
1925 SELECT jrf.reference_id
1926 FROM jai_rgm_trx_refs jrf,
1927 ap_invoice_lines_all aila,
1928 ap_invoice_distributions_all aida
1929 Where aida.invoice_id = aila.invoice_id
1930 AND aida.INVOICE_LINE_NUMBER = aila.line_number
1931 AND aida.invoice_distribution_id <> pn_invoice_line_id
1932 AND aila.reference_key1 = pn_tax_id
1933 AND aila.reference_key2 =
1934 (
1935 SELECT al.reference_key2
1936 from ap_invoice_lines_all al,
1937 ap_invoice_distributions_all ad
1938 where al.invoice_id = ad.invoice_id
1939 and ad.INVOICE_LINE_NUMBER = al.line_number
1940 and al.invoice_id = pn_invoice_id
1941 and ad.invoice_distribution_id = pn_invoice_line_id
1942 and aila.reference_key1 = pn_tax_id
1943 )
1944 AND jrf.source = jai_constants.source_ap
1945 AND jrf.invoice_id = pn_invoice_id
1946 AND jrf.line_id = aida.invoice_distribution_id
1947 AND jrf.tax_id = pn_tax_id;
1948
1949
1950
1951 r_sel c_settle%rowtype;
1952 r_rec c_rev_chg%rowtype;
1953 r_curr_dtls c_get_curr_dtls%rowtype;
1954 lv_regime_code JAI_RGM_DEFINITIONS.regime_code%type;
1955 ln_recovery_amount NUMBER;
1956 lv_organization_type VARCHAR2(10);
1957 lv_process_flag VARCHAR2(15);
1958 lv_process_message VARCHAR2(4000);
1959 ln_repository_id NUMBER;
1960 lv_service_type_code jai_rgm_trx_records.service_type_code%type;
1961 lv_repository_flag VARCHAR2(2);
1962 ln_discounted_amount number :=0 ;
1963 ln_reference_id NUMBER;
1964 lv_called_from varchar2(100) := 'jai_cmn_reverse_charge_pkg.process_settlement_repository';
1965 BEGIN
1966 FOR r_sel in c_settle loop
1967 FOR r_rec in c_rev_chg(r_sel.settlement_id) loop
1968 OPEN c_get_reference_id( r_rec.invoice_id,r_rec.invoice_line_id, r_rec.tax_id);
1969 FETCH c_get_reference_id INTO ln_reference_id;
1970 CLOSE c_get_reference_id;
1971
1972 OPEN c_get_repository_flag(r_sel.settlement_id,ln_reference_id, r_rec.invoice_id,r_rec.invoice_line_id,
1973 r_rec.tax_id);
1974 FETCH c_get_repository_flag INTO lv_repository_flag;
1975 CLOSE c_get_repository_flag;
1976 IF r_rec.mod_cr_percentage >0 and r_rec.debit_amount<>0 and nvl(lv_repository_flag,'N')<>'Y'THEN
1977 ln_recovery_amount := r_rec.debit_amount*r_rec.mod_cr_percentage/100 ;
1978 OPEN cur_regime_code;
1979 FETCH cur_regime_code INTO lv_regime_code;
1980 CLOSE cur_regime_code;
1981
1982 OPEN c_get_curr_dtls(r_rec.invoice_id);
1983 FETCH c_get_curr_dtls INTO r_curr_dtls;
1984 CLOSE c_get_curr_dtls;
1985
1986 OPEN c_get_service_type_code(r_sel.settlement_id,r_rec.invoice_id);
1987 FETCH c_get_service_type_code INTO lv_service_type_code;
1988 CLOSE c_get_service_type_code;
1989
1990 IF lv_regime_code = jai_constants.service_regime THEN
1991 lv_organization_type := jai_constants.orgn_type_io;
1992 ln_recovery_amount := ROUND(ln_recovery_amount, jai_constants.service_rgm_rnd_factor);
1993 END IF;
1994
1995 jai_cmn_rgm_recording_pkg.insert_repository_entry(
1996 p_repository_id => ln_repository_id,
1997 p_regime_id => p_regime_id,
1998 p_tax_type => r_rec.tax_type,
1999 p_organization_type => lv_organization_type,
2000 p_organization_id => r_rec.organization_id,
2001 p_location_id => r_rec.location_id,
2002 p_service_type_code => lv_service_type_code,
2003 p_source => jai_constants.source_ap,
2004 p_source_trx_type => 'INVOICE_SETTLEMENT',
2005 p_source_table_name => 'JAI_RGM_SETTLEMENTS',
2006 p_source_document_id => r_sel.settlement_id,
2007 p_transaction_date => r_sel.PAYMENT_DATE,
2008 p_account_name => NULL,
2009 p_charge_account_id => NULL,
2010 p_balancing_account_id => NULL,
2011 p_amount => ln_recovery_amount,
2012 p_assessable_value => NULL,
2013 p_tax_rate => r_rec.tax_rate,
2014 p_reference_id => ln_reference_id,
2015 p_batch_id => p_batch_id,
2016 p_called_from => lv_called_from,
2017 p_process_flag => lv_process_flag,
2018 p_process_message => lv_process_message,
2019 p_discounted_amount => ln_discounted_amount,
2020 p_inv_organization_id => r_rec.organization_id,
2021 p_accounting_date => r_sel.PAYMENT_DATE,
2022 p_currency_code => r_curr_dtls.payment_currency_code,
2023 p_curr_conv_date => r_curr_dtls.exchange_date,
2024 p_curr_conv_type => r_curr_dtls.exchange_rate_type,
2025 p_curr_conv_rate => r_curr_dtls.exchange_rate,
2026 p_trx_amount => ln_recovery_amount,
2027 p_accntg_required_flag => jai_constants.no,
2028 p_accrual_basis => 'N');
2029
2030 IF lv_process_flag = jai_constants.expected_error OR
2031 lv_process_flag = jai_constants.unexpected_error THEN
2032 fnd_file.put_line( fnd_file.log,
2033 '5.1 ERROR IN CALL TO jai_cmn_reverse_charge_pkg.process_settlement_repository - lv_process_flag '||lv_process_flag
2034 ||', lv_process_message'||lv_process_message);
2035 p_process_flag := lv_process_flag;
2036 p_process_message := lv_process_message;
2037 END IF;
2038 END IF;
2039 END LOOP;
2040 END LOOP;
2041
2042 EXCEPTION
2043 WHEN OTHERS THEN
2044 p_process_flag := jai_constants.unexpected_error;
2045 p_process_message := 'Repository Error SQLCODE='||TO_CHAR(SQLCODE)||' SQLERRM=:'||SQLERRM;
2046 Fnd_file.put_line( fnd_file.log, p_process_message);
2047 END process_settlement_repository;
2048
2049 end jai_cmn_reverse_charge_pkg;