1 PACKAGE BODY jai_rcv_trx_processing_pkg AS
2 /* $Header: jai_rcv_trx_prc.plb 120.61.12020000.9 2013/02/19 01:50:44 vkaranam 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 34. 30-Mar-2009 Bug 8346068 Version 120.11.12000000.5
366 Issue: CENVAT UNCLAIMED AFTER DELIVERY OF ITEMS IS NOT LOADED TO ITEM COST
367 Fix : commented the below condition
368 * and attribute1 = jai_rcv_deliver_rtr_pkg.cenvat_costed_flag *
369 in the cursor c_trxs_for_unclaim.
370
371 35. 28-APR-2009 Bug 8410609
372 Issue - Excise invoice number is getting generated for Return To Vendor transactions
373 of OSP items.
374 Expected Behavior - Excise invoice should not be generated for RTV of OSP items. They
375 are tracked using 57F4 challan.
376 Fix - Added the condition "AND Check_57F4_transaction(rtv_rec.transaction_id) <> 'YES'"
377 in PROCESS_RTV procedure for the generation of excise invoice number.
378
379 36. 15-JUN-2009 Bug 8319304 File version 120.11.12000000.7 / 120.25.12010000.5 / 120.29
380 Description : Forward porting of bugs 6914674 and 8314743. Modified the validate_transaction procedure
381 so that quantity registers would be hit when the item is excisable, and has excise taxes but with
382 recoverable amounts as zero.
383
384 37. 21-May-2009 Bug 8538155 (FP for bug 8466620) File version 120.11.12000000.8 / 120.25.12010000.6 / 120.30
385 Issue : Quantity register entry is not reversed during Deliver, when destination is
386 Expense and the receipt does not have excise taxes.
387 Fix : As per inputs from PM, fix is done using these guidelines:
388 1. Quantity register should not be hit during Receive, when destination is
389 Expense.
390 2. There should not be any entries in the amount register with zero amount.
391 3. If quantity register gets updated (due to Claim done by mistake), it should
392 be reversed when Deliver happens.
393
394 Necessary changes are done in validate_transaction procedure.
395
396 38. 10-Aug-2009 Bug 8319304 File version 120.11.12000000.9 / 120.25.12010000.7 / 120.31
397 Fixed review comments - ported the missing changes in process_transaction procedure for bug 6914674.
398
399 39 09-Aug-2009 Bug 8648138
400 Issue - If excise tax is unclaimed after running "Mass Addtions Create" program, the
401 unclaimed amount does not flow to assets when "Mass Additions Create" is run
402 again.
403 Fix - Added code in process_deferred_cenvat_claim procedure to update related flags in
404 ap_invoice_distributions_all for the matched invoices so that the tax distributions will
405 be picked up by the "Mass Additions Create" program.
406
407 40 23-Oct-2009 Bug 9032251 File version 120.11.12000000.12 / 120.25.12010000.10 / 120.34
408 Issue - If the receipt line has excise and vat taxes, and excise is unclaimed after
409 vat is unclaimed, costing entries are not generated for the exicse (cenvat) taxes.
410 Fix - Commented the "and (attribute2 IS NULL or attribute2 <> jai_constants.yes)"
411 condition in c_trxs_for_unclaim cursor in process_deferred_cenvat_claim procedure.
412
413 41. 26-Oct-2009 CSahoo for bug#9019561, File Version 120.11.12000000.13
414 Issue: NON RECOVERABLE TAXES ON PO ,IF CLAIMED FOR CENVAT IT IS HITTING THE EX.REGISTER
415 Fix: Made the changes in the procedure validate_transaction. Added the check that for trading
416 org, if there is no recoverable excise tax, then it would update the p_cenvat_rg_flag as 'X'.
417 In such case, the cenvat won't hit the registers.
418
419 42. 01-dec-2009 vkaranam for bug#7595016,File version 120.11.12000000.14
420 Issue:
421 RG23D NOT UPDATED AFTER RECEIVING THE ITEMS IN TRADING ORG. AT MODVAT LOCATION.
422 Fix:
423 For a trading org RG23D shall be hit if the item is excisable and the receipt
424 transaction has the excise taxes irrespective of whether the tax amount is 0
425 or not.
426 Cause for the issue:
427 Validate_transaction procedure :
428 if r_taxes.excise_cnt = 0 or r_receipt_cenvat_dtl.cenvat_amount = 0 then
429 lv_codepath := jai_general_pkg.plot_codepath(18, lv_codepath);
430 p_cenvat_rg_flag := 'X';
431 p_cenvat_rg_message := 'Excise Taxes do not exist';
432 goto end_of_cenvat_flag_validation;
433 end if;
434 Change details:
435 Added organization_type condition
436 --if r_taxes.excise_cnt = 0 or(r_trx.organization_type='M' and r_receipt_cenvat_dtl.cenvat_amount = 0 )
437
438 43. 25-aug-2010 vkaranam for bug#10036144
439 Issue:GOODS RETURN FROM CUSTOMER IS NOT HITTING RG1 REGISTER
440 Reason: IF r_base_trx.attr_cat = 'India RMA Receipt' AND r_trx.item_class IN ('FGIN',
441 'FGEX', 'CCIN', 'CCEX')
442
443
444 OR (r_base_trx.attr_Cat = 'India Receipt' and
445 r_base_trx.source_document_Code = 'INVENTORY' AND r_trx.item_class IN
446 ('FGIN', 'FGEX', 'CCIN', 'CCEX')) THEN
447
448 as part of dff elimination we are not populating attribute category as "India
449 RMA Receipt"/"India Receipt".
450
451
452
453 fix:
454 removed the attribute condition,query by bug number to see the change
455
456
457
458 26-nov-2010 Bug 10335708
459 Description : Customer requirement is that they should be able to claim CENVAT credit
460 on items delivered to EXPENSE. As the items are not tracked as Inventory, the quantity
461 register should be reversed immediately on delivery (or claim, whichever happens later).
462 Modified the validate_transaction procedure to handle this case.
463 09-aug-2011 vkaranam for bug#12817175
464 Issue:
465 The Inclusive taxes are loading to item cost while unclaiming the CENVAT.
466 Fix:
467 Case 1: Scenario where Cenvat is unclaimed after Receipt and Delivered to WH
468 (Recoverable Tax is treated as non-recoverable at time of Cenvat unclaim)
469 Event1 : Receive
470 Dr Inventory Receiving A/c - 1000
471 Cr AP Accrual A/c - 1000
472 Event 2: Delivery (This is Negative Value Cost Adjustment to Reduce Item Cost
473 by 100 for Recoverable tax component)
474 Dr Sub Inventory A/c - 100
475 Cr Inventory Material Value A/c - 100
476 Event3 : Cenvat unClaim (Reverse Entry passed at time Delivery for Negative
477 Value Cost Adjustment - Ideally the amount of tax now needs to be added back
478 to the Material Value)
479 Dr Inventory Material Value A/c - 100
480 Cr Sub Inventory A/c -100
481
482 Case 2: Scenario where Delivery happens to non-bonded Subinventory after
483 receipt (Recoverable Tax is treated as non-recoverable at time of Delivery
484 to non bonded subinventory)
485 Event1 : Receive
486 Dr Inventory Receiving A/c - 1000
487 Cr AP Accrual A/c - 1000
488 Event 2: Delivery to nonbonded subinventory
489 No Accounting Entry
490 Case 3:
491 scenario where Cenvat is unclaimed after Receipt and Delivered to non bonded WH
492 Unclaim : no accounting entry
493 Delivery : no accounting entry
494
495 Changes are done in process_deferred_cenvat_claim to support the above cases.
496
497 41. 16-Aug-2011 vkavulur Bug#12818250
498 Issue: NON BONDED SUB INVENTORY TRANSACTION IS HITING RG1 REGISTER
499 Fix: Made the changes in the procedure validate_transaction. Added the check that for deliver
500 transactions also the rg register is hit in case of non bonded sub inventories so that a reverse
501 transaction can be passed for offsetting the receive transaction already done.
502 42. 24-nov-2011 vkaranam for bug#13364465
503 issue:RTV TRANSACTION NOT FOUND IN RETURN TO VENDOR(INDIA) FORM FOR PROCESSING
504 --DFF er changes has been removed in 12.1.1 branch file due to enabling the dualcheckin btw 12.0.6-->12.1.1
505 fix:ported the DFF ER changes in bug#9305067 to the current file.
506
507 43. 22-Dec-2011 Bug 13514510
508 Issue - Cenvat cannot be claimed on ISO receipts of FGIN / FGEX items.
509 Fix - Modified validate_transaction procedure. Added REQ to the list of source document types which are allowed
510 for cenvat claim when item class is FGIN / FGEX.
511
512 43. 11-Apr-2012 vkavulur Bug#13110424
513 Issue: NON BONDED SUB INVENTORY TRANSACTION IS HITING RG1 REGISTER
514 Fix: Made the changes in the procedure process_transaction to pass Dr in place of CENVAT_DEBIT
515 and Cr in place of CENVAT_CREDIT
516
517 44. 06-Aug-2012 vkavulur Bug#14308585
518 Issue: India Localization excise taxes are charged to material account twice while unclaimed the cenvat
519 Fix: Made the changes in transaction_postprocessor procedure to update the non-bonded subinventory flag to
520 'Y' in case delivery is done to non bonded inventory so that the receipt wont be eligible for taking
521 cenvat credit.
522
523 45. 16-Aug-2012 vkavulur Bug#14503581
524 Issue: IN TRADING ORG EXCISE TAXES SHOULD UPDATE RG23D FOR NON-RECOVERABLE TAXES ALSO
525 Fix: Reverted back the changes done for bug #9019561. RG23 D should be hit for transactions
526 created in trading org irrespective of whether they are recoverable or non recoverable.
527
528 46. 29-Aug-2012 vkavulur Bug#14508154
529 Issue: SECONDARY CHALLAN NOT GENERATED FOR RTV
530 Fix: Secondary challan is not getting created at the time of RTV for OSP Receipt. In the process_rtv
531 procedure made the code fix to call the create_rcv_57f4 procedure even when no excise invoice no is getting
532 generated.
533
534 47. 03-Oct-2012 vkavulur Bug#13362352
535 Issue : RG IS NOT GETTING REVERSED FOR OSP ITEM RECEIPT ON DELIVER
536 Fix : Call to procedure jai_rcv_excise_processing_pkg.process_transaction is made even for a receipt
537 having destination type as 'SHOP FLOOR' to reverse the quantity in RG
538 48. 07-nov-12 vkaranam for bug#14346451
539 Issue: RG registers are getting updated if the item is not excisable
540 fix :online_qty_flag is set depending on the item_excisable_flag
541
542 49. 03-Dec-2012 vkavulur for bug #15907740
543 Issue : EXCISE INVOICE NUMBER AND DATE IS NOT POPULATED RECEIPTS FOR CORRECTION ENTRIES
544 Fix : In the validate_transaction procedure the validations added to check "if the correction entry
545 is done in the same month in which the RG is updated for the parent receipt transactions" is removed
546 so that the RG gets updated with the excise details irrespective of the date on which it is created.
547
548 50. 21-Dec-2012 vkavulur for bug #14852106
549 Issue : CENVAT CORRECTION ACCOUNTING NOT HAPPENING
550 Fix : In the validate_transaction procedure there is a validations added to mark the cenvat rg status flag
551 as 'X' if the transaction type is of deliver. Added few more conditions in the validation to allow cenvat
552 processing if the current transaction type is 'CORRECT' and if excise taxes exist for the receipt.
553
554 51. 31-Jan-2013 vkavulur for bug #16197556
555 Issue : ACCOUNTING IS HAPPENINING EVEN WITHOUT APPLYING BOE
556 Fix : Added a validation in validate_transaction procedure which checks if the Boe amount applied is equal to the
557 total customs tax amount. If it is not applied then the process completes with warning that BOE Entry is not completely
558 applied for the Receipt
559 52. 19-feb-2013 vkaranam for bug#16306678
560 Issue: RG23 part I register is not reversing the quanity on delivery of the material to non bonded subinventory.
561 fix: register entry type for deliver event is set to 'DR' .
562
563 Dependancy:
564 -----------
565
566 ----------------------------------------------------------------------------------------------------------------------------*/
567
568 /* following procedure added by Vijay Shankar for Bug#3940588 */
569 PROCEDURE transaction_preprocessor(
570 p_shipment_line_id IN NUMBER,
571 p_transaction_id IN NUMBER,
572 p_process_status IN OUT NOCOPY VARCHAR2,
573 p_process_message IN OUT NOCOPY VARCHAR2,
574 p_simulate_flag IN VARCHAR2 --File.Sql.35 Cbabu DEFAULT 'N'
575 ) IS
576
577 --added by ssawant
578 CURSOR cur_qty_setup( cp_organization_id NUMBER,cp_location_id NUMBER)
579 IS
580 SELECT quantity_register_update_event
581 FROM JAI_CMN_INVENTORY_ORGS
582 WHERE organization_id = cp_organization_id
583 AND location_id = cp_location_id ;
584
585
586 --added by ssawant
587 CURSOR c_base_trx(cp_transaction_id IN NUMBER) IS
588 SELECT shipment_header_id, shipment_line_id, transaction_type, quantity, unit_of_measure, uom_code,
589 parent_transaction_id, organization_id, location_id, subinventory, currency_conversion_rate,
590 attribute_category attr_cat, nvl(attribute5, 'XX') rma_type, nvl(attribute4, 'N') generate_excise_invoice
591 , routing_header_id -- porting of Bug#3949109 (3927371)
592 , attribute3 online_claim_flag, source_document_code, po_header_id -- Vijay Shankar for Bug#3940588
593 , po_line_location_id
594 FROM rcv_transactions
595 WHERE transaction_id = cp_transaction_id;
596
597 r_trx c_trx%ROWTYPE;
598 r_base_trx c_base_trx%ROWTYPE;
599 r_tax jai_rcv_excise_processing_pkg.tax_breakup;
600
601 ln_cenvat_amount NUMBER;
602 ln_other_cenvat_amt NUMBER;
603 lv_breakup_type VARCHAR2(10);
604
605 -- Bug 5581319. Added by Lakshmi Gopalsami
606 -- Increased the size of lv_localpath from 100 to 2000.
607 --
608 lv_localpath jai_rcv_transactions.codepath%TYPE; --VARCHAR2(2000); --File.Sql.35 Cbabu := '';
609 ln_dup_chk NUMBER; --File.Sql.35 Cbabu := 0;
610
611 lv_process_status VARCHAR2(2);
612 lv_process_message VARCHAR2(1000);
613
614
615
616
617
618 -- Vijay Shankar for Bug#4346453. RCV DFF Elim. Enh.
619 r_excise_invoice_no c_excise_invoice_no%ROWTYPE;
620
621 /* Added by Ramananda for bug#4407165 */
622 lv_object_name CONSTANT VARCHAR2(61) := 'jai_rcv_trx_processing_pkg.transaction_preprocessor';
623
624 -- added, Ramananda for bug # 4519719
625 CURSOR c_vat_exists(cp_shipment_line_id NUMBER )
626 IS
627 SELECT 1
628 FROM JAI_RCV_LINE_TAXES a , jai_regime_tax_types_v b
629 WHERE shipment_line_id = cp_shipment_line_id AND
630 b.regime_code= jai_constants.vat_regime
631 and b.tax_type = a.tax_type ;
632
633 ln_vat_exists NUMBER ;
634 -- ended, Ramananda for bug # 4519719
635
636 BEGIN
637
638 lv_localpath := '';
639 ln_dup_chk := 0;
640
641 OPEN c_trx(p_transaction_id);
642 FETCH c_trx INTO r_trx;
643 CLOSE c_trx;
644
645 IF r_trx.transaction_type IN ('RECEIVE', 'MATCH') THEN
646
647 select count(1) into ln_dup_chk
648 from JAI_RCV_CENVAT_CLAIMS
649 where transaction_id = r_trx.transaction_id;
650
651 IF ln_dup_chk > 0 THEN
652 return;
653 END IF;
654
655 IF r_trx.organization_type = 'T' THEN
656 lv_breakup_type := 'RG23D';
657 END IF;
658
659 jai_rcv_excise_processing_pkg.get_tax_amount_breakup(
660 p_shipment_line_id => r_trx.shipment_line_id,
661 p_transaction_id => r_trx.transaction_id,
662 p_curr_conv_rate => r_trx.currency_conversion_rate,
663 pr_tax => r_tax,
664 p_breakup_type => lv_breakup_type,
665 p_codepath => lv_localpath
666 );
667
668 -- Uncommented for bug 6109941
669 ln_cenvat_amount := r_tax.basic_excise +
670 r_tax.addl_excise +
671 r_tax.other_excise +
672 r_tax.cvd +
673 r_tax.addl_cvd ; -- Modified by SACSETHI Bug# 5228046
674 -- Forward porting the change in 11i bug 5365523
675 -- (Additional CVD Enhancement) as part of the R12 bug 5228046
676
677 ln_other_cenvat_amt := r_tax.excise_edu_cess +
678 r_tax.cvd_edu_cess+
679 r_tax.sh_exc_edu_cess +
680 r_tax.sh_cvd_edu_cess; --Added by kunkumar for forward porting bug#5907436 to R12
681
682
683 OPEN c_base_trx(p_transaction_id);
684 FETCH c_base_trx INTO r_base_trx;
685 CLOSE c_base_trx;
686
687 OPEN c_excise_invoice_no(r_base_trx.shipment_line_id);
688 FETCH c_excise_invoice_no INTO r_excise_invoice_no;
689 CLOSE c_excise_invoice_no;
690
691 -- even if there is no excise, then the data goes into this table. previous code doesnt insert data into this table
692 -- if excise taxes are not present for a line
693 INSERT INTO JAI_RCV_CENVAT_CLAIMS(
694 transaction_id, shipment_line_id, cenvat_amount, cenvat_claimed_ptg, cenvat_sequence,
695 other_cenvat_amt, other_cenvat_claimed_amt, creation_date, created_by, last_update_date,
696 last_updated_by, last_update_login,
697 online_claim_flag,
698 vendor_changed_flag
699 ) VALUES (
700 r_trx.transaction_id, r_trx.shipment_line_id, ln_cenvat_amount, 0, 0,
701 ln_other_cenvat_amt, 0, sysdate, fnd_global.user_id, sysdate,
702 fnd_global.user_id, fnd_global.login_id,
703 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),
704 jai_constants.no
705 );
706
707 -- Vijay Shankar for Bug#3940588 EDUCATION CESS
708 IF r_tax.excise_edu_cess <> 0 THEN
709 jai_rcv_excise_processing_pkg.other_cenvat_rg_recording(
710 p_source_register => jai_constants.reg_receipt_cenvat,
711 p_source_register_id => r_trx.transaction_id,
712 p_tax_type => jai_constants.tax_type_exc_edu_cess,
713 p_credit => r_tax.excise_edu_cess,
714 p_debit => null,
715 p_process_status => p_process_status,
716 p_process_message => p_process_message
717 );
718 END IF;
719
720 IF p_process_status = 'E' THEN
721 RETURN;
722 END IF;
723 /*Added by kunkumar for forward porting bug#5989740, start*/
724 IF r_tax.sh_exc_edu_cess <> 0 THEN
725 jai_rcv_excise_processing_pkg.other_cenvat_rg_recording(
726 p_source_register => jai_constants.reg_receipt_cenvat,
727 p_source_register_id => r_trx.transaction_id,
728 p_tax_type => jai_constants.tax_type_sh_exc_edu_cess,
729 p_credit => r_tax.sh_exc_edu_cess,
730 p_debit => null,
731 p_process_status => p_process_status,
732 p_process_message => p_process_message
733 );
734 END IF;
735
736 IF p_process_status = 'E' THEN
737 RETURN;
738 END IF;
739
740 IF r_tax.sh_cvd_edu_cess <> 0 THEN
741 jai_rcv_excise_processing_pkg.other_cenvat_rg_recording(
742 p_source_register => jai_constants.reg_receipt_cenvat,
743 p_source_register_id => r_trx.transaction_id,
744 p_tax_type => jai_constants.tax_type_sh_cvd_edu_cess,
745 p_credit => r_tax.sh_cvd_edu_cess,
746 p_debit => null,
747 p_process_status => p_process_status,
748 p_process_message => p_process_message
749 );
750 END IF;
751
752 IF p_process_status = 'E' THEN
753 RETURN;
754 END IF;
755 /*Added by kunkumar for 5989740, end*/
756
757
758
759 IF r_tax.cvd_edu_cess <> 0 THEN
760 jai_rcv_excise_processing_pkg.other_cenvat_rg_recording(
761 p_source_register => jai_constants.reg_receipt_cenvat,
762 p_source_register_id => r_trx.transaction_id,
763 p_tax_type => jai_constants.tax_type_cvd_edu_cess,
764 p_credit => r_tax.cvd_edu_cess,
765 p_debit => null,
766 p_process_status => p_process_status,
767 p_process_message => p_process_message
768 );
769 END IF;
770
771 IF p_process_status = 'E' THEN
772 RETURN;
773 END IF;
774
775 -- added, Ramananda for bug # 4519719
776 OPEN c_vat_exists(p_shipment_line_id) ;
777 FETCH c_vat_exists INTO ln_vat_exists ;
778 CLOSE c_vat_exists ;
779
780 IF ln_vat_exists = 1 THEN
781
782 /* Call added for VAT Implementation. Vijay Shankar for Bug#4250236(4245089) */
783 jai_rcv_rgm_claims_pkg.insert_rcv_lines(
784 p_shipment_header_id => null,
785 p_shipment_line_id => p_shipment_line_id,
786 p_transaction_id => p_transaction_id,
787 p_regime_code => jai_constants.vat_regime
788 , p_process_status => p_process_status,
789 p_process_message => p_process_message,
790 p_simulate_flag => p_simulate_flag
791 );
792
793 IF p_process_status <> jai_constants.successful THEN
794 p_process_status := 'E';
795 RETURN;
796 END IF;
797 END IF ;
798 -- ended, Ramananda for bug # 4519719
799 /*
800 Did not handle UOM Conversion between the Transaction Quantities
801
802 JA_IN_RCV_CENVAT_PKG.insert_row(
803 p_shipment_line_id => p_shipment_line_id ,
804 p_tax_transaction_id => r_trx.tax_transaction_id,
805 p_tax_qty => r_trx.quantity,
806 p_tax_qty_uom_code => r_trx.uom_code,
807 p_receipt_num => r_trx.receipt_num,
808 p_receipt_date => r_trx.transaction_date,
809 p_excise_invoice_no => r_trx.excise_invoice_no,
810 p_excise_invoice_date => r_trx.excise_invoice_date,
811 p_basic_excise => ln_basic,
812 p_addl_excise => ln_addl,
813 p_cvd => ln_cvd,
814 p_other_excise => ln_other,
815 p_cenvat_claimed_ptg => 0,
816 p_cenvat_claimed_amt => 0,
817 p_claimable_flag => null,
818 p_receive_qty => 0,
819 p_receive_corr_qty => null,
820 p_deliver_bonded_qty => null,
821 p_deliver_nonbonded_qty => null,
822 p_deliver_corr_bonded_qty => null,
823 p_deliver_corr_nonbonded_qty => null,
824 p_rtr_bonded_qty => null,
825 p_rtr_nonbonded_qty => null,
826 p_rtv_qty => null,
827 p_rtv_corr_qty => null,
828 p_excise_vendor_id => null,
829 p_excise_vendor_site_id => null,
830 p_called_from => 'jai_rcv_trx_processing_pkg.transaction_preprocessor',
831 p_simulate_flag => p_simulate_flag,
832 p_process_status => p_process_status,
833 p_process_message => p_process_status
834 );
835 */
836
837 END IF;
838
839 /* Added by Ramananda for bug#4407165 */
840 EXCEPTION
841 WHEN OTHERS THEN
842 FND_MESSAGE.SET_NAME('JA','JAI_EXCEPTION_OCCURED');
843 FND_MESSAGE.SET_TOKEN('JAI_PROCESS_MSG', lv_object_name ||'. Err:'||sqlerrm );
844 app_exception.raise_exception;
845
846 END transaction_preprocessor;
847
848 /* following procedure added by Vijay Shankar for Bug#3940588 */
849 PROCEDURE transaction_postprocessor(
850 p_shipment_line_id IN NUMBER,
851 p_transaction_id IN NUMBER,
852 p_process_status IN OUT NOCOPY VARCHAR2,
853 p_process_message IN OUT NOCOPY VARCHAR2,
854 p_simulate_flag IN VARCHAR2, --File.Sql.35 Cbabu DEFAULT 'N',
855 p_codepath IN OUT NOCOPY VARCHAR2
856 ) IS
857
858 r_trx c_trx%ROWTYPE;
859 r_base_trx c_base_trx%ROWTYPE; --added by rchandan for Bug#6030615
860 ln_cenvat_amount NUMBER;
861 ln_cenvat_claimed_ptg NUMBER;
862
863 /* Added by Ramananda for bug#4407165 */
864 lv_object_name CONSTANT VARCHAR2(61) := 'jai_rcv_trx_processing_pkg.transaction_postprocessor';
865
866 BEGIN
867
868
869 FND_FILE.put_line(FND_FILE.log, '^Trx Post Processor');
870
871 p_codepath := jai_general_pkg.plot_codepath(1, p_codepath, 'receipt_transactions_pkg.trx_post_proc', 'START');
872
873 OPEN c_trx(p_transaction_id);
874 FETCH c_trx INTO r_trx;
875 CLOSE c_trx;
876
877 FND_FILE.put_line(FND_FILE.log, '1. Inside ^Trx Post Processor');
878
879 IF r_trx.transaction_type IN ('RECEIVE','MATCH') THEN
880
881 p_codepath := jai_general_pkg.plot_codepath(2, p_codepath);
882 -- Cenvat Claim is not required for the transaction if the following if condition is satisfied
883 IF r_trx.cenvat_rg_status NOT IN ('Y', 'P', 'E') THEN
884
885 p_codepath := jai_general_pkg.plot_codepath(3, p_codepath);
886 IF r_trx.item_class IN ('FGIN', 'FGEX') THEN
887 p_codepath := jai_general_pkg.plot_codepath(4, p_codepath);
888 ln_cenvat_amount := 0;
889 ln_cenvat_claimed_ptg := 0;
890 END IF;
891
892 FND_FILE.put_line(FND_FILE.log, '2. updating JAI_RCV_CENVAT_CLAIMS cenvat amounts');
893
894 UPDATE JAI_RCV_CENVAT_CLAIMS
895 SET cenvat_amount = nvl(ln_cenvat_amount, cenvat_amount),
896 other_cenvat_amt = nvl(ln_cenvat_amount, other_cenvat_amt),
897 cenvat_claimed_ptg = nvl(ln_cenvat_claimed_ptg, 100),
898 cenvat_sequence = nvl(cenvat_sequence ,0) + 1, -- Vijay Shankar for Bug#4346453. RCV DFF Elim. Enh.
899 last_update_date = sysdate,
900 last_update_login = fnd_global.login_id,
901 last_updated_by = fnd_global.user_id
902 WHERE transaction_id = r_trx.tax_transaction_id;
903 END IF;
904
905 /* Vijay Shankar for Bug#4346453. RCV DFF Elim. Enh.. Following code is a replacement for removal of ja_in_create_rcv_57f4_trg trigger
906 as part of RTV DFF Elimination
907 elsif r_trx.transaction_type = 'RETURN TO VENDOR' then
908
909 if r_trx.attribute1 = INV_GEN_STATUS_INV_GENERATED then
910 jai_po_osp_pkg.create_rcv_57f4(
911 p_transaction_id => p_transaction_id,
912 p_process_status => p_process_status,
913 p_process_message => p_process_message
914 );
915
916 if p_process_status in (jai_constants.unexpected_error) then
917 p_process_status := 'E';
918 return;
919 end if;
920 end if;
921 */
922 end if;
923
924 FND_FILE.put_line(FND_FILE.log, '3. before updating JAI_RCV_CENVAT_CLAIMS');
925
926 --Uncommented and added for bug #14308585
927 IF r_trx.transaction_type = 'DELIVER' THEN
928
929 FND_FILE.put_line(FND_FILE.log, '4. updating JAI_RCV_CENVAT_CLAIMS, r_trx.loc_subinv_type : '||r_trx.loc_subinv_type);
930 -- meaning non bonded
931 IF r_trx.loc_subinv_type = 'N' THEN
932
933 FND_FILE.put_line(FND_FILE.log, '5. updating JAI_RCV_CENVAT_CLAIMS for shipment line : '||r_trx.shipment_line_id);
934
935 UPDATE JAI_RCV_CENVAT_CLAIMS
936 SET non_bonded_delivery_flag = 'Y',
937 cenvat_claimed_ptg = 100,
938 last_update_date = sysdate
939 WHERE shipment_line_id = r_trx.shipment_line_id ;
940 END IF;
941
942 END IF;
943 --End--Added for bug #14308585
944
945 /*
946 --*** Did not handle UOM Conversion between the Transaction Quantities
947 JA_IN_RCV_CENVAT_PKG.update_quantities(
948 p_shipment_line_id => p_shipment_line_id,
949 p_tax_transaction_id => r_trx.tax_transaction_id,
950 p_transaction_type => r_trx.transaction_type,
951 p_parent_transaction_type => r_trx.parent_transaction_type,
952 p_subinventory_type => r_trx.loc_subinventory_type,
953 p_transaction_quantity => r_trx.quantity,
954 p_transaction_uom_code => r_trx.uom_code,
955 p_called_from => 'jai_rcv_trx_processing_pkg.transaction_preprocessor',
956 p_simulate_flag => p_simulate_flag,
957 p_process_status => p_process_status,
958 p_process_message => p_process_message
959 );
960 */
961 p_codepath := jai_general_pkg.plot_codepath(9, p_codepath, 'receipt_transactions_pkg.trx_post_proc', 'END');
962
963 /* Added by Ramananda for bug#4407165 */
964 EXCEPTION
965 WHEN OTHERS THEN
966 FND_MESSAGE.SET_NAME('JA','JAI_EXCEPTION_OCCURED');
967 FND_MESSAGE.SET_TOKEN('JAI_PROCESS_MSG', lv_object_name ||'. Err:'||sqlerrm );
968 app_exception.raise_exception;
969
970 END transaction_postprocessor;
971
972 /*============= Check for outside processing transaction ================== */
973 /* Bug 5365346. Added by Lakshmi Gopalsami */
974 FUNCTION Check_57F4_transaction( p_transaction_id in number )
975 RETURN varchar2 is
976 CURSOR c_check_57F4_for_po IS
977 SELECT jpol.line_id
978 FROM jai_po_osp_lines jpol,
979 jai_po_osp_hdrs jpoh
980 WHERE jpol.form_id = jpoh.form_id
981 AND (jpoh.po_header_id, jpol.po_line_id ) IN
982 ( SELECT po_header_id, po_line_id
983 FROM rcv_transactions
984 WHERE transaction_id = p_transaction_id
985 );
986 ln_line_id NUMBER ;
987 ln_ret_value VARCHAR2(3);
988
989
990 BEGIN
991 ln_line_id := 0;
992 ln_ret_value := 'NO';
993 OPEN c_check_57F4_for_po;
994 FETCH c_check_57F4_for_po INTO ln_line_id;
995 IF c_check_57F4_for_po%NOTFOUND THEN
996 ln_ret_value := 'NO';
997 fnd_file.put_line(FND_FILE.LOG, 'Check_57F4_transaction->57F4 challan doesnot exist ' );
998 ELSE
999 ln_ret_value := 'YES';
1000 fnd_file.put_line(FND_FILE.LOG, 'Check_57F4_transaction->57F4 challan exists ' );
1001 END IF ;
1002 CLOSE c_check_57F4_for_po;
1003 RETURN ln_ret_value;
1004 END check_57f4_transaction;
1005
1006 /*============================== DEFERRED CLAIM Main Procedure ==============================*/
1007 PROCEDURE process_deferred_cenvat_claim(
1008 p_batch_id IN NUMBER,
1009 p_called_from IN VARCHAR2,
1010 p_simulate_flag IN VARCHAR2, --File.Sql.35 Cbabu DEFAULT 'N',
1011 p_process_flag OUT NOCOPY VARCHAR2,
1012 p_process_message OUT NOCOPY VARCHAR2
1013 ) IS
1014
1015 r_trx c_trx%ROWTYPE;
1016 lv_process_flag JAI_RCV_TRANSACTIONS.PROCESS_STATUS%TYPE;
1017 lv_process_message JAI_RCV_TRANSACTIONS.process_message%TYPE;
1018 lv_cenvat_rg_flag JAI_RCV_TRANSACTIONS.CENVAT_RG_STATUS%TYPE;
1019 lv_cenvat_rg_message JAI_RCV_TRANSACTIONS.cenvat_rg_message%TYPE;
1020 lv_common_err_mesg VARCHAR2(1000);
1021
1022 lv_codepath VARCHAR2(1996);
1023 ln_processed_cnt NUMBER(10); --File.Sql.35 Cbabu := 0;
1024 ln_errored_cnt NUMBER(10); --File.Sql.35 Cbabu := 0;
1025
1026 r_tax jai_rcv_excise_processing_pkg.tax_breakup;
1027 lv_breakup_type VARCHAR2(15);
1028
1029 lv_ttype_correct JAI_RCV_TRANSACTIONS.transaction_type%type ;
1030 lv_ttype_deliver JAI_RCV_TRANSACTIONS.transaction_type%type ;
1031 lv_type_rtr JAI_RCV_TRANSACTIONS.transaction_type%type ;
1032
1033 ln_dlry_trx_id number;--12817175
1034 ln_inclusive_amt number; -- 12817175
1035 r_dlry_trx c_trx%ROWTYPE;--12817175
1036 lv_include_cenvat_in_costing VARCHAR2(1);--12817175
1037
1038
1039 CURSOR c_trxs_for_unclaim(cp_shipment_line_id IN NUMBER, cp_tax_transaction_id IN NUMBER) IS
1040 select transaction_id, shipment_line_id, organization_type
1041 from JAI_RCV_TRANSACTIONS
1042 where (transaction_type IN (lv_ttype_deliver, lv_type_rtr) --'DELIVER', 'RETURN TO RECEIVING')
1043 or (transaction_type= lv_ttype_correct and parent_transaction_type = lv_ttype_deliver) --'CORRECT' , 'DELIVER'
1044 )
1045 and tax_transaction_id = cp_tax_transaction_id
1046 and shipment_line_id = cp_shipment_line_id
1047 -- and cenvat_rg_flag <> ('P','X') -- pending for parent receive claim
1048 -- and cenvat_rg_flag IN ('N', 'X', 'P', 'XT')
1049 /* and attribute1 = jai_rcv_deliver_rtr_pkg.cenvat_costed_flag commented for bug 8346068 by vumaasha */
1050 --and (attribute2 IS NULL or attribute2 <> jai_constants.yes) /*commented for bug 9032251*/
1051 FOR UPDATE OF cenvat_rg_status, cenvat_rg_message
1052 order by shipment_line_id, transaction_id;
1053
1054 CURSOR c_trxs_to_be_claimed IS
1055 SELECT * FROM JAI_RCV_CENVAT_CLAIM_T
1056 WHERE batch_identifier = p_batch_id
1057 AND error_flag IS NULL
1058 FOR UPDATE OF transaction_id
1059 ORDER BY transaction_id;
1060
1061 CURSOR c_receipt_cenvat_dtl(cp_transaction_id IN NUMBER) IS
1062 SELECT cenvat_claimed_ptg, quantity_for_2nd_claim
1063 FROM JAI_RCV_CENVAT_CLAIMS
1064 WHERE transaction_id = cp_transaction_id;
1065
1066 r_receipt_cenvat_dtl c_receipt_cenvat_dtl%ROWTYPE;
1067 lv_2nd_claim_flag VARCHAR2(1);
1068 ln_qty_to_claim JAI_RCV_CENVAT_CLAIMS.quantity_for_2nd_claim%TYPE;
1069 lv_process_special_reason VARCHAR2(50);
1070 ln_process_special_amount NUMBER;
1071
1072
1073
1074
1075
1076 -- lv_codepath JAI_RCV_TRANSACTIONS.codepath%TYPE;
1077 -- lv_process_flag JAI_RCV_TRANSACTIONS.PROCESS_STATUS%TYPE;
1078 -- lv_process_message JAI_RCV_TRANSACTIONS.process_message%TYPE;
1079
1080 /*Bug 8648138 - Start*/
1081 ln_invoice_id ap_invoice_distributions_all.invoice_id%TYPE;
1082
1083 CURSOR get_invoice (cp_transaction_id NUMBER) IS
1084 SELECT DISTINCT invoice_id
1085 FROM ap_invoice_distributions_all
1086 WHERE rcv_transaction_id = cp_transaction_id;
1087 /*Bug 8648138 - End*/
1088
1089 BEGIN
1090
1091 ln_processed_cnt := 0;
1092 ln_errored_cnt := 0;
1093
1094 -- Unclaim means, Receipt Line is not eligible for Claim. So we need to set all related flags as non recoverable and
1095 -- and reverse the cenvat entries if any passed against the transactions
1096
1097 FOR temp_rec IN c_trxs_to_be_claimed LOOP
1098
1099 ln_processed_cnt := ln_processed_cnt + 1;
1100 lv_common_err_mesg := null;
1101 lv_cenvat_rg_flag := null;
1102 ln_qty_to_claim := null;
1103 lv_2nd_claim_flag := null;
1104 r_trx := null;
1105 r_tax := null;
1106 lv_codepath := '';
1107 lv_process_special_reason := null;
1108
1109 OPEN c_trx(temp_rec.transaction_id);
1110 FETCH c_trx INTO r_trx;
1111 CLOSE c_trx;
1112
1113 lv_process_flag := r_trx.process_status;
1114 lv_process_message := r_trx.process_message;
1115 lv_cenvat_rg_flag := r_trx.cenvat_rg_status;
1116 lv_cenvat_rg_message := r_trx.cenvat_rg_message;
1117
1118 lv_codepath := jai_general_pkg.plot_codepath(1, lv_codepath, 'receipt_transactions_pkg.process_deferred_cenvat_claim', 'START');
1119
1120 SAVEPOINT start_deferred_claim;
1121
1122 IF lb_debug THEN
1123 FND_FILE.put_line(FND_FILE.log, 'trx_id:'||temp_rec.transaction_id||', flag:'||temp_rec.process_flag);
1124 END IF;
1125
1126 IF temp_rec.process_flag = 'M' THEN
1127
1128 OPEN c_receipt_cenvat_dtl(r_trx.tax_transaction_id);
1129 FETCH c_receipt_cenvat_dtl INTO r_receipt_cenvat_dtl;
1130 CLOSE c_receipt_cenvat_dtl;
1131
1132 lv_codepath := jai_general_pkg.plot_codepath(2, lv_codepath);
1133
1134 IF r_trx.item_class IN ('CGIN','CGEX') THEN
1135
1136 lv_codepath := jai_general_pkg.plot_codepath(3, lv_codepath);
1137 IF r_trx.transaction_type IN ('RECEIVE','MATCH') THEN
1138
1139 IF r_receipt_cenvat_dtl.cenvat_claimed_ptg = 0 THEN
1140 lv_codepath := jai_general_pkg.plot_codepath(4, lv_codepath);
1141 lv_2nd_claim_flag := 'N';
1142
1143 ELSIF r_receipt_cenvat_dtl.cenvat_claimed_ptg = 100 THEN
1144 lv_codepath := jai_general_pkg.plot_codepath(5, lv_codepath);
1145 -- Already 100% is Claimed, some problem in code and thats why execution came till here. so process next record
1146 GOTO next_record;
1147
1148 -- second claim case
1149 ELSIF r_receipt_cenvat_dtl.cenvat_claimed_ptg < 100 THEN
1150
1151 lv_codepath := jai_general_pkg.plot_codepath(6, lv_codepath);
1152 -- 2nd Claim should be done only to the tune of JAI_RCV_CENVAT_CLAIMS.
1153 lv_2nd_claim_flag := 'Y';
1154
1155 ELSE
1156 lv_codepath := jai_general_pkg.plot_codepath(7, lv_codepath);
1157 GOTO next_record;
1158 END IF;
1159
1160 ELSE
1161
1162 lv_codepath := jai_general_pkg.plot_codepath(8, lv_codepath);
1163 IF r_trx.cenvat_claimed_ptg = 0 THEN
1164 lv_2nd_claim_flag := 'N';
1165 ELSE
1166 lv_codepath := jai_general_pkg.plot_codepath(8.1, lv_codepath);
1167 -- transactions other than RECEIVE should not be processed for 2nd Claim
1168 GOTO next_record;
1169 END IF;
1170
1171 END IF;
1172
1173 ELSE
1174 lv_codepath := jai_general_pkg.plot_codepath(9, lv_codepath);
1175 lv_2nd_claim_flag := 'N';
1176 END IF;
1177
1178 IF lv_2nd_claim_flag = 'Y' THEN
1179 ln_qty_to_claim := r_receipt_cenvat_dtl.quantity_for_2nd_claim;
1180 lv_process_special_reason := jai_rcv_excise_processing_pkg.second_50ptg_claim;
1181 END IF;
1182
1183 process_transaction(
1184 p_transaction_id => temp_rec.transaction_id,
1185 p_process_flag => lv_process_flag,
1186 p_process_message => lv_process_message,
1187 p_cenvat_rg_flag => lv_cenvat_rg_flag,
1188 p_cenvat_rg_message => lv_cenvat_rg_message,
1189 p_common_err_mesg => lv_common_err_mesg,
1190 p_called_from => p_called_from,
1191 p_simulate_flag => p_simulate_flag,
1192 p_codepath => lv_codepath,
1193 p_process_special_reason => lv_process_special_reason,
1194 p_process_special_qty => ln_qty_to_claim,
1195 p_excise_processing_reqd => jai_constants.yes, --File.Sql.35 Cbabu
1196 p_vat_processing_reqd => jai_constants.yes --File.Sql.35 Cbabu
1197 );
1198
1199 lv_codepath := jai_general_pkg.plot_codepath(10, lv_codepath);
1200
1201 -- flag value 'X' is removed from the following ELSIF conditions because it is not an ERROR Status. MYXZ
1202 IF lv_common_err_mesg IS NOT NULL THEN
1203 -- A common error occured. So, Whole Processing for Transaction should be stopped
1204 ROLLBACK TO start_deferred_claim;
1205 FND_FILE.put_line(FND_FILE.log, '*** Common Error for Transaction_id:'||r_trx.transaction_id
1206 ||fnd_global.local_chr(10)||fnd_global.local_chr(9)||'- Error:'||lv_common_err_mesg
1207 );
1208 lv_codepath := jai_general_pkg.plot_codepath(11, lv_codepath);
1209
1210 ELSIF lv_process_flag IN ('E') AND lv_cenvat_rg_flag IN ('E') THEN
1211 lv_codepath := jai_general_pkg.plot_codepath(12, lv_codepath);
1212 FND_FILE.put_line(FND_FILE.log, '*** FLAGS ERROR *** Transaction_id:'||r_trx.transaction_id
1213 ||fnd_global.local_chr(10)||fnd_global.local_chr(9)||'- ProcessErr:'||lv_process_message
1214 ||fnd_global.local_chr(10)||fnd_global.local_chr(9)||'- CenvatErr:'||lv_cenvat_rg_message
1215 );
1216 /*dbms_output.put_line( '*** FLAGS ERROR *** Transaction_id:'||r_trx.transaction_id
1217 ||fnd_global.local_chr(10)||fnd_global.local_chr(9)||'- ProcessErr:'||lv_process_message
1218 ||fnd_global.local_chr(10)||fnd_global.local_chr(9)||'- CenvatErr:'||lv_cenvat_rg_message );
1219 */
1220 ELSIF lv_process_flag IN ('E') THEN
1221 lv_codepath := jai_general_pkg.plot_codepath(13, lv_codepath);
1222 FND_FILE.put_line(FND_FILE.log, '*** PROCESS ERROR *** Transaction_id:'||r_trx.transaction_id
1223 ||fnd_global.local_chr(10)||fnd_global.local_chr(9)||'- ErrorMessage:'||lv_process_message
1224 );
1225 /*dbms_output.put_line('*** PROCESS ERROR *** Transaction_id:'||r_trx.transaction_id
1226 ||fnd_global.local_chr(10)||fnd_global.local_chr(9)||'- ErrorMessage:'||lv_process_message );
1227 */
1228 ELSIF lv_cenvat_rg_flag IN ('E') THEN
1229 lv_codepath := jai_general_pkg.plot_codepath(14, lv_codepath);
1230 FND_FILE.put_line(FND_FILE.log, '*** CENVAT ERROR *** Transaction_id:'||r_trx.transaction_id
1231 ||fnd_global.local_chr(10)||fnd_global.local_chr(9)||'- ErrorMessage:'||lv_cenvat_rg_message
1232 );
1233 /*dbms_output.put_line('*** CENVAT ERROR *** Transaction_id:'||r_trx.transaction_id
1234 ||fnd_global.local_chr(10)||fnd_global.local_chr(9)||'- ErrorMessage:'||lv_cenvat_rg_message
1235 );*/
1236 END IF;
1237
1238 IF lv_process_flag IN ('X') AND lv_cenvat_rg_flag IN ('X') THEN
1239 lv_codepath := jai_general_pkg.plot_codepath(15, lv_codepath);
1240 FND_FILE.put_line(FND_FILE.log, ' Transaction Cant be processed for trx:'||r_trx.transaction_id
1241 ||fnd_global.local_chr(10)||fnd_global.local_chr(9)||'- ProcessMessage(X):'||lv_process_message
1242 ||fnd_global.local_chr(10)||fnd_global.local_chr(9)||'- CenvatMessgae(X):'||lv_cenvat_rg_message
1243 );
1244 /*dbms_output.put_line('M: Err2: Transaction Cant be processed for trx:'||r_trx.transaction_id
1245 ||fnd_global.local_chr(10)||fnd_global.local_chr(9)||'- ProcessMessage(X):'||lv_process_message
1246 ||fnd_global.local_chr(10)||fnd_global.local_chr(9)||'- CenvatMessgae(X):'||lv_cenvat_rg_message);
1247 */
1248 ELSIF lv_process_flag IN ('X') THEN
1249 lv_codepath := jai_general_pkg.plot_codepath(16, lv_codepath);
1250 FND_FILE.put_line(FND_FILE.log, 'Process Message(X):'||lv_process_message);
1251 /*dbms_output.put_line('M: Err4:'||lv_process_message);*/
1252 ELSIF lv_cenvat_rg_flag IN ('X') THEN
1253 lv_codepath := jai_general_pkg.plot_codepath(17, lv_codepath);
1254 FND_FILE.put_line(FND_FILE.log, 'Cenvat Messgae(X):'||lv_cenvat_rg_message);
1255 /*dbms_output.put_line('M: Err3:'||lv_cenvat_rg_message);*/
1256 END IF;
1257
1258 IF lv_process_flag = 'E' OR lv_cenvat_rg_flag = 'E' THEN
1259 lv_codepath := jai_general_pkg.plot_codepath(18, lv_codepath);
1260 p_process_flag := lv_cenvat_rg_flag;
1261 p_process_message := lv_cenvat_rg_message;
1262 fnd_file.put_line(fnd_file.log, 'M: Err5:'||p_process_message);
1263 END IF;
1264
1265 -- UNCLAIM PROCESSING
1266 -- following Unclaim Processing will not happen for
1267 ELSIf temp_rec.process_flag = 'U' AND r_trx.transaction_type IN ('RECEIVE', 'MATCH') THEN
1268
1269 lv_codepath := jai_general_pkg.plot_codepath(19, lv_codepath);
1270 SAVEPOINT start_unclaim;
1271
1272 -- Costing(Average and Standard)/Expense Logic for Excise Amount
1273
1274 lv_ttype_correct := 'CORRECT';
1275 lv_ttype_deliver := 'DELIVER';
1276 lv_type_rtr := 'RETURN TO RECEIVING';
1277
1278 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*/
1279
1280 IF r_trx.organization_type = 'T' THEN
1281 lv_breakup_type := 'RG23D';
1282 END IF;
1283
1284 lv_codepath := jai_general_pkg.plot_codepath(21, lv_codepath);
1285 jai_rcv_excise_processing_pkg.get_tax_amount_breakup(
1286 p_shipment_line_id => loop_trx.shipment_line_id,
1287 p_transaction_id => loop_trx.transaction_id,
1288 p_curr_conv_rate => r_trx.currency_conversion_rate,
1289 pr_tax => r_tax, -- OUT Variable with Breakup
1290 p_breakup_type => lv_breakup_type,
1291 p_codepath => lv_codepath
1292 );
1293
1294 --start additions by vkaranam for bug#12817175
1295
1296 FND_FILE.put_line(FND_FILE.log, 'Unclaim before jai_rcv_trx_processing_pkg.get_ancestor_id loop_trx.transaction_id '||loop_trx.transaction_id
1297 ||' r_trx.shipment_line_id '||r_trx.shipment_line_id);
1298 ln_dlry_trx_id := jai_rcv_trx_processing_pkg.get_ancestor_id
1299 (
1300 loop_trx.transaction_id,
1301 r_trx.shipment_line_id,
1302 'DELIVER'
1303 );
1304
1305 FND_FILE.put_line(FND_FILE.log, 'Unclaim after jai_rcv_trx_processing_pkg.get_ancestor_id ln_dlry_trx_id '||ln_dlry_trx_id);
1306 open c_trx(ln_dlry_trx_id);
1307 fetch c_trx into r_dlry_trx;
1308 close c_trx;
1309 FND_FILE.put_line(FND_FILE.log, 'Unclaim r_dlry_trx.attribute1 '||r_dlry_trx.attribute1);
1310 /*if the receipt is delivered then the attribute_1 will be 'CENVAT_COSTED_FLAG',
1311 --unclaim accounting shall happen based on the below conditions
1312 Case 1:
1313 Event 1:receive
1314 Event 2:unclaim
1315 Event 3: Delivery
1316 the accounting entry shall not include the inclusive tax as the same has*/
1317
1318 /*12817175
1319 if r_dlry_trx.attribute1='CENVAT_COSTED_FLAG'
1320 then
1321 if r_dlry_trx.attribute2='Y'
1322 --if the item is delivered to nonbonded subinventory then
1323 -- inclusive amount shall not be accounted during unclaim
1324 then
1325
1326 ln_inclusive_amt:= nvl(r_tax.non_cenvat,0);
1327 else
1328 --if the item is delivered to bonded subinventory then
1329 -- inclusive amount shall be accounted during unclaim
1330 ln_inclusive_amt:= 0;
1331 END IF;
1332
1333 else
1334 ln_inclusive_amt:=0;
1335 end if;
1336 */
1337
1338 lv_include_cenvat_in_costing := jai_rcv_deliver_rtr_pkg.include_cenvat_in_costing
1339 (
1340 p_transaction_id => ln_dlry_trx_id,
1341 p_process_message => lv_process_message,
1342 p_process_status => lv_process_flag,
1343 p_codepath => lv_codepath
1344 );
1345
1346 fnd_file.put_line(fnd_file.log,'unclaim lv_include_cenvat_in_costing '||lv_include_cenvat_in_costing);
1347
1348 if nvl(lv_include_cenvat_in_costing,'N')='Y' and nvl(r_tax.non_cenvat,0)<0 and ln_dlry_trx_id is not null
1349 /*item delivered and is delivered to non bonded subinventory then the inclusive amt is not considered in the accounting*/
1350
1351 then
1352 ln_inclusive_amt:= nvl(r_tax.non_cenvat,0);
1353 else
1354 ln_inclusive_amt:=0;
1355 end if;
1356 fnd_file.put_line(fnd_file.log,'unclaim lv_include_cenvat_in_costing '||lv_include_cenvat_in_costing);
1357 --end additions by vkaranam for bug#12817175
1358
1359
1360
1361 ln_process_special_amount := r_tax.basic_excise +
1362 r_tax.addl_excise +
1363 r_tax.other_excise +
1364 r_tax.cvd +
1365 r_tax.addl_cvd +
1366 -- Modified by SACSETHI Bug# 5228046
1367 -- Forward porting the change in 11i bug 5365523
1368 -- (Additional CVD Enhancement) as part of the R12 bug 5228046
1369 r_tax.excise_edu_cess +
1370 r_tax.cvd_edu_cess+r_tax.sh_exc_edu_cess + --Added by kunkumar for forward porting bug#5907436 to R12
1371 r_tax.sh_cvd_edu_cess+ ln_inclusive_amt ; --added ln_inclusive_amt for bug#12817175
1372 --Added by kunkumar for forward porting bug#5907436 to R12
1373
1374
1375
1376
1377
1378 IF ln_process_special_amount = 0 THEN
1379 GOTO skip_unclaim_record;
1380 END IF;
1381
1382 lv_codepath := jai_general_pkg.plot_codepath(22, lv_codepath);
1383 --lv_codepath := '';
1384 jai_rcv_deliver_rtr_pkg.process_transaction (
1385 p_transaction_id => loop_trx.transaction_id,
1386 p_simulate => p_simulate_flag,
1387 p_codepath => lv_codepath,
1388 p_process_status => lv_process_flag,
1389 p_process_message => lv_process_message,
1390 p_process_special_source => jai_constants.cenvat_noclaim,
1391 p_process_special_amount => ln_process_special_amount
1392 );
1393
1394 IF p_process_flag IN ('E', 'X') THEN
1395 lv_codepath := jai_general_pkg.plot_codepath(23, lv_codepath);
1396 FND_FILE.put_line(FND_FILE.log, 'Unclaim PRC_FLG_Error: RollingBack to process_trxn_flag');
1397 /*dbms_output.put_line('Unclaim PRC_FLG_Error: RollingBack to process_trxn_flag');*/
1398 --p_codepath := jai_general_pkg.plot_codepath(6, p_codepath);
1399 ROLLBACK TO start_unclaim;
1400 -- following is to take care that if one transaction of RECEIVE childs fail, then loop should not
1401 -- be executed as the loop is related to CHILDs of RECEIVE Transaction
1402 EXIT;
1403 ElSIF p_process_flag = 'Y' THEN
1404 --p_codepath := jai_general_pkg.plot_codepath(7, p_codepath);
1405 p_process_message := 'Successful';
1406 ELSE
1407 FND_FILE.put_line(FND_FILE.log, 'Unclaim#PRC_FLG#'||p_process_flag);
1408 --p_codepath := jai_general_pkg.plot_codepath(8, p_codepath);
1409 /*dbms_output.put_line('Unclaim#PRC_FLG#'||p_process_flag);*/
1410 END IF;
1411
1412 UPDATE JAI_RCV_TRANSACTIONS
1413 SET CENVAT_RG_STATUS = 'X',
1414 cenvat_rg_message = 'Cenvat Unclaimed'
1415 WHERE CURRENT OF c_trxs_for_unclaim;
1416
1417 <<skip_unclaim_record>>
1418 NULL;
1419
1420 END LOOP;
1421
1422 lv_codepath := jai_general_pkg.plot_codepath(27, lv_codepath);
1423
1424 UPDATE JAI_RCV_TRANSACTIONS
1425 SET CENVAT_RG_STATUS = 'X',
1426 cenvat_rg_message = 'Cenvat Unclaimed'
1427 WHERE transaction_id = temp_rec.transaction_id
1428 -- following is to take care of Pending DELIVER and RTR and related CORRECTS
1429 -- as the parent receive is not yet claim so need of passing CENVAT RG entries.
1430 -- Simply update the flag as the transactions is already costed during delivery processing
1431 OR ( shipment_line_id = r_trx.shipment_line_id
1432 AND tax_transaction_id = r_trx.tax_transaction_id
1433 AND (transaction_type IN ('DELIVER', 'RETURN TO RECEIVING')
1434 or (transaction_type='CORRECT' and parent_transaction_type = 'DELIVER')
1435 )
1436 AND loc_subinv_type = 'N' -- non bonded
1437 AND CENVAT_RG_STATUS = 'P' -- waiting for receipt line claim
1438 );
1439
1440 /* Shall take the help of support to implement the unclaim functionality */
1441 UPDATE JAI_RCV_CENVAT_CLAIMS
1442 SET cenvat_amount = 0,
1443 other_cenvat_amt = 0,
1444 cenvat_sequence = cenvat_sequence + 1,
1445 unclaim_cenvat_flag ='Y',
1446 unclaim_cenvat_date = trunc(sysdate),
1447 unclaimed_cenvat_amount = temp_rec.unclaimed_cenvat_amount,
1448 last_update_date = sysdate,
1449 last_update_login = fnd_global.login_id,
1450 last_updated_by = fnd_global.user_id
1451 WHERE transaction_id = temp_rec.transaction_id;
1452
1453 /*Bug 8648138 - Unclaimed excise amount should be available for re-transfer to FA from AP, if the transfer
1454 is done before unclaim*/
1455
1456 ln_invoice_id := NULL;
1457 FOR r_invoice IN get_invoice(r_trx.tax_transaction_id)
1458 LOOP
1459 ln_invoice_id := r_invoice.invoice_id;
1460
1461 IF ln_invoice_id IS NOT NULL THEN
1462
1463 UPDATE ap_invoice_distributions_all aida
1464 SET assets_addition_flag = 'U',
1465 assets_tracking_flag = 'Y',
1466 charge_applicable_to_dist_id = (select invoice_distribution_id from ap_invoice_distributions_all
1467 where line_type_lookup_code in ('ITEM', 'ACCRUAL')
1468 and po_distribution_id = aida.po_distribution_id and invoice_id = ln_invoice_id)
1469 /*Added by nprashar for bug# 11659328 ,Removed code added by bug # 10628363, as the fix is functionally not correct , the only change
1470 required was to add an invoice_id condition*/
1471
1472
1473 WHERE invoice_id = ln_invoice_id
1474 AND invoice_distribution_id /*distribution_line_number*/ IN /*Replaced distribution_line_number by invoice_distribution_id for bug # 11659328*/
1475 (SELECT invoice_distribution_id /*distribution_line_number*/
1476 FROM jai_ap_match_inv_taxes jatd,
1477 jai_rcv_line_taxes jrtl
1478 WHERE jatd.invoice_id=ln_invoice_id
1479 AND jrtl.transaction_id=r_trx.tax_transaction_id
1480 AND jrtl.modvat_flag = 'Y'
1481 AND jrtl.shipment_line_id = temp_rec.shipment_line_id
1482 AND jrtl.tax_id = jatd.tax_id
1483 AND Upper(jrtl.tax_type) IN ('EXCISE', 'ADDL. EXCISE', 'OTHER EXCISE', jai_constants.tax_type_cvd,
1484 jai_constants.tax_type_add_cvd,
1485 jai_constants.tax_type_exc_edu_cess, jai_constants.tax_type_cvd_edu_cess,
1486 jai_constants.tax_type_sh_exc_edu_cess, jai_constants.tax_type_sh_cvd_edu_cess))
1487 AND LINE_TYPE_LOOKUP_CODE = 'MISCELLANEOUS' ; /*Added line_type_lookup_code condition for bug # 11659328 */
1488
1489
1490
1491 END IF;
1492 END LOOP;
1493 /*Bug 8648138*/
1494
1495 -- By doing this future transactions will be taken care not to see Excise taxes as Recoverable
1496 UPDATE JAI_RCV_LINE_TAXES
1497 SET modvat_flag ='N',
1498 last_update_date = sysdate,
1499 last_updated_by = fnd_global.user_id,
1500 last_update_login = fnd_global.login_id
1501 WHERE shipment_line_id = temp_rec.shipment_line_id
1502 AND upper(tax_type) IN ('EXCISE',
1503 'ADDL. EXCISE',
1504 'OTHER EXCISE',
1505 'CVD',
1506 jai_constants.tax_type_add_cvd,
1507 -- Modified by SACSETHI Bug# 5228046
1508 -- Forward porting the change in 11i bug 5365523
1509 -- (Additional CVD Enhancement) as part of the R12 bug 5228046
1510 jai_constants.tax_type_exc_edu_cess,
1511 jai_constants.tax_type_cvd_edu_cess, jai_constants.tax_type_sh_exc_edu_cess,
1512 jai_constants.tax_type_sh_cvd_edu_cess) -- By kunkumar for bug 5989740
1513 AND modvat_flag ='Y';
1514
1515 lv_codepath := jai_general_pkg.plot_codepath(27.1, lv_codepath);
1516
1517 END IF;
1518
1519 << next_record >>
1520
1521 If lv_common_err_mesg IS NOT NULL
1522 OR lv_cenvat_rg_flag IN ('E', jai_constants.unexpected_error, jai_constants.expected_error)
1523 THEN
1524
1525 lv_codepath := jai_general_pkg.plot_codepath(28, lv_codepath);
1526 ROLLBACK TO start_deferred_claim;
1527 p_process_flag := lv_cenvat_rg_flag;
1528 p_process_message := lv_cenvat_rg_message;
1529 ln_errored_cnt := ln_errored_cnt + 1;
1530
1531 UPDATE JAI_RCV_CENVAT_CLAIM_T
1532 SET error_flag = 'Y',
1533 error_description = substr(lv_cenvat_rg_message,1,150),
1534 process_date = sysdate
1535 WHERE CURRENT OF c_trxs_to_be_claimed;
1536
1537 ELSE
1538
1539 lv_codepath := jai_general_pkg.plot_codepath(29, lv_codepath);
1540 -- Finally after the processing is completed, we need to delete the record from temp table
1541 DELETE FROM JAI_RCV_CENVAT_CLAIM_T
1542 WHERE CURRENT OF c_trxs_to_be_claimed;
1543
1544 END IF;
1545
1546 lv_codepath := jai_general_pkg.plot_codepath(30, lv_codepath);
1547 fnd_file.put_line(fnd_file.log, 'Trx_id:'||temp_rec.transaction_id||'. Codepath:'||lv_codepath);
1548 /*dbms_output.put_line('Trx_id:'||temp_rec.transaction_id||'. Codepath:'||lv_codepath);*/
1549
1550 END LOOP;
1551
1552 IF ln_errored_cnt > 0 THEN
1553 p_process_flag := jai_constants.unexpected_error;
1554 p_process_message := 'Errored Record Count:'||ln_errored_cnt;
1555 fnd_file.put_line(fnd_file.log, 'DeferredClaimError Cnt>0:'||p_process_message);
1556 END IF;
1557
1558 EXCEPTION
1559 WHEN OTHERS THEN
1560 ROLLBACK TO start_deferred_claim;
1561 p_process_flag := 'E';
1562 fnd_file.put_line(fnd_file.log, 'ErrorCodepath:'||lv_codepath);
1563 p_process_message := 'Processed Count:'||ln_processed_cnt||', Errored Cnt:'||ln_errored_cnt||'. Error Message:'||SQLERRM;
1564 fnd_file.put_line(fnd_file.log, 'DeferredClaimError6:'||p_process_message);
1565 END process_deferred_cenvat_claim;
1566
1567
1568 /*============================== MAIN PROCEDURE ==============================*/
1569 PROCEDURE process_batch(
1570 errbuf OUT NOCOPY VARCHAR2,
1571 retcode OUT NOCOPY VARCHAR2,
1572 p_organization_id IN NUMBER,
1573 pv_transaction_from IN VARCHAR2, /* rallamse bug#4336482 changed to VARCHAR2 from DATE */
1574 pv_transaction_to IN VARCHAR2, /* rallamse bug#4336482 changed to VARCHAR2 from DATE */
1575 p_transaction_type IN VARCHAR2,
1576 p_parent_trx_type IN VARCHAR2,
1577 p_shipment_header_id IN NUMBER, -- New parameter added by Vijay Shankar for Bug#3940588
1578 p_receipt_num IN VARCHAR2,
1579 p_shipment_line_id IN NUMBER, -- New parameter added by Vijay Shankar for Bug#3940588
1580 p_transaction_id IN NUMBER,
1581 p_commit_switch IN VARCHAR2, --File.Sql.35 Cbabu DEFAULT 'Y',
1582 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
1583 p_simulate_flag IN VARCHAR2, --File.Sql.35 Cbabu DEFAULT 'N',
1584 p_trace_switch IN VARCHAR2, --File.Sql.35 Cbabu DEFAULT 'N'
1585 p_request_id IN NUMBER DEFAULT NULL, -- CSahoo for Bug 5344225
1586 p_group_id IN NUMBER DEFAULT NULL -- CSahoo for Bug 5344225
1587 ) IS
1588
1589 /* rallamse bug#4336482 */
1590 p_transaction_from DATE; --File.Sql.35 Cbabu DEFAULT fnd_date.canonical_to_date(pv_transaction_from);
1591 p_transaction_to DATE; --File.Sql.35 Cbabu DEFAULT fnd_date.canonical_to_date(pv_transaction_to);
1592 /* End of Bug# 4336482 */
1593
1594 ------------ Start, Declaration for TRACE Generation -------------
1595 lv_request_id VARCHAR2(50);
1596 ln_sid NUMBER;
1597 ln_serial NUMBER;
1598 lv_spid VARCHAR2(9);
1599 lv_dbname VARCHAR2(25);
1600 ln_audsid NUMBER; --File.Sql.35 Cbabu := userenv('SESSIONID');
1601 lv_tax_modified_check_flag varchar2(1); --File.Sql.35 Cbabu := 'Y' ;
1602
1603 /* added by nprashar for bug 13880102 */
1604
1605 loop_continue EXCEPTION;
1606
1607 TYPE process_msg_tab_type IS TABLE OF JAI_RCV_TRANSACTIONS.process_message%TYPE
1608 INDEX BY PLS_INTEGER;
1609
1610 process_msg_tab process_msg_tab_type;
1611
1612 /*end by nprashar for bug 13880102 */
1613
1614
1615 CURSOR c_get_audsid IS
1616 SELECT a.sid, a.serial#, b.spid
1617 FROM v$session a, v$process b
1618 WHERE audsid = ln_audsid
1619 AND a.paddr = b.addr;
1620
1621 CURSOR c_get_dbname IS
1622 SELECT name FROM v$database;
1623 ------------ End, Declaration for TRACE Generation -------------
1624
1625 --added, CSahoo for Bug 5344225
1626 ln_req_status BOOLEAN ;
1627 lv_phase VARCHAR2(80) ;
1628 lv_status VARCHAR2(80) ;
1629 lv_dev_phase VARCHAR2(80) ;
1630 lv_dev_status VARCHAR2(80) ;
1631 lv_message VARCHAR2(80) ;
1632
1633 Cursor c_interface_exists(cp_group_id IN NUMBER)
1634 IS
1635 select 1
1636 from rcv_transactions_interface
1637 where group_id = cp_group_id
1638 and rownum=1 ;
1639
1640 Cursor c_interface_error(cp_group_id IN NUMBER)
1641 IS
1642 select 1
1643 from rcv_transactions_interface
1644 where
1645 group_id = cp_group_id and
1646 (transaction_status_code = 'ERROR' or processing_status_code = 'ERROR') ;
1647
1648 ln_error NUMBER ;
1649 --ended, CSahoo for Bug 5344225
1650
1651 lv_process_flag JAI_RCV_TRANSACTIONS.PROCESS_STATUS%TYPE;
1652 lv_process_message JAI_RCV_TRANSACTIONS.process_message%TYPE;
1653 lv_cenvat_rg_flag JAI_RCV_TRANSACTIONS.CENVAT_RG_STATUS%TYPE;
1654 lv_cenvat_rg_message JAI_RCV_TRANSACTIONS.cenvat_rg_message%TYPE;
1655 lv_codepath JAI_RCV_TRANSACTIONS.codepath%TYPE;
1656
1657 lv_common_err_mesg VARCHAR2(500);
1658 ln_processed_cnt NUMBER; --File.Sql.35 Cbabu := 0;
1659 ln_batch_id NUMBER(15);
1660
1661 r_trx_after_processing c_trx%rowtype;
1662
1663
1664 --commented the below by Sanjikum for Bug#4929410
1665 /*CURSOR c_trxns_to_populate_dtls IS
1666 SELECT rowid, transaction_id, shipment_line_id, process_status, cenvat_rg_status,
1667 transaction_type, parent_transaction_type, receipt_num, cenvat_claimed_ptg
1668 , attribute_category -- Vijay Shankar for Bug#4250171
1669 FROM JAI_RCV_TRANSACTIONS a
1670 WHERE (p_organization_id IS NULL OR organization_id = p_organization_id)
1671 AND a.receipt_num IS NULL
1672 AND (p_shipment_header_id IS NULL OR a.shipment_header_id = p_shipment_header_id)
1673 AND
1674 ( ( p_called_from = 'JAINPORE' and p_shipment_header_id is null) -- added, Harshita for bug #4300708
1675 OR ( exists (select 1 from JAI_RCV_LINES b
1676 where a.shipment_line_id = b.shipment_line_id
1677 and tax_modified_flag='N') )
1678 )
1679 FOR UPDATE OF transaction_id
1680 ORDER BY receipt_num, transaction_id;
1681
1682
1683 CURSOR c_get_transactions IS
1684 SELECT rowid, transaction_id, process_status, cenvat_rg_status, process_message, cenvat_rg_message,
1685 transaction_type, parent_transaction_type, receipt_num, cenvat_claimed_ptg,
1686 shipment_line_id -- Vijay Shankar for Bug#3940588
1687 FROM JAI_RCV_TRANSACTIONS a
1688 WHERE ( p_simulate_flag = 'Y'
1689 OR
1690 ( process_status IS NULL OR process_status IN ('N', 'E','P') OR cenvat_rg_status IN ('N', 'E','P')
1691 -- following condition added by Vijay Shankar for Bug#4250236(4245089). VAT Implementation
1692 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.
1693 process_vat_status IN ('N', 'E', 'P', jai_constants.unexpected_error, jai_constants.expected_error)
1694 -- Vijay Shankar for Bug#3940588 RECEIPTS DEPLUG
1695 -- This is no more required because 2nd 50% Claim is handled seperately through deferred Claim procedure
1696 -- However process_transaction still uses cenvat_claimed_ptg flag to process 2nd 50% Claim
1697 -- OR cenvat_claimed_ptg = 50
1698 )
1699 )
1700 AND receipt_num IS NOT NULL -- a Check to see whether populate details is done for this trx or not
1701 AND (p_transaction_id IS NULL OR transaction_id = p_transaction_id)
1702 AND (p_organization_id IS NULL OR organization_id = p_organization_id)
1703 AND (p_shipment_header_id IS NULL OR shipment_header_id = p_shipment_header_id) -- Vijay Shankar for Bug#3940588
1704 AND (p_shipment_line_id IS NULL OR shipment_line_id = p_shipment_line_id) -- Vijay Shankar for Bug#3940588
1705 -- followingcondition is not required
1706 --AND (p_receipt_num IS NULL OR receipt_num = p_receipt_num)
1707 AND (p_transaction_type IS NULL OR transaction_type = p_transaction_type)
1708 AND (p_parent_trx_type IS NULL OR parent_transaction_type = p_parent_trx_type) -- Vijay Shankar for Bug#3940588
1709 AND (
1710 (p_transaction_from IS NULL AND p_transaction_to IS NULL)
1711 OR (p_transaction_from IS NULL AND trunc(creation_date) <= p_transaction_to)
1712 OR (p_transaction_to IS NULL AND trunc(creation_date) >= p_transaction_from)
1713 OR (trunc(creation_date) BETWEEN p_transaction_from AND p_transaction_to)
1714 )
1715 -- Check to pickup only those lines in which taxes cannot be modified,
1716 and
1717 ( ( p_shipment_header_id is null and p_called_from = 'JAINPORE' ) OR -- added, Harshita for bug #4300708
1718 exists (select 1 from JAI_RCV_LINES b
1719 where b.shipment_line_id = a.shipment_line_id
1720 and b.tax_modified_flag='N')
1721 )
1722 -- 3927371 (generic finding) this should process only these transactions if only receipt number is specified.
1723 -- This should be removed when old code is obsoleted with new code
1724 -- AND transaction_type IN ('CORRECT', 'DELIVER', 'RETURN TO RECEIVING'); -- commented for Vijay Shankar for Bug#3940588
1725 FOR UPDATE OF transaction_id -- added by Vijay Shankar for Bug#3940588
1726 ORDER BY transaction_id; -- added by Vijay Shankar for Bug#3940588 */
1727
1728 --Start added by Sanjikum for Bug#4929410
1729 PROCEDURE ja_in_populate_details IS
1730
1731 CURSOR c_lock_rcv_dtls IS
1732 SELECT rowid
1733 FROM jai_rcv_transactions
1734 WHERE transaction_id IN
1735 ( SELECT transaction_id
1736 FROM jai_rtp_populate_t)
1737 FOR UPDATE OF transaction_id ;
1738
1739 lock_rcv_rec_dtls c_lock_rcv_dtls%ROWTYPE ;
1740
1741 BEGIN
1742 IF p_shipment_header_id is null THEN
1743
1744 INSERT
1745 INTO JAI_RTP_POPULATE_T
1746 (TRANSACTION_ID,
1747 SHIPMENT_LINE_ID,
1748 PROCESS_FLAG,
1749 CENVAT_RG_FLAG,
1750 TRANSACTION_TYPE,
1751 PARENT_TRANSACTION_TYPE,
1752 RECEIPT_NUM,
1753 CENVAT_CLAIMED_PTG,
1754 ATTRIBUTE_CATEGORY)
1755 (
1756 SELECT transaction_id,
1757 shipment_line_id,
1758 process_status,
1759 cenvat_rg_status,
1760 transaction_type,
1761 parent_transaction_type,
1762 receipt_num,
1763 cenvat_claimed_ptg,
1764 attribute_category
1765 FROM jai_rcv_transactions a
1766 WHERE organization_id = p_organization_id
1767 AND a.receipt_num IS NULL
1768 AND p_called_from = 'JAINPORE'
1769 AND ( --Added date parameters for bug # 13880102
1770 ( p_transaction_from IS NULL AND p_transaction_to IS NULL)
1771 OR (p_transaction_from IS NULL AND trunc(creation_date) <= p_transaction_to)
1772 OR (p_transaction_to IS NULL AND trunc(creation_date) >= p_transaction_from)
1773 OR (trunc(creation_date) BETWEEN p_transaction_from AND p_transaction_to)
1774 )
1775 );
1776
1777 ELSE
1778 INSERT
1779 INTO JAI_RTP_POPULATE_T
1780 (TRANSACTION_ID,
1781 SHIPMENT_LINE_ID,
1782 PROCESS_FLAG,
1783 CENVAT_RG_FLAG,
1784 TRANSACTION_TYPE,
1785 PARENT_TRANSACTION_TYPE,
1786 RECEIPT_NUM,
1787 CENVAT_CLAIMED_PTG,
1788 ATTRIBUTE_CATEGORY)
1789 (
1790 SELECT transaction_id,
1791 shipment_line_id,
1792 process_status,
1793 cenvat_rg_status,
1794 transaction_type,
1795 parent_transaction_type,
1796 receipt_num,
1797 cenvat_claimed_ptg,
1798 attribute_category
1799 FROM jai_rcv_transactions a
1800 WHERE organization_id = p_organization_id
1801 AND a.shipment_header_id = p_shipment_header_id
1802 AND a.receipt_num IS NULL
1803 AND EXISTS (SELECT 1
1804 FROM jai_rcv_lines b
1805 WHERE a.shipment_line_id = b.shipment_line_id
1806 AND tax_modified_flag='N')
1807 AND ( --Added date parameters for bug # 13880102
1808 ( p_transaction_from IS NULL AND p_transaction_to IS NULL)
1809 OR (p_transaction_from IS NULL AND trunc(creation_date) <= p_transaction_to)
1810 OR (p_transaction_to IS NULL AND trunc(creation_date) >= p_transaction_from)
1811 OR (trunc(creation_date) BETWEEN p_transaction_from AND p_transaction_to)
1812 )
1813
1814 );
1815
1816
1817 END IF ;
1818
1819 OPEN c_lock_rcv_dtls ;
1820 FETCH c_lock_rcv_dtls INTO lock_rcv_rec_dtls ;
1821 CLOSE c_lock_rcv_dtls ;
1822 END ja_in_populate_details;
1823
1824 PROCEDURE ja_in_get_transactions IS
1825 CURSOR c_lock_rcv_trans IS
1826 SELECT rowid
1827 FROM jai_rcv_transactions
1828 WHERE transaction_id IN
1829 ( SELECT transaction_id
1830 FROM jai_rtp_trans_t )
1831 FOR UPDATE OF transaction_id ;
1832
1833 lock_rcv_rec_trans c_lock_rcv_trans%ROWTYPE ;
1834 BEGIN
1835 IF p_shipment_header_id is null THEN
1836 IF p_transaction_type IS NOT NULL THEN
1837 IF p_parent_trx_type IS NOT NULL THEN
1838 INSERT
1839 INTO JAI_RTP_TRANS_T
1840 (TRANSACTION_ID,
1841 PROCESS_FLAG,
1842 CENVAT_RG_FLAG,
1843 PROCESS_MESSAGE,
1844 CENVAT_RG_MESSAGE,
1845 TRANSACTION_TYPE,
1846 PARENT_TRANSACTION_TYPE,
1847 RECEIPT_NUM,
1848 CENVAT_CLAIMED_PTG,
1849 SHIPMENT_LINE_ID)
1850 (
1851 SELECT transaction_id,
1852 process_status,
1853 cenvat_rg_status,
1854 process_message,
1855 cenvat_rg_message,
1856 transaction_type,
1857 parent_transaction_type,
1858 receipt_num,
1859 cenvat_claimed_ptg,
1860 shipment_line_id
1861 FROM jai_rcv_transactions a
1862 WHERE organization_id = p_organization_id
1863 AND transaction_type = p_transaction_type
1864 AND parent_transaction_type = p_parent_trx_type
1865 AND receipt_num IS NOT NULL
1866 AND ( p_simulate_flag = 'Y'
1867 OR
1868 ( process_status IS NULL
1869 OR process_status IN ('N', 'E','P')
1870 OR cenvat_rg_status IN ('N', 'E','P')
1871 OR process_vat_status IN ('N', 'E', 'P', jai_constants.unexpected_error, jai_constants.expected_error)
1872 )
1873 )
1874 AND (
1875 ( p_transaction_from IS NULL AND p_transaction_to IS NULL)
1876 OR (p_transaction_from IS NULL AND trunc(creation_date) <= p_transaction_to)
1877 OR (p_transaction_to IS NULL AND trunc(creation_date) >= p_transaction_from)
1878 OR (trunc(creation_date) BETWEEN p_transaction_from AND p_transaction_to)
1879 )
1880 ) ;
1881 ELSE -- p_parent_trx_type IS NOT NULL
1882 INSERT
1883 INTO JAI_RTP_TRANS_T
1884 (TRANSACTION_ID,
1885 PROCESS_FLAG,
1886 CENVAT_RG_FLAG,
1887 PROCESS_MESSAGE,
1888 CENVAT_RG_MESSAGE,
1889 TRANSACTION_TYPE,
1890 PARENT_TRANSACTION_TYPE,
1891 RECEIPT_NUM,
1892 CENVAT_CLAIMED_PTG,
1893 SHIPMENT_LINE_ID)
1894 (
1895 SELECT transaction_id,
1896 process_status,
1897 cenvat_rg_status,
1898 process_message,
1899 cenvat_rg_message,
1900 transaction_type,
1901 parent_transaction_type,
1902 receipt_num,
1903 cenvat_claimed_ptg,
1904 shipment_line_id
1905 FROM jai_rcv_transactions a
1906 WHERE organization_id = p_organization_id
1907 AND transaction_type = p_transaction_type
1908 AND receipt_num IS NOT NULL
1909 AND ( p_simulate_flag = 'Y'
1910 OR
1911 ( process_status IS NULL
1912 OR process_status IN ('N', 'E','P')
1913 OR cenvat_rg_status IN ('N', 'E','P')
1914 OR process_vat_status IN ('N', 'E', 'P', jai_constants.unexpected_error, jai_constants.expected_error)
1915 )
1916 )
1917 AND (
1918 ( p_transaction_from IS NULL AND p_transaction_to IS NULL)
1919 OR (p_transaction_from IS NULL AND trunc(creation_date) <= p_transaction_to)
1920 OR (p_transaction_to IS NULL AND trunc(creation_date) >= p_transaction_from)
1921 OR (trunc(creation_date) BETWEEN p_transaction_from AND p_transaction_to)
1922 )
1923 ) ;
1924
1925 END IF ; --IF p_parent_trx_type IS NOT NULL
1926
1927 ELSE --IF p_transaction_type IS NOT NULL
1928 INSERT
1929 INTO JAI_RTP_TRANS_T
1930 (TRANSACTION_ID,
1931 PROCESS_FLAG,
1932 CENVAT_RG_FLAG,
1933 PROCESS_MESSAGE,
1934 CENVAT_RG_MESSAGE,
1935 TRANSACTION_TYPE,
1936 PARENT_TRANSACTION_TYPE,
1937 RECEIPT_NUM,
1938 CENVAT_CLAIMED_PTG,
1939 SHIPMENT_LINE_ID)
1940 (
1941 SELECT transaction_id,
1942 process_status,
1943 cenvat_rg_status,
1944 process_message,
1945 cenvat_rg_message,
1946 transaction_type,
1947 parent_transaction_type,
1948 receipt_num,
1949 cenvat_claimed_ptg,
1950 shipment_line_id
1951 FROM jai_rcv_transactions a
1952 WHERE organization_id = p_organization_id
1953 AND receipt_num IS NOT NULL
1954 AND ( p_simulate_flag = 'Y'
1955 OR
1956 ( process_status IS NULL
1957 OR process_status IN ('N', 'E','P')
1958 OR cenvat_rg_status IN ('N', 'E','P')
1959 OR process_vat_status IN ('N', 'E', 'P', jai_constants.unexpected_error, jai_constants.expected_error)
1960 )
1961 )
1962 AND (
1963 ( p_transaction_from IS NULL AND p_transaction_to IS NULL)
1964 OR (p_transaction_from IS NULL AND trunc(creation_date) <= p_transaction_to)
1965 OR (p_transaction_to IS NULL AND trunc(creation_date) >= p_transaction_from)
1966 OR (trunc(creation_date) BETWEEN p_transaction_from AND p_transaction_to)
1967 )
1968 ) ;
1969 END IF ; --IF p_transaction_type IS NOT NULL
1970 ELSE
1971 INSERT
1972 INTO JAI_RTP_TRANS_T
1973 (TRANSACTION_ID,
1974 PROCESS_FLAG,
1975 CENVAT_RG_FLAG,
1976 PROCESS_MESSAGE,
1977 CENVAT_RG_MESSAGE,
1978 TRANSACTION_TYPE,
1979 PARENT_TRANSACTION_TYPE,
1980 RECEIPT_NUM,
1981 CENVAT_CLAIMED_PTG,
1982 SHIPMENT_LINE_ID)
1983 (
1984 SELECT transaction_id,
1985 process_status,
1986 cenvat_rg_status,
1987 process_message,
1988 cenvat_rg_message,
1989 transaction_type,
1990 parent_transaction_type,
1991 receipt_num,
1992 cenvat_claimed_ptg,
1993 shipment_line_id
1994 FROM jai_rcv_transactions a
1995 WHERE organization_id = p_organization_id
1996 AND shipment_header_id = p_shipment_header_id AND
1997 ( p_simulate_flag = 'Y'
1998 OR
1999 ( process_status IS NULL
2000 OR process_status IN ('N', 'E','P')
2001 OR cenvat_rg_status IN ('N', 'E','P')
2002 OR process_vat_status IN ('N', 'E', 'P', jai_constants.unexpected_error, jai_constants.expected_error)
2003 )
2004 )
2005 AND receipt_num IS NOT NULL
2006 AND (p_transaction_id IS NULL OR transaction_id = p_transaction_id)
2007 AND (p_shipment_line_id IS NULL OR shipment_line_id = p_shipment_line_id)
2008 AND (p_transaction_type IS NULL OR transaction_type = p_transaction_type)
2009 AND (p_parent_trx_type IS NULL OR parent_transaction_type = p_parent_trx_type)
2010 AND (
2011 (p_transaction_from IS NULL AND p_transaction_to IS NULL)
2012 OR (p_transaction_from IS NULL AND trunc(creation_date) <= p_transaction_to)
2013 OR (p_transaction_to IS NULL AND trunc(creation_date) >= p_transaction_from)
2014 OR (trunc(creation_date) BETWEEN p_transaction_from AND p_transaction_to)
2015 )
2016 AND EXISTS (SELECT 1
2017 FROM jai_rcv_lines b
2018 WHERE b.shipment_line_id = a.shipment_line_id
2019 AND b.tax_modified_flag='N')
2020 );
2021
2022 END IF ; -- IF p_shipment_header_id is null
2023
2024 OPEN c_lock_rcv_trans ;
2025 FETCH c_lock_rcv_trans INTO lock_rcv_rec_trans ;
2026 CLOSE c_lock_rcv_trans ;
2027
2028 END ja_in_get_transactions ;
2029 --Start added by Sanjikum for Bug#4929410
2030
2031 BEGIN
2032
2033 p_transaction_from := fnd_date.canonical_to_date(pv_transaction_from);
2034 p_transaction_to := fnd_date.canonical_to_date(pv_transaction_to);
2035 ln_audsid := userenv('SESSIONID');
2036 lv_tax_modified_check_flag := 'Y' ;
2037 ln_processed_cnt := 0;
2038
2039
2040 --Added by CSahoo for Bug 5344225
2041 --Start
2042 BEGIN
2043
2044 IF p_request_id is not null and p_request_id <> -1 THEN -- pramasub FP modified the line and given the comment below
2045 /*
2046 || ssumaith - for Iprocurement Bug#4281841 added the -1 in the above condition to ensure that the program does not
2047 || return in case of a call from Iprocurement.
2048 */
2049 ln_req_status := fnd_concurrent.wait_for_request
2050 (request_id => p_request_id,
2051 interval => 1,
2052 max_wait => 0,
2053 phase => lv_phase,
2054 status => lv_status,
2055 dev_phase => lv_dev_phase,
2056 dev_status => lv_dev_status,
2057 message => lv_message) ;
2058
2059 IF not ln_req_status THEN
2060 FND_FILE.put_line(FND_FILE.log, 'Phase : ' || lv_phase || 'Status : ' || lv_status || 'Dev Phase : ' || lv_dev_phase ||
2061 ' Dev Status : ' || lv_dev_status || ' Message : ' || lv_message );
2062 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 ');
2063 RETURN ;
2064 END IF ;
2065 END IF ;
2066
2067 EXCEPTION
2068 WHEN OTHERS THEN
2069 FND_FILE.put_line(FND_FILE.log, 'Phase : ' || lv_phase || 'Status : ' || lv_status || 'Dev Phase : ' || lv_dev_phase ||
2070 ' Dev Status : ' || lv_dev_status || ' Message : ' || lv_message );
2071 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... ');
2072 RETURN ;
2073 END;
2074
2075
2076
2077 BEGIN
2078 IF p_group_id is not null THEN
2079
2080 FOR rec_exists in c_interface_exists(p_group_id)
2081 LOOP
2082 ln_error := 0 ;
2083
2084 OPEN c_interface_error(p_group_id) ;
2085 FETCH c_interface_error INTO ln_error;
2086 CLOSE c_interface_error ;
2087
2088 IF ln_error = 1 THEN
2089 raise_application_error(-20001, ' Error while processing Receiving Transactions. Exiting from India Receiving Transaction Processor ') ;
2090 END IF ;
2091
2092 dbms_lock.sleep(1);
2093 END LOOP ;
2094 END IF ;
2095 EXCEPTION
2096 WHEN OTHERS THEN
2097 FND_FILE.put_line(FND_FILE.log, SQLERRM) ;
2098 RETURN ;
2099 END;
2100
2101 --ended, CSahoo for Bug 5344225
2102
2103 FND_FILE.put_line( FND_FILE.log, 'Start of Batch. Date:'||to_char(SYSDATE,'dd/mm/yyyy hh24:mi:ss') );
2104
2105 lv_request_id := FND_PROFILE.value('CONC_REQUEST_ID');
2106 FND_FILE.put_line( FND_FILE.log, 'Inputs. OrgnId:'||nvl(p_organization_id,-1)
2107 ||', TrxFrom->'||nvl(p_transaction_from, to_date('1-01-1700', 'dd-mm-yyyy'))
2108 ||', Trxto->'||nvl(p_transaction_to, to_date('1-01-1700', 'dd-mm-yyyy'))
2109 ||', TrxType->'||nvl(p_transaction_type, 'XXX')
2110 ||', RecptNum->'||nvl(p_receipt_num, 'ABCD')
2111 ||', CalFrom->'||nvl(p_called_from,'NO')
2112 ||', TrxId->'|| nvl(p_transaction_id, -999)
2113 ||', SimFlg->'|| p_simulate_flag
2114 ||', TrcSwtch->'|| p_trace_switch
2115 ||', ReqId->'|| lv_request_id
2116 --CSahoo for Bug 534425
2117 ||', p_request_id ' || p_request_id
2118 ||', p_group_id ' || p_group_id
2119 );
2120
2121 ---------------- Trace Generation Logic-------------------------
2122 BEGIN
2123 IF p_trace_switch = 'Y' THEN
2124
2125 OPEN c_get_audsid;
2126 FETCH c_get_audsid INTO ln_sid, ln_serial, lv_spid;
2127 CLOSE c_get_audsid;
2128
2129 OPEN c_get_dbname;
2130 FETCH c_get_dbname INTO lv_dbname;
2131 CLOSE c_get_dbname;
2132
2133 FND_FILE.put_line(FND_FILE.log, 'TraceFile Name = '||lower(lv_dbname)||'_ora_'||lv_spid||'.trc');
2134
2135 EXECUTE IMMEDIATE 'ALTER SESSION SET EVENTS ''10046 trace name context forever, level 4''';
2136
2137 END IF;
2138 EXCEPTION
2139 WHEN OTHERS THEN
2140 FND_FILE.put_line(FND_FILE.log, '%%%Problem in Trace Generation%%%');
2141 END;
2142
2143 /*----------------------------- START of Business Logic ----------------------*/
2144
2145 /******************** START OF DEFERRED CENVAT CLAIM LOGIC **************/
2146 BEGIN
2147
2148 IF p_called_from = 'JAINMVAT' THEN
2149
2150 -- value contained in p_shipment_header_id when called from JAINMVAT is batch_id of Claim/Unclaim
2151 ln_batch_id := p_shipment_header_id;
2152
2153 FND_FILE.put_line( FND_FILE.log, '~~~~~ Start of Deferred Claim ~~~~~. Batch:'||ln_batch_id);
2154 process_deferred_cenvat_claim(
2155 p_batch_id => ln_batch_id,
2156 p_called_from => p_called_from,
2157 p_simulate_flag => p_simulate_flag,
2158 p_process_flag => lv_process_flag,
2159 p_process_message => lv_process_message
2160 );
2161
2162 FND_FILE.put_line( FND_FILE.log, '~~~~~ End of Deferred Claim ~~~~~');
2163 IF lv_process_flag IN (jai_constants.unexpected_error, jai_constants.expected_error) THEN
2164 FND_FILE.put_line(fnd_file.log, 'PROCESS_DEFERRED_CENVAT_CLAIM. Err Message - '||lv_process_message);
2165 FND_FILE.put_line(fnd_file.log, 'For details, Please refer to JAI_RCV_CENVAT_CLAIMS table with batch_id = '||ln_batch_id);
2166 retcode := jai_constants.request_warning;
2167 END IF;
2168
2169 GOTO end_of_batch;
2170
2171 END IF;
2172
2173 EXCEPTION
2174 WHEN OTHERS THEN
2175 ROLLBACK;
2176 FND_file.put_line(fnd_file.log, 'Error Message:'||SQLERRM);
2177 /*dbms_output.put_line('MainProc-DeferredClaim:Err:'||SQLERRM);*/
2178 retcode := jai_constants.request_error;
2179 RETURN;
2180 END;
2181 /******************** END OF DEFERRED CLAIM **************/
2182
2183 -- Start, Vijay Shankar for Bug#3940588
2184 -- Following has to be executed as and when it is submitted from Receipts Localization form
2185 jai_cmn_utils_pkg.print_log('tax_mod.log','In transaction processing p_called_from:'||p_called_from);
2186 FND_FILE.put_line( FND_FILE.log, 'p_called_from:'||p_called_from);
2187
2188 IF p_called_from = 'JAINPORE' THEN
2189 FND_FILE.put_line( FND_FILE.log, '~~~~~ Updating JAI_RCV_LINES.tax_modified_flag to N ~~~~~');
2190
2191 UPDATE JAI_RCV_LINES a
2192 SET tax_modified_flag = 'N',
2193 last_update_date = SYSDATE,
2194 last_update_login = fnd_global.login_id,
2195 last_updated_by = fnd_global.user_id
2196 WHERE shipment_header_id = p_shipment_header_id
2197 AND tax_modified_flag IN ('Y', 'X')
2198 -- This condition will take not to update tax modified flag incase localization table is not populated
2199 -- eg.Incase of Unordered Receipt, Until MATCH happens we should not update the tax_modified_flag to 'Y'
2200 AND exists (select 1 from JAI_RCV_TRANSACTIONS
2201 where shipment_line_id = a.shipment_line_id);
2202
2203 -- This Commit is a definitive Commit that has to happen
2204 COMMIT;
2205
2206 END IF;
2207 -- End, Vijay Shankar for Bug#3940588
2208
2209
2210 FND_FILE.put_line( FND_FILE.log, '~~~~~ Start Populate Details ~~~~~');
2211 -- populate_details should be called only once and that too when the trxn is processed for the first time
2212
2213 ja_in_populate_details ; -- added by Sanjikum for bug#4929410
2214
2215 --FOR trx IN c_trxns_to_populate_dtls LOOP
2216 --commented the above and added the below by Sanjikum for Bug#4929410
2217 FOR trx IN (SELECT * FROM JAI_RTP_POPULATE_T ORDER BY receipt_num, transaction_id) LOOP
2218 BEGIN -- Added by nprashar for bug # 13880102
2219 SAVEPOINT start_trx_population; /*Uncommented by nprashar for bug # 13880102 */
2220 lv_codepath := '';
2221
2222 FND_FILE.put_line( FND_FILE.log, 'Recpt:'||trx.receipt_num||', TrxId:'||trx.transaction_id);
2223 populate_details(
2224 p_transaction_id => trx.transaction_id,
2225 p_process_status => lv_process_flag,
2226 p_process_message => lv_process_message,
2227 p_simulate_flag => p_simulate_flag,
2228 p_codepath => lv_codepath
2229 );
2230
2231 IF lv_process_flag = 'E' THEN
2232 RAISE loop_continue; --Added by nprashar for bug # 13880102
2233 --exit; Commented by nprashar for bug # 13880102
2234
2235 END IF;
2236
2237 IF trx.transaction_type IN ('RECEIVE','MATCH')
2238 /*Vijay Shankar for Bug#4250171. condition added to support OPM Transactions */
2239 AND (p_called_from <> CALLED_FROM_OPM OR trx.attribute_category in (OPM_RECEIPT,OPM_RETURNS) )
2240 THEN
2241 transaction_preprocessor(
2242 p_shipment_line_id => trx.shipment_line_id,
2243 p_transaction_id => trx.transaction_id,
2244 p_process_status => lv_process_flag,
2245 p_process_message => lv_process_message,
2246 p_simulate_flag => p_simulate_flag
2247 );
2248 END IF;
2249
2250 IF lv_process_flag = 'E' THEN
2251 RAISE loop_continue; --Added by nprashar for bug # 13880102
2252 --exit; Commented by nprashar for bug # 13880102
2253
2254 END IF;
2255 --Added by nprashar for bug # 13880102
2256 IF p_commit_switch = 'Y' THEN
2257 COMMIT;
2258 END IF;
2259 EXCEPTION
2260 WHEN loop_continue THEN
2261 ROLLBACK to start_trx_population;
2262
2263 process_msg_tab(trx.transaction_id):='Transaction_id->'||trx.transaction_id||' Error Message->'||lv_process_message;
2264 IF p_shipment_header_id is NULL THEN
2265 retcode := jai_constants.request_warning;
2266 ELSE
2267 retcode := jai_constants.request_error;
2268 END IF;
2269
2270 DELETE FROM JAI_RTP_POPULATE_T
2271 WHERE transaction_id = trx.transaction_id;
2272
2273 COMMIT;
2274
2275 END;
2276
2277 END LOOP;
2278 IF process_msg_tab.COUNT > 0 THEN
2279
2280 FND_FILE.put_line( FND_FILE.log, '*** List of Transactions whose processing is skipped due to the below errors ***');
2281
2282 FOR err_trx in process_msg_tab.FIRST..process_msg_tab.LAST
2283 LOOP
2284 FND_FILE.put_line( FND_FILE.log,process_msg_tab(err_trx));
2285 END LOOP;
2286
2287 END IF;
2288 --Addtions ends here for bug # 13880102
2289
2290 -- IF UPPER(p_called_from) <> 'APPLICATION' THEN
2291 --Commented by nprashar for bug # 13880102
2292 /*IF lv_process_flag = 'E' THEN
2293 ROLLBACK;
2294 FND_FILE.put_line( FND_FILE.log, '*** POPULATE_DETAILS Error ***:'||lv_process_message);
2295 errbuf := lv_process_message;
2296 retcode := jai_constants.request_error;
2297 RETURN;
2298 ELSIF p_commit_switch = 'Y' THEN
2299 -- first commit to save all the populated details of JAI_RCV_TRANSACTIONS
2300 COMMIT;
2301 END IF;*/
2302
2303
2304 /*~~~~~~~~~~~~~~~~~~~~~~~~~ Start of PROCESSING TRANSACTIONS ~~~~~~~~~~~~~~~~~~~~~*/
2305
2306 ja_in_get_transactions ; -- internal procedure call ; -- added by Sanjikum for bug #4929410
2307
2308 --FOR trx IN c_get_transactions LOOP
2309 --commented the above and added the below by Sanjikum for Bug#4929410
2310 FOR trx IN (SELECT * FROM JAI_RTP_TRANS_T ORDER BY transaction_id) LOOP
2311
2312 BEGIN
2313
2314 lv_common_err_mesg := null;
2315 lv_codepath := '';
2316 r_trx_after_processing := null;
2317
2318 FND_FILE.put_line(FND_FILE.log, '+++ Start of ReceiptNo, Transaction_id:'||trx.receipt_num||','||trx.transaction_id
2319 ||', trxn_type:'||trx.transaction_type
2320 ||', parent_trxn_type:'||trx.parent_transaction_type
2321 ||', process_status:'||trx.process_flag
2322 ||', cenvat_rg_status:'||trx.cenvat_rg_flag
2323 ||', cenvat_claimed_ptg:'||trx.cenvat_claimed_ptg
2324 );
2325
2326 -- lv_codepath := jai_general_pkg.plot_codepath(1, lv_codepath, 'process_batch', 'start'); /* 1 */
2327
2328 SAVEPOINT process_trxn;
2329
2330 lv_process_flag := trx.process_flag;
2331 lv_process_message := trx.process_message;
2332 lv_cenvat_rg_flag := trx.cenvat_rg_flag;
2333 lv_cenvat_rg_message := trx.cenvat_rg_message;
2334
2335 -- added, Harshita for bug #4300708
2336
2337 IF ( p_called_from = 'JAINPORE' and p_shipment_header_id is null
2338 and trx.transaction_type IN ('RECEIVE', 'MATCH') ) THEN
2339 update JAI_RCV_LINES
2340 set tax_modified_flag = 'N'
2341 where receipt_num = trx.receipt_num ;
2342 END IF ;
2343
2344 -- ended, Harshita for bug #4300708
2345
2346 process_transaction(
2347 p_transaction_id => trx.transaction_id,
2348 p_process_flag => lv_process_flag,
2349 p_process_message => lv_process_message,
2350 p_cenvat_rg_flag => lv_cenvat_rg_flag,
2351 p_cenvat_rg_message => lv_cenvat_rg_message,
2352 p_common_err_mesg => lv_common_err_mesg,
2353 p_called_from => p_called_from,
2354 p_simulate_flag => p_simulate_flag,
2355 p_codepath => lv_codepath,
2356 p_excise_processing_reqd => jai_constants.yes, --File.Sql.35 Cbabu
2357 p_vat_processing_reqd => jai_constants.yes --File.Sql.35 Cbabu
2358 );
2359
2360 OPEN c_trx(trx.transaction_id);
2361 FETCH c_trx INTO r_trx_after_processing;
2362 CLOSE c_trx;
2363
2364 -- 'X' flag is removed from the following ELSIF conditions because it is not an ERROR Status. MYXZ
2365 IF lv_common_err_mesg IS NOT NULL THEN
2366 -- A common error occured. So, Whole Processing for Transaction should be stopped
2367 ROLLBACK TO process_trxn;
2368 FND_FILE.put_line(FND_FILE.log, '*** Common Error for Transaction_id:'||trx.transaction_id
2369 ||fnd_global.local_chr(10)||fnd_global.local_chr(9)||'- Error:'||lv_common_err_mesg
2370 );
2371 retcode := 1;
2372 ELSIF lv_process_flag IN ('E') AND lv_cenvat_rg_flag IN ('E') THEN
2373 FND_FILE.put_line(FND_FILE.log, '*** FLAGS ERROR *** Transaction_id:'||trx.transaction_id
2374 ||fnd_global.local_chr(10)||fnd_global.local_chr(9)||'- ProcessErr:'||lv_process_message
2375 ||fnd_global.local_chr(10)||fnd_global.local_chr(9)||'- CenvatErr:'||lv_cenvat_rg_message
2376 );
2377 ELSIF lv_process_flag IN ('E') THEN
2378 FND_FILE.put_line(FND_FILE.log, '*** PROCESS ERROR *** Transaction_id:'||trx.transaction_id
2379 ||fnd_global.local_chr(10)||fnd_global.local_chr(9)||'- ErrorMessage:'||lv_process_message
2380 );
2381 ELSIF lv_cenvat_rg_flag IN ('E') THEN
2382 FND_FILE.put_line(FND_FILE.log, '*** CENVAT ERROR *** Transaction_id:'||trx.transaction_id
2383 ||fnd_global.local_chr(10)||fnd_global.local_chr(9)||'- ErrorMessage:'||lv_cenvat_rg_message
2384 );
2385
2386 /* added for VAT Impl. Vijay Shankar for Bug#4250236(4245089) */
2387 -- Vijay Shankar for Bug#4346453. RCV DFF Elim. Enh. ELSIF r_trx_after_processing.process_vat_status <> jai_constants.successful THEN
2388 ELSIF r_trx_after_processing.process_vat_status in ('E', jai_constants.unexpected_error, jai_constants.expected_error) then
2389 FND_FILE.put_line(FND_FILE.log, '*** VAT Message *** Transaction_id:'||trx.transaction_id
2390 ||fnd_global.local_chr(10)||fnd_global.local_chr(9)||', Flag:'||r_trx_after_processing.process_vat_status
2391 ||' - ErrorMessage:'||r_trx_after_processing.process_vat_message
2392 );
2393 END IF;
2394
2395 -- Start, 3927371
2396 IF lv_process_flag IN ('X') AND lv_cenvat_rg_flag IN ('X') THEN
2397 FND_FILE.put_line(FND_FILE.log, 'Transaction_Id:'||trx.transaction_id
2398 ||fnd_global.local_chr(10)||fnd_global.local_chr(9)||'- ProcessMessage(X):'||lv_process_message
2399 ||fnd_global.local_chr(10)||fnd_global.local_chr(9)||'- CenvatMessgae(X):'||lv_cenvat_rg_message
2400 );
2401 ELSIF lv_process_flag IN ('X') THEN
2402 FND_FILE.put_line(FND_FILE.log, 'Process Message(X):'||lv_process_message);
2403 ELSIF lv_cenvat_rg_flag IN ('X') THEN
2404 FND_FILE.put_line(FND_FILE.log, 'Cenvat Messgae(X):'||lv_cenvat_rg_message);
2405 END IF;
2406
2407 IF lv_process_flag = 'E' OR lv_cenvat_rg_flag = 'E' THEN
2408 retcode := 1;
2409 END IF;
2410 -- End, 3927371
2411
2412 /* Start, Vijay Shankar for Bug#3940588 */
2413 -- Transaction Post Processor
2414 transaction_postprocessor(
2415 p_shipment_line_id => trx.shipment_line_id,
2416 p_transaction_id => trx.transaction_id,
2417 p_process_status => lv_process_flag,
2418 p_process_message => lv_process_message,
2419 p_simulate_flag => p_simulate_flag,
2420 p_codepath => lv_codepath
2421 );
2422 -- End, Vijay Shankar for Bug#3940588
2423
2424 if lv_process_flag = 'E' then
2425 ROLLBACK TO process_trxn;
2426 FND_FILE.put_line(FND_FILE.log, 'Error:Transaction_postprocessor. MSG:'||lv_process_message);
2427 end if;
2428
2429 IF p_simulate_flag = 'Y' THEN
2430 FND_FILE.put_line(FND_FILE.log, 'Codepath->'||lv_codepath);
2431 ELSE
2432 UPDATE JAI_RCV_TRANSACTIONS
2433 SET codepath = lv_codepath
2434 --WHERE rowid = trx.row_id
2435 --commented the above and added the below by sanjikum for Bug#4929410
2436 WHERE transaction_id = trx.transaction_id;
2437 END IF;
2438
2439 -- IF UPPER(p_called_from) <> 'APPLICATION' THEN
2440 /* Vijay Shankar for Bug#4208224
2441 IF p_commit_switch = 'Y' THEN
2442 IF ln_processed_cnt >= ln_commit_after THEN
2443 COMMIT;
2444 ln_processed_cnt := 0;
2445 ELSE
2446 END IF;
2447 END IF;
2448 */
2449
2450 ln_processed_cnt := ln_processed_cnt + 1;
2451 lv_common_err_mesg := null;
2452
2453 EXCEPTION
2454 WHEN OTHERS THEN
2455 ROLLBACK TO process_trxn;
2456 FND_FILE.put_line( FND_FILE.log, 'Rolled Back Processing in RECEIPT_TRANSACTIONS_PKG.process_batch: Error->'||SQLERRM);
2457 retcode := 1;
2458 END;
2459
2460 END LOOP;
2461
2462 <<end_of_batch>>
2463 -- IF UPPER(p_called_from) <> 'APPLICATION' THEN
2464 IF p_commit_switch = 'Y' THEN
2465 -- Final Commit to Permanently Save any changes left
2466 COMMIT;
2467 END IF;
2468
2469 /* added by Vijay Shankar for Bug#3940588 */
2470 EXCEPTION
2471 WHEN OTHERS THEN
2472 ROLLBACK;
2473 FND_FILE.put_line( FND_FILE.log, 'Error:'||SQLERRM);
2474 FND_FILE.put_line( FND_FILE.log, 'Error Path:'||lv_codepath);
2475 RAISE;
2476 END process_batch;
2477
2478 /* Vijay Shankar for Bug#4346453. RCV DFF Elim. Enh. */
2479 PROCEDURE insert_rtv_batch_group(
2480 pn_batch_group_id OUT NOCOPY NUMBER,
2481 pn_batch_num IN NUMBER,
2482 pv_regime_code IN VARCHAR2,
2483 pn_organization_id IN NUMBER,
2484 pn_location_id IN NUMBER,
2485 pn_vendor_id IN NUMBER,
2486 pn_vendor_site_id IN NUMBER,
2487 pv_invoice_no IN VARCHAR2,
2488 pd_invoice_date IN DATE,
2489 pv_process_status IN VARCHAR2,
2490 pv_process_message IN VARCHAR2
2491 ) IS
2492 ln_user_id NUMBER;
2493 ln_login_id NUMBER;
2494
2495 BEGIN
2496
2497 ln_user_id := fnd_global.user_id;
2498 ln_login_id := fnd_global.user_id;
2499
2500 INSERT INTO jai_rcv_rtv_batch_grps(
2501 batch_group_id,
2502 batch_num, regime_code, organization_id, location_id,
2503 vendor_id, vendor_site_id, invoice_no, invoice_date,
2504 creation_date, created_by, last_update_date, last_updated_by, last_update_login
2505 ) VALUES (
2506 jai_rcv_rtv_batch_grps_s.nextval,
2507 pn_batch_num, pv_regime_code, pn_organization_id, pn_location_id,
2508 pn_vendor_id, pn_vendor_site_id, pv_invoice_no, pd_invoice_date,
2509 sysdate, ln_user_id, sysdate, ln_user_id, ln_login_id
2510 ) RETURNING batch_group_id INTO pn_batch_group_id;
2511
2512 END insert_rtv_batch_group;
2513
2514 /*following procedure added as part of RTV DFF Elimination Enhacement Vijay Shankar for Bug#4346453. RCV DFF Elim. Enh. */
2515 procedure process_rtv(
2516 pv_errbuf OUT NOCOPY VARCHAR2,
2517 pv_retcode OUT NOCOPY VARCHAR2,
2518 pn_batch_num IN NUMBER,
2519 pn_min_transaction_id IN NUMBER,
2520 pn_max_transaction_id IN NUMBER,
2521 pv_called_from IN VARCHAR2, --File.Sql.35 Cbabu DEFAULT 'Y',
2522 pv_commit_switch IN VARCHAR2, --File.Sql.35 Cbabu DEFAULT 'Y',
2523 pv_debug_switch IN VARCHAR2 --File.Sql.35 Cbabu DEFAULT 'N'
2524 ) is
2525
2526 cursor c_rtv_trxs(cpn_batch_num in number) is
2527 select a.receipt_num, a.transaction_id, a.shipment_line_id, a.shipment_header_id,
2528 a.organization_id, a.location_id, b.vendor_id, b.vendor_site_id, b.excise_batch_num, b.vat_batch_num,
2529 a.excise_invoice_no, a.vat_invoice_no, a.excise_invoice_date, a.vat_invoice_date
2530 , a.parent_transaction_id,
2531 a.PROCESS_STATUS, a.process_message, a.CENVAT_RG_STATUS, a.cenvat_rg_message,
2532 a.PROCESS_VAT_STATUS, a.process_vat_message
2533 -- , a.attribute1 excise_invoice_action, a.attribute2 vat_invoice_action, commented and added the below by vkaranam for bug#13364465
2534 , a.excise_inv_gen_status excise_invoice_action, a.vat_inv_gen_status vat_invoice_action,
2535 nvl(b.receipt_excise_rate,0) receipt_excise_rate,
2536 nvl(b.rtv_excise_rate, nvl(b.receipt_excise_rate, 0)) rtv_excise_rate,
2537 decode( b.excise_batch_num, cpn_batch_num, jai_constants.yes, jai_constants.no) process_excise_in_batch,
2538 decode( b.vat_batch_num, cpn_batch_num, jai_constants.yes, jai_constants.no) process_vat_in_batch
2539 from JAI_RCV_TRANSACTIONS a, jai_rcv_rtv_batch_trxs b
2540 where a.transaction_id = b.transaction_id
2541 and (pn_min_transaction_id is null or a.transaction_id >= pn_min_transaction_id)
2542 and (pn_max_transaction_id is null or a.transaction_id <= pn_max_transaction_id)
2543 and
2544 ( ( b.excise_batch_num = cpn_batch_num and a.excise_invoice_no is null)
2545 or ( b.vat_batch_num = cpn_batch_num and a.vat_invoice_no is null)
2546 )
2547 order by a.organization_id, a.location_id, b.vendor_id, b.vendor_site_id, a.transaction_id
2548 for update of a.excise_invoice_no, a.excise_invoice_date, a.vat_invoice_no, a.vat_invoice_date;
2549
2550 cursor c_regime_id(cpv_regime_code varchar2) is
2551 select regime_id
2552 from JAI_RGM_DEFINITIONS
2553 where regime_code = cpv_regime_code;
2554
2555 ln_regime_id JAI_RGM_DEFINITIONS.regime_id%TYPE;
2556
2557 lv_excise_invoice_no JAI_RCV_TRANSACTIONS.excise_invoice_no%TYPE;
2558 ld_excise_invoice_date DATE;
2559 lv_gen_excise_invoice_no JAI_RCV_TRANSACTIONS.excise_invoice_no%TYPE;
2560 ld_gen_excise_invoice_date DATE;
2561
2562 lv_vat_invoice_no JAI_RCV_TRANSACTIONS.vat_invoice_no%TYPE;
2563 ld_vat_invoice_date DATE;
2564 lv_gen_vat_invoice_no JAI_RCV_TRANSACTIONS.vat_invoice_no%TYPE;
2565 ld_gen_vat_invoice_date DATE;
2566
2567 lv_same_invoice_no_flag VARCHAR2(1);
2568
2569 ln_organization_id NUMBER(15);
2570 ln_location_id NUMBER(15);
2571 ln_vendor_id NUMBER(15);
2572 ln_vendor_site_id NUMBER(15);
2573
2574 lv_errbuf VARCHAR2(1000);
2575 lv_statement_id VARCHAR2(4);
2576
2577
2578 lv_process_flag JAI_RCV_TRANSACTIONS.PROCESS_STATUS%TYPE;
2579 lv_process_message JAI_RCV_TRANSACTIONS.process_message%TYPE;
2580 lv_cenvat_rg_flag JAI_RCV_TRANSACTIONS.CENVAT_RG_STATUS%TYPE;
2581 lv_cenvat_rg_message JAI_RCV_TRANSACTIONS.cenvat_rg_message%TYPE;
2582 lv_common_err_mesg JAI_RCV_TRANSACTIONS.cenvat_rg_message%TYPE;
2583
2584 lv_excise_processing_reqd VARCHAR2(1);
2585 lv_vat_processing_reqd VARCHAR2(1);
2586 lv_ssi_cenvat_rg_flag JAI_RCV_TRANSACTIONS.CENVAT_RG_STATUS%TYPE;
2587 lv_ssi_cenvat_rg_message JAI_RCV_TRANSACTIONS.cenvat_rg_message%TYPE;
2588
2589 lv_codepath JAI_RCV_TRANSACTIONS.codepath%TYPE;
2590 r_trx_after_processing c_trx%ROWTYPE;
2591 lb_err_flag boolean;
2592 ln_processed_cnt number;
2593
2594 lv_excise_inv_gen_action VARCHAR2(50);
2595 lv_vat_inv_gen_action VARCHAR2(50);
2596
2597 ln_excise_batch_group_id jai_rcv_rtv_batch_grps.batch_group_id%TYPE;
2598 ln_vat_batch_group_id jai_rcv_rtv_batch_grps.batch_group_id%TYPE;
2599
2600 begin
2601
2602 lv_statement_id := '0';
2603 if lb_debug then
2604 fnd_file.put_line(fnd_file.log, 'Input Params. pn_batch_num:'||pn_batch_num
2605 ||', pn_min_transaction_id:'||pn_min_transaction_id
2606 ||', pn_max_transaction_id:'||pn_max_transaction_id
2607 ||', pv_called_from:'||pv_called_from
2608 ||', pv_commit_switch:'||pv_commit_switch
2609 ||', pv_debug_switch:'||pv_debug_switch);
2610 end if;
2611
2612
2613 ln_processed_cnt := 0;
2614 ln_organization_id := -1;
2615 ln_location_id := -1;
2616 ln_vendor_id := -1;
2617 ln_vendor_site_id := -1;
2618
2619 /* Excise Invoice Generation for Batch */
2620 for rtv_rec in c_rtv_trxs(pn_batch_num) loop
2621
2622 lv_statement_id := '1';
2623 lv_excise_invoice_no := null;
2624 ld_excise_invoice_date := null;
2625 lv_vat_invoice_no := null;
2626 ld_vat_invoice_date := null;
2627
2628 fnd_file.put_line(fnd_file.log, '1. INSIDE MAIN LOOP');
2629
2630 -- excise invoice number will be generated if the following grouping condition changes
2631 if ln_organization_id <> rtv_rec.organization_id
2632 OR ln_location_id <> rtv_rec.location_id
2633 OR ln_vendor_id <> rtv_rec.vendor_id
2634 OR ln_vendor_site_id <> rtv_rec.vendor_site_id
2635 then
2636 lv_statement_id := '3';
2637 ln_organization_id := rtv_rec.organization_id;
2638 ln_location_id := rtv_rec.location_id;
2639 ln_vendor_id := rtv_rec.vendor_id;
2640 ln_vendor_site_id := rtv_rec.vendor_site_id;
2641
2642 lv_statement_id := '4';
2643 lv_same_invoice_no_flag := null;
2644 lv_same_invoice_no_flag :=
2645 jai_cmn_rgm_recording_pkg.get_rgm_attribute_value(
2646 pv_regime_code => jai_constants.vat_regime,
2647 pv_organization_type => jai_constants.orgn_type_io,
2648 pn_organization_id => ln_organization_id,
2649 pn_location_id => ln_location_id,
2650 pv_registration_type => jai_constants.regn_type_others, --'OTHERS',
2651 pv_attribute_type_code => NULL,
2652 pv_attribute_code => jai_constants.attr_code_same_inv_no -- 'SAME_INVOICE_NO'
2653 );
2654
2655 lv_statement_id := '5';
2656 lv_same_invoice_no_flag := nvl(lv_same_invoice_no_flag, jai_constants.no);
2657 lv_gen_excise_invoice_no := null;
2658 ld_gen_excise_invoice_date := null;
2659 lv_gen_vat_invoice_no := null;
2660 ld_gen_vat_invoice_date := null;
2661 ln_excise_batch_group_id := null;
2662 ln_vat_batch_group_id := null;
2663
2664 end if;
2665
2666 lv_statement_id := '5.1';
2667 if lb_debug then
2668 fnd_file.put_line(fnd_file.log, '~~~ ReceiptNum:'||rtv_rec.receipt_num||', TrxId:'||rtv_rec.transaction_id
2669 ||', SameFlg:'||lv_same_invoice_no_flag
2670 ||', ExBatch:'||rtv_rec.excise_batch_num ||', VatBatch:'||rtv_rec.vat_batch_num
2671 ||', ExAct:'||rtv_rec.excise_invoice_action ||', VatAct:'||rtv_rec.vat_invoice_action
2672 );
2673 end if;
2674
2675 lv_statement_id := '6';
2676 /* Start Excise Inv. Gen */
2677 if rtv_rec.excise_batch_num = pn_batch_num and rtv_rec.excise_invoice_no is null
2678 and rtv_rec.excise_invoice_action = INV_GEN_STATUS_GENERATE
2679 /*bug 8410609 - excise invoice not to be generated for OSP items*/
2680 AND Check_57F4_transaction(rtv_rec.transaction_id) <>'YES'
2681 then
2682
2683 fnd_file.put_line(fnd_file.log, '2. INSIDE IF');
2684
2685 lv_statement_id := '7';
2686 if lv_same_invoice_no_flag = jai_constants.yes then
2687 lv_statement_id := '8';
2688 lv_excise_invoice_no := rtv_rec.vat_invoice_no;
2689 ld_excise_invoice_date := rtv_rec.vat_invoice_date;
2690 end if;
2691
2692 lv_statement_id := '9';
2693 if lv_excise_invoice_no is null then
2694 lv_statement_id := '10';
2695 lv_excise_invoice_no := lv_gen_excise_invoice_no;
2696 ld_excise_invoice_date := ld_gen_excise_invoice_date;
2697
2698 lv_statement_id := '11';
2699 if lv_excise_invoice_no is null then
2700 lv_statement_id := '12';
2701 ld_gen_excise_invoice_date := trunc(sysdate);
2702 jai_cmn_setup_pkg.generate_excise_invoice_no(
2703 p_organization_id => ln_organization_id,
2704 p_location_id => ln_location_id,
2705 p_called_from => 'P', -- Required for excise invoice generation for RTV
2706 p_order_invoice_type_id => NULL,
2707 p_fin_year => jai_general_pkg.get_fin_year(ln_organization_id),
2708 p_excise_inv_no => lv_gen_excise_invoice_no,
2709 p_errbuf => lv_errbuf
2710 );
2711
2712 lv_statement_id := '13';
2713 lv_excise_invoice_no := lv_gen_excise_invoice_no;
2714 ld_excise_invoice_date := ld_gen_excise_invoice_date;
2715
2716 end if;
2717 end if;
2718
2719 else
2720 lv_statement_id := '14';
2721 lv_excise_invoice_no := rtv_rec.excise_invoice_no;
2722 ld_excise_invoice_date := rtv_rec.excise_invoice_date;
2723 end if;
2724 /* End Excise Inv. Gen */
2725
2726 lv_statement_id := '15';
2727 /* Start VAT Inv. Gen */
2728 if rtv_rec.vat_batch_num = pn_batch_num and rtv_rec.vat_invoice_no is null
2729 and rtv_rec.vat_invoice_action = INV_GEN_STATUS_GENERATE
2730 then
2731 lv_statement_id := '16';
2732 if lv_same_invoice_no_flag = jai_constants.yes then
2733 lv_statement_id := '17';
2734 lv_vat_invoice_no := lv_excise_invoice_no;
2735 ld_vat_invoice_date := ld_excise_invoice_date;
2736 end if;
2737
2738 if lv_vat_invoice_no is null then
2739 lv_statement_id := '18';
2740 lv_vat_invoice_no := lv_gen_vat_invoice_no;
2741 ld_vat_invoice_date := ld_gen_vat_invoice_date;
2742
2743 if lv_vat_invoice_no is null then
2744
2745 lv_statement_id := '19';
2746 open c_regime_id(jai_constants.vat_regime);
2747 fetch c_regime_id into ln_regime_id;
2748 close c_regime_id;
2749
2750 lv_statement_id := '20';
2751 ld_gen_vat_invoice_date := trunc(sysdate);
2752 jai_cmn_rgm_setup_pkg.gen_invoice_number(
2753 p_regime_id => ln_regime_id,
2754 p_organization_id => ln_organization_id,
2755 p_location_id => ln_location_id,
2756 p_date => ld_gen_vat_invoice_date,
2757 p_doc_class => 'R',
2758 p_doc_type_id => null,
2759 P_invoice_number => lv_gen_vat_invoice_no,
2760 p_process_flag => lv_process_flag,
2761 p_process_msg => lv_process_message
2762 );
2763
2764 lv_statement_id := '21';
2765 if lv_process_flag in (jai_constants.expected_error, jai_constants.unexpected_error) then
2766 fnd_file.put_line(fnd_file.log, 'VAT Inv Gen Error. Params- RgmId:'||ln_regime_id
2767 ||', OrgnId:'||ln_organization_id ||', LocId:'||ln_location_id
2768 ||', InvDate:'||ld_gen_vat_invoice_date||', TrxId:'|| rtv_rec.transaction_id );
2769 fnd_file.put_line(fnd_file.log, 'ErrorCode:'||lv_process_flag
2770 ||', ErrMsg:'||lv_process_message);
2771 raise_application_error( -20112, 'VAT Inv Gen Error. Code:'||lv_process_flag
2772 ||', MSG:'||lv_process_message);
2773 end if;
2774
2775 lv_statement_id := '22';
2776 lv_vat_invoice_no := lv_gen_vat_invoice_no;
2777 ld_vat_invoice_date := ld_gen_vat_invoice_date;
2778 end if;
2779
2780 end if;
2781
2782 else
2783 lv_statement_id := '23';
2784 lv_vat_invoice_no := rtv_rec.vat_invoice_no;
2785 ld_vat_invoice_date := rtv_rec.vat_invoice_date;
2786 end if;
2787 /* End. VAT Inv. Gen. */
2788
2789 lv_statement_id := '23.1';
2790 if lb_debug then
2791 fnd_file.put_line(fnd_file.log, '~~~ ExInvNo:'||lv_excise_invoicE_no ||', ld_excise_invoice_Date:'||ld_excise_invoice_date
2792 ||', VatInvNo:'||lv_vat_invoice_no ||', ld_vat_invoice_Date:'||ld_vat_invoice_date
2793 );
2794 end if;
2795
2796 if rtv_rec.excise_batch_num = pn_batch_num and rtv_rec.excise_invoice_no is null then
2797
2798 if rtv_rec.excise_invoice_action = INV_GEN_STATUS_GENERATE then
2799
2800 lv_statement_id := '24';
2801 update JAI_RCV_TRANSACTIONS a
2802 set excise_invoice_no = lv_excise_invoice_no,
2803 excise_invoice_date = ld_excise_invoice_date,
2804 --attribute3 = pn_batch_num,
2805 last_update_date = sysdate,
2806 last_updated_by = fnd_global.user_id
2807 where current of c_rtv_trxs;
2808
2809 lv_statement_id := '25';
2810 INSERT INTO JAI_RCV_RTV_DTLS(
2811 transaction_id, parent_transaction_id, shipment_line_id,
2812 excise_invoice_no, excise_invoice_date, rg_register_part_i,
2813 creation_date, created_by, last_update_date, last_updated_by, last_update_login
2814 ) VALUES (
2815 rtv_rec.transaction_id, rtv_rec.parent_transaction_id, rtv_rec.shipment_line_id,
2816 lv_excise_invoice_no, ld_excise_invoice_date, NULL,
2817 sysdate, fnd_global.user_id, sysdate, fnd_global.user_id, fnd_global.login_id
2818 );
2819
2820 lv_excise_inv_gen_action := INV_GEN_STATUS_INV_GENERATED;
2821 else
2822 lv_excise_inv_gen_action := rtv_rec.excise_invoice_action;
2823 end if;
2824 end if;
2825
2826 if rtv_rec.vat_batch_num = pn_batch_num and rtv_rec.vat_invoice_no is null then
2827 if rtv_rec.vat_invoice_action = INV_GEN_STATUS_GENERATE then
2828 lv_statement_id := '26';
2829 update JAI_RCV_TRANSACTIONS a
2830 set vat_invoice_no = lv_vat_invoice_no,
2831 vat_invoice_date = ld_vat_invoice_date,
2832 --attribute4 = pn_batch_num,
2833 last_update_date = sysdate,
2834 last_updated_by = fnd_global.user_id
2835 where current of c_rtv_trxs;
2836 lv_vat_inv_gen_action := INV_GEN_STATUS_INV_GENERATED;
2837 else
2838 lv_vat_inv_gen_action := rtv_rec.vat_invoice_action;
2839 end if;
2840 end if;
2841
2842 if rtv_rec.process_excise_in_batch = jai_constants.yes and ln_excise_batch_group_id is null then
2843 lv_statement_id := '26.1';
2844 insert_rtv_batch_group(
2845 pn_batch_group_id => ln_excise_batch_group_id,
2846 pn_batch_num => pn_batch_num,
2847 pv_regime_code => jai_constants.excise_regime,
2848 pn_organization_id => ln_organization_id,
2849 pn_location_id => ln_location_id,
2850 pn_vendor_id => ln_vendor_id,
2851 pn_vendor_site_id => ln_vendor_site_id,
2852 pv_invoice_no => lv_excise_invoice_no,
2853 pd_invoice_date => ld_excise_invoice_date,
2854 pv_process_status => lv_process_flag,
2855 pv_process_message => lv_process_message
2856 );
2857
2858 if lv_process_flag in (jai_constants.unexpected_error, jai_constants.expected_error) then
2859 RAISE_APPLICATION_ERROR( 20015, 'Error in Excise insert_rtv_batch_group. MSG:'||lv_process_message);
2860 end if;
2861 end if;
2862
2863 if rtv_rec.process_vat_in_batch = jai_constants.yes and ln_vat_batch_group_id is null then
2864 lv_statement_id := '26.2';
2865 insert_rtv_batch_group(
2866 pn_batch_group_id => ln_vat_batch_group_id,
2867 pn_batch_num => pn_batch_num,
2868 pv_regime_code => jai_constants.vat_regime,
2869 pn_organization_id => ln_organization_id,
2870 pn_location_id => ln_location_id,
2871 pn_vendor_id => ln_vendor_id,
2872 pn_vendor_site_id => ln_vendor_site_id,
2873 pv_invoice_no => lv_vat_invoice_no,
2874 pd_invoice_date => ld_vat_invoice_date,
2875 pv_process_status => lv_process_flag,
2876 pv_process_message => lv_process_message
2877 );
2878
2879 if lv_process_flag in (jai_constants.unexpected_error, jai_constants.expected_error) then
2880 RAISE_APPLICATION_ERROR( 20015, 'Error in Excise insert_rtv_batch_group. MSG:'||lv_process_message);
2881 end if;
2882 end if;
2883
2884 if rtv_rec.process_excise_in_batch = jai_constants.yes
2885 or rtv_rec.process_vat_in_batch = jai_constants.yes
2886 then
2887 update jai_rcv_rtv_batch_trxs
2888 set excise_batch_group_id = decode(rtv_rec.process_excise_in_batch, jai_constants.yes, ln_excise_batch_group_id),
2889 vat_batch_group_id = decode(rtv_rec.process_vat_in_batch, jai_constants.yes, ln_vat_batch_group_id)
2890 where transaction_id = rtv_rec.transaction_id;
2891 end if;
2892
2893 lv_statement_id := '27';
2894
2895 lv_process_flag := rtv_rec.process_status;
2896 lv_process_message := rtv_rec.process_message;
2897 lv_cenvat_rg_flag := rtv_rec.cenvat_rg_status;
2898 lv_cenvat_rg_message := rtv_rec.cenvat_rg_message;
2899 lv_common_err_mesg := null;
2900 lb_err_flag := false;
2901
2902 if rtv_rec.process_excise_in_batch = jai_constants.yes
2903 and rtv_rec.excise_invoice_action = INV_GEN_STATUS_GENERATE
2904 then
2905 lv_excise_processing_reqd := jai_constants.yes;
2906 else
2907 lv_excise_processing_reqd := jai_constants.no;
2908 end if;
2909
2910 if rtv_rec.process_vat_in_batch = jai_constants.yes
2911 and rtv_rec.vat_invoice_action in (INV_GEN_STATUS_GENERATE, INV_GEN_STATUS_INV_NA)
2912 and rtv_rec.process_vat_status <> jai_constants.successful
2913 then
2914 lv_vat_processing_reqd := jai_constants.yes;
2915 else
2916 lv_vat_processing_reqd := jai_constants.no;
2917 end if;
2918
2919 fnd_file.put_line(fnd_file.log, '3. BEFORE PROCESS TRANSACTION');
2920
2921 /* call to process the transactions */
2922 process_transaction(
2923 p_transaction_id => rtv_rec.transaction_id,
2924 p_process_flag => lv_process_flag,
2925 p_process_message => lv_process_message,
2926 p_cenvat_rg_flag => lv_cenvat_rg_flag,
2927 p_cenvat_rg_message => lv_cenvat_rg_message,
2928 p_common_err_mesg => lv_common_err_mesg,
2929 p_called_from => CALLED_FROM_JAITIGRTV, -- pv_called_from,
2930 p_simulate_flag => 'N',
2931 p_codepath => lv_codepath,
2932 p_excise_processing_reqd => lv_excise_processing_reqd,
2933 p_vat_processing_reqd => lv_vat_processing_reqd
2934 );
2935
2936 lv_statement_id := '28';
2937 OPEN c_trx(rtv_rec.transaction_id);
2938 FETCH c_trx INTO r_trx_after_processing;
2939 CLOSE c_trx;
2940
2941 lv_statement_id := '29';
2942 IF lv_common_err_mesg IS NOT NULL THEN
2943 FND_FILE.put_line(FND_FILE.log, '*** Common Error for Transaction_id:'||rtv_rec.transaction_id
2944 ||fnd_global.local_chr(10)||fnd_global.local_chr(9)||'- Error:'||lv_common_err_mesg
2945 );
2946 lb_err_flag := true;
2947 goto end_of_trx;
2948 -- lv_err_mesg := lv_common_err_mesg;
2949 ELSIF lv_process_flag IN ('E') AND lv_cenvat_rg_flag IN ('E') THEN
2950 FND_FILE.put_line(FND_FILE.log, '*** FLAGS ERROR *** Transaction_id:'||rtv_rec.transaction_id
2951 ||fnd_global.local_chr(10)||fnd_global.local_chr(9)||'- ProcessErr:'||lv_process_message
2952 ||fnd_global.local_chr(10)||fnd_global.local_chr(9)||'- CenvatErr:'||lv_cenvat_rg_message
2953 );
2954 lb_err_flag := true;
2955 goto end_of_trx;
2956 ELSIF lv_process_flag IN ('E') THEN
2957 FND_FILE.put_line(FND_FILE.log, '*** PROCESS ERROR *** Transaction_id:'||rtv_rec.transaction_id
2958 ||fnd_global.local_chr(10)||fnd_global.local_chr(9)||'- ErrorMessage:'||lv_process_message
2959 );
2960 lb_err_flag := true;
2961 goto end_of_trx;
2962 ELSIF lv_cenvat_rg_flag IN ('E') THEN
2963 FND_FILE.put_line(FND_FILE.log, '*** CENVAT ERROR *** Transaction_id:'||rtv_rec.transaction_id
2964 ||fnd_global.local_chr(10)||fnd_global.local_chr(9)||'- ErrorMessage:'||lv_cenvat_rg_message
2965 );
2966 lb_err_flag := true;
2967 goto end_of_trx;
2968 ELSIF r_trx_after_processing.process_vat_status in ('E', jai_constants.unexpected_error, jai_constants.expected_error) THEN
2969 FND_FILE.put_line(FND_FILE.log, '*** VAT Message *** Transaction_id:'||rtv_rec.transaction_id
2970 ||fnd_global.local_chr(10)||fnd_global.local_chr(9)||', Flag:'||r_trx_after_processing.process_vat_status
2971 ||' - ErrorMessage:'||r_trx_after_processing.process_vat_message
2972 );
2973 lb_err_flag := true;
2974 goto end_of_trx;
2975 END IF;
2976
2977 lv_statement_id := '30';
2978 IF lv_process_flag IN ('X') AND lv_cenvat_rg_flag IN ('X') THEN
2979 FND_FILE.put_line(FND_FILE.log, 'Transaction_Id:'||rtv_rec.transaction_id
2980 ||fnd_global.local_chr(10)||fnd_global.local_chr(9)||'- ProcessMessage(X):'||lv_process_message
2981 ||fnd_global.local_chr(10)||fnd_global.local_chr(9)||'- CenvatMessgae(X):'||lv_cenvat_rg_message
2982 );
2983 ELSIF lv_process_flag IN ('X') THEN
2984 FND_FILE.put_line(FND_FILE.log, 'Process Message(X):'||lv_process_message);
2985 ELSIF lv_cenvat_rg_flag IN ('X') THEN
2986 FND_FILE.put_line(FND_FILE.log, 'Cenvat Messgae(X):'||lv_cenvat_rg_message);
2987 END IF;
2988
2989 lv_statement_id := '31';
2990 IF lv_process_flag = 'E' OR lv_cenvat_rg_flag = 'E' THEN
2991 lb_err_flag := true;
2992 goto end_of_trx;
2993 -- retcode := 1;
2994 END IF;
2995
2996 /* following procedure call is a replacement for SSI functinoality of JAINRTVN. This is created due to DFF removal */
2997 if lv_excise_processing_reqd = jai_constants.yes and rtv_rec.receipt_excise_rate <> rtv_rec.rtv_excise_rate then
2998 jai_rcv_excise_processing_pkg.rtv_processing_for_ssi(
2999 pn_transaction_id => rtv_rec.transaction_id,
3000 pv_codepath => lv_codepath,
3001 pv_process_status => lv_ssi_cenvat_rg_flag,
3002 pv_process_message => lv_ssi_cenvat_rg_message
3003 );
3004
3005 if lv_ssi_cenvat_rg_flag = 'E' then
3006 lb_err_flag := true;
3007 goto end_of_trx;
3008 end if;
3009 end if;
3010
3011 lv_statement_id := '31.1';
3012 /*
3013 commented for bug#13364465
3014 jai_rcv_transactions_pkg.update_attributes(
3015 p_transaction_id => rtv_rec.transaction_id,
3016 p_attribute1 => lv_excise_inv_gen_action,
3017 p_attribute2 => lv_vat_inv_gen_action
3018 );*/
3019
3020 --added for bug 13364465
3021 FND_FILE.put_line(FND_FILE.log, 'process_rtv call to jai_rcv_transactions_pkg.update_inv_stat_and_no rtv_rec.transaction_id '||rtv_rec.transaction_id
3022 ||' lv_excise_inv_gen_action '||lv_excise_inv_gen_action||' lv_vat_inv_gen_action '||lv_vat_inv_gen_action );
3023
3024 jai_rcv_transactions_pkg.update_inv_stat_and_no(
3025 p_transaction_id => rtv_rec.transaction_id,
3026 p_excise_inv_gen_status => lv_excise_inv_gen_action,
3027 p_vat_inv_gen_status => lv_vat_inv_gen_action
3028 );
3029
3030 fnd_file.put_line(fnd_file.log, '4. BEFORE CREATE 57F4');
3031
3032 lv_statement_id := '32';
3033 /* Following code is a replacement for removal of ja_in_create_rcv_57f4_trg trigger
3034 as part of RTV DFF Elimination */
3035 -- if r_trx.attribute1 = INV_GEN_STATUS_INV_GENERATED then
3036 --Commented below code and added if condition for bug #14508154
3037 --if lv_excise_processing_reqd = jai_constants.yes then
3038 if Check_57F4_transaction(rtv_rec.transaction_id) = 'YES' then
3039 jai_po_osp_pkg.create_rcv_57f4(
3040 p_transaction_id => rtv_rec.transaction_id,
3041 p_process_status => lv_process_flag,
3042 p_process_message => lv_process_message
3043 );
3044 end if;
3045
3046 lv_statement_id := '33';
3047 if lv_process_flag in (jai_constants.unexpected_error, jai_constants.expected_error) then
3048 FND_FILE.put_line(FND_FILE.log, 'Error in Call to jai_po_osp_pkg.create_rcv_57f4. Mesg:'||lv_process_message);
3049 lv_process_flag := 'E';
3050 lb_err_flag := true;
3051 goto end_of_trx;
3052 end if;
3053 -- end if;
3054
3055 lv_statement_id := '35';
3056 UPDATE JAI_RCV_TRANSACTIONS
3057 SET codepath = lv_codepath
3058 WHERE current of c_rtv_trxs;
3059
3060 <<end_of_trx>>
3061 if lb_err_flag then
3062 fnd_file.put_line( fnd_file.log, 'ErrCodepath:'||lv_codepath);
3063 raise_application_error( -20012, 'Error during RTV Processing. Look at the log for details');
3064 end if;
3065
3066 ln_processed_cnt := ln_processed_cnt + 1;
3067 lv_common_err_mesg := null;
3068 lv_codepath := '';
3069 end loop;
3070
3071 if pv_commit_switch = jai_constants.yes then
3072 COMMIT;
3073 -- ROLLBACK;
3074 end if;
3075
3076 EXCEPTION
3077 WHEN OTHERS THEN
3078 ROLLBACK;
3079 pv_retcode := jai_constants.request_error;
3080 pv_errbuf := 'Error(StmtId:'||lv_statement_id||')-'||SQLERRM;
3081 FND_FILE.put_line( FND_FILE.log, pv_errbuf);
3082 -- FND_FILE.put_line( FND_FILE.log, 'Error Path:'||lv_codepath);
3083 end process_rtv;
3084
3085
3086 PROCEDURE process_transaction(
3087 p_transaction_id IN NUMBER,
3088 p_process_flag IN OUT NOCOPY VARCHAR2,
3089 p_process_message IN OUT NOCOPY VARCHAR2,
3090 p_cenvat_rg_flag IN OUT NOCOPY VARCHAR2,
3091 p_cenvat_rg_message IN OUT NOCOPY VARCHAR2,
3092 p_common_err_mesg OUT NOCOPY VARCHAR2,
3093 p_called_from IN VARCHAR2,
3094 p_simulate_flag IN VARCHAR2,
3095 p_codepath IN OUT NOCOPY VARCHAR2,
3096 -- following parameters introduced for second claim of receive transaction
3097 p_process_special_reason IN VARCHAR2 DEFAULT NULL,
3098 p_process_special_qty IN NUMBER DEFAULT NULL,
3099 /*Vijay Shankar for Bug#4346453. RCV DFF Elim. Enh.*/
3100 p_excise_processing_reqd IN VARCHAR2, --File.Sql.35 Cbabu DEFAULT jai_constants.yes,
3101 p_vat_processing_reqd IN VARCHAR2 --File.Sql.35 Cbabu DEFAULT jai_constants.yes
3102 ) IS
3103
3104 --added by ssawant
3105 CURSOR c_base_trx(cp_transaction_id IN NUMBER) IS
3106 SELECT shipment_header_id, shipment_line_id, transaction_type, quantity, unit_of_measure, uom_code,
3107 parent_transaction_id, organization_id, location_id, subinventory, currency_conversion_rate,
3108 attribute_category attr_cat, nvl(attribute5, 'XX') rma_type, nvl(attribute4, 'N') generate_excise_invoice
3109 , routing_header_id -- porting of Bug#3949109 (3927371)
3110 , attribute3 online_claim_flag, source_document_code, po_header_id -- Vijay Shankar for Bug#3940588
3111 , po_line_location_id
3112 FROM rcv_transactions
3113 WHERE transaction_id = cp_transaction_id;
3114
3115 r_trx c_trx%ROWTYPE;
3116 r_base_trx c_base_trx%ROWTYPE;/*bgowrava for forward porting Bug#5756676*/
3117
3118 lv_transaction_type JAI_RCV_TRANSACTIONS.transaction_type%TYPE;
3119 ln_cenvat_claimed_ptg NUMBER;
3120
3121 lv_object_code VARCHAR2(10); --File.Sql.35 Cbabu := 'RCPT_TRXN:';
3122 lv_statement_id VARCHAR2(5);
3123
3124 lv_execution_point VARCHAR2(30); --File.Sql.35 Cbabu := 'COMMON_CODE';
3125 lv_temp VARCHAR2(100);
3126
3127 lv_process_vat_flag JAI_RCV_TRANSACTIONS.PROCESS_VAT_STATUS%TYPE;
3128 lv_process_vat_message JAI_RCV_TRANSACTIONS.process_vat_message%TYPE;
3129
3130 lv_process_status VARCHAR2(2); --added by ssawant
3131 lv_qty_register_entry_type VARCHAR2(20) := null; /*bug 7662347*/ /* Increased size from 2 to 20 for bug #12818250 by vkavulur */
3132
3133 lv_destination_type_code jai_rcv_transactions.destination_type_code%TYPE ;--Added by nprashar for bug #10221291
3134
3135 CURSOR cur_parent_trx( cp_prnt_trx_id NUMBER ) IS
3136 SELECT *
3137 FROM jai_rcv_transactions
3138 WHERE transaction_id = cp_prnt_trx_id; --Added by nprashar for bug#10221291
3139
3140 parent_trx_rec cur_parent_trx%ROWTYPE ; --Added by nprashar for bug#10221291
3141
3142 BEGIN
3143
3144 lv_object_code := 'RCPT_TRXN:';
3145 lv_execution_point := 'COMMON_CODE';
3146
3147 -- this is to identify the path in SQL TRACE file if any problem occured
3148 SELECT to_char(sysdate,'dd/mm/yyyy hh24:mi:ss')||'-jirt_pkg.process_transaction-'||p_transaction_id INTO lv_temp FROM DUAL;
3149
3150 FND_FILE.put_line(FND_FILE.log, '^Start of Trx:'||p_transaction_id||'. Time:'||to_char(SYSDATE,'dd/mm/yyyy hh24:mi:ss')
3151 ||', PrcSpecialReason:'||p_process_special_reason||', PrcSplQty:'||p_process_special_qty
3152 );
3153
3154 p_codepath := jai_general_pkg.plot_codepath(1, p_codepath, 'receipt_transactions_pkg.process_transaction', 'START');
3155
3156 lv_statement_id := '1';
3157
3158 --added the cursor and 2 assignments here by Ramananda for bug#4519697
3159 OPEN c_trx(p_transaction_id);
3160 FETCH c_trx INTO r_trx;
3161 CLOSE c_trx;
3162
3163 --Added by nprashar for bug # 10221291
3164 OPEN cur_parent_trx ( r_trx.parent_transaction_id );
3165 FETCH cur_parent_trx INTO parent_trx_rec;
3166 CLOSE cur_parent_trx;
3167
3168 lv_process_vat_flag := r_trx.process_vat_status; --Process_vat_flag ; -- Ramananda for bug#4519697
3169 lv_process_vat_message := r_trx.process_vat_message;
3170
3171
3172 OPEN c_base_trx(p_transaction_id); /*bgowrava for forward porting Bug#5756676*/
3173 FETCH c_base_trx INTO r_base_trx;
3174 CLOSE c_base_trx;
3175
3176 validate_transaction(
3177 p_transaction_id => p_transaction_id,
3178 p_process_flag => p_process_flag,
3179 p_process_message => p_process_message,
3180 p_cenvat_rg_flag => p_cenvat_rg_flag,
3181 p_cenvat_rg_message => p_cenvat_rg_message,
3182 /* following two flags introduced for VAT implementation. Vijay Shankar for Bug#4250236(4245089) */
3183 p_process_vat_flag => lv_process_vat_flag,
3184 p_process_vat_message => lv_process_vat_message,
3185 p_called_from => p_called_from,
3186 p_simulate_flag => p_simulate_flag,
3187 p_codepath => p_codepath
3188 );
3189
3190 p_codepath := jai_general_pkg.plot_codepath(2, p_codepath);
3191
3192 -- ERROR occured in Validate transaction. So proceed with next transaction
3193 -- We should not check for 'X' here. Because Cenvat can be 'N' which should be processed
3194 -- process_flag = 'E' means somehow an error occured in Validate transaction
3195 IF p_process_flag = 'E' THEN
3196 p_common_err_mesg := p_process_message;
3197 GOTO exit_processing;
3198 END IF;
3199
3200 lv_statement_id := '2';
3201 --removed the cursor c_trx() from here by Ramananda for bug#4519697
3202
3203 IF r_trx.transaction_type = 'CORRECT' THEN
3204 lv_transaction_type := r_trx.parent_transaction_type;
3205 ELSE
3206 lv_transaction_type := r_trx.transaction_type;
3207 END IF;
3208
3209 -- "MATCH" included by Vijay Shankar for Bug#3940588
3210 IF lv_transaction_type NOT IN ( 'RECEIVE', 'DELIVER', 'RETURN TO RECEIVING', 'RETURN TO VENDOR', 'MATCH' ) THEN
3211 -- Localization donot support these transactions. So, Pls return back
3212 FND_FILE.put_line(FND_FILE.log, lv_object_code||'Localization doesnot support this transaction type');
3213 RETURN;
3214 END IF;
3215
3216 p_codepath := jai_general_pkg.plot_codepath(3, p_codepath);
3217
3218 ------------ Start of PROCESS_FLAG Execution ----------
3219 -- IF p_process_flag IN ('N','E') THEN
3220 IF p_simulate_flag = 'Y'
3221 OR p_process_flag IN ('N')
3222 THEN
3223
3224 lv_statement_id := '3';
3225 SAVEPOINT process_trxn_flag;
3226
3227 lv_execution_point := 'START_PROCESS_FLAG';
3228 lv_destination_type_code := r_trx.destination_type_code;--Added by nprashar for bug # 10221291
3229
3230 IF lv_transaction_type IN ( 'RECEIVE', 'RETURN TO VENDOR') THEN
3231 lv_statement_id := '4';
3232 p_codepath := jai_general_pkg.plot_codepath(4, p_codepath);
3233
3234 jai_rcv_rcv_rtv_pkg.process_transaction(
3235 p_transaction_id => p_transaction_id,
3236 p_simulation => p_simulate_flag,
3237 p_process_flag => p_process_flag,
3238 p_process_message => p_process_message,
3239 p_debug => lv_debug,
3240 p_codepath => p_codepath
3241 );
3242
3243 ELSIF lv_transaction_type IN ('DELIVER', 'RETURN TO RECEIVING') AND nvl(lv_destination_type_code,'xyz') <> 'SHOP FLOOR' AND nvl( parent_trx_rec.destination_type_code,'xyz') <> 'SHOP FLOOR' THEN /*Added by nprahsar for bug # 10221291 */
3244
3245
3246 lv_statement_id := '5';
3247 p_codepath := jai_general_pkg.plot_codepath(5, p_codepath);
3248
3249 jai_rcv_deliver_rtr_pkg.process_transaction (
3250 p_transaction_id => p_transaction_id,
3251 p_simulate => p_simulate_flag,
3252 p_codepath => p_codepath,
3253 p_process_status => p_process_flag,
3254 p_process_message => p_process_message
3255 );
3256
3257 ELSE
3258 FND_FILE.put_line( FND_FILE.log, '1****** No Codepath ******');
3259 END IF;
3260
3261 lv_execution_point := 'END_PROCESS_FLAG';
3262
3263 lv_statement_id := '6';
3264 IF p_process_flag IN ('E', 'X') THEN
3265 FND_FILE.put_line(FND_FILE.log, 'PRC_FLG_Error: RollingBack to process_trxn_flag');
3266 p_codepath := jai_general_pkg.plot_codepath(6, p_codepath);
3267 ROLLBACK TO process_trxn_flag;
3268 ElSIF p_process_flag = 'Y' THEN
3269 p_codepath := jai_general_pkg.plot_codepath(7, p_codepath);
3270 p_process_message := 'Successful';
3271 ELSE
3272 FND_FILE.put_line(FND_FILE.log, '1#PRC_FLG#'||p_process_flag);
3273 p_codepath := jai_general_pkg.plot_codepath(8, p_codepath);
3274 END IF;
3275
3276 END IF;
3277
3278 FND_FILE.put_line(FND_FILE.log, '2#PRC_FLG#'||p_process_flag);
3279 ------------ End of PROCESS_FLAG Execution ----------
3280
3281 lv_statement_id := '7';
3282 ln_cenvat_claimed_ptg := r_trx.cenvat_claimed_ptg;
3283 FND_FILE.put_line(FND_FILE.log, 'r_trx.item_class:'||r_trx.item_class);
3284 FND_FILE.put_line(FND_FILE.log, 'lv_online_qty_flag:'||lv_online_qty_flag);
3285
3286 /*bgowrava for forward porting Bug#5756676..start*/
3287 IF lv_online_qty_flag = 'Y' THEN
3288
3289 SAVEPOINT process_online_qty;
3290
3291 /*
3292
3293
3294 IF r_base_trx.attr_cat = 'India RMA Receipt' AND r_trx.item_class IN ('FGIN', 'FGEX', 'CCIN', 'CCEX')
3295 /*following OR added for bug 8319304*
3296 OR (r_base_trx.attr_Cat = 'India Receipt' and r_base_trx.source_document_Code = 'INVENTORY' AND r_trx.item_class IN ('FGIN', 'FGEX', 'CCIN', 'CCEX')) THEN
3297 *//*commented and added the below if condition for bug#10036144*/
3298 IF (r_base_trx.source_document_Code in ('RMA','INVENTORY') AND r_trx.item_class IN ('FGIN', 'FGEX', 'CCIN', 'CCEX')) THEN
3299
3300
3301 FND_FILE.put_line(FND_FILE.log, 'Calling ja_in_receipt_cenvat_rg_pkg.rg_i_entry');
3302
3303 /*bug 7662347*/
3304 IF lv_transaction_type = 'RETURN TO VENDOR'
3305 OR lv_transaction_type = 'DELIVER' /* Added for bug #12818250 by vkavulur */
3306 THEN
3307 lv_qty_register_entry_type := 'Dr' ; /* Reverted back the code for Bug #13110424 */
3308
3309
3310 ELSE
3311 lv_qty_register_entry_type := 'Cr'; /* Reverted back the code for Bug #13110424 */
3312 END IF;
3313
3314 jai_rcv_excise_processing_pkg.rg_i_entry(
3315 p_transaction_id => r_trx.transaction_id,
3316 pr_tax => NULL,
3317 p_register_entry_type => lv_qty_register_entry_type, /*bug 7662347*/
3318 p_register_id => ln_part_i_register_id,
3319 p_process_status => lv_process_status,
3320 p_process_message => lv_process_message,
3321 p_simulate_flag => p_simulate_flag,
3322 p_codepath => p_codepath
3323 );
3324
3325 FND_FILE.put_line(FND_FILE.log, 'ln_part_i_register_id:'||ln_part_i_register_id);
3326
3327 ELSIF r_trx.item_class IN ('RMIN', 'RMEX', 'CCIN', 'CCEX', 'CGIN', 'CGEX') THEN
3328
3329 lv_register_type := jai_general_pkg.get_rg_register_type( p_item_class => r_trx.item_class);
3330
3331 FND_FILE.put_line(FND_FILE.log, 'Register Type:'||lv_register_type);
3332
3333 -- to determine the way in which CGIN Items are Processed
3334 IF lv_register_type = 'C' THEN
3335 jai_rcv_excise_processing_pkg.derive_cgin_scenario(
3336 p_transaction_id => p_transaction_id,
3337 p_cgin_code => lv_cgin_code,
3338 p_process_status => lv_process_status,
3339 p_process_message => lv_process_message,
3340 p_codepath => p_codepath
3341 );
3342
3343 FND_FILE.put_line(FND_FILE.log, 'CGIN_CODE->'||lv_cgin_code);
3344 END IF;
3345 -- RG23 Part I Entry is already made during first Claim, in case of CGIN Items
3346 -- So no need of another entry during Second 50% Claim of CENVAT
3347 IF nvl(lv_cgin_code, 'XXX') <> 'REGULAR-HALF' THEN
3348
3349 FND_FILE.put_line(FND_FILE.log, 'Calling ja_in_receipt_cenvat_rg_pkg.rg23_part_i_entry');
3350
3351 /*bug 7662347*/
3352 --ADDED in clause 'DELIVER' for bug#16306678
3353 IF lv_transaction_type in ('RETURN TO VENDOR','DELIVER') THEN
3354 lv_qty_register_entry_type := 'Dr';
3355 ELSE
3356 lv_qty_register_entry_type := 'Cr';
3357 END IF;
3358
3359 jai_rcv_excise_processing_pkg.rg23_part_i_entry(
3360 p_transaction_id => r_trx.transaction_id,
3361 pr_tax => NULL,
3362 p_register_entry_type => lv_qty_register_entry_type, /*bug 7662347*/
3363 p_register_id => ln_part_i_register_id,
3364 p_process_status => lv_process_status,
3365 p_process_message => lv_process_message,
3366 p_simulate_flag => p_simulate_flag,
3367 p_codepath => p_codepath
3368 );
3369
3370 --lv_qty_register := 'RG23';
3371
3372 ELSE
3373 FND_FILE.put_line( FND_FILE.log, 'No Call to RG23_PART_I_ENTRY');
3374 END IF;
3375
3376 END IF;
3377
3378 IF lv_process_status in ('E','X') THEN
3379 ROLLBACK TO PROCESS_ONLINE_QTY;
3380 END IF;
3381
3382 END IF;
3383
3384 /*bgowrava for forward porting Bug#5756676..end*/
3385
3386 /* RG/Cenvat Works only incase if its not a Simulation. Because simulation is not implemented for CENVAT Part */
3387 IF p_simulate_flag = 'N'
3388 AND ( -- Vijay Shankar for Bug#4346453. RCV DFF Elim. Enh. p_cenvat_rg_flag IN ('N', 'E')
3389 -- condition modified as part of DFF Elimination. Vijay Shankar for Bug#4346453. RCV DFF Elim. Enh.
3390 ( p_excise_processing_reqd = jai_constants.yes -- Vijay Shankar for Bug#4346453. RCV DFF Elim. Enh.
3391 AND p_cenvat_rg_flag IN ('N', 'E','C','XT') /*Added by nprashar for bug # 8644480 */
3392 /*Added the XT condition for bug # 9821680*/
3393 AND (r_trx.transaction_type <> 'RETURN TO VENDOR'
3394 OR (r_trx.transaction_type = 'RETURN TO VENDOR' and p_called_from = CALLED_FROM_JAITIGRTV) -- nvl(r_trx.attribute1,'XXX')<> INV_GEN_STATUS_PENDING)
3395 )
3396 )
3397 -- following condition will be satisfied during 2nd 50% claim of CGIN items when called from JAINMVAT form
3398 -- Vijay Shankar for Bug#3940588. RECEIPTS DEPLUG
3399 OR (p_called_from='JAINMVAT' AND r_trx.cenvat_rg_status='Y')
3400 OR (p_process_special_reason = jai_rcv_excise_processing_pkg.second_50ptg_claim AND ln_cenvat_claimed_ptg < 100)
3401 )
3402 THEN
3403
3404 SAVEPOINT process_cenvat_rg_flag;
3405
3406 lv_execution_point := 'START_CENVAT_FLAG';
3407
3408 IF lv_transaction_type IN ( 'RECEIVE', 'RETURN TO VENDOR', 'DELIVER', 'RETURN TO RECEIVING')
3409 --AND nvl(lv_destination_type_code,'xyz') <> 'SHOP FLOOR' AND nvl( parent_trx_rec.destination_type_code,'xyz') <> 'SHOP FLOOR' /*Added by nprashar for bug # 10221291 */
3410 /* Commented for bug #13362352 */
3411 THEN
3412 -- this call passes the cenvat related accounting and register entries based on transaction type
3413 lv_statement_id := '8';
3414 p_codepath := jai_general_pkg.plot_codepath(9, p_codepath);
3415
3416 jai_rcv_excise_processing_pkg.process_transaction(
3417 p_transaction_id => p_transaction_id,
3418 p_process_status => p_cenvat_rg_flag,
3419 p_cenvat_claimed_ptg => ln_cenvat_claimed_ptg,
3420 p_process_message => p_cenvat_rg_message,
3421 p_simulate_flag => p_simulate_flag,
3422 p_codepath => p_codepath,
3423 -- following added by Vijay Shankar for Bug#3940588. RECEIPTS DEPLUG
3424 p_process_special_reason => p_process_special_reason,
3425 p_process_special_qty => p_process_special_qty
3426 );
3427
3428 ELSE
3429 FND_FILE.put_line( FND_FILE.log, lv_object_code||'Transaction type Not supported for cenvat Entries');
3430 END IF;
3431
3432 lv_execution_point := 'END_CENVAT_FLAG';
3433
3434 lv_statement_id := '9';
3435 IF p_cenvat_rg_flag IN ('E', 'X') THEN
3436 FND_FILE.put_line(FND_FILE.log, 'CEN_FLG_Error: RollingBack to process_cenvat_rg_flag');
3437 p_codepath := jai_general_pkg.plot_codepath(10, p_codepath);
3438 ROLLBACK TO process_cenvat_rg_flag;
3439 ElSIF p_cenvat_rg_flag = 'Y' THEN
3440 p_cenvat_rg_message := 'Successful';
3441 p_codepath := jai_general_pkg.plot_codepath(11, p_codepath);
3442 ELSE
3443 FND_FILE.put_line(FND_FILE.log, '1#CENVAT_FLG#'||p_cenvat_rg_flag);
3444 p_codepath := jai_general_pkg.plot_codepath(12, p_codepath);
3445 END IF;
3446
3447 END IF;
3448
3449 FND_FILE.put_line(FND_FILE.log, '2#CENVAT_FLG#'||p_cenvat_rg_flag);
3450 /* End of CENVAT_RG_FLAG Execution */
3451
3452 lv_statement_id := '10';
3453 p_codepath := jai_general_pkg.plot_codepath(13, p_codepath);
3454
3455 /* Start of VAT Execution. Vijay Shankar for Bug#4250236(4245089) */
3456 IF p_simulate_flag = 'N'
3457 AND -- Vijay Shankar for Bug#4346453. RCV DFF Elim. Enh. lv_process_vat_flag IN ('N', 'E')
3458 ( p_vat_processing_reqd = jai_constants.yes -- Vijay Shankar for Bug#4346453. RCV DFF Elim. Enh.
3459 AND lv_process_vat_flag IN ('N', 'E', jai_constants.expected_error, jai_constants.unexpected_error)
3460 AND (r_trx.transaction_type <> 'RETURN TO VENDOR'
3461 OR (r_trx.transaction_type = 'RETURN TO VENDOR' and p_called_from = CALLED_FROM_JAITIGRTV)
3462 )
3463 )
3464 THEN
3465
3466 SAVEPOINT process_vat_flag;
3467
3468 lv_execution_point := 'START_PROCESS_VAT';
3469
3470 IF lv_transaction_type IN ( 'RECEIVE', 'RETURN TO VENDOR') THEN
3471 -- this call passes the cenvat related accounting and register entries based on transaction type
3472 lv_statement_id := '11';
3473 p_codepath := jai_general_pkg.plot_codepath(14, p_codepath);
3474
3475 jai_rcv_rgm_claims_pkg.process_vat(
3476 p_transaction_id => p_transaction_id,
3477 p_process_status => lv_process_vat_flag,
3478 p_process_message => lv_process_vat_message
3479 );
3480
3481 ELSE
3482 FND_FILE.put_line( FND_FILE.log, lv_object_code||'Trxn not supported for VAT processing');
3483 END IF;
3484
3485 lv_execution_point := 'END_PROCESS_VAT';
3486
3487 lv_statement_id := '12';
3488 IF lv_process_vat_flag = jai_constants.successful THEN
3489 lv_process_vat_message := 'Successful';
3490 p_codepath := jai_general_pkg.plot_codepath(16, p_codepath);
3491 ELSIF lv_process_vat_flag <> jai_constants.successful THEN
3492 FND_FILE.put_line(FND_FILE.log, 'PrcVatFlg Err: RollingBack to process_vat_flag. Mesg:'||lv_process_vat_message);
3493 p_codepath := jai_general_pkg.plot_codepath(15, p_codepath);
3494 ROLLBACK TO process_vat_flag;
3495 ELSE
3496 FND_FILE.put_line(FND_FILE.log, '1#PrcVatFlg#'||lv_process_vat_flag);
3497 p_codepath := jai_general_pkg.plot_codepath(17, p_codepath);
3498 END IF;
3499
3500 END IF;
3501
3502 FND_FILE.put_line(FND_FILE.log, '2#PrcVatFlg#'||lv_process_vat_flag);
3503 /* End of VAT Execution */
3504
3505 lv_statement_id := '14';
3506 p_codepath := jai_general_pkg.plot_codepath(18, p_codepath);
3507
3508 IF p_simulate_flag = 'N' THEN
3509
3510 jai_rcv_transactions_pkg.update_process_flags(
3511 p_transaction_id => p_transaction_id,
3512 p_process_flag => p_process_flag,
3513 p_process_message => p_process_message,
3514 p_cenvat_rg_flag => p_cenvat_rg_flag,
3515 p_cenvat_claimed_ptg => ln_cenvat_claimed_ptg,
3516 p_cenvat_rg_message => p_cenvat_rg_message,
3517 p_process_vat_flag => lv_process_vat_flag,
3518 p_process_vat_message => lv_process_vat_message,
3519 /*Vijay Shankar for Bug#4250171 p_process_vat_flag => null,
3520 p_process_vat_message => null,
3521 */p_process_date => SYSDATE
3522 );
3523
3524 END IF;
3525 <<exit_processing>>
3526
3527 FND_FILE.put_line(FND_FILE.log, '$End of Trx:'||p_transaction_id||'. Time:'||to_char(SYSDATE,'dd/mm/yyyy hh24:mi:ss'));
3528
3529 p_codepath := jai_general_pkg.plot_codepath(99, p_codepath, null, 'END');
3530
3531 EXCEPTION
3532 WHEN OTHERS THEN
3533 p_common_err_mesg := 'RECEIPT_TRANSACTIONS_PKG.process_transaction(StmtId:'||lv_statement_id||'). Error:'||SQLERRM;
3534 FND_FILE.put_line( FND_FILE.log, 'Error in '||p_common_err_mesg);
3535 IF p_process_flag <> 'Y' THEN
3536 p_process_flag := 'E';
3537 p_process_message := p_common_err_mesg;
3538 ELSIF p_cenvat_rg_flag <> 'Y' THEN
3539 p_cenvat_rg_flag := 'E';
3540 p_cenvat_rg_message := p_common_err_mesg;
3541 ELSE
3542 -- dont update any of the fields of JAI_RCV_TRANSACTIONS table
3543 NULL;
3544 END IF;
3545 p_codepath := jai_general_pkg.plot_codepath(999, p_codepath, null, 'END');
3546
3547 END process_transaction;
3548
3549 /* ~~~~~~~~~~~~~~~~~~~~ POPULATION of DETAILS Procedure ~~~~~~~~~~~~~~~~~~~~~~~~*/
3550
3551 PROCEDURE populate_details(
3552 p_transaction_id IN NUMBER,
3553 p_process_status OUT NOCOPY VARCHAR2,
3554 p_process_message OUT NOCOPY VARCHAR2,
3555 p_simulate_flag IN VARCHAR2,
3556 p_codepath IN OUT NOCOPY VARCHAR2
3557 ) IS
3558
3559 CURSOR c_shp_line_dtls(cp_shipment_line_id IN NUMBER) IS
3560 SELECT shipment_line_id, item_id
3561 FROM rcv_shipment_lines
3562 WHERE shipment_line_id = cp_shipment_line_id;
3563
3564 CURSOR c_shp_hdr_dtls(cp_shipment_header_id IN NUMBER) IS
3565 SELECT shipment_header_id, receipt_num
3566 FROM rcv_shipment_headers
3567 WHERE shipment_header_id = cp_shipment_header_id;
3568
3569 CURSOR c_loc_item_dtls(cp_organization_id IN NUMBER, cp_inventory_item_id IN NUMBER) IS
3570 SELECT item_class, modvat_flag, excise_flag, item_trading_flag
3571 FROM JAI_INV_ITM_SETUPS
3572 WHERE organization_id = cp_organization_id
3573 AND inventory_item_id = cp_inventory_item_id;
3574
3575 CURSOR c_base_item_dtls(cp_organization_id IN NUMBER, cp_inventory_item_id IN NUMBER) IS
3576 SELECT inventory_item_flag, inventory_asset_flag
3577 FROM mtl_system_items
3578 WHERE organization_id = cp_organization_id
3579 AND inventory_item_id = cp_inventory_item_id;
3580
3581 CURSOR c_loc_orgn_dtls(cp_organization_id IN NUMBER) IS
3582 SELECT decode( manufacturing, 'Y', 'M', decode(trading, 'Y', 'T', 'X')) organization_type, excise_in_rg23d
3583 FROM JAI_CMN_INVENTORY_ORGS
3584 WHERE organization_id = cp_organization_id
3585 AND location_id = 0;
3586
3587 CURSOR c_mtl_params(cp_organization_id IN NUMBER) IS
3588 SELECT primary_cost_method
3589 FROM mtl_parameters
3590 WHERE organization_id = cp_organization_id;
3591
3592 CURSOR c_inv_org_linked_to_location(cp_location_id IN NUMBER) IS
3593 SELECT nvl(inventory_organization_id, -99999) inventory_organization_id
3594 FROM hr_locations_all
3595 WHERE location_id = cp_location_id;
3596
3597 CURSOR c_loc_linked_to_org_subinv(cp_organization_id IN NUMBER, cp_subinventory IN VARCHAR2) IS
3598 SELECT location_id, decode(bonded, 'Y', 'B', decode(trading, 'Y', 'T', 'N')) loc_subinventory_type
3599 FROM JAI_INV_SUBINV_DTLS
3600 WHERE organization_id = cp_organization_id
3601 AND sub_inventory_name = cp_subinventory;
3602
3603 CURSOR c_base_subinv_dtls(cp_organization_id IN NUMBER, cp_subinventory IN VARCHAR2) IS
3604 SELECT asset_inventory
3605 FROM mtl_secondary_inventories
3606 WHERE organization_id = cp_organization_id
3607 AND secondary_inventory_name = cp_subinventory;
3608
3609 -- porting from Bug#3949109 (3927371)
3610 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
3611 SELECT subinventory
3612 FROM rcv_transactions
3613 WHERE shipment_line_id = cp_shipment_line_id
3614 AND parent_transaction_id = cp_receive_trx_id
3615 AND transaction_type = cp_transaction_type --'DELIVER'
3616 AND transaction_id > cp_receive_trx_id;
3617
3618 -- Start, Vijay Shankar for Bug#3940588
3619 ln_tax_transaction_id JAI_RCV_TRANSACTIONS.tax_transaction_id%TYPE;
3620 ln_non_po_vendor_cnt NUMBER;
3621 lv_third_party_flag VARCHAR2(1);
3622
3623 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*/
3624 SELECT nvl(count(distinct vendor_id), 0)
3625 from JAI_RCV_LINE_TAXES
3626 where shipment_line_id = cp_shipment_line_id
3627 AND vendor_id <> (SELECT vendor_id
3628 FROM rcv_shipment_headers
3629 WHERE shipment_header_id = cp_shipment_header_id
3630 )
3631 AND upper(tax_type) NOT IN (jai_constants.tax_type_tds, jai_constants.tax_type_modvat_recovery) --('TDS', 'MODVAT RECOVERY')
3632 AND tax_amount > 0 ;
3633 -- End, Vijay Shankar for Bug#3940588
3634 /*
3635 || srjayara bug 5064235 -- forward porting for bug# 5054114
3636 || Cursor fetches the subinventory from rcv_transactiosn for DELIVER line
3637 || based on shipment_line_id of transaction_id passed to the cursor
3638 */
3639 CURSOR cur_dlry_subinv ( cp_transaction_id IN NUMBER ) IS
3640 SELECT subinventory
3641 FROM rcv_transactions
3642 WHERE transaction_type = 'DELIVER'
3643 AND shipment_line_id = (SELECT shipment_line_id
3644 FROM jai_rcv_transactions
3645 WHERE transaction_id = cp_transaction_id);
3646
3647 r_base_trx c_base_trx%ROWTYPE;
3648 r_parent_base_trx c_base_trx%ROWTYPE;
3649
3650 -- Vijay Shankar for Bug#4038024
3651 lv_required_trx_type RCV_TRANSACTIONS.transaction_type%TYPE;
3652
3653 r_ancestor_dtls c_base_trx%ROWTYPE;
3654 r_shp_line_dtls c_shp_line_dtls%ROWTYPE;
3655 r_shp_hdr_dtls c_shp_hdr_dtls%ROWTYPE;
3656 r_loc_item_dtls c_loc_item_dtls%ROWTYPE;
3657 r_base_item_dtls c_base_item_dtls%ROWTYPE;
3658 r_loc_orgn_dtls c_loc_orgn_dtls%ROWTYPE;
3659 r_mtl_params c_mtl_params%ROWTYPE;
3660 r_base_subinv_dtls c_base_subinv_dtls%ROWTYPE;
3661 r_subinv_dtls c_loc_linked_to_org_subinv%ROWTYPE;
3662
3663
3664 ln_location_id NUMBER(15);
3665 ln_organization_id NUMBER(15);
3666 lv_subinventory RCV_TRANSACTIONS.subinventory%TYPE;
3667 lv_transaction_type RCV_TRANSACTIONS.transaction_type%TYPE;
3668 ln_ancestor_trxn_id NUMBER(15);
3669
3670 ln_tax_amount NUMBER;
3671 ln_cenvat_amount NUMBER;
3672 lv_loc_subinv_type JAI_RCV_TRANSACTIONS.loc_subinv_type%TYPE;
3673 lv_base_subinv_asset_flag JAI_RCV_TRANSACTIONS.BASE_ASSET_INVENTORY%TYPE;
3674
3675 r_exc_inv_no c_excise_invoice_no%ROWTYPE;
3676
3677 /* Vijay Shankar for Bug#4250171 */
3678 r_trx c_trx%ROWTYPE;
3679 r_parent_trx c_trx%ROWTYPE; -- JAI_RCV_TRANSACTIONS record
3680
3681 /* Added by Ramananda for bug#4407165 */
3682 lv_object_name CONSTANT VARCHAR2(61) := 'jai_rcv_trx_processing_pkg.populate_details';
3683 ln_tax_apportion_factor jai_rcv_transactions.tax_apportion_factor%TYPE; --Added by Sanjikum for Bug#4495135
3684
3685 BEGIN
3686
3687 IF lb_debug THEN
3688 FND_FILE.put_line(FND_FILE.log, '^ POPULATE_DETAILS');
3689 END IF;
3690
3691 p_codepath := jai_general_pkg.plot_codepath(1, p_codepath, 'populate_details', 'START');
3692
3693 OPEN c_base_trx(p_transaction_id);
3694 FETCH c_base_trx INTO r_base_trx;
3695 CLOSE c_base_trx;
3696
3697 /* Vijay Shankar for Bug#4250171 */
3698 OPEN c_trx(p_transaction_id);
3699 FETCH c_trx INTO r_trx;
3700 CLOSE c_trx;
3701
3702 IF r_base_trx.parent_transaction_id > 0 THEN
3703 p_codepath := jai_general_pkg.plot_codepath(2, p_codepath);
3704 OPEN c_base_trx(r_base_trx.parent_transaction_id);
3705 FETCH c_base_trx INTO r_parent_base_trx;
3706 CLOSE c_base_trx;
3707 END IF;
3708
3709 OPEN c_shp_line_dtls(r_base_trx.shipment_line_id);
3710 FETCH c_shp_line_dtls INTO r_shp_line_dtls;
3711 CLOSE c_shp_line_dtls;
3712
3713 OPEN c_shp_hdr_dtls(r_base_trx.shipment_header_id);
3714 FETCH c_shp_hdr_dtls INTO r_shp_hdr_dtls;
3715 CLOSE c_shp_hdr_dtls;
3716
3717 OPEN c_loc_item_dtls(r_base_trx.organization_id, r_shp_line_dtls.item_id);
3718 FETCH c_loc_item_dtls INTO r_loc_item_dtls;
3719 CLOSE c_loc_item_dtls;
3720
3721 OPEN c_base_item_dtls(r_base_trx.organization_id, r_shp_line_dtls.item_id);
3722 FETCH c_base_item_dtls INTO r_base_item_dtls;
3723 CLOSE c_base_item_dtls;
3724
3725 OPEN c_loc_orgn_dtls(r_base_trx.organization_id);
3726 FETCH c_loc_orgn_dtls INTO r_loc_orgn_dtls;
3727 CLOSE c_loc_orgn_dtls;
3728
3729 OPEN c_mtl_params(r_base_trx.organization_id);
3730 FETCH c_mtl_params INTO r_mtl_params;
3731 CLOSE c_mtl_params;
3732
3733 /* following if condition added as part of DFF elimination
3734 Vijay Shankar for Bug#4346453. RCV DFF Elim. Enh. */
3735 if lv_transaction_type <> 'RETURN TO VENDOR' then
3736 OPEN c_excise_invoice_no(r_base_trx.shipment_line_id);
3737 FETCH c_excise_invoice_no INTO r_exc_inv_no;
3738 CLOSE c_excise_invoice_no;
3739 end if;
3740
3741 IF r_base_trx.transaction_type = 'CORRECT' THEN
3742 p_codepath := jai_general_pkg.plot_codepath(3, p_codepath);
3743 lv_transaction_type := r_parent_base_trx.transaction_type;
3744 ELSE
3745 p_codepath := jai_general_pkg.plot_codepath(4, p_codepath);
3746 lv_transaction_type := r_base_trx.transaction_type;
3747 END IF;
3748
3749 -- Vijay Shankar for Bug#3940588. RECEIPTS DEPLUG
3750 -- this is coded to support UNORDERED Transactions by localization in new receipts code. Shall chk the impact
3751 IF r_base_trx.transaction_type = 'MATCH' THEN
3752 lv_transaction_type := 'RECEIVE';
3753 UPDATE JAI_RCV_TRANSACTIONS
3754 SET transaction_type = lv_transaction_type
3755 WHERE transaction_id = p_transaction_id;
3756 END IF;
3757
3758 /* Vijay Shankar for Bug#4250171 */
3759 /* following if condition added to OPM for VAT Functionality */
3760 -- IF r_trx.attribute_category in (OPM_RECEIPT,OPM_RETURNS) THEN 13364465
3761 IF r_trx.trx_information in (OPM_RECEIPT,OPM_RETURNS) THEN
3762 IF lv_transaction_type = 'RETURN TO VENDOR' THEN
3763 OPEN c_trx(r_trx.parent_transaction_id);
3764 FETCH c_trx INTO r_parent_trx;
3765 CLOSE c_trx;
3766
3767 ln_location_id := r_parent_trx.location_id;
3768 lv_subinventory := r_base_trx.subinventory;
3769 ELSE
3770 ln_location_id := r_trx.location_id;
3771 lv_subinventory := r_base_trx.subinventory;
3772 END IF;
3773
3774 -- if both location and subinventory are NULL then goto the parent type i.e RTV to RECEIVE and RTR to DELIVER
3775 ELSIF nvl(r_base_trx.location_id, 0) = 0 AND nvl(r_base_trx.subinventory, '-XX') = '-XX' THEN
3776
3777 p_codepath := jai_general_pkg.plot_codepath(5, p_codepath);
3778 -- following condition added by Vijay Shankar for Bug#4038024. Incase of CORRECT transactions, if location and subinventory
3779 -- are not present, then we need to look at parent transaction for location. this will mostly happen for DIRECT DELIVERY case
3780 IF lv_transaction_type IN ('RETURN TO RECEIVING', 'RETURN TO VENDOR')
3781 OR (r_base_trx.transaction_type = 'CORRECT' AND r_parent_base_trx.transaction_type IN ('RECEIVE', 'DELIVER')) -- BUG#3949502. (3927371)
3782 THEN
3783
3784 p_codepath := jai_general_pkg.plot_codepath(6, p_codepath);
3785 OPEN c_trx(r_base_trx.parent_transaction_id);
3786 FETCH c_trx INTO r_parent_trx;
3787 CLOSE c_trx;
3788
3789 ln_location_id := r_parent_trx.location_id;
3790
3791 -- following IF condition added as part of porting from Bug#3949109 (3927371)
3792 -- Incase of Direct Delivery RECEIVE transaction may not have both the location and subinventory. In this case we need to fetch the
3793 -- subinventory from DELIVER transaction
3794 ELSIF lv_transaction_type = 'RECEIVE' AND r_base_trx.routing_header_id = 3 THEN -- this will not execute for correct transactions
3795 p_codepath := jai_general_pkg.plot_codepath(7, p_codepath);
3796 OPEN c_dlry_subinventory(r_base_trx.shipment_line_id, p_transaction_id, 'DELIVER');
3797 FETCH c_dlry_subinventory INTO lv_subinventory;
3798 CLOSE c_dlry_subinventory;
3799
3800 END IF;
3801
3802 IF (lv_transaction_type in ('RETURN TO RECEIVING', 'DELIVER') AND nvl(lv_subinventory,'-XX')='-XX')
3803 OR (lv_transaction_type in ('RETURN TO VENDOR', 'RECEIVE') AND nvl(ln_location_id,0)=0 AND nvl(lv_subinventory,'-XX')='-XX' )
3804 THEN
3805
3806 p_codepath := jai_general_pkg.plot_codepath(8, p_codepath);
3807 IF lv_transaction_type IN ('DELIVER', 'RETURN TO RECEIVING') THEN
3808 lv_required_trx_type := 'DELIVER';
3809 ELSIF lv_transaction_type IN ('RECEIVE', 'RETURN TO VENDOR') THEN
3810 lv_required_trx_type := 'RECEIVE';
3811 END IF;
3812
3813 ln_ancestor_trxn_id := get_ancestor_id(
3814 p_transaction_id => p_transaction_id,
3815 p_shipment_line_id => r_base_trx.shipment_line_id,
3816 p_required_trx_type => lv_required_trx_type
3817 );
3818
3819 p_codepath := jai_general_pkg.plot_codepath(9, p_codepath);
3820 IF ln_ancestor_trxn_id IS NOT NULL THEN
3821 p_codepath := jai_general_pkg.plot_codepath(10, p_codepath);
3822 OPEN c_base_trx(ln_ancestor_trxn_id);
3823 FETCH c_base_trx INTO r_ancestor_dtls;
3824 CLOSE c_base_trx;
3825
3826 ln_location_id := r_ancestor_dtls.location_id;
3827 lv_subinventory := r_ancestor_dtls.subinventory;
3828 END IF;
3829
3830 END IF;
3831
3832 ELSE
3833 p_codepath := jai_general_pkg.plot_codepath(11, p_codepath);
3834 ln_location_id := r_base_trx.location_id;
3835 lv_subinventory := r_base_trx.subinventory;
3836 END IF;
3837
3838 /* added this end if to support VAT Func for OPM . Vijay Shankar for Bug#4250171*/
3839 IF nvl(r_trx.attribute_category, 'XXXX') NOT IN (OPM_RECEIPT, OPM_RETURNS) THEN
3840 /*
3841 || srjayara bug 5064235 -- forward porting for bug# 5054114
3842 || Added if condition .. end if to populate the subinventory from DELIVER line
3843 || in rcv_transactions
3844 */
3845 IF lv_subinventory IS NULL THEN
3846 OPEN cur_dlry_subinv ( p_transaction_id );
3847 FETCH cur_dlry_subinv INTO lv_subinventory ;
3848 CLOSE cur_dlry_subinv ;
3849 END IF ;
3850
3851
3852 IF lv_subinventory IS NOT NULL THEN
3853 p_codepath := jai_general_pkg.plot_codepath(12, p_codepath);
3854 OPEN c_loc_linked_to_org_subinv(r_base_trx.organization_id, lv_subinventory);
3855 FETCH c_loc_linked_to_org_subinv INTO r_subinv_dtls;
3856 CLOSE c_loc_linked_to_org_subinv;
3857
3858 OPEN c_base_subinv_dtls(r_base_trx.organization_id, lv_subinventory);
3859 FETCH c_base_subinv_dtls INTO r_base_subinv_dtls;
3860 CLOSE c_base_subinv_dtls;
3861
3862 IF (nvl(ln_location_id,0) = 0
3863 /* following condition added by Vijay Shankar for Bug#4278511 to take care of ISO Scenario */
3864 /* Bug 4589354. Added by Lakshmi Gopalsami.
3865 Commented the following condition.
3866 OR (r_base_trx.source_document_code = 'REQ' and */
3867 OR nvl(r_subinv_dtls.location_id, 0) <> 0 )
3868 THEN
3869 p_codepath := jai_general_pkg.plot_codepath(13, p_codepath);
3870 ln_location_id := r_subinv_dtls.location_id;
3871 END IF;
3872
3873 lv_loc_subinv_type := r_subinv_dtls.loc_subinventory_type;
3874 lv_base_subinv_asset_flag := r_base_subinv_dtls.asset_inventory;
3875
3876 END IF;
3877
3878 -- IF nvl(ln_location_id, 0) = 0 THEN
3879 IF nvl(ln_location_id, 0) <> 0 THEN
3880 p_codepath := jai_general_pkg.plot_codepath(14, p_codepath);
3881 -- OPEN c_inv_org_linked_to_location(r_base_trx.organization_id);
3882 OPEN c_inv_org_linked_to_location(ln_location_id);
3883 FETCH c_inv_org_linked_to_location INTO ln_organization_id;
3884 CLOSE c_inv_org_linked_to_location;
3885
3886 IF r_base_trx.organization_id <> ln_organization_id THEN
3887 p_codepath := jai_general_pkg.plot_codepath(15, p_codepath);
3888 ln_location_id := 0;
3889 END IF;
3890 END IF;
3891
3892 END IF; /* added this end if to support VAT Func for OPM . Vijay Shankar for Bug#4250171*/
3893
3894 /* Bug 4586752. Added by Lakshmi Gopalsami
3895 * Assigned the value of location_id to the local variable */
3896 IF NVL(ln_location_id,0) = 0 THEN
3897 ln_location_id := r_base_trx.location_id;
3898 END IF;
3899
3900 /*Added by nprashar for bug # 10155836*/
3901 If r_base_trx.currency_code = jai_general_pkg.INDIAN_CURRENCY
3902 and (r_base_trx.currency_conversion_rate is NULL or r_base_trx.currency_conversion_rate = 0)
3903 Then
3904 r_base_trx.currency_conversion_rate := 1;
3905 --Bug 12543504 - Get Currency Conversion Rate from GL based on Conversion Type and Receipt Transaction Date for Foreign Currency
3906 --Added ELSIF conditions as this GL API call is resulting in -ve currency conversion rate # 13823908
3907 ELSIF r_base_trx.currency_code <> jai_general_pkg.INDIAN_CURRENCY --13823908
3908 and (r_base_trx.currency_conversion_rate is NULL or r_base_trx.currency_conversion_rate = 0)
3909 Then
3910
3911 r_base_trx.currency_conversion_rate := gl_currency_api.get_rate_sql (
3912 x_from_currency => r_base_trx.currency_code,
3913 x_to_currency => 'INR',
3914 x_conversion_date => r_base_trx.transaction_date,
3915 x_conversion_type => r_base_trx.currency_conversion_type );
3916 /*Bug 12543504 - End*/
3917 End If;
3918
3919 p_codepath := jai_general_pkg.plot_codepath(16, p_codepath);
3920 ln_tax_amount := get_trxn_tax_amount(
3921 p_transaction_id => p_transaction_id,
3922 p_shipment_line_id => r_base_trx.shipment_line_id,
3923 p_curr_conv_rate => r_base_trx.currency_conversion_rate,
3924 p_return_in_inr_curr => jai_constants.yes --File.Sql.35 Cbabu
3925 );
3926
3927 ln_cenvat_amount := get_trxn_cenvat_amount(
3928 p_transaction_id => p_transaction_id,
3929 p_shipment_line_id => r_base_trx.shipment_line_id,
3930 p_organization_type => r_loc_orgn_dtls.organization_type,
3931 p_curr_conv_rate => r_base_trx.currency_conversion_rate
3932 );
3933
3934 --Start, added by Vijay Shankar for Bug#3940588
3935 ln_tax_transaction_id := get_ancestor_id(
3936 p_transaction_id => p_transaction_id,
3937 p_shipment_line_id => r_base_trx.shipment_line_id,
3938 p_required_trx_type => 'RECEIVE'
3939 );
3940
3941 -- Third Party invoice flag should be set only at LINE level which is first transaction of Receipt
3942 -- i.e RECEIVE or MATCH or CORRECT of RECEIVE
3943 IF lv_transaction_type IN ('RECEIVE', 'MATCH') THEN
3944 OPEN c_non_po_vendor_cnt(r_base_trx.shipment_header_id, r_base_trx.shipment_line_id);
3945 FETCH c_non_po_vendor_cnt INTO ln_non_po_vendor_cnt;
3946 CLOSE c_non_po_vendor_cnt;
3947
3948 IF ln_non_po_vendor_cnt > 0 THEN
3949 lv_third_party_flag := 'N';
3950 ELSE
3951 lv_third_party_flag := 'X';
3952 END IF;
3953 ELSE
3954 lv_third_party_flag := 'X';
3955 END IF;
3956 -- End, added by Vijay Shankar for Bug#3940588
3957
3958 IF lb_debug THEN
3959 FND_FILE.put_line(FND_FILE.log, '... RecNum:'||r_shp_hdr_dtls.receipt_num ||',p_cenvat_amount:'||ln_cenvat_amount );
3960 END IF;
3961
3962 p_codepath := jai_general_pkg.plot_codepath(17, p_codepath);
3963 jai_rcv_transactions_pkg.update_row(
3964 p_transaction_id => p_transaction_id,
3965 p_parent_transaction_type => r_parent_base_trx.transaction_type,
3966 p_receipt_num => r_shp_hdr_dtls.receipt_num,
3967 p_inventory_item_id => r_shp_line_dtls.item_id,
3968 p_item_class => nvl(r_loc_item_dtls.item_class, NO_ITEM_CLASS),
3969 p_item_cenvatable => nvl(r_loc_item_dtls.modvat_flag, NO_SETUP),
3970 p_item_excisable => nvl(r_loc_item_dtls.excise_flag, NO_SETUP),
3971 p_item_trading_flag => nvl(r_loc_item_dtls.item_trading_flag, NO_SETUP),
3972 p_inv_item_flag => nvl(r_base_item_dtls.inventory_item_flag, 'N'),
3973 p_inv_asset_flag => r_base_item_dtls.inventory_asset_flag,
3974 p_location_id => nvl(ln_location_id, 0),
3975 p_loc_subinv_type => nvl(lv_loc_subinv_type, NO_SETUP),
3976 p_base_subinv_asset_flag => lv_base_subinv_asset_flag,
3977 p_organization_type => r_loc_orgn_dtls.organization_type,
3978 p_excise_in_trading => nvl(r_loc_orgn_dtls.excise_in_rg23d, 'N'),
3979 p_costing_method => r_mtl_params.primary_cost_method,
3980 p_boe_applied_flag => NULL,
3981 p_third_party_flag => lv_third_party_flag, -- Vijay Shankar for Bug#3940588
3982 p_tax_amount => ln_tax_amount,
3983 p_cenvat_amount => ln_cenvat_amount,
3984 p_currency_conversion_rate => r_base_trx.currency_conversion_rate, /*Bug 12543504 - Update Conversion Rate in JAI_RCV_TRANSACTIONS*/
3985 p_excise_invoice_no => r_exc_inv_no.excise_invoice_no,
3986 p_excise_invoice_date => r_exc_inv_no.excise_invoice_date,
3987 p_tax_transaction_id => ln_tax_transaction_id, -- Vijay Shankar for Bug#3940588
3988 p_assessable_value => NULL -- This needs to be populated during Tax Calculation itself
3989 );
3990
3991
3992 p_codepath := jai_general_pkg.plot_codepath(18, p_codepath);
3993
3994 --Start Added by Sanjikum for Bug#4495135
3995 ln_tax_apportion_factor := get_apportion_factor(p_transaction_id);
3996
3997 p_codepath := jai_general_pkg.plot_codepath(19, p_codepath);
3998 --This update can't be merged with the above, as get_apportion_factor uses the column tax_transaction_id
3999 --which is updated only in the above update
4000
4001 jai_rcv_transactions_pkg.update_row(
4002 p_transaction_id => p_transaction_id,
4003 p_tax_apportion_factor => ln_tax_apportion_factor
4004 );
4005 --End Added by Sanjikum for Bug#4495135
4006
4007 p_codepath := jai_general_pkg.plot_codepath(20, p_codepath, 'populate_details', 'END');
4008
4009 IF lb_debug THEN
4010 FND_FILE.put_line(FND_FILE.log, '$ POPULATE_DETAILS');
4011 END IF;
4012
4013 /* Added by Ramananda for bug#4407165 */
4014 EXCEPTION
4015 WHEN OTHERS THEN
4016 p_process_status := null;
4017 p_process_message := null;
4018 FND_MESSAGE.SET_NAME('JA','JAI_EXCEPTION_OCCURED');
4019 FND_MESSAGE.SET_TOKEN('JAI_PROCESS_MSG', lv_object_name ||'. Err:'||sqlerrm );
4020 app_exception.raise_exception;
4021
4022 END populate_details;
4023
4024 /* ~~~~~~~~~~~~~~~~~~~~ TRANSACTION VALIDATION Procedure ~~~~~~~~~~~~~~~~~~~~~~~~*/
4025
4026 PROCEDURE validate_transaction (
4027 p_transaction_id IN NUMBER,
4028 p_process_flag IN OUT NOCOPY VARCHAR2,
4029 p_process_message IN OUT NOCOPY VARCHAR2,
4030 p_cenvat_rg_flag IN OUT NOCOPY VARCHAR2,
4031 p_cenvat_rg_message IN OUT NOCOPY VARCHAR2,
4032 /* following two flags introduced by Vijay Shankar for Bug#4250236(4245089). VAT Implementation */
4033 p_process_vat_flag IN OUT NOCOPY VARCHAR2,
4034 p_process_vat_message IN OUT NOCOPY VARCHAR2,
4035 p_called_from IN VARCHAR2,
4036 p_simulate_flag IN VARCHAR2,
4037 p_codepath IN OUT NOCOPY VARCHAR2
4038 ) IS
4039
4040 /*---------------------------------------------
4041 Functionality of this Procedure :
4042 0. If some Scenario has to be Supported/UnSupported by Localization, then this needs to be changed as everything is
4043 driven based on Flags set in this procedure
4044 1. This Procedure is to set Process Flags or cenvat RG Flag in JAI_RCV_TRANSACTIONS.
4045 Possible Values :
4046 ===================
4047 X - Not Applicable (All values prefexing X means variations of Non Applicability with exact problem)
4048 P - Pending for Parent transaction to be Processed
4049 N - To be Processed
4050 E - Errored out - Populate Error Message during Transaction Processing
4051 Y - Already Processed
4052 O - Others - Populate Information Message.
4053 C - Entire Quantity Correction has been perfomed after Claim
4054 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
4055 -------------------
4056 XT - Indicates that Cenvat Entries cant be passed as there is a Change of Month between parent and this transaction
4057
4058 OPEN ISSUES:
4059 - Third Party Flag needs to be Updated if CORRECTion transactions has to uptake the TP functionality or Whole Receipts
4060 functionality is moved into the this NEW RECEIPTS CODE
4061 - Assessable value needs to be populated
4062
4063 ---------------------------------------------*/
4064
4065 CURSOR c_trx(cp_transaction_id IN NUMBER) IS
4066 SELECT *
4067 FROM JAI_RCV_TRANSACTIONS
4068 WHERE transaction_id = cp_transaction_id;
4069
4070 CURSOR c_receipt_line_dtls(cp_shipment_line_id JAI_RCV_TRANSACTIONS.shipment_line_id%type) is
4071 SELECT excise_invoice_no, excise_invoice_date, online_claim_flag,
4072 claim_modvat_flag, nvl(rma_type, 'XXXX') rma_type, receipt_num --Added receipt_num for bug #16197556
4073 FROM JAI_RCV_LINES
4074 WHERE shipment_line_id = cp_shipment_line_id;
4075
4076 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*/
4077 SELECT count(1) total_cnt,
4078 sum( decode(upper(tax_type), 'EXCISE', 1,
4079 'ADDL. EXCISE', 1,
4080 'OTHER EXCISE', 1,
4081 'CVD', 1,
4082 jai_constants.tax_type_add_cvd,1,
4083 -- Modified by SACSETHI Bug# 5228046
4084 -- Forward porting the change in 11i bug 5365523
4085 -- (Additional CVD Enhancement) as part of the R12 bug 5228046
4086 jai_constants.tax_type_exc_edu_cess,1,
4087 jai_constants.tax_type_cvd_edu_cess,1,jai_constants.tax_type_sh_exc_edu_cess,1,
4088 jai_constants.tax_type_sh_cvd_edu_cess,1, 0) --kunkumar for bugno5989740 -- Vijay Shankar for Bug#3940588 EDU CESS
4089 ) excise_cnt
4090 FROM JAI_RCV_LINE_TAXES
4091 WHERE shipment_line_id = cp_shipment_line_id
4092 AND tax_type not in (jai_constants.tax_type_tds, jai_constants.tax_type_modvat_recovery); --('TDS', 'MODVAT RECOVERY')
4093
4094 CURSOR c_excise_tax_count(cp_shipment_line_id JAI_RCV_TRANSACTIONS.shipment_line_id%type) is
4095 SELECT count(1)
4096 FROM JAI_RCV_LINE_TAXES
4097 WHERE shipment_line_id = cp_shipment_line_id
4098 -- AND tax_type NOT IN ('TDS','Modvat Recovery')
4099 AND modvat_flag = jai_constants.yes
4100 AND upper(tax_type) IN ( 'EXCISE',
4101 'ADDL. EXCISE',
4102 'OTHER EXCISE',
4103 'CVD',
4104 jai_constants.tax_type_add_cvd ,
4105 -- Modified by SACSETHI Bug# 5228046
4106 -- Forward porting the change in 11i bug 5365523
4107 -- (Additional CVD Enhancement) as part of the R12 bug 5228046
4108 jai_constants.tax_type_exc_edu_cess,
4109 jai_constants.tax_type_cvd_edu_cess,jai_constants.tax_type_sh_exc_edu_cess,
4110 jai_constants.tax_type_sh_cvd_edu_cess);--Added by kunkumar for bugno5989740 -- Vijay Shankar for Bug#3940588 EDU CESS
4111
4112
4113 ln_recoverable_vat_tax_cnt NUMBER;
4114 CURSOR c_recoverable_vat_tax_cnt(cp_shipment_line_id number, cp_regime_code varchar2) is
4115 SELECT count(1)
4116 FROM JAI_RCV_LINE_TAXES
4117 WHERE shipment_line_id = cp_shipment_line_id
4118 AND modvat_flag = jai_constants.yes
4119 AND tax_type IN
4120 ( select tax_type from jai_regime_tax_types_v /*modified this to use View as part of OPM changes */
4121 where regime_code = cp_regime_code
4122 );
4123
4124 CURSOR c_acct_count(cp_parent_transaction_id JAI_RCV_TRANSACTIONS.transaction_id%type,
4125 cp_parent_transaction_type JAI_RCV_TRANSACTIONS.parent_transaction_type%type
4126 ) IS
4127 SELECT count(1)
4128 FROM JAI_RCV_JOURNAL_ENTRIES
4129 WHERE transaction_id = cp_parent_transaction_id
4130 AND transaction_type = cp_parent_transaction_type;
4131
4132 ld_parent_rg_entry_date DATE;
4133 CURSOR c_rg_count(cp_parent_transaction_id JAI_RCV_TRANSACTIONS.transaction_id%type,
4134 cp_organization_id JAI_RCV_TRANSACTIONS.organization_id%type
4135 ) IS
4136 SELECT creation_date
4137 FROM (SELECT creation_date
4138 FROM JAI_CMN_RG_23AC_II_TRXS
4139 WHERE receipt_ref = cp_parent_transaction_id
4140 AND organization_id = cp_organization_id
4141 AND transaction_source_num = 18
4142 UNION
4143 SELECT creation_date
4144 FROM JAI_CMN_RG_PLA_TRXS
4145 WHERE ref_document_id = cp_parent_transaction_id
4146 AND organization_id = cp_organization_id
4147 AND transaction_source_num = 19);
4148
4149 CURSOR c_parent_rg23d_entry(
4150 cp_parent_transaction_id JAI_RCV_TRANSACTIONS.transaction_id%type,
4151 cp_organization_id JAI_RCV_TRANSACTIONS.organization_id%type
4152 ) IS
4153 SELECT creation_date
4154 FROM JAI_CMN_RG_23D_TRXS
4155 WHERE receipt_ref = cp_parent_transaction_id
4156 AND organization_id = cp_organization_id
4157 AND transaction_source_num = 18;
4158
4159 CURSOR c_receipt_cenvat_dtl(cp_transaction_id IN NUMBER, cp_organization_type IN VARCHAR2) IS
4160 SELECT decode(cp_organization_type, 'M', online_claim_flag, jai_constants.yes) online_claim_flag, -- Changed by Vijay Shankar for Bug #4172424
4161 cenvat_claimed_ptg, cenvat_claimed_amt, unclaim_cenvat_flag, cenvat_amount
4162 FROM JAI_RCV_CENVAT_CLAIMS
4163 WHERE transaction_id = cp_transaction_id;
4164
4165 /*following cursor added for bug 8538155 (FP for bug 8466620)*/
4166 cursor c_get_dest(p_line_location_id number, p_distribution_id number) is
4167 select destination_type_code
4168 from po_distributions_all
4169 where line_location_id = p_line_location_id
4170 and po_distribution_id = nvl(p_distribution_id, po_distribution_id);
4171
4172 /*Cursor is checking for Non Recoverable taxes
4173 with modvat flag as 'N' also mod_cr_percentage can be NULL
4174 or less than 100*/
4175 Cursor c_check_non_recover_taxes (p_shipment_line_id number) is /*Added by nprashar for bug # 9767315*/
4176 select 1 from dual
4177 Where Exists (
4178 select 1 from jai_rcv_line_taxes jrlt, jai_cmn_taxes_all jcta
4179 where jrlt.tax_id = jcta.tax_id
4180 and jrlt.shipment_line_id = p_shipment_line_id
4181 and (jrlt.modvat_flag = 'N' or (jrlt.modvat_flag='Y' and nvl(jcta.mod_cr_percentage,0)<100)));
4182
4183
4184 r_trx c_trx%ROWTYPE;
4185 r_parent_trx c_trx%ROWTYPE;
4186 r_base_trx c_base_trx%ROWTYPE;
4187 r_receipt_line c_receipt_line_dtls%ROWTYPE;
4188 r_receipt_cenvat_dtl c_receipt_cenvat_dtl%ROWTYPE;
4189 r_taxes c_taxes%ROWTYPE;
4190 r_exc_inv_no c_excise_invoice_no%ROWTYPE;
4191
4192 lv_statement_id VARCHAR2(5);
4193
4194 lv_transaction_type JAI_RCV_TRANSACTIONS.transaction_type%TYPE;
4195 lv_include_cenvat_in_cost VARCHAR2(5);
4196
4197 ln_excise_tax_count NUMBER; --File.Sql.35 Cbabu := 0;
4198 ln_account_count NUMBER; --File.Sql.35 Cbabu := 0;
4199 ln_rg_count NUMBER; --File.Sql.35 Cbabu := 0;
4200 ln_rtv_cnt NUMBER; --File.Sql.35 Cbabu := 0;
4201
4202 lb_process_iso BOOLEAN;
4203
4204 -- Vijay Shankar for Bug#4346453. RCV DFF Elim. Enh.
4205 lv_excise_inv_gen_action VARCHAR2(50);
4206 lv_vat_inv_gen_action VARCHAR2(50);
4207
4208 lv_qty_upd_event VARCHAR2(30); --added by ssawant
4209
4210 lv_codepath JAI_RCV_TRANSACTIONS.codepath%TYPE; --File.Sql.35 := '';
4211 lv_dest_code VARCHAR2(20); /*added for bug 8538155 (FP for bug 8466620)*/
4212 ln_check_non_recover_tax NUMBER; /*Added by nprashar for bug # 9767315 */
4213 lv_exp_del_message varchar2(200);
4214
4215 lv_exp_del_flag varchar2(1);
4216
4217 /*Start additions by vkavulur - Bug#16197556 .*/
4218
4219 CURSOR rcp_cur (c_shipment_line_id NUMBER ,c_transaction_type VARCHAR2) IS
4220 SELECT sum(amount) amt
4221 FROM JAI_CMN_BOE_MATCHINGS
4222 WHERE shipment_line_id = c_shipment_line_id
4223 AND c_transaction_type = 'RECEIVE';
4224
4225
4226 CURSOR ship_cur (c_shipment_line_id NUMBER) IS
4227 SELECT tax_amount,currency
4228 FROM jai_Rcv_line_taxes
4229 WHERE shipment_line_id = c_shipment_line_id
4230 AND upper(tax_type) IN (jai_constants.tax_type_cvd, jai_constants.tax_type_add_cvd, UPPER(jai_constants.tax_type_customs),
4231 jai_constants.tax_type_cvd_edu_cess, jai_constants.tax_type_customs_edu_cess,jai_constants.tax_type_sh_cvd_edu_cess,
4232 jai_constants.tax_type_sh_customs_edu_Cess);
4233
4234 v_ship_rec ship_cur % ROWTYPE;
4235
4236 CURSOR fetch_sob_id(cp_org_id NUMBER) IS
4237 SELECT Set_Of_Books_Id
4238 FROM Org_Organization_Definitions
4239 WHERE Organization_Id = cp_org_id;
4240
4241 v_conv_factor rcv_transactions.currency_conversion_rate%TYPE;
4242 v_tax_sum JAI_CMN_BOE_MATCHINGS.amount%TYPE := 0;
4243 v_applied_amt JAI_CMN_BOE_MATCHINGS.amount%TYPE;
4244 v_sob_id org_organization_definitions.set_of_books_id%TYPE;
4245
4246 /*End additions by vkavulur - Bug#16197556 .*/
4247
4248
4249 BEGIN
4250
4251 ln_excise_tax_count := 0;
4252 ln_account_count := 0;
4253 ln_rg_count := 0;
4254 ln_rtv_cnt := 0;
4255 lv_codepath := '';
4256
4257 lv_codepath := jai_general_pkg.plot_codepath(1, lv_codepath, 'cenvat_rg_pkg.validate_trx', 'START');
4258 IF lb_debug THEN
4259 FND_FILE.put_line(FND_FILE.log, '^VALIDATE_TRANSACTION. Prc_Flag->'||p_process_flag||', Cen_Flag->'||p_cenvat_rg_flag);
4260 END IF;
4261
4262 IF p_process_flag IS NULL THEN
4263 p_process_message := NULL;
4264 END IF;
4265
4266 IF p_cenvat_rg_flag IS NULL THEN
4267 p_cenvat_rg_message := NULL;
4268 END IF;
4269
4270 lv_statement_id := '1';
4271 OPEN c_trx(p_transaction_id);
4272 FETCH c_trx into r_trx;
4273 CLOSE c_trx;
4274
4275 --start additions by vkaranam for bug#13110424
4276 open c_trx(r_trx.parent_transaction_id);
4277 fetch c_trx into r_parent_trx;
4278 close c_trx;
4279 --end additions by vkaranam for bug#13110424
4280
4281 OPEN c_base_trx(p_transaction_id);
4282 FETCH c_base_trx into r_base_trx;
4283 CLOSE c_base_trx;
4284
4285 /* Fetch all the Information from ja_rcv_lines*/
4286 OPEN c_receipt_line_dtls(r_trx.shipment_line_id);
4287 FETCH c_receipt_line_dtls into r_receipt_line;
4288 CLOSE c_receipt_line_dtls;
4289
4290 /*Start additions by vkavulur - Bug#16197556 .*/
4291
4292 OPEN fetch_sob_id(r_base_trx.organization_id);
4293 FETCH fetch_sob_id INTO v_sob_id;
4294 CLOSE fetch_sob_id;
4295
4296 OPEN ship_cur (r_base_trx.shipment_line_id);
4297 LOOP
4298 FETCH ship_cur INTO v_ship_rec;
4299 EXIT WHEN ship_cur % NOTFOUND;
4300
4301 FND_FILE.put_line(FND_FILE.log, 'r_base_trx.currency_code ->'||r_base_trx.currency_code);
4302
4303 v_conv_factor := nvl(r_base_trx.currency_conversion_rate,1);
4304
4305 FND_FILE.put_line(FND_FILE.log, 'v_conv_factor ->'||v_conv_factor);
4306
4307 FND_FILE.put_line(FND_FILE.log, '2. v_ship_rec.tax_amount ->'||v_ship_rec.tax_amount||'v_conv_factor - '||v_conv_factor);
4308
4309 v_tax_sum := NVL(v_tax_sum, 0) + NVL(v_ship_rec.tax_amount * v_conv_factor, 0);
4310
4311 END LOOP;
4312 CLOSE ship_cur;
4313
4314 FND_FILE.put_line(FND_FILE.log, 'v_tax_sum ->'||v_tax_sum);
4315
4316 IF NVL(v_tax_sum, 0) > 0
4317 THEN
4318
4319 OPEN rcp_cur (r_base_trx.shipment_line_id,r_base_trx.transaction_type);
4320 FETCH rcp_cur INTO v_applied_amt;
4321 CLOSE rcp_cur;
4322
4323 v_applied_amt := NVL(ROUND(v_applied_amt, 2),0);
4324
4325 FND_FILE.put_line(FND_FILE.log, 'v_applied_amt ->'||v_applied_amt);
4326
4327 IF ROUND(NVL(v_tax_sum, 0), 2) > NVL(v_applied_amt, 0) and r_base_trx.transaction_type = 'RECEIVE'
4328 Then
4329 p_process_message := 'BOE is not Completely applied for Receipt'||r_receipt_line.receipt_num;
4330 p_process_flag := 'E';
4331 p_cenvat_rg_flag := 'X';
4332 p_cenvat_rg_message :=' BOE is not Completely applied for Receipt'||r_receipt_line.receipt_num;
4333 FND_FILE.put_line(FND_FILE.log, 'Error '||p_process_message);
4334 GOTO end_of_cenvat_flag_validation;
4335 END IF;
4336
4337 END IF;
4338
4339 /*End additions by vkavulur - Bug#16197556 .*/
4340
4341 /* Vijay Shankar for Bug#4250171. following added to support OPM Functionality for VAT Processing */
4342 IF p_called_from = CALLED_FROM_OPM
4343 --OR r_trx.attribute_category in (OPM_RECEIPT, OPM_RETURNS) 13364465
4344 OR r_trx.trx_information in (OPM_RECEIPT, OPM_RETURNS)
4345 THEN
4346 lv_codepath := jai_general_pkg.plot_codepath(3, lv_codepath);
4347 p_process_flag := 'X';
4348 p_process_message := 'This Processing not required for OPM Transaction';
4349 p_cenvat_rg_flag := 'X';
4350 p_cenvat_rg_message := 'This Processing not required for OPM Transaction';
4351 GOTO end_of_cenvat_flag_validation;
4352 END IF;
4353
4354 -- this contains the details of RECEIVE/MATCH transaction type
4355 OPEN c_receipt_cenvat_dtl(r_trx.tax_transaction_id,
4356 r_trx.organization_type); -- Changed by Vijay Shankar for Bug #4172424
4357 FETCH c_receipt_cenvat_dtl into r_receipt_cenvat_dtl;
4358 CLOSE c_receipt_cenvat_dtl;
4359
4360 /*Shifted this code to the beginning of the procedure--vkavulur Bug#16197556 */
4361 /*OPEN c_base_trx(p_transaction_id);
4362 FETCH c_base_trx into r_base_trx;
4363 CLOSE c_base_trx; */
4364
4365 lv_statement_id := '2';
4366 IF r_trx.transaction_type = 'CORRECT' THEN
4367 lv_transaction_type := r_trx.parent_transaction_type;
4368 ELSE
4369 lv_transaction_type := r_trx.transaction_type;
4370 END IF;
4371
4372 lv_statement_id := '3';
4373 /* Fetch all the Information from JAI_RCV_LINES*/
4374 /*Shifted this code to the beginning of the procedure--vkavulur Bug#16197556 */
4375 /*OPEN c_receipt_line_dtls(r_trx.shipment_line_id);
4376 FETCH c_receipt_line_dtls into r_receipt_line;
4377 CLOSE c_receipt_line_dtls;*/
4378
4379 --FND_FILE.put_line(fnd_file.log, 'Shp_lineId:'||r_trx.shipment_line_id||', Cnt:'||SQL%ROWCOUNT
4380 -- ||', Cnt:'||SQL%ROWCOUNT||', exNo:'||r_receipt_line.excise_invoice_no);
4381
4382 lv_statement_id := '4';
4383 /* Fetch the Tax count */
4384 OPEN c_taxes(r_trx.shipment_line_id);
4385 FETCH c_taxes into r_taxes;
4386 CLOSE c_taxes;
4387
4388 lv_codepath := jai_general_pkg.plot_codepath(2, lv_codepath);
4389 lb_process_iso := process_iso_transaction(
4390 p_transaction_id => r_trx.transaction_id,
4391 p_shipment_line_id => r_trx.shipment_line_id
4392 );
4393
4394 ------------------- Process Flag Validation -------------------
4395 /* Process Flag is set to 'O' when some columns which are required are null or some specific scenarios */
4396
4397 -- values other than 'Y' are added by Vijay Shankar for Bug#3940588. RECEIPTS DEPLUG
4398 IF p_process_flag IN ('Y', 'X', 'O', 'XT') THEN
4399 lv_codepath := jai_general_pkg.plot_codepath(3, lv_codepath);
4400 goto end_of_process_flag_validation;
4401 END IF;
4402
4403
4404 /*bgowrava for forward porting Bug#5756676..start*/
4405
4406 lv_online_qty_flag := 'N';
4407
4408 OPEN cur_qty_setup(r_trx.organization_id,r_trx.location_id);
4409 FETCH cur_qty_setup INTO lv_qty_upd_event;
4410 IF lv_qty_upd_event IS NULL THEN
4411 CLOSE cur_qty_setup;
4412 OPEN cur_qty_setup(r_trx.organization_id,0);
4413 FETCH cur_qty_setup INTO lv_qty_upd_event;
4414 END IF;
4415 CLOSE cur_qty_setup;
4416
4417 FND_file.put_line( fnd_file.log, 'Quantity Update Event:'||lv_qty_upd_event);
4418
4419 OPEN cur_item_excise_flag(r_trx.organization_id,r_trx.inventory_item_id );
4420 FETCH cur_item_excise_flag INTO lv_excise_flag;
4421 CLOSE cur_item_excise_flag;
4422
4423 FND_file.put_line( fnd_file.log, 'Item Excisable:'||lv_excise_flag);
4424 FND_file.put_line( fnd_file.log, 'Excisable Taxes Count:'||r_taxes.excise_cnt);
4425
4426 FND_file.put_line( fnd_file.log, 'Transaction Type:'||lv_transaction_type);
4427
4428 /*Bug 8538155 (FP for bug 8466620) - Do not hit the quantity register when there are no recoverable
4429 * taxes and delivery is to expense location. Reason - when there are no recoverable taxes, the entry
4430 * will not be reversed during deliver.*/
4431 lv_dest_code := null;
4432 open c_get_dest(r_base_trx.po_line_location_id, r_base_trx.po_distribution_id);
4433 fetch c_get_dest into lv_dest_code;
4434 close c_get_dest;
4435
4436 IF r_trx.organization_type = MFG_ORGN AND nvl(lv_dest_code,'XXX') <> 'EXPENSE' THEN
4437 --ADDED excise flag condition for bug#14346451
4438 IF lv_excise_flag='Y' then
4439 IF ( ( nvl(lv_qty_upd_event,'X') = 'RECEIVE' AND lv_transaction_type in ('RECEIVE','MATCH') AND nvl(r_trx.quantity_register_flag,'N') = 'N' )
4440 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*/
4441 /*bug 8319304 - FP of bugs 6914674 and 8314743 - added the following OR*/
4442 OR ( lv_excise_flag = 'Y' AND r_taxes.excise_cnt>0 AND lv_transaction_type in ('RECEIVE','MATCH','RETURN TO VENDOR') AND nvl(r_receipt_cenvat_dtl.cenvat_amount,0) = 0)
4443 OR ( lv_transaction_type = 'DELIVER' AND NVL(r_trx.loc_subinv_type,'Z') = 'N' AND NVL(r_parent_trx.quantity_register_flag,'N') = 'Y') --Added qty flag validation for bug#13110424 /* Added for bug #12818250 by vkavulur */
4444 ) THEN
4445
4446 lv_online_qty_flag := 'Y';
4447
4448 FND_file.put_line( fnd_file.log, 'Quantity Register would be hit independent of Amount register');
4449
4450 END IF;
4451 else
4452 lv_online_qty_flag := 'N';
4453
4454 FND_file.put_line( fnd_file.log, 'Quantity Register will not hit as the item is not excisable');
4455 end if; --14346451
4456 END IF;
4457
4458 /*bgowrava for forward porting Bug#5756676..end*/
4459
4460 /* 1PROCESS_FLAG. START of PROCESS_FLAG BASIC VALIDATIONS */
4461 lv_statement_id := '6';
4462 if r_trx.organization_id is null then
4463 lv_codepath := jai_general_pkg.plot_codepath(3, lv_codepath);
4464 p_process_flag := 'O';
4465 p_process_message := jai_rcv_trx_processing_pkg.get_message('NO-ORG');
4466 goto end_of_process_flag_validation;
4467 end if;
4468
4469 lv_statement_id := '7';
4470 -- following condition added by Vijay Shankar for Bug#3940588
4471 -- Common Checks between process_flag and cenvat_rg_flag
4472 if lv_transaction_type = 'RETURN TO VENDOR'
4473 AND (r_base_trx.source_document_code='PO' AND r_base_trx.po_header_id IS NULL)
4474 then
4475 lv_codepath := jai_general_pkg.plot_codepath(4, lv_codepath);
4476 p_process_flag := 'X';
4477 p_process_message := 'RTV against Unordered Receipt will not be processed';
4478 goto end_of_process_flag_validation;
4479 end if;
4480 /* 1PROCESS_FLAG. END of PROCESS_FLAG BASIC VALIDATIONS */
4481
4482 /* 2PROCESS_FLAG. START of TRANSACTION VALIDATIONS To SEE WHETHER IT IS QUALIFIED w.r.t NON CENVAT TAXES(PROCESS_FLAG)*/
4483
4484 lv_statement_id := '10';
4485 if r_taxes.total_cnt = 0 then
4486 lv_codepath := jai_general_pkg.plot_codepath(5, lv_codepath);
4487 p_process_flag := 'X';
4488 p_process_message := jai_rcv_trx_processing_pkg.get_message('NO-TAXES');
4489 goto end_of_process_flag_validation;
4490 end if;
4491
4492 lv_statement_id := '11';
4493 -- Vijay Shankar for Bug#4346453. RCV DFF Elim. Enh. IF r_base_trx.attr_cat = 'India RMA Receipt' THEN
4494 if r_base_trx.source_document_code = source_rma then
4495 lv_codepath := jai_general_pkg.plot_codepath(6, lv_codepath);
4496 IF ( lv_transaction_type IN ('RECEIVE', 'RETURN TO VENDOR')
4497 -- Vijay Shankar for Bug#4346453. RCV DFF Elim. Enh. AND UPPER(r_base_trx.rma_type) NOT IN ('SCRAP')
4498 AND r_receipt_line.rma_type NOT IN ('SCRAP')
4499 )
4500 OR lv_transaction_type IN ('DELIVER', 'RETURN TO RECEIVING')
4501 THEN
4502 lv_codepath := jai_general_pkg.plot_codepath(7, lv_codepath);
4503 --Added the below by rchandan for Bug#6030615
4504 OPEN c_trx(p_transaction_id);
4505 FETCH c_trx into r_trx;
4506 CLOSE c_trx;
4507 p_process_flag := 'X';
4508 p_process_message := 'RMA Processing Not Required';
4509 goto end_of_process_flag_validation;
4510
4511 ELSIF lv_transaction_type IN ('RECEIVE', 'RETURN TO VENDOR')
4512 -- Vijay Shankar for Bug#4346453. RCV DFF Elim. Enh. AND UPPER(r_base_trx.rma_type) IN ('SCRAP')
4513 AND r_receipt_line.rma_type IN ('SCRAP')
4514 AND r_taxes.excise_cnt = 0
4515 THEN
4516 lv_codepath := jai_general_pkg.plot_codepath(8, lv_codepath);
4517 p_process_flag := 'X';
4518 p_process_message := 'Excise Taxes donot exist in this RMA transaction';
4519 goto end_of_process_flag_validation;
4520 END IF;
4521
4522 END IF;
4523
4524 lv_statement_id := '12';
4525 IF NOT lb_process_iso THEN
4526 lv_codepath := jai_general_pkg.plot_codepath(9, lv_codepath);
4527 p_process_flag := 'X';
4528 p_process_message := 'ISO Processing Not Required';
4529 goto end_of_process_flag_validation;
4530 END IF;
4531 /* 2PROCESS_FLAG. END of TRANSACTION VALIDATIONS To SEE WHETHER IT IS QUALIFIED w.r.t NON CENVAT TAXES(PROCESS_FLAG)*/
4532
4533 /* 3PROCESS_FLAG. START of TRANSACTION VALIDATIONS To SEE WHETHER IT IS PENDING FOR SOMETHING INSPITE OF BEING QUALIFIED for PROCESSING*/
4534 lv_statement_id := '13';
4535 --Ensures that if the Parent lines Accounting is not done, then the Accounting for this Line
4536 --would also be deferred.
4537 if r_trx.transaction_type = 'CORRECT' AND r_trx.parent_transaction_type in ('DELIVER','RETURN TO RECEIVING') then
4538 lv_codepath := jai_general_pkg.plot_codepath(10, lv_codepath);
4539 /* Fetch the Accounting of the parent transaction line */
4540 OPEN c_acct_count(r_trx.parent_transaction_id, r_trx.parent_transaction_type);
4541 FETCH c_acct_count into ln_account_count;
4542 CLOSE c_acct_count;
4543
4544
4545 OPEN c_check_non_recover_taxes(r_trx.shipment_line_id); /*Added by nprashar for bug #9767315 */
4546 FETCH c_check_non_recover_taxes into ln_check_non_recover_tax;
4547 CLOSE c_check_non_recover_taxes;
4548
4549 If nvl(ln_check_non_recover_tax,0) = 1 Then /*Added by nprashar for bug # 9767315*/
4550 if ln_account_count = 0 then
4551 lv_codepath := jai_general_pkg.plot_codepath(11, lv_codepath);
4552 p_process_flag := 'P';
4553 p_process_message := jai_rcv_trx_processing_pkg.get_message('NO-BASE-ACCT');
4554 goto end_of_process_flag_validation;
4555 end if;
4556 end if;
4557 end if;
4558 /* 3PROCESS_FLAG. END of TRANSACTION VALIDATIONS To SEE WHETHER IT IS PENDING FOR SOMETHING INSPITE OF BEING QUALIFIED for PROCESSING*/
4559
4560 p_process_flag := 'N';
4561
4562 <<end_of_process_flag_validation>>
4563
4564 ------------------- Cenvat RG Flag Validation Block -------------------
4565 /* Cenvat Flag is set to 'O' when some columns which are required are null or some specific scenarios */
4566
4567 lv_statement_id := '14';
4568 -- values other than 'Y' are added by Vijay Shankar for Bug#3940588. RECEIPTS DEPLUG
4569 IF p_cenvat_rg_flag IN ('Y', 'X', 'O', 'XT') THEN
4570 lv_codepath := jai_general_pkg.plot_codepath(12, lv_codepath);
4571 goto end_of_cenvat_flag_validation;
4572 END IF;
4573
4574 -- following condition is false if the call to this procedure happened from JAINMVAT (i.e deferred Claim Scree)
4575 FND_file.put_line( fnd_file.log, '54321 ttype:'||r_trx.transaction_type||',exno:'||r_receipt_line.excise_invoice_no
4576 ||',exda:'||r_receipt_line.excise_invoice_date||',onClFlg:'||r_receipt_cenvat_dtl.online_claim_flag
4577 ||',CenAmt:'||r_receipt_cenvat_dtl.cenvat_amount||',calFrm:'||p_called_from);
4578
4579 lv_statement_id := '15';
4580 /* 1CENVAT_RG_FLAG. START of CENVAT_RG_FLAG BASIC VALIDATIONS */
4581 if r_trx.organization_id is null then
4582 lv_codepath := jai_general_pkg.plot_codepath(13, lv_codepath);
4583 p_cenvat_rg_flag := 'O';
4584 p_cenvat_rg_message := jai_rcv_trx_processing_pkg.get_message('NO-ORG');
4585
4586 goto end_of_cenvat_flag_validation;
4587 end if;
4588
4589 lv_statement_id := '16';
4590 if r_trx.location_id = 0 then
4591 lv_codepath := jai_general_pkg.plot_codepath(14, lv_codepath);
4592 p_cenvat_rg_flag := 'O';
4593 p_process_flag :='E' ; /*Added by nprashar for bug # 9864327*/
4594 p_cenvat_rg_message := jai_rcv_trx_processing_pkg.get_message('NO-LOC-ORG-SETUP');
4595 goto end_of_cenvat_flag_validation;
4596 end if;
4597 /* 1CENVAT_RG_FLAG. END of CENVAT_RG_FLAG BASIC VALIDATIONS */
4598
4599 /* 2CENVAT_RG_FLAG. START of TRANSACTION VALIDATIONS To SEE WHETHER IT IS QUALIFIED w.r.t NON CENVAT TAXES(CENVAT_RG_FLAG)*/
4600 lv_statement_id := '17';
4601 if lv_transaction_type = 'RETURN TO VENDOR'
4602 AND (r_base_trx.source_document_code='PO' AND r_base_trx.po_header_id IS NULL)
4603 then
4604 lv_codepath := jai_general_pkg.plot_codepath(15, lv_codepath);
4605 p_cenvat_rg_flag := 'X';
4606 p_cenvat_rg_message := 'RTV against Unordered Receipt will not be processed';
4607 goto end_of_cenvat_flag_validation;
4608 end if;
4609
4610 lv_statement_id := '18';
4611 if r_taxes.total_cnt = 0 then
4612 lv_codepath := jai_general_pkg.plot_codepath(16, lv_codepath);
4613 p_cenvat_rg_flag := 'X';
4614 p_cenvat_rg_message := jai_rcv_trx_processing_pkg.get_message('NO-TAXES');
4615 goto end_of_cenvat_flag_validation;
4616 end if;
4617
4618 lv_statement_id := '19';
4619 if r_trx.transaction_type NOT IN ('RECEIVE', 'MATCH')
4620 AND r_receipt_cenvat_dtl.unclaim_cenvat_flag = 'Y'
4621 then
4622 lv_codepath := jai_general_pkg.plot_codepath(17, lv_codepath);
4623 p_cenvat_rg_flag := 'X';
4624 p_cenvat_rg_message := 'Parent is not eligible for Cenvat Claim';
4625 goto end_of_cenvat_flag_validation;
4626 end if;
4627
4628 /*this condition is changed for an observation made while fixing bug 8538155 (FP for bug 8466620).
4629 * when delivery is to expense and total recoverable cenvat amount is zero with
4630 * excise tax lines being present, then the amount register is updated with zero
4631 * amount during deliver. This behavior is wrong, as per inputs from PM*/
4632 --added r_trx.organization_type='M' for bug#7595016
4633 if r_taxes.excise_cnt = 0 or (r_trx.organization_type='M' and r_receipt_cenvat_dtl.cenvat_amount = 0 ) then
4634 lv_codepath := jai_general_pkg.plot_codepath(18, lv_codepath);
4635 p_cenvat_rg_flag := 'X';
4636 p_cenvat_rg_message := 'Excise Taxes do not exist';
4637 goto end_of_cenvat_flag_validation;
4638 end if;
4639
4640 lv_statement_id := '20';
4641 open c_excise_tax_count(r_trx.shipment_line_id);
4642 fetch c_excise_tax_count into ln_excise_tax_count;
4643 close c_excise_tax_count;
4644
4645 lv_statement_id := '21';
4646 --added the organization_type for Trading in the IF clause for bug#9019561
4647 --Reverted back the changes done for bug #9019561 for fixing bug #14503581
4648 if r_trx.organization_type in ('M') AND ln_excise_tax_count = 0 then
4649 lv_codepath := jai_general_pkg.plot_codepath(19, lv_codepath);
4650 p_cenvat_rg_flag := 'X';
4651 p_cenvat_rg_message := jai_rcv_trx_processing_pkg.get_message('NO-EXCISE-TAXES');
4652 goto end_of_cenvat_flag_validation;
4653 end if;
4654
4655 if r_trx.organization_type = 'T' AND lv_transaction_type IN ('DELIVER', 'RETURN TO RECEIVING') then
4656 lv_codepath := jai_general_pkg.plot_codepath(20, lv_codepath);
4657 p_cenvat_rg_flag := 'X';
4658 p_cenvat_rg_message := 'No Cenvat/RG Entries are passed for '||lv_transaction_type;
4659 goto end_of_cenvat_flag_validation;
4660 end if;
4661
4662 lv_statement_id := '22';
4663 if lv_transaction_type = 'RETURN TO VENDOR' then
4664
4665 lv_codepath := jai_general_pkg.plot_codepath(21, lv_codepath);
4666 if r_trx.transaction_type = 'CORRECT' THEN
4667 lv_statement_id := '23';
4668 SELECT count(1) INTO ln_rtv_cnt
4669 FROM JAI_RCV_RTV_DTLS
4670 WHERE transaction_id = r_trx.parent_transaction_id;
4671
4672 lv_statement_id := '24';
4673 if ln_rtv_cnt = 0 THEN
4674 lv_codepath := jai_general_pkg.plot_codepath(22, lv_codepath);
4675 p_cenvat_rg_flag := 'X';
4676 p_cenvat_rg_message := 'Parent RTV Transaction doesnt have Excise Invoice';
4677 goto end_of_cenvat_flag_validation;
4678 end if;
4679
4680 -- Vijay Shankar for Bug#4346453. RCV DFF Elim. Enh. elsif r_base_trx.generate_excise_invoice = 'N' then
4681 -- elsif r_trx.attribute1 in ( INV_GEN_STATUS_INV_NA ) then 13364465
4682 elsif r_trx.excise_inv_gen_status in ( INV_GEN_STATUS_INV_NA ) then
4683 --pramasub FP start IProc
4684 /*
4685 || Start Changes by ssumaith - Iprocurement Bug#4281841.
4686 || Check if the return is created from Iproc.
4687 */
4688 OPEN check_rcpt_source(r_base_Trx.po_line_location_id);
4689 FETCH check_rcpt_source INTO lv_apps_source_code;
4690 CLOSE check_rcpt_source;
4691
4692 IF NVL(lv_apps_source_code,'$$') <> 'POR' THEN
4693 /*
4694 || The above if was added by ssumaith for Iprocurement Bug#4281841.
4695 */
4696 --pramasub FP end IProc
4697 lv_codepath := jai_general_pkg.plot_codepath(23, lv_codepath);
4698 -- lv_excise_inv_gen_action := r_trx.attribute1; -- Vijay Shankar for Bug#4346453. RCV DFF Elim. Enh. 13364465
4699 lv_excise_inv_gen_action := r_trx.excise_inv_gen_status;
4700 p_cenvat_rg_flag := 'X';
4701 -- Vijay Shankar for Bug#4346453. RCV DFF Elim. Enh. p_cenvat_rg_message := 'DFF Value for Generate Excise Invoice is not given as ''Y''';
4702 p_cenvat_rg_message := 'Excise invoice not required';
4703 goto end_of_cenvat_flag_validation;
4704 --pramasub FP Iporc start
4705 ELSE
4706 IF r_trx.cenvat_Rg_status = 'P' or r_trx.process_status = 'P' or r_Trx.process_vat_status = 'P' THEN
4707 /*
4708 It means that the user has intentionally set the process action to N in the Iproc IL returns page.
4709 We are setting the values as to be processed as it can then be picked up and processed.
4710 */
4711 p_cenvat_rg_flag := 'X';
4712 --p_cenvat_rg_message := 'DFF Value for Generate Excise Invoice is not given as ''Y''';
4713 --commented out the above line as the message is changed for the bug#4346453 by cbabu | pramasub FP IProc
4714 p_cenvat_rg_message := 'Excise invoice not required';
4715 GOTO end_of_cenvat_flag_validation;
4716 ELSE
4717 /*
4718 || It means initially the flags were some value other than O and we are flagging it to process it later.
4719 */
4720 --lv_codepath := ja_in_general_pkg.plot_codepath(23.1, lv_codepath);
4721 p_cenvat_rg_flag := 'O';
4722 p_cenvat_rg_message := 'Call From Iprocurement returns page';
4723 p_process_flag := 'O';
4724 p_process_vat_flag := 'O';
4725 GOTO end_of_cenvat_flag_validation;
4726 END IF;
4727 END IF;
4728 /*
4729 || Above end if added by ssumaith - Iprocurement Bug#4281841 to handle returns.
4730 */
4731 --pramasub FP Iporc end
4732 end if;
4733 end if;
4734
4735 lv_statement_id := '25';
4736 if r_trx.organization_type = 'T' then
4737 if r_trx.item_trading_flag <> 'Y' then
4738 lv_codepath := jai_general_pkg.plot_codepath(24, lv_codepath);
4739 p_cenvat_rg_flag := 'O';
4740 p_cenvat_rg_message := jai_rcv_trx_processing_pkg.get_message('ITEM-TRADING-NO');
4741 goto end_of_cenvat_flag_validation;
4742 elsif r_trx.item_excisable <> 'Y' then
4743 lv_codepath := jai_general_pkg.plot_codepath(25, lv_codepath);
4744 p_cenvat_rg_flag := 'O';
4745 p_cenvat_rg_message := 'Trading Item is not Excisable';
4746 goto end_of_cenvat_flag_validation;
4747 end if;
4748 end if;
4749
4750 lv_statement_id := '26';
4751 if r_trx.item_class in ('OTIN','OTEX') then
4752 lv_codepath := jai_general_pkg.plot_codepath(26, lv_codepath);
4753 p_cenvat_rg_flag := 'O';
4754 p_cenvat_rg_message := jai_rcv_trx_processing_pkg.get_message('ITEM-CLASS-OTIN');
4755 goto end_of_cenvat_flag_validation;
4756 elsif r_trx.item_class NOT IN ('RMIN', 'RMEX', 'CGIN', 'CGEX', 'CCIN', 'CCEX', 'FGIN', 'FGEX') then
4757 lv_codepath := jai_general_pkg.plot_codepath(27, lv_codepath);
4758 p_cenvat_rg_flag := 'O';
4759 p_cenvat_rg_message := jai_rcv_trx_processing_pkg.get_message('ITEM-CLASS-NULL');
4760 goto end_of_cenvat_flag_validation;
4761 end if;
4762
4763 lv_statement_id := '27';
4764 /* following condition modified by Vijay Shankar for Bug#4179823 */
4765 -- if r_base_trx.source_document_code <> 'REQ' and r_trx.item_class in ('FGIN', 'FGEX')
4766 -- if r_base_trx.source_document_code <> 'RMA' and r_trx.item_class in ('FGIN', 'FGEX')
4767 --commented the above and added the below by rchandan for Bug#6030615
4768 if r_base_trx.source_document_code NOT IN ('RMA','INVENTORY','REQ') and r_trx.item_class in ('FGIN', 'FGEX') --added 'REQ' for bug #13514510
4769 and r_trx.organization_type = 'M' -- Changed by Vijay Shanker for Bug #4172424
4770 then
4771 p_codepath := jai_general_pkg.plot_codepath(28, p_codepath);
4772 p_cenvat_rg_flag := 'X';
4773 p_cenvat_rg_message := 'Cenvat Accounting not supported for FGIN Items';
4774 goto end_of_cenvat_flag_validation;
4775 end if;
4776
4777 lv_statement_id := '28';
4778 if r_trx.organization_type ='M' and r_trx.item_cenvatable = 'N' then
4779 lv_codepath := jai_general_pkg.plot_codepath(29, lv_codepath);
4780 p_cenvat_rg_flag := 'O';
4781 p_cenvat_rg_message := jai_rcv_trx_processing_pkg.get_message('ITEM-CENVATABLE-NO');
4782 goto end_of_cenvat_flag_validation;
4783 end if;
4784
4785 lv_statement_id := '29';
4786 /*
4787 ||Start of bug 5378630
4788 ||Modified the below if statement condition such that the RMA_type 'GOODS RETURN' was changed into GOODS RETURN
4789 */
4790
4791 IF r_base_trx.source_document_code = SOURCE_RMA
4792 AND r_receipt_line.rma_type NOT IN ('PRODUCTION INPUT', 'GOODS RETURN')
4793 THEN
4794 /* ENd of bug 5378630 */
4795 lv_codepath := jai_general_pkg.plot_codepath(30, lv_codepath);
4796 p_cenvat_rg_flag := 'X';
4797 p_cenvat_rg_message := 'RMA Processing Not Required';
4798 goto end_of_cenvat_flag_validation;
4799 END IF;
4800
4801 /* Fetch whether RG has been hit */
4802 if r_trx.transaction_type = 'CORRECT' then
4803
4804 lv_statement_id := '30';
4805 lv_codepath := jai_general_pkg.plot_codepath(31, lv_codepath);
4806 if r_trx.parent_transaction_type in ('RECEIVE', 'RETURN TO VENDOR') then
4807
4808 lv_codepath := jai_general_pkg.plot_codepath(32, lv_codepath);
4809 if r_trx.organization_type = 'M' then
4810 lv_statement_id := '32';
4811 open c_rg_count(r_trx.parent_transaction_id, r_trx.organization_id);
4812 -- fetch c_rg_count into ln_rg_count;
4813 fetch c_rg_count into ld_parent_rg_entry_date;
4814 close c_rg_count;
4815 else -- Trading Check
4816 lv_statement_id := '33';
4817 open c_parent_rg23d_entry(r_trx.parent_transaction_id, r_trx.organization_id);
4818 fetch c_parent_rg23d_entry into ld_parent_rg_entry_date;
4819 close c_parent_rg23d_entry;
4820 end if;
4821
4822 --Commented for bug #15907740
4823 /* if to_char(ld_parent_rg_entry_date, 'YYYYMM') <> to_char(SYSDATE, 'YYYYMM') THEN
4824 lv_codepath := jai_general_pkg.plot_codepath(33, lv_codepath);
4825 p_cenvat_rg_flag := 'XT';
4826 p_cenvat_rg_message := jai_rcv_trx_processing_pkg.get_message('BASE-MONTH-DIFFERENT');
4827 goto end_of_cenvat_flag_validation;
4828 end if; */
4829
4830 else
4831
4832 lv_statement_id := '35';
4833 lv_codepath := jai_general_pkg.plot_codepath(34, lv_codepath);
4834 OPEN c_trx(r_trx.parent_transaction_id);
4835 FETCH c_trx into r_parent_trx;
4836 CLOSE c_trx;
4837
4838 --Commented for bug #15907740
4839 /* if to_char(r_trx.transaction_date, 'YYYYMM') <> to_char(r_parent_trx.transaction_date, 'YYYYMM') THEN
4840 lv_codepath := jai_general_pkg.plot_codepath(35, lv_codepath);
4841 p_cenvat_rg_flag := 'XT';
4842 p_cenvat_rg_message := jai_rcv_trx_processing_pkg.get_message('BASE-MONTH-DIFFERENT');
4843 goto end_of_cenvat_flag_validation;
4844 end if; */
4845 end if;
4846 end if;
4847
4848 -- we dont do validation for ISO in case of trading because RG23D Entry should be passed even if accounting is not required
4849 lv_statement_id := '36';
4850 IF NOT lb_process_iso AND r_trx.organization_type = 'M' THEN
4851 lv_codepath := jai_general_pkg.plot_codepath(36, lv_codepath);
4852 p_cenvat_rg_flag := 'X';
4853 p_cenvat_rg_message := 'ISO Processing Not Required';
4854 goto end_of_cenvat_flag_validation;
4855 END IF;
4856
4857 IF lv_transaction_type IN ('DELIVER', 'RETURN TO RECEIVING') THEN
4858
4859 lv_codepath := jai_general_pkg.plot_codepath(37, lv_codepath);
4860 lv_statement_id := '37';
4861
4862 -- this call is specific to DELIVER and RTR transactions
4863 lv_include_cenvat_in_cost := jai_rcv_deliver_rtr_pkg.include_cenvat_in_costing(
4864 p_transaction_id => p_transaction_id,
4865 p_process_message => p_cenvat_rg_flag,
4866 p_process_status => p_cenvat_rg_message,
4867 p_codepath => lv_codepath
4868 );
4869
4870 lv_statement_id := '38';
4871 /*bug 10335708 - even if cenvat is not included in costing, quantity register should be updated
4872 *for DELIVER / RTR when destination type is EXPENSE. So modified the condition below, and also
4873 *added an ELSIF part to handle this case.*/
4874
4875 IF lv_include_cenvat_in_cost = 'N' AND lv_dest_code <> 'EXPENSE' AND r_trx.transaction_type <> 'CORRECT' AND lv_excise_flag = 'Y' AND r_taxes.excise_cnt > 0 --Added for bug #14852106
4876 THEN
4877 lv_codepath := jaI_general_pkg.plot_codepath(38, lv_codepath);
4878 p_cenvat_rg_flag := 'X';
4879 p_cenvat_rg_message := 'Cenvat Entries not Applicable for transaction type';
4880 goto end_of_cenvat_flag_validation;
4881 ELSIF lv_include_cenvat_in_cost = 'N' AND lv_dest_code = 'EXPENSE' THEN
4882 JAI_RCV_DELIVER_RTR_PKG.pr_issue_expense_delivery(p_transaction_id, lv_exp_del_message, lv_exp_del_flag, lv_codepath);
4883 p_cenvat_rg_flag := nvl(lv_exp_del_flag, p_cenvat_rg_flag);
4884 p_cenvat_rg_message := nvl(lv_exp_del_message, p_cenvat_rg_message); /*bug 9230069*/
4885 END IF;
4886
4887
4888 END IF;
4889
4890 /* 2CENVAT_RG_FLAG. END of TRANSACTION VALIDATIONS To SEE WHETHER IT IS QUALIFIED w.r.t NON CENVAT TAXES(CENVAT_RG_FLAG)*/
4891
4892 /* 3CENVAT_RG_FLAG. START of TRANSACTION VALIDATIONS To SEE WHETHER IT IS PENDING FOR SOMETHING INSPITE OF BEING QUALIFIED for PROCESSING*/
4893 lv_statement_id := '39';
4894 if r_trx.transaction_type = 'CORRECT' AND r_trx.parent_transaction_type in ('RECEIVE', 'RETURN TO VENDOR') then
4895 if ld_parent_rg_entry_date IS NULL then
4896 lv_codepath := jai_general_pkg.plot_codepath(39, lv_codepath);
4897 p_cenvat_rg_flag := 'P';
4898 p_cenvat_rg_message := jai_rcv_trx_processing_pkg.get_message('NO-BASE-RG');
4899 goto end_of_cenvat_flag_validation;
4900 end if;
4901 end if;
4902
4903 lv_statement_id := '40';
4904 IF r_trx.transaction_type IN ('RECEIVE', 'MATCH')
4905 AND ( r_receipt_line.excise_invoice_no IS NULL
4906 OR r_receipt_line.excise_invoice_date IS NULL
4907 OR ( nvl(r_receipt_cenvat_dtl.online_claim_flag, 'N') = 'N'
4908 AND nvl(r_receipt_cenvat_dtl.cenvat_amount,0) <> 0
4909 AND p_called_from<>'JAINMVAT'
4910 )
4911 )
4912 THEN
4913 lv_codepath := jai_general_pkg.plot_codepath(40, lv_codepath);
4914 p_cenvat_rg_flag := 'P';
4915 p_cenvat_rg_message := 'Pending for Claim';
4916 goto end_of_cenvat_flag_validation;
4917 END IF;
4918
4919 -- following conditions added by Vijay Shankar for Bug#3940588. RECEIPTS DEPLUG
4920 -- check to stop claim of later transactions of RECEIVE if RECEIVE line is not yet claimed
4921 lv_statement_id := '41';
4922 IF r_trx.transaction_type NOT IN ('RECEIVE', 'MATCH')
4923 AND nvl(r_receipt_cenvat_dtl.cenvat_claimed_amt,0) = 0 AND nvl(r_receipt_cenvat_dtl.cenvat_amount,0) <> 0
4924 AND nvl(r_receipt_cenvat_dtl.online_claim_flag,'N') = 'N' /*This condition added by nprashar for bug #8644480*/
4925 THEN
4926 lv_codepath := jai_general_pkg.plot_codepath(41, lv_codepath);
4927 p_cenvat_rg_flag := 'P';
4928 p_cenvat_rg_message := 'Pending for Receipt Line Claim'; -- - '||lv_transaction_type;
4929 goto end_of_cenvat_flag_validation;
4930 END IF;
4931 -- End, Vijay Shankar for Bug#3940588
4932 IF r_trx.transaction_type NOT IN ('RECEIVE', 'MATCH') /*Added by nprashar for bug # 8644480*/
4933 AND nvl(r_receipt_cenvat_dtl.cenvat_claimed_amt,0) = 0 AND nvl(r_receipt_cenvat_dtl.cenvat_amount,0) <> 0
4934 AND nvl(r_receipt_cenvat_dtl.online_claim_flag,'Y') = 'Y' THEN
4935 --These condition points to Entire quantity Correction ,in such a case the claimed_amount will be zero
4936 --But since its being claimed before performng Correction the online_claim_flag willbe Y and cenvat_claim_ptg will be > 0
4937 p_cenvat_rg_flag := 'C';
4938 goto end_of_cenvat_flag_validation;
4939 END IF;
4940
4941
4942 -- Updation of excise invoice number for all transactions other than RTV
4943 if lv_transaction_type <> 'RETURN TO VENDOR' and r_trx.excise_invoice_no is null then
4944
4945 lv_statement_id := '42';
4946 lv_codepath := jai_general_pkg.plot_codepath(42, lv_codepath);
4947 OPEN c_excise_invoice_no(r_trx.shipment_line_id);
4948 FETCH c_excise_invoice_no INTO r_exc_inv_no;
4949 CLOSE c_excise_invoice_no;
4950
4951 -- this is to update excise invoice no in case of Offline Claim or somehow excise invoice is not update in POPULATE_DETAILS
4952 jai_rcv_transactions_pkg.update_excise_invoice_no(
4953 p_transaction_id => p_transaction_id,
4954 p_excise_invoice_no => r_exc_inv_no.excise_invoice_no,
4955 p_excise_invoice_date => r_exc_inv_no.excise_invoice_date
4956 );
4957 end if;
4958
4959 /* 3CENVAT_RG_FLAG. END of TRANSACTION VALIDATIONS To SEE WHETHER IT IS PENDING FOR SOMETHING INSPITE OF BEING QUALIFIED for PROCESSING*/
4960
4961 p_cenvat_rg_flag :='N';
4962 <<end_of_cenvat_flag_validation>>
4963
4964 /* 4 Start of PROCESS_VAT_FLAG Validation */
4965
4966 --added the below by Ramananda for Bug#4519697
4967 if p_process_vat_flag = jai_constants.successful THEN
4968 goto end_of_vat_validation;
4969 end if;
4970
4971 /* following condition added as part of DFF elimination. Vijay Shankar for Bug#4346453. RCV DFF Elim. Enh. */
4972 if r_trx.process_vat_status in (jai_constants.yes, 'X', 'O') then
4973 lv_codepath := jai_general_pkg.plot_codepath(42.0, lv_codepath);
4974 goto end_of_vat_validation;
4975 end if;
4976
4977 if r_trx.location_id = 0 then
4978 lv_codepath := jai_general_pkg.plot_codepath(42.1, lv_codepath);
4979 p_process_vat_flag := 'O';
4980 p_process_vat_message := jai_rcv_trx_processing_pkg.get_message('NO-LOC-ORG-SETUP');
4981 goto end_of_vat_validation;
4982 end if;
4983
4984 IF lv_transaction_type NOT IN ( 'RECEIVE', 'RETURN TO VENDOR') THEN
4985 lv_statement_id := '42.1';
4986 lv_codepath := jai_general_pkg.plot_codepath(42.2, lv_codepath);
4987 p_process_vat_flag := 'X';
4988 p_process_vat_message := 'VAT Processing not required for this Transaction Type';
4989 GOTO end_of_vat_validation;
4990 END IF;
4991
4992 OPEN c_recoverable_vat_tax_cnt(r_trx.shipment_line_id, jai_constants.vat_regime);
4993 FETCH c_recoverable_vat_tax_cnt INTO ln_recoverable_vat_tax_cnt;
4994 CLOSE c_recoverable_vat_tax_cnt;
4995
4996 IF ln_recoverable_vat_tax_cnt = 0 THEN
4997 lv_statement_id := '42.2';
4998 lv_codepath := jai_general_pkg.plot_codepath(42.3, lv_codepath);
4999 p_process_vat_flag := 'X';
5000 p_process_vat_message := 'No VAT Taxes exist for receipt line';
5001 GOTO end_of_vat_validation;
5002 END IF;
5003 --pramasub FP Iproc start
5004 IF lv_transaction_type = 'RETURN TO VENDOR' then
5005 --IF r_base_trx.generate_excise_invoice = 'N' THEN
5006 --pramasub commented the above line as the same condition is replaced by the following line for bug#4346453
5007 -- IF r_trx.attribute1 in ( INV_GEN_STATUS_INV_NA ) then 13364465
5008 IF r_trx.excise_inv_gen_status in ( INV_GEN_STATUS_INV_NA ) then
5009 IF NVL(lv_apps_source_code,'$$') = 'POR' THEN /* gen ex inv in the dff is not 'Y' and its a return for iproc return */
5010 p_process_vat_flag := 'O';
5011 p_process_vat_message := 'Call from Iproc returns IL page';
5012 GOTO end_of_vat_validation;
5013 END IF;
5014 END IF;
5015 END IF;
5016 --pramasub FP IProc end
5017 p_process_vat_flag := 'N';
5018
5019 <<end_of_vat_validation>>
5020
5021 /* Vijay Shankar for Bug#4346453. RCV DFF Elim. Enh. */
5022 if r_trx.transaction_type = 'RETURN TO VENDOR' then
5023
5024 /* Excise */
5025 --if p_called_from in (CALLED_FROM_RCV_TRIGGER, CALLED_FROM_FND_REQUEST) then
5026 -- if lv_excise_inv_gen_action is null and r_trx.attribute1 is null then -- and r_trx.attribute3 is null then 13364465
5027 if lv_excise_inv_gen_action is null and r_trx.excise_inv_gen_status is null then
5028 lv_statement_id := '60';
5029 lv_codepath := jai_general_pkg.plot_codepath(60, lv_codepath);
5030 /* Flag is set to 'P' only if validations that set the flag as 'X' are done */
5031 if p_cenvat_rg_flag in ('N', 'P') or ( p_cenvat_rg_flag ='X' and r_trx.organization_type = 'M' and
5032 r_trx.item_class not in ('FGIN', 'FGEX') and r_trx.item_excisable = 'Y' and r_receipt_cenvat_dtl.cenvat_amount = 0 ) then -- Added flag 'X' with other conditions for bug # 12651453
5033
5034 lv_excise_inv_gen_action := INV_GEN_STATUS_PENDING;
5035 elsif p_cenvat_rg_flag in ('O','XT','X') then
5036 lv_excise_inv_gen_action := INV_GEN_STATUS_NA;
5037 end if;
5038
5039 elsif p_called_from = CALLED_FROM_JAITIGRTV and r_trx.attribute1 = INV_GEN_STATUS_INV_NA then
5040 lv_statement_id := '61';
5041 lv_codepath := jai_general_pkg.plot_codepath(61, lv_codepath);
5042 p_cenvat_rg_flag := 'X';
5043 p_cenvat_rg_message := 'Excise Invoice is not applicable';
5044 end if;
5045
5046 /* VAT */
5047 -- if lv_vat_inv_gen_action is null and r_trx.attribute2 is null then -- and r_trx.attribute4 is null then 13364465
5048 if lv_vat_inv_gen_action is null and r_trx.vat_inv_gen_status is null then
5049 lv_statement_id := '62';
5050 lv_codepath := jai_general_pkg.plot_codepath(62, lv_codepath);
5051 if p_process_vat_flag in ('N') then
5052 lv_vat_inv_gen_action := INV_GEN_STATUS_PENDING;
5053 elsif p_process_vat_flag in ('O','X') then
5054 lv_vat_inv_gen_action := INV_GEN_STATUS_NA;
5055 end if;
5056 end if;
5057 end if;
5058
5059 IF p_simulate_flag = 'N' THEN
5060 lv_codepath := jai_general_pkg.plot_codepath(43, lv_codepath);
5061 lv_statement_id := '43';
5062 /* Call to update the Flag values as the validation is completed */
5063 jai_rcv_transactions_pkg.update_process_flags(
5064 p_transaction_id => p_transaction_id,
5065 p_process_flag => p_process_flag,
5066 p_process_message => p_process_message,
5067 p_cenvat_rg_flag => p_cenvat_rg_flag,
5068 p_cenvat_rg_message => p_cenvat_rg_message,
5069 p_process_vat_flag => p_process_vat_flag,
5070 p_process_vat_message => p_process_vat_message,
5071 p_process_date => SYSDATE
5072 );
5073
5074 /* Vijay Shankar for Bug#4346453. RCV DFF Elim. Enh. */
5075 if p_called_from <> CALLED_FROM_JAITIGRTV THEN
5076 /* Bug 5365346. Added by Lakshmi Gopalsami
5077 | Check whether 57F4 transaction has been created for PO
5078 | before updating Excise invoice action. If so we need to set
5079 | the value to 'PENDING' instead of 'NOT_APPLICABLE
5080 */
5081 fnd_file.put_line(FND_FILE.LOG, ' transaction id ' || p_transaction_id);
5082
5083 IF ( Check_57F4_transaction( p_transaction_id ) = 'YES' ) THEN
5084 lv_excise_inv_gen_action := INV_GEN_STATUS_PENDING;
5085 END IF ;
5086 /*
5087
5088 jai_rcv_transactions_pkg.update_attributes(
5089 p_transaction_id => p_transaction_id,
5090 p_attribute1 => lv_excise_inv_gen_action,
5091 p_attribute2 => lv_vat_inv_gen_action
5092 );
5093 */
5094
5095 --13364465
5096 jai_rcv_transactions_pkg.update_inv_stat_and_no(
5097 p_transaction_id => p_transaction_id,
5098 p_excise_inv_gen_status => lv_excise_inv_gen_action,
5099 p_vat_inv_gen_status => lv_vat_inv_gen_action
5100 );
5101 end if;
5102
5103 END IF;
5104
5105 -- this is the final place where we assign the value to p_codepath from local codepath
5106 lv_statement_id := '49';
5107 p_codepath := jai_general_pkg.plot_codepath(lv_codepath||',49', p_codepath, 'cenvat_rg_pkg.validate_trx', 'END');
5108 -- p_codepath := substr(p_codepath||lv_codepath, 1, 2000);
5109
5110 FND_FILE.put_line( fnd_file.log, '$ VALIDATE_TRANSACTION PrcFlg:'||p_process_flag||', Msg:'||p_process_message
5111 ||', CenvatRgFlg:'||p_cenvat_rg_flag ||', Msg:'||p_cenvat_rg_message
5112 ||', PrcVatFlg:'||p_process_vat_flag ||', Msg:'||p_process_vat_message
5113 ||', localPath:'||lv_codepath
5114 );
5115
5116 EXCEPTION
5117 when others then
5118 p_process_flag := 'E';
5119 p_process_message := 'RECEIPT_TRANSACTION_PKG.Validate_transaction:'||SQLERRM||', Statement_id:'||lv_statement_id;
5120 FND_FILE.put_line( fnd_file.log, 'Error in '||p_process_message||'. localErrorPath:'||lv_codepath );
5121 p_codepath := jai_general_pkg.plot_codepath(lv_codepath||',-999', p_codepath, 'cenvat_rg_pkg.validate_trx', 'END');
5122
5123 END validate_transaction;
5124
5125
5126 FUNCTION process_iso_transaction(
5127 p_transaction_id IN NUMBER,
5128 p_shipment_line_id IN NUMBER
5129 ) RETURN BOOLEAN
5130 IS
5131
5132 CURSOR c_shp_hdr(cp_transaction_id number) IS
5133 SELECT receipt_source_code
5134 from rcv_shipment_headers
5135 WHERE shipment_header_id = (select shipment_header_id
5136 from rcv_transactions
5137 where transaction_id = cp_transaction_id);
5138
5139 CURSOR c_shp_line(cp_shipment_line_id number) IS
5140 SELECT from_organization_id, to_organization_id
5141 FROM rcv_shipment_lines
5142 WHERE shipment_line_id = cp_shipment_line_id;
5143
5144 CURSOR c_excise_tax_cnt(cp_shipment_line_id number) is
5145 SELECT count(1)
5146 FROM JAI_RCV_LINE_TAXES
5147 WHERE shipment_line_id = cp_shipment_line_id
5148 -- CVD is Not Considered, because in ISO scenario CVD is not supported.
5149 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
5150
5151 CURSOR c_organization_info(cp_organization_id number) IS
5152 SELECT nvl(trading, 'N') trading, nvl(manufacturing, 'N') manufacturing
5153 , nvl(excise_in_rg23d, 'N') excise_in_rg23d -- Vijay Shankar for Bug#4171469
5154 FROM JAI_CMN_INVENTORY_ORGS
5155 WHERE organization_id = cp_organization_id
5156 AND rownum = 1;
5157
5158 lb_process_iso_transaction BOOLEAN; --File.Sql.35 Cbabu := true;
5159
5160 /* Added by Ramananda for bug#4407165 */
5161 lv_object_name CONSTANT VARCHAR2(61) := 'jai_rcv_trx_processing_pkg.process_iso_transaction';
5162
5163 ln_excise_tax_cnt NUMBER;
5164 r_shp_line c_shp_line%ROWTYPE;
5165 r_shp_hdr c_shp_hdr%ROWTYPE;
5166 r_dest_org c_organization_info%ROWTYPE;
5167 r_src_org c_organization_info%ROWTYPE;
5168
5169 BEGIN
5170
5171 lb_process_iso_transaction := true;
5172
5173 OPEN c_shp_hdr(p_transaction_id);
5174 FETCH c_shp_hdr INTO r_shp_hdr;
5175 CLOSE c_shp_hdr;
5176
5177 IF r_shp_hdr.receipt_source_code IN ('INTERNAL ORDER','INVENTORY') THEN /*rchandan for bug#6030615*/
5178
5179 OPEN c_excise_tax_cnt(p_shipment_line_id);
5180 FETCH c_excise_tax_cnt INTO ln_excise_tax_cnt;
5181 CLOSE c_excise_tax_cnt;
5182
5183 IF ln_excise_tax_cnt > 0 THEN
5184
5185 OPEN c_shp_line(p_shipment_line_id);
5186 FETCH c_shp_line INTO r_shp_line;
5187 CLOSE c_shp_line;
5188
5189 OPEN c_organization_info(r_shp_line.from_organization_id);
5190 FETCH c_organization_info INTO r_src_org;
5191 CLOSE c_organization_info;
5192
5193 IF r_src_org.trading = 'Y' THEN
5194 OPEN c_organization_info(r_shp_line.to_organization_id);
5195 FETCH c_organization_info INTO r_dest_org;
5196 CLOSE c_organization_info;
5197
5198 /* Vijay Shankar for Bug#4171469
5199 following condition modified to pass accounting incase of Trading to Trading with both orgs
5200 having excise_in_rg23d flag set to 'Y'
5201 */
5202 -- if this following condition is true, then it means ISO Processing is not required
5203 -- IF r_dest_org.trading = 'Y' OR r_dest_org.manufacturing = 'Y' THEN
5204 -- following condition modified by Vijay Shankar for Bug#4171469
5205 IF r_dest_org.trading = 'Y' THEN
5206 IF r_src_org.excise_in_rg23d <> 'Y'
5207 --OR r_dest_org.excise_in_rg23d <> 'Y' --commented by Ramananda for Bug #4516577
5208 THEN
5209 lb_process_iso_transaction := false;
5210 END IF;
5211
5212 ELSIF r_dest_org.manufacturing = 'Y' THEN
5213 IF r_src_org.excise_in_rg23d <> 'Y' THEN --Added the if condition by Ramananda for Bug #4516577
5214 lb_process_iso_transaction := false;
5215 END IF;
5216 END IF;
5217 END IF;
5218
5219 END IF;
5220
5221 END IF;
5222
5223 RETURN lb_process_iso_transaction;
5224
5225 /* Added by Ramananda for bug#4407165 */
5226 EXCEPTION
5227 WHEN OTHERS THEN
5228 FND_MESSAGE.SET_NAME('JA','JAI_EXCEPTION_OCCURED');
5229 FND_MESSAGE.SET_TOKEN('JAI_PROCESS_MSG', lv_object_name ||'. Err:'||sqlerrm );
5230 app_exception.raise_exception;
5231
5232 END process_iso_transaction;
5233
5234
5235 FUNCTION get_ancestor_id(
5236 p_transaction_id IN NUMBER,
5237 p_shipment_line_id IN NUMBER,
5238 p_required_trx_type IN VARCHAR2
5239 ) RETURN NUMBER IS
5240
5241 ln_trx_id NUMBER;
5242
5243 lv_transaction_type RCV_TRANSACTIONS.transaction_type%TYPE;
5244
5245 BEGIN
5246
5247 ln_trx_id := p_transaction_id;
5248 FOR i IN (select transaction_id, transaction_type, parent_transaction_id
5249 from rcv_transactions
5250 where shipment_line_id = p_shipment_line_id
5251 and transaction_id <= p_transaction_id
5252 order by transaction_id desc)
5253 LOOP
5254
5255 IF i.transaction_id = ln_trx_id THEN
5256 -- added for Vijay Shankar for Bug#3940588
5257 --IF p_required_trx_type = 'MATCH' THEN /*commented by vkaranam for bug #4636397*/
5258 IF i.transaction_type = 'MATCH' THEN /*added by vkaranam for bug #4636397*/
5259 lv_transaction_type := 'RECEIVE';
5260 ELSE
5261 lv_transaction_type := i.transaction_type;
5262 END IF;
5263 -- End, Vijay Shankar for Bug#3940588
5264
5265 IF lv_transaction_type = p_required_trx_type THEN
5266 RETURN i.transaction_id;
5267 ELSE
5268 ln_trx_id := i.parent_transaction_id;
5269 END IF;
5270 END IF;
5271 END LOOP;
5272
5273 RETURN NULL;
5274 END get_ancestor_id;
5275
5276 FUNCTION get_trxn_tax_amount(
5277 p_transaction_id IN NUMBER,
5278 p_shipment_line_id IN NUMBER,
5279 p_curr_conv_rate IN NUMBER,
5280 p_return_in_inr_curr IN VARCHAR2 --File.Sql.35 Cbabu DEFAULT 'Y'
5281 ) RETURN NUMBER IS
5282
5283 /*
5284 Transaction can have two Currencies 1. Functional(INR) 2. Transactional (Non INR Currency in case of foreign Trxn)
5285 If p_return_in_inr_curr = 'Y' then Functional tax amount is returned otherwise in transactional currency in returned
5286 Tax amount returned is to the tune of TRANSACTION Quantity.
5287 eg in ILDEV -> select jai_rcv_trx_processing_pkg.get_trxn_tax_amount(14108, 10626, 50, 'N') amount from dual;
5288 */
5289
5290 -- This cursor gives tax_amount in FOREIGN Currency
5291 CURSOR c_tax_amount(cp_shipment_line_id IN NUMBER, cp_curr_conv_rate IN NUMBER) IS
5292 SELECT
5293 sum(
5294 nvl(tax_amount, 0) / decode(currency, jai_general_pkg.INDIAN_CURRENCY, cp_curr_conv_rate, 1)
5295 ) non_inr_tax_amount,
5296 sum(
5297 nvl(tax_amount, 0) * decode(currency, jai_general_pkg.INDIAN_CURRENCY, 1, cp_curr_conv_rate)
5298 ) inr_tax_amount
5299 FROM JAI_RCV_LINE_TAXES
5300 WHERE shipment_line_id = cp_shipment_line_id
5301 AND tax_type NOT IN ('TDS', 'Modvat Recovery');
5302
5303 ln_tax_amount NUMBER;
5304 ln_inr_tax_amount NUMBER;
5305 ln_non_inr_tax_amount NUMBER;
5306
5307 BEGIN
5308
5309 OPEN c_tax_amount(p_shipment_line_id, p_curr_conv_rate);
5310 FETCH c_tax_amount INTO ln_non_inr_tax_amount, ln_inr_tax_amount;
5311 CLOSE c_tax_amount;
5312
5313 IF p_return_in_inr_curr = 'Y' THEN
5314 ln_tax_amount := ln_inr_tax_amount;
5315 ELSE
5316 ln_tax_amount := ln_non_inr_tax_amount;
5317 END IF;
5318
5319 ln_tax_amount := nvl(ln_tax_amount, 0) * get_apportion_factor( p_transaction_id => p_transaction_id );
5320
5321 RETURN nvl(ln_tax_amount, 0);
5322
5323 END get_trxn_tax_amount;
5324
5325 FUNCTION get_trxn_cenvat_amount(
5326 p_transaction_id IN NUMBER,
5327 p_shipment_line_id IN NUMBER,
5328 p_organization_type IN VARCHAR2,
5329 p_curr_conv_rate IN NUMBER
5330 ) RETURN NUMBER IS
5331
5332 /*
5333 This Always Returns Total Cenvat amount in INR Currency to the tune of transaction quantity, uom
5334 eg in ILDEV -> select jai_rcv_trx_processing_pkg.get_trxn_cenvat_amount(14108, 10626, 50) amount from dual;
5335 */
5336
5337 -- This cursor gives tax_amount in FOREIGN Currency
5338 CURSOR c_tax_amount(cp_shipment_line_id IN NUMBER, cp_curr_conv_rate IN NUMBER, cp_organization_type IN VARCHAR2) IS
5339 SELECT
5340 sum(
5341 nvl(a.tax_amount, 0) * (b.mod_cr_percentage/100)
5342 * decode(a.currency, jai_general_pkg.INDIAN_CURRENCY, 1, cp_curr_conv_rate)
5343 ) manufacturing_cenvat,
5344 sum(
5345 nvl(a.tax_amount, 0) * decode(a.currency, jai_general_pkg.INDIAN_CURRENCY, 1, cp_curr_conv_rate)
5346 ) trading_cenvat
5347 FROM JAI_RCV_LINE_TAXES a, JAI_CMN_TAXES_ALL b
5348 WHERE shipment_line_id = cp_shipment_line_id
5349 AND a.tax_id = b.tax_id
5350 AND upper(a.tax_type) IN ( 'EXCISE',
5351 'ADDL. EXCISE',
5352 'OTHER EXCISE',
5353 'CVD',
5354 jai_constants.tax_type_add_cvd,
5355 -- Modified by SACSETHI Bug# 5228046
5356 -- Forward porting the change in 11i bug 5365523
5357 -- (Additional CVD Enhancement) as part of the R12 bug 5228046
5358 jai_constants.tax_type_exc_edu_cess,
5359 jai_constants.tax_type_cvd_edu_cess,jai_constants.tax_type_sh_exc_edu_cess,
5360 jai_constants.tax_type_sh_cvd_edu_cess) --Added by kunkumar for bugno5989740 -- Vijay Shankar for Bug#3940588 EDU CESS
5361 AND (cp_organization_type = 'T' OR (cp_organization_type <> 'T' AND a.modvat_flag = 'Y') );
5362
5363 ln_manufacturing_cenvat_amount NUMBER;
5364 ln_trading_cenvat_amount NUMBER;
5365 ln_tax_amount NUMBER;
5366
5367 BEGIN
5368
5369 OPEN c_tax_amount(p_shipment_line_id, p_curr_conv_rate, p_organization_type);
5370 FETCH c_tax_amount INTO ln_manufacturing_cenvat_amount, ln_trading_cenvat_amount;
5371 CLOSE c_tax_amount;
5372
5373 IF p_organization_type = 'M' THEN
5374 ln_tax_amount := ln_manufacturing_cenvat_amount;
5375 ELSIF p_organization_type = 'T' THEN
5376 ln_tax_amount := ln_trading_cenvat_amount;
5377 END IF;
5378
5379 ln_tax_amount := nvl(ln_tax_amount, 0) * get_apportion_factor( p_transaction_id => p_transaction_id );
5380
5381 RETURN nvl(ln_tax_amount, 0);
5382
5383 END get_trxn_cenvat_amount;
5384
5385
5386 FUNCTION get_apportion_factor(
5387 p_transaction_id IN NUMBER
5388 ) RETURN NUMBER IS
5389
5390 /*
5391 Returns the Value of (TransactionQuantity * TransactionUOM) / (TaxQuantity * TaxUom)
5392 i.e if Transaction Quantity, UOM = 18, EACH
5393 and Tax Quantity, UOM = 5, DOZEN
5394 then this function returns (18*1/5*12)=0.3
5395 */
5396
5397 /* Added by Ramananda for bug#4407165 */
5398 lv_object_name CONSTANT VARCHAR2(61) := 'jai_rcv_trx_processing_pkg.get_apportion_factor';
5399
5400 CURSOR c_ja_in_receipt_lines_qty(cp_shipment_line_id IN NUMBER) IS
5401 SELECT qty_received, transaction_id
5402 FROM JAI_RCV_LINES
5403 WHERE shipment_line_id = cp_shipment_line_id;
5404
5405 r_trx c_trx%ROWTYPE;
5406 r_tax_trx c_base_trx%ROWTYPE;
5407
5408 ln_tax_transaction_id NUMBER;
5409 ln_tax_quantity NUMBER;
5410 lv_tax_uom_code MTL_UNITS_OF_MEASURE.uom_code%TYPE;
5411 lv_trxn_uom_code MTL_UNITS_OF_MEASURE.uom_code%TYPE;
5412
5413 ln_uom_conv_rate NUMBER;
5414 ln_apportion_factor NUMBER;
5415
5416 BEGIN
5417
5418 -- if this is called from jai_rcv_trx_processing_pkg.populate_details then most of the fields are NULL.
5419 -- So, Check whether the required values are populated or not before proceding further
5420 OPEN c_trx(p_transaction_id);
5421 FETCH c_trx INTO r_trx;
5422 CLOSE c_trx;
5423
5424 OPEN c_ja_in_receipt_lines_qty(r_trx.shipment_line_id);
5425 FETCH c_ja_in_receipt_lines_qty INTO ln_tax_quantity, ln_tax_transaction_id;
5426 CLOSE c_ja_in_receipt_lines_qty;
5427
5428 IF ln_tax_quantity = 0 THEN
5429 RETURN 0;
5430 END IF;
5431
5432 OPEN c_base_trx(ln_tax_transaction_id);
5433 FETCH c_base_trx INTO r_tax_trx;
5434 CLOSE c_base_trx;
5435
5436 lv_trxn_uom_code := r_trx.uom_code;
5437 lv_tax_uom_code := nvl(r_tax_trx.uom_code,
5438 jai_general_pkg.get_uom_code( p_uom => r_tax_trx.unit_of_measure)
5439 );
5440
5441 IF lv_trxn_uom_code = lv_tax_uom_code THEN
5442 ln_uom_conv_rate := 1;
5443 ELSE
5444 ln_uom_conv_rate := jai_general_pkg.trxn_to_primary_conv_rate(
5445 p_transaction_uom_code => lv_trxn_uom_code,
5446 p_primary_uom_code => lv_tax_uom_code,
5447 p_inventory_item_id => r_trx.inventory_item_id
5448 );
5449 END IF;
5450
5451 ln_apportion_factor := ln_uom_conv_rate * r_trx.quantity/ln_tax_quantity;
5452
5453 RETURN ln_apportion_factor;
5454
5455 /* Added by Ramananda for bug#4407165 */
5456 EXCEPTION
5457 WHEN OTHERS THEN
5458 FND_MESSAGE.SET_NAME('JA','JAI_EXCEPTION_OCCURED');
5459 FND_MESSAGE.SET_TOKEN('JAI_PROCESS_MSG', lv_object_name ||'. Err:'||sqlerrm );
5460 app_exception.raise_exception;
5461
5462 END get_apportion_factor;
5463
5464 FUNCTION get_equivalent_qty_of_receive(
5465 p_transaction_id IN NUMBER
5466 ) RETURN NUMBER IS
5467
5468 /*
5469 Returns the Value of (TransactionQuantity * TransactionUOM) / (TaxUom)
5470 i.e if Transaction Quantity, UOM = 18, EACH
5471 and Tax Quantity, UOM = 5, DOZEN
5472 then this function returns (18*1/12) = 1.5
5473 */
5474
5475 CURSOR c_ja_in_receipt_lines_qty(cp_shipment_line_id IN NUMBER) IS
5476 SELECT qty_received, transaction_id
5477 FROM JAI_RCV_LINES
5478 WHERE shipment_line_id = cp_shipment_line_id;
5479
5480 r_trx c_trx%ROWTYPE;
5481 r_tax_trx c_base_trx%ROWTYPE;
5482
5483 ln_tax_transaction_id NUMBER;
5484 ln_tax_quantity NUMBER;
5485 lv_tax_uom_code MTL_UNITS_OF_MEASURE.uom_code%TYPE;
5486 lv_trxn_uom_code MTL_UNITS_OF_MEASURE.uom_code%TYPE;
5487
5488 ln_uom_conv_rate NUMBER;
5489 ln_apportion_factor NUMBER;
5490
5491 /* Added by Ramananda for bug#4407165 */
5492 lv_object_name CONSTANT VARCHAR2(61) := 'jai_rcv_trx_processing_pkg.get_equivalent_qty_of_receive';
5493
5494 BEGIN
5495
5496 -- if this is called from jai_rcv_trx_processing_pkg.populate_details then most of the fields are NULL.
5497 -- So, Check whether the required values are populated or not before proceding further
5498 OPEN c_trx(p_transaction_id);
5499 FETCH c_trx INTO r_trx;
5500 CLOSE c_trx;
5501
5502 OPEN c_ja_in_receipt_lines_qty(r_trx.shipment_line_id);
5503 FETCH c_ja_in_receipt_lines_qty INTO ln_tax_quantity, ln_tax_transaction_id;
5504 CLOSE c_ja_in_receipt_lines_qty;
5505
5506 IF ln_tax_quantity = 0 THEN
5507 RETURN 0;
5508 END IF;
5509
5510 OPEN c_base_trx(ln_tax_transaction_id);
5511 FETCH c_base_trx INTO r_tax_trx;
5512 CLOSE c_base_trx;
5513
5514 lv_trxn_uom_code := r_trx.uom_code;
5515 lv_tax_uom_code := nvl(r_tax_trx.uom_code,
5516 jai_general_pkg.get_uom_code( p_uom => r_tax_trx.unit_of_measure)
5517 );
5518
5519 IF lv_trxn_uom_code = lv_tax_uom_code THEN
5520 ln_uom_conv_rate := 1;
5521 ELSE
5522 ln_uom_conv_rate := jai_general_pkg.trxn_to_primary_conv_rate(
5523 p_transaction_uom_code => lv_trxn_uom_code,
5524 p_primary_uom_code => lv_tax_uom_code,
5525 p_inventory_item_id => r_trx.inventory_item_id
5526 );
5527 END IF;
5528
5529 ln_apportion_factor := ln_uom_conv_rate * r_trx.quantity;
5530
5531 RETURN ln_apportion_factor;
5532
5533
5534 /* Added by Ramananda for bug#4407165 */
5535 EXCEPTION
5536 WHEN OTHERS THEN
5537 FND_MESSAGE.SET_NAME('JA','JAI_EXCEPTION_OCCURED');
5538 FND_MESSAGE.SET_TOKEN('JAI_PROCESS_MSG', lv_object_name ||'. Err:'||sqlerrm );
5539 app_exception.raise_exception;
5540
5541 END get_equivalent_qty_of_receive;
5542
5543
5544 FUNCTION get_message( p_message_code IN VARCHAR2) RETURN VARCHAR2 IS
5545 lv_message JAI_RCV_TRANSACTIONS.process_message%type;
5546 BEGIN
5547
5548 IF p_message_code = 'NO-ORG' THEN
5549 lv_message := 'Organization is not found ';
5550
5551 ELSIF p_message_code = 'NO-LOC-ORG-SETUP' THEN
5552 lv_message := 'Localization setup does not exist for this Location';
5553
5554 ELSIF p_message_code = 'TAX-NOT-MODIFIED' THEN
5555 lv_message := 'Taxes can still be modified ';
5556
5557 ELSIF p_message_code = 'NO-TAXES' THEN
5558 lv_message := 'Localization taxes does not exist for this Organization';
5559
5560 ELSIF p_message_code = 'NO-BASE-ACCT' THEN
5561 lv_message := 'The Initial Line Accounting/costing is not done';
5562
5563 ELSIF p_message_code = 'ITEM-CLASS-NULL' THEN
5564 lv_message := 'Item Class is null ';
5565
5566 ELSIF p_message_code = 'ITEM-CLASS-OTIN' THEN
5567 lv_message := 'Item Class is OTIN/OTEX ';
5568
5569 ELSIF p_message_code = 'ITEM-TRADING-NO' THEN
5570 lv_message := 'Item Trading Flag is Not present ';
5571
5572 ELSIF p_message_code = 'ITEM-CENVATABLE-NO' THEN
5573 lv_message := 'Item Modvat Flag is Not present ';
5574
5575 ELSIF p_message_code = 'NO-EXCISE-TAXES' THEN
5576 lv_message := 'No Excise Taxes are present ';
5577
5578 ELSIF p_message_code = 'NO-BASE-RG' THEN
5579 lv_message := 'The Initial RG Entry for the parent transaction is not passed ';
5580
5581 ELSIF p_message_code ='BASE-MONTH-DIFFERENT' THEN
5582 lv_message := 'Month is changed since Parent RG Entry was passed';
5583 END IF;
5584
5585 return lv_message;
5586
5587 END get_message;
5588
5589 --------------------------------------End Message Procedure------------------------------------------------------
5590
5591 FUNCTION get_object_code( p_object_name IN VARCHAR2, p_event_name IN VARCHAR2) RETURN VARCHAR2 IS
5592
5593 -- This is for future use
5594 BEGIN
5595
5596 return p_object_name||p_event_name||':';
5597 END get_object_code;
5598
5599 FUNCTION get_accrue_on_receipt(
5600 p_po_distribution_id IN NUMBER,
5601 p_po_line_location_id IN NUMBER DEFAULT NULL
5602 ) RETURN VARCHAR2 IS
5603
5604 CURSOR c_dist_dtl(cp_po_distribution_id IN NUMBER) IS
5605 SELECT accrue_on_receipt_flag
5606 FROM po_distributions_all
5607 WHERE po_distribution_id = cp_po_distribution_id;
5608
5609 /* added by Vijay Shankar for Bug#4215402 */
5610 CURSOR c_po_shipment_dtl(cp_po_line_location_id IN NUMBER) IS
5611 SELECT accrue_on_receipt_flag
5612 FROM po_line_locations_all
5613 WHERE line_location_id = cp_po_line_location_id;
5614
5615 lv_accrue_on_receipt_flag po_line_locations_all.accrue_on_receipt_flag%TYPE;
5616
5617 BEGIN
5618
5619 IF p_po_distribution_id IS NOT NULL THEN
5620 OPEN c_dist_dtl(p_po_distribution_id);
5621 FETCH c_dist_dtl INTO lv_accrue_on_receipt_flag;
5622 CLOSE c_dist_dtl;
5623
5624 /* added by Vijay Shankar for Bug#4215402 */
5625 ELSE
5626 OPEN c_po_shipment_dtl(p_po_line_location_id);
5627 FETCH c_po_shipment_dtl INTO lv_accrue_on_receipt_flag;
5628 CLOSE c_po_shipment_dtl;
5629 END IF;
5630
5631 RETURN nvl(lv_accrue_on_receipt_flag, 'N');
5632
5633 END get_accrue_on_receipt;
5634
5635 END jai_rcv_trx_processing_pkg;
[Home] [Help]