[Home] [Help]
PACKAGE BODY: APPS.JAI_MTL_TRXS_PKG
Source
1 PACKAGE BODY JAI_MTL_TRXS_PKG AS
2 /* $Header: jai_mtl_trxs_pkg.plb 120.29.12020000.2 2012/12/24 13:22:30 mmurtuza ship $ */
3
4 /*----------------------------------------------------------------------------------------------------------------------
5 CHANGE HISTORY: FILENAME: jai_mtl_trxs_pkg.plb
6 S.No Date Author and Details
7 ------------------------------------------------------------------------------------------------------------------------
8 1 24-Jan-2007 rchandan for #2942973 for File Version 115.6 (INTER ORG Impl.)
9 The parameter p_receipt_num in claim_balance_cgvat was defined as NUMBER and
10 so the query was not happening properly. Now it is defined as VARCHAR2.
11 Also removed a delete statement which was deleting from jai_rgm_trm_schedules_t if
12 there were any installments generated already.
13
14 2 26/01/2007 cbabu for bug#2942973 Version: 115.9 (INTER ORG Impl.)
15 Added the procedures
16 default_taxes - This defaults or redefaults the taxes. Incase of redefaultation, deletes the
17 entire data that is saved before base transaction is saved
18
19 sync_with_base_trx - This deletes IL data where in record PKs do not exist in base transaction tables
20 this internally uses delete_trx_autonomous or delete_trx procedures
21
22 These changes are done as part of ReArch. Inter Org Form
23
24 3 29-Jan-2007 rchandan for #2942973 for File Version 115.11 (INTER ORG Impl.)
25 Modified procedure DEFAULT_TAXES to recalculate taxes if the quantity alone
26 is changed. In other cases it would redefault taxes
27
28 4 30-Jan-2007 rchandan for bug#942973 for File Version 115.11 (INTER ORG Impl.)
29 Deletion from JAI_CMN_MATCH_TAXES was missing when quantity is changed after matching.
30 So added this statement now
31
32 5. 5-mar-2007 kunkumar - bug#5907436 -file branch 6107 on 115 main code line.
33 Added higher education cess needed for implementing requirements imposed by budget of 2007.
34 Introduces huge dependencies as there are data model changes associated with this bug.
35
36 6. 14-apr-2007 Vkaranam for bug #5907436 ,File version #115.16.6107.4 / 115.19
37 ENH:Handling Secondary And Higher Education Cess.
38 Fix:
39 Code changes are done in do_cenvat_acctg and cenvat_process procedures.
40
41 7. 01-08-2007 rchandan for bug#6030615 , Version 120.0
42 Issue : Inter org Forward porting
43 This is a new file in R12 now.
44 8. 21-aug-2007 vkaranam for bug#6030615,File version 120.3
45 1.Changed std_cost_entry procedure.
46 2.added journal_entry_id column in the insert stmt of jai_mtl_trx_jrnls
47 9. 21-aug-2007 vkaranam for bug#6030615,File version 120.4
48 Changes are done as part of the performance issue.
49
50 10. 27-sep-2007 forward porting of bug 6377964
51
52 11. 09-Oct-2007 rchandan for bug#6487364,File version 120.8
53 Issue : QA observations for Inter org
54 Fix : For receiving organization no cess entries were made For RG23D.
55 Added calls to do this
56
57 12 10/10/2007 rchandan for bug#6487803, File version 120.10
58 Issue : R12RUP04.I/ORG.QA.ST1:NOT ABLE TO DO RECEIVING IN NON BONDED SUB INVENTORY
59 Fix : When Direct org transfer is done to a Non Bonded Subinventory, the excise processing
60 should not happen for receiving org , but the transaction should go through fine.
61 Added an elsif condiftion to do nothing for receiving org.
62 For accounting as well added condition so that the excise entries will be accounted
63 only for trading organization or Manufacturing org for a Bonded subinventory.
64
65 13. 12/10/2007 rchandan for bug#6497301,6487489. File version 120.12
66 Issue : R12RUP04.I/ORG.QA.ST1: NOT ABLE TO MAKE A CAPTIAL GOODS TRANSFER FOR INTRANSIT
67 Fix : It is identified that there are new columns added to table jai_rcv_journal_entries
68 and these are not null. As the impact of this is not taken care in cenvat_auto_claim
69 procedure this issue was coming.
70 Now added these columns in the insert into JAI_RCV_JOURNAL_ENTRIES
71 Issue : R12RUP04.I/ORG.QA.ST1: PPV ENTRY TO BE GENERATED FOR STD COST REC ORG
72 Fix : For standard costing , Purchase price variance entry needs to generated
73 for the non - recoverable tax amount.
74 Made changes in std_cost_entry procedure to this effect.
75 PPV account is debited and Inventory receiving account is credited.
76
77 14. 15/10/2007 rchandan for bug#6487489. File version 120.13
78 Issue : R12RUP04.I/ORG.QA.ST1: PPV ENTRY TO BE GENERATED FOR STD COST REC ORG
79 Fix : The PPV generated should be for the sum of all non recoverable taxes and excisable
80 recoverable taxes in case of non bonded sub inventory. In case of Bonded it should be
81 generated only for Non receoverable tax only. Changes for this effect are made.
82
83 15. 15/10/2007 rchandan for bug#6501436, File Version 120.14
84 Issue : R12RUP04.I/ORG.QA.ST1:PART 2 SHOULD NOT BE UPDATED FOR REC ORG IN CASE OF FGIN
85 Fix : For receiving org when the item class is FGIN or FGEX, no register updates should
86 haappen and also accounting for this amount should also not happen.
87 A check for the item class is put wherever applicable.
88
89 Fix for the previous bug#6487489 is also made. In this ln_oth_modvat_amt is removed
90 from the calculation of ln_cost_amount.
91 Moreover for FGIN and FGEX clas also PPV entry needs to be modified to include the
92 recoverable tax which was not hitting the register and also not included in the accounting.
93
94 16. 16/10/2007 rchandan for bug#6504150, File Version 120.15
95 Issue : R12RUP04.I/ORG.QA.ST1:USER_JE_CATEGORY_NAM TO BE CHANGED FOR PPV ENTRY
96 Fix : In the gl_interface table the user_je_category_name should be populated as
97 'MTL' for PPV entry which is being generated for the receiving org in the
98 Direct Org Transfer. Made a change to this effect.
99
100 17. 12-May-2008 Changes by nprashar for bug # 6710747. Forward ported from 11i bug#6086452.
101 Issue:
102 When trying to save the IL interorg transfer a error message pops up and also
103 not allowing close the form. Which makes to close the application and login again.
104
105 The above mentioned issue is happening because of the deadlock on jai_mtl_trxs table.
106 1.on the key commit trigger of mtl_trx_line in interorg form(JAINVMTX.fmb),there is an update stmt on jai_mtl_trxs table.
107 If any error occurs in this trigger and the user tries to comeout of the form there is a call to sync_with_base_trx
108 which will delete the error record in jai_mtl_trxs table.
109 since it is trying to delete the same record which has been locked by the update stmt,deadlock error occurs waiting for the resource .
110
111 Fix:
112 changes are done in cenvat_process procedure
113
114 18. 01-JUL-2009 Bgowrava for Bug#8414075 , File Version 120.15.12000000.3
115 Addded round condition to the ln_amount value according to the rounding factor mentioned in tax setup and the same is passed
116 to jai_cmn_rgm_terms_pkg.generate_term_schedules.
117
118 19. 18-aug-2009 vkaranam for bug#8800063,file version 120.15.12000000.6
119 Issue:
120 IL INTER ORGANIZATION TRANSFER IS GIVING DEADLOCK ERROR
121
122 issue is happening with the delete_trx_autonomous (Pragma autonomous
123 transaction) procedure.
124
125 The below stmt has been executed from (JAINVMTX.KEY-COMMIT)-->
126 jai_mtl_trxs_pkg. sync_trx_with_base (delete_trx) procedure.
127
128 Delete JAI_MTL_TRXS WHERE TRANSACTION_HEADER_ID = :B2 AND TRANSACTION_TEMP_ID
129 = NVL(:B1 , TRANSACTION_TEMP_ID ) AND TRANSACTION_COMMIT_DATE IS NULL.
130
131 Lock is acquired by the current session and it will be removed once the
132 transaction gets commited/rollbacked.
133
134 When any error comes during the forms commit processing the changes will not
135 get applied to the database..
136
137 Here some base error "lot/serial number does not match" is coming and the
138 changes are not getting applied due to which lock exists on jai_mtl_trxs..
139
140 Now when we try to close the form then the below stmt will be executed.
141 ( JAINVMTX.KEY-EXIT --->sync_with_base_trx (delete_trx_autonomous)).
142
143 Delete JAI_MTL_TRXS WHERE TRANSACTION_HEADER_ID = :B2 AND TRANSACTION_TEMP_ID
144 = NVL(:B1 , TRANSACTION_TEMP_ID ) AND TRANSACTION_COMMIT_DATE IS NULL.
145
146 This delete is waiting for the lock acquired for the previous delete stmt.
147 hence the deadlock issue is coming
148
149 Fix:
150
151 Modified sync_with_base_trx such that delete_trx_autonomous will not be used
152 anymore.
153
154 13-oct-2009 vkaranam for bug#8882785
155 Issue:
156 TST1212.XB1.QA.INCLUSIVE TAX IS NOT RIGHT FOR INTER-ORG TRANSFER TRANSACTION
157 if the interorg transfer has the inclusive taxes and the assessable price list is not \
158 attached to the internal ct,then the taxes are calculated wrongly.
159 issue is with the assessable_value being rounded.
160 In package jai_mtl_trxs_pkg.default_taxes, the variable is defined as
161 following:
162
163 ln_assessable_value number(15);
164 ln_vat_assessable_value number(15);
165
166 So when calling function jai_om_utils_pkg.get_oe_assesaable_value,
167 these two variables will be truncated.
168
169 Fix:
170 Changed the ln_assessable_value, ln_vat_assessable_value variables declartion to
171 NUMBER;
172
173 added round with precision 2 in the below condition.
174 ln_assessable_value := nvl(ln_assessable_value,0)* p_line_quantity;
175
176 2010/04/14 Bo Li For bug9305067
177 Change the parameters for the procedure insert_vat_repository_entry .
178
179 2010/09/06 Peng Zheng For bug10043656
180 New Tax Category Default logic for GST
181
182 2010/09/10 Jia for GST Bug#10043656.
183 03-dec-2010 vkaranam for bug#10242419
184 Issue:WRONG ACCOUNTING FOR IL INTER ORG
185 Fix: modified the do_cenvat_accounting procedure for cess and shecess
186 Correct accounting entries are as follows :
187
188 Direct org transfer :
189 Receiving organization is trading
190
191 Set1 :
192 Inventory Receiving Dr
193 Interorg Payable Account Cr
194
195 Set2:
196
197 Excise RG23D account Dr
198 Inventory Receiving Cr
199
200
201 In the ct case Set2 accounting entries are not getting generated for Basic
202 excise,But the same is happening for cess and shecess.
203
204
205 05-dec-2010 vkaranam for bug#10242419
206 Issue:WRONG ACCOUNTING FOR IL INTER ORG
207 Direct transfer ,sending organization accounting entry for excise is hitting "excise rcvble account" instead of excise paid accnt.
208 Fix:
209 Modified do_cenvat_Acctg to pass the excise paid accnt for sending org direct transfer excise entry
210 05-jan-2011 vkaranam for bug#10607819
211 Issue:
212 GL IMPORT FOR SOURCE "PURCHASING" COMPLETING WITH WARNING-ERROR CODE EU02
213 Reason:
214 CG auto claim entries for excise cess has 2 debit entries instead of 1 debit and 1 credit.
215 Also the SHE cess accounting is not done during the CG auto claim.
216
217 Fix:
218 changes are done in cenvat_auto_claim.
219
220 Accounting entries shall be in sync with the basic excise:
221
222 Credit Excise cess receivable account
223 Debit Excise Cess CG account
224
225 SHECESS:
226
227 Credit Excise SHE cess receivable account
228 Debit Excise SHE Cess CG account
229 5. 30-AUG-2011 vkaranam for bug#12807591
230 Issue:In Trading Organization, RG23D reversal accounting for Sales order shipment is hitting the OSP Excise account
231 provided in the Organization additional Info screen instead of the excise paid payble account.
232 fix:
233 Replace excise_paid_account with excise_rcvble_account for excise tax in do_cenvat_Acctg procedure.
234 changes are done in cenvat_auto_claim procedure
235 (replaced excise_rcvble_account with cenvat_rcvble_Account in cenvat_auto_claim).
236
237
238 ------------------------------------------------------------------------------------------------------------------------
239 */
240
241 TYPE gl_params IS RECORD (
242 amount NUMBER,
243 credit_account gl_interface.code_combination_id%TYPE,
244 debit_account gl_interface.code_combination_id%TYPE,
245 organization_id NUMBER,
246 organization_code gl_interface.reference1%TYPE,
247 remarks VARCHAR2(64));
248
249 TYPE gl_entries IS TABLE OF gl_params INDEX BY PLS_INTEGER;
250
251
252 PROCEDURE get_cost_amt
253 (
254 p_source_line_id IN NUMBER,
255 p_organization_id IN NUMBER,
256 p_location_id IN NUMBER,
257 p_item_id IN NUMBER,
258 p_excise_amount OUT NOCOPY NUMBER,
259 p_non_modvat_amount OUT NOCOPY NUMBER,
260 p_other_modvat_amount OUT NOCOPY NUMBER,
261 p_process_message OUT NOCOPY VARCHAR2,
262 p_process_status OUT NOCOPY VARCHAR2
263 ) IS
264
265 ln_modvat_amount NUMBER := 0;
266 ln_non_modvat_amount NUMBER := 0;
267 ln_other_modvat_amount NUMBER := 0;
268 ln_conv_factor NUMBER := 1;
269 lv_tax_modvat_flag JAI_RCV_LINE_TAXES.modvat_flag%type;
270
271 ln_precision number:= 2 ;
272 ln_recoverable_amt number;
273 ln_converted_tax_amt number;
274
275 lv_item_trading_flag VARCHAR2(20);
276 lv_excise_in_trading VARCHAR2(20);
277 lv_item_excisable VARCHAR2(20);
278 lv_organization_type VARCHAR2(20);
279 lv_manufacturing VARCHAR2(20);
280 lv_trading VARCHAR2(20);
281
282
283
284 cursor c_get_org_info IS
285 SELECT excise_in_RG23D excise_in_trading , NVL(manufacturing,'N') , NVL(trading ,'N')
286 FROM JAI_CMN_INVENTORY_ORGS
287 WHERE organization_id = p_organization_id
288 AND location_id = p_location_id;
289
290 cursor c_get_item_info IS
291 select item_Trading_flag , excise_flag
292 from JAI_INV_ITM_SETUPS
293 where inventory_item_id = p_item_id
294 and organization_id = p_organization_id;
295
296 BEGIN
297
298
299 OPEN c_get_org_info;
300 FETCH c_get_org_info INTO lv_excise_in_trading ,lv_manufacturing , lv_trading ;
301 CLOSE c_get_org_info;
302
303 OPEN c_get_item_info;
304 FETCH c_get_item_info INTO lv_item_trading_flag,lv_item_excisable ;
305 CLOSE c_get_item_info;
306
307 IF lv_manufacturing = 'Y' THEN
308 lv_organization_type := 'M';
309 ELSIF lv_trading = 'Y' THEN
310 lv_organization_type := 'T';
311 END IF;
312
313 FOR tax_rec IN
314 (
315 SELECT
316 rtl.tax_type,
317 nvl(rtl.tax_amt, 0) tax_amount,
318 nvl(rtl.modvat_flag, 'N') modvat_flag,
319 nvl(rtl.currency_code, 'INR') currency,
320 nvl(jtc.mod_cr_percentage, 0) mod_cr_percentage
321 FROM
322 jai_cmn_document_taxes rtl,
323 jai_cmn_taxes_all jtc
324 WHERE
325 source_doc_line_id = p_source_line_id
326 AND jtc.tax_id = rtl.tax_id
327 AND source_doc_type = 'INTERORG_XFER'
328
329 )
330 LOOP
331
332 ln_converted_tax_amt := tax_rec.tax_amount;
333 ln_converted_tax_amt := nvl(ln_converted_tax_amt,0);
334
335 if tax_rec.modvat_flag = 'Y'
336 and upper(tax_rec.tax_type) IN ('EXCISE', 'ADDL. EXCISE', 'OTHER EXCISE', 'CVD',
337 'ADDITIONAL_CVD', 'TDS', 'MODVAT RECOVERY',
338 jai_constants.tax_type_exc_edu_cess, jai_constants.tax_type_cvd_edu_cess, jai_constants.tax_type_sh_exc_edu_cess, jai_constants.tax_type_sh_cvd_edu_cess) --Added higher education cess by kundan kumar for bug#5907436
339
340 then
341
342
343 lv_tax_modvat_flag := 'Y';
344
345
346 elsif tax_rec.modvat_flag = 'Y'
347 and upper(tax_rec.tax_type) NOT IN ('EXCISE', 'ADDL. EXCISE', 'OTHER EXCISE', 'CVD',
348 'ADDITIONAL_CVD',
349 jai_constants.tax_type_exc_edu_cess,jai_constants.tax_type_sh_exc_edu_cess,jai_constants.tax_type_sh_cvd_edu_cess, jai_constants.tax_type_cvd_edu_cess) --Added higher education cess by kundan kumar for bug#5907436
350
351 then
352
353
354 lv_tax_modvat_flag := 'Y';
355
356 elsif tax_rec.modvat_flag = 'N'
357 and lv_item_trading_flag = 'Y' /* Excise IN RG23D scenario */
358 and lv_excise_in_trading = 'Y'
359 and lv_item_excisable = 'Y'
360 and lv_organization_type = 'T'
361 and upper(tax_rec.tax_type) IN ('EXCISE', 'ADDL. EXCISE', 'OTHER EXCISE', 'CVD',
362 'ADDITIONAL_CVD',
363 jai_constants.tax_type_exc_edu_cess,jai_constants.tax_type_sh_exc_edu_cess, --Added higher education cess by kundan kumar for bug#5907436
364
365
366 jai_constants.tax_type_sh_cvd_edu_cess, jai_constants.tax_type_cvd_edu_cess) --Added higher education cess by kundan kumar for bug#5907436
367
368 then
369
370 lv_tax_modvat_flag := 'Y';
371
372 else
373 lv_tax_modvat_flag := 'N';
374
375 end if; --tax_rec.modvat_flag = 'Y'
376
377
378 if upper(tax_rec.tax_type) NOT IN ('TDS', 'MODVAT RECOVERY') THEN
379
380
381 if lv_tax_modvat_flag = 'Y'
382 and upper(tax_rec.tax_type) IN ( 'EXCISE', 'ADDL. EXCISE', 'OTHER EXCISE', 'CVD',
383 'ADDITIONAL_CVD',
384 jai_constants.tax_type_exc_edu_cess,jai_constants.tax_type_sh_exc_edu_cess, --Added higher education cess by kundan kumar for bug#5907436
385
386 jai_constants.tax_type_sh_cvd_edu_cess, --Added higher education cess by kundan kumar for bug#5907436
387
388 jai_constants.tax_type_cvd_edu_cess)
389 then
390
391
392 ln_recoverable_amt :=
393 round( tax_rec.tax_amount * (tax_rec.mod_cr_percentage/100) * ln_conv_factor, ln_precision);
394 ln_modvat_amount := ln_modvat_amount + ln_recoverable_amt;
395 ln_non_modvat_amount := ln_non_modvat_amount + ( ln_converted_tax_amt - ln_recoverable_amt);
396
397
398 elsif lv_tax_modvat_flag = 'Y'
399 and upper(tax_rec.tax_type) NOT IN ('EXCISE', 'ADDL. EXCISE', 'OTHER EXCISE', 'CVD',
400 'ADDITIONAL_CVD',
401 jai_constants.tax_type_exc_edu_cess,jai_constants.tax_type_sh_exc_edu_cess, --Added higher education cess by kundan kumar for bug#5907436
402
403 jai_constants.tax_type_sh_cvd_edu_cess, --Added higher education cess by kundan kumar for bug#5907436
404
405 jai_constants.tax_type_cvd_edu_cess)
406 then
407
408 ln_recoverable_amt :=
409 round( tax_rec.tax_amount * (tax_rec.mod_cr_percentage/100) * ln_conv_factor, ln_precision);
410 ln_other_modvat_amount := ln_other_modvat_amount + ln_recoverable_amt;
411 ln_non_modvat_amount := ln_non_modvat_amount + ( ln_converted_tax_amt - ln_recoverable_amt);
412
413 ELSIF lv_tax_modvat_flag ='N' and upper(tax_rec.tax_type) NOT IN ('TDS', 'MODVAT RECOVERY') THEN
414
415 ln_non_modvat_amount := ln_non_modvat_amount + tax_rec.tax_amount * ln_conv_factor;
416
417 end if;
418
419 end if;
420
421 END LOOP;
422
423
424 p_excise_amount := ln_modvat_amount;
425 p_non_modvat_amount := ln_non_modvat_amount;
426 p_other_modvat_amount := ln_other_modvat_amount;
427
428 EXCEPTION
429 WHEN OTHERS THEN
430 p_process_status := 'E';
431 p_process_message := 'get_tax_amount_breakup:' || sqlerrm;
432 END get_cost_amt;
433
434
435
436 PROCEDURE cenvat_process(
437 p_transaction_temp_id IN NUMBER,
438 p_transaction_type in varchar2,
439 p_excise_inv_no in varchar2, /*Added by nprashar for bug # 6710747*/
440 p_process_status OUT NOCOPY VARCHAR2,
441 p_process_message OUT NOCOPY VARCHAR2)
442 IS
443 CURSOR main_cur IS
444 SELECT trx.transaction_date,
445 trx.inventory_item_id,
446 trx.transaction_uom,
447 trx.transaction_type_id,
448 trx.from_organization,
449 trx.to_organization,
450 trx.to_subinventory,
451 trx.transaction_temp_id,
452 --trx.excise_invoice_no,/*Added by nprashar for bug 6710747*/
453 trx.assessable_value,
454 subinv.bonded,
455 itm.excise_flag,
456 itm.item_class,
457 itm.item_trading_flag,
458 subinv.trading,
459 trx.location_id,
460 trx.quantity,
461 trx.creation_date,
462 trx.created_by,
463 trx.last_update_date,
464 trx.last_update_login
465 FROM jai_mtl_trxs trx,
466 JAI_INV_SUBINV_DTLS subinv,
467 JAI_INV_ITM_SETUPS itm
468 WHERE subinv.organization_id = trx.to_organization
469 AND itm.organization_id = trx.to_organization
470 AND itm.inventory_item_id = trx.inventory_item_id
471 AND subinv.sub_inventory_name = trx.to_subinventory
472 AND trx.transaction_temp_id = p_transaction_temp_id
473 AND trx.quantity > 0 ;
474 /*Commented by nprashar for bug # 6710747 AND trx.excise_invoice_no IS NOT NULL;*/
475
476 CURSOR organization_type_cur(p_org_id number,p_loc_id number) IS
477 SELECT trading, manufacturing, excise_duty_range, excise_duty_division
478 FROM JAI_CMN_INVENTORY_ORGS
479 WHERE organization_id = p_org_id
480 AND location_id = p_loc_id;
481
482
483 CURSOR excise_cur(trx_temp_id number) IS
484 SELECT SUM(decode(tax_type, 'Excise', round(tax_amt), 0)) exc,
485 SUM(decode(tax_type, 'Addl. Excise', round(tax_amt), 0)) additional_ed,
486 SUM(decode(tax_type, 'Other Excise', round(tax_amt), 0)) other_ed,
487 SUM(decode(tax_type, jai_constants.tax_type_exc_edu_cess, round(tax_amt), 0)) other_cess,
488 sum(decode(tax_type, jai_constants.tax_type_sh_exc_edu_cess, round(tax_amt), 0)) other_sh_cess --Added higher education cess constants by vkaranam for bug#5907436
489 FROM jai_cmn_document_taxes tax,
490 jai_mtl_trxs trx
491 WHERE tax.source_doc_line_id = trx.transaction_temp_id
492 AND trx.transaction_header_id = tax.source_doc_id
493 AND trx.transaction_temp_id = trx_temp_id;
494
495 CURSOR c_excise_tax_rate(cp_temp_id number)
496 IS
497 SELECT NVL(sum(tax_rate),0) , count(1)
498 FROM jai_cmn_document_taxes
499 WHERE source_doc_line_id = cp_temp_id
500 AND TAX_TYPE in ('Addl. Excise','Excise','Other Excise');
501
502 cursor c_rcpts_match (cp_temp_id IN NUMBER) IS
503 select sum(a.quantity_applied) quantity_applied , sum(b.excise_duty_rate) excise_duty_rate
504 from JAI_CMN_MATCH_RECEIPTS a ,JAI_CMN_RG_23D_TRXS b
505 where a.receipt_id = b.register_id
506 and a.ref_line_id = cp_temp_id
507 and a.order_invoice = 'X';
508
509
510 r_rcpts_match c_rcpts_match%rowtype;
511
512 l_trading JAI_CMN_INVENTORY_ORGS.trading%TYPE;
513 l_manufacturing JAI_CMN_INVENTORY_ORGS.manufacturing%TYPE;
514 l_range JAI_CMN_RG_I_TRXS.range_no%TYPE;
515 l_division JAI_CMN_RG_I_TRXS.division_no%TYPE;
516 l_register_id JAI_CMN_RG_I_TRXS.register_id%TYPE;
517 l_register_id_ii JAI_CMN_RG_I_TRXS.register_id_part_ii%TYPE;
518 l_slno JAI_CMN_RG_I_TRXS.slno%TYPE;
519 l_fin_year JAI_CMN_FIN_YEARS.fin_year%TYPE;
520 l_register_type VARCHAR2(3);
521 l_excise_duty NUMBER;
522 l_additional_ed NUMBER;
523 l_other_ed NUMBER;
524 ln_cess_amount number;
525 ln_other_sh_cess number; --Added higher education cess by kundan kumar for bug#5907436
526 main_rec main_cur%ROWTYPE;
527 l_process_status VARCHAR2(5) DEFAULT NULL;
528 l_process_message VARCHAR2(256) DEFAULT NULL;
529 v_register_id number;
530 lv_source_name varchar2(10);
531 processed_flag VARCHAR2(32) := '0';
532 stmt_name VARCHAR2(64);
533 ln_total_tax_rate NUMBER;
534 ln_number_of_Taxes NUMBER;
535 v_tax_rate NUMBER;
536 l_duty_amt number;
537
538 BEGIN
539
540 stmt_name := 'Opening main_cur';
541 OPEN main_cur;
542 FETCH main_cur INTO main_rec;
543 IF main_cur%NOTFOUND THEN
544 stmt_name := 'Fetching main_cur Cursor';
545 RAISE NO_DATA_FOUND;
546 END IF;
547
548 stmt_name := 'Opening Organization_type_cur';
549 OPEN organization_type_cur( main_rec.to_organization ,main_rec.location_id );
550 FETCH organization_type_cur INTO l_trading, l_manufacturing, l_range, l_division;
551 IF organization_type_cur%NOTFOUND THEN
552 stmt_name := 'Fetching organization_type Cursor '||':'||main_rec.to_organization||':'||main_rec.location_id;
553 CLOSE organization_type_cur;
554 RAISE NO_DATA_FOUND;
555 END IF;
556 CLOSE organization_type_cur;
557
558 stmt_name := 'Selecting fin_year from JAI_CMN_FIN_YEARS';
559 SELECT fin_year
560 INTO l_fin_year
561 FROM JAI_CMN_FIN_YEARS
562 WHERE organization_id = main_rec.to_organization
563 AND fin_active_flag = 'Y';
564
565 stmt_name := 'Opening excise_cur';
566 OPEN excise_cur(main_rec.transaction_temp_id);
567 FETCH excise_cur INTO l_excise_duty, l_additional_ed, l_other_ed,ln_cess_amount,ln_other_sh_cess;
568 CLOSE excise_cur;
569
570 l_duty_amt:=nvl(l_excise_duty,0)+nvl(l_additional_ed,0)+nvl(l_other_ed,0);
571
572 OPEN c_excise_tax_rate(main_rec.transaction_temp_id);
573 FETCH c_excise_tax_rate INTO ln_total_tax_rate , ln_number_of_Taxes ;
574 CLOSE c_excise_tax_rate;
575
576 IF NVL(ln_number_of_Taxes,0) = 0 THEN
577 ln_number_of_Taxes := 1;
578 END IF;
579
580 v_tax_rate := ln_total_tax_rate / ln_number_of_Taxes;
581
582 IF main_rec.item_class IN ('FGIN','FGEX','CCEX','CCIN','RMIN', 'RMEX') THEN
583 l_register_type := 'A';
584 ELSE
585 l_register_type := 'C';
586 END IF;
587
588 IF l_manufacturing = 'Y' THEN
589 IF main_rec.excise_flag = 'Y' AND main_rec.bonded = 'Y' AND
590 main_rec.item_class IN ('FGIN','FGEX','CCEX','CCIN') THEN
591 stmt_name := 'Calling JAI_CMN_RG_I_TRXS_pkg.create_rg1_entry';
592 JAI_CMN_RG_I_TRXS_pkg.create_rg1_entry(
593 P_REGISTER_ID => l_register_id,
594 P_REGISTER_ID_PART_II => null,
595 P_FIN_YEAR => l_fin_year,
596 P_SLNO => l_slno,
597 P_TRANSACTION_ID => 3,
598 P_ORGANIZATION_ID => main_rec.to_organization,
599 P_LOCATION_ID => main_rec.location_id,
600 P_TRANSACTION_DATE => main_rec.transaction_date,
601 P_INVENTORY_ITEM_ID => main_rec.inventory_item_id,
602 P_TRANSACTION_TYPE => 'R',
603 P_REF_DOC_ID => main_rec.transaction_temp_id,
604 P_QUANTITY => main_rec.quantity,
605 P_TRANSACTION_UOM_CODE => main_rec.transaction_uom,
606 P_ISSUE_TYPE => null,
607 P_EXCISE_DUTY_AMOUNT => null,
608 P_EXCISE_INVOICE_NUMBER => p_excise_inv_no /*Replacing main_rec.excise_invoice_no for bug # 6710747*/,
609 P_EXCISE_INVOICE_DATE => null,
610 P_PAYMENT_REGISTER => null,
611 P_CHARGE_ACCOUNT_ID => null,
612 P_RANGE_NO => l_range,
613 P_DIVISION_NO => l_division,
614 P_REMARKS => 'Inter Org transfer from '||main_rec.from_organization||' To '||main_rec.to_organization,
615 P_BASIC_ED => l_excise_duty,
616 P_ADDITIONAL_ED => l_additional_ed,
617 P_OTHER_ED => l_other_ed,
618 P_ASSESSABLE_VALUE => main_rec.assessable_value,
619 P_EXCISE_DUTY_RATE => null,
620 P_VENDOR_ID => main_rec.to_organization,
621 P_VENDOR_SITE_ID => main_rec.location_id,
622 P_CUSTOMER_ID => null,
623 P_CUSTOMER_SITE_ID => null,
624 P_CREATION_DATE => main_rec.creation_date,
625 P_CREATED_BY => main_rec.created_by,
626 P_LAST_UPDATE_DATE => sysdate,
627 P_LAST_UPDATED_BY => fnd_global.user_id,
628 P_LAST_UPDATE_LOGIN => main_rec.last_update_login,
629 P_CALLED_FROM => 'XFER',
630 P_CESS_AMOUNT => ln_cess_amount ,
631 P_SH_CESS_AMOUNT =>ln_other_sh_cess
632 );
633 processed_flag :='RG1';
634 ELSIF main_rec.excise_flag = 'Y' AND main_rec.bonded = 'Y' AND
635 main_rec.item_class IN ('RMIN', 'RMEX', 'CGIN', 'CGEX') THEN
636 stmt_name := 'Calling jai_cmn_rg_23ac_i_trxs_pkg.insert_row';
637 jai_cmn_rg_23ac_i_trxs_pkg.insert_row(
638 P_REGISTER_ID => l_register_id,
639 P_INVENTORY_ITEM_ID => main_rec.inventory_item_id,
640 P_ORGANIZATION_ID => main_rec.to_organization,
641 P_QUANTITY_RECEIVED => main_rec.quantity,
642 P_RECEIPT_ID => main_rec.transaction_temp_id,
643 P_TRANSACTION_TYPE => 'RECEIVE', --Changes by nprashar for bug # 11886787,replaced transaction type R with RECEIVE
644 P_RECEIPT_DATE => main_rec.transaction_date,
645 P_PO_HEADER_ID => null,
646 P_PO_HEADER_DATE => null,
647 P_PO_LINE_ID => null,
648 P_PO_LINE_LOCATION_ID => null,
649 P_VENDOR_ID => main_rec.from_organization,
650 P_VENDOR_SITE_ID => NULL,
651 P_CUSTOMER_ID => null,
652 P_CUSTOMER_SITE_ID => null,
653 P_GOODS_ISSUE_ID => null,
654 P_GOODS_ISSUE_DATE => null,
655 P_GOODS_ISSUE_QUANTITY => null,
656 P_SALES_INVOICE_ID => null,
657 P_SALES_INVOICE_DATE => null,
658 P_SALES_INVOICE_QUANTITY => null,
659 P_EXCISE_INVOICE_ID => p_excise_inv_no /*Replacing main_rec.excise_invoice_no for bug # 6710747*/,
660 P_EXCISE_INVOICE_DATE => sysdate,
661 P_OTH_RECEIPT_QUANTITY => null,
662 P_OTH_RECEIPT_ID => null,
663 P_OTH_RECEIPT_DATE => null,
664 P_REGISTER_TYPE => l_register_type,
665 P_IDENTIFICATION_NO => null,
666 P_IDENTIFICATION_MARK => null,
667 P_BRAND_NAME => null,
668 P_DATE_OF_VERIFICATION => null,
669 P_DATE_OF_INSTALLATION => null,
670 P_DATE_OF_COMMISSION => null,
671 P_REGISER_ID_PART_II => null,
672 P_PLACE_OF_INSTALL => null,
673 P_REMARKS => 'Inter Org transfer from '||main_rec.from_organization||' To '||main_rec.to_organization,
674 P_LOCATION_ID => main_rec.location_id,
675 P_TRANSACTION_UOM_CODE => main_rec.transaction_uom,
676 P_TRANSACTION_DATE => main_rec.transaction_date,
677 P_BASIC_ED => l_excise_duty,
678 P_ADDITIONAL_ED => l_additional_ed,
679 P_ADDITIONAL_CVD => null,
680 P_OTHER_ED => l_other_ed,
681 P_CHARGE_ACCOUNT_ID => null,
682 P_TRANSACTION_SOURCE => null,
683 P_CALLED_FROM => 'XFER',
684 P_SIMULATE_FLAG => null,
685 P_PROCESS_STATUS => l_process_status,
686 P_PROCESS_MESSAGE => l_process_message
687 );
688 if l_process_status='E' then
689 app_exception.raise_exception;
690 end if;
691 processed_flag :='RG23';
692
693 ELSIF main_rec.bonded = 'N' THEN /*6487803*/
694 l_process_status := 'N'; /*Do Nothing for Non Bonded subinventory*/
695 l_process_message := NULL;
696 processed_flag := 'N';
697 END IF;
698 ELSIF l_trading = 'Y' AND main_rec.trading = 'Y' AND main_rec.item_trading_flag = 'Y' THEN
699 stmt_name := 'Calling jai_cmn_rg_23d_trxs_pkg.insert_row';
700
701 if main_Rec.quantity <> 0 then
702 v_tax_rate := round((l_duty_amt / main_Rec.quantity),2);
703 end if;
704 OPEN c_rcpts_match (main_rec.transaction_temp_id);
705 fetch c_rcpts_match INTO r_rcpts_match;
706 close c_rcpts_match;
707 if r_rcpts_match.quantity_applied <> 0 THEN
708 ln_total_tax_rate := round (( ( r_rcpts_match.excise_duty_rate *r_rcpts_match.quantity_applied ) / r_rcpts_match.quantity_applied),2);
709 end if;
710
711 jai_cmn_rg_23d_trxs_pkg.insert_row(
712 P_REGISTER_ID => l_register_id,
713 P_ORGANIZATION_ID => main_rec.to_organization,
714 P_LOCATION_ID => main_rec.location_id,
715 P_TRANSACTION_TYPE => 'R',
716 P_RECEIPT_ID => main_rec.transaction_temp_id,
717 P_QUANTITY_RECEIVED => main_rec.quantity,
718 P_INVENTORY_ITEM_ID => main_rec.inventory_item_id,
719 P_SUBINVENTORY => main_rec.to_subinventory,
720 P_REFERENCE_LINE_ID => null,
721 P_TRANSACTION_UOM_CODE => main_rec.transaction_uom,
722 P_CUSTOMER_ID => null,
723 P_BILL_TO_SITE_ID => null,
724 P_SHIP_TO_SITE_ID => null,
725 P_QUANTITY_ISSUED => null,
726 P_REGISTER_CODE => null,
727 P_RELEASED_DATE => null,
728 P_COMM_INVOICE_NO => p_excise_inv_no /*Replacing main_rec.excise_invoice_no for bug # 6710747*/,
729 P_COMM_INVOICE_DATE => sysdate,
730 P_RECEIPT_BOE_NUM => null,
731 P_OTH_RECEIPT_ID => null,
732 P_OTH_RECEIPT_DATE => null,
733 P_OTH_RECEIPT_QUANTITY => null,
734 P_REMARKS => 'Inter Org transfer from '||main_rec.from_organization||' To '||main_rec.to_organization,
735 P_QTY_TO_ADJUST => main_rec.quantity,
736 P_RATE_PER_UNIT => v_tax_rate,
737 P_EXCISE_DUTY_RATE => ln_total_tax_rate,
738 P_CHARGE_ACCOUNT_ID => null,
739 P_DUTY_AMOUNT => round(l_duty_amt,0),
740 P_RECEIPT_DATE => sysdate,
741 P_GOODS_ISSUE_ID => null,
742 P_GOODS_ISSUE_DATE => null,
743 P_GOODS_ISSUE_QUANTITY => null,
744 P_TRANSACTION_DATE => main_rec.transaction_date,
745 P_BASIC_ED => round(l_excise_duty,0),
746 P_ADDITIONAL_ED => round(l_additional_ed,0),
747 P_ADDITIONAL_CVD => null,
748 P_OTHER_ED => round(l_other_ed,0),
749 P_CVD => null,
750 P_VENDOR_ID => main_rec.from_organization,
751 P_VENDOR_SITE_ID => NULL,
752 P_RECEIPT_NUM => null,
753 P_ATTRIBUTE1 => null,
754 P_ATTRIBUTE2 => null,
755 P_ATTRIBUTE3 => null,
756 P_ATTRIBUTE4 => null,
757 P_ATTRIBUTE5 => null,
758 P_CONSIGNEE => null,
759 P_MANUFACTURER_NAME => null,
760 P_MANUFACTURER_ADDRESS => null,
761 P_MANUFACTURER_RATE_AMT_PER_UN => null,
762 P_QTY_RECEIVED_FROM_MANUFACTUR => null,
763 P_TOT_AMT_PAID_TO_MANUFACTURER => null,
764 P_OTHER_TAX_CREDIT => NVL(ln_cess_amount,0)+NVL(ln_other_sh_cess,0) ,--ADDED ln_other_sh_cessby vkaranam for bug #5907436
765 P_OTHER_TAX_DEBIT => null,
766 P_TRANSACTION_SOURCE => p_transaction_type,
767 P_CALLED_FROM => 'XFER',
768 P_SIMULATE_FLAG => null,
769 P_PROCESS_STATUS => l_process_status,
770 P_PROCESS_MESSAGE => l_process_message
771 );
772 processed_flag :='RG23D';
773
774 -- rchandan for bug#6487364 start
775
776 IF NVL(ln_cess_amount,0) <> 0 then
777
778 stmt_name := 'Calling jai_cmn_rg_others_pkg.insert_row for Cess of RG23D';
779
780 jai_cmn_rg_others_pkg.insert_row(p_source_type => 3,
781 p_source_name => 'RG23D',
782 p_source_id => l_register_id,
783 p_tax_type => jai_constants.tax_type_exc_edu_cess,
784 debit_amt => NULL,
785 credit_amt => ln_cess_amount,
786 p_process_flag => p_process_status,
787 p_process_msg => p_process_message
788 );
789 IF p_process_status <> 'SS' THEN
790 app_exception.raise_exception;
791 END IF;
792
793 END IF;
794 IF NVL(ln_other_sh_cess,0) <> 0 then
795
796 stmt_name := 'Calling jai_cmn_rg_others_pkg.insert_row for SH Cess of RG23D';
797
798 jai_cmn_rg_others_pkg.insert_row(p_source_type => 3,
799 p_source_name => 'RG23D',
800 p_source_id => l_register_id,
801 p_tax_type => jai_constants.tax_type_sh_exc_edu_cess,
802 debit_amt => NULL,
803 credit_amt => ln_other_sh_cess,
804 p_process_flag => p_process_status,
805 p_process_msg => p_process_message
806 );
807 IF p_process_status <> 'SS' THEN
808 app_exception.raise_exception;
809 END IF;
810
811 END IF;
812
813 -- rchandan for bug#6487364 end
814
815 END IF;
816 IF processed_flag = '0' THEN
817 l_process_status := 'E';
818 l_process_message := 'No data Processed';
819 ELSIF processed_flag IN ('RG1','RG23') and main_rec.item_class NOT IN ('FGIN', 'FGEX') THEN /*6501436*/
820 stmt_name := 'Calling jai_cmn_rg_23ac_ii_pkg.insert_row';
821 jai_cmn_rg_23ac_ii_pkg.insert_row(
822 P_REGISTER_ID => l_register_id_ii,
823 P_INVENTORY_ITEM_ID => main_rec.inventory_item_id,
824 P_ORGANIZATION_ID => main_rec.to_organization,
825 P_RECEIPT_ID => main_rec.transaction_temp_id,
826 P_RECEIPT_DATE => null,
827 P_CR_BASIC_ED => l_excise_duty,
828 P_CR_ADDITIONAL_ED => l_additional_ed,
829 P_CR_ADDITIONAL_CVD => null,
830 P_CR_OTHER_ED => l_other_ed,
831 P_DR_BASIC_ED => null,
832 P_DR_ADDITIONAL_ED => null,
833 P_DR_ADDITIONAL_CVD => null,
834 P_DR_OTHER_ED => null,
835 P_EXCISE_INVOICE_NO => p_excise_inv_no /*Replacing main_rec.excise_invoice_no for bug # 6710747*/,
836 P_EXCISE_INVOICE_DATE => sysdate,
837 P_REGISTER_TYPE => l_register_type,
838 P_REMARKS => 'Inter Org transfer from '||main_rec.from_organization||' To '||main_rec.to_organization,
839 P_VENDOR_ID => main_rec.from_organization,
840 P_VENDOR_SITE_ID => NULL,
841 P_CUSTOMER_ID => null,
842 P_CUSTOMER_SITE_ID => null,
843 P_LOCATION_ID => main_rec.location_id,
844 P_TRANSACTION_DATE => main_rec.transaction_date,
845 P_CHARGE_ACCOUNT_ID => null,
846 P_REGISTER_ID_PART_I => l_register_id ,
847 P_REFERENCE_NUM => null,
848 P_ROUNDING_ID => null,
849 P_OTHER_TAX_CREDIT =>nvl(ln_cess_amount,0)+NVL(ln_other_sh_cess,0) ,--ADDED ln_other_sh_cessby vkaranam for bug #5907436,
850 P_OTHER_TAX_DEBIT => null,
851 p_transaction_type => 'R',
852 P_TRANSACTION_SOURCE => null,
853 P_CALLED_FROM => null,
854 P_SIMULATE_FLAG => null,
855 P_PROCESS_STATUS => l_process_status,
856 P_PROCESS_MESSAGE => l_process_message
857 );
858 IF processed_flag = 'RG1' THEN
859 UPDATE JAI_CMN_RG_I_TRXS
860 SET register_id_part_ii = l_register_id_ii,cess_amt = ln_Cess_amount
861 WHERE register_id = l_register_id;
862 ELSE
863 UPDATE JAI_CMN_RG_23AC_I_TRXS SET register_id_part_ii = l_register_id_ii WHERE register_id = l_register_id;
864 END IF;
865 IF SQL%NOTFOUND THEN
866 stmt_name := 'UPDATE register_id = '||l_register_id||': processed_flag = '||processed_flag;
867 RAISE no_data_found;
868 END IF;
869 IF nvl(ln_cess_amount,0)<>0 THEN
870 /*
871 BEGIN
872 SELECT JAI_CMN_RG_23AC_I_TRXSI_S.CURRVAL INTO v_register_id FROM dual;
873 EXCEPTION when others THEN
874 SELECT JAI_CMN_RG_23AC_I_TRXSI_S.CURRVAL INTO v_register_id FROM dual;
875 END;
876 *//*commented the above by vkaranam for bug #5907436*/
877 BEGIN
878 stmt_name := 'Calling jai_cmn_rg_others_pkg.insert_row';
879 if l_register_type='A' THEN
880 lv_source_name:='RG23A_P2';
881 elsif l_register_type='C' THEN
882 lv_source_name:='RG23C_P2';
883 end if;
884 jai_cmn_rg_others_pkg.insert_row(
885 P_SOURCE_TYPE => 1 ,
886 P_SOURCE_NAME => lv_source_name ,
887 P_SOURCE_ID => l_register_id_ii ,
888 P_TAX_TYPE => 'EXCISE_EDUCATION_CESS' ,
889 DEBIT_AMT =>null ,
890 CREDIT_AMT =>ln_cess_amount ,
891 P_PROCESS_FLAG =>l_process_status ,
892 P_PROCESS_MSG =>l_process_message
893 );
894 END;
895
896 END IF;
897 /*added the following for shcess by vkaranam for bug #5907436*/
898 --start 5907436
899 IF nvl(ln_other_sh_cess,0)<>0 THEN
900 BEGIN
901 stmt_name := 'Calling jai_cmn_rg_others_pkg.insert_row';
902 if l_register_type='A' THEN
903 lv_source_name:='RG23A_P2';
904 elsif l_register_type='C' THEN
905 lv_source_name:='RG23C_P2';
906 end if;
907 jai_cmn_rg_others_pkg.insert_row(
908 P_SOURCE_TYPE => 1 ,
909 P_SOURCE_NAME => lv_source_name ,
910 P_SOURCE_ID => l_register_id_ii ,
911 P_TAX_TYPE => jai_constants.tax_type_sh_exc_edu_cess ,
912 DEBIT_AMT =>null ,
913 CREDIT_AMT =>ln_other_sh_cess ,
914 P_PROCESS_FLAG =>l_process_status ,
915 P_PROCESS_MSG =>l_process_message
916 );
917 END;
918 --end bug #5907436
919
920 END IF;
921
922
923 END IF;
924 CLOSE main_cur;
925 p_process_status := l_process_status;
926 p_process_message := l_process_message;
927 EXCEPTION
928 WHEN OTHERS THEN
929 p_process_status := 'E';
930 p_process_message := 'Encounterd an error when doing '||stmt_name||' :'||sqlcode||': '||l_process_message;
931 END;
932 PROCEDURE recv_vat_process(p_organization_id IN NUMBER,
933 p_location_id IN NUMBER,
934 p_Set_of_books_id IN number,
935 p_currency in varchar2,
936 p_transaction_header_id IN NUMBER,
937 p_transaction_temp_id IN NUMBER,
938 p_vat_invoice_no IN VARCHAR2,
939 p_process_status OUT NOCOPY VARCHAR2,
940 p_process_message OUT NOCOPY VARCHAR2)
941 IS
942 ln_regime_id NUMBER;
943 lv_inv_gen_process_flag VARCHAR2(10);
944 lv_inv_gen_process_message VARCHAR2(2000);
945 ln_repository_id NUMBER;
946 lv_source_trx_type VARCHAR2(30):='RECEIVING';
947 table_rcv_transactions VARCHAR2(30):= 'JAI_MTL_TRXS';
948 lv_account_name VARCHAR2(50);
949 ln_code_combination_id NUMBER;
950 ln_interim_recovery_account NUMBER;
951 ln_entered_dr NUMBER;
952 ln_entered_cr NUMBER;
953 lv_process_status VARCHAR2(2);
954 lv_process_message VARCHAR2(1000);
955 v_source_name VARCHAR2(100) := 'Register India' ; -- bug 6487405
956 v_category_name VARCHAR2(100) := 'VAT India' ; -- bug 6487405
957
958 --regime varchar2(100):=jai_constants.vat_regime;
959 CURSOR c_regime_cur IS
960 SELECT regime_id
961 FROM jai_rgm_definitions
962 WHERE regime_code = 'VAT';
963 /*added the below cursor for performance issue*/
964 --start
965 CURSOR c_chk_rgm_trxs(cp_transaction_header_id in number,cp_transaction_temp_id in number,cp_tax_id in number) IS
966 SELECT 1
967 FROM
968 jai_rgm_trx_records jrtr
969 WHERE
970 jrtr.attribute1 = cp_transaction_header_id AND
971 jrtr.source_document_id = cp_transaction_temp_id AND
972 jrtr.reference_id = cp_tax_id AND
973 jrtr.organization_id=p_organization_id AND
974 jrtr.location_id=p_location_id;
975 CURSOR get_tax_id(cp_transaction_header_id in number,cp_transaction_temp_id in number)
976 IS
977 select tax_id
978 from jai_cmn_document_taxes
979 where source_doc_id=cp_transaction_header_id
980 and source_doc_line_id=cp_transaction_temp_id
981 and source_doc_type='INTERORG_XFER';
982 ---END----
983 CURSOR cur_get_mtltxns
984 IS
985 SELECT
986 jtc.tax_type,
987 jtc.tax_rate,
988 --jtc.tax_id,
989 jmt.transaction_temp_id,
990 jmt.transaction_header_id,
991 --jmt.creation_date,
992 sum(jcdt.tax_amt) tax_amt
993 FROM
994 jai_mtl_trxs jmt,
995 jai_cmn_document_taxes jcdt,
996 jai_cmn_taxes_all jtc,
997 jai_rgm_registrations jrg,
998 jai_rgm_definitions jrr
999 WHERE
1000 jmt.to_organization = p_organization_id
1001 AND jmt.location_id = p_location_id
1002 AND jmt.transaction_header_id = p_transaction_header_id
1003 AND jmt.transaction_temp_id=p_transaction_temp_id
1004 AND jmt.transaction_header_id=jcdt.source_doc_id
1005 AND jmt.transaction_temp_id=jcdt.source_doc_line_id
1006 AND jcdt.tax_id=jtc.tax_id
1007 AND jtc.tax_type= jrg.attribute_code -- bug 6436781
1008 AND jrr.regime_code = jai_constants.vat_regime
1009 AND jrg.regime_id = jrr.regime_id
1010 AND jrg.registration_type = 'TAX_TYPES'
1011 AND upper(jrg.attribute_code) <> 'VAT REVERSAL'
1012 GROUP BY jtc.tax_type,
1013 jtc.tax_rate,
1014 jmt.transaction_temp_id,
1015 jmt.transaction_header_id;
1016
1017
1018 stmt_name VARCHAR2(64);
1019 ln_rgm_cnt number;--bug #6030615
1020 r_get_tax_id get_tax_id%rowtype;--bug #6030615
1021 BEGIN
1022 OPEN c_regime_cur;
1023 FETCH c_regime_cur into ln_regime_id;
1024 CLOSE c_regime_cur;
1025 FOR rec_claims IN cur_get_mtltxns
1026 LOOP
1027 ln_rgm_cnt:=0;
1028 FOR r_get_tax_id in get_tax_id(rec_claims.transaction_header_id,rec_claims.transaction_temp_id)
1029 LOOP
1030 open c_chk_rgm_trxs(rec_claims.transaction_header_id,rec_claims.transaction_temp_id,r_get_tax_id.tax_id);
1031 fetch c_chk_rgm_trxs into ln_rgm_cnt;
1032 close c_chk_rgm_trxs;
1033 if ln_rgm_cnt=1 then
1034 exit;
1035 end if;
1036 END LOOP;
1037 --added this if condition for performance issue
1038 if nvl(ln_rgm_cnt,0) = 0 then
1039 lv_account_name := jai_constants.recovery;
1040 stmt_name:='Getting the interim recovery amount';
1041 ln_interim_recovery_account :=
1042 jai_cmn_rgm_recording_pkg.get_account(
1043 p_regime_id => ln_regime_id,
1044 p_organization_type => jai_constants.orgn_type_io,
1045 p_organization_id => p_organization_id,
1046 p_location_id => p_location_id,
1047 p_tax_type => rec_claims.tax_type,
1048 p_account_name => jai_constants.recovery_interim);
1049 IF ln_interim_recovery_account IS NULL THEN
1050 p_process_status := jai_constants.expected_error;
1051 p_process_message := 'Interim recovery Account not defined in VAT Setup';
1052 RETURN;
1053 END IF;
1054 stmt_name:='Getting the code combination id';
1055 ln_code_combination_id :=
1056 jai_cmn_rgm_recording_pkg.get_account(
1057 p_regime_id => ln_regime_id,
1058 p_organization_type => jai_constants.orgn_type_io,
1059 p_organization_id => p_organization_id,
1060 p_location_id => p_location_id,
1061 p_tax_type => rec_claims.tax_type,
1062 p_account_name => jai_constants.recovery);
1063 IF ln_code_combination_id IS NULL THEN
1064 p_process_status := jai_constants.expected_error;
1065 p_process_message := 'Recovery Account not defined in VAT Setup';
1066 RETURN;
1067 END IF;
1068 ln_entered_dr := NULL;
1069 ln_entered_cr := rec_claims.tax_amt;
1070 IF ln_entered_cr < 0 THEN
1071 ln_entered_dr := ln_entered_cr*-1;
1072 ln_entered_cr := NULL;
1073 END IF;
1074 stmt_name:='Calling insert vat repository entry';
1075 jai_cmn_rgm_recording_pkg.insert_vat_repository_entry(
1076 pn_repository_id => ln_repository_id,
1077 pn_regime_id => ln_regime_id,
1078 pv_tax_type => rec_claims.tax_type,
1079 pv_organization_type => jai_constants.orgn_type_io,
1080 pn_organization_id => p_organization_id,
1081 pn_location_id => p_location_id,
1082 pv_source => jai_constants.source_rcv,
1083 pv_source_trx_type => lv_source_trx_type,
1084 pv_source_table_name => table_rcv_transactions,
1085 pn_source_id => p_transaction_temp_id,
1086 pd_transaction_date => trunc(sysdate),
1087 pv_account_name => lv_account_name,
1088 pn_charge_account_id => ln_code_combination_id,
1089 pn_balancing_account_id => ln_interim_recovery_account,
1090 pn_credit_amount => ln_entered_cr,
1091 pn_debit_amount => ln_entered_dr,
1092 pn_assessable_value => NULL,
1093 pn_tax_rate => NULL,
1094 pn_reference_id => p_transaction_temp_id,/*r_claim_schedule.claim_schedule_id,*/
1095 pn_batch_id => NULL,
1096 pn_inv_organization_id => P_organization_id,
1097 pv_invoice_no => p_vat_invoice_no,
1098 pd_invoice_date => trunc(sysdate),
1099 pv_called_from => 'JAINVMTX',
1100 pv_process_flag => lv_process_status,
1101 pv_process_message => lv_process_message,
1102 --Added by Bo Li for bug9305067 2010-4-14 BEGIN
1103 --------------------------------------------------
1104 pv_trx_reference_context => NULL,
1105 pv_trx_reference1 => NULL,
1106 pv_trx_reference2 => NULL,
1107 pv_trx_reference3 => NULL,
1108 pv_trx_reference4 => NULL,
1109 pv_trx_reference5 => NULL
1110 --------------------------------------------------
1111 --Added by Bo Li for bug9305067 2010-4-14 END
1112 );
1113 IF lv_process_status <> jai_constants.successful THEN
1114 p_process_status := lv_process_status;
1115 p_process_message := lv_process_message;
1116 RETURN;
1117 END IF;
1118 begin
1119 jai_mtl_trxs_pkg.do_cenvat_Acctg(
1120 p_set_of_books_id => p_set_of_books_id ,
1121 p_transaction_temp_id =>p_transaction_temp_id ,
1122 p_je_source_name =>v_source_name ,
1123 p_je_category_name =>v_category_name,
1124 p_currency_code => p_currency ,
1125 p_register_type => 'RVAT',
1126 p_process_status => lv_process_status,
1127 p_process_message =>lv_process_message
1128 );
1129 if lv_process_message is not null then
1130 p_process_message:=lv_process_message;
1131 app_exception.raise_exception;
1132 end if;
1133 exception when others then
1134 app_exception.raise_exception;
1135 end;
1136
1137
1138 /*COMMENTED BY VASAVI*/
1139 ---CR Vat Recovery
1140 /*
1141 ln_entered_dr := NULL;
1142 ln_entered_cr := rec_claims.tax_amt;
1143 stmt_name:='calling do vat accounting procedure for credit';
1144 jai_cmn_rgm_recording_pkg.do_vat_accounting(
1145 pn_regime_id => ln_regime_id,
1146 pn_repository_id => ln_repository_id,
1147 pv_organization_type => jai_constants.orgn_type_io,
1148 pn_organization_id => p_organization_id,
1149 pd_accounting_date => trunc(sysdate),
1150 pd_transaction_date => trunc(sysdate),
1151 pn_credit_amount => ln_entered_cr,
1152 pn_debit_amount => ln_entered_dr,
1153 pn_credit_ccid => ln_interim_recovery_account ,
1154 pn_debit_ccid => ln_code_combination_id,
1155 pv_called_from => NULL,
1156 pv_process_flag => lv_process_status,
1157 pv_process_message => lv_process_message,
1158 pv_tax_type => rec_claims.tax_type,
1159 pv_source => jai_constants.source_rcv,
1160 pv_source_trx_type => lv_source_trx_type,
1161 pv_source_table_name => table_rcv_transactions,
1162 pn_source_id => p_transaction_temp_id,
1163 pv_reference_name => NULL,
1164 pn_reference_id => NULL
1165 );
1166 ---Dr Vat Recovery
1167 ln_entered_dr := rec_claims.tax_amt;
1168 ln_entered_cr:= NULL;
1169 stmt_name:='calling do vat accounting procedure for debit';
1170 jai_cmn_rgm_recording_pkg.do_vat_accounting(
1171 pn_regime_id => ln_regime_id,
1172 pn_repository_id => ln_repository_id,
1173 pv_organization_type => jai_constants.orgn_type_io,
1174 pn_organization_id => p_organization_id,
1175 pd_accounting_date => trunc(sysdate),
1176 pd_transaction_date => trunc(sysdate),
1177 pn_credit_amount => ln_entered_cr,
1178 pn_debit_amount => ln_entered_dr,
1179 pn_credit_ccid => ln_interim_recovery_account ,
1180 pn_debit_ccid => ln_code_combination_id,
1181 pv_called_from => NULL,
1182 pv_process_flag => lv_process_status,
1183 pv_process_message => lv_process_message,
1184 pv_tax_type => rec_claims.tax_type,
1185 pv_source => jai_constants.source_rcv,
1186 pv_source_trx_type => lv_source_trx_type,
1187 pv_source_table_name => table_rcv_transactions,
1188 pn_source_id => p_transaction_temp_id,
1189 pv_reference_name => NULL,
1190 pn_reference_id => NULL
1191 );
1192 */
1193 end if; --ln_rgm_cnt
1194 END LOOP;
1195 EXCEPTION
1196 WHEN OTHERS THEN
1197 p_process_status := 'E';
1198 p_process_message := 'SQL error '||stmt_name||' :'||sqlcode||': '||sqlerrm;
1199 END;
1200
1201 procedure cenvat_recpt_det(block_data in out NOCOPY claimcur,
1202 p_organization_id in number)
1203 is
1204 begin
1205 open block_data for
1206 select rcv.receipt_num,
1207 rcv.quantity,
1208 cen.cenvat_claimed_ptg,
1209 cen.quantity_for_2nd_claim,
1210 cen.cenvat_amt_for_2nd_claim,
1211 cen.shipment_line_id,
1212 cen.cenvat_claimed_amt,
1213 cen.other_cenvat_claimed_amt ,
1214 cen.other_cenvat_amt_for_2nd_claim,
1215 cen.cenvat_amount,
1216 cen.other_cenvat_amt,
1217 cen.transaction_id ,
1218 rcv.excise_invoice_no ,
1219 rcv.excise_invoice_date
1220 from
1221 JAI_RCV_TRANSACTIONS rcv,
1222 JAI_RCV_CENVAT_CLAIMS cen
1223 where
1224 rcv.shipment_line_id=cen.shipment_line_id
1225 and rcv.item_class in ('CGIN','CGEX')
1226 and rcv.transaction_type='RECEIVE'
1227 and cen.cenvat_claimed_ptg<>0
1228 and cen.quantity_for_2nd_claim is not null
1229 and organization_id=p_organization_id
1230 order by receipt_num;
1231 end cenvat_recpt_det;
1232
1233 PROCEDURE gl_entry(p_params IN gl_entries,
1234 p_set_of_books_id IN NUMBER,
1235 p_je_source_name IN VARCHAR2,
1236 p_je_category_name IN VARCHAR2,
1237 p_currency_code IN VARCHAR2,
1238 p_transaction_temp_id IN NUMBER,
1239 p_process_status OUT NOCOPY VARCHAR2,
1240 p_process_message OUT NOCOPY VARCHAR2)
1241 IS
1242 stmt_name VARCHAR2(256);
1243 BEGIN
1244
1245 FOR i IN 0..p_params.COUNT-1
1246 LOOP
1247
1248 IF p_params(i).amount IS NOT NULL AND
1249 p_params(i).debit_account IS NOT NULL AND
1250 p_params(i).credit_account IS NOT NULL
1251 THEN
1252 stmt_name := 'Calling insert into gl_interface debit';
1253 insert into gl_interface
1254 ( status,
1255 set_of_books_id,
1256 user_je_source_name,
1257 user_je_category_name,
1258 accounting_date,
1259 currency_code,
1260 date_created,
1261 created_by,
1262 actual_flag,
1263 entered_cr,
1264 entered_dr,
1265 transaction_date,
1266 code_combination_id,
1267 currency_conversion_date,
1268 user_currency_conversion_type,
1269 currency_conversion_rate,
1270 reference1,
1271 reference10,
1272 reference23,
1273 reference24,
1274 reference25,
1275 reference26,
1276 reference27,
1277 reference22
1278 )
1279 VALUES
1280 ('NEW',
1281 p_set_of_books_id,
1282 p_je_source_name,
1283 p_je_category_name,
1284 sysdate,
1285 p_currency_code,
1286 sysdate,
1287 fnd_global.user_id,
1288 'A',
1289 null,
1290 p_params(i).amount,
1291 sysdate,
1292 p_params(i).debit_account, -- Derived Value from JAI_CMN_INVENTORY_ORGS / mtl_interorg_parameters
1293 null,
1294 null,
1295 null,
1296 p_params(i).organization_code, -- From mtl_parameters
1297 'India Localization Entry for Interorg-XFER ',
1298 'jai_mtl_trx_pkg.do_cenvat_Acctg',
1299 'jai_mtl_trxs',
1300 p_transaction_temp_id,
1301 'transaction_temp_id',
1302 to_char(p_params(i).organization_id),
1303 'India Localization Entry' -- bug 6487405
1304 );
1305
1306
1307 stmt_name := 'Calling insert into jai_mtl_trx_jrnls debit';
1308 insert into jai_mtl_trx_jrnls
1309 (journal_entry_id,
1310 status,
1311 set_of_books_id,
1312 user_je_source_name,
1313 user_je_category_name,
1314 accounting_date,
1315 currency_code,
1316 date_created,
1317 created_by,
1318 entered_cr,
1319 entered_dr,
1320 transaction_date,
1321 code_combination_id,
1322 currency_conversion_date,
1323 user_currency_conversion_type,
1324 currency_conversion_rate,
1325 reference1,
1326 reference10,
1327 reference23,
1328 reference24,
1329 reference25,
1330 reference26,
1331 reference27,
1332 creation_Date,
1333 last_updated_by,
1334 last_update_date,
1335 last_update_login,
1336 transaction_temp_id
1337
1338 )
1339 VALUES
1340 (jai_mtl_trx_jrnls_s.nextval,
1341 'NEW',
1342 p_set_of_books_id,
1343 p_je_source_name,
1344 p_je_category_name,
1345 sysdate,
1346 p_currency_code,
1347 sysdate,
1348 fnd_global.user_id,
1349 null,
1350 p_params(i).amount,
1351 sysdate,
1352 p_params(i).debit_account, -- Derived Value from JAI_CMN_INVENTORY_ORGS / mtl_interorg_parameters
1353 null,
1354 null,
1355 null,
1356 p_params(i).organization_code, -- From mtl_parameters
1357 'India Localization Entry for Interorg-XFER ',
1358 'jai_mtl_trx_pkg.do_cenvat_Acctg',
1359 'jai_mtl_trxs',
1360 p_transaction_temp_id,
1361 'transaction_temp_id',
1362 to_char(p_params(i).organization_id),
1363 sysdate,
1364 fnd_global.user_id,
1365 sysdate,
1366 fnd_global.login_id,
1367 p_transaction_temp_id
1368 );
1369
1370
1371 stmt_name := 'Calling insert int gl_interface credit';
1372 insert into gl_interface
1373 ( status,
1374 set_of_books_id,
1375 user_je_source_name,
1376 user_je_category_name,
1377 accounting_date,
1378 currency_code,
1379 date_created,
1380 created_by,
1381 actual_flag,
1382 entered_cr,
1383 entered_dr,
1384 transaction_date,
1385 code_combination_id,
1386 currency_conversion_date,
1387 user_currency_conversion_type,
1388 currency_conversion_rate,
1389 reference1,
1390 reference10,
1391 reference23,
1392 reference24,
1393 reference25,
1394 reference26,
1395 reference27,
1396 reference22
1397 )
1398 VALUES
1399 ('NEW',
1400 p_set_of_books_id,
1401 p_je_source_name,
1402 p_je_category_name,
1403 sysdate,
1404 p_currency_code,
1405 sysdate,
1406 fnd_global.user_id,
1407 'A',
1408 p_params(i).amount,
1409 null,
1410 sysdate,
1411 p_params(i).credit_account,
1412 null,
1413 null,
1414 null,
1415 p_params(i).organization_code,
1416 'India Localization Entry for Interorg-XFER ',
1417 'jai_mtl_trx_pkg.do_cenvat_Acctg',
1418 'jai_mtl_trxs',
1419 p_transaction_temp_id,
1420 'transaction_temp_id',
1421 to_char(p_params(i).organization_id),
1422 'India Localization Entry' -- bug 6487405
1423 );
1424
1425
1426 stmt_name := 'Calling insert int jai_mtl_trx_jrnls credit';
1427 insert into jai_mtl_trx_jrnls
1428 (journal_entry_id,
1429 status,
1430 set_of_books_id,
1431 user_je_source_name,
1432 user_je_category_name,
1433 accounting_date,
1434 currency_code,
1435 date_created,
1436 created_by,
1437 entered_cr,
1438 entered_dr,
1439 transaction_date,
1440 code_combination_id,
1441 currency_conversion_date,
1442 user_currency_conversion_type,
1443 currency_conversion_rate,
1444 reference1,
1445 reference10,
1446 reference23,
1447 reference24,
1448 reference25,
1449 reference26,
1450 reference27,
1451 creation_Date,
1452 last_updated_by,
1453 last_update_date,
1454 last_update_login,
1455 transaction_temp_id
1456 )
1457 VALUES
1458 (jai_mtl_trx_jrnls_s.nextval,
1459 'NEW',
1460 p_set_of_books_id,
1461 p_je_source_name,
1462 p_je_category_name,
1463 sysdate,
1464 p_currency_code,
1465 sysdate,
1466 fnd_global.user_id,
1467 p_params(i).amount,
1468 null,
1469 sysdate,
1470 p_params(i).credit_account,
1471 null,
1472 null,
1473 null,
1474 p_params(i).organization_code,
1475 'India Localization Entry for Interorg-XFER ',
1476 'jai_mtl_trx_pkg.do_cenvat_Acctg',
1477 'jai_mtl_trxs',
1478 p_transaction_temp_id,
1479 'transaction_temp_id',
1480 to_char(p_params(i).organization_id),
1481 sysdate,
1482 fnd_global.user_id,
1483 sysdate,
1484 fnd_global.login_id,
1485 p_transaction_temp_id
1486 );
1487
1488 END IF;
1489 END LOOP;
1490
1491 EXCEPTION
1492 WHEN OTHERS THEN
1493 p_process_status := 'E';
1494 p_process_message := 'Stmt'||stmt_name||'-'||sqlcode||':'||sqlerrm;
1495 ROLLBACK TO gl_acctg;
1496 END;
1497
1498
1499
1500 PROCEDURE do_cenvat_Acctg(
1501 p_set_of_books_id IN NUMBER,
1502 p_transaction_temp_id IN NUMBER,
1503 p_je_source_name IN VARCHAR2,
1504 p_je_category_name IN VARCHAR2,
1505 p_currency_code IN VARCHAR2,
1506 p_register_type IN VARCHAR2,
1507 p_process_status OUT NOCOPY VARCHAR2,
1508 p_process_message OUT NOCOPY VARCHAR2
1509 )
1510 IS
1511 CURSOR main_cur IS
1512 SELECT A.Tax_Id,
1513 DECODE(aa.regime_code, 'VAT', 4, DECODE( UPPER( A.Tax_Type ),
1514 'EXCISE', 1,
1515 'ADDL. EXCISE', 1,
1516 'OTHER EXCISE', 1,
1517 jai_constants.tax_type_exc_edu_cess, 5,jai_constants.tax_type_sh_exc_edu_cess,6, /*changed taxtype_val to 6 for sh_cess by vkaranam for bug #5907436*/--Added higher education cess by kundan kumar for bug#5907436
1518
1519 'TDS', 2, 0)) tax_type_val,
1520 A.Tax_Amt tax_amount,
1521 b.tax_account_id ,
1522 A.Tax_Type tax_type,
1523 d.from_organization ,
1524 d.from_subinventory ,
1525 d.to_organization ,
1526 d.to_subinventory ,
1527 d.location_id,
1528 d.inventory_item_id
1529 FROM Jai_cmn_document_Taxes A,
1530 jai_cmn_taxes_all B,
1531 jai_mtl_trxs D,
1532 jai_regime_tax_types_v aa
1533 WHERE source_doc_line_id = p_transaction_temp_id
1534 AND d.transaction_temp_id = p_transaction_temp_id
1535 AND a.source_doc_type = 'INTERORG_XFER'
1536 AND A.Tax_Id = B.Tax_Id
1537 AND aa.tax_type(+) = b.tax_type;
1538
1539
1540 l_process_status VARCHAR2(5) DEFAULT NULL;
1541 l_process_message VARCHAR2(256) DEFAULT NULL;
1542 processed_flag VARCHAR2(32) := '0';
1543 stmt_name VARCHAR2(256);
1544
1545 ln_from_loc_id NUMBER;
1546
1547 -- Parameter Table
1548 t_gl_entries gl_entries;
1549 rec NUMBER := 0;
1550 /*
1551 ccids from organization additional information setup
1552 */
1553 r_from_ja_in_hr_org JAI_CMN_INVENTORY_ORGS%rowtype;
1554 r_to_ja_in_hr_org JAI_CMN_INVENTORY_ORGS%rowtype;
1555
1556 /*
1557 ccids from mtl interorg parameters
1558 */
1559 r_mtl_interorg mtl_interorg_parameters%rowtype;
1560
1561 /*
1562 cc id of inventory recvng accnt
1563 */
1564 ln_inv_recvng NUMBER;
1565
1566
1567
1568 CURSOR c_get_location(p_organization_id IN NUMBER , p_subinventory IN VARCHAR2)
1569 IS
1570 SELECT location_id
1571 FROM JAI_INV_SUBINV_DTLS
1572 WHERE organization_id = p_organization_id
1573 AND sub_inventory_name = p_subinventory;
1574
1575 CURSOR c_get_ja_accts(p_organization_id NUMBER , p_location_id number) IS
1576 SELECT *
1577 FROM JAI_CMN_INVENTORY_ORGS
1578 WHERE organization_id = p_organization_id
1579 AND location_id = p_location_id;
1580
1581 CURSOR c_get_interorg_params( p_from_organization_id IN NUMBER, p_to_organization_id IN NUMBER ) IS
1582 SELECT *
1583 FROM mtl_interorg_parameters
1584 WHERE from_organization_id = p_from_organization_id
1585 AND to_organization_id = p_to_organization_id ;
1586
1587 CURSOR c_get_trx_info IS
1588 SELECT * FROM jai_mtl_trxs
1589 WHERE transaction_temp_id = p_transaction_temp_id;
1590
1591 CURSOR c_rcv_params (p_organization_id IN NUMBER) IS
1592 select receiving_account_id
1593 FROM rcv_parameters
1594 WHERE organization_id = p_organization_id;
1595
1596 CURSOR c_org_code(p_org_id IN NUMBER) IS
1597 SELECT organization_code
1598 FROM mtl_parameters
1599 WHERE organization_id = p_org_id;
1600
1601 CURSOR c_item_class (cp_inv_item_id IN NUMBER , cp_orgn_id IN NUMBER) IS
1602 select item_class
1603 from JAI_INV_ITM_SETUPS
1604 where inventory_item_id = cp_inv_item_id
1605 and organization_id = cp_orgn_id ;
1606
1607 r_item_class c_item_class%rowtype;
1608
1609
1610 r_mtl_trx_info c_get_trx_info%ROWTYPE;
1611 r_rcv_params c_rcv_params%ROWTYPE;
1612 r_get_interorg_params c_get_interorg_params%ROWTYPE;
1613 l_from_org_cd mtl_parameters.organization_code%TYPE;
1614 l_to_org_cd mtl_parameters.organization_code%TYPE;
1615 l_regime_id NUMBER;
1616
1617 lv_process_flag VARCHAR2(20);
1618 lv_process_message VARCHAR2(2000);
1619
1620 CURSOR c_get_bonded(p_organization_id IN NUMBER , p_subinventory IN VARCHAR2)/*6487803*/
1621 IS
1622 SELECT bonded
1623 FROM JAI_INV_SUBINV_DTLS
1624 WHERE organization_id = p_organization_id
1625 AND sub_inventory_name = p_subinventory;
1626
1627 lv_bonded JAI_INV_SUBINV_DTLS.bonded%TYPE; /*6487803*/
1628
1629
1630
1631 BEGIN
1632 SAVEPOINT gl_acctg;
1633
1634 stmt_name := 'Opening main_cur';
1635 OPEN c_get_trx_info;
1636 FETCH c_get_trx_info INTO r_mtl_trx_info;
1637 CLOSE c_get_trx_info;
1638
1639 stmt_name := 'Selecting From_organization_code';
1640 OPEN c_org_code(r_mtl_trx_info.from_organization);
1641 FETCH c_org_code INTO l_from_org_cd;
1642 CLOSE c_org_code;
1643
1644 stmt_name := 'Selecting to_organization_code';
1645 OPEN c_org_code(r_mtl_trx_info.to_organization);
1646 FETCH c_org_code INTO l_to_org_cd;
1647 CLOSE c_org_code;
1648
1649 stmt_name := 'Selecting regime id';
1650 SELECT regime_id
1651 INTO l_regime_id
1652 FROM jai_rgm_definitions
1653 WHERE regime_Code = 'VAT';
1654
1655 stmt_name := 'Opening c_get_interorg_params';
1656 OPEN c_get_interorg_params(r_mtl_trx_info.from_organization, r_mtl_trx_info.to_organization) ;
1657 FETCH c_get_interorg_params INTO r_get_interorg_params;
1658 CLOSE c_get_interorg_params;
1659
1660 /*
1661 get the from org accts from org additional info setup
1662 */
1663
1664
1665 /*
1666 1. get the FROM location
1667 2. get the accounts FROM ja_in_hr_organization units FOR FROM org AND TO org
1668 3. get the accounts FROM mtl_interorg_parameters based ON FROM org AND TO org
1669 4. get the inventory recvng accnt FROM rcv_parameters
1670 */
1671
1672 stmt_name := 'Opening c_get_location';
1673 OPEN c_get_location(r_mtl_trx_info.from_organization , r_mtl_trx_info.from_subinventory);
1674 FETCH c_get_location INTO ln_from_loc_id;
1675 CLOSE c_get_location;
1676
1677 stmt_name := 'Opening c_get_bonded'; /*6487803*/
1678 OPEN c_get_bonded(r_mtl_trx_info.to_organization , r_mtl_trx_info.to_subinventory);
1679 FETCH c_get_bonded INTO lv_bonded;
1680 CLOSE c_get_bonded;
1681
1682 stmt_name := 'Opening c_get_ja_accts';
1683 OPEN c_get_ja_accts ( r_mtl_trx_info.from_organization , ln_from_loc_id);
1684 FETCH c_get_ja_accts INTO r_from_ja_in_hr_org;
1685 CLOSE c_get_ja_accts;
1686
1687 stmt_name := 'Opening c_get_ja_accts';
1688 OPEN c_get_ja_accts ( r_mtl_trx_info.to_organization , r_mtl_trx_info.location_id);
1689 FETCH c_get_ja_accts INTO r_to_ja_in_hr_org;
1690 CLOSE c_get_ja_accts;
1691
1692 stmt_name := 'Opening c_rcv_params';
1693 OPEN c_rcv_params(r_mtl_trx_info.to_organization);
1694 FETCH c_rcv_params INTO r_rcv_params;
1695 CLOSE c_rcv_params;
1696
1697
1698
1699 FOR main_rec IN main_cur
1700 LOOP
1701
1702
1703
1704 IF main_rec.tax_type_val = 1 and p_register_type IN ('A','C','PLA','RG23D' ) THEN
1705
1706 IF r_from_ja_in_hr_org.TRADING <> 'Y' THEN -- bug 6740006
1707
1708 t_gl_entries(rec).amount := main_rec.tax_amount;
1709
1710 /*accounting entries for direct org transfer for sending org is :
1711 Debit: Excise Paid / Payable (Excise)
1712 Debit: Excise Cess Payable (ED Cess)
1713 Credit: RG23 Register / PLA A/C
1714
1715 Debit: Inter Org Receivable A/C
1716 Credit: Excise Paid / Payable (Excise)
1717 Credit: Excise Cess Payable (ED Cess)
1718
1719 excise has to hit the excise paid payable account instead of receivable account 10242419***/
1720
1721 -- t_gl_entries(rec).debit_account := r_from_ja_in_hr_org.excise_rcvble_account; modified the condition for bug#10242419
1722 -- t_gl_entries(rec).debit_account := r_from_ja_in_hr_org.excise_paid_account; -- 10242419 12807591
1723 t_gl_entries(rec).debit_account := r_from_ja_in_hr_org.excise_rcvble_account; -- 12807591
1724
1725
1726 IF t_gl_entries(rec).debit_account IS NULL THEN
1727 p_process_status := 'E';
1728 p_process_message := 'Excise paid/payables a/c is null';
1729 RETURN;
1730 END IF;
1731 /*commented for bug#12807591
1732 IF r_from_ja_in_hr_org.excise_paid_account IS NULL THEN
1733 p_process_status := 'E';
1734 p_process_message := '';
1735 END IF;
1736 */
1737
1738 IF p_register_type = 'A' THEN
1739 t_gl_entries(rec).credit_account := r_from_ja_in_hr_org.modvat_rm_account_id;
1740 IF t_gl_entries(rec).credit_account IS NULL THEN
1741 p_process_status := 'E';
1742 p_process_message := 'Modvat RM a/c is null';
1743 RETURN;
1744 END IF;
1745 ELSIF p_register_type = 'PLA' THEN
1746 t_gl_entries(rec).credit_account := r_from_ja_in_hr_org.modvat_pla_account_id;
1747 IF t_gl_entries(rec).credit_account IS NULL THEN
1748 p_process_status := 'E';
1749 p_process_message := 'Modvat PLA a/c is null';
1750 RETURN;
1751 END IF;
1752 ELSIF p_register_type = 'C' THEN
1753 t_gl_entries(rec).credit_account := r_from_ja_in_hr_org.modvat_cg_account_id;
1754 IF t_gl_entries(rec).credit_account IS NULL THEN
1755 p_process_status := 'E';
1756 p_process_message := 'Modvat CG a/c is null';
1757 RETURN;
1758 END IF;
1759 ELSIF p_register_type = 'RG23D' THEN
1760 t_gl_entries(rec).credit_account := r_from_ja_in_hr_org.excise_23d_account;
1761 IF t_gl_entries(rec).credit_account IS NULL THEN
1762 p_process_status := 'E';
1763 p_process_message := 'Excise 23D a/c is null';
1764 RETURN;
1765 END IF;
1766
1767 END IF;
1768 t_gl_entries(rec).organization_id := r_mtl_trx_info.from_organization;
1769 t_gl_entries(rec).organization_code := l_from_org_cd;
1770 rec := rec + 1;
1771 END IF ; -- bug 6740006
1772
1773
1774 IF r_get_interorg_params.intransit_type = 2 /* Intransit */ THEN
1775
1776 IF r_get_interorg_params.fob_point = 1 THEN -- SHIPMENTS
1777
1778 t_gl_entries(rec).amount := main_rec.tax_amount;
1779 t_gl_entries(rec).debit_account := r_get_interorg_params.interorg_receivables_account;
1780 IF r_from_ja_in_hr_org.TRADING = 'Y' THEN -- BUG 6488406
1781 t_gl_entries(rec).credit_account := r_from_ja_in_hr_org.excise_23d_account;
1782 ELSE
1783 t_gl_entries(rec).credit_account := r_from_ja_in_hr_org.excise_rcvble_account;
1784 END if;
1785 t_gl_entries(rec).organization_id := r_mtl_trx_info.from_organization;
1786 t_gl_entries(rec).organization_code := l_from_org_cd;
1787
1788 IF t_gl_entries(rec).debit_account IS NULL THEN
1789 p_process_status := 'E';
1790 p_process_message := 'Interorg Receivables a/c is null';
1791 RETURN;
1792 ELSIF t_gl_entries(rec).credit_account IS NULL THEN
1793 p_process_status := 'E';
1794 p_process_message := 'Excise paid/payables a/c is null';
1795 RETURN;
1796 END IF;
1797 rec := rec + 1;
1798
1799 t_gl_entries(rec).amount := main_rec.tax_amount;
1800 t_gl_entries(rec).debit_account := r_get_interorg_params.intransit_inv_account ;
1801 t_gl_entries(rec).credit_account := r_get_interorg_params.interorg_payables_account;
1802 t_gl_entries(rec).organization_id := r_mtl_trx_info.to_organization;
1803 t_gl_entries(rec).organization_code := l_to_org_cd;
1804 IF t_gl_entries(rec).debit_account IS NULL THEN
1805 p_process_status := 'E';
1806 p_process_message := 'Intransit inventory a/c is null';
1807 RETURN;
1808 ELSIF t_gl_entries(rec).credit_account IS NULL THEN
1809 p_process_status := 'E';
1810 p_process_message := 'InterOrg Payables a/c is null';
1811 RETURN;
1812 END IF;
1813 rec := rec + 1;
1814
1815 ELSIF r_get_interorg_params.fob_point = 2 THEN -- Receipt
1816
1817 t_gl_entries(rec).amount := main_rec.tax_amount;
1818 t_gl_entries(rec).debit_account := r_get_interorg_params.intransit_inv_account ;
1819 IF r_from_ja_in_hr_org.TRADING = 'Y' THEN -- BUG 6488406
1820 t_gl_entries(rec).credit_account := r_from_ja_in_hr_org.excise_23d_account;
1821 ELSE
1822 t_gl_entries(rec).credit_account := r_from_ja_in_hr_org.excise_rcvble_account;
1823 END if;
1824 t_gl_entries(rec).organization_id := r_mtl_trx_info.from_organization;
1825 t_gl_entries(rec).organization_code := l_from_org_cd;
1826 IF t_gl_entries(rec).debit_account IS NULL THEN
1827 p_process_status := 'E';
1828 p_process_message := 'Intransit inventory a/c is null';
1829 RETURN;
1830 ELSIF t_gl_entries(rec).credit_account IS NULL THEN
1831 p_process_status := 'E';
1832 p_process_message := 'Excise paid/payables a/c is null';
1833 RETURN;
1834 END IF;
1835 rec := rec + 1;
1836
1837 END IF;
1838
1839 ELSIF r_get_interorg_params.intransit_type = 1 THEN -- Direct
1840
1841 t_gl_entries(rec).amount := main_rec.tax_amount;
1842 t_gl_entries(rec).debit_account := r_get_interorg_params.interorg_receivables_account;
1843
1844 IF r_from_ja_in_hr_org.TRADING = 'Y' THEN -- BUG 6740006
1845 t_gl_entries(rec).credit_account := r_from_ja_in_hr_org.excise_23d_account;
1846 ELSE
1847 -- t_gl_entries(rec).credit_account := r_from_ja_in_hr_org.excise_rcvble_account; 10242419
1848 --t_gl_entries(rec).credit_account := r_from_ja_in_hr_org.excise_paid_account; --10242419 12807591
1849 t_gl_entries(rec).credit_account := r_from_ja_in_hr_org.excise_rcvble_account; --12807591
1850 END IF;
1851
1852 t_gl_entries(rec).organization_id := r_mtl_trx_info.from_organization;
1853 t_gl_entries(rec).organization_code := l_from_org_cd;
1854
1855
1856 IF t_gl_entries(rec).debit_account IS NULL THEN
1857 p_process_status := 'E';
1858 p_process_message := 'InterOrg receivables a/c is null';
1859 RETURN;
1860 ELSIF t_gl_entries(rec).credit_account IS NULL THEN
1861 p_process_status := 'E';
1862 p_process_message := 'Excise paid payables a/c is null';
1863 RETURN;
1864 END IF;
1865 rec := rec + 1;
1866
1867 t_gl_entries(rec).amount := main_rec.tax_amount;
1868 t_gl_entries(rec).debit_account := r_rcv_params.receiving_account_id;
1869 t_gl_entries(rec).credit_account := r_get_interorg_params.interorg_payables_account;
1870 t_gl_entries(rec).organization_id := r_mtl_trx_info.to_organization;
1871 t_gl_entries(rec).organization_code := l_to_org_cd;
1872
1873
1874
1875 IF t_gl_entries(rec).debit_account IS NULL THEN
1876 p_process_status := 'E';
1877 p_process_message := 'Inventory Receiving a/c is null';
1878 RETURN;
1879 ELSIF t_gl_entries(rec).credit_account IS NULL THEN
1880 p_process_status := 'E';
1881 p_process_message := 'InterOrg Payables a/c is null';
1882 RETURN;
1883 END IF;
1884 rec := rec + 1;
1885
1886 open c_item_class(main_rec.inventory_item_id, r_mtl_trx_info.to_organization);
1887 FETCH c_item_class INTO r_item_class;
1888 close c_item_class;
1889 /*modified the below condition for bug#10242419
1890 IF (r_to_ja_in_hr_org.TRADING = 'Y' or lv_bonded = 'Y') AND /*6487803*
1891 r_item_class.item_class NOT IN ('FGIN','FGEX') THEN /*6501436*/
1892
1893 --bug#10242419
1894 IF (r_to_ja_in_hr_org.TRADING = 'Y' and nvl(r_to_ja_in_hr_org.excise_in_rg23d,'N') ='Y')
1895 OR
1896 (lv_bonded = 'Y' AND r_item_class.item_class NOT IN ('FGIN','FGEX') )
1897 THEN
1898 t_gl_entries(rec).amount := main_rec.tax_amount;
1899 -- CHANGES FOR START BUG 6740006
1900
1901
1902 IF r_to_ja_in_hr_org.TRADING = 'Y' THEN
1903 t_gl_entries(rec).debit_account := r_to_ja_in_hr_org.excise_23d_account;
1904 IF r_to_ja_in_hr_org.excise_23d_account IS NULL THEN
1905 p_process_status :='E';
1906 p_process_message :='RG23D Account not defined in Receiving Org';
1907 RETURN;
1908 END IF;
1909 ELSIF r_item_class.item_class IN ('RMIN', 'RMEX', 'CCIN', 'CCEX') then /*/*6501436.removed FGIN and FGEX*/
1910 t_gl_entries(rec).debit_account := r_to_ja_in_hr_org.modvat_rm_account_id;
1911 IF r_to_ja_in_hr_org.modvat_rm_account_id IS NULL THEN
1912 p_process_status := 'E';
1913 p_process_message := 'Modvat RM Account not defined in Receiving Org';
1914 RETURN;
1915 END IF;
1916 ELSIF r_item_class.item_class IN ('CGIN', 'CGEX') then
1917 t_gl_entries(rec).debit_account := r_to_ja_in_hr_org.modvat_cg_account_id;
1918 IF r_to_ja_in_hr_org.modvat_cg_account_id IS NULL THEN
1919 p_process_status :='E';
1920 p_process_message :='Modvat CG Account not defined in Receiving Org';
1921 RETURN;
1922 END IF;
1923 end if;
1924
1925
1926 -- CHANGES FOR END BUG 6740006
1927
1928
1929 t_gl_entries(rec).credit_account := r_rcv_params.receiving_account_id;
1930 IF t_gl_entries(rec).credit_account IS NULL THEN
1931 p_process_Status := 'E';
1932 p_process_message := 'Receiving Inventory ACcount is not defined for Receiving Org';
1933 RETURN;
1934 END IF;
1935 t_gl_entries(rec).organization_id := r_mtl_trx_info.to_organization;
1936 t_gl_entries(rec).organization_code := l_to_org_cd;
1937 rec := rec + 1;
1938 END IF;/*6487803*/
1939
1940 END IF;
1941 ELSIF main_rec.tax_type_val = 5 and p_register_type IN ('A','C','PLA','RG23D' ) THEN -- excise education cess
1942
1943 IF r_from_ja_in_hr_org.TRADING <> 'Y' THEN -- bug 6740006
1944
1945 t_gl_entries(rec).amount := main_rec.tax_amount;
1946 t_gl_entries(rec).debit_account := r_from_ja_in_hr_org.cess_paid_payable_account_id;
1947
1948
1949
1950
1951 IF p_register_type = 'A' THEN
1952 t_gl_entries(rec).credit_account := r_from_ja_in_hr_org.excise_edu_cess_rm_account;
1953 IF t_gl_entries(rec).credit_account IS NULL THEN
1954 p_process_status := 'E';
1955 p_process_message := 'Excise cess RM a/c is null';
1956 RETURN;
1957 END IF;
1958 ELSIF p_register_type = 'PLA' THEN
1959 t_gl_entries(rec).credit_account := r_from_ja_in_hr_org.modvat_pla_account_id;
1960 IF t_gl_entries(rec).credit_account IS NULL THEN
1961 p_process_status := 'E';
1962 p_process_message := 'Modvat PLA a/c is null';
1963 RETURN;
1964 END IF;
1965 ELSIF p_register_type = 'C' THEN
1966 t_gl_entries(rec).credit_account := r_from_ja_in_hr_org.excise_edu_cess_cg_account;
1967 IF t_gl_entries(rec).credit_account IS NULL THEN
1968 p_process_status := 'E';
1969 p_process_message := 'Excise cess cg a/c is null';
1970 RETURN;
1971 END IF;
1972 ELSIF p_register_type = 'RG23D' THEN
1973 t_gl_entries(rec).credit_account := r_from_ja_in_hr_org.excise_23d_account;
1974 IF t_gl_entries(rec).credit_account IS NULL THEN
1975 p_process_status := 'E';
1976 p_process_message := 'Excise RG23D a/c is null';
1977 RETURN;
1978 END IF;
1979
1980 END IF;
1981 t_gl_entries(rec).organization_id := r_mtl_trx_info.from_organization;
1982 t_gl_entries(rec).organization_code := l_from_org_cd;
1983 IF t_gl_entries(rec).debit_account IS NULL THEN
1984 p_process_status := 'E';
1985 p_process_message := 'Cess-Paid Payables a/c is null';
1986 RETURN;
1987 END IF;
1988
1989
1990
1991
1992 rec := rec + 1;
1993
1994 END IF; -- bug 6740006
1995
1996 IF r_get_interorg_params.intransit_type = 2 THEN -- IN TRANSIT
1997
1998 IF r_get_interorg_params.fob_point = 1 THEN -- Shipments
1999 t_gl_entries(rec).amount := main_rec.tax_amount;
2000 t_gl_entries(rec).debit_account := r_get_interorg_params.interorg_receivables_account;
2001 IF r_from_ja_in_hr_org.TRADING = 'Y' THEN -- BUG 6488406
2002 t_gl_entries(rec).credit_account := r_from_ja_in_hr_org.excise_23d_account;
2003 ELSE
2004 t_gl_entries(rec).credit_account := r_from_ja_in_hr_org.cess_paid_payable_account_id;
2005 END if;
2006 t_gl_entries(rec).organization_id := r_mtl_trx_info.from_organization;
2007 t_gl_entries(rec).organization_code := l_from_org_cd;
2008 IF t_gl_entries(rec).debit_account IS NULL THEN
2009 p_process_status := 'E';
2010 p_process_message := 'InterOrg recevables a/c is null';
2011 RETURN;
2012 ELSIF t_gl_entries(rec).credit_account IS NULL THEN
2013 p_process_status := 'E';
2014 p_process_message := 'Cess-paid Payables a/c is null';
2015 RETURN;
2016 END IF;
2017 rec := rec + 1;
2018
2019 t_gl_entries(rec).amount := main_rec.tax_amount;
2020 t_gl_entries(rec).debit_account := r_get_interorg_params.intransit_inv_account;
2021 t_gl_entries(rec).credit_account := r_get_interorg_params.interorg_payables_account;
2022 t_gl_entries(rec).organization_id := r_mtl_trx_info.to_organization;
2023 t_gl_entries(rec).organization_code := l_to_org_cd;
2024 IF t_gl_entries(rec).debit_account IS NULL THEN
2025 p_process_status := 'E';
2026 p_process_message := 'Intransit inventory a/c is null';
2027 RETURN;
2028 ELSIF t_gl_entries(rec).credit_account IS NULL THEN
2029 p_process_status := 'E';
2030 p_process_message := 'InterOrg Payables a/c is null';
2031 RETURN;
2032 END IF;
2033 rec := rec + 1;
2034
2035 ELSIF r_get_interorg_params.fob_point = 2 THEN -- Receipts
2036
2037 t_gl_entries(rec).amount := main_rec.tax_amount;
2038 t_gl_entries(rec).debit_account := r_get_interorg_params.intransit_inv_account;
2039 IF r_from_ja_in_hr_org.TRADING = 'Y' THEN -- BUG 6488406
2040 t_gl_entries(rec).credit_account := r_from_ja_in_hr_org.excise_23d_account;
2041 ELSE
2042 t_gl_entries(rec).credit_account := r_from_ja_in_hr_org.cess_paid_payable_account_id;
2043 END if;
2044 t_gl_entries(rec).organization_id := r_mtl_trx_info.from_organization;
2045 t_gl_entries(rec).organization_code := l_from_org_cd;
2046 IF t_gl_entries(rec).debit_account IS NULL THEN
2047 p_process_status := 'E';
2048 p_process_message := 'Intransit Inventory a/c is null';
2049 RETURN;
2050 ELSIF t_gl_entries(rec).credit_account IS NULL THEN
2051 p_process_status := 'E';
2052 p_process_message := 'Cess-paid Payables a/c is null';
2053 RETURN;
2054 END IF;
2055 rec := rec + 1;
2056
2057 END IF;
2058
2059 ELSIF r_get_interorg_params.intransit_type = 1 THEN -- Direct
2060
2061 t_gl_entries(rec).amount := main_rec.tax_amount;
2062 t_gl_entries(rec).debit_account := r_get_interorg_params.interorg_receivables_account;
2063
2064 IF r_from_ja_in_hr_org.TRADING = 'Y' THEN -- BUG 6740006
2065 t_gl_entries(rec).credit_account := r_from_ja_in_hr_org.excise_23d_account;
2066 ELSE
2067 t_gl_entries(rec).credit_account := r_from_ja_in_hr_org.cess_paid_payable_account_id;
2068 END IF;
2069
2070 t_gl_entries(rec).organization_id := r_mtl_trx_info.from_organization;
2071 t_gl_entries(rec).organization_code := l_from_org_cd;
2072
2073
2074
2075
2076 IF t_gl_entries(rec).debit_account IS NULL THEN
2077 p_process_status := 'E';
2078 p_process_message := 'Inventory receiving a/c is null';
2079 RETURN;
2080 ELSIF t_gl_entries(rec).credit_account IS NULL THEN
2081 p_process_status := 'E';
2082 p_process_message := 'Cess-paid Payables a/c is null';
2083 RETURN;
2084 END IF;
2085 rec := rec + 1;
2086
2087 t_gl_entries(rec).amount := main_rec.tax_amount;
2088 t_gl_entries(rec).debit_account := r_rcv_params.receiving_account_id;
2089 t_gl_entries(rec).credit_account := r_get_interorg_params.interorg_payables_account;
2090 t_gl_entries(rec).organization_id := r_mtl_trx_info.to_organization;
2091 t_gl_entries(rec).organization_code := l_to_org_cd;
2092
2093
2094
2095 IF t_gl_entries(rec).debit_account IS NULL THEN
2096 p_process_status := 'E';
2097 p_process_message := 'Inventory receiving a/c is null';
2098 RETURN;
2099 ELSIF t_gl_entries(rec).credit_account IS NULL THEN
2100 p_process_status := 'E';
2101 p_process_message := 'InterOrg Payables a/c is null';
2102 RETURN;
2103 END IF;
2104 rec := rec + 1;
2105
2106 -- start entries for cenvat in direct org
2107
2108 open c_item_class(main_rec.inventory_item_id, r_mtl_trx_info.to_organization);
2109 FETCH c_item_class INTO r_item_class;
2110 close c_item_class;
2111
2112 /*modified the below condition for bug#10242419
2113 IF (r_to_ja_in_hr_org.TRADING = 'Y' or lv_bonded = 'Y') AND /*6487803*
2114 r_item_class.item_class NOT IN ('FGIN','FGEX') THEN /*6501436*/
2115
2116 --bug#10242419
2117 IF (r_to_ja_in_hr_org.TRADING = 'Y' and nvl(r_to_ja_in_hr_org.excise_in_rg23d,'N') ='Y')
2118 OR
2119 (lv_bonded = 'Y' AND r_item_class.item_class NOT IN ('FGIN','FGEX') )
2120 THEN
2121
2122 t_gl_entries(rec).amount := main_rec.tax_amount;
2123
2124 -- CHANGES FOR START BUG 6740006
2125
2126 IF r_to_ja_in_hr_org.TRADING = 'Y' THEN
2127 t_gl_entries(rec).debit_account := r_to_ja_in_hr_org.excise_23d_account;
2128 IF r_to_ja_in_hr_org.excise_23d_account IS NULL THEN
2129 p_process_status :='E';
2130 p_process_message :='RG23D Account not defined in Receiving Org';
2131 RETURN;
2132 END IF;
2133 ELSIF r_item_class.item_class IN ('RMIN', 'RMEX', 'CCIN', 'CCEX') then /*6501436..removed FGIN FGEX*/
2134 t_gl_entries(rec).debit_account := r_to_ja_in_hr_org.excise_edu_cess_rm_account;
2135 IF r_to_ja_in_hr_org.excise_edu_cess_rm_account IS NULL THEN
2136 p_process_status := 'E';
2137 p_process_message := 'Cess RM Account not defined in Receiving Org';
2138 RETURN;
2139 END IF;
2140 ELSIF r_item_class.item_class IN ('CGIN', 'CGEX') then
2141 t_gl_entries(rec).debit_account := r_to_ja_in_hr_org.excise_edu_cess_cg_account;
2142 IF r_to_ja_in_hr_org.excise_edu_cess_cg_account IS NULL THEN
2143 p_process_status :='E';
2144 p_process_message :='Cess CG Account not defined in Receiving Org';
2145 RETURN;
2146 END IF;
2147 END if;
2148
2149 -- CHANGES FOR END BUG 6740006
2150
2151
2152 t_gl_entries(rec).credit_account := r_rcv_params.receiving_account_id;
2153 IF t_gl_entries(rec).credit_account IS NULL THEN
2154 p_process_status := 'E';
2155 p_process_message := 'Receiving Inventory Account not defined for receiving org';
2156 RETURN;
2157 END IF;
2158 t_gl_entries(rec).organization_id := r_mtl_trx_info.to_organization;
2159 t_gl_entries(rec).organization_code := l_to_org_cd;
2160
2161 rec := rec + 1;
2162
2163 END IF; /*6487803*/
2164 -- end
2165
2166 END IF;
2167 /*following elsif condition added for shcess by vkaranam for bug #5907436*/
2168 --start 5907436
2169
2170 ELSIF main_rec.tax_type_val = 6 and p_register_type IN ('A','C','PLA','RG23D' ) THEN
2171
2172
2173 IF r_from_ja_in_hr_org.TRADING <> 'Y' THEN -- bug 6740006
2174
2175 t_gl_entries(rec).amount := main_rec.tax_amount;
2176 t_gl_entries(rec).debit_account := r_from_ja_in_hr_org.sh_cess_paid_payable_acct_id;
2177 IF p_register_type = 'A' THEN
2178 t_gl_entries(rec).credit_account := r_from_ja_in_hr_org.sh_cess_rm_account;
2179 IF t_gl_entries(rec).credit_account IS NULL THEN
2180 p_process_status := 'E';
2181 p_process_message := 'SH Excise cess RM a/c is null';
2182 RETURN;
2183 END IF;
2184 ELSIF p_register_type = 'PLA' THEN
2185 t_gl_entries(rec).credit_account := r_from_ja_in_hr_org.modvat_pla_account_id;
2186 IF t_gl_entries(rec).credit_account IS NULL THEN
2187 p_process_status := 'E';
2188 p_process_message := 'Modvat PLA a/c is null';
2189 RETURN;
2190 END IF;
2191 ELSIF p_register_type = 'C' THEN
2192 t_gl_entries(rec).credit_account := r_from_ja_in_hr_org.sh_cess_cg_account_id;
2193 IF t_gl_entries(rec).credit_account IS NULL THEN
2194 p_process_status := 'E';
2195 p_process_message := 'SH Excise cess cg a/c is null';
2196 RETURN;
2197 END IF;
2198 ELSIF p_register_type = 'RG23D' THEN
2199 t_gl_entries(rec).credit_account := r_from_ja_in_hr_org.excise_23d_account;
2200 IF t_gl_entries(rec).credit_account IS NULL THEN
2201 p_process_status := 'E';
2202 p_process_message := 'Excise RG23D a/c is null';
2203 RETURN;
2204 END IF;
2205
2206 END IF;
2207 t_gl_entries(rec).organization_id := r_mtl_trx_info.from_organization;
2208 t_gl_entries(rec).organization_code := l_from_org_cd;
2209
2210
2211 IF t_gl_entries(rec).debit_account IS NULL THEN
2212 p_process_status := 'E';
2213 p_process_message := 'SH Cess-Paid Payables a/c is null';
2214 RETURN;
2215 END IF;
2216 rec := rec + 1;
2217
2218 END IF ; -- bug 6740006
2219
2220 IF r_get_interorg_params.intransit_type = 2 THEN -- IN TRANSIT
2221
2222 IF r_get_interorg_params.fob_point = 1 THEN -- Shipments
2223 t_gl_entries(rec).amount := main_rec.tax_amount;
2224 t_gl_entries(rec).debit_account := r_get_interorg_params.interorg_receivables_account;--1102
2225 IF r_from_ja_in_hr_org.TRADING = 'Y' THEN -- BUG 6488406
2226 t_gl_entries(rec).credit_account := r_from_ja_in_hr_org.excise_23d_account;
2227 ELSE
2228 t_gl_entries(rec).credit_account := r_from_ja_in_hr_org.sh_cess_paid_payable_acct_id;
2229 END if;
2230 t_gl_entries(rec).organization_id := r_mtl_trx_info.from_organization;
2231 t_gl_entries(rec).organization_code := l_from_org_cd;
2232 IF t_gl_entries(rec).debit_account IS NULL THEN
2233 p_process_status := 'E';
2234 p_process_message := 'InterOrg recevables a/c is null';
2235 RETURN;
2236 ELSIF t_gl_entries(rec).credit_account IS NULL THEN
2237 p_process_status := 'E';
2238 p_process_message := 'SH Cess-paid Payables a/c is null';
2239 RETURN;
2240 END IF;
2241 rec := rec + 1;
2242
2243 t_gl_entries(rec).amount := main_rec.tax_amount;
2244 t_gl_entries(rec).debit_account := r_get_interorg_params.intransit_inv_account;
2245 t_gl_entries(rec).credit_account := r_get_interorg_params.interorg_payables_account;
2246 t_gl_entries(rec).organization_id := r_mtl_trx_info.to_organization;
2247 t_gl_entries(rec).organization_code := l_to_org_cd;
2248 IF t_gl_entries(rec).debit_account IS NULL THEN
2249 p_process_status := 'E';
2250 p_process_message := 'Intransit inventory a/c is null';
2251 RETURN;
2252 ELSIF t_gl_entries(rec).credit_account IS NULL THEN
2253 p_process_status := 'E';
2254 p_process_message := 'InterOrg Payables a/c is null';
2255 RETURN;
2256 END IF;
2257 rec := rec + 1;
2258
2259 ELSIF r_get_interorg_params.fob_point = 2 THEN -- Receipts
2260
2261 t_gl_entries(rec).amount := main_rec.tax_amount;
2262 t_gl_entries(rec).debit_account := r_get_interorg_params.intransit_inv_account;
2263 IF r_from_ja_in_hr_org.TRADING = 'Y' THEN -- BUG 6488406
2264 t_gl_entries(rec).credit_account := r_from_ja_in_hr_org.excise_23d_account;
2265 ELSE
2266 t_gl_entries(rec).credit_account := r_from_ja_in_hr_org.sh_cess_paid_payable_acct_id;
2267 END if;
2268 t_gl_entries(rec).organization_id := r_mtl_trx_info.from_organization;
2269 t_gl_entries(rec).organization_code := l_from_org_cd;
2270 IF t_gl_entries(rec).debit_account IS NULL THEN
2271 p_process_status := 'E';
2272 p_process_message := 'Intransit Inventory a/c is null';
2273 RETURN;
2274 ELSIF t_gl_entries(rec).credit_account IS NULL THEN
2275 p_process_status := 'E';
2276 p_process_message := 'SH Cess-paid Payables a/c is null';
2277 RETURN;
2278 END IF;
2279 rec := rec + 1;
2280
2281 END IF;
2282
2283 ELSIF r_get_interorg_params.intransit_type = 1 THEN -- Direct
2284
2285
2286 t_gl_entries(rec).amount := main_rec.tax_amount;
2287 t_gl_entries(rec).debit_account := r_get_interorg_params.interorg_receivables_account;
2288
2289 IF r_from_ja_in_hr_org.TRADING = 'Y' THEN -- BUG 6740006
2290 t_gl_entries(rec).credit_account := r_from_ja_in_hr_org.excise_23d_account;
2291 ELSE
2292 t_gl_entries(rec).credit_account := r_from_ja_in_hr_org.sh_cess_paid_payable_acct_id;
2293 END IF;
2294
2295 t_gl_entries(rec).organization_id := r_mtl_trx_info.from_organization;
2296 t_gl_entries(rec).organization_code := l_from_org_cd;
2297
2298
2299
2300 IF t_gl_entries(rec).debit_account IS NULL THEN
2301 p_process_status := 'E';
2302 p_process_message := 'Inventory receiving a/c is null';
2303 RETURN;
2304 ELSIF t_gl_entries(rec).credit_account IS NULL THEN
2305 p_process_status := 'E';
2306 p_process_message := 'SH Cess-paid Payables a/c is null';
2307 RETURN;
2308 END IF;
2309 rec := rec + 1;
2310
2311 t_gl_entries(rec).amount := main_rec.tax_amount;
2312 t_gl_entries(rec).debit_account := r_rcv_params.receiving_account_id;
2313 t_gl_entries(rec).credit_account := r_get_interorg_params.interorg_payables_account;
2314 t_gl_entries(rec).organization_id := r_mtl_trx_info.to_organization;
2315 t_gl_entries(rec).organization_code := l_to_org_cd;
2316 IF t_gl_entries(rec).debit_account IS NULL THEN
2317 p_process_status := 'E';
2318 p_process_message := 'Inventory receiving a/c is null';
2319 RETURN;
2320 ELSIF t_gl_entries(rec).credit_account IS NULL THEN
2321 p_process_status := 'E';
2322 p_process_message := 'InterOrg Payables a/c is null';
2323 RETURN;
2324 END IF;
2325 rec := rec + 1;
2326
2327 open c_item_class(main_rec.inventory_item_id, r_mtl_trx_info.to_organization);
2328 FETCH c_item_class INTO r_item_class;
2329 close c_item_class;
2330
2331 /*modified the below condition for bug#10242419
2332 IF (r_to_ja_in_hr_org.TRADING = 'Y' or lv_bonded = 'Y') AND /*6487803*
2333 r_item_class.item_class NOT IN ('FGIN','FGEX') THEN /*6501436*/
2334
2335 --bug#10242419
2336 IF (r_to_ja_in_hr_org.TRADING = 'Y' and nvl(r_to_ja_in_hr_org.excise_in_rg23d,'N') ='Y')
2337 OR
2338 (lv_bonded = 'Y' AND r_item_class.item_class NOT IN ('FGIN','FGEX') )
2339 THEN
2340 -- start entries for cenvat in direct org
2341 t_gl_entries(rec).amount := main_rec.tax_amount;
2342
2343
2344 -- CHANGES FOR START BUG 6740006
2345
2346
2347 IF r_to_ja_in_hr_org.TRADING = 'Y' THEN
2348 t_gl_entries(rec).debit_account := r_to_ja_in_hr_org.excise_23d_account;
2349 IF r_to_ja_in_hr_org.excise_23d_account IS NULL THEN
2350 p_process_status :='E';
2351 p_process_message :='RG23D Account not defined in Receiving Org';
2352 RETURN;
2353 END IF;
2354
2355 ELSIF r_item_class.item_class IN ('RMIN', 'RMEX', 'CCIN', 'CCEX') then /*6501436.removed FGIN FGEX*/
2356 t_gl_entries(rec).debit_account := r_to_ja_in_hr_org.sh_cess_rm_account;
2357 IF r_to_ja_in_hr_org.sh_cess_rm_account IS NULL THEN
2358 p_process_status := 'E';
2359 p_process_message := 'SH Cess RM Account not defined in Receiving Org';
2360 RETURN;
2361 END IF;
2362 ELSIF r_item_class.item_class IN ('CGIN', 'CGEX') then
2363 t_gl_entries(rec).debit_account := r_to_ja_in_hr_org.sh_cess_cg_account_id;
2364 IF r_to_ja_in_hr_org.sh_cess_cg_account_id IS NULL THEN
2365 p_process_status :='E';
2366 p_process_message :='SH Cess CG Account not defined in Receiving Org';
2367 RETURN;
2368 END IF;
2369 end if;
2370
2371 -- CHANGES FOR END BUG 6740006
2372
2373
2374 t_gl_entries(rec).credit_account := r_rcv_params.receiving_account_id;
2375 IF t_gl_entries(rec).credit_account IS NULL THEN
2376 p_process_status := 'E';
2377 p_process_message := 'Receiving Inventory Account not defined for receiving org';
2378 RETURN;
2379 END IF;
2380 t_gl_entries(rec).organization_id := r_mtl_trx_info.to_organization;
2381 t_gl_entries(rec).organization_code := l_to_org_cd;
2382
2383
2384 rec := rec + 1;
2385
2386 END IF;/*6487803*/
2387 -- end
2388
2389 END IF;
2390 --END 5907436
2391
2392 ELSIF main_rec.tax_type_val = 4 and p_register_type IN ('SVAT','RVAT') THEN /* value added tax*/
2393
2394 IF p_register_type='SVAT' and main_rec.tax_type <> 'VAT REVERSAL' then
2395 t_gl_entries(rec).amount := main_rec.tax_amount;
2396 stmt_name := 'Calling:4 jai_cmn_rgm_recording_pkg.get_account()';
2397 t_gl_entries(rec).debit_account := jai_cmn_rgm_recording_pkg.get_account(
2398 p_regime_id => l_regime_id,
2399 p_organization_type => jai_constants.orgn_type_io,
2400 p_organization_id => r_mtl_trx_info.from_organization,
2401 p_location_id => ln_from_loc_id,
2402 p_tax_type => main_rec.tax_type,
2403 p_account_name => jai_constants.liability_interim
2404 );
2405 stmt_name := 'Calling:3 jai_cmn_rgm_recording_pkg.get_account()';
2406 t_gl_entries(rec).credit_account := jai_cmn_rgm_recording_pkg.get_account(
2407 p_regime_id => l_regime_id,
2408 p_organization_type => jai_constants.orgn_type_io,
2409 p_organization_id => r_mtl_trx_info.from_organization,
2410 p_location_id => ln_from_loc_id,
2411 p_tax_type => main_rec.tax_type,
2412 p_account_name => jai_constants.liability
2413 ) ;
2414 t_gl_entries(rec).organization_id := r_mtl_trx_info.from_organization;
2415 t_gl_entries(rec).organization_code := l_from_org_cd;
2416
2417 IF t_gl_entries(rec).debit_account IS NULL THEN
2418 p_process_status := 'E';
2419 p_process_message := 'Interim Liability a/c is null for '||main_rec.tax_type;
2420 RETURN;
2421 ELSIF t_gl_entries(rec).credit_account IS NULL THEN
2422 p_process_status := 'E';
2423 p_process_message := 'Liability a/c is null for '||main_rec.tax_type;
2424 RETURN;
2425 END IF;
2426 rec := rec + 1;
2427
2428 -- ELSIF p_register_type='SVAT' and main_rec.tax_type = 'VAT REVERSAL' then
2429 /*
2430 debit expense accnt
2431 credit VAT recovery Account
2432 */
2433 -- t_gl_entries(rec).amount := main_rec.tax_amount;
2434 -- t_gl_entries(rec).debit_account := jai_cmn_rgm_recording_pkg.get_account(
2435 -- p_regime_id => l_regime_id,
2436 -- p_organization_type => jai_constants.orgn_type_io,
2437 -- p_organization_id => r_mtl_trx_info.from_organization,
2438 -- p_location_id => ln_from_loc_id,
2439 -- p_tax_type => main_rec.tax_type,
2440 -- p_account_name => 'EXPENSE'
2441 -- );
2442
2443 -- t_gl_entries(rec).credit_account := jai_cmn_rgm_recording_pkg.get_account(
2444 -- p_regime_id => l_regime_id,
2445 -- p_organization_type => jai_constants.orgn_type_io,
2446 -- p_organization_id => r_mtl_trx_info.from_organization,
2447 -- p_location_id => ln_from_loc_id,
2448 -- p_tax_type => main_rec.tax_type,
2449 -- p_account_name => 'RECOVERY'
2450 -- );
2451 -- IF t_gl_entries(rec).credit_account IS NULL THEN
2452 -- p_process_status := 'E';
2453 -- p_process_message := 'Recovery Account not setup in receiving Org for ' || main_rec.tax_type ;
2454 -- RETURN;
2455 -- END IF;
2456 -- IF t_gl_entries(rec).debit_account IS NULL THEN
2457 -- p_process_status := 'E';
2458 -- p_process_message := 'Expense Account not setup in receiving Org for ' || main_rec.tax_type ;
2459 -- RETURN;
2460 -- END IF;
2461 -- t_gl_entries(rec).organization_id := r_mtl_trx_info.from_organization;
2462 -- t_gl_entries(rec).organization_code := l_from_org_cd;
2463
2464 -- rec := rec + 1;
2465 END IF;
2466
2467 IF r_get_interorg_params.intransit_type = 2 and main_Rec.tax_type <> 'VAT REVERSAL' THEN /* Intransit */
2468
2469 IF r_get_interorg_params.fob_point = 1 THEN -- Shippment
2470 t_gl_entries(rec).amount := main_rec.tax_amount;
2471 t_gl_entries(rec).debit_account := r_get_interorg_params.interorg_receivables_account;
2472 stmt_name := 'Calling:2 jai_cmn_rgm_recording_pkg.get_account()';
2473 t_gl_entries(rec).credit_account := jai_cmn_rgm_recording_pkg.get_account(
2474 p_regime_id => l_regime_id,
2475 p_organization_type => jai_constants.orgn_type_io,
2476 p_organization_id => r_mtl_trx_info.from_organization,
2477 p_location_id => ln_from_loc_id,
2478 p_tax_type => main_rec.tax_type,
2479 p_account_name => jai_constants.liability_interim
2480 );
2481 t_gl_entries(rec).organization_id := r_mtl_trx_info.from_organization;
2482 t_gl_entries(rec).organization_code := l_from_org_cd;
2483 IF t_gl_entries(rec).debit_account IS NULL THEN
2484 p_process_status := 'E';
2485 p_process_message := 'InterOrg receivable a/c is null';
2486 RETURN;
2487 ELSIF t_gl_entries(rec).credit_account IS NULL THEN
2488 p_process_status := 'E';
2489 p_process_message := 'Interim Liablility a/c is null for '||main_rec.tax_type;
2490 RETURN;
2491 END IF;
2492 rec := rec + 1;
2493
2494 t_gl_entries(rec).amount := main_rec.tax_amount;
2495 t_gl_entries(rec).debit_account := r_get_interorg_params.intransit_inv_account;
2496 t_gl_entries(rec).credit_account := r_get_interorg_params.interorg_payables_account;
2497 t_gl_entries(rec).organization_id := r_mtl_trx_info.to_organization;
2498 t_gl_entries(rec).organization_code := l_to_org_cd;
2499 IF t_gl_entries(rec).debit_account IS NULL THEN
2500 p_process_status := 'E';
2501 p_process_message := 'Intransit Inventory a/c is null';
2502 RETURN;
2503 ELSIF t_gl_entries(rec).credit_account IS NULL THEN
2504 p_process_status := 'E';
2505 p_process_message := 'InterOrg Payables a/c is null';
2506 RETURN;
2507 END IF;
2508 rec := rec + 1;
2509
2510 ELSIF r_get_interorg_params.fob_point = 2 THEN --Receipt
2511 t_gl_entries(rec).amount := main_rec.tax_amount;
2512 t_gl_entries(rec).debit_account := r_get_interorg_params.intransit_inv_account;
2513 stmt_name := 'Calling:1 jai_cmn_rgm_recording_pkg.get_account()';
2514 t_gl_entries(rec).credit_account := jai_cmn_rgm_recording_pkg.get_account(
2515 p_regime_id => l_regime_id,
2516 p_organization_type => jai_constants.orgn_type_io,
2517 p_organization_id => r_mtl_trx_info.from_organization,
2518 p_location_id => ln_from_loc_id,
2519 p_tax_type => main_rec.tax_type,
2520 p_account_name => jai_constants.liability_interim
2521 );
2522 t_gl_entries(rec).organization_id := r_mtl_trx_info.from_organization;
2523 t_gl_entries(rec).organization_code := l_from_org_cd;
2524 IF t_gl_entries(rec).debit_account IS NULL THEN
2525 p_process_status := 'E';
2526 p_process_message := 'Intransit Inventory a/c is null for '||main_rec.tax_type;
2527 RETURN;
2528 ELSIF t_gl_entries(rec).credit_account IS NULL THEN
2529 p_process_status := 'E';
2530 p_process_message := 'Interim Liablility a/c is null for'||main_rec.tax_type;
2531 RETURN;
2532 END IF;
2533 rec := rec + 1;
2534
2535 END IF;
2536
2537 ELSIF r_get_interorg_params.intransit_type = 1
2538 and p_register_type = 'RVAT' and main_rec.tax_type <> 'VAT RECOVERY' /* Direct */ THEN
2539 t_gl_entries(rec).amount := main_rec.tax_amount;
2540 t_gl_entries(rec).debit_account := r_get_interorg_params.interorg_receivables_account;
2541 stmt_name := 'Calling jai_cmn_rgm_recording_pkg.get_account()';
2542 t_gl_entries(rec).credit_account := jai_cmn_rgm_recording_pkg.get_account(
2543 p_regime_id => l_regime_id,
2544 p_organization_type => jai_constants.orgn_type_io,
2545 p_organization_id => r_mtl_trx_info.from_organization,
2546 p_location_id => ln_from_loc_id,
2547 p_tax_type => main_rec.tax_type,
2548 p_account_name => jai_constants.liability_interim
2549 );
2550 t_gl_entries(rec).organization_id := r_mtl_trx_info.from_organization;
2551 t_gl_entries(rec).organization_code := l_from_org_cd;
2552 IF t_gl_entries(rec).debit_account IS NULL THEN
2553 p_process_status := 'E';
2554 p_process_message := 'InterOrg Receivable a/c is null';
2555 RETURN;
2556 ELSIF t_gl_entries(rec).credit_account IS NULL THEN
2557 p_process_status := 'E';
2558 p_process_message := 'Interim Liablility a/c is null for'||main_rec.tax_type;
2559 RETURN;
2560 END IF;
2561 rec := rec + 1;
2562
2563 t_gl_entries(rec).amount := main_rec.tax_amount;
2564 t_gl_entries(rec).debit_account := r_rcv_params.receiving_account_id;
2565 t_gl_entries(rec).credit_account := r_get_interorg_params.interorg_payables_account;
2566 t_gl_entries(rec).organization_id := r_mtl_trx_info.to_organization;
2567 t_gl_entries(rec).organization_code := l_to_org_cd;
2568 IF t_gl_entries(rec).debit_account IS NULL THEN
2569 p_process_status := 'E';
2570 p_process_message := 'Inventory Receiving a/c is Null';
2571 RETURN;
2572 ELSIF t_gl_entries(rec).credit_account IS NULL THEN
2573 p_process_status := 'E';
2574 p_process_message := 'InterOrg Payables a/c is null';
2575 RETURN;
2576 END IF;
2577 rec := rec + 1;
2578
2579 -- starts here code for VAT
2580 t_gl_entries(rec).amount := main_rec.tax_amount;
2581 t_gl_entries(rec).debit_account := jai_cmn_rgm_recording_pkg.get_account(
2582 p_regime_id => l_regime_id,
2583 p_organization_type => jai_constants.orgn_type_io,
2584 p_organization_id => r_mtl_trx_info.to_organization,
2585 p_location_id => r_mtl_trx_info.location_id,
2586 p_tax_type => main_rec.tax_type,
2587 p_account_name => jai_constants.recovery_interim
2588 );
2589 IF t_gl_entries(rec).debit_account IS NULL THEN
2590 p_process_status := 'E';
2591 p_process_message := 'Interim Recovery Account is not defined for ' || main_rec.tax_type;
2592 RETURN;
2593 END IF;
2594 t_gl_entries(rec).credit_account := r_rcv_params.receiving_account_id;
2595 IF t_gl_entries(rec).credit_account IS NULL THEN
2596 p_process_status := 'E';
2597 p_process_message := 'Receiving Invetory is not defined ';
2598 RETURN;
2599 END IF;
2600 t_gl_entries(rec).organization_id := r_mtl_trx_info.to_organization;
2601 t_gl_entries(rec).organization_code := l_to_org_cd;
2602 rec := rec + 1;
2603
2604
2605 t_gl_entries(rec).amount := main_rec.tax_amount;
2606 t_gl_entries(rec).debit_account := jai_cmn_rgm_recording_pkg.get_account(
2607 p_regime_id => l_regime_id,
2608 p_organization_type => jai_constants.orgn_type_io,
2609 p_organization_id => r_mtl_trx_info.to_organization,
2610 p_location_id => r_mtl_trx_info.location_id,
2611 p_tax_type => main_rec.tax_type,
2612 p_account_name => jai_constants.recovery
2613 );
2614
2615 IF t_gl_entries(rec).debit_account IS NULL THEN
2616 p_process_status := 'E';
2617 p_process_message := 'Recovery Account not defined for ' || main_rec.tax_type;
2618 RETURN;
2619 END IF;
2620 t_gl_entries(rec).credit_account := jai_cmn_rgm_recording_pkg.get_account(
2621 p_regime_id => l_regime_id,
2622 p_organization_type => jai_constants.orgn_type_io,
2623 p_organization_id => r_mtl_trx_info.to_organization,
2624 p_location_id => r_mtl_trx_info.location_id,
2625 p_tax_type => main_rec.tax_type,
2626 p_account_name => jai_constants.recovery_interim
2627 );
2628 IF t_gl_entries(rec).credit_account IS NULL THEN
2629 p_process_status := 'E';
2630 p_process_message := 'Interim Recovery Account not defined for ' || main_rec.tax_type;
2631 RETURN;
2632 END IF;
2633 t_gl_entries(rec).organization_id := r_mtl_trx_info.to_organization;
2634 t_gl_entries(rec).organization_code := l_to_org_cd;
2635 rec := rec + 1;
2636
2637 -- ends here
2638 END IF;
2639
2640 ELSIF main_rec.tax_type_val =0 and p_register_type is null then
2641 /* other tax types */
2642 IF r_get_interorg_params.intransit_type = 2 THEN /* Intransit */
2643 IF main_rec.tax_type <> 'VAT REVERSAL' THEN
2644 IF r_get_interorg_params.fob_point = 1 THEN -- Shippments
2645
2646 t_gl_entries(rec).amount := main_rec.tax_amount;
2647 t_gl_entries(rec).debit_account := r_get_interorg_params.interorg_receivables_account;
2648 t_gl_entries(rec).credit_account := main_rec.tax_account_id;
2649 t_gl_entries(rec).organization_id := r_mtl_trx_info.from_organization;
2650 t_gl_entries(rec).organization_code := l_from_org_cd;
2651 IF t_gl_entries(rec).debit_account IS NULL THEN
2652 p_process_status := 'E';
2653 p_process_message := 'InterOrg Receivables a/c is Null';
2654 RETURN;
2655 ELSIF t_gl_entries(rec).credit_account IS NULL THEN
2656 p_process_status := 'E';
2657 p_process_message := 'Other Taxes account is null';
2658 RETURN;
2659 END IF;
2660 rec := rec + 1;
2661
2662 t_gl_entries(rec).amount := main_rec.tax_amount;
2663 t_gl_entries(rec).debit_account := r_get_interorg_params.intransit_inv_account;
2664 t_gl_entries(rec).credit_account := r_get_interorg_params.interorg_payables_account;
2665 t_gl_entries(rec).organization_id := r_mtl_trx_info.to_organization;
2666 t_gl_entries(rec).organization_code := l_to_org_cd;
2667 IF t_gl_entries(rec).debit_account IS NULL THEN
2668 p_process_status := 'E';
2669 p_process_message := 'Intransit Inventory a/c is Null';
2670 RETURN;
2671 ELSIF t_gl_entries(rec).credit_account IS NULL THEN
2672 p_process_status := 'E';
2673 p_process_message := 'InterOrg Payable a/c is null';
2674 RETURN;
2675 END IF;
2676 rec := rec + 1;
2677 ELSIF r_get_interorg_params.fob_point = 2 THEN -- Receipts
2678
2679 IF r_get_interorg_params.intransit_inv_account IS NULL THEN
2680 p_process_status := 'E';
2681 p_process_message := 'Intransit Inventory a/c is Null';
2682 RETURN;
2683 ELSIF main_rec.tax_account_id IS NULL THEN
2684 p_process_status := 'E';
2685 p_process_message := 'Other Taxes a/c is null';
2686 RETURN;
2687 END IF;
2688 t_gl_entries(rec).amount := main_rec.tax_amount;
2689 t_gl_entries(rec).debit_account := r_get_interorg_params.intransit_inv_account;
2690 t_gl_entries(rec).credit_account := main_rec.tax_account_id;
2691 t_gl_entries(rec).organization_id := r_mtl_trx_info.from_organization;
2692 t_gl_entries(rec).organization_code := l_from_org_cd;
2693 rec := rec + 1;
2694 END IF;
2695 -- do for vat reversal here
2696 ELSIF main_rec.tax_type = 'VAT REVERSAL' then
2697 /*
2698 debit expense accnt
2699 credit VAT recovery Account
2700 */
2701 t_gl_entries(rec).amount := main_rec.tax_amount;
2702 t_gl_entries(rec).debit_account := jai_cmn_rgm_recording_pkg.get_account(
2703 p_regime_id => l_regime_id,
2704 p_organization_type => jai_constants.orgn_type_io,
2705 p_organization_id => r_mtl_trx_info.from_organization,
2706 p_location_id => ln_from_loc_id,
2707 p_tax_type => main_rec.tax_type,
2708 p_account_name => 'EXPENSE'
2709 );
2710
2711 t_gl_entries(rec).credit_account := jai_cmn_rgm_recording_pkg.get_account(
2712 p_regime_id => l_regime_id,
2713 p_organization_type => jai_constants.orgn_type_io,
2714 p_organization_id => r_mtl_trx_info.from_organization,
2715 p_location_id => ln_from_loc_id,
2716 p_tax_type => main_rec.tax_type,
2717 p_account_name => 'RECOVERY'
2718 );
2719 IF t_gl_entries(rec).credit_account IS NULL THEN
2720 p_process_status := 'E';
2721 p_process_message := 'Recovery Account not setup in receiving Org for ' || main_rec.tax_type ;
2722 RETURN;
2723 END IF;
2724 IF t_gl_entries(rec).debit_account IS NULL THEN
2725 p_process_status := 'E';
2726 p_process_message := 'Expense Account not setup in receiving Org for ' || main_rec.tax_type ;
2727 RETURN;
2728 END IF;
2729 t_gl_entries(rec).organization_id := r_mtl_trx_info.from_organization;
2730 t_gl_entries(rec).organization_code := l_from_org_cd;
2731
2732 rec := rec + 1;
2733 -- ends here for vat reversal
2734 END IF;
2735 ELSIF r_get_interorg_params.intransit_type = 1 and main_rec.tax_type <> 'VAT REVERSAL' /* Direct */ THEN
2736
2737 IF r_get_interorg_params.interorg_receivables_account IS NULL THEN
2738 p_process_status := 'E';
2739 p_process_message := 'Inter-Organization Recevable a/c is Null';
2740 RETURN;
2741 ELSIF main_rec.tax_account_id IS NULL THEN
2742 p_process_status := 'E';
2743 p_process_message := 'Other Taxes a/c is null';
2744 RETURN;
2745 END IF;
2746 t_gl_entries(rec).amount := main_rec.tax_amount;
2747 t_gl_entries(rec).debit_account := r_get_interorg_params.interorg_receivables_account;
2748 t_gl_entries(rec).credit_account := main_rec.tax_account_id;
2749 t_gl_entries(rec).organization_id := r_mtl_trx_info.from_organization;
2750 t_gl_entries(rec).organization_code := l_from_org_cd;
2751 rec := rec + 1;
2752
2753 IF r_get_interorg_params.interorg_payables_account IS NULL THEN
2754 p_process_status := 'E';
2755 p_process_message := 'Inter-org Payables a/c is null';
2756 RETURN;
2757 ELSIF r_rcv_params.receiving_account_id IS NULL THEN
2758 p_process_status := 'E';
2759 p_process_message := 'Inventory Receiving a/c is null';
2760 RETURN;
2761 END IF;
2762 t_gl_entries(rec).amount := main_rec.tax_amount;
2763 t_gl_entries(rec).debit_account := r_rcv_params.receiving_account_id;
2764 t_gl_entries(rec).credit_account := r_get_interorg_params.interorg_payables_account;
2765 t_gl_entries(rec).organization_id := r_mtl_trx_info.to_organization;
2766 t_gl_entries(rec).organization_code := l_to_org_cd;
2767 rec := rec + 1;
2768 -- for vat reversal in direct org transfer
2769 ELSIF main_rec.tax_type = 'VAT REVERSAL' then
2770 /*
2771 debit expense accnt
2772 credit VAT recovery Account
2773 */
2774 t_gl_entries(rec).amount := main_rec.tax_amount;
2775 t_gl_entries(rec).debit_account := jai_cmn_rgm_recording_pkg.get_account(
2776 p_regime_id => l_regime_id,
2777 p_organization_type => jai_constants.orgn_type_io,
2778 p_organization_id => r_mtl_trx_info.from_organization,
2779 p_location_id => ln_from_loc_id,
2780 p_tax_type => main_rec.tax_type,
2781 p_account_name => 'EXPENSE'
2782 );
2783
2784 t_gl_entries(rec).credit_account := jai_cmn_rgm_recording_pkg.get_account(
2785 p_regime_id => l_regime_id,
2786 p_organization_type => jai_constants.orgn_type_io,
2787 p_organization_id => r_mtl_trx_info.from_organization,
2788 p_location_id => ln_from_loc_id,
2789 p_tax_type => main_rec.tax_type,
2790 p_account_name => 'RECOVERY'
2791 );
2792 IF t_gl_entries(rec).credit_account IS NULL THEN
2793 p_process_status := 'E';
2794 p_process_message := 'Recovery Account not setup in receiving Org for ' || main_rec.tax_type ;
2795 RETURN;
2796 END IF;
2797 IF t_gl_entries(rec).debit_account IS NULL THEN
2798 p_process_status := 'E';
2799 p_process_message := 'Expense Account not setup in receiving Org for ' || main_rec.tax_type ;
2800 RETURN;
2801 END IF;
2802 t_gl_entries(rec).organization_id := r_mtl_trx_info.from_organization;
2803 t_gl_entries(rec).organization_code := l_from_org_cd;
2804
2805 rec := rec + 1;
2806 -- ends here for vat reversal
2807 END IF;
2808
2809 END IF;
2810
2811 END LOOP;
2812 IF r_get_interorg_params.intransit_type = 1 and p_register_type is NULL THEN
2813 do_costing(p_transaction_temp_id,
2814 lv_process_flag,
2815 lv_process_message);
2816 END IF;
2817
2818
2819 stmt_name := 'Calling gl_entry()';
2820 gl_entry(t_gl_entries,
2821 p_set_of_books_id,
2822 p_je_source_name,
2823 p_je_category_name,
2824 p_currency_code,
2825 p_transaction_temp_id,
2826 p_process_status,
2827 p_process_message);
2828
2829 EXCEPTION
2830 WHEN OTHERS THEN
2831 p_process_status := 'E';
2832 p_process_message := 'Encountered an error when doing GL Entries '||stmt_name||' :'||sqlcode||': '||sqlerrm;
2833 ROLLBACK TO gl_acctg;
2834 END;
2835
2836
2837 procedure do_costing
2838 (
2839 transaction_id IN NUMBER,
2840 process_flag OUT NOCOPY varchar2,
2841 process_msg OUT NOCOPY varchar2
2842 )
2843 is
2844 CURSOR c_mtl_types(cp_transaction_type_name IN VARCHAR2) IS
2845 SELECT transaction_type_id, transaction_source_type_id, transaction_action_id
2846 FROM mtl_transaction_types
2847 WHERE transaction_type_name = cp_transaction_type_name;
2848
2849 CURSOR c_costing_group ( cp_organization_id IN NUMBER) IS
2850 SELECT mp.default_cost_group_id
2851 FROM mtl_parameters mp
2852 WHERE mp.organization_id = cp_organization_id
2853 AND mp.primary_cost_method = 2; --Average
2854
2855 CURSOR c_rcv_params( cp_organization_id IN NUMBER) IS
2856 SELECT * from rcv_parameters
2857 WHERE organization_id = cp_organization_id;
2858
2859 CURSOR c_mtl_params(cp_organization_id IN NUMBER) IS
2860 SELECT *
2861 FROM mtl_parameters
2862 WHERE organization_id = cp_organization_id;
2863
2864 CURSOR c_jai_mtl_Trxs ( cp_Trx_temp_id IN NUMBER ) is
2865 SELECT * from jai_mtl_Trxs
2866 WHERE transaction_Temp_id = cp_Trx_temp_id;
2867
2868
2869 CURSOR c_proc_exists(cp_object_name user_procedures.object_name%type ,
2870 cp_procedure_name user_procedures.procedure_name%type) IS
2871
2872 SELECT 1
2873 FROM user_procedures
2874 WHERE object_name = cp_object_name
2875 AND procedure_name = cp_procedure_name ;
2876
2877 /*rchandan for bug#6487489..start*/
2878
2879 CURSOR c_ppv_acct(cp_from_organization_id NUMBER,cp_to_organization_id NUMBER)
2880 IS
2881 SELECT INTERORG_PRICE_VAR_ACCOUNT
2882 FROM mtl_interorg_parameters
2883 WHERE from_organization_id = cp_from_organization_id
2884 AND to_organization_id = cp_to_organization_id ;
2885
2886 CURSOR cur_get_bonded(cp_organization_id NUMBER,
2887 cp_sub_inventory VARCHAR2)
2888 IS
2889 SELECT bonded
2890 FROM jai_inv_subinv_dtls
2891 WHERE organization_id = cp_organization_id
2892 AND sub_inventory_name = cp_sub_inventory;
2893
2894 ln_cost_amount NUMBER;
2895 lv_bonded jai_inv_subinv_dtls.bonded%TYPE;
2896 ln_ppv_acct_id NUMBER;/*6487489*/
2897
2898
2899 /*rchandan for bug#6487489..end*/
2900
2901 p_trx_temp_id NUMBER:= transaction_id ;--11015556;
2902
2903 ln_txn_header_id NUMBER;
2904 r_mtl_types c_mtl_types%ROWTYPE;
2905 r_Rcv_params c_rcv_params%ROWTYPE;
2906 r_mtl_params c_mtl_params%Rowtype;
2907 ln_costing_grp_id NUMBER;
2908 lv_trx_type_name VARCHAR2(30);
2909 r_mtl_Trxs c_jai_mtl_Trxs%ROWTYPE;
2910
2911 ln_Excise_amt NUMBER;
2912 ln_non_modvat_amt NUMBER;
2913 ln_oth_modvat_amt NUMBER;
2914 lv_process_msg VARCHAR2(2000);
2915 lv_process_status VARCHAR2(20);
2916 lv_object_name user_procedures.object_name%type ;
2917 lv_procedure_name user_procedures.procedure_name%type ;
2918 ln_exists NUMBER := 0 ;
2919 lv_sqlstmt VARCHAR2(2000) ;
2920 ln_retval NUMBER;
2921 lv_return_status VARCHAR2(10);
2922 ln_msg_cnt NUMBER;
2923 lv_msg_data VARCHAR2(2000);
2924 ln_trans_count NUMBER;
2925
2926
2927 cursor cur_item_class /*6501436*/
2928 IS
2929 SELECT jiis.item_class
2930 FROM jai_mtl_trxs jmt,
2931 JAI_INV_ITM_SETUPS jiis
2932 WHERE jmt.inventory_item_id = jiis.inventory_item_id
2933 AND jmt.transaction_temp_id = p_trx_temp_id;
2934
2935 lv_item_class jai_inv_itm_setups.item_class%TYPE;/*6501436*/
2936 BEGIN
2937
2938 lv_trx_type_name := 'Average cost update' ;--'Direct Org Transfer';
2939
2940 OPEN c_jai_mtl_Trxs(p_trx_temp_id);
2941 FETCH c_jai_mtl_Trxs INTO r_mtl_Trxs;
2942 CLOSE c_jai_mtl_Trxs;
2943
2944 Open c_mtl_types(lv_trx_type_name);
2945 FETCH c_mtl_types INTO r_mtl_types;
2946 CLOSE c_mtl_types;
2947
2948 OPEN c_costing_group(r_mtl_Trxs.to_organization);
2949 FETCH c_costing_group INTO ln_costing_grp_id;
2950 CLOSE c_costing_group;
2951
2952 OPEN c_rcv_params(r_mtl_Trxs.to_organization);
2953 FETCH c_rcv_params INTO r_Rcv_params;
2954 CLOSE c_rcv_params;
2955
2956 OPEN c_mtl_params(r_mtl_Trxs.to_organization);
2957 FETCH c_mtl_params INTO r_mtl_params;
2958 CLOSE c_mtl_params;
2959
2960
2961 /*
2962 || This procedure would get the cost amount
2963 || Need to pass the transaction_temp_id as the param for p_trx_temp_id
2964 || ln_non_modvat_amt variable would have the cost.
2965 */
2966
2967 get_cost_amt
2968 ( p_source_line_id => p_trx_temp_id ,
2969 p_organization_id => r_mtl_Trxs.to_organization,
2970 p_location_id => r_mtl_Trxs.location_id,
2971 p_item_id => r_mtl_Trxs.inventory_item_id,
2972 p_excise_amount => ln_Excise_amt,
2973 p_non_modvat_amount => ln_non_modvat_amt,
2974 p_other_modvat_amount => ln_oth_modvat_amt,
2975 p_process_message => lv_process_msg,
2976 p_process_status => lv_process_status );
2977
2978 /*
2979 || avg costing if r_mtl_params.primary cost thod = 2
2980 */
2981
2982 If r_mtl_params.primary_cost_method=2 then
2983 avg_cost_entry( p_txn_header_id => ln_txn_header_id ,
2984 p_item_id => r_mtl_Trxs.inventory_item_id,
2985 p_organization_id => r_mtl_Trxs.to_organization,
2986 p_uom_code => r_mtl_Trxs.transaction_uom,
2987 p_transaction_date => r_mtl_Trxs.transaction_Date,
2988 p_transaction_type_id => r_mtl_types.transaction_type_id,
2989 p_transaction_source_type_id => r_mtl_types.transaction_source_type_id ,
2990 p_transaction_id => p_trx_temp_id ,
2991 p_cost_group_id => ln_costing_grp_id ,
2992 p_receiving_account_id => r_Rcv_params.receiving_account_id,
2993 p_absorption_account_id => r_Rcv_params.receiving_account_id,
2994 p_value_change => ln_non_modvat_amt,
2995 p_transaction_action_id => r_mtl_types.transaction_action_id,
2996 p_from_organization_id => r_mtl_trxs.from_organization,
2997 p_from_subinventory => r_mtl_trxs.from_subinventory,
2998 p_to_subinventory => r_mtl_trxs.to_subinventory,
2999 p_txn_quantity => 0
3000 );
3001
3002 lv_object_name := 'INV_TXN_MANAGER_PUB' ;
3003 lv_procedure_name := 'PROCESS_TRANSACTIONS' ;
3004
3005 OPEN c_proc_exists(lv_object_name, lv_procedure_name) ;
3006 FETCH c_proc_exists INTO ln_exists ;
3007 CLOSE c_proc_exists ;
3008
3009 IF ln_exists = 1 THEN
3010 lv_sqlstmt := 'BEGIN
3011 :ln_retval := inv_txn_manager_pub.process_transactions (
3012 p_api_version => 1,
3013 p_init_msg_list => :fnd_api_g_false ,
3014 p_commit => :fnd_api_g_false1 ,
3015 p_validation_level => :fnd_api_g_valid_level_full ,
3016 x_return_status => :lv_return_status,
3017 x_msg_count => :ln_msg_cnt,
3018 x_msg_data => :lv_msg_data,
3019 x_trans_count => :ln_trans_count,
3020 p_table => 1,
3021 p_header_id => :ln_txn_header_id
3022 );
3023 END; ';
3024 EXECUTE IMMEDIATE lv_sqlstmt USING OUT ln_retval ,
3025 IN fnd_api.g_false ,
3026 IN fnd_api.g_false ,
3027 IN fnd_api.g_valid_level_full ,
3028 OUT lv_return_status ,
3029 OUT ln_msg_cnt ,
3030 OUT lv_msg_data ,
3031 OUT ln_trans_count ,
3032 IN ln_txn_header_id ;
3033
3034
3035 END IF;
3036
3037
3038 /*
3039 || std costing if r_mtl_params.primary cost thod = 1
3040 */
3041
3042 elsif r_mtl_params.primary_cost_method=1 then
3043
3044 OPEN cur_item_class;/*6501436*/
3045 FETCH cur_item_class INTO lv_item_class;
3046 CLOSE cur_item_class;
3047
3048 /*6487489 start*/
3049
3050 OPEN c_ppv_acct(r_mtl_Trxs.from_organization,r_mtl_Trxs.to_organization);
3051 FETCH c_ppv_acct INTO ln_ppv_acct_id;
3052 CLOSE c_ppv_acct;
3053
3054 OPEN cur_get_bonded(r_mtl_Trxs.to_organization,r_mtl_Trxs.to_subinventory);
3055 FETCH cur_get_bonded INTO lv_bonded;
3056 CLOSE cur_get_bonded;
3057
3058 IF lv_bonded = 'N' or lv_item_class IN ('FGIN','FGEX') THEN /*6501436..Included item check*/
3059
3060 ln_cost_amount := nvl(ln_Excise_amt,0) + nvl(ln_non_modvat_amt,0) ; /*it should include Excisable recoverable taxes and Non recoverable taxes*/
3061
3062 ELSE
3063
3064 ln_cost_amount := nvl(ln_non_modvat_amt,0);
3065
3066 END IF;
3067
3068 /*6487489 end*/
3069
3070
3071 IF ln_cost_amount <> 0 THEN /*6487489*/
3072
3073 std_cost_entry(
3074 p_transaction_id => p_trx_temp_id,
3075 p_reference_account => ln_ppv_acct_id,/*6487489..replaced material account*/
3076 p_inventory_item_id => r_mtl_Trxs.inventory_item_id,
3077 p_organization_id => r_mtl_Trxs.to_organization,
3078 p_transaction_source_id => p_trx_temp_id,
3079 p_transaction_source_type_id => r_mtl_types.transaction_source_type_id,
3080 p_primary_quantity => r_mtl_Trxs.quantity,
3081 p_transaction_date => r_mtl_Trxs.transaction_Date,
3082 p_cost_amount => ln_cost_amount,
3083 p_process_flag => lv_process_status ,
3084 p_process_msg => lv_process_msg);
3085
3086 end if;
3087
3088 end if;
3089
3090
3091 END do_costing;
3092
3093 PROCEDURE avg_cost_entry(
3094 p_txn_header_id IN OUT NOCOPY NUMBER,
3095 p_item_id IN NUMBER,
3096 p_organization_id IN NUMBER,
3097 p_uom_code IN VARCHAR2,
3098 p_transaction_date IN DATE,
3099 p_transaction_type_id IN NUMBER,
3100 p_transaction_source_type_id IN NUMBER,
3101 p_transaction_id IN NUMBER,
3102 p_cost_group_id IN NUMBER,
3103 p_receiving_account_id IN NUMBER,
3104 p_absorption_account_id IN NUMBER,
3105 p_value_change IN NUMBER,
3106 p_transaction_action_id IN NUMBER,
3107 p_from_organization_id IN NUMBER,
3108 p_from_subinventory IN VARCHAR2,
3109 p_to_subinventory IN VARCHAR2,
3110 p_txn_quantity IN NUMBER
3111 ) IS
3112
3113 ln_txn_interface_id NUMBER;
3114
3115 -- Default Values
3116 lv_transaction_source_name VARCHAR2(30) := 'Avg Cost Update Conversion';
3117 lv_source_code VARCHAR2(50) := 'IL-Value Change - Direct Org';
3118 ln_src_line_id NUMBER := -1;
3119 ln_src_header_id NUMBER := -1;
3120 ln_process_flag NUMBER := 1;
3121 ln_transaction_mode NUMBER := 3;
3122 ln_quantity NUMBER := p_txn_quantity;
3123 ln_lock_flag NUMBER := 2; -- No Lock
3124 ln_material_cost_element_id NUMBER := 1; -- Material
3125 ln_overhead_cost_element_id NUMBER := 2; -- Material
3126 ln_level_type NUMBER := 1; -- This Level
3127
3128 BEGIN
3129
3130 INSERT INTO mtl_transactions_interface (
3131 source_code ,
3132 source_line_id ,
3133 source_header_id ,
3134 process_flag ,
3135 transaction_mode ,
3136 transaction_interface_id ,
3137 transaction_header_id ,
3138 inventory_item_id ,
3139 organization_id ,
3140 revision ,
3141 transaction_quantity ,
3142 transaction_uom ,
3143 transaction_date ,
3144 transaction_source_name ,
3145 transaction_type_id ,
3146 transaction_source_type_Id ,
3147 rcv_transaction_id ,
3148 transaction_reference ,-- mtl_transaction Id.
3149 last_update_date ,
3150 last_updated_by ,
3151 creation_date ,
3152 created_by ,
3153 cost_group_id ,
3154 material_account ,
3155 material_overhead_account ,--overhead absorption account
3156 resource_account ,
3157 overhead_account ,
3158 outside_processing_account ,
3159 lock_flag ,
3160 transaction_action_id ,
3161 transfer_organization,
3162 transfer_subinventory ,
3163 subinventory_code ,
3164 value_change
3165 )
3166 VALUES (
3167 lv_source_code ,
3168 ln_src_line_id ,
3169 ln_src_header_id ,
3170 ln_process_flag ,
3171 ln_transaction_mode ,
3172 mtl_material_transactions_s.nextval ,
3173 decode( p_txn_header_id, null ,
3174 mtl_material_transactions_s.currval ,
3175 p_txn_header_id
3176 ) ,
3177 p_item_id ,
3178 p_organization_id ,
3179 null ,
3180 ln_quantity , -- No Qty
3181 p_uom_code ,
3182 p_transaction_date ,
3183 lv_transaction_source_name ,
3184 p_transaction_type_id , -- Avg Cost Update
3185 p_transaction_source_type_id , -- Inventory
3186 p_transaction_id ,
3187 to_char(p_transaction_id) ,
3188 sysdate ,
3189 fnd_global.user_id ,
3190 sysdate ,
3191 fnd_global.user_id ,
3192 p_cost_group_id ,
3193 p_receiving_account_id ,
3194 p_absorption_account_id ,
3195 p_receiving_account_id ,
3196 p_receiving_account_id ,
3197 p_receiving_account_id ,
3198 ln_lock_flag ,
3199 p_transaction_action_id ,
3200 p_from_organization_id ,
3201 p_from_subinventory,
3202 p_to_subinventory ,
3203 p_value_change
3204 )
3205 RETURNING transaction_interface_id ,
3206 transaction_header_id
3207 INTO ln_txn_interface_id ,
3208 p_txn_header_id ;
3209
3210
3211
3212
3213
3214 INSERT INTO JAI_MTL_TXN_CST_HDR_T (
3215 source_code ,
3216 source_line_id ,
3217 source_header_id ,
3218 process_flag ,
3219 transaction_mode ,
3220 transaction_interface_id ,
3221 transaction_header_id ,
3222 inventory_item_id ,
3223 organization_id ,
3224 revision ,
3225 transaction_quantity ,
3226 transaction_uom ,
3227 transaction_date ,
3228 transaction_source_name ,
3229 transaction_type_id ,
3230 transaction_source_type_Id , --PVI
3231 rcv_transaction_id ,
3232 transaction_reference , -- rcv_transaction Id.
3233 last_update_date ,
3234 last_updated_by ,
3235 creation_date ,
3236 created_by ,
3237 cost_group_id ,
3238 material_account ,
3239 material_overhead_account , --overhead absorption account
3240 resource_account ,
3241 overhead_account ,
3242 outside_processing_account ,
3243 lock_flag ,
3244 transaction_id
3245 )
3246
3247
3248 VALUES (
3249 lv_source_code ,
3250 ln_src_line_id ,
3251 ln_src_header_id ,
3252 ln_process_flag ,
3253 ln_transaction_mode ,
3254 ln_txn_interface_id ,
3255 p_txn_header_id ,
3256 p_item_id ,
3257 p_organization_id ,
3258 null ,
3259 ln_quantity , -- No Qty
3260 p_uom_code ,
3261 p_transaction_date ,
3262 lv_transaction_source_name ,
3263 p_transaction_type_id , -- Avg Cost Update
3264 p_transaction_source_type_id , -- Inventory
3265 p_transaction_id ,
3266 to_char(p_transaction_id) ,
3267 sysdate ,
3268 fnd_global.user_id ,
3269 sysdate ,
3270 fnd_global.user_id ,
3271 p_cost_group_id ,
3272 p_receiving_account_id ,
3273 p_absorption_account_id ,
3274 p_receiving_account_id ,
3275 p_receiving_account_id ,
3276 p_receiving_account_id ,
3277 ln_lock_flag ,
3278 ln_txn_interface_id
3279 ) ;
3280
3281
3282 INSERT INTO mtl_txn_cost_det_interface (
3283 transaction_interface_id ,
3284 last_update_date ,
3285 last_updated_by ,
3286 creation_date ,
3287 created_by ,
3288 organization_id ,
3289 cost_element_id ,
3290 level_type ,
3291 value_change
3292 )
3293 VALUES (
3294 ln_txn_interface_id ,
3295 sysdate ,
3296 fnd_global.user_id ,
3297 sysdate ,
3298 fnd_global.user_id ,
3299 p_organization_id ,
3300 ln_material_cost_element_id ,
3301 ln_level_type ,
3302 p_value_change
3303 );
3304
3305 INSERT INTO JAI_MTL_TXN_CST_DTL_T(
3306 transaction_interface_id ,
3307 last_update_date ,
3308 last_updated_by ,
3309 creation_date ,
3310 created_by ,
3311 organization_id ,
3312 cost_element_id ,
3313 level_type ,
3314 value_change
3315 )
3316 VALUES (
3317 ln_txn_interface_id ,
3318 sysdate ,
3319 fnd_global.user_id ,
3320 sysdate ,
3321 fnd_global.user_id ,
3322 p_organization_id ,
3323 ln_material_cost_element_id ,
3324 ln_level_type ,
3325 p_value_change
3326 );
3327
3328
3329
3330
3331 INSERT INTO mtl_txn_cost_det_interface
3332 (
3333 transaction_interface_id,
3334 last_update_date,
3335 last_updated_by,
3336 creation_date,
3337 created_by,
3338 organization_id,
3339 cost_element_id,
3340 level_type,
3341 value_change
3342 )
3343 (SELECT
3344 ln_txn_interface_id ,
3345 sysdate ,
3346 fnd_global.user_id ,
3347 sysdate ,
3348 fnd_global.user_id ,
3349 p_organization_id ,
3350 clcd.cost_element_id ,
3351 clcd.level_type ,
3352 0
3353 FROM
3354 cst_layer_cost_details clcd,
3355 cst_quantity_layers cql
3356 WHERE
3357 cql.organization_id = p_organization_id
3358 and cql.inventory_item_id = p_item_id
3359 and cql.cost_group_id = p_cost_group_id
3360 and clcd.layer_id = cql.layer_id
3361 and (clcd.cost_element_id,clcd.level_type) NOT IN
3362 ( SELECT
3363 mctcd1.cost_element_id,
3364 mctcd1.level_type
3365 FROM
3366 mtl_txn_cost_det_interface mctcd1
3367 WHERE
3368 mctcd1.transaction_interface_id = ln_txn_interface_id
3369 )
3370 );
3371
3372 INSERT INTO JAI_MTL_TXN_CST_DTL_T
3373 (
3374 transaction_interface_id,
3375 last_update_date,
3376 last_updated_by,
3377 creation_date,
3378 created_by,
3379 organization_id,
3380 cost_element_id,
3381 level_type,
3382 value_change
3383 )
3384 (SELECT
3385 ln_txn_interface_id ,
3386 sysdate ,
3387 fnd_global.user_id ,
3388 sysdate ,
3389 fnd_global.user_id ,
3390 p_organization_id ,
3391 clcd.cost_element_id ,
3392 clcd.level_type ,
3393 0
3394 FROM
3395 cst_layer_cost_details clcd,
3396 cst_quantity_layers cql
3397 WHERE
3398 cql.organization_id = p_organization_id
3399 and cql.inventory_item_id = p_item_id
3400 and cql.cost_group_id = p_cost_group_id
3401 and clcd.layer_id = cql.layer_id
3402 and (clcd.cost_element_id,clcd.level_type) NOT IN
3403 ( SELECT
3404 mctcd1.cost_element_id,
3405 mctcd1.level_type
3406 FROM
3407 jai_mtl_txn_cst_dtl_t mctcd1
3408 WHERE
3409 mctcd1.transaction_interface_id = ln_txn_interface_id
3410 )
3411 );
3412
3413 end avg_cost_entry;
3414
3415 PROCEDURE std_cost_entry(
3416 p_transaction_id IN NUMBER,
3417 p_reference_account IN NUMBER,
3418 p_inventory_item_id IN NUMBER,
3419 p_organization_id IN NUMBER,
3420 p_transaction_source_id IN NUMBER,
3421 p_transaction_source_type_id IN NUMBER,
3422 p_primary_quantity IN NUMBER,
3423 p_transaction_date IN DATE,
3424 p_cost_amount IN NUMBER,
3425 p_process_flag OUT NOCOPY VARCHAR2,
3426 p_process_msg OUT NOCOPY VARCHAR2
3427 )
3428 is
3429 lv_organization_code org_organization_definitions.organization_code%type;
3430 ln_set_of_books_id org_organization_definitions.set_of_books_id%type;
3431
3432 l_func_curr_det jai_plsql_cache_pkg.func_curr_details;
3433 lv_status gl_interface.status%type;
3434 lv_reference_entry gl_interface.reference22%type;
3435 lv_reference_10 gl_interface.reference10%TYPE;
3436 lv_reference_23 gl_interface.reference23%TYPE;
3437 lv_reference_24 gl_interface.reference24%TYPE;
3438 lv_reference_25 gl_interface.reference25%TYPE;
3439 lv_reference_26 gl_interface.reference26%TYPE;
3440 lv_source_name gl_interface.user_je_source_name%TYPE;
3441 lv_category_name gl_interface.user_je_category_name%TYPE;
3442 lv_currency_code gl_interface.currency_code%TYPE;
3443
3444 cursor cur_rcv_accnt(cp_organization_id NUMBER) /*6487489..added the cursor*/
3445 IS
3446 select receiving_account_id
3447 from rcv_parameters
3448 Where organization_id = cp_organization_id;
3449
3450 ln_receiving_accnt_id NUMBER;
3451
3452
3453 BEGIN
3454 l_func_curr_det := jai_plsql_cache_pkg.return_sob_curr
3455 (p_org_id => p_organization_id);
3456 lv_organization_code := l_func_curr_det.organization_code;
3457 ln_set_of_books_id := l_func_curr_det.ledger_id;
3458 lv_source_name := 'Purchasing India';
3459 lv_category_name:='MMT';/*6504150*/
3460 lv_reference_10 :='India Local Standard Cost Entry For INTERORG_XFER and Organization_code= '|| lv_organization_code ;
3461 lv_reference_entry := 'India Localization Entry';
3462 lv_reference_23:='jai_mtl_trxs_pkg.std_cost_entry';
3463 lv_reference_24:='jai_mtl_trxs';
3464 lv_reference_26:='transaction_id';
3465 lv_currency_code :='INR';
3466
3467 OPEN cur_rcv_accnt(p_organization_id);
3468 FETCH cur_rcv_accnt INTO ln_receiving_accnt_id;
3469 CLOSE cur_rcv_accnt;
3470
3471 p_process_flag := 'SUCCESS';
3472
3473 lv_status := 'NEW' ;
3474
3475
3476 /*The following code is all added/modified for rchandan for bug#6487489 to generate PPV accounting*/
3477 insert into gl_interface
3478 (
3479 status,
3480 set_of_books_id,
3481 user_je_source_name,
3482 user_je_category_name,
3483 accounting_date,
3484 currency_code,
3485 date_created,
3486 created_by,
3487 actual_flag,
3488 entered_cr,
3489 entered_dr,
3490 transaction_date,
3491 code_combination_id,
3492 currency_conversion_date,
3493 user_currency_conversion_type,
3494 currency_conversion_rate,
3495 reference1,
3496 reference10,
3497 reference22,
3498 reference23,
3499 reference24,
3500 reference25,
3501 reference26,
3502 reference27
3503 )
3504 VALUES
3505 (
3506 lv_status , --'NEW',
3507 ln_set_of_books_id,
3508 lv_source_name,
3509 lv_category_name,
3510 trunc(sysdate),
3511 lv_currency_code,
3512 sysdate,
3513 fnd_global.user_id,
3514 'A',
3515 p_cost_amount,
3516 NULL,
3517 sysdate,
3518 ln_receiving_accnt_id,/*Inventory receiving Account*/
3519 NULL,
3520 NULL,
3521 NULL,
3522 lv_organization_code,
3523 lv_reference_10,
3524 lv_reference_entry,
3525 lv_reference_23,
3526 lv_reference_24,
3527 p_transaction_id,
3528 lv_reference_26,
3529 to_char(p_organization_id)
3530 );
3531
3532 insert into jai_mtl_trx_jrnls
3533 (journal_entry_id,
3534 status,
3535 set_of_books_id,
3536 user_je_source_name,
3537 user_je_category_name,
3538 accounting_date,
3539 currency_code,
3540 date_created,
3541 created_by,
3542 entered_cr,
3543 entered_dr,
3544 transaction_date,
3545 code_combination_id,
3546 currency_conversion_date,
3547 user_currency_conversion_type,
3548 currency_conversion_rate,
3549 reference1,
3550 reference10,
3551 reference23,
3552 reference24,
3553 reference25,
3554 reference26,
3555 reference27,
3556 creation_Date,
3557 last_updated_by,
3558 last_update_date,
3559 last_update_login,
3560 transaction_temp_id
3561
3562 )
3563 VALUES
3564 (jai_mtl_trx_jrnls_s.nextval,
3565 lv_status,
3566 ln_set_of_books_id,
3567 lv_source_name,
3568 lv_category_name,
3569 sysdate,
3570 lv_currency_code,
3571 sysdate,
3572 fnd_global.user_id,
3573 p_cost_amount,
3574 NULL,
3575 sysdate,
3576 ln_receiving_accnt_id,
3577 null,
3578 null,
3579 null,
3580 lv_organization_code,
3581 lv_reference_10,
3582 lv_reference_23,
3583 lv_reference_24,
3584 p_transaction_id,
3585 lv_reference_26,
3586 to_char(p_organization_id),
3587 sysdate,
3588 fnd_global.user_id,
3589 sysdate,
3590 fnd_global.login_id,
3591 p_transaction_id
3592 );
3593
3594
3595 /*removed the insert to MTA by vkaranam for bug #6030615 as part of SLA uptake*/
3596
3597
3598 insert into gl_interface
3599 (
3600 status,
3601 set_of_books_id,
3602 user_je_source_name,
3603 user_je_category_name,
3604 accounting_date,
3605 currency_code,
3606 date_created,
3607 created_by,
3608 actual_flag,
3609 entered_cr,
3610 entered_dr,
3611 transaction_date,
3612 code_combination_id,
3613 currency_conversion_date,
3614 user_currency_conversion_type,
3615 currency_conversion_rate,
3616 reference1,
3617 reference10,
3618 reference22,
3619 reference23,
3620 reference24,
3621 reference25,
3622 reference26,
3623 reference27
3624 )
3625 VALUES
3626 (
3627 lv_status , --'NEW',
3628 ln_set_of_books_id,
3629 lv_source_name,
3630 lv_category_name,
3631 trunc(sysdate),
3632 lv_currency_code,
3633 sysdate,
3634 fnd_global.user_id,
3635 'A',
3636 NULL,
3637 p_cost_amount,
3638 sysdate,
3639 p_reference_account,/*PPV Account*/
3640 NULL,
3641 NULL,
3642 NULL,
3643 lv_organization_code,
3644 lv_reference_10,
3645 lv_reference_entry,
3646 lv_reference_23,
3647 lv_reference_24,
3648 p_transaction_id,
3649 lv_reference_26,
3650 to_char(p_organization_id)
3651 );
3652
3653 insert into jai_mtl_trx_jrnls
3654 (journal_entry_id,
3655 status,
3656 set_of_books_id,
3657 user_je_source_name,
3658 user_je_category_name,
3659 accounting_date,
3660 currency_code,
3661 date_created,
3662 created_by,
3663 entered_cr,
3664 entered_dr,
3665 transaction_date,
3666 code_combination_id,
3667 currency_conversion_date,
3668 user_currency_conversion_type,
3669 currency_conversion_rate,
3670 reference1,
3671 reference10,
3672 reference23,
3673 reference24,
3674 reference25,
3675 reference26,
3676 reference27,
3677 creation_Date,
3678 last_updated_by,
3679 last_update_date,
3680 last_update_login,
3681 transaction_temp_id
3682
3683 )
3684 VALUES
3685 (jai_mtl_trx_jrnls_s.nextval,
3686 lv_status,
3687 ln_set_of_books_id,
3688 lv_source_name,
3689 lv_category_name,
3690 sysdate,
3691 lv_currency_code,
3692 sysdate,
3693 fnd_global.user_id,
3694 NULL,
3695 p_cost_amount,
3696 sysdate,
3697 p_reference_account,
3698 null,
3699 null,
3700 null,
3701 lv_organization_code,
3702 lv_reference_10,
3703 lv_reference_23,
3704 lv_reference_24,
3705 p_transaction_id,
3706 lv_reference_26,
3707 to_char(p_organization_id),
3708 sysdate,
3709 fnd_global.user_id,
3710 sysdate,
3711 fnd_global.login_id,
3712 p_transaction_id
3713 );
3714
3715
3716 exception
3717 WHEN OTHERS THEN
3718 p_process_flag := 'ERROR';
3719 p_process_msg := SQLERRM;
3720 end std_cost_entry;
3721
3722
3723 procedure cenvat_auto_claim
3724 (p_transaction_Temp_id IN NUMBER ,
3725 p_shipment_line_id IN NUMBER ,
3726 p_applied_quantity IN NUMBER
3727 ) IS
3728 CURSOR c_rcpt_dtls(cp_shipment_line_id IN NUMBER) Is
3729 SELECT * from jai_rcv_lines
3730 WHERE shipment_line_id = cp_shipment_line_id;
3731
3732 CURSOR c_recpt_tax_dtls(cp_shipment_line_id IN NUMBER) IS
3733 SELECT * from JAI_RCV_LINE_TAXES
3734 WHERE shipment_line_id = cp_shipment_line_id;
3735
3736 CURSOR c_trx (cp_transaction_id IN NUMBER) IS
3737 SELECT * from JAI_RCV_TRANSACTIONS
3738 WHERE transaction_id = cp_transaction_id ;
3739
3740 CURSOR c_base_trx (cp_transaction_id IN NUMBER) IS
3741 SELECT vendor_id , vendor_site_id , transaction_id , shipment_line_id from rcv_transactions
3742 WHERE transaction_id = cp_transaction_id;
3743
3744 CURSOR c_mtl_params(cp_organization_id IN NUMBER) IS
3745 SELECT * from mtl_parameters
3746 WHERE organization_id = cp_organization_id;
3747
3748 CURSOR c_cenvat_accts (cp_organization_id IN NUMBER , cp_location_id IN NUMBER) IS
3749 select *
3750 FROM JAI_CMN_INVENTORY_ORGS
3751 WHERE organization_id = cp_organization_id
3752 AND location_id = cp_location_id ;
3753
3754 CURSOR c_gl_periods(cp_set_of_books_id IN NUMBER) IS
3755 SELECT period_name
3756 FROM gl_period_statuses
3757 where set_of_books_id = cp_set_of_books_id
3758 and sysdate between start_Date and end_Date
3759 and application_id = 101;
3760
3761
3762 lv_period_name gl_period_statuses.period_name%type;
3763 ln_shipment_line_id NUMBER;
3764 ln_register_id NUMBER;
3765 lv_process_status VARCHAR2(20);
3766 lv_process_message VARCHAR2(2000);
3767 ln_Excise_amt NUMBER;
3768 ln_addl_excise_amt NUMBER;
3769 ln_oth_excise_amt NUMBER;
3770 ln_excise_cess_amt NUMBER;
3771 ln_excise_sh_cess_amt NUMBER; --10607819
3772 ln_shCess_amt NUMBER; --10607819
3773 ln_cvd_sh_cess_amt NUMBER; --10607819
3774 ln_cvd_cess_amt NUMBER;
3775 ln_Cess_amt NUMBER;
3776 ln_add_cvd_amt NUMBER;
3777 ln_addl_cvd_amt NUMBER;
3778 r_base_trx c_base_trx%rowtype;
3779 r_trx c_trx%rowtype;
3780 ln_Factor NUMBER := 0.5;
3781 ln_applied_qty NUMBER;
3782 r_rcpt_Dtls c_rcpt_dtls%ROWTYPE;
3783 lv_reference_num VARCHAR2(100);
3784 r_mtl_params c_mtl_params%ROWTYPE;
3785 r_cenvat_accts c_cenvat_accts%ROWTYPE;
3786 ln_tot_cenvat_amt NUMBER;
3787 ln_set_of_books_id NUMBER;
3788
3789 lv_reference_10 VARCHAR2(240);
3790 ln_created_by NUMBER := fnd_global.user_id;
3791
3792
3793 BEGIN
3794
3795
3796 ln_shipment_line_id := p_shipment_line_id;
3797 ln_applied_qty := p_applied_quantity;
3798
3799 fnd_profile.get('GL_SET_OF_BKS_ID',ln_set_of_books_id);
3800
3801 OPEN c_rcpt_dtls(ln_shipment_line_id);
3802 FETCH c_rcpt_dtls INTO r_rcpt_Dtls;
3803 CLOSE c_rcpt_dtls;
3804
3805 OPEN c_trx(r_rcpt_Dtls.transaction_id);
3806 FETCH c_trx INTO r_Trx;
3807 CLOSE c_trx;
3808
3809 OPEN c_base_trx(r_rcpt_Dtls.transaction_id);
3810 FETCH c_base_Trx INTO r_base_trx;
3811 CLOSE c_base_trx;
3812
3813 OPEN c_mtl_params(r_Trx.organization_id);
3814 FETCH c_mtl_params INTO r_mtl_params;
3815 CLOSE c_mtl_params;
3816
3817 OPEN c_cenvat_accts(r_trx.organization_id , r_trx.location_id);
3818 FETCH c_cenvat_accts INTO r_cenvat_accts;
3819 CLOSE c_cenvat_accts;
3820
3821 OPEN c_gl_periods(ln_set_of_books_id);
3822 FETCH c_gl_periods INTO lv_period_name;
3823 CLOSE c_gl_periods;
3824
3825 ln_factor := ln_factor * ( ln_applied_qty/ r_Trx.quantity);
3826
3827
3828 FOR r_rcpt_Tax_dtls IN c_recpt_tax_dtls(ln_shipment_line_id)
3829 LOOP
3830
3831 IF NVL(r_rcpt_Tax_dtls.modvat_flag,'N') = 'Y' THEN
3832
3833 if UPPER(r_rcpt_Tax_dtls.tax_type) = 'EXCISE' THEN
3834 ln_Excise_amt := NVL(ln_Excise_amt,0) + NVL(r_rcpt_Tax_dtls.tax_amount,0);
3835 ELSIF UPPER(r_rcpt_Tax_dtls.tax_type) IN ('ADDL. EXCISE' ,'CVD') THEN
3836 ln_addl_excise_amt := NVL(ln_addl_excise_amt,0) + NVL(r_rcpt_Tax_dtls.tax_amount,0);
3837 ELSIF UPPER(r_rcpt_Tax_dtls.tax_type) IN ('ADDITIONAL_CVD') THEN
3838 ln_addl_cvd_amt := NVL(ln_addl_cvd_amt,0) + NVL(r_rcpt_Tax_dtls.tax_amount,0);
3839 ELSIF UPPER(r_rcpt_Tax_dtls.tax_type) = 'OTHER EXCISE' THEN
3840 ln_oth_excise_amt := NVL(ln_oth_excise_amt,0) + NVL(r_rcpt_Tax_dtls.tax_amount,0);
3841 --START additions for bug#10607819
3842 ELSIF UPPER(r_rcpt_Tax_dtls.tax_type) = 'EXCISE_SH_EDU_CESS' THEN
3843 ln_excise_sh_cess_amt := NVL(ln_excise_sh_cess_amt,0) + NVL(r_rcpt_Tax_dtls.tax_amount,0);
3844 ELSIF UPPER(r_rcpt_Tax_dtls.tax_type) = 'CVD_SH_EDU_CESS' THEN
3845 ln_cvd_sh_cess_amt := NVL(ln_cvd_sh_cess_amt,0) + NVL(r_rcpt_Tax_dtls.tax_amount,0);
3846 --end additions for bug#10607819
3847
3848 ELSIF UPPER(r_rcpt_Tax_dtls.tax_type) = 'EXCISE_EDUCATION_CESS' THEN
3849 ln_excise_cess_amt := NVL(ln_excise_cess_amt,0) + NVL(r_rcpt_Tax_dtls.tax_amount,0);
3850 ELSIF UPPER(r_rcpt_Tax_dtls.tax_type) = 'CVD_EDUCATION_CESS' THEN
3851 ln_cvd_cess_amt := NVL(ln_cvd_cess_amt,0) + NVL(r_rcpt_Tax_dtls.tax_amount,0);
3852 END IF;
3853
3854 END IF;
3855
3856 END LOOP;
3857
3858 ln_tot_cenvat_amt := NVL(ln_Excise_amt,0) + NVL(ln_addl_excise_amt,0) + NVL(ln_oth_excise_amt,0) ;
3859
3860 ln_Cess_amt := NVL(ln_cvd_cess_amt,0) + NVL(ln_excise_cess_amt,0);
3861
3862 ln_shCess_amt := NVL(ln_cvd_sh_cess_amt,0) + NVL(ln_excise_sh_cess_amt,0);--10607819
3863 lv_reference_num := r_Trx.receipt_num || p_transaction_Temp_id;
3864
3865 ln_Excise_amt := ln_Excise_amt * ln_factor;
3866 ln_addl_excise_amt := ln_addl_excise_amt * ln_factor;
3867 ln_add_cvd_amt := ln_add_cvd_amt * ln_factor;
3868 ln_oth_excise_amt := ln_oth_excise_amt * ln_factor;
3869 ln_Cess_amt := ln_Cess_amt * ln_factor;
3870 ln_shCess_amt := ln_shCess_amt * ln_factor;--10607819
3871
3872 ln_tot_cenvat_amt := NVL(ln_Excise_amt,0) + NVL(ln_addl_excise_amt,0) + NVL(ln_oth_excise_amt,0) ;
3873
3874 --ln_Cess_amt := NVL(ln_cvd_cess_amt,0) + NVL(ln_excise_cess_amt,0);
3875
3876
3877 jai_cmn_rg_23ac_ii_pkg.insert_row
3878 (
3879 P_REGISTER_ID => ln_register_id ,
3880 P_INVENTORY_ITEM_ID => r_trx.inventory_item_id,
3881 P_ORGANIZATION_ID => r_trx.organization_id,
3882 P_RECEIPT_ID => r_Trx.transaction_id,
3883 P_RECEIPT_DATE => r_Trx.transaction_date,
3884 P_CR_BASIC_ED => ln_Excise_amt,
3885 P_CR_ADDITIONAL_ED => ln_addl_excise_amt,
3886 P_CR_ADDITIONAL_CVD => ln_add_cvd_amt,
3887 P_CR_OTHER_ED => ln_oth_excise_amt,
3888 P_DR_BASIC_ED => Null,
3889 P_DR_ADDITIONAL_ED => null,
3890 P_DR_ADDITIONAL_CVD => null,
3891 P_DR_OTHER_ED => null,
3892 P_EXCISE_INVOICE_NO => r_Trx.excise_invoice_no,
3893 P_EXCISE_INVOICE_DATE => r_trx.excise_invoice_date,
3894 P_REGISTER_TYPE => 'C',
3895 P_REMARKS => 'AutoClaim of remaining 50% for CG in interorg XFER',
3896 P_VENDOR_ID => r_base_Trx.vendor_id,
3897 P_VENDOR_SITE_ID => r_base_trx.vendor_site_id,
3898 P_CUSTOMER_ID => null,
3899 P_CUSTOMER_SITE_ID => null,
3900 P_LOCATION_ID => r_trx.location_id,
3901 P_TRANSACTION_DATE => trunc(sysdate),
3902 P_CHARGE_ACCOUNT_ID => null,
3903 P_REGISTER_ID_PART_I => null,
3904 P_REFERENCE_NUM => lv_reference_num ,
3905 P_ROUNDING_ID => null,
3906 P_OTHER_TAX_CREDIT => ln_Cess_amt+ ln_shCess_amt, --added + ln_shCess_amt for bug#10607819
3907 P_OTHER_TAX_DEBIT => null,
3908 P_TRANSACTION_TYPE => 'R',
3909 P_TRANSACTION_SOURCE => 'INTERORG_XFER',
3910 P_CALLED_FROM => 'INTERORG_XFER',
3911 P_SIMULATE_FLAG => 'N',
3912 P_PROCESS_STATUS => lv_process_status,
3913 P_PROCESS_MESSAGE => lv_process_message
3914 );
3915
3916 lv_Reference_10 := 'India Local Receiving Entry for the Receipt Number' || r_trx.receipt_num || 'for the Trx Type RECEIVE for the Organization code' || r_mtl_params.organization_code ;
3917 --cr cenvat receivable accnt ,10607819
3918 insert into JAI_RCV_JOURNAL_ENTRIES
3919 (
3920 JOURNAL_ENTRY_ID,
3921 ORGANIZATION_CODE,
3922 RECEIPT_NUM,
3923 TRANSACTION_ID ,
3924 CREATION_DATE ,
3925 TRANSACTION_DATE ,
3926 SHIPMENT_LINE_ID ,
3927 ACCT_TYPE ,
3928 ACCT_NATURE ,
3929 SOURCE_NAME ,
3930 CATEGORY_NAME ,
3931 CODE_COMBINATION_ID ,
3932 ENTERED_DR ,
3933 ENTERED_CR ,
3934 TRANSACTION_TYPE ,
3935 PERIOD_NAME ,
3936 CREATED_BY ,
3937 CURRENCY_CODE ,
3938 CURRENCY_CONVERSION_TYPE,
3939 CURRENCY_CONVERSION_DATE,
3940 CURRENCY_CONVERSION_RATE,
3941 REFERENCE_ID ,
3942 REFERENCE_NAME ,
3943 last_update_date ,/*6497301*/
3944 last_updated_by ,/*6497301*/
3945 last_update_login /*6497301*/
3946
3947 )
3948 VALUES
3949 (jai_rcv_journal_entries_s.nextval,/*6497301*/
3950 r_mtl_params.organization_code ,
3951 r_trx.receipt_num ,
3952 r_trx.transaction_id ,
3953 sysdate ,
3954 sysdate ,
3955 r_Trx.shipment_line_id,
3956 'REGULAR',
3957 'CENVAT-AUTOCLAIM-INTERORG-XFER',
3958 'Purchasing',
3959 'Receiving India',
3960 -- r_cenvat_accts.excise_rcvble_account, --12807591
3961 r_cenvat_accts.cenvat_rcvble_account, --12807591
3962 Null, -- 10607819
3963 ln_tot_cenvat_amt, --10607819
3964 'Receive',
3965 lv_period_name,
3966 ln_created_by,
3967 'INR',
3968 NULL,
3969 NULL,
3970 NULL,
3971 NULL,
3972 NULL,
3973 sysdate, /*6497301*/
3974 fnd_global.user_id,/*6497301*/
3975 fnd_global.login_id/*6497301*/
3976 );
3977 --cr excise receivable accnt
3978 INSERT INTO GL_INTERFACE
3979 (
3980 status,
3981 set_of_books_id,
3982 user_je_source_name,
3983 user_je_category_name,
3984 accounting_date,
3985 currency_code,
3986 date_created,
3987 created_by,
3988 actual_flag,
3989 entered_cr,
3990 entered_dr,
3991 transaction_date,
3992 code_combination_id,
3993 currency_conversion_date,
3994 user_currency_conversion_type,
3995 currency_conversion_rate,
3996 reference1,
3997 reference10,
3998 reference22 ,
3999 reference23,
4000 reference24,
4001 reference25,
4002 reference26,
4003 reference27
4004 )
4005 VALUES
4006 (
4007 'NEW',
4008 ln_set_of_books_id,
4009 'Purchasing',
4010 'Receiving India',
4011 trunc(sysdate),
4012 'INR',
4013 sysdate,
4014 ln_created_by,
4015 'A',
4016 ln_tot_cenvat_amt,
4017 NULL,
4018 sysdate,
4019 -- r_cenvat_accts.excise_rcvble_account, --12807591
4020 r_cenvat_accts.cenvat_rcvble_account, --12807591
4021 Null,
4022 NULL,
4023 NULL,
4024 r_mtl_params.organization_code,
4025 lv_reference_10,
4026 'India Localization Entry',
4027 'jai_mtl_trxs_pkg.auto_claim',
4028 'rcv_transactions',
4029 'rcv_Transaction_id',
4030 r_Trx.transaction_id,
4031 r_trx.organization_id
4032 );
4033
4034 --dr modvat cg accnt 10607819
4035 INSERT INTO JAI_RCV_JOURNAL_ENTRIES
4036 (
4037 JOURNAL_ENTRY_ID ,
4038 ORGANIZATION_CODE ,
4039 RECEIPT_NUM ,
4040 TRANSACTION_ID ,
4041 CREATION_DATE ,
4042 TRANSACTION_DATE ,
4043 SHIPMENT_LINE_ID ,
4044 ACCT_TYPE ,
4045 ACCT_NATURE ,
4046 SOURCE_NAME ,
4047 CATEGORY_NAME ,
4048 CODE_COMBINATION_ID ,
4049 ENTERED_DR ,
4050 ENTERED_CR ,
4051 TRANSACTION_TYPE ,
4052 PERIOD_NAME ,
4053 CREATED_BY ,
4054 CURRENCY_CODE ,
4055 CURRENCY_CONVERSION_TYPE,
4056 CURRENCY_CONVERSION_DATE,
4057 CURRENCY_CONVERSION_RATE,
4058 REFERENCE_ID ,
4059 REFERENCE_NAME ,
4060 last_update_date ,/*6497301*/
4061 last_updated_by ,/*6497301*/
4062 last_update_login /*6497301*/
4063 )
4064 VALUES
4065 (jai_rcv_journal_entries_s.nextval,
4066 r_mtl_params.organization_code ,
4067 r_trx.receipt_num ,
4068 r_trx.transaction_id ,
4069 sysdate ,
4070 sysdate ,
4071 r_Trx.shipment_line_id,
4072 'REGULAR',
4073 'CENVAT-AUTOCLAIM-INTERORG-XFER',
4074 'Purchasing',
4075 'Receiving India',
4076 r_cenvat_accts.modvat_cg_account_id ,
4077 ln_tot_cenvat_amt,-- 10607819
4078 NULL, -- 10607819
4079 'Receive',
4080 lv_period_name,
4081 ln_created_by,
4082 'INR',
4083 NULL,
4084 NULL,
4085 NULL,
4086 NULL,
4087 NULL,
4088 sysdate, /*6497301*/
4089 fnd_global.user_id,/*6497301*/
4090 fnd_global.login_id/*6497301*/
4091 );
4092 --dr modvat cg acount
4093 INSERT INTO GL_INTERFACE
4094 (
4095 status,
4096 set_of_books_id,
4097 user_je_source_name,
4098 user_je_category_name,
4099 accounting_date,
4100 currency_code,
4101 date_created,
4102 created_by,
4103 actual_flag,
4104 entered_cr,
4105 entered_dr,
4106 transaction_date,
4107 code_combination_id,
4108 currency_conversion_date,
4109 user_currency_conversion_type,
4110 currency_conversion_rate,
4111 reference1,
4112 reference10,
4113 reference22 ,
4114 reference23,
4115 reference24,
4116 reference25,
4117 reference26,
4118 reference27
4119 )
4120 VALUES
4121 (
4122 'NEW',
4123 ln_set_of_books_id,
4124 'Purchasing',
4125 'Receiving India',
4126 trunc(sysdate),
4127 'INR',
4128 sysdate,
4129 ln_created_by,
4130 'A',
4131 NULL,
4132 ln_tot_cenvat_amt,
4133 sysdate,
4134 r_cenvat_accts.modvat_cg_account_id,
4135 Null,
4136 NULL,
4137 NULL,
4138 r_mtl_params.organization_code,
4139 lv_reference_10,
4140 'India Localization Entry',
4141 'jai_mtl_trxs_pkg.auto_claim',
4142 'rcv_transactions',
4143 'rcv_Transaction_id',
4144 r_Trx.transaction_id,
4145 r_trx.organization_id
4146 );
4147
4148
4149
4150 IF ln_cvd_cess_amt IS NOT NULL THEN
4151
4152 jai_cmn_rg_others_pkg.insert_row
4153 (
4154 P_SOURCE_TYPE => 1,
4155 P_SOURCE_NAME => 'RG23C_P2',
4156 P_SOURCE_ID => ln_register_id,
4157 P_TAX_TYPE => 'CVD_EDUCATION_CESS',
4158 DEBIT_AMT => null,
4159 CREDIT_AMT => ln_cvd_cess_amt * ln_factor,
4160 P_PROCESS_FLAG => lv_process_status,
4161 P_PROCESS_MSG => lv_process_message
4162 );
4163
4164 END IF;
4165
4166 IF ln_excise_cess_amt IS NOT NULL THEN
4167 jai_cmn_rg_others_pkg.insert_row
4168 (
4169 P_SOURCE_TYPE => 1,
4170 P_SOURCE_NAME => 'RG23C_P2',
4171 P_SOURCE_ID => ln_register_id,
4172 P_TAX_TYPE => 'EXCISE_EDUCATION_CESS',
4173 DEBIT_AMT => null,
4174 CREDIT_AMT => ln_excise_cess_amt * ln_factor,
4175 P_PROCESS_FLAG => lv_process_status,
4176 P_PROCESS_MSG => lv_process_message
4177 );
4178 END IF;
4179 --start additions for bug#10607819
4180 IF ln_cvd_sh_cess_amt IS NOT NULL THEN
4181
4182 jai_cmn_rg_others_pkg.insert_row
4183 (
4184 P_SOURCE_TYPE => 1,
4185 P_SOURCE_NAME => 'RG23C_P2',
4186 P_SOURCE_ID => ln_register_id,
4187 P_TAX_TYPE => 'CVD_SH_EDU_CESS',
4188 DEBIT_AMT => null,
4189 CREDIT_AMT => ln_cvd_sh_cess_amt * ln_factor,
4190 P_PROCESS_FLAG => lv_process_status,
4191 P_PROCESS_MSG => lv_process_message
4192 );
4193
4194 END IF;
4195
4196 IF ln_excise_sh_cess_amt IS NOT NULL THEN
4197 jai_cmn_rg_others_pkg.insert_row
4198 (
4199 P_SOURCE_TYPE => 1,
4200 P_SOURCE_NAME => 'RG23C_P2',
4201 P_SOURCE_ID => ln_register_id,
4202 P_TAX_TYPE => 'EXCISE_SH_EDU_CESS',
4203 DEBIT_AMT => null,
4204 CREDIT_AMT => ln_excise_sh_cess_amt * ln_factor,
4205 P_PROCESS_FLAG => lv_process_status,
4206 P_PROCESS_MSG => lv_process_message
4207 );
4208 END IF;
4209 --end additions for bug#10607819
4210
4211 IF ln_Cess_amt IS NOT NULL THEN
4212 --cr edu cess receivable accnt
4213 INSERT INTO JAI_RCV_JOURNAL_ENTRIES
4214 (
4215 JOURNAL_ENTRY_ID,
4216 ORGANIZATION_CODE ,
4217 RECEIPT_NUM ,
4218 TRANSACTION_ID ,
4219 CREATION_DATE ,
4220 TRANSACTION_DATE ,
4221 SHIPMENT_LINE_ID ,
4222 ACCT_TYPE ,
4223 ACCT_NATURE ,
4224 SOURCE_NAME ,
4225 CATEGORY_NAME ,
4226 CODE_COMBINATION_ID ,
4227 ENTERED_DR ,
4228 ENTERED_CR ,
4229 TRANSACTION_TYPE ,
4230 PERIOD_NAME ,
4231 CREATED_BY ,
4232 CURRENCY_CODE ,
4233 CURRENCY_CONVERSION_TYPE ,
4234 CURRENCY_CONVERSION_DATE ,
4235 CURRENCY_CONVERSION_RATE ,
4236 REFERENCE_ID ,
4237 REFERENCE_NAME ,
4238 last_update_date ,/*6497301*/
4239 last_updated_by ,/*6497301*/
4240 last_update_login /*6497301*/
4241 )
4242 VALUES
4243 (jai_rcv_journal_entries_s.nextval,/*6497301*/
4244 r_mtl_params.organization_code ,
4245 r_trx.receipt_num ,
4246 r_trx.transaction_id ,
4247 sysdate ,
4248 sysdate ,
4249 r_Trx.shipment_line_id,
4250 'REGULAR',
4251 'CENVAT-AUTOCLAIM-INTERORG-XFER',
4252 'Purchasing',
4253 'Receiving India',
4254 r_cenvat_accts.excise_edu_cess_rcvble_accnt,
4255 Null,--10607819
4256 ln_Cess_amt,--10607819
4257 'Receive',
4258 lv_period_name,
4259 ln_created_by,
4260 'INR',
4261 NULL,
4262 NULL,
4263 NULL,
4264 NULL,
4265 NULL,
4266 sysdate, /*6497301*/
4267 fnd_global.user_id,/*6497301*/
4268 fnd_global.login_id/*6497301*/
4269 );
4270 --cr excise cess recvable accnt --10607819
4271 INSERT INTO GL_INTERFACE
4272 (
4273 status,
4274 set_of_books_id,
4275 user_je_source_name,
4276 user_je_category_name,
4277 accounting_date,
4278 currency_code,
4279 date_created,
4280 created_by,
4281 actual_flag,
4282 entered_cr,
4283 entered_dr,
4284 transaction_date,
4285 code_combination_id,
4286 currency_conversion_date,
4287 user_currency_conversion_type,
4288 currency_conversion_rate,
4289 reference1,
4290 reference10,
4291 reference22 ,
4292 reference23,
4293 reference24,
4294 reference25,
4295 reference26,
4296 reference27
4297 )
4298 VALUES
4299 (
4300 'NEW',
4301 ln_set_of_books_id,
4302 'Purchasing',
4303 'Receiving India',
4304 trunc(sysdate),
4305 'INR',
4306 sysdate,
4307 ln_created_by,
4308 'A',
4309 ln_cess_amt,--10607819
4310 NULL,--10607819
4311 sysdate,
4312 r_cenvat_accts.excise_edu_cess_rcvble_accnt,
4313 Null,
4314 NULL,
4315 NULL,
4316 r_mtl_params.organization_code,
4317 lv_reference_10,
4318 'India Localization Entry',
4319 'jai_mtl_trxs_pkg.auto_claim',
4320 'rcv_transactions',
4321 'rcv_Transaction_id',
4322 r_Trx.transaction_id,
4323 r_trx.organization_id
4324 );
4325
4326 --dr edu cess CG account 10607819
4327 INSERT INTO JAI_RCV_JOURNAL_ENTRIES
4328 (
4329 JOURNAL_ENTRY_ID,
4330 ORGANIZATION_CODE ,
4331 RECEIPT_NUM ,
4332 TRANSACTION_ID ,
4333 CREATION_DATE ,
4334 TRANSACTION_DATE ,
4335 SHIPMENT_LINE_ID ,
4336 ACCT_TYPE ,
4337 ACCT_NATURE ,
4338 SOURCE_NAME ,
4339 CATEGORY_NAME ,
4340 CODE_COMBINATION_ID ,
4341 ENTERED_DR ,
4342 ENTERED_CR ,
4343 TRANSACTION_TYPE ,
4344 PERIOD_NAME ,
4345 CREATED_BY ,
4346 CURRENCY_CODE ,
4347 CURRENCY_CONVERSION_TYPE ,
4348 CURRENCY_CONVERSION_DATE ,
4349 CURRENCY_CONVERSION_RATE ,
4350 REFERENCE_ID ,
4351 REFERENCE_NAME ,
4352 last_update_date ,/*6497301*/
4353 last_updated_by ,/*6497301*/
4354 last_update_login /*6497301*/
4355 )
4356 VALUES
4357 (
4358 jai_rcv_journal_entries_s.nextval,/*6497301*/
4359 r_mtl_params.organization_code ,
4360 r_trx.receipt_num,
4361 r_trx.transaction_id ,
4362 sysdate ,
4363 sysdate ,
4364 r_Trx.shipment_line_id,
4365 'REGULAR',
4366 'CENVAT-AUTOCLAIM-INTERORG-XFER',
4367 'Purchasing',
4368 'Receiving India',
4369 r_cenvat_accts.excise_edu_cess_cg_account,
4370 ln_Cess_amt,--10607819
4371 NULL,--10607819
4372 'Receive',
4373 lv_period_name,
4374 ln_created_by,
4375 'INR',
4376 NULL,
4377 NULL,
4378 NULL,
4379 NULL,
4380 NULL,
4381 sysdate, /*6497301*/
4382 fnd_global.user_id,/*6497301*/
4383 fnd_global.login_id/*6497301*/
4384 );
4385 --dr edu cess cg account 10607819
4386 INSERT INTO GL_INTERFACE
4387 (
4388 status,
4389 set_of_books_id,
4390 user_je_source_name,
4391 user_je_category_name,
4392 accounting_date,
4393 currency_code,
4394 date_created,
4395 created_by,
4396 actual_flag,
4397 entered_cr,
4398 entered_dr,
4399 transaction_date,
4400 code_combination_id,
4401 currency_conversion_date,
4402 user_currency_conversion_type,
4403 currency_conversion_rate,
4404 reference1,
4405 reference10,
4406 reference22 ,
4407 reference23,
4408 reference24,
4409 reference25,
4410 reference26,
4411 reference27
4412 )
4413 VALUES
4414 (
4415 'NEW',
4416 ln_set_of_books_id,
4417 'Purchasing',
4418 'Receiving India',
4419 trunc(sysdate),
4420 'INR',
4421 sysdate,
4422 ln_created_by,
4423 'A',
4424 NULL,
4425 ln_cess_amt,
4426 sysdate,
4427 r_cenvat_accts.excise_edu_cess_cg_account,
4428 Null,
4429 NULL,
4430 NULL,
4431 r_mtl_params.organization_code,
4432 lv_reference_10,
4433 'India Localization Entry',
4434 'jai_mtl_trxs_pkg.auto_claim',
4435 'rcv_transactions',
4436 'rcv_Transaction_id',
4437 r_Trx.transaction_id,
4438 r_trx.organization_id
4439 );
4440 END IF;
4441 --start additions for bug#10607819
4442 IF ln_shCess_amt IS NOT NULL THEN
4443 --cr excise rcvble accnt 10607819
4444 INSERT INTO JAI_RCV_JOURNAL_ENTRIES
4445 (
4446 JOURNAL_ENTRY_ID,
4447 ORGANIZATION_CODE ,
4448 RECEIPT_NUM ,
4449 TRANSACTION_ID ,
4450 CREATION_DATE ,
4451 TRANSACTION_DATE ,
4452 SHIPMENT_LINE_ID ,
4453 ACCT_TYPE ,
4454 ACCT_NATURE ,
4455 SOURCE_NAME ,
4456 CATEGORY_NAME ,
4457 CODE_COMBINATION_ID ,
4458 ENTERED_DR ,
4459 ENTERED_CR ,
4460 TRANSACTION_TYPE ,
4461 PERIOD_NAME ,
4462 CREATED_BY ,
4463 CURRENCY_CODE ,
4464 CURRENCY_CONVERSION_TYPE ,
4465 CURRENCY_CONVERSION_DATE ,
4466 CURRENCY_CONVERSION_RATE ,
4467 REFERENCE_ID ,
4468 REFERENCE_NAME ,
4469 last_update_date ,/*6497301*/
4470 last_updated_by ,/*6497301*/
4471 last_update_login /*6497301*/
4472 )
4473 VALUES
4474 (jai_rcv_journal_entries_s.nextval,/*6497301*/
4475 r_mtl_params.organization_code ,
4476 r_trx.receipt_num ,
4477 r_trx.transaction_id ,
4478 sysdate ,
4479 sysdate ,
4480 r_Trx.shipment_line_id,
4481 'REGULAR',
4482 'CENVAT-AUTOCLAIM-INTERORG-XFER',
4483 'Purchasing',
4484 'Receiving India',
4485 r_cenvat_accts.sh_cess_rcvble_acct_id,
4486 Null,
4487 ln_shCess_amt,
4488 'Receive',
4489 lv_period_name,
4490 ln_created_by,
4491 'INR',
4492 NULL,
4493 NULL,
4494 NULL,
4495 NULL,
4496 NULL,
4497 sysdate, /*6497301*/
4498 fnd_global.user_id,/*6497301*/
4499 fnd_global.login_id/*6497301*/
4500 );
4501 --cr excise rcvble accnt 10607819
4502 INSERT INTO GL_INTERFACE
4503 (
4504 status,
4505 set_of_books_id,
4506 user_je_source_name,
4507 user_je_category_name,
4508 accounting_date,
4509 currency_code,
4510 date_created,
4511 created_by,
4512 actual_flag,
4513 entered_cr,
4514 entered_dr,
4515 transaction_date,
4516 code_combination_id,
4517 currency_conversion_date,
4518 user_currency_conversion_type,
4519 currency_conversion_rate,
4520 reference1,
4521 reference10,
4522 reference22 ,
4523 reference23,
4524 reference24,
4525 reference25,
4526 reference26,
4527 reference27
4528 )
4529 VALUES
4530 (
4531 'NEW',
4532 ln_set_of_books_id,
4533 'Purchasing',
4534 'Receiving India',
4535 trunc(sysdate),
4536 'INR',
4537 sysdate,
4538 ln_created_by,
4539 'A',
4540 ln_shCess_amt,
4541 NULL,
4542 sysdate,
4543 r_cenvat_accts.sh_cess_rcvble_acct_id,
4544 Null,
4545 NULL,
4546 NULL,
4547 r_mtl_params.organization_code,
4548 lv_reference_10,
4549 'India Localization Entry',
4550 'jai_mtl_trxs_pkg.auto_claim',
4551 'rcv_transactions',
4552 'rcv_Transaction_id',
4553 r_Trx.transaction_id,
4554 r_trx.organization_id
4555 );
4556
4557 --dr cg accnt 10607819
4558 INSERT INTO JAI_RCV_JOURNAL_ENTRIES
4559 (
4560 JOURNAL_ENTRY_ID,
4561 ORGANIZATION_CODE ,
4562 RECEIPT_NUM ,
4563 TRANSACTION_ID ,
4564 CREATION_DATE ,
4565 TRANSACTION_DATE ,
4566 SHIPMENT_LINE_ID ,
4567 ACCT_TYPE ,
4568 ACCT_NATURE ,
4569 SOURCE_NAME ,
4570 CATEGORY_NAME ,
4571 CODE_COMBINATION_ID ,
4572 ENTERED_DR ,
4573 ENTERED_CR ,
4574 TRANSACTION_TYPE ,
4575 PERIOD_NAME ,
4576 CREATED_BY ,
4577 CURRENCY_CODE ,
4578 CURRENCY_CONVERSION_TYPE ,
4579 CURRENCY_CONVERSION_DATE ,
4580 CURRENCY_CONVERSION_RATE ,
4581 REFERENCE_ID ,
4582 REFERENCE_NAME ,
4583 last_update_date ,/*6497301*/
4584 last_updated_by ,/*6497301*/
4585 last_update_login /*6497301*/
4586 )
4587 VALUES
4588 (
4589 jai_rcv_journal_entries_s.nextval,/*6497301*/
4590 r_mtl_params.organization_code ,
4591 r_trx.receipt_num,
4592 r_trx.transaction_id ,
4593 sysdate ,
4594 sysdate ,
4595 r_Trx.shipment_line_id,
4596 'REGULAR',
4597 'CENVAT-AUTOCLAIM-INTERORG-XFER',
4598 'Purchasing',
4599 'Receiving India',
4600 r_cenvat_accts.sh_cess_cg_account_id,
4601 ln_shCess_amt,
4602 NULL,
4603 'Receive',
4604 lv_period_name,
4605 ln_created_by,
4606 'INR',
4607 NULL,
4608 NULL,
4609 NULL,
4610 NULL,
4611 NULL,
4612 sysdate, /*6497301*/
4613 fnd_global.user_id,/*6497301*/
4614 fnd_global.login_id/*6497301*/
4615 );
4616 --dr cg accnt 10607819
4617 INSERT INTO GL_INTERFACE
4618 (
4619 status,
4620 set_of_books_id,
4621 user_je_source_name,
4622 user_je_category_name,
4623 accounting_date,
4624 currency_code,
4625 date_created,
4626 created_by,
4627 actual_flag,
4628 entered_cr,
4629 entered_dr,
4630 transaction_date,
4631 code_combination_id,
4632 currency_conversion_date,
4633 user_currency_conversion_type,
4634 currency_conversion_rate,
4635 reference1,
4636 reference10,
4637 reference22 ,
4638 reference23,
4639 reference24,
4640 reference25,
4641 reference26,
4642 reference27
4643 )
4644 VALUES
4645 (
4646 'NEW',
4647 ln_set_of_books_id,
4648 'Purchasing',
4649 'Receiving India',
4650 trunc(sysdate),
4651 'INR',
4652 sysdate,
4653 ln_created_by,
4654 'A',
4655 NULL,
4656 ln_shCess_amt,
4657 sysdate,
4658 r_cenvat_accts.sh_cess_cg_account_id,
4659 Null,
4660 NULL,
4661 NULL,
4662 r_mtl_params.organization_code,
4663 lv_reference_10,
4664 'India Localization Entry',
4665 'jai_mtl_trxs_pkg.auto_claim',
4666 'rcv_transactions',
4667 'rcv_Transaction_id',
4668 r_Trx.transaction_id,
4669 r_trx.organization_id
4670 );
4671 END IF;
4672 --end additions for bug#10607819
4673 END;
4674 PROCEDURE CGVAT_REPOSIT_ENTRY(p_organization_id IN NUMBER,
4675 p_location_id IN NUMBER,
4676 p_Set_of_books_id IN number,
4677 p_currency in varchar2,
4678 p_transaction_header_id IN NUMBER,
4679 p_transaction_temp_id IN NUMBER,
4680 p_transaction_id IN NUMBER,
4681 p_vat_invoice_no IN VARCHAR2,
4682 p_tax_type in varchar2,
4683 p_amount in number,
4684 p_claim_schedule_id in number,
4685 p_process_status OUT NOCOPY VARCHAR2,
4686 p_process_message OUT NOCOPY VARCHAR2)
4687 IS
4688 ln_regime_id NUMBER;
4689 lv_inv_gen_process_flag VARCHAR2(10);
4690 lv_inv_gen_process_message VARCHAR2(2000);
4691 ln_repository_id NUMBER;
4692 lv_source_trx_type VARCHAR2(30):='RECEIVING';
4693 table_rcv_transactions VARCHAR2(30):= 'JAI_MTL_TRXS';
4694 lv_account_name VARCHAR2(50);
4695 ln_code_combination_id NUMBER;
4696 ln_interim_recovery_account NUMBER;
4697 ln_entered_dr NUMBER;
4698 ln_entered_cr NUMBER;
4699 lv_process_status VARCHAR2(2);
4700 lv_process_message VARCHAR2(1000);
4701 CURSOR c_regime_cur IS
4702 SELECT regime_id
4703 FROM jai_rgm_definitions
4704 WHERE regime_code = 'VAT';
4705 stmt_name VARCHAR2(64);
4706 BEGIN
4707 OPEN c_regime_cur;
4708 FETCH c_regime_cur into ln_regime_id;
4709 CLOSE c_regime_cur;
4710 lv_account_name := jai_constants.recovery;
4711 stmt_name:='Getting the interim recovery amount';
4712 ln_interim_recovery_account :=
4713 jai_cmn_rgm_recording_pkg.get_account(
4714 p_regime_id => ln_regime_id,
4715 p_organization_type => jai_constants.orgn_type_io,
4716 p_organization_id => p_organization_id,
4717 p_location_id => p_location_id,
4718 p_tax_type => p_tax_type,
4719 p_account_name => jai_constants.recovery_interim);
4720 IF ln_interim_recovery_account IS NULL THEN
4721 p_process_status := jai_constants.expected_error;
4722 p_process_message := 'Interim recovery Account not defined in VAT Setup';
4723 RETURN;
4724 END IF;
4725 stmt_name:='Getting the code combination id';
4726 ln_code_combination_id :=
4727 jai_cmn_rgm_recording_pkg.get_account(
4728 p_regime_id => ln_regime_id,
4729 p_organization_type => jai_constants.orgn_type_io,
4730 p_organization_id => p_organization_id,
4731 p_location_id => p_location_id,
4732 p_tax_type => p_tax_type,
4733 p_account_name => jai_constants.recovery);
4734 IF ln_code_combination_id IS NULL THEN
4735 p_process_status := jai_constants.expected_error;
4736 p_process_message := 'Recovery Account not defined in VAT Setup';
4737 RETURN;
4738 END IF;
4739 ln_entered_cr:=p_amount;
4740 stmt_name:='Calling insert vat repository entry';
4741 jai_cmn_rgm_recording_pkg.insert_vat_repository_entry(
4742 pn_repository_id => ln_repository_id,
4743 pn_regime_id => ln_regime_id,
4744 pv_tax_type => p_tax_type,
4745 pv_organization_type => jai_constants.orgn_type_io,
4746 pn_organization_id => p_organization_id,
4747 pn_location_id => p_location_id,
4748 pv_source => jai_constants.source_rcv,
4749 pv_source_trx_type => lv_source_trx_type,
4750 pv_source_table_name => table_rcv_transactions,
4751 pn_source_id => p_transaction_id,
4752 pd_transaction_date => trunc(sysdate),
4753 pv_account_name => lv_account_name,
4754 pn_charge_account_id => ln_code_combination_id,
4755 pn_balancing_account_id => ln_interim_recovery_account,
4756 pn_credit_amount =>ln_entered_cr ,
4757 pn_debit_amount =>ln_entered_dr ,
4758 pn_assessable_value => NULL,
4759 pn_tax_rate => NULL,
4760 pn_reference_id => p_claim_schedule_id,/*r_claim_schedule.claim_schedule_id,*/
4761 pn_batch_id => NULL,
4762 pn_inv_organization_id => P_organization_id,
4763 pv_invoice_no => p_vat_invoice_no,
4764 pd_invoice_date => trunc(sysdate),
4765 pv_called_from => 'JAINVMTX',
4766 pv_process_flag => lv_process_status,
4767 pv_process_message => lv_process_message,
4768 --Added by Bo Li for bug9305067 2010-4-14 BEGIN
4769 --------------------------------------------------
4770 pv_trx_reference_context => NULL,
4771 pv_trx_reference1 => NULL,
4772 pv_trx_reference2 => NULL,
4773 pv_trx_reference3 => NULL,
4774 pv_trx_reference4 => NULL,
4775 pv_trx_reference5 => NULL
4776 --------------------------------------------------
4777 --Added by Bo Li for bug9305067 2010-4-14 END
4778 );
4779 IF lv_process_status <> jai_constants.successful THEN
4780 p_process_status := lv_process_status;
4781 p_process_message := lv_process_message;
4782 RETURN;
4783 END IF;
4784
4785 process_vat_claim_acctg(
4786 ln_repository_id ,
4787 lv_procesS_status,
4788 lv_process_message);
4789 if lv_process_status<> jai_constants.successful THEN
4790 app_exception.raise_exception;
4791 end if;
4792
4793
4794 exception when others then
4795 p_process_status := lv_process_status;
4796 p_process_message := lv_process_message;
4797 end cgvat_reposit_entry;
4798 PROCEDURE claim_balance_cgvat(
4799 p_term_id IN jai_rgm_terms.term_id%TYPE DEFAULT NULL,
4800 p_shipment_header_id IN rcv_shipment_headers.shipment_header_id%TYPE DEFAULT NULL,
4801 p_shipment_line_id IN rcv_shipment_lines.shipment_line_id%TYPE DEFAULT NULL,
4802 p_transaction_id IN rcv_transactions.transaction_id%TYPE DEFAULT NULL,
4803 p_tax_type IN jai_cmn_taxes_all.tax_type%TYPE DEFAULT NULL,
4804 p_tax_id IN jai_cmn_taxes_all.tax_id%TYPE DEFAULT NULL,
4805 p_receipt_num IN VARCHAR2,
4806 P_applied_qty IN NUMBER,
4807 p_organization_id IN NUMBER,
4808 p_inventory_item_id IN NUMBER,
4809 p_location_id IN NUMBER,
4810 p_Set_of_books_id IN NUMBER,
4811 p_currency IN VARCHAR2,
4812 p_transaction_header_id IN NUMBER,
4813 p_transaction_temp_id IN NUMBER,
4814 p_vat_invoice_no IN VARCHAR2,
4815 p_process_status OUT NOCOPY VARCHAR2,
4816 p_process_message OUT NOCOPY VARCHAR2)
4817 IS
4818
4819 CURSOR cur_lines(cp_shipment_header_id IN rcv_shipment_headers.shipment_header_id%TYPE,
4820 cp_shipment_line_id IN rcv_shipment_lines.shipment_line_id%TYPE)
4821 IS
4822 SELECT shipment_header_id, shipment_line_id
4823 FROM jai_rcv_lines
4824 WHERE shipment_header_id = NVL(cp_shipment_header_id, shipment_header_id)
4825 AND shipment_line_id = NVL(cp_shipment_line_id, shipment_line_id)
4826 AND receipt_num=p_receipt_num
4827 and organization_id=p_organization_id
4828 and inventory_item_id=p_inventory_item_id
4829 ORDER BY shipment_line_id;
4830
4831
4832 CURSOR cur_txns(cp_shipment_line_id IN rcv_shipment_lines.shipment_line_id%TYPE,
4833 cp_transaction_id IN rcv_transactions.transaction_id%TYPE)
4834 IS
4835 SELECT transaction_id,
4836 transaction_type,
4837 transaction_date,
4838 tax_transaction_id,
4839 parent_transaction_type,
4840 currency_conversion_rate,
4841 quantity
4842 FROM JAI_RCV_TRANSACTIONS
4843 WHERE shipment_line_id = NVL(cp_shipment_line_id, shipment_line_id)
4844 AND transaction_id = NVL(cp_transaction_id, transaction_id)
4845 and organization_id=p_organization_id
4846 and inventory_item_id=p_inventory_item_id
4847 AND (
4848 transaction_type IN ('RECEIVE', 'RETURN TO VENDOR')
4849 OR
4850 ( transaction_type = 'CORRECT'
4851 AND parent_transaction_type IN ('RECEIVE', 'RETURN TO VENDOR')
4852 )
4853 )
4854 ORDER BY transaction_id;
4855
4856
4857
4858 CURSOR cur_tax(cp_shipment_line_id IN rcv_transactions.shipment_line_id%TYPE,
4859 cp_currency_conversion_rate IN JAI_RCV_TRANSACTIONS.currency_conversion_rate%TYPE)
4860 IS
4861 SELECT DECODE(a.currency, jai_constants.func_curr, a.tax_amount, a.tax_amount*cp_currency_conversion_rate) tax_amount, --Removed Round condition by Bgowrava for Bug#8414075
4862 a.tax_type,
4863 a.tax_id,
4864 NVL(b.rounding_factor,0) rounding_factor,
4865 c.qty_received
4866 FROM JAI_RCV_LINE_TAXES a,
4867 jai_cmn_taxes_all b,
4868 jai_rcv_lines c
4869 WHERE a.shipment_line_id =c.shipment_line_id
4870 AND a.shipment_line_id = cp_shipment_line_id
4871 AND a.tax_type IN ( select tax_type
4872 from jai_regime_tax_types_v
4873 where regime_code = jai_constants.vat_regime
4874 )
4875 AND a.tax_id = b.tax_id
4876 AND a.modvat_flag = 'Y'
4877 AND NVL(a.tax_amount,0) <> 0;
4878
4879 CURSOR cur_term(cp_shipment_line_id IN rcv_shipment_lines.shipment_line_id%TYPE)
4880 IS
4881 SELECT term_id, rcv_rgm_line_id, receipt_date
4882 FROM jai_rcv_rgm_lines
4883 WHERE shipment_line_id = cp_shipment_line_id
4884 AND receipt_num=p_receipt_num
4885 and organization_id=p_organization_id
4886 and inventory_item_id=p_inventory_item_id
4887 AND regime_code=jai_constants.vat_regime;
4888
4889 CURSOR cur_sum_schedules(cp_schedule_id IN NUMBER)
4890 IS
4891 SELECT SUM(installment_amount) total_installment_amount, MAX(installment_no) max_installment_no
4892 FROM jai_rgm_trm_schedules_t
4893 WHERE schedule_id = cp_schedule_id;
4894
4895
4896 CURSOR cur_installment_count( cp_rcv_rgm_line_id IN NUMBER,
4897 cp_transaction_id IN NUMBER,
4898 cp_tax_id IN NUMBER,
4899 cp_schedule_id IN NUMBER)
4900 IS
4901 SELECT COUNT(*) count
4902 FROM jai_rcv_rgm_claims
4903 WHERE rcv_rgm_line_id = cp_rcv_rgm_line_id
4904 AND transaction_id = cp_transaction_id
4905 AND tax_id = cp_tax_id
4906 AND installment_no IN ( SELECT installment_no
4907 FROM jai_rgm_trm_schedules_t
4908 WHERE schedule_id = cp_schedule_id);
4909 CURSOR cur_installpaid_count( cp_rcv_rgm_line_id IN NUMBER,
4910 cp_transaction_id IN NUMBER,
4911 cp_tax_id IN NUMBER)
4912 IS
4913 SELECT COUNT(*) count
4914 FROM jai_rcv_rgm_claims
4915 WHERE rcv_rgm_line_id = cp_rcv_rgm_line_id
4916 AND transaction_id = cp_transaction_id
4917 AND tax_id = cp_tax_id
4918 AND installment_amount <> 0
4919 and status='Y';
4920
4921
4922
4923 cursor cur_get_schedule(cp_schedule_id in number)
4924 is
4925 select * from jai_rgm_trm_schedules_t
4926 where schedule_id=cp_schedule_id
4927 order by installment_no;
4928
4929 r_term cur_term%ROWTYPE;
4930 ln_schedule_id NUMBER;
4931 lv_process_flag VARCHAR2(2);
4932 lv_process_msg VARCHAR2(1000);
4933 ln_amount NUMBER;
4934 r_sum_schedules cur_sum_schedules%ROWTYPE;
4935 r_installment_count cur_installment_count%ROWTYPE;
4936 ln_apportion_factor NUMBER;
4937 ln_claim_schedule_id NUMBER;
4938 ln_instpaid_cnt NUMBER;
4939 ln_debit_amt NUMBER;
4940 LN_CNT NUMBER;
4941
4942
4943 BEGIN
4944
4945 p_process_status := jai_constants.successful;
4946 p_process_message := NULL;
4947
4948 FOR rec_lines IN cur_lines(p_shipment_header_id, p_shipment_line_id)
4949 LOOP
4950
4951
4952 OPEN cur_term(rec_lines.shipment_line_id);
4953 FETCH cur_term INTO r_term;
4954 CLOSE cur_term;
4955
4956 FOR rec_txns IN cur_txns(rec_lines.shipment_line_id, p_transaction_id)
4957 LOOP
4958 ln_apportion_factor := ABS(jai_rcv_trx_processing_pkg.get_apportion_factor(rec_txns.transaction_id));
4959
4960
4961 FOR tax_rec IN cur_tax(rec_lines.shipment_line_id , rec_txns.currency_conversion_rate) -- Harshita for Bug 4995579
4962 LOOP
4963 ln_cnt := 0;
4964 if tax_rec.qty_received<>0 then
4965 ln_amount := ROUND(((tax_rec.tax_amount * ln_apportion_factor)*(p_applied_qty/nvl(tax_rec.qty_received,1))),
4966 tax_rec.rounding_factor); --Added Round condition by Bgowrava for Bug#8414075
4967 end if;
4968
4969 jai_cmn_rgm_terms_pkg.generate_term_schedules(p_term_id => NVL(p_term_id,r_term.term_id),
4970 p_amount => ln_amount,
4971 p_register_date => r_term.receipt_date,
4972 p_schedule_id => ln_schedule_id,
4973 p_process_flag => lv_process_flag,
4974 p_process_msg => lv_process_msg);
4975
4976 IF lv_process_flag <> jai_constants.successful THEN
4977
4978 DELETE jai_rgm_trm_schedules_t
4979 WHERE schedule_id = ln_schedule_id;
4980
4981 p_process_status := lv_process_flag;
4982 p_process_message := lv_process_msg;
4983 RETURN;
4984 END IF;
4985
4986
4987 UPDATE jai_rgm_trm_schedules_t
4988 SET installment_amount = ROUND(installment_amount, tax_rec.rounding_factor)
4989 WHERE schedule_id = ln_schedule_id;
4990
4991 OPEN cur_sum_schedules(ln_schedule_id);
4992 FETCH cur_sum_schedules INTO r_sum_schedules;
4993 CLOSE cur_sum_schedules;
4994
4995 IF NVL(r_sum_schedules.total_installment_amount,0) <> NVL(ln_amount,0) THEN
4996 UPDATE jai_rgm_trm_schedules_t
4997 SET installment_amount = installment_amount + ln_amount - r_sum_schedules.total_installment_amount
4998 WHERE installment_no = r_sum_schedules.max_installment_no
4999 AND schedule_id = ln_schedule_id;
5000 END IF;
5001 OPEN cur_installpaid_count( cp_rcv_rgm_line_id => r_term.rcv_rgm_line_id,
5002 cp_transaction_id => rec_txns.transaction_id,
5003 cp_tax_id => tax_rec.tax_id);
5004
5005 FETCH cur_installpaid_count INTO ln_instpaid_cnt;
5006 CLOSE cur_installpaid_count;
5007
5008 UPDATE jai_rgm_trm_schedules_t
5009 SET installment_amount = installment_amount * (-1) /*This is to reduce the quantity available for claim*/
5010 WHERE schedule_id = ln_schedule_id;
5011
5012
5013 for sch_det in cur_get_schedule(ln_schedule_id)
5014 loop
5015 select jai_rcv_rgm_claims_s.NEXTVAL into ln_claim_schedule_id from dual;
5016 INSERT
5017 INTO jai_rcv_rgm_claims
5018 (
5019 CLAIM_SCHEDULE_ID,
5020 RCV_RGM_LINE_ID,
5021 Shipment_header_id,
5022 Shipment_line_id,
5023 Regime_code,
5024 Tax_transaction_id,
5025 Transaction_type,
5026 Transaction_id,
5027 Parent_transaction_type,
5028 Installment_no,
5029 Installment_amount,
5030 Claimed_amount,
5031 Scheduled_date,
5032 claimed_date,
5033 Status,
5034 Manual_claim_flag,
5035 Remarks,
5036 Tax_type,
5037 Tax_id,
5038 Trx_tax_id,
5039 CREATED_BY,
5040 CREATION_DATE,
5041 LAST_UPDATED_BY,
5042 LAST_UPDATE_DATE,
5043 LAST_UPDATE_LOGIN
5044 )values
5045 (ln_claim_schedule_id,
5046 r_term.rcv_rgm_line_id,
5047 rec_lines.shipment_header_id,
5048 rec_lines.shipment_line_id,
5049 jai_constants.vat_regime,
5050 rec_txns.tax_transaction_id,
5051 rec_txns.transaction_type,
5052 rec_txns.transaction_id,
5053 rec_txns.parent_transaction_type,
5054 sch_det.installment_no,
5055 sch_det.installment_amount,
5056 NULL,
5057 sch_det.installment_date,
5058 NULL,
5059 'N',
5060 NULL,
5061 NULL,
5062 tax_rec.tax_type,
5063 tax_rec.tax_id,
5064 NULL,
5065 fnd_global.user_id,
5066 SYSDATE,
5067 fnd_global.user_id,
5068 SYSDATE,
5069 fnd_global.login_id);
5070 if ln_instpaid_cnt>nvl(ln_cnt,0) then
5071 update jai_rcv_rgm_claims
5072 set claimed_amount=0,
5073 claimed_date=sysdate,
5074 status='Y',
5075 installment_amount=0
5076 where claim_schedule_id=ln_claim_schedule_id;
5077 ln_cnt:=nvl(ln_cnt,0)+1;
5078 else
5079 ln_debit_amt:=nvl(ln_debit_amt,0)+ sch_det.installment_amount;
5080 end if;
5081
5082 end loop;
5083 update jai_rcv_rgm_lines
5084 set recoverable_amount = nvl(recoverable_amount,0) + nvl(ln_debit_amt,0)
5085 where rcv_rgm_line_id = r_term.rcv_rgm_line_id;
5086
5087 cgvat_reposit_entry(p_organization_id ,
5088 p_location_id ,
5089 p_Set_of_books_id ,
5090 p_currency ,
5091 p_transaction_header_id,
5092 p_transaction_temp_id ,
5093 rec_txns.transaction_id,
5094 p_vat_invoice_no ,
5095 tax_rec.tax_type ,
5096 (-1) * ln_debit_amt ,
5097 ln_schedule_id ,
5098 lv_process_flag ,
5099 lv_process_msg );
5100
5101 if lv_process_flag<> jai_constants.successful THEN
5102 app_exception.raise_exception;
5103 end if;
5104
5105 DELETE jai_rgm_trm_schedules_t
5106 WHERE schedule_id = ln_schedule_id;
5107
5108 END LOOP;
5109
5110 END LOOP;
5111
5112 END LOOP;
5113 EXCEPTION
5114 WHEN OTHERS THEN
5115 p_process_status := lv_process_flag;
5116 p_process_message := lv_process_msg;
5117 END claim_balance_cgvat;
5118
5119 PROCEDURE process_vat_claim_acctg(
5120 p_repository_id IN NUMBER,
5121 p_process_status OUT NOCOPY VARCHAR2,
5122 p_process_message OUT NOCOPY VARCHAR2)
5123 IS
5124 CURSOR cur_claims
5125 IS
5126 SELECT REPOSITORY_ID,
5127 REGIME_CODE,
5128 TAX_TYPE,
5129 SOURCE_DOCUMENT_ID,
5130 DEBIT_AMOUNT,
5131 CREDIT_AMOUNT,
5132 ORGANIZATION_ID,
5133 LOCATION_ID
5134 FROM jai_rgm_trx_records
5135 WHERE repository_id = p_repository_id;
5136
5137 CURSOR cur_regimes(cp_regime_code VARCHAR2)
5138 IS
5139 SELECT regime_id
5140 FROM jai_rgm_definitions
5141 WHERE regime_code = cp_regime_code;
5142
5143 lv_accounting_type VARCHAR2(100);
5144 lv_account_nature VARCHAR2(100);
5145 lv_source_name VARCHAR2(100);
5146 lv_category_name VARCHAR2(100);
5147 ln_debit_ac NUMBER;
5148 ln_credit_ac NUMBER;
5149 lv_currency_code VARCHAR2(10);
5150 ln_entered_dr NUMBER;
5151 ln_entered_cr NUMBER;
5152 ld_accounting_date DATE;
5153 ln_repository_id jai_rgm_trx_records.repository_id%TYPE;
5154 lv_destination VARCHAR2(10);
5155 lv_code_path JAI_RCV_TRANSACTIONS.codepath%TYPE;
5156 lv_process_status VARCHAR2(2);
5157 lv_process_message VARCHAR2(1000);
5158 ln_regime_id jai_rgm_definitions.regime_id%TYPE;
5159
5160 rec_claims cur_claims%ROWTYPE;
5161
5162 lv_reference_10 gl_interface.reference10%TYPE;
5163 lv_reference_23 gl_interface.reference23%TYPE;
5164 lv_reference_24 gl_interface.reference24%TYPE;
5165 lv_reference_26 gl_interface.reference26%TYPE;
5166
5167 sqlstmt VARCHAR2(512);
5168 BEGIN
5169
5170 lv_accounting_type := 'REVERSAL';
5171 lv_account_nature := 'VAT CLAIM';
5172 lv_source_name := 'Purchasing';
5173 lv_category_name := 'Receiving India';
5174 lv_reference_10 := 'VAT claim accounting';
5175 lv_reference_23 := 'jai_mtl_trxs_pkg.process_vat_claim_acctg';
5176 lv_reference_24 := 'jai_rgm_trx_records';
5177 lv_reference_26 := 'repository_id';
5178 lv_currency_code := jai_constants.func_curr;
5179 ld_accounting_date := TRUNC(SYSDATE);
5180 lv_destination := 'G';
5181
5182 sqlstmt := 'opening cur_claims cursor';
5183 OPEN cur_claims;
5184 FETCH cur_claims INTO rec_claims;
5185 CLOSE cur_claims;
5186
5187 sqlstmt := 'opening cur_regimes cursor';
5188 OPEN cur_regimes(rec_claims.regime_code);
5189 FETCH cur_regimes INTO ln_regime_id;
5190 CLOSE cur_regimes;
5191
5192 sqlstmt := 'jai_cmn_rgm_recording_pkg.get_account for interim recovery account';
5193 ln_debit_ac := jai_cmn_rgm_recording_pkg.get_account(
5194 p_regime_id => ln_regime_id,
5195 p_organization_type => jai_constants.orgn_type_io,
5196 p_organization_id => rec_claims.organization_id,
5197 p_location_id => rec_claims.location_id,
5198 p_tax_type => rec_claims.tax_type,
5199 p_account_name => jai_constants.recovery_interim);
5200
5201 sqlstmt := 'jai_cmn_rgm_recording_pkg.get_account for recovery account';
5202 ln_credit_ac := jai_cmn_rgm_recording_pkg.get_account(
5203 p_regime_id => ln_regime_id,
5204 p_organization_type => jai_constants.orgn_type_io,
5205 p_organization_id => rec_claims.organization_id,
5206 p_location_id => rec_claims.location_id,
5207 p_tax_type => rec_claims.tax_type,
5208 p_account_name => jai_constants.recovery);
5209
5210
5211 ln_entered_dr := rec_claims.credit_amount;
5212 ln_entered_cr := NULL;
5213 lv_reference_10 := 'India Local VAT Claim Entries For Repository id'||rec_claims.repository_id;
5214
5215 sqlstmt := 'jai_rcv_accounting_pkg.process_transaction for debit';
5216 IF NVL(ln_entered_dr,0) <> 0 THEN
5217 jai_rcv_accounting_pkg.process_transaction(
5218 p_transaction_id => rec_claims.source_document_id,
5219 p_acct_type => lv_accounting_type,
5220 p_acct_nature => lv_account_nature,
5221 p_source_name => lv_source_name,
5222 p_category_name => lv_category_name,
5223 p_code_combination_id => ln_debit_ac,
5224 p_entered_dr => ln_entered_dr,
5225 p_entered_cr => ln_entered_cr,
5226 p_currency_code => lv_currency_code,
5227 p_accounting_date => ld_accounting_date,
5228 p_reference_10 => lv_reference_10,
5229 p_reference_23 => lv_reference_23,
5230 p_reference_24 => lv_reference_24,
5231 p_reference_25 => p_repository_id,
5232 p_reference_26 => lv_reference_26,
5233 p_destination => lv_destination,
5234 p_simulate_flag => 'N',
5235 p_codepath => lv_code_path,
5236 p_process_message => lv_process_message,
5237 p_process_status => lv_process_status,
5238 p_reference_name => jai_constants.repository_name,
5239 p_reference_id => p_repository_id);
5240
5241 IF lv_process_status <> jai_constants.successful THEN
5242 p_process_status := lv_process_status;
5243 p_process_message := lv_process_message;
5244 RETURN;
5245 END IF;
5246 END IF;
5247
5248 ln_entered_dr := NULL;
5249 ln_entered_cr := rec_claims.credit_amount;
5250 sqlstmt := 'jai_rcv_accounting_pkg.process_transaction for credit';
5251 IF NVL(ln_entered_cr,0) <> 0 THEN
5252 jai_rcv_accounting_pkg.process_transaction(
5253 p_transaction_id => rec_claims.source_document_id,
5254 p_acct_type => lv_accounting_type,
5255 p_acct_nature => lv_account_nature,
5256 p_source_name => lv_source_name,
5257 p_category_name => lv_category_name,
5258 p_code_combination_id => ln_credit_ac,
5259 p_entered_dr => ln_entered_dr,
5260 p_entered_cr => ln_entered_cr,
5261 p_currency_code => lv_currency_code,
5262 p_accounting_date => ld_accounting_date,
5263 p_reference_10 => lv_reference_10,
5264 p_reference_23 => lv_reference_23,
5265 p_reference_24 => lv_reference_24,
5266 p_reference_25 => p_repository_id,
5267 p_reference_26 => lv_reference_26,
5268 p_destination => lv_destination,
5269 p_simulate_flag => 'N',
5270 p_codepath => lv_code_path,
5271 p_process_message => lv_process_message,
5272 p_process_status => lv_process_status,
5273 p_reference_name => jai_constants.repository_name,
5274 p_reference_id => p_repository_id);
5275
5276 IF lv_process_status <> jai_constants.successful THEN
5277 p_process_status := lv_process_status;
5278 p_process_message := lv_process_message;
5279 RETURN;
5280 END IF;
5281 END IF;
5282
5283 p_process_status := jai_constants.successful;
5284 EXCEPTION
5285 WHEN OTHERS THEN
5286 p_process_status := jai_constants.unexpected_error;
5287 p_process_message := 'SQL error while calling '||sqlstmt||' :'||SQLCODE||' : '||SQLERRM;
5288 END process_vat_claim_acctg;
5289
5290 PROCEDURE delete_trx(p_transaction_header_id IN NUMBER,
5291 p_transaction_temp_id IN NUMBER)
5292 IS
5293 BEGIN
5294
5295 DELETE jai_mtl_match_receipts
5296 WHERE transaction_temp_id in
5297 (
5298 SELECT transaction_temp_id
5299 FROM jai_mtl_trxs
5300 WHERE transaction_header_id = p_transaction_header_id
5301 AND transaction_temp_id = nvl(p_transaction_temp_id, transaction_temp_id )
5302 AND transaction_commit_date is null
5303 );
5304
5305 DELETE jai_cmn_document_taxes
5306 WHERE source_doc_type = 'INTERORG_XFER'
5307 and source_table_name = 'MTL_MATERIAL_TRANSACTIONS_TEMP'
5308 AND source_doc_line_id IN
5309 (
5310 SELECT transaction_temp_id
5311 FROM jai_mtl_trxs
5312 WHERE transaction_header_id = p_transaction_header_id
5313 AND transaction_temp_id = nvl(p_transaction_temp_id, transaction_temp_id )
5314 AND transaction_commit_date is null
5315 );
5316
5317 DELETE JAI_CMN_MATCH_TAXES
5318 WHERE ref_line_id IN
5319 (
5320 SELECT transaction_temp_id
5321 FROM jai_mtl_trxs
5322 WHERE transaction_header_id = p_transaction_header_id
5323 AND transaction_temp_id = nvl(p_transaction_temp_id, transaction_temp_id )
5324 AND transaction_commit_date is null
5325 )
5326 AND order_invoice='X';
5327
5328 DELETE JAI_CMN_MATCH_RECEIPTS
5329 WHERE ref_line_id IN
5330 (
5331 SELECT transaction_temp_id
5332 FROM jai_mtl_trxs
5333 WHERE transaction_header_id = p_transaction_header_id
5334 AND transaction_temp_id = nvl(p_transaction_temp_id, transaction_temp_id )
5335 AND transaction_commit_date is null
5336 )
5337 AND order_invoice='X';
5338
5339 DELETE jai_mtl_trxs
5340 WHERE transaction_header_id = p_transaction_header_id
5341 AND transaction_temp_id = nvl(p_transaction_temp_id, transaction_temp_id )
5342 AND transaction_commit_date is null;
5343
5344 END delete_trx;
5345
5346 PROCEDURE default_taxes(
5347 p_to_organization_id number ,
5348 p_to_location_code VARCHAR2 ,
5349 p_transfer_subinventory varchar2 ,
5350 p_toorg_location_id number ,
5351 p_organization_id number ,
5352 p_subinventory_code varchar2 ,
5353 p_transaction_type_id number ,
5354 p_header_id NUMBER ,
5355 p_line_id NUMBER ,
5356 p_inventory_item_id NUMBER ,
5357 p_uom_code VARCHAR2 ,
5358 p_line_quantity NUMBER ,
5359 p_item_cost NUMBER ,
5360 p_currency VARCHAR2 ,
5361 p_currency_conv_factor NUMBER ,
5362 p_date_order DATE ,
5363 p_Iproc_profile_val number ,
5364 p_assessable_value OUT NOCOPY NUMBER ,
5365 p_vat_assessable_value OUT NOCOPY NUMBER ,
5366 p_tax_amount IN OUT NOCOPY NUMBER
5367 --, pn_gst_assessable_value OUT NOCOPY NUMBER -- Added by Jia for GST Bug#10043656 on 2010/09/10 /*Commented by mmurtuza for removing GST Changes*/
5368 )
5369 IS
5370
5371 PRAGMA AUTONOMOUS_TRANSACTION;
5372
5373 cursor c_cust_dtl is
5374 SELECT su.site_use_id,cas.cust_account_id
5375 FROM
5376 hz_cust_acct_sites_all cas,
5377 hz_cust_site_uses_all su,
5378 po_location_associations_all pla,
5379 hr_locations hrl
5380 WHERE cas.cust_acct_site_id = su.cust_acct_site_id
5381 AND su.site_use_id = pla.site_use_id(+)
5382 AND pla.location_id = hrl.location_id(+)
5383 AND su.site_use_code = 'SHIP_TO'
5384 AND RTRIM(Ltrim(hrl.location_code)) = LTRIM(Rtrim(p_to_location_code))
5385 AND hrl.inventory_organization_id = p_to_organization_id ; -- bug 6444945
5386
5387 cursor c_jai_mtl_trxs is
5388 SELECT to_subinventory ,
5389 inventory_item_id ,
5390 quantity ,
5391 transaction_uom
5392 FROM jai_mtl_trxs
5393 WHERE transaction_header_id = p_header_id
5394 AND transaction_temp_id = p_line_id;
5395
5396 r_jai_mtl_trxs c_jai_mtl_trxs%ROWTYPE;
5397 ln_site_use_id number(15);
5398 ln_cust_account_id number(15);
5399 ln_tax_category_id number(15);
5400 ln_user_id number(15);
5401
5402 ln_assessable_value NUMBER; -- number(15); changed for bug #8882785
5403 ln_vat_assessable_value NUMBER; -- number(15); changed for bug #8882785
5404 --ln_gst_assessable_value NUMBER; -- Added by Jia for GST Bug#10043656 on 2010/09/10 /*Commented by mmurtuza for removing GST Changes*/
5405
5406 --added by peng.zheng for bug 10043656, begins
5407 /*Start Commenting by mmurtuza for removing GST Changes*/
5408
5409 /*lv_enable_gst_flag VARCHAR2(3);
5410 v_location_id NUMBER;
5411
5412 CURSOR Location_Cursor IS
5413 SELECT Location_id
5414 FROM JAI_INV_SUBINV_DTLS
5415 WHERE Sub_Inventory_Name = p_subinventory_code
5416 AND organization_id = p_organization_id; */
5417 /*End Commenting by mmurtuza for removing GST Changes*/
5418
5419 --added by peng.zheng for bug 10043656, ends
5420
5421 BEGIN
5422
5423 ln_user_id := fnd_global.user_id;
5424
5425 open c_jai_mtl_trxs;
5426 fetch c_jai_mtl_trxs into r_jai_mtl_trxs;
5427 close c_jai_mtl_trxs;
5428
5429 /*
5430 ||The following condition is to see if only quantity is changed
5431 ||In this scenario the taxes need to be recalculated and not redefaulted.
5432 ||If any match information is present this would also be deleted
5433 */
5434 IF r_jai_mtl_trxs.quantity <> p_line_quantity THEN
5435
5436 IF r_jai_mtl_trxs.inventory_item_id = p_inventory_item_id AND
5437 r_jai_mtl_trxs.transaction_uom = p_uom_code AND
5438 r_jai_mtl_trxs.to_subinventory = p_transfer_subinventory THEN
5439
5440 UPDATE jai_cmn_document_taxes
5441 SET tax_amt = ( p_line_quantity / r_jai_mtl_trxs.quantity ) * tax_amt,
5442 FUNC_TAX_AMT = ( p_line_quantity / r_jai_mtl_trxs.quantity ) * func_tax_amt,
5443 last_update_date = sysdate,
5444 last_updated_by = ln_user_id
5445 WHERE source_doc_id = p_header_id
5446 AND source_doc_line_id = p_line_id;
5447
5448 UPDATE jai_mtl_trxs
5449 SET quantity = p_line_quantity,
5450 last_update_date = sysdate,
5451 last_updated_by = ln_user_id
5452 WHERE transaction_header_id = p_header_id
5453 AND transaction_temp_id = p_line_id;
5454
5455 DELETE JAI_CMN_MATCH_RECEIPTS
5456 WHERE ref_line_id = p_line_id
5457 AND order_invoice = 'X';
5458
5459 DELETE jai_mtl_match_receipts
5460 WHERE transaction_temp_id = p_line_id ;
5461
5462 DELETE JAI_CMN_MATCH_TAXES
5463 WHERE ref_line_id = p_line_id
5464 AND order_invoice = 'X' ;
5465
5466 COMMIT;
5467
5468 RETURN;
5469
5470 END IF;
5471
5472 END IF;
5473
5474 open c_cust_dtl;
5475 fetch c_cust_dtl into ln_site_use_id, ln_cust_account_id;
5476 close c_cust_dtl;
5477
5478
5479 /* following deletes all the IL data sofar saved as the taxes are going to redefault */
5480 delete_trx(
5481 p_transaction_header_id => p_header_id,
5482 p_transaction_temp_id => p_line_id
5483 );
5484
5485 -- lv_Iproc_profile_val := fnd_profile.value_specific(NAME =>'GL_SET_OF_BKS_ID',user_id=>ln_user_id);
5486
5487 --lv_enable_gst_flag := JAI_GST_GENERAL_PKG.IS_GST_ENABLED;--added by peng.zheng for bug 10043656 /*Commented by mmurtuza for removing GST Changes*/
5488 --IF nvl(lv_enable_gst_flag,'N') = 'N' THEN--added by peng.zheng for bug 10043656 /*Commented by mmurtuza for removing GST Changes*/
5489 jai_cmn_tax_defaultation_pkg.ja_in_cust_default_taxes (
5490 p_org_id =>p_to_organization_id,
5491 p_customer_id =>ln_cust_account_id,
5492 p_ship_to_site_use_id =>ln_site_use_id ,
5493 p_inventory_item_id =>p_inventory_item_id ,
5494 p_header_id =>p_header_id ,
5495 p_line_id =>p_line_id ,
5496 p_tax_category_id =>ln_tax_category_id
5497 );
5498 /*Start Commenting by mmurtuza for removing GST Changes*/
5499 /*ELSE
5500 --added by peng.zheng for bug 10043656, begins
5501 --Get The Location Id
5502 OPEN Location_Cursor;
5503 FETCH
5504 Location_Cursor
5505 INTO
5506 v_location_id;
5507 CLOSE Location_Cursor;
5508
5509 -- get v_location_id from WSH API if ln_location_id IS NULL
5510 IF v_location_id IS NULL
5511 THEN
5512 v_location_id := WSH_UTIL_CORE.Org_To_Location(p_organization_id, TRUE);
5513 END IF;
5514
5515
5516 jai_gst_tax_defaultation_pkg.jai_gst_cust_default_taxes (
5517 pn_organization_id =>p_organization_id,--should use 1st party organization
5518 pn_location_id =>v_location_id,
5519 pn_customer_id =>ln_cust_account_id,
5520 pn_ship_to_site_use_id =>ln_site_use_id,
5521 pn_inventory_item_id =>p_inventory_item_id,
5522 pd_transaction_date =>p_date_order,
5523 pn_tax_category_id =>ln_tax_category_id
5524 );
5525
5526 END IF;*/--added by peng.zheng for bug 10043656, ends
5527 /*End Commenting by mmurtuza for removing GST Changes*/
5528
5529 ln_assessable_value := jai_om_utils_pkg.get_oe_assessable_value
5530 (
5531 p_customer_id => ln_cust_account_id,
5532 p_ship_to_site_use_id => ln_site_use_id,
5533 p_inventory_item_id => p_inventory_item_id,
5534 p_uom_code => p_uom_code,
5535 p_default_price => p_item_cost,
5536 p_ass_value_date => p_date_order,
5537 p_sob_id => p_Iproc_profile_val,
5538 p_curr_conv_code => null,
5539 p_conv_rate => null
5540 );
5541
5542 -- copy(v_assessable_value,'MTL_TRX_LINE.ASSESSABLE_VALUE');
5543
5544 ln_vat_assessable_value := jai_general_pkg.ja_in_vat_assessable_value
5545 (
5546 p_party_id => ln_cust_account_id ,
5547 p_party_site_id => ln_site_use_id ,
5548 p_inventory_item_id => p_inventory_item_id ,
5549 p_uom_code => p_uom_code ,
5550 p_default_price => p_item_cost,
5551 p_ass_value_date => p_date_order ,
5552 p_party_type => 'C'
5553 );
5554
5555 ln_vat_assessable_value :=round( nvl(ln_vat_assessable_value,0) * p_line_quantity,2);--added round for bug#8882785
5556
5557 -- copy(ln_vat_assessable_value,'MTL_TRX_LINE.VAT_ASSESSABLE_VALUE');
5558
5559 -- Added by Jia for GST Bug#10043656 on 2010/09/10, Begin
5560 -------------------------------------------------------------------------------
5561 /*Start Commenting by mmurtuza for removing GST Changes*/
5562 /*ln_gst_assessable_value := jai_gst_general_pkg.get_gst_assessable_value
5563 (
5564 p_party_id => ln_cust_account_id ,
5565 p_party_site_id => ln_site_use_id ,
5566 p_inventory_item_id => p_inventory_item_id ,
5567 p_uom_code => p_uom_code ,
5568 p_default_price => p_item_cost,
5569 p_ass_value_date => p_date_order ,
5570 p_party_type => 'C'
5571 );
5572
5573 ln_gst_assessable_value :=round( nvl(ln_gst_assessable_value,0) * p_line_quantity,2);*/
5574 /*End Commenting by mmurtuza for removing GST Changes*/
5575 -------------------------------------------------------------------------------
5576 -- Added by Jia for GST Bug#10043656 on 2010/09/10, Begin
5577
5578 INSERT INTO jai_mtl_trxs(
5579 transaction_id,
5580 transaction_header_id ,
5581 transaction_temp_id ,
5582 transaction_type_id ,
5583 from_organization ,
5584 to_organization ,
5585 inventory_item_id ,
5586 from_subinventory ,
5587 to_subinventory ,
5588 location_id ,
5589 quantity ,
5590 taxcategory_id ,
5591 selling_price ,
5592 assessable_value ,
5593 vat_assessable_value ,
5594 --gst_assessable_value , -- Added by Jia for GST Bug#10043656 on 2010/09/10 /*Commented by mmurtuza for removing GST Changes*/
5595 transaction_date,
5596 transaction_uom ,
5597 creation_date,
5598 created_by,
5599 last_update_date,
5600 last_updated_by,
5601 last_update_login
5602 ) VALUES (
5603 jai_mtl_trxs_s.nextval,
5604 p_header_id, -- :mtl_trx_line.transaction_header_id,
5605 p_line_id, -- :mtl_trx_line.transaction_temp_id,
5606 p_transaction_type_id, -- :mtl_trx_line.transaction_type_id,
5607 p_organization_id, -- :mtl_trx_line.organization_id,
5608 p_to_organization_id, -- :org.to_org_id,
5609 p_inventory_item_id, -- :mtl_trx_line.inventory_item_id,
5610 p_subinventory_code, -- :mtl_trx_line.subinventory_code,
5611 p_transfer_subinventory, -- :mtl_trx_line.transfer_subinventory,
5612 p_toorg_location_id, -- :mtl_trx_line.toorg_location_id
5613 p_line_quantity, -- :mtl_trx_line.transaction_quantity
5614 ln_tax_category_id, -- :ja_in_tax.tax_category_id,
5615 p_item_cost, -- :mtl_trx_line.item_cost,
5616 ln_assessable_value, -- v_assessable_value,
5617 ln_vat_assessable_value, -- :mtl_trx_line.vat_assessable_value,
5618 --ln_gst_assessable_value, -- :Mtl_trx_line.gst_assessable_value. Added by Jia for GST Bug#10043656 on 2010/09/10 /*Commented by mmurtuza for removing GST Changes*/
5619 sysdate, -- p_date_order
5620 p_uom_code, -- :mtl_trx_line.transaction_uom,
5621 sysdate,
5622 ln_user_id,
5623 sysdate,
5624 ln_user_id,
5625 fnd_global.login_id
5626 );
5627
5628 --added round for bug#8882785
5629 ln_assessable_value := round(nvl(ln_assessable_value,0)* p_line_quantity,2); /* value returned is excise assessable value of the line */
5630
5631 jai_cmn_tax_defaultation_pkg.ja_in_calc_prec_taxes(
5632 transaction_name => 'INTERORG_XFER',
5633 p_tax_category_id => ln_tax_category_id,
5634 p_header_id => p_header_id,
5635 p_line_id => p_line_id,
5636 p_assessable_value => ln_assessable_value ,
5637 p_tax_amount => p_tax_amount ,
5638 p_inventory_item_id => p_inventory_item_id ,
5639 p_line_quantity => p_line_quantity ,
5640 p_uom_code => p_uom_code,
5641 p_vendor_id => NULL,
5642 p_currency => 'INR',
5643 p_currency_conv_factor => p_currency_conv_factor,
5644 p_creation_date => sysdate ,
5645 p_created_by => ln_user_id,
5646 p_last_update_date => sysdate ,
5647 p_last_updated_by => ln_user_id,
5648 p_last_update_login => fnd_global.login_id ,
5649 p_operation_flag => NULL,
5650 p_vat_assessable_value => ln_vat_assessable_value
5651 --, pn_gst_assessable_value => ln_gst_assessable_value -- Added by Jia for GST Bug#10043656 on 2010/09/10 /*Commented by mmurtuza for removing GST Changes*/
5652 ) ;
5653
5654 COMMIT;
5655
5656 p_assessable_value := ln_assessable_value; /* value returned is excise assessable value of the line */
5657 p_vat_assessable_value := nvl(ln_vat_assessable_value,0); /* value returned is VAT assessable value of the line */
5658 --pn_gst_assessable_value := nvl(ln_gst_assessable_value, 0); -- Added by Jia for GST Bug#10043656 on 2010/09/10 /*Commented by mmurtuza for removing GST Changes*/
5659
5660 exception
5661 when others then
5662 app_exception.raise_exception;
5663
5664 END default_taxes;
5665
5666 PROCEDURE delete_trx_autonomous(p_transaction_header_id IN NUMBER,
5667 p_transaction_temp_id IN NUMBER)
5668 IS
5669 PRAGMA AUTONOMOUS_TRANSACTION;
5670 BEGIN
5671
5672 delete_trx(p_transaction_header_id,p_transaction_temp_id) ;
5673 COMMIT;
5674
5675 END delete_trx_autonomous;
5676
5677
5678 PROCEDURE sync_with_base_trx(
5679 p_transaction_header_id IN NUMBER,
5680 p_transaction_temp_id IN NUMBER,
5681 p_event IN VARCHAR2
5682 ) IS
5683 BEGIN
5684
5685 /*commented for bug#8800063
5686 delete_trx_autonomous is giving the deadlock if any error occurs
5687 during the forms commit processing*
5688 sync_with_base_trx will be called from key-exit,key-clrrec,key-clrblk,when-window-closed,key-clrform,
5689 hence added the commit stmt such that the delete_trx stmts will get commited.*
5690
5691 IF p_event <> 'KEY-COMMIT' THEN
5692
5693 delete_trx_autonomous(p_transaction_header_id ,
5694 p_transaction_temp_id);
5695
5696 ELSE
5697 */
5698
5699 delete_trx(
5700 p_transaction_header_id ,
5701 p_transaction_temp_id
5702 );
5703 commit; --bug#8800063
5704
5705 -- END IF;
5706
5707 END sync_with_base_trx;
5708
5709 END;