[Home] [Help]
PACKAGE BODY: APPS.JAI_OM_RG_PKG
Source
1 PACKAGE BODY jai_om_rg_pkg AS
2 /* $Header: jai_om_rg.plb 120.41.12020000.4 2013/03/25 07:14:34 mmurtuza ship $ */
3
4
5 /*----------------------------------------------------------------------------------------
6 Filename:
7
8 Change History:
9
10 Date Remarks
11 --------- -------------------------------------------------------------
12 08-Jun-2005 File Version 116.2. Object is Modified to refer to New DB Entity names in place of Old DB Entity Names
13 as required for CASE COMPLAINCE.
14
15 13-Jun-2005 Ramananda for bug#4428980. File Version: 116.3
16 Removal of SQL LITERALs is done
17
18 06-Jul-2005 Ramananda for bug#4477004. File Version: 116.4
19 GL Sources and GL Categories got changed. Refer bug for the details
20
21 14-Jul-2005 rchandan for bug#4487676. File Version 117.2
22 Sequence JAI_CMN_RG_23AC_I_TXNS_S is replaced by JAI_CMN_RG_23AC_I_TRXS_S
23
24 28/07/2005 Changes in JA_IN_ACCOUNTING_ENTRIES Procedure
25 Ramananda for Bug#4516577, Version 120.2
26
27 Problem
28 -------
29 ISO Accounting Entries from Trading to Excise bonded inventory are not generated in case of following Scenarios
30 1. Trading organization to Trading Organization (only Source organizations with the 'Excise in RG23D' setup).
31 2. Trading organization to Manufacturing Organization (Source Organization with the 'Excise in RG23D' setup).
32
33 Fix
34 ---
35 1. In the procedure - ja_in_accounting_entries, made the following changes
36 a. Modified the IF condition -
37 "IF NVL(p_non_iso_credit_account_id,0) >0 AND NVL(p_non_iso_debit_account_id,0) >0 AND
38 NOT
39 (
40 (
41 NVL(lv_source_trading,'N') = 'Y' and NVL(lv_dest_trading,'N') = 'Y'
42 )
43 AND
44 (
45 NVL(ln_dest_excise_in_rg23d,'N') = 'Y' and NVL(ln_src_excise_in_rg23d,'N') = 'Y'
46 )
47 )"
48 TO
49 "IF NVL(p_non_iso_credit_account_id,0) >0 AND NVL(p_non_iso_debit_account_id,0) >0 AND
50 NOT
51 (
52 NVL(lv_source_trading,'N') = 'Y'
53 AND ( NVL(lv_dest_trading,'N') = 'Y' OR NVL(lv_dest_manufacturing,'N') = 'Y')
54 AND NVL(ln_src_excise_in_rg23d,'N') = 'Y'
55
56 )"
57
58 b. Replaced the IF condition
59 "IF NVL(lv_source_trading,'N') = 'Y' and NVL(lv_dest_trading,'N') = 'Y' THEN
60 IF NVL(ln_dest_excise_in_rg23d,'N') = 'Y' and NVL(ln_src_excise_in_rg23d,'N') = 'Y'"
61 By
62 "IF NVL(lv_source_trading,'N') = 'Y'
63 AND ( NVL(lv_dest_trading,'N') = 'Y' OR NVL(lv_dest_manufacturing,'N') = 'Y')
64 AND NVL(ln_src_excise_in_rg23d,'N') = 'Y'"
65
66 2. In the Procedure Ja_In_Rg23d_Entry, Changed the IF Condition -
67 IF v_order_source_id = 10 AND
68 (
69 (
70 NVL(lv_source_trading,'N') = 'Y' and NVL(lv_dest_trading,'N') = 'Y'
71 )
72 AND
73 (
74 NVL(ln_dest_excise_in_rg23d,'N') = 'Y' and NVL(ln_src_excise_in_rg23d,'N') = 'Y'
75 )
76 )
77 TO
78 IF v_order_source_id = 10 AND
79 (
80 NVL(lv_source_trading,'N') = 'Y'
81 AND ( NVL(lv_dest_trading,'N') = 'Y' OR NVL(lv_dest_manufacturing,'N') = 'Y')
82 AND NVL(ln_src_excise_in_rg23d,'N') = 'Y'
83 )
84
85 3. In the Procedure ja_in_register_txn_entry, in the cursor - c_get_om_cess_amount,
86 Added the NVL for column sum(jsptl.func_tax_amount) in the Select
87
88 (Functional) Dependency Due to This Bug
89 --------------------------
90 jai_rcv_rcv_rtv.plb (120.3)
91 jai_rcv_trx_prc.plb (120.2)
92
93
94 19-aUG-2005 Bug4562791. Added by Lakshmi Gopalsami Version 120.3
95 Added gl_accounting_date as a package variable.
96 Passing this variable insted of NULL for gl accounting date.
97
98 Dependencies(Functional nd Technical)
99 ------------
100 jai_om_rg.pls 120.2
101 jai_om_wsh.plb 120.3
102
103 02-DEC-2005 Bug 4765347, Added by aiyer for Version 120.4
104 Added few more fnd_file statements.
105
106 Dependencies Due to this issue :-
107 Yes, please refer the future dependencies section.
108
109
110
111 30-OCT-2006 SACSETHI for bug 5228046, File version 120.9
112 Forward porting the change in 11i bug 5365523 (Additional CVD Enhancement).
113 This bug has datamodel and spec changes.
114
115 26-FEB-2007 SSAWANT , File version 120.11
116 Forward porting the change in 11.5 bug 4714518 to R12 bug no 4724137
117
118 27. 16/04/2007 bduvarag for the Bug#5989740, file version 120.12
119 Forward porting the changes done in 11i bug#5907436
120
121 28. 04/06/2007 sacsethi for bug 6109941 , File version 120.13
122
123 1. Cursor c_get_ar_cess_rate is removed for bug 5228046 forward porting bug
124 2. in procedure ja_in_pla_entry , sh_cess_amoumnt was missing
125
126 29 17/06/2007 ssumaith - bug# 6131804 - bond register is not gettnig hit in INR for foreign currency trxs.
127 Code changes are done in this package for handling the cess amount.
128
129 30. 28/06/2007 CSahoo - BUG#6155839, File Version 120.16
130 replaced RG Register Data Entry by jai_constants.je_category_rg_entry
131 31. 02/07/2007 vkaranam - BUG#6159579, File Version 120.17
132 1.In Procedure ja_in_cess_entries while calling ja_in_om_cess_register_entries p_delivery_detail_id parameter is not passed.
133
134 32. 05/07/2007 kunkumar for Bug#5745729 file version 120.18
135 Modified the cursors in the procedure ja_in_om_cess_register_entries so as to be in sync with the latest
136 version in R11i.Also there are changes to the body of the procedure.
137
138
139 33. 04/12/2007 ssumaith - bug# 6650203 - file version 120.8.12000000.4
140
141 Issue :
142
143 When the excise invoice number is having characters in it,the bond register transaction is failing as
144 the excise invoice number was being inserted into the picking_header_id field in the JAI_OM_OE_BOND_TRXS table.
145 the picking header id field was of type number and hence a character insert is causing an invalid number error.
146
147 Fix :
148
149 Made the following changes
150 a) in the jai_om_rg_pkg, when the insert into the JAI_OM_OE_BOND_TRXS table happens through the ja_in_register_txn_entry procedure , insert of excise invoice number into picking header id has been removed.
151 Instead the picking_line_id is stamped with the delivery_id / customer_trx_line_id in case of OM / AR respectively.
152
153 34. 14-May-2008 Changes by nprashar for bug # 6710747.
154 Issue:INTER-ORGANIZATION TRANSFER WITH EXCISE TAXES FAILS
155 Reason:
156 p_header_id parameter is used to insert the excise_invoice_id value of ja_in_rg23_part_i table.
157 if excise_invoice_no generated contains characters then while calling ja_in_rg23_part_i procedure ,the calling prg
158 errors out.
159 Fix:
160 Changed the ja_in_rg23_part_i entry procedure p_header_id parameter to varchar2 type.
161
162 35 23-Jun-2008 Changed by JMEENA for bug#7172215
163 1. Added condition IF NVL(ln_Cess_amount,0) > 0 before calling ja_in_cess_acctg_entries in the procedure ja_in_cess_register_entries.
164 2. Added log messages before RAISE_APPLICATION_ERROR to print in the log file.
165
166 36. 01-Aug-2008 Changed by JMEENA for bug#7277543
167 Added log messages to print in log file for missing accounts setup informations.
168 37 13-oct-2008 bug#7479016
169 Forward ported the changes done in 5597403
170 File Version : 120.8.12000000.8/120.24
171
172 38 01-Jun-2009 Bug 8537295 File version 120.8.12000000.9 / 120.25
173 Issue - Accounting entries for cess / sh cess taxes are not rounded for Internal Sales Orders
174 Fix - Changed the value of v_precision variable in JA_IN_CESS_ENTRIES procedure from the
175 currency setup value to zero.
176
177 39. 31-mar-2010 vkaranam for bug#9539924
178 Issue:
179 Cess/Shcess amounts in RG register are not rounded to nearest rupee for Manual AR invoice.
180 Fix:
181 Changes are done in ja_in_Ar_cess_Register_entries procedure.
182 added round to func_tax_amount in c_tax_type_rec cursor.
183
184 26 05-Apr-2010 Bug 9550254
185 The opening balance for the RG23 Part I and RG I has been derived from the previous
186 financial year closing balance, if no entries found for the current year.
187 27-apr-2010 bug#9466919
188 issue :quantity in rg registers are not in sync with the inventory.
189 fix:
190 added the rounding precision of 5 to the quantity fields while inserting.
191 28. 03-Mar-2011 Xiao for Open Interface ER bug#11683927.
192 Fixed: For 'External' event, only accounting is updated with tax ccid and tax amount
193 from JAI Interface table. Do not update RG register.
194 29. 10-May-2011 Zhiwei for Open Interface ER Bug#12537533 .
195 Fixed: For External event, check put to JAI_JAR_TRXS_TRIGGER_PKG where the procedure was invoked .
196
197 6. 27-Dec-2012 mmurtuza for bug 16022303
198 Issue: RG23D REVERSAL ENTRY DEBITS COGS IN CASE OF INCLUSIVE TAX
199 Fix: Added code in Ja_In_Rg23d_Entry to bifurcate amount to excise, cess and she cess paid accounts.
200 Also ported the cahnges done for bug 14307860
201
202 44. 11-JAN-2013 nkodakan for the bug 16014871
203 Issue : bond register cess and sh cess are reflected with Zero values
204 Fix : Issue is cess and sh cess amouts are picking from the AR table. So made some changes mentioned below.
205 commented the cursors c_get_ar_exc_amount ,c_get_ar_cess_amount, c_ar_cess_ctr, c_ar_sh_cess_ctr which are picking the data from the AR tables for bond register. Actually it should pick from shipment tables
206 Added cursor c_get_om_cess_amount, c_get_om_sh_cess_amount to get the cess and sh cess amount for the particular delivery id. Added a cursor get_ref_header_line_id to get the rma header id, rma line id using rma_line_id as a parameter.
207 Added a cursor get_rma_delivery_id to get the delivery id using header id and line id
208
209
210
211 Future Dependencies For the release Of this Object:-
212 (Please add a row in the section below only if your bug introduces a dependency due to spec change/ A new call to a object/
213 A datamodel change )
214
215 ----------------------------------------------------------------------------------------------------------------------------------------------------
216 Current Version Current Bug Dependent Files Version Author Date Remarks
217 Of File On Bug/Patchset Dependent On
218 jai_om_rg_pkg.plb
219 ----------------------------------------------------------------------------------------------------------------------------------------------------
220 120.4 4765347 JAIITMCL.fmb 120.9 Aiyer 02-DEC-2005
221 jai_om_wsh.plb 120.5 Aiyer 02-DEC-2005
222 ---------------------------------------------------------------------------------------------------------------------------------------------------- */
223
224 -- start additions by ssumaith - bug#3817625
225
226
227
228
229
230 /***************************** START JA_IN_CESS_ENTRIES *******************************************************************/
231
232 Procedure JA_IN_CESS_ENTRIES
233 (
234 p_organization_id number ,
235 p_location_id number ,
236 p_delivery_detail_id number ,
237 p_intransit_inv_acct number ,
238 p_intercompany_rcvbl number ,
239 p_intercompany_paybl number ,
240 p_fob_point number ,
241 p_currency_code varchar2 ,
242 p_source_name varchar2 ,
243 p_category_name varchar2 ,
244 p_created_by Number ,
245 P_REF_10 varchar2 ,
246 P_REF_23 varchar2 ,
247 P_REF_24 varchar2 ,
248 P_REF_25 varchar2 ,
249 P_REF_26 varchar2
250 )
251 IS
252
253 -- A/c Entries for CESS needs to be passed as follows:
254
255 -- Dr Intransit Material Value
256 -- Cr CESS Payable Paid
257 -- It will be called from ja_in_pla_entry and ja_in_rg23_part_ii_entry because only they have the
258 -- amount impact for excise registers
259
260 --If the FOB point is set to SHIPMENT, the following entry also needs to be passed :
261
262 -- Intercompany Receivable Cess amt
263 -- Intercompany Payables cess amt
264
265 cursor c_delivery_cur(cp_delivery_Detail_id number) is
266 select delivery_id , org_id
267 from JAI_OM_WSH_LINES_ALL
268 where delivery_detail_id = cp_delivery_Detail_id;
269
270 cursor c_cess_amount(cp_delivery_id number) is
271 select sum(a.tax_amount)
272 from JAI_OM_WSH_LINE_TAXES a, JAI_CMN_TAXES_ALL b
273 where delivery_detail_id in
274 (select delivery_detail_id
275 from JAI_OM_WSH_LINES_ALL
276 where delivery_id = cp_delivery_id
277 )
278 and a.tax_id = b.tax_id
279 and upper(b.tax_type) in (jai_constants.TAX_TYPE_CVD_EDU_CESS,jai_constants.TAX_TYPE_EXC_EDU_CESS);
280
281
282 /*Bug 5989740 bduvarag start*/
283 cursor c_sh_cess_amount(cp_delivery_id number) is
284 select sum(a.tax_amount)
285 from JAI_OM_WSH_LINE_TAXES a, JAI_CMN_TAXES_ALL b
286 where delivery_detail_id in
287 (select delivery_detail_id
288 from JAI_OM_WSH_LINES_ALL
289 where delivery_id = cp_delivery_id
290 )
291 and a.tax_id = b.tax_id
292 and upper(b.tax_type) in (JAI_CONSTANTS.TAX_TYPE_SH_CVD_EDU_CESS,JAI_CONSTANTS.TAX_TYPE_SH_EXC_EDU_CESS);
293
294 /*Bug 5989740 bduvarag end*/
295 cursor c_fetch_cess_account(cp_organization_id number , cp_location_id number) is
296 select cess_paid_payable_account_id
297 from JAI_CMN_INVENTORY_ORGS
298 where organization_id = cp_organization_id
299 and location_id = cp_location_id;
300 /*Bug 5989740 bduvarag start*/
301 cursor c_fetch_sh_cess_account(cp_organization_id number , cp_location_id number) is
302 select sh_cess_paid_payable_acct_id
303 from JAI_CMN_INVENTORY_ORGS
304 where organization_id = cp_organization_id
305 and location_id = cp_location_id;
306 /*Bug 5989740 bduvarag end*/
307
308 /* Bug 5243532. Added by Lakshmi Gopalsami
309 Removed the cursors c_get_sob_currency and currency
310 c_currency_precision and implemented the same using caching logic.
311 */
312 l_func_curr_det jai_plsql_cache_pkg.func_curr_details;
313
314
315 v_precision fnd_currencies.precision%type;
316 v_currency_code GL_SETS_OF_BOOKS.CURRENCY_CODE%TYPE ;
317 v_delivery_id JAI_OM_WSH_LINES_ALL.delivery_id%type;
318 v_cess_amount number;
319 v_sh_cess_amount number;/*Bug 5989740 bduvarag*/
320 v_cess_paid_payable JAI_CMN_INVENTORY_ORGS.cess_paid_payable_account_id%type;
321 v_org_id JAI_OM_WSH_LINES_ALL.org_id%type;
322 ln_sh_Cess_paid_payable JAI_CMN_INVENTORY_ORGS.cess_paid_payable_account_id%type;/*Bug 5989740 bduvarag*/
323 lv_object_name CONSTANT VARCHAR2 (61) := 'jai_om_rg_pkg.ja_in_cess_entries';
324
325 /*
326 The procedure assumes that it will be called from the ja_in_accounting_entries procedure only when
327 it is an ISO order ie the comparison is to be done in ja_in_accounting _entries_procedure
328 on p_source_name = 'Register India', p_category_name= 'Register India' and order_type_id = 10
329 So no further check is done here for the same.
330 */
331 begin
332
333 Fnd_File.PUT_LINE(Fnd_File.LOG, ' start of JA_IN_CESS_ENTRIES with p_delivery_detail_id = ' || p_delivery_detail_id);
334
335 v_Delivery_id :=0;
336 open c_delivery_cur(p_delivery_detail_id);
337 fetch c_delivery_cur into v_Delivery_id, v_org_id;
338 close c_delivery_cur;
339
340 Fnd_File.PUT_LINE(Fnd_File.LOG, 'v_Delivery_id =, Org id = ' || v_Delivery_id || ' : ' || v_org_id);
341
342 if v_delivery_id > 0 then
343
344 open c_cess_amount(v_Delivery_id);
345 fetch c_cess_amount into v_cess_amount;
346 close c_cess_amount;
347 /*Bug 5989740 bduvarag*/
348 open c_sh_cess_amount(v_Delivery_id);
349 fetch c_sh_cess_amount into v_sh_cess_amount;
350 close c_sh_cess_amount;
351
352 open c_fetch_cess_account( p_organization_id , p_location_id);
353 fetch c_fetch_cess_account into v_cess_paid_payable;
354 close c_fetch_cess_account ;
355 /*Bug 5989740 bduvarag*/
356 open c_fetch_sh_cess_account( p_organization_id , p_location_id);
357 fetch c_fetch_sh_cess_account into ln_sh_Cess_paid_payable;
358 close c_fetch_sh_cess_account ;
359
360 /* Bug 5243532. Added by Lakshmi Gopalsami
361 Removed the reference to cursors c_get_sob_currency
362 and implemented using caching logic.
363 */
364 l_func_curr_det := jai_plsql_cache_pkg.return_sob_curr
365 (p_org_id => v_org_id );
366 v_currency_code := l_func_curr_det.currency_code;
367 --v_precision := l_func_curr_det.precision;
368 v_precision := 0; /*bug 8537295 - for excise invoice, amount should be rounded to nearest rupee*/
369
370 Fnd_File.PUT_LINE(Fnd_File.LOG, 'v_currency_code =, v_precision = , v_cess_paid_payable =' || v_currency_code || ' : ' || v_precision || ' : ' || v_cess_paid_payable );
371
372 if v_precision is null then
373 v_precision :=0;
374 end if;
375
376 v_cess_amount := round(v_cess_amount,v_precision);
377 v_sh_cess_amount := round(v_sh_cess_amount,v_precision);/*Bug 5989740 bduvarag*/
378
379 Fnd_File.PUT_LINE(Fnd_File.LOG, 'v_cess_amount = ' || v_cess_amount);
380
381 if v_cess_amount > 0 then
382
383 -- Dr Intransit Material Value
384 Fnd_File.PUT_LINE(Fnd_File.LOG, 'before calling gl_interface for CESS ');
385
386 jai_cmn_gl_pkg.create_gl_entry
387 (p_organization_id,
388 p_currency_code,
389 0,--Credit
390 v_cess_amount, --Debit
391 p_intransit_inv_acct,
392 p_source_name,
393 p_category_name,
394 p_created_by,
395 /* Bug 4562791. Added by Lakshmi Gopalsami
396 Changed NULL to gl_accounting_date */
397 gl_accounting_date ,
398 NULL,
399 NULL,
400 NULL,
401 P_REF_10,
402 P_REF_23,
403 P_REF_24,
404 P_REF_25,
405 P_REF_26);
406
407 -- Cr CESS Payable Paid
408
409 jai_cmn_gl_pkg.create_gl_entry
410 (p_organization_id,
411 p_currency_code,
412 v_cess_amount,--Credit
413 0, --Debit
414 v_cess_paid_payable,
415 p_source_name,
416 p_category_name,
417 p_created_by,
418 /* Bug 4562791. Added by Lakshmi Gopalsami
419 Changed NULL to gl_accounting_date */
420 gl_accounting_date ,
421 NULL,
422 NULL,
423 NULL,
424 P_REF_10,
425 P_REF_23,
426 P_REF_24,
427 P_REF_25,
428 P_REF_26);
429
430 if p_fob_point =1 then -- fobpoint =(shipment)
431
432
433 -- Dr Intercompany Receivable
434 Fnd_File.PUT_LINE(Fnd_File.LOG, 'before calling gl_interface for CESS ');
435
436 jai_cmn_gl_pkg.create_gl_entry
437 (p_organization_id,
438 p_currency_code,
439 0,--Credit
440 v_cess_amount, --Debit
441 p_intercompany_rcvbl ,
442 p_source_name,
443 p_category_name,
444 p_created_by,
445 /* Bug 4562791. Added by Lakshmi Gopalsami
446 Changed NULL to gl_accounting_date */
447 gl_accounting_date ,
448 NULL,
449 NULL,
450 NULL,
451 P_REF_10,
452 P_REF_23,
453 P_REF_24,
454 P_REF_25,
455 P_REF_26
456 );
457
458 -- Cr Intercompany Payables
459
460 jai_cmn_gl_pkg.create_gl_entry
461 (p_organization_id,
462 p_currency_code,
463 v_cess_amount,--Credit
464 0, --Debit
465 p_intercompany_paybl,
466 p_source_name,
467 p_category_name,
468 p_created_by,
469 /* Bug 4562791. Added by Lakshmi Gopalsami
470 Changed NULL to gl_accounting_date */
471 gl_accounting_date ,
472 NULL,
473 NULL,
474 NULL,
475 P_REF_10,
476 P_REF_23,
477 P_REF_24,
478 P_REF_25,
479 P_REF_26
480 );
481
482 end if;
483 end if;
484 /*Bug 5989740 bduvarag start*/
485 if v_sh_cess_amount > 0 then
486 jai_cmn_gl_pkg.create_gl_entry
487 (p_organization_id,
488 p_currency_code,
489 0,--Credit
490 v_sh_cess_amount, --Debit
491 p_intransit_inv_acct,
492 p_source_name,
493 p_category_name,
494 p_created_by,
495 gl_accounting_date,
496 NULL,
497 NULL,
498 NULL,
499 P_REF_10,
500 P_REF_23,
501 P_REF_24,
502 P_REF_25,
503 P_REF_26);
504
505 -- Cr CESS Payable Paid
506
507 jai_cmn_gl_pkg.create_gl_entry
508 (p_organization_id,
509 p_currency_code,
510 v_sh_cess_amount,--Credit
511 0, --Debit
512 ln_sh_Cess_paid_payable ,
513 p_source_name,
514 p_category_name,
515 p_created_by,
516 gl_accounting_date,
517 NULL,
518 NULL,
519 NULL,
520 P_REF_10,
521 P_REF_23,
522 P_REF_24,
523 P_REF_25,
524 P_REF_26);
525
526 if p_fob_point =1 then -- fobpoint =(shipment)
527
528
529 -- Dr Intercompany Receivable
530 Fnd_File.PUT_LINE(Fnd_File.LOG, 'before calling gl_interface for CESS ');
531
532 jai_cmn_gl_pkg.create_gl_entry
533 (p_organization_id,
534 p_currency_code,
535 0,--Credit
536 v_sh_cess_amount, --Debit
537 p_intercompany_rcvbl ,
538 p_source_name,
539 p_category_name,
540 p_created_by,
541 gl_accounting_date,
542 NULL,
543 NULL,
544 NULL,
545 P_REF_10,
546 P_REF_23,
547 P_REF_24,
548 P_REF_25,
549 P_REF_26
550 );
551
552 -- Cr Intercompany Payables
553
554 jai_cmn_gl_pkg.create_gl_entry
555 (p_organization_id,
556 p_currency_code,
557 v_sh_cess_amount,--Credit
558 0, --Debit
559 p_intercompany_paybl,
560 p_source_name,
561 p_category_name,
562 p_created_by,
563 gl_accounting_date,
564 NULL,
565 NULL,
566 NULL,
567 P_REF_10,
568 P_REF_23,
569 P_REF_24,
570 P_REF_25,
571 P_REF_26
572 );
573
574 end if;
575 end if;
576 /*Bug 5989740 bduvarag*/
577 end if;
578
579 Fnd_File.PUT_LINE(Fnd_File.LOG, ' End of JA_IN_CESS_ENTRIES');
580
581 EXCEPTION
582 WHEN OTHERS THEN
583 FND_MESSAGE.SET_NAME ('JA','JAI_EXCEPTION_OCCURED');
584 FND_MESSAGE.SET_TOKEN ('JAI_PROCESS_MSG',lv_object_name ||'.Err:'||sqlerrm);
585 app_exception.raise_exception;
586 end;
587
588 -- end additions by ssumaith - bug#3817625
589 /***************************** END JA_IN_CESS_ENTRIES *******************************************************************/
590
591 /*************************************START JA_IN_CESS_ACCTG_ENTRIES************************************************ */
592
593 procedure ja_in_cess_acctg_entries(
594 p_trx_hdr_id number ,
595 p_inv_orgn_id number ,
596 p_cess_amount number ,
597 p_debit_account gl_interface.code_combination_id%type ,
598 p_credit_account gl_interface.code_combination_id%type ,
599 p_je_source_name gl_interface.user_je_source_name%type ,
600 p_je_Category_name gl_interface.user_je_category_name%type,
601 p_currency_code gl_interface.currency_Code%type ,
602 P_REFERENCE_10 varchar2 ,
603 P_REFERENCE_23 varchar2 ,
604 P_REFERENCE_24 varchar2 ,
605 P_REFERENCE_25 varchar2 ,
606 P_REFERENCE_26 varchar2
607 ) is
608 /**********************************************************************
609 CREATED BY : ssumaith
610 CREATED DATE : 11-JAN-2005
611 ENHANCEMENT BUG : 4136981
612 PURPOSE : To calculate cess amount in case of an AR transaction.
613 CALLED FROM : jai_om_rg_pkg.ja_in_rg23_part_ii_entry , jai_om_rg_pkg.pla_emtry , jai_om_rg_pkg.ja_in23d_entry
614
615 **********************************************************************/
616 lv_object_name CONSTANT VARCHAR2 (61) := 'jai_om_rg_pkg.ja_in_cess_acctg_entries';
617 begin
618
619 Fnd_File.PUT_LINE(Fnd_File.LOG, ' Before calling gl_interface for credit entry for CESS' );
620
621
622 if p_debit_account is null or p_credit_account is null then
623 Fnd_File.PUT_LINE(Fnd_File.LOG, 'Cess Accounts have not been setup in Organization Additional Information Screen - Cannot Process' ); --Added for bug#7172215
624 raise_application_error(-20107,'Cess Accounts have not been setup in Organization Additional Information Screen - Cannot Process');
625 end if;
626
627 jai_cmn_gl_pkg.create_gl_entry(
628 P_ORGANIZATION_ID => p_inv_orgn_id ,
629 P_CURRENCY_CODE => p_currency_code ,
630 P_CREDIT_AMOUNT => p_cess_amount ,
631 P_DEBIT_AMOUNT => NULL ,
632 P_CC_ID => p_credit_account ,
633 P_JE_SOURCE_NAME => p_je_source_name ,
634 P_JE_CATEGORY_NAME => p_je_Category_name ,
635 P_CREATED_BY => fnd_global.user_id ,
636 /* Bug 4562791. Added by Lakshmi Gopalsami
637 Changed NULL to gl_accounting_date */
638 P_ACCOUNTING_dATE => gl_accounting_date ,
639 P_REFERENCE_10 => p_reference_10 ,
640 P_REFERENCE_23 => p_reference_23 ,
641 P_REFERENCE_24 => p_reference_24 ,
642 P_REFERENCE_25 => p_reference_25 ,
643 P_REFERENCE_26 => p_reference_26
644 );
645
646 Fnd_File.PUT_LINE(Fnd_File.LOG, ' Before calling gl_interface for debit entry for CESS' );
647 jai_cmn_gl_pkg.create_gl_entry(
648 P_ORGANIZATION_ID => p_inv_orgn_id ,
649 P_CURRENCY_CODE => p_currency_code ,
650 P_CREDIT_AMOUNT => NULL ,
651 P_DEBIT_AMOUNT => p_cess_amount ,
652 P_CC_ID => p_debit_account ,
653 P_JE_SOURCE_NAME => p_je_source_name ,
654 P_JE_CATEGORY_NAME => p_je_Category_name ,
655 P_CREATED_BY => fnd_global.user_id ,
656 /* Bug 4562791. Added by Lakshmi Gopalsami
657 Changed NULL to gl_accounting_date */
658 P_ACCOUNTING_dATE => gl_accounting_date ,
659 P_REFERENCE_10 => p_reference_10 ,
660 P_REFERENCE_23 => p_reference_23 ,
661 P_REFERENCE_24 => p_reference_24 ,
662 P_REFERENCE_25 => p_reference_25 ,
663 P_REFERENCE_26 => p_reference_26
664 );
665
666 EXCEPTION
667 WHEN OTHERS THEN
668 FND_MESSAGE.SET_NAME ('JA','JAI_EXCEPTION_OCCURED');
669 FND_MESSAGE.SET_TOKEN ('JAI_PROCESS_MSG',lv_object_name ||'.Err:'||sqlerrm);
670 app_exception.raise_exception;
671 end;
672
673
674 /********************************************************************************************************************** */
675
676 /************************************START JA_IN_OM_CESS_REGISTER_ENTRIES***********************************************/
677 procedure ja_in_om_cess_register_entries(p_delivery_id JAI_OM_WSH_LINES_ALL.DELIVERY_ID%TYPE ,
678 p_source_type JAI_CMN_RG_OTHERS.SOURCE_TYPE%TYPE ,
679 p_source_name JAI_CMN_RG_OTHERS.SOURCE_REGISTER%TYPE ,
680 p_source_id JAI_CMN_RG_OTHERS.SOURCE_REGISTER_ID%TYPE ,
681 p_register_type VARCHAR2 ,
682 p_cess_amount OUT NOCOPY NUMBER ,
683 p_delivery_detail_id JAI_OM_WSH_LINES_ALL.DELIVERY_DETAIL_ID%TYPE DEFAULT NULL, -- added, Bug 4724137
684 p_cess_type VARCHAR2/*Bug 5989740 bduvarag*/
685 )
686 /**********************************************************************
687 CREATED BY : ssumaith
688 CREATED DATE : 11-JAN-2005
689 ENHANCEMENT BUG : 4136981
690 PURPOSE : To calculate cess amount in case of an OM transaction.
691 CALLED FROM : jai_om_rg_pkg.ja_in_rg23_part_ii_entry , jai_om_rg_pkg.pla_emtry , jai_om_rg_pkg.ja_in23d_entry
692
693 **********************************************************************/
694 is /* Added the having clause to the following cursor - ssumaith bug#4185392
695 This was done because if there is no cess amount, there is no need to call the insert row procedure
696 */
697 CURSOR c_tax_type_rec IS
698 SELECT jtc.tax_type , sum(jsptl.func_tax_amount) tax_amount --rchandan for bug#4388950 /*Removed rounding factor by mmurtuza for bug 16534065*/
699 FROM JAI_OM_WSH_LINE_TAXES jsptl ,
700 JAI_CMN_TAXES_ALL jtc
701 WHERE jtc.tax_id = jsptl.tax_id
702 --Modified for bug5747126
703 AND delivery_detail_id = p_delivery_detail_id
704 AND (( upper(jtc.tax_type) IN (jai_constants.tax_type_cvd_edu_cess,JAI_CONSTANTS.TAX_TYPE_EXC_EDU_CESS) and p_cess_type = 'EXC') --Date 12/03/2007 by SACSETHI for bug#5907436
705 OR
706 ( upper(jtc.tax_type) IN (jai_constants.tax_type_sh_cvd_edu_cess,JAI_CONSTANTS.TAX_TYPE_sh_EXC_EDU_CESS) and p_cess_type = 'SH') )
707 GROUP BY jtc.tax_type
708 HAVING SUM(jsptl.func_tax_amount) <> 0;
709 -- added, Harshita for Bug 4714518
710 /*AND
711 (
712 ( delivery_detail_id = p_delivery_detail_id AND p_source_type = 3)
713 OR
714 ( delivery_detail_id IN
715 (SELECT delivery_detail_id
716 FROM ja_in_so_picking_lines jspl
717 WHERE jspl.delivery_id = p_delivery_id
718 )
719 AND p_source_type <> 3
720 )
721 )*/
722 -- ended, Harshita for Bug 4714518
723
724 -- foll cursor added by ssumaith - bug# 5747126 - one off
725 CURSOR c_tax_type_for_delivery_rec IS
726 SELECT jtc.tax_type , sum(jsptl.func_tax_amount) tax_amount /*Removed rounding factor by mmurtuza for bug 16534065*/
727 FROM JAI_OM_WSH_LINE_TAXES jsptl ,
728 JAI_OM_WSH_LINES_ALL jspl ,
729 JAI_CMN_TAXES_ALL jtc ,
730 JAI_INV_ITM_SETUPS jmsi /* Added by Ramananda for bug#5912620*/
731 WHERE jtc.tax_id = jsptl.tax_id
732 AND jspl.delivery_id = p_delivery_id
733 AND jspl.delivery_detail_id = jsptl.delivery_detail_id
734 AND (( upper(jtc.tax_type) IN (jai_constants.tax_type_cvd_edu_cess,JAI_CONSTANTS.TAX_TYPE_EXC_EDU_CESS) and p_cess_type = 'EXC') --Date 12/03/2007 by SACSETHI for bug#5907436
735 OR
736 ( upper(jtc.tax_type) IN (jai_constants.tax_type_sh_cvd_edu_cess,JAI_CONSTANTS.TAX_TYPE_sh_EXC_EDU_CESS) and p_cess_type = 'SH') )
737 /* Added for bug#5912620, Starts */
738 AND jmsi.inventory_item_id = jspl.inventory_item_id
739 AND jmsi.organization_id = jspl.organization_id
740 AND jmsi.excise_flag = 'Y'
741 /* Added for bug#5912620, Endseft */
742 GROUP BY jtc.tax_type
743 HAVING SUM(jsptl.func_tax_amount) <> 0;
744
745 -- ends additions by ssumaith - bug# 5747126 - one off
746
747 lv_process_flag VARCHAR2(2);
748 lv_process_msg VARCHAR2(1000);
749 ln_Cess_amount NUMBER := 0;
750
751 BEGIN
752
753 IF p_source_type = 3 THEN /* For RG23D */ /* if condition added by ssumaith - bug# 5747126 */
754
755 FOR tax_type_rec IN c_tax_type_rec
756 LOOP
757 ln_Cess_amount := ln_Cess_amount + nvl(tax_type_rec.tax_amount,0);
758
759 Fnd_File.PUT_LINE(Fnd_File.LOG, 'before calling jai_Rg_others_pkg.insert_row');
760
761 jai_cmn_rg_others_pkg.insert_row(
762 P_SOURCE_TYPE => p_source_type ,
763 P_SOURCE_NAME => p_source_name ,
764 P_SOURCE_ID => p_source_id ,
765 P_TAX_TYPE => tax_type_rec.tax_type ,
766 DEBIT_AMT => tax_type_rec.tax_amount,
767 CREDIT_AMT => NULL ,
768 P_PROCESS_FLAG => lv_process_flag ,
769 P_PROCESS_MSG => lv_process_msg
770 );
771
772 Fnd_File.PUT_LINE(Fnd_File.LOG, 'after calling jai_Rg_others_pkg.insert_row with P_PROCESS_FLAG => ' || lv_process_flag);
773 Fnd_File.PUT_LINE(Fnd_File.LOG, 'after calling jai_Rg_others_pkg.insert_row with P_PROCESS_MSG => ' || lv_process_msg);
774 IF lv_process_flag <> jai_constants.successful THEN
775 Fnd_File.PUT_LINE(Fnd_File.LOG, 'Error Encountered is ' ||lv_process_msg); --Added for bug#7172215
776 raise_application_error(-20110,'Error Encountered is ' || lv_process_msg);
777 END IF;
778
779 END LOOP;
780
781 p_cess_amount := ln_Cess_amount;
782
783 ELSIF p_source_type in (1,2) THEN /* PLA and RG23Part II */
784
785 /* elsif condition and the code till end if below added by ssumaith - bug# 5747126 */
786
787 ln_Cess_amount := 0;
788
789 FOR tax_type_rec IN c_tax_type_for_delivery_rec
790 LOOP
791 ln_Cess_amount := ln_Cess_amount + nvl(tax_type_rec.tax_amount,0);
792
793 Fnd_File.PUT_LINE(Fnd_File.LOG, 'before calling jai_Rg_others_pkg.insert_row');
794
795 jai_cmn_rg_others_pkg.insert_row(
796 P_SOURCE_TYPE => p_source_type ,
797 P_SOURCE_NAME => p_source_name ,
798 P_SOURCE_ID => p_source_id ,
799 P_TAX_TYPE => tax_type_rec.tax_type ,
800 DEBIT_AMT => tax_type_rec.tax_amount,
801 CREDIT_AMT => NULL ,
802 P_PROCESS_FLAG => lv_process_flag ,
803 P_PROCESS_MSG => lv_process_msg
804 );
805
806 Fnd_File.PUT_LINE(Fnd_File.LOG, 'after calling jai_Rg_others_pkg.insert_row with P_PROCESS_FLAG => ' || lv_process_flag);
807 Fnd_File.PUT_LINE(Fnd_File.LOG, 'after calling jai_Rg_others_pkg.insert_row with P_PROCESS_MSG => ' || lv_process_msg);
808 IF lv_process_flag <> jai_constants.successful THEN
809 Fnd_File.PUT_LINE(Fnd_File.LOG, 'Error Encountered is ' ||lv_process_msg); --Added for bug#7172215
810 raise_application_error(-20110,'Error Encountered is ' || lv_process_msg);
811 END IF;
812
813 END LOOP;
814
815 p_cess_amount := ln_Cess_amount;
816
817 END IF;
818
819 END ja_in_om_cess_register_entries;
820
821 /**********************************************************************************************************************/
822 PROCEDURE ja_in_ar_cess_register_entries(p_customer_trx_id JAI_AR_TRXS.CUSTOMER_TRX_ID%TYPE ,
823 p_source_type JAI_CMN_RG_OTHERS.SOURCE_TYPE%TYPE ,
824 p_source_name JAI_CMN_RG_OTHERS.SOURCE_REGISTER%TYPE ,
825 p_source_id JAI_CMN_RG_OTHERS.SOURCE_REGISTER_ID%TYPE ,
826 p_register_type VARCHAR2 ,
827 p_cess_amount OUT NOCOPY NUMBER ,
828 p_delivery_detail_id JAI_OM_WSH_LINES_ALL.DELIVERY_DETAIL_ID%TYPE DEFAULT NULL, -- added for Bug 4724137
829 p_cess_type VARCHAR2/*Bug 5989740 bduvarag*/
830 )
831
832 /**********************************************************************
833 CREATED BY : ssumaith
834 CREATED DATE : 11-JAN-2005
835 ENHANCEMENT BUG : 4136981
836 PURPOSE : To calculate cess amount in case of an AR transaction.
837 CALLED FROM : jai_om_rg_pkg.ja_in_rg23_part_ii_entry , jai_om_rg_pkg.pla_emtry , jai_om_rg_pkg.ja_in23d_entry
838 ====================================================================================
839 Change History
840 ====================================================================================
841 1. 23-Aug-2005 Aiyer - Bug# 4541303 (Forward porting for the 11.5 bug 4538315) 120.4
842 For a manual AR invoice with more than one line, the cess amount was being hit for the whole of the
843 invoice amount for each of the lines.
844
845 Code changes are done in the package jai_om_rg_pkg as well this trigger.
846
847 Code changes done in the package include calculating the cess amount for the current customer trx line id.
848
849 Code changes done in the trigger include sending the customer trx line id when pla is hit . This is inline
850 with the way JAI_CMN_RG_23AC_II_TRXS works.
851
852 Dependency Due to this bug:-
853 jai_jai_t.sql (120.1)
854 2 15-jul-2009 vkaranam for bug#8679064,file version 120.8.12000000.10
855 Issue:
856 MANUAL AR invoice is not passing Cess,sh cess entries for RG23 part II.
857 Fix:
858 While calling the procedure ja_in_ar_cess_register_entries ,p_customer_trx_id value is been passed
859 as p_transasction_hdr_id i.e customer_trx_line_id for AR.7/15/20097/15/2009
860
861 **********************************************************************/
862
863 is
864 /* Added the having clause to the following cursor - ssumaith bug#4185392
865 This was done because if there is no cess amount, there is no need to call the insert row procedure
866 */
867 CURSOR c_tax_type_rec IS
868 SELECT jtc.tax_type , sum(jrctl.func_tax_amount) tax_amount --added round for bug#9539924 /*Removed rounding factor by mmurtuza for bug 16534065*/
869 FROM JAI_AR_TRX_TAX_LINES jrctl ,
870 JAI_CMN_TAXES_ALL jtc
871 WHERE jtc.tax_id = jrctl.tax_id
872 AND link_to_cust_trx_line_id = p_customer_trx_id -- added, aiyer for Bug 4541303 /*Bug 5989740 bduvarag*/
873 AND (( upper(jtc.tax_type) IN (jai_constants.tax_type_cvd_edu_cess,JAI_CONSTANTS.TAX_TYPE_EXC_EDU_CESS) and p_cess_type = 'EXC')
874 OR
875 ( upper(jtc.tax_type) IN (jai_constants.tax_type_sh_cvd_edu_cess,JAI_CONSTANTS.TAX_TYPE_sh_EXC_EDU_CESS) and p_cess_type = 'SH') )
876
877
878 GROUP BY jtc.tax_type
879 HAVING SUM(jrctl.func_tax_amount) <> 0;
880
881 lv_process_flag varchar2(2);
882 lv_process_msg varchar2(1000);
883 ln_Cess_amount number := 0;
884 lv_object_name CONSTANT VARCHAR2 (61) := 'jai_om_rg_pkg.ja_in_ar_cess_register_entries';
885
886 begin
887
888 For tax_type_rec in c_tax_type_rec
889 Loop
890
891 ln_Cess_amount := ln_Cess_amount + nvl(tax_type_rec.tax_amount,0);
892 jai_cmn_rg_others_pkg.insert_row(
893 P_SOURCE_TYPE => p_source_type ,
894 P_SOURCE_NAME => p_source_name ,
895 P_SOURCE_ID => p_source_id ,
896 P_TAX_TYPE => tax_type_rec.tax_type ,
897 DEBIT_AMT => tax_type_rec.tax_amount,
898 CREDIT_AMT => NULL ,
899 P_PROCESS_FLAG => lv_process_flag ,
900 P_PROCESS_MSG => lv_process_msg
901 );
902 End Loop;
903 p_cess_amount := ln_Cess_amount;
904 EXCEPTION
905 WHEN OTHERS THEN
906 p_cess_amount:=null;
907 FND_MESSAGE.SET_NAME ('JA','JAI_EXCEPTION_OCCURED');
908 FND_MESSAGE.SET_TOKEN ('JAI_PROCESS_MSG',lv_object_name ||'.Err:'||sqlerrm);
909 app_exception.raise_exception;
910 end ja_in_ar_cess_register_entries;
911
912 /**********************************************************************************************************************/
913 procedure ja_in_cess_register_entries(
914 p_register_id JAI_CMN_RG_23AC_II_TRXS.register_id%type ,
915 p_register_type varchar2 ,
916 p_inv_orgn_id JAI_CMN_INVENTORY_ORGS.organization_id%type ,
917 p_je_source_name gl_interface.USER_JE_SOURCE_NAME%type ,
918 p_je_category_name gl_interface.USER_JE_CATEGORY_NAME%type ,
919 p_source_type JAI_CMN_RG_OTHERS.source_type%type ,
920 p_currency_code gl_interface.currency_code%type ,
921 p_transaction_hdr_id Number ,
922 p_debit_account Number ,
923 p_Credit_account Number ,
924 p_cess_amount OUT NOCOPY number ,
925 p_cess_type varchar2 , /*Bug 5989740 bduvarag*/
926 P_REFERENCE_10 varchar2 default Null ,
927 P_REFERENCE_23 varchar2 default Null ,
928 P_REFERENCE_24 varchar2 default Null ,
929 P_REFERENCE_25 varchar2 default Null ,
930 P_REFERENCE_26 varchar2 default Null
931 )
932
933 /**********************************************************************
934 CREATED BY : ssumaith
935 CREATED DATE : 11-JAN-2005
936 ENHANCEMENT BUG : 4136981
937 PURPOSE : To paass register and accounting entries for education cess
938 CALLED FROM : jai_om_rg_pkg.ja_in_rg23_part_ii_entry , jai_om_rg_pkg.pla_emtry , jai_om_rg_pkg.ja_in23d_entry
939
940 **********************************************************************/
941 IS
942 cursor c_delivery_details is
943 select delivery_id , organization_id
944 from JAI_OM_WSH_LINES_ALL
945 where delivery_detail_id = p_transaction_hdr_id;
946
947 cursor c_customer_trx_details is
948 select customer_trx_id
949 from JAI_AR_TRX_LINES
950 where customer_trx_line_id = p_transaction_hdr_id;
951
952 ln_delivery_id JAI_OM_WSH_LINES_ALL.delivery_id%type;
953 ln_customer_trx_id JAI_AR_TRX_LINES.customer_trx_id%type;
954 ln_Cess_amount number;
955 ln_inv_orgn_id number;
956 ln_header_id number;
957 lv_source_name varchar2(20);
958 lv_object_name CONSTANT VARCHAR2 (61) := 'jai_om_rg_pkg.ja_in_cess_register_entries';
959
960 begin
961
962 open c_delivery_details;
963 fetch c_delivery_details into ln_delivery_id , ln_inv_orgn_id;
964 close c_delivery_details;
965
966 ln_inv_orgn_id := p_inv_orgn_id;
967
968 /*
969
970 if called from Excise Invoice Generation program the values for the parameters are as follows:
971
972 p_source_name := 'Register India' ;
973 p_category_name := 'Register India' ;
974 p_transaction_hdr_id := It has the delivery detail id
975
976 Based on the delivery detail id , pick up the delivery id, organization id and get the taxes from JAI_OM_WSH_LINE_TAXES
977
978 If called from AR invoice completion the values for the parameters are as follows.
979
980 p_source_name := 'Receivables India'
981 p_category_name := 'RG Register Data Entry'
982 p_transaction_hdr_id := customer_trx_line_id from JAI_AR_TRX_LINES table
983
984 Based on the customer_trx_line_id , pick up the customer_trx_id , organization id and get the taxes from JAI_AR_TRX_TAX_LINES
985
986 */
987 if p_source_type = 2 then
988 ln_customer_trx_id := p_transaction_hdr_id;
989 lv_source_name := 'PLA';
990 elsif p_source_type = 1 then
991
992 /*
993 In the case of RG23 Part II , the customer trx line id was being passed .
994 In case of PLA customer trx id was being passed from manual ar invoice completion trigger
995 */
996 open c_customer_trx_details;
997 fetch c_customer_trx_details into ln_customer_trx_id;
998 close c_customer_trx_details;
999
1000 if p_Register_type = 'A' then
1001 lv_source_name := 'RG23A_P2';
1002 elsif p_Register_type = 'C' then
1003 lv_source_name := 'RG23C_P2';
1004 end if;
1005 elsif p_source_type = 3 then
1006 /* for RG23D */
1007 ln_customer_trx_id := p_transaction_hdr_id;/*Bug 5989740 bduvarag*/
1008 lv_source_name := 'RG23D';
1009 end if;
1010
1011 if p_je_source_name = 'Register India' and p_je_category_name = 'Register India' then
1012 /*
1013 Do all things needed to populate records into the register table , picking up taxes from the JAI_OM_WSH_LINE_TAXES table
1014 */
1015 ln_header_id := ln_delivery_id;
1016 ja_in_om_cess_register_entries(p_delivery_id => ln_delivery_id ,
1017 p_source_type => p_source_type ,
1018 p_source_name => lv_source_name ,
1019 p_source_id => p_register_id ,
1020 p_register_type => p_register_type ,
1021 p_cess_amount => ln_Cess_amount ,
1022 p_delivery_detail_id => p_transaction_hdr_id,/*added by vkaranam for bug #6159579*/
1023 p_cess_type => p_cess_type /*Bug 5989740 bduvarag*/
1024 );
1025
1026
1027 elsif p_je_source_name = 'Receivables India' and p_je_category_name = jai_constants.je_category_rg_entry then --replaced RG Register Data Entry, csahoo for bug#6155839
1028 /*
1029 Do all things needed to populate records into the register table , picking up taxes from the ja_in_customer_trx_tax_lines table
1030 */
1031 ln_header_id := ln_customer_trx_id;
1032
1033 ja_in_ar_cess_register_entries(p_customer_trx_id => p_transaction_hdr_id ,-- ln_customer_trx_id passed p_transaction_hdr_id for bug#8679064 ,
1034 p_source_type => p_source_type ,
1035 p_source_name => lv_source_name ,
1036 p_source_id => p_register_id ,
1037 p_register_type => p_register_type ,
1038 p_cess_amount => ln_Cess_amount,
1039 p_delivery_detail_id => NULL,
1040 p_cess_type => p_cess_type/*Bug 5989740 bduvarag*/
1041 );
1042 end if;
1043
1044 Fnd_File.PUT_LINE(Fnd_File.LOG, ' Before calling ja_in_cess_acctg_entries' );
1045 /*
1046 -- MXYZ No Cess A/c in trading scenario
1047 */
1048 if p_source_type <> 3 then
1049 IF NVL(ln_Cess_amount,0) <> 0 THEN --Added for bug#7172215. accounting entries should happen only if amount non zero
1050 ja_in_cess_acctg_entries(
1051 ln_header_id ,
1052 ln_inv_orgn_id ,
1053 ln_Cess_amount ,
1054 p_debit_account ,
1055 p_credit_account ,
1056 p_je_source_name ,
1057 p_je_Category_name,
1058 p_currency_code ,
1059 P_REFERENCE_10 ,
1060 P_REFERENCE_23 ,
1061 P_REFERENCE_24 ,
1062 P_REFERENCE_25 ,
1063 P_REFERENCE_26
1064 );
1065 End If;
1066 Fnd_File.PUT_LINE(Fnd_File.LOG, ' after calling ja_in_cess_acctg_entries' );
1067 end if;
1068
1069 p_cess_amount := ln_Cess_amount;
1070
1071 EXCEPTION
1072 WHEN OTHERS THEN
1073 p_cess_amount := null;
1074 FND_MESSAGE.SET_NAME ('JA','JAI_EXCEPTION_OCCURED');
1075 FND_MESSAGE.SET_TOKEN ('JAI_PROCESS_MSG',lv_object_name ||'.Err:'||sqlerrm);
1076 app_exception.raise_exception;
1077 end;
1078
1079 /**********************************************************************************************************************/
1080
1081
1082 /************************************** JA_IN_ACCOUNTING_ENTRIES **************************************************
1083 Sl No. Date Author and Details
1084 1. 11/07/2002 This PROCEDURE IS created BY Aparajita On 03/08/2002 FOR bug # 2496388 .
1085
1086 This was done TO implement the internal sales ORDER accounting entries.
1087 Procedures ja_in_pla_entry AND ja_in_rg23_part_II_entry
1088 are calling this PROCEDURE FOR creating accounting entries.
1089 Logic :
1090 For non internal sales order :
1091 Debit and Credit accounts as already found out in the respective procedures and
1092 passed onto this procedure
1093
1094 FOR internal sales order :
1095 - Debit Inter_org Receivable Account
1096 - Credit Excise Paid/Payable Account
1097
1098 if transfer type is in-transit then
1099 - Debit Intransit Account
1100 - Credit Inter_org Payable Account
1101 end if;
1102
1103 2. 01/04/2003 Aparajita for bug#2848921
1104 As per the logic above, for internal sales order the first two accounting entries are not
1105 required. But as per this bug, for ISO these two accounting entries should also get passed.
1106
1107 The intransit_inv_account was picked from mtl_interorg_parameters through cursor
1108 c_get_iso_accounts for the combination of from and to organization. As per the requirement
1109 now, it should be picked from mtl_parameters for the receiving organization. Added cursor
1110 c_get_intransit_inv_account.
1111
1112 Added the check of fob_point along with in transit type.
1113
1114 3. 07/04/2003 Aparajita for bug#2893284
1115 Removed the cursor c_get_intransit_inv_account as the requiremnts are that it should be
1116 picked up from mtl_interorg_parameters, so using cursor c_get_iso_accounts.
1117
1118 Removed the cursor c_get_intransit_type, as it was redundant and used c_get_iso_accounts.
1119
1120 4. 13/05/2003 Nagaraj.s for Bug#2912007
1121 --Changed by Nagaraj.s for Bug2912007.
1122 --The Following is the New Accounting Entries as Required in case of ISO.
1123 --***************************************************************************************************
1124 --For Intransit Transfers(v_intransit_type=2)
1125 --If FOB Point =1(Shipment)
1126 Accounting Entries are : 1. Dr Inter Org Receivable, Cr Excise Paid Payable
1127 2. Dr Intransit Inventory Account, Cr Inter Org Payable Account
1128
1129 --If FOB Point =2(Receipt)
1130 Accounting Entries are : 1. Dr Intransit Inventory Account, Cr Excise Paid Payable Account
1131 --***************************************************************************************************
1132 --For Direct Transfers(v_intransit_type=1)
1133 Accounting Entries are : 1. Dr Inter Org Receivable, Cr Excise Paid Payable Account
1134
1135 5. 20/08/2004 ssumaith - bug#3817625 file version 115.1
1136
1137 Created an internal procedure JA_IN_CESS_ENTRIES
1138
1139 -- A/c Entries for CESS needs to be passed as follows:
1140 -- Dr Intransit Material Value
1141 -- Cr CESS Payable Paid
1142 -- It will be called from ja_in_pla_entry and ja_in_rg23_part_ii_entry because only they have the
1143 -- amount impact for excise registers
1144
1145 --If the FOB point is set to SHIPMENT, the following entry also needs to be passed :
1146
1147 -- Intercompany Receivable Cess amt
1148 -- Intercompany Payables cess amt
1149
1150 6. 11/01/2005 ssumaith - bug# 4136981
1151
1152 Coded for accounting entries impact in excise cess and cvd cess when called from manual AR invoice
1153 completion and excise invoice generation program.
1154 Internal procedures have been created which are called from ja_in_rg23_part_ii_entry , ja_in_pla_entry
1155 and Ja_In_Rg23d_Entry procedures
1156
1157 This fix does not introduce dependency on this object , but this patch cannot be sent alone to the CT
1158 because it relies on the alter done and the new tables created as part of the education cess enhancement
1159 bug# 4146708 creates the objects
1160
1161 All future code changes on this object should have reference to bug#4146708 as dependency
1162
1163
1164
1165 ************************************* JA_IN_ACCOUNTING_ENTRIES **************************************************/
1166 PROCEDURE ja_in_accounting_entries
1167 (
1168 p_org_id NUMBER,
1169 p_location_id NUMBER,
1170 p_currency_code VARCHAR2,
1171 p_excise_amount NUMBER,
1172 p_source_name VARCHAR2,
1173 p_category_name VARCHAR2,
1174 p_created_by NUMBER,
1175 p_delivery_detail_id NUMBER,
1176 p_non_iso_credit_account_id NUMBER,
1177 p_non_iso_debit_account_id NUMBER,
1178 P_REF_10 VARCHAR2 DEFAULT NULL ,-- added by sriram - bug # 2769440
1179 P_REF_23 VARCHAR2 DEFAULT NULL ,-- added by sriram - bug # 2769440
1180 P_REF_24 VARCHAR2 DEFAULT NULL ,-- added by sriram - bug # 2769440
1181 P_REF_25 VARCHAR2 DEFAULT NULL ,-- added by sriram - bug # 2769440
1182 P_REF_26 VARCHAR2 DEFAULT NULL -- added by sriram - bug # 2769440
1183 )
1184 IS
1185 v_order_header_id WSH_DELIVERY_DETAILS.SOURCE_HEADER_ID%TYPE;
1186 v_order_line_id WSH_DELIVERY_DETAILS.SOURCE_LINE_ID%TYPE;
1187 v_order_source_id OE_ORDER_HEADERS_ALL.ORDER_SOURCE_ID%TYPE;
1188 v_intransit_type MTL_SHIPPING_NETWORK_VIEW.INTRANSIT_TYPE%TYPE;
1189 v_fob_point MTL_SHIPPING_NETWORK_VIEW.FOB_POINT%TYPE;
1190 v_interorg_receivables_account MTL_INTERORG_PARAMETERS.INTERORG_RECEIVABLES_ACCOUNT%TYPE;
1191 v_interorg_payables_account MTL_INTERORG_PARAMETERS.INTERORG_PAYABLES_ACCOUNT%TYPE;
1192 v_intransit_inv_account MTL_INTERORG_PARAMETERS.INTRANSIT_INV_ACCOUNT%TYPE;
1193 v_from_organization_id WSH_DELIVERY_DETAILS.ORGANIZATION_ID%TYPE;
1194 v_to_organization_id WSH_DELIVERY_DETAILS.ORGANIZATION_ID%TYPE;
1195 v_excise_rcvble_account JAI_CMN_INVENTORY_ORGS.EXCISE_RCVBLE_ACCOUNT%TYPE;
1196 v_requisition_header_id PO_REQUISITION_LINES_ALL.REQUISITION_HEADER_ID%TYPE;
1197 v_requisition_line_id PO_REQUISITION_LINES_ALL.REQUISITION_LINE_ID%TYPE;
1198 ln_src_excise_23d_account JAI_CMN_INVENTORY_ORGS.EXCISE_23D_ACCOUNT%TYPE;
1199 ln_dest_excise_23d_account JAI_CMN_INVENTORY_ORGS.EXCISE_23D_ACCOUNT%TYPE;
1200 ln_dest_excise_rcvble_account JAI_CMN_INVENTORY_ORGS.EXCISE_RCVBLE_ACCOUNT%TYPE;
1201 lv_object_name CONSTANT VARCHAR2 (61) := 'jai_om_rg_pkg.ja_in_accounting_entries';
1202
1203
1204 -- get the delivery details
1205 CURSOR c_get_delivery_details IS
1206 SELECT source_header_id, source_line_id
1207 FROM wsh_delivery_details
1208 WHERE delivery_detail_id = p_delivery_detail_id;
1209
1210 -- cursor to get order source id, when 10 this means that it is an internal sales order(iso)
1211 -- this also gets other details to link to requisition side and the from org id.
1212 CURSOR c_get_order_details(p_header_id NUMBER, p_line_id NUMBER) IS
1213 SELECT ship_from_org_id, order_source_id, source_document_id, source_document_line_id
1214 FROM oe_order_lines_all
1215 WHERE header_id = p_header_id
1216 AND line_id = p_line_id;
1217
1218 -- get the to organization id from the requisition details
1219 CURSOR c_get_to_organization(p_requisition_header_id NUMBER, p_requisition_line_id NUMBER) IS
1220 SELECT destination_organization_id , deliver_to_location_id /* deliver_to_location_id added by ssumaith - to handle trading to trading ISO */
1221 FROM po_requisition_lines_all
1222 WHERE requisition_header_id = p_requisition_header_id
1223 AND requisition_line_id = p_requisition_line_id;
1224
1225
1226 CURSOR c_get_iso_accounts(p_from_org_id NUMBER, p_to_org_id NUMBER) IS
1227 SELECT intransit_type, fob_point, interorg_receivables_account, interorg_payables_account, intransit_inv_account
1228 FROM mtl_interorg_parameters
1229 WHERE from_organization_id = p_from_org_id
1230 AND to_organization_id = p_to_org_id;
1231
1232 CURSOR debit_account_cur( cp_organization_id JAI_CMN_INVENTORY_ORGS.ORGANIZATION_ID%TYPE,
1233 cp_location_id JAI_CMN_INVENTORY_ORGS.LOCATION_ID%TYPE
1234 ) IS
1235 SELECT excise_rcvble_account ,
1236 excise_23d_account,
1237 excise_in_rg23d,
1238 Trading,
1239 Manufacturing
1240 FROM JAI_CMN_INVENTORY_ORGS
1241 WHERE organization_id = cp_organization_id
1242 AND location_id = cp_location_id;
1243
1244
1245 CURSOR c_delivery_cur (cp_delivery_detail_id JAI_OM_WSH_LINES_ALL.DELIVERY_DETAIL_ID%TYPE) IS
1246 SELECT delivery_id
1247 FROM JAI_OM_WSH_LINES_ALL
1248 WHERE delivery_detail_id = cp_delivery_detail_id;
1249
1250 CURSOR c_cess_amount(cp_delivery_id number) is
1251 SELECT sum(a.tax_amount)
1252 FROM JAI_OM_WSH_LINE_TAXES a, JAI_CMN_TAXES_ALL b
1253 WHERE delivery_detail_id in
1254 (SELECt delivery_detail_id
1255 FROM JAI_OM_WSH_LINES_ALL
1256 WHERE delivery_id = cp_delivery_id
1257 )
1258 AND a.tax_id = b.tax_id
1259 AND upper(b.tax_type) in (jai_constants.TAX_TYPE_CVD_EDU_CESS,jai_constants.TAX_TYPE_EXC_EDU_CESS);
1260
1261
1262
1263 ln_to_location_id PO_REQUISITION_LINES_ALL.DELIVER_TO_LOCATION_ID%TYPE;
1264 lv_dest_intransit_type MTL_INTERORG_PARAMETERS.INTRANSIT_TYPE%TYPE;
1265 ln_dest_fob_point MTL_INTERORG_PARAMETERS.FOB_POINT%TYPE;
1266 ln_dest_interorg_rcvbles_acc MTL_INTERORG_PARAMETERS.INTERORG_RECEIVABLES_ACCOUNT%TYPE;
1267 ln_dest_interorg_payables_acc MTL_INTERORG_PARAMETERS.INTERORG_PAYABLES_ACCOUNT%TYPE;
1268 ln_dest_intransit_inv_account MTL_INTERORG_PARAMETERS.INTRANSIT_INV_ACCOUNT%TYPE;
1269 ln_src_excise_in_rg23d JAI_CMN_INVENTORY_ORGS.EXCISE_IN_RG23D%TYPE;
1270 ln_dest_excise_in_rg23d JAI_CMN_INVENTORY_ORGS.EXCISE_IN_RG23D%TYPE;
1271 lv_source_trading JAI_CMN_INVENTORY_ORGS.TRADING%TYPE;
1272 lv_source_manufacturing JAI_CMN_INVENTORY_ORGS.MANUFACTURING%TYPE;
1273 lv_dest_trading JAI_CMN_INVENTORY_ORGS.TRADING%TYPE;
1274 lv_dest_manufacturing JAI_CMN_INVENTORY_ORGS.MANUFACTURING%TYPE;
1275 ln_debit_acc GL_CODE_COMBINATIONS.CODE_COMBINATION_ID%TYPE;
1276 ln_credit_acc GL_CODE_COMBINATIONS.CODE_COMBINATION_ID%TYPE;
1277 ln_cess_amount JAI_CMN_RG_OTHERS.DEBIT%TYPE;
1278 ln_delivery_id JAI_OM_WSH_LINES_ALL.DELIVERY_ID%TYPE;
1279
1280 BEGIN
1281
1282 -- get the details of the delivery, the oreder header id
1283 OPEN c_get_delivery_details;
1284 FETCH c_get_delivery_details INTO v_order_header_id, v_order_line_id ;
1285 CLOSE c_get_delivery_details;
1286
1287 OPEN c_delivery_cur (p_delivery_detail_id);
1288 FETCH c_delivery_cur INTO ln_delivery_id;
1289 CLOSE c_delivery_cur ;
1290
1291 OPEN c_cess_amount( ln_delivery_id );
1292 FETCH c_cess_amount INTO ln_cess_amount;
1293 CLOSE c_cess_amount;
1294
1295 -- get the source of the order, 10 means internal order.
1296 OPEN c_get_order_details(v_order_header_id, v_order_line_id);
1297 FETCH c_get_order_details INTO
1298 v_from_organization_id, v_order_source_id, v_requisition_header_id, v_requisition_line_id;
1299 CLOSE c_get_order_details;
1300
1301 Fnd_File.PUT_LINE(Fnd_File.LOG, ' in the gl_interface procedure with values as follows' );
1302 Fnd_File.PUT_LINE(Fnd_File.LOG, ' v_order_header_id='||v_order_header_id||', v_order_line_id='|| v_order_line_id );
1303 Fnd_File.PUT_LINE(Fnd_File.LOG, ' v_from_organization_id='||v_from_organization_id||', v_order_source_id='||v_order_source_id||', v_requisition_header_id='||v_requisition_header_id||', v_requisition_line_id='||v_requisition_line_id);
1304
1305 Fnd_File.PUT_LINE(Fnd_File.LOG, ' p_non_iso_credit_account_id ='||p_non_iso_credit_account_id|| 'and p_non_iso_debit_account_id ='||p_non_iso_debit_account_id);
1306
1307
1308 -- IF NVL(v_order_source_id, 0) <> 10 THEN, commented by Aparajita for bug # 2848921
1309
1310 -- not an internal order
1311
1312 OPEN debit_account_cur(v_from_organization_id , p_location_id);
1313 FETCH debit_account_cur INTO v_excise_rcvble_account , ln_src_excise_23d_account, ln_src_excise_in_rg23d, lv_source_trading, lv_source_manufacturing;
1314 CLOSE debit_account_cur;
1315
1316 Fnd_File.PUT_LINE(Fnd_File.LOG, ' v_to_organization_id = ' || v_to_organization_id );
1317 Fnd_File.PUT_LINE(Fnd_File.LOG, ' v_intransit_type, v_fob_point ' || v_intransit_type || ' , ' || v_fob_point);
1318 Fnd_File.PUT_LINE(Fnd_File.LOG, ' v_excise_rcvble_account , v_intransit_inv_account ' || v_excise_rcvble_account || ', ' || v_intransit_inv_account );
1319
1320 OPEN debit_account_cur(v_to_organization_id , ln_to_location_id );
1321 FETCH debit_account_cur INTO ln_dest_excise_rcvble_account , ln_dest_excise_23d_account , ln_dest_excise_in_rg23d ,lv_dest_trading, lv_dest_manufacturing;
1322 CLOSE debit_account_cur;
1323 --Added for bug#7277543
1324 IF p_non_iso_credit_account_id IS NULL OR p_non_iso_debit_account_id IS NULL THEN
1325 Fnd_File.PUT_LINE(Fnd_File.LOG, 'Excise Payable Accounts (RG23A/C/PLA) have not been setup in Organization Additional Information Screen - Cannot Process for Accounting');
1326 END IF;
1327 --End bug#7277543
1328 IF NVL(p_non_iso_credit_account_id,0) >0 AND NVL(p_non_iso_debit_account_id,0) >0 AND
1329 NOT /* The NOT part of the if condition added by ssumaith - bug# 4171469 */
1330 (
1331 /*(
1332 NVL(lv_source_trading,'N') = 'Y' and NVL(lv_dest_trading,'N') = 'Y'
1333 )
1334 AND
1335 (
1336 NVL(ln_dest_excise_in_rg23d,'N') = 'Y' and NVL(ln_src_excise_in_rg23d,'N') = 'Y'
1337 )*/
1338 --commented the above and added the below by Ramananda for Bug #4516577
1339 (NVL(lv_source_trading,'N') = 'Y'
1340 AND
1341 ( NVL(lv_dest_trading,'N') = 'Y') OR (NVL(lv_dest_manufacturing,'N') = 'Y')
1342 AND
1343 NVL(ln_src_excise_in_rg23d,'N') = 'Y')
1344
1345 )
1346 THEN
1347
1348 -- v_excise_amount := NVL(p_dr_basic_ed,0) + NVL(p_dr_additional_ed,0) + NVL(p_dr_other_ed,0);
1349 Fnd_File.PUT_LINE(Fnd_File.LOG, ' before calling gl interface -1 ');
1350 jai_cmn_gl_pkg.create_gl_entry
1351 (
1352 p_org_id,
1353 p_currency_code,
1354 p_excise_amount,
1355 0,
1356 p_non_iso_credit_account_id,
1357 p_source_name,
1358 p_category_name,
1359 p_created_by,
1360 /* Bug 4562791. Added by Lakshmi Gopalsami
1361 Changed NULL to gl_accounting_date */
1362 gl_accounting_date ,
1363 NULL, -- added by sriram - bug # 2769440
1364 NULL, -- added by sriram - bug # 2769440
1365 NULL, -- added by sriram - bug # 2769440
1366 P_REF_10,-- added by sriram - bug # 2769440
1367 P_REF_23,-- added by sriram - bug # 2769440
1368 P_REF_24,-- added by sriram - bug # 2769440
1369 P_REF_25,-- added by sriram - bug # 2769440
1370 P_REF_26);-- added by sriram - bug # 2769440
1371
1372 jai_cmn_gl_pkg.create_gl_entry
1373 (
1374 p_org_id,
1375 p_currency_code,
1376 0,
1377 p_excise_amount,
1378 p_non_iso_debit_account_id,
1379 p_source_name,
1380 p_category_name,
1381 p_created_by,
1382 /* Bug 4562791. Added by Lakshmi Gopalsami
1383 Changed NULL to gl_accounting_date */
1384 gl_accounting_date ,
1385 NULL,-- added by sriram - bug # 2769440
1386 NULL,-- added by sriram - bug # 2769440
1387 NULL,-- added by sriram - bug # 2769440
1388 P_REF_10,-- added by sriram - bug # 2769440
1389 P_REF_23,-- added by sriram - bug # 2769440
1390 P_REF_24,-- added by sriram - bug # 2769440
1391 P_REF_25,-- added by sriram - bug # 2769440
1392 P_REF_26);-- added by sriram - bug # 2769440
1393
1394 END IF;
1395
1396
1397 IF NVL(v_order_source_id, 0) = 10 THEN -- added by Aparajita for bug#2848921.
1398
1399 -- order is internal, fetch the extra informations.
1400
1401 -- get the destination organization
1402 OPEN c_get_to_organization(v_requisition_header_id, v_requisition_line_id);
1403 FETCH c_get_to_organization INTO v_to_organization_id , ln_to_location_id ;
1404 CLOSE c_get_to_organization;
1405
1406 OPEN c_get_iso_accounts(v_from_organization_id, v_to_organization_id);
1407 FETCH c_get_iso_accounts
1408 INTO v_intransit_type, v_fob_point, v_interorg_receivables_account,
1409 v_interorg_payables_account, v_intransit_inv_account;
1410 CLOSE c_get_iso_accounts;
1411
1412 /*
1413 getting the accounts of the destination organization.
1414
1415 OPEN c_get_iso_accounts(v_to_organization_id, v_from_organization_id);
1416 FETCH c_get_iso_accounts
1417 INTO lv_dest_intransit_type, ln_dest_fob_point, ln_dest_interorg_rcvbles_acc,
1418 ln_dest_interorg_payables_acc, ln_dest_intransit_inv_account;
1419 CLOSE c_get_iso_accounts;
1420 */
1421
1422
1423 -- check if type is intransit
1424 /*
1425 --Changed by Nagaraj.s for Bug2912007.
1426 --The Following is the New Accounting Entries as Required in case of ISO.
1427 --***************************************************************************************************
1428 --For Intransit Transfers(v_intransit_type=2)
1429 --If FOB Point =1(Shipment)
1430 Accounting Entries are : 1. Dr Inter Org Receivable, Cr Excise Paid Payable
1431 2. Dr Intransit Inventory Account, Cr Inter Org Payable Account
1432
1433 --If FOB Point =2(Receipt)
1434 Accounting Entries are : 1. Dr Intransit Inventory Account, Cr Excise Paid Payable Account
1435 --***************************************************************************************************
1436 --For Direct Transfers(v_intransit_type=1)
1437 Accounting Entries are : 1. Dr Inter Org Receivable, Cr Excise Paid Payable Account
1438 */
1439
1440
1441 IF v_intransit_type = 2 THEN
1442 -- fob point check added by Aparajita for bug#2848921, fob point 1 is shipment
1443 -- credit excise paid, payable account , debit inter org receiavable account.
1444
1445 IF v_fob_point IN (1,2) THEN
1446
1447 /*
1448 start additions by ssumaith for bug# 4171469 on shipment side in case of trading to trading ISO scenario.
1449 Get the details of the destination organiztion such as rg23d account , excise in rg23d
1450 */
1451
1452 /*IF NVL(lv_source_trading,'N') = 'Y' and NVL(lv_dest_trading,'N') = 'Y' THEN
1453 IF NVL(ln_dest_excise_in_rg23d,'N') = 'Y' and NVL(ln_src_excise_in_rg23d,'N') = 'Y' THEN*/
1454 --commented the above and added the below by Ramananda for Bug #4516577
1455 IF NVL(lv_source_trading,'N') = 'Y'
1456 AND
1457 ( NVL(lv_dest_trading,'N') = 'Y' OR NVL(lv_dest_manufacturing,'N') = 'Y')
1458 AND
1459 NVL(ln_src_excise_in_rg23d,'N') = 'Y' THEN
1460 /*
1461 write code to pass specific a/c entries for ttading to trading iso
1462 IF AN ISO TRANSACTIONS HAPPENS BETWEEN TWO TRADING ORGANIZATIONS, THEN THE FOLLOWING A/c Entries
1463 need to be passed provided both the source and destination organizations have the 'Excise in rg23D ' field
1464 set to 'Y' for the org + location combination.
1465
1466 FOB Point => SHIPMENT
1467 Debit Inventory Intransit A/c of Receiving org for the excise + cess amount
1468 Credit Excise A/c of Source organization - Excise + Cess amount
1469
1470 FOB Point => RECEIPT
1471
1472 Debit Inventory Intransit A/c of Source Org - Excise + Cess amt
1473 Credit Excise A/c of Source Org - Excise and Cess amt.
1474 */
1475
1476 ln_debit_acc := v_intransit_inv_account;
1477 ln_credit_acc := ln_src_excise_23d_account;
1478
1479
1480 jai_cmn_gl_pkg.create_gl_entry
1481 (
1482 p_org_id,
1483 p_currency_code,
1484 p_excise_amount + nvl(ln_cess_amount,0), --Credit
1485 0, --Debit
1486 ln_credit_acc,
1487 p_source_name,
1488 p_category_name,
1489 p_created_by,
1490 /* Bug 4562791. Added by Lakshmi Gopalsami
1491 Changed NULL to gl_accounting_date */
1492 gl_accounting_date ,
1493 NULL,
1494 NULL,
1495 NULL,
1496 P_REF_10,
1497 P_REF_23,
1498 P_REF_24,
1499 P_REF_25,
1500 P_REF_26);
1501
1502
1503
1504 jai_cmn_gl_pkg.create_gl_entry
1505 (p_org_id,
1506 p_currency_code,
1507 0,--Credit
1508 p_excise_amount + nvl(ln_cess_amount,0), --Debit
1509 ln_debit_acc,
1510 p_source_name,
1511 p_category_name,
1512 p_created_by,
1513 /* Bug 4562791. Added by Lakshmi Gopalsami
1514 Changed NULL to gl_accounting_date */
1515 gl_accounting_date ,
1516 NULL,
1517 NULL,
1518 NULL,
1519 P_REF_10,
1520 P_REF_23,
1521 P_REF_24,
1522 P_REF_25,
1523 P_REF_26
1524 );
1525 GOTO end_of_procedure;
1526 --END IF;
1527 --commented the above by Ramananda for Bug #4516577
1528 END IF;
1529
1530 /*
1531 ends here additions by ssumaith - bug# 4171469
1532 */
1533 --Added for bug#7277543
1534 IF v_excise_rcvble_account IS NULL OR v_intransit_inv_account IS NULL THEN
1535 Fnd_File.PUT_LINE(Fnd_File.LOG, 'Excise Receivable account or Intransit Invoice Account have not been setup in Organization Additional Information Screen - Cannot Process for Accounting');
1536 END IF;
1537 --End bug#7277543
1538 IF NVL(v_excise_rcvble_account,0) >0 AND NVL(v_intransit_inv_account,0) >0 THEN
1539
1540 --CREDIT Excise Paid Payable Account
1541 Fnd_File.PUT_LINE(Fnd_File.LOG, ' before calling gl interface - 2 ');
1542 jai_cmn_gl_pkg.create_gl_entry
1543 (
1544 p_org_id,
1545 p_currency_code,
1546 p_excise_amount, --Credit
1547 0, --Debit
1548 v_excise_rcvble_account,
1549 p_source_name,
1550 p_category_name,
1551 p_created_by,
1552 /* Bug 4562791. Added by Lakshmi Gopalsami
1553 Changed NULL to gl_accounting_date */
1554 gl_accounting_date ,
1555 NULL,-- added by sriram - bug # 2769440
1556 NULL,-- added by sriram - bug # 2769440
1557 NULL,-- added by sriram - bug # 2769440
1558 P_REF_10,-- added by sriram - bug # 2769440
1559 P_REF_23,-- added by sriram - bug # 2769440
1560 P_REF_24,-- added by sriram - bug # 2769440
1561 P_REF_25,-- added by sriram - bug # 2769440
1562 P_REF_26);-- added by sriram - bug # 2769440
1563
1564
1565 --DEBIT InTransit Inventory Account
1566 jai_cmn_gl_pkg.create_gl_entry
1567 (p_org_id,
1568 p_currency_code,
1569 0,--Credit
1570 p_excise_amount, --Debit
1571 v_intransit_inv_account,
1572 p_source_name,
1573 p_category_name,
1574 p_created_by,
1575 /* Bug 4562791. Added by Lakshmi Gopalsami
1576 Changed NULL to gl_accounting_date */
1577 gl_accounting_date ,
1578 NULL,-- added by sriram - bug # 2769440
1579 NULL,-- added by sriram - bug # 2769440
1580 NULL,-- added by sriram - bug # 2769440
1581 P_REF_10,-- added by sriram - bug # 2769440
1582 P_REF_23,-- added by sriram - bug # 2769440
1583 P_REF_24,-- added by sriram - bug # 2769440
1584 P_REF_25,-- added by sriram - bug # 2769440
1585 P_REF_26);-- added by sriram - bug # 2769440
1586
1587 END IF; --End if For Account Checks
1588
1589 END IF; --End if For v_fob_point in 1,2
1590
1591 IF v_fob_point=1 THEN
1592 --Added for bug#7277543
1593 IF v_interorg_payables_account IS NULL OR v_interorg_receivables_account IS NULL THEN
1594 Fnd_File.PUT_LINE(Fnd_File.LOG, ' Interorg Payables Account or Interorg Receivables Account have not been setup in Organization Additional Information Screen - Cannot Process for Accounting');
1595 END IF;
1596 --End bug#7277543
1597
1598 IF NVL(v_interorg_payables_account, 0) >0 AND NVL(v_interorg_receivables_account,0) >0 THEN
1599
1600 -- DEBIT Inter Org Receivables Account
1601 Fnd_File.PUT_LINE(Fnd_File.LOG, ' before calling gl interface - 3 ');
1602
1603 jai_cmn_gl_pkg.create_gl_entry
1604 ( p_org_id,
1605 p_currency_code,
1606 0, --Credit
1607 p_excise_amount, --Debit
1608 v_interorg_receivables_account,
1609 p_source_name,
1610 p_category_name,
1611 p_created_by,
1612 /* Bug 4562791. Added by Lakshmi Gopalsami
1613 Changed NULL to gl_accounting_date */
1614 gl_accounting_date ,
1615 NULL,-- added by sriram - bug # 2769440
1616 NULL,-- added by sriram - bug # 2769440
1617 NULL,-- added by sriram - bug # 2769440
1618 P_REF_10,-- added by sriram - bug # 2769440
1619 P_REF_23,-- added by sriram - bug # 2769440
1620 P_REF_24,-- added by sriram - bug # 2769440
1621 P_REF_25,-- added by sriram - bug # 2769440
1622 P_REF_26);-- added by sriram - bug # 2769440
1623
1624 -- CREDIT Inter Org Payables Account
1625 jai_cmn_gl_pkg.create_gl_entry
1626 (
1627 p_org_id,
1628 p_currency_code,
1629 p_excise_amount, --Credit
1630 0, --Debit
1631 v_interorg_payables_account,
1632 p_source_name,
1633 p_category_name,
1634 p_created_by,
1635 /* Bug 4562791. Added by Lakshmi Gopalsami
1636 Changed NULL to gl_accounting_date */
1637 gl_accounting_date ,
1638 NULL,-- added by sriram - bug # 2769440
1639 NULL,-- added by sriram - bug # 2769440
1640 NULL,-- added by sriram - bug # 2769440
1641 P_REF_10,-- added by sriram - bug # 2769440
1642 P_REF_23,-- added by sriram - bug # 2769440
1643 P_REF_24,-- added by sriram - bug # 2769440
1644 P_REF_25,-- added by sriram - bug # 2769440
1645 P_REF_26);-- added by sriram - bug # 2769440
1646
1647 END IF; --End if For Account Checks
1648
1649 END IF; --End if For v_fob_point
1650
1651 /*
1652 as discussed with support (yadunath - commenting out the code for v_intransit_type = 1 - bug#4171469
1653 ELSIF v_intransit_type = 1 THEN
1654
1655 IF NVL(v_excise_rcvble_account, 0) >0 AND NVL(v_interorg_receivables_account,0) >0 THEN
1656
1657 --CREDIT Excise Paid Payable Account
1658 Fnd_File.PUT_LINE(Fnd_File.LOG, ' before calling gl interface - 4 ');
1659
1660 jai_cmn_gl_pkg.create_gl_entry
1661 (
1662 p_org_id,
1663 p_currency_code,
1664 p_excise_amount, --Credit
1665 0, --Debit
1666 v_excise_rcvble_account,
1667 p_source_name,
1668 p_category_name,
1669 p_created_by,
1670 NULL,-- added by sriram - bug # 2769440
1671 NULL,-- added by sriram - bug # 2769440
1672 NULL,-- added by sriram - bug # 2769440
1673 NULL,-- added by sriram - bug # 2769440
1674 P_REF_10,-- added by sriram - bug # 2769440
1675 P_REF_23,-- added by sriram - bug # 2769440
1676 P_REF_24,-- added by sriram - bug # 2769440
1677 P_REF_25,-- added by sriram - bug # 2769440
1678 P_REF_26);-- added by sriram - bug # 2769440
1679
1680 -- DEBIT Inter Org Receivables Account
1681 jai_cmn_gl_pkg.create_gl_entry
1682 (
1683 p_org_id,
1684 p_currency_code,
1685 0, --Credit
1686 p_excise_amount, --Debit
1687 v_interorg_receivables_account,
1688 p_source_name,
1689 p_category_name,
1690 p_created_by,
1691 NULL,-- added by sriram - bug # 2769440
1692 NULL,-- added by sriram - bug # 2769440
1693 NULL,-- added by sriram - bug # 2769440
1694 NULL,-- added by sriram - bug # 2769440
1695 P_REF_10,-- added by sriram - bug # 2769440
1696 P_REF_23,-- added by sriram - bug # 2769440
1697 P_REF_24,-- added by sriram - bug # 2769440
1698 P_REF_25,-- added by sriram - bug # 2769440
1699 P_REF_26);-- added by sriram - bug # 2769440
1700
1701 END IF; --End if for Account Checks. */
1702 -- type is transit, extra accounting entries
1703
1704 -- start additions by ssumaith - bug#3817625
1705 Fnd_File.PUT_LINE(Fnd_File.LOG, ' before calling JA_IN_CESS_ENTRIES');
1706
1707 JA_IN_CESS_ENTRIES
1708 (
1709 p_organization_id => p_org_id ,
1710 p_location_id => p_location_id ,
1711 p_delivery_detail_id => p_delivery_detail_id ,
1712 p_intransit_inv_acct => v_intransit_inv_account ,
1713 p_intercompany_rcvbl => v_interorg_receivables_account ,
1714 p_intercompany_paybl => v_interorg_payables_account ,
1715 p_fob_point => v_fob_point ,
1716 p_currency_code => p_currency_code ,
1717 p_source_name => p_source_name ,
1718 p_category_name => p_category_name ,
1719 p_created_by => p_created_by ,
1720 P_REF_10 => P_REF_10 ,
1721 P_REF_23 => P_REF_23 ,
1722 P_REF_24 => P_REF_24 ,
1723 P_REF_25 => P_REF_25 ,
1724 P_REF_26 => P_REF_26
1725 );
1726 END IF;/*Bug 5989740 bduvarag*/
1727 Fnd_File.PUT_LINE(Fnd_File.LOG, ' after calling JA_IN_CESS_ENTRIES');
1728 -- start additions by ssumaith - bug#3817625
1729 END IF; -- internal order.
1730
1731 <<end_of_procedure>>
1732 null;
1733 EXCEPTION
1734 WHEN OTHERS THEN
1735 FND_MESSAGE.SET_NAME ('JA','JAI_EXCEPTION_OCCURED');
1736 FND_MESSAGE.SET_TOKEN ('JAI_PROCESS_MSG',lv_object_name ||'.Err:'||sqlerrm);
1737 app_exception.raise_exception;
1738 END ja_in_accounting_entries;
1739
1740 /************************************** END JA_IN_ACCOUNTING_ENTRIES **************************************************/
1741
1742
1743 /***************************** JA_IN_RG_I_ENTRY *******************************************************************/
1744
1745 PROCEDURE ja_in_rg_I_entry(
1746 p_fin_year NUMBER ,
1747 p_org_id NUMBER ,
1748 p_location_id NUMBER ,
1749 p_inventory_item_id NUMBER ,
1750 p_transaction_id NUMBER ,
1751 p_transaction_date DATE ,
1752 p_transaction_type VARCHAR2 ,
1753 p_header_id NUMBER ,
1754 p_excise_quantity NUMBER ,
1755 p_excise_amount NUMBER ,
1756 p_uom_code VARCHAR2 ,
1757 p_excise_invoice_no VARCHAR2 ,
1758 p_excise_invoice_date DATE ,
1759 p_payment_register VARCHAR2 ,
1760 p_basic_ed NUMBER ,
1761 p_additional_ed NUMBER ,
1762 p_other_ed NUMBER ,
1763 p_excise_duty_rate NUMBER ,
1764 p_customer_id NUMBER ,
1765 p_customer_site_id NUMBER ,
1766 p_register_code VARCHAR2 ,
1767 p_creation_date DATE ,
1768 p_created_by NUMBER ,
1769 p_last_update_date DATE ,
1770 p_last_updated_by NUMBER ,
1771 p_last_update_login NUMBER ,
1772 p_assessable_value NUMBER ,
1773 p_cess_amt JAI_CMN_RG_I_TRXS.CESS_AMT%TYPE DEFAULT NULL ,
1774 p_sh_cess_amt JAI_CMN_RG_I_TRXS.SH_CESS_AMT%TYPE DEFAULT NULL , /*Bug 5989740 bduvarag*/
1775 p_source JAI_CMN_RG_I_TRXS.SOURCE%TYPE DEFAULT NULL /*Parameters p_cess_amt and p_source added by aiyer for the bug 4566054 */
1776 ) IS
1777
1778 --parameter for assessable value added
1779 v_serial_no NUMBER := 0;
1780 v_previous_serial_no NUMBER := 0;
1781 v_range_no VARCHAR2(50);
1782 v_division_no VARCHAR2(50);
1783 v_manufactured_qty NUMBER := 0;
1784 v_manufactured_packed_qty NUMBER := 0;
1785 v_manufactured_loose_qty NUMBER := 0;
1786 v_other_purpose_n_pay_ed_qty NUMBER := 0;
1787 v_other_purpose_n_pay_ed_val NUMBER := 0;
1788 v_for_export_pay_ed_qty NUMBER := 0;
1789 v_for_export_pay_ed_val NUMBER := 0;
1790 v_for_export_n_pay_ed_qty NUMBER := 0;
1791 v_for_export_n_pay_ed_val NUMBER := 0;
1792 v_home_use_pay_ed_qty NUMBER := 0;
1793 v_home_use_pay_ed_val NUMBER := 0;
1794 v_other_purpose VARCHAR2(25);
1795 v_primary_uom_code VARCHAR2(20);
1796 v_balance_packed NUMBER := 0;
1797 v_balance_loose NUMBER := 0;
1798 v_packed_loose_qty NUMBER := 0;
1799 v_left_balance NUMBER := 0;
1800 v_issue_type VARCHAR2(10);
1801 v_excise_duty_amount NUMBER; -- := p_excise_amount; --Ramananda for File.Sql.35
1802 v_basic_ed NUMBER; -- := p_basic_ed; --Ramananda for File.Sql.35
1803 v_additional_ed NUMBER; -- := p_additional_ed; --Ramananda for File.Sql.35
1804 v_other_ed NUMBER; -- := p_other_ed; --Ramananda for File.Sql.35
1805 v_conversion_rate NUMBER := 1;
1806 v_assessable_value NUMBER; -- := p_assessable_value;
1807 -- Commente and Added by Brathod, For Bug# 4299606 (DFF Elimination)
1808 -- V_ITEM_CLASS_ISSUE MTL_SYSTEM_ITEMS.ATTRIBUTE3%TYPE; ---- 20-APR-01 Vijay Jagdish
1809 V_ITEM_CLASS_ISSUE JAI_INV_ITM_SETUPS.ITEM_CLASS%TYPE;
1810
1811 -- Start, Vijay Shankar for Bug# 3408210
1812 v_to_other_fact_n_pay_ed_qty NUMBER;
1813 v_to_other_fact_n_pay_ed_val NUMBER;
1814
1815 v_converted_rate number := 1; /*Added by nprashar for bug # 11812969 */
1816
1817 l_func_curr_det jai_plsql_cache_pkg.func_curr_details; /*Added by nprashar for bug # 11812969 */
1818
1819 v_set_of_books_id NUMBER; /*Added by nprashar for bug # 11812969 */
1820
1821 v_order_header_id oe_order_headers_all.header_id%type; /*Added by nprashar for bug # 11812969 */
1822 v_order_line_id oe_order_lines_all.line_id%type;
1823 v_currency_code OE_ORDER_HEADERS_ALL.TRANSACTIONAL_CURR_CODE%TYPE ; /*Added by nprashar for bug # 11812969 */
1824 v_conv_type_code OE_ORDER_HEADERS_ALL.CONVERSION_TYPE_CODE%TYPE ;
1825 v_conv_rate NUMBER ;
1826 v_conv_date DATE ;
1827 v_order_number NUMBER ;
1828
1829 Cursor get_coversion_details ( p_header_id number, p_line_id number) is /*Added by nprashar for bug # 11812969 */
1830 SELECT
1831 order_number ,
1832 transactional_curr_code ,
1833 conversion_type_code ,
1834 conversion_rate ,
1835 b.actual_shipment_date
1836 FROM
1837 oe_order_headers_all a ,
1838 oe_order_lines_all b
1839 WHERE
1840 a.header_id = b.header_id AND
1841 b.line_id = p_line_id AND
1842 a.header_id = p_header_id ;
1843
1844 Cursor get_order_info(p_delivery_detail_id number) is /*Added by nprashar for bug # 11812969 */
1845 Select temp.order_header_id, oola.line_id from
1846 JAI_OM_OE_GEN_TAXINV_T temp , oe_order_lines_all oola
1847 where temp.order_header_id = oola.header_id
1848 and temp.delivery_detail_id = p_delivery_detail_id;
1849
1850 CURSOR c_exc_exempt_dtls(p_delivery_detail_id IN NUMBER) IS
1851 SELECT excise_exempt_type
1852 FROM JAI_OM_WSH_LINES_ALL
1853 WHERE delivery_detail_id = p_delivery_detail_id;
1854 v_exc_exempt_dtls_rec c_exc_exempt_dtls%ROWTYPE;
1855 -- End, Vijay Shankar for Bug# 3408210
1856
1857 CURSOR primary_uom_cur IS
1858 SELECT primary_uom_code
1859 FROM mtl_system_items
1860 WHERE inventory_item_id = p_inventory_item_id AND
1861 organization_id = p_org_id;
1862
1863 CURSOR serial_no_cur IS
1864 SELECT NVL(MAX(slno),0), (NVL(MAX(slno),0) + 1)
1865 FROM JAI_CMN_RG_I_TRXS
1866 WHERE organization_id = p_org_id AND
1867 location_id = p_location_id AND
1868 inventory_item_id = p_inventory_item_id AND
1869 fin_year = p_fin_year;
1870
1871 CURSOR packed_loose_qty_cur(p_previous_serial_no IN NUMBER) IS
1872 SELECT NVL(balance_packed,0), NVL(balance_loose,0)
1873 FROM JAI_CMN_RG_I_TRXS
1874 WHERE organization_id = p_org_id AND
1875 location_id = p_location_id AND
1876 inventory_item_id = p_inventory_item_id AND
1877 fin_year = p_fin_year AND
1878 slno = p_previous_serial_no;
1879
1880 CURSOR range_division_cur IS
1881 SELECT excise_duty_range,excise_duty_division
1882 FROM JAI_CMN_CUS_ADDRESSES
1883 WHERE customer_id = p_customer_id
1884 AND address_id = (SELECT cust_acct_site_id -- address_id
1885 FROM hz_cust_site_uses_all A -- Removed ra_site_uses_all for Bug# 4434287
1886 WHERE A.site_use_id = p_customer_site_id);
1887
1888 /***added by Jagdish and vijay on */
1889 CURSOR rg_manufactured_loose_cur IS
1890 SELECT item_class -- Commented attribute3 by Brathod, For Bug# 4299606 (DFF Elimination)
1891 FROM JAI_INV_ITM_SETUPS -- Commneted mtl_system_items by Brathod for Bug# 4299606 (DFF Elimination)
1892 WHERE inventory_item_id=p_inventory_item_id
1893 AND organization_id = p_org_id;
1894
1895 lv_object_name CONSTANT VARCHAR2 (61) := 'jai_om_rg_pkg.ja_in_rg_I_entry';
1896
1897 /*bug 9122545*/
1898 CURSOR c_org_addl_rg_flag (cp_organization_id jai_cmn_inventory_orgs.organization_id%type,
1899 cp_location_id jai_cmn_inventory_orgs.location_id%type)
1900 IS
1901 SELECT nvl(allow_negative_rg_flag,'N')
1902 FROM jai_cmn_inventory_orgs
1903 WHERE organization_id = cp_organization_id
1904 AND location_id = cp_location_id;
1905
1906 lv_allow_negative_rg_flag jai_cmn_inventory_orgs.allow_negative_rg_flag%type;
1907 /*end bug 9122545*/
1908
1909 /*------------------------------------------------------------------------------------------
1910 FILENAME: jai_om_rg_pkg.sql
1911 CHANGE HISTORY:
1912 ------------------------------------------------------------------------------------------
1913 1. 2002/07/03 Nagaraj.s - For Enh#2415656.
1914 The changes were done only in ja_in_pla_entry procedure.
1915 2 cursors c_order_type_id and c_register_code is added.
1916 and cursors pla_balance_cur,balance_cur are also changed
1917 It is checked that if the Export Oriented Flag is true, then the balances
1918 are populated into each component balances and if the
1919 Export Oriented Unit is False, then the same coding is in place.
1920
1921 2. 2002/08/03 Aparajita FOR bug # 2496388. Version#615.1
1922 Accounting entries are different for internal sales order, did the changes
1923 in procedure ja_in_accounting_entries and modified the procedure rg23_part_ii and PLA
1924 to call the new procedure for accounting entries.
1925
1926 3. 2003/03/16 Sriram - Bug # 2796717. Version#615.2
1927 Added CT3 also for the comparison on excise exempted types , because this was
1928 causing the excise paid payable account to be hit instead of Cenvat Reversal account
1929 for excise exempted transactions.
1930
1931 4. 2003/04/01 Aparajita - bug#2848921. Version#615.3
1932 Modified procedure ja_in_accounting_entries for iso accounting entries.
1933 Refer to the pocedure ja_in_accounting_entries for more details.
1934
1935 5. 2003/04/07 Aparajita - bug#2893284. Version#615.4
1936 Modified procedure ja_in_accounting_entries for iso accounting entries.
1937 Refer to the pocedure ja_in_accounting_entries for more details.
1938
1939 6. 2003/05/13 Nagaraj.s - Bug2912007 Version : 615.5
1940 Modified procedure ja_in_accounting_entries for iso accounting entries.
1941 Refer to the pocedure ja_in_accounting_entries for more details.
1942
1943 7. 2003/07/31 ssumaith Bug # 2769440 - Version 616.1
1944 Added new parameters in call to the jai_cmn_gl_pkg.create_gl_entry
1945 procedure call . The parameters are added so that the values such as
1946 delivery id and other info can be displayed from the front end.
1947
1948 8. 2003/08/23 ssumaith Bug # 3021588 - Version 616.2
1949
1950 For Multiple Bond Register Enhancement,
1951
1952 Instead of using the cursors for fetching the register associated with the order / invoice type , a call has been made to the procedures
1953 of the jai_cmn_bond_register_pkg package. There enhancement has created dependency because of the
1954 introduction of 3 new columns in the JAI_OM_OE_BOND_REG_HDRS table and also call to the new package jai_cmn_bond_register_pkg.
1955
1956 New Validations for checking the bond expiry date and to check the balance based on the call to the jai_cmn_bond_register_pkg has been added
1957
1958 Provision for letter of undertaking has been incorporated. In the case of the letter of undetaking , its also a type of bond register
1959 but without validation for the balances.
1960 This has been done by checking if the LOU_FLAG is 'Y' in the JAI_OM_OE_BOND_REG_HDRS table for the
1961 associated register id , if yes , then validation is only based on bond expiry date .
1962
1963 This fix has introduced huge dependency . All future changes in this object should have this bug as a prereq
1964
1965 9. 09/02/2004 Vijay Shankar for Bug# 3408210, File Version: 618.1
1966 JA_IN_RG_I_ENTRY procedure:- CT3 Excise Exempted Issue transaction is hitting RG1 with Home Use fields, which is wrong and code is modified
1967 to hit To_other_factory_n_pay_ed_qty field with quantity. transactions is identified as CT3 by fetching data from
1968 JAI_OM_WSH_LINES_ALL through a cursor c_exc_exempt_dtls. Insert into JAI_CMN_RG_I_TRXS is modified to populate specified columns
1969 in case of CT3 transaction. following fields are added in the procedure
1970 v_to_other_fact_n_pay_ed_qty NUMBER;
1971 v_to_other_fact_n_pay_ed_val NUMBER;
1972 v_exc_exempt_dtls_rec c_exc_exempt_dtls%ROWTYPE;
1973
1974 10. 13/04/2004 Aiyer for Bug#3556320 File Version 619.1
1975 Issue:-
1976 Quantity is populated into wrong column (column 5) for CT2 excise exemption type of transactions
1977 with register as Domestic_Excise in the India RG1 register report.
1978
1979 Fix:-
1980 Modified this procedure such that in case of CT2 excise exemption and DOMESTIC_EXCISE
1981 register_code the fields to_other_fact_n_pay_ed_qty and to_other_fact_n_pay_ed_val
1982 should be populated instead of home_use_pay_ed_qty and home_use_pay_ed_val.
1983 and issue_type should be 'OF' instead of 'HU'
1984
1985 11 04/05/2004 Vijay Shankar for Bug# 3604540, File Version: 619.2
1986 JA_IN_RG_I_ENTRY procedure:- Order Management transactions are hitting For_home_use_pay_ed_qty and manufactured_loose_qty columns, which should
1987 actually hit only the first field. This is rectified by commenting the code that populates manufactured_loose_qty column
1988
1989 12 28/05/2004 Vijay Shankar for Bug# 3657742, File Version: 115.1
1990 modified Ja_In_Rg23d_Entry procedure to consider item previous year balances for opening balance calculation of
1991 a new transaction (happening in a new financial year). two cursor c_max_register_id and c_rg23d_rec are added
1992 for the purpose. also code added to make serial number as 1 if it is a first transaction of financial year or
1993 new item transaction
1994
1995 13. 25/01/2005 ssumaith - bug#4136981 File version 115.2
1996
1997 Code changes done for Education cess. In case of Manual AR invoice completion and Excise invoice generation,
1998 Accounting entries and register entries to be hit for education cess
1999
2000 This fix does not introduce dependency on this object , but this patch cannot be sent alone to the CT
2001 because it relies on the alter done and the new tables created as part of the education cess enhancement
2002 bug# 4146708 creates the objects
2003
2004 14. 16/02/2005 ssumaith - bug# 4185392 - File Version 115.4
2005
2006 Even when cess amount is zero , still call to insert row for cess record was being done.
2007 This was not necessary , hence call to the procedure JAI_CMN_RG_OTHERS_pkg.insert_row was done only
2008 if the cess amount is a non zero value.
2009
2010 Changes are made in the following places
2011
2012 1. procedure ja_in_om_cess_register_entries - code change done is to add a having condition to get only those taxes where cess amount <> 0
2013 2. procedure ja_in_ar_cess_register_entries - same as above.
2014
2015 Dependency due to this bug:-
2016 None
2017
2018
2019 15. 16/03/2005 ssumaith - For VAT -bug#4245053 - File Version - 115.5
2020
2021 For Excise Exempted transactions , cenvat reversal account is being used to hit the cess reversal entries also.
2022 This is in line with the discussion with product management and support , that cenvat reversal account needs
2023 to be used for cess as well .
2024
2025 16 26/04/2005 Brathod for Bug# 4299606 File Version 116.1
2026 Issue:-
2027 Item DFF Elimination
2028 Fix:-
2029 Changed the code that references attributeN (where N=1,2,3,4,5,15) of
2030 mtl_system_items to corrosponding columns in JAI_INV_ITM_SETUPS
2031
2032 Dependency :-
2033 IN60106 + 4239736 (Service Tax) + 4245089 (VAT)
2034
2035 17. 23-Aug-2005 Aiyer - Bug 4566054 (Forward porting for the 11.5 bug 4346220 ),Version 120.4
2036 Issue :-
2037 Rg does not show correct cess value in case of Shipment transactions.
2038
2039 Fix:-
2040 Two fields cess_amt and source have been added in JAI_CMN_RG_I_TRXS table.
2041 The cess amt and source would be populated from jai_jar_t_aru_t1 (Source -> 'AR' ) and
2042 as 'WSH' from jai_om_wsh.plb procedure Shipment.
2043 Corresponding changes have been done in the form JAINIRGI.fmb and JAFRMRG1.rdf .
2044 For shipment and Ar receivable transaction currently the transaction_id is 33 and in some cases where the jai_cmn_rg_i_trxs.ref_doc_id
2045 exactly matches the jai_om_wsh_lines_all.delivery_detail_id and jai_ar_trxs.customer_trx_id the tracking of the source
2046 becomes very difficult hence to have a clear demarcation between WSh and AR sources hence the source field has been added.
2047
2048 Added 2 new parametes p_cess_amt and p_source to jai_om_rg_pkg.ja_in_rg_i_entry package.
2049 This has been populated from this and jai_om_wsh_pkg.process_delivery procedure.
2050
2051 A migration script has been provided to migrate the value for cess and source.
2052
2053 Dependency due to this bug:-
2054 1. Datamodel change in table JAI_CMN_RG_I_TRXS, added the cess_amt and source fields
2055 2. Added two new parameters in jai_om_rg_pkg.ja_in_rg_i_entry procedure to insert data into JAI_CMN_RG_I_TRXS table
2056 3. Modified the trigger jai_jar_t_aru_t1
2057 4. Procedure jai_om_wsh_pkg.process_delivery
2058 5. Report JAICMNRG1.rdf
2059 6. Created a migration script to populate cess_amt and source for Shipment and Receivable transactions.
2060 Both functional and technical dependencies exists
2061
2062 18. 15-Feb-2007 CSahoo Bug#5390583, File Version - 120.12
2063 Forward Porting of 11i Bug 5357400
2064 Issue : Excise amount not hitting bond register in functional currency.
2065 Fix : Excise and cess amounts would hit bond register in functional currency.
2066 Changes are done in three objects.
2067
2068 1. Package jai_om_rg_pkg. - Added a parameter to the ja_in_register_txn_entry called p_currency_rate
2069 It holds the currency conversion rate which would be multiplied by the transaction amts to
2070 get the functional amounts.
2071
2072 2. Package jai_jar_t.plb - In the call to the ja_in_register_txn_entry procedure
2073 added the parameter called p_currency_code.
2074
2075 3. Package - jai_ract_trg_pkg - When a change is done in the invoice currency code from the front end
2076 the change is being reflected in the JAI_AR_TRXS table.
2077
2078 Future Dependency due to this Bug
2079 ------------------------
2080 YES - A new parameter is added to the procedure - ja_in_register_txn_entry in the package jai_om_rg_pkg.
2081 It has a technical dependency on jai_om_rg_pkg and Package jai_jar_t.plb.
2082 It has functional dependency on jai_ract_trg.plb
2083
2084
2085 19. 09/10/2007 ssumaith - bug#6487667 - File version - 120.19
2086 When a sales order trx is done that hits bond register , if only excise tax is present and cess , she_cess is not present,
2087 the register balance was becoming 0. It was because of an incorrect handling of null values.
2088 Added nvls to the variables ln_cess_amount and ln_sh_cess_amount in the ja_in_register_txn_entry procedure.
2089
2090 20. 27-Nov-2009 Bug 9122545 File version 120.8.12000000.14 / 120.20.12010000.8 / 120.30
2091 Description - Checked the setup option to allow negative quantity in RG register before raising the
2092 error "Enough RG1 balance is not available to Issue the Goods".
2093
2094
2095 Future Dependencies For the release Of this Object:-
2096 (Please add a row in the section below only if your bug introduces a dependency due to spec change/ A new call to a object/
2097 A datamodel change )
2098
2099 ----------------------------------------------------------------------------------------------------------------------------------------------------
2100 Current Version Current Bug Dependent Files Version Author Date Remarks
2101 Of File On Bug/Patchset Dependent On
2102 jai_om_rg_pkg.sql
2103 ------------------------------------------------------------------------------------------------------------------------------------------------
2104 616.2 3021588 IN60104D1 + ssumaith 22/08/2003 Bond Register Enhancement
2105 2801751 +
2106 2769440
2107
2108 115.2 4136981 4146708 ssumaith 27/01/2005 Education Cess Enhancement
2109
2110 115.9 4299606 IN60106 brathod 26/04/2005 Item DFF Elimination
2111 + 4239736 (Service Tax)
2112 + 4245089 (VAT)
2113
2114 12.0 4566054 jai_om_rg.pls 120.3 Aiyer 24-Aug-2005
2115 jai_om_rg.plb 120.4
2116 jai_om_wsh.plb (jai_om_wsh_pkg.process_delivery) 120.4
2117 JAINIRGI.fmb 120.2
2118 jain14.odf 120.3
2119 jain14reg.ldt 120.3
2120 New migration script to port data into new tables 120.0
2121 JAICMNRG1.rdf 120.3
2122 jai_jai_t.sql (trigger jai_jar_t_aru_t1) 120.1
2123
2124 --------------------------------------------------------------------------------------------------------------*/
2125 BEGIN
2126
2127
2128 v_excise_duty_amount := p_excise_amount; --Ramananda for File.Sql.35
2129 v_basic_ed := p_basic_ed; --Ramananda for File.Sql.35
2130 v_additional_ed := p_additional_ed; --Ramananda for File.Sql.35
2131 v_other_ed := p_other_ed; --Ramananda for File.Sql.35
2132 v_assessable_value := p_assessable_value; --Ramananda for File.Sql.35
2133
2134 OPEN primary_uom_cur;
2135 FETCH primary_uom_cur INTO v_primary_uom_code;
2136 CLOSE primary_uom_cur;
2137
2138 Inv_Convert.inv_um_conversion(p_uom_code, v_primary_uom_code, p_inventory_item_id,v_conversion_rate);
2139
2140 IF NVL(v_conversion_rate, 0) <= 0 THEN
2141 Inv_Convert.inv_um_conversion(p_uom_code, v_primary_uom_code, 0, v_conversion_rate);
2142 IF NVL(v_conversion_rate, 0) <= 0 THEN
2143 v_conversion_rate := 0;
2144 END IF;
2145 END IF;
2146
2147 /*Addition of code starts for bug # 11812969 */
2148 l_func_curr_det := jai_plsql_cache_pkg.return_sob_curr (p_org_id);
2149 v_set_of_books_id := l_func_curr_det.ledger_id;
2150
2151 Open get_order_info (p_header_id);
2152 Fetch get_order_info into v_order_header_id, v_order_line_id;
2153 Close get_order_info;
2154
2155 Open get_coversion_details( v_order_header_id, v_order_line_id);
2156 Fetch get_coversion_details into v_order_number ,
2157 v_currency_code ,
2158 v_conv_type_code,
2159 v_conv_rate ,
2160 v_conv_date;
2161
2162 If get_coversion_details%FOUND Then
2163 v_converted_rate := jai_cmn_utils_pkg.currency_conversion (
2164 v_set_of_books_id ,
2165 v_currency_code ,
2166 v_conv_date ,
2167 v_conv_type_code ,
2168 v_conv_rate
2169 );
2170 If v_converted_rate is null then
2171 v_converted_rate := 1;
2172 End if;
2173 End IF;
2174 Close get_coversion_details;
2175 /*Ends here for bug # 11812969 */
2176
2177 OPEN RANGE_DIVISION_CUR;
2178 FETCH RANGE_DIVISION_CUR INTO v_range_no,v_division_no;
2179 CLOSE RANGE_DIVISION_CUR;
2180
2181 IF p_register_code IN ('DOMESTIC_EXCISE') THEN
2182
2183 -- Vijay Shankar for Bug# 3408210
2184 -- following Cursor and if condition are introduced by Vijay Shankar for Bug# 3408210 to resolve the
2185 -- excise exemption case
2186 OPEN c_exc_exempt_dtls(p_header_id);
2187 FETCH c_exc_exempt_dtls INTO v_exc_exempt_dtls_rec;
2188 CLOSE c_exc_exempt_dtls;
2189 -- Start of bug #3556320
2190 /******
2191 Code added by aiyer for the bug 3556320
2192 Changed the if condition to add 'CT2' excise_exempt_type.
2193 The functional requirement is that in case of CT2 excise exemption and DOMESTIC_EXCISE
2194 register_code the fields to_other_fact_n_pay_ed_qty and to_other_fact_n_pay_ed_val
2195 should be populated instead of home_use_pay_ed_qty and home_use_pay_ed_val.
2196 and issue_type should be 'OF' instead of 'HU'
2197 ******/
2198 IF v_exc_exempt_dtls_rec.excise_exempt_type IN ('CT3','CT2') THEN
2199 -- End of bug #3556320
2200 v_issue_type := 'OF';
2201 v_to_other_fact_n_pay_ed_qty := p_excise_quantity * v_conversion_rate ;
2202 v_to_other_fact_n_pay_ed_val := round(NVL(v_assessable_value, 0) * NVL(v_to_other_fact_n_pay_ed_qty, 0) * v_converted_rate,2) ; /*Added by nprashar for bug # 11812969 */--added round for bug#7479016
2203 ELSE
2204 v_issue_type := 'HU';
2205 v_home_use_pay_ed_qty := p_excise_quantity * v_conversion_rate;
2206 v_home_use_pay_ed_val :=round(NVL(v_assessable_value, 0) * NVL(v_home_use_pay_ed_qty, 0) * v_converted_rate,2) ; /*Added by nprashar for bug # 11812969 */--added round for bug#7479016
2207 END IF;
2208
2209 ELSIF p_register_code IN ('EXPORT_EXCISE') THEN
2210 v_issue_type := 'EWE';
2211 v_for_export_pay_ed_qty := p_excise_quantity * v_conversion_rate ;
2212 v_for_export_pay_ed_val := round(NVL(v_assessable_value, 0) * NVL(v_for_export_pay_ed_qty, 0) * v_converted_rate,2) ; /*Added by nprashar for bug # 11812969 */ --added round for bug#7479016
2213 ELSIF p_register_code IN ('BOND_REG') THEN
2214 v_issue_type := 'ENE';
2215 v_for_export_n_pay_ed_qty := p_excise_quantity * v_conversion_rate ;
2216 v_for_export_n_pay_ed_val := round(NVL(v_assessable_value, 0) * NVL(v_for_export_n_pay_ed_qty, 0) * v_converted_rate,2) ; /*Added by nprashar for bug # 11812969 */ --added round for bug#7479016
2217 ELSIF p_register_code IN ('DOM_WITHOUT_EXCISE') THEN
2218 v_issue_type := 'OPNE';
2219 v_other_purpose_n_pay_ed_qty := p_excise_quantity * v_conversion_rate ;
2220 v_other_purpose_n_pay_ed_val := round(NVL(v_assessable_value, 0) * NVL(v_other_purpose_n_pay_ed_qty, 0) * v_converted_rate,2) ; /*Added by nprashar for bug # 11812969 */--added round for bug#7479016
2221 v_other_purpose := 'Domestic Without Excise';
2222 END IF;
2223
2224 IF p_register_code NOT IN ('EXPORT_EXCISE','DOMESTIC_EXCISE') THEN
2225 v_excise_duty_amount := '';
2226 END IF;
2227
2228 /*Bug 9550254 - Start*/
2229 /*
2230 OPEN serial_no_cur;
2231 FETCH serial_no_cur INTO v_previous_serial_no, v_serial_no;
2232 CLOSE serial_no_cur;
2233
2234 OPEN packed_loose_qty_cur(v_previous_serial_no);
2235 FETCH packed_loose_qty_cur INTO v_balance_packed, v_balance_loose;
2236 CLOSE packed_loose_qty_cur;
2237 */
2238 /*Code modified to fetch the Opening Balance when no transactions currently exist in JAI_CMN_RG_I_TRXS*/
2239 v_balance_loose := ja_in_rgi_balance(p_org_id,p_location_id,p_inventory_item_id,p_fin_year,
2240 v_previous_serial_no,v_balance_packed);
2241
2242 IF NVL(v_previous_serial_no,0) = 0 then
2243 v_serial_no := 1;
2244 ELSE
2245 v_serial_no := v_previous_serial_no + 1;
2246 END IF;
2247 /*Bug 9550254 - End*/
2248
2249 v_packed_loose_qty := v_conversion_rate * NVL(p_excise_quantity,0);
2250
2251 IF (v_balance_packed + v_balance_loose) >= v_packed_loose_qty THEN
2252 IF v_balance_loose >= v_packed_loose_qty THEN
2253 v_balance_loose := v_balance_loose - v_packed_loose_qty;
2254 ELSE
2255 v_balance_packed := v_balance_packed - (v_packed_loose_qty - v_balance_loose);
2256 v_balance_loose := 0;
2257 END IF;
2258 ELSE
2259
2260 /*bug 9122545*/
2261 OPEN c_org_addl_rg_flag(p_org_id, p_location_id );
2262 FETCH c_org_addl_rg_flag INTO lv_allow_negative_rg_flag;
2263 CLOSE c_org_addl_rg_flag ;
2264
2265 IF lv_allow_negative_rg_flag = 'Y'
2266 THEN
2267 IF v_balance_loose >= v_packed_loose_qty THEN
2268 v_balance_loose := v_balance_loose - v_packed_loose_qty;
2269 ELSE
2270 v_balance_packed := v_balance_packed - (v_packed_loose_qty - v_balance_loose);
2271 v_balance_loose := 0;
2272 END IF;
2273 ELSIF lv_allow_negative_rg_flag = 'N'
2274 THEN
2275 RAISE_APPLICATION_ERROR(-20199, 'Enough RG1 balance is not available to Issue the Goods');
2276 v_left_balance := v_balance_loose + v_balance_packed;
2277 v_balance_loose := 0;
2278 v_balance_packed := 0;
2279 v_packed_loose_qty := v_packed_loose_qty - v_left_balance;
2280 v_balance_loose := v_balance_loose - v_packed_loose_qty;
2281 END IF;
2282 /*end bug 9122545*/
2283 END IF;
2284 -----------------------------------------------------------------------------
2285
2286 /* Vijay Shankar for Bug# 3604540
2287 OPEN rg_manufactured_loose_cur;
2288 FETCH rg_manufactured_loose_cur INTO V_ITEM_CLASS_ISSUE;
2289 CLOSE rg_manufactured_loose_cur ;
2290
2291 IF p_transaction_id = 33 THEN
2292 IF v_item_class_issue = 'FGIN' THEN
2293 v_manufactured_loose_qty := v_packed_loose_qty ;
2294 END IF;
2295 END IF;
2296 */
2297
2298 INSERT INTO JAI_CMN_RG_I_TRXS(
2299 Register_ID ,
2300 Fin_Year ,
2301 SLNO ,
2302 Organization_id ,
2303 Location_id ,
2304 Inventory_Item_id ,
2305 TRANSACTION_SOURCE_NUM ,
2306 Transaction_Type ,
2307 Transaction_date ,
2308 REF_DOC_NO ,
2309 manufactured_qty ,
2310 manufactured_packed_qty ,
2311 manufactured_loose_qty ,
2312 other_purpose_n_pay_ed_qty ,
2313 other_purpose_n_pay_ed_val ,
2314 for_export_pay_ed_qty ,
2315 for_export_pay_ed_val ,
2316 for_export_n_pay_ed_qty ,
2317 for_export_n_pay_ed_val ,
2318 for_home_use_pay_ed_qty ,
2319 for_home_use_pay_ed_val ,
2320 primary_uom_code ,
2321 transaction_uom_code ,
2322 balance_packed ,
2323 balance_loose ,
2324 issue_type ,
2325 payment_register ,
2326 excise_invoice_number ,
2327 excise_invoice_date ,
2328 excise_duty_amount ,
2329 basic_ed ,
2330 additional_ed ,
2331 other_ed ,
2332 excise_duty_rate ,
2333 customer_id ,
2334 customer_site_id ,
2335 range_no ,
2336 division_no ,
2337 creation_date ,
2338 created_by ,
2339 last_update_login ,
2340 last_update_date ,
2341 last_updated_by ,
2342 other_purpose ,
2343 to_other_factory_n_pay_ed_qty ,
2344 to_other_factory_n_pay_ed_val , -- Vijay Shankar for Bug# 3408210
2345 cess_amt , /* The columns cess_amt and source have been added by aiyer for the bug 4566054*/
2346 sh_cess_amt , /*Bug 5989740 bduvarag*/
2347 source
2348
2349 ) VALUES (
2350 jai_cmn_rg_i_trxs_s.nextval ,
2351 p_fin_year ,
2352 v_serial_no ,
2353 p_org_id ,
2354 p_location_id ,
2355 p_inventory_item_id ,
2356 p_transaction_id ,
2357 p_transaction_type ,
2358 TRUNC(p_transaction_date) ,
2359 p_header_id ,
2360 round(v_manufactured_qty,5) ,
2361 round(v_manufactured_packed_qty ,5) ,
2362 round(v_manufactured_loose_qty ,5) ,
2363 round(v_other_purpose_n_pay_ed_qty ,5) ,
2364 v_other_purpose_n_pay_ed_val ,
2365 round(v_for_export_pay_ed_qty ,5) ,
2366 v_for_export_pay_ed_val ,
2367 round(v_for_export_n_pay_ed_qty ,5) ,
2368 v_for_export_n_pay_ed_val ,
2369 round(v_home_use_pay_ed_qty ,5) ,
2370 v_home_use_pay_ed_val ,
2371 v_primary_uom_code ,
2372 p_uom_code ,
2373 round(v_balance_packed ,5) ,
2374 round(v_balance_loose ,5) ,
2375 v_issue_type ,
2376 p_payment_register ,
2377 p_excise_invoice_no ,
2378 p_excise_invoice_date ,
2379 v_excise_duty_amount ,
2380 v_basic_ed ,
2381 v_additional_ed ,
2382 v_other_ed ,
2383 p_excise_duty_rate ,
2384 p_customer_id ,
2385 p_customer_site_id ,
2386 v_range_no ,
2387 v_division_no ,
2388 p_creation_date ,
2389 p_created_by ,
2390 p_last_update_login ,
2391 p_last_update_date ,
2392 p_last_updated_by ,
2393 v_other_purpose ,
2394 round(v_to_other_fact_n_pay_ed_qty ,5) ,
2395 round(v_to_other_fact_n_pay_ed_val ,5) , -- Vijay Shankar for Bug# 3408210
2396 p_cess_amt , /* The columns cess_amt and source have been added by aiyer for the bug 4566054*/
2397 p_sh_cess_amt ,/*Bug 5989740 bduvarag*/
2398 p_source
2399 );
2400 EXCEPTION
2401 WHEN OTHERS THEN
2402 FND_MESSAGE.SET_NAME ('JA','JAI_EXCEPTION_OCCURED');
2403 FND_MESSAGE.SET_TOKEN ('JAI_PROCESS_MSG',lv_object_name ||'.Err:'||sqlerrm);
2404 app_exception.raise_exception;
2405 END ja_in_rg_I_entry;
2406 /***************************** JA_IN_RG_I_ENTRY *******************************************************************/
2407
2408
2409
2410 /***************************** JA_IN_RG23_PART_I_ENTRY ****************************************************************/
2411 PROCEDURE ja_in_rg23_part_I_entry(p_register_type VARCHAR2,p_fin_year NUMBER, p_org_id NUMBER, p_location_id NUMBER,
2412 p_inventory_item_id NUMBER,p_transaction_id NUMBER, p_transaction_date DATE, p_transaction_type VARCHAR2,
2413 p_excise_quantity NUMBER, p_uom_code VARCHAR2, p_excise_invoice_id VARCHAR2,
2414 p_excise_invoice_date DATE, p_basic_ed NUMBER, p_additional_ed NUMBER,
2415 p_other_ed NUMBER, p_customer_id NUMBER, p_customer_site_id NUMBER,
2416 p_header_id VARCHAR2,/*Changes by nprashar for bug # 6710747NUMBER,*/ p_sales_invoice_date DATE, p_register_code VARCHAR2,
2417 p_creation_date DATE, p_created_by NUMBER,p_last_update_date DATE,
2418 p_last_updated_by NUMBER, p_last_update_login NUMBER
2419 ) IS
2420 v_opening_quantity NUMBER := 0;
2421 v_closing_quantity NUMBER := 0;
2422 v_basic_ed NUMBER; -- := p_basic_ed; --Ramananda for File.Sql.35
2423 v_additional_ed NUMBER; -- := p_additional_ed; --Ramananda for File.Sql.35
2424 v_other_ed NUMBER; -- := p_other_ed; --Ramananda for File.Sql.35
2425 v_conversion_rate NUMBER := 0;
2426 v_previous_serial_no NUMBER := 0;
2427 v_serial_no NUMBER := 0;
2428 v_goods_issue_id VARCHAR2(20);
2429 v_primary_uom_code VARCHAR2(20);
2430 v_range_no VARCHAR2(50);
2431 v_division_no VARCHAR2(50);
2432 v_excise_quantity NUMBER; -- := p_excise_quantity; --Ramananda for File.Sql.35
2433
2434 CURSOR primary_uom_cur IS
2435 SELECT primary_uom_code
2436 FROM mtl_system_items
2437 WHERE inventory_item_id = p_inventory_item_id AND
2438 organization_id = p_org_id;
2439 CURSOR serial_no_cur IS
2440 SELECT NVL(MAX(slno),0) , NVL(MAX(slno),0) + 1
2441 FROM JAI_CMN_RG_23AC_I_TRXS
2442 WHERE organization_id = p_org_id AND
2443 location_id = p_location_id AND
2444 inventory_item_id = p_inventory_item_id AND
2445 fin_year = p_fin_year AND
2446 register_type = p_register_type;
2447 CURSOR opening_balance_qty_cur(p_previous_serial_no IN NUMBER) IS
2448 SELECT NVL(opening_balance_qty,0), NVL(closing_balance_qty,0)
2449 FROM JAI_CMN_RG_23AC_I_TRXS
2450 WHERE slno = p_previous_serial_no AND
2451 organization_id = p_org_id AND
2452 location_id = p_location_id AND
2453 register_type = p_register_type AND
2454 fin_year = p_fin_year AND
2455 inventory_item_id = p_inventory_item_id;
2456 CURSOR range_division_cur IS
2457 SELECT excise_duty_range,excise_duty_division
2458 FROM JAI_CMN_CUS_ADDRESSES
2459 WHERE customer_id = p_customer_id
2460 AND address_id = (SELECT cust_acct_site_id -- address_id
2461 FROM hz_cust_site_uses_all A -- Removed ra_site_uses_all for BUg# 4434287
2462 WHERE A.site_use_id = p_customer_site_id);
2463 lv_object_name CONSTANT VARCHAR2 (61) := 'jai_om_rg_pkg.ja_in_rg23_part_I_entry';
2464 BEGIN
2465
2466 v_basic_ed := p_basic_ed; --Ramananda for File.Sql.35
2467 v_additional_ed := p_additional_ed; --Ramananda for File.Sql.35
2468 v_other_ed := p_other_ed; --Ramananda for File.Sql.35
2469 v_excise_quantity := p_excise_quantity; --Ramananda for File.Sql.35
2470
2471 OPEN primary_uom_cur;
2472 FETCH primary_uom_cur INTO v_primary_uom_code;
2473 CLOSE primary_uom_cur;
2474 OPEN RANGE_DIVISION_CUR;
2475 FETCH RANGE_DIVISION_CUR INTO v_range_no,v_division_no;
2476 CLOSE RANGE_DIVISION_CUR;
2477 Inv_Convert.inv_um_conversion(p_uom_code, v_primary_uom_code, p_inventory_item_id,v_conversion_rate);
2478 IF NVL(v_conversion_rate, 0) <= 0 THEN
2479 Inv_Convert.inv_um_conversion(p_uom_code, v_primary_uom_code, 0, v_conversion_rate);
2480 IF NVL(v_conversion_rate, 0) <= 0 THEN
2481 v_conversion_rate := 0;
2482 END IF;
2483 END IF;
2484 v_excise_quantity := NVL(v_excise_quantity,0) * v_conversion_rate;
2485 /*Bug 9550254 - Start*/
2486 /*
2487 OPEN serial_no_cur;
2488 FETCH serial_no_cur INTO v_previous_serial_no, v_serial_no;
2489 CLOSE serial_no_cur;
2490 */
2491 v_opening_quantity := ja_in_rg23i_balance(p_org_id,p_location_id,p_inventory_item_id,p_fin_year,p_register_type,v_previous_serial_no);
2492 /*Bug 9550254 - End*/
2493 IF NVL(v_previous_serial_no,0) = 0 THEN
2494 v_previous_serial_no := 0;
2495 v_serial_no := 1;
2496 -- v_opening_quantity := 0; /*Commented for Bug 9550254*/
2497 v_closing_quantity := v_opening_quantity - v_excise_quantity;
2498 ELSE
2499 v_serial_no := v_previous_serial_no + 1; /*Added for Bug 9550254*/
2500 OPEN opening_balance_qty_cur(v_previous_serial_no);
2501 FETCH opening_balance_qty_cur INTO v_opening_quantity, v_closing_quantity;
2502 CLOSE opening_balance_qty_cur;
2503 IF NVL(v_closing_quantity,0) = 0
2504 THEN
2505 v_opening_quantity := 0;
2506 v_closing_quantity := NVL(v_opening_quantity,0) - NVL(v_excise_quantity,0);
2507 ELSE
2508 v_opening_quantity := v_closing_quantity;
2509 v_closing_quantity := NVL(v_opening_quantity,0) - NVL(v_excise_quantity,0);
2510 END IF;
2511 END IF;
2512 IF p_register_code NOT IN ('EXPORT_EXCISE','DOMESTIC_EXCISE') THEN
2513 v_basic_ed := '';
2514 v_additional_ed := '';
2515 v_other_ed := '';
2516 END IF;
2517 INSERT INTO JAI_CMN_RG_23AC_I_TRXS (register_id, fin_year, slno, inventory_item_id, organization_id,
2518 location_id, TRANSACTION_SOURCE_NUM, transaction_type, transaction_date,
2519 customer_id, customer_site_id, range_no, division_no,
2520 SALES_INVOICE_NO, sales_invoice_quantity, sales_invoice_date,
2521 EXCISE_INVOICE_NO, excise_invoice_date, register_type,
2522 transaction_uom_code, primary_uom_code, basic_ed, additional_ed,
2523 other_ed, opening_balance_qty, closing_balance_qty,
2524 creation_date,created_by,last_update_login,
2525 last_update_date,last_updated_by)
2526 VALUES(JAI_CMN_RG_23AC_I_TRXS_S.NEXTVAL, p_fin_year, v_serial_no, p_inventory_item_id, p_org_id,/*rchandan for bug#4487676*/
2527 p_location_id, p_transaction_id, p_transaction_type, TRUNC(p_transaction_date),
2528 p_customer_id,p_customer_site_id,v_range_no,v_division_no,
2529 p_excise_invoice_id, round(v_excise_quantity,5), p_excise_invoice_date,
2530 p_header_id, p_sales_invoice_date, p_register_type,
2531 p_uom_code, v_primary_uom_code, v_basic_ed, v_additional_ed,
2532 v_other_ed,round( v_opening_quantity,5), round(v_closing_quantity,5),
2533 p_creation_date, p_created_by, p_last_update_login,
2534 p_last_update_date, p_last_updated_by );
2535 EXCEPTION
2536 WHEN OTHERS THEN
2537 FND_MESSAGE.SET_NAME ('JA','JAI_EXCEPTION_OCCURED');
2538 FND_MESSAGE.SET_TOKEN ('JAI_PROCESS_MSG',lv_object_name ||'.Err:'||sqlerrm);
2539 app_exception.raise_exception;
2540 END ja_in_rg23_part_I_entry;
2541 /***************************** JA_IN_RG23_PART_I_ENTRY ****************************************************************/
2542
2543
2544 /***************************** JA_IN_RG23_PART_II_ENTRY ****************************************************************/
2545 -- This procedure has been modified by by Aparajita on 03/08/2002 for calling the accounting entries procedure.
2546 -- Bug # 2496388
2547
2548 PROCEDURE ja_in_rg23_part_II_entry(p_register_code VARCHAR2 , p_register_type VARCHAR2,
2549 p_fin_year NUMBER , p_org_id NUMBER ,
2550 p_location_id NUMBER , p_inventory_item_id NUMBER ,
2551 p_transaction_id NUMBER , p_transaction_date DATE ,
2552 p_part_i_register_id NUMBER , p_excise_invoice_no VARCHAR2,
2553 p_excise_invoice_date DATE , p_dr_basic_ed NUMBER ,
2554 p_dr_additional_ed NUMBER , p_dr_other_ed NUMBER ,
2555 p_customer_id NUMBER , p_customer_site_id NUMBER ,
2556 p_source_name VARCHAR2 , p_category_name VARCHAR2,
2557 p_creation_date DATE , p_created_by NUMBER ,
2558 p_last_update_date DATE , p_last_updated_by NUMBER ,
2559 p_last_update_login NUMBER , p_picking_line_id NUMBER DEFAULT NULL,
2560 p_excise_exempt_type VARCHAR2 DEFAULT NULL,
2561 p_remarks VARCHAR2 DEFAULT NULL ,
2562 P_REF_10 VARCHAR2 DEFAULT NULL ,-- added by sriram - bug # 2769440
2563 P_REF_23 VARCHAR2 DEFAULT NULL ,-- added by sriram - bug # 2769440
2564 P_REF_24 VARCHAR2 DEFAULT NULL ,-- added by sriram - bug # 2769440
2565 P_REF_25 VARCHAR2 DEFAULT NULL ,-- added by sriram - bug # 2769440
2566 P_REF_26 VARCHAR2 DEFAULT NULL -- added by sriram - bug # 2769440
2567 ) IS
2568
2569 v_opening_balance NUMBER := 0;
2570 v_closing_balance NUMBER := 0;
2571 v_account_id NUMBER := 0;
2572 v_sh_cess_account_id Number := 0;/*Bug 5989740 bduvarag*/
2573 v_cess_account_id Number := 0;
2574 v_previous_serial_no NUMBER := 0;
2575 v_serial_no NUMBER := 0;
2576 v_rg_balance NUMBER := 0;
2577 v_range_no VARCHAR2(50);
2578 v_division_no VARCHAR2(50);
2579 v_debit_account_id NUMBER := NULL;
2580 v_sh_cess_debit_account_id number := NULL;/*Bug 5989740 bduvarag*/
2581 v_cess_debit_account_id number := NULL; /* added by ssumaith for CESS Solution*/
2582 v_currency_code VARCHAR2(10) := 0;
2583 v_excise_amount NUMBER := 0;
2584 v_ssi_unit_flag VARCHAR2(1);
2585 ln_cess_amount number;
2586 ln_sh_cess_amount number;/*Bug 5989740 bduvarag*/
2587
2588 CURSOR balance_cur(p_previous_serial_no IN NUMBER) IS
2589 SELECT NVL(opening_balance,0),NVL(closing_balance,0)
2590 FROM JAI_CMN_RG_23AC_II_TRXS
2591 WHERE organization_id = p_org_id AND
2592 location_id = p_location_id AND
2593 slno = p_previous_serial_no AND
2594 register_type = p_register_type AND
2595 fin_year = p_fin_year;
2596
2597 CURSOR rg23a_part_ii_balance_cur IS
2598 SELECT NVL(rg23a_balance,0)
2599 FROM JAI_CMN_RG_BALANCES
2600 WHERE organization_id = p_org_id AND
2601 location_id = p_location_id;
2602
2603 CURSOR rg23c_part_ii_balance_cur IS
2604 SELECT NVL(rg23c_balance,0)
2605 FROM JAI_CMN_RG_BALANCES
2606 WHERE organization_id = p_org_id AND
2607 location_id = p_location_id;
2608
2609 CURSOR serial_no_cur IS
2610 SELECT NVL(MAX(slno),0) , NVL(MAX(slno),0) + 1
2611 FROM JAI_CMN_RG_23AC_II_TRXS
2612 WHERE organization_id = p_org_id AND
2613 location_id = p_location_id AND
2614 fin_year = p_fin_year AND
2615 register_type = p_register_type;
2616
2617 CURSOR range_division_cur IS
2618 SELECT excise_duty_range, excise_duty_division
2619 FROM JAI_CMN_CUS_ADDRESSES
2620 WHERE customer_id = p_customer_id
2621 AND address_id = (SELECT cust_acct_site_id -- address_id
2622 FROM hz_cust_site_uses_all A -- Removed ra_site_uses_all for Bug# 4434287
2623 WHERE A.site_use_id = p_customer_site_id);
2624 CURSOR rm_account_cur IS
2625 SELECT modvat_rm_account_id , excise_edu_cess_rm_account ,SH_CESS_RM_ACCOUNT/*Bug 5989740 bduvarag*/
2626 FROM JAI_CMN_INVENTORY_ORGS
2627 WHERE organization_id = p_org_id AND location_id = p_location_id;
2628
2629 CURSOR cg_account_cur IS
2630 SELECT modvat_cg_account_id , excise_edu_cess_cg_account ,SH_CESS_CG_ACCOUNT_ID/*Bug 5989740 bduvarag*/
2631 FROM JAI_CMN_INVENTORY_ORGS
2632 WHERE organization_id = p_org_id AND location_id = p_location_id;
2633
2634 CURSOR debit_account_cur IS
2635 SELECT EXCISE_RCVBLE_ACCOUNT , CESS_PAID_PAYABLE_ACCOUNT_ID , /* CESS_PAID_PAYABLE_ACCOUNT_ID added by ssumaith */
2636 SH_CESS_PAID_PAYABLE_ACCT_ID/*Bug 5989740 bduvarag*/
2637 FROM JAI_CMN_INVENTORY_ORGS
2638 WHERE organization_id = p_org_id AND location_id = p_location_id;
2639
2640 CURSOR mod_debit_acc_org IS
2641 SELECT MODVAT_REVERSE_ACCOUNT_ID
2642 FROM JAI_CMN_INVENTORY_ORGS
2643 WHERE organization_id = p_org_id
2644 AND location_id = p_location_id;
2645
2646 /* Bug 4931887. Added by Lakshmi Gopalsami
2647 Removed the references to currency_cur
2648 and implemented the same using the global cursor
2649 get_curr_code which is defined in package specification
2650 */
2651
2652
2653 CURSOR ssi_unit_flag_cur IS
2654 SELECT ssi_unit_flag
2655 FROM JAI_CMN_INVENTORY_ORGS
2656 WHERE organization_id = p_org_id AND
2657 location_id = p_location_id;
2658
2659
2660 ln_register_id number;
2661
2662 lv_object_name CONSTANT VARCHAR2 (61) := 'jai_om_rg_pkg.ja_in_rg23_part_II_entry';
2663 --Added by Xiao for Open Interface ER bug#11683927 on 03-Mar-2011, begin
2664 --------------------------------------------------------------------------------------
2665 /*CURSOR get_external_flag_cur IS
2666 SELECT COUNT(lines.customer_trx_id)
2667 FROM jai_ar_trx_lines lines,
2668 jai_interface_lines_all intfs
2669 WHERE lines.customer_trx_line_id = intfs.internal_trx_line_id
2670 AND lines.customer_trx_id IN (SELECT customer_trx_id
2671 FROM jai_ar_trx_lines
2672 WHERE customer_trx_line_id = p_picking_line_id)
2673 AND lines.interface_flag = 'Y'
2674 AND intfs.taxable_event = 'EXTERNAL';
2675
2676 CURSOR get_external_tax_cur IS
2677 SELECT tax.code_combination_id
2678 , tax.tax_amount
2679 FROM jai_ar_trx_lines jatl
2680 , jai_ar_trx_tax_lines jattl
2681 , jai_interface_lines_all lines
2682 , jai_interface_tax_lines_all tax
2683 WHERE jatl.customer_trx_line_id = p_picking_line_id
2684 AND jattl.customer_trx_line_id = p_picking_line_id
2685 AND tax.tax_line_no = jattl.tax_line_no
2686 AND tax.interface_line_id = lines.interface_line_id
2687 AND lines.internal_trx_id = jatl.customer_trx_id
2688 AND lines.internal_trx_line_id = p_picking_line_id;
2689
2690 ln_external_flag NUMBER;*/--Comments by zhiwei for Bug#12537533
2691 --------------------------------------------------------------------------------------
2692 --Added by Xiao for Open Interface ER bug#11683927 on 03-Mar-2011, end
2693 BEGIN
2694
2695
2696 OPEN RANGE_DIVISION_CUR;
2697 FETCH RANGE_DIVISION_CUR INTO v_range_no,v_division_no;
2698 CLOSE RANGE_DIVISION_CUR;
2699
2700 Fnd_File.PUT_LINE(Fnd_File.LOG, ' in rg pkg p_excise_exempt_type is ' || p_excise_exempt_type);
2701
2702 IF p_excise_exempt_type IN ('CT2', 'EXCISE_EXEMPT_CERT','CT2_OTH', 'EXCISE_EXEMPT_CERT_OTH' ,'CT3') THEN
2703
2704 -- CT3 added by sriram into the list. Bug # 2796717. 16/mar/2003
2705 OPEN mod_debit_acc_org;
2706 FETCH mod_debit_acc_org INTO v_debit_account_id;
2707 CLOSE mod_debit_acc_org;
2708 /*
2709 || Bug# 4245053
2710 || Following line of code added by ssumaith . During VAT testing it came up , excise exempted transaction was
2711 || not hitting the rg register , it was showing an error that cess accounts are not defined.
2712 || Discussed with product management and functional team , and used their input that cenvat reversal account can be used
2713 */
2714 v_cess_debit_account_id := v_debit_account_id;
2715 v_sh_cess_debit_account_id := v_debit_account_id;/*Bug 5989740 bduvarag*/
2716 ELSE
2717 OPEN debit_account_cur;
2718 FETCH debit_account_cur INTO v_debit_account_id , v_cess_debit_account_id,v_sh_cess_debit_account_id ; /*Bug 5989740 bduvarag*/
2719 CLOSE debit_account_cur;
2720 END IF;
2721
2722
2723 IF v_debit_account_id IS NULL THEN
2724 OPEN debit_account_cur;
2725 FETCH debit_account_cur INTO v_debit_account_id, v_cess_debit_account_id,v_sh_cess_debit_account_id /*Bug 5989740 bduvarag*/;
2726 CLOSE debit_account_cur;
2727 END IF;
2728
2729 Fnd_File.PUT_LINE(Fnd_File.LOG, ' debit account id is ' || v_debit_account_id);
2730 Fnd_File.PUT_LINE(Fnd_File.LOG, ' Cess debit account id is ' || v_cess_debit_account_id);
2731 Fnd_File.PUT_LINE(Fnd_File.LOG, ' SH Cess debit account id is ' || v_sh_cess_debit_account_id);
2732 --Added for bug#7277543
2733 IF v_debit_account_id IS NULL OR v_cess_debit_account_id IS NULL OR v_sh_cess_debit_account_id IS NULL THEN
2734 Fnd_File.PUT_LINE(Fnd_File.LOG, 'Debit or Cess Debit or SH Cess Debit Accounts have not been setup in Organization Additional Information Screen ' );
2735 END IF;
2736 --End bug#7277543
2737 /* Bug 4931887. Added by Lakshmi Gopalsami
2738 Re-used the cursor get_curr_code for fixing perf. issue reported.
2739 */
2740 OPEN get_curr_code(p_org_id, p_location_id);
2741 FETCH get_curr_code INTO v_currency_code;
2742 CLOSE get_curr_code;
2743
2744 OPEN ssi_unit_flag_cur;
2745 FETCH ssi_unit_flag_cur INTO v_ssi_unit_flag;
2746 CLOSE ssi_unit_flag_cur;
2747
2748 Fnd_File.PUT_LINE(Fnd_File.LOG, ' p_register_type is ' || p_register_type);
2749 IF p_register_type = 'A' THEN
2750 OPEN rm_account_cur;
2751 FETCH rm_account_cur INTO v_account_id , v_cess_account_id,v_sh_cess_account_id; /*Bug 5989740 bduvarag*/
2752 CLOSE rm_account_cur;
2753 ELSIF p_register_type = 'C' THEN
2754 OPEN cg_account_cur;
2755 FETCH cg_account_cur INTO v_account_id ,v_cess_account_id,v_sh_cess_account_id; /*Bug 5989740 bduvarag*/
2756 CLOSE cg_account_cur;
2757 END IF;
2758
2759 OPEN serial_no_cur;
2760 FETCH serial_no_cur INTO v_previous_serial_no, v_serial_no;
2761 CLOSE serial_no_cur;
2762
2763 Fnd_File.PUT_LINE(Fnd_File.LOG, ' v_previous_serial_no is ' || v_previous_serial_no);
2764 IF NVL(v_previous_serial_no,0) = 0 THEN
2765 v_previous_serial_no := 0;
2766 v_serial_no := 1;
2767 END IF;
2768
2769 Fnd_File.PUT_LINE(Fnd_File.LOG, ' v_previous_serial_no is ' || v_previous_serial_no);
2770 IF NVL(v_previous_serial_no,0) > 0 THEN
2771
2772 OPEN balance_cur(v_previous_serial_no);
2773 FETCH balance_cur INTO v_opening_balance, v_closing_balance;
2774 CLOSE balance_cur;
2775
2776 v_opening_balance := v_closing_balance;
2777 v_closing_balance := v_closing_balance - (NVL(p_dr_basic_ed,0) + NVL(p_dr_additional_ed,0) + NVL(p_dr_other_ed,0));
2778
2779 Fnd_File.PUT_LINE(Fnd_File.LOG, ' opening and closing balances : ' || v_opening_balance || ' and ' || v_closing_balance);
2780 IF NVL(v_closing_balance,0) < 0 THEN
2781 IF NVL(v_ssi_unit_flag,'N') = 'N' THEN
2782 Fnd_File.PUT_LINE(Fnd_File.LOG, 'Cannot Debit more than the RG23 PART II Opening Balance ---'|| TO_CHAR(v_opening_balance)); --Added for bug#7172215
2783 RAISE_APPLICATION_ERROR(-20120, 'Cannot Debit more than the RG23 PART II Opening Balance ---'|| TO_CHAR(v_opening_balance));
2784 END IF;
2785 END IF;
2786
2787 ELSE
2788
2789 IF p_register_type = 'A' THEN
2790
2791 OPEN rg23a_part_ii_balance_cur;
2792 FETCH rg23a_part_ii_balance_cur INTO v_rg_balance;
2793 CLOSE rg23a_part_ii_balance_cur;
2794
2795 ELSE
2796 OPEN rg23c_part_ii_balance_cur;
2797 FETCH rg23c_part_ii_balance_cur INTO v_rg_balance;
2798 CLOSE rg23c_part_ii_balance_cur;
2799 END IF;
2800
2801 v_opening_balance := NVL(v_rg_balance,0);
2802 v_closing_balance := NVL(v_rg_balance,0) - (NVL(p_dr_basic_ed,0) + NVL(p_dr_additional_ed,0) + NVL(p_dr_other_ed,0));
2803
2804 IF v_closing_balance < 0 THEN
2805 IF NVL(v_ssi_unit_flag,'N') = 'N' THEN
2806 Fnd_File.PUT_LINE(Fnd_File.LOG, 'Cannot Debit more than the RG23 PART II Opening Balance ---'|| TO_CHAR(v_rg_balance)); --Added for bug#7172215
2807 RAISE_APPLICATION_ERROR(-20120, 'Cannot Debit more than the RG23 PART II Opening Balance '|| TO_CHAR(v_rg_balance));
2808 END IF;
2809 END IF;
2810
2811 END IF;
2812 Fnd_File.PUT_LINE(Fnd_File.LOG, 'before insert into JAI_CMN_RG_23AC_II_TRXS');
2813 --Added by Xiao for Open Interface ER bug#11683927 on 03-Mar-2011, begin
2814 --------------------------------------------------------------------------------------
2815 /*
2816 OPEN get_external_flag_cur;
2817 FETCH get_external_flag_cur INTO ln_external_flag;
2818 CLOSE get_external_flag_cur;
2819
2820 IF ln_external_flag > 0 AND p_register_code <>'BOND_REG' THEN
2821
2822 OPEN get_external_tax_cur;
2823 FETCH get_external_tax_cur INTO v_account_id, v_excise_amount;
2824 CLOSE get_external_tax_cur;
2825
2826 ja_in_accounting_entries
2827 (
2828 p_org_id,
2829 p_location_id,
2830 v_currency_code,
2831 v_excise_amount,
2832 p_source_name ,
2833 p_category_name,
2834 p_created_by ,
2835 p_picking_line_id,
2836 v_account_id,
2837 v_debit_account_id,
2838 P_REF_10,
2839 P_REF_23,
2840 P_REF_24,
2841 P_REF_25,
2842 P_REF_26
2843 );
2844
2845 RETURN;
2846 END IF ; */--Comments by zhiwei for Bug#12537533
2847 --------------------------------------------------------------------------------------
2848 --Added by Xiao for Open Interface ER bug#11683927 on 03-Mar-2011, end
2849
2850 select JAI_CMN_RG_23AC_II_TRXS_S.NEXTVAL
2851 into ln_register_id
2852 from dual;
2853
2854 INSERT INTO JAI_CMN_RG_23AC_II_TRXS (register_id,
2855 fin_year,
2856 slno,
2857 inventory_item_id,
2858 organization_id,
2859 location_id,
2860 TRANSACTION_SOURCE_NUM,
2861 transaction_date,
2862 customer_id,
2863 customer_site_id,
2864 range_no,
2865 division_no,
2866 excise_invoice_no,
2867 excise_invoice_date,
2868 register_type,
2869 dr_basic_ed,
2870 dr_additional_ed,
2871 dr_other_ed,
2872 opening_balance,
2873 closing_balance,
2874 charge_account_id,
2875 register_id_part_i,
2876 creation_date,
2877 created_by,
2878 last_update_login,
2879 last_update_date,
2880 last_updated_by,
2881 remarks
2882 )
2883 VALUES(
2884 ln_register_id,
2885 p_fin_year,
2886 v_serial_no,
2887 p_inventory_item_id,
2888 p_org_id,
2889 p_location_id,
2890 p_transaction_id,
2891 p_transaction_date,
2892 p_customer_id,
2893 p_customer_site_id,
2894 v_range_no,
2895 v_division_no,
2896 p_excise_invoice_no,
2897 p_excise_invoice_date,
2898 p_register_type,
2899 p_dr_basic_ed,
2900 p_dr_additional_ed,
2901 p_dr_other_ed,
2902 v_opening_balance,
2903 v_closing_balance,
2904 v_debit_account_id,
2905 p_part_i_register_id,
2906 p_creation_date,
2907 p_created_by,
2908 p_last_update_login,
2909 p_last_update_date,
2910 p_last_updated_by,
2911 p_remarks
2912 );
2913
2914 Fnd_File.PUT_LINE(Fnd_File.LOG, 'after insert into JAI_CMN_RG_23AC_II_TRXS');
2915 -- Code has been modified here by Aparajita on 08/03/2002 for bug # 2496388
2916
2917
2918 IF p_register_code <>'BOND_REG' THEN
2919
2920 v_excise_amount := NVL(p_dr_basic_ed,0) + NVL(p_dr_additional_ed,0) + NVL(p_dr_other_ed,0);
2921 Fnd_File.PUT_LINE(Fnd_File.LOG, 'before calling ja_in_accounting entries procedure');
2922 ja_in_accounting_entries
2923 (
2924 p_org_id,
2925 p_location_id,
2926 v_currency_code,
2927 v_excise_amount,
2928 p_source_name ,
2929 p_category_name,
2930 p_created_by ,
2931 p_picking_line_id,
2932 v_account_id,
2933 v_debit_account_id,
2934 P_REF_10,
2935 P_REF_23,
2936 P_REF_24,
2937 P_REF_25,
2938 P_REF_26
2939 );
2940 Fnd_File.PUT_LINE(Fnd_File.LOG, 'after calling ja_in_accounting entries procedure');
2941
2942
2943 ja_in_cess_register_entries(p_register_id => ln_register_id ,
2944 p_register_type => p_register_type ,
2945 p_inv_orgn_id => p_org_id ,
2946 p_je_source_name => p_source_name ,
2947 p_je_category_name => p_category_name ,
2948 p_source_type => 1 ,
2949 p_currency_code => v_currency_code ,
2950 p_transaction_hdr_id => p_picking_line_id , /* delivery detail id in case of om , trx line id in case of ar*/
2951 p_debit_account => v_cess_debit_account_id ,
2952 p_Credit_account => v_cess_account_id ,
2953 p_cess_amount => ln_cess_amount ,
2954 p_cess_type => 'EXC' , /*Bug 5989740 bduvarag*/
2955 P_REFERENCE_10 => P_REF_10 ,
2956 P_REFERENCE_23 => P_REF_23 ,
2957 P_REFERENCE_24 => P_REF_24 ,
2958 P_REFERENCE_25 => P_REF_25 ,
2959 P_REFERENCE_26 => P_REF_26
2960 );
2961
2962
2963
2964 ja_in_cess_register_entries(p_register_id => ln_register_id ,
2965 p_register_type => p_register_type ,
2966 p_inv_orgn_id => p_org_id ,
2967 p_je_source_name => p_source_name ,
2968 p_je_category_name => p_category_name ,
2969 p_source_type => 1 ,
2970 p_currency_code => v_currency_code ,
2971 p_transaction_hdr_id => p_picking_line_id , /* delivery detail id in case of om , trx line id in case of ar*/
2972 p_debit_account => v_sh_cess_debit_account_id ,
2973 p_Credit_account => v_sh_cess_account_id ,
2974 p_cess_amount => ln_sh_cess_amount ,
2975 p_cess_type => 'SH' , /*Bug 5989740 bduvarag*/
2976 P_REFERENCE_10 => P_REF_10 ,
2977 P_REFERENCE_23 => P_REF_23 ,
2978 P_REFERENCE_24 => P_REF_24 ,
2979 P_REFERENCE_25 => P_REF_25 ,
2980 P_REFERENCE_26 => P_REF_26
2981 );
2982
2983 update JAI_CMN_RG_23AC_II_TRXS
2984 set other_tax_debit = ln_cess_amount + ln_sh_cess_amount/*Bug 5989740 bduvarag*/
2985 where register_id = ln_register_id;
2986
2987 END IF;
2988
2989 IF p_register_type = 'A' THEN
2990
2991 UPDATE JAI_CMN_RG_BALANCES
2992 SET rg23a_balance = rg23a_balance - v_excise_amount
2993 WHERE organization_id = p_org_id AND
2994 location_id = p_location_id;
2995
2996 ELSIF p_register_type = 'C' THEN
2997
2998 UPDATE JAI_CMN_RG_BALANCES
2999 SET rg23c_balance = rg23c_balance - v_excise_amount
3000 WHERE organization_id = p_org_id AND
3001 location_id = p_location_id;
3002 END IF;
3003 EXCEPTION
3004 WHEN OTHERS THEN
3005 FND_MESSAGE.SET_NAME ('JA','JAI_EXCEPTION_OCCURED');
3006 FND_MESSAGE.SET_TOKEN ('JAI_PROCESS_MSG',lv_object_name ||'.Err:'||sqlerrm);
3007 app_exception.raise_exception;
3008 END ja_in_rg23_part_II_entry;
3009
3010 /***************************** JA_IN_RG23_PART_II_ENTRY ****************************************************************/
3011
3012
3013
3014 /***************************** JA_IN_PLA_ENTRY ****************************************************************/
3015 -- This procedure has been modified by Aparajita on 03/08/2002 for bug # 2496388 for calling the accounting entry procedure.
3016 PROCEDURE ja_in_pla_entry(p_org_id NUMBER,
3017 p_location_id NUMBER,
3018 p_inventory_item_id NUMBER,
3019 p_fin_year NUMBER,
3020 p_transaction_id NUMBER,
3021 p_header_id NUMBER,
3022 p_ref_document_date DATE,
3023 p_excise_invoice_no VARCHAR2,
3024 p_excise_invoice_date DATE,
3025 p_dr_basic_ed NUMBER,
3026 p_dr_additional_ed NUMBER,
3027 p_dr_other_ed NUMBER,
3028 p_customer_id NUMBER,
3029 p_customer_site_id NUMBER,
3030 p_source_name VARCHAR2,
3031 p_category_name VARCHAR2,
3032 p_creation_date DATE,
3033 p_created_by NUMBER,
3034 p_last_update_date DATE,
3035 p_last_updated_by NUMBER,
3036 p_last_update_login NUMBER ,
3037 P_REF_10 VARCHAR2 DEFAULT NULL ,-- added by sriram - bug # 2769440
3038 P_REF_23 VARCHAR2 DEFAULT NULL ,-- added by sriram - bug # 2769440
3039 P_REF_24 VARCHAR2 DEFAULT NULL ,-- added by sriram - bug # 2769440
3040 P_REF_25 VARCHAR2 DEFAULT NULL ,-- added by sriram - bug # 2769440
3041 P_REF_26 VARCHAR2 DEFAULT NULL -- added by sriram - bug # 2769440
3042 ) IS
3043 v_opening_balance NUMBER := 0;
3044 v_closing_balance NUMBER := 0;
3045 v_rg_balance NUMBER := 0;
3046 v_range_no VARCHAR2(50);
3047 v_division_no VARCHAR2(50);
3048 v_account_id NUMBER;
3049 v_previous_serial_no NUMBER := 0;
3050 v_serial_no NUMBER := 0;
3051 v_debit_account_id NUMBER := 0;
3052 v_debit_cess_account NUMBER := 0;
3053 v_debit_sh_cess_account NUMBER := 0;/*Bug 5989740 bduvarag*/
3054 ln_cess_amount NUMBER := 0;
3055 ln_sh_cess_amount NUMBER := 0;/*Bug 5989740 bduvarag*/
3056 v_currency_code VARCHAR2(10) := 0;
3057 v_excise_amount NUMBER := 0;
3058 v_ssi_unit_flag VARCHAR2(1);
3059
3060 --Variables Added by Nagaraj.s for Enhancement
3061 v_register_code JAI_OM_OE_BOND_REG_HDRS.register_code%TYPE;
3062 v_basic_opening_balance NUMBER;
3063 v_basic_closing_balance NUMBER;
3064 v_additional_opening_balance NUMBER;
3065 v_additional_closing_balance NUMBER;
3066 v_other_opening_balance NUMBER;
3067 v_other_closing_balance NUMBER;
3068 v_export_oriented_unit JAI_CMN_INVENTORY_ORGS.export_oriented_unit%TYPE;
3069 v_rg_basic_balance NUMBER;
3070 v_rg_additional_balance NUMBER;
3071 v_rg_other_balance NUMBER;
3072 v_order_type_id JAI_OM_WSH_LINES_ALL.order_type_id%TYPE;
3073 --Variable declaration ends here.
3074
3075 v_asst_register_id Number; -- variable added by sriram - bug # 3021588
3076
3077 --Changed by Nagaraj.s for Enh2415656.........
3078 CURSOR balance_cur(p_previous_serial_no IN NUMBER) IS
3079 SELECT NVL(opening_balance,0),NVL(closing_balance,0),
3080 NVL(basic_opening_balance,0) ,NVL(basic_closing_balance,0),
3081 NVL(additional_opening_balance,0) ,NVL(additional_closing_balance,0),
3082 NVL(other_opening_balance,0), NVL(other_closing_balance,0)
3083 FROM JAI_CMN_RG_PLA_TRXS
3084 WHERE organization_id = p_org_id AND
3085 location_id = p_location_id AND
3086 slno = p_previous_serial_no AND
3087 fin_year = p_fin_year;
3088
3089 --Changed by Nagaraj.s for Enh#2415656..........
3090 CURSOR pla_balance_cur IS
3091 SELECT NVL(pla_balance,0),NVL(basic_pla_balance,0),
3092 NVL(additional_pla_balance,0), NVL(other_pla_balance,0)
3093 FROM JAI_CMN_RG_BALANCES
3094 WHERE organization_id = p_org_id AND
3095 location_id = p_location_id;
3096
3097 CURSOR range_division_cur IS
3098 SELECT excise_duty_range, excise_duty_division
3099 FROM JAI_CMN_CUS_ADDRESSES
3100 WHERE customer_id = p_customer_id
3101 AND address_id = (SELECT cust_acct_site_id -- address_id
3102 FROM hz_cust_site_uses_all A -- Removed ra_site_uses_all for Bug# 4434287
3103 WHERE A.site_use_id = p_customer_site_id);
3104
3105 CURSOR serial_no_cur IS
3106 SELECT NVL(MAX(slno),0) , NVL(MAX(slno),0) + 1
3107 FROM JAI_CMN_RG_PLA_TRXS
3108 WHERE organization_id = p_org_id AND
3109 location_id = p_location_id AND
3110 fin_year = p_fin_year;
3111
3112 --Changed by Nagaraj.s for Enh2415656...
3113 CURSOR pla_account_cur IS
3114 SELECT MODVAT_PLA_ACCOUNT_ID,NVL(export_oriented_unit,'N')
3115 FROM JAI_CMN_INVENTORY_ORGS
3116 WHERE organization_id = p_org_id
3117 AND location_id = p_location_id;
3118
3119 CURSOR debit_account_cur IS
3120 SELECT EXCISE_RCVBLE_ACCOUNT , CESS_PAID_PAYABLE_ACCOUNT_ID, /* CESS_PAID_PAYABLE_ACCOUNT_ID added by ssumaith */
3121 SH_CESS_PAID_PAYABLE_ACCT_ID/*Bug 5989740 bduvarag*/
3122 FROM JAI_CMN_INVENTORY_ORGS
3123 WHERE organization_id = p_org_id AND location_id = p_location_id;
3124
3125 /* Bug 4931887. Added by Lakshmi Gopalsami
3126 Removed the references to currency_cur
3127 and implemented the same using the global cursor
3128 get_curr_code which is defined in package specification
3129 */
3130
3131 CURSOR ssi_unit_flag_cur IS
3132 SELECT NVL(ssi_unit_flag, 'N')
3133 FROM JAI_CMN_INVENTORY_ORGS
3134 WHERE organization_id = p_org_id AND
3135 location_id = p_location_id;
3136
3137 --Cursors Added by Nagaraj.s for ENH2415656..
3138 CURSOR c_order_type_id IS
3139 SELECT order_type_id
3140 FROM JAI_OM_WSH_LINES_ALL
3141 WHERE Organization_id=p_org_id AND
3142 location_id = p_location_id AND
3143 delivery_detail_id = p_header_id;
3144
3145 CURSOR c_register_code IS
3146 SELECT A.register_code
3147 FROM JAI_OM_OE_BOND_REG_HDRS A, JAI_OM_OE_BOND_REG_DTLS b
3148 WHERE A.organization_id = p_org_id
3149 AND A.location_id = p_location_id
3150 AND A.register_id = b.register_id
3151 AND b.order_flag = 'Y'
3152 AND b.order_type_id = v_order_type_id ;
3153
3154 /*
3155 ||Added By aiyer for the bug 4765347
3156 */
3157 CURSOR cur_get_pla_reg_id
3158 IS
3159 SELECT
3160 jai_cmn_rg_pla_trxs_s1.nextval
3161 FROM
3162 dual;
3163
3164 ln_register_id JAI_CMN_RG_PLA_TRXS.REGISTER_ID%TYPE;
3165
3166 lv_object_name CONSTANT VARCHAR2 (61) := 'jai_om_rg_pkg.ja_in_pla_entry';
3167 --Added by Xiao for Open Interface ER bug#11683927 on 03-Mar-2011, begin
3168 --------------------------------------------------------------------------------------
3169 /*CURSOR get_external_flag_cur IS
3170 SELECT COUNT(lines.customer_trx_id)
3171 FROM jai_ar_trx_lines lines,
3172 jai_interface_lines_all intfs
3173 WHERE lines.customer_trx_line_id = intfs.internal_trx_line_id
3174 AND lines.customer_trx_id IN (SELECT customer_trx_id
3175 FROM jai_ar_trx_lines
3176 WHERE customer_trx_line_id = p_header_id)
3177 AND lines.interface_flag = 'Y'
3178 AND intfs.taxable_event = 'EXTERNAL';
3179
3180 CURSOR get_external_tax_cur IS
3181 SELECT tax.code_combination_id
3182 , tax.tax_amount
3183 FROM jai_ar_trx_lines jatl
3184 , jai_ar_trx_tax_lines jattl
3185 , jai_interface_lines_all lines
3186 , jai_interface_tax_lines_all tax
3187 WHERE jatl.customer_trx_line_id = p_header_id
3188 AND jattl.customer_trx_line_id = p_header_id
3189 AND tax.tax_line_no = jattl.tax_line_no
3190 AND tax.interface_line_id = lines.interface_line_id
3191 AND lines.internal_trx_id = jatl.customer_trx_id
3192 AND lines.internal_trx_line_id = p_header_id;
3193
3194 ln_external_flag NUMBER;*/--Comments by zhiwei for Bug#12537533
3195 --------------------------------------------------------------------------------------
3196 --Added by Xiao for Open Interface ER bug#11683927 on 03-Mar-2011, end
3197 BEGIN
3198 fnd_file.put_line(fnd_file.log, '1 Start of procedure jai_om_rg_pkg.ja_in_pla_entry');
3199 OPEN RANGE_DIVISION_CUR;
3200 FETCH RANGE_DIVISION_CUR INTO v_range_no,v_division_no;
3201 CLOSE RANGE_DIVISION_CUR;
3202
3203 OPEN pla_account_cur;
3204 FETCH pla_account_cur INTO v_account_id,v_export_oriented_unit;
3205 CLOSE pla_account_cur;
3206
3207 OPEN debit_account_cur;
3208 FETCH debit_account_cur INTO v_debit_account_id , v_debit_cess_account,v_debit_sh_cess_account;
3209 CLOSE debit_account_cur;
3210
3211 /* Bug 4931887. Added by Lakshmi Gopalsami
3212 Re-used the cursor get_curr_code for fixing perf. issue reported.
3213 */
3214 OPEN get_curr_code(p_org_id, p_location_id);
3215 FETCH get_curr_code INTO v_currency_code;
3216 CLOSE get_curr_code;
3217
3218 OPEN serial_no_cur;
3219 FETCH serial_no_cur INTO v_previous_serial_no, v_serial_no;
3220 CLOSE serial_no_cur;
3221
3222 OPEN ssi_unit_flag_cur;
3223 FETCH ssi_unit_flag_cur INTO v_ssi_unit_flag;
3224 CLOSE ssi_unit_flag_cur;
3225
3226 --Nagaraj.s
3227 OPEN c_order_type_id;
3228 FETCH c_order_type_id INTO v_order_type_id;
3229 CLOSE c_order_type_id;
3230
3231 --sriram - bug # 3021588 and using the following procedure call instead.
3232 Fnd_File.PUT_LINE(Fnd_File.LOG, '2 before call to jai_cmn_bond_register_pkg.get_register_id ');
3233
3234 jai_cmn_bond_register_pkg.get_register_id (p_org_id ,
3235 p_location_id,
3236 v_order_type_id, -- order type id
3237 'Y', -- order invoice type
3238 v_asst_register_id, -- out parameter to get the register id
3239 v_register_code);
3240
3241 Fnd_File.PUT_LINE(Fnd_File.LOG, '3 after call from jai_cmn_bond_register_pkg.get_register_id ');
3242 IF NVL(v_previous_serial_no,0) = 0 THEN
3243 v_previous_serial_no := 0;
3244 v_serial_no := 1;
3245 END IF;
3246
3247 Fnd_File.PUT_LINE(Fnd_File.LOG, '4 p_org_id -> ' ||p_org_id
3248 ||' ,p_location_id -> ' ||p_location_id
3249 ||' ,v_order_type_id -> ' ||v_order_type_id
3250 ||' ,v_asst_register_id -> ' ||v_asst_register_id
3251 ||' ,v_register_code -> ' ||v_register_code
3252 );
3253 IF NVL(v_previous_serial_no,0) > 0 THEN
3254
3255 OPEN balance_cur(v_previous_serial_no);
3256 FETCH balance_cur INTO v_opening_balance, v_closing_balance,v_basic_opening_balance,
3257 v_basic_closing_balance, v_additional_opening_balance, v_additional_closing_balance,
3258 v_other_opening_balance, v_other_closing_balance;
3259 CLOSE balance_cur;
3260
3261
3262 --Changed by Nagaraj.s for Enh#2415656..........
3263 v_opening_balance := v_closing_balance;
3264 v_closing_balance := v_closing_balance - (NVL(p_dr_basic_ed,0) + NVL(p_dr_additional_ed,0) + NVL(p_dr_other_ed,0));
3265 v_basic_opening_balance := v_basic_closing_balance;
3266 v_basic_closing_balance := v_basic_closing_balance - NVL(p_dr_basic_ed,0);
3267 v_additional_opening_balance := v_additional_closing_balance;
3268 v_additional_closing_balance := v_additional_closing_balance - NVL(p_dr_additional_ed,0);
3269 v_other_opening_balance := v_other_closing_balance;
3270 v_other_closing_balance := v_other_closing_balance - NVL(p_dr_other_ed,0);
3271 --Ends here....
3272 Fnd_File.PUT_LINE(Fnd_File.LOG, '5 ');
3273 -- Check for Export Excise and EOU.............
3274 IF v_register_code ='EXPORT_EXCISE' AND v_export_oriented_unit='Y' THEN
3275 Fnd_File.PUT_LINE(Fnd_File.LOG, '6 ');
3276 IF NVL(v_basic_closing_balance,0) < 0 THEN
3277 Fnd_File.PUT_LINE(Fnd_File.LOG, 'Cannot Debit more than the Basic PLA Opening Balance '|| TO_CHAR(ROUND(v_basic_opening_balance))); --Added for bug#7172215
3278 RAISE_APPLICATION_ERROR(-20120, 'Cannot Debit more than the Basic PLA Opening Balance '|| TO_CHAR(ROUND(v_basic_opening_balance)));
3279 END IF;
3280 IF NVL(v_additional_closing_balance,0) < 0 THEN
3281 Fnd_File.PUT_LINE(Fnd_File.LOG, 'Cannot Debit more than the Additional PLA Opening Balance '|| TO_CHAR(ROUND(v_additional_opening_balance))); --Added for bug#7172215
3282 RAISE_APPLICATION_ERROR(-20120, 'Cannot Debit more than the Additional PLA Opening Balance '|| TO_CHAR(ROUND(v_additional_opening_balance)));
3283 END IF;
3284
3285 IF NVL(v_other_closing_balance,0) < 0 THEN
3286 Fnd_File.PUT_LINE(Fnd_File.LOG, 'Cannot Debit more than the Other PLA Opening Balance '|| TO_CHAR(ROUND(v_other_opening_balance))); --Added for bug#7172215
3287 RAISE_APPLICATION_ERROR(-20120, 'Cannot Debit more than the Other PLA Opening Balance '|| TO_CHAR(ROUND(v_other_opening_balance)));
3288 END IF;
3289
3290 ELSE
3291 IF NVL(v_closing_balance,0) < 0 THEN
3292 IF NVL(v_ssi_unit_flag,'N') = 'N' THEN
3293 Fnd_File.PUT_LINE(Fnd_File.LOG, 'Cannot Debit more than the PLA Opening Balance '|| TO_CHAR(ROUND(v_opening_balance))); --Added for bug#7172215
3294 RAISE_APPLICATION_ERROR(-20120, 'Cannot Debit more than the PLA Opening Balance '|| TO_CHAR(ROUND(v_opening_balance)));
3295 END IF;
3296 END IF;
3297 END IF;
3298 Fnd_File.PUT_LINE(Fnd_File.LOG, '7 ');
3299 ELSE
3300 Fnd_File.PUT_LINE(Fnd_File.LOG, '8 ');
3301 --Changed by Nagaraj.s for Enh2415656....
3302 OPEN pla_balance_cur;
3303 FETCH pla_balance_cur INTO v_rg_balance,v_rg_basic_balance,v_rg_additional_balance,v_rg_other_balance;
3304 CLOSE pla_balance_cur;
3305
3306 fnd_file.put_line(fnd_file.log, '9 v_rg_balance '
3307 ||' ,v_rg_basic_balance -> '||v_rg_basic_balance
3308 ||' ,v_rg_additional_balance -> '||v_rg_additional_balance
3309 ||' ,v_rg_other_balance -> ' ||v_rg_other_balance);
3310
3311 v_opening_balance := NVL(v_rg_balance,0);
3312 v_closing_balance := NVL(v_rg_balance,0) - (NVL(p_dr_basic_ed,0) + NVL(p_dr_additional_ed,0) + NVL(p_dr_other_ed,0));
3313 v_basic_opening_balance := NVL(v_rg_basic_balance,0);
3314 v_basic_closing_balance := NVL(v_rg_basic_balance,0) - NVL(p_dr_basic_ed,0);
3315 v_additional_opening_balance := NVL(v_rg_additional_balance,0);
3316 v_additional_closing_balance := NVL(v_rg_additional_balance,0) - NVL(p_dr_additional_ed,0);
3317 v_other_opening_balance := NVL(v_rg_other_balance,0);
3318 v_other_closing_balance := NVL(v_rg_other_balance,0) - NVL(p_dr_other_ed,0);
3319 IF v_register_code ='EXPORT_EXCISE' AND v_export_oriented_unit='Y' THEN
3320 fnd_file.put_line(fnd_file.log, '10 ');
3321 IF NVL(v_basic_closing_balance,0) < 0 THEN
3322 Fnd_File.PUT_LINE(Fnd_File.LOG, 'Cannot Debit more than the Basic PLA Opening Balance '|| TO_CHAR(ROUND(v_basic_opening_balance))); --Added for bug#7172215
3323 RAISE_APPLICATION_ERROR(-20120, 'Cannot Debit more than the Basic PLA Opening Balance '|| TO_CHAR(ROUND(v_basic_opening_balance)));
3324 END IF;
3325
3326 IF NVL(v_additional_closing_balance,0) < 0 THEN
3327 Fnd_File.PUT_LINE(Fnd_File.LOG, 'Cannot Debit more than the Additional PLA Opening Balance '|| TO_CHAR(ROUND(v_additional_opening_balance))); --Added for bug#7172215
3328 RAISE_APPLICATION_ERROR(-20120, 'Cannot Debit more than the Additional PLA Opening Balance '|| TO_CHAR(ROUND(v_additional_opening_balance)));
3329 END IF;
3330
3331 IF NVL(v_other_closing_balance,0) < 0 THEN
3332 Fnd_File.PUT_LINE(Fnd_File.LOG, 'Cannot Debit more than the Other PLA Opening Balance '|| TO_CHAR(ROUND(v_other_opening_balance))); --Added for bug#7172215
3333 RAISE_APPLICATION_ERROR(-20120, 'Cannot Debit more than the Other PLA Opening Balance '|| TO_CHAR(ROUND(v_other_opening_balance)));
3334 END IF;
3335 fnd_file.put_line(fnd_file.log, '11 ');
3336 ELSE
3337 fnd_file.put_line(fnd_file.log, '12 ');
3338 IF v_closing_balance < 0 THEN
3339 IF NVL(v_ssi_unit_flag,'N') = 'N' THEN
3340 Fnd_File.PUT_LINE(Fnd_File.LOG, 'Cannot Debit more than the PLA Opening Balance '|| TO_CHAR(ROUND(v_rg_balance))); --Added for bug#7172215
3341 RAISE_APPLICATION_ERROR(-20120, 'Cannot Debit more than the PLA Opening Balance '|| TO_CHAR(ROUND(v_rg_balance)));
3342 END IF;
3343 END IF;
3344 END IF;
3345 fnd_file.put_line(fnd_file.log, '13 ');
3346 END IF;
3347
3348 fnd_file.put_line(fnd_file.log, '14 ');
3349 --Added by Xiao for Open Interface ER bug#11683927 on 03-Mar-2011, begin
3350 --For 'External' event, pick accounting/tax_amount from JAI Interface,
3351 --update accounting, and returns.
3352 --------------------------------------------------------------------------------------
3353 /*
3354 OPEN get_external_flag_cur;
3355 FETCH get_external_flag_cur INTO ln_external_flag;
3356 CLOSE get_external_flag_cur;
3357
3358 IF ln_external_flag > 0 THEN
3359
3360 OPEN get_external_tax_cur;
3361 FETCH get_external_tax_cur INTO v_account_id, v_excise_amount;
3362 CLOSE get_external_tax_cur;
3363
3364 ja_in_accounting_entries(
3365 p_org_id,
3366 p_location_id,
3367 v_currency_code,
3368 v_excise_amount,
3369 p_source_name ,
3370 p_category_name,
3371 p_created_by ,
3372 p_header_id,
3373 v_account_id,
3374 v_debit_account_id,
3375 P_REF_10,
3376 P_REF_23,
3377 P_REF_24,
3378 P_REF_25,
3379 P_REF_26
3380 );
3381 RETURN;
3382 END IF ; */--Comments by zhiwei for Bug#12537533
3383 --------------------------------------------------------------------------------------
3384 --Added by Xiao for Open Interface ER bug#11683927 on 03-Mar-2011, end
3385 OPEN cur_get_pla_reg_id ;
3386 FETCH cur_get_pla_reg_id INTO ln_register_id ;
3387 CLOSE cur_get_pla_reg_id ;
3388
3389 IF v_register_code ='EXPORT_EXCISE' AND v_export_oriented_unit='Y' THEN
3390
3391 fnd_file.put_line(fnd_file.log, '15 before insert into update into JAI_CMN_RG_PLA_TRXS , slno ' || v_serial_no
3392 ||' ,register_id ->' || ln_register_id
3393 ||' ,organization_id ->' || p_org_id
3394 ||' ,location_id ->' || p_location_id
3395 ||' ,inventory_item_id ->' || p_inventory_item_id
3396 ||' ,fin_year ->' || p_fin_year
3397 ||' ,TRANSACTION_SOURCE_NUM ->' || p_transaction_id
3398 ||' ,transaction_date ->' || p_ref_document_date
3399 ||' ,ref_document_id ->' || p_header_id
3400 ||' ,ref_document_date ->' || p_ref_document_date
3401 ||' ,DR_INVOICE_NO ->' || p_excise_invoice_no
3402 ||' ,dr_invoice_date ->' || p_excise_invoice_date
3403 ||' ,dr_basic_ed ->' || p_dr_basic_ed
3404 ||' ,dr_additional_ed ->' || p_dr_additional_ed
3405 ||' ,dr_other_ed ->' || p_dr_other_ed
3406 ||' ,vendor_cust_flag ->' || 'C'
3407 ||' ,vendor_id ->' || p_customer_id
3408 ||' ,vendor_site_id ->' || p_customer_site_id
3409 ||' ,range_no ->' || v_range_no
3410 ||' ,division_no ->' || v_division_no
3411 ||' ,opening_balance ->' || v_opening_balance
3412 ||' ,closing_balance ->' || v_closing_balance
3413 ||' ,charge_account_id ->' || v_debit_account_id
3414 ||' ,creation_date ->' || p_creation_date
3415 ||' ,created_by ->' || p_created_by
3416 ||' ,last_update_login ->' || p_last_update_login
3417 ||' ,last_update_date ->' || p_last_update_date
3418 ||' ,last_updated_by ->' || p_last_updated_by
3419 ||' ,basic_opening_balance ->' || v_basic_opening_balance
3420 ||' ,basic_closing_balance ->' || v_basic_closing_balance
3421 ||' ,additional_opening_balance ->' || v_additional_opening_balance
3422 ||' ,additional_closing_balance ->' || v_additional_closing_balance
3423 ||' ,other_opening_balance ->' || v_other_opening_balance
3424 ||' ,other_closing_balance ->' || v_other_closing_balance
3425 );
3426
3427
3428 /* Modified by Ramananda for removal of SQL LITERALs :bug#4428980*/
3429
3430 INSERT INTO JAI_CMN_RG_PLA_TRXS(register_id,
3431 slno,
3432 organization_id,
3433 location_id,
3434 inventory_item_id,
3435 fin_year,
3436 TRANSACTION_SOURCE_NUM,
3437 transaction_date,
3438 ref_document_id,
3439 ref_document_date,
3440 DR_INVOICE_NO,
3441 dr_invoice_date,
3442 dr_basic_ed,
3443 dr_additional_ed,
3444 dr_other_ed,
3445 vendor_cust_flag,
3446 vendor_id,
3447 vendor_site_id,
3448 range_no,
3449 division_no,
3450 opening_balance,
3451 closing_balance,
3452 charge_account_id,
3453 creation_date,
3454 created_by,
3455 last_update_login,
3456 last_update_date,
3457 last_updated_by,
3458 basic_opening_balance,
3459 basic_closing_balance,
3460 additional_opening_balance,
3461 additional_closing_balance,
3462 other_opening_balance,
3463 other_closing_balance)
3464 VALUES
3465 (
3466 ln_register_id ,
3467 v_serial_no,
3468 p_org_id,
3469 p_location_id,
3470 p_inventory_item_id,
3471 p_fin_year,
3472 p_transaction_id,
3473 p_ref_document_date,
3474 p_header_id ,
3475 p_ref_document_date,
3476 p_excise_invoice_no,
3477 p_excise_invoice_date,
3478 p_dr_basic_ed,
3479 p_dr_additional_ed,
3480 p_dr_other_ed,
3481 'C',
3482 p_customer_id,
3483 p_customer_site_id ,
3484 v_range_no,
3485 v_division_no,
3486 v_opening_balance,
3487 v_closing_balance,
3488 v_debit_account_id,
3489 p_creation_date,
3490 p_created_by,
3491 p_last_update_login,
3492 p_last_update_date,
3493 p_last_updated_by,
3494 v_basic_opening_balance,
3495 v_basic_closing_balance,
3496 v_additional_opening_balance,
3497 v_additional_closing_balance,
3498 v_other_opening_balance,
3499 v_other_closing_balance
3500 ) returning register_id into ln_register_id;
3501
3502 fnd_file.put_line(fnd_file.log, '16 after insert into JAI_CMN_RG_PLA_TRXS');
3503
3504 v_excise_amount := NVL(p_dr_basic_ed,0) + NVL(p_dr_additional_ed,0) + NVL(p_dr_other_ed,0);
3505
3506 UPDATE JAI_CMN_RG_BALANCES
3507 SET pla_balance = pla_balance - v_excise_amount,
3508 basic_pla_balance = basic_pla_balance - p_dr_basic_ed,
3509 additional_pla_balance = NVL(additional_pla_balance,0) - NVL(p_dr_additional_ed,0),
3510 other_pla_balance = NVL(other_pla_balance,0) - NVL(p_dr_other_ed,0)
3511 WHERE organization_id = p_org_id
3512 AND location_id = p_location_id;
3513 fnd_file.put_line(fnd_file.log, '17 after update into JAI_CMN_RG_BALANCES');
3514 ELSE
3515 fnd_file.put_line(fnd_file.log, '18 before insert into update into JAI_CMN_RG_PLA_TRXS , slno ' || v_serial_no
3516 ||' , register_id ->' || ln_register_id
3517 ||' , organization_id ->' || p_org_id
3518 ||' , location_id ->' || p_location_id
3519 ||' , inventory_item_id ->' || p_inventory_item_id
3520 ||' , fin_year ->' || p_fin_year
3521 ||' , transaction_source_num ->' || p_transaction_id
3522 ||' , transaction_date ->' || p_ref_document_date
3523 ||' , ref_document_id ->' || p_header_id
3524 ||' , ref_document_date ->' || p_ref_document_date
3525 ||' , DR_INVOICE_NO ->' || p_excise_invoice_no
3526 ||' , dr_invoice_date ->' || p_excise_invoice_date
3527 ||' , dr_basic_ed ->' || p_dr_basic_ed
3528 ||' , dr_additional_ed ->' || p_dr_additional_ed
3529 ||' , dr_other_ed ->' || p_dr_other_ed
3530 ||' , vendor_cust_flag ->' || 'C'
3531 ||' , vendor_id ->' || p_customer_id
3532 ||' , vendor_site_id ->' || p_customer_site_id
3533 ||' , range_no ->' || v_range_no
3534 ||' , division_no ->' || v_division_no
3535 ||' , opening_balance ->' || v_opening_balance
3536 ||' , closing_balance ->' || v_closing_balance
3537 ||' , charge_account_id ->' || v_debit_account_id
3538 ||' , creation_date ->' || p_creation_date
3539 ||' , created_by ->' || p_created_by
3540 ||' , last_update_login ->' || p_last_update_login
3541 ||' , last_update_date ->' || p_last_update_date
3542 ||' , last_updated_by ->' || p_last_updated_by
3543 );
3544
3545 /* Modified by Ramananda for removal of SQL LITERALs :bug#4428980*/
3546 INSERT INTO JAI_CMN_RG_PLA_TRXS(
3547 register_id,
3548 slno,
3549 organization_id,
3550 location_id,
3551 inventory_item_id,
3552 fin_year,
3553 TRANSACTION_SOURCE_NUM,
3554 transaction_date,
3555 ref_document_id,
3556 ref_document_date,
3557 DR_INVOICE_NO,
3558 dr_invoice_date,
3559 dr_basic_ed,
3560 dr_additional_ed,
3561 dr_other_ed,
3562 vendor_cust_flag,
3563 vendor_id,
3564 vendor_site_id,
3565 range_no,
3566 division_no,
3567 opening_balance,
3568 closing_balance,
3569 charge_account_id,
3570 creation_date,
3571 created_by,
3572 last_update_login,
3573 last_update_date,
3574 last_updated_by
3575 )
3576 VALUES(
3577 ln_register_id,
3578 v_serial_no,
3579 p_org_id,
3580 p_location_id,
3581 p_inventory_item_id,
3582 p_fin_year,
3583 p_transaction_id,
3584 p_ref_document_date,
3585 p_header_id ,
3586 p_ref_document_date,
3587 p_excise_invoice_no ,
3588 p_excise_invoice_date,
3589 p_dr_basic_ed,
3590 p_dr_additional_ed,
3591 p_dr_other_ed,
3592 'C',
3593 p_customer_id,
3594 p_customer_site_id ,
3595 v_range_no,
3596 v_division_no,
3597 v_opening_balance,
3598 v_closing_balance,
3599 v_debit_account_id,
3600 p_creation_date,
3601 p_created_by,
3602 p_last_update_login,
3603 p_last_update_date,
3604 p_last_updated_by
3605 ) returning register_id into ln_register_id;
3606
3607 fnd_file.put_line(fnd_file.log, '19 after insert into JAI_CMN_RG_PLA_TRXS');
3608
3609 v_excise_amount := NVL(p_dr_basic_ed,0) + NVL(p_dr_additional_ed,0) + NVL(p_dr_other_ed,0);
3610
3611 UPDATE JAI_CMN_RG_BALANCES
3612 SET pla_balance = pla_balance - v_excise_amount
3613 WHERE organization_id = p_org_id
3614 AND location_id = p_location_id;
3615
3616 fnd_file.put_line(fnd_file.log, '20 after update of JAI_CMN_RG_BALANCES');
3617
3618 END IF;
3619
3620
3621 fnd_file.put_line(fnd_file.log, '21 before call to ja_in_accounting_entries procedure ');
3622
3623 ja_in_accounting_entries
3624 (
3625 p_org_id,
3626 p_location_id,
3627 v_currency_code,
3628 v_excise_amount,
3629 p_source_name ,
3630 p_category_name,
3631 p_created_by ,
3632 p_header_id,
3633 v_account_id,
3634 v_debit_account_id,
3635 P_REF_10,
3636 P_REF_23,
3637 P_REF_24,
3638 P_REF_25,
3639 P_REF_26
3640 );
3641
3642 Fnd_File.PUT_LINE(Fnd_File.LOG, 'before calling ja_in_cess_register_entries procedure');
3643 ja_in_cess_register_entries(
3644 p_register_id => ln_register_id ,
3645 p_register_type => 'PLA' ,
3646 p_inv_orgn_id => p_org_id ,
3647 p_je_source_name => p_source_name ,
3648 p_je_category_name => p_category_name ,
3649 p_source_type => 2 ,
3650 p_currency_code => v_currency_code ,
3651 p_transaction_hdr_id => p_header_id ,
3652 p_debit_account => v_debit_cess_account,
3653 p_Credit_account => v_account_id ,
3654 p_cess_amount => ln_cess_amount ,
3655 p_cess_type => 'EXC' ,
3656 P_REFERENCE_10 => P_REF_10 ,
3657 P_REFERENCE_23 => P_REF_23 ,
3658 P_REFERENCE_24 => P_REF_24 ,
3659 P_REFERENCE_25 => P_REF_25 ,
3660 P_REFERENCE_26 => P_REF_26
3661 );
3662 /*Bug 5989740 bduvarag*/
3663
3664
3665 ja_in_cess_register_entries(
3666 p_register_id => ln_register_id ,
3667 p_register_type => 'PLA' ,
3668 p_inv_orgn_id => p_org_id ,
3669 p_je_source_name => p_source_name ,
3670 p_je_category_name => p_category_name ,
3671 p_source_type => 2 ,
3672 p_currency_code => v_currency_code ,
3673 p_transaction_hdr_id => p_header_id ,
3674 p_debit_account => v_debit_sh_cess_account,
3675 p_Credit_account => v_account_id ,
3676 p_cess_amount => ln_sh_cess_amount ,
3677 p_cess_type => 'SH' ,
3678 P_REFERENCE_10 => P_REF_10 ,
3679 P_REFERENCE_23 => P_REF_23 ,
3680 P_REFERENCE_24 => P_REF_24 ,
3681 P_REFERENCE_25 => P_REF_25 ,
3682 P_REFERENCE_26 => P_REF_26
3683 );
3684 Fnd_File.PUT_LINE(Fnd_File.LOG, 'after calling ja_in_cess_register_entries procedure');
3685
3686
3687 update JAI_CMN_RG_PLA_TRXS
3688 set other_tax_debit = ln_cess_amount + ln_sh_cess_amount -- Date 04/06/2007 by Sacsethi for bug 6109941
3689 where register_id = ln_register_id;
3690
3691 EXCEPTION
3692 WHEN OTHERS THEN
3693 FND_MESSAGE.SET_NAME ('JA','JAI_EXCEPTION_OCCURED');
3694 FND_MESSAGE.SET_TOKEN ('JAI_PROCESS_MSG',lv_object_name ||'.Err:'||sqlerrm);
3695 app_exception.raise_exception;
3696 END ja_in_pla_entry;
3697
3698 /***************************** JA_IN_PLA_ENTRY ****************************************************************/
3699
3700
3701 /***************************** Ja_In_Rg23d_Entry ****************************************************************/
3702 PROCEDURE Ja_In_Rg23d_Entry(
3703 p_register_id NUMBER,p_org_id NUMBER,p_location_id NUMBER, p_fin_year NUMBER,
3704 p_transaction_type VARCHAR2,p_inventory_item_id NUMBER, p_reference_line_id NUMBER,
3705 p_primary_uom_code VARCHAR2,p_transaction_uom_code VARCHAR2, p_customer_id NUMBER,
3706 p_bill_to_site_id NUMBER,p_ship_to_site_id NUMBER,p_quantity_issued NUMBER, p_register_code VARCHAR2,
3707 p_rate_per_unit NUMBER,p_excise_duty_rate NUMBER, p_duty_amount NUMBER, p_transaction_id NUMBER,
3708 p_source_name VARCHAR2, p_category_name VARCHAR2, p_receipt_id NUMBER, p_oth_receipt_id NUMBER,
3709 p_creation_date DATE,p_created_by NUMBER,p_last_update_date DATE,p_last_update_login NUMBER,
3710 p_last_updated_by NUMBER,p_dr_basic_ed NUMBER,p_dr_additional_ed NUMBER,p_dr_other_ed NUMBER,
3711 p_comm_invoice_no VARCHAR2,p_comm_invoice_date DATE,
3712 P_REF_10 VARCHAR2 DEFAULT NULL ,-- added by sriram - bug # 2769440
3713 P_REF_23 VARCHAR2 DEFAULT NULL ,-- added by sriram - bug # 2769440
3714 P_REF_24 VARCHAR2 DEFAULT NULL ,-- added by sriram - bug # 2769440
3715 P_REF_25 VARCHAR2 DEFAULT NULL ,-- added by sriram - bug # 2769440
3716 P_REF_26 VARCHAR2 DEFAULT NULL, -- added by sriram - bug # 2769440
3717 p_dr_cvd_amt NUMBER DEFAULT NULL, --Added by nprashar for bug # 5735284 added for bug#6199766
3718 p_dr_additional_cvd_amt NUMBER DEFAULT NULL --Added by nprashar for bug # 5735284 added for bug#6199766
3719 ) IS
3720
3721 v_opening_balance NUMBER;
3722 v_closing_balance NUMBER;
3723 v_currency_code VARCHAR2(10);
3724 v_srno NUMBER;
3725 v_srno1 NUMBER;
3726 v_rg23d_account NUMBER;
3727 v_duty_amount NUMBER;
3728 v_excise_amount NUMBER;
3729 v_debit_account_id NUMBER;
3730 ln_cess_debit_account_id number;
3731 ln_sh_cess_debit_account_id number;/*Bug 5989740 bduvarag*/
3732
3733 CURSOR balance_cur(p_previous_serial_no IN NUMBER) IS
3734 SELECT NVL(ROUND(opening_balance_qty,0),0),NVL(ROUND(closing_balance_qty,0),0)
3735 FROM JAI_CMN_RG_23D_TRXS
3736 WHERE organization_id = p_org_id AND
3737 location_id = p_location_id AND
3738 slno = p_previous_serial_no AND
3739 fin_year = p_fin_year
3740 AND inventory_item_id = p_inventory_item_id;
3741
3742 CURSOR serial_no_cur IS
3743 SELECT NVL(MAX(slno),0) , NVL(MAX(slno),0) + 1
3744 FROM JAI_CMN_RG_23D_TRXS
3745 WHERE organization_id = p_org_id AND
3746 location_id = p_location_id AND
3747 fin_year = p_fin_year
3748 AND inventory_item_id = p_inventory_item_id;
3749
3750 -- Start, Vijay Shankar for Bug# 3657742
3751 v_max_register_id NUMBER;
3752 CURSOR c_max_register_id( p_orgn_id IN NUMBER, p_loc_id IN NUMBER,
3753 p_inv_item_id IN NUMBER) IS
3754 SELECT max(register_id)
3755 FROM JAI_CMN_RG_23D_TRXS
3756 WHERE organization_id = p_orgn_id
3757 AND location_id = p_loc_id
3758 AND inventory_item_id = p_inv_item_id;
3759
3760 CURSOR c_rg23d_rec( p_register_id IN NUMBER) IS
3761 SELECT fin_year, slno, opening_balance_qty, closing_balance_qty
3762 FROM JAI_CMN_RG_23D_TRXS
3763 WHERE register_id = p_register_id;
3764 v_rg23d_rec c_rg23d_rec%ROWTYPE;
3765 -- End, Vijay Shankar for Bug# 3657742
3766 /*
3767 CURSOR rg23d_Account IS
3768 SELECT EXCISE_23D_ACCOUNT
3769 FROM JAI_CMN_INVENTORY_ORGS
3770 WHERE organization_id = p_org_id AND location_id = p_location_id;
3771
3772 CURSOR debit_account_cur IS
3773 SELECT EXCISE_RCVBLE_ACCOUNT , CESS_PAID_PAYABLE_ACCOUNT_ID
3774 FROM JAI_CMN_INVENTORY_ORGS
3775 WHERE organization_id = p_org_id AND location_id = p_location_id;
3776 */
3777 /* Bug 4931887. Added by Lakshmi Gopalsami
3778 Removed the references to currency_cur
3779 and implemented the same using the global cursor
3780 get_curr_code which is defined in package specification
3781 */
3782
3783 /*
3784 start ssumaith - bug#
3785 */
3786 -- get the delivery details
3787 CURSOR c_get_delivery_details IS
3788 SELECT source_header_id, source_line_id
3789 FROM wsh_delivery_details
3790 WHERE delivery_detail_id = p_reference_line_id;
3791
3792 -- cursor to get order source id, when 10 this means that it is an internal sales order(iso)
3793 -- this also gets other details to link to requisition side and the from org id.
3794 CURSOR c_get_order_details(p_header_id NUMBER, p_line_id NUMBER) IS
3795 SELECT ship_from_org_id, order_source_id, source_document_id, source_document_line_id
3796 FROM oe_order_lines_all
3797 WHERE header_id = p_header_id
3798 AND line_id = p_line_id;
3799
3800 -- get the to organization id from the requisition details
3801 CURSOR c_get_to_organization(p_requisition_header_id NUMBER, p_requisition_line_id NUMBER) IS
3802 SELECT destination_organization_id , deliver_to_location_id /* deliver_to_location_id added by ssumaith - to handle trading to trading ISO */
3803 FROM po_requisition_lines_all
3804 WHERE requisition_header_id = p_requisition_header_id
3805 AND requisition_line_id = p_requisition_line_id;
3806
3807
3808 CURSOR c_get_iso_accounts(p_from_org_id NUMBER, p_to_org_id NUMBER) IS
3809 SELECT intransit_type, fob_point, interorg_receivables_account, interorg_payables_account, intransit_inv_account
3810 FROM mtl_interorg_parameters
3811 WHERE from_organization_id = p_from_org_id
3812 AND to_organization_id = p_to_org_id;
3813
3814 CURSOR debit_account_cur( cp_organization_id JAI_CMN_INVENTORY_ORGS.ORGANIZATION_ID%TYPE,
3815 cp_location_id JAI_CMN_INVENTORY_ORGS.LOCATION_ID%TYPE
3816 ) IS
3817 SELECT excise_rcvble_account , excise_23d_account ,excise_in_rg23d , Trading, manufacturing
3818 ,cess_paid_payable_account_id, sh_cess_paid_payable_acct_id /*Added two columns by mmurtuza for bug 16022303*/
3819 FROM JAI_CMN_INVENTORY_ORGS
3820 WHERE organization_id = cp_organization_id
3821 AND location_id = cp_location_id;
3822
3823
3824 CURSOR c_cess_amount IS
3825 SELECT sum(a.tax_amount)
3826 FROM JAI_OM_WSH_LINE_TAXES a, JAI_CMN_TAXES_ALL b
3827 WHERE delivery_detail_id = p_reference_line_id
3828 AND a.tax_id = b.tax_id
3829 AND upper(b.tax_type) in (jai_constants.TAX_TYPE_CVD_EDU_CESS,jai_constants.TAX_TYPE_EXC_EDU_CESS);
3830 /*Bug 5989740 bduvarag*/
3831 CURSOR c_sh_cess_amount IS
3832 SELECT sum(a.tax_amount)
3833 FROM JAI_OM_WSH_LINE_TAXES a, JAI_CMN_TAXES_ALL b
3834 WHERE delivery_detail_id = p_reference_line_id
3835 AND a.tax_id = b.tax_id
3836 AND upper(b.tax_type) in (JAI_CONSTANTS.tax_type_sh_cvd_edu_cess,JAI_CONSTANTS.tax_type_sh_exc_edu_cess);
3837
3838
3839
3840 /*
3841 The following variables defined for cess trading to trading
3842 */
3843 ln_to_location_id PO_REQUISITION_LINES_ALL.DELIVER_TO_LOCATION_ID%TYPE;
3844 lv_dest_intransit_type MTL_INTERORG_PARAMETERS.INTRANSIT_TYPE%TYPE;
3845 ln_dest_fob_point MTL_INTERORG_PARAMETERS.FOB_POINT%TYPE;
3846 ln_dest_interorg_rcvbles_acc MTL_INTERORG_PARAMETERS.INTERORG_RECEIVABLES_ACCOUNT%TYPE;
3847 ln_dest_interorg_payables_acc MTL_INTERORG_PARAMETERS.INTERORG_PAYABLES_ACCOUNT%TYPE;
3848 ln_dest_intransit_inv_account MTL_INTERORG_PARAMETERS.INTRANSIT_INV_ACCOUNT%TYPE;
3849 ln_src_excise_in_rg23d JAI_CMN_INVENTORY_ORGS.EXCISE_IN_RG23D%TYPE;
3850 ln_dest_excise_in_rg23d JAI_CMN_INVENTORY_ORGS.EXCISE_IN_RG23D%TYPE;
3851 lv_source_trading JAI_CMN_INVENTORY_ORGS.TRADING%TYPE;
3852 lv_dest_trading JAI_CMN_INVENTORY_ORGS.TRADING%TYPE;
3853 lv_source_manufacturing JAI_CMN_INVENTORY_ORGS.MANUFACTURING%TYPE;
3854 lv_dest_manufacturing JAI_CMN_INVENTORY_ORGS.MANUFACTURING%TYPE;
3855 ln_debit_acc GL_CODE_COMBINATIONS.CODE_COMBINATION_ID%TYPE;
3856 ln_credit_acc GL_CODE_COMBINATIONS.CODE_COMBINATION_ID%TYPE;
3857 ln_cess_amount JAI_CMN_RG_OTHERS.DEBIT%TYPE;
3858 ln_sh_cess_amount JAI_CMN_RG_OTHERS.DEBIT%TYPE;/*Bug 5989740 bduvarag*/
3859 ln_delivery_id JAI_OM_WSH_LINES_ALL.DELIVERY_ID%TYPE;
3860 v_order_header_id WSH_DELIVERY_DETAILS.SOURCE_HEADER_ID%TYPE;
3861 v_order_line_id WSH_DELIVERY_DETAILS.SOURCE_LINE_ID%TYPE;
3862 v_order_source_id OE_ORDER_HEADERS_ALL.ORDER_SOURCE_ID%TYPE;
3863 v_intransit_type MTL_SHIPPING_NETWORK_VIEW.INTRANSIT_TYPE%TYPE;
3864 v_fob_point MTL_SHIPPING_NETWORK_VIEW.FOB_POINT%TYPE;
3865 v_interorg_receivables_account MTL_INTERORG_PARAMETERS.INTERORG_RECEIVABLES_ACCOUNT%TYPE;
3866 v_interorg_payables_account MTL_INTERORG_PARAMETERS.INTERORG_PAYABLES_ACCOUNT%TYPE;
3867 v_intransit_inv_account MTL_INTERORG_PARAMETERS.INTRANSIT_INV_ACCOUNT%TYPE;
3868 v_from_organization_id WSH_DELIVERY_DETAILS.ORGANIZATION_ID%TYPE;
3869 v_to_organization_id WSH_DELIVERY_DETAILS.ORGANIZATION_ID%TYPE;
3870 v_excise_rcvble_account JAI_CMN_INVENTORY_ORGS.EXCISE_RCVBLE_ACCOUNT%TYPE;
3871 v_requisition_header_id PO_REQUISITION_LINES_ALL.REQUISITION_HEADER_ID%TYPE;
3872 v_requisition_line_id PO_REQUISITION_LINES_ALL.REQUISITION_LINE_ID%TYPE;
3873 ln_src_excise_23d_account JAI_CMN_INVENTORY_ORGS.EXCISE_23D_ACCOUNT%TYPE;
3874 ln_dest_excise_23d_account JAI_CMN_INVENTORY_ORGS.EXCISE_23D_ACCOUNT%TYPE;
3875 ln_dest_excise_rcvble_account JAI_CMN_INVENTORY_ORGS.EXCISE_RCVBLE_ACCOUNT%TYPE;
3876
3877 /*Start additons by mmurtuza for bug 16022303*/
3878 ln_credit_acc_cess_paid GL_CODE_COMBINATIONS.CODE_COMBINATION_ID%TYPE;
3879 ln_credit_acc_sh_cess_paid GL_CODE_COMBINATIONS.CODE_COMBINATION_ID%TYPE;
3880 ln_dest_credit_acc_cess_paid JAI_CMN_INVENTORY_ORGS.cess_paid_payable_account_id%TYPE;
3881 ln_dest_credit_acc_shcess_paid JAI_CMN_INVENTORY_ORGS.sh_cess_paid_payable_acct_id%TYPE;
3882 /*End additons by mmurtuza for bug 16022303*/
3883
3884 lv_object_name CONSTANT VARCHAR2 (61) := 'jai_om_rg_pkg.ja_in_rg23d_entry';
3885
3886
3887
3888
3889 BEGIN
3890
3891 /* Bug 4931887. Added by Lakshmi Gopalsami
3892 Re-used the cursor get_curr_code for fixing perf. issue reported.
3893 */
3894 OPEN get_curr_code(p_org_id, p_location_id);
3895 FETCH get_curr_code INTO v_currency_code;
3896 CLOSE get_curr_code;
3897
3898 /* OPEN rg23d_account;
3899 FETCH rg23d_account INTO v_rg23d_account;
3900 CLOSE rg23d_account;
3901
3902 OPEN debit_account_cur;
3903 FETCH debit_account_cur INTO v_debit_account_id , ln_cess_debit_account_id;
3904 CLOSE debit_account_cur;
3905 */
3906
3907 /* Start, Vijay Shankar for Bug# 3657742 */
3908 OPEN c_max_register_id(p_org_id, p_location_id, p_inventory_item_id);
3909 FETCH c_max_register_id into v_max_register_id;
3910 CLOSE c_max_register_id;
3911
3912 IF v_max_register_id IS NULL THEN
3913 v_srno1 := 1;
3914 v_opening_balance := 0;
3915 v_closing_balance := 0;
3916 ELSE
3917 OPEN c_rg23d_rec(v_max_register_id);
3918 FETCH c_rg23d_rec into v_rg23d_rec;
3919 CLOSE c_rg23d_rec;
3920
3921 IF v_rg23d_rec.fin_year <> p_fin_year THEN
3922 v_srno1 := 1;
3923 ELSE
3924 v_srno1 := v_rg23d_rec.slno + 1;
3925 END IF;
3926
3927 v_opening_balance := v_rg23d_rec.opening_balance_qty;
3928 v_closing_balance := v_rg23d_rec.closing_balance_qty;
3929 END IF;
3930 /* End, Vijay Shankar for Bug# 3657742 */
3931
3932 /* Vijay Shankar for Bug# 3657742
3933 OPEN serial_no_cur;
3934 FETCH serial_no_cur INTO v_srno,v_srno1;
3935 CLOSE serial_no_cur;
3936
3937 OPEN balance_cur(v_srno);
3938 FETCH balance_cur INTO v_opening_balance, v_closing_balance;
3939 CLOSE balance_cur;
3940 */
3941
3942 INSERT INTO JAI_CMN_RG_23D_TRXS (
3943 register_id, organization_id, location_id, slno, fin_year,
3944 transaction_type, inventory_item_id, reference_line_id, primary_uom_code,
3945 transaction_uom_code, customer_id, bill_to_site_id, ship_to_site_id,
3946 quantity_issued, register_code, charge_account_id, rate_per_unit, excise_duty_rate,duty_amount, TRANSACTION_SOURCE_NUM,
3947 basic_ed, additional_ed, other_ed ,cvd, additional_cvd, opening_balance_qty, closing_balance_qty, /*Added CVD columns by nprashar for bug # 5735284*/
3948 RECEIPT_REF, OTH_RECEIPT_ID_REF,
3949 creation_date, created_by,last_update_login,
3950 last_update_date,last_updated_by,comm_invoice_no,comm_invoice_date
3951 ) VALUES (
3952 p_register_id, p_org_id, p_location_id, v_srno1, p_fin_year,
3953 p_transaction_type, p_inventory_item_id, p_reference_line_id, p_primary_uom_code,
3954 p_transaction_uom_code, p_customer_id, p_bill_to_site_id, p_ship_to_site_id,
3955 p_quantity_issued, p_register_code, v_rg23d_account, p_rate_per_unit, p_excise_duty_rate,p_duty_amount, 33,
3956 p_dr_basic_ed, p_dr_additional_ed, p_dr_other_ed, p_dr_cvd_amt,p_dr_additional_cvd_amt, /*Added by nprashar for bug # 5735284*/
3957 NVL(v_closing_balance,0), NVL(v_closing_balance,0) - NVL(p_quantity_issued,0),
3958 p_receipt_id, p_oth_receipt_id,
3959 p_creation_date, p_created_by, p_last_update_login,
3960 p_last_update_date, p_last_updated_by,p_comm_invoice_no,p_comm_invoice_date
3961 );
3962
3963 v_excise_amount := NVL(p_dr_basic_ed,0) + NVL(p_dr_additional_ed,0) + NVL(p_dr_other_ed,0);
3964
3965 /* Commented the code after discussing with Yadunath Bug#4171469
3966 IF NVL(v_rg23d_account,0)>0 AND NVL(v_debit_account_id,0) >0 THEN
3967 jai_cmn_gl_pkg.create_gl_entry(
3968 p_org_id,
3969 v_currency_code,
3970 v_excise_amount,
3971 0,
3972 v_rg23d_account,
3973 p_source_name,
3974 p_category_name,
3975 p_created_by,
3976 NULL,
3977 NULL,
3978 NULL,
3979 NULL,
3980 P_REF_10,
3981 P_REF_23,
3982 P_REF_24,
3983 P_REF_25,
3984 P_REF_26
3985 );
3986
3987 jai_cmn_gl_pkg.create_gl_entry(
3988 p_org_id,
3989 v_currency_code,
3990 0,
3991 v_excise_amount,
3992 v_debit_account_id,
3993 p_source_name,
3994 p_category_name,
3995 p_created_by,
3996 NULL,
3997 NULL,
3998 NULL,
3999 NULL,
4000 P_REF_10,
4001 P_REF_23,
4002 P_REF_24,
4003 P_REF_25,
4004 P_REF_26
4005 );
4006 END IF;
4007
4008 */
4009 /* RG23D */
4010 ja_in_cess_register_entries(
4011 p_register_id => p_register_id ,
4012 p_register_type => 'RG23D' ,
4013 p_inv_orgn_id => p_org_id ,
4014 p_je_source_name => p_source_name ,
4015 p_je_category_name => p_category_name ,
4016 p_source_type => 3 ,
4017 p_currency_code => v_currency_code ,
4018 p_transaction_hdr_id => p_reference_line_id ,
4019 p_debit_account => ln_cess_debit_account_id,
4020 p_Credit_account => v_rg23d_account ,
4021 p_cess_amount => ln_cess_amount ,
4022 p_cess_type => 'EXC' , /*Bug 5989740 bduvarag*/
4023 P_REFERENCE_10 => P_REF_10 ,
4024 P_REFERENCE_23 => P_REF_23 ,
4025 P_REFERENCE_24 => P_REF_24 ,
4026 P_REFERENCE_25 => P_REF_25 ,
4027 P_REFERENCE_26 => P_REF_26
4028 );
4029
4030 ja_in_cess_register_entries(
4031 p_register_id => p_register_id ,
4032 p_register_type => 'RG23D' ,
4033 p_inv_orgn_id => p_org_id ,
4034 p_je_source_name => p_source_name ,
4035 p_je_category_name => p_category_name ,
4036 p_source_type => 3 ,
4037 p_currency_code => v_currency_code ,
4038 p_transaction_hdr_id => p_reference_line_id ,
4039 p_debit_account => ln_sh_cess_debit_account_id,
4040 p_Credit_account => v_rg23d_account ,
4041 p_cess_amount => ln_sh_cess_amount ,
4042 p_cess_type => 'SH' , /*Bug 5989740 bduvarag*/
4043 P_REFERENCE_10 => P_REF_10 ,
4044 P_REFERENCE_23 => P_REF_23 ,
4045 P_REFERENCE_24 => P_REF_24 ,
4046 P_REFERENCE_25 => P_REF_25 ,
4047 P_REFERENCE_26 => P_REF_26
4048 );
4049 update JAI_CMN_RG_23D_TRXS
4050 set other_tax_debit = ln_cess_amount + ln_sh_cess_amount/*Bug 5989740 bduvarag*/
4051 where register_id = p_register_id;
4052
4053
4054 /*
4055 start ssumaith - bug#
4056 */
4057
4058 OPEN c_get_delivery_details;
4059 FETCH c_get_delivery_details INTO v_order_header_id, v_order_line_id ;
4060 CLOSE c_get_delivery_details;
4061
4062 OPEN c_get_order_details(v_order_header_id, v_order_line_id);
4063 FETCH c_get_order_details INTO
4064 v_from_organization_id, v_order_source_id, v_requisition_header_id, v_requisition_line_id;
4065 CLOSE c_get_order_details;
4066
4067 OPEN c_get_to_organization(v_requisition_header_id, v_requisition_line_id);
4068 FETCH c_get_to_organization INTO v_to_organization_id , ln_to_location_id ;
4069 CLOSE c_get_to_organization;
4070
4071 OPEN c_get_iso_accounts(v_from_organization_id, v_to_organization_id);
4072 FETCH c_get_iso_accounts
4073 INTO v_intransit_type, v_fob_point, v_interorg_receivables_account,
4074 v_interorg_payables_account, v_intransit_inv_account;
4075 CLOSE c_get_iso_accounts;
4076
4077 OPEN c_cess_amount;
4078 FETCH c_cess_amount INTO ln_cess_amount;
4079 CLOSE c_cess_amount;
4080 /*Bug 5989740 bduvarag*/
4081 OPEN c_sh_cess_amount;
4082 FETCH c_sh_cess_amount INTO ln_sh_cess_amount;
4083 CLOSE c_sh_cess_amount;
4084
4085 Fnd_File.PUT_LINE(Fnd_File.LOG, ' in the gl_interface procedure with values as follows' );
4086 Fnd_File.PUT_LINE(Fnd_File.LOG, ' v_order_header_id, v_order_line_id' || v_order_header_id || v_order_line_id );
4087 Fnd_File.PUT_LINE(Fnd_File.LOG, ' v_from_organization_id, v_order_source_id, v_requisition_header_id, v_requisition_line_id '
4088 || v_from_organization_id ||' , ' || v_order_source_id ||' , ' || v_requisition_header_id ||' , ' || v_requisition_line_id );
4089
4090 OPEN debit_account_cur(v_from_organization_id , p_location_id);
4091 FETCH debit_account_cur INTO v_excise_rcvble_account , ln_src_excise_23d_account, ln_src_excise_in_rg23d, lv_source_trading, lv_source_manufacturing
4092 ,ln_credit_acc_cess_paid, ln_credit_acc_sh_cess_paid; /*Added two variables by mmurtuza for bug 16022303*/
4093 CLOSE debit_account_cur;
4094
4095
4096 OPEN debit_account_cur(v_to_organization_id , ln_to_location_id );
4097 FETCH debit_account_cur INTO ln_dest_excise_rcvble_account , ln_dest_excise_23d_account , ln_dest_excise_in_rg23d ,lv_dest_trading, lv_dest_manufacturing
4098 , ln_dest_credit_acc_cess_paid, ln_dest_credit_acc_shcess_paid; /*Added two variables by mmurtuza for bug 16022303*/
4099 CLOSE debit_account_cur;
4100
4101 IF v_order_source_id = 10 AND /* INTERNAL SOURCE ORDER */
4102 (
4103 /*(
4104 NVL(lv_source_trading,'N') = 'Y' and NVL(lv_dest_trading,'N') = 'Y'
4105 )
4106 AND
4107 (
4108 NVL(ln_dest_excise_in_rg23d,'N') = 'Y' and NVL(ln_src_excise_in_rg23d,'N') = 'Y'
4109 )*/
4110 --commented the above and added the below by Ramananda for Bug#4516577
4111 NVL(lv_source_trading,'N') = 'Y'
4112 AND
4113 ( NVL(lv_dest_trading,'N') = 'Y' OR NVL(lv_dest_manufacturing,'N') = 'Y' )
4114 AND
4115 NVL(ln_src_excise_in_rg23d,'N') = 'Y'
4116
4117 )
4118 THEN
4119
4120 IF v_intransit_type = 2 THEN
4121 -- fob point check added by Aparajita for bug#2848921, fob point 1 is shipment
4122 -- credit excise paid, payable account , debit inter org receiavable account.
4123
4124 IF v_fob_point IN (1,2) THEN
4125
4126 /*
4127 start additions by ssumaith for bug# 4171469 on shipment side in case of trading to trading ISO scenario.
4128 Get the details of the destination organiztion such as rg23d account , excise in rg23d
4129 */
4130
4131 /*
4132 write code to pass specific a/c entries for ttading to trading iso
4133 IF AN ISO TRANSACTIONS HAPPENS BETWEEN TWO TRADING ORGANIZATIONS, THEN THE FOLLOWING A/c Entries
4134 need to be passed provided both the source and destination organizations have the 'Excise in rg23D ' field
4135 set to 'Y' for the org + location combination.
4136
4137 FOB Point => SHIPMENT
4138 Debit Inventory Intransit A/c of Receiving org for the excise + cess amount
4139 Credit Excise A/c of Source organization - Excise + Cess amount
4140
4141 FOB Point => RECEIPT
4142
4143 Debit Inventory Intransit A/c of Source Org - Excise + Cess amt
4144 Credit Excise A/c of Source Org - Excise and Cess amt.
4145 */
4146
4147 ln_debit_acc := v_intransit_inv_account;
4148 /*ln_credit_acc := ln_src_excise_23d_account; */ /*Commented for bug 14307860*/
4149
4150 /*Start additions by mmurtuza for Bug 14307860*/
4151 if (v_fob_point = 1) then
4152 ln_credit_acc := ln_src_excise_23d_account;
4153 elsif (v_fob_point = 2) then
4154 ln_credit_acc := v_excise_rcvble_account;
4155 end if;
4156 /*End additions by mmurtuza for Bug 14307860*/
4157
4158 if (v_fob_point = 1) then -- added by mmurtuza for bug 16022303
4159
4160 jai_cmn_gl_pkg.create_gl_entry
4161 (
4162 p_org_id,
4163 v_currency_code,
4164 p_duty_amount + nvl(ln_cess_amount,0)+ nvl(ln_sh_cess_amount,0) , --Credit /*Bug 5989740 bduvarag*/
4165 0, --Debit
4166 ln_credit_acc,
4167 p_source_name,
4168 p_category_name,
4169 p_created_by,
4170 NULL,
4171 NULL,
4172 NULL,
4173 NULL,
4174 P_REF_10,
4175 P_REF_23,
4176 P_REF_24,
4177 P_REF_25,
4178 P_REF_26);
4179
4180 /*Start additons by mmurtuza for bug 16022303*/
4181
4182 elsif (v_fob_point = 2) then
4183
4184 jai_cmn_gl_pkg.create_gl_entry
4185 (
4186 p_org_id,
4187 v_currency_code,
4188 p_duty_amount,
4189 0, --Debit
4190 ln_credit_acc,
4191 p_source_name,
4192 p_category_name,
4193 p_created_by,
4194 NULL,
4195 NULL,
4196 NULL,
4197 NULL,
4198 P_REF_10,
4199 P_REF_23,
4200 P_REF_24,
4201 P_REF_25,
4202 P_REF_26);
4203
4204 if (nvl(ln_cess_amount,0) <> 0) then
4205
4206 jai_cmn_gl_pkg.create_gl_entry
4207 (
4208 p_org_id,
4209 v_currency_code,
4210 nvl(ln_cess_amount,0),
4211 0, --Debit
4212 ln_credit_acc_cess_paid,
4213 p_source_name,
4214 p_category_name,
4215 p_created_by,
4216 NULL,
4217 NULL,
4218 NULL,
4219 NULL,
4220 P_REF_10,
4221 P_REF_23,
4222 P_REF_24,
4223 P_REF_25,
4224 P_REF_26);
4225
4226 end if;
4227
4228 if (nvl(ln_sh_cess_amount,0) <> 0) then
4229 jai_cmn_gl_pkg.create_gl_entry
4230 (
4231 p_org_id,
4232 v_currency_code,
4233 nvl(ln_sh_cess_amount,0) ,
4234 0, --Debit
4235 ln_credit_acc_sh_cess_paid,
4236 p_source_name,
4237 p_category_name,
4238 p_created_by,
4239 NULL,
4240 NULL,
4241 NULL,
4242 NULL,
4243 P_REF_10,
4244 P_REF_23,
4245 P_REF_24,
4246 P_REF_25,
4247 P_REF_26);
4248 end if;
4249
4250 end if;
4251
4252 /*End additons by mmurtuza for bug 16022303*/
4253
4254
4255
4256 jai_cmn_gl_pkg.create_gl_entry
4257 (p_org_id,
4258 v_currency_code,
4259 0,--Credit
4260 p_duty_amount + nvl(ln_cess_amount,0)+ nvl(ln_sh_cess_amount,0), --Debit /*Bug 5989740 bduvarag*/
4261 ln_debit_acc,
4262 p_source_name,
4263 p_category_name,
4264 p_created_by,
4265 NULL,
4266 NULL,
4267 NULL,
4268 NULL,
4269 P_REF_10,
4270 P_REF_23,
4271 P_REF_24,
4272 P_REF_25,
4273 P_REF_26
4274 );
4275
4276
4277 /*
4278 ends here additions by ssumaith - bug# 4171469
4279 */
4280
4281 END IF;
4282 END IF;
4283
4284
4285 END IF;
4286
4287 /*
4288 end ssumaith - bug#
4289 */
4290
4291 EXCEPTION
4292 WHEN OTHERS THEN
4293 FND_MESSAGE.SET_NAME ('JA','JAI_EXCEPTION_OCCURED');
4294 FND_MESSAGE.SET_TOKEN ('JAI_PROCESS_MSG',lv_object_name ||'.Err:'||sqlerrm);
4295 app_exception.raise_exception;
4296
4297 END Ja_In_Rg23d_Entry;
4298
4299 /***************************** Ja_In_Rg23d_Entry ****************************************************************/
4300
4301
4302 /***************************** JA_IN_REGISTER_TXN_ENTRY ******************************************************/
4303 PROCEDURE ja_in_register_txn_entry(p_org_id NUMBER,
4304 p_location_id NUMBER,
4305 p_excise_invoice_no VARCHAR2,
4306 p_transaction_name VARCHAR2,
4307 p_order_flag VARCHAR2,
4308 p_header_id NUMBER, /* Earlier was passing order header id */
4309 p_transaction_amount NUMBER,
4310 p_register_code VARCHAR2,
4311 p_creation_date DATE,
4312 p_created_by NUMBER,
4313 p_last_update_date DATE,
4314 p_last_updated_by NUMBER,
4315 p_last_update_login NUMBER ,
4316 p_order_invoice_type_id IN NUMBER,
4317 p_currency_rate IN NUMBER DEFAULT 1 /* added by CSahoo - bug#5390583 */
4318 )IS
4319 v_register_id NUMBER;
4320 v_register_balance NUMBER := 0;
4321 v_rg23d_register_balance NUMBER := 0;
4322 v_charge_amount NUMBER := 0;
4323 v_rg23d_transaction_amount NUMBER := 0;
4324 v_reg_transaction_amount NUMBER := 0;
4325 CURSOR register_balance_cur IS
4326 SELECT register_balance
4327 FROM JAI_OM_OE_BOND_TRXS
4328 WHERE transaction_id = (SELECT MAX(transaction_id) FROM JAI_OM_OE_BOND_TRXS
4329 WHERE register_id = (SELECT register_id FROM JAI_OM_OE_BOND_REG_HDRS
4330 WHERE organization_id = p_org_id AND location_id = p_location_id
4331 AND register_code = p_register_code));
4332 CURSOR register_balance_cur1 IS
4333 SELECT rg23d_register_balance
4334 FROM JAI_OM_OE_BOND_TRXS
4335 WHERE transaction_id = (SELECT MAX(transaction_id) FROM JAI_OM_OE_BOND_TRXS
4336 WHERE register_id = (SELECT register_id FROM JAI_OM_OE_BOND_REG_HDRS
4337 WHERE organization_id = p_org_id AND location_id = p_location_id
4338 AND register_code = p_register_code));
4339 CURSOR register_id_cur IS
4340 SELECT register_ID
4341 FROM JAI_OM_OE_BOND_REG_HDRS
4342 WHERE organization_id = p_org_id
4343 AND location_id = p_location_id
4344 AND register_code = p_register_code;
4345
4346
4347 cursor c_get_order_type(cp_header_id number) is
4348 select order_type_id
4349 from oe_order_headers_all
4350 where header_id = cp_header_id;
4351
4352 cursor c_get_invoice_type(cp_order_header_id number) is
4353 select batch_source_id
4354 from ra_customer_trx_all
4355 where customer_trx_id = cp_order_header_id;
4356
4357 cursor c_order_header_cur is
4358 select order_header_id
4359 from JAI_OM_WSH_LINES_ALL
4360 where delivery_id = p_header_id;
4361
4362 cursor c_invoice_header_cur(cp_header_id number) is
4363 select customer_trx_id
4364 from JAI_AR_TRX_LINES
4365 where customer_trx_line_id = cp_header_id;
4366
4367 /*
4368 following two cursors added by ssumaith - bug# 4136981
4369 */
4370 cursor c_get_om_cess_amount(cp_delivery_id number) is
4371 select SUM(NVL(jsptl.func_tax_amount,0)) tax_amount --NVL(sum(jsptl.func_tax_amount),0) tax_amount -- added , Ramananda NVL condition for bug #4516577
4372 from JAI_OM_WSH_LINE_TAXES jsptl ,
4373 JAI_CMN_TAXES_ALL jtc
4374 where jtc.tax_id = jsptl.tax_id
4375 and delivery_detail_id in
4376 (select delivery_detail_id
4377 from JAI_OM_WSH_LINES_ALL
4378 where delivery_id = cp_delivery_id
4379 )
4380 and upper(jtc.tax_type) in (jai_constants.TAX_TYPE_CVD_EDU_CESS,jai_constants.TAX_TYPE_EXC_EDU_CESS);
4381 /*Bug 5989740 bduvarag*/
4382 cursor c_get_om_sh_cess_amount(cp_delivery_id number) is
4383 select NVL(sum(jsptl.func_tax_amount),0) tax_amount
4384 from JAI_OM_WSH_LINE_TAXES jsptl ,
4385 JAI_CMN_TAXES_ALL jtc
4386 where jtc.tax_id = jsptl.tax_id
4387 and delivery_detail_id in
4388 (select delivery_detail_id
4389 from JAI_OM_WSH_LINES_ALL
4390 where delivery_id = cp_delivery_id
4391 )
4392 and upper(jtc.tax_type) in (JAI_CONSTANTS.tax_type_sh_cvd_edu_cess,JAI_CONSTANTS.tax_type_sh_exc_edu_cess);
4393
4394
4395 cursor c_get_ar_cess_amount(cp_customer_trx_id number) is
4396 select nvl(sum(jrctl.func_tax_amount),0) tax_amount
4397 from JAI_AR_TRX_TAX_LINES jrctl ,
4398 JAI_CMN_TAXES_ALL jtc
4399 where jtc.tax_id = jrctl.tax_id
4400 and link_to_cust_trx_line_id in
4401 (select customer_trx_line_id
4402 from JAI_AR_TRX_LINES
4403 where customer_trx_id = cp_customer_trx_id
4404 )
4405 and upper(jtc.tax_type) in (jai_constants.TAX_TYPE_CVD_EDU_CESS,jai_constants.TAX_TYPE_EXC_EDU_CESS) ;
4406 /*Bug 5989740 bduvarag*/
4407 cursor c_get_ar_sh_cess_amount(cp_customer_trx_id number) is
4408 select nvl(sum(jrctl.func_tax_amount),0) tax_amount
4409 from JAI_AR_TRX_TAX_LINES jrctl ,
4410 JAI_CMN_TAXES_ALL jtc
4411 where jtc.tax_id = jrctl.tax_id
4412 and link_to_cust_trx_line_id in
4413 (select customer_trx_line_id
4414 from JAI_AR_TRX_LINES
4415 where customer_trx_id = cp_customer_trx_id
4416 )
4417 and upper(jtc.tax_type) in (JAI_CONSTANTS.tax_type_sh_cvd_edu_cess,JAI_CONSTANTS.tax_type_sh_exc_edu_cess) ;
4418
4419 cursor c_ar_cess_ctr(cp_customer_trx_id number) is
4420 select count(1)
4421 from JAI_AR_TRX_TAX_LINES jrctl ,
4422 JAI_CMN_TAXES_ALL jtc
4423 where jtc.tax_id = jrctl.tax_id
4424 and link_to_cust_trx_line_id in
4425 (select customer_trx_line_id
4426 from JAI_AR_TRX_LINES
4427 where customer_trx_id = cp_customer_trx_id
4428 )
4429 and upper(jtc.tax_type) in (jai_constants.TAX_TYPE_CVD_EDU_CESS,jai_constants.TAX_TYPE_EXC_EDU_CESS) ;
4430 /*Bug 5989740 bduvarag*/
4431 cursor c_ar_sh_cess_ctr(cp_customer_trx_id number) is
4432 select count(1)
4433 from JAI_AR_TRX_TAX_LINES jrctl ,
4434 JAI_CMN_TAXES_ALL jtc
4435 where jtc.tax_id = jrctl.tax_id
4436 and link_to_cust_trx_line_id in
4437 (select customer_trx_line_id
4438 from JAI_AR_TRX_LINES
4439 where customer_trx_id = cp_customer_trx_id
4440 )
4441 and upper(jtc.tax_type) in (JAI_CONSTANTS.tax_type_sh_cvd_edu_cess,JAI_CONSTANTS.tax_type_sh_exc_edu_cess) ;
4442
4443 -- added by nkodakan for the bug 16014871 starts
4444
4445 Cursor get_ref_header_line_id(cp_line_id number)
4446 is
4447 select reference_header_id , reference_line_id
4448 from oe_order_lines_all
4449 where line_id= cp_line_id;
4450
4451 Cursor get_rma_delivery_id (cp_ref_header_id number,cp_ref_line_id number)
4452 is
4453 select delivery_id
4454 from JAI_OM_WSH_LINES_ALL
4455 where order_header_id=cp_ref_header_id
4456 and order_line_id=cp_ref_line_id;
4457
4458 -- added by nkodakan for the bug 16014871 ends
4459
4460 v_reg_expiry_date JAI_OM_OE_BOND_REG_HDRS.BOND_EXPIRY_DATE%type;
4461 v_lou_flag JAI_OM_OE_BOND_REG_HDRS.LOU_FLAG%TYPE;
4462 v_asst_register_id JAI_OM_OE_BOND_REG_HDRS.register_id%type;
4463 v_order_type_id JAI_OM_OE_BOND_REG_DTLS.order_type_id%type;
4464 v_register_code JAI_OM_OE_BOND_REG_HDRS.register_code%type;
4465 ln_header_id number;
4466 ln_cess_amount number;
4467 ln_Cess_Ctr number;
4468
4469 ln_sh_cess_amount number;/*Bug 5989740 bduvarag*/
4470 ln_sh_Cess_Ctr number;/*Bug 5989740 bduvarag*/
4471
4472 ln_customer_Trx_id number;
4473
4474 lv_object_name CONSTANT VARCHAR2 (61) := 'jai_om_rg_pkg.ja_in_register_txn_entry';
4475 /* added by nkodakan for the bug 16014871 starts */
4476 ln_rma_line_id number;
4477 ln_rma_header_id number;
4478 ln_ref_header_id number;
4479 ln_ref_line_id number;
4480 ln_rma_delivery_id number;
4481 /* added by nkodkan for the bug 16014871 ends */
4482
4483 BEGIN
4484
4485 if p_order_flag = 'Y' then
4486
4487 open c_order_header_cur;
4488 fetch c_order_header_cur into ln_header_id;
4489 close c_order_header_cur;
4490
4491 open c_get_order_type(ln_header_id);
4492 fetch c_get_order_type into v_order_type_id;
4493 close c_get_order_type;
4494
4495 open c_get_om_cess_amount(p_header_id);
4496 fetch c_get_om_cess_amount into ln_cess_amount;
4497 close c_get_om_cess_amount;
4498 /*Bug 5989740 bduvarag*/
4499 open c_get_om_sh_cess_amount(p_header_id);
4500 fetch c_get_om_sh_cess_amount into ln_sh_cess_amount;
4501 close c_get_om_sh_cess_amount;
4502
4503 elsif p_order_flag = 'N' then
4504 v_order_type_id := p_order_invoice_type_id;
4505
4506 open c_invoice_header_cur(p_header_id);
4507 fetch c_invoice_header_cur into ln_customer_Trx_id;
4508 close c_invoice_header_cur;
4509 -- commented by nkodakan for the bug 16014871 starts
4510 /* open c_get_ar_cess_amount(ln_customer_Trx_id);
4511 fetch c_get_ar_cess_amount into ln_cess_amount;
4512 close c_get_ar_cess_amount;
4513
4514 open c_ar_cess_ctr(ln_customer_Trx_id);
4515 fetch c_ar_cess_ctr into ln_Cess_Ctr;
4516 close c_ar_cess_ctr;
4517 --Bug 5989740 bduvarag start
4518 open c_get_ar_sh_cess_amount(ln_customer_Trx_id);
4519 fetch c_get_ar_sh_cess_amount into ln_sh_cess_amount;
4520 close c_get_ar_sh_cess_amount;
4521
4522 open c_ar_sh_cess_ctr(ln_customer_Trx_id);
4523 fetch c_ar_sh_cess_ctr into ln_sh_Cess_Ctr;
4524 close c_ar_sh_cess_ctr;
4525 --Bug 5989740 bduvarag end
4526 */
4527 -- commented by nkodakan for the bug 16014871 ends
4528 -- added by nkodakan for the bug 16014871 starts
4529 open get_ref_header_line_id(p_header_id);
4530 fetch get_ref_header_line_id into ln_ref_header_id, ln_ref_line_id;
4531 close get_ref_header_line_id;
4532
4533 open get_rma_delivery_id(ln_ref_header_id,ln_ref_line_id);
4534 fetch get_rma_delivery_id into ln_rma_delivery_id;
4535 close get_rma_delivery_id;
4536
4537 open c_get_om_cess_amount(ln_rma_delivery_id);
4538 fetch c_get_om_cess_amount into ln_cess_amount;
4539 close c_get_om_cess_amount;
4540
4541 open c_get_om_sh_cess_amount(ln_rma_delivery_id);
4542 fetch c_get_om_sh_cess_amount into ln_sh_cess_amount;
4543 close c_get_om_sh_cess_amount;
4544 -- added by nkodakan for the bug 16014871 ends
4545
4546 ln_header_id := ln_customer_Trx_id;
4547
4548 if ln_cess_amount = 0 then
4549
4550 if ln_Cess_Ctr > 0 then
4551
4552 for line_rec in
4553 (
4554 select customer_trx_line_id
4555 from JAI_AR_TRX_LINES
4556 where customer_trx_line_id = p_header_id
4557 )
4558 Loop
4559 for cess_rec in
4560 (
4561 select jrctl.link_to_cust_trx_line_id,
4562 jrctl.customer_trx_line_id,
4563 jrctl.tax_rate,
4564 jrctl.precedence_1,
4565 jrctl.precedence_2,
4566 jrctl.precedence_3,
4567 jrctl.precedence_4,
4568 jrctl.precedence_5 ,
4569 jrctl.precedence_6, -- Date 31/10/2006 Bug 5228046 added by SACSETHI
4570 jrctl.precedence_7,
4571 jrctl.precedence_8,
4572 jrctl.precedence_9,
4573 jrctl.precedence_10
4574 from JAI_AR_TRX_TAX_LINES jrctl ,
4575 JAI_CMN_TAXES_ALL jtc
4576 where link_to_cust_trx_line_id = line_rec.customer_trx_line_id
4577 and jtc.tax_id = jrctl.tax_id
4578 and jtc.tax_type in (jai_constants.TAX_TYPE_CVD_EDU_CESS,jai_constants.TAX_TYPE_EXC_EDU_CESS)
4579 )
4580 Loop
4581 for cess_amt_rec in
4582 (
4583 select
4584 decode(tax_amount, 0,
4585 (base_tax_amount * ( tax_rate / 100) )
4586 , tax_amount
4587 ) cess_amt
4588 from JAI_AR_TRX_TAX_LINES
4589 where link_to_cust_trx_line_id = line_rec.customer_trx_line_id
4590 and tax_line_no in
4591 (cess_rec.precedence_1,
4592 cess_rec.precedence_2,
4593 cess_rec.precedence_3,
4594 cess_rec.precedence_4,
4595 cess_rec.precedence_5 ,
4596 cess_rec.precedence_6, -- Date 31/10/2006 Bug 5228046 added by SACSETHI
4597 cess_rec.precedence_7,
4598 cess_rec.precedence_8,
4599 cess_rec.precedence_9,
4600 cess_rec.precedence_10
4601 )
4602 )
4603 Loop
4604 ln_cess_amount := nvl(ln_cess_amount,0) + ( nvl(cess_amt_rec.cess_amt,0) * (cess_rec.tax_rate/100 ));
4605 End loop;
4606 End loop;
4607 end loop;
4608 ln_cess_amount := ln_cess_amount * p_currency_rate; /* ssumaith - bug#6131804 */
4609 end if;
4610
4611 end if;
4612 /*Bug 5989740 bduvarag start*/
4613 if ln_sh_cess_amount = 0 then
4614
4615 if ln_sh_Cess_Ctr > 0 then
4616
4617 for line_rec in
4618 (
4619 select customer_trx_line_id
4620 from JAI_AR_TRX_LINES
4621 where customer_trx_line_id = p_header_id
4622 )
4623 Loop
4624 for cess_rec in
4625 (
4626 select jrctl.link_to_cust_trx_line_id,
4627 jrctl.customer_trx_line_id,
4628 jrctl.tax_rate,
4629 jrctl.precedence_1,
4630 jrctl.precedence_2,
4631 jrctl.precedence_3,
4632 jrctl.precedence_4,
4633 jrctl.precedence_5,
4634 jrctl.precedence_6,
4635 jrctl.precedence_7,
4636 jrctl.precedence_8,
4637 jrctl.precedence_9,
4638 jrctl.precedence_10
4639 from JAI_AR_TRX_TAX_LINES jrctl ,
4640 JAI_CMN_TAXES_ALL jtc
4641 where link_to_cust_trx_line_id = line_rec.customer_trx_line_id
4642 and jtc.tax_id = jrctl.tax_id
4643 and jtc.tax_type in (JAI_CONSTANTS.tax_type_sh_cvd_edu_cess,JAI_CONSTANTS.tax_type_sh_exc_edu_cess)
4644 )
4645 Loop
4646 for cess_amt_rec in
4647 (
4648 select
4649 decode(tax_amount, 0,
4650 (base_tax_amount * ( tax_rate / 100) )
4651 , tax_amount
4652 ) sh_cess_amt
4653 from JAI_AR_TRX_TAX_LINES
4654 where link_to_cust_trx_line_id = line_rec.customer_trx_line_id
4655 and tax_line_no in
4656 (cess_rec.precedence_1,
4657 cess_rec.precedence_2,
4658 cess_rec.precedence_3,
4659 cess_rec.precedence_4,
4660 cess_rec.precedence_5,
4661 cess_rec.precedence_6,
4662 cess_rec.precedence_7,
4663 cess_rec.precedence_8,
4664 cess_rec.precedence_9,
4665 cess_rec.precedence_10
4666 )
4667 )
4668 Loop
4669 ln_sh_cess_amount := nvl(ln_sh_cess_amount,0) + ( nvl(cess_amt_rec.sh_cess_amt,0) * (cess_rec.tax_rate/100 ));
4670 End loop;
4671 End loop;
4672 end loop;
4673 ln_sh_cess_amount := ln_sh_cess_amount * p_currency_rate;
4674 end if;
4675 end if;
4676 /*Bug 5989740 bduvarag end*/
4677 end if;
4678
4679 jai_cmn_bond_register_pkg.GET_REGISTER_ID (p_org_id ,
4680 p_location_id,
4681 v_order_type_id, -- order type id
4682 p_order_flag, -- order invoice type
4683 v_asst_register_id, -- out parameter to get the register id
4684 v_register_code);
4685
4686 /*
4687 call to get the register balance and expiry details
4688 */
4689 jai_cmn_bond_register_pkg.GET_REGISTER_DETAILS(v_asst_register_id,
4690 v_register_balance,
4691 v_reg_expiry_date,
4692 v_lou_flag);
4693
4694
4695
4696
4697 v_register_id := v_asst_register_id;
4698
4699
4700 /* NVLS to ln_cess_amount and ln_sh_cess_amount variables added by ssumaith - bug# 6487667*/
4701 IF v_register_code = 'BOND_REG'
4702 THEN
4703 v_charge_amount := v_register_balance;
4704 v_reg_transaction_amount := p_transaction_amount + NVL(ln_cess_amount,0) + NVL(ln_sh_cess_amount,0) ;/*Bug 5989740 bduvarag*/
4705 ELSIF v_register_code = '23D_EXPORT_WITHOUT_EXCISE'
4706 THEN
4707 v_charge_amount := v_rg23d_register_balance;
4708 v_rg23d_transaction_amount := p_transaction_amount + NVL(ln_cess_amount,0) + NVL(ln_sh_cess_amount,0);/*Bug 5989740 bduvarag*/
4709 END IF;
4710
4711
4712
4713
4714 --IF v_charge_amount >= p_transaction_amount THEN
4715 if ((nvl(v_lou_flag,'N') = 'Y') or ( v_register_balance >= p_transaction_amount) ) then
4716 if NVL(v_reg_expiry_date,sysdate) >= p_creation_date then
4717 INSERT INTO JAI_OM_OE_BOND_TRXS(transaction_id,
4718 register_id ,
4719 transaction_name,
4720 order_flag,
4721 order_header_id,
4722 transaction_amount,
4723 edu_cess_amount , /* added by ssumaith - bug# 4136981*/
4724 SH_CESS_AMOUNT ,/*Bug 5989740 bduvarag*/
4725 register_balance,
4726 rg23d_register_balance,
4727 -- picking_header_id, -- bug#6650203
4728 picking_line_id, -- ssumaith bug#6650203
4729 creation_date,
4730 created_by,
4731 last_update_login,
4732 last_update_date,
4733 last_updated_by
4734 )
4735 VALUES (
4736 JAI_OM_OE_BOND_TRXS_S.NEXTVAL,
4737 v_register_id,
4738 p_transaction_name,
4739 p_order_flag,
4740 ln_header_id ,
4741 p_transaction_amount,
4742 round(NVL(ln_cess_amount,0),2),
4743 round(NVL(ln_sh_cess_amount,0),2) ,/*Bug 5989740 bduvarag*/
4744 /* added by ssumaith - bug# 4136981*/
4745 /* added round(2) based on support feedback for cess CSahoo - bug# 5390583 */
4746 NVL(v_register_balance - v_reg_transaction_amount,0),
4747 NVL(v_rg23d_register_balance - v_rg23d_transaction_amount, 0),
4748 -- p_excise_invoice_no, -- bug#6650203
4749 p_header_id, -- ssumaith - bug#6650203
4750 p_creation_date,
4751 p_created_by,
4752 p_last_update_login,
4753 p_last_update_date,
4754 p_last_updated_by );
4755 else
4756 Fnd_File.PUT_LINE(Fnd_File.LOG, ' Validity Period of the Bond Register has expired' ); --Added for bug#7172215
4757 RAISE_APPLICATION_ERROR(-20121,' Validity Period of the Bond Register has expired' );
4758 end if;
4759 ELSE
4760 Fnd_File.PUT_LINE(Fnd_File.LOG, 'Bonded Amount -> ' || TO_CHAR(v_charge_amount) || 'cannot be less than the transaction_amount -> ' || TO_CHAR(p_transaction_amount)); --Added for bug#7172215
4761 RAISE_APPLICATION_ERROR(-20120, 'Bonded Amount -> ' || TO_CHAR(v_charge_amount)
4762 || 'cannot be less than the transaction_amount -> '
4763 || TO_CHAR(p_transaction_amount));
4764 END IF;
4765 EXCEPTION
4766 WHEN OTHERS THEN
4767 FND_MESSAGE.SET_NAME ('JA','JAI_EXCEPTION_OCCURED');
4768 FND_MESSAGE.SET_TOKEN ('JAI_PROCESS_MSG',lv_object_name ||'.Err:'||sqlerrm);
4769 app_exception.raise_exception;
4770 END ja_in_register_txn_entry;
4771 /***************************** JA_IN_REGISTER_TXN_ENTRY *************************************************************/
4772
4773 /*Bug 9550254 - Start*/
4774
4775 /***************************** JA_IN_RGI_BALANCE *************************************************************/
4776 Function ja_in_rgi_balance(
4777 p_organization_id in number,
4778 p_location_id in number,
4779 p_inventory_item_id in number,
4780 p_curr_finyear in number,
4781 p_slno out NOCOPY number,
4782 p_balance_packed out NOCOPY number) return number
4783 is
4784 Cursor c_yearslno is
4785 select fin_year,max(slno)
4786 from JAI_CMN_RG_I_TRXS
4787 where organization_id = p_organization_id
4788 and location_id = p_location_id
4789 and inventory_item_id = p_inventory_item_id
4790 group by fin_year
4791 order by fin_year desc;
4792
4793 Cursor c_fetchbalance(cp_balyear number,cp_slno number) is
4794 select NVL(balance_packed,0), NVL(balance_loose,0)
4795 from JAI_CMN_RG_I_TRXS
4796 where organization_id = p_organization_id
4797 and location_id = p_location_id
4798 and inventory_item_id = p_inventory_item_id
4799 and fin_year = cp_balyear
4800 and slno = cp_slno;
4801
4802 v_curr_Finyear number;
4803 v_Bal_Finyear number;
4804 v_slno number;
4805 v_balance_loose number;
4806
4807 begin
4808
4809 open c_yearslno;
4810 fetch c_yearslno into v_Bal_Finyear, v_slno;
4811 close c_yearslno;
4812
4813 If p_curr_finyear = v_Bal_Finyear then
4814 p_slno := v_slno;
4815 else
4816 p_slno := NULL;
4817 END IF;
4818
4819 open c_fetchbalance(v_Bal_Finyear,v_slno);
4820 fetch c_fetchbalance into p_balance_packed,v_balance_loose;
4821 close c_fetchbalance;
4822
4823 p_balance_packed := nvl(p_balance_packed,0);
4824 return(nvl(v_balance_loose,0));
4825
4826 end ja_in_rgi_balance;
4827 /***************************** JA_IN_RGI_BALANCE *************************************************************/
4828
4829 /***************************** ja_in_rg23i_balance *************************************************************/
4830 FUNCTION ja_in_rg23i_balance(
4831 p_organization_id in number,
4832 p_location_id in number,
4833 p_inventory_item_id in number,
4834 p_curr_finyear in number,
4835 p_register_type in varchar2,
4836 p_slno out NOCOPY number) return number
4837 is
4838 Cursor c_yearslno is
4839 select fin_year,max(slno) from JAI_CMN_RG_23AC_I_TRXS
4840 where organization_id = p_organization_id
4841 and location_id = p_location_id
4842 and inventory_item_id = p_inventory_item_id
4843 and register_type = p_register_type
4844 group by fin_year
4845 order by fin_year desc;
4846
4847 Cursor c_fetchbalance(cp_balyear number,cp_slno number) is
4848 select NVL(closing_balance_qty, 0) closing_balance_qty
4849 from JAI_CMN_RG_23AC_I_TRXS
4850 where organization_id = p_organization_id
4851 and location_id = p_location_id
4852 and inventory_item_id = p_inventory_item_id
4853 and register_type = p_register_type
4854 and fin_year = cp_balyear
4855 and slno = cp_slno;
4856
4857 v_curr_Finyear number;
4858 v_Bal_Finyear number;
4859 v_slno number;
4860 v_balance number;
4861
4862 begin
4863
4864 open c_yearslno;
4865 fetch c_yearslno into v_Bal_Finyear, v_slno;
4866 close c_yearslno;
4867
4868 If p_curr_finyear = v_Bal_Finyear then
4869 p_slno := v_slno;
4870 else
4871 p_slno := NULL;
4872 END IF;
4873
4874 open c_fetchbalance(v_Bal_Finyear,v_slno);
4875 fetch c_fetchbalance into v_balance;
4876 close c_fetchbalance;
4877
4878 return(nvl(v_balance,0));
4879
4880 end ja_in_rg23i_balance;
4881
4882 /***************************** ja_in_rg23i_balance *************************************************************/
4883
4884 /*Bug 9550254 - End*/
4885
4886 END jai_om_rg_pkg;