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