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