1 PACKAGE BODY jai_cmn_rgm_processing_pkg AS
2 /* $Header: jai_cmn_rgm_prc.plb 120.27.12010000.3 2009/01/13 07:14:26 vumaasha ship $ */
3
4 /* */
5 /*----------------------------------------------------------------------------------------------------------------------------
6 CHANGE HISTORY for FILENAME: jai_rgm_trx_processing_pkg_b.sql
7 S.No dd/mm/yyyy Author and Details
8 ------------------------------------------------------------------------------------------------------------------------------
9 1 26/07/2004 Vijay Shankar for Bug# 4068823, Version:115.0
10
11 Basic Package that Starts Service Tax Processing of both AP and AR transactions based on the inputs
12 provided through request of "India - Service Tax Processing" concurrent
13
14 - PROCESS_BATCH - this procedure is invoked from the request submitted by users through JAISRVTP concurrent
15 This picksup all the Operating Units that are linked to the given Service Tax registration number and
16 invokes AP Processor and AR Processor for their respective processing
17
18 - INSERT_REQUEST_DETAILS : inserts a record into batch header with input details
19 - GET_ITEM_LINE_ID : fetches ITEM Invoice Distribution of TAX invoice distribution incase of AP transactions
20
21 - PROCESS_PAYMENT : records the recovered service tax into repository to the tune of payment amount
22 w.r.t invoice amount and service tax distribution amount. this has all the required functional logic related
23 to Payment reversals and apportioning of Service Tax if multiple payments exists for same invoice etc.
24
25 - PROCESS_PAYMENTS : This is the AP Processor that picks up all the eligible Payments(includes prepayments
26 also) and invokes process_payment for each payment
27
28 2 19/03/2005 Vijay Shankar for Bug#4250236(4245089). FileVersion: 115.1
29 removed the usage of regime effective_date_from and replace it with regime creation_date as part of VAT Impl.
30 This is required as effective dates are removed Regime setup
31
32 3 12/04/2005 Brathod, for Bug# 4286646, Version 115.2
33 Issue :-
34 Because of change in Valueset from JA_IN_DATE to FND_STANDARD_DATE Concurrent was resulting
35 in error because JA_IN_DATE uses normal date format while FND_STANDARD_DATE uses NLS_DATE format
36 and it is passed as character value.
37 Fix :-
38 Procedure signature modified to convert p_trx_from_date, p_trx_from_date from date to
39 pv_trx_from_date, pv_trx_from_date varchar2. And the varchar2 values are converted back
40 to date fromat using fnd_date.canonical_to_date function.
41
42 4. 14/04/2005 ssumaith - bug# 4284505 - File version 115.3
43
44 Added code to pick the third party taxes from the jai_Rcv_tp_inv_details table in case of
45 third party invoices.
46
47 This is done by adding code for checking - source in the ap_invoices_all table , if it
48 equals to 'RECEIPT' , getting the third party taxes from the jai_Rcv_tp_inv_Details table.
49
50 5. 24/05/2005 Ramananda for bug# 4388958 File Version: 116.1
51 Changed AP Lookup code from 'RECEIPT' to 'INDIA TAX INVOICE'
52
53
54 6. 08-Jun-2005 Version 116.3 jai_cmn_rgm_prc -Object is Modified to refer to New DB Entity names in place of Old DB Entity Names
55 as required for CASE COMPLAINCE.
56
57 7. 14-Jun-2005 rchandan for bug#4428980, Version 116.4
58 Modified the object to remove literals from DML statements and CURSORS.
59
60 8. 24-Jan-2006 Bug 4991017. Added by Lakshmi Gopalsami version 120.4
61 Merged the cursors c_ap_accounted_invoices and
62 c_event_distributions because of SLA uptake by base
63 and removed the same.
64 (1) Changed the reference to xla_ae_headers instead
65 of ap_ae_headers_all
66 (2) Also added xla_transaction_entities to get the entity_id
67 and source_int_id_1 so that it can be joined with
68 transaction tables.
69 (3) Discussed with shekhar and found that we should derive by
70 accounting_date and not on the creation_date.
71 (4) Added accounting_event_id in cursor
72 (5) Added local variable lv_entity_code
73
74 DEPENDANCY:
75 -----------
76 IN60106 + 4068823 + 4245089
77
78 8. 29/07/2005 Aiyer - bug# 4523205 - File version 120.2 - (R12 Forward Porting FROM 11.5 bugs 4348774, 4357984)
79 Procedure process has been changed for the bug. Please refer the details in the change history section
80 of the procedure.
81
82 9. 08-Mar-2006 , Bug 4947102, By Aiyer , File Version 120.5
83 Issue:-
84 Cursor c_period_payments has high cost of execution.
85
86 Fix:-
87 Merged the cursors c_period_payments with c_invoice_distributions into c_period_payments so that the IL table in cursor
88 can reduce the overall rows searched by the query.
89 SQL-ID as reported in the repository is 14828450.
90
91 Dependency Due to this bug :-
92 None
93
94 10. 09-JUNE-2007 ,Kunkumar for Bug 6012489 version 12.6
95 Added an if condtion for assignment to local variable
96 If action is accounting, then generated vat invoice number is picked
97 11. 05-SEP-2007 CSahoo for bug#5680459, File Version 120.23
98 R.TST1203:FORWARD PORTING FROM 115 BUG 5645003
99 commented the part where lv_inv_gen_process_flag and lv_inv_gen_process_message
100 were getting assigned as NULL.
101 replaced the party_id by party_site_id as the second parameter in the call to check_reg_dealer procedure.
102
103 12. 04-OCT-2007 CSahoo for bug#6436576, File Version 120.24
104 R.TST1203.XB2.QA:SERVICE TAX REVIEW REPOSITORY SHOW MULTIPLE ACCOUNTING LINES
105 added the following AND condition in the cursor c_period_payments in process_payments procedure.
106 AND apinvp.invoice_id = ainvd.invoice_id
107
108 13. 13-01-2009 vumaasha for bug 7684820
109 INDIA LOCALIZATION: SERVICE TAX RECOVERABLE PORTION IS INCORRECT CALCULATED
110 ---------------------------------------------------------------------------------------------------------------------------*/
111
112 CURSOR c_rgm_repository_id(cp_source IN VARCHAR2, cp_source_table_name IN VARCHAR2,
113 cp_source_document_id IN NUMBER, cp_reference_id IN NUMBER) IS
114 SELECT repository_id
115 FROM jai_rgm_trx_records
116 WHERE source = cp_source
117 AND source_table_name = cp_source_table_name
118 AND source_document_id = cp_source_document_id
119 AND reference_id = cp_reference_id;
120
121 CURSOR c_repo_recovered_amt(cp_source IN VARCHAR2, cp_source_table_name IN VARCHAR2,
122 cp_source_document_id IN NUMBER, cp_reference_id IN NUMBER) IS
123 SELECT nvl(credit_amount, debit_amount) amount
124 FROM jai_rgm_trx_records
125 WHERE source = cp_source
126 AND source_table_name = cp_source_table_name
127 AND source_document_id = cp_source_document_id
128 AND reference_id = cp_reference_id;
129
130 CURSOR c_reference(cp_source IN VARCHAR2, cp_invoice_id IN NUMBER, cp_invoice_distribution_id IN NUMBER) IS
131 SELECT reference_id, parent_reference_id, item_line_id, reversal_flag, nvl(recovered_amount, 0) recovered_amount,
132 tax_type, recoverable_amount, nvl(discounted_amount,0) discounted_amount
133 FROM jai_rgm_trx_refs
134 WHERE source = cp_source
135 AND invoice_id = cp_invoice_id
136 AND line_id = cp_invoice_distribution_id;
137
138 CURSOR c_reference_using_id(cp_reference_id IN NUMBER) IS
139 SELECT reference_id, parent_reference_id, item_line_id, reversal_flag, recovered_amount, tax_type,
140 recoverable_amount
141 FROM jai_rgm_trx_refs
142 WHERE reference_id = cp_reference_id;
143
144 CURSOR c_invoice_distribution(cp_invoice_distribution_id IN NUMBER) IS
145 SELECT a.invoice_id, a.invoice_distribution_id,
146 a.invoice_line_number, /* INVOICE LINES UPTAKE */
147 a.distribution_line_number, a.prepay_distribution_id,
148 a.amount, a.reversal_flag, a.parent_reversal_id, a.accounting_event_id, a.posted_flag, a.org_id,
149 a.accounting_date, b.invoice_amount, b.amount_paid, b.cancelled_date, b.invoice_type_lookup_code invoice_type,
150 a.creation_date, a.po_distribution_id
151 FROM ap_invoice_distributions_all a, ap_invoices_all b
152 WHERE a.invoice_id = b.invoice_id
153 AND invoice_distribution_id = cp_invoice_distribution_id;
154
155 CURSOR c_invoice_payment(cp_invoice_payment_id IN NUMBER) IS
156 SELECT a.invoice_payment_id, a.check_id, a.amount, a.payment_base_amount, -- a.reversal_flag, reversal_inv_pmt_id,
157 a.org_id, b.status_lookup_code, b.check_date, b.void_date, b.future_pay_due_date,
158 a.accounting_date, a.reversal_inv_pmt_id, discount_taken
159 FROM ap_invoice_payments_all a, ap_checks_all b
160 WHERE a.check_id = b.check_id
161 AND a.invoice_payment_id = cp_invoice_payment_id;
162
163 ---------------------------- GET_ITEM_LINE_ID ---------------------------
164 FUNCTION get_item_line_id(
165 p_invoice_id IN NUMBER,
166 p_po_distribution_id IN NUMBER,
167 p_rcv_transaction_id IN NUMBER
168 ) RETURN NUMBER IS
169
170 CURSOR c_parent_distribution_id( p_line_type_lookup_code ap_invoice_distributions_all.line_type_lookup_code%TYPE ) IS
171 SELECT invoice_distribution_id
172 FROM ap_invoice_distributions_all
173 WHERE invoice_id = p_invoice_id
174 AND (p_rcv_transaction_id IS NULL OR rcv_transaction_id = p_rcv_transaction_id)
175 AND po_distribution_id = p_po_distribution_id
176 AND line_type_lookup_code = p_line_type_lookup_code--rchandan for bug#4428980
177 AND parent_reversal_id IS NULL; -- CHK
178
179 ln_item_distribution_id AP_INVOICE_DISTRIBUTIONS_ALL.invoice_distribution_id%TYPE;
180
181 BEGIN
182
183 OPEN c_parent_distribution_id('ITEM');
184 FETCH c_parent_distribution_id INTO ln_item_distribution_id;
185 CLOSE c_parent_distribution_id;
186
187 RETURN ln_item_distribution_id;
188
189 END get_item_line_id;
190
191 ---------------------------- INSERT_REQUEST_DETAILS ---------------------------
192 PROCEDURE insert_request_details(
193 p_batch_id OUT NOCOPY NUMBER,
194 p_regime_id IN NUMBER,
195 p_rgm_registration_num IN VARCHAR2,
196 p_trx_from_date IN DATE,
197 p_trx_till_date IN DATE
198 ) IS
199
200 ln_conc_request_id FND_CONCURRENT_REQUESTS.request_id%TYPE;
201 ln_conc_request_date FND_CONCURRENT_REQUESTS.request_date%TYPE;
202
203 CURSOR c_request_date(cp_request_id IN NUMBER) IS
204 SELECT request_date
205 FROM fnd_concurrent_requests
206 WHERE request_id = cp_request_id;
207
208 /* Added by Ramananda for bug#4407165 */
209 lv_object_name CONSTANT VARCHAR2(61) := 'jai_cmn_rgm_processing_pkg.insert_request_details';
210
211 BEGIN
212
213 ln_conc_request_id := FND_PROFILE.value('CONC_REQUEST_ID');
214
215 OPEN c_request_date(ln_conc_request_id);
216 FETCH c_request_date INTO ln_conc_request_date;
217 CLOSE c_request_date;
218
219 INSERT INTO jai_rgm_conc_requests(
220 batch_id,
221 request_id,
222 request_date,
223 regime_id,
224 rgm_registration_num,
225 trx_from_date,
226 trx_till_date,
227 creation_date,
228 created_by,
229 last_update_date,
230 last_updated_by,
231 last_update_login,
232 program_application_id,
233 program_id,
234 program_login_id
235 ) VALUES (
236 jai_rgm_conc_requests_s.nextval,
237 ln_conc_request_id,
238 ln_conc_request_date,
239 p_regime_id,
240 p_rgm_registration_num,
241 p_trx_from_date,
242 p_trx_till_date,
243 sysdate,
244 FND_GLOBAL.user_id,
245 sysdate,
246 fnd_global.user_id,
247 fnd_global.login_id,
248 fnd_profile.value('PROG_APPL_ID'),
249 fnd_profile.value('CONC_PROGRAM_ID'),
250 fnd_profile.value('CONC_LOGIN_ID')
251 ) RETURNING batch_id INTO p_batch_id;
252
253
254 /* Added by Ramananda for bug#4407165 */
255 EXCEPTION
256 WHEN OTHERS THEN
257 FND_MESSAGE.SET_NAME('JA','JAI_EXCEPTION_OCCURED');
258 FND_MESSAGE.SET_TOKEN('JAI_PROCESS_MSG', lv_object_name ||'. Err:'||sqlerrm );
259 app_exception.raise_exception;
260
261 END insert_request_details;
262
263 ---------------------------- PROCESS_PAYMENT ---------------------------
264 PROCEDURE process_payment(
265 p_batch_id IN NUMBER,
266 p_regime_id IN NUMBER,
267 p_org_id IN NUMBER,
268 p_source IN VARCHAR2,
269 p_payment_table_name IN VARCHAR2,
270 p_payment_document_id IN NUMBER,
271 p_invoice_id IN NUMBER,
272 p_inv_dist_id IN NUMBER,
273 p_inv_accounting_chk_done IN VARCHAR2,
274 p_process_flag OUT NOCOPY VARCHAR2,
275 p_process_message OUT NOCOPY VARCHAR2
276 ) IS
277 /*Bug 5879769 bduvarag start*/
278 /* CURSOR c_inv_organization_id(cp_po_distribution_id IN NUMBER) IS
279 SELECT b.ship_to_organization_id
280 FROM po_distributions_all a, po_line_locations_all b
281 WHERE a.line_location_id = b.line_location_id
282 AND a.po_distribution_id = cp_po_distribution_id;*/
283 lv_service_type_code jai_po_line_locations.service_type_code%TYPE;
284 ln_organization_id NUMBER;
285 ln_location_id NUMBER;
286 lv_process_flag VARCHAR2(15);
287 lv_process_message VARCHAR2(4000);
288 /*Bug 5879769 bduvarag End*/
289
290 r_ref c_reference%ROWTYPE;
291 r_parent_ref c_reference%ROWTYPE;
292 r_dist c_invoice_distribution%ROWTYPE;
293
294 r_prepayment c_invoice_distribution%ROWTYPE;
295 r_payment c_invoice_payment%ROWTYPE;
296
297 ln_inv_organization_id NUMBER(15);
298 ln_rgm_reposotory_id NUMBER;
299 -- ln_payment_amount NUMBER;
300
301 lv_src_trx_type VARCHAR2(30);
302 ln_recovered_amount NUMBER;
303 ln_parent_recovered_amt NUMBER;
304
305 ln_payment_amount NUMBER;
306 ld_transaction_date JAI_RGM_TRX_RECORDS.transaction_date%TYPE;
307 ld_accounting_date DATE;
308 ln_validate_amount NUMBER;
309 ln_discounted_amount NUMBER := 0;
310 ln_payment_discount NUMBER := 0;
311
312 ln_diff_amount NUMBER;
313
314 lv_codepath VARCHAR2(1996); -- := ''; File.Sql.35 by Brathod
315 lv_called_from VARCHAR2(100); --rchandan for bug#4428980
316
317 BEGIN
318 g_debug := 'Y';
319 lv_codepath := jai_general_pkg.plot_codepath(1,lv_codepath, 'PROCESS_PAYMENT', 'START');
320
321 -- Accounting check for the invoice_distribution, whether it is accounted or not
322 IF p_inv_accounting_chk_done = jai_constants.no THEN
323 OPEN c_invoice_distribution(p_inv_dist_id);
324 FETCH c_invoice_distribution INTO r_dist;
325 CLOSE c_invoice_distribution;
326
327 lv_codepath := jai_general_pkg.plot_codepath(2, lv_codepath);
328
329 -- Following condition is true only if Invoice Distribution Accounting did not happen
330 IF r_dist.posted_flag IS NULL OR r_dist.posted_flag <> 'Y' THEN
331 lv_codepath := jai_general_pkg.plot_codepath(3, lv_codepath);
332 IF g_debug='Y' THEN
333 fnd_file.put_line(fnd_file.log,'AccntChkFail. InvId,LineNum,DisNum:'||r_dist.invoice_id||','
334 ||r_dist.invoice_line_number||','||r_dist.distribution_line_number);
335 END IF;
336 p_process_flag := jai_constants.not_accounted;
337 --p_process_message := 'Invoice is not accounted';
338 RETURN;
339 END IF;
340
341 END IF;
342
343 OPEN c_reference(p_source, p_invoice_id, p_inv_dist_id);
344 FETCH c_reference INTO r_ref;
345 CLOSE c_reference;
346
347 -- If the following if condition is satisfied, then it means there is no REFERENCE entry and thus no RECEOVERY should happen
348 IF r_ref.reference_id IS NULL THEN
349 lv_codepath := jai_general_pkg.plot_codepath(4, lv_codepath);
350 RETURN;
351 -- if the following is satisfied then it means this is a reversal of a parent line which is processed and hence should return back
352 ELSIF r_ref.reversal_flag = 'Y' THEN
353 lv_codepath := jai_general_pkg.plot_codepath(5, lv_codepath);
354 p_process_flag := jai_constants.already_processed;
355 RETURN;
356 END IF;
357
358 IF g_debug='Y' THEN fnd_file.put_line(fnd_file.log,'ProPay.r_ref:'||r_ref.reference_id||',taxty:'||r_ref.tax_type); END IF;
359
360 OPEN c_rgm_repository_id(p_source, p_payment_table_name, p_payment_document_id, r_ref.reference_id);
361 FETCH c_rgm_repository_id INTO ln_rgm_reposotory_id;
362 CLOSE c_rgm_repository_id;
363
364 -- if the following is satisfied, then it means the payment against the invoice is already processed
365 IF ln_rgm_reposotory_id IS NOT NULL THEN
366 lv_codepath := jai_general_pkg.plot_codepath(6, lv_codepath);
367 p_process_flag := jai_constants.already_processed;
368 RETURN;
369 END IF;
370
371 lv_codepath := jai_general_pkg.plot_codepath(7, lv_codepath);
372 -- following will be true only in case Accounting Check for Invoice distribution is not done in calling procedure
373 IF r_dist.invoice_distribution_id IS NULL THEN
374 OPEN c_invoice_distribution(p_inv_dist_id);
375 FETCH c_invoice_distribution INTO r_dist;
376 CLOSE c_invoice_distribution;
377 END IF;
378
379 IF p_payment_table_name = jai_constants.ap_payments THEN
380
381 lv_codepath := jai_general_pkg.plot_codepath(8, lv_codepath);
382 OPEN c_invoice_payment(p_payment_document_id);
383 FETCH c_invoice_payment INTO r_payment;
384 CLOSE c_invoice_payment;
385
386 IF r_payment.future_pay_due_date IS NOT NULL AND r_payment.future_pay_due_date > trunc(sysdate) THEN
387 lv_codepath := jai_general_pkg.plot_codepath(9, lv_codepath);
388 p_process_flag := 'FP';
389 p_process_message := 'Future payment which is not yet matured';
390 RETURN;
391 END IF;
392
393 ln_payment_amount := r_payment.amount;
394 ln_payment_discount := r_payment.discount_taken;
395
396 ld_accounting_date := r_payment.accounting_date;
397
398 -- To Derive Src Trx Type and Transaction Date for Normal Payment
399 IF r_payment.amount > 0 THEN
400 IF r_payment.future_pay_due_date IS NOT NULL THEN
401 lv_codepath := jai_general_pkg.plot_codepath(10, lv_codepath);
402 lv_src_trx_type := jai_constants.future_payment;
403 ld_transaction_date := r_payment.future_pay_due_date;
404 ELSE
405 lv_codepath := jai_general_pkg.plot_codepath(11, lv_codepath);
406 lv_src_trx_type := jai_constants.payment;
407 ld_transaction_date := r_payment.check_date;
408 END IF;
409
410 -- Void Case
411 ELSE
412 IF r_payment.void_date IS NOT NULL THEN
413 lv_codepath := jai_general_pkg.plot_codepath(12, lv_codepath);
414 lv_src_trx_type := jai_constants.payment_voided;
415 ld_transaction_date := r_payment.void_date;
416 ELSE
417 lv_codepath := jai_general_pkg.plot_codepath(13, lv_codepath);
418 lv_src_trx_type := jai_constants.payment_reversal;
419 ld_transaction_date := r_payment.check_date;
420 END IF;
421 END IF;
422
423 ELSIF p_payment_table_name = jai_constants.ap_prepayments THEN
424
425 lv_codepath := jai_general_pkg.plot_codepath(14, lv_codepath);
426 OPEN c_invoice_distribution(p_payment_document_id);
427 FETCH c_invoice_distribution INTO r_prepayment;
428 CLOSE c_invoice_distribution;
429
430 -- Prepayment Application is always a -ve line in invoice distributions, so to make it as +ve we need to negate it
431 ln_payment_amount := -r_prepayment.amount;
432
433 ld_accounting_date := r_prepayment.accounting_date;
434 ld_transaction_date := trunc(r_prepayment.creation_date);
435
436 -- if the following condition is satisfied, then it means a prepayment unapplication onto invoice
437 IF r_prepayment.parent_reversal_id IS NOT NULL THEN
438 lv_codepath := jai_general_pkg.plot_codepath(15, lv_codepath);
439 lv_src_trx_type := jai_constants.prepay_unapplication;
440 ELSE
441 lv_codepath := jai_general_pkg.plot_codepath(16, lv_codepath);
442 lv_src_trx_type := jai_constants.prepay_application;
443 END IF;
444
445 END IF;
446
447 -- following condition is satisfied if the invoice is cancelled and line has been already claimed that needs to be reversed
448 --IF r_dist.cancelled_date IS NOT NULL THEN
449
450 -- Following condition is satisfied if the distribution tax line is reversal of a parent distribution tax line
451 IF r_dist.reversal_flag = 'Y' AND r_dist.parent_reversal_id IS NOT NULL THEN
452
453 lv_codepath := jai_general_pkg.plot_codepath(17, lv_codepath);
454 OPEN c_reference(p_source, p_invoice_id, r_dist.parent_reversal_id);
455 FETCH c_reference INTO r_parent_ref;
456 CLOSE c_reference;
457
458 UPDATE jai_rgm_trx_refs
459 SET reversal_flag = 'Y',
460 last_update_date = sysdate
461 WHERE source = p_source
462 AND invoice_id = p_invoice_id
463 AND line_id in (p_inv_dist_id, r_dist.parent_reversal_id);
464
465 ln_recovered_amount := -r_parent_ref.recovered_amount;
466 ln_discounted_amount := -r_parent_ref.discounted_amount;
467
468 -- following elsif is added to take care of void scenarios, where in the recovered amt againt the main payment is reversed
469 ELSIF lv_src_trx_type = jai_constants.payment_voided THEN
470
471 lv_codepath := jai_general_pkg.plot_codepath(18, lv_codepath);
472 OPEN c_repo_recovered_amt(p_source, p_payment_table_name, r_payment.reversal_inv_pmt_id, r_ref.reference_id);
473 FETCH c_repo_recovered_amt INTO ln_parent_recovered_amt;
474 CLOSE c_repo_recovered_amt;
475
476 ln_recovered_amount := -ln_parent_recovered_amt;
477
478 if r_payment.amount = 0 THEN
479 r_payment.amount :=1;
480 end if;
481
482 ln_discounted_amount := -ln_parent_recovered_amt * ( nvl(r_payment.discount_taken,0)/nvl(r_payment.amount,1) );
483
484 -- following elsif is added to take care of Prepay Unapply scenarios, where in the recovered amt againt the main payment is reversed
485 ELSIF lv_src_trx_type = jai_constants.prepay_unapplication THEN
486 lv_codepath := jai_general_pkg.plot_codepath(20, lv_codepath);
487 OPEN c_repo_recovered_amt(p_source, p_payment_table_name, r_prepayment.parent_reversal_id, r_ref.reference_id);
488 FETCH c_repo_recovered_amt INTO ln_parent_recovered_amt;
489 CLOSE c_repo_recovered_amt;
490
491 ln_recovered_amount := -ln_parent_recovered_amt;
492
493 ELSE
494 IF r_dist.invoice_amount = 0 THEN
495 r_dist.invoice_amount := 1;
496 end if;
497 lv_codepath := jai_general_pkg.plot_codepath(22, lv_codepath);
498 ln_recovered_amount := (r_ref.recoverable_amount * ln_payment_amount) / r_dist.invoice_amount; -- CHK
499
500 /* Discount is considered only for payments and not for prepayments */
501 if r_payment.amount = 0 THEN
502 r_payment.amount := 1;
503 end if;
504 ln_discounted_amount := ln_recovered_amount * ( r_payment.discount_taken / r_payment.amount );
505
506 END IF;
507
508 IF g_debug = 'Y' THEN
509 FND_FILE.put_line(fnd_file.log, 'RecoAmt:'||ln_recovered_amount||', RefRecobleAmt:'||r_ref.recoverable_amount
510 ||', PaymtAmt:'||ln_payment_amount||', InvAmt:'||r_dist.invoice_amount||', DiscTaken:'||r_payment.discount_taken
511 ||', rPayAmt:'||r_payment.amount||', DiscRecoAmt:'||ln_discounted_amount);
512 END IF;
513
514 lv_codepath := jai_general_pkg.plot_codepath(23, lv_codepath);
515 ln_recovered_amount := nvl(ln_recovered_amount, 0);
516 ln_discounted_amount := nvl(ln_discounted_amount, 0);
517
518 ln_validate_amount := r_ref.recovered_amount + r_ref.discounted_amount + ln_recovered_amount + ln_discounted_amount;
519
520 if ln_validate_amount = 0 THEN
521 ln_validate_amount := 1;
522 end if;
523
524
525 IF r_ref.recoverable_amount > 0 AND ln_validate_amount > r_ref.recoverable_amount THEN
526 lv_codepath := jai_general_pkg.plot_codepath(24, lv_codepath);
527
528 ln_diff_amount := ln_validate_amount - r_ref.recoverable_amount;
529 ln_discounted_amount := ln_discounted_amount - (ln_discounted_amount * ln_diff_amount / ln_validate_amount);
530 ln_recovered_amount := ln_recovered_amount - (ln_recovered_amount * ln_diff_amount / ln_validate_amount);
531 --ln_recovered_amount := r_ref.recoverable_amount - r_ref.recovered_amount;
532
533 ELSIF r_ref.recoverable_amount < 0 AND ln_validate_amount < r_ref.recoverable_amount THEN
534 lv_codepath := jai_general_pkg.plot_codepath(25, lv_codepath);
535
536 ln_diff_amount := ln_validate_amount - r_ref.recoverable_amount;
537 ln_discounted_amount := ln_discounted_amount - (ln_discounted_amount * ln_diff_amount / ln_validate_amount);
538 ln_recovered_amount := ln_recovered_amount - (ln_recovered_amount * ln_diff_amount / ln_validate_amount);
539 -- ln_recovered_amount := r_ref.recoverable_amount - r_ref.recovered_amount;
540
541 END IF;
542
543 IF g_debug = 'Y' THEN
544 FND_FILE.put_line(fnd_file.log, 'DiffAmt:'||ln_diff_amount||', ValidtAmt:'||ln_validate_amount
545 ||', RecoAmt:'||ln_recovered_amount||', DiscRecoAmt:'||ln_discounted_amount);
546 END IF;
547
548 IF ln_recovered_amount = 0 THEN
549 lv_codepath := jai_general_pkg.plot_codepath(26, lv_codepath);
550 IF g_debug='Y' THEN
551 fnd_file.put_line(fnd_file.log,'Allready amount is recovered');
552 END IF;
553 RETURN;
554 END IF;
555 /*Bug 5879769 bduvarag start*/
556 /* OPEN c_inv_organization_id(r_dist.po_distribution_id);
557 FETCH c_inv_organization_id INTO ln_inv_organization_id;
558 CLOSE c_inv_organization_id;*/
559 jai_trx_repo_extract_pkg.get_doc_from_reference(p_reference_id => r_ref.reference_id,
560 p_organization_id => ln_organization_id,
561 p_location_id => ln_location_id,
562 p_service_type_code => lv_service_type_code,
563 p_process_flag => lv_process_flag,
564 p_process_message => lv_process_message
565 );
566
567 IF lv_process_flag <> jai_constants.successful THEN
568 lv_codepath := jai_general_pkg.plot_codepath(27.1, lv_codepath);
569 FND_FILE.put_line(fnd_file.log, 'Error Flag:'||lv_process_flag||' Error Message:'||lv_process_message);
570 return;
571 END IF;
572 /*Bug 5879769 bduvarag End*/
573 lv_codepath := jai_general_pkg.plot_codepath(27, lv_codepath);
574 lv_called_from := 'AP_PROCESSING';--rchandan for bug#4428980
575 jai_cmn_rgm_recording_pkg.insert_repository_entry(
576 p_repository_id => ln_rgm_reposotory_id,
577 p_regime_id => p_regime_id,
578 p_tax_type => r_ref.tax_type,
579 p_organization_type => jai_constants.orgn_type_io ,/*5694855*/
580 p_organization_id => ln_organization_id ,/*5694855*/
581 p_location_id => ln_location_id,/*5694855*/
582 p_service_type_code => lv_service_type_code,/*5694855*/
583 p_source => p_source,
584 p_source_trx_type => lv_src_trx_type,
585 p_source_table_name => p_payment_table_name,
586 p_source_document_id => p_payment_document_id,
587 p_transaction_date => ld_transaction_date,
588 p_account_name => null,
589 p_charge_account_id => null,
590 p_balancing_account_id => null,
591 p_amount => ln_recovered_amount,
592 p_discounted_amount => ln_discounted_amount,
593 p_inv_organization_id => ln_organization_id,/*Bug 5879769 bduvarag*/
594 p_trx_amount => ln_recovered_amount,
595 p_assessable_value => null,
596 p_tax_rate => null,
597 p_reference_id => r_ref.reference_id,
598 p_batch_id => p_batch_id,
599 p_called_from => lv_called_from, --rchandan for bug#4428980
600 p_process_flag => p_process_flag,
601 p_process_message => p_process_message,
602 p_accntg_required_flag => jai_constants.yes,
603 p_accounting_date => ld_accounting_date,
604 p_balancing_orgn_type => null,
605 p_balancing_orgn_id => null,
606 p_balancing_location_id => null,
607 p_balancing_tax_type => null,
608 p_balancing_accnt_name => null,
609 p_currency_code => jai_constants.func_curr,
610 p_curr_conv_date => null,
611 p_curr_conv_type => null,
612 p_curr_conv_rate => null
613 );
614
615 IF p_process_flag <> jai_constants.successful THEN
616 lv_codepath := jai_general_pkg.plot_codepath(28, lv_codepath);
617 RETURN;
618 END IF;
619
620 jai_cmn_rgm_recording_pkg.update_reference(
621 p_source => p_source,
622 p_reference_id => r_ref.reference_id,
623 p_recovered_amount => ln_recovered_amount,
624 p_discounted_amount => ln_discounted_amount, -- CHK (Implementation)
625 p_process_flag => p_process_flag,
626 p_process_message => p_process_message
627 );
628
629 <<end_of_dist>>
630 lv_codepath := jai_general_pkg.plot_codepath(49, lv_codepath, 'PROCESS_PAYMENT', 'END');
631
632 IF g_debug = 'Y' THEN
633 FND_FILE.put_line( fnd_file.log, 'Codepath:'||lv_codepath);
634 END IF;
635
636 EXCEPTION
637 WHEN OTHERS THEN
638 p_process_flag := jai_constants.unexpected_error;
639 p_process_message := 'Process Payment Error:'||SQLERRM;
640 FND_FILE.put_line( fnd_file.log, p_process_message);
641 FND_FILE.put_line( fnd_file.log, 'Error Codepath:'||lv_codepath);
642
643 END process_payment;
644
645 ---------------------------- PROCESS_BATCH ---------------------------
646 PROCEDURE process_batch(
647 errbuf OUT NOCOPY VARCHAR2,
648 retcode OUT NOCOPY VARCHAR2,
649 p_regime_id IN NUMBER,
650 p_rgm_registration_num IN VARCHAR2,
651 pv_trx_from_date IN VARCHAR2,
652 pv_trx_till_date IN VARCHAR2,
653 p_called_from IN VARCHAR2, -- DEFAULT 'Batch' File.Sql.35 by Brathod
654 p_debug IN VARCHAR2, -- DEFAULT 'Y' File.Sql.35 by Brathod
655 p_trace_switch IN VARCHAR2, -- DEFAULT 'N' File.Sql.35 by Brathod
656 p_organization_id IN NUMBER DEFAULT NULL /*5694855*/
657 ) IS
658
659 ln_batch_id JAI_RGM_CONC_REQUESTS.batch_id%TYPE;
660 ln_request_id JAI_RGM_CONC_REQUESTS.request_id%TYPE;
661 ld_trx_start_date DATE;
662
663 lv_process_flag VARCHAR2(2);
664 lv_process_message VARCHAR2(1000);
665
666 /* Brathod, for Bug# 4286646*/
667 p_trx_from_date DATE; -- DEFAULT fnd_date.canonical_to_date(pv_trx_from_date) File.Sql.35 by Brathod
668 p_trx_till_date DATE; -- DEFAULT fnd_date.canonical_to_date(pv_trx_till_date) File.Sql.35 by Brathod
669 /*End of Bug# 4286646 */
670
671 CURSOR c_regime_orgs(cp_regime_id IN NUMBER, cp_orgn_type IN VARCHAR2, cp_registration_num IN VARCHAR2,p_att_type_code jai_rgm_registrations.attribute_type_code%TYPE,cp_organization_id IN NUMBER ) IS/*Bug 5879769 bduvarag*/
672 SELECT a.organization_id org_id,a.location_id /*Bug 5879769 bduvarag*/
673 FROM JAI_RGM_ORG_REGNS_V a
674 WHERE regime_id = cp_regime_id
675 AND registration_type = jai_constants.regn_type_others
676 AND attribute_type_code = p_att_type_code--rchandan for bug#4428980
677 AND organization_type = cp_orgn_type
678 AND attribute_value = cp_registration_num
679 AND a.organization_id = nvl(cp_organization_id,a.organization_id) /*5694855*/;
680
681
682 ld_rgm_effective_from JAI_RGM_DEFINITIONS.effective_date_from%TYPE;
683 CURSOR c_rgm_effective_from_date(cp_regime_id IN NUMBER) IS
684 SELECT trunc(creation_date) effective_date_from /* effective_date_from. Commneted this as part of VAT Impl. Vijay Shankar for Bug#425023(4245089) */
685 FROM JAI_RGM_DEFINITIONS
686 WHERE regime_id = cp_regime_id;
687 /*Bug 5879769 bduvarag start*/
688 CURSOR cur_fetch_ou(cp_organization_id NUMBER)
689 IS
690 SELECT org_information3
691 FROM hr_organization_information
692 WHERE upper(ORG_INFORMATION_CONTEXT) = 'ACCOUNTING INFORMATION'
693 AND organization_id = cp_organization_id;
694
695 ln_org_id NUMBER; /*5694855*/
696 /*Bug 5879769 bduvarag End*/
697 BEGIN
698
699 p_trx_from_date := fnd_date.canonical_to_date(pv_trx_from_date); --File.Sql.35 by Brathod
700 p_trx_till_date := fnd_date.canonical_to_date(pv_trx_till_date); --File.Sql.35 by Brathod
701
702 FND_FILE.put_line(fnd_file.log,'Value of from date is '||p_trx_from_date);
703 FND_FILE.put_line(fnd_file.log,'Value of from date is '||p_trx_till_date);
704
705
706 g_debug := p_debug;
707 g_debug := 'Y';
708
709 IF p_debug = 'Y' THEN
710 fnd_file.put_line(fnd_file.log, 'Enter1');
711 END IF;
712
713 /*
714 OPEN c_previous_batch_dtls(p_regime_id, p_rgm_registration_num);
715 FETCH c_previous_batch_dtls INTO ld_trx_start_date;
716 CLOSE c_previous_batch_dtls;
717
718 IF ld_trx_start_date IS NULL THEN
719 ld_trx_start_date := to_date('22-DEC-2004', 'DD-MON-YYYY'); -- TEST CODE
720 END IF;
721 */
722
723 insert_request_details(
724 p_batch_id => ln_batch_id, -- OUT parameter
725 p_regime_id => p_regime_id,
726 p_rgm_registration_num => p_rgm_registration_num,
727 p_trx_from_date => p_trx_from_date,
728 p_trx_till_date => p_trx_till_date
729 );
730
731 OPEN c_rgm_effective_from_date(p_regime_id);
732 FETCH c_rgm_effective_from_date INTO ld_rgm_effective_from;
733 CLOSE c_rgm_effective_from_date;
734
735 IF p_trx_from_date < ld_rgm_effective_from THEN
736
737 FND_FILE.put_line(fnd_file.log, 'Start Date('||to_char(p_trx_from_Date,'DD-MON-YYYY')
738 ||') of Transaction Processing cannot be less than Regime Effective Date('||to_char(ld_rgm_effective_from,'DD-MON-YYYY')||')'
739 );
740 retcode := jai_constants.request_error;
741
742 RETURN;
743
744 END IF;
745
746 ld_trx_start_date := p_trx_from_date;
747
748 FOR loop_io IN c_regime_orgs(p_regime_id, jai_constants.orgn_type_io, p_rgm_registration_num,'PRIMARY',p_organization_id) LOOP/*Bug 5879769 bduvarag*/
749
750 /* start changes by ssumaith - code review comments - bug# 6109941*/
751 OPEN cur_fetch_ou(loop_io.org_id);
752 FETCH cur_fetch_ou INTO ln_org_id;
753 CLOSE cur_fetch_ou;
754 /* ends additions by ssumaith - bug#6109941*/
755
756 /*Added by nprashar for bug # 6636517*/
757 fnd_file.put_line(fnd_file.log,'P_organization_type :' || jai_constants.orgn_type_io);
758 fnd_file.put_line(fnd_file.log,'P_organization_id :' ||ln_org_id);
759 fnd_file.put_line(fnd_file.log,'P_Location_id :' ||loop_io.location_id); /*Ends here for bug #6636517*/
760 /***************** Processing of AP Start Here *********************/
761 process_payments(
762 p_regime_id => p_regime_id,
763 p_organization_type => jai_constants.orgn_type_io, /* ssumaith - bug# 6109941 */
764 p_organization_id => loop_io.org_id,/*5694855*/ /* ssumaith 6109941 */
765 p_trx_from_date => ld_trx_start_date,
766 p_trx_to_date => p_trx_till_date,
767 p_org_id => ln_org_id,
768 p_batch_id => ln_batch_id,
769 p_debug => p_debug,
770 p_process_flag => lv_process_flag,
771 p_process_message => lv_process_message
772 );
773
774 IF lv_process_flag IN (jai_constants.expected_error, jai_constants.unexpected_error) THEN
775 GOTO end_of_batch;
776 END IF;
777
778 /***************** Processing of AR Start Here *********************/
779 jai_ar_rgm_processing_pkg.process_records(
780 p_regime_id => p_regime_id,
781 p_organization_type => jai_constants.orgn_type_io,
782 p_organization_id => loop_io.org_id ,/*5694855*/
783 p_from_date => ld_trx_start_date,
784 p_to_date => p_trx_till_date,
785 p_org_id => ln_org_id,
786 p_batch_id => ln_batch_id,
787 p_process_flag => lv_process_flag,
788 p_process_message => lv_process_message
789 );
790
791 IF lv_process_flag IN (jai_constants.expected_error, jai_constants.unexpected_error) THEN
792 GOTO end_of_batch;
793 END IF;
794
795 END LOOP; -- Operating Units loop of Registration Number
796
797 <<end_of_batch>>
798
799 IF lv_process_flag IN (jai_constants.expected_error, jai_constants.unexpected_error) THEN
800 FND_FILE.put_line( FND_FILE.log, 'Problem Message:'||lv_process_message);
801 fnd_file.put_line(fnd_file.log,'Problem Message:'||lv_process_message);
802 retcode := jai_constants.request_warning;
803 errbuf := lv_process_message;
804 END IF;
805
806 -- FINAL Commit to permanently save the transactions
807 COMMIT;
808
809 EXCEPTION
810 WHEN OTHERS THEN
811 ROLLBACK;
812 retcode := jai_constants.request_error;
813 errbuf := 'Unexpected Error Occured:'||SQLERRM;
814 FND_FILE.put_line( fnd_file.log, 'Unexpected Error Occured:'||SQLERRM);
815
816 END process_batch;
817
818 PROCEDURE process_payments(
819 p_regime_id IN NUMBER,
820 p_organization_type IN VARCHAR2,
821 p_trx_from_date IN DATE,
822 p_trx_to_date IN DATE,
823 p_org_id IN NUMBER,
824 p_batch_id IN NUMBER,
825 p_debug IN VARCHAR2,
826 p_process_flag OUT NOCOPY VARCHAR2,
827 p_process_message OUT NOCOPY VARCHAR2,
828 p_organization_id IN NUMBER DEFAULT NULL /*5694855*/
829 ) IS
830
831 v_today DATE ; -- := trunc(sysdate) -- File.Sql.35 by Brathod
832 lv_standard_lookup CONSTANT varchar2(30) := 'STANDARD'; --rchandan for bug#4428980
833 lv_debit_lookup CONSTANT varchar2(30) := 'DEBIT'; --rchandan for bug#4428980
834 --Bug 4991017. Added by Lakshmi Gopalsami
835 lv_entity_code CONSTANT varchar2(30) := 'AP_INVOICES';
836
837 CURSOR c_previous_batch_dtls(cp_regime_id IN NUMBER, cp_registration_num IN VARCHAR2) IS
838 SELECT trx_till_date+1
839 FROM jai_rgm_conc_requests
840 WHERE regime_id = cp_regime_id
841 AND rgm_registration_num = cp_registration_num;
842
843 /* Bug 4991017. Added by Lakshmi Gopalsami
844 Merged the cursors c_ap_accounted_invoices and c_event_distributions
845 because of SLA uptake by base and removed the same.
846 (1) Changed the reference to xla_ae_headers instead of ap_ae_headers_all
847 (2) Also added xla_transaction_entities to get the entity_id and
848 source_int_id_1 so that it can be joined with transaction tables.
849 (3) Discussed with shekhar and found that we should derive by
850 accounting_date and not on the creation_date.
851 (4) Added accounting_event_id in cursor
852 */
853 -- Considers only Localization Tax Distributions created from Receipt, PO, Invoice Matching
854 CURSOR c_ap_accounted_inv_dist(cp_ae_category IN VARCHAR2,
855 cp_start_date IN DATE ,
856 cp_till_date IN DATE ,
857 cp_sob_id IN NUMBER) IS/*Bug 5879769 bduvarag*/
858 SELECT aid.invoice_id, aid.invoice_distribution_id,
859 aid.distribution_line_number, aid.invoice_line_number,
860 aid.reversal_flag, aid.parent_reversal_id,
861 aid.accrual_posted_flag, aid.cash_posted_flag,
862 aid.amount, aid.base_amount,
863 aid.po_distribution_id, aid.rcv_transaction_id,
864 -- Bug 4991017 Added by Lakshmi Gopalsami.
865 -- Added accounting_event_id in cursor.
866 aid.org_id, aid.accounting_event_id,
867 ai.vendor_id, ai.vendor_site_id, ai.invoice_currency_code,
868 aid.exchange_rate, aid.exchange_rate_type, aid.exchange_date,
869 ai.source
870 FROM xla_ae_headers xah ,
871 xla_transaction_entities xte,
872 ap_invoices_all ai,
873 ap_invoice_distributions_all aid
874 WHERE xah.je_category_name = cp_ae_category
875 AND xah.ledger_id = cp_sob_id
876 AND xah.application_id =200
877 and xah.entity_id = xte.entity_id
878 AND xte.application_id = 200
879 and xte.entity_code =lv_entity_code --'AP_INVOICES'
880 and xte.source_id_int_1 = ai.invoice_id
881 AND aid.invoice_id = ai.invoice_id
882 and aid.accounting_event_id = xah.event_id
883 AND ai.invoice_type_lookup_code IN (lv_standard_lookup, lv_debit_lookup)
884 AND ai.cancelled_date IS NULL
885 AND aid.line_type_lookup_code = jai_constants.misc_line
886 AND aid.posted_flag = 'Y'
887 AND trunc(xah.accounting_date) between cp_start_date AND cp_till_date
888 AND trunc(aid.accounting_date) between cp_start_date AND cp_till_date
889 and ai.org_id = p_org_id
890 and aid.org_id = p_org_id
891 ORDER BY aid.accounting_date, aid.invoice_distribution_id;
892 /*Bug 5879769 bduvarag start*/
893 CURSOR c_prepayment_applications(cp_start_date IN DATE, cp_till_date IN DATE)
894 IS
895 SELECT invoice_id,
896 invoice_distribution_id,
897 prepay_distribution_id ,
898 amount ,
899 reversal_flag ,
900 parent_reversal_id ,
901 org_id
902 FROM ap_invoice_distributions_all
903 WHERE org_id = p_org_id
904 AND line_type_lookup_code = jai_constants.prepay_line
905 AND invoice_id IN ( SELECT invoice_id
906 FROM ap_invoice_distributions_all
907 WHERE po_distribution_id IN ( SELECT pda.po_distribution_id
908 FROM po_line_locations_all pll,
909 po_distributions_all pda,
910 jai_po_line_locations jpll
911 WHERE pll.line_location_id = jpll.line_location_id
912 AND pll.line_location_id = pda.line_location_id
913 AND pll.ship_to_organization_id = p_organization_id
914 )
915 AND ( (cp_start_date IS NULL AND creation_date < cp_till_date)
916 OR (cp_start_date IS NOT NULL AND trunc(creation_date) between cp_start_date AND cp_till_date)
917 )
918 )/*5694855*/
919 AND prepay_distribution_id IS NOT NULL
920 AND ( ( cp_start_date IS NULL AND creation_date < cp_till_date)
921 OR ( cp_start_date IS NOT NULL AND trunc(creation_date) between cp_start_date AND cp_till_date)
922 )
923
924 ORDER BY invoice_distribution_id;
925 /*Bug 5879769 bduvarag end*/
926 CURSOR c_invoice_distributions(cp_invoice_id IN NUMBER) IS
927 SELECT a.invoice_id, a.invoice_distribution_id, a.distribution_line_number, a.dist_match_type,
928 a.invoicE_line_number, /* INVOICE LINES UPTAKE */
929 a.parent_reversal_id, a.reversal_flag, a.rcv_transaction_id, a.po_distribution_id
930 FROM ap_invoice_distributions_all a, jai_rgm_trx_refs b /* second table is used for join just to take IL records */
931 WHERE a.invoice_id = cp_invoice_id
932 AND a.line_type_lookup_code = jai_constants.misc_line -- <> 'PREPAY'
933 AND b.source = jai_constants.source_ap
934 and b.invoice_id = a.invoice_id
935 and b.line_id = a.invoice_distribution_id
936 ORDER BY a.invoice_distribution_id;
937
938 CURSOR c_tax_dist_dtl(cp_regime_id IN NUMBER, cp_invoice_id IN NUMBER, cp_inv_distribution_id IN NUMBER) IS -- cp_dist_line_no IN NUMBER) IS
939 SELECT 1 chk, a.tax_id, b.tax_rate, a.tax_amount, a.parent_invoice_distribution_id, b.tax_type,
940 a.invoice_line_number, /* INVOICE LINES UPTAKE */
941 nvl(b.mod_cr_percentage,0) recoverable_ptg, a.base_amount
942 FROM JAI_AP_MATCH_INV_TAXES a, JAI_CMN_TAXES_ALL b, JAI_RGM_REGISTRATIONS c
943 WHERE a.invoice_id = cp_invoice_id
944 -- AND a.distribution_line_number = cp_dist_line_no Modified as part of AP INVOICE Lines Uptake project
945 AND a.invoice_distribution_id = cp_inv_distribution_id
946 AND a.tax_id = b.tax_id
947 AND b.tax_type = c.attribute_code
948 and c.regime_id = cp_regime_id
949 and c.registration_type = jai_constants.regn_type_tax_types
950 --
951 -- 5763527, modified and condition as below
952 AND ( mod_cr_percentage = 100
953 or ( mod_cr_percentage > 0 and mod_cr_percentage < 100 and nvl(recoverable_flag,'Y') <> 'N')
954 );
955 -- AND b.mod_cr_percentage > 0;
956
957 /* Cursor added by ssumaith - bug# 4284505*/
958 CURSOR c_tp_inv_details (cp_regime_id IN NUMBER , cp_invoice_id IN NUMBER , cp_line_number IN NUMBER) IS
959 SELECT 1 chk , a.tax_id , a.tax_rate , a.tax_amount , NULL ,a.tax_type ,
960 a.line_number invoice_line_number, /* INVOICE LINES UPTAKE */
961 NVL(b.mod_cr_percentage,0) recoverable_ptg, NULL
962 FROM jai_rcv_tp_inv_details a , JAI_CMN_TAXES_ALL b , jai_rcv_tp_invoices c, JAI_RGM_REGISTRATIONS d
963 WHERE c.invoice_id = cp_invoice_id
964 AND a.batch_invoice_id = c.batch_invoice_id
965 AND a.tax_id = b.tax_id
966 AND a.line_number = cp_line_number /*INVOICE LINES UPTAKE cp_dist_line_number */
967 AND b.tax_type = attribute_code
968 and d.regime_id = cp_regime_id
969 AND d.registration_type = jai_constants.regn_type_tax_types
970 AND b.mod_cr_percentage > 0;
971
972
973 CURSOR c_item_id(cp_po_distribution_id IN NUMBER) IS
974 SELECT b.item_id
975 FROM po_distributions_all a, po_lines_all b
976 WHERE po_distribution_id = cp_po_distribution_id
977 AND a.po_line_id = b.po_line_id;
978
979 CURSOR c_batch_references(cp_batch_id IN NUMBER, cp_source IN VARCHAR2) IS
980 SELECT distinct invoice_id
981 FROM jai_rgm_trx_refs
982 WHERE batch_id = cp_batch_id
983 AND source = cp_source;
984
985 CURSOR c_previous_payments_of_inv(cp_invoice_id IN NUMBER, cp_start_date IN DATE) IS
986 SELECT a.invoice_payment_id, a.check_id, a.amount, a.payment_base_amount, a.reversal_flag,
987 a.reversal_inv_pmt_id, a.org_id
988 FROM ap_invoice_payments_all a, ap_checks_all b
989 WHERE a.invoice_id = cp_invoice_id
990 AND a.check_id = b.check_id
991 AND a.creation_date < cp_start_date
992 AND nvl(b.future_pay_due_date, v_today) <= v_today
993 AND a.amount <> 0 /* ssumaith bug# 6104491 */
994 AND a.invoice_payment_id NOT IN (select source_document_id from jai_rgm_trx_records -- CHK is this required
995 where source = jai_constants.source_ap
996 and source_table_name = jai_constants.ap_payments
997 and source_document_id = a.invoice_payment_id
998 )
999 ORDER BY invoice_payment_id;
1000
1001 CURSOR c_previous_prepayments(cp_invoice_id IN NUMBER, cp_start_date IN DATE) IS
1002 SELECT invoice_distribution_id, reversal_flag, parent_reversal_id, amount, org_id
1003 FROM ap_invoice_distributions_all
1004 WHERE invoice_id = cp_invoice_id
1005 AND creation_date < cp_start_date
1006 ORDER BY invoice_distribution_id;
1007
1008 /*
1009 ||Cursor modified by aiyer for the bug 4947102 .
1010 ||Merged the cursors c_period_payments with c_invoice_distributions into c_period_payments
1011 */
1012 CURSOR c_period_payments( /*Bug 5879769 bduvarag*/
1013 cp_start_date IN DATE ,
1014 cp_till_date IN DATE
1015 )
1016 IS
1017 SELECT
1018 ainvd.invoice_id,
1019 ainvd.invoice_distribution_id,
1020 ainvd.distribution_line_number,
1021 ainvd.dist_match_type,
1022 ainvd.invoice_line_number, /* INVOICE LINES UPTAKE */
1023 ainvd.parent_reversal_id,
1024 ainvd.reversal_flag,
1025 ainvd.rcv_transaction_id,
1026 ainvd.po_distribution_id,
1027 apinvp.invoice_payment_id,
1028 apinvp.check_id,
1029 apinvp.amount,
1030 apinvp.org_id
1031 FROM
1032 ap_invoice_payments_all apinvp,
1033 ap_checks_all apc ,
1034 ap_invoice_distributions_all ainvd ,
1035 jai_rgm_trx_refs jrtr /* second table is used for join just to take IL records */
1036 WHERE
1037 apinvp.org_id = p_org_id
1038 AND apinvp.check_id = apc.check_id
1039 AND nvl(apc.future_pay_due_date, v_today) <= v_today
1040 AND apinvp.accounting_date/*Commented by nprashar for bug #6636517
1041 v_today*/ BETWEEN cp_start_date AND cp_till_date
1042 AND ainvd.invoice_id IN
1043 ( SELECT invoice_id
1044 FROM ap_invoice_distributions_all
1045 WHERE org_id = p_org_id
1046 AND po_distribution_id in
1047 (SELECT pda.po_distribution_id
1048 FROM po_line_locations_all pll,
1049 po_distributions_all pda,
1050 jai_po_line_locations jpll
1051 WHERE pll.line_location_id = jpll.line_location_id
1052 AND pll.line_location_id = pda.line_location_id
1053 AND pll.ship_to_organization_id = p_organization_id
1054 )
1055 /* Bug 7172723. Added by Lakshmi Gopalsami
1056 * Added union clause.
1057 */
1058 UNION
1059 SELECT jrti.invoice_id
1060 FROM jai_rcv_tp_invoices jrti
1061 WHERE jrti.vendor_id = apc.vendor_id
1062 AND jrti.vendor_site_id = apc.vendor_site_id
1063 AND apc.org_id = p_org_id
1064
1065 )/*5694855*/
1066
1067 AND ainvd.line_type_lookup_code = jai_constants.misc_line -- <> 'PREPAY'
1068 AND jrtr.source = jai_constants.source_ap
1069 AND jrtr.invoice_id = ainvd.invoice_id
1070 AND apinvp.invoice_id = ainvd.invoice_id --added by csahoo for bug#6436576
1071 AND jrtr.line_id = ainvd.invoice_distribution_id
1072 ORDER BY
1073 apinvp.invoice_payment_id ,
1074 ainvd.invoice_distribution_id;
1075
1076 CURSOR c_invoice_batch_refs(cp_source IN VARCHAR2, cp_batch_id IN NUMBER, cp_invoice_id IN NUMBER) IS
1077 SELECT *
1078 FROM jai_rgm_trx_refs
1079 WHERE source = cp_source
1080 AND batch_id = cp_batch_id
1081 AND invoice_id = cp_invoice_id
1082 AND reversal_flag IS NULL
1083 ORDER by invoice_id, line_id;
1084
1085 /* Bug 5243532. Added by Lakshmi Gopalsami
1086 removed the cursor c_sob_of_ou and implemented using caching
1087 logic.
1088 */
1089 CURSOR c_payment_chk(cp_source IN VARCHAR2, cp_source_table_name IN VARCHAR2, cp_source_document_id IN NUMBER) IS
1090 SELECT 1
1091 FROM jai_rgm_trx_records
1092 WHERE source = jai_constants.source_ap
1093 AND source_table_name = jai_constants.ap_payments
1094 AND source_document_id = cp_source_document_id;
1095
1096 /*OPEN c_payment_chk(jai_constants.source_ap, jai_constants.ap_payments, inv_payment.invoice_payment_id);
1097 FETCH c_payment_chk INTO ln_chk;
1098 CLOSE c_payment_chk; */
1099
1100 r_ref c_reference%ROWTYPE;
1101 r_parent_ref c_reference%ROWTYPE;
1102 r_parent_dist c_invoice_distribution%ROWTYPE;
1103
1104 r_tax_dist_dtl c_tax_dist_dtl%ROWTYPE;
1105
1106 ln_item_line_id NUMBER(15); -- Incase of AP -> AP_INVOICE_DISTRIBUTIONS_ALL.invoice_distribution_id%TYPE;
1107 ln_item_id MTL_SYSTEM_ITEMS.inventory_item_id%TYPE;
1108 ln_reference_id JAI_RGM_TRX_REFS.reference_id%TYPE;
1109 ln_parent_reference_id JAI_RGM_TRX_REFS.parent_reference_id%TYPE;
1110 ln_taxable_basis NUMBER;
1111
1112 ln_rgm_reposotory_id JAI_RGM_TRX_RECORDS.repository_id%TYPE;
1113 ln_recovered_amount JAI_RGM_TRX_REFS.recovered_amount%TYPE;
1114 ln_recoverable_amount JAI_RGM_TRX_REFS.recoverable_amount%TYPE;
1115
1116 ln_sob_id GL_SETS_OF_BOOKS.set_of_books_id%TYPE;
1117 lv_src_trx_type JAI_RGM_TRX_RECORDS.source_trx_type%TYPE;
1118 lv_process_flag VARCHAR2(2);
1119 lv_process_message VARCHAR2(1000);
1120
1121 ln_commit_interval NUMBER(5) := 500;
1122 ln_uncommited_trxs NUMBER(6) := 0;
1123
1124 /* Bug 5243532. Added by Lakshmi Gopalsami
1125 Defined variahle for caching logic.
1126 */
1127 l_func_curr_det jai_plsql_cache_pkg.func_curr_details;
1128 BEGIN
1129
1130 v_today := trunc(sysdate); -- File.Sql.35 by Brathod
1131
1132 /* Bug 5243532. Added by Lakshmi Gopalsami
1133 removed the cursor c_sob_of_ou and implemented using caching
1134 logic.
1135 */
1136 l_func_curr_det := jai_plsql_cache_pkg.return_sob_curr
1137 (p_org_id => p_org_id );
1138 ln_sob_id := l_func_curr_det.ledger_id;
1139
1140
1141 IF p_debug = 'Y' THEN fnd_file.put_line(fnd_file.log, 'AAA Enter2 - Organization id:'||p_organization_id||'OU ID:'||p_org_id||',sob:'||ln_sob_id); END IF;/*Bug 5879769 bduvarag*/
1142
1143 -- this is required for to rollback the changes made in this procedure incase any unexpected error
1144 -- SAVEPOINT start_payments;
1145
1146 -- ~~~~~~~~~~ Payables Processing ~~~~~~~~~~
1147 -- Logic to Insert data into REFERENCEs Table
1148 -- if the invoice is cancelled, then no references are populated so that the lines are not processed against any
1149 -- Payements/Voids that are present for this invoice
1150 FOR ap_acc_dist IN c_ap_accounted_inv_dist( 'Purchase Invoices', p_trx_from_date, p_trx_to_date, ln_sob_id) LOOP/*Bug 5879769 bduvarag*/
1151
1152 IF p_debug = 'Y' THEN fnd_file.put_line(fnd_file.log, 'Enter3 - ap_event_id:'||ap_acc_dist.accounting_event_id); END IF;
1153
1154 --Bug 4991017. Added by Lakshmi gopalsami
1155 -- Removed the FOR.. LOOP dist.
1156
1157 IF p_debug = 'Y' THEN fnd_file.put_line(fnd_file.log, 'Enter4 - invid:'||ap_acc_dist.invoice_id
1158 ||', LineNum:'||ap_acc_dist.invoice_line_number ||',distid:'||ap_acc_dist.distribution_line_number); END IF;
1159
1160 -- Initialization Point
1161 ln_reference_id := null;
1162 ln_item_line_id := null;
1163 ln_item_id := null;
1164
1165 r_ref := null;
1166 r_tax_dist_dtl := null;
1167 r_parent_dist := null;
1168 r_parent_ref := null;
1169
1170 OPEN c_reference(jai_constants.source_ap, ap_acc_dist.invoice_id, ap_acc_dist.invoice_distribution_id); -- , r_tax_dist_dtl.tax_id);
1171 FETCH c_reference INTO r_ref;
1172 CLOSE c_reference;
1173
1174 -- following condition is satisfied if the invoice line is already inserted into REFERENCEs table
1175 IF r_ref.reference_id IS NOT NULL THEN
1176 IF p_debug = 'Y' THEN
1177 fnd_file.put_line(fnd_file.log, 'Enter5 - Return Ref NotNull');
1178 END IF;
1179 GOTO end_of_reference_insertion;
1180 END IF;
1181
1182 -- ~~~~~~~~~~~~~~~~~~~~~~~~~ POPULATION Logic for Data Entry into JAI_RGM_TRX_REFS ~~~~~~~~~~~~~~~~~~~~~~~~
1183 -- following condition is satisfied for REVERSAL lines
1184 IF ap_acc_dist.reversal_flag = 'Y' AND ap_acc_dist.parent_reversal_id IS NOT NULL THEN
1185 OPEN c_invoice_distribution(ap_acc_dist.parent_reversal_id);
1186 FETCH c_invoice_distribution INTO r_parent_dist;
1187 CLOSE c_invoice_distribution;
1188
1189 OPEN c_tax_dist_dtl(p_regime_id, r_parent_dist.invoice_id, r_parent_dist.invoice_distribution_id ); -- distribution_line_number );
1190 FETCH c_tax_dist_dtl INTO r_tax_dist_dtl;
1191 CLOSE c_tax_dist_dtl;
1192
1193 ELSIF NVL(ap_acc_dist.source,'$$$') = 'INDIA TAX INVOICE' THEN --'RECEIPT' THEN --Ramanand for bug#4388958
1194 /*
1195 || above elsif added by ssumaith - bug# 4284505
1196 || NVL(ap_acc_dist.source,'$$$') = 'INDIA TAX INVOICE' --'RECEIPT' - It means a third party invoice.is being processed. ----Ramanand for bug#4388958
1197
1198 || nvl(r_tax_dist_dtl.chk, 0) = 0 means that no records were found in the JAI_AP_MATCH_INV_TAXES table.
1199 || For third party invoices , there will be no records in the JAI_AP_MATCH_INV_TAXES table
1200 || For third party invoices , the tax details need to be picked up from the jai_rcv_tp_inv_details table.
1201 || It should be joined to the jai_rcv_tp_.invoices table based on the batch_invoice_id column and
1202 || we arrive at the correct batch_invoice_id based on the invoice_id link between the third party
1203 || invoice and jai_rcv_tp_invoices table.
1204 || Using this link, if the r_tax_dist_dtl is populated, it will take its normal course.
1205 */
1206
1207 OPEN c_tp_inv_details(p_regime_id , ap_acc_dist.invoice_id , ap_acc_dist.invoice_line_number); /* INVOICE LINES UPTAKE distribution_line_number); */
1208 FETCH c_tp_inv_Details INTO r_tax_dist_dtl;
1209 CLOSE c_tp_inv_Details;
1210
1211 -- Normal Distribution and not a Reversal and not a third party distribution
1212 ELSE
1213 OPEN c_tax_dist_dtl(p_regime_id, ap_acc_dist.invoice_id, ap_acc_dist.invoice_distribution_id); -- AP INVOICE LINES UPTAKE ap_acc_dist.distribution_line_number); --
1214 FETCH c_tax_dist_dtl INTO r_tax_dist_dtl;
1215 CLOSE c_tax_dist_dtl;
1216
1217 END IF;
1218
1219 IF nvl(r_tax_dist_dtl.chk, 0) = 0 THEN
1220 IF p_debug = 'Y' THEN
1221 fnd_file.put_line(fnd_file.log, 'Enter6 - DistChk is 0');
1222 END IF;
1223
1224 GOTO end_of_reference_insertion;
1225 END IF;
1226
1227 IF r_tax_dist_dtl.recoverable_ptg = 0 THEN
1228 IF p_debug = 'Y' THEN
1229 fnd_file.put_line(fnd_file.log, 'Enter7 - recov_ptg is 0');
1230 END IF;
1231 FND_FILE.put_line( FND_FILE.log, 'Invoice_id, LineNum, DistNum->'||ap_acc_dist.invoice_id
1232 ||','||ap_acc_dist.invoice_line_number||','||ap_acc_dist.distribution_line_number||' is not Recoverable');
1233 GOTO end_of_reference_insertion;
1234 END IF;
1235
1236 IF r_tax_dist_dtl.parent_invoice_distribution_id IS NOT NULL THEN
1237 ln_item_line_id := r_tax_dist_dtl.parent_invoice_distribution_id;
1238 /* Bug 7172723. Added by Lakshmi Gopalsami
1239 * If it is third party invoice there is no reference item line.
1240 * and so the dist line itself is the parent.
1241 * assigning the invoice_distribution_id of ST tax itself
1242 */
1243 ELSIF NVL(ap_acc_dist.source,'$$$') = 'INDIA TAX INVOICE' THEN
1244 ln_item_line_id := ap_acc_dist.invoice_distribution_id;
1245
1246 ELSE
1247 ln_item_line_id := get_item_line_id(
1248 p_invoice_id => ap_acc_dist.invoice_id,
1249 p_po_distribution_id => ap_acc_dist.po_distribution_id,
1250 p_rcv_transaction_id => ap_acc_dist.rcv_transaction_id
1251 );
1252 END IF;
1253
1254 OPEN c_item_id(ap_acc_dist.po_distribution_id);
1255 FETCH c_item_id INTO ln_item_id;
1256 CLOSE c_item_id;
1257
1258 IF ap_acc_dist.parent_reversal_id is not null then /* condition introduced for AP LINES Uptake Project */
1259 OPEN c_reference(jai_constants.source_ap, ap_acc_dist.invoice_id, ap_acc_dist.parent_reversal_id); -- , r_tax_dist_dtl.tax_id);
1260 FETCH c_reference INTO r_parent_ref;
1261 CLOSE c_reference;
1262 END IF;
1263
1264 ln_recoverable_amount := ap_acc_dist.amount ; /* r_tax_dist_dtl.recoverable_ptg/100 commented for bug 7684820 */
1265 lv_process_flag := null;
1266
1267 savepoint start_of_ref;
1268
1269 jai_cmn_rgm_recording_pkg.insert_reference(
1270 p_reference_id => ln_reference_id, -- OUT Variable
1271 p_organization_id => p_organization_id,/*5694855*/
1272 p_source => jai_constants.source_ap,
1273 p_invoice_id => ap_acc_dist.invoice_id,
1274 p_line_id => ap_acc_dist.invoice_distribution_id,
1275 p_tax_type => r_tax_dist_dtl.tax_type,
1276 p_tax_id => r_tax_dist_dtl.tax_id,
1277 p_tax_rate => r_tax_dist_dtl.tax_rate,
1278 p_recoverable_ptg => r_tax_dist_dtl.recoverable_ptg,
1279 p_recoverable_amount => ln_recoverable_amount,
1280 p_party_type => jai_constants.party_type_vendor,
1281 p_party_id => ap_acc_dist.vendor_id,
1282 p_party_site_id => ap_acc_dist.vendor_site_id,
1283 p_tax_amount => ap_acc_dist.amount,
1284 p_recovered_amount => 0,
1285 p_taxable_basis => r_tax_dist_dtl.base_amount, -- CHK << what amount i should populate >>
1286 p_item_line_id => ln_item_line_id,
1287 p_item_id => ln_item_id,
1288 p_trx_tax_amount => ap_acc_dist.amount,
1289 p_trx_currency => ap_acc_dist.invoice_currency_code,
1290 p_curr_conv_date => ap_acc_dist.exchange_date,
1291 p_curr_conv_rate => ap_acc_dist.exchange_rate,
1292 p_parent_reference_id => r_parent_ref.reference_id,
1293 p_reversal_flag => ap_acc_dist.reversal_flag,
1294 p_batch_id => p_batch_id,
1295 p_process_flag => lv_process_flag,
1296 p_process_message => lv_process_message
1297 );
1298
1299 IF lv_process_flag IN (jai_constants.expected_error, jai_constants.unexpected_error) THEN
1300 -- RAISE_APPLICATION_ERROR( -20201, p_process_flag||':'||p_process_message);
1301 -- ERROR RECORDING should be there for all errored records, so that we can code the processing in future. CHK
1302 ROLLBACK TO start_of_ref;
1303 p_process_flag := lv_process_flag;
1304 p_process_message := lv_process_message;
1305 END IF;
1306
1307 ln_uncommited_trxs := ln_uncommited_trxs + 1;
1308 IF ln_uncommited_trxs >= ln_commit_interval THEN
1309 COMMIT;
1310 ln_uncommited_trxs := 0;
1311 END IF;
1312
1313 IF p_debug = 'Y' THEN fnd_file.put_line(fnd_file.log, 'Enter8 - Inserted Reference:'||ln_reference_id); END IF;
1314
1315 <<end_of_reference_insertion>>
1316 NULL;
1317
1318 -- Bug 4991017. Added by Lakshmi Gopalsami.
1319 -- Removed the END LOOP as the two cursors has been merged.
1320
1321 END LOOP; -- ap_acc_dist for Operating Unit
1322
1323 -- Logic to Make Register Entry for the Invoice Distributions that are populated into REFERENCES table and which are PAID
1324 -- Prior to the start date of this concurrent program. This is because localization only considers invoices that are accounted
1325 FOR invo IN c_batch_references(p_batch_id, jai_constants.source_ap) LOOP
1326
1327 -- Logic to Process the PAST DATED PAYMENTS that are not processed due to Invoice Accounting did not happen
1328 FOR inv_payment IN c_previous_payments_of_inv(invo.invoice_id, p_trx_from_date) LOOP
1329
1330 FOR dist IN c_invoice_batch_refs(jai_constants.source_ap, p_batch_id, invo.invoice_id) LOOP
1331
1332 lv_process_flag := null;
1333
1334 SAVEPOINT process_payment;
1335
1336 process_payment(
1337 p_batch_id => p_batch_id,
1338 p_regime_id => p_regime_id,
1339 p_org_id => inv_payment.org_id,
1340 p_source => jai_constants.source_ap,
1341 p_payment_table_name => jai_constants.ap_payments,
1342 p_payment_document_id => inv_payment.invoice_payment_id,
1343 p_invoice_id => dist.invoice_id,
1344 p_inv_dist_id => dist.line_id,
1345 p_inv_accounting_chk_done => jai_constants.yes,
1346 p_process_flag => lv_process_flag,
1347 p_process_message => lv_process_message
1348 );
1349
1350
1351 -- "FP" Means means future payment and it is not yet matured, so
1352 --IF p_process_flag = 'FP' THEN
1353 --ELS
1354 IF lv_process_flag IN (jai_constants.expected_error, jai_constants.unexpected_error) THEN
1355 ROLLBACK TO process_payment;
1356 --retcode := jai_constants.request_warning;
1357 p_process_flag := lv_process_flag;
1358 p_process_message := lv_process_message;
1359 END IF;
1360
1361 ln_uncommited_trxs := ln_uncommited_trxs + 1;
1362 IF ln_uncommited_trxs >= ln_commit_interval THEN
1363 COMMIT;
1364 ln_uncommited_trxs := 0;
1365 END IF;
1366
1367 END LOOP; -- invoice distributions
1368
1369 END LOOP; -- invoice payments
1370
1371 -- Logic to Process the PAST DATED PREPAYMENTS that are not processed due to Invoice Accounting did not happen
1372 FOR pp IN c_period_payments( p_trx_from_date, p_trx_to_date) LOOP
1373
1374 FOR dist IN c_invoice_batch_refs(jai_constants.source_ap, p_batch_id, invo.invoice_id) LOOP
1375
1376 lv_process_flag := null;
1377
1378 SAVEPOINT process_prepayment;
1379 process_payment(
1380 p_batch_id => p_batch_id,
1381 p_regime_id => p_regime_id,
1382 p_org_id => pp.org_id,
1383 p_source => jai_constants.source_ap,
1384 p_payment_table_name => jai_constants.ap_prepayments,
1385 p_payment_document_id => pp.invoice_distribution_id,
1386 p_invoice_id => dist.invoice_id,
1387 p_inv_dist_id => dist.line_id,
1388 p_inv_accounting_chk_done => jai_constants.yes,
1389 p_process_flag => lv_process_flag,
1390 p_process_message => lv_process_message
1391 );
1392
1393 IF lv_process_flag IN (jai_constants.expected_error, jai_constants.unexpected_error) THEN
1394 ROLLBACK TO process_prepayment;
1395 -- retcode := jai_constants.request_warning;
1396 p_process_flag := lv_process_flag;
1397 p_process_message := lv_process_message;
1398 END IF;
1399
1400 ln_uncommited_trxs := ln_uncommited_trxs + 1;
1401 IF ln_uncommited_trxs >= ln_commit_interval THEN
1402 COMMIT;
1403 ln_uncommited_trxs := 0;
1404 END IF;
1405
1406 END LOOP; -- invoice distributions
1407
1408 END LOOP; -- invoice prepayments
1409
1410 END LOOP; -- batch_references
1411
1412 -- Logic to Process Payments that fall for the specified period
1413 /*
1414 ||Cursor for Loops c_period_payments and c_invoice_distributions merged into c_period_payments by aiyer for the bug 4947102
1415 || This has been done to derive performance improvement
1416 || SQL ID 14828450
1417 */
1418 FOR inv_payment IN c_period_payments( p_trx_from_date, p_trx_to_date) LOOP
1419 lv_process_flag := null;
1420
1421 IF g_debug='Y' THEN fnd_file.put_line(fnd_file.log,'PeriodPay. Inv,DistId:'||inv_payment.invoice_id||','||inv_payment.invoice_distribution_id); END IF;
1422
1423 SAVEPOINT process_payment;
1424
1425 process_payment(
1426 p_batch_id => p_batch_id,
1427 p_regime_id => p_regime_id,
1428 p_org_id => inv_payment.org_id,
1429 p_source => jai_constants.source_ap,
1430 p_payment_table_name => jai_constants.ap_payments,
1431 p_payment_document_id => inv_payment.invoice_payment_id,
1432 p_invoice_id => inv_payment.invoice_id,
1433 p_inv_dist_id => inv_payment.invoice_distribution_id,
1434 p_inv_accounting_chk_done => jai_constants.no,
1435 p_process_flag => lv_process_flag,
1436 p_process_message => lv_process_message
1437 );
1438
1439 IF lv_process_flag IN (jai_constants.expected_error, jai_constants.unexpected_error) THEN
1440 ROLLBACK TO process_payment;
1441 -- retcode := jai_constants.request_warning;
1442 p_process_flag := lv_process_flag;
1443 p_process_message := lv_process_message;
1444 END IF;
1445
1446 ln_uncommited_trxs := ln_uncommited_trxs + 1;
1447 IF ln_uncommited_trxs >= ln_commit_interval THEN
1448 COMMIT;
1449 ln_uncommited_trxs := 0;
1450 END IF;
1451
1452 END LOOP;
1453
1454 -- Logic to Process Prepayment Applications onto standard invoices that fall in the processing period
1455 FOR pp IN c_prepayment_applications(p_trx_from_date, p_trx_to_date) LOOP
1456
1457 FOR dist IN c_invoice_distributions(pp.invoice_id) LOOP
1458
1459 lv_process_flag := null;
1460
1461 SAVEPOINT process_prepayment;
1462
1463 process_payment(
1464 p_batch_id => p_batch_id,
1465 p_regime_id => p_regime_id,
1466 p_org_id => pp.org_id,
1467 p_source => jai_constants.source_ap,
1468 p_payment_table_name => jai_constants.ap_prepayments,
1469 p_payment_document_id => pp.invoice_distribution_id,
1470 p_invoice_id => dist.invoice_id,
1471 p_inv_dist_id => dist.invoice_distribution_id,
1472 p_inv_accounting_chk_done => jai_constants.no,
1473 p_process_flag => lv_process_flag,
1474 p_process_message => lv_process_message
1475 );
1476
1477 IF lv_process_flag IN (jai_constants.expected_error, jai_constants.unexpected_error) THEN
1478 ROLLBACK TO process_prepayment;
1479 -- retcode := jai_constants.request_warning;
1480 p_process_flag := lv_process_flag;
1481 p_process_message := lv_process_message;
1482 END IF;
1483
1484 ln_uncommited_trxs := ln_uncommited_trxs + 1;
1485 IF ln_uncommited_trxs >= ln_commit_interval THEN
1486 COMMIT;
1487 ln_uncommited_trxs := 0;
1488 END IF;
1489
1490 END LOOP;
1491
1492 END LOOP; -- Prepayments
1493
1494 EXCEPTION
1495 WHEN OTHERS THEN
1496 ROLLBACK;
1497 -- retcode := jai_constants.request_error;
1498 -- errbuf := 'Unexpected Error Occured:'||SQLERRM;
1499 p_process_flag := jai_constants.unexpected_error;
1500 p_process_message := 'Unexpected Error Occured in Process_Payments:'||SQLERRM;
1501 FND_FILE.put_line( fnd_file.log, 'Unexpected Error Occured:'||p_process_message);
1502
1503 END process_payments;
1504
1505 /*
1506 CREATED BY : ssumaith
1507 CREATED DATE : 15-MAR-2005
1508 ENHANCEMENT BUG : 4245053
1509 PURPOSE : wrapper program to interpret the input parameters and suitably call program to
1510 generate vat imvoice number and pass accounting during shipment
1511 CALLED FROM : Concurrent program JAIVATP
1512
1513 */
1514
1515
1516 -- foll function created by kunkumar - for seperate vat invoice num for unreg dealers - bug# 5233925
1517
1518
1519 FUNCTION check_reg_dealer ( pn_customer_id NUMBER ,
1520 pn_site_use_id NUMBER ) return boolean
1521
1522 IS
1523 ln_address_id NUMBER;
1524 lv_regno JAI_CMN_CUS_ADDRESSES.vat_Reg_no%type;
1525
1526
1527 CURSOR c_get_address is
1528 SELECT hzcas.cust_acct_site_id
1529 FROM hz_cust_site_uses_all hzcsu ,
1530 hz_cust_acct_sites_all hzcas
1531 WHERE hzcas.cust_acct_site_id = hzcsu.cust_acct_site_id
1532 AND hzcsu.site_use_id = pn_site_use_id
1533 AND hzcas.cust_account_id = pn_customer_id ;
1534
1535 CURSOR c_regno (pn_address_id NUMBER) IS
1536 SELECT vat_Reg_no
1537 FROM JAI_CMN_CUS_ADDRESSES
1538 WHERE customer_id = pn_customer_id
1539 AND address_id = pn_address_id;
1540
1541 BEGIN
1542
1543 open c_get_address;
1544 fetch c_get_address into ln_address_id;
1545 close c_get_address;
1546 IF ln_address_id IS NOT NULL THEN
1547
1548 open c_regno (ln_address_id);
1549 fetch c_regno into lv_regno;
1550 close c_regno;
1551 END IF;
1552
1553 IF lv_regno IS NULL THEN
1554 return (false);
1555 ELSE
1556 return (true);
1557 END IF;
1558
1559
1560 END check_reg_dealer;
1561
1562 /*
1563 || kunkumar - for seperate vat invoice num for unreg dealers - bug# 5233925
1564 */
1565
1566
1567
1568
1569 PROCEDURE process (
1570 retcode OUT NOCOPY VARCHAR2,
1571 errbuf OUT NOCOPY VARCHAR2,
1572 p_regime_id JAI_RGM_DEFINITIONS.REGIME_ID%TYPE,
1573 p_registration_num JAI_RGM_TRX_RECORDS.REGIME_PRIMARY_REGNO%TYPE,
1574 p_organization_id JAI_OM_WSH_LINES_ALL.ORGANIZATION_ID%TYPE,
1575 p_location_id JAI_OM_WSH_LINES_ALL.LOCATION_ID%TYPE,
1576 p_delivery_id_from JAI_OM_WSH_LINES_ALL.DELIVERY_ID%TYPE,
1577 p_delivery_id_to JAI_OM_WSH_LINES_ALL.DELIVERY_ID%TYPE,
1578 pv_delivery_date_from VARCHAR2, --DATE, Harshita for Bug 4918870
1579 pv_delivery_date_to VARCHAR2, --DATE, Harshita for Bug 4918870
1580 p_process_action VARCHAR2,
1581 p_single_invoice_num VARCHAR2,
1582 p_override_invoice_date VARCHAR2, /* aiyer for the bug 5369250 */
1583 p_debug VARCHAR2
1584 )
1585 IS
1586 /*************************************************************************************************************************************
1587 Purpose:-
1588 || It processes single / multiple deliveries based on the parameters entered.
1589 || In a loop , each delivery is processed and two tasks are done based upon the p_process_action parameter
1590 || If the p_process_action = 'Generate Invoice Number' or p_process_action = 'All' then the subsection a) happens.
1591 || If the p_process_action = 'Process Accounting' or p_process_action = 'All' then the subsection b) happens.
1592 ||
1593 || a) make a call to an api to generate vat invoice number depending on various settings
1594 || a.1) If the Parameter p_single_invoice_num is set to 'Y' , then for all the deliveries of a cust / cust site a single
1595 || invoice number is generated. The call happen to the procedure to generate the vat invoice number just once
1596 || and the same value retained for this record set of same cust / cust site
1597 || a.2) If the parameter p_single_invoice_num is set to 'N' , then for each delivery a seperate vat invoice number
1598 || will be generated.
1599 || If the generate vat invoice number api returns error , then the subsection b will not be processed and the delivery
1600 || will be flagged as errored in the table JAI_RGM_INVOICE_GEN_T for the delivery_id
1601 ||
1602 || b) make a call to the api to process accounting
1603 ||
1604 || b.1 If it returns success then if the p_process_action = 'All' then flag both the fields VAT_INV_GEN_STATUS
1605 || and VAT_ACCT_STATUS are to be set to completed - 'C'
1606 || commit the delivery and continue with the next delivery
1607 ||
1608 || If it returns error (either expected error or unexpected error) and if the p_process_action = 'All' then
1609 || flag the fields VAT_INV_GEN_STATUS and VAT_ACCT_STATUS as - Errored 'E'
1610 || Rollback the delivery and continue with the next delivery.
1611 ||
1612 Change History -
1613 1. 29/07/2005 Aiyer - bug# 4523205 - File version 120.2 - (R12 Forward Porting FROM 11.5 bugs 4348774, 4357984)
1614
1615 Issues :
1616 -------
1617 1. The concurrent program is picking up all records irrespective of the registration number passed
1618 in the parameter.
1619 2. (Logged in bug 4534166) Returning clause is not required, hence needs to be removed 4357984.
1620
1621 Fix :
1622 -----
1623 1.The issue has been fixed by adding the p_registration_num and p_Regime_id in the where clause.
1624 2.As the returning clause was not required and hence was removed. Also added the fnd_file log in
1625 the exception section of the procedure.
1626
1627 Dependency due to this bug:-
1628 None
1629 2. 05-Jul-2006 Aiyer for the bug 5369250, Version 120.7
1630 Issue:-
1631 --------
1632 The concurrent failes with the following error :-
1633 "FDPSTP failed due to ORA-01861: literal does not match format string ORA-06512: at line 1 "
1634
1635 Reason:-
1636 ---------
1637 The procedure PROCESS had a parameters p_override_invoice_date of type date , however the concurrent program
1638 passes it in the canonical format and hence the failure.
1639
1640 Fix:-
1641 -----------
1642 Modified the procedure update_excise_invoice_no.
1643 Changed the datatype of p_override_invoice_date from date to varchar2 as this parameter.
1644 Also added the new parameter ld_override_invoice_date . The value in p_override_invoice_date would be converted to date format and
1645 stored in the local variable ld_override_invoice_date.
1646
1647 Dependency due to this fix:-
1648 None
1649
1650 3. 3-Feb-2007 srjayara for bug 4702156, file version 120.8
1651 Forward porting for 11i bug#4542996
1652
1653 Issue:-
1654 --------
1655 VAT invoice number and accounting was not happening for all the delivery lines in a delivery.
1656
1657 Fix:-
1658 ------
1659 Possible reason identified is that the all lines are not inventory interfaced at the same time and
1660 hence only those lines which are inventory interfaced are considered at the time vat processing concurrent
1661 runs.
1662 Added a check that only if all the delivery details are inventory interfaced , the delivery needs to be considered.
1663
1664 4. 4-jun-2007 ssumaith - bug#6109941 -
1665 The Service tax by IO code was incorrectly forward ported to R12. There were some code missing and operating unit was being passed instead of inventory org. Such code has been corrected.
1666
1667
1668 5 07-jun-2007 ssumaith - bug# 6109941 - divisor by zero error was coming . this has been resolved by checking
1669 for zero divides before the divide is done.
1670
1671 6 25-jun-2007 ssumaith - bug#6147385 - when all delivery details in a delivery are not interfaced trip stopped
1672 then, the program was returning instead of processing the next delivery.
1673 It was because of a return statement, instead added the code to process the next delivery and increment the failure counter.
1674
1675 Adde the nvl condition in the where clauseto use the table's registration number its passed as null
1676 11. 12-Jul-2007 CSahoo for bug#6176277, File Version 120.20
1677 assigned the variable ln_excise_invoice_not_done to NULL before opening the cursor.
1678
1679 12. 13-jul-2007 ssumaith - bug# 6176277 - The variable - lv_inv_gen_process_flag was not re-initialised
1680 re-initialised the variables - lv_inv_gen_process_flag , lv_inv_gen_process_message to NULL
1681
1682 **************************************************************************************************************************************/
1683
1684 lv_acct_process_flag VARCHAR2(10);
1685 lv_inv_gen_process_flag VARCHAR2(10);
1686 lv_inv_gen_process_message VARCHAR2(1996);
1687 lv_acct_process_message VARCHAR2(1996);
1688 lv_invoice_generated VARCHAR2(100);
1689 lv_vat_invoice_number VARCHAR2(100);
1690 ln_failure_delivery_ctr NUMBER;
1691 ln_success_delivery_Ctr NUMBER;
1692 ln_regime_id NUMBER;
1693 lv_debug VARCHAR2(5); -- := jai_constants.no /* This should be either 'Y' or 'N' */ File.Sql.35 by Brathod
1694 ln_order_type_id JAI_OM_WSH_LINES_ALL.ORDER_TYPE_ID%TYPE;
1695 lv_inv_num_already_generated VARCHAR2(10); --:= jai_constants.value_false File.Sql.35 by Brathod
1696 ln_batch_id NUMBER;
1697 lv_regime_code JAI_RGM_DEFINITIONS.REGIME_CODE%TYPE;
1698 ln_current_party_id NUMBER;
1699 ln_current_party_site_id NUMBER;
1700 lv_party_has_changed VARCHAR2(10);
1701 ln_conc_progam_id NUMBER;
1702 ln_conc_request_id NUMBER;
1703 ln_conc_prog_appl_id NUMBER;
1704 lv_Same_invoice_no VARCHAR2(100);
1705 lv_Excise_invoice_no JAI_OM_WSH_LINES_ALL.EXCISE_INVOICE_NO%TYPE;
1706 ld_excise_invoice_date JAI_OM_WSH_LINES_ALL.EXCISE_INVOICE_DATE%TYPE;
1707 lb_completion_status BOOLEAN;
1708 ld_override_invoice_date DATE; /* aiyer for the bug 5369250 */
1709 lv_doc_type_class varchar2(2); /*kunkumar for bug #5233925*/
1710 CURSOR c_regime_cur(cp_trx_Date DATE) IS
1711 SELECT regime_id
1712 FROM JAI_RGM_DEFINITIONS
1713 WHERE regime_code = jai_constants.vat_regime;
1714
1715
1716 CURSOR c_shipment_info(cp_Delivery_id JAI_OM_WSH_LINES_ALL.DELIVERY_ID%TYPE) IS
1717 SELECT order_type_id , excise_invoice_no
1718 FROM JAI_OM_WSH_LINES_ALL
1719 WHERE delivery_id = cp_delivery_id;
1720
1721 CURSOR c_same_inv_no(cp_organization_id JAI_OM_WSH_LINES_ALL.ORGANIZATION_ID%TYPE , cp_location_id JAI_OM_WSH_LINES_ALL.location_id%TYPE ) IS
1722 SELECT attribute_Value
1723 FROM JAI_RGM_ORG_REGNS_V
1724 WHERE regime_id = p_regime_id
1725 AND attribute_type_code = jai_constants.regn_type_others
1726 AND attribute_code = jai_constants.attr_code_same_inv_no
1727 AND organization_id = cp_organization_id
1728 AND location_id = cp_location_id;
1729
1730 CURSOR c_excise_invoice_not_done ( cp_Delivery_id JAI_OM_WSH_LINES_ALL.DELIVERY_ID%TYPE)IS
1731 SELECT 1
1732 FROM JAI_OM_OE_GEN_TAXINV_T
1733 WHERE delivery_id = cp_delivery_id;
1734
1735 ln_excise_invoice_not_done NUMBER;
1736
1737 /*srjayara for bug 4702156*/
1738
1739 /*
1740 || The following cursor is added to check that all the delivery lines in the delivery are inventory interfaced
1741 */
1742 CURSOR c_check_interface_status (cp_delivery_id NUMBER) IS
1743 SELECT 1
1744 FROM
1745 wsh_delivery_details wdd ,
1746 wsh_new_deliveries wnd ,
1747 wsh_delivery_assignments wda
1748 WHERE
1749 wdd.delivery_detail_id = wda.delivery_detail_id AND
1750 wda.Delivery_Id = wnd.Delivery_Id AND
1751 wnd.Delivery_Id = cp_delivery_id AND
1752 wdd.source_code = 'OE' AND
1753 NVL(wdd.inv_interfaced_flag,'N') <> 'Y';
1754
1755 ln_interface_status NUMBER;
1756
1757 /*end bug 4702156*/
1758
1759 -- Harshita for Bug 4918870
1760 p_delivery_date_from DATE DEFAULT fnd_date.canonical_to_date(pv_delivery_date_from);
1761 p_delivery_date_to DATE DEFAULT fnd_date.canonical_to_date(pv_delivery_date_to);
1762
1763 BEGIN
1764 /*
1765 ||aiyer for the bug 5369250
1766 ||convert from canonical to date format
1767 */
1768 ld_override_invoice_date := fnd_date.canonical_to_date(p_override_invoice_date);
1769 lv_inv_num_already_generated := jai_constants.value_false; -- File.Sql.35 by Brathod
1770 lv_debug := NVL(P_DEBUG,jai_constants.no);
1771 ln_current_party_id := -9999;
1772 ln_current_party_site_id := -9999;
1773 IF lv_debug = 'Y' THEN
1774 Fnd_File.PUT_LINE(Fnd_File.LOG, ' 1. Entered in the proc with parameters :');
1775 Fnd_File.PUT_LINE(Fnd_File.LOG, ' P_REGIME_ID :' || P_REGIME_ID);
1776 Fnd_File.PUT_LINE(Fnd_File.LOG, ' P_REGISTRATION_NUM :' || P_REGISTRATION_NUM);
1777 Fnd_File.PUT_LINE(Fnd_File.LOG, ' P_ORGANIZATION_ID :' || P_ORGANIZATION_ID);
1778 Fnd_File.PUT_LINE(Fnd_File.LOG, ' P_LOCATION_ID :' || P_LOCATION_ID);
1779 Fnd_File.PUT_LINE(Fnd_File.LOG, ' P_DELIVERY_ID_FROM :' || P_DELIVERY_ID_FROM);
1780 Fnd_File.PUT_LINE(Fnd_File.LOG, ' P_DELIVERY_ID_TO :' || P_DELIVERY_ID_TO);
1781 Fnd_File.PUT_LINE(Fnd_File.LOG, ' P_DELIVERY_DATE_FROM :' || P_DELIVERY_DATE_FROM);
1782 Fnd_File.PUT_LINE(Fnd_File.LOG, ' P_DELIVERY_DATE_TO : ' || P_DELIVERY_DATE_TO);
1783 Fnd_File.PUT_LINE(Fnd_File.LOG, ' P_PROCESS_ACTION :' || P_PROCESS_ACTION);
1784 Fnd_File.PUT_LINE(Fnd_File.LOG, ' P_SINGLE_INVOICE_NUM :' || P_SINGLE_INVOICE_NUM);
1785 Fnd_File.PUT_LINE(Fnd_File.LOG, ' P_OVERRIDE_INVOICE_DATE :' ||P_OVERRIDE_INVOICE_DATE);
1786 END IF;
1787 ln_conc_progam_id := FND_GLOBAL.conc_program_id;
1788 ln_conc_request_id := FND_GLOBAL.conc_request_id;
1789 ln_conc_prog_appl_id := FND_GLOBAL.prog_appl_id;
1790 lv_inv_gen_process_flag := jai_constants.successful;
1791 lv_acct_process_flag := jai_constants.successful;
1792 lv_inv_gen_process_message := NULL;
1793 lv_acct_process_message := NULL;
1794 ln_batch_id := ln_conc_request_id;
1795
1796 ln_failure_delivery_ctr :=0;
1797 ln_success_delivery_Ctr :=0;
1798 ln_interface_status :=0; /*added by srjayara for bug 4702156*/
1799
1800 IF P_PROCESS_ACTION IS NULL THEN
1801 Fnd_File.PUT_LINE(Fnd_File.LOG, ' +++ P_PROCESS_ACTION parameter IS NULL Hence returning +++ ' );
1802 RETURN;
1803 END IF;
1804
1805 FOR mainrec IN
1806 (
1807 SELECT delivery_id , delivery_date , organization_id , location_id , vat_invoice_no,
1808 party_id , party_site_id , party_type ,
1809 vat_inv_gen_status , vat_acct_status
1810 FROM JAI_RGM_INVOICE_GEN_T
1811 WHERE regime_id = p_regime_id
1812 AND registration_num = NVL(p_registration_num,registration_num)
1813 /* Where clause has been modified to add the regime_id
1814 and p_registration_number Added by aiyer - bug# 4523205 */
1815 /* added the nvl condition in the registration number where clause - ssumaith - bug#6147385 */
1816 AND delivery_id BETWEEN NVL(p_delivery_id_from,delivery_id) AND NVL(p_delivery_id_to,delivery_id)
1817 AND TRUNC(delivery_date) BETWEEN NVL(TRUNC(p_delivery_date_from),delivery_date) AND NVL(TRUNC(p_delivery_date_to),delivery_date)
1818 AND organization_id = NVL(p_organization_id,organization_id)
1819 AND location_id = NVL(p_location_id,location_id)
1820 AND (vat_inv_gen_status <> 'C' OR vat_acct_status <> 'C')
1821 ORDER BY party_id , party_type, party_site_id
1822 )
1823 LOOP
1824
1825 /*commented by csahoo for bug#5680459
1826 lv_inv_gen_process_flag := NULL;
1827 lv_inv_gen_process_message := NULL;*/
1828
1829 /*added by srjayara for bug 4702156*/
1830 /*
1831 || The following cursor is added by srjayara to check that only if all the delivery details are inventory interfaced,
1832 the delivery should not be processed.
1833 */
1834 OPEN c_check_interface_status(mainrec.delivery_id);
1835 FETCH c_check_interface_status into ln_interface_status;
1836 CLOSE c_check_interface_status;
1837
1838 IF ln_interface_status = 1 then
1839 Fnd_File.PUT_LINE(Fnd_File.LOG, 'Delivery - ' || mainrec.delivery_id || ' Cannot be processed because all delivery details are not inventory interfaced');
1840 /* removed the return and instead making the control process the next delivery by issueing a goto stmt
1841 ssumaith - bug#6147385
1842 */
1843 ln_failure_delivery_ctr := NVL(ln_failure_Delivery_ctr,0) + 1;
1844 goto NEXTDELIVERY;
1845 END IF;
1846
1847 /*end bug 4702156*/
1848
1849 IF lv_debug = 'Y' THEN
1850 Fnd_File.PUT_LINE(Fnd_File.LOG, ' Processing Delivery - ' || mainrec.delivery_id);
1851 END IF;
1852
1853 OPEN c_regime_cur(trunc(mainrec.delivery_date));
1854 FETCH c_regime_cur into ln_regime_id;
1855 CLOSE c_regime_cur;
1856
1857 ln_regime_id := p_regime_id ;
1858
1859 IF mainrec.vat_inv_gen_status = 'C' THEN
1860 GOTO Processaccounting;
1861 END IF;
1862
1863 /*
1864 || Get the order type id and excise invoice num from the JAI_OM_WSH_LINES_ALL table for the first delivery detail of the delivery
1865 || and pass this to the api.
1866 || make an API Call to generate the invoice number
1867 */
1868 OPEN c_shipment_info(mainrec.delivery_id);
1869 FETCH c_shipment_info INTO ln_order_type_id ,lv_vat_invoice_number ;
1870 CLOSE c_shipment_info;
1871
1872 /*
1873 || Check if the excise invoice number can be used as VAT invoice number.
1874 */
1875 OPEN c_same_inv_no(mainrec.organization_id , mainrec.location_id );
1876 FETCH c_same_inv_no INTO lv_Same_invoice_no;
1877 CLOSE c_same_inv_no;
1878
1879 IF NVL(lv_Same_invoice_no,jai_constants.no) = jai_constants.yes THEN
1880
1881 IF lv_debug = 'Y' THEN
1882 Fnd_File.PUT_LINE(Fnd_File.LOG, ' lv_Same_invoice_no = Y , Hence we need not call inv num generation routine - using excise inv num instead');
1883 END IF;
1884 ln_excise_invoice_not_done := NULL; --added by csahoo for bug#6176277
1885 OPEN c_excise_invoice_not_done (mainrec.delivery_id);
1886 FETCH c_excise_invoice_not_done INTO ln_excise_invoice_not_done;
1887 CLOSE c_excise_invoice_not_done ;
1888
1889 IF lv_debug = 'Y' THEN
1890 Fnd_File.PUT_LINE(Fnd_File.LOG, ' ln_excise_invoice_not_done = ' || NVL(ln_excise_invoice_not_done,-1));
1891 END IF;
1892
1893 IF ln_excise_invoice_not_done IS NULL THEN
1894 /*
1895 || Excise invoice number in not found , populate it as -1
1896 */
1897 ln_excise_invoice_not_done := -1;
1898 END IF;
1899
1900 IF ln_excise_invoice_not_done = 1 THEN
1901 /*
1902 || It means the delivery is still existing in the JAI_OM_OE_GEN_TAXINV_T table
1903 || It means that the excise invoice generation was either not processed or errored out.
1904 || We need to raise an error saying that excise invoice number needs to be run before VAT processing can happen.
1905 */
1906 lv_inv_gen_process_flag := jai_constants.expected_error;
1907 lv_inv_gen_process_message := 'Excise Invoice Generation is not generated yet for this Delivery. Please run the excise invoice number generation number for delivery : '
1908 || mainrec.delivery_id;
1909
1910 /*
1911 || Not using the debug flag for the following message because it needs to be shown to the user irrespective of debug flag.
1912 */
1913 Fnd_File.PUT_LINE(Fnd_File.LOG, 'Excise Invoice Generation is not generated yet for this Delivery. Please run the excise invoice number generation number for delivery : '
1914 || mainrec.delivery_id);
1915
1916 ln_failure_delivery_ctr := NVL(ln_failure_Delivery_ctr,0) + 1;
1917 goto NEXTDELIVERY;
1918 ELSE
1919 /*
1920 || Control comes here - It means record for the delivery does not exist in the JAI_OM_OE_GEN_TAXINV_T table
1921 because of reasons such as :
1922 || a. Delivery does not have excise taxes
1923 || b. Explicit setting such as bond register is set to DOMESTIC_WITHOUT_EXCISE or EXPORT_WITHOUT_EXCISE
1924 || c. The item itself is not excisable , hence no excise invoice num is generated.
1925 || d. Excise invoice is already generated
1926
1927 For cases a to c , need to generate VAT invoice number explicitly.
1928 For case d , need to copy the excise invoice number and make it the vat invoice number.
1929 */
1930
1931 IF lv_vat_invoice_number IS NOT NULL THEN
1932 /*
1933 || Excise invoice number is not null - so the same excise invoice number needs to be used as VAT invoice number
1934 */
1935
1936 IF lv_debug = 'Y' THEN
1937 Fnd_File.PUT_LINE(Fnd_File.LOG, ' Excise Invoice number - ' || lv_vat_invoice_number || ' Will be used as VAT Invoice number for delivery : ' || mainrec.delivery_id);
1938 Fnd_File.PUT_LINE(Fnd_File.LOG, ' before updating jai_om_wsh_lines_all for ex inv num');
1939 END IF;
1940
1941 /*
1942 || Code added by aiyer for the Bug 4534166 (fixed as a part of the bug 4523205)
1943 || removed the returning clause as it was not required.
1944 */
1945 UPDATE JAI_OM_WSH_LINES_ALL
1946 SET vat_invoice_no = excise_invoice_no,
1947 vat_invoice_date = excise_invoice_date
1948 WHERE delivery_id = mainrec.delivery_id;
1949
1950 IF lv_debug = 'Y' THEN
1951 Fnd_File.PUT_LINE(Fnd_File.LOG, ' before updating jai_rgm_invoice_gen_t for ex inv num');
1952 END IF;
1953
1954 UPDATE JAI_RGM_INVOICE_GEN_T
1955 SET vat_invoice_no = lv_vat_invoice_number ,
1956 vat_inv_gen_status = 'C',
1957 vat_inv_gen_err_message = NULL , /* added the following in the update columns - srjayara for bug 4702156*/
1958 request_id = ln_conc_request_id,
1959 program_id = ln_conc_progam_id,
1960 program_application_id = ln_conc_prog_appl_id,
1961 last_update_login = fnd_global.conc_login_id,
1962 last_update_Date = sysdate
1963
1964 WHERE Delivery_id = mainrec.delivery_id;
1965
1966
1967 IF lv_debug = 'Y' THEN
1968 Fnd_File.PUT_LINE(Fnd_File.LOG, ' after updating jai_rgm_invoice_gen_t for ex inv num');
1969 END IF;
1970
1971 ln_success_delivery_Ctr := NVL(ln_success_Delivery_Ctr,0) + 1;
1972
1973 IF lv_debug = 'Y' THEN
1974 Fnd_File.PUT_LINE(Fnd_File.LOG, ' Before going to process accounting for generating accounting');
1975 END IF;
1976 lv_inv_gen_process_flag := jai_constants.successful; /*ssumaith bug# 6176277 */
1977
1978 GOTO Processaccounting;
1979 END IF;
1980
1981 END IF;
1982
1983 END IF;
1984
1985 IF lv_debug = 'Y' THEN
1986 Fnd_File.PUT_LINE(Fnd_File.LOG, ' 2. In the Loop');
1987 END IF;
1988
1989 /*
1990 || Check if we need to generate vat invoice number and within that check if an invoice number is already generated.
1991 || if an invoice is already generated and if the parameter p_single_invoice_num is set to 'Y' then do not make an
1992 || API call again and again to the generation api . Just update the JAI_OM_WSH_LINES_ALL table to set the
1993 || vat invoice number for the delivery and continue.
1994 */
1995 /*
1996 || kunkumar - for - bug# 5233925
1997 */
1998 if check_reg_dealer(mainrec.party_id,mainrec.party_site_id) then --replaced party_id in the second parameter by party_site_id for bug#5680459
1999 lv_doc_type_class :='O';
2000 else
2001 lv_doc_type_class :='UO'; /*made it to UO from VO */
2002 end if;
2003
2004 IF lv_Debug = 'Y' THEN
2005 Fnd_File.PUT_LINE(Fnd_File.LOG, ' ln_current_party_id : ' || ln_current_party_id);
2006 Fnd_File.PUT_LINE(Fnd_File.LOG, ' mainrec.party_id : ' || mainrec.party_id);
2007 Fnd_File.PUT_LINE(Fnd_File.LOG, ' ln_current_party_site_id : ' || ln_current_party_site_id);
2008 Fnd_File.PUT_LINE(Fnd_File.LOG, ' mainrec.party_site_id :' || mainrec.party_site_id);
2009 Fnd_File.PUT_LINE(Fnd_File.LOG, 'lv_doc_type_class:' || lv_doc_type_class);--kunkumar for 5233925
2010 END IF;
2011
2012 IF ln_current_party_id <> mainrec.party_id OR ln_current_party_site_id <> mainrec.party_site_id THEN
2013 /*
2014 || There has been a change either in the party id or the party site id .
2015 || Hence a new loop needs to start
2016 */
2017 ln_current_party_id := mainrec.party_id;
2018 ln_current_party_site_id := mainrec.party_site_id;
2019 lv_party_has_changed := jai_constants.value_true;
2020 ELSE
2021 lv_party_has_changed := jai_constants.value_false;
2022 END IF;
2023
2024 IF lv_Debug = 'Y' THEN
2025 Fnd_File.PUT_LINE(Fnd_File.LOG, ' lv_party_has_changed :' || lv_party_has_changed);
2026 Fnd_File.PUT_LINE(Fnd_File.LOG, ' lv_inv_num_already_generated :' || lv_inv_num_already_generated);
2027 END IF;
2028
2029 IF p_process_action in (jai_constants.om_action_gen_inv_n_accnt ,jai_constants.om_action_gen_invoice) THEN
2030 IF NVL(p_single_invoice_num,jai_constants.No) = jai_constants.yes THEN
2031 IF lv_inv_num_already_generated = jai_constants.value_false AND lv_party_has_changed = jai_constants.value_true THEN
2032 IF lv_Debug = 'Y' THEN
2033 Fnd_File.PUT_LINE(Fnd_File.LOG, ' before call to jai_cmn_rgm_setup_pkg.Gen_Invoice_number with ln_order_type_id' || ln_order_type_id || 'date ' || mainrec.delivery_date );
2034 END IF;
2035 jai_cmn_rgm_setup_pkg.Gen_Invoice_number(
2036 p_regime_id => ln_regime_id ,
2037 p_organization_id => mainrec.organization_id ,
2038 p_location_id => mainrec.location_id ,
2039 p_date => mainrec.delivery_date ,
2040 p_doc_class => lv_doc_type_class,
2041 p_doc_type_id => ln_order_type_id,
2042 P_invoice_number => lv_vat_invoice_number,
2043 p_process_flag => lv_inv_gen_process_flag ,
2044 p_process_msg => lv_inv_gen_process_message
2045 );
2046 IF lv_Debug = 'Y' THEN
2047 Fnd_File.PUT_LINE(Fnd_File.LOG, ' after call with lv_vat_invoice_number:' || lv_vat_invoice_number || lv_inv_gen_process_flag ||lv_inv_gen_process_message);
2048 END IF;
2049 /*
2050 check the return status and update the JAI_OM_WSH_LINES_ALL table to set the vat invoice number
2051 */
2052 IF lv_inv_gen_process_flag = jai_constants.successful THEN
2053 IF lv_vat_invoice_number IS NOT NULL THEN
2054 ln_success_delivery_Ctr := NVL(ln_success_Delivery_Ctr,0) + 1;
2055 lv_inv_num_already_generated := jai_constants.value_true;
2056 UPDATE JAI_OM_WSH_LINES_ALL
2057 SET VAT_INVOICE_NO = lv_vat_invoice_number,
2058 VAT_INVOICE_DATE = nvl(ld_override_invoice_date ,sysdate),
2059 LAST_UPDATE_DATE = sysdate,
2060 LAST_UPDATE_LOGIN = fnd_global.login_id,
2061 LAST_UPDATED_BY = fnd_global.user_id
2062 WHERE DELIVERY_ID = mainrec.delivery_id;
2063
2064 UPDATE JAI_RGM_INVOICE_GEN_T
2065 SET vat_invoice_no = lv_vat_invoice_number,
2066 vat_inv_gen_status = 'C',
2067 request_id = ln_conc_request_id,
2068 program_id = ln_conc_progam_id,
2069 program_application_id = ln_conc_prog_appl_id,
2070 last_update_login = fnd_global.conc_login_id,
2071 last_update_date = sysdate /*added by srjayara for bug 4702156*/
2072 WHERE delivery_id = mainrec.delivery_id;
2073 ELSE
2074 lv_inv_gen_process_flag := jai_constants.unexpected_error;
2075 lv_acct_process_flag := jai_constants.expected_error;
2076 ln_failure_delivery_ctr := NVL(ln_failure_Delivery_ctr,0) + 1;
2077 END IF;
2078 ELSE
2079 ln_failure_delivery_ctr := NVL(ln_failure_Delivery_ctr,0) + 1;
2080 END IF;
2081 END IF;
2082 /*
2083 || Party / party site has not changed and an invoice num was already generated .
2084 || So this can be used for all the deliveries of the current party / party site.
2085 */
2086 IF lv_inv_num_already_generated = jai_constants.value_true AND lv_party_has_changed = jai_constants.value_false THEN
2087 IF lv_vat_invoice_number IS NOT NULL THEN
2088 /*
2089 || Update the vat_invoice_num field in JAI_OM_WSH_LINES_ALL table for the current delivery.
2090 */
2091 UPDATE JAI_OM_WSH_LINES_ALL
2092 SET vat_invoice_no = lv_vat_invoice_number,
2093 VAT_INVOICE_DATE = nvl(ld_override_invoice_date ,sysdate),
2094 last_update_date = sysdate,
2095 last_update_login = fnd_global.login_id,
2096 last_updated_by = fnd_global.user_id
2097 WHERE delivery_id IN
2098 (SELECT delivery_id
2099 FROM JAI_RGM_INVOICE_GEN_T
2100 WHERE party_id = ln_current_party_id
2101 AND party_site_id = ln_current_party_site_id
2102 AND party_type = mainrec.party_type
2103 AND vat_inv_gen_status <> 'C'
2104 AND delivery_id between NVL(P_DELIVERY_ID_FROM,delivery_id) AND NVL(P_DELIVERY_ID_TO,delivery_id)
2105 AND delivery_Date between NVL(P_DELIVERY_DATE_FROM,Delivery_date) AND NVL(P_DELIVERY_DATE_TO,delivery_date)
2106 );
2107
2108 UPDATE JAI_RGM_INVOICE_GEN_T
2109 SET vat_invoice_no = lv_vat_invoice_number,
2110 vat_inv_gen_status = 'C',
2111 request_id = ln_conc_request_id,
2112 program_id = ln_conc_progam_id,
2113 program_application_id = ln_conc_prog_appl_id,
2114 last_update_login = fnd_global.conc_login_id
2115 WHERE delivery_id IN
2116 (SELECT delivery_id
2117 FROM JAI_RGM_INVOICE_GEN_T
2118 WHERE party_id = ln_current_party_id
2119 AND party_site_id = ln_current_party_site_id
2120 AND party_type = mainrec.party_type
2121 AND vat_inv_gen_status <> 'C'
2122 AND delivery_id between NVL(P_DELIVERY_ID_FROM,delivery_id) AND NVL(P_DELIVERY_ID_TO,delivery_id)
2123 AND delivery_Date between NVL(P_DELIVERY_DATE_FROM,Delivery_date) AND NVL(P_DELIVERY_DATE_TO,delivery_date)
2124 );
2125 END IF;
2126 END IF;
2127 ELSE
2128 IF lv_Debug = 'Y' THEN
2129 Fnd_File.PUT_LINE(Fnd_File.LOG, 'In the Else when p_single_invoice is not Y ');
2130 END IF;
2131 /*
2132 || This is the Else Part of the IF p_single_invoice_num = 'Y' THEN
2133 || In this comes the code that is needed for different generating vat invoice number for every delivery
2134 */
2135 IF lv_Debug = 'Y' THEN
2136 Fnd_File.PUT_LINE(Fnd_File.LOG, '+++ before call to jai_cmn_rgm_setup_pkg.Gen_Invoice_number In the Else when p_single_invoice is not Y with order type = '
2137 || ln_order_type_id || ' +++ ' );
2138
2139 Fnd_File.PUT_LINE(Fnd_File.LOG, '0 ' || ln_regime_id || ' ' ||
2140 mainrec.organization_id || ' ' || mainrec.location_id || ' ' || mainrec.delivery_date || '' || ln_order_type_id ||
2141 ' ' || lv_vat_invoice_number || ' ' || lv_inv_gen_process_flag || ' ' || lv_inv_gen_process_message ) ; -- test
2142
2143 END IF;
2144 jai_cmn_rgm_setup_pkg.Gen_Invoice_number(
2145 p_regime_id => ln_regime_id ,
2146 p_organization_id => mainrec.organization_id ,
2147 p_location_id => mainrec.location_id ,
2148 p_date => mainrec.delivery_date,
2149 p_doc_class =>lv_doc_type_class,
2150 p_doc_type_id => ln_order_type_id,
2151 P_invoice_number => lv_vat_invoice_number,
2152 p_process_flag => lv_inv_gen_process_flag ,
2153 p_process_msg => lv_inv_gen_process_message
2154 );
2155 IF lv_Debug = 'Y' THEN
2156 Fnd_File.PUT_LINE(Fnd_File.LOG, ' +++ after call to jai_cmn_rgm_setup_pkg.Gen_Invoice_number In the Else when p_single_invoice is not Y with lv_vat_invoice_number = '
2157 || lv_vat_invoice_number || '+++');
2158 Fnd_File.PUT_LINE(Fnd_File.LOG, ' +++ after call to jai_cmn_rgm_setup_pkg.Gen_Invoice_number with lv_inv_gen_process_flag = '
2159 || lv_inv_gen_process_flag || 'lv_inv_gen_process_message '|| lv_inv_gen_process_message || '+++');
2160 END IF;
2161 IF lv_inv_gen_process_flag = jai_constants.successful THEN
2162 IF lv_vat_invoice_number IS NOT NULL THEN
2163 ln_success_delivery_Ctr := NVL(ln_success_Delivery_Ctr,0) + 1;
2164
2165 UPDATE JAI_OM_WSH_LINES_ALL
2166 SET vat_invoice_no = lv_vat_invoice_number,
2167 vat_invoice_date = nvl(ld_override_invoice_date ,sysdate),
2168 last_update_date = sysdate,
2169 last_update_login = fnd_global.login_id,
2170 last_updated_by = fnd_global.user_id
2171 WHERE delivery_id = mainrec.delivery_id;
2172
2173 UPDATE JAI_RGM_INVOICE_GEN_T
2174 SET vat_invoice_no = lv_vat_invoice_number,
2175 vat_inv_gen_status = 'C',
2176 vat_inv_gen_err_message = NULL , /*following columns added by srjayara for bug 4702156*/
2177 request_id = ln_conc_request_id,
2178 program_id = ln_conc_progam_id,
2179 program_application_id = ln_conc_prog_appl_id,
2180 last_update_login = fnd_global.conc_login_id,
2181 last_update_date = sysdate
2182 WHERE delivery_id = mainrec.delivery_id;
2183
2184 ELSE
2185 lv_inv_gen_process_flag := jai_constants.unexpected_error;
2186 lv_inv_gen_process_message := 'No VAT Invoice Number Generated';
2187 ln_failure_delivery_ctr := NVL(ln_failure_Delivery_ctr,0) + 1;
2188 END IF;
2189 ELSE
2190 UPDATE JAI_RGM_INVOICE_GEN_T
2191 SET vat_inv_gen_err_message = substr(lv_inv_gen_process_message,1,1000),
2192 vat_inv_gen_status = 'E',
2193 request_id = ln_conc_request_id, /*following columns added by srjayara for bug 4702156*/
2194 program_id = ln_conc_progam_id,
2195 program_application_id = ln_conc_prog_appl_id,
2196 last_update_login = fnd_global.conc_login_id,
2197 last_update_date = sysdate
2198 WHERE delivery_id = mainrec.delivery_id;
2199 ln_failure_delivery_ctr := NVL(ln_failure_Delivery_ctr,0) + 1;
2200 END IF;
2201 END IF;
2202 END IF; /* This is the END if for IF p_process_action in ('ALL','GENERATE INVOICE NUM') THEN */
2203
2204 /*
2205 || Now process the om-ar accounting if it is needed
2206 */
2207 <<Processaccounting>>
2208
2209 IF lv_debug = 'Y' THEN
2210 Fnd_File.PUT_LINE(Fnd_File.LOG, ' In process accounting section with p_process_action = ' || p_process_action);
2211 END IF;
2212
2213 IF p_process_action in (jai_constants.om_action_gen_inv_n_accnt,jai_constants.om_action_gen_accounting) THEN
2214 /*
2215 || Only In case the parameter p_process_action in ('PROCESS ALL','PROCESS ACCOUNTING') AND
2216 ||
2217 */
2218 IF lv_Debug = 'Y' THEN
2219 Fnd_File.PUT_LINE(Fnd_File.LOG, ' mainrec.vat_acct_status : ' || mainrec.vat_acct_status || ' lv_inv_gen_process_flag : ' || lv_inv_gen_process_flag );
2220 END IF;
2221 IF mainrec.vat_acct_status = 'C' THEN
2222 GOTO NEXTDELIVERY;
2223 END IF;
2224 IF lv_Debug = 'Y' THEN
2225 Fnd_File.PUT_LINE(Fnd_File.LOG, ' lv_inv_gen_process_flag = ' || lv_inv_gen_process_flag );
2226 END IF;
2227 IF lv_inv_gen_process_flag = jai_constants.successful THEN
2228
2229 IF lv_vat_invoice_number IS NULL THEN
2230 lv_vat_invoice_number:= mainrec.vat_invoice_no;
2231 END IF;
2232 --Added the following condtion and assignment by kunkumar for forward porting Bug#6012489
2233 IF p_process_action = jai_constants.om_action_gen_accounting THEN
2234 lv_vat_invoice_number := mainrec.vat_invoice_no;
2235 END IF;
2236 --End Added by kunkumar for Bug#6012489
2237 IF lv_Debug = 'Y' THEN
2238 Fnd_File.PUT_LINE(Fnd_File.LOG, 'Before Call to jai_cmn_rgm_vat_accnt_pkg.process_order_invoice ');
2239 END IF;
2240 jai_cmn_rgm_vat_accnt_pkg.process_order_invoice(
2241 P_REGIME_ID => ln_regime_id ,
2242 P_SOURCE => jai_constants.source_wsh ,
2243 P_ORGANIZATION_ID => mainrec.organization_id,
2244 P_LOCATION_ID => mainrec.location_id ,
2245 P_DELIVERY_ID => mainrec.delivery_id ,
2246 P_CUSTOMER_TRX_ID => NULL ,
2247 P_VAT_INVOICE_NO => lv_vat_invoice_number ,
2248 P_TRANSACTION_TYPE => jai_cmn_rgm_vat_accnt_pkg.gv_transaction_type_dflt,
2249 P_DEFAULT_INVOICE_DATE => NVL(ld_override_invoice_date,SYSDATE),
2250 P_BATCH_ID => ln_batch_id ,
2251 P_CALLED_FROM => 'jai_cmn_rgm_processing_pkg.PROCESS',
2252 P_DEBUG => lv_debug ,
2253 P_PROCESS_FLAG => lv_acct_process_flag ,
2254 P_PROCESS_MESSAGE => lv_acct_process_message
2255 );
2256 IF lv_Debug = 'Y' THEN
2257 Fnd_File.PUT_LINE(Fnd_File.LOG, 'after Call to jai_cmn_rgm_vat_accnt_pkg.process_order_invoice with status = ' || lv_acct_process_flag);
2258 END IF;
2259 IF lv_acct_process_flag = jai_constants.successful THEN
2260 /*
2261 || If the control comes here it means that Accounting got processed successfully.
2262 || Check here if Delivery for successfully processed and invoice got successfully processed
2263 || and only then do a commit
2264 */
2265 IF lv_inv_gen_process_flag = jai_constants.successful AND lv_acct_process_flag = jai_constants.successful THEN
2266 /*
2267 || Both the activities have been succesfully completed
2268 || Can commit the changes made to the delivery.
2269 */
2270 UPDATE JAI_RGM_INVOICE_GEN_T
2271 SET vat_acct_status = 'C',
2272 vat_inv_gen_err_message = NULL, /*following columns added by srjayara for bug 4702156*/
2273 request_id = ln_conc_request_id,
2274 program_id = ln_conc_progam_id,
2275 program_application_id = ln_conc_prog_appl_id,
2276 last_update_login = fnd_global.conc_login_id,
2277 last_update_date = sysdate
2278 WHERE delivery_id = mainrec.delivery_id;
2279 COMMIT;
2280 END IF;
2281 ELSE
2282 ln_failure_delivery_ctr := NVL(ln_failure_Delivery_ctr,0) + 1;
2283 Fnd_File.PUT_LINE(Fnd_File.LOG, 'Error Encountered after call to process_order_invoice is ' || lv_acct_process_message);
2284 END IF;
2285
2286 IF lv_inv_gen_process_flag <> jai_constants.successful OR lv_acct_process_flag <> jai_constants.successful THEN
2287
2288 /*
2289 || There have been some errors which have happened during accounting
2290 */
2291 ROLLBACK;
2292
2293 IF lv_inv_gen_process_flag <> jai_constants.successful THEN
2294 UPDATE JAI_RGM_INVOICE_GEN_T
2295 SET vat_inv_gen_err_message = substr(lv_inv_gen_process_message,1,1000),
2296 vat_inv_gen_status = 'E',
2297 request_id = ln_conc_request_id, /*following columns added by srjayara for bug 4702156*/
2298 program_id = ln_conc_progam_id,
2299 program_application_id = ln_conc_prog_appl_id,
2300 last_update_login = fnd_global.conc_login_id,
2301 last_update_date = sysdate
2302 WHERE delivery_id = mainrec.delivery_id;
2303 END IF;
2304
2305 IF lv_acct_process_flag <> jai_constants.successful THEN
2306 UPDATE JAI_RGM_INVOICE_GEN_T
2307 SET vat_acct_err_message = substr(lv_acct_process_message,1,1000),
2308 vat_acct_status = 'E',
2309 request_id = ln_conc_request_id, /*following columns added by srjayara for bug 4702156*/
2310 program_id = ln_conc_progam_id,
2311 program_application_id = ln_conc_prog_appl_id,
2312 last_update_login = fnd_global.conc_login_id,
2313 last_update_date = sysdate
2314
2315 WHERE delivery_id = mainrec.delivery_id;
2316 END IF;
2317 COMMIT;
2318 END IF;
2319
2320 END IF; /* END IF For IF lv_inv_gen_process_flag = jai_constants.successful THEN */
2321 END IF; /* END IF For IF p_process_action in ('ALL','PROCESS ACCOUNTING') THEN */
2322 <<NEXTDELIVERY>>
2323 NULL;
2324 END LOOP;
2325
2326 /*
2327 || Coding here to mark the status of the concurrent and generating statictics.
2328 ||
2329 */
2330 Fnd_File.PUT_LINE(Fnd_File.LOG, ' +++ Number of Successful deliveries : ' || ln_success_delivery_Ctr || '+++');
2331 Fnd_File.PUT_LINE(Fnd_File.LOG, ' +++ Number of Failed deliveries : ' || ln_failure_delivery_ctr || '+++');
2332
2333 IF ln_failure_delivery_ctr > 0 AND ln_success_delivery_Ctr > 0 then
2334 /*
2335 || Atleast one delivery failed Atleast one delivery Succeeded
2336 || Signal completion with warning
2337 */
2338 lb_completion_status := FND_CONCURRENT.SET_COMPLETION_STATUS('WARNING', NVL(lv_acct_process_message,lv_inv_gen_process_message));
2339 retcode := '1';
2340 END IF;
2341 IF ln_failure_delivery_ctr = 0 AND ln_success_delivery_Ctr > 0 then
2342 /*
2343 || Atleast one delivery Succeeded and none failed
2344 || Signal completion with success
2345 */
2346 lb_completion_status := FND_CONCURRENT.SET_COMPLETION_STATUS('NORMAL', NULL);
2347 retcode := '0';
2348 END IF;
2349 IF ln_failure_delivery_ctr > 0 AND ln_success_delivery_Ctr = 0 then
2350 /*
2351 || Atleast one delivery failed and No delivery Succeeded
2352 || Signal completion with error
2353 */
2354 retcode := '2';
2355 lb_completion_status := FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR',NVL(lv_acct_process_message,lv_inv_gen_process_message));
2356 END IF;
2357 IF ln_failure_delivery_ctr = 0 AND ln_success_delivery_Ctr = 0 then
2358 /*
2359 || No delivery failed and No delivery Succeeded
2360 || Signal completion with Success
2361 */
2362 lb_completion_status := FND_CONCURRENT.SET_COMPLETION_STATUS('NORMAL', NULL);
2363 retcode := '0';
2364 END IF;
2365
2366 EXCEPTION
2367 WHEN OTHERS THEN
2368 RETCODE := '2';
2369 Fnd_File.PUT_LINE(Fnd_File.LOG,'Unexpected Error occured in procedure jai_cmn_rgm_processing_pkg.process '||substr(sqlerrm,1,300));
2370 lb_completion_status := FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR',substr(sqlerrm,1,1000));
2371 ERRBUF := substr(sqlerrm,1,1000);
2372 lv_inv_gen_process_flag := jai_constants.unexpected_error;
2373 lv_acct_process_flag := jai_constants.unexpected_error;
2374 lv_inv_gen_process_message := sqlerrm;
2375 lv_acct_process_message := sqlerrm;
2376
2377 END PROCESS;
2378
2379 END jai_cmn_rgm_processing_pkg;
[Home] [Help]