[Home] [Help]
PACKAGE BODY: APPS.JAI_RCV_RGM_CLAIMS_PKG
Source
1 PACKAGE BODY jai_rcv_rgm_claims_pkg AS
2 /* $Header: jai_rcv_rgm_clm.plb 120.15.12010000.2 2008/10/14 10:17:08 lgopalsa ship $ */
3
4
5 TABLE_RCV_TRANSACTIONS CONSTANT VARCHAR2(30) := 'RCV_TRANSACTIONS';
6 RECEIVING CONSTANT VARCHAR2(30) := 'RECEIVING';
7 RTV CONSTANT VARCHAR2(15) := 'RTV';
8 CORRECT_RECEIVE CONSTANT VARCHAR2(30) := 'CORRECT-RECEIVE';
9 CORRECT_RTV CONSTANT VARCHAR2(30) := 'CORRECT-RTV';
10
11 PROCEDURE get_location(
12 p_transaction_id IN rcv_transactions.transaction_id%TYPE,
13 p_location_id OUT NOCOPY hr_locations_all.location_id%TYPE,
14 p_process_status OUT NOCOPY VARCHAR2,
15 p_process_message OUT NOCOPY VARCHAR2)
16 IS
17 CURSOR c_dlry_subinventory(cp_shipment_line_id IN NUMBER,
18 cp_receive_trx_id IN NUMBER,
19 cp_transaction_type rcv_transactions.transaction_type%type)
20 IS
21 SELECT subinventory
22 FROM rcv_transactions
23 WHERE shipment_line_id = cp_shipment_line_id
24 AND parent_transaction_id = cp_receive_trx_id
25 AND transaction_type = cp_transaction_type --'DELIVER' /* Modified by Ramananda for removal of SQL LITERALs :bug#4428980*/
26 AND transaction_id > cp_receive_trx_id;
27
28 CURSOR c_loc_linked_to_org_subinv(cp_organization_id IN NUMBER,
29 cp_subinventory IN VARCHAR2)
30 IS
31 SELECT location_id
32 FROM JAI_INV_SUBINV_DTLS
33 WHERE organization_id = cp_organization_id
34 AND sub_inventory_name = cp_subinventory;
35
36 CURSOR c_inv_org_linked_to_location(cp_location_id IN NUMBER)
37 IS
38 SELECT nvl(inventory_organization_id, -99999) inventory_organization_id
39 FROM hr_locations_all
40 WHERE location_id = cp_location_id;
41
42 r_trx c_trx%ROWTYPE;
43 r_parent_trx c_trx%ROWTYPE;
44 r_base_trx c_base_trx%ROWTYPE;
45 r_parent_base_trx c_base_trx%ROWTYPE;
46 r_ancestor_dtls c_base_trx%ROWTYPE;
47 lv_subinventory RCV_TRANSACTIONS.subinventory%TYPE;
48 ln_location_id NUMBER(15);
49 lv_required_trx_type RCV_TRANSACTIONS.transaction_type%TYPE;
50 ln_ancestor_trxn_id NUMBER(15);
51 r_subinv_dtls c_loc_linked_to_org_subinv%ROWTYPE;
52 ln_organization_id NUMBER(15);
53 lv_transaction_type RCV_TRANSACTIONS.transaction_type%TYPE;
54 BEGIN
55 /*****************************************************************************************************************************************************************************************
56 Change History -
57 *****************************************************************************************************************************************************************************************
58 1. 27-Jan-2005 Sanjikum for Bug #4248727 Version #115.1
59 New Package created for creating VAT Processing
60
61 2 25/03/2005 Vijay Shankar for Bug#4250171. Version: 115.2
62 modified the code in get_location procedure, so that location_id value is fetched from JAI_RCV_TRANSACTIONS
63 incase of OPM Receipts
64
65 3 01/04/2005 Vijay Shankar for Bug#4278511. Version:115.3
66 Incase of ISO receipts, location_id has to be derived from SUBINVENTORY attached to the transaction if present, otherwise
67 we need to fetch location of RCV_TRANSACTONS. Code is modified in get_location procedure
68
69 4. 04/04/2005 Sanjikum for Bug #4279050 Version #115.4
70 Problem
71 -------
72 In the Procedure update_rcv_lines, For setting the flag lv_process_status_flag, first Partial Claim is checked and then Full Claimed,
73 which is creating the problem in case of full claim happens in the first installment
74
75 Fix
76 ---
77 1) In the Procedure update_rcv_lines, For setting the flag lv_process_status_flag, now first Full Claimed is checked
78 and then Partial Claim
79 2) In the procedure - update_rcv_lines, added one more parameter - p_shipment_header_id
80
81 5. 08/04/2005 Sanjikum for Bug #4279050 Version #116.0 (115.5)
82 Re-checked the same file again
83
84 6. 10/05/2005 Vijay Shankar for Bug#4346453. Version: 116.1
85 Code is modified due to the Impact of Receiving Transactions DFF Elimination
86
87 * High Dependancy for future Versions of this object *
88
89 7. 08-Jun-2005 File Version 116.2. Object is Modified to refer to New DB Entity names in place of Old DB Entity Names
90 as required for CASE COMPLAINCE.
91
92 8. 13-Jun-2005 Ramananda for bug#4428980. File Version: 116.3
93 Removal of SQL LITERALs is done
94
95 9. 06-Jul-2005 Ramananda for bug#4477004. File Version: 116.4
96 GL Sources and GL Categories got changed. Refer bug for the details
97
98 10.02-Aug-2005 Ramananda for Bug#4530112. File Version 120.2
99 Problem
100 -------
101 In case of RTV, if VAT Claim is not done, system is asking to make the VAT Claim first
102
103 Fix
104 ---
105 1) In the Procedure process_vat, added cursor c_rcv_rgm_lines
106 2) In the Procedure process_vat, added an IF condition -
107 "IF r_rcv_rgm_lines.invoice_no IS NULL AND (r_trx.transaction_type = 'RETURN TO VENDOR'
108 OR (r_trx.transaction_type = 'CORRECT' AND r_trx.parent_transaction_type IN ('RECEIVE', 'MATCH', 'RETURN TO VENDOR') ) ) THEN"
109
110 Added the following as the object is not compiled because of R12 changes.
111 These were introudced as default values were removed from the procedure spec. and function spec.
112 1. Procedue call update_rcv_lines is changed:
113 p_invoice_no => jai_rcv_rgm_claims_pkg.gv_invoice_no_dflt, --File.Sql.35 Cbabu
114 p_invoice_date => jai_rcv_rgm_claims_pkg.gd_invoice_date_dflt --File.Sql.35 Cbabu
115
116 2. Procedure call generate_schedule is changed:
117 p_override => jai_constants.no
118
119 Dependency Due to this Bug:-
120 File jai_rcv_tax.plb (120.4) is changed as part of this Bug,
121 so this object is dependent on current Bug and object jai_rcv_tax.plb(120.4)
122
123 11. 02-Aug-2005 Ramananda for Bug#4519719. File Version 120.2
124 Issue : Processing should not take place if no VAT type of taxes
125 Fis : Added a condition to check if VAT type of taxes exist in the receipt
126 before the call to jai_rcv_rgm_claims_pkg.insert_rcv_lines. If no VAT type of taxes
127 exist, the return from the procedure.
128
129 Dependency due to this bug:-
130 jai_rcv_trx_prc.plb (120.4)
131
132 12. 02-Sep-2005 Bug4589354. Added by Lakshmi Gopalsami version 120.3
133 Commented the following condition.
134 OR (r_base_trx.source_document_code = 'REQ' and
135
136 Dependencies :
137 jai_rcv_trx_prc.plb 120.6
138 jai_rcv_rgm_clm.plb 120.3
139
140 13. 25-Jan-2006 Bug4929929. Added by Lakshmi Gopalsami Version 120.4
141 Removed the NVL in cursor c_receipt_source_code
142 as shipment_header_id and shipment_line_id cannot be null.
143
144 14. 26-FEB-2007 SSAWANT , File version 120.7
145 Forward porting the change in 11.5 bug 4760317 to R12 bug no 4950914
146
147 a) Following changes are done in procedure - generate_schedule
148 - In the definition of procedure added one more parameter - p_simulate_flag
149 - In the cursor - cur_tax, added one more where condition - "AND NVL(a.tax_amount,0) <> 0;"
150 - Added a new cursor - cur_installment_count
151 - Added new variable - r_installment_count
152 - In loop of cursor - cur_tax, after calling procedure - generate_term_schedules, added a delete statement
153 - In loop of cursor - cur_tax, after calling procedure - generate_term_schedules, added the code for
154 b) Following changes are done in procedure - process_vat
155 - Added a new condition to return from the procedure, if ja_in_rcv_transactions.process_vat_flag is 'SS'.
156 After this added the call to procedure - generate_schedule
157
158 c) Following changes are done in procedure - process_claim
159 - Before call to procedure - jai_rgm_trx_recording_pkg.insert_vat_repository_entry, added the condition that
160 either of debit or credit amount should be null
161
162 d) Following changes are done in procedure - process_no_claim
163 - Changed the definition of procedure
164 - Changed the definition of cursor - c_shipment_lines and changed the statement to open this cursor
165 - Before call to procedure - ja_in_receipt_accounting_pkg.process_transaction, added the condition that
166 either of debit or credit amount should be null
167
168 e) Following changes are done in procedure - process_batch
169 - Commented the condition - "IF p_batch_id IS NULL AND p_shipment_header_id IS NULL AND p_shipment_line_id IS NULL THEN"
170 - Changed the call to procedure - process_no_claim
171
172 f) Following changes are done in procedure - do_rtv_accounting
173 - Before call to procedure - jai_rgm_trx_recording_pkg.insert_vat_repository_entry, added the condition that
174 either of debit or credit amount should be null
175
176 g) Following changes are done in procedure - do_rma_accounting
177 - In the loop of cursor - cur_tax, Added the following condition -
178 IF NVL(rec_tax.tax_amount,0) = 0 THEN
179 goto END_OF_LOOP;
180 END IF;
181 - Before call to procedure - ja_in_receipt_accounting_pkg.process_transaction, added the condition that
182 ln_tax_amount <> 0
183 - Added a new Label - <<END_OF_LOOP>>
184 15 05-03-2007 bduvarag for bug#5899383,File version 120.8
185 Forward porting the changes done in 11i bug 5496355
186
187 Dependency Due to this bug
188 --------------------------
189 Yes, there are new parameters added in some procedures
190
191 16 28-05-2007 SACSETHI for bug 6071533 file Version 120.10
192
193 VAT CLAIM ACCOUNTING ENTRY IS NOT GETTING GENERATED FOR RECEIPTS
194
195 Problem - In Procedure Generate Schedule , We were making default value
196 for argument p_simulate_flag to null but it should be 'N'
197 Which resulting in execution not happening for vat
198
199 Solution - Signature of procedure generate_schedule is changes for argument
200 p_simulate_flag from NULL TO 'N'
201
202 Dependncies - jai_rcv_rgm_clm.pls , jai_rcv_rgm_clm.plb
203
204 17 29-05-2007 sacsehi for bug 6078460 File version 120.11
205 R12RUP03-ST1: RTV NOT WORKING
206
207 Problem - Cursor c_regime where clause was wrongly specified which
208 resulting in failure in generation of rtv accounting .....
209
210 18. 21/06/2007 brathod, for bug# 6109941, File Version 120.12, 120.13
211 1. Removed update of ATTRIBUTE (DFF) columns of RCV_SHIPMENT_HEADERS and RCV_SHIPMENT_LINES table
212 2. added excise_invoice_no and excise_invoice_date in cursor c_shipment_lines
213 3. Changed reference of r_trx.excise_invoice_no and r_trx.excise_invoice_date to
214 rec_lines.excise_invoice_no and rec_lines.excise_invoice_date resp.
215
216
217 Future Dependencies For the release Of this Object:-
218 (Please add a row in the section below only if your bug introduces a dependency due to spec change/ A new call to a object/
219 A datamodel change )
220
221 ============================================================================================================
222 --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
223 Current Version Current Bug Dependent Files Version Author Date Remarks
224 Of File On Bug/Patchset Dependent On
225 jai_ap_interface_pkg_b.sql
226 --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
227 115.0 4248727 4245089 This is Part of VAT Enhancement, so dependent on VAT Enhancement
228 115.2 4250171 4250171 There are changes done for OPM. So dependency is introduced
229 115.3 4278511 4278511 There are changes done for OPM. So dependency is introduced
230 115.4 4279050 4279050 jai_rcv_rgm_claims_s.sql 115.1 Sanjikum 07/04/2005
231 115.4 4279050 4279050 ja_in_create_4279050.sql 115.0 Sanjikum 07/04/2005
232 --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
233 ****************************************************************************************************************************************************************************************/
234 p_process_status := jai_constants.successful;
235 p_process_message := NULL;
236
237 OPEN c_trx(p_transaction_id);
238 FETCH c_trx INTO r_trx;
239 CLOSE c_trx;
240
241 OPEN c_base_trx(p_transaction_id);
242 FETCH c_base_trx INTO r_base_trx;
243 CLOSE c_base_trx;
244
245 OPEN c_trx(r_base_trx.parent_transaction_id);
246 FETCH c_trx INTO r_parent_trx;
247 CLOSE c_trx;
248
249 OPEN c_base_trx(r_base_trx.parent_transaction_id);
250 FETCH c_base_trx INTO r_parent_base_trx;
251 CLOSE c_base_trx;
252
253 IF r_base_trx.transaction_type = 'CORRECT' THEN
254 lv_transaction_type := r_parent_base_trx.transaction_type;
255 ELSE
256 lv_transaction_type := r_base_trx.transaction_type;
257 END IF;
258
259 /* Vijay Shankar for Bug#4250171. following condition added to implement VAT Functionality for OPM */
260 if r_trx.attribute_category = jai_rcv_trx_processing_pkg.OPM_RECEIPT then
261
262 if lv_transaction_type = 'RETURN TO VENDOR' THEN
263 ln_location_id := r_parent_trx.location_id;
264 lv_subinventory := r_base_trx.subinventory;
265 else
266 ln_location_id := r_trx.location_id;
267 lv_subinventory := r_base_trx.subinventory;
268 end if;
269
270 -- if both location and subinventory are NULL then goto the parent type i.e RTV to RECEIVE and RTR to DELIVER
271 ELSIF nvl(r_base_trx.location_id, 0) = 0 AND nvl(r_base_trx.subinventory, '-XX') = '-XX' THEN
272 -- following condition added by Vijay Shankar for Bug#4038024. Incase of CORRECT transactions, if location and subinventory
273 -- are not present, then we need to look at parent transaction for location. this will mostly happen for DIRECT DELIVERY case
274 IF lv_transaction_type IN ('RETURN TO RECEIVING', 'RETURN TO VENDOR')
275 OR (r_base_trx.transaction_type = 'CORRECT' AND r_parent_base_trx.transaction_type IN ('RECEIVE', 'DELIVER'))
276 THEN
277
278 ln_location_id := r_parent_trx.location_id;
279
280 -- Incase of Direct Delivery RECEIVE transaction may not have both the location and subinventory. In this case we need to fetch the
281 -- subinventory from DELIVER transaction
282 ELSIF lv_transaction_type = 'RECEIVE' AND r_base_trx.routing_header_id = 3 THEN -- this will not execute for correct transactions
283 OPEN c_dlry_subinventory(r_base_trx.shipment_line_id, p_transaction_id, 'DELIVER'); /* Modified by Ramananda for removal of SQL LITERALs :bug#4428980*/
284 FETCH c_dlry_subinventory INTO lv_subinventory;
285 CLOSE c_dlry_subinventory;
286
287 END IF;
288
289 IF (lv_transaction_type in ('RETURN TO RECEIVING', 'DELIVER') AND nvl(lv_subinventory,'-XX')='-XX')
290 OR (lv_transaction_type in ('RETURN TO VENDOR', 'RECEIVE') AND nvl(ln_location_id,0)=0 AND nvl(lv_subinventory,'-XX')='-XX' )
291 THEN
292
293 IF lv_transaction_type IN ('DELIVER', 'RETURN TO RECEIVING') THEN
294 lv_required_trx_type := 'DELIVER';
295 ELSIF lv_transaction_type IN ('RECEIVE', 'RETURN TO VENDOR') THEN
296 lv_required_trx_type := 'RECEIVE';
297 END IF;
298
299 ln_ancestor_trxn_id := jai_rcv_trx_processing_pkg.get_ancestor_id(
300 p_transaction_id => p_transaction_id,
301 p_shipment_line_id => r_base_trx.shipment_line_id,
302 p_required_trx_type => lv_required_trx_type
303 );
304
305 IF ln_ancestor_trxn_id IS NOT NULL THEN
306 OPEN c_base_trx(ln_ancestor_trxn_id);
307 FETCH c_base_trx INTO r_ancestor_dtls;
308 CLOSE c_base_trx;
309
310 ln_location_id := r_ancestor_dtls.location_id;
311 lv_subinventory := r_ancestor_dtls.subinventory;
312 END IF;
313
314 END IF;
315
316 ELSE
317 ln_location_id := r_base_trx.location_id;
318 lv_subinventory := r_base_trx.subinventory;
319 END IF;
320
321 IF lv_subinventory IS NOT NULL THEN
322 OPEN c_loc_linked_to_org_subinv(r_base_trx.organization_id, lv_subinventory);
323 FETCH c_loc_linked_to_org_subinv INTO r_subinv_dtls;
324 CLOSE c_loc_linked_to_org_subinv;
325
326 IF (nvl(ln_location_id,0) = 0
327 /* following condition added by Vijay Shankar for Bug#4278511 to take care of ISO Scenario */
328 /* Bug 4589354. Added by Lakshmi Gopalsami.
329 Commented the following condition.
330 OR (r_base_trx.source_document_code = 'REQ' and */
331 OR nvl(r_subinv_dtls.location_id, 0) <> 0 )
332 THEN
333 ln_location_id := r_subinv_dtls.location_id;
334 END IF;
335
336 END IF;
337
338 IF nvl(ln_location_id, 0) <> 0 THEN
339 OPEN c_inv_org_linked_to_location(ln_location_id);
340 FETCH c_inv_org_linked_to_location INTO ln_organization_id;
341 CLOSE c_inv_org_linked_to_location;
342
343 IF r_base_trx.organization_id <> ln_organization_id THEN
344 ln_location_id := 0;
345 END IF;
346 END IF;
347
348 p_location_id := ln_location_id;
349 EXCEPTION
350 WHEN OTHERS THEN
351 p_process_status := jai_constants.unexpected_error;
352 p_process_message := SUBSTR('jai_rcv_rgm_claims_pkg.get_location Error:'||SQLERRM,1,250);
353 END get_location;
354
355 PROCEDURE generate_schedule(
356 p_term_id IN jai_rgm_terms.term_id%TYPE DEFAULT NULL,
357 p_shipment_header_id IN rcv_shipment_headers.shipment_header_id%TYPE DEFAULT NULL,
358 p_shipment_line_id IN rcv_shipment_lines.shipment_line_id%TYPE DEFAULT NULL,
359 p_transaction_id IN rcv_transactions.transaction_id%TYPE DEFAULT NULL,
360 p_tax_type IN JAI_CMN_TAXES_ALL.tax_type%TYPE DEFAULT NULL,
361 p_tax_id IN JAI_CMN_TAXES_ALL.tax_id%TYPE DEFAULT NULL,
362 p_override IN VARCHAR2, --File.Sql.35 Cbabu DEFAULT 'N',
363 --p_simulate_flag IN VARCHAR2 DEFAULT 'N', --Added for Bug 4950914
364 p_process_status OUT NOCOPY VARCHAR2,
365 p_process_message OUT NOCOPY VARCHAR2,
366 /*Bug 5096787. Added by Lakshmi Gopalsami */
367 p_simulate_flag IN VARCHAR2 DEFAULT 'N' -- Date 28/05/2007 sacsethi for bug 6071533 Change default value from null to N
368 )
369 IS
370 CURSOR cur_lines(cp_shipment_header_id IN rcv_shipment_headers.shipment_header_id%TYPE,
371 cp_shipment_line_id IN rcv_shipment_lines.shipment_line_id%TYPE)
372 IS
373 SELECT shipment_header_id, shipment_line_id
374 FROM JAI_RCV_LINES
375 WHERE shipment_header_id = NVL(cp_shipment_header_id, shipment_header_id)
376 AND shipment_line_id = NVL(cp_shipment_line_id, shipment_line_id)
377 ORDER BY shipment_line_id;
378
379 CURSOR cur_txns(cp_shipment_line_id IN rcv_shipment_lines.shipment_line_id%TYPE,
380 cp_transaction_id IN rcv_transactions.transaction_id%TYPE)
381 IS
382 SELECT transaction_id,
383 transaction_type,
384 transaction_date,
385 tax_transaction_id,
386 parent_transaction_type,
387 currency_conversion_rate,
388 quantity,
389 DECODE(transaction_type, 'RECEIVE', 1, 'RETURN TO VENDOR', -1, 'CORRECT',
390 DECODE(parent_transaction_type, 'RECEIVE', SIGN(quantity), 'RETURN TO VENDOR', SIGN(quantity)*-1)) quantity_multiplier
391 FROM JAI_RCV_TRANSACTIONS
392 WHERE shipment_line_id = NVL(cp_shipment_line_id, shipment_line_id)
393 AND transaction_id = NVL(cp_transaction_id, transaction_id)
394 AND (
395 transaction_type IN ('RECEIVE', 'RETURN TO VENDOR')
396 OR
397 ( transaction_type = 'CORRECT'
398 AND parent_transaction_type IN ('RECEIVE', 'RETURN TO VENDOR')
399 )
400 )
401 ORDER BY transaction_id;
402
403 CURSOR cur_tax(cp_transaction_id IN rcv_transactions.transaction_id%TYPE,
404 cp_currency_conversion_rate IN JAI_RCV_TRANSACTIONS.currency_conversion_rate%TYPE)
405 IS
406 SELECT ROUND(--added by Nitin for bug no 6681800
407 DECODE(a.currency, jai_constants.func_curr, a.tax_amount*(b.mod_cr_percentage/100), a.tax_amount*cp_currency_conversion_rate*(b.mod_cr_percentage/100)),
408 NVL(b.rounding_factor, 0)
409 ) tax_amount
410 /*DECODE(a.currency, jai_constants.func_curr, a.tax_amount, a.tax_amount*cp_currency_conversion_rate),
411 NVL(b.rounding_factor, 0) Commented by Nitin for Bug:# 6681800
412 ) tax_amount*/,
413 a.tax_type,
414 a.tax_id,
415 NVL(b.rounding_factor,0) rounding_factor
416 FROM JAI_RCV_LINE_TAXES a,
417 JAI_CMN_TAXES_ALL b
418 WHERE a.transaction_id = cp_transaction_id
419 AND a.tax_type IN ( select tax_type
420 from jai_regime_tax_types_v
421 where regime_code = jai_constants.vat_regime
422 )
423 AND a.tax_id = b.tax_id
424 AND a.modvat_flag = 'Y'
425 --Added for Bug#4950914
426 AND NVL(a.tax_amount,0) <> 0;
427
428 CURSOR cur_term(cp_shipment_line_id IN rcv_shipment_lines.shipment_line_id%TYPE)
429 IS
430 SELECT term_id, rcv_rgm_line_id, receipt_date
431 FROM jai_rcv_rgm_lines
432 WHERE shipment_line_id = cp_shipment_line_id;
433
434 CURSOR cur_sum_schedules(cp_schedule_id IN NUMBER)
435 IS
436 SELECT SUM(installment_amount) total_installment_amount, MAX(installment_no) max_installment_no
437 FROM jai_rgm_trm_schedules_t
438 WHERE schedule_id = cp_schedule_id;
439
440 --Added the cursor for Bug#4950914
441 CURSOR cur_installment_count( cp_rcv_rgm_line_id IN NUMBER,
442 cp_transaction_id IN NUMBER,
443 cp_tax_id IN NUMBER,
444 cp_schedule_id IN NUMBER)
445 IS
446 SELECT COUNT(*) count
447 FROM jai_rcv_rgm_claims
448 WHERE rcv_rgm_line_id = cp_rcv_rgm_line_id
449 AND transaction_id = cp_transaction_id
450 AND tax_id = cp_tax_id
451 AND installment_no IN ( SELECT installment_no
452 FROM jai_rgm_trm_schedules_t
453 WHERE schedule_id = cp_schedule_id);
454
455
456 r_term cur_term%ROWTYPE;
457 ln_schedule_id NUMBER;
458 lv_process_flag VARCHAR2(2);
459 lv_process_msg VARCHAR2(1000);
460 ln_amount NUMBER;
461 r_sum_schedules cur_sum_schedules%ROWTYPE;
462 r_installment_count cur_installment_count%ROWTYPE; --Added for Bug#4950914
463
464 ln_apportion_factor NUMBER;
465
466 BEGIN
467
468 p_process_status := jai_constants.successful;
469 p_process_message := NULL;
470
471 IF p_override = 'Y' THEN
472 DELETE jai_rcv_rgm_claims
473 WHERE shipment_header_id = NVL(p_shipment_header_id, shipment_header_id)
474 AND shipment_line_id = NVL(p_shipment_line_id, shipment_line_id)
475 AND transaction_id = NVL(p_transaction_id, transaction_id)
476 AND tax_type = NVL(p_tax_type, tax_type)
477 AND tax_id = NVL(p_tax_id, tax_id);
478 END IF;
479
480 FOR rec_lines IN cur_lines(p_shipment_header_id, p_shipment_line_id)
481 LOOP
482 IF gv_debug THEN
483 fnd_file.put_line(fnd_file.log, 'In Generate Schedule -- LOOP 1:');
484 fnd_file.put_line(fnd_file.log, 'Shipment Header :'||rec_lines.shipment_header_id||' shipment_line_id '||rec_lines.shipment_line_id);
485 END IF;
486
487 OPEN cur_term(rec_lines.shipment_line_id);
488 FETCH cur_term INTO r_term;
489 CLOSE cur_term;
490
491 FOR rec_txns IN cur_txns(rec_lines.shipment_line_id, p_transaction_id)
492 LOOP
493 ln_apportion_factor := ABS(jai_rcv_trx_processing_pkg.get_apportion_factor(rec_txns.transaction_id));
494
495 IF gv_debug THEN
496 fnd_file.put_line(fnd_file.log, 'ln_apportion_factor: '||ln_apportion_factor);
497 fnd_file.put_line(fnd_file.log, 'Transaction_id: '||rec_txns.transaction_id);
498 fnd_file.put_line(fnd_file.log, 'Tax_transaction_id: '||rec_txns.tax_transaction_id);
499 fnd_file.put_line(fnd_file.log, 'p_simulate_flag: '||p_simulate_flag);
500 END IF;
501
502
503 FOR tax_rec IN cur_tax(rec_txns.tax_transaction_id, rec_txns.currency_conversion_rate)
504 LOOP
505 ln_amount := tax_rec.tax_amount * ln_apportion_factor;
506
507 IF gv_debug THEN
508 fnd_file.put_line(fnd_file.log, 'tax_rec.tax_amount: '||tax_rec.tax_amount);
509 fnd_file.put_line(fnd_file.log, 'ln_amount: '||ln_amount);
510 END IF;
511
512 jai_cmn_rgm_terms_pkg.generate_term_schedules(p_term_id => NVL(p_term_id,r_term.term_id),
513 p_amount => ln_amount,
514 p_register_date => r_term.receipt_date,
515 p_schedule_id => ln_schedule_id,
516 p_process_flag => lv_process_flag,
517 p_process_msg => lv_process_msg);
518
519 IF lv_process_flag <> jai_constants.successful THEN
520
521 --Added for Bug#4950914
522 DELETE jai_rgm_trm_schedules_t
523 WHERE schedule_id = ln_schedule_id;
524 fnd_file.put_line(fnd_file.log, ' After Generating jai_cmn_rgm_terms_pkg.generate_term_schedules');
525 p_process_status := lv_process_flag;
526 p_process_message := lv_process_msg;
527 RETURN;
528 END IF;
529
530
531 --Start Added by Sanjikum for Bug#4950914
532 r_installment_count := NULL;
533
534 OPEN cur_installment_count( cp_rcv_rgm_line_id => r_term.rcv_rgm_line_id,
535 cp_transaction_id => rec_txns.transaction_id,
536 cp_tax_id => tax_rec.tax_id,
537 cp_schedule_id => ln_schedule_id);
538 FETCH cur_installment_count INTO r_installment_count;
539 CLOSE cur_installment_count;
540
541 IF r_installment_count.count > 0 THEN
542 DELETE jai_rgm_trm_schedules_t
543 WHERE schedule_id = ln_schedule_id;
544
545 IF p_simulate_flag = 'Y' THEN
546 p_process_status := jai_constants.expected_error;
547 p_process_message := 'Duplicate Records in jai_rcv_rgm_claims';
548 --This message text is being compared for duplication check, so don't change this.
549 --Or search for the same text in this package and change it at all the places
550 RETURN;
551 ELSE
552 EXIT;
553 END IF;
554
555 END IF;
556
557 IF p_simulate_flag = 'N' THEN
558 --End Added by Sanjikum for Bug#4950914
559
560 UPDATE jai_rgm_trm_schedules_t
561 SET installment_amount = ROUND(installment_amount, tax_rec.rounding_factor)
562 WHERE schedule_id = ln_schedule_id;
563
564 OPEN cur_sum_schedules(ln_schedule_id);
565 FETCH cur_sum_schedules INTO r_sum_schedules;
566 CLOSE cur_sum_schedules;
567 IF NVL(r_sum_schedules.total_installment_amount,0) <> NVL(ln_amount,0) THEN
568 UPDATE jai_rgm_trm_schedules_t
569 SET installment_amount = installment_amount + ln_amount - r_sum_schedules.total_installment_amount
570 WHERE installment_no = r_sum_schedules.max_installment_no
571 AND schedule_id = ln_schedule_id;
572 END IF;
573
574 UPDATE jai_rgm_trm_schedules_t
575 SET installment_amount = installment_amount*rec_txns.quantity_multiplier
576 WHERE schedule_id = ln_schedule_id;
577
578 INSERT
579 INTO jai_rcv_rgm_claims
580 (
581 CLAIM_SCHEDULE_ID,
582 RCV_RGM_LINE_ID,
583 Shipment_header_id,
584 Shipment_line_id,
585 Regime_code,
586 Tax_transaction_id,
587 Transaction_type,
588 Transaction_id,
589 Parent_transaction_type,
590 Installment_no,
591 Installment_amount,
592 Claimed_amount,
593 Scheduled_date,
594 claimed_date,
595 Status,
596 Manual_claim_flag,
597 Remarks,
598 Tax_type,
599 Tax_id,
600 Trx_tax_id,
601 CREATED_BY,
602 CREATION_DATE,
603 LAST_UPDATED_BY,
604 LAST_UPDATE_DATE,
605 LAST_UPDATE_LOGIN
606 )
607 SELECT jai_rcv_rgm_claims_s.NEXTVAL,
608 r_term.rcv_rgm_line_id,
609 rec_lines.shipment_header_id,
610 rec_lines.shipment_line_id,
611 jai_constants.vat_regime,
612 rec_txns.tax_transaction_id,
613 rec_txns.transaction_type,
614 rec_txns.transaction_id,
615 rec_txns.parent_transaction_type,
616 installment_no,
617 installment_amount,
618 NULL,
619 installment_date,
620 NULL,
621 'N',
622 NULL,
623 NULL,
624 tax_rec.tax_type,
625 tax_rec.tax_id,
626 NULL,
627 fnd_global.user_id,
628 SYSDATE,
629 fnd_global.user_id,
630 SYSDATE,
631 fnd_global.login_id
632 FROM jai_rgm_trm_schedules_t
633 WHERE schedule_id = ln_schedule_id;
634 END IF; --end of p_simulate_flag
635
636
637 DELETE jai_rgm_trm_schedules_t
638 WHERE schedule_id = ln_schedule_id;
639
640 END LOOP;
641 END LOOP;
642 END LOOP;
643 EXCEPTION
644 WHEN OTHERS THEN
645 p_process_status := jai_constants.unexpected_error;
646 p_process_message := SUBSTR(SQLERRM,1,200);
647 fnd_file.put_line(fnd_file.log, 'gone into error ');
648 END generate_schedule;
649
650 PROCEDURE insert_rcv_lines(
651 p_shipment_header_id IN rcv_shipment_headers.shipment_header_id%TYPE,
652 p_shipment_line_id IN rcv_shipment_lines.shipment_line_id%TYPE,
653 p_transaction_id IN rcv_transactions.transaction_id%TYPE,
654 p_regime_code IN JAI_RGM_DEFINITIONS.regime_code%TYPE,
655 p_simulate_flag IN VARCHAR2, --File.Sql.35 Cbabu DEFAULT 'N',
656 p_process_status OUT NOCOPY VARCHAR2,
657 p_process_message OUT NOCOPY VARCHAR2)
658 IS
659 r_trx c_trx%ROWTYPE;
660 r_base_trx c_base_trx%ROWTYPE;
661
662 /* File.Sql.35 BY Brathod */
663 ln_recoverable_amount NUMBER ; -- := 0;
664 ln_non_recoverable_amt NUMBER ; -- := 0;
665 ln_dup_chk NUMBER ; -- := 0;
666 lv_localpath VARCHAR2(100); -- := '';
667 /* End of File.Sql.35 by Brathod*/
668
669 lv_breakup_type VARCHAR2(10);
670 lv_process_status VARCHAR2(2);
671 lv_process_message VARCHAR2(1000);
672 ln_apportion_factor NUMBER;
673 ln_curr_conv NUMBER;
674 lv_process_flag VARCHAR2(2);
675 lv_process_msg VARCHAR2(1996);
676 lv_item_class jai_rgm_lookups.attribute_code%TYPE;
677 ln_term_id NUMBER;
678 ln_location_id NUMBER(15);
679
680 CURSOR c_tax_amount (cp_shipment_line_id IN rcv_shipment_lines.shipment_line_id%TYPE)
681 IS
682 SELECT nvl(rtl.tax_amount, 0) tax_amount,
683 nvl(rtl.modvat_flag, 'N') modvat_flag,
684 nvl(rtl.currency, jai_constants.func_curr) currency,
685 nvl(jtc.rounding_factor, 0) rnd,
686 nvl(jtc.mod_cr_percentage, 0) modvat_percentage /*Added by Nitin for bug # 6681800*/
687 FROM JAI_RCV_LINE_TAXES rtl,
688 JAI_CMN_TAXES_ALL jtc
689 WHERE shipment_line_id = cp_shipment_line_id
690 AND jtc.tax_id = rtl.tax_id
691 AND rtl.modvat_flag = 'Y'
692 AND jtc.tax_type IN (select tax_type
693 from jai_regime_tax_types_v
694 where regime_code = jai_constants.vat_regime);
695
696 CURSOR c_shipment_lines(cp_shipment_header_id IN rcv_shipment_headers.shipment_header_id%TYPE,
697 cp_shipment_line_id IN rcv_shipment_lines.shipment_line_id%TYPE)
698 IS
699 SELECT a.shipment_line_id, a.transaction_id, a.inventory_item_id, a.receipt_num, b.line_num
700 , excise_invoice_no, excise_invoice_date -- brathod, Bug# 6109941
701 FROM JAI_RCV_LINES a,
702 rcv_shipment_lines b
703 WHERE a.shipment_header_id = b.shipment_header_id
704 AND a.shipment_line_id = b.shipment_line_id
705 AND a.shipment_header_id = NVL(cp_shipment_header_id,a.shipment_header_id)
706 AND a.shipment_line_id = NVL(cp_shipment_line_id, a.shipment_line_id);
707
708 CURSOR c_regime (cp_regime_id IN JAI_RGM_DEFINITIONS.regime_id%TYPE DEFAULT NULL,
709 cp_regime_code IN JAI_RGM_DEFINITIONS.regime_code%TYPE DEFAULT NULL)
710 IS
711 SELECT *
712 FROM JAI_RGM_DEFINITIONS
713 WHERE regime_id = NVL(cp_regime_id, regime_id)
714 AND regime_code = NVL(cp_regime_code, regime_code);
715
716 /* Bug 4929929. Added by Lakshmi Gopalsami
717 Removed the NVL as shipment_header_id and shipment_line_id
718 cannot be null.
719 */
720
721 CURSOR c_receipt_source_code(cp_shipment_header_id IN rcv_shipment_headers.shipment_header_id%TYPE,
722 cp_shipment_line_id IN rcv_shipment_lines.shipment_line_id%TYPE)
723 IS
724 SELECT receipt_source_code
725 FROM rcv_shipment_headers a,
726 rcv_shipment_lines b
727 WHERE a.shipment_header_id = b.shipment_header_id
728 AND a.shipment_header_id = cp_shipment_header_id
729 AND b.shipment_line_id = cp_shipment_line_id;
730
731 r_regime c_regime%ROWTYPE;
732 r_receipt_source_code c_receipt_source_code%ROWTYPE;
733
734 -- added, Ramananda for bug # 4519719
735
736 CURSOR c_vat_exists(cp_shipment_line_id NUMBER )
737 IS
738 SELECT 1
739 FROM JAI_RCV_LINE_TAXES a , jai_regime_tax_types_v b
740 WHERE shipment_line_id = cp_shipment_line_id AND
741 b.regime_code= jai_constants.vat_regime
742 and b.tax_type = a.tax_type ;
743
744 ln_vat_exists NUMBER ;
745
746 -- ended, Ramananda for bug # 4519719
747 /*bduvarag Bug 5899383 Start*/
748 lv_def_vat_invoice_no jai_rcv_transactions.excise_invoice_no%type;
749 ld_def_vat_invoice_Date jai_rcv_transactions.excise_invoice_date%type;
750 lv_default_invoice_setup VARCHAR2(100);
751 /*bduvarag Bug 5899383 End*/
752
753 BEGIN
754
755 -- added, Ramananda for bug # 4519719
756
757 OPEN c_vat_exists(p_shipment_line_id) ;
758 FETCH c_vat_exists INTO ln_vat_exists ;
759 CLOSE c_vat_exists ;
760
761 IF ln_vat_exists <> 1 THEN
762 return ;
763 END IF ;
764
765 -- ended, Ramananda for bug # 4519719
766
767 p_process_status := jai_constants.successful;
768 p_process_message := NULL;
769
770 /* File.Sql.35 by Brathod */
771 ln_recoverable_amount := 0;
772 ln_non_recoverable_amt := 0;
773 ln_dup_chk := 0;
774 lv_localpath := '';
775 /* End of File.Sql.35 by Brathod */
776
777 OPEN c_regime(NULL, jai_constants.vat_regime);
778 FETCH c_regime INTO r_regime;
779 CLOSE c_regime;
780
781 OPEN c_receipt_source_code(p_shipment_header_id, p_shipment_line_id);
782 FETCH c_receipt_source_code INTO r_receipt_source_code;
783 CLOSE c_receipt_source_code;
784
785 IF gv_debug THEN
786 fnd_file.put_line(fnd_file.log, '1 insert_rcv_lines: Bef Main loop:');
787 END IF;
788
789 FOR rec_lines IN c_shipment_lines(p_shipment_header_id, p_shipment_line_id)
790 LOOP
791 OPEN c_trx(rec_lines.transaction_id);
792 FETCH c_trx INTO r_trx;
793 CLOSE c_trx;
794
795
796 IF r_trx.transaction_type IN ('RECEIVE', 'MATCH') THEN
797
798 select count(1)
799 into ln_dup_chk
800 from jai_rcv_rgm_lines
801 where transaction_id = r_trx.transaction_id;
802
803 IF gv_debug THEN
804 fnd_file.put_line(fnd_file.log, '2 insert_rcv_lines:ln_dup_chk:'||ln_dup_chk);
805 END IF;
806
807 IF ln_dup_chk > 0 THEN
808 return;
809 END IF;
810
811 ln_recoverable_amount := 0;
812
813 FOR tax_rec IN c_tax_amount(rec_lines.shipment_line_id)
814 LOOP
815 IF tax_rec.currency <> jai_constants.func_curr THEN
816 ln_curr_conv := NVL(r_trx.currency_conversion_rate, 1);
817 ELSE
818 ln_curr_conv := 1;
819 END IF;
820 ln_recoverable_amount := ln_recoverable_amount + round(tax_rec.tax_amount * ln_curr_conv * (tax_rec.modvat_percentage/100), tax_rec.rnd); /*ADDED BY NITIN FOR BUG # 6681800*/
821 END LOOP;
822
823 IF gv_debug THEN
824 fnd_file.put_line(fnd_file.log, '3 insert_rcv_lines:ln_recoverable_amount:'||ln_recoverable_amount);
825 END IF;
826
827 --In the Final Observation Change
828 if ln_recoverable_amount = 0 then
829 p_process_status := jai_constants.successful;
830 p_process_message := NULL;
831 RETURN;
832 end if;
833
834 OPEN c_base_trx(rec_lines.transaction_id);
835 FETCH c_base_trx INTO r_base_trx;
836 CLOSE c_base_trx;
837
838 ln_location_id := r_base_trx.location_id;
839
840 IF r_base_trx.location_id IS NULL
841 /* following condition added by Vijay Shankar for Bug#4278511 to take care of ISO Scenario */
842 OR r_base_trx.source_document_code = 'REQ'
843 THEN
844 get_location( p_transaction_id => rec_lines.transaction_id,
845 p_location_id => ln_location_id,
846 p_process_status => lv_process_flag,
847 p_process_message => lv_process_msg);
848
849 IF lv_process_flag <> jai_constants.successful THEN
850 p_process_status := lv_process_flag;
851 p_process_message := 'Error in Line Number '||rec_lines.line_num||' - '||lv_process_msg;
852 RETURN;
853 END IF;
854 END IF;
855 IF gv_debug THEN
856 fnd_file.put_line(fnd_file.log, '4 insert_rcv_lines: after get_location:'||r_base_trx.location_id);
857 END IF;
858
859
860 jai_inv_items_pkg.jai_get_attrib(
861 p_regime_code => r_regime.regime_code,
862 p_organization_id => r_base_trx.organization_id,
863 p_inventory_item_id => rec_lines.inventory_item_id,
864 p_attribute_code => 'ITEM CLASS',
865 p_attribute_value => lv_item_class,
866 p_process_flag => lv_process_flag,
867 p_process_msg => lv_process_msg
868 );
869 IF lv_process_flag <> jai_constants.successful THEN
870 p_process_status := lv_process_flag;
871 p_process_message := 'Error in Line Number '||rec_lines.line_num||' - '||lv_process_msg;
872 RETURN;
873 END IF;
874
875
876 jai_cmn_rgm_terms_pkg.get_term_id(
877 p_regime_id => r_regime.regime_id,
878 p_item_id => rec_lines.inventory_item_id,
879 p_organization_id => r_base_trx.organization_id,
880 p_party_type => jai_constants.orgn_type_io,
881 p_location_id => ln_location_id,
882 p_term_id => ln_term_id,
883 p_process_flag => lv_process_flag,
884 p_process_msg => lv_process_msg
885 );
886 IF lv_process_flag <> jai_constants.successful THEN
887 p_process_status := lv_process_flag;
888 p_process_message := 'Error in Line Number '||rec_lines.line_num||' - '||lv_process_msg;
889 RETURN;
890 END IF;
891
892 jai_cmn_rgm_terms_pkg.set_term_in_use(
893 p_term_id => ln_term_id,
894 p_process_flag => lv_process_flag,
895 p_process_msg => lv_process_msg
896 );
897
898 IF lv_process_flag <> jai_constants.successful THEN
899 p_process_status := lv_process_flag;
900 p_process_message := 'Error in Line Number '||rec_lines.line_num||' - '||lv_process_msg;
901 RETURN;
902 END IF;
903
904 -- even if there is no VAT, then the data goes into this table
905 /*bduvarag Bug 5899383 start*/
906 lv_default_invoice_setup := jai_cmn_rgm_recording_pkg.get_rgm_attribute_value(
907 pv_regime_code => jai_constants.vat_regime,
908 pv_organization_type => jai_constants.orgn_type_io,
909 pn_organization_id => r_trx.organization_id,
910 pn_location_id => ln_location_id,
911 pv_registration_type => jai_constants.regn_type_others, --'OTHERS',
912 pv_attribute_type_code => NULL,
913 pv_attribute_code => 'DEFAULT_INVOICE_DETAILS' ); --'DEFAULT_INVOICE_DETAILS');
914
915
916 --
917 -- Bug# 6109941
918 -- All the reference to r_trx.excise_invoice_no and r_trx.excise_invoice_date are changed to
919 -- rec_lines.excise_invoice_no and rec_lines.excise_invoice_date resp.
920 --
921 IF gv_debug THEN
922 fnd_file.put_line(fnd_file.log, 'ABCD def_inv_setup : lv_default_invoice_setup:'||lv_default_invoice_setup);
923 fnd_file.put_line(fnd_file.log, 'ABCD r_trx.organization_id : '||r_trx.organization_id);
924 fnd_file.put_line(fnd_file.log, 'ABCD ln_location_id : '||ln_location_id);
925 fnd_file.put_line(fnd_file.log, 'ABCD rec_lines.excise_invoice_no : '||rec_lines.excise_invoice_no); -- Bug# 6109941
926 fnd_file.put_line(fnd_file.log, 'ABCD rec_lines.excise_invoice_date : '||rec_lines.excise_invoice_date); -- Bug# 6109941
927
928 END IF;
929
930 IF upper(lv_default_invoice_setup) in ( 'Y', 'YES') then
931
932 /* Means - We can use the excise invoice number as vat invoice number and excise invoice date as vat invoice date
933 || Need to check whether the ja_in_rcv_transactions has the excise_invoice_no and excise_invoice_Date stamped
934 for the shipment line id .
935 */
936
937 IF rec_lines.excise_invoice_no is not NULL THEN -- Bug# 6109941
938 lv_def_vat_invoice_no := rec_lines.excise_invoice_no; -- Bug# 6109941
939 ld_def_vat_invoice_Date := rec_lines.excise_invoice_date; -- Bug# 6109941
940 ELSE
941 lv_def_vat_invoice_no := NULL;
942 ld_def_vat_invoice_Date := NULL;
943 END IF;
944
945 --
946 -- End Bug# 6109941
947 --
948
949
950 END IF;
951 /*bduvarag Bug 5899383 End*/
952
953 INSERT
954 INTO jai_rcv_rgm_lines
955 (
956 RCV_RGM_LINE_ID,
957 SHIPMENT_HEADER_ID,
958 SHIPMENT_LINE_ID,
959 ORGANIZATION_ID,
960 LOCATION_ID,
961 INVENTORY_ITEM_ID,
962 RECEIPT_NUM,
963 RECEIPT_DATE,
964 REGIME_CODE,
965 REGIME_ITEM_CLASS,
966 TRANSACTION_ID,
967 RECOVERABLE_AMOUNT,
968 RECOVERED_AMOUNT,
969 PROCESS_STATUS_FLAG,
970 TERM_ID,
971 INVOICE_NO,
972 INVOICE_DATE,
973 VENDOR_ID,
974 VENDOR_SITE_ID,
975 RECEIPT_SOURCE_CODE,
976 RECEIVE_QTY,
977 CORRECT_RECEIVE_QTY,
978 RTV_QTY,
979 CORRECT_RTV_QTY,
980 CREATED_BY,
981 CREATION_DATE,
982 LAST_UPDATED_BY,
983 LAST_UPDATE_DATE,
984 LAST_UPDATE_LOGIN
985 )
986 VALUES
987 (
988 jai_rcv_rgm_lines_s.NEXTVAL,
989 r_base_trx.shipment_header_id,
990 r_base_trx.shipment_line_id,
991 r_base_trx.organization_id,
992 ln_location_id,
993 rec_lines.inventory_item_id,
994 rec_lines.receipt_num,
995 r_base_trx.transaction_date,
996 jai_constants.vat_regime,
997 lv_item_class,
998 r_trx.transaction_id,
999 ln_recoverable_amount,
1000 0,
1001 DECODE(NVL(ln_recoverable_amount,0), 0, 'X', 'N'),
1002 ln_term_id,
1003 lv_def_vat_invoice_no, /* bduvarag Bug# 5899383*/
1004 ld_def_vat_invoice_Date, /* bduvarag Bug# 5899383*/
1005 NULL,
1006 NULL,
1007 r_receipt_source_code.receipt_source_code,
1008 r_base_trx.quantity,
1009 NULL,
1010 NULL,
1011 NULL,
1012 fnd_global.user_id,
1013 SYSDATE,
1014 fnd_global.user_id,
1015 SYSDATE,
1016 fnd_global.login_id);
1017 END IF;
1018 END LOOP;
1019 EXCEPTION
1020 WHEN OTHERS THEN
1021 p_process_status := jai_constants.unexpected_error;
1022 p_process_message := SUBSTR(SQLERRM,1,200);
1023 END insert_rcv_lines;
1024
1025 PROCEDURE update_rcv_lines(
1026 p_shipment_header_id IN rcv_shipment_headers.shipment_header_id%TYPE,
1027 p_shipment_line_id IN rcv_shipment_lines.shipment_line_id%TYPE DEFAULT NULL,
1028 p_receipt_num IN JAI_RCV_LINES.receipt_num%TYPE DEFAULT NULL,
1029 p_recoverable_amount IN jai_rcv_rgm_lines.recoverable_amount%TYPE DEFAULT NULL,
1030 p_recovered_amount IN jai_rcv_rgm_lines.recovered_amount%TYPE DEFAULT NULL,
1031 p_term_id IN jai_rgm_terms.term_id%TYPE DEFAULT -999,
1032 p_invoice_no IN JAI_RCV_TRANSACTIONS.vat_invoice_no%TYPE, --File.Sql.35 Cbabu DEFAULT '-X9X',
1033 p_invoice_date IN JAI_RCV_TRANSACTIONS.vat_invoice_date%TYPE, --File.Sql.35 Cbabu DEFAULT TO_DATE('01/01/1900','DD/MM/YYYY'),
1034 p_vendor_id IN po_vendors.vendor_id%TYPE DEFAULT -999,
1035 p_vendor_site_id IN po_vendor_sites_all.vendor_site_id%TYPE DEFAULT NULL,
1036 p_correct_receive_qty IN jai_rcv_rgm_lines.correct_receive_qty%TYPE DEFAULT NULL,
1037 p_rtv_qty IN jai_rcv_rgm_lines.rtv_qty%TYPE DEFAULT NULL,
1038 p_correct_rtv_qty IN jai_rcv_rgm_lines.correct_rtv_qty%TYPE DEFAULT NULL,
1039 p_process_status OUT NOCOPY VARCHAR2,
1040 p_process_message OUT NOCOPY VARCHAR2)
1041 IS
1042 ln_recoverable_amount NUMBER;
1043 ln_recovered_amount NUMBER;
1044 lv_process_status_flag VARCHAR2(2);
1045
1046 CURSOR c_total_amount(cp_shipment_header_id IN rcv_shipment_headers.shipment_header_id%TYPE,
1047 cp_shipment_line_id IN rcv_shipment_lines.shipment_line_id%TYPE)
1048 IS
1049 SELECT NVL(SUM(installment_amount),0) recoverable_amount,
1050 NVL(SUM(claimed_amount),0) recovered_amount
1051 FROM jai_rcv_rgm_claims
1052 WHERE shipment_header_id = cp_shipment_header_id
1053 AND shipment_line_id = cp_shipment_line_id;
1054
1055 r_total_amount c_total_amount%ROWTYPE;
1056 BEGIN
1057 p_process_status := jai_constants.successful;
1058 p_process_message := NULL;
1059
1060 IF p_shipment_line_id IS NULL AND p_receipt_num IS NULL THEN
1061 p_process_status := jai_constants.expected_error;
1062 p_process_message := 'Either of Shipment_line_id or receipt_num is mandatory';
1063 RETURN;
1064 END IF;
1065
1066 OPEN c_total_amount(p_shipment_header_id, p_shipment_line_id);
1067 FETCH c_total_amount INTO r_total_amount;
1068 CLOSE c_total_amount;
1069
1070 UPDATE JAI_RCV_RGM_LINES
1071 SET recoverable_amount = r_total_amount.recoverable_amount,
1072 recovered_amount = r_total_amount.recovered_amount,
1073 correct_receive_qty = NVL(correct_receive_qty,0) + NVL(p_correct_receive_qty,0),
1074 rtv_qty = NVL(rtv_qty,0) + NVL(p_rtv_qty,0),
1075 correct_rtv_qty = NVL(correct_rtv_qty,0) + NVL(p_correct_rtv_qty,0)
1076 WHERE shipment_line_id = NVL(p_shipment_line_id,shipment_line_id)
1077 AND receipt_num = NVL(p_receipt_num, receipt_num)
1078 RETURNING recoverable_amount, recovered_amount INTO ln_recoverable_amount, ln_recovered_amount;
1079
1080 IF (ln_recovered_amount > 0) OR (ln_recoverable_amount = ln_recovered_amount) THEN
1081 --Interchanged the IF and ELSIF conditions for Bug #4279050, by Sanjikum
1082 IF ln_recoverable_amount = ln_recovered_amount THEN --
1083 lv_process_status_flag := 'F';
1084 ELSIF ln_recovered_amount > 0 THEN
1085 lv_process_status_flag := 'P';
1086 END IF;
1087
1088 UPDATE JAI_RCV_RGM_LINES
1089 SET process_status_flag = lv_process_status_flag
1090 WHERE shipment_line_id = NVL(p_shipment_line_id,shipment_line_id)
1091 AND receipt_num = NVL(p_receipt_num, receipt_num);
1092 END IF;
1093
1094 EXCEPTION
1095 WHEN OTHERS THEN
1096 p_process_status := jai_constants.unexpected_error;
1097 p_process_message := SUBSTR(SQLERRM,1,200);
1098 END update_rcv_lines;
1099
1100 PROCEDURE process_vat(
1101 p_transaction_id IN rcv_transactions.transaction_id%TYPE,
1102 p_process_status OUT NOCOPY VARCHAR2,
1103 p_process_message OUT NOCOPY VARCHAR2)
1104 IS
1105 r_trx c_trx%ROWTYPE;
1106 r_base_trx c_base_trx%ROWTYPE;
1107
1108 -- Vijay Shankar for Bug#4346453. RCV DFF Elim. Enh. lv_vat_invoice_no JAI_RCV_TRANSACTIONS.vat_invoice_no%TYPE;
1109
1110 lv_accounting_type VARCHAR2(100);
1111
1112 /* File.Sql.35 by Brathod */
1113 lv_account_nature VARCHAR2(100); -- := 'VAT INTERIM';
1114 lv_source_name VARCHAR2(100); -- := 'Purchasing India';
1115 lv_category_name VARCHAR2(100); -- := 'Receiving India';
1116 lv_reference_23 gl_interface.reference23%TYPE ; -- := 'jai_rgm_claim_pkg.process_vat';
1117 lv_reference_24 gl_interface.reference24%TYPE ; -- := 'rcv_transactions';
1118 lv_reference_25 gl_interface.reference25%TYPE ; -- := p_transaction_id;
1119 lv_reference_26 gl_interface.reference26%TYPE ; -- := 'transaction_id';
1120 lv_destination VARCHAR2(10) ; -- := 'G';
1121 ld_accounting_date DATE ; -- := TRUNC(SYSDATE);
1122 /* End of File.Sql.35 by Brathod */
1123
1124 ln_code_combination_id NUMBER;
1125 ln_entered_dr NUMBER;
1126 ln_entered_cr NUMBER;
1127 lv_currency_code VARCHAR2(10);
1128 lv_reference_10 gl_interface.reference10%TYPE;
1129 lv_code_path VARCHAR2(1996);
1130 lv_process_status VARCHAR2(2);
1131 lv_process_message VARCHAR2(1000);
1132 ln_location_id NUMBER;
1133 ln_statement_id NUMBER;
1134
1135 /*CURSOR cur_total_tax(cp_transaction_id IN JAI_RCV_LINE_TAXES.transaction_id%TYPE,
1136 cp_currency_conversion_rate IN JAI_RCV_TRANSACTIONS.currency_conversion_rate%TYPE)
1137 IS
1138 SELECT SUM(DECODE(currency, jai_constants.func_curr, tax_amount, tax_amount*cp_currency_conversion_rate)) tax_amount
1139 FROM JAI_RCV_LINE_TAXES
1140 WHERE transaction_id = cp_transaction_id
1141 AND tax_type in (select tax_type
1142 from jai_regime_tax_types_v
1143 where regime_code = jai_constants.vat_regime)
1144 AND NVL(modvat_flag,'N') = 'Y';*/
1145
1146 CURSOR c_regime (cp_regime_id IN JAI_RGM_DEFINITIONS.regime_id%TYPE DEFAULT NULL,
1147 cp_regime_code IN JAI_RGM_DEFINITIONS.regime_code%TYPE DEFAULT NULL)
1148 IS
1149 SELECT *
1150 FROM JAI_RGM_DEFINITIONS
1151 WHERE regime_id = NVL(cp_regime_id, regime_id)
1152 AND regime_code = NVL(cp_regime_code, regime_code);
1153
1154 CURSOR c_vat_invoice(cp_transaction_id IN NUMBER)
1155 IS
1156 SELECT vat_invoice_no, vat_invoice_date
1157 FROM JAI_RCV_TRANSACTIONS
1158 WHERE transaction_id = cp_transaction_id;
1159
1160 /*CURSOR cur_txn(cp_transaction_id IN rcv_transactions.transaction_id%TYPE)
1161 IS
1162 SELECT currency_conversion_rate,
1163 quantity,
1164 DECODE(transaction_type, 'RECEIVE', 1, 'RETURN TO VENDOR', -1, 'CORRECT',
1165 DECODE(parent_transaction_type, 'RECEIVE', SIGN(quantity), 'RETURN TO VENDOR', SIGN(quantity)*-1)) quantity_multiplier
1166 FROM JAI_RCV_TRANSACTIONS
1167 WHERE transaction_id = cp_transaction_id
1168 AND (
1169 transaction_type IN ('RECEIVE', 'RETURN TO VENDOR')
1170 OR
1171 ( transaction_type = 'CORRECT'
1172 AND parent_transaction_type IN ('RECEIVE', 'RETURN TO VENDOR')
1173 )
1174 );*/
1175
1176 /* Bug 5243532. Added by Lakshmi Gopalsami
1177 * Removed cursor c_org_info and variable r_org_info
1178 * and implemented caching logic.
1179 */
1180 --cursor added by Ramananda for Bug#4530112
1181 CURSOR c_rcv_rgm_lines(cp_shipment_line_id rcv_shipment_lines.shipment_line_id%TYPE)
1182 IS
1183 SELECT *
1184 FROM jai_rcv_rgm_lines
1185 WHERE shipment_line_id = cp_shipment_line_id;
1186
1187 r_rcv_rgm_lines c_rcv_rgm_lines%ROWTYPE;
1188
1189 r_regime c_regime%ROWTYPE;
1190 --ln_total_cenvat_amount NUMBER;
1191 ln_tax_amount NUMBER;
1192 --ln_apportion_factor NUMBER;
1193
1194 lv_codepath JAI_RCV_TRANSACTIONS.codepath%TYPE;
1195 lv_setup VARCHAR2(10);
1196 r_vat_invoice c_vat_invoice%ROWTYPE;
1197 --r_txn cur_txn%ROWTYPE;
1198
1199 lv_simulate_flag VARCHAR2(1); --File.Sql.35 Cbabu
1200 ln_rtv_qty NUMBER;
1201 ln_correct_receive_qty NUMBER;
1202 ln_correct_rtv_qty NUMBER;
1203
1204 /* Bug 5243532. Added by Lakshmi Gopalsami
1205 * Defined variable for implementing caching logic.
1206 */
1207 l_func_curr_det jai_plsql_cache_pkg.func_curr_details;
1208 lv_organization_code org_organization_definitions.organization_code%TYPE;
1209 -- End for bug 5243532
1210
1211 BEGIN
1212
1213 /* File.Sql.35 by Brathod */
1214
1215 lv_account_nature := 'VAT INTERIM';
1216 lv_source_name := 'Purchasing India';
1217 lv_category_name := 'Receiving India';
1218 lv_reference_23 := 'jai_rgm_claim_pkg.process_vat';
1219 lv_reference_24 := 'rcv_transactions';
1220 lv_reference_25 := p_transaction_id;
1221 lv_reference_26 := 'transaction_id';
1222 lv_destination := 'G';
1223 ld_accounting_date := TRUNC(SYSDATE);
1224
1225 /* End of File.Sql.35 by Brathod */
1226
1227 ln_statement_id := 1;
1228
1229 lv_simulate_flag := jai_constants.no; --File.Sql.35 Cbabu
1230 p_process_status := jai_constants.successful;
1231 p_process_message := NULL;
1232
1233 lv_codepath := jai_general_pkg.plot_codepath(1, lv_codepath, 'rgm_claim_pkg.process_vat', 'START');
1234
1235 OPEN c_trx(p_transaction_id);
1236 FETCH c_trx INTO r_trx;
1237 CLOSE c_trx;
1238
1239 --Start for Bug#4950914
1240 --means transacrion has already completed
1241 IF r_trx.process_vat_status = jai_constants.successful THEN
1242 RETURN;
1243 END IF;
1244
1245 --this is just to check, if the transaction has already been processed by checking the records from
1246 --jai_rcv_rgm_claims for current transaction
1247 generate_schedule (
1248 p_shipment_header_id => r_trx.shipment_header_id,
1249 p_shipment_line_id => r_trx.shipment_line_id,
1250 p_transaction_id => r_trx.transaction_id,
1251 p_simulate_flag => 'Y',
1252 p_process_message => lv_process_message,
1253 p_process_status => lv_process_status,
1254 p_term_id => NULL,
1255 p_tax_id => NULL,
1256 p_override => NULL
1257 );
1258 IF lv_process_status = jai_constants.expected_error AND
1259 lv_process_message = 'Duplicate Records in jai_rcv_rgm_claims' THEN
1260 --This process message is being passed from the procedure generate_schedule
1261 --So shouldn't be changed OR should be changed at all the places in the in this package
1262 RETURN;
1263 END IF;
1264 --End for Bug#4950914
1265
1266 ln_statement_id := 2;
1267 lv_codepath := jai_general_pkg.plot_codepath(2, lv_codepath);
1268
1269 ln_location_id := r_trx.location_id;
1270
1271 ln_statement_id := 3;
1272 OPEN c_base_trx(p_transaction_id);
1273 FETCH c_base_trx INTO r_base_trx;
1274 CLOSE c_base_trx;
1275
1276 --Code Added by Ramananda for Bug #4530112 the following Scenario
1277 --VAT Claim is not done
1278 --Following Transactions are being done
1279 --RTV
1280 --CORRECT OF RECEIVE
1281 --CORRECT OF RTV
1282 OPEN c_rcv_rgm_lines(r_trx.shipment_line_id);
1283 FETCH c_rcv_rgm_lines INTO r_rcv_rgm_lines;
1284 CLOSE c_rcv_rgm_lines;
1285
1286 IF r_rcv_rgm_lines.invoice_no IS NULL AND (r_trx.transaction_type = 'RETURN TO VENDOR' OR (r_trx.transaction_type = 'CORRECT' AND r_trx.parent_transaction_type IN ('RECEIVE', 'MATCH', 'RETURN TO VENDOR') ) ) THEN
1287 --to Generate the Schedule for the Current Transaction
1288 generate_schedule (
1289 p_shipment_header_id => r_base_trx.shipment_header_id,
1290 p_shipment_line_id => r_base_trx.shipment_line_id,
1291 p_transaction_id => r_trx.transaction_id,
1292 p_process_message => lv_process_message,
1293 p_process_status => lv_process_status,
1294 p_override => jai_constants.no --File.Sql.35 Added by Ramananda for bug#4530112
1295 );
1296
1297 IF lv_process_status <> jai_constants.successful THEN
1298 p_process_status := lv_process_status;
1299 p_process_message := lv_process_message;
1300 GOTO EXIT_POINT;
1301 END IF;
1302
1303 IF r_trx.transaction_type = 'RETURN TO VENDOR' THEN
1304 ln_rtv_qty := r_base_trx.quantity;
1305 ELSIF r_trx.transaction_type = 'CORRECT' THEN
1306 IF r_trx.parent_transaction_type IN ('RECEIVE', 'MATCH') THEN
1307 ln_correct_receive_qty := r_base_trx.quantity;
1308 ELSIF r_trx.parent_transaction_type = 'RETURN TO VENDOR' THEN
1309 ln_correct_rtv_qty := r_base_trx.quantity;
1310 END IF;
1311 END IF;
1312
1313 --to Update the jai_rcv_rgm_lines table for the Schedule generated above
1314 update_rcv_lines(p_shipment_header_id => r_base_trx.shipment_header_id,
1315 p_shipment_line_id => r_base_trx.shipment_line_id,
1316 p_rtv_qty => ln_rtv_qty,
1317 p_correct_receive_qty => ln_correct_receive_qty,
1318 p_correct_rtv_qty => ln_correct_rtv_qty,
1319 p_process_message => lv_process_message,
1320 p_process_status => lv_process_status,
1321 /* Added the following by Ramananda for bug#4530112 */
1322 p_invoice_no => jai_rcv_rgm_claims_pkg.gv_invoice_no_dflt, --File.Sql.35
1323 p_invoice_date => jai_rcv_rgm_claims_pkg.gd_invoice_date_dflt --File.Sql.35
1324 );
1325
1326 IF lv_process_status <> jai_constants.successful THEN
1327 p_process_status := lv_process_status;
1328 p_process_message := lv_process_message;
1329 GOTO EXIT_POINT;
1330 END IF;
1331
1332 GOTO EXIT_POINT;
1333
1334 END IF;
1335 --End Added by Ramananda for Bug #4530112
1336
1337 OPEN c_regime(NULL, jai_constants.vat_regime);
1338 FETCH c_regime INTO r_regime;
1339 CLOSE c_regime;
1340 /* Bug 5243532. Added by Lakshmi Gopalsami
1341 * Removed the commented codes for cursr cur_txn
1342 * and cur_total_tax.
1343 * Removed cursor c_org_info and implemented caching logic.
1344 */
1345
1346 l_func_curr_det := jai_plsql_cache_pkg.return_sob_curr
1347 (p_org_id => r_trx.organization_id );
1348 lv_organization_code := l_func_curr_det.organization_code;
1349
1350 ln_statement_id := 5;
1351
1352 IF r_base_trx.transaction_type = 'CORRECT' THEN
1353 lv_reference_10 := 'India Local VAT Entries for Receipt:'||r_trx.receipt_num||'. Transaction Type CORRECT of '||r_trx.parent_transaction_type||' for the Organization code '||lv_organization_code;
1354 ELSE
1355 lv_reference_10 := 'India Local VAT Entries for Receipt:'||r_trx.receipt_num||'. Transaction Type '||r_trx.transaction_type||' for the Organization code '||lv_organization_code;
1356 END IF;
1357
1358 lv_currency_code := jai_constants.func_curr;
1359
1360 IF gv_debug THEN
1361 fnd_file.put_line(fnd_file.log, 'r_base_trx.transaction_type:'||r_base_trx.transaction_type);
1362 END IF;
1363
1364 ln_statement_id := 6;
1365 IF r_base_trx.transaction_type IN ('RECEIVE', 'MATCH') THEN
1366
1367 IF gv_debug THEN
1368 fnd_file.put_line(fnd_file.log, '1 RECEIVE: Before insert_rcv_lines:');
1369 END IF;
1370
1371 ln_statement_id := 7;
1372 --Populate the lines for quantity and Recoverable amount
1373 insert_rcv_lines(p_shipment_header_id => r_base_trx.shipment_header_id,
1374 p_shipment_line_id => r_base_trx.shipment_line_id,
1375 p_transaction_id => r_trx.transaction_id,
1376 p_regime_code => jai_constants.vat_regime,
1377 p_process_message => lv_process_message,
1378 p_process_status => lv_process_status,
1379 p_simulate_flag => lv_simulate_flag --File.Sql.35 Cbabu
1380 );
1381
1382 IF gv_debug THEN
1383 fnd_file.put_line(fnd_file.log, '1.1 RECEIVE: After insert_rcv_lines:');
1384 END IF;
1385
1386 IF lv_process_status <> jai_constants.successful THEN
1387 p_process_status := lv_process_status;
1388 p_process_message := lv_process_message;
1389 GOTO EXIT_POINT;
1390 END IF;
1391
1392 lv_codepath := jai_general_pkg.plot_codepath(3, lv_codepath);
1393
1394 ln_statement_id := 8;
1395 generate_schedule(
1396 p_shipment_header_id => r_base_trx.shipment_header_id,
1397 p_shipment_line_id => r_base_trx.shipment_line_id,
1398 p_transaction_id => r_trx.transaction_id,
1399 p_process_message => lv_process_message,
1400 p_process_status => lv_process_status,
1401 p_override => jai_constants.no --File.Sql.35 Cbabu
1402 );
1403
1404 IF lv_process_status <> jai_constants.successful THEN
1405 p_process_status := lv_process_status;
1406 p_process_message := lv_process_message;
1407 GOTO EXIT_POINT;
1408 END IF;
1409
1410 IF r_base_trx.source_document_code = 'RMA' then --.receipt_source_code = 'CUSTOMER' THEN
1411 do_rma_accounting(p_transaction_id => p_transaction_id,
1412 p_process_message => lv_process_message,
1413 p_process_status => lv_process_status
1414 );
1415
1416 IF lv_process_status <> jai_constants.successful THEN
1417 p_process_status := lv_process_status;
1418 p_process_message := lv_process_message;
1419 GOTO EXIT_POINT;
1420 END IF;
1421 END IF;
1422
1423 ELSIF r_base_trx.transaction_type = 'RETURN TO VENDOR' THEN
1424 ln_statement_id := 9;
1425 lv_accounting_type := 'REVERSAL';
1426
1427 IF gv_debug THEN
1428 fnd_file.put_line(fnd_file.log, 'Inside RTV:');
1429 END IF;
1430
1431 IF gv_debug THEN
1432 fnd_file.put_line(fnd_file.log, 'Before generate Schedule');
1433 END IF;
1434
1435 ln_statement_id := 10;
1436 generate_schedule (
1437 p_shipment_header_id => r_base_trx.shipment_header_id,
1438 p_shipment_line_id => r_base_trx.shipment_line_id,
1439 p_transaction_id => r_trx.transaction_id,
1440 p_process_message => lv_process_message,
1441 p_process_status => lv_process_status,
1442 p_override => jai_constants.no --File.Sql.35 Cbabu
1443 );
1444
1445 IF lv_process_status <> jai_constants.successful THEN
1446 p_process_status := lv_process_status;
1447 p_process_message := lv_process_message;
1448 GOTO EXIT_POINT;
1449 END IF;
1450
1451 IF gv_debug THEN
1452 fnd_file.put_line(fnd_file.log, 'After generate Schedule');
1453 END IF;
1454
1455 /* Vijay Shankar for Bug#4346453. RCV DFF Elim. Enh.
1456 IF gv_debug THEN
1457 fnd_file.put_line(fnd_file.log, 'r_base_trx.attribute_category'||r_base_trx.attribute_category);
1458 fnd_file.put_line(fnd_file.log, 'r_base_trx.attribute4'||r_base_trx.attribute4);
1459 END IF;
1460 */
1461
1462 ln_statement_id := 11;
1463 /* Vijay Shankar for Bug#4346453. RCV DFF Elim. Enh. IF r_base_trx.attribute_category = 'India Return to Vendor' AND r_base_trx.attribute4 = 'Y' THEN
1464 */
1465 --
1466 -- Bug# 6109941 , Removed commented code based on DFF logic
1467 --
1468
1469 ln_statement_id := 14;
1470 do_rtv_accounting( p_shipment_header_id => r_base_trx.shipment_header_id,
1471 p_shipment_line_id => r_base_trx.shipment_line_id,
1472 p_transaction_id => p_transaction_id,
1473 p_called_from => 'RETURN TO VENDOR',
1474 p_invoice_no => r_trx.vat_invoice_no, -- Vijay Shankar for Bug#4346453. RCV DFF Elim. Enh. lv_vat_invoice_no,
1475 p_invoice_date => r_trx.vat_invoice_date, --TRUNC(SYSDATE),
1476 p_process_status => lv_process_status,
1477 p_process_message => lv_process_message);
1478
1479 IF lv_process_status <> jai_constants.successful THEN
1480 p_process_status := lv_process_status;
1481 p_process_message := lv_process_message;
1482 GOTO EXIT_POINT;
1483 END IF;
1484
1485 ln_statement_id := 15;
1486
1487 --ln_total_cenvat_amount := ln_total_cenvat_amount * r_txn.quantity_multiplier * ln_apportion_factor;
1488
1489 ln_statement_id := 16;
1490 update_rcv_lines(p_shipment_header_id => r_base_trx.shipment_header_id,
1491 p_shipment_line_id => r_base_trx.shipment_line_id,
1492 p_rtv_qty => r_trx.quantity,
1493 --p_recoverable_amount => ln_total_cenvat_amount,
1494 p_process_message => lv_process_message,
1495 p_process_status => lv_process_status,
1496 p_invoice_no => jai_rcv_rgm_claims_pkg.gv_invoice_no_dflt, --File.Sql.35 Cbabu
1497 p_invoice_date => jai_rcv_rgm_claims_pkg.gd_invoice_date_dflt --File.Sql.35 Cbabu
1498 );
1499
1500 IF lv_process_status <> jai_constants.successful THEN
1501 p_process_status := lv_process_status;
1502 p_process_message := lv_process_message;
1503 GOTO EXIT_POINT;
1504 END IF;
1505
1506 IF gv_debug THEN
1507 fnd_file.put_line(fnd_file.log, 'After update_rcv_lines:');
1508 END IF;
1509 ln_statement_id := 17;
1510
1511 ELSIF r_base_trx.transaction_type = 'CORRECT' THEN
1512
1513 IF r_trx.parent_transaction_type = 'RECEIVE' THEN
1514 ln_statement_id := 18;
1515 lv_accounting_type := 'REGULAR';
1516
1517 IF gv_debug THEN
1518 fnd_file.put_line(fnd_file.log, 'r_trx.parent_transaction_type :'||r_trx.parent_transaction_type);
1519 END IF;
1520
1521 ln_statement_id := 19;
1522 generate_schedule (
1523 p_shipment_header_id => r_base_trx.shipment_header_id,
1524 p_shipment_line_id => r_base_trx.shipment_line_id,
1525 p_transaction_id => r_trx.transaction_id,
1526 p_process_message => lv_process_message,
1527 p_process_status => lv_process_status,
1528 p_override => jai_constants.no --File.Sql.35 Cbabu
1529 );
1530
1531 IF lv_process_status <> jai_constants.successful THEN
1532 p_process_status := lv_process_status;
1533 p_process_message := lv_process_message;
1534 GOTO EXIT_POINT;
1535 END IF;
1536
1537 ln_statement_id := 20;
1538 IF gv_debug THEN
1539 fnd_file.put_line(fnd_file.log, 'After Generate Schedule');
1540 END IF;
1541
1542 OPEN c_vat_invoice(r_trx.parent_transaction_id);
1543 FETCH c_vat_invoice INTO r_vat_invoice;
1544 CLOSE c_vat_invoice;
1545
1546 ln_statement_id := 21;
1547 UPDATE JAI_RCV_TRANSACTIONS
1548 SET vat_invoice_no = r_vat_invoice.vat_invoice_no,
1549 vat_invoice_date = r_vat_invoice.vat_invoice_date
1550 WHERE transaction_id = r_trx.transaction_id;
1551
1552 IF gv_debug THEN
1553 fnd_file.put_line(fnd_file.log, 'Before Process_Claim');
1554 END IF;
1555
1556 ln_statement_id := 22;
1557 --Call the Claim API with Shipment_line_id
1558 process_claim( p_regime_id => r_regime.regime_id,
1559 p_shipment_header_id => r_base_trx.shipment_header_id,
1560 p_shipment_line_id => r_base_trx.shipment_line_id,
1561 p_invoice_no => r_vat_invoice.vat_invoice_no,
1562 p_invoice_date => TRUNC(SYSDATE),
1563 p_called_from => 'CORRECT OF RECEIVE',
1564 p_process_message => lv_process_message,
1565 p_process_status => lv_process_status
1566 );
1567
1568 IF lv_process_status <> jai_constants.successful THEN
1569 p_process_status := lv_process_status;
1570 p_process_message := lv_process_message;
1571 GOTO EXIT_POINT;
1572 END IF;
1573
1574 IF r_base_trx.source_document_code = 'RMA' then --r_receipt_source_code.receipt_source_code = 'CUSTOMER' THEN
1575 do_rma_accounting(p_transaction_id => p_transaction_id,
1576 p_process_message => lv_process_message,
1577 p_process_status => lv_process_status
1578 );
1579
1580 IF lv_process_status <> jai_constants.successful THEN
1581 p_process_status := lv_process_status;
1582 p_process_message := lv_process_message;
1583 GOTO EXIT_POINT;
1584 END IF;
1585 END IF;
1586
1587 ln_statement_id := 23;
1588 IF gv_debug THEN
1589 fnd_file.put_line(fnd_file.log, 'After Process_claim');
1590 END IF;
1591
1592 --ln_total_cenvat_amount := ln_total_cenvat_amount * r_txn.quantity_multiplier * ln_apportion_factor;
1593
1594 --Update the lines
1595 update_rcv_lines(p_shipment_header_id => r_base_trx.shipment_header_id,
1596 p_shipment_line_id => r_base_trx.shipment_line_id,
1597 p_correct_receive_qty => r_base_trx.quantity,
1598 --p_recoverable_amount => ln_total_cenvat_amount,
1599 p_process_status => lv_process_status,
1600 p_process_message => lv_process_message,
1601 p_invoice_no => jai_rcv_rgm_claims_pkg.gv_invoice_no_dflt, --File.Sql.35 Cbabu
1602 p_invoice_date => jai_rcv_rgm_claims_pkg.gd_invoice_date_dflt --File.Sql.35 Cbabu
1603 );
1604
1605 IF lv_process_status <> jai_constants.successful THEN
1606 p_process_status := lv_process_status;
1607 p_process_message := lv_process_message;
1608 GOTO EXIT_POINT;
1609 END IF;
1610
1611 ln_statement_id := 24;
1612 IF gv_debug THEN
1613 fnd_file.put_line(fnd_file.log, 'After Update_rcv_lines');
1614 END IF;
1615
1616 ELSIF r_trx.parent_transaction_type = 'RETURN TO VENDOR' THEN
1617 lv_accounting_type := 'REVERSAL';
1618
1619 ln_statement_id := 25;
1620
1621 IF gv_debug THEN
1622 fnd_file.put_line(fnd_file.log, 'r_trx.parent_transaction_type :'||r_trx.parent_transaction_type);
1623 END IF;
1624
1625 generate_schedule (
1626 p_shipment_header_id => r_base_trx.shipment_header_id,
1627 p_shipment_line_id => r_base_trx.shipment_line_id,
1628 p_transaction_id => r_trx.transaction_id,
1629 p_process_message => lv_process_message,
1630 p_process_status => lv_process_status,
1631 p_override => jai_constants.no --File.Sql.35 Cbabu
1632 );
1633
1634 IF lv_process_status <> jai_constants.successful THEN
1635 p_process_status := lv_process_status;
1636 p_process_message := lv_process_message;
1637 GOTO EXIT_POINT;
1638 END IF;
1639
1640 ln_statement_id := 26;
1641
1642 IF gv_debug THEN
1643 fnd_file.put_line(fnd_file.log, 'After Generate Schedule');
1644 END IF;
1645
1646 --Get the VAT Invoice no from Parent
1647 OPEN c_trx(r_base_trx.parent_transaction_id);
1648 FETCH c_trx INTO r_trx;
1649 CLOSE c_trx;
1650
1651 ln_statement_id := 27;
1652
1653 --Update the VAT Invoice no in JAI_RCV_TRANSACTIONS
1654 UPDATE JAI_RCV_TRANSACTIONS
1655 SET vat_invoice_no = r_trx.vat_invoice_no,
1656 vat_invoice_date = TRUNC(SYSDATE)
1657 WHERE transaction_id = r_base_trx.transaction_id; --r_trx now points to the parent transaction
1658
1659 IF gv_debug THEN
1660 fnd_file.put_line(fnd_file.log, 'Before Process_Claim');
1661 END IF;
1662
1663 ln_statement_id := 28;
1664
1665 do_rtv_accounting( p_shipment_header_id => r_base_trx.shipment_header_id,
1666 p_shipment_line_id => r_base_trx.shipment_line_id,
1667 p_transaction_id => p_transaction_id,
1668 p_called_from => 'CORRECT OF RETURN TO VENDOR',
1669 p_invoice_no => r_trx.vat_invoice_no,
1670 p_invoice_date => TRUNC(SYSDATE),
1671 p_process_status => lv_process_status,
1672 p_process_message => lv_process_message);
1673
1674 IF lv_process_status <> jai_constants.successful THEN
1675 p_process_status := lv_process_status;
1676 p_process_message := lv_process_message;
1677 GOTO EXIT_POINT;
1678 END IF;
1679
1680 ln_statement_id := 29;
1681 /*
1682 --Call the Claim API with Shipment_line_id
1683 process_claim( p_regime_id => r_regime.regime_id,
1684 p_shipment_header_id => r_base_trx.shipment_header_id,
1685 p_shipment_line_id => r_base_trx.shipment_line_id,
1686 p_invoice_no => r_trx.vat_invoice_no,
1687 p_invoice_date => TRUNC(SYSDATE),
1688 p_called_from => 'CORRECT OF RETURN TO VENDOR',
1689 p_process_message => lv_process_message,
1690 p_process_status => lv_process_status
1691 );
1692
1693 IF lv_process_status <> jai_constants.successful THEN
1694 p_process_status := lv_process_status;
1695 p_process_message := lv_process_message;
1696 GOTO EXIT_POINT;
1697 END IF;
1698
1699 IF gv_debug THEN
1700 fnd_file.put_line(fnd_file.log, 'After Process_claim');
1701 END IF;
1702 */
1703
1704 --ln_total_cenvat_amount := ln_total_cenvat_amount * r_txn.quantity_multiplier * ln_apportion_factor;
1705
1706 ln_statement_id := 30;
1707 --Update the lines
1708 update_rcv_lines(p_shipment_header_id => r_base_trx.shipment_header_id,
1709 p_shipment_line_id => r_base_trx.shipment_line_id,
1710 p_correct_rtv_qty => r_base_trx.quantity,
1711 p_process_message => lv_process_message,
1712 p_process_status => lv_process_status,
1713 p_invoice_no => jai_rcv_rgm_claims_pkg.gv_invoice_no_dflt, --File.Sql.35 Cbabu
1714 p_invoice_date => jai_rcv_rgm_claims_pkg.gd_invoice_date_dflt --File.Sql.35 Cbabu
1715 );
1716
1717 IF lv_process_status <> jai_constants.successful THEN
1718 p_process_status := lv_process_status;
1719 p_process_message := lv_process_message;
1720 GOTO EXIT_POINT;
1721 END IF;
1722
1723 IF gv_debug THEN
1724 fnd_file.put_line(fnd_file.log, 'After Update_rcv_lines');
1725 END IF;
1726
1727 END IF;
1728 END IF;
1729 <<EXIT_POINT>>
1730 UPDATE JAI_RCV_TRANSACTIONS
1731 SET PROCESS_VAT_STATUS = SUBSTR(p_process_status,1,2),
1732 process_vat_message = SUBSTR(p_process_message,1,1000)
1733 WHERE transaction_id = p_transaction_id;
1734
1735 EXCEPTION
1736 WHEN OTHERS THEN
1737 p_process_status := jai_constants.unexpected_error;
1738 p_process_message := 'Stmt :'||ln_statement_id||' '||SUBSTR(SQLERRM,1,200);
1739 fnd_file.put_line(fnd_file.log, 'PROCESS_VAT ERROR:'||p_process_message);
1740
1741 END process_vat;
1742
1743 PROCEDURE process_claim(
1744 p_regime_id IN JAI_RGM_DEFINITIONS.regime_id%TYPE,
1745 p_regime_regno IN VARCHAR2 DEFAULT NULL,
1746 p_organization_id IN hr_all_organization_units.organization_id%TYPE DEFAULT NULL,
1747 p_location_id IN hr_locations_all.location_id%TYPE DEFAULT NULL,
1748 p_shipment_header_id IN rcv_shipment_headers.shipment_header_id%TYPE DEFAULT NULL,
1749 p_shipment_line_id IN rcv_shipment_lines.shipment_line_id%TYPE DEFAULT NULL,
1750 p_batch_id IN JAI_RCV_RGM_LINES.BATCH_NUM%TYPE DEFAULT NULL,
1751 p_force IN VARCHAR2 DEFAULT NULL,
1752 p_invoice_no IN JAI_RCV_TRANSACTIONS.vat_invoice_no%TYPE,
1753 p_invoice_date IN JAI_RCV_TRANSACTIONS.vat_invoice_date%TYPE,
1754 p_called_from IN VARCHAR2,
1755 p_process_status OUT NOCOPY VARCHAR2,
1756 p_process_message OUT NOCOPY VARCHAR2)
1757 IS
1758 CURSOR cur_claims(cp_regime_id IN VARCHAR2,
1759 cp_regime_regno IN VARCHAR2,
1760 cp_organization_id IN NUMBER,
1761 cp_location_id IN NUMBER,
1762 cp_shipment_header_id IN VARCHAR2,
1763 cp_shipment_line_id IN NUMBER,
1764 cp_batch_id IN NUMBER,
1765 cp_force IN VARCHAR2)
1766 IS
1767 SELECT b.organization_id,
1768 b.location_id,
1769 b.shipment_header_id,
1770 b.shipment_line_id,
1771 b.scheduled_date,
1772 b.transaction_id,
1773 b.tax_type,
1774 b.installment_amount,
1775 b.installment_no,
1776 b.invoice_no,
1777 b.invoice_date,
1778 b.receipt_num,
1779 b.rcv_rgm_line_id
1780 FROM JAI_RGM_ORG_REGNS_V a,
1781 jai_rcv_rgm_txns_v b,
1782 JAI_RGM_DEFINITIONS c
1783 WHERE a.regime_code = c.regime_code
1784 AND c.regime_id = cp_regime_id
1785 AND a.attribute_value = NVL(cp_regime_regno, a.attribute_value)
1786 AND a.attribute_type_code = jai_constants.rgm_attr_type_code_primary --'PRIMARY'
1787 AND a.attribute_code = jai_constants.attr_code_regn_no --'REGISTRATION_NO'
1788 AND a.organization_id = NVL(cp_organization_id, a.organization_id)
1789 AND a.location_id = NVL(cp_location_id, a.location_id)
1790 AND b.shipment_header_id = NVL(cp_shipment_header_id,b.shipment_header_id)
1791 AND b.shipment_line_id = NVL(cp_shipment_line_id, b.shipment_line_id)
1792 AND ( NVL(cp_batch_id,0) = 0
1793 OR (NVL(cp_batch_id,0) <> 0 AND b.batch_num = cp_batch_id)
1794 )
1795 AND a.organization_id = b.organization_id
1796 AND a.location_id = b.location_id
1797 AND b.scheduled_date <= DECODE(cp_force, 'Y', b.scheduled_date, SYSDATE)
1798 AND b.invoice_no IS NOT NULL
1799 AND b.process_status_flag NOT IN ('Z') /* 'Z' meaning the line is marked for UNCLAIM, but not yet processed*/
1800 AND NVL(b.installment_amount,0) <> 0;
1801
1802 CURSOR c_regime (cp_regime_id IN JAI_RGM_DEFINITIONS.regime_id%TYPE DEFAULT NULL,
1803 cp_regime_code IN JAI_RGM_DEFINITIONS.regime_code%TYPE DEFAULT NULL)
1804 IS
1805 SELECT *
1806 FROM JAI_RGM_DEFINITIONS
1807 WHERE regime_id = NVL(cp_regime_id, regime_id)
1808 AND regime_code = NVL(cp_regime_code, regime_code);
1809
1810 CURSOR c_claim_schedule( cp_shipment_header_id IN rcv_shipment_headers.shipment_header_id%TYPE,
1811 cp_shipment_line_id IN rcv_shipment_lines.shipment_line_id%TYPE,
1812 cp_tax_type IN JAI_CMN_TAXES_ALL.tax_type%TYPE,
1813 cp_installment_no IN jai_rcv_rgm_claims.installment_no%TYPE)
1814 IS
1815 SELECT MIN(claim_schedule_id) claim_schedule_id
1816 FROM jai_rcv_rgm_claims
1817 WHERE shipment_header_id = cp_shipment_header_id
1818 AND shipment_line_id = cp_shipment_line_id
1819 AND tax_type = cp_tax_type
1820 AND installment_no = cp_installment_no
1821 AND nvl(status,'N') = 'N';
1822
1823 CURSOR c_vat_invoice(cp_shipment_header_id IN NUMBER,
1824 cp_shipment_line_id IN NUMBER)
1825 IS
1826 SELECT a.vat_invoice_no, a.vat_invoice_date, b.transaction_id, a.tax_transaction_id,
1827 a.excise_invoice_no , a.excise_invoice_Date /*bduvarag Bug5899383*/
1828 FROM JAI_RCV_TRANSACTIONS a,
1829 JAI_RCV_LINES b
1830 WHERE a.transaction_id = b.transaction_id
1831 AND b.shipment_header_id = cp_shipment_header_id
1832 AND b.shipment_line_id = cp_shipment_line_id;
1833
1834 lv_currency_code VARCHAR2(10);
1835 r_regime c_regime%ROWTYPE;
1836
1837 lv_accounting_type VARCHAR2(100);
1838 lv_account_nature VARCHAR2(100); -- := 'VAT CLAIM';
1839 lv_source_name VARCHAR2(100); -- := 'Purchasing India';
1840 lv_category_name VARCHAR2(100); -- := 'Receiving India';
1841 ld_accounting_date DATE ; -- := TRUNC(SYSDATE);
1842 lv_reference_10 gl_interface.reference10%TYPE;
1843 lv_reference_23 gl_interface.reference23%TYPE ; -- := 'jai_rgm_claim_pkg.process_claim';
1844 lv_reference_24 gl_interface.reference24%TYPE ; -- := 'jai_rgm_trx_records';
1845 lv_reference_26 gl_interface.reference26%TYPE ; -- := 'repository_id';
1846 lv_destination VARCHAR2(10) ; -- := 'G';
1847
1848 ln_code_combination_id NUMBER;
1849 ln_entered_dr NUMBER;
1850 ln_entered_cr NUMBER;
1851 lv_reference_25 gl_interface.reference25%TYPE;
1852 lv_code_path JAI_RCV_TRANSACTIONS.codepath%TYPE;
1853 lv_process_status VARCHAR2(2);
1854 lv_process_message VARCHAR2(1000);
1855 ln_repository_id jai_rgm_trx_records.repository_id%TYPE;
1856 r_claim_schedule c_claim_schedule%ROWTYPE;
1857 lv_codepath JAI_RCV_TRANSACTIONS.codepath%TYPE;
1858 ln_interim_recovery_account NUMBER;
1859 r_vat_invoice c_vat_invoice%ROWTYPE;
1860 lv_source_trx_type VARCHAR2(50);
1861 lv_account_name VARCHAR2(50);
1862 lv_invoice_no JAI_RCV_RGM_LINES.invoice_no%TYPE;
1863 ld_invoice_date JAI_RCV_RGM_LINES.invoice_date%TYPE;
1864 /*bduvarag Bug 5899383 Start*/
1865 lv_default_invoice_setup VARCHAR2(100);
1866 ld_excise_invoice_Date DATE;
1867 /*bduvarag Bug 5899383 End*/
1868 BEGIN
1869
1870 p_process_status := jai_constants.successful;
1871 p_process_message := NULL;
1872
1873 /* File.Sql.35 by Brathod */
1874 lv_account_nature := 'VAT CLAIM';
1875 lv_source_name := 'Purchasing India';
1876 lv_category_name := 'Receiving India';
1877 ld_accounting_date := TRUNC(SYSDATE);
1878 lv_reference_23 := 'jai_rgm_claim_pkg.process_claim';
1879 lv_reference_24 := 'jai_rgm_trx_records';
1880 lv_reference_26 := 'repository_id';
1881 lv_destination := 'G';
1882 /* End of File.Sql.35 by Brathod */
1883
1884
1885 IF gv_debug THEN
1886 fnd_file.put_line(fnd_file.log, 'Inside Claim Process');
1887 END IF;
1888
1889 OPEN c_regime(p_regime_id, NULL);
1890 FETCH c_regime INTO r_regime;
1891 CLOSE c_regime;
1892
1893 IF p_called_from IN ('CONCURRENT', 'CORRECT OF RECEIVE') THEN
1894 lv_accounting_type := 'REGULAR';
1895 ELSIF p_called_from IN ('RETURN TO VENDOR', 'CORRECT OF RETURN TO VENDOR') THEN
1896 lv_accounting_type := 'REVERSAL';
1897 END IF;
1898
1899 FOR rec_claims IN cur_claims( p_regime_id,
1900 p_regime_regno,
1901 p_organization_id,
1902 p_location_id,
1903 p_shipment_header_id,
1904 p_shipment_line_id,
1905 p_batch_id,
1906 p_force)
1907 LOOP
1908
1909 IF gv_debug THEN
1910 fnd_file.put_line(fnd_file.log, 'Inside Loop');
1911 fnd_file.put_line(fnd_file.log, 'Installment Amount : '||rec_claims.installment_amount);
1912 fnd_file.put_line(fnd_file.log, 'receipt_num : '||rec_claims.receipt_num);
1913
1914 END IF;
1915
1916 lv_reference_10 := 'India Local VAT Claim Entries For Receipt:'||rec_claims.receipt_num||' Transaction Type CLAIM for installment no '||rec_claims.installment_no;
1917
1918 OPEN c_vat_invoice(rec_claims.shipment_header_id, rec_claims.shipment_line_id);
1919 FETCH c_vat_invoice INTO r_vat_invoice;
1920 CLOSE c_vat_invoice;
1921
1922 IF r_vat_invoice.vat_invoice_no IS NULL OR r_vat_invoice.vat_invoice_date IS NULL THEN
1923
1924 UPDATE JAI_RCV_TRANSACTIONS
1925 SET vat_invoice_no = rec_claims.invoice_no,
1926 vat_invoice_date = rec_claims.invoice_date
1927 WHERE transaction_id = r_vat_invoice.transaction_id;
1928 END IF;
1929 /*bduvarag Bug 5899383 start*/
1930 lv_default_invoice_setup := jai_cmn_rgm_recording_pkg.get_rgm_attribute_value(
1931 pv_regime_code => jai_constants.vat_regime,
1932 pv_organization_type => jai_constants.orgn_type_io,
1933 pn_organization_id => rec_claims.organization_id,
1934 pn_location_id => rec_claims.location_id,
1935 pv_registration_type => jai_constants.regn_type_others, --'OTHERS',
1936 pv_attribute_type_code => NULL,
1937 pv_attribute_code => 'DEFAULT_INVOICE_DETAILS' ); --'DEFAULT_INVOICE_DETAILS');
1938 If upper(lv_default_invoice_setup) in ( 'Y', 'YES') then
1939
1940 /* Means - We can use the excise invoice number as vat invoice number and excise invoice date as vat invoice date
1941 || and viceversa Need to check whether the ja_in_rcv_transactions has the excise_invoice_no and excise_invoice_Date stamped
1942 for the shipment line id .
1943 */
1944
1945 IF rec_claims.invoice_no is not NULL and r_vat_invoice.excise_invoice_no is null THEN
1946
1947 --
1948 -- Bug 6109941, Removed update statement of dff attribute columns of
1949 -- RCV_SHIPMENT_HEADERS and RCV_TRASACTIONS tables
1950 --
1951
1952 UPDATE JAI_RCV_LINES
1953 SET excise_invoice_no = rec_claims.invoice_no,
1954 excise_invoice_Date = rec_claims.invoice_date
1955 WHERE shipment_header_id = rec_claims.shipment_header_id
1956 AND excise_invoice_no IS NULL;
1957
1958
1959 UPDATE jai_rcv_transactions
1960 SET excise_invoice_no = rec_claims.invoice_no,
1961 excise_invoice_Date = rec_claims.invoice_date
1962 WHERE transaction_id = r_vat_invoice.transaction_id
1963 AND excise_invoice_no IS NULL;
1964
1965 /*
1966 || End additions by ssumaith for defaulting the vat invoice number as excise invoice number - Bug# 5899383
1967 */
1968
1969
1970 END IF;
1971 END IF;
1972 /*bduvarag Bug 5899383 End*/
1973 --DO the Register_entry
1974 OPEN c_claim_schedule(rec_claims.shipment_header_id,
1975 rec_claims.shipment_line_id,
1976 rec_claims.tax_type,
1977 rec_claims.installment_no);
1978 FETCH c_claim_schedule INTO r_claim_schedule;
1979 CLOSE c_claim_schedule;
1980
1981 IF gv_debug THEN
1982 fnd_file.put_line(fnd_file.log, 'Before Start of Accounting');
1983 END IF;
1984
1985 --Accounting
1986 lv_currency_code := jai_constants.func_curr;
1987
1988 --for Balancing Accountid for register entry
1989 ln_interim_recovery_account :=
1990 jai_cmn_rgm_recording_pkg.get_account(
1991 p_regime_id => r_regime.regime_id,
1992 p_organization_type => jai_constants.orgn_type_io,
1993 p_organization_id => rec_claims.organization_id,
1994 p_location_id => rec_claims.location_id,
1995 p_tax_type => rec_claims.tax_type,
1996 p_account_name => jai_constants.recovery_interim);
1997
1998 IF ln_interim_recovery_account IS NULL THEN
1999 p_process_status := jai_constants.expected_error;
2000 p_process_message := 'Interim recovery Account not defined in VAT Setup';
2001 RETURN;
2002 END IF;
2003
2004 ln_code_combination_id :=
2005 jai_cmn_rgm_recording_pkg.get_account(
2006 p_regime_id => r_regime.regime_id,
2007 p_organization_type => jai_constants.orgn_type_io,
2008 p_organization_id => rec_claims.organization_id,
2009 p_location_id => rec_claims.location_id,
2010 p_tax_type => rec_claims.tax_type,
2011 p_account_name => jai_constants.recovery);
2012
2013 IF ln_code_combination_id IS NULL THEN
2014 p_process_status := jai_constants.expected_error;
2015 p_process_message := 'Recovery Account not defined in VAT Setup';
2016 RETURN;
2017 END IF;
2018
2019 ln_entered_dr := NULL;
2020 ln_entered_cr := rec_claims.installment_amount;
2021
2022 IF ln_entered_cr < 0 THEN
2023 ln_entered_dr := ln_entered_cr*-1;
2024 ln_entered_cr := NULL;
2025 END IF;
2026
2027 lv_account_name := jai_constants.recovery;
2028 /*
2029 IF p_called_from = 'RETURN TO VENDOR' THEN
2030 lv_source_trx_type := 'VAT CLAIM for RTV';
2031 ELSIF p_called_from = 'CORRECT OF RETURN TO VENDOR' THEN
2032 lv_source_trx_type := 'VAT CLAIM for CORRECT OF RTV';
2033 ELSIF p_called_from = 'CORRECT OF RECEIVE' THEN
2034 lv_source_trx_type := 'VAT CLAIM for CORRECT OF RCV';
2035 ELSE
2036 lv_source_trx_type := 'VAT CLAIM';
2037 END IF;
2038 */
2039 IF p_called_from = 'RETURN TO VENDOR' THEN
2040 lv_source_trx_type := RTV;
2041 ELSIF p_called_from = 'CORRECT OF RETURN TO VENDOR' THEN
2042 lv_source_trx_type := CORRECT_RTV;
2043 ELSIF p_called_from = 'CORRECT OF RECEIVE' THEN
2044 lv_source_trx_type := CORRECT_RECEIVE;
2045 ELSE
2046 lv_source_trx_type := RECEIVING;
2047 END IF;
2048
2049 IF p_called_from = 'CONCURRENT' OR p_invoice_no IS NULL THEN
2050 lv_invoice_no := rec_claims.invoice_no;
2051 ld_invoice_date := rec_claims.invoice_date;
2052 ELSIF p_invoice_no IS NOT NULL THEN
2053 lv_invoice_no := p_invoice_no;
2054 ld_invoice_date := p_invoice_date;
2055 END IF;
2056
2057 IF gv_debug THEN
2058 fnd_file.put_line(fnd_file.log, 'Before Passing the Repository Entry');
2059 END IF;
2060
2061 IF NVL(ln_entered_cr,0) <> 0 OR NVL(ln_entered_dr,0) <> 0 THEN --Added for Bug#4950914
2062 jai_cmn_rgm_recording_pkg.insert_vat_repository_entry(
2063 pn_repository_id => ln_repository_id,
2064 pn_regime_id => r_regime.regime_id,
2065 pv_tax_type => rec_claims.tax_type,
2066 pv_organization_type => jai_constants.orgn_type_io,
2067 pn_organization_id => rec_claims.organization_id,
2068 pn_location_id => rec_claims.location_id,
2069 pv_source => jai_constants.source_rcv,
2070 pv_source_trx_type => lv_source_trx_type,
2071 pv_source_table_name => TABLE_RCV_TRANSACTIONS, /* 'JAI_RCV_RGM_CLAIMS', Vijay */
2072 pn_source_id => nvl(r_vat_invoice.tax_transaction_id, r_vat_invoice.transaction_id), /* r_claim_schedule.claim_schedule_id, Vijay */
2073 pd_transaction_date => trunc(sysdate),
2074 pv_account_name => lv_account_name,
2075 pn_charge_account_id => ln_code_combination_id,
2076 pn_balancing_account_id => ln_interim_recovery_account,
2077 pn_credit_amount => ln_entered_cr,
2078 pn_debit_amount => ln_entered_dr,
2079 pn_assessable_value => NULL,
2080 pn_tax_rate => NULL,
2081 pn_reference_id => r_claim_schedule.claim_schedule_id,
2082 pn_batch_id => NULL,
2083 pn_inv_organization_id => rec_claims.organization_id,
2084 pv_invoice_no => lv_invoice_no,
2085 pd_invoice_date => ld_invoice_date,
2086 pv_called_from => 'JAI_RGM_CLAIM_PKG.PROCESS_CLAIM',
2087 pv_process_flag => lv_process_status,
2088 pv_process_message => lv_process_message,
2089 pv_attribute_context => NULL,
2090 pv_attribute1 => NULL,
2091 pv_attribute2 => NULL,
2092 pv_attribute3 => NULL,
2093 pv_attribute4 => NULL,
2094 pv_attribute5 => NULL);
2095
2096 IF gv_debug THEN
2097 fnd_file.put_line(fnd_file.log, 'lv_process_status'||lv_process_status);
2098 fnd_file.put_line(fnd_file.log, 'lv_process_message'||lv_process_message);
2099 END IF;
2100
2101
2102 IF lv_process_status <> jai_constants.successful THEN
2103 p_process_status := lv_process_status;
2104 p_process_message := lv_process_message;
2105 RETURN;
2106 END IF;
2107 END IF;
2108
2109 IF gv_debug THEN
2110 fnd_file.put_line(fnd_file.log, 'After Passing the Repository Entry');
2111 END IF;
2112
2113 -- Dr VAT Recovery
2114 ln_entered_dr := rec_claims.installment_amount;
2115 ln_entered_cr := NULL;
2116
2117 IF NVL(rec_claims.installment_amount,0) <> 0 THEN
2118 jai_rcv_accounting_pkg.process_transaction(
2119 p_transaction_id => rec_claims.transaction_id,
2120 p_acct_type => lv_accounting_type,
2121 p_acct_nature => lv_account_nature,
2122 p_source_name => lv_source_name,
2123 p_category_name => lv_category_name,
2124 p_code_combination_id => ln_code_combination_id,
2125 p_entered_dr => ln_entered_dr,
2126 p_entered_cr => ln_entered_cr,
2127 p_currency_code => lv_currency_code,
2128 p_accounting_date => ld_accounting_date,
2129 p_reference_10 => lv_reference_10,
2130 p_reference_23 => lv_reference_23,
2131 p_reference_24 => lv_reference_24,
2132 p_reference_25 => ln_repository_id,
2133 p_reference_26 => lv_reference_26,
2134 p_destination => lv_destination,
2135 p_simulate_flag => 'N',
2136 p_codepath => lv_code_path,
2137 p_process_message => lv_process_message,
2138 p_process_status => lv_process_status,
2139 p_reference_name => 'JAI_RCV_RGM_CLAIMS',
2140 p_reference_id => r_claim_schedule.claim_schedule_id);
2141
2142 IF lv_process_status <> jai_constants.successful THEN
2143 p_process_status := lv_process_status;
2144 p_process_message := lv_process_message;
2145 RETURN;
2146 END IF;
2147 END IF;
2148
2149 /*
2150 -- Cr Vat Interim
2151 ln_code_combination_id :=
2152 jai_cmn_rgm_recording_pkg.get_account(
2153 p_regime_id => r_regime.regime_id,
2154 p_organization_type => jai_constants.orgn_type_io,
2155 p_organization_id => rec_claims.organization_id,
2156 p_location_id => rec_claims.location_id,
2157 p_tax_type => rec_claims.tax_type,
2158 p_account_name => jai_constants.recovery_interim);
2159
2160 IF ln_interim_recovery_account IS NULL THEN
2161 p_process_status := jai_constants.expected_error;
2162 p_process_message := 'Interim recovery Account not defined in VAT Setup';
2163 RETURN;
2164 END IF;
2165 */
2166
2167 ln_entered_dr := NULL;
2168 ln_entered_cr := rec_claims.installment_amount;
2169
2170 IF NVL(rec_claims.installment_amount,0) <> 0 THEN
2171 jai_rcv_accounting_pkg.process_transaction(
2172 p_transaction_id => rec_claims.transaction_id,
2173 p_acct_type => lv_accounting_type,
2174 p_acct_nature => lv_account_nature,
2175 p_source_name => lv_source_name,
2176 p_category_name => lv_category_name,
2177 p_code_combination_id => ln_interim_recovery_account,
2178 p_entered_dr => ln_entered_dr,
2179 p_entered_cr => ln_entered_cr,
2180 p_currency_code => lv_currency_code,
2181 p_accounting_date => ld_accounting_date,
2182 p_reference_10 => lv_reference_10,
2183 p_reference_23 => lv_reference_23,
2184 p_reference_24 => lv_reference_24,
2185 p_reference_25 => ln_repository_id,
2186 p_reference_26 => lv_reference_26,
2187 p_destination => lv_destination,
2188 p_simulate_flag => 'N',
2189 p_codepath => lv_code_path,
2190 p_process_message => lv_process_message,
2191 p_process_status => lv_process_status,
2192 p_reference_name => 'JAI_RCV_RGM_CLAIMS',
2193 p_reference_id => r_claim_schedule.claim_schedule_id);
2194
2195 IF lv_process_status <> jai_constants.successful THEN
2196 p_process_status := lv_process_status;
2197 p_process_message := lv_process_message;
2198 RETURN;
2199 END IF;
2200 END IF;
2201
2202 IF gv_debug THEN
2203 fnd_file.put_line(fnd_file.log, 'After Passing the Accounting Entry');
2204 END IF;
2205
2206 UPDATE jai_rcv_rgm_claims
2207 SET status = 'Y',
2208 claimed_amount = installment_amount,
2209 claimed_date = TRUNC(SYSDATE)
2210 WHERE shipment_header_id = rec_claims.shipment_header_id
2211 AND shipment_line_id = rec_claims.shipment_line_id
2212 AND tax_type = rec_claims.tax_type
2213 AND installment_no = rec_claims.installment_no
2214 AND status <> 'Y';
2215
2216 --Update lines for recovered amount
2217 update_rcv_lines(p_shipment_header_id => rec_claims.shipment_header_id,
2218 p_shipment_line_id => rec_claims.shipment_line_id,
2219 p_recovered_amount => rec_claims.installment_amount,
2220 p_process_message => lv_process_message,
2221 p_process_status => lv_process_status,
2222 p_invoice_no => jai_rcv_rgm_claims_pkg.gv_invoice_no_dflt, --File.Sql.35 Cbabu
2223 p_invoice_date => jai_rcv_rgm_claims_pkg.gd_invoice_date_dflt --File.Sql.35 Cbabu
2224 );
2225
2226 IF lv_process_status <> jai_constants.successful THEN
2227 p_process_status := lv_process_status;
2228 p_process_message := lv_process_message;
2229 RETURN;
2230 END IF;
2231
2232
2233 --Update the claims table with status, claimed_amount and claim date
2234 END LOOP;
2235
2236 EXCEPTION
2237 WHEN OTHERS THEN
2238 p_process_status := jai_constants.unexpected_error;
2239 p_process_message := SUBSTR('jai_rcv_rgm_claims_pkg.process_claim Error:'||SQLERRM,1,200);
2240 END process_claim;
2241
2242 /*PROCEDURE process_no_claim(
2243 p_shipment_header_id IN rcv_shipment_headers.shipment_header_id%TYPE DEFAULT NULL,
2244 p_shipment_line_id IN rcv_shipment_lines.shipment_line_id%TYPE DEFAULT NULL,
2245 p_batch_id IN jai_rcv_rgm_lines.batch_id%TYPE DEFAULT NULL,
2246 p_process_status OUT NOCOPY VARCHAR2,
2247 p_process_message OUT NOCOPY VARCHAR2)*/
2248 --commented the above for Bug#4950914
2249 PROCEDURE process_no_claim(
2250 p_shipment_header_id IN rcv_shipment_headers.shipment_header_id%TYPE DEFAULT NULL,
2251 p_shipment_line_id IN rcv_shipment_lines.shipment_line_id%TYPE DEFAULT NULL,
2252 p_batch_id IN jai_rcv_rgm_lines.batch_num%TYPE DEFAULT NULL,
2253 p_regime_regno IN VARCHAR2 DEFAULT NULL,
2254 p_organization_id IN hr_all_organization_units.organization_id%TYPE DEFAULT NULL,
2255 p_location_id IN hr_locations_all.location_id%TYPE DEFAULT NULL,
2256 p_process_status OUT NOCOPY VARCHAR2,
2257 p_process_message OUT NOCOPY VARCHAR2,
2258 p_regime_id IN JAI_RGM_DEFINITIONS.regime_id%TYPE DEFAULT NULL )
2259 IS
2260 /*CURSOR c_shipment_lines(cp_shipment_header_id IN rcv_shipment_headers.shipment_header_id%TYPE,
2261 cp_shipment_line_id IN rcv_shipment_lines.shipment_line_id%TYPE,
2262 cp_batch_id IN jai_rcv_rgm_lines.batch_id%TYPE)
2263 IS
2264 SELECT shipment_header_id,
2265 shipment_line_id,
2266 receipt_num,
2267 transaction_id
2268 FROM jai_rcv_rgm_lines
2269 WHERE (( NVL(cp_batch_id,0) = 0
2270 AND shipment_header_id = NVL(cp_shipment_header_id, shipment_header_id)
2271 AND shipment_line_id = NVL(cp_shipment_line_id, shipment_line_id))
2272 OR ( NVL(cp_batch_id,0) <> 0
2273 AND batch_id = cp_batch_id))
2274 AND process_status_flag = 'Z'
2275 ORDER BY transaction_id;*/
2276
2277 --commented the above and added the below for Bug#4950914
2278
2279 CURSOR c_shipment_lines(cp_regime_id IN VARCHAR2,
2280 cp_regime_regno IN VARCHAR2,
2281 cp_organization_id IN NUMBER,
2282 cp_location_id IN NUMBER,
2283 cp_shipment_header_id IN VARCHAR2,
2284 cp_shipment_line_id IN NUMBER,
2285 cp_batch_id IN NUMBER)
2286 IS
2287 SELECT b.organization_id,
2288 b.location_id,
2289 b.shipment_header_id,
2290 b.shipment_line_id,
2291 b.transaction_id,
2292 b.receipt_num,
2293 b.rcv_rgm_line_id
2294 FROM JAI_RGM_ORG_REGNS_V a,
2295 jai_rcv_rgm_lines b,
2296 JAI_RGM_DEFINITIONS c
2297 WHERE a.regime_code = c.regime_code
2298 AND c.regime_id = cp_regime_id
2299 AND a.attribute_value = NVL(cp_regime_regno, a.attribute_value)
2300 AND a.attribute_type_code = jai_constants.rgm_attr_type_code_primary --'PRIMARY'
2301 AND a.attribute_code = jai_constants.attr_code_regn_no --'REGISTRATION_NO'
2302 AND a.organization_id = NVL(cp_organization_id, a.organization_id)
2303 AND a.location_id = NVL(cp_location_id, a.location_id)
2304 AND b.shipment_header_id = NVL(cp_shipment_header_id,b.shipment_header_id)
2305 AND b.shipment_line_id = NVL(cp_shipment_line_id, b.shipment_line_id)
2306 AND ( NVL(cp_batch_id,0) = 0
2307 OR (NVL(cp_batch_id,0) <> 0 AND b.BATCH_NUM = cp_batch_id)
2308 )
2309 AND a.organization_id = b.organization_id
2310 AND a.location_id = b.location_id
2311 AND b.process_status_flag = 'Z'; /* 'Z' meaning the line is marked for UNCLAIM, but not yet processed*/
2312
2313 CURSOR cur_tax(cp_transaction_id IN JAI_RCV_LINE_TAXES.transaction_id%TYPE,
2314 cp_currency_conversion_rate IN JAI_RCV_TRANSACTIONS.currency_conversion_rate%TYPE)
2315 IS
2316 SELECT jtl.tax_type,
2317 /*Added by Nitin for Bug # 6681800 */ SUM(DECODE(jtl.currency, jai_constants.func_curr, jtl.tax_amount*(rtl.mod_cr_percentage/100), jtl.tax_amount*(rtl.mod_cr_percentage/100)*cp_currency_conversion_rate)) tax_amount
2318 /*Commented by Nitin for bug :6681800 SUM(DECODE(currency, jai_constants.func_curr, tax_amount, tax_amount*cp_currency_conversion_rate)) tax_amount*/
2319 FROM JAI_RCV_LINE_TAXES jtl ,JAI_CMN_TAXES_ALL rtl /* Need to have join with JAI_CMN_TAXES_ALL*/
2320 WHERE jtl.transaction_id = cp_transaction_id
2321 AND jtl.tax_type in (select tax_type
2322 from jai_regime_tax_types_v
2323 where regime_code = jai_constants.vat_regime)
2324 AND NVL(jtl.modvat_flag,'N') = 'Y'
2325 AND jtl.tax_id = rtl.tax_id -- Bug 7454592. Added by Lakshmi Gopalsami
2326 GROUP BY jtl.tax_type;
2327
2328 CURSOR cur_total_tax(cp_transaction_id IN JAI_RCV_LINE_TAXES.transaction_id%TYPE,
2329 cp_currency_conversion_rate IN JAI_RCV_TRANSACTIONS.currency_conversion_rate%TYPE)
2330 IS
2331 SELECT SUM(ROUND(DECODE(a.currency, jai_constants.func_curr, /*Added by Nitin for bug # 6681800*/a.tax_amount*(b.mod_cr_percentage/100), a.tax_amount*(b.mod_cr_percentage/100)*cp_currency_conversion_rate),NVL(b.rounding_factor,1))) tax_amount
2332 FROM JAI_RCV_LINE_TAXES a,
2333 JAI_CMN_TAXES_ALL b
2334 WHERE a.transaction_id = cp_transaction_id
2335 AND a.tax_type in (select tax_type
2336 from jai_regime_tax_types_v
2337 where regime_code = jai_constants.vat_regime)
2338 AND a.tax_id = b.tax_id
2339 AND NVL(a.modvat_flag,'N') = 'Y';
2340
2341 CURSOR c_receive_transaction(cp_shipment_header_id IN rcv_shipment_headers.shipment_header_id%TYPE,
2342 cp_shipment_line_id IN rcv_shipment_lines.shipment_line_id%TYPE)
2343 IS
2344 SELECT transaction_id,
2345 organization_id,
2346 location_id,
2347 currency_conversion_rate
2348 FROM JAI_RCV_TRANSACTIONS
2349 WHERE shipment_header_id = cp_shipment_header_id
2350 AND shipment_line_id = cp_shipment_line_id
2351 AND transaction_type = 'RECEIVE';
2352
2353 CURSOR c_regime (cp_regime_id IN JAI_RGM_DEFINITIONS.regime_id%TYPE DEFAULT NULL,
2354 cp_regime_code IN JAI_RGM_DEFINITIONS.regime_code%TYPE DEFAULT NULL)
2355 IS
2356 SELECT *
2357 FROM JAI_RGM_DEFINITIONS
2358 WHERE regime_id = NVL(cp_regime_id, regime_id)
2359 AND regime_code = NVL(cp_regime_code, regime_code);
2360
2361 CURSOR c_rcv_parameters(cp_organization_id number) IS
2362 SELECT receiving_account_id
2363 FROM rcv_parameters
2364 WHERE organization_id = cp_organization_id;
2365
2366 lv_ttype_receive JAI_RCV_TRANSACTIONS.transaction_type%type;
2367 lv_ttype_correct JAI_RCV_TRANSACTIONS.transaction_type%type;
2368 lv_ttype_deliver JAI_RCV_TRANSACTIONS.transaction_type%type;
2369 lv_ttype_rtr JAI_RCV_TRANSACTIONS.transaction_type%type;
2370
2371 CURSOR c_receive_correct_txns(cp_shipment_header_id IN rcv_shipment_headers.shipment_header_id%TYPE, /* Modified by Ramananda for removal of SQL LITERALs :bug#4428980*/
2372 cp_shipment_line_id IN rcv_shipment_lines.shipment_line_id%TYPE)
2373 IS
2374 SELECT transaction_id,
2375 tax_transaction_id
2376 FROM JAI_RCV_TRANSACTIONS
2377 WHERE shipment_header_id = cp_shipment_header_id
2378 AND shipment_line_id = cp_shipment_line_id
2379 AND ( transaction_type = lv_ttype_receive --'RECEIVE'
2380 OR
2381 ( transaction_type = lv_ttype_correct -- 'CORRECT'
2382 AND parent_transaction_type = lv_ttype_receive )--'RECEIVE')
2383 )
2384 ORDER BY transaction_id;
2385
2386 CURSOR c_deliver_rtr_txns(cp_shipment_header_id IN rcv_shipment_headers.shipment_header_id%TYPE,
2387 cp_shipment_line_id IN rcv_shipment_lines.shipment_line_id%TYPE) /* Modified by Ramananda for removal of SQL LITERALs :bug#4428980*/
2388 IS
2389 SELECT transaction_id,
2390 tax_transaction_id,
2391 SIGN(quantity) quantity_multiplier
2392 FROM JAI_RCV_TRANSACTIONS
2393 WHERE shipment_header_id = cp_shipment_header_id
2394 AND shipment_line_id = cp_shipment_line_id
2395 AND ( transaction_type IN (lv_ttype_deliver, lv_ttype_rtr) --('DELIVER', 'RETURN TO RECEIVING')
2396 OR
2397 ( transaction_type = lv_ttype_correct --'CORRECT'
2398 AND parent_transaction_type IN (lv_ttype_deliver, lv_ttype_rtr)) --('DELIVER', 'RETURN TO RECEIVING'))
2399 )
2400 ORDER BY transaction_id;
2401
2402 r_regime c_regime%ROWTYPE;
2403 r_rcv_parameters c_rcv_parameters%rowtype;
2404 r_receive_transaction c_receive_transaction%ROWTYPE;
2405
2406
2407 /* File.Sql.35 by Brathod */
2408 lv_accounting_type VARCHAR2(100) ; -- := 'REGULAR';
2409 lv_account_nature VARCHAR2(100) ; -- := 'VAT NO CLAIM';
2410 lv_source_name VARCHAR2(100) ; -- := 'Purchasing India';
2411 lv_category_name VARCHAR2(100) ; -- := 'Receiving India';
2412 ld_accounting_date DATE ; -- := TRUNC(SYSDATE);
2413 lv_reference_10 gl_interface.reference10%TYPE ; -- := 'VAT Unclaim of the Receiving Entries';
2414 lv_reference_23 gl_interface.reference23%TYPE ; -- := 'jai_rgm_claim_pkg.process_no_claim';
2415 lv_reference_24 gl_interface.reference24%TYPE ; -- := 'JAI_RCV_TRANSACTIONS';
2416 lv_reference_25 gl_interface.reference25%TYPE ;
2417 lv_reference_26 gl_interface.reference26%TYPE ; -- := 'transaction_id';
2418 lv_destination VARCHAR2(10) ; -- := 'G';
2419 /* End of File.Sql.35 by Brathod */
2420
2421 ln_code_combination_id NUMBER;
2422 ln_entered_dr NUMBER;
2423 ln_entered_cr NUMBER;
2424 lv_currency_code VARCHAR2(10);
2425
2426 lv_code_path VARCHAR2(1996);
2427 lv_process_status VARCHAR2(2);
2428 lv_process_message VARCHAR2(1000);
2429 lv_codepath JAI_RCV_TRANSACTIONS.codepath%TYPE;
2430 rec_total_tax cur_total_tax%ROWTYPE;
2431 ln_process_special_amount NUMBER;
2432 ln_apportion_factor NUMBER;
2433
2434 BEGIN
2435 /* File.Sql.35 BY Brathod */
2436 lv_accounting_type := 'REGULAR';
2437 lv_account_nature := 'VAT NO CLAIM';
2438 lv_source_name := 'Purchasing India';
2439 lv_category_name := 'Receiving India';
2440 ld_accounting_date := TRUNC(SYSDATE);
2441 lv_reference_10 := 'VAT Unclaim of the Receiving Entries';
2442 lv_reference_23 := 'jai_rgm_claim_pkg.process_no_claim';
2443 lv_reference_24 := 'JAI_RCV_TRANSACTIONS';
2444 lv_reference_26 := 'transaction_id';
2445 lv_destination := 'G';
2446 /* End of File.Sql.35 by Brathod */
2447
2448 p_process_status := jai_constants.successful;
2449 p_process_message := NULL;
2450
2451
2452 lv_codepath := jai_general_pkg.plot_codepath(1, lv_codepath, 'jai_rcv_rgm_claims_pkg.process_no_claim', 'START');
2453
2454 OPEN c_regime(NULL, jai_constants.vat_regime);
2455 FETCH c_regime INTO r_regime;
2456 CLOSE c_regime;
2457
2458 /*FOR r_shipment_lines IN c_shipment_lines(p_shipment_header_id,
2459 p_shipment_line_id,
2460 p_batch_id)*/
2461 --commented the above and added the below for Bug#4950914
2462 FOR r_shipment_lines IN c_shipment_lines( cp_regime_id => p_regime_id,
2463 cp_regime_regno => p_regime_regno,
2464 cp_organization_id => p_organization_id,
2465 cp_location_id => p_location_id,
2466 cp_shipment_header_id => p_shipment_header_id,
2467 cp_shipment_line_id => p_shipment_line_id,
2468 cp_batch_id => p_batch_id)
2469 LOOP
2470
2471 r_receive_transaction := NULL;
2472 r_rcv_parameters := NULL;
2473
2474 lv_reference_10 := 'India Local UnClaim VAT Entries for Receipt:'||r_shipment_lines.receipt_num||'. Transaction Type VAT Unclaim';
2475
2476 lv_reference_25 := r_shipment_lines.transaction_id;
2477
2478 lv_codepath := jai_general_pkg.plot_codepath(2, lv_codepath);
2479
2480 OPEN c_receive_transaction(r_shipment_lines.shipment_header_id, r_shipment_lines.shipment_line_id);
2481 FETCH c_receive_transaction INTO r_receive_transaction;
2482 CLOSE c_receive_transaction;
2483
2484 OPEN c_rcv_parameters(r_receive_transaction.organization_id);
2485 FETCH c_rcv_parameters into r_rcv_parameters;
2486 CLOSE c_rcv_parameters;
2487
2488 --Pass the accounting entry
2489 lv_ttype_receive := 'RECEIVE' ;
2490 lv_ttype_correct := 'CORRECT' ;
2491
2492 FOR r_receive_correct_txns IN c_receive_correct_txns(r_shipment_lines.shipment_header_id, r_shipment_lines.shipment_line_id)
2493 LOOP
2494
2495 ln_apportion_factor := jai_rcv_trx_processing_pkg.get_apportion_factor(r_receive_correct_txns.transaction_id);
2496
2497 FOR rec_tax IN cur_tax(r_receive_transaction.transaction_id, r_receive_transaction.currency_conversion_rate)
2498 LOOP
2499 lv_currency_code := jai_constants.func_curr;
2500
2501 --DR Inventory Receiving
2502 ln_code_combination_id := r_rcv_parameters.receiving_account_id;
2503
2504 ln_entered_dr := rec_tax.tax_amount*ln_apportion_factor;
2505 ln_entered_cr := NULL;
2506
2507 IF NVL(ln_entered_dr,0) <> 0 OR NVL(ln_entered_cr,0) <> 0 THEN --Added for Bug#4950914
2508 jai_rcv_accounting_pkg.process_transaction(
2509 p_transaction_id => r_receive_correct_txns.transaction_id,
2510 p_acct_type => lv_accounting_type,
2511 p_acct_nature => lv_account_nature,
2512 p_source_name => lv_source_name,
2513 p_category_name => lv_category_name,
2514 p_code_combination_id => ln_code_combination_id,
2515 p_entered_dr => ln_entered_dr,
2516 p_entered_cr => ln_entered_cr,
2517 p_currency_code => lv_currency_code,
2518 p_accounting_date => ld_accounting_date,
2519 p_reference_10 => lv_reference_10,
2520 p_reference_23 => lv_reference_23,
2521 p_reference_24 => lv_reference_24,
2522 p_reference_25 => lv_reference_25,
2523 p_reference_26 => lv_reference_26,
2524 p_destination => lv_destination,
2525 p_simulate_flag => 'N',
2526 p_codepath => lv_code_path,
2527 p_process_message => lv_process_message,
2528 p_process_status => lv_process_status,
2529 p_reference_name => rec_tax.tax_type,
2530 p_reference_id => NULL);
2531
2532 IF lv_process_status <> jai_constants.successful THEN
2533 p_process_status := lv_process_status;
2534 p_process_message := lv_process_message;
2535 RETURN;
2536 END IF;
2537 END IF;
2538
2539 --CR VAT Interrim
2540 ln_code_combination_id :=
2541 jai_cmn_rgm_recording_pkg.get_account(
2542 p_regime_id => r_regime.regime_id,
2543 p_organization_type => jai_constants.orgn_type_io,
2544 p_organization_id => r_receive_transaction.organization_id,
2545 p_location_id => r_receive_transaction.location_id,
2546 p_tax_type => rec_tax.tax_type,
2547 p_account_name => jai_constants.recovery_interim);
2548
2549 ln_entered_dr := NULL;
2550 ln_entered_cr := rec_tax.tax_amount*ln_apportion_factor;
2551
2552 IF NVL(ln_entered_dr,0) <> 0 OR NVL(ln_entered_cr,0) <> 0 THEN --Added for Bug#4950914
2553 jai_rcv_accounting_pkg.process_transaction(
2554 p_transaction_id => r_receive_correct_txns.transaction_id,
2555 p_acct_type => lv_accounting_type,
2556 p_acct_nature => lv_account_nature,
2557 p_source_name => lv_source_name,
2558 p_category_name => lv_category_name,
2559 p_code_combination_id => ln_code_combination_id,
2560 p_entered_dr => ln_entered_dr,
2561 p_entered_cr => ln_entered_cr,
2562 p_currency_code => lv_currency_code,
2563 p_accounting_date => ld_accounting_date,
2564 p_reference_10 => lv_reference_10,
2565 p_reference_23 => lv_reference_23,
2566 p_reference_24 => lv_reference_24,
2567 p_reference_25 => lv_reference_25,
2568 p_reference_26 => lv_reference_26,
2569 p_destination => lv_destination,
2570 p_simulate_flag => 'N',
2571 p_codepath => lv_code_path,
2572 p_process_message => lv_process_message,
2573 p_process_status => lv_process_status,
2574 p_reference_name => rec_tax.tax_type,
2575 p_reference_id => NULL);
2576
2577 IF lv_process_status <> jai_constants.successful THEN
2578 p_process_status := lv_process_status;
2579 p_process_message := lv_process_message;
2580 RETURN;
2581 END IF;
2582 END IF;
2583
2584 END LOOP;
2585 END LOOP;
2586
2587 -- LOOP Through DELIVER/RTR
2588
2589 IF gv_debug THEN
2590 fnd_file.put_line(fnd_file.log, 'Before the Deliver RTR Transaction Cost Reversal');
2591 fnd_file.put_line(fnd_file.log, 'Shipment_header_id'||r_shipment_lines.shipment_header_id);
2592 fnd_file.put_line(fnd_file.log, 'Shipment_line_id'||r_shipment_lines.shipment_line_id);
2593 END IF;
2594
2595 lv_ttype_deliver := 'DELIVER' ;
2596 lv_ttype_rtr := 'RETURN TO RECEIVING' ;
2597 lv_ttype_receive := 'RECEIVE' ;
2598 lv_ttype_correct := 'CORRECT' ;
2599
2600
2601 FOR r_deliver_rtr_txns IN c_deliver_rtr_txns(r_shipment_lines.shipment_header_id, r_shipment_lines.shipment_line_id)
2602 LOOP
2603
2604 OPEN cur_total_tax(r_deliver_rtr_txns.tax_transaction_id, r_receive_transaction.currency_conversion_rate);
2605 FETCH cur_total_tax INTO rec_total_tax;
2606 CLOSE cur_total_tax;
2607
2608 ln_process_special_amount := rec_total_tax.tax_amount *
2609 ABS(jai_rcv_trx_processing_pkg.get_apportion_factor(r_deliver_rtr_txns.transaction_id)) *
2610 r_deliver_rtr_txns.quantity_multiplier;
2611 IF gv_debug THEN
2612 fnd_file.put_line(fnd_file.log, 'In the LOOP');
2613 fnd_file.put_line(fnd_file.log, 'Tax_transaction_id'||r_deliver_rtr_txns.Tax_transaction_id);
2614 fnd_file.put_line(fnd_file.log, 'transaction_id'||r_deliver_rtr_txns.transaction_id);
2615 fnd_file.put_line(fnd_file.log, 'currency_conversion_rate'||r_receive_transaction.currency_conversion_rate);
2616 fnd_file.put_line(fnd_file.log, 'Tax_amount'||rec_total_tax.tax_amount);
2617 fnd_file.put_line(fnd_file.log, 'Apportion Factor'||ABS(jai_rcv_trx_processing_pkg.get_apportion_factor(r_deliver_rtr_txns.transaction_id)));
2618
2619 END IF;
2620
2621 jai_rcv_deliver_rtr_pkg.process_transaction(
2622 p_transaction_id => r_deliver_rtr_txns.transaction_id,
2623 p_simulate => jai_constants.no,
2624 p_codepath => lv_code_path,
2625 p_process_message => lv_process_message,
2626 p_process_status => lv_process_status,
2627 p_process_special_source => jai_constants.vat_noclaim,
2628 p_process_special_amount => ln_process_special_amount);
2629
2630
2631 IF lv_process_status = 'E' THEN
2632 p_process_status := jai_constants.expected_error;
2633 p_process_message := lv_process_message;
2634 RETURN;
2635 END IF;
2636
2637 END LOOP;
2638
2639 UPDATE JAI_RCV_LINE_TAXES
2640 SET modvat_flag = 'N',
2641 last_update_date = SYSDATE,
2642 last_updated_by = fnd_global.user_id,
2643 last_update_login = fnd_global.login_id
2644 WHERE shipment_header_id = r_shipment_lines.shipment_header_id
2645 AND shipment_line_id = r_shipment_lines.shipment_line_id
2646 AND tax_type IN (select tax_type
2647 from jai_regime_tax_types_v
2648 where regime_code = jai_constants.vat_regime)
2649 AND modvat_flag = 'Y';
2650
2651 --To update the Status, so that It should not be considered for claiming
2652 UPDATE jai_rcv_rgm_lines
2653 SET process_status_flag = 'U',
2654 recoverable_amount = 0,
2655 recovered_amount = 0
2656 WHERE shipment_header_id = r_shipment_lines.shipment_header_id
2657 AND shipment_line_id = r_shipment_lines.shipment_line_id;
2658 --ABC--Update the other fields also, which are updated at the time of inserting the lines
2659
2660 --Ideally, this shouldn't delete any rows.
2661 DELETE jai_rcv_rgm_claims
2662 WHERE shipment_header_id = r_shipment_lines.shipment_header_id
2663 AND shipment_line_id = r_shipment_lines.shipment_line_id;
2664 END LOOP;
2665
2666 lv_codepath := jai_general_pkg.plot_codepath(3, lv_codepath, 'jai_rcv_rgm_claims_pkg.process_no_claim', 'START');
2667 EXCEPTION
2668 WHEN OTHERS THEN
2669 p_process_status := jai_constants.unexpected_error;
2670 p_process_message := SUBSTR(SQLERRM,1,200);
2671 END process_no_claim;
2672
2673 PROCEDURE process_batch(
2674 errbuf OUT NOCOPY VARCHAR2,
2675 retcode OUT NOCOPY VARCHAR2,
2676 p_regime_id IN JAI_RGM_DEFINITIONS.regime_id%TYPE,
2677 p_regime_regno IN VARCHAR2 DEFAULT NULL,
2678 p_organization_id IN hr_all_organization_units.organization_id%TYPE DEFAULT NULL,
2679 p_location_id IN hr_locations_all.location_id%TYPE DEFAULT NULL,
2680 p_shipment_header_id IN rcv_shipment_headers.shipment_header_id%TYPE DEFAULT NULL,
2681 p_shipment_line_id IN rcv_shipment_lines.shipment_line_id%TYPE DEFAULT NULL,
2682 p_batch_id IN JAI_RCV_RGM_LINES.BATCH_NUM%TYPE DEFAULT NULL,
2683 p_force IN VARCHAR2 DEFAULT NULL,
2684 p_commit_switch IN VARCHAR2,
2685 p_invoice_no IN JAI_RCV_TRANSACTIONS.vat_invoice_no%TYPE,
2686 pv_invoice_date IN VARCHAR2, /* rallamse bug#4336482 changed to VARCHAR2 from DATE */
2687 p_called_from IN VARCHAR2)
2688 IS
2689 lv_process_status VARCHAR2(2);
2690 lv_process_message VARCHAR2(1996);
2691 lv_codepath JAI_RCV_TRANSACTIONS.codepath%TYPE;
2692
2693 /* rallamse bug#4336482 */
2694 p_invoice_date CONSTANT DATE DEFAULT fnd_date.canonical_to_date(pv_invoice_date);
2695 /* End of Bug# 4336482 */
2696
2697 -- Date 29/05/2007 by sacsethi for bug 6078460
2698 -- Cursor where clause changed
2699
2700 CURSOR c_regime (cp_regime_id IN JAI_RGM_DEFINITIONS.regime_id%TYPE DEFAULT NULL,
2701 cp_regime_code IN JAI_RGM_DEFINITIONS.regime_code%TYPE DEFAULT NULL)
2702 IS
2703 SELECT *
2704 FROM JAI_RGM_DEFINITIONS
2705 WHERE (regime_id is null OR ( cp_regime_id IS NULL OR regime_id = cp_regime_id )) /* Modified by Ramananda for removal of SQL LITERALs :bug#4428980*/
2706 AND (regime_code = cp_regime_code OR regime_code is null);
2707
2708 --WHERE regime_id = NVL(cp_regime_id, regime_id)
2709 --AND regime_code = NVL(cp_regime_code, regime_code);
2710
2711 r_regime c_regime%ROWTYPE;
2712
2713 BEGIN
2714 lv_codepath := jai_general_pkg.plot_codepath(1, lv_codepath, 'jai_rcv_rgm_claims_pkg.process_batch', 'START');
2715
2716
2717 OPEN c_regime(p_regime_id, NULL);
2718 FETCH c_regime INTO r_regime;
2719 CLOSE c_regime;
2720
2721 IF r_regime.regime_code <> jai_constants.vat_regime THEN
2722 errbuf := 'This program is for VAT Regime Only';
2723 retcode := jai_constants.request_error;
2724 RETURN;
2725 END IF;
2726
2727 --commented the below for Bug#4950914
2728 /*IF p_batch_id IS NULL AND p_shipment_header_id IS NULL AND p_shipment_line_id IS NULL THEN
2729 errbuf := 'Invalid Parameters Passed';
2730 retcode := jai_constants.request_error;
2731 RETURN;
2732 END IF;*/
2733
2734 lv_codepath := jai_general_pkg.plot_codepath(2, lv_codepath);
2735
2736 process_claim(p_regime_id => p_regime_id,
2737 p_regime_regno => p_regime_regno,
2738 p_organization_id => p_organization_id,
2739 p_location_id => p_location_id,
2740 p_shipment_header_id => p_shipment_header_id,
2741 p_shipment_line_id => p_shipment_line_id,
2742 p_batch_id => p_batch_id,
2743 p_force => p_force,
2744 p_invoice_no => p_invoice_no,
2745 p_invoice_date => p_invoice_date,
2746 p_called_from => p_called_from,
2747 p_process_status => lv_process_status,
2748 p_process_message => lv_process_message);
2749
2750 IF lv_process_status <> jai_constants.successful THEN
2751 retcode := jai_constants.request_error;
2752 errbuf := lv_process_message;
2753 RETURN;
2754 END IF;
2755
2756 lv_codepath := jai_general_pkg.plot_codepath(3, lv_codepath);
2757
2758 --changed/Added the below for Bug#4950914
2759 process_no_claim(
2760 p_regime_id => p_regime_id,
2761 p_regime_regno => p_regime_regno,
2762 p_organization_id => p_organization_id,
2763 p_location_id => p_location_id,
2764 p_shipment_header_id => p_shipment_header_id,
2765 p_shipment_line_id => p_shipment_line_id,
2766 p_batch_id => p_batch_id,
2767 p_process_status => lv_process_status,
2768 p_process_message => lv_process_message);
2769
2770
2771 IF lv_process_status <> jai_constants.successful THEN
2772 retcode := jai_constants.request_error;
2773 errbuf := lv_process_message;
2774 RETURN;
2775 END IF;
2776
2777 lv_codepath := jai_general_pkg.plot_codepath(4, lv_codepath);
2778
2779 IF p_commit_switch = 'Y' THEN
2780 COMMIT;
2781 END IF;
2782
2783 lv_codepath := jai_general_pkg.plot_codepath(1, lv_codepath, 'jai_rcv_rgm_claims_pkg.process_batch', 'END');
2784 END process_batch;
2785
2786 PROCEDURE do_rtv_accounting(
2787 p_shipment_header_id IN rcv_shipment_headers.shipment_header_id%TYPE,
2788 p_shipment_line_id IN rcv_shipment_lines.shipment_line_id%TYPE,
2789 p_transaction_id IN rcv_transactions.transaction_id%TYPE,
2790 p_called_from IN VARCHAR2,
2791 p_invoice_no IN JAI_RCV_TRANSACTIONS.vat_invoice_no%TYPE,
2792 p_invoice_date IN JAI_RCV_TRANSACTIONS.vat_invoice_date%TYPE,
2793 p_process_status OUT NOCOPY VARCHAR2,
2794 p_process_message OUT NOCOPY VARCHAR2)
2795 IS
2796 CURSOR c_total_vat_amount(cp_transaction_id IN rcv_transactions.transaction_id%TYPE)
2797 IS
2798 SELECT b.organization_id,
2799 b.location_id,
2800 b.receipt_num,
2801 a.tax_type,
2802 (NVL(SUM(a.installment_amount),0) - NVL(SUM(a.claimed_amount),0))*-1 installment_amount
2803 FROM jai_rcv_rgm_claims a,
2804 jai_rcv_rgm_lines b
2805 WHERE a.rcv_rgm_line_id = b.rcv_rgm_line_id
2806 AND a.transaction_id = cp_transaction_id
2807 GROUP BY b.organization_id,
2808 b.location_id,
2809 b.receipt_num,
2810 a.tax_type;
2811
2812 CURSOR c_min_installment_no(cp_shipment_header_id IN rcv_shipment_headers.shipment_header_id%TYPE,
2813 cp_shipment_line_id IN rcv_shipment_lines.shipment_line_id%TYPE,
2814 cp_transaction_type jai_rcv_rgm_claims.transaction_type%type)
2815 IS
2816 SELECT NVL(max(installment_no),0) installment_no --for Bug #4279050, changed from min to max
2817 FROM jai_rcv_rgm_claims
2818 WHERE shipment_header_id = cp_shipment_header_id
2819 AND shipment_line_id = cp_shipment_line_id
2820 AND transaction_type = cp_transaction_type --'RECEIVE' /* Modified by Ramananda for removal of SQL LITERALs :bug#4428980*/
2821 AND claimed_date IS NOT NULL;
2822
2823 CURSOR c_total_reversal_amount(cp_shipment_header_id IN rcv_shipment_headers.shipment_header_id%TYPE,
2824 cp_shipment_line_id IN rcv_shipment_lines.shipment_line_id%TYPE,
2825 cp_transaction_id IN rcv_transactions.transaction_id%TYPE,
2826 cp_tax_type IN VARCHAR2,
2827 cp_installment_no IN NUMBER)
2828 IS
2829 SELECT (NVL(SUM(installment_amount),0) - NVL(SUM(claimed_amount),0))*-1 installment_amount
2830 FROM jai_rcv_rgm_claims
2831 WHERE shipment_header_id = cp_shipment_header_id
2832 AND shipment_line_id = cp_shipment_line_id
2833 AND transaction_id = cp_transaction_id
2834 AND installment_no > cp_installment_no
2835 AND tax_type = cp_tax_type;
2836
2837 -- Date 29/05/2007 by sacsethi for bug 6078460
2838 -- Cursor where clause changed
2839
2840 CURSOR c_regime (cp_regime_id IN JAI_RGM_DEFINITIONS.regime_id%TYPE DEFAULT NULL,
2841 cp_regime_code IN JAI_RGM_DEFINITIONS.regime_code%TYPE DEFAULT NULL)
2842 IS
2843 SELECT *
2844 FROM JAI_RGM_DEFINITIONS
2845 WHERE (regime_id is null OR ( cp_regime_id IS NULL OR regime_id = cp_regime_id )) /* Modified by Ramananda for removal of SQL LITERALs :bug#4428980*/
2846 AND (regime_code = cp_regime_code OR regime_code is null);
2847
2848 --WHERE regime_id = NVL(cp_regime_id, regime_id)
2849 --AND regime_code = NVL(cp_regime_code, regime_code);
2850
2851 CURSOR c_claim_schedule(cp_shipment_header_id IN rcv_shipment_headers.shipment_header_id%TYPE,
2852 cp_shipment_line_id IN rcv_shipment_lines.shipment_line_id%TYPE,
2853 cp_transaction_id IN rcv_transactions.transaction_id%TYPE,
2854 cp_tax_type IN JAI_CMN_TAXES_ALL.tax_type%TYPE)
2855 IS
2856 SELECT MIN(claim_schedule_id) claim_schedule_id
2857 FROM jai_rcv_rgm_claims
2858 WHERE shipment_header_id = cp_shipment_header_id
2859 AND shipment_line_id = cp_shipment_line_id
2860 AND tax_type = cp_tax_type
2861 AND transaction_id = cp_transaction_id
2862 AND NVL(claimed_amount,0) = 0;
2863
2864 r_total_vat_amount c_total_vat_amount%ROWTYPE;
2865 r_min_installment_no c_min_installment_no%ROWTYPE;
2866 r_total_reversal_amount c_total_reversal_amount%ROWTYPE;
2867 r_regime c_regime%ROWTYPE;
2868 r_claim_schedule c_claim_schedule%ROWTYPE;
2869 r_trx c_trx%ROWTYPE; /* Vijay */
2870
2871 /* File.Sql.35 by Brathod */
2872 lv_accounting_type VARCHAR2(100) ; -- := 'REVERSAL';
2873 lv_account_nature VARCHAR2(100) ; -- := 'VAT CLAIM';
2874 lv_source_name VARCHAR2(100) ; -- := 'Purchasing India';
2875 lv_category_name VARCHAR2(100) ; -- := 'Receiving India';
2876 ld_accounting_date DATE ; -- := TRUNC(SYSDATE);
2877 lv_reference_23 gl_interface.reference23%TYPE ; -- := 'jai_rgm_claim_pkg.process_vat';
2878 lv_reference_24 gl_interface.reference24%TYPE ; -- := 'JAI_RCV_TRANSACTIONS';
2879 lv_reference_25 gl_interface.reference25%TYPE ; -- := p_transaction_id;
2880 lv_reference_26 gl_interface.reference26%TYPE ; -- := 'transaction_id';
2881 lv_destination VARCHAR2(10) ; -- := 'G';
2882 /* End of File.Sql.35 by Brathod */
2883
2884 ln_rec_ccid NUMBER;
2885 ln_int_ccid NUMBER;
2886 ln_entered_dr NUMBER;
2887 ln_entered_cr NUMBER;
2888
2889 lv_reference_10 gl_interface.reference10%TYPE;
2890 lv_code_path VARCHAR2(1996);
2891 lv_process_status VARCHAR2(2);
2892 lv_process_message VARCHAR2(1000);
2893 lv_currency_code VARCHAR2(10);
2894 ln_repository_id NUMBER;
2895 ln_statement_id NUMBER;
2896 BEGIN
2897
2898 /* File.Sql.35 by Brathod */
2899 lv_accounting_type := 'REVERSAL';
2900 lv_account_nature := 'VAT CLAIM';
2901 lv_source_name := 'Purchasing India';
2902 lv_category_name := 'Receiving India';
2903 ld_accounting_date := TRUNC(SYSDATE);
2904 lv_reference_23 := 'jai_rgm_claim_pkg.process_vat';
2905 lv_reference_24 := 'JAI_RCV_TRANSACTIONS';
2906 lv_reference_25 := p_transaction_id;
2907 lv_reference_26 := 'transaction_id';
2908 lv_destination := 'G';
2909 /* End of File.Sql.35 by Brathod */
2910
2911 ln_statement_id := 100;
2912 p_process_status := jai_constants.successful;
2913 p_process_message := NULL;
2914
2915 OPEN c_regime(NULL, jai_constants.vat_regime);
2916 FETCH c_regime INTO r_regime;
2917 CLOSE c_regime;
2918
2919 OPEN c_min_installment_no(p_shipment_header_id, p_shipment_line_id, 'RECEIVE');
2920 FETCH c_min_installment_no INTO r_min_installment_no;
2921 CLOSE c_min_installment_no;
2922
2923 /* Vijay */
2924 OPEN c_trx(p_transaction_id);
2925 FETCH c_trx INTO r_trx;
2926 CLOSE c_trx;
2927
2928 lv_currency_code := jai_constants.func_curr;
2929
2930 ln_statement_id := 101;
2931 FOR rec_taxes IN c_total_vat_amount(p_transaction_id)
2932 LOOP
2933
2934 ln_statement_id := 102;
2935 lv_reference_10 := 'India Local VAT Claim Reversal Entries for Receipt:'||rec_taxes.receipt_num||'. Transaction Type '||p_called_from;
2936
2937 OPEN c_claim_schedule(p_shipment_header_id, p_shipment_line_id, p_transaction_id,rec_taxes.tax_type);
2938 FETCH c_claim_schedule INTO r_claim_schedule;
2939 CLOSE c_claim_schedule;
2940
2941 ln_statement_id := 102.1;
2942
2943 OPEN c_total_reversal_amount(p_shipment_header_id, p_shipment_line_id, p_transaction_id, rec_taxes.tax_type, r_min_installment_no.installment_no);
2944 FETCH c_total_reversal_amount INTO r_total_reversal_amount;
2945 CLOSE c_total_reversal_amount;
2946
2947 ln_statement_id := 102.2;
2948 ln_rec_ccid :=
2949 jai_cmn_rgm_recording_pkg.get_account(
2950 p_regime_id => r_regime.regime_id,
2951 p_organization_type => jai_constants.orgn_type_io,
2952 p_organization_id => rec_taxes.organization_id,
2953 p_location_id => rec_taxes.location_id,
2954 p_tax_type => rec_taxes.tax_type,
2955 p_account_name => jai_constants.recovery);
2956
2957 IF ln_rec_ccid IS NULL THEN
2958 p_process_status := jai_constants.expected_error;
2959 p_process_message := 'Recovery Account not defined in VAT Setup';
2960 RETURN;
2961 END IF;
2962
2963 ln_statement_id := 102.3;
2964 ln_int_ccid :=
2965 jai_cmn_rgm_recording_pkg.get_account(
2966 p_regime_id => r_regime.regime_id,
2967 p_organization_type => jai_constants.orgn_type_io,
2968 p_organization_id => rec_taxes.organization_id,
2969 p_location_id => rec_taxes.location_id,
2970 p_tax_type => rec_taxes.tax_type,
2971 p_account_name => jai_constants.recovery_interim);
2972
2973 IF ln_int_ccid IS NULL THEN
2974 p_process_status := jai_constants.expected_error;
2975 p_process_message := 'Internal Recovery Account not defined in VAT Setup';
2976 RETURN;
2977 END IF;
2978
2979 ln_statement_id := 103;
2980 --For Unclaimed Amount(for eg if out of 20, 15 is already claimed...then for 5)
2981 --DR Vat Recovery
2982 ln_entered_dr := r_total_reversal_amount.installment_amount;
2983 ln_entered_cr := NULL;
2984
2985 IF NVL(r_total_reversal_amount.installment_amount,0) <> 0 THEN
2986 jai_rcv_accounting_pkg.process_transaction(
2987 p_transaction_id => nvl(r_trx.tax_transaction_id, p_transaction_id), /* p_transaction_id, Vijay */
2988 p_acct_type => lv_accounting_type,
2989 p_acct_nature => lv_account_nature,
2990 p_source_name => lv_source_name,
2991 p_category_name => lv_category_name,
2992 p_code_combination_id => ln_rec_ccid,
2993 p_entered_dr => ln_entered_dr,
2994 p_entered_cr => ln_entered_cr,
2995 p_currency_code => lv_currency_code,
2996 p_accounting_date => ld_accounting_date,
2997 p_reference_10 => lv_reference_10,
2998 p_reference_23 => lv_reference_23,
2999 p_reference_24 => lv_reference_24,
3000 p_reference_25 => lv_reference_25,
3001 p_reference_26 => lv_reference_26,
3002 p_destination => lv_destination,
3003 p_simulate_flag => 'N',
3004 p_codepath => lv_code_path,
3005 p_process_message => lv_process_message,
3006 p_process_status => lv_process_status,
3007 p_reference_name => rec_taxes.tax_type||'1',
3008 p_reference_id => NULL);
3009
3010 IF lv_process_status <> jai_constants.successful THEN
3011 p_process_status := lv_process_status;
3012 p_process_message := lv_process_message;
3013 RETURN;
3014 END IF;
3015 END IF;
3016
3017 --CR Vat Interim
3018 ln_entered_dr := NULL;
3019 ln_entered_cr := r_total_reversal_amount.installment_amount;
3020
3021 IF NVL(r_total_reversal_amount.installment_amount,0) <> 0 THEN
3022 jai_rcv_accounting_pkg.process_transaction(
3023 p_transaction_id => nvl(r_trx.tax_transaction_id, p_transaction_id), /* p_transaction_id, Vijay */
3024 p_acct_type => lv_accounting_type,
3025 p_acct_nature => lv_account_nature,
3026 p_source_name => lv_source_name,
3027 p_category_name => lv_category_name,
3028 p_code_combination_id => ln_int_ccid,
3029 p_entered_dr => ln_entered_dr,
3030 p_entered_cr => ln_entered_cr,
3031 p_currency_code => lv_currency_code,
3032 p_accounting_date => ld_accounting_date,
3033 p_reference_10 => lv_reference_10,
3034 p_reference_23 => lv_reference_23,
3035 p_reference_24 => lv_reference_24,
3036 p_reference_25 => lv_reference_25,
3037 p_reference_26 => lv_reference_26,
3038 p_destination => lv_destination,
3039 p_simulate_flag => 'N',
3040 p_codepath => lv_code_path,
3041 p_process_message => lv_process_message,
3042 p_process_status => lv_process_status,
3043 p_reference_name => rec_taxes.tax_type||'1',
3044 p_reference_id => NULL);
3045
3046 IF lv_process_status <> jai_constants.successful THEN
3047 p_process_status := lv_process_status;
3048 p_process_message := lv_process_message;
3049 RETURN;
3050 END IF;
3051 END IF;
3052
3053 --For Unclaimed Amount(for eg if out of 20, 15 is already claimed...then for 20)
3054 --DR Vat Interim
3055 ln_entered_dr := rec_taxes.installment_amount;
3056 ln_entered_cr := NULL;
3057
3058 IF NVL(rec_taxes.installment_amount,0) <> 0 THEN
3059 jai_rcv_accounting_pkg.process_transaction(
3060 p_transaction_id => p_transaction_id,
3061 p_acct_type => lv_accounting_type,
3062 p_acct_nature => lv_account_nature,
3063 p_source_name => lv_source_name,
3064 p_category_name => lv_category_name,
3065 p_code_combination_id => ln_int_ccid,
3066 p_entered_dr => ln_entered_dr,
3067 p_entered_cr => ln_entered_cr,
3068 p_currency_code => lv_currency_code,
3069 p_accounting_date => ld_accounting_date,
3070 p_reference_10 => lv_reference_10,
3071 p_reference_23 => lv_reference_23,
3072 p_reference_24 => lv_reference_24,
3073 p_reference_25 => lv_reference_25,
3074 p_reference_26 => lv_reference_26,
3075 p_destination => lv_destination,
3076 p_simulate_flag => 'N',
3077 p_codepath => lv_code_path,
3078 p_process_message => lv_process_message,
3079 p_process_status => lv_process_status,
3080 p_reference_name => rec_taxes.tax_type||'2',
3081 p_reference_id => NULL);
3082
3083 IF lv_process_status <> jai_constants.successful THEN
3084 p_process_status := lv_process_status;
3085 p_process_message := lv_process_message;
3086 RETURN;
3087 END IF;
3088 END IF;
3089
3090 --CR Vat Recovery
3091 ln_entered_dr := NULL;
3092 ln_entered_cr := rec_taxes.installment_amount;
3093
3094 IF NVL(rec_taxes.installment_amount,0) <> 0 THEN
3095 jai_rcv_accounting_pkg.process_transaction(
3096 p_transaction_id => p_transaction_id,
3097 p_acct_type => lv_accounting_type,
3098 p_acct_nature => lv_account_nature,
3099 p_source_name => lv_source_name,
3100 p_category_name => lv_category_name,
3101 p_code_combination_id => ln_rec_ccid,
3102 p_entered_dr => ln_entered_dr,
3103 p_entered_cr => ln_entered_cr,
3104 p_currency_code => lv_currency_code,
3105 p_accounting_date => ld_accounting_date,
3106 p_reference_10 => lv_reference_10,
3107 p_reference_23 => lv_reference_23,
3108 p_reference_24 => lv_reference_24,
3109 p_reference_25 => lv_reference_25,
3110 p_reference_26 => lv_reference_26,
3111 p_destination => lv_destination,
3112 p_simulate_flag => 'N',
3113 p_codepath => lv_code_path,
3114 p_process_message => lv_process_message,
3115 p_process_status => lv_process_status,
3116 p_reference_name => rec_taxes.tax_type||'2',
3117 p_reference_id => NULL);
3118
3119 IF lv_process_status <> jai_constants.successful THEN
3120 p_process_status := lv_process_status;
3121 p_process_message := lv_process_message;
3122 RETURN;
3123 END IF;
3124 END IF;
3125
3126 ln_statement_id := 104;
3127
3128 ln_entered_dr := rec_taxes.installment_amount;
3129 ln_entered_cr := NULL;
3130
3131 IF ln_entered_dr < 0 THEN
3132 ln_entered_cr := ln_entered_dr*-1;
3133 /* Vijay ln_entered_cr := NULL; */
3134 ln_entered_dr := NULL;
3135 END IF;
3136
3137 ln_statement_id := 105;
3138 IF NVL(ln_entered_dr,0) <> 0 OR NVL(ln_entered_cr,0) <> 0 THEN --Added for Bug#4950914
3139 jai_cmn_rgm_recording_pkg.insert_vat_repository_entry(
3140 pn_repository_id => ln_repository_id,
3141 pn_regime_id => r_regime.regime_id,
3142 pv_tax_type => rec_taxes.tax_type,
3143 pv_organization_type => jai_constants.orgn_type_io,
3144 pn_organization_id => rec_taxes.organization_id,
3145 pn_location_id => rec_taxes.location_id,
3146 pv_source => jai_constants.source_rcv,
3147 pv_source_trx_type => 'VAT CLAIM',
3148 pv_source_table_name => TABLE_RCV_TRANSACTIONS, /* 'JAI_RCV_RGM_CLAIMS', Vijay */
3149 pn_source_id => p_transaction_id, /* r_claim_schedule.claim_schedule_id, Vijay */
3150 pd_transaction_date => trunc(sysdate),
3151 pv_account_name => jai_constants.recovery,
3152 pn_charge_account_id => ln_rec_ccid,
3153 pn_balancing_account_id => ln_int_ccid,
3154 pn_credit_amount => ln_entered_cr,
3155 pn_debit_amount => ln_entered_dr,
3156 pn_assessable_value => NULL,
3157 pn_tax_rate => NULL,
3158 pn_reference_id => r_claim_schedule.claim_schedule_id,
3159 pn_batch_id => NULL,
3160 pn_inv_organization_id => rec_taxes.organization_id,
3161 pv_invoice_no => p_invoice_no,
3162 pd_invoice_date => p_invoice_date,
3163 pv_called_from => 'JAI_RGM_CLAIM_PKG.DO_RTV_ACCOUNTING',
3164 pv_process_flag => lv_process_status,
3165 pv_process_message => lv_process_message,
3166 pv_attribute_context => NULL,
3167 pv_attribute1 => NULL,
3168 pv_attribute2 => NULL,
3169 pv_attribute3 => NULL,
3170 pv_attribute4 => NULL,
3171 pv_attribute5 => NULL);
3172 ln_statement_id := 106;
3173 IF gv_debug THEN
3174 fnd_file.put_line(fnd_file.log, 'lv_process_status'||lv_process_status);
3175 fnd_file.put_line(fnd_file.log, 'lv_process_message'||lv_process_message);
3176 END IF;
3177
3178 IF lv_process_status <> jai_constants.successful THEN
3179 p_process_status := lv_process_status;
3180 p_process_message := lv_process_message;
3181 RETURN;
3182 END IF;
3183
3184 END IF;
3185
3186 ln_statement_id := 107;
3187 ln_entered_dr := NULL;
3188 ln_entered_cr := r_total_reversal_amount.installment_amount;
3189
3190 IF ln_entered_cr < 0 THEN
3191 ln_entered_dr := ln_entered_cr*-1;
3192 ln_entered_cr := NULL;
3193 END IF;
3194
3195 ln_statement_id := 108;
3196 /* if condition added by Vijay */
3197 IF NVL(r_total_reversal_amount.installment_amount,0) <> 0 THEN
3198
3199 jai_cmn_rgm_recording_pkg.insert_vat_repository_entry(
3200 pn_repository_id => ln_repository_id,
3201 pn_regime_id => r_regime.regime_id,
3202 pv_tax_type => rec_taxes.tax_type,
3203 pv_organization_type => jai_constants.orgn_type_io,
3204 pn_organization_id => rec_taxes.organization_id,
3205 pn_location_id => rec_taxes.location_id,
3206 pv_source => jai_constants.source_rcv,
3207 pv_source_trx_type => 'VAT CLAIM for RTV',
3208 pv_source_table_name => TABLE_RCV_TRANSACTIONS, /* 'JAI_RCV_RGM_CLAIMS', Vijay */
3209 pn_source_id => nvl(r_trx.tax_transaction_id, p_transaction_id), /* r_claim_schedule.claim_schedule_id, Vijay*/
3210 pd_transaction_date => TRUNC(SYSDATE),
3211 pv_account_name => jai_constants.recovery,
3212 pn_charge_account_id => ln_rec_ccid,
3213 pn_balancing_account_id => ln_int_ccid,
3214 pn_credit_amount => ln_entered_cr,
3215 pn_debit_amount => ln_entered_dr,
3216 pn_assessable_value => NULL,
3217 pn_tax_rate => NULL,
3218 pn_reference_id => r_claim_schedule.claim_schedule_id,
3219 pn_batch_id => NULL,
3220 pn_inv_organization_id => rec_taxes.organization_id,
3221 pv_invoice_no => p_invoice_no,
3222 pd_invoice_date => p_invoice_date,
3223 pv_called_from => 'JAI_RGM_CLAIM_PKG.DO_RTV_ACCOUNTING',
3224 pv_process_flag => lv_process_status,
3225 pv_process_message => lv_process_message,
3226 pv_attribute_context => NULL,
3227 pv_attribute1 => NULL,
3228 pv_attribute2 => NULL,
3229 pv_attribute3 => NULL,
3230 pv_attribute4 => NULL,
3231 pv_attribute5 => NULL);
3232
3233 ln_statement_id := 109;
3234 IF gv_debug THEN
3235 fnd_file.put_line(fnd_file.log, 'lv_process_status'||lv_process_status);
3236 fnd_file.put_line(fnd_file.log, 'lv_process_message'||lv_process_message);
3237 END IF;
3238
3239 IF lv_process_status <> jai_constants.successful THEN
3240 p_process_status := lv_process_status;
3241 p_process_message := lv_process_message;
3242 RETURN;
3243 END IF;
3244
3245 end if;
3246
3247 END LOOP;
3248
3249 UPDATE jai_rcv_rgm_claims
3250 SET claimed_amount = installment_amount,
3251 claimed_date = TRUNC(SYSDATE),
3252 status = 'Y'
3253 WHERE shipment_header_id = p_shipment_header_id
3254 AND shipment_line_id = p_shipment_line_id
3255 AND transaction_id = p_transaction_id
3256 AND installment_no <= r_min_installment_no.installment_no
3257 AND status = 'N';
3258
3259 EXCEPTION
3260 WHEN OTHERS THEN
3261 p_process_status := jai_constants.unexpected_error;
3262 p_process_message := 'Stmt :'||ln_statement_id||' '||SUBSTR(SQLERRM,1,200);
3263 END do_rtv_accounting;
3264
3265 PROCEDURE do_rma_accounting(
3266 p_transaction_id IN rcv_transactions.transaction_id%TYPE,
3267 p_process_status OUT NOCOPY VARCHAR2,
3268 p_process_message OUT NOCOPY VARCHAR2)
3269 IS
3270
3271 CURSOR cur_tax(cp_transaction_id IN JAI_RCV_LINE_TAXES.transaction_id%TYPE,
3272 cp_currency_conversion_rate IN JAI_RCV_TRANSACTIONS.currency_conversion_rate%TYPE)
3273 IS
3274 SELECT a.tax_type,/*COL (b.mod_cr_percentage/100) added by Nitin for bug #6681800*/
3275 SUM(ROUND(DECODE(a.currency, jai_constants.func_curr, a.tax_amount*(b.mod_cr_percentage/100), a.tax_amount*(b.mod_cr_percentage/100)*cp_currency_conversion_rate),NVL(b.rounding_factor,0))) tax_amount
3276 FROM JAI_RCV_LINE_TAXES a,
3277 JAI_CMN_TAXES_ALL b
3278 WHERE a.transaction_id = cp_transaction_id
3279 AND a.tax_type in (select tax_type
3280 from jai_regime_tax_types_v
3281 where regime_code = jai_constants.vat_regime)
3282 AND NVL(a.modvat_flag,'N') = 'Y'
3283 AND a.tax_id = b.tax_id
3284 GROUP BY a.tax_type;
3285
3286 -- Date 29/05/2007 by sacsethi for bug 6078460
3287 -- Cursor where clause changed
3288
3289 CURSOR c_regime (cp_regime_id IN JAI_RGM_DEFINITIONS.regime_id%TYPE DEFAULT NULL,
3290 cp_regime_code IN JAI_RGM_DEFINITIONS.regime_code%TYPE DEFAULT NULL)
3291 IS
3292 SELECT *
3293 FROM JAI_RGM_DEFINITIONS
3294 WHERE (regime_id is null OR ( cp_regime_id IS NULL OR regime_id = cp_regime_id )) /* Modified by Ramananda for removal of SQL LITERALs :bug#4428980*/
3295 AND (regime_code = cp_regime_code OR regime_code is null);
3296
3297 --WHERE regime_id = NVL(cp_regime_id, regime_id)
3298 --AND regime_code = NVL(cp_regime_code, regime_code);
3299
3300
3301 r_regime c_regime%ROWTYPE;
3302 r_trx c_trx%ROWTYPE;
3303 /* File.Sql.35 by Brathod */
3304 lv_accounting_type VARCHAR2(100) ; -- := 'REGULAR';
3305 lv_account_nature VARCHAR2(100) ; -- := 'Receiving';
3306 lv_source_name VARCHAR2(100) ; -- := 'Purchasing India';
3307 lv_category_name VARCHAR2(100) ; -- := 'Receiving India';
3308 lv_reference_23 gl_interface.reference23%TYPE ; -- := 'jai_rgm_claim_pkg.do_rma_accounting';
3309 lv_reference_24 gl_interface.reference24%TYPE ; -- := 'rcv_transactions';
3310 lv_reference_25 gl_interface.reference25%TYPE ; -- := p_transaction_id;
3311 lv_reference_26 gl_interface.reference26%TYPE ; -- := 'transaction_id';
3312 lv_destination VARCHAR2(10) ; -- := 'G';
3313 /* End of File.Sql.35 by Brathod */
3314 ln_code_combination_id NUMBER;
3315 ln_entered_dr NUMBER;
3316 ln_entered_cr NUMBER;
3317 lv_currency_code VARCHAR2(10);
3318 ld_accounting_date DATE;
3319 lv_reference_10 gl_interface.reference10%TYPE;
3320 lv_code_path JAI_RCV_TRANSACTIONS.codepath%TYPE;
3321 lv_process_status VARCHAR2(2);
3322 lv_process_message VARCHAR2(1000);
3323 ln_apportion_factor NUMBER;
3324 ln_tax_amount NUMBER;
3325 LN_DEBIT_CCID NUMBER;
3326 LN_CREDIT_CCID NUMBER;
3327
3328 BEGIN
3329
3330 /* File.Sql.35 by Brathod */
3331 lv_accounting_type := 'REGULAR';
3332 lv_account_nature := 'Receiving';
3333 lv_source_name := 'Purchasing India';
3334 lv_category_name := 'Receiving India';
3335 lv_reference_23 := 'jai_rgm_claim_pkg.do_rma_accounting';
3336 lv_reference_24 := 'rcv_transactions';
3337 lv_reference_25 := p_transaction_id;
3338 lv_reference_26 := 'transaction_id';
3339 lv_destination := 'G';
3340 /* End of File.Sql.35 by Brathod */
3341
3342 p_process_status := jai_constants.successful;
3343 p_process_message := NULL;
3344
3345 OPEN c_trx(p_transaction_id);
3346 FETCH c_trx INTO r_trx;
3347 CLOSE c_trx;
3348
3349 OPEN c_regime(NULL, jai_constants.vat_regime);
3350 FETCH c_regime INTO r_regime;
3351 CLOSE c_regime;
3352
3353 ld_accounting_date := trunc(r_trx.transaction_date);
3354 lv_currency_code := jai_constants.func_curr;
3355 lv_reference_10 := 'India Local VAT RMA Entries for Receipt:'||r_trx.receipt_num
3356 ||'. Transaction Type '||r_trx.transaction_type;
3357
3358 if r_trx.transaction_type = 'CORRECT' then
3359 lv_reference_10 := lv_reference_10 || ' of type ' || r_trx.parent_transaction_type;
3360 end if;
3361
3362 ln_apportion_factor := jai_rcv_trx_processing_pkg.get_apportion_factor(p_transaction_id);
3363
3364 lv_code_path := '';
3365
3366 FOR rec_tax IN cur_tax(r_trx.tax_transaction_id, r_trx.currency_conversion_rate)
3367 LOOP
3368
3369 --Added by for Bug#4950914
3370 IF NVL(rec_tax.tax_amount,0) = 0 THEN
3371 goto END_OF_LOOP;
3372 END IF;
3373
3374 ln_tax_amount := rec_tax.tax_amount*ln_apportion_factor;
3375
3376 IF gv_debug THEN
3377 fnd_file.put_line(fnd_file.log, 'rec_tax.tax_amount:'||rec_tax.tax_amount);
3378 fnd_file.put_line(fnd_file.log, 'ln_tax_amount:'||ln_tax_amount);
3379 END IF;
3380
3381 ln_debit_ccid :=
3382 jai_cmn_rgm_recording_pkg.get_account(
3383 p_regime_id => r_regime.regime_id,
3384 p_organization_type => jai_constants.orgn_type_io,
3385 p_organization_id => r_trx.organization_id,
3386 p_location_id => r_trx.location_id,
3387 p_tax_type => rec_tax.tax_type,
3388 p_account_name => jai_constants.recovery_interim);
3389
3390 IF ln_debit_ccid IS NULL THEN
3391 p_process_status := jai_constants.expected_error;
3392 p_process_message := 'Interim recovery Account not defined in VAT Setup';
3393 RETURN;
3394 END IF;
3395
3396 --dr VAT Interim
3397 ln_entered_dr := ln_tax_amount;
3398 ln_entered_cr := NULL;
3399
3400 --Added for Bug#4950914
3401 IF NVL(ln_tax_amount,0) <> 0 THEN
3402 jai_rcv_accounting_pkg.process_transaction(
3403 p_transaction_id => r_trx.transaction_id,
3404 p_acct_type => lv_accounting_type,
3405 p_acct_nature => lv_account_nature,
3406 p_source_name => lv_source_name,
3407 p_category_name => lv_category_name,
3408 p_code_combination_id => ln_debit_ccid,
3409 p_entered_dr => ln_entered_dr,
3410 p_entered_cr => ln_entered_cr,
3411 p_currency_code => lv_currency_code,
3412 p_accounting_date => ld_accounting_date,
3413 p_reference_10 => lv_reference_10,
3414 p_reference_23 => lv_reference_23,
3415 p_reference_24 => lv_reference_24,
3416 p_reference_25 => lv_reference_25,
3417 p_reference_26 => lv_reference_26,
3418 p_destination => lv_destination,
3419 p_simulate_flag => 'N',
3420 p_codepath => lv_code_path,
3421 p_process_message => lv_process_message,
3422 p_process_status => lv_process_status,
3423 p_reference_name => rec_tax.tax_type,
3424 p_reference_id => NULL);
3425
3426 IF lv_process_status <> jai_constants.successful THEN
3427 p_process_status := lv_process_status;
3428 p_process_message := lv_process_message;
3429 RETURN;
3430 END IF;
3431 END IF;
3432 --CR Interim Liability
3433 ln_credit_ccid :=
3434 jai_cmn_rgm_recording_pkg.get_account(
3435 p_regime_id => r_regime.regime_id,
3436 p_organization_type => jai_constants.orgn_type_io,
3437 p_organization_id => r_trx.organization_id,
3438 p_location_id => r_trx.location_id,
3439 p_tax_type => rec_tax.tax_type,
3440 p_account_name => jai_constants.liability_interim);
3441
3442 IF ln_credit_ccid IS NULL THEN
3443 p_process_status := jai_constants.expected_error;
3444 p_process_message := 'Interim Liability Account not defined in VAT Setup';
3445 RETURN;
3446 END IF;
3447
3448 ln_entered_dr := NULL;
3449 ln_entered_cr := ln_tax_amount;
3450
3451 --Added for Bug#4950914
3452 IF NVL(ln_tax_amount,0) <> 0 THEN
3453 jai_rcv_accounting_pkg.process_transaction(
3454 p_transaction_id => r_trx.transaction_id,
3455 p_acct_type => lv_accounting_type,
3456 p_acct_nature => lv_account_nature,
3457 p_source_name => lv_source_name,
3458 p_category_name => lv_category_name,
3459 p_code_combination_id => ln_credit_ccid,
3460 p_entered_dr => ln_entered_dr,
3461 p_entered_cr => ln_entered_cr,
3462 p_currency_code => lv_currency_code,
3463 p_accounting_date => ld_accounting_date,
3464 p_reference_10 => lv_reference_10,
3465 p_reference_23 => lv_reference_23,
3466 p_reference_24 => lv_reference_24,
3467 p_reference_25 => lv_reference_25,
3468 p_reference_26 => lv_reference_26,
3469 p_destination => lv_destination,
3470 p_simulate_flag => 'N',
3471 p_codepath => lv_code_path,
3472 p_process_message => lv_process_message,
3473 p_process_status => lv_process_status,
3474 p_reference_name => rec_tax.tax_type,
3475 p_reference_id => NULL);
3476
3477 IF lv_process_status <> jai_constants.successful THEN
3478 p_process_status := lv_process_status;
3479 p_process_message := lv_process_message;
3480 RETURN;
3481 END IF;
3482 END IF;
3483 <<END_OF_LOOP>>
3484
3485 NULL;
3486
3487 END LOOP;
3488 EXCEPTION
3489 WHEN OTHERS THEN
3490 p_process_status := jai_constants.unexpected_error;
3491 p_process_message := SUBSTR(SQLERRM,1,200);
3492 END do_rma_accounting;
3493
3494 END jai_rcv_rgm_claims_pkg;