1 PACKAGE BODY jai_rcv_trx_processing_pkg AS
2 /* $Header: jai_rcv_trx_prc.plb 120.25.12010000.2 2009/01/05 09:45:18 srjayara ship $ */
3 /*----------------------------------------------------------------------------------------------------------------------------
4 CHANGE HISTORY for FILENAME: jai_rcv_trx_processing_pkg.sql
5 S.No dd/mm/yyyy Author and Details
6 ------------------------------------------------------------------------------------------------------------------------------
7 1 26/07/2004 Vijay Shankar for Bug# 3496408, Version:115.0
8 This Package is coded for Corrections Enhancement to invoke CENVAT and RG related insert APIs for PO Functionality.
9
10 - PROCESS_BATCH
11 Main procedure that is called from JAINRVCTP Concurrent Program. All the Concurrent Program Parameters are Optional,
12 in the sence that all the unprocessed and Pending Receipt Transactions will be Picked up for processing. Calls different
13 APIs to process the transaction and pass Localization Related Accounting, Cenvat and RG Entries
14 This procedure doesnot COMMIT the data if it is called from an Application
15
16 - PROCESS_TRANSACTION
17 Driving procedure that validates the transaction processing and if eligible, then calls different API's based on
18 trasaction type for processing. This should never be invoked directly as this needs data in JAI_RCV_TRANSACTIONS and which
19 is populated in batch procedure only.
20 - POPULATE_DETAILS
21 UPDATEs JAI_RCV_TRANSACTIONS with details(transaction,setup, validity) of the transaction that will be used
22 while processing the transaction
23 - VALIDATE_TRANSACTION
24 Validates applicability of Transaction for Normal and Cenvat Accounting, RG Processing. Finally after validation, updates
25 process_flag, cenvat_rg_flag, messages of JAI_RCV_TRANSACTIONS with relevant details. Further these values are used
26 to decide whether to proceed or not for Accounting and Cenvat processing
27 - PROCESS_ISO_TRANSACTION
28 RETURNs true if ISO Entries needs to be passed for transaction, else returns false. This is basically an applicability func.
29 - GET_ANCESTOR_ID
30 RETURNs the transaction_id of PARANT transaction type required for the current transaction
31 - GET_APPORTION_FACTOR
32 RETURNs the factor that should be used for multiplication with transaction Quantity and JAI_RCV_LINE_TAXES.tax amount to
33 get the transaction tax amount
34 - GET_TRXN_CENVAT_AMOUNT
35 RETURNs the transaction EXCISE Amount
36 - GET_TRXN_TAX_AMOUNT
37 RETURNs the transaction total TAX Amount (Excluding Modvat Recovery, TDS)
38 Other Procedures/Functions are coded for simplicity of the APPLICATION logic
39
40 2 26/10/2004 Vijay Shankar for Bugs#3927371,3949109,3949502 Version:115.1
41 Bugs#3927371 - Code modified to PROCESS only CORRECT, DELIVER and RTR transactions in PROCESS_TRANSACTIONS procedure
42 Code modified to return back ERROR Status only if any error (i.e process_status='E') occurs during Processing.
43 If process_status is 'X', then Normal Status is return back by just printing an Information Message in Log
44 Bugs#3949109 - added code in POPULATE_DETAILS to fetch subinventory from DELIVER transaction incase of direct delivery if the RECEIVE transaction
45 donot have either of location_id or subinventory attached with it
46 Bugs#3949502 - For a CORRECT of DELIVER transaction Subinventory is not getting populated, which is Stopping Accounting
47 of CORRECT transaction. this is resolved by fetching Subinventory from parent DELIVER and use it for processing
48
49 3 18/12/2004 Vijay Shankar for Bug# 4038024, 4070938, 4038044. FileVersion: 115.2
50 Bug#4038024, 4038044
51 Modified the code in populate_details to fetch Subinventory/location from parent transaction if it is not present in
52 the current transaction
53 Bug#4070938
54 Modified the value contained in the Package Variable NO_ITEM_CLASS to contain OTIN instead of 'XXXX'. This would mean
55 that, if there is no item attached to Receipt Line or if this is a non localization item, then it is treated as OTIN item
56
57 4 03/01/2005 Vijay Shankar for Bug# 3940588, Version:115.3
58
59 Following are the changes made for the purpose of RECEIPTsDEPLUG of Old Code and link it with New Corrections Code
60 - Added the procedures/functions
61 - PROCESS_DEFERRED_CENVAT_CLAIM : Will be invoked when request for JAINRVCTP is submitted from JAINMVAT form.
62 This is coded to process records of JAI_RCV_CENVAT_CLAIM_T table that needs to be claimed/unclaimed.
63 Incase of Unclaim, the transactions that are not yet processed/pending for claim or excise is not included in
64 cost are processed for Cenvat Costing. This happens for DELIVER, RTR and related CORRECTIONS
65 - GET_EQUIVALENT_QTY_OF_RECEIVE : Function that Returns quantity equivalent to RECEIVE UOM. Useful incase there
66 are changed between RECEIVE and other receiving transactions
67 - TRANSACTION_PROPROCESSOR : when Called for all RECEIVE and MATCH transactions, this does initial processing of
68 shipment line which will be used for all receiving transactions. Also this is place for other transactions also
69 where in initial processing has to happen for any transaction before going ahead for actual processing.
70 This inserts data into JAI_RCV_CENVAT_CLAIMS, JAI_CMN_RG_OTHERS tables for Cenvat and Cess Amounts
71 - TRANSACTION_POSTPROCESSOR : Does the post processing logic like updating some quantity columns at shipment
72 line level for DELIVER, RTR, RTV and related CORRECTs
73 - Increased the filtering condition for transactions processing by adding p_shipment_header_id and p_shipment_line_id
74 parameters to PROCESS_BATCH procedure
75 - Modified CURSORs c_trxns_to_populate_dtls and c_get_transactions of PROCESS_BATCH, to pickup only those transactions
76 where in users cannot modify the taxes anymore
77 - UPDATEs JAI_RCV_LINES with tax_modified_flag as 'N' so that taxes for that line cannot be modified anymore
78 when p_called_from is JAINPORE (Localization Receipts form)
79 - Calls to transaction_preprocessor and transaction_postprocessed are made to do processing required before and after
80 actual processing
81 - Changes in PROCESS_TRANSACTION procedure
82 - Opened up the code to execute procedure for all localization supported receiving transactions
83 - Modified the condition which if satisfied makes a call to jai_rcv_excise_processing_pkg.process_transaction
84 - Added p_process_special_reason, p_process_special_qty parameters in call to jai_rcv_excise_processing_pkg.process_transaction
85 - Modified POPULATE_DETAILS procedure to populate tax_transaction_id and third_party_flag values. Tax_Transaction_id is the
86 transaction_id related to parent transaction for which taxes are defaulted/attached. usually this is either RECEIVE or MATCH trx
87 - Also changes are made to update ja_in_rcv_transaction.transaction_type to RECEIVE incase of MATCH transaction
88 - Modifed VALIDATE_TRANSACTION to function properly. In this procedure different validations are applied that are
89 required for NON-CENVAT and CENVAT processing of transactions
90 - get_ancestor_id modified to support MATCH transaction also
91
92 - Changes required for Education CESS are done in all procedures/functions to consider CESS taxes also whereever
93 Excise and CVD taxes are referred
94
95 5. 09/02/2005 Vijay Shankar for Bug #4172424, Version 115.4
96 Issue -
97 RG23 D register / accounting entries are not happening
98 (i) if the item class is FGIN/FGEX
99 (ii) if the Claim Cenvat on Receipt flag on receipt is not filled in or set to NO.
100 Fix -
101 Following changes have been -
102 (i) Changed the cursor - c_receipt_cenvat_dtl.
103 Added in Input parameter - cp_organization_type.
104 Select for the column online_claim_flag is changed from online_claim_flag to
105 decode(cp_organization_type, 'M', online_claim_flag, jai_constants.yes)
106 (ii) While opening the cursor c_receipt_cenvat_dtl, passed the additional parameter
107 r_trx.organization_type
108 (iii) In the If condition after lv_statement_id := 27, added the condition -
109 and r_trx.organization_type = 'M'
110
111 6. 17/02/2005 Vijay Shankar for Bug#4171469, Version: 115.5
112 changes are made in process_iso_transaction Function as given below to return NOT TO PROCESS ISO if it is
113 a trading to trading and both the orgs have excise_in_rg23d flag as 'Y'
114
115
116 7 23/02/2005 Vijay Shankar for Bug#4179823, FileVersion:115.6
117 Modified an IF condition in validate_transaction procedure to allow FGIN items in case of RMA Receipts.
118 Previously it is allowing for ISO receipts only incase of FGIN items which is wrong
119
120 8 28/02/2005 Vijay Shankar for Bug#4208224,4215402 FileVersion:115.7
121
122 Bug#4208224
123 The concept of commit interval is giving FETCH OUT OF SEQUENCE Error as we are using FOR UPDATE OF clause
124 for main cursors after RECEIPTS DEPLUG. So, the commit interval concept is removed with this bugfix
125
126 Bug#4215402
127 Signature of the function get_accrue_on_receipt is modified to accept po_line_location_id also, because there
128 can be cases where a call to this procedure can pass a null value for po_distribution_id and thus returns a
129 wrong value to caller. this happens in case of receiving of non inventory items.
130 So a new parameter is added, which is used to pick the accrue_on_receipt_flag from po_line_locations_all table
131 if po_distribution_id is null
132
133 9 19/03/2005 Vijay Shankar for Bug#4250236(4245089). FileVersion: 115.8
134 .added two new parameter in validate_transaction to hold the process_vat flag and message.
135 .made a call to jai_rcv_rgm_claims_pkg.insert_rcv_lines in transaction preprocessor
136 .made a call to jai_rcv_rgm_claims_pkg.process_vat based on vat flag. this will do required processing
137 before vat claim happens
138 .added required validation in VALIDATE_TRANSACTION for vat processing and set correct values to process_vat_status
139 and related message
140 .added process_vat_status filter in main cursor c_get_transactions to fetch unprocessed VAT transactions
141
142 10 25/03/2005 Vijay Shankar for Bug#4250171. Version: 115.9
143 Following changes are made to make VAT Functionality work for OPM Receipts
144 .transaction_preprocessor is not invoked
145 .started updating ja_in_rcv_transaction which is not done in previous version of this object
146 .location_id logic execution is stopped if it is OPM Receipt as there might have been already a value
147 for this column in the record being processed
148 .process_status and cenvat_rg_flag variables are made 'X' in validate transaction if OPM RECEIPT
149
150 11 01/04/2005 Vijay Shankar for Bug#4278511. Version:115.10
151 Incase of ISO receipts, location_id has to be derived from SUBINVENTORY attached to the transaction if present, otherwise
152 we need to fetch location of RCV_TRANSACTONS. Code is modified in populate_details procedure
153
154 12 12/04/2005 Harshita for Bug#4300708. Version:116.0 (115.11)
155 When a new receipt gets created, it takes some time for the RTP concurrent to complete and the receipt to
156 get generated. Meanwhile, the customer is clicking on the 'NEW' button and proceeding with
157 the creation of a new receipt.
158 Thus accounting entries for these receipt would not be generated as the concurrent
159 'India - Receiving Transactions Processor' does not get fired. The India - RTP concurrent currently fires
160 only after the receipt gets generated in the Receipts Localized screen and the user either closes the form
161 or clicks on the 'NEW' button.
162 To overcome this issue, The concurrent 'India - Receiving Transactions Processor' has been
163 scheduled. The parameter of the concurrent 'P_CALLED_FROM' has been made visible and
164 defaulted to 'JAINPORE'. The concurrent has been updated to account all the receipts at
165 the organization level when it is called from JAINPORE and the shipemnt_header_id is null.
166
167 13 10/05/2005 Vijay Shankar for Bug#4346453. Version: 116.1
168 Code is modified due to the Impact of Receiving Transactions DFF Elimination
169 Code added to implement the functionality of Tax Invoice Generation by grouping the RTV's based
170 on Orgn, Loc, Vendor and site. New procedure process_rtv is written for this functionality and
171 linked to JAITIGRTV concurrent
172
173 * High Dependancy for future Versions of this object *
174
175 14 19/05/2005 rallamse for Bug#4336482, Version 116.1
176 For SEED there is a change in concurrent "JAINRVCTP" to use FND_STANDARD_DATE with STANDARD_DATE format
177 Procedure ja_in_rg_rounding_pkg.do_rounding signature modified by converting p_transaction_from, p_transaction_to
178 of DATE datatype to pv_transaction_from, pv_transaction_to of varchar2 datatype.
179 The varchar2 values are converted to DATE fromat using fnd_date.canonical_to_date function.
180
181 15 08-Jun-2005 File Version 116.3. Object is Modified to refer to New DB Entity names in place of Old DB Entity Names
182 as required for CASE COMPLAINCE.
183
184 16. 13-Jun-2005 Ramananda for bug#4428980. File Version: 116.4
185 Removal of SQL LITERALs is done
186
187 17. 17-Jul-2005 Sanjikum for Bug#4495135, File Version 117.1
188 Following changes are done in procedure - populate_details
189 1) Added the variable - ln_tax_apportion_factor and populated the same using - get_apportion_factor(p_transaction_id)
190 2) Added the call to jai_rcv_transactions_pkg.update_row to update jai_rcv_transactions.tax_apportion_factor
191
192 18. 27/07/2005 Ramananda for Bug#4516577, Version 120.2
193 Problem
194 -------
195 ISO Accounting Entries from Trading to Excise bonded inventory are not generated in case of following Scenarios
196 1. Trading organization to Trading Organization (only Source organizations with the 'Excise in RG23D' setup).
197 2. Trading organization to Manufacturing Organization (Source Organization with the 'Excise in RG23D' setup).
198
199 Fix
200 ---
201 In the function - process_iso_transaction, made the following changes
202 1. In the If condition -
203 " IF r_src_org.excise_in_rg23d <> 'Y' OR r_dest_org.excise_in_rg23d <> 'Y' THEN"
204 removed the second part of the OR "r_dest_org.excise_in_rg23d <> 'Y'"
205
206 2. In the If condition - "ELSIF r_dest_org.manufacturing = 'Y' THEN"
207 Added the If condition - "IF r_src_org.excise_in_rg23d <> 'Y' THEN"
208 for the statement - lb_process_iso_transaction := false;
209
210 Dependency Due to This Bug
211 --------------------------
212 jai_rcv_rcv_rtv.plb (120.3)
213 jai_om_rg.plb (120.2)
214
215 16. 01-Aug-2005 Ramananda for Bug#4519697, File Version 120.3
216 1) In the procedure - process_transaction, moved the cursor - c_trx, before calling validate_transaction
217 2) In the procedure - process_transaction, Variables - lv_process_vat_flag, lv_process_vat_message are
218 assigned value before calling validate_transaction
219 3) In the procedure - validate_transaction, added the following condition -
220 "if p_process_vat_flag = jai_constants.successful THEN
221 goto end_of_vat_validation;
222 end if;"
223
224 Column process_vat_flag changed to process_vat_status (jai_rcv_transactions).
225 This issue is identified while compiling the object as a part of this fix.
226
227 Dependency due to this Bug
228 --------------------------
229 jai_rcv_rt_t1.sql (120.2)
230 jai_rcv_tax.plb (120.3)
231
232 17. 28/06/2005 Ramananda for Bug#4519719, File Version 120.4
233 Issue : A call to jai_rcv_rgm_claims_pkg.insert_rcv_lines is made even though there
234 do not exist any VAT type of taxes in the receipt.
235 Fis : Added a condition to check if VAT type of taxes exist in the receipt
236 before the call to jai_rcv_rgm_claims_pkg.insert_rcv_lines
237
238 Dependency due to this bug:-
239 jai_rcv_rgm_clm.plb (120.2)
240
241 18. 01/09/2005 Bug4586752. Added by Lakshmi gopalsami Version 120.5
242 Assigned the value of location_id in populate_details
243 and used for populating jai_rcv_transactions
244 Dependency (Functional)
245 ----------------------
246 JAIRGMCT.fmb 120.2
247 JAIRGMPT.fmb 120.3
248 JAIRGMSG.fmb 120.2
249 jai_rcv_trx_prc.plb 120.5
250
251 19. 02-Sep-2005 Bug4589354. Added by Lakshmi Gopalsami version 120.3
252 Commented the following condition.
253 OR (r_base_trx.source_document_code = 'REQ' and
254
255 Dependencies :
256 jai_rcv_trx_prc.plb 120.6
257 jai_rcv_rgm_clm.plb 120.3
258
259 20. 26-May-2006 Sanjikum for Bug#4929410, File Version 120.7
260 1) Changes done related to performance
261
262 21. 17-Jul-2006 Aiyer for the bug 5378630 , File Version 120.6
263 Issue:-
264 India Receiving transaction processor fails during validation phase for RMA
265 type of transactions.
266
267 Fix:-
268 Converting the reference of RMA TYPE "FG RETURN" into "GOODS RETURN" as FG return is not as per the abbreviation
269 standard
270
271 22. 30-OCT-2006 SACSETHI for bug 5228046, File version 120.2
272 Forward porting the change in 11i bug 5365523 (Additional CVD Enhancement).
273 This bug has datamodel and spec changes.
274
275 23. 13-FEB-2007 Vkaranam for bug 4636397,File Version 120.14
276 Forward porting the change in 11i bug 4626571 (India Localization- Unordered Receitps-50% Gets Hit Without Claiming Modvat).
277 Changes are done in get_ancestor_id function.
278
279 24. 16-Feb-2007 srjayara for bug 5064235 -- forward porting for bug# 5054114 in 11i
280 File version 120.15
281 Issue : The subinventory's location_id is not getting populated in ja_in_rcv_transactions for
282 'RECEIVE' line
283 Fix : The subinventory's location_id will be populated in ja_in_rcv_transactions for 'RECEIVE' line
284 by fetching the subinventory from rcv_transactions 'DELIVER' line.
285 25. 21-Feb-2007 CSahoo for BUG 5344225, File Version 120.16
286 Forward porting of 11i BUG 5343848
287 Added two input parameters p_request_id,p_group_id to the procedure process_batch.
288 Added a parameter request_id Default null
289 If the request_id is not null
290 Called the fnd_concurrent.wait_for_request_id .
291 This call will wait till the RVCTP concurrent is completed.
292
293 Added a parameter group_id Default null
294 If the group_id is not null
295 Run a infinite loop till the data in rcv_transactions_interface table
296 is purged for the particular group_id.
297
298 The rest of the code in the concurrent is processed only after
299 the data is purged.
300
301 16/04/2007 Kunkumar for bugno 5989740
302 forward porting to R12 filename:ja_in_receipt_transactions_pkg.sql version 115.42.6107.2
303
304
305 26. 10/05/2007 bgowrava for forward porting Bug#5756676, 11i bug#5747013. File Version :120.18
306 Issue : QTY REGISTER SHOULD BE UPDATED ON RECEIVE DEPENDING ON SETUP
307 Fix : Changes are made to hit the Qty register independent of the Amount register.
308 This would happen in following cases:
309 i) In case of deferred claim, the Qty register would be hit at RECEIVE or MATCH.
310 Previously it was at CLAIM only. Decision to hit the Qty register at RECEIVE
311 or CLAIM would be made depending on Setup.
312 ii) For an excisable item, if there are no taxes attached then the Qty register
313 would be hit. Previously the Qty register was hit at the time of CLAIM and
314 CLAIM can be done only if there are taxes.
315
316 A spec variable lv_online_qty_flag is set depending on the above conditions.
317 The changes are made in validate_transaction procedure for this.
318 Changes are made in process_transaction to hit the Qty register if lv_online_qty_flag
319 is set to Y.
320
321 The changes are made on top of 115.34 as 115.35 is obsolete.
322
323 Dependency Due to this Bug : Yes.
324
325 27. 10/05/2007 bgowrava for forward porting Bug#5756676, 11i bug#5747013. File Version :120.18
326 Issue : QTY REGISTER SHOULD BE UPDATED ON RECEIVE DEPENDING ON SETUP
327 Fix : The variable which has the count of excise taxes was used before
328 the cursor was used to fetch the value and so the count is always coming as zero.
329 Now moved the cursor position.
330
331 28. 10/05/2007 bgowrava for forward porting Bug#5756676, 11i bug#5747013. File Version :120.18
332 Issue : QTY REGISTER SHOULD BE UPDATED ON RECEIVE DEPENDING ON SETUP
333 Fix : If the organization , location combination does not have any setup for
334 "Update Qty Register Event" then we should get the setup value from
335 NULL site. To do this we were checking if cur_qty_setup%NOTFOUND for location id.
336 This would never be true as the cursor would fetch a record. Now modified this
337 to lv_qty_upd_event IS NULL. If this is NULL then we will fetch it from NULL site.
338
339
340 29. 14-05-2007 ssawant for bug 5879769, File Version 120.19
341 Objects was not compiling. so changes are done to make it compiling.
342
343 30. 04/06/2007 sacsethi for bug 6109941 File Version 120.20
344
345 CODE REVIEW COMMENTS FOR ENHANCEMENTS
346
347 Problem- Code related to cenvat amount was wrongly commented
348
349 31. 21/06/2007 rchandan for bug#6109941, File Version 120.21
350
351 Issue: Code review for enhancements(ER bug#5747013)
352 Fix: removed the decalaration of lv_online_qty_flag as it is already decalred in the spec and
353 added a nvl check in an if condition.
354
355 32. 01-08-2007 rchandan for bug#6030615 , Version 120.23
356 Issue : Inter org Forward porting
357
358 33. 05-JAN-2009 Bug 7662347 File version 120.11.12000000.4 / 120.25.12010000.2 / 120.26
359 Issue : RG23 Part I register is not hit during RTV when there are no excise taxes.
360 Fix : Changed the code so that lv_online_qty_flag will be Y for the RTV transactions
361 also, for receipts which do not have excise taxes. Also added a variable
362 lv_qty_register_entry_type in the process_transaction procedure so that the
363 quantity register will be hit with proper sign.
364
365 Dependancy:
366 -----------
367
368 ----------------------------------------------------------------------------------------------------------------------------*/
369
370 /* following procedure added by Vijay Shankar for Bug#3940588 */
371 PROCEDURE transaction_preprocessor(
372 p_shipment_line_id IN NUMBER,
373 p_transaction_id IN NUMBER,
374 p_process_status IN OUT NOCOPY VARCHAR2,
375 p_process_message IN OUT NOCOPY VARCHAR2,
376 p_simulate_flag IN VARCHAR2 --File.Sql.35 Cbabu DEFAULT 'N'
377 ) IS
378
379 --added by ssawant
380 CURSOR cur_qty_setup( cp_organization_id NUMBER,cp_location_id NUMBER)
381 IS
382 SELECT quantity_register_update_event
383 FROM JAI_CMN_INVENTORY_ORGS
384 WHERE organization_id = cp_organization_id
385 AND location_id = cp_location_id ;
386
387
388 --added by ssawant
389 CURSOR c_base_trx(cp_transaction_id IN NUMBER) IS
390 SELECT shipment_header_id, shipment_line_id, transaction_type, quantity, unit_of_measure, uom_code,
391 parent_transaction_id, organization_id, location_id, subinventory, currency_conversion_rate,
392 attribute_category attr_cat, nvl(attribute5, 'XX') rma_type, nvl(attribute4, 'N') generate_excise_invoice
393 , routing_header_id -- porting of Bug#3949109 (3927371)
394 , attribute3 online_claim_flag, source_document_code, po_header_id -- Vijay Shankar for Bug#3940588
395 , po_line_location_id
396 FROM rcv_transactions
397 WHERE transaction_id = cp_transaction_id;
398
399 r_trx c_trx%ROWTYPE;
400 r_base_trx c_base_trx%ROWTYPE;
401 r_tax jai_rcv_excise_processing_pkg.tax_breakup;
402
403 ln_cenvat_amount NUMBER;
404 ln_other_cenvat_amt NUMBER;
405 lv_breakup_type VARCHAR2(10);
406
407 -- Bug 5581319. Added by Lakshmi Gopalsami
408 -- Increased the size of lv_localpath from 100 to 2000.
409 --
410 lv_localpath jai_rcv_transactions.codepath%TYPE; --VARCHAR2(2000); --File.Sql.35 Cbabu := '';
411 ln_dup_chk NUMBER; --File.Sql.35 Cbabu := 0;
412
413 lv_process_status VARCHAR2(2);
414 lv_process_message VARCHAR2(1000);
415
416
417
418
419
420 -- Vijay Shankar for Bug#4346453. RCV DFF Elim. Enh.
421 r_excise_invoice_no c_excise_invoice_no%ROWTYPE;
422
423 /* Added by Ramananda for bug#4407165 */
424 lv_object_name CONSTANT VARCHAR2(61) := 'jai_rcv_trx_processing_pkg.transaction_preprocessor';
425
426 -- added, Ramananda for bug # 4519719
427 CURSOR c_vat_exists(cp_shipment_line_id NUMBER )
428 IS
429 SELECT 1
430 FROM JAI_RCV_LINE_TAXES a , jai_regime_tax_types_v b
431 WHERE shipment_line_id = cp_shipment_line_id AND
432 b.regime_code= jai_constants.vat_regime
433 and b.tax_type = a.tax_type ;
434
435 ln_vat_exists NUMBER ;
436 -- ended, Ramananda for bug # 4519719
437
438 BEGIN
439
440 lv_localpath := '';
441 ln_dup_chk := 0;
442
443 OPEN c_trx(p_transaction_id);
444 FETCH c_trx INTO r_trx;
445 CLOSE c_trx;
446
447 IF r_trx.transaction_type IN ('RECEIVE', 'MATCH') THEN
448
449 select count(1) into ln_dup_chk
450 from JAI_RCV_CENVAT_CLAIMS
451 where transaction_id = r_trx.transaction_id;
452
453 IF ln_dup_chk > 0 THEN
454 return;
455 END IF;
456
457 IF r_trx.organization_type = 'T' THEN
458 lv_breakup_type := 'RG23D';
459 END IF;
460
461 jai_rcv_excise_processing_pkg.get_tax_amount_breakup(
462 p_shipment_line_id => r_trx.shipment_line_id,
463 p_transaction_id => r_trx.transaction_id,
464 p_curr_conv_rate => r_trx.currency_conversion_rate,
465 pr_tax => r_tax,
466 p_breakup_type => lv_breakup_type,
467 p_codepath => lv_localpath
468 );
469
470 -- Uncommented for bug 6109941
471 ln_cenvat_amount := r_tax.basic_excise +
472 r_tax.addl_excise +
473 r_tax.other_excise +
474 r_tax.cvd +
475 r_tax.addl_cvd ; -- Modified by SACSETHI Bug# 5228046
476 -- Forward porting the change in 11i bug 5365523
477 -- (Additional CVD Enhancement) as part of the R12 bug 5228046
478
479 ln_other_cenvat_amt := r_tax.excise_edu_cess +
480 r_tax.cvd_edu_cess+
481 r_tax.sh_exc_edu_cess +
482 r_tax.sh_cvd_edu_cess; --Added by kunkumar for forward porting bug#5907436 to R12
483
484
485 OPEN c_base_trx(p_transaction_id);
486 FETCH c_base_trx INTO r_base_trx;
487 CLOSE c_base_trx;
488
489 OPEN c_excise_invoice_no(r_base_trx.shipment_line_id);
490 FETCH c_excise_invoice_no INTO r_excise_invoice_no;
491 CLOSE c_excise_invoice_no;
492
493 -- even if there is no excise, then the data goes into this table. previous code doesnt insert data into this table
494 -- if excise taxes are not present for a line
495 INSERT INTO JAI_RCV_CENVAT_CLAIMS(
496 transaction_id, shipment_line_id, cenvat_amount, cenvat_claimed_ptg, cenvat_sequence,
497 other_cenvat_amt, other_cenvat_claimed_amt, creation_date, created_by, last_update_date,
498 last_updated_by, last_update_login,
499 online_claim_flag,
500 vendor_changed_flag
501 ) VALUES (
502 r_trx.transaction_id, r_trx.shipment_line_id, ln_cenvat_amount, 0, 0,
503 ln_other_cenvat_amt, 0, sysdate, fnd_global.user_id, sysdate,
504 fnd_global.user_id, fnd_global.login_id,
505 r_excise_invoice_no.online_claim_flag, -- Vijay Shankar for Bug#4346453. RCV DFF Elim. Enh. nvl(r_base_trx.online_claim_flag, jai_constants.no),
506 jai_constants.no
507 );
508
509 -- Vijay Shankar for Bug#3940588 EDUCATION CESS
510 IF r_tax.excise_edu_cess <> 0 THEN
511 jai_rcv_excise_processing_pkg.other_cenvat_rg_recording(
512 p_source_register => jai_constants.reg_receipt_cenvat,
513 p_source_register_id => r_trx.transaction_id,
514 p_tax_type => jai_constants.tax_type_exc_edu_cess,
515 p_credit => r_tax.excise_edu_cess,
516 p_debit => null,
517 p_process_status => p_process_status,
518 p_process_message => p_process_message
519 );
520 END IF;
521
522 IF p_process_status = 'E' THEN
523 RETURN;
524 END IF;
525 /*Added by kunkumar for forward porting bug#5989740, start*/
526 IF r_tax.sh_exc_edu_cess <> 0 THEN
527 jai_rcv_excise_processing_pkg.other_cenvat_rg_recording(
528 p_source_register => jai_constants.reg_receipt_cenvat,
529 p_source_register_id => r_trx.transaction_id,
530 p_tax_type => jai_constants.tax_type_sh_exc_edu_cess,
531 p_credit => r_tax.sh_exc_edu_cess,
532 p_debit => null,
533 p_process_status => p_process_status,
534 p_process_message => p_process_message
535 );
536 END IF;
537
538 IF p_process_status = 'E' THEN
539 RETURN;
540 END IF;
541
542 IF r_tax.sh_cvd_edu_cess <> 0 THEN
543 jai_rcv_excise_processing_pkg.other_cenvat_rg_recording(
544 p_source_register => jai_constants.reg_receipt_cenvat,
545 p_source_register_id => r_trx.transaction_id,
546 p_tax_type => jai_constants.tax_type_sh_cvd_edu_cess,
547 p_credit => r_tax.sh_cvd_edu_cess,
548 p_debit => null,
549 p_process_status => p_process_status,
550 p_process_message => p_process_message
551 );
552 END IF;
553
554 IF p_process_status = 'E' THEN
555 RETURN;
556 END IF;
557 /*Added by kunkumar for 5989740, end*/
558
559
560
561 IF r_tax.cvd_edu_cess <> 0 THEN
562 jai_rcv_excise_processing_pkg.other_cenvat_rg_recording(
563 p_source_register => jai_constants.reg_receipt_cenvat,
564 p_source_register_id => r_trx.transaction_id,
565 p_tax_type => jai_constants.tax_type_cvd_edu_cess,
566 p_credit => r_tax.cvd_edu_cess,
567 p_debit => null,
568 p_process_status => p_process_status,
569 p_process_message => p_process_message
570 );
571 END IF;
572
573 IF p_process_status = 'E' THEN
574 RETURN;
575 END IF;
576
577 -- added, Ramananda for bug # 4519719
578 OPEN c_vat_exists(p_shipment_line_id) ;
579 FETCH c_vat_exists INTO ln_vat_exists ;
580 CLOSE c_vat_exists ;
581
582 IF ln_vat_exists = 1 THEN
583
584 /* Call added for VAT Implementation. Vijay Shankar for Bug#4250236(4245089) */
585 jai_rcv_rgm_claims_pkg.insert_rcv_lines(
586 p_shipment_header_id => null,
587 p_shipment_line_id => p_shipment_line_id,
588 p_transaction_id => p_transaction_id,
589 p_regime_code => jai_constants.vat_regime
590 , p_process_status => p_process_status,
591 p_process_message => p_process_message,
592 p_simulate_flag => p_simulate_flag
593 );
594
595 IF p_process_status <> jai_constants.successful THEN
596 p_process_status := 'E';
597 RETURN;
598 END IF;
599 END IF ;
600 -- ended, Ramananda for bug # 4519719
601 /*
602 Did not handle UOM Conversion between the Transaction Quantities
603
604 JA_IN_RCV_CENVAT_PKG.insert_row(
605 p_shipment_line_id => p_shipment_line_id ,
606 p_tax_transaction_id => r_trx.tax_transaction_id,
607 p_tax_qty => r_trx.quantity,
608 p_tax_qty_uom_code => r_trx.uom_code,
609 p_receipt_num => r_trx.receipt_num,
610 p_receipt_date => r_trx.transaction_date,
611 p_excise_invoice_no => r_trx.excise_invoice_no,
612 p_excise_invoice_date => r_trx.excise_invoice_date,
613 p_basic_excise => ln_basic,
614 p_addl_excise => ln_addl,
615 p_cvd => ln_cvd,
616 p_other_excise => ln_other,
617 p_cenvat_claimed_ptg => 0,
618 p_cenvat_claimed_amt => 0,
619 p_claimable_flag => null,
620 p_receive_qty => 0,
621 p_receive_corr_qty => null,
622 p_deliver_bonded_qty => null,
623 p_deliver_nonbonded_qty => null,
624 p_deliver_corr_bonded_qty => null,
625 p_deliver_corr_nonbonded_qty => null,
626 p_rtr_bonded_qty => null,
627 p_rtr_nonbonded_qty => null,
628 p_rtv_qty => null,
629 p_rtv_corr_qty => null,
630 p_excise_vendor_id => null,
631 p_excise_vendor_site_id => null,
632 p_called_from => 'jai_rcv_trx_processing_pkg.transaction_preprocessor',
633 p_simulate_flag => p_simulate_flag,
634 p_process_status => p_process_status,
635 p_process_message => p_process_status
636 );
637 */
638
639 END IF;
640
641 /* Added by Ramananda for bug#4407165 */
642 EXCEPTION
643 WHEN OTHERS THEN
644 FND_MESSAGE.SET_NAME('JA','JAI_EXCEPTION_OCCURED');
645 FND_MESSAGE.SET_TOKEN('JAI_PROCESS_MSG', lv_object_name ||'. Err:'||sqlerrm );
646 app_exception.raise_exception;
647
648 END transaction_preprocessor;
649
650 /* following procedure added by Vijay Shankar for Bug#3940588 */
651 PROCEDURE transaction_postprocessor(
652 p_shipment_line_id IN NUMBER,
653 p_transaction_id IN NUMBER,
654 p_process_status IN OUT NOCOPY VARCHAR2,
655 p_process_message IN OUT NOCOPY VARCHAR2,
656 p_simulate_flag IN VARCHAR2, --File.Sql.35 Cbabu DEFAULT 'N',
657 p_codepath IN OUT NOCOPY VARCHAR2
658 ) IS
659
660 r_trx c_trx%ROWTYPE;
661 r_base_trx c_base_trx%ROWTYPE; --added by rchandan for Bug#6030615
662 ln_cenvat_amount NUMBER;
663 ln_cenvat_claimed_ptg NUMBER;
664
665 /* Added by Ramananda for bug#4407165 */
666 lv_object_name CONSTANT VARCHAR2(61) := 'jai_rcv_trx_processing_pkg.transaction_postprocessor';
667
668 BEGIN
669
670
671 FND_FILE.put_line(FND_FILE.log, '^Trx Post Processor');
672
673 p_codepath := jai_general_pkg.plot_codepath(1, p_codepath, 'receipt_transactions_pkg.trx_post_proc', 'START');
674
675 OPEN c_trx(p_transaction_id);
676 FETCH c_trx INTO r_trx;
677 CLOSE c_trx;
678
679 IF r_trx.transaction_type IN ('RECEIVE','MATCH') THEN
680
681 p_codepath := jai_general_pkg.plot_codepath(2, p_codepath);
682 -- Cenvat Claim is not required for the transaction if the following if condition is satisfied
683 IF r_trx.cenvat_rg_status NOT IN ('Y', 'P', 'E') THEN
684
685 p_codepath := jai_general_pkg.plot_codepath(3, p_codepath);
686 IF r_trx.item_class IN ('FGIN', 'FGEX') THEN
687 p_codepath := jai_general_pkg.plot_codepath(4, p_codepath);
688 ln_cenvat_amount := 0;
689 ln_cenvat_claimed_ptg := 0;
690 END IF;
691
692 UPDATE JAI_RCV_CENVAT_CLAIMS
693 SET cenvat_amount = nvl(ln_cenvat_amount, cenvat_amount),
694 other_cenvat_amt = nvl(ln_cenvat_amount, other_cenvat_amt),
695 cenvat_claimed_ptg = nvl(ln_cenvat_claimed_ptg, 100),
696 cenvat_sequence = nvl(cenvat_sequence ,0) + 1, -- Vijay Shankar for Bug#4346453. RCV DFF Elim. Enh.
697 last_update_date = sysdate,
698 last_update_login = fnd_global.login_id,
699 last_updated_by = fnd_global.user_id
700 WHERE transaction_id = r_trx.tax_transaction_id;
701 END IF;
702
703 /* Vijay Shankar for Bug#4346453. RCV DFF Elim. Enh.. Following code is a replacement for removal of ja_in_create_rcv_57f4_trg trigger
704 as part of RTV DFF Elimination
705 elsif r_trx.transaction_type = 'RETURN TO VENDOR' then
706
707 if r_trx.attribute1 = INV_GEN_STATUS_INV_GENERATED then
708 jai_po_osp_pkg.create_rcv_57f4(
709 p_transaction_id => p_transaction_id,
710 p_process_status => p_process_status,
711 p_process_message => p_process_message
712 );
713
714 if p_process_status in (jai_constants.unexpected_error) then
715 p_process_status := 'E';
716 return;
717 end if;
718 end if;
719 */
720 end if;
721 /*
722 IF r_trx.transaction_type = 'DELIVER' THEN
723
724 -- meaning non bonded
725 IF r_trx.loc_subinv_type = 'N' THEN
726 UPDATE JAI_RCV_CENVAT_CLAIMS
727 SET non_bonded_delivery_flag = 'Y',
728 cenvat_claimed_ptg = 100
729 --last_update_date = sysdate,
730 --last_updated_by = fnd_global.user_id
731 WHERE transaction_id = p_transaction_id;
732 END IF;
733
734 END IF;
735 */
736 /*
737 --*** Did not handle UOM Conversion between the Transaction Quantities
738 JA_IN_RCV_CENVAT_PKG.update_quantities(
739 p_shipment_line_id => p_shipment_line_id,
740 p_tax_transaction_id => r_trx.tax_transaction_id,
741 p_transaction_type => r_trx.transaction_type,
742 p_parent_transaction_type => r_trx.parent_transaction_type,
743 p_subinventory_type => r_trx.loc_subinventory_type,
744 p_transaction_quantity => r_trx.quantity,
745 p_transaction_uom_code => r_trx.uom_code,
746 p_called_from => 'jai_rcv_trx_processing_pkg.transaction_preprocessor',
747 p_simulate_flag => p_simulate_flag,
748 p_process_status => p_process_status,
749 p_process_message => p_process_message
750 );
751 */
752 p_codepath := jai_general_pkg.plot_codepath(9, p_codepath, 'receipt_transactions_pkg.trx_post_proc', 'END');
753
754 /* Added by Ramananda for bug#4407165 */
755 EXCEPTION
756 WHEN OTHERS THEN
757 FND_MESSAGE.SET_NAME('JA','JAI_EXCEPTION_OCCURED');
758 FND_MESSAGE.SET_TOKEN('JAI_PROCESS_MSG', lv_object_name ||'. Err:'||sqlerrm );
759 app_exception.raise_exception;
760
761 END transaction_postprocessor;
762
763 /*============= Check for outside processing transaction ================== */
764 /* Bug 5365346. Added by Lakshmi Gopalsami */
765 FUNCTION Check_57F4_transaction( p_transaction_id in number )
766 RETURN varchar2 is
767 CURSOR c_check_57F4_for_po IS
768 SELECT jpol.line_id
769 FROM jai_po_osp_lines jpol,
770 jai_po_osp_hdrs jpoh
771 WHERE jpol.form_id = jpoh.form_id
772 AND (jpoh.po_header_id, jpol.po_line_id ) IN
773 ( SELECT po_header_id, po_line_id
774 FROM rcv_transactions
775 WHERE transaction_id = p_transaction_id
776 );
777 ln_line_id NUMBER ;
778 ln_ret_value VARCHAR2(3);
779
780
781 BEGIN
782 ln_line_id := 0;
783 ln_ret_value := 'NO';
784 OPEN c_check_57F4_for_po;
785 FETCH c_check_57F4_for_po INTO ln_line_id;
786 IF c_check_57F4_for_po%NOTFOUND THEN
787 ln_ret_value := 'NO';
788 fnd_file.put_line(FND_FILE.LOG, 'Check_57F4_transaction->57F4 challan doesnot exist ' );
789 ELSE
790 ln_ret_value := 'YES';
791 fnd_file.put_line(FND_FILE.LOG, 'Check_57F4_transaction->57F4 challan exists ' );
792 END IF ;
793 CLOSE c_check_57F4_for_po;
794 RETURN ln_ret_value;
795 END check_57f4_transaction;
796
797 /*============================== DEFERRED CLAIM Main Procedure ==============================*/
798 PROCEDURE process_deferred_cenvat_claim(
799 p_batch_id IN NUMBER,
800 p_called_from IN VARCHAR2,
801 p_simulate_flag IN VARCHAR2, --File.Sql.35 Cbabu DEFAULT 'N',
802 p_process_flag OUT NOCOPY VARCHAR2,
803 p_process_message OUT NOCOPY VARCHAR2
804 ) IS
805
806 r_trx c_trx%ROWTYPE;
807 lv_process_flag JAI_RCV_TRANSACTIONS.PROCESS_STATUS%TYPE;
808 lv_process_message JAI_RCV_TRANSACTIONS.process_message%TYPE;
809 lv_cenvat_rg_flag JAI_RCV_TRANSACTIONS.CENVAT_RG_STATUS%TYPE;
810 lv_cenvat_rg_message JAI_RCV_TRANSACTIONS.cenvat_rg_message%TYPE;
811 lv_common_err_mesg VARCHAR2(1000);
812
813 lv_codepath VARCHAR2(1996);
814 ln_processed_cnt NUMBER(10); --File.Sql.35 Cbabu := 0;
815 ln_errored_cnt NUMBER(10); --File.Sql.35 Cbabu := 0;
816
817 r_tax jai_rcv_excise_processing_pkg.tax_breakup;
818 lv_breakup_type VARCHAR2(15);
819
820 lv_ttype_correct JAI_RCV_TRANSACTIONS.transaction_type%type ;
821 lv_ttype_deliver JAI_RCV_TRANSACTIONS.transaction_type%type ;
822 lv_type_rtr JAI_RCV_TRANSACTIONS.transaction_type%type ;
823
824 CURSOR c_trxs_for_unclaim(cp_shipment_line_id IN NUMBER, cp_tax_transaction_id IN NUMBER) IS
825 select transaction_id, shipment_line_id, organization_type
826 from JAI_RCV_TRANSACTIONS
827 where (transaction_type IN (lv_ttype_deliver, lv_type_rtr) --'DELIVER', 'RETURN TO RECEIVING')
828 or (transaction_type= lv_ttype_correct and parent_transaction_type = lv_ttype_deliver) --'CORRECT' , 'DELIVER'
829 )
830 and tax_transaction_id = cp_tax_transaction_id
831 and shipment_line_id = cp_shipment_line_id
832 -- and cenvat_rg_flag <> ('P','X') -- pending for parent receive claim
833 -- and cenvat_rg_flag IN ('N', 'X', 'P', 'XT')
834 and attribute1 = jai_rcv_deliver_rtr_pkg.cenvat_costed_flag
835 and (attribute2 IS NULL or attribute2 <> jai_constants.yes)
836 FOR UPDATE OF cenvat_rg_status, cenvat_rg_message
837 order by shipment_line_id, transaction_id;
838
839 CURSOR c_trxs_to_be_claimed IS
840 SELECT * FROM JAI_RCV_CENVAT_CLAIM_T
841 WHERE batch_identifier = p_batch_id
842 AND error_flag IS NULL
843 FOR UPDATE OF transaction_id
844 ORDER BY transaction_id;
845
846 CURSOR c_receipt_cenvat_dtl(cp_transaction_id IN NUMBER) IS
847 SELECT cenvat_claimed_ptg, quantity_for_2nd_claim
848 FROM JAI_RCV_CENVAT_CLAIMS
849 WHERE transaction_id = cp_transaction_id;
850
851 r_receipt_cenvat_dtl c_receipt_cenvat_dtl%ROWTYPE;
852 lv_2nd_claim_flag VARCHAR2(1);
853 ln_qty_to_claim JAI_RCV_CENVAT_CLAIMS.quantity_for_2nd_claim%TYPE;
854 lv_process_special_reason VARCHAR2(50);
855 ln_process_special_amount NUMBER;
856 -- lv_codepath JAI_RCV_TRANSACTIONS.codepath%TYPE;
857 -- lv_process_flag JAI_RCV_TRANSACTIONS.PROCESS_STATUS%TYPE;
858 -- lv_process_message JAI_RCV_TRANSACTIONS.process_message%TYPE;
859
860 BEGIN
861
862 ln_processed_cnt := 0;
863 ln_errored_cnt := 0;
864
865 -- Unclaim means, Receipt Line is not eligible for Claim. So we need to set all related flags as non recoverable and
866 -- and reverse the cenvat entries if any passed against the transactions
867
868 FOR temp_rec IN c_trxs_to_be_claimed LOOP
869
870 ln_processed_cnt := ln_processed_cnt + 1;
871 lv_common_err_mesg := null;
872 lv_cenvat_rg_flag := null;
873 ln_qty_to_claim := null;
874 lv_2nd_claim_flag := null;
875 r_trx := null;
876 r_tax := null;
877 lv_codepath := '';
878 lv_process_special_reason := null;
879
880 OPEN c_trx(temp_rec.transaction_id);
881 FETCH c_trx INTO r_trx;
882 CLOSE c_trx;
883
884 lv_process_flag := r_trx.process_status;
885 lv_process_message := r_trx.process_message;
886 lv_cenvat_rg_flag := r_trx.cenvat_rg_status;
887 lv_cenvat_rg_message := r_trx.cenvat_rg_message;
888
889 lv_codepath := jai_general_pkg.plot_codepath(1, lv_codepath, 'receipt_transactions_pkg.process_deferred_cenvat_claim', 'START');
890
891 SAVEPOINT start_deferred_claim;
892
893 IF lb_debug THEN
894 FND_FILE.put_line(FND_FILE.log, 'trx_id:'||temp_rec.transaction_id||', flag:'||temp_rec.process_flag);
895 END IF;
896
897 IF temp_rec.process_flag = 'M' THEN
898
899 OPEN c_receipt_cenvat_dtl(r_trx.tax_transaction_id);
900 FETCH c_receipt_cenvat_dtl INTO r_receipt_cenvat_dtl;
901 CLOSE c_receipt_cenvat_dtl;
902
903 lv_codepath := jai_general_pkg.plot_codepath(2, lv_codepath);
904
905 IF r_trx.item_class IN ('CGIN','CGEX') THEN
906
907 lv_codepath := jai_general_pkg.plot_codepath(3, lv_codepath);
908 IF r_trx.transaction_type IN ('RECEIVE','MATCH') THEN
909
910 IF r_receipt_cenvat_dtl.cenvat_claimed_ptg = 0 THEN
911 lv_codepath := jai_general_pkg.plot_codepath(4, lv_codepath);
912 lv_2nd_claim_flag := 'N';
913
914 ELSIF r_receipt_cenvat_dtl.cenvat_claimed_ptg = 100 THEN
915 lv_codepath := jai_general_pkg.plot_codepath(5, lv_codepath);
916 -- Already 100% is Claimed, some problem in code and thats why execution came till here. so process next record
917 GOTO next_record;
918
919 -- second claim case
920 ELSIF r_receipt_cenvat_dtl.cenvat_claimed_ptg < 100 THEN
921
922 lv_codepath := jai_general_pkg.plot_codepath(6, lv_codepath);
923 -- 2nd Claim should be done only to the tune of JAI_RCV_CENVAT_CLAIMS.
924 lv_2nd_claim_flag := 'Y';
925
926 ELSE
927 lv_codepath := jai_general_pkg.plot_codepath(7, lv_codepath);
928 GOTO next_record;
929 END IF;
930
931 ELSE
932
933 lv_codepath := jai_general_pkg.plot_codepath(8, lv_codepath);
934 IF r_trx.cenvat_claimed_ptg = 0 THEN
935 lv_2nd_claim_flag := 'N';
936 ELSE
937 lv_codepath := jai_general_pkg.plot_codepath(8.1, lv_codepath);
938 -- transactions other than RECEIVE should not be processed for 2nd Claim
939 GOTO next_record;
940 END IF;
941
942 END IF;
943
944 ELSE
945 lv_codepath := jai_general_pkg.plot_codepath(9, lv_codepath);
946 lv_2nd_claim_flag := 'N';
947 END IF;
948
949 IF lv_2nd_claim_flag = 'Y' THEN
950 ln_qty_to_claim := r_receipt_cenvat_dtl.quantity_for_2nd_claim;
951 lv_process_special_reason := jai_rcv_excise_processing_pkg.second_50ptg_claim;
952 END IF;
953
954 process_transaction(
955 p_transaction_id => temp_rec.transaction_id,
956 p_process_flag => lv_process_flag,
957 p_process_message => lv_process_message,
958 p_cenvat_rg_flag => lv_cenvat_rg_flag,
959 p_cenvat_rg_message => lv_cenvat_rg_message,
960 p_common_err_mesg => lv_common_err_mesg,
961 p_called_from => p_called_from,
962 p_simulate_flag => p_simulate_flag,
963 p_codepath => lv_codepath,
964 p_process_special_reason => lv_process_special_reason,
965 p_process_special_qty => ln_qty_to_claim,
966 p_excise_processing_reqd => jai_constants.yes, --File.Sql.35 Cbabu
967 p_vat_processing_reqd => jai_constants.yes --File.Sql.35 Cbabu
968 );
969
970 lv_codepath := jai_general_pkg.plot_codepath(10, lv_codepath);
971
972 -- flag value 'X' is removed from the following ELSIF conditions because it is not an ERROR Status. MYXZ
973 IF lv_common_err_mesg IS NOT NULL THEN
974 -- A common error occured. So, Whole Processing for Transaction should be stopped
975 ROLLBACK TO start_deferred_claim;
976 FND_FILE.put_line(FND_FILE.log, '*** Common Error for Transaction_id:'||r_trx.transaction_id
977 ||fnd_global.local_chr(10)||fnd_global.local_chr(9)||'- Error:'||lv_common_err_mesg
978 );
979 lv_codepath := jai_general_pkg.plot_codepath(11, lv_codepath);
980
981 ELSIF lv_process_flag IN ('E') AND lv_cenvat_rg_flag IN ('E') THEN
982 lv_codepath := jai_general_pkg.plot_codepath(12, lv_codepath);
983 FND_FILE.put_line(FND_FILE.log, '*** FLAGS ERROR *** Transaction_id:'||r_trx.transaction_id
984 ||fnd_global.local_chr(10)||fnd_global.local_chr(9)||'- ProcessErr:'||lv_process_message
985 ||fnd_global.local_chr(10)||fnd_global.local_chr(9)||'- CenvatErr:'||lv_cenvat_rg_message
986 );
987 /*dbms_output.put_line( '*** FLAGS ERROR *** Transaction_id:'||r_trx.transaction_id
988 ||fnd_global.local_chr(10)||fnd_global.local_chr(9)||'- ProcessErr:'||lv_process_message
989 ||fnd_global.local_chr(10)||fnd_global.local_chr(9)||'- CenvatErr:'||lv_cenvat_rg_message );
990 */
991 ELSIF lv_process_flag IN ('E') THEN
992 lv_codepath := jai_general_pkg.plot_codepath(13, lv_codepath);
993 FND_FILE.put_line(FND_FILE.log, '*** PROCESS ERROR *** Transaction_id:'||r_trx.transaction_id
994 ||fnd_global.local_chr(10)||fnd_global.local_chr(9)||'- ErrorMessage:'||lv_process_message
995 );
996 /*dbms_output.put_line('*** PROCESS ERROR *** Transaction_id:'||r_trx.transaction_id
997 ||fnd_global.local_chr(10)||fnd_global.local_chr(9)||'- ErrorMessage:'||lv_process_message );
998 */
999 ELSIF lv_cenvat_rg_flag IN ('E') THEN
1000 lv_codepath := jai_general_pkg.plot_codepath(14, lv_codepath);
1001 FND_FILE.put_line(FND_FILE.log, '*** CENVAT ERROR *** Transaction_id:'||r_trx.transaction_id
1002 ||fnd_global.local_chr(10)||fnd_global.local_chr(9)||'- ErrorMessage:'||lv_cenvat_rg_message
1003 );
1004 /*dbms_output.put_line('*** CENVAT ERROR *** Transaction_id:'||r_trx.transaction_id
1005 ||fnd_global.local_chr(10)||fnd_global.local_chr(9)||'- ErrorMessage:'||lv_cenvat_rg_message
1006 );*/
1007 END IF;
1008
1009 IF lv_process_flag IN ('X') AND lv_cenvat_rg_flag IN ('X') THEN
1010 lv_codepath := jai_general_pkg.plot_codepath(15, lv_codepath);
1011 FND_FILE.put_line(FND_FILE.log, ' Transaction Cant be processed for trx:'||r_trx.transaction_id
1012 ||fnd_global.local_chr(10)||fnd_global.local_chr(9)||'- ProcessMessage(X):'||lv_process_message
1013 ||fnd_global.local_chr(10)||fnd_global.local_chr(9)||'- CenvatMessgae(X):'||lv_cenvat_rg_message
1014 );
1015 /*dbms_output.put_line('M: Err2: Transaction Cant be processed for trx:'||r_trx.transaction_id
1016 ||fnd_global.local_chr(10)||fnd_global.local_chr(9)||'- ProcessMessage(X):'||lv_process_message
1017 ||fnd_global.local_chr(10)||fnd_global.local_chr(9)||'- CenvatMessgae(X):'||lv_cenvat_rg_message);
1018 */
1019 ELSIF lv_process_flag IN ('X') THEN
1020 lv_codepath := jai_general_pkg.plot_codepath(16, lv_codepath);
1021 FND_FILE.put_line(FND_FILE.log, 'Process Message(X):'||lv_process_message);
1022 /*dbms_output.put_line('M: Err4:'||lv_process_message);*/
1023 ELSIF lv_cenvat_rg_flag IN ('X') THEN
1024 lv_codepath := jai_general_pkg.plot_codepath(17, lv_codepath);
1025 FND_FILE.put_line(FND_FILE.log, 'Cenvat Messgae(X):'||lv_cenvat_rg_message);
1026 /*dbms_output.put_line('M: Err3:'||lv_cenvat_rg_message);*/
1027 END IF;
1028
1029 IF lv_process_flag = 'E' OR lv_cenvat_rg_flag = 'E' THEN
1030 lv_codepath := jai_general_pkg.plot_codepath(18, lv_codepath);
1031 p_process_flag := lv_cenvat_rg_flag;
1032 p_process_message := lv_cenvat_rg_message;
1033 fnd_file.put_line(fnd_file.log, 'M: Err5:'||p_process_message);
1034 END IF;
1035
1036 -- UNCLAIM PROCESSING
1037 -- following Unclaim Processing will not happen for
1038 ELSIf temp_rec.process_flag = 'U' AND r_trx.transaction_type IN ('RECEIVE', 'MATCH') THEN
1039
1040 lv_codepath := jai_general_pkg.plot_codepath(19, lv_codepath);
1041 SAVEPOINT start_unclaim;
1042
1043 -- Costing(Average and Standard)/Expense Logic for Excise Amount
1044
1045 lv_ttype_correct := 'CORRECT';
1046 lv_ttype_deliver := 'DELIVER';
1047 lv_type_rtr := 'RETURN TO RECEIVING';
1048
1049 FOR loop_trx IN c_trxs_for_unclaim(r_trx.shipment_line_id, r_trx.tax_transaction_id) LOOP /* Modified by Ramananda for removal of SQL LITERALs :bug#4428980*/
1050
1051 IF r_trx.organization_type = 'T' THEN
1052 lv_breakup_type := 'RG23D';
1053 END IF;
1054
1055 lv_codepath := jai_general_pkg.plot_codepath(21, lv_codepath);
1056 jai_rcv_excise_processing_pkg.get_tax_amount_breakup(
1057 p_shipment_line_id => loop_trx.shipment_line_id,
1058 p_transaction_id => loop_trx.transaction_id,
1059 p_curr_conv_rate => r_trx.currency_conversion_rate,
1060 pr_tax => r_tax, -- OUT Variable with Breakup
1061 p_breakup_type => lv_breakup_type,
1062 p_codepath => lv_codepath
1063 );
1064
1065 ln_process_special_amount := r_tax.basic_excise +
1066 r_tax.addl_excise +
1067 r_tax.other_excise +
1068 r_tax.cvd +
1069 r_tax.addl_cvd +
1070 -- Modified by SACSETHI Bug# 5228046
1071 -- Forward porting the change in 11i bug 5365523
1072 -- (Additional CVD Enhancement) as part of the R12 bug 5228046
1073 r_tax.excise_edu_cess +
1074 r_tax.cvd_edu_cess+r_tax.sh_exc_edu_cess + --Added by kunkumar for forward porting bug#5907436 to R12
1075 r_tax.sh_cvd_edu_cess; --Added by kunkumar for forward porting bug#5907436 to R12
1076
1077
1078 IF ln_process_special_amount = 0 THEN
1079 GOTO skip_unclaim_record;
1080 END IF;
1081
1082 lv_codepath := jai_general_pkg.plot_codepath(22, lv_codepath);
1083 --lv_codepath := '';
1084 jai_rcv_deliver_rtr_pkg.process_transaction (
1085 p_transaction_id => loop_trx.transaction_id,
1086 p_simulate => p_simulate_flag,
1087 p_codepath => lv_codepath,
1088 p_process_status => lv_process_flag,
1089 p_process_message => lv_process_message,
1090 p_process_special_source => jai_constants.cenvat_noclaim,
1091 p_process_special_amount => ln_process_special_amount
1092 );
1093
1094 IF p_process_flag IN ('E', 'X') THEN
1095 lv_codepath := jai_general_pkg.plot_codepath(23, lv_codepath);
1096 FND_FILE.put_line(FND_FILE.log, 'Unclaim PRC_FLG_Error: RollingBack to process_trxn_flag');
1097 /*dbms_output.put_line('Unclaim PRC_FLG_Error: RollingBack to process_trxn_flag');*/
1098 --p_codepath := jai_general_pkg.plot_codepath(6, p_codepath);
1099 ROLLBACK TO start_unclaim;
1100 -- following is to take care that if one transaction of RECEIVE childs fail, then loop should not
1101 -- be executed as the loop is related to CHILDs of RECEIVE Transaction
1102 EXIT;
1103 ElSIF p_process_flag = 'Y' THEN
1104 --p_codepath := jai_general_pkg.plot_codepath(7, p_codepath);
1105 p_process_message := 'Successful';
1106 ELSE
1107 FND_FILE.put_line(FND_FILE.log, 'Unclaim#PRC_FLG#'||p_process_flag);
1108 --p_codepath := jai_general_pkg.plot_codepath(8, p_codepath);
1109 /*dbms_output.put_line('Unclaim#PRC_FLG#'||p_process_flag);*/
1110 END IF;
1111
1112 UPDATE JAI_RCV_TRANSACTIONS
1113 SET CENVAT_RG_STATUS = 'X',
1114 cenvat_rg_message = 'Cenvat Unclaimed'
1115 WHERE CURRENT OF c_trxs_for_unclaim;
1116
1117 <<skip_unclaim_record>>
1118 NULL;
1119
1120 END LOOP;
1121
1122 lv_codepath := jai_general_pkg.plot_codepath(27, lv_codepath);
1123
1124 UPDATE JAI_RCV_TRANSACTIONS
1125 SET CENVAT_RG_STATUS = 'X',
1126 cenvat_rg_message = 'Cenvat Unclaimed'
1127 WHERE transaction_id = temp_rec.transaction_id
1128 -- following is to take care of Pending DELIVER and RTR and related CORRECTS
1129 -- as the parent receive is not yet claim so need of passing CENVAT RG entries.
1130 -- Simply update the flag as the transactions is already costed during delivery processing
1131 OR ( shipment_line_id = r_trx.shipment_line_id
1132 AND tax_transaction_id = r_trx.tax_transaction_id
1133 AND (transaction_type IN ('DELIVER', 'RETURN TO RECEIVING')
1134 or (transaction_type='CORRECT' and parent_transaction_type = 'DELIVER')
1135 )
1136 AND loc_subinv_type = 'N' -- non bonded
1137 AND CENVAT_RG_STATUS = 'P' -- waiting for receipt line claim
1138 );
1139
1140 /* Shall take the help of support to implement the unclaim functionality */
1141 UPDATE JAI_RCV_CENVAT_CLAIMS
1142 SET cenvat_amount = 0,
1143 other_cenvat_amt = 0,
1144 cenvat_sequence = cenvat_sequence + 1,
1145 unclaim_cenvat_flag ='Y',
1146 unclaim_cenvat_date = trunc(sysdate),
1147 unclaimed_cenvat_amount = temp_rec.unclaimed_cenvat_amount,
1148 last_update_date = sysdate,
1149 last_update_login = fnd_global.login_id,
1150 last_updated_by = fnd_global.user_id
1151 WHERE transaction_id = temp_rec.transaction_id;
1152
1153 -- By doing this future transactions will be taken care not to see Excise taxes as Recoverable
1154 UPDATE JAI_RCV_LINE_TAXES
1155 SET modvat_flag ='N',
1156 last_update_date = sysdate,
1157 last_updated_by = fnd_global.user_id,
1158 last_update_login = fnd_global.login_id
1159 WHERE shipment_line_id = temp_rec.shipment_line_id
1160 AND upper(tax_type) IN ('EXCISE',
1161 'ADDL. EXCISE',
1162 'OTHER EXCISE',
1163 'CVD',
1164 jai_constants.tax_type_add_cvd,
1165 -- Modified by SACSETHI Bug# 5228046
1166 -- Forward porting the change in 11i bug 5365523
1167 -- (Additional CVD Enhancement) as part of the R12 bug 5228046
1168 jai_constants.tax_type_exc_edu_cess,
1169 jai_constants.tax_type_cvd_edu_cess, jai_constants.tax_type_sh_exc_edu_cess,
1170 jai_constants.tax_type_sh_cvd_edu_cess) -- By kunkumar for bug 5989740
1171 AND modvat_flag ='Y';
1172
1173 lv_codepath := jai_general_pkg.plot_codepath(27.1, lv_codepath);
1174
1175 END IF;
1176
1177 << next_record >>
1178
1179 If lv_common_err_mesg IS NOT NULL
1180 OR lv_cenvat_rg_flag IN ('E', jai_constants.unexpected_error, jai_constants.expected_error)
1181 THEN
1182
1183 lv_codepath := jai_general_pkg.plot_codepath(28, lv_codepath);
1184 ROLLBACK TO start_deferred_claim;
1185 p_process_flag := lv_cenvat_rg_flag;
1186 p_process_message := lv_cenvat_rg_message;
1187 ln_errored_cnt := ln_errored_cnt + 1;
1188
1189 UPDATE JAI_RCV_CENVAT_CLAIM_T
1190 SET error_flag = 'Y',
1191 error_description = substr(lv_cenvat_rg_message,1,150),
1192 process_date = sysdate
1193 WHERE CURRENT OF c_trxs_to_be_claimed;
1194
1195 ELSE
1196
1197 lv_codepath := jai_general_pkg.plot_codepath(29, lv_codepath);
1198 -- Finally after the processing is completed, we need to delete the record from temp table
1199 DELETE FROM JAI_RCV_CENVAT_CLAIM_T
1200 WHERE CURRENT OF c_trxs_to_be_claimed;
1201
1202 END IF;
1203
1204 lv_codepath := jai_general_pkg.plot_codepath(30, lv_codepath);
1205 fnd_file.put_line(fnd_file.log, 'Trx_id:'||temp_rec.transaction_id||'. Codepath:'||lv_codepath);
1206 /*dbms_output.put_line('Trx_id:'||temp_rec.transaction_id||'. Codepath:'||lv_codepath);*/
1207
1208 END LOOP;
1209
1210 IF ln_errored_cnt > 0 THEN
1211 p_process_flag := jai_constants.unexpected_error;
1212 p_process_message := 'Errored Record Count:'||ln_errored_cnt;
1213 fnd_file.put_line(fnd_file.log, 'DeferredClaimError Cnt>0:'||p_process_message);
1214 END IF;
1215
1216 EXCEPTION
1217 WHEN OTHERS THEN
1218 ROLLBACK TO start_deferred_claim;
1219 p_process_flag := 'E';
1220 fnd_file.put_line(fnd_file.log, 'ErrorCodepath:'||lv_codepath);
1221 p_process_message := 'Processed Count:'||ln_processed_cnt||', Errored Cnt:'||ln_errored_cnt||'. Error Message:'||SQLERRM;
1222 fnd_file.put_line(fnd_file.log, 'DeferredClaimError6:'||p_process_message);
1223 END process_deferred_cenvat_claim;
1224
1225
1226 /*============================== MAIN PROCEDURE ==============================*/
1227 PROCEDURE process_batch(
1228 errbuf OUT NOCOPY VARCHAR2,
1229 retcode OUT NOCOPY VARCHAR2,
1230 p_organization_id IN NUMBER,
1231 pv_transaction_from IN VARCHAR2, /* rallamse bug#4336482 changed to VARCHAR2 from DATE */
1232 pv_transaction_to IN VARCHAR2, /* rallamse bug#4336482 changed to VARCHAR2 from DATE */
1233 p_transaction_type IN VARCHAR2,
1234 p_parent_trx_type IN VARCHAR2,
1235 p_shipment_header_id IN NUMBER, -- New parameter added by Vijay Shankar for Bug#3940588
1236 p_receipt_num IN VARCHAR2,
1237 p_shipment_line_id IN NUMBER, -- New parameter added by Vijay Shankar for Bug#3940588
1238 p_transaction_id IN NUMBER,
1239 p_commit_switch IN VARCHAR2, --File.Sql.35 Cbabu DEFAULT 'Y',
1240 p_called_from IN VARCHAR2, --File.Sql.35 Cbabu DEFAULT 'Batch', -- If the value passed is 'APPLICATION', then data is not Commited in this procedure
1241 p_simulate_flag IN VARCHAR2, --File.Sql.35 Cbabu DEFAULT 'N',
1242 p_trace_switch IN VARCHAR2, --File.Sql.35 Cbabu DEFAULT 'N'
1243 p_request_id IN NUMBER DEFAULT NULL, -- CSahoo for Bug 5344225
1244 p_group_id IN NUMBER DEFAULT NULL -- CSahoo for Bug 5344225
1245 ) IS
1246
1247 /* rallamse bug#4336482 */
1248 p_transaction_from DATE; --File.Sql.35 Cbabu DEFAULT fnd_date.canonical_to_date(pv_transaction_from);
1249 p_transaction_to DATE; --File.Sql.35 Cbabu DEFAULT fnd_date.canonical_to_date(pv_transaction_to);
1250 /* End of Bug# 4336482 */
1251
1252 ------------ Start, Declaration for TRACE Generation -------------
1253 lv_request_id VARCHAR2(50);
1254 ln_sid NUMBER;
1255 ln_serial NUMBER;
1256 lv_spid VARCHAR2(9);
1257 lv_dbname VARCHAR2(25);
1258 ln_audsid NUMBER; --File.Sql.35 Cbabu := userenv('SESSIONID');
1259 lv_tax_modified_check_flag varchar2(1); --File.Sql.35 Cbabu := 'Y' ;
1260
1261 CURSOR c_get_audsid IS
1262 SELECT a.sid, a.serial#, b.spid
1263 FROM v$session a, v$process b
1264 WHERE audsid = ln_audsid
1265 AND a.paddr = b.addr;
1266
1267 CURSOR c_get_dbname IS
1268 SELECT name FROM v$database;
1269 ------------ End, Declaration for TRACE Generation -------------
1270
1271 --added, CSahoo for Bug 5344225
1272 ln_req_status BOOLEAN ;
1273 lv_phase VARCHAR2(80) ;
1274 lv_status VARCHAR2(80) ;
1275 lv_dev_phase VARCHAR2(80) ;
1276 lv_dev_status VARCHAR2(80) ;
1277 lv_message VARCHAR2(80) ;
1278
1279 Cursor c_interface_exists(cp_group_id IN NUMBER)
1280 IS
1281 select 1
1282 from rcv_transactions_interface
1283 where group_id = cp_group_id
1284 and rownum=1 ;
1285
1286 Cursor c_interface_error(cp_group_id IN NUMBER)
1287 IS
1288 select 1
1289 from rcv_transactions_interface
1290 where
1291 group_id = cp_group_id and
1292 (transaction_status_code = 'ERROR' or processing_status_code = 'ERROR') ;
1293
1294 ln_error NUMBER ;
1295 --ended, CSahoo for Bug 5344225
1296
1297 lv_process_flag JAI_RCV_TRANSACTIONS.PROCESS_STATUS%TYPE;
1298 lv_process_message JAI_RCV_TRANSACTIONS.process_message%TYPE;
1299 lv_cenvat_rg_flag JAI_RCV_TRANSACTIONS.CENVAT_RG_STATUS%TYPE;
1300 lv_cenvat_rg_message JAI_RCV_TRANSACTIONS.cenvat_rg_message%TYPE;
1301 lv_codepath JAI_RCV_TRANSACTIONS.codepath%TYPE;
1302
1303 lv_common_err_mesg VARCHAR2(500);
1304 ln_processed_cnt NUMBER; --File.Sql.35 Cbabu := 0;
1305 ln_batch_id NUMBER(15);
1306
1307 r_trx_after_processing c_trx%rowtype;
1308
1309
1310 --commented the below by Sanjikum for Bug#4929410
1311 /*CURSOR c_trxns_to_populate_dtls IS
1312 SELECT rowid, transaction_id, shipment_line_id, process_status, cenvat_rg_status,
1313 transaction_type, parent_transaction_type, receipt_num, cenvat_claimed_ptg
1314 , attribute_category -- Vijay Shankar for Bug#4250171
1315 FROM JAI_RCV_TRANSACTIONS a
1316 WHERE (p_organization_id IS NULL OR organization_id = p_organization_id)
1317 AND a.receipt_num IS NULL
1318 AND (p_shipment_header_id IS NULL OR a.shipment_header_id = p_shipment_header_id)
1319 AND
1320 ( ( p_called_from = 'JAINPORE' and p_shipment_header_id is null) -- added, Harshita for bug #4300708
1321 OR ( exists (select 1 from JAI_RCV_LINES b
1322 where a.shipment_line_id = b.shipment_line_id
1323 and tax_modified_flag='N') )
1324 )
1325 FOR UPDATE OF transaction_id
1326 ORDER BY receipt_num, transaction_id;
1327
1328
1329 CURSOR c_get_transactions IS
1330 SELECT rowid, transaction_id, process_status, cenvat_rg_status, process_message, cenvat_rg_message,
1331 transaction_type, parent_transaction_type, receipt_num, cenvat_claimed_ptg,
1332 shipment_line_id -- Vijay Shankar for Bug#3940588
1333 FROM JAI_RCV_TRANSACTIONS a
1334 WHERE ( p_simulate_flag = 'Y'
1335 OR
1336 ( process_status IS NULL OR process_status IN ('N', 'E','P') OR cenvat_rg_status IN ('N', 'E','P')
1337 -- following condition added by Vijay Shankar for Bug#4250236(4245089). VAT Implementation
1338 OR -- process_vat_status IN ('N', 'E', 'P') this condition is modified as below for DFF elimination. Vijay Shankar for Bug#4346453. RCV DFF Elim. Enh.
1339 process_vat_status IN ('N', 'E', 'P', jai_constants.unexpected_error, jai_constants.expected_error)
1340 -- Vijay Shankar for Bug#3940588 RECEIPTS DEPLUG
1341 -- This is no more required because 2nd 50% Claim is handled seperately through deferred Claim procedure
1342 -- However process_transaction still uses cenvat_claimed_ptg flag to process 2nd 50% Claim
1343 -- OR cenvat_claimed_ptg = 50
1344 )
1345 )
1346 AND receipt_num IS NOT NULL -- a Check to see whether populate details is done for this trx or not
1347 AND (p_transaction_id IS NULL OR transaction_id = p_transaction_id)
1348 AND (p_organization_id IS NULL OR organization_id = p_organization_id)
1349 AND (p_shipment_header_id IS NULL OR shipment_header_id = p_shipment_header_id) -- Vijay Shankar for Bug#3940588
1350 AND (p_shipment_line_id IS NULL OR shipment_line_id = p_shipment_line_id) -- Vijay Shankar for Bug#3940588
1351 -- followingcondition is not required
1352 --AND (p_receipt_num IS NULL OR receipt_num = p_receipt_num)
1353 AND (p_transaction_type IS NULL OR transaction_type = p_transaction_type)
1354 AND (p_parent_trx_type IS NULL OR parent_transaction_type = p_parent_trx_type) -- Vijay Shankar for Bug#3940588
1355 AND (
1356 (p_transaction_from IS NULL AND p_transaction_to IS NULL)
1357 OR (p_transaction_from IS NULL AND trunc(creation_date) <= p_transaction_to)
1358 OR (p_transaction_to IS NULL AND trunc(creation_date) >= p_transaction_from)
1359 OR (trunc(creation_date) BETWEEN p_transaction_from AND p_transaction_to)
1360 )
1361 -- Check to pickup only those lines in which taxes cannot be modified,
1362 and
1363 ( ( p_shipment_header_id is null and p_called_from = 'JAINPORE' ) OR -- added, Harshita for bug #4300708
1364 exists (select 1 from JAI_RCV_LINES b
1365 where b.shipment_line_id = a.shipment_line_id
1366 and b.tax_modified_flag='N')
1367 )
1368 -- 3927371 (generic finding) this should process only these transactions if only receipt number is specified.
1369 -- This should be removed when old code is obsoleted with new code
1370 -- AND transaction_type IN ('CORRECT', 'DELIVER', 'RETURN TO RECEIVING'); -- commented for Vijay Shankar for Bug#3940588
1371 FOR UPDATE OF transaction_id -- added by Vijay Shankar for Bug#3940588
1372 ORDER BY transaction_id; -- added by Vijay Shankar for Bug#3940588 */
1373
1374 --Start added by Sanjikum for Bug#4929410
1375 PROCEDURE ja_in_populate_details IS
1376
1377 CURSOR c_lock_rcv_dtls IS
1378 SELECT rowid
1379 FROM jai_rcv_transactions
1380 WHERE transaction_id IN
1381 ( SELECT transaction_id
1382 FROM jai_rtp_populate_t)
1383 FOR UPDATE OF transaction_id ;
1384
1385 lock_rcv_rec_dtls c_lock_rcv_dtls%ROWTYPE ;
1386
1387 BEGIN
1388 IF p_shipment_header_id is null THEN
1389
1390 INSERT
1391 INTO JAI_RTP_POPULATE_T
1392 (TRANSACTION_ID,
1393 SHIPMENT_LINE_ID,
1394 PROCESS_FLAG,
1395 CENVAT_RG_FLAG,
1396 TRANSACTION_TYPE,
1397 PARENT_TRANSACTION_TYPE,
1398 RECEIPT_NUM,
1399 CENVAT_CLAIMED_PTG,
1400 ATTRIBUTE_CATEGORY)
1401 (
1402 SELECT transaction_id,
1403 shipment_line_id,
1404 process_status,
1405 cenvat_rg_status,
1406 transaction_type,
1407 parent_transaction_type,
1408 receipt_num,
1409 cenvat_claimed_ptg,
1410 attribute_category
1411 FROM jai_rcv_transactions a
1412 WHERE organization_id = p_organization_id
1413 AND a.receipt_num IS NULL
1414 AND p_called_from = 'JAINPORE');
1415
1416 ELSE
1417 INSERT
1418 INTO JAI_RTP_POPULATE_T
1419 (TRANSACTION_ID,
1420 SHIPMENT_LINE_ID,
1421 PROCESS_FLAG,
1422 CENVAT_RG_FLAG,
1423 TRANSACTION_TYPE,
1424 PARENT_TRANSACTION_TYPE,
1425 RECEIPT_NUM,
1426 CENVAT_CLAIMED_PTG,
1427 ATTRIBUTE_CATEGORY)
1428 (
1429 SELECT transaction_id,
1430 shipment_line_id,
1431 process_status,
1432 cenvat_rg_status,
1433 transaction_type,
1434 parent_transaction_type,
1435 receipt_num,
1436 cenvat_claimed_ptg,
1437 attribute_category
1438 FROM jai_rcv_transactions a
1439 WHERE organization_id = p_organization_id
1440 AND a.shipment_header_id = p_shipment_header_id
1441 AND a.receipt_num IS NULL
1442 AND EXISTS (SELECT 1
1443 FROM jai_rcv_lines b
1444 WHERE a.shipment_line_id = b.shipment_line_id
1445 AND tax_modified_flag='N')
1446 );
1447
1448 END IF ;
1449
1450 OPEN c_lock_rcv_dtls ;
1451 FETCH c_lock_rcv_dtls INTO lock_rcv_rec_dtls ;
1452 CLOSE c_lock_rcv_dtls ;
1453 END ja_in_populate_details;
1454
1455 PROCEDURE ja_in_get_transactions IS
1456 CURSOR c_lock_rcv_trans IS
1457 SELECT rowid
1458 FROM jai_rcv_transactions
1459 WHERE transaction_id IN
1460 ( SELECT transaction_id
1461 FROM jai_rtp_trans_t )
1462 FOR UPDATE OF transaction_id ;
1463
1464 lock_rcv_rec_trans c_lock_rcv_trans%ROWTYPE ;
1465 BEGIN
1466 IF p_shipment_header_id is null THEN
1467 IF p_transaction_type IS NOT NULL THEN
1468 IF p_parent_trx_type IS NOT NULL THEN
1469 INSERT
1470 INTO JAI_RTP_TRANS_T
1471 (TRANSACTION_ID,
1472 PROCESS_FLAG,
1473 CENVAT_RG_FLAG,
1474 PROCESS_MESSAGE,
1475 CENVAT_RG_MESSAGE,
1476 TRANSACTION_TYPE,
1477 PARENT_TRANSACTION_TYPE,
1478 RECEIPT_NUM,
1479 CENVAT_CLAIMED_PTG,
1480 SHIPMENT_LINE_ID)
1481 (
1482 SELECT transaction_id,
1483 process_status,
1484 cenvat_rg_status,
1485 process_message,
1486 cenvat_rg_message,
1487 transaction_type,
1488 parent_transaction_type,
1489 receipt_num,
1490 cenvat_claimed_ptg,
1491 shipment_line_id
1492 FROM jai_rcv_transactions a
1493 WHERE organization_id = p_organization_id
1494 AND transaction_type = p_transaction_type
1495 AND parent_transaction_type = p_parent_trx_type
1496 AND receipt_num IS NOT NULL
1497 AND ( p_simulate_flag = 'Y'
1498 OR
1499 ( process_status IS NULL
1500 OR process_status IN ('N', 'E','P')
1501 OR cenvat_rg_status IN ('N', 'E','P')
1502 OR process_vat_status IN ('N', 'E', 'P', jai_constants.unexpected_error, jai_constants.expected_error)
1503 )
1504 )
1505 AND (
1506 ( p_transaction_from IS NULL AND p_transaction_to IS NULL)
1507 OR (p_transaction_from IS NULL AND trunc(creation_date) <= p_transaction_to)
1508 OR (p_transaction_to IS NULL AND trunc(creation_date) >= p_transaction_from)
1509 OR (trunc(creation_date) BETWEEN p_transaction_from AND p_transaction_to)
1510 )
1511 ) ;
1512 ELSE -- p_parent_trx_type IS NOT NULL
1513 INSERT
1514 INTO JAI_RTP_TRANS_T
1515 (TRANSACTION_ID,
1516 PROCESS_FLAG,
1517 CENVAT_RG_FLAG,
1518 PROCESS_MESSAGE,
1519 CENVAT_RG_MESSAGE,
1520 TRANSACTION_TYPE,
1521 PARENT_TRANSACTION_TYPE,
1522 RECEIPT_NUM,
1523 CENVAT_CLAIMED_PTG,
1524 SHIPMENT_LINE_ID)
1525 (
1526 SELECT transaction_id,
1527 process_status,
1528 cenvat_rg_status,
1529 process_message,
1530 cenvat_rg_message,
1531 transaction_type,
1532 parent_transaction_type,
1533 receipt_num,
1534 cenvat_claimed_ptg,
1535 shipment_line_id
1536 FROM jai_rcv_transactions a
1537 WHERE organization_id = p_organization_id
1538 AND transaction_type = p_transaction_type
1539 AND receipt_num IS NOT NULL
1540 AND ( p_simulate_flag = 'Y'
1541 OR
1542 ( process_status IS NULL
1543 OR process_status IN ('N', 'E','P')
1544 OR cenvat_rg_status IN ('N', 'E','P')
1545 OR process_vat_status IN ('N', 'E', 'P', jai_constants.unexpected_error, jai_constants.expected_error)
1546 )
1547 )
1548 AND (
1549 ( p_transaction_from IS NULL AND p_transaction_to IS NULL)
1550 OR (p_transaction_from IS NULL AND trunc(creation_date) <= p_transaction_to)
1551 OR (p_transaction_to IS NULL AND trunc(creation_date) >= p_transaction_from)
1552 OR (trunc(creation_date) BETWEEN p_transaction_from AND p_transaction_to)
1553 )
1554 ) ;
1555
1556 END IF ; --IF p_parent_trx_type IS NOT NULL
1557
1558 ELSE --IF p_transaction_type IS NOT NULL
1559 INSERT
1560 INTO JAI_RTP_TRANS_T
1561 (TRANSACTION_ID,
1562 PROCESS_FLAG,
1563 CENVAT_RG_FLAG,
1564 PROCESS_MESSAGE,
1565 CENVAT_RG_MESSAGE,
1566 TRANSACTION_TYPE,
1567 PARENT_TRANSACTION_TYPE,
1568 RECEIPT_NUM,
1569 CENVAT_CLAIMED_PTG,
1570 SHIPMENT_LINE_ID)
1571 (
1572 SELECT transaction_id,
1573 process_status,
1574 cenvat_rg_status,
1575 process_message,
1576 cenvat_rg_message,
1577 transaction_type,
1578 parent_transaction_type,
1579 receipt_num,
1580 cenvat_claimed_ptg,
1581 shipment_line_id
1582 FROM jai_rcv_transactions a
1583 WHERE organization_id = p_organization_id
1584 AND receipt_num IS NOT NULL
1585 AND ( p_simulate_flag = 'Y'
1586 OR
1587 ( process_status IS NULL
1588 OR process_status IN ('N', 'E','P')
1589 OR cenvat_rg_status IN ('N', 'E','P')
1590 OR process_vat_status IN ('N', 'E', 'P', jai_constants.unexpected_error, jai_constants.expected_error)
1591 )
1592 )
1593 AND (
1594 ( p_transaction_from IS NULL AND p_transaction_to IS NULL)
1595 OR (p_transaction_from IS NULL AND trunc(creation_date) <= p_transaction_to)
1596 OR (p_transaction_to IS NULL AND trunc(creation_date) >= p_transaction_from)
1597 OR (trunc(creation_date) BETWEEN p_transaction_from AND p_transaction_to)
1598 )
1599 ) ;
1600 END IF ; --IF p_transaction_type IS NOT NULL
1601 ELSE
1602 INSERT
1603 INTO JAI_RTP_TRANS_T
1604 (TRANSACTION_ID,
1605 PROCESS_FLAG,
1606 CENVAT_RG_FLAG,
1607 PROCESS_MESSAGE,
1608 CENVAT_RG_MESSAGE,
1609 TRANSACTION_TYPE,
1610 PARENT_TRANSACTION_TYPE,
1611 RECEIPT_NUM,
1612 CENVAT_CLAIMED_PTG,
1613 SHIPMENT_LINE_ID)
1614 (
1615 SELECT transaction_id,
1616 process_status,
1617 cenvat_rg_status,
1618 process_message,
1619 cenvat_rg_message,
1620 transaction_type,
1621 parent_transaction_type,
1622 receipt_num,
1623 cenvat_claimed_ptg,
1624 shipment_line_id
1625 FROM jai_rcv_transactions a
1626 WHERE organization_id = p_organization_id
1627 AND shipment_header_id = p_shipment_header_id AND
1628 ( p_simulate_flag = 'Y'
1629 OR
1630 ( process_status IS NULL
1631 OR process_status IN ('N', 'E','P')
1632 OR cenvat_rg_status IN ('N', 'E','P')
1633 OR process_vat_status IN ('N', 'E', 'P', jai_constants.unexpected_error, jai_constants.expected_error)
1634 )
1635 )
1636 AND receipt_num IS NOT NULL
1637 AND (p_transaction_id IS NULL OR transaction_id = p_transaction_id)
1638 AND (p_shipment_line_id IS NULL OR shipment_line_id = p_shipment_line_id)
1639 AND (p_transaction_type IS NULL OR transaction_type = p_transaction_type)
1640 AND (p_parent_trx_type IS NULL OR parent_transaction_type = p_parent_trx_type)
1641 AND (
1642 (p_transaction_from IS NULL AND p_transaction_to IS NULL)
1643 OR (p_transaction_from IS NULL AND trunc(creation_date) <= p_transaction_to)
1644 OR (p_transaction_to IS NULL AND trunc(creation_date) >= p_transaction_from)
1645 OR (trunc(creation_date) BETWEEN p_transaction_from AND p_transaction_to)
1646 )
1647 AND EXISTS (SELECT 1
1648 FROM jai_rcv_lines b
1649 WHERE b.shipment_line_id = a.shipment_line_id
1650 AND b.tax_modified_flag='N')
1651 );
1652
1653 END IF ; -- IF p_shipment_header_id is null
1654
1655 OPEN c_lock_rcv_trans ;
1656 FETCH c_lock_rcv_trans INTO lock_rcv_rec_trans ;
1657 CLOSE c_lock_rcv_trans ;
1658
1659 END ja_in_get_transactions ;
1660 --Start added by Sanjikum for Bug#4929410
1661
1662 BEGIN
1663
1664 p_transaction_from := fnd_date.canonical_to_date(pv_transaction_from);
1665 p_transaction_to := fnd_date.canonical_to_date(pv_transaction_to);
1666 ln_audsid := userenv('SESSIONID');
1667 lv_tax_modified_check_flag := 'Y' ;
1668 ln_processed_cnt := 0;
1669
1670
1671 --Added by CSahoo for Bug 5344225
1672 --Start
1673 BEGIN
1674
1675 IF p_request_id is not null and p_request_id <> -1 THEN -- pramasub FP modified the line and given the comment below
1676 /*
1677 || ssumaith - for Iprocurement Bug#4281841 added the -1 in the above condition to ensure that the program does not
1678 || return in case of a call from Iprocurement.
1679 */
1680 ln_req_status := fnd_concurrent.wait_for_request
1681 (request_id => p_request_id,
1682 interval => 1,
1683 max_wait => 0,
1684 phase => lv_phase,
1685 status => lv_status,
1686 dev_phase => lv_dev_phase,
1687 dev_status => lv_dev_status,
1688 message => lv_message) ;
1689
1690 IF not ln_req_status THEN
1691 FND_FILE.put_line(FND_FILE.log, 'Phase : ' || lv_phase || 'Status : ' || lv_status || 'Dev Phase : ' || lv_dev_phase ||
1692 ' Dev Status : ' || lv_dev_status || ' Message : ' || lv_message );
1693 FND_FILE.put_line(FND_FILE.log, 'Problem in Completion of Receiving Transaction Processor - Request Id ' || p_request_id || '. Exiting from India - Receicing Transaction Processor ');
1694 RETURN ;
1695 END IF ;
1696 END IF ;
1697
1698 EXCEPTION
1699 WHEN OTHERS THEN
1700 FND_FILE.put_line(FND_FILE.log, 'Phase : ' || lv_phase || 'Status : ' || lv_status || 'Dev Phase : ' || lv_dev_phase ||
1701 ' Dev Status : ' || lv_dev_status || ' Message : ' || lv_message );
1702 FND_FILE.put_line(FND_FILE.log, 'Error in the Call to The fnd_concurrent.wait_for_request for Request Id ' || p_request_id || '. Returning... ');
1703 RETURN ;
1704 END;
1705
1706
1707
1708 BEGIN
1709 IF p_group_id is not null THEN
1710
1711 FOR rec_exists in c_interface_exists(p_group_id)
1712 LOOP
1713 ln_error := 0 ;
1714
1715 OPEN c_interface_error(p_group_id) ;
1716 FETCH c_interface_error INTO ln_error;
1717 CLOSE c_interface_error ;
1718
1719 IF ln_error = 1 THEN
1720 raise_application_error(-20001, ' Error while processing Receiving Transactions. Exiting from India Receiving Transaction Processor ') ;
1721 END IF ;
1722
1723 dbms_lock.sleep(1);
1724 END LOOP ;
1725 END IF ;
1726 EXCEPTION
1727 WHEN OTHERS THEN
1728 FND_FILE.put_line(FND_FILE.log, SQLERRM) ;
1729 RETURN ;
1730 END;
1731
1732 --ended, CSahoo for Bug 5344225
1733
1734 FND_FILE.put_line( FND_FILE.log, 'Start of Batch. Date:'||to_char(SYSDATE,'dd/mm/yyyy hh24:mi:ss') );
1735
1736 lv_request_id := FND_PROFILE.value('CONC_REQUEST_ID');
1737 FND_FILE.put_line( FND_FILE.log, 'Inputs. OrgnId:'||nvl(p_organization_id,-1)
1738 ||', TrxFrom->'||nvl(p_transaction_from, to_date('1-01-1700', 'dd-mm-yyyy'))
1739 ||', Trxto->'||nvl(p_transaction_to, to_date('1-01-1700', 'dd-mm-yyyy'))
1740 ||', TrxType->'||nvl(p_transaction_type, 'XXX')
1741 ||', RecptNum->'||nvl(p_receipt_num, 'ABCD')
1742 ||', CalFrom->'||nvl(p_called_from,'NO')
1743 ||', TrxId->'|| nvl(p_transaction_id, -999)
1744 ||', SimFlg->'|| p_simulate_flag
1745 ||', TrcSwtch->'|| p_trace_switch
1746 ||', ReqId->'|| lv_request_id
1747 --CSahoo for Bug 534425
1748 ||', p_request_id ' || p_request_id
1749 ||', p_group_id ' || p_group_id
1750 );
1751
1752 ---------------- Trace Generation Logic-------------------------
1753 BEGIN
1754 IF p_trace_switch = 'Y' THEN
1755
1756 OPEN c_get_audsid;
1757 FETCH c_get_audsid INTO ln_sid, ln_serial, lv_spid;
1758 CLOSE c_get_audsid;
1759
1760 OPEN c_get_dbname;
1761 FETCH c_get_dbname INTO lv_dbname;
1762 CLOSE c_get_dbname;
1763
1764 FND_FILE.put_line(FND_FILE.log, 'TraceFile Name = '||lower(lv_dbname)||'_ora_'||lv_spid||'.trc');
1765
1766 EXECUTE IMMEDIATE 'ALTER SESSION SET EVENTS ''10046 trace name context forever, level 4''';
1767
1768 END IF;
1769 EXCEPTION
1770 WHEN OTHERS THEN
1771 FND_FILE.put_line(FND_FILE.log, '%%%Problem in Trace Generation%%%');
1772 END;
1773
1774 /*----------------------------- START of Business Logic ----------------------*/
1775
1776 /******************** START OF DEFERRED CENVAT CLAIM LOGIC **************/
1777 BEGIN
1778
1779 IF p_called_from = 'JAINMVAT' THEN
1780
1781 -- value contained in p_shipment_header_id when called from JAINMVAT is batch_id of Claim/Unclaim
1782 ln_batch_id := p_shipment_header_id;
1783
1784 FND_FILE.put_line( FND_FILE.log, '~~~~~ Start of Deferred Claim ~~~~~. Batch:'||ln_batch_id);
1785 process_deferred_cenvat_claim(
1786 p_batch_id => ln_batch_id,
1787 p_called_from => p_called_from,
1788 p_simulate_flag => p_simulate_flag,
1789 p_process_flag => lv_process_flag,
1790 p_process_message => lv_process_message
1791 );
1792
1793 FND_FILE.put_line( FND_FILE.log, '~~~~~ End of Deferred Claim ~~~~~');
1794 IF lv_process_flag IN (jai_constants.unexpected_error, jai_constants.expected_error) THEN
1795 FND_FILE.put_line(fnd_file.log, 'PROCESS_DEFERRED_CENVAT_CLAIM. Err Message - '||lv_process_message);
1796 FND_FILE.put_line(fnd_file.log, 'For details, Please refer to JAI_RCV_CENVAT_CLAIMS table with batch_id = '||ln_batch_id);
1797 retcode := jai_constants.request_warning;
1798 END IF;
1799
1800 GOTO end_of_batch;
1801
1802 END IF;
1803
1804 EXCEPTION
1805 WHEN OTHERS THEN
1806 ROLLBACK;
1807 FND_file.put_line(fnd_file.log, 'Error Message:'||SQLERRM);
1808 /*dbms_output.put_line('MainProc-DeferredClaim:Err:'||SQLERRM);*/
1809 retcode := jai_constants.request_error;
1810 RETURN;
1811 END;
1812 /******************** END OF DEFERRED CLAIM **************/
1813
1814 -- Start, Vijay Shankar for Bug#3940588
1815 -- Following has to be executed as and when it is submitted from Receipts Localization form
1816 jai_cmn_utils_pkg.print_log('tax_mod.log','In transaction processing p_called_from:'||p_called_from);
1817 FND_FILE.put_line( FND_FILE.log, 'p_called_from:'||p_called_from);
1818
1819 IF p_called_from = 'JAINPORE' THEN
1820 FND_FILE.put_line( FND_FILE.log, '~~~~~ Updating JAI_RCV_LINES.tax_modified_flag to N ~~~~~');
1821
1822 UPDATE JAI_RCV_LINES a
1823 SET tax_modified_flag = 'N',
1824 last_update_date = SYSDATE,
1825 last_update_login = fnd_global.login_id,
1826 last_updated_by = fnd_global.user_id
1827 WHERE shipment_header_id = p_shipment_header_id
1828 AND tax_modified_flag IN ('Y', 'X')
1829 -- This condition will take not to update tax modified flag incase localization table is not populated
1830 -- eg.Incase of Unordered Receipt, Until MATCH happens we should not update the tax_modified_flag to 'Y'
1831 AND exists (select 1 from JAI_RCV_TRANSACTIONS
1832 where shipment_line_id = a.shipment_line_id);
1833
1834 -- This Commit is a definitive Commit that has to happen
1835 COMMIT;
1836
1837 END IF;
1838 -- End, Vijay Shankar for Bug#3940588
1839
1840
1841 FND_FILE.put_line( FND_FILE.log, '~~~~~ Start Populate Details ~~~~~');
1842 -- populate_details should be called only once and that too when the trxn is processed for the first time
1843
1844 ja_in_populate_details ; -- added by Sanjikum for bug#4929410
1845
1846 --FOR trx IN c_trxns_to_populate_dtls LOOP
1847 --commented the above and added the below by Sanjikum for Bug#4929410
1848 FOR trx IN (SELECT * FROM JAI_RTP_POPULATE_T ORDER BY receipt_num, transaction_id) LOOP
1849 -- SAVEPOINT start_trx_population;
1850 lv_codepath := '';
1851
1852 FND_FILE.put_line( FND_FILE.log, 'Recpt:'||trx.receipt_num||', TrxId:'||trx.transaction_id);
1853 populate_details(
1854 p_transaction_id => trx.transaction_id,
1855 p_process_status => lv_process_flag,
1856 p_process_message => lv_process_message,
1857 p_simulate_flag => p_simulate_flag,
1858 p_codepath => lv_codepath
1859 );
1860
1861 IF lv_process_flag = 'E' THEN
1862 exit;
1863 END IF;
1864
1865 IF trx.transaction_type IN ('RECEIVE','MATCH')
1866 /*Vijay Shankar for Bug#4250171. condition added to support OPM Transactions */
1867 AND (p_called_from <> CALLED_FROM_OPM OR trx.attribute_category in (OPM_RECEIPT,OPM_RETURNS) )
1868 THEN
1869 transaction_preprocessor(
1870 p_shipment_line_id => trx.shipment_line_id,
1871 p_transaction_id => trx.transaction_id,
1872 p_process_status => lv_process_flag,
1873 p_process_message => lv_process_message,
1874 p_simulate_flag => p_simulate_flag
1875 );
1876 END IF;
1877
1878 IF lv_process_flag = 'E' THEN
1879 exit;
1880 END IF;
1881
1882 END LOOP;
1883
1884 -- IF UPPER(p_called_from) <> 'APPLICATION' THEN
1885 IF lv_process_flag = 'E' THEN
1886 ROLLBACK;
1887 FND_FILE.put_line( FND_FILE.log, '*** POPULATE_DETAILS Error ***:'||lv_process_message);
1888 errbuf := lv_process_message;
1889 retcode := jai_constants.request_error;
1890 RETURN;
1891 ELSIF p_commit_switch = 'Y' THEN
1892 -- first commit to save all the populated details of JAI_RCV_TRANSACTIONS
1893 COMMIT;
1894 END IF;
1895
1896 /*~~~~~~~~~~~~~~~~~~~~~~~~~ Start of PROCESSING TRANSACTIONS ~~~~~~~~~~~~~~~~~~~~~*/
1897
1898 ja_in_get_transactions ; -- internal procedure call ; -- added by Sanjikum for bug #4929410
1899
1900 --FOR trx IN c_get_transactions LOOP
1901 --commented the above and added the below by Sanjikum for Bug#4929410
1902 FOR trx IN (SELECT * FROM JAI_RTP_TRANS_T ORDER BY transaction_id) LOOP
1903
1904 BEGIN
1905
1906 lv_common_err_mesg := null;
1907 lv_codepath := '';
1908 r_trx_after_processing := null;
1909
1910 FND_FILE.put_line(FND_FILE.log, '+++ Start of ReceiptNo, Transaction_id:'||trx.receipt_num||','||trx.transaction_id
1911 ||', trxn_type:'||trx.transaction_type
1912 ||', parent_trxn_type:'||trx.parent_transaction_type
1913 ||', process_status:'||trx.process_flag
1914 ||', cenvat_rg_status:'||trx.cenvat_rg_flag
1915 ||', cenvat_claimed_ptg:'||trx.cenvat_claimed_ptg
1916 );
1917
1918 -- lv_codepath := jai_general_pkg.plot_codepath(1, lv_codepath, 'process_batch', 'start'); /* 1 */
1919
1920 SAVEPOINT process_trxn;
1921
1922 lv_process_flag := trx.process_flag;
1923 lv_process_message := trx.process_message;
1924 lv_cenvat_rg_flag := trx.cenvat_rg_flag;
1925 lv_cenvat_rg_message := trx.cenvat_rg_message;
1926
1927 -- added, Harshita for bug #4300708
1928
1929 IF ( p_called_from = 'JAINPORE' and p_shipment_header_id is null
1930 and trx.transaction_type IN ('RECEIVE', 'MATCH') ) THEN
1931 update JAI_RCV_LINES
1932 set tax_modified_flag = 'N'
1933 where receipt_num = trx.receipt_num ;
1934 END IF ;
1935
1936 -- ended, Harshita for bug #4300708
1937
1938 process_transaction(
1939 p_transaction_id => trx.transaction_id,
1940 p_process_flag => lv_process_flag,
1941 p_process_message => lv_process_message,
1942 p_cenvat_rg_flag => lv_cenvat_rg_flag,
1943 p_cenvat_rg_message => lv_cenvat_rg_message,
1944 p_common_err_mesg => lv_common_err_mesg,
1945 p_called_from => p_called_from,
1946 p_simulate_flag => p_simulate_flag,
1947 p_codepath => lv_codepath,
1948 p_excise_processing_reqd => jai_constants.yes, --File.Sql.35 Cbabu
1949 p_vat_processing_reqd => jai_constants.yes --File.Sql.35 Cbabu
1950 );
1951
1952 OPEN c_trx(trx.transaction_id);
1953 FETCH c_trx INTO r_trx_after_processing;
1954 CLOSE c_trx;
1955
1956 -- 'X' flag is removed from the following ELSIF conditions because it is not an ERROR Status. MYXZ
1957 IF lv_common_err_mesg IS NOT NULL THEN
1958 -- A common error occured. So, Whole Processing for Transaction should be stopped
1959 ROLLBACK TO process_trxn;
1960 FND_FILE.put_line(FND_FILE.log, '*** Common Error for Transaction_id:'||trx.transaction_id
1961 ||fnd_global.local_chr(10)||fnd_global.local_chr(9)||'- Error:'||lv_common_err_mesg
1962 );
1963 retcode := 1;
1964 ELSIF lv_process_flag IN ('E') AND lv_cenvat_rg_flag IN ('E') THEN
1965 FND_FILE.put_line(FND_FILE.log, '*** FLAGS ERROR *** Transaction_id:'||trx.transaction_id
1966 ||fnd_global.local_chr(10)||fnd_global.local_chr(9)||'- ProcessErr:'||lv_process_message
1967 ||fnd_global.local_chr(10)||fnd_global.local_chr(9)||'- CenvatErr:'||lv_cenvat_rg_message
1968 );
1969 ELSIF lv_process_flag IN ('E') THEN
1970 FND_FILE.put_line(FND_FILE.log, '*** PROCESS ERROR *** Transaction_id:'||trx.transaction_id
1971 ||fnd_global.local_chr(10)||fnd_global.local_chr(9)||'- ErrorMessage:'||lv_process_message
1972 );
1973 ELSIF lv_cenvat_rg_flag IN ('E') THEN
1974 FND_FILE.put_line(FND_FILE.log, '*** CENVAT ERROR *** Transaction_id:'||trx.transaction_id
1975 ||fnd_global.local_chr(10)||fnd_global.local_chr(9)||'- ErrorMessage:'||lv_cenvat_rg_message
1976 );
1977
1978 /* added for VAT Impl. Vijay Shankar for Bug#4250236(4245089) */
1979 -- Vijay Shankar for Bug#4346453. RCV DFF Elim. Enh. ELSIF r_trx_after_processing.process_vat_status <> jai_constants.successful THEN
1980 ELSIF r_trx_after_processing.process_vat_status in ('E', jai_constants.unexpected_error, jai_constants.expected_error) then
1981 FND_FILE.put_line(FND_FILE.log, '*** VAT Message *** Transaction_id:'||trx.transaction_id
1982 ||fnd_global.local_chr(10)||fnd_global.local_chr(9)||', Flag:'||r_trx_after_processing.process_vat_status
1983 ||' - ErrorMessage:'||r_trx_after_processing.process_vat_message
1984 );
1985 END IF;
1986
1987 -- Start, 3927371
1988 IF lv_process_flag IN ('X') AND lv_cenvat_rg_flag IN ('X') THEN
1989 FND_FILE.put_line(FND_FILE.log, 'Transaction_Id:'||trx.transaction_id
1990 ||fnd_global.local_chr(10)||fnd_global.local_chr(9)||'- ProcessMessage(X):'||lv_process_message
1991 ||fnd_global.local_chr(10)||fnd_global.local_chr(9)||'- CenvatMessgae(X):'||lv_cenvat_rg_message
1992 );
1993 ELSIF lv_process_flag IN ('X') THEN
1994 FND_FILE.put_line(FND_FILE.log, 'Process Message(X):'||lv_process_message);
1995 ELSIF lv_cenvat_rg_flag IN ('X') THEN
1996 FND_FILE.put_line(FND_FILE.log, 'Cenvat Messgae(X):'||lv_cenvat_rg_message);
1997 END IF;
1998
1999 IF lv_process_flag = 'E' OR lv_cenvat_rg_flag = 'E' THEN
2000 retcode := 1;
2001 END IF;
2002 -- End, 3927371
2003
2004 /* Start, Vijay Shankar for Bug#3940588 */
2005 -- Transaction Post Processor
2006 transaction_postprocessor(
2007 p_shipment_line_id => trx.shipment_line_id,
2008 p_transaction_id => trx.transaction_id,
2009 p_process_status => lv_process_flag,
2010 p_process_message => lv_process_message,
2011 p_simulate_flag => p_simulate_flag,
2012 p_codepath => lv_codepath
2013 );
2014 -- End, Vijay Shankar for Bug#3940588
2015
2016 if lv_process_flag = 'E' then
2017 ROLLBACK TO process_trxn;
2018 FND_FILE.put_line(FND_FILE.log, 'Error:Transaction_postprocessor. MSG:'||lv_process_message);
2019 end if;
2020
2021 IF p_simulate_flag = 'Y' THEN
2022 FND_FILE.put_line(FND_FILE.log, 'Codepath->'||lv_codepath);
2023 ELSE
2024 UPDATE JAI_RCV_TRANSACTIONS
2025 SET codepath = lv_codepath
2026 --WHERE rowid = trx.row_id
2027 --commented the above and added the below by sanjikum for Bug#4929410
2028 WHERE transaction_id = trx.transaction_id;
2029 END IF;
2030
2031 -- IF UPPER(p_called_from) <> 'APPLICATION' THEN
2032 /* Vijay Shankar for Bug#4208224
2033 IF p_commit_switch = 'Y' THEN
2034 IF ln_processed_cnt >= ln_commit_after THEN
2035 COMMIT;
2036 ln_processed_cnt := 0;
2037 ELSE
2038 END IF;
2039 END IF;
2040 */
2041
2042 ln_processed_cnt := ln_processed_cnt + 1;
2043 lv_common_err_mesg := null;
2044
2045 EXCEPTION
2046 WHEN OTHERS THEN
2047 ROLLBACK TO process_trxn;
2048 FND_FILE.put_line( FND_FILE.log, 'Rolled Back Processing in RECEIPT_TRANSACTIONS_PKG.process_batch: Error->'||SQLERRM);
2049 retcode := 1;
2050 END;
2051
2052 END LOOP;
2053
2054 <<end_of_batch>>
2055 -- IF UPPER(p_called_from) <> 'APPLICATION' THEN
2056 IF p_commit_switch = 'Y' THEN
2057 -- Final Commit to Permanently Save any changes left
2058 COMMIT;
2059 END IF;
2060
2061 /* added by Vijay Shankar for Bug#3940588 */
2062 EXCEPTION
2063 WHEN OTHERS THEN
2064 ROLLBACK;
2065 FND_FILE.put_line( FND_FILE.log, 'Error:'||SQLERRM);
2066 FND_FILE.put_line( FND_FILE.log, 'Error Path:'||lv_codepath);
2067 RAISE;
2068 END process_batch;
2069
2070 /* Vijay Shankar for Bug#4346453. RCV DFF Elim. Enh. */
2071 PROCEDURE insert_rtv_batch_group(
2072 pn_batch_group_id OUT NOCOPY NUMBER,
2073 pn_batch_num IN NUMBER,
2074 pv_regime_code IN VARCHAR2,
2075 pn_organization_id IN NUMBER,
2076 pn_location_id IN NUMBER,
2077 pn_vendor_id IN NUMBER,
2078 pn_vendor_site_id IN NUMBER,
2079 pv_invoice_no IN VARCHAR2,
2080 pd_invoice_date IN DATE,
2081 pv_process_status IN VARCHAR2,
2082 pv_process_message IN VARCHAR2
2083 ) IS
2084 ln_user_id NUMBER;
2085 ln_login_id NUMBER;
2086
2087 BEGIN
2088
2089 ln_user_id := fnd_global.user_id;
2090 ln_login_id := fnd_global.user_id;
2091
2092 INSERT INTO jai_rcv_rtv_batch_grps(
2093 batch_group_id,
2094 batch_num, regime_code, organization_id, location_id,
2095 vendor_id, vendor_site_id, invoice_no, invoice_date,
2096 creation_date, created_by, last_update_date, last_updated_by, last_update_login
2097 ) VALUES (
2098 jai_rcv_rtv_batch_grps_s.nextval,
2099 pn_batch_num, pv_regime_code, pn_organization_id, pn_location_id,
2100 pn_vendor_id, pn_vendor_site_id, pv_invoice_no, pd_invoice_date,
2101 sysdate, ln_user_id, sysdate, ln_user_id, ln_login_id
2102 ) RETURNING batch_group_id INTO pn_batch_group_id;
2103
2104 END insert_rtv_batch_group;
2105
2106 /*following procedure added as part of RTV DFF Elimination Enhacement Vijay Shankar for Bug#4346453. RCV DFF Elim. Enh. */
2107 procedure process_rtv(
2108 pv_errbuf OUT NOCOPY VARCHAR2,
2109 pv_retcode OUT NOCOPY VARCHAR2,
2110 pn_batch_num IN NUMBER,
2111 pn_min_transaction_id IN NUMBER,
2112 pn_max_transaction_id IN NUMBER,
2113 pv_called_from IN VARCHAR2, --File.Sql.35 Cbabu DEFAULT 'Y',
2114 pv_commit_switch IN VARCHAR2, --File.Sql.35 Cbabu DEFAULT 'Y',
2115 pv_debug_switch IN VARCHAR2 --File.Sql.35 Cbabu DEFAULT 'N'
2116 ) is
2117
2118 cursor c_rtv_trxs(cpn_batch_num in number) is
2119 select a.receipt_num, a.transaction_id, a.shipment_line_id, a.shipment_header_id,
2120 a.organization_id, a.location_id, b.vendor_id, b.vendor_site_id, b.excise_batch_num, b.vat_batch_num,
2121 a.excise_invoice_no, a.vat_invoice_no, a.excise_invoice_date, a.vat_invoice_date
2122 , a.parent_transaction_id,
2123 a.PROCESS_STATUS, a.process_message, a.CENVAT_RG_STATUS, a.cenvat_rg_message,
2124 a.PROCESS_VAT_STATUS, a.process_vat_message
2125 , a.attribute1 excise_invoice_action, a.attribute2 vat_invoice_action,
2126 nvl(b.receipt_excise_rate,0) receipt_excise_rate,
2127 nvl(b.rtv_excise_rate, nvl(b.receipt_excise_rate, 0)) rtv_excise_rate,
2128 decode( b.excise_batch_num, cpn_batch_num, jai_constants.yes, jai_constants.no) process_excise_in_batch,
2129 decode( b.vat_batch_num, cpn_batch_num, jai_constants.yes, jai_constants.no) process_vat_in_batch
2130 from JAI_RCV_TRANSACTIONS a, jai_rcv_rtv_batch_trxs b
2131 where a.transaction_id = b.transaction_id
2132 and (pn_min_transaction_id is null or a.transaction_id >= pn_min_transaction_id)
2133 and (pn_max_transaction_id is null or a.transaction_id <= pn_max_transaction_id)
2134 and
2135 ( ( b.excise_batch_num = cpn_batch_num and a.excise_invoice_no is null)
2136 or ( b.vat_batch_num = cpn_batch_num and a.vat_invoice_no is null)
2137 )
2138 order by a.organization_id, a.location_id, b.vendor_id, b.vendor_site_id, a.transaction_id
2139 for update of a.excise_invoice_no, a.excise_invoice_date, a.vat_invoice_no, a.vat_invoice_date;
2140
2141 cursor c_regime_id(cpv_regime_code varchar2) is
2142 select regime_id
2143 from JAI_RGM_DEFINITIONS
2144 where regime_code = cpv_regime_code;
2145
2146 ln_regime_id JAI_RGM_DEFINITIONS.regime_id%TYPE;
2147
2148 lv_excise_invoice_no JAI_RCV_TRANSACTIONS.excise_invoice_no%TYPE;
2149 ld_excise_invoice_date DATE;
2150 lv_gen_excise_invoice_no JAI_RCV_TRANSACTIONS.excise_invoice_no%TYPE;
2151 ld_gen_excise_invoice_date DATE;
2152
2153 lv_vat_invoice_no JAI_RCV_TRANSACTIONS.vat_invoice_no%TYPE;
2154 ld_vat_invoice_date DATE;
2155 lv_gen_vat_invoice_no JAI_RCV_TRANSACTIONS.vat_invoice_no%TYPE;
2156 ld_gen_vat_invoice_date DATE;
2157
2158 lv_same_invoice_no_flag VARCHAR2(1);
2159
2160 ln_organization_id NUMBER(15);
2161 ln_location_id NUMBER(15);
2162 ln_vendor_id NUMBER(15);
2163 ln_vendor_site_id NUMBER(15);
2164
2165 lv_errbuf VARCHAR2(1000);
2166 lv_statement_id VARCHAR2(4);
2167
2168
2169 lv_process_flag JAI_RCV_TRANSACTIONS.PROCESS_STATUS%TYPE;
2170 lv_process_message JAI_RCV_TRANSACTIONS.process_message%TYPE;
2171 lv_cenvat_rg_flag JAI_RCV_TRANSACTIONS.CENVAT_RG_STATUS%TYPE;
2172 lv_cenvat_rg_message JAI_RCV_TRANSACTIONS.cenvat_rg_message%TYPE;
2173 lv_common_err_mesg JAI_RCV_TRANSACTIONS.cenvat_rg_message%TYPE;
2174
2175 lv_excise_processing_reqd VARCHAR2(1);
2176 lv_vat_processing_reqd VARCHAR2(1);
2177 lv_ssi_cenvat_rg_flag JAI_RCV_TRANSACTIONS.CENVAT_RG_STATUS%TYPE;
2178 lv_ssi_cenvat_rg_message JAI_RCV_TRANSACTIONS.cenvat_rg_message%TYPE;
2179
2180 lv_codepath JAI_RCV_TRANSACTIONS.codepath%TYPE;
2181 r_trx_after_processing c_trx%ROWTYPE;
2182 lb_err_flag boolean;
2183 ln_processed_cnt number;
2184
2185 lv_excise_inv_gen_action VARCHAR2(50);
2186 lv_vat_inv_gen_action VARCHAR2(50);
2187
2188 ln_excise_batch_group_id jai_rcv_rtv_batch_grps.batch_group_id%TYPE;
2189 ln_vat_batch_group_id jai_rcv_rtv_batch_grps.batch_group_id%TYPE;
2190
2191 begin
2192
2193 lv_statement_id := '0';
2194 if lb_debug then
2195 fnd_file.put_line(fnd_file.log, 'Input Params. pn_batch_num:'||pn_batch_num
2196 ||', pn_min_transaction_id:'||pn_min_transaction_id
2197 ||', pn_max_transaction_id:'||pn_max_transaction_id
2198 ||', pv_called_from:'||pv_called_from
2199 ||', pv_commit_switch:'||pv_commit_switch
2200 ||', pv_debug_switch:'||pv_debug_switch);
2201 end if;
2202
2203
2204 ln_processed_cnt := 0;
2205 ln_organization_id := -1;
2206 ln_location_id := -1;
2207 ln_vendor_id := -1;
2208 ln_vendor_site_id := -1;
2209
2210 /* Excise Invoice Generation for Batch */
2211 for rtv_rec in c_rtv_trxs(pn_batch_num) loop
2212
2213 lv_statement_id := '1';
2214 lv_excise_invoice_no := null;
2215 ld_excise_invoice_date := null;
2216 lv_vat_invoice_no := null;
2217 ld_vat_invoice_date := null;
2218
2219 -- excise invoice number will be generated if the following grouping condition changes
2220 if ln_organization_id <> rtv_rec.organization_id
2221 OR ln_location_id <> rtv_rec.location_id
2222 OR ln_vendor_id <> rtv_rec.vendor_id
2223 OR ln_vendor_site_id <> rtv_rec.vendor_site_id
2224 then
2225 lv_statement_id := '3';
2226 ln_organization_id := rtv_rec.organization_id;
2227 ln_location_id := rtv_rec.location_id;
2228 ln_vendor_id := rtv_rec.vendor_id;
2229 ln_vendor_site_id := rtv_rec.vendor_site_id;
2230
2231 lv_statement_id := '4';
2232 lv_same_invoice_no_flag := null;
2233 lv_same_invoice_no_flag :=
2234 jai_cmn_rgm_recording_pkg.get_rgm_attribute_value(
2235 pv_regime_code => jai_constants.vat_regime,
2236 pv_organization_type => jai_constants.orgn_type_io,
2237 pn_organization_id => ln_organization_id,
2238 pn_location_id => ln_location_id,
2239 pv_registration_type => jai_constants.regn_type_others, --'OTHERS',
2240 pv_attribute_type_code => NULL,
2241 pv_attribute_code => jai_constants.attr_code_same_inv_no -- 'SAME_INVOICE_NO'
2242 );
2243
2244 lv_statement_id := '5';
2245 lv_same_invoice_no_flag := nvl(lv_same_invoice_no_flag, jai_constants.no);
2246 lv_gen_excise_invoice_no := null;
2247 ld_gen_excise_invoice_date := null;
2248 lv_gen_vat_invoice_no := null;
2249 ld_gen_vat_invoice_date := null;
2250 ln_excise_batch_group_id := null;
2251 ln_vat_batch_group_id := null;
2252
2253 end if;
2254
2255 lv_statement_id := '5.1';
2256 if lb_debug then
2257 fnd_file.put_line(fnd_file.log, '~~~ ReceiptNum:'||rtv_rec.receipt_num||', TrxId:'||rtv_rec.transaction_id
2258 ||', SameFlg:'||lv_same_invoice_no_flag
2259 ||', ExBatch:'||rtv_rec.excise_batch_num ||', VatBatch:'||rtv_rec.vat_batch_num
2260 ||', ExAct:'||rtv_rec.excise_invoice_action ||', VatAct:'||rtv_rec.vat_invoice_action
2261 );
2262 end if;
2263
2264 lv_statement_id := '6';
2265 /* Start Excise Inv. Gen */
2266 if rtv_rec.excise_batch_num = pn_batch_num and rtv_rec.excise_invoice_no is null
2267 and rtv_rec.excise_invoice_action = INV_GEN_STATUS_GENERATE
2268 then
2269 lv_statement_id := '7';
2270 if lv_same_invoice_no_flag = jai_constants.yes then
2271 lv_statement_id := '8';
2272 lv_excise_invoice_no := rtv_rec.vat_invoice_no;
2273 ld_excise_invoice_date := rtv_rec.vat_invoice_date;
2274 end if;
2275
2276 lv_statement_id := '9';
2277 if lv_excise_invoice_no is null then
2278 lv_statement_id := '10';
2279 lv_excise_invoice_no := lv_gen_excise_invoice_no;
2280 ld_excise_invoice_date := ld_gen_excise_invoice_date;
2281
2282 lv_statement_id := '11';
2283 if lv_excise_invoice_no is null then
2284 lv_statement_id := '12';
2285 ld_gen_excise_invoice_date := trunc(sysdate);
2286 jai_cmn_setup_pkg.generate_excise_invoice_no(
2287 p_organization_id => ln_organization_id,
2288 p_location_id => ln_location_id,
2289 p_called_from => 'P', -- Required for excise invoice generation for RTV
2290 p_order_invoice_type_id => NULL,
2291 p_fin_year => jai_general_pkg.get_fin_year(ln_organization_id),
2292 p_excise_inv_no => lv_gen_excise_invoice_no,
2293 p_errbuf => lv_errbuf
2294 );
2295
2296 lv_statement_id := '13';
2297 lv_excise_invoice_no := lv_gen_excise_invoice_no;
2298 ld_excise_invoice_date := ld_gen_excise_invoice_date;
2299
2300 end if;
2301 end if;
2302
2303 else
2304 lv_statement_id := '14';
2305 lv_excise_invoice_no := rtv_rec.excise_invoice_no;
2306 ld_excise_invoice_date := rtv_rec.excise_invoice_date;
2307 end if;
2308 /* End Excise Inv. Gen */
2309
2310 lv_statement_id := '15';
2311 /* Start VAT Inv. Gen */
2312 if rtv_rec.vat_batch_num = pn_batch_num and rtv_rec.vat_invoice_no is null
2313 and rtv_rec.vat_invoice_action = INV_GEN_STATUS_GENERATE
2314 then
2315 lv_statement_id := '16';
2316 if lv_same_invoice_no_flag = jai_constants.yes then
2317 lv_statement_id := '17';
2318 lv_vat_invoice_no := lv_excise_invoice_no;
2319 ld_vat_invoice_date := ld_excise_invoice_date;
2320 end if;
2321
2322 if lv_vat_invoice_no is null then
2323 lv_statement_id := '18';
2324 lv_vat_invoice_no := lv_gen_vat_invoice_no;
2325 ld_vat_invoice_date := ld_gen_vat_invoice_date;
2326
2327 if lv_vat_invoice_no is null then
2328
2329 lv_statement_id := '19';
2330 open c_regime_id(jai_constants.vat_regime);
2331 fetch c_regime_id into ln_regime_id;
2332 close c_regime_id;
2333
2334 lv_statement_id := '20';
2335 ld_gen_vat_invoice_date := trunc(sysdate);
2336 jai_cmn_rgm_setup_pkg.gen_invoice_number(
2337 p_regime_id => ln_regime_id,
2338 p_organization_id => ln_organization_id,
2339 p_location_id => ln_location_id,
2340 p_date => ld_gen_vat_invoice_date,
2341 p_doc_class => 'R',
2342 p_doc_type_id => null,
2343 P_invoice_number => lv_gen_vat_invoice_no,
2344 p_process_flag => lv_process_flag,
2345 p_process_msg => lv_process_message
2346 );
2347
2348 lv_statement_id := '21';
2349 if lv_process_flag in (jai_constants.expected_error, jai_constants.unexpected_error) then
2350 fnd_file.put_line(fnd_file.log, 'VAT Inv Gen Error. Params- RgmId:'||ln_regime_id
2351 ||', OrgnId:'||ln_organization_id ||', LocId:'||ln_location_id
2352 ||', InvDate:'||ld_gen_vat_invoice_date||', TrxId:'|| rtv_rec.transaction_id );
2353 fnd_file.put_line(fnd_file.log, 'ErrorCode:'||lv_process_flag
2354 ||', ErrMsg:'||lv_process_message);
2355 raise_application_error( -20112, 'VAT Inv Gen Error. Code:'||lv_process_flag
2356 ||', MSG:'||lv_process_message);
2357 end if;
2358
2359 lv_statement_id := '22';
2360 lv_vat_invoice_no := lv_gen_vat_invoice_no;
2361 ld_vat_invoice_date := ld_gen_vat_invoice_date;
2362 end if;
2363
2364 end if;
2365
2366 else
2367 lv_statement_id := '23';
2368 lv_vat_invoice_no := rtv_rec.vat_invoice_no;
2369 ld_vat_invoice_date := rtv_rec.vat_invoice_date;
2370 end if;
2371 /* End. VAT Inv. Gen. */
2372
2373 lv_statement_id := '23.1';
2374 if lb_debug then
2375 fnd_file.put_line(fnd_file.log, '~~~ ExInvNo:'||lv_excise_invoicE_no ||', ld_excise_invoice_Date:'||ld_excise_invoice_date
2376 ||', VatInvNo:'||lv_vat_invoice_no ||', ld_vat_invoice_Date:'||ld_vat_invoice_date
2377 );
2378 end if;
2379
2380 if rtv_rec.excise_batch_num = pn_batch_num and rtv_rec.excise_invoice_no is null then
2381
2382 if rtv_rec.excise_invoice_action = INV_GEN_STATUS_GENERATE then
2383
2384 lv_statement_id := '24';
2385 update JAI_RCV_TRANSACTIONS a
2386 set excise_invoice_no = lv_excise_invoice_no,
2387 excise_invoice_date = ld_excise_invoice_date,
2388 --attribute3 = pn_batch_num,
2389 last_update_date = sysdate,
2390 last_updated_by = fnd_global.user_id
2391 where current of c_rtv_trxs;
2392
2393 lv_statement_id := '25';
2394 INSERT INTO JAI_RCV_RTV_DTLS(
2395 transaction_id, parent_transaction_id, shipment_line_id,
2396 excise_invoice_no, excise_invoice_date, rg_register_part_i,
2397 creation_date, created_by, last_update_date, last_updated_by, last_update_login
2398 ) VALUES (
2399 rtv_rec.transaction_id, rtv_rec.parent_transaction_id, rtv_rec.shipment_line_id,
2400 lv_excise_invoice_no, ld_excise_invoice_date, NULL,
2401 sysdate, fnd_global.user_id, sysdate, fnd_global.user_id, fnd_global.login_id
2402 );
2403
2404 lv_excise_inv_gen_action := INV_GEN_STATUS_INV_GENERATED;
2405 else
2406 lv_excise_inv_gen_action := rtv_rec.excise_invoice_action;
2407 end if;
2408 end if;
2409
2410 if rtv_rec.vat_batch_num = pn_batch_num and rtv_rec.vat_invoice_no is null then
2411 if rtv_rec.vat_invoice_action = INV_GEN_STATUS_GENERATE then
2412 lv_statement_id := '26';
2413 update JAI_RCV_TRANSACTIONS a
2414 set vat_invoice_no = lv_vat_invoice_no,
2415 vat_invoice_date = ld_vat_invoice_date,
2416 --attribute4 = pn_batch_num,
2417 last_update_date = sysdate,
2418 last_updated_by = fnd_global.user_id
2419 where current of c_rtv_trxs;
2420 lv_vat_inv_gen_action := INV_GEN_STATUS_INV_GENERATED;
2421 else
2422 lv_vat_inv_gen_action := rtv_rec.vat_invoice_action;
2423 end if;
2424 end if;
2425
2426 if rtv_rec.process_excise_in_batch = jai_constants.yes and ln_excise_batch_group_id is null then
2427 lv_statement_id := '26.1';
2428 insert_rtv_batch_group(
2429 pn_batch_group_id => ln_excise_batch_group_id,
2430 pn_batch_num => pn_batch_num,
2431 pv_regime_code => jai_constants.excise_regime,
2432 pn_organization_id => ln_organization_id,
2433 pn_location_id => ln_location_id,
2434 pn_vendor_id => ln_vendor_id,
2435 pn_vendor_site_id => ln_vendor_site_id,
2436 pv_invoice_no => lv_excise_invoice_no,
2437 pd_invoice_date => ld_excise_invoice_date,
2438 pv_process_status => lv_process_flag,
2439 pv_process_message => lv_process_message
2440 );
2441
2442 if lv_process_flag in (jai_constants.unexpected_error, jai_constants.expected_error) then
2443 RAISE_APPLICATION_ERROR( 20015, 'Error in Excise insert_rtv_batch_group. MSG:'||lv_process_message);
2444 end if;
2445 end if;
2446
2447 if rtv_rec.process_vat_in_batch = jai_constants.yes and ln_vat_batch_group_id is null then
2448 lv_statement_id := '26.2';
2449 insert_rtv_batch_group(
2450 pn_batch_group_id => ln_vat_batch_group_id,
2451 pn_batch_num => pn_batch_num,
2452 pv_regime_code => jai_constants.vat_regime,
2453 pn_organization_id => ln_organization_id,
2454 pn_location_id => ln_location_id,
2455 pn_vendor_id => ln_vendor_id,
2456 pn_vendor_site_id => ln_vendor_site_id,
2457 pv_invoice_no => lv_vat_invoice_no,
2458 pd_invoice_date => ld_vat_invoice_date,
2459 pv_process_status => lv_process_flag,
2460 pv_process_message => lv_process_message
2461 );
2462
2463 if lv_process_flag in (jai_constants.unexpected_error, jai_constants.expected_error) then
2464 RAISE_APPLICATION_ERROR( 20015, 'Error in Excise insert_rtv_batch_group. MSG:'||lv_process_message);
2465 end if;
2466 end if;
2467
2468 if rtv_rec.process_excise_in_batch = jai_constants.yes
2469 or rtv_rec.process_vat_in_batch = jai_constants.yes
2470 then
2471 update jai_rcv_rtv_batch_trxs
2472 set excise_batch_group_id = decode(rtv_rec.process_excise_in_batch, jai_constants.yes, ln_excise_batch_group_id),
2473 vat_batch_group_id = decode(rtv_rec.process_vat_in_batch, jai_constants.yes, ln_vat_batch_group_id)
2474 where transaction_id = rtv_rec.transaction_id;
2475 end if;
2476
2477 lv_statement_id := '27';
2478
2479 lv_process_flag := rtv_rec.process_status;
2480 lv_process_message := rtv_rec.process_message;
2481 lv_cenvat_rg_flag := rtv_rec.cenvat_rg_status;
2482 lv_cenvat_rg_message := rtv_rec.cenvat_rg_message;
2483 lv_common_err_mesg := null;
2484 lb_err_flag := false;
2485
2486 if rtv_rec.process_excise_in_batch = jai_constants.yes
2487 and rtv_rec.excise_invoice_action = INV_GEN_STATUS_GENERATE
2488 then
2489 lv_excise_processing_reqd := jai_constants.yes;
2490 else
2491 lv_excise_processing_reqd := jai_constants.no;
2492 end if;
2493
2494 if rtv_rec.process_vat_in_batch = jai_constants.yes
2495 and rtv_rec.vat_invoice_action in (INV_GEN_STATUS_GENERATE, INV_GEN_STATUS_INV_NA)
2496 and rtv_rec.process_vat_status <> jai_constants.successful
2497 then
2498 lv_vat_processing_reqd := jai_constants.yes;
2499 else
2500 lv_vat_processing_reqd := jai_constants.no;
2501 end if;
2502
2503 /* call to process the transactions */
2504 process_transaction(
2505 p_transaction_id => rtv_rec.transaction_id,
2506 p_process_flag => lv_process_flag,
2507 p_process_message => lv_process_message,
2508 p_cenvat_rg_flag => lv_cenvat_rg_flag,
2509 p_cenvat_rg_message => lv_cenvat_rg_message,
2510 p_common_err_mesg => lv_common_err_mesg,
2511 p_called_from => CALLED_FROM_JAITIGRTV, -- pv_called_from,
2512 p_simulate_flag => 'N',
2513 p_codepath => lv_codepath,
2514 p_excise_processing_reqd => lv_excise_processing_reqd,
2515 p_vat_processing_reqd => lv_vat_processing_reqd
2516 );
2517
2518 lv_statement_id := '28';
2519 OPEN c_trx(rtv_rec.transaction_id);
2520 FETCH c_trx INTO r_trx_after_processing;
2521 CLOSE c_trx;
2522
2523 lv_statement_id := '29';
2524 IF lv_common_err_mesg IS NOT NULL THEN
2525 FND_FILE.put_line(FND_FILE.log, '*** Common Error for Transaction_id:'||rtv_rec.transaction_id
2526 ||fnd_global.local_chr(10)||fnd_global.local_chr(9)||'- Error:'||lv_common_err_mesg
2527 );
2528 lb_err_flag := true;
2529 goto end_of_trx;
2530 -- lv_err_mesg := lv_common_err_mesg;
2531 ELSIF lv_process_flag IN ('E') AND lv_cenvat_rg_flag IN ('E') THEN
2532 FND_FILE.put_line(FND_FILE.log, '*** FLAGS ERROR *** Transaction_id:'||rtv_rec.transaction_id
2533 ||fnd_global.local_chr(10)||fnd_global.local_chr(9)||'- ProcessErr:'||lv_process_message
2534 ||fnd_global.local_chr(10)||fnd_global.local_chr(9)||'- CenvatErr:'||lv_cenvat_rg_message
2535 );
2536 lb_err_flag := true;
2537 goto end_of_trx;
2538 ELSIF lv_process_flag IN ('E') THEN
2539 FND_FILE.put_line(FND_FILE.log, '*** PROCESS ERROR *** Transaction_id:'||rtv_rec.transaction_id
2540 ||fnd_global.local_chr(10)||fnd_global.local_chr(9)||'- ErrorMessage:'||lv_process_message
2541 );
2542 lb_err_flag := true;
2543 goto end_of_trx;
2544 ELSIF lv_cenvat_rg_flag IN ('E') THEN
2545 FND_FILE.put_line(FND_FILE.log, '*** CENVAT ERROR *** Transaction_id:'||rtv_rec.transaction_id
2546 ||fnd_global.local_chr(10)||fnd_global.local_chr(9)||'- ErrorMessage:'||lv_cenvat_rg_message
2547 );
2548 lb_err_flag := true;
2549 goto end_of_trx;
2550 ELSIF r_trx_after_processing.process_vat_status in ('E', jai_constants.unexpected_error, jai_constants.expected_error) THEN
2551 FND_FILE.put_line(FND_FILE.log, '*** VAT Message *** Transaction_id:'||rtv_rec.transaction_id
2552 ||fnd_global.local_chr(10)||fnd_global.local_chr(9)||', Flag:'||r_trx_after_processing.process_vat_status
2553 ||' - ErrorMessage:'||r_trx_after_processing.process_vat_message
2554 );
2555 lb_err_flag := true;
2556 goto end_of_trx;
2557 END IF;
2558
2559 lv_statement_id := '30';
2560 IF lv_process_flag IN ('X') AND lv_cenvat_rg_flag IN ('X') THEN
2561 FND_FILE.put_line(FND_FILE.log, 'Transaction_Id:'||rtv_rec.transaction_id
2562 ||fnd_global.local_chr(10)||fnd_global.local_chr(9)||'- ProcessMessage(X):'||lv_process_message
2563 ||fnd_global.local_chr(10)||fnd_global.local_chr(9)||'- CenvatMessgae(X):'||lv_cenvat_rg_message
2564 );
2565 ELSIF lv_process_flag IN ('X') THEN
2566 FND_FILE.put_line(FND_FILE.log, 'Process Message(X):'||lv_process_message);
2567 ELSIF lv_cenvat_rg_flag IN ('X') THEN
2568 FND_FILE.put_line(FND_FILE.log, 'Cenvat Messgae(X):'||lv_cenvat_rg_message);
2569 END IF;
2570
2571 lv_statement_id := '31';
2572 IF lv_process_flag = 'E' OR lv_cenvat_rg_flag = 'E' THEN
2573 lb_err_flag := true;
2574 goto end_of_trx;
2575 -- retcode := 1;
2576 END IF;
2577
2578 /* following procedure call is a replacement for SSI functinoality of JAINRTVN. This is created due to DFF removal */
2579 if lv_excise_processing_reqd = jai_constants.yes and rtv_rec.receipt_excise_rate <> rtv_rec.rtv_excise_rate then
2580 jai_rcv_excise_processing_pkg.rtv_processing_for_ssi(
2581 pn_transaction_id => rtv_rec.transaction_id,
2582 pv_codepath => lv_codepath,
2583 pv_process_status => lv_ssi_cenvat_rg_flag,
2584 pv_process_message => lv_ssi_cenvat_rg_message
2585 );
2586
2587 if lv_ssi_cenvat_rg_flag = 'E' then
2588 lb_err_flag := true;
2589 goto end_of_trx;
2590 end if;
2591 end if;
2592
2593 lv_statement_id := '31.1';
2594 jai_rcv_transactions_pkg.update_attributes(
2595 p_transaction_id => rtv_rec.transaction_id,
2596 p_attribute1 => lv_excise_inv_gen_action,
2597 p_attribute2 => lv_vat_inv_gen_action
2598 );
2599
2600 lv_statement_id := '32';
2601 /* Following code is a replacement for removal of ja_in_create_rcv_57f4_trg trigger
2602 as part of RTV DFF Elimination */
2603 -- if r_trx.attribute1 = INV_GEN_STATUS_INV_GENERATED then
2604 if lv_excise_processing_reqd = jai_constants.yes then
2605 jai_po_osp_pkg.create_rcv_57f4(
2606 p_transaction_id => rtv_rec.transaction_id,
2607 p_process_status => lv_process_flag,
2608 p_process_message => lv_process_message
2609 );
2610 end if;
2611
2612 lv_statement_id := '33';
2613 if lv_process_flag in (jai_constants.unexpected_error, jai_constants.expected_error) then
2614 FND_FILE.put_line(FND_FILE.log, 'Error in Call to jai_po_osp_pkg.create_rcv_57f4. Mesg:'||lv_process_message);
2615 lv_process_flag := 'E';
2616 lb_err_flag := true;
2617 goto end_of_trx;
2618 end if;
2619 -- end if;
2620
2621 lv_statement_id := '35';
2622 UPDATE JAI_RCV_TRANSACTIONS
2623 SET codepath = lv_codepath
2624 WHERE current of c_rtv_trxs;
2625
2626 <<end_of_trx>>
2627 if lb_err_flag then
2628 fnd_file.put_line( fnd_file.log, 'ErrCodepath:'||lv_codepath);
2629 raise_application_error( -20012, 'Error during RTV Processing. Look at the log for details');
2630 end if;
2631
2632 ln_processed_cnt := ln_processed_cnt + 1;
2633 lv_common_err_mesg := null;
2634 lv_codepath := '';
2635 end loop;
2636
2637 if pv_commit_switch = jai_constants.yes then
2638 COMMIT;
2639 -- ROLLBACK;
2640 end if;
2641
2642 EXCEPTION
2643 WHEN OTHERS THEN
2644 ROLLBACK;
2645 pv_retcode := jai_constants.request_error;
2646 pv_errbuf := 'Error(StmtId:'||lv_statement_id||')-'||SQLERRM;
2647 FND_FILE.put_line( FND_FILE.log, pv_errbuf);
2648 -- FND_FILE.put_line( FND_FILE.log, 'Error Path:'||lv_codepath);
2649 end process_rtv;
2650
2651
2652 PROCEDURE process_transaction(
2653 p_transaction_id IN NUMBER,
2654 p_process_flag IN OUT NOCOPY VARCHAR2,
2655 p_process_message IN OUT NOCOPY VARCHAR2,
2656 p_cenvat_rg_flag IN OUT NOCOPY VARCHAR2,
2657 p_cenvat_rg_message IN OUT NOCOPY VARCHAR2,
2658 p_common_err_mesg OUT NOCOPY VARCHAR2,
2659 p_called_from IN VARCHAR2,
2660 p_simulate_flag IN VARCHAR2,
2661 p_codepath IN OUT NOCOPY VARCHAR2,
2662 -- following parameters introduced for second claim of receive transaction
2663 p_process_special_reason IN VARCHAR2 DEFAULT NULL,
2664 p_process_special_qty IN NUMBER DEFAULT NULL,
2665 /*Vijay Shankar for Bug#4346453. RCV DFF Elim. Enh.*/
2666 p_excise_processing_reqd IN VARCHAR2, --File.Sql.35 Cbabu DEFAULT jai_constants.yes,
2667 p_vat_processing_reqd IN VARCHAR2 --File.Sql.35 Cbabu DEFAULT jai_constants.yes
2668 ) IS
2669
2670 --added by ssawant
2671 CURSOR c_base_trx(cp_transaction_id IN NUMBER) IS
2672 SELECT shipment_header_id, shipment_line_id, transaction_type, quantity, unit_of_measure, uom_code,
2673 parent_transaction_id, organization_id, location_id, subinventory, currency_conversion_rate,
2674 attribute_category attr_cat, nvl(attribute5, 'XX') rma_type, nvl(attribute4, 'N') generate_excise_invoice
2675 , routing_header_id -- porting of Bug#3949109 (3927371)
2676 , attribute3 online_claim_flag, source_document_code, po_header_id -- Vijay Shankar for Bug#3940588
2677 , po_line_location_id
2678 FROM rcv_transactions
2679 WHERE transaction_id = cp_transaction_id;
2680
2681 r_trx c_trx%ROWTYPE;
2682 r_base_trx c_base_trx%ROWTYPE;/*bgowrava for forward porting Bug#5756676*/
2683
2684 lv_transaction_type JAI_RCV_TRANSACTIONS.transaction_type%TYPE;
2685 ln_cenvat_claimed_ptg NUMBER;
2686
2687 lv_object_code VARCHAR2(10); --File.Sql.35 Cbabu := 'RCPT_TRXN:';
2688 lv_statement_id VARCHAR2(5);
2689
2690 lv_execution_point VARCHAR2(30); --File.Sql.35 Cbabu := 'COMMON_CODE';
2691 lv_temp VARCHAR2(100);
2692
2693 lv_process_vat_flag JAI_RCV_TRANSACTIONS.PROCESS_VAT_STATUS%TYPE;
2694 lv_process_vat_message JAI_RCV_TRANSACTIONS.process_vat_message%TYPE;
2695
2696 lv_process_status VARCHAR2(2); --added by ssawant
2697 lv_qty_register_entry_type VARCHAR2(2); /*bug 7662347*/
2698
2699 BEGIN
2700
2701 lv_object_code := 'RCPT_TRXN:';
2702 lv_execution_point := 'COMMON_CODE';
2703
2704 -- this is to identify the path in SQL TRACE file if any problem occured
2705 SELECT to_char(sysdate,'dd/mm/yyyy hh24:mi:ss')||'-jirt_pkg.process_transaction-'||p_transaction_id INTO lv_temp FROM DUAL;
2706
2707 FND_FILE.put_line(FND_FILE.log, '^Start of Trx:'||p_transaction_id||'. Time:'||to_char(SYSDATE,'dd/mm/yyyy hh24:mi:ss')
2708 ||', PrcSpecialReason:'||p_process_special_reason||', PrcSplQty:'||p_process_special_qty
2709 );
2710
2711 p_codepath := jai_general_pkg.plot_codepath(1, p_codepath, 'receipt_transactions_pkg.process_transaction', 'START');
2712
2713 lv_statement_id := '1';
2714
2715 --added the cursor and 2 assignments here by Ramananda for bug#4519697
2716 OPEN c_trx(p_transaction_id);
2717 FETCH c_trx INTO r_trx;
2718 CLOSE c_trx;
2719
2720 lv_process_vat_flag := r_trx.process_vat_status; --Process_vat_flag ; -- Ramananda for bug#4519697
2721 lv_process_vat_message := r_trx.process_vat_message;
2722
2723
2724 OPEN c_base_trx(p_transaction_id); /*bgowrava for forward porting Bug#5756676*/
2725 FETCH c_base_trx INTO r_base_trx;
2726 CLOSE c_base_trx;
2727
2728 validate_transaction(
2729 p_transaction_id => p_transaction_id,
2730 p_process_flag => p_process_flag,
2731 p_process_message => p_process_message,
2732 p_cenvat_rg_flag => p_cenvat_rg_flag,
2733 p_cenvat_rg_message => p_cenvat_rg_message,
2734 /* following two flags introduced for VAT implementation. Vijay Shankar for Bug#4250236(4245089) */
2735 p_process_vat_flag => lv_process_vat_flag,
2736 p_process_vat_message => lv_process_vat_message,
2737 p_called_from => p_called_from,
2738 p_simulate_flag => p_simulate_flag,
2739 p_codepath => p_codepath
2740 );
2741
2742 p_codepath := jai_general_pkg.plot_codepath(2, p_codepath);
2743
2744 -- ERROR occured in Validate transaction. So proceed with next transaction
2745 -- We should not check for 'X' here. Because Cenvat can be 'N' which should be processed
2746 -- process_flag = 'E' means somehow an error occured in Validate transaction
2747 IF p_process_flag = 'E' THEN
2748 p_common_err_mesg := p_process_message;
2749 GOTO exit_processing;
2750 END IF;
2751
2752 lv_statement_id := '2';
2753 --removed the cursor c_trx() from here by Ramananda for bug#4519697
2754
2755 IF r_trx.transaction_type = 'CORRECT' THEN
2756 lv_transaction_type := r_trx.parent_transaction_type;
2757 ELSE
2758 lv_transaction_type := r_trx.transaction_type;
2759 END IF;
2760
2761 -- "MATCH" included by Vijay Shankar for Bug#3940588
2762 IF lv_transaction_type NOT IN ( 'RECEIVE', 'DELIVER', 'RETURN TO RECEIVING', 'RETURN TO VENDOR', 'MATCH' ) THEN
2763 -- Localization donot support these transactions. So, Pls return back
2764 FND_FILE.put_line(FND_FILE.log, lv_object_code||'Localization doesnot support this transaction type');
2765 RETURN;
2766 END IF;
2767
2768 p_codepath := jai_general_pkg.plot_codepath(3, p_codepath);
2769
2770 ------------ Start of PROCESS_FLAG Execution ----------
2771 -- IF p_process_flag IN ('N','E') THEN
2772 IF p_simulate_flag = 'Y'
2773 OR p_process_flag IN ('N')
2774 THEN
2775
2776 lv_statement_id := '3';
2777 SAVEPOINT process_trxn_flag;
2778
2779 lv_execution_point := 'START_PROCESS_FLAG';
2780
2781 IF lv_transaction_type IN ( 'RECEIVE', 'RETURN TO VENDOR') THEN
2782 lv_statement_id := '4';
2783 p_codepath := jai_general_pkg.plot_codepath(4, p_codepath);
2784
2785 jai_rcv_rcv_rtv_pkg.process_transaction(
2786 p_transaction_id => p_transaction_id,
2787 p_simulation => p_simulate_flag,
2788 p_process_flag => p_process_flag,
2789 p_process_message => p_process_message,
2790 p_debug => lv_debug,
2791 p_codepath => p_codepath
2792 );
2793
2794 ELSIF lv_transaction_type IN ('DELIVER', 'RETURN TO RECEIVING') THEN
2795
2796 lv_statement_id := '5';
2797 p_codepath := jai_general_pkg.plot_codepath(5, p_codepath);
2798
2799 jai_rcv_deliver_rtr_pkg.process_transaction (
2800 p_transaction_id => p_transaction_id,
2801 p_simulate => p_simulate_flag,
2802 p_codepath => p_codepath,
2803 p_process_status => p_process_flag,
2804 p_process_message => p_process_message
2805 );
2806
2807 ELSE
2808 FND_FILE.put_line( FND_FILE.log, '1****** No Codepath ******');
2809 END IF;
2810
2811 lv_execution_point := 'END_PROCESS_FLAG';
2812
2813 lv_statement_id := '6';
2814 IF p_process_flag IN ('E', 'X') THEN
2815 FND_FILE.put_line(FND_FILE.log, 'PRC_FLG_Error: RollingBack to process_trxn_flag');
2816 p_codepath := jai_general_pkg.plot_codepath(6, p_codepath);
2817 ROLLBACK TO process_trxn_flag;
2818 ElSIF p_process_flag = 'Y' THEN
2819 p_codepath := jai_general_pkg.plot_codepath(7, p_codepath);
2820 p_process_message := 'Successful';
2821 ELSE
2822 FND_FILE.put_line(FND_FILE.log, '1#PRC_FLG#'||p_process_flag);
2823 p_codepath := jai_general_pkg.plot_codepath(8, p_codepath);
2824 END IF;
2825
2826 END IF;
2827
2828 FND_FILE.put_line(FND_FILE.log, '2#PRC_FLG#'||p_process_flag);
2829 ------------ End of PROCESS_FLAG Execution ----------
2830
2831 lv_statement_id := '7';
2832 ln_cenvat_claimed_ptg := r_trx.cenvat_claimed_ptg;
2833 FND_FILE.put_line(FND_FILE.log, 'r_trx.item_class:'||r_trx.item_class);
2834 FND_FILE.put_line(FND_FILE.log, 'lv_online_qty_flag:'||lv_online_qty_flag);
2835
2836 /*bgowrava for forward porting Bug#5756676..start*/
2837 IF lv_online_qty_flag = 'Y' THEN
2838
2839 SAVEPOINT process_online_qty;
2840
2841 IF r_base_trx.attr_cat = 'India RMA Receipt' AND r_trx.item_class IN ('FGIN', 'FGEX', 'CCIN', 'CCEX') THEN
2842
2843 FND_FILE.put_line(FND_FILE.log, 'Calling ja_in_receipt_cenvat_rg_pkg.rg_i_entry');
2844
2845 /*bug 7662347*/
2846 IF lv_transaction_type = 'RETURN TO VENDOR' THEN
2847 lv_qty_register_entry_type := 'Dr';
2848 ELSE
2849 lv_qty_register_entry_type := 'Cr';
2850 END IF;
2851
2852 jai_rcv_excise_processing_pkg.rg_i_entry(
2853 p_transaction_id => r_trx.transaction_id,
2854 pr_tax => NULL,
2855 p_register_entry_type => lv_qty_register_entry_type, /*bug 7662347*/
2856 p_register_id => ln_part_i_register_id,
2857 p_process_status => lv_process_status,
2858 p_process_message => lv_process_message,
2859 p_simulate_flag => p_simulate_flag,
2860 p_codepath => p_codepath
2861 );
2862
2863 FND_FILE.put_line(FND_FILE.log, 'ln_part_i_register_id:'||ln_part_i_register_id);
2864
2865 ELSIF r_trx.item_class IN ('RMIN', 'RMEX', 'CCIN', 'CCEX', 'CGIN', 'CGEX') THEN
2866
2867 lv_register_type := jai_general_pkg.get_rg_register_type( p_item_class => r_trx.item_class);
2868
2869 FND_FILE.put_line(FND_FILE.log, 'Register Type:'||lv_register_type);
2870
2871 -- to determine the way in which CGIN Items are Processed
2872 IF lv_register_type = 'C' THEN
2873 jai_rcv_excise_processing_pkg.derive_cgin_scenario(
2874 p_transaction_id => p_transaction_id,
2875 p_cgin_code => lv_cgin_code,
2876 p_process_status => lv_process_status,
2877 p_process_message => lv_process_message,
2878 p_codepath => p_codepath
2879 );
2880
2881 FND_FILE.put_line(FND_FILE.log, 'CGIN_CODE->'||lv_cgin_code);
2882 END IF;
2883 -- RG23 Part I Entry is already made during first Claim, in case of CGIN Items
2884 -- So no need of another entry during Second 50% Claim of CENVAT
2885 IF nvl(lv_cgin_code, 'XXX') <> 'REGULAR-HALF' THEN
2886
2887 FND_FILE.put_line(FND_FILE.log, 'Calling ja_in_receipt_cenvat_rg_pkg.rg23_part_i_entry');
2888
2889 /*bug 7662347*/
2890 IF lv_transaction_type = 'RETURN TO VENDOR' THEN
2891 lv_qty_register_entry_type := 'Dr';
2892 ELSE
2893 lv_qty_register_entry_type := 'Cr';
2894 END IF;
2895
2896 jai_rcv_excise_processing_pkg.rg23_part_i_entry(
2897 p_transaction_id => r_trx.transaction_id,
2898 pr_tax => NULL,
2899 p_register_entry_type => lv_qty_register_entry_type, /*bug 7662347*/
2900 p_register_id => ln_part_i_register_id,
2901 p_process_status => lv_process_status,
2902 p_process_message => lv_process_message,
2903 p_simulate_flag => p_simulate_flag,
2904 p_codepath => p_codepath
2905 );
2906
2907 --lv_qty_register := 'RG23';
2908
2909 ELSE
2910 FND_FILE.put_line( FND_FILE.log, 'No Call to RG23_PART_I_ENTRY');
2911 END IF;
2912
2913 END IF;
2914
2915 IF lv_process_status in ('E','X') THEN
2916 ROLLBACK TO PROCESS_ONLINE_QTY;
2917 END IF;
2918
2919 END IF;
2920
2921 /*bgowrava for forward porting Bug#5756676..end*/
2922
2923 /* RG/Cenvat Works only incase if its not a Simulation. Because simulation is not implemented for CENVAT Part */
2924 IF p_simulate_flag = 'N'
2925 AND ( -- Vijay Shankar for Bug#4346453. RCV DFF Elim. Enh. p_cenvat_rg_flag IN ('N', 'E')
2926 -- condition modified as part of DFF Elimination. Vijay Shankar for Bug#4346453. RCV DFF Elim. Enh.
2927 ( p_excise_processing_reqd = jai_constants.yes -- Vijay Shankar for Bug#4346453. RCV DFF Elim. Enh.
2928 AND p_cenvat_rg_flag IN ('N', 'E')
2929 AND (r_trx.transaction_type <> 'RETURN TO VENDOR'
2930 OR (r_trx.transaction_type = 'RETURN TO VENDOR' and p_called_from = CALLED_FROM_JAITIGRTV) -- nvl(r_trx.attribute1,'XXX')<> INV_GEN_STATUS_PENDING)
2931 )
2932 )
2933 -- following condition will be satisfied during 2nd 50% claim of CGIN items when called from JAINMVAT form
2934 -- Vijay Shankar for Bug#3940588. RECEIPTS DEPLUG
2935 OR (p_called_from='JAINMVAT' AND r_trx.cenvat_rg_status='Y')
2936 OR (p_process_special_reason = jai_rcv_excise_processing_pkg.second_50ptg_claim AND ln_cenvat_claimed_ptg < 100)
2937 )
2938 THEN
2939
2940 SAVEPOINT process_cenvat_rg_flag;
2941
2942 lv_execution_point := 'START_CENVAT_FLAG';
2943
2944 IF lv_transaction_type IN ( 'RECEIVE', 'RETURN TO VENDOR', 'DELIVER', 'RETURN TO RECEIVING') THEN
2945 -- this call passes the cenvat related accounting and register entries based on transaction type
2946 lv_statement_id := '8';
2947 p_codepath := jai_general_pkg.plot_codepath(9, p_codepath);
2948
2949 jai_rcv_excise_processing_pkg.process_transaction(
2950 p_transaction_id => p_transaction_id,
2951 p_process_status => p_cenvat_rg_flag,
2952 p_cenvat_claimed_ptg => ln_cenvat_claimed_ptg,
2953 p_process_message => p_cenvat_rg_message,
2954 p_simulate_flag => p_simulate_flag,
2955 p_codepath => p_codepath,
2956 -- following added by Vijay Shankar for Bug#3940588. RECEIPTS DEPLUG
2957 p_process_special_reason => p_process_special_reason,
2958 p_process_special_qty => p_process_special_qty
2959 );
2960
2961 ELSE
2962 FND_FILE.put_line( FND_FILE.log, lv_object_code||'Transaction type Not supported for cenvat Entries');
2963 END IF;
2964
2965 lv_execution_point := 'END_CENVAT_FLAG';
2966
2967 lv_statement_id := '9';
2968 IF p_cenvat_rg_flag IN ('E', 'X') THEN
2969 FND_FILE.put_line(FND_FILE.log, 'CEN_FLG_Error: RollingBack to process_cenvat_rg_flag');
2970 p_codepath := jai_general_pkg.plot_codepath(10, p_codepath);
2971 ROLLBACK TO process_cenvat_rg_flag;
2972 ElSIF p_cenvat_rg_flag = 'Y' THEN
2973 p_cenvat_rg_message := 'Successful';
2974 p_codepath := jai_general_pkg.plot_codepath(11, p_codepath);
2975 ELSE
2976 FND_FILE.put_line(FND_FILE.log, '1#CENVAT_FLG#'||p_cenvat_rg_flag);
2977 p_codepath := jai_general_pkg.plot_codepath(12, p_codepath);
2978 END IF;
2979
2980 END IF;
2981
2982 FND_FILE.put_line(FND_FILE.log, '2#CENVAT_FLG#'||p_cenvat_rg_flag);
2983 /* End of CENVAT_RG_FLAG Execution */
2984
2985 lv_statement_id := '10';
2986 p_codepath := jai_general_pkg.plot_codepath(13, p_codepath);
2987
2988 /* Start of VAT Execution. Vijay Shankar for Bug#4250236(4245089) */
2989 IF p_simulate_flag = 'N'
2990 AND -- Vijay Shankar for Bug#4346453. RCV DFF Elim. Enh. lv_process_vat_flag IN ('N', 'E')
2991 ( p_vat_processing_reqd = jai_constants.yes -- Vijay Shankar for Bug#4346453. RCV DFF Elim. Enh.
2992 AND lv_process_vat_flag IN ('N', 'E', jai_constants.expected_error, jai_constants.unexpected_error)
2993 AND (r_trx.transaction_type <> 'RETURN TO VENDOR'
2994 OR (r_trx.transaction_type = 'RETURN TO VENDOR' and p_called_from = CALLED_FROM_JAITIGRTV)
2995 )
2996 )
2997 THEN
2998
2999 SAVEPOINT process_vat_flag;
3000
3001 lv_execution_point := 'START_PROCESS_VAT';
3002
3003 IF lv_transaction_type IN ( 'RECEIVE', 'RETURN TO VENDOR') THEN
3004 -- this call passes the cenvat related accounting and register entries based on transaction type
3005 lv_statement_id := '11';
3006 p_codepath := jai_general_pkg.plot_codepath(14, p_codepath);
3007
3008 jai_rcv_rgm_claims_pkg.process_vat(
3009 p_transaction_id => p_transaction_id,
3010 p_process_status => lv_process_vat_flag,
3011 p_process_message => lv_process_vat_message
3012 );
3013
3014 ELSE
3015 FND_FILE.put_line( FND_FILE.log, lv_object_code||'Trxn not supported for VAT processing');
3016 END IF;
3017
3018 lv_execution_point := 'END_PROCESS_VAT';
3019
3020 lv_statement_id := '12';
3021 IF lv_process_vat_flag = jai_constants.successful THEN
3022 lv_process_vat_message := 'Successful';
3023 p_codepath := jai_general_pkg.plot_codepath(16, p_codepath);
3024 ELSIF lv_process_vat_flag <> jai_constants.successful THEN
3025 FND_FILE.put_line(FND_FILE.log, 'PrcVatFlg Err: RollingBack to process_vat_flag. Mesg:'||lv_process_vat_message);
3026 p_codepath := jai_general_pkg.plot_codepath(15, p_codepath);
3027 ROLLBACK TO process_vat_flag;
3028 ELSE
3029 FND_FILE.put_line(FND_FILE.log, '1#PrcVatFlg#'||lv_process_vat_flag);
3030 p_codepath := jai_general_pkg.plot_codepath(17, p_codepath);
3031 END IF;
3032
3033 END IF;
3034
3035 FND_FILE.put_line(FND_FILE.log, '2#PrcVatFlg#'||lv_process_vat_flag);
3036 /* End of VAT Execution */
3037
3038 lv_statement_id := '14';
3039 p_codepath := jai_general_pkg.plot_codepath(18, p_codepath);
3040
3041 IF p_simulate_flag = 'N' THEN
3042
3043 jai_rcv_transactions_pkg.update_process_flags(
3044 p_transaction_id => p_transaction_id,
3045 p_process_flag => p_process_flag,
3046 p_process_message => p_process_message,
3047 p_cenvat_rg_flag => p_cenvat_rg_flag,
3048 p_cenvat_claimed_ptg => ln_cenvat_claimed_ptg,
3049 p_cenvat_rg_message => p_cenvat_rg_message,
3050 p_process_vat_flag => lv_process_vat_flag,
3051 p_process_vat_message => lv_process_vat_message,
3052 /*Vijay Shankar for Bug#4250171 p_process_vat_flag => null,
3053 p_process_vat_message => null,
3054 */p_process_date => SYSDATE
3055 );
3056
3057 END IF;
3058 <<exit_processing>>
3059
3060 FND_FILE.put_line(FND_FILE.log, '$End of Trx:'||p_transaction_id||'. Time:'||to_char(SYSDATE,'dd/mm/yyyy hh24:mi:ss'));
3061
3062 p_codepath := jai_general_pkg.plot_codepath(99, p_codepath, null, 'END');
3063
3064 EXCEPTION
3065 WHEN OTHERS THEN
3066 p_common_err_mesg := 'RECEIPT_TRANSACTIONS_PKG.process_transaction(StmtId:'||lv_statement_id||'). Error:'||SQLERRM;
3067 FND_FILE.put_line( FND_FILE.log, 'Error in '||p_common_err_mesg);
3068 IF p_process_flag <> 'Y' THEN
3069 p_process_flag := 'E';
3070 p_process_message := p_common_err_mesg;
3071 ELSIF p_cenvat_rg_flag <> 'Y' THEN
3072 p_cenvat_rg_flag := 'E';
3073 p_cenvat_rg_message := p_common_err_mesg;
3074 ELSE
3075 -- dont update any of the fields of JAI_RCV_TRANSACTIONS table
3076 NULL;
3077 END IF;
3078 p_codepath := jai_general_pkg.plot_codepath(999, p_codepath, null, 'END');
3079
3080 END process_transaction;
3081
3082 /* ~~~~~~~~~~~~~~~~~~~~ POPULATION of DETAILS Procedure ~~~~~~~~~~~~~~~~~~~~~~~~*/
3083
3084 PROCEDURE populate_details(
3085 p_transaction_id IN NUMBER,
3086 p_process_status OUT NOCOPY VARCHAR2,
3087 p_process_message OUT NOCOPY VARCHAR2,
3088 p_simulate_flag IN VARCHAR2,
3089 p_codepath IN OUT NOCOPY VARCHAR2
3090 ) IS
3091
3092 CURSOR c_shp_line_dtls(cp_shipment_line_id IN NUMBER) IS
3093 SELECT shipment_line_id, item_id
3094 FROM rcv_shipment_lines
3095 WHERE shipment_line_id = cp_shipment_line_id;
3096
3097 CURSOR c_shp_hdr_dtls(cp_shipment_header_id IN NUMBER) IS
3098 SELECT shipment_header_id, receipt_num
3099 FROM rcv_shipment_headers
3100 WHERE shipment_header_id = cp_shipment_header_id;
3101
3102 CURSOR c_loc_item_dtls(cp_organization_id IN NUMBER, cp_inventory_item_id IN NUMBER) IS
3103 SELECT item_class, modvat_flag, excise_flag, item_trading_flag
3104 FROM JAI_INV_ITM_SETUPS
3105 WHERE organization_id = cp_organization_id
3106 AND inventory_item_id = cp_inventory_item_id;
3107
3108 CURSOR c_base_item_dtls(cp_organization_id IN NUMBER, cp_inventory_item_id IN NUMBER) IS
3109 SELECT inventory_item_flag, inventory_asset_flag
3110 FROM mtl_system_items
3111 WHERE organization_id = cp_organization_id
3112 AND inventory_item_id = cp_inventory_item_id;
3113
3114 CURSOR c_loc_orgn_dtls(cp_organization_id IN NUMBER) IS
3115 SELECT decode( manufacturing, 'Y', 'M', decode(trading, 'Y', 'T', 'X')) organization_type, excise_in_rg23d
3116 FROM JAI_CMN_INVENTORY_ORGS
3117 WHERE organization_id = cp_organization_id
3118 AND location_id = 0;
3119
3120 CURSOR c_mtl_params(cp_organization_id IN NUMBER) IS
3121 SELECT primary_cost_method
3122 FROM mtl_parameters
3123 WHERE organization_id = cp_organization_id;
3124
3125 CURSOR c_inv_org_linked_to_location(cp_location_id IN NUMBER) IS
3126 SELECT nvl(inventory_organization_id, -99999) inventory_organization_id
3127 FROM hr_locations_all
3128 WHERE location_id = cp_location_id;
3129
3130 CURSOR c_loc_linked_to_org_subinv(cp_organization_id IN NUMBER, cp_subinventory IN VARCHAR2) IS
3131 SELECT location_id, decode(bonded, 'Y', 'B', decode(trading, 'Y', 'T', 'N')) loc_subinventory_type
3132 FROM JAI_INV_SUBINV_DTLS
3133 WHERE organization_id = cp_organization_id
3134 AND sub_inventory_name = cp_subinventory;
3135
3136 CURSOR c_base_subinv_dtls(cp_organization_id IN NUMBER, cp_subinventory IN VARCHAR2) IS
3137 SELECT asset_inventory
3138 FROM mtl_secondary_inventories
3139 WHERE organization_id = cp_organization_id
3140 AND secondary_inventory_name = cp_subinventory;
3141
3142 -- porting from Bug#3949109 (3927371)
3143 CURSOR c_dlry_subinventory(cp_shipment_line_id IN NUMBER, cp_receive_trx_id IN NUMBER, cp_transaction_type rcv_transactions.transaction_type%type) IS
3144 SELECT subinventory
3145 FROM rcv_transactions
3146 WHERE shipment_line_id = cp_shipment_line_id
3147 AND parent_transaction_id = cp_receive_trx_id
3148 AND transaction_type = cp_transaction_type --'DELIVER'
3149 AND transaction_id > cp_receive_trx_id;
3150
3151 -- Start, Vijay Shankar for Bug#3940588
3152 ln_tax_transaction_id JAI_RCV_TRANSACTIONS.tax_transaction_id%TYPE;
3153 ln_non_po_vendor_cnt NUMBER;
3154 lv_third_party_flag VARCHAR2(1);
3155
3156 CURSOR c_non_po_vendor_cnt(cp_shipment_header_id IN NUMBER, cp_shipment_line_id IN NUMBER) IS /* Modified by Ramananda for removal of SQL LITERALs :bug#4428980*/
3157 SELECT nvl(count(distinct vendor_id), 0)
3158 from JAI_RCV_LINE_TAXES
3159 where shipment_line_id = cp_shipment_line_id
3160 AND vendor_id <> (SELECT vendor_id
3161 FROM rcv_shipment_headers
3162 WHERE shipment_header_id = cp_shipment_header_id
3163 )
3164 AND upper(tax_type) NOT IN (jai_constants.tax_type_tds, jai_constants.tax_type_modvat_recovery) --('TDS', 'MODVAT RECOVERY')
3165 AND tax_amount > 0 ;
3166 -- End, Vijay Shankar for Bug#3940588
3167 /*
3168 || srjayara bug 5064235 -- forward porting for bug# 5054114
3169 || Cursor fetches the subinventory from rcv_transactiosn for DELIVER line
3170 || based on shipment_line_id of transaction_id passed to the cursor
3171 */
3172 CURSOR cur_dlry_subinv ( cp_transaction_id IN NUMBER ) IS
3173 SELECT subinventory
3174 FROM rcv_transactions
3175 WHERE transaction_type = 'DELIVER'
3176 AND shipment_line_id = (SELECT shipment_line_id
3177 FROM jai_rcv_transactions
3178 WHERE transaction_id = cp_transaction_id);
3179
3180 r_base_trx c_base_trx%ROWTYPE;
3181 r_parent_base_trx c_base_trx%ROWTYPE;
3182
3183 -- Vijay Shankar for Bug#4038024
3184 lv_required_trx_type RCV_TRANSACTIONS.transaction_type%TYPE;
3185
3186 r_ancestor_dtls c_base_trx%ROWTYPE;
3187 r_shp_line_dtls c_shp_line_dtls%ROWTYPE;
3188 r_shp_hdr_dtls c_shp_hdr_dtls%ROWTYPE;
3189 r_loc_item_dtls c_loc_item_dtls%ROWTYPE;
3190 r_base_item_dtls c_base_item_dtls%ROWTYPE;
3191 r_loc_orgn_dtls c_loc_orgn_dtls%ROWTYPE;
3192 r_mtl_params c_mtl_params%ROWTYPE;
3193 r_base_subinv_dtls c_base_subinv_dtls%ROWTYPE;
3194 r_subinv_dtls c_loc_linked_to_org_subinv%ROWTYPE;
3195
3196
3197 ln_location_id NUMBER(15);
3198 ln_organization_id NUMBER(15);
3199 lv_subinventory RCV_TRANSACTIONS.subinventory%TYPE;
3200 lv_transaction_type RCV_TRANSACTIONS.transaction_type%TYPE;
3201 ln_ancestor_trxn_id NUMBER(15);
3202
3203 ln_tax_amount NUMBER;
3204 ln_cenvat_amount NUMBER;
3205 lv_loc_subinv_type JAI_RCV_TRANSACTIONS.loc_subinv_type%TYPE;
3206 lv_base_subinv_asset_flag JAI_RCV_TRANSACTIONS.BASE_ASSET_INVENTORY%TYPE;
3207
3208 r_exc_inv_no c_excise_invoice_no%ROWTYPE;
3209
3210 /* Vijay Shankar for Bug#4250171 */
3211 r_trx c_trx%ROWTYPE;
3212 r_parent_trx c_trx%ROWTYPE; -- JAI_RCV_TRANSACTIONS record
3213
3214 /* Added by Ramananda for bug#4407165 */
3215 lv_object_name CONSTANT VARCHAR2(61) := 'jai_rcv_trx_processing_pkg.populate_details';
3216 ln_tax_apportion_factor jai_rcv_transactions.tax_apportion_factor%TYPE; --Added by Sanjikum for Bug#4495135
3217
3218 BEGIN
3219
3220 IF lb_debug THEN
3221 FND_FILE.put_line(FND_FILE.log, '^ POPULATE_DETAILS');
3222 END IF;
3223
3224 p_codepath := jai_general_pkg.plot_codepath(1, p_codepath, 'populate_details', 'START');
3225
3226 OPEN c_base_trx(p_transaction_id);
3227 FETCH c_base_trx INTO r_base_trx;
3228 CLOSE c_base_trx;
3229
3230 /* Vijay Shankar for Bug#4250171 */
3231 OPEN c_trx(p_transaction_id);
3232 FETCH c_trx INTO r_trx;
3233 CLOSE c_trx;
3234
3235 IF r_base_trx.parent_transaction_id > 0 THEN
3236 p_codepath := jai_general_pkg.plot_codepath(2, p_codepath);
3237 OPEN c_base_trx(r_base_trx.parent_transaction_id);
3238 FETCH c_base_trx INTO r_parent_base_trx;
3239 CLOSE c_base_trx;
3240 END IF;
3241
3242 OPEN c_shp_line_dtls(r_base_trx.shipment_line_id);
3243 FETCH c_shp_line_dtls INTO r_shp_line_dtls;
3244 CLOSE c_shp_line_dtls;
3245
3246 OPEN c_shp_hdr_dtls(r_base_trx.shipment_header_id);
3247 FETCH c_shp_hdr_dtls INTO r_shp_hdr_dtls;
3248 CLOSE c_shp_hdr_dtls;
3249
3250 OPEN c_loc_item_dtls(r_base_trx.organization_id, r_shp_line_dtls.item_id);
3251 FETCH c_loc_item_dtls INTO r_loc_item_dtls;
3252 CLOSE c_loc_item_dtls;
3253
3254 OPEN c_base_item_dtls(r_base_trx.organization_id, r_shp_line_dtls.item_id);
3255 FETCH c_base_item_dtls INTO r_base_item_dtls;
3256 CLOSE c_base_item_dtls;
3257
3258 OPEN c_loc_orgn_dtls(r_base_trx.organization_id);
3259 FETCH c_loc_orgn_dtls INTO r_loc_orgn_dtls;
3260 CLOSE c_loc_orgn_dtls;
3261
3262 OPEN c_mtl_params(r_base_trx.organization_id);
3263 FETCH c_mtl_params INTO r_mtl_params;
3264 CLOSE c_mtl_params;
3265
3266 /* following if condition added as part of DFF elimination
3267 Vijay Shankar for Bug#4346453. RCV DFF Elim. Enh. */
3268 if lv_transaction_type <> 'RETURN TO VENDOR' then
3269 OPEN c_excise_invoice_no(r_base_trx.shipment_line_id);
3270 FETCH c_excise_invoice_no INTO r_exc_inv_no;
3271 CLOSE c_excise_invoice_no;
3272 end if;
3273
3274 IF r_base_trx.transaction_type = 'CORRECT' THEN
3275 p_codepath := jai_general_pkg.plot_codepath(3, p_codepath);
3276 lv_transaction_type := r_parent_base_trx.transaction_type;
3277 ELSE
3278 p_codepath := jai_general_pkg.plot_codepath(4, p_codepath);
3279 lv_transaction_type := r_base_trx.transaction_type;
3280 END IF;
3281
3282 -- Vijay Shankar for Bug#3940588. RECEIPTS DEPLUG
3283 -- this is coded to support UNORDERED Transactions by localization in new receipts code. Shall chk the impact
3284 IF r_base_trx.transaction_type = 'MATCH' THEN
3285 lv_transaction_type := 'RECEIVE';
3286 UPDATE JAI_RCV_TRANSACTIONS
3287 SET transaction_type = lv_transaction_type
3288 WHERE transaction_id = p_transaction_id;
3289 END IF;
3290
3291 /* Vijay Shankar for Bug#4250171 */
3292 /* following if condition added to OPM for VAT Functionality */
3293 IF r_trx.attribute_category in (OPM_RECEIPT,OPM_RETURNS) THEN
3294 IF lv_transaction_type = 'RETURN TO VENDOR' THEN
3295 OPEN c_trx(r_trx.parent_transaction_id);
3296 FETCH c_trx INTO r_parent_trx;
3297 CLOSE c_trx;
3298
3299 ln_location_id := r_parent_trx.location_id;
3300 lv_subinventory := r_base_trx.subinventory;
3301 ELSE
3302 ln_location_id := r_trx.location_id;
3303 lv_subinventory := r_base_trx.subinventory;
3304 END IF;
3305
3306 -- if both location and subinventory are NULL then goto the parent type i.e RTV to RECEIVE and RTR to DELIVER
3307 ELSIF nvl(r_base_trx.location_id, 0) = 0 AND nvl(r_base_trx.subinventory, '-XX') = '-XX' THEN
3308
3309 p_codepath := jai_general_pkg.plot_codepath(5, p_codepath);
3310 -- following condition added by Vijay Shankar for Bug#4038024. Incase of CORRECT transactions, if location and subinventory
3311 -- are not present, then we need to look at parent transaction for location. this will mostly happen for DIRECT DELIVERY case
3312 IF lv_transaction_type IN ('RETURN TO RECEIVING', 'RETURN TO VENDOR')
3313 OR (r_base_trx.transaction_type = 'CORRECT' AND r_parent_base_trx.transaction_type IN ('RECEIVE', 'DELIVER')) -- BUG#3949502. (3927371)
3314 THEN
3315
3316 p_codepath := jai_general_pkg.plot_codepath(6, p_codepath);
3317 OPEN c_trx(r_base_trx.parent_transaction_id);
3318 FETCH c_trx INTO r_parent_trx;
3319 CLOSE c_trx;
3320
3321 ln_location_id := r_parent_trx.location_id;
3322
3323 -- following IF condition added as part of porting from Bug#3949109 (3927371)
3324 -- Incase of Direct Delivery RECEIVE transaction may not have both the location and subinventory. In this case we need to fetch the
3325 -- subinventory from DELIVER transaction
3326 ELSIF lv_transaction_type = 'RECEIVE' AND r_base_trx.routing_header_id = 3 THEN -- this will not execute for correct transactions
3327 p_codepath := jai_general_pkg.plot_codepath(7, p_codepath);
3328 OPEN c_dlry_subinventory(r_base_trx.shipment_line_id, p_transaction_id, 'DELIVER');
3329 FETCH c_dlry_subinventory INTO lv_subinventory;
3330 CLOSE c_dlry_subinventory;
3331
3332 END IF;
3333
3334 IF (lv_transaction_type in ('RETURN TO RECEIVING', 'DELIVER') AND nvl(lv_subinventory,'-XX')='-XX')
3335 OR (lv_transaction_type in ('RETURN TO VENDOR', 'RECEIVE') AND nvl(ln_location_id,0)=0 AND nvl(lv_subinventory,'-XX')='-XX' )
3336 THEN
3337
3338 p_codepath := jai_general_pkg.plot_codepath(8, p_codepath);
3339 IF lv_transaction_type IN ('DELIVER', 'RETURN TO RECEIVING') THEN
3340 lv_required_trx_type := 'DELIVER';
3341 ELSIF lv_transaction_type IN ('RECEIVE', 'RETURN TO VENDOR') THEN
3342 lv_required_trx_type := 'RECEIVE';
3343 END IF;
3344
3345 ln_ancestor_trxn_id := get_ancestor_id(
3346 p_transaction_id => p_transaction_id,
3347 p_shipment_line_id => r_base_trx.shipment_line_id,
3348 p_required_trx_type => lv_required_trx_type
3349 );
3350
3351 p_codepath := jai_general_pkg.plot_codepath(9, p_codepath);
3352 IF ln_ancestor_trxn_id IS NOT NULL THEN
3353 p_codepath := jai_general_pkg.plot_codepath(10, p_codepath);
3354 OPEN c_base_trx(ln_ancestor_trxn_id);
3355 FETCH c_base_trx INTO r_ancestor_dtls;
3356 CLOSE c_base_trx;
3357
3358 ln_location_id := r_ancestor_dtls.location_id;
3359 lv_subinventory := r_ancestor_dtls.subinventory;
3360 END IF;
3361
3362 END IF;
3363
3364 ELSE
3365 p_codepath := jai_general_pkg.plot_codepath(11, p_codepath);
3366 ln_location_id := r_base_trx.location_id;
3367 lv_subinventory := r_base_trx.subinventory;
3368 END IF;
3369
3370 /* added this end if to support VAT Func for OPM . Vijay Shankar for Bug#4250171*/
3371 IF nvl(r_trx.attribute_category, 'XXXX') NOT IN (OPM_RECEIPT, OPM_RETURNS) THEN
3372 /*
3373 || srjayara bug 5064235 -- forward porting for bug# 5054114
3374 || Added if condition .. end if to populate the subinventory from DELIVER line
3375 || in rcv_transactions
3376 */
3377 IF lv_subinventory IS NULL THEN
3378 OPEN cur_dlry_subinv ( p_transaction_id );
3379 FETCH cur_dlry_subinv INTO lv_subinventory ;
3380 CLOSE cur_dlry_subinv ;
3381 END IF ;
3382
3383
3384 IF lv_subinventory IS NOT NULL THEN
3385 p_codepath := jai_general_pkg.plot_codepath(12, p_codepath);
3386 OPEN c_loc_linked_to_org_subinv(r_base_trx.organization_id, lv_subinventory);
3387 FETCH c_loc_linked_to_org_subinv INTO r_subinv_dtls;
3388 CLOSE c_loc_linked_to_org_subinv;
3389
3390 OPEN c_base_subinv_dtls(r_base_trx.organization_id, lv_subinventory);
3391 FETCH c_base_subinv_dtls INTO r_base_subinv_dtls;
3392 CLOSE c_base_subinv_dtls;
3393
3394 IF (nvl(ln_location_id,0) = 0
3395 /* following condition added by Vijay Shankar for Bug#4278511 to take care of ISO Scenario */
3396 /* Bug 4589354. Added by Lakshmi Gopalsami.
3397 Commented the following condition.
3398 OR (r_base_trx.source_document_code = 'REQ' and */
3399 OR nvl(r_subinv_dtls.location_id, 0) <> 0 )
3400 THEN
3401 p_codepath := jai_general_pkg.plot_codepath(13, p_codepath);
3402 ln_location_id := r_subinv_dtls.location_id;
3403 END IF;
3404
3405 lv_loc_subinv_type := r_subinv_dtls.loc_subinventory_type;
3406 lv_base_subinv_asset_flag := r_base_subinv_dtls.asset_inventory;
3407
3408 END IF;
3409
3410 -- IF nvl(ln_location_id, 0) = 0 THEN
3411 IF nvl(ln_location_id, 0) <> 0 THEN
3412 p_codepath := jai_general_pkg.plot_codepath(14, p_codepath);
3413 -- OPEN c_inv_org_linked_to_location(r_base_trx.organization_id);
3414 OPEN c_inv_org_linked_to_location(ln_location_id);
3415 FETCH c_inv_org_linked_to_location INTO ln_organization_id;
3416 CLOSE c_inv_org_linked_to_location;
3417
3418 IF r_base_trx.organization_id <> ln_organization_id THEN
3419 p_codepath := jai_general_pkg.plot_codepath(15, p_codepath);
3420 ln_location_id := 0;
3421 END IF;
3422 END IF;
3423
3424 END IF; /* added this end if to support VAT Func for OPM . Vijay Shankar for Bug#4250171*/
3425
3426 /* Bug 4586752. Added by Lakshmi Gopalsami
3427 * Assigned the value of location_id to the local variable */
3428 IF NVL(ln_location_id,0) = 0 THEN
3429 ln_location_id := r_base_trx.location_id;
3430 END IF;
3431
3432 p_codepath := jai_general_pkg.plot_codepath(16, p_codepath);
3433 ln_tax_amount := get_trxn_tax_amount(
3434 p_transaction_id => p_transaction_id,
3435 p_shipment_line_id => r_base_trx.shipment_line_id,
3436 p_curr_conv_rate => r_base_trx.currency_conversion_rate,
3437 p_return_in_inr_curr => jai_constants.yes --File.Sql.35 Cbabu
3438 );
3439
3440 ln_cenvat_amount := get_trxn_cenvat_amount(
3441 p_transaction_id => p_transaction_id,
3442 p_shipment_line_id => r_base_trx.shipment_line_id,
3443 p_organization_type => r_loc_orgn_dtls.organization_type,
3444 p_curr_conv_rate => r_base_trx.currency_conversion_rate
3445 );
3446
3447 --Start, added by Vijay Shankar for Bug#3940588
3448 ln_tax_transaction_id := get_ancestor_id(
3449 p_transaction_id => p_transaction_id,
3450 p_shipment_line_id => r_base_trx.shipment_line_id,
3451 p_required_trx_type => 'RECEIVE'
3452 );
3453
3454 -- Third Party invoice flag should be set only at LINE level which is first transaction of Receipt
3455 -- i.e RECEIVE or MATCH or CORRECT of RECEIVE
3456 IF lv_transaction_type IN ('RECEIVE', 'MATCH') THEN
3457 OPEN c_non_po_vendor_cnt(r_base_trx.shipment_header_id, r_base_trx.shipment_line_id);
3458 FETCH c_non_po_vendor_cnt INTO ln_non_po_vendor_cnt;
3459 CLOSE c_non_po_vendor_cnt;
3460
3461 IF ln_non_po_vendor_cnt > 0 THEN
3462 lv_third_party_flag := 'N';
3463 ELSE
3464 lv_third_party_flag := 'X';
3465 END IF;
3466 ELSE
3467 lv_third_party_flag := 'X';
3468 END IF;
3469 -- End, added by Vijay Shankar for Bug#3940588
3470
3471 IF lb_debug THEN
3472 FND_FILE.put_line(FND_FILE.log, '... RecNum:'||r_shp_hdr_dtls.receipt_num ||',p_cenvat_amount:'||ln_cenvat_amount );
3473 END IF;
3474
3475 p_codepath := jai_general_pkg.plot_codepath(17, p_codepath);
3476 jai_rcv_transactions_pkg.update_row(
3477 p_transaction_id => p_transaction_id,
3478 p_parent_transaction_type => r_parent_base_trx.transaction_type,
3479 p_receipt_num => r_shp_hdr_dtls.receipt_num,
3480 p_inventory_item_id => r_shp_line_dtls.item_id,
3481 p_item_class => nvl(r_loc_item_dtls.item_class, NO_ITEM_CLASS),
3482 p_item_cenvatable => nvl(r_loc_item_dtls.modvat_flag, NO_SETUP),
3483 p_item_excisable => nvl(r_loc_item_dtls.excise_flag, NO_SETUP),
3484 p_item_trading_flag => nvl(r_loc_item_dtls.item_trading_flag, NO_SETUP),
3485 p_inv_item_flag => nvl(r_base_item_dtls.inventory_item_flag, 'N'),
3486 p_inv_asset_flag => r_base_item_dtls.inventory_asset_flag,
3487 p_location_id => nvl(ln_location_id, 0),
3488 p_loc_subinv_type => nvl(lv_loc_subinv_type, NO_SETUP),
3489 p_base_subinv_asset_flag => lv_base_subinv_asset_flag,
3490 p_organization_type => r_loc_orgn_dtls.organization_type,
3491 p_excise_in_trading => nvl(r_loc_orgn_dtls.excise_in_rg23d, 'N'),
3492 p_costing_method => r_mtl_params.primary_cost_method,
3493 p_boe_applied_flag => NULL,
3494 p_third_party_flag => lv_third_party_flag, -- Vijay Shankar for Bug#3940588
3495 p_tax_amount => ln_tax_amount,
3496 p_cenvat_amount => ln_cenvat_amount,
3497 p_excise_invoice_no => r_exc_inv_no.excise_invoice_no,
3498 p_excise_invoice_date => r_exc_inv_no.excise_invoice_date,
3499 p_tax_transaction_id => ln_tax_transaction_id, -- Vijay Shankar for Bug#3940588
3500 p_assessable_value => NULL -- This needs to be populated during Tax Calculation itself
3501 );
3502
3503
3504 p_codepath := jai_general_pkg.plot_codepath(18, p_codepath);
3505
3506 --Start Added by Sanjikum for Bug#4495135
3507 ln_tax_apportion_factor := get_apportion_factor(p_transaction_id);
3508
3509 p_codepath := jai_general_pkg.plot_codepath(19, p_codepath);
3510 --This update can't be merged with the above, as get_apportion_factor uses the column tax_transaction_id
3511 --which is updated only in the above update
3512
3513 jai_rcv_transactions_pkg.update_row(
3514 p_transaction_id => p_transaction_id,
3515 p_tax_apportion_factor => ln_tax_apportion_factor
3516 );
3517 --End Added by Sanjikum for Bug#4495135
3518
3519 p_codepath := jai_general_pkg.plot_codepath(20, p_codepath, 'populate_details', 'END');
3520
3521 IF lb_debug THEN
3522 FND_FILE.put_line(FND_FILE.log, '$ POPULATE_DETAILS');
3523 END IF;
3524
3525 /* Added by Ramananda for bug#4407165 */
3526 EXCEPTION
3527 WHEN OTHERS THEN
3528 p_process_status := null;
3529 p_process_message := null;
3530 FND_MESSAGE.SET_NAME('JA','JAI_EXCEPTION_OCCURED');
3531 FND_MESSAGE.SET_TOKEN('JAI_PROCESS_MSG', lv_object_name ||'. Err:'||sqlerrm );
3532 app_exception.raise_exception;
3533
3534 END populate_details;
3535
3536 /* ~~~~~~~~~~~~~~~~~~~~ TRANSACTION VALIDATION Procedure ~~~~~~~~~~~~~~~~~~~~~~~~*/
3537
3538 PROCEDURE validate_transaction (
3539 p_transaction_id IN NUMBER,
3540 p_process_flag IN OUT NOCOPY VARCHAR2,
3541 p_process_message IN OUT NOCOPY VARCHAR2,
3542 p_cenvat_rg_flag IN OUT NOCOPY VARCHAR2,
3543 p_cenvat_rg_message IN OUT NOCOPY VARCHAR2,
3544 /* following two flags introduced by Vijay Shankar for Bug#4250236(4245089). VAT Implementation */
3545 p_process_vat_flag IN OUT NOCOPY VARCHAR2,
3546 p_process_vat_message IN OUT NOCOPY VARCHAR2,
3547 p_called_from IN VARCHAR2,
3548 p_simulate_flag IN VARCHAR2,
3549 p_codepath IN OUT NOCOPY VARCHAR2
3550 ) IS
3551
3552 /*---------------------------------------------
3553 Functionality of this Procedure :
3554 0. If some Scenario has to be Supported/UnSupported by Localization, then this needs to be changed as everything is
3555 driven based on Flags set in this procedure
3556 1. This Procedure is to set Process Flags or cenvat RG Flag in JAI_RCV_TRANSACTIONS.
3557 Possible Values :
3558 ===================
3559 X - Not Applicable (All values prefexing X means variations of Non Applicability with exact problem)
3560 P - Pending for Parent transaction to be Processed
3561 N - To be Processed
3562 E - Errored out - Populate Error Message during Transaction Processing
3563 Y - Already Processed
3564 O - Others - Populate Information Message.
3565
3566 CenvatRGFlag Values (All the Values Starting with X mean the Cenvat enries cant be passed and second letter onwards it gives the exactness of problem
3567 -------------------
3568 XT - Indicates that Cenvat Entries cant be passed as there is a Change of Month between parent and this transaction
3569
3570 OPEN ISSUES:
3571 - Third Party Flag needs to be Updated if CORRECTion transactions has to uptake the TP functionality or Whole Receipts
3572 functionality is moved into the this NEW RECEIPTS CODE
3573 - Assessable value needs to be populated
3574
3575 ---------------------------------------------*/
3576
3577 CURSOR c_trx(cp_transaction_id IN NUMBER) IS
3578 SELECT *
3579 FROM JAI_RCV_TRANSACTIONS
3580 WHERE transaction_id = cp_transaction_id;
3581
3582 CURSOR c_receipt_line_dtls(cp_shipment_line_id JAI_RCV_TRANSACTIONS.shipment_line_id%type) is
3583 SELECT excise_invoice_no, excise_invoice_date, online_claim_flag,
3584 claim_modvat_flag, nvl(rma_type, 'XXXX') rma_type
3585 FROM JAI_RCV_LINES
3586 WHERE shipment_line_id = cp_shipment_line_id;
3587
3588 CURSOR c_taxes(cp_shipment_line_id JAI_RCV_TRANSACTIONS.shipment_line_id%type) is /* Modified by Ramananda for removal of SQL LITERALs :bug#4428980*/
3589 SELECT count(1) total_cnt,
3590 sum( decode(upper(tax_type), 'EXCISE', 1,
3591 'ADDL. EXCISE', 1,
3592 'OTHER EXCISE', 1,
3593 'CVD', 1,
3594 jai_constants.tax_type_add_cvd,1,
3595 -- Modified by SACSETHI Bug# 5228046
3596 -- Forward porting the change in 11i bug 5365523
3597 -- (Additional CVD Enhancement) as part of the R12 bug 5228046
3598 jai_constants.tax_type_exc_edu_cess,1,
3599 jai_constants.tax_type_cvd_edu_cess,1,jai_constants.tax_type_sh_exc_edu_cess,1,
3600 jai_constants.tax_type_sh_cvd_edu_cess,1, 0) --kunkumar for bugno5989740 -- Vijay Shankar for Bug#3940588 EDU CESS
3601 ) excise_cnt
3602 FROM JAI_RCV_LINE_TAXES
3603 WHERE shipment_line_id = cp_shipment_line_id
3604 AND tax_type not in (jai_constants.tax_type_tds, jai_constants.tax_type_modvat_recovery); --('TDS', 'MODVAT RECOVERY')
3605
3606 CURSOR c_excise_tax_count(cp_shipment_line_id JAI_RCV_TRANSACTIONS.shipment_line_id%type) is
3607 SELECT count(1)
3608 FROM JAI_RCV_LINE_TAXES
3609 WHERE shipment_line_id = cp_shipment_line_id
3610 -- AND tax_type NOT IN ('TDS','Modvat Recovery')
3611 AND modvat_flag = jai_constants.yes
3612 AND upper(tax_type) IN ( 'EXCISE',
3613 'ADDL. EXCISE',
3614 'OTHER EXCISE',
3615 'CVD',
3616 jai_constants.tax_type_add_cvd ,
3617 -- Modified by SACSETHI Bug# 5228046
3618 -- Forward porting the change in 11i bug 5365523
3619 -- (Additional CVD Enhancement) as part of the R12 bug 5228046
3620 jai_constants.tax_type_exc_edu_cess,
3621 jai_constants.tax_type_cvd_edu_cess,jai_constants.tax_type_sh_exc_edu_cess,
3622 jai_constants.tax_type_sh_cvd_edu_cess);--Added by kunkumar for bugno5989740 -- Vijay Shankar for Bug#3940588 EDU CESS
3623
3624
3625 ln_recoverable_vat_tax_cnt NUMBER;
3626 CURSOR c_recoverable_vat_tax_cnt(cp_shipment_line_id number, cp_regime_code varchar2) is
3627 SELECT count(1)
3628 FROM JAI_RCV_LINE_TAXES
3629 WHERE shipment_line_id = cp_shipment_line_id
3630 AND modvat_flag = jai_constants.yes
3631 AND tax_type IN
3632 ( select tax_type from jai_regime_tax_types_v /*modified this to use View as part of OPM changes */
3633 where regime_code = cp_regime_code
3634 );
3635
3636 CURSOR c_acct_count(cp_parent_transaction_id JAI_RCV_TRANSACTIONS.transaction_id%type,
3637 cp_parent_transaction_type JAI_RCV_TRANSACTIONS.parent_transaction_type%type
3638 ) IS
3639 SELECT count(1)
3640 FROM JAI_RCV_JOURNAL_ENTRIES
3641 WHERE transaction_id = cp_parent_transaction_id
3642 AND transaction_type = cp_parent_transaction_type;
3643
3644 ld_parent_rg_entry_date DATE;
3645 CURSOR c_rg_count(cp_parent_transaction_id JAI_RCV_TRANSACTIONS.transaction_id%type,
3646 cp_organization_id JAI_RCV_TRANSACTIONS.organization_id%type
3647 ) IS
3648 SELECT creation_date
3649 FROM (SELECT creation_date
3650 FROM JAI_CMN_RG_23AC_II_TRXS
3651 WHERE receipt_ref = cp_parent_transaction_id
3652 AND organization_id = cp_organization_id
3653 AND transaction_source_num = 18
3654 UNION
3655 SELECT creation_date
3656 FROM JAI_CMN_RG_PLA_TRXS
3657 WHERE ref_document_id = cp_parent_transaction_id
3658 AND organization_id = cp_organization_id
3659 AND transaction_source_num = 19);
3660
3661 CURSOR c_parent_rg23d_entry(
3662 cp_parent_transaction_id JAI_RCV_TRANSACTIONS.transaction_id%type,
3663 cp_organization_id JAI_RCV_TRANSACTIONS.organization_id%type
3664 ) IS
3665 SELECT creation_date
3666 FROM JAI_CMN_RG_23D_TRXS
3667 WHERE receipt_ref = cp_parent_transaction_id
3668 AND organization_id = cp_organization_id
3669 AND transaction_source_num = 18;
3670
3671 CURSOR c_receipt_cenvat_dtl(cp_transaction_id IN NUMBER, cp_organization_type IN VARCHAR2) IS
3672 SELECT decode(cp_organization_type, 'M', online_claim_flag, jai_constants.yes) online_claim_flag, -- Changed by Vijay Shankar for Bug #4172424
3673 cenvat_claimed_ptg, cenvat_claimed_amt, unclaim_cenvat_flag, cenvat_amount
3674 FROM JAI_RCV_CENVAT_CLAIMS
3675 WHERE transaction_id = cp_transaction_id;
3676
3677 r_trx c_trx%ROWTYPE;
3678 r_parent_trx c_trx%ROWTYPE;
3679 r_base_trx c_base_trx%ROWTYPE;
3680 r_receipt_line c_receipt_line_dtls%ROWTYPE;
3681 r_receipt_cenvat_dtl c_receipt_cenvat_dtl%ROWTYPE;
3682 r_taxes c_taxes%ROWTYPE;
3683 r_exc_inv_no c_excise_invoice_no%ROWTYPE;
3684
3685 lv_statement_id VARCHAR2(5);
3686
3687 lv_transaction_type JAI_RCV_TRANSACTIONS.transaction_type%TYPE;
3688 lv_include_cenvat_in_cost VARCHAR2(5);
3689
3690 ln_excise_tax_count NUMBER; --File.Sql.35 Cbabu := 0;
3691 ln_account_count NUMBER; --File.Sql.35 Cbabu := 0;
3692 ln_rg_count NUMBER; --File.Sql.35 Cbabu := 0;
3693 ln_rtv_cnt NUMBER; --File.Sql.35 Cbabu := 0;
3694
3695 lb_process_iso BOOLEAN;
3696
3697 -- Vijay Shankar for Bug#4346453. RCV DFF Elim. Enh.
3698 lv_excise_inv_gen_action VARCHAR2(50);
3699 lv_vat_inv_gen_action VARCHAR2(50);
3700
3701 lv_qty_upd_event VARCHAR2(30); --added by ssawant
3702
3703 lv_codepath JAI_RCV_TRANSACTIONS.codepath%TYPE; --File.Sql.35 := '';
3704
3705 BEGIN
3706
3707 ln_excise_tax_count := 0;
3708 ln_account_count := 0;
3709 ln_rg_count := 0;
3710 ln_rtv_cnt := 0;
3711 lv_codepath := '';
3712
3713 lv_codepath := jai_general_pkg.plot_codepath(1, lv_codepath, 'cenvat_rg_pkg.validate_trx', 'START');
3714 IF lb_debug THEN
3715 FND_FILE.put_line(FND_FILE.log, '^VALIDATE_TRANSACTION. Prc_Flag->'||p_process_flag||', Cen_Flag->'||p_cenvat_rg_flag);
3716 END IF;
3717
3718 IF p_process_flag IS NULL THEN
3719 p_process_message := NULL;
3720 END IF;
3721
3722 IF p_cenvat_rg_flag IS NULL THEN
3723 p_cenvat_rg_message := NULL;
3724 END IF;
3725
3726 lv_statement_id := '1';
3727 OPEN c_trx(p_transaction_id);
3728 FETCH c_trx into r_trx;
3729 CLOSE c_trx;
3730
3731 /* Vijay Shankar for Bug#4250171. following added to support OPM Functionality for VAT Processing */
3732 IF p_called_from = CALLED_FROM_OPM
3733 OR r_trx.attribute_category in (OPM_RECEIPT, OPM_RETURNS)
3734 THEN
3735 lv_codepath := jai_general_pkg.plot_codepath(3, lv_codepath);
3736 p_process_flag := 'X';
3737 p_process_message := 'This Processing not required for OPM Transaction';
3738 p_cenvat_rg_flag := 'X';
3739 p_cenvat_rg_message := 'This Processing not required for OPM Transaction';
3740 GOTO end_of_cenvat_flag_validation;
3741 END IF;
3742
3743 -- this contains the details of RECEIVE/MATCH transaction type
3744 OPEN c_receipt_cenvat_dtl(r_trx.tax_transaction_id,
3745 r_trx.organization_type); -- Changed by Vijay Shankar for Bug #4172424
3746 FETCH c_receipt_cenvat_dtl into r_receipt_cenvat_dtl;
3747 CLOSE c_receipt_cenvat_dtl;
3748
3749 OPEN c_base_trx(p_transaction_id);
3750 FETCH c_base_trx into r_base_trx;
3751 CLOSE c_base_trx;
3752
3753 lv_statement_id := '2';
3754 IF r_trx.transaction_type = 'CORRECT' THEN
3755 lv_transaction_type := r_trx.parent_transaction_type;
3756 ELSE
3757 lv_transaction_type := r_trx.transaction_type;
3758 END IF;
3759
3760 lv_statement_id := '3';
3761 /* Fetch all the Information from JAI_RCV_LINES*/
3762 OPEN c_receipt_line_dtls(r_trx.shipment_line_id);
3763 FETCH c_receipt_line_dtls into r_receipt_line;
3764 CLOSE c_receipt_line_dtls;
3765
3766 --FND_FILE.put_line(fnd_file.log, 'Shp_lineId:'||r_trx.shipment_line_id||', Cnt:'||SQL%ROWCOUNT
3767 -- ||', Cnt:'||SQL%ROWCOUNT||', exNo:'||r_receipt_line.excise_invoice_no);
3768
3769 lv_statement_id := '4';
3770 /* Fetch the Tax count */
3771 OPEN c_taxes(r_trx.shipment_line_id);
3772 FETCH c_taxes into r_taxes;
3773 CLOSE c_taxes;
3774
3775 lv_codepath := jai_general_pkg.plot_codepath(2, lv_codepath);
3776 lb_process_iso := process_iso_transaction(
3777 p_transaction_id => r_trx.transaction_id,
3778 p_shipment_line_id => r_trx.shipment_line_id
3779 );
3780
3781 ------------------- Process Flag Validation -------------------
3782 /* Process Flag is set to 'O' when some columns which are required are null or some specific scenarios */
3783
3784 -- values other than 'Y' are added by Vijay Shankar for Bug#3940588. RECEIPTS DEPLUG
3785 IF p_process_flag IN ('Y', 'X', 'O', 'XT') THEN
3786 lv_codepath := jai_general_pkg.plot_codepath(3, lv_codepath);
3787 goto end_of_process_flag_validation;
3788 END IF;
3789
3790
3791 /*bgowrava for forward porting Bug#5756676..start*/
3792
3793 lv_online_qty_flag := 'N';
3794
3795 OPEN cur_qty_setup(r_trx.organization_id,r_trx.location_id);
3796 FETCH cur_qty_setup INTO lv_qty_upd_event;
3797 IF lv_qty_upd_event IS NULL THEN
3798 CLOSE cur_qty_setup;
3799 OPEN cur_qty_setup(r_trx.organization_id,0);
3800 FETCH cur_qty_setup INTO lv_qty_upd_event;
3801 END IF;
3802 CLOSE cur_qty_setup;
3803
3804 FND_file.put_line( fnd_file.log, 'Quantity Update Event:'||lv_qty_upd_event);
3805
3806 OPEN cur_item_excise_flag(r_trx.organization_id,r_trx.inventory_item_id );
3807 FETCH cur_item_excise_flag INTO lv_excise_flag;
3808 CLOSE cur_item_excise_flag;
3809
3810 FND_file.put_line( fnd_file.log, 'Item Excisable:'||lv_excise_flag);
3811 FND_file.put_line( fnd_file.log, 'Excisable Taxes Count:'||r_taxes.excise_cnt);
3812
3813 FND_file.put_line( fnd_file.log, 'Transaction Type:'||lv_transaction_type);
3814
3815 IF r_trx.organization_type = MFG_ORGN THEN
3816
3817 IF ( ( nvl(lv_qty_upd_event,'X') = 'RECEIVE' AND lv_transaction_type in ('RECEIVE','MATCH') AND nvl(r_trx.quantity_register_flag,'N') = 'N' )
3818 OR ( lv_excise_flag = 'Y' AND nvl(r_taxes.excise_cnt,0) = 0 AND lv_transaction_type in ('RECEIVE','MATCH','RETURN TO VENDOR'))/*rchandan for bug#6109941.added nvl for tax count*/ /*bug 7662347 - added RETURN TO VENDOR*/
3819 ) THEN
3820
3821 lv_online_qty_flag := 'Y';
3822
3823 FND_file.put_line( fnd_file.log, 'Quantity Register would be hit independent of Amount register');
3824
3825 END IF;
3826
3827 END IF;
3828
3829 /*bgowrava for forward porting Bug#5756676..end*/
3830
3831 /* 1PROCESS_FLAG. START of PROCESS_FLAG BASIC VALIDATIONS */
3832 lv_statement_id := '6';
3833 if r_trx.organization_id is null then
3834 lv_codepath := jai_general_pkg.plot_codepath(3, lv_codepath);
3835 p_process_flag := 'O';
3836 p_process_message := jai_rcv_trx_processing_pkg.get_message('NO-ORG');
3837 goto end_of_process_flag_validation;
3838 end if;
3839
3840 lv_statement_id := '7';
3841 -- following condition added by Vijay Shankar for Bug#3940588
3842 -- Common Checks between process_flag and cenvat_rg_flag
3843 if lv_transaction_type = 'RETURN TO VENDOR'
3844 AND (r_base_trx.source_document_code='PO' AND r_base_trx.po_header_id IS NULL)
3845 then
3846 lv_codepath := jai_general_pkg.plot_codepath(4, lv_codepath);
3847 p_process_flag := 'X';
3848 p_process_message := 'RTV against Unordered Receipt will not be processed';
3849 goto end_of_process_flag_validation;
3850 end if;
3851 /* 1PROCESS_FLAG. END of PROCESS_FLAG BASIC VALIDATIONS */
3852
3853 /* 2PROCESS_FLAG. START of TRANSACTION VALIDATIONS To SEE WHETHER IT IS QUALIFIED w.r.t NON CENVAT TAXES(PROCESS_FLAG)*/
3854
3855 lv_statement_id := '10';
3856 if r_taxes.total_cnt = 0 then
3857 lv_codepath := jai_general_pkg.plot_codepath(5, lv_codepath);
3858 p_process_flag := 'X';
3859 p_process_message := jai_rcv_trx_processing_pkg.get_message('NO-TAXES');
3860 goto end_of_process_flag_validation;
3861 end if;
3862
3863 lv_statement_id := '11';
3864 -- Vijay Shankar for Bug#4346453. RCV DFF Elim. Enh. IF r_base_trx.attr_cat = 'India RMA Receipt' THEN
3865 if r_base_trx.source_document_code = source_rma then
3866 lv_codepath := jai_general_pkg.plot_codepath(6, lv_codepath);
3867 IF ( lv_transaction_type IN ('RECEIVE', 'RETURN TO VENDOR')
3868 -- Vijay Shankar for Bug#4346453. RCV DFF Elim. Enh. AND UPPER(r_base_trx.rma_type) NOT IN ('SCRAP')
3869 AND r_receipt_line.rma_type NOT IN ('SCRAP')
3870 )
3871 OR lv_transaction_type IN ('DELIVER', 'RETURN TO RECEIVING')
3872 THEN
3873 lv_codepath := jai_general_pkg.plot_codepath(7, lv_codepath);
3874 --Added the below by rchandan for Bug#6030615
3875 OPEN c_trx(p_transaction_id);
3876 FETCH c_trx into r_trx;
3877 CLOSE c_trx;
3878 p_process_flag := 'X';
3879 p_process_message := 'RMA Processing Not Required';
3880 goto end_of_process_flag_validation;
3881
3882 ELSIF lv_transaction_type IN ('RECEIVE', 'RETURN TO VENDOR')
3883 -- Vijay Shankar for Bug#4346453. RCV DFF Elim. Enh. AND UPPER(r_base_trx.rma_type) IN ('SCRAP')
3884 AND r_receipt_line.rma_type IN ('SCRAP')
3885 AND r_taxes.excise_cnt = 0
3886 THEN
3887 lv_codepath := jai_general_pkg.plot_codepath(8, lv_codepath);
3888 p_process_flag := 'X';
3889 p_process_message := 'Excise Taxes donot exist in this RMA transaction';
3890 goto end_of_process_flag_validation;
3891 END IF;
3892
3893 END IF;
3894
3895 lv_statement_id := '12';
3896 IF NOT lb_process_iso THEN
3897 lv_codepath := jai_general_pkg.plot_codepath(9, lv_codepath);
3898 p_process_flag := 'X';
3899 p_process_message := 'ISO Processing Not Required';
3900 goto end_of_process_flag_validation;
3901 END IF;
3902 /* 2PROCESS_FLAG. END of TRANSACTION VALIDATIONS To SEE WHETHER IT IS QUALIFIED w.r.t NON CENVAT TAXES(PROCESS_FLAG)*/
3903
3904 /* 3PROCESS_FLAG. START of TRANSACTION VALIDATIONS To SEE WHETHER IT IS PENDING FOR SOMETHING INSPITE OF BEING QUALIFIED for PROCESSING*/
3905 lv_statement_id := '13';
3906 --Ensures that if the Parent lines Accounting is not done, then the Accounting for this Line
3907 --would also be deferred.
3908 if r_trx.transaction_type = 'CORRECT' AND r_trx.parent_transaction_type in ('DELIVER','RETURN TO RECEIVING') then
3909 lv_codepath := jai_general_pkg.plot_codepath(10, lv_codepath);
3910 /* Fetch the Accounting of the parent transaction line */
3911 OPEN c_acct_count(r_trx.parent_transaction_id, r_trx.parent_transaction_type);
3912 FETCH c_acct_count into ln_account_count;
3913 CLOSE c_acct_count;
3914
3915 if ln_account_count = 0 then
3916 lv_codepath := jai_general_pkg.plot_codepath(11, lv_codepath);
3917 p_process_flag := 'P';
3918 p_process_message := jai_rcv_trx_processing_pkg.get_message('NO-BASE-ACCT');
3919 goto end_of_process_flag_validation;
3920 end if;
3921 end if;
3922 /* 3PROCESS_FLAG. END of TRANSACTION VALIDATIONS To SEE WHETHER IT IS PENDING FOR SOMETHING INSPITE OF BEING QUALIFIED for PROCESSING*/
3923
3924 p_process_flag := 'N';
3925
3926 <<end_of_process_flag_validation>>
3927
3928 ------------------- Cenvat RG Flag Validation Block -------------------
3929 /* Cenvat Flag is set to 'O' when some columns which are required are null or some specific scenarios */
3930
3931 lv_statement_id := '14';
3932 -- values other than 'Y' are added by Vijay Shankar for Bug#3940588. RECEIPTS DEPLUG
3933 IF p_cenvat_rg_flag IN ('Y', 'X', 'O', 'XT') THEN
3934 lv_codepath := jai_general_pkg.plot_codepath(12, lv_codepath);
3935 goto end_of_cenvat_flag_validation;
3936 END IF;
3937
3938 -- following condition is false if the call to this procedure happened from JAINMVAT (i.e deferred Claim Scree)
3939 FND_file.put_line( fnd_file.log, '54321 ttype:'||r_trx.transaction_type||',exno:'||r_receipt_line.excise_invoice_no
3940 ||',exda:'||r_receipt_line.excise_invoice_date||',onClFlg:'||r_receipt_cenvat_dtl.online_claim_flag
3941 ||',CenAmt:'||r_receipt_cenvat_dtl.cenvat_amount||',calFrm:'||p_called_from);
3942
3943 lv_statement_id := '15';
3944 /* 1CENVAT_RG_FLAG. START of CENVAT_RG_FLAG BASIC VALIDATIONS */
3945 if r_trx.organization_id is null then
3946 lv_codepath := jai_general_pkg.plot_codepath(13, lv_codepath);
3947 p_cenvat_rg_flag := 'O';
3948 p_cenvat_rg_message := jai_rcv_trx_processing_pkg.get_message('NO-ORG');
3949
3950 goto end_of_cenvat_flag_validation;
3951 end if;
3952
3953 lv_statement_id := '16';
3954 if r_trx.location_id = 0 then
3955 lv_codepath := jai_general_pkg.plot_codepath(14, lv_codepath);
3956 p_cenvat_rg_flag := 'O';
3957 p_cenvat_rg_message := jai_rcv_trx_processing_pkg.get_message('NO-LOC-ORG-SETUP');
3958 goto end_of_cenvat_flag_validation;
3959 end if;
3960 /* 1CENVAT_RG_FLAG. END of CENVAT_RG_FLAG BASIC VALIDATIONS */
3961
3962 /* 2CENVAT_RG_FLAG. START of TRANSACTION VALIDATIONS To SEE WHETHER IT IS QUALIFIED w.r.t NON CENVAT TAXES(CENVAT_RG_FLAG)*/
3963 lv_statement_id := '17';
3964 if lv_transaction_type = 'RETURN TO VENDOR'
3965 AND (r_base_trx.source_document_code='PO' AND r_base_trx.po_header_id IS NULL)
3966 then
3967 lv_codepath := jai_general_pkg.plot_codepath(15, lv_codepath);
3968 p_cenvat_rg_flag := 'X';
3969 p_cenvat_rg_message := 'RTV against Unordered Receipt will not be processed';
3970 goto end_of_cenvat_flag_validation;
3971 end if;
3972
3973 lv_statement_id := '18';
3974 if r_taxes.total_cnt = 0 then
3975 lv_codepath := jai_general_pkg.plot_codepath(16, lv_codepath);
3976 p_cenvat_rg_flag := 'X';
3977 p_cenvat_rg_message := jai_rcv_trx_processing_pkg.get_message('NO-TAXES');
3978 goto end_of_cenvat_flag_validation;
3979 end if;
3980
3981 lv_statement_id := '19';
3982 if r_trx.transaction_type NOT IN ('RECEIVE', 'MATCH')
3983 AND r_receipt_cenvat_dtl.unclaim_cenvat_flag = 'Y'
3984 then
3985 lv_codepath := jai_general_pkg.plot_codepath(17, lv_codepath);
3986 p_cenvat_rg_flag := 'X';
3987 p_cenvat_rg_message := 'Parent is not eligible for Cenvat Claim';
3988 goto end_of_cenvat_flag_validation;
3989 end if;
3990
3991 if r_taxes.excise_cnt = 0 then
3992 lv_codepath := jai_general_pkg.plot_codepath(18, lv_codepath);
3993 p_cenvat_rg_flag := 'X';
3994 p_cenvat_rg_message := 'Excise Taxes do not exist';
3995 goto end_of_cenvat_flag_validation;
3996 end if;
3997
3998 lv_statement_id := '20';
3999 open c_excise_tax_count(r_trx.shipment_line_id);
4000 fetch c_excise_tax_count into ln_excise_tax_count;
4001 close c_excise_tax_count;
4002
4003 lv_statement_id := '21';
4004 if r_trx.organization_type = 'M' AND ln_excise_tax_count = 0 then
4005 lv_codepath := jai_general_pkg.plot_codepath(19, lv_codepath);
4006 p_cenvat_rg_flag := 'X';
4007 p_cenvat_rg_message := jai_rcv_trx_processing_pkg.get_message('NO-EXCISE-TAXES');
4008 goto end_of_cenvat_flag_validation;
4009 end if;
4010
4011 if r_trx.organization_type = 'T' AND lv_transaction_type IN ('DELIVER', 'RETURN TO RECEIVING') then
4012 lv_codepath := jai_general_pkg.plot_codepath(20, lv_codepath);
4013 p_cenvat_rg_flag := 'X';
4014 p_cenvat_rg_message := 'No Cenvat/RG Entries are passed for '||lv_transaction_type;
4015 goto end_of_cenvat_flag_validation;
4016 end if;
4017
4018 lv_statement_id := '22';
4019 if lv_transaction_type = 'RETURN TO VENDOR' then
4020
4021 lv_codepath := jai_general_pkg.plot_codepath(21, lv_codepath);
4022 if r_trx.transaction_type = 'CORRECT' THEN
4023 lv_statement_id := '23';
4024 SELECT count(1) INTO ln_rtv_cnt
4025 FROM JAI_RCV_RTV_DTLS
4026 WHERE transaction_id = r_trx.parent_transaction_id;
4027
4028 lv_statement_id := '24';
4029 if ln_rtv_cnt = 0 THEN
4030 lv_codepath := jai_general_pkg.plot_codepath(22, lv_codepath);
4031 p_cenvat_rg_flag := 'X';
4032 p_cenvat_rg_message := 'Parent RTV Transaction doesnt have Excise Invoice';
4033 goto end_of_cenvat_flag_validation;
4034 end if;
4035
4036 -- Vijay Shankar for Bug#4346453. RCV DFF Elim. Enh. elsif r_base_trx.generate_excise_invoice = 'N' then
4037 elsif r_trx.attribute1 in ( INV_GEN_STATUS_INV_NA ) then
4038 --pramasub FP start IProc
4039 /*
4040 || Start Changes by ssumaith - Iprocurement Bug#4281841.
4041 || Check if the return is created from Iproc.
4042 */
4043 OPEN check_rcpt_source(r_base_Trx.po_line_location_id);
4044 FETCH check_rcpt_source INTO lv_apps_source_code;
4045 CLOSE check_rcpt_source;
4046
4047 IF NVL(lv_apps_source_code,'$$') <> 'POR' THEN
4048 /*
4049 || The above if was added by ssumaith for Iprocurement Bug#4281841.
4050 */
4051 --pramasub FP end IProc
4052 lv_codepath := jai_general_pkg.plot_codepath(23, lv_codepath);
4053 lv_excise_inv_gen_action := r_trx.attribute1; -- Vijay Shankar for Bug#4346453. RCV DFF Elim. Enh.
4054 p_cenvat_rg_flag := 'X';
4055 -- Vijay Shankar for Bug#4346453. RCV DFF Elim. Enh. p_cenvat_rg_message := 'DFF Value for Generate Excise Invoice is not given as ''Y''';
4056 p_cenvat_rg_message := 'Excise invoice not required';
4057 goto end_of_cenvat_flag_validation;
4058 --pramasub FP Iporc start
4059 ELSE
4060 IF r_trx.cenvat_Rg_status = 'P' or r_trx.process_status = 'P' or r_Trx.process_vat_status = 'P' THEN
4061 /*
4062 It means that the user has intentionally set the process action to N in the Iproc IL returns page.
4063 We are setting the values as to be processed as it can then be picked up and processed.
4064 */
4065 p_cenvat_rg_flag := 'X';
4066 --p_cenvat_rg_message := 'DFF Value for Generate Excise Invoice is not given as ''Y''';
4067 --commented out the above line as the message is changed for the bug#4346453 by cbabu | pramasub FP IProc
4068 p_cenvat_rg_message := 'Excise invoice not required';
4069 GOTO end_of_cenvat_flag_validation;
4070 ELSE
4071 /*
4072 || It means initially the flags were some value other than O and we are flagging it to process it later.
4073 */
4074 --lv_codepath := ja_in_general_pkg.plot_codepath(23.1, lv_codepath);
4075 p_cenvat_rg_flag := 'O';
4076 p_cenvat_rg_message := 'Call From Iprocurement returns page';
4077 p_process_flag := 'O';
4078 p_process_vat_flag := 'O';
4079 GOTO end_of_cenvat_flag_validation;
4080 END IF;
4081 END IF;
4082 /*
4083 || Above end if added by ssumaith - Iprocurement Bug#4281841 to handle returns.
4084 */
4085 --pramasub FP Iporc end
4086 end if;
4087 end if;
4088
4089 lv_statement_id := '25';
4090 if r_trx.organization_type = 'T' then
4091 if r_trx.item_trading_flag <> 'Y' then
4092 lv_codepath := jai_general_pkg.plot_codepath(24, lv_codepath);
4093 p_cenvat_rg_flag := 'O';
4094 p_cenvat_rg_message := jai_rcv_trx_processing_pkg.get_message('ITEM-TRADING-NO');
4095 goto end_of_cenvat_flag_validation;
4096 elsif r_trx.item_excisable <> 'Y' then
4097 lv_codepath := jai_general_pkg.plot_codepath(25, lv_codepath);
4098 p_cenvat_rg_flag := 'O';
4099 p_cenvat_rg_message := 'Trading Item is not Excisable';
4100 goto end_of_cenvat_flag_validation;
4101 end if;
4102 end if;
4103
4104 lv_statement_id := '26';
4105 if r_trx.item_class in ('OTIN','OTEX') then
4106 lv_codepath := jai_general_pkg.plot_codepath(26, lv_codepath);
4107 p_cenvat_rg_flag := 'O';
4108 p_cenvat_rg_message := jai_rcv_trx_processing_pkg.get_message('ITEM-CLASS-OTIN');
4109 goto end_of_cenvat_flag_validation;
4110 elsif r_trx.item_class NOT IN ('RMIN', 'RMEX', 'CGIN', 'CGEX', 'CCIN', 'CCEX', 'FGIN', 'FGEX') then
4111 lv_codepath := jai_general_pkg.plot_codepath(27, lv_codepath);
4112 p_cenvat_rg_flag := 'O';
4113 p_cenvat_rg_message := jai_rcv_trx_processing_pkg.get_message('ITEM-CLASS-NULL');
4114 goto end_of_cenvat_flag_validation;
4115 end if;
4116
4117 lv_statement_id := '27';
4118 /* following condition modified by Vijay Shankar for Bug#4179823 */
4119 -- if r_base_trx.source_document_code <> 'REQ' and r_trx.item_class in ('FGIN', 'FGEX')
4120 -- if r_base_trx.source_document_code <> 'RMA' and r_trx.item_class in ('FGIN', 'FGEX')
4121 --commented the above and added the below by rchandan for Bug#6030615
4122 if r_base_trx.source_document_code NOT IN ('RMA','INVENTORY') and r_trx.item_class in ('FGIN', 'FGEX')
4123 and r_trx.organization_type = 'M' -- Changed by Vijay Shanker for Bug #4172424
4124 then
4125 p_codepath := jai_general_pkg.plot_codepath(28, p_codepath);
4126 p_cenvat_rg_flag := 'X';
4127 p_cenvat_rg_message := 'Cenvat Accounting not supported for FGIN Items';
4128 goto end_of_cenvat_flag_validation;
4129 end if;
4130
4131 lv_statement_id := '28';
4132 if r_trx.organization_type ='M' and r_trx.item_cenvatable = 'N' then
4133 lv_codepath := jai_general_pkg.plot_codepath(29, lv_codepath);
4134 p_cenvat_rg_flag := 'O';
4135 p_cenvat_rg_message := jai_rcv_trx_processing_pkg.get_message('ITEM-CENVATABLE-NO');
4136 goto end_of_cenvat_flag_validation;
4137 end if;
4138
4139 lv_statement_id := '29';
4140 /*
4141 ||Start of bug 5378630
4142 ||Modified the below if statement condition such that the RMA_type 'GOODS RETURN' was changed into GOODS RETURN
4143 */
4144
4145 IF r_base_trx.source_document_code = SOURCE_RMA
4146 AND r_receipt_line.rma_type NOT IN ('PRODUCTION INPUT', 'GOODS RETURN')
4147 THEN
4148 /* ENd of bug 5378630 */
4149 lv_codepath := jai_general_pkg.plot_codepath(30, lv_codepath);
4150 p_cenvat_rg_flag := 'X';
4151 p_cenvat_rg_message := 'RMA Processing Not Required';
4152 goto end_of_cenvat_flag_validation;
4153 END IF;
4154
4155 /* Fetch whether RG has been hit */
4156 if r_trx.transaction_type = 'CORRECT' then
4157
4158 lv_statement_id := '30';
4159 lv_codepath := jai_general_pkg.plot_codepath(31, lv_codepath);
4160 if r_trx.parent_transaction_type in ('RECEIVE', 'RETURN TO VENDOR') then
4161
4162 lv_codepath := jai_general_pkg.plot_codepath(32, lv_codepath);
4163 if r_trx.organization_type = 'M' then
4164 lv_statement_id := '32';
4165 open c_rg_count(r_trx.parent_transaction_id, r_trx.organization_id);
4166 -- fetch c_rg_count into ln_rg_count;
4167 fetch c_rg_count into ld_parent_rg_entry_date;
4168 close c_rg_count;
4169 else -- Trading Check
4170 lv_statement_id := '33';
4171 open c_parent_rg23d_entry(r_trx.parent_transaction_id, r_trx.organization_id);
4172 fetch c_parent_rg23d_entry into ld_parent_rg_entry_date;
4173 close c_parent_rg23d_entry;
4174 end if;
4175
4176 if to_char(ld_parent_rg_entry_date, 'YYYYMM') <> to_char(SYSDATE, 'YYYYMM') THEN
4177 lv_codepath := jai_general_pkg.plot_codepath(33, lv_codepath);
4178 p_cenvat_rg_flag := 'XT';
4179 p_cenvat_rg_message := jai_rcv_trx_processing_pkg.get_message('BASE-MONTH-DIFFERENT');
4180 goto end_of_cenvat_flag_validation;
4181 end if;
4182
4183 else
4184
4185 lv_statement_id := '35';
4186 lv_codepath := jai_general_pkg.plot_codepath(34, lv_codepath);
4187 OPEN c_trx(r_trx.parent_transaction_id);
4188 FETCH c_trx into r_parent_trx;
4189 CLOSE c_trx;
4190
4191 if to_char(r_trx.transaction_date, 'YYYYMM') <> to_char(r_parent_trx.transaction_date, 'YYYYMM') THEN
4192 lv_codepath := jai_general_pkg.plot_codepath(35, lv_codepath);
4193 p_cenvat_rg_flag := 'XT';
4194 p_cenvat_rg_message := jai_rcv_trx_processing_pkg.get_message('BASE-MONTH-DIFFERENT');
4195 goto end_of_cenvat_flag_validation;
4196 end if;
4197 end if;
4198 end if;
4199
4200 -- we dont do validation for ISO in case of trading because RG23D Entry should be passed even if accounting is not required
4201 lv_statement_id := '36';
4202 IF NOT lb_process_iso AND r_trx.organization_type = 'M' THEN
4203 lv_codepath := jai_general_pkg.plot_codepath(36, lv_codepath);
4204 p_cenvat_rg_flag := 'X';
4205 p_cenvat_rg_message := 'ISO Processing Not Required';
4206 goto end_of_cenvat_flag_validation;
4207 END IF;
4208
4209 IF lv_transaction_type IN ('DELIVER', 'RETURN TO RECEIVING') THEN
4210
4211 lv_codepath := jai_general_pkg.plot_codepath(37, lv_codepath);
4212 lv_statement_id := '37';
4213
4214 -- this call is specific to DELIVER and RTR transactions
4215 lv_include_cenvat_in_cost := jai_rcv_deliver_rtr_pkg.include_cenvat_in_costing(
4216 p_transaction_id => p_transaction_id,
4217 p_process_message => p_cenvat_rg_flag,
4218 p_process_status => p_cenvat_rg_message,
4219 p_codepath => lv_codepath
4220 );
4221
4222 lv_statement_id := '38';
4223 -- If cenvat is included in costing, then we need to reverse cenvat entries that are passed during RECEIVE Trxn
4224 IF lv_include_cenvat_in_cost = 'N' THEN
4225 lv_codepath := jai_general_pkg.plot_codepath(38, lv_codepath);
4226 p_cenvat_rg_flag := 'X';
4227 p_cenvat_rg_message := 'Cenvat Entries not Applicable for transaction type';
4228 goto end_of_cenvat_flag_validation;
4229 END IF;
4230
4231 END IF;
4232
4233 /* 2CENVAT_RG_FLAG. END of TRANSACTION VALIDATIONS To SEE WHETHER IT IS QUALIFIED w.r.t NON CENVAT TAXES(CENVAT_RG_FLAG)*/
4234
4235 /* 3CENVAT_RG_FLAG. START of TRANSACTION VALIDATIONS To SEE WHETHER IT IS PENDING FOR SOMETHING INSPITE OF BEING QUALIFIED for PROCESSING*/
4236 lv_statement_id := '39';
4237 if r_trx.transaction_type = 'CORRECT' AND r_trx.parent_transaction_type in ('RECEIVE', 'RETURN TO VENDOR') then
4238 if ld_parent_rg_entry_date IS NULL then
4239 lv_codepath := jai_general_pkg.plot_codepath(39, lv_codepath);
4240 p_cenvat_rg_flag := 'P';
4241 p_cenvat_rg_message := jai_rcv_trx_processing_pkg.get_message('NO-BASE-RG');
4242 goto end_of_cenvat_flag_validation;
4243 end if;
4244 end if;
4245
4246 lv_statement_id := '40';
4247 IF r_trx.transaction_type IN ('RECEIVE', 'MATCH')
4248 AND ( r_receipt_line.excise_invoice_no IS NULL
4249 OR r_receipt_line.excise_invoice_date IS NULL
4250 OR ( nvl(r_receipt_cenvat_dtl.online_claim_flag, 'N') = 'N'
4251 AND nvl(r_receipt_cenvat_dtl.cenvat_amount,0) <> 0
4252 AND p_called_from<>'JAINMVAT'
4253 )
4254 )
4255 THEN
4256 lv_codepath := jai_general_pkg.plot_codepath(40, lv_codepath);
4257 p_cenvat_rg_flag := 'P';
4258 p_cenvat_rg_message := 'Pending for Claim';
4259 goto end_of_cenvat_flag_validation;
4260 END IF;
4261
4262 -- following conditions added by Vijay Shankar for Bug#3940588. RECEIPTS DEPLUG
4263 -- check to stop claim of later transactions of RECEIVE if RECEIVE line is not yet claimed
4264 lv_statement_id := '41';
4265 IF r_trx.transaction_type NOT IN ('RECEIVE', 'MATCH')
4266 AND nvl(r_receipt_cenvat_dtl.cenvat_claimed_amt,0) = 0 AND nvl(r_receipt_cenvat_dtl.cenvat_amount,0) <> 0
4267 THEN
4268 lv_codepath := jai_general_pkg.plot_codepath(41, lv_codepath);
4269 p_cenvat_rg_flag := 'P';
4270 p_cenvat_rg_message := 'Pending for Receipt Line Claim'; -- - '||lv_transaction_type;
4271 goto end_of_cenvat_flag_validation;
4272 END IF;
4273 -- End, Vijay Shankar for Bug#3940588
4274
4275 -- Updation of excise invoice number for all transactions other than RTV
4276 if lv_transaction_type <> 'RETURN TO VENDOR' and r_trx.excise_invoice_no is null then
4277
4278 lv_statement_id := '42';
4279 lv_codepath := jai_general_pkg.plot_codepath(42, lv_codepath);
4280 OPEN c_excise_invoice_no(r_trx.shipment_line_id);
4281 FETCH c_excise_invoice_no INTO r_exc_inv_no;
4282 CLOSE c_excise_invoice_no;
4283
4284 -- this is to update excise invoice no in case of Offline Claim or somehow excise invoice is not update in POPULATE_DETAILS
4285 jai_rcv_transactions_pkg.update_excise_invoice_no(
4286 p_transaction_id => p_transaction_id,
4287 p_excise_invoice_no => r_exc_inv_no.excise_invoice_no,
4288 p_excise_invoice_date => r_exc_inv_no.excise_invoice_date
4289 );
4290 end if;
4291
4292 /* 3CENVAT_RG_FLAG. END of TRANSACTION VALIDATIONS To SEE WHETHER IT IS PENDING FOR SOMETHING INSPITE OF BEING QUALIFIED for PROCESSING*/
4293
4294 p_cenvat_rg_flag :='N';
4295 <<end_of_cenvat_flag_validation>>
4296
4297 /* 4 Start of PROCESS_VAT_FLAG Validation */
4298
4299 --added the below by Ramananda for Bug#4519697
4300 if p_process_vat_flag = jai_constants.successful THEN
4301 goto end_of_vat_validation;
4302 end if;
4303
4304 /* following condition added as part of DFF elimination. Vijay Shankar for Bug#4346453. RCV DFF Elim. Enh. */
4305 if r_trx.process_vat_status in (jai_constants.yes, 'X', 'O') then
4306 lv_codepath := jai_general_pkg.plot_codepath(42.0, lv_codepath);
4307 goto end_of_vat_validation;
4308 end if;
4309
4310 if r_trx.location_id = 0 then
4311 lv_codepath := jai_general_pkg.plot_codepath(42.1, lv_codepath);
4312 p_process_vat_flag := 'O';
4313 p_process_vat_message := jai_rcv_trx_processing_pkg.get_message('NO-LOC-ORG-SETUP');
4314 goto end_of_vat_validation;
4315 end if;
4316
4317 IF lv_transaction_type NOT IN ( 'RECEIVE', 'RETURN TO VENDOR') THEN
4318 lv_statement_id := '42.1';
4319 lv_codepath := jai_general_pkg.plot_codepath(42.2, lv_codepath);
4320 p_process_vat_flag := 'X';
4321 p_process_vat_message := 'VAT Processing not required for this Transaction Type';
4322 GOTO end_of_vat_validation;
4323 END IF;
4324
4325 OPEN c_recoverable_vat_tax_cnt(r_trx.shipment_line_id, jai_constants.vat_regime);
4326 FETCH c_recoverable_vat_tax_cnt INTO ln_recoverable_vat_tax_cnt;
4327 CLOSE c_recoverable_vat_tax_cnt;
4328
4329 IF ln_recoverable_vat_tax_cnt = 0 THEN
4330 lv_statement_id := '42.2';
4331 lv_codepath := jai_general_pkg.plot_codepath(42.3, lv_codepath);
4332 p_process_vat_flag := 'X';
4333 p_process_vat_message := 'No VAT Taxes exist for receipt line';
4334 GOTO end_of_vat_validation;
4335 END IF;
4336 --pramasub FP Iproc start
4337 IF lv_transaction_type = 'RETURN TO VENDOR' then
4338 --IF r_base_trx.generate_excise_invoice = 'N' THEN
4339 --pramasub commented the above line as the same condition is replaced by the following line for bug#4346453
4340 IF r_trx.attribute1 in ( INV_GEN_STATUS_INV_NA ) then
4341 IF NVL(lv_apps_source_code,'$$') = 'POR' THEN /* gen ex inv in the dff is not 'Y' and its a return for iproc return */
4342 p_process_vat_flag := 'O';
4343 p_process_vat_message := 'Call from Iproc returns IL page';
4344 GOTO end_of_vat_validation;
4345 END IF;
4346 END IF;
4347 END IF;
4348 --pramasub FP IProc end
4349 p_process_vat_flag := 'N';
4350
4351 <<end_of_vat_validation>>
4352
4353 /* Vijay Shankar for Bug#4346453. RCV DFF Elim. Enh. */
4354 if r_trx.transaction_type = 'RETURN TO VENDOR' then
4355
4356 /* Excise */
4357 --if p_called_from in (CALLED_FROM_RCV_TRIGGER, CALLED_FROM_FND_REQUEST) then
4358 if lv_excise_inv_gen_action is null and r_trx.attribute1 is null then -- and r_trx.attribute3 is null then
4359 lv_statement_id := '60';
4360 lv_codepath := jai_general_pkg.plot_codepath(60, lv_codepath);
4361 /* Flag is set to 'P' only if validations that set the flag as 'X' are done */
4362 if p_cenvat_rg_flag in ('N', 'P') then -- FLAG 'P' is not considered as the transaction will be processed again and FLAG will be set to 'N'
4363 lv_excise_inv_gen_action := INV_GEN_STATUS_PENDING;
4364 elsif p_cenvat_rg_flag in ('X', 'O', 'XT') then
4365 lv_excise_inv_gen_action := INV_GEN_STATUS_NA;
4366 end if;
4367
4368 elsif p_called_from = CALLED_FROM_JAITIGRTV and r_trx.attribute1 = INV_GEN_STATUS_INV_NA then
4369 lv_statement_id := '61';
4370 lv_codepath := jai_general_pkg.plot_codepath(61, lv_codepath);
4371 p_cenvat_rg_flag := 'X';
4372 p_cenvat_rg_message := 'Excise Invoice is not applicable';
4373 end if;
4374
4375 /* VAT */
4376 if lv_vat_inv_gen_action is null and r_trx.attribute2 is null then -- and r_trx.attribute4 is null then
4377 lv_statement_id := '62';
4378 lv_codepath := jai_general_pkg.plot_codepath(62, lv_codepath);
4379 if p_process_vat_flag in ('N') then
4380 lv_vat_inv_gen_action := INV_GEN_STATUS_PENDING;
4381 elsif p_process_vat_flag in ('X', 'O') then
4382 lv_vat_inv_gen_action := INV_GEN_STATUS_NA;
4383 end if;
4384 end if;
4385 end if;
4386
4387 IF p_simulate_flag = 'N' THEN
4388 lv_codepath := jai_general_pkg.plot_codepath(43, lv_codepath);
4389 lv_statement_id := '43';
4390 /* Call to update the Flag values as the validation is completed */
4391 jai_rcv_transactions_pkg.update_process_flags(
4392 p_transaction_id => p_transaction_id,
4393 p_process_flag => p_process_flag,
4394 p_process_message => p_process_message,
4395 p_cenvat_rg_flag => p_cenvat_rg_flag,
4396 p_cenvat_rg_message => p_cenvat_rg_message,
4397 p_process_vat_flag => p_process_vat_flag,
4398 p_process_vat_message => p_process_vat_message,
4399 p_process_date => SYSDATE
4400 );
4401
4402 /* Vijay Shankar for Bug#4346453. RCV DFF Elim. Enh. */
4403 if p_called_from <> CALLED_FROM_JAITIGRTV THEN
4404 /* Bug 5365346. Added by Lakshmi Gopalsami
4405 | Check whether 57F4 transaction has been created for PO
4406 | before updating Excise invoice action. If so we need to set
4407 | the value to 'PENDING' instead of 'NOT_APPLICABLE
4408 */
4409 fnd_file.put_line(FND_FILE.LOG, ' transaction id ' || p_transaction_id);
4410
4411 IF ( Check_57F4_transaction( p_transaction_id ) = 'YES' ) THEN
4412 lv_excise_inv_gen_action := INV_GEN_STATUS_PENDING;
4413 END IF ;
4414
4415 jai_rcv_transactions_pkg.update_attributes(
4416 p_transaction_id => p_transaction_id,
4417 p_attribute1 => lv_excise_inv_gen_action,
4418 p_attribute2 => lv_vat_inv_gen_action
4419 );
4420 end if;
4421
4422 END IF;
4423
4424 -- this is the final place where we assign the value to p_codepath from local codepath
4425 lv_statement_id := '49';
4426 p_codepath := jai_general_pkg.plot_codepath(lv_codepath||',49', p_codepath, 'cenvat_rg_pkg.validate_trx', 'END');
4427 -- p_codepath := substr(p_codepath||lv_codepath, 1, 2000);
4428
4429 FND_FILE.put_line( fnd_file.log, '$ VALIDATE_TRANSACTION PrcFlg:'||p_process_flag||', Msg:'||p_process_message
4430 ||', CenvatRgFlg:'||p_cenvat_rg_flag ||', Msg:'||p_cenvat_rg_message
4431 ||', PrcVatFlg:'||p_process_vat_flag ||', Msg:'||p_process_vat_message
4432 ||', localPath:'||lv_codepath
4433 );
4434
4435 EXCEPTION
4436 when others then
4437 p_process_flag := 'E';
4438 p_process_message := 'RECEIPT_TRANSACTION_PKG.Validate_transaction:'||SQLERRM||', Statement_id:'||lv_statement_id;
4439 FND_FILE.put_line( fnd_file.log, 'Error in '||p_process_message||'. localErrorPath:'||lv_codepath );
4440 p_codepath := jai_general_pkg.plot_codepath(lv_codepath||',-999', p_codepath, 'cenvat_rg_pkg.validate_trx', 'END');
4441
4442 END validate_transaction;
4443
4444
4445 FUNCTION process_iso_transaction(
4446 p_transaction_id IN NUMBER,
4447 p_shipment_line_id IN NUMBER
4448 ) RETURN BOOLEAN
4449 IS
4450
4451 CURSOR c_shp_hdr(cp_transaction_id number) IS
4452 SELECT receipt_source_code
4453 from rcv_shipment_headers
4454 WHERE shipment_header_id = (select shipment_header_id
4455 from rcv_transactions
4456 where transaction_id = cp_transaction_id);
4457
4458 CURSOR c_shp_line(cp_shipment_line_id number) IS
4459 SELECT from_organization_id, to_organization_id
4460 FROM rcv_shipment_lines
4461 WHERE shipment_line_id = cp_shipment_line_id;
4462
4463 CURSOR c_excise_tax_cnt(cp_shipment_line_id number) is
4464 SELECT count(1)
4465 FROM JAI_RCV_LINE_TAXES
4466 WHERE shipment_line_id = cp_shipment_line_id
4467 -- CVD is Not Considered, because in ISO scenario CVD is not supported.
4468 AND upper(tax_type) IN ('EXCISE', 'ADDL. EXCISE', 'OTHER EXCISE', jai_constants.tax_type_exc_edu_cess,jai_constants.tax_type_sh_exc_edu_cess); --Added by kunkumar for bugno5989740 -- Vijay Shankar for Bug#3940588 EDU CESS
4469
4470 CURSOR c_organization_info(cp_organization_id number) IS
4471 SELECT nvl(trading, 'N') trading, nvl(manufacturing, 'N') manufacturing
4472 , nvl(excise_in_rg23d, 'N') excise_in_rg23d -- Vijay Shankar for Bug#4171469
4473 FROM JAI_CMN_INVENTORY_ORGS
4474 WHERE organization_id = cp_organization_id
4475 AND rownum = 1;
4476
4477 lb_process_iso_transaction BOOLEAN; --File.Sql.35 Cbabu := true;
4478
4479 /* Added by Ramananda for bug#4407165 */
4480 lv_object_name CONSTANT VARCHAR2(61) := 'jai_rcv_trx_processing_pkg.process_iso_transaction';
4481
4482 ln_excise_tax_cnt NUMBER;
4483 r_shp_line c_shp_line%ROWTYPE;
4484 r_shp_hdr c_shp_hdr%ROWTYPE;
4485 r_dest_org c_organization_info%ROWTYPE;
4486 r_src_org c_organization_info%ROWTYPE;
4487
4488 BEGIN
4489
4490 lb_process_iso_transaction := true;
4491
4492 OPEN c_shp_hdr(p_transaction_id);
4493 FETCH c_shp_hdr INTO r_shp_hdr;
4494 CLOSE c_shp_hdr;
4495
4496 IF r_shp_hdr.receipt_source_code IN ('INTERNAL ORDER','INVENTORY') THEN /*rchandan for bug#6030615*/
4497
4498 OPEN c_excise_tax_cnt(p_shipment_line_id);
4499 FETCH c_excise_tax_cnt INTO ln_excise_tax_cnt;
4500 CLOSE c_excise_tax_cnt;
4501
4502 IF ln_excise_tax_cnt > 0 THEN
4503
4504 OPEN c_shp_line(p_shipment_line_id);
4505 FETCH c_shp_line INTO r_shp_line;
4506 CLOSE c_shp_line;
4507
4508 OPEN c_organization_info(r_shp_line.from_organization_id);
4509 FETCH c_organization_info INTO r_src_org;
4510 CLOSE c_organization_info;
4511
4512 IF r_src_org.trading = 'Y' THEN
4513 OPEN c_organization_info(r_shp_line.to_organization_id);
4514 FETCH c_organization_info INTO r_dest_org;
4515 CLOSE c_organization_info;
4516
4517 /* Vijay Shankar for Bug#4171469
4518 following condition modified to pass accounting incase of Trading to Trading with both orgs
4519 having excise_in_rg23d flag set to 'Y'
4520 */
4521 -- if this following condition is true, then it means ISO Processing is not required
4522 -- IF r_dest_org.trading = 'Y' OR r_dest_org.manufacturing = 'Y' THEN
4523 -- following condition modified by Vijay Shankar for Bug#4171469
4524 IF r_dest_org.trading = 'Y' THEN
4525 IF r_src_org.excise_in_rg23d <> 'Y'
4526 --OR r_dest_org.excise_in_rg23d <> 'Y' --commented by Ramananda for Bug #4516577
4527 THEN
4528 lb_process_iso_transaction := false;
4529 END IF;
4530
4531 ELSIF r_dest_org.manufacturing = 'Y' THEN
4532 IF r_src_org.excise_in_rg23d <> 'Y' THEN --Added the if condition by Ramananda for Bug #4516577
4533 lb_process_iso_transaction := false;
4534 END IF;
4535 END IF;
4536 END IF;
4537
4538 END IF;
4539
4540 END IF;
4541
4542 RETURN lb_process_iso_transaction;
4543
4544 /* Added by Ramananda for bug#4407165 */
4545 EXCEPTION
4546 WHEN OTHERS THEN
4547 FND_MESSAGE.SET_NAME('JA','JAI_EXCEPTION_OCCURED');
4548 FND_MESSAGE.SET_TOKEN('JAI_PROCESS_MSG', lv_object_name ||'. Err:'||sqlerrm );
4549 app_exception.raise_exception;
4550
4551 END process_iso_transaction;
4552
4553
4554 FUNCTION get_ancestor_id(
4555 p_transaction_id IN NUMBER,
4556 p_shipment_line_id IN NUMBER,
4557 p_required_trx_type IN VARCHAR2
4558 ) RETURN NUMBER IS
4559
4560 ln_trx_id NUMBER;
4561
4562 lv_transaction_type RCV_TRANSACTIONS.transaction_type%TYPE;
4563
4564 BEGIN
4565
4566 ln_trx_id := p_transaction_id;
4567 FOR i IN (select transaction_id, transaction_type, parent_transaction_id
4568 from rcv_transactions
4569 where shipment_line_id = p_shipment_line_id
4570 and transaction_id <= p_transaction_id
4571 order by transaction_id desc)
4572 LOOP
4573
4574 IF i.transaction_id = ln_trx_id THEN
4575 -- added for Vijay Shankar for Bug#3940588
4576 --IF p_required_trx_type = 'MATCH' THEN /*commented by vkaranam for bug #4636397*/
4577 IF i.transaction_type = 'MATCH' THEN /*added by vkaranam for bug #4636397*/
4578 lv_transaction_type := 'RECEIVE';
4579 ELSE
4580 lv_transaction_type := i.transaction_type;
4581 END IF;
4582 -- End, Vijay Shankar for Bug#3940588
4583
4584 IF lv_transaction_type = p_required_trx_type THEN
4585 RETURN i.transaction_id;
4586 ELSE
4587 ln_trx_id := i.parent_transaction_id;
4588 END IF;
4589 END IF;
4590 END LOOP;
4591
4592 RETURN NULL;
4593 END get_ancestor_id;
4594
4595 FUNCTION get_trxn_tax_amount(
4596 p_transaction_id IN NUMBER,
4597 p_shipment_line_id IN NUMBER,
4598 p_curr_conv_rate IN NUMBER,
4599 p_return_in_inr_curr IN VARCHAR2 --File.Sql.35 Cbabu DEFAULT 'Y'
4600 ) RETURN NUMBER IS
4601
4602 /*
4603 Transaction can have two Currencies 1. Functional(INR) 2. Transactional (Non INR Currency in case of foreign Trxn)
4604 If p_return_in_inr_curr = 'Y' then Functional tax amount is returned otherwise in transactional currency in returned
4605 Tax amount returned is to the tune of TRANSACTION Quantity.
4606 eg in ILDEV -> select jai_rcv_trx_processing_pkg.get_trxn_tax_amount(14108, 10626, 50, 'N') amount from dual;
4607 */
4608
4609 -- This cursor gives tax_amount in FOREIGN Currency
4610 CURSOR c_tax_amount(cp_shipment_line_id IN NUMBER, cp_curr_conv_rate IN NUMBER) IS
4611 SELECT
4612 sum(
4613 nvl(tax_amount, 0) / decode(currency, jai_general_pkg.INDIAN_CURRENCY, cp_curr_conv_rate, 1)
4614 ) non_inr_tax_amount,
4615 sum(
4616 nvl(tax_amount, 0) * decode(currency, jai_general_pkg.INDIAN_CURRENCY, 1, cp_curr_conv_rate)
4617 ) inr_tax_amount
4618 FROM JAI_RCV_LINE_TAXES
4619 WHERE shipment_line_id = cp_shipment_line_id
4620 AND tax_type NOT IN ('TDS', 'Modvat Recovery');
4621
4622 ln_tax_amount NUMBER;
4623 ln_inr_tax_amount NUMBER;
4624 ln_non_inr_tax_amount NUMBER;
4625
4626 BEGIN
4627
4628 OPEN c_tax_amount(p_shipment_line_id, p_curr_conv_rate);
4629 FETCH c_tax_amount INTO ln_non_inr_tax_amount, ln_inr_tax_amount;
4630 CLOSE c_tax_amount;
4631
4632 IF p_return_in_inr_curr = 'Y' THEN
4633 ln_tax_amount := ln_inr_tax_amount;
4634 ELSE
4635 ln_tax_amount := ln_non_inr_tax_amount;
4636 END IF;
4637
4638 ln_tax_amount := nvl(ln_tax_amount, 0) * get_apportion_factor( p_transaction_id => p_transaction_id );
4639
4640 RETURN nvl(ln_tax_amount, 0);
4641
4642 END get_trxn_tax_amount;
4643
4644 FUNCTION get_trxn_cenvat_amount(
4645 p_transaction_id IN NUMBER,
4646 p_shipment_line_id IN NUMBER,
4647 p_organization_type IN VARCHAR2,
4648 p_curr_conv_rate IN NUMBER
4649 ) RETURN NUMBER IS
4650
4651 /*
4652 This Always Returns Total Cenvat amount in INR Currency to the tune of transaction quantity, uom
4653 eg in ILDEV -> select jai_rcv_trx_processing_pkg.get_trxn_cenvat_amount(14108, 10626, 50) amount from dual;
4654 */
4655
4656 -- This cursor gives tax_amount in FOREIGN Currency
4657 CURSOR c_tax_amount(cp_shipment_line_id IN NUMBER, cp_curr_conv_rate IN NUMBER, cp_organization_type IN VARCHAR2) IS
4658 SELECT
4659 sum(
4660 nvl(a.tax_amount, 0) * (b.mod_cr_percentage/100)
4661 * decode(a.currency, jai_general_pkg.INDIAN_CURRENCY, 1, cp_curr_conv_rate)
4662 ) manufacturing_cenvat,
4663 sum(
4664 nvl(a.tax_amount, 0) * decode(a.currency, jai_general_pkg.INDIAN_CURRENCY, 1, cp_curr_conv_rate)
4665 ) trading_cenvat
4666 FROM JAI_RCV_LINE_TAXES a, JAI_CMN_TAXES_ALL b
4667 WHERE shipment_line_id = cp_shipment_line_id
4668 AND a.tax_id = b.tax_id
4669 AND upper(a.tax_type) IN ( 'EXCISE',
4670 'ADDL. EXCISE',
4671 'OTHER EXCISE',
4672 'CVD',
4673 jai_constants.tax_type_add_cvd,
4674 -- Modified by SACSETHI Bug# 5228046
4675 -- Forward porting the change in 11i bug 5365523
4676 -- (Additional CVD Enhancement) as part of the R12 bug 5228046
4677 jai_constants.tax_type_exc_edu_cess,
4678 jai_constants.tax_type_cvd_edu_cess,jai_constants.tax_type_sh_exc_edu_cess,
4679 jai_constants.tax_type_sh_cvd_edu_cess) --Added by kunkumar for bugno5989740 -- Vijay Shankar for Bug#3940588 EDU CESS
4680 AND (cp_organization_type = 'T' OR (cp_organization_type <> 'T' AND a.modvat_flag = 'Y') );
4681
4682 ln_manufacturing_cenvat_amount NUMBER;
4683 ln_trading_cenvat_amount NUMBER;
4684 ln_tax_amount NUMBER;
4685
4686 BEGIN
4687
4688 OPEN c_tax_amount(p_shipment_line_id, p_curr_conv_rate, p_organization_type);
4689 FETCH c_tax_amount INTO ln_manufacturing_cenvat_amount, ln_trading_cenvat_amount;
4690 CLOSE c_tax_amount;
4691
4692 IF p_organization_type = 'M' THEN
4693 ln_tax_amount := ln_manufacturing_cenvat_amount;
4694 ELSIF p_organization_type = 'T' THEN
4695 ln_tax_amount := ln_trading_cenvat_amount;
4696 END IF;
4697
4698 ln_tax_amount := nvl(ln_tax_amount, 0) * get_apportion_factor( p_transaction_id => p_transaction_id );
4699
4700 RETURN nvl(ln_tax_amount, 0);
4701
4702 END get_trxn_cenvat_amount;
4703
4704
4705 FUNCTION get_apportion_factor(
4706 p_transaction_id IN NUMBER
4707 ) RETURN NUMBER IS
4708
4709 /*
4710 Returns the Value of (TransactionQuantity * TransactionUOM) / (TaxQuantity * TaxUom)
4711 i.e if Transaction Quantity, UOM = 18, EACH
4712 and Tax Quantity, UOM = 5, DOZEN
4713 then this function returns (18*1/5*12)=0.3
4714 */
4715
4716 /* Added by Ramananda for bug#4407165 */
4717 lv_object_name CONSTANT VARCHAR2(61) := 'jai_rcv_trx_processing_pkg.get_apportion_factor';
4718
4719 CURSOR c_ja_in_receipt_lines_qty(cp_shipment_line_id IN NUMBER) IS
4720 SELECT qty_received, transaction_id
4721 FROM JAI_RCV_LINES
4722 WHERE shipment_line_id = cp_shipment_line_id;
4723
4724 r_trx c_trx%ROWTYPE;
4725 r_tax_trx c_base_trx%ROWTYPE;
4726
4727 ln_tax_transaction_id NUMBER;
4728 ln_tax_quantity NUMBER;
4729 lv_tax_uom_code MTL_UNITS_OF_MEASURE.uom_code%TYPE;
4730 lv_trxn_uom_code MTL_UNITS_OF_MEASURE.uom_code%TYPE;
4731
4732 ln_uom_conv_rate NUMBER;
4733 ln_apportion_factor NUMBER;
4734
4735 BEGIN
4736
4737 -- if this is called from jai_rcv_trx_processing_pkg.populate_details then most of the fields are NULL.
4738 -- So, Check whether the required values are populated or not before proceding further
4739 OPEN c_trx(p_transaction_id);
4740 FETCH c_trx INTO r_trx;
4741 CLOSE c_trx;
4742
4743 OPEN c_ja_in_receipt_lines_qty(r_trx.shipment_line_id);
4744 FETCH c_ja_in_receipt_lines_qty INTO ln_tax_quantity, ln_tax_transaction_id;
4745 CLOSE c_ja_in_receipt_lines_qty;
4746
4747 IF ln_tax_quantity = 0 THEN
4748 RETURN 0;
4749 END IF;
4750
4751 OPEN c_base_trx(ln_tax_transaction_id);
4752 FETCH c_base_trx INTO r_tax_trx;
4753 CLOSE c_base_trx;
4754
4755 lv_trxn_uom_code := r_trx.uom_code;
4756 lv_tax_uom_code := nvl(r_tax_trx.uom_code,
4757 jai_general_pkg.get_uom_code( p_uom => r_tax_trx.unit_of_measure)
4758 );
4759
4760 IF lv_trxn_uom_code = lv_tax_uom_code THEN
4761 ln_uom_conv_rate := 1;
4762 ELSE
4763 ln_uom_conv_rate := jai_general_pkg.trxn_to_primary_conv_rate(
4764 p_transaction_uom_code => lv_trxn_uom_code,
4765 p_primary_uom_code => lv_tax_uom_code,
4766 p_inventory_item_id => r_trx.inventory_item_id
4767 );
4768 END IF;
4769
4770 ln_apportion_factor := ln_uom_conv_rate * r_trx.quantity/ln_tax_quantity;
4771
4772 RETURN ln_apportion_factor;
4773
4774 /* Added by Ramananda for bug#4407165 */
4775 EXCEPTION
4776 WHEN OTHERS THEN
4777 FND_MESSAGE.SET_NAME('JA','JAI_EXCEPTION_OCCURED');
4778 FND_MESSAGE.SET_TOKEN('JAI_PROCESS_MSG', lv_object_name ||'. Err:'||sqlerrm );
4779 app_exception.raise_exception;
4780
4781 END get_apportion_factor;
4782
4783 FUNCTION get_equivalent_qty_of_receive(
4784 p_transaction_id IN NUMBER
4785 ) RETURN NUMBER IS
4786
4787 /*
4788 Returns the Value of (TransactionQuantity * TransactionUOM) / (TaxUom)
4789 i.e if Transaction Quantity, UOM = 18, EACH
4790 and Tax Quantity, UOM = 5, DOZEN
4791 then this function returns (18*1/12) = 1.5
4792 */
4793
4794 CURSOR c_ja_in_receipt_lines_qty(cp_shipment_line_id IN NUMBER) IS
4795 SELECT qty_received, transaction_id
4796 FROM JAI_RCV_LINES
4797 WHERE shipment_line_id = cp_shipment_line_id;
4798
4799 r_trx c_trx%ROWTYPE;
4800 r_tax_trx c_base_trx%ROWTYPE;
4801
4802 ln_tax_transaction_id NUMBER;
4803 ln_tax_quantity NUMBER;
4804 lv_tax_uom_code MTL_UNITS_OF_MEASURE.uom_code%TYPE;
4805 lv_trxn_uom_code MTL_UNITS_OF_MEASURE.uom_code%TYPE;
4806
4807 ln_uom_conv_rate NUMBER;
4808 ln_apportion_factor NUMBER;
4809
4810 /* Added by Ramananda for bug#4407165 */
4811 lv_object_name CONSTANT VARCHAR2(61) := 'jai_rcv_trx_processing_pkg.get_equivalent_qty_of_receive';
4812
4813 BEGIN
4814
4815 -- if this is called from jai_rcv_trx_processing_pkg.populate_details then most of the fields are NULL.
4816 -- So, Check whether the required values are populated or not before proceding further
4817 OPEN c_trx(p_transaction_id);
4818 FETCH c_trx INTO r_trx;
4819 CLOSE c_trx;
4820
4821 OPEN c_ja_in_receipt_lines_qty(r_trx.shipment_line_id);
4822 FETCH c_ja_in_receipt_lines_qty INTO ln_tax_quantity, ln_tax_transaction_id;
4823 CLOSE c_ja_in_receipt_lines_qty;
4824
4825 IF ln_tax_quantity = 0 THEN
4826 RETURN 0;
4827 END IF;
4828
4829 OPEN c_base_trx(ln_tax_transaction_id);
4830 FETCH c_base_trx INTO r_tax_trx;
4831 CLOSE c_base_trx;
4832
4833 lv_trxn_uom_code := r_trx.uom_code;
4834 lv_tax_uom_code := nvl(r_tax_trx.uom_code,
4835 jai_general_pkg.get_uom_code( p_uom => r_tax_trx.unit_of_measure)
4836 );
4837
4838 IF lv_trxn_uom_code = lv_tax_uom_code THEN
4839 ln_uom_conv_rate := 1;
4840 ELSE
4841 ln_uom_conv_rate := jai_general_pkg.trxn_to_primary_conv_rate(
4842 p_transaction_uom_code => lv_trxn_uom_code,
4843 p_primary_uom_code => lv_tax_uom_code,
4844 p_inventory_item_id => r_trx.inventory_item_id
4845 );
4846 END IF;
4847
4848 ln_apportion_factor := ln_uom_conv_rate * r_trx.quantity;
4849
4850 RETURN ln_apportion_factor;
4851
4852
4853 /* Added by Ramananda for bug#4407165 */
4854 EXCEPTION
4855 WHEN OTHERS THEN
4856 FND_MESSAGE.SET_NAME('JA','JAI_EXCEPTION_OCCURED');
4857 FND_MESSAGE.SET_TOKEN('JAI_PROCESS_MSG', lv_object_name ||'. Err:'||sqlerrm );
4858 app_exception.raise_exception;
4859
4860 END get_equivalent_qty_of_receive;
4861
4862
4863 FUNCTION get_message( p_message_code IN VARCHAR2) RETURN VARCHAR2 IS
4864 lv_message JAI_RCV_TRANSACTIONS.process_message%type;
4865 BEGIN
4866
4867 IF p_message_code = 'NO-ORG' THEN
4868 lv_message := 'Organization is not found ';
4869
4870 ELSIF p_message_code = 'NO-LOC-ORG-SETUP' THEN
4871 lv_message := 'Localization setup does not exist for this Location';
4872
4873 ELSIF p_message_code = 'TAX-NOT-MODIFIED' THEN
4874 lv_message := 'Taxes can still be modified ';
4875
4876 ELSIF p_message_code = 'NO-TAXES' THEN
4877 lv_message := 'Localization taxes does not exist for this Organization';
4878
4879 ELSIF p_message_code = 'NO-BASE-ACCT' THEN
4880 lv_message := 'The Initial Line Accounting/costing is not done';
4881
4882 ELSIF p_message_code = 'ITEM-CLASS-NULL' THEN
4883 lv_message := 'Item Class is null ';
4884
4885 ELSIF p_message_code = 'ITEM-CLASS-OTIN' THEN
4886 lv_message := 'Item Class is OTIN/OTEX ';
4887
4888 ELSIF p_message_code = 'ITEM-TRADING-NO' THEN
4889 lv_message := 'Item Trading Flag is Not present ';
4890
4891 ELSIF p_message_code = 'ITEM-CENVATABLE-NO' THEN
4892 lv_message := 'Item Modvat Flag is Not present ';
4893
4894 ELSIF p_message_code = 'NO-EXCISE-TAXES' THEN
4895 lv_message := 'No Excise Taxes are present ';
4896
4897 ELSIF p_message_code = 'NO-BASE-RG' THEN
4898 lv_message := 'The Initial RG Entry for the parent transaction is not passed ';
4899
4900 ELSIF p_message_code ='BASE-MONTH-DIFFERENT' THEN
4901 lv_message := 'Month is changed since Parent RG Entry was passed';
4902 END IF;
4903
4904 return lv_message;
4905
4906 END get_message;
4907
4908 --------------------------------------End Message Procedure------------------------------------------------------
4909
4910 FUNCTION get_object_code( p_object_name IN VARCHAR2, p_event_name IN VARCHAR2) RETURN VARCHAR2 IS
4911
4912 -- This is for future use
4913 BEGIN
4914
4915 return p_object_name||p_event_name||':';
4916 END get_object_code;
4917
4918 FUNCTION get_accrue_on_receipt(
4919 p_po_distribution_id IN NUMBER,
4920 p_po_line_location_id IN NUMBER DEFAULT NULL
4921 ) RETURN VARCHAR2 IS
4922
4923 CURSOR c_dist_dtl(cp_po_distribution_id IN NUMBER) IS
4924 SELECT accrue_on_receipt_flag
4925 FROM po_distributions_all
4926 WHERE po_distribution_id = cp_po_distribution_id;
4927
4928 /* added by Vijay Shankar for Bug#4215402 */
4929 CURSOR c_po_shipment_dtl(cp_po_line_location_id IN NUMBER) IS
4930 SELECT accrue_on_receipt_flag
4931 FROM po_line_locations_all
4932 WHERE line_location_id = cp_po_line_location_id;
4933
4934 lv_accrue_on_receipt_flag po_line_locations_all.accrue_on_receipt_flag%TYPE;
4935
4936 BEGIN
4937
4938 IF p_po_distribution_id IS NOT NULL THEN
4939 OPEN c_dist_dtl(p_po_distribution_id);
4940 FETCH c_dist_dtl INTO lv_accrue_on_receipt_flag;
4941 CLOSE c_dist_dtl;
4942
4943 /* added by Vijay Shankar for Bug#4215402 */
4944 ELSE
4945 OPEN c_po_shipment_dtl(p_po_line_location_id);
4946 FETCH c_po_shipment_dtl INTO lv_accrue_on_receipt_flag;
4947 CLOSE c_po_shipment_dtl;
4948 END IF;
4949
4950 RETURN nvl(lv_accrue_on_receipt_flag, 'N');
4951
4952 END get_accrue_on_receipt;
4953
4954 END jai_rcv_trx_processing_pkg;
[Home] [Help]